Oracle Purchasing Few important Queries and Facts

A)- list out all Internal Requisitions that do not have an associated Internal Sales order.

---used to list all Internal Requisitions that do not have an  associated ISO

Select RQH.SEGMENT1 REQ_NUM,
RQL.LINE_NUM,
RQL.REQUISITION_HEADER_ID ,
RQL.REQUISITION_LINE_ID,
RQL.ITEM_ID ,
RQL.UNIT_MEAS_LOOKUP_CODE ,
RQL.UNIT_PRICE ,
RQL.QUANTITY ,
RQL.QUANTITY_CANCELLED,
RQL.QUANTITY_DELIVERED ,
RQL.CANCEL_FLAG ,
RQL.SOURCE_TYPE_CODE ,
RQL.SOURCE_ORGANIZATION_ID ,
RQL.DESTINATION_ORGANIZATION_ID,
RQH.TRANSFERRED_TO_OE_FLAG
from
PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
where
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not null
and not exists (select 'existing internal order'
from OE_ORDER_LINES_ALL LIN
where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

B)-display what requisition and PO are linked(Relation with Requisition and PO )

select r.segment1 "Req Num",
       p.segment1 "PO Num"
from po_headers_all p, 
po_distributions_all d,
po_req_distributions_all rd, 
po_requisition_lines_all rl,
po_requisition_headers_all r 
where p.po_header_id = d.po_header_id 
and d.req_distribution_id = rd.distribution_id 
and rd.requisition_line_id = rl.requisition_line_id 
and rl.requisition_header_id = r.requisition_header_id 

C)-  list out all cancel Requisitions

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 

D)- list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)

select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  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