Aging Report Procedure

CREATE CUSTOMIZED TABLE AGING REPORT

DROP TABLE APPS.CUST_AGING_POPULATE CASCADE CONSTRAINTS;

CREATE TABLE APPS.CUST_AGING_POPULATE

(

  INVOICE_NUM            VARCHAR2(50 BYTE),

  INVOICE_ID             NUMBER(15),

  VENDOR_NAME            VARCHAR2(360 BYTE),

  VENDOR_NUMBER          VARCHAR2(30 BYTE),

  INVOICE_AMOUNT         NUMBER,

  AMOUNT_PAID            NUMBER,

  WHTAX                  NUMBER,

  TAX                    NUMBER,

  GL_DATE                DATE,

  AS_ON_DATE             DATE,

  DAYS_BETWEEN           NUMBER,

  DAYS                   NUMBER,

  DAYS_NAME              VARCHAR2(50 BYTE),

  PAY_GROUP_LOOKUP_CODE  VARCHAR2(25 BYTE),

  STATUS                 VARCHAR2(50 BYTE)

)

TABLESPACE APPS_TS_TX_DATA

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          128K

            NEXT             128K

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

PROCEDURE   AGING REPORT

CREATE OR REPLACE PROCEDURE APPS.CUST_AGING_PROC

(

    ERRBUF                      OUT     VARCHAR2,

    RETCODE                     OUT     VARCHAR2,

    P_AS_ON_DATE                        VARCHAR2

    ,P_PAY_GROUP                          VARCHAR2

)

AS

    P_DATE_AS_ON                 DATE;

BEGIN

   

    P_DATE_AS_ON := TO_DATE(SUBSTR(P_AS_ON_DATE,1,10),'YYYY/MM/DD');

            DELETE FROM     CUST_AGING_POPULATE;

            COMMIT;

            INSERT INTO     CUST_AGING_POPULATE                

select              distinct

                    ai.INVOICE_NUM , ai.INVOICE_ID , 

                    NVL (pv.vendor_name, hp.party_name) vendor_name

                ,   NVL (pv.SEGMENT1 , hp.PARTY_NUMBER) vendor_number   

                ,   (select  sum(ail.AMOUNT)                    

                    from    AP_INVOICE_lines_all ail                     

                    where ail.INVOICE_ID = ai.INVOICE_ID

                    and ail.LINE_TYPE_LOOKUP_CODE in ('ITEM'))INVOICE_AMOUNT   

                ,   ai.AMOUNT_PAID

                ,   (select  sum(ail.AMOUNT)

                    from    AP_INVOICE_lines_all ail

                    where   ail.INVOICE_ID = ai.INVOICE_ID

                    and     ail.LINE_TYPE_LOOKUP_CODE = 'AWT' ) WHTAX

                ,   (select  sum(ail.AMOUNT)

                    from    AP_INVOICE_lines_all ail

                    where   ail.INVOICE_ID = ai.INVOICE_ID

                    and     ail.LINE_TYPE_LOOKUP_CODE = 'TAX' ) TAX

                ,   ai.GL_DATE       ,   P_DATE_AS_ON as as_on_date  , (P_DATE_AS_ON-ai.GL_DATE) as days_between

                ,   case when      AT1.NAME = '7 DAYS'      then  7

                         when      AT1.NAME = '15 DAYS'     then  15

                         when      AT1.NAME = '30 DAYS'     then  30

                         when      AT1.NAME = '45 DAYS'     then  45

                         when      AT1.NAME = '60 DAYS'     then  60

                         when      AT1.NAME in ('ADVANCE 100%','IMMEDIATE')   then  0

                         end DAYS

                ,   at1.NAME days_name  ,   ai.PAY_GROUP_LOOKUP_CODE 

                ,   ap_invoices_pkg.get_approval_status

                     (ai.invoice_id,

                      ai.invoice_amount,

                      ai.payment_status_flag,

                      ai.invoice_type_lookup_code

                     )  status      

from                po_vendors pv

                ,   hz_parties hp  

                ,   AP_INVOICES_all ai

                ,   ap_terms_tl AT1

where               pv.vendor_id(+)     = ai.vendor_id

AND                 ai.party_id         = hp.party_id                 

AND                 ai.terms_id         = AT1.term_id(+)

AND                 TRUNC(AI.GL_DATE )         <= P_DATE_AS_ON

AND                 AI.PAY_GROUP_LOOKUP_CODE      = NVL(P_PAY_GROUP,AI.PAY_GROUP_LOOKUP_CODE)

;

COMMIT;

                delete from CUST_AGING_PARA;

                commit;

                INSERT INTO CUST_AGING_PARA   

                (PAY_GROUP) VALUES (P_PAY_GROUP);

COMMIT;

DELETE FROM CUST_AGING_POPULATE CAP WHERE (CAP.INVOICE_AMOUNT+nvl(cap.TAX,0)+nvl(cap.WHTAX,0)) = CAP.AMOUNT_PAID;

COMMIT;

           

EXCEPTION

   WHEN OTHERS

   THEN

      NULL;

END;