Prepayment query in oracle apps

Prepayment query in oracle apps

In this post , We will discuss abut Prepayment query in oracle apps. 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 query in oracle apps which helps to list all the prepayment given to the suppliers.

 

 

Example of Prepayment query in oracle apps

SELECT hou.NAME

,aia.INVOICE_NUM Invoice_Number

,aia.INVOICE_TYPE_LOOKUP_CODE

,aia.INVOICE_CURRENCY_CODE Invoice_Currency

,aia.invoice_amount Invoice_Amount -- *

,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

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