Select
acra.RECEIPT_NUMBER TRX_NUMBER,
HZ.PARTY_NAME CUSTOMER_NAME,
(Select listagg(apsa1.trx_number,’;’) within group (order by RECEIVABLE_APPLICATION_ID)
from
ar_payment_schedules_all apsa1,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA
where
araa.APPLIED_PAYMENT_SCHEDULE_ID=apsa1.PAYMENT_SCHEDULE_ID
and araa.cash_receipt_id=acra.cash_receipt_id
and araa.display=’Y’
) Application_Reference
from
AR_CASH_RECEIPTS_ALL Acra,
AR_PAYMENT_SCHEDULES_ALL apsa ,
HZ_CUST_ACCOUNTS hca,
HZ_PARTIES HZ
where
acra.CASH_RECEIPT_ID = apsa.CASH_RECEIPT_ID(+)
and acra.PAY_FROM_CUSTOMER=hca.cust_account_id
and hca.party_id=hz.party_id
