Purchasing Interview Question and Answers

Explain about Accounts Payable.

The Accounts Payable application component records and manages accounting data for all vendors. It is also an integral part of the purchasing system: Deliveries and invoices are managed according to vendors. The system automatically triggers postings in response to the operative transactions. In the same way, the system supplies the Cash Management application component with figures from invoices in order to optimize liquidity planning.

What is the meaning of invoice?

An invoice or bill is a commercial document issued by a seller to the buyer, indicating the products, quantities, and agreed prices for products or services the seller has provided the buyer. An invoice indicates the buyer must pay the seller, according to the payment terms.

In the rental industry, an invoice must include a specific reference to the duration of the time being billed, so rather than quantity, price and discount the invoicing amount is based on quantity, price, discount and duration. Generally speaking each line of a rental invoice will refer to the actual hours, days, weeks, months etc being billed.

Can you give a sample Process Flow for Procure to Pay Cycle?

Process flow for Procure to pay will go through two departmentsv(Commercial & Finance)

Procure – Commercial Department The following steps involve to procure any item

-Received Requisition from concern Department

-Request for Quotation from Suppliers at least three

-Finalize the best Quotation by keeping in mind about our companies standard

-Check the Budget for the same

-Negotiate with supplier for more economic pricing and finalize the payment terms

-Process the PO and forward to the supplier to supply the goods and services

Pay Cycle – Finance Department

-The following steps need to be fulfil

-Invoice should be match with PO

-Invoice should has all the supporting documents such as PO copy,Delivery note duly signed by receiver (our staff who authorized to received goods / store keeper)

-If the invoice is for services then it should be forwarded to the concern department head or project manager for his confirmation of work done and his approval

-Even if it not the services invoice, it should forwarded to the concern person’s approval who -request the PO for the same

-Finance can reject the invoice if it is not budgeted and ask for the reasons.

-After receiving all the confirmation and approvals from the concern department heads the invoice will be update in to the accounting system first in order to avoid any duplication of Invoice and PO (it shown on accounting package if the invoice is duplicate if not, altelast it tells you if the PO already used or cancel)

What is the difference between EFT & Wire?

EFT and WIRE are the most popular form of electronic payment method. EFT stands for electronic fund transfer and it is one of the fastest mode of electronic payment after WIRE. EFT is a batch oriented mechanism for transfering funds from one bank to another because of which clearing & settlement takes around 2 to 4 days. On the other hand, WIRE is a RTGS i.e. real time gross settlement system of making the fund transfer on real time and gross basis. Clearing and settlement happens on the same day. WIRE is more expensive and faster than EFT.

How many types of purchase order types/agreements are there?

Standard Purchase Order: You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions. If you use encumbrance accounting, the purchase order may be encumbered since the required information is known

Planned PO : A planned purchase order is a long-term agreement committing to buy it

items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities and estimated cost.

EX: Buying goods for Christmas from a specific dealer.

Contract PO : You create contract purchase agreement with your supplier to agree on specific terms and conditions without indicating the goods and services that you will be purchasing i.e. for $ amount you must supply this much quantity. You can later issue standard PO referencing your contracts and you can encumber these purchase orders if you use encumbrance accounting.

Blanket PO: You create blanket purchase agreements when you know the detail of goods or services you plan to buy from a specific supplier in a period , but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.

A Blanket Purchase Agreement is a sort of contract between the you and ur supplier about the price at which you will purchase the items from the supplier in future. Here you enter the price of the item not the quantity of the items. When you create the release you enter the quantity of the items. The price is not updatable in the release. The quantity * price makes the Released Amount. Now suppose your contract with your supplier is such that you can only purchase the items worth a fixed amount against the contract.

Explain the P2P process flow?

Procure to pay (p2p) is a process of requesting, purchasing, receiving, paying for and accounting for goods and services.  Procure to Pay Lifecycle is one of the important business Process in Oracle Applications. It’s the flow that gets the goods required to do business.  It involves the transactional flow of data that is sent to a supplier as well as the data that surrounds the fulfillment of the actual order and payment for the product or service.

Create a requisition>> create RFQ>> create a quotation from quote analysis>> generate a PO>>receipt of material>> create Invoice in payables>> transfer to GL

