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%';
Oracle Apps Information
Monday, August 29, 2022
SQL Query to identify all personalizations on a Form
Subscribe to:
Posts (Atom)