To use them first modify with variables pertaining to env, test them
and use at own risk.
1. To extract custom responsibilities
select *
from fnd_responsibility_tl
where (upper(RESPONSIBILITY_NAME) like 'XX%' )
order by responsibility_name;
========================
2. To extract all the valid combinations of app short and responsibility names:
SELECT DISTINCT
APPLICATION_SHORT_NAME,
RESPONSIBILITY_NAME
FROM FND_RESPONSIBILITY_TL, FND_APPLICATION
WHERE FND_APPLICATION.APPLICATION_ID =
FND_RESPONSIBILITY_TL.APPLICATION_ID and
(upper(RESPONSIBILITY_NAME) like 'XX%' );
========================
select u.user_name,
a.application_name,
r.responsibility_name,
r.responsibility_id,
wu.end_date
from apps.fnd_user u,
apps.fnd_responsibility_tl r,
apps.fnd_application_tl a,
apps.wf_local_roles ur,
apps.wf_user_role_assignments wu
where u.user_name=wu.user_name and
wu.role_name=ur.name and
ur.display_name=r.responsibility_name and
a.application_id=r.application_id and
(wu.end_date is null or wu.end_date = sysdate) and
a.language='US'
order by u.user_name, r.responsibility_name;
SELECT DISTINCT
B.RESPONSIBILITY_NAME
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL
OR A.end_date >= sysdate);
========================
3. To get a list of Responsibilities user belongs to:
SELECT fu.user_name,
frv.responsibility_name
FROM apps.fnd_responsibility_vl frv,
applsys.fnd_request_groups frg,
apps.fnd_user_resp_groups_all furga,
applsys.fnd_user fu
WHERE fu.user_name =
TRUNC(SYSDATE) BETWEEN fu.start_date AND
NVL(fu.end_date , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND
fu.user_id = furga.user_id AND
TRUNC(SYSDATE) BETWEEN furga.start_date AND
NVL(furga.end_date , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND
furga.responsibility_id = frv.responsibility_id AND
frv.request_group_id = frg.request_group_id;
=====================
4. To Find Responsibilites and Key:
SELECT B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID AND
A.LANGUAGE = USERENV('LANG') and
(upper(A.RESPONSIBILITY_NAME) like 'XX%')
ORDER BY A.RESPONSIBILITY_NAME;
======================
5. To see users, corresponding responsibilities and menus:
SELECT FND_USER.USER_NAME,
FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME,
FND_MENUS_TL.USER_MENU_NAME,
FND_MENUS_TL.MENU_ID
FROM FND_USER_RESP_GROUPS,
FND_USER,
FND_RESPONSIBILITY,
FND_RESPONSIBILITY_TL,
FND_MENUS_TL
WHERE FND_USER_RESP_GROUPS.USER_ID = FND_USER.USER_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID =
FND_RESPONSIBILITY.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID =
FND_RESPONSIBILITY.APPLICATION_ID
AND FND_RESPONSIBILITY.APPLICATION_ID =
FND_RESPONSIBILITY_TL.APPLICATION_ID
AND FND_RESPONSIBILITY.RESPONSIBILITY_ID =
FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID
AND FND_RESPONSIBILITY_TL.LANGUAGE = 'US'
AND FND_USER_RESP_GROUPS.SECURITY_GROUP_ID = 0
AND FND_MENUS_TL.LANGUAGE = 'US'
AND FND_MENUS_TL.MENU_ID = FND_RESPONSIBILITY.MENU_ID
ORDER BY
FND_USER.USER_NAME,
FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME;
======================
6. To find Flexfields and valuesets
SELECT A.ID_FLEX_STRUCTURE_CODE, B.ID_FLEX_CODE, E.LANGUAGE, D.FLEX_VALUE, E.DESCRIPTION, E.FLEX_VALUE_MEANING, B.FLEX_VALUE_SET_ID, B.APPLICATION_COLUMN_NAME, B.SEGMENT_NAME, C.FLEX_VALUE_SET_NAME, D.FLEX_VALUE_ID
FROM APPS.fnd_id_flex_structures A, APPS.fnd_id_flex_segments B, APPS.fnd_flex_value_sets C, APPS.fnd_flex_values D, APPS.fnd_flex_values_tl E
WHERE A.ID_FLEX_NUM=B.ID_FLEX_NUM --AND A.ID_FLEX_CODE='GL#' --AND B.ID_FLEX_CODE='GL#' AND B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID AND C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID AND D.FLEX_VALUE_ID=E.FLEX_VALUE_ID AND A.APPLICATION_ID IN (101,201) AND E.LANGUAGE='US' ORDER BY A.ID_FLEX_STRUCTURE_CODE;
7. To find Descriptive Field information for PO and GL
SELECT flex_vl.title,
flex.descriptive_flexfield_name,
context_vl.descriptive_flex_context_code,
context_vl.description,
col_usage_vl.application_column_name,
col_usage_vl.end_user_column_name
FROM apps.FND_DESCRIPTIVE_FLEXS flex,
apps.FND_DESCRIPTIVE_FLEXS_vl flex_vl,
apps.FND_DESCR_FLEX_CONTEXTS_VL context_vl,
apps.FND_DESCR_FLEX_COL_USAGE_VL col_usage_vl
WHERE flex_vl.application_id IN (201, 101)
-- AND flex_vl.title = <'Approved Supplier List' >
AND flex.descriptive_flexfield_name =
flex_vl.descriptive_flexfield_name
AND flex.descriptive_flexfield_name =
context_vl.descriptive_flexfield_name --'PO_APPROVED_SUPPLIER_LIST'
AND flex.descriptive_flexfield_name =
col_usage_vl.descriptive_flexfield_name
AND col_usage_vl.descriptive_flex_context_code =
context_vl.descriptive_flex_context_code
ORDER BY col_usage_vl.descriptive_flex_context_code,
col_usage_vl.application_column_name;
8. To find Menus
SELECT DISTINCT
B.ROWID ROW_ID,
B.MENU_ID,
B.MENU_NAME,
B.TYPE,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATION_DATE,
B.CREATED_BY,
T.USER_MENU_NAME,
T.DESCRIPTION,
T.LANGUAGE,
C.SUB_MENU_ID
FROM apps.FND_MENUS_TL T,
apps.FND_MENUS B,
apps.FND_MENU_ENTRIES C
WHERE B.MENU_ID = T.MENU_ID
AND T.LANGUAGE = USERENV ('LANG')
AND B.MENU_ID = C.MENU_ID;
9. to find Responsibilities, Attached Menu and Application to A particular responsibility:
SELECT DISTINCT
B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY,
B.APPLICATION_ID,
C.USER_MENU_NAME,
E.APPLICATION_NAME
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B,
APPS.FND_MENUS_TL C,
APPS.FND_MENUS D,
apps.FND_APPLICATION_TL E,
apps.FND_APPLICATION F
WHERE A.RESPONSIBILITY_ID(+) = B.RESPONSIBILITY_ID
AND B.MENU_ID = C.MENU_ID
AND B.MENU_ID = D.MENU_ID
AND E.APPLICATION_ID = F.APPLICATION_ID
AND F.APPLICATION_ID = B.APPLICATION_ID
AND A.LANGUAGE = 'US';
===================
