隨手筆記

select prh.segment1 "PR NUM",
       trunc(prh.creation_date) "PO Creation Date",
       trunc(prl.creation_date) "Line Creation Date",
       prl.line_num "Seq #",
       msi.segment1 "Item Num",
       prl.item_description "Description",
       prl.quantity "Qty",
       trunc(prl.need_by_date) "Required By",
       ppf1.full_name "REQUESTOR",
       ppf2.agent_name "BUYER"
  from po.po_requisition_headers_all prh,
       po.po_requisition_lines_all prl,
       apps.per_people_f ppf1,
       (select distinct agent_id, agent_name from apps.po_agents_v) ppf2,
       po.po_req_distributions_all prd,
       inv.mtl_system_items_b msi,
       po.po_line_locations_all pll,
       po.po_lines_all pl,
       po.po_headers_all ph
 Where prh.requisition_header_id = prl.requisition_header_id
   and prl.requisition_line_id = prd.requisition_line_id
   and ppf1.person_id = prh.preparer_id
   and prh.creation_date between ppf1.effective_start_date and
       ppf1.effective_end_date
   and ppf2.agent_id(+) = msi.buyer_id
   and msi.inventory_item_id = prl.item_id
   and msi.organization_id = prl.destination_organization_id
   and pll.line_location_id(+) = prl.line_location_id
   and pll.po_header_id = ph.po_header_id(+)
   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
   AND PLL.LINE_LOCATION_ID IS NULL
   AND PRL.CLOSED_CODE IS NULL
   AND NVL(PRL.CANCEL_FLAG, 'N') <> 'Y'
 orDER BY 1, 2