Oracle Procure to Pay Cycle in

R12
(Technical and Functional Flow)
By
Brinda

Brinda

Page | 1

15 Apr. 15

[Link]

Description

Page No.
3
5

Procure To Pay Cycle - Overview

1

Organization Structure to understand Procure to Pay

2
3

cycle process flow

Pre-Requisites for Purchase Order
How to create a Purchase Requisition in Oracle Apps
1 Functional Flow

6
8
9

.
2

15

Base tables for Purchase Requisition in Oracle

. Apps
Steps to create a Request for Quotation (RFQ) in

17

Oracle Apps
1 Functional Flow

18

.
2

Base tables for Request for Quotation (RFQ )in

23

. Oracle Apps
How to create Quotation and Perform Quote Analysis

23

in Oracle Apps
1 Functional Flow

23

.
Steps to create a Purchase order (PO) in Oracle Apps
1 Functional Flow

27
28

.
2

35

Base tables Purchase Order (PO) in Oracle Apps

.
Creating Receipts in Oracle Apps
1 Functional Flow

40
40

.
2

42

Base tables for Receipts in Oracle Apps

.
Verify Items in Inventory
1 Base tables for Inventory items in Oracle Apps

43
44

.
Creating and Making payments for an Invoice in

45

Oracle Apps
1 Functional Flow

45

Brinda

Page | 2

15 Apr. 15

2
10

51

Base tables for Invoices in Oracle Apps

.
Flow of Accounting Entries from AP to GL

53

1 Procure To Pay Cycle-Overview

Procure to Pay cycle in Oracle is the cycle which is concerned with
the procurement of the raw materials from suppliers to prepare the
finished goods to the step of payment to the supplier for the goods
purchased. The process flow remains the same across various versions of
Oracle Apps viz. 11i, R12 with slight variations in the options that are
present in each version.
In P2P cycle the primary modules included are PO, AR, AP, CM and
GL

with

an

integration

with

iSupplier, iExpenses, Services

other

modules

Procurement,

like iProcurement,

Sourcing,

Procurement

Contracts, Approval Management, EBTax, and Payments. Sometimes

considered

as

an

another

module,

SubLedger

Accounting

is

functionality which is integrated into other modules to generate

accounting entries for transactions generated for those modules.

P2P In Simple?
Procuring raw materials from suppliers which are required to
manufacture the final or finished goods. Paying the supplier from whom
the material was purchased.
Fig.1. P2P flow diagram

Brinda

Page | 3

15 Apr. 15

The requirement arises from requirements for items, and company

places order for items needed. The vendor supplies the items. Once items
are received payment will be made i.e an invoice will be raised and
payment will be made to the vendor. The payment can be of cash, cheque
etc. This is governed under cash management and finally the transaction
details will be accounted in GL.
This article discusses the steps involved in a Procure to Pay (P to P)
cycle applied in many business organizations.
1. Organization Structure to understand Procure to Pay cycle process flow
2. How to create a Purchase Requisition in Oracle Apps.
3. Steps to create a Request for Quotation (RFQ) in Oracle Apps
4. How to create Quotation and Perform Quote analysis in Oracle Apps
5. Steps to Create Purchase order (PO) in Oracle Apps
6. Creating Receipts in Oracle Apps
7. Verify Items in Inventory
8. Creating and Making Payments for an Invoice in Oracle Apps
9. Create Journal entries in General Ledger and post

Brinda

Page | 4

15 Apr. 15

The following is a high level process flow diagram of Procure to Pay cycle. It
details the series of transactions from procurement to payment and final
accounting.
Fig.2. Process Flow Diagram of P2P

1. Organization Structure to understand Procure to Pay

cycle process flow
The Procure to pay cycle is tightly linked to the organizational
structure. Hence, it is important to first understand the organization structure
of the business (also referred to as Org Structure in ERP consulting parlance) to
understand the business process flows. The organization structure specified
in this article is based on the Multi-Organization concepts as applicable
to Oracle Apps R12 version.
To give an illustration of why this is important, consider the following
example. When there is a requirement for a specific item to be procured, it is
essential to know which inventory organization (say Chicago manufacturing
plant) in the Organization Structure has the requirement for that item. It is also
essential

to

know

the

Operating

Unit

corresponding

to

the

Inventory

Organization for which the requisition is being made.

Brinda

Page | 5

15 Apr. 15

While creating a requisition, the details of both Operating Unit and

Inventory Organization would have to be provided. Hence, having a pictorial
representation of the organization structure as follows helps.
The Organization Structure represented in the diagram below can be
interpreted as follows:
1. There is a single Business Group which is like a parent group for
all the legal entities. A Business Group is not a legal entity and is
not a register enterprise.
2. There can be one or more Legal Entities. Legal Entities require
compliance with local laws both legal and accounting related.
3. The Primary Ledger is required for recording all the financial
transactions.
4. Operating Units are defined for each Legal Entity.
5. A Legal Entity can have one or more Operating Units. The
transactions in each Operating Unit are recorded in the Primary
Ledger tagged with that Operating Unit.
6. Inventory Organizations are defined for each Operating Unit.
These

represent

the

units

where

actually

production/manufacturing/Processing may take place.

2. Pre Requisites for Purchase Order:

1. Item Creation
2. Supplier Creation
3. Buyer Creation
------------------------------------------------1. Item Creation:
a. Attach the Responsibility called "Inventory Vision Operations
(USA)"
b. Open the Items form
i. Items=>Master Item
c. Select the Organization name - Vision Operations
d. Enter the Item Name, Item Description
i. Go to Inventory tab check the checkbox called
Inventory Item
ii. Go to purchasing tab check the check box called
Purchasable
e. Save