What is a Purchase Requisition and define various requisitions?

It is a formal request intended to procure/buy something that is needed by the organization. It is created and approved by the department requiring the goods and services.

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.

Basically, requisitions are of two types:

Internal requisition and purchase requisition

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.

What are the base tables that are affected when you create a P.O?

PO_HEADERS_ALL (SEGMENT1 column in this table represents the Document number)

This table stores header information of a Purchasing Document. You need one row for each document you create.


This table stores the line information of a Purchasing Document


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.


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


This table stores the general information about the suppliers


This table stores information about the supplier sites. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information.


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.


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


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.

What is 2-way, 3-way, 4-way matching?

Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received.

Two–Way: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Three–Way: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Four–Way: Purchase order, receipt, accepted, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Payment to Supplier

Once invoices are validated, they can be paid. Payables integrates with Oracle Payments, the E-Business Suite payment engine, to handle every form of payment, including checks, manual payments, wire transfers, EDI payments, bank drafts, and electronic funds transfers.  Payables also integrates with Oracle Cash Management to support automatic or manual reconciliation of your payments with bank statements sent by the bank.

This is how the P2P Cycle occurs in Oracle Apps.

What are the major transactions in RECEIVING?

Purchase Order Receipts

Internal Requisition Receipts

Inventory Inter-Org Transfer Receipts

Customer Return Receipts

What is Pay on Receipt AutoInvoice Program?

By running this program, we can automatically create an invoice for a PO when we enter a receipt for the respective PO.

What is Invoice Validation Process?

Before you can pay or create accounting entries for any invoice, the Invoice Validation process must validate the invoice.

Invoice Validation checks the matching, tax, period status, exchange rate, and distribution information for invoices you enter and automatically applies holds to exception invoices. If an invoice has a hold, you can release the hold by correcting the exception that caused Invoice Validation to apply the hold by updating the invoice or the purchase order, or changing the invoice tolerances.

What is an approval hierarchy?

Approval hierarchies let you automatically route documents for approval. There are two kinds of approval hierarchies in Purchasing: position hierarchy and employee/supervisor relationships.

What are the documents that use PO_HEADERS_ALL?

The following are the documents that use PO_HEADERS_ALL

RFQ’s, Quotations, Standard Purchase Order, Planned Purchase Order, Blanket Purchase Order and Contracts

Can the original Purchase Order be viewed in any way without resorting to SQL, for a revised Purchase Order?

The original version of a revised PO cannot be viewed from the PO form or PO summary form. Information on the original PO can be obtained from the PO_HEADERS_ARCHIVE and PO_LINES_ARCHIVE tables using the PO_HEADER_ID column as a common reference using SQL only.

Can we automatically ‘Close’ the Purchase order without receiving the full quantity?

The Receipt Close Tolerance lets you specify a quantity percentage within which Purchasing closes a partially received shipment. For example, if your Receipt Close Tolerance is 5% and you receive 96% of an expected shipment, Purchasing automatically closes this shipment for receiving.

When does a Purchase Order line get the Status ‘Closed for Receiving’?

Goods have been received on the system against this line but an invoice has not been matched to the order.

Can we match an Invoice against a line even when it is ‘Closed for Invoicing’?

The Close for invoicing status does not prevent you from matching an invoice to a purchase order or to a receipt.

What does create internal order conc request do?

Create internal order request will transfer the IR info to OM interface tables.

Explain the Receipt Routing?

Receipt Routing is of three types: Direct, Standard and Inspection

In Direct once the goods arrive at the destination, we directly move them to a specific Sub-Inv

In Standard once the goods are at the destination, we receive it at the receiving point first and then move them to the Sub-Inv.

In Inspection once the goods are at the destination, we receive it at the receiving point and then we perform inspection and accordingly we either accept it or reject them.

What are the different Purchasing modes in Receiving?

There are three modes:

Online: Receipts are processed online. If there are any errors, they are shown on the FORM itself, and don’t let you IGNORE and PROCEED.

Immediate: Receipts are processed immediately, but no errors are shown. Errors are recorded in REC_TRANSACTION_INTERFACE table.

