[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(function_name) into v_edit_priv
FROM fnd_form_functions
WHERE function_name='ENG_EDIT_CHANGE' AND
function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
SELECT count(function_name) into v_view_priv
FROM fnd_form_functions
WHERE function_name='ENG_VIEW_CHANGE' AND
function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
cursor_select NUMBER;
SELECT
parties.party_name grantee_name,
grantee_type,
role_name,
role_display_name,
menu_id
FROM hz_parties parties,
(
SELECT DISTINCT
GRANTS.grantee_orig_system_id grantee_orig_system_id,
DECODE(grants.grantee_key,'GLOBAL','HZ_GLOBAL', SUBSTR(grants.grantee_key,0, INSTR(grants.grantee_key,':',1,1)-1)) grantee_orig_system,
GRANTS.grantee_type grantee_type,
menus.Menu_name role_name,
menus.user_menu_name role_display_name,
grants.grantee_key,
menus.menu_id menu_id
FROM fnd_grants GRANTS,
fnd_objects OBJECTS,
fnd_menus_vl MENUS
WHERE OBJECTS.object_id = GRANTS.object_id
AND OBJECTS.obj_name=cp_entity_name
AND GRANTS.instance_type='INSTANCE'
AND GRANTS.instance_pk1_value=cp_pk1_value
AND
(( grants.instance_pk2_value = cp_pk2_value
) OR (( grants.instance_pk2_value = '*NULL*')
AND ( cp_pk2_value is NULL)
))
AND
(( grants.instance_pk3_value = cp_pk3_value
) OR (( grants.instance_pk3_value = '*NULL*')
AND ( cp_pk3_value is NULL)
))
AND
(( grants.instance_pk4_value = cp_pk4_value
) OR (( grants.instance_pk4_value = '*NULL*')
AND ( cp_pk4_value is NULL)
))
AND
(( grants.instance_pk5_value = cp_pk5_value
) OR (( grants.instance_pk5_value = '*NULL*')
AND ( cp_pk5_value is NULL)
))
AND GRANTS.menu_id = MENUS.menu_id
AND
(( cp_role_name is null AND menus.menu_name like '%'
) OR ( MENUS.MENU_NAME in (cp_role_name)
))
AND GRANTS.start_date <= sysdate
AND
(
GRANTS.end_date is null
OR grants.end_date >= SYSDATE
)
) grants -- End of First From clause
WHERE grantee_type in ('USER','GROUP') --,'COMPANY','GLOBAL')
AND grantee_orig_system in ('HZ_PARTY','HZ_GROUP') --,'HZ_COMPANY', 'HZ_GLOBAL')
AND parties.party_id=DECODE(grants.grantee_key,'GLOBAL',-1000, SUBSTR(grants.grantee_key, INSTR(grants.grantee_key,':',1,1)+1));
SELECT pk1_value, pk2_value FROM eng_change_subjects where change_id = p_pk1_value;
SELECT revised_item_id, organization_id FROM eng_revised_items WHERE change_id = p_pk1_value; -- p_pk1_value = change_id
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'( ' ||
'SELECT ' ||
'C.CHANGE_ID, ' ||
'P.ASSIGNEE_ID, ' ||
'C.CREATION_DATE ' ||
'FROM WF_ACTIVITIES WA, ' ||
'WF_ACTIVITY_ATTRIBUTES WAA, ' ||
'ENG_CHANGE_ROUTE_STEPS S, ' ||
'ENG_CHANGE_ROUTE_PEOPLE P, ' ||
'ENG_CHANGE_ROUTES R, ' ||
'ENG_ENGINEERING_CHANGES C ' ||
'WHERE WAA.TEXT_DEFAULT = ''ENG_CHANGE_WF_APPROVERS'' ' ||
'AND WAA.NAME = ''DEFAULT_CHANGE_ROLE'' ' ||
'AND WAA.ACTIVITY_VERSION = WA.VERSION ' ||
'AND WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE ' ||
'AND WAA.ACTIVITY_NAME = WA.NAME ' ||
'AND WA.TYPE = ''PROCESS'' ' ||
'AND WA.BEGIN_DATE <= SYSDATE ' ||
'AND ' ||
'( ' ||
'WA.END_DATE >= SYSDATE ' ||
'OR WA.END_DATE IS NULL ' ||
') ' ||
'AND WA.ITEM_TYPE = S.WF_ITEM_TYPE ' ||
'AND WA.NAME = S.WF_PROCESS_NAME ' ||
'AND P.ASSIGNEE_ID <> -1 ' ||
'AND P.STEP_ID = S.STEP_ID ' ||
'AND S.ROUTE_ID = R.ROUTE_ID ' ||
'AND R.CLASSIFICATION_CODE = TO_CHAR(C.STATUS_CODE) ' ||
'AND R.OBJECT_ID1 = C.CHANGE_ID ' ||
'AND R.OBJECT_NAME = ''ENG_CHANGE'' ' ||
'AND R.TEMPLATE_FLAG = ''N'' ' ||
') ' ||
'child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_APPROVER'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'FND_USER fuser, ' ||
'FND_USER fpuser, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.CREATED_BY = fuser.user_id ' ||
'AND child_table_name.CREATED_BY = fpuser.user_id ' ||
'AND TO_CHAR(fuser.employee_id) = hzei.person_identifier(+) ' ||
'AND fuser.customer_id = hzci.party_id(+) ' ||
'AND fuser.supplier_id = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = hzei.party_id ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_CREATOR'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_ASSIGNEE'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''GROUP'' grantee_type, ' ||
'grantee_group.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES grantee_group, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = grantee_group.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND grantee_group.party_type = ''GROUP'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_ASSIGNEE'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.REQUESTOR_ID = hzei.party_id(+) ' ||
'AND child_table_name.REQUESTOR_ID = hzci.party_id(+) ' ||
'AND child_table_name.REQUESTOR_ID = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = child_table_name.REQUESTOR_ID ' ||
'AND child_table_name.REQUESTOR_ID IS NOT NULL ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REQUESTOR'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''GROUP'' grantee_type, ' ||
'grantee_group.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES grantee_group, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.REQUESTOR_ID = grantee_group.party_id(+) ' ||
'AND child_table_name.REQUESTOR_ID IS NOT NULL ' ||
'AND grantee_group.party_type = ''GROUP'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REQUESTOR'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'( ' ||
'SELECT ' ||
'C.CHANGE_ID, ' ||
'P.ASSIGNEE_ID, ' ||
'C.CREATION_DATE ' ||
'FROM WF_ACTIVITIES WA, ' ||
'WF_ACTIVITY_ATTRIBUTES WAA, ' ||
'ENG_CHANGE_ROUTE_STEPS S, ' ||
'ENG_CHANGE_ROUTE_PEOPLE P, ' ||
'ENG_CHANGE_ROUTES R, ' ||
'ENG_ENGINEERING_CHANGES C ' ||
'WHERE WAA.TEXT_DEFAULT = ''ENG_CHANGE_WF_REVIEWERS'' ' ||
'AND WAA.NAME = ''DEFAULT_CHANGE_ROLE'' ' ||
'AND WAA.ACTIVITY_VERSION = WA.VERSION ' ||
'AND WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE ' ||
'AND WAA.ACTIVITY_NAME = WA.NAME ' ||
'AND WA.TYPE = ''PROCESS'' ' ||
'AND WA.BEGIN_DATE <= SYSDATE ' ||
'AND ' ||
'( ' ||
' WA.END_DATE >= SYSDATE ' ||
' OR WA.END_DATE IS NULL ' ||
') ' ||
'AND WA.ITEM_TYPE = S.WF_ITEM_TYPE ' ||
'AND WA.NAME = S.WF_PROCESS_NAME ' ||
'AND P.ASSIGNEE_ID <> -1 ' ||
'AND P.STEP_ID = S.STEP_ID ' ||
'AND S.ROUTE_ID = R.ROUTE_ID ' ||
'AND R.CLASSIFICATION_CODE = TO_CHAR(C.STATUS_CODE) ' ||
'AND R.OBJECT_ID1 = C.CHANGE_ID ' ||
'AND R.OBJECT_NAME = ''ENG_CHANGE'' ' ||
'AND R.TEMPLATE_FLAG = ''N'' ' ||
') ' ||
'child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'ENG_CHANGE_LINES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''GROUP'' grantee_type, ' ||
'grantee_group.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES grantee_group, ' ||
'ENG_CHANGE_LINES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = grantee_group.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND grantee_group.party_type = ''GROUP'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''USER'' grantee_type, ' ||
'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES hzei, ' ||
'HZ_PARTIES hzci, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND hzei.party_type=''PERSON'' ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'UNION ' ||
'SELECT DISTINCT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''GROUP'' grantee_type, ' ||
'grantee_group.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'HZ_PARTIES grantee_group, ' ||
'ENG_ENGINEERING_CHANGES child_table_name ' ||
'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
'AND child_table_name.ASSIGNEE_ID = grantee_group.party_id(+) ' ||
'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
'AND grantee_group.party_type = ''GROUP'' ' ||
'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') '; /* || -- Removed since There will be no roles for
'SELECT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''COMPANY'' grantee_type, ' ||
'internal_company.company_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM hz_parties grantee_global, ' ||
'fnd_menus_tl granted_menu, ' ||
'fnd_menus profile_menu_data, ' ||
'fnd_menus granted_menu_data, ' ||
'ego_obj_role_mappings mapping, ' ||
'fnd_objects obj, ' ||
'FND_PROFILE_OPTIONS profile, ' ||
'FND_PROFILE_OPTION_VALUES profile_value, ' ||
'EGO_INTERNAL_COMPANY_V internal_company ' ||
'WHERE profile.profile_option_id = profile_value.profile_option_id ' ||
'AND profile.profile_option_name in (''EGO_INTERNAL_USER_DEFAULT_ROLE'', ''ENG_INTERNAL_USER_DEFAULT_ROLE'') ' ||
'AND obj.obj_name = ''EGO_ITEM'' ' ||
'AND grantee_global.party_id = -1000 ' ||
'AND mapping.parent_object_id = obj.object_id ' ||
'AND profile_menu_data.menu_name = profile_value.profile_option_value ' ||
'AND mapping.parent_role_id = profile_menu_data.menu_id ' ||
'AND granted_menu.menu_id = mapping.child_role_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'AND granted_menu_data.menu_id = granted_menu.menu_id ' ||
'AND EXISTS ' ||
'( ' ||
'SELECT ' ||
'pk1_value, ' ||
'pk2_value ' ||
'FROM eng_change_subjects_v ' ||
'WHERE ' ||
'( ' ||
' OBJECT_NAME=''EGO_ITEM'' ' ||
' OR OBJECT_NAME=''EGO_ITEM_REVISION'' ' ||
') ' ||
'AND pk1_value is NOT NULL ' ||
'AND change_id = ' || l_pk1_value || ' ' ||
') ' ||
'UNION ' ||
'SELECT ' ||
'granted_menu_data.menu_name role_name, ' ||
'granted_menu.user_menu_name role_display_name, ' ||
'''COMPANY'' grantee_type, ' ||
'internal_company.company_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM hz_parties grantee_global, ' ||
'fnd_menus_tl granted_menu, ' ||
'fnd_menus profile_menu_data, ' ||
'fnd_menus granted_menu_data, ' ||
'fnd_objects obj, ' ||
'FND_PROFILE_OPTIONS profile, ' ||
'FND_PROFILE_OPTION_VALUES profile_value, ' ||
'EGO_INTERNAL_COMPANY_V internal_company ' ||
'WHERE profile.profile_option_id = profile_value.profile_option_id ' ||
'AND profile.profile_option_name in (''EGO_INTERNAL_USER_DEFAULT_ROLE'', ''ENG_INTERNAL_USER_DEFAULT_ROLE'') ' ||
'AND obj.obj_name = ''ENG_CHANGE'' ' ||
'AND obj.APPLICATION_ID = profile.APPLICATION_ID ' ||
'AND grantee_global.party_id = -1000 ' ||
'AND profile_menu_data.menu_name = profile_value.profile_option_value ' ||
'AND granted_menu.menu_id = profile_menu_data.menu_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'AND granted_menu_data.menu_id = granted_menu.menu_id ' ; */
'SELECT ' ||
'granted_menu_data.menu_name internal_role_name, ' ||
'granted_menu.user_menu_name role_name, ' ||
'grants.grantee_type grantee_type, ' ||
'hzsi.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_grants grants, ' ||
'HZ_PARTIES hzsi, ' ||
'HZ_RELATIONSHIPS hzr, ' ||
'HZ_PARTIES hzc, ' ||
'fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'ego_obj_role_mappings mapping, ' ||
'fnd_objects obj,' ||
'eng_engineering_changes changes,' ||
'eng_change_subjects subjects,' ||
'eng_revised_items rev_items ' ||
'WHERE grants.grantee_type = ''USER'' ' ||
'AND grants.object_id = obj.object_id ' ||
'AND mapping.parent_object_id = grants.object_id ' ||
'AND mapping.parent_role_id = grants.menu_id ' ||
'AND SUBSTR(grants.grantee_key, 1, INSTR(grants.grantee_key, '':'')-1) =''HZ_PARTY'' ' ||
'AND TO_NUMBER(REPLACE(grants.grantee_key,''HZ_PARTY:'','''')) = hzsi.party_id(+) ' ||
'AND hzsi.party_type=''PERSON'' ' ||
'AND hzr.SUBJECT_ID(+) = TO_NUMBER(REPLACE(grants.grantee_key,''HZ_PARTY:'','''')) ' ||
'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
'AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)' ||
'AND granted_menu.menu_id = mapping.child_role_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.LANGUAGE= USERENV(''LANG'') ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND grants.instance_type = ''SET'' ' ||
'AND ' ||
'( ( obj.obj_name = ''EGO_ITEM'' ' ||
' AND ( grants.instance_set_id in (' || l_inst_set_ids || ')' ||
' ) ' ||
')) ' ||
'AND changes.change_id = ' || l_pk1_value || ' ' ||
'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
' OR ( changes.change_id = REV_ITEMS.change_id )) ' ;
'SELECT ' ||
'granted_menu_data.menu_name internal_role_name, ' ||
'granted_menu.user_menu_name role_name, ' ||
'grants.grantee_type grantee_type, ' ||
'grantee_group.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_grants grants, ' ||
'HZ_PARTIES grantee_group, ' ||
'fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'ego_obj_role_mappings mapping, ' ||
'fnd_objects obj , ' ||
'eng_engineering_changes changes, ' ||
'eng_change_subjects subjects, ' ||
'eng_revised_ITEMS REV_ITEMS ' ||
'WHERE grants.grantee_type = ''GROUP'' ' ||
'AND grants.object_id = obj.object_id ' ||
'AND mapping.parent_object_id = grants.object_id ' ||
'AND mapping.parent_role_id = grants.menu_id ' ||
'AND grantee_group.party_type = ''GROUP'' ' ||
'AND SUBSTR(grants.grantee_key, 1, INSTR(grants.grantee_key, '':'')-1) =''HZ_GROUP'' ' ||
'AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE) ' ||
'AND TO_NUMBER(REPLACE(grants.grantee_key,''HZ_GROUP:'','''')) = grantee_group.party_id ' ||
'AND granted_menu.menu_id = mapping.child_role_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.LANGUAGE= USERENV(''LANG'') ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND grants.instance_type = ''SET'' ' ||
'AND ' ||
'( ( obj.obj_name = ''EGO_ITEM'' AND ' ||
' grants.instance_set_id in ( ' || l_inst_set_ids || ' ) ' ||
') ) ' ||
'AND changes.change_id = ' || l_pk1_value || ' ' ||
'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
' OR ( changes.change_id = REV_ITEMS.change_id )) ';
'SELECT ' ||
'granted_menu_data.menu_name internal_role_name, ' ||
'granted_menu.user_menu_name role_name, ' ||
'grants.grantee_type grantee_type, ' ||
'grantee_company.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_grants grants, ' ||
'hz_parties grantee_company, ' ||
'fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'ego_obj_role_mappings mapping, ' ||
'fnd_objects obj, ' ||
'eng_engineering_changes changes, ' ||
'eng_change_subjects subjects, ' ||
'eng_revised_items rev_items ' ||
'WHERE grants.grantee_type = ''COMPANY'' ' ||
'AND grants.object_id = obj.object_id ' ||
'AND grantee_company.party_type = ''ORGANIZATION'' ' ||
'AND mapping.parent_object_id = grants.object_id ' ||
'AND mapping.parent_role_id = grants.menu_id ' ||
'AND to_number(replace(grants.grantee_key,''HZ_COMPANY:'','''')) = grantee_company.party_id ' ||
'AND substr(grants.grantee_key, 1, instr(grants.grantee_key, '':'')-1) =''HZ_COMPANY'' ' ||
'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
'AND granted_menu.menu_id = mapping.child_role_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND grants.instance_type = ''SET'' ' ||
'AND ' ||
'( ( obj.obj_name = ''EGO_ITEM'' ' ||
' AND ( grants.instance_set_id in ( ' || l_inst_set_ids || ' ) )' ||
') ) ' ||
'AND changes.change_id = ' || l_pk1_value || ' ' ||
'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
' OR ( changes.change_id = REV_ITEMS.change_id ))';
'SELECT ' ||
'granted_menu_data.menu_name internal_role_name, ' ||
'granted_menu.user_menu_name role_name, ' ||
'grants.grantee_type grantee_type, ' ||
'grantee_global.party_name grantee_name, ' ||
'granted_menu_data.menu_id menu_id ' ||
'FROM fnd_grants grants, ' ||
'hz_parties grantee_global, ' ||
'fnd_menus_tl granted_menu, ' ||
'fnd_menus granted_menu_data, ' ||
'ego_obj_role_mappings mapping, ' ||
'fnd_objects obj, ' ||
'eng_engineering_changes changes, ' ||
'eng_change_subjects subjects, ' ||
'eng_revised_items rev_items ' ||
'WHERE grants.grantee_type = ''GLOBAL'' ' ||
'AND grants.object_id = obj.object_id ' ||
'AND mapping.parent_object_id = grants.object_id ' ||
'AND mapping.parent_role_id = grants.menu_id ' ||
'AND grantee_global.party_type = ''GLOBAL'' ' ||
'AND grantee_global.party_id = -1000 ' ||
'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
'AND granted_menu.menu_id = mapping.child_role_id ' ||
'AND ' ||
'(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
')) ' ||
'AND granted_menu.language= userenv(''LANG'') ' ||
'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
'AND grants.instance_type = ''SET'' ' ||
'AND ' ||
'( ( ' ||
' obj.obj_name = ''EGO_ITEM'' AND ' ||
' ( grants.instance_set_id in (' || l_inst_set_ids || ' ) ' ||
' ) ' ||
') ) ' ||
'AND changes.change_id = ' || l_pk1_value || ' ' ||
'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
' OR ( changes.change_id = REV_ITEMS.change_id )) ';
cursor_select := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
dbms_sql.DEFINE_COLUMN(cursor_select, 1, '', 30);
dbms_sql.DEFINE_COLUMN(cursor_select, 2, '', 120);
dbms_sql.DEFINE_COLUMN(cursor_select, 3, '', 30);
dbms_sql.DEFINE_COLUMN(cursor_select, 4, '', 120);
dbms_sql.DEFINE_COLUMN(cursor_select, 5, l_temp_menu_id);
cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
IF dbms_sql.fetch_rows(cursor_select) > 0 THEN
DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_grantee_list(l_index).role_name);
DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_grantee_list(l_index).role_display_name);
DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_grantee_list(l_index).grantee_type);
DBMS_SQL.COLUMN_VALUE(cursor_select, 4, l_grantee_list(l_index).grantee_name);
DBMS_SQL.COLUMN_VALUE(cursor_select, 5, l_temp_menu_id);
DBMS_SQL.CLOSE_CURSOR(cursor_select);
SELECT DISTINCT
sets.instance_set_id instance_set_id ,
sets.instance_set_name instance_set_name,
sets.predicate predicate
FROM fnd_grants grants,
fnd_object_instance_sets sets,
fnd_objects obj
WHERE obj.obj_name = p_obj_name
AND grants.object_id = obj.object_id
AND grants.instance_type='SET'
AND grants.parameter1 is null
AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate)
AND grants.grantee_type = p_grantee_type
AND sets.instance_set_id = grants.instance_set_id
ORDER BY instance_set_name;
SELECT
DATABASE_OBJECT_NAME,
PK1_COLUMN_NAME,
PK2_COLUMN_NAME,
PK3_COLUMN_NAME,
PK4_COLUMN_NAME,
PK5_COLUMN_NAME
FROM fnd_objects
WHERE OBJ_NAME = p_obj_name;
cursor_select NUMBER;
obj_std_pkq := 'SELECT ' || obj_meta_data_rec.PK1_COLUMN_NAME;
query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
cursor_select := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_select, ':id1', p_bind1);
DBMS_SQL.BIND_VARIABLE(cursor_select, ':id2', p_bind2);
DBMS_SQL.BIND_VARIABLE(cursor_select, ':id3', p_bind3);
DBMS_SQL.BIND_VARIABLE(cursor_select, ':id4', p_bind4);
DBMS_SQL.BIND_VARIABLE(cursor_select, ':id5', p_bind5);
cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
IF i = 1 THEN
inst_set_ids := to_char(inst_set_preds_rec.instance_set_id);
DBMS_SQL.CLOSE_CURSOR(cursor_select);