Oracle Purchare Order (PO) Queries

--Query to list canceled Requisition:

select

  prh.REQUISITION_HEADER_ID,

  prh.PREPARER_ID ,

  prh.SEGMENT1 "REQ NUM",

  trunc(prh.CREATION_DATE),

  prh.DESCRIPTION,

  prh.NOTE_TO_AUTHORIZER

from apps.Po_Requisition_headers_all prh,

  apps.po_action_history pah

where Action_code='CANCEL'

  and pah.object_type_code='REQUISITION'

  and pah.object_id=prh.REQUISITION_HEADER_ID

--REQUISITION

select

  prh.segment1 Req_num,

  pdt.document_type_code,

  prh.TYPE_LOOKUP_CODE Type,

  prh.AUTHORIZATION_STATUS status,

  ppf.FULL_NAME Preparer,

  PRL.LINE_NUM ,

  msi.SEGMENT1 item_num,

  PLT.LINE_TYPE,

  msi.DESCRIPTION Item_des,

  ppf.FULL_NAME Requester

from po_requisition_headers prh,

  per_people_f ppf,

  po_requisition_lines prl,

  PO_LINE_TYPES PLT,

  po_document_types pdt,

  Mtl_system_items msi

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

  and prh.preparer_id = ppf.person_id

  and plt.line_type_id = prl.line_type_id

  AND msi.inventory_item_id= prl.item_id

  -- and msi.ORGANIZATION_ID=prl.ORG_ID

  AND pdt.document_subtype = prh.type_lookup_code

  AND pdt.document_type_code = 'REQUISITION'

  AND pdt.org_id=prh.org_id

  AND prh.segment1='5633'

--PURCHASE ORDER QUERY

select

  pv.VENDOR_NAME,

  pv.VENDOR_ID,

  pv.SEGMENT1 vend_num,

  pp.FULL_NAME buyer_name,

  pp.LAST_NAME,

  pdt.type_name po_type,

  ph.STATUS_LOOKUP_CODE STATUS,

  msi.SEGMENT1 item_num,

  msi.DESCRIPTION Item_des,

  pl.QUANTITY,

  pl.UNIT_PRICE

FROM po_vendors pv,

  po_headers ph,

  po_lines pl,

  po_document_types pdt,

  per_people_f pp,

  mtl_system_items msi

where ph.po_header_id=pl.PO_HEADER_ID

  AND pv.VENDOR_ID=ph.VENDOR_ID

  and msi.ORGANIZATION_ID=ph.ORG_ID

  AND (( pdt.document_type_code IN ('PO', 'PA') AND pdt.document_subtype = ph.type_lookup_code) )

  AND msi.inventory_item_id = pl.item_id

  AND ph.agent_id = pp.person_id

  AND ph.SEGMENT1=4419

--P2P Cycle

select

  prh.segment1 reqno,

  ph.SEGMENT1 po_num ,

  prh.TYPE_LOOKUP_CODE po_Type,

  prh.TYPE_LOOKUP_CODE req_type ,

  prl.LINE_NUM,ph.ORG_ID,

  pd.PO_DISTRIBUTION_ID,

  ai.INVOICE_TYPE_LOOKUP_CODE ,

  ai.INVOICE_AMOUNT,

  ai.AMOUNT_PAID,

  ai.INVOICE_DATE

from po_requisition_headers_all prh,

  po_requisition_lines_all prl,

  po_req_distributions_all prd,

  po_headers ph

  ,po_lines pl

  ,ap_invoices_all ai

  ,po_distributions_all pd

  ,RCV_SHIPMENT_HEADERS RSH

  ,RCV_SHIPMENT_LINES RSL

  ,ap_invoice_distributions_all aid

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

  and prd.REQUISITION_LINE_ID=prl.REQUISITION_LINE_ID

  and pd.REQ_DISTRIBUTION_ID =prd.DISTRIBUTION_ID

  and RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID

  and ph.PO_HEADER_ID=rsl.PO_HEADER_ID

  and pl.PO_LINE_ID=pd.PO_LINE_ID

  and pl.PO_HEADER_ID=ph.PO_HEADER_ID

  and aid.INVOICE_ID=ai.INVOICE_ID

  and pd.PO_DISTRIBUTION_ID=aid.PO_DISTRIBUTION_ID

  and prh.segment1='5659'