Batch: Receipts are processed in batch, but no errors are shown. Errors are recorded in REC_TRANSACTION_INTERFACE table.

Note: In all the above two cases, it requires Receiving Transaction Processor to be run periodically.

What fields will print on the purchase order?

The following fields will be printed on a purchase order: item description, unit of measure, quantity, unit price and total, supplier name and address, need by date, requestor name, deliver-to location and notes to supplier.

Is it possible to create a PO from an Internal Requisition?

No. PO can’t be created from Internal Requisition.

What factors decide whether the Requisition created by the system is Internal or Purchase?

Sourcing : Internal or Supplier

Make / Buy : Make or Buy

An approved supplier can perform which four activities (Business rules)?

PO Approval


Schedule Confirmation

Manufacture Link To Distributor

How will you enter the Supplier Acceptance of a PO?

Query for the required PO and choose Acceptances from Special Menu. The type acceptance can be defined in Quick Codes (All Terms Accepted, Partially accepted etc.)

How will you approve a quotation?

Navigate: RFQ and Quotations > Quote Analysis.

How will you decide the life of a Quotation to be expired after a certain period?

While approving the Quotation, enter an ‘Effective To’ date, so that the Quotation will not be available for reference after the entered date.

Can you limit the usage of Quotation Information only for creating Requisitions?

While approving the Quotation, select the ‘Shipment Approved Type’ as Requisition so that the Quotation information could be used only for Requisitions.

The other Types are All Orders (can be used in POs and Requisitions), Purchase Agreements and Standard Pos.

Where will you mention that auto numbering is required for your RFQs, Quotations and PO?

The mode of numbering of RFQs, Quotations and PO (Automatic or Manual and if Automatic, what should be the starting number) should be defined in the Purchasing Options.

While creating the Purchase Documents in the ‘Auto Create’ mode, within it, there is a ‘Manual Mode’. This Manual mode is required for what?

If the numbering method selected for the document being created is manual, we can enter the document number in the ‘Manual’ mode. More over, we can decide which Requisition lines we want to combine and where we want them to appear on the document.

Is it possible to enter Price Break information in an RFQ, if the RFQ Header Type is selected ‘Bid’?

No. The Price Break window will be available only for Catalog Quotation Class.

What are the different Currency conversion Types?

Corporate: This is an exchange rate, generally a standard market rule, determined by the senior financial management of an organization, which will be used throughout the organization.

Spot: An exchange rate entered to perform conversions based on the rate on a specific date. It applies to the immediate delivery of a currency.

User Defined: An exchange rate that is specified when a foreign currency journal entry is made, that does not have a daily exchange rate.

How will you ensure that Receipt Routing mentioned in a Requisition or PO is not override at the time of Receiving?

Set the profile option ‘RCV: Routing Override’ to No.

(But in our instance I am getting the profile option named as ‘RCV: Allow Routing Override’ and the Default value and user value field are grayed out, I am not able to fill any thing at user value also. why?)

How will you ensure that the Buyer name is enforced in a PO?

Set the Purchasing Option ‘Enforce Buyer Name’ to Yes.

Is it possible to receive and invoice items supplied by a supplier on Hold?

Yes. A supplier Hold doesn’t prevent from receiving and invoicing Pos placed earlier to the Hold.

How will you find out the number of documents assigned to a particular Buyer?

Navigation: Management > Manage Buyer Workload

Select the field ‘Action’ > Review

Enter the other required search criteria and press FIND

In the popup window we see the following details:

Buyer Assigned   Urgent Late Needed   Open Pos

How will you reassign the Buyer to even out the workload of Buyers?

Navigation: Management > Manage Buyer Workload

Select in the field ‘Action’ ‘Assign’

Enter the required search criteria and FIND

In the popup window, enter the New Buyer name, select the required lines & save. Upon saving, the lines will be reassigned to the new Buyer.

How will you identify the status of a Purchasing document?

If the status is to be seen for a specific document, go to the respective document-defining window and see the current status at the Status region.

If the status of any document is to be found out, go either to the ‘Notification window or the Action history window (Navigation: Special > View Action History).

What is the use of Sourcing Rule?

