ORACLE MASTERMINDS

Query to fetch Customer Statement of Account Details

SELECT   rc.customer_name "Customer_Name",
         rc.customer_number "Customer_Number", hou.NAME operating_unit,
         DECODE (ps.CLASS,
                 'INV', 'Invoice',
                 'DM', 'Debit Memo',
                 'DEP', 'Deposit',
                 'CM', 'Credit Memo',
                 'PMT', 'Receipts'
                ) invoice_type,
         ps.trx_number invoice_or_receipt_number, ps.trx_date invoice_date,
         cr.doc_sequence_value document_number,
         cr.customer_receipt_reference REFERENCE,
         remit_bank_branch.bank_name remitance_bank_name, ps.gl_date,
         CASE
            WHEN ps.amount_due_original < 0
               THEN NVL (ABS (  ps.amount_due_original
                              * NVL (ps.exchange_rate, 1)
                             ),
                         0
                        )
            ELSE 0
         END credit_amount,
         CASE
            WHEN ps.amount_due_original > 0
               THEN NVL (ABS (  ps.amount_due_original
                              * NVL (ps.exchange_rate, 1)
                             ),
                         0
                        )
            ELSE 0
         END debit_amount,
         NVL (REPLACE (rat.interface_header_attribute12, CHR (9), ''),
              ''
             ) description,
         ps.amount_due_remaining balance,
         TO_DATE (SYSDATE) - TO_DATE (ps.trx_date) aging,
         SUM (ps.amount_applied) amount,
         loc.address1 || loc.address2 || loc.address3 bill_to_address,
         glcc.segment2, hou.organization_id, rc.customer_id,
         ps.created_by cussoa_created_by,
         ps.invoice_currency_code invoice_curr_code
    FROM apps.ra_customers rc,
         apps.ar_payment_schedules_all ps,
         apps.ra_customer_trx_all rat,
         apps.hr_operating_units hou,
         apps.ap_bank_accounts_all apba,
         apps.ap_bank_branches apb,
         apps.ra_cust_trx_line_gl_dist_all rag,
         apps.gl_code_combinations glcc,
         apps.hz_cust_accounts cust_acct,
         apps.hz_parties party,
         apps.hz_cust_acct_sites_all acct_site,
         apps.hz_party_sites party_site,
         apps.hz_locations loc,
         apps.ar_cash_receipts_all cr,
         apps.ar_cash_receipt_history_all crh,
         apps.ap_bank_accounts_all remit_bank,
         apps.ap_bank_branches remit_bank_branch
   WHERE ps.customer_id = rc.customer_id
     AND ps.trx_number = rat.trx_number(+)
     AND ps.org_id = hou.organization_id
     AND rag.code_combination_id = glcc.code_combination_id(+)
     AND rat.customer_trx_id = rag.customer_trx_id(+)
     AND rat.customer_bank_account_id = apba.bank_account_id(+)
     AND apba.bank_branch_id = apb.bank_branch_id(+)
     AND ps.customer_id = cust_acct.cust_account_id(+)
     AND cust_acct.party_id = party.party_id(+)
     AND cust_acct.party_id = party.party_id
     AND cust_acct.cust_account_id = acct_site.cust_account_id
     AND ps.customer_id = acct_site.cust_account_id
     AND acct_site.party_site_id = party_site.party_site_id
     AND loc.location_id = party_site.location_id
     AND rc.customer_id = NVL (p_customer, rc.customer_id)
     AND ps.cash_receipt_id = cr.cash_receipt_id(+)
     AND crh.cash_receipt_id(+) = cr.cash_receipt_id
     AND remit_bank.bank_account_id(+) = cr.remittance_bank_account_id
     AND remit_bank.bank_branch_id = remit_bank_branch.bank_branch_id(+)
     AND hou.organization_id = NVL (p_operating_unit, hou.organization_id)
     AND ps.gl_date BETWEEN TO_DATE (p_from_date, 'RRRR/MM/DD hh24:mi:ss')
                        AND TO_DATE (p_to_date, 'RRRR/MM/DD hh24:mi:ss')
GROUP BY rc.customer_name,
         rc.customer_number,
         hou.NAME,
         ps.CLASS,
         ps.trx_number,
         ps.trx_date,
         rat.doc_sequence_value,
         rat.ct_reference,
         apb.bank_name,
         ps.gl_date,
         ps.amount_due_original,
         rat.interface_header_attribute12,
         ps.amount_due_remaining,
         ps.trx_date,
         ps.amount_applied,
         hou.organization_id,
         rc.customer_id,
         ps.created_by,
         ps.exchange_rate,
         glcc.segment2,
         ps.invoice_currency_code,
         loc.address1,
         loc.address2,
         loc.address3,
         remit_bank_branch.bank_name,
         cr.doc_sequence_value,
         cr.customer_receipt_reference;

By
Deepak J