Prepayment tables in oracle payables r12

Prepayment tables in oracle payables r12

In this post , We will Discuss about Prepayment tables in oracle payables r12. Prepayment is the advance given to the Supplier before receiving his Final set of Service and the Billed Invoice so when we got supplier Final Invoice then we applied the advance/ Prepayment given to the supplier and then do rest of the left payment to the supplier. In this post , we will Discuss about Prepayment tables in oracle payables r12.

 

 

2 important Prepayment tables in oracle payables r12 

1.AP_INVOICES_ALL

2.AP_INVOICE_LINES_ALL

 

 

Sql query using Prepayment tables in oracle payables r12

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