select poh.segment1 po_number,
pol.line_num,
pol.item_description,
ploc.quantity,
mtl.segment1||','||mtl.segment2 po_item, prh.segment1 req_num,
fnd.user_name requestor, prl.need_by_date
from po_line_locations_all ploc,
po_lines_all pol,
po_headers_all poh,
mtl_system_items_b mtl,
po_requisition_lines_all prl,
po_requisition_headers_all prh,
fnd_user fnd
where poh.po_header_id = pol.po_header_id
and pol.po_line_id = ploc.po_line_id
and pol.item_id = mtl.inventory_item_id(+)
and ploc.line_location_id = prl.line_location_id
and prl.requisition_header_id = prh.requisition_header_id
and prh.preparer_id = fnd.employee_id
and mtl.organization_id(+) = <your_org>
1)PO_DISTRIBUTIONS_ALL (PO_HEADER_ID, REQ_DISTRIBUTION_ID)
2)PO_HEADERS_ALL (PO_HEADER_ID, SEGMENT1)
3)PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID, REQUISITION_LINE_ID)
4)PO_REQUISITION_LINES_ALL (REQUISITION_LINE_ID)
5)PO_REQQUISITION_HEADERS_ALL (REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1)
Make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and
PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
Join with
po_distributions.req_distribution_id = po_req_distributions.distribution_id (+)