prompt *********************** CREATE PROCEDURE SP_TOPAZ_STATUS_UPDATE *********************** 

create or replace PROCEDURE SP_TOPAZ_STATUS_UPDATE
(RETCODE OUT NUMBER, RETMSG OUT VARCHAR2)
AS

NO_ACTIVE_PROC_RUN EXCEPTION;

V_TPZTXNID IMPACTMGR.TPZ_TRANSACTION_TBL.TpzTransactionID%TYPE;
V_RECEIVETIME TIMESTAMP;
V_HOUR NUMBER;
V_MINUTE NUMBER;
V_TOTAL_MIN NUMBER;
V_TXN_STATUS VARCHAR2(25) ;
V_CURR_STATUS_ID VARCHAR2(100);

CURSOR CURS_TPZTXNID 
IS
Select TPZTRANSACTIONID from TPZ_TRANSACTION_TBL;

BEGIN
  
    OPEN CURS_TPZTXNID;
    LOOP
    FETCH CURS_TPZTXNID INTO V_TPZTXNID;
    EXIT WHEN CURS_TPZTXNID%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(V_TPZTXNID);
    BEGIN
       
       SELECT RECEIVETIME, CURRSTATUSID INTO V_RECEIVETIME, V_CURR_STATUS_ID from tpz_current_status_tbl 
       WHERE TRIGGERTIME = (select max(TRIGGERTIME) from tpz_current_status_tbl
                            WHERE tpztransactionid = V_TPZTXNID and triggertime IS NOT NULL)
       and tpztransactionid = V_TPZTXNID;
        
        -- DBMS_OUTPUT.PUT_LINE(V_RECEIVETIME||':'||V_CURR_STATUS_ID);
    EXCEPTION
             WHEN NO_DATA_FOUND THEN
                RETCODE := 100;
                RETMSG := SUBSTR(SQLERRM, 1, 200);
             WHEN OTHERS THEN
                RETCODE := SQLCODE;
                RETMSG := SUBSTR(SQLERRM, 1, 200);
          
      END;
    
     V_HOUR := extract( HOUR from TO_CHAR(systimestamp,'DD-MON-RR HH.MI.SS.FF AM')-(V_RECEIVETIME));
     V_MINUTE := extract( MINUTE from TO_CHAR(systimestamp,'DD-MON-RR HH.MI.SS.FF AM')-(V_RECEIVETIME));
     
    -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(systimestamp,'DD-MON-RR HH.MI.SS.FF AM')-(V_RECEIVETIME) ||':'|| V_HOUR||':'||V_MINUTE);
     
     V_TOTAL_MIN := ( V_HOUR * 60 ) + V_MINUTE;
          
     IF( V_TOTAL_MIN > 15) THEN
     
       UPDATE  TPZ_TRANSACTION_STATUS_TBL
       SET STATUS = 'GREEN'
       WHERE TPZTRANSACTIONID = V_TPZTXNID; 
      -- DBMS_OUTPUT.PUT_LINE(V_TPZTXNID||':'||V_TOTAL_MIN);
     END IF;
             
    END LOOP;
    CLOSE CURS_TPZTXNID;

RETCODE := 0;
RETMSG := 'SUCCESS';

COMMIT;

EXCEPTION
       WHEN NO_ACTIVE_PROC_RUN THEN
            RETCODE := -20000;
            RETMSG := 'NO_ACTIVE_PROC_RUN';
       WHEN NO_DATA_FOUND THEN
                    RETCODE := 100;
                    RETMSG := SUBSTR(SQLERRM, 1, 200);
       WHEN OTHERS THEN
           RETCODE := SQLCODE;
           RETMSG := SUBSTR(SQLERRM, 1, 200);
  
END SP_TOPAZ_STATUS_UPDATE;

/

commit;