CREATE OR REPLACE FUNCTION rule_topn2_proc()
 RETURNS VOID
 LANGUAGE plpgsql
 VOLATILE
 
 
 
 
 
 
 
 
 AS $procedure$
     
    DECLARE
    v_err_log     char(3);
    v_err         char(1) := 'N';
    v_errmsg      vpx_proc_log.description%type;
 
    v_status      vpx_job_log.status%type;
    v_recovery_period  smallint;
    v_last_run    vpx_job_log.last_run%type;
    v_last_stat   vpx_sample_time1.sample_time%type;
    v_job_id      int;
    v_log_rtn     int;
    v_time_level       smallint;
 BEGIN
 
     BEGIN
        v_job_id             := 2;
        v_time_level         := 2;
        v_recovery_period    := 90;
 
        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 ('RULE_TOPN2_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
        end if;
 
 
        
        select status into v_status
        from vpx_job_log
        where job_id = v_job_id;
 
        
        if v_status = 'RUNNING' then
           RETURN;
        end if;
 
 
         
        SELECT SAMPLE_TIME into v_last_run
          FROM VPX_TOPN_STATE
         WHERE TIME_LEVEL = v_time_level;
 
 
        
        SELECT max(sample_time) - (v_recovery_period || ' min')::interval into v_last_stat
        FROM vpx_sample_time2;
        IF v_last_stat IS NULL THEN
           if v_err_log = 'ON' then
               INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                  VALUES ('RULE_TOPN2_PROC','EXIT', TIMEZONE('UTC',clock_timestamp()), 0, 'vpx_sample_time2 is empty.');
           end if;
           return;
        END IF;
 
 
        
        v_last_run := coalesce(v_last_run,v_last_stat -(v_recovery_period || ' min')::interval);
 
 
         
        IF v_last_run <= v_last_stat - (v_recovery_period || ' min')::interval then
           v_last_run := v_last_stat - (v_recovery_period || ' min')::interval;
        END IF;
 
 
       
        UPDATE vpx_job_log
        SET status = 'RUNNING'
        WHERE job_id = v_job_id;
 
        if v_err_log = 'ON' then
            INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                 VALUES ('CALC_TOPN2_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
        end if;
 
        BEGIN
           PERFORM calc_topn2_proc(v_last_run,v_last_stat);
           EXCEPTION
           WHEN OTHERS THEN
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              v_err := 'Y';
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                    VALUES ('CALC_TOPN2_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 ('CALC_TOPN2_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
        end if;
 
        
        UPDATE vpx_job_log
        SET status = 'WAITING',last_run = COALESCE(v_last_stat,v_last_run)
        WHERE job_id = v_job_id;
 
        v_err := 'N';
        
         
 
 
         if v_err_log = 'ON' then
            INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                 VALUES ('CLEAR_TOPN2_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
        end if;
 
        BEGIN
           PERFORM clear_topn2_proc();
           EXCEPTION
           WHEN OTHERS THEN
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              v_err := 'Y';
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                    VALUES ('CLEAR_TOPN2_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 ('CLEAR_TOPN2_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
        end if;
 
        
        if v_err_log = 'ON' then
            INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                 VALUES ('RULE_TOPN2_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
        end if;
 
     EXCEPTION
        WHEN OTHERS THEN
           UPDATE vpx_job_log
           SET status = 'WAITING'
           WHERE job_id = v_job_id;
           
           if v_err_log = 'ON' then
               INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                 VALUES ('RULE_TOPN2_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, 'Error:' || SUBSTR(cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, ''), 1, 1000));
           end if;
           return;
     END;
 
 END;
 $procedure$
