Tech Trainings

Query to get AP Invoice headers , lines and distribution details

April 19, 2023 by admin

--Headers 

SELECT * FROM ap_invoices_all

--Lines 

SELECT * FROM ap_invoice_lines_all

-- Distributions 

SELECT * FROM ap_invoice_distributions_all 

--AP Invoice Header , Lines and Distributions 
SELECT
    hou.name         bu_name,
    aia.invoice_num,
    hp.party_name,
    hp.party_number,
    pos.segment1     supplier_num,
    pssam.vendor_site_code,
    aia.invoice_type_lookup_code,
    aila.line_number,
    aila.description line_desc,
    aila.amount,
    aila.base_amount fun_base_amount,
    aida.distribution_line_number,
    aida.amount      dist_amount,
    aila.cancelled_flag,
    aila.accounting_date
FROM
    ap_invoices_all              aia,
    ap_invoice_lines_all         aila,
    ap_invoice_distributions_all aida,
    hr_operating_units           hou,
    poz_suppliers                pos,
    hz_parties                   hp,
    poz_supplier_sites_all_m     pssam
WHERE
        1 = 1
    AND aia.invoice_id = aila.invoice_id
    AND aida.invoice_id = aila.invoice_id
    AND aida.invoice_line_number = aila.line_number
    AND aia.org_id = hou.organization_id
    AND pos.party_id = hp.party_id
    AND pos.vendor_id = aia.vendor_id
    AND aia.vendor_site_id = pssam.vendor_site_id
ORDER BY
    aia.invoice_id

Comments |0|