Purchasing Useful Scripts .

Relation between Requisition and PO tables,Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

[sql] view plain copy print ? ---used to list all Internal Requisitions that do not have an  associated Internal Sales order    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;  

---used to list all Internal Requisitions that do not have an  associated Internal Sales orderSelect 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_FLAGfromPO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select 'existing internal order'from OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10)ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

[sql] view plain copy print ? -----Relation with Requistion 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   

-----Relation with Requistion and POselect 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 

3. You need to list out all cancel Requisitions

[sql] view plain copy print ? -----list My cancel Requistion    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   

-----list My cancel Requistionselect prh.REQUISITION_HEADER_ID,      prh.PREPARER_ID ,      prh.SEGMENT1 "REQ NUM",      trunc(prh.CREATION_DATE),      prh.DESCRIPTION,      prh.NOTE_TO_AUTHORIZERfrom 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 

4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)

[sql] view plain copy print ? -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.      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  

-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  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

5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

[sql] view plain copy print ? ----- List and all data entry from PR till PO       select distinct u.description "Requestor",    porh.segment1 as "Req Number",    trunc(porh.Creation_Date) "Created On",    pord.LAST_UPDATED_BY,    porh.Authorization_Status "Status",    porh.Description "Description",    poh.segment1 "PO Number",    trunc(poh.Creation_date) "PO Creation Date",    poh.AUTHORIZATION_STATUS "PO Status",    trunc(poh.Approved_Date) "Approved Date"   from apps.po_headers_all poh,    apps.po_distributions_all pod,    apps.po_req_distributions_all pord,    apps.po_requisition_lines_all porl,    apps.po_requisition_headers_all porh,    apps.fnd_user u    where porh.requisition_header_id = porl.requisition_header_id    and porl.requisition_line_id = pord.requisition_line_id    and pord.distribution_id = pod.req_distribution_id(+)    and pod.po_header_id = poh.po_header_id(+)    and porh.created_by = u.user_id   order by 2   

----- List and all data entry from PR till POselect distinct u.description "Requestor", porh.segment1 as "Req Number", trunc(porh.Creation_Date) "Created On", pord.LAST_UPDATED_BY, porh.Authorization_Status "Status", porh.Description "Description", poh.segment1 "PO Number", trunc(poh.Creation_date) "PO Creation Date", poh.AUTHORIZATION_STATUS "PO Status", trunc(poh.Approved_Date) "Approved Date"from apps.po_headers_all poh, apps.po_distributions_all pod, apps.po_req_distributions_all pord, apps.po_requisition_lines_all porl, apps.po_requisition_headers_all porh, apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and pord.distribution_id = pod.req_distribution_id(+) and pod.po_header_id = poh.po_header_id(+) and porh.created_by = u.user_idorder by 2 

6.Identifying all PO's which does not have any PR's

[sql] view plain copy print ? -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.      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  

-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  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.effec