Script to extract Goods Received Date for Suppliers along with Invoice and PO Information

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