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!

Query to find customer open Invoice and receipt amounts

Discussion in 'Oracle Financials' started by venkateshguru, Oct 9, 2013.

  1. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Hi Guru,

    can you please let me know how do we find out the customer open balance and credit limit and cash paid. Which table we need to consider. i have the below query please check and confirm whether its correct.

    SELECT E.CUSTOMER_ID,
    D.PARTY_NAME,
    INVOICE_CURRENCY_CODE,
    SUM (AMOUNT_DUE_ORIGINAL),
    SUM (AMOUNT_DUE_REMAINING),
    SUM (ACCTD_AMOUNT_DUE_REMAINING)
    FROM APPS.hz_cust_site_uses_all A,
    HZ_CUST_ACCT_SITES_ALL B,
    APPS.hz_cust_accounts C,
    APPS.HZ_PARTIES D,
    APPS.ar_payment_schedules_all E
    WHERE 1 = 1
    AND A.CUST_ACCT_SITE_ID = B.CUST_ACCT_SITE_ID
    AND A.site_use_code = 'BILL_TO'
    AND A.STATUS = 'A'
    AND B.cust_account_id = C.cust_account_id
    AND D.PARTY_ID = C.PARTY_ID
    AND E.CUSTOMER_ID = C.CUST_ACCOUNT_ID
    --AND E.CUSTOMER_ID=1425
    AND E.CUSTOMER_SITE_USE_ID = A.SITE_USE_ID
    AND E.CLASS = 'INV'
    AND E.STATUS = 'OP'
    --AND D. party_id=85292
    AND E.AMOUNT_DUE_REMAINING <> 0
    GROUP BY CUSTOMER_ID, PARTY_NAME,INVOICE_CURRENCY_CODE
    ORDER BY CUSTOMER_ID DESC

    --Payment by Customer

    SELECT E.CUSTOMER_ID,
    D.PARTY_NAME,
    INVOICE_CURRENCY_CODE,
    SUM (AMOUNT_DUE_ORIGINAL),
    SUM (AMOUNT_DUE_REMAINING),
    SUM (ACCTD_AMOUNT_DUE_REMAINING)
    FROM APPS.hz_cust_site_uses_all A,
    HZ_CUST_ACCT_SITES_ALL B,
    APPS.hz_cust_accounts C,
    APPS.HZ_PARTIES D,
    APPS.ar_payment_schedules_all E
    WHERE 1 = 1
    AND A.CUST_ACCT_SITE_ID = B.CUST_ACCT_SITE_ID
    AND A.site_use_code = 'BILL_TO'
    AND A.STATUS = 'A'
    AND B.cust_account_id = C.cust_account_id
    AND D.PARTY_ID = C.PARTY_ID
    AND E.CUSTOMER_ID = C.CUST_ACCOUNT_ID
    --AND E.CUSTOMER_ID=1425
    AND E.CUSTOMER_SITE_USE_ID = A.SITE_USE_ID
    AND E.CLASS = 'PMT'
    AND E.STATUS = 'OP'
    --AND D. party_id=85292
    AND E.AMOUNT_DUE_REMAINING <> 0
    GROUP BY CUSTOMER_ID, PARTY_NAME,INVOICE_CURRENCY_CODE
    ORDER BY CUSTOMER_ID DESC

    --Open invoices by customer

    SELECT hp.party_name,
    aps.customer_trx_id,
    aps.payment_schedule_id,
    aps.amount_due_original,
    aps.amount_applied,
    aps.amount_due_remaining,
    class,
    aps.status,
    aps.CUSTOMER_ID,
    aps.invoice_currency_code,
    aps.ACCTD_AMOUNT_DUE_REMAINING,
    ra.EXCHANGE_RATE
    FROM ra_customer_trx_all ra,
    ra_customer_trx_lines_all rl,
    ar_payment_schedules_all aps,
    ra_cust_trx_types_all rt,
    hz_cust_accounts hc,
    hz_parties hp,
    hz_cust_acct_sites_all hcasa_bill,
    hz_cust_site_uses_all hcsua_bill,
    hz_party_sites hps_bill,
    ra_cust_trx_line_gl_dist_all rct
    WHERE 1 = 1
    AND ra.customer_trx_id = rl.customer_trx_id
    AND ra.customer_trx_id = aps.customer_trx_id
    AND ra.org_id = aps.org_id
    AND rct.customer_trx_id = aps.customer_trx_id
    AND rct.customer_trx_id = ra.customer_trx_id
    AND rct.customer_trx_id = rl.customer_trx_id
    AND rct.customer_trx_line_id = rl.customer_trx_line_id
    AND ra.complete_flag = 'Y'
    AND rl.line_type IN ('FREIGHT', 'LINE')
    AND ra.cust_trx_type_id = rt.cust_trx_type_id
    --AND RT.CUST_TRX_TYPE_ID=1340
    AND ra.bill_to_customer_id = hc.cust_account_id
    AND hc.status = 'A'
    AND hp.party_id = hc.party_id
    AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
    AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
    AND hcsua_bill.site_use_code = 'BILL_TO'
    AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
    AND hps_bill.party_site_id = hcasa_bill.party_site_id
    AND hcasa_bill.status = 'A'
    AND hcsua_bill.status = 'A'
    -- AND aps.amount_due_remaining <> 0
    AND aps.CLASS='INV'
    AND aps.status = 'OP'
    AND APS.AMOUNT_DUE_ORIGINAL <> 0
    AND hc.cust_account_id = 1425
    GROUP BY hp.party_name,
    aps.customer_trx_id,
    aps.amount_due_original,
    aps.amount_due_remaining,
    aps.amount_applied,
    aps.payment_schedule_id,
    class,
    aps.status,
    aps.CUSTOMER_ID,
    aps.invoice_currency_code,
    aps.ACCTD_AMOUNT_DUE_REMAINING,
    ra.EXCHANGE_RATE
    ORDER BY CUSTOMER_TRX_ID
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi, Did you find the right Query?