[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM MTH_TAG_REASON_READINGS;
mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_REASON_READINGS - ' || 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,
reading_time,
hour_fk_key)
(SELECT Decode(mtr.mth_entity,8,1,17,3) reason_type,
mtr.equipment_fk_key,
mes.from_date,
mes.To_Date,
mtr.tag_data reason_code,
SYSDATE,
SYSDATE,
mtr.reading_time,
mes.hour_fk_key
FROM (SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings mtr
WHERE processed_flag = 0
UNION
SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings_err mte
WHERE mte.processed_flag = 0) mtr,
mth_equip_statuses mes
WHERE mtr.reading_time = mes.reading_time
AND mtr.equipment_fk_key = mes.equipment_fk_key
AND mes.last_update_date <= v_log_date
AND mtr.mth_entity IN (8,17)
UNION
SELECT Decode(mtr.mth_entity,13,2) reason_type,
mtr.equipment_fk_key,
meo.reading_time from_date,
meo.reading_time To_Date,
mtr.tag_data reason_code,
SYSDATE,
SYSDATE,
mtr.reading_time,
meo.hour_fk_key
FROM (SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings mtr
WHERE processed_flag = 0
UNION
SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings_err mte
WHERE mte.processed_flag = 0) mtr,
mth_equip_output meo
WHERE mtr.reading_time = meo.reading_time
AND mtr.equipment_fk_key = meo.equipment_fk_key
AND meo.last_update_date <= v_log_date
AND mtr.mth_entity = 13);
mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE mth_tag_readings mtr
SET mtr.last_update_date = SYSDATE,
mtr.processed_flag = 1
WHERE EXISTS
( SELECT 1
FROM mth_tag_reason_readings mtrr
WHERE mtrr.reading_time = mtr.reading_time
AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
AND mtrr.equipment_fk_key = mtr.equipment_fk_key
AND mtr.processed_flag = 0 ) ;
UPDATE mth_tag_readings_err mtr
SET mtr.processed_flag = 1
WHERE EXISTS
( SELECT 1
FROM mth_tag_reason_readings mtrr
WHERE mtrr.reading_time = mtr.reading_time
AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
AND mtrr.equipment_fk_key = mtr.equipment_fk_key
AND mtr.processed_flag = 0 ) ;
mth_util_pkg.log_msg('No. of rows updated in MTH_TAG_READINGS : '||SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
SELECT Decode(mtr.mth_entity,8,1,17,3) reason_type,
mtr.equipment_fk_key,
mes.from_date,
mes.To_Date,
mtr.tag_data reason_code,
SYSDATE creation_date,
SYSDATE last_update_date,
mtr.reading_time,
mes.hour_fk_key
FROM (SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings
WHERE PROCESSED_FLAG = 0
AND mth_entity IN (8,17)
UNION
SELECT Decode(reason_type,1,8),
equipment_fk_key,
reason_code,
reading_time
FROM mth_tag_reason_readings
WHERE To_Date IS NULL
UNION
SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings_err
WHERE processed_flag = 0
AND mth_entity IN (8,17)) mtr,
mth_equip_statuses mes
WHERE mtr.reading_time = mes.reading_time
AND mtr.equipment_fk_key = mes.equipment_fk_key
AND mes.last_update_date BETWEEN v_run_log_from_date AND v_run_log_to_date
AND mtr.mth_entity IN (8,17)
UNION
SELECT Decode(mtr.mth_entity,13,2) reason_type,
mtr.equipment_fk_key,
meo.reading_time from_date,
meo.reading_time To_Date,
mtr.tag_data reason_code,
SYSDATE creation_date,
SYSDATE last_update_date,
mtr.reading_time,
meo.hour_fk_key
FROM (SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings mtr
WHERE PROCESSED_FLAG = 0
UNION
SELECT mth_entity,
equipment_fk_key,
tag_data,
reading_time
FROM mth_tag_readings_err mte
WHERE mte.processed_flag = 0
) mtr,
mth_equip_output meo
WHERE mtr.reading_time = meo.reading_time
AND mtr.equipment_fk_key = meo.equipment_fk_key
AND meo.last_update_date BETWEEN v_run_log_from_date AND v_run_log_to_date
AND mtr.mth_entity = 13)mtr
ON ( mtrr.equipment_fk_key = mtr.equipment_fk_key
AND mtrr.from_date = mtr.from_date
AND mtrr.reading_time = mtr.reading_time
AND mtrr.reason_type = mtr.reason_type )
WHEN MATCHED THEN
UPDATE SET
mtrr.To_Date = mtr.To_Date,
mtrr.last_update_date = mtr.last_update_date
WHEN NOT MATCHED THEN
INSERT
(mtrr.reason_type,
mtrr.equipment_fk_key,
mtrr.from_date,
mtrr.To_Date,
mtrr.reason_code,
mtrr.creation_date,
mtrr.last_update_date,
mtrr.reading_time,
mtrr.hour_fk_key)
VALUES
(mtr.reason_type,
mtr.equipment_fk_key,
mtr.from_date,
mtr.To_Date,
mtr.reason_code,
mtr.creation_date,
mtr.last_update_date,
mtr.reading_time,
mtr.hour_fk_key
);
UPDATE mth_tag_readings mtr
SET mtr.last_update_date = SYSDATE,
mtr.processed_flag = 1
WHERE EXISTS
( SELECT 1
FROM mth_tag_reason_readings mtrr
WHERE mtrr.reading_time = mtr.reading_time
AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
AND mtrr.equipment_fk_key = mtr.equipment_fk_key
AND mtr.processed_flag = 0 ) ;
UPDATE mth_tag_readings_err mtr
SET mtr.processed_flag = 1
WHERE EXISTS
( SELECT 1
FROM mth_tag_reason_readings mtrr
WHERE mtrr.reading_time = mtr.reading_time
AND Decode(mtrr.reason_type,1,8,2,13,3,17) = mtr.mth_entity
AND mtrr.equipment_fk_key = mtr.equipment_fk_key
AND mtr.processed_flag = 0 ) ;
mth_util_pkg.log_msg('No. of rows updated in mth_tag_readings : '||SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);