[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT employee_id, person_party_id -- Bug 4527617. Replaced customer_id with person_party_id.
INTO l_emp_id, l_cust_id
FROM fnd_user
WHERE user_id=G_user_id;
select /*+ leading(PER) index(PER PER_PEOPLE_F_PK) */ wfr.name, per.party_id
from per_all_people_f per,
wf_roles wfr
where per.person_id = c_emp_id
and per.party_id = wfr.orig_system_id /* Added outer join for bug 3417803 */
and wfr.orig_system = 'HZ_PARTY' /* Added outer join for bug 3417803 */
and rownum = 1;
select name
from wf_roles
where orig_system_id = c_cust_id
and orig_system = 'HZ_PARTY'
and rownum = 1;
SELECT per.party_id
FROM per_all_people_f per
WHERE per.person_id = c_emp_id;
SELECT employee_id, person_party_id -- Bug 4527617. Replaced customer_id with person_party_id.
INTO l_emp_id, l_cust_id
FROM fnd_user
WHERE user_id=p_source_id;
select project_system_status_code into G_project_system_status_code
from pa_projects_all ppa,
pa_project_statuses pps
where ppa.project_status_code = pps.project_status_code
and ppa.project_id = p_object_key;
if pa_security.allow_update (p_object_key)<>'Y' then
x_ret_code:=fnd_api.g_false;
select 'Y'
from fnd_user users,
pa_project_parties ppp,
pa_project_role_types roletypes
where decode (p_source_type, 'PERSON', users.employee_id,
'HZ_PARTY', users.customer_id)
= ppp.resource_source_id
and ppp.resource_type_id= decode(p_source_type, 'PERSON', 101,
'HZ_PARTY', 112,
111)
and ppp.project_role_id=roletypes.project_role_id
and users.user_id =p_user_id
and ppp.object_id=p_object_key
and ppp.object_type=p_object_name
and roletypes.menu_id is not null
and ROWNUM=1;
select 'Y'
from fnd_user users,
pa_project_parties ppp,
--pa_project_role_types roletypes --bug 4004821
pa_project_role_types_b roletypes
where decode (p_source_type, 'PERSON', users.employee_id,
'HZ_PARTY', users.person_party_id) -- Bug 4527617. Replaced customer_id with person_party_id.
= ppp.resource_source_id
and ppp.resource_type_id= decode(p_source_type,'PERSON',101,
'HZ_PARTY', 112,
111)
and ppp.project_role_id=roletypes.project_role_id
and users.user_id =p_user_id
and ppp.object_id=p_object_key
and ppp.object_type=p_object_name
and roletypes.menu_id is null
and roletypes.role_party_class = 'PERSON' --bug 4004821
and ROWNUM=1;
select 'Y'
from fnd_user users,
pa_project_parties ppp
where decode (p_source_type, 'PERSON', users.employee_id,
'HZ_PARTY', users.person_party_id) -- Bug 4527617. Replaced customer_id with person_party_id.
= ppp.resource_source_id
and ppp.resource_type_id= decode(p_source_type,'PERSON',101,
'HZ_PARTY', 112,
111)
and users.user_id =p_user_id
and ppp.object_id=p_object_key
and ppp.object_type=p_object_name
and ROWNUM=1;
/* select 'Y'
from dual
where not exists
(select 'Y'
from fnd_grants fg,
fnd_objects obj
where fg.object_id=obj.object_id
and obj.obj_name=p_object_name
and fg.grantee_type='USER'
and fg.grantee_key='PER:'||to_char(get_party_id)
and fg.instance_type='INSTANCE'
and fg.instance_pk1_value=p_object_key);
SELECT person_id INTO ret
FROM pa_resource_txn_attributes
WHERE resource_id = p_resource_id
AND person_id IS NOT NULL;
SELECT organization_id
FROM per_all_assignments_f -- Bug 4359282: Changed from per_assignments_f to all
WHERE person_id=v_person_id
AND TRUNC(effective_start_date)<=TRUNC(NVL(p_start_date, effective_start_date))
AND TRUNC(NVL(p_start_date,SYSDATE))<=TRUNC(effective_end_date)
AND primary_flag='Y'
AND Assignment_type in ('E', 'C')
ORDER BY effective_start_date;
SELECT RESOURCE_ORGANIZATION_ID
FROM pa_resources_denorm
WHERE person_id = v_person_id
AND TRUNC(resource_effective_start_date)<=TRUNC(NVL(p_start_date, resource_effective_start_date))
AND TRUNC(NVL(p_start_date,SYSDATE))<=TRUNC(resource_effective_end_date);
SELECT nvl(future_term_wf_flag,'N')
INTO l_future_term_wf_flag
FROM pa_resources
WHERE resource_id = p_resource_id;
select CARRYING_OUT_ORGANIZATION_ID
into x_project_org_id
from pa_projects_all
where project_id=p_project_id;
SELECT 'Y'
INTO l_is_manager
FROM dual
WHERE p_manager_id IN ( SELECT Manager_id
FROM pa_resources_denorm
WHERE nvl(p_start_date, trunc(sysdate)) BETWEEN resource_effective_start_date
AND resource_effective_end_date
AND manager_id is not null
START WITH person_id = p_person_id
CONNECT BY PRIOR manager_id = person_id
AND manager_id <> prior person_id
AND nvl(p_start_date, trunc(sysdate)) BETWEEN resource_effective_start_date
AND resource_effective_end_date);
SELECT manager_id INTO l_mgr_id
FROM pa_resources_denorm WHERE
person_id = l_person_id
AND nvl(p_start_date, trunc(sysdate))
BETWEEN resource_effective_start_date AND resource_effective_end_date;
select object_id
into l_object_id
from fnd_objects
where obj_name=p_object_name;
SELECT grant_guid
INTO l_grant_guid
FROM fnd_grants
WHERE grantee_type='USER' AND
grantee_key=get_grantee_key(p_source_type, p_party_id) AND
menu_id=l_menu_id AND
object_id=l_object_id AND
instance_type=l_object_key_type AND
((l_object_key_type='INSTANCE' AND
instance_pk1_value=TO_CHAR(p_object_key)) OR
(l_object_key_type='SET' AND
instance_set_id=p_object_key));
PROCEDURE update_role
( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_debug_mode in varchar2 default 'N',
p_grant_guid in raw,
p_project_role_id_old IN number default null,
p_object_name_old IN VARCHAR2 default null,
p_object_key_type_old IN VARCHAR2 default null,
p_object_key_old IN NUMBER default null,
p_party_id_old IN NUMBER default null,
p_source_type_old in varchar2 default null,
p_start_date_old IN DATE default null,
p_start_date_new IN DATE default null,
p_end_date_new IN DATE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) is
l_success varchar2(1);
fnd_grants_pkg.update_grant
(
p_api_version =>l_api_version,
p_grant_guid =>p_grant_guid,
p_start_date =>p_start_date_new,
p_end_date =>p_end_date_new,
x_success =>l_success
) ;
select object_id
into l_object_id
from fnd_objects
where obj_name=p_object_name;
select instance_set_id into l_set_id
from fnd_object_instance_sets
where instance_set_name=p_set_name;
select menu.menu_name
into v_menu_name
from fnd_menus menu,
--pa_project_role_types role
pa_project_role_types_b role --Bug 4867700
where menu.menu_id=role.menu_id
and role.project_role_id= p_project_role_id;
select menu.menu_name
into v_menu_name
from fnd_menus menu
where menu.menu_id=p_menu_id;
select menu.menu_name
into v_menu_name
from fnd_menus menu,
pa_project_role_types role
where menu.menu_id=role.menu_id
and role.meaning= p_project_role_name;
select menu_id into v_menu_id
from fnd_menus
where menu_name =p_menu_name;
select menu_id into v_menu_id
--from pa_project_role_types --bug 4004821
from pa_project_role_types_b
where project_role_id =p_project_role_id;
select meaning
into v_role_name
from pa_project_role_types
where project_role_id =p_project_role_id;
SELECT DISTINCT 'Y'
INTO l_dummy
FROM fnd_grants fg ,
fnd_objects fo
WHERE fg.object_id=fo.object_id
AND fo.obj_name = p_object_name
AND fg.INSTANCE_type = l_object_key_type
AND fg.menu_id = p_role_id
AND fg.grantee_type='USER'
AND (p_party_id IS NULL OR fg.grantee_key=l_grantee_key)
AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
and trunc(NVL(fg.END_DATE, SYSDATE+1))
AND ((l_object_key_type='INSTANCE' AND
instance_pk1_value=NVL(p_object_key, instance_pk1_value)) OR
(l_object_key_type='SET' AND
fg.instance_set_id=TO_NUMBER(p_object_key)));
SELECT pk1_column_name
,pk2_column_name
,pk3_column_name
,pk4_column_name
,pk5_column_name
, database_object_name
FROM fnd_objects
WHERE obj_name=p_object_name ;
l_sql := 'SELECT 1 FROM '||l_db_object_name||
' WHERE pa_security_pvt.check_sec_by_resp('||
g_user_id||',''PA_PROJECTS'','''||
g_source_type||''','||l_db_pk1_column||')=''T'''||
' AND pa_security.allow_update('||l_db_pk1_column||')=''Y'''||
' AND ROWNUM=1';
pa_debug.G_err_stage := 'checking allow_update in case of PA_PROJECTS';
l_sql := 'SELECT 1 FROM '||l_db_object_name||
' WHERE pa_security_pvt.check_sec_by_resp('||
g_user_id||','''||p_object_name||''','''||
g_source_type||''','||l_db_pk1_column||')=''T'''||
' AND ROWNUM=1';
l_predicate := 'SELECT 1 FROM '||l_db_object_name||' WHERE '||l_predicate;
select 'T' into l_grant_exists
from fnd_grants
where grantee_key = p_grantee_key
and grantee_type = 'USER'
and instance_set_id = l_instance_set_id
and grantee_type = p_grantee_type
and instance_type = p_instance_type
and parameter1 = to_char(p_project_role_id)
and rownum=1;
PROCEDURE update_menu
( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_debug_mode in varchar2 default 'N',
p_project_role_id IN number,
p_menu_id IN number,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) is
BEGIN
IF G_project_roles_ins_set_id IS NULL THEN
G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
UPDATE fnd_grants
SET menu_id = p_menu_id
WHERE parameter1 = to_char(p_project_role_id)
AND (parameter2 = 'NON_STATUS_BASED' OR parameter2 = 'DEFAULT')
AND instance_type = 'SET'
AND instance_set_id = G_project_roles_ins_set_id;
p_procedure_name => 'UPDATE_MENU',
p_error_text => SQLCODE);
END update_menu;
DELETE FROM fnd_grants
WHERE parameter1 = to_char(p_project_role_id)
AND instance_type = 'SET'
AND instance_set_id = G_project_roles_ins_set_id;
select distinct ppp.resource_type_id,ppp.resource_source_id
-- , wfr.name grantee_key
from pa_project_parties ppp
-- wf_roles wfr
where ppp.project_role_id = p_project_role_id;
select distinct ppp.resource_type_id,ppp.resource_source_id, wfr.name grantee_key
from pa_project_parties ppp,
per_all_people_f per,
wf_roles wfr
where ppp.project_role_id = p_project_role_id
and ppp.resource_type_id = 101
and ppp.resource_source_id = per.person_id
and per.party_id = wfr.orig_system_id
and wfr.orig_system = 'HZ_PARTY';
SELECT wfr.name grantee_key
INTO l_grantee_key
FROM wf_roles wfr
WHERE wfr.orig_system_id = res.resource_source_id
AND wfr.orig_system = 'HZ_PARTY'
AND rownum = 1;
SELECT wfr.name grantee_key
INTO l_grantee_key
FROM per_all_people_f per,
wf_roles wfr
WHERE res.resource_source_id = per.person_id
AND per.party_id = wfr.orig_system_id
AND wfr.orig_system = 'HZ_PARTY'
AND rownum = 1;
DELETE FROM fnd_grants
WHERE parameter1 = to_char(p_project_role_id)
AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
AND instance_type = 'SET'
AND instance_set_id = G_project_roles_ins_set_id;
PROCEDURE update_status_based_sec
( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_debug_mode in varchar2 default 'N',
p_project_role_id IN number,
p_status_level IN pa_project_role_types_b.status_level%TYPE,
p_new_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_new_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_new_menu_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_new_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
p_mod_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_mod_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_mod_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
p_mod_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
p_del_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_del_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
p_del_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) is
cursor get_resources_on_role is
select distinct ppp.resource_type_id,ppp.resource_source_id, wfr.name grantee_key
from pa_project_parties ppp,
wf_roles wfr
where ppp.project_role_id = p_project_role_id
and ppp.resource_type_id = 112
and ppp.resource_source_id = wfr.orig_system_id
and wfr.orig_system = 'HZ_PARTY'
UNION ALL
select distinct ppp.resource_type_id,ppp.resource_source_id, wfr.name grantee_key
from pa_project_parties ppp,
per_all_people_f per,
wf_roles wfr
where ppp.project_role_id = p_project_role_id
and ppp.resource_type_id = 101
and ppp.resource_source_id = per.person_id
and per.party_id = wfr.orig_system_id
and wfr.orig_system = 'HZ_PARTY';
DELETE FROM fnd_grants
WHERE parameter1 = to_char(p_project_role_id)
AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
AND parameter3 = p_del_status_code_tbl(j)
AND parameter4 = p_del_role_sts_menu_id_tbl(j)
AND instance_type = 'SET'
AND instance_set_id = G_project_roles_ins_set_id;
UPDATE fnd_grants
SET parameter2 = p_status_level,
parameter3 = p_mod_status_code_tbl(k),
menu_id = p_mod_menu_id_tbl(k)
WHERE parameter1 = to_char(p_project_role_id)
AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
AND parameter4 = p_mod_role_sts_menu_id_tbl(k)
AND instance_type = 'SET'
AND instance_set_id = G_project_roles_ins_set_id;
select count(role_status_menu_id) into l_status_menu_count
from pa_role_status_menu_map
where role_id = p_project_role_id
and rownum=1;
UPDATE fnd_grants
SET parameter2 = l_parameter2
WHERE parameter1 = to_char(p_project_role_id)
AND parameter2 = l_opp_param2
AND instance_type = 'SET'
AND instance_set_id = G_project_roles_ins_set_id;
p_procedure_name => 'UPDATE_STATUS_BASED_SEC',
p_error_text => SQLCODE);
END update_status_based_sec;
select a.responsibility_id, c.application_id,
b.responsibility_key, c.application_short_name
from fnd_user_resp_groups_all a,
fnd_responsibility_vl b,
fnd_application c
where a.user_id = p_user_id
and sysdate between NVL(a.start_date,sysdate) and NVL(a.end_date,sysdate)
and a.responsibility_id = b.responsibility_id
and a.responsibility_application_id = b.application_id
and b.application_id = c.application_id;