create or replace FUNCTION l_stats_rollup3_proc()
 RETURNS VOID
 
 
 
 
 
 
 
    AS $procedure$
     
    DECLARE
    v_err_log  CHAR(3);
    v_errnum  VARCHAR(10);    
    v_errmsg  VARCHAR(2048);  
    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;
 
