Tables involved in Procure to Pay (P2P) Cycle - Oracle R12

Procure to Pay Lifecycle

Algorithmic representation of P2P Cycle

Purchase Requisition:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

Requisition_header_id is the link between PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL tables

Requisition_line_id is the link between PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL tables

Purchase Order:

PO_RELEASES_ALL

PO_HEADERS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_DISTRIBUTIONS_ALL

PO_header_id is the link between all these tables

Receipt:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

RCV_TRANSACTIONS

Shipment_header_id is the link between RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINE.

PO_header_id is the link between RCV_SHIPMENT_HEADERS and RCV_TRANSACTIONS

Quality results are stored in QA_RESULTS table

Once the Receipt process is completed, MTL_ONHAND_QUANTITIES table gets updated.

Invoices:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Invoice_id is the link between all these tables

Payments:

AP_INVOICE_PAYMENTS_ALL

AP_PAYMENT_SCHEDULES_ALL

Check’s information will be stored in AP_CHECKS_ALL table.

Check format information will be stored in AP_CHECK_FORMATS table.

Note:

RFQ & Quotation records also get stored in the same PO_HEADERS_ALL table, but with type_lookup_code as ‘RFQ’ and ‘QUOTATION’ respectively.

---X---

Period Close Reconciliation Report

The Period Close Reconciliation report is used to compare account balances with inventory value at period end. This report has replaced the following period close reports,

• Inventory value report
• All Inventories value report
• Transaction value historical summary report

This report will show the distribution and quantity valuations in addition to adjustments made.

When this report is run for an Open period, it becomes a simulation report and pulls data from the temp table that holds the data for the period. The data will be included for the dates the user submits as one of the parameters. The only difference between the simulation report and the actual report is that the word Simulation is included in the title of the report.

When this report is run for a Closed period, the report will show the real inventory value summary. The report will pull the data from the CST_PERIOD_CLOSE_SUMMARY table. The report will automatically summarize data for a period if it has not yet been done.

This summarization is determined by use of the profile option CST: Period Summary. There are two values for this Automatic and Manual. If the user does not want to summarize the periods as they close, the value needs to be set to Manual. The user can then run the summarization at a later date or even not at all.

Note:

If Period Close Reconciliation Report is not run then, the status of the Inventory Period will be 'closed but not summarized'. Once the Report is run, the status will be changed to 'Closed'.

---X---

How the Costing Organization is getting defaulted in Organization Parameters form ?

This depends on the control level of the attribute 'Inventory Asset Value'. If this is controlled at Master level, then whenever you change the master org of the inventory org, then the costing org will be defaulted to new master org.

If the attribute is controlled at Org level, then the costing org will not change & is defaulted to the child organization (i.e.) the Inventory organization itself.

Scenario:

For better understanding let’s consider the following scenario,

M1 - Master Organization

C1 - Inventory Organization

If attribute 'Inventory Asset Value' is set to Master level, then we observe that in the costing tab of org C1, we see master org M1. If we change the master org from M1 to say M2, then the costing org also changes to M2.

If attribute 'Inventory Asset value' is set at org level, then we observe that in the costing tab of org C1, we see costing org as C1. If we change the master org from M1 to say M2, the costing org does not change & is retained as C1.

Navigation:

Inventory > Setup > Items > Attribute Controls > Query for Inventory Asset Value

If the control is set at the Master Level, then it indicates that the child organization does not maintain inventory value and hence need not retain a cost. The cost should be maintained at the master organization and so the organization parameters automatically set the costing organization as the Item Master.

The parameter can be changed only in the following cases,

• No on-hand quantity should exist in the Organization.
• No transactions should have happened in the Organization.

---X---

Clicking Edit Requisition or View Requisition Details displays ‘You have insufficient privileges for the current operation. Please contact your System Administrator’ error message

From the Requisition Approval Notification, choosing either ‘Edit Requisition’ or ‘View Requisition Details’ displays an error on the next page. This is being accessed through the iProcurement responsibility.

Cause:

In order to use the ‘Edit Requisition’ or ‘View Requisition Details’ from notifications the user must have a Web enabled responsibility. If the responsibility is not web based, then the system will throw this error message.

Note:

The responsibility must be self service web application e.g. Internet procurement and not iProcurement Super User which is a core application responsibility (forms based responsibility) and won't work.

Solution:

To be able to use the edit/view link in the notification, do the following

