Important queries for Purchase Order

execute fnd_client_info.set_org_context('204');

SELECT   DECODE (por.release_num,
                   NULL, poh.segment1,
                   poh.segment1 || '-' || por.release_num)
              PO_Number_Release,
           pol.line_num Line,
           pov.vendor_name Vendor,
           pol.item_revision Rev,
           pol.item_description Description,
           pll.shipment_num,
           pod.distribution_num Distribution,
           DECODE (plt.order_type_lookup_code,
                   'AMOUNT', NULL,
                   pll.price_override)
              Unit_Price,
           pll.promised_date Promised_Date,
           pol.unit_meas_lookup_code Unit,
           DECODE (POL.order_type_lookup_code,
                   'RATE', POD.amount_ordered,
                   'FIXED PRICE', POD.amount_ordered,
                   POD.quantity_ordered)
              Quantity_Amount_Ordered,
           DECODE (POL.order_type_lookup_code,
                   'RATE', POD.amount_billed,
                   'FIXED PRICE', POD.amount_billed,
                   POD.quantity_billed)
              Quantity_Amount_Billed,
           DECODE (POL.order_type_lookup_code,
                   'RATE', POD.amount_delivered,
                   'FIXED PRICE', POD.amount_delivered,
                   POD.quantity_delivered)
              Qty_Amount_Delivered,
           DECODE (
              POL.order_type_lookup_code,
              'RATE',
              (NVL (POD.amount_ordered, 0) - NVL (POD.amount_billed, 0))
              / DECODE (NVL (POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
              'FIXED PRICE',
              (NVL (POD.amount_ordered, 0) - NVL (POD.amount_billed, 0))
              / DECODE (NVL (POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
              (NVL (POD.quantity_ordered, 0) - NVL (POD.quantity_billed, 0))
              / DECODE (NVL (POD.quantity_ordered, 0),
                        0, 1,
                        POD.quantity_ordered)
           )
           * 100
              Percent_Unbilled,
           DECODE (
              POL.order_type_lookup_code,
              'RATE',
                POD.amount_ordered
              - NVL (POD.amount_cancelled, 0)
              - NVL (POD.amount_billed, 0),
              'FIXED PRICE',
                POD.amount_ordered
              - NVL (POD.amount_cancelled, 0)
              - NVL (POD.amount_billed, 0),
              (  POD.quantity_ordered
               - NVL (POD.quantity_cancelled, 0)
               - NVL (POD.quantity_billed, 0))
              * PLL.price_override
           )
              C_AMOUNT_OPEN_INV,
           poh.po_header_id,
           pol.po_line_id,
           por.release_num,
           poh.currency_code C_CURRENCY,
           NVL (por.po_release_id, -1) release_id
    FROM   po_distributions pod,
           mtl_system_items msi,
           po_line_locations pll,
           po_lines pol,
           po_releases por,
           po_headers poh,
           po_vendors pov,
           financials_system_parameters fsp,
           po_line_types plt
   WHERE       poh.segment1 = '804'
           AND poh.po_header_id = pol.po_header_id
           AND pol.po_line_id = pll.po_line_id
           AND pll.line_location_id = pod.line_location_id
           AND pol.item_id = msi.inventory_item_id(+)
           AND msi.organization_id = fsp.inventory_organization_id
           AND poh.vendor_id = pov.vendor_id(+)
           AND pll.po_release_id = por.po_release_id(+)
           AND pol.line_type_id = plt.line_type_id
           AND pll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
           AND NVL (pol.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (pll.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (poh.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (por.closed_code, 'OPEN') NOT IN
                    ('CLOSED', 'FINALLY CLOSED')
           AND NVL (poh.cancel_flag, 'N') = 'N'
           AND NVL (por.cancel_flag, 'N') = 'N'
           AND NVL (pol.cancel_flag, 'N') = 'N'
           AND NVL (pll.cancel_flag, 'N') = 'N'
ORDER BY   pll.line_location_id
/

Query to find receipts against a PO shipment line :

/* Formatted on 1/13/2015 2:24:43 PM (QP5 v5.115.810.9015) */
SELECT   pol.po_header_id,
           pol.po_line_id,
           pll.line_location_id,
           pll.quantity,
           rsh.shipment_header_id,
           rsh.receipt_source_code,
           rsh.vendor_id,
           rsh.vendor_site_id,
           rsh.organization_id,
           rsh.shipment_num,
           rsh.receipt_num,
           rsh.ship_to_location_id,
           rsh.bill_of_lading,
           rsl.shipment_line_id,
           rsl.QUANTITY_SHIPPED,
           rsl.QUANTITY_RECEIVED,
           rct.transaction_type,
           rct.transaction_id,
           DECODE (pol.order_type_lookup_code,
                   'RATE', NVL (rct.amount, 0),
                   'FIXED PRICE', NVL (rct.amount, 0),
                   NVL (rct.source_doc_quantity, 0))
              transaction_qty
    FROM   rcv_transactions rct,
           rcv_shipment_headers rsh,
           rcv_shipment_lines rsl,
           po_lines pol,
           po_line_locations pll
   WHERE       rct.po_line_location_id = 28302
           AND rct.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
                    ('RATE', 'FIXED PRICE')
           AND rct.shipment_line_id = rsl.shipment_line_id
           AND rsl.shipment_header_id = rsh.shipment_header_id
ORDER BY   rct.transaction_id
/

Query to find PO returns :

/* Formatted on 1/13/2015 2:24:58 PM (QP5 v5.115.810.9015) */
SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM ( (NVL (rct.source_doc_quantity, 0))) Qty_returned
    FROM   rcv_transactions rct, po_lines pol, po_line_locations pll
   WHERE       rct.transaction_type = 'RETURN TO VENDOR'
           AND rct.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id
UNION ALL
  SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM ( (NVL (rct.amount, 0))) Qty_returned
    FROM   rcv_transactions rct, po_lines pol, po_line_locations pll
   WHERE       rct.transaction_type = 'RETURN TO VENDOR'
           AND rct.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id
/

Query to find PO corrections :

/* Formatted on 1/13/2015 2:26:28 PM (QP5 v5.115.810.9015) */
SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM (NVL (rct1.source_doc_quantity, 0)) Qty_corrected
    FROM   rcv_transactions rct,
           rcv_transactions rct1,
           po_lines pol,
           po_line_locations pll
   WHERE       rct.transaction_type IN ('RECEIVE', 'MATCH')
           AND rct.po_line_location_id = pll.line_location_id
           AND rct1.transaction_type = 'CORRECT'
           AND rct1.parent_transaction_id = rct.transaction_id
           AND rct1.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id
UNION ALL
  SELECT   pol.po_header_id,
           pol.po_line_id,
           rct.po_line_location_id Line_location_id,
           SUM (NVL (rct1.amount, 0)) Qty_corrected
    FROM   rcv_transactions rct,
           rcv_transactions rct1,
           po_lines pol,
           po_line_locations pll
   WHERE       rct.transaction_type IN ('RECEIVE', 'MATCH')
           AND rct.po_line_location_id = pll.line_location_id
           AND rct1.transaction_type = 'CORRECT'
           AND rct1.parent_transaction_id = rct.transaction_id
           AND rct1.po_line_location_id = pll.line_location_id
           AND rct.po_line_id = pol.po_line_id
           AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN
                    ('RATE', 'FIXED PRICE')
GROUP BY   pol.po_header_id, pol.po_line_id, rct.po_line_location_id