Brinda

Page | 6

15 Apr. 15

f. Go to Tools Menu => Organization Assignment option to assign

for the multiple Organizations.
Select * FROM MTL_SYSTEM_ITEMS_B where SEGMENT1='APACHE';
-- INVENTORY_ITEM_ID=20817
2. Supplier Creation:
a. Attach the Responsibility called "Purchasing Vision Operations
(USA)"
b. Go to the Supplier Form
Supply Base=>Suppliers
c. Enter the Supplier Name. Save. Supplier number will be created
automatically.
d. Select the Sites button enter the supplier site address and other
details
e. Go to the Contacts tab enter the Contact details
i.
Name
ii.
Phno
iii.
Position and so on.....
Select * FROM AP_SUPPLIERS where SEGMENT1='20067'

--VENDOR_ID= 4128
Select * FROM AP_SUPPLIER_SITES_ALL where VENDOR_ID= 4128

--VENDOR_SITE_ID=5193
Select * FROM PO_VENDOR_CONTACTS where VENDOR_SITE_ID
IN=5193

a.
b.

c.
d.
e.

f.
g.

3. Buyer Creation:
Attach the Responsibility called "US HRMS Manager"
Create Employee
People => Enter and Maintain=>Select New button
Enter EMP name
Select action option select "create Employment" select the option is
"Buyer"
Enter Data of Birth
Save => Ok => Employee number will be generated.
Go to System Administrator open the User form create or query user
Select the Person field attach the EMP name (Which we have created)
h. Save.
i. Go to Purchasing Responsibility
j. Open the Buyers form

Brinda

Page | 7

15 Apr. 15

k. Enter the employee name and save the transactions.

Setup => Personal =>Buyers
Select * FROM PER_ALL_PEOPLE_F where PERSON_ID='25'
--EMPLOYEE_NUMBER='1289' --PERSON_ID=13496

3. How to create a Purchase Requisition in Oracle Apps

What is Purchase Requisition?
Requisition is nothing but a formal request to buy something.
Requisitions represent the demand for any goods or services that an
organization needs. It is created and approved by the department
requiring the goods and services.
Fig.4. Purchase Requisition Flow

A purchase requisition typically contains the description and

quantity of the goods or services to be purchased, a required delivery
date, account number and the amount of money that the purchasing
department is authorized to spend for the goods or services. Often, the
names of suggested supply sources are also included.
Requisitions for goods and services:

Are generated by applications including Inventory, Work in Process

(WIP),

Material

Requirements

Planning

(MRP)

and

Order

Management.

Brinda

May be entered manually through Purchasing windows.

Page | 8

15 Apr. 15

May be entered using iProcurement.

May be imported from external systems.

There are two types of Requisitions

Internal Requisitions are created if the Items are to be obtained

from one Inventory location to another location within the same
organization. Here the source of the requisition would be
INVENTORY. There is no approval process for internal requisition.
Purchase Requisitions are created if the goods are obtained from
external suppliers. Here the source of the requisition would be
SUPPLIERS. The purchase requisitions are sent for approvals.
With Oracle Purchasing module, you can create, edit, and review
requisition information on- line. Then the Requisitions will go for
approvals from proper authorities.

1. Functional Flow
Navigation: Purchasing responsibility >> Requisitions >> Requisitions
Requisition is divided into two parts: header and lines
The header holds the general information about the requisition that
is related to all the lines where as the lines have the specific information
about the item to be purchased. Choose the requisition type (here it is
purchase requisition). Preparer is the default person who is creating the
requisition and cannot be changed. Item requester can be different from
preparer. You can give the description in the description field.
Navigate to lines tab and select the item that you wanted to
purchase and enter the quantity and need by date.
Fig.5. Purchase Requisition

Brinda

Page | 9

15 Apr. 15

Header

Lines

In the Source details tab, you can input detailed information. You
can give a specific note to the buyer which might give particular
information related to this purchase. You can also add buyers [Link]
required check box denotes that the purchase of the item requires a
request for quote
Document type, document and line are all completed by EBS
automatically when a requisition is created from a blank order, a contract
or a quotation.
Fig.6. Source Details Tab

Global is checked when a blanket purchase agreement for the entire

organization exists for the item being purchased.

Brinda

P a g e | 10

15 Apr. 15

Owning

organization

displays

the

organization

who

owns

the

agreement. This feature is usually seen when an organization is using a

centralized purchasing model.
Contract num along with rev references the associated contract
agreement with the supplier for specific terms and conditions
You can further add extra description in the details tab. You can notify
supplier that the item is needed urgently by checking urgent checkbox.
Note to receiver might give some information to the receiver when he
receives

the

item.

Transaction

nature

describes

the

nature

of

transaction. You can enter a reference number which provides a reference

to a document in another system such as a work order. You can select UN
number and hazard from list of values.
Fig.7. Details Tab

Requisitions can be added in any currency set up in EBS by identifying the

currency code and exchange rate type on currency tab.

Fig.8. Currency Tab

Brinda

P a g e | 11

15 Apr. 15

Select the Distributions tab and enter the charge account

Fig.9. Entering the Charge Account

Close the form and save your work. You will now notice that
Approve button is highlighted. Click the Approve button to submit this
requisition for approval.

Fig.10. Approving the Requisition

Brinda

P a g e | 12

15 Apr. 15

