Tuesday, April 29, 2014

Query to get Requisitions for which there is no PO

SELECT prh.segment1 ReqNum,
           prl.line_num lineNum,
           NVL(msi.segment1, prl.item_description) Item,
           prl.Quantity Qty,
           prl.suggested_vendor_name SuggSuppName,
           prl.need_by_date NeedDate,
           prh.authorization_status Status,
           pap.full_name Requestor
  FROM po_requisition_headers_all prh,
          per_all_people_f pap,
          po_requisition_lines_all prl,
          po_req_distributions_all prd,
          mtl_system_items msi
WHERE prh.preparer_id=pap.person_id
    AND prl.requisition_header_id = prh.requisition_header_id
    AND prd.requisition_line_id= prl.requisition_line_id
    AND msi.inventory_item_id (+) = prl.item_id
    AND msi.organization_id (+) = prl.destination_organization_id
    AND NOT EXISTS ( SELECT 1 
                                 FROM po_distributions_all pd
                               WHERE pd.req_distribution_id = prd.distribution_id)
  ORDER BY 1, 2

No comments:

Post a Comment