The following query extracts the information from the setup on the E-Business Suite and receives has parameter the USER_FUNCTION_NAME it returns the resposibilities related to that user function name.
/*
get Responsibilities associated to Funtions
Only the funtions not Excluded
*/
WITH DAT
AS (SELECT 'FND_FORM_FUNCTIONS-' || fff.function_id ID,
FUNCTION_NAME,
USER_FUNCTION_NAME,
DESCRIPTION,
'FUNCTION' DATA_TYPE,
APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) ASOCIATED_function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_FORM_FUNCTIONS-' || fff.function_id EXC
FROM FND_FORM_FUNCTIONS_VL fff
UNION ALL
SELECT 'FND_MENUS-' || MENU_ID,
MENU_NAME,
USER_MENU_NAME,
DESCRIPTION,
'MENU' DATA_TYPE,
NULL APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_MENUS-' || MENU_ID
FROM fnd_menus_VL fm
UNION ALL
SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID,
RESPONSIBILITY_KEY,
RESPONSIBILITY_NAME,
DESCRIPTION,
'RESPONSIBILITY',
APPLICATION_ID,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CAST (NULL AS VARCHAR2 (480)) function_name,
CAST (NULL AS NUMBER) function_application_id,
'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID
FROM fnd_responsibility_vl
UNION ALL
SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE,
CAST (ENTRY_SEQUENCE AS VARCHAR2 (4000)),
PROMPT,
ent.DESCRIPTION,
'MENU ITEM ' || NVL2 (fun.TYPE, '-' || fun.TYPE, '-SUBMENU'),
NULL APPLICATION_ID,
CAST (NULL AS DATE) START_DATE,
CAST (NULL AS DATE) END_DATE,
ent.CREATION_DATE,
ent.CREATED_BY,
ent.LAST_UPDATED_BY,
ent.LAST_UPDATE_DATE,
FUN.function_name,
FUN.APPLICATION_ID function_application_id,
'FND_FORM_FUNCTIONS-' || fun.function_id EXC
FROM FND_MENU_ENTRIES_VL ent, FND_FORM_FUNCTIONS_VL fun
WHERE 1 = 1
AND PROMPT IS NOT NULL
AND NVL (ent.GRANT_FLAG, 'Y') = 'Y'
AND (ent.function_id IS NOT NULL OR SUB_MENU_ID IS NOT NULL)
AND ent.function_id = fun.function_id(+)
),
REL
AS (SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_MENUS-' || MENU_ID CH FROM FND_RESPONSIBILITY
UNION ALL
SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_REQUEST_GROUPS-' || REQUEST_GROUP_ID CH
FROM FND_RESPONSIBILITY
UNION ALL
SELECT NULL PAR, 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID CH
FROM FND_RESPONSIBILITY
UNION ALL
SELECT 'FND_MENUS-' || MENU_ID PAR, 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE CH FROM FND_MENU_ENTRIES
UNION ALL
SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE par,
NVL2 (SUB_MENU_ID, 'FND_MENUS-' || SUB_MENU_ID, 'FND_FORM_FUNCTIONS-' || function_id)
FROM FND_MENU_ENTRIES
)
,
FN AS (
SELECT 'FND_RESPONSIBILITY-'||RESPONSIBILITY_ID|| '-' || APPLICATION_ID ID, DECODE (RULE_TYPE, 'F', 'FND_FORM_FUNCTIONS-' || ACTION_ID, 'M', 'FND_MENUS-' || ACTION_ID) RESP_ID
FROM FND_RESP_FUNCTIONS
)
SELECT tree.FUNCTION_NAME RESP_KEY,
tree.USER_FUNCTION_NAME RESP_DESCRIPTION,
tree.RESP_NAME function_name,
tree.RESP_DESCRIPTION USER_FUNCTION_NAME,
tree.NAVIGATION
FROM (SELECT CONNECT_BY_ROOT function_name RESP_NAME,
CONNECT_BY_ROOT USER_FUNCTION_NAME RESP_DESCRIPTION,
CONNECT_BY_ROOT DAT.APPLICATION_ID RESP_APPLICATION_ID,
CONNECT_BY_ROOT DAT.ID RESP_ID,
CONNECT_BY_ROOT dat.DATA_TYPE RESP_DATA_TYPE,
SYS_CONNECT_BY_PATH (function_name || ':' || USER_FUNCTION_NAME, '>') tree,
SYS_CONNECT_BY_PATH (DECODE (data_type,'RESPONSIBILITY',CAST (NULL AS VARCHAR2 (80)),function_name || ':' || USER_FUNCTION_NAME), '>') tree_NO_RESP,
SYS_CONNECT_BY_PATH (LPAD (FUNCTION_NAME, 20, '0'), '>') ORD_FUN,
SYS_CONNECT_BY_PATH (CAST (DECODE (data_type, 'MENU', CAST (NULL AS VARCHAR2 (80)), USER_FUNCTION_NAME || '-') AS VARCHAR2 (80)), '>')
NAVIGATION,
DAT.*,
cr.user_name CREATED_BY_NAME,
upd.user_name LAST_UPDATED_BY_NAME,
REL.par,
rel.ch
FROM DAT,
REL,
fnd_user cr,
fnd_user upd
WHERE dat.ID = rel.ch
AND dat.created_by = cr.user_id(+)
AND dat.last_updated_by = upd.user_id(+)
CONNECT BY NOCYCLE PRIOR par = ch
START WITH (
USER_FUNCTION_NAME = :P_NAME-->>NAME OF THE FUNCTION HERE
)
)
tree ,FN
WHERE DATA_TYPE='RESPONSIBILITY'
AND RESP_DATA_TYPE='FUNCTION'
AND tree.RESP_ID=fn.RESP_ID(+)
AND tree.ID=fn.id(+)
and fn.id is null;
