Featured post

Functionality of Segment Value Inheritance ESS Process

  The Segment Value Inheritance process simplifies the maintenance of the chart of accounts. When the characteristics of values in the value...

Sunday 10 July 2016

Oracle Purchasing ( PO )SQL Scripts , Queries

Click here to find Useful SQL Scripts, Queries ( All Modules)



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

  1. ---used to list all Internal Requisitions that do not have an  associated Internal Sales order  
  2. Select RQH.SEGMENT1 REQ_NUM,  
  3. RQL.LINE_NUM,  
  4. RQL.REQUISITION_HEADER_ID ,  
  5. RQL.REQUISITION_LINE_ID,  
  6. RQL.ITEM_ID ,  
  7. RQL.UNIT_MEAS_LOOKUP_CODE ,  
  8. RQL.UNIT_PRICE ,  
  9. RQL.QUANTITY ,  
  10. RQL.QUANTITY_CANCELLED,  
  11. RQL.QUANTITY_DELIVERED ,  
  12. RQL.CANCEL_FLAG ,  
  13. RQL.SOURCE_TYPE_CODE ,  
  14. RQL.SOURCE_ORGANIZATION_ID ,  
  15. RQL.DESTINATION_ORGANIZATION_ID,  
  16. RQH.TRANSFERRED_TO_OE_FLAG  
  17. from  
  18. PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH  
  19. where  
  20. RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID  
  21. and RQL.SOURCE_TYPE_CODE = 'INVENTORY'  
  22. and RQL.SOURCE_ORGANIZATION_ID is not null  
  23. and not exists (select 'existing internal order'  
  24. from OE_ORDER_LINES_ALL LIN  
  25. where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID  
  26. and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)  
  27. 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 )


  1. -----Relation with Requistion and PO  
  2. select r.segment1 "Req Num",  
  3.        p.segment1 "PO Num"  
  4. from po_headers_all p,   
  5. po_distributions_all d,  
  6. po_req_distributions_all rd,   
  7. po_requisition_lines_all rl,  
  8. po_requisition_headers_all r   
  9. where p.po_header_id = d.po_header_id   
  10. and d.req_distribution_id = rd.distribution_id   
  11. and rd.requisition_line_id = rl.requisition_line_id   
  12. and rl.requisition_header_id = r.requisition_header_id   


