create or replace FUNCTION l_stats_rollup1_proc()
RETURNS VOID
-- ==================================================================
/*
  Purpose:
    Loging wrap procedure of stats_rollup1_proc
*/
-- ==================================================================

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

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_ROLLUP1_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
   end if;


   BEGIN
      PERFORM STATS_ROLLUP1_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_ROLLUP1_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_ROLLUP1_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
   end if;


END; $procedure$
LANGUAGE plpgsql;
