[Home] [Help]
PACKAGE BODY: APPS.DDR_BASE_UTIL_PKG
Source
1 PACKAGE BODY DDR_BASE_UTIL_PKG AS
2 /* $Header: ddrubasb.pls 120.1.12010000.4 2010/03/03 04:20:34 vbhave ship $ */
3
4 FUNCTION set_load_id RETURN NUMBER
5 AS
6 v_load_id NUMBER;
7 BEGIN
8 select DDR_LOAD_SEQ.NEXTVAL
9 into v_load_id
10 from dual;
11 RETURN v_load_id;
12 END set_load_id;
13
14
15 FUNCTION RTL_INV_ITEM_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2 AS
16 v_load_id NUMBER := NVL(p_load_id, set_load_id);
17 BEGIN
18
19 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
20
21 INSERT INTO DDR_E_RTL_INV_ITEM
22 (REC_ID, LOAD_ID, ERR_REASON,
23 GLBL_ITEM_ID, GLBL_ITEM_ID_TYP,RTL_BSNS_UNIT_CD,
24 RTL_SKU_ITEM_NBR,INV_LOC_CD,UOM, ON_HAND_QTY,RECVD_QTY,
25 IN_TRANSIT_QTY,BCK_ORDR_QTY,QLTY_HOLD_QTY,
26 ON_HAND_NET_COST_AMT, RECVD_NET_COST_AMT,
27 IN_TRANSIT_NET_COST_AMT, BCKORDR_NET_COST_AMT,
28 QLTY_HOLD_NET_COST_AMT, ON_HAND_RTL_AMT, RECVD_RTL_AMT,
29 IN_TRANSIT_RTL_AMT,BCKORDR_RTL_AMT,
30 QLTY_HOLD_RTL_AMT,SRC_SYS_IDNT, SRC_SYS_DT,
31 SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT, RTL_ORG_CD
32 )
33 SELECT REC_ID, v_load_id,'Duplicate Record',
34 GLBL_ITEM_ID,GLBL_ITEM_ID_TYP, RTL_BSNS_UNIT_CD,
35 RTL_SKU_ITEM_NBR, INV_LOC_CD,UOM,
36 ON_HAND_QTY, RECVD_QTY,
37 IN_TRANSIT_QTY, BCK_ORDR_QTY,
38 QLTY_HOLD_QTY, ON_HAND_NET_COST_AMT,
39 RECVD_NET_COST_AMT, IN_TRANSIT_NET_COST_AMT,
40 BCKORDR_NET_COST_AMT, QLTY_HOLD_NET_COST_AMT,
41 ON_HAND_RTL_AMT, RECVD_RTL_AMT,
42 IN_TRANSIT_RTL_AMT, BCKORDR_RTL_AMT,
43 QLTY_HOLD_RTL_AMT, SRC_SYS_IDNT,
44 SRC_SYS_DT,'I','N',TRANS_DT, RTL_ORG_CD
45 FROM DDR_I_RTL_INV_ITEM
46 WHERE
47 (RTL_BSNS_UNIT_CD,
48 TRANS_DT,
49 GLBL_ITEM_ID,
50 RTL_SKU_ITEM_NBR,
51 GLBL_ITEM_ID_TYP,
52 INV_LOC_CD, RTL_ORG_CD)
53 IN (
54 SELECT
55 RTL_BSNS_UNIT_CD,
56 TRANS_DT,
57 GLBL_ITEM_ID,
58 RTL_SKU_ITEM_NBR,
59 GLBL_ITEM_ID_TYP,
60 INV_LOC_CD, RTL_ORG_CD
61 FROM DDR_I_RTL_INV_ITEM
62 GROUP BY
63 RTL_BSNS_UNIT_CD,
64 TRANS_DT,
65 GLBL_ITEM_ID,
66 RTL_SKU_ITEM_NBR,
67 GLBL_ITEM_ID_TYP,
68 INV_LOC_CD, RTL_ORG_CD
69 HAVING COUNT(*) > 1
70 );
71 COMMIT;
72
73
74 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
75
76
77 DELETE FROM DDR_I_RTL_INV_ITEM
78 WHERE
79 (RTL_BSNS_UNIT_CD,
80 TRANS_DT,
81 GLBL_ITEM_ID,
82 RTL_SKU_ITEM_NBR,
83 GLBL_ITEM_ID_TYP,
84 INV_LOC_CD, RTL_ORG_CD)
85 IN (
86 SELECT
87 RTL_BSNS_UNIT_CD,
88 TRANS_DT,
89 GLBL_ITEM_ID,
90 RTL_SKU_ITEM_NBR,
91 GLBL_ITEM_ID_TYP,
92 INV_LOC_CD, RTL_ORG_CD
93 FROM DDR_E_RTL_INV_ITEM
94 GROUP BY
95 RTL_BSNS_UNIT_CD,
96 TRANS_DT,
97 GLBL_ITEM_ID,
98 RTL_SKU_ITEM_NBR,
99 GLBL_ITEM_ID_TYP,
100 INV_LOC_CD, RTL_ORG_CD
101 HAVING COUNT(*) > 1
102 );
103 COMMIT;
104
105 RETURN('Y');
106
107 END RTL_INV_ITEM_DUPS_FNC;
108
109
110 FUNCTION PRMTN_PLN_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
111 AS
112 v_load_id NUMBER := NVL(p_load_id, set_load_id);
113 BEGIN
114 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
115 INSERT INTO DDR_E_PRMTN_PLN
116 (REC_ID, LOAD_ID, ERR_REASON,RTL_BSNS_UNIT_CD,
117 PRMTN_TYP,
118 PRMTN_FROM_DT,
119 PRMTN_TO_DT,
120 GLBL_ITEM_ID,
121 RTL_SKU_ITEM_NBR,
122 GLBL_ITEM_ID_TYP,
123 PRMTN_PRICE_AMT,
124 SRC_SYS_IDNT,
125 SRC_SYS_DT,
126 SRC_IDNT_FLAG,
127 ACTION_FLAG,
128 TRANS_DT,
129 RTL_ORG_CD,
130 ATTRIBUTE1,
131 ATTRIBUTE2,
132 ATTRIBUTE3,
133 ATTRIBUTE4,
134 ATTRIBUTE5,
135 ATTRIBUTE6,
136 ATTRIBUTE7,
137 ATTRIBUTE8,
138 ATTRIBUTE9,
139 ATTRIBUTE10,
140 ATTRIBUTE11,
141 ATTRIBUTE12,
142 ATTRIBUTE13,
143 ATTRIBUTE14,
144 ATTRIBUTE15,
145 ATTRIBUTE16,
146 ATTRIBUTE17,
147 ATTRIBUTE18,
148 ATTRIBUTE19,
149 ATTRIBUTE20
150 )
151 SELECT REC_ID, v_load_id,'Duplicate Record',
152 RTL_BSNS_UNIT_CD,
153 PRMTN_TYP,
154 PRMTN_FROM_DT,
155 PRMTN_TO_DT,
156 GLBL_ITEM_ID,
157 RTL_SKU_ITEM_NBR,
158 GLBL_ITEM_ID_TYP,
159 PRMTN_PRICE_AMT,
160 SRC_SYS_IDNT,
161 SRC_SYS_DT,
162 'I','N',TRANS_DT, RTL_ORG_CD,
163 ATTRIBUTE1,
164 ATTRIBUTE2,
165 ATTRIBUTE3,
166 ATTRIBUTE4,
167 ATTRIBUTE5,
168 ATTRIBUTE6,
169 ATTRIBUTE7,
170 ATTRIBUTE8,
171 ATTRIBUTE9,
172 ATTRIBUTE10,
173 ATTRIBUTE11,
174 ATTRIBUTE12,
175 ATTRIBUTE13,
176 ATTRIBUTE14,
177 ATTRIBUTE15,
178 ATTRIBUTE16,
179 ATTRIBUTE17,
180 ATTRIBUTE18,
181 ATTRIBUTE19,
182 ATTRIBUTE20
183 FROM DDR_I_PRMTN_PLN
184 WHERE
185 (GLBL_ITEM_ID,
186 RTL_BSNS_UNIT_CD,
187 PRMTN_TYP,
188 PRMTN_FROM_DT,
189 PRMTN_TO_DT,
190 RTL_SKU_ITEM_NBR,
191 GLBL_ITEM_ID_TYP,
192 TRANS_DT, RTL_ORG_CD)
193 IN (
194 SELECT
195 GLBL_ITEM_ID,
196 RTL_BSNS_UNIT_CD,
197 PRMTN_TYP,
198 PRMTN_FROM_DT,
199 PRMTN_TO_DT,
200 RTL_SKU_ITEM_NBR,
201 GLBL_ITEM_ID_TYP,
202 TRANS_DT, RTL_ORG_CD
203 FROM DDR_I_PRMTN_PLN
204 GROUP BY
205 GLBL_ITEM_ID,
206 RTL_BSNS_UNIT_CD,
207 PRMTN_TYP,
208 PRMTN_FROM_DT,
209 PRMTN_TO_DT,
210 RTL_SKU_ITEM_NBR,
211 GLBL_ITEM_ID_TYP,
212 TRANS_DT, RTL_ORG_CD
213 HAVING COUNT(*) > 1
214 );
215 COMMIT;
216
217 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
218
219
220 DELETE FROM DDR_I_PRMTN_PLN
221 WHERE
222 (GLBL_ITEM_ID,
223 RTL_BSNS_UNIT_CD,
224 PRMTN_TYP,
225 PRMTN_FROM_DT,
226 PRMTN_TO_DT,
227 RTL_SKU_ITEM_NBR,
228 GLBL_ITEM_ID_TYP,
229 TRANS_DT, RTL_ORG_CD)
230 IN (
231 SELECT
232 GLBL_ITEM_ID,
233 RTL_BSNS_UNIT_CD,
234 PRMTN_TYP,
235 PRMTN_FROM_DT,
236 PRMTN_TO_DT,
237 RTL_SKU_ITEM_NBR,
238 GLBL_ITEM_ID_TYP,
239 TRANS_DT, RTL_ORG_CD
240 FROM DDR_E_PRMTN_PLN
241 GROUP BY
242 GLBL_ITEM_ID,
243 RTL_BSNS_UNIT_CD,
244 PRMTN_TYP,
245 PRMTN_FROM_DT,
246 PRMTN_TO_DT,
247 RTL_SKU_ITEM_NBR,
248 GLBL_ITEM_ID_TYP,
249 TRANS_DT, RTL_ORG_CD
250 HAVING COUNT(*) > 1
251 );
252 COMMIT;
253
254 RETURN('Y');
255
256 END PRMTN_PLN_DUPS_FNC;
257
258
259 FUNCTION RTL_ORDR_ITEM_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
260 AS
261 v_load_id NUMBER := NVL(p_load_id, set_load_id);
262 BEGIN
263 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
264
265
266 INSERT INTO DDR_E_RTL_ORDR_ITEM
267 (REC_ID, LOAD_ID, ERR_REASON,
268 RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
269 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, UOM,
270 ORDR_QTY,ORDR_AMT,SRC_SYS_IDNT,
271 SRC_SYS_DT, TRANS_DT, SRC_IDNT_FLAG,
272 ACTION_FLAG, RTL_ORG_CD
273 )
274 SELECT REC_ID, v_load_id,'Duplicate Record',
275 RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
276 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, UOM,
277 ORDR_QTY,ORDR_AMT,SRC_SYS_IDNT,
278 SRC_SYS_DT, TRANS_DT,'I','N', RTL_ORG_CD
279 FROM DDR_I_RTL_ORDR_ITEM
280 WHERE
281 (RTL_BSNS_UNIT_CD,
282 GLBL_ITEM_ID,
283 RTL_SKU_ITEM_NBR,
284 GLBL_ITEM_ID_TYP,
285 TRANS_DT, RTL_ORG_CD)
286 IN (
287 SELECT
288 RTL_BSNS_UNIT_CD,
289 GLBL_ITEM_ID,
290 RTL_SKU_ITEM_NBR,
291 GLBL_ITEM_ID_TYP,
292 TRANS_DT, RTL_ORG_CD
293 FROM DDR_I_RTL_ORDR_ITEM
294 GROUP BY
295 RTL_BSNS_UNIT_CD,
296 GLBL_ITEM_ID,
297 RTL_SKU_ITEM_NBR,
298 GLBL_ITEM_ID_TYP,
299 TRANS_DT, RTL_ORG_CD
300 HAVING COUNT(*) > 1
301 );
302 COMMIT;
303
304
305 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
306
307
308 DELETE FROM DDR_I_RTL_ORDR_ITEM
309 WHERE
310 (RTL_BSNS_UNIT_CD,
311 GLBL_ITEM_ID,
312 RTL_SKU_ITEM_NBR,
313 GLBL_ITEM_ID_TYP,
314 TRANS_DT, RTL_ORG_CD)
315 IN (
316 SELECT
317 RTL_BSNS_UNIT_CD,
318 GLBL_ITEM_ID,
319 RTL_SKU_ITEM_NBR,
320 GLBL_ITEM_ID_TYP,
321 TRANS_DT, RTL_ORG_CD
322 FROM DDR_E_RTL_ORDR_ITEM
323 GROUP BY
324 RTL_BSNS_UNIT_CD,
325 GLBL_ITEM_ID,
326 RTL_SKU_ITEM_NBR,
327 GLBL_ITEM_ID_TYP,
328 TRANS_DT, RTL_ORG_CD
329 HAVING COUNT(*) > 1
330 );
331 COMMIT;
332
333 RETURN('Y');
334
335 END RTL_ORDR_ITEM_DUPS_FNC;
336
337 FUNCTION RTL_SHIP_ITEM_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
338 AS
339 v_load_id NUMBER := NVL(p_load_id, set_load_id);
340 BEGIN
341 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
342
343
344 INSERT INTO DDR_E_RTL_SHIP_ITEM
345 (REC_ID, LOAD_ID, ERR_REASON,
346 RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
347 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
348 UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
349 SRC_SYS_DT, SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT,
350 RTL_ORG_CD, SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD, SHIP_COST
351 )
352 SELECT REC_ID, v_load_id,'Duplicate Record',
353 RTL_BSNS_UNIT_CD,GLBL_ITEM_ID,
354 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
355 UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
356 SRC_SYS_DT,'I','N', TRANS_DT,
357 RTL_ORG_CD, SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD, SHIP_COST
358 FROM DDR_I_RTL_SHIP_ITEM
359 WHERE
360 (GLBL_ITEM_ID,
361 RTL_BSNS_UNIT_CD,
362 RTL_SKU_ITEM_NBR,
363 GLBL_ITEM_ID_TYP,
364 TRANS_DT, RTL_ORG_CD,
365 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD )
366 IN (
367 SELECT
368 GLBL_ITEM_ID,
369 RTL_BSNS_UNIT_CD,
370 RTL_SKU_ITEM_NBR,
371 GLBL_ITEM_ID_TYP,
372 TRANS_DT, RTL_ORG_CD,
373 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD
374 FROM DDR_I_RTL_SHIP_ITEM
375 GROUP BY
376 GLBL_ITEM_ID,
377 RTL_BSNS_UNIT_CD,
378 RTL_SKU_ITEM_NBR,
379 GLBL_ITEM_ID_TYP,
380 TRANS_DT, RTL_ORG_CD,
381 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD
382 HAVING COUNT(*) > 1
383 );
384 COMMIT;
385
386
387 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
388
389
390 DELETE FROM DDR_I_RTL_SHIP_ITEM
391 WHERE
392 (GLBL_ITEM_ID,
393 RTL_BSNS_UNIT_CD,
394 RTL_SKU_ITEM_NBR,
395 GLBL_ITEM_ID_TYP,
396 TRANS_DT, RTL_ORG_CD,
397 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD)
398 IN (
399 SELECT
400 GLBL_ITEM_ID,
401 RTL_BSNS_UNIT_CD,
402 RTL_SKU_ITEM_NBR,
403 GLBL_ITEM_ID_TYP,
404 TRANS_DT, RTL_ORG_CD,
405 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD
406 FROM DDR_E_RTL_SHIP_ITEM
407 GROUP BY
408 GLBL_ITEM_ID,
409 RTL_BSNS_UNIT_CD,
410 RTL_SKU_ITEM_NBR,
411 GLBL_ITEM_ID_TYP,
412 TRANS_DT, RTL_ORG_CD,
413 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD
414 HAVING COUNT(*) > 1
415 );
416 COMMIT;
417
418 RETURN('Y');
419
420 END RTL_SHIP_ITEM_DUPS_FNC;
421
422 FUNCTION RTL_SL_RTN_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
423 AS
424 v_load_id NUMBER := NVL(p_load_id, set_load_id);
425 v_FUNC_MSG VARCHAR2(1) := 'N';
426
427 BEGIN
428
429 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
430
431
432 INSERT INTO DDR_E_RTL_SL_RTN_ITEM
433 (REC_ID, LOAD_ID, ERR_REASON,
434 GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
435 GLBL_ITEM_ID_TYP, UOM, SLS_QTY,
436 SLS_AMT, SLS_COST_AMT,RTRN_QTY,
437 RTRN_AMT,RTRN_COST_AMT,SRC_SYS_IDNT,
438 SRC_SYS_DT, PERIOD_TYP_FLAG,
439 LOC_IDNT_CD, LOC_IDNT_FLAG,
440 ORG_LVL_CD,SRC_IDNT_FLAG, ACTION_FLAG,
444 GLBL_ITEM_ID_TYP, UOM, SLS_QTY,
441 TRANS_DT, PRMTN_FLAG, RTL_ORG_CD)
442 SELECT REC_ID, v_load_id,'Duplicate Record',
443 GLBL_ITEM_ID,RTL_SKU_ITEM_NBR,
445 SLS_AMT,SLS_COST_AMT,
446 RTRN_QTY, RTRN_AMT,
447 RTRN_COST_AMT, SRC_SYS_IDNT,
448 SRC_SYS_DT, PERIOD_TYP_FLAG,
449 LOC_IDNT_CD, LOC_IDNT_FLAG,
450 ORG_LVL_CD,'I','N',TRANS_DT, PRMTN_FLAG, RTL_ORG_CD
451 FROM DDR_I_RTL_SL_RTN_ITEM
452 WHERE
453 (LOC_IDNT_CD, GLBL_ITEM_ID,
454 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
455 TRANS_DT, PERIOD_TYP_FLAG,
456 LOC_IDNT_FLAG, ORG_LVL_CD, RTL_ORG_CD)
457 IN (
458 SELECT
459 LOC_IDNT_CD, GLBL_ITEM_ID,
460 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
461 TRANS_DT, PERIOD_TYP_FLAG,
462 LOC_IDNT_FLAG, ORG_LVL_CD, RTL_ORG_CD
463 FROM DDR_I_RTL_SL_RTN_ITEM
464 GROUP BY
465 LOC_IDNT_CD, GLBL_ITEM_ID,
466 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
467 TRANS_DT, PERIOD_TYP_FLAG,
468 LOC_IDNT_FLAG, ORG_LVL_CD, RTL_ORG_CD
469 HAVING COUNT(*) > 1
470 );
471 COMMIT;
472
473
474 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
475
476
477 DELETE FROM DDR_I_RTL_SL_RTN_ITEM
478 WHERE
479 (LOC_IDNT_CD, GLBL_ITEM_ID,
480 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
481 TRANS_DT, PERIOD_TYP_FLAG,
482 LOC_IDNT_FLAG, ORG_LVL_CD, RTL_ORG_CD)
483 IN (
484 SELECT
485 LOC_IDNT_CD, GLBL_ITEM_ID,
486 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
487 TRANS_DT, PERIOD_TYP_FLAG,
488 LOC_IDNT_FLAG, ORG_LVL_CD, RTL_ORG_CD
489 FROM DDR_E_RTL_SL_RTN_ITEM
490 GROUP BY
491 LOC_IDNT_CD, GLBL_ITEM_ID,
492 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
493 TRANS_DT, PERIOD_TYP_FLAG,
494 LOC_IDNT_FLAG, ORG_LVL_CD, RTL_ORG_CD
495 HAVING COUNT(*) > 1
496 );
497 COMMIT;
498
499 RETURN('Y');
500
501 END RTL_SL_RTN_DUPS_FNC;
502
503 FUNCTION SLS_FRCST_ITEM_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
504 AS
505 v_load_id NUMBER := NVL(p_load_id, set_load_id);
506 BEGIN
507 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
508
509
510 INSERT INTO DDR_E_SLS_FRCST_ITEM
511 (REC_ID, LOAD_ID, ERR_REASON,
512 FRCST_SLS_UOM, FRCST_NBR,
513 FRCST_TYP, GLBL_ITEM_ID,
514 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
515 FRCST_SLS_QTY, FRCST_SLS_AMT,
516 SRC_SYS_IDNT, SRC_SYS_DT,
517 PERIOD_TYP_FLAG, LOC_IDNT_CD,
518 LOC_IDNT_FLAG, ORG_LVL_CD,
519 SRC_IDNT_FLAG, ACTION_FLAG,
520 TRANS_DT, RTL_ORG_CD,FRCST_PURP
521 )
522 SELECT REC_ID,v_load_id,'Duplicate Record',
523 FRCST_SLS_UOM, FRCST_NBR,
524 FRCST_TYP, GLBL_ITEM_ID,
525 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP,
526 FRCST_SLS_QTY, FRCST_SLS_AMT,
527 SRC_SYS_IDNT, SRC_SYS_DT,
528 PERIOD_TYP_FLAG, LOC_IDNT_CD,
529 LOC_IDNT_FLAG, ORG_LVL_CD,
530 'I','N', TRANS_DT, RTL_ORG_CD,FRCST_PURP
531 FROM DDR_I_SLS_FRCST_ITEM
532 WHERE
533 (TRANS_DT, FRCST_NBR,
534 FRCST_TYP, LOC_IDNT_CD,
535 GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
536 GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
537 LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT, RTL_ORG_CD,FRCST_PURP)
538 IN (
539 SELECT
540 TRANS_DT, FRCST_NBR,
541 FRCST_TYP, LOC_IDNT_CD,
542 GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
543 GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
544 LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT, RTL_ORG_CD,FRCST_PURP
545 FROM DDR_I_SLS_FRCST_ITEM
546 GROUP BY
547 TRANS_DT, FRCST_NBR,
548 FRCST_TYP, LOC_IDNT_CD,
549 GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
550 GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
551 LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT, RTL_ORG_CD,FRCST_PURP
552 HAVING COUNT(*) > 1
553 );
554 COMMIT;
555
556
557 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
558
559
560 DELETE FROM DDR_I_SLS_FRCST_ITEM
561 WHERE
562 (TRANS_DT, FRCST_NBR,
563 FRCST_TYP, LOC_IDNT_CD,
564 GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
565 GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
566 LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT, RTL_ORG_CD,FRCST_PURP)
567 IN (
568 SELECT
569 TRANS_DT, FRCST_NBR, FRCST_TYP, LOC_IDNT_CD,
570 GLBL_ITEM_ID, RTL_SKU_ITEM_NBR,
571 GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
572 LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT, RTL_ORG_CD,FRCST_PURP
573 FROM DDR_E_SLS_FRCST_ITEM
574 GROUP BY
575 TRANS_DT, FRCST_NBR, FRCST_TYP,
576 LOC_IDNT_CD, GLBL_ITEM_ID,
577 RTL_SKU_ITEM_NBR, GLBL_ITEM_ID_TYP, PERIOD_TYP_FLAG,
578 LOC_IDNT_FLAG, ORG_LVL_CD, SRC_SYS_DT, RTL_ORG_CD,FRCST_PURP
579 HAVING COUNT(*) > 1
580 );
581 COMMIT;
582
583 RETURN('Y');
584
585 END SLS_FRCST_ITEM_DUPS_FNC;
586
587 FUNCTION decide_dedup_chk RETURN VARCHAR2 IS
588 RT VARCHAR2(5);
589 BEGIN
590 select LKUP_NAME into RT from DDR_R_LKUP_MST
591 where LKUP_TYP_CD='SYS_PARAM'
592 and LKUP_CD='PERFORM_DUP_CHECK';
593
594 RETURN RT;
595 END decide_dedup_chk;
596
597 FUNCTION decide_discover_mode RETURN VARCHAR2 IS
598 RT VARCHAR2(5);
599 BEGIN
600 select LKUP_NAME into RT from DDR_R_LKUP_MST
601 where LKUP_TYP_CD='SYS_PARAM'
602 and LKUP_CD='DISCOVERY_MODE';
603 RETURN RT;
604 END decide_discover_mode;
605
609 BEGIN
606 FUNCTION DECIDE_RUN_MAP_ERR(map_nm IN VARCHAR2, map_stg IN VARCHAR2) RETURN NUMBER IS
607 cnt_rows Number;
608 tab_name_ Varchar2(250);
610 cnt_rows := 0;
611 tab_name_ := 'select count(1) from '||map_nm || ', ' || map_stg || ' where '|| map_nm||'.load_id = '|| map_stg || '.load_id and rownum <2';
612 execute immediate tab_name_ into cnt_rows;
613 RETURN cnt_rows;
614 END DECIDE_RUN_MAP_ERR;
615
616 FUNCTION decide_run_typ RETURN VARCHAR2 IS
617 RT VARCHAR2(5);
618 BEGIN
619 select LKUP_NAME into RT from DDR_R_LKUP_MST
620 where LKUP_TYP_CD='SYS_PARAM'
621 and LKUP_CD='RUN_TYPE_FACT';
622
623 RETURN RT;
624 END decide_run_typ;
625
626 FUNCTION decide_SF_MAP RETURN VARCHAR2 IS
627 RT VARCHAR2(5);
628 BEGIN
629 select LKUP_NAME into RT from DDR_R_LKUP_MST
630 where LKUP_TYP_CD='SYS_PARAM'
631 and LKUP_CD='STAGE_TO_TARGET_VALIDATION';
632
633 RETURN RT;
634 END decide_SF_MAP;
635
636 FUNCTION DECIDE_RUN_CNT_STG(map_nm IN VARCHAR2) RETURN NUMBER IS
637 cnt_rows Number;
638 tab_name_ Varchar2(250);
639 BEGIN
640 cnt_rows := 0;
641 tab_name_ := 'select count(1) from '||map_nm|| ' where rownum <2';
642 execute immediate tab_name_ into cnt_rows;
643 RETURN cnt_rows;
644 END DECIDE_RUN_CNT_STG;
645
646 FUNCTION DECIDE_RUN_MAP(map_nm IN VARCHAR2) RETURN NUMBER IS
647 cnt_rows Number;
648 tab_name_ Varchar2(250);
649 BEGIN
650 cnt_rows := 0;
651 tab_name_ := 'select count(1) from '||map_nm|| ' where rownum <2';
652 execute immediate tab_name_ into cnt_rows;
653 RETURN cnt_rows;
654 END DECIDE_RUN_MAP;
655
656 PROCEDURE trunc_tble_pub( p_tbl_name IN VARCHAR2)
657 as
658 Begin
659 EXECUTE IMMEDIATE 'TRUNCATE TABLE '|| 'DDR.' || p_tbl_name;
660 End trunc_tble_pub;
661
662
663 FUNCTION get_map_run_id(p_audit_id VARCHAR2) RETURN NUMBER IS
664
665 BEGIN
666 IF p_audit_id = c_audit_id THEN --{
667 RETURN c_map_id;
668 ELSE --}{
669 c_audit_id := p_audit_id;
670 c_map_id := set_load_id;
671 RETURN c_map_id;
672 END IF; --}
673
674 END get_map_run_id;
675
676 FUNCTION rtl_sl_rtn_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
677 AS
678 v_load_id NUMBER := NVL(p_load_id, set_load_id);
679 v_FUNC_MSG VARCHAR2(1) := 'N';
680
681 CURSOR c1 IS
682 SELECT *
683 FROM ddr_s_rtl_sl_rtn_item
684 WHERE (day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id)
685 IN (SELECT day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id
686 FROM ddr_s_rtl_sl_rtn_item
687 GROUP BY day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id
688 HAVING count(*) > 1)
689 FOR UPDATE;
690 BEGIN
691
692 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
693
694 FOR rec IN c1 LOOP --{
695 INSERT INTO ddr_e_rtl_sl_rtn_item
696 (crncy_cd
697 ,day_cd
698 ,eff_from_dt
699 ,eff_to_dt
700 ,glbl_item_id
701 ,glbl_item_id_typ
702 ,item_bsns_unt_assc_id
703 ,itm_typ
704 ,load_id
705 ,mfg_org_cd
706 ,mfg_sku_item_id
707 ,mfg_sku_item_nbr
708 ,org_bsns_unit_id
709 ,prmtn_flag
710 ,rec_id
711 ,rtl_bsns_unit_cd
712 ,rtl_org_cd
713 ,rtl_sku_item_id
714 ,rtl_sku_item_nbr
715 ,rtrn_amt
716 ,rtrn_amt_lcl
717 ,rtrn_amt_rpt
718 ,rtrn_cost_amt
719 ,rtrn_cost_amt_lcl
720 ,rtrn_cost_amt_rpt
721 ,rtrn_qty
722 ,rtrn_qty_alt
723 ,rtrn_qty_prmry
724 ,sls_amt
725 ,sls_amt_lcl
726 ,sls_amt_rpt
727 ,sls_cost_amt
728 ,sls_cost_amt_lcl
729 ,sls_cost_amt_rpt
730 ,sls_qty
731 ,sls_qty_alt
732 ,sls_qty_prmry
733 ,src_sys_dt
734 ,src_sys_idnt
735 ,trans_dt
736 ,uom_cd
737 ,uom_cd_alt
738 ,uom_cd_prmry
739 ,src_idnt_flag
740 ,action_flag
741 ,err_reason
742 ) VALUES
743 (rec.crncy_cd
744 ,rec.day_cd
745 ,rec.eff_from_dt
746 ,rec.eff_to_dt
747 ,rec.glbl_item_id
748 ,rec.glbl_item_id_typ
749 ,rec.item_bsns_unt_assc_id
750 ,rec.itm_typ
751 ,v_load_id
752 ,rec.mfg_org_cd
753 ,rec.mfg_sku_item_id
754 ,rec.mfg_sku_item_nbr
755 ,rec.org_bsns_unit_id
756 ,rec.prmtn_flag
757 ,rec.rec_id
758 ,rec.rtl_bsns_unit_cd
759 ,rec.rtl_org_cd
760 ,rec.rtl_sku_item_id
761 ,rec.rtl_sku_item_nbr
762 ,rec.rtrn_amt
763 ,rec.rtrn_amt_lcl
764 ,rec.rtrn_amt_rpt
765 ,rec.rtrn_cost_amt
766 ,rec.rtrn_cost_amt_lcl
767 ,rec.rtrn_cost_amt_rpt
768 ,rec.rtrn_qty
769 ,rec.rtrn_qty_alt
770 ,rec.rtrn_qty_prmry
771 ,rec.sls_amt
772 ,rec.sls_amt_lcl
773 ,rec.sls_amt_rpt
774 ,rec.sls_cost_amt
775 ,rec.sls_cost_amt_lcl
776 ,rec.sls_cost_amt_rpt
777 ,rec.sls_qty
778 ,rec.sls_qty_alt
779 ,rec.sls_qty_prmry
780 ,rec.src_sys_dt
781 ,rec.src_sys_idnt
782 ,rec.trans_dt
783 ,rec.uom_cd
784 ,rec.uom_cd_alt
785 ,rec.uom_cd_prmry
786 ,'S'
787 ,'N'
788 ,'Duplicate record - Staging'
789 );
790
791 -- delete duplicate records from staging table --
792 DELETE ddr_s_rtl_sl_rtn_item
793 WHERE CURRENT OF c1;
794
795 END LOOP; --}
796
797 COMMIT;
798
802
799 RETURN('Y');
800
801 END rtl_sl_rtn_dups_s_fnc;
803
804 FUNCTION rtl_inv_item_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
805 AS
806 v_load_id NUMBER := NVL(p_load_id, set_load_id);
807 v_FUNC_MSG VARCHAR2(1) := 'N';
808
809 CURSOR c1 IS
810 SELECT *
811 FROM ddr_s_rtl_inv_item
812 WHERE (day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,inv_loc_id)
813 IN (SELECT day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,inv_loc_id
814 FROM ddr_s_rtl_inv_item
815 GROUP BY day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,inv_loc_id
816 HAVING count(*) > 1)
817 FOR UPDATE;
818 BEGIN
819
820 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
821
822 FOR rec IN c1 LOOP --{
823 INSERT INTO ddr_e_rtl_inv_item
824 (bck_ordr_qty
825 ,bck_ordr_qty_alt
826 ,bck_ordr_qty_prmry
827 ,bckordr_net_cost_amt
828 ,bckordr_net_cost_amt_lcl
829 ,bckordr_net_cost_amt_rpt
830 ,bckordr_rtl_amt
831 ,bckordr_rtl_amt_lcl
832 ,bckordr_rtl_amt_rpt
833 ,crncy_cd
834 ,day_cd
835 ,glbl_item_id
836 ,glbl_item_id_typ
837 ,in_transit_net_cost_amt
838 ,in_transit_net_cost_amt_lcl
839 ,in_transit_net_cost_amt_rpt
840 ,in_transit_qty
841 ,in_transit_qty_alt
842 ,in_transit_qty_prmry
843 ,in_transit_rtl_amt
844 ,in_transit_rtl_amt_lcl
845 ,in_transit_rtl_amt_rpt
846 ,inv_loc_cd
847 ,inv_loc_id
848 ,inv_loc_typ_cd
849 ,itm_typ
850 ,load_id
851 ,mfg_org_cd
852 ,mfg_sku_item_id
853 ,on_hand_net_cost_amt
854 ,on_hand_net_cost_amt_lcl
855 ,on_hand_net_cost_amt_rpt
856 ,on_hand_qty
857 ,on_hand_qty_alt
858 ,on_hand_qty_prmry
859 ,on_hand_rtl_amt
860 ,on_hand_rtl_amt_lcl
861 ,on_hand_rtl_amt_rpt
862 ,org_bsns_unit_id
863 ,qlty_hold_net_cost_amt
864 ,qlty_hold_net_cost_amt_lcl
865 ,qlty_hold_net_cost_amt_rpt
866 ,qlty_hold_qty
867 ,qlty_hold_qty_alt
868 ,qlty_hold_qty_prmry
869 ,qlty_hold_rtl_amt
870 ,qlty_hold_rtl_amt_lcl
871 ,qlty_hold_rtl_amt_rpt
872 ,rec_id
873 ,recvd_net_cost_amt
874 ,recvd_net_cost_amt_lcl
875 ,recvd_net_cost_amt_rpt
876 ,recvd_qty
877 ,recvd_qty_alt
878 ,recvd_qty_prmry
879 ,recvd_rtl_amt
880 ,recvd_rtl_amt_lcl
881 ,recvd_rtl_amt_rpt
882 ,rtl_bsns_unit_cd
883 ,rtl_org_cd
884 ,rtl_sku_item_id
885 ,rtl_sku_item_nbr
886 ,src_sys_dt
887 ,src_sys_idnt
888 ,trans_dt
889 ,uom_cd
890 ,uom_cd_alt
891 ,uom_cd_prmry
892 ,src_idnt_flag
893 ,action_flag
894 ,err_reason
895 ) VALUES
896 (rec.bck_ordr_qty
897 ,rec.bck_ordr_qty_alt
898 ,rec.bck_ordr_qty_prmry
899 ,rec.bckordr_net_cost_amt
900 ,rec.bckordr_net_cost_amt_lcl
901 ,rec.bckordr_net_cost_amt_rpt
902 ,rec.bckordr_rtl_amt
903 ,rec.bckordr_rtl_amt_lcl
904 ,rec.bckordr_rtl_amt_rpt
905 ,rec.crncy_cd
906 ,rec.day_cd
907 ,rec.glbl_item_id
908 ,rec.glbl_item_id_typ
909 ,rec.in_transit_net_cost_amt
910 ,rec.in_transit_net_cost_amt_lcl
911 ,rec.in_transit_net_cost_amt_rpt
912 ,rec.in_transit_qty
913 ,rec.in_transit_qty_alt
914 ,rec.in_transit_qty_prmry
915 ,rec.in_transit_rtl_amt
916 ,rec.in_transit_rtl_amt_lcl
917 ,rec.in_transit_rtl_amt_rpt
918 ,rec.inv_loc_cd
919 ,rec.inv_loc_id
920 ,rec.inv_loc_typ_cd
921 ,rec.itm_typ
922 ,v_load_id
923 ,rec.mfg_org_cd
924 ,rec.mfg_sku_item_id
925 ,rec.on_hand_net_cost_amt
926 ,rec.on_hand_net_cost_amt_lcl
927 ,rec.on_hand_net_cost_amt_rpt
928 ,rec.on_hand_qty
929 ,rec.on_hand_qty_alt
930 ,rec.on_hand_qty_prmry
931 ,rec.on_hand_rtl_amt
932 ,rec.on_hand_rtl_amt_lcl
933 ,rec.on_hand_rtl_amt_rpt
934 ,rec.org_bsns_unit_id
935 ,rec.qlty_hold_net_cost_amt
936 ,rec.qlty_hold_net_cost_amt_lcl
937 ,rec.qlty_hold_net_cost_amt_rpt
938 ,rec.qlty_hold_qty
939 ,rec.qlty_hold_qty_alt
940 ,rec.qlty_hold_qty_prmry
941 ,rec.qlty_hold_rtl_amt
942 ,rec.qlty_hold_rtl_amt_lcl
943 ,rec.qlty_hold_rtl_amt_rpt
944 ,rec.rec_id
945 ,rec.recvd_net_cost_amt
946 ,rec.recvd_net_cost_amt_lcl
947 ,rec.recvd_net_cost_amt_rpt
948 ,rec.recvd_qty
949 ,rec.recvd_qty_alt
950 ,rec.recvd_qty_prmry
951 ,rec.recvd_rtl_amt
952 ,rec.recvd_rtl_amt_lcl
953 ,rec.recvd_rtl_amt_rpt
954 ,rec.rtl_bsns_unit_cd
955 ,rec.rtl_org_cd
956 ,rec.rtl_sku_item_id
957 ,rec.rtl_sku_item_nbr
958 ,rec.src_sys_dt
959 ,rec.src_sys_idnt
960 ,rec.trans_dt
961 ,rec.uom_cd
962 ,rec.uom_cd_alt
963 ,rec.uom_cd_prmry
964 ,'S'
965 ,'N'
966 ,'Duplicate record - Staging'
967 );
968
969 -- delete duplicate records from staging table --
970 DELETE ddr_s_rtl_inv_item
971 WHERE CURRENT OF c1;
972
973 END LOOP; --}
974
975 COMMIT;
976
977 RETURN('Y');
978
979 END rtl_inv_item_dups_s_fnc;
980
981 FUNCTION rtl_ship_item_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
982 AS
986 CURSOR c1 IS
983 v_load_id NUMBER := NVL(p_load_id, set_load_id);
984 v_FUNC_MSG VARCHAR2(1) := 'N';
985
987 SELECT *
988 FROM ddr_s_rtl_ship_item
989 WHERE
990 (day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,ship_to_bsns_unit_id)
991 IN (SELECT day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,ship_to_bsns_unit_id
992 FROM ddr_s_rtl_ship_item
993 GROUP BY day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,ship_to_bsns_unit_id
994 HAVING count(*) > 1)
995 FOR UPDATE;
996 BEGIN
997
998 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
999
1000 FOR rec IN c1 LOOP --{
1001 INSERT INTO ddr_e_rtl_ship_item
1002 (crncy_cd
1003 ,day_cd
1004 ,glbl_item_id
1005 ,glbl_item_id_typ
1006 ,itm_typ
1007 ,load_id
1008 ,mfg_org_cd
1009 ,mfg_sku_item_id
1010 ,org_bsns_unit_id
1011 ,rec_id
1012 ,rtl_bsns_unit_cd
1013 ,rtl_org_cd
1014 ,rtl_sku_item_id
1015 ,rtl_sku_item_nbr
1016 ,ship_amt
1017 ,ship_amt_lcl
1018 ,ship_amt_rpt
1019 ,ship_qty
1020 ,ship_qty_alt
1021 ,ship_qty_prmry
1022 ,src_sys_dt
1023 ,src_sys_idnt
1024 ,trans_dt
1025 ,uom_cd
1026 ,uom_cd_alt
1027 ,uom_cd_prmry
1028 ,src_idnt_flag
1029 ,action_flag
1030 ,err_reason
1031 ,ship_to_org_cd
1032 ,ship_to_bsns_unit_id
1033 ,ship_to_bsns_unit_cd
1034 ,ship_cost
1035 ,ship_cost_rpt
1036 ,ship_cost_lcl
1037 ) VALUES
1038 (rec.crncy_cd
1039 ,rec.day_cd
1040 ,rec.glbl_item_id
1041 ,rec.glbl_item_id_typ
1042 ,rec.itm_typ
1043 ,v_load_id
1044 ,rec.mfg_org_cd
1045 ,rec.mfg_sku_item_id
1046 ,rec.org_bsns_unit_id
1047 ,rec.rec_id
1048 ,rec.rtl_bsns_unit_cd
1049 ,rec.rtl_org_cd
1050 ,rec.rtl_sku_item_id
1051 ,rec.rtl_sku_item_nbr
1052 ,rec.ship_amt
1053 ,rec.ship_amt_lcl
1054 ,rec.ship_amt_rpt
1055 ,rec.ship_qty
1056 ,rec.ship_qty_alt
1057 ,rec.ship_qty_prmry
1058 ,rec.src_sys_dt
1059 ,rec.src_sys_idnt
1060 ,rec.trans_dt
1061 ,rec.uom_cd
1062 ,rec.uom_cd_alt
1063 ,rec.uom_cd_prmry
1064 ,'S'
1065 ,'N'
1066 ,'Duplicate record - Staging'
1067 ,rec.ship_to_org_cd
1068 ,rec.ship_to_bsns_unit_id
1069 ,rec.ship_to_bsns_unit_cd
1070 ,rec.ship_cost
1071 ,rec.ship_cost_rpt
1072 ,rec.ship_cost_lcl
1073 );
1074
1075 -- delete duplicate records from staging table --
1076 DELETE ddr_s_rtl_ship_item
1077 WHERE CURRENT OF c1;
1078
1079 END LOOP; --}
1080
1081 COMMIT;
1082
1083 RETURN('Y');
1084
1085 END rtl_ship_item_dups_s_fnc;
1086
1087 FUNCTION rtl_ordr_item_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
1088 AS
1089 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1090 v_FUNC_MSG VARCHAR2(1) := 'N';
1091
1092 CURSOR c1 IS
1093 SELECT *
1094 FROM ddr_s_rtl_ordr_item
1095 WHERE (day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id)
1096 IN (SELECT day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id
1097 FROM ddr_s_rtl_ordr_item
1098 GROUP BY day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id
1099 HAVING count(*) > 1)
1100 FOR UPDATE;
1101 BEGIN
1102
1103 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1104
1105 FOR rec IN c1 LOOP --{
1106 INSERT INTO ddr_e_rtl_ordr_item
1107 (crncy_cd
1108 ,day_cd
1109 ,glbl_item_id
1110 ,glbl_item_id_typ
1111 ,itm_typ
1112 ,load_id
1113 ,mfg_org_cd
1114 ,mfg_sku_item_id
1115 ,ordr_amt
1116 ,ordr_amt_lcl
1117 ,ordr_amt_rpt
1118 ,ordr_qty
1119 ,ordr_qty_alt
1120 ,ordr_qty_prmry
1121 ,org_bsns_unit_id
1122 ,rec_id
1123 ,rtl_bsns_unit_cd
1124 ,rtl_org_cd
1125 ,rtl_sku_item_id
1126 ,rtl_sku_item_nbr
1127 ,src_sys_dt
1128 ,src_sys_idnt
1129 ,trans_dt
1130 ,uom_cd
1131 ,uom_cd_alt
1132 ,uom_cd_prmry
1133 ,src_idnt_flag
1134 ,action_flag
1135 ,err_reason
1136 ) VALUES
1137 (rec.crncy_cd
1138 ,rec.day_cd
1139 ,rec.glbl_item_id
1140 ,rec.glbl_item_id_typ
1141 ,rec.itm_typ
1142 ,v_load_id
1143 ,rec.mfg_org_cd
1144 ,rec.mfg_sku_item_id
1145 ,rec.ordr_amt
1146 ,rec.ordr_amt_lcl
1147 ,rec.ordr_amt_rpt
1148 ,rec.ordr_qty
1149 ,rec.ordr_qty_alt
1150 ,rec.ordr_qty_prmry
1151 ,rec.org_bsns_unit_id
1152 ,rec.rec_id
1153 ,rec.rtl_bsns_unit_cd
1154 ,rec.rtl_org_cd
1155 ,rec.rtl_sku_item_id
1156 ,rec.rtl_sku_item_nbr
1157 ,rec.src_sys_dt
1158 ,rec.src_sys_idnt
1159 ,rec.trans_dt
1160 ,rec.uom_cd
1161 ,rec.uom_cd_alt
1162 ,rec.uom_cd_prmry
1163 ,'S'
1164 ,'N'
1165 ,'Duplicate record - Staging'
1166 );
1167
1168 -- delete duplicate records from staging table --
1169 DELETE ddr_s_rtl_ordr_item
1170 WHERE CURRENT OF c1;
1171
1172 END LOOP; --}
1173
1174 COMMIT;
1175
1176 RETURN('Y');
1177
1178 END rtl_ordr_item_dups_s_fnc;
1179
1180 FUNCTION sls_frcst_item_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
1181 AS
1182 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1186 SELECT *
1183 v_FUNC_MSG VARCHAR2(1) := 'N';
1184
1185 CURSOR c1 IS
1187 FROM ddr_s_sls_frcst_item
1188 WHERE (frcst_vrsn,day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,frcst_purp,frcst_typ)
1189 IN (SELECT frcst_vrsn,day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,frcst_purp,frcst_typ
1190 FROM ddr_s_sls_frcst_item
1191 GROUP BY frcst_vrsn,day_cd,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,frcst_purp,frcst_typ
1192 HAVING count(*) > 1)
1193 FOR UPDATE;
1194 BEGIN
1195
1196 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1197
1198 FOR rec IN c1 LOOP --{
1199 INSERT INTO ddr_e_sls_frcst_item
1200 (crncy_cd
1201 ,day_cd
1202 ,frcst_nbr
1203 ,frcst_sls_amt
1204 ,frcst_sls_amt_lcl
1205 ,frcst_sls_amt_rpt
1206 ,frcst_sls_qty
1207 ,frcst_sls_qty_alt
1208 ,frcst_sls_qty_prmry
1209 ,frcst_sls_uom_cd
1210 ,frcst_sls_uom_cd_alt
1211 ,frcst_sls_uom_cd_prmry
1212 ,frcst_typ
1213 ,frcst_vrsn
1214 ,glbl_item_id
1215 ,glbl_item_id_typ
1216 ,itm_typ
1217 ,load_id
1218 ,mfg_org_cd
1219 ,mfg_sku_item_id
1220 ,org_bsns_unit_id
1221 ,rec_id
1222 ,bsns_unit_cd
1223 ,rtl_org_cd
1224 ,rtl_sku_item_id
1225 ,rtl_sku_item_nbr
1226 ,src_sys_dt
1227 ,src_sys_idnt
1228 ,trans_dt
1229 ,src_idnt_flag
1230 ,action_flag
1231 ,err_reason
1232 ,frcst_purp
1233 ) VALUES
1234 (rec.crncy_cd
1235 ,rec.day_cd
1236 ,rec.frcst_nbr
1237 ,rec.frcst_sls_amt
1238 ,rec.frcst_sls_amt_lcl
1239 ,rec.frcst_sls_amt_rpt
1240 ,rec.frcst_sls_qty
1241 ,rec.frcst_sls_qty_alt
1242 ,rec.frcst_sls_qty_prmry
1243 ,rec.frcst_sls_uom_cd
1244 ,rec.frcst_sls_uom_cd_alt
1245 ,rec.frcst_sls_uom_cd_prmry
1246 ,rec.frcst_typ
1247 ,rec.frcst_vrsn
1248 ,rec.glbl_item_id
1249 ,rec.glbl_item_id_typ
1250 ,rec.itm_typ
1251 ,v_load_id
1252 ,rec.mfg_org_cd
1253 ,rec.mfg_sku_item_id
1254 ,rec.org_bsns_unit_id
1255 ,rec.rec_id
1256 ,rec.bsns_unit_cd
1257 ,rec.rtl_org_cd
1258 ,rec.rtl_sku_item_id
1259 ,rec.rtl_sku_item_nbr
1260 ,rec.src_sys_dt
1261 ,rec.src_sys_idnt
1262 ,rec.trans_dt
1263 ,'S'
1264 ,'N'
1265 ,'Duplicate record - Staging'
1266 ,rec.frcst_purp
1267 );
1268
1269 -- delete duplicate records from staging table --
1270 DELETE ddr_s_sls_frcst_item
1271 WHERE CURRENT OF c1;
1272
1273 END LOOP; --}
1274
1275 COMMIT;
1276
1277 RETURN('Y');
1278
1279 END sls_frcst_item_dups_s_fnc;
1280
1281 FUNCTION prmtn_pln_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
1282 AS
1283 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1284 v_FUNC_MSG VARCHAR2(1) := 'N';
1285
1286 CURSOR c1 IS
1287 SELECT *
1288 FROM ddr_s_prmtn_pln
1289 WHERE (prmtn_typ,prmtn_from_dt,prmtn_to_dt,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id)
1290 IN (SELECT prmtn_typ,prmtn_from_dt,prmtn_to_dt,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id
1291 FROM ddr_s_prmtn_pln
1292 GROUP BY prmtn_typ,prmtn_from_dt,prmtn_to_dt,org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id
1293 HAVING count(*) > 1)
1294 FOR UPDATE;
1295 BEGIN
1296
1297 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1298
1299 FOR rec IN c1 LOOP --{
1300 INSERT INTO ddr_e_prmtn_pln
1301 (crncy_cd
1302 ,glbl_item_id
1303 ,glbl_item_id_typ
1304 ,itm_typ
1305 ,load_id
1306 ,mfg_org_cd
1307 ,mfg_sku_item_id
1308 ,org_bsns_unit_id
1309 ,prmtn_from_dt
1310 ,prmtn_price_amt
1311 ,prmtn_price_amt_lcl
1312 ,prmtn_price_amt_rpt
1313 ,prmtn_to_dt
1314 ,prmtn_typ
1315 ,rec_id
1316 ,rtl_bsns_unit_cd
1317 ,rtl_org_cd
1318 ,rtl_sku_item_id
1319 ,rtl_sku_item_nbr
1320 ,src_sys_dt
1321 ,src_sys_idnt
1322 ,trans_dt
1323 ,src_idnt_flag
1324 ,action_flag
1325 ,err_reason
1326 ,ATTRIBUTE1
1327 ,ATTRIBUTE2
1328 ,ATTRIBUTE3
1329 ,ATTRIBUTE4
1330 ,ATTRIBUTE5
1331 ,ATTRIBUTE6
1332 ,ATTRIBUTE7
1333 ,ATTRIBUTE8
1334 ,ATTRIBUTE9
1335 ,ATTRIBUTE10
1336 ,ATTRIBUTE11
1337 ,ATTRIBUTE12
1338 ,ATTRIBUTE13
1339 ,ATTRIBUTE14
1340 ,ATTRIBUTE15
1341 ,ATTRIBUTE16
1342 ,ATTRIBUTE17
1343 ,ATTRIBUTE18
1344 ,ATTRIBUTE19
1345 ,ATTRIBUTE20
1346 ) VALUES
1347 (rec.crncy_cd
1348 ,rec.glbl_item_id
1349 ,rec.glbl_item_id_typ
1350 ,rec.itm_typ
1351 ,v_load_id
1352 ,rec.mfg_org_cd
1353 ,rec.mfg_sku_item_id
1354 ,rec.org_bsns_unit_id
1355 ,rec.prmtn_from_dt
1356 ,rec.prmtn_price_amt
1357 ,rec.prmtn_price_amt_lcl
1358 ,rec.prmtn_price_amt_rpt
1359 ,rec.prmtn_to_dt
1360 ,rec.prmtn_typ
1361 ,rec.rec_id
1362 ,rec.rtl_bsns_unit_cd
1363 ,rec.rtl_org_cd
1364 ,rec.rtl_sku_item_id
1365 ,rec.rtl_sku_item_nbr
1366 ,rec.src_sys_dt
1367 ,rec.src_sys_idnt
1368 ,rec.trans_dt
1369 ,'S'
1370 ,'N'
1371 ,'Duplicate record - Staging'
1372 ,rec.ATTRIBUTE1
1373 ,rec.ATTRIBUTE2
1374 ,rec.ATTRIBUTE3
1375 ,rec.ATTRIBUTE4
1376 ,rec.ATTRIBUTE5
1377 ,rec.ATTRIBUTE6
1381 ,rec.ATTRIBUTE10
1378 ,rec.ATTRIBUTE7
1379 ,rec.ATTRIBUTE8
1380 ,rec.ATTRIBUTE9
1382 ,rec.ATTRIBUTE11
1383 ,rec.ATTRIBUTE12
1384 ,rec.ATTRIBUTE13
1385 ,rec.ATTRIBUTE14
1386 ,rec.ATTRIBUTE15
1387 ,rec.ATTRIBUTE16
1388 ,rec.ATTRIBUTE17
1389 ,rec.ATTRIBUTE18
1390 ,rec.ATTRIBUTE19
1391 ,rec.ATTRIBUTE20
1392 );
1393
1394 -- delete duplicate records from staging table --
1395 DELETE ddr_s_prmtn_pln
1396 WHERE CURRENT OF c1;
1397
1398 END LOOP; --}
1399
1400 COMMIT;
1401
1402 RETURN('Y');
1403
1404 END prmtn_pln_dups_s_fnc;
1405
1406
1407 FUNCTION SYND_CNSMPTN_DATA_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2 AS
1408
1409 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1410
1411 BEGIN
1412
1413 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1414
1415 INSERT INTO DDR_E_SYND_CNSMPTN_DATA
1416 (REC_ID, LOAD_ID, ERR_REASON,
1417 SRC_CD, PROD_KEY, MFG_ITM_HCHY_LVL,
1418 MFG_ITM_HCHY_CD, GEO_KEY, MKT_AREA_CD,MKT_AREA_TYP, RTL_ORG_HCHY_LVL,
1419 RTL_ORG_HCHY_CD, GEO_RGN_CD, GEO_SUB_RGN_CD, CHNL_TYP_CD,
1420 PERIOD_END_DATE, TIME_HCHY_LVL, SRC_SYS_IDNT,
1421 SRC_SYS_DT, SRC_IDNT_FLAG, ACTION_FLAG, MEASURE_SET,
1422 MEASURE1, MEASURE2, MEASURE3, MEASURE4, MEASURE5,
1423 MEASURE6, MEASURE7, MEASURE8, MEASURE9, MEASURE10,
1424 MEASURE11, MEASURE12, MEASURE13, MEASURE14, MEASURE15,
1425 MEASURE16, MEASURE17, MEASURE18, MEASURE19, MEASURE20,
1426 MEASURE21, MEASURE22, MEASURE23, MEASURE24, MEASURE25,
1427 MEASURE26, MEASURE27, MEASURE28, MEASURE29, MEASURE30,
1428 MEASURE31, MEASURE32, MEASURE33, MEASURE34, MEASURE35,
1429 MEASURE36, MEASURE37, MEASURE38, MEASURE39, MEASURE40,
1430 MEASURE41, MEASURE42, MEASURE43, MEASURE44, MEASURE45,
1431 MEASURE46, MEASURE47, MEASURE48, MEASURE49, MEASURE50,
1432 MEASURE51, MEASURE52, MEASURE53, MEASURE54, MEASURE55,
1433 MEASURE56, MEASURE57, MEASURE58, MEASURE59, MEASURE60,
1434 MEASURE61, MEASURE62, MEASURE63, MEASURE64, MEASURE65,
1435 MEASURE66, MEASURE67, MEASURE68, MEASURE69, MEASURE70,
1436 MEASURE71, MEASURE72, MEASURE73, MEASURE74, MEASURE75,
1437 MEASURE76, MEASURE77, MEASURE78, MEASURE79, MEASURE80,
1438 MEASURE81, MEASURE82, MEASURE83, MEASURE84, MEASURE85,
1439 MEASURE86, MEASURE87, MEASURE88, MEASURE89, MEASURE90,
1440 MEASURE91, MEASURE92, MEASURE93, MEASURE94, MEASURE95,
1441 MEASURE96, MEASURE97, MEASURE98, MEASURE99, MEASURE100,
1442 RTL_ORG_CD,AO_HCHY_CD,ACCT_CD
1443 )
1444 SELECT REC_ID, v_load_id,'Duplicate Record',
1445 SRC_CD, PROD_KEY, MFG_ITM_HCHY_LVL,
1446 MFG_ITM_HCHY_CD, GEO_KEY, MKT_AREA_CD,MKT_AREA_TYP, RTL_ORG_HCHY_LVL,
1447 RTL_ORG_HCHY_CD, GEO_RGN_CD, GEO_SUB_RGN_CD, CHNL_TYP_CD,
1448 PERIOD_END_DATE, TIME_HCHY_LVL, SRC_SYS_IDNT,
1449 SRC_SYS_DT, 'I','N', MEASURE_SET,
1450 MEASURE1, MEASURE2, MEASURE3, MEASURE4, MEASURE5,
1451 MEASURE6, MEASURE7, MEASURE8, MEASURE9, MEASURE10,
1452 MEASURE11, MEASURE12, MEASURE13, MEASURE14, MEASURE15,
1453 MEASURE16, MEASURE17, MEASURE18, MEASURE19, MEASURE20,
1454 MEASURE21, MEASURE22, MEASURE23, MEASURE24, MEASURE25,
1455 MEASURE26, MEASURE27, MEASURE28, MEASURE29, MEASURE30,
1456 MEASURE31, MEASURE32, MEASURE33, MEASURE34, MEASURE35,
1457 MEASURE36, MEASURE37, MEASURE38, MEASURE39, MEASURE40,
1458 MEASURE41, MEASURE42, MEASURE43, MEASURE44, MEASURE45,
1459 MEASURE46, MEASURE47, MEASURE48, MEASURE49, MEASURE50,
1460 MEASURE51, MEASURE52, MEASURE53, MEASURE54, MEASURE55,
1461 MEASURE56, MEASURE57, MEASURE58, MEASURE59, MEASURE60,
1462 MEASURE61, MEASURE62, MEASURE63, MEASURE64, MEASURE65,
1463 MEASURE66, MEASURE67, MEASURE68, MEASURE69, MEASURE70,
1464 MEASURE71, MEASURE72, MEASURE73, MEASURE74, MEASURE75,
1465 MEASURE76, MEASURE77, MEASURE78, MEASURE79, MEASURE80,
1466 MEASURE81, MEASURE82, MEASURE83, MEASURE84, MEASURE85,
1467 MEASURE86, MEASURE87, MEASURE88, MEASURE89, MEASURE90,
1468 MEASURE91, MEASURE92, MEASURE93, MEASURE94, MEASURE95,
1469 MEASURE96, MEASURE97, MEASURE98, MEASURE99, MEASURE100,
1470 RTL_ORG_CD,AO_HCHY_CD,ACCT_CD
1471 FROM DDR_I_SYND_CNSMPTN_DATA
1472 WHERE
1473 (MEASURE_SET, SRC_CD, NVL(PROD_KEY,'PROD'), NVL(GEO_KEY,'GEO'),
1474 NVL(CHNL_TYP_CD,'CHNL'), NVL(GEO_SUB_RGN_CD,'SUB'),
1475 NVL(GEO_RGN_CD,'RGN'), NVL(MFG_ITM_HCHY_LVL,'LVL'),
1476 NVL(MFG_ITM_HCHY_CD,'HCHY'), NVL(MKT_AREA_CD,'MKT'),NVL(MKT_AREA_TYP,'MKT_TYP'),
1477 NVL(RTL_ORG_HCHY_LVL,'ORG_LVL'), NVL(RTL_ORG_HCHY_CD,'ORG_HCHY'),
1478 PERIOD_END_DATE, NVL(RTL_ORG_CD,'ORG_CD'), NVL(AO_HCHY_CD,'AO_HCHY'),NVL(ACCT_CD,'ACCT_CD'))
1479 IN (
1480 SELECT
1481 MEASURE_SET, SRC_CD, NVL(PROD_KEY,'PROD'), NVL(GEO_KEY,'GEO'),
1482 NVL(CHNL_TYP_CD,'CHNL'), NVL(GEO_SUB_RGN_CD,'SUB'),
1483 NVL(GEO_RGN_CD,'RGN'), NVL(MFG_ITM_HCHY_LVL,'LVL'),
1484 NVL(MFG_ITM_HCHY_CD,'HCHY'), NVL(MKT_AREA_CD,'MKT'),NVL(MKT_AREA_TYP,'MKT_TYP'),
1485 NVL(RTL_ORG_HCHY_LVL,'ORG_LVL'), NVL(RTL_ORG_HCHY_CD,'ORG_HCHY'),
1486 PERIOD_END_DATE, NVL(RTL_ORG_CD,'ORG_CD') ,NVL(AO_HCHY_CD,'AO_HCHY'),NVL(ACCT_CD, 'ACCT_CD')
1487 FROM DDR_I_SYND_CNSMPTN_DATA
1488 GROUP BY
1489 MEASURE_SET, SRC_CD, NVL(PROD_KEY,'PROD'), NVL(GEO_KEY,'GEO'),
1490 NVL(CHNL_TYP_CD,'CHNL'), NVL(GEO_SUB_RGN_CD,'SUB'),
1491 NVL(GEO_RGN_CD,'RGN'), NVL(MFG_ITM_HCHY_LVL,'LVL'),
1492 NVL(MFG_ITM_HCHY_CD,'HCHY'), NVL(MKT_AREA_CD,'MKT'),NVL(MKT_AREA_TYP,'MKT_TYP'),
1493 NVL(RTL_ORG_HCHY_LVL,'ORG_LVL'), NVL(RTL_ORG_HCHY_CD,'ORG_HCHY'),
1497 COMMIT;
1494 PERIOD_END_DATE, NVL(RTL_ORG_CD,'ORG_CD'), NVL(AO_HCHY_CD,'AO_HCHY'),NVL(ACCT_CD,'ACCT_CD')
1495 HAVING COUNT(*) > 1
1496 );
1498
1499
1500 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
1501
1502
1503 DELETE FROM DDR_I_SYND_CNSMPTN_DATA
1504 WHERE
1505 (MEASURE_SET, SRC_CD, NVL(PROD_KEY,'PROD'), NVL(GEO_KEY,'GEO'),
1506 NVL(CHNL_TYP_CD,'CHNL'), NVL(GEO_SUB_RGN_CD,'SUB'),
1507 NVL(GEO_RGN_CD,'RGN'), NVL(MFG_ITM_HCHY_LVL,'LVL'),
1508 NVL(MFG_ITM_HCHY_CD,'HCHY'), NVL(MKT_AREA_CD,'MKT'),NVL(MKT_AREA_TYP,'MKT_TYP'),
1509 NVL(RTL_ORG_HCHY_LVL,'ORG_LVL'), NVL(RTL_ORG_HCHY_CD,'ORG_HCHY'),
1510 PERIOD_END_DATE, NVL(RTL_ORG_CD,'ORG_CD'), NVL(AO_HCHY_CD,'AO_HCHY'),NVL(ACCT_CD,'ACCT_CD'))
1511 IN (
1512 SELECT
1513 MEASURE_SET, SRC_CD, NVL(PROD_KEY,'PROD'), NVL(GEO_KEY,'GEO'),
1514 NVL(CHNL_TYP_CD,'CHNL'), NVL(GEO_SUB_RGN_CD,'SUB'),
1515 NVL(GEO_RGN_CD,'RGN'), NVL(MFG_ITM_HCHY_LVL,'LVL'),
1516 NVL(MFG_ITM_HCHY_CD,'HCHY'), NVL(MKT_AREA_CD,'MKT'),NVL(MKT_AREA_TYP,'MKT_TYP'),
1517 NVL(RTL_ORG_HCHY_LVL,'ORG_LVL'), NVL(RTL_ORG_HCHY_CD,'ORG_HCHY'),
1518 PERIOD_END_DATE, NVL(RTL_ORG_CD,'ORG_CD'), NVL(AO_HCHY_CD,'AO_HCHY'),NVL(ACCT_CD,'ACCT_CD')
1519 FROM DDR_E_SYND_CNSMPTN_DATA
1520 GROUP BY
1521 MEASURE_SET, SRC_CD, NVL(PROD_KEY,'PROD'), NVL(GEO_KEY,'GEO'),
1522 NVL(CHNL_TYP_CD,'CHNL'), NVL(GEO_SUB_RGN_CD,'SUB'),
1523 NVL(GEO_RGN_CD,'RGN'), NVL(MFG_ITM_HCHY_LVL,'LVL'),
1524 NVL(MFG_ITM_HCHY_CD,'HCHY'), NVL(MKT_AREA_CD,'MKT'),NVL(MKT_AREA_TYP,'MKT_TYP'),
1525 NVL(RTL_ORG_HCHY_LVL,'ORG_LVL'), NVL(RTL_ORG_HCHY_CD,'ORG_HCHY'),
1526 PERIOD_END_DATE, NVL(RTL_ORG_CD,'ORG_CD'), NVL(AO_HCHY_CD,'AO_HCHY'),NVL(ACCT_CD,'ACCT_CD')
1527 HAVING COUNT(*) > 1
1528 );
1529 COMMIT;
1530
1531 RETURN('Y');
1532
1533 END SYND_CNSMPTN_DATA_DUPS_FNC;
1534
1535
1536 FUNCTION synd_cnsmptn_data_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
1537 AS
1538
1539 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1540 v_FUNC_MSG VARCHAR2(1) := 'N';
1541
1542 CURSOR c1 IS
1543 SELECT *
1544 FROM ddr_s_synd_cnsmptn_data
1545 WHERE (MEASURE_SET, SRC_CD, CHNL_TYP_CD, GEO_SUB_RGN_ID, GEO_RGN_ID,MFG_ITM_HCHY_LVL,
1546 MFG_ITM_HCHY_ID, MKT_AREA_ID, RTL_ORG_HCHY_LVL, RTL_ORG_HCHY_ID,RTL_ORG_CD, PERIOD_ID,AO_HCHY_CD ,ACCT_CD)
1547 IN (SELECT MEASURE_SET, SRC_CD, CHNL_TYP_CD, GEO_SUB_RGN_ID, GEO_RGN_ID,MFG_ITM_HCHY_LVL,
1548 MFG_ITM_HCHY_ID, MKT_AREA_ID, RTL_ORG_HCHY_LVL, RTL_ORG_HCHY_ID,RTL_ORG_CD, PERIOD_ID, AO_HCHY_CD,ACCT_CD
1549 FROM ddr_s_synd_cnsmptn_data
1550 GROUP BY MEASURE_SET, SRC_CD, CHNL_TYP_CD, GEO_SUB_RGN_ID, GEO_RGN_ID,MFG_ITM_HCHY_LVL,
1551 MFG_ITM_HCHY_ID, MKT_AREA_ID, RTL_ORG_HCHY_LVL, RTL_ORG_HCHY_ID,RTL_ORG_CD, PERIOD_ID, AO_HCHY_CD,ACCT_CD
1552 HAVING COUNT(*) > 1)
1553 FOR UPDATE;
1554 BEGIN
1555
1556 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1557
1558 FOR rec IN c1 LOOP --{
1559 INSERT INTO ddr_e_synd_cnsmptn_data
1560 (REC_ID, LOAD_ID, ERR_REASON, SRC_CD,
1561 RTL_ORG_HCHY_ID, RTL_ORG_HCHY_LVL, MFG_ITM_HCHY_ID,
1562 MFG_ITM_HCHY_LVL, MKT_AREA_ID, GEO_RGN_ID,
1563 GEO_SUB_RGN_ID, CHNL_TYP_CD, PERIOD_ID, TIME_HCHY_LVL,
1564 SRC_SYS_IDNT, SRC_SYS_DT, SRC_IDNT_FLAG, ACTION_FLAG, MEASURE_SET,
1565 MEASURE1, MEASURE2, MEASURE3, MEASURE4, MEASURE5,
1566 MEASURE6, MEASURE7, MEASURE8, MEASURE9, MEASURE10,
1567 MEASURE11, MEASURE12, MEASURE13, MEASURE14, MEASURE15,
1568 MEASURE16, MEASURE17, MEASURE18, MEASURE19, MEASURE20,
1569 MEASURE21, MEASURE22, MEASURE23, MEASURE24, MEASURE25,
1570 MEASURE26, MEASURE27, MEASURE28, MEASURE29, MEASURE30,
1571 MEASURE31, MEASURE32, MEASURE33, MEASURE34, MEASURE35,
1572 MEASURE36, MEASURE37, MEASURE38, MEASURE39, MEASURE40,
1573 MEASURE41, MEASURE42, MEASURE43, MEASURE44, MEASURE45,
1574 MEASURE46, MEASURE47, MEASURE48, MEASURE49, MEASURE50,
1575 MEASURE51, MEASURE52, MEASURE53, MEASURE54, MEASURE55,
1576 MEASURE56, MEASURE57, MEASURE58, MEASURE59, MEASURE60,
1577 MEASURE61, MEASURE62, MEASURE63, MEASURE64, MEASURE65,
1578 MEASURE66, MEASURE67, MEASURE68, MEASURE69, MEASURE70,
1579 MEASURE71, MEASURE72, MEASURE73, MEASURE74, MEASURE75,
1580 MEASURE76, MEASURE77, MEASURE78, MEASURE79, MEASURE80,
1581 MEASURE81, MEASURE82, MEASURE83, MEASURE84, MEASURE85,
1582 MEASURE86, MEASURE87, MEASURE88, MEASURE89, MEASURE90,
1583 MEASURE91, MEASURE92, MEASURE93, MEASURE94, MEASURE95,
1584 MEASURE96, MEASURE97, MEASURE98, MEASURE99, MEASURE100,
1585 RTL_ORG_CD,AO_HCHY_CD,ACCT_CD)
1586 VALUES (
1587 rec.REC_ID, v_load_id, 'Duplicate record - Staging', rec.SRC_CD,
1588 rec.RTL_ORG_HCHY_ID, rec.RTL_ORG_HCHY_LVL, rec.MFG_ITM_HCHY_ID,
1589 rec.MFG_ITM_HCHY_LVL, rec.MKT_AREA_ID, rec.GEO_RGN_ID,
1590 rec.GEO_SUB_RGN_ID, rec.CHNL_TYP_CD, rec.PERIOD_ID, rec.TIME_HCHY_LVL,
1591 rec.SRC_SYS_IDNT, rec.SRC_SYS_DT, 'S', 'N', rec.MEASURE_SET,
1592 rec.MEASURE1, rec.MEASURE2, rec.MEASURE3, rec.MEASURE4, rec.MEASURE5,
1593 rec.MEASURE6, rec.MEASURE7, rec.MEASURE8, rec.MEASURE9, rec.MEASURE10,
1594 rec.MEASURE11, rec.MEASURE12, rec.MEASURE13, rec.MEASURE14, rec.MEASURE15,
1595 rec.MEASURE16, rec.MEASURE17, rec.MEASURE18, rec.MEASURE19, rec.MEASURE20,
1596 rec.MEASURE21, rec.MEASURE22, rec.MEASURE23, rec.MEASURE24, rec.MEASURE25,
1597 rec.MEASURE26, rec.MEASURE27, rec.MEASURE28, rec.MEASURE29, rec.MEASURE30,
1598 rec.MEASURE31, rec.MEASURE32, rec.MEASURE33, rec.MEASURE34, rec.MEASURE35,
1599 rec.MEASURE36, rec.MEASURE37, rec.MEASURE38, rec.MEASURE39, rec.MEASURE40,
1600 rec.MEASURE41, rec.MEASURE42, rec.MEASURE43, rec.MEASURE44, rec.MEASURE45,
1601 rec.MEASURE46, rec.MEASURE47, rec.MEASURE48, rec.MEASURE49, rec.MEASURE50,
1602 rec.MEASURE51, rec.MEASURE52, rec.MEASURE53, rec.MEASURE54, rec.MEASURE55,
1606 rec.MEASURE71, rec.MEASURE72, rec.MEASURE73, rec.MEASURE74, rec.MEASURE75,
1603 rec.MEASURE56, rec.MEASURE57, rec.MEASURE58, rec.MEASURE59, rec.MEASURE60,
1604 rec.MEASURE61, rec.MEASURE62, rec.MEASURE63, rec.MEASURE64, rec.MEASURE65,
1605 rec.MEASURE66, rec.MEASURE67, rec.MEASURE68, rec.MEASURE69, rec.MEASURE70,
1607 rec.MEASURE76, rec.MEASURE77, rec.MEASURE78, rec.MEASURE79, rec.MEASURE80,
1608 rec.MEASURE81, rec.MEASURE82, rec.MEASURE83, rec.MEASURE84, rec.MEASURE85,
1609 rec.MEASURE86, rec.MEASURE87, rec.MEASURE88, rec.MEASURE89, rec.MEASURE90,
1610 rec.MEASURE91, rec.MEASURE92, rec.MEASURE93, rec.MEASURE94, rec.MEASURE95,
1611 rec.MEASURE96, rec.MEASURE97, rec.MEASURE98, rec.MEASURE99, rec.MEASURE100,
1612 rec.RTL_ORG_CD,rec.AO_HCHY_CD,rec.ACCT_CD
1613 );
1614
1615 -- delete duplicate records from staging table --
1616 DELETE ddr_s_synd_cnsmptn_data
1617 WHERE CURRENT OF c1;
1618
1619 END LOOP; --}
1620
1621 COMMIT;
1622
1623 RETURN('Y');
1624
1625 END synd_cnsmptn_data_dups_s_fnc;
1626
1627
1628 FUNCTION MFG_SHIP_ITEM_DUPS_FNC(p_load_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
1629 AS
1630 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1631 BEGIN
1632 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1633
1634
1635 INSERT INTO DDR_E_MFG_SHIP_ITEM
1636 (REC_ID, LOAD_ID, ERR_REASON,
1637 BSNS_UNIT_CD, MFG_SKU_ITEM_NBR,
1638 UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
1639 SRC_SYS_DT, SRC_IDNT_FLAG, ACTION_FLAG, TRANS_DT,
1640 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD, SHIP_COST
1641 )
1642 SELECT REC_ID, v_load_id,'Duplicate Record',
1643 BSNS_UNIT_CD, MFG_SKU_ITEM_NBR,
1644 UOM,SHIP_QTY, SHIP_AMT, SRC_SYS_IDNT,
1645 SRC_SYS_DT,'I','N', TRANS_DT,
1646 SHIP_TO_ORG_CD, SHIP_TO_BSNS_UNIT_CD, SHIP_COST
1647 FROM DDR_I_MFG_SHIP_ITEM
1648 WHERE
1649 (BSNS_UNIT_CD,
1650 MFG_SKU_ITEM_NBR,
1651 TRANS_DT, SHIP_TO_ORG_CD,
1652 SHIP_TO_BSNS_UNIT_CD )
1653 IN (
1654 SELECT
1655 BSNS_UNIT_CD,
1656 MFG_SKU_ITEM_NBR,
1657 TRANS_DT, SHIP_TO_ORG_CD,
1658 SHIP_TO_BSNS_UNIT_CD
1659 FROM DDR_I_MFG_SHIP_ITEM
1660 GROUP BY
1661 BSNS_UNIT_CD,
1662 MFG_SKU_ITEM_NBR,
1663 TRANS_DT, SHIP_TO_ORG_CD,
1664 SHIP_TO_BSNS_UNIT_CD
1665 HAVING COUNT(*) > 1);
1666 COMMIT;
1667
1668 -- DELETE DUPLICATE RECORDS FROM INTERFACE TABLE --
1669
1670 DELETE FROM DDR_I_MFG_SHIP_ITEM
1671 WHERE
1672 (BSNS_UNIT_CD,
1673 MFG_SKU_ITEM_NBR,
1674 TRANS_DT, SHIP_TO_ORG_CD,
1675 SHIP_TO_BSNS_UNIT_CD)
1676 IN (
1677 SELECT
1678 BSNS_UNIT_CD,
1679 MFG_SKU_ITEM_NBR,
1680 TRANS_DT, SHIP_TO_ORG_CD,
1681 SHIP_TO_BSNS_UNIT_CD
1682 FROM DDR_E_MFG_SHIP_ITEM
1683 GROUP BY
1684 BSNS_UNIT_CD,
1685 MFG_SKU_ITEM_NBR,
1686 TRANS_DT, SHIP_TO_ORG_CD,
1687 SHIP_TO_BSNS_UNIT_CD
1688 HAVING COUNT(*) > 1
1689 );
1690 COMMIT;
1691
1692 RETURN('Y');
1693
1694 END MFG_SHIP_ITEM_DUPS_FNC;
1695
1696
1697 FUNCTION mfg_ship_item_dups_s_fnc(p_load_id IN NUMBER DEFAULT NULL) RETURN
1698 VARCHAR2
1699 AS
1700 v_load_id NUMBER := NVL(p_load_id, set_load_id);
1701 v_FUNC_MSG VARCHAR2(1) := 'N';
1702
1703 CURSOR c1 IS
1704 SELECT *
1705 FROM ddr_s_mfg_ship_item
1706 WHERE
1707 (day_cd,bsns_unit_id,mfg_sku_item_id,ship_to_bsns_unit_id)
1708 IN (SELECT
1709 day_cd,bsns_unit_id,mfg_sku_item_id,ship_to_bsns_unit_id
1710 FROM ddr_s_mfg_ship_item
1711 GROUP BY
1712 day_cd,bsns_unit_id,mfg_sku_item_id,ship_to_bsns_unit_id
1713 HAVING count(*) > 1)
1714 FOR UPDATE;
1715 BEGIN
1716
1717 -- INSERT DUPLICATE RECORDS IN THE ERROR TABLE --
1718
1719 FOR rec IN c1 LOOP --{
1720 INSERT INTO ddr_e_mfg_ship_item
1721 (crncy_cd
1722 ,day_cd
1723 ,load_id
1724 ,mfg_sku_item_id
1725 ,bsns_unit_id
1726 ,rec_id
1727 ,bsns_unit_cd
1728 ,ship_amt
1729 ,ship_amt_lcl
1730 ,ship_qty
1731 ,ship_qty_alt
1732 ,ship_qty_prmry
1733 ,src_sys_dt
1734 ,src_sys_idnt
1735 ,trans_dt
1736 ,uom_cd
1737 ,uom_cd_alt
1738 ,uom_cd_prmry
1739 ,src_idnt_flag
1740 ,action_flag
1741 ,err_reason
1742 ,ship_to_org_cd
1743 ,ship_to_bsns_unit_id
1744 ,ship_to_bsns_unit_cd
1745 ,ship_cost
1746 ,ship_cost_lcl
1747 ) VALUES
1748 (rec.crncy_cd
1749 ,rec.day_cd
1750 ,v_load_id
1751 ,rec.mfg_sku_item_id
1752 ,rec.bsns_unit_id
1753 ,rec.rec_id
1754 ,rec.bsns_unit_cd
1755 ,rec.ship_amt
1756 ,rec.ship_amt_lcl
1757 ,rec.ship_qty
1758 ,rec.ship_qty_alt
1759 ,rec.ship_qty_prmry
1760 ,rec.src_sys_dt
1761 ,rec.src_sys_idnt
1762 ,rec.trans_dt
1763 ,rec.uom_cd
1764 ,rec.uom_cd_alt
1765 ,rec.uom_cd_prmry
1766 ,'S'
1767 ,'N'
1768 ,'Duplicate record - Staging'
1769 ,rec.ship_to_org_cd
1770 ,rec.ship_to_bsns_unit_id
1771 ,rec.ship_to_bsns_unit_cd
1772 ,rec.ship_cost
1773 ,rec.ship_cost_lcl
1774 );
1775
1776 -- delete duplicate records from staging table --
1777 DELETE ddr_s_mfg_ship_item
1778 WHERE CURRENT OF c1;
1779
1780 END LOOP; --}
1781
1782 COMMIT;
1783
1787
1784 RETURN('Y');
1785
1786 END mfg_ship_item_dups_s_fnc;
1788
1789 FUNCTION get_load_id(p_run_id IN NUMBER) RETURN NUMBER
1790 AS
1791 v_run_id NUMBER;
1792 BEGIN
1793 v_run_id := NVL(p_run_id, set_load_id);
1794 RETURN v_run_id;
1795 END get_load_id;
1796
1797
1798 END ddr_base_util_pkg;