Oracle Apps

Oracle PO Approval Hierarchy Routing Issue Resolved by Ron Morrison, UGI

nospam@example.com (Barb Matthews) — Tue, 09 Nov 2010 09:00:50 -0700

Ron Morrison, from UGI, is this month’s winner of our newsletter article contest. We found Ron’s article interesting for two reasons: first, it shows his problem solving methodology that he uses when he finds an issue with Workflow. Ron worked together with Oracle Support to recommend a solution. Second, Oracle Support actually came up with a one-off bug fix for the problem, and then realized, before implementing it, that there was already a solution in place in another patch. This gives us some insight into Oracle’s problem solving methodology – they have a lot of code they’re working with, so you would expect that once in a while they might “re-fix” a problem because of issues searching for an existing fix.

Recently we encountered several PO Approvals that were routed to an incorrect routing path according to the HR hierarchy supervisor relationship after the 3rd notification approval time-out was encountered. I discovered that the hierarchy routing path was not including an edit restriction to only select supervisor relationships with an ASSIGNMENT_TYPE = ‘E’ when the PO_REQAPPROVAL_FINDAPPRV1.GetMgrHRHier was executed against the PER_ASSIGNMENTS_F table. The logic was retrieving both the active employee ASSIGNMENT_TYPE of ‘E’ and a Benefits ASSIGNMENT_TYPE of ‘B’ for the hierarchy information.

The affected employee had worked with one of our subsidiaries and left the subsidiary to work at the main company office. By leaving the subsidiary, the employee was set up with a Benefits end-date, and a benefits profile was established based on his number of years with the subsidiary, and thus generated an ASSIGNMENT_TYPE ‘B’ for the Benefits information. I discovered that the ASSIGNMENT_TYPE ‘B’ information was extracted when I executed the Oracle Diagnostics application under Purchasing for the specific PO that was routed incorrectly under the section for the PER_ASSIGNMENTS_F table information that was extracted.

I used Workflow builder to identify the process that was being executed, which identified the source code that was being executed. I submitted a Service Request to Oracle regarding this issue and recommended that an edit be added to the logic in the PO_REQAPPROVAL_FINDAPPRV1.GetMgrHRHier process to include a selection criteria of ASSIGNMENT_TYPE = ‘E’. Oracle agreed that this modification to the source would resolve this issue and assigned BUG 10035329.

Oracle provided us with a one off Patch 6616285 to correct this issue. They said to apply the patch to $PO_TOP/patch/115/sql/

Later, Oracle realized that they had already fixed the issue, but that we were on an older version of code. The final solution was to upgrade from version 115.22.11510.2 to version 115.22.11510.3 to get this fix. Since Patch 5451684 containing version 115.22.11510.3 is not generally released, we were asked to apply Patch 7109979.

The following screenshots show where and how to identify the problem source code information and the solution offered by Oracle.

When looking at the PER_ASSIGNMENT_F table you should also verify that ASSIGNMENT_TYPE = ‘E’ – not doing so allows the process to select both ASSIGNMENT_TYPE = ‘E’ or ‘B’ for benefits information. This should not be allowed in PO Approval flow.

What You Need to Know About the July CPU by Barbara Matthews

nospam@example.com (Barb Matthews) — Fri, 17 Sep 2010 05:05:37 -0700

Recently I listened to Integrigy's webcast about the database impact of Oracle's July 2010 CPU (Critical Patch Update). Here are just a few of the things I learned:

There are CPUs and PSUs. A PSU is a CPU with extra patches, ones that Oracle thinks would be helpful to you and that are not considered high risk. If you apply a PSU, then you have to continue applying PSUs until you upgrade to a new version of your database. PSUs are well and thoroughly tested, but not necessarily by the E-Business Suite Development Group. So, you might hit a problem with a PSU in your E-Business Suite environment. But Oracle stands by those patches, so if you hit a problem, they'll help you work through it.
If you are being a good defender of your database, you should be staying within P-1 as far as the CPUs or PSUs are concerned. That means that at the very least, you should have tested and then applied the April 2010 CPU or PSU to your production environment. And if you are really on the ball, you will already be testing the July 2010 CPU. Now, say you've applied that July 2010 CPU, and now you're getting ready to upgrade your database from RDBMS Version 10.2.0.4 to 11.2.0.1. Since 11gR2 was released PRIOR TO the July 2010 CPU, then when you're done upgrading, you must remember to RE-APPLY the July 2010 CPU. Change control really is a... challenge.

Now, you might wonder just how dangerous it is to be behind on your CPUs or PSUs. The Integrigy speaker was kind enough to show us how to google a snippet of code that could exploit a vulnerability, drop it into Oracle Developer, and then, well, exploit the vulnerability. My mother could take you down if she knew how to cut and paste.

You can limit some of your risk from outside attacks by enabling the Managed SQL*NET Access and Server Security features. This feature will limit the people who can attack you to those whom you have given access to. You can still be hacked, so your best line of defense is to test and apply the CPUs as quickly as possible. More details about how to enable these features, and what enabling them entails, are covered in Integrigy’s April 12, 2007 Security Advisory. There are also a number of other recommendations in this document about how to reduce your security risk.

I recommend that you put the Integrigy website on your Favorites list, and check them out whenever a new quarterly CPU/PSU becomes available. They do a terrific job of evaluating and explaining what the security patches do, and what your risks are if you choose not to apply them. If you missed out on Integrigy’s webcast, they have more coming up:

Oracle Critical Patch Update October 2010
E-Business Suite Impact Webinar
Thursday, October 21, 2pm EDT

Oracle Critical Patch Update October 2010
Oracle Database Impact Webinar
Thursday, October 28, 2pm EDT

Two Instances - Getting Unstuck from 11i by Chris Busbee, eprentise

nospam@example.com (Barb Matthews) — Thu, 16 Sep 2010 18:04:23 -0700

Summary Comparison Between Migration and Consolidation
If you are running Oracle® E-Business Suite (EBS) and have an 11i instance and a R12 instance in production, there are two approaches to get from multiple instances and reach the goal of a single EBS R12 instance in which to run an entire business:

· data migration plus sunset instance, and

· an upgrade of the 11i instance to R12 followed by eprentise Consolidation.

Migration is the way things have been done in the past when it was necessary to combine two instances, and it is characterized as relying on highly skilled technical staff, is labor intensive, and is supported by very general purpose software utilities such as Oracle Data Loader. The end result is usually a compromise between schedule, business, and technical constraints. Migration generally takes a long time and is expensive; it has been the only game in town and follows practices and techniques generally used in the Oracle EBS community.

Consolidation is new, characterized as relying on a purpose-specific software product that displaces labor, elapsed time, and technical risk. The end result has no business or IT compromises. We have developed the eprentise Consolidation software to meet Oracle EBS customer needs, reduce the costs of EBS, and improve the business results.

Migration
A migration approach generally calls for data to be extracted from one instance, transformed by custom scripts, and loaded into a new instance. During a migration companies generally only bring master data, open business transactions, and 1 - 2 years of closed transactional data into the new global instance. As a result, one of the byproducts of the migration approach is a recycled instance that must remain in a "sunset" state for 5 - 10 years, the exact duration being based on external regulations for data retention and internal business operational requirements.

Consolidation
The consolidation approach involves a software-assisted combination of all data from all current instances, whether your current instances include 11i, R12, or both. If you are running both 11i and R12, this approach calls for Oracle's standard upgrade process to transform any 11i instances into new R12 instances. The next step of the approach relies on eprentise Consolidation software to combine the newly upgraded R12 instance data and setup configurations into the existing R12 instance, resulting in a single, global R12 instance. If you are only running two 11i instances, eprentise consolidation becomes the first stage of the process, followed by a single Oracle upgrade to R12. If you are only running two or more R12instances, an upgrade is eliminated and consolidation becomes the only stage of the process.