The planning process to create Purchase Requisition for Buy items & Internal Requisitions for Make Items uses a Sourcing Rule.

Is it possible to use a Sourcing Rule which will be available in all the organizations?

Provided that the Sourcing Rule is defined as Global, it will be available in all organizations.

Is it possible to access a Purchase Document currently under approval process using the entry window by anybody as per the access level nor security level?

No. Nobody can access it through the Entry window.

What are the reasons that an Express processor may reject lines due to validation failures?

If the item revision, Subinventory and locator is not available & the entered destination is final. (Revision, Subinventory & Locator are not obtainable as a default from the item definition.)

If the early / late receipt date tolerance is exceeded & the exception control is set to Reject.

If Lot / Serial information is required.

If Ship-to- location is not available & the destination is Receiving.

If the location is not available for Expense destination type & the destination is final destination.

If PO Supplier & Receipt Header Supplier do not match.

If other receipts are pending for the specified shipment.

If the Search Supplier Item Catalog window is not appearing while invoking from the Requisition or PO entry window, the System will use which criteria to search?

The values that are defaulted from the Requisition or PO will be used by the system to query the default source options.

Is it possible to change the supplier after approving the PO?

No. The supplier can’t be changed after approving the PO. If such situations arise, cancel the PO & create a new PO with the new supplier.

Where will you define the Line Type that has to be defaulted while creating a purchase document?

Enter the required line type in the field, ‘Line type’ in the Purchasing option.

When will you use Requisition preferences?

Requisition Preferences are used to enter information that are to be defaulted in a Requisition. When multiple requisitions are to be created with many data the same, all those data can be entered as a Requisition Preference. The preferences defined will be defaulted in all new Requisition Lines as long as the user is exiting the Application. The defined preferences are not saved to the database.



Difference between Org_id ,organization_id and operating_unit

For instance let us say you are having Inventory Store.
You have Inventory Stores in two different countries like India and USA
You have installed Oracle Apps single instance and entered all the suppliers information,customers data,tax rules etc.

And your business requirement is like this, all the rules and the data corresponding to indian store shouldnt be applicable/available to american store and vice versa.

So to meet the above requirements you will define two operating units one for india and one for USA.

Now each operating unit will have an ID known as ORG ID.

And we use ORG ID to secure our business data.

Now to do transactions for indian operations you need to define one responsibility and attach indian ORG ID to this responsibility.By this an indian will not see any data/rules applicable to USA.

Similarly you will define another responsibility for USA and attach USA’s ORG ID to this responsibility so that an american will not see any data/rules corresponding to India.

This is the concept of ORG ID.

Now let us see what is Organization ID.

Within India you have got various branches in different parts of the country in different states like AP,UP,MP etc.
And the business requirement is like this in AP you will maintain stock of Rice,UP for Wheat and MP for Maize.
To meet this business requirement you will define 3 Inventory Organizations under Indian Operating Unit to maintain the respective stocks of the states..

Each Inventory Organization will have an Organization ID.

This is the outline of the differences between ORG ID and Organization ID.

Navigation: Inventory (Responsibility) >> Setup >> Organizations >> Organizations

Both operating unit and inventory organization are organizations defined in the Oracle EBS (E-Business Suite). They differ in the organization classification which is highlighted in the red box in below images.

The information about all the organizations (including inventory organizations) are available in HR_ORGANIZATION_UNITS table.

The information about all the operating units are available in the HR_OPERATING_UNITStable.

An inventory organization is uniquely identified by ORGANIZATION_ID.

An operating unit is uniquely identified by ORG_ID in different tables for which the value is the ORGANIZATION_ID from HR_OPERATING_UNITS table.



  FROM hr_organization_units;


  FROM hr_operating_units;

MOAC (Multiple Organization Access Control) in Oracle E-Business Suite Release 12

With the release of EBS R12, Oracle has released some new features on how data is accessed through the applications and standard programs by the use of Multiple Organization Access Control (MOAC). MOAC allows you to create a security group which can contain many operating units and assign that to the User’s responsibility. All the forms that process OU striped data now allow you to pick an OU to work in from a list that contains all the OU you have access to. You will also find all the OU based reports have a parameter added for OU.