--REQUSITION & PO QUERY FOR ID’S

select

  prh.SEGMENT1 reqno,

  ph.SEGMENT1 ponum,

  prh.REQUISITION_HEADER_ID,

  ppf.FULL_NAME preparer,

  ppf.FULL_NAME requester,

  podt.DOCUMENT_TYPE_CODE,

  plt.LINE_TYPE,

  ms.SEGMENT1 Item,

  mo.ORGANIZATION_NAME,

  pv.VENDOR_NAME supplier,

  hl.LOCATION_CODE "ship to location",

  mo.CITY "bill to location",

  ppf.FULL_NAME buyer

from

  po_requisition_headers_all prh

  ,po_requisition_lines_all prl

  ,po_document_types podt

  ,po_headers ph

  ,po_lines pl

  ,po_vendors pv

  ,po_line_types plt

  ,po_req_distributions_all prd

  ,po_distributions_all pd

  ,per_people_f ppf

  ,mtl_system_items ms

  ,mtl_organizations mo

  ,hr_locations_all_tl hl

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

  and ph.PO_HEADER_ID=pl.PO_HEADER_ID

  and ph.VENDOR_ID=pv.VENDOR_ID

  and prl.REQUISITION_LINE_ID =prd.REQUISITION_LINE_ID

  and prd.DISTRIBUTION_ID=pd.REQ_DISTRIBUTION_ID

  and pl.PO_LINE_ID=pd.PO_LINE_ID

  and prh.TYPE_LOOKUP_CODE=podt.DOCUMENT_SUBTYPE

  and prl.LINE_TYPE_ID=plt.LINE_TYPE_ID

  and prh.PREPARER_ID=ppf.PERSON_ID

  and prl.ITEM_ID=ms.INVENTORY_ITEM_ID

  and prh.ORG_ID=mo.ORGANIZATION_ID

  and prh.ORG_ID=ms.ORGANIZATION_ID

  and ph.SHIP_TO_LOCATION_ID=hl.LOCATION_ID

  and prh.SEGMENT1='5710'

--ABOVE QUERY TO GET THE DETAILS FOR REQUSITION WITHOUT PO

select

  prh.SEGMENT1 reqno,

  ph.SEGMENT1 ponum,

  prh.REQUISITION_HEADER_ID,

  ppf.FULL_NAME preparer,

  ppf.FULL_NAME requester,

  podt.DOCUMENT_TYPE_CODE,

  plt.LINE_TYPE,ms.SEGMENT1 Item,

  mo.ORGANIZATION_NAME,

  pv.VENDOR_NAME supplier,

  hl.LOCATION_CODE "ship to location",

  mo.CITY "bill to location",

  ppf.FULL_NAME buyer

from

  po_requisition_headers_all prh

  ,po_requisition_lines_all prl

  ,po_document_types podt

  ,po_headers ph

  ,po_lines pl

  ,po_vendors pv

  , po_line_types plt

  ,po_req_distributions_all prd

  ,po_distributions_all pd

  ,per_people_f ppf

  ,mtl_system_items ms

  ,mtl_organizations mo

  ,hr_locations_all_tl hl

where prh.REQUISITION_HEADER_ID=prl.REQUISITION_HEADER_ID

  and ph.PO_HEADER_ID(+)=pl.PO_HEADER_ID

  and ph.VENDOR_ID=pv.VENDOR_ID(+)

  and prl.REQUISITION_LINE_ID =prd.REQUISITION_LINE_ID

  and prd.DISTRIBUTION_ID=pd.REQ_DISTRIBUTION_ID(+)

  and pl.PO_LINE_ID(+)=pd.PO_LINE_ID

  and prh.TYPE_LOOKUP_CODE=podt.DOCUMENT_SUBTYPE

  and prl.LINE_TYPE_ID=plt.LINE_TYPE_ID

  and prh.PREPARER_ID=ppf.PERSON_ID

  and prl.ITEM_ID=ms.INVENTORY_ITEM_ID

  and prh.ORG_ID=mo.ORGANIZATION_ID

  and prh.ORG_ID=ms.ORGANIZATION_ID

  and ph.SHIP_TO_LOCATION_ID=hl.LOCATION_ID(+)

  and prh.SEGMENT1='5711'