Friday 31 August 2018

Prepayment invoice query in oracle apps

Prepayment invoice query in oracle apps

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

Example of Prepayment invoice 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

5 comments:

Anonymous said...

Nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training

Rifath said...

Splendid blog I visit this blog it's unimaginably great. Inquisitively, in this blog substance shaped without a doubt and reasonable. The substance of data is useful.
Oracle Fusion HCM Online Training
Oracle Fusion Financials Online Training

Anonymous said...

Good Blog Thanks For Sharing this informative airtrl/
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Rifath said...

Brilliant blog I visit this blog it's incredibly awesome. Curiously, in this blog content formed doubtlessly and sensible. The substance of information is helpful.
Oracle Fusion HCM Online Training

Rainbow Training Institute said...

Thank you for sharing such a nice and interesting blog.

Spark and Scala Online Training
Spark Scala Training
Hyderabad

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *