· 8 years ago · Dec 01, 2017, 02:18 AM
1IF OBJECT_ID( 'sp_NewCust_insert', 'P' ) IS NOT NULL
2 DROP PROCEDURE dbo.sp_NewCust_insert;
3GO
4CREATE PROCEDURE [dbo].[sp_NewCust_insert]
5AS
6 BEGIN
7 DECLARE @CompanyID INT
8 SELECT @CompanyID = CompanyID
9 FROM dbo.DMSAimStagingConfig
10
11
12 BEGIN TRANSACTION
13
14 BEGIN TRY
15
16 SELECT *
17 INTO #tempNewCust
18 FROM dbo.DMSAimNewCustomer
19 WHERE CustomerCD NOT IN (
20 SELECT ISNULL(CustomerID_PDA, '')
21 FROM PNCDMS..DMSCalculatorFrequency
22 WHERE CompanyID = @CompanyID )
23
24 INSERT INTO PNCDMS..DMSCalculatorFrequency
25 ( CompanyID ,
26 CustomerID_PDA ,
27 OutletName ,
28 ContactName ,
29 OutletType ,
30 DistributorID ,
31 Salesperson ,
32 CountryID ,
33 ProvinceID ,
34 DistrictID ,
35 WardID ,
36 StreetID ,
37 RouteCD ,
38 Email ,
39 ActionType ,
40 DMSCode ,
41 CustomerClassID ,
42 Phone1 ,
43 Phone2 ,
44 AddressLine1 ,
45 AddressLine2 ,
46 AddressLine3 ,
47 AddressLine4 ,
48 Lattitude ,
49 Longtitude ,
50 LocationCD ,
51 LocationName ,
52 SuggestTimeVisit ,
53 Note ,
54 IdentityCard ,
55 Birthday ,
56 BankAccNo ,
57 BankAccOwner ,
58 BankName ,
59 AvataFile ,
60 CaptureDate ,
61 Attribute0 ,
62 Attribute1 ,
63 Attribute2 ,
64 Attribute3 ,
65 Attribute4 ,
66 Attribute5 ,
67 Attribute6 ,
68 Attribute7 ,
69 Attribute8 ,
70 Attribute9 ,
71 CreatedByScreenID ,
72 CreatedDateTime ,
73 LastModifiedByScreenID ,
74 LastModifiedDateTime ,
75 Monday ,
76 Tuesday ,
77 Wednesday ,
78 Thursday ,
79 Friday ,
80 Saturday ,
81 Sunday
82 )
83 SELECT @CompanyID , -- CompanyID - int
84 CustomerCD , -- CustomerID_PDA - nvarchar(30)
85 CustomerName , -- OutletName - nvarchar(50)
86 ContactPerson , -- ContactName - nvarchar(50)
87 'O' , -- OutletType - varchar(15)
88 b.BranchID , -- DistributorID - int
89 e.EmployeeID , -- Salesperson - int
90 p.CountryID , -- CountryID - varchar(2)
91 c.ProvinceID , -- ProvinceID - int
92 DistrictID , -- DistrictID - int
93 WardID , -- WardID - int
94 StreetID , -- StreetID - int
95 e.RouteCD , -- RouteCD - varchar(20)
96 c.Email , -- Email - nvarchar(50)
97 'IN' , -- ActionType - varchar(2)
98 CustomerCD , -- DMSCode - varchar(20)
99 NULL , -- CustomerClassID - varchar(15)
100 Phone , -- Phone1 - nvarchar(12)
101 c.Phone2 , -- Phone2 - nvarchar(12)
102 AddressLine1 , -- AddressLine1 - nvarchar(50)
103 AddressLine2 , -- AddressLine2 - nvarchar(50)
104 AddressLine3 , -- AddressLine3 - nvarchar(50)
105 AddressLine4 , -- AddressLine4 - nvarchar(50)
106 Latitude , -- Lattitude - decimal
107 Longitude , -- Longtitude - decimal
108 NULL , -- LocationCD - nvarchar(50)
109 c.LocationName , -- LocationName - nvarchar(50)
110 SuggestTimeVisit , -- SuggestTimeVisit - nvarchar(50)
111 Note , -- Note - nvarchar(255)
112 CMNN , -- IdentityCard - nvarchar(50)
113 Birthday , -- Birthday - nvarchar(50)
114 BankAccNo , -- BankAccNo - nvarchar(50)
115 BankAccOwner , -- BankAccOwner - nvarchar(255)
116 BankName , -- BankName - nvarchar(255)
117 AvataFile , -- AvataFile - nvarchar(255)
118 CaptureDate , -- CaptureDate - datetime
119 Channel , -- Attribute0 - int
120 c.SubChannel , -- Attribute1 - int
121 c.ShopFormat , -- Attribute2 - int
122 c.DisplayClass , -- Attribute3 - int
123 NULL , -- Attribute4 - int
124 c.ShopArea , -- Attribute5 - int
125 LocationType , -- Attribute6 - int
126 c.SalesLevel , -- Attribute7 - int
127 SellingType , -- Attribute8 - int
128 NULL , -- Attribute9 - int
129 'PDA' , -- CreatedByScreenID - char(8)
130 GETDATE() , -- CreatedDateTime - datetime
131 'PDA' , -- LastModifiedByScreenID - char(8)
132 GETDATE() , -- LastModifiedDateTime - datetime
133 ( CASE WHEN LEN(DayVisitInWeek) >= 1
134 THEN SUBSTRING(DayVisitInWeek, 1, 1)
135 ELSE 0
136 END ) , -- Monday - bit
137 ( CASE WHEN LEN(DayVisitInWeek) >= 2
138 THEN SUBSTRING(DayVisitInWeek, 2, 1)
139 ELSE 0
140 END ) , -- Tuesday - bit
141 ( CASE WHEN LEN(DayVisitInWeek) >= 3
142 THEN SUBSTRING(DayVisitInWeek, 3, 1)
143 ELSE 0
144 END ) , -- Wednesday - bit
145 ( CASE WHEN LEN(DayVisitInWeek) >= 4
146 THEN SUBSTRING(DayVisitInWeek, 4, 1)
147 ELSE 0
148 END ) , -- Thursday - bit
149 ( CASE WHEN LEN(DayVisitInWeek) >= 5
150 THEN SUBSTRING(DayVisitInWeek, 5, 1)
151 ELSE 0
152 END ) , -- Friday - bit
153 ( CASE WHEN LEN(DayVisitInWeek) >= 6
154 THEN SUBSTRING(DayVisitInWeek, 6, 1)
155 ELSE 0
156 END ) , -- Saturday - bit
157 ( CASE WHEN LEN(DayVisitInWeek) >= 7
158 THEN SUBSTRING(DayVisitInWeek, 7, 1)
159 ELSE 0
160 END ) -- Sunday - bit
161 FROM #tempNewCust c
162 INNER JOIN dbo.DMSAimEmployeeList e ON c.UserName = e.EmployeeICD
163 INNER JOIN PNCDMS..DMSProvince p ON p.CompanyID = @CompanyID
164 AND p.ProvinceID = c.ProvinceID
165 LEFT JOIN PNCDMS..Branch b ON b.CompanyID = @CompanyID
166 AND c.DistributorCD = b.BranchCD
167
168 COMMIT TRANSACTION
169 END TRY
170 BEGIN CATCH
171 ROLLBACK TRANSACTION
172 END CATCH
173 END
174
175
176
177GO
178
179GO
180
181IF OBJECT_ID('sp_OES_DMSAimNewCustomer_Insert', 'P') IS NOT NULL
182 DROP PROCEDURE dbo.sp_OES_DMSAimNewCustomer_Insert;
183GO
184CREATE PROCEDURE [dbo].[sp_OES_DMSAimNewCustomer_Insert]
185 @UserName NVARCHAR(50) ,
186 @CustomerCD NVARCHAR(50) ,
187 @CustomerName NVARCHAR(255) ,
188 @LocationCD NVARCHAR(50) ,
189 @LocationName NVARCHAR(255) ,
190 @DistributorCD NVARCHAR(50) ,
191 @AddressLine1 NVARCHAR(255) ,
192 @AddressLine2 NVARCHAR(255) = '' ,
193 @AddressLine3 NVARCHAR(255) = '' ,
194 @AddressLine4 NVARCHAR(255) = '' ,
195 @StreetID NVARCHAR(50) = '' ,
196 @WardID NVARCHAR(50) = '' ,
197 @DistrictID NVARCHAR(50) = '' ,
198 @ProvinceID NVARCHAR(50) = '' ,
199 @RegionID NVARCHAR(50) = '' ,
200 @SuggestTimeVisit NVARCHAR(50) = '' ,
201 @ContactPerson NVARCHAR(255) = '' ,
202 @Phone NVARCHAR(50) = '' ,
203 @Note NVARCHAR(255) = '' ,
204 @LocationType INT = 0 ,
205 @DayVisitInWeek NVARCHAR(7) = '' ,
206 @SellingType INT = 0 ,
207 @Channel NVARCHAR(50) = '' ,
208 @CMNN NVARCHAR(50) = '' ,
209 @Birthday NVARCHAR(50) = '' ,
210 @BankAccNo NVARCHAR(50) = '' ,
211 @BankAccOwner NVARCHAR(255) = '' ,
212 @BankName NVARCHAR(255) = '' ,
213 @BeginTime NVARCHAR(30) = '' ,
214 @EndTime NVARCHAR(30) = '' ,
215 @GPSType NVARCHAR(50) = '' ,
216 @TimeGetLocation NVARCHAR(50) = '' ,
217 @Accuracy NVARCHAR(50) = '' ,
218 @InfoVersion NVARCHAR(50) = '' ,
219 @InfoSeri NVARCHAR(50) = '' ,
220 @IsEnableAirPlaneMode NVARCHAR(15) = '' ,
221 @IsEnableGPSMode NVARCHAR(15) = '' ,
222 @IsEnableNetworkMode NVARCHAR(15) = '' ,
223 @AvataFile NVARCHAR(255) = '' ,
224 @CaptureDate DATETIME ,
225 @Latitude FLOAT ,
226 @Longitude FLOAT ,
227 @IsUploaded INT ,
228 @IsSync INT ,
229 @Phone2 NVARCHAR(20) = '' ,
230 @SubChannel INT ,
231 @ShopFormat INT ,
232 @DisplayClass INT ,
233 @SalesLevel INT ,
234 @ShopArea INT ,
235 @Email NVARCHAR(50) = ''
236AS
237 BEGIN
238 IF NOT EXISTS ( SELECT *
239 FROM dbo.DMSAimNewCustomer
240 WHERE UserName = @UserName
241 AND CustomerCD = @CustomerCD
242 AND LocationCD = @LocationCD
243 AND DistributorCD = @DistributorCD )
244 INSERT INTO DMSAimNewCustomer
245 ( UserName ,
246 CustomerCD ,
247 CustomerName ,
248 LocationCD ,
249 LocationName ,
250 DistributorCD ,
251 AddressLine1 ,
252 AddressLine2 ,
253 AddressLine3 ,
254 AddressLine4 ,
255 StreetID ,
256 WardID ,
257 DistrictID ,
258 ProvinceID ,
259 RegionID ,
260 SuggestTimeVisit ,
261 ContactPerson ,
262 Phone ,
263 Note ,
264 LocationType ,
265 DayVisitInWeek ,
266 SellingType ,
267 Channel ,
268 CMNN ,
269 Birthday ,
270 BankAccNo ,
271 BankAccOwner ,
272 BankName ,
273 BeginTime ,
274 EndTime ,
275 GPSType ,
276 TimeGetLocation ,
277 Accuracy ,
278 InfoVersion ,
279 InfoSeri ,
280 IsEnableAirPlaneMode ,
281 IsEnableGPSMode ,
282 IsEnableNetworkMode ,
283 AvataFile ,
284 CaptureDate ,
285 Latitude ,
286 Longitude ,
287 IsUploaded ,
288 IsSync ,
289 Phone2 ,
290 SubChannel ,
291 ShopFormat ,
292 DisplayClass ,
293 SalesLevel ,
294 ShopArea ,
295 Email
296 )
297 VALUES ( @UserName ,
298 @CustomerCD ,
299 @CustomerName ,
300 @LocationCD ,
301 @LocationName ,
302 @DistributorCD ,
303 @AddressLine1 ,
304 @AddressLine2 ,
305 @AddressLine3 ,
306 @AddressLine4 ,
307 @StreetID ,
308 @WardID ,
309 @DistrictID ,
310 @ProvinceID ,
311 @RegionID ,
312 @SuggestTimeVisit ,
313 @ContactPerson ,
314 @Phone ,
315 @Note ,
316 @LocationType ,
317 @DayVisitInWeek ,
318 @SellingType ,
319 @Channel ,
320 @CMNN ,
321 @Birthday ,
322 @BankAccNo ,
323 @BankAccOwner ,
324 @BankName ,
325 @BeginTime ,
326 @EndTime ,
327 @GPSType ,
328 @TimeGetLocation ,
329 @Accuracy ,
330 @InfoVersion ,
331 @InfoSeri ,
332 @IsEnableAirPlaneMode ,
333 @IsEnableGPSMode ,
334 @IsEnableNetworkMode ,
335 @AvataFile ,
336 @CaptureDate ,
337 @Latitude ,
338 @Longitude ,
339 @IsUploaded ,
340 @IsSync ,
341 @Phone2 ,
342 @SubChannel ,
343 @ShopFormat ,
344 @DisplayClass ,
345 @SalesLevel ,
346 @ShopArea ,
347 @Email
348 )
349 END
350
351GO
352
353GO
354
355IF OBJECT_ID( 'sp_OES_DMSAimSMAvatar_Select', 'P' ) IS NOT NULL
356 DROP PROCEDURE dbo.sp_OES_DMSAimSMAvatar_Select;
357GO
358CREATE PROCEDURE [dbo].[sp_OES_DMSAimSMAvatar_Select]
359 -- Add the parameters for the stored procedure here
360 @UserName NVARCHAR(50)
361AS
362BEGIN
363 SELECT * FROM dbo.DMSAimSMAvatar WHERE UserName = @UserName
364END
365
366GO
367
368GO
369
370IF OBJECT_ID('sp_OES_DMSAimUploadedSMAvatar_Insert', 'P') IS NOT NULL
371 DROP PROCEDURE dbo.sp_OES_DMSAimUploadedSMAvatar_Insert;
372GO
373CREATE PROCEDURE [dbo].[sp_OES_DMSAimUploadedSMAvatar_Insert]
374 @UserName NVARCHAR(50) ,
375 @AvatarFile NVARCHAR(255) ,
376 @MD5 NVARCHAR(50)
377AS
378 BEGIN
379 UPDATE DMSAimUploadedSMAvatar
380 SET AvatarFile=@AvatarFile, MD5 = @MD5
381 WHERE UserName = @UserName
382
383 IF @@ROWCOUNT = 0
384 INSERT INTO DMSAimUploadedSMAvatar (UserName, AvatarFile, MD5)
385 VALUES (@UserName, @AvatarFile, @MD5)
386 END
387
388GO
389
390GO
391
392IF OBJECT_ID( 'sp_Sync_Calc_allEmployee', 'P' ) IS NOT NULL
393 DROP PROCEDURE dbo.sp_Sync_Calc_allEmployee;
394GO
395CREATE PROCEDURE [dbo].[sp_Sync_Calc_allEmployee] @CompanyID INT
396AS
397 BEGIN
398 TRUNCATE TABLE dbo.DMSAimEmployeeList;
399 INSERT INTO dbo.DMSAimEmployeeList
400 ( EmployeeID ,
401 EmployeeICD ,
402 RouteCD ,
403 GPSLock ,
404 Distance ,
405 GPSOutRouteLock ,
406 FullVisitOutRoute ,
407 RefNBR ,
408 SellingCategoryID
409 )
410 SELECT DISTINCT
411 s.EmployeeID , -- EmployeeID - int
412 s.EmployeeCD , -- EmployeeICD - nvarchar(50)
413 r.RouteCD , -- RouteCD - nvarchar(50)
414 ISNULL(r.GPSLock, 0) ,
415 ISNULL(r.Distance, 200) ,
416 ISNULL(r.GPSOutRouteLock, 0) ,
417 ISNULL(r.FullVisitOutRoute, 0) ,
418 r.RefNbr ,
419 r.SellingCategoryID
420 FROM PNCDMS..DMSSalesForce s
421 INNER JOIN PNCDMS..DMSSFHierachy h ON h.CompanyID = s.CompanyID
422 AND s.SFHierachyID = h.SFHierachyID
423 AND h.IsSalesperson = 1
424 INNER JOIN PNCDMS..DMSRouteSetting r ON r.CompanyID = s.CompanyID
425 AND r.SalespersonID = s.EmployeeID
426 AND r.Active = 1
427 AND r.Status = 'G'
428 WHERE DATEDIFF(dd, r.EffectiveDate, GETDATE()) >= 0
429 AND DATEDIFF(DD, ISNULL(r.EndDate, GETDATE()),
430 GETDATE()) <= 0
431 AND h.CompanyID = @CompanyID
432 AND s.Active = 1;
433
434
435--Load danh sach NPP ma SM dc gan vao
436 TRUNCATE TABLE dbo.DMSAimDistributor;
437 INSERT INTO dbo.DMSAimDistributor
438 ( UserName ,
439 DistributorCD ,
440 DistributorName ,
441 DistributorLat ,
442 DistributorLong ,
443 SalesOrg0_Value ,
444 SalesOrg0_Name ,
445 SalesOrg1_Value ,
446 SalesOrg1_Name ,
447 SalesOrg2_Value ,
448 SalesOrg2_Name ,
449 SalesOrg3_Value ,
450 SalesOrg3_Name ,
451 SalesOrg4_Value ,
452 SalesOrg4_Name ,
453 SalesOrg5_Value ,
454 SalesOrg5_Name ,
455 SalesOrg6_Value ,
456 SalesOrg6_Name ,
457 SalesOrg7_Value ,
458 SalesOrg7_Name ,
459 SalesOrg8_Value ,
460 SalesOrg8_Name ,
461 SalesOrg9_Value ,
462 SalesOrg9_Name
463 )
464 SELECT DISTINCT
465 s.EmployeeCD , -- UserName - nvarchar(50)
466 b.BranchCD , -- DistributorCD - nvarchar(50)
467 a.AcctName , -- DistributorName - nvarchar(255)
468 0.0 , -- DistributorLat - float
469 0.0 , -- DistributorLong - float
470 d.Attribute0ID , -- SalesOrg0_Value - varchar(15)
471 d.Attribute0Descr , -- SalesOrg0_Name - nvarchar(255)
472 d.Attribute1ID , -- SalesOrg1_Value - varchar(15)
473 d.Attribute1Descr , -- SalesOrg1_Name - nvarchar(255)
474 d.Attribute2ID , -- SalesOrg2_Value - varchar(15)
475 d.Attribute2Descr , -- SalesOrg2_Name - nvarchar(255)
476 d.Attribute3ID , -- SalesOrg3_Value - varchar(15)
477 d.Attribute3Descr , -- SalesOrg3_Name - nvarchar(255)
478 d.Attribute4ID , -- SalesOrg4_Value - varchar(15)
479 d.Attribute4Descr , -- SalesOrg4_Name - nvarchar(255)
480 d.Attribute5ID , -- SalesOrg5_Value - varchar(15)
481 d.Attribute5Descr , -- SalesOrg5_Name - nvarchar(255)
482 d.Attribute6ID , -- SalesOrg6_Value - varchar(15)
483 d.Attribute6Descr , -- SalesOrg6_Name - nvarchar(255)
484 d.Attribute7ID , -- SalesOrg7_Value - varchar(15)
485 d.Attribute7Descr , -- SalesOrg7_Name - nvarchar(255)
486 d.Attribute8ID , -- SalesOrg8_Value - varchar(15)
487 d.Attribute8Descr , -- SalesOrg8_Name - nvarchar(255)
488 d.Attribute9ID , -- SalesOrg9_Value - varchar(15)
489 d.Attribute9Descr -- SalesOrg9_Name - nvarchar(255)
490 FROM PNCDMS..DMSSalesForce s
491 INNER JOIN PNCDMS..DMSSFHierachy h ON h.CompanyID = s.CompanyID
492 AND s.SFHierachyID = h.SFHierachyID
493 AND h.IsSalesperson = 1
494 INNER JOIN PNCDMS..DMSRouteSetting r ON r.CompanyID = s.CompanyID
495 AND r.SalespersonID = s.EmployeeID
496 AND r.Active = 1
497 AND r.Status = 'G'
498 INNER JOIN PNCDMS..DMSMCPDetail p ON r.RefNbr = p.RefNbr
499 AND p.CompanyID = r.CompanyID
500 INNER JOIN PNCDMS..Branch b ON p.DistributorID = b.BranchID
501 AND p.CompanyID = b.CompanyID
502 INNER JOIN PNCDMS..BAccount a ON b.CompanyID = a.CompanyID
503 AND a.BAccountID = b.BAccountID
504 AND a.CompanyID = @CompanyID
505 INNER JOIN PNCDMS.dbo.DMSViewDistributorLocation d ON p.DistributorID = d.DistributorID
506 AND p.DistributorLocationID = d.LocationID
507 WHERE DATEDIFF(dd, r.EffectiveDate, GETDATE()) >= 0
508 AND DATEDIFF(DD, ISNULL(r.EndDate, GETDATE()),
509 GETDATE()) <= 0;
510
511 INSERT INTO dbo.DMSAimUser
512 ( UserName ,
513 Name ,
514 DataDate ,
515 WorkingDate ,
516 LastVisistID ,
517 LastOrderID ,
518 WorkingDay ,
519 TotalWorkingDay
520 )
521 SELECT EmployeeICD , -- UserName - nvarchar(50)
522 s.Descr , -- Name - nvarchar(255)
523 CONVERT(VARCHAR(30), GETDATE(), 121) , -- DataDate - datetime
524 CONVERT(VARCHAR(30), GETDATE(), 121) , -- WorkingDate - smalldatetime
525 0 , -- LastVisistID - int
526 0 , -- LastOrderID - int
527 w.MTDWorkingday , -- WorkingDay - int
528 w.TotalWorkingday -- TotalWorkingDay - int
529 FROM PNCDMS..DMSSalesForce s
530 INNER JOIN dbo.DMSAimEmployeeList e ON e.EmployeeID = s.EmployeeID
531 INNER JOIN dbo.vs_Salesman_workingday w ON e.EmployeeID = w.SalespersonID
532 WHERE s.CompanyID = @CompanyID;
533
534 INSERT INTO dbo.DMSAimParameter
535 ( ID ,
536 [Desc] ,
537 Value ,
538 UserName
539 )
540 --VALUES ( N'', -- ID - nvarchar(50)
541 -- N'', -- Desc - nvarchar(100)
542 -- N'', -- Value - nvarchar(50)
543 -- N'' -- UserName - nvarchar(50)
544 -- )
545 SELECT 'Distance' AS ID ,
546 N'Khoảng cách cho phép' AS [DESC] ,
547 Distance AS Value ,
548 EmployeeICD
549 FROM dbo.DMSAimEmployeeList
550 UNION ALL
551 SELECT 'GPSLock' AS ID ,
552 N'Khóa tá»a Ä‘á»™' AS [DESC] ,
553 GPSLock AS Value ,
554 EmployeeICD
555 FROM dbo.DMSAimEmployeeList
556 UNION ALL
557 SELECT 'FullVisitOutRoute' AS ID ,
558 N'Cho phép visit full trái tuyến' AS [DESC] ,
559 FullVisitOutRoute AS Value ,
560 EmployeeICD
561 FROM dbo.DMSAimEmployeeList
562 UNION ALL
563 SELECT 'GPSOutRouteLock' AS ID ,
564 N'Khóa tá»a Ä‘á»™ khi visit trái tuyến' AS [DESC] ,
565 GPSOutRouteLock AS Value ,
566 EmployeeICD
567 FROM dbo.DMSAimEmployeeList;
568
569 INSERT INTO dbo.DMSSyncSetting
570 ( UserName ,
571 SyncDistance ,
572 ByPass
573 )
574 SELECT u.UserName ,
575 200 , -- SyncDistance - float
576 1 -- ByPass - int
577 FROM dbo.DMSAimUser u
578 LEFT JOIN DMSSyncSetting s ON s.UserName = u.UserName
579 WHERE s.UserName IS NULL;
580
581 END;
582
583GO
584
585
586GO
587
588IF OBJECT_ID( 'sp_Sync_Calc_allInventory', 'P' ) IS NOT NULL
589 DROP PROCEDURE dbo.sp_Sync_Calc_allInventory;
590GO
591CREATE PROCEDURE [dbo].[sp_Sync_Calc_allInventory] @CompanyID INT
592AS
593 BEGIN
594 TRUNCATE TABLE dbo.DMSAimInventoryList;
595 INSERT INTO dbo.DMSAimInventoryList
596 ( InventoryID ,
597 InventoryCD ,
598 SalesUnit ,
599 UnitPerCase
600 )
601 SELECT i.InventoryID , -- InventoryID - int
602 InventoryCD , -- InventoryCD - nvarchar(50)
603 SalesUnit , -- SalesUnit - nvarchar(50),
604 CAST(ISNULL(u.UnitRate, 1) AS INT)
605 FROM PNCDMS..InventoryItem i
606 LEFT JOIN PNCDMS..INUnit u ON i.CompanyID = u.CompanyID
607 AND i.InventoryID = u.InventoryID
608 AND u.ToUnit = i.SalesUnit
609 AND u.FromUnit = 'CAR'
610 WHERE i.CompanyID = @CompanyID
611 AND ItemStatus NOT IN ( 'IN' );
612
613 -- Lấy danh sách đơn vị của sản phẩm
614 TRUNCATE TABLE dbo.DMSAimInventoryUnit
615 INSERT INTO dbo.DMSAimInventoryUnit
616 ( InventoryCD ,
617 SalesUnit ,
618 CaseUnit ,
619 UnitRate
620 )
621 SELECT i.InventoryCD , -- InventoryCD - nvarchar(50)
622 i.SalesUnit , -- SalesUnit - nvarchar(50),
623 u.FromUnit , -- CaseUnit - nvarchar(50)
624 CAST(ISNULL(u.UnitRate, 1) AS INT) -- UnitRate - int
625 FROM dbo.DMSAimInventoryList i
626 LEFT JOIN PNCDMS..INUnit u ON i.InventoryID = u.InventoryID
627 AND u.ToUnit <> u.FromUnit
628 AND u.ToUnit = i.SalesUnit
629 WHERE u.CompanyID = @CompanyID
630 AND u.UnitRate <> 0
631
632 -- Gen SP cho all SM
633 INSERT INTO dbo.DMSAimInventoryItem
634 ( UserName ,
635 InventoryCD ,
636 InventoryName ,
637 ShortName ,
638 UnitPerCase ,
639 SalesUnit ,
640 Kparam ,
641 AllowSales ,
642 Enddate ,
643 ProductKind ,
644 Volume ,
645 Weight ,
646 IsExclusive ,
647 PurchaseUnit
648 )
649 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
650 l.InventoryCD , -- InventoryCD - nvarchar(50)
651 i.Descr , -- InventoryName - nvarchar(255)
652 ISNULL(i.UsrShortName, i.Descr) , -- ShortName - nvarchar(50)
653 l.UnitPerCase , -- UnitPerCase - int
654 l.SalesUnit , -- SalesUnit - nvarchar(50)
655 0.0 , -- Kparam - float
656 CASE WHEN e.SellingCategoryID IS NOT NULL
657 THEN ( CASE WHEN i.UsrIsExclusive = 1
658 OR c.InventoryID IS NOT NULL
659 THEN 1
660 ELSE 0
661 END )
662 ELSE 1
663 END AllowSales , -- AllowSales - int
664 GETDATE() , -- Enddate - date
665 0 , -- ProductKind - int
666 i.BaseItemVolume ,
667 i.BaseItemWeight ,
668 ISNULL(i.UsrIsExclusive, 0) ,
669 i.PurchaseUnit
670 FROM PNCDMS..InventoryItem i
671 INNER JOIN dbo.DMSAimInventoryList l ON i.InventoryID = l.InventoryID
672 AND i.CompanyID = @CompanyID
673 CROSS JOIN dbo.DMSAimEmployeeList e
674 LEFT JOIN PNCDMS..DMSViewProductBySellingCat c ON i.CompanyID = c.CompanyID
675 AND i.InventoryID = c.InventoryID
676 AND c.SellingCategoryID = e.SellingCategoryID
677
678 -- Lay gia ban
679 INSERT INTO dbo.DMSAimSalesPrice
680 ( UserName ,
681 DistrictID ,
682 CustomerPriceClassID ,
683 InventoryCD ,
684 Price
685 )
686 SELECT d.UserName , -- UserName - nvarchar(50)
687 s.DistrictID , -- DistrictID - nvarchar(50)
688 s.CustomerPriceClassID , -- CustomerPriceClassID - nvarchar(50)
689 l.InventoryCD , -- InventoryCD - nvarchar(50)
690 s.Price -- Price - float
691 FROM dbo.DMSTempSalesPrice s
692 INNER JOIN dbo.DMSAimInventoryList l ON l.InventoryID = s.InventoryCD
693 INNER JOIN ( SELECT DISTINCT
694 UserName ,
695 DistrictID
696 FROM dbo.DMSAimCustomer
697 ) AS d ON d.DistrictID = s.DistrictID;
698
699 END;
700
701GO
702
703GO
704
705IF OBJECT_ID( 'sp_Sync_file_new', 'P' ) IS NOT NULL
706 DROP PROCEDURE dbo.sp_Sync_file_new;
707GO
708CREATE PROCEDURE [dbo].[sp_Sync_file_new] @CompanyID INT
709AS
710 BEGIN
711 DECLARE @StagingURL NVARCHAR(250);
712 DECLARE @AcuURL NVARCHAR(250);
713
714 SELECT @StagingURL = StagingURL ,
715 @AcuURL = AcuURL
716 FROM dbo.DMSAimStagingConfig;
717
718 INSERT INTO dbo.DMSAimFileSync
719 ( UserName ,
720 Link ,
721 MD5CheckSum ,
722 SaveTo
723 )
724 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
725 Link , -- Link - nvarchar(255)
726 md5 , -- MD5CheckSum - nvarchar(50)
727 saveto -- SaveTo - nvarchar(255)
728 FROM dbo.DMSAimUploadFileList
729 CROSS JOIN dbo.DMSAimEmployeeList e
730
731 INSERT INTO dbo.DMSAimPresentation
732 ( UserName ,
733 PresentationID ,
734 [Type] ,
735 Descr ,
736 Startdate ,
737 Enddate
738 )
739 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
740 PresentationID , -- PresentationID - nvarchar(50)
741 [Type] , -- Type - nvarchar(50)
742 Descr , -- Descr - nvarchar(1000)
743 Startdate , -- Startdate - date
744 Enddate -- Enddate - date
745 FROM dbo.DMSAimPresentationList
746 CROSS JOIN dbo.DMSAimEmployeeList e;
747
748
749 INSERT INTO dbo.DMSAimPresentationFile
750 ( UserName ,
751 PresentationID ,
752 Filename ,
753 FileLocation
754 )
755 SELECT DISTINCT
756 e.EmployeeICD , -- UserName - nvarchar(50)
757 PresentationID , -- PresentationID - nvarchar(50)
758 Filename , -- Filename - nvarchar(255)
759 FileLocation -- FileLocation - nvarchar(500)
760 FROM dbo.DMSAimPresentationFileList
761 CROSS JOIN dbo.DMSAimEmployeeList e;
762
763
764 --SMAvatar
765 TRUNCATE TABLE dbo.DMSAimSMAvatar
766 INSERT INTO dbo.DMSAimSMAvatar
767 ( UserName, AvatarFile, MD5 )
768 SELECT UserName,
769 REVERSE(LEFT(REVERSE(AvatarFile),
770 CHARINDEX('/', REVERSE(AvatarFile)) - 1)) ,
771 MD5
772 FROM dbo.DMSAimUploadedSMAvatar
773
774 INSERT INTO dbo.DMSAimFileSync
775 ( UserName ,
776 Link ,
777 MD5CheckSum ,
778 SaveTo
779 )
780 SELECT UserName , -- UserName - nvarchar(50)
781 @StagingURL + '/SalesmanAvatarImage/' + AvatarFile , -- Link - nvarchar(255)
782 MD5 , -- MD5CheckSum - nvarchar(50)
783 '/sdcard/NextGen/AimBG/sm_avatar.jpg' -- SaveTo - nvarchar(255)
784 FROM dbo.DMSAimSMAvatar a
785
786 --Cust avatar
787 INSERT INTO dbo.DMSAimFileSync
788 ( UserName ,
789 Link ,
790 MD5CheckSum ,
791 SaveTo
792 )
793 SELECT UserName , -- UserName - nvarchar(50)
794 @StagingURL + '/AvartaImage/' + AvatarName , -- Link - nvarchar(255)
795 r.MD5Checksum , -- MD5CheckSum - nvarchar(50)
796 '/sdcard/NextGen/AimAvatar/' + a.AvatarName -- SaveTo - nvarchar(255)
797 FROM dbo.DMSAimCustomer c
798 INNER JOIN dbo.DMSAimCustomerAvatar a ON a.CustomerCD = c.CustomerCD
799 AND a.LocationCD = c.LocationCD
800 INNER JOIN dbo.DMSAimImageReceived r ON a.AvatarName = r.ImageName
801 AND r.Type = 'A'
802
803 -- planogram
804 UNION ALL
805 SELECT DISTINCT
806 e.EmployeeICD ,
807 @AcuURL + ImageFile ,
808 MD5 ,
809 '/sdcard/NextGen/AimImage/' + ImageName
810 FROM PNCDMS..DMSProgramPlanogramDetailImage
811 CROSS JOIN dbo.DMSAimEmployeeList e
812 WHERE CompanyID = @CompanyID
813 AND IsActive = 1;
814 END;
815
816GO
817
818IF OBJECT_ID('sp_Sync_Price_Priority_Distributor_Inventory_SalesOrg', 'P') IS NOT NULL
819 DROP PROCEDURE dbo.sp_Sync_Price_Priority_Distributor_Inventory_SalesOrg;
820GO
821CREATE PROCEDURE [dbo].[sp_Sync_Price_Priority_Distributor_Inventory_SalesOrg]
822 @CompanyID INT
823AS
824 BEGIN
825
826 TRUNCATE TABLE dbo.DMSAimPricePriorityDistributor;
827 TRUNCATE TABLE dbo.DMSAimPricePriorityInventory;
828 TRUNCATE TABLE dbo.DMSAimPricePrioritySalesOrg;
829 --Get price with priority of distributor
830 INSERT INTO dbo.DMSAimPricePriorityDistributor
831 ( CompanyID ,
832 RefNbr ,
833 DistributorID ,
834 DistributorCD
835 )
836 SELECT p.CompanyID ,
837 p.RefNbr ,
838 p.DistributorID ,
839 b.BranchCD
840 FROM PNCDMS..DMSPricePriorityDistributor p
841 JOIN
842 PNCDMS..Branch b
843 ON b.CompanyID = p.CompanyID AND b.BranchID = p.DistributorID
844 WHERE p.CompanyID = @CompanyID
845
846 --Get price with priority of Inventory
847
848 INSERT INTO dbo.DMSAimPricePriorityInventory
849 ( CompanyID ,
850 RefNbr ,
851 InventoryId ,
852 InventoryCD ,
853 UOM ,
854 Price ,
855 Priority ,
856 CreatedDateTime
857 )
858 SELECT p.CompanyID ,
859 p.RefNbr ,
860 p.InventoryID,
861 i.InventoryCD,
862 p.UOM,
863 p.Price,
864 p.Priority,
865 p.CreatedDateTime
866 FROM PNCDMS..DMSPricePriorityInventory p
867 INNER JOIN
868 dbo.DMSAimInventoryList i
869 ON i.InventoryID = p.InventoryID
870 WHERE CompanyID = @CompanyID
871
872 --Get price with priority of SalesOrg
873 INSERT INTO dbo.DMSAimPricePrioritySalesOrg
874 ( CompanyID ,
875 RefNbr ,
876 SalesOrgID ,
877 ChannelID ,
878 CreatedDateTime ,
879 SalesOrgCD
880 )
881 SELECT CompanyID ,
882 RefNbr ,
883 SalesOrgID,
884 ChannelID,
885 CreatedDateTime,
886 SalesOrgCD
887
888 FROM PNCDMS..DMSPricePrioritySalesOrg
889 WHERE CompanyID = @CompanyID
890
891 END
892
893GO
894
895GO
896
897IF OBJECT_ID( 'sp_Sync_TMKT_New', 'P' ) IS NOT NULL
898 DROP PROCEDURE dbo.sp_Sync_TMKT_New;
899GO
900CREATE PROCEDURE [dbo].[sp_Sync_TMKT_New] @CompanyID INT
901AS
902 BEGIN
903 --Insert customer group define
904 INSERT INTO dbo.DMSAimTMKTCustomerGroupHeader
905 ( UserName ,
906 GroupCD ,
907 Descr ,
908 CustomerType ,
909 CustomerGroupType
910 )
911 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
912 GroupCD , -- GroupCD - nvarchar(50)
913 Descr , -- Descr - nvarchar(255)
914 CustomerType , -- CustomerType - nvarchar(1)
915 CustomerGroupType -- CustomerGroupType - nvarchar(1)
916 FROM PNCDMS..DMSCustomerGroupHeader h
917 CROSS JOIN dbo.DMSAimEmployeeList e
918 WHERE Status = 'R'
919 AND CompanyID = @CompanyID;
920 INSERT INTO dbo.DMSAimTMKTCustomerGroupAttribute
921 ( UserName ,
922 GroupCD ,
923 Attribute0 ,
924 Attribute1 ,
925 Attribute2 ,
926 Attribute3 ,
927 Attribute4 ,
928 Attribute5 ,
929 Attribute6 ,
930 Attribute7 ,
931 Attribute8 ,
932 Attribute9
933 )
934 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
935 h.GroupCD , -- GroupCD - nvarchar(50)
936 Attribute0 , -- Attribute0 - int
937 Attribute1 , -- Attribute1 - int
938 Attribute2 , -- Attribute2 - int
939 Attribute3 , -- Attribute3 - int
940 Attribute4 , -- Attribute4 - int
941 Attribute5 , -- Attribute5 - int
942 Attribute6 , -- Attribute6 - int
943 Attribute7 , -- Attribute7 - int
944 Attribute8 , -- Attribute8 - int
945 Attribute9 -- Attribute9 - int
946 FROM PNCDMS..DMSCustomerGroupAttribute a
947 INNER JOIN PNCDMS..DMSCustomerGroupHeader h ON h.CompanyID = a.CompanyID
948 AND h.GroupID = a.GroupID
949 CROSS JOIN dbo.DMSAimEmployeeList e
950 WHERE h.Status = 'R'
951 AND a.CompanyID = @CompanyID;
952 INSERT INTO dbo.DMSAimTMKTCustomerGroupList
953 ( UserName ,
954 GroupCD ,
955 CustomerCD ,
956 LocationCD
957 )
958 SELECT DISTINCT
959 u.UserName , -- UserName - nvarchar(50)
960 h.GroupCD , -- GroupCD - nvarchar(50)
961 c.CustomerCD , -- CustomerCD - nvarchar(50)
962 c.LocationCD -- LocationCD - nvarchar(50)
963 FROM PNCDMS..DMSCustomerGroupList l
964 INNER JOIN PNCDMS..DMSCustomerGroupHeader h ON h.CompanyID = l.CompanyID
965 AND h.GroupID = l.GroupID
966 AND h.CompanyID = @CompanyID
967 INNER JOIN dbo.DMSAllCustomer c ON c.LocationID = l.CustomerID
968 INNER JOIN dbo.DMSAimCustomer u ON c.LocationCD = u.LocationCD
969 AND c.CustomerCD = u.CustomerCD;
970
971 --DN TMKT program
972 INSERT INTO dbo.DMSAimTMKTProgram
973 ( UserName ,
974 ProgramCD ,
975 ProgramType ,
976 Descr ,
977 LoyaltyType ,
978 IsRevenue ,
979 Frequency ,
980 IncentiveType ,
981 ApplyTo ,
982 ApplicableTo ,
983 StartRegisterDate ,
984 EndRegisterDate ,
985 StartExecutionDate ,
986 EndExecutionDate ,
987 RegisterType ,
988 EffectiveDateType ,
989 IsAccumulate ,
990 AccumulateBy ,
991 IsRatio ,
992 RatioBy
993 )
994 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
995 ProgramCD , -- ProgramCD - nvarchar(50)
996 ProgramType , -- ProgramType - nvarchar(1)
997 Descr , -- Descr - nvarchar(255)
998 '' , -- LoyaltyType - nvarchar(1)
999 0 , -- IsRevenue - int
1000 Frequency , -- Frequency - nvarchar(1)
1001 IncentiveBy , -- IncentiveType - nvarchar(1)
1002 ApplyTo , -- ApplyTo - nvarchar(1)
1003 ApplicableTo , -- ApplicableTo - nvarchar(1)
1004 StartRegisterDate , -- StartRegisterDate - date
1005 EndRegisterDate , -- EndRegisterDate - date
1006 StartExecutionDate , -- StartExecutionDate - date
1007 EndExecutionDate , -- EndExecutionDate - date
1008 RegisterType , -- RegisterType - nvarchar(1)
1009 EffectiveDateType , -- EffectiveDateType - nvarchar(1)
1010 ISNULL(IsAccumulate, 0) , -- IsAccumulate - int
1011 ISNULL(AccumulateBy, '') , -- AccumulateBy - nvarchar(1)
1012 ISNULL(IsRatio, 0) , -- IsRatio - int
1013 ISNULL(RatioBy, '') -- RatioBy - nvarchar(1)
1014 FROM PNCDMS..DMSProgram
1015 CROSS JOIN dbo.DMSAimEmployeeList e
1016 WHERE Status = 'R'
1017 AND CompanyID = @CompanyID
1018 AND ( ( DATEDIFF(dd, StartRegisterDate, GETDATE()) >= 0
1019 AND DATEDIFF(DD, EndRegisterDate, GETDATE()) <= 0
1020 )
1021 OR ( DATEDIFF(dd, StartExecutionDate, GETDATE()) >= 0
1022 AND DATEDIFF(DD, EndExecutionDate,
1023 GETDATE()) <= 0
1024 )
1025 );
1026
1027
1028 INSERT INTO dbo.DMSAimTMKTProgramApplicableTo
1029 ( UserName ,
1030 ProgramCD ,
1031 GroupCD
1032 )
1033 SELECT m.UserName , -- UserName - nvarchar(50)
1034 p.ProgramCD , -- ProgramCD - nvarchar(50)
1035 h.GroupCD -- GroupCD - nvarchar(50)
1036 FROM PNCDMS..DMSProgramApplicableTo t
1037 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = t.CompanyID
1038 AND t.ProgramID = p.ProgramID
1039 AND t.CompanyID = @CompanyID
1040 INNER JOIN PNCDMS..DMSCustomerGroupHeader h ON h.CompanyID = t.CompanyID
1041 AND t.CustomerGroupID = h.GroupID
1042 INNER JOIN dbo.DMSAimTMKTProgram m ON m.ProgramCD = p.ProgramCD;
1043
1044 INSERT INTO dbo.DMSAimTMKTProgramApplyTo
1045 ( UserName ,
1046 ProgramCD ,
1047 ApplyToCD ,
1048 Type
1049 )
1050 SELECT m.UserName , -- UserName - nvarchar(50)
1051 p.ProgramCD , -- ProgramCD - nvarchar(50)
1052 t.ApplyToID , -- ApplyToCD - nvarchar(50)
1053 t.Type -- Type - nvarchar(1)
1054 FROM PNCDMS..DMSProgramApplyTo t
1055 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = t.CompanyID
1056 AND t.ProgramID = p.ProgramID
1057 AND t.CompanyID = @CompanyID
1058 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1059
1060
1061
1062 INSERT INTO dbo.DMSAimTMKProgramBreakDownDetail
1063 ( UserName ,
1064 ProgramCD ,
1065 BreakByLevelID ,
1066 BreakByLevelName ,
1067 MinAmtRegister ,
1068 DisplayClass ,
1069 Revenue ,
1070 IncentivePercent ,
1071 IncentiveAmt ,
1072 Weight,
1073 Volume,
1074 Quantity
1075 )
1076 SELECT m.UserName , -- UserName - nvarchar(50)
1077 p.ProgramCD , -- ProgramCD - nvarchar(50)
1078 b.BreakByLevelID , -- BreakByLevelID - int
1079 b.BreakByLevelName , -- BreakByLevelName - nvarchar(255)
1080 0 , -- MinAmtRegister - decimal
1081 DisplayClass , -- DisplayClass - nvarchar(50)
1082 ISNULL(b.Revenue, 0) , -- Revenue - decimal
1083 b.IncentivePercent , -- IncentivePercent - decimal
1084 b.IncentiveAmt , -- IncentiveAmt - decimal
1085 ISNULL(b.Weight, 0), -- Weight - int
1086 ISNULL(b.Volume, 0),
1087 ISNULL(b.Quantity, 0)
1088
1089 FROM PNCDMS..DMSProgramBreakDownDetail b
1090 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = b.CompanyID
1091 AND p.ProgramID = b.ProgramID
1092 AND b.CompanyID = @CompanyID
1093 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1094
1095 INSERT INTO dbo.DMSAimTMKTProgramFreeItem
1096 ( UserName ,
1097 ProgramCD ,
1098 BreakByLevelID ,
1099 InventoryCD ,
1100 Qty
1101 )
1102 SELECT m.UserName , -- UserName - nvarchar(50)
1103 p.ProgramCD , -- ProgramCD - nvarchar(50)
1104 f.BreakByLevelID , -- BreakByLevelID - int
1105 l.InventoryCD , -- InventoryCD - nvarchar(50)
1106 f.Qty -- Qty - decimal
1107 FROM PNCDMS..DMSProgramFreeItem f
1108 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = f.CompanyID
1109 AND p.ProgramID = f.ProgramID
1110 AND f.CompanyID = @CompanyID
1111 INNER JOIN dbo.DMSAimInventoryList l ON f.InventoryID = l.InventoryID
1112 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1113
1114 INSERT INTO dbo.DMSAimTMKTProgramRatioProduct
1115 ( UserName ,
1116 ProgramCD ,
1117 BreakByLevelID ,
1118 LineNbr ,
1119 InventoryCD ,
1120 IsGroup ,
1121 GroupItemCD ,
1122 Operators ,
1123 Ratio ,
1124 Target ,
1125 Qty
1126 )
1127 SELECT m.UserName , -- UserName - nvarchar(50)
1128 p.ProgramCD , -- ProgramCD - nvarchar(50)
1129 r.BreakByLevelID , -- BreakByLevelID - int
1130 ISNULL(r.LineNbr, 0) , -- LineNbr - int
1131 ISNULL(l.InventoryCD,'') , -- InventoryCD - nvarchar(50)
1132 ISNULL(r.IsGroup, 0) , -- IsGroup - int
1133 ISNULL(r.GroupItemID, '') , -- GroupItemCD - nvarchar(50)
1134 ISNULL(r.Operators, '') , -- Operators - nvarchar(2)
1135 r.[Percent] , -- Ratio - decimal
1136 r.Amount , -- Target - decimal
1137 r.Qty -- Qty - decimal
1138 FROM PNCDMS..DMSProgramRatioProduct r
1139 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = r.CompanyID
1140 AND p.ProgramID = r.ProgramID
1141 AND r.CompanyID = @CompanyID
1142 --INNER JOIN dbo.DMSAimInventoryList l ON r.InventoryID = l.InventoryID
1143 LEFT JOIN dbo.DMSAimInventoryList l ON r.InventoryID = l.InventoryID
1144 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1145
1146 INSERT INTO dbo.DMSAimTMKTProgramRegister
1147 ( UserName ,
1148 ProgramCD ,
1149 CustomerCD ,
1150 LocationCD ,
1151 LevelID ,
1152 RegisterDate ,
1153 RegisterAmt ,
1154 ResultImage ,
1155 IsRegister ,
1156 Status ,
1157 ReasonID ,
1158 Times
1159 )
1160 SELECT DISTINCT
1161 a.UserName , -- UserName - nvarchar(50)
1162 p.ProgramCD , -- ProgramCD - nvarchar(50)
1163 c.CustomerCD , -- CustomerCD - nvarchar(50)
1164 c.LocationCD , -- LocationCD - nvarchar(50)
1165 r.LevelID , -- LevelID - int
1166 ISNULL(r.RegisterDate, '2016-01-01') , -- RegisterDate - date
1167 r.RegisterAmt , -- RegisterAmt - decimal
1168 r.ResultImage , -- ResultImage - nvarchar(1)
1169 r.IsRegister , -- IsRegister - int
1170 r.Status , -- Status - int
1171 r.ReasonID , -- ReasonID - int
1172 r.Times -- Times - int
1173 FROM PNCDMS..DMSProgramRegister r
1174 INNER JOIN PNCDMS..DMSProgram p ON p.CompanyID = r.CompanyID
1175 AND p.ProgramID = r.ProgramID
1176 AND r.CompanyID = @CompanyID
1177 INNER JOIN dbo.DMSAllCustomer c ON r.CustomerID = c.LocationID
1178 INNER JOIN dbo.DMSAimCustomer a ON a.CustomerCD = c.CustomerCD
1179 AND a.LocationCD = c.LocationCD
1180 INNER JOIN dbo.DMSAimTMKTProgram m ON p.ProgramCD = m.ProgramCD;
1181 --AND r.Status = 'A'
1182
1183
1184 INSERT INTO dbo.DMSAimTMKTProgramPlanogram
1185 ( UserName ,
1186 RefNbr ,
1187 Descr ,
1188 Type ,
1189 IsProgram ,
1190 ProgramCD ,
1191 Stage ,
1192 Status ,
1193 StartDate ,
1194 EndDate
1195 )
1196 SELECT DISTINCT
1197 e.EmployeeICD , -- UserName - nvarchar(50)
1198 p.RefNbr , -- RefNbr - nvarchar(50)
1199 p.Descr , -- Descr - nvarchar(255)
1200 p.Type , -- Type - nvarchar(1)
1201 p.IsProgram , -- IsProgram - int
1202 ISNULL(d.ProgramCD, '') , -- ProgramCD - nvarchar(50)
1203 p.Stage , -- Stage - nvarchar(1)
1204 p.Status , -- Status - nvarchar(1)
1205 p.StartDate , -- StartDate - date
1206 p.EndDate -- EndDate - date
1207 FROM PNCDMS..DMSProgramPlanogram p
1208 LEFT JOIN PNCDMS..DMSProgram d ON p.CompanyID = d.CompanyID
1209 AND p.ProgramID = d.ProgramID
1210 CROSS JOIN dbo.DMSAimEmployeeList e
1211 WHERE p.Status = 'R'
1212 AND p.CompanyID = @CompanyID
1213 AND DATEDIFF(dd, p.StartDate, GETDATE()) >= 0
1214 AND DATEDIFF(DD, p.EndDate, GETDATE()) <= 0;
1215
1216 INSERT INTO dbo.DMSAimTMKTProgramPlanogramDetail
1217 ( UserName ,
1218 RefNbr ,
1219 BreakByLevelID ,
1220 ShopFormat ,
1221 LineNBR
1222 )
1223 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1224 d.RefNbr , -- RefNbr - nvarchar(50)
1225 d.BreakByLevelID , -- BreakByLevelID - int
1226 d.ShopFormat , -- ShopFormat - nvarchar(50)
1227 d.LineNbr -- LineNBR - int
1228 FROM PNCDMS..DMSProgramPlanogramDetail d
1229 INNER JOIN PNCDMS..DMSProgramPlanogram p ON p.CompanyID = d.CompanyID
1230 AND p.RefNbr = d.RefNbr
1231 CROSS JOIN dbo.DMSAimEmployeeList e
1232 WHERE p.Status = 'R'
1233 AND p.CompanyID = @CompanyID;
1234
1235 INSERT INTO dbo.DMSAimTMKTProgramPlanogramDetailImage
1236 ( UserName ,
1237 RefNbr ,
1238 LineNBR ,
1239 ImageID ,
1240 ImageName ,
1241 Descr
1242 )
1243 SELECT e.EmployeeICD , -- UserName - nvarchar(50)
1244 i.RefNbr , -- RefNbr - nvarchar(50)
1245 i.LineNbr , -- LineNBR - int
1246 i.ImageID , -- ImageID - int
1247 i.ImageName , -- ImageName - nvarchar(255)
1248 i.Descr -- Descr - nvarchar(255)
1249 FROM PNCDMS..DMSProgramPlanogramDetailImage i
1250 INNER JOIN PNCDMS..DMSProgramPlanogram p ON p.CompanyID = i.CompanyID
1251 AND p.RefNbr = i.RefNbr
1252 CROSS JOIN dbo.DMSAimEmployeeList e
1253 WHERE p.Status = 'R'
1254 AND i.IsActive = 1
1255 AND p.CompanyID = @CompanyID;
1256
1257 -- Lay nhom SP TMKT
1258 INSERT INTO dbo.DMSAimTMKTPROItemsGroupHeader
1259 ( UserName ,
1260 GroupCD ,
1261 Status ,
1262 Description
1263 )
1264 SELECT DISTINCT
1265 e.EmployeeICD ,
1266 GroupID ,-- GroupCD - nvarchar(50)
1267 g.Status ,
1268 Description -- Desc - nvarchar(100)
1269 FROM PNCDMS..DMSPROItemsGroupHeader g
1270 CROSS JOIN dbo.DMSAimEmployeeList e
1271 WHERE Status = 'R'
1272 AND g.CompanyID = @CompanyID;
1273
1274 INSERT INTO dbo.DMSAimTMKTPROItemsGroupDetail
1275 ( UserName ,
1276 GroupCD ,
1277 InventoryCD ,
1278 Rate
1279 )
1280 SELECT e.EmployeeICD ,
1281 d.GroupID , -- GroupID - int
1282 i.InventoryCD , -- InventoryID - int
1283 ISNULL(d.Rate, 0) AS Rate -- Rate - Decimal 18,2
1284 FROM PNCDMS..DMSPROItemsGroupDetail d
1285 INNER JOIN dbo.DMSAimInventoryList i ON d.InventoryID = i.InventoryID
1286 AND d.CompanyID = @CompanyID
1287 INNER JOIN PNCDMS..DMSPROItemsGroupHeader g ON d.CompanyID = g.CompanyID
1288 AND d.GroupID = g.GroupID
1289 CROSS JOIN dbo.DMSAimEmployeeList e
1290 WHERE d.Active = 1
1291 AND g.Status = 'R';
1292
1293 --INSERT INTO dbo.DMSAimCustomerMTDProgram
1294 -- ( UserName ,
1295 -- CustomerCD ,
1296 -- LocationCD ,
1297 -- ProgramCD ,
1298 -- MTDQty ,
1299 -- MTDAmount ,
1300 -- MTDWeight ,
1301 -- MTDVolume
1302 -- )
1303 -- SELECT c.UserName , -- UserName - nvarchar(50)
1304 -- c.CustomerCD , -- CustomerCD - nvarchar(50)
1305 -- c.LocationCD , -- LocationCD - nvarchar(50)
1306 -- b.ProgramCD , -- ProgramCD - nvarchar(50)
1307 -- SUM(b.InventoryQty) , -- MTDQty - int
1308 -- SUM(b.InventoryRevenue) , -- MTDAmount - float
1309 -- 0.0 , -- MTDWeight - float
1310 -- 0.0 -- MTDVolume - float
1311 -- FROM dbo.DMSAimCustomer c
1312 -- INNER JOIN dbo.DMSAllCustomer a ON c.CustomerCD = a.CustomerCD
1313 -- AND c.LocationCD = a.LocationCD
1314 -- INNER JOIN PNCBaseline..DMSBLProgramTracking b ON a.BaccountID = b.CustomerID
1315 -- AND a.LocationID = b.CustomerLocationID
1316 -- AND b.CompanyID = @CompanyID
1317 -- AND b.Month = MONTH(GETDATE())
1318 -- AND b.Year = YEAR(GETDATE())
1319 -- GROUP BY c.UserName , -- UserName - nvarchar(50)
1320 -- c.CustomerCD , -- CustomerCD - nvarchar(50)
1321 -- c.LocationCD , -- LocationCD - nvarchar(50)
1322 -- b.ProgramCD;
1323 END;
1324
1325
1326
1327GO
1328
1329GO