[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c0 is select object_version_number
from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id;
cursor c0 is select max(routing_history_id) from pqh_routing_history
where transaction_category_id = p_transaction_category_id
and transaction_id = p_worksheet_detail_id ;
cursor c1 is select person_name_to,role_name_to,position_name_to,user_name_to
from pqh_routing_history_v
where transaction_id = p_worksheet_detail_id
and transaction_category_id = p_transaction_category_id
and routing_history_id = l_max_routing_history_id ;
cursor c2 is select user_id from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id;
cursor c4(p_user_id number) is select user_name from fnd_user
where user_id = p_user_id;
cursor c1 is select worksheet_detail_id
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id;
cursor c1 is select worksheet_detail_id
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and action_cd ='D';
select worksheet_detail_id,status,object_version_number
from pqh_worksheet_details
where action_cd ='D'
and parent_worksheet_detail_id = p_worksheet_detail_id;
select wkd.status,wkd.parent_worksheet_detail_id,wkd.object_version_number,
wks.worksheet_id,wks.object_version_number
into l_status,l_parent_wkd_id,l_wkd_ovn,l_worksheet_id,l_wks_ovn
from pqh_worksheet_details wkd, pqh_worksheets wks
where wkd.worksheet_detail_id = p_worksheet_detail_id
and wkd.worksheet_id = wks.worksheet_id;
update_worksheet_detail(
p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd_ovn,
p_status => 'REJECT'
);
pqh_worksheets_api.update_worksheet(
p_worksheet_id => l_worksheet_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wks_ovn,
p_transaction_status => 'REJECT'
);
update_worksheet_detail(
p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd_ovn,
p_status => 'REJECT'
);
hr_utility.set_location('status updated '||p_worksheet_detail_id||l_proc,120);
update_worksheet_detail( p_worksheet_detail_id => i.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd1_ovn,
p_status => 'APPROVED');
update_worksheet_detail( p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd_ovn,
p_status => 'APPROVED');
pqh_worksheets_api.update_worksheet(
p_worksheet_id => l_worksheet_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wks_ovn,
p_transaction_status => 'APPROVED');
update_worksheet_detail( p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd_ovn,
p_status => 'APPROVED');
update_worksheet_detail( p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd_ovn,
p_status => 'SUBMITTED');
pqh_worksheets_api.update_worksheet(
p_worksheet_id => l_worksheet_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wks_ovn,
p_transaction_status => 'SUBMITTED');
update_worksheet_detail( p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wkd_ovn,
p_status => 'PENDING');
pqh_worksheets_api.update_worksheet(
p_worksheet_id => l_worksheet_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_wks_ovn,
p_transaction_status => 'PENDING');
select wkd.object_version_number,wks.object_version_number
into l_wkd_ovn,l_wks_ovn
from pqh_worksheet_details wkd, pqh_worksheets wks
where wkd.worksheet_detail_id = p_worksheet_detail_id
and wkd.worksheet_id = wks.worksheet_id;
select wkd.object_version_number, wks.object_version_number
into l_wkd_ovn,l_wks_ovn
from pqh_worksheet_details wkd, pqh_worksheets wks
where wkd.worksheet_detail_id = p_worksheet_detail_id
and wkd.worksheet_id = wks.worksheet_id;
cursor c1 is select budget_unit1_available ,
budget_unit2_available ,
budget_unit3_available ,
budget_unit1_value_type_cd,
budget_unit2_value_type_cd,
budget_unit3_value_type_cd,
status,organization_id,worksheet_detail_id,object_version_number,propagation_method
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and action_cd ='D'
and status ='APPROVED'
for update of status;
cursor c2 is select budget_unit1_available,budget_unit2_available,
budget_unit3_available,worksheet_detail_id,object_version_number
from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id;
update_worksheet_detail( p_worksheet_detail_id => i.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_status => 'DELEGATE');
update_worksheet_detail(p_worksheet_detail_id => i.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => p_object_version_number,
p_budget_unit1_available => nvl(i.budget_unit1_available,0) - nvl(l_chg_unit1_available,0),
p_budget_unit2_available => nvl(i.budget_unit2_available,0) - nvl(l_chg_unit2_available,0),
p_budget_unit3_available => nvl(i.budget_unit3_available,0) - nvl(l_chg_unit3_available,0));
hr_utility.set_location('parent updated and exiting'||l_proc,70);
cursor c1 is select worksheet_detail_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
object_version_number
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and nvl(action_cd,'D') = 'B'
for update of budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent ;
cursor c2 is select worksheet_detail_id,status,object_version_number,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
old_unit1_value,old_unit2_value,old_unit3_value,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and nvl(action_cd,'D') = 'D'
for update of status,budget_unit1_value,budget_unit2_value,budget_unit3_value,
old_unit1_value,old_unit2_value,old_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent ;
cursor c3 is select wks.propagation_method
from pqh_worksheets wks, pqh_worksheet_details wkd
where wks.worksheet_id = wkd.worksheet_id
and worksheet_detail_id = p_worksheet_detail_id;
update_worksheet_detail( p_worksheet_detail_id => i.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_budget_unit1_percent => l_budget_unit1_percent,
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_percent => l_budget_unit2_percent,
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_percent => l_budget_unit3_percent,
p_budget_unit3_value => l_budget_unit3_value,
p_budget_unit1_available => l_budget_unit1_available,
p_budget_unit2_available => l_budget_unit2_available,
p_budget_unit3_available => l_budget_unit3_available,
p_object_version_number => l_object_version_number);
hr_utility.set_location('budget row updated '||l_proc,120);
update_worksheet_detail(
p_worksheet_detail_id => j.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_budget_unit1_percent => l_budget_unit1_percent,
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_percent => l_budget_unit2_percent,
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_percent => l_budget_unit3_percent,
p_budget_unit3_value => l_budget_unit3_value,
p_old_unit1_value => l_old_unit1_value,
p_old_unit2_value => l_old_unit2_value,
p_old_unit3_value => l_old_unit3_value,
p_budget_unit1_available => l_budget_unit1_available,
p_budget_unit2_available => l_budget_unit2_available,
p_budget_unit3_available => l_budget_unit3_available
);
hr_utility.set_location('worksheet updated'||l_proc,260);
cursor c1 is select worksheet_period_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_worksheet_periods
where worksheet_detail_id = p_worksheet_detail_id
for update of budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available ;
update pqh_worksheet_periods
set budget_unit1_value = l_period_unit1_value,
budget_unit2_value = l_period_unit2_value,
budget_unit3_value = l_period_unit3_value,
budget_unit1_percent = l_period_unit1_percent,
budget_unit2_percent = l_period_unit2_percent,
budget_unit3_percent = l_period_unit3_percent,
budget_unit1_available = l_period_unit1_available,
budget_unit2_available = l_period_unit2_available,
budget_unit3_available = l_period_unit3_available
where current of c1;
hr_utility.set_location('after period updated '||l_proc,140);
cursor c1 is select budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_worksheet_budget_sets
where worksheet_period_id = p_worksheet_period_id
for update of budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available ;
hr_utility.set_location('before update values passed are '||l_proc,110);
update pqh_worksheet_budget_sets
set budget_unit1_value = l_budgetset_unit1_value,
budget_unit2_value = l_budgetset_unit2_value,
budget_unit3_value = l_budgetset_unit3_value,
budget_unit1_percent = l_budgetset_unit1_percent,
budget_unit2_percent = l_budgetset_unit2_percent,
budget_unit3_percent = l_budgetset_unit3_percent,
budget_unit1_available = l_budgetset_unit1_available,
budget_unit2_available = l_budgetset_unit2_available,
budget_unit3_available = l_budgetset_unit3_available
where current of c1;
hr_utility.set_location('after update out nocopy values passed are '||l_proc,130);
cursor c1 is select position_id,organization_id,parent_worksheet_detail_id,worksheet_detail_id,
budget_unit1_percent,budget_unit1_value,budget_unit1_value_type_cd,
budget_unit2_percent,budget_unit2_value,budget_unit2_value_type_cd,
budget_unit3_percent,budget_unit3_value,budget_unit3_value_type_cd,
object_version_number
from pqh_worksheet_details
where parent_worksheet_detail_id = p_parent_wd_id
and action_cd ='B'
for update of parent_worksheet_detail_id ,budget_unit1_percent,budget_unit2_percent,budget_unit3_percent;
select organization_id
from hr_positions
where position_id = p_position_id;
cursor c2 is select organization_id_child
from per_org_structure_elements
where org_structure_version_id = p_org_str_id
connect by prior organization_id_child = organization_id_parent
and org_structure_version_id = p_org_str_id
start with organization_id_parent = p_delegate_org_id
and org_structure_version_id = p_org_str_id;
update_worksheet_detail(
p_worksheet_detail_id => j.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_parent_worksheet_detail_id => p_delegate_wd_id,
p_budget_unit1_percent => l_budget_unit1_percent,
p_budget_unit2_percent => l_budget_unit2_percent,
p_budget_unit3_percent => l_budget_unit3_percent
);
update_worksheet_detail(
p_worksheet_detail_id => p_delegate_wd_id,
p_effective_date => trunc(sysdate),
p_object_version_number => p_delegate_ovn,
p_budget_unit1_value => p_del_budget_unit1_value,
p_budget_unit2_value => p_del_budget_unit2_value,
p_budget_unit3_value => p_del_budget_unit3_value,
p_budget_unit1_available => p_del_budget_unit1_available,
p_budget_unit2_available => p_del_budget_unit2_available,
p_budget_unit3_available => p_del_budget_unit3_available
);
procedure delete_delegate(p_worksheet_detail_id in number) as
cursor c1 is select worksheet_detail_id,rowid row_id
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and action_cd ='D'
for update of worksheet_detail_id ;
select count(*)
from pqh_worksheet_details
where parent_worksheet_detail_id = p_parent_wd_id;
l_proc varchar2(100) := g_package||'delete_delegate' ;
hr_utility.set_location('goind to delete details of '||i.worksheet_detail_id||l_proc,20);
delete_delegate(i.worksheet_detail_id);
delete from pqh_worksheet_details where rowid = i.row_id;
end delete_delegate;
procedure delete_delegate_chk(p_worksheet_detail_id in number,
p_status_flag out nocopy number) as
cursor c1 is select worksheet_detail_id,status
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and action_cd ='D'
for update of worksheet_detail_id ;
select count(*)
from pqh_worksheet_details
where parent_worksheet_detail_id = p_parent_wd_id
and action_cd ='D';
l_proc varchar2(100) := g_package||'delete_delegate_chk' ;
delete_delegate_chk(i.worksheet_detail_id,l_status);
end delete_delegate_chk;
procedure delete_adjustment(p_parent_wd_id in number,
p_delegate_wd_id in number,
p_budget_style_cd in varchar2,
p_budget_unit1_value in out nocopy number,
p_budget_unit2_value in out nocopy number,
p_budget_unit3_value in out nocopy number,
p_budget_unit1_available in out nocopy number,
p_budget_unit2_available in out nocopy number,
p_budget_unit3_available in out nocopy number)
is
cursor c2 is select worksheet_detail_id
from pqh_worksheet_details
where action_cd ='D'
and parent_worksheet_detail_id = p_delegate_wd_id ;
cursor c1 is select action_cd,parent_worksheet_detail_id,worksheet_detail_id,
budget_unit1_percent,budget_unit1_value,budget_unit1_value_type_cd,
budget_unit2_percent,budget_unit2_value,budget_unit2_value_type_cd,
budget_unit3_percent,budget_unit3_value,budget_unit3_value_type_cd,
object_version_number
from pqh_worksheet_details
where action_cd = 'B'
and parent_worksheet_detail_id = p_delegate_wd_id
for update of parent_worksheet_detail_id,budget_unit1_percent,budget_unit2_percent,budget_unit3_percent ;
l_proc varchar2(100) := g_package||'delete_adjustment' ;
hr_utility.set_location('going to update values for wd '||j.worksheet_detail_id||l_proc,10);
hr_utility.set_location('going to update worksheetdetail '||j.worksheet_detail_id||l_proc,20);
update_worksheet_detail(
p_worksheet_detail_id => j.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_parent_worksheet_detail_id => p_parent_wd_id,
p_budget_unit1_percent => l_budget_unit1_percent,
p_budget_unit2_percent => l_budget_unit2_percent,
p_budget_unit3_percent => l_budget_unit3_percent
);
delete_adjustment(p_parent_wd_id => p_parent_wd_id,
p_delegate_wd_id => i.worksheet_detail_id,
p_budget_style_cd => p_budget_style_cd,
p_budget_unit1_value => p_budget_unit1_value,
p_budget_unit2_value => p_budget_unit2_value,
p_budget_unit3_value => p_budget_unit3_value,
p_budget_unit1_available => p_budget_unit1_available,
p_budget_unit2_available => p_budget_unit2_available,
p_budget_unit3_available => p_budget_unit3_available);
end delete_adjustment ;
procedure delegate_delete_adjustment(p_parent_wd_id in number,
p_delegate_wd_id in number,
p_budget_style_cd in varchar2,
p_budget_unit1_value in out nocopy number,
p_budget_unit2_value in out nocopy number,
p_budget_unit3_value in out nocopy number,
p_budget_unit1_available in out nocopy number,
p_budget_unit2_available in out nocopy number,
p_budget_unit3_available in out nocopy number)
is
l_proc varchar2(100) := g_package||'delegate_delete_adjustment' ;
delete_adjustment(p_parent_wd_id => p_parent_wd_id,
p_delegate_wd_id => p_delegate_wd_id,
p_budget_style_cd => p_budget_style_cd,
p_budget_unit1_value => p_budget_unit1_value,
p_budget_unit2_value => p_budget_unit2_value,
p_budget_unit3_value => p_budget_unit3_value,
p_budget_unit1_available => p_budget_unit1_available,
p_budget_unit2_available => p_budget_unit2_available,
p_budget_unit3_available => p_budget_unit3_available);
hr_utility.set_location('going to delete '||p_delegate_wd_id||l_proc,1000);
delete_delegate(p_delegate_wd_id);
end delegate_delete_adjustment ;
Insert_from_budget is a overloaded procedure .
This one copies budget details as well as their values, but it can fail if the details for the version are having values upto the limit of the budget version values.
This procedure may be removed after some time.
*/
procedure insert_from_budget(p_budget_version_id in number,
p_budgeted_entity_cd in varchar,
p_worksheet_id in number,
p_business_group_id in number,
p_start_organization_id in number,
p_parent_worksheet_detail_id in number,
p_worksheet_unit1_available in out nocopy number,
p_worksheet_unit2_available in out nocopy number,
p_worksheet_unit3_available in out nocopy number,
p_worksheet_unit1_value in out nocopy number,
p_worksheet_unit2_value in out nocopy number,
p_worksheet_unit3_value in out nocopy number,
p_org_hier_ver in number,
p_copy_budget_periods in varchar2,
p_budget_style_cd in varchar,
p_rows_inserted out nocopy number) IS
cursor c0 is select budget_unit1_value,budget_unit2_value,budget_unit3_value
from pqh_budget_versions
where budget_version_id = p_budget_version_id;
cursor c1 is select position_id , grade_id, bud.organization_id organization_id, job_id,budget_detail_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd
from pqh_budget_details bud, hr_organization_units org
where org.business_group_id = p_business_group_id
and bud.organization_id = org.organization_id
and bud.budget_version_id = p_budget_version_id;
cursor c2 is select bud.position_id, bud.grade_id, bud.organization_id , bud.job_id,bud.budget_detail_id,
bud.budget_unit1_value,bud.budget_unit2_value,bud.budget_unit3_value,
bud.budget_unit1_available,bud.budget_unit2_available,bud.budget_unit3_available,
bud.budget_unit1_percent,bud.budget_unit2_percent,bud.budget_unit3_percent,
bud.budget_unit1_value_type_cd,bud.budget_unit2_value_type_cd,bud.budget_unit3_value_type_cd
from (select organization_id_child from pqh_worksheet_organizations_v
where org_structure_version_id = p_org_hier_ver
connect by prior organization_id_child = organization_id_parent and org_structure_version_id = p_org_hier_ver
start with organization_id_parent = p_start_organization_id and org_structure_version_id = p_org_hier_ver
union all
select p_start_organization_id organization_id_child from dual )x
, pqh_budget_details bud
where bud.budget_version_id = p_budget_version_id
and bud.organization_id = x.organization_id_child;
cursor c3 is select position_id , grade_id, bud.organization_id organization_id, job_id,budget_detail_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd
from pqh_budget_details bud, hr_organization_units org
where org.business_group_id = p_business_group_id
and bud.organization_id = org.organization_id
and pqh_budget.already_budgeted_org(bud.organization_id) = 'FALSE'
and bud.budget_version_id = p_budget_version_id;
cursor c4 is select position_id, grade_id, organization_id , job_id,budget_detail_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd
from (select organization_id_child from pqh_worksheet_organizations_v
where org_structure_version_id = p_org_hier_ver
connect by prior organization_id_child = organization_id_parent and org_structure_version_id = p_org_hier_ver
start with organization_id_parent = p_start_organization_id and org_structure_version_id = p_org_hier_ver
union all
select p_start_organization_id organization_id_child from dual )x
, pqh_budget_details
where pqh_budget.already_budgeted_org(organization_id) = 'FALSE'
and budget_version_id = p_budget_version_id
and organization_id = x.organization_id_child;
cursor c5 is select position_id ,grade_id, organization_id , job_id,budget_detail_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd
from pqh_budget_details
where pqh_budget.already_budgeted_job(job_id) = 'FALSE'
and budget_version_id = p_budget_version_id;
cursor c6 is select position_id ,grade_id, organization_id , job_id,budget_detail_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd
from pqh_budget_details
where pqh_budget.already_budgeted_grd(grade_id) = 'FALSE'
and budget_version_id = p_budget_version_id;
cursor c7 is select position_id ,grade_id, organization_id , job_id,budget_detail_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd
from pqh_budget_details
where budget_version_id = p_budget_version_id;
l_rows_inserted number := 0;
l_proc varchar2(100) := g_package||'insert_from_budget' ;
l_rows_inserted := l_rows_inserted + 1;
hr_utility.set_location('inserting into plsql table'||l_proc,70);
pqh_budget.insert_pos_is_bud(i.position_id);
hr_utility.set_location('inserting into worksheet_detail table'||l_proc,80);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('insert worksheet_detail table complete'||l_proc,90);
hr_utility.set_location('before insert loop '||l_proc,135);
hr_utility.set_location('inside insert loop '||l_proc,140);
l_rows_inserted := l_rows_inserted + 1;
hr_utility.set_location('going for insert '||l_proc,148);
pqh_budget.insert_pos_is_bud(i.position_id);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('row inserted going for period copy'||l_proc,150);
hr_utility.set_location('before insert loop '||l_proc,190);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_org_is_bud(i.organization_id);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,200);
hr_utility.set_location('before insert loop '||l_proc,230);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_org_is_bud(i.organization_id);
insert_worksheet_detail
(
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_old_unit1_value => ''
,p_old_unit2_value => ''
,p_old_unit3_value => ''
,p_defer_flag => ''
,p_propagation_method => ''
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,240);
hr_utility.set_location('before insert loop'||l_proc,270);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_job_is_bud(i.job_id);
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,280);
hr_utility.set_location('before insert loop '||l_proc,310);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_grd_is_bud(i.grade_id);
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,320);
hr_utility.set_location('before insert loop '||l_proc,350);
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value => i.budget_unit1_value
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value => i.budget_unit2_value
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value => i.budget_unit3_value
,p_budget_unit1_value_type_cd => i.budget_unit1_value_type_cd
,p_budget_unit2_value_type_cd => i.budget_unit2_value_type_cd
,p_budget_unit3_value_type_cd => i.budget_unit3_value_type_cd
,p_status => ''
,p_budget_unit1_available => i.budget_unit1_value
,p_budget_unit2_available => i.budget_unit2_value
,p_budget_unit3_available => i.budget_unit3_value
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,360);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
end insert_from_budget;
p_rows_inserted out nocopy number) as
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date,valid_grade_reqd_flag,budgeted_entity_cd
from pqh_budgets bge, pqh_worksheets wks
where wks.budget_id = bge.budget_id
and wks.worksheet_id = p_worksheet_id;
cursor c1 is select grade_id from per_grades a
where business_group_id = p_business_group_id
and ((nvl(l_valid_grade_flag,'N') = 'Y' and l_budget_entity_cd = 'GRADE' and
a.grade_id in (select b.grade_id from per_valid_grades b
where b.date_from < l_budget_end_date
and (b.date_to > l_budget_start_date or b.date_to is null)))
or (nvl(l_valid_grade_flag,'N') = 'N' and date_from < l_budget_end_date
and (date_to > l_budget_start_date or date_to is null)));
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => ''
,p_job_id => ''
,p_position_id => ''
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B');
pqh_budget.insert_grd_is_bud(i.grade_id);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
p_rows_inserted out nocopy number) as
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_worksheets wks
where wks.budget_id = bgt.budget_id
and wks.worksheet_id = p_worksheet_id;
cursor c1 is select job_id from per_jobs
where business_group_id = p_business_group_id
and date_from < l_budget_end_date
and (date_to > l_budget_start_date or date_to is null);
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => ''
,p_job_id => i.job_id
,p_position_id => ''
,p_grade_id => ''
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B');
pqh_budget.insert_job_is_bud(i.job_id);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
p_rows_inserted out nocopy number) as
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_worksheets wks
where wks.budget_id = bgt.budget_id
and wks.worksheet_id = p_worksheet_id;
cursor c1 is select position_id,job_id,organization_id,availability_status_id
from hr_positions
where business_group_id = p_business_group_id
and effective_start_date < l_budget_end_date
and effective_end_date > l_budget_start_date ;
cursor csr_orgs is select organization_id_child
from pqh_worksheet_organizations_v
where org_structure_version_id = p_org_hier_ver
connect by prior organization_id_child = organization_id_parent
and org_structure_version_id = p_org_hier_ver
start with organization_id_parent = p_start_organization_id
and org_structure_version_id = p_org_hier_ver
union all
select p_start_organization_id organization_id_child from dual;
select position_id,job_id,organization_id,availability_status_id
from hr_positions
where effective_start_date < l_budget_end_date
and effective_end_date > l_budget_start_date
and organization_id = p_organization_id;
l_rows_inserted number := 0;
hr_utility.set_location('Business group cursor selected '||l_proc,20);
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => ''
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B');
pqh_budget.insert_pos_is_bud(i.position_id);
hr_utility.set_location('position inserted '||i.position_id||l_proc,40);
hr_utility.set_location('Org hierarchy cursor selected '||l_proc,45);
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail ( p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => ''
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B');
pqh_budget.insert_pos_is_bud(i.position_id);
hr_utility.set_location('position inserted '||i.position_id||l_proc,50);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
p_rows_inserted out nocopy number) as
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_worksheets wks
where wks.budget_id = bgt.budget_id
and wks.worksheet_id = p_worksheet_id;
cursor c1 is select organization_id
from hr_organization_units
where business_group_id = p_business_group_id
and date_from < l_budget_end_date
and (date_to > l_budget_start_date or date_to is null)
and pqh_budget.already_budgeted_org(organization_id) = 'FALSE' ;
cursor c2 is select organization_id
from (select organization_id_child from pqh_worksheet_organizations_v
where org_structure_version_id = p_org_hier_ver
connect by prior organization_id_child = organization_id_parent and org_structure_version_id = p_org_hier_ver
start with organization_id_parent = p_start_organization_id and org_structure_version_id = p_org_hier_ver
union all
select p_start_organization_id organization_id_child from dual )x,
hr_organization_units
where pqh_budget.already_budgeted_org(organization_id) = 'FALSE'
and date_from < l_budget_end_date
and (date_to > l_budget_start_date or date_to is null)
and organization_id = x.organization_id_child;
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => ''
,p_position_id => ''
,p_grade_id => ''
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B');
pqh_budget.insert_org_is_bud(i.organization_id);
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => ''
,p_position_id => ''
,p_grade_id => ''
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B');
pqh_budget.insert_org_is_bud(i.organization_id);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
p_rows_inserted out nocopy number) as
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_worksheets wks
where wks.budget_id = bgt.budget_id
and wks.worksheet_id = p_worksheet_id;
cursor c1 is select hier.organization_id_child
from per_org_structure_elements hier, hr_organization_units org
where hier.org_structure_version_id = p_org_hier_ver
and org.date_from < l_budget_end_date
and (org.date_to > l_budget_start_date or org.date_to is null)
and org.organization_id = hier.organization_id_child
and hier.organization_id_parent = p_start_organization_id
and pqh_budget.already_delegated_org(hier.organization_id_child) = 'FALSE' ;
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail (
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id_child
,p_job_id => ''
,p_position_id => ''
,p_grade_id => ''
,p_position_transaction_id => ''
,p_budget_detail_id => ''
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'D'
,p_status => 'PENDING'
,p_defer_flag => ''
,p_propagation_method => p_wks_propagation_method);
pqh_budget.insert_org_is_del(i.organization_id_child);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
cursor c1 is select worksheet_detail_id,budget_detail_id,object_version_number,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_worksheet_details
where worksheet_id = p_worksheet_id
and action_cd ='B'
and budget_detail_id is not null
for update of budget_unit1_available,budget_unit2_available,budget_unit3_available;
update_worksheet_detail(
p_worksheet_detail_id => i.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_budget_unit1_available => l_budget_unit1_available,
p_budget_unit2_available => l_budget_unit2_available,
p_budget_unit3_available => l_budget_unit3_available
);
select budget_period_id,start_time_period_id,end_time_period_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
-- budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_budget_periods
where budget_detail_id = p_budget_detail_id;
select budget_set_id,dflt_budget_set_id,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_value_type_cd,budget_unit2_value_type_cd,budget_unit3_value_type_cd,
budget_unit1_percent,budget_unit2_percent,budget_unit3_percent,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_budget_sets
where budget_period_id = p_budget_period_id;
select budget_element_id,element_type_id,distribution_percentage
from pqh_budget_elements
where budget_set_id = p_budget_set_id;
select cost_allocation_keyflex_id,distribution_percentage
from pqh_budget_fund_srcs
where budget_element_id = p_budget_element_id;
select count(*) into l_count
from pqh_worksheet_periods where worksheet_detail_id = p_worksheet_detail_id;
hr_utility.set_location('period inserted '||l_proc,37);
hr_utility.set_location('after inserting '||l_proc,70);
procedure insert_org_is_del(p_org_id number) as
ins boolean := true;
l_proc varchar2(100) := g_package||'insert_org_is_del' ;
end insert_org_is_del;
procedure delete_org_is_del(p_org_id number) as
l_proc varchar2(100) := g_package||'delete_org_is_del' ;
p_what_org_is_del.DELETE(i) ;
end delete_org_is_del;
procedure delete_org_is_del is
l_proc varchar2(100) := g_package||'delete_org_is_del' ;
p_what_org_is_del.DELETE ;
end delete_org_is_del;
procedure insert_org_is_bud(p_org_id number) as
ins boolean := true;
l_proc varchar2(100) := g_package||'insert_org_is_bud' ;
end insert_org_is_bud;
procedure delete_org_is_bud(p_org_id number) as
l_proc varchar2(100) := g_package||'delete_org_is_bud' ;
p_what_org_is_bud.DELETE(i) ;
end delete_org_is_bud;
procedure delete_org_is_bud is
l_proc varchar2(100) := g_package||'delete_org_is_bud' ;
p_what_org_is_bud.DELETE ;
end delete_org_is_bud;
procedure insert_pos_is_bud(p_pos_id number) as
i number;
l_proc varchar2(100) := g_package||'insert_pos_is_bud' ;
end insert_pos_is_bud;
procedure delete_pos_is_bud(p_pos_id number) as
l_proc varchar2(100) := g_package||'delete_pos_is_bud' ;
p_what_pos_is_bud.DELETE(i);
end delete_pos_is_bud;
procedure delete_pos_is_bud is
l_proc varchar2(100) := g_package||'delete_pos_is_bud' ;
p_what_pos_is_bud.DELETE ;
end delete_pos_is_bud;
cursor c1 is select position_id,job_id,organization_id,grade_id,position_transaction_id
from pqh_worksheet_details
where parent_worksheet_detail_id = p_parent_worksheet_detail_id
and action_cd ='B';
delete_pos_is_bud;
delete_pot_is_bud;
pqh_budget.insert_pos_is_bud(i.position_id);
pqh_budget.insert_pot_is_bud(i.position_transaction_id);
delete_org_is_bud;
pqh_budget.insert_org_is_bud(i.organization_id);
delete_job_is_bud;
pqh_budget.insert_job_is_bud(i.job_id);
delete_grd_is_bud;
pqh_budget.insert_grd_is_bud(i.grade_id);
cursor c1 is select position_id,job_id,organization_id,grade_id
from pqh_budget_details
where budget_version_id = p_budget_version_id;
delete_pos_is_bud;
delete_pot_is_bud;
pqh_budget.insert_pos_is_bud(i.position_id);
delete_org_is_bud;
pqh_budget.insert_org_is_bud(i.organization_id);
delete_job_is_bud;
pqh_budget.insert_job_is_bud(i.job_id);
delete_grd_is_bud;
pqh_budget.insert_grd_is_bud(i.grade_id);
cursor c1 is select organization_id
from pqh_worksheet_details
where parent_worksheet_detail_id = p_parent_worksheet_detail_id
and action_cd ='D' ;
delete_org_is_del;
pqh_budget.insert_org_is_del(i.organization_id);
procedure insert_pot_is_bud(p_pot_id number) as
i number;
l_proc varchar2(100) := g_package||'insert_pot_is_bud' ;
end insert_pot_is_bud;
procedure delete_pot_is_bud(p_pot_id number) as
l_proc varchar2(100) := g_package||'delete_pot_is_bud' ;
p_what_pot_is_bud.DELETE(i);
end delete_pot_is_bud;
procedure delete_pot_is_bud is
l_proc varchar2(100) := g_package||'delete_pot_is_bud' ;
p_what_pot_is_bud.DELETE ;
end delete_pot_is_bud;
procedure insert_job_is_bud(p_job_id number) as
ins boolean := true;
l_proc varchar2(100) := g_package||'insert_job_is_bud' ;
hr_utility.set_location('match not exists inserting'||l_proc,50);
end insert_job_is_bud;
procedure delete_job_is_bud(p_job_id number) as
l_proc varchar2(100) := g_package||'delete_job_is_bud' ;
p_what_job_is_bud.DELETE(i) ;
end delete_job_is_bud;
procedure delete_job_is_bud is
l_proc varchar2(100) := g_package||'delete_job_is_bud' ;
p_what_job_is_bud.DELETE ;
end delete_job_is_bud;
procedure insert_grd_is_bud(p_grd_id number) as
i number;
l_proc varchar2(100) := g_package||'insert_grd_is_bud' ;
end insert_grd_is_bud;
procedure delete_grd_is_bud(p_grd_id number) as
l_proc varchar2(100) := g_package||'delete_grd_is_bud' ;
p_what_grd_is_bud.DELETE(i) ;
end delete_grd_is_bud;
procedure delete_grd_is_bud is
l_proc varchar2(100) := g_package||'delete_grd_is_bud' ;
p_what_grd_is_bud.DELETE ;
end delete_grd_is_bud;
cursor c1 is select worksheet_detail_id,propagation_method,object_version_number,
old_unit1_value,old_unit2_value,old_unit3_value,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and action_cd ='D'
for update of old_unit1_value,old_unit2_value,old_unit3_value,
budget_unit1_available,budget_unit2_available,budget_unit3_available;
update_worksheet_detail(
p_worksheet_detail_id => i.worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_budget_unit1_available => l_budget_unit1_available,
p_budget_unit2_available => l_budget_unit2_available,
p_budget_unit3_available => l_budget_unit3_available,
p_old_unit1_value => null,
p_old_unit2_value => null,
p_old_unit3_value => null);
cursor c1 is select period_set_name,budget_start_date,budget_end_date
from pqh_budgets
where budget_id = p_budget_id;
cursor c2 is select start_date
from per_time_periods
where period_set_name = l_period_set_name
and start_date >= l_prd_start_date
and end_date <= l_prd_end_date ;
p_prd_unit_tab.delete;
hr_utility.set_location('delete of table failed'||l_proc,42);
cursor c1 is select tps.start_date prd_start_date,tpe.end_date prd_end_date,
prd.budget_unit1_value unit1_value,prd.budget_unit2_value unit2_value,
prd.budget_unit3_value unit3_value
from pqh_worksheet_periods prd, per_time_periods tps, per_time_periods tpe
where prd.worksheet_detail_id = p_worksheet_detail_id
and prd.start_time_period_id = tps.time_period_id
and prd.end_time_period_id = tpe.time_period_id;
cursor c1 is select tps.start_date prd_start_date,tpe.end_date prd_end_date,
prd.budget_unit1_value unit1_value,prd.budget_unit2_value unit2_value,
prd.budget_unit3_value unit3_value
from pqh_worksheet_periods prd, per_time_periods tps, per_time_periods tpe
where prd.worksheet_detail_id = p_worksheet_detail_id
and prd.start_time_period_id = tps.time_period_id
and prd.end_time_period_id = tpe.time_period_id;
cursor c1 is select tps.start_date prd_start_date,tpe.end_date prd_end_date,
prd.budget_unit1_value unit1_value,prd.budget_unit2_value unit2_value,
prd.budget_unit3_value unit3_value
from pqh_budget_periods prd, per_time_periods tps, per_time_periods tpe
where prd.budget_detail_id = p_budget_detail_id
and prd.start_time_period_id = tps.time_period_id
and prd.end_time_period_id = tpe.time_period_id;
cursor c1 is select tps.start_date prd_start_date,tpe.end_date prd_end_date,
prd.budget_unit1_value unit1_value,prd.budget_unit2_value unit2_value,
prd.budget_unit3_value unit3_value
from pqh_budget_periods prd, per_time_periods tps, per_time_periods tpe
where prd.budget_detail_id = p_budget_detail_id
and prd.start_time_period_id = tps.time_period_id
and prd.end_time_period_id = tpe.time_period_id;
procedure insert_worksheet_detail(
p_worksheet_id in number,
p_organization_id in number default null,
p_job_id in number default null,
p_position_id in number default null,
p_grade_id in number default null,
p_position_transaction_id in number default null,
p_budget_detail_id in number default null,
p_parent_worksheet_detail_id in number default null,
p_user_id in number default null,
p_action_cd in varchar2 default null,
p_budget_unit1_percent in number default null,
p_budget_unit1_value in number default null,
p_budget_unit2_percent in number default null,
p_budget_unit2_value in number default null,
p_budget_unit3_percent in number default null,
p_budget_unit3_value in number default null,
p_budget_unit1_value_type_cd in varchar2 default null,
p_budget_unit2_value_type_cd in varchar2 default null,
p_budget_unit3_value_type_cd in varchar2 default null,
p_status in varchar2 default null,
p_budget_unit1_available in number default null,
p_budget_unit2_available in number default null,
p_budget_unit3_available in number default null,
p_old_unit1_value in number default null,
p_old_unit2_value in number default null,
p_old_unit3_value in number default null,
p_defer_flag in varchar2 default null,
p_propagation_method in varchar2 default null,
p_worksheet_detail_id out nocopy number,
p_copy_budget_periods in varchar2 default 'N'
) is
l_object_version_number number;
end insert_worksheet_detail;
Procedure update_worksheet_detail
(
p_effective_date in date,
p_worksheet_detail_id in number,
p_worksheet_id in number default hr_api.g_number,
p_organization_id in number default hr_api.g_number,
p_job_id in number default hr_api.g_number,
p_position_id in number default hr_api.g_number,
p_grade_id in number default hr_api.g_number,
p_position_transaction_id in number default hr_api.g_number,
p_budget_detail_id in number default hr_api.g_number,
p_parent_worksheet_detail_id in number default hr_api.g_number,
p_user_id in number default hr_api.g_number,
p_action_cd in varchar2 default hr_api.g_varchar2,
p_budget_unit1_percent in number default hr_api.g_number,
p_budget_unit1_value in number default hr_api.g_number,
p_budget_unit2_percent in number default hr_api.g_number,
p_budget_unit2_value in number default hr_api.g_number,
p_budget_unit3_percent in number default hr_api.g_number,
p_budget_unit3_value in number default hr_api.g_number,
p_object_version_number in out nocopy number,
p_budget_unit1_value_type_cd in varchar2 default hr_api.g_varchar2,
p_budget_unit2_value_type_cd in varchar2 default hr_api.g_varchar2,
p_budget_unit3_value_type_cd in varchar2 default hr_api.g_varchar2,
p_status in varchar2 default hr_api.g_varchar2,
p_budget_unit1_available in number default hr_api.g_number,
p_budget_unit2_available in number default hr_api.g_number,
p_budget_unit3_available in number default hr_api.g_number,
p_old_unit1_value in number default hr_api.g_number,
p_old_unit2_value in number default hr_api.g_number,
p_old_unit3_value in number default hr_api.g_number,
p_defer_flag in varchar2 default hr_api.g_varchar2,
p_propagation_method in varchar2 default hr_api.g_varchar2
) as
l_proc varchar2(61) := g_package||'Update_wkd';
pqh_worksheet_details_api.update_worksheet_detail(
p_validate => FALSE
,p_worksheet_detail_id => p_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => p_organization_id
,p_position_id => p_position_id
,p_job_id => p_job_id
,p_grade_id => p_grade_id
,p_position_transaction_id => p_position_transaction_id
,p_budget_detail_id => p_budget_detail_id
,p_user_id => p_user_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_action_cd => p_action_cd
,p_budget_unit1_value => p_budget_unit1_value
,p_budget_unit1_percent => p_budget_unit1_percent
,p_budget_unit1_available => p_budget_unit1_available
,p_old_unit1_value => p_old_unit1_value
,p_budget_unit1_value_type_cd => p_budget_unit1_value_type_cd
,p_budget_unit2_value => p_budget_unit2_value
,p_budget_unit2_percent => p_budget_unit2_percent
,p_budget_unit2_available => p_budget_unit2_available
,p_old_unit2_value => p_old_unit2_value
,p_budget_unit2_value_type_cd => p_budget_unit2_value_type_cd
,p_budget_unit3_value => p_budget_unit3_value
,p_budget_unit3_percent => p_budget_unit3_percent
,p_budget_unit3_available => p_budget_unit3_available
,p_old_unit3_value => p_old_unit3_value
,p_budget_unit3_value_type_cd => p_budget_unit3_value_type_cd
,p_status => p_status
,p_defer_flag => p_defer_flag
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(sysdate)
,p_propagation_method => p_propagation_method
);
end update_worksheet_detail;
after inserting the budget data, the periods and other details are also to be copied. In
doing this the available figures of the worksheet detail will also change.
if copy_budget_periods is yes then
call to copy_budget_details is to be made.
update worksheet_detail for available figures.
end if;
hr_utility.set_location('going for update_wkd with ovn'||l_object_version_number||l_proc,70);
update_worksheet_detail(
p_worksheet_detail_id => p_worksheet_detail_id,
p_effective_date => trunc(sysdate),
p_object_version_number => l_object_version_number,
p_budget_unit1_available => l_budget_unit1_available,
p_budget_unit2_available => l_budget_unit2_available,
p_budget_unit3_available => l_budget_unit3_available
);
hr_utility.set_location('after update_wkd with ovn'||l_object_version_number||l_proc,80);
This copy budget_details is called from insert-from_budget procedure when copy_budget_periods is enable
this is a local procedure only. There exists another procedure with the same name which is also in header with
different signature , which copies values as well.
Value coping will be going away after some time.
*/
procedure copy_budget_details(p_budget_detail_id in number,
p_worksheet_detail_id in number) is
cursor c1(p_budget_detail_id number) is
select budget_period_id,start_time_period_id,end_time_period_id
from pqh_budget_periods
where budget_detail_id = p_budget_detail_id;
select budget_set_id,dflt_budget_set_id
from pqh_budget_sets
where budget_period_id = p_budget_period_id;
select budget_element_id,element_type_id,distribution_percentage
from pqh_budget_elements
where budget_set_id = p_budget_set_id;
select cost_allocation_keyflex_id,distribution_percentage
from pqh_budget_fund_srcs
where budget_element_id = p_budget_element_id;
select count(*) into l_count
from pqh_worksheet_periods where worksheet_detail_id = p_worksheet_detail_id;
hr_utility.set_location('period inserted '||l_proc,37);
procedure insert_from_budget(p_budget_version_id in number,
p_budgeted_entity_cd in varchar,
p_worksheet_id in number,
p_business_group_id in number,
p_start_organization_id in number,
p_parent_worksheet_detail_id in number,
p_org_hier_ver in number,
p_copy_budget_periods in varchar2,
p_rows_inserted out nocopy number) IS
cursor c1 is select position_id , grade_id, bud.organization_id organization_id, job_id,budget_detail_id
from pqh_budget_details bud, hr_organization_units org
where org.business_group_id = p_business_group_id
and bud.organization_id = org.organization_id
and pqh_budget.already_budgeted_pos(bud.position_id) = 'FALSE'
and bud.budget_version_id = p_budget_version_id;
cursor c2 is select bud.position_id, bud.grade_id, bud.organization_id , bud.job_id,bud.budget_detail_id
from (select organization_id_child from pqh_worksheet_organizations_v
where org_structure_version_id = p_org_hier_ver
connect by prior organization_id_child = organization_id_parent and org_structure_version_id = p_org_hier_ver
start with organization_id_parent = p_start_organization_id and org_structure_version_id = p_org_hier_ver
union all
select p_start_organization_id organization_id_child from dual )x
, pqh_budget_details bud
where pqh_budget.already_budgeted_pos(bud.position_id) = 'FALSE'
and bud.budget_version_id = p_budget_version_id
and bud.organization_id = x.organization_id_child;
cursor c3 is select position_id , grade_id, bud.organization_id organization_id, job_id,budget_detail_id
from pqh_budget_details bud, hr_organization_units org
where org.business_group_id = p_business_group_id
and bud.organization_id = org.organization_id
and pqh_budget.already_budgeted_org(bud.organization_id) = 'FALSE'
and bud.budget_version_id = p_budget_version_id;
cursor c4 is select position_id, grade_id, organization_id , job_id,budget_detail_id
from (select organization_id_child from pqh_worksheet_organizations_v
where org_structure_version_id = p_org_hier_ver
connect by prior organization_id_child = organization_id_parent and org_structure_version_id = p_org_hier_ver
start with organization_id_parent = p_start_organization_id and org_structure_version_id = p_org_hier_ver
union all
select p_start_organization_id organization_id_child from dual )x
, pqh_budget_details
where pqh_budget.already_budgeted_org(organization_id) = 'FALSE'
and budget_version_id = p_budget_version_id
and organization_id = x.organization_id_child;
cursor c5 is select position_id ,grade_id, organization_id , job_id,budget_detail_id
from pqh_budget_details
where pqh_budget.already_budgeted_job(job_id) = 'FALSE'
and budget_version_id = p_budget_version_id;
cursor c6 is select position_id ,grade_id, organization_id , job_id,budget_detail_id
from pqh_budget_details
where pqh_budget.already_budgeted_grd(grade_id) = 'FALSE'
and budget_version_id = p_budget_version_id;
cursor c7 is select position_id ,grade_id, organization_id , job_id,budget_detail_id
from pqh_budget_details
where budget_version_id = p_budget_version_id;
l_rows_inserted number := 0;
l_proc varchar2(100) := g_package||'insert_from_budget' ;
l_rows_inserted := l_rows_inserted + 1;
hr_utility.set_location('inserting into plsql table'||l_proc,70);
pqh_budget.insert_pos_is_bud(i.position_id);
hr_utility.set_location('inserting into worksheet_detail table'||l_proc,80);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('insert worksheet_detail table complete'||l_proc,90);
hr_utility.set_location('before insert loop '||l_proc,135);
hr_utility.set_location('inside insert loop '||l_proc,140);
l_rows_inserted := l_rows_inserted + 1;
hr_utility.set_location('going for insert '||l_proc,148);
pqh_budget.insert_pos_is_bud(i.position_id);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('row inserted going for period copy'||l_proc,150);
hr_utility.set_location('before insert loop '||l_proc,190);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_org_is_bud(i.organization_id);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,200);
hr_utility.set_location('before insert loop '||l_proc,230);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_org_is_bud(i.organization_id);
insert_worksheet_detail(p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,240);
hr_utility.set_location('before insert loop'||l_proc,270);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_job_is_bud(i.job_id);
insert_worksheet_detail
(
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,280);
hr_utility.set_location('before insert loop '||l_proc,310);
l_rows_inserted := l_rows_inserted + 1;
pqh_budget.insert_grd_is_bud(i.grade_id);
insert_worksheet_detail
(
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,320);
hr_utility.set_location('before insert loop '||l_proc,350);
l_rows_inserted := l_rows_inserted + 1;
insert_worksheet_detail
(
p_worksheet_detail_id => l_worksheet_detail_id
,p_worksheet_id => p_worksheet_id
,p_organization_id => i.organization_id
,p_job_id => i.job_id
,p_position_id => i.position_id
,p_grade_id => i.grade_id
,p_position_transaction_id => ''
,p_budget_detail_id => i.budget_detail_id
,p_parent_worksheet_detail_id => p_parent_worksheet_detail_id
,p_user_id => ''
,p_action_cd => 'B'
,p_copy_budget_periods => p_copy_budget_periods );
hr_utility.set_location('after insert '||l_proc,360);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
end insert_from_budget;
SELECT currency_code
FROM per_business_groups
WHERE business_group_id = l_business_group_id;
SELECT org_information10
FROM hr_organization_information
WHERE organization_id = l_business_group_id;
SELECT org_information10
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_business_group_id
AND hoi.org_information_context = 'Business Group Information'
AND hoi.org_information2 IS NOT NULL
AND EXISTS
( SELECT NULL
FROM hr_org_info_types_by_class oitbc,
hr_organization_information org_info
WHERE org_info.organization_id = hoi.organization_id
AND org_info.org_information_context = 'CLASS'
AND org_info.org_information2 = 'Y'
AND oitbc.org_classification = org_info.org_information1
AND oitbc.org_information_type = 'Business Group Information'
);
SELECT currency_code,business_group_id
FROM pqh_budgets bgt
WHERE bgt.budget_id = p_budget_id;
cursor c1 is select tps.start_date prd_start_date,tpe.end_date prd_end_date,
prd.budget_unit1_value unit1_value,prd.budget_unit2_value unit2_value,
prd.budget_unit3_value unit3_value
from pqh_budget_periods prd, per_time_periods tps, per_time_periods tpe
where prd.budget_detail_id = p_budget_detail_id
and prd.start_time_period_id = tps.time_period_id
and prd.end_time_period_id = tpe.time_period_id;
cursor c1 is select tps.start_date prd_start_date,tpe.end_date prd_end_date,
prd.budget_unit1_value unit1_value,prd.budget_unit2_value unit2_value,
prd.budget_unit3_value unit3_value
from pqh_worksheet_periods prd, per_time_periods tps, per_time_periods tpe
where prd.worksheet_detail_id = p_worksheet_detail_id
and prd.start_time_period_id = tps.time_period_id
and prd.end_time_period_id = tpe.time_period_id;