Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« June 2011 | Main | August 2011 »

July 29, 2011

Database and Application administration essential for Oracle E-Business Suite R12 upgrade

Guest post by
Umesh Tanna, Senior Technology Architect, Infosys


Oracle E-business suite R12 upgrade is a major project that organization undertakes. Functional, technical and infrastructure administration teams are the primary groups that are involved in upgrade project from IT department.  Oracle Application Database Administration activities are very critical activities in upgrade project that is performed by Oracle Apps DBA. Following are most essential aspects of R12 upgrade project from the standpoint of Oracle Apps DBA.

Upgrade Solution

This is the overall blueprint of R12 upgrade project. Database, application, availability/fault tolerance, disaster recovery, node topologies, performance expectation and last but not least - supported upgrade paths/procedures by Oracle are the important considerations while designing the upgrade solution.

  • There is flexibility to choose from available upgrade paths based on the current application/database version.
  • If the existing version is very old, then upgrade solution has to first upgrade to intermediate version and then finally to latest target version.
  • Similarly, it is flexible to do both database and application upgrade in one large outage or one have choice of upgrading them separately over different week end to reduce the outage.
  • If we want to change the platform (Ex. UNIX to Linux) then Oracle provides cross platform migration solution for upgrade.
  • Another crucial decision while designing the solution is whether we are changing any architectural components or not as a part of the R12 upgrade.
  • If there is no fault tolerance in the existing architecture, upgrade project may be the right opportunity to introduce it for ex. clustering of database (Oracle RAC) and load balanced application servers are probable candidate.
  • In many cases where existing operating system version is quite old, in order to upgrade to latest version of R12, one has to upgrade the operating system first.

Perform due diligence of analyzing, evaluating, validating and then designing optimal upgrade solution that incorporates all the above aspects.

Research, Plan and Prepare

This is always self-explaining but is very important. Outcome of this should be documented steps and procedures to perform for upgrade. Give due focus to this activity to pre-empt the problems by proactively taking care of them.  Though Oracle R12.x upgrade manual is available and is the main source of information for this, however, many latest information/finding is published in Oracle support sites and it is worth discovering all relevant information of project interest. For ex. Oracle now publishes consolidated upgrade patch to take care of the problems/bugs experienced during upgrade and incorporating such patch in our upgrade project proactively, makes the upgrade smooth.  Reviewing the Readme, platform specific release note, Oracle upgrade center (A dedicated section in Oracle support site), Oracle white paper, and Oracle webcasts goes long way in preparing for smooth execution of upgrade.

Upgrade Iterations

Upgrade activity is highly complex activity and one has to expect that issues would be experienced that may be specific to organization's infrastructure, set up and configuration. Hence, multiple iterations of upgrade should be planned.  Though based on the overall complexity this may vary but minimum three to four iterations should be planned to identify all the issues, resolving them and recording the fix applied. Iteration is also required to benchmark the various database and other parameters to optimize the execution time of upgrade. Intention is to have subsequent iterations as much flawless as possible and have reasonable repository of known problems/fixes rather than having surprise during go live.

Go Live and Outage Planning

In general overall upgrade execution takes several hours for typically sized installation. It is not uncommon to see this is taking much longer for highly complex and large sized environment.

  • Identify the set of activities such as software installation that can be performed in advance to reduce the critical downtime.
  • For environment having multiple nodes, plan to complete the upgrade in one node and then clone that to other nodes to save time rather than applying patches in all nodes.
  • Merge the patches and then apply rather than applying individual large patch.
  • Use the database and other patching procedure parameters that offered maximum benefits during iterations.
  • Try to script as much work as possible rather than executing them interactively.
  • Plan and execute dry run immediately before the go live to rehearse from start to finish involving multiple teams to achieve maximum coordination and orchestrating entire go live event to perfection.


Execute with excellence throughout the duration of project.

  • Play attention to details. Don't assume or ignore. It may prove costly for ex. One must read Readme of patches to understand the pre-requisites and post -requisites action.
  • Keep various tracker for ex. Patch tracker, configuration tracker (to keep recording of configuration item changed), Oracle SR tracker. Such trackers are very useful over complete project duration to have repeatable process.
  • Avoid applying manual fix and insists on Oracle provide patch.
  • Be extremely cautious doing anything that is unsupported. 
  • Perform the technical procedures consistently in multiple iterations to have desired outcome (except those controlled change that is carried out as correction or to optimize execution time).
  • Upgrade project is long project.  Keep practice of documenting what you do during upgrade or after upgrade as a fix.

