P2P Queries

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'