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.ENI_DENORM_HRCHY dependencies on ENI_DENORM_HIERARCHIES

Line 46: FROM ENI_DENORM_HIERARCHIES

42: FUNCTION GET_LAST_CATALOG_UPDATE_DATE RETURN DATE IS
43: l_date DATE;
44: BEGIN
45: SELECT MAX(LAST_UPDATE_DATE) INTO l_date
46: FROM ENI_DENORM_HIERARCHIES
47: WHERE OBJECT_ID = g_catset_id
48: AND OBJECT_TYPE = 'CATEGORY_SET';
49:
50: RETURN l_date;

Line 642: FROM MTL_CATEGORY_SET_VALID_CATS T, ENI_DENORM_HIERARCHIES D, ENI_DENORM_HIERARCHIES D1

638: T.PARENT_CATEGORY_ID PARENT_ID,
639: T.CATEGORY_ID CHILD_ID,
640: D.TOP_NODE_FLAG,
641: D1.LEAF_NODE_FLAG
642: FROM MTL_CATEGORY_SET_VALID_CATS T, ENI_DENORM_HIERARCHIES D, ENI_DENORM_HIERARCHIES D1
643: WHERE T.CATEGORY_SET_ID = g_catset_id
644: AND T.PARENT_CATEGORY_ID IS NOT NULL
645: AND D.OBJECT_TYPE = 'CATEGORY_SET'
646: AND D.OBJECT_ID = g_catset_id

Line 682: DELETE FROM ENI_DENORM_HIERARCHIES

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

