[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM MTH_EQUIP_PROD_PERFORMANCE_F;
DELETE FROM MTH_EQUIP_PROD_PERF_DETAIL_F;
DELETE FROM MTH_EQUIP_PROD_SUSTAIN_F;
update_EPP_from_status(p_mode);
update_EPP_from_status(p_mode);
* Procedure :insert_into_EPP_per_output_rec *
* Description : Break this EPP entry by shifts and hours and insert them *
* into EPP table. *
* equipment *
* File Name :MTHPDPB.PLS *
* Visibility :Private *
* Parameters :p_actual_from_date - Actual from date *
* Return Value :None *
**************************************************************************** */
PROCEDURE insert_into_EPP_per_output_rec (p_actual_from_date DATE,
p_actual_to_date DATE,
p_equipment_fk_key NUMBER,
p_shift_workday_fk_key NUMBER,
p_workorder_fk_key NUMBER,
p_segment_fk_key NUMBER,
p_item_fk_key NUMBER,
p_system_fk_key NUMBER,
p_user_dim1_fk_key NUMBER,
p_user_dim2_fk_key NUMBER,
p_user_dim3_fk_key NUMBER,
p_user_dim4_fk_key NUMBER,
p_user_dim5_fk_key NUMBER,
p_user_attr1 VARCHAR2,
p_user_attr2 VARCHAR2,
p_user_attr3 VARCHAR2,
p_user_attr4 VARCHAR2,
p_user_attr5 VARCHAR2,
p_user_measure1 NUMBER,
p_user_measure2 NUMBER,
p_user_measure3 NUMBER,
p_user_measure4 NUMBER,
p_user_measure5 NUMBER,
p_sysdate DATE,
p_creation_system_id NUMBER,
p_last_update_system_id NUMBER,
p_created_by NUMBER,
p_last_update_login NUMBER,
p_last_updated_by NUMBER,
p_qty_completed NUMBER,
p_qty_scrap NUMBER,
p_qty_output NUMBER,
p_qty_rejected NUMBER,
p_qty_rework NUMBER,
p_qty_good NUMBER,
p_qty_uom VARCHAR2)
IS
-- This is used to calculate the beginning time of the catch all shift.
-- It should be the actual_to_date of the last reading.
v_b_time_4_cs DATE;
mth_util_pkg.log_msg('insert_into_EPP_per_output_rec start', mth_util_pkg.G_DBG_PROC_FUN_START);
INSERT INTO MTH_EQUIP_PROD_PERFORMANCE_F
(equipment_fk_key, shift_workday_fK_key, hour_fk_key,
workorder_fk_key, segment_fk_key, item_fk_key,
actual_from_date, actual_to_date, 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_update_login, last_updated_by,
equip_prod_perf_pk_key, run_hours, down_hours,
idle_hours, off_hours,
qty_completed,
qty_scrap,
qty_output,
qty_rejected,
qty_rework,
qty_good,
qty_uom)
SELECT p_equipment_fk_key equipment_fk_key,
shift_workday_fK_key,
hour_fk_key,
CASE WHEN entry_type = 3
THEN p_workorder_fk_key
ELSE -99999 END workorder_fk_key,
CASE WHEN entry_type = 3
THEN p_segment_fk_key
ELSE -99999 END segment_fk_key,
CASE WHEN entry_type = 3
THEN p_item_fk_key
ELSE -99999 END item_fk_key,
actual_from_date,
actual_to_date,
p_system_fk_key system_fk_key,
CASE WHEN entry_type = 3
THEN p_user_dim1_fk_key
ELSE NULL END user_dim1_fk_key,
CASE WHEN entry_type = 3
THEN p_user_dim2_fk_key
ELSE NULL END user_dim2_fk_key,
CASE WHEN entry_type = 3
THEN p_user_dim3_fk_key
ELSE NULL END user_dim3_fk_key,
CASE WHEN entry_type = 3
THEN p_user_dim4_fk_key
ELSE NULL END user_dim4_fk_key,
CASE WHEN entry_type = 3
THEN p_user_dim5_fk_key
ELSE NULL END user_dim5_fk_key,
CASE WHEN entry_type = 3
THEN p_user_attr1
ELSE NULL END user_attr1,
CASE WHEN entry_type = 3
THEN p_user_attr2
ELSE NULL END user_attr2,
CASE WHEN entry_type = 3
THEN p_user_attr3
ELSE NULL END user_attr3,
CASE WHEN entry_type = 3
THEN p_user_attr4
ELSE NULL END user_attr4,
CASE WHEN entry_type = 3
THEN p_user_attr5
ELSE NULL END user_attr5,
CASE WHEN entry_type = 3
THEN p_user_measure1
ELSE NULL END user_measure1,
CASE WHEN entry_type = 3
THEN p_user_measure2
ELSE NULL END user_measure2,
CASE WHEN entry_type = 3
THEN p_user_measure3
ELSE NULL END user_measure3,
CASE WHEN entry_type = 3
THEN p_user_measure4
ELSE NULL END user_measure4,
CASE WHEN entry_type = 3
THEN p_user_measure5
ELSE NULL END user_measure5,
p_sysdate creation_date,
p_sysdate last_update_date,
p_creation_system_id creation_system_id,
p_last_update_system_id last_update_system_id,
p_created_by created_by,
p_last_update_login last_update_login,
p_last_updated_by last_updated_by,
MTH_EQUIP_PROD_PERF_S.NEXTVAL equip_prod_perf_pk_key,
0 run_hours,
0 down_hours,
0 idle_hours,
0 off_hours,
CASE WHEN entry_type = 3
THEN allocation_pct * p_qty_completed
ELSE 0 END qty_completed,
CASE WHEN entry_type = 3
THEN allocation_pct * p_qty_scrap
ELSE 0 END qty_scrap,
CASE WHEN entry_type = 3
THEN allocation_pct * p_qty_output
ELSE 0 END qty_output,
CASE WHEN entry_type = 3
THEN allocation_pct * p_qty_rejected
ELSE 0 END qty_rejected,
CASE WHEN entry_type = 3
THEN allocation_pct * p_qty_rework
ELSE 0 END qty_rework,
CASE WHEN entry_type = 3
THEN allocation_pct * p_qty_good
ELSE 0 END qty_good,
CASE WHEN entry_type = 3
THEN p_qty_uom
ELSE NULL END qty_uom
FROM (
SELECT s.shift_workday_fK_key ,
h.hour_pk_key hour_fk_key,
greatest(h.from_time, s.from_date, p_actual_from_date) actual_from_date,
least(h.to_time, s.to_date, p_actual_to_date) actual_to_date,
CASE WHEN s.availability_flag = 'Y'
THEN (least(h.to_time, s.to_date, p_actual_to_date) -
greatest(h.from_time, s.from_date, p_actual_from_date) + 1 /86400)/
Sum((least(h.to_time, s.to_date, p_actual_to_date) -
greatest(h.from_time, s.from_date, p_actual_from_date) + 1 /86400))
over (PARTITION BY s.equipment_fk_key, s.shift_workday_fK_key,
s.availability_flag) ELSE 0 END allocation_pct,
CASE WHEN s.availability_flag = 'Y' THEN 2 ELSE 0 END +
CASE WHEN s.shift_workday_fk_key = p_shift_workday_fK_key
THEN 1 ELSE 0 END entry_type
FROM (SELECT equipment_fk_key,
shift_workday_fk_key,
from_date,
To_Date,
availability_flag
FROM MTH_EQUIPMENT_SHIFTS_D
WHERE equipment_fk_key = p_equipment_fk_key AND
from_date IS NOT NULL AND
to_date IS NOT NULL AND
from_date <> To_Date AND
(p_actual_to_date BETWEEN from_date AND To_Date OR
p_actual_from_date BETWEEN from_date AND To_Date OR
from_date BETWEEN p_actual_from_date AND p_actual_to_date OR
To_Date BETWEEN p_actual_from_date AND p_actual_to_date )
) s,
mth_hour_d h
WHERE equipment_fk_key = p_equipment_fk_key AND
s.from_date IS NOT NULL AND
s.to_date IS NOT NULL AND
s.from_date <> s.To_Date AND
(p_actual_to_date BETWEEN s.from_date AND s.To_Date OR
p_actual_from_date BETWEEN s.from_date AND s.To_Date OR
from_date BETWEEN p_actual_from_date AND p_actual_to_date OR
To_Date BETWEEN p_actual_from_date AND p_actual_to_date ) AND
(p_actual_to_date BETWEEN h.from_time AND h.To_time OR
p_actual_from_date BETWEEN h.from_time AND h.To_time OR
from_time BETWEEN p_actual_from_date AND p_actual_to_date OR
to_time BETWEEN p_actual_from_date AND p_actual_to_date ) AND
(s.from_date BETWEEN h.from_time AND h.To_time OR
s.to_date BETWEEN h.from_time AND h.To_time OR
h.from_time BETWEEN s.from_date AND s.To_Date OR
h.to_time BETWEEN s.from_date AND s.To_Date )
UNION ALL
SELECT Nvl(cs.shift_workday_fk_key, -99999) shift_workday_fK_key,
h.hour_pk_key hour_fk_key,
greatest(h.from_time, s.catch_all_from_date) actual_from_date,
least(h.to_time, s.catch_all_to_date) actual_to_date,
0 allocation_pct,
-1 entry_type -- for catch-all shift
FROM (SELECT equipment_fk_key,
lead ( from_date )
OVER ( PARTITION BY EQUIPMENT_FK_KEY
ORDER BY from_date ) - 1/86400 catch_all_to_date,
To_Date + 1/86400 catch_all_from_date
FROM MTH_EQUIPMENT_SHIFTS_D
WHERE equipment_fk_key = p_equipment_fk_key AND
from_date is not null and
to_date is not null and
from_date <> to_date and
(p_actual_to_date BETWEEN from_date AND To_Date OR
v_b_time_4_cs BETWEEN from_date AND To_Date OR
from_date BETWEEN v_b_time_4_cs AND p_actual_to_date OR
To_Date BETWEEN v_b_time_4_cs AND p_actual_to_date )
)s,
mth_hour_d h ,
(SELECT equipment_fk_key,
shift_workday_fk_key,
availability_date
FROM MTH_EQUIPMENT_SHIFTS_D
WHERE (from_date = To_Date OR
from_date IS NULL AND To_Date IS NULL) AND
equipment_fk_key = p_equipment_fk_key) cs
WHERE s.equipment_fk_key = p_equipment_fk_key AND
s.catch_all_from_date < s.catch_all_to_date AND
(p_actual_to_date BETWEEN s.catch_all_from_date AND s.catch_all_to_date OR
v_b_time_4_cs BETWEEN s.catch_all_from_date AND
s.catch_all_to_date OR
s.catch_all_from_date BETWEEN v_b_time_4_cs AND p_actual_to_date OR
s.catch_all_to_date BETWEEN v_b_time_4_cs AND p_actual_to_date ) AND
(p_actual_to_date BETWEEN h.from_time AND h.To_time OR
v_b_time_4_cs BETWEEN h.from_time AND h.To_time OR
h.from_time BETWEEN v_b_time_4_cs AND p_actual_to_date OR
h.to_time BETWEEN v_b_time_4_cs AND p_actual_to_date ) AND
(s.catch_all_from_date BETWEEN h.from_time AND h.To_time OR
s.catch_all_to_date BETWEEN h.from_time AND h.To_time OR
h.from_time BETWEEN s.catch_all_from_date AND s.catch_all_to_date OR
h.to_time BETWEEN s.catch_all_from_date AND s.catch_all_to_date ) AND
Trunc(h.from_time) = cs.availability_date (+)
--Trunc(s.catch_all_from_date) = cs.availability_date (+) AND
--s.equipment_fk_key = cs.equipment_fk_key (+)
);
mth_util_pkg.log_msg('insert_into_EPP_per_output_rec end', mth_util_pkg.G_DBG_PROC_FUN_END);
END insert_into_EPP_per_output_rec;
SELECT equipment_fk_key,
Max(Nvl(actual_to_date, actual_from_date))
latest_actual_to_date
FROM MTH_EQUIP_PROD_PERFORMANCE_F
GROUP BY equipment_fk_key;
SELECT o.EQUIPMENT_FK_KEY,
o.SHIFT_WORKDAY_FK_KEY,
o.WORKORDER_FK_KEY,
o.SEGMENT_FK_KEY,
o.ITEM_FK_KEY,
o.READING_TIME,
o.HOUR_FK_KEY,
NVL(o.QTY_COMPLETED, 0) QTY_COMPLETED,
NVL(o.QTY_SCRAP, 0) QTY_SCRAP,
NVL(o.QTY_REJECTED, 0) QTY_REJECTED,
NVL(o.QTY_REWORK, 0) QTY_REWORK,
o.QTY_UOM,
NVL(o.QTY_GOOD, 0) QTY_GOOD,
NVL(o.QTY_OUTPUT, 0) QTY_OUTPUT,
o.SYSTEM_FK_KEY,
o.USER_DIM1_FK_KEY,
o.USER_DIM2_FK_KEY,
o.USER_DIM3_FK_KEY,
o.USER_DIM4_FK_KEY,
o.USER_DIM5_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,
Min(s.from_date) shift_from_date,
Max(s.To_Date) shift_to_date,
o.READING_TIME + 4000 actual_from_date, -- As a place holder
o.READING_TIME actual_to_date
FROM MTH_EQUIP_OUTPUT o,
MTH_EQUIPMENTS_D e,
MTH_EQUIPMENT_SHIFTS_D s,
(SELECT from_date, To_Date
FROM MTH_RUN_LOG
WHERE fact_table = 'MTH_EQUIP_PROD_PERFORMANCE_F' AND
ROWNUM = 1) rl
WHERE o.last_update_date > rl.from_date AND
o.last_update_date <= rl.To_Date AND
o.equipment_fk_key = e.equipment_pk_key AND
o.equipment_fk_key = s.equipment_fk_key AND
o.shift_workday_fk_key = s.shift_workday_fk_key AND
Upper(s.availability_flag) = 'Y' AND
Upper(e.production_equipment) = 'Y'
GROUP BY o.EQUIPMENT_FK_KEY,
o.SHIFT_WORKDAY_FK_KEY,
o.WORKORDER_FK_KEY,
o.SEGMENT_FK_KEY,
o.ITEM_FK_KEY,
o.READING_TIME,
o.HOUR_FK_KEY,
o.QTY_COMPLETED,
o.QTY_SCRAP,
o.QTY_REJECTED,
o.QTY_REWORK,
o.QTY_UOM,
o.QTY_GOOD,
o.QTY_OUTPUT,
o.SYSTEM_FK_KEY,
o.USER_DIM1_FK_KEY,
o.USER_DIM2_FK_KEY,
o.USER_DIM3_FK_KEY,
o.USER_DIM4_FK_KEY,
o.USER_DIM5_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
ORDER BY o.equipment_fk_key, o.reading_time, o.workorder_fk_key,
o.segment_fk_key, o.item_fk_key;
insert_into_EPP_per_output_rec (v_c_o_rec.actual_from_date,
v_c_o_rec.actual_to_date,
v_c_o_rec.equipment_fk_key,
v_c_o_rec.shift_workday_fk_key,
v_c_o_rec.workorder_fk_key,
v_c_o_rec.segment_fk_key,
v_c_o_rec.item_fk_key,
v_c_o_rec.system_fk_key,
v_c_o_rec.user_dim1_fk_key,
v_c_o_rec.user_dim2_fk_key,
v_c_o_rec.user_dim3_fk_key,
v_c_o_rec.user_dim4_fk_key,
v_c_o_rec.user_dim5_fk_key,
v_c_o_rec.user_attr1,
v_c_o_rec.user_attr2,
v_c_o_rec.user_attr3,
v_c_o_rec.user_attr4,
v_c_o_rec.user_attr5,
v_c_o_rec.user_measure1,
v_c_o_rec.user_measure2,
v_c_o_rec.user_measure3,
v_c_o_rec.user_measure4,
v_c_o_rec.user_measure5,
SYSDATE,
-99999,
-99999,
-99999,
-99999,
-99999,
v_c_o_rec.qty_completed,
v_c_o_rec.qty_scrap,
v_c_o_rec.qty_output,
v_c_o_rec.qty_rejected,
v_c_o_rec.qty_rework,
v_c_o_rec.qty_good,
v_c_o_rec.qty_uom);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_PROD_PERFORMANCE_F - ' || v_rec_count, mth_util_pkg.G_DBG_ROW_CNT);
SELECT EQUIPMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_FK_KEY,
NEW_FROM_DATE,
CASE WHEN TEMP_TO_DATE > SYSDATE THEN NULL ELSE NEW_TO_DATE END NEW_TO_DATE,
RUN_HOURS,
IDLE_HOURS,
DOWN_HOURS,
OFF_HOURS,
0 QTY_COMPLETED,
0 QTY_SCRAP,
0 QTY_REJECTED,
0 QTY_REWORK,
0 QTY_GOOD,
0 QTY_OUTPUT,
SYSDATE CREATION_DATE,
SYSDATE LAST_UPDATE_DATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID,
MTH_UTIL_PKG.MTH_UA_GET_VAL() LAST_UPDATE_SYSTEM_ID,
MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATED_BY
FROM (
SELECT EQUIPMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
Min(STATUS_FROM_DATE) NEW_FROM_DATE,
Max(STATUS_TO_DATE) NEW_TO_DATE,
Max(CALC_STATUS_TO_DATE) TEMP_TO_DATE,
Sum(CASE WHEN STATUS = '1'
THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
ELSE 0 END) RUN_HOURS,
Sum(CASE WHEN STATUS = '2'
THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
ELSE 0 END) IDLE_HOURS,
Sum(CASE WHEN STATUS = '3'
THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
ELSE 0 END) DOWN_HOURS,
Sum(CASE WHEN STATUS = '4'
THEN (((NVL(STATUS_TO_DATE, SYSDATE) - STATUS_FROM_DATE)*24)+(1/3600))
ELSE 0 END) OFF_HOURS
FROM ( SELECT agg_sts.EQUIPMENT_FK_KEY,
agg_sts.SHIFT_WORKDAY_FK_KEY,
agg_sts.STATUS_FROM_DATE,
Nvl(agg_sts.STATUS_TO_DATE, Least(SYSDATE, h.TO_TIME, es.To_Date)) STATUS_TO_DATE,
NVL(agg_sts.STATUS_TO_DATE, SYSDATE+10) CALC_STATUS_TO_DATE,
agg_sts.STATUS,
agg_sts.HOUR_FK_KEY
FROM (
SELECT sts_all.EQUIPMENT_FK_KEY,
sts_all.SHIFT_WORKDAY_FK_KEY,
sts_all.FROM_DATE STATUS_FROM_DATE,
sts_all.To_Date STATUS_TO_DATE,
sts_all.STATUS,
sts_all.SYSTEM_FK_KEY,
sts_all.HOUR_FK_KEY
FROM MTH_EQUIP_STATUSES sts_all,
(SELECT sts.EQUIPMENT_FK_KEY,
sts.SHIFT_WORKDAY_FK_KEY,
sts.FROM_DATE STATUS_FROM_DATE,
sts.To_Date STATUS_TO_DATE,
sts.STATUS,
sts.SYSTEM_FK_KEY,
sts.HOUR_FK_KEY
FROM MTH_EQUIP_STATUSES sts,
(SELECT FACT_TABLE, FROM_DATE RUN_LOG_FROM_DATE,
TO_DATE RUN_LOG_TO_DATE
FROM MTH_RUN_LOG
WHERE FACT_TABLE = 'MTH_EQUIP_NON_PROD_EPP'
AND ROWNUM =1) run_log,
MTH_EQUIPMENTS_D equip
WHERE sts.LAST_UPDATE_DATE > run_log.RUN_LOG_FROM_DATE
AND sts.LAST_UPDATE_DATE <= run_log.RUN_LOG_TO_DATE
AND equip.EQUIPMENT_PK_KEY = sts.EQUIPMENT_FK_KEY
AND Upper(Nvl(equip.PRODUCTION_EQUIPMENT, 'N')) = 'N')sts_new_rows
WHERE sts_all.EQUIPMENT_FK_KEY = sts_new_rows.EQUIPMENT_FK_KEY
AND sts_all.SHIFT_WORKDAY_FK_KEY = sts_new_rows.SHIFT_WORKDAY_FK_KEY
AND sts_all.HOUR_FK_KEY = sts_new_rows.HOUR_FK_KEY ) agg_sts,
MTH_EQUIPMENT_SHIFTS_D es, MTH_HOUR_D h
WHERE es.ENTITY_TYPE = 'EQUIPMENT'
AND es.SHIFT_WORKDAY_FK_KEY = agg_sts.SHIFT_WORKDAY_FK_KEY
AND es.EQUIPMENT_FK_KEY = agg_sts.EQUIPMENT_FK_KEY
AND h.HOUR_PK_KEY = agg_sts.HOUR_FK_KEY
GROUP BY agg_sts.EQUIPMENT_FK_KEY, agg_sts.SHIFT_WORKDAY_FK_KEY, agg_sts.HOUR_FK_KEY, agg_sts.STATUS,
agg_sts.STATUS_FROM_DATE, STATUS_TO_DATE,
Nvl(agg_sts.STATUS_TO_DATE, Least(SYSDATE, h.TO_TIME, es.To_Date)),
NVL(agg_sts.STATUS_TO_DATE, SYSDATE+10))
GROUP BY EQUIPMENT_FK_KEY, SHIFT_WORKDAY_FK_KEY, HOUR_FK_KEY)) MERGE_QUERY
ON (MTH_EQUIP_PROD_PERFORMANCE_F.EQUIPMENT_FK_KEY = MERGE_QUERY.EQUIPMENT_FK_KEY
AND MTH_EQUIP_PROD_PERFORMANCE_F.SHIFT_WORKDAY_FK_KEY = MERGE_QUERY.SHIFT_WORKDAY_FK_KEY
AND MTH_EQUIP_PROD_PERFORMANCE_F.ACTUAL_FROM_DATE = MERGE_QUERY.NEW_FROM_DATE
AND MTH_EQUIP_PROD_PERFORMANCE_F.WORKORDER_FK_KEY = MERGE_QUERY.WORKORDER_FK_KEY
AND MTH_EQUIP_PROD_PERFORMANCE_F.WORKORDER_FK_KEY = MERGE_QUERY.SEGMENT_FK_KEY
AND MTH_EQUIP_PROD_PERFORMANCE_F.WORKORDER_FK_KEY = MERGE_QUERY.ITEM_FK_KEY)
WHEN MATCHED THEN
UPDATE
SET ACTUAL_TO_DATE = MERGE_QUERY.NEW_TO_DATE,
SYSTEM_FK_KEY = MERGE_QUERY.SYSTEM_FK_KEY,
RUN_HOURS = MERGE_QUERY.RUN_HOURS,
IDLE_HOURS = MERGE_QUERY.IDLE_HOURS,
DOWN_HOURS = MERGE_QUERY.DOWN_HOURS,
OFF_HOURS = MERGE_QUERY.OFF_HOURS,
QTY_COMPLETED = MERGE_QUERY.QTY_COMPLETED,
QTY_SCRAP = MERGE_QUERY.QTY_SCRAP,
QTY_REJECTED = MERGE_QUERY.QTY_REJECTED,
QTY_REWORK = MERGE_QUERY.QTY_REWORK,
QTY_GOOD = MERGE_QUERY.QTY_GOOD,
QTY_OUTPUT = MERGE_QUERY.QTY_OUTPUT,
HOUR_FK_KEY = MERGE_QUERY.HOUR_FK_KEY,
LAST_UPDATE_DATE = MERGE_QUERY.LAST_UPDATE_DATE,
LAST_UPDATE_SYSTEM_ID = MERGE_QUERY.LAST_UPDATE_SYSTEM_ID,
CREATED_BY = MERGE_QUERY.CREATED_BY
WHEN NOT MATCHED THEN
INSERT (EQUIPMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
SYSTEM_FK_KEY,
WORKORDER_FK_KEY,
SEGMENT_FK_KEY,
ITEM_FK_KEY,
ACTUAL_FROM_DATE,
ACTUAL_TO_DATE,
RUN_HOURS,
IDLE_HOURS,
DOWN_HOURS,
OFF_HOURS,
QTY_COMPLETED,
QTY_SCRAP,
QTY_REJECTED,
QTY_REWORK,
QTY_GOOD,
QTY_OUTPUT,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
CREATED_BY,
EQUIP_PROD_PERF_PK_KEY)
VALUES(
MERGE_QUERY.EQUIPMENT_FK_KEY,
MERGE_QUERY.SHIFT_WORKDAY_FK_KEY,
MERGE_QUERY.HOUR_FK_KEY,
MERGE_QUERY.SYSTEM_FK_KEY,
MERGE_QUERY.WORKORDER_FK_KEY,
MERGE_QUERY.SEGMENT_FK_KEY,
MERGE_QUERY.ITEM_FK_KEY,
MERGE_QUERY.NEW_FROM_DATE,
MERGE_QUERY.NEW_TO_DATE,
MERGE_QUERY.RUN_HOURS,
MERGE_QUERY.IDLE_HOURS,
MERGE_QUERY.DOWN_HOURS,
MERGE_QUERY.OFF_HOURS,
MERGE_QUERY.QTY_COMPLETED,
MERGE_QUERY.QTY_SCRAP,
MERGE_QUERY.QTY_REJECTED,
MERGE_QUERY.QTY_REWORK,
MERGE_QUERY.QTY_GOOD,
MERGE_QUERY.QTY_OUTPUT,
MERGE_QUERY.CREATION_DATE,
MERGE_QUERY.LAST_UPDATE_DATE,
MERGE_QUERY.CREATION_SYSTEM_ID,
MERGE_QUERY.LAST_UPDATE_SYSTEM_ID,
MERGE_QUERY.CREATED_BY,
MTH_EQUIP_PROD_PERF_S.NEXTVAL) ;
* Procedure :update_EPP_from_status *
* Description : Update run_hours,idle_hours,down_hours,off_hours *
* of EPP from status table *
* File Name :MTHPDPB.PLS *
* Visibility :Public *
* Parameters :None *
* Return Value :None *
**************************************************************************** */
PROCEDURE update_EPP_from_status(p_run_mode VARCHAR2)
IS
v_fact_table_name VARCHAR2(30) := 'MTH_EQUIP_PROD_PERFORMANCE_F_S';
v_last_update_date DATE;
v_last_update_date := SYSDATE;
mth_util_pkg.log_msg('UPDATE_EPP_FROM_STATUS start', mth_util_pkg.G_DBG_PROC_FUN_START);
SELECT Sum(Decode(sts_shift.status,1,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) run_hours,
Sum(Decode(sts_shift.status,3,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) down_hours,
Sum(Decode(sts_shift.status,2,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) idle_hours,
Sum(Decode(sts_shift.status,4,((Least(epp.prod_perf_actual_to_date,sts_shift.status_to_date)-Greatest(epp.actual_from_date,sts_shift.from_date))*24)+(1/3600),0)) off_hours,
epp.equipment_fk_key,
epp.workorder_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
epp.hour_fk_key,
epp.item_fk_key,
epp.actual_from_date
FROM (SELECT DISTINCT epp.equipment_fk_key,
epp.workorder_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
epp.item_fk_key,
epp.actual_from_date,
epp.actual_to_date,
epp.run_hours,
epp.down_hours,
epp.idle_hours,
epp.hour_fk_key,
Nvl(epp.actual_to_date,SYSDATE) prod_perf_actual_to_date,
epp.last_update_date
FROM mth_equip_prod_performance_f epp,
mth_equip_statuses sts
WHERE epp.equipment_fk_key = sts.equipment_fk_key
AND epp.shift_workday_fk_key = sts.shift_workday_fk_key
AND epp.hour_fk_key = sts.hour_fk_key)epp,
(SELECT sts.equipment_fk_key,
sts.shift_workday_fk_key,
sts.hour_fk_key,
sts.from_date,
sts.status,
Min(sts.To_Date) to_date,
Least(Nvl(Min(Decode(sts.To_Date,NULL,mhd.to_time,sts.To_Date)),SYSDATE),Max(shift.To_Date)) status_to_date
FROM mth_equip_statuses sts,
mth_equipment_shifts_d shift,
mth_hour_d mhd
WHERE sts.equipment_fk_key = shift.equipment_fk_key
AND sts.shift_workday_fk_key = shift.shift_workday_fk_key
AND sts.hour_fk_key = mhd.hour_pk_key
GROUP BY sts.equipment_fk_key,
sts.shift_workday_fk_key,
sts.hour_fk_key,
sts.from_date,
sts.status,
shift.equipment_fk_key,
shift.shift_workday_fk_key) sts_shift,
(SELECT from_date, To_Date
FROM mth_run_log
WHERE fact_table = v_fact_table_name
AND ROWNUM = 1) mrl
WHERE epp.equipment_fk_key = sts_shift.equipment_fk_key
AND epp.shift_workday_fk_key = sts_shift.shift_workday_fk_key
AND epp.last_update_date > mrl.from_date AND epp.last_update_date <= mrl.to_date
AND epp.hour_fk_key = sts_shift.hour_fk_key
AND (( epp.prod_perf_actual_to_date BETWEEN sts_shift.from_date AND sts_shift.status_to_date )
OR ( epp.actual_from_date BETWEEN sts_shift.from_date AND sts_shift.status_to_date )
OR ( sts_shift.status_to_date BETWEEN epp.actual_from_date AND epp.prod_perf_actual_to_date )
OR ( sts_shift.from_date BETWEEN epp.actual_from_date AND epp.prod_perf_actual_to_date ))
GROUP BY epp.equipment_fk_key,
epp.workorder_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
epp.hour_fk_key,
epp.item_fk_key,
epp.actual_from_date) cal_hours
ON
( eppf.equipment_fk_key = cal_hours.equipment_fk_key
AND eppf.workorder_fk_key = cal_hours.workorder_fk_key
AND eppf.segment_fk_key = cal_hours.segment_fk_key
AND eppf.shift_workday_fk_key = cal_hours.shift_workday_fk_key
AND eppf.item_fk_key = cal_hours.item_fk_key
AND eppf.actual_from_date = cal_hours.actual_from_date
AND eppf.hour_fk_key = cal_hours.hour_fk_key
)
WHEN MATCHED THEN
UPDATE
SET
eppf.last_update_date = v_last_update_date,
eppf.run_hours = cal_hours.run_hours,
eppf.down_hours = cal_hours.down_hours,
eppf.idle_hours = cal_hours.idle_hours,
eppf.off_hours = cal_hours.off_hours;
mth_util_pkg.log_msg('UPDATE_EPP_FROM_STATUS end', mth_util_pkg.G_DBG_PROC_FUN_END);
mth_util_pkg.log_msg('Exception OTHERS in update_EPP_from_status', mth_util_pkg.G_DBG_EXCEPTION);
END update_EPP_from_status;
SELECT epp.equip_prod_perf_fk_key,
epp.equipment_fk_key,
epp.system_fk_key,
sts.status,
Greatest(epp.actual_from_date, sts.from_date) from_time,
Least(epp.actual_to_date,sts.to_date) to_time,
epp.workorder_fk_key,
epp.item_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
epp.hour_fk_key,
((Least(epp.actual_to_date,sts.to_date) - Greatest(epp.actual_from_date,sts.from_date )) * 24 + 1/3600) duration_in_hours,
v_log_date creation_date,
v_log_date last_update_date,
v_ua_val creation_system_id,
v_ua_val last_update_system_id,
v_ua_val created_by,
v_ua_val last_updated_by,
v_ua_val last_update_login
FROM (SELECT DISTINCT Max(epp.equip_prod_perf_pk_key) equip_prod_perf_fk_key,
epp.equipment_fk_key,
epp.actual_from_date,
epp.actual_to_date,
epp.workorder_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
Max(epp.item_fk_key) item_fk_key,
Max(epp.system_fk_key) system_fk_key,
epp.hour_fk_key
FROM mth_equip_prod_performance_f epp,
mth_run_log mrl
WHERE epp.actual_to_date IS NOT NULL
AND (epp.run_hours > 0 OR epp.down_hours > 0 OR epp.idle_hours > 0 OR epp.off_hours > 0)
AND mrl.fact_table = 'MTH_EQUIP_PROD_PERF_DETAIL_F'
AND epp.last_update_date > mrl.from_date AND epp.last_update_date <= mrl.To_Date
GROUP BY epp.equipment_fk_key,
epp.actual_from_date,
epp.actual_to_date,
epp.workorder_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
epp.hour_fk_key) epp,
mth_equip_statuses sts
WHERE epp.equipment_fk_key = sts.equipment_fk_key
AND ((epp.actual_from_date >= sts.from_date AND epp.actual_from_date <= sts.to_date)
OR (epp.actual_to_date >= sts.from_date AND epp.actual_to_date <= sts.to_date )
OR (sts.from_date >= epp.actual_from_date AND sts.from_date <= epp.actual_to_date ))
AND sts.to_date IS NOT NULL) epp
ON
(
epd.equipment_fk_key = epp.equipment_fk_key
AND epd.status = epp.status
AND epd.from_time = epp.from_time
AND epd.to_time = epp.to_time
)
WHEN MATCHED
THEN
UPDATE
SET
epd.system_fk_key = epp.system_fk_key
WHEN NOT MATCHED
THEN
INSERT
( epd.epp_detail_pk_key,
epd.equipment_fk_key,
epd.system_fk_key,
epd.status,
epd.from_time,
epd.to_time,
epd.workorder_fk_key,
epd.item_fk_key,
epd.segment_fk_key,
epd.shift_workday_fk_key,
epd.hour_fk_key,
epd.duration_in_hours,
epd.creation_date,
epd.last_update_date,
epd.creation_system_id,
epd.last_update_system_id,
epd.created_by,
epd.last_updated_by,
epd.last_udpate_login,
epd.equip_prod_perf_fk_key)
VALUES
( mth_equip_prod_perf_detail_f_s.NEXTVAL,
epp.equipment_fk_key,
epp.system_fk_key,
epp.status,
epp.from_time,
epp.to_time,
epp.workorder_fk_key,
epp.item_fk_key,
epp.segment_fk_key,
epp.shift_workday_fk_key,
epp.hour_fk_key,
epp.duration_in_hours,
epp.creation_date,
epp.last_update_date,
epp.creation_system_id,
epp.last_update_system_id,
epp.created_by,
epp.last_updated_by,
epp.last_update_login,
epp.equip_prod_perf_fk_key);
mth_util_pkg.log_msg('Exception OTHERS in update_EPP_from_status', mth_util_pkg.G_DBG_EXCEPTION);
SELECT epp_sust_aspect.epp_detail_pk_key epp_detail_fk_key,
epp_sust_aspect.sustain_aspect_fk_key,
Sum(epp_sust_aspect.usage_value *
(Least(epp_sust_aspect.epp_to_time,epp_sust_aspect.esa_to_time) -
Greatest(epp_sust_aspect.epp_from_time,epp_sust_aspect.esa_from_time) + 1/86400) /
(epp_sust_aspect.esa_to_time - epp_sust_aspect.esa_from_time + 1/86400) +
Nvl(eps.sustain_aspect_usage_value,0)) sustain_aspect_usage_value,
Max(epp_sust_aspect.usage_uom) sustain_aspect_uom,
Sum(epp_sust_aspect.usage_cost *
(Least(epp_sust_aspect.epp_to_time,epp_sust_aspect.esa_to_time) -
Greatest(epp_sust_aspect.epp_from_time,epp_sust_aspect.esa_from_time) + 1/86400) /
(epp_sust_aspect.esa_to_time - epp_sust_aspect.esa_from_time + 1/86400) +
Nvl(eps.sustain_aspect_usage_cost,0)) sustain_aspect_usage_cost,
epp_sust_aspect.equip_prod_perf_fk_key
FROM mth_equip_prod_sustain_f eps,
(SELECT epp.epp_detail_pk_key,
esa.sustain_aspect_fk_key,
esa.from_time esa_from_time,
esa.to_time esa_to_time,
esa.usage_value,
esa.usage_cost,
epp.from_time epp_from_time,
epp.to_time epp_to_time,
esa.usage_uom usage_uom,
epp.equip_prod_perf_fk_key equip_prod_perf_fk_key
FROM mth_equip_prod_perf_detail_f epp,
mth_entity_sustain_aspect esa,
(SELECT DISTINCT from_date,
To_Date
FROM mth_run_log
WHERE fact_table = 'MTH_EQUIP_PROD_SUSTAIN_F') mrl
WHERE ((esa.last_update_date > mrl.from_date AND esa.last_update_date <= mrl.To_Date)
OR (epp.last_update_date > mrl.from_date AND epp.last_update_date <= mrl.To_Date))
AND esa.entity_fk_key = epp.equipment_fk_key
AND esa.entity_type IN ('Equipment', 'EQUIPMENT')
AND ((epp.from_time >= esa.from_time AND epp.from_time <= esa.to_time )
OR ( epp.to_time >= esa.from_time AND epp.to_time <= esa.to_time )
OR ( esa.from_time >= epp.from_time AND esa.from_time <= epp.to_time ))
AND esa.hour_fk_key = epp.hour_fk_key) epp_sust_aspect
WHERE epp_sust_aspect.epp_detail_pk_key = eps.epp_detail_fk_key (+)
AND epp_sust_aspect.sustain_aspect_fk_key = eps.sustain_aspect_fk_key(+)
GROUP BY epp_sust_aspect.equip_prod_perf_fk_key,
epp_sust_aspect.epp_detail_pk_key,
epp_sust_aspect.sustain_aspect_fk_key) merge_subquery
ON
( epsf.epp_detail_fk_key = merge_subquery.epp_detail_fk_key
AND epsf.sustain_aspect_fk_key = merge_subquery.sustain_aspect_fk_key
AND epsf.equip_prod_perf_fk_key = merge_subquery.equip_prod_perf_fk_key)
WHEN MATCHED THEN
UPDATE
SET
epsf.sustain_aspect_usage_value = merge_subquery.sustain_aspect_usage_value,
epsf.sustain_aspect_uom = merge_subquery.sustain_aspect_uom,
epsf.sustain_aspect_usage_cost = merge_subquery.sustain_aspect_usage_cost,
epsf.last_update_date = v_log_date,
epsf.last_update_system_id = v_ua_val,
epsf.last_updated_by = v_ua_val,
epsf.last_update_login = v_ua_val
WHEN NOT MATCHED THEN
INSERT
(epsf.epp_detail_fk_key,
epsf.sustain_aspect_fk_key,
epsf.sustain_aspect_usage_value,
epsf.sustain_aspect_uom,
epsf.sustain_aspect_usage_cost,
epsf.creation_date,
epsf.last_update_date,
epsf.creation_system_id,
epsf.last_update_system_id,
epsf.created_by,
epsf.last_updated_by,
epsf.last_update_login,
epsf.equip_prod_perf_fk_key)
VALUES
(merge_subquery.epp_detail_fk_key,
merge_subquery.sustain_aspect_fk_key,
merge_subquery.sustain_aspect_usage_value,
merge_subquery.sustain_aspect_uom,
merge_subquery.sustain_aspect_usage_cost,
v_log_date,
v_log_date,
v_ua_val,
v_ua_val,
v_ua_val,
v_ua_val,
v_ua_val,
merge_subquery.equip_prod_perf_fk_key);