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 plaincopyprint?

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;  

---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 plaincopyprint?

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   

-----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 plaincopyprint?

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   

-----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 plaincopyprint?

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 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 plaincopyprint?

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 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 plaincopyprint?

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 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

7. List all the PO's with there approval ,invoice and Payment Details

[sql] view plaincopyprint?

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'   

----- List and PO With there approval , invoice and payment detailsselect a.org_id "ORG ID", E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY", to_char(trunc(d.CREATION_DATE)) "PO Date", d.segment1 "PO NUM",d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED", c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID" , g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE", (NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", (select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id)"PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT", to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", a.invoice_num "INVOICE NUMBER", (select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", a.amount_paid,h.amount, h.check_id, h.invoice_payment_id "Payment Id", i.check_number "Cheque Number", to_char(trunc(i.check_DATE)) "PAYMENT DATE"  FROM AP.AP_INVOICES_ALL A, AP.AP_INVOICE_DISTRIBUTIONS_ALL B, PO.PO_DISTRIBUTIONS_ALL C, PO.PO_HEADERS_ALL D, PO.PO_VENDORS E, PO.PO_VENDOR_SITES_ALL F, PO.PO_LINES_ALL G, AP.AP_INVOICE_PAYMENTS_ALL H, AP.AP_CHECKS_ALL I where a.invoice_id = b.invoice_id and b.po_distribution_id = c. po_distribution_id (+) and c.po_header_id = d.po_header_id (+) and e.vendor_id (+) = d.VENDOR_ID and f.vendor_site_id (+) = d.vendor_site_id and d.po_header_id = g.po_header_id and c.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id and c.PO_HEADER_ID is not null and a.payment_status_flag = 'Y' 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

[sql] view plaincopyprint?

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')  

----- List all open PO'Sselect h.segment1 "PO NUM", h.authorization_status "STATUS", l.line_num "SEQ NUM", ll.line_location_id, d.po_distribution_id , h.type_lookup_code "TYPE" from po.po_headers_all h, po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_all d where h.po_header_id = l.po_header_id and ll.po_line_id = l.po_Line_id and ll.line_location_id = d.line_location_id and h.closed_date is null 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'.

--------------------------------------------------------------------------------------------------------------------------------------------------------

2..1 Purchase Requisition details

[sql] view plaincopyprint?

1. SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",  
2.        prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,  
3.        prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,  
4.        prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,  
5.        prl.cancel_reason  
6.   FROM po_requisition_headers_all prh,  
7.        po_requisition_lines_all prl,  
8.        po_req_distributions_all prd,  
9.        per_people_x ppx,  
10.        po_headers_all poh,  
11.        po_distributions_all pda  
12.  WHERE prh.requisition_header_id = prl.requisition_header_id  
13.    AND ppx.person_id = prh.preparer_id  
14.    AND prh.type_lookup_code = 'PURCHASE'  
15.    AND prd.requisition_line_id = prl.requisition_line_id  
16.    AND pda.req_distribution_id = prd.distribution_id  
17.    AND pda.po_header_id = poh.po_header_id  
18.    AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')  

SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",       prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,       prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,       prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,       prl.cancel_reason  FROM po_requisition_headers_all prh,       po_requisition_lines_all prl,       po_req_distributions_all prd,       per_people_x ppx,       po_headers_all poh,       po_distributions_all pda WHERE prh.requisition_header_id = prl.requisition_header_id   AND ppx.person_id = prh.preparer_id   AND prh.type_lookup_code = 'PURCHASE'   AND prd.requisition_line_id = prl.requisition_line_id   AND pda.req_distribution_id = prd.distribution_id   AND pda.po_header_id = poh.po_header_id   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

2.2 Internal Requisition details

[sql] view plaincopyprint?

1. SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",  
2.        prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prl.line_num,  
3.        prl.line_type_id, prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,  
4.        prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,  
5.        prl.cancel_reason  
6.   FROM po_requisition_headers_all prh,  
7.        po_requisition_lines_all prl,  
8.        po_req_distributions_all prd,  
9.        per_people_x ppx,  
10.        po_headers_all poh,  
11.        po_distributions_all pda  
12.  WHERE prh.requisition_header_id = prl.requisition_header_id  
13.    AND ppx.person_id = prh.preparer_id  
14.    AND prh.type_lookup_code = 'INTERNAL'  
15.    AND prd.requisition_line_id = prl.requisition_line_id  
16.    AND pda.req_distribution_id (+) = prd.distribution_id  
17.    AND pda.po_header_id = poh.po_header_id (+)  
18.    AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')  

SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",       prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prl.line_num,       prl.line_type_id, prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,       prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,       prl.cancel_reason  FROM po_requisition_headers_all prh,       po_requisition_lines_all prl,       po_req_distributions_all prd,       per_people_x ppx,       po_headers_all poh,       po_distributions_all pda WHERE prh.requisition_header_id = prl.requisition_header_id   AND ppx.person_id = prh.preparer_id   AND prh.type_lookup_code = 'INTERNAL'   AND prd.requisition_line_id = prl.requisition_line_id   AND pda.req_distribution_id (+) = prd.distribution_id   AND pda.po_header_id = poh.po_header_id (+)   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

2.3 Purchase Order details

[sql] view plaincopyprint?

1. -- Purchase Orders for non inventory items like service  
2. SELECT  
3.     ph.SEGMENT1 po_num  
4.   , ph.CREATION_DATE  
5.   , hou.name  "Operating Unit"  
6.   , ppx.full_name "Buyer Name"  
7.   , ph.type_lookup_code "PO Type"  
8.   , plc.displayed_field "PO Status"  
9.   , ph.COMMENTS  
10.   , pl.line_num  
11.   , plt.order_type_lookup_code "Line Type"  
12.   , NULL "Item Code"  
13.   , pl.item_description  
14.   , pl.unit_meas_lookup_code "UOM"  
15.   , pl.base_unit_price  
16.   , pl.unit_price  
17.   , pl.quantity  
18.   , ood.organization_code "Shipment Org Code"  
19.   , ood.organization_name "Shipment Org Name"  
20.   , pv.vendor_name supplier  
21.   , pvs.vendor_site_code  
22.   , (pl.unit_price * pl.quantity) "Line Amount"  
23.   , prh.segment1 req_num  
24.   , prh.type_lookup_code req_method  
25.   , ppx1.full_name "Requisition requestor"  
26. FROM  po_headers_all ph  
27.     , po_lines_all pl  
28.     , po_distributions_all pda  
29.     , po_vendors pv  
30.     , po_vendor_sites_all pvs  
31.     , po_distributions_all pd  
32.     , po_req_distributions_all prd  
33.     , po_requisition_lines_all prl  
34.     , po_requisition_headers_all prh  
35.     , hr_operating_units hou  
36.     , per_people_x ppx  
37.     , po_line_types_b plt  
38.     , org_organization_definitions ood  
39.     , per_people_x ppx1  
40.     , po_lookup_codes plc  
41. WHERE  
42.   1=1  
43.   AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)  
44.   AND ph.vendor_id = pv.vendor_id  
45.   AND ph.po_header_id = pl.po_header_id  
46.   AND ph.vendor_site_id = pvs.vendor_site_id  
47.   AND ph.po_header_id = pd.po_header_id  
48.   and pl.po_line_id = pd.po_line_id  
49.   AND pd.req_distribution_id = prd.distribution_id (+)  
50.   AND prd.requisition_line_id = prl.requisition_line_id (+)  
51.   AND prl.requisition_header_id = prh.requisition_header_id (+)  
52.  and hou.organization_id = ph.org_id  
53.  and ph.agent_id = ppx.person_id  
54.  and pda.po_header_id = ph.po_header_id  
55.  and pda.po_line_id = pl.po_line_id  
56.  and pl.line_type_id = plt.line_type_id  
57.  and ood.organization_id = pda.destination_organization_id  
58.  and ppx1.person_id (+) = prh.preparer_id  
59.  and plc.lookup_type = 'DOCUMENT STATE'  
60.  and plc.LOOKUP_CODE = ph.closed_code  
61.  and pl.item_id is null  
62. UNION  
63. -- Purchase Orders for inventory items  
64. SELECT  
65.     ph.SEGMENT1 po_num  
66.   , ph.CREATION_DATE  
67.   , hou.name  "Operating Unit"  
68.   , ppx.full_name "Buyer Name"  
69.   , ph.type_lookup_code "PO Type"  
70.   , plc.displayed_field "PO Status"  
71.   , ph.COMMENTS  
72.   , pl.line_num  
73.   , plt.order_type_lookup_code "Line Type"  
74.   , msi.segment1 "Item Code"  
75.   , pl.item_description  
76.   , pl.unit_meas_lookup_code "UOM"  
77.   , pl.base_unit_price  
78.   , pl.unit_price  
79.   , pl.quantity  
80.   , ood.organization_code "Shipment Org Code"  
81.   , ood.organization_name "Shipment Org Name"  
82.   , pv.vendor_name supplier  
83.   , pvs.vendor_site_code  
84.   , (pl.unit_price * pl.quantity) "Line Amount"  
85.   , prh.segment1 req_num  
86.   , prh.type_lookup_code req_method  
87.   , ppx1.full_name "Requisition requestor"  
88. FROM  po_headers_all ph  
89.     , po_lines_all pl  
90.     , po_distributions_all pda  
91.     , po_vendors pv  
92.     , po_vendor_sites_all pvs  
93.     , po_distributions_all pd  
94.     , po_req_distributions_all prd  
95.     , po_requisition_lines_all prl  
96.     , po_requisition_headers_all prh  
97.     , hr_operating_units hou  
98.     , per_people_x ppx  
99.     , mtl_system_items_b msi  
100.     , po_line_types_b plt  
101.     , org_organization_definitions ood  
102.     , per_people_x ppx1  
103.     , po_lookup_codes plc  
104. WHERE  
105.   1=1  
106.   AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)  
107.   AND ph.vendor_id = pv.vendor_id  
108.   AND ph.po_header_id = pl.po_header_id  
109.   AND ph.vendor_site_id = pvs.vendor_site_id  
110.   AND ph.po_header_id = pd.po_header_id  
111.   and pl.po_line_id = pd.po_line_id  
112.   AND pd.req_distribution_id = prd.distribution_id (+)  
113.   AND prd.requisition_line_id = prl.requisition_line_id (+)  
114.   AND prl.requisition_header_id = prh.requisition_header_id (+)  
115.  and hou.organization_id = ph.org_id  
116.  and ph.agent_id = ppx.person_id  
117.  and pda.po_header_id = ph.po_header_id  
118.  and pda.po_line_id = pl.po_line_id  
119.  and pl.line_type_id = plt.line_type_id  
120.  and ood.organization_id = pda.destination_organization_id  
121.  and ppx1.person_id (+) = prh.preparer_id  
122.  and pda.destination_organization_id = msi.organization_id (+)  
123.  and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)-- OR pl.item_id is null)  
124.  and plc.lookup_type = 'DOCUMENT STATE'  
125.  and plc.LOOKUP_CODE = ph.closed_code  
126.  and pl.item_id is not null  

