Patching and Customisations in Oracle E-Business Suite Release 12

The ability to easily customise the standard functionality in Oracle eBusiness Suite to suit individual business requirements is one of the product’s more appealing features.

But what happens to that customisation when you need to apply a patch to fix a bug, or as part of general proactive maintenance, part of an upgrade project, or to obtain the latest legislative or tax changes?

Oracle have made a number of significant improvements in eBusiness Suite so that customisations can be automatically preserved through upgrades or patches (for example through Personalisations) but sometimes the only way to make the system work in a way that a business demands is through a direct change to one of the standard Oracle files or database objects (“customisation by modification”). In that case there is always the risk that the customisation will be lost if a patch comes along that delivers a new version of the file which overwrites the customised version; or perhaps the customised version was taken as a copy of the standard object (“customisation by extension”) – in that case the customisation will not be lost, but it will be based on an older, potentially obsolete, version of the standard Oracle code that will not contain any new bug fixes delivered by the new version.

What we really need is to have a tool which will tell us before we apply a patch whether any of the files that we have directly customised or copied and changed will be updated; that way we will know before testing of the patch starts whether any re-development work is required

In Release 12, Oracle Applications Manager has features which – after a bit of initial setup – can do just that

In 11i, customisations could be recorded in a file called applcust.txt and adpatch could be run in “preinstall” mode to assess whether any of these files were going to be overwritten, but this functionality is deprecated in Release 12 and no longer works

This article will detail the steps required to implement this functionality in R12, with some ‘real-world’ hints and tops along the way!

Identify customisations

This is probably the hardest part of the process.

In an ideal world, there will be documentation in place on any piece of standard Oracle code which has been customised, including the name and path of the file and details of what the customisation actually is.

In practice, however, there will be some customisations which are not documented – perhaps the project team that delivered the changes are no longer in the picture, or an emergency fix was rushed through, or a new supplier is running the system as a managed service and the previous incumbent did not provide these details. In this case DBAs, developers and business users will need to work together to try to identify what areas of standard code have been amended, or copied and changed. There may be some which fall through the cracks and which only come to light at a later point, so this may be an iterative process.

With a lack of documentation, this can be particularly difficult for those files which have been customised by modification, since the file names and version numbers will be identical to those delivered by Oracle as standard and the only way to identify such customisations is through ‘business memory’ (a user remembers a change being made at some point in the past) or an onerous process of looking for developer comments in database objects.

It is also important that after the initial analysis is completed, that any future development code work is flagged up so that it can be included in the list of customised code.

Any purely bespoke code does not need to be included in this process – any custom code developed from scratch will by definition not be impacted by a standard Oracle eBusiness Suite patch; all we need to know about are the Oracle-supplied files which have been directly changed, or which have been copied and enhanced

Sometimes it is possible to say that a particular object has been changed, but it is not always obvious what physical file on the $APPL_TOP installs that object, and we need to know this file name for the next stage.

• For a Report or a Form, it’s usually pretty easy – the name of the file will contain the short name of the product, RDF files are always held in <$PRODUCT_TOP>/reports/<language> and FMB/PLL files are always held in $AU_TOP
  For example, I if have customised Report PAXTRTRX.rdf and I am using default American/America as a language, then the full file name is $PA_TOP/reports/US/PAXTRTRX.rdf
• For database packages, procedures and functions can be a little more complicated, but usually the definition of the object in the database will include the file header and the source file will be found in <PRODUCT_TOP>/patch/115/admin/sql
  For example, if I have customised PL/SQL package POR_CUSTOM_PKG then the full file name is $POR_TOP/patch/115/sql/PORCUSTB.pls
• For tables, views, triggers and sequences, the definition of the object in the database will usually not include the file header, and the name of file which installs the object will often bear no relation to the actual object name. In this case, the only way to find the file is to go looking for it – the object name will contain the short name of the product and the installation file will normally be included in <PRODUCT_TOP>/patch/115/admin/odf.

For example: I have customised view PA_COMMITMENTS_V but need to find the file that installs this object
1) Go to $PA_TOP
2) Run command find . -type f -exec grep -il “PA_COMMITMENTS_V” {} \;
3) This tells me that the file that installs this view is $PA_TOP/patch/115/odf/pav1342.odf

• For Workflows, the source files are in <$PRODUCT_TOP>/115/import/<language>
  For example, I have customised the “AP Invoice Approval” but need to find the file that installs this Workflow