Line 703: INSERT INTO ENI_DENORM_HIERARCHIES(

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,
706: CHILD_ID,
707: OBJECT_TYPE,

Line 751: 'INSERT INTO ENI_DENORM_HIERARCHIES(

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(
752: PARENT_ID,
753: IMM_CHILD_ID,
754: CHILD_ID,
755: OBJECT_TYPE,

Line 812: INSERT INTO ENI_DENORM_HIERARCHIES (

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,
814: IMM_CHILD_ID,
815: CHILD_ID,
816: OBJECT_TYPE,

Line 865: UPDATE ENI_DENORM_HIERARCHIES B

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'
868: AND B.OBJECT_ID = g_catset_id
869: AND EXISTS (SELECT NULL

Line 901: INSERT INTO ENI_DENORM_HIERARCHIES (

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,
905: OBJECT_TYPE,

Line 942: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HIERARCHIES ');

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

Line 943: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HIERARCHIES');

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
947: WHEN NO_DATA_FOUND THEN

Line 1001: INSERT INTO ENI_DENORM_HIERARCHIES(

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,
1003: IMM_CHILD_ID,
1004: CHILD_ID,
1005: OBJECT_TYPE,

Line 1043: AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES H

1039: SYSDATE,
1040: l_conc_program_id
1041: FROM MTL_CATEGORIES_B B
1042: WHERE B.STRUCTURE_ID = l_struct_id
1043: AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES H
1044: WHERE H.OBJECT_TYPE = 'CATEGORY_SET'
1045: AND H.OBJECT_ID = g_catset_id
1046: AND H.PARENT_ID = B.CATEGORY_ID
1047: AND H.CHILD_ID = B.CATEGORY_ID);

Line 1076: DELETE FROM ENI_DENORM_HIERARCHIES B -- changed the statement due to performance reasons

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'
1078: AND OBJECT_ID = g_catset_id
1079: AND EXISTS (SELECT NULL
1080: FROM ENI_DENORM_HRCHY_STG S

Line 1092: INSERT INTO ENI_DENORM_HIERARCHIES(

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,
1096: OBJECT_TYPE,

Line 1153: DELETE FROM ENI_DENORM_HIERARCHIES B

1149: AND MODE_FLAG = 'M'
1150: AND BATCH_FLAG = 'CURRENT_BATCH'
1151: ORDER BY CHILD_LEVEL DESC) LOOP -- Bug# 3047381, removed TOP_NODE_ID ASC from order by clause
1152:
1153: DELETE FROM ENI_DENORM_HIERARCHIES B
1154: WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1155: AND B.OBJECT_ID = g_catset_id
1156: AND EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES T -- all records with child = i.child_id or children of i.child_id
1157: WHERE T.OBJECT_TYPE = B.OBJECT_TYPE

Line 1156: AND EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES T -- all records with child = i.child_id or children of i.child_id

1152:
1153: DELETE FROM ENI_DENORM_HIERARCHIES B
1154: WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1155: AND B.OBJECT_ID = g_catset_id
1156: AND EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES T -- all records with child = i.child_id or children of i.child_id
1157: WHERE T.OBJECT_TYPE = B.OBJECT_TYPE
1158: AND T.OBJECT_ID = B.OBJECT_ID
1159: AND B.CHILD_ID = T.CHILD_ID
1160: AND T.PARENT_ID = i.CHILD_ID)

Line 1161: AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES D -- Hierarchy below the i.child_id must not be deleted

1157: WHERE T.OBJECT_TYPE = B.OBJECT_TYPE
1158: AND T.OBJECT_ID = B.OBJECT_ID
1159: AND B.CHILD_ID = T.CHILD_ID
1160: AND T.PARENT_ID = i.CHILD_ID)
1161: AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES D -- Hierarchy below the i.child_id must not be deleted
1162: WHERE D.OBJECT_TYPE = B.OBJECT_TYPE
1163: AND D.OBJECT_ID = B.OBJECT_ID
1164: AND B.PARENT_ID = D.CHILD_ID
1165: AND D.PARENT_ID = i.CHILD_ID)

Line 1207: INSERT INTO ENI_DENORM_HIERARCHIES (

1203: EXIT;
1204: END;
1205:
1206: -- Inserting records due to change in hierarchy
1207: INSERT INTO ENI_DENORM_HIERARCHIES (
1208: PARENT_ID,
1209: IMM_CHILD_ID,
1210: CHILD_ID,
1211: OBJECT_TYPE,

Line 1257: WHERE NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES P

1253: (SELECT CATEGORY_ID CHILD_ID
1254: FROM MTL_CATEGORY_SET_VALID_CATS A
1255: START WITH CATEGORY_ID = l_affected_child AND A.CATEGORY_SET_ID = g_catset_id
1256: CONNECT BY PARENT_CATEGORY_ID = PRIOR CATEGORY_ID AND A.CATEGORY_SET_ID = g_catset_id) B
1257: WHERE NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES P
1258: WHERE P.OBJECT_TYPE = 'CATEGORY_SET'
1259: AND P.OBJECT_ID = g_catset_id
1260: AND P.PARENT_ID = A.PARENT_ID
1261: AND P.IMM_CHILD_ID = A.IMM_CHILD_ID

Line 1283: 'UPDATE ENI_DENORM_HIERARCHIES B

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 :=
1283: 'UPDATE ENI_DENORM_HIERARCHIES B
1284: SET LEAF_NODE_FLAG = DECODE(B.LEAF_NODE_FLAG, ''N'', ''Y'', ''N'') ,
1285: LAST_UPDATE_DATE = SYSDATE,
1286: LAST_UPDATED_BY = :l_user_id,
1287: LAST_UPDATE_LOGIN = :l_user_id,

Line 1315: FROM ENI_DENORM_HIERARCHIES B, MTL_CATEGORY_SET_VALID_CATS C

1311: UPDATE (
1312: SELECT B.PARENT_ID, DECODE(C.PARENT_CATEGORY_ID, NULL, 'Y', 'N') NEW_TOP_NODE, B.TOP_NODE_FLAG,
1313: B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.REQUEST_ID, B.PROGRAM_APPLICATION_ID,
1314: B.PROGRAM_UPDATE_DATE, B.PROGRAM_ID
1315: FROM ENI_DENORM_HIERARCHIES B, MTL_CATEGORY_SET_VALID_CATS C
1316: WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1317: AND B.OBJECT_ID = g_catset_id
1318: AND C.CATEGORY_SET_ID = B.OBJECT_ID
1319: AND C.CATEGORY_ID = B.PARENT_ID)

Line 1339: UPDATE ENI_DENORM_HIERARCHIES B

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
1341: ITEM_ASSGN_FLAG = 'Y',
1342: LAST_UPDATE_DATE = SYSDATE,
1343: LAST_UPDATED_BY = l_user_id,

Line 1361: UPDATE ENI_DENORM_HIERARCHIES B

1357:
1358: l_count := SQL%ROWCOUNT;
1359:
1360: -- updating Item Assignment flag to 'N', if item assignment is not present and current Item assgn flag is not 'N'
1361: UPDATE ENI_DENORM_HIERARCHIES B
1362: SET
1363: ITEM_ASSGN_FLAG = 'N',
1364: LAST_UPDATE_DATE = SYSDATE,
1365: LAST_UPDATED_BY = l_user_id,

Line 1484: FROM ENI_DENORM_HIERARCHIES

1480: ELSIF p_refresh_mode = 'PARTIAL' THEN
1481: BEGIN -- Bug# 3057568 Start
1482: -- Checking if De-norm table is empty then calling Initial Load, else Incr. Load
1483: SELECT 1 INTO l_cnt
1484: FROM ENI_DENORM_HIERARCHIES
1485: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1486: AND ROWNUM = 1;
1487:
1488: SYNC_HIERARCHY(err, ret);

Line 1701: UPDATE ENI_DENORM_HIERARCHIES B

1697: END IF;
1698: END IF;
1699:
1700: -- updating Item Assignment flag for all categories, which have items attached to it
1701: UPDATE ENI_DENORM_HIERARCHIES B
1702: SET
1703: ITEM_ASSGN_FLAG = 'Y',
1704: LAST_UPDATE_DATE = SYSDATE,
1705: LAST_UPDATED_BY = l_user_id,

Line 1720: UPDATE ENI_DENORM_HIERARCHIES B

1716: WHERE C.CATEGORY_SET_ID = g_catset_id
1717: AND C.CATEGORY_ID = B.CHILD_ID);
1718:
1719: -- updating Item Assignment flag for all categories, which does not have items attached to it
1720: UPDATE ENI_DENORM_HIERARCHIES B
1721: SET
1722: ITEM_ASSGN_FLAG = 'N',
1723: LAST_UPDATE_DATE = SYSDATE,
1724: LAST_UPDATED_BY = l_user_id,

Line 1757: UPDATE ENI_DENORM_HIERARCHIES B

1753: EXCEPTION WHEN NO_DATA_FOUND THEN
1754: l_count := 0;
1755: END;
1756:
1757: UPDATE ENI_DENORM_HIERARCHIES B
1758: SET
1759: ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'N', 'Y'),
1760: LAST_UPDATE_DATE = SYSDATE,
1761: LAST_UPDATED_BY = l_user_id,