-- Purchase Orders for non inventory items like serviceSELECT    ph.SEGMENT1 po_num  , ph.CREATION_DATE  , hou.name  "Operating Unit"  , ppx.full_name "Buyer Name"  , ph.type_lookup_code "PO Type"  , plc.displayed_field "PO Status"  , ph.COMMENTS  , pl.line_num  , plt.order_type_lookup_code "Line Type"  , NULL "Item Code"  , pl.item_description  , pl.unit_meas_lookup_code "UOM"  , pl.base_unit_price  , pl.unit_price  , pl.quantity  , ood.organization_code "Shipment Org Code"  , ood.organization_name "Shipment Org Name"  , pv.vendor_name supplier  , pvs.vendor_site_code  , (pl.unit_price * pl.quantity) "Line Amount"  , prh.segment1 req_num  , prh.type_lookup_code req_method  , ppx1.full_name "Requisition requestor"FROM  po_headers_all ph    , po_lines_all pl    , po_distributions_all pda    , po_vendors pv    , po_vendor_sites_all pvs    , po_distributions_all pd    , po_req_distributions_all prd    , po_requisition_lines_all prl    , po_requisition_headers_all prh    , hr_operating_units hou    , per_people_x ppx    , po_line_types_b plt    , org_organization_definitions ood    , per_people_x ppx1    , po_lookup_codes plcWHERE  1=1  AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)  AND ph.vendor_id = pv.vendor_id  AND ph.po_header_id = pl.po_header_id  AND ph.vendor_site_id = pvs.vendor_site_id  AND ph.po_header_id = pd.po_header_id  and pl.po_line_id = pd.po_line_id  AND pd.req_distribution_id = prd.distribution_id (+)  AND prd.requisition_line_id = prl.requisition_line_id (+)  AND prl.requisition_header_id = prh.requisition_header_id (+) and hou.organization_id = ph.org_id and ph.agent_id = ppx.person_id and pda.po_header_id = ph.po_header_id and pda.po_line_id = pl.po_line_id and pl.line_type_id = plt.line_type_id and ood.organization_id = pda.destination_organization_id and ppx1.person_id (+) = prh.preparer_id and plc.lookup_type = 'DOCUMENT STATE' and plc.LOOKUP_CODE = ph.closed_code and pl.item_id is nullUNION-- Purchase Orders for inventory itemsSELECT    ph.SEGMENT1 po_num  , ph.CREATION_DATE  , hou.name  "Operating Unit"  , ppx.full_name "Buyer Name"  , ph.type_lookup_code "PO Type"  , plc.displayed_field "PO Status"  , ph.COMMENTS  , pl.line_num  , plt.order_type_lookup_code "Line Type"  , msi.segment1 "Item Code"  , pl.item_description  , pl.unit_meas_lookup_code "UOM"  , pl.base_unit_price  , pl.unit_price  , pl.quantity  , ood.organization_code "Shipment Org Code"  , ood.organization_name "Shipment Org Name"  , pv.vendor_name supplier  , pvs.vendor_site_code  , (pl.unit_price * pl.quantity) "Line Amount"  , prh.segment1 req_num  , prh.type_lookup_code req_method  , ppx1.full_name "Requisition requestor"FROM  po_headers_all ph    , po_lines_all pl    , po_distributions_all pda    , po_vendors pv    , po_vendor_sites_all pvs    , po_distributions_all pd    , po_req_distributions_all prd    , po_requisition_lines_all prl    , po_requisition_headers_all prh    , hr_operating_units hou    , per_people_x ppx    , mtl_system_items_b msi    , po_line_types_b plt    , org_organization_definitions ood    , per_people_x ppx1    , po_lookup_codes plcWHERE  1=1  AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)  AND ph.vendor_id = pv.vendor_id  AND ph.po_header_id = pl.po_header_id  AND ph.vendor_site_id = pvs.vendor_site_id  AND ph.po_header_id = pd.po_header_id  and pl.po_line_id = pd.po_line_id  AND pd.req_distribution_id = prd.distribution_id (+)  AND prd.requisition_line_id = prl.requisition_line_id (+)  AND prl.requisition_header_id = prh.requisition_header_id (+) and hou.organization_id = ph.org_id and ph.agent_id = ppx.person_id and pda.po_header_id = ph.po_header_id and pda.po_line_id = pl.po_line_id and pl.line_type_id = plt.line_type_id and ood.organization_id = pda.destination_organization_id and ppx1.person_id (+) = prh.preparer_id and pda.destination_organization_id = msi.organization_id (+) and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)-- OR pl.item_id is null) and plc.lookup_type = 'DOCUMENT STATE' and plc.LOOKUP_CODE = ph.closed_code and pl.item_id is not null

