Skip to main content

PO: Tips and useful Query

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

Comments

Popular posts from this blog

R12 Oracle E-Business Tax Configuration Guide

Applies to: Oracle E-Business Tax - Version: 12.0 to 12.2 Information in this document applies to any platform. Abstract R12 Oracle E-Business Tax Configuration Guide Document History R12 Oracle E-Business Tax Configuration 1. Tax Basic Tax Configuration Tax Definition: comprises the tax data that you set up for each tax regime and tax that your company or institution is subject to. The Tax Authority designates the regulations and rates that apply to the tax regime. Required Task List a) External Dependencies Create First Party: Legal Entity and Establishments Create Reporting and Collecting Tax Authorities b) Tax Configuration Create Tax Authorities Party Tax Profiles Create Tax Regimes Create First Party Legal Entity Party Tax Profile Create Tax Create Tax Status Create Tax Jurisdictions Tax Rate Responsibility: Tax Manager Path: Tax Managers -> Tax Managers -> Home Complete the following setup tasks: -Legal Entities and Establishments -Reporting and Collecting Tax Authorities ...

Create Accounting for a Payment generates errors 95333 and 95359

Create Accounting for a Payment generates errors 95333 and 95359 Error: 95333: A conversion rate does not exist to convert USD to AUD for the conversion type Corporate and conversion date 20-MAR-09 for line -25. Please use the Daily Rates form in General Ledger to enter a conversion rate for these currencies, conversion date and conversion type. 95359: There is no accounted amount for the subledger journal entry line. Please inform your system administrator or support representative that: The source assigned to the accounting attribute Accounted Amount has no value for extract line number 88547. Please make sure the source assigned to the accounting attribute Accounted Amount has a valid value, or assign a different source to this accounting attribute. Solution: 1. Specify a conversion rate for the currencies and conversion date mentioned in the error message 95333 Navigation under the General Ledger responsibility: Setup > Currencies > Currency Rates Manager > Daily Rates ...

R12: Payables Holds Explained – Why They are Applied and How to Remove

Invoice Validation checks the matching, tax, period status, exchange rate, and distribution information for invoices you enter. If an invoice has a hold, you can release the hold by correcting the exception that caused Invoice Validation to apply the hold by updating the invoice or the purchase order, or changing the invoice tolerances. Then resubmit Invoice Validation to release the hold. In the Invoice Holds tab, you can manually release certain invoice holds, even if you have not resolved the matching error condition. Here are some of the regular holds and explanation Account Holds Payables uses holds with this type to identify invoices with invalid or incorrect accounts. An invalid account prevents payment of an invoice and prevents Payables from creating accounting entries for an invoice or payment. Dist Acct Invalid:  Invoice distribution distributed to invalid account. To release the holds you must correct account and resubmit invoice validation. ERV Acct Invalid: ...