3. You need to list out all cancel Requisitions

  1. -----list My cancel Requistion  
  2. select prh.REQUISITION_HEADER_ID,  
  3.       prh.PREPARER_ID ,  
  4.       prh.SEGMENT1 "REQ NUM",  
  5.       trunc(prh.CREATION_DATE),  
  6.       prh.DESCRIPTION,  
  7.       prh.NOTE_TO_AUTHORIZER  
  8. from apps.Po_Requisition_headers_all prh,  
  9.      apps.po_action_history pah   
  10. where Action_code='CANCEL'   
  11. and pah.object_type_code='REQUISITION'   
  12. 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)


  1. -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  
  2.   select   
  3.   prh.segment1 "PR NUM",   
  4.   trunc(prh.creation_date) "CREATED ON",   
  5.   trunc(prl.creation_date) "Line Creation Date" ,  
  6.   prl.line_num "Seq #",   
  7.   msi.segment1 "Item Num",   
  8.   prl.item_description "Description",   
  9.   prl.quantity "Qty",   
  10.   trunc(prl.need_by_date) "Required By",   
  11.   ppf1.full_name "REQUESTOR",   
  12.   ppf2.agent_name "BUYER"   
  13.   from   
  14.   po.po_requisition_headers_all prh,   
  15.   po.po_requisition_lines_all prl,   
  16.   apps.per_people_f ppf1,   
  17.   (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,   
  18.   po.po_req_distributions_all prd,   
  19.   inv.mtl_system_items_b msi,   
  20.   po.po_line_locations_all pll,   
  21.   po.po_lines_all pl,   
  22.   po.po_headers_all ph   
  23.   WHERE   
  24.   prh.requisition_header_id = prl.requisition_header_id   
  25.   and prl.requisition_line_id = prd.requisition_line_id   
  26.   and ppf1.person_id = prh.preparer_id   
  27.   and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date   
  28.   and ppf2.agent_id(+) = msi.buyer_id   
  29.   and msi.inventory_item_id = prl.item_id   
  30.   and msi.organization_id = prl.destination_organization_id   
  31.   and pll.line_location_id(+) = prl.line_location_id   
  32.   and pll.po_header_id = ph.po_header_id(+)   
  33.   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)   
  34.   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'   
  35.   AND PLL.LINE_LOCATION_ID IS NULL   
  36.   AND PRL.CLOSED_CODE IS NULL   
  37.   AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'  
  38.   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.


  1. ----- List and all data entry from PR till PO  
  2.   
  3. select distinct u.description "Requestor",   
  4. porh.segment1 as "Req Number",   
  5. trunc(porh.Creation_Date) "Created On",   
  6. pord.LAST_UPDATED_BY,   
  7. porh.Authorization_Status "Status",   
  8. porh.Description "Description",   
  9. poh.segment1 "PO Number",   
  10. trunc(poh.Creation_date) "PO Creation Date",   
  11. poh.AUTHORIZATION_STATUS "PO Status",   
  12. trunc(poh.Approved_Date) "Approved Date"  
  13. from apps.po_headers_all poh,   
  14. apps.po_distributions_all pod,   
  15. apps.po_req_distributions_all pord,   
  16. apps.po_requisition_lines_all porl,   
  17. apps.po_requisition_headers_all porh,   
  18. apps.fnd_user u   
  19. where porh.requisition_header_id = porl.requisition_header_id   
  20. and porl.requisition_line_id = pord.requisition_line_id   
  21. and pord.distribution_id = pod.req_distribution_id(+)   
  22. and pod.po_header_id = poh.po_header_id(+)   
  23. and porh.created_by = u.user_id  
  24. order by 2   
6.Identifying all PO's which does not have any PR's


  1. -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  
  2.   select   
  3.   prh.segment1 "PR NUM",   
  4.   trunc(prh.creation_date) "CREATED ON",   
  5.   trunc(prl.creation_date) "Line Creation Date" ,  
  6.   prl.line_num "Seq #",   
  7.   msi.segment1 "Item Num",   
  8.   prl.item_description "Description",   
  9.   prl.quantity "Qty",   
  10.   trunc(prl.need_by_date) "Required By",   
  11.   ppf1.full_name "REQUESTOR",   
  12.   ppf2.agent_name "BUYER"   
  13.   from   
  14.   po.po_requisition_headers_all prh,   
  15.   po.po_requisition_lines_all prl,   
  16.   apps.per_people_f ppf1,   
  17.   (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,   
  18.   po.po_req_distributions_all prd,   
  19.   inv.mtl_system_items_b msi,   
  20.   po.po_line_locations_all pll,   
  21.   po.po_lines_all pl,   
  22.   po.po_headers_all ph   
  23.   WHERE   
  24.   prh.requisition_header_id = prl.requisition_header_id   
  25.   and prl.requisition_line_id = prd.requisition_line_id   
  26.   and ppf1.person_id = prh.preparer_id   
  27.   and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date   
  28.   and ppf2.agent_id(+) = msi.buyer_id   
  29.   and msi.inventory_item_id = prl.item_id   
  30.   and msi.organization_id = prl.destination_organization_id   
  31.   and pll.line_location_id(+) = prl.line_location_id   
  32.   and pll.po_header_id = ph.po_header_id(+)   
  33.   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)   
  34.   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'   
  35.   AND PLL.LINE_LOCATION_ID IS NULL   
  36.   AND PRL.CLOSED_CODE IS NULL   
  37.   AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'  
  38.   ORDER BY 1,2  