Timewise, it may be difficult to run an upgrade and the eprentise Consolidation in a single cutover weekend. In that case, the newly upgraded R12 instance will have to go into production and all upstream and downstream interfaces will need to be reviewed and possibly modified to work in the R12 environment. So you don't need to rewrite the interfaces twice (once for the upgrade to R12, and once for after the instances are consolidated), it will be necessary to ensure that the interfaces are re-written to utilize the reference data (such as customer number, invoice number, vendor number, item number, etc.) rather than the system identifiers such as customer_id, invoice_id, vendor_id, and item_id.

Comparative Costs of Migration and Consolidation Approaches
The following example spreadsheets calculate the minimum and maximum costs for resources for a traditional migration approach compared to the cost of resources for the eprentise Consolidation approach. Neither example includes resources for creating and testing interfaces, project management, or database administration since those would be similar for both approaches. The minimum is based on the shortest time and the fewest resources in the above diagram, while the maximum is based on the longest duration and the most resources. A standard of 12 modules and a consulting rate of $1,000/day were used in the calculations for all examples. All of the examples utilize a 50-week year and five day work weeks. The total cost of resources for a traditional migration approach ranges from a minimum of $9,000,000 (12 months duration and 3 resources) to a maximum of $27,000,000 (18 months duration and 6 resources).

The major difference between the eprentise Consolidation Approach and the Migration Approach is that since there are no scripts to write, test, and revise, the only required resources are business users who would test the functionality of the resulting consolidated instance and make decisions about what rules to include based on the data requirements. Our experience is that the functional users would only be required an average of quarter-time. The cost table below does not include the license costs for the eprentise Consolidation software. The estimated resource costs range from $255,000 for a 4 month project and a single functional resource per module to $1,020,000 for two resources per module on an 8 month project.

Conclusion

eprentise Consolidation provides a more complete solution at a lower cost and in a shorter time than the Traditional Migration Approach. With eprentise, the total project costs are a fraction of those of Migration. All history is converted. There is no need to worry about getting the right technical resources or compromising the data integrity. Having the right tools make the users look like pros. Using eprentise Consolidation software is the right choice for a successful project.

Demantra Configuration and Tuning Case Study by Mike Swing, TruTek

nospam@example.com (Barb Matthews) — Tue, 27 Jul 2010 07:32:17 -0700

TruTek recently participated in a tuning exercise for one of our Demantra customers. This paper describes some of our lessons learned.

What is Demantra?
Demantra provides demand management, sales and operations planning, and trade promotions management solutions. Demantra provides powerful analytics for more accurate demand-driven planning, forecasting and modeling.

You can read more about Demantra performance at “Oracle Demantra Performance, An Oracle White Paper, September 2007”. This paper describes recommended design considerations, appserver properties, database parameters, and client server attributes for Demantra Worksheets, which are the primary user interface for the Demantra tools. The bottom line is that there are many areas where tuning can improve the overall performance of Demantra worksheets. Our efforts focused on tuning the operating system and database.

The Hardware Configuration
Our client recently implemented new AIX hardware with 64 threads, 128 GB RAM, 1.6 TB of SSD and 2 TB of 15K Raid 10.

In general, when compared to the initial configuration on 6/21/2010, we were able to improve the performance of worksheets from about 300% to 1000% on 7/2/2010. The following table illustrates the worksheet runtimes before and after performance tuning.

The Operating System (AIX) Configuration

Concurrent I/O

Our testing concluded that using Concurrent I/O would improve performance. Concurrent I/O was first introduced in AIX Version 5.2. This feature invokes direct I/O, so it has all the other performance considerations associated with direct I/O. With standard direct I/O, inodes (data structures associated with a file) are locked to prevent a condition where multiple threads might try to change the consults of a file simultaneously. Concurrent I/O bypasses the inode lock, which allows multiple threads to read and write data concurrently to the same file. This is due to the way in which JFS2 is implemented with a write-exclusive inode lock, allowing multiple users to read the same file simultaneously. As you can imagine, direct I/O can cause major problems with databases that continuously read from the same file. Concurrent I/O solves this problem, which is why it is known as a feature that is used primarily for relational databases. Similar to direct I/O, you can implement this either through an open system call or by mounting the file system, as follows:

# mount -o cio /u

When you mount the file system with this command, all its files use concurrent I/O. Even more so than using direct I/O, concurrent I/O provides almost all the advantages of using raw logical volumes, while still keeping the ease of administration available with file systems. Note that you cannot use concurrent I/O with JFS (only JFS2). Further, applications that might benefit from having a file system read ahead or that have high buffer cache hit rates might actually see performance degradation.

Performance Improvement – Oracle won’t work without this setting:

AIXTHREAD_SCOPE=S

AIX has a setting that allows process PIDs to change. In order for Oracle to work correctly, the PIDs need to be static.

set AIXTHREAD_SCOPE=S in the environment:

export AIXTHREAD_SCOPE=S

Demantra I/O Requirements

Demantra installations for medium to large companies will need to have I/O subsystems that can accommodate 150K - 300K I/Os per second (IOPS). Low-end solid state drives (SSD) have IOPS of about 120K. High-end SSDs have IOPS over 500K.

With 15K hard disks, the IOPS is about 200. Achieving 150K IOPS with JBOD would require 750 drives.

Database Configuration and Performance Tuning

1. Solid State Disks (SSD) improve I/O performance by 1000 times. For moved objects that require fast access to the SSD, two SSD mount points would be best:
o SALES_DATA, MDP_MATRIX tables, partitions, local indexes and indexes
o redo logs, TEMP tablespace, undo tablespace

Reading and writing from SSD is about 1000 times faster than comparable operations on JBOD. In combination with 128 GB of RAM, the use of SSD has minimized the physical read wait times and the IOPS is roughly 1000 times greater than the previous system. Coupled with 64 processor threads the SSD I/O system is more than 1000 times faster than the old I/O system.

2. Running the worksheets with multiple CPUs in parallel improved performance by 8 times. We decided to evaluate the effect of varying degrees of parallelism. In our example, we have 64 processor threads available.

We found that the degree of parallelism for tables could be high, but that indexes generally should have a degree of 1, to avoid issues with the CBO. However, by setting the SALES_DATA to a higher degree we were able to increase the CPU utilization of the worksheet. For example, if the SALES_DATA table is set to a parallel degree of 8, 8 processes will execute the parallel query.

The following diagram shows the same Capacity Planning worksheet run twice: once for 2:00 minutes with 8 CPUs and the second run for 15:45 minutes with 1 CPU.

The CAPACITY_PLANNING worksheet uses the parallel index SALES_DATA_144_IDXN. The following example shows how increasing the parallelism of the worksheet decreases the runtime of the worksheet, almost linearly.

The associated worksheet runtimes to this graph are as follows:

Note that as the number of processors doubles, the time should be reduced by half. For example, with a parallel degree of 8, 8 CPUs are used to process the worksheet and the runtime is 1:55. By doubling the CPUs running a worksheet, we might expect the runtime to be about 1:00 minute. However, the runtime with 16 processors is 1:20, meaning this is a non-linear process with some loss due to ‘friction’ and approximately 63% efficient.

3. Partitioning improved performance by up to 8 times (degree of parallelism) for most worksheets
Partitioning allows for performance improvements by reducing the physical reads by using partition pruning for SELECT statements and allows for parallel updates, when the updates fall in different partitions and parallel execution is enabled. Evaluate the effect of varying the number of partitions.

Partition – different partitioning options were evaluated

SALES_DATA – change partitions to be based on week. This could be a finer resolution, maybe “day”, but usually data is loaded weekly , so the effective resolution is weekly.

MDP_MATRIX – change the number of partitions of locations to be 1500, about 200 locations per partition. Initial testing with 30,000 location partitions proved to be unusable.

4. Enable Parallel DML for Load Programs:

alter session enable parallel_dml;

