282: l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
283: l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
284:
285: BEGIN
286: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table begining');
287: FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
288: -- getting attribute group id for sales and marketing
289: -- this id will be inserted into the denorm hrchy parents table
290: BEGIN
283: l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
284:
285: BEGIN
286: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table begining');
287: FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
288: -- getting attribute group id for sales and marketing
289: -- this id will be inserted into the denorm hrchy parents table
290: BEGIN
291: SELECT ATTR_GROUP_ID INTO l_attr_grp_id
294: AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET'
295: AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';
296: EXCEPTION
297: WHEN NO_DATA_FOUND THEN
298: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: Attribute Group not found for Sales and Marketing');
299: RAISE;
300: END;
301:
302: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Denorm Hierarchy Parents table');
298: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: Attribute Group not found for Sales and Marketing');
299: RAISE;
300: END;
301:
302: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Denorm Hierarchy Parents table');
303: DELETE ENI_DENORM_HRCHY_PARENTS;
304:
305: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Populating Denorm Hierarchy Parents table');
306: -- for each language installed and each category in hierarchy
301:
302: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Denorm Hierarchy Parents table');
303: DELETE ENI_DENORM_HRCHY_PARENTS;
304:
305: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Populating Denorm Hierarchy Parents table');
306: -- for each language installed and each category in hierarchy
307: l_count := 0;
308: FOR i IN C1 LOOP
309: -- initializing all variables
381:
382: l_count := l_count + 1;
383: END LOOP;
384:
385: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records inserted into Denorm Hierarchy Parents table.');
386:
387: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HRCHY_PARENTS ');
388: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HRCHY_PARENTS');
389:
383: END LOOP;
384:
385: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records inserted into Denorm Hierarchy Parents table.');
386:
387: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HRCHY_PARENTS ');
388: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HRCHY_PARENTS');
389:
390: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table Complete.');
391: EXCEPTION
386:
387: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HRCHY_PARENTS ');
388: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HRCHY_PARENTS');
389:
390: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table Complete.');
391: EXCEPTION
392: WHEN NO_DATA_FOUND THEN
393: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
394: errbuf := 'No data found ' || sqlerrm;
389:
390: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table Complete.');
391: EXCEPTION
392: WHEN NO_DATA_FOUND THEN
393: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
394: errbuf := 'No data found ' || sqlerrm;
395: retcode := 2;
396: ROLLBACK;
397: RAISE;
462: l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
463: l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
464:
465: BEGIN
466: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm Hierarchy Parents table begining.');
467:
468: BEGIN
469: SELECT ATTR_GROUP_ID INTO l_attr_grp_id
470: FROM EGO_FND_DSC_FLX_CTX_EXT
473: AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';
474:
475: EXCEPTION
476: WHEN NO_DATA_FOUND THEN
477: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: Attribute Group not found for Sales and Marketing');
478: RAISE;
479: END;
480:
481: /* Bug : 5233230
478: RAISE;
479: END;
480:
481: /* Bug : 5233230
482: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting records of all disabled languages from Denorm Hierarchy Parents table');
483: -- Deleting all languages, which are being deactivated
484: DELETE FROM ENI_DENORM_HRCHY_PARENTS B
485: WHERE NOT EXISTS (SELECT NULL FROM FND_LANGUAGES L
486: WHERE L.INSTALLED_FLAG IN ('I', 'B')
486: WHERE L.INSTALLED_FLAG IN ('I', 'B')
487: AND B.LANGUAGE = L.LANGUAGE_CODE);
488:
489: l_count := SQL%ROWCOUNT;
490: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');
491: Bug 5233230 */
492:
493: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting all categories which are deleted from hierarchy OR excluded from user view.');
494: -- Deleting all deleted nodes
489: l_count := SQL%ROWCOUNT;
490: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');
491: Bug 5233230 */
492:
493: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting all categories which are deleted from hierarchy OR excluded from user view.');
494: -- Deleting all deleted nodes
495: DELETE FROM ENI_DENORM_HRCHY_PARENTS B
496: WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
497: AND B.OBJECT_ID = g_catset_id
502: AND S.MODE_FLAG IN ('D', 'S')
503: AND S.BATCH_FLAG <> 'NEXT_BATCH');
504:
505: l_count := SQL%ROWCOUNT;
506: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');
507:
508: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting/Updating into Denorm Hierarchy Parents table');
509:
510: l_count := 0;
504:
505: l_count := SQL%ROWCOUNT;
506: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');
507:
508: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting/Updating into Denorm Hierarchy Parents table');
509:
510: l_count := 0;
511: FOR i IN C1 LOOP
512:
607: END LOOP; -- end C2
608: END IF;
609: END LOOP; -- end C1
610:
611: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count || ' records inserted/updated into Denorm Hierarchy Parents table');
612: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm Hierarchy Parents table Complete.');
613: EXCEPTION
614: WHEN OTHERS THEN
615: ROLLBACK;
608: END IF;
609: END LOOP; -- end C1
610:
611: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count || ' records inserted/updated into Denorm Hierarchy Parents table');
612: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm Hierarchy Parents table Complete.');
613: EXCEPTION
614: WHEN OTHERS THEN
615: ROLLBACK;
616: UPDATE ENI_DENORM_HRCHY_STG
663: l_validate_flag VARCHAR2(1); -- Bug# 3306212
664: l_struct_id NUMBER; -- Bug# 3306212
665:
666: BEGIN
667: FND_FILE.PUT_LINE(FND_FILE.LOG,'Denorm table Initial Load Start');
668:
669: -- Finding whether Hierarchy is enabled or not
670: BEGIN -- Bug# 3013192, Bug# 3306212
671: SELECT HIERARCHY_ENABLED, VALIDATE_FLAG, STRUCTURE_ID INTO l_hrchy_enabled, l_validate_flag, l_struct_id
671: SELECT HIERARCHY_ENABLED, VALIDATE_FLAG, STRUCTURE_ID INTO l_hrchy_enabled, l_validate_flag, l_struct_id
672: FROM MTL_CATEGORY_SETS_B
673: WHERE CATEGORY_SET_ID = g_catset_id;
674: EXCEPTION WHEN NO_DATA_FOUND THEN
675: FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
676: RAISE;
677: END;
678:
679: -- Deleting records from Denorm Table for object_type = CATEGORY_SET
676: RAISE;
677: END;
678:
679: -- Deleting records from Denorm Table for object_type = CATEGORY_SET
680: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting Records from Denorm Table for Product Catalog');
681:
682: DELETE FROM ENI_DENORM_HIERARCHIES
683: WHERE OBJECT_TYPE = 'CATEGORY_SET';
684:
683: WHERE OBJECT_TYPE = 'CATEGORY_SET';
684:
685: -- Bug# 3047381, moved delete of staging table from last to begining. So that any changes in hierarchy, during Load is running
686: -- will be captured in next incremental load.
687: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting records from Staging table');
688: -- deleting records from staging table, since all the changes are already there in Denorm table
689: DELETE FROM ENI_DENORM_HRCHY_STG
690: WHERE OBJECT_TYPE = 'CATEGORY_SET'
691: AND OBJECT_ID = g_catset_id;
690: WHERE OBJECT_TYPE = 'CATEGORY_SET'
691: AND OBJECT_ID = g_catset_id;
692:
693: l_count := SQL%ROWCOUNT;
694: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted from Staging table');
695:
696: -- Inserting Self-referencing Nodes
697: IF (NVL(l_validate_flag, 'N') = 'N' AND NVL(l_hrchy_enabled, 'N') = 'N') THEN
698: -- Inserting Self-referencing Nodes from mtl_categories
697: IF (NVL(l_validate_flag, 'N') = 'N' AND NVL(l_hrchy_enabled, 'N') = 'N') THEN
698: -- Inserting Self-referencing Nodes from mtl_categories
699: -- since enforce list of valid categories is not true and
700: -- hierarchy is not enabled
701: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Self Referencing Nodes');
702:
703: INSERT INTO ENI_DENORM_HIERARCHIES(
704: PARENT_ID,
705: IMM_CHILD_ID,
743: FROM MTL_CATEGORIES_B
744: WHERE STRUCTURE_ID = l_struct_id;
745: ELSE
746: -- Inserting Self-referencing Nodes
747: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Self Referencing Nodes');
748: -- Using execute immediate to set ITEM_ASSGN_FLAG and LEAF_NODE_FLAG
749: -- the same insert statement doesn't works without execute immediate
750: l_sql :=
751: 'INSERT INTO ENI_DENORM_HIERARCHIES(
799:
800: END IF;
801:
802: l_count := SQL%ROWCOUNT;
803: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserted '||l_count||' Self-referencing records');
804:
805: IF NVL(l_hrchy_enabled, 'N') = 'Y' THEN -- Bug# 3013192
806: l_count := 0;
807: -- For Inserting Parent, Immchild, Child Relationships
804:
805: IF NVL(l_hrchy_enabled, 'N') = 'Y' THEN -- Bug# 3013192
806: l_count := 0;
807: -- For Inserting Parent, Immchild, Child Relationships
808: FND_FILE.PUT_LINE(FND_FILE.LOG,'Hierarchy is enabled'); -- Bug# 3013192
809: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Hierarchical records');
810:
811: FOR i in c1 LOOP
812: INSERT INTO ENI_DENORM_HIERARCHIES (
805: IF NVL(l_hrchy_enabled, 'N') = 'Y' THEN -- Bug# 3013192
806: l_count := 0;
807: -- For Inserting Parent, Immchild, Child Relationships
808: FND_FILE.PUT_LINE(FND_FILE.LOG,'Hierarchy is enabled'); -- Bug# 3013192
809: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Hierarchical records');
810:
811: FOR i in c1 LOOP
812: INSERT INTO ENI_DENORM_HIERARCHIES (
813: PARENT_ID,
855:
856: l_count := l_count + SQL%ROWCOUNT;
857: END LOOP;
858:
859: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted '||l_count||' Hierarchical records');
860: END IF;
861:
862: -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
863: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
859: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted '||l_count||' Hierarchical records');
860: END IF;
861:
862: -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
863: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
864: -- updating Item Assignment flag for all categories, which have items attached to it
865: UPDATE ENI_DENORM_HIERARCHIES B
866: SET ITEM_ASSGN_FLAG = 'Y'
867: WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
872: AND C.CATEGORY_ID = B.CHILD_ID);
873:
874: l_count := SQL%ROWCOUNT;
875:
876: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
877:
878: FND_FILE.PUT_LINE(FND_FILE.LOG,'Checking Item Assignments for Unassigned');
879: -- Checking Item assignment flag for Unassigned category
880: -- if all items are attached to some categories within this category set then
874: l_count := SQL%ROWCOUNT;
875:
876: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
877:
878: FND_FILE.PUT_LINE(FND_FILE.LOG,'Checking Item Assignments for Unassigned');
879: -- Checking Item assignment flag for Unassigned category
880: -- if all items are attached to some categories within this category set then
881: -- Item assignment flag for Unassigned node will be 'N'
882: BEGIN
887: WHERE C.CATEGORY_SET_ID = g_catset_id
888: AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
889: AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);
890: EXCEPTION WHEN NO_DATA_FOUND THEN
891: FND_FILE.PUT_LINE(FND_FILE.LOG,'All Items are assigned to Product Catalog');
892: l_count := 0;
893: END;
894: /* ER# 3185516, updating Item Assignment Flag even in non-DBI env.
895: ELSE
896: l_count := 0;
897: END IF;
898: */
899:
900: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Unassigned Node');
901: INSERT INTO ENI_DENORM_HIERARCHIES (
902: PARENT_ID,
903: IMM_CHILD_ID,
904: CHILD_ID,
938: l_prog_appl_id,
939: SYSDATE,
940: l_conc_program_id);
941:
942: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HIERARCHIES ');
943: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HIERARCHIES');
944:
945: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
946: EXCEPTION
941:
942: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HIERARCHIES ');
943: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HIERARCHIES');
944:
945: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
946: EXCEPTION
947: WHEN NO_DATA_FOUND THEN
948: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
949: errbuf := 'No data found ' || sqlerrm;
944:
945: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
946: EXCEPTION
947: WHEN NO_DATA_FOUND THEN
948: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
949: errbuf := 'No data found ' || sqlerrm;
950: retcode := 2;
951: ROLLBACK;
952: RAISE;
990: SELECT VALIDATE_FLAG, STRUCTURE_ID INTO l_validate_flag, l_struct_id
991: FROM MTL_CATEGORY_SETS_B
992: WHERE CATEGORY_SET_ID = g_catset_id;
993: EXCEPTION WHEN NO_DATA_FOUND THEN
994: FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
995: RAISE;
996: END;
997:
998: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Incremental Load begining');
994: FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
995: RAISE;
996: END;
997:
998: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Incremental Load begining');
999:
1000: IF NVL(l_validate_flag, 'N') = 'N' THEN -- Bug# 3306212
1001: INSERT INTO ENI_DENORM_HIERARCHIES(
1002: PARENT_ID,
1058: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1059: AND T.OBJECT_ID = g_catset_id;
1060:
1061: l_count := SQL%ROWCOUNT;
1062: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records found in Staging Table for Incremental Load');
1063:
1064: -- commiting to release Lock on ENI_DENORM_HRCHY_STG table. If any exception occurs then
1065: -- batch_flag will be updated back to 'NEXT_BATCH' and commited.
1066: COMMIT;
1069: -- If a parent node is deleted from hierarchy, then all its children are also deleted from hierarchy
1070: -- and all the nodes actually deleted from hierarchy will be inserted into the staging table with
1071: -- mode_flag = 'D'. So we need to delete all records from denorm table, where child_id = child_id in staging table
1072: -- and mode_flag = 'D'
1073: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Nodes from Denorm Table, which are deleted from Hierarchy');
1074:
1075: -- Bug# 3047381 , removed use of ROWID , instead using PK columns
1076: DELETE FROM ENI_DENORM_HIERARCHIES B -- changed the statement due to performance reasons
1077: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1084: AND S.MODE_FLAG = 'D'
1085: AND S.BATCH_FLAG = 'CURRENT_BATCH');
1086:
1087: l_count := SQL%ROWCOUNT;
1088: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted from denorm table');
1089:
1090: -- Inserting Self Referencing Nodes For New Nodes Into Denorm Table
1091: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Self-referencing nodes for new nodes');
1092: INSERT INTO ENI_DENORM_HIERARCHIES(
1087: l_count := SQL%ROWCOUNT;
1088: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted from denorm table');
1089:
1090: -- Inserting Self Referencing Nodes For New Nodes Into Denorm Table
1091: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Self-referencing nodes for new nodes');
1092: INSERT INTO ENI_DENORM_HIERARCHIES(
1093: PARENT_ID,
1094: IMM_CHILD_ID,
1095: CHILD_ID,
1135: AND S.MODE_FLAG = 'A'
1136: AND S.BATCH_FLAG = 'CURRENT_BATCH';
1137:
1138: l_count := SQL%ROWCOUNT;
1139: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted as Self-referencing nodes');
1140:
1141: l_count := 0;
1142: -- Deleting all rows, which will no longer be a part of Hierarchy
1143: -- whenever a node is moved i.e. MODE_FLAG='M', there will be some records in denorm table
1141: l_count := 0;
1142: -- Deleting all rows, which will no longer be a part of Hierarchy
1143: -- whenever a node is moved i.e. MODE_FLAG='M', there will be some records in denorm table
1144: -- which needs to be deleted, as they will no longer will be a part of the hierarhcy
1145: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting records, which are no longer required, due to movement in Hierarchy');
1146: FOR i IN (SELECT * FROM ENI_DENORM_HRCHY_STG
1147: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1148: AND OBJECT_ID = g_catset_id
1149: AND MODE_FLAG = 'M'
1174:
1175: l_count := l_count + SQL%ROWCOUNT;
1176: END LOOP;
1177:
1178: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted');
1179:
1180: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting new relations');
1181: l_count := 0;
1182: -- Creating Records For Changes In The Hierarchy
1176: END LOOP;
1177:
1178: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted');
1179:
1180: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting new relations');
1181: l_count := 0;
1182: -- Creating Records For Changes In The Hierarchy
1183: FOR i IN top_nodes LOOP
1184: LOOP
1272:
1273: END LOOP;
1274: END LOOP; -- End Loop For Top Nodes
1275:
1276: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted');
1277:
1278: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Leaf Node Flag');
1279: -- updating leaf node flag for all records where new leaf_node_flag <> current leaf_node_flag
1280: -- Using Execute Immediate because the same code doesn't compiles without Execute immediate
1274: END LOOP; -- End Loop For Top Nodes
1275:
1276: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted');
1277:
1278: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Leaf Node Flag');
1279: -- updating leaf node flag for all records where new leaf_node_flag <> current leaf_node_flag
1280: -- Using Execute Immediate because the same code doesn't compiles without Execute immediate
1281: -- Bug# 3045649, added WHO columns in update statements
1282: l_sql :=
1301: EXECUTE IMMEDIATE l_sql USING l_user_id, l_user_id, l_conc_request_id, l_prog_appl_id, l_conc_program_id, g_catset_id, g_catset_id;
1302:
1303: l_count := SQL%ROWCOUNT;
1304:
1305: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Leaf Node Flag');
1306:
1307: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Top Node Flag');
1308: -- updating top node flag for all records where new top_node_flag <> current top_node_flag
1309: -- using a inline view to improve the performance
1303: l_count := SQL%ROWCOUNT;
1304:
1305: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Leaf Node Flag');
1306:
1307: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Top Node Flag');
1308: -- updating top node flag for all records where new top_node_flag <> current top_node_flag
1309: -- using a inline view to improve the performance
1310: -- Bug# 3045649, added WHO columns in update statements
1311: UPDATE (
1328: PROGRAM_ID = l_conc_program_id
1329: WHERE NEW_TOP_NODE <> TOP_NODE_FLAG;
1330:
1331: l_count := SQL%ROWCOUNT;
1332: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Top Node Flag');
1333: END IF; -- Bug# 3306212
1334:
1335: -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1336: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
1332: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Top Node Flag');
1333: END IF; -- Bug# 3306212
1334:
1335: -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1336: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
1337: -- updating Item Assignment flag to 'Y', if item assignment is present and current Item assgn flag is not 'Y'
1338: -- Bug# 3045649, added WHO columns in update statements
1339: UPDATE ENI_DENORM_HIERARCHIES B
1340: SET
1377: WHERE C.CATEGORY_SET_ID = g_catset_id
1378: AND C.CATEGORY_ID = B.CHILD_ID);
1379:
1380: l_count := l_count + SQL%ROWCOUNT;
1381: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
1382: -- END IF; -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1383:
1384: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm table complete');
1385: EXCEPTION
1380: l_count := l_count + SQL%ROWCOUNT;
1381: FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
1382: -- END IF; -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1383:
1384: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm table complete');
1385: EXCEPTION
1386: WHEN OTHERS THEN
1387: if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1388: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_HIERARCHY', 'Error: ' ||
1427: l_status := NULL;
1428: l_installed := fnd_installation.get(appl_id => 279, dep_appl_id => 279, status => l_status, industry => l_industry );
1429: IF NVL(l_status, 'N') = 'I' THEN
1430: l_AS_installed := TRUE;
1431: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Sales (AS) is installed.');
1432: END IF;
1433:
1434: -- checking installation of 'AMS' (Oracle Marketing Online)
1435: l_status := NULL;
1435: l_status := NULL;
1436: l_installed := fnd_installation.get(appl_id => 530, dep_appl_id => 530, status => l_status, industry => l_industry );
1437: IF NVL(l_status, 'N') = 'I' THEN
1438: l_AMS_installed := TRUE;
1439: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Marketing Online (AMS) is installed.');
1440: END IF;
1441:
1442: -- checking installation of 'OZF' (Oracle Trade Management)
1443: l_status := NULL;
1443: l_status := NULL;
1444: l_installed := fnd_installation.get(appl_id => 682, dep_appl_id => 682, status => l_status, industry => l_industry );
1445: IF NVL(l_status, 'N') = 'I' THEN
1446: l_OZF_installed := TRUE;
1447: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Trade Management (OZF) is installed.');
1448: END IF;
1449:
1450: -- checking installation of 'ASN'
1451: l_status := NULL;
1451: l_status := NULL;
1452: l_installed := fnd_installation.get(appl_id => 280, dep_appl_id => 280, status => l_status, industry => l_industry );
1453: IF NVL(l_status, 'N') = 'I' THEN
1454: l_ASN_installed := TRUE;
1455: FND_FILE.PUT_LINE(FND_FILE.LOG, 'ASN is installed.');
1456: END IF;
1457:
1458: -- if any of the above applications are installed then load de-norm parents table
1459: IF l_AS_installed OR l_AMS_installed OR l_OZF_installed OR l_ASN_installed THEN
1503: SYNC_DENORM_PARENTS_PROD_HRCHY(err, ret);
1504: errbuf := err;
1505: retcode := ret;
1506: EXCEPTION WHEN NO_DATA_FOUND THEN
1507: FND_FILE.PUT_LINE(FND_FILE.LOG, 'De-norm parents table is empty, calling Initial Load for de-norm parents table...');
1508: LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1509: errbuf := err;
1510: retcode := ret;
1511: END;
1518: AND OBJECT_TYPE = 'CATEGORY_SET'
1519: AND OBJECT_ID = g_catset_id;
1520:
1521: EXCEPTION WHEN NO_DATA_FOUND THEN
1522: FND_FILE.PUT_LINE(FND_FILE.LOG, 'De-norm table is empty, calling Initial Load...');
1523: LOAD_HIERARCHY(err, ret);
1524: errbuf := err;
1525: retcode := ret;
1526:
1839: l_product_catalog MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE;
1840:
1841: BEGIN
1842: retcode := 0;
1843: FND_FILE.PUT_LINE(FND_FILE.LOG,'OBIEE Denorm table Initial Load Start');
1844:
1845: -- Finding whether Hierarchy is enabled or not
1846: BEGIN
1847: SELECT HIERARCHY_ENABLED, STRUCTURE_ID INTO l_hrchy_enabled, l_struct_id
1848: FROM MTL_CATEGORY_SETS_B
1849: WHERE CATEGORY_SET_ID = g_catset_id
1850: AND HIERARCHY_ENABLED = 'Y';
1851: EXCEPTION WHEN NO_DATA_FOUND THEN
1852: FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
1853: FND_FILE.PUT_LINE(FND_FILE.LOG,'- Or the Category set is not enabled for hierarchies');
1854: RAISE;
1855: END;
1856:
1849: WHERE CATEGORY_SET_ID = g_catset_id
1850: AND HIERARCHY_ENABLED = 'Y';
1851: EXCEPTION WHEN NO_DATA_FOUND THEN
1852: FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
1853: FND_FILE.PUT_LINE(FND_FILE.LOG,'- Or the Category set is not enabled for hierarchies');
1854: RAISE;
1855: END;
1856:
1857: BEGIN
1860: FROM mtl_default_category_sets mdcs
1861: ,mtl_category_sets_vl csvl
1862: WHERE csvl.category_set_id = mdcs.category_set_id AND mdcs.functional_area_id=11;
1863: EXCEPTION WHEN OTHERS THEN
1864: FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while searching for functional area 11');
1865: RAISE;
1866: END;
1867:
1868: --Truncate the table first [INITIAL LOAD]
1865: RAISE;
1866: END;
1867:
1868: --Truncate the table first [INITIAL LOAD]
1869: FND_FILE.PUT_LINE(FND_FILE.LOG,'Truncating the DENORM table');
1870:
1871: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_tab_schema || '.ENI_ICAT_CDENORM_HIERARCHIES';
1872:
1873: --Fetch the profile value for No of levels to flatten
1873: --Fetch the profile value for No of levels to flatten
1874: fnd_profile.get('ENI_ICAT_DENORM_LEVEL', l_levels_to_flatten);
1875:
1876: IF l_levels_to_flatten IS NULL or l_levels_to_flatten > 10 or l_levels_to_flatten < 5 THEN
1877: FND_FILE.PUT_LINE(FND_FILE.LOG,'Profile [ENI: SBA/OBIEE LEVELS IN HIERARCHY] has incorrect value of: [' || l_levels_to_flatten || ']');
1878: FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting the denormalisation to default level of 5');
1879: l_levels_to_flatten := 5;
1880: END IF;
1881:
1874: fnd_profile.get('ENI_ICAT_DENORM_LEVEL', l_levels_to_flatten);
1875:
1876: IF l_levels_to_flatten IS NULL or l_levels_to_flatten > 10 or l_levels_to_flatten < 5 THEN
1877: FND_FILE.PUT_LINE(FND_FILE.LOG,'Profile [ENI: SBA/OBIEE LEVELS IN HIERARCHY] has incorrect value of: [' || l_levels_to_flatten || ']');
1878: FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting the denormalisation to default level of 5');
1879: l_levels_to_flatten := 5;
1880: END IF;
1881:
1882: --Start populating the denorm table
1879: l_levels_to_flatten := 5;
1880: END IF;
1881:
1882: --Start populating the denorm table
1883: FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalizing catalog [' || l_product_catalog || '] to ' || l_levels_to_flatten || ' levels.');
1884: FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalization is Row + Column flattening');
1885:
1886: l_sql :=
1887: 'INSERT INTO eni_icat_cdenorm_hierarchies ( ' ||
1880: END IF;
1881:
1882: --Start populating the denorm table
1883: FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalizing catalog [' || l_product_catalog || '] to ' || l_levels_to_flatten || ' levels.');
1884: FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalization is Row + Column flattening');
1885:
1886: l_sql :=
1887: 'INSERT INTO eni_icat_cdenorm_hierarchies ( ' ||
1888: ' category_id_level1 ' ||
1955: USING l_user_id, SYSDATE, l_user_id, SYSDATE, l_user_id, l_conc_request_id ,l_prog_appl_id ,SYSDATE ,l_conc_program_id
1956: ,g_catset_id, g_catset_id;
1957:
1958: IF SQL%ROWCOUNT > 0 THEN
1959: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted [' || SQL%ROWCOUNT ||'] rows into denorm table.');
1960: ELSE
1961: RAISE NO_DATA_FOUND;
1962: END IF;
1963:
1960: ELSE
1961: RAISE NO_DATA_FOUND;
1962: END IF;
1963:
1964: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert the UNASSIGNED product category row.');
1965:
1966: INSERT INTO eni_icat_cdenorm_hierarchies (
1967: category_id_level1
1968: ,category_id_level2
1997: ,SYSDATE
1998: ,l_conc_program_id
1999: );
2000:
2001: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_ICAT_CDENORM_HIERARCHIES ');
2002: FND_STATS.gather_table_stats (ownname=>g_tab_schema, tabname=>'ENI_ICAT_CDENORM_HIERARCHIES');
2003:
2004: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
2005:
2000:
2001: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_ICAT_CDENORM_HIERARCHIES ');
2002: FND_STATS.gather_table_stats (ownname=>g_tab_schema, tabname=>'ENI_ICAT_CDENORM_HIERARCHIES');
2003:
2004: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
2005:
2006: COMMIT;
2007: EXCEPTION
2008: WHEN NO_DATA_FOUND THEN
2006: COMMIT;
2007: EXCEPTION
2008: WHEN NO_DATA_FOUND THEN
2009:
2010: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
2011: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.');
2012: errbuf := SQLERRM || ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.';
2013: retcode := 1;
2014: ROLLBACK;
2007: EXCEPTION
2008: WHEN NO_DATA_FOUND THEN
2009:
2010: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
2011: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.');
2012: errbuf := SQLERRM || ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.';
2013: retcode := 1;
2014: ROLLBACK;
2015: WHEN OTHERS THEN