[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date mth_run_log.last_update_date%TYPE;--who column
l_last_update_system_id mth_run_log.last_update_system_id%TYPE;--who column
SELECT ebs_organization_id,organization_code,
source,plant_pk,system_fk_key,from_date
FROM mth_plants_d, mth_systems_setup,mth_organizations_l
WHERE system_pk_key = system_fk_key
AND system_pk = p_db_global_name
AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
AND plant_fk_key=plant_pk_key;
DELETE FROM mth_run_log WHERE fact_table = p_fact_table;
l_last_update_system_id := -99999;
l_last_update_date := l_sysdate;
SELECT COUNT(*)
INTO l_mode
FROM mth_run_log
WHERE fact_table = l_fact_table
AND db_global_name = p_db_global_name
AND hub_organization_code = l_orgs.plant_pk;
--statement for insert
INSERT INTO mth_run_log (fact_table, ebs_organization_id,
ebs_organization_code,from_date,to_date, source, db_global_name,
creation_date,last_update_date,creation_system_id,
last_update_system_id,plant_start_date,hub_organization_code)
VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
l_from_date,l_to_date,l_source,p_db_global_name,l_creation_date,
l_last_update_date,l_creation_system_id,l_last_update_system_id,
l_plant_start_date,l_hub_organization_code);
/* update all non_ebs organizations from same system and plant with to_date as the passed date */
--Custom Logic for the time dimension
IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
THEN
UPDATE mth_run_log
SET from_date = p_run_start_date
WHERE
fact_table = p_fact_table and db_global_name=p_db_global_name;
--statment for update
UPDATE mth_run_log
SET TO_DATE = l_to_date,
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
WHERE
fact_table =l_fact_table
AND db_global_name = p_db_global_name
AND hub_organization_code = l_hub_organization_code;
SELECT COUNT(*)
INTO l_mode
FROM mth_run_log
WHERE fact_table = l_fact_table
AND ebs_organization_id = l_orgs.ebs_organization_id
AND db_global_name = p_db_global_name
AND hub_organization_code = l_orgs.plant_pk;
--statement for insert
INSERT INTO mth_run_log (fact_table, ebs_organization_id,
ebs_organization_code,from_date,to_date, source, db_global_name,
creation_date,last_update_date,creation_system_id,
last_update_system_id,plant_start_date,hub_organization_code)
VALUES(l_fact_table,l_ebs_organization_id,l_ebs_organization_code,
l_from_date,l_to_date,l_source,p_db_global_name,l_creation_date,
l_last_update_date,l_creation_system_id,l_last_update_system_id,
l_plant_start_date,l_hub_organization_code);
--if the above condition fails then update the row
ELSE--incremental load
--Custom Logic for the time dimension
IF p_fact_table = 'MTH_WORKDAY_SHIFTS_D'
THEN
UPDATE mth_run_log
SET from_date = p_run_start_date
WHERE
fact_table = p_fact_table;
--statment for update
UPDATE mth_run_log
SET TO_DATE = l_to_date,
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATE_SYSTEM_ID =l_last_update_system_id
WHERE
fact_table =l_fact_table
AND source =l_source
AND db_global_name = p_db_global_name
AND ebs_organization_id = l_ebs_organization_id
AND hub_organization_code = l_hub_organization_code;
l_last_update_system_id mth_run_log.last_update_system_id%TYPE;
l_last_update_date mth_run_log.last_update_date%TYPE;
SELECT ebs_organization_id,system_fk_key
FROM mth_plants_d, mth_systems_setup,mth_organizations_l
WHERE system_pk_key = system_fk_key
AND system_pk = p_db_global_name
AND NVL(to_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)--pick active plants only
AND plant_fk_key=plant_pk_key;
l_last_update_system_id := -99999;
l_last_update_date := l_sysdate;
all organizations are populated with sysdate(source or target) as the to_date. So giving a generic update command here would work */
SELECT min(to_date) --min is to avoid getting duplicate rows
INTO l_from_date--from date set to previous to_date
FROM mth_run_log
WHERE fact_table = l_fact_table
AND db_global_name = p_db_global_name;
UPDATE mth_run_log
SET from_date = l_from_date,--from date set to previous to_date
to_date = NULL,--to_date set to null for next run
last_update_date = l_last_update_date
Where fact_table = l_fact_table
AND db_global_name = p_db_global_name;
SELECT to_date
INTO l_from_date--from date set to previous to_date
FROM mth_run_log
WHERE fact_table = l_fact_table
AND db_global_name = p_db_global_name
AND ebs_organization_id = l_ebs_organization_id;
UPDATE mth_run_log
SET from_date = l_from_date,--from date set to previous to_date
to_date = NULL,--to_date set to null for next run
last_update_date = l_last_update_date
Where fact_table = l_fact_table
AND ebs_organization_id = l_ebs_organization_id
AND db_global_name = p_db_global_name;
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
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
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 owner
FROM ALL_TABLES
WHERE table_name = p_table_name
AND owner in (p_owner1, p_owner2);
SELECT log_owner, log_table
FROM ALL_SNAPSHOT_LOGS
WHERE master = p_table_name
AND log_owner in (p_owner1, p_owner2);
SELECT lookup_code INTO v_lookup_code FROM FND_LOOKUP_VALUES WHERE
lookup_type='MTH_UNASSIGNED_L' AND language=userenv('LANG');
SELECT meaning INTO v_lookup_meaning FROM FND_LOOKUP_VALUES WHERE
lookup_type='MTH_UNASSIGNED_L' AND language=userenv('LANG');
SELECT to_date
FROM mth_run_log
WHERE fact_table = p_key_table and rownum=1;
INSERT INTO mth_run_log
(fact_table, ebs_organization_id, ebs_organization_code, from_date,
to_date, source, db_global_name, creation_date, last_update_date,
creation_system_id, last_update_system_id, plant_start_date)
VALUES
(p_key_table, -1, '-1', v_from_date,
v_to_date, -1, '-99999', v_sysdate, v_sysdate,
-1, -1, v_default_start_date);
UPDATE mth_run_log
SET TO_DATE = v_to_date,
FROM_DATE = v_from_date,
LAST_UPDATE_DATE = v_sysdate
WHERE
fact_table = p_key_table;
select fnd_profile.value(p_profile_name) into v_profile_value from dual;
SELECT last_refresh_date
FROM user_mviews
WHERE mview_name = p_mv_name;
SELECT max(to_date) into v_refresh_date_required
FROM mth_run_log
WHERE fact_table = 'MTH_ALL_MVS' AND db_global_name = v_unassigned_string;
* Description :This procedure is used to insert the level_num column *
* in the mth_equipment_denorm_d table *
* File Name :MTHUTILB.PLS *
* Visibility :Private *
* Modification log : *
* Author Date Change *
* shanthi donthu 16-Jul-2008 Initial Creation *
***************************************************************************** */
PROCEDURE PUT_EQUIP_DENORM_LEVEL_NUM
IS
BEGIN
UPDATE MTH_EQUIPMENT_DENORM_D SET LEVEL_NUM = (
CASE WHEN EQUIPMENT_FK_KEY IS NOT NULL THEN 10
ELSE CASE WHEN LEVEL9_LEVEL_KEY IS NOT NULL THEN 9
ELSE CASE WHEN LEVEL8_LEVEL_KEY IS NOT NULL THEN 8
ELSE CASE WHEN LEVEL7_LEVEL_KEY IS NOT NULL THEN 7
ELSE CASE WHEN LEVEL6_LEVEL_KEY IS NOT NULL THEN 6
ELSE CASE WHEN LEVEL5_LEVEL_KEY IS NOT NULL THEN 5
ELSE CASE WHEN LEVEL4_LEVEL_KEY IS NOT NULL THEN 4
ELSE CASE WHEN LEVEL3_LEVEL_KEY IS NOT NULL THEN 3
ELSE CASE WHEN LEVEL2_LEVEL_KEY IS NOT NULL THEN 2
ELSE CASE WHEN LEVEL1_LEVEL_KEY IS NOT NULL THEN 1
END
END
END
END
END
END
END
END
END
END )
WHERE LEVEL_NUM IS NULL;
* Procedure :update_equip_hrchy_gid *
* Description :This procedue is used for updating the group_id column in *
* the mth_equip_hierarchy table. The group id will be used to determine the *
* sequence in which a particular record will be processed in the equipment SCD *
* logic. The oldest relationships will have the lowest group id =1 and the new *
* relationships will have higher group id. All the catch all relationships i.e.*
* the relationship with parent = -99999 and effective date = 1-Jan-1900 will *
* have group id defaulted to 1 inside the MTH_EQUIP_HRCHY_UA_ALL_MAP map. *
* File Name :MTHUTILB.PLS *
* Visibility :Public *
* Parameters : none *
* Modification log : *
* Author Date Change *
* Ankit Goyal 26-Aug-2008 Initial Creation *
***************************************************************************** */
PROCEDURE update_equip_hrchy_gid
IS
/*variable to track # of conlficting rows*/
l_max_gid NUMBER := 0;
SELECT old_rows.hierarchy_id,
old_rows.level_num ,
old_rows.group_id ,
old_rows.level_fk_key ,
old_rows.effective_date
FROM
(SELECT hierarchy_id ,
level_fk_key ,
level_num ,
effective_date effective_date,
group_id
FROM mth_equip_hierarchy
WHERE group_id > 1
/*group_id==1 are catch all rows. */
GROUP BY hierarchy_id,
level_fk_key ,
level_num ,
group_id ,
effective_date
) old_rows ,
(SELECT hierarchy_id,
level_fk_key ,
level_num ,
effective_date ,
parent_fk_key
FROM mth_equip_hierarchy
WHERE group_id IS NULL
) new_rows
/*new relationships with group id as null */
WHERE old_rows.hierarchy_id = new_rows.hierarchy_id
AND old_rows.level_fk_key = new_rows.level_fk_key
AND old_rows.level_num = new_rows.level_num
AND old_rows.effective_date > new_rows.effective_date;
effective date.This tells us all the rows that will need to be updated so
that they are processed in the correct groups*/
CURSOR cr_aggr_conflict_rows(p_effective_date IN date,p_hierarchy_id IN number,p_level_fk_key IN number,p_level_num IN number)
IS
SELECT new_ed FROM (
SELECT old_rows.hierarchy_id ,
old_rows.level_num ,
MIN(old_rows.group_id) group_id,
/*to skip group by */
old_rows.level_fk_key ,
MIN(old_rows.effective_date) effective_date,
/*effecitve date of the old row */
new_rows.effective_date new_ed
/*effecitve date of the new row */
FROM
(SELECT hierarchy_id ,
level_fk_key ,
level_num ,
effective_date effective_date,
group_id
FROM mth_equip_hierarchy
WHERE group_id > 1
/*group_id==1 are catch all rows. */
) old_rows ,
(SELECT hierarchy_id,
level_fk_key ,
level_num ,
effective_date ,
parent_fk_key
FROM mth_equip_hierarchy
WHERE group_id IS NULL
) new_rows
/*new relationships with group id as null */
WHERE old_rows.hierarchy_id = new_rows.hierarchy_id
AND old_rows.level_fk_key = new_rows.level_fk_key
AND old_rows.level_num = new_rows.level_num
AND old_rows.effective_date > new_rows.effective_date
GROUP BY old_rows.hierarchy_id,
old_rows.level_num ,
old_rows.level_fk_key ,
new_rows.effective_date) c_rows WHERE
c_rows.effective_date =p_effective_date
AND c_rows.hierarchy_id = p_hierarchy_id
AND c_rows.level_fk_key = p_level_fk_key
AND c_rows.level_num = p_level_num ;
SELECT effective_date,
hierarchy_id ,
level_fk_key ,
level_num
FROM mth_equip_hierarchy
WHERE group_id IS NULL;
UPDATE mth_equip_hierarchy
SET group_id = l_rows.group_id
WHERE effective_date = v_new_ed
/*This is the determining condition */
AND hierarchy_id = l_rows.hierarchy_id
AND level_fk_key = l_rows.level_fk_key
AND level_num = l_rows.level_num;
/*Update the odl rows and increment the group id by 1 */
UPDATE mth_equip_hierarchy
SET group_id = l_rows.group_id + 1
WHERE effective_date = l_rows.effective_date
AND hierarchy_id = l_rows.hierarchy_id
AND level_fk_key = l_rows.level_fk_key
AND level_num = l_rows.level_num;
/*This part of the logic will update any rows which did not cause a conflict
with the old rows. This logic is necessary as the data can contain both the
conflit rows and non conflict rows*/
/*get the number of new rows remaining to be updated. */
SELECT COUNT(* )
INTO l_new_rows
FROM mth_equip_hierarchy
WHERE group_id IS NULL;
SELECT MAX(group_id)
INTO l_max_gid
FROM mth_equip_hierarchy
WHERE hierarchy_id = new_rows.hierarchy_id
AND level_fk_key = new_rows.level_fk_key
AND level_num = new_rows.level_num;
/*update the new rows gorup_id column and set it = group_id of old row + 1*/
UPDATE mth_equip_hierarchy
SET group_id = l_max_gid + 1
WHERE hierarchy_id = new_rows.hierarchy_id
AND level_fk_key = new_rows.level_fk_key
AND level_num = new_rows.level_num
AND effective_date = new_rows.effective_date;
END update_equip_hrchy_gid;
SELECT MIN(group_id)
INTO v_minmax
FROM mth_equip_hierarchy;
SELECT MAX(group_id)
INTO v_minmax
FROM mth_equip_hierarchy;
v_stmt := 'SELECT processing_flag FROM ' ||
v_schema_name || '.' || p_table_name ||
' WHERE rownum < 2';