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.MTH_ITEM_DIMENSION_PKG dependencies on MTH_UTIL_PKG

Line 23: Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

19: UPDATE MTH_DIM_HIERARCHY DH
20: SET dh.last_update_date = SYSDATE,
21: DH.NUMBER_OF_LEVEL =
22: (SELECT
23: Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

20: SET dh.last_update_date = SYSDATE,
21: DH.NUMBER_OF_LEVEL =
22: (SELECT
23: Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

21: DH.NUMBER_OF_LEVEL =
22: (SELECT
23: Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

22: (SELECT
23: Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

23: Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))

Line 28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

24: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
32: AS num_levels

Line 29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

25: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
32: AS num_levels
33: FROM MTH_ITEM_DENORM_D ID

Line 30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

26: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
32: AS num_levels
33: FROM MTH_ITEM_DENORM_D ID
34: WHERE id.LEVEL_num = 10 and

Line 31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))

27: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
32: AS num_levels
33: FROM MTH_ITEM_DENORM_D ID
34: WHERE id.LEVEL_num = 10 and
35: ID.hierarchy_id = DH.hierarchy_id )

Line 41: ID.item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND

37: DH.dimension_name= 'ITEM' AND
38: EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
39: WHERE DH.dimension_name= 'ITEM' AND
40: ID.level_num = 10 AND
41: ID.item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
42: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
43: END UPDATE_ITEM_HIER_LVL_IN_DIM_HR;
44:
45: /* ****************************************************************************

Line 64: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;

60: v_log_from_date DATE;
61: v_unassigned_item_name varchar2(240);
62: v_unassigned_category_name varchar2(240);
63: BEGIN
64: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
65: v_log_to_date := SYSDATE;
66: /*New additions */
67: -- Call mth_run_log_pre_load
68: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);

Line 68: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);

64: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
65: v_log_to_date := SYSDATE;
66: /*New additions */
67: -- Call mth_run_log_pre_load
68: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);
69: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date) ;
70:
71: --insert the values in the denormalised item denorm table.
72: --use of max function in the select is just to by pass the usage in the

Line 69: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date) ;

65: v_log_to_date := SYSDATE;
66: /*New additions */
67: -- Call mth_run_log_pre_load
68: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);
69: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date) ;
70:
71: --insert the values in the denormalised item denorm table.
72: --use of max function in the select is just to by pass the usage in the
73: --group by clause.

Line 77: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;

73: --group by clause.
74: --Maximun nuumber of leves supported is 10.
75: --Item will be at lowest level i.e. 10.
76:
77: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
78:
79: BEGIN
80: SELECT ITEM_NAME into v_unassigned_item_name
81: from MTH_ITEMS_D

Line 278: MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key

274: -- for all the hierarchies
275: GROUP BY HIERARCHY_ID,ITEM_PK_KEY
276: UNION
277: select mdh.hierarchy_id,
278: MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
279: ,v_unassigned_key level1_level_key
280: ,v_unassigned_key level2_level_key
281: ,v_unassigned_key level3_level_key
282: ,v_unassigned_key level4_level_key

Line 307: mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');

303: where mdh.dimension_name= 'ITEM' and
304: mdll.hierarchy_id (+) = mdh.hierarchy_id);
305:
306: -- Balance the item denorm table
307: mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');
308:
309: -- Push up and fill the level key and name for the ones with NULL
310:
311: UPDATE MTH_ITEM_DENORM_D

Line 818: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key);

814:
815:
816: /*New additions */
817: ----Call mth_run_log_post_load
818: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key);
819:
820: EXCEPTION
821: WHEN OTHERS THEN
822: ROLLBACK;

Line 960: WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL

956: decode(level2_fk_key,NULL,0,1) +
957: decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
958: max_level--maximum level in the hierarchy
959: FROM MTH_ITEM_DENORM_D_TMP
960: WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
961: )
962: WHERE c_level 963: AND level9_fk_key IS NOT NULL
964: )';

Line 1043: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;