Fig.11. Submit for Approval

Click OK button to send the Approval notification to the concerned

person. Approval Hierarchies are used to route the documents to the
concerned person for Approval. The document can be approved or
rejected.

Brinda

P a g e | 13

15 Apr. 15

You can always check the Status of your requisition by

navigating

to

Requisition

summary.

Here

you

can

get

your

requisition details by entering your requisition number .

Requisitions>> Requisition Summary
Fig.12. Requisition Summary

Here, our requisition is approved. You can also view the Action
History of the requisition by navigating to Tools>> Action History.
Here the sequence of the steps involved (who has submitted the
requisition, and who has approved/ rejected the requisition) is shown:
Fig.13. Action history of Purchase Requisition

Brinda

P a g e | 14

15 Apr. 15

2. Base tables for Purchase Requisition in Oracle Apps

1.

PO_REQUISITION_HEADERS_ALL (SEGMENT1 column in this

table represents the requisition number).

This table stores Header information of a Purchase Requisition.
Important columns of this table:
REQUISITION_HEADER_ID:
generated Requisition identifier

It is a unique system

PREPARER_ID:
It is a unique identifier of the
employee who prepared the requisition
SEGMENT1:

It is the Requisition number

AUTHORIZATION_STATUS:

Authorization status type

TYPE_LOOKUP_CODE:

Requisition type

ORG_ID:
identifier

Unique Operating unit unique

You can get REQUISITION_HEADER_ID by executing the following

command:
Select REQUISITION_HEADER_ID from PO_REQUISITION_HEADERS_ALL
where SEGMENT1= 14373;
-- REQUISITION_HEADER_ID = 181283 (14373is our Requisition Number)
REQUISITION_HEADER_ID is the link between
PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL
2.

PO_REQUISITION_LINES_ALL
This table stores information about Requisition lines in a Purchase

Requisition. This table stores information related to the line number, item
number, item category, item description, item quantities, units, prices,
need-by date, deliver-to location, requestor, notes, and suggested supplier
information for the requisition line.

Brinda

P a g e | 15

15 Apr. 15

Important columns of this table:

REQUISITION_HEADER_ID:
Requisition identifier

It is a unique system generated

REQUISITION_LINE_ID:
Link between
PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL
LINE_NUM:

Indicates the Line number

LINE_TYPE_ID:

Indicates the Line type

CATEGORY_ID:
identifier

Unique Item category

ITEM_DESCRIPTION:
QUANTITY NUMBER:

Description of the Item

Quantity ordered

Select REQUISITION_LINE_ID From PO_REQUISITION_LINES_ALL Where

REQUISITION_HEADER_ID=181283;
-- REQUISITION_LINE_ID = 208463
3.

PO_REQ_DISTRIBUTIONS_ALL
This table stores information about the accounting distributions of a

requisition line. Each requisition line must have at least one accounting
distribution. Each row includes the Accounting Flexfield ID and Requisition
line quantity.
Important columns of this table:
DISTRIBUTION_ID:

Unique Requisition distribution

identifier
REQUISITION_LINE_ID:

Unique Requisition line identifier

CODE_COMBINATION_ID:

Unique General Ledger charge

account identifier
DISTRIBUTION_NUM:

Brinda

P a g e | 16

Distribution number

15 Apr. 15

Select DISTRIBUTION_ID from PO_REQ_DISTRIBUTIONS_ALL where

REQUISITION_LINE_ID= 208463;
-- DISTRIBUTION_ID= 206987
REQUISITION_LINE_ID is the link between the
PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL
Viewing results by executing SQL commands:
Select CREATION_DATE, AUTHORIZATION_STATUS, SEGMENT1,
TYPE_LOOKUP_CODE from PO_REQUISITION_HEADERS_ALL where
SEGMENT1= 14373;

Select CREATION_DATE, CREATED_BY, ORG_ID, REQ_LINE_QUANTITY from

PO_REQ_DISTRIBUTIONS_ALL where REQUISITION_LINE_ID=208463;

1 Steps to create a Request for Quotation (RFQ) in

Oracle Apps
Once the Requisition is Approved Buyer will prepare the RFQ document
which will be delivered to the supplier.
Request for Quotation (RFQ) is a formal request sent to the suppliers
to find the pricing and other information for an item or items. Based on the
information supplied, the supplier quotes a quotation against the RFQ form.
In general, RFQs are created before purchasing any item to actually
know the price quotes from one or more [Link] will respond for
that with quotation.
We have Three types of RFQ documents
Brinda

P a g e | 17

15 Apr. 15

 Bid RFQ: This will be prepared for the specific fixed quantity and
there won't be any PriceBreaks(Discounts).
Catalog RFQ: This will be created for the materials which we will
purchase from the suppliers regularly, and large number of
quantity. Here we can specify the PriceBreaks.
Standard RFQ: This will be prepared for the Items which we will
purchase only once not very often, Here we can include the
Discounts information at different quantity levels.
RFQ Information will be entered at 3 Level:
1)Header
2)Lines
3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)
Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and
we will enter the terms and condition details.

Payment Terms: When Organization is going to make the

payment and Interest rates
Fright Terms: Who is going to Bear the Transportation chargers
whether Buyer or Supplier
FOB (FreeOnBoard): If any materials damage or any missing
quantity is there then the responsibility of those materials.
Carrier
: In which Transportation Company Organization
Required Materials Transportation company Name.

1.1 Functional Flow

