In this article, we shall try to understand the end to end process involved in the P2P process from a functional as well a technical point of view. We shall look at the navigation required during each step and the tables that get affected during that process.
High level Scenario: Consider a car company that buys tires from a third party supplier. And when the Supplier sends in those tires, the company fixes the tires to the cars in their assembly lines and continue their manufacturing process of building the cars. In this scenario, let us suppose the number of tires that this car company has, has fallen below a count of 100. That’s when the company’s procurement team requests the third party seller to replenish the tires inventory by sending out a new batch of tires.
In the above mentioned scenario, from an ERP perspective, the tires count is maintained in Inventory module. Suppliers are maintained in the Accounts Payables (AP) module. Procurement for new tires is made and requested from the Procurement module. And when the Invoice is paid – the Payment accounting is created as journals and printed as reports in the General Ledger module.
Thus, a P2P process flows through the following steps:
- Create / Raise requirement for the item.
- Create Requisition
- Request for Quotation
- Create Quotation
- Quotation Analysis
- Create Purchase Order
- Create Invoice
- Create Accounting (for Invoice)
- Interface to GL
- Journal Import and Create Journals
- Post the Journals
- Create Payment
- Create Accounting (for Payment)
- Interface to GL
- Journal Import and Create Journals
- Post the Journals
Step 1: Requesting for Item
Create an item – VI50000.
In the above image, we can see that for this item – in the General Planning tab –
- Inventory Planning Method is defined as Min-Max.
- Min-Max Quantity section the Minimum quantity is 100 and Maximum quantity is 1000.
- Make or Buy is – Buy
- And the Source Type is – Supplier.
That means, whenever the on-hand quantity of that item falls below a minimum of 100, then the Min-Max Planning report automatically replenishes the quantity of the item upto 1000. The tables affected during this step are:
Query to item details from the Item Master table: SELECT * FROM mtl_system_items_b WHERE segment1 = 'VI50000';
Query to find out the onhand quantity. SELECT * FROM mtl_onhand_quantities_detail WHERE inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b WHERE segment1 = 'VI50000'); SELECT * FROM mtl_onhand_quantities WHERE inventory_item_id IN (SELECT inventory_item_id FROM mtl_system_items_b WHERE segment1 = 'VI50000');
Step 2: Now, run the Min-Max Planning Report.
Inventory Super User -> Planning -> Min-Max Planning Report
Step 3: Upon Running this report – the process of creating a requisition starts. Once this Min-Max Planning report is completed, a record is inserted into the Po Requisitions Interface table. A record is inserted into the PO_REQUISITIONS_INTERFACE_ALL table.
--The record in this table can be identified using the item_id of the item that we are procuring. SELECT * FROM PO_REQUISITIONS_INTERFACE_ALL WHERE ITEM_ID IN (SELECT inventory_item_id FROM mtl_system_items_b WHERE segment1 = 'VI50000');
The PO_REQUISITIONS_INTERFACE_ALL table contains requisition information from other applications. Each row includes all the information necessary to create approved or unapproved requisitions in Oracle Purchasing. The Oracle Purchasing Requisition Import feature uses this information to create new requisition headers, lines and distributions.
Step 4 – Create Requisition: Run the “REQUISITION IMPORT” concurrent program to import the requisition from the interface table to the base tables, thereby creating the Requisition in Oracle.
The Requisition Import Concurrent Program validates the data in the PO_REQUISITIONS_INTERFACE_ALL table and derives additional information as required. Upon completion, data is loaded into the PO Requisition Base Tables. The base tables are:
In order to identify the record in these base table – get the inventory_item_id of the above item
SELECT requisition_header_id FROM PO_REQUISITION_LINES_ALL WHERE item_id IN (SELECT inventory_item_id FROM mtl_system_items_b WHERE segment1 = 'VI50000') and org_id = p_org_id; -- get the requisition_header_id and requisition_line_id SELECT segment1 "REQUISITION NUMBER" FROM PO_REQUISITION_HEADERS_ALL WHERE requisition_header_id = p_req_header_id AND org_id = p_org_id; -- use the requisition_header_id here SELECT code_combination_id FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID = p_req_line_id; -- use the requisition_line_id here
PO_REQ_DISTRIBUTIONS_ALL table contains the information about the accounting distributions associated with each requisition line. Each
requisition line must have at least one accounting distribution.
From the above query – get the code_combination_id value – that can used to get the accounting string.
SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE code_combination_id = p_code_combination_id -- get the accounting string. SELECT * FROM GL_CODE_COMBINATIONS WHERE code_combination_id = p_code_combination_id
Approved Requisitions can be accessed from – PO Super User -> Requisitions -> Requisition Summary.
If there are any errors then, then error records are written into PO_INTERFACE_ERRORS table.
The join is – PO_REQUISITIONS_INTERFACE_ALL.transaction_id = PO_INTERFACE_ERRORS.interface_transaction_id
STEP5 – REQUEST FOR QUOTATION – RFQ
Once the Requisition is created, now we have to Create RFQ – that is Request for Quotation – asking the Suppliers to provide their Quotation. Once we receive their quotations, we can decide which quote from the Suppliers is best for our requirement.
Navigation – Purchasing Super User -> RFQ’s and Quotations -> RFQ’s
Create an RFQ by selecting the following fields:
- Type – Standard RFQ (the other two types are Bid RFQ, Catalog RFQ).
- Status – Active
- Close date – provide a date until when this RFQ is valid
- Item – Item name
- Target Price – provide a target price
- Currency – Provide the currency details.
- Terms – Payment Terms, Freight Terms, FOB.
- Price Break – In the Price Break – you can provide the quantity and a discount.
And click on Save. Tables that get affected when Request for Quotation is created are:
SELECT * FROM po_headers_all WHERE segment1 = '1011' AND org_id = p_org_id AND type_lookup_code = 'RFQ'; SELECT * FROM PO_LINES_ALL WHERE po_header_id = p_header_id; -- use po_header_id from above
Click on Suppliers tab on the RFQ window to assign the RFQ to that particular Supplier. Here you can see that this RFQ has been assigned to a couple of Suppliers.
This information is stored in PO_RFQ_VENDORS table. This table stores the information about the set of suppliers assigned to an RFQ. Each row in this table identifies the Supplier who received this RFQ. In the above example, we would get 2 records.
SELECT * FROM po_rfq_vendors WHERE po_header_id = p_header_id; - get the po_header_id from above query
Once the RFQ is completed – run the PRINTED RFQ Report concurrent program that will send the RFQ details to the supplier via email / fax or other methods that have been entered. Once the below program completes, the status on the RFQ changes from ACTIVE to PRINTED.
Step 6 – Quotations
Now that an RFQ is created and sent out to Suppliers, the Suppliers send us their respective Quotes back. We select the required quote and create it in Oracle for record purpose. A Quote can be created from the RFQ screen itself. Open the RFQ -> Tools – Copy Document. Give the following values:
- Action – Entire RFQ
- Type – Standard Quotation
- Supplier – Select one of the two suppliers that we entered above.
- Site – Auto populates.
Once OK is pressed, a note appears that “A new Quotation has been copied from this RFQ. The Quotation document number is – ..
As can be seen above – a Quotation is created for the corresponding RFQ. And the tables affected are:
SELECT * FROM PO_HEADERS_ALL WHERE segment1 = '1013' --- quotation number AND org_id = p_org_id AND type_lookup_code = 'QUOTATION' SELECT * FROM PO_LINES_ALL WHERE po_header_id = p_header_id; -- po_header_id from above SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE po_header_id = p_header_id AND po_line_id = p_line_id AND org_id = p_org_id;
Click on Approve Button.
Step 7 – Quote Analysis
In this step – the Quote that was approved earlier is Analyzed and is approved. Navigation: Purchasing Super User -> RFQ’s and Quotations -> Quote Analysis -> Enter the RFQ Number. Click on Approve Entire Quotation
Step 8 – Create Purchase Order (PO)
Now that the Quote analysis is completed – we are ready to create a Purchase Order. Navigation – Purchasing Super User -> Purchase Orders -> Purchase Orders.
Enter the Supplier Name, Supplier Site, Bill-To, Ship-To, enter the Item information. As soon as we enter the quantity, the price changes to the discounted price on the quotation.
Click on Terms – and we have information about Payment Terms (30 Net or Immediate), Freight (Due), Carrier, FOB, Supplier Note, Receiver Note etc.
Click on Shipments – and we get information about the Ship-To organization Name, UOM, Quantity, Promised Date, Need By Date, Charge Account and Amount.
Click on Shipments – and More – we get information on how the Match Approval Level is defined. Whether it is a 2-way or 3-way or 4-way match approval. And Invoice Match Option – Receipt / PO.
Once the PO Is approved, we can check the PO details from the backend. The tables that get affected are:
SELECT * FROM PO_HEADERS_ALL WHERE segment1 = '10428' AND org_id = p_org_id; -- get po_header_id SELECT * FROM PO_LINES_ALL WHERE po_header_id = p_header_id; -- get po_line_id SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE po_header_id = p_header_id AND po_line_id = p_line_id AND org_id = p_org_id; -- get line_location_id SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE po_header_id = p_header_id AND po_line_id = p_line_id AND line_location_id = p_line_location_id AND org_id = p_org_id;
Step 9 – Receiving
Once the PO Is created, the Supplier sends the items to the Ship-To organization specified. And once we get the goods, we receive them in Oracle too. Navigation – Purchasing Super User -> Receiving -> Receipts. Enter the PO Number and click Find to get the receipt details.
Once it is saved, we receive it in EBS. The tables that get affected are:
SELECT * FROM RCV_SHIPMENT_HEADERS WHERE receipt_num = '1031' AND ship_to_org_id = p_ship_to_org_id; SELECT * FROM RCV_SHIPMENT_LINES WHERE shipment_header_id = p_shipment_header_id; SELECT * FROM RCV_TRANSACTIONS WHERE shipment_header_id = p_shipment_header_id;
Step 10 – Create Invoice (Payables Invoice)
Once we received the items, we create a Payables Invoice in order to send it to the Supplier so they can pay out that amount. The Invoice is created for the amount for which the PO was created for. In the PO Number field, enter the PO number that was created earlier. And Distribution Information can be seen by clicking All Distributions.
Click on Actions to Validate the Invoice.
After clicking validation – the status on the Invoice changes to “Validated”
Tables Affected after creating the invoice are:
SELECT * FROM AP_INVOICES_ALL WHERE invoice_num = 'Inv_V1'; SELECT * FROM ap_invoice_lines_all WHERE invoice_id = p_invoice_id; SELECT * FROM ap_invoice_distributions_all WHERE invoice_id = p_invoice_id;
Step 11 – Create Accounting
After the invoice is validate – Click Actions and Click Create Accounting.
When the Create Accounting program is run, it processes the eligible accounting events to create the subledger journal entries. This program:
- Validates and creates the journal entries.
- Transfers the journal entries in the current batch run to General Ledger and starts the GL posting process.
- Generates the Subledger Accounting Program Report, which has the results of the Create Accounting program.
Before running the Create Accounting program, the Accounting_event_id column in the AP_INVOICE_DISTRIBUTIONS_ALL is NULL. After running the program, the field is populated with the value that can be used in the Subledger Accounting (SLA) tables. The tables affected after running the Create Accounting are as below.
SELECT accounting_event_id FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id = p_invoice_id; -- get the accounting_event_id SELECT * FROM XLA_AE_HEADERS WHERE event_id = p_accounting_event_id; -- get the ae_header_id SELECT * FROM XLA_AE_LINES WHERE ae_header_id = p_xla_ae_header_id;
Step 12 – Journal Import
The Journal Import program transfers the data from the GL_INTERFACE table to the GL Base tables. The corresponding JE_Batch_id, JE_Header_ID, JE_LINE_NUM fields are populated. These can be used to check the data in the GL Base tables. The tables getting affected in GL are as below:
-- Get the je_batch_id, je_header_id and je_line_num values from the GL_Interface table. SELECT * FROM GL_JE_BATCHES WHERE je_batch_id = p_je_batch_id; SELECT * FROM GL_JE_HEADERS WHERE je_header_id = p_je_header_id; SELECT * FROM GL_JE_LINES WHERE je_header_id = p_je_header_id AND je_line_num = p_je_line_num;
Step 13 – Create Payment
Now that the Invoice was sent out to the Supplier and the Supplier pays the Invoice in full, then we record that payment in the EBS. The Navigation to the payment screen is AP Super User -> Payments -> Entry -> Payments. The other way is to go from the AP Invoice Screen. Go to the Invoice – Click Actions and Click – Pay in Full. The Payment screen opens up.
And once all the required fields are populated – click on Save. The following tables are populated with the payment data.
SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id IN (SELECT invoice_id FROM AP_INVOICES_ALL WHERE invoice_num = 'Inv_V1');
The AP_Invoice_Payment_Schedules_all table holds data about any pending amount on the invoice. It contains information about scheduled payments for an invoice. The AMOUNT_REMAINING columns holds the data for any amount that is pending on the invoice. The PAYMENT_STATUS_FLAG may be ‘Y’ for fully paid payment schedules, ‘N’ for unpaid scheduled payments, or ‘P’ for partially paid scheduled payments.
SELECT * FROM ap_payment_schedules_all WHERE invoice_id IN (SELECT invoice_id FROM AP_INVOICES_ALL WHERE invoice_num = 'Inv_V1'); -- get Check_id value
The AP_CHECKS_ALL table stores the information about any payments made to the Supplier or any refund provided to the Supplier. There exists a record for each activity in this table. This table stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. This table also stores Bank_Account_name, check_date, Check Number, Currency Code, Address Information etc..
SELECT * FROM ap_checks_all WHERE check_id = (SELECT check_id FROM ap_payment_schedules_all WHERE invoice_id = (SELECT invoice_id FROM AP_INVOICES_ALL WHERE invoice_num = 'Inv_V1'));
Step 14 – Create Accounting (for Payment)
Now that the Payment has been recorded in EBS (AP module), the information has to pass through the Subledger Accounting (SLA) and then to GL. In order for this to happen, on the Payments screen – click Action – and Click – Create Accounting.
Once this activity is completed, the Create Accounting Seeded concurrent program starts and initiates the transfer of data from AP to GL via GL interface. The following tables get affected during this process.
--Get the Accounting_event_id value from the Payments table above. SELECT accounting_event_id FROM AP_INVOICE_PAYMENTS_ALL WHERE invoice_id IN (SELECT invoice_id FROM AP_INVOICES_ALL WHERE invoice_num = 'Inv_V1'); SELECT * FROM XLA_EVENTS WHERE event_id = p_accounting_event_id; SELECT * FROM XLA_AE_HEADERS WHERE event_id = p_accounting_event_id; SELECT * FROM XLA_AE_LINES WHERE ae_header_id = p_xla_ae_header_id;
And once the Journal Import completes the journals can be seen from the GL base tables namely GL_JE_BATCHES,
GL_JE_HEADERS , GL_JE_LINES and upon posting the Journals – the GL data can be seen in the GL_Balances table.
That pretty much sums up the Procure to Pay Cycle in Oracle Apps.