July 19, 2011

Centralized Procurement with Oracle Fusion


With the business expanding across globes and with manufacturing organizations resorting to sub-contracting, there is a need for organizations to look at procurement beyond their current markets.  This introduces the need for a centralized procurement function for better purchasing efficiency, better control over organization spend, and central and simpler management of contracts with suppliers.  Oracle Fusion Procurement introduces new and better features that aid organizations to better manage their procurement functions.  The following are the advantages of a centralized procurement function:

  • Better control over organization spend
  • Consolidated purchasing across business units
  • Leverage volume discounts by consolidated demand
  • Better supplier relationship management
  • Single point of contact in buying organization for supplier
  • Centralized contracts - easier implementation and better management
  • Consolidated measurement of supplier performance
  • Reduced overheads

Centralized Procurement in Oracle Fusion

Oracle Fusion uses the concept of Business Units and each business unit (BU) will be associated with a set of business functions.  The model also allows defining relationships between two business units as shown in the figure below. 


BU-Relationship-1.pngThe relationship between two BUs will be of the form Producer-Consumer where one of the business units will consume the services offered by another.  So a BU with requisition business function can consume the services of another BU that has purchasing business function.

In addition to the relationship between BUs, another important factor that drives purchasing functions is the relationship of the BUs with the suppliers and supplier sites.  In Fusion, a supplier is defined at global level and data related to a supplier can be accessed across BUs.  However, a supplier site is defined at the business unit level.  Each supplier site is owned by a Procurement BU.  These supplier sites can then be assigned to one or more requisitioning BU that the specific procurement BU serves.

With the above infrastructure that Oracle Fusion provides, it becomes easy now to define a centralized procurement organization (and even a centralized payables organization). Fusion allows defining an employee as a 'Procurement Agent'.  A procurement agent will be associated with one procurement BU and given access to one or more or all of the upstream requisitioning BUs of that procurement BU.  An agent can be a category manager, a VP of Purchasing or any other role that a procurement organization chooses to define.  But all procurement related roles will require the employee to be defined as an agent as a pre-requisite.

An agent will then access all requisitions that flow in from the upstream requisitioning organizations that s/he has been given access to.  The Process Demand page (formerly called the Demand Workbench in R12) will provide a single point access to all requisitions that the agent has access to.  The process demand page will allow the agent to either auto-create POs or use document builder to create a purchase order, blanket agreement or a sourcing negotiation as the agent may seem fit to meet the demand.

While creating a purchase order or a blanket agreement, Fusion ensures that the BU that raised the requisition is stamped on the document provided the supplier site assignment has the requisition BU also as the sold-to BU (see figure below for supplier site assignment).  If the sold-to BU is different, then the purchase order or blanket agreement will carry the Sold-to BU name instead of the Requisitioning BU.  In other words, the liability will remain with the organization that will make the payment to the supplier/site.  This is illustrated in the image below.


As can be seen from the above illustration, Oracle Fusion Procurement allows an organization to easily model its procurement function based on its needs.  This model allows setting up a basic, straight forward set-up of a business unit catering to its procurement needs to a complex set-up that may address global procurement needs.  Oracle Fusion also ensures that organizations that wish to set up a centralized procurement unit can do so with no issues related to ownership of liabilities, received goods or legal issues related to payment and international trade.


July 5, 2011

Landed cost for process industries - An Oracle solution

Look at the mirror directly or through spectacles, you are most probably seeing through it or seeing just the glass. Look deep into it - In all chance it contains sand from Australia, soda ash from China, both processed in Taiwan, manufactured in India using Italian machinery and recipe combination verified in American Laboratories. Each of these processes adds cost towards freight, storage, taxes and duty. Some part of the money you paid was actually for these. As a user you need not know the profitability and margins. But for the manufacturer this is of utmost importance. Normally the landed cost charges are applied to different items based on weight, volume and quantity.

Oracle Process Manufacturing Financials supports Landed Cost management (LCM), a web based application which is part of Oracle E-Business suite and a milestone for customers to track and control the cost of landed goods. Oracle Landed Cost management application is seamlessly integrated with Oracle Process Manufacturing (OPM), Oracle Purchasing, Oracle Inventory and Oracle Payables.