1039: v_unassigned_item_name varchar2(240);
1040: v_unassigned_category_name varchar2(240);
1041: BEGIN
1042:
1043: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1044:
1045: BEGIN
1046: SELECT ITEM_NAME into v_unassigned_item_name
1047: from MTH_ITEMS_D

Line 1240: MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key

1236: -- for all the hierarchies
1237: GROUP BY HIERARCHY_ID,ITEM_PK_KEY
1238: UNION
1239: select mdh.hierarchy_id,
1240: MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
1241: ,v_unassigned_key level1_level_key
1242: ,v_unassigned_key level2_level_key
1243: ,v_unassigned_key level3_level_key
1244: ,v_unassigned_key level4_level_key

Line 2050: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;

2046:
2047:
2048: BEGIN
2049:
2050: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2051: INSERT
2052: INTO MTH_ITEM_DENORM_D_TMP
2053: (
2054: HIERARCHY_ID,

Line 2356: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2352: level1_fk_key,
2353: level9_name,level8_name,level7_name,level6_name,
2354: level5_name,level4_name,level3_name,level2_name,
2355: level1_name,
2356: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2353: level9_name,level8_name,level7_name,level6_name,
2354: level5_name,level4_name,level3_name,level2_name,
2355: level1_name,
2356: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2354: level5_name,level4_name,level3_name,level2_name,
2355: level1_name,
2356: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2355: level1_name,
2356: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2356: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level

Line 2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2357: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP

Line 2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2358: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND

Line 2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2359: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND

Line 2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level

2360: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND

2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2376: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND

Line 2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2376: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2377: level9_fk_key IS NOT NULL

Line 2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2370: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2376: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2377: level9_fk_key IS NOT NULL
2378: );

Line 2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2371: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2376: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2377: level9_fk_key IS NOT NULL
2378: );
2379:

Line 2376: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND

2372: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2376: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2377: level9_fk_key IS NOT NULL
2378: );
2379:
2380:

Line 2548: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2544: level1_fk_key,
2545: level9_name,level8_name,level7_name,level6_name,
2546: level5_name,level4_name,level3_name,level2_name,
2547: level1_name,
2548: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2545: level9_name,level8_name,level7_name,level6_name,
2546: level5_name,level4_name,level3_name,level2_name,
2547: level1_name,
2548: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2546: level5_name,level4_name,level3_name,level2_name,
2547: level1_name,
2548: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2547: level1_name,
2548: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2548: p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level

Line 2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2549: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D

Line 2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2550: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND

Line 2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2551: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND

Line 2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level

2552: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND

2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2568: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND

Line 2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2568: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2569: level9_fk_key IS NOT NULL

Line 2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2562: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2568: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2569: level9_fk_key IS NOT NULL
2570: );

Line 2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

2563: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2568: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2569: level9_fk_key IS NOT NULL
2570: );
2571:

Line 2568: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND

2564: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2568: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2569: level9_fk_key IS NOT NULL
2570: );
2571:
2572:

Line 2672: MTH_UTIL_PKG.MTH_UA_GET_VAL

