Prepayment balance query in oracle

Prepayment balance query in oracle

In this post , We will discuss abut Prepayment balance query in oracle. Prepayment means , Advance given to the supplier before using its services or goods and when supplier sent his final invoice then we adjust the advance / prepayment given to this supplier initially and we do payment to this supplier against his invoice for the rest of the payments. Here below I am sharing the Prepayment balance query in oracle which helps to list all the pending prepayment which are still open for the apply.


SELECT hou.NAME
,aia.INVOICE_NUM Invoice_Number
,aia.INVOICE_TYPE_LOOKUP_CODE
,aia.INVOICE_CURRENCY_CODE Invoice_Currency-- *
,to_char(aia.INVOICE_DATE, 'RRRR/MM/DD') Invoice_Date -- *
,(
SELECT vendor_name
FROM ap_suppliers
WHERE vendor_id = aia.VENDOR_ID
) Supplier_Name -- *
,(
SELECT segment1
FROM ap_suppliers
WHERE vendor_id = aia.VENDOR_ID
) Supplier_Number -- *
,(
SELECT party_site_name
FROM ap_supplier_sites_all assa
,hz_party_sites hps
WHERE assa.party_site_id = hps.party_site_id
AND assa.vendor_site_id = aia.VENDOR_SITE_ID
) Supplier_Site -- *
,aia.DESCRIPTION Description
,aila.LINE_NUMBER LINE_NO
,aiDA.LINE_TYPE_LOOKUP_CODE LINE_Type
,GCC.CONCATENATED_SEGMENTS
,sum(AIDA.AMOUNT) LINE_AMOUNT
,NVL(ap_invoices_utility_pkg.get_prepay_amount_remaining(
aia.invoice_id ),0) PREPAYMENT_AMOUNT_REMAINING
,aia.invoice_amount Invoice_Amount
FROM AP_INVOICES_ALL aia
,hr_operating_units hou,
apps.ap_invoice_distributions_all aida,
apps.ap_invoice_lines_all aila,
APPS.GL_CODE_COMBINATIONS_KFV GCC
WHERE aia.INVOICE_TYPE_LOOKUP_CODE IN ( 'PREPAYMENT')
AND aia.org_id = hou.organization_id
AND aia.payment_status_flag IN ('Y')
AND aia.cancelled_date IS NULL
AND AIDA.DIST_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND aida.INVOICE_ID=AIA.INVOICE_ID
and aida.INVOICE_LINE_NUMBER=aila.LINE_NUMBER
AND aia.INVOICE_ID=aila.INVOICE_ID
AND aia.invoice_amount<>0
AND AIA.CREATION_DATE > '01-APR-2015'
AND NVL(ap_invoices_utility_pkg.get_prepay_amount_remaining(
aia.invoice_id ),0)>0
AND AIA.ORG_ID=:P_ORG_ID
AND aia.invoice_id IN (
SELECT invoice_id
FROM ap_payment_schedules_all
WHERE checkrun_id IS NULL
)
group by
hou.NAME
,aia.invoice_id
,aia.INVOICE_TYPE_LOOKUP_CODE
,aia.INVOICE_NUM
,aia.INVOICE_CURRENCY_CODE
,aia.invoice_amount
,aia.INVOICE_DATE
, aia.VENDOR_ID
, aia.VENDOR_ID
,aia.VENDOR_SITE_ID
,aia.DESCRIPTION
,aila.LINE_NUMBER
,aiDA.LINE_TYPE_LOOKUP_CODE
,GCC.CONCATENATED_SEGMENTS
ORDER BY 1