For tables with partitions and loads that cross partitions with parallel dml enabled, this will cause normal update statements to work in parallel.

5. We changed multiblock_read_count from 8 to 32. This resulted in 4 times faster I/O during full object scans. Overall worksheet performance improved an average of 20% – 25%.

6. We researched the most efficient memory utilization – after much testing, 65% SGA and 35% PGA, with 128 GB of available RAM turned out to be the best. By effectively tuning the database memory usage, we were able to reduce physical I/Os by allowing the memory to cache both data blocks in the buffer cache, and code in the PGA. This allowed for a performance improvement of about 10% to 20%, depending on how this was initially set.

7. Reorganizing tables resulted in about a 5% improvement when the SALES_DATA table was 28% out of order:

SALES_DATA 28% out of order Capacity worksheet 2:05
SALES_DATA 0% out of order Capacity worksheet 1:59

8. We changed the block size from 8K to 32K – reducing chaining can result in fewer physical reads and could improve performance by a factor of 2 or more if every row was chained. However, in our case, about 5% of the rows were chained in the worst table. While we have reduced future chaining, the performance improvement was small.

9. We evaluated the performance of the Default Buffer Pool, Keep Buffer Pool and Recycle Buffer Pool for use with the Solid State disks. Our recommendation – use the Default BUFFER_POOL

Best Settings for Performance

Maintenance

We determined the best way to rebuild the SALES_DATA table, using sales_date, item_id and location_id in the ORDER BY of the INSERT. The code was generated by TOAD and manually updated with the ORDER BY on sales_date, item_id and location_id.

For help implementing this solution or other configuration or tuning tasks, please contact Sherri Dudley, sherri@trutek.com or Barb Matthews, barb@trutek.com.

eprentise Case Study: Automotive Parts Supplier Consolidates Regional Production Instances of Oracle E-Business Suite by Chris Busbee, eprentise

nospam@example.com (Barb Matthews) — Tue, 27 Jul 2010 06:31:53 -0700

The automotive industry aftermarket is worth more than $250 billion and is growing annually. With a booming market such as this, it is no surprise that many distributors of aftermarket automotive products have expanded their operations in order to reach additional markets and become a truly global operation. Global expansion generally requires additional investments in IT to support the add-on operations. As experienced by one aftermarket auto parts supplier, multiple enterprise resource planning (ERP) environments can be difficult to manage due to the shared aspects of the different systems as well as the disparate aspects between them.

The company is headquartered in the US but has extended its network to Canada, an initiative that required additional IT support in the form of an additional instance of its Oracle E-Business Suite (EBS) ERP system, as that provided the shortest time to a go-live. Both the US and Canadian enterprise-class data centers operated a production instance, a test instance, a development instance, and a project instance, in addition to the backup and DR instances. The deliberate over-allocation of hardware resources to accommodate the sporadic peaks in CPU and RAM usage coupled with the fact that each EBS instance resided on a separate server exacerbated the cost of maintaining multiple instances. IT had waved the flag for many years on the benefits of consolidating (reduce hardware, reduce maintenance / support across all areas, eliminate duplicate effort around upgrades, enhancements, apply patches, etc.)

After years of operating with two disparate instances, the company decided to find a solution to the problems of multiple systems maintenance, high infrastructure costs, tedious consolidated financial reporting, and inconsistent business processes that were hindering the company from operating as a truly global business with consistent data and a single source of truth. Using eprentise Consolidation Software for E-Business Suite, the company consolidated its US and Canadian instances into a single, global instance that is enabling them to reduce infrastructure costs, to reduce IT personnel previously required to support multiple systems, to streamline business processes and leverage suppliers, as well as to grow with agility and embrace new initiatives.

The two main areas of the business involved with the planning and decision making for the consolidation project simultaneously developed their own business cases for going forward:

Finance / HR:

 Facilitate efficient and effective decision making with timely and reliable fact based information
 Obtain a single version of the truth / single system of record
 Access real-time information at consolidated level
 Create Centralized Shared Services (maintenance, setup, centralized processing)

IT:

 Reduce expenditure on hardware and IT personnel (fewer servers, fewer databases, less storage)
 Maintain consistent support by eliminating multiple instances
 Eliminate duplicate integrations and interfaces
 Reduce testing during patches and upgrade

________

IT wanted to consolidate because of the reduced hardware footprint, reduced maintenance, and reduced support across all areas, but it wasn’t until the stakeholders saw a direct business benefit that we finally received approval.

________

Clearly both Finance and IT saw reasons that consolidating instances would be beneficial to the business. Regional production instances were simply an obstacle to business improvements and the ability to grow. Additionally, the sales from Canadian operations accounted for only 10% of the company’s total revenue, but the level of IT support required to maintain the Canadian instance was virtually equivalent to that required to maintain the US instance. It made very little sense from a business process perspective for 10% of the revenue to be burdened by the cost structure of an entire EBS instance.

The auto parts supplier considered three options for performing the instance consolidation, including keeping separate instances, before deciding to move forward with eprentise Consolidation Software. The table below indicates the pros, cons, and benefits of each.

Project Planning and Roadmap
After the decision was made to use eprentise Consolidation software to get to a single, global instance, the planning for the project began with the following considerations:

 RICE Activities
 Data warehouse integration
 Reporting requirements
 OFA – Oracle Financial Analyzer impact
 Interfaces/Integration to and from third party systems
 Tax requirements
 New Functionality in R12
 Upgrade to R12

Because the company decided on a software solution, the personnel resources required were minimal and limited to the following:

 Project sponsor
 Project management
 DBA
 Testing team
 Technical resources for interfaces, customizations, reports, integration of non E-Business Suite modules
 Standard EBS support and maintenance (clones, backups, access, security)

The consolidation project was done in three stages. The first stage started involved running eprentise Metadata Analysis to discover the scope of the project and identify the rules that needed to be created by eprentise Consolidation software. The second stage of the project moved all of the data, including history, from the Canadian instance into the US instance. The third phase of the project focused on standardizing all configurations, resolving duplicates, and restructuring operating units and inventory orgs to allow the business to leverage suppliers, execute common business processes, and do business as a truly global enterprise.

The graphic below represents the project roadmap and timeline, including an upgrade to R12 at a future time.

Results
By using eprentise Software to consolidate two regional instances to a single, global instance, the company saved millions of dollars and months of project time compared to a similar company’s alternative approach of hiring consultants to do a manual data migration. Without using eprentise software, the other company manually migrating their instances into a single instance used ~ 250 consultants, spent approximately $12 - $15 million, and took 18 months just to migrate the data, only bringing over balances and limited history.

Does Your Database Need a Hug?

nospam@example.com (Barb Matthews) — Thu, 24 Jun 2010 06:17:06 -0700
We received this terrific feedback after Tanel Poder visited a client:

Our recent exercise with Tanel Poder was an extremely positive one and one which I would recommend to anyone seeking help with a contention issue on their Oracle database. I had seen Tanel speak at the Hotsos Conference in Dallas and knew that he would be extremely valuable in helping us identify what was causing an unpredictable latching scenario in our production environment.

Even before he arrived he asked for and begin reviewing the data we had collected (AWR reports, statspack output, etc.) and begin devising a "plan of attack" for determining the cause of our issue. Upon arrival he took our input very seriously, but was quick to validate our assumptions with factual database analysis. I also appreciated his concern for our security and the well-being of our production environments and was very careful to not run scripts which would cause contention themselves in production, or leave behind bothersome custom objects.

Tanel was always courteous and friendly and was willing to repeat his explanations and translate his very technical knowledge into terms that the lesser-trained DBA could comprehend. And the tools he brought along were bundled up nicely for us to download to our own environment and use for our troubleshooting. He summarized his findings for us in a way that was presentable to upper management and gave a clear explanation of his approach, analysis, and final recommendations.

