· 4 years ago · Jul 20, 2021, 10:16 AM
1IF EXISTS ( SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('OpsProd.usp_LoadFactTrackMailItem') AND type in (N'P') )
2 DROP PROC [OpsProd].[usp_LoadFactTrackMailItem];
3 GO
4
5CREATE PROC [OpsProd].[usp_LoadFactTrackMailItem] AS
6BEGIN
7
8 IF OBJECT_ID('CTAS.FACT_TRACK_MAIL_ITEM', 'U') IS NOT NULL
9 DROP TABLE CTAS.FACT_TRACK_MAIL_ITEM
10
11 CREATE TABLE CTAS.FACT_TRACK_MAIL_ITEM
12 WITH (DISTRIBUTION = HASH ([TMI_UID])
13 ,CLUSTERED COLUMNSTORE INDEX
14 ) AS
15 SELECT
16 ET.[TMI_UID]
17 ,ET.[DELIVERYCONFIRMATIONNUMBER]
18 ,ET.[CUSTOMERCONFIRMATIONNUMBER]
19 ,ET.[WEIGHT]
20 ,ET.[CUSTOMERREFERENCE]
21 ,ET.[BATCHREFERENCE]
22 ,ET.[MARKUPREASONCODE]
23 ,ET.[SIGNATURECONFIRMATIONFLAG]
24 ,ET.[ENTRYUNIT]
25 ,ET.[ENTRYLEVEL]
26 ,ET.[MANIFESTEDDSPPRODUCT]
27 ,ET.[RECEIVEDATE]
28 ,ET.[MANIFESTDATE]
29 ,ET.[ENCODEDATE]
30 ,ET.[PICKUP]
31 ,ET.[STOPCLOCKDATE]
32 ,ET.[STOPCLOCKEVENT]
33 ,ET.[STOPCLOCKZIP]
34 ,ET.[LASTEVENT_ID]
35 ,ET.[LASTEVENTDATE]
36 ,ET.[LASTEVENTZIP]
37 ,ET.[VENDOREVENT_ID]
38 ,ET.[VENDOREVENT_DATE]
39 ,ET.[VENDOREVENT_ZIP]
40 ,ET.[ZIP5]
41 ,ET.[ZIP3]
42 ,ET.[ORIGINFACILITYCODE]
43 ,ET.[DESTINATIONFACILITYCODE]
44 ,ET.[CONSIGNMENTNOTENUMBER]
45 ,ET.[JOBNUMBER]
46 ,ET.[WORKORDER]
47 ,ET.[PRODUCT_ID]
48 ,ET.[PERF_D]
49 ,ET.[PERF_C]
50 ,ET.[PERF_B]
51 ,ET.[PERF_M]
52 ,ET.[PERF_S]
53 ,ET.[PERF_V]
54 ,ET.[CYCLE_TIME]
55 ,ET.[SACK_SORT_LEVEL]
56 ,ET.[SACK_BARCODE]
57 ,ET.[SACK_BARCODE_TYPE]
58 ,ET.[ROUTING_ID]
59 ,ET.[ROUTING_NAME]
60 ,ET.[ENTRYRATECODE]
61 ,ET.[ENTRYRATECODE_EVS]
62 ,ET.[POSTAGE_ZONE]
63 ,ET.[BEDEBARCODE1]
64 ,ET.[DELIVERYCONFIRMATIONFLAG]
65 ,ET.[DSP_ID]
66 ,ET.[ENCLOSUREPOSTAGE]
67 ,ET.[ENCODING_STATION]
68 ,ET.[ENCODING_USER]
69 ,ET.[IMBCODE]
70 ,ET.[IMBFLAG]
71 ,ET.[MOVEUPDATEHANDLING]
72 ,ET.[OVERLABELEDIMBCODE]
73 ,ET.[QUICKNETMAILITEMFLAG]
74 ,ET.[REENCODEFLAG]
75 ,ET.[SURCHARGE]
76 ,ET.[POSTAGE]
77 ,ET.[ACTUALRECIPIENTCOUNTRY]
78 ,ET.[DELETED]
79 ,ET.[CUSTOMERCONFIRMATIONFLAG]
80 ,ET.[VENDOREVENTSFLAG]
81 ,ET.[LASTEVENTCOUNTRY]
82 ,ET.[PROCESSABLEFLAG]
83 ,ET.[STOPCLOCKCOUNTRY]
84 ,ET.[EXPECTEDDELIVERY]
85 ,ET.[VENDOREVENT_COUNTRY]
86 ,ET.[PERF_L]
87 ,ET.[PERF_LM]
88 ,ET.[ADDRESSIDFLAG]
89 ,ET.[ZIPCODECHANGEFLAG]
90 ,ET.[MAILTYPE]
91 ,ET.[DATACAPTUREMETHOD]
92 ,ET.[INTENDEDTRANSPORTMODE]
93 ,ET.[DSPMAILIDENTIFIER]
94 ,ET.[DSP_ACCOUNT_ID]
95 ,ET.[EXCEPTION_FLAG]
96 ,ET.[EXCEPTION_EVENT_ID]
97 ,ET.[EXCEPTION_DATE]
98 ,ET.[EXCEPTION_ZIP]
99 ,ET.[EXCEPTION_COUNTRY]
100 ,ET.[SORTCODE_OUTBOUND]
101 ,ET.[SORTCODE_INBOUND]
102 ,ET.[OVERLABEL_DC_EVS]
103 ,ET.[MAILIDENTIFIER]
104 ,ET.[LOCAL_RECEIVEDATE]
105 ,ET.[PERF_E]
106 ,ET.[DN_CONTAINER]
107 ,ET.[ACTUALRECIPIENTADDRESS3]
108 ,ET.[MANIFESTNUMBER]
109 ,ET.[DIMWEIGHT]
110 ,ET.[DIMHEIGHT]
111 ,ET.[DIMWIDTH]
112 ,ET.[DIMLENGTH]
113 ,ET.[CUSTOMERCONFIRMATIONNUMBER2]
114 ,ET.[GATEWAY]
115 ,ET.[MAILCONTENTCATEGORY]
116 ,ET.[DECLAREDVALUE]
117 ,ET.[INCOTERM]
118 ,ET.[ROUTINGFORMAT]
119 ,ET.[INTENDEDFACILITYCODE]
120 ,ET.[OVERLABELREASONID]
121 ,ET.[INTERNAL_RECEIVEDATE]
122 ,ET.[SOURCE_SYSTEM]
123 ,ET.[ORIGINFACILITYCODE_CONNECT]
124 ,ET.[POSTAGE_CONNECT]
125 ,ET.[POSTAGE_CURRENCY]
126 ,ET.[ROUTING_ID_CONNECT]
127 ,ET.[ROUTING_NAME_CONNECT]
128 ,ET.[WEIGHT_CONNECT]
129 ,ET.[BILLABLEWEIGHT_CONNECT]
130 ,ET.[MANIFESTEDDSPPRODUCT_CONNECT]
131 ,ET.[ENCODINGSESSIONID]
132 ,ET.[COD_REMIT_TRACKINGNUMBER]
133 ,ET.[COD_CURRENCY]
134 ,ET.[COD_AMOUNT]
135 ,ET.[ORIGINAL_ORDERED_PRODUCTCODE]
136 ,ET.[SECURITY_SCREEN]
137 ,ET.[BILLABLEWEIGHT]
138 ,ET.[DN_SHIPDATE]
139 ,ET.[DN_RECEIVEDATE]
140 ,ET.[ENCODEDATE_CONNECT]
141 ,ET.[SIGNED_FOR_NAME]
142 ,ET.[LASTEVENT_TIMEZONE]
143 ,ET.[STOPCLOCK_TIMEZONE]
144 ,ET.[VENDOREVENT_TIMEZONE]
145 ,ET.[EXCEPTION_TIMEZONE]
146 ,ET.[CONSIGNMENTNOTENUMBER2]
147 ,ET.[AMP_STATUS]
148 ,ET.[ACTUALRECIPIENTADDRESS1]
149 ,ET.[ACTUALRECIPIENTZIP4]
150 ,ET.[DELIVERY_POINT]
151 ,ET.[ACTUALRECIPIENTADDRESS2]
152 ,ET.[ACTUALRECIPIENTCITY]
153 ,ET.[ACTUALRECIPIENTSTATE]
154 ,ET.[ACTUALRECIPIENTZIP]
155 ,ET.[CONTENT_CATEGORY_ID]
156 ,ET.[CONTAINER_TYPE]
157 ,ET.[CONTAINER_BARCODE]
158 ,ET.[SCAN2SACKDATE]
159 ,ET.[DAYDEFINITEDELIVERY]
160 ,ET.[MAPPED_DSP_ID]
161 ,ET.[LAST_SECONDARY_EVENT_ID]
162 ,ET.[SCREENER_NAME]
163 ,ET.[SCREENING_METHOD]
164 ,ET.[SCREENING_DATETIME]
165 ,ET.[SCREENING_STATUS]
166 ,ET.[AWB]
167 ,ET.[CARRIER]
168 ,ET.[INBOUND_ACCEPTANCE]
169 ,ET.[OPS_DIM_FLAG]
170 ,ET.[RETURNTYPE]
171 ,ET.[ORIGINAL_MAILIDENTIFIER]
172 ,ET.[RETURN_REASON]
173 ,ET.[BUNDLE_MAILIDENTIFIER]
174 ,ET.[SUCCESSSTOPCLOCKEVENT]
175 ,ET.[SUCCESSSTOPCLOCKZIP]
176 ,ET.[SUCCESSSTOPCLOCKDATE]
177 ,ET.[SUCCESSSTOPCLOCKCOUNTRY]
178 ,ET.[SUCCESSSSTOPCLOCKTIMEZONE]
179 ,ET.[INITIALTRANSPORTEVENT]
180 ,ET.[INITIALTRANSPORTZIP]
181 ,ET.[INITIALTRANSPORTDATE]
182 ,ET.[INITIALTRANSPORTCOUNTRY]
183 ,ET.[INITIALTRANSPORTTIMEZONE]
184 ,ET.[FINALTRANSPORTEVENT]
185 ,ET.[FINALTRANSPORTZIP]
186 ,ET.[FINALTRANSPORTDATE]
187 ,ET.[FINALTRANSPORTCOUNTRY]
188 ,ET.[FINALTRANSPORTTIMEZONE]
189 ,ET.[CUSTOMSCOMPLETEEVENT]
190 ,ET.[CUSTOMSCOMPLETEZIP]
191 ,ET.[CUSTOMSCOMPLETEDATE]
192 ,ET.[CUSTOMSCOMPLETECOUNTRY]
193 ,ET.[CUSTOMSCOMPLETETIMEZONE]
194 ,ET.[RURAL_ZIP]
195 ,ET.[SORT_CODE_UPDATED]
196 ,ET.[DECLARED_VALUE_CURR]
197 ,ET.[U1_EVENT_DATE]
198 ,ET.[PERF_U]
199 ,ET.[PICKUP_CONNECT]
200 ,ET.[DIMWEIGHT_CONNECT]
201 ,ET.[DIMHEIGHT_CONNECT]
202 ,ET.[DIMWIDTH_CONNECT]
203 ,ET.[DIMLENGTH_CONNECT]
204 ,ET.[HOLD_REASON]
205 ,ET.[DIM_LAST_DATE]
206 ,ET.[XTREME_SE_FIRST_DATE]
207 ,ET.[XTREME_SE_FIRST_FACILITY]
208 ,ET.[XTREME_SE_LAST_DATE]
209 ,ET.[XTREME_SE_LAST_FACILITY]
210 ,ET.[CUBOIDAL_FLAG]
211 ,ET.[CONTAINER_REF1]
212 ,ET.[CONTAINER_REF2]
213 ,ET.[DAYDEFINITEDELIVERY_ENC_STOP]
214 ,ET.[MESSAGE_GROUP_ID]
215 ,ET.[MESSAGE_CATEGORY]
216 ,ET.[PERF_IT]
217 ,ET.[PERF_FT]
218 ,ET.[PERF_CC]
219 ,ET.[PERF_SC]
220 ,ET.[PERF_SD]
221 ,ET.[PERF_T]
222 ,ET.[PERF_REC_ENC]
223 ,ET.[PERF_ENC_DNS]
224 ,ET.[PERF_DNS_DNR]
225 ,ET.[PERF_DNR_MAN]
226 ,ET.[PERF_ENC_MAN]
227 ,ET.[PERF_REC_MAN]
228 ,ET.[PERF_EDD]
229 ,ET.[EDD_T]
230 ,ET.[ENCODING_STATION_CONNECT]
231 ,ET.[ENCODING_USER_CONNECT]
232 ,ET.[DATA_CAPTURED_METHOD_CONNECT]
233 ,ET.[LOGICAL_RECEIVEDATE]
234 ,ET.[STARTCLOCK_FACILITYCODE]
235 ,ET.[PERF_RFD]
236 ,ET.[CONSIGNEE_ID_NUMBER]
237 ,ET.[CONSIGNEE_ID_TYPE]
238 ,ET.[PICKUP2]
239 ,ET.[LAST_OBTAINED_LATITUDE]
240 ,ET.[LAST_OBTAINED_LONGITUDE]
241 ,ET.[TRANSIT_ODC_DDC]
242 ,ET.[TRANSIT_DDC_FVE]
243 ,ET.[DIM_SOURCE]
244 ,ET.[BILLED_ON]
245 ,ET.[PROCESSED_ON]
246 --,A.SOURCE_SYSTEM AS
247 ,ALERT_SOURCE_SYSTEM
248 FROM [ext-OpsProd].vw_TrackMailItem ET
249 OPTION (LABEL = 'elt_user')
250
251
252 WITH RECORDS (
253 SELECT ISNULL([OpsProd].[FactTrackMailItem].[TMI_UID], "NEW") [STATUS],
254 [CTAS].[FACT_TRACK_MAIL_ITEM].*
255 FROM [CTAS].[FACT_TRACK_MAIL_ITEM]
256 LEFT JOIN [OpsProd].[FactTrackMailItem].[TMI_UID] = ET.[TMI_UID]
257 WHERE [CTAS].[FACT_TRACK_MAIL_ITEM].[PROCESSED_ON] > [OpsProd].[FactTrackMailItem].[PROCESSED_ON]
258 OR [OpsProd].[FactTrackMailItem].[PROCESSED_ON] IS NULL
259 )
260
261 UPDATE [OpsProd].[FactTrackMailItem]
262 SET [DELIVERYCONFIRMATIONNUMBER] = ET.[DELIVERYCONFIRMATIONNUMBER]
263 ,[CUSTOMERCONFIRMATIONNUMBER] = ET.[CUSTOMERCONFIRMATIONNUMBER]
264 ,[WEIGHT] = ET.[WEIGHT]
265 ,[CUSTOMERREFERENCE] = ET.[CUSTOMERREFERENCE]
266 ,[BATCHREFERENCE] = ET.[BATCHREFERENCE]
267 ,[MARKUPREASONCODE] = ET.[MARKUPREASONCODE]
268 ,[SIGNATURECONFIRMATIONFLAG] = ET.[SIGNATURECONFIRMATIONFLAG]
269 ,[ENTRYUNIT] = ET.[ENTRYUNIT]
270 ,[ENTRYLEVEL] = ET.[ENTRYLEVEL]
271 ,[MANIFESTEDDSPPRODUCT] = ET.[MANIFESTEDDSPPRODUCT]
272 ,[RECEIVEDATE] = ET.[RECEIVEDATE]
273 ,[MANIFESTDATE] = ET.[MANIFESTDATE]
274 ,[ENCODEDATE] = ET.[ENCODEDATE]
275 ,[PICKUP] = ET.[PICKUP]
276 ,[STOPCLOCKDATE] = ET.[STOPCLOCKDATE]
277 ,[STOPCLOCKEVENT] = ET.[STOPCLOCKEVENT]
278 ,[STOPCLOCKZIP] = ET.[STOPCLOCKZIP]
279 ,[LASTEVENT_ID] = ET.[LASTEVENT_ID]
280 ,[LASTEVENTDATE] = ET.[LASTEVENTDATE]
281 ,[LASTEVENTZIP] = ET.[LASTEVENTZIP]
282 ,[VENDOREVENT_ID] = ET.[VENDOREVENT_ID]
283 ,[VENDOREVENT_DATE] = ET.[VENDOREVENT_DATE]
284 ,[VENDOREVENT_ZIP] = ET.[VENDOREVENT_ZIP]
285 ,[ZIP5] = ET.[ZIP5]
286 ,[ZIP3] = ET.[ZIP3]
287 ,[ORIGINFACILITYCODE] = ET.[ORIGINFACILITYCODE]
288 ,[DESTINATIONFACILITYCODE] = ET.[DESTINATIONFACILITYCODE]
289 ,[CONSIGNMENTNOTENUMBER] = ET.[CONSIGNMENTNOTENUMBER]
290 ,[JOBNUMBER] = ET.[JOBNUMBER]
291 ,[WORKORDER] = ET.[WORKORDER]
292 ,[PRODUCT_ID] = ET.[PRODUCT_ID]
293 ,[PERF_D] = ET.[PERF_D]
294 ,[PERF_C] = ET.[PERF_C]
295 ,[PERF_B] = ET.[PERF_B]
296 ,[PERF_M] = ET.[PERF_M]
297 ,[PERF_S] = ET.[PERF_S]
298 ,[PERF_V] = ET.[PERF_V]
299 ,[CYCLE_TIME] = ET.[CYCLE_TIME]
300 ,[SACK_SORT_LEVEL] = ET.[SACK_SORT_LEVEL]
301 ,[SACK_BARCODE] = ET.[SACK_BARCODE]
302 ,[SACK_BARCODE_TYPE] = ET.[SACK_BARCODE_TYPE]
303 ,[ROUTING_ID] = ET.[ROUTING_ID]
304 ,[ROUTING_NAME] = ET.[ROUTING_NAME]
305 ,[ENTRYRATECODE] = ET.[ENTRYRATECODE]
306 ,[ENTRYRATECODE_EVS] = ET.[ENTRYRATECODE_EVS]
307 ,[POSTAGE_ZONE] = ET.[POSTAGE_ZONE]
308 ,[BEDEBARCODE1] = ET.[BEDEBARCODE1]
309 ,[DELIVERYCONFIRMATIONFLAG] = ET.[DELIVERYCONFIRMATIONFLAG]
310 ,[DSP_ID] = ET.[DSP_ID]
311 ,[ENCLOSUREPOSTAGE] = ET.[ENCLOSUREPOSTAGE]
312 ,[ENCODING_STATION] = ET.[ENCODING_STATION]
313 ,[ENCODING_USER] = ET.[ENCODING_USER]
314 ,[IMBCODE] = ET.[IMBCODE]
315 ,[IMBFLAG] = ET.[IMBFLAG]
316 ,[MOVEUPDATEHANDLING] = ET.[MOVEUPDATEHANDLING]
317 ,[OVERLABELEDIMBCODE] = ET.[OVERLABELEDIMBCODE]
318 ,[QUICKNETMAILITEMFLAG] = ET.[QUICKNETMAILITEMFLAG]
319 ,[REENCODEFLAG] = ET.[REENCODEFLAG]
320 ,[SURCHARGE] = ET.[SURCHARGE]
321 ,[POSTAGE] = ET.[POSTAGE]
322 ,[ACTUALRECIPIENTCOUNTRY] = ET.[ACTUALRECIPIENTCOUNTRY]
323 ,[DELETED] = ET.[DELETED]
324 ,[CUSTOMERCONFIRMATIONFLAG] = ET.[CUSTOMERCONFIRMATIONFLAG]
325 ,[VENDOREVENTSFLAG] = ET.[VENDOREVENTSFLAG]
326 ,[LASTEVENTCOUNTRY] = ET.[LASTEVENTCOUNTRY]
327 ,[PROCESSABLEFLAG] = ET.[PROCESSABLEFLAG]
328 ,[STOPCLOCKCOUNTRY] = ET.[STOPCLOCKCOUNTRY]
329 ,[EXPECTEDDELIVERY] = ET.[EXPECTEDDELIVERY]
330 ,[VENDOREVENT_COUNTRY] = ET.[VENDOREVENT_COUNTRY]
331 ,[PERF_L] = ET.[PERF_L]
332 ,[PERF_LM] = ET.[PERF_LM]
333 ,[ADDRESSIDFLAG] = ET.[ADDRESSIDFLAG]
334 ,[ZIPCODECHANGEFLAG] = ET.[ZIPCODECHANGEFLAG]
335 ,[MAILTYPE] = ET.[MAILTYPE]
336 ,[DATACAPTUREMETHOD] = ET.[DATACAPTUREMETHOD]
337 ,[INTENDEDTRANSPORTMODE] = ET.[INTENDEDTRANSPORTMODE]
338 ,[DSPMAILIDENTIFIER] = ET.[DSPMAILIDENTIFIER]
339 ,[DSP_ACCOUNT_ID] = ET.[DSP_ACCOUNT_ID]
340 ,[EXCEPTION_FLAG] = ET.[EXCEPTION_FLAG]
341 ,[EXCEPTION_EVENT_ID] = ET.[EXCEPTION_EVENT_ID]
342 ,[EXCEPTION_DATE] = ET.[EXCEPTION_DATE]
343 ,[EXCEPTION_ZIP] = ET.[EXCEPTION_ZIP]
344 ,[EXCEPTION_COUNTRY] = ET.[EXCEPTION_COUNTRY]
345 ,[SORTCODE_OUTBOUND] = ET.[SORTCODE_OUTBOUND]
346 ,[SORTCODE_INBOUND] = ET.[SORTCODE_INBOUND]
347 ,[OVERLABEL_DC_EVS] = ET.[OVERLABEL_DC_EVS]
348 ,[MAILIDENTIFIER] = ET.[MAILIDENTIFIER]
349 ,[LOCAL_RECEIVEDATE] = ET.[LOCAL_RECEIVEDATE]
350 ,[PERF_E] = ET.[PERF_E]
351 ,[DN_CONTAINER] = ET.[DN_CONTAINER]
352 ,[ACTUALRECIPIENTADDRESS3] = ET.[ACTUALRECIPIENTADDRESS3]
353 ,[MANIFESTNUMBER] = ET.[MANIFESTNUMBER]
354 ,[DIMWEIGHT] = ET.[DIMWEIGHT]
355 ,[DIMHEIGHT] = ET.[DIMHEIGHT]
356 ,[DIMWIDTH] = ET.[DIMWIDTH]
357 ,[DIMLENGTH] = ET.[DIMLENGTH]
358 ,[CUSTOMERCONFIRMATIONNUMBER2] = ET.[CUSTOMERCONFIRMATIONNUMBER2]
359 ,[GATEWAY] = ET.[GATEWAY]
360 ,[MAILCONTENTCATEGORY] = ET.[MAILCONTENTCATEGORY]
361 ,[DECLAREDVALUE] = ET.[DECLAREDVALUE]
362 ,[INCOTERM] = ET.[INCOTERM]
363 ,[ROUTINGFORMAT] = ET.[ROUTINGFORMAT]
364 ,[INTENDEDFACILITYCODE] = ET.[INTENDEDFACILITYCODE]
365 ,[OVERLABELREASONID] = ET.[OVERLABELREASONID]
366 ,[INTERNAL_RECEIVEDATE] = ET.[INTERNAL_RECEIVEDATE]
367 ,[SOURCE_SYSTEM] = ET.[SOURCE_SYSTEM]
368 ,[ORIGINFACILITYCODE_CONNECT] = ET.[ORIGINFACILITYCODE_CONNECT]
369 ,[POSTAGE_CONNECT] = ET.[POSTAGE_CONNECT]
370 ,[POSTAGE_CURRENCY] = ET.[POSTAGE_CURRENCY]
371 ,[ROUTING_ID_CONNECT] = ET.[ROUTING_ID_CONNECT]
372 ,[ROUTING_NAME_CONNECT] = ET.[ROUTING_NAME_CONNECT]
373 ,[WEIGHT_CONNECT] = ET.[WEIGHT_CONNECT]
374 ,[BILLABLEWEIGHT_CONNECT] = ET.[BILLABLEWEIGHT_CONNECT]
375 ,[MANIFESTEDDSPPRODUCT_CONNECT] = ET.[MANIFESTEDDSPPRODUCT_CONNECT]
376 ,[ENCODINGSESSIONID] = ET.[ENCODINGSESSIONID]
377 ,[COD_REMIT_TRACKINGNUMBER] = ET.[COD_REMIT_TRACKINGNUMBER]
378 ,[COD_CURRENCY] = ET.[COD_CURRENCY]
379 ,[COD_AMOUNT] = ET.[COD_AMOUNT]
380 ,[ORIGINAL_ORDERED_PRODUCTCODE] = ET.[ORIGINAL_ORDERED_PRODUCTCODE]
381 ,[SECURITY_SCREEN] = ET.[SECURITY_SCREEN]
382 ,[BILLABLEWEIGHT] = ET.[BILLABLEWEIGHT]
383 ,[DN_SHIPDATE] = ET.[DN_SHIPDATE]
384 ,[DN_RECEIVEDATE] = ET.[DN_RECEIVEDATE]
385 ,[ENCODEDATE_CONNECT] = ET.[ENCODEDATE_CONNECT]
386 ,[SIGNED_FOR_NAME] = ET.[SIGNED_FOR_NAME]
387 ,[LASTEVENT_TIMEZONE] = ET.[LASTEVENT_TIMEZONE]
388 ,[STOPCLOCK_TIMEZONE] = ET.[STOPCLOCK_TIMEZONE]
389 ,[VENDOREVENT_TIMEZONE] = ET.[VENDOREVENT_TIMEZONE]
390 ,[EXCEPTION_TIMEZONE] = ET.[EXCEPTION_TIMEZONE]
391 ,[CONSIGNMENTNOTENUMBER2] = ET.[CONSIGNMENTNOTENUMBER2]
392 ,[AMP_STATUS] = ET.[AMP_STATUS]
393 ,[ACTUALRECIPIENTADDRESS1] = ET.[ACTUALRECIPIENTADDRESS1]
394 ,[ACTUALRECIPIENTZIP4] = ET.[ACTUALRECIPIENTZIP4]
395 ,[DELIVERY_POINT] = ET.[DELIVERY_POINT]
396 ,[ACTUALRECIPIENTADDRESS2] = ET.[ACTUALRECIPIENTADDRESS2]
397 ,[ACTUALRECIPIENTCITY] = ET.[ACTUALRECIPIENTCITY]
398 ,[ACTUALRECIPIENTSTATE] = ET.[ACTUALRECIPIENTSTATE]
399 ,[ACTUALRECIPIENTZIP] = ET.[ACTUALRECIPIENTZIP]
400 ,[CONTENT_CATEGORY_ID] = ET.[CONTENT_CATEGORY_ID]
401 ,[CONTAINER_TYPE] = ET.[CONTAINER_TYPE]
402 ,[CONTAINER_BARCODE] = ET.[CONTAINER_BARCODE]
403 ,[SCAN2SACKDATE] = ET.[SCAN2SACKDATE]
404 ,[DAYDEFINITEDELIVERY] = ET.[DAYDEFINITEDELIVERY]
405 ,[MAPPED_DSP_ID] = ET.[MAPPED_DSP_ID]
406 ,[LAST_SECONDARY_EVENT_ID] = ET.[LAST_SECONDARY_EVENT_ID]
407 ,[SCREENER_NAME] = ET.[SCREENER_NAME]
408 ,[SCREENING_METHOD] = ET.[SCREENING_METHOD]
409 ,[SCREENING_DATETIME] = ET.[SCREENING_DATETIME]
410 ,[SCREENING_STATUS] = ET.[SCREENING_STATUS]
411 ,[AWB] = ET.[AWB]
412 ,[CARRIER] = ET.[CARRIER]
413 ,[INBOUND_ACCEPTANCE] = ET.[INBOUND_ACCEPTANCE]
414 ,[OPS_DIM_FLAG] = ET.[OPS_DIM_FLAG]
415 ,[RETURNTYPE] = ET.[RETURNTYPE]
416 ,[ORIGINAL_MAILIDENTIFIER] = ET.[ORIGINAL_MAILIDENTIFIER]
417 ,[RETURN_REASON] = ET.[RETURN_REASON]
418 ,[BUNDLE_MAILIDENTIFIER] = ET.[BUNDLE_MAILIDENTIFIER]
419 ,[SUCCESSSTOPCLOCKEVENT] = ET.[SUCCESSSTOPCLOCKEVENT]
420 ,[SUCCESSSTOPCLOCKZIP] = ET.[SUCCESSSTOPCLOCKZIP]
421 ,[SUCCESSSTOPCLOCKDATE] = ET.[SUCCESSSTOPCLOCKDATE]
422 ,[SUCCESSSTOPCLOCKCOUNTRY] = ET.[SUCCESSSTOPCLOCKCOUNTRY]
423 ,[SUCCESSSSTOPCLOCKTIMEZONE] = ET.[SUCCESSSSTOPCLOCKTIMEZONE]
424 ,[INITIALTRANSPORTEVENT] = ET.[INITIALTRANSPORTEVENT]
425 ,[INITIALTRANSPORTZIP] = ET.[INITIALTRANSPORTZIP]
426 ,[INITIALTRANSPORTDATE] = ET.[INITIALTRANSPORTDATE]
427 ,[INITIALTRANSPORTCOUNTRY] = ET.[INITIALTRANSPORTCOUNTRY]
428 ,[INITIALTRANSPORTTIMEZONE] = ET.[INITIALTRANSPORTTIMEZONE]
429 ,[FINALTRANSPORTEVENT] = ET.[FINALTRANSPORTEVENT]
430 ,[FINALTRANSPORTZIP] = ET.[FINALTRANSPORTZIP]
431 ,[FINALTRANSPORTDATE] = ET.[FINALTRANSPORTDATE]
432 ,[FINALTRANSPORTCOUNTRY] = ET.[FINALTRANSPORTCOUNTRY]
433 ,[FINALTRANSPORTTIMEZONE] = ET.[FINALTRANSPORTTIMEZONE]
434 ,[CUSTOMSCOMPLETEEVENT] = ET.[CUSTOMSCOMPLETEEVENT]
435 ,[CUSTOMSCOMPLETEZIP] = ET.[CUSTOMSCOMPLETEZIP]
436 ,[CUSTOMSCOMPLETEDATE] = ET.[CUSTOMSCOMPLETEDATE]
437 ,[CUSTOMSCOMPLETECOUNTRY] = ET.[CUSTOMSCOMPLETECOUNTRY]
438 ,[CUSTOMSCOMPLETETIMEZONE] = ET.[CUSTOMSCOMPLETETIMEZONE]
439 ,[RURAL_ZIP] = ET.[RURAL_ZIP]
440 ,[SORT_CODE_UPDATED] = ET.[SORT_CODE_UPDATED]
441 ,[DECLARED_VALUE_CURR] = ET.[DECLARED_VALUE_CURR]
442 ,[U1_EVENT_DATE] = ET.[U1_EVENT_DATE]
443 ,[PERF_U] = ET.[PERF_U]
444 ,[PICKUP_CONNECT] = ET.[PICKUP_CONNECT]
445 ,[DIMWEIGHT_CONNECT] = ET.[DIMWEIGHT_CONNECT]
446 ,[DIMHEIGHT_CONNECT] = ET.[DIMHEIGHT_CONNECT]
447 ,[DIMWIDTH_CONNECT] = ET.[DIMWIDTH_CONNECT]
448 ,[DIMLENGTH_CONNECT] = ET.[DIMLENGTH_CONNECT]
449 ,[HOLD_REASON] = ET.[HOLD_REASON]
450 ,[DIM_LAST_DATE] = ET.[DIM_LAST_DATE]
451 ,[XTREME_SE_FIRST_DATE] = ET.[XTREME_SE_FIRST_DATE]
452 ,[XTREME_SE_FIRST_FACILITY] = ET.[XTREME_SE_FIRST_FACILITY]
453 ,[XTREME_SE_LAST_DATE] = ET.[XTREME_SE_LAST_DATE]
454 ,[XTREME_SE_LAST_FACILITY] = ET.[XTREME_SE_LAST_FACILITY]
455 ,[CUBOIDAL_FLAG] = ET.[CUBOIDAL_FLAG]
456 ,[CONTAINER_REF1] = ET.[CONTAINER_REF1]
457 ,[CONTAINER_REF2] = ET.[CONTAINER_REF2]
458 ,[DAYDEFINITEDELIVERY_ENC_STOP] = ET.[DAYDEFINITEDELIVERY_ENC_STOP]
459 ,[MESSAGE_GROUP_ID] = ET.[MESSAGE_GROUP_ID]
460 ,[MESSAGE_CATEGORY] = ET.[MESSAGE_CATEGORY]
461 ,[PERF_IT] = ET.[PERF_IT]
462 ,[PERF_FT] = ET.[PERF_FT]
463 ,[PERF_CC] = ET.[PERF_CC]
464 ,[PERF_SC] = ET.[PERF_SC]
465 ,[PERF_SD] = ET.[PERF_SD]
466 ,[PERF_T] = ET.[PERF_T]
467 ,[PERF_REC_ENC] = ET.[PERF_REC_ENC]
468 ,[PERF_ENC_DNS] = ET.[PERF_ENC_DNS]
469 ,[PERF_DNS_DNR] = ET.[PERF_DNS_DNR]
470 ,[PERF_DNR_MAN] = ET.[PERF_DNR_MAN]
471 ,[PERF_ENC_MAN] = ET.[PERF_ENC_MAN]
472 ,[PERF_REC_MAN] = ET.[PERF_REC_MAN]
473 ,[PERF_EDD] = ET.[PERF_EDD]
474 ,[EDD_T] = ET.[EDD_T]
475 ,[ENCODING_STATION_CONNECT] = ET.[ENCODING_STATION_CONNECT]
476 ,[ENCODING_USER_CONNECT] = ET.[ENCODING_USER_CONNECT]
477 ,[DATA_CAPTURED_METHOD_CONNECT] = ET.[DATA_CAPTURED_METHOD_CONNECT]
478 ,[LOGICAL_RECEIVEDATE] = ET.[LOGICAL_RECEIVEDATE]
479 ,[STARTCLOCK_FACILITYCODE] = ET.[STARTCLOCK_FACILITYCODE]
480 ,[PERF_RFD] = ET.[PERF_RFD]
481 ,[CONSIGNEE_ID_NUMBER] = ET.[CONSIGNEE_ID_NUMBER]
482 ,[CONSIGNEE_ID_TYPE] = ET.[CONSIGNEE_ID_TYPE]
483 ,[PICKUP2] = ET.[PICKUP2]
484 ,[LAST_OBTAINED_LATITUDE] = ET.[LAST_OBTAINED_LATITUDE]
485 ,[LAST_OBTAINED_LONGITUDE] = ET.[LAST_OBTAINED_LONGITUDE]
486 ,[TRANSIT_ODC_DDC] = ET.[TRANSIT_ODC_DDC]
487 ,[TRANSIT_DDC_FVE] = ET.[TRANSIT_DDC_FVE]
488 ,[DIM_SOURCE] = ET.[DIM_SOURCE]
489 ,[BILLED_ON] = ET.[BILLED_ON]
490 ,[PROCESSED_ON] = ET.[PROCESSED_ON]
491 ,ALERT_SOURCE_SYSTEM = ET.ALERT_SOURCE_SYSTEM
492 ,[DWH_UPDATE_DATE] = GETDATE()
493 FROM RECORDS
494 WHERE RECORDS.[STATUS] != 'NEW' AND [OpsProd].[FactTrackMailItem].[TMI_UID] = RECORDS.[TMI_UID]
495 OPTION (LABEL = 'elt_user')
496
497
498 /********************************************************************************************/
499 /*Step 6: INSERT [asb].[FactOrderMovementRemark] TABLE from CTAS table for new data which are copied from external tables*/
500 /********************************************************************************************/
501 INSERT INTO [OpsProd].[FactTrackMailItem] (
502 [TMI_UID]
503 ,[DELIVERYCONFIRMATIONNUMBER]
504 ,[CUSTOMERCONFIRMATIONNUMBER]
505 ,[WEIGHT]
506 ,[CUSTOMERREFERENCE]
507 ,[BATCHREFERENCE]
508 ,[MARKUPREASONCODE]
509 ,[SIGNATURECONFIRMATIONFLAG]
510 ,[ENTRYUNIT]
511 ,[ENTRYLEVEL]
512 ,[MANIFESTEDDSPPRODUCT]
513 ,[RECEIVEDATE]
514 ,[MANIFESTDATE]
515 ,[ENCODEDATE]
516 ,[PICKUP]
517 ,[STOPCLOCKDATE]
518 ,[STOPCLOCKEVENT]
519 ,[STOPCLOCKZIP]
520 ,[LASTEVENT_ID]
521 ,[LASTEVENTDATE]
522 ,[LASTEVENTZIP]
523 ,[VENDOREVENT_ID]
524 ,[VENDOREVENT_DATE]
525 ,[VENDOREVENT_ZIP]
526 ,[ZIP5]
527 ,[ZIP3]
528 ,[ORIGINFACILITYCODE]
529 ,[DESTINATIONFACILITYCODE]
530 ,[CONSIGNMENTNOTENUMBER]
531 ,[JOBNUMBER]
532 ,[WORKORDER]
533 ,[PRODUCT_ID]
534 ,[PERF_D]
535 ,[PERF_C]
536 ,[PERF_B]
537 ,[PERF_M]
538 ,[PERF_S]
539 ,[PERF_V]
540 ,[CYCLE_TIME]
541 ,[SACK_SORT_LEVEL]
542 ,[SACK_BARCODE]
543 ,[SACK_BARCODE_TYPE]
544 ,[ROUTING_ID]
545 ,[ROUTING_NAME]
546 ,[ENTRYRATECODE]
547 ,[ENTRYRATECODE_EVS]
548 ,[POSTAGE_ZONE]
549 ,[BEDEBARCODE1]
550 ,[DELIVERYCONFIRMATIONFLAG]
551 ,[DSP_ID]
552 ,[ENCLOSUREPOSTAGE]
553 ,[ENCODING_STATION]
554 ,[ENCODING_USER]
555 ,[IMBCODE]
556 ,[IMBFLAG]
557 ,[MOVEUPDATEHANDLING]
558 ,[OVERLABELEDIMBCODE]
559 ,[QUICKNETMAILITEMFLAG]
560 ,[REENCODEFLAG]
561 ,[SURCHARGE]
562 ,[POSTAGE]
563 ,[ACTUALRECIPIENTCOUNTRY]
564 ,[DELETED]
565 ,[CUSTOMERCONFIRMATIONFLAG]
566 ,[VENDOREVENTSFLAG]
567 ,[LASTEVENTCOUNTRY]
568 ,[PROCESSABLEFLAG]
569 ,[STOPCLOCKCOUNTRY]
570 ,[EXPECTEDDELIVERY]
571 ,[VENDOREVENT_COUNTRY]
572 ,[PERF_L]
573 ,[PERF_LM]
574 ,[ADDRESSIDFLAG]
575 ,[ZIPCODECHANGEFLAG]
576 ,[MAILTYPE]
577 ,[DATACAPTUREMETHOD]
578 ,[INTENDEDTRANSPORTMODE]
579 ,[DSPMAILIDENTIFIER]
580 ,[DSP_ACCOUNT_ID]
581 ,[EXCEPTION_FLAG]
582 ,[EXCEPTION_EVENT_ID]
583 ,[EXCEPTION_DATE]
584 ,[EXCEPTION_ZIP]
585 ,[EXCEPTION_COUNTRY]
586 ,[SORTCODE_OUTBOUND]
587 ,[SORTCODE_INBOUND]
588 ,[OVERLABEL_DC_EVS]
589 ,[MAILIDENTIFIER]
590 ,[LOCAL_RECEIVEDATE]
591 ,[PERF_E]
592 ,[DN_CONTAINER]
593 ,[ACTUALRECIPIENTADDRESS3]
594 ,[MANIFESTNUMBER]
595 ,[DIMWEIGHT]
596 ,[DIMHEIGHT]
597 ,[DIMWIDTH]
598 ,[DIMLENGTH]
599 ,[CUSTOMERCONFIRMATIONNUMBER2]
600 ,[GATEWAY]
601 ,[MAILCONTENTCATEGORY]
602 ,[DECLAREDVALUE]
603 ,[INCOTERM]
604 ,[ROUTINGFORMAT]
605 ,[INTENDEDFACILITYCODE]
606 ,[OVERLABELREASONID]
607 ,[INTERNAL_RECEIVEDATE]
608 ,[SOURCE_SYSTEM]
609 ,[ORIGINFACILITYCODE_CONNECT]
610 ,[POSTAGE_CONNECT]
611 ,[POSTAGE_CURRENCY]
612 ,[ROUTING_ID_CONNECT]
613 ,[ROUTING_NAME_CONNECT]
614 ,[WEIGHT_CONNECT]
615 ,[BILLABLEWEIGHT_CONNECT]
616 ,[MANIFESTEDDSPPRODUCT_CONNECT]
617 ,[ENCODINGSESSIONID]
618 ,[COD_REMIT_TRACKINGNUMBER]
619 ,[COD_CURRENCY]
620 ,[COD_AMOUNT]
621 ,[ORIGINAL_ORDERED_PRODUCTCODE]
622 ,[SECURITY_SCREEN]
623 ,[BILLABLEWEIGHT]
624 ,[DN_SHIPDATE]
625 ,[DN_RECEIVEDATE]
626 ,[ENCODEDATE_CONNECT]
627 ,[SIGNED_FOR_NAME]
628 ,[LASTEVENT_TIMEZONE]
629 ,[STOPCLOCK_TIMEZONE]
630 ,[VENDOREVENT_TIMEZONE]
631 ,[EXCEPTION_TIMEZONE]
632 ,[CONSIGNMENTNOTENUMBER2]
633 ,[AMP_STATUS]
634 ,[ACTUALRECIPIENTADDRESS1]
635 ,[ACTUALRECIPIENTZIP4]
636 ,[DELIVERY_POINT]
637 ,[ACTUALRECIPIENTADDRESS2]
638 ,[ACTUALRECIPIENTCITY]
639 ,[ACTUALRECIPIENTSTATE]
640 ,[ACTUALRECIPIENTZIP]
641 ,[CONTENT_CATEGORY_ID]
642 ,[CONTAINER_TYPE]
643 ,[CONTAINER_BARCODE]
644 ,[SCAN2SACKDATE]
645 ,[DAYDEFINITEDELIVERY]
646 ,[MAPPED_DSP_ID]
647 ,[LAST_SECONDARY_EVENT_ID]
648 ,[SCREENER_NAME]
649 ,[SCREENING_METHOD]
650 ,[SCREENING_DATETIME]
651 ,[SCREENING_STATUS]
652 ,[AWB]
653 ,[CARRIER]
654 ,[INBOUND_ACCEPTANCE]
655 ,[OPS_DIM_FLAG]
656 ,[RETURNTYPE]
657 ,[ORIGINAL_MAILIDENTIFIER]
658 ,[RETURN_REASON]
659 ,[BUNDLE_MAILIDENTIFIER]
660 ,[SUCCESSSTOPCLOCKEVENT]
661 ,[SUCCESSSTOPCLOCKZIP]
662 ,[SUCCESSSTOPCLOCKDATE]
663 ,[SUCCESSSTOPCLOCKCOUNTRY]
664 ,[SUCCESSSSTOPCLOCKTIMEZONE]
665 ,[INITIALTRANSPORTEVENT]
666 ,[INITIALTRANSPORTZIP]
667 ,[INITIALTRANSPORTDATE]
668 ,[INITIALTRANSPORTCOUNTRY]
669 ,[INITIALTRANSPORTTIMEZONE]
670 ,[FINALTRANSPORTEVENT]
671 ,[FINALTRANSPORTZIP]
672 ,[FINALTRANSPORTDATE]
673 ,[FINALTRANSPORTCOUNTRY]
674 ,[FINALTRANSPORTTIMEZONE]
675 ,[CUSTOMSCOMPLETEEVENT]
676 ,[CUSTOMSCOMPLETEZIP]
677 ,[CUSTOMSCOMPLETEDATE]
678 ,[CUSTOMSCOMPLETECOUNTRY]
679 ,[CUSTOMSCOMPLETETIMEZONE]
680 ,[RURAL_ZIP]
681 ,[SORT_CODE_UPDATED]
682 ,[DECLARED_VALUE_CURR]
683 ,[U1_EVENT_DATE]
684 ,[PERF_U]
685 ,[PICKUP_CONNECT]
686 ,[DIMWEIGHT_CONNECT]
687 ,[DIMHEIGHT_CONNECT]
688 ,[DIMWIDTH_CONNECT]
689 ,[DIMLENGTH_CONNECT]
690 ,[HOLD_REASON]
691 ,[DIM_LAST_DATE]
692 ,[XTREME_SE_FIRST_DATE]
693 ,[XTREME_SE_FIRST_FACILITY]
694 ,[XTREME_SE_LAST_DATE]
695 ,[XTREME_SE_LAST_FACILITY]
696 ,[CUBOIDAL_FLAG]
697 ,[CONTAINER_REF1]
698 ,[CONTAINER_REF2]
699 ,[DAYDEFINITEDELIVERY_ENC_STOP]
700 ,[MESSAGE_GROUP_ID]
701 ,[MESSAGE_CATEGORY]
702 ,[PERF_IT]
703 ,[PERF_FT]
704 ,[PERF_CC]
705 ,[PERF_SC]
706 ,[PERF_SD]
707 ,[PERF_T]
708 ,[PERF_REC_ENC]
709 ,[PERF_ENC_DNS]
710 ,[PERF_DNS_DNR]
711 ,[PERF_DNR_MAN]
712 ,[PERF_ENC_MAN]
713 ,[PERF_REC_MAN]
714 ,[PERF_EDD]
715 ,[EDD_T]
716 ,[ENCODING_STATION_CONNECT]
717 ,[ENCODING_USER_CONNECT]
718 ,[DATA_CAPTURED_METHOD_CONNECT]
719 ,[LOGICAL_RECEIVEDATE]
720 ,[STARTCLOCK_FACILITYCODE]
721 ,[PERF_RFD]
722 ,[CONSIGNEE_ID_NUMBER]
723 ,[CONSIGNEE_ID_TYPE]
724 ,[PICKUP2]
725 ,[LAST_OBTAINED_LATITUDE]
726 ,[LAST_OBTAINED_LONGITUDE]
727 ,[TRANSIT_ODC_DDC]
728 ,[TRANSIT_DDC_FVE]
729 ,[DIM_SOURCE]
730 ,[BILLED_ON]
731 ,[PROCESSED_ON]
732 ,ALERT_SOURCE_SYSTEM
733 ,[DWH_INSERT_DATE]
734 ,[DWH_UPDATE_DATE]
735 )
736 SELECT ET.[TMI_UID]
737 ,ET.[DELIVERYCONFIRMATIONNUMBER]
738 ,ET.[CUSTOMERCONFIRMATIONNUMBER]
739 ,ET.[WEIGHT]
740 ,ET.[CUSTOMERREFERENCE]
741 ,ET.[BATCHREFERENCE]
742 ,ET.[MARKUPREASONCODE]
743 ,ET.[SIGNATURECONFIRMATIONFLAG]
744 ,ET.[ENTRYUNIT]
745 ,ET.[ENTRYLEVEL]
746 ,ET.[MANIFESTEDDSPPRODUCT]
747 ,ET.[RECEIVEDATE]
748 ,ET.[MANIFESTDATE]
749 ,ET.[ENCODEDATE]
750 ,ET.[PICKUP]
751 ,ET.[STOPCLOCKDATE]
752 ,ET.[STOPCLOCKEVENT]
753 ,ET.[STOPCLOCKZIP]
754 ,ET.[LASTEVENT_ID]
755 ,ET.[LASTEVENTDATE]
756 ,ET.[LASTEVENTZIP]
757 ,ET.[VENDOREVENT_ID]
758 ,ET.[VENDOREVENT_DATE]
759 ,ET.[VENDOREVENT_ZIP]
760 ,ET.[ZIP5]
761 ,ET.[ZIP3]
762 ,ET.[ORIGINFACILITYCODE]
763 ,ET.[DESTINATIONFACILITYCODE]
764 ,ET.[CONSIGNMENTNOTENUMBER]
765 ,ET.[JOBNUMBER]
766 ,ET.[WORKORDER]
767 ,ET.[PRODUCT_ID]
768 ,ET.[PERF_D]
769 ,ET.[PERF_C]
770 ,ET.[PERF_B]
771 ,ET.[PERF_M]
772 ,ET.[PERF_S]
773 ,ET.[PERF_V]
774 ,ET.[CYCLE_TIME]
775 ,ET.[SACK_SORT_LEVEL]
776 ,ET.[SACK_BARCODE]
777 ,ET.[SACK_BARCODE_TYPE]
778 ,ET.[ROUTING_ID]
779 ,ET.[ROUTING_NAME]
780 ,ET.[ENTRYRATECODE]
781 ,ET.[ENTRYRATECODE_EVS]
782 ,ET.[POSTAGE_ZONE]
783 ,ET.[BEDEBARCODE1]
784 ,ET.[DELIVERYCONFIRMATIONFLAG]
785 ,ET.[DSP_ID]
786 ,ET.[ENCLOSUREPOSTAGE]
787 ,ET.[ENCODING_STATION]
788 ,ET.[ENCODING_USER]
789 ,ET.[IMBCODE]
790 ,ET.[IMBFLAG]
791 ,ET.[MOVEUPDATEHANDLING]
792 ,ET.[OVERLABELEDIMBCODE]
793 ,ET.[QUICKNETMAILITEMFLAG]
794 ,ET.[REENCODEFLAG]
795 ,ET.[SURCHARGE]
796 ,ET.[POSTAGE]
797 ,ET.[ACTUALRECIPIENTCOUNTRY]
798 ,ET.[DELETED]
799 ,ET.[CUSTOMERCONFIRMATIONFLAG]
800 ,ET.[VENDOREVENTSFLAG]
801 ,ET.[LASTEVENTCOUNTRY]
802 ,ET.[PROCESSABLEFLAG]
803 ,ET.[STOPCLOCKCOUNTRY]
804 ,ET.[EXPECTEDDELIVERY]
805 ,ET.[VENDOREVENT_COUNTRY]
806 ,ET.[PERF_L]
807 ,ET.[PERF_LM]
808 ,ET.[ADDRESSIDFLAG]
809 ,ET.[ZIPCODECHANGEFLAG]
810 ,ET.[MAILTYPE]
811 ,ET.[DATACAPTUREMETHOD]
812 ,ET.[INTENDEDTRANSPORTMODE]
813 ,ET.[DSPMAILIDENTIFIER]
814 ,ET.[DSP_ACCOUNT_ID]
815 ,ET.[EXCEPTION_FLAG]
816 ,ET.[EXCEPTION_EVENT_ID]
817 ,ET.[EXCEPTION_DATE]
818 ,ET.[EXCEPTION_ZIP]
819 ,ET.[EXCEPTION_COUNTRY]
820 ,ET.[SORTCODE_OUTBOUND]
821 ,ET.[SORTCODE_INBOUND]
822 ,ET.[OVERLABEL_DC_EVS]
823 ,ET.[MAILIDENTIFIER]
824 ,ET.[LOCAL_RECEIVEDATE]
825 ,ET.[PERF_E]
826 ,ET.[DN_CONTAINER]
827 ,ET.[ACTUALRECIPIENTADDRESS3]
828 ,ET.[MANIFESTNUMBER]
829 ,ET.[DIMWEIGHT]
830 ,ET.[DIMHEIGHT]
831 ,ET.[DIMWIDTH]
832 ,ET.[DIMLENGTH]
833 ,ET.[CUSTOMERCONFIRMATIONNUMBER2]
834 ,ET.[GATEWAY]
835 ,ET.[MAILCONTENTCATEGORY]
836 ,ET.[DECLAREDVALUE]
837 ,ET.[INCOTERM]
838 ,ET.[ROUTINGFORMAT]
839 ,ET.[INTENDEDFACILITYCODE]
840 ,ET.[OVERLABELREASONID]
841 ,ET.[INTERNAL_RECEIVEDATE]
842 ,ET.[SOURCE_SYSTEM]
843 ,ET.[ORIGINFACILITYCODE_CONNECT]
844 ,ET.[POSTAGE_CONNECT]
845 ,ET.[POSTAGE_CURRENCY]
846 ,ET.[ROUTING_ID_CONNECT]
847 ,ET.[ROUTING_NAME_CONNECT]
848 ,ET.[WEIGHT_CONNECT]
849 ,ET.[BILLABLEWEIGHT_CONNECT]
850 ,ET.[MANIFESTEDDSPPRODUCT_CONNECT]
851 ,ET.[ENCODINGSESSIONID]
852 ,ET.[COD_REMIT_TRACKINGNUMBER]
853 ,ET.[COD_CURRENCY]
854 ,ET.[COD_AMOUNT]
855 ,ET.[ORIGINAL_ORDERED_PRODUCTCODE]
856 ,ET.[SECURITY_SCREEN]
857 ,ET.[BILLABLEWEIGHT]
858 ,ET.[DN_SHIPDATE]
859 ,ET.[DN_RECEIVEDATE]
860 ,ET.[ENCODEDATE_CONNECT]
861 ,ET.[SIGNED_FOR_NAME]
862 ,ET.[LASTEVENT_TIMEZONE]
863 ,ET.[STOPCLOCK_TIMEZONE]
864 ,ET.[VENDOREVENT_TIMEZONE]
865 ,ET.[EXCEPTION_TIMEZONE]
866 ,ET.[CONSIGNMENTNOTENUMBER2]
867 ,ET.[AMP_STATUS]
868 ,ET.[ACTUALRECIPIENTADDRESS1]
869 ,ET.[ACTUALRECIPIENTZIP4]
870 ,ET.[DELIVERY_POINT]
871 ,ET.[ACTUALRECIPIENTADDRESS2]
872 ,ET.[ACTUALRECIPIENTCITY]
873 ,ET.[ACTUALRECIPIENTSTATE]
874 ,ET.[ACTUALRECIPIENTZIP]
875 ,ET.[CONTENT_CATEGORY_ID]
876 ,ET.[CONTAINER_TYPE]
877 ,ET.[CONTAINER_BARCODE]
878 ,ET.[SCAN2SACKDATE]
879 ,ET.[DAYDEFINITEDELIVERY]
880 ,ET.[MAPPED_DSP_ID]
881 ,ET.[LAST_SECONDARY_EVENT_ID]
882 ,ET.[SCREENER_NAME]
883 ,ET.[SCREENING_METHOD]
884 ,ET.[SCREENING_DATETIME]
885 ,ET.[SCREENING_STATUS]
886 ,ET.[AWB]
887 ,ET.[CARRIER]
888 ,ET.[INBOUND_ACCEPTANCE]
889 ,ET.[OPS_DIM_FLAG]
890 ,ET.[RETURNTYPE]
891 ,ET.[ORIGINAL_MAILIDENTIFIER]
892 ,ET.[RETURN_REASON]
893 ,ET.[BUNDLE_MAILIDENTIFIER]
894 ,ET.[SUCCESSSTOPCLOCKEVENT]
895 ,ET.[SUCCESSSTOPCLOCKZIP]
896 ,ET.[SUCCESSSTOPCLOCKDATE]
897 ,ET.[SUCCESSSTOPCLOCKCOUNTRY]
898 ,ET.[SUCCESSSSTOPCLOCKTIMEZONE]
899 ,ET.[INITIALTRANSPORTEVENT]
900 ,ET.[INITIALTRANSPORTZIP]
901 ,ET.[INITIALTRANSPORTDATE]
902 ,ET.[INITIALTRANSPORTCOUNTRY]
903 ,ET.[INITIALTRANSPORTTIMEZONE]
904 ,ET.[FINALTRANSPORTEVENT]
905 ,ET.[FINALTRANSPORTZIP]
906 ,ET.[FINALTRANSPORTDATE]
907 ,ET.[FINALTRANSPORTCOUNTRY]
908 ,ET.[FINALTRANSPORTTIMEZONE]
909 ,ET.[CUSTOMSCOMPLETEEVENT]
910 ,ET.[CUSTOMSCOMPLETEZIP]
911 ,ET.[CUSTOMSCOMPLETEDATE]
912 ,ET.[CUSTOMSCOMPLETECOUNTRY]
913 ,ET.[CUSTOMSCOMPLETETIMEZONE]
914 ,ET.[RURAL_ZIP]
915 ,ET.[SORT_CODE_UPDATED]
916 ,ET.[DECLARED_VALUE_CURR]
917 ,ET.[U1_EVENT_DATE]
918 ,ET.[PERF_U]
919 ,ET.[PICKUP_CONNECT]
920 ,ET.[DIMWEIGHT_CONNECT]
921 ,ET.[DIMHEIGHT_CONNECT]
922 ,ET.[DIMWIDTH_CONNECT]
923 ,ET.[DIMLENGTH_CONNECT]
924 ,ET.[HOLD_REASON]
925 ,ET.[DIM_LAST_DATE]
926 ,ET.[XTREME_SE_FIRST_DATE]
927 ,ET.[XTREME_SE_FIRST_FACILITY]
928 ,ET.[XTREME_SE_LAST_DATE]
929 ,ET.[XTREME_SE_LAST_FACILITY]
930 ,ET.[CUBOIDAL_FLAG]
931 ,ET.[CONTAINER_REF1]
932 ,ET.[CONTAINER_REF2]
933 ,ET.[DAYDEFINITEDELIVERY_ENC_STOP]
934 ,ET.[MESSAGE_GROUP_ID]
935 ,ET.[MESSAGE_CATEGORY]
936 ,ET.[PERF_IT]
937 ,ET.[PERF_FT]
938 ,ET.[PERF_CC]
939 ,ET.[PERF_SC]
940 ,ET.[PERF_SD]
941 ,ET.[PERF_T]
942 ,ET.[PERF_REC_ENC]
943 ,ET.[PERF_ENC_DNS]
944 ,ET.[PERF_DNS_DNR]
945 ,ET.[PERF_DNR_MAN]
946 ,ET.[PERF_ENC_MAN]
947 ,ET.[PERF_REC_MAN]
948 ,ET.[PERF_EDD]
949 ,ET.[EDD_T]
950 ,ET.[ENCODING_STATION_CONNECT]
951 ,ET.[ENCODING_USER_CONNECT]
952 ,ET.[DATA_CAPTURED_METHOD_CONNECT]
953 ,ET.[LOGICAL_RECEIVEDATE]
954 ,ET.[STARTCLOCK_FACILITYCODE]
955 ,ET.[PERF_RFD]
956 ,ET.[CONSIGNEE_ID_NUMBER]
957 ,ET.[CONSIGNEE_ID_TYPE]
958 ,ET.[PICKUP2]
959 ,ET.[LAST_OBTAINED_LATITUDE]
960 ,ET.[LAST_OBTAINED_LONGITUDE]
961 ,ET.[TRANSIT_ODC_DDC]
962 ,ET.[TRANSIT_DDC_FVE]
963 ,ET.[DIM_SOURCE]
964 ,ET.[BILLED_ON]
965 ,ET.[PROCESSED_ON]
966 ,ET.ALERT_SOURCE_SYSTEM
967 ,GETDATE()
968 ,GETDATE()
969 FROM RECORDS ET
970 WHERE RECORDS.[STATUS] = 'NEW' AND [OpsProd].[FactTrackMailItem].[TMI_UID] = RECORDS.[TMI_UID]
971 OPTION (LABEL = 'elt_user')
972
973
974 /* Insert TMI_UID in Alert Table which don't have alert' */
975 /*
976 INSERT INTO [OpsProd].[FactTrackMailAlert] (
977 [TMI_UID]
978 ,[DELIVERYCONFIRMATIONNUMBER]
979 ,[CUSTOMERCONFIRMATIONNUMBER]
980 ,[RECEIVEDATE]
981 ,MAIL_ORIGIN
982 )
983 select
984 ET.[TMI_UID]
985 ,ET.[DELIVERYCONFIRMATIONNUMBER]
986 ,ET.[CUSTOMERCONFIRMATIONNUMBER]
987 ,ET.ENCODEDATE
988 ,'PHYSICAL'
989 FROM CTAS.FACT_TRACK_MAIL_ITEM ET
990 LEFT JOIN [OpsProd].[FactTrackMailaLERT] DFT ON DFT.[TMI_UID] = ET.[TMI_UID]
991 WHERE DFT.[TMI_UID] IS NULL
992 */
993
994 Update [OpsProd].[FactTrackMailAlert]
995 SET ENCODEDATE = ET.ENCODEDATE
996 ,PHYSICALMAILDELETED = ET.DELETED
997 ,INITIALTRANSPORTDATE = ET.INITIALTRANSPORTDATE
998 ,FINALTRANSPORTDATE = ET.FINALTRANSPORTDATE
999 ,ENCODED_LASTEVENTDATE = ET.LASTEVENTDATE
1000 ,ENCODED_LASTEVENT_ID = ET.LASTEVENT_ID
1001 ,ENCODED_ODC = ET.ORIGINFACILITYCODE
1002 ,ENCODED_DDC = ET.DESTINATIONFACILITYCODE
1003 ,ENCODED_PRODUCT = ET.PRODUCT_ID
1004 FROM RECORDS ET
1005 WHERE [OpsProd].[FactTrackMailAlert].[TMI_UID] = ET.[TMI_UID]
1006 OPTION (LABEL = 'elt_user')
1007
1008 --UPDATE OpsProd.FactTrackMailItem
1009 -- SET ALERT_SOURCE_SYSTEM=A.SOURCE_SYSTEM
1010 -- FROM OpsProd.FactTrackMailAlert A
1011 -- where OpsProd.FactTrackMailItem.TMI_UID = A.TMI_UID
1012
1013 IF OBJECT_ID('CTAS.FACT_TRACK_MAIL_ITEM', 'U') IS NOT NULL
1014 DROP TABLE CTAS.FACT_TRACK_MAIL_ITEM
1015
1016
1017END;
1018GO
1019