· 8 years ago · Dec 27, 2017, 10:08 AM
1USE [PNCeTools]
2GO
3
4/****** Object: StoredProcedure [dbo].[pp_Sync_Distributor] Script Date: 12/27/2017 5:05:48 PM ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11--EXEC [pp_Sync_Distributor]
12--DELETE FROM Distributor
13ALTER PROCEDURE [dbo].[pp_Sync_Distributor]
14AS
15BEGIN
16DECLARE @CompanyID INT = (SELECT TOP 1 [SettingValue] FROM [dbo].[CustomSetting] WHERE SettingCode = 'CompanyID')
17
18--TRUNCATE TABLE Distributor
19--CREATE TEMP TABLE STRUCT #Export = dbo.Distributor
20SELECT * INTO
21 #Export
22FROM
23 dbo.Distributor
24WHERE
25 1 = 0
26--------- GETDATA TEM IN AMUCATICA
27
28SELECT DISTINCT
29 DistributorID = D.LocationID,
30 DistributorCode = D.DistributorCD,
31 --DistributorName = CASE WHEN D.LocationCD LIKE 'KL%'
32 -- THEN REPLACE(CONVERT(NVARCHAR(250),LTRIM(RTRIM(D.Descr))), CONVERT(NVARCHAR(250),LTRIM(RTRIM(D.DistributorCD)) + ' - '), N'')
33 -- ELSE D.DistributorFullName
34 -- END,
35 DistributorName = UPPER(D.Descr),
36 Active = D.IsActive,
37 --SRArea = SR.ValueCD,
38 RegionID = R.ValueCD,
39 AreaID = A.ValueCD, --ProvinceID,--
40
41 ProvinceID = ProvinceID,
42 LoginID = DistributorCD,
43 Email = '',
44 Address = UPPER(AddressLine1),
45 Phone1 = ISNULL(D.DistributorPhone1,''),
46 Phone2 = ISNULL(D.DistributorPhone2,''),
47 [Distributor_OldID] = D.DistributorID,
48 [Distributor_OldCD] = D.DistributorCD
49INTO #Distributor
50FROM
51 [PNCDMS].[dbo].DMSViewDistributorLocation D WITH (NOLOCK)
52 JOIN [PNCDMS].[dbo].DMSSalesOrgValue SR WITH (NOLOCK)
53 ON SR.CompanyID = D.CompanyID
54 AND SR.ValueID = D.SalesAreaID
55 JOIN [PNCDMS].[dbo].DMSSalesOrgValue A WITH (NOLOCK)
56 ON A.CompanyID = SR.CompanyID
57 AND A.ValueID = SR.ParentID
58 JOIN [PNCDMS].[dbo].DMSSalesOrgValue R WITH (NOLOCK)
59 ON R.CompanyID = A.CompanyID
60 AND R.ValueID = A.ParentID
61WHERE
62 D.CompanyID = @CompanyID
63 --AND DistributorType = 'DL'
64 --(D.CompanyID = @CompanyID AND DistributorType = 'DL')
65 --OR (D.CompanyID = @CompanyID AND D.DistributorID = 1 AND R.ValueCD = 'MB' AND A.ValueCD = 'HN1')
66
67--SELECT
68-- ba.BAccountID AS [DistributorID],
69-- ba.UsrDMSCode AS [DistributorCode],
70-- ba.AcctName as [DistributorName] ,
71-- Active = CASE WHEN ba.[Status] = 'A' THEN 1 ELSE 0 END,
72-- --addr.UsrRegionID as RegionID,
73-- R.RegionCD as RegionID,
74-- addr.UsrProvinceID as AreaID,
75-- addr.UsrProvinceID as ProvinceID,
76-- ba.UsrDMSCode as LoginID,
77-- ct.EMail as Email,
78-- addr.AddressLine1 + N' ' + addr.AddressLine2 AS [Address],
79-- ct.Phone1 as Phone1,
80-- ct.Phone2 as Phone2
81-- into #Distributor
82--FROM [PNCDMS].dbo.BAccount as ba
83--left join [PNCDMS].dbo.Address as addr on addr.CompanyID = ba.CompanyID and ba.BAccountID = addr.BAccountID and
84-- addr.AddressID = ba.DefAddressID
85--INNER JOIN [PNCDMS].dbo.Contact as ct WITH (NOLOCK)
86-- ON ba.CompanyID = ct.CompanyID
87-- AND ba.BAccountID = ct.BAccountID
88-- AND ba.DefContactID = ct.ContactID
89--JOIN PNCDMS.[dbo].[DMSRegion] R WITH (NOLOCK)
90-- ON addr.UsrRegionID = R.RegionID
91--WHERE ba.CompanyID > 0 and UsrDMSCode is NOT NULL
92--and ba.Type = 'CP'
93
94--- SELECT UPDATED OR NOT INSERTED FROM ACU TO ERoute
95INSERT INTO #Export
96 (
97 [DistributorID] ,
98 [DistributorCode] ,
99 [DistributorName] ,
100 --CompanyType ,
101 Active ,
102 RegionID ,
103 AreaID ,
104 ProvinceID ,
105 LoginID,
106 Email,
107 [Address],
108 Phone1,
109 Phone2,
110 [Distributor_OldID],
111 [Distributor_OldCD]
112 )
113 (
114 select * from #Distributor
115
116 )
117
118--Synchronize the target table with
119--refreshed data from source table
120MERGE dbo.Distributor AS TARGET
121USING #Export AS SOURCE
122 ON TARGET.[DistributorID] = SOURCE.[DistributorID]
123 AND TARGET.[DistributorCode] = SOURCE.[DistributorCode]
124 AND TARGET.[LoginID] = SOURCE.[LoginID]
125--When records are matched, update
126--the records if there is any change
127WHEN MATCHED AND
128 TARGET.[DistributorName] <> SOURCE.[DistributorName]
129 --OR TARGET.[CompanyType] <> SOURCE.[CompanyType]
130 OR TARGET.[Active] <> SOURCE.[Active]
131 OR TARGET.[RegionID] <> SOURCE.[RegionID]
132 OR TARGET.[AreaID] <> SOURCE.[AreaID]
133 OR TARGET.[ProvinceID] <> SOURCE.[ProvinceID]
134 OR ISNULL(TARGET.[Email],'') <> ISNULL(SOURCE.[Email],'')
135 OR ISNULL(TARGET.[Address],'') <> ISNULL(SOURCE.[Address],'')
136 OR ISNULL(TARGET.Phone1,'') <> ISNULL(SOURCE.Phone1,'')
137 OR ISNULL(TARGET.Phone2,'') <> ISNULL(SOURCE.Phone2,'')
138 OR ISNULL(TARGET.[Distributor_OldID],0) <> ISNULL(SOURCE.[Distributor_OldID],0)
139 OR ISNULL(TARGET.[Distributor_OldCD],'') <> ISNULL(SOURCE.[Distributor_OldCD],'')
140 THEN
141 UPDATE SET
142 TARGET.[DistributorName] = SOURCE.[DistributorName],
143 --TARGET.[CompanyType] = SOURCE.[CompanyType],
144 TARGET.[Active] = SOURCE.[Active],
145 TARGET.[RegionID] = SOURCE.[RegionID],
146 TARGET.[AreaID] = SOURCE.[AreaID],
147 TARGET.[ProvinceID] = SOURCE.[ProvinceID],
148 TARGET.[Email] = SOURCE.[Email],
149 TARGET.[Address] = SOURCE.[Address],
150 TARGET.Phone1 = SOURCE.Phone1,
151 TARGET.Phone2 = SOURCE.Phone2,
152 TARGET.[Distributor_OldID] = SOURCE.[Distributor_OldID],
153 TARGET.[Distributor_OldCD] = SOURCE.[Distributor_OldCD]
154--When no records are matched, insert
155--the incoming records from source
156--table to target table
157WHEN NOT MATCHED BY TARGET
158 THEN
159 INSERT (
160 [DistributorID] ,
161 [DistributorCode] ,
162 [DistributorName] ,
163 --CompanyType ,
164 [Active],
165 [RegionID],
166 [AreaID],
167 [ProvinceID],
168 [LoginID],
169 [Email],
170 [Address],
171 Phone1,
172 Phone2,
173 [Distributor_OldID],
174 [Distributor_OldCD]
175 )
176 VALUES (
177 SOURCE.[DistributorID],
178 SOURCE.[DistributorCode],
179 SOURCE.[DistributorName],
180 --SOURCE.[CompanyType],
181 SOURCE.[Active],
182 SOURCE.[RegionID],
183 SOURCE.[AreaID],
184 SOURCE.[ProvinceID],
185 SOURCE.[LoginID],
186 SOURCE.[Email],
187 SOURCE.[Address],
188 SOURCE.Phone1,
189 SOURCE.Phone2,
190 SOURCE.[Distributor_OldID],
191 SOURCE.[Distributor_OldCD]
192 )
193--When there is a row that exists in target table and
194--same record does not exist in source table
195--then delete this record from target table
196WHEN NOT MATCHED BY SOURCE
197 THEN
198 DELETE
199;
200SELECT @@ROWCOUNT
201
202
203--DROP TABLE #Export
204DROP TABLE #Export
205END
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228GO