Open purchase order query in oracle apps

Open purchase order query in oracle apps

In this post, I am sharing the script to get the open purchase order data in Oracle apps. We will get the complete information of the Open Purchase order using this script. Open Purchase Order means those purchase orders for which we have not received the material or we have received the material but we have not billed again the purchase order. Here below is the  detail Open purchase order query in oracle apps.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 ph.closed_code = 'OPEN'

    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