The new feature in R12 enables companies wanting to implement a shared services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications’ responsibility.

The setup is straight forward. You can define a security profile in the HR Security Profile form, adding Operating Units to it, and then you must run Security List Maintenance program before you can assign the security profile to the profile option MO: Security Profile for a responsibility.

MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Oracle Release 12 when the value of “MO: Security Profile” is set.

When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set. This enables us to use both R12 MOAC behavior and 11i behavior simultaneously in R 12. You can also choose to completely use one of them.

With MOAC, users can:

  • Perform multiple tasks across Operating Units without changing responsibilities such as invoice entry, order processing, bank payments etc. thus improving the efficiency of transactions for companies that have centralized business functions or operate Shared Service Centers
  • Obtain better information for decision making such as, accessing supplier and customer site levels details across multiple OUs
  • Speed up data entry
  • Reduce setup and maintenance of many responsibilities

How Does the R12 MOAC Defaulting Rules Work?

  1. If the profile option “MO: Security Profile” is not set, then “MO:
    Operating Unit” value is used as the default Operating Unit even if “MO:
    Default Operating Unit” profile is set to a different value.
  2. If the profile option “MO: Security Profile” is set and gives access to
    one Operating Unit, the default Operating Unit will return this value even if
    “MO: Default Operating Unit” is set to a different value.
  3. If the profile option “MO: Security Profile” is set and gives access to
    multiple Operating Units, then the profile value “MO: Default Operating Unit”
    if set is validated against the list of Operating Units in “MO: Security
    Profile”. If the Operating Unit is included in the security profile then it
    is returned as the default value. Otherwise there is no Operating Unit
    default. Moreover, if the Profile Option “MO: Default Operating Unit” is not
    set, then there is no default Operating Unit.

What is the impact to you?

With R12 the views owned by “apps” schema are replaced by synonyms to the base tables. These synonyms have security policies attached to them to provide the proper org_ID(s) in the where clause to retrieve the data (the database rewrites the SQL statement to include the conditions set by the security policy). You can read up on virtual private database (VPD) and its features in the Oracle Metalink note mentioned at top of the article. If your report or program uses these synonyms then you will want to choose the proper value for the Operating Unit Mode field. However, if your report or program does use the base tables then this field is not as important.

To understand which applications have multiple organizations access control feature enabled you can query a new table “FND_MO_PRODUCT_INIT). If the product that is enabled has a “Y” for status field then that product can use the MOAC feature.

Pre R12 Multi-Org Features

  • Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
  • A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB(USERENV (’CLIENT_INFO’), 1, 10)

R12 Multi-Org Features

  • Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
  • A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.

Pre-R12 you could set your SQL session context for multi-org with the following:

  • In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.

In R12 you can set your SQL session context for a single OU with the following:
execute mo_global.set_policy_context(’S’,101);

  • The ‘S’ means Single Org Context
  • 101 is the ORG_ID you want set

Also In R12 you can set your SQL session context for multiple OUs with the following:
execute mo_global.set_org_access(NULL,111,‘ONT’);

  • 111 is the Security Profile you want to use
  • ‘ONT’ is the application short name associated with the responsibility you will be using to find the security profiles:

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them:



How MOAC works technically:

MOAC is initialized when you open a Form, Oracle EBS page or a Report or submit the concurrent program. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operating units to which access is allowed is fetched and the list of values (LOV) is populated .This list of values is nothing but list of OUs associated with the Security Profile attached to MO: Security Profile. Security profiles are defined with the help of the HR responsibility. Then, default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”.

When the profile “MO: Security Profile” does not have a value, MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set.

MOAC setups:

Following are the basic steps to be performed in order to enable MOAC feature:

  1. Define Security Profiles (using form function ‘Define Global Security Profile’)
  • Enter a unique name for the security profile.
  • To restrict access by discrete list of organizations, select ‘Secure organizations by organization hierarchy and/or organization list for the Security Type’.
  • Check the Exclude Business Group check box to remove the business group in the list of organizations.
  • Use the Classification field to limit the list of values (LOV) in the Organization Name field. For example, if you select the classification to Operating Unit, only operating units will display in the LOV.
  • In the organization name field, select the Operating Unit for which you want access.

