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

Line 1: PACKAGE BODY ENI_DENORM_HRCHY AS

1: PACKAGE BODY ENI_DENORM_HRCHY AS
2: /* $Header: ENIDENHB.pls 120.4 2007/03/13 08:52:48 lparihar ship $ */
3:
4: g_func_area_id NUMBER := 11; -- Variable To Hold Functional Area For Product Functional Area
5: g_catset_id NUMBER := ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID; -- Variable To Hold Product Catalog Category Set

Line 5: g_catset_id NUMBER := ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID; -- Variable To Hold Product Catalog Category Set

1: PACKAGE BODY ENI_DENORM_HRCHY AS
2: /* $Header: ENIDENHB.pls 120.4 2007/03/13 08:52:48 lparihar ship $ */
3:
4: g_func_area_id NUMBER := 11; -- Variable To Hold Functional Area For Product Functional Area
5: g_catset_id NUMBER := ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID; -- Variable To Hold Product Catalog Category Set
6: g_tab_schema VARCHAR2(20) := 'ENI';
7:
8: -- This Public Function will return the Default Category Set Associated with
9: -- Product Reporting Functional Area

Line 75: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');

71: -- validating parameters bug 3134719
72: IF p_mode_flag NOT IN ('A', 'M', 'D', 'C', 'S', 'E') THEN
73: x_return_status := 'E';
74: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
75: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');
76: END IF;
77: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
78: RETURN;
79: END IF;

Line 84: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');

80:
81: IF p_object_type <> 'CATEGORY_SET' THEN
82: x_return_status := 'E';
83: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
84: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');
85: END IF;
86: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
87: RETURN;
88: END IF;

Line 93: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');

89:
90: IF p_object_id IS NULL THEN
91: x_return_status := 'E';
92: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
93: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');
94: END IF;
95: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
96: RETURN;
97: END IF;

Line 102: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');

98:
99: IF p_child_id IS NULL THEN
100: x_return_status := 'E';
101: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
102: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');
103: END IF;
104: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
105: RETURN;
106: END IF;

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 188: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');

184: l_language_code := NULL;
185: WHEN NO_DATA_FOUND THEN
186: x_return_status := 'E';
187: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
188: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');
189: END IF;
190: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
191: RETURN;
192: END;

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 236: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);

232: x_msg_data := null;
233: EXCEPTION WHEN OTHERS THEN
234: x_return_status := 'U';
235: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);
237: END IF;
238: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
239: END INSERT_INTO_STAGING;
240:

Line 303: DELETE ENI_DENORM_HRCHY_PARENTS;

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
307: l_count := 0;

Line 341: INSERT INTO ENI_DENORM_HRCHY_PARENTS (

337: l_imm_par_id := k.CHILD_ID;
338: END IF;
339: END LOOP;
340:
341: INSERT INTO ENI_DENORM_HRCHY_PARENTS (
342: OBJECT_TYPE,
343: OBJECT_ID,
344: ATTRIBUTE_GROUP_ID,
345: CATEGORY_ID,

Line 387: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HRCHY_PARENTS ');

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

Line 388: FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HRCHY_PARENTS');

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

Line 400: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_DENORM_PARENTS_PROD_HRCHY', 'Error: ' ||

396: ROLLBACK;
397: RAISE;
398: WHEN OTHERS THEN
399: if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
400: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_DENORM_PARENTS_PROD_HRCHY', 'Error: ' ||
401: sqlerrm || ' .Transaction will be rolled back');
402: end if;
403: errbuf := 'Error :' || sqlerrm;
404: retcode := 2;

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 484: DELETE FROM ENI_DENORM_HRCHY_PARENTS B

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')
487: AND B.LANGUAGE = L.LANGUAGE_CODE);
488:

Line 495: DELETE FROM ENI_DENORM_HRCHY_PARENTS B

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
498: AND EXISTS (SELECT NULL FROM ENI_DENORM_HRCHY_STG S
499: WHERE S.OBJECT_TYPE = 'CATEGORY_SET'

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 546: UPDATE ENI_DENORM_HRCHY_PARENTS B

542: l_imm_par_id := k.CHILD_ID;
543: END IF;
544: END LOOP;
545:
546: UPDATE ENI_DENORM_HRCHY_PARENTS B
547: SET CATEGORY_DESC = l_desc,
548: CONCAT_CAT_PARENTAGE = RTRIM(l_concat_desc, '/'),
549: CATEGORY_LEVEL_NUM = l_eff_level,
550: DISABLE_DATE = j.DISABLE_DATE,

Line 565: INSERT INTO ENI_DENORM_HRCHY_PARENTS (

561: AND CATEGORY_ID = j.CATEGORY_ID
562: AND LANGUAGE = i.LANGUAGE_CODE;
563:
564: IF SQL%NOTFOUND THEN
565: INSERT INTO ENI_DENORM_HRCHY_PARENTS (
566: OBJECT_TYPE,
567: OBJECT_ID,
568: ATTRIBUTE_GROUP_ID,
569: CATEGORY_ID,

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 623: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_DENORM_PARENTS_PROD_HRCHY', 'Error: ' ||

619: AND OBJECT_TYPE = 'CATEGORY_SET'
620: AND OBJECT_ID = g_catset_id;
621: COMMIT;
622: if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
623: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_DENORM_PARENTS_PROD_HRCHY', 'Error: ' ||
624: sqlerrm || ' .Transaction will be rolled back');
625: end if;
626: errbuf := 'Error :' || sqlerrm;
627: retcode := 2;

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 955: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_HIERARCHY', 'Error: ' ||

951: ROLLBACK;
952: RAISE;
953: WHEN OTHERS THEN
954: if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
955: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_HIERARCHY', 'Error: ' ||
956: sqlerrm || ' .Transaction will be rolled back');
957: end if;
958: errbuf := 'Error :' || sqlerrm;
959: retcode := 2;

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 1388: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_HIERARCHY', 'Error: ' ||

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: ' ||
1389: sqlerrm || ' .Transaction will be rolled back');
1390: end if;
1391: errbuf := 'Error :' || sqlerrm;
1392: retcode := 2;

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 1499: FROM ENI_DENORM_HRCHY_PARENTS

1495: ret := null;
1496: -- if de-norm parents table is empty then call full load of only de-norm parents table
1497: BEGIN
1498: SELECT 1 INTO l_cnt
1499: FROM ENI_DENORM_HRCHY_PARENTS
1500: WHERE OBJECT_TYPE = 'CATEGORY_SET'
1501: AND ROWNUM = 1;
1502:
1503: SYNC_DENORM_PARENTS_PROD_HRCHY(err, ret);

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:

Line 1548: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_PRODUCT_HIERARCHY', 'Error: ' ||

1544: END;
1545: EXCEPTION
1546: WHEN OTHERS THEN
1547: if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1548: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_PRODUCT_HIERARCHY', 'Error: ' ||
1549: sqlerrm || ' .Transaction will be rolled back');
1550: end if;
1551: errbuf := 'Error :' || sqlerrm;
1552: retcode := 2;

Line 1645: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);

1641: EXCEPTION
1642: WHEN OTHERS THEN
1643: x_return_status := 'U';
1644: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1645: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1646: END IF;
1647: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1648: END SYNC_CATEGORY_ASSIGNMENTS;
1649:

Line 1778: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);

1774: EXCEPTION
1775: WHEN OTHERS THEN
1776: x_return_status := 'U';
1777: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1778: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);
1779: END IF;
1780: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1781: END SYNC_STAR_ITEMS_FROM_IOI;
1782:

Line 1916: ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||

1912:
1913: --Start of SELECT clause
1914: l_sql := l_sql ||
1915: '( SELECT ' ||
1916: ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1917: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1918: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1919: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1920: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';

Line 1917: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||

1913: --Start of SELECT clause
1914: l_sql := l_sql ||
1915: '( SELECT ' ||
1916: ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1917: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1918: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1919: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1920: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';
1921:

Line 1918: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||

1914: l_sql := l_sql ||
1915: '( SELECT ' ||
1916: ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1917: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1918: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1919: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1920: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';
1921:
1922: --Add category_id_level* columns according to the profile value selected

Line 1919: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||

1915: '( SELECT ' ||
1916: ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1917: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1918: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1919: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1920: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';
1921:
1922: --Add category_id_level* columns according to the profile value selected
1923: FOR i IN 6..l_levels_to_flatten

Line 1920: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';

1916: ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1917: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1918: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1919: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1920: ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';
1921:
1922: --Add category_id_level* columns according to the profile value selected
1923: FOR i IN 6..l_levels_to_flatten
1924: LOOP

Line 1925: l_sql := l_sql || ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,' || i || ',''' || g_delimiter || ''') ';

1921:
1922: --Add category_id_level* columns according to the profile value selected
1923: FOR i IN 6..l_levels_to_flatten
1924: LOOP
1925: l_sql := l_sql || ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,' || i || ',''' || g_delimiter || ''') ';
1926: END LOOP;
1927:
1928: l_sql := l_sql ||
1929: ' ,category_id ' ||

Line 2017: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_OBIEE_HIERARCHY', 'Error: ' ||

2013: retcode := 1;
2014: ROLLBACK;
2015: WHEN OTHERS THEN
2016: if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2017: FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_OBIEE_HIERARCHY', 'Error: ' ||
2018: sqlerrm || ' .Transaction will be rolled back');
2019: end if;
2020: errbuf := 'Error :' || sqlerrm;
2021: retcode := 2;

Line 2025: END ENI_DENORM_HRCHY;

2021: retcode := 2;
2022: ROLLBACK;
2023: END LOAD_OBIEE_HIERARCHY;
2024:
2025: END ENI_DENORM_HRCHY;