Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

PO: Tips and useful Query

Posted on February 11th, 2008 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

The consultant life while working at client site is not easy during ERP transformation projects, many times it's required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.

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

Internal Requisitions without Sales order

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

Requisition and PO

3. You need to list out all cancel Requisitions

Cancel Requisition

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

PR without PO

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.

PR to PO

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

PO without Requisition

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

8.You need to find table which hold PO Approval path...

These two table keeps the data:

  • PO_APPROVAL_LIST_HEADERS
  • PO_APPROVAL_LIST_LINES

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

List PO's with Approval , invoice and Payment info

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

11. List me all open PO's

List me all Open PO'S

12.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'.

13. A standard Quotations one that you can tie back to a PO.
Navigate to RFQ -> Auto create -> enter a PO and reference it back.

14. I want to debug for a PO , where should I start.

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 :

double-arrowPO_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>;

double-arrowpo_lines_all

select * from po_lines_all where po_header_id =<po_header_id>;

double-arrowpo_line_locations_all

select * from po_line_locations_all where po_header_id =<po_header_id>;

double-arrowpo_distributions_all

select * from po_distributions_all where po_header_id =<po_header_id>;

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

double-arrowRCV_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>);

double-arrowRCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =<po_header_id>;

double-arrowRCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =<po_header_id>;

double-arrowRCV_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>);

double-arrowRCV_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>);

double-arrowRCV_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>);

double-arrowMTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =<po_header_id>;

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

double-arrow 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>);

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

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

double-arrowPrompt 17. GL_BC_PACKETS ..This is for encumbrances

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

double-arrowGL_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>));

double-arrowGL_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>))

Feel free to share or comment..

Related Posts

Posted in Oracle Purchasing | 29 Comments »Email This Post Email This Post | Print This Post Print This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
29 Responses
  1. Ahmad Bilal Says:

    Dear Please correct the spell of Releated and and i think instead of timer use Times

    Stage 4 : Many Timer there is tie up with Project Releated PO

    Thanks & Regards,
    Ahmad Bilal

    Moderator
    http://www.erpstuff.com
    Oracle ERP Portal

    http://oracleebusinesssuite.wordpress.com/

  2. Sanjit Anand Says:

    Thanks Ahmad,

    For correcting me some typo.I am really struggling with WordPress editor, which seems there spell check does not working properly.

    thanks once again
    sanjit

  3. FD Says:

    Thats really a good piece of information. Thanks a lot.

  4. Ahmad Bilal Says:

    No problem Sanjit always welcome.

    to avoid such typo i would suggest use Mozila Firefox

  5. Srinivas Says:

    Thanks aton Sanjit and Bilal for your effort and support.. Very Nice Articles to go through…It would be great if you can publish one Product based implementation with screenshots like Purchasing, OM in R12. that would be great of you…or send me to personally to my mail id srinivasnarsimla@gmail.com

    Regards,
    Srinivas Narsimla

  6. Jerome LARBOULLET Says:

    Hello,

    I am in need to get the quantity ordered, the quantity received, the quantity due to received, the quantity invoiced.

    Do you have a script for that?

  7. Sanjit Anand Says:

    Jerome ,

    I donot have any script, but you can easily pull other information with joining Receiving table, this is simple.

    thanks
    sanjit

  8. Stephen Says:

    I’m trying to write a query that will provide a comparison of Invoice Date to PO create date. I’m having issues finding a solid create date. Seems multiple PO headers relate to a singular invoice and each header has a separate create date. Could vary by years. Any suggestions? Clearly I am not pulling the proper data.

  9. sajeed Says:

    I need a script which gives a list of Internal Requisition numbers, Corresponding Sales Order numbers, Quantity shipped and Quantity Received from this Internal Sales Orders.

  10. Nicolas Says:

    Great information! Thanks Dude!

  11. Fernando U. Says:

    Sanjit Anand, thanks a lot for that monumental peace of code.
    you save me a precious time with that queries.
    but, i’m wondering if you have something for the workflow of the purchase orders.

    thanks.

  12. SB Says:

    Need R12 Setup steps for iProcurement.

  13. Subhajit Maji Says:

    Thanks a lot for the article. Really helpful for OraApps beginners like me.

  14. Ravikumar GN Says:

    PLease lt me know the requisition referance in the RFQ

  15. anusha Says:

    Hi

    Useful info, but we are still struggling to find GL_SL_link id in purchasing…which purchasing table on purchasing is this populated on? and also the GL transfer status from Purchasing

  16. ERP: POs and PRs for a particular project and expenditure type‏ - dBforums Says:

    [...] Hi, Use this link – it gives detail description of how to extract information from tables as per the requirements. PO: Tips and useful Query [...]

  17. Steve Holloway Says:

    PR Without PO

    This query is very useful.

    When I run in SQL and compare to the lines in the Autocreate form I do not see the lines without an Item.

    Can you modify the SQL to include lines where the Item field is not populated?

    Thank You

  18. Ayman Says:

    Both “PR without PO” and “PO without Requisition” links lead to the same file – prwithoutpo.txt. Could you please fix the second link?

  19. arya Says:

    Do you have query to view PR to Payment (PR-Negotiation-Quote-PO-Receipt-invoice-payment).

  20. Alette GarcĂ­a Says:

    Hi,
    The queries here are very useful.
    Just wanted to point that “PR without PO”
    and “PO without Requisition” redirect to the same query.

  21. shanky Says:

    I am working on pl/sql pacakge to generate sales report based on input start and end dates. this report should account for all payment adjustments, taxes, charges and Shipped Not Invoiced…need some queries / joins /relationships which will help me with this report…any help is really appreciated.

  22. prasanna Says:

    Hi Guys,

    I have some concerns about:

    For Stage 3: Invoicing details:

    We can get all the details in single view called ap_invoices_v

    as ‘select * from ap_invoices_v
    where po_number=’Po number value’

    This ap_invoices_v have all the details like
    1.Invoice matched with PO or not.
    If so only the po_number coloumn populated.

    at that time Release_number,receipt number cloumn showing as ‘UNMATCHED’

    Similarly for Receipt matching,Release matching.

    The above details which I have given is sample only.

    ap_invoices_v have lot of Needed details also.

    Kindly let me know for any concrens.

  23. Prasanna Says:

    Hi Shanky,

    I cant understand your Exact requirement.

    But for tax amount,payment details,Invoiced or not details you can refer ap_invoices_v.

    Kindly let us konw for any concerns.

  24. ACS811 Says:

    Great collection of SQLs.
    It helps for end to end diagonostics.

  25. varaprasad Says:

    Hi.. I need join condition between project accounting base tables to oe_order_lines_all table…

    here im unable to collet data..

    Please do the needfull

  26. varaprasad Says:

    hi.. Im unable to find the join condition between oe_order_lines and project accounting base table..

    please do the needfulll.

    Regards
    vara prasad

  27. bikram Says:

    good one

  28. Bardamu Says:

    Can someone explain to me what the abbreviations PR and PO means ? I hear this all the time at work, but I’ve never understood.

    Thank you.

  29. GG Says:

    Hi,

    PR – Purchase Requisition
    (Request for any New Item)
    PO – Purchase Order
    (Order for any New Item)

    rgds,
    GG

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.