How to Find the Link Between Transaction Number and Receipt Number in AR

 

ELECT  ACR.RECEIPT_NUMBER RECEIPT_NO,RCT.TRX_NUMBER INVOICE_NO
FROM    AR_RECEIVABLE_APPLICATIONS_ALL ARA,
        AR_CASH_RECEIPTS_ALL           ACR,
        RA_CUSTOMER_TRX_ALL            RCT
WHERE   ARA.STATUS='APP'
  AND   ARA.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID
  AND   ARA.APPLIED_CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID

Select distinct acra.cash_receipt_id , araa.applied_payment_schedule_id ,
acra.amount ,
acra.status , acra.org_id ,acra.receipt_number , aps.status apsstatus ,
aps.customer_id ,aps.customer_trx_id ,
aps.amount_applied ,aps.amount_due_original ,aps.amount_due_remaining
,aps.customer_site_use_id ,
aps.cash_receipt_status_last from ar_cash_receipts_all acra ,
ar_payment_schedules_all aps ,
ar_receivable_applications_all araa, ra_customer_trx_all rcta ,
ra_customer_trx_lines_all rctla
where aps.cash_receipt_id = acra.cash_receipt_id
and acra.customer_site_use_id = aps.customer_site_use_id
andaraa.cash_receipt_id = acra.cash_receipt_id
*--and acra.status = 'APP'*
and araa.applied_payment_schedule_id is not null
and aps.class = 'PMT'
and acra.status != 'REV'