[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select expenditure_category
into l_expenditure_category
from igw_expenditure_types_v
where expenditure_type = p_expenditure_type;
select '1'
into l_dummy
from igw_exp_type_rate_types
where expenditure_category = l_expenditure_category /* p_expenditure_type */
and rate_class_id = p_rate_class_id
and rate_type_id = p_rate_type_id
and rownum < 2;
select budget_period_id
, start_date
, end_date
from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = p_version_id;
select pbd.line_item_id
, pbd.expenditure_type
, pbd.expenditure_category_flag
, pbd.apply_inflation_flag
, pbd.line_item_cost
, pbd.cost_sharing_amount
, pbd.location_code
, et.personnel_attached_flag
, pbd.budget_period_id
, pbd.proposal_id
, pbd.version_id
from igw_budget_details pbd
, igw_budget_expenditures_v et
where pbd.expenditure_type = et.budget_expenditure
and pbd.expenditure_category_flag = et.expenditure_category_flag
and pbd.proposal_id = p_proposal_id
and pbd.budget_period_id = l_budget_period_id
and pbd.version_id = p_version_id;
select budget_personnel_detail_id
, start_date
, end_date
, percent_charged
, cost_sharing_percent
, person_id
, appointment_type_code
from igw_budget_personnel_details
where line_item_id = l_line_item_id;
select apply_rate_flag
into l_apply_rate_flag_oh
from igw_budget_details_cal_amts
where line_item_id = rec_budget_details.line_item_id;
update igw_budget_details
set line_item_cost = l_base_amount
, underrecovery_amount = nvl(l_oh_value - l_oh_value_ov,0)
where line_item_id = rec_budget_details.line_item_id;
delete from igw_budget_details_cal_amts
where line_item_id = rec_budget_details.line_item_id;
select pca.apply_rate_flag
into l_apply_rate_flag_oh
from igw_budget_personnel_cal_amts pca
, igw_budget_personnel_details pbd
where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
and pca.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pca.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'O');
select pca.apply_rate_flag
into l_apply_rate_flag_eb
from igw_budget_personnel_cal_amts pca
, igw_budget_personnel_details pbd
where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
and pca.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pca.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'E');
select calculation_base
, effective_date
, appointment_type_code
into l_calculation_base
, l_effective_date
, l_appointment_type_code
from igw_budget_persons
where proposal_id = p_proposal_id
and version_id = p_version_id
and person_id = rec_budget_personnel.person_id
and appointment_type_code = rec_budget_personnel.appointment_type_code;
update igw_budget_personnel_details
set salary_requested = l_inflated_salary_ov
, cost_sharing_amount = rec_budget_personnel.cost_sharing_percent/100 *
l_inflated_salary_ov
, underrecovery_amount = nvl(l_oh_value_d - l_oh_value_ov,0)
where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
delete from igw_budget_personnel_cal_amts
where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
delete from igw_budget_details_cal_amts
where line_item_id = rec_budget_details.line_item_id;
select sum(nvl(ppc.calculated_cost,0))
, sum(nvl(ppc.calculated_cost_sharing,0))
into l_calculated_cost_oh
, l_cost_sharing_oh
from igw_budget_personnel_cal_amts ppc
, igw_budget_personnel_details ppd
where ppd.line_item_id = rec_budget_details.line_item_id
and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
and ppc.rate_class_id = l_rate_class_id_oh
and ppc.rate_type_id = l_rate_type_id_oh;
select sum(nvl(ppc.calculated_cost,0))
, sum(nvl(ppc.calculated_cost_sharing,0))
into l_calculated_cost_eb
, l_cost_sharing_eb
from igw_budget_personnel_cal_amts ppc
, igw_budget_personnel_details ppd
where ppd.line_item_id = rec_budget_details.line_item_id
and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
and ppc.rate_class_id = l_rate_class_id_eb
and ppc.rate_type_id = l_rate_type_id_eb;
select sum(nvl(ppd.salary_requested,0))
, sum(nvl(ppd.cost_sharing_amount,0))
, sum(nvl(ppd.underrecovery_amount,0))
into l_salary_requested_upd
, l_cost_sharing_amount_upd
, l_underrecovery_amount_upd
from igw_budget_personnel_details ppd
where ppd.line_item_id = rec_budget_details.line_item_id;
update igw_budget_details pdb
set line_item_cost = nvl(l_salary_requested_upd,0)
, cost_sharing_amount = nvl(l_cost_sharing_amount_upd,0)
, underrecovery_amount = nvl(l_underrecovery_amount_upd,0)
where pdb.line_item_id = rec_budget_details.line_item_id;
select nvl(sum(line_item_cost),0)
, nvl(sum(cost_sharing_amount),0)
, nvl(sum(underrecovery_amount),0)
into l_direct_cost1
, l_cost_share1
, l_underrecovery
from igw_budget_details
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id;
select nvl(sum(calculated_cost_sharing),0)
, nvl(sum(calculated_cost),0)
into l_cost_share2
, l_indirect_cost
from igw_budget_details_cal_amts pc
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id
and pc.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pc.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'O');
select nvl(sum(calculated_cost_sharing),0)
, nvl(sum(calculated_cost),0)
into l_cost_share3
, l_direct_cost2
from igw_budget_details_cal_amts pc
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id
and pc.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pc.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'E');
update igw_budget_periods
set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
, total_direct_cost = (l_direct_cost1+l_direct_cost2)
, total_indirect_cost = l_indirect_cost
, cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
, underrecovery_amount = l_underrecovery
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id;
select nvl(sum(total_cost),0)
, nvl(sum(total_direct_cost),0)
, nvl(sum(total_indirect_cost),0)
, nvl(sum(cost_sharing_amount),0)
, nvl(sum(underrecovery_amount),0)
, nvl(sum(total_cost_limit),0)
into l_total_cost
, l_total_direct_cost
, l_total_indirect_cost
, l_cost_sharing_amt
, l_underrecovery_amount
, l_total_cost_limit
from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = p_version_id;
update igw_budgets
set total_cost = l_total_cost
, total_direct_cost = l_total_direct_cost
, total_indirect_cost = l_total_indirect_cost
, cost_sharing_amount = l_cost_sharing_amt
, underrecovery_amount = l_underrecovery_amount
, total_cost_limit = l_total_cost_limit
where proposal_id = p_proposal_id
and version_id = p_version_id;
select *
from igw_budgets
where proposal_id = p_proposal_id
and version_id = nvl(p_version_id, version_id);
select *
from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = l_version_id;
select pbd.line_item_id
, pbd.expenditure_type
, pbd.budget_category_code
, pbd.expenditure_category_flag
, pbd.line_item_description
, pbd.based_on_line_item
, pbd.line_item_cost
, pbd.cost_sharing_amount
, pbd.underrecovery_amount
, pbd.apply_inflation_flag
, pbd.budget_justification
, pbd.location_code
, et.personnel_attached_flag
, pbd.budget_period_id
from igw_budget_details pbd
, igw_budget_expenditures_v et
where pbd.expenditure_type = et.budget_expenditure
and pbd.expenditure_category_flag = et.expenditure_category_flag
and pbd.proposal_id = p_proposal_id
and pbd.budget_period_id = l_budget_period_id
and pbd.version_id = l_version_id;
select *
from igw_budget_details_cal_amts
where line_item_id = l_line_item_id;
select *
from igw_budget_personnel_details
where line_item_id = l_line_item_id;
select *
from igw_budget_personnel_cal_amts
where budget_personnel_detail_id = l_budget_personnel_detail_id;
select *
from igw_budget_persons
where proposal_id = p_proposal_id
and version_id = l_version_id;
select *
from igw_prop_rates
where proposal_id = p_proposal_id
and version_id = l_version_id;
select max(version_id)+1
into l_target_version_id
from igw_budgets
where proposal_id = p_proposal_id;
insert into igw_budgets( proposal_id
,version_id
,start_date
,end_date
,total_cost
,total_direct_cost
,total_indirect_cost
,cost_sharing_amount
,underrecovery_amount
,residual_funds
,total_cost_limit
,oh_rate_class_id
,proposal_form_number
,comments
,final_version_flag
,budget_type_code
,record_version_number
,apply_inflation_setup_rates
,apply_eb_setup_rates
,apply_oh_setup_rates
,enter_budget_at_period_level
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15)
values( p_target_proposal_id
,l_target_version_id
,rec_budgets.start_date
,rec_budgets.end_date
,rec_budgets.total_cost
,rec_budgets.total_direct_cost
,rec_budgets.total_indirect_cost
,rec_budgets.cost_sharing_amount
,rec_budgets.underrecovery_amount
,rec_budgets.residual_funds
,rec_budgets.total_cost_limit
,rec_budgets.oh_rate_class_id
,rec_budgets.proposal_form_number
,rec_budgets.comments
,'N'
,p_budget_type_code
,1
,rec_budgets.apply_inflation_setup_rates
,rec_budgets.apply_eb_setup_rates
,rec_budgets.apply_oh_setup_rates
,rec_budgets.enter_budget_at_period_level
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,rec_budgets.attribute_category
,rec_budgets.attribute1
,rec_budgets.attribute2
,rec_budgets.attribute3
,rec_budgets.attribute4
,rec_budgets.attribute5
,rec_budgets.attribute6
,rec_budgets.attribute7
,rec_budgets.attribute8
,rec_budgets.attribute9
,rec_budgets.attribute10
,rec_budgets.attribute11
,rec_budgets.attribute12
,rec_budgets.attribute13
,rec_budgets.attribute14
,rec_budgets.attribute15);
insert into igw_budget_periods( proposal_id
,version_id
,budget_period_id
,start_date
,end_date
,total_cost
,total_direct_cost
,total_indirect_cost
,cost_sharing_amount
,underrecovery_amount
,total_cost_limit
,program_income
,program_income_source
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
values( p_target_proposal_id
,l_target_version_id
,rec_budget_periods.budget_period_id
,rec_budget_periods.start_date
,rec_budget_periods.end_date
,rec_budget_periods.total_cost
,rec_budget_periods.total_direct_cost
,rec_budget_periods.total_indirect_cost
,rec_budget_periods.cost_sharing_amount
,rec_budget_periods.underrecovery_amount
,rec_budget_periods.total_cost_limit
,rec_budget_periods.program_income
,rec_budget_periods.program_income_source
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
select igw_budget_details_s.currval into l_based_on_line_item from dual;
insert into igw_budget_personnel_details (
budget_personnel_detail_id
,proposal_id
,version_id
,budget_period_id
,line_item_id
,person_id
,party_id
,start_date
,end_date
,period_type_code
,appointment_type_code
,salary_requested
,percent_charged
,percent_effort
,cost_sharing_percent
,cost_sharing_amount
,underrecovery_amount
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
values (igw_budget_personnel_s.nextval
,p_target_proposal_id
,l_target_version_id
,rec_budget_details.budget_period_id
,igw_budget_details_s.currval
,rec_budget_personnel.person_id
,rec_budget_personnel.party_id
,rec_budget_personnel.start_date
,rec_budget_personnel.end_date
,rec_budget_personnel.period_type_code
,rec_budget_personnel.appointment_type_code
,rec_budget_personnel.salary_requested
,rec_budget_personnel.percent_charged
,rec_budget_personnel.percent_effort
,rec_budget_personnel.cost_sharing_percent
,rec_budget_personnel.cost_sharing_amount
,rec_budget_personnel.underrecovery_amount
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
select igw_budget_personnel_s.currval into l_dummy_personnel_id from dual;
select igw_budget_details_s.currval into l_dummy_line_item_id from dual;
insert into igw_budget_persons( proposal_id
,version_id
,person_id
,party_id
,appointment_type_code
,effective_date
,calculation_base
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
values( p_target_proposal_id
,l_target_version_id
,rec_budget_persons.person_id
,rec_budget_persons.party_id
,rec_budget_persons.appointment_type_code
,rec_budget_persons.effective_date
,rec_budget_persons.calculation_base
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
insert into igw_prop_rates( proposal_id
,version_id
,rate_class_id
,rate_type_id
,fiscal_year
,location_code
,activity_type_code
,start_date
,applicable_rate
,institute_rate
,record_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
values( p_target_proposal_id
,l_target_version_id
,rec_prop_rates.rate_class_id
,rec_prop_rates.rate_type_id
,rec_prop_rates.fiscal_year
,rec_prop_rates.location_code
,rec_prop_rates.activity_type_code
,rec_prop_rates.start_date
,rec_prop_rates.applicable_rate
,rec_prop_rates.institute_rate
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
select nvl(sum(line_item_cost),0)
, nvl(sum(cost_sharing_amount),0)
, nvl(sum(underrecovery_amount),0)
into l_direct_cost1
, l_cost_share1
, l_underrecovery
from igw_budget_details
where proposal_id = p_target_proposal_id
and version_id = l_target_version_id
and budget_period_id = 1;
select nvl(sum(calculated_cost_sharing),0)
, nvl(sum(calculated_cost),0)
into l_cost_share2
, l_indirect_cost
from igw_budget_details_cal_amts pc
where proposal_id = p_target_proposal_id
and version_id = l_target_version_id
and budget_period_id = 1
and pc.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pc.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'O');
select nvl(sum(calculated_cost_sharing),0)
, nvl(sum(calculated_cost),0)
into l_cost_share3
, l_direct_cost2
from igw_budget_details_cal_amts pc
where proposal_id = p_target_proposal_id
and version_id = l_target_version_id
and budget_period_id = 1
and pc.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pc.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'E');
update igw_budget_periods
set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
, total_direct_cost = (l_direct_cost1+l_direct_cost2)
, total_indirect_cost = l_indirect_cost
, cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
, underrecovery_amount = l_underrecovery
where proposal_id = p_target_proposal_id
and version_id = l_target_version_id
and budget_period_id = 1;
select nvl(sum(total_cost),0)
, nvl(sum(total_direct_cost),0)
, nvl(sum(total_indirect_cost),0)
, nvl(sum(cost_sharing_amount),0)
, nvl(sum(underrecovery_amount),0)
, nvl(sum(total_cost_limit),0)
into l_total_cost
, l_total_direct_cost
, l_total_indirect_cost
, l_cost_sharing_amt
, l_underrecovery_amount
, l_total_cost_limit
from igw_budget_periods
where proposal_id = p_target_proposal_id
and version_id = l_target_version_id;
update igw_budgets
set total_cost = l_total_cost
, total_direct_cost = l_total_direct_cost
, total_indirect_cost = l_total_indirect_cost
, cost_sharing_amount = l_cost_sharing_amt
, underrecovery_amount = l_underrecovery_amount
, total_cost_limit = l_total_cost_limit
where proposal_id = p_target_proposal_id
and version_id = l_target_version_id;
select enter_budget_at_period_level
, apply_inflation_setup_rates
, apply_eb_setup_rates
, apply_oh_setup_rates
from igw_budgets
where proposal_id = p_proposal_id
and version_id = p_version_id;
select budget_period_id
, start_date
, end_date
, total_direct_cost
, total_indirect_cost
, total_cost
from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = nvl(p_budget_period_id, budget_period_id);
select pbd.line_item_id
, pbd.expenditure_type
, pbd.expenditure_category_flag
, pbd.apply_inflation_flag
, pbd.line_item_cost
, pbd.cost_sharing_amount
, pbd.location_code
, et.personnel_attached_flag
, pbd.budget_period_id
, pbd.proposal_id
, pbd.version_id
from igw_budget_details pbd
, igw_budget_expenditures_v et
where pbd.expenditure_type = et.budget_expenditure
and pbd.expenditure_category_flag = et.expenditure_category_flag
and pbd.proposal_id = p_proposal_id
and pbd.budget_period_id = l_budget_period_id
and pbd.version_id = p_version_id
and pbd.line_item_id = nvl(p_line_item_id, line_item_id);
select budget_personnel_detail_id
, start_date
, end_date
, percent_charged
, cost_sharing_percent
, person_id
, party_id
, appointment_type_code
from igw_budget_personnel_details
where line_item_id = l_line_item_id
and budget_personnel_detail_id = nvl(p_budget_personnel_detail_id, budget_personnel_detail_id);
select activity_type_code
into l_activity_type_code
from igw_proposals_all
where proposal_id = p_proposal_id;
select oh_rate_class_id
into l_oh_rate_class_id
from igw_budgets
where proposal_id = p_proposal_id
and version_id = p_version_id;
select apply_rate_flag, calculated_cost, calculated_cost_sharing
into l_apply_rate_flag_oh, l_oh_value_ov_usr, l_calculated_cost_share_ov_usr
from igw_budget_details_cal_amts
where line_item_id = rec_budget_details.line_item_id;
update igw_budget_details
set line_item_cost = l_base_amount
, underrecovery_amount = nvl(l_oh_value - l_oh_value_ov,0)
where line_item_id = rec_budget_details.line_item_id;
update igw_budget_details
set line_item_cost = l_base_amount
, underrecovery_amount = nvl(l_oh_value - l_oh_value_ov_usr,0)
where line_item_id = rec_budget_details.line_item_id;
delete from igw_budget_details_cal_amts
where line_item_id = rec_budget_details.line_item_id;
select pca.apply_rate_flag , calculated_cost, calculated_cost_sharing
into l_apply_rate_flag_oh, l_oh_value_ov_usr, l_calculated_cost_share_ov_usr
from igw_budget_personnel_cal_amts pca
, igw_budget_personnel_details pbd
where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
and pca.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pca.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'O');
select pca.apply_rate_flag , calculated_cost, calculated_cost_sharing
into l_apply_rate_flag_eb, l_eb_value_ov_usr, l_calculated_cost_share_eb_usr
from igw_budget_personnel_cal_amts pca
, igw_budget_personnel_details pbd
where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
and pca.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pca.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'E');
select calculation_base
, effective_date
, appointment_type_code
into l_calculation_base
, l_effective_date
, l_appointment_type_code
from igw_budget_persons
where proposal_id = p_proposal_id
and version_id = p_version_id
--and person_id = rec_budget_personnel.person_id
and party_id = rec_budget_personnel.party_id
and appointment_type_code = rec_budget_personnel.appointment_type_code;
update igw_budget_personnel_details
set salary_requested = l_inflated_salary_ov
,cost_sharing_amount = l_pers_cost_sharing_amt -- Bug 2702314
--, cost_sharing_amount = rec_budget_personnel.cost_sharing_percent/100 *
-- l_inflated_salary_ov
, underrecovery_amount = nvl(l_oh_value_d - l_oh_value_ov,0)
where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
update igw_budget_personnel_details
set salary_requested = l_inflated_salary_ov
,cost_sharing_amount = l_pers_cost_sharing_amt -- Bug 2702314
--, cost_sharing_amount = rec_budget_personnel.cost_sharing_percent/100 *
-- l_inflated_salary_ov
, underrecovery_amount = nvl(l_oh_value_d - l_oh_value_ov_usr,0)
where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
delete from igw_budget_personnel_cal_amts
where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
delete from igw_budget_details_cal_amts
where line_item_id = rec_budget_details.line_item_id;
select sum(nvl(ppc.calculated_cost,0))
, sum(nvl(ppc.calculated_cost_sharing,0))
into l_calculated_cost_oh
, l_cost_sharing_oh
from igw_budget_personnel_cal_amts ppc
, igw_budget_personnel_details ppd
where ppd.line_item_id = rec_budget_details.line_item_id
and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
and ppc.rate_class_id = l_rate_class_id_oh
and ppc.rate_type_id = l_rate_type_id_oh;
select sum(nvl(ppc.calculated_cost,0))
, sum(nvl(ppc.calculated_cost_sharing,0))
into l_calculated_cost_eb
, l_cost_sharing_eb
from igw_budget_personnel_cal_amts ppc
, igw_budget_personnel_details ppd
where ppd.line_item_id = rec_budget_details.line_item_id
and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
and ppc.rate_class_id = l_rate_class_id_eb
and ppc.rate_type_id = l_rate_type_id_eb;
select sum(nvl(ppd.salary_requested,0))
, sum(nvl(ppd.cost_sharing_amount,0))
, sum(nvl(ppd.underrecovery_amount,0))
into l_salary_requested_upd
, l_cost_sharing_amount_upd
, l_underrecovery_amount_upd
from igw_budget_personnel_details ppd
where ppd.line_item_id = rec_budget_details.line_item_id;
update igw_budget_details pdb
set line_item_cost = nvl(l_salary_requested_upd,0)
, cost_sharing_amount = nvl(l_cost_sharing_amount_upd,0)
, underrecovery_amount = nvl(l_underrecovery_amount_upd,0)
where pdb.line_item_id = rec_budget_details.line_item_id;
select nvl(sum(line_item_cost),0)
, nvl(sum(cost_sharing_amount),0)
, nvl(sum(underrecovery_amount),0)
into l_direct_cost1
, l_cost_share1
, l_underrecovery
from igw_budget_details
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id;
select nvl(sum(calculated_cost_sharing),0)
, nvl(sum(calculated_cost),0)
into l_cost_share2
, l_indirect_cost
from igw_budget_details_cal_amts pc
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id
and pc.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pc.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'O');
select nvl(sum(calculated_cost_sharing),0)
, nvl(sum(calculated_cost),0)
into l_cost_share3
, l_direct_cost2
from igw_budget_details_cal_amts pc
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id
and pc.rate_class_id = ( select pr.rate_class_id
from igw_rate_classes pr
where pc.rate_class_id = pr.rate_class_id
and pr.rate_class_type = 'E');
update igw_budget_periods
set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
, total_direct_cost = (l_direct_cost1+l_direct_cost2)
, total_indirect_cost = l_indirect_cost
, cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
, underrecovery_amount = l_underrecovery
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id;
update igw_budget_periods
set total_cost = nvl(rec_budget_periods.total_direct_cost,0) + nvl(rec_budget_periods.total_indirect_Cost,0)
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = rec_budget_periods.budget_period_id;
select nvl(sum(total_cost),0)
, nvl(sum(total_direct_cost),0)
, nvl(sum(total_indirect_cost),0)
, nvl(sum(cost_sharing_amount),0)
, nvl(sum(underrecovery_amount),0)
--, nvl(sum(total_cost_limit),0)
into l_total_cost
, l_total_direct_cost
, l_total_indirect_cost
, l_cost_sharing_amt
, l_underrecovery_amount
--, l_total_cost_limit
from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = p_version_id;
update igw_budgets
set total_cost = l_total_cost
, total_direct_cost = l_total_direct_cost
, total_indirect_cost = l_total_indirect_cost
, cost_sharing_amount = l_cost_sharing_amt
, underrecovery_amount = l_underrecovery_amount
--, total_cost_limit = l_total_cost_limit
where proposal_id = p_proposal_id
and version_id = p_version_id;