create or replace FUNCTION l_stats_rollup3_proc()
RETURNS VOID

-- ==================================================================
/*
  Purpose:
    Loging wrap procedure of stats_rollup3_proc
*/
-- ==================================================================



   AS $procedure$
    /*variables declaration*/
   DECLARE
   v_err_log  CHAR(3);
   v_errnum  VARCHAR(10);    -- catched exception No
   v_errmsg  VARCHAR(2048);  -- catched exception Msg
   v_log_rtn INTEGER;

BEGIN
   v_errnum := '00000';

   SELECT COALESCE(value,'OFF') into v_err_log
   FROM vpx_parameter
   WHERE name = 'DBProc.Log.Debug.Info';

   IF v_err_log = 'ON' THEN
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('STATS_ROLLUP3_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
   END IF;


   SELECT CAST(VALUE AS INTEGER) INTO v_log_rtn
   FROM vpx_parameter
   WHERE NAME = 'DBProc.Log.Topn.Purge';

   DELETE FROM vpx_proc_log
   WHERE event_dt < timezone('UTC', clock_timestamp()) - (v_log_rtn || ' days')::interval
   AND PROC_NAME like '%STATS%';

   BEGIN
      PERFORM STATS_ROLLUP3_PROC();
   EXCEPTION
      WHEN OTHERS THEN
         v_errnum := sqlstate;
         v_errmsg := SQLERRM;
         IF v_err_log = 'ON' THEN
             INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
               VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
         END IF;
   END;

   IF v_err_log = 'ON' THEN
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('STATS_ROLLUP3_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
   END IF;

END; $procedure$
LANGUAGE plpgsql;

