CREATE OR REPLACE FUNCTION rule_topn1_proc()
 RETURNS VOID
 
 
 
 
 
 
 
 
 
 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
    v_job_id             := 1;
    v_time_level         := 1;
    v_recovery_period    := 30;
 
 
    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_TOPN1_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
        if v_err_log = 'ON' then
           INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
               VALUES ('RULE_TOPN1_PROC','EXIT', TIMEZONE('UTC',clock_timestamp()), 0, 'The job is running.');
        end if;
        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_time1;
    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_TOPN1_PROC','EXIT', TIMEZONE('UTC',clock_timestamp()), 0, 'vpx_sample_time1 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_TOPN1_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
    end if;
 
    BEGIN
       PERFORM calc_topn1_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_TOPN1_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_TOPN1_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_TOPN1_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
    end if;
 
 
    BEGIN
       PERFORM clear_topn1_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_TOPN1_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_TOPN1_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_TOPN1_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_TOPN1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, 'Error:' || SUBSTR(cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, ''), 1, 1000));
       end if;
 END; $procedure$
 LANGUAGE plpgsql;