1)    Go to $AP_TOP
2)    Run command find . -type f -exec grep -il ” AP Invoice Approval” {} \;
3)    This tells me that the file that installs this Workflow is $AP_TOP/patch/115/import/US/apfhanwf.wft

Register Customisations

We should now have a list of all of the standard Oracle files that have been modified (or at least a list based on best endeavours should documentation be lacking!)
The next step is to make the eBusiness Suite application aware of this list – this is done with the Register Flagged Files function in OAM

The following detailed steps are based on a 12.1.3 environment

1. Logon to eBusiness Suite with the System Administrator responsibility
2. Navigate to Oracle Applications Manager > Workflow
3. Click Site Map
4. Click the Maintenance tab
5. Click the link for Register Flagged Files
6. Click Add
7. In the filter condition, change to File Name
8. Search for the name of the standard Oracle object which has been customised and click Go e.g. in this example

• If a file is listed in multiple paths, always choose the one in the patch directory since this is the one that a patch will always update
• If a file is listed in a language sub-directory (e.g. US), always choose that one rather than the one a level above, since this is the one that a patch will always update. For a multi-language environment, choose all files which have been customised for each territory
• If a file is listed as an apparent duplicate (same file name, same path) then select *both* (this is because in the AD_FILES table there will be one row for the product name in lower-case and another with the product name in upper-case and both are needed for the next stage to work correctly!)

Example when searching for PAAPINVW.wft; in this case we would choose the second record

9. Select the checkbox next to the customised file
Click Apply

10. Enter some meaningful details in the comment section – such as the package name, Workflow name and the customer reference for the customisation

11. Repeat for all customised files

Patch Analysis

Once all customised code has been registered in the application, you can then perform an analysis of any future patches to see if it will deliver a new version of that file
This step requires no downtime

1. Logon to eBusiness Suite with the System Administrator responsibility
2. Navigate to Oracle Applications Manager > Workflow
3. Click Setup in the top-right
4. Choose My Oracle Support Credentials in the top-left
5. Verify that the My Oracle Support login details are correct
6. Click Site Map
7. Click the Maintenance tab
8. Click Patch Wizard
9. Click the Tasks button on the line for Patch Wizard Preferences
10. Specify a patch stage location on the server
11. Click the Tasks button for Recommend/Analyze Patches
12. Select the Analyse Specific Patches button and paste in the patch number
13. Click Ok and OK
14. The Patch Wizard will then download the patch to the designated staging area given above if not already present and then analyse it. If there is no internet access on the server, then the patch can be downloaded manually from My Oracle Support and placed in the expected directory
    a. Applications DBA (AD) patches should be copied to <STAGE_TOP>/ad 
    b. Patches for any other product should be copied to <STAGE_TOP>/nonad
15. Progress can be monitored by clicking on the Job Status button in the Recommend/Analyze Patches line
16. It will start as Running/Paused while it runs the child requests of the job and will eventually go to Completed. The larger the patch, the longer this process will take but generally only takes a few minutes
17. To view the results when complete go to the main Patch Wizard page and click the Details button on the appropriate patch line at the bottom of the page e.g.
18. Then click the Impact button
19. To see what customised files the patch will overwrite click the number next to the Flagged Filed Changed line

Now we know what customised standard code the patch will impact, and we can line up developers to see what to do about it – whether the customisation should be re-applied on top of the new version of the standard code, or perhaps the customisation is now obsolete if new functionality delivered by the patch supersedes it

But the point is that we can make sure that the customisation is ‘fixed’ as a post-patch step before handing the system over to the users for testing and proactively avoids a defect being raised as a result of the patch being applied which can negatively impact business confidence in that patch

References

Some useful Oracle Notes on My Oracle Support and on the web:

• Patching Best Practices And Reducing Downtime (Doc ID 225165.1)
• Oracle E-Business Suite Patching FAQ for Release 11i and Release 12 (Doc ID 1325930.1)
• Oracle Applications Patching Procedures – http://docs.oracle.com/cd/B34956_01/current/acrobat/oa_patching_r12.pdf
• Adpatch Doesn’t Report Customized Seeded Oracle Files In R12 (Doc ID 1532347.1)
• R12 : Register Flagged Files tool , Autopatch Does Not Review Ad_files Table To Check Customizations, Why ? .Is this “applcust.txt” still be used in R12? (Doc ID 1330615.1)
• Flagged File are not recognized by Patch Wizard (Doc ID 1616392.1)
• Patchwizard – Analyze Specific Patches Fails In 12.1.3 (Doc ID 1352605.1)