I appreciated Tanel's offer to be a resource in the future if we have further questions after using his scripts and tools and I fully intend to take him up on it!

Don't miss the chance to learn straight from the master. Tanel is available for consulting engagements.

Project Profiles

nospam@example.com (Barb Matthews) — Thu, 17 Jun 2010 08:04:32 -0700
The IT leaders who run Oracle E-Business Suite know the objective – R12. It’s the release that will carry them to 2015 or beyond. They will run one instance, it will be global in scope, and all the business users on the planet who logon to their instance will follow the same business processes. R12 will be the single source of financial truth. Some 11i customers have already made the transition, others are in process, many are planning, and a few are too busy with other priorities and will wait.

Making the transition to a major release of an enterprise system with E-Business Suite’s scope is a big complicated game and a puzzle that takes several years to play. How do you make the transition? What are the moves? What’s the sequence? The IT teams that get to R12 at just the right time for their organization, with the lowest transition costs and ongoing operational cost structures, and the best functioning R12 environments will be the winners.

Oracle EBS customers can be grouped according to four distinct R12 transition project profiles: Direct Upgrade, Transform Then Upgrade, Partial Reimplementation, and Full Reimplementation. Customers select the project profile that best fits their EBS landscape, available resources, and culture. The chart below shows the profiles, ordered from easiest to most difficult:

Project Profile

Description

Direct Upgrade

The organization has confidence that R12 offers business value. Everyone accepts that the costs will be reasonably low and also unavoidable, so there are no arguments. The organization has plenty of resources and the right skills, or a consulting firm they are comfortable working with.

Landscape: Single production instance, compatible with the new R12 Financials architecture. The implementation-time setup configurations – Chart Of Accounts, Calendar, Flexfields, and Organization structures – are all suitable for the business for the next five years.

Plan: Run Oracle’s Upgrade software to convert the application layer and the business data, and you’re done. Go live in 3-8 months.

Consider: While this takes few resources and uses familiar technology, the E-Business Suite implementation-time setup configurations will not change. They were selected years ago for Release 10 or 11i. Are they good for the business today? Will the 11i structure, when re-cast in R12, still be good for the business through 2015?

Transform Then Upgrade

There are stakeholders in the business whose success depends on the business capabilities due when R12 goes live. R12 business value is accepted. The business is not satisfied with the current 11i instance or instances.

        The organization is resource-constrained but maneuvers for a single global R12 instance, without compromise, with a go-live date sooner than anyone expects.

Landscape: One or more production instances. Implementation-time setup configurations may need to be changed to realize full R12 value and properly model the business for the next five years.

Plan: If there are multiple 11i instances, use a commercial software product from eprentise to consolidate them into a single instance, without losing any data or database integrity. If the implementation-time setups need to change, use eprentise software to change them. Run Oracle’s upgrade to take the single, pre-conditioned 11i instance to R12. Go live in 4 - 10 months.

Consider: The business still needs to define what must be different in R12 to maximize value, but they don’t waste effort on configurations and setups that need not change. Business value sooner + lower transition costs = faster payback.

        Many eprentise customers transform their 11i instance(s), go into production for a while, capture some of the business benefits, then wait for the best time to complete the upgrade to a single R12 instance.

Partial Reimplementation

The organization doesn’t have the resources or the technology assets to go directly to R12. Top management may not understand how R12 helps the stock price. Getting part of the business on R12 becomes the short term, intermediate objective, to build support for R12.

Landscape: There may be one instance with obsolete setups, or multiple 11i production instances.

Plan: Select a small, relatively simple business unit to be the R12 pilot. If there are multiple 11i instances, upgrade the most recently implemented one. Alternatively, create a fresh implementation. Go live in 12 – 24 months, then monitor success. Advertise R12 throughout the business to create interest in R12.

Consider: The next objective is now more complex: to get from the mature, core 11i instance(s) and a clean, lean R12 instance to the single global R12 instance. The intermediate situation is not of much value, but it looks like progress. R12 is live.

         The next phase is to go from division to division and reimplement each into the small R12 instance. There may be a learning curve where the re-implementations and data conversions get easier. This takes another 12 – 24 months before the enterprise is live on the single R12 instance.

Full

Reimplementation

The organization is not satisfied with the current 11i instance or instances, and they accept R12 business value. They are willing to spend whatever it takes to configure Oracle right this time, or to get to a single instance, or both. There is not much value in the 11i historical data, which may also be corrupt and untrustworthy.

Landscape: One or more instances, or obsolete implementation-time setup configurations, or both. Incompatible with the new R12 architecture.

Plan: Leave the 11i instances behind, implement a fresh R12 instance, and import all the 11i data. Or import just some of the old transactional data. The R12 instance may take 14 – 36 months to go live, but it will work fine for the long haul.

Consider: It’s a big effort for a global business to implement and deploy an enterprise application. Many across the business will be drawn into the design, decision, and testing effort. Most EBS customer will be forced to engage outside help.

         There is risk that a project this big and long-running will run into trouble keeping resources and funding, with changing business requirements, and technology shifts over its life.

Direct Upgrade remains the preferred project profile whenever it’s possible. Transform Then Upgrade eliminates Full Reimplementation and Partial Reimplementation as practical project profiles for R12 transitions.

eprentise provides the software technology that changes the 11i to R12 transition game for (a) organizations who have multiple instances and want a single instance, and (b) those who have obsolete implementation-time business setups like Chart Of Accounts, Calendar, or Organizations. They retain more of the investment in the 11i instances, and Time-To-R12-Value is shorter. The resulting R12 instance is configured to last to 2015, and it is a true single global instance with all historical data.

The surprising combination of eprentise Transformation software plus Oracle’s R12 Upgrade software causes the Oracle EBS thought leaders to ask “Why reimplement?”

New Class: Introduction to Oracle E-Business Suite and Oracle Project Management

nospam@example.com (Barb Matthews) — Wed, 16 Jun 2010 20:26:02 -0700
This class sounded so good that our entire office is bickering about who gets to take the class first. We spotted Todd Dearborn, from Broadridge Financial Solutions, at Collaborate this year. Todd's co-workers raved about his wonderful presentation skills and ability to share knowledge. So we can't wait to take Introduction to Oracle E-Business Suite and Oracle Project Management. Todd's 3-day class will start with two days covering the modules that make up the E-Business Suite, and how they fit together. Then he'll teach one day on how to use Oracle's Application Implementation Methodology (AIM) to manage a project. Todd promises to use lots of real world examples and describe best practices. If you've been looking for a class that can help prepare you for the planning stages of an Oracle implementation or upgrade, this is the class for you.

New Class: Oracle Release 12 AME (Approvals Management Engine)

nospam@example.com (Barb Matthews) — Wed, 16 Jun 2010 20:25:14 -0700
OK, we're pumped. We've wanted to offer a class about AME, and now w've got a terrific one lined up: Oracle Release 12 AME (Approvals Management Engine). Take this 3-day class and you'll learn how to use AME to transfer employees from one division to another, authorize expenses, pay invoices, or approve purchase requisitions. Topics include an overview of AME, how to implement AME, how to implement tasks, approvals management, and transaction types, how to use Attributes, and testing and administration.

PL/SQL Tips by Dan Stober

nospam@example.com (Barb Matthews) — Wed, 16 Jun 2010 20:04:56 -0700
Impaired by Implicit Cursors
Oracle calls the structure an implicit cursor, but most people just know it as “SELECT…INTO.” It’s a shortcut way to query the database for a single record and place fields values from that record into PLSQL program variables. But, there are several gotchas associated with their use that can lead to inefficient – or even buggy – code.

SELECT … INTO … randomly
The SELECT .. INTO .. construct requires a query that returns one and only record. If the query returns no records, PLSQL will raise the no_data_found exception, and if it returns more than one, PLSQL raises too_many_rows. Sometimes, I see code where it’s obvious that the developer applied a bandage to patch up code that raised a too_many_rows, and the result is a statement like this:

SELECT empno
INTO v_empno
FROM emp
WHERE deptno = v_deptno
AND ROWNUM = 1;

At some point in the past, the query probably returned more than one row and PLSQL subsequently raised an exception, or the developer believes that the query has the potential to return more than one row, so she added that “ROWNUM=1” to make sure that it doesn’t.

The block now contains exception avoidance, but at what cost? Does the developer know why the query is returning two rows? If there are two records, does she care which record will be placed into the variable v_empno? If you’re going to go through the trouble to have PLSQL execute a SQL statement and retrieve a value from the database, shouldn’t that value be important enough that you don’t leave it to the vagaries of database internals to determine which record it will be?

After all, it is possible that the same query, executed twice, can return the records in a different order. In fact, it happens all of the time! What you should do is write a query that is certain to return only one record. If possible, try to access the table using the primary key. Failing that, use the natural key. Also, ensure that all of the joins are necessary and are complete. Even if you access one table using the primary key, if it’s joined to another table with a one-to-many relationship, your query will return as many records as are present in the child table with that foreign key value

Here is an example of a query with an unnecessary join:

SELECT dname
INTO v_dname
FROM dept JOIN emp USING ( deptno )
WHERE deptno = v_deptno
AND ROWNUM = 1;

The dname field and the deptno fields are both in the dept table. Everything needed to answer the question is in the dept table: given this value for primary key deptno, what is the value in the dname field? But, for some inexplicable reason, the developer has included a join to emp. Now, if there are four employees assigned to the department, the query returns four records. Instead of remedying the problem by removing the unneeded join, the developer patched it with that glaring “ROWNUM” limitation. (Note: There is a possibility that the join to emp was included so that the program would populate a value into v_dname only in cases where there are employees assigned to the department. This inner join would not return any records when there are no emp records. But, even if that were the requirement, there are much better ways to accomplish that; ways that do not appear to be a bug in the code.)

Another variation on this theme, is the use of ROWNUM = 1 along with ORDER BY in an apparent attempt to get the lowest value.

SELECT ename
INTO v_ename
FROM emp
WHERE ROWNUM = 1
ORDER BY empno;

This query appears to be an attempt to get the name of the employee with the lowest empno. But, this too, is wrong. Oracle assigns the ROWNUM values before applying the sort. The record with ROWNUM = 1 will be the first one that Oracle retrieved, which is not necessarily the lowest empno record.

SELECT … INTO… with aggregates

Whenever I review PLSQL code, I see many developers use a structure like the one below to identify cases where records do not exist:

BEGIN
SELECT COUNT(*)
INTO v_temp
FROM emp
WHERE deptno = v_deptno;
EXPEPTION
WHEN no_data_found THEN
v_temp := 0;
END;

IF v_temp = 0 THEN
...stuff to do when no records exist

There are two things to note about the code fragment above.

First, notice the use of the no_data_found handler in a block that includes only a SELECT COUNT(*) statement. That is an exception that never will be raised. The COUNT(*) function, when used without a GROUP BY, is guaranteed to return a record. The count might be zero, but there definitely will be a record. That exception handler –and the anonymous block added to support it – is completely unnecessary.

Second, from the code fragment shown, it appears that the program does not really need to know the actual number of records that this WHERE clause would yield, only whether there are any records at all. Essentially this is wasted computing power, especially if the count is in the millions of rows!
If you really want to test and see if at least record exists, then change your query to look for only one. If we change the WHERE clause in the query above to look like this:

WHERE deptno = v_deptno
AND ROWNUM = 1
then, COUNT(*) will return 1 if records exist, and 0 if there are none. And that’s all we needed to know!

Avoid exceptions with an inline CURSOR

Consider the following block:

BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = v_empno;
EXPEPTION
WHEN no_data_found THEN
v_ename := 'No record found';
END;

The idea seems to be that we want to look up the record identified by v_empno and place that employee’s name into v_ename. We also have an exception handler, and PLSQL will place the string “No record found” into that v_ename variable when the lookup fails. The block will accomplish that purpose just fine and might never break.

Nevertheless, the work is performed by means of a technique that many purists hold in low regard: It employs the raising of exceptions to accomplish a normal, expected part of the algorithm. Many theorists believe that exceptions and their handlers should be reserved for occurrences which truly are errors. I don’t wish to wade into that discussion right now, but I do have a solution that is just as easy to understand, and accomplishes the same thing without relying on exceptions to accomplish the program logic:

v_ename := 'No record found';

FOR rec IN (SELECT ename
FROM emp
WHERE empno = v_empno )
LOOP

v_ename := rec.ename;

END LOOP;

In this case, the loop only executes if the query returns a record. We’ve predefined the value of v_ename with the “No record found” message. If the query returns a record, that value will be overwritten with the value of ename from the record. If the query does not return a record, then, the cursor loop will not be enetered, and v_ename will retain that predefined value.

The only downside of this approach is that the cursor loop also will not raise too_many_rows. If the query returns multiple records, the variable will be left with the value from the last record seen when the program exits the loop. Of course, we saw above that this scenario effectively leads to assignment of a field value from a random record. Of course, if you’re looking for a good way to hide your bandage fix and avoid adding on the “ROWNUM = 1”…

Check out the TruTek Training Schedule, as of 5/5/2010

nospam@example.com (Barb Matthews) — Tue, 11 May 2010 13:51:32 -0700
We've got a terrific set of classes coming up, including classes taught by Jonathan Lewis, Scott Spendolini, Tanel Poder and Steven Feuerstein.

TruTek Training Schedule as of 5-5-10

We Call Them the Oracle Upgrade Trio

nospam@example.com (Barb Matthews) — Sun, 09 May 2010 20:18:08 -0700
"Sometimes the questions are complicated and the answers are simple." -- Dr. Seuss
"Unfortunately, that is not the case with upgrading to R12." -- Mike Swing

Are you the Mighty Oracle Foot Soldier, ready to tackle the upgrade to Release 12.1.2 for your company? We recommend you start here, with TruTek's 3-part guided tour.

One of the first steps you should take in preparing to upgrade is to actually try the upgrade. If you haven't already installed the Release 11.5.10.2 Vision Instance on a test environment, go ahead and do it. And then use Mike's guides to upgrade that instance to Release 12.1.2. Surprisingly, even the Vision Instance doesn't upgrade flawlessly. Mike includes pointers to additional patches and steps that you'll need to take to make your upgrade a success.

Mike's comprehensive trilogy consists of:

the little r12.1.2 project plan - this is the project plan that Mike's students use when they step through an upgrade.

the little r12.1.2 upgrade essentials for managers and team members - this book describes why you need to upgrade, and includes a discussion of how it should be done.

the little r12.1.2 upgrade guide - sponsored by the Oracle Application Users Group, this 414 page (so far) book provides the detailed steps to upgrading your Vision instance from R11i to R12.1.2. It includes the steps for upgrading your database to Version 11gR2. It includes pointers to more than 175 patches and 150 My Oracle Support notes. That's right, just because you've got this book doesn't mean you get out of downloading patches and reading the Readmes and assorted other explanations. But we tell you, all in one place, which documents to read, how to apply the patches, and "gotchas" that we've discovered along the way.

This edition is bigger and better than the last, as Mike continues to add information about patches and bugs and features that you need to understand to upgrade to E-Business Suite Release 12.1.

Are you looking for a guided tour? Don't worry, Mike uses this book as the basis for TruTek's Oracle E-Business Suite R11i to R12.1 Technical Upgrade class. He's also available to chat if you'd like to discuss your upcoming upgrade plans - just give him a call at 801 486-6655 or send him an email at mswing at trutek dot com. Best of all, Mike has the hands-on experience that you'll need for an upgrade, so consider TruTek for meeting your consulting needs.

Collaborate 10 Upgrade SIG Meeting by Barbara Matthews

