Oracle EBS R12: Extract Personalizations via SQL

The purpose of this post is to provide you an easy way out for extracting all the personalizations that you have configured on your EBS instance. The following query can be used to extract the aforementioned:

Note: The query is designed in such a way that you ONLY need to provide the APPLICATION_ID of the forms that you require the personalizations of. You may amend it in any way as per your liking (restrict it to a function level maybe?)

select fat.APPLICATION_NAME,
       fffv.USER_FUNCTION_NAME,
       ffcr.SEQUENCE,
       ffcr.DESCRIPTION,
       ffcr.TRIGGER_EVENT,
       ffcr.TRIGGER_OBJECT,
       ffcr.CONDITION,
       ffcr.ENABLED,
       ffca.SEQUENCE Line_seq,
       decode(ffca.ACTION_TYPE, 'P', 'Property', 
              decode(ffca.ACTION_TYPE, 'M', 'Message', 
                     decode(ffca.ACTION_TYPE, 'S', 'Menu', 
                            decode(ffca.ACTION_TYPE, 'B', 'Builtin', 'FK' )))) Line_type,
       ffca.OBJECT_TYPE,
       ffca.TARGET_OBJECT,
       ffcp.PROPERTY_NAME,
       ffca.PROPERTY_VALUE,
       decode(ffca.BUILTIN_TYPE, 'G', 'GO_ITEM',
              decode(ffca.BUILTIN_TYPE, 'P', 'EXECUTE A PROCEDURE',
                     decode(ffca.BUILTIN_TYPE, 'D', 'DO_KEY',
                            decode(ffca.BUILTIN_TYPE, 'B', 'GO_BLOCK',
                                   decode(ffca.BUILTIN_TYPE, 'Q', 'CREATE RECORD GROUP FROM A QUERY',
                                          decode(ffca.BUILTIN_TYPE, 'E', 'LAUNCH A FUNCTION', ffca.BUILTIN_TYPE,
                                                 decode(ffca.BUILTIN_TYPE, 'R', 'RAISE FORM_TRIGGER_FAILURE', ffca.BUILTIN_TYPE,
                                                        decode(ffca.BUILTIN_TYPE, 'C', 'Launch SRS Form', ffca.BUILTIN_TYPE,
                                                               decode(ffca.BUILTIN_TYPE, 'U', 'Launch a URL', ffca.BUILTIN_TYPE,
                                                                      decode(ffca.BUILTIN_TYPE, 'F', 'FORMS_DLL', ffca.BUILTIN_TYPE,
                                                                             decode(ffca.BUILTIN_TYPE, 'T', 'EXECUTE_TRIGGER', ffca.BUILTIN_TYPE,
                                                                                    decode(ffca.BUILTIN_TYPE, 'S', 'SYNCHRONIZE', ffca.BUILTIN_TYPE,
                                                                                           decode(ffca.BUILTIN_TYPE, 'L', 'Call Custom Library', ffca.BUILTIN_TYPE,
                                                                                                  decode(ffca.BUILTIN_TYPE, 'X', 'Set Profile Value in Cache', ffca.BUILTIN_TYPE)))))))))))))) BUILTIN_TYPE,
       ffca.BUILTIN_ARGUMENTS,
       ffca.MENU_ARGUMENT_SHORT,
       ffca.MENU_ARGUMENT_LONG,
       decode(ffca.MESSAGE_TYPE, 'S', 'Show',
              decode(ffca.MESSAGE_TYPE, 'H', 'Hint',
                     decode(ffca.MESSAGE_TYPE, 'E', 'Error',
                            decode(ffca.MESSAGE_TYPE, 'D', 'Debug',
                                   decode(ffca.MESSAGE_TYPE, 'W', 'Warning', ffca.MESSAGE_TYPE))))) MESSAGE_TYPE,
       ffca.MESSAGE_TEXT,
       ffca.MENU_ENTRY,
       ffca.MENU_LABEL                      
from  FND_FORM_CUSTOM_RULES ffcr,
      FND_FORM_CUSTOM_ACTIONS ffca,
      FND_FORM_CUSTOM_PROP_LIST ffcp,
      FND_FORM_FUNCTIONS fff,
      FND_FORM_FUNCTIONS_TL fffv,
      FND_APPLICATION_TL fat
where 1=1
and ffcr.ID = ffca.RULE_ID
and ffca.PROPERTY_NAME = ffcp.PROPERTY_ID (+)
and ffca.OBJECT_TYPE = ffcp.FIELD_TYPE (+)
and ffcr.FUNCTION_NAME = fff.FUNCTION_NAME
and fff.FUNCTION_ID = fffv.FUNCTION_ID
and fff.APPLICATION_ID =  fat.APPLICATION_ID
and fat.APPLICATION_ID in ( 
                            201, ---Purchasing---
                            200, ---Payables---
                            401, ---Inventory---
                            250, ---Quality---
                            140, ---Assets---
                            660, ---Order Management---
                            880, ---Receivables---
                            800  ---Human Resources---
                            )
order by fat.APPLICATION_NAME,
         ffcr.FUNCTION_NAME, 
         ffcr.SEQUENCE,         
         ffca.SEQUENCE

And there you have it. This shall extract all your required personalizations onto a single page.

Added Bonus (Optional): Go on reading below for an additional 2 minute guide to present the extracted data from the query above on a very presentable and professional Excel format.

Phase 2 (Optional) – The Presentation:

You can download a the aforementioned excel template from the following link:

Download Personalizations Excel Template

Just copy the extracted data (from the query) AS-IS and paste it into the excel template. It has been made as per the structure of the query output.

Template

The template has been conditionally formatted to properly present the 4 different types of personalization lines:

  1. Property
  2. Menu
  3. Message
  4. Builtin

Now, lets paste the extracted data on to the excel template:

Template2

You may notice some contradicting data in this sheet. For eg. You will notice that for a Builtin type personalization, the query has returned values for Property type fields too. That’s just how the data is stored in the personalization relevant tables. Lets call this contradictory data “Dirt”.

I have placed a dirt validation in the last column of the sheet, to show you if the line contains any “Dirt”:

Template3.jpg

The FALSE indicator means that this line contains “Dirt”. Be smart. Use the color filters. Remove the “Dirt”.

Now, on to the final step to complete the perfect presentation. This excel template contains a Macro to merge all the similar values in a single column, because lets be honest, you could really do without all the repeating values in every row as follows:

Template

Navigate to the sheet named Merge Macro and copy the syntax:

Template

Press ALT+F11. The following will appear, Navigate to “Module” of your relevant worksheet as follows:

Template

Paste the copied Macro and Save:

Template.jpg

Navigate back to the Personalizations sheet, select the fields (within one column) you would like to merge, and press ALT+F8:

Template

Press Run and all the highlighted cells will be Merged.

Template

There you go. All your personalizations on an Excel file. Very professionally presented.

Needless to say, you may use this Macro for many other purposes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s