The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'HZ_GROUP:'||group_membership_rel.object_id group_name
FROM hz_relationships group_membership_rel
WHERE group_membership_rel.RELATIONSHIP_CODE = 'MEMBER_OF'
AND group_membership_rel.status= 'A'
AND group_membership_rel.start_date <= SYSDATE
AND NVL(group_membership_rel.end_date, SYSDATE) >= SYSDATE
AND group_membership_rel.subject_id = cp_orig_system_id;
SELECT 'HZ_COMPANY:'||group_membership_rel.object_id company_name
FROM hz_relationships group_membership_rel
WHERE group_membership_rel.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND group_membership_rel.status= 'A'
AND group_membership_rel.start_date <= SYSDATE
AND NVL(group_membership_rel.end_date, SYSDATE) >= SYSDATE
AND group_membership_rel.subject_id = cp_orig_system_id;
SELECT count(1) into l_count
FROM hz_parties employee,
FND_USER FND_user,
PER_ALL_PEOPLE_F HR_EMPLOYEE
WHERE HR_EMPLOYEE.PERSON_ID = FND_user.EMPLOYEE_ID
AND fnd_user.start_date <= SYSDATE
AND NVL(fnd_user.end_date, SYSDATE) >= SYSDATE
AND (HR_EMPLOYEE.CURRENT_EMPLOYEE_FLAG = 'Y'
OR HR_EMPLOYEE.CURRENT_NPW_FLAG = 'Y'
)
AND HR_EMPLOYEE.EFFECTIVE_START_DATE <= SYSDATE
AND NVL(HR_EMPLOYEE.EFFECTIVE_END_DATE,SYSDATE) >= SYSDATE
AND employee.party_type = 'PERSON'
AND employee.status = 'A'
AND employee.party_id = HR_EMPLOYEE.PARTY_ID
AND user_name = p_user_name;
SELECT Count(1) into l_count
FROM hz_parties employee ,
FND_USER FND_user ,
PER_ALL_PEOPLE_F HR_EMPLOYEE
WHERE HR_EMPLOYEE.PERSON_ID = FND_user.EMPLOYEE_ID
AND fnd_user.start_date <= SYSDATE
AND NVL(fnd_user.end_date, SYSDATE) >= SYSDATE
AND (HR_EMPLOYEE.CURRENT_EMPLOYEE_FLAG = 'Y'
OR HR_EMPLOYEE.CURRENT_NPW_FLAG = 'Y')
AND HR_EMPLOYEE.EFFECTIVE_START_DATE <= SYSDATE
AND NVL(HR_EMPLOYEE.EFFECTIVE_END_DATE,SYSDATE) >= SYSDATE
AND employee.party_type = 'PERSON'
AND employee.status = 'A'
AND employee.party_id = HR_EMPLOYEE.PARTY_ID
AND employee.party_id = l_party_id;
select WR.orig_system, WR.orig_system_id
into x_orig_system, x_orig_system_id
from WF_ROLES WR
where WR.NAME = x_user_name
and WR.ORIG_SYSTEM IN ('PER', 'FND_USR');
SELECT party_id
INTO x_orig_system_id
FROM ego_user_v
where user_name = x_user_name;
select object_id
into l_object_id
from fnd_objects
where obj_name=p_object_name;
select menu_id
into l_role_id
from fnd_menus
where menu_name = p_role_name;
SELECT pk1_column_name
,pk2_column_name
,pk3_column_name
,pk4_column_name
,pk5_column_name
,pk1_column_type
,pk2_column_type
,pk3_column_type
,pk4_column_type
,pk5_column_type
, database_object_name
FROM fnd_objects
WHERE obj_name=p_object_name ;
l_select_query_part VARCHAR2(3000);
SELECT menu_id
FROM fnd_menu_entries
WHERE function_id =
(SELECT function_id
FROM fnd_form_functions
WHERE function_name = cp_function_name
);
'SELECT ''X'' ' ||
' FROM fnd_grants grants ' ||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE '||
' AND NVL(grants.end_date, SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ' ||
' AND grants.instance_pk1_value = :pk1_val ' ||
' AND ( grants.instance_pk2_value = :pk2_val OR' ||
' ( grants.instance_pk2_value = ''*NULL*'' AND :pk2_val is NULL )) '||
' AND ( grants.instance_pk3_value = :pk3_val OR '||
' ( grants.instance_pk3_value = ''*NULL*'' AND :pk3_val is NULL )) '||
' AND ( grants.instance_pk4_value = :pk4_val OR '||
' ( grants.instance_pk4_value = ''*NULL*'' AND :pk4_val is NULL )) '||
' AND ( grants.instance_pk5_value = :pk5_val OR '||
' ( grants.instance_pk5_value = ''*NULL*'' AND :pk5_val is NULL )) '; */
'SELECT ''X'' ' ||
' FROM fnd_grants grants, ego_item_cat_denorm_hier cathier ' ||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE '||
' AND NVL(grants.end_date, SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ' ||
' AND grants.instance_pk1_value = cathier.parent_catalog_group_id ' ||
' AND cathier.child_catalog_group_id = :pk1_val ' ||
' AND ( grants.instance_pk2_value = :pk2_val OR' ||
' ( grants.instance_pk2_value = ''*NULL*'' AND :pk2_val is NULL )) '||
' AND ( grants.instance_pk3_value = :pk3_val OR '||
' ( grants.instance_pk3_value = ''*NULL*'' AND :pk3_val is NULL )) '||
' AND ( grants.instance_pk4_value = :pk4_val OR '||
' ( grants.instance_pk4_value = ''*NULL*'' AND :pk4_val is NULL )) '||
' AND ( grants.instance_pk5_value = :pk5_val OR '||
' ( grants.instance_pk5_value = ''*NULL*'' AND :pk5_val is NULL )) ';
'SELECT ''X'' ' ||
' FROM fnd_grants grants ' ||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE '||
' AND NVL(grants.end_date, SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ' ||
' AND grants.instance_pk1_value = :pk1_val ' ||
' AND ( grants.instance_pk2_value = :pk2_val OR' ||
' ( grants.instance_pk2_value = ''*NULL*'' AND :pk2_val is NULL )) '||
' AND ( grants.instance_pk3_value = :pk3_val OR '||
' ( grants.instance_pk3_value = ''*NULL*'' AND :pk3_val is NULL )) '||
' AND ( grants.instance_pk4_value = :pk4_val OR '||
' ( grants.instance_pk4_value = ''*NULL*'' AND :pk4_val is NULL )) '||
' AND ( grants.instance_pk5_value = :pk5_val OR '||
' ( grants.instance_pk5_value = ''*NULL*'' AND :pk5_val is NULL )) ';
/*l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE '; */
l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name || ', ego_item_cat_denorm_hier cathier'||
' WHERE ';
l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE ';
l_select_query_part := l_select_query_part ||
' ( '||l_db_pk1_column||' = :pk1_val '||
' OR ( '||l_db_pk1_column||' is NULL AND :pk1_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk2_column||' = :pk2_val '||
' OR ( '||l_db_pk2_column||' is NULL AND :pk2_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk3_column||' = :pk3_val '||
' OR ( '||l_db_pk3_column||' is NULL AND :pk3_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk4_column||' = :pk4_val '||
' OR ( '||l_db_pk4_column||' is NULL AND :pk4_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk5_column||' = :pk5_val '||
' OR ( '||l_db_pk5_column||' is NULL AND :pk5_val is NULL))';
l_select_query_part := l_select_query_part || ' AND item_catalog_group_id = cathier.child_catalog_group_id(+) AND ';
l_select_query_part := l_select_query_part || ' AND ';
l_dynamic_sql := l_select_query_part;
' SELECT sets.instance_SET_ID, sets.predicate ' ||
' FROM fnd_object_instance_sets sets '||
' WHERE (sets.instance_set_id, sets.object_id) IN ' ||
' (SELECT grants.instance_set_id, grants.object_id ' ||
' FROM fnd_grants grants '||
' WHERE grants.object_id = :object_id '||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE '||
' AND grants.instance_type = :instance_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND '||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') )' ;
SELECT p.parent_role_id parent_role_id
FROM fnd_menu_entries r, fnd_form_functions f,
fnd_menus m, ego_obj_role_mappings p
WHERE r.function_id = f.function_id
AND r.menu_id = m.menu_id
AND f.function_name = cp_function
AND m.menu_id = p.child_role_id
AND p.child_object_id = cp_object_id
AND p.parent_object_id = cp_parent_object_id
AND p.child_object_type = cp_object_type;
' SELECT ''X'' ' ||
' FROM fnd_grants grants ' ||
' WHERE grants.object_id = :object_id' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND grants.instance_pk1_value = :parent_instance_pk1_value ' ||
' AND grants.instance_pk2_value = :pk2_value ' ||
' AND grants.instance_pk3_value = :pk3_value ' ||
' AND grants.instance_pk4_value = :pk4_value ' ||
' AND grants.instance_pk5_value = :pk5_value ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') '; */
' SELECT ''X'' ' ||
' FROM fnd_grants grants , ego_item_cat_denorm_hier cathier ' ||
' WHERE grants.object_id = :object_id' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND grants.instance_pk1_value = cathier.parent_catalog_group_id ' ||
' AND cathier.child_catalog_group_id = :parent_instance_pk1_value ' ||
' AND grants.instance_pk2_value = :pk2_value ' ||
' AND grants.instance_pk3_value = :pk3_value ' ||
' AND grants.instance_pk4_value = :pk4_value ' ||
' AND grants.instance_pk5_value = :pk5_value ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ';
' SELECT ''X'' ' ||
' FROM fnd_grants grants ' ||
' WHERE grants.object_id = :object_id' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND grants.instance_pk1_value = :parent_instance_pk1_value ' ||
' AND grants.instance_pk2_value = :pk2_value ' ||
' AND grants.instance_pk3_value = :pk3_value ' ||
' AND grants.instance_pk4_value = :pk4_value ' ||
' AND grants.instance_pk5_value = :pk5_value ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ';
' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = :instance_type '||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ';
'SELECT ''X'' '||
' FROM '|| l_db_object_name ||
' WHERE '; */
'SELECT ''X'' '||
' FROM '|| l_db_object_name || ', ego_item_cat_denorm_hier cathier'||
' WHERE ';
'SELECT ''X'' '||
' FROM '|| l_db_object_name ||
' WHERE ';
l_select_query_part VARCHAR2(3000);
/*l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE '; */
l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name || ', ego_item_cat_denorm_hier cathier'||
' WHERE ';
l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE ';
l_select_query_part := l_select_query_part ||
' ( '||l_db_pk1_column||' = :pk1_val '||
' OR ( '||l_db_pk1_column||' is NULL AND :pk1_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk2_column||' = :pk2_val '||
' OR ( '||l_db_pk2_column||' is NULL AND :pk2_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk3_column||' = :pk3_val '||
' OR ( '||l_db_pk3_column||' is NULL AND :pk3_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk4_column||' = :pk4_val '||
' OR ( '||l_db_pk4_column||' is NULL AND :pk4_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk5_column||' = :pk5_val '||
' OR ( '||l_db_pk5_column||' is NULL AND :pk5_val is NULL))';
l_select_query_part := l_select_query_part || ' AND item_catalog_group_id = cathier.child_catalog_group_id(+) AND ';
l_select_query_part := l_select_query_part || ' AND ';
' SELECT DISTINCT sets.instance_set_id, sets.predicate ' ||
' FROM fnd_grants grants, ' ||
' fnd_object_instance_sets sets '||
' WHERE grants.instance_type = :instance_type '||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_set_id = sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) ';
l_dynamic_sql :=l_select_query_part ||
' ( ' || l_one_set_predicate || ' ) ';
' l_select_query_part: '||l_select_query_part||
' - l_one_set_predicate: '||l_one_set_predicate
);
'SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf '||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))'||
' AND cmf.function_id = fnd_functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id '; */
'SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf, '||
' ego_item_cat_denorm_hier cathier '||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))'||
' AND cmf.function_id = fnd_functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ';
'SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf '||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))'||
' AND cmf.function_id = fnd_functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ';
l_select_query_part VARCHAR2(3000);
/* l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE ';*/
l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||', ego_item_cat_denorm_hier cathier'||
' WHERE ';
l_select_query_part:=
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE ';
l_select_query_part := l_select_query_part ||
' ( '||l_db_pk1_column||' = :pk1_val '||
' OR ( '||l_db_pk1_column||' is NULL AND :pk1_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk2_column||' = :pk2_val '||
' OR ( '||l_db_pk2_column||' is NULL AND :pk2_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk3_column||' = :pk3_val '||
' OR ( '||l_db_pk3_column||' is NULL AND :pk3_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk4_column||' = :pk4_val '||
' OR ( '||l_db_pk4_column||' is NULL AND :pk4_val is NULL))';
l_select_query_part := l_select_query_part ||
' AND ( '||l_db_pk5_column||' = :pk5_val '||
' OR ( '||l_db_pk5_column||' is NULL AND :pk5_val is NULL))';
l_select_query_part := l_select_query_part || ' AND item_catalog_group_id = cathier.child_catalog_group_id(+) AND ';
l_select_query_part := l_select_query_part || ' AND ';
,p_message => 'Select Query Part '||l_select_query_part
);
' SELECT DISTINCT sets.instance_set_id, sets.predicate ' ||
' FROM fnd_grants grants, fnd_object_instance_sets sets' ||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE '||
' AND grants.instance_type = :instance_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND '||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND sets.instance_set_id = grants.instance_set_id ' ||
' AND sets.object_id = grants.object_id ';
l_dynamic_sql := l_select_query_part ||
' (' || l_one_set_predicate || ') ';
'SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf, '||
' ego_obj_role_mappings mapping '||
' WHERE grants.object_id = :object_id '||
' AND grants.start_date <= SYSDATE '||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))'||
' AND mapping.child_role_id = cmf.menu_id ' ||
' AND mapping.parent_role_id = grants.menu_id ' ||
' AND mapping.child_object_id = :child_object_id ' ||
' AND mapping.parent_object_id = :parent_object_id ' ||
' AND mapping.child_object_type = :object_type ' ||
' AND cmf.function_id = fnd_functions.function_id ';
' SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf, ' ||
' ego_obj_role_mappings mapping, ' ||
' fnd_menus menus ' ||
' WHERE menus.menu_name = :profile_role ' ||
' AND mapping.parent_role_id = menus.menu_id ' ||
' AND mapping.child_role_id = cmf.menu_id ' ||
' AND mapping.child_object_id = :profile_ch_object_id ' ||
' AND mapping.parent_object_id = :profile_parent_object_id ' ||
' AND mapping.child_object_type = :profile_ch_object_type ' ||
' AND cmf.function_id = fnd_functions.function_id ';
l_aggregate_predicate := 'EXISTS ( SELECT ''X'' ';
l_aggregate_predicate := l_pk_column_names || ' IN ( SELECT ' ||l_type_converted_val_cols;
' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = :instance_type ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND functions.function_name = :function ' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :grantee_key )' ||
' OR (grants.grantee_type = ''GROUP'' ' ||
' AND grants.grantee_key in ('||l_group_info||' ))' ||
' OR (grants.grantee_type = ''COMPANY'' ' ||
' AND grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
' UNION ALL (SELECT ''X'' ' || ' FROM ' ||
l_db_object_name || ' WHERE ' ||
l_orig_pk1_column || '=' || p_pk1_alias, 1, g_pred_buf_size);*/
' UNION ALL (SELECT ''X'' ' || ' FROM ' ||
l_db_object_name ||', ego_item_cat_denorm_hier cathier WHERE item_catalog_group_id = cathier.child_catalog_group_id(+) AND ' ||
l_orig_pk1_column || '=' || p_pk1_alias, 1, g_pred_buf_size);
' UNION ALL (SELECT ''X'' ' || ' FROM ' ||
l_db_object_name || ' WHERE ' ||
l_orig_pk1_column || '=' || p_pk1_alias, 1, g_pred_buf_size);
' UNION ALL (SELECT ' ||
l_pk_orig_column_names || ' FROM ' ||
l_db_object_name || ' WHERE ' ||
l_set_predicates || '))',
1, g_pred_buf_size);
SELECT party_type
FROM hz_parties
WHERE party_id=cp_party_id;
' SELECT INSTANCE_PK1_VALUE' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf ' ||
' WHERE grants.INSTANCE_PK1_VALUE=to_char('||p_pk1_alias||')'||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type= ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = ' || l_object_id ||
' AND functions.function_name = ''' || p_function || '''' ||
' AND ( ( grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = '''||l_user_name||''')'||
' OR ( grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in '||
' ( '|| l_group_info || ')) ' ||
' OR ( grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in '||
' ( '|| l_company_info || ')) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )))';*/
' SELECT INSTANCE_PK1_VALUE' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, ' ||
' ego_item_cat_denorm_hier cathier ' ||
' WHERE grants.INSTANCE_PK1_VALUE=to_char(cathier.parent_catalog_group_id)' ||
' AND cathier.child_catalog_group_id = to_char('||p_pk1_alias||')' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type= ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = ' || l_object_id ||
' AND functions.function_name = ''' || p_function || '''' ||
' AND ( ( grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = '''||l_user_name||''')'||
' OR ( grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in '||
' ( '|| l_group_info || ')) ' ||
' OR ( grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in '||
' ( '|| l_company_info || ')) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )))';
' SELECT INSTANCE_PK1_VALUE' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf ' ||
' WHERE grants.INSTANCE_PK1_VALUE=to_char('||p_pk1_alias||')'||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type= ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = ' || l_object_id ||
' AND functions.function_name = ''' || p_function || '''' ||
' AND ( ( grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = '''||l_user_name||''')'||
' OR ( grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in '||
' ( '|| l_group_info || ')) ' ||
' OR ( grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in '||
' ( '|| l_company_info || ')) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )))';
/*l_sub_pred_clause:= ' UNION ALL ( SELECT to_char('
||l_pk_orig_column_names || ') FROM ' ||
l_db_object_name ||
' WHERE ' || l_pk_orig_column_names||'='||p_pk1_alias ||
' AND ';*/
l_sub_pred_clause:= ' UNION ALL ( SELECT to_char('
||l_pk_orig_column_names || ') FROM ' ||
l_db_object_name ||
' , ego_item_cat_denorm_hier cathier WHERE ' || l_pk_orig_column_names||'='||p_pk1_alias ||
' AND ';
l_sub_pred_clause:= ' UNION ALL ( SELECT to_char('
||l_pk_orig_column_names || ') FROM ' ||
l_db_object_name ||
' WHERE ' || l_pk_orig_column_names||'='||p_pk1_alias ||
' AND ';
' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = :instance_type ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND functions.function_name = :function ' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :grantee_key )' ||
' OR (grants.grantee_type = ''GROUP'' ' ||
' AND grants.grantee_key in ( '||l_group_info||' ))' ||
' OR (grants.grantee_type = ''COMPANY'' ' ||
' AND grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
SELECT party_type
FROM hz_parties
WHERE party_id=cp_party_id;
' SELECT INSTANCE_PK1_VALUE' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf ' ||
' WHERE grants.start_date <= sysdate ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type= ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = ' || l_object_id ||
' AND functions.function_name = ''' || p_function || '''' ||
' AND ( ( grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = '''||l_user_name||''')'||
' OR ( grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in '||
' ( '|| l_group_info || ')) ' ||
' OR ( grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in '||
' ( '|| l_company_info || ')) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )))';*/
' SELECT INSTANCE_PK1_VALUE' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, ' ||
'ego_item_cat_denorm_hier cathier ' ||
'WHERE grants.INSTANCE_PK1_VALUE=to_char(cathier.parent_catalog_group_id) '||
'AND cathier.child_catalog_group_id = to_char('||p_pk1_alias||')' ||
' AND grants.start_date <= sysdate ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type= ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = ' || l_object_id ||
' AND functions.function_name = ''' || p_function || '''' ||
' AND ( ( grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = '''||l_user_name||''')'||
' OR ( grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in '||
' ( '|| l_group_info || ')) ' ||
' OR ( grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in '||
' ( '|| l_company_info || ')) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )))';
' SELECT INSTANCE_PK1_VALUE' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf ' ||
' WHERE grants.start_date <= sysdate ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type= ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = ' || l_object_id ||
' AND functions.function_name = ''' || p_function || '''' ||
' AND ( ( grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = '''||l_user_name||''')'||
' OR ( grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in '||
' ( '|| l_group_info || ')) ' ||
' OR ( grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in '||
' ( '|| l_company_info || ')) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )))';
/*l_sub_pred_clause:=' UNION ALL ( SELECT to_char(' ||
l_pk_orig_column_names || ') FROM ' ||
l_db_object_name || ' WHERE ';*/
l_sub_pred_clause:=' UNION ALL ( SELECT to_char(' ||
l_pk_orig_column_names || ') FROM ' ||
l_db_object_name || ', ego_item_cat_denorm_hier cathier WHERE item_catalog_group_id = cathier.child_catalog_group_id(+) AND ';
l_sub_pred_clause:=' UNION ALL ( SELECT to_char(' ||
l_pk_orig_column_names || ') FROM ' ||
l_db_object_name || ' WHERE ';
' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = :instance_type ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND functions.function_name = :function ' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :grantee_key )' ||
' OR (grants.grantee_type = ''GROUP'' ' ||
' AND grants.grantee_key in ( '||l_group_info||' ))' ||
' OR (grants.grantee_type = ''COMPANY'' ' ||
' AND grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
SELECT DISTINCT instance_sets.predicate
FROM fnd_grants grants,
fnd_object_instance_sets instance_sets
WHERE grants.instance_type = 'SET'
AND grants.start_date <= SYSDATE
AND ( grants.end_date IS NULL
OR grants.end_date >= SYSDATE )
AND grants.instance_set_id = instance_sets.instance_set_id
AND grants.object_id = cp_parent_object_id
AND ( grants.menu_id in
( SELECT p.parent_role_id
FROM fnd_menu_entries r, fnd_form_functions f,
fnd_menus m, ego_obj_role_mappings p
WHERE r.function_id = f.function_id
AND r.menu_id = m.menu_id
AND f.function_name = cp_function
AND m.menu_id = p.child_role_id
AND p.child_object_id = cp_object_id
AND P.parent_object_id = cp_parent_object_id ))
AND ( ( grants.grantee_type = 'USER'
AND grants.grantee_key = cp_user_name)
OR ( grants.grantee_type = 'GROUP'
AND grants.grantee_key in
( cp_group_info ))
OR ( grants.grantee_type = 'COMPANY'
AND grants.grantee_key in
( cp_company_info ))
OR (grants.grantee_type = 'GLOBAL'
AND grants.grantee_key in ('HZ_GLOBAL:-1000', 'GLOBAL') ));
SELECT DISTINCT p.parent_role_id
FROM fnd_menu_entries r, fnd_form_functions f,
fnd_menus m, ego_obj_role_mappings p
WHERE r.function_id = f.function_id
AND r.menu_id = m.menu_id
AND f.function_name = cp_function
AND m.menu_id = p.child_role_id
AND p.child_object_id = cp_object_id
AND P.parent_object_id = cp_parent_object_id
AND p.child_object_type = cp_object_type;
SELECT DISTINCT p.parent_role_id
FROM fnd_menu_entries r, fnd_form_functions f,
fnd_menus m, ego_obj_role_mappings p
WHERE r.function_id = f.function_id
AND r.menu_id = m.menu_id
AND f.function_name = cp_function
AND m.menu_id = p.child_role_id
AND p.child_object_id = cp_object_id
AND P.parent_object_id = cp_parent_object_id;
select cm.menu_name from
fnd_menus m, ego_obj_role_mappings r, fnd_menus cm
where r.parent_role_id = m.menu_id and
m.menu_name = cp_parent_role
and r.parent_object_id = cp_parent_object_id
and r.child_object_id = cp_child_object_id
and r.child_object_type = cp_child_object_type
and cm.MENU_ID = r.CHILD_ROLE_ID;
' SELECT '||l_type_converted_val_cols ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions functions, ' ||
' fnd_menu_entries cmf ';
l_sub_pred_clause:= ' UNION ALL ( SELECT '
||l_pk_orig_column_names || ' FROM ' ||
l_db_object_name ||
' WHERE ' || l_pk_orig_column_names||'='||p_pk1_alias ||
' and ';
' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = :instance_type ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND functions.function_name = :function ' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :grantee_key )' ||
' OR (grants.grantee_type = ''GROUP'' ' ||
' AND grants.grantee_key in ( '||l_group_info||' ))' ||
' OR (grants.grantee_type = ''COMPANY'' ' ||
' AND grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
' SELECT DISTINCT instance_sets.predicate '||
' FROM fnd_grants grants, '||
' fnd_object_instance_sets instance_sets,' ||
' fnd_form_functions F, fnd_menu_entries R, '||
' fnd_menus M, ego_obj_role_mappings P '||
' WHERE grants.instance_type = ''SET'' '||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE '||
' AND grants.instance_set_id = instance_sets.instance_set_id '||
' AND instance_sets.object_id = :parent_object_id ' ||
' AND grants.object_id = :parent_object_id1 ' ||
' AND grants.menu_id = p.parent_role_id ' ||
' AND r.function_id = f.function_id '||
' AND r.menu_id = m.menu_id ' ||
' AND f.function_name = :function_id '||
' AND m.menu_id = p.child_role_id ' ||
' AND p.child_object_id = :child_object_id '||
' AND p.parent_object_id = :parent_object_id2 '||
' AND p.child_object_type = :child_object_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR (grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))';
l_parent_predicate := l_parent_predicate || 'Select ' || l_pk_orig_column_names;
' SELECT '||l_type_converted_val_cols ||
' FROM fnd_grants grants ';
' UNION ALL ( SELECT ' ||l_pk_orig_column_names ||
' FROM ' || l_db_object_name ||
' WHERE ' || l_orig_pk_column || ' = ' || p_parent_obj_pk1alias_tbl(l_table_index)
, 1, g_pred_buf_size); */
' UNION ALL ( SELECT ' ||l_pk_orig_column_names ||
' FROM ' || l_db_object_name || ' , ego_item_cat_denorm_hier cathier '||
' WHERE item_catalog_group_id = cathier.child_catalog_group_id(+) AND ' || l_orig_pk_column || ' = ' || p_parent_obj_pk1alias_tbl(l_table_index)
, 1, g_pred_buf_size);
' UNION ALL ( SELECT ' ||l_pk_orig_column_names ||
' FROM ' || l_db_object_name ||
' WHERE ' || l_orig_pk_column || ' = ' || p_parent_obj_pk1alias_tbl(l_table_index)
, 1, g_pred_buf_size);
' UNION ALL ( SELECT '
||l_pk_orig_column_names || ' FROM ' ||
l_db_object_name || ' WHERE '
|| l_set_predicates || '))',
1, g_pred_buf_size);
'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE '||l_predicate||' ';
select s.predicate,
o.database_object_name,
o.pk1_column_name,
o.pk2_column_name,
o.pk3_column_name,
o.pk4_column_name,
o.pk5_column_name,
o.pk1_column_type,
o.pk2_column_type,
o.pk3_column_type,
o.pk4_column_type,
o.pk5_column_type
into l_predicate,
l_object_name,
l_pk1_col_name,
l_pk2_col_name,
l_pk3_col_name,
l_pk4_col_name,
l_pk5_col_name,
l_pk1_col_type,
l_pk2_col_type,
l_pk3_col_type,
l_pk4_col_type,
l_pk5_col_type
from fnd_objects o, fnd_object_instance_sets s
where s.instance_set_name = p_instance_set_name
and s.object_id = o.object_id;
'SELECT ''X'' FROM sys.dual WHERE EXISTS (' ||
'SELECT ''X'''||
' FROM '|| l_object_name ||
' WHERE ';
INSERT INTO EGO_OBJ_ROLE_MAPPINGS
(
PARENT_OBJECT_ID
, PARENT_ROLE_ID
, CHILD_OBJECT_ID
, CHILD_OBJECT_TYPE
, CHILD_ROLE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
)
VALUES
(
l_parent_obj_id
, l_parent_role_id
, l_child_obj_id
, p_child_object_type
, l_child_role_id
, l_Sysdate
, l_owner
, l_Sysdate
, l_owner
, FND_GLOBAL.Login_id
);
PROCEDURE Update_Role_Mapping(
p_api_version IN NUMBER
,p_parent_obj_name IN VARCHAR2
,p_parent_role_name IN VARCHAR2
,p_child_obj_name IN VARCHAR2
,p_child_object_type IN VARCHAR2
,p_child_role_name IN VARCHAR2
,p_owner IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Role_Mapping';
SAVEPOINT Update_Role_Mapping_PUB;
UPDATE EGO_OBJ_ROLE_MAPPINGS
SET
CHILD_ROLE_ID = l_child_role_id
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = l_owner
, LAST_UPDATE_LOGIN = l_curr_login_id
WHERE
PARENT_OBJECT_ID = l_parent_obj_id AND
PARENT_ROLE_ID = l_parent_role_id AND
CHILD_OBJECT_ID = l_child_obj_id AND
CHILD_OBJECT_TYPE = p_child_object_type;
UPDATE EGO_OBJ_ROLE_MAPPINGS
SET
CHILD_ROLE_ID = l_child_role_id
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = l_owner
, LAST_UPDATE_LOGIN = l_curr_login_id
WHERE
PARENT_OBJECT_ID = l_parent_obj_id AND
PARENT_ROLE_ID = l_parent_role_id AND
CHILD_OBJECT_ID = l_child_obj_id AND
CHILD_OBJECT_TYPE is null;
,p_module => 'Update_Role_Mapping '
,p_message => 'Returning 13 with params '||
' x_return_status: '|| x_return_status||
' - x_errorcode: '||x_errorcode||
' - x_msg_count: '||x_msg_count||
' - x_msg_data: '||x_msg_data
);
ROLLBACK TO Update_Role_Mapping_PUB;
ROLLBACK TO Update_Role_Mapping_PUB;
ROLLBACK TO Update_Role_Mapping_PUB;
END Update_Role_Mapping;
PROCEDURE Update_Role_Mapping(
p_api_version IN NUMBER
,p_parent_obj_name IN VARCHAR2
,p_parent_role_name IN VARCHAR2
,p_child_obj_name IN VARCHAR2
,p_child_object_type IN VARCHAR2
,p_child_role_name IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
SetGlobals();
,p_module => 'Update_Role_Mapping'
,p_message => 'Started with 12 params '||
' p_api_version: '|| p_api_version||
' - p_parent_obj_name: '||p_parent_obj_name||
' - p_parent_role_name: '||p_parent_role_name||
' - p_child_obj_name: '||p_child_obj_name||
' - p_child_object_type: '||p_child_object_type||
' - p_child_role_name: '||p_child_role_name||
' - p_init_msg_list: '||p_init_msg_list||
' - p_commit: '||p_commit
);
Update_Role_Mapping(
p_api_version => p_api_version
,p_parent_obj_name => p_parent_obj_name
,p_parent_role_name => p_parent_role_name
,p_child_obj_name => p_child_obj_name
,p_child_object_type => p_child_object_type
,p_child_role_name => p_child_role_name
,p_owner => NULL
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,p_module => 'Update_Role_Mapping'
,p_message => 'Returning 12 with params '||
' x_return_status: '|| x_return_status||
' - x_errorcode: '||x_errorcode||
' - x_msg_count: '||x_msg_count||
' - x_msg_data: '||x_msg_data
);
END Update_Role_Mapping;
PROCEDURE Delete_Role_Mapping(
p_api_version IN NUMBER
,p_parent_obj_name IN VARCHAR2
,p_parent_role_name IN VARCHAR2
,p_child_obj_name IN VARCHAR2
,p_child_object_type IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Role_Mapping';
SAVEPOINT Delete_Role_Mapping_PUB;
DELETE FROM EGO_OBJ_ROLE_MAPPINGS
WHERE
PARENT_OBJECT_ID = l_parent_obj_id AND
PARENT_ROLE_ID = l_parent_role_id AND
CHILD_OBJECT_ID = l_child_obj_id AND
CHILD_OBJECT_TYPE = p_child_object_type;
DELETE FROM EGO_OBJ_ROLE_MAPPINGS
WHERE
PARENT_OBJECT_ID = l_parent_obj_id AND
PARENT_ROLE_ID = l_parent_role_id AND
CHILD_OBJECT_ID = l_child_obj_id AND
CHILD_OBJECT_TYPE is null;
ROLLBACK TO Delete_Role_Mapping_PUB;
ROLLBACK TO Delete_Role_Mapping_PUB;
ROLLBACK TO Delete_Role_Mapping_PUB;
END Delete_Role_Mapping;
SELECT functions.function_name
FROM fnd_form_functions functions,
fnd_menu_entries cmf,
fnd_menus menus
WHERE functions.function_id = cmf.function_id
AND menus.menu_id = cmf.menu_id
AND menus.menu_name = cp_role_name;
l_select_query_part VARCHAR2(3000);
/*l_select_query_part:= 'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE ('; */
l_select_query_part:= 'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||', ego_item_cat_denorm_hier cathier'||
' WHERE (';
l_select_query_part:= 'SELECT '|| l_pk_column_names ||
' FROM '|| l_db_object_name ||
' WHERE (';
l_select_query_part := l_select_query_part || l_db_pk1_column;
l_select_query_part := l_select_query_part || ',' || l_db_pk2_column;
l_select_query_part := l_select_query_part || ',' || l_db_pk3_column;
l_select_query_part := l_select_query_part || ',' || l_db_pk4_column;
l_select_query_part := l_select_query_part || ',' || l_db_pk5_column;
l_select_query_part := l_select_query_part || ') ';
l_select_query_part := l_select_query_part ||' IN ' ||
' (' || p_parent_object_sql_tbl (l_table_index) || ') ';
/*l_select_query_part := l_select_query_part || ' AND ';*/
l_select_query_part := l_select_query_part || ' AND item_catalog_group_id = cathier.child_catalog_group_id(+) AND ';
l_select_query_part := l_select_query_part || ' AND ';
,p_message => l_select_query_part
);
' SELECT DISTINCT sets.instance_set_id, sets.predicate ' ||
' FROM fnd_grants grants, fnd_object_instance_sets sets' ||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date, SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR (grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND sets.instance_set_id = grants.instance_set_id ' ||
' AND sets.object_id = grants.object_id ';
l_dynamic_sql := l_select_query_part ||
' (' || l_one_set_predicate || ') ';
'SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_grants grants, ' ||
' fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf, '||
' ego_obj_role_mappings mapping '||
' WHERE grants.object_id = :object_id '||
' AND grants.start_date <= SYSDATE '||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR (grants.grantee_type = ''GROUP'' AND ' ||
' grants.grantee_key in ( '||l_group_info||' )) ' ||
' OR (grants.grantee_type = ''COMPANY'' AND ' ||
' grants.grantee_key in ( '||l_company_info||' )) ' ||
' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))'||
' AND mapping.child_role_id = cmf.menu_id ' ||
' AND mapping.parent_role_id = grants.menu_id ' ||
' AND mapping.child_object_id = :child_object_id ' ||
' AND mapping.parent_object_id = :parent_object_id ' ||
' AND mapping.child_object_type = :object_type ' ||
' AND cmf.function_id = fnd_functions.function_id ';
' SELECT DISTINCT fnd_functions.function_name ' ||
' FROM fnd_form_functions fnd_functions, ' ||
' fnd_menu_entries cmf, ' ||
' ego_obj_role_mappings mapping, ' ||
' fnd_menus menus ' ||
' WHERE menus.menu_name = :profile_role ' ||
' AND mapping.parent_role_id = menus.menu_id ' ||
' AND mapping.child_role_id = cmf.menu_id ' ||
' AND mapping.child_object_id = :profile_object_id ' ||
' AND mapping.parent_object_id = :profile_parent_object_id ' ||
' AND mapping.child_object_type = :profile_object_type ' ||
' AND cmf.function_id = fnd_functions.function_id ';
select p.parent_role_id parent_role_id
from fnd_menu_entries r, fnd_form_functions f,
fnd_menus m, ego_obj_role_mappings p
where r.function_id = f.function_id
and r.menu_id = m.menu_id
and f.function_name = cp_function
and m.menu_id = p.child_role_id
and p.child_object_id = cp_object_id
and p.parent_object_id = cp_parent_object_id
and p.child_object_type = cp_object_type;
'SELECT ''X'' ' ||
' FROM fnd_grants grants ' ||
' WHERE grants.object_id = :object_id ' ||
' AND grants.start_date <= SYSDATE '||
' AND NVL(grants.end_date, SYSDATE) >= SYSDATE ' ||
' AND grants.instance_type = :instance_type ' ||
' AND ' || l_pk_values_string ||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id IN (' || l_menu_info ||') ';
' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = :instance_type '||
' AND grants.start_date <= SYSDATE ' ||
' AND (grants.end_date IS NULL OR grants.end_date >= SYSDATE) ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :parent_object_id '||
' AND ( ( grants.grantee_type = ''USER'' AND ' ||
' grants.grantee_key = :user_name ) '||
' OR ( grants.grantee_type = ''GROUP'' AND '||
' grants.grantee_key in ( '||l_group_info||' ))' ||
' OR ( grants.grantee_type = ''COMPANY'' AND '||
' grants.grantee_key in ( '||l_company_info||' ))' ||
' OR (grants.grantee_type = ''GLOBAL'' AND '||
' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') )) '||
' AND grants.menu_id in (' || l_menu_info || ')';
'SELECT ''X'' '||
' FROM '|| l_db_object_name ||
' WHERE (';