USE AT YOUR OWN RISK
/* This script takes arguments and will MULTIPLY reading values to correct them */
/* use with caution */
/* update 6/7/2013 */
/* This script requires a function call to convert UTC to our local time, mountain time */
/* the function */
CREATE OR REPLACE FUNCTION AMFC.to_mountain_time (DATE_INPUT IN DATE)
RETURN DATE DETERMINISTIC
IS
MT_DATE DATE;
/******************************************************************************
NAME: to_mountain_time
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 9/7/2012 asanchez 1. Created this function.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: utc_to_mountain_time
Sysdate: 9/7/2012
Date and Time: 9/7/2012, 3:39:25 PM, and 9/7/2012 3:39:25 PM
Username: asanchez (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
MT_DATE :=
TO_DATE (
TO_CHAR (
FROM_TZ (
TO_TIMESTAMP (TO_CHAR (DATE_INPUT, 'MM/DD/YYYY HH:MI:SS PM'),
'MM/DD/YYYY HH:MI:SS PM'),
'UTC')
AT TIME ZONE 'US/Mountain',
'MM/DD/YYYY HH:MI:SS PM'),
'MM/DD/YYYY HH:MI:SS PM');
RETURN MT_DATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END to_mountain_time;
/
/* The script */
VAR before_date VARCHAR2(50);
VAR register_channel_id VARCHAR2(50);
VAR lp_channel_id VARCHAR2(50);
VAR multiplier_adjustment NUMBER;
EXEC :before_date := '05/01/2013 23:59:00';
EXEC :register_channel_id := '1-4A5QB';
EXEC :lp_channel_id := '1-4A5Q8';
EXEC :multiplier_adjustment := 10;
/* registers */
SELECT local_read_time, cum_read rr_before
FROM emapp.register_reads
WHERE channel_id = :register_channel_id
AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_read_time DESC;
UPDATE emapp.register_reads
SET cum_read = cum_read * :multiplier_adjustment
WHERE channel_id = :register_channel_id
AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');
SELECT local_read_time, cum_read rr_after
FROM emapp.register_reads
WHERE channel_id = :register_channel_id
AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_read_time DESC;
/* intervals */
SELECT local_interval_time, lp_value lp_before
FROM emapp.lp_intervals
WHERE channel_id = :lp_channel_id
AND local_interval_time <=
TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_interval_time DESC;
UPDATE emapp.lp_intervals
SET lp_value = lp_value * :multiplier_adjustment
WHERE channel_id = :lp_channel_id
AND local_interval_time <=
TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');
SELECT local_interval_time, lp_value lp_after
FROM emapp.lp_intervals
WHERE channel_id = :lp_channel_id
AND local_interval_time <=
TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_interval_time DESC;
/* lp batch update */
SELECT end_read lpb_before
FROM emapp.lp_batch
WHERE channel_id = :lp_channel_id
AND amfc.to_mountain_Time (utc_end_time) <=
TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY utc_end_time DESC;
UPDATE emapp.lp_batch
SET end_read = end_read * :multiplier_adjustment
WHERE channel_id = :lp_channel_id
AND amfc.to_mountain_Time (utc_end_time) <=
TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');
SELECT end_read lpb_after
FROM emapp.lp_batch
WHERE channel_id = :lp_channel_id
AND amfc.to_mountain_Time (utc_end_time) <=
TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY utc_end_time DESC;