2.4 Receiving transactions with PO and requisition information

[sql] view plaincopyprint?

1. SELECT  
2. ph.segment1 po_num,  
3. ood.organization_name,  
4. pol.po_line_id,  
5. pll.quantity,  
6. rsh. receipt_source_code,  
7. rsh. vendor_id,  
8. rsh. vendor_site_id,  
9. rsh. organization_id,  
10. rsh. shipment_num,  
11. rsh. receipt_num,  
12. rsh. ship_to_location_id,  
13. rsh. bill_of_lading,  
14. rsl.shipment_line_id,  
15. rsl.QUANTITY_SHIPPED,  
16. rsl.QUANTITY_RECEIVED ,  
17. rct.transaction_type,  
18. rct.transaction_id,  
19. nvl(rct.source_doc_quantity,0) transaction_qty  
20. from rcv_transactions rct  
21. , rcv_shipment_headers rsh  
22. , rcv_shipment_lines rsl  
23. , po_lines_all pol  
24. , po_line_locations_all pll  
25. , po_headers_all ph  
26. , org_organization_definitions ood  
27. where 1=1  
28. and to_char(rct.creation_date, 'YYYY') in ('2010', '2011')  
29. and rct.po_header_id = ph.po_header_id  
30. and rct.po_line_location_id = pll.line_location_id  
31. and rct.po_line_id = pol.po_line_id  
32. and rct.shipment_line_id=rsl.shipment_line_id  
33. and rsl.shipment_header_id=rsh.shipment_header_id  
34. and rsh.ship_to_org_id = ood.organization_id  
35. order by rct.transaction_id  

