Oracle Cloud + Query to get AR Receipts Recommendations Details

 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