Thursday, 4 August 2011

List of Open PO

SELECT     pha.segment1 "PO_NO",
               pha.creation_date "PO_CREATION_DATE",
               aps.segment1 "VENDOR_CODE",
               msib.segment1 "MATERIAL_CODE",
               msib.description "MATERIAL_DESCRIPTION",
               plla.need_by_date,
               pla.unit_meas_lookup_code "UOM",
               plla.quantity "PO_QTY",
                SUM(plla.quantity_received) "RECEIVED_QTY",
                   SUM(plla.quantity_accepted) "ACCEPTED_QTY",
                   SUM(plla.quantity_rejected) "REJECTED_QTY",
              (plla.quantity-plla.quantity_received) "BALANCE_QTY_TO_BE_RECEIVED"
FROM      po_headers_all pha,
              po_lines_all pla,
              po_line_locations_all plla,
              po_distributions_all pda,
              mtl_system_items_b msib,
              ap_suppliers aps
WHERE pha.vendor_id=aps.vendor_id
AND     pha.po_header_id=pla.po_header_id
AND     pla.po_line_id=plla.po_line_id
AND     pla.item_id=msib.inventory_item_id
AND     pha.org_id=:ORGANIZATION
AND     TRUNC (pha.creation_date) BETWEEN NVL (:FROM_RECEIPT_DATE,TO_DATE
         (pha.creation_date,'DD-MON-RR'))AND NVL (:TO_RECEIPT_DATE,TO_DATE
         (pha.creation_date,'DD-MON-RR'))                    
AND     pha.vendor_id=:VENDOR
AND     pha.segment1='7015'
GROUP BY    pha.segment1,
                  pha.creation_date,
                  aps.segment1,
                  msib.segment1,
                  msib.description,
                  plla.quantity,
                  plla.need_by_date,
                  pla.unit_meas_lookup_code,
                  plla.quantity,
                  plla.quantity_received,
                  plla.quantity_accepted,
                  plla.quantity_rejected,
                  plla.quantity,
                  plla.quantity_received
Posted by at

2 comments:

  1. sachin ahuja6 June 2013 at 02:35

    Where is msib organization id being joined?
    This is wrong.

    ReplyDelete
  2. Unknown10 October 2018 at 23:07

    SELECT pha.segment1 "PO_NO",
    pha.creation_date "PO_CREATION_DATE",
    aps.segment1 "VENDOR_CODE",
    aps.vendor_name,
    msib.segment1 "MATERIAL_CODE",
    msib.description "MATERIAL_DESCRIPTION",
    pha.currency_code,
    pha.authorization_status,
    plla.need_by_date,
    pla.unit_meas_lookup_code "UOM",
    plla.quantity "PO_QTY",
    SUM(plla.quantity_received) "RECEIVED_QTY",
    SUM(plla.quantity_accepted) "ACCEPTED_QTY",
    SUM(plla.quantity_rejected) "REJECTED_QTY",
    SUM(plla.quantity_cancelled) "CANCELLED_QTY",
    (plla.quantity-plla.quantity_received) "BALANCE_QTY_TO_BE_RECEIVED",
    (select organization_code from mtl_parameters where organization_id = plla.ship_to_organization_id) as TO_WAREHOUSE
    FROM po_headers_all pha,
    po_lines_all pla,
    po_line_locations_all plla,
    po_distributions_all pda,
    (select inventory_item_id, segment1, description from mtl_system_items_b where organization_id=102) msib,
    ap_suppliers aps
    WHERE pha.vendor_id = aps.vendor_id
    AND pla.po_header_id = pha.po_header_id
    AND plla.po_header_id(+) = pla.po_header_id
    AND plla.po_line_id(+) = pla.po_line_id
    AND pla.item_id = msib.inventory_item_id
    and pda.po_header_id(+) = pla.po_header_id
    and pda.po_line_id(+) = pla.po_line_id
    AND pha.org_id = :p_org_id
    AND TRUNC(pha.creation_date) BETWEEN NVL(:FROM_PO_DATE,TO_DATE(pha.creation_date,'DD-MON-RR'))
    AND NVL(:TO_PO_DATE,TO_DATE(pha.creation_date,'DD-MON-RR'))
    --AND pha.vendor_id like :VENDOR
    --AND pha.segment1 like '%'
    and (plla.quantity-(plla.quantity_received+plla.quantity_rejected+plla.quantity_cancelled)) > 0
    GROUP BY pha.segment1,
    pha.creation_date,
    aps.segment1,
    aps.vendor_name,
    msib.segment1,
    msib.description,
    pha.currency_code,
    pha.authorization_status,
    plla.quantity,
    plla.need_by_date,
    pla.unit_meas_lookup_code,
    plla.quantity,
    plla.quantity_received,
    plla.quantity_accepted,
    plla.quantity_rejected,
    plla.quantity,
    plla.quantity_received,
    plla.ship_to_organization_id


    ------ corrected sql ----------

    ReplyDelete

Subscribe to: Post Comments (Atom)