[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c1 is select precision
from fnd_currencies
where currency_code = p_currency_code;
cursor c1 is select 'x' from per_valid_grades
where (position_id = p_position_id or position_id is null)
and (job_id = p_job_id or job_id is null)
and grade_id = p_grade_id ;
cursor c1 is select parent_worksheet_detail_id from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id;
cursor c1 is select status from pqh_worksheet_details
where action_cd ='D'
and parent_worksheet_detail_id = p_worksheet_detail_id;
cursor c1 is select budget_unit1_id,budget_unit2_id,budget_unit3_id
from pqh_budgets bgt,pqh_worksheets wks, pqh_worksheet_details wkd
where wkd.worksheet_id = wks.worksheet_id
and wks.budget_id = bgt.budget_id
and wkd.worksheet_detail_id = p_worksheet_detail_id;
cursor c1 is select ors.name
from per_org_structure_versions osv, per_organization_structures ors
where osv.organization_structure_id = ors.organization_structure_id
and osv.org_structure_version_id = p_org_structure_version_id;
cursor c1 is select system_type_cd
from per_shared_types
where lookup_type ='BUDGET_MEASUREMENT_TYPE'
and shared_type_id = p_unit_id;
cursor c1 is select shared_type_name
from per_shared_types_vl
where lookup_type ='BUDGET_MEASUREMENT_TYPE'
and shared_type_id = p_unit_id;
select parent_worksheet_detail_id,worksheet_id
from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id ;
select propagation_method,worksheet_detail_id
from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id ;
select propagation_method
from pqh_worksheets
where worksheet_id = p_worksheet_id ;
cursor c1 is select nvl(description,meaning) description
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
cursor c0 is select worksheet_id,propagation_method from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id ;
cursor c1 is select propagation_method from pqh_worksheets
where worksheet_id = l_worksheet_id ;
cursor c1 is select currency_code,budget_unit1_id,budget_unit2_id,budget_unit3_id
from pqh_budgets
where budget_id = p_budget_id;
cursor c1 is select budget_id
from pqh_worksheets
where worksheet_id = p_worksheet_id;
cursor c1 is select wks.budget_id
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 bgt.budget_unit1_aggregate,bgt.budget_unit2_aggregate,bgt.budget_unit3_aggregate
from pqh_worksheets wks, pqh_budgets bgt
where wks.worksheet_id = p_worksheet_id
and wks.budget_id = bgt.budget_id;
cursor c1 is select bgt.budget_unit1_aggregate,bgt.budget_unit2_aggregate,bgt.budget_unit3_aggregate
from pqh_worksheets wks, pqh_worksheet_details wkd, pqh_budgets bgt
where wks.worksheet_id = wkd.worksheet_id
and wks.budget_id = bgt.budget_id
and wkd.worksheet_detail_id = p_worksheet_detail_id;
procedure insert_budgetset(p_dflt_budget_set_id number,
p_worksheet_budget_set_id number) IS
cursor c1 is select dflt_budget_element_id,element_type_id,dflt_dist_percentage
from pqh_dflt_budget_elements pbe
where dflt_budget_set_id = p_dflt_budget_set_id ;
select project_id, award_id, task_id,
organization_id, expenditure_type,
cost_allocation_keyflex_id,dflt_dist_percentage
from pqh_dflt_fund_srcs
where dflt_budget_element_id = p_dflt_budget_element_id ;
select count(*) into l_count from pqh_worksheet_bdgt_elmnts
where worksheet_budget_set_id = p_worksheet_budget_set_id ;
end insert_budgetset;
procedure insert_budgetset(p_dflt_budget_set_id number,
p_budget_set_id number) IS
cursor c1 is select dflt_budget_element_id,element_type_id,dflt_dist_percentage
from pqh_dflt_budget_elements pbe
where dflt_budget_set_id = p_dflt_budget_set_id ;
select project_id, award_id, task_id,
organization_id, expenditure_type,
cost_allocation_keyflex_id,dflt_dist_percentage
from pqh_dflt_fund_srcs
where dflt_budget_element_id = p_dflt_budget_element_id ;
select count(*) into l_count from pqh_budget_elements
where budget_set_id = p_budget_set_id ;
end insert_budgetset;
cursor c1 is select worksheet_detail_id,user_id,status,defer_flag,object_version_number
from pqh_worksheet_details
where action_cd ='D'
and parent_worksheet_detail_id = p_worksheet_detail_id
and nvl(defer_flag,'N') = 'N'
and user_id is not null
and organization_id is not null
and status = 'DELEGATE'
for update of status;
select user_name
from fnd_user
where user_id = p_user_id ;
hr_utility.set_location('going to update status'||l_proc,60);
pqh_budget.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 => 'DELEGATED'
);
hr_utility.set_location('updated status'||l_proc,60);
cursor c0 is select max(version_number) from pqh_budget_versions
where budget_id = p_budget_id ;
cursor c1 is select 'x' from pqh_budget_versions
where budget_version_id = p_budget_version_id
and budget_id = p_budget_id;
cursor c2 is select date_to from pqh_budget_versions
where version_number = l_max_version
and budget_id = p_budget_id;
cursor c3 is select version_number from pqh_budget_versions
where budget_version_id = p_budget_version_id;
cursor c1 is select worksheet_detail_id,object_version_number,
budget_unit1_value,budget_unit2_value,budget_unit3_value,
old_unit1_value,old_unit2_value,old_unit3_value
from pqh_worksheet_details
where parent_worksheet_detail_id = p_worksheet_detail_id
and action_cd ='D';
pqh_budget.update_worksheet_detail(
p_worksheet_detail_id => i.worksheet_detail_id,
p_object_version_number => l_object_version_number,
p_effective_date => trunc(sysdate),
p_budget_unit1_value => l_budget_unit1_value,
p_budget_unit2_value => l_budget_unit2_value,
p_budget_unit3_value => l_budget_unit3_value,
p_old_unit1_value => l_budget_unit1_value,
p_old_unit2_value => l_budget_unit2_value,
p_old_unit3_value => l_budget_unit3_value);
p_rows_inserted out nocopy number) is
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 bgt, pqh_budget_versions bgv
where bgv.budget_id = bgt.budget_id
and bgv.budget_version_id = p_budget_version_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_budgeted_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)))
and pqh_budget.already_budgeted_grd(a.grade_id) = 'FALSE' ;
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
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) is
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_budget_versions bgv
where bgv.budget_id = bgt.budget_id
and bgv.budget_version_id = p_budget_version_id;
cursor c1 is select job_id from per_jobs job, per_job_groups jgr
where job.job_group_id = jgr.job_group_id and jgr.internal_name = 'HR_' || job.business_group_id
and job.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_job(job_id) = 'FALSE';
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
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) is
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_budget_versions bgv
where bgv.budget_id = bgt.budget_id
and bgv.budget_version_id = p_budget_version_id;
cursor c1 is select position_id,job_id,pos.organization_id organization_id
from hr_positions pos,hr_organization_units org
where org.business_group_id = p_business_group_id
and pos.business_group_id = p_business_group_id
and pos.organization_id = org.organization_id
and pos.effective_start_date < l_budget_end_date
and pos.effective_end_date > l_budget_start_date
and pqh_budget.already_budgeted_pos(position_id) = 'FALSE'
and get_position_budget_flag(pos.availability_status_id) = 'Y';
cursor c2 is select distinct --Added Distinct to eliminate duplicates from the cursor to fix the bug#10284825.
position_id,job_id,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
select p_start_organization_id organization_id_child from dual )x,
hr_positions_f
where pqh_budget.already_budgeted_pos(position_id) = 'FALSE'
and get_position_budget_flag(availability_status_id) = 'Y'
and effective_start_date < l_budget_end_date
and effective_end_date > l_budget_start_date
and organization_id = x.organization_id_child ;
l_rows_inserted number := 0;
hr_utility.set_location('Business group cursor selected '||l_proc,20);
l_rows_inserted := l_rows_inserted + 1;
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;
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) is
l_budget_start_date date;
cursor c0 is select budget_start_date,budget_end_date
from pqh_budgets bgt, pqh_budget_versions bgv
where bgv.budget_id = bgt.budget_id
and bgv.budget_version_id = p_budget_version_id;
cursor c1 is select organization_id
from hr_all_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 DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', ORGANIZATION_ID))='TRUE'
--and decode(hr_general.get_xbg_profile,'Y', business_group_id , hr_general.get_business_group_id) = business_group_id
and pqh_budget.already_budgeted_org(organization_id) = 'FALSE';
cursor c2 is select w.organization_id_child organization_id
from pqh_worksheet_organizations_v w
where org_structure_version_id = p_org_hier_ver
and pqh_budget.already_budgeted_org(w.organization_id_child) = 'FALSE'
and exists
(select null
from hr_all_organization_units hao
where organization_id = w.organization_id_child
and date_from < l_budget_end_date
and (date_to > l_budget_start_date or date_to is null)
and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID))='TRUE' )
--AND decode(hr_general.get_xbg_profile,'Y', hao.business_group_id , hr_general.get_business_group_id) = hao.business_group_id)
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
select organization_id
from hr_all_organization_units hao
where organization_id = p_start_organization_id
and pqh_budget.already_budgeted_org(p_start_organization_id) = 'FALSE'
and date_from < l_budget_end_date
and (date_to > l_budget_start_date or date_to is null)
and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID))='TRUE' ;
l_rows_inserted number := 0;
l_rows_inserted := l_rows_inserted + 1;
hr_utility.set_location('inserting '||i.organization_id||l_proc,30);
pqh_budget.insert_org_is_bud(i.organization_id);
l_rows_inserted := l_rows_inserted + 1;
hr_utility.set_location('inserting '||i.organization_id||l_proc,50);
pqh_budget.insert_org_is_bud(i.organization_id);
p_rows_inserted := l_rows_inserted;
p_rows_inserted := null;
cursor c1 is select budget_id from pqh_worksheets
where worksheet_id = p_worksheet_id;
cursor c1 is select worksheet_id from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id;
cursor c1 is select budget_id
from pqh_budget_versions bgv, pqh_budget_details bgd
where bgd.budget_detail_id = p_budget_detail_id
and bgd.budget_version_id = bgv.budget_version_id ;
procedure insert_default_period(p_worksheet_detail_id in number,
p_wkd_ovn in out nocopy number,
p_worksheet_unit1_value in number default null,
p_worksheet_unit2_value in number default null,
p_worksheet_unit3_value in number default null,
p_worksheet_period_id out nocopy number,
p_wpr_ovn out nocopy number) is
l_wkd_ovn number := p_wkd_ovn;
select time_period_id,start_date
from per_time_periods
where period_set_name = l_calendar
and start_date >= l_budget_start_date
and start_date < l_budget_end_date
order by start_date;
select time_period_id,end_date
from per_time_periods
where period_set_name = l_calendar
and end_date > l_budget_start_date
and end_date <= l_budget_end_date
and end_date > l_period_start_date
order by end_date desc;
l_proc varchar2(100) := g_package||'insert_default_period' ;
select period_set_name,budget_start_date,budget_end_date
into l_calendar,l_budget_start_date,l_budget_end_date
from pqh_budgets
where budget_id = l_budget_id;
pqh_budget.update_worksheet_detail(
p_worksheet_detail_id => p_worksheet_detail_id,
p_object_version_number => p_wkd_ovn,
p_effective_date => trunc(sysdate),
p_budget_unit1_available => p_worksheet_unit1_value,
p_budget_unit2_available => p_worksheet_unit1_value,
p_budget_unit3_available => p_worksheet_unit1_value);
hr_utility.set_location('inserting worksheet period '||l_proc,100);
end insert_default_period;
select wks.worksheet_name,wkd.organization_id
from pqh_worksheets wks, pqh_worksheet_details wkd
where wkd.worksheet_id = wks.worksheet_id
and worksheet_detail_id = p_worksheet_detail_id
and nvl(action_cd,'D') ='D';
Cursor C_position is select valid_grade_id
from per_valid_grades
where position_id = p_position_id
and grade_id = p_grade_id
and rownum < 2;
Cursor C_job is select valid_grade_id
from per_valid_grades
where job_id = p_job_id
and grade_id = p_grade_id
and rownum < 2;
Cursor C_job is select valid_grade_id
from per_valid_grades
where job_id = l_job_id
and grade_id = p_grade_id
and date_from < p_end_bud_date
and (date_to > p_start_bud_date or date_to is null)
and rownum < 2;
Cursor C_position is select valid_grade_id
from per_valid_grades
where position_id = p_position_id
and grade_id = p_grade_id
and date_from < p_end_bud_date
and (date_to > p_start_bud_date or date_to is null)
and rownum < 2;
Cursor C2 is select valid_grade_id
from per_valid_grades
where grade_id = p_grade_id
and date_from < p_end_bud_date
and (date_to > p_start_bud_date or date_to is null)
and rownum < 2;
Select nvl(information1,'Y')
from per_shared_types
where lookup_type = 'POSITION_AVAILABILITY_STATUS'
and shared_type_id = p_availability_status_id;
select organization_id,job_id,effective_start_date,effective_end_date
into l_org_id,l_job_id,l_pos_start_date,l_pos_end_date
from pqh_position_transactions
where position_transaction_id = p_position_transaction_id;
procedure update_wkd_pot(p_worksheet_detail_id number) is
begin
update pqh_worksheet_details
set position_transaction_id = null
where worksheet_detail_id = p_worksheet_detail_id;
end update_wkd_pot;
cursor c_worksheet_periods is select worksheet_period_id,object_version_number
from pqh_worksheet_periods where worksheet_detail_id = p_worksheet_detail_id;
select worksheet_budget_set_id,object_version_number
from pqh_worksheet_budget_sets where worksheet_period_id = p_worksheet_period_id;
select worksheet_bdgt_elmnt_id,object_version_number
from pqh_worksheet_bdgt_elmnts where worksheet_budget_set_id = p_worksheet_budget_set_id;
select worksheet_fund_src_id,object_version_number
from pqh_worksheet_fund_srcs where worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
pqh_worksheet_fund_srcs_api.DELETE_WORKSHEET_FUND_SRC(
P_WORKSHEET_FUND_SRC_ID => l.worksheet_fund_src_id,
P_OBJECT_VERSION_NUMBER => l.object_version_number);
pqh_worksheet_bdgt_elmnts_api.DELETE_WORKSHEET_BDGT_ELMNT(
P_WORKSHEET_BDGT_ELMNT_ID => k.worksheet_bdgt_elmnt_id,
P_OBJECT_VERSION_NUMBER => k.object_version_number);
pqh_worksheet_budget_sets_api.DELETE_WORKSHEET_BUDGET_SET(
P_WORKSHEET_BUDGET_SET_ID => j.worksheet_budget_set_id,
P_EFFECTIVE_DATE => trunc(sysdate),
P_OBJECT_VERSION_NUMBER => j.object_version_number);
pqh_worksheet_periods_api.DELETE_WORKSHEET_PERIOD(
P_WORKSHEET_PERIOD_ID => i.worksheet_period_id,
P_EFFECTIVE_DATE => trunc(sysdate),
P_OBJECT_VERSION_NUMBER => i.object_version_number);
select parent_worksheet_detail_id,budget_unit1_value,budget_unit2_value,budget_unit3_value,object_version_number
into l_parent_wkd_id,l_budget_unit1_value,l_budget_unit2_value,l_budget_unit3_value,l_object_version_number
from pqh_worksheet_details where worksheet_detail_id = p_worksheet_detail_id;
update pqh_worksheet_details
set budget_unit1_available = nvl(budget_unit1_available,0) - nvl(l_budget_unit1_value,0),
budget_unit2_available = nvl(budget_unit2_available,0) - nvl(l_budget_unit2_value,0),
budget_unit3_available = nvl(budget_unit3_available,0) - nvl(l_budget_unit3_value,0)
where worksheet_detail_id = l_parent_wkd_id;
update pqh_worksheet_details
set budget_unit1_value = nvl(budget_unit1_value,0) - nvl(l_budget_unit1_value,0),
budget_unit2_value = nvl(budget_unit2_value,0) - nvl(l_budget_unit2_value,0),
budget_unit3_value = nvl(budget_unit3_value,0) - nvl(l_budget_unit3_value,0)
where worksheet_detail_id = l_parent_wkd_id;
pqh_worksheet_details_api.DELETE_WORKSHEET_DETAIL(
P_WORKSHEET_DETAIL_ID => p_worksheet_detail_id,
P_EFFECTIVE_DATE => trunc(sysdate),
P_OBJECT_VERSION_NUMBER => l_object_version_number);
procedure delete_wkd(p_worksheet_detail_id in number,
p_object_version_number in number) is
l_proc varchar2(100) := g_package||'delete_wkd' ;
select position_id,worksheet_detail_id
from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id;
Position transaction should be updated to null
else
delete the dependent records of worksheet period, budgetsets etc.
delete the worksheet_detail
update the parent worksheet_detail balances
end if;
delete the dependent records of worksheet period, budgetsets etc.
delete the worksheet_detail
update the parent worksheet_detail balances
end if;
select budgeted_entity_cd,budget_style_cd into l_budgeted_entity_cd,l_budget_style_cd
from pqh_budgets where budget_id = l_budget_id;
update_wkd_pot(p_worksheet_detail_id => p_worksheet_detail_id);
end delete_wkd;
cursor gms is select a.status, a.application_id, b.application_short_name
from
fnd_product_installations a, fnd_application b
where
a.application_id = b.application_id
and
b.application_short_name = 'GMS' and status = 'I';