Repeat until you have included all organizations to which you need access.

  1. Run the concurrent program “Security List Maintenance Program” from the standard request submission form. The “Security List Maintenance Program” can be run for a single named security profile to prevent impact to other security profiles.
  2. Assign appropriate security to the profile option “MO: Security Profile” for your users and responsibilities
  • Navigate to the “System Administrator” responsibility > System Profile Options
  • Assign the security profiles to MO: Security Profile for your responsibilities and/or users.
  1. Assign a value for profile option “MO: Default Operating Unit” (Optional)
  • Navigate to System Administrator Responsibility > System Profile Options
  • Assign a default operating unit to “MO: Default Operating Unit” profile option for your responsibilities and/or user.
  1. Assign MO: Operating Unit (Mandatory for only Single Org or if MO: Security Profile is not defined)
  • Navigate to System Administrator Responsibility > System Profile Options
  • Assign the Operating unit to MO: Operating Unit profile option for your responsibility or user.

Note – From the above screen shots we can conclude that user with purchasing responsibility will be able to access data from two Operating Units Vision Operations and Vision Services.

Developer’s Insight:

To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.

The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user.

MOAC –Changes to Custom Code while upgrading to R12 from 11i-–During R12 upgrade the major task is to enable the MOAC feature to custom code. Following is the recommended approach to achieve MOAC implemented in real aspect to custom code:

  1. Multiple Organizations Views/Tables Changes

Single Organization View –

  • Drop the single organization view
  • Create a synonym with the same name as the obsolete single organization view
  • Attach a policy function to the synonym

Reference Views 

  • Add the ORG_ID column if it does not exist
  • Replace single organization views with _ALL tables for all except one, which must be a secured synonym
  • Include the ORG_ID filter in the where clause of the view to avoid the cartesian product, if the ORG_ID is the driving key or part of the composite key
  • Include the ORG_ID parameter in the columns based on functions, if necessary
  1. Enhancements to Forms

The multiple organizations setup and transaction forms must display the Operating Unit field. This allows users to select the operating unit and enter the setup or transaction for the operating unit. Oracle recommends deriving the operating units from the transaction attributes.

  1.  Enhancements to Reports and Concurrent Programs

  • You must remove references of CLIENT_INFO and NVL function to the ‘ORG_ID’ column in the reports.
  • Single Organization Reports—The operating unit mode for single organization reports are flagged as     ’SINGLE’ in the Define Concurrent Programs page.
  • Cross Organization Reports–The Operating Unit mode for cross organization reports are flagged as ‘MULTIPLE’ in the Define Concurrent Programs page.
  1. Enhancements to Public APIs
  • Do not use the multiple organizations temporary table directly in the SQL query.
  • Rewrite the SQL joins with two or more views to use just one secured synonym depending on the driving table for the query and replace the remaining views by _ALL tables.
  • Add the ORG_ID to the WHERE clause of the SQL to avoid cartesian joins for tables that include ORG_ID the composite or driving key.
  • Use MO_GLOBAL.Set_Policy_Context.
    This API has 2 parameters –1. Operating unit 2. Context
    Context has 2 values 1. M  2. S
    When policy context is set to ‘M’, data from all accessible Operating Units will be returned.
    When policy context is set to ‘S’, then only data from the specified Org_Id will be returned.
  • Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.
  1. Enhancements to Workflows

With multiple organizations access control, you must set the current organization ID and not the CLIENT_INFO org context. You must derive the current organization ID from item keys. Do not rely on MO: Security Profile, MO: Default Operating Unit, and MO: Operating Unit profile options when setting the organization context because the operating unit must be validated before initiating the workflow.

Query of Business Structure

select * from per_business_groups where business_group_id= 101

select * from hr_operating_units where organization_id = 112 — (ORG_ID)

select * from org_organization_definitions where OPERATING_UNIT = 112  — Organization_id exists in INV tables

select * from hr_locations where location_id = 142

