eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EDW_ITEMS_M_C dependencies on EDW_LOG

Line 57: edw_log.put_line('Getting functional area for ' || p_category_set_name);

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,

Line 141: edw_log.put_line( 'The collection range is FROM '||

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:

Line 144: edw_log.put_line(' ');

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 || ', ' ||

Line 147: edw_log.put_line( 'The category sets are ' ||

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 || ',' ||

Line 154: edw_log.put_line(' ');

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:

Line 160: edw_log.put_line('');

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:

Line 161: edw_log.put_line('----------------------------------------------');

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

Line 162: edw_log.put_line('Checking for multiple items with the same name');

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';

Line 163: edw_log.put_line('----------------------------------------------');

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);

Line 167: edw_log.put_line(c4.concatenated_segments);

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;

Line 174: edw_log.put_line('----- None -----');

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:

Line 189: edw_log.put_line('Pushing Data');

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);

Line 236: edw_log.put_line(' ');

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,

Line 237: edw_log.put_line('Pushing EDW_ITEM_ITEMREV');

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,

Line 288: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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;

Line 290: edw_log.put_line(' ');

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:

Line 313: edw_log.put_line(' ');

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,

Line 314: edw_log.put_line('Pushing EDW_ITEM_PRDFAM');

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,

Line 362: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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;

Line 364: edw_log.put_line(' ');

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

Line 425: edw_log.put_line(' ');

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;

Line 426: edw_log.put_line('Pushing EDW_ITEM_ITEMORG records in TEMP table');

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:

Line 555: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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

Line 563: edw_log.put_line('Inserting additional items due to category changes ');

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;

Line 698: edw_log.put_line('Done Inserting category changed items into item-org ');

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');

Line 702: edw_log.put_line('Resolving category fks for staging table records');

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 */

Line 703: edw_log.put_line(' ');

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:

Line 732: edw_log.put_line('Resolving lookups for staging table records');

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 =

Line 733: edw_log.put_line(' ');

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

Line 788: edw_log.put_line('Inserting TEMP table records into staging table');

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,

Line 789: edw_log.put_line(' ');

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,

Line 917: edw_log.put_line(' ');

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,

Line 918: edw_log.put_line('Pushing EDW_ITEM_ITEMORG for one time items');

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,

Line 1032: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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:

Line 1034: edw_log.put_line(' ');

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:

Line 1039: 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:

Line 1040: edw_log.put_line('EDW_ITEM_ITEMORG for one time items will not be pushed');

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(' ');

Line 1044: 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:

Line 1045: edw_log.put_line('Pushing items to lower level EDW_ITEM_ITEMREV');

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(

Line 1069: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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;

Line 1071: edw_log.put_line(' ');

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

Line 1078: edw_log.put_line('Pushing one time items to lower level EDW_ITEM_ITEMREV');

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,

Line 1096: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

1092: FROM EDW_ITEM_ONETIME_ITEMORG_LCV
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;

Line 1098: edw_log.put_line(' ');

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;

Line 1167: edw_log.put_line(' ');

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,

Line 1168: edw_log.put_line('Pushing EDW_ITEM_ITEM');

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,

Line 1236: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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:
1240: /* Bug# 2197243 */

Line 1238: edw_log.put_line(' ');

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:
1240: /* Bug# 2197243 */
1241: /** Need to INSERT additional items for whom the category assignments
1242: ** have changed.

Line 1244: edw_log.put_line('Inserting additional items due to category changes ');

1240: /* Bug# 2197243 */
1241: /** Need to INSERT additional items for whom the category assignments
1242: ** have changed.
1243: */
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;

Line 1333: edw_log.put_line('Done Inserting category changed items into item ');

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');

Line 1337: edw_log.put_line('Resolving category fks for staging table records');

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 */

Line 1338: edw_log.put_line(' ');

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:

Line 1485: edw_log.put_line('Pushing EDW_ITEM_ITEM records to Item Org level');

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,

Line 1511: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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:

Line 1513: edw_log.put_line(' ');

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:

Line 1516: edw_log.put_line('Pushing EDW_ITEM_ITEM records to Item Rev level');

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,
1520: ITEM_ORG_FK,

Line 1538: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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;

Line 1540: edw_log.put_line(' ');

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

Line 1546: edw_log.put_line('Pushing EDW_ITEM_ITEM for one time items');

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,

Line 1608: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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:

Line 1610: edw_log.put_line(' ');

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:

Line 1613: edw_log.put_line('Pushing one-time EDW_ITEM_ITEM records to Item Org level');

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,

Line 1635: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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:

Line 1637: edw_log.put_line(' ');

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,

Line 1656: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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;

Line 1658: edw_log.put_line(' ');

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

Line 1664: edw_log.put_line(' ');

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');
1666:
1667: END IF;
1668:

Line 1665: edw_log.put_line('EDW_ITEM_ITEM for one time items will not be pushed');

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');
1666:
1667: END IF;
1668:
1669: --

Line 1733: edw_log.put_line(' ');

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:

Line 1734: edw_log.put_line('No rows fetched for insert into '||p_staging_table_name);

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

Line 1743: edw_log.debug_line(' ');

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:

Line 1744: edw_log.debug_line('Constructing the sql statement for ' || p_staging_table_name || ' and pushing category set ' || p_category_set_name);

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');

Line 1748: edw_log.debug_line('Constructing PKs and FKs');

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',

Line 1772: edw_log.debug_line('VBH insert - Pulling from prior level staging table');

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

Line 1779: edw_log.debug_line('Assigning PKs and FKs based on level');

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:

Line 1803: edw_log.debug_line('Constructing insert stmt');

1799: ' AND category_set_name = :l_category_set_name';
1800:
1801: END IF;
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 || ','||

Line 1855: edw_log.put_line(l_stmt);

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);