1. Log in using System administrator responsibility
2. Navigate Security: Responsibility. Query for the responsibility of the approver and note down the menu that is assigned.
3. Go to Application > Menu and query for the appropriate menu
4. In the 'User Menu Name' field, query the Menu name associated to the responsibility
5. Assign one of the following submenus,

'Internet Procurement Home' (ICX_POR_SSP_HOME)

Or

'iProcurement: Homepage menu' (ICX_POR_HOMEPAGE_MENU)

6. Save & verify that the menu is compiled successfully
7. Clear Java Cache:

           Login with 'Functional Administrator' responsibility

           Navigate to Core Service > Caching Framework > Global Configuration

           Click on 'Clear All Cache'

8. Retest the issue.

---X---

Inventory period has a status of ‘Closed Not Summarized’

After closing the Inventory period, the Status is getting changed to ‘Closed not summarized’ not as ‘Closed’. Even though there is no real impact to operations due to this status, we will see why this happens.

Impact:

The period close reconciliation report was designed as a tool for matching the accounted value to the on-hand value on a period-by-period basis. Information for each period is mostly calculated independently.

For each period, we must arrive at two values: accounted and on-hand.

For the case of on-hand value, we take the current on-hand quantity out of Inventory's information table MTL_ONHAND_QUANTITY and revert transaction quantities in MTL_MATERIAL_TRANSACTIONS for all transactions after the end of the period being reconciled. After multiplying with historical cost at the end of the period, we can arrive at an ending on-hand value for this period.

For the case of accounted value, we need a baseline value and delta. The delta is obtained from rows in the MTL_TRANSACTION_ACCOUNTS distributions table for the respective period. The baseline value is from the previous periods ending on-hand value. The reason we use on-hand value instead of accounted value, as one might expect, is for the independence of each period's values.

The usage of this value ensures that each period begins with a "clean slate" and it is easier to find where discrepancies result from since we don't have to worry about mismatches in value being propagated from one period to the next.

Solution:

Until the Period Close Reconciliation Report is run the status of the Inventory period will be ‘Closed but not summarized’. Once the report is run, it will summarize and the status will be changed to ‘Closed’. 

To know more about Period Close Reconciliation report click the following link http://dj-oracleapps.blogspot.in/2014/08/period-close-reconciliation-report.html 

Note:

1. If the status of the Inventory Period of Dec and Jan is 'closed but not summarized', then first you need to run the Report for the month of Dec and then for Jan. Else you will encounter an error.
2. A 'Closed not Summarized' period is treated by Inventory in exactly the same way as a 'Closed' period (transaction entry not allowed). In addition, the accounting would have already been transferred to GL in both cases. The only thing different has to do with the availability of summary information. This information can be queried through the Accounting Periods form or through the Period Close Reconciliation Report.
3. If it is run on a 'Closed' period, it will simply pick up the summary information previously calculated. If it is run on an 'Open' period, it will run through a simulation of the summarization (can detect any potential errors before closing for real).

---X---

WSH_INVALID_TPDATES

Message – Latest ship date XXX is less than the earliest ship date XXX

Even though this exception is ‘Information only’ and does not restrict the process, over a period of time the shipping department fails to recognize a true exception.

The WSH_INVALID_TPDATES exception is logged depending on the following profiles,

OM: Sequence for TP Ship/Deliver Deadline                                                                                                                                 

OM: Source for TP Early Ship/Deliver Date 

If these profiles are not set and the order line's promise date (latest ship date) is less than the delivery detail creation date (earliest ship date), then the exception will be logged.

If the Transportation module is not used, the Exception WSH_INVALID_TPDATES message can be disabled.

Navigation: Shipping > Setup > Define Exceptions

Query the Exception WSH_INVALID_TPDATES

Uncheck the ‘Enabled’ checkbox to disable the exception.

---X---

ORA-01403: no data found in Package OE_Holds_PUB Procedure evaluate_holds_post_write

While saving the details entered in Sales order line, system throws error message "ORA-01403: no data found in Package OE_Holds_PUB Procedure evaluate_holds_post_write" 

Reason:

The problem is the Warehouse is not getting populated in the order line.

Solution:

Check whether the Warehouse field displays an organization at the header level. If not enter and save, by default the warehouse will be populated for all the lines & the issue will be resolved.

If the system did not populate the warehouse information to the line even after entering at the header level, enter the warehouse at the line level & save the transaction.

If the issue occurs consistently raise an SR to Oracle, after analyzing your system the service engineer will provide you a patch for resolving the issue.

Note:

Until you get a permanent fix, we can enter the warehouse in line level and complete the transactions.

---X---