[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 IN ('Scrap Quantity',
'Rejected Quantity',
'Output Quantity',
'Rework Quantity',
'Completed Quantity')
AND mrl.fact_table = 'MTH_EQUIP_OUTPUT'
AND mtr.last_update_date > mrl.from_date;
SELECT Count(1)
INTO v_output_count
FROM mth_equip_output meo,
mth_run_log mrl
WHERE mrl.fact_table = 'MTH_EQUIP_OUTPUT_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_ITEM_COST_MV','MTH_RESOURCE_COST_MV')
AND owner = sys_context('USERENV','CURRENT_SCHEMA')
AND compile_state <> 'VALID' ;
DELETE FROM MTH_EQUIP_OUTPUT;
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_EQUIP_OUTPUT (
EQUIPMENT_FK_KEY,
ITEM_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
WORKORDER_FK_KEY,
HOUR_FK_KEY,
READING_TIME,
QTY_COMPLETED,
QTY_SCRAP,
QTY_REJECTED,
QTY_REWORK,
QTY_GOOD,
QTY_OUTPUT,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
RECIPE_NUM,
RECIPE_VERSION,
SEGMENT_FK_KEY
)
SELECT a.EQUIPMENT_FK_KEY,
a.ITEM_FK_KEY,
a.SHIFT_WORKDAY_FK_KEY,
a.WORKORDER_FK_KEY,
a.HOUR_FK_KEY,
a.READING_TIME,
SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_COMPLETED,
SUM((CASE b.MTH_ALIAS WHEN 'Scrap Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_SCRAP,
SUM((CASE b.MTH_ALIAS WHEN 'Rejected Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REJECTED,
SUM((CASE b.MTH_ALIAS WHEN 'Rework Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REWORK,
SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_GOOD,
SUM((CASE b.MTH_ALIAS WHEN 'Output Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_OUTPUT,
v_unassigned_val,
SYSDATE,
SYSDATE,
v_unassigned_val,
v_unassigned_val,
a.RECIPE_NUM,
a.RECIPE_VERSION,
a.SEGMENT_FK_KEY
FROM MTH_TAG_READINGS a,
MTH_ENTITIES b
WHERE a.MTH_ENTITY = b.ID
AND a.EQUIPMENT_FK_KEY IS NOT NULL
AND a.WORKORDER_FK_KEY IS NOT NULL
AND a.SEGMENT_FK_KEY IS NOT NULL
AND a.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND a.ITEM_FK_KEY IS NOT NULL
AND a.HOUR_FK_KEY IS NOT NULL
AND b.MTH_ALIAS IN ( 'Completed Quantity' , 'Output Quantity' , 'Rejected Quantity' , 'Scrap Quantity' , 'Rework Quantity' )
AND a.LAST_UPDATE_DATE <= v_log_date
GROUP BY a.READING_TIME,
a.EQUIPMENT_FK_KEY,
a.WORKORDER_FK_KEY,
a.ITEM_FK_KEY,
a.SHIFT_WORKDAY_FK_KEY,
a.RECIPE_NUM,
a.RECIPE_VERSION,
a.SEGMENT_FK_KEY,
a.HOUR_FK_KEY;
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || 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.WORKORDER_FK_KEY IS NOT NULL
AND t.SEGMENT_FK_KEY IS NOT NULL
AND t.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND t.ITEM_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
AND t.LAST_UPDATE_DATE <= v_log_date);
mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
(SELECT a.EQUIPMENT_FK_KEY,
a.ITEM_FK_KEY,
a.SHIFT_WORKDAY_FK_KEY,
a.WORKORDER_FK_KEY,
a.HOUR_FK_KEY,
a.READING_TIME,
SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_COMPLETED,
SUM((CASE b.MTH_ALIAS WHEN 'Scrap Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_SCRAP,
SUM((CASE b.MTH_ALIAS WHEN 'Rejected Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REJECTED,
SUM((CASE b.MTH_ALIAS WHEN 'Rework Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REWORK,
SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_GOOD,
SUM((CASE b.MTH_ALIAS WHEN 'Output Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_OUTPUT,
v_unassigned_val SYSTEM_FK_KEY,
a.RECIPE_NUM,
a.RECIPE_VERSION,
a.SEGMENT_FK_KEY
FROM MTH_TAG_READINGS a,
MTH_ENTITIES b
WHERE a.MTH_ENTITY = b.ID
AND a.EQUIPMENT_FK_KEY IS NOT NULL
AND a.WORKORDER_FK_KEY IS NOT NULL
AND a.SEGMENT_FK_KEY IS NOT NULL
AND a.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND a.ITEM_FK_KEY IS NOT NULL
AND a.HOUR_FK_KEY IS NOT NULL
AND a.PROCESSED_FLAG = 0
AND b.MTH_ALIAS IN ( 'Completed Quantity' , 'Output Quantity' , 'Rejected Quantity' , 'Scrap Quantity' , 'Rework Quantity' )
AND a.LAST_UPDATE_DATE > v_log_from_date and a.LAST_UPDATE_DATE <=v_log_to_date
GROUP BY a.READING_TIME,
a.EQUIPMENT_FK_KEY,
a.WORKORDER_FK_KEY,
a.ITEM_FK_KEY,
a.SHIFT_WORKDAY_FK_KEY,
a.RECIPE_NUM,
a.RECIPE_VERSION,
a.SEGMENT_FK_KEY,
a.HOUR_FK_KEY) tr
ON (o.EQUIPMENT_FK_KEY = tr.EQUIPMENT_FK_KEY
AND o.ITEM_FK_KEY = tr.ITEM_FK_KEY
AND o.SHIFT_WORKDAY_FK_KEY = tr.SHIFT_WORKDAY_FK_KEY
AND o.WORKORDER_FK_KEY = tr.WORKORDER_FK_KEY
AND o.HOUR_FK_KEY = tr.HOUR_FK_KEY
AND o.READING_TIME = tr.READING_TIME
AND o.SEGMENT_FK_KEY = tr.SEGMENT_FK_KEY
AND nvl(o.RECIPE_NUM,'@@@') = nvl(tr.RECIPE_NUM,'@@@')
AND nvl(o.RECIPE_VERSION,'@@@') = nvl(tr.RECIPE_VERSION,'@@@')
AND o.SYSTEM_FK_KEY = tr.SYSTEM_FK_KEY)
WHEN MATCHED THEN
UPDATE SET
o.QTY_COMPLETED = o.QTY_COMPLETED + tr.QTY_COMPLETED,
o.QTY_SCRAP = o.QTY_SCRAP + tr.QTY_SCRAP,
o.QTY_REJECTED = o.QTY_REJECTED + tr.QTY_REJECTED,
o.QTY_REWORK = o.QTY_REWORK + tr.QTY_REWORK,
o.QTY_GOOD = o.QTY_GOOD + tr.QTY_GOOD,
o.QTY_OUTPUT = o.QTY_OUTPUT + tr.QTY_OUTPUT,
o.LAST_UPDATE_DATE = SYSDATE,
o.LAST_UPDATE_SYSTEM_ID = v_unassigned_val
WHEN NOT MATCHED THEN
INSERT (
o.EQUIPMENT_FK_KEY,
o.ITEM_FK_KEY,
o.SHIFT_WORKDAY_FK_KEY,
o.WORKORDER_FK_KEY,
o.HOUR_FK_KEY,
o.READING_TIME,
o.QTY_COMPLETED,
o.QTY_SCRAP,
o.QTY_REJECTED,
o.QTY_REWORK,
o.QTY_GOOD,
o.QTY_OUTPUT,
o.SYSTEM_FK_KEY,
o.CREATION_DATE,
o.LAST_UPDATE_DATE,
o.CREATION_SYSTEM_ID,
o.LAST_UPDATE_SYSTEM_ID,
o.RECIPE_NUM,
o.RECIPE_VERSION,
o.SEGMENT_FK_KEY
)
VALUES
(
tr.EQUIPMENT_FK_KEY,
tr.ITEM_FK_KEY,
tr.SHIFT_WORKDAY_FK_KEY,
tr.WORKORDER_FK_KEY,
tr.HOUR_FK_KEY,
tr.READING_TIME,
tr.QTY_COMPLETED,
tr.QTY_SCRAP,
tr.QTY_REJECTED,
tr.QTY_REWORK,
tr.QTY_GOOD,
tr.QTY_OUTPUT,
v_unassigned_val,
SYSDATE,
SYSDATE,
v_unassigned_val,
v_unassigned_val,
tr.RECIPE_NUM,
tr.RECIPE_VERSION,
tr.SEGMENT_FK_KEY
);
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.WORKORDER_FK_KEY IS NOT NULL
AND t.SEGMENT_FK_KEY IS NOT NULL
AND t.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND t.ITEM_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
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);
DELETE FROM MTH_EQUIP_OUTPUT o
WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
AND o.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.READING_TIME);
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_EQUIP_OUTPUT (
EQUIPMENT_FK_KEY,
ITEM_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
WORKORDER_FK_KEY,
HOUR_FK_KEY,
READING_TIME,
QTY_COMPLETED,
QTY_SCRAP,
QTY_REJECTED,
QTY_REWORK,
QTY_GOOD,
QTY_OUTPUT,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
RECIPE_NUM,
RECIPE_VERSION,
SEGMENT_FK_KEY
)
SELECT a.EQUIPMENT_FK_KEY,
a.ITEM_FK_KEY,
a.SHIFT_WORKDAY_FK_KEY,
a.WORKORDER_FK_KEY,
a.HOUR_FK_KEY,
a.READING_TIME,
SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_COMPLETED,
SUM((CASE b.MTH_ALIAS WHEN 'Scrap Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_SCRAP,
SUM((CASE b.MTH_ALIAS WHEN 'Rejected Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REJECTED,
SUM((CASE b.MTH_ALIAS WHEN 'Rework Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_REWORK,
SUM((CASE b.MTH_ALIAS WHEN 'Completed Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_GOOD,
SUM((CASE b.MTH_ALIAS WHEN 'Output Quantity' THEN TO_NUMBER(a.TAG_DATA) ELSE 0 END)) QTY_OUTPUT,
v_unassigned_val,
SYSDATE,
SYSDATE,
v_unassigned_val,
v_unassigned_val,
a.RECIPE_NUM,
a.RECIPE_VERSION,
a.SEGMENT_FK_KEY
FROM MTH_TAG_READINGS a,
MTH_ENTITIES b
WHERE a.MTH_ENTITY = b.ID
AND a.EQUIPMENT_FK_KEY IS NOT NULL
AND a.WORKORDER_FK_KEY IS NOT NULL
AND a.SEGMENT_FK_KEY IS NOT NULL
AND a.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND a.ITEM_FK_KEY IS NOT NULL
AND a.HOUR_FK_KEY IS NOT NULL
AND a.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key, a.EQUIPMENT_FK_KEY)
AND b.MTH_ALIAS IN ( 'Completed Quantity' , 'Output Quantity' , 'Rejected Quantity' , 'Scrap Quantity' , 'Rework Quantity' )
AND a.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,a.READING_TIME)
GROUP BY a.READING_TIME,
a.EQUIPMENT_FK_KEY,
a.WORKORDER_FK_KEY,
a.ITEM_FK_KEY,
a.SHIFT_WORKDAY_FK_KEY,
a.RECIPE_NUM,
a.RECIPE_VERSION,
a.SEGMENT_FK_KEY,
a.HOUR_FK_KEY;
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || 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.WORKORDER_FK_KEY IS NOT NULL
AND t.SEGMENT_FK_KEY IS NOT NULL
AND t.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND t.ITEM_FK_KEY IS NOT NULL
AND t.HOUR_FK_KEY IS NOT NULL
AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
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);
DELETE FROM MTH_EQUIP_OUTPUT;
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO mth_equip_output_stg(equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code)
(SELECT equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code
FROM mth_equip_output_err
WHERE reprocess_ready_yn = 'Y');
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_OUTPUT_ERR
WHERE REPROCESS_READY_YN = 'Y';
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'ITM '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
FROM mth_items_d mid,
mth_equip_output_stg stg
WHERE mid.item_pk = stg.item_fk) itm
WHERE itm.item_pk = stg.item_fk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'SEG '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
FROM mth_production_segments_f msf,
mth_equip_output_stg stg
WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk)
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'WKO '
WHERE stg.workorder_fk IS NOT NULL
AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
FROM mth_production_schedules_f mps,
mth_equip_output_stg stg
WHERE stg.workorder_fk = mps.workorder_pk(+)
AND stg.workorder_fk IS NOT NULL) wko
WHERE wko.workorder_pk = stg.workorder_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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_output_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_output_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
FROM mth_equip_output_stg
GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
WHERE dup.cnt>1
AND dup.equipment_fk = stg.equipment_fk
AND dup.shift_workday_fk = stg.shift_workday_fk
AND dup.reading_time = stg.reading_time
AND dup.item_fk = stg.item_fk
AND dup.workorder_fk = stg.workorder_fk
AND dup.segment_fk = stg.segment_fk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_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_output_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_output_stg stg
SET stg.err_code = stg.err_code || 'NWDS '
WHERE stg.shift_workday_fk IS NULL
AND stg.processing_flag = v_processing_flag;
UPDATE mth_equip_output_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_output_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
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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_output_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_output_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_output_stg stg
SET stg.err_code = stg.err_code || 'SPR '
WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
FROM fnd_lookups flk,
mth_equip_output_stg stg
WHERE stg.scrap_reason_code = flk.lookup_code (+)
AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR
stg.scrap_reason_code <> dtr.lookup_code)
AND dtr.reading_time = stg.reading_time
AND dtr.qty_scrap = stg.qty_scrap
AND dtr.scrap_reason_code = stg.scrap_reason_code
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'FTD '
WHERE stg.reading_time > SYSDATE
AND stg.processing_flag = v_processing_flag;
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'ITR '
WHERE NOT 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_output_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.reading_time >= mes.from_date
AND stg.reading_time <= mes.to_date) itr
WHERE itr.shift_workday_pk = stg.shift_workday_fk
AND itr.equipment_pk = stg.equipment_fk
AND stg.reading_time >= itr.from_date
AND stg.reading_time <= itr.to_date
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'DOP '
WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
FROM mth_equip_output meo,
mth_equip_output_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds
WHERE med.equipment_pk_key = meo.equipment_fk_key
AND wds.shift_workday_pk_key = meo.shift_workday_fk_key
AND med.equipment_pk = stg.equipment_fk
AND wds.shift_workday_pk = stg.shift_workday_fk
AND meo.reading_time = stg.reading_time) dop
WHERE dop.reading_time = stg.reading_time
AND dop.equipment_pk = stg.equipment_fk
AND dop.shift_workday_pk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag );
INSERT INTO mth_equip_output_err(equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code,
reprocess_ready_yn,
err_code)
(SELECT equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code,
'N',
err_code
FROM mth_equip_output_stg
WHERE err_code IS NOT NULL);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO mth_equip_output( equipment_fk_key,
item_fk_key,
shift_workday_fk_key,
workorder_fk_key,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk_key,
recipe_version,
recipe_num,
segment_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,
hour_fk_key )
(SELECT med.equipment_pk_key ,
mid.item_pk_key ,
wds.shift_workday_pk_key ,
mps.workorder_pk_key ,
stg.reading_time ,
stg.qty_completed,
stg.qty_scrap,
stg.qty_rejected,
stg.qty_rework,
stg.qty_uom,
stg.qty_good,
stg.qty_output,
Nvl(mss.system_pk_key,v_unassigned_val) ,
stg.recipe_version ,
stg.recipe_num,
msf.segment_pk_key,
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,
mhd.hour_pk_key
FROM mth_equip_output_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_items_d mid,
mth_production_segments_f msf,
mth_production_schedules_f mps,
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.ITEM_FK = mid.ITEM_PK (+)
AND stg.WORKORDER_FK = mps.WORKORDER_PK (+)
AND stg.SEGMENT_FK = msf.SEGMENT_PK (+)
AND stg.reading_time BETWEEN mhd.from_time AND mhd.to_time
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_SCRAP_REASON'
AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
AND stg.err_code IS NULL
AND stg.processing_flag = v_processing_flag);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || 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)
(SELECT 2 reason_type,
med.equipment_pk_key,
stg.reading_time,
stg.reading_time,
stg.scrap_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
NULL,
stg.reading_time
FROM mth_equip_output_stg stg,
mth_equipments_d med
WHERE med.equipment_pk = stg.equipment_fk
AND med.status = 'ACTIVE'
AND stg.processing_flag = v_processing_flag
AND stg.ERR_CODE IS NULL
AND stg.qty_scrap IS NOT 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_output_stg(equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code)
(SELECT equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code
FROM mth_equip_output_err
WHERE reprocess_ready_yn = 'Y');
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_OUTPUT_ERR
WHERE REPROCESS_READY_YN = 'Y';
mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'ITM '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
FROM mth_items_d mid,
mth_equip_output_stg stg
WHERE mid.item_pk = stg.item_fk) itm
WHERE itm.item_pk = stg.item_fk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'SEG '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
FROM mth_production_segments_f msf,
mth_equip_output_stg stg
WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk)
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'WKO '
WHERE stg.workorder_fk IS NOT NULL
AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
FROM mth_production_schedules_f mps,
mth_equip_output_stg stg
WHERE stg.workorder_fk = mps.workorder_pk(+)
AND stg.workorder_fk IS NOT NULL) wko
WHERE wko.workorder_pk = stg.workorder_fk
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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_output_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_output_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
WHERE eqp.equipment_pk = stg.equipment_fk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
FROM mth_equip_output_stg
GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
WHERE dup.cnt>1
AND dup.equipment_fk = stg.equipment_fk
AND dup.shift_workday_fk = stg.shift_workday_fk
AND dup.reading_time = stg.reading_time
AND dup.item_fk = stg.item_fk
AND dup.workorder_fk = stg.workorder_fk
AND dup.segment_fk = stg.segment_fk
AND stg.processing_flag = v_processing_flag );
UPDATE mth_equip_output_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_output_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_output_stg stg
SET stg.err_code = stg.err_code || 'NWDS '
WHERE stg.shift_workday_fk IS NULL
AND stg.processing_flag = v_processing_flag;
UPDATE mth_equip_output_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_output_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
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_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_output_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_output_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_output_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_output_stg stg
SET stg.err_code = stg.err_code || 'SPR '
WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
FROM fnd_lookups flk,
mth_equip_output_stg stg
WHERE stg.scrap_reason_code = flk.lookup_code (+)
AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR
stg.scrap_reason_code <> dtr.lookup_code)
AND dtr.reading_time = stg.reading_time
AND dtr.qty_scrap = stg.qty_scrap
AND dtr.scrap_reason_code = stg.scrap_reason_code
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'FTD '
WHERE stg.reading_time > SYSDATE
AND stg.processing_flag = v_processing_flag;
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'ITR '
WHERE NOT 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_output_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.reading_time >= mes.from_date
AND stg.reading_time <= mes.to_date) itr
WHERE itr.shift_workday_pk = stg.shift_workday_fk
AND itr.equipment_pk = stg.equipment_fk
AND stg.reading_time >= itr.from_date
AND stg.reading_time <= itr.to_date
AND stg.processing_flag = v_processing_flag);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'DOP '
WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
FROM mth_equip_output meo,
mth_equip_output_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds
WHERE med.equipment_pk_key = meo.equipment_fk_key
AND wds.shift_workday_pk_key = meo.shift_workday_fk_key
AND med.equipment_pk = stg.equipment_fk
AND wds.shift_workday_pk = stg.shift_workday_fk
AND meo.reading_time = stg.reading_time) dop
WHERE dop.reading_time = stg.reading_time
AND dop.equipment_pk = stg.equipment_fk
AND dop.shift_workday_pk = stg.shift_workday_fk
AND stg.processing_flag = v_processing_flag );
INSERT INTO mth_equip_output_err(equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code,
reprocess_ready_yn,
err_code)
(SELECT equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk,
recipe_version,
recipe_num,
segment_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,
scrap_reason_code,
'N',
err_code
FROM mth_equip_output_stg
WHERE err_code IS NOT NULL);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO mth_equip_output( equipment_fk_key,
item_fk_key,
shift_workday_fk_key,
workorder_fk_key,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk_key,
recipe_version,
recipe_num,
segment_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,
hour_fk_key )
(SELECT med.equipment_pk_key ,
mid.item_pk_key ,
wds.shift_workday_pk_key ,
mps.workorder_pk_key ,
stg.reading_time ,
stg.qty_completed,
stg.qty_scrap,
stg.qty_rejected,
stg.qty_rework,
stg.qty_uom,
stg.qty_good,
stg.qty_output,
Nvl(mss.system_pk_key,v_unassigned_val) ,
stg.recipe_version ,
stg.recipe_num,
msf.segment_pk_key,
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,
mhd.hour_pk_key
FROM mth_equip_output_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_items_d mid,
mth_production_segments_f msf,
mth_production_schedules_f mps,
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.ITEM_FK = mid.ITEM_PK (+)
AND stg.WORKORDER_FK = mps.WORKORDER_PK (+)
AND stg.SEGMENT_FK = msf.SEGMENT_PK (+)
AND stg.reading_time BETWEEN mhd.from_time AND mhd.to_time
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_SCRAP_REASON'
AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
AND stg.err_code IS NULL
AND stg.processing_flag = v_processing_flag);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || 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)
(SELECT 2 reason_type,
med.equipment_pk_key,
stg.reading_time,
stg.reading_time,
stg.scrap_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
NULL,
stg.reading_time
FROM mth_equip_output_stg stg,
mth_equipments_d med
WHERE med.equipment_pk = stg.equipment_fk
AND med.status = 'ACTIVE'
AND stg.processing_flag = v_processing_flag
AND stg.ERR_CODE IS NULL
AND stg.qty_scrap IS NOT NULL);
mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
SELECT equipment_fk,
item_fk,
shift_workday_fk,
workorder_fk,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
segment_fk,
user_dim1_fk,
user_dim2_fk,
user_dim3_fk,
user_dim4_fk,
user_dim5_fk,
scrap_reason_code,
err_code
FROM mth_equip_output_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_output_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(reading_time)
INTO p_min_reading_time_csv
FROM MTH_equip_output_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(reading_time)
INTO p_max_reading_time_csv
FROM MTH_equip_output_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);
DELETE
FROM mth_equip_output
WHERE reading_time >= p_recal_from_date
AND reading_time <= Nvl(p_recal_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_output
WHERE reading_time >= p_recal_from_date
AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
DELETE
FROM mth_tag_reason_readings
WHERE reading_time >= p_recal_from_date
AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
AND REASON_TYPE=2
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 reading_time >= p_recal_from_date
AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
AND REASON_TYPE=2
AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'ITM '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
FROM mth_items_d mid,
mth_equip_output_stg stg
WHERE mid.item_pk = stg.item_fk) itm
WHERE itm.item_pk = stg.item_fk );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'SEG '
WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
FROM mth_production_segments_f msf,
mth_equip_output_stg stg
WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk));
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'WKO '
WHERE stg.workorder_fk IS NOT NULL
AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
FROM mth_production_schedules_f mps,
mth_equip_output_stg stg
WHERE stg.workorder_fk = mps.workorder_pk(+)
AND stg.workorder_fk IS NOT NULL) wko
WHERE wko.workorder_pk = stg.workorder_fk);
UPDATE mth_equip_output_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_output_stg stg
WHERE med.equipment_pk = stg.equipment_fk) eqp
WHERE eqp.equipment_pk = stg.equipment_fk);
UPDATE mth_equip_output_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_output_stg stg
WHERE med.equipment_pk = stg.equipment_fk
AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
WHERE eqp.equipment_pk = stg.equipment_fk );
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'DUP '
WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
FROM mth_equip_output_stg
GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
WHERE dup.cnt>1
AND dup.equipment_fk = stg.equipment_fk
AND dup.shift_workday_fk = stg.shift_workday_fk
AND dup.reading_time = stg.reading_time
AND dup.item_fk = stg.item_fk
AND dup.workorder_fk = stg.workorder_fk
AND dup.segment_fk = stg.segment_fk );
UPDATE mth_equip_output_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_output_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_output_stg stg
SET stg.err_code = stg.err_code || 'NWDS '
WHERE stg.shift_workday_fk IS NULL;
UPDATE mth_equip_output_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_output_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_output_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_output_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_output_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_output_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_output_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_output_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_output_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_output_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_output_stg stg
SET stg.err_code = stg.err_code || 'SPR '
WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
FROM fnd_lookups flk,
mth_equip_output_stg stg
WHERE stg.scrap_reason_code = flk.lookup_code (+)
AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR
stg.scrap_reason_code <> dtr.lookup_code)
AND dtr.reading_time = stg.reading_time
AND dtr.qty_scrap = stg.qty_scrap
AND dtr.scrap_reason_code = stg.scrap_reason_code);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'FTD '
WHERE stg.reading_time > SYSDATE;
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'ITR '
WHERE NOT 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_output_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.reading_time >= mes.from_date
AND stg.reading_time <= mes.to_date) itr
WHERE itr.shift_workday_pk = stg.shift_workday_fk
AND itr.equipment_pk = stg.equipment_fk
AND stg.reading_time >= itr.from_date
AND stg.reading_time <= itr.to_date);
UPDATE mth_equip_output_stg stg
SET stg.err_code = stg.err_code || 'DOP '
WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
FROM mth_equip_output meo,
mth_equip_output_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds
WHERE med.equipment_pk_key = meo.equipment_fk_key
AND wds.shift_workday_pk_key = meo.shift_workday_fk_key
AND med.equipment_pk = stg.equipment_fk
AND wds.shift_workday_pk = stg.shift_workday_fk
AND meo.reading_time = stg.reading_time) dop
WHERE dop.reading_time = stg.reading_time
AND dop.equipment_pk = stg.equipment_fk
AND dop.shift_workday_pk = stg.shift_workday_fk );
INSERT INTO mth_equip_output( equipment_fk_key,
item_fk_key,
shift_workday_fk_key,
workorder_fk_key,
reading_time,
qty_completed,
qty_scrap,
qty_rejected,
qty_rework,
qty_uom,
qty_good,
qty_output,
system_fk_key,
recipe_version,
recipe_num,
segment_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,
hour_fk_key )
(SELECT med.equipment_pk_key ,
mid.item_pk_key ,
wds.shift_workday_pk_key ,
mps.workorder_pk_key ,
stg.reading_time ,
stg.qty_completed,
stg.qty_scrap,
stg.qty_rejected,
stg.qty_rework,
stg.qty_uom,
stg.qty_good,
stg.qty_output,
Nvl(mss.system_pk_key,v_unassigned_val) ,
stg.recipe_version ,
stg.recipe_num,
msf.segment_pk_key,
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,
mhd.hour_pk_key
FROM mth_equip_output_stg stg,
mth_equipments_d med,
mth_workday_shifts_d wds,
mth_items_d mid,
mth_production_segments_f msf,
mth_production_schedules_f mps,
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.ITEM_FK = mid.ITEM_PK (+)
AND stg.WORKORDER_FK = mps.WORKORDER_PK (+)
AND stg.SEGMENT_FK = msf.SEGMENT_PK (+)
AND stg.reading_time BETWEEN mhd.from_time AND mhd.to_time
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_SCRAP_REASON'
AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
AND stg.err_code IS NULL);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || 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)
(SELECT 2 reason_type,
med.equipment_pk_key,
stg.reading_time,
stg.reading_time,
stg.scrap_reason_code,
v_log_date,
v_log_date,
v_unassigned_val,
v_unassigned_val,
NULL,
NULL,
NULL,
stg.reading_time
FROM mth_equip_output_stg stg,
mth_equipments_d med
WHERE med.equipment_pk = stg.equipment_fk
AND med.status = 'ACTIVE'
AND stg.ERR_CODE IS NULL
AND stg.qty_scrap IS NOT 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_OUTPUT_STG;
UPDATE MTH_EQUIP_STATUS_SUMMARY
SET wo_item_count = null,
required_hours = null;
mth_util_pkg.log_msg('Number of rows updated in status summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE FROM MTH_EQUIP_OUTPUT_SUMMARY;
mth_util_pkg.log_msg('Number of rows deleted from output summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT
INTO
MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,
ITEM_FK_KEY,
WORKORDER_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
QTY_COMPLETED,
QTY_SCRAP,
QTY_REJECTED,
QTY_REWORK,
QTY_GOOD,
QTY_OUTPUT,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECIPE_NUM,
RECIPE_VERSION,
SEGMENT_FK_KEY,
STANDARD_RATE_1,
ITEM_COST,
RESOURCE_FK_KEY,
RESOURCE_COST )
( SELECT meo.equipment_fk_key,
meo.item_fk_key,
Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
meo.shift_workday_fk_key,
meo.hour_fk_key,
Sum(meo.qty_completed) qty_completed,
Sum(meo.qty_scrap) qty_scrap,
Sum(meo.qty_rejected ) qty_rejected,
Sum(meo.qty_rework) qty_rework,
Sum(meo.qty_completed) qty_good,
Sum(meo.qty_output) qty_output,
v_ua_val,
v_log_date,
v_log_date,
v_ua_val,
v_ua_val,
v_ua_val,
v_ua_val,
Nvl(meo.recipe_num,v_ua_val) recipe_num,
Nvl(meo.recipe_version,v_ua_val) recipe_version,
meo.segment_fk_key segment_fk_key,
Min(srf.standard_rate_1) standard_rate_1,
Min(mic.cost) item_cost,
Min(med.level9_level_key) level9_level_key,
Min(mrc.cost) resource_cost
FROM mth_item_cost_mv mic,
mth_resource_cost_mv mrc,
mth_workday_shifts_d msg,
mth_equipment_denorm_d med,
mth_equip_standard_rates_f srf,
mth_equip_output meo
WHERE med.equipment_hierarchy_key = -2
AND med.equipment_fk_key is not null
AND msg.from_date between med.equipment_effective_date
and nvl(med.equipment_expiration_date , msg.from_date)
AND nvl(med.level9_level_key, v_ua_val) = mrc.resource_fk_key (+)
AND med.equipment_fk_key = meo.equipment_fk_key
AND meo.shift_workday_fk_key = msg.shift_workday_pk_key
AND meo.item_fk_key = mic.item_fk_key (+)
AND meo.equipment_fk_key = srf.equipment_fk_key(+)
AND meo.item_fk_key = srf.item_fk_key(+)
AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
AND meo.last_update_date <= v_run_log_to_date
GROUP BY meo.equipment_fk_key,
meo.item_fk_key,
meo.workorder_fk_key ,
meo.shift_workday_fk_key,
meo.recipe_version ,
meo.recipe_num ,
meo.segment_fk_key,
meo.hour_fk_key );
mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
UPDATE
MTH_EQUIP_STATUS_SUMMARY mes
SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
mes.LAST_UPDATE_DATE,mes.LAST_UPDATE_SYSTEM_ID,
mes.LAST_UPDATE_LOGIN,mes.LAST_UPDATED_BY)=
(SELECT statrec.wo_item_count, statrec.required_time,
statrec.last_update_date,statrec.last_update_system_id,
statrec.last_update_login,statrec.last_updated_by
FROM (SELECT meos.equipment_fk_key,
meos.shift_workday_fk_key,
sum(case when nvl(meos.qty_output,0) = 0 then
nvl(meos.qty_completed,0) +
case when nvl((meos.qty_rejected),0) = 0 then
nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
else meos.qty_rejected
end
else meos.qty_output
end / meos.standard_rate_1) required_time,
count(*) wo_item_count,
meos.hour_fk_key,
v_log_date last_update_date,
v_ua_val last_update_system_id,
v_ua_val last_update_login,
v_ua_val last_updated_by
FROM MTH_EQUIP_OUTPUT_SUMMARY meos
WHERE meos.standard_rate_1 is NOT NULL
AND meos.standard_rate_1 <> 0
GROUP BY meos.equipment_fk_key,
meos.shift_workday_fk_key,
meos.hour_fk_key) statrec
WHERE 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 )
WHERE EXISTS (SELECT 1
FROM (SELECT meos.equipment_fk_key,
meos.shift_workday_fk_key,
sum(case when nvl(meos.qty_output,0) = 0 then
nvl(meos.qty_completed,0) +
case when nvl((meos.qty_rejected),0) = 0 then
nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
else meos.qty_rejected
end
else meos.qty_output
end / meos.standard_rate_1) required_time,
count(*) wo_item_count,
meos.hour_fk_key,
v_log_date last_update_date,
v_ua_val last_update_system_id,
v_ua_val last_update_login,
v_ua_val last_updated_by
FROM MTH_EQUIP_OUTPUT_SUMMARY meos
WHERE meos.standard_rate_1 is NOT NULL
AND meos.standard_rate_1 <> 0
GROUP BY meos.equipment_fk_key,
meos.shift_workday_fk_key,
meos.hour_fk_key) meos
WHERE mes.EQUIPMENT_FK_KEY = meos.EQUIPMENT_FK_KEY
AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
UPDATE
MTH_EQUIP_STATUS_SUMMARY mes
SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
mes.LAST_UPDATE_DATE,mes.LAST_UPDATE_SYSTEM_ID,
mes.LAST_UPDATE_LOGIN,mes.LAST_UPDATED_BY)=
(SELECT (nvl(mes.WO_ITEM_COUNT,0) + nvl(statrec.wo_item_count,0)), (nvl(mes.REQUIRED_HOURS,0) + nvl(statrec.required_time,0)),
statrec.last_update_date,statrec.last_update_system_id,
statrec.last_update_login,statrec.last_updated_by
FROM
(SELECT aggr.equipment_fk_key equipment_fk_key,
aggr.shift_workday_fk_key shift_workday_fk_key,
aggr.hour_fk_key hour_fk_key,
v_log_date last_update_date,
null,
v_ua_val last_update_system_id,
null,
v_ua_val last_update_login,
v_ua_val last_updated_by,
Sum((case when srf.standard_rate_1 is not null then
case when nvl(aggr.qty_output,0) = 0 then
nvl(aggr.qty_completed,0) +
case when nvl((aggr.qty_rejected),0) = 0 then
nvl(aggr.qty_rework,0) + nvl(aggr.qty_scrap,0)
else aggr.qty_rejected
end
else aggr.qty_output
end / srf.standard_rate_1
end) ) required_time,
Sum((CASE WHEN eos.equipment_fk_key IS NULL THEN 1 ELSE 0 END)) wo_item_count,
null,
null
FROM mth_equip_output_summary eos,
mth_equip_standard_rates_f srf,
(SELECT meo.equipment_fk_key,
meo.item_fk_key,
Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
Sum(meo.qty_completed) qty_completed,
Sum(meo.qty_scrap) qty_scrap,
Sum(meo.qty_rejected) qty_rejected,
Sum(meo.qty_rework) qty_rework,
Sum(meo.qty_output) qty_output,
meo.shift_workday_fk_key,
Nvl(meo.recipe_version,v_ua_val) recipe_version,
Nvl(meo.recipe_num,v_ua_val) recipe_num,
meo.segment_fk_key segment_fk_key,
meo.hour_fk_key
FROM MTH_EQUIP_OUTPUT meo
WHERE meo.last_update_date > v_run_log_from_date AND meo.last_update_date <= v_run_log_to_date
GROUP BY meo.equipment_fk_key,
meo.item_fk_key,
meo.workorder_fk_key ,
meo.shift_workday_fk_key,
meo.recipe_version ,
meo.recipe_num ,
meo.segment_fk_key,
meo.hour_fk_key) aggr
WHERE aggr.equipment_fk_key = eos.equipment_fk_key(+)
AND aggr.item_fk_key = eos.item_fk_key(+)
AND aggr.workorder_fk_key = eos.workorder_fk_key(+)
AND aggr.shift_workday_fk_key = eos.shift_workday_fk_key(+)
AND aggr.recipe_version = eos.recipe_version(+)
AND aggr.recipe_num = eos.recipe_num(+)
AND aggr.segment_fk_key = eos.segment_fk_key(+)
AND aggr.hour_fk_key = eos.hour_fk_key(+)
AND aggr.equipment_fk_key = srf.equipment_fk_key(+)
AND aggr.item_fk_key = srf.item_fk_key(+)
AND aggr.shift_workday_fk_key = srf.shift_workday_fk_key(+)
AND srf.standard_rate_1 IS NOT NULL
AND srf.standard_rate_1 <> 0
GROUP BY aggr.equipment_fk_key,
aggr.shift_workday_fk_key,
aggr.hour_fk_key) statrec
WHERE 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 )
WHERE EXISTS (SELECT 1
FROM (
SELECT aggr.equipment_fk_key equipment_fk_key,
aggr.shift_workday_fk_key shift_workday_fk_key,
aggr.hour_fk_key hour_fk_key,
v_log_date last_update_date,
null,
v_ua_val last_update_system_id,
null,
v_ua_val last_update_login,
v_ua_val last_updated_by,
Sum((case when srf.standard_rate_1 is not null then
case when nvl(aggr.qty_output,0) = 0 then
nvl(aggr.qty_completed,0) +
case when nvl((aggr.qty_rejected),0) = 0 then
nvl(aggr.qty_rework,0) + nvl(aggr.qty_scrap,0)
else aggr.qty_rejected
end
else aggr.qty_output
end / srf.standard_rate_1
end) ) required_time,
Sum((case when eos.equipment_fk_key is null then 1 else 0 end)) wo_item_count,
null,
null
FROM mth_equip_output_summary eos,
mth_equip_standard_rates_f srf,
(SELECT meo.equipment_fk_key,
meo.item_fk_key,
Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
Sum(meo.qty_completed) qty_completed,
Sum(meo.qty_scrap) qty_scrap,
Sum(meo.qty_rejected) qty_rejected,
Sum(meo.qty_rework) qty_rework,
Sum(meo.qty_output) qty_output,
meo.shift_workday_fk_key,
Nvl(meo.recipe_version,v_ua_val) recipe_version,
Nvl(meo.recipe_num,v_ua_val) recipe_num,
meo.segment_fk_key segment_fk_key,
meo.hour_fk_key
FROM MTH_EQUIP_OUTPUT meo
WHERE meo.last_update_date > v_run_log_from_date AND meo.last_update_date <= v_run_log_to_date
GROUP BY meo.equipment_fk_key,
meo.item_fk_key,
meo.workorder_fk_key ,
meo.shift_workday_fk_key,
meo.recipe_version ,
meo.recipe_num ,
meo.segment_fk_key,
meo.hour_fk_key) aggr
WHERE aggr.equipment_fk_key = eos.equipment_fk_key(+)
AND aggr.item_fk_key = eos.item_fk_key(+)
AND aggr.workorder_fk_key = eos.workorder_fk_key(+)
AND aggr.shift_workday_fk_key = eos.shift_workday_fk_key(+)
AND aggr.recipe_version = eos.recipe_version(+)
AND aggr.recipe_num = eos.recipe_num(+)
AND aggr.segment_fk_key = eos.segment_fk_key(+)
AND aggr.hour_fk_key = eos.hour_fk_key(+)
AND aggr.equipment_fk_key = srf.equipment_fk_key(+)
AND aggr.item_fk_key = srf.item_fk_key(+)
AND aggr.shift_workday_fk_key = srf.shift_workday_fk_key(+)
AND srf.standard_rate_1 IS NOT NULL
AND srf.standard_rate_1 <> 0
GROUP BY aggr.equipment_fk_key,
aggr.shift_workday_fk_key,
aggr.hour_fk_key) meos
WHERE mes.EQUIPMENT_FK_KEY = meos.EQUIPMENT_FK_KEY
AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
( SELECT meo.equipment_fk_key sq_equipment_fk_key,
meo.item_fk_key sq_item_fk_key,
Nvl(meo.workorder_fk_key,v_ua_val) sq_workorder_fk_key,
meo.shift_workday_fk_key sq_shift_workday_fk_key,
meo.hour_fk_key sq_hour_fk_key,
Sum(meo.qty_completed) sq_qty_completed,
Sum(meo.qty_scrap) sq_qty_scrap,
Sum(meo.qty_rejected ) sq_qty_rejected,
Sum(meo.qty_rework) sq_qty_rework,
Sum(meo.qty_completed) sq_qty_good,
Sum(meo.qty_output) sq_qty_output,
v_ua_val sq_system_fk_key,
v_log_date sq_log_date,
Nvl(meo.recipe_num,v_ua_val) sq_recipe_num,
Nvl(meo.recipe_version,v_ua_val) sq_recipe_version,
meo.segment_fk_key segment,
Min(srf.standard_rate_1) sq_standard_rate_1,
Min(mic.cost) sq_item_cost,
Min(med.level9_level_key) sq_level9_level_key,
Min(mrc.cost) sq_resource_cost
FROM mth_item_cost_mv mic,
mth_resource_cost_mv mrc,
mth_workday_shifts_d msg,
mth_equipment_denorm_d med,
mth_equip_standard_rates_f srf,
mth_equip_output meo
WHERE med.equipment_hierarchy_key = -2
AND med.equipment_fk_key is not null
AND msg.from_date between med.equipment_effective_date
and nvl(med.equipment_expiration_date , msg.from_date)
AND nvl(med.level9_level_key, v_ua_val) = mrc.resource_fk_key (+)
AND med.equipment_fk_key = meo.equipment_fk_key
AND meo.shift_workday_fk_key = msg.shift_workday_pk_key
AND meo.item_fk_key = mic.item_fk_key (+)
AND meo.equipment_fk_key = srf.equipment_fk_key(+)
AND meo.item_fk_key = srf.item_fk_key(+)
AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
AND meo.hour_fk_key IN ( SELECT hour_fk_key
FROM mth_equip_output
WHERE last_update_date > v_run_log_from_date
AND last_update_date <= v_run_log_to_date )
GROUP BY meo.equipment_fk_key,
meo.item_fk_key,
meo.workorder_fk_key ,
meo.shift_workday_fk_key,
meo.recipe_version ,
meo.recipe_num ,
meo.segment_fk_key,
meo.hour_fk_key ) subquery
ON
(
mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
AND mth_equip_output_summary.segment_fk_key = subquery.segment
)
WHEN MATCHED THEN
UPDATE
SET
qty_completed = nvl(subquery.sq_qty_completed,0),
qty_scrap = nvl(subquery.sq_qty_scrap,0),
qty_rejected = nvl(subquery.sq_qty_rejected,0),
qty_rework = nvl(subquery.sq_qty_rework,0),
qty_good = nvl(subquery.sq_qty_good,0),
qty_output = nvl(subquery.sq_qty_output,0),
system_fk_key = subquery.sq_system_fk_key,
last_update_date = subquery.sq_log_date,
last_update_system_id = subquery.sq_system_fk_key,
last_update_login = subquery.sq_system_fk_key,
last_updated_by = subquery.sq_system_fk_key,
standard_rate_1 = subquery.sq_standard_rate_1,
item_cost = subquery.sq_item_cost,
resource_fk_key = subquery.sq_level9_level_key,
resource_cost = subquery.sq_resource_cost
WHEN NOT MATCHED THEN
INSERT
(mth_equip_output_summary.equipment_fk_key,
mth_equip_output_summary.item_fk_key,
mth_equip_output_summary.workorder_fk_key,
mth_equip_output_summary.shift_workday_fk_key,
mth_equip_output_summary.hour_fk_key,
mth_equip_output_summary.qty_completed,
mth_equip_output_summary.qty_scrap,
mth_equip_output_summary.qty_rejected,
mth_equip_output_summary.qty_rework,
mth_equip_output_summary.qty_good,
mth_equip_output_summary.qty_output,
mth_equip_output_summary.system_fk_key,
mth_equip_output_summary.creation_date,
mth_equip_output_summary.last_update_date,
mth_equip_output_summary.creation_system_id,
mth_equip_output_summary.last_update_system_id,
mth_equip_output_summary.last_update_login,
mth_equip_output_summary.last_updated_by,
mth_equip_output_summary.recipe_num,
mth_equip_output_summary.recipe_version,
mth_equip_output_summary.segment_fk_key,
mth_equip_output_summary.standard_rate_1,
mth_equip_output_summary.item_cost,
mth_equip_output_summary.resource_fk_key,
mth_equip_output_summary.resource_cost)
VALUES
(subquery.sq_equipment_fk_key,
subquery.sq_item_fk_key,
subquery.sq_workorder_fk_key,
subquery.sq_shift_workday_fk_key,
subquery.sq_hour_fk_key,
subquery.sq_qty_completed,
subquery.sq_qty_scrap,
subquery.sq_qty_rejected,
subquery.sq_qty_rework,
subquery.sq_qty_good,
subquery.sq_qty_output,
subquery.sq_system_fk_key,
subquery.sq_log_date,
subquery.sq_log_date,
subquery.sq_system_fk_key,
subquery.sq_system_fk_key,
subquery.sq_system_fk_key,
subquery.sq_system_fk_key,
subquery.sq_recipe_num,
subquery.sq_recipe_version,
subquery.segment,
subquery.sq_standard_rate_1,
subquery.sq_item_cost,
subquery.sq_level9_level_key,
subquery.sq_resource_cost);
SELECT Min(from_time)
FROM mth_hour_d
WHERE p_recalc_from_date BETWEEN from_time AND to_time;
SELECT Max(to_time)
FROM mth_hour_d
WHERE v_recalc_to_date BETWEEN from_time AND to_time;
SELECT Max(reading_time)
FROM MTH_EQUIP_OUTPUT;
UPDATE MTH_EQUIP_STATUS_SUMMARY
SET wo_item_count = null,
required_hours = null
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);
UPDATE MTH_EQUIP_STATUS_SUMMARY
SET wo_item_count = null,
required_hours = null
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);
DELETE
FROM MTH_EQUIP_OUTPUT_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
FROM MTH_EQUIP_OUTPUT_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_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
INSERT
INTO
MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,
ITEM_FK_KEY,
WORKORDER_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
QTY_COMPLETED,
QTY_SCRAP,
QTY_REJECTED,
QTY_REWORK,
QTY_GOOD,
QTY_OUTPUT,
SYSTEM_FK_KEY,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECIPE_NUM,
RECIPE_VERSION,
SEGMENT_FK_KEY,
STANDARD_RATE_1,
ITEM_COST,
RESOURCE_FK_KEY,
RESOURCE_COST )
( SELECT meo.equipment_fk_key,
meo.item_fk_key,
Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,
meo.shift_workday_fk_key,
meo.hour_fk_key,
Sum(meo.qty_completed) qty_completed,
Sum(meo.qty_scrap) qty_scrap,
Sum(meo.qty_rejected ) qty_rejected,
Sum(meo.qty_rework) qty_rework,
Sum(meo.qty_completed) qty_good,
Sum(meo.qty_output) qty_output,
v_ua_val,
v_log_date,
v_log_date,
v_ua_val,
v_ua_val,
v_ua_val,
v_ua_val,
Nvl(meo.recipe_num,v_ua_val) recipe_num,
Nvl(meo.recipe_version,v_ua_val) recipe_version,
meo.segment_fk_key segment_fk_key,
Min(srf.standard_rate_1) standard_rate_1,
Min(mic.cost) item_cost,
Min(med.level9_level_key) level9_level_key,
Min(mrc.cost) resource_cost
FROM mth_item_cost_mv mic,
mth_resource_cost_mv mrc,
mth_workday_shifts_d msg,
mth_equipment_denorm_d med,
mth_equip_standard_rates_f srf,
mth_equip_output meo
WHERE med.equipment_hierarchy_key = -2
AND med.equipment_fk_key is not null
AND msg.from_date between med.equipment_effective_date
and nvl(med.equipment_expiration_date , msg.from_date)
AND nvl(med.level9_level_key, v_ua_val) = mrc.resource_fk_key (+)
AND med.equipment_fk_key = meo.equipment_fk_key
AND meo.shift_workday_fk_key = msg.shift_workday_pk_key
AND meo.item_fk_key = mic.item_fk_key (+)
AND meo.equipment_fk_key = srf.equipment_fk_key(+)
AND meo.item_fk_key = srf.item_fk_key(+)
AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
AND meo.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 meo.equipment_fk_key = nvl(p_recalc_equip_key,meo.equipment_fk_key)
AND meo.equipment_fk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
GROUP BY meo.equipment_fk_key,
meo.item_fk_key,
meo.workorder_fk_key ,
meo.shift_workday_fk_key,
meo.recipe_version ,
meo.recipe_num ,
meo.segment_fk_key,
meo.hour_fk_key );
mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
UPDATE
MTH_EQUIP_STATUS_SUMMARY mes
SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
mes.LAST_UPDATE_DATE,mes.LAST_UPDATE_SYSTEM_ID,
mes.LAST_UPDATE_LOGIN,mes.LAST_UPDATED_BY)=
(SELECT statrec.wo_item_count, statrec.required_time,
statrec.last_update_date,statrec.last_update_system_id,
statrec.last_update_login,statrec.last_updated_by
FROM (SELECT meos.equipment_fk_key,
meos.shift_workday_fk_key,
sum(case when nvl(meos.qty_output,0) = 0 then
nvl(meos.qty_completed,0) +
case when nvl((meos.qty_rejected),0) = 0 then
nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
else meos.qty_rejected
end
else meos.qty_output
end / meos.standard_rate_1) required_time,
count(*) wo_item_count,
meos.hour_fk_key ,
v_log_date last_update_date,
v_ua_val last_update_system_id,
v_ua_val last_update_login,
v_ua_val last_updated_by
FROM MTH_EQUIP_OUTPUT_SUMMARY meos
WHERE meos.standard_rate_1 is NOT NULL
AND meos.standard_rate_1 <> 0
AND meos.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 meos.equipment_fk_key = nvl(p_recalc_equip_key,meos.equipment_fk_key)
AND meos.equipment_fk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
GROUP BY meos.equipment_fk_key,
meos.shift_workday_fk_key,
meos.hour_fk_key) statrec
WHERE 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 )
WHERE EXISTS (SELECT 1
FROM (SELECT meos.equipment_fk_key,
meos.shift_workday_fk_key,
sum(case when nvl(meos.qty_output,0) = 0 then
nvl(meos.qty_completed,0) +
case when nvl((meos.qty_rejected),0) = 0 then
nvl(meos.qty_rework,0) + nvl(meos.qty_scrap,0)
else meos.qty_rejected
end
else meos.qty_output
end / meos.standard_rate_1) required_time,
count(*) wo_item_count,
meos.hour_fk_key ,
v_log_date last_update_date,
v_ua_val last_update_system_id,
v_ua_val last_update_login,
v_ua_val last_updated_by
FROM MTH_EQUIP_OUTPUT_SUMMARY meos
WHERE meos.standard_rate_1 is NOT NULL
AND meos.standard_rate_1 <> 0
AND meos.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 meos.equipment_fk_key = nvl(p_recalc_equip_key,meos.equipment_fk_key)
AND meos.equipment_fk_key IN (SELECT equipment_pk_key
FROM mth_equipments_d
WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
GROUP BY meos.equipment_fk_key,
meos.shift_workday_fk_key,
meos.hour_fk_key) meos
WHERE mes.EQUIPMENT_FK_KEY = meos.EQUIPMENT_FK_KEY
AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);