[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;
* viveksha 30-Jan-2009 Updated the procedure to update txn ids *
***************************************************************************** */
PROCEDURE mth_run_log_post_load(p_fact_table IN VARCHAR2,
p_db_global_name IN VARCHAR2)
IS
--local variables initialization
l_fact_table mth_run_log.fact_table%TYPE;--fact table
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;
SELECT MIN(to_txn_id)
INTO l_from_txn_id--from txn id to be set to previous to txn id
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,
from_txn_id = l_from_txn_id, -- from txn id set to previous to txn id
to_txn_id = NULL
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
from_txn_id = l_from_txn_id, -- from txn id set to previous to txn id
to_txn_id = NULL,
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 Count(*) into v_count FROM mth_shift_reference_mv;
SELECT Least(
Decode(InStr(v_str,'+',v_str_len),0,4000,InStr(v_str,'+',v_str_len)),
Decode(InStr(v_str,'-',v_str_len),0,4000,InStr(v_str,'-',v_str_len)),
Decode(InStr(v_str,'*',v_str_len),0,4000,InStr(v_str,'*',v_str_len)),
Decode(InStr(v_str,'/',v_str_len),0,4000,InStr(v_str,'/',v_str_len)),
Decode(InStr(v_str,'(',v_str_len),0,4000,InStr(v_str,'(',v_str_len)),
Decode(InStr(v_str,')',v_str_len),0,4000,InStr(v_str,')',v_str_len)))
INTO v_position
FROM dual;
SELECT SubStr(v_str,v_str_len,v_position-1-(v_str_len-1))
INTO v_meter_id
FROM dual;
SELECT meter_name, meter_type, virtual_meter_formula
INTO v_meter_name, v_meter_type, v_virt_meter_formula
FROM mth_meters
WHERE meter_pk_key IN (SELECT COMPONENT_VALUE
FROM MTH_VIRTUAL_METER_COMPONENTS
WHERE component_name = v_meter_id);
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';
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,from_txn_id,to_txn_id)
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,l_from_txn_id,l_to_txn_id);
/* 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,
TO_TXN_ID = l_to_txn_id,
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,from_txn_id,to_txn_id)
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,l_from_txn_id,l_to_txn_id);
--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,
TO_TXN_ID = l_to_txn_id,
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;
SELECT DATABASE_COLUMN
FROM EGO_ATTRS_V
WHERE application_id = 9001 AND
ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP' AND
attr_group_name = p_att_grp_name AND
ATTR_NAME = p_attr_name;
SELECT ATTR_GROUP_ID
FROM EGO_ATTR_GROUPS_V
WHERE application_id = 9001 AND
ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP' AND
attr_group_name = p_att_grp_name;
query_str := 'SELECT ' || v_low_column_name || ', ' || v_upp_column_name ||
' FROM mth_equipments_ext_b' ||
' WHERE equipment_pk_key = :e_id AND ' ||
v_parameter_col_name || ' = :attr_name AND ' ||
' attr_group_id = :attr_group_id';
SELECT tag_data, reading_time, processed_flag
FROM mth_tag_readings_stg
WHERE tag_code = p_tag_code AND
reading_time < p_start_reading_time AND
reading_time >= p_end_reading_time
ORDER BY reading_time desc;
SELECT tag_data, reading_time
FROM mth_tag_readings
WHERE tag_code = p_tag_code AND
reading_time < p_start_reading_time AND
reading_time >= p_end_reading_time
ORDER BY reading_time desc;
SELECT tag_data, reading_time
FROM mth_tag_readings_err
WHERE tag_code = p_tag_code AND
reading_time < p_start_reading_time AND
reading_time >= p_end_reading_time
ORDER BY reading_time desc;
SELECT tag_data, reading_time, equipment_fk_key
FROM mth_tag_readings_stg
WHERE tag_code = p_tag_code AND
reading_time < p_start_reading_time AND
reading_time >= p_end_reading_time
ORDER BY reading_time desc;
SELECT tag_data, reading_time, equipment_fk_key
FROM mth_tag_readings
WHERE tag_code = p_tag_code AND
reading_time < p_start_reading_time AND
reading_time >= p_end_reading_time
ORDER BY reading_time desc;
SELECT tag_data, reading_time, equipment_fk_key
FROM mth_tag_readings_err
WHERE tag_code = p_tag_code AND
reading_time < p_start_reading_time AND
reading_time >= p_end_reading_time
ORDER BY reading_time desc;
SELECT s.availability_flag
FROM MTH_EQUIPMENT_SHIFTS_D s
WHERE s.equipment_fk_key = p_equipment_fk_key AND
p_reading_time BETWEEN s.from_date AND s.To_Date;
SELECT tag_data1, reading_time1, tag_data2, reading_time2
FROM (
SELECT r1.tag_data tag_data1, r1.reading_time reading_time1,
r2.tag_data tag_data2, r2.reading_time reading_time2
FROM mth_tag_readings_stg r1, mth_tag_readings_stg r2
WHERE r1.group_id = r2.GROUP_id AND
r1.reading_time < p_reading_time1 AND
r2.reading_time < p_reading_time2 AND
r1.reading_time >= p_end_time AND
r2.reading_time >= p_end_time AND
r1.tag_code = p_tag_code1 AND
r2.tag_code = p_tag_code2
UNION ALL
SELECT r1.tag_data tag_data1, r1.reading_time reading_time1,
r2.tag_data tag_data2, r2.reading_time reading_time2
FROM mth_tag_readings r1, mth_tag_readings r2
WHERE r1.group_id = r2.GROUP_id AND
r1.reading_time < p_reading_time1 AND
r2.reading_time < p_reading_time2 AND
r1.reading_time >= p_end_time AND
r2.reading_time >= p_end_time AND
r1.tag_code = p_tag_code1 AND
r2.tag_code = p_tag_code2
UNION ALL
SELECT r1.tag_data tag_data1, r1.reading_time reading_time1,
r2.tag_data tag_data2, r2.reading_time reading_time2
FROM mth_tag_readings_err r1, mth_tag_readings_err r2
WHERE r1.group_id = r2.GROUP_id AND
r1.reading_time < p_reading_time1 AND
r2.reading_time < p_reading_time2 AND
r1.reading_time >= p_end_time AND
r2.reading_time >= p_end_time AND
r1.tag_code = p_tag_code1 AND
r2.tag_code = p_tag_code2
)
ORDER BY reading_time1 DESC, reading_time2 DESC;
SELECT a.attr_name, t.equipment_fk_key
FROM mth_tag_destination_map t, ego_attrs_v a, EGO_ATTR_GROUPS_V g
WHERE t.tag_code = p_tag_code and t.attribute = a.attr_id AND
t.attribute_group = g.attr_group_id AND a.application_id = 9001 AND
a.application_id = g.application_id and
a.attr_group_name = g.attr_group_name;
SELECT tag_data, reading_time
FROM (
SELECT tag_data, reading_time
FROM mth_tag_readings_stg
WHERE reading_time < p_reading_time AND
reading_time >= p_end_time AND
tag_code = p_tag_code
UNION ALL
SELECT tag_data, reading_time
FROM mth_tag_readings
WHERE reading_time < p_reading_time AND
reading_time >= p_end_time AND
tag_code = p_tag_code
UNION ALL
SELECT tag_data, reading_time
FROM mth_tag_readings_err
WHERE reading_time < p_reading_time AND
reading_time >= p_end_time AND
tag_code = p_tag_code
)
ORDER BY reading_time DESC;
query_str := 'SELECT ' || v_mean_column_name ||
' FROM mth_equipments_ext_b' ||
' WHERE equipment_pk_key = :e_id AND ' ||
v_parameter_col_name || ' = :attr_name AND ' ||
' attr_group_id = :attr_group_id';
SELECT DISTINCT from_date INTO v_run_date FROM mth_run_log WHERE fact_table = 'MTH_EQUIP_DOWN_STS_UPTIME';
FOR i IN (SELECT equipment_fk_key,shift_workday_fk_key,from_date,To_Date,system_fk_key,creation_date,last_update_date,creation_system_id,
last_update_system_id FROM mth_equip_statuses WHERE status=3 AND To_Date IS NOT NULL AND last_update_date>=v_run_date)
LOOP
v_start_time := i.from_date;
FOR j IN (SELECT equipment_fk_key,shift_workday_fk_key,from_date,To_Date FROM mth_equipment_shifts_d WHERE equipment_fk_key = i.equipment_fk_key
AND shift_workday_fk_key = i.shift_workday_fk_key AND Upper(availability_flag)= 'N')
LOOP
--Dbms_Output.PUT_LINE('Equip_shift'||'------'||To_Char(j.from_date,'dd-Mon-yyyy hh24:mi:ss')||'------'||To_Char(j.to_date,'dd-Mon-yyyy hh24:mi:ss'));
UPDATE mth_equip_statuses SET To_Date = j.from_date WHERE equipment_fk_key = i.equipment_fk_key
AND shift_workday_fk_key = i.shift_workday_fk_key AND from_date = v_start_time;
INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
creation_date,last_update_date,creation_system_id,last_update_system_id) VALUES (i.equipment_fk_key,
i.shift_workday_fk_key,v_start_time,j.from_date,3,i.system_fk_key,i.creation_date,sysdate,
i.creation_system_id,i.last_update_system_id);
INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
creation_date,last_update_date,creation_system_id,last_update_system_id,expected_up_time,status_type) VALUES (i.equipment_fk_key,
i.shift_workday_fk_key,j.from_date,v_end_time,3,i.system_fk_key,i.creation_date,sysdate,
i.creation_system_id,i.last_update_system_id,((j.To_Date-j.from_date)*24),'PLANNED DOWNTIME');/*** EXPECTED_UP_TIME = J.TO_DATE - J.FROM_DATE **/
INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
creation_date,last_update_date,creation_system_id,last_update_system_id,expected_up_time,status_type) VALUES (i.equipment_fk_key,
i.shift_workday_fk_key,j.from_date,j.to_date,3,i.system_fk_key,i.creation_date,sysdate,
i.creation_system_id,i.last_update_system_id,((j.To_Date-j.from_date)*24),'PLANNED DOWNTIME');/*** EXPECTED_UP_TIME = J.TO_DATE - J.FROM_DATE **/
UPDATE mth_equip_statuses SET expected_up_time = ((j.to_date-v_start_time)*24), status_type = 'PLANNED DOWNTIME', last_update_date = SYSDATE
WHERE equipment_fk_key = i.equipment_fk_key
AND shift_workday_fk_key = i.shift_workday_fk_key AND from_date = v_start_time AND To_Date=v_end_time;
/***UPDATE THE RECORD WITH *** EXPECTED_UP_TIME = V_END_TIME - V_START_TIMR **/
EXIT ;
UPDATE mth_equip_statuses SET To_Date = j.to_date, last_update_date = SYSDATE,expected_up_time=((j.To_Date - v_start_time)*24), status_type ='PLANNED DOWNTIME' WHERE equipment_fk_key = i.equipment_fk_key
AND shift_workday_fk_key = i.shift_workday_fk_key AND from_date = v_start_time;
INSERT INTO mth_equip_statuses(equipment_fk_key,shift_workday_fk_key,from_date,To_Date,status,system_fk_key,
creation_date,last_update_date,creation_system_id,last_update_system_id) VALUES (i.equipment_fk_key,
i.shift_workday_fk_key,j.to_date,v_end_time,3,i.system_fk_key,i.creation_date,sysdate,
i.creation_system_id,i.last_update_system_id);
DELETE FROM mth_equip_statuses WHERE from_date = To_Date;
USING (select p_equipment_fk_key p_equipment_fk_key, p_shift_workday_fk_key p_shift_workday_fk_key,
p_hour_fk_key p_hour_fk_key, p_from_date p_from_date, p_to_date p_to_date, p_status p_status,
p_system_fk_key p_system_fk_key,p_user_dim1_fk_key p_user_dim1_fk_key,
p_user_dim2_fk_key p_user_dim2_fk_key, p_user_dim3_fk_key p_user_dim3_fk_key,
p_user_dim4_fk_key p_user_dim4_fk_key, p_user_dim5_fk_key p_user_dim5_fk_key,
p_user_attr1 p_user_attr1,p_user_attr2 p_user_attr2, p_user_attr3 p_user_attr3,
p_user_attr4 p_user_attr4, p_user_attr5 p_user_attr5, p_user_measure1 p_user_measure1,
p_user_measure2 p_user_measure2, p_user_measure3 p_user_measure3,
p_user_measure4 p_user_measure4, p_user_measure5 p_user_measure5 FROM dual) var
ON (stat.equipment_fk_key = var.p_equipment_fk_key AND
stat.shift_workday_fk_key = var.p_shift_workday_fk_key AND
stat.hour_fk_key = var.p_hour_fk_key AND
stat.from_date = var.p_from_date)
WHEN MATCHED THEN
UPDATE SET To_Date = p_to_date, status = p_status, system_fk_key = Nvl(p_system_fk_key,v_unassigned_string),
user_dim1_fk_key = p_user_dim1_fk_key, user_dim2_fk_key = p_user_dim2_fk_key,
user_dim3_fk_key = p_user_dim3_fk_key, user_dim4_fk_key = p_user_dim4_fk_key,
user_dim5_fk_key = p_user_dim1_fk_key, user_attr1 = p_user_attr1,user_attr2 =p_user_attr2,
user_attr3 = p_user_attr3, user_attr4 = p_user_attr4, user_attr5 = p_user_attr5,
user_measure1 = p_user_measure1, user_measure2 = p_user_measure2,
user_measure3 = p_user_measure3, user_measure4 = p_user_measure4,
user_measure5 = p_user_measure5, last_update_date = SYSDATE,last_update_system_id =p_system_fk_key
WHEN NOT MATCHED THEN
INSERT (stat.EQUIPMENT_FK_KEY, stat.SHIFT_WORKDAY_FK_KEY, stat.FROM_DATE, stat.TO_DATE,
stat.STATUS, stat.SYSTEM_FK_KEY, stat.USER_DIM1_FK_KEY, stat.USER_DIM2_FK_KEY, stat.USER_DIM3_FK_KEY,
stat.USER_DIM4_FK_KEY, stat.USER_DIM5_FK_KEY, stat.USER_ATTR1, stat.USER_ATTR2, stat.USER_ATTR3,
stat.USER_ATTR4, stat.USER_ATTR5, stat.USER_MEASURE1, stat.USER_MEASURE2, stat.USER_MEASURE3,
stat.USER_MEASURE4, stat.USER_MEASURE5, stat.CREATION_DATE, stat.LAST_UPDATE_DATE, stat.CREATION_SYSTEM_ID,
stat.LAST_UPDATE_SYSTEM_ID, stat.CREATED_BY, stat.LAST_UPDATE_LOGIN, stat.LAST_UPDATED_BY,
stat.EXPECTED_UP_TIME, stat.STATUS_TYPE, stat.HOUR_FK_KEY)
VALUES (p_equipment_fk_key, p_shift_workday_fk_key, p_from_date, p_to_date, p_status,
Nvl(p_system_fk_key,v_unassigned_string),
p_user_dim1_fk_key, p_user_dim2_fk_key, p_user_dim3_fk_key, p_user_dim4_fk_key, p_user_dim5_fk_key, p_user_attr1,
p_user_attr2, p_user_attr3, p_user_attr4, p_user_attr5, p_user_measure1, p_user_measure2, p_user_measure3,
p_user_measure4, p_user_measure5,SYSDATE,SYSDATE,Nvl(p_system_fk_key,v_unassigned_string),
Nvl(p_system_fk_key,v_unassigned_string),NULL,NULL,NULL,NULL,NULL,p_hour_fk_key);
USING (select p_equipment_fk_key p_equipment_fk_key, p_shift_workday_fk_key p_shift_workday_fk_key,
p_hour_fk_key p_hour_fk_key, p_from_date p_from_date, p_to_date p_to_date, p_status p_status,
p_system_fk_key p_system_fk_key,p_user_dim1_fk_key p_user_dim1_fk_key,
p_user_dim2_fk_key p_user_dim2_fk_key, p_user_dim3_fk_key p_user_dim3_fk_key,
p_user_dim4_fk_key p_user_dim4_fk_key, p_user_dim5_fk_key p_user_dim5_fk_key,p_user_attr1 p_user_attr1,
p_user_attr2 p_user_attr2, p_user_attr3 p_user_attr3, p_user_attr4 p_user_attr4,
p_user_attr5 p_user_attr5, p_user_measure1 p_user_measure1, p_user_measure2 p_user_measure2,
p_user_measure3 p_user_measure3,p_user_measure4 p_user_measure4, p_user_measure5 p_user_measure5 FROM dual) var
ON (stat.equipment_fk_key = var.p_equipment_fk_key AND
stat.shift_workday_fk_key = var.p_shift_workday_fk_key AND
stat.hour_fk_key = var.p_hour_fk_key AND
stat.from_date = var.p_from_date)
WHEN MATCHED THEN
UPDATE SET To_Date = p_hour_to_time, status = p_status, system_fk_key = Nvl(p_system_fk_key,v_unassigned_string),
user_dim1_fk_key = p_user_dim1_fk_key, user_dim2_fk_key = p_user_dim2_fk_key,
user_dim3_fk_key = p_user_dim3_fk_key, user_dim4_fk_key = p_user_dim4_fk_key,
user_dim5_fk_key = p_user_dim1_fk_key, user_attr1 = p_user_attr1,user_attr2 =p_user_attr2,
user_attr3 = p_user_attr3, user_attr4 = p_user_attr4, user_attr5 = p_user_attr5,
user_measure1 = p_user_measure1, user_measure2 = p_user_measure2,
user_measure3 = p_user_measure3, user_measure4 = p_user_measure4,
user_measure5 = p_user_measure5, last_update_date = SYSDATE,last_update_system_id =p_system_fk_key
WHEN NOT MATCHED THEN
INSERT (stat.EQUIPMENT_FK_KEY, stat.SHIFT_WORKDAY_FK_KEY, stat.FROM_DATE, stat.TO_DATE,
stat.STATUS, stat.SYSTEM_FK_KEY, stat.USER_DIM1_FK_KEY, stat.USER_DIM2_FK_KEY, stat.USER_DIM3_FK_KEY,
stat.USER_DIM4_FK_KEY, stat.USER_DIM5_FK_KEY, stat.USER_ATTR1, stat.USER_ATTR2, stat.USER_ATTR3,
stat.USER_ATTR4, stat.USER_ATTR5, stat.USER_MEASURE1, stat.USER_MEASURE2, stat.USER_MEASURE3,
stat.USER_MEASURE4, stat.USER_MEASURE5, stat.CREATION_DATE, stat.LAST_UPDATE_DATE, stat.CREATION_SYSTEM_ID,
stat.LAST_UPDATE_SYSTEM_ID, stat.CREATED_BY, stat.LAST_UPDATE_LOGIN, stat.LAST_UPDATED_BY,
stat.EXPECTED_UP_TIME, stat.STATUS_TYPE, stat.HOUR_FK_KEY)
VALUES (p_equipment_fk_key, p_shift_workday_fk_key, p_from_date, p_hour_to_time, p_status,
Nvl(p_system_fk_key,v_unassigned_string),
p_user_dim1_fk_key, p_user_dim2_fk_key, p_user_dim3_fk_key, p_user_dim4_fk_key, p_user_dim5_fk_key, p_user_attr1,
p_user_attr2, p_user_attr3, p_user_attr4, p_user_attr5, p_user_measure1, p_user_measure2, p_user_measure3,
p_user_measure4, p_user_measure5,SYSDATE,SYSDATE,Nvl(p_system_fk_key,v_unassigned_string),
Nvl(p_system_fk_key,v_unassigned_string),NULL,NULL,NULL,NULL,NULL,p_hour_fk_key);
SELECT FLOOR((p_to_date -p_hour_to_time)*24) INTO v_count FROM DUAL;
SELECT LEAD_HOUR,LEAD_FROM_DATE,LEAD_TO_DATE, LEAST(p_to_date, LEAD_TO_DATE)
INTO v_next_hour_fk_key, v_next_hour_from_time, v_next_hour_to_time, v_least_to_date
FROM(
SELECT HOUR_PK,LEAD( HOUR_PK_KEY ,i) OVER(ORDER BY FROM_TIME ) LEAD_HOUR, LEAD( FROM_TIME,i) OVER(ORDER BY FROM_TIME ) LEAD_FROM_DATE,
LEAD( TO_TIME ,i) OVER(ORDER BY FROM_TIME) LEAD_TO_DATE FROM MTH_HOUR_D)
WHERE HOUR_PK = p_hour_fk;
USING (select p_equipment_fk_key p_equipment_fk_key, p_shift_workday_fk_key p_shift_workday_fk_key,
p_hour_fk_key p_hour_fk_key, p_from_date p_from_date, p_to_date p_to_date, p_status p_status,
p_system_fk_key p_system_fk_key,p_user_dim1_fk_key p_user_dim1_fk_key,
p_user_dim2_fk_key p_user_dim2_fk_key, p_user_dim3_fk_key p_user_dim3_fk_key,
p_user_dim4_fk_key p_user_dim4_fk_key, p_user_dim5_fk_key p_user_dim5_fk_key,
p_user_attr1 p_user_attr1,p_user_attr2 p_user_attr2, p_user_attr3 p_user_attr3,
p_user_attr4 p_user_attr4, p_user_attr5 p_user_attr5, p_user_measure1 p_user_measure1,
p_user_measure2 p_user_measure2, p_user_measure3 p_user_measure3,p_user_measure4 p_user_measure4,
p_user_measure5 p_user_measure5,v_next_hour_fk_key v_next_hour_fk_key,
v_next_hour_from_time v_next_hour_from_time,v_next_hour_to_time v_next_hour_to_time FROM dual) var
ON (stat.equipment_fk_key = var.p_equipment_fk_key AND
stat.shift_workday_fk_key = var.p_shift_workday_fk_key AND
stat.hour_fk_key = var.v_next_hour_fk_key AND
stat.from_date = var.v_next_hour_from_time)
WHEN MATCHED THEN
UPDATE SET To_Date = least(p_to_date,v_next_hour_to_time), status = p_status,
system_fk_key = Nvl(p_system_fk_key,v_unassigned_string),
user_dim1_fk_key = p_user_dim1_fk_key, user_dim2_fk_key = p_user_dim2_fk_key,
user_dim3_fk_key = p_user_dim3_fk_key, user_dim4_fk_key = p_user_dim4_fk_key,
user_dim5_fk_key = p_user_dim1_fk_key, user_attr1 = p_user_attr1,user_attr2 =p_user_attr2,
user_attr3 = p_user_attr3, user_attr4 = p_user_attr4, user_attr5 = p_user_attr5,
user_measure1 = p_user_measure1, user_measure2 = p_user_measure2,
user_measure3 = p_user_measure3, user_measure4 = p_user_measure4,
user_measure5 = p_user_measure5, last_update_date = SYSDATE,last_update_system_id =p_system_fk_key
WHEN NOT MATCHED THEN
INSERT (stat.EQUIPMENT_FK_KEY, stat.SHIFT_WORKDAY_FK_KEY, stat.FROM_DATE, stat.TO_DATE,
stat.STATUS, stat.SYSTEM_FK_KEY, stat.USER_DIM1_FK_KEY, stat.USER_DIM2_FK_KEY, stat.USER_DIM3_FK_KEY,
stat.USER_DIM4_FK_KEY, stat.USER_DIM5_FK_KEY, stat.USER_ATTR1, stat.USER_ATTR2, stat.USER_ATTR3,
stat.USER_ATTR4, stat.USER_ATTR5, stat.USER_MEASURE1, stat.USER_MEASURE2, stat.USER_MEASURE3,
stat.USER_MEASURE4, stat.USER_MEASURE5, stat.CREATION_DATE, stat.LAST_UPDATE_DATE, stat.CREATION_SYSTEM_ID,
stat.LAST_UPDATE_SYSTEM_ID, stat.CREATED_BY, stat.LAST_UPDATE_LOGIN, stat.LAST_UPDATED_BY,
stat.EXPECTED_UP_TIME, stat.STATUS_TYPE, stat.HOUR_FK_KEY)
VALUES (p_equipment_fk_key, p_shift_workday_fk_key, v_next_hour_from_time,
least(p_to_date,v_next_hour_to_time), p_status,Nvl(p_system_fk_key,v_unassigned_string),
p_user_dim1_fk_key, p_user_dim2_fk_key, p_user_dim3_fk_key, p_user_dim4_fk_key, p_user_dim5_fk_key, p_user_attr1,
p_user_attr2, p_user_attr3, p_user_attr4, p_user_attr5, p_user_measure1, p_user_measure2, p_user_measure3,
p_user_measure4, p_user_measure5,SYSDATE,SYSDATE,Nvl(p_system_fk_key,v_unassigned_string),
Nvl(p_system_fk_key,v_unassigned_string),NULL,NULL,NULL,NULL,NULL,v_next_hour_fk_key);
l_last_update_system_id NUMBER ;
l_last_update_date DATE ;
select start_time ,end_time ,graveyard ,shift_num , shift_name,shift_type
-- into l_start_time, l_end_time, l_graveyard, l_shift_num,l_line_num,l_shift_name,l_shift_type
from
mth_site_shift_definitions where plant_fk_key=p_plant_fk_key;
SELECT shift_date
FROM mth_shift_reference_mv
WHERE PLANT_FK_KEY = p_plant_key;
SELECT compile_state
FROM user_mviews
WHERE mview_name = 'MTH_SHIFT_REFERENCE_MV';
--DELETE FROM mth_workday_shifts_D WHERE plant_fk_key = l_plant_fk_key and shift_date>=l_start_date ;
DELETE FROM mth_workday_shifts_D WHERE plant_fk_key = l_plant_fk_key and Trunc(from_date)>=l_start_date AND Trunc(from_date)<=l_end_date;
DELETE FROM mth_workday_shifts_D WHERE plant_fk_key = l_plant_fk_key and shift_date>=l_start_date AND shift_date<=l_end_date;
DELETE FROM mth_equipment_shifts_d WHERE availability_date>=l_start_date AND availability_date<=l_end_date
AND equipment_fk_key IN (SELECT DISTINCT(Nvl(a.equipment_fk_key,0))
FROM mth_equipment_shifts_d a,mth_equipments_d b WHERE b.equipment_pk_key=a.equipment_fk_key AND b.plant_fk_key =l_plant_fk_key
UNION ALL
SELECT distinct(Nvl(a.equipment_fk_key,0) )
FROM mth_equipment_shifts_d a,mth_resources_d b WHERE b.resource_pk_key=a.equipment_fk_key AND b.plant_fk_key =l_plant_fk_key
UNION ALL
SELECT distinct(Nvl(a.equipment_fk_key,0) )
FROM mth_equipment_shifts_d a,mth_plants_d b WHERE b.plant_pk_key=a.equipment_fk_key AND b.plant_pk_key =l_plant_fk_key
UNION ALL
SELECT distinct(Nvl(a.equipment_fk_key,0) )
FROM mth_equipment_shifts_d a,mth_equip_entities_mst b WHERE b.entity_pk_key=a.equipment_fk_key AND b.plant_fk_key =l_plant_fk_key
);
select plant_pk into l_plant_pk from mth_plants_d where plant_pk_key=l_plant_fk_key;
--SELECT organization_code INTO l_organization_code FROM mth_organizations_l WHERE plant_fk_key =
--l_plant_fk_key;
l_last_update_system_id := -99999;
l_last_update_date := l_sysdate;
INSERT INTO mth_workday_shifts_D(shift_workday_pk_key, shift_workday_pk,
shift_date,shift_date_julian,plant_fk_key,shift_type,
graveyard_shift,from_date,to_date, shift_num,shift_name,
source_org_code,system_fk_key,
creation_date,last_update_date,creation_system_id,
last_update_system_id)
VALUES(mth_workdays_shifts_s.nextval,
to_char(DECODE(GREATEST( l_start_time1, l_end_time1 ) ,
l_start_time1 , l_start_date ,
l_start_date) + ((TO_NUMBER(SUBSTR(l_start_time,1,2))+
(TO_NUMBER(SUBSTR(l_start_time,4,2))/60)+
(TO_NUMBER(SUBSTR(l_start_time,7,2))/3600))/24),
'yyyymmdd-hh24:mi:ss')||'-'||l_shift_num||'-'||l_plant_pk,
DECODE(GREATEST( l_start_time1, l_end_time1 ) , l_start_time1 ,
l_start_date + Decode( l_shift_def.graveyard, 'SED',1,0) , l_start_date) ,
TO_NUMBER(TO_CHAR( l_start_date ,'J')),
l_plant_fk_key,l_shift_type,l_graveyard,
l_start_date + ((TO_NUMBER(SUBSTR(l_start_time,1,2))+
(TO_NUMBER(SUBSTR(l_start_time,4,2))/60)+
(TO_NUMBER(SUBSTR(l_start_time,7,2))/3600))/24),
DECODE(GREATEST( l_start_time1, l_end_time1 ) , l_start_time1 ,
l_start_date + ((TO_NUMBER(SUBSTR(l_end_time,1,2))+
(TO_NUMBER(SUBSTR(l_end_time,4,2))/60)+
(TO_NUMBER(SUBSTR(l_end_time,7,2))/3600))/24)+ Decode( l_shift_def.graveyard, 'SED',1,1) ,
l_start_date + ((TO_NUMBER(SUBSTR(l_end_time,1,2))+
(TO_NUMBER(SUBSTR(l_end_time,4,2))/60)+
(TO_NUMBER(SUBSTR(l_end_time,7,2))/3600))/24)),
l_shift_num,l_shift_name,null, l_system_fk_key,
l_sysdate, l_sysdate,
l_creation_system_id,l_last_update_system_id);
INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
last_update_system_id) (
SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,a.from_date from_date,a.to_date To_Date,1 line_num,'Y',
b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
--INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
--l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
FROM mth_workday_shifts_d a ,
(
SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
FROM mth_equip_entities_mst
UNION ALL
SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
FROM mth_plants_d
UNION ALL
SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
FROM mth_resources_d
UNION ALL
SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
FROM mth_equipments_d
)b
WHERE b.site_id = a.plant_fk_key
AND a.plant_fk_key=l_plant_fk_key
AND shift_date>=l_start_date
AND shift_date<=l_end_date
AND UPPER(a.shift_type)='BOTH'
--AND a.line_num=l_line_num
AND a.shift_num=l_shift_num
AND a.shift_name=l_shift_name
AND l_shift_def.start_time=To_Char(from_date,'HH24:MI:SS')
AND l_shift_def.end_time=To_Char(To_Date,'HH24:MI:SS'));
INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
last_update_system_id) (
SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,a.from_date from_date,a.to_date To_Date,1 line_num,'Y',
b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
--INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
--l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
FROM mth_workday_shifts_d a ,
(
SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
FROM mth_equip_entities_mst
UNION ALL
SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
FROM mth_plants_d
UNION ALL
SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
FROM mth_resources_d
UNION ALL
SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
FROM mth_equipments_d
)b
WHERE b.site_id = a.plant_fk_key
AND a.plant_fk_key=l_plant_fk_key
AND shift_date>=l_start_date
AND shift_date<=l_end_date
AND b.production_entity='Y'
AND UPPER(a.shift_type)='PROD-SHIFT'
--AND a.line_num=l_line_num
AND a.shift_num=l_shift_num
AND a.shift_name=l_shift_name
AND l_shift_def.start_time=To_Char(from_date,'HH24:MI:SS')
AND l_shift_def.end_time=To_Char(To_Date,'HH24:MI:SS'));
INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
last_update_system_id) (
SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,a.from_date from_date,a.to_date To_Date,1 line_num,'Y',
b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
--INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
--l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
FROM mth_workday_shifts_d a ,
(
SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
FROM mth_equip_entities_mst
UNION ALL
SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
FROM mth_plants_d
UNION ALL
SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
FROM mth_resources_d
UNION ALL
SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
FROM mth_equipments_d
)b
WHERE b.site_id = a.plant_fk_key
AND a.plant_fk_key=l_plant_fk_key
AND shift_date>=l_start_date
AND shift_date<=l_end_date
AND b.production_entity='N'
AND UPPER(a.shift_type)='NON-PROD-SHIFT'
--AND a.line_num=l_line_num
AND a.shift_num=l_shift_num
AND a.shift_name=l_shift_name
AND l_shift_def.start_time=To_Char(from_date,'HH24:MI:SS')
AND l_shift_def.end_time=To_Char(To_Date,'HH24:MI:SS'));
l_last_update_system_id := -99999;
l_last_update_date := l_sysdate;
INSERT INTO mth_workday_shifts_D(shift_workday_pk_key, shift_workday_pk,
shift_date,shift_date_julian,plant_fk_key,shift_type,graveyard_shift,from_date,to_date, shift_num,shift_name,source_org_code,system_fk_key,
creation_date,last_update_date,creation_system_id,
last_update_system_id)
VALUES(mth_workdays_shifts_s.nextval,TO_CHAR(l_start_date,'yyyymmdd-hh24:mi:ss')||'-'||l_plant_pk|| "MTH_UTIL_PKG"."MTH_UA_GET_VAL"(),l_start_date ,TO_NUMBER(TO_CHAR( l_start_date ,'J')),
l_plant_fk_key,'BOTH',null,null,
null,null,FND_PROFILE.VALUE('MTH_CATCH_ALL_NAME'),null, l_system_fk_key,
l_sysdate, l_sysdate,l_creation_system_id,l_last_update_system_id);
INSERT INTO mth_equipment_shifts_d(equipment_fk_key,availability_date,shift_workday_fk_key,from_date,To_Date,line_num,availability_flag,entity_type,creation_date,last_update_date,creation_system_id,
last_update_system_id) (
SELECT b.entity_pk_key entity_pk_key,a.shift_date shift_date,a.shift_workday_pk_key shift_workday_fk_key,null,null,1 line_num,'Y',
b.entity_type entity_type,SYSDATE,SYSDATE,-1,-99999
--INTO l_site_id,l_entity_pk_key,l_entity_name,l_entity_type,l_production_entity,
--l_shift_workday_pk_key,l1_shift_date,l_from_date,l_to_date
FROM mth_workday_shifts_d a ,
(
SELECT plant_fk_key site_id, entity_pk_key, entity_name, entity_type, production_entity production_entity
FROM mth_equip_entities_mst
UNION ALL
SELECT plant_pk_key site_id, plant_pk_key entity_pk_key, plant_name entity_name, 'SITE', production_site production_entity
FROM mth_plants_d
UNION ALL
SELECT plant_fk_key site_id, resource_pk_key entity_pk_key, resource_name entity_name, 'RESOURCE', production_resource production_entity
FROM mth_resources_d
UNION ALL
SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key, equipment_name entity_name, 'EQUIPMENT', production_equipment production_entity
FROM mth_equipments_d
)b
WHERE b.site_id = a.plant_fk_key
AND a.plant_fk_key=l_plant_fk_key
AND shift_date>=l_start_date
AND shift_date<=l_end_date
AND UPPER(a.shift_type)='BOTH'
AND a.FROM_DATE IS NULL
AND a .To_Date IS NULL
--AND a.line_num=NULL
AND a.shift_num IS NULL
);
* Description :Insert the error row into the error with the error code *
* File Name :MTHSUSAB.PLS *
* Visibility :Private *
* Parameters :p_tag_value - tag value *
* p_is_number - 1 if tag value is number; 0 otherwise *
* Procedure :update_tag_to_latest_tab *
* Description :Update an existing the latest reading time and tag value *
* for a tag if table MTH_TAG_READINGS_LATEST already *
* has a entry for the tag. Otherwise, insert a new row *
* File Name :MTHUTILB.PLS *
* Visibility :Private *
* Parameters :p_tag_code - tag code *
* p_latest_reading_time - reading time of the latest *
* p_latest_tag_value - latest tag reading *
* p_lookup_entry_exist - whether the entry with the *
* same tag code exists in the *
* MTH_TAG_READINGS_LATEST or not *
* Return Value :None *
**************************************************************************** */
PROCEDURE update_tag_to_latest_tab(p_tag_code IN VARCHAR2,
p_latest_reading_time IN DATE,
p_latest_tag_value IN VARCHAR2,
p_lookup_entry_exist IN BOOLEAN)
IS
BEGIN
-- If the entry exists, do the update; otherwise, do the insert
UPDATE MTH_TAG_READINGS_LATEST
SET reading_time = p_latest_reading_time, tag_value = p_latest_tag_value
WHERE tag_code = p_tag_code;
INSERT INTO MTH_TAG_READINGS_LATEST
(TAG_CODE, READING_TIME, TAG_VALUE) VALUES
(p_tag_code, p_latest_reading_time, p_latest_tag_value);
END update_tag_to_latest_tab;
SELECT R.TAG_CODE, T.TAG_CODE TAG_MAST_TAG_CODE,R.READING_TIME + v_factor_time_zone READING_TIME, R.TAG_DATA,
Decode(DATA_TYPE, 'NUM', 1, 0) IS_NUMBER,
Decode(T.READING_TYPE, 'CHNG', 1, 0) AS IS_CUMULATIVE,
Decode(T.ORDER_TYPE, 'ASC', 1, 0) IS_ASSENDING,
T.INITIAL_VALUE, T.MAX_RESET_VALUE, R.GROUP_ID, R.CREATION_DATE, R.USER_ATTR1, R.USER_ATTR2, R.USER_ATTR3, R.USER_ATTR4, R.USER_ATTR5, R.USER_MEASURE1, R.USER_MEASURE2, R.USER_MEASURE3, R.USER_MEASURE4, R.USER_MEASURE5, R.QUALITY_FLAG
FROM MTH_TAG_READINGS_RAW R, MTH_TAG_MASTER T
WHERE R.PROCESSING_FLAG = p_processing_flag AND
R.TAG_CODE = T.TAG_CODE (+)
ORDER BY TAG_CODE, READING_TIME;
SELECT TAG_VALUE, READING_TIME
FROM MTH_TAG_READINGS_LATEST
WHERE TAG_CODE = p_tag_code;
update_tag_to_latest_tab(v_last_tag_code,
v_last_reading_time,
v_last_tag_value,
v_lookup_entry_exist);
INSERT INTO MTH_TAG_READINGS_UNPROCESS_ERR
(GROUP_ID, READING_TIME , TAG_CODE, TAG_DATA, CREATION_DATE, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4, USER_MEASURE5, QUALITY_FLAG, REPROCESSED_READY_YN, ERR_CODE)
VALUES (r_raw_data.GROUP_ID, r_raw_data.READING_TIME, r_raw_data.TAG_CODE, r_raw_data.TAG_DATA, r_raw_data.CREATION_DATE, r_raw_data.USER_ATTR1, r_raw_data.USER_ATTR2, r_raw_data.USER_ATTR3, r_raw_data.USER_ATTR4, r_raw_data.USER_ATTR5,
r_raw_data.USER_MEASURE1, r_raw_data.USER_MEASURE2, r_raw_data.USER_MEASURE3, r_raw_data.USER_MEASURE4, r_raw_data.USER_MEASURE5, r_raw_data.QUALITY_FLAG,'N', v_err_code);
INSERT INTO MTH_TAG_READINGS_RAW_PROCESSED (GROUP_ID, READING_TIME , TAG_CODE, TAG_DATA, CREATION_DATE, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4,
USER_MEASURE5, QUALITY_FLAG) VALUES ( r_raw_data.GROUP_ID, r_raw_data.READING_TIME, r_raw_data.TAG_CODE, v_incr_tag_value, r_raw_data.CREATION_DATE, r_raw_data.USER_ATTR1, r_raw_data.USER_ATTR2, r_raw_data.USER_ATTR3,
r_raw_data.USER_ATTR4, r_raw_data.USER_ATTR5, r_raw_data.USER_MEASURE1, r_raw_data.USER_MEASURE2, r_raw_data.USER_MEASURE3, r_raw_data.USER_MEASURE4, r_raw_data.USER_MEASURE5, r_raw_data.QUALITY_FLAG);
update_tag_to_latest_tab(v_last_tag_code,
v_last_reading_time,
v_last_tag_value,
v_lookup_entry_exist);
* Procedure :INSERT_OPEN_SALES_ORDERS*
* Description :The procedure will insert open sales orders in the table, passed as a parameter,
*in the partition that is empty. In the initial run, both the partitions will be empty,
*then the data will move to partition with partition key=1. The alternate partition key
*will be passed as the out parameter *
* File Name :MTHUTILB.PLS *
* Visibility :Public
* Modification log : *
* Author Date Change: Amrit Kaur 28-Jul-2011 Initial Creation *
*
******************************************************************************/
PROCEDURE insert_open_sales_orders(p_table_name IN VARCHAR2, p_current_processing_flag OUT NOCOPY NUMBER)
IS
v_stmt VARCHAR2(400);
SELECT db_link
FROM user_db_links
WHERE Upper(db_link) like Upper(source_service||'%@'||source_location);
/*The procedure will insert open sales orders in the table, passed as a parameter,
*in the partition that is empty. In the initial run, both the partitions will be empty,
*then the data will move to partition with partition key=1. The alternate partition key
*will be passed as the out parameter*/
v_stmt_no := 10;
v_stmt := 'SELECT COUNT(1) FROM '||p_table_name|| '@' || l_dblink || ' WHERE PROCESSING_FLAG = 1';
v_stmt := 'INSERT INTO '||p_table_name|| '@' || l_dblink || ' (EBS_HEADER_ID, PROCESSING_FLAG) (SELECT HEADER_ID, 2 FROM OE_ORDER_HEADERS_ALL@' ||l_dblink||' WHERE OPEN_FLAG = '||''''||'Y'||''''||')';
v_stmt := 'INSERT INTO '||p_table_name|| '@' || l_dblink || ' (EBS_HEADER_ID, PROCESSING_FLAG) (SELECT HEADER_ID, 1 FROM OE_ORDER_HEADERS_ALL@' ||l_dblink||' WHERE OPEN_FLAG = '||''''||'Y'||''''||')';
END insert_open_sales_orders;
SELECT MAX(FROM_DATE), MAX(TO_DATE)
INTO p_from_date, p_to_date
FROM MTH_RUN_LOG
WHERE FACT_TABLE = p_fact_table
AND (db_global_name = nvl(p_db_global_name,db_global_name)
OR db_global_name IS NULL)
AND (ebs_organization_id = nvl(p_ebs_organization_id,ebs_organization_id)
OR ebs_organization_id IS NULL)
AND (hub_organization_code = nvl(p_hub_organization_code,hub_organization_code)
OR hub_organization_code IS NULL);
select value
into mth_util_pkg.g_debug_file_dir
from v$parameter2
where name='utl_file_dir'
and rownum = 1;
* Description :The procedure will insert records in equipment shifts table by taking the data*
from workday shifts and mth_all_entities_v table *
* File Name :MTHUTILB.PLS *
* Visibility :Public
* Modification log : *
* Author Date Change: Amrit Kaur 27-Mar-2012 Initial Creation *
*
******************************************************************************/
PROCEDURE INCR_EQUIP_SHIFTS IS
v_log_from_date DATE;
DELETE
FROM MTH_EQUIPMENT_SHIFTS_D ESD
WHERE EXISTS (SELECT 1
FROM MTH_WORKDAY_SHIFTS_D WSD,MTH_ALL_ENTITIES_V MAV
WHERE WSD.LAST_UPDATE_DATE > v_log_from_date
AND (MAV.PRODUCTION_ENTITY ='Y'or MAV.PRODUCTION_ENTITY ='N')
AND WSD.PLANT_FK_KEY = MAV.SITE_ID
AND
ESD.EQUIPMENT_FK_KEY = MAV.ENTITY_PK_KEY AND
ESD.SHIFT_WORKDAY_FK_KEY = WSD.SHIFT_WORKDAY_PK_KEY ) ;
mth_util_pkg.log_msg('Number of rows deleted from MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_EQUIPMENT_SHIFTS_D (EQUIPMENT_FK_KEY,
AVAILABILITY_DATE,
SHIFT_WORKDAY_FK_KEY,
FROM_DATE,
TO_DATE,
AVAILABILITY_FLAG,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
LINE_NUM,
RESOURCE_FK_KEY,
RESOURCE_COST,
ENTITY_TYPE)
(SELECT
RES.EQUIPMENT_PK_KEY,
RES.SHIFT_DATE,
RES.SHIFT_WORKDAY_PK_KEY ,
RES.FROM_DATE,
RES.TO_DATE,
'Y' ,
v_log_to_date,
v_log_to_date,
v_unassigned_val,
v_unassigned_val ,
1 ,
COST.LEVEL9_LEVEL_KEY,
COST.COST,
RES.ENTITY_TYPE
FROM
( SELECT MAV.ENTITY_PK_KEY EQUIPMENT_PK_KEY,
WSD.SHIFT_WORKDAY_PK_KEY,
WSD.SHIFT_DATE,
WSD.FROM_DATE,
WSD.TO_DATE,
MAV.ENTITY_TYPE
FROM MTH_ALL_ENTITIES_V MAV,
MTH_WORKDAY_SHIFTS_D WSD
WHERE (WSD.LAST_UPDATE_DATE > v_log_from_date OR
( MAV.CREATION_DATE > v_log_from_date AND
TRUNC( MAV.CREATION_DATE ) <= WSD.SHIFT_DATE))
AND (MAV.PRODUCTION_ENTITY ='Y'or MAV.PRODUCTION_ENTITY ='N')
AND WSD.PLANT_FK_KEY = MAV.SITE_ID
) RES ,( SELECT MED.EQUIPMENT_FK_KEY,
MED.LEVEL9_LEVEL_KEY,
MED.EQUIPMENT_EFFECTIVE_DATE,
MED.EQUIPMENT_EXPIRATION_DATE,
MRC.RESOURCE_FK_KEY,
MRC.COST
FROM
MTH_EQUIPMENT_DENORM_D MED ,MTH_RESOURCE_COST_MV MRC
WHERE MED.EQUIPMENT_FK_KEY IS NOT NULL AND
MED.EQUIPMENT_HIERARCHY_KEY = -2
AND MED.LEVEL9_LEVEL_KEY = MRC.RESOURCE_FK_KEY (+))COST
WHERE
RES.EQUIPMENT_PK_KEY = COST.EQUIPMENT_FK_KEY (+) And
( RES.FROM_DATE >= COST.EQUIPMENT_EFFECTIVE_DATE (+) AND
RES.FROM_DATE <= NVL ( COST.EQUIPMENT_EXPIRATION_DATE (+) ,TO_DATE('4000-01-01', 'YYYY-MM-DD') )
) )
;
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIPMENT_SHIFTS_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
* Description :The procedure will insert valid records in workday shifts table and*
invalid records in workday shifts error table *
* File Name :MTHUTILB.PLS *
* Visibility :Public
* Modification log : *
* Author Date Change: Amrit Kaur 27-Mar-2012 Initial Creation *
*
******************************************************************************/
PROCEDURE MTH_WORKDAY_SHIFTS_SF IS
v_log_date DATE;
SELECT MTH_UTIL_PKG.GET_PROFILE_VAL('MTH_SHIFT_SETUP' ) INTO v_profile FROM DUAL ;
INSERT INTO MTH_WORKDAY_SHIFTS_STG
(SHIFT_DATE,
SOURCE_ORG_CODE,
USER_ATTR1,
USER_ATTR2,
USER_ATTR3,
USER_ATTR4,
USER_ATTR5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
SHIFT_WORKDAY_PK,
SYSTEM_FK,
FROM_DATE,
SHIFT_NUM,
TO_DATE,
SHIFT_NAME,
SHIFT_TYPE,
GRAVEYARD_SHIFT)
(SELECT SHIFT_DATE,
SOURCE_ORG_CODE,
USER_ATTR1 ,
USER_ATTR2,
USER_ATTR3,
USER_ATTR4,
USER_ATTR5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5 ,
SHIFT_WORKDAY_PK ,
SYSTEM_FK,
FROM_DATE,
SHIFT_NUM,
TO_DATE,
SHIFT_NAME,
SHIFT_TYPE,
GRAVEYARD_SHIFT
FROM MTH_WORKDAY_SHIFTS_ERR
WHERE UPPER( REPROCESS_READY_YN ) = 'Y'
)
;
mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_WORKDAY_SHIFTS_ERR
WHERE UPPER(REPROCESS_READY_YN) IN ('D','Y'); --Bug 14753663
mth_util_pkg.log_msg('Number of rows deleted in MTH_WORKDAY_SHIFTS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT shift_workday_pk,Count(shift_workday_pk) cnt
FROM mth_workday_shifts_stg
GROUP BY shift_workday_pk) dup
WHERE dup.cnt>1
AND dup.shift_workday_pk = stg.shift_workday_pk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'NFD '
WHERE stg.from_date IS NULL
AND stg.processing_flag = v_processing_flag;
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'NTD '
WHERE stg.to_date IS NULL
AND stg.processing_flag = v_processing_flag;
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'SDT '
WHERE stg.from_date= stg.to_date
AND stg.processing_flag = v_processing_flag;
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'DTE '
WHERE Nvl(stg.from_date,TO_DATE ('01-01-1900', 'DD-MM-YYYY')) >Nvl ( stg.To_Date, TO_DATE ('01-01-2999', 'DD-MM-YYYY') )
AND stg.processing_flag = v_processing_flag;
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'SFT '
WHERE stg.SHIFT_TYPE IS NOT NULL
AND EXISTS (SELECT * FROM (SELECT stg.SHIFT_TYPE SHIFT_TYPE ,fl.meaning
FROM mth_workday_shifts_stg stg,
( SELECT meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_SHIFT_TYPE'
AND fl.LANGUAGE = USERENV('LANG')
AND fl.ENABLED_FLAG ='Y' ) fl
WHERE stg.SHIFT_TYPE = fl.meaning(+)
AND stg.SHIFT_TYPE IS NOT NULL
) fls
WHERE fls.SHIFT_TYPE = stg.SHIFT_TYPE
AND stg.processing_flag = v_processing_flag
AND fls.meaning IS NULL);
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'GRA '
WHERE stg.graveyard_shift IS NOT NULL
AND EXISTS (SELECT * FROM (SELECT fl.meaning, stg.graveyard_shift
FROM mth_workday_shifts_stg stg,
( SELECT meaning FROM fnd_lookup_values fl WHERE fl.LOOKUP_TYPE ='MTH_GRAVEYARD_SHIFT_TYPE'
AND fl.LANGUAGE = USERENV('LANG')
AND fl.ENABLED_FLAG ='Y' )fl
WHERE stg.graveyard_shift = fl.meaning (+)
AND stg.graveyard_shift IS NOT NULL
)flg
WHERE flg.graveyard_shift = stg.graveyard_shift
AND stg.processing_flag = v_processing_flag
AND flg.meaning IS NULL);
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'PLT '
WHERE EXISTS (SELECT * FROM ( SELECT PLANTS.PLANT_PK_KEY,STG.SHIFT_WORKDAY_PK FROM MTH_WORKDAY_SHIFTS_STG STG
, ( SELECT MOL.ORGANIZATION_CODE,MP.PLANT_PK_KEY,MS.SYSTEM_PK FROM MTH_PLANTS_D MP,
MTH_ORGANIZATIONS_L MOL,
MTH_SYSTEMS_SETUP MS
WHERE NVL(MP.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
AND MP.PLANT_PK_KEY = MOL.PLANT_FK_KEY
AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY )PLANTS
WHERE STG.SOURCE_ORG_CODE = PLANTS.ORGANIZATION_CODE (+)
AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = PLANTS.SYSTEM_PK (+)
)plants1
WHERE stg.shift_workday_pk = plants1.shift_workday_pk
AND stg.processing_flag = v_processing_flag
AND plants1.plant_pk_key IS NULL);
UPDATE mth_workday_shifts_stg stag
SET stag.err_code = stag.err_code || 'OVP '
WHERE EXISTS (SELECT *
FROM
(SELECT CASE
WHEN (LAG(stg.from_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date)) IS NOT NULL
AND (LAG (stg.to_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date)) IS NOT NULL
AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date))
AND
stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date)))
OR
(stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date))
AND
stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY stg.source_org_code,stg.system_fk ORDER BY stg.from_date )))
)
THEN 1 END overlap,
stg.shift_workday_pk,
stg.from_date,
stg.to_date
FROM mth_workday_shifts_stg stg ) ovp
WHERE ovp.overlap = 1
AND stag.shift_workday_pk = ovp.shift_workday_pk
AND stag.from_date = ovp.from_date
AND stag.To_Date = ovp.To_Date);
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'SAE '
WHERE EXISTS (SELECT *
FROM
(SELECT stg.shift_workday_pk
FROM mth_workday_shifts_stg stg,
MTH_SHIFT_REFERENCE_MV MSV,
mth_plants_d plants,
mth_organizations_l mol,
mth_systems_setup ms
WHERE NVL(MSV.SHIFT_DATE,stg.shift_date-1) >= stg.shift_date
AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY
AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE (+)
AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK (+)
AND plants.PLANT_PK_KEY=MSV.PLANT_FK_KEY )sae
WHERE stg.shift_workday_pk = sae.shift_workday_pk);
UPDATE mth_workday_shifts_stg stg
SET stg.err_code = stg.err_code || 'PRF '
WHERE v_profile<>'External Source';
--Insert records into mth_workday_shifts_err
INSERT INTO MTH_WORKDAY_SHIFTS_ERR(REPROCESS_READY_YN,
SHIFT_DATE,
SOURCE_ORG_CODE,
USER_ATTR1,
USER_ATTR2,
USER_ATTR3,
USER_ATTR4,
USER_ATTR5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
SHIFT_WORKDAY_PK,
SYSTEM_FK,
FROM_DATE,
TO_DATE,
SHIFT_NUM,
SHIFT_NAME,
ERR_CODE,
SHIFT_TYPE,
GRAVEYARD_SHIFT)
(SELECT 'N',
SHIFT_DATE,
SOURCE_ORG_CODE,
USER_ATTR1,
USER_ATTR2,
USER_ATTR3,
USER_ATTR4,
USER_ATTR5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
SHIFT_WORKDAY_PK,
SYSTEM_FK,
FROM_DATE,
TO_DATE,
SHIFT_NUM,
SHIFT_NAME,
ERR_CODE,
SHIFT_TYPE,
GRAVEYARD_SHIFT
FROM mth_workday_shifts_stg
WHERE err_code IS NOT NULL
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_WORKDAY_SHIFTS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM mth_equipment_shifts_D
WHERE shift_workday_fk_key IN (SELECT shift_workday_pk_key FROM
mth_workday_shifts_D wsd,
mth_shift_reference_MV mv,
mth_workday_shifts_stg stg,
mth_plants_d plants ,
MTH_ORGANIZATIONS_L MOL,
MTH_SYSTEMS_SETUP MS
WHERE wsd.SHIFT_DATE > Nvl( mv.SHIFT_DATE ,wsd.shift_date-1)
And (TRUNC( wsd.SHIFT_DATE ) = TRUNC ( stg.SHIFT_DATE ) OR
TRUNC( wsd.FROM_DATE ) = TRUNC ( stg.SHIFT_DATE ) )
And wsd.PLANT_FK_KEY = mv.PLANT_FK_KEY(+)
And wsd.PLANT_FK_KEY = plants.PLANT_PK_KEY
AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY
AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK
AND stg.err_code IS NULL
And stg.SOURCE_ORG_CODE = nvl ( wsd.SOURCE_ORG_CODE , stg.SOURCE_ORG_CODE ));
mth_util_pkg.log_msg('Number of rows deleted from mth_equipment_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM mth_workday_shifts_D
WHERE shift_workday_pk_key IN (SELECT shift_workday_pk_key FROM
mth_workday_shifts_D wsd,
mth_shift_reference_MV mv,
mth_workday_shifts_stg stg,
mth_plants_d plants ,
MTH_ORGANIZATIONS_L MOL,
MTH_SYSTEMS_SETUP MS
WHERE wsd.SHIFT_DATE > Nvl( mv.SHIFT_DATE,wsd.shift_date-1)
And (TRUNC( wsd.SHIFT_DATE ) = TRUNC ( stg.SHIFT_DATE ) OR
TRUNC( wsd.FROM_DATE ) = TRUNC ( stg.SHIFT_DATE ) )
And wsd.PLANT_FK_KEY = mv.PLANT_FK_KEY(+)
And wsd.PLANT_FK_KEY = plants.PLANT_PK_KEY
AND NVL(plants.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
AND plants.PLANT_PK_KEY = MOL.PLANT_FK_KEY
AND MOL.SYSTEM_FK_KEY = MS.SYSTEM_PK_KEY
AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = MS.SYSTEM_PK
AND stg.err_code IS NULL
And stg.SOURCE_ORG_CODE = nvl ( wsd.SOURCE_ORG_CODE , stg.SOURCE_ORG_CODE ));
mth_util_pkg.log_msg('Number of rows deleted from mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_WORKDAY_SHIFTS_D
(SHIFT_WORKDAY_PK_KEY,
SHIFT_WORKDAY_PK,
SHIFT_DATE,
SHIFT_DATE_JULIAN,
PLANT_FK_KEY,
SYSTEM_FK_KEY,
USER_ATTR1,
USER_ATTR2,
USER_ATTR3,
USER_ATTR4,
USER_ATTR5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
FROM_DATE,
TO_DATE,
SHIFT_NUM,
SHIFT_NAME,
SOURCE_ORG_CODE,
SHIFT_TYPE,
GRAVEYARD_SHIFT)
(SELECT MTH_WORKDAYS_SHIFTS_S.NEXTVAL,
stg.SHIFT_WORKDAY_PK,
stg.SHIFT_DATE,
TO_NUMBER(TO_CHAR( stg.SHIFT_DATE ,'J')),
PD.plant_pk_key,
NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
STG.USER_ATTR1,
stg.USER_ATTR2,
stg.USER_ATTR3,
stg.USER_ATTR4,
stg.USER_ATTR5,
stg.USER_MEASURE1,
stg.USER_MEASURE2,
stg.USER_MEASURE3,
stg.USER_MEASURE4,
stg.USER_MEASURE5,
SYSDATE,
SYSDATE,
NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
stg.FROM_DATE,
stg.TO_DATE,
STG.SHIFT_NUM,
STG.SHIFT_NAME,
STG.SOURCE_ORG_CODE,
Decode(STG.SHIFT_TYPE,NULL,'PROD-SHIFT',FL2.LOOKUP_CODE),
DECODE(STG.GRAVEYARD_SHIFT,NULL,NULL,FL1.LOOKUP_CODE)
FROM
MTH_WORKDAY_SHIFTS_STG STG ,MTH_SYSTEMS_SETUP SP,MTH_PLANTS_D PD,MTH_ORGANIZATIONS_L MOL,FND_LOOKUP_VALUES FL1,FND_LOOKUP_VALUES FL2
WHERE NVL ( STG.SYSTEM_FK , MTH_UTIL_PKG.MTH_UA_GET_VAL ) = SP.SYSTEM_PK
AND NVL(PD.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
AND PD.PLANT_PK_KEY = MOL.PLANT_FK_KEY
AND MOL.SYSTEM_FK_KEY = SP.SYSTEM_PK_KEY
AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
AND NVL(STG.SYSTEM_FK, MTH_UTIL_PKG.MTH_UA_GET_VAL() ) = SP.SYSTEM_PK (+)
AND FL1.LOOKUP_TYPE ='MTH_GRAVEYARD_SHIFT_TYPE'
AND FL1.LANGUAGE = USERENV('LANG')
AND FL1.ENABLED_FLAG ='Y'
AND FL1.MEANING =NVL(STG.GRAVEYARD_SHIFT,'Shift End Date')
AND FL2.LOOKUP_TYPE ='MTH_SHIFT_TYPE'
AND FL2.LANGUAGE = USERENV('LANG')
AND FL2.ENABLED_FLAG ='Y'
AND NVL( STG.SHIFT_TYPE ,'Production Shift') = FL2.MEANING
AND STG.processing_flag = v_processing_flag
AND STG.ERR_CODE IS NULL );
mth_util_pkg.log_msg('Number of rows inserted in mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_WORKDAY_SHIFTS_D
(SHIFT_WORKDAY_PK_KEY,
SHIFT_WORKDAY_PK,
SHIFT_DATE,
SHIFT_DATE_JULIAN,
PLANT_FK_KEY,
SYSTEM_FK_KEY,
USER_ATTR1,
USER_ATTR2,
USER_ATTR3,
USER_ATTR4,
USER_ATTR5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
FROM_DATE,
TO_DATE,
SHIFT_NUM,
SHIFT_NAME,
SOURCE_ORG_CODE,
SHIFT_TYPE,
GRAVEYARD_SHIFT)
(SELECT MTH_WORKDAYS_SHIFTS_S.NEXTVAL,
TO_CHAR( CATCH_ALL.DAY,'yyyymmdd-hh24:mi:ss') ||'-'|| PD.PLANT_PK || MTH_UTIL_PKG.MTH_UA_GET_VAL(),
CATCH_ALL.DAY,
TO_NUMBER(TO_CHAR( CATCH_ALL.DAY ,'J')),
PD.plant_pk_key,
NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
SYSDATE,
NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
NVL( SP.SYSTEM_PK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
NULL,
NULL,
NULL,
MTH_UTIL_PKG.GET_PROFILE_VAL('MTH_CATCH_ALL_NAME'),
STG.SOURCE_ORG_CODE,
'BOTH' ,
NULL
FROM
( SELECT DISTINCT SHIFT_DATE,SOURCE_ORG_CODE,system_fk from
MTH_WORKDAY_SHIFTS_STG STG where STG.processing_flag = v_processing_flag
AND STG.ERR_CODE IS NULL
) STG ,MTH_SYSTEMS_SETUP SP,MTH_PLANTS_D PD,MTH_ORGANIZATIONS_L MOL,(SELECT DISTINCT DAY FROM(SELECT DAY FROM MTH_445_PERIOD_CALENDAR
UNION
SELECT DAY FROM MTH_GREGORIAN_CALENDAR
UNION
SELECT REPORT_DATE DAY FROM MTH_DAY_D )) CATCH_ALL
WHERE NVL ( STG.SYSTEM_FK , MTH_UTIL_PKG.MTH_UA_GET_VAL ) = SP.SYSTEM_PK
AND NVL(PD.TO_DATE,SYSDATE) >= TRUNC(SYSDATE)
AND PD.PLANT_PK_KEY = MOL.PLANT_FK_KEY
AND MOL.SYSTEM_FK_KEY = SP.SYSTEM_PK_KEY
AND STG.SOURCE_ORG_CODE = MOL.ORGANIZATION_CODE
AND CATCH_ALL.DAY = stg.SHIFT_DATE
);
mth_util_pkg.log_msg('Number of catch all rows inserted in mth_workday_shifts_D - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
v_stmt := 'SELECT ' || p_seq_name || '.nextval FROM dual';
v_stmt := 'SELECT ' || p_seq_name || '.nextval FROM dual';
SELECT LOOKUP_CODE
FROM FND_LOOKUP_Values
WHERE LOOKUP_TYPE=p_lookup_type
AND DESCRIPTION = 'TABLE'
AND LANGUAGE = userenv('LANG');
SELECT LOOKUP_CODE
FROM FND_LOOKUP_Values
WHERE LOOKUP_TYPE=p_lookup_type
AND DESCRIPTION = 'SEQUENCE'
AND LANGUAGE = userenv('LANG');
SELECT LOOKUP_CODE
FROM FND_LOOKUP_Values
WHERE LOOKUP_TYPE=p_lookup_type
AND DESCRIPTION = 'MVIEW'
AND LANGUAGE = userenv('LANG');
DELETE FROM MTH_RUN_LOG rl
WHERE exists (
SELECT 1
FROM FND_LOOKUP_VALUES lv
WHERE lv.LOOKUP_TYPE=v_lookup_type
AND lv.DESCRIPTION = 'TABLE'
AND lv.LANGUAGE = userenv('LANG')
AND lv.LOOKUP_CODE like rl.FACT_TABLE || '%');
SELECT c.DATA_DEFAULT
into v_flag
FROM user_synonyms s
LEFT OUTER JOIN
(all_editioning_views ev
INNER JOIN all_editioning_view_cols evc
ON evc.owner = ev.owner
AND evc.view_name = ev.view_name)
ON ev.view_name = s.table_name
AND ev.owner = s.table_owner
INNER JOIN all_tab_columns c
ON Nvl(ev.OWNER,s.table_owner) = c.owner
AND Nvl(ev.table_name,s.table_name) = c.table_name
AND Nvl(evc.TABLE_COLUMN_NAME,c.COLUMN_NAME) = c.COLUMN_NAME
WHERE s.synonym_name = p_table_name
AND Nvl(evc.TABLE_COLUMN_NAME,c.COLUMN_NAME) = 'PROCESSING_FLAG';