Procure to Pay (p2p) Cycle/Process Step By Step Techno Functional - R12

 

Step 1: Create Requisition - PR.

Requisition: It is one of the purchasing documents, which will prepare by the employee who required the materials or Services, or Training and so on.

During the Purchasing application flow, three types of roles are involved. Those are:

Requestor: Employee who require or request for the materials.

Preparer: Employee who is going to prepare the Document.

Buyer: Employee who is having the authority to purchase materials.

These Requisitions contain three levels of information i.e. 1.header 2. Lines. 3. Distribution

They two types of Requisition 1. Internal Requisition 2. Purchase Requisition.       

1. Internal Requisition: Internal Requisition will be created by Employee and send to the warehouse to adjust items internally.

2. Purchase Requisition: It will be created by Employee and send to the Purchasing department to accrue goods from external sources/suppliers.

Navigation: Purchasing è Requisitions è Requisitions
OR

iProc – User – Resp è iProcurement Home Page è Click on Stores

Select the appropriate Store for which you want to create PR.
For Example: I selected “Internal OPEX”

  Some department will show and select the desired department.

After clicking on desired department, it will show the complete category as follows. Click on this. 

 Click on the “Add to Cart” button to add your desired Item in your shopping cart.

 Now enter the item description and click on “Continue”.

 Now your item added in your shopping cart.
Click on “View Cart and Checkout” button.

Here you can change the quantity and Price.
Click on “Save” for save your work and “Checkout” for proceed further. 

 Now you need to select Requisition Type here.
Charge account will automatically generate based on Item Category, which you have selected earlier.
You can submit this through “Submit” button or click on “Next” button for more information and final submission.

1. We can check approvals in Approval Section.
2. Any additional information we can see in Notes section.
3. If PR having any attachment then we can check the same in Attachment section.
Now click on “Next” button.

 Review the PR information and click on “Submit” button for final submission.

 PR Number has been generated.

 Once the PR will approve then we can proceed with PO Process (AutoCreate) in Purchasing (PO) module.

PR Technical Specification.

--Query to get PR Header information
select *
from po_requisition_headers_all
where segment1 = '160105193'

Its stores about requisition headers information. Need one row for each requisition header we create.
Each row contains the requisition number, PR preparer, status, and description etc.

--Query to get PR Line information
select *
from po_requisition_lines_all
where requisition_header_id = 498859 --Take from header table

Its stores about requisition lines information.
Need one row for each requisition line you create.
Each row contains the line number, item number, item category, item description, need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line etc.

--Query to get PR Distribution information
select *
from po_req_distributions_all
where requisition_line_id = 550111 --Take from line table

 Its stores information about the accounting distributions associated with each purchase requisition line. Each requisition line must have at least one accounting distribution. You need one row for each requisition distribution you create.

Link between PR Tables.

 --Query to get Complete PR Information
select prha.segment1 PR_NUM
,prha.description
,prha.authorization_status approval_status
,prla.item_description
,prla.quantity
,prla.unit_price
,gcc.CONCATENATED_SEGMENTS charge_account
from po_requisition_headers_all prha
,po_requisition_lines_all prla
,po_req_distributions_all prda
,gl_code_combinations_kfv gcc
where prha.requisition_header_id = prla.requisition_header_id
and prla.requisition_line_id = prda.requisition_line_id
and prda.distribution_id = gcc.code_combination_id
and prha.org_id = prla.org_id
and prha.requisition_header_id = 498859 --take from header table

Step 2: Create Purchase Order (PO).

It’s a legal document which will be created by Organization and send to the Supplier to buy goods or services.
They are Four Types of Purchase Order i.e.
1. Standard Purchase Order
2. Plan Purchase Order
3. Blanket Purchase Agreement
4. Contract Purchase Agreement

These are four level of information in purchase order.
1. Header
2. Lines
3. Shipment
4. Distribution
Purchase Order can be create in two ways: 1. Manual PO 2. AutoCreate PO

Navigation: PO Resp è AutoCreate (From PR) è Enter the Operating Unit, Requisition (earlier created PR) è click on Find è Select the Check Box è Finally click on Automatic

 New Document window will open. Enter the information if required.

 PO Number has been generated.

 

Enter the GST/Tax information.

Menu è Tools è India Tax Details è Enter Required Information and click on Apply button.

 Now click on “Approve” button for approval submission. Select check box under Approval section and click on OK button.

 