2668: LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
2669: LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
2670: LEVEL1_NAME )
2671: VALUES ( p_hierarchy_id,
2672: MTH_UTIL_PKG.MTH_UA_GET_VAL
2673: ,p_unassigned_key
2674: ,p_unassigned_key
2675: ,p_unassigned_key
2676: ,p_unassigned_key

Line 3744: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;

3740: v_unassigned_val VARCHAR2(30);
3741: BEGIN
3742:
3743: v_log_to_date := sysdate;
3744: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3745:
3746: INSERT INTO mth_item_denorm_d
3747: (hierarchy_id,
3748: item_fk_key,

Line 3842: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;

3838: v_unassigned_val VARCHAR2(30);
3839: BEGIN
3840:
3841: v_log_to_date := sysdate;
3842: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3843:
3844: Merge INTO mth_item_denorm_d mid USING (SELECT hierarchy_id,
3845: item_fk_key,
3846: level9_fk_key,

Line 3974: Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3970: SELECT id.hierarchy_id, mdh.NUMBER_OF_LEVEL,
3971: id.max_level, id.min_level
3972: FROM (
3973: SELECT hierarchy_id,
3974: Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3971: id.max_level, id.min_level
3972: FROM (
3973: SELECT hierarchy_id,
3974: Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3972: FROM (
3973: SELECT hierarchy_id,
3974: Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3973: SELECT hierarchy_id,
3974: Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3974: Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,

Line 3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3975: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3976: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3977: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,

3978: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3979: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3980: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3981: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3982: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

Line 3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3983: Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level

Line 3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3984: decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992: FROM MTH_ITEM_DENORM_D_TMP

Line 3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3985: decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992: FROM MTH_ITEM_DENORM_D_TMP
3993: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL

Line 3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +

3986: decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992: FROM MTH_ITEM_DENORM_D_TMP
3993: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL
3994: GROUP BY hierarchy_id) id,

Line 3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level

3987: decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992: FROM MTH_ITEM_DENORM_D_TMP
3993: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL
3994: GROUP BY hierarchy_id) id,
3995: mth_dim_hierarchy mdh

Line 3993: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL

3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992: FROM MTH_ITEM_DENORM_D_TMP
3993: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL
3994: GROUP BY hierarchy_id) id,
3995: mth_dim_hierarchy mdh
3996: WHERE id.hierarchy_id = mdh.hierarchy_id;
3997:

Line 4001: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;

3997:
3998:
3999: BEGIN
4000: v_log_to_date := sysdate;
4001: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4002:
4003:
4004: BEGIN
4005: SELECT ITEM_NAME into v_unassigned_item_name

Line 4030: --mth_util_pkg.log_msg('ITEM_DIM_LOAD_DENORM_INCR start',

4026: when others then
4027: v_unassigned_item_name := null;
4028: end;
4029:
4030: --mth_util_pkg.log_msg('ITEM_DIM_LOAD_DENORM_INCR start',
4031: -- mth_util_pkg.G_DBG_PROC_FUN_START);
4032:
4033:
4034: -- Step 1: If the table is empty, call init procedure

Line 4031: -- mth_util_pkg.G_DBG_PROC_FUN_START);

4027: v_unassigned_item_name := null;
4028: end;
4029:
4030: --mth_util_pkg.log_msg('ITEM_DIM_LOAD_DENORM_INCR start',
4031: -- mth_util_pkg.G_DBG_PROC_FUN_START);
4032:
4033:
4034: -- Step 1: If the table is empty, call init procedure
4035: /*

Line 4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',

4043:
4044: /*Step 2*/
4045: -- Call mth_run_log_pre_load
4046: -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049: NULL,0,v_log_to_date);
4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4051: v_log_from_date,v_log_to_date) ;

Line 4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',

4044: /*Step 2*/
4045: -- Call mth_run_log_pre_load
4046: -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049: NULL,0,v_log_to_date);
4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4051: v_log_from_date,v_log_to_date) ;
4052:

Line 4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,

4046: -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049: NULL,0,v_log_to_date);
4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4051: v_log_from_date,v_log_to_date) ;
4052:
4053: -- Step 3: Load items impacted by the changed categories entries
4054: -- from item hierarchy since last run into item denorm temp table

