[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Procedure :UPDATE_ITEM_HIER_LVL_IN_DIM_HR *
* Description :This procedure updates the number of levels in dim hierarchy *
* for item hierarchies existed in item denorm table *
* File Name :MTHITEMDB.PLS *
* Visibility :Private *
* Parameters : *
* Modification log : *
* Author Date Change *
* Yong Feng 03-July--2012 Initial Creation *
**************************************************************************** */
PROCEDURE UPDATE_ITEM_HIER_LVL_IN_DIM_HR
IS
BEGIN
UPDATE MTH_DIM_HIERARCHY DH
SET dh.last_update_date = SYSDATE,
DH.NUMBER_OF_LEVEL =
(SELECT
Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
AS num_levels
FROM MTH_ITEM_DENORM_D ID
WHERE id.LEVEL_num = 10 and
ID.hierarchy_id = DH.hierarchy_id )
WHERE DH.NUMBER_OF_LEVEL IS NULL AND
DH.dimension_name= 'ITEM' AND
EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
WHERE DH.dimension_name= 'ITEM' AND
ID.level_num = 10 AND
ID.item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
END UPDATE_ITEM_HIER_LVL_IN_DIM_HR;
SELECT ITEM_NAME into v_unassigned_item_name
from MTH_ITEMS_D
where item_PK_KEY = v_unassigned_key;
select CATEGORY_NAME into v_unassigned_category_name
from MTH_ITEM_CATEGORIES_D
where CATEGORY_PK_KEY = v_unassigned_key;
INSERT
INTO MTH_ITEM_DENORM_D
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
SELECT HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
ITEM_PK_KEY ITEM_FK_KEY,
MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
MAX(LEVEL_NUM) LEVEL_NUM,
MAX(ITEM_NAME) ITEM_NAME,
MAX(LEVEL1_NAME) LEVEL1_NAME,
MAX(LEVEL2_NAME) LEVEL2_NAME,
MAX(LEVEL3_NAME) LEVEL3_NAME,
MAX(LEVEL4_NAME) LEVEL4_NAME,
MAX(LEVEL5_NAME) LEVEL5_NAME,
MAX(LEVEL6_NAME) LEVEL6_NAME,
MAX(LEVEL7_NAME) LEVEL7_NAME,
MAX(LEVEL8_NAME) LEVEL8_NAME,
MAX(LEVEL9_NAME) LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
B.ITEM_PK_KEY,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL9_FK_KEY,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL8_FK_KEY,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL7_FK_KEY,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL6_FK_KEY,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL5_FK_KEY,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL4_FK_KEY,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL3_FK_KEY,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL2_FK_KEY,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL1_FK_KEY,
10 LEVEL_NUM,
B.ITEM_NAME,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_NAME
ELSE NULL
END) LEVEL9_NAME,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_NAME
ELSE NULL
END) LEVEL8_NAME,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_NAME
ELSE NULL
END) LEVEL7_NAME,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_NAME
ELSE NULL
END) LEVEL6_NAME,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_NAME
ELSE NULL
END) LEVEL5_NAME,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_NAME
ELSE NULL
END) LEVEL4_NAME,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_NAME
ELSE NULL
END) LEVEL3_NAME,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_NAME
ELSE NULL
END) LEVEL2_NAME,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_NAME
ELSE NULL
END) LEVEL1_NAME
FROM (SELECT LEVEL LEVEL_NUM,
ITEM_PK_KEY,
LEVEL_FK_KEY,
LEVEL_NAME,
PARENT_FK_KEY,
PARENT_NAME,
SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
HIERARCHY_ID
FROM MTH_ITEM_HIERARCHY,
MTH_ITEMS_D B
WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
START WITH B.ITEM_PK_KEY IS NOT NULL
and MTH_ITEM_HIERARCHY.level_fk_key<> v_unassigned_key
AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE > v_log_from_date
AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE <= v_log_to_date
CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
(SELECT ITEM_PK_KEY, ITEM_NAME
FROM MTH_ITEMS_D) B
WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
--The granuality level is item and this would be done
-- for all the hierarchies
GROUP BY HIERARCHY_ID,ITEM_PK_KEY
UNION
select mdh.hierarchy_id,
MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
,v_unassigned_key level1_level_key
,v_unassigned_key level2_level_key
,v_unassigned_key level3_level_key
,v_unassigned_key level4_level_key
,v_unassigned_key level5_level_key
,v_unassigned_key level6_level_key
,v_unassigned_key level7_level_key
,v_unassigned_key level8_level_key
,v_unassigned_key level9_level_key
,10 level_num
,v_unassigned_item_name item_name
,v_unassigned_category_name level1_name
,v_unassigned_category_name level2_name
,v_unassigned_category_name level3_name
,v_unassigned_category_name level4_name
,v_unassigned_category_name level5_name
,v_unassigned_category_name level6_name
,v_unassigned_category_name level7_name
,v_unassigned_category_name level8_name
,v_unassigned_category_name level9_name
from dual,
mth_dim_hierarchy mdh,
(select distinct hierarchy_id
from mth_dim_level_lookup) mdll
where mdh.dimension_name= 'ITEM' and
mdll.hierarchy_id (+) = mdh.hierarchy_id);
UPDATE MTH_ITEM_DENORM_D
SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
level1_name = decode(level1_fk_key, NULL, v_unassigned_category_name, level1_name),
level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
level2_name = Decode(level2_fk_key, NULL, v_unassigned_category_name, level2_name),
level3_fk_key = nvl(level3_fk_key, v_unassigned_key),
level3_name = decode(level3_fk_key, NULL, v_unassigned_category_name, level3_name),
level4_fk_key = nvl(level4_fk_key, v_unassigned_key),
level4_name = decode(level4_fk_key, NULL, v_unassigned_category_name, level4_name),
level5_fk_key = nvl(level5_fk_key, v_unassigned_key),
level5_name = decode(level5_fk_key, NULL, v_unassigned_category_name, level5_name),
level6_fk_key = nvl(level6_fk_key, v_unassigned_key),
level6_name = decode(level6_fk_key, NULL, v_unassigned_category_name, level6_name),
level7_fk_key = nvl(level7_fk_key, v_unassigned_key),
level7_name = decode(level7_fk_key, NULL, v_unassigned_category_name, level7_name),
level8_fk_key = nvl(level8_fk_key, v_unassigned_key),
level8_name = decode(level8_fk_key, NULL, v_unassigned_category_name, level8_name),
level9_fk_key = nvl(level9_fk_key, v_unassigned_key),
level9_name = decode(level9_fk_key, NULL, v_unassigned_category_name, level9_name);
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
9,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
From mth_item_denorm_d
Where LEVEL9_FK_KEY is not null and level_NUM = 10;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
NULL,
8,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
NULL
From mth_item_denorm_d
Where LEVEL8_FK_KEY is not null and level_NUM = 9;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
NULL,
NULL,
7,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL7_FK_KEY is not null and level_NUM = 8;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
NULL,
NULL,
NULL,
6,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL6_FK_KEY is not null and level_NUM = 7;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
NULL,
NULL,
NULL,
NULL,
5,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL5_FK_KEY is not null and level_NUM = 6;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
4,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL4_FK_KEY is not null and level_NUM = 5;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
3,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL3_FK_KEY is not null and level_NUM = 4;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
2,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL2_FK_KEY is not null and level_NUM = 3;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
null,
LEVEL1_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
1,
null,
LEVEL1_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL1_FK_KEY is not null and level_NUM = 2;
UPDATE MTH_DIM_HIERARCHY
SET NUMBER_OF_LEVEL = NULL
WHERE dimension_name = 'ITEM';
UPDATE_ITEM_HIER_LVL_IN_DIM_HR();
OPEN item_cur FOR 'SELECT --select for the newe levels
level9_fk_key,hierarchy_id,item_fk_key,
Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
level6_fk_key_new,
Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
level9_fk_key) level5_fk_key_new,
Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
level8_fk_key,level9_fk_key) level4_fk_key_new,
Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
level2_fk_key_new,
Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
level9_fk_key) level1_fk_key_new,
level9_name,
Decode(diff_level,1,level8_name,level9_name) level7_name_new,
Decode(diff_level,1,level7_name,2,level8_name,level9_name)
level6_name_new,
Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
level9_name) level5_name_new,
Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
level8_name,level9_name) level4_name_new,
Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
level7_name,5,level8_name,level9_name) level3_name_new,
Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
level6_name,5,level7_name,6,level8_name,level9_name)
level2_name_new,
Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
level5_name,5,level6_name,6,level7_name,7,level8_name,
level9_name) level1_name_new
from
(--select the levels to be balanced
SELECT hierarchy_id ,item_fk_key,
level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
level1_fk_key,
level9_name,level8_name,level7_name,level6_name,
level5_name,level4_name,level3_name,level2_name,
level1_name,
max_level-c_level diff_level
FROM
(
SELECT hierarchy_id ,item_fk_key,
level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
level1_fk_key,
level9_name,level8_name,level7_name,level6_name,
level5_name,level4_name,level3_name,level2_name,
level1_name,
decode(level9_fk_key,NULL,0,1) +
decode(level8_fk_key,NULL,0,1) +
decode(level7_fk_key,NULL,0,1) +
decode(level6_fk_key,NULL,0,1) +
decode(level5_fk_key,NULL,0,1) +
decode(level4_fk_key,NULL,0,1) +
decode(level3_fk_key,NULL,0,1) +
decode(level2_fk_key,NULL,0,1) +
decode(level1_fk_key,NULL,0,1) c_level,--current level
Max(decode(level9_fk_key,NULL,0,1) +
decode(level8_fk_key,NULL,0,1) +
decode(level7_fk_key,NULL,0,1) +
decode(level6_fk_key,NULL,0,1) +
decode(level5_fk_key,NULL,0,1) +
decode(level4_fk_key,NULL,0,1) +
decode(level3_fk_key,NULL,0,1) +
decode(level2_fk_key,NULL,0,1) +
decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
max_level--maximum level in the hierarchy
FROM MTH_ITEM_DENORM_D_TMP
WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
)
WHERE c_level
--bulk update using forall
FORALL i IN
denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
UPDATE MTH_ITEM_DENORM_D_TMP
SET
level8_fk_key = denorm_rec.level9_fk_key(i),
level7_fk_key = denorm_rec.level7_fk_key(i),
level6_fk_key = denorm_rec.level6_fk_key(i),
level5_fk_key = denorm_rec.level5_fk_key(i),
level4_fk_key = denorm_rec.level4_fk_key(i),
level3_fk_key = denorm_rec.level3_fk_key(i),
level2_fk_key = denorm_rec.level2_fk_key(i),
level1_fk_key = denorm_rec.level1_fk_key(i),
level8_name = denorm_rec.level9_name(i),
level7_name = denorm_rec.level7_name(i),
level6_name = denorm_rec.level6_name(i),
level5_name = denorm_rec.level5_name(i),
level4_name = denorm_rec.level4_name(i),
level3_name = denorm_rec.level3_name(i),
level2_name = denorm_rec.level2_name(i),
level1_name = denorm_rec.level1_name(i)
WHERE
item_fk_key = denorm_rec.baselevel_fk_key(i)
AND hierarchy_id= denorm_rec.hierarchy_id(i);
SELECT ITEM_NAME into v_unassigned_item_name
from MTH_ITEMS_D
where item_PK_KEY = v_unassigned_key;
select CATEGORY_NAME into v_unassigned_category_name
from MTH_ITEM_CATEGORIES_D
where CATEGORY_PK_KEY = v_unassigned_key;
INSERT
INTO MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
SELECT HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
ITEM_PK_KEY ITEM_FK_KEY,
MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
MAX(LEVEL_NUM) LEVEL_NUM,
MAX(ITEM_NAME) ITEM_NAME,
MAX(LEVEL1_NAME) LEVEL1_NAME,
MAX(LEVEL2_NAME) LEVEL2_NAME,
MAX(LEVEL3_NAME) LEVEL3_NAME,
MAX(LEVEL4_NAME) LEVEL4_NAME,
MAX(LEVEL5_NAME) LEVEL5_NAME,
MAX(LEVEL6_NAME) LEVEL6_NAME,
MAX(LEVEL7_NAME) LEVEL7_NAME,
MAX(LEVEL8_NAME) LEVEL8_NAME,
MAX(LEVEL9_NAME) LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
B.ITEM_PK_KEY,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL9_FK_KEY,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL8_FK_KEY,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL7_FK_KEY,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL6_FK_KEY,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL5_FK_KEY,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL4_FK_KEY,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL3_FK_KEY,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL2_FK_KEY,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL1_FK_KEY,
10 LEVEL_NUM,
B.ITEM_NAME,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_NAME
ELSE NULL
END) LEVEL9_NAME,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_NAME
ELSE NULL
END) LEVEL8_NAME,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_NAME
ELSE NULL
END) LEVEL7_NAME,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_NAME
ELSE NULL
END) LEVEL6_NAME,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_NAME
ELSE NULL
END) LEVEL5_NAME,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_NAME
ELSE NULL
END) LEVEL4_NAME,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_NAME
ELSE NULL
END) LEVEL3_NAME,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_NAME
ELSE NULL
END) LEVEL2_NAME,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_NAME
ELSE NULL
END) LEVEL1_NAME
FROM (SELECT LEVEL LEVEL_NUM,
ITEM_PK_KEY,
LEVEL_FK_KEY,
LEVEL_NAME,
PARENT_FK_KEY,
PARENT_NAME,
SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
HIERARCHY_ID
FROM MTH_ITEM_HIERARCHY,
MTH_ITEMS_D B
WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
START WITH B.ITEM_PK_KEY IS NOT NULL
CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
(SELECT ITEM_PK_KEY, ITEM_NAME
FROM MTH_ITEMS_D) B
WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
--The granuality level is item and this would be done
-- for all the hierarchies
GROUP BY HIERARCHY_ID,ITEM_PK_KEY
UNION
select mdh.hierarchy_id,
MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
,v_unassigned_key level1_level_key
,v_unassigned_key level2_level_key
,v_unassigned_key level3_level_key
,v_unassigned_key level4_level_key
,v_unassigned_key level5_level_key
,v_unassigned_key level6_level_key
,v_unassigned_key level7_level_key
,v_unassigned_key level8_level_key
,v_unassigned_key level9_level_key
,10 level_num
,v_unassigned_item_name item_name
,v_unassigned_category_name level1_name
,v_unassigned_category_name level2_name
,v_unassigned_category_name level3_name
,v_unassigned_category_name level4_name
,v_unassigned_category_name level5_name
,v_unassigned_category_name level6_name
,v_unassigned_category_name level7_name
,v_unassigned_category_name level8_name
,v_unassigned_category_name level9_name
from dual,
mth_dim_hierarchy mdh,
(select distinct hierarchy_id
from mth_dim_level_lookup) mdll
where mdh.dimension_name= 'ITEM' and
mdll.hierarchy_id (+) = mdh.hierarchy_id);
UPDATE MTH.MTH_ITEM_DENORM_D_TMP
SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
level1_name = decode(level1_fk_key, NULL, v_unassigned_category_name, level1_name),
level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
level2_name = Decode(level2_fk_key, NULL, v_unassigned_category_name, level2_name),
level3_fk_key = nvl(level3_fk_key, v_unassigned_key),
level3_name = decode(level3_fk_key, NULL, v_unassigned_category_name, level3_name),
level4_fk_key = nvl(level4_fk_key, v_unassigned_key),
level4_name = decode(level4_fk_key, NULL, v_unassigned_category_name, level4_name),
level5_fk_key = nvl(level5_fk_key, v_unassigned_key),
level5_name = decode(level5_fk_key, NULL, v_unassigned_category_name, level5_name),
level6_fk_key = nvl(level6_fk_key, v_unassigned_key),
level6_name = decode(level6_fk_key, NULL, v_unassigned_category_name, level6_name),
level7_fk_key = nvl(level7_fk_key, v_unassigned_key),
level7_name = decode(level7_fk_key, NULL, v_unassigned_category_name, level7_name),
level8_fk_key = nvl(level8_fk_key, v_unassigned_key),
level8_name = decode(level8_fk_key, NULL, v_unassigned_category_name, level8_name),
level9_fk_key = nvl(level9_fk_key, v_unassigned_key),
level9_name = decode(level9_fk_key, NULL, v_unassigned_category_name, level9_name);
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
9,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL9_FK_KEY is not null and level_NUM = 10;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
NULL,
8,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL8_FK_KEY is not null and level_NUM = 9;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
NULL,
NULL,
7,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL7_FK_KEY is not null and level_NUM = 8;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
NULL,
NULL,
NULL,
6,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
NULL,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL6_FK_KEY is not null and level_NUM = 7;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
NULL,
NULL,
NULL,
NULL,
5,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
NULL,
NULL,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL5_FK_KEY is not null and level_NUM = 6;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
4,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL4_FK_KEY is not null and level_NUM = 5;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
3,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL3_FK_KEY is not null and level_NUM = 4;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
2,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL2_FK_KEY is not null and level_NUM = 3;
Insert into MTH.MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
null,
LEVEL1_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
1,
null,
LEVEL1_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH.MTH_ITEM_DENORM_D_TMP
Where LEVEL1_FK_KEY is not null and level_NUM = 2;
UPDATE MTH_ITEM_HIERARCHY_STG A
SET LEVEL_NUM =
(SELECT LEVEL
FROM MTH_ITEM_HIERARCHY_STG B
WHERE A.LEVEL_FK = B.LEVEL_FK
AND A.HIERARCHY_NAME = B.HIERARCHY_NAME CONNECT BY PRIOR
B.LEVEL_FK = B.PARENT_FK
AND PRIOR B.HIERARCHY_NAME = A.HIERARCHY_NAME START
WITH B.PARENT_FK IS NULL
);
* updated item category entries in the item hierarchy table *
* File Name :MTHITEMDB.PLS *
* Visibility :private *
* Parameters : *
* Modification log : *
* Author Date Change *
* 30-June--2012 Initial Creation *
**************************************************************************** */
FUNCTION load_items_from_changed_cats (p_log_from_date IN DATE,
p_log_to_date IN DATE,
p_unassigned_key IN NUMBER) RETURN NUMBER
IS
BEGIN
INSERT
INTO MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
SELECT HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
ITEM_PK_KEY ITEM_FK_KEY,
MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
MAX(LEVEL_NUM) LEVEL_NUM,
MAX(ITEM_NAME) ITEM_NAME,
MAX(LEVEL1_NAME) LEVEL1_NAME,
MAX(LEVEL2_NAME) LEVEL2_NAME,
MAX(LEVEL3_NAME) LEVEL3_NAME,
MAX(LEVEL4_NAME) LEVEL4_NAME,
MAX(LEVEL5_NAME) LEVEL5_NAME,
MAX(LEVEL6_NAME) LEVEL6_NAME,
MAX(LEVEL7_NAME) LEVEL7_NAME,
MAX(LEVEL8_NAME) LEVEL8_NAME,
MAX(LEVEL9_NAME) LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
B.ITEM_PK_KEY,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL9_FK_KEY,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL8_FK_KEY,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL7_FK_KEY,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL6_FK_KEY,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL5_FK_KEY,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL4_FK_KEY,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL3_FK_KEY,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL2_FK_KEY,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL1_FK_KEY,
10 LEVEL_NUM,
B.ITEM_NAME,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_NAME
ELSE NULL
END) LEVEL9_NAME,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_NAME
ELSE NULL
END) LEVEL8_NAME,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_NAME
ELSE NULL
END) LEVEL7_NAME,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_NAME
ELSE NULL
END) LEVEL6_NAME,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_NAME
ELSE NULL
END) LEVEL5_NAME,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_NAME
ELSE NULL
END) LEVEL4_NAME,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_NAME
ELSE NULL
END) LEVEL3_NAME,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_NAME
ELSE NULL
END) LEVEL2_NAME,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_NAME
ELSE NULL
END) LEVEL1_NAME
FROM (SELECT LEVEL LEVEL_NUM,
ITEM_PK_KEY,
LEVEL_FK_KEY,
LEVEL_NAME,
PARENT_FK_KEY,
PARENT_NAME,
SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
HIERARCHY_ID
FROM MTH_ITEM_HIERARCHY,
MTH_ITEMS_D B
WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
START WITH B.ITEM_PK_KEY IS NOT NULL AND
B.ITEM_PK_KEY IN
(SELECT c.item_fk_key
FROM mth_item_hierarchy a ,
mth_item_categories_d bb,
mth_item_denorm_d c
WHERE (a.level_fk_key = c.level9_fk_key OR
a.level_fk_key = c.level8_fk_key OR
a.level_fk_key = c.level7_fk_key OR
a.level_fk_key = c.level6_fk_key OR
a.level_fk_key = c.level5_fk_key OR
a.level_fk_key = c.level4_fk_key OR
a.level_fk_key = c.level3_fk_key OR
a.level_fk_key = c.level2_fk_key OR
a.level_fk_key = c.level1_fk_key) AND
c.level_num = 10 AND
c.item_fk_key <> p_unassigned_key AND
a.LEVEL_FK_KEY <> p_unassigned_key AND
a.hierarchy_id = c.hierarchy_id AND
a.LEVEL_FK_KEY = Bb.CATEGORY_PK_KEY AND
a.LAST_UPDATE_DATE > p_log_from_date AND
a.LAST_UPDATE_DATE <= p_log_to_date)
CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
(SELECT ITEM_PK_KEY, ITEM_NAME
FROM MTH_ITEMS_D) B
WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
GROUP BY HIERARCHY_ID,ITEM_PK_KEY ) ;
* for new or newly updated items in item hierarchy since last run *
* File Name :MTHITEMDB.PLS *
* Visibility :private *
* Parameters : *
* Modification log : *
* Author Date Change *
* 30-June--2012 Initial Creation *
**************************************************************************** */
FUNCTION load_new_items_to_item_dnm_tmp(p_log_from_date IN DATE,
p_log_to_date IN DATE ) RETURN NUMBER
IS
v_unassigned_val VARCHAR2(30);
INSERT
INTO MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
SELECT HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
ITEM_PK_KEY ITEM_FK_KEY,
MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
MAX(LEVEL_NUM) LEVEL_NUM,
MAX(ITEM_NAME) ITEM_NAME,
MAX(LEVEL1_NAME) LEVEL1_NAME,
MAX(LEVEL2_NAME) LEVEL2_NAME,
MAX(LEVEL3_NAME) LEVEL3_NAME,
MAX(LEVEL4_NAME) LEVEL4_NAME,
MAX(LEVEL5_NAME) LEVEL5_NAME,
MAX(LEVEL6_NAME) LEVEL6_NAME,
MAX(LEVEL7_NAME) LEVEL7_NAME,
MAX(LEVEL8_NAME) LEVEL8_NAME,
MAX(LEVEL9_NAME) LEVEL9_NAME
FROM (SELECT HIERARCHY_ID,
B.ITEM_PK_KEY,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL9_FK_KEY,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL8_FK_KEY,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL7_FK_KEY,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL6_FK_KEY,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL5_FK_KEY,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL4_FK_KEY,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL3_FK_KEY,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL2_FK_KEY,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
ELSE NULL
END) LEVEL1_FK_KEY,
10 LEVEL_NUM,
B.ITEM_NAME,
(CASE
WHEN LEVEL_NUM = 1 THEN PARENT_NAME
ELSE NULL
END) LEVEL9_NAME,
(CASE
WHEN LEVEL_NUM = 2 THEN PARENT_NAME
ELSE NULL
END) LEVEL8_NAME,
(CASE
WHEN LEVEL_NUM = 3 THEN PARENT_NAME
ELSE NULL
END) LEVEL7_NAME,
(CASE
WHEN LEVEL_NUM = 4 THEN PARENT_NAME
ELSE NULL
END) LEVEL6_NAME,
(CASE
WHEN LEVEL_NUM = 5 THEN PARENT_NAME
ELSE NULL
END) LEVEL5_NAME,
(CASE
WHEN LEVEL_NUM = 6 THEN PARENT_NAME
ELSE NULL
END) LEVEL4_NAME,
(CASE
WHEN LEVEL_NUM = 7 THEN PARENT_NAME
ELSE NULL
END) LEVEL3_NAME,
(CASE
WHEN LEVEL_NUM = 8 THEN PARENT_NAME
ELSE NULL
END) LEVEL2_NAME,
(CASE
WHEN LEVEL_NUM = 9 THEN PARENT_NAME
ELSE NULL
END) LEVEL1_NAME
FROM (SELECT LEVEL LEVEL_NUM,
ITEM_PK_KEY,
LEVEL_FK_KEY,
LEVEL_NAME,
PARENT_FK_KEY,
PARENT_NAME,
SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
HIERARCHY_ID
FROM MTH_ITEM_HIERARCHY,
MTH_ITEMS_D B
WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
START WITH B.ITEM_PK_KEY IS NOT NULL
and MTH_ITEM_HIERARCHY.level_fk_key<> v_unassigned_val
AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE > p_log_from_date
AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE <= p_log_to_date
CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
(SELECT ITEM_PK_KEY, ITEM_NAME
FROM MTH_ITEMS_D) B
WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
GROUP BY HIERARCHY_ID,ITEM_PK_KEY );
* Description :This procedure is used to update the denorm table *
* tmp table to change the level fk key and corresponding level name *
* from NULL to -99999, and 'Unassigned' *
* File Name :MTHITEMDB.PLS *
* Visibility :Private *
* Parameters : *
* Modification log : *
* Author Date Change *
* 30-June--2012 Initial Creation *
**************************************************************************** */
PROCEDURE UPD_ITEM_DENORM_TMP_UNASSIGNED(p_unassigned_key IN NUMBER,
p_unassigned_category_name IN VARCHAR2)
IS
BEGIN
UPDATE MTH.MTH_ITEM_DENORM_D_TMP
SET level1_fk_key = nvl(level1_fk_key, p_unassigned_key),
level1_name = decode(level1_fk_key, NULL, p_unassigned_category_name, level1_name),
level2_fk_key = nvl(level2_fk_key, p_unassigned_key),
level2_name = Decode(level2_fk_key, NULL, p_unassigned_category_name, level2_name),
level3_fk_key = nvl(level3_fk_key, p_unassigned_key),
level3_name = decode(level3_fk_key, NULL, p_unassigned_category_name, level3_name),
level4_fk_key = nvl(level4_fk_key, p_unassigned_key),
level4_name = decode(level4_fk_key, NULL, p_unassigned_category_name, level4_name),
level5_fk_key = nvl(level5_fk_key, p_unassigned_key),
level5_name = decode(level5_fk_key, NULL, p_unassigned_category_name, level5_name),
level6_fk_key = nvl(level6_fk_key, p_unassigned_key),
level6_name = decode(level6_fk_key, NULL, p_unassigned_category_name, level6_name),
level7_fk_key = nvl(level7_fk_key, p_unassigned_key),
level7_name = decode(level7_fk_key, NULL, p_unassigned_category_name, level7_name),
level8_fk_key = nvl(level8_fk_key, p_unassigned_key),
level8_name = decode(level8_fk_key, NULL, p_unassigned_category_name, level8_name),
level9_fk_key = nvl(level9_fk_key, p_unassigned_key),
level9_name = decode(level9_fk_key, NULL, p_unassigned_category_name, level9_name);
SELECT --select for the newe levels
level9_fk_key,hierarchy_id,item_fk_key,
Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
level6_fk_key_new,
Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
level9_fk_key) level5_fk_key_new,
Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
level8_fk_key,level9_fk_key) level4_fk_key_new,
Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
level2_fk_key_new,
Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
level9_fk_key) level1_fk_key_new,
level9_name,
Decode(diff_level,1,level8_name,level9_name) level7_name_new,
Decode(diff_level,1,level7_name,2,level8_name,level9_name)
level6_name_new,
Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
level9_name) level5_name_new,
Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
level8_name,level9_name) level4_name_new,
Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
level7_name,5,level8_name,level9_name) level3_name_new,
Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
level6_name,5,level7_name,6,level8_name,level9_name)
level2_name_new,
Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
level5_name,5,level6_name,6,level7_name,7,level8_name,
level9_name) level1_name_new
from
(--select the levels to be balanced
SELECT hierarchy_id ,item_fk_key,
level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
level1_fk_key,
level9_name,level8_name,level7_name,level6_name,
level5_name,level4_name,level3_name,level2_name,
level1_name,
p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
FROM MTH_ITEM_DENORM_D_TMP
WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
hierarchy_id = p_hierarchy_id AND
p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
level9_fk_key IS NOT NULL
);
--bulk update using forall
FORALL i IN
denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
UPDATE MTH_ITEM_DENORM_D_TMP
SET
level8_fk_key = denorm_rec.level9_fk_key(i),
level7_fk_key = denorm_rec.level7_fk_key(i),
level6_fk_key = denorm_rec.level6_fk_key(i),
level5_fk_key = denorm_rec.level5_fk_key(i),
level4_fk_key = denorm_rec.level4_fk_key(i),
level3_fk_key = denorm_rec.level3_fk_key(i),
level2_fk_key = denorm_rec.level2_fk_key(i),
level1_fk_key = denorm_rec.level1_fk_key(i),
level8_name = denorm_rec.level9_name(i),
level7_name = denorm_rec.level7_name(i),
level6_name = denorm_rec.level6_name(i),
level5_name = denorm_rec.level5_name(i),
level4_name = denorm_rec.level4_name(i),
level3_name = denorm_rec.level3_name(i),
level2_name = denorm_rec.level2_name(i),
level1_name = denorm_rec.level1_name(i)
WHERE
item_fk_key = denorm_rec.baselevel_fk_key(i)
AND hierarchy_id= denorm_rec.hierarchy_id(i);
SELECT --select for the newe levels
level9_fk_key,hierarchy_id,item_fk_key,
Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
level6_fk_key_new,
Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
level9_fk_key) level5_fk_key_new,
Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
level8_fk_key,level9_fk_key) level4_fk_key_new,
Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
level2_fk_key_new,
Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
level9_fk_key) level1_fk_key_new,
level9_name,
Decode(diff_level,1,level8_name,level9_name) level7_name_new,
Decode(diff_level,1,level7_name,2,level8_name,level9_name)
level6_name_new,
Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
level9_name) level5_name_new,
Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
level8_name,level9_name) level4_name_new,
Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
level7_name,5,level8_name,level9_name) level3_name_new,
Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
level6_name,5,level7_name,6,level8_name,level9_name)
level2_name_new,
Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
level5_name,5,level6_name,6,level7_name,7,level8_name,
level9_name) level1_name_new
from
(--select the levels to be balanced
SELECT hierarchy_id ,item_fk_key,
level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
level1_fk_key,
level9_name,level8_name,level7_name,level6_name,
level5_name,level4_name,level3_name,level2_name,
level1_name,
p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
FROM MTH_ITEM_DENORM_D
WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
hierarchy_id = p_hierarchy_id AND
p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
level9_fk_key IS NOT NULL
);
--bulk update using forall
FORALL i IN
denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
UPDATE MTH_ITEM_DENORM_D
SET
level8_fk_key = denorm_rec.level9_fk_key(i),
level7_fk_key = denorm_rec.level7_fk_key(i),
level6_fk_key = denorm_rec.level6_fk_key(i),
level5_fk_key = denorm_rec.level5_fk_key(i),
level4_fk_key = denorm_rec.level4_fk_key(i),
level3_fk_key = denorm_rec.level3_fk_key(i),
level2_fk_key = denorm_rec.level2_fk_key(i),
level1_fk_key = denorm_rec.level1_fk_key(i),
level8_name = denorm_rec.level9_name(i),
level7_name = denorm_rec.level7_name(i),
level6_name = denorm_rec.level6_name(i),
level5_name = denorm_rec.level5_name(i),
level4_name = denorm_rec.level4_name(i),
level3_name = denorm_rec.level3_name(i),
level2_name = denorm_rec.level2_name(i),
level1_name = denorm_rec.level1_name(i)
WHERE
item_fk_key = denorm_rec.baselevel_fk_key(i)
AND hierarchy_id= denorm_rec.hierarchy_id(i);
* Procedure :INSERT_UNASS_ITEM_DENORM_TMP *
* Description :This procedure inserts unassigned entry into *
* MTH_ITEM_DENORM_D_TMP when the hierarchy does not exist *
* in item denorm table *
* File Name :MTHITEMDB.PLS *
* Visibility :Private *
* Parameters : *
* Modification log : *
* Author Date Change *
* Yong Feng 03-July--2012 Initial Creation *
**************************************************************************** */
PROCEDURE INSERT_UNASS_ITEM_DENORM_TMP(p_hierarchy_id IN NUMBER,
p_unassigned_key IN NUMBER,
p_unassigned_item_name IN VARCHAR2,
p_unassigned_category_name IN VARCHAR2)
IS
BEGIN
INSERT INTO MTH_ITEM_DENORM_D_TMP
( HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY,
LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
LEVEL1_NAME )
VALUES ( p_hierarchy_id,
MTH_UTIL_PKG.MTH_UA_GET_VAL
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,p_unassigned_key
,10
,p_unassigned_item_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name
,p_unassigned_category_name);
END INSERT_UNASS_ITEM_DENORM_TMP;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
9,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
From mth_item_denorm_d
Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
NULL,
8,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
NULL
From mth_item_denorm_d
Where LEVEL8_FK_KEY is not null and level_NUM = 9 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
NULL,
NULL,
7,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL7_FK_KEY is not null and level_NUM = 8 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
NULL,
NULL,
NULL,
6,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL6_FK_KEY is not null and level_NUM = 7 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
NULL,
NULL,
NULL,
NULL,
5,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL5_FK_KEY is not null and level_NUM = 6 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
4,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL4_FK_KEY is not null and level_NUM = 5 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
3,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL3_FK_KEY is not null and level_NUM = 4 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
2,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL2_FK_KEY is not null and level_NUM = 3 AND
hierarchy_id = p_hierarchy_id;
Insert into mth_item_denorm_d
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
null,
LEVEL1_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
1,
null,
LEVEL1_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From mth_item_denorm_d
Where LEVEL1_FK_KEY is not null and level_NUM = 2 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
9,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
From MTH_ITEM_DENORM_D_TMP
Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
NULL,
8,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL8_FK_KEY is not null and level_NUM = 9 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
NULL,
NULL,
7,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL7_FK_KEY is not null and level_NUM = 8 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
NULL,
NULL,
NULL,
6,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
NULL,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL6_FK_KEY is not null and level_NUM = 7 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
NULL,
NULL,
NULL,
NULL,
5,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
NULL,
NULL,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL5_FK_KEY is not null and level_NUM = 6 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
4,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL4_FK_KEY is not null and level_NUM = 5 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
3,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL3_FK_KEY is not null and level_NUM = 4 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
NULL,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
2,
NULL,
LEVEL1_NAME,
LEVEL2_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL2_FK_KEY is not null and level_NUM = 3 AND
hierarchy_id = p_hierarchy_id;
Insert into MTH_ITEM_DENORM_D_TMP
(
HIERARCHY_ID,
ITEM_FK_KEY,
LEVEL1_FK_KEY,
LEVEL2_FK_KEY,
LEVEL3_FK_KEY,
LEVEL4_FK_KEY,
LEVEL5_FK_KEY,
LEVEL6_FK_KEY,
LEVEL7_FK_KEY,
LEVEL8_FK_KEY,
LEVEL9_FK_KEY,
LEVEL_NUM,
ITEM_NAME,
LEVEL1_NAME,
LEVEL2_NAME,
LEVEL3_NAME,
LEVEL4_NAME,
LEVEL5_NAME,
LEVEL6_NAME,
LEVEL7_NAME,
LEVEL8_NAME,
LEVEL9_NAME
)
select distinct
HIERARCHY_ID,
null,
LEVEL1_FK_KEY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
1,
null,
LEVEL1_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
From MTH_ITEM_DENORM_D_TMP
Where LEVEL1_FK_KEY is not null and level_NUM = 2 AND
hierarchy_id = p_hierarchy_id;
* Description :This procedure inserts level 1 to level 9 data in the item *
* denorm tmp table that do not exist in the item denorm table *
* for the specified hierarchy id *
* into the item denorm table *
* File Name :MTHITEMDB.PLS *
* Visibility :Private *
* Parameters : *
* Modification log : *
* Author Date Change *
* Yong Feng 30-June--2012 Initial Creation *
**************************************************************************** */
PROCEDURE insrt_lvl_1_9_to_item_denorm (p_hierarchy_id IN NUMBER)
IS
v_log_to_date DATE ;
INSERT INTO mth_item_denorm_d
(hierarchy_id,
item_fk_key,
level9_fk_key,
level8_fk_key,
level7_fk_key,
level6_fk_key,
level5_fk_key,
level4_fk_key,
level3_fk_key,
level2_fk_key,
level1_fk_key,
level_num,
item_name,
level9_name,
level8_name,
level7_name,
level6_name,
level5_name,
level4_name,
level3_name,
level2_name,
level1_name,
created_by,
last_updated_by,
last_update_login,
creation_date,
last_update_date)
SELECT tmp.hierarchy_id,
tmp.item_fk_key,
tmp.level9_fk_key,
tmp.level8_fk_key,
tmp.level7_fk_key,
tmp.level6_fk_key,
tmp.level5_fk_key,
tmp.level4_fk_key,
tmp.level3_fk_key,
tmp.level2_fk_key,
tmp.level1_fk_key,
tmp.level_num,
tmp.item_name,
tmp.level9_name,
tmp.level8_name,
tmp.level7_name,
tmp.level6_name,
tmp.level5_name,
tmp.level4_name,
tmp.level3_name,
tmp.level2_name,
tmp.level1_name,
v_unassigned_val,
v_unassigned_val,
v_unassigned_val,
v_log_to_date,
v_log_to_date
FROM mth_item_denorm_d_tmp tmp
WHERE tmp.level_num < 10 AND
tmp.hierarchy_id = p_hierarchy_id AND
NOT EXISTS
(SELECT 1
FROM mth_item_denorm_d mid
WHERE mid.hierarchy_id = tmp.hierarchy_id AND
((mid.level_num = 9 AND tmp.level_num = 9 AND mid.level9_fk_key = tmp.level9_fk_key)
OR (mid.level_num = 8 AND tmp.level_num = 8 AND mid.level8_fk_key = tmp.level8_fk_key)
OR (mid.level_num = 7 AND tmp.level_num = 7 AND mid.level7_fk_key = tmp.level7_fk_key)
OR (mid.level_num = 6 AND tmp.level_num = 6 AND mid.level6_fk_key = tmp.level6_fk_key)
OR (mid.level_num = 5 AND tmp.level_num = 5 AND mid.level5_fk_key = tmp.level5_fk_key)
OR (mid.level_num = 4 AND tmp.level_num = 4 AND mid.level4_fk_key = tmp.level4_fk_key)
OR (mid.level_num = 3 AND tmp.level_num = 3 AND mid.level3_fk_key = tmp.level3_fk_key)
OR (mid.level_num = 2 AND tmp.level_num = 2 AND mid.level2_fk_key = tmp.level2_fk_key)
OR (mid.level_num = 1 AND tmp.level_num = 1 AND mid.level1_fk_key = tmp.level1_fk_key)) ) ;
Merge INTO mth_item_denorm_d mid USING (SELECT hierarchy_id,
item_fk_key,
level9_fk_key,
level8_fk_key,
level7_fk_key,
level6_fk_key,
level5_fk_key,
level4_fk_key,
level3_fk_key,
level2_fk_key,
level1_fk_key,
level_num,
item_name,
level9_name,
level8_name,
level7_name,
level6_name,
level5_name,
level4_name,
level3_name,
level2_name,
level1_name
FROM (SELECT * FROM mth_item_denorm_d_tmp WHERE level_num < 10 )) tmp
ON (mid.hierarchy_id = p_hierarchy_id AND tmp.hierarchy_id = mid.hierarchy_id
AND ((mid.level_num = 9 AND tmp.level_num = 9 AND mid.level9_fk_key = tmp.level9_fk_key)
OR (mid.level_num = 8 AND tmp.level_num = 8 AND mid.level8_fk_key = tmp.level8_fk_key)
OR (mid.level_num = 7 AND tmp.level_num = 7 AND mid.level7_fk_key = tmp.level7_fk_key)
OR (mid.level_num = 6 AND tmp.level_num = 6 AND mid.level6_fk_key = tmp.level6_fk_key)
OR (mid.level_num = 5 AND tmp.level_num = 5 AND mid.level5_fk_key = tmp.level5_fk_key)
OR (mid.level_num = 4 AND tmp.level_num = 4 AND mid.level4_fk_key = tmp.level4_fk_key)
OR (mid.level_num = 3 AND tmp.level_num = 3 AND mid.level3_fk_key = tmp.level3_fk_key)
OR (mid.level_num = 2 AND tmp.level_num = 2 AND mid.level2_fk_key = tmp.level2_fk_key)
OR (mid.level_num = 1 AND tmp.level_num = 1 AND mid.level1_fk_key = tmp.level1_fk_key)) )
WHEN MATCHED THEN
UPDATE SET
mid.last_updated_by = mid.last_updated_by
WHEN NOT MATCHED THEN
INSERT (
mid.hierarchy_id,
mid.item_fk_key,
mid.level9_fk_key,
mid.level8_fk_key,
mid.level7_fk_key,
mid.level6_fk_key,
mid.level5_fk_key,
mid.level4_fk_key,
mid.level3_fk_key,
mid.level2_fk_key,
mid.level1_fk_key,
mid.level_num,
mid.item_name,
mid.level9_name,
mid.level8_name,
mid.level7_name,
mid.level6_name,
mid.level5_name,
mid.level4_name,
mid.level3_name,
mid.level2_name,
mid.level1_name,
mid.created_by,
mid.last_updated_by,
mid.last_update_login,
mid.creation_date,
mid.last_update_date)
VALUES (
tmp.hierarchy_id,
tmp.item_fk_key,
tmp.level9_fk_key,
tmp.level8_fk_key,
tmp.level7_fk_key,
tmp.level6_fk_key,
tmp.level5_fk_key,
tmp.level4_fk_key,
tmp.level3_fk_key,
tmp.level2_fk_key,
tmp.level1_fk_key,
tmp.level_num,
tmp.item_name,
tmp.level9_name,
tmp.level8_name,
tmp.level7_name,
tmp.level6_name,
tmp.level5_name,
tmp.level4_name,
tmp.level3_name,
tmp.level2_name,
tmp.level1_name,
v_unassigned_val,
v_unassigned_val,
v_unassigned_val,
v_log_to_date,
v_log_to_date) ;
v_num_rows_deleted BOOLEAN := FALSE;
SELECT id.hierarchy_id, mdh.NUMBER_OF_LEVEL,
id.max_level, id.min_level
FROM (
SELECT hierarchy_id,
Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
FROM MTH_ITEM_DENORM_D_TMP
WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL
GROUP BY hierarchy_id) id,
mth_dim_hierarchy mdh
WHERE id.hierarchy_id = mdh.hierarchy_id;
SELECT ITEM_NAME into v_unassigned_item_name
from MTH_ITEMS_D
where item_PK_KEY = v_unassigned_key;
select CATEGORY_NAME into v_unassigned_category_name
from MTH_ITEM_CATEGORIES_D
where CATEGORY_PK_KEY = v_unassigned_key;
SELECT 1 INTO v_num_rows FROM mth_item_denorm_d WHERE ROWNUM = 1;
UPDATE_ITEM_HIER_LVL_IN_DIM_HR();
UPDATE MTH_DIM_HIERARCHY DH
SET dh.last_update_date = SYSDATE,
DH.NUMBER_OF_LEVEL =
-- (SELECT num_levels
-- FROM
(SELECT
decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
AS num_levels
FROM MTH_ITEM_DENORM_D ID
WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 )
-- WHERE ROWNUM = 1)
WHERE (DH.NUMBER_OF_LEVEL IS NULL OR DH.NUMBER_OF_LEVEL = 0) AND
DH.dimension_name= 'ITEM' AND
EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
DH.dimension_name= 'ITEM' AND
ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
SELECT
Max(decode(level9_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level8_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level7_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level6_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level5_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level4_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level3_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level2_fk_key,v_unassigned_key,0,NULL,0,1) +
decode(level1_fk_key,v_unassigned_key,0,NULL,0,1))
INTO v_num_levels
FROM MTH_ITEM_DENORM_D
WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
level_num = 10 AND
hierarchy_id = r_hid_and_levels.hierarchy_id;
v_num_rows_deleted := FALSE;
DELETE FROM mth_item_denorm_d id
WHERE id.hierarchy_id = r_hid_and_levels.hierarchy_id AND
level_num = 10 AND
item_fk_key IN (SELECT idt.item_fk_key
FROM mth_item_denorm_d_tmp idt
WHERE idt.hierarchy_id = id.hierarchy_id AND
idt.level_num = 10);
v_num_rows_deleted := (SQL%ROWCOUNT > 0);
IF v_num_levels IS NULL AND NOT v_num_rows_deleted THEN
-- Double check if the hierarchy does not exist in item denorm
BEGIN
SELECT 1 INTO v_num_rows
FROM mth_item_denorm_d
WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
level_num = 10 AND
ROWNUM = 1;
INSERT_UNASS_ITEM_DENORM_TMP(r_hid_and_levels.hierarchy_id,
v_unassigned_key,
v_unassigned_item_name,
v_unassigned_category_name);
IF ( v_num_rows_deleted OR
v_num_levels IS NOT NULL AND v_num_levels > 0 AND
r_hid_and_levels.max_level > v_num_levels ) THEN
DELETE FROM mth_item_denorm_d
WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
level_num < 10;
INSERT INTO MTH_ITEM_DENORM_D
( HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY, CREATED_BY,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
LEVEL1_NAME )
SELECT DISTINCT HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY, v_unassigned_key,
v_unassigned_key, v_unassigned_key, v_log_to_date, v_log_to_date,
LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
LEVEL1_NAME
FROM MTH_ITEM_DENORM_D_TMP
WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
level_num = 10;
IF ( v_num_rows_deleted ) THEN
BUILD_LEVEL_1_9_item_denorm(r_hid_and_levels.hierarchy_id);
UPDATE MTH_DIM_HIERARCHY
SET NUMBER_OF_LEVEL = r_hid_and_levels.max_level
WHERE dimension_name= 'ITEM' and
hierarchy_id = r_hid_and_levels.hierarchy_id;
INSERT INTO MTH_ITEM_HIERARCHY (HIERARCHY_ID,
LEVEL_FK_KEY,
PARENT_FK_KEY,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
LEVEL_NAME,
PARENT_NAME)
(SELECT UN_REC.HIERARCHY_ID,
UN_REC.LEVEL_FK_KEY,
v_unassigned_val,
v_unassigned_val,
v_log_to_date,
v_log_to_date,
v_unassigned_val,
v_unassigned_val,
UN_REC.LEVEL_NAME,
MTH_UTIL_PKG.MTH_UA_GET_MEANING()
FROM (SELECT MDM.HIERARCHY_ID HIERARCHY_ID,
MID.ITEM_PK_KEY LEVEL_FK_KEY,
MID.ITEM_NAME LEVEL_NAME
FROM
MTH_ITEMS_D MID,
MTH_SYSTEMS_SETUP MSS,
MTH_DIM_HIERARCHY MDM
WHERE MID.CREATION_DATE >v_log_from_date AND
MID.CREATION_DATE <= v_log_to_date AND
MID.ITEM_PK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() AND
MID.SYSTEM_FK_KEY = MSS.SYSTEM_PK_KEY AND
MDM.DIMENSION_NAME ='ITEM'
MINUS
SELECT MIH.HIERARCHY_ID,
MIH.LEVEL_FK_KEY ,
MIH.LEVEL_NAME
FROM
MTH_ITEM_HIERARCHY MIH )UN_REC
)
;
mth_util_pkg.log_msg('Number of rows inserted in MTH_ITEM_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);