Query to retrive Account Receivable Invoice, Receipt and Payment Status

Query to retrive Account Receivable Invoice, Receipt and Payment Status

-- ------------------------------------------------------------------------------------------------------
-- Purpose : Query to retrive Account Receivable Invoice, Receipt and Payment Status
-- Date      : 02.Mar.2017
-- Created by : Mohammed Nasiruddin
-- .....
-- ------------------------------------------------------------------------------------------------------


SELECT ct.CUSTOMER_TRX_ID,
ps_inv.TRX_NUMBER,
ct.ct_reference,
(select sum(extenDed_amount) from ra_customer_trx_lines_all
where customer_trx_id = ct.CUSTOMER_TRX_ID) Invoice_Amount,
cr.RECEIPT_NUMBER,
cr.STATUS,
cr.AMOUNT total_Receipt_amount,
nvl((select sum(-amount)
from ar_adjustments_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0) Adjustment_Amount
/*decode((nvl((select sum(-amount)
from ar_adjustments_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0)+cr.AMOUNT) -
(select sum(extenDed_amount) from ra_customer_trx_lines_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0,'Invoice Fully Paid', 'Partially Paid') Payment_Status */
FROM ar_receivable_applications_all app,
ar_cash_receipts cr,
ar_payment_schedules_all ps_inv,
ra_customer_trx_all ct,
ar_receivables_trx_all art
WHERE 1=1
AND app.cash_receipt_id = cr.cash_receipt_id
AND ct.customer_trx_id(+) = ps_inv.customer_trx_id
AND app.applied_payment_schedule_id = ps_inv.payment_schedule_id
AND art.receivables_trx_id(+) = app.receivables_trx_id
--AND cr.RECEIPT_NUMBER = :ar_receipt_num
AND ct.TRX_NUMBER = :ar_invoice_num;