[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Count(1)
INTO v_tag_reading_count
FROM mth_tag_readings mtr,
mth_entities mte,
mth_run_log mrl
WHERE mtr.mth_entity = mte.id
AND mte.mth_alias = 'Status'
AND mrl.fact_table = 'MTH_EQUIP_STATUSES'
AND mtr.last_update_date > mrl.from_date;
SELECT Count(1)
INTO v_output_count
FROM mth_equip_statuses meo,
mth_run_log mrl
WHERE mrl.fact_table = 'MTH_EQUIP_STATUS_SUMMARY'
AND meo.last_update_date > mrl.from_date;
SELECT mview_name
BULK COLLECT
INTO v_compile_state
FROM dba_mviews
WHERE mview_name IN ('MTH_RESOURCE_COST_MV')
AND owner = sys_context('USERENV','CURRENT_SCHEMA')
AND compile_state <> 'VALID' ;
DELETE FROM MTH_EQUIP_STATUSES;
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_EQUIP_STATUSES (
EQUIPMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
FROM_DATE,
TO_DATE,
STATUS,
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,
READING_TIME
)
SELECT r.EQUIPMENT_FK_KEY,
s.SHIFT_WORKDAY_FK_KEY,
s.HOUR_PK_KEY HOUR_FK_KEY,
Greatest(s.FROM_DATE,r.FROM_DATE) AS FROM_DATE,
Least(s.TO_DATE,r.TO_DATE) AS TO_DATE,
r.TAG_DATA STATUS,
v_unassigned_val as SYSTEM_FK_KEY,
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,
SYSDATE CREATION_DATE,
SYSDATE LAST_UPDATE_DATE,
v_unassigned_val as CREATION_SYSTEM_ID,
v_unassigned_val as LAST_UPDATE_SYSTEM_ID,
r.FROM_DATE
FROM (SELECT r.READING_TIME FROM_DATE,
Lead(r.READING_TIME) over (PARTITION BY r.EQUIPMENT_FK_KEY ORDER BY r.READING_TIME) - 1/(24*60*60) AS To_Date,
r.EQUIPMENT_FK_KEY,
r.TAG_DATA,
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
FROM (SELECT readings.READING_TIME,
readings.EQUIPMENT_FK_KEY,
readings.TAG_DATA,
readings.USER_ATTR1,
readings.USER_ATTR2,
readings.USER_ATTR3,
readings.USER_ATTR4,
readings.USER_ATTR5,
readings.USER_MEASURE1,
readings.USER_MEASURE2,
readings.USER_MEASURE3,
readings.USER_MEASURE4,
readings.USER_MEASURE5 ,
readings.mth_entity
FROM mth_tag_readings readings
WHERE readings.EQUIPMENT_FK_KEY IS NOT NULL AND
readings.HOUR_FK_KEY IS NOT NULL AND
-- readings.PROCESSED_FLAG = 0 AND
readings.LAST_UPDATE_DATE <= v_log_date AND
readings.TAG_DATA IS NOT NULL
UNION
SELECT err.READING_TIME,
err.EQUIPMENT_FK_KEY,
err.TAG_DATA,
err.USER_ATTR1,
err.USER_ATTR2,
err.USER_ATTR3,
err.USER_ATTR4,
err.USER_ATTR5,
err.USER_MEASURE1,
err.USER_MEASURE2,
err.USER_MEASURE3,
err.USER_MEASURE4,
err.USER_MEASURE5 ,
err.mth_entity
FROM mth_tag_readings_err err
WHERE err.EQUIPMENT_FK_KEY IS NOT NULL AND
err.HOUR_FK_KEY IS NOT NULL AND
--err.PROCESSED_FLAG = 0 AND
err.EQUIPMENT_STATUS = 'ACTIVE' AND
err.TAG_DATA IS NOT NULL )r,
MTH_ENTITIES e
WHERE e.mth_alias = 'Status'
AND e.id = r.mth_entity
AND r.TAG_DATA IS NOT NULL
) r,
MTH_EQUIP_SHIFT_HR_V s
WHERE ( ( r.FROM_DATE BETWEEN s.from_date AND s.to_date )
OR ( s.from_date BETWEEN r.FROM_DATE AND nvl(r.To_Date,r.from_date) ) )
AND r.EQUIPMENT_FK_KEY = s.EQUIPMENT_FK_KEY;
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
update MTH_TAG_READINGS t
set PROCESSED_FLAG = 1,
last_update_date=sysdate
where exists (
select 1
from mth_entities m
where t.MTH_ENTITY = m.ID
--AND t.PROCESSED_FLAG = 0
AND t.EQUIPMENT_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND m.MTH_ALIAS = 'Status'
AND t.LAST_UPDATE_DATE <= v_log_date
AND t.TAG_DATA IS NOT NULL
);
mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
update MTH_TAG_READINGS_ERR t
set PROCESSED_FLAG = 1
where exists (
select 1
from mth_entities m
where t.MTH_ENTITY = m.ID
-- AND t.PROCESSED_FLAG = 0
AND t.EQUIPMENT_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND m.MTH_ALIAS = 'Status'
AND t.EQUIPMENT_STATUS = 'ACTIVE'
AND t.TAG_DATA IS NOT NULL
);
mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
( SELECT r.EQUIPMENT_FK_KEY,
s.SHIFT_WORKDAY_FK_KEY,
s.HOUR_PK_KEY HOUR_FK_KEY,
Greatest(s.FROM_DATE,r.FROM_DATE) AS FROM_DATE,
Least(s.TO_DATE,r.TO_DATE) AS TO_DATE,
r.TAG_DATA STATUS,
v_unassigned_val as SYSTEM_FK_KEY,
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.FROM_DATE Reading_time
FROM (SELECT r.READING_TIME FROM_DATE,
Lead(r.READING_TIME) over (PARTITION BY r.EQUIPMENT_FK_KEY ORDER BY r.READING_TIME) - 1/(24*60*60) AS To_Date,
r.EQUIPMENT_FK_KEY,
r.TAG_DATA,
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
FROM (SELECT readings.READING_TIME,
readings.EQUIPMENT_FK_KEY,
readings.TAG_DATA,
readings.USER_ATTR1,
readings.USER_ATTR2,
readings.USER_ATTR3,
readings.USER_ATTR4,
readings.USER_ATTR5,
readings.USER_MEASURE1,
readings.USER_MEASURE2,
readings.USER_MEASURE3,
readings.USER_MEASURE4,
readings.USER_MEASURE5,
readings.mth_entity
FROM mth_tag_readings readings
WHERE readings.EQUIPMENT_FK_KEY IS NOT NULL AND
readings.HOUR_FK_KEY IS NOT NULL AND
( ( readings.PROCESSED_FLAG = 0 AND readings.LAST_UPDATE_DATE > v_log_from_date and readings.LAST_UPDATE_DATE<=v_log_to_date )
OR readings.reading_time IN (SELECT i.FROM_DATE
FROM mth_equip_statuses i
WHERE i.To_Date IS NULL
AND i.equipment_fk_key =readings.equipment_fk_key
)) AND
readings.TAG_DATA IS NOT NULL
UNION
SELECT err.READING_TIME,
err.EQUIPMENT_FK_KEY,
err.TAG_DATA,
err.USER_ATTR1,
err.USER_ATTR2,
err.USER_ATTR3,
err.USER_ATTR4,
err.USER_ATTR5,
err.USER_MEASURE1,
err.USER_MEASURE2,
err.USER_MEASURE3,
err.USER_MEASURE4,
err.USER_MEASURE5 ,
err.mth_entity
FROM mth_tag_readings_err err
WHERE err.EQUIPMENT_FK_KEY IS NOT NULL AND
err.HOUR_FK_KEY IS NOT NULL AND
( err.PROCESSED_FLAG = 0
OR err.reading_time IN (SELECT i.FROM_DATE
FROM mth_equip_statuses i
WHERE i.To_Date IS NULL
AND i.equipment_fk_key =err.equipment_fk_key
)) AND
err.EQUIPMENT_STATUS = 'ACTIVE' AND
err.TAG_DATA IS NOT NULL )r,
MTH_ENTITIES e
WHERE e.mth_alias = 'Status'
AND e.id = r.mth_entity
AND r.TAG_DATA IS NOT NULL
ORDER BY r.equipment_fk_key, r.reading_time
) r,
MTH_EQUIP_SHIFT_HR_V s
WHERE ( ( r.FROM_DATE BETWEEN s.from_date AND s.to_date )
OR ( s.from_date BETWEEN r.FROM_DATE AND nvl(r.To_Date,r.from_date) ) )
AND r.EQUIPMENT_FK_KEY = s.EQUIPMENT_FK_KEY
)tr
ON (o.EQUIPMENT_FK_KEY = tr.EQUIPMENT_FK_KEY
AND o.SHIFT_WORKDAY_FK_KEY = tr.SHIFT_WORKDAY_FK_KEY
AND o.HOUR_FK_KEY = tr.HOUR_FK_KEY
AND o.FROM_DATE = tr.FROM_DATE)
WHEN MATCHED THEN
UPDATE SET
o.TO_DATE = tr.TO_DATE,
o.STATUS = tr.STATUS,
o.USER_ATTR1 = tr.USER_ATTR1,
o.USER_ATTR2 = tr.USER_ATTR2,
o.USER_ATTR3 = tr.USER_ATTR3,
o.USER_ATTR4 = tr.USER_ATTR4,
o.USER_ATTR5 = tr.USER_ATTR5,
o.USER_MEASURE1 = tr.USER_MEASURE1,
o.USER_MEASURE2 = tr.USER_MEASURE2,
o.USER_MEASURE3 = tr.USER_MEASURE3,
o.USER_MEASURE4 = tr.USER_MEASURE4,
o.USER_MEASURE5 = tr.USER_MEASURE5,
o.LAST_UPDATE_DATE =SYSDATE,
o.LAST_UPDATE_SYSTEM_ID = v_unassigned_val
WHEN NOT MATCHED THEN
INSERT (
o.EQUIPMENT_FK_KEY,
o.SHIFT_WORKDAY_FK_KEY,
o.HOUR_FK_KEY,
o.FROM_DATE,
o.TO_DATE,
o.STATUS,
o.SYSTEM_FK_KEY,
o.USER_ATTR1,
o.USER_ATTR2,
o.USER_ATTR3,
o.USER_ATTR4,
o.USER_ATTR5,
o.USER_MEASURE1,
o.USER_MEASURE2,
o.USER_MEASURE3,
o.USER_MEASURE4,
o.USER_MEASURE5,
o.CREATION_DATE,
o.LAST_UPDATE_DATE,
o.CREATION_SYSTEM_ID,
o.LAST_UPDATE_SYSTEM_ID ,
o.READING_TIME
)
VALUES
(
tr.EQUIPMENT_FK_KEY,
tr.SHIFT_WORKDAY_FK_KEY,
tr.HOUR_FK_KEY,
tr.FROM_DATE,
tr.TO_DATE,
tr.STATUS,
v_unassigned_val,
tr.USER_ATTR1,
tr.USER_ATTR2,
tr.USER_ATTR3,
tr.USER_ATTR4,
tr.USER_ATTR5,
tr.USER_MEASURE1,
tr.USER_MEASURE2,
tr.USER_MEASURE3,
tr.USER_MEASURE4,
tr.USER_MEASURE5,
SYSDATE,
SYSDATE,
v_unassigned_val,
v_unassigned_val,
tr.Reading_time
);
update MTH_TAG_READINGS t
set PROCESSED_FLAG = 1,
last_update_date=sysdate
where exists (
select 1
from mth_entities m
where t.MTH_ENTITY = m.ID
AND t.PROCESSED_FLAG = 0
AND t.EQUIPMENT_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND t.TAG_DATA IS NOT NULL
AND m.MTH_ALIAS = 'Status'
AND t.LAST_UPDATE_DATE BETWEEN v_log_from_date and v_log_to_date);
mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
update MTH_TAG_READINGS_ERR t
set PROCESSED_FLAG = 1
where exists (
select 1
from mth_entities m
where t.MTH_ENTITY = m.ID
AND t.PROCESSED_FLAG = 0
AND t.EQUIPMENT_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND m.MTH_ALIAS = 'Status'
AND t.EQUIPMENT_STATUS = 'ACTIVE'
AND t.TAG_DATA IS NOT NULL
);
mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_STATUSES o
WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
AND ( ( o.FROM_DATE BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.FROM_DATE) )
OR ( p_recal_from_date BETWEEN o.FROM_DATE AND nvl(o.To_Date,p_recal_from_date) ) ) ;
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
( SELECT r.EQUIPMENT_FK_KEY,
s.SHIFT_WORKDAY_FK_KEY,
s.HOUR_PK_KEY HOUR_FK_KEY,
Greatest(s.FROM_DATE,r.FROM_DATE) AS FROM_DATE,
Least(s.TO_DATE,r.TO_DATE) AS TO_DATE,
r.TAG_DATA STATUS,
v_unassigned_val as SYSTEM_FK_KEY,
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
FROM (SELECT r.READING_TIME FROM_DATE,
Lead(r.READING_TIME) over (PARTITION BY r.EQUIPMENT_FK_KEY ORDER BY r.READING_TIME) - 1/(24*60*60) AS To_Date,
r.EQUIPMENT_FK_KEY,
r.TAG_DATA,
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
FROM mth_tag_readings r,
MTH_ENTITIES e
WHERE e.mth_alias = 'Status'
AND e.id = r.mth_entity
AND r.EQUIPMENT_FK_KEY IS NOT NULL
AND r.HOUR_FK_KEY IS NOT NULL
AND r.TAG_DATA IS NOT NULL
AND r.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key, r.EQUIPMENT_FK_KEY)
AND ( ( r.READING_TIME BETWEEN p_recal_from_date AND p_recal_to_date
OR r.READING_TIME IN ((SELECT Max(i.READING_TIME)
FROM mth_tag_readings i , MTH_ENTITIES e
WHERE e.mth_alias = 'Status'
AND e.id = i.mth_entity
AND i.READING_TIME < p_recal_from_date
AND i.equipment_fk_key = r.equipment_fk_key)
, (SELECT Min(i.READING_TIME)
FROM mth_tag_readings i, MTH_ENTITIES e
WHERE e.mth_alias = 'Status'
AND e.id = i.mth_entity
AND i.READING_TIME > p_recal_to_date
AND i.equipment_fk_key = r.equipment_fk_key ) ) )
)
ORDER BY r.equipment_fk_key, r.reading_time
) r,
MTH_EQUIP_SHIFT_HR_V s
WHERE ( ( r.FROM_DATE BETWEEN s.from_date AND s.to_date )
OR ( s.from_date BETWEEN r.FROM_DATE AND nvl(r.To_Date,r.from_date) ) )
AND r.EQUIPMENT_FK_KEY = s.EQUIPMENT_FK_KEY
)tr
ON (o.EQUIPMENT_FK_KEY = tr.EQUIPMENT_FK_KEY
AND o.SHIFT_WORKDAY_FK_KEY = tr.SHIFT_WORKDAY_FK_KEY
AND o.HOUR_FK_KEY = tr.HOUR_FK_KEY
AND o.FROM_DATE = tr.FROM_DATE)
WHEN MATCHED THEN
UPDATE SET
o.STATUS = tr.STATUS,
o.LAST_UPDATE_DATE =SYSDATE,
o.LAST_UPDATE_SYSTEM_ID = v_unassigned_val
WHEN NOT MATCHED THEN
INSERT (
o.EQUIPMENT_FK_KEY,
o.SHIFT_WORKDAY_FK_KEY,
o.HOUR_FK_KEY,
o.FROM_DATE,
o.TO_DATE,
o.STATUS,
o.SYSTEM_FK_KEY,
o.USER_ATTR1,
o.USER_ATTR2,
o.USER_ATTR3,
o.USER_ATTR4,
o.USER_ATTR5,
o.USER_MEASURE1,
o.USER_MEASURE2,
o.USER_MEASURE3,
o.USER_MEASURE4,
o.USER_MEASURE5,
o.CREATION_DATE,
o.LAST_UPDATE_DATE,
o.CREATION_SYSTEM_ID,
o.LAST_UPDATE_SYSTEM_ID
)
VALUES
(
tr.EQUIPMENT_FK_KEY,
tr.SHIFT_WORKDAY_FK_KEY,
tr.HOUR_FK_KEY,
tr.FROM_DATE,
tr.TO_DATE,
tr.STATUS,
v_unassigned_val,
tr.USER_ATTR1,
tr.USER_ATTR2,
tr.USER_ATTR3,
tr.USER_ATTR4,
tr.USER_ATTR5,
tr.USER_MEASURE1,
tr.USER_MEASURE2,
tr.USER_MEASURE3,
tr.USER_MEASURE4,
tr.USER_MEASURE5,
SYSDATE,
SYSDATE,
v_unassigned_val,
v_unassigned_val
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
update MTH_TAG_READINGS t
set PROCESSED_FLAG = 1,
last_update_date=sysdate
where exists (
select 1
from mth_entities m
where t.MTH_ENTITY = m.ID
AND t.PROCESSED_FLAG = 0
AND t.EQUIPMENT_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND t.TAG_DATA IS NOT NULL
AND m.MTH_ALIAS = 'Status'
AND t.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,t.READING_TIME));
mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO mth_equip_statuses_stg(equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
downtime_reason_code)
(SELECT equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
downtime_reason_code
FROM mth_equip_statuses_err
WHERE reprocess_ready_yn = 'Y');
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_STATUSES_ERR
WHERE REPROCESS_READY_YN = 'Y';
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_STATUSES;
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
FROM mth_equip_statuses_stg
GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
WHERE dup.cnt>1
AND dup.equipment_fk = stg.equipment_fk
AND dup.shift_workday_fk = stg.shift_workday_fk
AND dup.from_date = stg.from_date
AND dup.To_Date = stg.To_Date
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'EQP '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'IEQ '
WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND med.status <> 'ACTIVE') eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'WDS '
WHERE stg.shift_workday_fk IS NOT NULL
AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg
WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
AND stg.shift_workday_fk IS NOT NULL) wds
WHERE wds.shift_workday_pk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'ESD '
WHERE NOT EXISTS ( SELECT * FROM
( SELECT mee.equipment_pk, mws.shift_workday_pk
FROM mth_equipments_d mee,
mth_equipment_shifts_d med,
mth_workday_shifts_d mws
WHERE mee.equipment_pk_key = med.equipment_fk_key
AND med.shift_workday_fk_key = mws.shift_workday_pk_key
AND med.entity_type = 'EQUIPMENT') esd
WHERE stg.equipment_fk = esd.equipment_pk
AND stg.shift_workday_fk = esd.shift_workday_pk
AND stg.processing_flag = v_processing_flag)
AND EXISTS ( SELECT * FROM
( SELECT mds.shift_workday_pk
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg
WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
AND stg.shift_workday_fk IS NOT NULL) wds
WHERE wds.shift_workday_pk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag)
AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD1 '
WHERE stg.user_dim1_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim1_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim1_fk = mue.entity_pk (+)
AND stg.user_dim1_fk IS NOT NULL) ud1
WHERE ud1.user_dim1_fk = stg.user_dim1_fk
AND stg.processing_flag = v_processing_flag
AND ud1.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD2 '
WHERE stg.user_dim2_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim2_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim2_fk = mue.entity_pk (+)
AND stg.user_dim2_fk IS NOT NULL) ud2
WHERE ud2.user_dim2_fk = stg.user_dim2_fk
AND stg.processing_flag = v_processing_flag
AND ud2.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD3 '
WHERE stg.user_dim3_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim3_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim3_fk = mue.entity_pk (+)
AND stg.user_dim3_fk IS NOT NULL) ud3
WHERE ud3.user_dim3_fk = stg.user_dim3_fk
AND stg.processing_flag = v_processing_flag
AND ud3.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD4 '
WHERE stg.user_dim4_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim4_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim4_fk = mue.entity_pk (+)
AND stg.user_dim4_fk IS NOT NULL) ud4
WHERE ud4.user_dim4_fk = stg.user_dim4_fk
AND stg.processing_flag = v_processing_flag
AND ud4.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD5 '
WHERE stg.user_dim5_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim5_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim5_fk = mue.entity_pk (+)
AND stg.user_dim5_fk IS NOT NULL) ud5
WHERE ud5.user_dim5_fk = stg.user_dim5_fk
AND stg.processing_flag = v_processing_flag
AND ud5.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'GAP '
WHERE EXISTS (SELECT *
FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
(Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c
WHERE c.Count >= 1
AND stg.from_date = c.from_date
AND stg.equipment_fk = c.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'FTD '
WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
AND stg.processing_flag = v_processing_flag;
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'DTR '
WHERE EXISTS (SELECT *
FROM
(SELECT flk.lookup_code,
flk.lookup_type,
stg.FROM_date,
stg.to_date,
stg.downtime_reason_code,
stg.status
FROM fnd_lookups flk,
mth_equip_statuses_stg stg
WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.downtime_reason_code = flk.lookup_code (+)
AND stg.status = 3) dtr
WHERE ((stg.downtime_reason_code = dtr.downtime_reason_code
AND stg.status = dtr.status
AND dtr.lookup_code IS NULL
AND dtr.from_date = stg.from_date
AND dtr.To_Date = stg.To_Date)
OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL))
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'IDR '
WHERE EXISTS (SELECT *
FROM
( SELECT flk.lookup_code,
flk.lookup_type,
stg.FROM_date,
stg.to_date,
stg.downtime_reason_code,
stg.status
FROM fnd_lookups flk,
mth_equip_statuses_stg stg
WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
AND stg.downtime_reason_code = flk.lookup_code (+)
AND stg.status = 2) dtr
WHERE stg.downtime_reason_code = dtr.downtime_reason_code
AND stg.status = dtr.status
AND dtr.lookup_code IS NULL
AND dtr.from_date = stg.from_date
AND dtr.To_Date = stg.To_Date
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'ITR '
WHERE EXISTS ( SELECT * FROM (
SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg,
mth_equipment_shifts_d mes,
mth_equipments_d med
WHERE stg.shift_workday_fk = mds.shift_workday_pk
AND stg.equipment_fk = med.equipment_pk
AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
AND med.equipment_pk_key = mes.equipment_fk_key
AND (stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND Upper(mes.entity_type) = 'EQUIPMENT'
AND stg.processing_flag = v_processing_flag) itr
WHERE itr.shift_workday_pk = stg.shift_workday_fk
AND itr.equipment_pk = stg.equipment_fk
AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date)
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'MSF '
WHERE EXISTS ( SELECT * FROM (
SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg,
mth_equipment_shifts_d mes,
mth_equipments_d med
WHERE stg.shift_workday_fk = mds.shift_workday_pk
AND stg.equipment_fk = med.equipment_pk
AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
AND med.equipment_pk_key = mes.equipment_fk_key
AND (((stg.from_date BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date))
OR ((stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date BETWEEN mes.from_date AND mes.To_Date)))
AND Upper(mes.entity_type) = 'EQUIPMENT'
AND stg.processing_flag = v_processing_flag) msf
WHERE msf.shift_workday_pk = stg.shift_workday_fk
AND msf.equipment_pk = stg.equipment_fk
AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date)))
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stag
SET stag.err_code = stag.err_code || 'OCSV '
WHERE EXISTS (SELECT *
FROM
(SELECT CASE
WHEN (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
AND (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
AND
stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)))
OR
(stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
AND
stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date )))
)
THEN 1 END overlap,
med.equipment_pk,
stg.from_date,
stg.to_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med
WHERE stg.equipment_fk = med.equipment_pk
AND stg.processing_flag = v_processing_flag) ovp
WHERE ovp.overlap = 1
AND stag.equipment_fk = ovp.equipment_pk
AND stag.from_date = ovp.from_date
AND stag.To_Date = ovp.To_Date
AND stag.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stag
SET stag.err_code = stag.err_code || 'OSTS '
WHERE EXISTS (SELECT *
FROM ( SELECT CASE
WHEN (stg.FROM_DATE >= sts.FROM_DATE
AND stg.FROM_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
OR (stg.TO_DATE >= sts.FROM_DATE
AND stg.TO_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
OR (stg.FROM_DATE < sts.FROM_DATE
AND stg.TO_DATE > NVL(sts.TO_DATE,stg.TO_DATE+1/86400))
THEN 1 END overlap ,
med.equipment_pk,
wds.shift_workday_pk,
stg.from_date,
stg.to_date
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts,
mth_equipments_d med,
mth_workday_shifts_d wds
WHERE stg.equipment_fk = med.equipment_pk
AND stg.shift_workday_fk = wds.shift_workday_pk
AND med.equipment_pk_key = sts.equipment_fk_key
AND wds.shift_workday_pk_key = sts.shift_workday_fk_key
AND stg.processing_flag = v_processing_flag) osts
WHERE osts.overlap = 1
AND stag.equipment_fk = osts.equipment_pk
AND stag.shift_workday_fk = osts.shift_workday_pk
AND stag.processing_flag = v_processing_flag
AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code ||'WRC '
WHERE NOT EXISTS ( SELECT *
FROM (SELECT stg.*
FROM mth_equip_statuses_stg stg,
MTH_EQUIPMENT_REASON_SETUP mer,
mth_equipments_d med
WHERE stg.equipment_fk = med.equipment_pk
AND med.equipment_pk_key = mer.equipment_fk_key
AND stg.downtime_reason_code = mer.reason_code
AND stg.status IN (3,2)
AND stg.downtime_reason_code IS NOT NULL) ers
WHERE ers.equipment_fk = stg.equipment_fk
AND ers.downtime_reason_code = stg.downtime_reason_code
AND ers.from_date = stg.from_date
AND Nvl(ers.To_Date,SYSDATE) = Nvl(stg.To_Date,SYSDATE)
AND ers.shift_workday_fk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag
)
AND stg.status IN (3,2)
AND stg.downtime_reason_code IS NOT NULL;
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code ||'STS '
WHERE stg.processing_flag = v_processing_flag
AND stg.status NOT IN (1,2,3,4);
--Insert records into mth_equip_statuses_err
INSERT INTO mth_equip_statuses_err(equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
reprocess_ready_yn,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
err_code,
downtime_reason_code)
(SELECT equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
'N',
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
err_code,
downtime_reason_code
FROM mth_equip_statuses_stg
WHERE err_code IS NOT NULL
AND processing_flag = v_processing_flag);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
--Insert records into mth_equip_statuses table
INSERT INTO mth_equip_statuses( equipment_fk_key,
shift_workday_fk_key,
from_date,
To_Date,
status,
system_fk_key,
user_dim1_fk_key,
user_dim2_fk_key,
user_dim3_fk_key,
user_dim4_fk_key,
user_dim5_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,
created_by,
last_updated_by,
last_update_login,
expected_up_time,
status_type,
hour_fk_key,
reading_time )
(SELECT med.EQUIPMENT_PK_KEY ,
wds.SHIFT_WORKDAY_PK_KEY ,
Greatest(stg.from_date,mhd.from_time),
Least(stg.To_Date,mhd.to_time) ,
stg.STATUS ,
Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
mue1.ENTITY_PK_KEY ,
mue2.ENTITY_PK_KEY ,
mue3.ENTITY_PK_KEY ,
mue4.ENTITY_PK_KEY ,
mue5.ENTITY_PK_KEY ,
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 ,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
null,
null,
null,
null,
null,
mhd.HOUR_PK_KEY,
stg.from_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_systems_setup mss,
mth_user_dim_entities_mst mue1,
mth_user_dim_entities_mst mue2,
mth_user_dim_entities_mst mue3,
mth_user_dim_entities_mst mue4,
mth_user_dim_entities_mst mue5,
fnd_lookups lkp,
mth_hour_d mhd
WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
AND mhd.to_time >= stg.from_date
AND mhd.from_time <= stg.to_date
AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+)
AND stg.err_code IS NULL
AND stg.processing_flag = v_processing_flag
UNION ALL
SELECT med.EQUIPMENT_PK_KEY ,
wds.SHIFT_WORKDAY_PK_KEY ,
Greatest(stg.from_date,mhd.from_time),
stg.To_Date,
stg.STATUS ,
Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
mue1.ENTITY_PK_KEY ,
mue2.ENTITY_PK_KEY ,
mue3.ENTITY_PK_KEY ,
mue4.ENTITY_PK_KEY ,
mue5.ENTITY_PK_KEY ,
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 ,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
null,
null,
null,
null,
null,
mhd.HOUR_PK_KEY,
stg.from_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_systems_setup mss,
mth_user_dim_entities_mst mue1,
mth_user_dim_entities_mst mue2,
mth_user_dim_entities_mst mue3,
mth_user_dim_entities_mst mue4,
mth_user_dim_entities_mst mue5,
fnd_lookups lkp,
mth_hour_d mhd
WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
AND stg.from_date BETWEEN mhd.from_time AND mhd.to_time
AND stg.To_Date IS NULL
AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.processing_flag = v_processing_flag
AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+) AND stg.err_code IS NULL );
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
EQUIPMENT_FK_KEY,
FROM_DATE,
To_Date,
REASON_CODE,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
reading_time,
hour_fk_key)
(SELECT 1 reason_type,
sts.equipment_fk_key,
sts.from_date,
sts.To_Date,
stg.downtime_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
null,
sts.reading_time,
sts.hour_fk_key
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts
WHERE stg.from_date = sts.reading_time
AND sts.status = stg.status
AND stg.status = 3
AND stg.processing_flag = v_processing_flag
AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE equipment_pk = stg.equipment_fk )
AND stg.err_code IS NULL
UNION
SELECT 3 reason_type,
sts.equipment_fk_key,
sts.from_date,
sts.To_Date,
stg.downtime_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
null,
sts.reading_time,
sts.hour_fk_key
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts
WHERE stg.from_date = sts.reading_time
AND sts.status = stg.status
AND stg.status = 2
AND stg.processing_flag = v_processing_flag
AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE equipment_pk = stg.equipment_fk )
AND stg.err_code IS NULL
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO mth_equip_statuses_stg(equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
downtime_reason_code,
processing_flag)
(SELECT equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
downtime_reason_code,
v_processing_flag
FROM mth_equip_statuses_err
WHERE reprocess_ready_yn = 'Y');
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_STATUSES_ERR
WHERE REPROCESS_READY_YN = 'Y';
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
FROM mth_equip_statuses_stg
GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
WHERE dup.cnt>1
AND dup.equipment_fk = stg.equipment_fk
AND dup.shift_workday_fk = stg.shift_workday_fk
AND dup.from_date = stg.from_date
AND dup.To_Date = stg.To_Date
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'EQP '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'IEQ '
WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND med.status <> 'ACTIVE') eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'WDS '
WHERE stg.shift_workday_fk IS NOT NULL
AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg
WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
AND stg.shift_workday_fk IS NOT NULL) wds
WHERE wds.shift_workday_pk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'ESD '
WHERE NOT EXISTS ( SELECT * FROM
( SELECT mee.equipment_pk, mws.shift_workday_pk
FROM mth_equipments_d mee,
mth_equipment_shifts_d med,
mth_workday_shifts_d mws
WHERE mee.equipment_pk_key = med.equipment_fk_key
AND med.shift_workday_fk_key = mws.shift_workday_pk_key
AND med.entity_type = 'EQUIPMENT') esd
WHERE stg.equipment_fk = esd.equipment_pk
AND stg.shift_workday_fk = esd.shift_workday_pk
AND stg.processing_flag = v_processing_flag)
AND EXISTS ( SELECT * FROM
( SELECT mds.shift_workday_pk
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg
WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
AND stg.shift_workday_fk IS NOT NULL
AND stg.processing_flag = v_processing_flag) wds
WHERE wds.shift_workday_pk = stg.shift_workday_fk )
AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD1 '
WHERE stg.user_dim1_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim1_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim1_fk = mue.entity_pk (+)
AND stg.user_dim1_fk IS NOT NULL) ud1
WHERE ud1.user_dim1_fk = stg.user_dim1_fk
AND stg.processing_flag = v_processing_flag
AND ud1.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD2 '
WHERE stg.user_dim2_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim2_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim2_fk = mue.entity_pk (+)
AND stg.user_dim2_fk IS NOT NULL) ud2
WHERE ud2.user_dim2_fk = stg.user_dim2_fk
AND stg.processing_flag = v_processing_flag
AND ud2.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD3 '
WHERE stg.user_dim3_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim3_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim3_fk = mue.entity_pk (+)
AND stg.user_dim3_fk IS NOT NULL) ud3
WHERE ud3.user_dim3_fk = stg.user_dim3_fk
AND stg.processing_flag = v_processing_flag
AND ud3.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD4 '
WHERE stg.user_dim4_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim4_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim4_fk = mue.entity_pk (+)
AND stg.user_dim4_fk IS NOT NULL) ud4
WHERE ud4.user_dim4_fk = stg.user_dim4_fk
AND stg.processing_flag = v_processing_flag
AND ud4.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD5 '
WHERE stg.user_dim5_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim5_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim5_fk = mue.entity_pk (+)
AND stg.user_dim5_fk IS NOT NULL) ud5
WHERE ud5.user_dim5_fk = stg.user_dim5_fk
AND stg.processing_flag = v_processing_flag
AND ud5.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'GAP '
WHERE EXISTS (SELECT *
FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
(Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c
WHERE c.Count >= 1
AND stg.from_date = c.from_date
AND stg.equipment_fk = c.equipment_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'FTD '
WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
AND stg.processing_flag = v_processing_flag;
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'DTR '
WHERE EXISTS (SELECT *
FROM
(SELECT flk.lookup_code,
flk.lookup_type,
stg.FROM_date,
stg.to_date,
stg.downtime_reason_code,
stg.status
FROM fnd_lookups flk,
mth_equip_statuses_stg stg
WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.downtime_reason_code = flk.lookup_code (+)
AND stg.status = 3) dtr
WHERE ((stg.downtime_reason_code = dtr.downtime_reason_code
AND stg.status = dtr.status
AND dtr.lookup_code IS NULL
AND dtr.from_date = stg.from_date
AND dtr.To_Date = stg.To_Date)
OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL))
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'IDR '
WHERE EXISTS (SELECT *
FROM
( SELECT flk.lookup_code,
flk.lookup_type,
stg.FROM_date,
stg.to_date,
stg.downtime_reason_code,
stg.status
FROM fnd_lookups flk,
mth_equip_statuses_stg stg
WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
AND stg.downtime_reason_code = flk.lookup_code (+)
AND stg.status = 2) dtr
WHERE stg.downtime_reason_code = dtr.downtime_reason_code
AND stg.status = dtr.status
AND dtr.lookup_code IS NULL
AND dtr.from_date = stg.from_date
AND dtr.To_Date = stg.To_Date
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'ITR '
WHERE EXISTS ( SELECT * FROM (
SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg,
mth_equipment_shifts_d mes,
mth_equipments_d med
WHERE stg.shift_workday_fk = mds.shift_workday_pk
AND stg.equipment_fk = med.equipment_pk
AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
AND med.equipment_pk_key = mes.equipment_fk_key
AND (stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND Upper(mes.entity_type) = 'EQUIPMENT'
AND stg.processing_flag = v_processing_flag) itr
WHERE itr.shift_workday_pk = stg.shift_workday_fk
AND itr.equipment_pk = stg.equipment_fk
AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date)
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'MSF '
WHERE EXISTS ( SELECT * FROM (
SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg,
mth_equipment_shifts_d mes,
mth_equipments_d med
WHERE stg.shift_workday_fk = mds.shift_workday_pk
AND stg.equipment_fk = med.equipment_pk
AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
AND med.equipment_pk_key = mes.equipment_fk_key
AND (((stg.from_date BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date))
OR ((stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date BETWEEN mes.from_date AND mes.To_Date)))
AND Upper(mes.entity_type) = 'EQUIPMENT'
AND stg.processing_flag = v_processing_flag) msf
WHERE msf.shift_workday_pk = stg.shift_workday_fk
AND msf.equipment_pk = stg.equipment_fk
AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date)))
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stag
SET stag.err_code = stag.err_code || 'OCSV '
WHERE EXISTS (SELECT *
FROM
(SELECT CASE
WHEN (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
AND (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
AND
stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)))
OR
(stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
AND
stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date )))
)
THEN 1 END overlap,
med.equipment_pk,
stg.from_date,
stg.to_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med
WHERE stg.equipment_fk = med.equipment_pk
AND stg.processing_flag = v_processing_flag) ovp
WHERE ovp.overlap = 1
AND stag.equipment_fk = ovp.equipment_pk
AND stag.from_date = ovp.from_date
AND stag.To_Date = ovp.To_Date
AND stag.processing_flag = v_processing_flag);
UPDATE mth_equip_statuses_stg stag
SET stag.err_code = stag.err_code || 'OSTS '
WHERE EXISTS (SELECT *
FROM ( SELECT CASE
WHEN (stg.FROM_DATE >= sts.FROM_DATE
AND stg.FROM_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
OR (stg.TO_DATE >= sts.FROM_DATE
AND stg.TO_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
OR (stg.FROM_DATE < sts.FROM_DATE
AND stg.TO_DATE > NVL(sts.TO_DATE,stg.TO_DATE+1/86400))
THEN 1 END overlap ,
med.equipment_pk,
wds.shift_workday_pk,
stg.from_date,
stg.to_date
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts,
mth_equipments_d med,
mth_workday_shifts_d wds
WHERE stg.equipment_fk = med.equipment_pk
AND stg.shift_workday_fk = wds.shift_workday_pk
AND med.equipment_pk_key = sts.equipment_fk_key
AND wds.shift_workday_pk_key = sts.shift_workday_fk_key
AND stg.processing_flag = v_processing_flag) osts
WHERE osts.overlap = 1
AND stag.equipment_fk = osts.equipment_pk
AND stag.shift_workday_fk = osts.shift_workday_pk
AND stag.processing_flag = v_processing_flag
AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code ||'WRC '
WHERE NOT EXISTS ( SELECT *
FROM (SELECT stg.*
FROM mth_equip_statuses_stg stg,
MTH_EQUIPMENT_REASON_SETUP mer,
mth_equipments_d med
WHERE stg.equipment_fk = med.equipment_pk
AND med.equipment_pk_key = mer.equipment_fk_key
AND stg.downtime_reason_code = mer.reason_code
AND stg.status IN (3,2)
AND stg.downtime_reason_code IS NOT NULL) ers
WHERE ers.equipment_fk = stg.equipment_fk
AND ers.downtime_reason_code = stg.downtime_reason_code
AND ers.from_date = stg.from_date
AND Nvl(ers.To_Date,SYSDATE) = Nvl(stg.To_Date,SYSDATE)
AND ers.shift_workday_fk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag
)
AND stg.status IN (3,2)
AND stg.downtime_reason_code IS NOT NULL;
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code ||'STS '
WHERE stg.processing_flag = v_processing_flag
AND stg.status NOT IN (1,2,3,4);
--Insert records into mth_equip_statuses_err
INSERT INTO mth_equip_statuses_err(equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
reprocess_ready_yn,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
err_code,
downtime_reason_code)
(SELECT equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
'N',
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
err_code,
downtime_reason_code
FROM mth_equip_statuses_stg
WHERE err_code IS NOT NULL
AND processing_flag = v_processing_flag);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
(SELECT med.EQUIPMENT_PK_KEY equipment_fk_key,
wds.SHIFT_WORKDAY_PK_KEY shift_workday_fk_key,
Greatest(stg.from_date,mhd.from_time) from_date,
Least(stg.To_Date,mhd.to_time) to_date,
stg.STATUS status,
Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) system_fk_key,
mue1.ENTITY_PK_KEY USER_DIM1_FK_KEY,
mue2.ENTITY_PK_KEY USER_DIM2_FK_KEY,
mue3.ENTITY_PK_KEY USER_DIM3_FK_KEY,
mue4.ENTITY_PK_KEY USER_DIM4_FK_KEY,
mue5.ENTITY_PK_KEY USER_DIM5_FK_KEY,
stg.USER_ATTR1 USER_ATTR1,
stg.USER_ATTR2 USER_ATTR2,
stg.USER_ATTR3 USER_ATTR3,
stg.USER_ATTR4 USER_ATTR4,
stg.USER_ATTR5 USER_ATTR5,
stg.USER_MEASURE1 USER_MEASURE1,
stg.USER_MEASURE2 USER_MEASURE2,
stg.USER_MEASURE3 USER_MEASURE3,
stg.USER_MEASURE4 USER_MEASURE4,
stg.USER_MEASURE5 USER_MEASURE5,
v_log_date log_date,
v_unassigned_val unassigned_val,
mhd.HOUR_PK_KEY hour_fk_key,
stg.from_date reading_time
FROM mth_equip_statuses_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_systems_setup mss,
mth_user_dim_entities_mst mue1,
mth_user_dim_entities_mst mue2,
mth_user_dim_entities_mst mue3,
mth_user_dim_entities_mst mue4,
mth_user_dim_entities_mst mue5,
fnd_lookups lkp,
mth_hour_d mhd
WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
AND mhd.to_time >= stg.from_date
AND mhd.from_time <= stg.to_date
AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+)
AND stg.processing_flag = v_processing_flag
AND err_code IS NULL
UNION ALL
SELECT med.EQUIPMENT_PK_KEY equipment_fk_key,
wds.SHIFT_WORKDAY_PK_KEY shift_workday_fk_key,
Greatest(stg.from_date,mhd.from_time) from_date,
stg.To_Date to_date,
stg.STATUS status,
Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) system_fk_key,
mue1.ENTITY_PK_KEY USER_DIM1_FK_KEY,
mue2.ENTITY_PK_KEY USER_DIM2_FK_KEY,
mue3.ENTITY_PK_KEY USER_DIM3_FK_KEY,
mue4.ENTITY_PK_KEY USER_DIM4_FK_KEY,
mue5.ENTITY_PK_KEY USER_DIM5_FK_KEY,
stg.USER_ATTR1 USER_ATTR1,
stg.USER_ATTR2 USER_ATTR2,
stg.USER_ATTR3 USER_ATTR3,
stg.USER_ATTR4 USER_ATTR4,
stg.USER_ATTR5 USER_ATTR5,
stg.USER_MEASURE1 USER_MEASURE1,
stg.USER_MEASURE2 USER_MEASURE2,
stg.USER_MEASURE3 USER_MEASURE3,
stg.USER_MEASURE4 USER_MEASURE4,
stg.USER_MEASURE5 USER_MEASURE5,
v_log_date log_date,
v_unassigned_val unassigned_val,
mhd.HOUR_PK_KEY hour_fk_key,
stg.from_date reading_time
FROM mth_equip_statuses_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_systems_setup mss,
mth_user_dim_entities_mst mue1,
mth_user_dim_entities_mst mue2,
mth_user_dim_entities_mst mue3,
mth_user_dim_entities_mst mue4,
mth_user_dim_entities_mst mue5,
fnd_lookups lkp,
mth_hour_d mhd
WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
AND stg.from_date BETWEEN mhd.from_time AND mhd.to_time
AND stg.To_Date IS NULL
AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.processing_flag = v_processing_flag
AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+) AND stg.err_code IS NULL ) subquery
ON ( stat.equipment_fk_key = subquery.equipment_fk_key
AND stat.shift_workday_fk_key = subquery.shift_workday_fk_key
AND stat.hour_fk_key = subquery.hour_fk_key
AND stat.from_date = subquery.from_date )
WHEN MATCHED THEN
UPDATE SET stat.To_Date = subquery.to_date,
stat.status = subquery.status,
stat.system_fk_key = subquery.system_fk_key,
stat.user_dim1_fk_key = subquery.USER_DIM1_FK_KEY,
stat.user_dim2_fk_key = subquery.USER_DIM2_FK_KEY,
stat.user_dim3_fk_key = subquery.USER_DIM3_FK_KEY,
stat.user_dim4_fk_key = subquery.USER_DIM4_FK_KEY,
stat.user_dim5_fk_key = subquery.USER_DIM5_FK_KEY,
stat.user_attr1 = subquery.USER_ATTR1,
stat.user_attr2 = subquery.USER_ATTR2,
stat.user_attr3 = subquery.USER_ATTR3,
stat.user_attr4 = subquery.USER_ATTR4,
stat.user_attr5 = subquery.USER_ATTR5,
stat.user_measure1 = subquery.USER_MEASURE1,
stat.user_measure2 = subquery.USER_MEASURE2,
stat.user_measure3 = subquery.USER_MEASURE3,
stat.user_measure4 = subquery.USER_MEASURE4,
stat.user_measure5 = subquery.USER_MEASURE5,
stat.last_update_date = subquery.log_date,
stat.last_update_system_id = subquery.unassigned_val
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_updated_by,
stat.last_update_login,
stat.expected_up_time,
stat.status_type,
stat.hour_fk_key,
stat.reading_time )
VALUES
( subquery.equipment_fk_key,
subquery.shift_workday_fk_key,
subquery.from_date,
subquery.To_Date,
subquery.status,
subquery.system_fk_key,
subquery.user_dim1_fk_key,
subquery.user_dim2_fk_key,
subquery.user_dim3_fk_key,
subquery.user_dim4_fk_key,
subquery.user_dim5_fk_key,
subquery.user_attr1 ,
subquery.user_attr2 ,
subquery.user_attr3 ,
subquery.user_attr4 ,
subquery.user_attr5 ,
subquery.user_measure1 ,
subquery.user_measure2 ,
subquery.user_measure3 ,
subquery.user_measure4 ,
subquery.user_measure5 ,
subquery.log_date,
subquery.log_date,
subquery.unassigned_val,
subquery.unassigned_val,
NULL,
NULL,
NULL,
NULL,
NULL,
subquery.hour_fk_key,
subquery.reading_time );
(SELECT 1 reason_type,
sts.equipment_fk_key equipment_fk_key,
sts.from_date from_date,
sts.To_Date to_date,
stg.downtime_reason_code reason_code,
v_log_date log_date,
v_unassigned_val ua_val,
NULL oth_col,
sts.reading_time reading_time,
sts.hour_fk_key hour_fk_key
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts
WHERE stg.from_date = sts.reading_time
AND sts.status = stg.status
AND stg.status = 3
AND stg.processing_flag = v_processing_flag
AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE equipment_pk = stg.equipment_fk )
AND stg.err_code IS NULL
UNION
SELECT 3 reason_type,
sts.equipment_fk_key equipment_fk_key,
sts.from_date from_date,
sts.To_Date to_date,
stg.downtime_reason_code reason_code,
v_log_date log_date,
v_unassigned_val ua_val,
NULL oth_col,
sts.reading_time reading_time,
sts.hour_fk_key hour_fk_key
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts
WHERE stg.from_date = sts.reading_time
AND sts.status = stg.status
AND stg.status = 2
AND stg.processing_flag = v_processing_flag
AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE equipment_pk = stg.equipment_fk )
AND stg.err_code IS NULL
)subquery
ON ( trr.equipment_fk_key = subquery.equipment_fk_key
AND trr.from_date = subquery.from_date
)
WHEN MATCHED THEN
UPDATE SET trr.to_date = subquery.to_date,
trr.last_update_date = subquery.log_date,
trr.last_update_login = subquery.oth_col,
trr.last_updated_by = subquery.oth_col
WHEN NOT MATCHED THEN
INSERT (trr.REASON_TYPE,
trr.EQUIPMENT_FK_KEY,
trr.FROM_DATE,
trr.To_Date,
trr.REASON_CODE,
trr.CREATION_DATE,
trr.LAST_UPDATE_DATE,
trr.CREATION_SYSTEM_ID,
trr.LAST_UPDATE_SYSTEM_ID,
trr.CREATED_BY,
trr.LAST_UPDATE_LOGIN,
trr.LAST_UPDATED_BY,
trr.READING_TIME,
trr.HOUR_FK_KEY)
VALUES (subquery.reason_type,
subquery.equipment_fk_key,
subquery.from_date,
subquery.to_date,
subquery.reason_code,
subquery.log_date,
subquery.log_date,
subquery.ua_val,
subquery.ua_val,
subquery.oth_col,
subquery.oth_col,
subquery.oth_col,
subquery.reading_time,
subquery.hour_fk_key);
SELECT equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
downtime_reason_code,
err_code
FROM mth_equip_statuses_stg
WHERE err_code IS NOT NULL;
SELECT Count(*)
INTO l_count
FROM mth_plants_d mpd
WHERE mpd.plant_pk_key = p_plant_pk_key;
SELECT Count(*)
INTO l_count
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND med.equipment_pk_key = p_equipment_pk_key
AND med.plant_fk_key = Nvl(p_plant_pk_key,med.plant_fk_key);
SELECT Min(from_date)
INTO p_min_from_date_csv
FROM mth_equip_statuses_stg stg
WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
FROM mth_equipments_d
WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
SELECT Max(to_date)
INTO p_max_to_date_csv
FROM mth_equip_statuses_stg stg
WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
FROM mth_equipments_d
WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
UPDATE mth_equip_statuses sts
SET sts.To_Date = (SELECT recal.new_to_date
FROM (SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
--AND p_recal_from_date < (To_Date - 1/86400)
)
THEN (p_recal_from_date - 1/86400)
END new_To_Date
FROM mth_equip_statuses)recal
WHERE sts.equipment_fk_key = recal.equipment_fk_key
AND sts.shift_workday_fk_key = recal.shift_workday_fk_key
AND sts.from_date = recal.FROM_date
AND sts.To_Date = recal.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND recal.new_to_date IS NOT NULL)
WHERE EXISTS (SELECT 1
FROM (SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
--AND p_recal_from_date < (To_Date - 1/86400)
)
THEN (p_recal_from_date - 1/86400)
END new_To_Date
FROM mth_equip_statuses)rcl
WHERE sts.equipment_fk_key = rcl.equipment_fk_key
AND sts.shift_workday_fk_key = rcl.shift_workday_fk_key
AND sts.from_date = rcl.FROM_date
AND sts.To_Date = rcl.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND rcl.new_to_date IS NOT NULL);
mth_util_pkg.log_msg('Number of rows updated for from_date MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_equip_statuses sts
SET sts.from_date = (SELECT recal.new_from_date
FROM (SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
--AND p_recal_to_date < (To_Date + 1/86400)
)
THEN (p_recal_to_date + 1/86400)
END new_from_date
FROM mth_equip_statuses)recal
WHERE sts.equipment_fk_key = recal.equipment_fk_key
AND sts.shift_workday_fk_key = recal.shift_workday_fk_key
AND sts.from_date = recal.FROM_date
AND sts.To_Date = recal.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND recal.new_from_date IS NOT NULL)
WHERE EXISTS (SELECT 1
FROM ( SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
--AND p_recal_to_date < (To_Date + 1/86400)
)
THEN (p_recal_to_date + 1/86400)
END new_from_date
FROM mth_equip_statuses)rcl
WHERE sts.equipment_fk_key = rcl.equipment_fk_key
AND sts.shift_workday_fk_key = rcl.shift_workday_fk_key
AND sts.from_date = rcl.FROM_date
AND sts.To_Date = rcl.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND rcl.new_from_date IS NOT NULL);
mth_util_pkg.log_msg('Number of rows updated for to_date MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_tag_reason_readings sts
SET sts.To_Date = (SELECT recal.new_to_date
FROM (SELECT equipment_fk_key,from_date,To_Date,
CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
--AND p_recal_from_date < (To_Date - 1/86400)
)
THEN (p_recal_from_date - 1/86400)
END new_To_Date
FROM mth_tag_reason_readings)recal
WHERE sts.equipment_fk_key = recal.equipment_fk_key
AND sts.from_date = recal.FROM_date
AND sts.To_Date = recal.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND recal.new_to_date IS NOT NULL)
WHERE EXISTS (SELECT 1
FROM (SELECT equipment_fk_key,from_date,To_Date,
CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
--AND p_recal_from_date < (To_Date - 1/86400)
)
THEN (p_recal_from_date - 1/86400)
END new_To_Date
FROM mth_tag_reason_readings)rcl
WHERE sts.equipment_fk_key = rcl.equipment_fk_key
AND sts.from_date = rcl.FROM_date
AND sts.To_Date = rcl.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND rcl.new_to_date IS NOT NULL);
mth_util_pkg.log_msg('Number of rows updated for from_date MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_tag_reason_readings sts
SET sts.from_date = (SELECT recal.new_from_date
FROM (SELECT equipment_fk_key, from_date,To_Date,
CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
--AND p_recal_to_date < (To_Date + 1/86400)
)
THEN (p_recal_to_date + 1/86400)
END new_from_date
FROM mth_tag_reason_readings)recal
WHERE sts.equipment_fk_key = recal.equipment_fk_key
AND sts.from_date = recal.FROM_date
AND sts.To_Date = recal.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND recal.new_from_date IS NOT NULL)
WHERE EXISTS (SELECT 1
FROM ( SELECT equipment_fk_key, from_date,To_Date,
CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
--AND p_recal_to_date < (To_Date + 1/86400)
)
THEN (p_recal_to_date + 1/86400)
END new_from_date
FROM mth_tag_reason_readings)rcl
WHERE sts.equipment_fk_key = rcl.equipment_fk_key
AND sts.from_date = rcl.FROM_date
AND sts.To_Date = rcl.To_Date
AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
AND rcl.new_from_date IS NOT NULL);
mth_util_pkg.log_msg('Number of rows updated for to_date MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE
FROM mth_equip_statuses
WHERE from_date >= p_recal_from_date
AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
AND equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = p_plant_pk_key);
DELETE
FROM mth_equip_statuses
WHERE from_date >= p_recal_from_date
AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
mth_util_pkg.log_msg('Number of rows deleted from MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE
FROM mth_tag_reason_readings
WHERE from_date >= p_recal_from_date
AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
AND reason_type IN (1,3)
AND equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = p_plant_pk_key);
DELETE
FROM mth_tag_reason_readings
WHERE from_date >= p_recal_from_date
AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
AND reason_type IN (1,3)
AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
mth_util_pkg.log_msg('Number of rows deleted from MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
FROM mth_equip_statuses_stg
GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
WHERE dup.cnt>1
AND dup.equipment_fk = stg.equipment_fk
AND dup.shift_workday_fk = stg.shift_workday_fk
AND dup.from_date = stg.from_date
AND dup.To_Date = stg.To_Date );
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'EQP '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk );
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'IEQ '
WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND med.status <> 'ACTIVE') eqp
WHERE eqp.equipment_pk = stg.equipment_fk );
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'WDS '
WHERE stg.shift_workday_fk IS NOT NULL
AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg
WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
AND stg.shift_workday_fk IS NOT NULL) wds
WHERE wds.shift_workday_pk = stg.shift_workday_fk);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'ESD '
WHERE NOT EXISTS ( SELECT * FROM
( SELECT mee.equipment_pk, mws.shift_workday_pk
FROM mth_equipments_d mee,
mth_equipment_shifts_d med,
mth_workday_shifts_d mws
WHERE mee.equipment_pk_key = med.equipment_fk_key
AND med.shift_workday_fk_key = mws.shift_workday_pk_key
AND med.entity_type = 'EQUIPMENT') esd
WHERE stg.equipment_fk = esd.equipment_pk
AND stg.shift_workday_fk = esd.shift_workday_pk)
AND EXISTS ( SELECT * FROM
( SELECT mds.shift_workday_pk
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg
WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
AND stg.shift_workday_fk IS NOT NULL) wds
WHERE wds.shift_workday_pk = stg.shift_workday_fk )
AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
FROM mth_equipments_d med,
mth_equip_statuses_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk );
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD1 '
WHERE stg.user_dim1_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim1_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim1_fk = mue.entity_pk (+)
AND stg.user_dim1_fk IS NOT NULL) ud1
WHERE ud1.user_dim1_fk = stg.user_dim1_fk
AND ud1.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD2 '
WHERE stg.user_dim2_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim2_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim2_fk = mue.entity_pk (+)
AND stg.user_dim2_fk IS NOT NULL) ud2
WHERE ud2.user_dim2_fk = stg.user_dim2_fk
AND ud2.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD3 '
WHERE stg.user_dim3_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim3_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim3_fk = mue.entity_pk (+)
AND stg.user_dim3_fk IS NOT NULL) ud3
WHERE ud3.user_dim3_fk = stg.user_dim3_fk
AND ud3.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD4 '
WHERE stg.user_dim4_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim4_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim4_fk = mue.entity_pk (+)
AND stg.user_dim4_fk IS NOT NULL) ud4
WHERE ud4.user_dim4_fk = stg.user_dim4_fk
AND ud4.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'UD5 '
WHERE stg.user_dim5_fk IS NOT NULL
AND EXISTS (SELECT *
FROM
(SELECT mue.entity_pk, stg.user_dim5_fk
FROM mth_user_dim_entities_mst mue,
mth_equip_statuses_stg stg
WHERE stg.user_dim5_fk = mue.entity_pk (+)
AND stg.user_dim5_fk IS NOT NULL) ud5
WHERE ud5.user_dim5_fk = stg.user_dim5_fk
AND ud5.entity_pk IS NULL);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'GAP '
WHERE EXISTS (SELECT *
FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
(Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
FROM mth_equip_statuses_stg a) b) c
WHERE c.Count >= 1
AND stg.from_date = c.from_date
AND stg.equipment_fk = c.equipment_fk);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'FTD '
WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE;
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'DTR '
WHERE EXISTS (SELECT *
FROM
(SELECT flk.lookup_code,
flk.lookup_type,
stg.FROM_date,
stg.to_date,
stg.downtime_reason_code,
stg.status
FROM fnd_lookups flk,
mth_equip_statuses_stg stg
WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.downtime_reason_code = flk.lookup_code (+)
AND stg.status = 3) dtr
WHERE (stg.downtime_reason_code = dtr.downtime_reason_code
AND stg.status = dtr.status
AND dtr.lookup_code IS NULL
AND dtr.from_date = stg.from_date
AND dtr.To_Date = stg.To_Date)
OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL));
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'IDR '
WHERE EXISTS (SELECT *
FROM
( SELECT flk.lookup_code,
flk.lookup_type,
stg.FROM_date,
stg.to_date,
stg.downtime_reason_code,
stg.status
FROM fnd_lookups flk,
mth_equip_statuses_stg stg
WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
AND stg.downtime_reason_code = flk.lookup_code (+)
AND stg.status = 2) dtr
WHERE stg.downtime_reason_code = dtr.downtime_reason_code
AND stg.status = dtr.status
AND dtr.lookup_code IS NULL
AND dtr.from_date = stg.from_date
AND dtr.To_Date = stg.To_Date);
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'ITR '
WHERE EXISTS ( SELECT * FROM (
SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg,
mth_equipment_shifts_d mes,
mth_equipments_d med
WHERE stg.shift_workday_fk = mds.shift_workday_pk
AND stg.equipment_fk = med.equipment_pk
AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
AND med.equipment_pk_key = mes.equipment_fk_key
AND (stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND Upper(mes.entity_type) = 'EQUIPMENT') itr
WHERE itr.shift_workday_pk = stg.shift_workday_fk
AND itr.equipment_pk = stg.equipment_fk
AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date));
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code || 'MSF '
WHERE EXISTS ( SELECT * FROM (
SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
FROM mth_workday_shifts_d mds,
mth_equip_statuses_stg stg,
mth_equipment_shifts_d mes,
mth_equipments_d med
WHERE stg.shift_workday_fk = mds.shift_workday_pk
AND stg.equipment_fk = med.equipment_pk
AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
AND med.equipment_pk_key = mes.equipment_fk_key
AND (((stg.from_date BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date))
OR ((stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
AND (stg.to_date BETWEEN mes.from_date AND mes.To_Date)))
AND Upper(mes.entity_type) = 'EQUIPMENT') msf
WHERE msf.shift_workday_pk = stg.shift_workday_fk
AND msf.equipment_pk = stg.equipment_fk
AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date))));
UPDATE mth_equip_statuses_stg stag
SET stag.err_code = stag.err_code || 'OCSV '
WHERE EXISTS (SELECT *
FROM
(SELECT CASE
WHEN (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
AND (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
AND
stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)))
OR
(stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
AND
stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date )))
)
THEN 1 END overlap,
med.equipment_pk,
stg.from_date,
stg.to_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med
WHERE stg.equipment_fk = med.equipment_pk ) ovp
WHERE ovp.overlap = 1
AND stag.equipment_fk = ovp.equipment_pk
AND stag.from_date = ovp.from_date
AND stag.To_Date = ovp.To_Date);
UPDATE mth_equip_statuses_stg stag
SET stag.err_code = stag.err_code || 'OSTS '
WHERE EXISTS (SELECT *
FROM ( SELECT CASE
WHEN (stg.FROM_DATE >= sts.FROM_DATE
AND stg.FROM_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
OR (stg.TO_DATE >= sts.FROM_DATE
AND stg.TO_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
OR (stg.FROM_DATE < sts.FROM_DATE
AND stg.TO_DATE > NVL(sts.TO_DATE,stg.TO_DATE+1/86400))
THEN 1 END overlap ,
med.equipment_pk,
wds.shift_workday_pk,
stg.from_date,
stg.to_date
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts,
mth_equipments_d med,
mth_workday_shifts_d wds
WHERE stg.equipment_fk = med.equipment_pk
AND stg.shift_workday_fk = wds.shift_workday_pk
AND med.equipment_pk_key = sts.equipment_fk_key
AND wds.shift_workday_pk_key = sts.shift_workday_fk_key) osts
WHERE osts.overlap = 1
AND stag.equipment_fk = osts.equipment_pk
AND stag.shift_workday_fk = osts.shift_workday_pk
AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code ||'WRC '
WHERE NOT EXISTS ( SELECT *
FROM (SELECT stg.*
FROM mth_equip_statuses_stg stg,
MTH_EQUIPMENT_REASON_SETUP mer,
mth_equipments_d med
WHERE stg.equipment_fk = med.equipment_pk
AND med.equipment_pk_key = mer.equipment_fk_key
AND stg.downtime_reason_code = mer.reason_code
AND stg.status IN (3,2)
AND stg.downtime_reason_code IS NOT NULL) ers
WHERE ers.equipment_fk = stg.equipment_fk
AND ers.downtime_reason_code = stg.downtime_reason_code
AND ers.from_date = stg.from_date
AND Nvl(ers.To_Date,SYSDATE) = Nvl(stg.To_Date,SYSDATE)
AND ers.shift_workday_fk = stg.shift_workday_fk
)
AND stg.status IN (3,2)
AND stg.downtime_reason_code IS NOT NULL;
UPDATE mth_equip_statuses_stg stg
SET stg.err_code = stg.err_code ||'STS '
WHERE stg.status NOT IN (1,2,3,4);
/*--Insert records into mth_equip_statuses_err
INSERT INTO mth_equip_statuses_err(equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
reprocess_ready_yn,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
err_code,
downtime_reason_code)
(SELECT equipment_fk,
shift_workday_fk,
from_date,
To_Date,
status,
system_fk,
'N',
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
user_attr1,
user_attr2,
user_attr3,
user_attr4,
user_attr5,
user_measure1,
user_measure2,
user_measure3,
user_measure4,
user_measure5,
err_code,
downtime_reason_code
FROM mth_equip_statuses_stg
WHERE err_code IS NOT NULL);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);*/
--Insert records into mth_equip_statuses table
INSERT INTO mth_equip_statuses( equipment_fk_key,
shift_workday_fk_key,
from_date,
To_Date,
status,
system_fk_key,
user_dim1_fk_key,
user_dim2_fk_key,
user_dim3_fk_key,
user_dim4_fk_key,
user_dim5_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,
created_by,
last_updated_by,
last_update_login,
expected_up_time,
status_type,
hour_fk_key,
reading_time )
(SELECT med.EQUIPMENT_PK_KEY ,
wds.SHIFT_WORKDAY_PK_KEY ,
Greatest(stg.from_date,mhd.from_time),
Least(stg.To_Date,mhd.to_time) ,
stg.STATUS ,
Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
mue1.ENTITY_PK_KEY ,
mue2.ENTITY_PK_KEY ,
mue3.ENTITY_PK_KEY ,
mue4.ENTITY_PK_KEY ,
mue5.ENTITY_PK_KEY ,
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 ,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
null,
null,
null,
null,
null,
mhd.HOUR_PK_KEY,
stg.from_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_systems_setup mss,
mth_user_dim_entities_mst mue1,
mth_user_dim_entities_mst mue2,
mth_user_dim_entities_mst mue3,
mth_user_dim_entities_mst mue4,
mth_user_dim_entities_mst mue5,
fnd_lookups lkp,
mth_hour_d mhd
WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
AND mhd.to_time >= stg.from_date
AND mhd.from_time <= stg.to_date
AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+)
AND err_code IS NULL
UNION ALL
SELECT med.EQUIPMENT_PK_KEY ,
wds.SHIFT_WORKDAY_PK_KEY ,
Greatest(stg.from_date,mhd.from_time),
stg.To_Date,
stg.STATUS ,
Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
mue1.ENTITY_PK_KEY ,
mue2.ENTITY_PK_KEY ,
mue3.ENTITY_PK_KEY ,
mue4.ENTITY_PK_KEY ,
mue5.ENTITY_PK_KEY ,
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 ,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
null,
null,
null,
null,
null,
mhd.HOUR_PK_KEY,
stg.from_date
FROM mth_equip_statuses_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_systems_setup mss,
mth_user_dim_entities_mst mue1,
mth_user_dim_entities_mst mue2,
mth_user_dim_entities_mst mue3,
mth_user_dim_entities_mst mue4,
mth_user_dim_entities_mst mue5,
fnd_lookups lkp,
mth_hour_d mhd
WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
AND stg.from_date BETWEEN mhd.from_time AND mhd.to_time
AND stg.To_Date IS NULL
AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+) AND stg.err_code IS NULL );
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
--Insertion of records in tag reason readings
INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
EQUIPMENT_FK_KEY,
FROM_DATE,
To_Date,
REASON_CODE,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
READING_TIME,
HOUR_FK_KEY)
(SELECT 1 reason_type,
sts.equipment_fk_key,
sts.from_date,
sts.To_Date,
stg.downtime_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
null,
sts.reading_time,
sts.hour_fk_key
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts
WHERE stg.from_date = sts.reading_time
AND sts.status = stg.status
AND stg.status = 3
AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE equipment_pk = stg.equipment_fk )
AND stg.err_code IS NULL
UNION
SELECT 3 reason_type,
sts.equipment_fk_key,
sts.from_date,
sts.To_Date,
stg.downtime_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
null,
sts.reading_time,
sts.hour_fk_key
FROM mth_equip_statuses_stg stg,
mth_equip_statuses sts
WHERE stg.from_date = sts.reading_time
AND sts.status = stg.status
AND stg.status = 2
AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
FROM mth_equipments_d
WHERE equipment_pk = stg.equipment_fk )
AND stg.err_code IS NULL
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_STATUS_SUMMARY;
mth_util_pkg.log_msg('Number of rows deleted from status summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT
INTO
MTH_EQUIP_STATUS_SUMMARY( EQUIPMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
RUN_HOURS,
IDLE_HOURS,
DOWN_HOURS,
UP_HOURS,
OFF_HOURS,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
RESOURCE_FK_KEY,
RESOURCE_COST)
(SELECT eqsts.equipment_fk_key,
eqsts.shift_workday_fk_key,
eqsts.hour_fk_key,
Sum(Decode (eqsts.status,1,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) run_hours,
Sum(Decode (eqsts.status,2,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) idle_hours,
Sum(Decode (eqsts.status,3,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) down_hours,
Sum((CASE WHEN (eqsts.status = 1 OR eqsts.status = 2) THEN ((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600)
ELSE 0 END)) up_hours,
Sum(Decode (eqsts.status,4,((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) off_hours,
v_ua_val,
v_log_date,
v_log_date,
v_ua_val,
v_ua_val,
Min(med.level9_level_key) resource_fk_key,
Min(mrc.cost) resource_cost
FROM mth_equip_statuses eqsts,
mth_hour_d msg,
mth_equipment_denorm_d med,
mth_resource_cost_mv mrc
WHERE med.equipment_hierarchy_key = -2
AND med.equipment_fk_key IS NOT NULL
AND msg.from_time BETWEEN med.equipment_effective_date
AND Nvl(med.equipment_expiration_date , msg.from_time)
AND med.equipment_fk_key = eqsts.equipment_fk_key
AND eqsts.hour_fk_key = msg.hour_pk_key
AND med.level9_level_key = mrc.resource_fk_key(+)
AND eqsts.last_update_date <= v_run_log_to_date
GROUP BY eqsts.equipment_fk_key,
eqsts.shift_workday_fk_key,
eqsts.hour_fk_key);
mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
( SELECT eqsts.equipment_fk_key,
eqsts.shift_workday_fk_key,
eqsts.hour_fk_key,
Sum(Decode (eqsts.status,1,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) run_hours,
Sum(Decode (eqsts.status,2,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) idle_hours,
Sum(Decode (eqsts.status,3,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) down_hours,
Sum((CASE WHEN (eqsts.status = 1 OR eqsts.status = 2) THEN ((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600)
ELSE 0 END)) up_hours,
Sum(Decode (eqsts.status,4,((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) off_hours,
v_ua_val system_fk_key,
v_log_date creation_date,
v_log_date last_update_date,
v_ua_val creation_system_id,
v_ua_val last_update_system_id,
Min(med.level9_level_key) resource_fk_key,
Min(mrc.cost) resource_cost
FROM mth_equip_statuses eqsts,
mth_hour_d msg,
mth_equipment_denorm_d med,
mth_resource_cost_mv mrc
WHERE med.equipment_hierarchy_key = -2
AND med.equipment_fk_key IS NOT NULL
AND msg.from_time BETWEEN med.equipment_effective_date
AND Nvl(med.equipment_expiration_date , msg.from_time)
AND med.equipment_fk_key = eqsts.equipment_fk_key
AND eqsts.hour_fk_key = msg.hour_pk_key
AND med.level9_level_key = mrc.resource_fk_key(+)
AND eqsts.hour_fk_key IN ( SELECT hour_fk_key
FROM mth_equip_statuses
WHERE last_update_date > v_run_log_from_date
AND last_update_date <= v_run_log_to_date )
GROUP BY eqsts.equipment_fk_key,
eqsts.shift_workday_fk_key,
eqsts.hour_fk_key) statrec
ON (
mes.equipment_fk_key = statrec.equipment_fk_key
AND mes.shift_workday_fk_key = statrec.shift_workday_fk_key
AND mes.hour_fk_key = statrec.hour_fk_key
)
WHEN MATCHED THEN
UPDATE
SET
mes.up_hours = statrec.up_hours,
mes.down_hours = statrec.down_hours,
mes.run_hours = statrec.run_hours,
mes.idle_hours = statrec.idle_hours,
mes.off_hours = statrec.off_hours,
mes.system_fk_key = statrec.system_fk_key,
mes.last_update_date = statrec.last_update_date,
mes.last_update_system_id = statrec.last_update_system_id,
mes.resource_fk_key = statrec.resource_fk_key,
mes.resource_cost = statrec.resource_cost
WHEN NOT MATCHED THEN
INSERT
(mes.equipment_fk_key,
mes.shift_workday_fk_key,
mes.hour_fk_key,
mes.up_hours,
mes.down_hours,
mes.run_hours,
mes.idle_hours,
mes.off_hours,
mes.system_fk_key,
mes.creation_date,
mes.last_update_date,
mes.creation_system_id,
mes.last_update_system_id,
mes.resource_fk_key,
mes.resource_cost)
VALUES
(statrec.equipment_fk_key,
statrec.shift_workday_fk_key,
statrec.hour_fk_key,
statrec.up_hours,
statrec.down_hours,
statrec.run_hours,
statrec.idle_hours,
statrec.off_hours,
statrec.system_fk_key,
statrec.creation_date,
statrec.last_update_date,
statrec.creation_system_id,
statrec.last_update_system_id,
statrec.resource_fk_key,
statrec.resource_cost)
;
SELECT Min(from_time)
FROM mth_hour_d
WHERE (SELECT Min(Max(reading_time))
FROM mth_tag_readings
WHERE reading_time < p_recalc_from_date
GROUP BY equipment_fk_key) BETWEEN from_time AND to_time;*/
SELECT Min(from_time)
FROM mth_hour_d
WHERE (SELECT Min(from_date) reading_time
FROM mth_equip_statuses_stg stg,
mth_equipments_d eq
WHERE stg.equipment_fk = eq.equipment_pk
AND eq.equipment_pk_key = nvl(p_recalc_equip_key,eq.equipment_pk_key)
AND eq.equipment_pk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
GROUP BY eq.equipment_pk_key )
BETWEEN from_time AND to_time;
SELECT Max(to_time)
FROM mth_hour_d
WHERE (SELECT Max(Min(reading_time))
FROM mth_tag_readings
WHERE reading_time > v_recalc_to_date
GROUP BY equipment_fk_key) BETWEEN from_time AND to_time;*/
SELECT Max(to_time)
FROM mth_hour_d
WHERE (SELECT Max(to_date) reading_time
FROM mth_equip_statuses_stg stg,
mth_equipments_d eq
WHERE stg.equipment_fk = eq.equipment_pk
AND eq.equipment_pk_key = nvl(p_recalc_equip_key,eq.equipment_pk_key)
AND eq.equipment_pk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
GROUP BY eq.equipment_pk_key )
BETWEEN from_time AND to_time;
SELECT Max(from_date),
Max(To_Date)
FROM MTH_EQUIP_STATUSES
WHERE equipment_fk_key = nvl(v_recalc_equip_key,equipment_fk_key);
DELETE MTH_EQUIP_STATUS_SUMMARY
WHERE hour_fk_key IN (SELECT hour_pk_key
FROM mth_hour_d
WHERE from_time >= p_n_recalc_from_date
AND to_time <= p_n_recalc_to_date)
AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key);
DELETE MTH_EQUIP_STATUS_SUMMARY
WHERE hour_fk_key IN (SELECT hour_pk_key
FROM mth_hour_d
WHERE from_time >= p_n_recalc_from_date
AND to_time <= p_n_recalc_to_date)
AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key)
AND equipment_fk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key));
mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
INSERT
INTO
MTH_EQUIP_STATUS_SUMMARY( EQUIPMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
RUN_HOURS,
IDLE_HOURS,
DOWN_HOURS,
UP_HOURS,
OFF_HOURS,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
RESOURCE_FK_KEY,
RESOURCE_COST)
(SELECT eqsts.equipment_fk_key,
eqsts.shift_workday_fk_key,
eqsts.hour_fk_key,
Sum(Decode (eqsts.status,1,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) run_hours,
Sum(Decode (eqsts.status,2,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) idle_hours,
Sum(Decode (eqsts.status,3,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) down_hours,
Sum((CASE WHEN (eqsts.status = 1 OR eqsts.status = 2) THEN ((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600)
ELSE 0 END)) up_hours,
Sum(Decode (eqsts.status,4,((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) off_hours,
v_ua_val,
v_log_date,
v_log_date,
v_ua_val,
v_ua_val,
Min(med.level9_level_key) resource_fk_key,
Min(mrc.cost) resource_cost
FROM mth_equip_statuses eqsts,
mth_hour_d msg,
mth_equipment_denorm_d med,
mth_resource_cost_mv mrc
WHERE med.equipment_hierarchy_key = -2
AND med.equipment_fk_key IS NOT NULL
AND msg.from_time BETWEEN med.equipment_effective_date
AND Nvl(med.equipment_expiration_date , msg.from_time)
AND med.equipment_fk_key = eqsts.equipment_fk_key
AND eqsts.hour_fk_key = msg.hour_pk_key
AND eqsts.hour_fk_key IN (SELECT hour_pk_key
FROM mth_hour_d
WHERE from_time >= p_n_recalc_from_date
AND to_time <= p_n_recalc_to_date)
AND eqsts.equipment_fk_key = nvl(p_recalc_equip_key,eqsts.equipment_fk_key)
AND eqsts.equipment_fk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
AND med.level9_level_key = mrc.resource_fk_key(+)
GROUP BY eqsts.equipment_fk_key,
eqsts.shift_workday_fk_key,
eqsts.hour_fk_key);
-- Call the logging API to log the number of rows inserted
mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_STATUSES_STG;