[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.
*
*/
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 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 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 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;