In Oracle EBS, RFQs can be auto created from an existing Purchase
Requisition or can be a fresh RFQ.
Lets auto create RFQ from an existing Purchase Requisition.
(Note: by selecting Auto Create, the system automatically fills the data
in the form based on the details provided in the Purchase Requisition
form. For a new RFQ, we need to enter the data manually)

Brinda

P a g e | 18

15 Apr. 15

Navigate to Purchasing>> Requisitions>> Requisitions Summary to

find a requisition number.
Fig.14. Requisition Headers Summary

I will auto create RFQ from an existing Purchase Requisition (here it is

14373).
Navigate to Purchasing>> Auto Create
Press Clear button and enter your Requisition number and click find.
[Link] Requisition Lines

Brinda

P a g e | 19

15 Apr. 15

Your Requisition summary is shown in the next screen.

Check box your Requisition line and in the Document Type, select RFQ
and click Automatic button to Auto Create RFQ.
Fig.16. Auto Create documents

It will navigate to a new window. Here the RFQ Type is Standard RFQ and
click create button.
Fig.17. New Document

In the next window, RFQ form is auto created from an existing Purchase
requisition.

Brinda

P a g e | 20

15 Apr. 15

Fig.18. Auto Create RFQ

Click Suppliers button and enter the details of the Suppliers to whom
you want to send this RFQ.
Fig.19. RFQ Suppliers

Click on Terms button and add payment terms and freight terms.
[Link] Terms

Brinda

P a g e | 21

15 Apr. 15

Click Price Breaks button

The Price Breaks form is generally used to enter pricing
information for the RFQ and to negotiate/bargain with the suppliers by
asking a discount. You can also provide multiple price breaks to receive a
different quotation from the suppliers by altering payment terms, quantity
etc.
Fig.21. RFQ Price Breakdowns

In the above picture, the first line shows the actual quantity is 250
and the actual price is 25. In the second line, we have asked for a discount
of 2% and the price has changed to 24.5. This way the above form is used
for negotiation and price break up. Close the form.
Change the status to Active and save the RFQ.
Fig.22. RFQ

Brinda

P a g e | 22

15 Apr. 15

Now the RFQ is Auto Created from an existing Purchase Requisition and
now we need to send this form to the Suppliers we selected in the above
RFQ form.
Run the Concurrent Request called Printed RFQ Report to print this RFQ
to send it to the suppliers

1.2 Base Tables and links in the backend

RFQquery

Select * from PO_HEADERS_ALL where SEGMENT1='309' AND

TYPE_LOOKUP_CODE='RFQ'--PO_HEADER_ID= 110407
Select * from PO_LINES_ALL where PO_HEADER_ID=110407
-- PO_LINE_ID=173282
Select * from PO_LINE_LOCATIONS_ALL where PO_LINE_ID= 173301
--LINE_LOCATION_ID= 264455

2 How to Create Quotation and Perform Quote Analysis

in Oracle Apps
A quotation is a suppliers response to RFQ. In this article we will discuss
how a quotation can be prepared from RFQ form.

2.1

Functional Flow
[Link] RFQ

Brinda

P a g e | 23

15 Apr. 15

The above picture shows a completed RFQ form. Now we will

create quotations based on the response from the suppliers with
relevant to this RFQ form.
You should get two new quotations against the two suppliers. (Here my
quotation numbers are 504 and 505).
Now, the corresponding supplier will send the quotation to quote for
the RFQ using
two ways:
1. Purchasing -> RFQs and Quoatations -> Quotations and entering the
quotation with the supplier name as the one which we specified in the RFQ
suppliers.(Fig.19.)
Fig.24. Quotations entered by Supplier

2. The second method is to create quotation directly from the RFQ:

Now we will create quotations based on the response from the
suppliers with relevant to this RFQ form.
Navigate to Tools>> Copy Document
Fig.25. Copy RFQ Document

Brinda

P a g e | 24

15 Apr. 15

Here we have selected the second supplier and entering the quotation
sent by the supplier. Click OK button.
Fig.26. Quotation Number

Navigate to RFQs and Quotations>> Quotations and query for

Quotation (504)
[Link](1)

Notice that the quotation has been created from RFQ number 308.
Change the status to Active. Similarly repeat the steps for Quotation
number 505 and save it.
Fig.27.b Quotation(2)

Brinda

P a g e | 25

15 Apr. 15

Quote Analysis
Quote analysis is the process of reviewing the quotations given by
the suppliers. The best quotation will be selected by analyzing certain
factors like price, quality, delivery time etc.
Navigate to RFQs and quotations>> Quote Analysis
Query by RFQ number and click Find button.
Fig.28. Find Quotations

Fig.29. Analyze Quotations

Brinda

P a g e | 26

15 Apr. 15

Here you can analyze all the quotations sent by the suppliers and select
the best supplier and click Approve Entire Quotation button.
[Link] Quotation

Click OK.
Fig.31. Approval Status

The next step is create a Purchase Order based on this Quotation

Brinda

P a g e | 27

15 Apr. 15

3 Steps to create a Purchase order (PO) in Oracle Apps

A Purchase order is a commercial document and first official order
issued by the buyer to the supplier, indicating types, quantities, and agreed
prices for products or services the supplier will provide to the buyer.
Basically, there are four types of Purchase Orders, viz
Standard

Used for One-time purchases for goods and services.

Purchase Order Here you know the item, price, payment terms an
Planned

delivery schedule
Created when you have long-term agreement with the

Purchase Order supplier. You must specify the details of goods and
services, payment terms and the tentative delivery
Blanket

schedule
Created when the details of items and services,

