Introduction
This Post illustrates the steps required to extract Goods Received Date for Suppliers along with Invoice and PO Information in Oracle EBS R12.
Query
select (select hou.name from apps.hr_operating_units hou
where organization_id = (select org_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only)) “Entity_OU”,
TO_CHAR(ap.creation_date,’DD-MON-YYYY’) “Vendor_Creation_Date”,
fu.user_name “Vendor_Created_by”,
ap.VENDOR_NAME “Vendor_Name”,
ap.SEGMENT1 “Vendor_Num”,
(CASE
WHEN ap.END_DATE_ACTIVE is NULL
THEN ‘Active’
WHEN ap.END_DATE_ACTIVE is NOT NULL AND trunc(ap.END_DATE_ACTIVE) <= TRUNC(SYSDATE)
THEN ‘Inactive’
ELSE ‘Inactive’
END) “Vendor_Status”,
(select vendor_site_code from apps.ap_supplier_sites_all
where vendor_site_id = (select vendor_site_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only)) “Vendor_Site”,
(select to_char(invoice_date,’DD-MON-YYYY’) from apps.ap_invoices_all
where vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) “First_Invoice_Date”,
(select user_name from apps.fnd_user
where user_id = (select created_by from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only)) “Invoice_Created_By”,
(select invoice_num from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) “Invoice_Number”,
(select invoice_amount from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) “Invoice_Amount”,
(select INVOICE_CURRENCY_CODE from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) “Invoice_Currency”,
(select DESCRIPTION from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) “Invoice_Description”,
(select AP_INVOICES_PKG.GET_APPROVAL_STATUS(apa.invoice_id,
apa.invoice_amount,
apa.payment_status_flag,
apa.invoice_type_lookup_code) from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) “Invoice_Status”,
(SELECT distinct to_char(pha.creation_date, ‘DD_MON-YYYY’)
FROM po_headers_all pha
,po_distributions_all pda
,ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) ) “PO_Creation_Date”,
(SELECT distinct to_char(pha.APPROVED_DATE, ‘DD_MON-YYYY’)
FROM po_headers_all pha
,po_distributions_all pda
,ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) ) “PO_Approval_Date”,
( select user_name from apps.fnd_user where user_id = (SELECT distinct pha.created_by
FROM apps.po_headers_all pha
,apps.po_distributions_all pda
,apps.ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) )) “PO_Created_By”,
(SELECT distinct pha.segment1
FROM apps.po_headers_all pha
,apps.po_distributions_all pda
,apps.ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) ) “PO_Number”,
( select sum(NVL(unit_price,0) * NVL(quantity,0)) from apps.po_lines_all pla
where po_header_id = (SELECT distinct pha.po_header_id
FROM apps.po_headers_all pha
,apps.po_distributions_all pda
,apps.ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) )) “PO_Amount”,
(SELECT distinct pha.CURRENCY_CODE
FROM apps.po_headers_all pha
,apps.po_distributions_all pda
,apps.ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) ) “PO_Currency”,
(SELECT distinct pha.COMMENTS
FROM apps.po_headers_all pha
,apps.po_distributions_all pda
,apps.ap_invoice_distributions_all aid
WHERE pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aid.invoice_id= (select invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id
order by creation_date
fetch first 1 row only) ) “PO_Description”
from apps.ap_suppliers ap,
apps.fnd_user fu
where fu.user_id = ap.created_by
What we expect in the script.
This script helps us to comprehend how to extract Goods Received Date for Suppliers along with Invoice and PO Information. Couple of tables which is being used in the scripts are po_headers_all pha,po_distributions_all pda, ap_invoice_distributions_all aid etc.
Summary
This Post described the script extract Goods Received Date for Suppliers along with Invoice and PO Information 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.