View approval action history.

Menu è Inquire è View Action History

 

 Now the PO is in Approved status. 

Three Match Approval Levels for PO.
2–Way: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.
3–Way: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid.
4–Way: Purchase order, receipt, inspection and invoice quantities must match within tolerance before the corresponding invoice can be paid. 

PO Technical Specification.

--Query to get PO Line information
select *
from po_lines_all
where po_header_id = 650371 --take from header table

This table stores the line information of a Purchasing Document.
Important columns of this table: PO_LINE_ID, PO_HEADER_ID, LINE_TYPE_ID, LINE_NUMBER, ITEM_ID, ORG_ID, CLOSED_CODE etc.

--Query to get PO Line Location information
select *
from po_line_locations_all
where po_header_id = 650371 --take from header table

This table contains the information related to purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line.
Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and price break information for blanket purchase orders.
Important columns are: LINE_LOCATION_ID, PO_HEADER_ID, PO_LINE_ID, QUANTITY, QUANTITY_RECEIVED, QUANTITY_ACCEPTED, QUANTITY_REJECTED, QUANTITY_BILLED, QUANTITY_CANCELLED, MATCH_OPTION etc.

--Query to get PO distiribution information.
select *
from po_distributions_all
where po_header_id = 650371 --take from header table

It’s contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment.
Each row includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases. PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order and release information. Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:

• If you AutoCreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution you copy on the purchase order.
• If you use a foreign currency on your purchase order, Oracle Purchasing stores currency conversion information in RATE and RATE_DATE.
• If you use encumbrance, GL_ENCUMBERED_DATE and GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create journal entries in Oracle General Ledger.
• If you do not AutoCreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table.
• If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release.
• If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release. If you cancel the distribution, Oracle Purchasing automatically updates QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also enters UNENCUMBERED_AMOUNT if you use encumbrance.

Link between PO Tables:
--Query to get PO information
select pha.segment1 po_num
,pha.authorization_status
,pha.CLOSED_CODE po_status
,pla.ITEM_DESCRIPTION
,pla.UNIT_PRICE
,pla.QUANTITY
,pla.CLOSED_CODE line_status
,plla.PROMISED_DATE
,plla.NEED_BY_DATE
,plla.match_option
,pda.po_distribution_id
from po_headers_all pha
,po_lines_all pla
,po_line_locations_all plla
,po_distributions_all pda
where pla.PO_LINE_ID = plla.PO_LINE_ID
and pha.PO_HEADER_ID= pla.PO_HEADER_ID
and plla.LINE_LOCATION_ID = pda.LINE_LOCATION_ID
and pha.org_id = pla.org_id
and pha.segment1 = '160403837' --take from header table 

Step 3: Receiving (Receipt).

Entering Delivery Information in the system is called a Receiving.

Navigation: Purchasing Responsibility è Receiving è Receipts

It will ask to choose organization where you want to receive the item.

After this, enter the PO number and find.

Receiving Items and Check Quantity in Inventory
By entering Purchase Order Number.
Enable Quantity Receive check box.

Check the GST tax details from

Menu è India Tax Details

After saving click on Header, button. And you can see receipt number: 1601007294

We have configured receipt routing as “Direct Deliver”, hence we can check receipt transaction summary directly.

To see transaction click on Transaction button.

Receiving Technical Specification.

--Query to get Receipt header information
select *
from rcv_shipment_headers
where receipt_num = '1601007294'

--Query to get Receipt line information
select *
from rcv_shipment_lines
where shipment_header_id = 564315 --take from header table

--Query to get Receipt transaction information
select *
from rcv_transactions
where shipment_header_id = 564315 --take from header table

Link between Receiving Tables:
--Query to get Receiving information 
SELECT rsh.receipt_num
        ,rsh.creation_date receipt_date
        ,rsh.receipt_source_code
        ,rsh.vendor_id
        ,rsh.vendor_site_id
        ,rsh.shipment_header_id
        ,rsl.quantity_shipped
        ,rsl.quantity_received
        ,rsl.item_description
        ,rsl.shipment_line_status_code
        ,rsl.destination_type_code
        ,rt.transaction_type
        ,rt.po_distribution_id
        ,rt.po_header_id
        ,rt.po_line_id
        ,rt.po_line_location_id
        ,rt.transaction_date
    FROM rcv_shipment_headers rsh
        ,rcv_shipment_lines rsl      
        ,rcv_transactions rt  
