Correcting LP and Register Data Processed Delivered With Wrong Multiplier (7.2)

Every now and then our AMI system gets the wrong multiplier set for a module.  Since it delivers the data fully multiplied to EnergyIP, the data is stored incorrectly.  Fixing this can be daunting, so I put together a little script to help.  This script can easily be modified to divide the existing values should the multiplier have been set to high.  In our case its usually that the multiplier was set too low.

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;