These costs are initially estimated as Estimated Landed Cost (ELC) and then updated with Actual Landed Cost (ALC), as when they become known, allocating them to shipments, orders, and products.  Accordingly the estimate versus the actual cost comparison through LCM workbench would highlight improvement actions on controlling the landed costs. Oracle Process manufacturing customers irrespective of using different cost methods can use the Landed cost management features.

OPM - Landed cost management application supports two basic scenarios of receiving flows. First one is Landed Cost management as Pre-receiving and the other is Landed Cost management as Servicing. Unique selection of either of the flow can be made and henceforth the Organization would operate exclusively as Landed Cost management for Pre-receiving or Servicing. Let us consider a routine procure to pay flow as an example and understand how the landed cost for items are arrived.

LCM as Pre-receiving:
LCM as Pre-receiving is used when we intend to calculate the Landed cost estimation before we receive the goods in Organization with reference to Purchase order. The LCM Pre-receiving functionality has the flexibility of varying the item quantity and price defined in Purchase order. The charges associated with the items are generated and validation is performed manually to derive the Estimated Landed Cost (ELC).  The Pre-receiving details from LCM module are passed on to the Receiving applications and receipt of goods and receiving transaction takes place for the same quantity.

LCM as Servicing:
The Servicing scenario is used when the receipt of items into inventory happens followed by automatic creation of Estimated Landed Cost. The receipt happens with reference to Purchase order quantity and price, which cannot be changed like pre-receiving.  This scenario is favorable when you assess that the landed cost of an item remains constant with known service providers without much variation to the charges.

Through Oracle Payables, the Item invoice and freight invoices are created and matched to the receipt of the item with actual prices. This information is passed back from Oracle payables module to the Landed cost management in order to calculate the Actual Landed Cost (ALC) and the relevant account postings happen to General Ledger. For both Pre-receiving and Servicing, the Actual Landed Cost could be viewed and compared with Estimated Landed Cost. This would help to analyze and control the Landed cost charges. The Oracle Process Manufacturing reports display the history of the landed cost adjustments made to the item.

Without knowing the landed cost of a product and realizing the profit margins would end up exhausting your pocket. These landed costs is usually 30 to 40% of the product cost which may even go beyond this if unnoticed and uncontrolled. Lot many hidden costs would put up your profitability at risk.  The usage of LCM application gives the opportunity to identify areas of potential cost reduction, more accurate product profitability reporting, and increased competitiveness by sourcing of materials from foreign locations.

July 4, 2011

Power of Empowering - The new perspective of ERP implementations

Objective of my blog is to present the power of empowering and new perspective of ERP implementation that can make the implementations easy.

The success of ERP does not mean to have very tight control in every transaction that need to be performed. Control without affecting the productivity is fine but excessive controls only adds headache for the people performing the transactions as well as their managers too. Let me share my experience in some of the ERP implementations where the employees are empowered and made meaningful decisions for the overall benefit of the business.

Case1:  In one of the ERP Implementation the department manager is very clear to define the requirements as not to impose the approval limits to the buyers as they are supposed to honor the demand that comes from the user department and know the price they need to pay. The approval limits will not only make the buyer limited about his responsibilities and increases the work throughout the approval hierarchy. Rather few reports have been made to know the purchase committed cost on a daily, weekly basis. The belief is that the buyer is supposed to know what he is ordering, how much he is ordering and at what price. If not the buyer is not suitable for the assignment they are assigned with. The buyers have already been approved by way of providing access to the system. Hence additional approvals are not required. This shows the confidence that the organization has put on the buyers and buyers were pride for performing their job without any limitations. This is the power of empowerment that eases the implementation.

Case2:In an another implementation we were demoing basic functionality of ERP and consultants have shown all the controls available in purchasing and payment process whereas not many controls were existed in Order management or sales invoicing process. Traditionally many controls were available for purchase and payment processes and less or nil controls for sales order processes. This made the client very surprised and their business processes requires more control on top line that comes from the sales orders invoicing than the payment process where which controls the bottom line. Most of the sales reps were new to the organization and less experienced. As we know every organization requires control of top and bottom lines. But lack of control on top line can result in no control on the bottom line. Hence it makes sense for the implementation team to put more controls on sales order processes than purchase processes. This is certainly a new perspective of ERP implementation and a shift from the traditional design.

Case3: In this case the ERP team is of the opinion that the there is need to establish a very tight control and provide access for the specific functions and menus for the users including department managers. The sponsor of the project made it very clear to the project team not to spend any effort and introduce such measures. The management was very clear that if a manager does not know what and how to perform his functions is not fit to be in that position. Hence grant all the accesses and leave it to the discretion of the manager to perform his duties. This shows the confidence and trust of the management had in the managers. This is again a shift from the traditional implementation.