7. List all the PO's with there approval ,invoice and Payment Details


  1. ----- List and PO With there approval , invoice and payment details  
  2. select   
  3. a.org_id "ORG ID",   
  4. E.SEGMENT1 "VENDOR NUM",  
  5. e.vendor_name "SUPPLIER NAME",  
  6. UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",   
  7. f.vendor_site_code "VENDOR SITE CODE",  
  8. f.ADDRESS_LINE1 "ADDRESS",  
  9. f.city "CITY",  
  10. f.country "COUNTRY",   
  11. to_char(trunc(d.CREATION_DATE)) "PO Date",   
  12. d.segment1 "PO NUM",  
  13. d.type_lookup_code "PO Type",   
  14. c.quantity_ordered "QTY ORDERED",   
  15. c.quantity_cancelled "QTY CANCELLED",   
  16. g.item_id "ITEM ID" ,   
  17. g.item_description "ITEM DESCRIPTION",  
  18. g.unit_price "UNIT PRICE",   
  19. (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",   
  20. (select   
  21. decode(ph.approved_FLAG, 'Y', 'Approved')   
  22. from po.po_headers_all ph   
  23. where ph.po_header_ID = d.po_header_id)"PO Approved?",   
  24. a.invoice_type_lookup_code "INVOICE TYPE",  
  25. a.invoice_amount "INVOICE AMOUNT",   
  26. to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",   
  27. a.invoice_num "INVOICE NUMBER",   
  28. (select   
  29. decode(x.MATCH_STATUS_FLAG, 'A', 'Approved')   
  30. from ap.ap_invoice_distributions_all x   
  31. where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?",   
  32. a.amount_paid,  
  33. h.amount,   
  34. h.check_id,   
  35. h.invoice_payment_id "Payment Id",   
  36. i.check_number "Cheque Number",   
  37. to_char(trunc(i.check_DATE)) "PAYMENT DATE"   
  38.    
  39. FROM AP.AP_INVOICES_ALL A,   
  40. AP.AP_INVOICE_DISTRIBUTIONS_ALL B,   
  41. PO.PO_DISTRIBUTIONS_ALL C,   
  42. PO.PO_HEADERS_ALL D,   
  43. PO.PO_VENDORS E,   
  44. PO.PO_VENDOR_SITES_ALL F,   
  45. PO.PO_LINES_ALL G,   
  46. AP.AP_INVOICE_PAYMENTS_ALL H,   
  47. AP.AP_CHECKS_ALL I   
  48. where a.invoice_id = b.invoice_id   
  49. and b.po_distribution_id = c. po_distribution_id (+)   
  50. and c.po_header_id = d.po_header_id (+)   
  51. and e.vendor_id (+) = d.VENDOR_ID   
  52. and f.vendor_site_id (+) = d.vendor_site_id   
  53. and d.po_header_id = g.po_header_id   
  54. and c.po_line_id = g.po_line_id   
  55. and a.invoice_id = h.invoice_id   
  56. and h.check_id = i.check_id   
  57. and f.vendor_site_id = i.vendor_site_id   
  58. and c.PO_HEADER_ID is not null   
  59. and a.payment_status_flag = 'Y'   
  60. and d.type_lookup_code != 'BLANKET'   


8.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..
The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
  • REFERENCE_1- Source (PO or REQ)
  • REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
    po_requisition_headers_all.requisition_header_id)
  • REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
    po_req_distributions_all.distribution_id)
  • REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
    po_requisition_headers_all.segment1)
  • REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
  • REFERENCE_1- Source (PO)
  • REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
  • REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
  • REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
  • REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.

9. List me all open PO's

  1. ----- List all open PO'S  
  2. select   
  3. h.segment1 "PO NUM",   
  4. h.authorization_status "STATUS",   
  5. l.line_num "SEQ NUM",   
  6. ll.line_location_id,   
  7. d.po_distribution_id ,   
  8. h.type_lookup_code "TYPE"   
  9. from   
  10. po.po_headers_all h,   
  11. po.po_lines_all l,   
  12. po.po_line_locations_all ll,   
  13. po.po_distributions_all d   
  14. where h.po_header_id = l.po_header_id   
  15. and ll.po_line_id = l.po_Line_id   
  16. and ll.line_location_id = d.line_location_id   
  17. and h.closed_date is null   
  18. and h.type_lookup_code not in ('QUOTATION')  
