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_HRCHY_STG

Line 111: UPDATE ENI_DENORM_HRCHY_STG

107:
108: -- for sales and marketing enhancement
109: -- if changes in description, skip flag, effective date then inserting separate record
110: IF p_mode_flag IN ('A', 'M', 'D') THEN
111: UPDATE ENI_DENORM_HRCHY_STG
112: SET PARENT_ID = p_parent_id,
113: MODE_FLAG = DECODE(p_mode_flag, 'A', DECODE(MODE_FLAG, 'D', 'M', 'A'), p_mode_flag)
114: WHERE OBJECT_TYPE = p_object_type
115: AND OBJECT_ID = p_object_id

Line 120: INSERT INTO ENI_DENORM_HRCHY_STG (

116: AND CHILD_ID = p_child_id
117: AND BATCH_FLAG = 'NEXT_BATCH';
118:
119: IF SQL%NOTFOUND THEN
120: INSERT INTO ENI_DENORM_HRCHY_STG (
121: OBJECT_TYPE,
122: OBJECT_ID,
123: CHILD_ID,
124: PARENT_ID,

Line 141: UPDATE ENI_DENORM_HRCHY_STG

137: -- U - EXCLUDE_USER_VIEW column set to 'N'
138: -- E - Change in DISABLE_DATE column of a category
139: -- mode flag value 'S' will override the values 'C', 'E'
140: -- if mode flag 'S' is sent twice, we will toggle mode flag to 'S', 'U'
141: UPDATE ENI_DENORM_HRCHY_STG
142: SET PARENT_ID = p_parent_id,
143: MODE_FLAG = DECODE(MODE_FLAG, 'S', DECODE(p_mode_flag, 'E', 'S', 'S', 'U', p_mode_flag),
144: 'U', DECODE(p_mode_flag, 'E', 'U', 'S', 'S', p_mode_flag), p_mode_flag)
145: WHERE OBJECT_TYPE = p_object_type

Line 152: INSERT INTO ENI_DENORM_HRCHY_STG (

148: AND MODE_FLAG IN ('S', 'U', 'E', 'C')
149: AND BATCH_FLAG = 'NEXT_BATCH';
150:
151: IF SQL%NOTFOUND THEN
152: INSERT INTO ENI_DENORM_HRCHY_STG (
153: OBJECT_TYPE,
154: OBJECT_ID,
155: CHILD_ID,
156: PARENT_ID,

Line 200: UPDATE ENI_DENORM_HRCHY_STG

196:
197: select userenv('LANG') into l_language_code from dual;
198: END IF;
199:
200: UPDATE ENI_DENORM_HRCHY_STG
201: SET PARENT_ID = p_parent_id,
202: MODE_FLAG = DECODE(MODE_FLAG, 'S', 'S', 'U', 'U', 'E', 'E', p_mode_flag),
203: LANGUAGE_CODE = DECODE(MODE_FLAG, 'C', DECODE(LANGUAGE_CODE, l_language_code, l_language_code, NULL), NULL)
204: WHERE OBJECT_TYPE = p_object_type

Line 211: INSERT INTO ENI_DENORM_HRCHY_STG (

207: AND MODE_FLAG IN ('S', 'U', 'E', 'C')
208: AND BATCH_FLAG = 'NEXT_BATCH';
209:
210: IF SQL%NOTFOUND THEN
211: INSERT INTO ENI_DENORM_HRCHY_STG (
212: OBJECT_TYPE,
213: OBJECT_ID,
214: CHILD_ID,
215: PARENT_ID,

Line 416: FROM ENI_DENORM_HRCHY_STG S, FND_LANGUAGES TL

412: -- sales and marketing is installed
413: PROCEDURE SYNC_DENORM_PARENTS_PROD_HRCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
414: CURSOR C1 IS
415: SELECT TL.LANGUAGE_CODE, S.CHILD_ID, S.MODE_FLAG, S.LANGUAGE_CODE STG_LANG
416: FROM ENI_DENORM_HRCHY_STG S, FND_LANGUAGES TL
417: WHERE S.OBJECT_ID = g_catset_id
418: AND S.OBJECT_TYPE = 'CATEGORY_SET'
419: AND TL.INSTALLED_FLAG IN ('I', 'B')
420: AND S.MODE_FLAG <> 'D'

Line 498: AND EXISTS (SELECT NULL FROM ENI_DENORM_HRCHY_STG S

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
498: AND EXISTS (SELECT NULL FROM ENI_DENORM_HRCHY_STG S
499: WHERE S.OBJECT_TYPE = 'CATEGORY_SET'
500: AND S.OBJECT_ID = g_catset_id
501: AND S.CHILD_ID = B.CATEGORY_ID
502: AND S.MODE_FLAG IN ('D', 'S')

Line 616: UPDATE ENI_DENORM_HRCHY_STG

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
617: SET BATCH_FLAG = 'NEXT_BATCH'
618: WHERE BATCH_FLAG <> 'NEXT_BATCH'
619: AND OBJECT_TYPE = 'CATEGORY_SET'
620: AND OBJECT_ID = g_catset_id;

Line 689: DELETE FROM ENI_DENORM_HRCHY_STG

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;
692:
693: l_count := SQL%ROWCOUNT;

Line 970: FROM ENI_DENORM_HRCHY_STG

966: PROCEDURE SYNC_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
967:
968: CURSOR top_nodes IS
969: SELECT DISTINCT TOP_NODE_ID
970: FROM ENI_DENORM_HRCHY_STG
971: WHERE BATCH_FLAG <> 'NEXT_BATCH'
972: AND OBJECT_TYPE = 'CATEGORY_SET'
973: AND OBJECT_ID = g_catset_id
974: AND MODE_FLAG IN ('A', 'M'); -- modified for sales and marketing enhancement

Line 1050: UPDATE ENI_DENORM_HRCHY_STG T

1046: AND H.PARENT_ID = B.CATEGORY_ID
1047: AND H.CHILD_ID = B.CATEGORY_ID);
1048: ELSE -- Bug# 3306212 end
1049: -- To Get The Top Node And Child Level In Temp Table
1050: UPDATE ENI_DENORM_HRCHY_STG T
1051: SET (TOP_NODE_ID, CHILD_LEVEL)=
1052: (SELECT X.CATEGORY_ID, LEVEL
1053: FROM MTL_CATEGORY_SET_VALID_CATS X
1054: WHERE X.PARENT_CATEGORY_ID IS NULL

Line 1064: -- commiting to release Lock on ENI_DENORM_HRCHY_STG table. If any exception occurs then

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;
1067:
1068: -- Deleting Nodes from Denorm Table, which are deleted i.e. MODE_FLAG='D'

Line 1080: FROM ENI_DENORM_HRCHY_STG S

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
1081: WHERE S.OBJECT_TYPE = B.OBJECT_TYPE
1082: AND S.OBJECT_ID = B.OBJECT_ID
1083: AND S.CHILD_ID = B.CHILD_ID
1084: AND S.MODE_FLAG = 'D'

Line 1132: FROM ENI_DENORM_HRCHY_STG S

1128: l_conc_request_id,
1129: l_prog_appl_id,
1130: SYSDATE,
1131: l_conc_program_id
1132: FROM ENI_DENORM_HRCHY_STG S
1133: WHERE S.OBJECT_TYPE = 'CATEGORY_SET'
1134: AND S.OBJECT_ID = g_catset_id
1135: AND S.MODE_FLAG = 'A'
1136: AND S.BATCH_FLAG = 'CURRENT_BATCH';

Line 1146: FOR i IN (SELECT * FROM ENI_DENORM_HRCHY_STG

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'
1150: AND BATCH_FLAG = 'CURRENT_BATCH'

Line 1191: FROM ENI_DENORM_HRCHY_STG T

1187: SELECT CHILD_ID , CHILD_LEVEL
1188: INTO l_affected_child , l_affected_level
1189: FROM
1190: (SELECT CHILD_ID, CHILD_LEVEL
1191: FROM ENI_DENORM_HRCHY_STG T
1192: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1193: AND OBJECT_ID = g_catset_id
1194: AND TOP_NODE_ID = i.TOP_NODE_ID
1195: AND BATCH_FLAG = 'CURRENT_BATCH'

Line 1267: UPDATE ENI_DENORM_HRCHY_STG SET BATCH_FLAG = 'PROCESSED'

1263:
1264: l_count := l_count + SQL%ROWCOUNT;
1265:
1266: -- Updating STG Table, making current child as PROCESSED, so that it will not be picked up again
1267: UPDATE ENI_DENORM_HRCHY_STG SET BATCH_FLAG = 'PROCESSED'
1268: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1269: AND OBJECT_ID = g_catset_id
1270: AND CHILD_ID = l_affected_child
1271: AND BATCH_FLAG = 'CURRENT_BATCH';

Line 1396: UPDATE ENI_DENORM_HRCHY_STG

1392: retcode := 2;
1393: ROLLBACK;
1394: -- if any error occurs, then updating staging table's batch_flag back to 'NEXT_BATCH'
1395: -- so that it can be picked up in next incremental load.
1396: UPDATE ENI_DENORM_HRCHY_STG
1397: SET BATCH_FLAG = 'NEXT_BATCH'
1398: WHERE BATCH_FLAG <> 'NEXT_BATCH'
1399: AND OBJECT_TYPE = 'CATEGORY_SET'
1400: AND OBJECT_ID = g_catset_id;

Line 1516: DELETE FROM ENI_DENORM_HRCHY_STG

1512: END IF;
1513:
1514: -- deleting all nodes from staging table, where batch_flag is not 'NEXT_BATCH', since the batch_flag can be
1515: -- CURRENT_BATCH or PROCESSED
1516: DELETE FROM ENI_DENORM_HRCHY_STG
1517: WHERE BATCH_FLAG <> 'NEXT_BATCH'
1518: AND OBJECT_TYPE = 'CATEGORY_SET'
1519: AND OBJECT_ID = g_catset_id;
1520: