Query to get PO Total amount in Oracle Apps

Query to get PO Total amount in Oracle Apps

Hi friends, We are going to discuss about the Query to get PO Total amount in Oracle Apps. We are sharing the complete sql query , which helps to extract the list of PO with PO Total Amount in Oracle apps. Using this query , we can find out the total purchase order amount for all PO's in Oracle apps. One of the major limitation's in PO tables in oracle apps is this , there is no column for the PO total Amount , we need to calculate this amount based on the calculation from PO lines tables. We will be share the two important sql queries , which helps to extract the PO details with PO total Amount in oracle apps. Please find below below the complete sql query to get the PO total amount in Oracle Apps.

Query to get PO Total amount in Oracle Apps

2 Important SQL queries to get PO total Amount

We will be share the two types of sql queries to extract the Po total Amount in Oracle Apps.

1. Summary Level Query to Get PO total Amount.

2.Detail Level Query to Get PO total Amount.

Summary Level Query to Get PO total Amount in Oracle Apps.

SELECT pha.segment1 "PO NUMBER",ph.creation_date, 

SUM (NVL((pla.unit_price * pla.quantity),0)) "PO Total Amount"

    FROM po_lines_all pla, po_headers_all pha

   WHERE pla.po_header_id = pha.po_header_id

  AND pha.type_lookup_code ='STANDARD'

 AND PHA.ORG_ID=:P_ORG_ID

Group By pha.segment1,ph.creation_date

order by pha.segment1

Detail Level Query to Get PO total Amount in Oracle Apps.

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

      (SELECT SUM (NVL((plaa1.unit_price * plaa1.quantity),0)) "PO Total Amount"

    FROM po_lines_all plaa1, po_headers_all phaa2

   WHERE plaa1.po_header_id = phaa2.po_header_id

   and phaa2.po_header_id=ph.po_header_id

    and plaa1.po_line_id=pl.po_line_id) "PO TOTAL AMOUNT",

        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

Query to get PO Total amount in Oracle Apps