Description:
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;
Summary: This post explains about Query to fetch Customer Statement of Account Details
Queries?
Do drop a note by writing us at contact@staging.doyensys.com or use the comment section below to ask your questions.