Line 4087: decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4083: DH.NUMBER_OF_LEVEL =
4084: -- (SELECT num_levels
4085: -- FROM
4086: (SELECT
4087: decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4084: -- (SELECT num_levels
4085: -- FROM
4086: (SELECT
4087: decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4085: -- FROM
4086: (SELECT
4087: decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4086: (SELECT
4087: decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

Line 4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4087: decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)

Line 4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4088: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096: AS num_levels

Line 4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4089: decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096: AS num_levels
4097: FROM MTH_ITEM_DENORM_D ID

Line 4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +

4090: decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096: AS num_levels
4097: FROM MTH_ITEM_DENORM_D ID
4098: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND

Line 4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)

4091: decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096: AS num_levels
4097: FROM MTH_ITEM_DENORM_D ID
4098: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4099: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 )

Line 4098: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND

4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096: AS num_levels
4097: FROM MTH_ITEM_DENORM_D ID
4098: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4099: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 )
4100: -- WHERE ROWNUM = 1)
4101: WHERE (DH.NUMBER_OF_LEVEL IS NULL OR DH.NUMBER_OF_LEVEL = 0) AND
4102: DH.dimension_name= 'ITEM' AND

Line 4104: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND

4100: -- WHERE ROWNUM = 1)
4101: WHERE (DH.NUMBER_OF_LEVEL IS NULL OR DH.NUMBER_OF_LEVEL = 0) AND
4102: DH.dimension_name= 'ITEM' AND
4103: EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
4104: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4105: DH.dimension_name= 'ITEM' AND
4106: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
4107: */
4108:

Line 4178: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND

4174: decode(level2_fk_key,v_unassigned_key,0,NULL,0,1) +
4175: decode(level1_fk_key,v_unassigned_key,0,NULL,0,1))
4176: INTO v_num_levels
4177: FROM MTH_ITEM_DENORM_D
4178: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4179: level_num = 10 AND
4180: hierarchy_id = r_hid_and_levels.hierarchy_id;
4181:
4182:

Line 4358: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',

4354:
4355:
4356:
4357: -- Step 8: Run mth_run_log_post_load to update run log entry
4358: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',
4359: MTH_UTIL_PKG.MTH_UA_GET_VAL());
4360:
4361:
4362: COMMIT;

Line 4359: MTH_UTIL_PKG.MTH_UA_GET_VAL());

4355:
4356:
4357: -- Step 8: Run mth_run_log_post_load to update run log entry
4358: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',
4359: MTH_UTIL_PKG.MTH_UA_GET_VAL());
4360:
4361:
4362: COMMIT;
4363:

Line 4395: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;

4391: BEGIN
4392:
4393: -- Initialize default parameters
4394: v_log_to_date := sysdate;
4395: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4396:
4397: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP start', mth_util_pkg.G_DBG_PROC_FUN_START);
4398:
4399: -- Call mth_run_log_pre_load

Line 4397: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP start', mth_util_pkg.G_DBG_PROC_FUN_START);

4393: -- Initialize default parameters
4394: v_log_to_date := sysdate;
4395: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4396:
4397: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP start', mth_util_pkg.G_DBG_PROC_FUN_START);
4398:
4399: -- Call mth_run_log_pre_load
4400: mth_util_pkg.mth_run_log_pre_load('MTH_ITEM_UA_D',v_unassigned_val,NULL,NULL,0,v_log_to_date);
4401:

Line 4400: mth_util_pkg.mth_run_log_pre_load('MTH_ITEM_UA_D',v_unassigned_val,NULL,NULL,0,v_log_to_date);

