Oracle Apps R12 – PO Summary Query (Complete Guide)

 

Introduction

In Oracle E-Business Suite R12, Purchase Orders (PO) are a critical part of the Procure-to-Pay (P2P) cycle. A PO Summary Query is commonly used by developers, functional consultants, and analysts to extract consolidated information such as PO number, supplier, amount, status, and receipt details.

This blog explains:

• Key tables involved in PO Summary
• Relationships between tables
• Sample SQL query
• Column-level explanation

--------------------------------------------------------------------------------------------------

SELECT

    org_code,

    po_no,

    po_date,

    po_status,

    paid_amount,

    currency,

    curr_conv,

    supplier_name,

    supplier_site,

    origin_of_vendor,

    payment_term,

    po_header_id,

    po_basic_amt,

    ( po_basic_amt * nvl(curr_conv, 1) )                               po_basic_amt_inr,

    po_descr,

    item_code,

    ( tax_amt ),

    ( ( ( po_basic_amt * nvl(curr_conv, 1) ) ) + ( tax_amt ) )            total_amount

    ,tax_category

FROM

    (

        SELECT

            hou.name                                                             AS operating_unit,

            (

                SELECT

                    organization_code

                FROM

                    apps.org_organization_definitions

                WHERE

                        1 = 1

                    AND organization_id = pod.destination_organization_id

            )                                                                    org_code,

            pha.segment1                                                         AS po_no,

            to_char(pha.creation_date, 'DD-MM-YYYY')                             AS po_date,

            pha.authorization_status                                             AS po_status,

            SUM(pod.amount_billed)                                               AS paid_amount,

            pha.currency_code                                                    AS currency,

            pha.rate                                                             AS curr_conv,

            pv.vendor_name                                                       AS supplier_name,

            pvs.vendor_site_code                                                 AS supplier_site,

            ftv.territory_short_name                                             AS origin_of_vendor,

            apt.name                                                             AS payment_term,

            pla.po_header_id,

            SUM(round(nvl(pla.unit_price * pla.quantity, 0), 2)) po_basic_amt,

            MAX(pla.item_description)                                            po_descr,

            MAX(msib.segment1)                                                   item_code,

            nvl(SUM(jtl.unround_tax_amt_tax_curr), 0)                           AS tax_amt

           , max(jct.tax_category_name)  AS tax_category

        FROM

            apps.po_headers_all           pha,

               --  APPS.po_line_locations_all plla,

            apps.po_distributions_all     pod,

            apps.hr_operating_units       hou,

            apps.ap_suppliers             pv,

            apps.ap_supplier_sites_all    pvs,

            apps.fnd_territories_vl       ftv,

            apps.ap_terms                 apt,

            apps.po_lines_all             pla,

            apps.mtl_system_items_b       msib,

            apps.jai_tax_lines_all        jtl,

              apps.jai_tax_categories     jct,

                    apps.jai_tax_det_factors    jtd

        WHERE

                1 = 1

            AND pod.po_header_id = pha.po_header_id

            AND pod.org_id = pha.org_id

            AND hou.organization_id = pha.org_id

            AND pha.vendor_id = pv.vendor_id

            AND pha.vendor_site_id = pvs.vendor_site_id

            AND pv.vendor_id = pvs.vendor_id

            AND pha.org_id = pvs.org_id

            AND pvs.country = ftv.territory_code

            AND pha.terms_id = apt.term_id

            AND pla.po_header_id = pha.po_header_id

            AND pla.org_id = pha.org_id

            AND pla.item_id = msib.inventory_item_id

            AND pod.destination_organization_id = msib.organization_id

            AND  jtl.trx_number= pha.segment1 

            AND jtl.org_id = pha.org_id

            and jtl.ENTITY_CODE = 'PURCHASE_ORDER'

             AND jtl.trx_line_id = jtd.trx_line_id (+)

                    AND pha.segment1 = jtl.trx_number

                    AND jtl.org_id = pha.org_id

                    AND jtl.trx_id = jtd.trx_id

                    AND jtd.entity_code = 'PURCHASE_ORDER'

                    AND jtd.override_tax_category_id = jct.tax_category_id (+)

                    AND jct.org_id = jtd.org_id

                    AND ( jct.effective_to IS NULL

                          OR jct.effective_to >= sysdate )

                    AND jtl.org_id = jtd.org_id

                 AND (   :p_from_date IS NULL  OR trunc(pha.creation_date) >= trunc(:p_from_date))

                 AND ( :p_to_date IS NULL OR trunc(pha.creation_date) <= trunc(:p_to_date))

            AND hou.organization_id = :p_org_code

        GROUP BY

            pha.segment1,

            pha.org_id,

            hou.name,

            pha.authorization_status,

            to_char(pha.creation_date, 'DD-MM-YYYY'),

            pha.currency_code,

            pod.destination_organization_id,

            pha.rate,

            pv.vendor_name,

            pvs.vendor_site_code,

            ftv.territory_short_name,

            apt.name,

            pla.po_header_id

         ,jtl.trx_number,

            jtl.org_id

        ORDER BY

            pha.segment1,

            pv.vendor_name,

            pvs.vendor_site_code