Line 1858: edw_log.debug_line('Parse the cursor');

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);

Line 1861: edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');

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

Line 1872: edw_log.debug_line('Pushing data, Executing the cursor');

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);

Line 1875: edw_log.debug_line('Close the cursor');

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)||

Line 1877: edw_log.debug_line(' ');

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(' ');

Line 1879: edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||

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;

Line 1881: edw_log.put_line(' ');

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:

Line 1965: edw_log.debug_line(' ');

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

Line 1966: edw_log.debug_line('Get Value Set id and active Segment for VBH Segment');

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;

Line 1975: edw_log.debug_line(' ');

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;

Line 1976: edw_log.debug_line('Check that VBH Structure has only one segment enabled');

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;

Line 1983: edw_log.debug_line(' ');

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;

Line 1984: edw_log.debug_line('Check that VBH Structure is using Segment1');

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;

Line 1993: edw_log.debug_line(' ');

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;

Line 1994: edw_log.debug_line('Check that Value Set has been assigned to VBH Category Set');

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;

Line 2018: edw_log.debug_line(' ');

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;

Line 2019: edw_log.debug_line('Check that the Category is still assigned at the Item Level');

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;

Line 2027: edw_log.debug_line(' ');

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;

Line 2028: edw_log.debug_line('Check that the Category Set does not allow multiple assignments of items to categories');

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;

Line 2065: edw_log.debug_line(' ');

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:

Line 2066: edw_log.debug_line('Constructing the sql statement for ' || l_staging_table_name || ' AND pushing category set ' || p_category_set_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:
2070: l_fk_name := ' ALL_FK';

Line 2251: edw_log.put_line(l_stmt);

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');

Line 2252: -- edw_log.put_line(l_from_clause);

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);

Line 2253: -- edw_log.put_line(l_where_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:

Line 2255: edw_log.debug_line('Parse the cursor');

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);

Line 2258: edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');

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);

Line 2268: edw_log.debug_line('Pushing data, Executing the cursor');

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);

Line 2271: edw_log.debug_line('Close the cursor');

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)||

Line 2273: edw_log.debug_line(' ');

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(' ');

Line 2275: edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||

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

Line 2277: edw_log.put_line(' ');

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;

Line 2342: edw_log.debug_line(' ');

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;

Line 2343: edw_log.debug_line('Check that the Category Set is still assigned at the Item Level');

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;

Line 2351: edw_log.debug_line(' ');

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;

Line 2352: edw_log.debug_line('Check that the Category Set does not allow multiple assignments of items to categories');

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;

Line 2359: edw_log.put_line('WARNING! : Enforce List of Valid categories is FALSE for Hierarchy Enabled Category set - '||p_category_set_name);

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;

Line 2390: edw_log.debug_line(' ');

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 */

Line 2391: edw_log.debug_line('In Loading Category Set Hierarchy ');

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';

Line 2392: edw_log.debug_line('Constructing the sql statement for ' || l_staging_table_name || ' AND pushing category set ' || p_category_set_name);

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';

Line 2514: edw_log.put_line(l_stmt);

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');

Line 2515: -- edw_log.put_line(l_from_clause);

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);

Line 2516: -- edw_log.put_line(l_where_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:

Line 2518: edw_log.debug_line('Parse the cursor');

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);

Line 2521: edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');

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

Line 2529: edw_log.debug_line('Pushing data, Executing the cursor');

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);

Line 2532: edw_log.debug_line('Close the cursor');

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)||

Line 2534: edw_log.debug_line(' ');

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(' ');

Line 2536: edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||

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

Line 2538: edw_log.put_line(' ');

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;

Line 2646: edw_log.put_line('Determining the collection view to SELECT FROM');

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