WHERE 1 = 1
     AND rsh.shipment_header_id = rsl.shipment_header_id
     AND rsl.shipment_line_id = rt.shipment_line_id
     AND rsh.shipment_header_id = rt.shipment_header_id    
     AND rt.transaction_type = 'RECEIVE'
     AND rt.po_header_id = 650371 --take from po headers

Step 4: AP Invoicing (Receipt Match).

Navigation: AP Responsibility è Oracle Payables è Invoices è Entry è Invoices

After creating the invoice, we have to do the 3 steps.
1. Validate Invoice
2. Approve Invoice
3. Create Accounting Transactions.

Enter the supplier and other required information and click on “Match” button for PO/Receipt match.

Enter the Receipt number in case Receipt match else enter PO Number for PO Match invoice.

After enter Receipt/PO Number, click on “Find” button.

Select the Match check box and click on “Match” button.

Now AP Invoice has been generated as “TestInv01”

We can see the PO and Receipt number in line information tab of AP invoice with tax information, which has applied at the time of PO creation.

It’s Automatically Match to Invoice and PO Number, Receipt Number and Quantity

Now validate this invoice.

Go to Action 1 è Validate Check box è OK

Now invoice in Validated status.

AP Invoice Technical Specification:

--Query to get Invoice header information
select *
from ap_invoices_all
where invoice_num = 'TestInv01'

--Query to get Invoice line information
select *
from ap_invoice_lines_all
where invoice_id = 590642 --take from invoice header

--Query to get Invoice Distribution information
select *
from ap_invoice_distributions_all
where invoice_id = 590642 --take from invoice header

Link between Receiving Tables.

--Query to get Invoicing information
select aia.invoice_num
      ,aia.invoice_date
      ,aia.vendor_id
      ,aia.invoice_AMOUNT
      ,ail.DESCRIPTION
      ,ail.MATCH_TYPE
      ,ail.line_type_lookup_code
      ,aid.DIST_CODE_COMBINATION_ID
  from ap_invoice_lines_all ail
      ,ap_invoices_all aia
      ,ap_invoice_distributions_all aid
 where ail.invoice_id = aia.invoice_id
   AND aia.org_id = ail.org_id
   AND ail.invoice_id = aid.invoice_id
   AND ail.line_number = aid.invoice_line_number
   AND aia.invoice_id = 590642 --take from invoice header

Step 5: Make Payment against Invoices.

Once the Invoice is created, approved and accounted.

Then we can make the Payments to the supplier.

There are three types of Payments.

Manual: If Organization is going to pay the amount by check or cash then it will be captured under the manual payment type.

Quick: System will automatically generate the payment transaction as per the Invoice number. System will automatically generate the Check number also.

Refund: If any refundable amount is there, we have to pay to the supplier, customer, or employee then those payments will be select as Refund type.

You can make payment through invoice form just click on Actions button then “Pay in Full”

Click on OK

Enter all payment related details and save it payment will be created.

AP Payment Technical Specification:

--Query to get payment information

select *
from ap_invoice_payments_all aip
where invoice_id = 590642 --take from invoice header

--Query to get check and bank information
select *
from ap_checks_all ac
where check_id = 520239 -- take from payment table

Link between AP Payment Tables.

--Query to get Invoice payment  information

select aip.AMOUNT pay_amount
      ,aip.ACCOUNTING_DATE
      ,ac.CHECK_NUMBER
      ,ac.CHECK_DATE
from ap_invoice_payments_all aip
    ,ap_checks_all ac
where 1=1
  AND NVL(aip.reversal_flag, 'N') <> 'Y'
  AND aip.check_id = ac.check_id
  AND aip.invoice_id = 590642 --take from invoice header

Step 6: Create Accounting.

Again, click on Action button and then click on Create Accounting. You can create final accounting here.

“Create Accounting” program runs in background and its and related programs must be completed with normal status. 

Step 7: Transfer in GL.

Run Transfer To GL program to transfer this payment to GL.

Program: Transfer Journal Entries to GL

After successful completion of these concurrent programs, journal entries will be transferred to General Ledger module. You can check these with Batch name.

General Ledger:

Navigation: General Ledger è Journals è Enter è Batch name or journal è Find

Procure to Payment (till GL) process has been completed.