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.
The template has been conditionally formatted to properly present the 4 different types of personalization lines:
Now, lets paste the extracted data on to the excel template:
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”:
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:
Navigate to the sheet named Merge Macro and copy the syntax:
Press ALT+F11. The following will appear, Navigate to “Module” of your relevant worksheet as follows:
Paste the copied Macro and Save:
Navigate back to the Personalizations sheet, select the fields (within one column) you would like to merge, and press ALT+F8:
Press Run and all the highlighted cells will be Merged.
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.