Line 2654: edw_log.put_line('Determining the control level');

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

Line 2888: edw_log.put_line(' Hierarchy enabled column exists');

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:

Line 2893: -- edw_log.put_line('in hrchy col type = C' );

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.

Line 2905: edw_log.put_line('Constructing the SQL statement: ' || l_hierarchy_stmt);

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);

Line 2914: edw_log.put_line('rows inserted ' || l_rows_inserted);

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);

Line 2919: -- edw_log.put_line('l_hrchy_enabled ' || l_hrchy_enabled);

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:

Line 2926: -- edw_log.put_line('in exception ');

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

Line 2932: edw_log.put_line(' Hierarchy enabled column does not exist');

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

Line 3088: edw_log.put_line(' ');

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: --

Line 3089: edw_log.put_line('Pushing categories to lower levels (EDW_ITEM_ITEMREV, EDW_ITEM_ITEMORG)');

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

Line 3148: edw_log.put_line('Inserted '||TO_CHAR(NVL(l_rows_inserted,0))||

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:

Line 3150: edw_log.put_line(' ');

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:

Line 3155: 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:

Line 3156: edw_log.put_line('Pushing categories to lower levels '

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

Line 3172: edw_log.put_line(' ');

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';

Line 3173: edw_log.put_line('Pushing CATSET1 to lower levels ');

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';

Line 3179: edw_log.put_line(' ');

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';

Line 3180: edw_log.put_line('Pushing CATSET2 to lower levels ');

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';

Line 3267: edw_log.put_line(' ');

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;

Line 3268: edw_log.put_line('Pushing CATSET3 to lower levels ');

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';

Line 3348: edw_log.put_line(' ');

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;

Line 3349: edw_log.put_line('Inserted '||TO_CHAR(NVL(l_rows_inserted,0))||

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:

Line 3351: edw_log.put_line(' ');

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:

Line 3360: edw_log.debug_line(SUBSTRB(l_stmt,1,2000));

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:

Line 3369: edw_log.put_line('Pushing ItemOrg Categories');

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;

Line 3371: edw_log.put_line('Completed Pushing ItemOrg Categories');

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(

Line 3379: edw_log.put_line('Pushing Item Categories');

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;

Line 3381: edw_log.put_line('Completed Pushing Item Categories');

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(

Line 3403: edw_log.put_line(' ');

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,

Line 3404: edw_log.put_line('Pushing EDW_ITEM_ITEM_PROD_LINE');

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,

Line 3455: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0)) ||' rows into the staging table');

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:

Line 3456: edw_log.put_line('');

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(

Line 3458: edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_PROD_CATG');

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,

Line 3477: edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_PROD_GRP');

3473: LAST_UPDATE_DATE
3474: FROM EDW_ITEM_PROD_LINE_LCV
3475: WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
3476:
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,

Line 3496: edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_ITEM');

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,

Line 3521: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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');

Line 3523: edw_log.put_line(' ');

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(

Line 3525: edw_log.put_line('Pushing Product Line to lower level EDW_ITEM_ITEMORG');

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,

Line 3548: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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');

Line 3550: edw_log.put_line(' ');

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(

Line 3552: edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEMREV');

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,

Line 3571: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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;

Line 3573: edw_log.put_line(' ');

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(

Line 3593: edw_log.put_line(' ');

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,

Line 3594: edw_log.put_line('Pushing EDW_ITEM_PROD_CATG');

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,

Line 3645: edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_PROD_GRP');

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,

Line 3664: edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEM');

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,

Line 3689: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||

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');

Line 3691: edw_log.put_line(' ');

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(

Line 3693: edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEMORG');

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,

Line 3716: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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');

Line 3718: edw_log.put_line(' ');

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(

Line 3720: edw_log.put_line('Pushing Product Category to lower level EDW_ITEM_ITEMREV');

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,

Line 3739: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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;

Line 3741: edw_log.put_line(' ');

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(

Line 3759: edw_log.put_line(' ');

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');
3758:
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,

Line 3760: edw_log.put_line('Pushing EDW_ITEM_PROD_GRP');

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');
3758:
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,

Line 3811: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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');

Line 3813: edw_log.put_line(' ');

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(

Line 3815: edw_log.put_line('Pushing Product Group to lower level EDW_ITEM_ITEMREV');

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,

Line 3834: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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');

Line 3836: edw_log.put_line(' ');

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(

Line 3838: edw_log.put_line('Pushing Product Group to lower level EDW_ITEM_ITEMORG');

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,

Line 3861: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||

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');

Line 3863: edw_log.put_line(' ');

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(

Line 3865: edw_log.put_line('Pushing Product Group to lower level EDW_ITEM_ITEM');

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,

Line 3890: edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0)) ||

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;

Line 3892: edw_log.put_line(' ');

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;