Applied Receipts Register Query (Query to find Receipt applied against which transaction) in Oracle Apps R12

SELECT a.cash_receipt_id,
       (SELECT gcc.segment1
        FROM   ap_system_parameters_all ap,
               gl_code_combinations_kfv gcc,
               hr_operating_units ou
        WHERE  ap.accts_pay_code_combination_id = gcc.code_combination_id
               AND ap.org_id = ou.organization_id
               AND ou.organization_id = a.org_id)        unit_code,
       e.name                                            org_name,
       d.customer_name,
       a3.bank_account_name,
       a3.bank_account_num,
       a.trx_number                                      receipt_number,
       a.trx_date                                        receipt_date,
       a.amount_due_original * Nvl (c.exchange_rate, 1)  receipt_amt,
       a.amount_applied * Nvl (c.exchange_rate, 1)       amount_applied,
       a.amount_due_remaining * Nvl (c.exchange_rate, 1) remaining_amount,
       b.amount_applied * Nvl (c.exchange_rate, 1)       inv_amt_applied,
       -- A.AMOUNT_ADJUSTED, A.AMOUNT_CREDITED,
       c.trx_number                                      invoice_number,
       e1.name                                           inv_type,
       c.trx_date                                        invoice_date,
       b.apply_date,
       b.gl_date
FROM   ar_payment_schedules_all a,
       ar_receivable_applications_all b,
       ra_customer_trx_all c,
       ar_customers d,
       hr_operating_units e,
       ra_cust_trx_types_all e1,
       ar_cash_receipts_all a1,
       ce_bank_acct_uses_all a2,
       ce_bank_accounts a3
WHERE  a.cash_receipt_id = b.cash_receipt_id
       AND a.cash_receipt_id = a1.cash_receipt_id
       AND a1.remit_bank_acct_use_id = a2.bank_acct_use_id
       AND a2.bank_account_id = a3.bank_account_id
       AND b.applied_customer_trx_id = c.customer_trx_id(+)
       AND a.customer_id = d.customer_id(+)
       AND a.org_id = e.organization_id
       AND c.cust_trx_type_id = e1.cust_trx_type_id
       AND c.org_id = e1.org_id
       AND A.trx_date >= To_date(:p_from_date)
       AND A.trx_date < To_date(:p_to_date) + 1
       AND a.class = 'PMT'
       --INV->Invoice; DM->Memo; CM->Credit Memo; CB->Chargeback; PMT->receipt; BR->Bills Receivable
       AND display = 'Y'
--     AND c.trx_number = '21*******941'
ORDER  BY a.org_id,
          a.trx_date,
          a.trx_number;

(OR)

SELECT F.customer_trx_id,
       D.source_id_int_1,
       d.entity_code,
       f.bill_to_customer_id,
       d.transaction_number,
       (SELECT x.name FROM   apps.ra_terms x WHERE  x.term_id = f.term_id)
       term_name,
       CASE
         WHEN d.entity_code = 'RECEIPTS' THEN (SELECT X.receipt_date
                                               FROM
         apps.ar_cash_receipts_all X
                                               WHERE
         X.cash_receipt_id = D.source_id_int_1)
       END
       RECEIPT_DATE,
       (SELECT X.amount * Nvl(X.exchange_rate, 1)
        FROM   apps.ar_cash_receipts_all X
        WHERE  X.cash_receipt_id = D.source_id_int_1)
       RECEIPT_AMOUNT,
       a.accounting_class_code,
       b.segment1
       unit_code,
       (SELECT customer_name
        FROM   apps.ar_customers
        WHERE  customer_id = f.bill_to_customer_id)
       customer_name,
       g.name
       org_name,--e.APPLIED_TO_SOURCE_ID_NUM_1,
       e.applied_to_entity_code,
       f.trx_number,
       (SELECT SUM(X.amount_due_original)
        FROM   apps.ar_payment_schedules_all X
        WHERE  X.customer_trx_id = F.customer_trx_id)
       INVOICE_AMOUNT,
       (SELECT Max(X.trx_date)
        FROM   apps.ar_payment_schedules_all X
        WHERE  X.customer_trx_id = F.customer_trx_id)
       INVOICE_DATE,
       Nvl((SELECT SUM(X.amount_applied * Nvl(Y.exchange_rate, 1))
            FROM   apps.ar_receivable_applications_all X,
                   apps.ar_cash_receipts_all Y
            WHERE  X.cash_receipt_id = Y.cash_receipt_id
                   AND X.applied_customer_trx_id = F.customer_trx_id
                   AND X.gl_date BETWEEN '01-Oct-2022' AND '31-Mar-2023'
                   AND X.cash_receipt_id = D.source_id_int_1
                   AND X.display = 'Y'), 0)
       APPLIED_AMOUNT,
       SUM(Nvl(e.unrounded_accounted_dr, 0) - Nvl(e.unrounded_accounted_cr, 0))
       amt
FROM   apps.xla_ae_lines a,
       apps.gl_code_combinations b,
       apps.xla_ae_headers c,
       xla.xla_transaction_entities d,
       apps.xla_distribution_links e,
       apps.ra_customer_trx_all f,
       apps.hr_operating_units g
WHERE  a.code_combination_id = b.code_combination_id
       AND a.ae_header_id = e.ae_header_id
       AND a.ae_line_num = e.ae_line_num
       AND a.application_id = e.application_id
       AND a.ae_header_id = c.ae_header_id
       AND a.application_id = c.application_id
       AND c.entity_id = d.entity_id
       AND d.security_id_int_1 = g.organization_id
       AND c.application_id = d.application_id
       AND e.applied_to_source_id_num_1 = f.customer_trx_id(+)
       AND a.ledger_id = :P_LEDGER_ID
       AND b.segment2 = '<COA_ACCOUNT_CODE>'
       --AND D.TRANSACTION_NUMBER='<------>'
       --and d.entity_code!='RECEIPTS'
       --and c.event_id=163694391
       AND a.accounting_date BETWEEN '01-Oct-2022' AND '31-Mar-2023'
GROUP  BY d.entity_code,
          b.segment1,
          f.bill_to_customer_id,
          e.applied_to_source_id_num_1,
          e.applied_to_entity_code,
          d.transaction_number,
          f.trx_number,
          a.accounting_class_code,
          g.name,
          F.customer_trx_id,
          D.source_id_int_1,
          f.term_id
ORDER  BY 4,2,7;