nospam@example.com (Barb Matthews) — Sun, 09 May 2010 19:48:22 -0700
The Panel

- John Stouffer, Independent Consultant, Past Chair of the Upgrade SIG, Moderator
- Lester Gutierrez, Oracle EBS Performance Group, works on upgrades and reducing downtime
- Udayn Parvate, EBS Release Management Team, works on building and packaging the E-Business Suite software. Udayn performs installation and upgrade activities, defines and enforces standards for packaging and delivery with development teams. He works to get upgrade issues resolved so E-Business Suite customers' experience is as smooth as possible.
- Sandra Vucinic, Vlad Group, Inc., Chair of the Upgrade SIG
- Floyd Teter, Jet Propulsion Labs, functional guy on the panel
- Steven Chan, Director, Applications Technology Integration, Oracle Corporation
- Michael Rulf, Executive Director - Product Development, AT&T Hosting and Application Services (H&AS)

The Questions and Answers

We are going to upgrade from Release 11.0.3 to Release 12. We are currently running on Sun Solaris and will switch to a Linux Intel environment. Our architecture team is wondering how to do performance load simulation so we know how much hardware we need to buy to run the upgraded E-Business Suite environment. Our database is 700 gigabytes, and we have 2500 users.

- First, you need to get a sense of how many concurrent users you have, and what those users are doing. While you may have a total of 2500 users, those users probably aren't all logged on at the same time, and they likely aren't submitting data and hitting commit at the same time. The limits will not be software, but will be hardware limitations based on the degree of throughput, and read and write operations per second. The architecture team needs to understand the product mix (modules), the read/write workload, and the concurrent manager workload, and then bring in the hardware vendor to help build out a test environment. You can also use load simulation software like Mercury Interactive. Oracle has a Test Starter Kit (see http://blogs.oracle.com/stevenChan/2010/04/ebs_1211_tsk.html) for E-Business Suite Release 12.1.1 that can be helpful, but you need to choose carefully when you put together your test environment.

- As you purchase your hardware, make sure you are positioned to be scalable, so you have room to grow if you need to. You might find, for example, that when you do the first close on the new system, you need to add more hardware to deal with the extra load. If you can move to parallel concurrent processing, you can add another node easily. Given the size of your environment, you should research and understand the options available for using load balancing and shared appl tiers (see Parallel Concurrent Processing Failover and Load Balancing of E-Business Suite Release 11i and Release 12 by Mike Swing at TruTek).

- As far as hardware is concerned, we are starting to see where the Intel world is supporting more processor cores, and the hardware is hot pluggable. Be careful not to skimp on the chassis, but populate it with the number of processors you want. We're about to see a big jump in the industry from 4 core to 8 core. You should try to pick a hardware solution that allows you to make that move if you need to. Note that there's a tradeoff to buying more processing power than you need - if you overload your processor, your software fees will go up, so you have to strike a balance.

We upgraded to release 12.0.4 and saw a 25-30% increase in database size. Is there a similar increase with the 12.0.4 to 12.1.2 upgrade?

TCA, SLA, and E-Business Tax added a lot to the database size, but you've already bit the bullet on those modules, so no, you shouldn't see a significant increase.

We are currently running Release 11.5.10.2. Is there a recommended upgrade path?

You should upgrade to RDBMS Version 11gR2 and then Release 12.1.1, and then apply the 12.1.2 RUP. We've "borrowed" a wonderful diagram from Steven Chan's blog of what your E-Business Suite Upgrade Roadmap is:

.

Is there a plan to offer a gui screen for the Mobile Supply Chain module? Currently it uses a telnet session.

It is a high priority with the User Experience team, but we aren't sure when it will be released. The Release 11.5.10 gui version, according to someone in the audience, was too slow, so it wasn't adopted by many users.

Do people typically upgrade or reimplement?

Upgrading is less expensive, and much better supported by Oracle. Because of the nature of reimplementations, the path isn't as clear because we all have different reasons for doing it. It is much more difficult to determine what post upgrade patches to apply, and how to do validation testing. Reimplementation customers will have to blaze their own trail.

Is bifurcation still recommended?

For Release 12, the new term is Upgrade by Request. Upgrade by Request allows you to upgrade part of your data during the upgrade, and then upgrade the rest of your data after your upgrade completes. Modules that can use Upgrade by Request include Financials and Procurement, Projects, Supply Chain Management, and CRM. You can read more about Upgrade by Request in Appendix G of the Release 12 Upgrade Manual.

Are there any recommendations for preparing for a Release 11.5.10.2 upgrade to Release 12.1.2?

- See Sandra Vucinic's presentation, Get Ready for EBS Release 12.1! Tasks to Complete Now to Ease R12.1 Upgrade Process. Here's a list of tasks from her presentation:

- Upgrade database to 11gR1 or 11gR2
- Convert tablespaces to OATM model
- Evaluate impact of R12.1 on customizations and extensions
- Introduce BI (XML) Publisher and JDeveloper
- Introduce Web ADI and Report Manager
- Archive and purge EBS 11i data
- Convert from JInitiator to Native Sun JRE - _21 is recommended
- Integrate Discoverer Server Release 10g with EBS 11i - use Discoverer 11g if possible as Discoverer 10g is desupported at the end of 2010
- Configure Oracle iAS Release 10g for external apps (SSO, OID, Portal) and integrate with EBS 11i
- Position for high availability and scalability
- Evaluate and complete platform change based on ROI

- RDBMS 11gR2 is a major architectural change in the nature of the cluster software. DBAs need time to understand the fairly steep learning curve. The many new features may help justify doing the database upgrade separately from the E-Business Suite upgrade so DBAs can come up to speed on the technology.

- In terms of testing, have you catalogued all of your customizations? How are you going to train your user community? What tool will you use to develop training guides?

- Read the 11gR2 Upgrade Companion, and be sure to stay current throughout your upgrade on new patches. There's already a RDBMS Version 11.2.0.1.1 patch which fixes a lot of issues.

A user related his story of upgrading from Release 11.5.10.2 to Release 12.1.2. During the upgrade the patch stuck on a program, they waited a day, and got a pre-upgrade patch that needed to be applied before starting the upgrade. He noted how frustrating this was, and asked if there is any My Oracle Support note that tells all performance and serious functional issues in the upgrade.

The official My Oracle Support documents are the Release 12 Upgrade Guide, the Release Notes, and the NLS Release Notes. When Oracle encounters issues, they update the Release Notes. They may also release additional patches, including Critical Update Patches (CUP), which include a consolidation of fixes. Users must actively monitor My Oracle Support for additional patches and alerts while upgrading. Another option is to look at the Release 12 Forum, a very active, live, real time forum. Often solutions can be found there before logging a problem in My Oracle Support. Check out the forum at: http://forums.oracle.com/forums/forum.jspa?forumID=395&start=0

How close are we to desupport on Release 11.5.10.2?

Premier Support ends at the end of November, 2010. The extra cost for Extended Support is waived until November, 2011. Extended Support ends in November 2013. Premier support is what you have today, and includes certifications with Oracle products and other products. If a new MS Windows client is released, with Premium Support, Oracle will certify to that. Extended Support, however, will not include third party certifications, so if a new service pack for Windows 7 is released after November, 2010, Oracle is not bound to certify to that service pack. Odds are, they'll give it a try, but they are not likely to produce new patches. The emphasis at Oracle once Extended Support kicks in for Release 11.5.10.2 will be on Release 12.

This point is very important - E-Business Suite customers use a variety of other products besides the E-Business Suite on their computers. Once Extended Support starts, customers may find themselves needing to upgrade because of some other product that they use, but unable to do so because of certification issues with Windows or JRE or other products.

When you move to R12, JInitiator is not certified and won't work on Windows 7 or Vista. You will need to migrate to the native SUN JRE client. Don't have to worry about java conflicts between applications.

I am new to an organization that uses grants, contracts, and projects. In the past, these were considered outliers that kept us from going to the latest release. Will these modules impact our ability to go to Release 12?

Panel members pointed to several companies using those modules who are upgrading, and said they have not hit any big issues.

An audience member said that she had put together a plan for her company based on what she has heard at the conference and wanted to make sure they were heading in the right direction. They are running RDBMS Version 10.2.0.3 with Release 11.5.10.2, they are already on JRE, they have Discoverer and ADI/GL already implemented with the web, and are moving to RDBMS 11g using OATM with a 2 node shared $APPL_TOP and the latest Release 12 (currently 12.1.2). Is that where her company should be heading?

The panel agreed, her approach is correct.

What features of RDBMS Version 11gR2 would be useful to the E-Business Suite Applications?

You can save a baseline of your production RDBMS 10g performance, and 11gR2 will tell you that your current execution plan is fine, or that you need to run a different plan. You won't lose any performance by going to 11g. This feature is built into 11g. If you run into a performance problem, you can fall back to the 10g plan to get immediate relief while working with Oracle Support to get a more permanent fix.

Conclusion

All in all, it was a fun meeting with lots of questions and interesting answers. As one might expect, the OAUG Upgrade SIG strongly urges you, if you haven't done so already, to get ready to upgrade to Release 12. There are exciting features in RDBMS 11gR2 that will benefit the E-Business Suite Applications even if you don't upgrade to Release 12. The path to upgrading is well-laid out and tested, and the software is now very stable. If you have questions about the upgrade, feel free to contact the OAUG Upgrade SIG. Just drop a note to barb at oncalldba dot com

An Inquiring Mind Wants to Know (about 3rd Party Reimplementation Tools) by Barbara Matthews

nospam@example.com (Barb Matthews) — Sun, 09 May 2010 19:42:31 -0700
At the end of the Reimplement vs. Upgrade Panel at Collaborate 10, someone asked the panel ”Do third party products that claim to fix the issues that might make you decide to reimplement actually work?” The issues include changing the chart of accounts, calendar, and organization structures, or consolidating multiple instances to a single global instance with a shared service center.

The question went unanswered, because no one on the panel had firsthand experience. Since Floyd Teter said “Reimplementing is like getting a root canal without anesthetic in the lobby of the IRS while you're waiting for the audit,” and others on the panel agreed, I decided to do a little digging around. If you can avoid reimplementing the full E-Business Suite and migrating your 11i data, based on everything that was said by the panel, then it would be worthwhile to do so.

So I searched for a software vendor and came across eprentise. I started with Skip Straus, an ex-Oracle Consulting Practice Manager and now an eprentise salesman, and asked him lots of questions. Then I read a paper that he wrote called Why Reimplement? I really wanted to attend the eprentise presentations at Collaborate on global instance consolidation and upgrade vs. reimplement, but that didn't work out. I spoke to Skip again over the phone, asked more questions, and finished up by looking at the eprentise website.

I also asked around to see if my consulting colleagues had any opinions.

The biggest concern raised by consultants was whether Oracle would support a customer that used eprentise transformation software to change Oracle EBS’s "implementation-time configurations." According to Skip, Oracle does not support third party tools, nor does it support the conversion scripts that you would write yourself to extract and transform the data. The assorted eprentise solutions change the data content or format (such as numeric to alpha), but not the database structure. The result is consistent and correct. He said that eprentise has lots of customers who have made the move, and they haven't had any customer report that Oracle wouldn't support them. The eprentise software changes and converts the data; the company supports the conversion and will address any issues. That does not violate any Oracle support agreement.

Also, if you're going to reimplement because your data is bad, your choice is to clean up your data anyway, or abandon it since it’s untrustworthy. If you are reimplementing because you want to change your chart of accounts, well, you'll still be changing your chart of accounts as part of the reimplementation. So it would seem like fixing the data over on your Release 11i side, making the changes to base setups in the E-Business Suite that are not easily changed, would be a good way to go. I also like the idea of changing your data first, and upgrading second, rather than trying to do both at the same time as part of a reimplementation.

Migrating all your data manually seems like a painful way to go rather than using Oracle's well-tested upgrade path. I understand the panelists who said it would take a lot more time and money to reimplement and migrate your data.

I'll leave you with these questions:

1) If you've used a third party vendor's software to avoid reimplementing, whose product did you use?
2) Did it work?
3) Were the tools easy to use?
4) Would you recommend this path to others?
5) Do you have any caveats?
6) Did you run into any issues with Oracle Support?