Purchase

payment terms are known but not specific about the

Agreement
Contract

delivery schedule
Created when the terms and conditions of a purchase

Purchase

are known but specific goods and services are not.

Agreement

3.1 Functional Flow

Lets create a Standard Purchase Order.
Here, we will Auto Create a Purchase Order based on Approved
Requisition.
Navigation: Purchasing>> Auto Create
Enter requisition number and click Find to navigate to Auto Create
documents window
Fig.32. Find Requisition Lines

Brinda

P a g e | 28

15 Apr. 15

Fig.33. Auto Create Document

Select your Requisition Line by ticking the check box next to the
Requisition Line
Action:

Create

Document

Standard

Type:
Grouping:

PO
Default

Click Automatic button and click create button in the New Document
form. By clicking Automatic button, a standard PO is created based on
the details provided in the Purchasing requisition.
You can select Supplier either in the New Document form or in the
Purchase Order form
Fig.34. New Document

Brinda

P a g e | 29

15 Apr. 15

Fig.35. Auto Create a PO

Status represents the status of the document. Incomplete is the default

status for all purchase orders until they are submitted for the first time for
approval.
Click Shipments button.
Shipment number, Org, Ship-to, UOM, Quantity and Need-by-date
are shown by default. These fields can be edited as needed. You can split
the lines and can change the ship-to Organization, quantity of items to be
shipped and delivery date.[you can request the supplier to supply some of
the quantities to be supplied by the need-by date you provided and rest of
the quantities (to the same ship-to address or a different address) to a
different need-by-date]
Fig.36. Shipments

Brinda

P a g e | 30

15 Apr. 15

Click More tab. The default values are generated.

Fig.37. Shipments

Receipt Close Tolerance (%) determines when this line will close for
receiving
Invoice Close Tolerance (%) determines when this line will close for
invoicing
A Purchase Order has three main close points: Closed for Receiving,
Closed for Invoicing (these two relate to a specific line) and Purchase
Order itself has a closed status.
The entire order will not close automatically if all the lines are not closed
for both receiving and invoicing.
A tolerance of 0% indicates that it will close when the total amount
received or invoiced equals the amount on the order, whereas a close

Brinda

P a g e | 31

15 Apr. 15

tolerance of 100% indicates that no receipts or invoices are required for

this order, which will close the lines for receiving as soon as the order is
approved.
Reviewing these default close tolerances on a regular basis is a good idea
to ensure orders are properly controlled and closed with minimal
intervention by the purchasing agent.
Select an option at Match approval [Link] determines the close
point for an order.
2-way determines Purchase Order and Invoice quantities must match with
in the tolerance before the corresponding invoice can be paid.
Quantity Billed = Quantity Ordered
Invoice Price = Purchase Order Price
3-way determines Purchase order, receipt, and invoice quantities must
match within tolerance before the corresponding invoice can be paid.
Quantity Billed = Quantity Ordered
Invoice Price = Purchase Order Price
Quantity Billed = quantity Received
4-way determines Purchase order, receipt, accepted, and invoice
quantities must match within tolerance before the corresponding invoice
can be paid.
Quantity Billed = Quantity Ordered
Invoice Price = Purchase Order Price
Quantity Billed = quantity Received
Quantity Billed= Quantity Accepted

Brinda

P a g e | 32

15 Apr. 15

Select an Invoice Match Option

The Invoice Match Option determines whether the invoice will be matched
to the Receipt or the PO when using 3-Way matching
Select PO when using 2-way and select PO or Receipt for 3-Way.
Enter the Receiving control information by clicking Receiving Controls
button
Fig.38. Receiving Controls

Enter distributions for the shipments by clicking Distributions button

Fig.39. Distributions

Brinda

P a g e | 33

15 Apr. 15

Click on More tab. The Requisition number from which this Purchase
Order has been created is shown by default.
Fig.40. Distributions

Click Terms to enter terms, conditions, and control information for

purchase orders.
Fig.41. Terms

Brinda

P a g e | 34

15 Apr. 15

Click Currency button to enter and change currency information

Save your work and click Approve button for Approval process.
Fig.42. Approve Document

Click OK and check the status in the Purchase Order

3.2 Base tables for Purchase Order (PO) in Oracle Apps

Base tables for Purchase Order are as follows:
1. PO_HEADERS_ALL (SEGMENT1 column in this table represents
the Document number)

Brinda

P a g e | 35

15 Apr. 15

This table stores header information of a Purchasing Document. You need

one row for each document you create.
The following are the documents that use PO_HEADERS_ALL
RFQs, Quotations, Standard Purchase Order, Planned Purchase
Order, Blanket Purchase Order and Contracts
Important columns of this table:
PO_HEADER_ID, SEGMENT1, TYPE_LOOKUP_CODE, VENDOR_ID,
VENDOR_SITE_ID, CLOSED_CODE
PO_HEADER_ID is a unique system generated primary key and is invisible to
the users.
SEGMENT1 is the document number
You can uniquely identify a row in PO_HEADERS_ALL using ORG_ID,
SEGMENT1, and TYPE_LOOKUP_CODE, or using PO_HEADER_ID.
Sample Queries:
Select PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE, CLOSED_CODE
from PO_HEADERS_ALL where SEGMENT1= 6044;
--PO_HEADER_ID=110415

2. PO_LINES_ALL
This table stores the line information of a Purchasing Document.
Important columns of this table:
PO_LINE_ID:

Brinda

Unique identifier of the Document Line

P a g e | 36

15 Apr. 15

