Posts Tagged ‘Oracle ERP’

Star Wars Episode IV: A New Hope – R12 Self Service Jedis

April 14, 2011

Now I can’t do the scrolling Yellow Text and the famous Star Wars fanfare, but I reckon most people will have already got the Star Wars theme for this blog. The force is obviously strong in you.

There is a serious disturbance in the force across the corporate Galaxy. Have you ever thought that your company is built up of numerous Sith Lords, all wanting to basically build a Galactic Empire of epic proportions, with no relevance to the goals of your Business.

Think of it this way. Each of those Empires is building deathstars 0f immense power over decades. Each of these wields terrible power across your company reaching to every corner of your corporate universe. At the head of these powerful empires are your typical Darth Vaders, Darth Sidius’s and other villains that steal the lifeblood from your company and quash any rebellions against them with absolute force.

However despite the absolute power, in our galactic quadrant, a small group of Rebels started a rebellion with a view to wipe out the tyranny of the Evil Empires and bring freedom to the corporate galaxy. These few were the R12 Self Service Jedis. Or perhaps you’re a Princess Leia wanna-be? (If you’re a guy and a Princess Leia wanna-be then I’d certainly find you a little strange in a long flowing white dress, but each to their own……)

So what better way to start with than a movie  recommendation. It has to be Star Wars Episode IV: A New Hope. This was a truly cutting edge movie (which a top movie critic in the UK stated was “rubbish” at the time of release – there was a truly epic screw-up……..). Well Star Wars was the thing of dreams for millions of kids (and adults) and continues to be to this very day. It’s a 10 out of 10 movie and you should buy it on DVD.

In 2001, our company built an ERP deathstar providing the various Empires powerful facilities that did everything to consolidate their power. Bar one item. Included in this deathstar was one area that could, maybe just could, undermine the Empire. There was a small part of Oracle ERP dedicated to the galactic citizens. This allowed galactic citizens to check Galactic HR records for:


Staff Receivables (*)

Pension (*)

Company Savings Scheme (*)

Bank Account designated for Payroll

Personal Information

Staff Information


(*) Indicates custom Screens we have written in OA Framework

What is interesting is that many of the above screens are pure standard functionality. Easily and very quickly enabled across the entire Galactic Empire.

Enabling the simple Staff Information gives your staff access to Employment, Salary, Performance, Training and Job Applications with a simple addition to a menu. (The screen has most of the data removed for privacy reasons).

Now perhaps you are thinking big-deal, who cares about Payslips. Well we have 2500 staff across 27 Galaxies each requiring a Payslip twice per month of 3 pages. We also have 1,000 Pensioners requiring a Payslip across 66 Galaxies. That’s over 200,000 pieces of paper that have to be printed, put in an envelope, sent to the various far flung corners of the galaxy and all that costs a huge amount of time, money and bureaucracy. Putting a Payslip online (and have it populated as a natural part of Payroll) wipes out this pointless costly burueacracy in one swift action. Start to consider the effect for a Galactic Corporation of 25,000 employees, or 50,000 employees. The savings year after year after year are immense. Not to mention that online Payslips are delivered at the speed of light, being available seconds after Payroll is completed.

