Oracle EBS SQL Scripts for data extracts

 SQL Script to display the Supplier Invoices are fully matched with Purchase Order(PO) and Requisition(REQ)  along with Bill-To & Ship-To details:-

SELECT 
        DISTINCT 
prha.segment1              AS requisition_number ,
        pha.segment1                                         AS po_number          ,
        aia.invoice_num                                   AS Invoice_Number     ,
        aia.invoice_date                                   AS Invoice_Date       ,
        aia.description          AS Invoice_Description,
        aia.invoice_amount                             AS Invoice_Amount     ,
        aia.gl_date                 AS Invoice_GL_Date    ,
        pv.vendor_id                                        AS Supplier_ID        ,
        pv.vendor_name                                 AS Supplier_Name      ,
        pvs.vendor_site_code                       AS Supplier_Site      ,
        pv.segment1                                         AS Supplier_Number    ,
        hl_ship.location_code                        AS ship_to_location   ,
        hl_ship.address_line_1                      AS ship_to_address    ,
        hl_bill.location_code                         AS bill_to_location   ,
        hl_bill.address_line_1                       AS bill_to_address
FROM 
        ap_invoices_all aia
JOIN 
        ap_invoice_lines_all aila ON aia.invoice_id = aila.invoice_id
JOIN 
        ap_invoice_distributions_all aida ON aia.invoice_id = aida.invoice_id 
        AND aila.line_number = aida.invoice_line_number
JOIN 
        po_distributions_all pda ON aida.po_distribution_id = pda.po_distribution_id
JOIN 
        po_headers_all pha ON pda.po_header_id = pha.po_header_id
JOIN 
        po_req_distributions_all prda ON pda.req_distribution_id = prda.distribution_id
JOIN 
        po_requisition_lines_all prla ON prda.requisition_line_id = prla.requisition_line_id
JOIN 
        po_requisition_headers_all prha ON prla.requisition_header_id = prha.requisition_header_id
JOIN 
        po_vendors pv ON aia.vendor_id = pv.vendor_id
JOIN 
        po_vendor_sites_all pvs ON aia.vendor_site_id = pvs.vendor_site_id
JOIN 
        hr_locations_all hl_ship ON pha.ship_to_location_id = hl_ship.location_id
JOIN 
        hr_locations_all hl_bill ON pha.bill_to_location_id = hl_bill.location_id
WHERE 
        1=1 --aida.match_status_flag = 'A' 
        AND aia.cancelled_date IS NULL -- Exclude Cancelled Invoices
        AND aia.invoice_amount <> 0 -- Exclude Zero Amount Invoices
        AND aia.org_id = &ORG_ID                   -- Filter by specific Org
        --AND aia.invoice_date >= '01-JAN-2025'    -- Specific Date filter
        AND aia.invoice_date BETWEEN '01-MAY-2020' AND '31-DEC-2024'
ORDER BY 

        aia.invoice_date DESC;