SELECT cr.doc_sequence_value Receipt_number,
TO_CHAR (cr.receipt_date, 'DD/MM/YYYY') "Receipt_Date",
cr.receipt_number Cheque_Num,
TO_CHAR (aps.due_date, 'DD/MM/YYYY') Cheque_Date,
crh_current.acctd_amount Amount
-- ,cr.CUSTOMER_SITE_USE_ID
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh_current,
ar_payment_schedules_all aps,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_parties hp
WHERE crh_current.cash_receipt_id = cr.cash_receipt_id
AND crh_current.org_id = cr.org_id
AND crh_current.current_record_flag = NVL ('Y', cr.receipt_number)
AND crh_current.status IN ('CONFIRMED', 'REMITTED')
AND cr.customer_site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND hp.party_name = :p_customer_name
AND aps.trx_number = cr.receipt_number
and cr.org_id = nvl(:p_org_id,cr.org_id)
