Query to find open invoices with the Supplier and PO details

Introduction

This Post is about Query to find open invoices with the Supplier and PO details  in Oracle EBS R12.

 

Script to find open invoices with the Supplier and PO details.

SELECT   i.invoice_num “Invoice Number”,

(SELECT MAX (pha.segment1) po_number

FROM apps.ap_invoices_all aia,

apps.ap_invoice_lines_all aila,

apps.ap_invoice_distributions_all aida,

apps.po_headers_all pha,

apps.po_lines_all pla,

apps.po_distributions_all pda

WHERE pha.po_header_id = pla.po_header_id

AND pla.po_line_id = pda.po_line_id

AND aida.po_distribution_id = pda.po_distribution_id

AND aida.invoice_id = aia.invoice_id

AND aia.invoice_id = aila.invoice_id

AND aia.invoice_id = i.invoice_id

AND aila.line_number = ail.line_number

AND aia.vendor_id = i.vendor_id) “PO Number”,

v.segment1 “Supplier Number”, v.vendor_name “Supplier Name”,

vs.vendor_site_code “Supplier Site”, i.invoice_date “Invoice Date”,

i.description “Invoice Description”,

ail.description “Invoice Line Description”, SUM (ail.amount)

“Amount”,

DECODE (i.cancelled_date, NULL, ‘NO’, ‘YES’) “Cancel Status”

FROM po_vendors v,

po_vendor_sites_all vs,

ap_invoices_all i,

apps.ap_invoice_lines_all ail

WHERE v.vendor_id = vs.vendor_id

AND i.invoice_id = ail.invoice_id

AND i.vendor_id = v.vendor_id

AND i.vendor_site_id = vs.vendor_site_id

–and     i.invoice_num = ‘10190183’

AND i.invoice_date BETWEEN ’01-JAN-2015′ AND ’31-DEC-2015′

AND EXISTS (

SELECT 1

FROM apps.ap_invoice_distributions_all d

WHERE d.invoice_id = i.invoice_id

AND d.match_status_flag = ‘A’)

GROUP BY v.vendor_name,

vs.vendor_site_code,

i.invoice_id,

i.invoice_num,

i.invoice_date,

i.description,

ail.description,

ail.line_number,

i.vendor_id,

v.segment1,

i.cancelled_date

ORDER BY v.vendor_name, i.invoice_num;

 

What we expect in the scrip

This script helps us to comprehend how to find open invoices with the Supplier and PO details. Couple of tables which is being used in the query are po_vendors v,po_vendor_sites_all vs,ap_invoices_all ,apps.ap_invoice_lines_all ail etc.

Summary

This Post described the script find open invoices with the Supplier and PO details in Oracle EBS R12.

 

Got any queries?

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.