[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OPEN c_equip_hierarchy FOR 'Select NVL(GROUP_ID, 1) GROUP_ID, LEVEL_FK_KEY, HIERARCHY_ID, LEVEL_NUM, EFFECTIVE_DATE
FROM MTH_EQUIP_HIERARCHY
ORDER BY HIERARCHY_ID, LEVEL_NUM, LEVEL_FK_KEY, EFFECTIVE_DATE ';
SELECT parent_fk_key,child_level_fk_key,parent_level_fk_key,child_date,parent_date,child_level_num,parent_level_num,parent_level_num_incr
FROM (SELECT level_num child_level_num,level_fk_key child_level_fk_key,Min(effective_date) child_date,hierarchy_id,parent_fk_key FROM mth_equip_hierarchy
WHERE level_num >1
GROUP BY level_num,level_fk_key,hierarchy_id,parent_fk_key ) h_child,
(SELECT level_num parent_level_num, (level_num+1) parent_level_num_incr,level_fk_key parent_level_fk_key,Min(effective_date) parent_date ,hierarchy_id FROM mth_equip_hierarchy
WHERE level_num <10
GROUP BY level_num,level_fk_key,hierarchy_id ) h_parent
WHERE
h_child.parent_fk_key = h_parent.parent_level_fk_key(+)
AND h_child.hierarchy_id = h_parent.hierarchy_id(+)
AND h_child.child_level_num = h_parent.parent_level_num_incr(+) ;
SELECT Count(*) record_count
FROM mth_equip_hierarchy h,
mth_equipment_denorm_d d,
(SELECT Min(from_date) from_date
FROM mth_run_log
WHERE fact_table = 'MTH_EQUIPMENT_DENORM_D') r
WHERE h.creation_date > r.from_date
AND h.level_num <= 10
AND d.level_num >= h.level_num
AND h.level_fk_key = Decode(h.level_num,2,d.level2_level_key,
3,d.level3_level_key,
4,d.level4_level_key,
5,d.level5_level_key,
6,d.level6_level_key,
7,d.level7_level_key,
8,d.level8_level_key,
9,d.level9_level_key)
AND h.hierarchy_id = Decode(h.level_num,2,d.level2_hierarchy_key,
3,d.level3_hierarchy_key,
4,d.level4_hierarchy_key,
5,d.level5_hierarchy_key,
6,d.level6_hierarchy_key,
7,d.level7_hierarchy_key,
8,d.level8_hierarchy_key,
9,d.level9_hierarchy_key)
AND h.effective_date <= Decode(d.level_num,
3, d.level3_effective_date,
4, d.level4_effective_date,
5, d.level5_effective_date,
6, d.level6_effective_date,
7, d.level7_effective_date,
8, d.level8_effective_date,
9, d.level9_effective_date,
10,d.equipment_effective_date);
SELECT Decode(h.level_num,2,d.level2_hierarchy_key,
3,d.level3_hierarchy_key,
4,d.level4_hierarchy_key,
5,d.level5_hierarchy_key,
6,d.level6_hierarchy_key,
7,d.level7_hierarchy_key,
8,d.level8_hierarchy_key,
9,d.level9_hierarchy_key,
10,d.equipment_hierarchy_key) hierarchy_id,
h.level_num,
h.level_fk_key,
Decode(h.level_num,2,d.level2_level_name,
3,d.level3_level_name,
4,d.level4_level_name,
5,d.level5_level_name,
6,d.level6_level_name,
7,d.level7_level_name,
8,d.level8_level_name,
9,d.level9_level_name,
9,d.equipment_level_name) level_name,
h.PARENT_FK_KEY,
h.effective_date,
d.level_num child_level_num,
Decode(d.level_num,2,d.level2_level_name,
3,d.level3_level_name,
4,d.level4_level_name,
5,d.level5_level_name,
6,d.level6_level_name,
7,d.level7_level_name,
8,d.level8_level_name,
9,d.level9_level_name,
10,d.equipment_level_name) child_level_name,
Decode(d.level_num,2,d.level2_effective_date,
3,d.level3_effective_date,
4,d.level4_effective_date,
5,d.level5_effective_date,
6,d.level6_effective_date,
7,d.level7_effective_date,
8,d.level8_effective_date,
9,d.level9_effective_date,
10,d.equipment_effective_date) child_effective_date
FROM mth_equip_hierarchy h,
mth_equipment_denorm_d d,
(SELECT Min(from_date) from_date
FROM mth_run_log
WHERE fact_table = 'MTH_EQUIPMENT_DENORM_D') r
WHERE h.creation_date > r.from_date
AND h.level_num <= 10
AND d.level_num >= h.level_num
AND h.level_fk_key = Decode(h.level_num,2,d.level2_level_key,
3,d.level3_level_key,
4,d.level4_level_key,
5,d.level5_level_key,
6,d.level6_level_key,
7,d.level7_level_key,
8,d.level8_level_key,
9,d.level9_level_key)
AND h.hierarchy_id = Decode(h.level_num,2,d.level2_hierarchy_key,
3,d.level3_hierarchy_key,
4,d.level4_hierarchy_key,
5,d.level5_hierarchy_key,
6,d.level6_hierarchy_key,
7,d.level7_hierarchy_key,
8,d.level8_hierarchy_key,
9,d.level9_hierarchy_key)
AND h.effective_date <= Decode(d.level_num,
3, d.level3_effective_date,
4, d.level4_effective_date,
5, d.level5_effective_date,
6, d.level6_effective_date,
7, d.level7_effective_date,
8, d.level8_effective_date,
9, d.level9_effective_date,
10,d.equipment_effective_date);
/*This procedure uses a heuristic query to check if the entities from the hierarchy table need to be moved/inserted. If the previous effective
date is NULL, this emplies, this entry does not exist on the Equipment Denorm table, and hence the Insert procedure is called to insert
new rows into the denorm table. Else, the Move procedure is called to move the existing entities. */
PROCEDURE MTH_POPULATE_EQUIP_DENORM IS
v_log_from_date DATE;
SELECT * from
(SELECT hierarchy_id,level_num,level_fk_key,parent_fk_key,level_name,group_id,creation_date,effective_date,Lag(effective_date)
over (PARTITION BY hierarchy_id,level_num,level_fk_key
ORDER BY group_id ) previous_effective_date,
(Lead(effective_date)
over (PARTITION BY hierarchy_id,level_num,level_fk_key
ORDER BY group_id )) - 1/(24*60*60) expiration_date
FROM mth_equip_hierarchy) equip_hierarchy /*,
(SELECT
DISTINCT
MTH_RUN_LOG.FROM_DATE,
MTH_RUN_LOG.TO_DATE
FROM
MTH_RUN_LOG
WHERE
(MTH_RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENT_DENORM_D')) denorm_run_log */
WHERE equip_hierarchy.creation_date > p_from_date
AND equip_hierarchy.creation_date <= p_to_date
--ORDER BY HIERARCHY_ID, GROUP_ID, LEVEL_NUM ;
MTH_INSERT_EQUIP_DENORM(r_equip_hierarchy.level_fk_key,r_equip_hierarchy.parent_fk_key,r_equip_hierarchy.hierarchy_id,r_equip_hierarchy.level_num,r_equip_hierarchy.effective_date,r_equip_hierarchy.level_name);
* Procedure :MTH_INSERT_EQUIP_DENORM *
* Description :This procedure is used for populating equipment *
* denorm table *
* File Name :---- *
* Visibility :Public *
* Parameters :p_level_fk_key *
* p_parent_fk_key *
* p_hierarchy_id *
* p_level_num *
* p_effective_date *
* p_level_name *
* *
* Modification log : *
* Author Date Change *
* Sanjeev Vellore Ramani 30-Mar-2012 Initial Creation*
*******************************************************************************/
/*This procedure inserts entries into the equipment denorm table. First, the ancestor information already existing for an entity
in the denorm table is determined. Then a new row is inserted into the Equipment Denorm table, complete with the ancestor information.*/
PROCEDURE MTH_INSERT_EQUIP_DENORM(
p_level_fk_key IN NUMBER,
p_parent_fk_key IN NUMBER,
p_hierarchy_id IN NUMBER,
p_level_num IN NUMBER,
p_effective_date IN DATE,
p_level_name IN VARCHAR2
) IS
v_log_date DATE;
/* pick the ancestors for the specified entry to be inserted into the denorm table. This might pick more than one ancestor row,
if the current row's effective date is between "effective date - non null expiration date" range of one of the parent rows*/
CURSOR c_equip_denorm IS
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE level_num = p_level_num - 1
and level1_hierarchy_key = p_hierarchy_id
and ((p_level_num =2 and level1_level_key = p_parent_fk_key and level1_hierarchy_key = p_hierarchy_id)
OR
(p_level_num =3 and level2_level_key = p_parent_fk_key and level2_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level2_expiration_date,p_effective_date))
OR
(p_level_num =4 and level3_level_key = p_parent_fk_key and level3_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level3_expiration_date,p_effective_date))
OR
(p_level_num =5 and level4_level_key = p_parent_fk_key and level4_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level4_expiration_date,p_effective_date))
OR
(p_level_num =6 and level5_level_key = p_parent_fk_key and level5_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level5_expiration_date,p_effective_date))
OR
(p_level_num =7 and level6_level_key = p_parent_fk_key and level6_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level6_expiration_date,p_effective_date))
OR
(p_level_num =8 and level7_level_key = p_parent_fk_key and level7_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level7_expiration_date,p_effective_date))
OR
(p_level_num =9 and level8_level_key = p_parent_fk_key and level8_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level8_expiration_date,p_effective_date))
OR
(p_level_num =10 and level9_level_key = p_parent_fk_key and level9_hierarchy_key = p_hierarchy_id
AND p_effective_date <=Nvl(level9_expiration_date,p_effective_date))
);
mth_util_pkg.log_msg('MTH_INSERT_EQUIP_DENORM start', mth_util_pkg.G_DBG_PROC_FUN_START);
/*Ensure that an insert happens even when denorm table is empty and the parent cursor does not return any row*/
exit when (c_equip_denorm%NOTFOUND AND v_count > 0) ;
v_row.LAST_UPDATE_DATE := v_log_date;
v_row.LAST_UPDATE_LOGIN := v_unassigned_val;
v_row.LAST_UPDATED_BY := v_unassigned_val;
INSERT INTO mth_equipment_denorm_d(dimension_key,
level1_hierarchy_key,
level1_id,
level1_level_key,
level1_level_name,
level2_hierarchy_key,
level2_effective_date,
level2_expiration_date,
level2_id,
level2_level_key,
level2_parent_key,
level2_level_name,
level3_hierarchy_key,
level3_effective_date,
level3_expiration_date,
level3_id,
level3_level_key,
level3_parent_key,
level3_level_name,
level4_hierarchy_key,
level4_effective_date,
level4_expiration_date,
level4_id,
level4_level_key,
level4_parent_key,
level4_level_name,
level5_hierarchy_key,
level5_effective_date,
level5_expiration_date,
level5_id,
level5_level_key,
level5_parent_key,
level5_level_name,
level6_hierarchy_key,
level6_effective_date,
level6_expiration_date,
level6_id,
level6_level_key,
level6_parent_key,
level6_level_name,
level7_hierarchy_key,
level7_effective_date,
level7_expiration_date,
level7_id,
level7_level_key,
level7_parent_key,
level7_level_name,
level8_hierarchy_key,
level8_effective_date,
level8_expiration_date,
level8_id,
level8_level_key,
level8_parent_key,
level8_level_name,
level9_hierarchy_key,
level9_effective_date,
level9_expiration_date,
level9_id,
level9_level_key,
level9_parent_key,
level9_level_name,
equipment_hierarchy_key,
equipment_effective_date,
equipment_expiration_date,
equipment_id,
equipment_fk_key,
equipment_parent_key,
equipment_level_name,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
level_num)
VALUES (v_row.dimension_key,
v_row.level1_hierarchy_key,
v_row.level1_id,
v_row.level1_level_key,
v_row.level1_level_name,
v_row.level2_hierarchy_key,
v_row.level2_effective_date,
v_row.level2_expiration_date,
v_row.level2_id,
v_row.level2_level_key,
v_row.level2_parent_key,
v_row.level2_level_name,
v_row.level3_hierarchy_key,
v_row.level3_effective_date,
v_row.level3_expiration_date,
v_row.level3_id,
v_row.level3_level_key,
v_row.level3_parent_key,
v_row.level3_level_name,
v_row.level4_hierarchy_key,
v_row.level4_effective_date,
v_row.level4_expiration_date,
v_row.level4_id,
v_row.level4_level_key,
v_row.level4_parent_key,
v_row.level4_level_name,
v_row.level5_hierarchy_key,
v_row.level5_effective_date,
v_row.level5_expiration_date,
v_row.level5_id,
v_row.level5_level_key,
v_row.level5_parent_key,
v_row.level5_level_name,
v_row.level6_hierarchy_key,
v_row.level6_effective_date,
v_row.level6_expiration_date,
v_row.level6_id,
v_row.level6_level_key,
v_row.level6_parent_key,
v_row.level6_level_name,
v_row.level7_hierarchy_key,
v_row.level7_effective_date,
v_row.level7_expiration_date,
v_row.level7_id,
v_row.level7_level_key,
v_row.level7_parent_key,
v_row.level7_level_name,
v_row.level8_hierarchy_key,
v_row.level8_effective_date,
v_row.level8_expiration_date,
v_row.level8_id,
v_row.level8_level_key,
v_row.level8_parent_key,
v_row.level8_level_name,
v_row.level9_hierarchy_key,
v_row.level9_effective_date,
v_row.level9_expiration_date,
v_row.level9_id,
v_row.level9_level_key,
v_row.level9_parent_key,
v_row.level9_level_name,
v_row.equipment_hierarchy_key,
v_row.equipment_effective_date,
v_row.equipment_expiration_date,
v_row.equipment_id,
v_row.equipment_fk_key,
v_row.equipment_parent_key,
v_row.equipment_level_name,
v_row.creation_date,
v_row.last_update_date,
v_row.created_by,
v_row.last_updated_by,
v_row.last_update_login,
v_row.level_num
)
RETURNING dimension_key INTO v_dimension_key;
mth_util_pkg.log_msg('Dimension_key inserted - ' || v_dimension_key, mth_util_pkg.G_DBG_VAR_VAL);
mth_util_pkg.log_msg('MTH_INSERT_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
mth_util_pkg.log_msg('Exception OTHERS in MTH_INSERT_EQUIP_DENORM', mth_util_pkg.G_DBG_EXCEPTION);
mth_util_pkg.log_msg('MTH_INSERT_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
/*Select the entry at given level to be moved, along with its children*/
IF(p_level_num =2)THEN
OPEN c_move_equip_denorm FOR
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL2_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL2_LEVEL_KEY = p_level_fk_key AND
LEVEL2_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL3_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL3_LEVEL_KEY = p_level_fk_key AND
LEVEL3_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL4_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL4_LEVEL_KEY = p_level_fk_key AND
LEVEL4_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL5_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL5_LEVEL_KEY = p_level_fk_key AND
LEVEL5_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL6_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL6_LEVEL_KEY = p_level_fk_key AND
LEVEL6_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL7_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL7_LEVEL_KEY = p_level_fk_key AND
LEVEL7_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL8_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL8_LEVEL_KEY = p_level_fk_key AND
LEVEL8_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
LEVEL9_HIERARCHY_KEY = p_hierarchy_id AND
LEVEL9_LEVEL_KEY = p_level_fk_key AND
LEVEL9_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
SELECT *
FROM MTH_EQUIPMENT_DENORM_D
WHERE
LEVEL_NUM >= p_level_num AND
EQUIPMENT_HIERARCHY_KEY = p_hierarchy_id AND
EQUIPMENT_FK_KEY = p_level_fk_key AND
EQUIPMENT_EXPIRATION_DATE IS NULL
ORDER BY LEVEL_NUM;
/*Insert a new row with parent information passed from the calling procedure*/
MTH_INSERT_EQUIP_DENORM(p_level_fk_key,p_parent_fk_key,p_hierarchy_id,p_level_num,p_effective_date,p_level_name);
/*Set the expiration date and insert a new row for one or more children of the newly moved parent*/
IF (r_move_equip_denorm.level_num =3 AND r_move_equip_denorm.level3_expiration_date IS NULL)THEN
MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level3_level_key,r_move_equip_denorm.level2_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level3_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level4_level_key,r_move_equip_denorm.level3_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level4_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level5_level_key,r_move_equip_denorm.level4_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level5_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level6_level_key,r_move_equip_denorm.level5_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level6_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level7_level_key,r_move_equip_denorm.level6_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level7_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level8_level_key,r_move_equip_denorm.level7_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level8_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level9_level_key,r_move_equip_denorm.level8_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level9_level_name);
MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.equipment_fk_key,r_move_equip_denorm.level9_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.equipment_level_name);
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level1_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level2_expiration_date = v_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 2;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level2_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level3_expiration_date = v_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 3;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level3_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level4_expiration_date = v_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 4;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level4_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level5_expiration_date = v_expiration_date,
level4_expiration_date = r_expire_denorm.level4_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 5;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level5_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level6_expiration_date = v_expiration_date,
level5_expiration_date = r_expire_denorm.level5_expiration_date,
level4_expiration_date = r_expire_denorm.level4_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 6;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level6_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level7_expiration_date = v_expiration_date,
level6_expiration_date = r_expire_denorm.level6_expiration_date,
level5_expiration_date = r_expire_denorm.level5_expiration_date,
level4_expiration_date = r_expire_denorm.level4_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 7;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level7_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level8_expiration_date = v_expiration_date,
level7_expiration_date = r_expire_denorm.level7_expiration_date,
level6_expiration_date = r_expire_denorm.level6_expiration_date,
level5_expiration_date = r_expire_denorm.level5_expiration_date,
level4_expiration_date = r_expire_denorm.level4_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 8;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level8_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET level9_expiration_date = v_expiration_date,
level8_expiration_date = r_expire_denorm.level8_expiration_date,
level7_expiration_date = r_expire_denorm.level7_expiration_date,
level6_expiration_date = r_expire_denorm.level6_expiration_date,
level5_expiration_date = r_expire_denorm.level5_expiration_date,
level4_expiration_date = r_expire_denorm.level4_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 9;
SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
(SELECT level9_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
UPDATE mth_equipment_denorm_d
SET equipment_expiration_date = v_expiration_date,
level9_expiration_date = r_expire_denorm.level9_expiration_date,
level8_expiration_date = r_expire_denorm.level8_expiration_date,
level7_expiration_date = r_expire_denorm.level7_expiration_date,
level6_expiration_date = r_expire_denorm.level6_expiration_date,
level5_expiration_date = r_expire_denorm.level5_expiration_date,
level4_expiration_date = r_expire_denorm.level4_expiration_date,
level3_expiration_date = r_expire_denorm.level3_expiration_date,
level2_expiration_date = r_expire_denorm.level2_expiration_date,
last_update_date = v_log_date
WHERE dimension_key = p_dimension_key
AND level_num = 10;
mth_util_pkg.log_msg('Number of rows updated - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
(SELECT MDM.HIERARCHY_ID,
10 LEVEL_NUM,
ME.EQUIPMENT_PK_KEY ,
v_unassigned_val PARENT_FK_KEY,
To_Date('01-01-1900','DD-MM-YYYY') EFFECTIVE_DATE,
ME.EQUIPMENT_NAME EQUIPMENT_NAME,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() PARENT_NAME
FROM MTH_EQUIPMENTS_D ME,MTH_DIM_HIERARCHY MDM
WHERE MDM.DIMENSION_NAME = 'EQUIPMENT'
)CATCH_ALL
ON (
MEQU.HIERARCHY_ID = CATCH_ALL.HIERARCHY_ID AND
MEQU.LEVEL_NUM = CATCH_ALL.LEVEL_NUM AND
MEQU.LEVEL_FK_KEY = CATCH_ALL.EQUIPMENT_PK_KEY AND
MEQU.EFFECTIVE_DATE = CATCH_ALL.EFFECTIVE_DATE
)
WHEN MATCHED THEN UPDATE
SET
LAST_UPDATE_SYSTEM_ID = v_unassigned_val,
GROUP_ID = 1
WHEN NOT MATCHED THEN
INSERT
(MEQU.HIERARCHY_ID,
MEQU.LEVEL_NUM,
MEQU.LEVEL_FK_KEY,
MEQU.PARENT_FK_KEY,
MEQU.SYSTEM_FK_KEY,
MEQU.EFFECTIVE_DATE,
MEQU.CREATION_DATE,
MEQU.LAST_UPDATE_DATE,
MEQU.CREATION_SYSTEM_ID,
MEQU.LAST_UPDATE_SYSTEM_ID,
MEQU.LEVEL_NAME,
MEQU.PARENT_NAME,
MEQU.GROUP_ID)
VALUES
(CATCH_ALL.HIERARCHY_ID,
CATCH_ALL.LEVEL_NUM,
CATCH_ALL.EQUIPMENT_PK_KEY,
CATCH_ALL.PARENT_FK_KEY,
v_unassigned_val,
CATCH_ALL.EFFECTIVE_DATE,
v_log_to_date,
v_log_to_date,
v_unassigned_val,
v_unassigned_val,
CATCH_ALL.EQUIPMENT_NAME,
CATCH_ALL.PARENT_NAME,
1)
;
( SELECT LEVEL AS n
FROM dual
CONNECT BY LEVEL <= 9
)
SELECT c.n AS LEVEL_NUM
, To_Date ('01-01-1900','DD-MM-YYYY') AS EFFECTIVE_DATE
, -99999 AS LEVEL_KEY
, 'Unassigned' AS LEVEL_NAME
, -99999 AS PARENT_KEY
, 'Unassigned' AS PARENT_NAME
, MDH.HIERARCHY_ID
FROM MTH_DIM_HIERARCHY MDH
CROSS JOIN cntr c
WHERE MDH.CREATION_DATE >= v_log_from_date
AND MDH.DIMENSION_NAME = 'EQUIPMENT')UN_ALL
ON (
MEQ.HIERARCHY_ID = UN_ALL.HIERARCHY_ID AND
MEQ.LEVEL_NUM = UN_ALL.LEVEL_NUM AND
MEQ.LEVEL_FK_KEY = UN_ALL.LEVEL_KEY AND
MEQ.EFFECTIVE_DATE = UN_ALL.EFFECTIVE_DATE
)
WHEN MATCHED THEN
UPDATE
SET
PARENT_FK_KEY =UN_ALL.PARENT_KEY,
SYSTEM_FK_KEY = v_unassigned_val,
CREATION_SYSTEM_ID =v_unassigned_val ,
LEVEL_NAME = UN_ALL.LEVEL_NAME,
GROUP_ID =1
WHEN NOT MATCHED THEN
INSERT
(HIERARCHY_ID,
LEVEL_NUM,
LEVEL_FK_KEY,
PARENT_FK_KEY,
SYSTEM_FK_KEY,
EFFECTIVE_DATE,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
LEVEL_NAME,
PARENT_NAME,
GROUP_ID)
VALUES
(UN_ALL.HIERARCHY_ID,
UN_ALL.LEVEL_NUM,
UN_ALL.LEVEL_KEY,
UN_ALL.PARENT_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
UN_ALL.EFFECTIVE_DATE,
v_log_to_date,
v_log_to_date,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
UN_ALL.LEVEL_NAME,
UN_ALL.PARENT_NAME,
1)
;
(SELECT
distinct --Added to avoid MTH_EQUIP_HIERARCHY_U1 violation in case of
--reassignments of same level to different parent (sasuren)
MEH.HIERARCHY_ID,
MEH.LEVEL_NUM,
MEH.LEVEL_FK_KEY,
--MEH.SYSTEM_FK_KEY ,
--SF interface populates this as valid value while hierarchy UA populates as
-- -99999. Hence has to be taken out from here
To_Date('01-01-1900','DD-MM-YYYY') CATCH_ALL_EFF_DATE,
--MEH.LAST_UPDATE_DATE LAST_UPDATE_DATE,
--Commented to ensure distinct works fine in case reassignment happens on a
--different date
MEH.LEVEL_NAME,
MTH_UTIL_PKG.MTH_UA_GET_MEANING UNASSIGNED_PARENT_NAME
FROM MTH_EQUIP_HIERARCHY MEH
WHERE
MEH.LAST_UPDATE_DATE >= v_log_from_date AND
MEH.LEVEL_FK_KEY <> -99999
)MERGE_QUERY
ON (MEQB.HIERARCHY_ID = MERGE_QUERY.HIERARCHY_ID AND
MEQB.LEVEL_NUM = MERGE_QUERY.LEVEL_NUM AND
MEQB.LEVEL_FK_KEY = MERGE_QUERY.LEVEL_FK_KEY AND
MEQB.EFFECTIVE_DATE = MERGE_QUERY.CATCH_ALL_EFF_DATE
)
WHEN MATCHED THEN
UPDATE
SET
SYSTEM_FK_KEY = v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
LEVEL_NAME = MERGE_QUERY.LEVEL_NAME,
GROUP_ID = 1
WHEN NOT MATCHED THEN
INSERT
(MEQB.HIERARCHY_ID,
MEQB.LEVEL_NUM,
MEQB.LEVEL_FK_KEY,
MEQB.PARENT_FK_KEY,
MEQB.SYSTEM_FK_KEY,
MEQB.EFFECTIVE_DATE,
MEQB.CREATION_DATE,
MEQB.LAST_UPDATE_DATE,
MEQB.CREATION_SYSTEM_ID,
MEQB.LAST_UPDATE_SYSTEM_ID,
MEQB.LEVEL_NAME,
MEQB.PARENT_NAME,
MEQB.GROUP_ID)
VALUES
(MERGE_QUERY.HIERARCHY_ID,
MERGE_QUERY.LEVEL_NUM,
MERGE_QUERY.LEVEL_FK_KEY,
-99999 ,
v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
MERGE_QUERY.CATCH_ALL_EFF_DATE,
v_log_to_date, --MERGE_QUERY.LAST_UPDATE_DATE,
v_log_to_date, --MERGE_QUERY.LAST_UPDATE_DATE,
v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
MERGE_QUERY.LEVEL_NAME,
MERGE_QUERY.UNASSIGNED_PARENT_NAME,
1)
;