SELECTph.segment1 po_num,ood.organization_name,pol.po_line_id,pll.quantity,rsh. receipt_source_code,rsh. vendor_id,rsh. vendor_site_id,rsh. organization_id,rsh. shipment_num,rsh. receipt_num,rsh. ship_to_location_id,rsh. bill_of_lading,rsl.shipment_line_id,rsl.QUANTITY_SHIPPED,rsl.QUANTITY_RECEIVED ,rct.transaction_type,rct.transaction_id,nvl(rct.source_doc_quantity,0) transaction_qtyfrom rcv_transactions rct, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_lines_all pol, po_line_locations_all pll, po_headers_all ph, org_organization_definitions oodwhere 1=1and to_char(rct.creation_date, 'YYYY') in ('2010', '2011')and rct.po_header_id = ph.po_header_idand rct.po_line_location_id = pll.line_location_idand rct.po_line_id = pol.po_line_idand rct.shipment_line_id=rsl.shipment_line_idand rsl.shipment_header_id=rsh.shipment_header_idand rsh.ship_to_org_id = ood.organization_idorder by rct.transaction_id

--------------------------------------------------------------------------------------------------------------------------------------------------------

Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.

Stage 1: PO Creation :

PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =<po_number>;

select * from po_headers_all where po_header_id =<po_header_id>;

po_lines_all

select * from po_lines_all where po_header_id =<po_header_id>;

po_line_locations_all

select * from po_line_locations_all where po_header_id =<po_header_id>;

po_distributions_all

select * from po_distributions_all where po_header_id =<po_header_id>;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =<po_header_id>;

RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =<po_header_id>;

RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);

RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =<po_header_id>;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =<po_header_id>);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>);

AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = <po_header_id> );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');

GL_INTERFACE

SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id <po_header_id>));

GL_IMPORT_REFERENCES

SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>))

 

FROM:  http://blog.csdn.net/pan_tian/article/details/8776590