[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* p_operation_code VARCHAR2 e.g SELECT,INSERT etc.
* p_db_object_name VARCHAR2 Object_name in fnd_objects
* e.g.HZ_PARTIES
* p_instance_pk1_value VARCHAR2 e.g Party_id = 1000
* p_user VARCHAR2 e.g JDOE
*
* OUT: T/F
* IN/ OUT:
*
* RETURNS : NONE
*
* NOTES
*
* MODIFICATION HISTORY -
* Jyoti Pandey 08-07-2002 Created.
*
*/
FUNCTION test_instance (
p_operation_code IN VARCHAR2,
p_db_object_name IN VARCHAR2,
p_instance_pk1_value IN VARCHAR2,
p_instance_pk2_value IN VARCHAR2 ,
p_instance_pk3_value IN VARCHAR2 ,
p_instance_pk4_value IN VARCHAR2 ,
p_instance_pk5_value IN VARCHAR2 ,
p_user_name IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_init_msg_list IN VARCHAR2
) RETURN VARCHAR2 IS
CURSOR c_check_user IS
SELECT '1'
FROM fnd_user
WHERE user_name = p_user_name
AND (start_date IS NULL OR start_date <= SYSDATE)
AND (end_date IS NULL OR end_date >= SYSDATE);
SELECT '1'
FROM fnd_objects
WHERE database_object_name = p_db_object_name
AND ROWNUM = 1;
SELECT '1'
FROM ar_lookups lu
WHERE lu.lookup_type = 'HZ_DATA_OPERATIONS'
AND lu.lookup_code = p_operation_code;
SELECT dss.security_scheme_code , func.function_name
FROM hz_dss_scheme_functions dss,
fnd_form_functions func
WHERE dss.data_operation_code = p_operation_code
AND dss.status = 'A'
AND dss.function_id = func.function_id;
* p_operation_code VARCHAR2 e.g. SELECT
*
* OUT: x_granted_groups table of data dharing group , entity_id
* IN/ OUT:
*
* RETURNS : NONE
*
* NOTES
*
* MODIFICATION HISTORY -
* Jyoti Pandey 08-07-2002 Created.
*
*/
PROCEDURE get_granted_groups (
p_user_name IN VARCHAR2,
p_operation_code IN VARCHAR2,
x_granted_groups OUT NOCOPY dss_group_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_menu_id NUMBER;
SELECT distinct menu_id ,dsf.security_scheme_code ,dsf.function_id
FROM hz_dss_scheme_functions dsf, fnd_compiled_menu_functions cmf
WHERE dsf.data_operation_code = t_operation_code
AND dsf.function_id = cmf.function_id
AND dsf.status = 'A';
SELECT instance_set_id
FROM fnd_grants grants
WHERE grants.menu_id= t_menu_id --grant for a menu
AND grants.start_date <= sysdate
AND ( grants.end_date IS NULL
OR grants.end_date >= sysdate )
AND ( ( grants.grantee_type = 'USER' --grantee a user
AND grants.grantee_key = t_user_name)
OR ( grants.grantee_type = 'GROUP' --grantee a group
AND grants.grantee_key in
(select role_name
from wf_user_roles
where user_name = t_user_name))
OR (grants.grantee_type = 'GLOBAL'));
select dss_group_code, entity_id
from hz_dss_secured_entities
where dss_instance_set_id = t_object_instance_set_id
and status = 'A';
select '1'
into l_exists
from fnd_user
where user_name = p_user_name
and ( start_date IS NULL OR start_date <= SYSDATE)
and ( end_date is null or end_date >= sysdate );
select '1'
into l_exists
from ar_lookups lu
where lu.lookup_type = 'HZ_DATA_OPERATIONS'
and lu.lookup_code = p_operation_code;
SELECT 1
FROM hz_dss_criteria
WHERE dss_group_code = p_dss_group_code
AND status = 'A'
AND ROWNUM = 1;
SELECT 1
FROM hz_dss_criteria
WHERE dss_group_code = p_dss_group_code
AND owner_table_name = 'AR_LOOKUPS'
AND owner_table_id1 = 'HZ_CREATED_BY_MODULES'
AND status = 'A'
AND ROWNUM = 1;
SELECT 1
FROM hz_dss_criteria
WHERE dss_group_code = p_dss_group_code
AND owner_table_name = 'FND_LOOKUP_VALUES'
AND status = 'A'
AND ROWNUM = 1;
SELECT 1
FROM hz_dss_criteria
WHERE dss_group_code = p_dss_group_code
AND owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND status = 'A'
AND ROWNUM = 1;
SELECT obj.object_id, obj.obj_name, null instance_set_id, null instance_set_name,
obj.pk1_column_name, obj.pk2_column_name, null predicate,
dsg.dss_group_code, dsg.rank
FROM hz_dss_entities dse,
hz_dss_secured_entities se,
hz_dss_groups_b dsg,
fnd_objects obj
WHERE obj.database_object_name = p_db_object_name
AND dse.object_id IS NOT NULL
AND dse.object_id = obj.object_id
AND dse.entity_id = se.entity_id
AND se.dss_group_code = dsg.dss_group_code
AND se.status = 'A'
AND dsg.status = 'A'
AND dse.status = 'A'
UNION ALL
SELECT obj.object_id, obj.obj_name, ins.instance_set_id, ins.instance_set_name,
obj.pk1_column_name, obj.pk2_column_name, ins.predicate,
dsg.dss_group_code, dsg.rank
FROM hz_dss_entities dse,
hz_dss_secured_entities se,
hz_dss_groups_b dsg,
fnd_objects obj,
fnd_object_instance_sets ins
WHERE obj.database_object_name = p_db_object_name
AND dse.object_id IS NULL
AND dse.instance_set_id = ins.instance_set_id
AND ins.object_id = obj.object_id
AND dse.entity_id = se.entity_id
AND se.dss_group_code = dsg.dss_group_code
AND se.status = 'A'
AND dsg.status = 'A'
AND dse.status = 'A'
ORDER BY rank;
l_sql := 'SELECT 1 FROM '||l_db_object_name||' '||
'WHERE '||l_pk1_column_name||' = :1'||' '||
'AND '||l_predicate;
SELECT dse.entity_id, fo.object_id,
fo.pk1_column_name, fo.pk2_column_name ,
dse.parent_fk_column1 , dse.parent_fk_column2,
dse.group_assignment_level
FROM fnd_objects fo , hz_dss_entities dse
WHERE ( ( dse.object_id IN ( select object_id from fnd_objects
where database_object_name = t_object_name) )
OR
(dse.instance_set_id in ( select instance_set_id
from fnd_object_instance_sets ois
where ois.object_id IN
(select object_id from fnd_objects
where database_object_name = t_object_name)
)
)
)
AND fo.object_id = dse.object_id
AND dse.status = 'A';
l_sql := ' UPDATE ' || p_db_object_name ||
' SET ' || ' dss_group_code ' || ' = :dsg ' ||
' WHERE ' || l_pk1_column_name|| ' = :pk ' ;
SELECT entity_id,
fo.database_object_name,
fo.pk1_column_name,
fo.pk2_column_name ,
dse.parent_entity_id , dse.parent_fk_column1 , dse.parent_fk_column2
FROM fnd_objects fo , hz_dss_entities dse
WHERE parent_entity_id is not null
AND ( dse.object_id is not null and
fo.object_id = dse.object_id )
OR (dse.instance_set_id is not null and
fo.object_id = ( select distinct object_id from fnd_object_instance_sets
where instance_set_id = dse.instance_set_id))
AND dse.parent_entity_id = t_entity_id
AND dse.status = 'A'
order by dse.entity_id;
'SELECT ' || l_pk1_column_name ||' , '||
nvl(l_pk2_column_name,-1) ||
' FROM ' || l_database_object_name||
' WHERE '|| l_parent_fk_column1 || '= :id1 ' ||
' AND ' || l_parent_fk_column2 || '= :id2 ' USING p_object_pk1 , p_object_pk2;
'SELECT ' || l_pk1_column_name || ',' ||
nvl(l_pk2_column_name ,-1) ||
' FROM ' || l_database_object_name||
' WHERE '|| l_parent_fk_column1 || '= :id1 ' USING p_object_pk1;
| with the Data Sharing Groups that the user has SELECT access to
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS :
|
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY -
| Jyoti Pandey 08-07-2002 Created.
|
+===========================================================================*/
procedure switch_context (p_user_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)IS
x_granted_groups HZ_DSS_UTIL_PUB.dss_group_tbl_type;
'SELECT',
x_granted_groups,
l_return_status,
l_msg_count,
l_msg_data);
SELECT obj.database_object_name ,
decode(pk1_column_name , null,null, pk1_column_name) ||
decode(pk2_column_name , null,null, ','|| pk2_column_name) ||
decode(pk3_column_name , null,null, ','|| pk3_column_name) ||
decode(pk4_column_name , null,null, ','|| pk4_column_name) ||
decode(pk5_column_name , null,null, ','|| pk5_column_name)
FROM fnd_objects obj,
hz_dss_entities dse
WHERE dse.entity_id = p_entity_id
AND dse.status = 'A'
AND dse.object_id IS NOT NULL
AND dse.object_id = obj.object_id
UNION ALL
SELECT obj.database_object_name ,
decode(pk1_column_name , null,null, pk1_column_name) ||
decode(pk2_column_name , null,null, ','|| pk2_column_name) ||
decode(pk3_column_name , null,null, ','|| pk3_column_name) ||
decode(pk4_column_name , null,null, ','|| pk4_column_name) ||
decode(pk5_column_name , null,null, ','|| pk5_column_name)
FROM fnd_object_instance_sets ins,
fnd_objects obj,
hz_dss_entities dse
WHERE dse.entity_id = p_entity_id
AND dse.status = 'A'
AND dse.instance_set_id IS NOT NULL
AND dse.instance_set_id = ins.instance_set_id
AND ins.object_id = obj.object_id;
SELECT 1
FROM hz_parties p, hz_dss_criteria dc
WHERE p.party_id = p_party_id
AND p.created_by_module IN (
SELECT owner_table_id2
FROM hz_dss_criteria
WHERE dss_group_code = p_dss_group_code
AND owner_table_name = 'AR_LOOKUPS'
AND owner_table_id1 = 'HZ_CREATED_BY_MODULES'
AND status = 'A');
SELECT 1
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND class_category = p_class_category
AND class_code = p_class_code
AND SYSDATE BETWEEN
NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)
AND status = 'A';
SELECT dsc.owner_table_id1 , dsc.owner_table_id2
FROM hz_dss_criteria dsc
WHERE dsc.dss_group_code = p_dss_group_code
AND owner_table_name = 'FND_LOOKUP_VALUES'
AND status = 'A';
SELECT class_code
FROM hz_class_code_denorm ccd
WHERE ccd.class_category = p_class_category
AND INSTRB('/'||concat_class_code||'/','/'||p_class_code||'/') > 0
AND LANGUAGE = userenv('LANG')
ORDER BY concat_class_code; -- Bug 7290836(no need to fetch more rec if
For example, if 1 is updateable while other is not,
without relationship id filter, behaviour is random and
either both becomes updateable or both becomes non-updateable.
25-Mar-2010 Sudhir Gokavarapu Bug#8837776 FP for Bug 8797990
Changes made for bug 5687869 is causing regression in other
entity checks (other than Relationship). If any 'Relationship
Role' criteria is met, all the entities which are marked to be
secured should be secured. Securing only 1 relationship and
leaving rest open is not the design of DSS.
Additionally, enhanced the support for securing entities
hanging from Relationship Party (like Contact Point for Org Contacts)
Since relationship party cannot have 'Relationship Criteria'
attached to it. So, there is no way to secure them. In that case,
check if parties forming the relationship meets the "Relationship
Security Criteria".
*
*/
FUNCTION check_relationship_types (
p_dss_group_code IN VARCHAR2,
p_party_id IN NUMBER
--,p_relationship_id IN NUMBER -- Bug 5687869 (Nishant)
) RETURN VARCHAR2 IS
CURSOR c_check_relationship_types_p (
p_party_id NUMBER,
p_relationship_type_id NUMBER
--,p_relationship_id NUMBER -- Bug 5687869 (Nishant)
) IS
-- SELECT 1
/*SELECT rel.relationship_type -- changed so that it is easy to debug
FROM hz_relationships rel
WHERE rel.subject_id = p_party_id AND
rel.subject_table_name = 'HZ_PARTIES'
AND rel.relationship_id = p_relationship_id -- added for Bug 5687869
AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
SELECT relationship_type, forward_rel_code,
subject_type, object_type
FROM hz_relationship_types rt
WHERE rt.relationship_type_id = p_relationship_type_id)
AND SYSDATE BETWEEN
NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE)
AND status = 'A'
AND ROWNUM = 1;
SELECT rel.relationship_code -- changed so that it is easy to debug
FROM hz_relationships rel
WHERE p_party_id IN (rel.subject_id, rel.object_id)
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.directional_flag = 'F'
AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
SELECT relationship_type, forward_rel_code,
subject_type, object_type
FROM hz_relationship_types rt
WHERE rt.relationship_type_id = p_relationship_type_id)
AND SYSDATE BETWEEN
NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
AND rel.status = 'A'
AND ROWNUM = 1;
SELECT owner_table_id1
FROM hz_dss_criteria dsc
WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND dsc.dss_group_code = p_dss_group_code
AND dsc.status = 'A';
SELECT subject_id, object_id
FROM hz_relationships rel
WHERE rel.party_id = p_party_id
AND rel.directional_flag = 'F'
AND SYSDATE BETWEEN
NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
AND rel.status = 'A'
AND ROWNUM = 1;
SELECT rel.relationship_type
FROM hz_relationships rel
WHERE rel.party_id = p_party_id
AND rel.relationship_id = p_object_pk1 --(Bug 5687869)
AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
SELECT relationship_type, forward_rel_code,
subject_type, object_type
FROM hz_relationship_types rt, hz_dss_criteria dsc
WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND dsc.dss_group_code = p_dss_group_code
AND dsc.owner_table_id1 = rt.relationship_type_id
AND dsc.status = 'A' )
AND SYSDATE BETWEEN
NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE)
AND status = 'A';
SELECT rel.relationship_code -- changed so that it is easy to debug
FROM hz_relationships rel
WHERE p_subj_id IN (rel.subject_id, rel.object_id)
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.directional_flag = 'F'
AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
SELECT relationship_type, forward_rel_code,
subject_type, object_type
FROM hz_relationship_types rt, hz_dss_criteria dsc
WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND dsc.dss_group_code = p_dss_group_code
AND dsc.owner_table_id1 = rt.relationship_type_id
AND dsc.status = 'A')
AND SYSDATE BETWEEN
NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
AND rel.status = 'A'
AND ROWNUM = 1
UNION
SELECT rel.relationship_code -- changed so that it is easy to debug
FROM hz_relationships rel
WHERE p_obj_id IN (rel.subject_id, rel.object_id)
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.directional_flag = 'F'
AND (rel.relationship_type, rel.relationship_code, rel.subject_type, rel.object_type) IN (
SELECT relationship_type, forward_rel_code,
subject_type, object_type
FROM hz_relationship_types rt, hz_dss_criteria dsc
WHERE dsc.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND dsc.dss_group_code = p_dss_group_code
AND dsc.owner_table_id1 = rt.relationship_type_id
AND dsc.status = 'A')
AND SYSDATE BETWEEN
NVL(rel.start_date, SYSDATE) AND NVL(rel.end_date, SYSDATE)
AND rel.status = 'A'
AND ROWNUM = 1;
SELECT party_type
FROM hz_parties
WHERE party_id = p_party_id;
SELECT relationship_id
FROM hz_relationships
WHERE party_id = p_party_id
AND directional_flag = 'F';
SELECT party_id, party_type
FROM hz_parties
WHERE party_id = p_party_id;
SELECT p.party_id, p.party_type
FROM hz_party_sites ps, hz_parties p
WHERE party_site_id = p_party_site_id
AND ps.party_id = p.party_id;
SELECT p.party_id, p.party_type
FROM hz_locations loc, hz_party_sites ps, hz_parties p
WHERE loc.location_id = p_location_id
AND loc.location_id = ps.location_id
AND ps.party_id = p.party_id;
SELECT p.party_id, p.party_type
FROM hz_code_assignments, hz_parties p
WHERE code_assignment_id = p_code_assignment_id
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p.party_id;
SELECT subject_id, subject_table_name, subject_type,
object_id, object_table_name, object_type
FROM hz_relationships
WHERE relationship_id = p_relationship_id
AND directional_flag = p_directional_flag
AND (subject_table_name = 'HZ_PARTIES' OR
object_table_name = 'HZ_PARTIES');
SELECT p.party_id, p.party_type
FROM hz_contact_points, hz_parties p
WHERE contact_point_id = p_contact_point_id
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p.party_id;
SELECT p.party_id, p.party_type
FROM hz_contact_points cp, hz_party_sites ps, hz_parties p
WHERE contact_point_id = p_contact_point_id
AND owner_table_name = 'HZ_PARTY_SITES'
AND owner_table_id = ps.party_site_id
AND ps.party_id = p.party_id;
x_party_id_tbl.DELETE(2);
x_party_type_tbl.DELETE(2);
SELECT display_name
FROM fnd_objects_vl
WHERE obj_name = p_object_name;
SELECT display_name
FROM fnd_object_instance_sets_vl
WHERE instance_set_name = p_object_instance_name;