PO_HEADER_ID:
Unique identifier of the Document Header (with
reference to PO_HEADERS_ALL, PO_LINE_ID)
LINE_TYPE_ID:
Unique identifier of Line_Type (with reference to
PO_HEADERS_ALL, PO_LINE_ID and PO_HEADER_ID)
LINE_NUMBER:

Line Number

ITEM_ID:

Unique Item Identifier

ORG_ID:

Unique Identifier of the Operating Unit

CLOSED_CODE:

Status of the Document

Sample Queries:
Select PO_LINE_ID from PO_LINES_ALL where PO_HEADER_ID= 110415;
-- PO_LINE_ID= 173312
3. PO_LINE_LOCATIONS_ALL
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.
There following are the seven documents that use shipment schedules:
1. RFQs
2. Quotations
3. Standard purchase orders
4. Planned purchase orders
5. Planned purchase order releases
6. Blanket purchase orders
7. Blanket purchase order releases
Each row includes the location, quantity, and dates for each shipment
schedule. Oracle Purchasing uses this information to record delivery

Brinda

P a g e | 37

15 Apr. 15

schedule information for purchase orders, and price break information for
blanket purchase orders, quotations and RFQs.
The following are the important columns of this table:
LINE_LOCATION_ID:
Unique Identifier of Document shipment
schedule
PO_HEADER_ID:

Unique Identifier of Document header.

(with reference to
PO_HEADERS_ALL.po_header_id.)
PO_LINE_ID:
Unique Identifier of Document line (with
reference to
QUANTITY:

PO_LINES_ALL.po_line_id)
Quantity ordered for Purchase Orders,

RFQs and Quota

QUANTITY_RECEIVED:
QUANTITY_ACCEPTED:
QUANTITY_REJECTED:
QUANTITY_BILLED:

tions
Quantity received until today
Quantity accepted after inspection
Quantity rejected after inspection
Quantity invoiced by Oracle

Payables
QUANTITY_CANCELLED:
TAXABLE_FLAG:

Quantity cancelled
Indicates whether the shipment is

taxable
ORG_ID:

Operating unit unique identifier

SHIP_TO_ORGANIZATION_ID:

Unique identifier of Ship-to organization

Sample Queries:
Select LINE_LOCATION_ID from PO_LINE_LOCATIONS_ALL where
PO_HEADER_ID= 110415 AND PO_LINE_ID= 173312;
--LINE_LOCATION_ID=264473
Select QUANTITY, QUANTITY_RECEIVED, QUANTITY_ACCEPTED,
QUANTITY_BILLED, QUANTITY_REJECTED, ORG_ID from
PO_LINE_LOCATIONS_ALL where LINE_LOCATION_ID= 264473;

4.

Brinda

PO_DISTRIBUTIONS_ALL

P a g e | 38

15 Apr. 15

This table contains the information related to accounting distribution of a

purchase order shipment line. You need one row for each distribution line
you attach to a purchase order shipment. There are four types of
documents using distributions in Oracle Purchasing:
1. Standard Purchase Orders
2. Planned Purchase Orders
3. Planned Purchase Order Releases
4. Blanket Purchase Order Releases
Each row includes the destination type, requestor ID, quantity ordered and
deliver-to location for the distribution.
Important columns of this table:
PO_DISTRIBUTION_ID:

This is the primary key for this table. It is

a unique Document Distribution identifier.

PO_HEADER_ID, PO_LINE_ID
LINE_LOCATION_ID:

Unique Identifier of the Document

Shipment Schedule (with reference to PO_LINE_LOCATIONS_ALL,

LINE_LOCATION_ID)
CODE_COMBINATION_ID:

Unique Identifier of General Ledger Charge

Account (with reference to

GL_CODE_COMBINATIONS.CODE_COMBINATION_ID)
REQ_DISTRIBUTION_ID:

Unique Identifier of a Requisition

distribution (with Reference to

PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID)
Sample Queries:

Brinda

P a g e | 39

15 Apr. 15

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE

LINE_LOCATION_ID=264440
--PO_DISTRIBUTION_ID=270905
************Here when we need to find the purchase order for a particular
Requisition, Follow this order of querying***********you will know the
requisition number through the segment1 of the
po_requisition_headers_all************
select requisition_header_id,last_update_date,segment1,created_by from
po_requisition_headers_all where SEGMENT1='14373'
--requisition_number --segment1=14352 --requisition_header_id=181283
select * from po_requisition_lines_all where requisition_header_id=181283
--requisition_line_id = 208463
select * from po_req_distributions_all where requisition_line_id=208463
--distribution_id = 206987
select * from po_distributions_all where req_distribution_id = 206987
--po_header_id=110414
select * from po_headers_all where po_header_id=110415
--segment1=6055 --PO Number
**************************
5.

AP_SUPPLIERS

This table stores the general information about the suppliers.

Select * from AP_SUPPLIERS where VENDOR_NAME like 'IN070sup%'
--VENDOR_ID=35201
6.

Brinda

AP_SUPPLIER_SITES_ALL

P a g e | 40

15 Apr. 15

This table stores information about the supplier sites. Each row includes the
site address, supplier reference, purchasing, payment, bank, and general
information.
Select * from AP_SUPPLIER_SITES_ALL where VENDOR_ID=35201
--VENDOR_SITE_ID=6939
7.

PO_RELEASES_ALL

This table stores information related to planned and blanket Purchase Order
releases. Each row includes the buyer, date, release status, and release
number. Each release must have at least one purchase order shipment.
8.

