AR Customer Receipt Details

  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)