Blog / Oracle Application Blog
Query to find open invoices with the Supplier and PO details
Query
to find open invoices with the Supplier and PO details
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”,
Name”,
vs.vendor_site_code “Supplier Site”, i.invoice_date “Invoice
Date”,
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_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;
By
Sivachandaran S
