SELECT
acra.receipt_number receipt_number
,rcta.trx_number recommendation
,acr.match_score_value reference_score
,DECODE(acr.match_reason_code ,'AR_AA_BELOW_TRX_TSLD','Below transaction threshold','AR_AM_INV_THRESHOLD','Above transaction threshold') reason
,apsa.amount_due_remaining balance
,DECODE(tta.type, 'INV', 'Invoice', 'CM', 'Credit Memo') document_type
,hp.party_name customer
,acr.recommendation_status accepted
,acr.amount_applied
,SUBSTR(apsa.trx_number,'-0',7)||' '||acr.amount_applied Reference_number
FROM
ar_cash_receipts_all acra
,ar_receipt_methods arm
,ar_cash_remit_refs_all acrfa
,ar_cash_recos_all acr
,ar_cash_reco_lines_all acrla
,ra_customer_trx_all rcta
,ar_payment_schedules_all apsa
,ra_cust_trx_types_all tta
,hz_cust_accounts hca
,hz_parties hp
,hz_party_sites hps
,hz_cust_site_uses_all hcsu
,hz_cust_acct_sites_all hcsa
WHERE 1 = 1
AND acra.receipt_number = '111300952062031'
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.cash_receipt_id = acrfa.cash_receipt_id
AND acrfa.remit_reference_id = acr.remit_reference_id
AND acr.recommendation_id = acrla.recommendation_id
AND acr.resolved_matching_number = rcta.trx_number
AND rcta.customer_trx_id = apsa.customer_trx_id
AND rcta.cust_trx_type_seq_id = tta.cust_trx_type_seq_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND acr.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND acr.customer_site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.cust_account_id = hca.cust_account_id
AND hcsa.party_site_id = hps.party_site_id
Thanks
Amar Alam