AP_SUPPLIER_CONTACTS

This table stores information about contacts related to Supplier site. Each
row includes contact name and site.
9.

PO_ACTION_HISTORY

This table stores information about the approval and control history of a
Purchasing Document. This table stores one record for each approval or
control action an employee takes on a purchase order, purchase
agreement, release or requisition.

4 Creating Receipts in Oracle Apps

Receipts are created to receive the items based on a Purchase Order

4.1 Functional Flow

Navigation>> Receiving>> Receipts
Enter the PO number and click Find button or you can simply click on Find
button to see the expected receipts.
Fig.43. Finding a receipt

Brinda

P a g e | 41

15 Apr. 15

Check the Lines you want to receive.

In the lower part of the screen, Purchasing displays the following detail
information for the current shipment line: Order Type, Order Number,
Source, Due Date, Item Description, Hazard class, Destination, UN
Number, Receiver Note, and Routing.
Fig.44. Receipt

Click on Header and save the receipt to get the receipt number
Fig.45. Receipt Header

Brinda

P a g e | 42

15 Apr. 15

Navigate back to Purchase Order, query for your Purchase Order and
select Shipments button and click on Status tab to verify that the
quantity ordered items match the quantity received and status should be
Closed for Receiving
Fig.46. Shipments

4.2 Base tables for Receipts in Oracle Apps

1)RCV_SHIPMENT_HEADERS
2)RCV_SHIPMENT_LINES
3)RCV_TRANSACTIONS

Note:
SHIPMENT_HEADER_ID is the link between RCV_SHIPMENT_HEADERS and
RCV_SHIPMENT_LINE

Brinda

P a g e | 43

15 Apr. 15

PO_HEADER_ID is the link between RCV_SHIPMENT_HEADERS and

RCV_TRANSACTION
Queries
Select * from RCV_SHIPMENT_HEADERS where RECEIPT_NUM='8462' and
VENDOR_ID=35201
--SHIPMENT_HEADER_ID=4811913
Select * from RCV_SHIPMENT_LINES where
SHIPMENT_HEADER_ID=4811913

--

SHIPMENT_LINE_ID=4802475 --ITEM_ID=87618
Select QUANTITY_SHIPPED, QUANTITY_RECEIVED,
UNIT_OF_MEASURE,ITEM_DESCRIPTION,ITEM_ID,SHIPMENT_LINE_STATUS_
CODE from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=4811913

SELECT * FROM RCV_TRANSACTIONS WHERE

SHIPMENT_HEADER_ID=4811913

5 Verify Items in Inventory

Navigate to Inventory>>On-Hand, Availability>>On-Hand Quantity
Query by your Item number and click Find
Fig.47. Query Material

Brinda

P a g e | 44

15 Apr. 15

Fig.48. Material Workbench

Click Availability
Fig.49. Availability

Brinda

P a g e | 45

15 Apr. 15

5.1

Base tables for Inventory items in Oracle Apps

1) MTL_SYSTEM_ITEMS_B
2) MTL_ONHAND_QUANTITIES

Note:
INVENTORY_ITEM_ID is the link between MTL_SYSTEM_ITEMS_B and
MTL_ONHAND_QUANTITIES
Queries
Select * from MTL_SYSTEM_ITEMS_B where SEGMENT1='U1029' and
ORGANIZATION_ID=204
--INVENTORY_ITEM_ID=87618
Select * from MTL_ONHAND_QUANTITIES where
INVENTORY_ITEM_ID=87618

Brinda

P a g e | 46

15 Apr. 15

Creating and Making payments for an Invoice in

Oracle Apps
Entering the Supplier Invoice
This is the first step in creating an invoice. You can manually enter
the details in the Invoice or you can generate Invoices from PO. Receipts
(you need to run a Concurrent Request names Pay on Receipt
AutoInvoice Program from Purchasing Module)

6.1 Functional Flow

Navigate to Payables>>Invoices>>Entry>>Invoices
Select your Operating Unit
Type: Standard
Enter PO number and press tab. Supplier details should be populated
automatically. Enter the Invoice date, Invoice number and Amount.
Fig.50. Invoice Header

Matching the Invoice to either a Purchase Order or a Receipt

If your Invoice has a Purchase Order associated with it, then you
can match the invoice quantity and price to that Purchase Order. If your
Purchase Order is setup as a 2 way match, then you match the Invoice
to the Purchase Order lines. If your Purchase Order is setup as a 3-way
match, then you match the Invoice to the Receipts.
Click on (B) Quick Match to match the Item related details to the Lines
tab
Now, Enter the amount to be invoiced for the item, Po line number and
shipment number in the Lines tab and save your work.
Now calculate tax for the amount you have to pay to the Supplier using
Calculate Tax button.
Brinda

P a g e | 47

15 Apr. 15

Fig.51. Invoice Lines

You will get the Tax amount in the Lines information along with their
distributions for
reviewing.
Fig.51. Tax Lines

The total amount to be paid will be generated along with the Taxation in
the Generals Tab. Now you need to change the header amount to
equivalent with this total to get your invoice validated.
Fig.52. Total Amount

Brinda

P a g e | 48

15 Apr. 15

Validating the Invoice

Once the invoice is entered into the system, you will need to run the
Invoice Validation Process to validate the Invoice. This can be done by
clicking Actions button or running an Invoice Validation Process.
The validation process performs a couple of processes. First, it checks to
see if it should apply any matching holds. Then it will calculate and apply
taxes, verify the GL period status, verify exchange rates, and verify
distribution information is valid.
Go to Actions, select Validate and click Ok.
Fig.53. Invoice Actions

