1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

AR & AP Opening Balances Amount as on particular date

Discussion in 'Oracle Financials' started by malli_18arjun, Feb 24, 2014.

  1. malli_18arjun

    malli_18arjun Active Member

    Messages:
    36
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Hyderabad
    Hi

    Need inputs to get AR & AP opening balances amount as on particular date.

    Thanks
    Arjun
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Arjun,

    For AR Opening Balance You can refer " Aging - 7 Buckets Report " .

    Any how the Below query will give customer Opening Balnce.( plz Test once)

    Code (SQL):

     SELECT NVL(SUM(inv_amount),0)
     FROM(
                SELECT
                        ROUND(NVL(aps.amount_due_original,0)*NVL(aps.EXCHANGE_RATE,1),2)   inv_amount
                FROM      
                        ar_payment_schedules_all     aps
                WHERE   1=1
                AND   aps.CUSTOMER_ID = :CUST_ACCOUNT_ID
                AND     TRUNC(aps.GL_DATE) <  :CF_START_DATE
                AND aps.org_id = :P_ORG_ID
                UNION ALL
                SELECT
                        NVL(adj.ACCTD_AMOUNT,0)   inv_amount
                FROM      
                        ar_payment_schedules_all     aps
                        ,AR_ADJUSTMENTS_ALL            adj  
                WHERE      aps.CUSTOMER_ID = :CUST_ACCOUNT_ID
                AND     aps.CUSTOMER_TRX_ID   =   adj.CUSTOMER_TRX_ID
                AND     TRUNC(adj.GL_DATE)  <  :CF_START_DATE
                AND   aps.org_id = :P_ORG_ID
                AND   aps.org_id = adj.org_id
                AND    adj.STATUS ='A'        
                UNION ALL
                SELECT
                        NVL(ach.ACCTD_AMOUNT,0) inv_amount
                FROM    
                        ar_cash_receipts_all         acr
                        ,ar_payment_schedules_all     aps
                        ,ar_cash_receipt_history_all ach
                WHERE  
                             1=1
                AND    acr.PAY_FROM_CUSTOMER = :CUST_ACCOUNT_ID
                AND    acr.CASH_RECEIPT_ID   = aps.CASH_RECEIPT_ID
                AND    aps.org_id = :P_ORG_ID
                AND    aps.org_id = acr.org_id
                AND    aps.org_id = ach.org_id
                AND    acr.cash_receipt_id = ach.cash_receipt_id
                AND    ach.trx_date  <  :CF_START_DATE
                AND    ach.STATUS ='REVERSED'  
                UNION ALL
                SELECT  
                         -ROUND(NVL(ara.AMOUNT_APPLIED,0)*NVL(rct.EXCHANGE_RATE,1),2)  inv_amount
                FROM
                     ar_cash_receipts_all         acr,
                     ar_receivable_applications_all ara,
                     ra_customer_trx_all       rct
                WHERE 1=1
                AND   ara.CASH_RECEIPT_ID=acr.CASH_RECEIPT_ID
                AND ara.APPLIED_CUSTOMER_TRX_ID = rct.CUSTOMER_TRX_ID(+)
                AND   ara.STATUS='APP'
                AND   acr.PAY_FROM_CUSTOMER = :CUST_ACCOUNT_ID
                AND ara.org_id = :P_ORG_ID
                AND ara.org_id = acr.org_id
                AND ara.org_id = rct.org_id
                --AND ara.GL_DATE  <  :CF_START_DATE --Changed on 31-JAN-13 by chandra
                AND trunc(NVL(ara.reversal_gl_date,ara.GL_DATE)) <  :CF_START_DATE
                UNION ALL
                SELECT  
                         -ROUND(NVL(ara.AMOUNT_APPLIED,0)*NVL(acr.EXCHANGE_RATE,1),2)  inv_amount
                FROM
                     ar_cash_receipts_all         acr,
                     ar_receivable_applications_all ara    
                WHERE 1=1
                AND   acr.PAY_FROM_CUSTOMER = :CUST_ACCOUNT_ID
                AND   ara.CASH_RECEIPT_ID=acr.CASH_RECEIPT_ID
                AND   ara.STATUS='UNAPP'
                AND ara.org_id = :P_ORG_ID
                AND acr.org_id =ara.org_id
                AND ara.program_id IS NOT NULL
                AND trunc(NVL(ara.reversal_gl_date,ara.GL_DATE)) <  :CF_START_DATE
                );
     

    Regards
    Sambasiva Reddy.K