SQL Query - Form Personalisation

Below is the list of tables that are populated when any Forms Personalisation is done

• FND_FORM_CUSTOM_RULES
• FND_FORM_CUSTOM_SCOPES
• FND_FORM_CUSTOM_ACTIONS
• FND_FORM_CUSTOM_PARAMS
• FND_FORM_CUSTOM_PROP_VALUES
• FND_FORM_CUSTOM_PROP_LIST

We can use FNDLOAD utility to move personalisations from one instance to another.

The below script list all the forms in Oracle Applications that have been customized using Forms Personlisation:

select distinct a.form_name , a.enabled, c.USER_FORM_NAME, d.APPLICATION_NAME 
from   FND_FORM_CUSTOM_RULES a,
          FND_FORM b,
          FND_FORM_TL c,
          FND_APPLICATION_TL d
where enabled = 'Y'
and a.form_name = b.form_name
and b.form_id = c.form_id
and b.application_id = d.application_id
order by application_name

The below query can be used to fetch details of form personalization

select  

appl.application_name,

ff.user_form_name,

ffcr.form_name,

ffcr.function_name,

ffcr.description,

decode (ffcr.rule_type, 'A', 'Function', 'Form') Personalisation_Level,

ffcr.sequence,

(ffcr.ENABLED)Pers_Enabled_Flag,

ffcr.FIRE_IN_ENTER_QUERY,

ffcr.trigger_event,

ffcr.trigger_object,

ffcr.condition,

(ffca.sequence)action_seq,

ffca.action_type,

ffca.builtin_type,

ffca.builtin_arguments,

ffca.object_type,

ffca.target_object,

ffcp.property_name,

ffca.argument_type,

ffca.property_value,

ffca.message_type,

ffca.menu_argument_short

from apps.fnd_form_custom_rules ffcr,

apps.fnd_form_custom_actions ffca,

apps.fnd_form_functions_vl fff,

apps.fnd_application_vl appl,

apps.fnd_form_vl ff,

apps.fnd_form_custom_prop_list ffcp

where       ffcr.id = ffca.rule_id

and ffca.property_name = ffcp.property_id

and ffca.object_type = ffcp.field_type

and ffcr.form_name = ff.form_name

and ffcr.function_name = fff.function_name

and fff.application_id = appl.application_id

and application_name in ('Human Resources','Payroll') --- enter the application name for which the data needs to be extracted

order by   fff.user_function_name, ffcr.sequence, action_seq

PREVIOUS POST || NEXT POST