Purchase order detail report query

Purchase order detail report query

In this post, I am sharing the Purchase order detail report query. We will get the complete information of the Purchase order using this. I am also sharing the 11 important tables uses in the SQL query to fetch the Purchase order detail report. Here below is the  detail Purchase order detail report query.

 

 

11 Important Tables Involved to get Open purchase order Data in oracle apps

po_headers_all

po_lines_all

po_distributions_all

po_vendors

po_vendor_sites_all

po_distributions_all

po_req_distributions_all

po_requisition_lines_all

po_requisition_headers_all

mtl_system_items_b

po_lookup_codes

 

 

Detail Open purchase order query in oracle apps

SELECT ph.segment1 po_num, ph.creation_date, hou.NAME "Operating Unit",

        ppx.full_name "Buyer Name", ph.type_lookup_code "PO Type",

        plc.displayed_field "PO Status", ph.comments, pl.line_num,

        plt.order_type_lookup_code "Line Type", msi.segment1 "Item Code",

        pl.item_description, pl.unit_meas_lookup_code "UOM",

        pl.base_unit_price, pl.unit_price, pl.quantity,

        ood.organization_code "Shipment Org Code",

        ood.organization_name "Shipment Org Name", pv.vendor_name supplier,

        pvs.vendor_site_code, (pl.unit_price * pl.quantity) "Line Amount",

        prh.segment1 req_num, prh.type_lookup_code req_method,

        ppx1.full_name "Requisition requestor"

   FROM po_headers_all ph,

        po_lines_all pl,

        po_distributions_all pda,

        po_vendors pv,

        po_vendor_sites_all pvs,

        po_distributions_all pd,

        po_req_distributions_all prd,

        po_requisition_lines_all prl,

        po_requisition_headers_all prh,

        hr_operating_units hou,

        per_people_x ppx,

        mtl_system_items_b msi,

        po_line_types_b plt,

        org_organization_definitions ood,

        per_people_x ppx1,

        po_lookup_codes plc

  WHERE 1 = 1

    AND ph.vendor_id = pv.vendor_id

    AND PH.ORG_ID=:P_ORG_ID

    AND ph.po_header_id = pl.po_header_id

    AND ph.vendor_site_id = pvs.vendor_site_id

    AND ph.po_header_id = pd.po_header_id

    AND pl.po_line_id = pd.po_line_id

    AND pd.req_distribution_id = prd.distribution_id(+)

    AND prd.requisition_line_id = prl.requisition_line_id(+)

    AND prl.requisition_header_id = prh.requisition_header_id(+)

    AND hou.organization_id = ph.org_id

    AND ph.agent_id = ppx.person_id

    AND pda.po_header_id = ph.po_header_id

    AND pda.po_line_id = pl.po_line_id

    AND pl.line_type_id = plt.line_type_id

    AND ood.organization_id = pda.destination_organization_id

    AND ppx1.person_id(+) = prh.preparer_id

    AND pda.destination_organization_id = msi.organization_id(+)

    AND msi.inventory_item_id = NVL (pl.item_id, msi.inventory_item_id)

    AND plc.lookup_type = 'DOCUMENT STATE'

    AND plc.lookup_code = ph.closed_code