The status should be Validated. Here my status shows Validated.

In case if the status shows as Needs Revalidation it is because after
Validating the Invoice, the tax has been calculated and the price is
updated which included the calculated price. So we need to update the
price at the top(Header) and do validation again.
Fig.54. Invoice Validated

Brinda

P a g e | 49

15 Apr. 15

Now the Invoice has been Validated, its time to make payment to the
Supplier.
Click Actions, Pay in Full and OK.
Fig.55. Invoice Actions

Enter the Payment date, Bank Account, Payment Method, Payment

Process Profile, payment Document and Document Number and
save your work.
Fig.56. Payments

Brinda

P a g e | 50

15 Apr. 15

Click Invoice Overview to view the Invoice details.

Fig.57. Invoice Overview

Click Invoice Workbench and click Actions button and select Create
Accounting and select Final Post and click OK.
Fig. 58. Create Accounting

The status of Accounted should be Yes. This step should transfer the
details into the General Ledger
Brinda

P a g e | 51

15 Apr. 15

Fig.59. Accounting Status

Go to Tools and click View accounting Events to see the result.

Fig.60. Accounting Events

Go to View>> Request>> Find to see the Concurrent Programs that are

generated.
Fig.61. Requests

Brinda

P a g e | 52

15 Apr. 15

6.2

Base tables for Invoices in Oracle Apps

1) AP_INVOICES_ALL
2) AP_INVOICE_LINES_ALL
3) AP_INVOICE_DISTRIBUTIONS_ALL
Invoices
SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='121342'
--INVOICE_ID=212192
--VENDOR_ID(LINK B/W AP INVOICE AND PO_VENDORS)=35201

SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=212192

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE

INVOICE_ID=212008
--ACCCOUNTING_EVENT_ID=3324070
--INVOICE_DISTRIBUTION_ID BETWEEN 3431484 AND 3431487

Invoice Holds if any

SELECT * FROM AP_HOLDS_ALL --INVOICE_ID
Release for Holds
SELECT * FROM AP_HOLDS_RELEASE_NAME_V
Payments
SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=212192
--INVOICE_PAYMENT_ID=157479
--CHECK_ID=77758
--ACCTS_PAY_CODE_COMBINATION_ID=12854

Brinda

P a g e | 53

15 Apr. 15

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE

INVOICE_ID=212008

--BATCH_ID=161884

Accounting Entries

SELECT *FROM AP_ACCOUNTING_EVENTS_ALL

SELECT * FROM AP_AE_HEADERS_ALL
SELECT * FROM AP_AE_LINES_ALL
Check Information
SELECT * FROM AP_CHECKS_ALL WHERE CHECK_ID=77758
--CHECKRUN_NAME=QUICK PAYMENT: ID=77758
Check Format
SELECT * FROM AP_CHECK_FORMATS;
SELECT * FROM AP_CHECKRUN_CONC_PROCESSES_ALL

7 Flow of Accounting Entries from AP to GL:

Oracle Table links to find out the payment Entries in GL for the invoice
that has been validated and Posted
--TO VIEW THE INVOICE DETAILS

SELECT*FROMAP_INVOICES_ALLWHEREINVOICE_NUM='11113'
INVOICE_ID=212192

SELECT*FROMAP_INVOICE_DISTRIBUTIONS_ALLWHERE
INVOICE_ID=212192
INVOICE_DISTRIBUTION_IDbetween3432322and3432325
=SOURCE_DISTRIBUTION_ID_NUM_1inXLA_DISTRIBUTION_LINKS
--TO VIEW THE SUB LEDGER ACCOUNTING DETAILS FOR THE INVOICE
Brinda

P a g e | 54

15 Apr. 15

SELECT*FROMXLA_DISTRIBUTION_LINKSWHERE
SOURCE_DISTRIBUTION_ID_NUM_1IN(3432322,3432323,3432324,
3432325)
AE_HEADER_ID=4947840

SELECT*FROMXLA_AE_LINESWHEREAE_HEADER_ID=4947840
GL_SL_LINK_IDBETWEEN6865867AND6865868
--TO VIEW THE ENTRY IN GL FROM XLA THROUGH GL_SL_LINK_ID

SELECT*FROMGL_IMPORT_REFERENCES
WHEREGL_SL_LINK_IDBETWEEN6865867AND6865868
JE_BATCH_ID=(2928906AND2928907)

SELECT*FROMGL_INTERFACEWHEREJE_BATCH_IDIN
( 2928906,2928907)
--TO VIEW JOURNAL IMPORT DETAILS:

SELECT*FROMGL_JE_HEADERSWHEREJE_BATCH_IDIN
(2928906,2928907)
JE_HEADER_ID=4682985AND4682986

SELECT*FROMGL_JE_LINESWHEREJE_HEADER_IDIN
(4682985,4682986)CODE_COMBINATION_ID=12854
PERIOD_NAME=Feb14LEDGER_ID=1,650

SELECT*FROMGL_JE_BATCHESWHEREJE_BATCH_IDIN
(2928906,2928907)
--TO VIEW POSTING IN GL

Brinda

P a g e | 55

15 Apr. 15

SELECT*FROMGL_BALANCES
WHERECODE_COMBINATION_ID=12854ANDCURRENCY_CODE='USD'
ANDPERIOD_NAME='Feb14'ANDLEDGER_ID=1

Brinda

P a g e | 56

15 Apr. 15