Hello,
Request for SQL Query for Oracle EBS R12 AR Customer Statement.
cases:
1. invoice number 10421 Amount:500, without any receipts.
2. invoice_number = 10422 Amount: 1000
Receipt fully applied wit status cleared/confirmed/remitted.
Receipt: R10422_1
Amount:100 Applied to Invoice 10422
3. Receipt half applied and half un applied.
Receipt : R10422_2 Amount: 300
100 Applied to Invoice_Number = 10422
200 unapplied
Columns needed.
customer_id, invoice_number, AMOUNT_DUE_ORIGINAL, RECEIPT_NUMBER (IF APPLIED), APPLIED_AMT, UNAPPLIED_AMT
123,10421, 500,500,null,null,null
123,10422, 1000, R10422_1,100,0
123,10422, 1000, R10422_2,100,200
Tried using below tables:
RA_CUSTOMER_TRX_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_CASH_RECEIPTS_ALL
AR_RECEIVABLE_APPLICATIONS_ALL
But not able to get the desired output.
Kindly suggest.
Regards,
Afzal.