(As above all the key information has been removed for privacy reason, i.e. my privacy……. although as can be seen last month I cleared 34 cents – it was better than usual 🙂

For 30 years this Payroll Empire outpost had created tyranny and bureaucracy by making citizens beg for key information that was held on the Empire’s central computers. This functionality brought a small part of freedom to the citizens in this far flung quadrant.  However the Jedis from the Oracle Corporation sect quickly left in 2001 as these were mercenaries, who would only stay whilst the galactic credits kept flowing. After that the Galactic Empire’s consolidated their power, insisting each citizen continue to file vast quantities of paper with no specific purpose.

Our journey to restore freedom to our corporate Galaxy fighting against the Empire began on the small planet of Tatooine some 6 years ago back in 2005. Surveying the corporate galaxy the Jedi’s looked for areas of maximum gain where an attack would carry minimum cost and risk.

The Empire ran a facility to give Loans to Galactic Citizens (Employees). The loans were ironically managed by an army of Galactic clones that actually managed the loans and deductions to Payroll MANUALLY using spreadsheets. There was no risk on this, given they were TRIPLED CHECKED, leading to even more clones.

The Jedi’s reviewed this facility. A citizen could loan based on their Galactic Salary and length of service to their company. The Jedi’s checked and  they could get this information from the HR System. A citizen could request for a loan to be paid into their Bank Account. This could also be found in the Payroll/Payables system. The Loan could be paid using Accounts Payable and R12 Payments modules. Finally Loan interest was deducted each month from the citizens Payroll. Check – The Jedi’s could do this through 100% automation.

So exactly what were these clones doing that they demanded citizens to fill out reams of paper, have it checked in 6 separate Empire facilities shuffling paper between each, wait for weeks or months for the approval, then finally pay the loan. This was Galactic madness, purely to justify the Empire’s existence.

With a simple OA Framework screen linked to the Empire’s Accounts Payable facility, the Jedi’s managed to remove the Empire’s clone army in a six month operation. Citizens would apply for a Loan with a few clicks of a Self Service screen. This sent an invoice to Payables (pre-approved) and fed through to R12 Payments, then through SWIFT to the citizens Bank Account. The end to end application process moved from 2 weeks to 2 minutes, with no Empire intervention.

The second part of this sytem, the Loan Engine created by the Jedi’s plugged into the Empire’s computers and automatically removed the correct interest each month from the citizen’s Payroll, with no Empire intervention from clones. The Loan Deduction fed through automatically to the Self Service Payslip.

This system now processes over 4,000 Employee Loans every year. Imagine the manual work that was done before this Self Service System was deployed.

Given this was the first attack on the Empire, everything was automated, except for the R12 Payments module, which still required manual runs of the Payment Cycle Programs in R12 Payments. The Jedi’s were careful about picking full scale battles with the much feared Sith Lord Dollarius at this early stage in case the Empire became aware of the serious challenge that was growing within. A key tactic in the Jedi Wars to bring the freedom of Self Service to the Corporate Galaxy was to carefully choose their battles, delivering quick wins, at limited cost and risk.

(Again the screen is censored, but normally you could see the Loan amounts you can borrow, current loans, etc)

The next obvious move was to take out an Empire facility that did nothing but produce Certificates for Citizens. These Certificates could be for Employment, Salary, Wages, Tax and many more. Staff used ancient technology from the days of the Old Republic to fill out Old Republic Excel sheets and sign, and send to the Empire Central Processing Facility. The Empire demanded citizens wait two weeks for their own information, whilst armies of Empire Drones ran reports, got the information, sent memos to other facilities, got returned information from said facilities, cut and paste a variety of sources into an Old Republic Excel or Word document, checked the information and waited for the return of Darth Vader, the feared Head of the HR Facility to physically sign. This was the information tyranny that was feared throughout the corporate galaxy, draining desperately needed funds from well run Republic planets to fund an army of mindless Empire drones in bureaucracy.

With the plans from Princess Leia to the facilities HRMS System, the Jedi’s worked out that a small custom system to access this could be used by the citizens of the Old Republic to access their own information, removing the need for the endless army of Empire clones.

“There is something very wrong when it takes so long to get your own information from the Empire”

Obi Wan Kenobi

With a small OA Framework screen, loading of electronically scanned signatures and a little bit of BI Publisher force, the Jedi’s slashed through the endless bureaucracy of the Empire. The Empire’s Darth Attornius questioned the validity of scanned signatures, but traders and governments throughout the Empire readily accepted these, allowing citizens to apply easily for Bank Accounts, Tax Refunds and all manner of other necessities.

A signed (scanned) certificate with the citizen’s own information could be generated within seconds by the citizen themselves simply by selecting the certificate type in a Self Service screen, pressing a button and waiting a few seconds for a PDF file to be generated, with a scanned signature attached. Imagine a process that involved many people, many departments and took weeks to deliver information that citizens often needed quickly. Imagine that process where absolutely no-one was required, the citizen pressed a button and got what they needed within 5 seconds. That was the power of the Jedi Self Service.

Another small Empire outpost fell without a fight to the R12 Self Service Jedis.

In 2006, the year we launched this Self Service feature, we had 2,500 Certificates created. By 2010 that had grown to over 6,000. (For a company of 2,500 people).

This could be delivered by any small Jedi strike team in a very short period of time at minimal cost. Scale this up to a global entity of 50,000 and savings are immense. The other immediate payback is a lot of very happy citizens who get what they need in seconds.

(Note there are confirmed rumors that the Jedi Sect from Oracle Corporation has provided Certificates in R12 which the Empire and most Jedis are unaware of. See Employment Verification in Oracle Self-Service Human Resources Deploy Self-Service Capability Guide (Part B31648-03)

This short episode outlined the initial skirmishes with the Empire, mostly which went to plan, to deliver the initial foundations of Self Service functionality and freedom to the Corporate Galaxy with rapid ROI.

The R12 Self Service Jedi’s will continue with Episode V – The Empire Strikes back shortly…….

Further Prophecies can be found at


R12 Security and the Auditors from Mars

October 9, 2010

Let’s face it auditors are not from this planet. Rather like the famous movie War of the Worlds, they bring fear and terror wherever they go. Are they from Mars? Probably, although the one thing certain about auditors is the uncertainty they bring with them, together with the general fear and terror they instill.

I thought the picture below was a rather appropriate one of a hand reaching out with the entire planet in its grip…..when the auditors are around you sometimes get the feeling that your entire company is under the grip of these terrifying Auditors from Mars…..


Now this column seems almost like a recommendation for movies these days, so if you’ve not seen War of the Worlds, get it on DVD. It’s a great movie.

Now as someone that had not one, not two, not three but FOUR different companies looking at my R12 Upgrade Project (AND FINDING NOTHING!!!!!) I have to say I’ve learned to deal with them pretty well over time. Our first R12 audit was from our internal audit guys, who wanted to learn R12. It’s important for internal staff to learn, so we were happy to help. We then had an audit from E&Y as our R12 Upgrade was part of a large Program…….we then had a pre-Attestation audit from PWC to make sure the project was OK from an attestation viewpoint. Finally we had another guy from a large Consulting Organization looking at it and never quite figured out why. (Guess they also wanted to learn how to do an R12 Upgrade). Amusing that these audits cost 30% of the actual cost of the upgrade and ended up commending the R12 Upgrade 🙂 One audit surveyed random users with 20 out of 21 commending the team, with the other neutral. It was a lot of work providing the auditors everything they needed, but I have to ask – 4 audits???????

Now here’s a question for you. If you saw an auditor drowning in a river, would you run to get the life belt 30 feet away from the river, or run to your car 50 feet away to get your video camera? Alternatively if you’re a Project Management Professional (PMP) you’d have planned ahead and kept a bag with bricks in your car beside your video camera. As you throw the bag of bricks the auditor is thinking “What a nice guy throwing a bag to keep me afloat”, whilst you are thinking “What a sucker, that’ll help him sink……”


I have to point out you should not take this blog too seriously, as the above may get you into some serious trouble, although with a good lawyer you’ll probably get off with a small fine as it could be seen as justifiable or reasonable……. You may be able to even make some money back to pay for your legal fees from the video on America’s favorite home movies show.

There is no question that Auditors are from Mars, causing fear and terror wherever they go………well recently they struck on an area that probably too many people just don’t take seriously enough or don’t even realize is their responsibility. This area is Data Security within your ERP regarding access of your Support Team. Now what is worse is that when auditors do raise this they generally have a very good point and your best interests at heart.

So we’re almost going back to our Koan stuff (see the Zen column). How do you do support Oracle ERP Production when your support team can’t access Production due to information and data security concerns?

That’s a difficult question and I’ve yet to see any really effective, comprehensive solution either in a blog, white paper or from Oracle themselves (at a reasonable price – I had a discussion with Oracle last week and almost fell off my chair – the Oracle Sales guy might as well have had a mask and a gun…….).

So you’ve got your auditors on the one hand, a whole raft of legislation on data security (your legally obliged to follow – it’s not a matter of choice) and a very limited IT budget. So how do you square the circle to keep every party happy?

So look at your typical ERP these days. It’s a monolithic, super system:

  • Financial performance – if you’re a listed company that causes major data security issues to prevent internal staff dealing in shares, because they know you had a record quarter looking at your General Ledger before it was announced or worse short your shares if they know there is bad news coming. That’s real company loyalty for you.
  • It probably has all your HR information in there, including performance appraisals (some none too pleasant quotes on how a staff was involved in some questionable activity), potentially medical information, salary of course and a whole lot more.
  • You might have Procurement information, so a huge amount of confidential contracts also.
  • And most ERP sites run Payables and Payments. Do you know the potential fines you can get if you lose bank information? Or worse if you lost your Supplier bank information and the damage that could do to your company’s reputation, not to mention legal liability.
  • Ever worked out that your Accounts Receivables is a very good indication of a customer’s credit rating? After all, you kind of know who’s got cash flow problems……And the Bank Account information is everywhere. Are you really looking after that securely? Your customers won’t be too impressed if you lost that and would probably never do business with you again.
  • Or are you capturing credit card information as part of say your Order Management/Accounts Receivable process…….Have a look here if you are.

Data Security is an absolute minefield for any IT department responsible for Oracle ERP.

There is just so much legislation on this area that most are blissfully unaware of:

  • Sarbanes Oxley
  • PCI
  • European Directives
  • Individual country legislation
  • And a whole lot more…..

This is serious business and can really hit you hard if things go wrong – blissful ignorance is no legal defense. And yet I bet you haven’t reviewed anything from a legal viewpoint on this……

There’s a couple of interesting links at the bottom. Choicepoint had 163,000 customer records compromised. That cost $15 million US Dollars, not to mention reputational damage and cost of implementing proper data security. The data involved? Names, Social Security Numbers, Birth Dates, Employment Information – sounds pretty much like Oracle HRMS right – they got fined for not protecting exactly the type of information you’re holding…..Or how about the California hospital that got hit with $250,000 penalties just for reporting a breach late……

Not in the US, so don’t really care? Have a look at this link. The UK has put in place legislation for fines up to GBP500,000 (equivalent to around US800,000) for data security breaches. Or HSBC, one of the largest banks in the world, that got hit with a fine of almost 5,000,000 US Dollars.

So not in the US, not in the UK, still don’t care. How about the following:

A German company fined 137,500 Euro for asking employees why they were sick and storing the information. If you run absence management, are you potentially collecting scanned medical records and are they properly secured?

Still not convinced? Forrester estimates a cost of $155 Per record for data security breaches. Lose 100,000 records and you’ll be looking at $15.5 Million US Dollars legal liability and probably looking for another job…….I guess that’s finally caught your attention? Legislation has been enacted or is being enacted worldwide. Sooner or later, it’s going to hit your country too.

Now I have to state from the outset this article does not provide all the answers. This is a highly specialized area which goes way beyond my own personal capabilities in this area. So if you’re looking for all the answers, hire a specialist. Actually read hire multiple specialists over a number of years. Security is a massive task to do well and therefore effectively.

The aim of this article is simply to show what we are doing and have done to implement a more robust security around our R12 Oracle ERP that allows us to support the Business, whilst still trying to do our best to be secure.

This purely looks at using roles to restrict database access for support people. This article does nothing more and that has to be stressed at the outset.

Hopefully it can get you at least part of the way there, giving some additional protection without costing a fortune.


Step 1 – Find out what access was out there historically

Our starting point was to figure out from an Applications side what access our IT guys had to Production (and note when I am talking IT I am talking ERP Support only in this column. Other areas such as DBA, System Admin, etc are beyond the scope of this article). Luckily we were already very heavily locked down. We don’t have any access to modify any part of Oracle ERP and I’d say that’s a very good thing. It makes me sleep easier at night knowing that there is no way my staff can log in to Production, “cause there’s a really easy way to fix something………”. Every change we do to Production is through a formal turnover to separate groups, fully approved, appropriately segregated and audited. That keeps our Production system safe. (All code is also in source control using Subversion just in case we ever need to rollback a change).

The next step was to see what read-only access was given to Support Teams. Now over the years, many IT systems just grow and grow, access is given for support purposes and no-one really thinks too much about it.  I was relieved to see that the Support Team had no access to Oracle ERP through the front end applications.

I thought I’d throw in a useful note at this point. We have a custom program that reports quarterly on all menus/functions/applications by person. It replaces the standard Oracle System Administrator reports that generate listings the size of War and Peace. This report is reviewed both in IT and Business to ensure that each user has the appropriate access for their job. We also tied this to staff movements within our HRMS System, so that when someone moves, this is highlighted as a potential access control violation and can be assessed before it even happens (using date-tracking in HRMS). The other useful point here is that this quarterly check also ensures complete segregation of duties. Finally by using a custom report and showing dates menus/functions were changed, it vastly reduces the work to check each menu against each data item. Actually a single maximum date at the top of the report means the users only check this and can thereby can confirm no changes from the last review. Not perfect, but keeps our auditors happy. This is a cheap and cheerful approach without buying Oracle’s GRC Suite although I think the GRC Suite looks very interesting indeed.

With our front end applications secure from our Support Team, we then started to look at the database access. Now I know some people say IT Support should have no access, especially the evil DBA Vogons from Vogsphere, who according to the book The Hitchhikers Guide to the Galaxy have “as much sex appeal as a road accident”. On the other hand others say “With no access, I can’t see anything, therefore no support’. We’re back to Koan (See R12 Patching and the Art of Zen) here – How can you fix something you cannot see? (To our DBA friends I heard the new 11G Database Release 2 is self-managing, so exactly what do you guys do???? – that should provoke a few comments 🙂  )

Our support team had read-only access using database roles to Production, but when we looked at the roles created we concluded that these were too broad and needed some serious work. Roles are simply access to database tables/views or other objects given to a specific support person by the evil DBA Vogon group.

Note: For those interested in Vogons, it’s from a movie called Hitchhikers Guide to the Galaxy. Here’s a movie recommendation. Don’t see this as it sucks big-time (although the original TV series and radio show and book were amazing).

Now our next step was pretty simple. Monthly we review database access to Production and with the count of accesses by support staff, we found that many actually just didn’t need access to Production, because the support volumes regarding database queries by support staff were low (or zero). We cut these immediately.

Again doing a review monthly (only takes ten minutes) ensures that as people leave, move job, etc your access controls remain updated and correct. Again this keeps the auditors happy.

At the end of this step, we defined a Security Access Matrix, showing each Database Role against each Support Staff, shown below. This was our first baseline.



Step 2 – Defining Your Database Security Architecture

Ignore the fancy title. What this means is simple. You need to look at:

  1. Your Support Team Structure
  2. Your Applications
  3. Your Required Access

So let’s take an example. We run a fairly large ERP footprint:

  • HRMS Suite (HRMS, Payroll, Learning, Time & Labor, Self Service, Advanced Benefits, iRecruitment)
  • Financials (General Ledger, Payables, Payments, Fixed Assets, SLA, EBiz Tax, Accounts Receivables)
  • Procurement (Purchasing,  Services, Inventory, iSupplier)
  • Projects
  • A large number of custom modules
  • We’re going to add CRM (hopefully Fusion) to that in 2011.

Our Support Team is organized along classic line of business model (Procurement, Finance, HRMS). It is further segregated so that certain support people provide support for say HRMS/Payroll, others do Self Service, etc.

Now the problem we had was two-fold:

  1. Too many Support people had too much access
  2. The data protection needed to be much stronger

Our approach was simple, but effective. Each role would be along the lines of module or modules required for logical support. So we would set up roles for Procurement, AP/Payments, General Ledger, HRMS, etc.

Now this for us still didn’t get us to where we wanted to be. What was going to be in these roles database object wise?

Our next task was to then define exactly what that role would have access to table and view wise. Your average ERP Module can have several hundred tables (or even more….). Now you can give a blanket access if you want, but I think that’s overkill. It’s hard to audit and hard to police. Your average support person needs access to only a limited number of tables. That is what you SHOULD define.

In addition, obviously there is some cross-cutting of access between modules and tables, so for instance you may have to grant GL_CODE_COMBINATIONS across various roles.

In our final step, to the horror of many, we decided to NOT give access to standard sensitive tables, but instead to build views WITHOUT KEY ELEMENTS. Now a lot of support people are going to scream at this, but I need to ask, if you have a support call, do you really need access to the guy’s date of birth or performance or disciplinary records or even Full Name? I doubt it for 99.99% of cases.

Here are a couple of examples of securing tables into masked views:

Instead of giving access on PER_ALL_PEOPLE_F we removed all information except the Employee Number. No access to Social Security, Performance, Home Phone Numbers or anything else.

Instead of giving access on the Bank Tables, we removed all information and masked the bank account.

Instead of giving access of PO_VENDORS, we removed all information and simply gave the VENDOR_NUMBER.

What you need to be careful of is the other tables that you really wouldn’t expect to hold sensitive information. A prime example of this is the workflow tables.

To secure workflow tables we ensured that each role only had access to relevant workflow ITEM_TYPES. This was a very simple, yet highly effective way to secure this by role.

The great thing about this is that we got our support people to define these roles in a simple spreadsheet. This gets you your buy-in from the very people who would be screaming about cutting access. After all they are now the ones tasked with defining it (obviously subject to reviews within IT and approvals by Business).

We then decided that we’ll make this formalized. This has a lot of benefits:

  1. It provides an agreed document of what a role has access to data-wise
  2. It provides an agreed document between Business and IT on that role/data
  3. It provides a document that can be agreed and signed by all parties
  4. It makes you think about your data and protection of said data in a disciplined manner
  5. It provides the basis for all future access to sensitive data by IT Support Personnel
  6. It provides a clear statement of who has access to what in a transparent, controlled manner
  7. It provides protection from your auditors

We decided to record other details in this spreadsheet. (Each role has a separate spreadsheet).

The spreadsheets had the following columns:

  1. Object Name
  2. Database Owner
  3. Object Type
  4. Business Owner
  5. Description
  6. Object Classification (Custom/Standard or Secure. A Secure Object was a restricted, masked view of a key table)
  7. Data Security Risk (Critical, High, Medium, Low)
  8. IT Review Date

A separate Tab (Security Details) on the Excel Spreadsheet held additional details

  1. Object Name
  2. Base Table
  3. Masked Columns (.e.g. vendor name, bank account, social security number, etc)
  4. Date Approved

The Spreadsheet was split into two sections – data objects and reference objects. (Reference objects are the pre-seeded or setup tables within R12). A further tab showing review actions was included to show that this was a serious, considered exercise to the auditors.


 Suddenly, without even realizing it, you’ve just created a very precise, auditable and comprehensive technical security framework for your R12 Oracle ERP that will vastly improve your security of data, whilst still allowing effective support. This also provides a very powerful data classification framework for both you and your users. Even more interesting, by putting a risk score on each object, you can actually see the overall risk access rating of each role, allowing you to make informed decisions on how widely this role is given to support personnel (or not).

If you have knowledgeable, motivated people, this doesn’t take a huge amount of time. I am extremely lucky to have such a great team of people.

Once the roles were defined, signed off and agreed, the DBA Team created the roles/access very easily.

Note that once you have these documents in place and have implemented the framework within your database, these documents (and hence the roles within the database) should be strictly controlled otherwise all your work will have been for nothing……

So after not a huge amount of work we had roles for most applications that were nicely segregated, secured and with all key information masked appropriately. This can be seen in the Security Matrix below:


(Note how the General Database Access role was entirely removed once we worked out roles/access. The above is only a sample, not the real resources/roles).

So in a couple of weeks we went to very fine grained security on our Production System, without spending vast sums of either cash or time.

Our overall security had vastly improved for our Support access to Production. Indeed the data had really been masked very effectively yet we still could do effective support, without disclosing any critical data to our IT Support Staff.


Step 3 – Defining Your Access Security Processes

Without policies and procedures any security framework will fall to bits pretty quickly. Now this article isn’t really about processes, but here are the key aspects to consider. Security is not a one-time process. It’s a critical ongoing process that needs to have strong controls to keep you secure going forward:

  • When people join our company, we ensure that non-disclosure clauses are signed the very minute they step in the door.
  • Security is not just about securing your database or applications or data once.
  • The Database Security Architecture showing each module/access to tables/views should be agreed and signed off by the Business. It is important that Business understand the access IT has to data in a transparent manner. Too many IT organizations think they own the data. They don’t, they are merely custodians.
  • When new access is required for a Support person, or additional access is required, there must be a strong process to seek authorization from the Business for that access. This process should be formalized, ideally automated and fully auditable. We are currently implementing Oracle’s Identity Management product and intend to integrate the process into this.
  • When a person changes role or position, it is important that any access is immediately discontinued. Linking your process to the HRMS System gets you part of the way there and avoids any embarrassing moments when the auditor asks why a person that was fired still has access to your HRMS database details……..
  • When the Database Security Architecture documents need adjusted to add a new table/view, this must go through a formal change control process, with appropriate sign off by the Business users.
  • The DBA Team should not update any role to add/modify any table/view unless the change has been formally signed off in the Database Security Architecture documents. All requests for changes in the database should go through a formal turnover process which is fully auditable and approved by IT Management and Business Users.
  • A monthly review of Support Personnel/Access/Roles should be done by the person managing the Team. This doesn’t take long and ensures that only those needed access have access. Documenting this process protects you when the auditors role in. (Note the Oracle Database can be configured to automatically email you the roles/people/number of accesses)
  • There is one other interesting area. If you formalize the Data Architecture documents and make that part of every project before go-live, you’ll also ensure that support for go-live is properly thought through and adding new systems does not compromise security going forward.

 Step 4 – Encrypt Non-Production Databases

I’ll cover this in another article (working title is Securing your R12 using Captured Alien Technology from Roswell), but the key point here is that there is no point in protecting your production database, if you then clone and have all the same access in copies of production……..Security is about all your databases, not just Production.

 Step 5 – Securing all Layers

Again, this is beyond the scope of this article, but keep in mind that the best security is a layered approach. Your Oracle ERP security framework must involve all of the following:

  1. Network
  2. Servers (Linux, Unix, etc)
  3. Oracle ERP (across the modules)
  4. Individual Modules (each with their own security models)
  5. Database
  6. Security Patch Policy across all areas

Security is only as strong as the weakest layer…….A couple of the best articles that take a holistically approach are found in the Related Articles Section at the end of this article.

 Step 6 – Future Directions

Our goal in this article was to make others aware of simple steps to improve security for Support Staff supporting Production systems by using role based access at the database level.

However, as we looked more and more on the security, and as we became more aware of the challenges, but also consequences of getting it wrong, we see other opportunities that remain very interesting future initiatives that we will seriously look at.

We’re interested to lock down database access to restricted IP addresses. This will stop password sharing, but also ensure that even if an account is compromised, unless you sit in the person’s chair, you’re still going to have difficulty accessing. Not bullet proof for sure, but it adds another layer in our security.

We’re interested in auditing the database statements of support personnel and perhaps moving database roles/custom built to a more secure and robust solution. It looks like Oracle’s Audit Vault may get us there, but at a heavy price. This can implement roles in a more secure manner, provide full auditing, reporting, restrict data by IP address and a whole lot more.

We do already have data scrambling and encryption on our non-Production databases, along with formalized controls and processes. However as one article points out, the days of custom scripts are coming to an end (or is it just an Oracle sales pitch to get you to buy the products?) and we are seriously looking at a couple of options:

  1. The Oracle Enterprise Manager with Data Masking Pack. This allows you to setup and execute data masking. With Grid Control you can then have central data masking across all Non-Production databases. So yes they are trying to sell you even more, but then again, it’s better and cheaper than a 15M US Dollar fine for non-compliance…..
  2. Oracle Application Management Pack for ERP – This is a pretty broad release that provides functionality for Configuration Management, Application Performance Management and Service Level Management (including related to this article obfuscating sensitive data). This will allow you to clone and scramble data in a single process.


 One area we are interested in looking at in the respect of functional changes, setup, etc is the GRC (Governance, Risk and Compliance) module. This provides a lot of the audit capturing and reporting capability as a by-product of the general setup process. More details can be found at the end of this column. We haven’t fully evaluated this product but it does look interesting. Again Oracle has added analytics that could potentially make this a complete, all round solution (subject to our evaluation).

Oracle Identity Management. We are so interested in this product that we actually went out and bought it. This will once fully operational, control access to all our key systems, allowing us to comply with key controls from both auditors and legislation. We intend to use this directly linked to our HRMS system to basically provide all provisioning of user accounts, access, etc. This is linked not only to the triggering HR Process, but also directly to the Business owners who grant access to the modules, all using Oracle workflow technology. We’re also having a close look at the Identity Management analytics, although have not bought this yet. The two products combined can not only provide very strong security, but save a huge amount on the cost of our compliance, which becomes more and more every year……



Our final exercise is to get every Support Person off Production completely. We’re working on nightly, automated clones with data scrambling. At that point we expect to remove almost all access to Production. After all you have everything you need to do your job as a support person, bar critical calls. This is where we really want to be, but whether we get to that point remains to be seen, but we’re certainly going to try.

Of course, we’ll have all the usual security exercises on database hardening, ERP hardening, Linux hardening, segregation of duties, access control, etc going to ensure we have continuous improvement across these key areas.

I hope you’ve enjoyed this article and hopefully it provided some useful insights into how we are improving security and easing our compliance burden overall. This article has mainly been on securing data in Production from support, but I guess I’ve gone off on a tangent into some other closely connected areas also……

 I’d be very interested also to hear comments from others, as I think security is such a vast topic that really others views (please do comment) would be very helpful.

So what are you doing on security of data encryption/access to production data?

I’d very much appreciate anyone to comment. (I have written this column in the hope of actually getting feedback and learning what others are doing to improve what I also do 🙂  )

And now, if you’ll excuse me, I’m off to apply a security patches to Production before the PWC Auditors from Hong Kong (awesome city) role in for a Penetration Test in October……..I like to call them my Loser friends (nothing like poking fun at the auditors….the loser buys lunch……), but they are immensely smart guys (as most PWC folks seem to be) and security is always a moving target requiring continuous improvement, so it’ll be interesting to see if I put one over on the auditors yet again (where they find nothing) or they finally put one over me and I need to buy them lunch…….

Of course either way, our company will be a safer and better place, security wise.


Related Articles

I hope that as you start to consider security in more depth, you may find the other articles relevant and useful reading also. My thanks go to the generous authors on a number of the articles below for sharing their knowledge for the wider Oracle ERP (and also Oracle Database) community.

Oracle Audit Vault

Oracle Governance, Risk and Compliance

Best Practices for Securing Oracle E-Business Suite Release 12 (Note 403537.1)

Oracle Identity Management

Oracle Identity Management Analytics

Oracle ERP Management Tools and Solutions

Oracle Applications Management Pack (with Data Scrambling)

Oracle Enterprise Manager 11G

Oracle Enterprise Manager 11G Data Masking Pack

Security Breaches – US Company Penalty

Security Breaches – UK Data Protection Penalties 1

Security Breaches – UK Data Protection Penalties 2

Security Breaches – California hospital Penalties

Security Breaches – HSBC Penalties

Germany Company Penalties

Data Masking – Strengthening Data Privacy and Security

Solution Beacon Security Column

Solution Beacon Security Best Practices

Project Lockdown – Securing Your Database

Recommendations for Leveraging the Critical Patch Updates

Oracle Security Column – Technology Network

Mask your Secrets using Oracle Enterprise Manager

Oracle Security Column – Documentation and Best Practices

Oracle Security Column – Critical Patch Updates and Security Alerts

Business Intelligence and the Kung Fu Dragons of Wudang

October 3, 2010

I recently watched an interesting program on TV about the Kung Fu Dragons in Wudang, China. The students join from a very young age and basically study 16 hours a day to become a Kung Fu Dragon Master over many, many years. The training is difficult, filled with risks of failure and many give up before ever coming close to becoming a Kung Fu Dragon master. Indeed the truth is most simply don’t have the skills, time or patience to reach the heights of a true Kung Fu Dragon Master. Very few will ever become a Kung Fu Dragon Master.

Now you look at data warehouses and Business Intelligence and ERP. It’s extremely difficult, filled with risks of failure and many give up (after spending a fortune) before they even get close after many, many years of trying. The truth is most simply don’t have the skills, time or patience to reach the heights of a true Kung Fu Dragon Master in Business Intelligence and ERP. The parallels and similarities are indeed striking.

Now the Art of Zen Patching article talked about patience and a non-aggressive approach. Not so this article. This article is really designed to allow you to become a true Kung Fu Dragon Master in Business Intelligence in 9-12 weeks (in vanilla implementation) and stresses a highly aggressive approach to Business Intelligence and ERP. (This article doesn’t ask for any money to be sent for some dodgy DVD and a certificate I printed in my bedroom. This is free, it’s not marketing hype – this is the real deal).

A cautionary tale before I start.

I’ve seen the two sides of data warehousing with Oracle ERP. One European company I know bought a bunch of expensive, state of the art tools almost a decade ago. Almost a decade on, with a lot of tears and pain  (sounds like a Kung Fu Dragon training course…..???), 2 million bucks and a change in system architecture that will require massive re-work, users simply not adopting because what has been achieved is small, fragmented and ad-hoc, and performs like a three legged dog (indeed in the user manual it instructs the person to submit the report and then get a coffee for 20 minutes before coming back or lunch if it’s a larger report) – the dreams of being a Kung Fu Dragon Master in ERP Data Warehousing lie in shattered pieces, a ruin of what should have been a shining example of 21st Century technology. This isn’t a one off – according to independent research a huge percentage of projects fail in the custom approach. Indeed it is the minority of ERP Data Warehouse projects that succeed.

Sure they bought the most expensive software, they put in piles of money, but they never sought a Kung Fu Dragon master to train them properly, preferring naively to think that they could learn the art of the Kung Fu Dragon Business Intelligence off a DVD in front of the bedroom mirror whilst their parents wondered what the weird shouts and snake hissing sounds were from upstairs………Instead of being a Kung Fu Dragon Master of Business Intelligence, they ended up as more like something of a joke reminiscent from the movie Kung Fu Panda……….Trust me if you’re a Kung Fu Business Intelligence Panda, people are laughing at you, not with you…….


(By the way, if you haven’t got the DVD for Kung Fu Panda movie, it’s highly recommended). As well as Oracle Fusion Apps being released in 2011, Kung Fu Panda 2 will also be out…..

Here’s the other side. I’ve seen Oracle come in with the prebuilt ERP Analytics, install the database, ETL tools, dashboards, KPI’s and reports and do all the data orchestration, extracts and imports in…… hours………Now I’m an utter skeptic when it comes to Oracle marketing and consultancy, but yes, I watched as one single Kung Fu Dragon Master in Business Intelligence and Pre-Built ERP Analytics achieved in 7 hours what could not be achieved in 7 years by an army of untrained people in another European company. Now this was only a demo (done for free as a demonstration of what could be achieved), but it took all our historical R12 Oracle ERP Accounts Payable data for 7 years and transformed it in a fully defined, fully functional data warehouse with dashboards in 7 hours. This guy was an Indian Kung Fu Dragon Master in Business Intelligence from Oracle Singapore. I was in utter awe of what could be achieved so easily and so quickly by one talented individual.

Have a look at Haemonetics Corporation story at Oracle Open World on Demand (Oracle On Demand Access Required) and then the results when they moved to Oracle’s prebuilt BI Applications approach.

The reason this utterly amazing complete data warehouse could be achieved is due to the pre-built Analytics packs that Oracle now provides (for of course, an additional license fee), together with of course an incredibly talented Indian Kung Fu Dragon Master.

So what’s the deal with Oracle BI and Pre-built Analytics?

Oracle is moving heavily into the BI market, taking the overall lead in terms of BI according to Gartner, much to the surprise of many.

In parallel Oracle is moving to create an unprecedented amount of pre-built content for Oracle ERP (including R11, R12, Peoplesoft, Siebel, JD Edwards with SAP in the works in 7.9.7…..) moving the task of creating a data warehouse from a vast army over many years (with a horrific failure rate) to a guy with an installation disk. Or that’s the single great hope and prophecy of Oracle Corporation. OK, we’ll never get to an installation disk, but if the guy with the installation disk can get us to 70% I won’t be complaining……..

Finally Oracle is now heading towards, probably in R12.1.4 to embedding the BI content directly into Oracle ERP screens. (The technology to do this by embedding into Java screens – not hanging a BIEE screen of an ERP menu by the way – was released as part of 12.1.2). If you also look at the new Fusion Apps, embedded dashboards are now an integral part of each module. According to Oracle at Open World 2010, there will be “hundreds of pre-built embedded analytics in (Fusion V1) or as they put it another way “Analytics are always available in Fusion Apps”.



Whatever BI tool or preference, you have to admit Oracle’s strategy and growth is certainly very impressive. With the recent release of Oracle BIEE 11G it just gets even better. The presentation layer is truly fantastic.


So many are probably asking, what’s the pre-built content for Oracle ERP?

Oracle has effectively written for you the following components:

  • Data Extraction from Oracle ERP (and many other ERP’s)
  • Data Orchestration (pulling dependent tables as needed with full synchronization)
  • Data Import to Data Warehouse with full management and restart capabilities
  • Data Model Design of Warehouse
  • Standard Dashboards
  • Standard KPI’s
  • Standard Reports
  • Security Models
  • Integration both to and from Oracle ERP
  • and a whole lot more

Now all of the above have been done not by a single Kung Fu Dragon Master in Business Intelligence, but by an army of Kung Fu Dragon Masters in Business Intelligence and ERP at Oracle Corporation.

These guys are experts in Business.

These guys are experts in Data Warehouse modeling.

These guys are experts in BI.

These guys are experts integrating BI to ERP.

These guys are experts at extracting data reliably from ERP to a data-warehouse.

The product line has come from a huge amount of hours collaborating with top businesses around the globe to find out what an ERP Data Warehouse should be

They have created something that no matter how much money and how deep your pockets, you will never be able to match, because Oracle Corporation is in the software business and can use economies of scale. Your company isn’t, despite the best intentions of your IT department.

The Pre-Built Analytics module list for Oracle ERP is equally impressive and is growing substantially on a consistent basis. This now covers:

Now it’s almost definite that this list will continue rapidly expanding, so that not only is there prebuilt content on ERP, but you’ll find it integrated directly into ERP (R12, Fusion, etc) and I bet into all of the other tools that Oracle has in the coming years including their database and other management tools. Basically if you are running Oracle ERP now, you WILL be running Oracle BIEE in the years to come.

Not only does Oracle Pre-Built Analytics create entire date warehouse content, they also add to it on a frequent basis. So when Pre-Built Analytics 7.9.6 was released it had a whole raft of brand new areas of HR addressed including Talent Management, Learning and iRecruitment.

So if you are still considering building using a custom approach to Oracle ERP Data warehousing, think about this foolish Kung Fu Panda……

Imagine that Oracle ERP has some 14,000 tables – do you really want to build a data warehouse from this (and will it actually perform…….????)

Imagine having to write extracts from the key tables which although much smaller is still huge. Imagine writing all the complex logic on something like Order Management for the extracts.

Imagine having to define a complex data warehouse model (and no, ERP and standard database design don’t work for data warehouses – it’s a completely different approach from a relational database model so your skills won’t work here……).

Imagine having to write processes to manage the extraction and import of data.

Imagine having to do the Security models.

Imagine having to do all the integration to and from your ERP.

Imagine trying to keep your custom built data warehouse in synch with Oracle patches, upgrades, etc

Imagine designing and building 100’s of dashboards with no idea of the proper principles of dashboard design and usability

Still fancy your chances of building a BI Apps comparable product Kung Fu Panda? Have a look at the summary data warehouse of BI Apps…….I guess you would be a whole lot less confident if it was your own money at stake on your custom build rather than your company’s……


The above represents years of work by an army of highly skilled, highly paid people, where the company must wait for a very significant time to get the results if you choose to custom build. Where you don’t get these highly trained people (as too many companies don’t), the consequence is simple – utter failure and massive cost.

And of course, even if you build it, what happens when you add another 2,000 users? Is it scalable or will you find major flaws in the design that are incredibly costly to fix? With Oracle Pre-Built Analytics scalability has already been designed in by the experts.

Still not convinced? Well let me give you the stats on Oracle’s prebuilt analytics:

350 Fact Tables

550 Dimension Tables

5,200 Pre-Built Metrics

15,000 Data Elements

And Kung Fu Panda, do you really think that the quality and performance and design can match that of the Kung Fu Dragon Masters from Oracle Corporation that do nothing but BI Applications? If so put down that Vodka bottle (or whatever else you are smoking) and get a reality check.


Or how about this Dashboard, with all the extracts, imports, data model behind it? For info this is Oracle Projects, one of the hardest niche modules in the entire ERP Suite, with links to EVERYTHING…….


In BI Apps just for HRMS there are 9 dashboards with a whopping 47 pages, 230 reports and 330+ metrics. And that’s just ONE MODULE OF BI APPS !

By the time you build that using a custom build approach (that’s so 1990’s in terms of approach – are you also still using Computer Punch Cards?) you’ll be retired or fired before you see it go-live……..

Today Oracle has the capability to deploy in 9-12 weeks (in vanilla implementation). Marketing slogans and nothing else? Absolutely not. We watched Oracle install everything (from database, to tools, to dashboards and migrate 7 years of data) and bring up Accounts Payable in 7 hours as a proof of concept. I can be pretty skeptical and hard on vendors, but believe me, this was truly amazing the ease with which this product could be brought up.

Now true there are some serious challenges around also, even with the Pre-built Analytics. Anyone thinking they can buy this and that’s it, well that unfortunately is naïve. I wish life was that simple……

The fit of the BI Analytics needs to be checked in terms of dashboards. What we found was that a lot of dashboards fitted very well onto the Business Requirements. Please bear in mind that dashboards are very easy to build (and BIEE is very easy to learn), especially when the Kung Fu Dragon Masters at Oracle Corporation have done the hardest part of building the entire data model and all the data extractions for you. Even if you find no use for the pre-built dashboards, simply having the data model and extracts will save you a huge amount of time. Now looking at Infosys they reckon a 70-80% fit in terms of BI Apps. I’d tend to agree that would be a ballpark figure when we assessed this against our ERP.  (And remember a fit means data extraction, import, data models, dashboards and all the other components).

If you do have custom modules, then you will need to build the extracts, data model, etc. However don’t be fooled by naïve arguments that if you’ve customized your ERP, BI Apps will not work. I’ve rarely seen a company modify base tables in ERP (add new custom tables yes) as they would be extremely foolish to do so. Therefore BI Apps will have just about every key table from the core ERP model you could possibly need. That’s a huge chunk of work already done.

You will need to define further dashboards and reports. That is a given. BI Apps will get you very far down the line, but it won’t be a silver bullet.

You will need to design data models and extracts for any custom modules you have. That can be a difficult task, but your ERP will already have all the data models, extracts provided by BI Apps. Custom modules are almost always a whole lot simpler than Oracle ERP Data models.

One of the biggest tasks that not even pre-built analytics will address is Training and user adoption. Moving from reports and Excel to Analytics is a major business change exercise and should be treated accordingly. The project can still fail even with great pre-built content, because users are not trained appropriately and there is a lack of vision in how this can really change the way the user community work.

The cost of prebuilt analytics is still a major problem I think. There are alternates out there that are much, much cheaper but then you have to consider upgrades, risk of building, integration and a whole host of other problems. I love the Oracle BIEE and the Pre-Built analytics but the cost is still pretty high. Of course compare the cost of that to the cost of building all this yourself and there is just no comparison between the two, like for like. Playing hardball with Oracle Corporation (ideally at their year end when the best deals can be achieved) can pay dividends in terms of price, although I’m not going to give away how cheaply we got offered the prebuilt analytics……There are still alternates to Oracle in many areas, solution wise and Oracle should be reminded that you can go elsewhere…..

Pre-Built analytics for ERP can get you there fast. But a big bang approach is not recommended. A fast initial deployment to a small community followed by consistent wins over a period of time, slowly building up the user base and expanding across the lines of the business is the way to go, bringing in and training champions each and every step of the journey. Then you’ll succeed where so many others fail. When everyone can see frequent wins (and with BI Apps it can be every few months), it becomes an unstoppable train that everyone wants to get on. BI Apps is beautiful eye candy that users seem to love. (Or is it that they finally can get their own answers in seconds without waiting 12 weeks for the IT Department…..)

You can buy the software, but you need to sell the vision, otherwise you fail. IT Alignment to Business Alignment and support from Business is the key to any successful project.

I’d still recommend having a BI Expert around who you can gain the knowledge from. Again Pre-Built Analytics is not a silver bullet and your staff will need to be properly trained by an expert.

I think all companies running ERP should be looking seriously at BI Apps, even those with the approach of building everything. In the 90’s companies realized the value of letting Oracle, SAP and the rest build their ERP applications. That’s now a generally accepted principal by most companies around the globe.

Over 2,500 companies have already adopted the BI Applications. If it was cheaper (a message to Oracle Corporation and Larry Ellison) the uptake I am sure would be massive.

In the coming months, I’ll write a few more articles on deep-dive into some of the core areas of BI Applications – HRMS, Financials and Procurement, time permitting.

I think in the 21st century, that same principal will gain general acceptance on the value of pre-built analytics for ERP. Put quite simply, in the cut-throat economy companies can’t wait ten years to become a Kung Fu Dragon Master of Business Intelligence with Oracle ERP.

Companies need to achieve that in 9-12 weeks (vanilla implementation) and the only way to do that is to rely on the Kung Fu Dragon Masters from Oracle Corporation that have already done this for you…………before your competitors beat you to it……..

Related Articles

Below are a number of articles. Some are your average Oracle Sales pitches, but all the same very informative overall. Others are written by generous authors whom I acknowledge and extend my thanks to for writing very interesting and useful articles, which were used as background research during writing this article. (I particularly enjoyed the articles by Jeff McQuigg and Mark Rittman, both renowned experts in their field and excellent authors. Their articles are highly recommended reading).

 For those lucky enough to have been to the Oracle Open World 2010 show (hope you enjoyed the Black Eyed Peas !!! Turn down the volume before you click that link……), the following can be accessed from Oracle Open World On Demand.

(Note there were many more sessions, but many do not yet have the content uploaded at the time of writing – 2nd October 2010. I have chosen to hyperlink only a few of my favorite session links. Others are available in Oracle Open World On Demand).

  • End-to-End Oracle Business Intelligence: From Warehouse to Advanced Analytics
  • Enterprise Sales Analytics with Oracle Business Intelligence Enterprise Edition
  • Oracle BI Applications Roadmap, Including Support for Oracle Fusion Applications
  • Oracle Business Intelligence Enterprise Edition Integration at Comcast
  • Oracle Human Resources/Oracle Financial Analytics for ResCare Decision-Making
  • What’s New in Oracle Business Intelligence Suite, Enterprise Edition Plus 11g
  • Gain the Insight You Need with Oracle Business Intelligence Applications
  • Implementation Experiences with Oracle Business Intelligence Applications
  • Implementation Experiences with Oracle BI Applications
  • Oracle Transactional Business Intelligence (OTBI)
  • Haemonetics Corporation: Better Decisions with Oracle Human Resources Analytics
  • Get Real-Time and Actionable Information with New Reporting and Analytics

R12 Patching and the Art of Zen

September 18, 2010

Reading through Wikipedia, I found an interesting article on the concepts of Zen. Now I’m not really into that type of stuff myself (each to their own), but I thought it would make an original way to present this article 🙂

“One practice of Zen Buddhist’s is Koan Inquiry. A koan is a question, or statement, the meaning of which cannot be understood by rational thinking but may be accessible through intuition. The answer can occur during meditation or during your typical daily life with all the mundane tasks you do.

To Zen Buddhist’s the Koan is “the place and the time and the event where truth reveals itself”. It is a way to induce an experience of enlightenment or realization, not through rational reasoning, but through intuition.

Answering a Koan requires a student to let go of conceptual thinking and of the logical way we order the world, so that like creativity in art, the appropriate insight and response arises naturally and spontaneously in the mind.”

Or to quote from a very non-Zen perspective, you think about a problem very hard all day. You fail to make any breakthrough. During the next morning, in the shower, without even thinking of the problem, you suddenly think of the idea. Ironically perhaps we are all practicing Koan Inquiry as a natural state of mind to solve difficult problems, without even having to think about the problem at hand.

Now let’s look at ERP Patching in relation to Zen 🙂 We need to be clear from the outset that this form of Zen Patching applies only to the following patches below. This is extremely important to keep in mind.

Security Patches

ATG Patches

Database Patches

This form of Zen does not apply to other ERP Patches

Applying this form of Zen Patching to any other types of patches will cause you some serious grief in your career when you report to your boss that your ERP for your entire organization worldwide is trashed because you read some amazing article by some “new age ERP guy called the Oracle Prophet” on a radical new method using the Art of Zen for ERP Patching and thought it was worth a try on your Production System……….

Do note that this form of Zen Patching does work on both R11 and R12, but not on R10. It also works on 9i, 10G and 11G databases. Please check Oracle Certification matrices and raise the question to Oracle Support if in doubt.

Oracle Support – Good morning. Can I help you?

Reader – Yes could you tell me if the Art of Zen patching is certified against R12 Apps please?

If the phone goes dead at this point, we suggest you assume Oracle Support is not aware of the Art of Zen patching and you should not pursue your question with them……….We also suggest you give your colleagues name during any telephone calls with Oracle,  in case Oracle raises a complaint for nuisance phone calls to your company…..:-)

So where does the Art of Zen fit into Oracle ERP patching?

 Let’s use a typical a koan to provide an illustration.

“We will test the patch by not testing the patch. Only then will we know that the patch has worked.”

Now at this point in time, you are probably thinking I’ve been hitting some fairly strong stuff to get to this state of mind, or I’ve completely lost the plot.

I can hear everyone thinking “So let me get this straight. You are going to test the patch by not testing the patch, so that you know the patch is working”. To which I’d reply, great you’ve got it. You are certainly a quick learner on this Zen Patching stuff!!! 🙂

Our R12 Patching Philosophy actually made our auditors jaws drop, not in terms of the Zen stuff (trust me, keep this stuff between yourself and myself please and maybe better not mention to your management or auditors……), but on the thoroughness of approach.

We always have five databases for our patching (at a minimum). This is probably a lot more than most have but let me explain why and you’ll probably want to then copy this model.

Our DBA Environment. This is where the patches are applied to make sure, well they actually apply. Believe it or not some patches from Oracle don’t even apply cleanly.

Our Patch Environment. This is where they are applied with a little bit of testing. OK we deviate a little from the Zen stuff, but give me a break……This makes sure they at least do what they say on the box without major functional failure.

Our development environment, which is always busy with daily activity by our development team , functional team and testers.

Our test environment which is always busy with daily activity by our users.

Our Production environment.  I’ve been pushing our company to drop this as it uses a lot of space and we hear most of our complaints from this database, but management insist it is important and needed. 🙂

We should also state our databases are pretty heavily used so application flows naturally are being used throughout development and test databases. We also apply any patches onto any other instances we have at that point in time, so that the patch is naturally tested by the simple day to day activity in as many places as possible, with a careful rollout to each environment.


The Art of Zen Patching

The point is simple on these types of patches. Oracle does release patches that should be applied at some point.

The Security Patches typically come quarterly and we try to apply 3-6 months after they come out. Security patches represent a serious risk to apply, although generally apply well. However security patches also represent a risk if you do not apply. You need to find the balance, but you SHOULD apply these regularly.

The ATG Patches are less frequent but provide critical updates to Browsers (especially if you have DMZ applications) and other technology components, including diagnostics.

The Database patches (and we’re talking 10G to 11G for instance) do come out periodically and at some point you need to decide to keep at least supported, although we’re very picky on applying these, but are in the process of an 11G Upgrade. (Various 10G database versions are losing or have lost premier support). This activity is every couple of years or so.

We’re not talking about applying every patch. No company in their right mind can achieve this. We’re talking about keeping your head above water and staying supportable.

The point is this. To test every time on these types of patches across every last item is impossible. The conventional way is to get the patch, apply, test everything and then move to Production in a few weeks. That’s a very logical way to order the world of Oracle ERP. But unfortunately this is not a very practical or safe way. These patches are by their very nature too broad and silently hit too many areas to be open to a logical, standardized testing process. The conventional approach actually increases risk with these types of patches because it is by no means obvious what could be impacted.

There must be a better way where you find that balance. This is the Art of R12 Zen Patching.

Our philosophy is simple. We plan carefully on all these types of patches well ahead of applying.

We do not apply these patches immediately they come out. We are kind enough to give others the opportunity to be the heroes or unenlightened who find the bugs, log them in My Oracle Support and make our life so much simpler because we heavily research each patch to find the problems the unenlightened logged. This way we avoid the bulk of the problems. Are you one of the unenlightened? If so we appreciate you finding the bugs for us, causing issues for your users on Production systems and generally making our life so much easier and less stressful.

Our philosophy then rests correctly on a peace of mind that these patches are largely stable, largely trusted and tested by others around the world. This isn’t just a philosophy, it’s backed up by hard facts based on an incredibly low failure rate of patches we have applied. The patch types listed are generally very mature, very stable and very reliable. The quality of these types of patches is far higher than the Oracle ERP patches for the application modules.

Our key philosophy can be defined by the koan below. (As you raise a smile, remember this is used in leadership teaching by guys that make more in a month than you make in 5 years and sell books by the truckload at Amazon 🙂

“Once upon a time in ancient Japan, a young man was studying martial arts under a famous teacher. Every day the young man would practice in a courtyard along with the other students. One day, as the master watched, he could see that the other students were consistently interfering with the young man’s technique. Sensing the student’s frustration, the master approached the student and tapped him on the shoulder. “What is wrong?” inquired the teacher. “I cannot execute my technique and I do not understand why,” replied the student. “This is because you do not understand harmony. Please follow me,” said the master. Leaving the practice hall, the master and student walked a short distance into the woods until they came upon a stream. After standing silently beside the streambed for a few minutes, the master spoke. “Look at the water,” he instructed. “It does not slam into the rocks and stop out of frustration, but instead flows around them and continues down the stream. Become like the water and you will understand harmony.” Soon, the student learned to move and flow like the stream, and none of the other students could keep him from executing his techniques” – Timothy H. Warneka

Now I’m not into all this stuff and I’m as skeptical as anyone else, but maybe they have a point. Too many companies are simply slamming into the rocks with patches, rather than working with the flow of Oracle Corporation. Working with Oracle, you often feel that you are not talking of a stream but more a raging torrent of patches. The problem is you are always fighting against the flood of patches, rather than finding what these guys would refer to as “harmony”.

The very essence of our philosophy and the koan itself can now be answered 🙂

“We will test the patch by not testing the patch. Only then will we know that the patch has worked.”

After rolling the patch through DBA and Patch environments very carefully over many weeks, we are ready to proceed to our main development and testing environments.

We typically roll patches into our development environment for a minimum of 4 weeks. We observe the behavior of the environment and record any bugs. We carefully investigate all bug occurrences.

Once we are comfortable at that point, we do run testing. OK so we broke our mantra, but nowhere near the testing that would normally be required. Why? Because we have seen the bugs naturally arise through our normal daily activity (as a Project Manager you’ll know typically what is going on and where the gaps may be I would hope). So to quote the Art of Zen,” the appropriate insight and response arises naturally.” This is the beauty of the Art of Zen Patching 🙂 You do your daily stuff to get to the answer of whether the patch causes major grief.

At this point we normally release the patches to our Test Instance, again allowing patches to settle for 4-8 weeks. Again using normal user activity, we gain further appropriate insight and responses, in terms of stability of the patch and subsequent bugs, arising from the natural process of user activity.

We do ask our users to test and hit the key functionality, but again, with the insight given from normal daily use, we have achieved ” the appropriate insight and response which arises naturally” as a Zen Master or Leadership or Lifestyle coach would tell you for quite a lot of cash 🙂

Even our DBA Team reaches a relaxed Zen like state and if you know your average DBA guys……… With planning comes time for our DBA Team to work and document carefully the steps needed for each patch. The timeframes create space for many, many practice runs, so that on the day of application to Production, they know exactly what to do and what to expect. It also creates the space and time for good old fashioned research on My Oracle Support. (A tip is that as we’re doing this approach over a number of months, the DBA’s always get copies of Production on a regular basis to run through the patching process, so any production specific issues are always encountered early).

In addition, we carefully plan for releases. So if you take our last security patches, these were rolled into an ATG RUP6 and a minor database point release (to stay supported). This reduces a constant patch cycle to a more manageable ITIL Release concept, reducing your workload overall. The raging torrent of patches becomes a much more manageable stream.

Most companies have huge stress over these types of patches. Most companies don’t even bother applying, much to the detriment in terms of support, future upgrades and security.

We are like every other company in many respects. We are highly conservative on applying patches. We like to stick on what we know.

But we do pay attention and plan for security, de-support of databases, new browser support in ATG RUP’s, etc in a very careful manner well ahead of time, allowing us to practice the Art of Zen Patching 🙂

Companies stress out, rush patches and therefore make mistakes. That is not the Art of Zen Patching. Zen Patching stresses the very opposite approach. Put the patch into your environments, slowly observe and watch over many months, then test and finally you will see the appropriate insight and responses that it has worked. Now looking at Wiki again, Monks meditate over many months or even years to answer a Koan. There is no difference in the approach of Zen Patching 🙂 The time the patches spend in your environments can be thought of as your meditation period over many months (typically 3-6 months depending on risk assessment) to find the answer to the koan of “how to test the patch without testing the patch to know the patch has worked”.

But with our R12 Zen Patching, we’ve reached an almost Zen like state 🙂 Patches are simply a natural part of the lifecycle of ERP. We have accepted that. They are planned and are allowed to settle for several months, to give insight into their nature and risk. Patches still require testing, but to a far lesser extent than a fully focused, high risk “lets test this patch and apply this patch in two weeks time”, which is similar to slamming into the rocks in the stream.

So where are we today with such an approach?

R12.0.4 RUP5 (yes this was a nightmare of testing the old fashioned way, definitely slamming into the rocks in the stream, but our go-live was incredibly smooth. Zen Patching doesn’t work here I’m afraid for those embarking on an R12 Upgrade. It’s the good old fashioned conventional testing approach that is needed here).

Security Patches to April 2010


11G and July Security Patches are currently under the Zen method as is a SUSE Linux Upgrade

We have never had a failure or serious outage as a result of Zen Patching (should I trademark this perhaps and make a lot of cash like those leadership guys??????), although as with all Oracle patches, it is a very serious business, with serious risks, so there is no place for complacency.

So what about the opinions of others on our ERP and how up to date we are overall with patches?  To quote one Senior Consultant DBA recently, we are way ahead of most companies in terms of patching, and have an “aggressive patching policy”.

I would say that the Art of Zen Patching can never be described with words like “aggressive” 🙂 . In fact it is quite the opposite. It is a very slow and considered process, stressing great patience, over many months, waiting for insight as a part of a natural process to reduce the risks we face, as the real Zen guys would put it 🙂

We simply achieve a lot more than most companies, with a lot less effort and a lot less risk. I think the Zen guys and leadership/lifestyle gurus would term it as “simply learning to move and flow with the Oracle stream creating harmony and peace of mind”. Obviously they’d be charging a thousand US Bucks an hour for this type of advice. (I remember we had one such IT Guru in our company. Cost us 6 figures for six weeks of work – we ended up using his laminated b*llshit as coffee mats……that was about the only value we got……). Now maybe the Consultant our company hired wasn’t too hot except for the coffee mats, but with some of these leadership and other philosophies, well maybe there’s something in it after all……

Call it Zen. Call it Lifestyle (or is it Patch) Management :-), but to have a safe, low risk and stress free approach to this type of patching which works with reduced effort (rather than increased effort) is  not a bad place to be, as an ERP Manager…….

Health Warning

This article was designed to present a very serious subject in a hopefully entertaining and educational manner utilizing both conventional approaches of testing, in conjunction with a more unconventional approach. However applying any patches on a Production Database is a very serious business. Patches do need testing and this should never be underestimated. However the point of this article is that by allowing patches to settle into various instances over time, you vastly increase the chances of spotting serious issues and vastly reduce the risk of issues in production that conventional testing can never address. This is the safest way to apply such patches that I have found, using a conventional testing approach, together with a far less conventional approach. 21st Century Testing meets 5th Century Zen Philosophy.


(By the way, I haven’t been smoking anything….the intention of this article is to present a very serious subject – Oracle Patching – in hopefully what some will find a very funny and original manner, that can then be remembered and applied to help all of us that face the very serious risks of patching global ERP Systems, so don’ take all the Zen references for Patching too seriously………otherwise someone may think you’ve been smoking something………). Think Monty Python British humour as you read it……..

If you remember the article, change patching from a rush to a planned perspective and patch carefully over a period of months, then the article did it’s job 🙂

I hope you find it as funny to read as I did writing it 🙂

Looks like 12.1.3 is Imminent

August 20, 2010

With the rapidly approaching of Oracle Open World 2010, it looks like 12.1.3 is also rapidly approaching.

The Release Notes for 12.1.3 are now out:

12.1.3 Release Documents

Having a quick look through probably the most interesting change is in the Applications Technology area.

It’s a shame that as far as I can see (correct me if I’m wrong) but they haven’t put BIEE dashboards directly into any ERP screens yet (with half the screen in OAF Framework and the other half embedded BIEE), despite enabling the technology in the previous 12.1.2 Release. For me that is a little disappointing, but hopefully by R12.1.4 we’ll see that.

It is now possible to link to Oracle Application Development Framework (ADF) Release 11g applications deployed on an Oracle Fusion Middleware Release 11g container from the Oracle E-Business Suite home page. Each ADF application will have an FND Function of type ‘ADF’ defined for it. If such a function is granted to a user, then when the user logs in, the home page automatically builds a link to the external ADF application.”

This is an interesting note on ADF Integration to ERP. I’m left wondering if clients can finally move from OAF to ADF and still have the integration to ERP. The new functionality sounds as if it will allow you to call cleanly ADF Applications from the Oracle ERP homepage. It’ll be interesting to see this as it could provide those who do go to 12.1.3 another good staging point to Fusion in the future that pre 12.1.3 sites don’t have. I’ve talked to a number of people in Oracle over the last couple of years on when to start using ADF instead of OAF, but the message has always been to use OAF. Obviously this left me wondering for the future on “will I need to convert all these OAF applications in 3 or 4 years as I move to Fusion Apps”, so not an ideal situation. Maybe over the coming months the OAF/ADF will become clearer.

On the Finance, Procurement and HRMS there is the usual small changes here and there, additional changes for different countries, but nothing overall that for my organization is really ground-breaking. 12.1.1 and 12.1.2 had a lot more functionality that we would be interested in overall.

A short reminder that demo Vision instances are available and a whole lot more from the excellent Solution Beacon site, allowing you to test drive the new features for those with a CSI Number from Oracle, before you take the plunge. Currently up to 12.1.2, as of the time of this article (20th August 2010).

R12.1.x Vision Instances

Hopefully we’ll all start to see the new mythical Fusion Apps over the coming weeks.

Footnote: A short footnote courtesy of Steven Chan’s excellent blog that 12.1.3 is now available. See the latest at:

  • Oracle E-Business Suite Release 12.1.3 Release Update Pack (Patch 9239090)
  • Oracle E-Business Suite Release 12.1.3 Readme (Note 1080973.1)
  • Oracle Open World 2009

    October 17, 2009

    Just got back from Oracle Open World 2009 and it was a fantastic show. Points of interest for me?

    1. Most of the sessions started off with the question “How many companies are on R12, please raise your hands”. What really surprised me was how few companies were actually on R12. Now I assume that the ones that go to Open World are the more zealous of Oracle companies and therefore you’d expect a higher uptake of R12. Now I have no idea what the official figures are but I’d say no more than 5% of all people (and we are talking hundreds at each session) raised there hands. Most when asked were still on 11.5.x………..Which means that 2010 may well be a boom year for upgrades to R12.

    2. Oracle Business Intelligence and Pre-built Analytics. I’ve loved this product since the day I saw it. Basically it was acquired from Siebel and it’s the best Oracle product I have seen in the last decade, even if it ain’t really Oracle’s……..This is really the unstated direction for Discoverer in the future and I think this product (BIEE) will really gain very substantial market share in the coming years. What they also have is pre-built analytics (entire data warehouse, dashboards, KPI’s, reports, etc) for most of the Oracle ERP. The quality and coverage of these is again superb with new products and more coverage being released on a very frequent basis. What really gets you about these products is the time it takes to implement – Oracle came on site to us and put in the entire technology, software, database, etc and extracted our data and got the dashboards running – IN SEVEN HOURS as a demo. If you heavily use ERP this is something everyone should be looking at. It’s not cheap, but then building your data warehouse from scratch usually ends in disaster………BI Analytics is built by professionals at Oracle and it shows. Absolutely stunning product that can allow management to keep a real eye on how business is doing and provides powerful reporting and analytics across the business.

    3. GRC – This is another acquired product and again looks very interesting. Basically this allows you to keep track of all setup changes (although unfortunately not across all modules). Now when the auditors come in and ask to show what has been changed, nice and easy – just run a report. Or if one module stops working after changes, then if you’ve created a snapshot you can see the difference, ending hours or days of minute researching to find the problem……Or compare a UAT to a newly upgraded R12 to check you’ve got identical setup within minutes……what a great tool.

    Now this has other parts to it as well including access controls, enforcing segregation of duties,  adding rules, workflow and analytics (in BIEE separate license called GRCI) and other areas. Now apparently the Consulting company reckoned they could have this up and running in two months……

    4. Another one I loved was the Imaging and Process Management. Basically we want to streamline Payables by imaging all invoices and workflowing them to the users. Oracle seems to be heading towards creating modules that provide services ERP wide and this is one of them. Basically this can do imaging, workflow, etc and has prebuilt adapters for Accounts Payable. Text Recognition is also coming out in the coming quarters, so it’s a very exciting product that could streamline not just AP operations, but many other areas as well.

    5. When wandering around the demo grounds saw a Product called Oracle Application Testing Suite. Now this looks like the Mercury tools and it’s a great addition to Oracle’s suite. Basically it will allow you to build automated test scripts for Oracle ERP. They reckon they will also be adding pre-built test scripts, but no commitment on that. So basically you apply your patch, run the Test Suite, go get a coffee and come back and see what failed. Now my one doubt is always how long it takes to actually build the test suite – it’s not easy and I have seen a lot of companies fail. However if you can actually build a robust test suite, this could save a huge amount of time and money in the future. A very interesting product.

    6. IFRS – with most companies heading towards IFRS it sounds like Oracle is already prepared. Indeed some companies are already using IFRS with Oracle. It was hard to stay awake as a bunch of accountants talked about the exciting features of IFRS, but as someone in the IT area, its good to know that Oracle should have it covered (although there will still be a chunk of work, possibly changes to your existing applications and certainly changes in the reporting sphere, so don’t assume that Oracle has it totally covered – it provides the foundations only).

    7. Another interesting product was the Oracle Application Change Management Pack. Basically this allows you to build your own patches for customizations and deploy. Very nice product that should ease DBA workload and make applying customizations much more reliable and safe.

    With 1800 sessions, it was hard to do anything except scrape the surface. But as an average company using ERP,  hope these areas will be as interesting to you as they were to me.

    R12 – A Detailed Transition Plan

    September 19, 2009

    So your UAT is heading for completion, you’ve got as many bugs out as you can and your ready for go-live.

    Problem is…..this is the most dangerous part of the entire project. All the hard work and success could go be wrecked over a poorly planned weekend (or long weekend if your upgrade is really tricky or big)

    Planning is the key.

    It’s recommended that everything is documented very, very heavily. Start off with a PM.030 and get into your head exactly what your going to do.  And a PMP.030 Transition Plan isn’t just what you will do over the cutover weekend; its before, during and after – how are you going to support the R12 when live? Are your teams ready for that?

    Get all your Functional Setup into a Spreadsheet, BR.100 per module or other installation documents. Give assignments to each person. Make it clear who does what, when they do it, order, dependencies, etc. On a cutover weekend there is ZERO time for mistakes.

    Have a spreadsheet of all tasks to countdown, dates, people, etc. Mark them off one by one as you go.

    Have another spreadsheet for all cutover tasks. I suggest some sort of matrix where setup is assigned and you can see each persons workload. Then try to balance that workload. If one person is too slow or too busy on the cutover, you’ll hit major delays on cutover weekend.

    Your source code should be in a Source Control System (we used Subversion – it’s FREE – it’s GREAT). You should repeatedly clone Production and Test the deployment until very close to Production. Also apply Functional Setup and retest.

    Are you confident that what was passed in UAT is what you’ll put in Production?

    Keep doing the clone, setup and test until your happy that the answer is 99.9% yes.

    Also during final approach to go-live, do a simulated financial close on the Clone. Trust me this will save you a lot of heartache. Wouldn’t you rather find disastrous stuff you need to do before you hit it in the crazy, high pressured time after go-live. Pay careful attention to AP closing. It’s tough. Run the accounting health checks.

    You must have an automated mechanism for code application and your DBA’s should be familiar with this. And by automated it can be as simple as a shell script to move stuff to the right place and a script to cut and paste and compile objects. Get this 100% before you do it.

    One tactic that worked well for us was to do setup on 11.5.10. We’re not saying everything, but some menus, some value sets, messages…..every little helps. Of course you can also do these on the day using FNDLOAD, but the point is the more you can do ahead, the less you have to do in a high pressured weekend.

    Get the meetings going with Business. Make sure everyone is aware of how and what is getting done. We closed on R11.5.10 for all subledgers prior to R12 apart from GL. This really helped reduce overall errors and risk, as all that had to be done in week one of R12 was a GL close. It went very smoothly. And then you have 4 weeks until your first real R12 close – gives you time to simulate another close 3 weeks into your financial period. Now some may disagree with this approach and prefer to go-live after month end closing. I understand this is another perfectly valid option. In our case we made the decision to jump immediately after subledger closing as we also run two large payrolls twice a month…….so our cutover windows for making the jumps were seriously limited. Also we had done this procedure many, many times (including on very recent clones), so had a level of confidence that this was safe to do. If your cutover testing isn’t giving you that confidence, suggest you close in R11 fully then jump to R12.

    Make sure you have plenty of meetings with your DBA Team, Unix Team and Management. I prefer to plan for the worst; set the expectations low and then everyone will be prepared mentally for a tough time. If it goes smooth then everyone is happy. Doing an R12 is the toughest upgrade anyone will have ever done, don’t underestimate the issues you will have. Many, many companies have had horror stories during go-live.

    So your plans are done, teams are briefed and your now ready for launch. Did you actually remember in advance to tell all users (and post notices on your website internal and external) that your system is unavailable due to upgrade? Did you remember to tell all the other systems (Legacy, data warehouse, etc) that your system is unavailable?

    Oracle people (including myself think the world is Oracle). Remember there’s more to life than Oracle…..and most companies have a lot more systems than just Oracle, although Oracle is the foundation… if you take that away and forget to announce, you’ll have some very unhappy people……

    Make sure you brief your helpdesk. Make sure they know the escalation procedures. Even if you do a great job, it’s going to be a busy time. Make sure you Oracle ERP Team is also ready to work round the clock post go-live, just in case…….

    We brought our ERP down at 5:00PM on a Wednesday after closing all subledgers. Actually it was 5:52PM and the DBA’s were already complaining like crazy.

    The DBA’s in our case brought the system down and started the export. They worked through the night to export from IBM and bring this into HP Linux. (Yes, we throw in not only an upgrade but also a migration for fun…..)

    The Upgrade started on the Thursday and was then backed up early Friday morning.

    The DBA’s automatically deployed the code (7,000+ files) onto what was effectively a live Production system. (with no one else on and of course backed up on R12 AND R11.5.10  before we started).

    The Deployment Team rolled in at 10AM on Friday, sat around for a few hours and then started at 11:45AM to do setup. Of course we screamed at the DBA’s for being 1hour and 45 minutes late, but they claimed we were 52 minutes late handing the system over on Wednesday…….things are always tense during an upgrade cutover.

    Functional Setup continued until 9PM on Friday night (remember all those new modules, Payments is a large setup, and there are loads of System Admin tasks  – Hint split some setup into Application Developer responsibility. Do others setup using FND_LOADER. I think your System Admin may be a bottle neck – that’s what happened with us as there were just so many changes between R11.5.10 and R12)

    Around 9:30PM Friday the database was ready to be backed up and cloned. DBA’s worked to clone the database and make it available at 10:00AM Saturday.

    Testing Team rolled in on Saturday and did a quick Financial Reconciliation. All well and good testing started on major key functionality. I hope in your cutover plan you detailed what would be tested, who would test, etc.  In addition each Team Lead should prepare a more detailed Test Plan for the transition and brief their respective teams. Concentrate on key functionality. If you do this, you’ll be doing well if it all works on day 1. Testing continued for about 10 hours on Saturday and started at 9:00AM Sunday.

    As we caught problems, we logged them and got our System Admins/DBA to deploy missing items to Production (with heavily controlled approval, logging of bugs, etc so we tracked every single change)

    Around about 4:00PM on Sunday we had a Team Lead meeting. Each Lead was asked for a go/no go signal. With the decision to go made, we went to Management. The current status was reported and management after discussion gave the green light.

    All workflow services were turned on (with emails being caught in our email system so we could monitor and then release – nice little trick). Once we were happy emails/workflow was OK, we released one (a Leave from one of my staff) and tried the complete cycle. Once we were happy with this, we released all workflow, all concurrent jobs (we had simulated release of all concurrent jobs on Saturday to avoid surprises) and there was no going back.

    We went live on R12.0.4 (RUP5) at 5:00PM on 2nd August 2009, some 96 hours after bringing down the R11.5.10. (Although we are on RUP5 we’re really heavily patched on Financials so RUP5 is a bit misleading)

    Teams had worked round the clock, across all areas to make the cutover a huge success – we finished 30 minutes ahead of planned time. Now that’s pretty good planning. Have you planned to that level? If not you should be.

    On 3rd August, our users logged on to R12 and we were fully operational.

    R12 Upgrade Plan

    April 9, 2009

    1. The most important part prior to starting an R12 Upgrade is to build support for the project. If the Business isn’t on board, it’s going to hit real problems when you want their time – and it’ll take a lot of their time……Look for the key drivers of why your business needs an upgrade; for us we had many new Oracle modules and we felt it was better to do those projects on R12. rather than implement in 11.5.10 and upgrade in R12 later. A move to Linux and the age of 11.5.10 were other critical factors. With R12 being out for 2.5 years by the time we implement we figured it would be relatively stable overall – a lot of companies got badly burned by jumping way too early into R12.

    For those companies not in R12 now is a good time to start thinking – the product is stable in most areas (even AP is getting there) and the market is depressed, so there are a lot of available resources (albeit mainly in 11.5.10, but most modules remain pretty similar overall)

    2. A study approach of building a Business Case to assess the Upgrade may be a good idea. It won’t cost a lot, but will allow you to make a very informed choice ; the new functionality, the customizations you have, the estimate of effort, time and cash it going to take, pitfalls and other issues.

    This approach lets you build concensus and support. It minimizes risk and allows you to build a strong business case to get necessary funding.

    For us, we identified several major issues upfront; Payables was a complete re-implementation for us. Payments was a complete re-implementation. We had a LOT of customizations. General Ledger customizations require re-work; small but 60% of our customizations were hit…..Self Service screens must be migrated. All Payment formats require new build. This was going to be the biggest upgrade I have seen in a very long time…..Understand also the architecture – Oracle ERP isn’t an island. It usually talks to an awful lot of other systems and if you don’t understand this at the outset it may hit you just before go-live or worse……after.

    We also used a lot of good documentation on R12. Look on Metalink, google, OAUG. People have made plenty of mistakes and published where they went wrong – with the documents available now you should avoid the pitfalls for most.

    Remember this is not just an upgrade – Oracle has brand new modules so this is also a major implementation project; Payments is new and completely different, EBiz Tax is new, Subledger Accounting is new and if you are running AP , you have no choice but to implement the Payments. The other two are now core modules that must be implemented, even if your not using Tax………

    Be careful of your plans for current Production during the Upgrade. Most of your resources will sink into R12, so try and reduce big new commitments.

    3. Starting the project. Make sure you have enough hardware……you’ll need to run a lot of instances AND still support your existing Production on 11.5.10…….If you don’t have the hardware your project will suffer badly. Resourcing wise there are lots of options, but it will take a lot more than an upgrade from 11.5.5 to 11.5.10. I can’t stress enough how important it is to get top tier consultants – a few really good people (albeit expensive) actually saves you money. Depending on your customizations, you’ll need a fair chunk of development resources. But by doing the study you’ll be able to make good estimates. (Don’t underestimate Payables and Payments – these will take a VERY LONG TIME).

    Remember that resourcing won’t just be Oracle people; you will need people from the feeder systems involved (Legacy Teams, Data Warehouse Teams – the stuff you change may create work for them), the DBA Team, the Unix Team, the Email Team, the ERP System Admins, potentially people from external (such as Citibank). Most of all , your going to need your users.

    4. So your ready to start. Once your over the ubiquitous kickoff meetings (which shouldn’t have any surprises as you did all the planning, lobbying, studies in advance right :-)? ), its time to get the team going.

    Obviously an R12 ERP would be helpful. Make sure that you have the hardware, know how to upgrade a copy of Production and have it available BEFORE you have your Upgrade army. It’s a lot of cash to burn through otherwise….If you’ve got a good DBA Team they should be able to get you an instance in 2-4 Week timeframe.

    If you have good internal resources, then this really helps. Otherwise external consultant will need to take a bit of time to understand how you use Oracle ERP – sure most companies work in roughly the same way, but every company has its difference; some are very different indeed.

    At this point I normally split off into streams; I split into modules, team leads across each module (we have 31 standard and custom modules, spanning HRMS, Procurement and Finance).

    The Functional Team should be preparing an initial high level Test Plan, working closely with Business. In parallel they should use their knowledge (and the R12 Upgrade Guides) to identify the relevant changes to the company. The Test Plan should be comprehensive and signed off once complete. This should cover all Test Scenarios across all Business Areas, including external systems. The Functional Team should also try to identify, in conjunction with the Business and Development Team, customizatons that can be removed with new functionality provided as standard. (We removed 10 check formats in a single morning – imagine the saving when you need to rewrite these, do Technical MD.070, Functional MD.050, Testing, etc. A Little time doing this could bring significant savings).

    The Development Team should again be split across Modules. If you have a customization register (and we have all 1,600 customizations registered so we don’t miss any) you can use this as a very good monitoring tool. Working closely with Functional Team, identify the core stuff – there is no point in working on a report first, if you can’t enter the invoice to test the report. Once you prioritize the customizations (and possibly modules if you have limited resources), start working to upgrade these, document and test.

    Now based on R12 experience, from a Technical Perspective, AP and Payments are very difficult. GL customizations had 60% small updates required. Self Service needed a re-implementation to move from MOD*PLSQL to standard Java pages. Custom modules were largely unaffacted and OAF upgrades with minimal overall issues.

    If the Developers update the Customization Register with flags saying Unit Tested, Fixed, Require Fix, it becomes an excellent monitoring tool providing exact progress on your project. Ensure that you use source control. There are cheap, cheerful and excellent solutions including Subversion. This is a great tool.

    Once the Test Plan is signed off, Consultants and QA’s should start ensuring that comprehensive Test Scripts are available. As they are being written, the application should be tested initially to throw up the bugs. The earlier you find them the earlier you get them to Oracle Support to get fixes. R12 does have quite a chunk of bugs, especially in Payables. Keeping a close eye on Metalink is important – when new RUP’s are released, you have a very important decision to make on whether to apply. If your well into your project, the advice I would give is not to apply….they’ll cause a lot of issues, it’s newly released and you’ll be the poor guys finding all the new bugs. Leave that to someone else. Obviously if you have critical errors, pile the pressure on to Oracle to provide single patches. If you are early in the project stage, then apply the RUP’s, but do it on a Patch System and check it first…..they can cause real damage. At some point though your going to have to freeze patches, otherwise you will never finish.

    Whilst all this is going on your DBA team should be working on practise upgrades and optimization. It will take many upgrades to get it right.

    5. So you have your Test Plan, Test Scripts and Customizations. We run what we call an Internal UAT. Basically its a UAT but it is carried out by the R12 Upgrade Team, not the user. Try and get an exact copy of Production, upgrade fully, deploy the objects and do the setup. (Be careful to carry out pre and post upgrade steps)

    Given that some modules are going to be delivered before others, you may want to do this staged. It’s not ideal but it does compress the overall project time. You will have problems on Payables, and should that hold up your HRMS track? I would argue no.

    The point of the Internal UAT is to run through the entire test, as the users would do. This includes external systems such as Legacy, your planning systems, Citibank, or whatever. All bugs should be recorded. (We use a tool called JIRA which is fantastic)

    Be particularly careful to look at the upgrade process itself. You should simulate a close on your R11.5.10, reconcile on 11.5.10, then do the upgrade then reconcile again on R12 and make sure the R11.5.10 and R12 add up. This is critical and we found what we think is a major issue on accruals when we did this.

    6. Another clone of Production should be taken. All objects, customizations, setup should be done on the UAT environment (If you have not been keeping deployment registers, BR.100 would strongly suggest you start doing so……) Usual UAT kickoff meetings should be held and contact lists given to users. Your team will need to work hand in hand with the Business Users to co-ordinate the UAT. It’s going to be a lot of work, a lot of new and unfamiliar screens and quite a few complaints, no matter how well you’ve done till now. The Test Plans and Test Scripts should have been agreed months ago, with full review and signoff.

    One trick here is to put the Test Plan on a shared drive and ask the user to mark Pass/Fail/To Test as they progress. As an Upgrade Manager you can then review and summarize easily progress across the entire ERP Suite. Now that is sweet…….Again delivering some modules into UAT before others may be an option if you want to progress quicker. Others prefer to deliver the entire UAT. What I have found is that by progressing some modules quicker, it gives the project momentum and when you put out weekly progress tables to users, they don’t want to be last to finish.

    Make sure all Test Scripts are tested. Make sure Month End and Year End are similuted, with proper reconciliations. Make sure all the systems to and from Oracle ERP are tested and continue to work with R12. Make sure integration between modules is smooth and working – organizing this across departments is tricky. A UAT room for this can be handy…..providing cookies and drinks (non alcoholic please…..don’t want people testing when they are drunk….) creates a good ambience.

    Record all issues and bugs as you go – again a tool like JIRA is great. Monitor closely and ensure that when your users are doing UAT, your R12 Team are there to assist. Some areas may need twice weekly meetings on certain modules. This will help to manage difficult UAT areas, stop user frustration and make the UAT a little less painful for all. We did this on Payables in particular.

    On completion, ask the head of each area and other users to sign off a standard AIM Acceptance certificate.

    7. Whilst the UAT was going on I hope you were also planning to do something else right? As UAT goes on, you should be planning what will be a very compex and difficult cutover. The team should also be doing rehearsals by taking copies of production, upgrading, deploying, setting up and testing. All bugs should be recorded.

    Don’t underestimate the deployment.

    You should have deployment registers for each module, including tasks for DBA, System Admin, etc. You should also have a big thick document on the steps the DBA’s do in the actual upgrade. Separate deployment registers with hyperlinks to relevant setup docs, BR.100, etc.

    Review the deployment registers and figure out what can be done in advance of R12. We transferred new programs, menus, value sets, etc to R11.5.10 without switching on certain components. All of this saves a little bit of time and will ease the stress of the upgrade weekend.

    Ensure the BR.100 (or other setup instructions) are clear, concise and complete.

    Call meetings with the users and make sure that the cutover (and possibly closing processes) are clear.  We are doing the closing in R11.5.10 just before cutover, closing down all transactions, etc. Why? Couple of reasons. If data is left open in R11 (say unaccounted) it will give real problems in R12. (We worked for months to get the script from Oracle and luckily we found this prior to go-live). Secondly it allows you a whole month before your first closing in R12. You can do a couple of simulations of Production and sort out issues if needed. The users will lose Production for a few business days, unless you do it on a holiday……..its a big upgrade. Our’s runs for 42 hours.

    Get a register of who needs what responsibility on the day and how many setups they need to do. Set these up in advance so you know who needs what access and are not scrambling on the day. It also lets you balance the workload. We have almost 600 setups. A lot in Payments.

    Deployment of code. If you have used Subversion (or another Source control system) then you can grab all changes and give to the DBA’s. Make sure your directories match Oracle’s and Subversion as that will make deployment easier. A lot can be automated and this will save a lot of time on the cutover. Now with all the prep work the DBA’s started over the previous months maybe this is something they could also have been working on…..There are a lot of standard Loaders that can save lots of time.

    Talk to Oracle and make them aware of your key dates, cutover, month end, payroll, etc. They can get you critical support management over that time.

    Try and do as many rehearsals as you can whilst users are doing UAT. The closer the rehearsal is to an exact recent copy of Production the better.

    Make sure you do patch reconciliations between your UAT and Rehearsal databases.

    Hold a few internal meetings to ensure that everyone in IT knows exactly their role. Watch out for people booking holidays in advance. One key person out could derail your go-live.

    As PM, you should be writing a PM.030 Transition and Continegency Plan that covers all the angles.

    If the cutover fails all your hardwork will have gone to waste.  Rehearse, Rehearse, Rehearse……

    8. Cutover

    Generally over a four day window. Holidays are ideal but make sure you tell Business well in advance if their key system is going to be down during business hours. We plan to be down by 5:00PM on a Wednesday and up by 7:00AM Monday. We also have a migration to Linux thrown in. Wednesday, Thursday are days for the actual Upgrade, Friday the Upgrade Team roll in to do setup, customizations, etc. Saturday and Sunday are for testing with a go-live decision on Sunday evening. As we are moving server, our contingency is simple; if the cutover is a no-go we simply turn our old server back on……Don’t repeat the errors you made in rehearsals, keep a log of everything that failed and make sure it doesn’t happen on your go-live, as you simply won’t have time to troubleshoot.

    Be very careful that all transactions are accounted, workflows complete (as much as possible), payments complete. Review the Best Practises from Oracle for more detailed advice. If you don’t do this you will hit serious problems.

    9. Post Go-Live. There are going to be problems on anything of this scale. Thats a given. Have all teams on standby so that when issues arise, they can be addressed. With Oracle Critical Support you’ll hopefully be in better shape than with standard support. As soon as possible clone production to a test system and simulate the payroll, closing and other key events in advance.

    10. Party? If you do an R12 Upgrade, you deserve a big party. R12 is the most challenging upgrade you will ever have done in Oracle ERP. Good Luck !!!

    R12 Upgrade – Top Ten Pitfalls

    March 13, 2009

    If you were doing an Oracle R12 Upgrade, what Top Ten would you watch out for?

    1. An R12 is a lot bigger than R11.5.x to R11.5.10. It will take considerably more time and money

    2. Watch out for Oracle Payables; its had some major structural changes that have affected the stability overall. In 12.0.4 major functionality works but the accounting can still cause issues. It’s a very hard module to get through a UAT.

    3. Watch out Payments; its completely new. Accounts Payable Payments functionality is now covered by this. It’s entirely in OA Framework and very, very different. All the formats will need to be redone. Basically a re-implementation so needs a lot of time and extensive user training.

    4. Any customizations in Mod*PLSQL? If so, none of these will work. They will need to be upgraded to OA Framework

    5. Watch out for new modules; E*Biz Tax and Subledger Accounting. These need to be configured and tested.

    6. If you have customizations on General Ledger, 66% are probably going to need some re-work as Sets of Books heads to Ledgers. Minor work, but hits most customizations, custom value sets, etc.

    7. Major areas such as Purchasing, HRMS, General Ledger, Payroll, Accounts Receivable are generally stable in 12.0.4. This is one you don’t need to worry quite so much on, so it’s not all bad news…….

    8. In 11.5.5 to 11.5.10, 8% of our customizations failed. From 11.5.10 (CU1) to 12.0.4, 24% failed. Again give more time for the upgrade of customizations. There are some fairly large structural changes especially in Accounts Payable and General Ledger.

    9. Banks – very different and there appears to still be a bug that creates duplicate banks that give an error in 12.0.4. Be prepared to spend time cleaning up your Bank Accounts. It all moves into TCA. New Rules means the Bank Number becomes mandatory for certain countries.

    10. Give plenty of time to your UAT. It will take considerably longer.

    So what’s your Top Ten Pitfalls?