Tuesday, 19 December 2023

Query To Get Form Personalizations Details in Oracle APPS R12

/*SQL query to get Oracle Form Personalization Details*/


SELECT fcr.SEQUENCE                                   rule_seq,
       ffv.form_name,
       user_form_name,
       fcr.function_name,
       fff.user_function_name,
       fcr.description                                per_description,
       trigger_event,
       trigger_object,
       fcr.condition,
       fcr.enabled,
       (SELECT user_name
        FROM   fnd_user fu
        WHERE  fu.user_id = fcr.created_by)           created_by,
       Decode (fcr.rule_type, 'F', 'Form',
                              'A', 'Function')        personalize_rule_level,
       Decode (fcs.level_id, 10, 'Industry',
                             20, 'Site',
                             30, 'Responsibility',
                             40, 'User')              context_level,
       Decode (fcs.level_id, 10, '',
                             20, '',
                             30, frt.responsibility_name,
                             40, fu.user_name)        context_level_value,
       ca.SEQUENCE                                    action_seq,
       Decode (ca.action_type, 'P', 'Property',
                               'B', 'Builtin',
                               'M', 'Message',
                               ca.action_type)        ACTION_TYPE,
       Decode (ca.message_type, 'W', 'Warn',
                                'E', 'Error',
                                'S', 'Show',
                                ca.message_type)      message_type,
       ca.message_text,
       fnd_load_util.Owner_name (fcr.last_updated_by) apps_owner
FROM   fnd_form_custom_rules fcr,
       fnd_form_custom_scopes fcs,
       fnd_form_functions_vl fff,
       fnd_responsibility_tl frt,
       fnd_user fu,
       fnd_form_vl ffv,
       fnd_form_custom_actions ca
WHERE  fcs.rule_id = fcr.id
       AND fcr.function_name = fff.function_name
       AND fcs.level_value = frt.responsibility_id(+)
       AND fcs.level_value = fu.user_id(+)
       AND ffv.form_name = fcr.form_name
       AND fcr.id = ca.rule_id
--and fcr.ID= 1448
--and ffv.form_name = 'OEXOEORD' --Sales Orders
ORDER  BY ffv.form_name; 

No comments:

Post a Comment