/*Form personalisations Query in Oracle APPS R12*/
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,
ca.object_type action_object_type,
CASE
WHEN ca.action_type = 'P' THEN ca.target_object
WHEN ca.action_type = 'M' THEN
CASE
WHEN ca.message_type = 'S' THEN 'Show'
WHEN ca.message_type = 'H' THEN 'Hint'
WHEN ca.message_type = 'E' THEN 'Error'
WHEN ca.message_type = 'D' THEN 'Debug'
WHEN ca.message_type = 'W' THEN 'Warn'
ELSE ca.message_type
END
WHEN ca.action_type = 'B' THEN
CASE
WHEN ca.builtin_type = 'U' THEN 'Launch URL'
WHEN ca.builtin_type = 'C' THEN 'Launch SRS Form'
WHEN ca.builtin_type = 'E' THEN 'Launch a Function'
WHEN ca.builtin_type = 'D' THEN 'Do Key'
WHEN ca.builtin_type = 'P' THEN 'Exceute a Procedure'
WHEN ca.builtin_type = 'G' THEN 'Go Item'
WHEN ca.builtin_type = 'B' THEN 'Go Block'
WHEN ca.builtin_type = 'F' THEN 'Forms DDL'
WHEN ca.builtin_type = 'R' THEN 'Raise Forms Trigger Failure'
WHEN ca.builtin_type = 'T' THEN 'Execute Trigger'
WHEN ca.builtin_type = 'S' THEN 'Synchronize'
ELSE ca.builtin_type
END
WHEN ca.action_type = 'S' THEN ca.menu_entry
END action_target_object,
CASE
WHEN ca.action_type = 'P' THEN cpl.property_name
WHEN ca.action_type = 'M' THEN ca.message_text
WHEN ca.action_type = 'B' THEN ca.builtin_arguments
WHEN ca.action_type = 'S' THEN ca.menu_label
END action_property_name,
CASE
WHEN ca.action_type = 'P' THEN
CASE
WHEN ca.property_value = '4' THEN 'True'
WHEN ca.property_value = '5' THEN 'False'
ELSE ca.property_value
END
WHEN ca.action_type = 'B' THEN ca.menu_argument_short
WHEN ca.action_type = 'S' THEN ca.menu_argument_short
END action_value,
Decode (ca.message_type, 'W', 'Warn',
'E', 'Error',
'S', 'Show',
ca.message_type) message_type,
ca.message_text,--frt.language, frt.SOURCE_LANG
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,
applsys.fnd_form_custom_prop_list cpl
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 ca.property_name = cpl.property_id(+)
AND ca.object_type = cpl.field_type(+)
--and frt.language= USERENV ('LANG')
--and frt.SOURCE_LANG= USERENV ('LANG')
AND ffv.form_name = 'OEXOEORD' --Sales Orders
ORDER BY ffv.form_name,
fcr.SEQUENCE;