select * from mtl_system_items_b where ORGANIZATION_ID = 133 — (Organization id of org_organization_definitions table

Open Oracle Concurrent Program Output Directly on WEB browser through Oracle Forms

–Add library on form



— Create CP

CREATE OR REPLACE PROCEDURE XXX_CALL_CP(errbuf out varchar2,retcode out varchar2)






— Create Procedure on form and call it on when-button-pressed


vlrequestid NUMBER := 0;

vlrphase VARCHAR2 (30);

vlrstatus VARCHAR2 (30);

vldphase VARCHAR2 (30);

vldstatus VARCHAR2 (30);

vlmessage VARCHAR2 (30);

vlwaitstatus BOOLEAN;



l_user_id NUMBER := -1;

l_resp_id NUMBER := -1;

l_application_id NUMBER := -1;


l_req_id NUMBER;

l_request_completed BOOLEAN := FALSE;


l_req_phase VARCHAR2(20);

l_req_status VARCHAR2(1000);

l_req_dev_phase VARCHAR2(1000);

l_req_dev_status VARCHAR2(1000);

l_req_message VARCHAR2(1000);


l_x varchar2(1);

l_y varchar2(1);

l_z varchar2(1);

l_a varchar2(1);

l_b varchar2(1);





l_user_id := fnd_global.user_id;

l_application_id := fnd_global.resp_appl_id;

l_resp_id := fnd_global.resp_id;

fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);


l_req_id := fnd_request.submit_request (‘XXX’,






:System.Message_Level := ’25’;


l_request_completed := fnd_concurrent.wait_for_request(request_id =>



,phase => l_req_phase

,status => l_req_status

,dev_phase => l_req_dev_phase

,dev_status => l_req_dev_status

,MESSAGE => l_req_message);


:System.Message_Level := ’25’;


editor_pkg.report(l_req_id, ‘Y’);







Standard API of Rice Components





































How many execution methods in Executable?

Ques:- How many execution methods in Executable?

Ans:- There are 12 execution methods in executable.

1.  Host

2.  PL/SQL Stored procedure

3.  Oracle Reports

4.  SQL *Loader

5.  SQL *Plus

6.  Java Stored Procedure

7.  Java Concurrent Program

8.  Immediate

9.  Multi Language Function

10. Spawned

11.  Request Set Stage Function

12.  Perl  Concurrent Program

How to Create a Concurrent Program in Oracle Apps.

Now I discuss with you How to Create a Concurrent Program in Oracle Apps.

We Create a concurrent program which execution method is PL/SQL Stored Procedure.

Step 1:- First we create a stored procedure in database.











ERRBUFF and RETCODE are the mendetory parameter.

Compile that procedure in database.

Step 2:- Now we create login Oracle Apps.

Step 3:- Select the Application Developer Responsibility.

Step 4:- Open the Executable Form

Navigation :- Application Developer Responsibility -> Concurrent -> Executable

Step 5:-

a)  Give the name of Executable(Which is Display for User).

b)  Give the short name of Executable(for Back end).

c)  Chose Application Name in which we want to register these Executable(we select “Work in Process”).

d)  Give Description(Its is not mandatory field).

e)  Chose Execution method Name from LOV. (we select  PL/SQL Stored Procedure)

f)  Give the procedure name in Execution File Name field.

g) Save it.

Step 6:- Open the Concurrent Program Form


Navigation :- Application Developer Responsibility -> Concurrent -> Program

Step 7:-

a) Give the Concurrent Program Name(Which is Display for User).

b) Give the short name of Program(for Back end).

c) Chose Execution method Name from LOV. (we select “Work in Process”)

d) Give the Executable Short name in Executable name,which is created in above step.

e) Save it.

Step 8:- Go to System Administrator Responsibility

Navigation :- System Administrator Responsibility -> Security -> Responsibility -> Request

Step 9:- Attach those Concurrent program in your request group.

a) In name field give the concurrent program name and save it.

Step 10:- Select our responsibility which is assign on our user.

Step 11:- Now we submit our Concurrent program.


Click on View from menu bar then click on request.

Click on Submit a New Request button,select Single Request then press Ok button.

Give Concurrent program Name, press Submit Button.Press No and find.

Now we see our concurrent program in SRS window.

Step 12:- See Output

Click on View output button and see Output “HELLO ORACLE”.