Monday, August 29, 2022

SQL Query to identify all personalizations on a Form


 SELECT 
       *       
 FROM
 (
SELECT   fpt.application_name, ff.form_name source_form_name,
         fft.user_form_name, fft.description form_description,
          ffcr.SEQUENCE personalize_rule_sequence,
         ffcr.description personalize_rule_description,
         --ffcr.last_update_date,
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function'
                ) personalize_rule_level,
         ffcr.enabled personalize_rule_enabled,
         ffcr.last_update_date,
         ffcr.trigger_event personalize_rule_event, ffcr.trigger_object,
         ffcr.condition personalize_rule_condition,
         DECODE (ffcs.level_id,
                 10, 'Industry',
                 20, 'Site',
                 30, 'Responsibility',
                 40, 'User'
                ) context_level,
         DECODE (ffcs.level_id,
                 10, '',
                 20, '',
                 30, frt.responsibility_name,
                 40, fu.user_name
                ) context_level_value,
         ffca.SEQUENCE action_sequence,
         DECODE (ffca.action_type,
                 'P', 'Property',
                 'M', 'Message',
                 'B', 'Built in',
                 'S', 'Menu',
                 ''
                ) action_type,
         ffca.summary action_description, ffca.enabled action_enabled,
         DECODE (ffca.action_type,
                 'P', ffca.object_type,
                 NULL
                ) action_object_type,
         DECODE (ffca.action_type,
                 'P', ffca.target_object,
                 NULL
                ) action_target_object,
         DECODE (ffca.action_type,
                 'P', ffcpl.property_name,
                 NULL
                ) action_property_name,
         DECODE (ffca.action_type,
                 'P', DECODE (ffca.argument_type,
                              'B', DECODE (ffca.property_value,
                                           5, 'False',
                                           4, 'True'
                                          ),
                              ffca.property_value
                             ),
                 NULL
                ) action_property_value,
         DECODE (ffca.action_type,
                 'M', DECODE (ffca.MESSAGE_TYPE,
                              'S', 'Show',
                              'E', 'Error',
                              'W', 'Warning',
                              'H', 'Hint',
                              'D', 'Debug'
                             ),
                 NULL
                ) action_message_type,
         DECODE (ffca.action_type,
                 'M', ffca.MESSAGE_TEXT,
                 NULL
                ) action_message_text,
         DECODE (ffca.action_type,
                 'B', ffca.builtin_type,
                 NULL
                ) action_builtin_type,
         DECODE (ffca.action_type,
                 'B', ffca.builtin_arguments,
                 NULL
                ) action_builtin_arguments,
         DECODE (ffca.action_type,
                 'B', ffca.menu_argument_long,
                 NULL
                ) action_builtin_parameters,
         DECODE (ffca.action_type,
                 'B', ffca.menu_argument_short,
                 NULL
                ) action_builtin_code,
         DECODE (ffca.action_type,
                 'S', ffca.menu_entry,
                 NULL
                ) action_menu_entry,
         DECODE (ffca.action_type,
                 'S', ffca.menu_label,
                 NULL
                ) action_menu_label,
         DECODE (ffca.action_type,
                 'S', ffca.menu_seperator,
                 NULL
                ) action_menu_seperator,
         DECODE (ffca.action_type,
                 'S', ffca.menu_enabled_in,
                 NULL
                ) action_menu_blocks_enabled,
         DECODE (ffca.action_type,
                 'S', ffca.menu_argument_short,
                 NULL
                ) action_menu_icon_name
    FROM apps.fnd_application fp,
         apps.fnd_application_tl fpt,
         apps.fnd_form ff,
         apps.fnd_form_tl fft,
         apps.fnd_form_functions fff,
         apps.fnd_form_functions_tl ffft,
         apps.fnd_form_custom_rules ffcr,
         apps.fnd_form_custom_scopes ffcs,
         apps.fnd_responsibility_tl frt,
         apps.fnd_user fu,
         apps.fnd_form_custom_actions ffca,
         apps.fnd_form_custom_prop_list ffcpl
   WHERE fp.application_id = fpt.application_id
     AND fpt.LANGUAGE = 'US'
     AND fpt.application_id = ff.application_id
     AND ff.form_id = fft.form_id
     AND fft.LANGUAGE = 'US'
     AND ff.form_id = fff.form_id
     AND fff.function_id = ffft.function_id
     AND ffft.LANGUAGE = 'US'
     AND ff.form_name = ffcr.form_name
     AND ffcr.function_name = fff.function_name
     AND ffcr.ID = ffcs.rule_id
     AND ffcs.level_value = frt.responsibility_id(+)
     AND frt.LANGUAGE(+) = 'US'
     AND ffcs.level_value = fu.user_id(+)
     AND ffcr.ID = ffca.rule_id
     AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =
                                                             ffcpl.property_id
     AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =
                                                              ffcpl.field_type
     AND ffcr.trigger_event LIKE '%QUERY%'
     --and ff.form_name='OEXOEORD'
ORDER BY fft.application_id,
         ff.form_name,
         ffcr.SEQUENCE,
         ffcs.level_id,
         ffcs.level_value,
         ffca.SEQUENCE
)
WHERE 1 = 1
--AND action_type = 'Property'
--AND personalize_rule_enabled = 'Y'
--AND personalize_rule_event LIKE '%QUERY%'
--AND ACTION_TYPE = 'Builtin'
--AND personalize_rule_sequence IN ('55')
--AND personalize_rule_description LIKE '%CPQ%Punch%'
  AND personalize_rule_condition LIKE '%SUMMARY%';
--AND action_message_text LIKE '%You%allowed%'
--AND action_message_type = 'Show'
--AND personalize_rule_sequence IN ('55','54') 
ORDER BY last_update_date DESC;
--action_property_name LIKE '%DEFAULT_WHERE%';