Drop me a line at editor@trutek.com, as I'd love to hear more about this.

Collaborate 10 Release 12 Reimplement vs Upgrade Panel by Barbara Matthews

nospam@example.com (Barb Matthews) — Sun, 09 May 2010 19:31:23 -0700
The Panel

Panel members included Sandra Vucinic, Vlad Group, Inc., John Stouffer, Independent Consultant, Floyd Teter, JPL, Stephen Horgan, Oracle Corporation, Kyle Harris, Oracle Corporation, Mike Swing, TruTek, and Alyssa Johnson, Solution Beacon.

The Questions and Answers

What are the main decision points for upgrading versus reimplementing?

- If you are thinking of changing your accounting structure, you may have to reimplement. Reimplementing can offer a good opportunity to make changes to the accounting setup manager, subledger accounting, and the chart of accounts, and implement additional ledgers. Other reasons that might drive a reimplementation include if there is significant bad data in the existing environment, if the customer has lost their customization history, or company wide consolidations, mergers and acquisitions.

- The biggest issue with reimplementing is money. When you upgrade, you can expect to spend 20-25% of your original implementation costs. Reimplementation costs go way up; close to the original implementation costs. Floyd Teter suggested that if you can squeeze by with an upgrade, do the upgrade. If something dramatic has changed with the way you model your business, then reimplement. Floyd said that reimplementing versus upgrading was strictly a cost/benefit tradeoff.

Do reimplementations take longer than upgrades?

- Floyd said that reimplementation is always going to take longer. He suggested tacking on 40% to the schedule in terms of effort, but it could be even more, depending on your implementation.

- If you haven't archived or purged your data in a very long time, and have a lot of historical data, how does that drive the implement versus upgrade decision? Can you update selected data?

- You should archive as much data as you can before your upgrade process starts.

- You can also bring over only the data you want, and use your Release 11i instance to reference historical data.

- You can use Oracle's Upgrade by Request process to pick and choose how much data to bring over. Oracle provides a lot of flexibility, but you have to time it and make sure it makes sense. With SLA there is a pre-upgrade patch that lets you control how much of the data you upgrade. If you have 10 million debits and credits, they will all get updated, but you can use Upgrade by Request to update some now and the rest later. You can upgrade the last fiscal year of your data, and it will ensure you have at least 6 months worth of data when you go live.

Are there any lessons learned from upgrading?

- The technological architecture changes, along with E-Business Tax, SLA and TCA caused a big increase in size with Release 12. TCA alone went from 30 tables to 300. You should expect to need an additional 20-25% of disk space for Release 12.

- With the original upgrade to Release 12, AP changed considerably and there were a number of functionality issues that came up. AP is in much better shape since Release 12.0.4, although 12.0.6, the most current release, is also the terminal release for 12.0.x. Performance has improved and there are less errors. Oracle has a proactive information center on My Oracle Support that has information about AP. In many cases, you can find solutions without even logging an SR. AP in Release 12.1.2 is as stable as the other subledger modules.

How does customization impact the decision to reimplement or upgrade?

The consensus was that customization impacts need to be dealt with for both reimplementing and upgrading, and that customizations shouldn't have more impact on one method over another. For both reimplementing and upgrading, customers should consider the following:

- No matter what you decide, those customizations are in your business now because they serve a purpose and are addressing business needs. From that point of view, you need to decide whether the business process gap is still there or not. You need to figure this out whether you plan to upgrade or reimplement. The good news is that sometimes customers have been able to eliminate customizations thanks to new features in Release 12.

