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

Query to get PO Requisition, PO and Receipt details in R12

Query to get PO Requisition, PO and Receipt details in R12

I had to provide data to auditors on the

1. Internal & Purchase Requisitions created by users
2. Purchase Orders created for the requisitions (inventory and non inventory items)
3. Receiving transactions with PO and Requisition information

Purchase Requisition details

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

Internal Requisition details

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

Purchase Order details

SELECT

    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 plc

WHERE

  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 null

UNION

-- Purchase Orders for inventory items

SELECT

    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 plc

WHERE

  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

Receiving transactions with PO and requisition information

SELECT

ph.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_qty

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

where 1=1

and to_char(rct.creation_date, 'YYYY') in ('2010', '2011')

and rct.po_header_id = ph.po_header_id

and rct.po_line_location_id = pll.line_location_id

and rct.po_line_id = pol.po_line_id

and rct.shipment_line_id=rsl.shipment_line_id

and rsl.shipment_header_id=rsh.shipment_header_id

and rsh.ship_to_org_id = ood.organization_id

order by rct.transaction_id

Lessions Learned in Upgrade to R12

Backgund:

• Oracle upgraded to R12 in January 2010 after the books were closed.
• It took 3.5 days to do the upgrade.
• Winrunner scripts were used for automated testing.
• Manual testing was done for the more complex modules.
• The upgrade added 1.3 terabits of data.
• Upgraded to R12 from Sep 09 - Mar-11.; Version 12.01 RUP2 and RUP3.
• Rehearsed pre and post upgrade steps.
• Oracle upgraded AP, AR, AP, OIE, GL, CM, Procurement, Workflow, AME, OM, etc...CRM apps were also moved from the CRM Suite e-Business Suite to Siebel.

R12 Upgrade Approach:

• Considered the project as a technical upgrade
• Only implemented mandatory functionality
• Migrated 8 years of SLA data
• Updated test plans
• Parallel testing
• Identify features that can be implemented prior to R12
• New features (TCA for customers) is mandatory in R12
• Implemented Multi-org access control
• Setup Ledger sets
• Replaced custom code with standard code
• Copy of the database prior to the upgrade

Project Management and Testing Strateg:

• Identified a Project Manager to oversee all of the upgrade activities
• Program office to work with both the functional and technical teams
• Full end to end testing
• Weekly meetings and status reports

Lessons Learned:

• Close out any open Workflow transactions. No transactions can be in transit.
• Make sure the interfaces are cleared
• Stop any pending or scheduled concurrent manager processes
• Update request sets
• Print off any reports used for reconciliation, print off before the upgrade. (IT should take the lead on doing this.)

Sub-ledger Accounting (SLA):

• SLA sits between the sub-module and the GL
• All sub-ledger data is stored in SLA
• SLA holds the true accounting and passes the summary to the GL
• Reconciliation reports will need to be redone based on the SLA tables.
• AR and PA autoacccouting rules will remain intact (except intercompany.)
• You can store alternative accounting in the SLA tables for transactions
• R12 has a feature for updating invalid code combinations
• Run Create Accounting (or Sub Accounting in AR) move the data from the sub-ledger to the SLA and the SLA to the GL.
• Oracle also suggests running the create accounting process throughout the month into SLA.
• Consider parallelization (multiple threads) with the create accounting process
• sub-ledger period close exceptions report (provides clear view of the exceptions.)

The Global Accounting engine (AX)

• All statutory SOB's become secondary ledgers
• Need to setup sequences
• Reports are all XML based in R12. Oracle suggested ramping up XML knowledge.

EBtax

• Tax setup is migrated from 11i to R12
• Clean-up your tax setup before the update
• Single global tax engine for AP and AR

Payments (AP)

• All payments format files are now XML. Any payment format files will not upgrade
• The 11i files will not upgrade
• New user interface

TCA (AR)
 

• Remove all code dependent on backward compatible views in R12
• New web-based interface for maintaining parties and party sites

Procurement and Iexpense

• Cleanup catalog, supplier data
• Consider any 11i per Diem and mileage schedules
• Remove any expenses that are in transit. They should all be paid or withdrawn.

Intercompany Accounting:

• Clean-up Intercompany rules
• Setup distribution accounting and clearing rules using R12 transaction account functionality

Customizations:

• Review your customizations and ensure they are still needed.
• R12 has many XML reports that can be easily updated by the user

Other considerations

• Ensure there is a monthend protocol
• Run create accounting daily
• Ensure a request set is created to include create accounting and the transfer to the GL as well as the exception report
• Ebtax and SLA are new shared modules
• PA: Post upgrade run the UPG: Upgrade transaction attributes program for historical transactions.
• IN AP, remember the Payments module is completely reworked.
• Volume test to find performance issues
• Scrambling of credit card data

Critical success factors

• Buy in from all stakeholders
• Detailed project planning and communications
• Multiple practice rounds of performing the upgrade
• Detailed inventory of patches
• Proper tracking of status and issue reporting
• Strong user involvement in all phases of the project. There are changes in business process and new forms. User should know how to use these forms.
• Focus on testing
• Robust end to end testing
• Document any reconciling items prior to the R12 upgrade

There is a white paper on Metalink called "R12 Best Practices." The presenter highly recommends this white paper.