10.There are different authorization_status can a requisition have.

  • Approved
  • Cancelled
  • In Process
  • Incomplete
  • Pre-Approved
  • Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.



Below are the some of useful queries/scripts…..


List Purchase Requisitions with out PO


  1. -----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.  
  2.   select   
  3.   prh.segment1 "PR NUM",   
  4.   trunc(prh.creation_date) "CREATED ON",   
  5.   trunc(prl.creation_date) "Line Creation Date" ,  
  6.   prl.line_num "Seq #",   
  7.   msi.segment1 "Item Num",   
  8.   prl.item_description "Description",   
  9.   prl.quantity "Qty",   
  10.   trunc(prl.need_by_date) "Required By",   
  11.   ppf1.full_name "REQUESTOR",   
  12.   ppf2.agent_name "BUYER"   
  13.   from   
  14.   po.po_requisition_headers_all prh,   
  15.   po.po_requisition_lines_all prl,   
  16.   apps.per_people_f ppf1,   
  17.   (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,   
  18.   po.po_req_distributions_all prd,   
  19.   inv.mtl_system_items_b msi,   
  20.   po.po_line_locations_all pll,   
  21.   po.po_lines_all pl,   
  22.   po.po_headers_all ph   
  23.   WHERE   
  24.   prh.requisition_header_id = prl.requisition_header_id   
  25.   and prl.requisition_line_id = prd.requisition_line_id   
  26.   and ppf1.person_id = prh.preparer_id   
  27.   and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date   
  28.   and ppf2.agent_id(+) = msi.buyer_id   
  29.   and msi.inventory_item_id = prl.item_id   
  30.   and msi.organization_id = prl.destination_organization_id   
  31.   and pll.line_location_id(+) = prl.line_location_id   
  32.   and pll.po_header_id = ph.po_header_id(+)   
  33.   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)   
  34.   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'   
  35.   AND PLL.LINE_LOCATION_ID IS NULL   
  36.   AND PRL.CLOSED_CODE IS NULL   
  37.   AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'  
  38.   ORDER BY 1,2  

List all information form PR to PO


  1. ----- List and all data entry from PR till PO  
  2.   
  3. select distinct u.description "Requestor",   
  4. porh.segment1 as "Req Number",   
  5. trunc(porh.Creation_Date) "Created On",   
  6. pord.LAST_UPDATED_BY,   
  7. porh.Authorization_Status "Status",   
  8. porh.Description "Description",   
  9. poh.segment1 "PO Number",   
  10. trunc(poh.Creation_date) "PO Creation Date",   
  11. poh.AUTHORIZATION_STATUS "PO Status",   
  12. trunc(poh.Approved_Date) "Approved Date"  
  13. from apps.po_headers_all poh,   
  14. apps.po_distributions_all pod,   
  15. apps.po_req_distributions_all pord,   
  16. apps.po_requisition_lines_all porl,   
  17. apps.po_requisition_headers_all porh,   
  18. apps.fnd_user u   
  19. where porh.requisition_header_id = porl.requisition_header_id   
  20. and porl.requisition_line_id = pord.requisition_line_id   
  21. and pord.distribution_id = pod.req_distribution_id(+)   
  22. and pod.po_header_id = poh.po_header_id(+)   
  23. and porh.created_by = u.user_id  
  24. order by 2   

List out all cancel Requisitions


  1. -----list My cancel Requistion  
  2. select prh.REQUISITION_HEADER_ID,  
  3.       prh.PREPARER_ID ,  
  4.       prh.SEGMENT1 "REQ NUM",  
  5.       trunc(prh.CREATION_DATE),  
  6.       prh.DESCRIPTION,  
  7.       prh.NOTE_TO_AUTHORIZER  
  8. from apps.Po_Requisition_headers_all prh,  
  9.      apps.po_action_history pah   
  10. where Action_code='CANCEL'   
  11. and pah.object_type_code='REQUISITION'   
  12. and pah.object_id=prh.REQUISITION_HEADER_ID
By at
Labels:

No comments:

Post a Comment

Please review my topic and update your comments

Note: only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)