Transcript
Oracle Procure to Pay Cycle in R12 (Technical and Functional Flow) By Brinda
Brinda
Page | 1
15 Apr. 15
S.No
Description
Page No. 3 5
Procure To Pay Cycle - Overview 1
Organization Structure to understand Procure to Pay 2 3
4
5
6
7
8
9
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
a
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 buyer’s name.RFQ 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 (14373 is 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, RFQ’s are created before purchasing any item to actually know the price quotes from one or more suppliers.Supplier 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, RFQ’s can be auto created from an existing Purchase Requisition or can be a fresh RFQ. Let’s 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. Fig.15.Find 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. Fig.20.RFQ 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 RFQ query
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 supplier’s response to RFQ. In this article we will discuss how a quotation can be prepared from RFQ form.
2.1
Functional Flow Fig.23.Completed 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 RFQ’s and Quotations>> Quotations and query for Quotation (504) Fig.27.a.Quotation(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 RFQ’s 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. Fig.30.Approve 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 Let’s 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 Level.This 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 RFQ’s, 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
6
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, it’s 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 * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='11113' INVOICE_ID=212192
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=212192 INVOICE_DISTRIBUTION_ID between 3432322 and 3432325 = SOURCE_DISTRIBUTION_ID_NUM_1 in XLA_DISTRIBUTION_LINKS --TO VIEW THE SUB LEDGER ACCOUNTING DETAILS FOR THE INVOICE Brinda
P a g e | 54
15 Apr. 15
SELECT * FROM XLA_DISTRIBUTION_LINKS WHERE SOURCE_DISTRIBUTION_ID_NUM_1 IN (3432322,3432323,3432324, 3432325) AE_HEADER_ID=4947840
SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=4947840 GL_SL_LINK_ID BETWEEN 6865867 AND 6865868 --TO VIEW THE ENTRY IN GL FROM XLA THROUGH GL_SL_LINK_ID
SELECT * FROM GL_IMPORT_REFERENCES WHERE GL_SL_LINK_ID BETWEEN 6865867 AND 6865868 JE_BATCH_ID=(2928906 AND 2928907)
SELECT * FROM GL_INTERFACE WHERE JE_BATCH_ID IN ( 2928906,2928907) --TO VIEW JOURNAL IMPORT DETAILS:
SELECT * FROM GL_JE_HEADERS WHERE JE_BATCH_ID IN (2928906,2928907) JE_HEADER_ID = 4682985 AND 4682986
SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID IN (4682985,4682986) CODE_COMBINATION_ID=12854 PERIOD_NAME=Feb14 LEDGER_ID=1,650
SELECT * FROM GL_JE_BATCHES WHERE JE_BATCH_ID IN (2928906,2928907) --TO VIEW POSTING IN GL
Brinda
P a g e | 55
15 Apr. 15
SELECT * FROM GL_BALANCES WHERE CODE_COMBINATION_ID=12854 AND CURRENCY_CODE='USD' AND PERIOD_NAME='Feb14' AND LEDGER_ID=1
Brinda
P a g e | 56
15 Apr. 15
