Thursday, August 4, 2011

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'

1 comment:

Najeeb Janwadkar said...

Cant copy/paste your queries bro ?

Post a Comment

Best Blogger TipsGet Flower Effect