4396:
4397: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP start', mth_util_pkg.G_DBG_PROC_FUN_START);
4398:
4399: -- Call mth_run_log_pre_load
4400: mth_util_pkg.mth_run_log_pre_load('MTH_ITEM_UA_D',v_unassigned_val,NULL,NULL,0,v_log_to_date);
4401:
4402: -- Call GET_RUN_LOG_DATES
4403: mth_util_pkg.GET_RUN_LOG_DATES('MTH_ITEM_UA_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
4404:

Line 4403: mth_util_pkg.GET_RUN_LOG_DATES('MTH_ITEM_UA_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);

4399: -- Call mth_run_log_pre_load
4400: mth_util_pkg.mth_run_log_pre_load('MTH_ITEM_UA_D',v_unassigned_val,NULL,NULL,0,v_log_to_date);
4401:
4402: -- Call GET_RUN_LOG_DATES
4403: mth_util_pkg.GET_RUN_LOG_DATES('MTH_ITEM_UA_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
4404:
4405: INSERT INTO MTH_ITEM_HIERARCHY (HIERARCHY_ID,
4406: LEVEL_FK_KEY,
4407: PARENT_FK_KEY,

Line 4424: MTH_UTIL_PKG.MTH_UA_GET_MEANING()

4420: v_log_to_date,
4421: v_unassigned_val,
4422: v_unassigned_val,
4423: UN_REC.LEVEL_NAME,
4424: MTH_UTIL_PKG.MTH_UA_GET_MEANING()
4425: FROM (SELECT MDM.HIERARCHY_ID HIERARCHY_ID,
4426: MID.ITEM_PK_KEY LEVEL_FK_KEY,
4427: MID.ITEM_NAME LEVEL_NAME
4428: FROM

Line 4434: MID.ITEM_PK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() AND

4430: MTH_SYSTEMS_SETUP MSS,
4431: MTH_DIM_HIERARCHY MDM
4432: WHERE MID.CREATION_DATE >v_log_from_date AND
4433: MID.CREATION_DATE <= v_log_to_date AND
4434: MID.ITEM_PK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() AND
4435: MID.SYSTEM_FK_KEY = MSS.SYSTEM_PK_KEY AND
4436: MDM.DIMENSION_NAME ='ITEM'
4437: MINUS
4438: SELECT MIH.HIERARCHY_ID,

Line 4445: mth_util_pkg.log_msg('Number of rows inserted in MTH_ITEM_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

4441: FROM
4442: MTH_ITEM_HIERARCHY MIH )UN_REC
4443: )
4444: ;
4445: mth_util_pkg.log_msg('Number of rows inserted in MTH_ITEM_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4446:
4447: ----Call mth_run_log_post_load
4448: mth_util_pkg.mth_run_log_post_load('MTH_ITEM_UA_D',v_unassigned_val);
4449: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);

Line 4448: mth_util_pkg.mth_run_log_post_load('MTH_ITEM_UA_D',v_unassigned_val);

4444: ;
4445: mth_util_pkg.log_msg('Number of rows inserted in MTH_ITEM_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4446:
4447: ----Call mth_run_log_post_load
4448: mth_util_pkg.mth_run_log_post_load('MTH_ITEM_UA_D',v_unassigned_val);
4449: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);
4450:
4451: COMMIT;
4452: EXCEPTION

Line 4449: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);

4445: mth_util_pkg.log_msg('Number of rows inserted in MTH_ITEM_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4446:
4447: ----Call mth_run_log_post_load
4448: mth_util_pkg.mth_run_log_post_load('MTH_ITEM_UA_D',v_unassigned_val);
4449: mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);
4450:
4451: COMMIT;
4452: EXCEPTION
4453: WHEN OTHERS THEN

Line 4454: mth_util_pkg.log_msg('Exception OTHERS in MTH_ITEM_HRCHY_UNASSIGNED_MAP', mth_util_pkg.G_DBG_EXCEPTION);

4450:
4451: COMMIT;
4452: EXCEPTION
4453: WHEN OTHERS THEN
4454: mth_util_pkg.log_msg('Exception OTHERS in MTH_ITEM_HRCHY_UNASSIGNED_MAP', mth_util_pkg.G_DBG_EXCEPTION);
4455: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
4456: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
4457: ROLLBACK;
4458: RAISE;

Line 4455: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);

4451: COMMIT;
4452: EXCEPTION
4453: WHEN OTHERS THEN
4454: mth_util_pkg.log_msg('Exception OTHERS in MTH_ITEM_HRCHY_UNASSIGNED_MAP', mth_util_pkg.G_DBG_EXCEPTION);
4455: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
4456: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
4457: ROLLBACK;
4458: RAISE;
4459:

Line 4456: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);

4452: EXCEPTION
4453: WHEN OTHERS THEN
4454: mth_util_pkg.log_msg('Exception OTHERS in MTH_ITEM_HRCHY_UNASSIGNED_MAP', mth_util_pkg.G_DBG_EXCEPTION);
4455: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
4456: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
4457: ROLLBACK;
4458: RAISE;
4459:
4460: END MTH_ITEM_HRCHY_UNASSIGNED_MAP;