[Home] [Help]
PACKAGE BODY: APPS.EDW_ITEMS_M_C
Source
1 PACKAGE BODY EDW_ITEMS_M_C AS
2 /* $Header: ENICITMB.pls 120.3 2006/04/03 06:44:02 lparihar noship $ */
3
4 l_collect_onetime VARCHAR2(10) := NULL;
5 l_push_date_range1 DATE := NULL;
6 l_push_date_range2 DATE := NULL;
7 l_item_catset1_name VARCHAR2(40) := NULL;
8 l_item_func_area1_id NUMBER := 2;
9 l_item_catset2_name VARCHAR2(40) := NULL;
10 l_item_func_area2_id NUMBER := NULL;
11 l_item_catset3_name VARCHAR2(40) := NULL;
12 l_item_func_area3_id NUMBER := NULL;
13 l_itemorg_catset1_name VARCHAR2(40) := NULL;
14 l_itemorg_func_area1_id NUMBER := NULL;
15 l_itm_hrchy3_coll_type VARCHAR2(30) := NULL;
16 l_itm_hrchy3_vbh_top_node VARCHAR2(30) := NULL;
17 l_instance VARCHAR2(240) := NULL;
18 g_row_count NUMBER := 0;
19 g_EXCEPTION_message VARCHAR2(10000) := NULL;
20 g_error_message VARCHAR2(2000) := NULL;
21 l_column_exists NUMBER;
22
23 PROCEDURE Set_Category_Sets IS
24 BEGIN
25 SELECT DECODE(ITEM_CATEGORY_SET1, NULL, 'NA_EDW', ITEM_CATEGORY_SET1),
26 DECODE(ITEM_CATEGORY_SET2, NULL, 'NA_EDW', ITEM_CATEGORY_SET2),
27 DECODE(ITEM_CATEGORY_SET3, NULL, 'NA_EDW', ITEM_CATEGORY_SET3),
28 DECODE(ITEM_ORG_CATEGORY_SET1, NULL, 'NA_EDW', ITEM_ORG_CATEGORY_SET1),
29 DECODE(ITM_HRCHY3_COLL_TYPE, NULL, 'NA_EDW', ITM_HRCHY3_COLL_TYPE),
30 DECODE(ITM_HRCHY3_VBH_TOP_NODE, NULL, 'NA_EDW', ITM_HRCHY3_VBH_TOP_NODE)
31 INTO l_item_catset1_name,
32 l_item_catset2_name,
33 l_item_catset3_name,
34 l_itemorg_catset1_name,
35 l_itm_hrchy3_coll_type,
36 l_itm_hrchy3_vbh_top_node
37 FROM EDW_LOCAL_SYSTEM_PARAMETERS;
38
39 -- If collection is using VBH then use the VBH Category Set Name
40 -- for Category Set3
41 IF l_itm_hrchy3_coll_type = 'V' THEN
42 SELECT CATEGORY_SET_NAME
43 INTO l_item_catset3_name
44 FROM MTL_CATEGORY_SETS_VL
45 WHERE CATEGORY_SET_ID = g_vbh_catset_id;
46 END IF;
47
48 EXCEPTION WHEN OTHERS THEN
49 RAISE;
50 COMMIT;
51 END;
52
53 FUNCTION Get_Functional_Area (p_category_set_name VARCHAR2)
54 RETURN NUMBER IS
55 l_functional_area_id NUMBER := NULL;
56 BEGIN
57 edw_log.put_line('Getting functional area for ' || p_category_set_name);
58 IF p_category_set_name <> 'NA_EDW' THEN
59 SELECT mtd.functional_area_id
60 INTO l_functional_area_id
61 FROM mtl_category_sets mcs,
62 mtl_default_category_sets mtd
63 WHERE mcs.category_set_name = p_category_set_name
64 AND mcs.category_set_id = mtd.category_set_id;
65 END IF;
66 RETURN l_functional_area_id;
67
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 RETURN 0;
71 WHEN OTHERS THEN
72 RAISE;
73 COMMIT;
74 END;
75
76 PROCEDURE Push(Errbuf out NOCOPY VARCHAR2,
77 Retcode out NOCOPY VARCHAR2,
78 p_from_date IN VARCHAR2,
79 p_to_date IN VARCHAR2) IS
80
81 l_FROM_date DATE;
82 l_to_date DATE;
83 l_onetime_profile_option VARCHAR2(100);
84 l_temp VARCHAR2(1);
85
86 CURSOR l_instance_csr IS
87 SELECT instance_code
88 FROM EDW_LOCAL_INSTANCE;
89
90 -- Cursor to figure out the items having same name with diff. ids
91 CURSOR c_mult_item IS
92 SELECT
93 mti.concatenated_segments,
94 mti.organization_id,
95 COUNT(mti.inventory_item_id)
96 FROM
97 mtl_system_items_kfv mti
98 GROUP BY
99 mti.concatenated_segments,
100 mti.organization_id
101 HAVING COUNT(inventory_item_id) > 1;
102
103 -- This cursor is dependent on cursor c_mult_item. This
104 -- will only print out the item ids that have the same name
105 CURSOR c_item_id(l_name varchar2, l_org_id number) IS
106 SELECT
107 inventory_item_id,
108 organization_id
109 FROM
110 mtl_system_items_kfv
111 WHERE concatenated_segments = l_name
112 AND organization_id = l_org_id;
113
114 BEGIN
115 Errbuf :=NULL;
116 Retcode:=NULL;
117 IF (Not EDW_COLLECTION_UTIL.setup('EDW_ITEMS_M')) THEN
118 errbuf := fnd_message.get;
119 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
120 END IF;
121
122 -- Date processing
123
124 SELECT TO_DATE(p_from_date, 'YYYY/MM/DD HH24:MI:SS'),
125 TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS')
126 INTO l_FROM_date, l_to_date FROM DUAL;
127 --l_FROM_date :=
128
129 --l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
130
131 -- Should onetime items be collected?
132 IF fnd_profile.defined('ENI:COLLECT_ONETIME')
133 THEN
134 l_collect_onetime := fnd_profile.value('ENI:COLLECT_ONETIME');
135 ELSE
136 l_collect_onetime := 'Y';
137 END IF;
138
139 l_push_date_range1:= NVL(l_FROM_date,EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
140 l_push_date_range2:= NVL(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
141 edw_log.put_line( 'The collection range is FROM '||
142 TO_CHAR(l_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
143 TO_CHAR(l_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
144 edw_log.put_line(' ');
145
146 Set_Category_Sets;
147 edw_log.put_line( 'The category sets are ' ||
148 l_item_catset1_name || ', ' ||
149 l_item_catset2_name || ', ' ||
150 l_item_catset3_name || ', ' ||
151 l_itm_hrchy3_coll_type || ',' ||
152 l_itm_hrchy3_vbh_top_node || ',' ||
153 l_itemorg_catset1_name);
154 edw_log.put_line(' ');
155
156
157 -- Validation checks
158
159 l_temp := 'N';
160 edw_log.put_line('');
161 edw_log.put_line('----------------------------------------------');
162 edw_log.put_line('Checking for multiple items with the same name');
163 edw_log.put_line('----------------------------------------------');
164
165 FOR c4 in c_mult_item LOOP
166 l_temp := 'Y';
167 edw_log.put_line(c4.concatenated_segments);
168 retcode := 1;
169 errbuf := 'Items names need to be unique. Ensure that item names that failed the test are unique in the system';
170 RAISE_APPLICATION_ERROR(-20000,'Error in VALIDATION: ' || errbuf);
171 END LOOP;
172
173 IF l_temp = 'N' then
174 edw_log.put_line('----- None -----');
175 END IF;
176
177 -- fetching instance code into local variable
178
179 FOR l_instance_rec IN l_instance_csr LOOP
180 l_instance := l_instance_rec.instance_code;
181 END LOOP;
182
183 /* -- May be supported in future release
184 l_item_func_area1_id := Get_Functional_Area(l_item_catset1_name);
185 l_item_func_area2_id := Get_Functional_Area(l_item_catset2_name);
186 l_item_func_area3_id := Get_Functional_Area(l_item_catset3_name);
187 l_itemorg_func_area1_id := Get_Functional_Area(l_itemorg_catset1_name);
188 */
189 edw_log.put_line('Pushing Data');
190 /*
191 Push_EDW_ITEM_ITEMREV(l_push_date_range1, l_push_date_range2);
192 */
193 Push_EDW_ITEM_PRDFAM(l_push_date_range1, l_push_date_range2);
194 Push_EDW_ITEM_ITEMORG(l_push_date_range1, l_push_date_range2);
195 Push_EDW_ITEM_ITEM(l_push_date_range1, l_push_date_range2);
196 Push_EDW_ITEM_ITEMORG_CAT(l_push_date_range1, l_push_date_range2);
197 Push_EDW_ITEM_ITEM_CAT(l_push_date_range1, l_push_date_range2);
198 Push_EDW_ITEM_PROD_LINE(l_push_date_range1, l_push_date_range2);
199 Push_EDW_ITEM_PROD_CATG(l_push_date_range1, l_push_date_range2);
200 Push_EDW_ITEM_PROD_GRP(l_push_date_range1, l_push_date_range2);
201
202 EDW_COLLECTION_UTIL.wrapup(TRUE, EDW_ITEMS_M_C.g_row_count,NULL, l_push_date_range1,l_push_date_range2);
203 COMMIT;
204
205 EXCEPTION WHEN OTHERS THEN
206 IF g_error_message IS NULL THEN
207 Errbuf := sqlerrm;
208 Retcode := sqlcode;
209 EDW_ITEMS_M_C.g_EXCEPTION_message := EDW_ITEMS_M_C.g_EXCEPTION_message||' <> '||Retcode||' : '||Errbuf;
210 ELSE
211 Retcode := 2;
212 g_error_message := 'ERROR: ' || g_error_message;
213 EDW_ITEMS_M_C.g_EXCEPTION_message := EDW_ITEMS_M_C.g_EXCEPTION_message||
214 ' <> 2 : '|| g_error_message;
215 Errbuf := g_error_message;
216 END IF;
217 ROLLBACK;
218 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_ITEMS_M_C.g_EXCEPTION_message,l_push_date_range1, l_push_date_range2);
219 COMMIT;
220
221 END Push;
222
223 PROCEDURE Push_EDW_ITEM_ITEMREV(
224 p_from_date IN DATE,
225 p_to_date IN DATE) IS
226 l_staging_table_name VARCHAR2(30) :='EDW_ITEM_ITEMREV_LSTG' ;
227 L_PUSH_DATE_RANGE1 DATE:=NULL;
228 L_PUSH_DATE_RANGE2 DATE:=NULL;
229 l_rows_inserted NUMBER:=0;
230
231 BEGIN
232
233 l_push_date_range1:=p_from_date;
234 l_push_date_range2:=p_to_date;
235
236 edw_log.put_line(' ');
237 edw_log.put_line('Pushing EDW_ITEM_ITEMREV');
238
239 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
240 CREATION_DATE,
241 EFFECTIVE_DATE,
242 ERROR_CODE,
243 INSTANCE,
244 ITEM_ORG_FK,
245 ITEM_ORG_FK_KEY,
246 ITEM_REVISION,
247 ITEM_REVISION_DP,
248 ITEM_REVISION_PK,
249 LAST_UPDATE_DATE,
250 LEVEL_NAME,
251 NAME,
252 REQUEST_ID,
253 ROW_ID,
254 USER_ATTRIBUTE1,
255 USER_ATTRIBUTE2,
256 USER_ATTRIBUTE3,
257 USER_ATTRIBUTE4,
258 USER_ATTRIBUTE5,
259 OPERATION_CODE,
260 COLLECTION_STATUS)
261 SELECT
262 CREATION_DATE,
263 EFFECTIVE_DATE,
264 NULL, --ERROR_CODE,
265 INSTANCE,
266 ITEM_ORG_FK,
267 NULL, --ITEM_ORG_FK_KEY,
268 SUBSTRB(ITEM_REVISION, 1, 240),
269 SUBSTRB(ITEM_REVISION_DP, 1, 240),
270 ITEM_REVISION_PK,
271 LAST_UPDATE_DATE,
272 NULL, --LEVEL_NAME,
273 SUBSTRB(NAME, 1, 320),
274 NULL, --REQUEST_ID,
275 NULL, --ROW_ID,
276 USER_ATTRIBUTE1,
277 USER_ATTRIBUTE2,
278 USER_ATTRIBUTE3,
279 USER_ATTRIBUTE4,
280 USER_ATTRIBUTE5,
281 NULL, -- OPERATION_CODE
282 'READY'
283 FROM EDW_ITEM_ITEMREV_LCV
284 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
285
286 l_rows_inserted := SQL%ROWCOUNT;
287
288 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
289 ' rows into the staging table');
290 edw_log.put_line(' ');
291
292 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
293 COMMIT;
294
295 EXCEPTION WHEN OTHERS THEN
296 RAISE;
297 COMMIT;
298 END Push_EDW_ITEM_ITEMREV;
299
300 PROCEDURE Push_EDW_ITEM_PRDFAM(
301 p_from_date IN DATE,
302 p_to_date IN DATE) IS
303 l_staging_table_name VARCHAR2(30) :='EDW_ITEM_PRDFAM_LSTG' ;
304 L_PUSH_DATE_RANGE1 DATE := NULL;
305 L_PUSH_DATE_RANGE2 DATE := NULL;
306 l_rows_inserted NUMBER := 0;
307
308 BEGIN
309
310 l_push_date_range1 := p_from_date;
311 l_push_date_range2 := p_to_date;
312
313 edw_log.put_line(' ');
314 edw_log.put_line('Pushing EDW_ITEM_PRDFAM');
315
316 INSERT INTO EDW_ITEM_PRDFAM_LSTG(
317 ALL_FK,
318 ALL_FK_KEY,
319 DESCRIPTION,
320 ERROR_CODE,
321 INSTANCE,
322 NAME,
323 PRODUCT_FAMILY,
324 PROD_FAMILY_DP,
325 PROD_FAMILY_PK,
326 REQUEST_ID,
327 ROW_ID,
328 USER_ATTRIBUTE1,
329 USER_ATTRIBUTE2,
330 USER_ATTRIBUTE3,
331 USER_ATTRIBUTE4,
332 USER_ATTRIBUTE5,
333 OPERATION_CODE,
334 COLLECTION_STATUS,
335 CREATION_DATE,
336 LAST_UPDATE_DATE)
337 SELECT
338 ALL_FK,
339 NULL, --ALL_FK_KEY,
340 DESCRIPTION,
341 NULL, --ERROR_CODE,
342 l_instance, --INSTANCE, /* Bug# 2558245 */
343 SUBSTRB(NAME, 1, 320),
344 PRODUCT_FAMILY,
345 PROD_FAMILY_DP,
346 PROD_FAMILY_PK || '-' || l_instance, -- PROD_FAMILY_PK, /* Bug# 2558245 */
347 NULL, --REQUEST_ID,
348 NULL, --ROW_ID,
349 USER_ATTRIBUTE1,
350 USER_ATTRIBUTE2,
351 USER_ATTRIBUTE3,
352 USER_ATTRIBUTE4,
353 USER_ATTRIBUTE5,
354 NULL, -- OPERATION_CODE
355 'READY',
356 CREATION_DATE,
357 LAST_UPDATE_DATE
358 FROM EDW_ITEM_PRDFAM_LCV
359 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
360 l_rows_inserted := SQL%ROWCOUNT;
361
362 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
363 ' rows into the staging table');
364 edw_log.put_line(' ');
365
366 COMMIT;
367
368 EXCEPTION WHEN OTHERS THEN
369 RAISE;
370 COMMIT;
371 END Push_EDW_ITEM_PRDFAM;
372
373 PROCEDURE Push_EDW_ITEM_ITEMORG(
374 p_from_date IN DATE,
375 p_to_date IN DATE) IS
376
377 l_staging_table_name VARCHAR2(30) :='EDW_ITEM_ITEMORG_LSTG' ;
378 L_PUSH_DATE_RANGE1 DATE := NULL;
379 L_PUSH_DATE_RANGE2 DATE := NULL;
380 l_rows_inserted NUMBER := 0;
381 l_commit_count NUMBER := 0;
382 l_item_revision_pk VARCHAR2(320) :='NA_EDW';
383 l_all_revisions VARCHAR2(320) := NULL;
384 number_of_records NUMBER := 0;
385 -- l_instance VARCHAR2(240) := NULL; /* Bug# 2558245 */
386 l_all_item_revs VARCHAR2(100);
387
388 /* Bug# 2558245
389 CURSOR l_instance_csr is
390 SELECT instance_code
391 FROM edw_local_instance;
392 */
393
394 CURSOR category_assignments_cursor IS /* Bug# 2197243 */
395 SELECT
396 MIC.INVENTORY_ITEM_ID,
397 MIC.ORGANIZATION_ID
398 FROM
399 MTL_CATEGORIES CAT,
400 MTL_ITEM_CATEGORIES MIC,
401 MTL_CATEGORY_SETS SETS
402 WHERE CAT.CATEGORY_ID = MIC.CATEGORY_ID
403 AND MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
404 AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
405 AND SETS.CATEGORY_SET_NAME = l_itemorg_catset1_name;
406
407 category_assignments_rec category_assignments_cursor%ROWTYPE;
408
409 BEGIN
410
411 l_all_item_revs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IREV');
412
413 l_push_date_range1 := p_from_date;
414 l_push_date_range2 := p_to_date;
415
416 -- Added function call to -lookup- the ALL level based on
417 -- EDW requirements. By AS on 05/22/00
418
419 /* Bug# 2558245
420 for l_instance_rec in l_instance_csr loop
421 l_instance := l_instance_rec.instance_code;
422 end loop;
423 */
424
425 edw_log.put_line(' ');
426 edw_log.put_line('Pushing EDW_ITEM_ITEMORG records in TEMP table');
427
428 l_rows_inserted := 0;
429 l_commit_count := 0;
430
431 INSERT INTO EDW_ITEM_ITEMORG_TEMP(
432 APPROVED_SUPPLIER,
433 BUYER_FK,
434 CREATION_DATE,
435 CATSET_CATEGORY_FK,
436 CATSET_CATEGORY_FK_KEY,
437 DESCRIPTION,
438 ERROR_CODE,
439 EXPRS_DELIVERY,
440 HAZARD_CLASS_ID,
441 INSP_REQUIRED,
442 INSTANCE,
443 INTERNAL_ORD_FLAG,
444 INV_PLANNING_CODE,
445 ITEM_NUMBER,
446 ITEM_NUMBER_FK,
447 ITEM_NUMBER_FK_KEY,
448 ITEM_ORG_DP,
449 ITEM_ORG_PK,
450 INVENTORY_ITEM_ID,
451 ORGANIZATION_ID,
452 LAST_UPDATE_DATE,
453 LOCATOR_CONTROL,
454 EFFECTIVITY_CONTROL,
458 MRP_PLN_METHOD,
455 LOT_CONTROL,
456 MAKE_OR_BUY_FLAG,
457 MARKET_PRICE,
459 NAME,
460 ONE_TIME_FLAG,
461 OUTSIDE_OP_FLAG,
462 PLANNER_FK,
463 PRICE_TOL_PERCENT,
464 PROD_FAMILY_FK,
465 PROD_FAMILY_FK_KEY,
466 PURCHASABLE_FLAG,
467 RECEIPT_REQUIRED,
468 REQUEST_ID,
469 REVISION_CONTROL,
470 RFQ_REQUIRED_FLAG,
471 ROW_ID,
472 SERIAL_CONTROL,
473 SHELF_LIFE_CODE,
474 SHELF_LIFE_DAYS,
475 STOCKABLE_FLAG,
476 SUBSTITUTE_RCPT,
477 TAXABLE_FLAG,
478 TAX_CODE,
479 UNIT_LIST_PRICE,
480 UNORDERED_RCPT,
481 UN_NUMBER_ID,
482 SEGMENT1,
483 USER_ATTRIBUTE1,
484 USER_ATTRIBUTE2,
485 USER_ATTRIBUTE3,
486 USER_ATTRIBUTE4,
487 USER_ATTRIBUTE5,
488 OPERATION_CODE,
489 COLLECTION_STATUS,
490 ITEM_TYPE) /* Enh# 2544906 */
491 SELECT
492 APPROVED_SUPPLIER,
493 BUYER_FK,
494 CREATION_DATE,
495 CATSET_CATEGORY_FK,
496 INVENTORY_ITEM_ID, --CATSET_CATEGORY_FK_KEY,
497 DESCRIPTION,
498 NULL, --ERROR_CODE,
499 EXPRS_DELIVERY,
500 HAZARD_CLASS_ID,
501 INSP_REQUIRED,
502 l_instance,
503 INTERNAL_ORD_FLAG,
504 SUBSTRB(INV_PLANNING_CODE, 1, 40),
505 SUBSTRB(ITEM_NUMBER, 1, 240),
506 ITEM_NUMBER_FK || '-' || l_instance, -- ITEM_NUMBER_FK, /* Bug# 2558245 */
507 NULL, --ITEM_NUMBER_FK_KEY,
508 SUBSTRB(ITEM_ORG_DP, 1, 240),
509 ITEM_ORG_PK || '-' || l_instance, -- ITEM_ORG_PK, /* Bug# 2558245 */
510 INVENTORY_ITEM_ID,
511 ORGANIZATION_ID,
512 LAST_UPDATE_DATE,
513 LOCATOR_CONTROL,
514 EFFECTIVITY_CONTROL,
515 LOT_CONTROL,
516 SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
517 MARKET_PRICE,
518 MRP_PLN_METHOD,
519 SUBSTRB(NAME, 1, 320),
520 NULL, --ONE_TIME_FLAG,
521 OUTSIDE_OP_FLAG,
522 PLANNER_FK,
523 PRICE_TOL_PERCENT,
524 PROD_FAMILY_FK,
525 ORGANIZATION_ID, --PROD_FAMILY_FK_KEY,
526 PURCHASABLE_FLAG,
527 RECEIPT_REQUIRED,
528 NULL, --REQUEST_ID,
529 REVISION_CONTROL,
530 RFQ_REQUIRED_FLAG,
531 NULL, --ROW_ID,
532 SERIAL_CONTROL,
533 SHELF_LIFE_CODE,
534 SHELF_LIFE_DAYS,
535 STOCKABLE_FLAG,
536 SUBSTITUTE_RCPT,
537 TAXABLE_FLAG,
538 TAX_CODE,
539 UNIT_LIST_PRICE,
540 UNORDERED_RCPT,
541 UN_NUMBER_ID,
542 SEGMENT1,
543 USER_ATTRIBUTE1,
544 USER_ATTRIBUTE2,
545 USER_ATTRIBUTE3,
546 USER_ATTRIBUTE4,
547 USER_ATTRIBUTE5,
548 NULL, -- OPERATION_CODE
549 'READY',
550 ITEM_TYPE /* Enh# 2544906 */
551 FROM EDW_ITEM_ITEMORG_LCV
552 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
553
554 l_rows_inserted := SQL%ROWCOUNT;
555 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
556 ' rows into the staging table');
557
558 /* Bug 2197243 */
559 /** Need to INSERT additional items for whom the category assignments
560 ** have changed.
561 */
562
563 edw_log.put_line('Inserting additional items due to category changes ');
564
565 OPEN category_assignments_cursor;
566 LOOP
567 FETCH category_assignments_cursor INTO category_assignments_rec;
568 EXIT WHEN category_assignments_cursor%NOTFOUND;
569
570 INSERT INTO EDW_ITEM_ITEMORG_TEMP(
571 APPROVED_SUPPLIER,
572 BUYER_FK,
573 CREATION_DATE,
574 CATSET_CATEGORY_FK,
575 CATSET_CATEGORY_FK_KEY,
576 DESCRIPTION,
577 ERROR_CODE,
578 EXPRS_DELIVERY,
579 HAZARD_CLASS_ID,
580 INSP_REQUIRED,
581 INSTANCE,
582 INTERNAL_ORD_FLAG,
583 INV_PLANNING_CODE,
584 ITEM_NUMBER,
585 ITEM_NUMBER_FK,
586 ITEM_NUMBER_FK_KEY,
587 ITEM_ORG_DP,
588 ITEM_ORG_PK,
589 INVENTORY_ITEM_ID,
590 ORGANIZATION_ID,
591 LAST_UPDATE_DATE,
592 LOCATOR_CONTROL,
593 EFFECTIVITY_CONTROL,
594 LOT_CONTROL,
595 MAKE_OR_BUY_FLAG,
596 MARKET_PRICE,
597 MRP_PLN_METHOD,
598 NAME,
599 ONE_TIME_FLAG,
600 OUTSIDE_OP_FLAG,
601 PLANNER_FK,
602 PRICE_TOL_PERCENT,
603 PROD_FAMILY_FK,
604 PROD_FAMILY_FK_KEY,
605 PURCHASABLE_FLAG,
606 RECEIPT_REQUIRED,
607 REQUEST_ID,
608 REVISION_CONTROL,
609 RFQ_REQUIRED_FLAG,
610 ROW_ID,
611 SERIAL_CONTROL,
612 SHELF_LIFE_CODE,
613 SHELF_LIFE_DAYS,
614 STOCKABLE_FLAG,
615 SUBSTITUTE_RCPT,
616 TAXABLE_FLAG,
617 TAX_CODE,
618 UNIT_LIST_PRICE,
619 UNORDERED_RCPT,
620 UN_NUMBER_ID,
621 SEGMENT1,
622 USER_ATTRIBUTE1,
626 USER_ATTRIBUTE5,
623 USER_ATTRIBUTE2,
624 USER_ATTRIBUTE3,
625 USER_ATTRIBUTE4,
627 OPERATION_CODE,
628 COLLECTION_STATUS,
629 ITEM_TYPE) /* Enh# 2544906 */
630 SELECT
631 APPROVED_SUPPLIER,
632 BUYER_FK,
633 CREATION_DATE,
634 CATSET_CATEGORY_FK,
635 INVENTORY_ITEM_ID, --CATSET_CATEGORY_FK_KEY,
636 DESCRIPTION,
637 NULL, --ERROR_CODE,
638 EXPRS_DELIVERY,
639 HAZARD_CLASS_ID,
640 INSP_REQUIRED,
641 l_instance,
642 INTERNAL_ORD_FLAG,
643 SUBSTRB(INV_PLANNING_CODE, 1, 40),
644 SUBSTRB(ITEM_NUMBER, 1, 240),
645 ITEM_NUMBER_FK || '-' || l_instance, -- ITEM_NUMBER_FK, /* Bug# 2558245 */
646 NULL, --ITEM_NUMBER_FK_KEY,
647 SUBSTRB(ITEM_ORG_DP, 1, 240),
648 ITEM_ORG_PK || '-' || l_instance, -- ITEM_ORG_PK, /* Bug# 2558245 */
649 INVENTORY_ITEM_ID,
650 ORGANIZATION_ID,
651 LAST_UPDATE_DATE,
652 LOCATOR_CONTROL,
653 EFFECTIVITY_CONTROL,
654 LOT_CONTROL,
655 SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
656 MARKET_PRICE,
657 MRP_PLN_METHOD,
658 SUBSTRB(NAME, 1, 320),
659 NULL, --ONE_TIME_FLAG,
660 OUTSIDE_OP_FLAG,
661 PLANNER_FK,
662 PRICE_TOL_PERCENT,
663 PROD_FAMILY_FK,
664 ORGANIZATION_ID, --PROD_FAMILY_FK_KEY,
665 PURCHASABLE_FLAG,
666 RECEIPT_REQUIRED,
667 NULL, --REQUEST_ID,
668 REVISION_CONTROL,
669 RFQ_REQUIRED_FLAG,
670 NULL, --ROW_ID,
671 SERIAL_CONTROL,
672 SHELF_LIFE_CODE,
673 SHELF_LIFE_DAYS,
674 STOCKABLE_FLAG,
675 SUBSTITUTE_RCPT,
676 TAXABLE_FLAG,
677 TAX_CODE,
678 UNIT_LIST_PRICE,
679 UNORDERED_RCPT,
680 UN_NUMBER_ID,
681 SEGMENT1,
682 USER_ATTRIBUTE1,
683 USER_ATTRIBUTE2,
684 USER_ATTRIBUTE3,
685 USER_ATTRIBUTE4,
686 USER_ATTRIBUTE5,
687 NULL, -- OPERATION_CODE
688 'READY',
689 ITEM_TYPE /* Enh# 2544906 */
690 FROM EDW_ITEM_ITEMORG_LCV
691 WHERE inventory_item_id = category_assignments_rec.inventory_item_id
692 AND organization_id = category_assignments_rec.organization_id
693 AND last_update_date NOT BETWEEN l_push_date_range1 AND l_push_date_range2; /* Bug# 2659263 */
694
695 END LOOP;
696
697 CLOSE category_assignments_cursor;
698 edw_log.put_line('Done Inserting category changed items into item-org ');
699
700 COMMIT;
701
702 edw_log.put_line('Resolving category fks for staging table records');
703 edw_log.put_line(' ');
704
705 /* Bug# 2631155 added DECODE by dsakalle */
706 /* Bug# 2559696 Removed the use of EDW_ITEMS_CATEGORY_FKV by dsakalle */
707
708 UPDATE EDW_ITEM_ITEMORG_TEMP
709 SET CATSET_CATEGORY_FK =
710 (SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(1)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
711 FROM
712 MTL_ITEM_CATEGORIES cat,
713 MTL_CATEGORY_SETS_TL tl
714 WHERE cat.organization_id = prod_family_fk_key
715 AND cat.inventory_item_id = catset_category_fk_key
716 AND tl.category_set_name = l_itemorg_catset1_name
717 AND cat.category_set_id = tl.category_set_id
718 AND tl.language = userenv('LANG')),
719 PROD_FAMILY_FK =
720 (SELECT DECODE(count(cat.category_id), 1, max(cat.category_id) || '-' || l_instance, 'NA_EDW')
721 FROM
722 MTL_ITEM_CATEGORIES cat,
723 MTL_CATEGORY_SETS_TL tl
724 WHERE cat.organization_id = prod_family_fk_key
725 AND cat.inventory_item_id = catset_category_fk_key
726 AND tl.category_set_name = 'Product Family'
727 AND cat.category_set_id = tl.category_set_id
728 AND tl.language = userenv('LANG'));
729
730 COMMIT;
731
732 edw_log.put_line('Resolving lookups for staging table records');
733 edw_log.put_line(' ');
734
735 UPDATE EDW_ITEM_ITEMORG_TEMP
736 SET MAKE_OR_BUY_FLAG =
737 (SELECT lkup.meaning
738 FROM mfg_lookups lkup
739 WHERE lkup.lookup_type = 'MTL_PLANNING_MAKE_BUY'
740 AND lkup.lookup_code = TO_NUMBER(make_or_buy_flag)),
741 LOCATOR_CONTROL =
742 (SELECT lkup.meaning
743 FROM mfg_lookups lkup
744 WHERE lkup.lookup_type = 'MTL_LOCATION_CONTROL'
745 AND lkup.lookup_code = TO_NUMBER(locator_control)),
746 EFFECTIVITY_CONTROL =
747 (SELECT lkup.meaning
748 FROM mfg_lookups lkup
749 WHERE lkup.lookup_type = 'MTL_EFFECTIVITY_CONTROL'
750 AND lkup.lookup_code = TO_NUMBER(effectivity_control)),
751 LOT_CONTROL =
752 (SELECT lkup.meaning
753 FROM mfg_lookups lkup
754 WHERE lkup.lookup_type = 'MTL_LOT_CONTROL'
755 AND lkup.lookup_code = TO_NUMBER(lot_control)),
756 INV_PLANNING_CODE =
757 (SELECT lkup.meaning
758 FROM mfg_lookups lkup
759 WHERE lkup.lookup_type = 'MTL_MATERIAL_PLANNING'
763 FROM mfg_lookups lkup
760 AND lkup.lookup_code = TO_NUMBER(inv_planning_code)),
761 MRP_PLN_METHOD =
762 (SELECT lkup.meaning
764 WHERE lkup.lookup_type = 'MRP_PLANNING_CODE'
765 AND lkup.lookup_code = TO_NUMBER(mrp_pln_method)),
766 REVISION_CONTROL =
767 (SELECT lkup.meaning
768 FROM mfg_lookups lkup
769 WHERE lkup.lookup_type = 'MTL_ENG_QUANTITY'
770 AND lkup.lookup_code = TO_NUMBER(revision_control)),
771 SHELF_LIFE_CODE =
772 (SELECT lkup.meaning
773 FROM mfg_lookups lkup
774 WHERE lkup.lookup_type = 'MTL_SHELF_LIFE'
775 AND lkup.lookup_code = TO_NUMBER(shelf_life_code)),
776 SERIAL_CONTROL =
777 (SELECT lkup.meaning
778 FROM mfg_lookups lkup
779 WHERE lkup.lookup_type = 'MTL_SERIAL_NUMBER'
780 AND lkup.lookup_code = TO_NUMBER(serial_control)),
781 CATSET_CATEGORY_FK_KEY = NULL,
782 PROD_FAMILY_FK_KEY = NULL,
783 CATSET_CATEGORY_FK = NVL(CATSET_CATEGORY_FK, 'NA_EDW'),
784 PROD_FAMILY_FK = NVL(PROD_FAMILY_FK, 'NA_EDW');
785
786 COMMIT;
787
788 edw_log.put_line('Inserting TEMP table records into staging table');
789 edw_log.put_line(' ');
790
791 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
792 APPROVED_SUPPLIER,
793 BUYER_FK,
794 CREATION_DATE,
795 CATSET_CATEGORY_FK,
796 CATSET_CATEGORY_FK_KEY,
797 DESCRIPTION,
798 ERROR_CODE,
799 EXPRS_DELIVERY,
800 HAZARD_CLASS_ID,
801 INSP_REQUIRED,
802 INSTANCE,
803 INTERNAL_ORD_FLAG,
804 INV_PLANNING_CODE,
805 ITEM_NUMBER,
806 ITEM_NUMBER_FK,
807 ITEM_NUMBER_FK_KEY,
808 ITEM_ORG_DP,
809 ITEM_ORG_PK,
810 INVENTORY_ITEM_ID,
811 ORGANIZATION_ID,
812 LAST_UPDATE_DATE,
813 LOCATOR_CONTROL,
814 EFFECTIVITY_CONTROL,
815 LOT_CONTROL,
816 MAKE_OR_BUY_FLAG,
817 MARKET_PRICE,
818 MRP_PLN_METHOD,
819 NAME,
820 ONE_TIME_FLAG,
821 OUTSIDE_OP_FLAG,
822 PLANNER_FK,
823 PRICE_TOL_PERCENT,
824 PROD_FAMILY_FK,
825 PROD_FAMILY_FK_KEY,
826 PURCHASABLE_FLAG,
827 RECEIPT_REQUIRED,
828 REQUEST_ID,
829 REVISION_CONTROL,
830 RFQ_REQUIRED_FLAG,
831 ROW_ID,
832 SERIAL_CONTROL,
833 SHELF_LIFE_CODE,
834 SHELF_LIFE_DAYS,
835 STOCKABLE_FLAG,
836 SUBSTITUTE_RCPT,
837 TAXABLE_FLAG,
838 TAX_CODE,
839 UNIT_LIST_PRICE,
840 UNORDERED_RCPT,
841 UN_NUMBER_ID,
842 SEGMENT1,
843 USER_ATTRIBUTE1,
844 USER_ATTRIBUTE2,
845 USER_ATTRIBUTE3,
846 USER_ATTRIBUTE4,
847 USER_ATTRIBUTE5,
848 OPERATION_CODE,
849 COLLECTION_STATUS,
850 ITEM_TYPE) /* Enh# 2544906 */
851 SELECT
852 APPROVED_SUPPLIER,
853 BUYER_FK,
854 CREATION_DATE,
855 CATSET_CATEGORY_FK,
856 CATSET_CATEGORY_FK_KEY, --CATSET_CATEGORY_FK_KEY,
857 DESCRIPTION,
858 NULL, --ERROR_CODE,
859 EXPRS_DELIVERY,
860 HAZARD_CLASS_ID,
861 INSP_REQUIRED,
862 INSTANCE,
863 INTERNAL_ORD_FLAG,
864 SUBSTRB(INV_PLANNING_CODE, 1, 40),
865 SUBSTRB(ITEM_NUMBER, 1, 240),
866 ITEM_NUMBER_FK,
867 ITEM_NUMBER_FK_KEY, --ITEM_NUMBER_FK_KEY,
868 SUBSTRB(ITEM_ORG_DP, 1, 240),
869 ITEM_ORG_PK,
870 INVENTORY_ITEM_ID,
871 ORGANIZATION_ID,
872 LAST_UPDATE_DATE,
873 LOCATOR_CONTROL,
874 EFFECTIVITY_CONTROL,
875 LOT_CONTROL,
876 SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
877 MARKET_PRICE,
878 MRP_PLN_METHOD,
879 SUBSTRB(NAME, 1, 320),
880 NULL, --ONE_TIME_FLAG,
881 OUTSIDE_OP_FLAG,
882 PLANNER_FK,
883 PRICE_TOL_PERCENT,
884 PROD_FAMILY_FK,
885 PROD_FAMILY_FK_KEY, --PROD_FAMILY_FK_KEY,
886 PURCHASABLE_FLAG,
887 RECEIPT_REQUIRED,
888 NULL, --REQUEST_ID,
889 REVISION_CONTROL,
890 RFQ_REQUIRED_FLAG,
891 NULL, --ROW_ID,
892 SERIAL_CONTROL,
893 SHELF_LIFE_CODE,
894 SHELF_LIFE_DAYS,
895 STOCKABLE_FLAG,
896 SUBSTITUTE_RCPT,
897 TAXABLE_FLAG,
898 TAX_CODE,
899 UNIT_LIST_PRICE,
900 UNORDERED_RCPT,
901 UN_NUMBER_ID,
902 SEGMENT1,
903 USER_ATTRIBUTE1,
904 USER_ATTRIBUTE2,
905 USER_ATTRIBUTE3,
906 USER_ATTRIBUTE4,
907 USER_ATTRIBUTE5,
908 NULL, -- OPERATION_CODE
909 'READY',
910 ITEM_TYPE /* Enh# 2544906 */
911 FROM EDW_ITEM_ITEMORG_TEMP;
912
913 COMMIT;
914
915 IF l_collect_onetime = 'Y' THEN
916
917 edw_log.put_line(' ');
918 edw_log.put_line('Pushing EDW_ITEM_ITEMORG for one time items');
919
920 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
921 APPROVED_SUPPLIER,
922 BUYER_FK,
923 CREATION_DATE,
924 CATSET_CATEGORY_FK,
925 CATSET_CATEGORY_FK_KEY,
926 DESCRIPTION,
927 ERROR_CODE,
928 EXPRS_DELIVERY,
929 INSP_REQUIRED,
933 ITEM_NUMBER,
930 INSTANCE,
931 INTERNAL_ORD_FLAG,
932 INV_PLANNING_CODE,
934 ITEM_NUMBER_FK,
935 ITEM_NUMBER_FK_KEY,
936 ITEM_ORG_DP,
937 ITEM_ORG_PK,
938 LAST_UPDATE_DATE,
939 LOCATOR_CONTROL,
940 EFFECTIVITY_CONTROL,
941 LOT_CONTROL,
942 MAKE_OR_BUY_FLAG,
943 MARKET_PRICE,
944 MRP_PLN_METHOD,
945 NAME,
946 ONE_TIME_FLAG,
947 OUTSIDE_OP_FLAG,
948 PLANNER_FK,
949 PRICE_TOL_PERCENT,
950 PROD_FAMILY_FK,
951 PROD_FAMILY_FK_KEY,
952 PURCHASABLE_FLAG,
953 RECEIPT_REQUIRED,
954 REQUEST_ID,
955 REVISION_CONTROL,
956 RFQ_REQUIRED_FLAG,
957 ROW_ID,
958 SERIAL_CONTROL,
959 SHELF_LIFE_CODE,
960 SHELF_LIFE_DAYS,
961 STOCKABLE_FLAG,
962 SUBSTITUTE_RCPT,
963 TAXABLE_FLAG,
964 TAX_CODE,
965 UNIT_LIST_PRICE,
966 UNORDERED_RCPT,
967 USER_ATTRIBUTE1,
968 USER_ATTRIBUTE2,
969 USER_ATTRIBUTE3,
970 USER_ATTRIBUTE4,
971 USER_ATTRIBUTE5,
972 OPERATION_CODE,
973 COLLECTION_STATUS)
974 SELECT
975 APPROVED_SUPPLIER,
976 BUYER_FK,
977 CREATION_DATE,
978 'NA_EDW', --CATSET_CATEGORY_FK
979 NULL, --CATSET_CATEGORY_FK_KEY,
980 DESCRIPTION,
981 NULL, --ERROR_CODE,
982 EXPRS_DELIVERY,
983 INSP_REQUIRED,
984 l_instance,
985 INTERNAL_ORD_FLAG,
986 SUBSTRB(INV_PLANNING_CODE, 1, 40),
987 SUBSTRB(ITEM_NUMBER, 1, 240),
988 ITEM_NUMBER_FK || '-' || l_instance || '-ONETIME', -- ITEM_NUMBER_FK, /* Bug# 2558245 */
989 NULL, --ITEM_NUMBER_FK_KEY,
990 SUBSTRB(ITEM_ORG_DP, 1, 240),
991 ITEM_ORG_PK || '-' || l_instance || '-ONETIME', -- ITEM_ORG_PK, /* Bug# 2558245 */
992 LAST_UPDATE_DATE,
993 LOCATOR_CONTROL,
994 EFFECTIVITY_CONTROL,
995 LOT_CONTROL,
996 SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
997 MARKET_PRICE,
998 MRP_PLN_METHOD,
999 SUBSTRB(NAME, 1, 320),
1000 ONE_TIME_FLAG,
1001 OUTSIDE_OP_FLAG,
1002 PLANNER_FK,
1003 PRICE_TOL_PERCENT,
1004 NVL(PROD_FAMILY_FK, 'NA_EDW'),
1005 NULL, --PROD_FAMILY_FK_KEY,
1006 PURCHASABLE_FLAG,
1007 RECEIPT_REQUIRED,
1008 NULL, --REQUEST_ID,
1009 REVISION_CONTROL,
1010 RFQ_REQUIRED_FLAG,
1011 NULL, --ROW_ID,
1012 SERIAL_CONTROL,
1013 SHELF_LIFE_CODE,
1014 SHELF_LIFE_DAYS,
1015 STOCKABLE_FLAG,
1016 SUBSTITUTE_RCPT,
1017 TAXABLE_FLAG,
1018 TAX_CODE,
1019 UNIT_LIST_PRICE,
1020 UNORDERED_RCPT,
1021 USER_ATTRIBUTE1,
1022 USER_ATTRIBUTE2,
1023 USER_ATTRIBUTE3,
1024 USER_ATTRIBUTE4,
1025 USER_ATTRIBUTE5,
1026 NULL, -- OPERATION_CODE
1027 'READY'
1028 FROM EDW_ITEM_ONETIME_ITEMORG_LCV
1029 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1030
1031 l_rows_inserted := SQL%ROWCOUNT;
1032 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1033 ' rows into the staging table');
1034 edw_log.put_line(' ');
1035 COMMIT;
1036
1037 ELSE
1038
1039 edw_log.put_line(' ');
1040 edw_log.put_line('EDW_ITEM_ITEMORG for one time items will not be pushed');
1041
1042 END IF;
1043
1044 edw_log.put_line(' ');
1045 edw_log.put_line('Pushing items to lower level EDW_ITEM_ITEMREV');
1046 l_rows_inserted := 0;
1047 l_commit_count := 0;
1048
1049 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
1050 ITEM_REVISION_PK,
1051 ITEM_ORG_FK,
1052 INVENTORY_ITEM_ID,
1053 ORGANIZATION_ID,
1054 NAME,
1055 INSTANCE,
1056 COLLECTION_STATUS)
1057 SELECT
1058 SUBSTRB(ITEM_ORG_PK || '-' || l_instance, 1, 315)|| '-IORG', /* Bug# 2558245 */
1059 SUBSTRB(ITEM_ORG_PK || '-' || l_instance, 1, 320), /* Bug# 2558245 */
1060 INVENTORY_ITEM_ID,
1061 ORGANIZATION_ID,
1062 SUBSTRB(l_all_item_revs || ' (' || NAME || ')', 1, 320),
1063 l_instance,
1064 'READY'
1065 FROM EDW_ITEM_ITEMORG_LCV
1066 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1067
1068 l_rows_inserted := SQL%ROWCOUNT;
1069 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1070 ' rows into the staging table');
1071 edw_log.put_line(' ');
1072 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
1073 COMMIT;
1074
1075 IF l_collect_onetime = 'Y' THEN
1076
1077 l_rows_inserted := 0;
1078 edw_log.put_line('Pushing one time items to lower level EDW_ITEM_ITEMREV');
1079
1080 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
1081 ITEM_ORG_FK,
1082 ITEM_REVISION_PK,
1083 NAME,
1084 INSTANCE,
1085 COLLECTION_STATUS)
1086 SELECT
1087 SUBSTRB(ITEM_ORG_PK || '-' || l_instance,1,312) || '-ONETIME', /* Bug# 2558245 */
1088 SUBSTRB(ITEM_ORG_PK || '-' || l_instance,1,307) || '-ONETIME' || '-IORG',
1092 FROM EDW_ITEM_ONETIME_ITEMORG_LCV
1089 SUBSTRB(l_all_item_revs || '(' || NAME || ')',1,320),
1090 l_instance,
1091 'READY' --COLLECTION_STATUS
1093 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1094
1095 l_rows_inserted := SQL%ROWCOUNT;
1096 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1097 ' rows into the staging table');
1098 edw_log.put_line(' ');
1099 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
1100 COMMIT;
1101
1102 END IF;
1103
1104 EXCEPTION WHEN OTHERS THEN
1105 RAISE;
1106
1107 END Push_EDW_ITEM_ITEMORG;
1108
1109 PROCEDURE Push_EDW_ITEM_ITEM(
1110 p_from_date IN DATE,
1111 p_to_date IN DATE) IS
1112 l_staging_table_name VARCHAR2(30) :='EDW_ITEM_ITEM_LSTG' ;
1113 L_PUSH_DATE_RANGE1 DATE := NULL;
1114 L_PUSH_DATE_RANGE2 DATE := NULL;
1115 l_rows_inserted NUMBER := 0;
1116 -- l_instance VARCHAR2(240) := NULL; /* Bug# 2558245 */
1117
1118 /* Bug# 2558245
1119 CURSOR l_instance_csr is
1120 SELECT instance_code
1121 FROM edw_local_instance;
1122 */
1123
1124 l_all_item_orgs VARCHAR2(100);
1125 l_all_item_revs VARCHAR2(100);
1126
1127 /**Bug: 5130137
1128 CURSOR category_assignments_cursor IS Bug# 2197243
1129 SELECT
1130 MIC.INVENTORY_ITEM_ID,
1131 MIC.ORGANIZATION_ID
1132 FROM
1133 MTL_ITEM_CATEGORIES MIC,
1134 MTL_CATEGORY_SETS_TL SETS
1135 WHERE MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
1136 AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
1137 AND SETS.LANGUAGE = userenv('LANG')
1138 AND SETS.CATEGORY_SET_NAME IN (l_item_catset1_name,
1139 l_item_catset2_name,
1140 l_item_catset3_name);
1141 MTL_CATEGORIES CAT,
1142 MTL_ITEM_CATEGORIES MIC,
1143 MTL_CATEGORY_SETS SETS
1144 WHERE CAT.CATEGORY_ID = MIC.CATEGORY_ID
1145 AND MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
1146 AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
1147 AND SETS.CATEGORY_SET_NAME IN (l_item_catset1_name,
1148 l_item_catset2_name,
1149 l_item_catset3_name);
1150
1151 category_assignments_rec category_assignments_cursor%ROWTYPE;**/
1152
1153 BEGIN
1154
1155 l_all_item_orgs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IORG');
1156 l_all_item_revs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IREV');
1157
1158 l_push_date_range1 := p_from_date;
1159 l_push_date_range2:= p_to_date;
1160
1161 /* Bug# 2558245
1162 for l_instance_rec in l_instance_csr loop
1163 l_instance := l_instance_rec.instance_code;
1164 end loop;
1165 */
1166
1167 edw_log.put_line(' ');
1168 edw_log.put_line('Pushing EDW_ITEM_ITEM');
1169
1170 INSERT INTO EDW_ITEM_ITEM_TEMP(
1171 CREATION_DATE,
1172 CATSET1_CATEGORY_FK,
1173 CATSET1_CATEGORY_FK_KEY,
1174 CATSET2_CATEGORY_FK,
1175 CATSET2_CATEGORY_FK_KEY,
1176 CATSET3_CATEGORY_FK,
1177 CATSET3_CATEGORY_FK_KEY,
1178 DESCRIPTION,
1179 ERROR_CODE,
1180 INSTANCE,
1181 ITEM_NAME,
1182 ITEM_NUMBER_DP,
1183 ITEM_NUMBER_PK,
1184 INVENTORY_ITEM_ID,
1185 ORGANIZATION_ID,
1186 LAST_UPDATE_DATE,
1187 NAME,
1188 ONE_TIME_FLAG,
1189 PRODUCT_GROUP_FK,
1190 PRODUCT_GROUP_FK_KEY,
1191 REQUEST_ID,
1192 ROW_ID,
1193 USER_ATTRIBUTE1,
1194 USER_ATTRIBUTE2,
1195 USER_ATTRIBUTE3,
1196 USER_ATTRIBUTE4,
1197 USER_ATTRIBUTE5,
1198 OPERATION_CODE,
1199 COLLECTION_STATUS,
1200 ITEM_TYPE) /* Enh# 2544906 */
1201 SELECT
1202 CREATION_DATE,
1203 CATSET1_CATEGORY_FK,
1204 INVENTORY_ITEM_ID, -- CATSET1_CATEGORY_FK_KEY
1205 CATSET2_CATEGORY_FK,
1206 NULL, -- CATSET2_CATEGORY_FK_KEY,
1207 CATSET3_CATEGORY_FK,
1208 NULL, -- CATSET3_CATEGORY_FK_KEY,
1209 DESCRIPTION,
1210 NULL, --ERROR_CODE,
1211 l_instance, --INSTANCE, /* Bug# 2558245 */
1212 ITEM_NAME,
1213 ITEM_NUMBER_DP,
1214 ITEM_NUMBER_PK || '-' || l_instance, -- ITEM_NUMBER_PK, /* Bug# 2558245 */
1215 INVENTORY_ITEM_ID,
1216 ORGANIZATION_ID,
1217 LAST_UPDATE_DATE,
1218 SUBSTRB(NAME, 1, 320),
1219 NULL, --ONE_TIME_FLAG,
1220 PRODUCT_GROUP_FK,
1221 ORGANIZATION_ID, --PRODUCT_GROUP_FK_KEY,
1222 NULL, --REQUEST_ID,
1223 NULL, --ROW_ID,
1224 USER_ATTRIBUTE1,
1225 USER_ATTRIBUTE2,
1226 USER_ATTRIBUTE3,
1227 USER_ATTRIBUTE4,
1228 USER_ATTRIBUTE5,
1229 NULL, -- OPERATION_CODE
1230 'READY',
1231 ITEM_TYPE /* Enh# 2544906 */
1232 FROM EDW_ITEM_ITEM_LCV
1233 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1234
1235 l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
1236 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1237 ' rows into the staging table');
1238 edw_log.put_line(' ');
1239
1243 */
1240 /* Bug# 2197243 */
1241 /** Need to INSERT additional items for whom the category assignments
1242 ** have changed.
1244 edw_log.put_line('Inserting additional items due to category changes ');
1245
1246 /**5130137 OPEN category_assignments_cursor;
1247 LOOP
1248 FETCH category_assignments_cursor INTO category_assignments_rec;
1249 EXIT WHEN category_assignments_cursor%notfound;*/
1250
1251 INSERT INTO EDW_ITEM_ITEM_TEMP(
1252 CREATION_DATE,
1253 CATSET1_CATEGORY_FK,
1254 CATSET1_CATEGORY_FK_KEY,
1255 CATSET2_CATEGORY_FK,
1256 CATSET2_CATEGORY_FK_KEY,
1257 CATSET3_CATEGORY_FK,
1258 CATSET3_CATEGORY_FK_KEY,
1259 DESCRIPTION,
1260 ERROR_CODE,
1261 INSTANCE,
1262 ITEM_NAME,
1263 ITEM_NUMBER_DP,
1264 ITEM_NUMBER_PK,
1265 INVENTORY_ITEM_ID,
1266 ORGANIZATION_ID,
1267 LAST_UPDATE_DATE,
1268 NAME,
1269 ONE_TIME_FLAG,
1270 PRODUCT_GROUP_FK,
1271 PRODUCT_GROUP_FK_KEY,
1272 REQUEST_ID,
1273 ROW_ID,
1274 USER_ATTRIBUTE1,
1275 USER_ATTRIBUTE2,
1276 USER_ATTRIBUTE3,
1277 USER_ATTRIBUTE4,
1278 USER_ATTRIBUTE5,
1279 OPERATION_CODE,
1280 COLLECTION_STATUS,
1281 ITEM_TYPE) /* Enh# 2544906 */
1282 SELECT
1283 CREATION_DATE,
1284 CATSET1_CATEGORY_FK,
1285 eil.INVENTORY_ITEM_ID, -- CATSET1_CATEGORY_FK_KEY
1286 CATSET2_CATEGORY_FK,
1287 NULL, -- CATSET2_CATEGORY_FK_KEY,
1288 CATSET3_CATEGORY_FK,
1289 NULL, -- CATSET3_CATEGORY_FK_KEY,
1290 DESCRIPTION,
1291 NULL, --ERROR_CODE,
1292 l_instance, --INSTANCE, /* Bug# 2558245 */
1293 ITEM_NAME,
1294 ITEM_NUMBER_DP,
1295 ITEM_NUMBER_PK || '-' || l_instance, -- ITEM_NUMBER_PK, /* Bug# 2558245 */
1296 eil.INVENTORY_ITEM_ID,
1297 eil.ORGANIZATION_ID,
1298 LAST_UPDATE_DATE,
1299 SUBSTRB(NAME, 1, 320),
1300 NULL, --ONE_TIME_FLAG,
1301 PRODUCT_GROUP_FK,
1302 eil.ORGANIZATION_ID, --PRODUCT_GROUP_FK_KEY,
1303 NULL, --REQUEST_ID,
1304 NULL, --ROW_ID,
1305 USER_ATTRIBUTE1,
1306 USER_ATTRIBUTE2,
1307 USER_ATTRIBUTE3,
1308 USER_ATTRIBUTE4,
1309 USER_ATTRIBUTE5,
1310 NULL, -- OPERATION_CODE
1311 'READY',
1312 ITEM_TYPE /* Enh# 2544906 */
1313 FROM EDW_ITEM_ITEM_LCV eil,
1314 (
1315 SELECT
1316 MIC.INVENTORY_ITEM_ID,
1317 MIC.ORGANIZATION_ID
1318 FROM
1319 MTL_ITEM_CATEGORIES MIC,
1320 MTL_CATEGORY_SETS_TL SETS
1321 WHERE MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
1322 AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
1323 AND SETS.LANGUAGE = userenv('LANG')
1324 AND SETS.CATEGORY_SET_NAME IN (l_item_catset1_name,
1325 l_item_catset2_name,
1326 l_item_catset3_name)) category_assignments_rec
1327 WHERE eil.inventory_item_id = category_assignments_rec.inventory_item_id
1328 AND eil.organization_id = category_assignments_rec.organization_id
1329 AND last_update_date not BETWEEN l_push_date_range1 AND l_push_date_range2; /* Bug# 2659263 */
1330
1331 -- END LOOP;
1332
1333 edw_log.put_line('Done Inserting category changed items into item ');
1334
1335 COMMIT;
1336
1337 edw_log.put_line('Resolving category fks for staging table records');
1338 edw_log.put_line(' ');
1339
1340 /* Bug# 2631155 added DECODE by dsakalle */
1341 /* Bug# 2559696 Removed the use of EDW_ITEMS_CATEGORY_FKV By dsakalle */
1342
1343 IF (l_item_catset3_name = 'NA_EDW') THEN
1344 UPDATE EDW_ITEM_ITEM_TEMP
1345 SET CATSET1_CATEGORY_FK =
1346 (SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
1347 FROM
1348 MTL_ITEM_CATEGORIES cat,
1349 MTL_CATEGORY_SETS sets
1350 WHERE cat.organization_id = product_group_fk_key
1351 AND cat.inventory_item_id = catset1_category_fk_key
1352 -- AND sets.control_level = 1 Bug : 3720586
1353 AND sets.category_set_name = l_item_catset1_name
1354 AND cat.category_set_id = sets.category_set_id),
1355 CATSET2_CATEGORY_FK =
1356 (SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
1357 FROM
1358 MTL_ITEM_CATEGORIES cat,
1359 MTL_CATEGORY_SETS sets
1360 WHERE cat.organization_id = product_group_fk_key
1361 AND cat.inventory_item_id = catset1_category_fk_key
1362 AND sets.control_level = 1
1363 AND sets.category_set_name = l_item_catset2_name
1364 AND cat.category_set_id = sets.category_set_id),
1365 CATSET3_CATEGORY_FK = edw_itemcustom_m_c.get_product_category_set_fk(catset1_category_fk_key,
1366 product_group_fk_key, instance),
1367 PRODUCT_GROUP_FK = edw_items_pkg.get_prod_grp_fk(catset1_category_fk_key,
1368 product_group_fk_key, instance);
1369
1370 COMMIT;
1371 ELSE
1372 UPDATE EDW_ITEM_ITEM_TEMP
1373 SET CATSET1_CATEGORY_FK =
1377 MTL_CATEGORY_SETS sets
1374 (SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
1375 FROM
1376 MTL_ITEM_CATEGORIES cat,
1378 WHERE cat.organization_id = product_group_fk_key
1379 AND cat.inventory_item_id = catset1_category_fk_key
1380 -- AND sets.control_level = 1 Bug : 3720586
1381 AND sets.category_set_name = l_item_catset1_name
1382 AND cat.category_set_id = sets.category_set_id),
1383 CATSET2_CATEGORY_FK =
1384 (SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
1385 FROM
1386 MTL_ITEM_CATEGORIES cat,
1387 MTL_CATEGORY_SETS sets
1388 WHERE cat.organization_id = product_group_fk_key
1389 AND cat.inventory_item_id = catset1_category_fk_key
1390 AND sets.control_level = 1
1391 AND sets.category_set_name = l_item_catset2_name
1392 AND cat.category_set_id = sets.category_set_id),
1393 CATSET3_CATEGORY_FK =
1394 (SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
1395 FROM
1396 MTL_ITEM_CATEGORIES cat,
1397 MTL_CATEGORY_SETS sets
1398 WHERE cat.organization_id = product_group_fk_key
1399 AND cat.inventory_item_id = catset1_category_fk_key
1400 AND sets.control_level = 1
1401 AND sets.category_set_name = l_item_catset3_name
1402 AND cat.category_set_id = sets.category_set_id),
1403 PRODUCT_GROUP_FK = edw_items_pkg.get_prod_grp_fk(catset1_category_fk_key,
1404 product_group_fk_key, instance);
1405
1406 COMMIT;
1407 END IF;
1408
1409 UPDATE EDW_ITEM_ITEM_TEMP
1410 SET
1411 CATSET1_CATEGORY_FK = NVL(CATSET1_CATEGORY_FK, 'NA_EDW'),
1412 CATSET2_CATEGORY_FK = NVL(CATSET2_CATEGORY_FK, 'NA_EDW'),
1413 CATSET3_CATEGORY_FK = NVL(CATSET3_CATEGORY_FK, 'NA_EDW'),
1414 PRODUCT_GROUP_FK = NVL(PRODUCT_GROUP_FK, 'NA_EDW'),
1415 PRODUCT_GROUP_FK_KEY = NULL,
1416 CATSET1_CATEGORY_FK_KEY = NULL;
1417
1418 COMMIT;
1419
1420 INSERT INTO EDW_ITEM_ITEM_LSTG(
1421 CREATION_DATE,
1422 CATSET1_CATEGORY_FK,
1423 CATSET1_CATEGORY_FK_KEY,
1424 CATSET2_CATEGORY_FK,
1425 CATSET2_CATEGORY_FK_KEY,
1426 CATSET3_CATEGORY_FK,
1427 CATSET3_CATEGORY_FK_KEY,
1428 DESCRIPTION,
1429 ERROR_CODE,
1430 INSTANCE,
1431 ITEM_NAME,
1432 ITEM_NUMBER_DP,
1433 ITEM_NUMBER_PK,
1434 INVENTORY_ITEM_ID,
1435 ORGANIZATION_ID,
1436 LAST_UPDATE_DATE,
1437 NAME,
1438 ONE_TIME_FLAG,
1439 PRODUCT_GROUP_FK,
1440 PRODUCT_GROUP_FK_KEY,
1441 REQUEST_ID,
1442 ROW_ID,
1443 USER_ATTRIBUTE1,
1444 USER_ATTRIBUTE2,
1445 USER_ATTRIBUTE3,
1446 USER_ATTRIBUTE4,
1447 USER_ATTRIBUTE5,
1448 OPERATION_CODE,
1449 COLLECTION_STATUS,
1450 ITEM_TYPE) /* Enh# 2544906 */
1451 SELECT
1452 CREATION_DATE,
1453 CATSET1_CATEGORY_FK,
1454 NULL, --CATSET1_CATEGORY_FK_KEY
1455 CATSET2_CATEGORY_FK,
1456 NULL, --CATSET2_CATEGORY_FK_KEY
1457 CATSET3_CATEGORY_FK,
1458 NULL, --CATSET3_CATEGORY_FK_KEY
1459 DESCRIPTION,
1460 NULL, --ERROR_CODE,
1461 INSTANCE,
1462 ITEM_NAME,
1463 ITEM_NUMBER_DP,
1464 ITEM_NUMBER_PK,
1465 INVENTORY_ITEM_ID,
1466 ORGANIZATION_ID,
1467 LAST_UPDATE_DATE,
1468 SUBSTRB(NAME, 1, 320),
1469 NULL, --ONE_TIME_FLAG,
1470 PRODUCT_GROUP_FK, --PRODUCT_FAMILY_FK
1471 NULL, --PRODUCT_GROUP_FK_KEY,
1472 NULL, --REQUEST_ID,
1473 NULL, --ROW_ID,
1474 USER_ATTRIBUTE1,
1475 USER_ATTRIBUTE2,
1476 USER_ATTRIBUTE3,
1477 USER_ATTRIBUTE4,
1478 USER_ATTRIBUTE5,
1479 NULL, -- OPERATION_CODE
1480 'READY',
1481 ITEM_TYPE /* Enh# 2544906 */
1482 FROM EDW_ITEM_ITEM_TEMP;
1483 COMMIT;
1484
1485 edw_log.put_line('Pushing EDW_ITEM_ITEM records to Item Org level');
1486
1487 INSERT INTO edw_item_itemorg_lstg(
1488 ITEM_ORG_PK,
1489 ITEM_NUMBER_FK,
1490 INVENTORY_ITEM_ID,
1491 ORGANIZATION_ID,
1492 NAME,
1493 INSTANCE,
1494 COLLECTION_STATUS,
1495 CATSET_CATEGORY_FK,
1496 PROD_FAMILY_FK)
1497 SELECT
1498 SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance ||'-ITEM', 1, 1000), /* Bug# 2558245 */
1499 ITEM_NUMBER_PK || '-' || l_instance, /* Bug# 2558245 */
1500 INVENTORY_ITEM_ID,
1501 ORGANIZATION_ID,
1502 SUBSTRB(l_all_item_orgs || '(' || NAME || ')', 1, 320),
1503 l_instance, --INSTANCE, /* Bug# 2558245 */
1504 'READY',
1505 'NA_EDW',
1506 'NA_EDW'
1507 FROM edw_item_item_lcv
1508 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1509
1510 l_rows_inserted := SQL%ROWCOUNT;
1511 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1512 ' rows into the staging table');
1513 edw_log.put_line(' ');
1514 COMMIT;
1515
1516 edw_log.put_line('Pushing EDW_ITEM_ITEM records to Item Rev level');
1517
1518 INSERT INTO edw_item_itemrev_lstg(
1519 ITEM_REVISION_PK,
1523 NAME,
1520 ITEM_ORG_FK,
1521 INVENTORY_ITEM_ID,
1522 ORGANIZATION_ID,
1524 INSTANCE,
1525 COLLECTION_STATUS)
1526 SELECT
1527 SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance , 1, 315) || '-ITEM', /* Bug# 2558245 */
1528 SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance , 1, 315) || '-ITEM', /* Bug# 2558245 */
1529 INVENTORY_ITEM_ID,
1530 ORGANIZATION_ID,
1531 SUBSTRB(l_all_item_revs || '(' || NAME || ')', 1, 320),
1532 l_instance, --INSTANCE, /* Bug# 2558245 */
1533 'READY'
1534 FROM edw_item_item_lcv
1535 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1536
1537 l_rows_inserted := SQL%ROWCOUNT;
1538 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1539 ' rows into the staging table');
1540 edw_log.put_line(' ');
1541 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
1542 COMMIT;
1543
1544 IF l_collect_onetime = 'Y' THEN
1545
1546 edw_log.put_line('Pushing EDW_ITEM_ITEM for one time items');
1547
1548 INSERT INTO EDW_ITEM_ITEM_LSTG(
1549 CREATION_DATE,
1550 CATSET1_CATEGORY_FK,
1551 CATSET1_CATEGORY_FK_KEY,
1552 CATSET2_CATEGORY_FK,
1553 CATSET2_CATEGORY_FK_KEY,
1554 CATSET3_CATEGORY_FK,
1555 CATSET3_CATEGORY_FK_KEY,
1556 DESCRIPTION,
1557 ERROR_CODE,
1558 INSTANCE,
1559 ITEM_NAME,
1560 ITEM_NUMBER_DP,
1561 ITEM_NUMBER_PK,
1562 LAST_UPDATE_DATE,
1563 NAME,
1564 ONE_TIME_FLAG,
1565 PRODUCT_GROUP_FK,
1566 PRODUCT_GROUP_FK_KEY,
1567 REQUEST_ID,
1568 ROW_ID,
1569 USER_ATTRIBUTE1,
1570 USER_ATTRIBUTE2,
1571 USER_ATTRIBUTE3,
1572 USER_ATTRIBUTE4,
1573 USER_ATTRIBUTE5,
1574 OPERATION_CODE,
1575 COLLECTION_STATUS)
1576 SELECT
1577 CREATION_DATE,
1578 TO_CHAR(2)||'-'||CATSET_CATEGORY_FK||'-'||l_instance, -- Bug# 2848291 added l_instance
1579 NULL, --CATSET1_CATEGORY_FK_KEY,
1580 'NA_EDW',
1581 NULL, --CATSET2_CATEGORY_FK_KEY,
1582 'NA_EDW',
1583 NULL, --CATSET3_CATEGORY_FK_KEY,
1584 DESCRIPTION,
1585 NULL, --ERROR_CODE,
1586 l_instance, --INSTANCE, /* Bug# 2558245 */
1587 SUBSTRB(ITEM_NAME, 1, 240),
1588 SUBSTRB(ITEM_NUMBER_DP, 1, 240),
1589 ITEM_NUMBER_PK || '-' || l_instance || '-ONETIME', -- ITEM_NUMBER_PK, /* Bug# 2558245 */
1590 LAST_UPDATE_DATE,
1591 SUBSTRB(NAME, 1, 320),
1592 ONE_TIME_FLAG,
1593 NVL(PRODUCT_GROUP_FK, 'NA_EDW'),
1594 NULL, --PRODUCT_GROUP_FK_KEY,
1595 NULL, --REQUEST_ID,
1596 NULL, --ROW_ID,
1597 USER_ATTRIBUTE1,
1598 USER_ATTRIBUTE2,
1599 USER_ATTRIBUTE3,
1600 USER_ATTRIBUTE4,
1601 USER_ATTRIBUTE5,
1602 OPERATION_CODE,
1603 'READY'
1604 FROM EDW_ITEM_ONETIME_ITEM_LCV
1605 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1606
1607 l_rows_inserted := SQL%ROWCOUNT;
1608 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1609 ' rows into the staging table');
1610 edw_log.put_line(' ');
1611 COMMIT;
1612
1613 edw_log.put_line('Pushing one-time EDW_ITEM_ITEM records to Item Org level');
1614
1615 INSERT INTO edw_item_itemorg_lstg(
1616 ITEM_ORG_PK,
1617 ITEM_NUMBER_FK,
1618 NAME,
1619 INSTANCE,
1620 COLLECTION_STATUS,
1621 CATSET_CATEGORY_FK,
1622 PROD_FAMILY_FK)
1623 SELECT
1624 SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance,1,987)||'-ONETIME'||'-ITEM', /* Bug# 2558245 */
1625 ITEM_NUMBER_PK || '-' || l_instance || '-ONETIME', /* Bug# 2558245 */
1626 SUBSTRB(l_all_item_orgs || '(' || NAME || ')', 1, 320),
1627 l_instance, --INSTANCE, /* Bug# 2558245 */
1628 'READY',
1629 'NA_EDW',
1630 'NA_EDW'
1631 FROM edw_item_onetime_item_lcv
1632 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1633
1634 l_rows_inserted := SQL%ROWCOUNT;
1635 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1636 ' rows into the staging table');
1637 edw_log.put_line(' ');
1638 COMMIT;
1639
1640 INSERT INTO edw_item_itemrev_lstg(
1641 ITEM_REVISION_PK,
1642 ITEM_ORG_FK,
1643 NAME,
1644 INSTANCE,
1645 COLLECTION_STATUS)
1646 SELECT
1647 SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance,1,307) || '-ONETIME' || '-ITEM', /* Bug# 2558245 */
1648 SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance,1,307) || '-ONETIME' || '-ITEM', /* Bug# 2558245 */
1649 SUBSTRB(l_all_item_revs || '(' || NAME || ')', 1, 320),
1650 l_instance, --INSTANCE, /* Bug# 2558245 */
1651 'READY'
1652 FROM edw_item_onetime_item_lcv
1653 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
1654
1655 l_rows_inserted := SQL%ROWCOUNT;
1656 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
1657 ' rows into the staging table');
1658 edw_log.put_line(' ');
1659 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
1660 COMMIT;
1661
1662 ELSE
1663
1664 edw_log.put_line(' ');
1665 edw_log.put_line('EDW_ITEM_ITEM for one time items will not be pushed');
1669 --
1666
1667 END IF;
1668
1670 ---------------------------------------------------------------------------
1671 -- END OF Collection , Developer Customizable Section
1672 -- ---------------------------------------------------------------------------
1673
1674 EXCEPTION WHEN OTHERS THEN
1675 RAISE;
1676 COMMIT;
1677
1678 END Push_EDW_ITEM_ITEM;
1679
1680 PROCEDURE Insert_Category(
1681 p_from_date DATE ,
1682 p_to_date DATE ,
1683 p_staging_table_name VARCHAR2,
1684 p_view_name VARCHAR2,
1685 p_category_set_name VARCHAR2,
1686 p_control_level NUMBER) IS
1687
1688 l_stmt VARCHAR2(5000) := NULL;
1689 l_cursor NUMBER;
1690 l_rows_inserted NUMBER := 0;
1691 l_fk_name VARCHAR2(40);
1692 l_fk_value VARCHAR2(40);
1693 l_fk_key VARCHAR2(40);
1694 l_pk_value VARCHAR2(40);
1695 l_view_name VARCHAR2(1000);
1696 l_where_clause VARCHAR2(1000);
1697 l_row_cnt NUMBER; /* Bug# 2504279 */
1698 BEGIN
1699 /* Bug# 2504279, tempoary workaround begin*/
1700
1701 l_cursor:=dbms_sql.open_cursor;
1702
1703 -- Bug# 3296641
1704 IF SUBSTRB(p_view_name,1,15) = 'EDW_ITEM_CATSET' then
1705 l_where_clause := ' WHERE category_set_name = :l_category_set_name';
1706 l_where_clause := l_where_clause || ' AND COLLECTION_STATUS = ''READY''';
1707 ELSE
1708 l_where_clause := ' WHERE last_update_date BETWEEN :l_push_date_range1 AND :l_push_date_range2 AND category_set_name = :l_category_set_name';
1709 END IF;
1710
1711 l_stmt := 'SELECT count(*) row_cnt FROM '||p_view_name||l_where_clause;
1712
1713 dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
1714 dbms_sql.bind_variable(l_cursor, ':l_category_set_name', p_category_set_name);
1715
1716 -- Bug# 3296641
1717 IF SUBSTRB(p_view_name,1,15) = 'EDW_ITEM_CATSET' then
1718 null;
1719 ELSE
1720 dbms_sql.bind_variable(l_cursor, ':l_push_date_range1', p_from_date);
1721 dbms_sql.bind_variable(l_cursor, ':l_push_date_range2', p_to_date);
1722 END IF;
1723
1724 dbms_sql.define_column(l_cursor, 1, l_row_cnt);
1725
1726 l_rows_inserted:=dbms_sql.execute_and_fetch(l_cursor, true);
1727
1728 dbms_sql.column_value(l_cursor, 1, l_row_cnt);
1729
1730 dbms_sql.close_cursor(l_cursor);
1731
1732 IF l_row_cnt = 0 THEN
1733 edw_log.put_line(' ');
1734 edw_log.put_line('No rows fetched for insert into '||p_staging_table_name);
1735 RETURN;
1736 END IF;
1737
1738 l_where_clause := null; -- Bug# 3296641
1739
1740 l_rows_inserted := 0;
1741 /* Bug# 2504279, tempoary workaround end*/
1742
1743 edw_log.debug_line(' ');
1744 edw_log.debug_line('Constructing the sql statement for ' || p_staging_table_name || ' and pushing category set ' || p_category_set_name);
1745
1746 l_cursor:=dbms_sql.OPEN_CURSOR;
1747
1748 edw_log.debug_line('Constructing PKs and FKs');
1749
1750 IF (p_staging_table_name IN ( 'EDW_ITEM_CATSET1_C6_LSTG',
1751 'EDW_ITEM_CATSETI1_C6_LSTG',
1752 'EDW_ITEM_CATSETI2_C6_LSTG',
1753 'EDW_ITEM_CATSETI3_C10_LSTG')) THEN
1754 l_fk_name := ' ALL_FK';
1755 l_fk_key := ' ALL_FK_KEY';
1756 l_fk_value := ' ALL_FK';
1757 -- l_pk_value := ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'CATEGORY_PK'; /* Bug# 2558245 */
1758 l_pk_value := ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'CATEGORY_PK' ||'||' ||''''||'-'||l_instance||'''';
1759 ELSE
1760 l_fk_name := ' CATEGORY_FK';
1761 l_fk_key := ' CATEGORY_FK_KEY';
1762 -- l_fk_value := ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'CATEGORY_PK'; /* Bug# 2558245 */
1763 l_fk_value := ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'CATEGORY_PK' ||'||' ||''''||'-'||l_instance||'''';
1764 -- l_pk_value := ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'CATEGORY_PK'; /* Bug# 2558245 */
1765 l_pk_value := ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'CATEGORY_PK' ||'||' ||''''||'-'||l_instance||'''';
1766 END IF;
1767
1768
1769 -- If VBH insert then need to pull from prior level staging table rather than _LCV view
1770 IF SUBSTRB(p_view_name,1,15) = 'EDW_ITEM_CATSET' then
1771
1772 edw_log.debug_line('VBH insert - Pulling from prior level staging table');
1773
1774 -- append staging table database link to view
1775 l_view_name := p_view_name;
1776 l_where_clause := ' WHERE category_set_name = :l_category_set_name'; -- Bug# 3296641
1777 l_where_clause := l_where_clause || ' AND COLLECTION_STATUS = ''READY'''; -- Bug# 3296641
1778
1779 edw_log.debug_line('Assigning PKs and FKs based on level');
1780
1781 IF p_staging_table_name = 'EDW_ITEM_CATSETI3_C9_LSTG' THEN
1782 -- Selecting from level 10, hence take PK for FK rather than ALL_FK
1783
1784 l_fk_value := 'CATEGORY_PK';
1785 l_pk_value := 'CATEGORY_PK';
1786
1787 ELSE -- Selecting from level other than 10, hence take FK itself
1788
1789 l_fk_value := 'CATEGORY_FK';
1790 l_pk_value := 'CATEGORY_PK';
1791
1792 END IF;
1793
1794 ELSE -- not VBH so append _LCV view database link to view
1795
1796 l_view_name := p_view_name;
1797 -- Bug# 3296641
1801 END IF;
1798 l_where_clause := ' WHERE last_update_date BETWEEN :l_push_date_range1 AND :l_push_date_range2 ' ||
1799 ' AND category_set_name = :l_category_set_name';
1800
1802
1803 edw_log.debug_line('Constructing insert stmt');
1804
1805 l_stmt:= 'INSERT INTO '||p_staging_table_name||' ('||
1806 l_fk_name || ','||
1807 l_fk_key || ','||
1808 ' CATEGORY_NAME,'||
1809 ' CATEGORY_SET_NAME,'||
1810 ' CREATION_DATE,' ||
1811 ' DESCRIPTION,'||
1812 ' ERROR_CODE,' ||
1813 ' INSTANCE,' ||
1814 ' CATEGORY_DP,'||
1815 ' CATEGORY_PK,'||
1816 ' CATEGORY_ID,'||
1817 ' CATEGORY_SET_ID,'||
1818 ' LAST_UPDATE_DATE,' ||
1819 ' NAME,' ||
1820 ' REQUEST_ID,' ||
1821 ' ROW_ID,' ||
1822 ' USER_ATTRIBUTE1,' ||
1823 ' USER_ATTRIBUTE2,' ||
1824 ' USER_ATTRIBUTE3,' ||
1825 ' USER_ATTRIBUTE4,' ||
1826 ' USER_ATTRIBUTE5,' ||
1827 ' OPERATION_CODE,' ||
1828 ' COLLECTION_STATUS ) '||
1829 ' SELECT '||
1830 l_fk_value || ','||
1831 ' NULL,' ||
1832 ' NULL,' ||
1833 ' CATEGORY_SET_NAME,'||
1834 ' CREATION_DATE,' ||
1835 ' DESCRIPTION,'||
1836 ' NULL,' ||
1837 '''' || l_instance || '''' || ',' || /* Bug# 2558245 */
1838 ' CATEGORY_DP,' ||
1839 l_pk_value || ','||
1840 ' CATEGORY_ID,'||
1841 ' CATEGORY_SET_ID,'||
1842 ' LAST_UPDATE_DATE,' ||
1843 ' SUBSTRB(NAME, 1, 320),' ||
1844 ' NULL,' ||
1845 ' NULL,' ||
1846 ' USER_ATTRIBUTE1,' ||
1847 ' USER_ATTRIBUTE2,' ||
1848 ' USER_ATTRIBUTE3,' ||
1849 ' USER_ATTRIBUTE4,' ||
1850 ' USER_ATTRIBUTE5,' ||
1851 ' NULL,' ||
1852 '''READY'''||
1853 ' FROM '||l_view_name||l_where_clause; -- Bug# 3296641
1854
1855 edw_log.put_line(l_stmt);
1856
1857 l_rows_inserted := SQL%ROWCOUNT ;
1858 edw_log.debug_line('Parse the cursor');
1859 dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
1860
1861 edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');
1862 dbms_sql.bind_variable(l_cursor,':l_category_set_name',p_category_set_name);
1863
1864 -- Bug# 3296641
1865 IF SUBSTRB(p_view_name,1,15) = 'EDW_ITEM_CATSET' then
1866 null;
1867 ELSE
1868 dbms_sql.bind_variable(l_cursor,':l_push_date_range1',p_from_date);
1869 dbms_sql.bind_variable(l_cursor,':l_push_date_range2',p_to_date);
1870 END IF;
1871
1872 edw_log.debug_line('Pushing data, Executing the cursor');
1873 l_rows_inserted:=dbms_sql.execute(l_cursor);
1874
1875 edw_log.debug_line('Close the cursor');
1876 dbms_sql.close_cursor(l_cursor);
1877 edw_log.debug_line(' ');
1878
1879 edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||
1880 ' rows into the staging table');
1881 edw_log.put_line(' ');
1882
1883 END Insert_Category;
1884
1885
1886 PROCEDURE Insert_VBH_Category(
1887 p_from_date DATE,
1888 p_to_date DATE,
1889 p_view_name VARCHAR2,
1890 p_category_set_name VARCHAR2,
1891 p_no_of_catset_lvls NUMBER,
1892 p_hrchy_top_node VARCHAR2,
1893 p_control_level NUMBER) IS
1894
1895 l_stmt VARCHAR2(7000) := NULL;
1896 l_cursor NUMBER;
1897 l_rows_inserted NUMBER := 0;
1898 l_fk_name VARCHAR2(40);
1899 l_fk_value VARCHAR2(40);
1900 l_fk_key VARCHAR2(40);
1901 l_from_clause VARCHAR2(2000) := NULL;
1902 l_where_clause VARCHAR2(2000) := NULL;
1903 l_not_exists_clause VARCHAR2(1000) := NULL;
1904 l_vbh_value_set_id NUMBER;
1905 l_catset_control_level NUMBER;
1906 l_mult_item_flag VARCHAR2(1);
1907 l_segment_num VARCHAR2(30) := NULL;
1908 l_lower_lvl_exists BOOLEAN := TRUE;
1909 l_staging_table_name VARCHAR2(30);
1910 l_prior_staging_table_name VARCHAR2(30); -- as per staging table but for prior level
1911 l_prior_lvl_no NUMBER := 0;
1912 l_catset_lvl NUMBER := 0;
1913 l_catset_lvl1_flag VARCHAR2(1):= 'N';
1914 l_struct_code VARCHAR2(1);
1915 l_structure_id NUMBER;
1916
1917 INCORRECT_CTRL_LVL EXCEPTION;
1918 VBH_VALUE_SET_NOT_FOUND EXCEPTION;
1919 NOT_SEGMENT1 EXCEPTION;
1920 MULTIPLE_CAT_ALLOWED EXCEPTION;
1921
1922 -- CURSOR to get Segment NUMBER AND Value Set assigned to segment
1923
1924 CURSOR l_vbh_flex_segment_csr (c_category_set_id NUMBER) IS
1925 SELECT FLEX_VALUE_SET_ID, APPLICATION_COLUMN_NAME
1926 FROM FND_ID_FLEX_SEGMENTS
1927 WHERE APPLICATION_ID = '401'
1928 AND ID_FLEX_CODE = 'MCAT'
1929 AND ID_FLEX_NUM =
1930 (SELECT STRUCTURE_ID
1934
1931 FROM MTL_CATEGORY_SETS_VL
1932 WHERE CATEGORY_SET_ID = c_category_set_id)
1933 AND ENABLED_FLAG = 'Y';
1935 -- CURSOR to check that the VBH Category is still assigned at the Item Level
1936
1937 CURSOR l_vbh_chk_ctrl_lvl_csr (c_category_set_id VARCHAR2) is
1938 SELECT CONTROL_LEVEL, MULT_ITEM_CAT_ASSIGN_FLAG, STRUCTURE_ID
1939 FROM MTL_CATEGORY_SETS_VL
1940 WHERE CATEGORY_SET_ID = c_category_set_id;
1941
1942 BEGIN
1943 BEGIN
1944 -- Bug# 2765111 - moved the cursor opening and added structure id in select list
1945 OPEN l_vbh_chk_ctrl_lvl_csr (g_vbh_catset_id);
1946 FETCH l_vbh_chk_ctrl_lvl_csr INTO l_catset_control_level, l_mult_item_flag, l_structure_id;
1947 CLOSE l_vbh_chk_ctrl_lvl_csr;
1948
1949 -- Check to see if the structure asscoicated with the category set is PRODUCT_CATEGORIES
1950 BEGIN
1951 SELECT 'X' INTO l_struct_code
1952 FROM FND_ID_FLEX_STRUCTURES_VL
1953 WHERE ID_FLEX_NUM = l_structure_id
1954 AND ID_FLEX_STRUCTURE_CODE = 'PRODUCT_CATEGORIES'
1955 AND APPLICATION_ID = '401'
1956 AND ID_FLEX_CODE = 'MCAT';
1957
1958 EXCEPTION
1959 WHEN NO_DATA_FOUND THEN
1960 g_error_message := 'Error: The flex structure associated with this category set is not PRODUCT_CATEGORIES';
1961 RAISE;
1962 END;
1963
1964 -- Get Value Set id AND active Segment for VBH Segment
1965 edw_log.debug_line(' ');
1966 edw_log.debug_line('Get Value Set id and active Segment for VBH Segment');
1967
1968 OPEN l_vbh_flex_segment_csr (g_vbh_catset_id);
1969 LOOP
1970 FETCH l_vbh_flex_segment_csr INTO l_vbh_value_set_id, l_segment_num;
1971 EXIT WHEN l_vbh_flex_segment_csr%NOTFOUND;
1972 END LOOP;
1973
1974 -- Check that VBH Structure has only one segment enabled
1975 edw_log.debug_line(' ');
1976 edw_log.debug_line('Check that VBH Structure has only one segment enabled');
1977
1978 IF l_vbh_flex_segment_csr%ROWCOUNT > 1 THEN
1979 RAISE TOO_MANY_ROWS;
1980 END IF;
1981
1982 -- Check that VBH Structure is using Segment1
1983 edw_log.debug_line(' ');
1984 edw_log.debug_line('Check that VBH Structure is using Segment1');
1985
1986 IF l_segment_num <> 'SEGMENT1' THEN
1987 RAISE NOT_SEGMENT1;
1988 END IF;
1989
1990 CLOSE l_vbh_flex_segment_csr;
1991
1992 -- Check that Value Set has been assigned to VBH Category Set
1993 edw_log.debug_line(' ');
1994 edw_log.debug_line('Check that Value Set has been assigned to VBH Category Set');
1995
1996 IF l_vbh_value_set_id IS NULL THEN
1997 RAISE VBH_VALUE_SET_NOT_FOUND;
1998 END IF;
1999
2000 EXCEPTION
2001 WHEN VBH_VALUE_SET_NOT_FOUND THEN
2002 g_error_message := 'Value Set not assigned to structure '||p_category_set_name;
2003 RAISE;
2004 WHEN NOT_SEGMENT1 THEN
2005 g_error_message := 'Only Segment1 can be enabled for structure '||p_category_set_name;
2006 RAISE;
2007 WHEN TOO_MANY_ROWS THEN
2008 g_error_message := 'Should only enable Segment1 for structure '||p_category_set_name;
2009 RAISE;
2010 WHEN OTHERS THEN
2011 RAISE;
2012
2013 END;
2014
2015 BEGIN
2016
2017 -- Check that the Category is still assigned at the Item Level
2018 edw_log.debug_line(' ');
2019 edw_log.debug_line('Check that the Category is still assigned at the Item Level');
2020
2021 IF l_catset_control_level <> 1 THEN
2022 RAISE INCORRECT_CTRL_LVL;
2023 END IF;
2024
2025 -- Check that the Category Set does not allow multiple assignments
2026 -- of items to categories
2027 edw_log.debug_line(' ');
2028 edw_log.debug_line('Check that the Category Set does not allow multiple assignments of items to categories');
2029
2030 IF l_mult_item_flag = 'Y' THEN
2031 RAISE MULTIPLE_CAT_ALLOWED;
2032 END IF;
2033
2034 EXCEPTION
2035 WHEN INCORRECT_CTRL_LVL THEN
2036 g_error_message := 'Category is controlled at the Organization Level for '||p_category_set_name;
2037 RAISE;
2038 WHEN MULTIPLE_CAT_ALLOWED THEN
2039 g_error_message := 'Multiple assignments of items to categories is allowed for '||p_category_set_name;
2040 RAISE;
2041 WHEN OTHERS THEN
2042 RAISE;
2043 END;
2044
2045 --
2046 -- Loop to insert categories FROM Value Based Hierarchy
2047 -- into number of levels available. On each loop all children of
2048 -- the categories Inserted into the prior level table
2049 -- are Inserted into the current staging table
2050 --
2051
2052 -- Start at top level and work down the category set table hierarchy
2053 l_catset_lvl := p_no_of_catset_lvls;
2054
2055 -- Loop until there are no lower levels in the VBH hierarchy
2056 WHILE l_lower_lvl_exists LOOP
2057
2058 l_cursor:=dbms_sql.OPEN_CURSOR;
2059
2060 --
2061 -- Build staging table name
2062 --
2063 l_staging_table_name := 'EDW_ITEM_CATSETI3_C' || l_catset_lvl || '_LSTG';
2064
2065 edw_log.debug_line(' ');
2066 edw_log.debug_line('Constructing the sql statement for ' || l_staging_table_name || ' AND pushing category set ' || p_category_set_name);
2067
2068 IF l_catset_lvl = p_no_of_catset_lvls THEN /* top level */
2069
2073
2070 l_fk_name := ' ALL_FK';
2071 l_fk_key := ' ALL_FK_KEY';
2072 l_fk_value := ' ALL_FK';
2074 -- Construct the FROM clause for top level
2075
2076 /* Bug 2234621
2077
2078 l_from_clause := ' FROM '||' FND_FLEX_VALUE_CHILDREN_V FFVC'
2079 ||','||p_view_name||' LCV_VIEW'
2080 ||',MTL_CATEGORIES_VL MTC';
2081
2082 */
2083 l_from_clause := ' from ' || 'FND_FLEX_VALUE_CHILDREN_V FFVC'
2084 || ',' || p_view_name || ' LCV_VIEW';
2085
2086 -- Construct the WHERE clause for top level
2087
2088 /* Bug 2234621
2089
2090 l_where_clause := ' WHERE LCV_VIEW.LAST_UPDATE_DATE BETWEEN :l_push_date_range1'||
2091 ' AND :l_push_date_range2'||
2092 ' AND LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name'||
2093 ' AND LCV_VIEW.CATEGORY_ID = MTC.CATEGORY_ID'||
2094 ' AND MTC.'||l_segment_num||' = FFVC.FLEX_VALUE'||
2095 ' AND FFVC.FLEX_VALUE_SET_ID = :l_value_set_id'||
2096 ' AND FFVC.PARENT_FLEX_VALUE = :l_hrchy_top_node';
2097 */
2098 /* Bug# 3296641
2099 l_where_clause := ' where LCV_VIEW.LAST_UPDATE_DATE between :l_push_date_range1' ||
2100 ' and :l_push_date_range2' ||
2101 ' and LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
2102 ' and LCV_VIEW.NAME = FFVC.FLEX_VALUE' ||
2103 ' and FFVC.FLEX_VALUE_SET_ID = :l_value_set_id' ||
2104 ' and FFVC.PARENT_FLEX_VALUE = :l_hrchy_top_node';
2105 */
2106
2107 l_where_clause := ' where LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
2108 ' and LCV_VIEW.NAME = FFVC.FLEX_VALUE' ||
2109 ' and FFVC.FLEX_VALUE_SET_ID = :l_value_set_id' ||
2110 ' and FFVC.PARENT_FLEX_VALUE = :l_hrchy_top_node';
2111
2112 ELSE /* Not top level */
2113
2114 --
2115 -- Build prior level staging table name
2116 --
2117 IF l_catset_lvl1_flag = 'Y' /* vbh greater than catset lvls, */
2118 THEN /* hence SELECT FROM AND INSERT INTO lowest lvl table */
2119 l_prior_staging_table_name := l_staging_table_name;
2120 ELSE
2121 l_prior_lvl_no := l_catset_lvl + 1;
2122 l_prior_staging_table_name := 'EDW_ITEM_CATSETI3_C' ||l_prior_lvl_no|| '_LSTG';
2123 END IF;
2124
2125 l_fk_name := ' CATEGORY_FK';
2126 l_fk_key := ' CATEGORY_FK_KEY';
2127
2128 IF l_catset_lvl1_flag = 'Y' /* vbh greater than catset lvls */
2129 THEN /* need fk on parent record rather than actual parent pk */
2130 l_fk_value := 'PARENT_STAGING.CATEGORY_FK';
2131 ELSE
2132 l_fk_value := 'PARENT_STAGING.CATEGORY_PK';
2133 END IF;
2134
2135 -- Construct the FROM clause for levels other than the top
2136 /* Bug 2234621
2137
2138 l_from_clause := ' FROM '||l_prior_staging_table_name||' PARENT_STAGING'
2139 ||','||p_view_name||' PARENT_LCV_VIEW'
2140 ||',MTL_CATEGORIES_VL PARENT_MTC'
2141 ||',FND_FLEX_VALUE_CHILDREN_V FFVC'
2142 ||','||p_view_name||' LCV_VIEW'
2143 ||',MTL_CATEGORIES_VL MTC';
2144 */
2145
2146 l_from_clause := ' from ' || l_prior_staging_table_name || ' PARENT_STAGING, '
2147 || ' FND_FLEX_VALUE_CHILDREN_V FFVC'
2148 || ',' || p_view_name || ' LCV_VIEW';
2149
2150 -- Construct the WHERE clause levels other than the top
2151
2152 /* Bug 2234621
2153
2154 l_where_clause := ' WHERE LCV_VIEW.LAST_UPDATE_DATE BETWEEN :l_push_date_range1'||
2155 ' AND :l_push_date_range2'||
2156 ' AND LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name'||
2157 ' AND LCV_VIEW.CATEGORY_ID = MTC.CATEGORY_ID'||
2158 ' AND MTC.'||l_segment_num||' = FFVC.FLEX_VALUE'||
2159 ' AND FFVC.FLEX_VALUE_SET_ID = :l_value_set_id'||
2160 ' AND FFVC.PARENT_FLEX_VALUE = PARENT_MTC.'||l_segment_num||
2161 ' AND PARENT_MTC.CATEGORY_ID = PARENT_LCV_VIEW.CATEGORY_ID'||
2162 ' AND PARENT_LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name'||
2163 ' AND PARENT_LCV_VIEW.CATEGORY_PK || '||''''||'-'||l_instance||'''' ||
2164 '= SUBSTRB(PARENT_STAGING.CATEGORY_PK,3)'||
2165 ' AND PARENT_STAGING.COLLECTION_STATUS = ''READY''';
2166 */
2167 /* Bug# 3296641
2168 l_where_clause := ' where LCV_VIEW.LAST_UPDATE_DATE between :l_push_date_range1' ||
2169 ' and :l_push_date_range2' ||
2170 ' and LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
2171 ' and LCV_VIEW.NAME = FFVC.FLEX_VALUE' ||
2172 ' and FFVC.FLEX_VALUE_SET_ID = :l_value_set_id' ||
2173 ' and FFVC.PARENT_FLEX_VALUE = PARENT_STAGING.NAME' ||
2174 ' and PARENT_STAGING.COLLECTION_STATUS = ''READY''';
2175 */
2176
2177 l_where_clause := ' where LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
2178 ' and LCV_VIEW.NAME = FFVC.FLEX_VALUE' ||
2182
2179 ' and FFVC.FLEX_VALUE_SET_ID = :l_value_set_id' ||
2180 ' and FFVC.PARENT_FLEX_VALUE = PARENT_STAGING.NAME' ||
2181 ' and PARENT_STAGING.COLLECTION_STATUS = ''READY''';
2183
2184 IF l_catset_lvl1_flag = 'Y' /* vbh greater than catset lvls */
2185 THEN /* don't SELECT children that already exist in staging table */
2186 l_not_exists_clause := ' AND not exists '||
2187 ' (SELECT NULL '||
2188 ' FROM '||l_staging_table_name||' STAGING'||
2189 ' WHERE SUBSTRB(STAGING.CATEGORY_PK,3) = LCV_VIEW.CATEGORY_PK ||'||''''||'-'||l_instance||'''' || /* Bug# 2558245 */
2190 ' AND STAGING.COLLECTION_STATUS = ''READY'')';
2191
2192 l_where_clause := l_where_clause||l_not_exists_clause;
2193
2194 END IF; /* vbh greater than catset levels */
2195
2196 END IF; /* top level */
2197
2198 -- Construct INSERT INTO staging table
2199
2200 l_stmt:= 'INSERT INTO '||l_staging_table_name||' ('||
2201 l_fk_name || ','||
2202 l_fk_key || ','||
2203 ' CATEGORY_NAME,'||
2204 ' CATEGORY_SET_NAME,'||
2205 ' CREATION_DATE,' ||
2206 ' DESCRIPTION,'||
2207 ' ERROR_CODE,' ||
2208 ' INSTANCE,' ||
2209 ' CATEGORY_DP,'||
2210 ' CATEGORY_PK,'||
2211 ' CATEGORY_ID,'||
2212 ' CATEGORY_SET_ID,'||
2213 ' LAST_UPDATE_DATE,' ||
2214 ' NAME,' ||
2215 ' REQUEST_ID,' ||
2216 ' ROW_ID,' ||
2217 ' USER_ATTRIBUTE1,' ||
2218 ' USER_ATTRIBUTE2,' ||
2219 ' USER_ATTRIBUTE3,' ||
2220 ' USER_ATTRIBUTE4,' ||
2221 ' USER_ATTRIBUTE5,' ||
2222 ' OPERATION_CODE,' ||
2223 ' COLLECTION_STATUS ) '||
2224 ' SELECT '||
2225 l_fk_value || ','||
2226 ' NULL,' ||
2227 ' NULL,' ||
2228 ' LCV_VIEW.CATEGORY_SET_NAME,'||
2229 ' LCV_VIEW.CREATION_DATE,' ||
2230 ' FFVC.DESCRIPTION,'||
2231 ' NULL,' ||
2232 '''' || l_instance || '''' || ',' || /* Bug# 2558245 */
2233 ' LCV_VIEW.CATEGORY_DP,' ||
2234 ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'LCV_VIEW.CATEGORY_PK' ||'||' ||''''||'-'||l_instance||''',' || /* Bug# 2558245 */
2235 ' LCV_VIEW.CATEGORY_ID,'||
2236 ' LCV_VIEW.CATEGORY_SET_ID,'||
2237 ' LCV_VIEW.LAST_UPDATE_DATE,' ||
2238 ' SUBSTRB(LCV_VIEW.NAME, 1, 320),' ||
2239 ' NULL,' ||
2240 ' NULL,' ||
2241 ' LCV_VIEW.USER_ATTRIBUTE1,' ||
2242 ' LCV_VIEW.USER_ATTRIBUTE2,' ||
2243 ' LCV_VIEW.USER_ATTRIBUTE3,' ||
2244 ' LCV_VIEW.USER_ATTRIBUTE4,' ||
2245 ' LCV_VIEW.USER_ATTRIBUTE5,' ||
2246 ' NULL,' ||
2247 '''READY'''||
2248 l_from_clause||
2249 l_where_clause;
2250
2251 edw_log.put_line(l_stmt);
2252 -- edw_log.put_line(l_from_clause);
2253 -- edw_log.put_line(l_where_clause);
2254 l_rows_inserted := SQL%ROWCOUNT ;
2255 edw_log.debug_line('Parse the cursor');
2256 dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
2257
2258 edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');
2259 dbms_sql.bind_variable(l_cursor,':l_category_set_name',p_category_set_name);
2260 -- Bug# 3296641 dbms_sql.bind_variable(l_cursor,':l_push_date_range1',p_from_date);
2261 -- Bug# 3296641 dbms_sql.bind_variable(l_cursor,':l_push_date_range2',p_to_date);
2262 dbms_sql.bind_variable(l_cursor,':l_value_set_id',l_vbh_value_set_id);
2263
2264 IF l_catset_lvl = p_no_of_catset_lvls THEN
2265 dbms_sql.bind_variable(l_cursor,':l_hrchy_top_node',p_hrchy_top_node);
2266 END IF;
2267
2268 edw_log.debug_line('Pushing data, Executing the cursor');
2269 l_rows_inserted:=dbms_sql.execute(l_cursor);
2270
2271 edw_log.debug_line('Close the cursor');
2272 dbms_sql.close_CURSOR(l_cursor);
2273 edw_log.debug_line(' ');
2274
2275 edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||
2276 ' rows into the staging table');
2277 edw_log.put_line(' ');
2278
2279 -- if there were no children found then end loop
2280 IF l_rows_inserted = 0 THEN
2281 l_lower_lvl_exists := FALSE;
2282 ELSE
2283
2284 -- if already at level 1 then do not decrement counter, continue
2285 -- to INSERT hierarchy INTO level 1
2286 IF l_catset_lvl <> 1 THEN
2287 l_catset_lvl := l_catset_lvl - 1;
2288 ELSE /* level 1 has already been processed, hence we know that vbh levels > catset levels */
2289 l_catset_lvl1_flag := 'Y';
2290 END IF; /* check if already at level 1 */
2291
2292 END IF; /* check if children exist in hierarchy */
2293
2294 END LOOP;
2295 END Insert_VBH_Category;
2296
2297 -- New procedure for supporting Product Hierarchy re-architechture (11.5.9)
2298 PROCEDURE INSERT_CATEGORY_HIERARCHY(
2299 p_from_date DATE,
2300 p_to_date DATE,
2304 p_no_of_catset_lvls NUMBER,
2301 p_view_name VARCHAR2,
2302 p_category_set_name VARCHAR2,
2303 p_category_set_id NUMBER,
2305 p_control_level NUMBER) IS
2306
2307 l_stmt VARCHAR2(7000) := NULL;
2308 l_cursor NUMBER;
2309 l_rows_inserted NUMBER := 0;
2310 l_fk_name VARCHAR2(40);
2311 l_fk_value VARCHAR2(40);
2312 l_fk_key VARCHAR2(40);
2313 l_from_clause VARCHAR2(2000) := NULL;
2314 l_where_clause VARCHAR2(2000) := NULL;
2315 l_not_exists_clause VARCHAR2(1000) := NULL;
2316 l_catset_control_level NUMBER;
2317 l_mult_item_flag VARCHAR2(1);
2318 l_lower_lvl_exists BOOLEAN := TRUE;
2319 l_staging_table_name VARCHAR2(30);
2320 l_prior_staging_table_name VARCHAR2(30); -- as per staging table but for prior level
2321 l_prior_lvl_no NUMBER := 0;
2322 l_catset_lvl NUMBER := 0;
2323 l_catset_lvl1_flag VARCHAR2(1):= 'N';
2324 l_validate_flag VARCHAR2(1);
2325
2326 INCORRECT_CTRL_LVL EXCEPTION;
2327 MULTIPLE_CAT_ALLOWED EXCEPTION;
2328
2329 CURSOR l_chk_ctrl_lvl_csr (c_category_set_id VARCHAR2) is
2330 SELECT CONTROL_LEVEL, MULT_ITEM_CAT_ASSIGN_FLAG, VALIDATE_FLAG
2331 FROM MTL_CATEGORY_SETS_VL
2332 WHERE CATEGORY_SET_ID = c_category_set_id;
2333
2334 BEGIN
2335
2336 OPEN l_chk_ctrl_lvl_csr (p_category_set_id);
2337 FETCH l_chk_ctrl_lvl_csr INTO l_catset_control_level, l_mult_item_flag, l_validate_flag;
2338 CLOSE l_chk_ctrl_lvl_csr;
2339
2340 BEGIN
2341 -- Check that the Category Set is still assigned at the Item Level
2342 edw_log.debug_line(' ');
2343 edw_log.debug_line('Check that the Category Set is still assigned at the Item Level');
2344
2345 IF l_catset_control_level <> 1 THEN
2346 RAISE INCORRECT_CTRL_LVL;
2347 END IF;
2348
2349 -- Check that the Category Set does not allow multiple assignments
2350 -- of items to categories
2351 edw_log.debug_line(' ');
2352 edw_log.debug_line('Check that the Category Set does not allow multiple assignments of items to categories');
2353
2354 IF l_mult_item_flag = 'Y' THEN
2355 RAISE MULTIPLE_CAT_ALLOWED;
2356 END IF;
2357
2358 IF NVL(l_validate_flag, 'N') = 'Y' THEN
2359 edw_log.put_line('WARNING! : Enforce List of Valid categories is FALSE for Hierarchy Enabled Category set - '||p_category_set_name);
2360 END IF;
2361 EXCEPTION
2362 WHEN INCORRECT_CTRL_LVL THEN
2363 g_error_message := 'Category Set is controlled at the Organization Level for '||p_category_set_name;
2364 RAISE;
2365 WHEN MULTIPLE_CAT_ALLOWED THEN
2366 g_error_message := 'Multiple assignments of items to categories is allowed for '||p_category_set_name;
2367 RAISE;
2368 WHEN OTHERS THEN
2369 RAISE;
2370 END;
2371
2372 --
2373 -- Loop to insert categories FROM Value Based Hierarchy
2374 -- into number of levels available. On each loop all children of
2375 -- the categories Inserted into the prior level table
2376 -- are Inserted into the current staging table
2377 --
2378
2379 -- Start at top level and work down the category set table hierarchy
2380 l_catset_lvl := p_no_of_catset_lvls;
2381
2382 -- Loop until there are no lower levels in the VBH hierarchy
2383 WHILE l_lower_lvl_exists LOOP
2384 l_cursor:=dbms_sql.OPEN_CURSOR;
2385 --
2386 -- Build staging table name
2387 --
2388 l_staging_table_name := 'EDW_ITEM_CATSETI3_C' || l_catset_lvl || '_LSTG';
2389
2390 edw_log.debug_line(' ');
2391 edw_log.debug_line('In Loading Category Set Hierarchy ');
2392 edw_log.debug_line('Constructing the sql statement for ' || l_staging_table_name || ' AND pushing category set ' || p_category_set_name);
2393
2394 IF l_catset_lvl = p_no_of_catset_lvls THEN /* top level */
2395 l_fk_name := ' ALL_FK';
2396 l_fk_key := ' ALL_FK_KEY';
2397 l_fk_value := ' ALL_FK';
2398
2399 -- Construct the FROM clause for top level
2400
2401 l_from_clause := ' from ' || 'MTL_CATEGORY_SET_VALID_CATS CATH'
2402 || ',' || p_view_name || ' LCV_VIEW';
2403
2404 -- Construct the WHERE clause for top level
2405
2406 l_where_clause := ' where LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
2407 ' and LCV_VIEW.CATEGORY_ID = CATH.CATEGORY_ID' ||
2408 ' and CATH.CATEGORY_SET_ID = :l_cat_set_id' ||
2409 ' and CATH.PARENT_CATEGORY_ID IS NULL';
2410
2411 ELSE /* Not top level */
2412 --
2413 -- Build prior level staging table name
2414 --
2415 IF l_catset_lvl1_flag = 'Y' /* hierarchy greater than catset lvls, */
2416 THEN /* hence SELECT FROM AND INSERT INTO lowest lvl table */
2417 l_prior_staging_table_name := l_staging_table_name;
2418 ELSE
2419 l_prior_lvl_no := l_catset_lvl + 1;
2420 l_prior_staging_table_name := 'EDW_ITEM_CATSETI3_C' ||l_prior_lvl_no|| '_LSTG';
2421 END IF;
2422
2423 l_fk_name := ' CATEGORY_FK';
2427 THEN /* need fk on parent record rather than actual parent pk */
2424 l_fk_key := ' CATEGORY_FK_KEY';
2425
2426 IF l_catset_lvl1_flag = 'Y' /* hierarchy greater than catset lvls */
2428 l_fk_value := 'PARENT_STAGING.CATEGORY_FK';
2429 ELSE
2430 l_fk_value := 'PARENT_STAGING.CATEGORY_PK';
2431 END IF;
2432
2433 -- Construct the FROM clause for levels other than the top
2434
2435 l_from_clause := ' from ' || l_prior_staging_table_name || ' PARENT_STAGING, '
2436 || ' MTL_CATEGORY_SET_VALID_CATS CATH'
2437 || ',' || p_view_name || ' LCV_VIEW';
2438
2439 -- Construct the WHERE clause levels other than the top
2440
2441 l_where_clause := ' where LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
2442 ' and LCV_VIEW.CATEGORY_ID = CATH.CATEGORY_ID' ||
2443 ' and CATH.CATEGORY_SET_ID = :l_cat_set_id' ||
2444 ' and CATH.PARENT_CATEGORY_ID = PARENT_STAGING.CATEGORY_ID' ||
2445 ' and PARENT_STAGING.COLLECTION_STATUS = ''READY''';
2446
2447
2448 IF l_catset_lvl1_flag = 'Y' /* hierarchy greater than catset lvls */
2449 THEN /* don't SELECT children that already exist in staging table */
2450 l_not_exists_clause := ' AND not exists '||
2451 ' (SELECT NULL '||
2452 ' FROM '||l_staging_table_name||' STAGING'||
2453 ' WHERE SUBSTRB(STAGING.CATEGORY_PK,3) = LCV_VIEW.CATEGORY_PK ||'||''''||'-'||l_instance||'''' || /* Bug# 2558245 */
2454 ' AND STAGING.COLLECTION_STATUS = ''READY'')';
2455
2456 l_where_clause := l_where_clause||l_not_exists_clause;
2457
2458 END IF; /* vbh greater than catset levels */
2459 END IF; /* top level */
2460
2461 -- Construct INSERT INTO staging table
2462
2463 l_stmt:= 'INSERT INTO '||l_staging_table_name||' ('||
2464 l_fk_name || ','||
2465 l_fk_key || ','||
2466 ' CATEGORY_NAME,'||
2467 ' CATEGORY_SET_NAME,'||
2468 ' CREATION_DATE,' ||
2469 ' DESCRIPTION,'||
2470 ' ERROR_CODE,' ||
2471 ' INSTANCE,' ||
2472 ' CATEGORY_DP,'||
2473 ' CATEGORY_PK,'||
2474 ' CATEGORY_ID,'||
2475 ' CATEGORY_SET_ID,'||
2476 ' LAST_UPDATE_DATE,' ||
2477 ' NAME,' ||
2478 ' REQUEST_ID,' ||
2479 ' ROW_ID,' ||
2480 ' USER_ATTRIBUTE1,' ||
2481 ' USER_ATTRIBUTE2,' ||
2482 ' USER_ATTRIBUTE3,' ||
2483 ' USER_ATTRIBUTE4,' ||
2484 ' USER_ATTRIBUTE5,' ||
2485 ' OPERATION_CODE,' ||
2486 ' COLLECTION_STATUS ) '||
2487 ' SELECT '||
2488 l_fk_value || ','||
2489 ' NULL,' ||
2490 ' NULL,' ||
2491 ' LCV_VIEW.CATEGORY_SET_NAME,'||
2492 ' LCV_VIEW.CREATION_DATE,' ||
2493 ' LCV_VIEW.DESCRIPTION,'||
2494 ' NULL,' ||
2495 '''' || l_instance || '''' || ',' || /* Bug# 2558245 */
2496 ' LCV_VIEW.CATEGORY_DP,' ||
2497 ''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'LCV_VIEW.CATEGORY_PK' ||'||' ||''''||'-'||l_instance||''',' || /* Bug# 2558245 */
2498 ' LCV_VIEW.CATEGORY_ID,'||
2499 ' LCV_VIEW.CATEGORY_SET_ID,'||
2500 ' LCV_VIEW.LAST_UPDATE_DATE,' ||
2501 ' SUBSTRB(LCV_VIEW.NAME, 1, 320),' ||
2502 ' NULL,' ||
2503 ' NULL,' ||
2504 ' LCV_VIEW.USER_ATTRIBUTE1,' ||
2505 ' LCV_VIEW.USER_ATTRIBUTE2,' ||
2506 ' LCV_VIEW.USER_ATTRIBUTE3,' ||
2507 ' LCV_VIEW.USER_ATTRIBUTE4,' ||
2508 ' LCV_VIEW.USER_ATTRIBUTE5,' ||
2509 ' NULL,' ||
2510 '''READY'''||
2511 l_from_clause||
2512 l_where_clause;
2513
2514 edw_log.put_line(l_stmt);
2515 -- edw_log.put_line(l_from_clause);
2516 -- edw_log.put_line(l_where_clause);
2517 l_rows_inserted := SQL%ROWCOUNT ;
2518 edw_log.debug_line('Parse the cursor');
2519 dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
2520
2521 edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');
2522 dbms_sql.bind_variable(l_cursor,':l_category_set_name',p_category_set_name);
2523 dbms_sql.bind_variable(l_cursor,':l_cat_set_id',p_category_set_id);
2524
2525 /* IF l_catset_lvl = p_no_of_catset_lvls THEN
2526 dbms_sql.bind_variable(l_cursor,':l_hrchy_top_node',p_hrchy_top_node);
2527 END IF;
2528 */
2529 edw_log.debug_line('Pushing data, Executing the cursor');
2530 l_rows_inserted:=dbms_sql.execute(l_cursor);
2531
2532 edw_log.debug_line('Close the cursor');
2533 dbms_sql.close_CURSOR(l_cursor);
2534 edw_log.debug_line(' ');
2535
2536 edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||
2537 ' rows into the staging table');
2538 edw_log.put_line(' ');
2539
2540 -- if there were no children found then end loop
2541 IF l_rows_inserted = 0 THEN
2542 l_lower_lvl_exists := FALSE;
2543 ELSE
2547 l_catset_lvl := l_catset_lvl - 1;
2544 -- if already at level 1 then do not decrement counter, continue
2545 -- to INSERT hierarchy INTO level 1
2546 IF l_catset_lvl <> 1 THEN
2548 ELSE /* level 1 has already been processed, hence we know that vbh levels > catset levels */
2549 l_catset_lvl1_flag := 'Y';
2550 END IF; /* check if already at level 1 */
2551 END IF; /* check if children exist in hierarchy */
2552
2553 END LOOP;
2554 END INSERT_CATEGORY_HIERARCHY;
2555
2556
2557 PROCEDURE Push_Category(
2558 p_from_date DATE ,
2559 p_to_date DATE ,
2560 p_item_item_org NUMBER) IS
2561
2562 --p_item_item_org=0 is for items and p_item_item_org=1 is for item/org
2563
2564 l_staging_table_name VARCHAR2(30); -- have to concat the rest depending
2565 -- on the staging table
2566 l_view_name VARCHAR2(40);
2567 l_pk_name VARCHAR2(40);
2568 l_dp VARCHAR2(40);
2569 l_functional_area NUMBER;
2570 l_control_level NUMBER;
2571 l_push_date_range1 DATE := NULL;
2572 l_push_date_range2 DATE := NULL;
2573 l_rows_inserted NUMBER := 0;
2574 l_stmt VARCHAR2(5000) := NULL;
2575 l_cursor NUMBER;
2576 l_item_revision_pk VARCHAR2(320) := 'NA_EDW';
2577 i2 NUMBER := 0;
2578 l_catset1_category_fk VARCHAR2(40) := NULL;
2579 l_catset2_category_fk VARCHAR2(40) := NULL;
2580 l_catset3_category_fk VARCHAR2(40) := NULL;
2581 l_catset_category_fk VARCHAR2(40) := NULL;
2582 l_item_catset_name VARCHAR2(40) := NULL;
2583 l_level_name VARCHAR2(5) := NULL;
2584 l_all_items VARCHAR2(100);
2585 l_all_item_orgs VARCHAR2(100);
2586 l_all_item_revs VARCHAR2(100);
2587 l_catset3_id NUMBER;
2588 l_hrchy_enabled VARCHAR2(1);
2589
2590 -- Local VBH variables
2591 l_prior_staging_table_name VARCHAR2(30); -- as per staging table but for prior level
2592 l_prior_level_no NUMBER := 0;
2593 l_hierarchy_stmt VARCHAR2(2000);
2594 l_inv_schema VARCHAR2(100) :='INV';
2595 --
2596 -- CURSOR to push categories down to lower levels
2597 -- Accepts user-assigned category set name for each category hierarchy push-down
2598 --
2599 CURSOR l_itemrev_csr(c_category_set_name VARCHAR2) is
2600 SELECT
2601 CATEGORY_PK || '-' || l_instance CATEGORY_PK, /* Bug# 2558245 */
2602 CREATION_DATE,
2603 l_instance INSTANCE, -- INSTANCE /* Bug# 2558245 */
2604 'NA_EDW' ITEM_ORG_FK,
2605 NULL ITEM_ORG_FK_KEY,
2606 NULL ITEM_REVISION,
2607 CATEGORY_ID,
2608 CATEGORY_SET_ID,
2609 LAST_UPDATE_DATE,
2610 SUBSTRB('(' || NAME || ')', 1, 320) NAME,
2611 'READY' COLLECTION_STATUS
2612 FROM edw_item_item_org_cat_lcv
2613 WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2 AND category_set_name = c_category_set_name;
2614
2615 -- 3296641
2616 -- CURSOR to push VBH categories down to lower levels
2617 --
2618 CURSOR l_itemrev_vbh_csr(c_category_set_name VARCHAR2) is
2619 SELECT
2620 CATEGORY_PK || '-' || l_instance CATEGORY_PK, /* Bug# 2558245 */
2621 CREATION_DATE,
2622 l_instance INSTANCE, -- INSTANCE /* Bug# 2558245 */
2623 'NA_EDW' ITEM_ORG_FK,
2624 NULL ITEM_ORG_FK_KEY,
2625 NULL ITEM_REVISION,
2626 CATEGORY_ID,
2627 CATEGORY_SET_ID,
2628 LAST_UPDATE_DATE,
2629 SUBSTRB('(' || NAME || ')', 1, 320) NAME,
2630 'READY' COLLECTION_STATUS
2631 FROM edw_item_item_org_cat_lcv
2632 WHERE category_set_name = c_category_set_name;
2633
2634 BEGIN
2635
2636 -- Getting lookup values for Push Down rows
2637
2638 l_all_items := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_ITEM');
2639 l_all_item_orgs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IORG');
2640 l_all_item_revs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IREV');
2641
2642 l_push_date_range1:=p_from_date;
2643 l_push_date_range2:=p_to_date;
2644
2645 -- dynamic sql necessary for all the 16 staging tables
2646 edw_log.put_line('Determining the collection view to SELECT FROM');
2647
2648 IF p_item_item_org=0 THEN
2649 l_view_name:='edw_item_item_cat_lcv';
2650 ELSE
2651 l_view_name:='edw_item_item_org_cat_lcv';
2652 END IF;
2653
2654 edw_log.put_line('Determining the control level');
2655
2656 IF p_item_item_org = 0 THEN
2657 l_control_level := 2;
2658 ELSE
2659 l_control_level := 1;
2660 END IF;
2661
2662 l_cursor:=dbms_sql.OPEN_CURSOR;
2663
2664 edw_log.put_line('Pushing Categories');
2665
2666 IF p_item_item_org=1 THEN
2667 --
2668 -- Pushing Item Org Categories
2669 --
2670
2671 IF (l_itemorg_catset1_name <> 'NA_EDW') THEN
2672
2673 --
2674 -- Loop to INSERT categories INTO 6 level tables
2675 --
2679 -- Build staging table name
2676 FOR i2 IN 1..6 LOOP
2677
2678 --
2680 --
2681 l_staging_table_name := 'EDW_ITEM_CATSET1_C' || i2 || '_LSTG';
2682
2683 --
2684 -- Calling function to Push Categories
2685 --
2686 Insert_Category(
2687 p_from_date => l_push_date_range1,
2688 p_to_date => l_push_date_range2,
2689 p_staging_table_name => l_staging_table_name,
2690 p_view_name => l_view_name,
2691 p_category_set_name => l_itemorg_catset1_name,
2692 p_control_level => l_control_level);
2693 END LOOP;
2694 END IF;
2695 ELSE
2696 --
2697 -- Pushing Item Categories
2698 --
2699 IF (l_item_catset1_name <> 'NA_EDW') THEN
2700
2701 --
2702 -- Loop to INSERT categories INTO 6 level tables
2703 --
2704 FOR i2 IN 1..6 LOOP
2705
2706 --
2707 -- Build staging table name
2708 --
2709 l_staging_table_name := 'EDW_ITEM_CATSETI1_C' || i2 || '_LSTG';
2710
2711 --
2712 -- Calling function to Push Categories
2713 --
2714 Insert_Category(
2715 p_from_date => l_push_date_range1,
2716 p_to_date => l_push_date_range2,
2717 p_staging_table_name => l_staging_table_name,
2718 p_view_name => l_view_name,
2719 p_category_set_name => l_item_catset1_name,
2720 p_control_level => l_control_level);
2721
2722 END LOOP;
2723 END IF;
2724
2725 IF (l_item_catset2_name <> 'NA_EDW') THEN
2726
2727 --
2728 -- Loop to INSERT categories INTO 6 level tables
2729 --
2730 FOR i2 IN 1..6 LOOP
2731
2732 --
2733 -- Build staging table name
2734 --
2735 l_staging_table_name := 'EDW_ITEM_CATSETI2_C' || i2 || '_LSTG';
2736
2737 --
2738 -- Calling function to Push Categories
2739 --
2740 Insert_Category(
2741 p_from_date => l_push_date_range1,
2742 p_to_date => l_push_date_range2,
2743 p_staging_table_name => l_staging_table_name,
2744 p_view_name => l_view_name,
2745 p_category_set_name => l_item_catset2_name,
2746 p_control_level => l_control_level);
2747
2748 END LOOP;
2749 END IF;
2750
2751 -- For Category Set Hierarchy 3, check whether the collection type
2752 -- is FROM a category set or value based hierarchy
2753
2754 IF (l_itm_hrchy3_coll_type = 'V') THEN
2755
2756 --
2757 -- Processing for Value Based Hierarchy Collection
2758 --
2759
2760 --
2761 -- Calling function to Push Child Categories
2762 --
2763 Insert_VBH_Category(
2764 p_from_date => l_push_date_range1,
2765 p_to_date => l_push_date_range2,
2766 p_view_name => 'EDW_ITEM_VBH_CAT_LCV',
2767 p_category_set_name => l_item_catset3_name,
2768 p_no_of_catset_lvls => 10,
2769 p_hrchy_top_node => l_itm_hrchy3_vbh_top_node,
2770 p_control_level => l_control_level);
2771
2772 --
2773 -- Loop to INSERT VBH categories FROM prior level tables
2774 --
2775 FOR i2 IN reverse 1..9 LOOP
2776
2777 --
2778 -- Build staging table names
2779 --
2780 l_staging_table_name := 'EDW_ITEM_CATSETI3_C' || i2 || '_LSTG';
2781
2782 --
2783 -- Build parent staging table name
2784 --
2785 l_prior_level_no := i2+1;
2786 l_prior_staging_table_name := 'EDW_ITEM_CATSETI3_C' ||l_prior_level_no|| '_LSTG';
2787
2788 --
2789 -- Calling function to Push Categories
2790 --
2791 Insert_Category(
2792 p_from_date => l_push_date_range1,
2793 p_to_date => l_push_date_range2,
2794 p_staging_table_name => l_staging_table_name,
2795 p_view_name => l_prior_staging_table_name,
2796 p_category_set_name => l_item_catset3_name,
2797 p_control_level => l_control_level);
2798
2799 END LOOP;
2800
2801 -- If any category exists in the category assignment but is
2802 -- not part of the hierarchy need to INSERT this category
2803 -- with an FK of NA_EDW
2804
2805 BEGIN
2806
2807 INSERT INTO EDW_ITEM_CATSETI3_C1_LSTG
2808 (CATEGORY_FK,
2809 CATEGORY_FK_KEY,
2810 CATEGORY_NAME,
2811 CATEGORY_SET_NAME,
2812 CREATION_DATE,
2813 DESCRIPTION,
2814 ERROR_CODE,
2815 INSTANCE,
2816 CATEGORY_DP,
2817 CATEGORY_PK,
2818 CATEGORY_ID,
2819 CATEGORY_SET_ID,
2820 LAST_UPDATE_DATE,
2821 NAME,
2822 REQUEST_ID,
2823 ROW_ID,
2824 USER_ATTRIBUTE1,
2825 USER_ATTRIBUTE2,
2829 OPERATION_CODE,
2826 USER_ATTRIBUTE3,
2827 USER_ATTRIBUTE4,
2828 USER_ATTRIBUTE5,
2830 COLLECTION_STATUS )
2831 SELECT
2832 'NA_EDW',
2833 NULL,
2834 NULL,
2835 CATEGORY_SET_NAME,
2836 CREATION_DATE,
2837 DESCRIPTION,
2838 NULL,
2839 l_instance, /* Bug# 2558245 */
2840 CATEGORY_DP,
2841 TO_CHAR(l_control_level) || '-' || CATEGORY_PK || '-' || l_instance, /* Bug# 2558245 */
2842 CATEGORY_ID,
2843 CATEGORY_SET_ID,
2844 LAST_UPDATE_DATE,
2845 SUBSTRB(NAME, 1, 320),
2846 NULL,
2847 NULL,
2848 USER_ATTRIBUTE1,
2849 USER_ATTRIBUTE2,
2850 USER_ATTRIBUTE3,
2851 USER_ATTRIBUTE4,
2852 USER_ATTRIBUTE5,
2853 NULL,
2854 'READY'
2855 FROM EDW_ITEM_ITEM_CAT_LCV LCV
2856 WHERE LCV.LAST_UPDATE_DATE BETWEEN l_push_date_range1 AND l_push_date_range2
2857 AND LCV.CATEGORY_SET_NAME = l_item_catset3_name
2858 AND NOT EXISTS
2859 (SELECT NULL
2860 FROM EDW_ITEM_CATSETI3_C1_LSTG LSTG
2861 WHERE SUBSTRB(LSTG.CATEGORY_PK, 3) = LCV.CATEGORY_PK || '-' || l_instance /* Bug 2558245 */
2862 AND LSTG.CATEGORY_SET_NAME = l_item_catset3_name
2863 AND LSTG.COLLECTION_STATUS = 'READY')
2864 AND EXISTS /* Bug# 2559696 Removed the use of EDW_ITEMS_CATEGORY_FKV by dsakalle */
2865 (SELECT NULL
2866 FROM
2867 MTL_ITEM_CATEGORIES cat,
2868 MTL_CATEGORY_SETS_TL tl
2869 WHERE tl.CATEGORY_SET_NAME = l_item_catset3_name
2870 AND cat.CATEGORY_ID = LCV.CATEGORY_ID
2871 AND cat.CATEGORY_SET_ID = tl.CATEGORY_SET_ID);
2872
2873 EXCEPTION
2874 WHEN OTHERS THEN RAISE;
2875 END;
2876
2877 ELSIF (l_itm_hrchy3_coll_type = 'C') THEN
2878 -- Supporting Product Hierarchy Re-Architecture in 11.5.9
2879
2880 BEGIN -- Bug 3514304
2881
2882 SELECT 1 into l_column_exists
2883 FROM all_tab_columns
2884 WHERE table_name = 'MTL_CATEGORY_SETS_B'
2885 AND column_name = 'HIERARCHY_ENABLED'
2886 AND owner = l_inv_schema;
2887
2888 edw_log.put_line(' Hierarchy enabled column exists');
2889
2890 BEGIN
2891
2892
2893 -- edw_log.put_line('in hrchy col type = C' );
2894
2895 -- Bug 3424451
2896 -- Changing the static sql to dynamic sql to make the package
2897 -- backward compatible.
2898 l_rows_inserted := 0;
2899 l_cursor := dbms_sql.open_cursor;
2900
2901 l_hierarchy_stmt := 'SELECT HIERARCHY_ENABLED, CATEGORY_SET_ID'||
2902 ' FROM MTL_CATEGORY_SETS ' ||
2903 ' WHERE CATEGORY_SET_NAME = :l_catset3_name';
2904
2905 edw_log.put_line('Constructing the SQL statement: ' || l_hierarchy_stmt);
2906
2907 dbms_sql.parse(l_cursor,l_hierarchy_stmt,dbms_sql.native);
2908 dbms_sql.bind_variable(l_cursor,':l_catset3_name', l_item_catset3_name);
2909 dbms_sql.define_column(l_cursor, 1, l_hrchy_enabled, 1);
2910 dbms_sql.define_column(l_cursor, 2, l_catset3_id);
2911
2912 l_rows_inserted := dbms_sql.execute_and_fetch(l_cursor, true);
2913
2914 edw_log.put_line('rows inserted ' || l_rows_inserted);
2915
2916 if l_rows_inserted > 0 then
2917 dbms_sql.column_value(l_cursor, 1, l_hrchy_enabled);
2918 dbms_sql.column_value(l_cursor, 2, l_catset3_id);
2919 -- edw_log.put_line('l_hrchy_enabled ' || l_hrchy_enabled);
2920 end if;
2921
2922 dbms_sql.close_cursor(l_cursor);
2923
2924 EXCEPTION
2925 WHEN NO_DATA_FOUND THEN
2926 -- edw_log.put_line('in exception ');
2927 null;
2928 END;
2929
2930 EXCEPTION
2931 WHEN NO_DATA_FOUND THEN
2932 edw_log.put_line(' Hierarchy enabled column does not exist');
2933 l_hrchy_enabled := 'N';
2934 END;
2935
2936 IF NVL(l_hrchy_enabled, 'N') = 'Y' THEN
2937 -- If hierarchy is enabled then pushing hierarchical records
2938 --
2939 -- Calling function to Push Child Categories
2940 --
2941 INSERT_CATEGORY_HIERARCHY(
2942 p_from_date => l_push_date_range1,
2943 p_to_date => l_push_date_range2,
2944 p_view_name => l_view_name,
2945 p_category_set_name => l_item_catset3_name,
2946 p_category_set_id => l_catset3_id,
2947 p_no_of_catset_lvls => 10,
2948 p_control_level => l_control_level);
2949
2950 --
2951 -- Loop to INSERT VBH categories FROM prior level tables
2952 --
2953 FOR i2 IN reverse 1..9 LOOP
2954
2955 --
2956 -- Build staging table names
2960 --
2957 --
2958 l_staging_table_name := 'EDW_ITEM_CATSETI3_C' || i2 || '_LSTG';
2959
2961 -- Build parent staging table name
2962 --
2963 l_prior_level_no := i2+1;
2964 l_prior_staging_table_name := 'EDW_ITEM_CATSETI3_C' ||l_prior_level_no|| '_LSTG';
2965
2966 --
2967 -- Calling function to Push Categories
2968 --
2969 Insert_Category(
2970 p_from_date => l_push_date_range1,
2971 p_to_date => l_push_date_range2,
2972 p_staging_table_name => l_staging_table_name,
2973 p_view_name => l_prior_staging_table_name,
2974 p_category_set_name => l_item_catset3_name,
2975 p_control_level => l_control_level);
2976
2977 END LOOP;
2978
2979 -- If any category exists in the category assignment but is
2980 -- not part of the hierarchy need to INSERT this category
2981 -- with an FK of NA_EDW
2982
2983 BEGIN
2984
2985 INSERT INTO EDW_ITEM_CATSETI3_C1_LSTG
2986 (CATEGORY_FK,
2987 CATEGORY_FK_KEY,
2988 CATEGORY_NAME,
2989 CATEGORY_SET_NAME,
2990 CREATION_DATE,
2991 DESCRIPTION,
2992 ERROR_CODE,
2993 INSTANCE,
2994 CATEGORY_DP,
2995 CATEGORY_PK,
2996 CATEGORY_ID,
2997 CATEGORY_SET_ID,
2998 LAST_UPDATE_DATE,
2999 NAME,
3000 REQUEST_ID,
3001 ROW_ID,
3002 USER_ATTRIBUTE1,
3003 USER_ATTRIBUTE2,
3004 USER_ATTRIBUTE3,
3005 USER_ATTRIBUTE4,
3006 USER_ATTRIBUTE5,
3007 OPERATION_CODE,
3008 COLLECTION_STATUS )
3009 SELECT
3010 'NA_EDW',
3011 NULL,
3012 NULL,
3013 CATEGORY_SET_NAME,
3014 CREATION_DATE,
3015 DESCRIPTION,
3016 NULL,
3017 l_instance, /* Bug# 2558245 */
3018 CATEGORY_DP,
3019 TO_CHAR(l_control_level) || '-' || CATEGORY_PK || '-' || l_instance, /* Bug# 2558245 */
3020 CATEGORY_ID,
3021 CATEGORY_SET_ID,
3022 LAST_UPDATE_DATE,
3023 SUBSTRB(NAME, 1, 320),
3024 NULL,
3025 NULL,
3026 USER_ATTRIBUTE1,
3027 USER_ATTRIBUTE2,
3028 USER_ATTRIBUTE3,
3029 USER_ATTRIBUTE4,
3030 USER_ATTRIBUTE5,
3031 NULL,
3032 'READY'
3033 FROM EDW_ITEM_ITEM_CAT_LCV LCV
3034 WHERE LCV.LAST_UPDATE_DATE BETWEEN l_push_date_range1 AND l_push_date_range2
3035 AND LCV.CATEGORY_SET_NAME = l_item_catset3_name
3036 AND NOT EXISTS
3037 (SELECT NULL
3038 FROM EDW_ITEM_CATSETI3_C1_LSTG LSTG
3039 WHERE SUBSTRB(LSTG.CATEGORY_PK, 3) = LCV.CATEGORY_PK || '-' || l_instance /* Bug 2558245 */
3040 AND LSTG.CATEGORY_SET_NAME = l_item_catset3_name
3041 AND LSTG.COLLECTION_STATUS = 'READY')
3042 AND EXISTS /* Bug# 2559696 Removed the use of EDW_ITEMS_CATEGORY_FKV by dsakalle */
3043 (SELECT NULL
3044 FROM
3045 MTL_ITEM_CATEGORIES cat,
3046 MTL_CATEGORY_SETS_TL tl
3047 WHERE tl.CATEGORY_SET_NAME = l_item_catset3_name
3048 AND cat.CATEGORY_ID = LCV.CATEGORY_ID
3049 AND cat.CATEGORY_SET_ID = tl.CATEGORY_SET_ID);
3050
3051 EXCEPTION
3052 WHEN OTHERS THEN RAISE;
3053 END;
3054 ELSE
3055 -- when Hierarchy is not enabled
3056 --
3057 -- Processing for Category Set Collection
3058 --
3059
3060 --
3064
3061 -- Loop to INSERT categories INTO 10 level tables
3062 --
3063 FOR i2 IN 1..10 LOOP
3065 --
3066 -- Build staging table name
3067 --
3068 l_staging_table_name := 'EDW_ITEM_CATSETI3_C' || i2 || '_LSTG';
3069
3070 --
3071 -- Calling function to Push Categories
3072 --
3073 Insert_Category(
3074 p_from_date => l_push_date_range1,
3075 p_to_date => l_push_date_range2,
3076 p_staging_table_name => l_staging_table_name,
3077 p_view_name => l_view_name,
3078 p_category_set_name => l_item_catset3_name,
3079 p_control_level => l_control_level);
3080 END LOOP;
3081 END IF; -- hierarchy enabled
3082 END IF; /* Check Collection Type */
3083 -- Supporting Product Hierarchy Re-Architecture in 11.5.9 END
3084 END IF;
3085
3086 IF (p_item_item_org=1) AND (l_itemorg_catset1_name <> 'NA_EDW') THEN
3087
3088 edw_log.put_line(' ');
3089 edw_log.put_line('Pushing categories to lower levels (EDW_ITEM_ITEMREV, EDW_ITEM_ITEMORG)');
3090 l_rows_inserted := 0;
3091
3092 --
3093 -- Pushing down rows for each hierarchy FROM CURSOR
3094 --
3095
3096 FOR l_itemrev_rec IN l_itemrev_csr(l_itemorg_catset1_name) LOOP
3097 l_catset_category_fk := TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK;
3098 l_level_name := '-COCT';
3099
3100 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
3101 ITEM_ORG_PK,
3102 CATSET_CATEGORY_FK,
3103 NAME,
3104 INSTANCE,
3105 COLLECTION_STATUS,
3106 ITEM_NUMBER_FK,
3107 PROD_FAMILY_FK,
3108 CATEGORY_ID,
3109 CATEGORY_SET_ID,
3110 LAST_UPDATE_DATE)
3111 VALUES (
3112 SUBSTRB(l_catset_category_fk || l_level_name, 1, 1000),
3113 l_catset_category_fk,
3114 SUBSTRB(l_all_item_orgs||'('||l_itemrev_rec.NAME||')', 1, 320),
3115 SUBSTRB(l_itemrev_rec.INSTANCE, 1, 30),
3116 'READY',
3117 'NA_EDW',
3118 'NA_EDW',
3119 l_itemrev_rec.CATEGORY_ID,
3120 l_itemrev_rec.CATEGORY_SET_ID,
3121 l_itemrev_rec.last_update_date
3122 );
3123
3124 l_rows_inserted := l_rows_inserted + 1;
3125
3126 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
3127 ITEM_REVISION_PK,
3128 ITEM_ORG_FK,
3129 INSTANCE,
3130 NAME,
3131 CATEGORY_ID,
3132 CATEGORY_SET_ID,
3133 COLLECTION_STATUS,
3134 LAST_UPDATE_DATE)
3135 VALUES (
3136 SUBSTRB(l_catset_category_fk || l_level_name, 1, 320),
3137 SUBSTRB(l_catset_category_fk || l_level_name, 1, 320),
3138 SUBSTRB(l_itemrev_rec.INSTANCE, 1, 30),
3139 SUBSTRB(l_all_item_revs||'('||l_itemrev_rec.Name||')', 1, 320),
3140 l_itemrev_rec.CATEGORY_ID,
3141 l_itemrev_rec.CATEGORY_SET_ID,
3142 'READY',
3143 l_itemrev_rec.last_update_date);
3144
3145 l_rows_inserted := l_rows_inserted + 1;
3146 END LOOP;
3147
3148 edw_log.put_line('Inserted '||TO_CHAR(NVL(l_rows_inserted,0))||
3149 ' rows into the lower level staging tables');
3150 edw_log.put_line(' ');
3151 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
3152
3153 ELSIF (p_item_item_org=0) THEN
3154
3155 edw_log.put_line(' ');
3156 edw_log.put_line('Pushing categories to lower levels '
3157 ||'(EDW_ITEM_ITEMREV, EDW_ITEM_ITEMORG, EDW_ITEM_ITEM)');
3158 l_rows_inserted := 0;
3159
3160 -- Bug# 3296641
3161 -- separated the push down of VBH i.e. item catset 3
3162 FOR i2 IN 1..2 LOOP
3163 IF (i2 = 1) THEN
3164 l_item_catset_name := l_item_catset1_name;
3165 ELSIF (i2 = 2) THEN
3166 l_item_catset_name := l_item_catset2_name;
3167 END IF;
3168
3169 IF (l_item_catset_name <> 'NA_EDW') THEN
3170 FOR l_itemrev_rec IN l_itemrev_csr(l_item_catset_name) LOOP
3171 IF (i2 = 1) THEN
3172 edw_log.put_line(' ');
3173 edw_log.put_line('Pushing CATSET1 to lower levels ');
3174 l_catset1_category_fk := TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK;
3175 l_level_name := '-PCAT';
3176 l_catset2_category_fk := 'NA_EDW';
3177 l_catset3_category_fk := 'NA_EDW';
3178 ELSIF (i2 = 2) THEN
3179 edw_log.put_line(' ');
3180 edw_log.put_line('Pushing CATSET2 to lower levels ');
3181 l_catset1_category_fk := 'NA_EDW';
3182 l_catset2_category_fk := TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK;
3183 l_catset3_category_fk := 'NA_EDW';
3184 l_level_name := '-C2CT';
3185 END IF;
3186
3187 INSERT INTO EDW_ITEM_ITEM_LSTG(
3188 ITEM_NUMBER_PK,
3189 INSTANCE,
3190 NAME,
3191 COLLECTION_STATUS,
3192 CATSET1_CATEGORY_FK,
3193 CATSET2_CATEGORY_FK,
3194 CATSET3_CATEGORY_FK,
3195 PRODUCT_GROUP_FK,
3196 CATEGORY_ID,
3197 CATEGORY_SET_ID,
3201 l_itemrev_rec.INSTANCE,
3198 LAST_UPDATE_DATE)
3199 VALUES (
3200 TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
3202 SUBSTRB(l_all_items||'('||l_itemrev_rec.NAME||')', 1, 320),
3203 'READY',
3204 l_catset1_category_fk,
3205 l_catset2_category_fk,
3206 l_catset3_category_fk,
3207 'NA_EDW',
3208 l_itemrev_rec.CATEGORY_ID,
3209 l_itemrev_rec.CATEGORY_SET_ID,
3210 l_itemrev_rec.last_update_date
3211 );
3212
3213 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
3214 ITEM_ORG_PK,
3215 ITEM_NUMBER_FK,
3216 INSTANCE,
3217 NAME,
3218 COLLECTION_STATUS,
3219 CATSET_CATEGORY_FK,
3220 PROD_FAMILY_FK,
3221 CATEGORY_ID,
3222 CATEGORY_SET_ID,
3223 LAST_UPDATE_DATE)
3224 VALUES (
3225 TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
3226 TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
3227 l_itemrev_rec.INSTANCE,
3228 SUBSTRB(l_all_item_orgs||'('||l_itemrev_rec.NAME||')', 1, 320),
3229 'READY',
3230 'NA_EDW',
3231 'NA_EDW',
3232 l_itemrev_rec.CATEGORY_ID,
3233 l_itemrev_rec.CATEGORY_SET_ID,
3234 l_itemrev_rec.last_update_date
3235 );
3236
3237 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
3238 ITEM_REVISION_PK,
3239 ITEM_ORG_FK,
3240 NAME,
3241 CATEGORY_ID,
3242 CATEGORY_SET_ID,
3243 INSTANCE,
3244 COLLECTION_STATUS,
3245 LAST_UPDATE_DATE)
3246 VALUES (
3247 SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
3248 SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
3249 SUBSTRB(l_all_item_revs||'('||l_itemrev_rec.NAME||')', 1, 320),
3250 l_itemrev_rec.CATEGORY_ID,
3251 l_itemrev_rec.CATEGORY_SET_ID,
3252 l_itemrev_rec.INSTANCE,
3253 'READY',
3254 l_itemrev_rec.last_update_date);
3255
3256 l_rows_inserted := l_rows_inserted + 1;
3257
3258 END LOOP;
3259 END IF;
3260 END LOOP;
3261
3262 -- Bug# 3296641
3263 l_item_catset_name := l_item_catset3_name;
3264
3265 IF (l_item_catset_name <> 'NA_EDW') THEN
3266 FOR l_itemrev_rec IN l_itemrev_vbh_csr(l_item_catset_name) LOOP
3267 edw_log.put_line(' ');
3268 edw_log.put_line('Pushing CATSET3 to lower levels ');
3269 l_catset1_category_fk := 'NA_EDW';
3270 l_catset2_category_fk := 'NA_EDW';
3271 l_catset3_category_fk := TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK;
3272 l_level_name := '-C3CT';
3273
3274 INSERT INTO EDW_ITEM_ITEM_LSTG(
3275 ITEM_NUMBER_PK,
3276 INSTANCE,
3277 NAME,
3278 COLLECTION_STATUS,
3279 CATSET1_CATEGORY_FK,
3280 CATSET2_CATEGORY_FK,
3281 CATSET3_CATEGORY_FK,
3282 PRODUCT_GROUP_FK,
3283 CATEGORY_ID,
3284 CATEGORY_SET_ID,
3285 LAST_UPDATE_DATE)
3286 VALUES (
3287 TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
3288 l_itemrev_rec.INSTANCE,
3289 SUBSTRB(l_all_items||'('||l_itemrev_rec.NAME||')', 1, 320),
3290 'READY',
3291 l_catset1_category_fk,
3292 l_catset2_category_fk,
3293 l_catset3_category_fk,
3294 'NA_EDW',
3295 l_itemrev_rec.CATEGORY_ID,
3296 l_itemrev_rec.CATEGORY_SET_ID,
3297 l_itemrev_rec.last_update_date
3298 );
3299
3300 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
3301 ITEM_ORG_PK,
3302 ITEM_NUMBER_FK,
3303 INSTANCE,
3304 NAME,
3305 COLLECTION_STATUS,
3306 CATSET_CATEGORY_FK,
3307 PROD_FAMILY_FK,
3308 CATEGORY_ID,
3309 CATEGORY_SET_ID,
3310 LAST_UPDATE_DATE)
3311 VALUES (
3312 TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
3313 TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
3314 l_itemrev_rec.INSTANCE,
3315 SUBSTRB(l_all_item_orgs||'('||l_itemrev_rec.NAME||')', 1, 320),
3316 'READY',
3317 'NA_EDW',
3318 'NA_EDW',
3319 l_itemrev_rec.CATEGORY_ID,
3320 l_itemrev_rec.CATEGORY_SET_ID,
3321 l_itemrev_rec.last_update_date
3322 );
3323
3324 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
3325 ITEM_REVISION_PK,
3326 ITEM_ORG_FK,
3327 NAME,
3328 CATEGORY_ID,
3329 CATEGORY_SET_ID,
3330 INSTANCE,
3331 COLLECTION_STATUS,
3332 LAST_UPDATE_DATE)
3333 VALUES (
3337 l_itemrev_rec.CATEGORY_ID,
3334 SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
3335 SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
3336 SUBSTRB(l_all_item_revs||'('||l_itemrev_rec.NAME||')', 1, 320),
3338 l_itemrev_rec.CATEGORY_SET_ID,
3339 l_itemrev_rec.INSTANCE,
3340 'READY',
3341 l_itemrev_rec.last_update_date);
3342
3343 l_rows_inserted := l_rows_inserted + 1;
3344
3345 END LOOP; -- Bug# 3296641
3346 END IF;
3347
3348 edw_log.put_line(' ');
3349 edw_log.put_line('Inserted '||TO_CHAR(NVL(l_rows_inserted,0))||
3350 ' rows into the lower level staging tables');
3351 edw_log.put_line(' ');
3352 EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
3353
3354 END IF;
3355
3356 COMMIT;
3357
3358 EXCEPTION
3359 WHEN OTHERS THEN
3360 edw_log.debug_line(SUBSTRB(l_stmt,1,2000));
3361 RAISE;
3362
3363 END Push_Category;
3364
3365 PROCEDURE Push_EDW_ITEM_ITEMORG_CAT(
3366 p_from_date DATE,
3367 p_to_date DATE) IS
3368 BEGIN
3369 edw_log.put_line('Pushing ItemOrg Categories');
3370 Push_Category(p_from_date, p_to_date,1); -- item/org cats
3371 edw_log.put_line('Completed Pushing ItemOrg Categories');
3372
3373 END Push_EDW_ITEM_ITEMORG_CAT;
3374
3375 PROCEDURE Push_EDW_ITEM_ITEM_CAT(
3376 p_from_date DATE,
3377 p_to_date DATE) IS
3378 BEGIN
3379 edw_log.put_line('Pushing Item Categories');
3380 Push_Category(p_from_date, p_to_date, 0); -- item cats
3381 edw_log.put_line('Completed Pushing Item Categories');
3382
3383 END Push_EDW_ITEM_ITEM_CAT;
3384
3385 PROCEDURE Push_EDW_ITEM_PROD_LINE(
3386 p_from_date DATE,
3387 p_to_date DATE) IS
3388
3389 l_all_prod_cats VARCHAR2(100);
3390 l_all_prod_grps VARCHAR2(100);
3391 l_all_items VARCHAR2(100);
3392 l_all_item_orgs VARCHAR2(100);
3393 l_all_item_revs VARCHAR2(100);
3394
3395 BEGIN
3396
3397 l_all_prod_cats := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_PCAT');
3398 l_all_prod_grps := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_PGRP');
3399 l_all_items := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_ITEM');
3400 l_all_item_orgs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IORG');
3401 l_all_item_orgs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IREV');
3402
3403 edw_log.put_line(' ');
3404 edw_log.put_line('Pushing EDW_ITEM_ITEM_PROD_LINE');
3405
3406 INSERT INTO EDW_ITEM_PROD_LINE_LSTG(
3407 ALL_FK_KEY,
3408 INTEREST_TYPE_ID,
3409 REQUEST_ID,
3410 ALL_FK,
3411 COLLECTION_STATUS,
3412 DESCRIPTION,
3413 ENABLED_FLAG,
3414 ERROR_CODE,
3415 INSTANCE_CODE,
3416 NAME,
3417 OPERATION_CODE,
3418 PRODUCT_LINE_DP,
3419 PRODUCT_LINE_PK,
3420 ROW_ID,
3421 USER_ATTRIBUTE1,
3422 USER_ATTRIBUTE2,
3423 USER_ATTRIBUTE3,
3424 USER_ATTRIBUTE4,
3425 USER_ATTRIBUTE5,
3426 CREATION_DATE,
3427 DELETION_DATE,
3428 LAST_UPDATE_DATE)
3429 SELECT
3430 NULL ALL_FK_KEY,
3431 INTEREST_TYPE_ID INTEREST_TYPE_ID,
3432 NULL REQUEST_ID,
3433 ALL_FK ALL_FK,
3434 'READY' COLLECTION_STATUS,
3435 DESCRIPTION DESCRIPTION,
3436 ENABLED_FLAG ENABLED_FLAG,
3437 NULL ERROR_CODE,
3438 l_instance INSTANCE_CODE, -- INSTANCE_CODE INSTANCE_CODE, /* Bug# 2558245 */
3439 NAME NAME,
3440 NULL OPERATION_CODE,
3441 PRODUCT_LINE_DP PRODUCT_LINE_DP,
3442 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' PRODUCT_LINE_PK,-- PRODUCT_LINE_PK PRODUCT_LINE_PK,/* Bug# 2558245 */
3443 NULL ROW_ID,
3444 USER_ATTRIBUTE1 USER_ATTRIBUTE1,
3445 USER_ATTRIBUTE2 USER_ATTRIBUTE2,
3446 USER_ATTRIBUTE3 USER_ATTRIBUTE3,
3447 USER_ATTRIBUTE4 USER_ATTRIBUTE4,
3448 USER_ATTRIBUTE5 USER_ATTRIBUTE5,
3449 CREATION_DATE CREATION_DATE,
3450 DELETION_DATE DELETION_DATE,
3451 LAST_UPDATE_DATE LAST_UPDATE_DATE
3452 FROM EDW_ITEM_PROD_LINE_LCV
3453 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
3454
3455 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0)) ||' rows into the staging table');
3456 edw_log.put_line('');
3457
3458 edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_PROD_CATG');
3459
3460 INSERT INTO EDW_ITEM_PROD_CATG_LSTG(
3461 PRODUCT_CATEG_PK,
3462 PRODUCT_LINE_FK,
3463 NAME,
3464 INSTANCE_CODE,
3465 COLLECTION_STATUS,
3466 LAST_UPDATE_DATE)
3467 SELECT
3468 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_CATEG_PK, /* Bug# 2558245 */
3469 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' PRODUCT_LINE_FK, /* Bug# 2558245 */
3470 SUBSTRB(l_all_prod_cats||'('||NAME||')', 1, 320) NAME,
3471 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3472 'READY',
3476
3473 LAST_UPDATE_DATE
3474 FROM EDW_ITEM_PROD_LINE_LCV
3475 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3477 edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_PROD_GRP');
3478
3479 INSERT INTO EDW_ITEM_PROD_GRP_LSTG(
3480 PRODUCT_GROUP_PK,
3481 PRODUCT_CATEG_FK,
3482 NAME,
3483 INSTANCE_CODE,
3484 COLLECTION_STATUS,
3485 LAST_UPDATE_DATE)
3486 SELECT
3487 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_GROUP_PK, /* Bug# 2558245 */
3488 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_CATEG_FK, /* Bug# 2558245 */
3489 SUBSTRB(l_all_prod_grps||'('||NAME||')', 1, 320) NAME,
3490 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3491 'READY',
3492 LAST_UPDATE_DATE
3493 FROM EDW_ITEM_PROD_LINE_LCV
3494 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3495
3496 edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_ITEM');
3497
3498 INSERT INTO EDW_ITEM_ITEM_LSTG(
3499 ITEM_NUMBER_PK,
3500 PRODUCT_GROUP_FK,
3501 NAME,
3502 INSTANCE,
3503 COLLECTION_STATUS,
3504 CATSET1_CATEGORY_FK,
3505 CATSET2_CATEGORY_FK,
3506 CATSET3_CATEGORY_FK,
3507 LAST_UPDATE_DATE)
3508 SELECT
3509 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_NUMBER_PK, /* Bug# 2558245 */
3510 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_GROUP_FK, /* Bug# 2558245 */
3511 SUBSTRB(l_all_items||'('||NAME||')', 1, 320) NAME,
3512 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3513 'READY',
3514 'NA_EDW',
3515 'NA_EDW',
3516 'NA_EDW',
3517 LAST_UPDATE_DATE
3518 FROM EDW_ITEM_PROD_LINE_LCV
3519 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3520
3521 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
3522 ' rows into the staging table');
3523 edw_log.put_line(' ');
3524
3525 edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_ITEMORG');
3526
3527 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
3528 ITEM_ORG_PK,
3529 ITEM_NUMBER_FK,
3530 NAME,
3531 INSTANCE,
3532 COLLECTION_STATUS,
3533 CATSET_CATEGORY_FK,
3534 PROD_FAMILY_FK,
3535 LAST_UPDATE_DATE)
3536 SELECT
3537 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_ORG_PK, /* Bug# 2558245 */
3538 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_NUMBER_FK, /* Bug# 2558245 */
3539 SUBSTRB(l_all_item_orgs||'('||NAME||')', 1, 320) NAME,
3540 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3541 'READY',
3542 'NA_EDW',
3543 'NA_EDW',
3544 LAST_UPDATE_DATE
3545 FROM EDW_ITEM_PROD_LINE_LCV
3546 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3547
3548 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3549 'rows into the staging table');
3550 edw_log.put_line(' ');
3551
3552 edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEMREV');
3553
3554 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
3555 ITEM_REVISION_PK,
3556 ITEM_ORG_FK,
3557 NAME,
3558 INSTANCE,
3559 COLLECTION_STATUS,
3560 LAST_UPDATE_DATE)
3561 SELECT
3562 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_REVISION_PK, /* Bug# 2558245 */
3563 PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_ORG_FK, /* Bug# 2558245 */
3564 SUBSTRB(l_all_item_revs||'('||NAME||')', 1, 320) NAME,
3565 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3566 'READY',
3567 LAST_UPDATE_DATE
3568 FROM EDW_ITEM_PROD_LINE_LCV
3569 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3570
3571 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3572 ' rows into the staging table');
3573 edw_log.put_line(' ');
3574
3575 END Push_EDW_ITEM_PROD_LINE;
3576
3577 PROCEDURE Push_EDW_ITEM_PROD_CATG(
3578 p_from_date DATE,
3579 p_to_date DATE) IS
3580
3581 l_all_prod_grps VARCHAR2(100);
3582 l_all_items VARCHAR2(100);
3583 l_all_item_orgs VARCHAR2(100);
3584 l_all_item_revs VARCHAR2(100);
3585
3586 BEGIN
3587
3588 l_all_prod_grps := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_PGRP');
3589 l_all_items := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_ITEM');
3590 l_all_item_orgs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IORG');
3591 l_all_item_revs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IREV');
3592
3593 edw_log.put_line(' ');
3594 edw_log.put_line('Pushing EDW_ITEM_PROD_CATG');
3595
3596 INSERT INTO EDW_ITEM_PROD_CATG_LSTG(
3597 PRIMARY_CODE_ID,
3598 PRODUCT_LINE_FK_KEY,
3599 REQUEST_ID,
3600 COLLECTION_STATUS,
3601 DESCRIPTION,
3602 ENABLED_FLAG,
3603 ERROR_CODE,
3604 INSTANCE_CODE,
3605 NAME,
3606 OPERATION_CODE,
3607 PRODUCT_CATEG_DP,
3608 PRODUCT_CATEG_PK,
3609 PRODUCT_LINE_FK,
3610 ROW_ID,
3611 USER_ATTRIBUTE1,
3612 USER_ATTRIBUTE2,
3613 USER_ATTRIBUTE3,
3614 USER_ATTRIBUTE4,
3615 USER_ATTRIBUTE5,
3616 CREATION_DATE,
3620 primary_code_id PRIMARY_CODE_ID,
3617 DELETION_DATE,
3618 LAST_UPDATE_DATE)
3619 SELECT
3621 NULL PRODUCT_LINE_FK_KEY,
3622 NULL REQUEST_ID,
3623 'READY' COLLECTION_STATUS,
3624 description DESCRIPTION,
3625 enabled_flag ENABLED_FLAG,
3626 NULL ERROR_CODE,
3627 l_instance INSTANCE_CODE, -- instance_code INSTANCE_CODE, /* Bug# 2558245 */
3628 name NAME,
3629 NULL OPERATION_CODE,
3630 product_categ_dp PRODUCT_CATEG_DP,
3631 product_categ_pk || '-' || l_instance || '-PRIM_CODE' PRODUCT_CATEG_PK, -- product_categ_pk PRODUCT_CATEG_PK, /* Bug# 2558245 */
3632 product_line_fk || '-' || l_instance || '-INTR_TYPE' PRODUCT_LINE_FK, -- product_line_fk PRODUCT_LINE_FK, /* Bug# 2558245 */
3633 NULL ROW_ID,
3634 user_attribute1 USER_ATTRIBUTE1,
3635 user_attribute2 USER_ATTRIBUTE2,
3636 user_attribute3 USER_ATTRIBUTE3,
3637 user_attribute4 USER_ATTRIBUTE4,
3638 user_attribute5 USER_ATTRIBUTE5,
3639 creation_date CREATION_DATE,
3640 deletion_date DELETION_DATE,
3641 last_update_date LAST_UPDATE_DATE
3642 FROM edw_item_prod_catg_lcv
3643 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
3644
3645 edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_PROD_GRP');
3646
3647 INSERT INTO EDW_ITEM_PROD_GRP_LSTG(
3648 PRODUCT_GROUP_PK,
3649 PRODUCT_CATEG_FK,
3650 NAME,
3651 INSTANCE_CODE,
3652 COLLECTION_STATUS,
3653 LAST_UPDATE_DATE)
3654 SELECT
3655 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' PRODUCT_GROUP_PK, /* Bug# 2558245 */
3656 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' PRODUCT_CATEG_FK, /* Bug# 2558245 */
3657 SUBSTRB(l_all_prod_grps||'('||NAME||')', 1, 320) NAME,
3658 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3659 'READY',
3660 LAST_UPDATE_DATE
3661 FROM EDW_ITEM_PROD_CATG_LCV
3662 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3663
3664 edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEM');
3665
3666 INSERT INTO EDW_ITEM_ITEM_LSTG(
3667 ITEM_NUMBER_PK,
3668 PRODUCT_GROUP_FK,
3669 NAME,
3670 INSTANCE,
3671 COLLECTION_STATUS,
3672 CATSET1_CATEGORY_FK,
3673 CATSET2_CATEGORY_FK,
3674 CATSET3_CATEGORY_FK,
3675 LAST_UPDATE_DATE)
3676 SELECT
3677 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_NUMBER_PK, /* Bug# 2558245 */
3678 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' PRODUCT_GROUP_FK, /* Bug# 2558245 */
3679 SUBSTRB(l_all_items||'('||NAME||')', 1, 320) NAME,
3680 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3681 'READY',
3682 'NA_EDW',
3683 'NA_EDW',
3684 'NA_EDW',
3685 LAST_UPDATE_DATE
3686 FROM EDW_ITEM_PROD_CATG_LCV
3687 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3688
3689 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
3690 ' rows into the staging table');
3691 edw_log.put_line(' ');
3692
3693 edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEMORG');
3694
3695 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
3696 ITEM_ORG_PK,
3697 ITEM_NUMBER_FK,
3698 NAME,
3699 INSTANCE,
3700 COLLECTION_STATUS,
3701 CATSET_CATEGORY_FK,
3702 PROD_FAMILY_FK,
3703 LAST_UPDATE_DATE)
3704 SELECT
3705 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_ORG_PK, /* Bug# 2558245 */
3706 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_NUMBER_FK, /* Bug# 2558245 */
3707 SUBSTRB(l_all_item_orgs||'('||NAME||')', 1, 320) NAME,
3708 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3709 'READY',
3710 'NA_EDW',
3711 'NA_EDW',
3712 LAST_UPDATE_DATE
3713 FROM EDW_ITEM_PROD_CATG_LCV
3714 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3715
3716 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3717 'rows into the staging table');
3718 edw_log.put_line(' ');
3719
3720 edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEMREV');
3721
3722 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
3723 ITEM_REVISION_PK,
3724 ITEM_ORG_FK,
3725 INSTANCE,
3726 NAME,
3727 COLLECTION_STATUS,
3728 LAST_UPDATE_DATE)
3729 SELECT
3730 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_REVISION_PK, /* Bug# 2558245 */
3731 PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_ORG_FK, /* Bug# 2558245 */
3732 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3733 SUBSTRB(l_all_item_revs||'('||NAME||')', 1, 320) NAME,
3734 'READY',
3735 LAST_UPDATE_DATE
3736 FROM EDW_ITEM_PROD_CATG_LCV
3737 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3738
3739 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3740 ' rows into the staging table');
3741 edw_log.put_line(' ');
3742
3743 END Push_EDW_ITEM_PROD_CATG;
3744
3745 PROCEDURE Push_EDW_ITEM_PROD_GRP(
3746 p_from_date DATE,
3747 p_to_date DATE) IS
3748
3749 l_all_items VARCHAR2(100);
3750 l_all_item_orgs VARCHAR2(100);
3751 l_all_item_revs VARCHAR2(100);
3752
3753 BEGIN
3754
3758
3755 l_all_items := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_ITEM');
3756 l_all_item_orgs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IORG');
3757 l_all_item_revs := edw_collection_util.get_lookup_value('EDW_LEVEL_PUSH_DOWN', 'EDW_ITEMS_M_IREV');
3759 edw_log.put_line(' ');
3760 edw_log.put_line('Pushing EDW_ITEM_PROD_GRP');
3761
3762 INSERT INTO EDW_ITEM_PROD_GRP_LSTG(
3763 PRODUCT_CATEG_FK_KEY,
3764 REQUEST_ID,
3765 SECONDARY_CODE_ID,
3766 COLLECTION_STATUS,
3767 DESCRIPTION,
3768 ENABLED_FLAG,
3769 ERROR_CODE,
3770 INSTANCE_CODE,
3771 NAME,
3772 OPERATION_CODE,
3773 PRODUCT_CATEG_FK,
3774 PRODUCT_GROUP_DP,
3775 PRODUCT_GROUP_PK,
3776 ROW_ID,
3777 USER_ATTRIBUTE1,
3778 USER_ATTRIBUTE2,
3779 USER_ATTRIBUTE3,
3780 USER_ATTRIBUTE4,
3781 USER_ATTRIBUTE5,
3782 CREATION_DATE,
3783 DELETION_DATE,
3784 LAST_UPDATE_DATE)
3785 SELECT
3786 NULL PRODUCT_CATEG_FK_KEY,
3787 NULL REQUEST_ID,
3788 SECONDARY_CODE_ID SECONDARY_CODE_ID,
3789 'READY' COLLECTION_STATUS,
3790 DESCRIPTION DESCRIPTION,
3791 ENABLED_FLAG ENABLED_FLAG,
3792 NULL ERROR_CODE,
3793 l_instance INSTANCE_CODE, -- INSTANCE_CODE INSTANCE_CODE, /* Bug# 2558245 */
3794 NAME NAME,
3795 NULL OPERATION_CODE,
3796 PRODUCT_CATEG_FK || '-' || l_instance || '-PRIM_CODE' PRODUCT_CATEG_FK, -- PRODUCT_CATEG_FK PRODUCT_CATEG_FK, /* Bug# 2558245 */
3797 PRODUCT_GROUP_DP PRODUCT_GROUP_DP,
3798 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' PRODUCT_GROUP_PK, -- PRODUCT_GROUP_PK PRODUCT_GROUP_PK, /* Bug# 2558245 */
3799 NULL ROW_ID,
3800 USER_ATTRIBUTE1 USER_ATTRIBUTE1,
3801 USER_ATTRIBUTE2 USER_ATTRIBUTE2,
3802 USER_ATTRIBUTE3 USER_ATTRIBUTE3,
3803 USER_ATTRIBUTE4 USER_ATTRIBUTE4,
3804 USER_ATTRIBUTE5 USER_ATTRIBUTE5,
3805 CREATION_DATE CREATION_DATE,
3806 DELETION_DATE DELETION_DATE,
3807 LAST_UPDATE_DATE LAST_UPDATE_DATE
3808 FROM edw_item_prod_grp_lcv
3809 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
3810
3811 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3812 ' rows into the staging table');
3813 edw_log.put_line(' ');
3814
3815 edw_log.put_line('Pushing Product Group to lower level EDW_ITEM_ITEMREV');
3816
3817 INSERT INTO EDW_ITEM_ITEMREV_LSTG(
3818 ITEM_REVISION_PK,
3819 ITEM_ORG_FK,
3820 NAME,
3821 INSTANCE,
3822 COLLECTION_STATUS,
3823 LAST_UPDATE_DATE)
3824 SELECT
3825 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE'||'-PGRP' ITEM_REVISION_PK, /* Bug# 2558245 */
3826 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE'||'-PGRP' ITEM_ORG_FK, /* Bug# 2558245 */
3827 SUBSTRB(l_all_item_revs||'('||NAME||')', 1, 320) NAME,
3828 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3829 'READY',
3830 LAST_UPDATE_DATE
3831 FROM EDW_ITEM_PROD_GRP_LCV
3832 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3833
3834 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3835 ' rows into the staging table');
3836 edw_log.put_line(' ');
3837
3838 edw_log.put_line('Pushing Product Group to lower level EDW_ITEM_ITEMORG');
3839
3840 INSERT INTO EDW_ITEM_ITEMORG_LSTG(
3841 ITEM_ORG_PK,
3842 ITEM_NUMBER_FK,
3843 NAME,
3844 INSTANCE,
3845 COLLECTION_STATUS,
3846 CATSET_CATEGORY_FK,
3847 PROD_FAMILY_FK,
3848 LAST_UPDATE_DATE)
3849 SELECT
3850 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' ||'-PGRP' ITEM_ORG_PK, /* Bug# 2558245 */
3851 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' ||'-PGRP' ITEM_NUMBER_FK, /* Bug# 2558245 */
3852 SUBSTRB(l_all_item_orgs||'('||NAME||')', 1, 320) NAME,
3853 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3854 'READY',
3855 'NA_EDW',
3856 'NA_EDW',
3857 LAST_UPDATE_DATE
3858 FROM EDW_ITEM_PROD_GRP_LCV
3859 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3860
3861 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
3862 'rows into the staging table');
3863 edw_log.put_line(' ');
3864
3865 edw_log.put_line('Pushing Product Group to lower level EDW_ITEM_ITEM');
3866
3867 INSERT INTO EDW_ITEM_ITEM_LSTG(
3868 ITEM_NUMBER_PK,
3869 PRODUCT_GROUP_FK,
3870 NAME,
3871 INSTANCE,
3872 COLLECTION_STATUS,
3873 CATSET1_CATEGORY_FK,
3874 CATSET2_CATEGORY_FK,
3875 CATSET3_CATEGORY_FK,
3876 LAST_UPDATE_DATE)
3877 SELECT
3878 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' ||'-PGRP' ITEM_NUMBER_PK, /* Bug# 2558245 */
3879 PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' PRODUCT_GROUP_FK, /* Bug# 2558245 */
3880 SUBSTRB(l_all_items||'('||NAME||')', 1, 320) NAME,
3881 l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
3882 'READY',
3883 'NA_EDW',
3884 'NA_EDW',
3885 'NA_EDW',
3886 LAST_UPDATE_DATE
3887 FROM EDW_ITEM_PROD_GRP_LCV
3888 WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3889
3890 edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0)) ||
3891 ' rows into the stagint table');
3892 edw_log.put_line(' ');
3893
3894 END Push_EDW_ITEM_PROD_GRP;
3895
3896 END EDW_ITEMS_M_C;