There can be many more cases like this where ERP is used to empower the employees and instill controls where necessary only. The lesson here is empowering is the key to the success in the modern times.

Oracle Exadata and DataWarehousing Impact - Part II

The Oracle's strategy to handle big data and business intelligence together, has got 3 key players in the winning combination namely Oracle Database Enterprise Edition 11g R2, Oracle Business Intelligence Enterprise Edition 11g and Oracle Exadata Database machine. Few additional players play an equally important role and those being OBIEE Applications for ERP/CRM, and Oracle Industry Business Intelligence/Analytics applications.

In continuation of the Part I of this blog series which can be referred here, lets continue the journey deep into Exadata world.

Let me begin with a YouTube video that might just open up eyes for the need of Exadata, please refer to the Video ( for better insights on how Information explosion is a reality.

  • From a functional standpoint the critical needs for a Data Warehousing platform are following:
    Faster response time to queries for effective analysis
  • Near real-time query analysis without impacting OLTP systems - Data availability, and Latency
  • Hide complexity of underlying data sources - Data Integration, Data Virtualization
  • Easier maintenance and monitoring - Integration with central administration toolsets


Diagram 1 - Key pillars for Effective Data Warehouse


From the technical standpoint following are broadly the Data Warehousing platform needs:
A) Query Performance
B) Fast I/O and data transfers between storage and database servers
C) Flexible Partitioning, various Indexing Options and Effecient Cache management
D) In-Database Analytical processing capabilities and features - OLAP, Data Mining, Statistics etc
E) Effecient Compression techniques which can enhance not only storage options, but faster data scans
F) Support Massively parallel processing which is scalable to handle larger data sets
G) Data virtualization and integration capabilities - supporting variety of data source options

Here's what Oracle Exadata has in the offering, to manage the above mentioned functional and technical requirements for effecient Data Warehousing and Reporting Platform:

  1. Query Offload processing in the Storage
  2. Smart scans to increase query performance and eliminating the need for indexing as DBA's had common practice in past
  3. Smart Flash Cache in database machine vastly increases for various workloads
  4. Oracle Database support for Analytics features within the Database (Predictive modeling and other data mining and advanced analytics, Geospatial latitude and longitude stored as geocodes)
  5. Flexible partitioning, Bitmap indexing, join indexing, materialized views and result cache
  6. OLAP, Statistics, Spatial, Data Mining, Real-time transactional ETL, Efficient point queries
  7. Data intensive processing directly in the storage
  8. Hybrid columnar compression (effecient compression increases the user data scan rates)


In today's blog we will cover Hybrid Columnar Compression technique. I specifically picked this as the first topic, as there's two fold large impact that Hybrid Columnar compression technique provides namely

  • Ability to retain more data in-memory for faster query results, as compression allows for more space in-memory.
  • Large compression allows for storage space savings
  • In standard database the typical format of data storage is as follows:


    Diagram 2 - Storing rows of columns sequentially in standard databases


    The compression techniques look for redundant data as one of the key criteria to compress the data. If the above diagram is to be transposed into columns, and then looked at rows as compression there's a better likelyhood of finding more redundant data in same columns that can be compressed more effectively. Effectively the data is grouped by columns & then compressed. The diagram below shows how the transposed database table leverages the Hybrid Columnar compression. This can enhance compression by a factor of 10x-50x, which gives you more flexiblity to bring larger amount of data into your Cache


    Diagram3.bmpDiagram 3 - Storing the columns of rows as Columnar model of compression


    There are two modes in which compression techniques help
    a) Query Mode - used for data warehouses, with key focus on optimization for speed. The order of compression can be 10x with column based compression. The data scans/smart scans improve proportionally with the compression orders
    b) Archival Mode - for archiving the infrequently used data and key focus being on reducing the space. This works with the view that your data can be directly archived onto the tapes instead of via the storage. Additionally with columnar compression, it allows for more data to be available for querying which is otherwise going to be archived and then retrieval takes it own sweet time before it's ready for query.

    Next blog I will be covering Smart Scans and Smart Flash Cache options that really power pack the Exadata.


    1. YouTube video on Oracle Exadata need - Oracle Exadata, are you ready?

    Subscribe to this blog's feed

    Follow us on

    Blogger Profiles

    Infosys on Twitter