- You should catalog your customizations. You should know why you put those customizations in and map it to Release 12 functionality to see which customizations you can retire, and then retire what you can. By doing this groundwork, your upgrade or reimplementation will be that much easier.

- Oracle Consulting offers in its Advanced Customer Services a CEMLI Catalog Service that provides an inventory of your CEMLIs (Configuration/Customization, Extension, Modification, Localization, and Integration). These are scripts, and don't appear to be generally available except through Oracle Consulting. But they sounded like they would be quite handy. You can read more about them at http://www.oracle.com/support/advanced-customer-services/cemli-services.html - They are available as part of a "free" Oracle Insight engagement.

The Gorilla in the Room: We're thinking about reimplementing next year. Should we do that or wait for the Fusion Applications?

These were the pearls of wisdom from our experts:

Sandra - Consider Release 12 as a milestone in your journey. It is stable. There are live customers on it. It is a known animal. Doing nothing leaves you with less options. R12 is your milestone to get to Fusion. A lot of great business functionality is available now. With desupport for Release 11i coming in December, 2010, you need to get to Release 12, rather than wait for Fusion.

Mike Swing - If you wait to upgrade to R12, you are putting your production system at risk of being forced to upgrade in a hurry to resolve an issue that can't be resolved in R11i.

Alyssa - Don't wait, you won't regret it.

Floyd - Reimplementing is like getting a root canal without anesthetic in the lobby of the IRS while you're waiting for the audit. Upgrading is always the better choice if you can.

Kyle Harris, from Oracle, pointed out that you may not have a path from Release 11i directly to Fusion. Floyd explained that there's no guarantee that Oracle will offer a direct path from Release 11.5.10 to Fusion 2 or 3. The current direct path offering is for Release 11.5.10.2 to Fusion 1. So if you think you're going to sit on Release 11.5.10 and wait for a fully functional Fusion Application, don't. 80% of E-Business Suite Release 11.5.10 customers are probably not good candidates for Fusion Release 1. Fusion Release 1 will not include manufacturing, for example.

Stephen - Oracle Support will support the upgrade and not the reimplementation. Customers need to really make sure they can't upgrade before considering reimplementation. This point was made at other conference presentations as well - Oracle has limited resources for testing and must focus on what the vast majority of customers will be doing, which is upgrading. Reimplementation issues will vary by customer, depending on their situation, so Oracle Support will certainly try to help with issues, but the more thoroughly tested path will always be the upgrade path.

In the past, when we went from Release 10.7 to Release 11i, Oracle had good numbers on the expected downtime window given certain factors. What is a typical idea of what you can expect for the downtime window?

Mike Swing suggested that the upgrade from RDBMS Version 10.2.0.3 to 11.2.0.1 could take 8-12 hours, with the applications upgrade taking 12-15 hours, plus 1 day of testing; so about 3 days. To get that 3 day downtime window, the other panelists suggested you would need to consider the following:

- Test your hardware environment. In most cases you will bring in new servers. Do as much as you can ahead of time. Consider adding CPUs that you might not use moving forward. Can you lease additional CPUs for the upgrade period? Can you borrow CPUs from other hardware?

- Brainstorm ways to improve performance with your third party vendors and consultants.
Use Upgrade by Request to process the most necessary data during the upgrade, and then complete processing the rest of the data after go-live.

- Consider taking the database upgrade off the table. All new releases of the database have been certified. Mitigate the risk. While you do have to test twice if you do the database upgrade separately from the applications upgrade, the testing is different for the database upgrade, where performance considerations are the main issue, and the Applications upgrade testing for R12 is largely functionality testing.

- You won't know until you test it, then, after you test it, try to figure out how much time it will take and what you can do to reduce the time. There are lots of documents about how to reduce the downtime on My Oracle Support.

Summary

All in all, the consensus from the panel was that if you can avoid doing a reimplementation, you should, due to issues with cost, support, and time. For those customers who are waiting for Fusion, the panel concluded that if the functionality provided in Fusion Release 1 doesn't match your business needs, then you should move to Release 12 first, and wait for Fusion to catch up with your requirements in a future release.

Project Profile	Description
Direct Upgrade	The organization has confidence that R12 offers business value. Everyone accepts that the costs will be reasonably low and also unavoidable, so there are no arguments. The organization has plenty of resources and the right skills, or a consulting firm they are comfortable working with. Landscape: Single production instance, compatible with the new R12 Financials architecture. The implementation-time setup configurations – Chart Of Accounts, Calendar, Flexfields, and Organization structures – are all suitable for the business for the next five years. Plan: Run Oracle’s Upgrade software to convert the application layer and the business data, and you’re done. Go live in 3-8 months. Consider: While this takes few resources and uses familiar technology, the E-Business Suite implementation-time setup configurations will not change. They were selected years ago for Release 10 or 11i. Are they good for the business today? Will the 11i structure, when re-cast in R12, still be good for the business through 2015?
Transform Then Upgrade	There are stakeholders in the business whose success depends on the business capabilities due when R12 goes live. R12 business value is accepted. The business is not satisfied with the current 11i instance or instances. The organization is resource-constrained but maneuvers for a single global R12 instance, without compromise, with a go-live date sooner than anyone expects. Landscape: One or more production instances. Implementation-time setup configurations may need to be changed to realize full R12 value and properly model the business for the next five years. Plan: If there are multiple 11i instances, use a commercial software product from eprentise to consolidate them into a single instance, without losing any data or database integrity. If the implementation-time setups need to change, use eprentise software to change them. Run Oracle’s upgrade to take the single, pre-conditioned 11i instance to R12. Go live in 4 - 10 months. Consider: The business still needs to define what must be different in R12 to maximize value, but they don’t waste effort on configurations and setups that need not change. Business value sooner + lower transition costs = faster payback. Many eprentise customers transform their 11i instance(s), go into production for a while, capture some of the business benefits, then wait for the best time to complete the upgrade to a single R12 instance.
Partial Reimplementation	The organization doesn’t have the resources or the technology assets to go directly to R12. Top management may not understand how R12 helps the stock price. Getting part of the business on R12 becomes the short term, intermediate objective, to build support for R12. Landscape: There may be one instance with obsolete setups, or multiple 11i production instances. Plan: Select a small, relatively simple business unit to be the R12 pilot. If there are multiple 11i instances, upgrade the most recently implemented one. Alternatively, create a fresh implementation. Go live in 12 – 24 months, then monitor success. Advertise R12 throughout the business to create interest in R12. Consider: The next objective is now more complex: to get from the mature, core 11i instance(s) and a clean, lean R12 instance to the single global R12 instance. The intermediate situation is not of much value, but it looks like progress. R12 is live. The next phase is to go from division to division and reimplement each into the small R12 instance. There may be a learning curve where the re-implementations and data conversions get easier. This takes another 12 – 24 months before the enterprise is live on the single R12 instance.
Full Reimplementation	The organization is not satisfied with the current 11i instance or instances, and they accept R12 business value. They are willing to spend whatever it takes to configure Oracle right this time, or to get to a single instance, or both. There is not much value in the 11i historical data, which may also be corrupt and untrustworthy. Landscape: One or more instances, or obsolete implementation-time setup configurations, or both. Incompatible with the new R12 architecture. Plan: Leave the 11i instances behind, implement a fresh R12 instance, and import all the 11i data. Or import just some of the old transactional data. The R12 instance may take 14 – 36 months to go live, but it will work fine for the long haul. Consider: It’s a big effort for a global business to implement and deploy an enterprise application. Many across the business will be drawn into the design, decision, and testing effort. Most EBS customer will be forced to engage outside help. There is risk that a project this big and long-running will run into trouble keeping resources and funding, with changing business requirements, and technology shifts over its life.