Introduction:
This SQL query is used to fetching the details of an institution
Based on their requirements
overview:
this query gives AR invoice details
ELECT SUM(rcl.unit_selling_price* rcl.quantity_invoiced)
FROM ra_customer_trx_lines_all rcl
WHERE rct.customer_trx_id = rcl.customer_trx_id) total ,
NVL((SELECT sum(araa.amount_applied)
FROM ar_receivable_applications_all araa,
ar_cash_receipts_all acra
WHERE araa.cash_receipt_id = acra.cash_receipt_id(+)
and rct.customer_trx_id = araa.applied_customer_trx_id(+)
and application_type = ‘CASH’),0) cash,
NVL((SELECT sum(araa.amount_applied)
FROM ar_receivable_applications_all araa,
ar_cash_receipts_all acra
WHERE araa.cash_receipt_id = acra.cash_receipt_id(+)
and rct.customer_trx_id = araa.applied_customer_trx_id(+)
and application_type = ‘CM’),0) cm,
NVL((SELECT sum(araa.amount_applied)
FROM ar_receivable_applications_all araa,
ar_cash_receipts_all acra
WHERE araa.cash_receipt_id = acra.cash_receipt_id(+)
and rct.customer_trx_id = araa.applied_customer_trx_id(+)
and application_type IN( ‘CASH’,’CM’)),0) payment,
(SELECT SUM(rcl.unit_selling_price* rcl.quantity_invoiced)
FROM ra_customer_trx_lines_all rcl
WHERE rct.customer_trx_id = rcl.customer_trx_id) –
NVL((SELECT sum(araa.amount_applied)
FROM ar_receivable_applications_all araa,
ar_cash_receipts_all acra
WHERE araa.cash_receipt_id = acra.cash_receipt_id(+)
and rct.customer_trx_id = araa.applied_customer_trx_id(+)),0) balance_due
from ra_customer_trx_all rct
where rct.trx_number = :trx_number
AND rct.customer_trx_id = :customer_trx_id
AND rct.org_id = NVL(:p_organization_id,rct.org_id)
