Invoice Aging Query

CREATE OR REPLACE PROCEDURE ALPHA.MAKE_INV_AGING(ASON DATE) IS

RET1 VARCHAR2(5000);

RET2 VARCHAR2(5000);

DUE NUMBER;

DDAYS NUMBER;

DAYS NUMBER;

OPN NUMBER;

TRN NUMBER;

BALANCE NUMBER;

BEGIN

DELETE FROM TBL_INV_AGING;

COMMIT;

FOR P IN (SELECT LACT_CODE,DUE_DAYS FROM LASTACCOUNT WHERE SACT_CODE IN ('02-12-56-000','02-12-57-000'))--'02-12-57-025')--

LOOP

SELECT ROUND(NVL(SUM(AMOUNT),0),2) INTO BALANCE FROM GL WHERE ACC_NUM = P.LACT_CODE AND GL_DAT <= ASON ;

FOR I IN (SELECT * FROM GL WHERE VCH_IDE = 8 AND ACC_NUM = P.LACT_CODE AND  NVL(AMOUNT,0) > 0 AND GL_DAT <= ASON   ORDER BY TO_DATE(GL_DAT) desc )

LOOP

IF NVL(BALANCE,0) >= I.AMOUNT THEN 

    INSERT INTO TBL_INV_AGING (PTYCODE,DUEDAYS,INV_DATE,INV_AMT,balance,REMK,DUE_DAYS)VALUES(p.LACT_CODE,TRUNC(SYSDATE) - TRUNC(I.GL_DAT),I.GL_DAT,I.AMOUNT,balance,'INVOICE',P.DUE_DAYS);

ELSE --NVL(BALANCE,0) < I.AMOUNT THEN 

INSERT INTO TBL_INV_AGING (PTYCODE,DUEDAYS,INV_DATE,INV_AMT,balance,REMK,DUE_DAYS)VALUES(I.ACC_NUM,TRUNC(SYSDATE) - TRUNC(I.GL_DAT),I.GL_DAT,BALANCE,balance,'BALANCE',P.DUE_DAYS);

EXIT;

END IF;

    BALANCE := BALANCE  - I.AMOUNT;

END LOOP;

END LOOP;

COMMIT;

DELETE FROM TBL_INV_AGING WHERE BALANCE=0;

COMMIT; 

END;

/