[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select assignment_id,position_id
into p_assignment_id, p_position_id
from per_all_assignments_f
where person_id = p_person_id
and primary_flag ='Y'
AND assignment_type = 'E' -- bug 7330323
and p_effective_date between effective_start_date and effective_end_date;
select user_id,employee_id
into l_user_id,l_employee_id
from fnd_user
where user_name = p_user_name;
select user_name,employee_id
into l_user_name,l_employee_id
from fnd_user
where user_id = p_user_id;
select freeze_status_cd,setup_type_cd
into l_freeze_status_cd,l_setup_type_cd
from pqh_transaction_categories
where transaction_category_id = p_tran_cat_id
and nvl(enable_flag,'Y') = 'Y';
select 'TRUE' into l_return
from per_pos_structure_elements
where subordinate_position_id = p_position_id
and pos_structure_version_id = p_pos_str_ver_id;
select 'TRUE' into l_return
from per_pos_structure_elements
where parent_position_id = p_position_id
and pos_structure_version_id = p_pos_str_ver_id;
cursor c1 is select name
from pqh_transaction_categories
where transaction_category_id = p_transaction_category_id;
cursor c1 is select role_name
from pqh_roles
where role_id = p_role_id;
select decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0) role_id
from per_people_extra_info pei , per_all_people_f ppf, fnd_user usr
WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
and p_session_date between ppf.effective_start_date and ppf.effective_end_date
and usr.employee_id = ppf.person_id
and nvl(pei.pei_information5,'Y')='Y'
and nvl(pei.pei_information4,'N')='Y'
and usr.user_id = p_user_id;
select employee_id
from fnd_user
where user_id = p_user_id;
select member_cd, workflow_enable_flag
from pqh_transaction_categories tct
where transaction_category_id = p_transaction_category_id;
select asg.assignment_id,asg.position_id
from per_all_assignments_f asg
, fnd_user fu
where asg.person_id = fu.employee_id
and fu.user_id = p_user_id
and asg.primary_flag = 'Y'
AND asg.assignment_type = 'E' -- Bug 7422915
and p_session_date between asg.effective_start_date and asg.effective_end_date;
select decode(information_type, 'PQH_POS_ROLE_ID' , to_number(poei_information3), -1) ROLE_ID
from per_position_extra_info pei, hr_all_positions_f pos, per_all_assignments_f asg
where pei.position_id=pos.position_id and pei.information_type='PQH_POS_ROLE_ID'
and pos.position_id = asg.position_id
and p_session_date between pos.effective_start_date and pos.effective_end_date
and p_session_date between asg.effective_start_date and asg.effective_end_date
and asg.assignment_id = p_assignment_id;
cursor c1 is select template_id
from pqh_role_templates
where role_id = p_role_id
and transaction_category_id = p_transaction_category_id
and enable_flag = 'Y';
cursor c1 is select role_name from pqh_roles
where role_id = p_role_id
and nvl(enable_flag,'X') = 'Y';
cursor c3 is select 'X'
from per_people_extra_info pei , per_all_people_f ppf, fnd_user usr
WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
and p_session_date between ppf.effective_start_date and ppf.effective_end_date
and usr.employee_id = ppf.person_id
and decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0)= p_role_id
and nvl(pei.pei_information5,'Y')='Y'
and usr.user_id = p_user_id ;
select role_id
from pqh_routing_history rht, pqh_routing_list_members rlm
where rht.forwarded_to_member_id = rlm.routing_list_member_id
and rht.routing_history_id = p_routing_history_id;
select 'X'
from per_people_extra_info pei , per_all_people_f ppf, fnd_user usr
WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and usr.employee_id = ppf.person_id
and decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0)= p_role_id
and nvl(pei.pei_information5,'Y')='Y'
and usr.user_id = p_user_id ;
select employee_id from fnd_user
where user_id = p_user_id;
SELECT decode(information_type, 'PQH_POS_ROLE_ID' , to_number(poei_information3), -1) ROLE_ID
from per_position_extra_info pei
WHERE pei.position_id=p_position_id
and pei.information_type='PQH_POS_ROLE_ID';
select position_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and person_id = l_person_id
and primary_flag = 'Y'
and sysdate between effective_start_date and effective_end_date;
select decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0) role_id
from per_people_extra_info pei , per_all_people_f ppf
WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = p_person_id
and nvl(pei.pei_information5,'Y')='Y'
and nvl(pei.pei_information4,'N')='Y';
select forwarded_to_member_id, forwarded_to_position_id,forwarded_to_assignment_id
into l_rht_member_id,l_rht_position_id,l_rht_assignment_id
from pqh_routing_history
where routing_history_id = p_routing_history_id;
select transaction_category_id
into l_local_txncat_id
from pqh_transaction_categories
where business_group_id = nvl(p_business_group_id,-1)
and short_name = p_short_name
and nvl(enable_flag,'Y') = 'Y';
select transaction_category_id
into l_global_txncat_id
from pqh_transaction_categories
where business_group_id is null
and short_name = p_short_name;
procedure insert_rout_crit(p_attribute_id in number,
p_used_for in varchar default null,
p_rule_name in varchar default null,
p_attribute_type in varchar default null,
p_from_char in varchar default null,
p_to_char in varchar default null,
p_from_num in number default null,
p_to_num in number default null,
p_from_date in date default null,
p_to_date in date default null,
p_value_char in varchar default null,
p_value_num in number default null,
p_value_date in date default null) is
l_proc varchar2(81) := g_package||'insert_rout_crit';
end insert_rout_crit;
procedure delete_rout_crit(p_used_for in varchar,
p_rule_name in varchar) is
l_proc varchar2(81) := g_package||'delete_rout_crit1';
g_routing_criterion.delete(i);
hr_utility.set_location('row deleted'||l_proc,10);
hr_utility.set_location('error in delete_routing criterion'||l_proc,140);
end delete_rout_crit;
procedure delete_rout_crit(p_used_for in varchar) is
l_proc varchar2(81) := g_package||'delete_rout_crit';
g_routing_criterion.delete(i);
hr_utility.set_location('row deleted'||l_proc,10);
hr_utility.set_location('error in delete_routing criterion'||l_proc,140);
end delete_rout_crit;
cursor c1 is select role_id,user_id
from pqh_routing_list_members
where routing_list_member_id = p_member_id ;
cursor c1 is select override_role_id,override_user_id,override_position_id,override_assignment_id
from pqh_routing_categories
where routing_category_id = p_routing_category_id
and nvl(enable_flag,'X') = 'Y'
and nvl(delete_flag,'N') = 'N';
cursor c1 is select forwarded_by_position_id,forwarded_by_assignment_id,forwarded_by_user_id,forwarded_by_role_id,
forwarded_by_member_id,routing_category_id
from pqh_routing_history
where routing_history_id = l_max_routing_history_id;
cursor c2 is select rlm.role_id
from pqh_routing_categories rc,pqh_routing_list_members rlm
where rc.routing_category_id = l_rh_routing_category_id
and rc.routing_list_id = rlm.routing_list_id
and rlm.routing_list_member_id = l_member_id
and nvl(rc.enable_flag,'X') = 'Y'
and nvl(rlm.enable_flag,'X') = 'Y'
and nvl(rc.delete_flag,'X') <> 'Y';
select max(routing_history_id)
into l_max_routing_history_id
from pqh_routing_history
where transaction_category_id = p_transaction_category_id
and transaction_id = p_transaction_id
and approval_cd ='APPROVED' ;
hr_utility.set_location('From_clause selected '||l_proc,40);
hr_utility.set_location('CATg selected'||to_char(l_routing_category_id)||l_range_name||l_proc,50);
hr_utility.set_location('selected routing catg was used for approval'||l_proc,52);
hr_utility.set_location('errors in selecting routing_category'||l_proc,60);
hr_utility.set_location('from clause selected '||l_proc,30);
hr_utility.set_location('from clause selected '||l_proc,30);
hr_utility.set_location('from clause selected '||l_proc,30);
cursor c1 is select person_id
from per_all_assignments_f
where position_id = p_position_id
and primary_flag = 'Y'
and p_value_date between effective_start_date and effective_end_date;
cursor c1 is select user_id
from fnd_user
where employee_id = p_person_id
and p_value_date between nvl(start_date,p_value_date)
and nvl(end_date,p_value_date);
hr_utility.set_location('From_clause selected '||l_proc,60);
hr_utility.set_location('CATg selected'||to_char(p_routing_category_id)||p_range_name||l_proc,70);
hr_utility.set_location('errors in selecting routing_category'||l_proc,70);
cursor c1 is select person_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_value_date between effective_start_date and effective_end_date;
hr_utility.set_location('user selected, checking applicability'||l_proc,110);
hr_utility.set_location('user selected '||to_char(p_position_id)||l_proc,90);
hr_utility.set_location('next assignment selected, checking '||l_proc,80);
cursor c0 is select wks.budget_id
from pqh_worksheets wks, pqh_worksheet_details wdt
where wdt.worksheet_detail_id = p_transaction_id
and wdt.worksheet_id = wks.worksheet_id
and wks.wf_transaction_category_id = p_tran_cat_id;
cursor c1 is select attribute_name,column_name
from pqh_attributes_vl
where attribute_id = p_attribute_id
and nvl(enable_flag,'X') ='Y';
cursor c2 is select sty.shared_type_name unit_name,lkp.description avail_desc
from pqh_budgets bgt, per_shared_types sty, hr_lookups lkp
where bgt.budget_id = l_budget_id
and bgt.budget_unit1_aggregate = lkp.lookup_code
and lkp.lookup_type ='PQH_BGT_UOM_AGGREGATE'
and bgt.budget_unit1_id = sty.shared_type_id ;
cursor c3 is select sty.shared_type_name unit_name, lkp.description avail_desc
from pqh_budgets bgt, per_shared_types sty, hr_lookups lkp
where bgt.budget_id = l_budget_id
and bgt.budget_unit2_aggregate = lkp.lookup_code
and lkp.lookup_type ='PQH_BGT_UOM_AGGREGATE'
and bgt.budget_unit2_id = sty.shared_type_id ;
cursor c4 is select sty.shared_type_name unit_name, lkp.description avail_desc
from pqh_budgets bgt, per_shared_types sty, hr_lookups lkp
where bgt.budget_id = l_budget_id
and bgt.budget_unit3_aggregate = lkp.lookup_code
and lkp.lookup_type ='PQH_BGT_UOM_AGGREGATE'
and bgt.budget_unit3_id = sty.shared_type_id ;
cursor c0 is select member_cd
from pqh_transaction_categories
where transaction_category_id = p_tran_cat_id;
select routing_list_id ,routing_category_id
from pqh_routing_categories
where transaction_category_id = p_tran_cat_id
and nvl(enable_flag,'N') ='Y'
and nvl(default_flag,'N') = 'Y'
and nvl(delete_flag,'N') = 'N'
and routing_list_id is not null;
select position_structure_id ,routing_category_id
from pqh_routing_categories
where transaction_category_id = p_tran_cat_id
and nvl(enable_flag,'N') ='Y'
and nvl(default_flag,'N') = 'Y'
and nvl(delete_flag,'N') = 'N'
and position_structure_id is not null;
select routing_category_id
from pqh_routing_categories
where transaction_category_id = p_tran_cat_id
and nvl(enable_flag,'N') ='Y'
and nvl(default_flag,'N') = 'Y'
and nvl(delete_flag,'N') = 'N'
and routing_list_id is null
and position_structure_id is null;
delete_rout_crit(p_used_for => nvl(p_used_for,'L'));
hr_utility.set_location('data deleted for rout select '||l_proc,40);
select distinct ar.routing_category_id, ar.range_name
from pqh_attribute_ranges ar
where ar.routing_list_member_id is null
and ar.position_id is null
and ar.assignment_id is null
and nvl(ar.enable_flag,'X') = 'Y'
and nvl(ar.delete_flag,'N') = 'N'
and routing_category_id in (select routing_category_id
from pqh_routing_categories rc, pqh_transaction_categories tc
where rc.transaction_category_id = p_tran_cat_id
and tc.transaction_category_id = rc.transaction_category_id
and ((tc.member_cd = 'R' and rc.routing_list_id is not null) or
(tc.member_cd = 'P' and rc.Position_structure_id is not null) or
(tc.member_cd = 'S' and rc.routing_list_id is null and rc.position_structure_id is null))
and nvl(rc.enable_flag,'X') = 'Y'
and nvl(rc.delete_flag,'N') = 'N'
and nvl(rc.default_flag,'X') <> 'Y' );
select att.attribute_id,att.attribute_name,att.column_name,att.column_type
from pqh_attributes att, pqh_txn_category_attributes tca
where att.attribute_id = tca.attribute_id
and tca.transaction_category_id = p_tran_cat_id
and tca.list_identifying_flag = 'Y'
and nvl(att.enable_flag,'X') = 'Y';
select ar.from_char,ar.to_char,ar.from_date,ar.to_date,ar.from_number,
ar.to_number,rc.routing_category_id,ar.range_name
from pqh_attribute_ranges ar,pqh_routing_categories rc
where ar.attribute_id = p_attribute_id
and ar.routing_category_id = rc.routing_category_id
and rc.transaction_category_id = p_tran_cat_id
and ar.routing_list_member_id is null
and ar.position_id is null
and ar.assignment_id is null
and nvl(ar.enable_flag,'X') = 'Y'
and nvl(rc.delete_flag,'N') = 'N'
and nvl(ar.delete_flag,'N') = 'N'
and nvl(rc.enable_flag,'X') = 'Y'
and nvl(rc.default_flag,'X') <> 'Y';
select tc.member_cd,rc.routing_list_id,rc.position_structure_id
from pqh_routing_categories rc, pqh_transaction_categories tc
where rc.routing_category_id = p_routing_category_id
and tc.transaction_category_id = rc.transaction_category_id
and nvl(rc.delete_flag,'N') = 'N'
and nvl(rc.enable_flag,'X') = 'Y';
selected_flag boolean) ;
delete_rout_crit(p_used_for => p_used_for);
l_hierarchy(l_rule_cnt).selected_flag := TRUE ;
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_char ;
hr_utility.set_location('error in select table'||l_proc,70);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_date ;
hr_utility.set_location('error in select table'||l_proc,70);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_num ;
hr_utility.set_location('error in select table'||l_proc,70);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
insert_rout_crit(p_attribute_id => i.attribute_id,
p_used_for => p_used_for,
p_rule_name => j.range_name,
p_attribute_type => i.column_type,
p_from_num => j.from_number,
p_to_num => j.to_number,
p_value_num => l_attribute_value_num,
p_from_char => j.from_char,
p_to_char => j.to_char,
p_value_char => l_attribute_value_char,
p_from_date => j.from_date,
p_to_date => j.to_date,
p_value_date => l_attribute_value_date);
hr_utility.set_location('Value not in range deselect range '||l_proc,150);
l_hierarchy(k).selected_flag := FALSE ;
hr_utility.set_location('catg deleted'||to_char(j.routing_category_id)||j.range_name||l_proc,155);
hr_utility.set_location('Picking selected range '||l_proc,160);
if l_hierarchy(i).selected_flag = TRUE then
if l_range_found_flag = TRUE then
-- hard coding the value of routing catg to 0
p_routing_category_id := 0;
delete_rout_crit(p_used_for => p_used_for,
p_rule_name => p_range_name);
select count(*) into l_standard_setup
from pqh_routing_categories
where transaction_category_id = p_tran_cat_id
and nvl(default_flag,'X') ='Y'
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') = 'Y';
hr_utility.set_message(8302,'PQH_NO_RANGE_SELECTED');
select count(*) into l_rout_cat
from pqh_routing_categories
where transaction_category_id = p_tran_cat_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') = 'Y';
select tc.member_cd,rc.routing_list_id,rc.position_structure_id,rc.routing_category_id
into p_member_cd,p_routing_list_id,p_pos_str_id,p_routing_category_id
from pqh_routing_categories rc, pqh_transaction_categories tc
where tc.transaction_category_id = p_tran_cat_id
and tc.transaction_category_id = rc.transaction_category_id
and nvl(rc.enable_flag,'X') = 'Y'
and nvl(rc.delete_flag,'N') = 'N' ;
select distinct range_name, NVL(approver_flag,'N') approve_flag
from pqh_attribute_ranges
where assignment_id = p_assignment_id
and routing_category_id = p_routing_category_id
and nvl(enable_flag,'X') = 'Y'
and nvl(delete_flag,'N') = 'N';
select att.attribute_id,att.attribute_name,att.column_name,att.column_type
from pqh_attributes att, pqh_txn_category_attributes tca
where att.attribute_id = tca.attribute_id and
tca.transaction_category_id = p_tran_cat_id
and tca.member_identifying_flag = 'Y'
and nvl(att.enable_flag,'X') = 'Y';
select range_name,from_char,to_char,from_date,to_date,from_number,to_number,approver_flag
from pqh_attribute_ranges
where attribute_id = p_attribute_id
and assignment_id = p_assignment_id
and routing_category_id = p_routing_category_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') = 'Y';
selected_flag boolean ) ;
delete_rout_crit(p_used_for => p_used_for);
l_assignment_rules(l_rule_cnt).selected_flag := TRUE ;
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_char ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_date ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_num ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
insert_rout_crit(p_attribute_id => i.attribute_id,
p_used_for => p_used_for,
p_attribute_type => i.column_type,
p_from_num => j.from_number,
p_to_num => j.to_number,
p_value_num => l_attribute_value_num,
p_from_char => j.from_char,
p_to_char => j.to_char,
p_value_char => l_attribute_value_char,
p_from_date => j.from_date,
p_to_date => j.to_date,
p_value_date => l_attribute_value_date);
l_assignment_rules(k).selected_flag := FALSE ;
if l_assignment_rules(i).selected_flag = TRUE then
if p_used_for = 'C' then
g_current_member_range := l_assignment_rules(i).range_name;
delete_rout_crit(p_used_for => p_used_for,
p_rule_name => l_assignment_rules(i).range_name);
Select attribute_id,nvl(approver_flag,'N') approver_flag
from pqh_attribute_ranges
where position_id = p_position_id
and routing_category_id = p_routing_category_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') ='Y'
and range_name = p_range_name;
cursor c1 is select distinct range_name
from pqh_attribute_ranges
where position_id = p_position_id
and routing_category_id = p_routing_category_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') ='Y';
select att.attribute_id,att.attribute_name,att.column_name,att.column_type
from pqh_attributes att,pqh_txn_category_attributes tca
where att.attribute_id = tca.attribute_id and
tca.transaction_category_id = p_tran_cat_id
and tca.member_identifying_flag = 'Y'
and nvl(att.enable_flag,'X') = 'Y';
select range_name,from_char,to_char,from_date,to_date,from_number,to_number,approver_flag
from pqh_attribute_ranges
where attribute_id = p_attribute_id
and position_id = p_position_id
and routing_category_id = p_routing_category_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') ='Y';
selected_flag boolean ) ;
l_position_rules(l_rule_cnt).selected_flag := TRUE ;
delete_rout_crit(p_used_for => p_used_for);
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_char ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_date ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_num ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
insert_rout_crit(p_attribute_id => i.attribute_id,
p_used_for => p_used_for,
p_attribute_type => i.column_type,
p_from_num => j.from_number,
p_to_num => j.to_number,
p_value_num => l_attribute_value_num,
p_from_char => j.from_char,
p_to_char => j.to_char,
p_value_char => l_attribute_value_char,
p_from_date => j.from_date,
p_to_date => j.to_date,
p_value_date => l_attribute_value_date);
hr_utility.set_location('not in range, deselecting'||l_proc,125);
l_position_rules(k).selected_flag := FALSE ;
if l_position_rules(i).selected_flag = TRUE then
if p_used_for = 'C' then
g_current_member_range := l_position_rules(i).range_name;
delete_rout_crit(p_used_for => p_used_for,
p_rule_name => l_position_rules(i).range_name);
cursor c1 is select distinct range_name, NVL(approver_flag,'N') approve_flag
from pqh_attribute_ranges
where routing_list_member_id = p_member_id
and routing_category_id = p_routing_category_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') ='Y';
select att.attribute_id,att.attribute_name,att.column_name,att.column_type
from pqh_attributes att, pqh_txn_category_attributes tca
where att.attribute_id = tca.attribute_id and
tca.transaction_category_id = p_tran_cat_id
and tca.member_identifying_flag = 'Y'
and nvl(att.enable_flag,'X') = 'Y';
select range_name,from_char,to_char,from_date,to_date,from_number,to_number,approver_flag
from pqh_attribute_ranges
where attribute_id = p_attribute_id
and routing_list_member_id = p_member_id
and routing_category_id = p_routing_category_id
and nvl(delete_flag,'N') = 'N'
and nvl(enable_flag,'X') ='Y';
cursor c4 is select approver_flag
from pqh_routing_list_members
where routing_list_member_id = p_member_id
and nvl(enable_flag,'X') = 'Y';
selected_flag boolean ) ;
l_member_rules(l_rule_cnt).selected_flag := TRUE ;
delete_rout_crit(p_used_for => p_used_for);
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_char ;
hr_utility.set_location('error in select table'||l_proc,72);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_date ;
hr_utility.set_location('error in select table'||l_proc,82);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
execute immediate 'select '||i.column_name||' '||p_from_clause
into l_attribute_value_num ;
hr_utility.set_location('error in select table'||l_proc,92);
hr_utility.set_message(8302,'PQH_SELECT_FAILED');
insert_rout_crit(p_attribute_id => i.attribute_id,
p_used_for => p_used_for,
p_attribute_type => i.column_type,
p_from_num => j.from_number,
p_to_num => j.to_number,
p_value_num => l_attribute_value_num,
p_from_char => j.from_char,
p_to_char => j.to_char,
p_value_char => l_attribute_value_char,
p_from_date => j.from_date,
p_to_date => j.to_date,
p_value_date => l_attribute_value_date);
l_member_rules(k).selected_flag := FALSE ;
hr_utility.set_location('not in range, deselecting'||l_member_rules(k).range_name||l_proc,132);
if l_member_rules(k).selected_flag = TRUE then
if l_member_rules(k).approve_flag = TRUE then
p_can_approve := TRUE;
delete_rout_crit(p_used_for => p_used_for,
p_rule_name => l_member_rules(k).range_name);
cursor c2 is select supervisor_id
from per_all_assignments_f
where assignment_id = p_cur_assignment_id
and primary_flag ='Y'
and p_value_date between effective_start_date and effective_end_date;
cursor c1 is select rou.from_clause,rou.where_clause
from pqh_transaction_categories cat ,pqh_table_route rou
where cat.transaction_category_id = p_tran_cat_id
and cat.consolidated_table_route_id = rou.table_route_id ;
cursor c1 is select position_structure_id
from per_pos_structure_versions
where pos_structure_version_id = p_pos_str_ver_id ;
cursor c1 is select max(pos_structure_version_id)
from per_pos_structure_versions
where position_structure_id = p_pos_str_id ;
cursor c1 is select role_id,role_name
from pqh_roles
where role_name = nvl(p_role_name,role_name)
and role_id = nvl(p_role_id,role_id)
and nvl(enable_flag,'X') ='Y';
cursor c2 is select routing_list_member_id
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and role_id = p_role_id
and user_id = p_user_id
and nvl(enable_flag,'X') ='Y';
cursor c3 is select routing_list_member_id
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and role_id = p_role_id and user_id is null
and nvl(enable_flag,'X') ='Y';
cursor c4 is select routing_list_member_id
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and role_id = p_old_role_id
and user_id = p_old_user_id
and nvl(enable_flag,'X') ='Y';
cursor c5 is select routing_list_member_id
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and role_id = p_old_role_id
and (user_id is null or user_id = p_user_id)
and nvl(enable_flag,'X') ='Y';
cursor c1 is select max(routing_history_id)
from pqh_routing_history
where transaction_category_id = p_tran_cat_id
and transaction_id = p_trans_id
and user_action_cd <> 'APPLY';
cursor c2 is select rh.forwarded_to_position_id,rh.forwarded_to_role_id,rh.forwarded_to_user_id,rh.forwarded_to_member_id,
rh.forwarded_to_assignment_id,rh.pos_structure_version_id,rc.routing_list_id
from pqh_routing_history rh,pqh_routing_categories rc
where routing_history_id = p_routing_history_id
and rh.routing_category_id = rc.routing_category_id;
select routing_list_member_id,role_id,user_id
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and seq_no = (select min(seq_no)
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and seq_no > (select seq_no
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and routing_list_member_id = p_cur_member_id)
and nvl(enable_flag,'X') = 'Y');
cursor c2 is select routing_list_member_id,role_id,user_id
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and seq_no = (select min(seq_no)
from pqh_routing_list_members
where routing_list_id = p_routing_list_id
and nvl(enable_flag,'X') = 'Y');
select parent_position_id
from per_pos_structure_elements
where subordinate_position_id = l_position_id
and pos_structure_version_id = p_pos_str_ver_id ;
select wf_transaction_category_id
into l_txn_cat_id
from pqh_position_transactions
where position_transaction_id = p_transaction_id;
select wks.wf_transaction_category_id
into l_txn_cat_id
from pqh_worksheet_details wkd, pqh_worksheets wks
where worksheet_detail_id = p_transaction_id
and wks.worksheet_id = wkd.worksheet_id;
select wf_transaction_category_id
into l_txn_cat_id
from pqh_budget_pools
where pool_id = p_transaction_id;
select member_cd,freeze_status_cd,name
from pqh_transaction_categories
where transaction_category_id = p_transaction_category_id
and nvl(enable_flag,'Y') = 'Y';
select employee_id
from fnd_user
where user_id = p_user_id;
select ppos.date_start, ppos.actual_termination_date
from per_periods_of_service ppos
where ppos.person_id = l_person_id
and p_date between ppos.date_start and nvl(ppos.actual_termination_date, hr_general.end_of_time) ;
select min(ppos.date_start)
from per_periods_of_service ppos
where ppos.person_id = l_person_id;
select user_action_cd
from pqh_routing_history
where routing_history_id = p_routing_history_id;
SELECT effective_date
INTO l_session_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'),trunc(sysdate));
select max(routing_history_id)
into l_routing_history_id
from pqh_routing_history
where routing_history_id > nvl(p_routing_history_id,0)
and transaction_id = p_transaction_id
and transaction_category_id = p_wf_transaction_category_id;
Select
decode(information_type, 'PQH_ROLE_USERS', to_number(pei.pei_information3), 0) role_id
from per_people_extra_info pei , fnd_user usr
WHERE usr.user_id = p_user_id
and usr.employee_id = pei.person_id
and information_type = 'PQH_ROLE_USERS'
/** Check if default role **/
and nvl(pei.pei_information4,'N') = 'Y'
/** Check if enabled **/
and nvl(pei.pei_information5,'Y')='Y';