[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(p_delete_level VARCHAR2
,p_proposal_id NUMBER
,p_version_id NUMBER
,p_budget_period_id NUMBER := null
,p_line_item_id NUMBER := null
,p_budget_personnel_detail_id NUMBER := null
,x_return_status OUT NOCOPY VARCHAR2) IS
/* possible values of p_delete_level are 'BUDGET_VERSION', 'BUDGET_PERIOD', 'BUDGET_LINE', 'BUDGET_PERSONNEL' */
l_api_name varchar2(30) := 'MANAGE_BUDGET_DELETION';
if p_delete_level = 'BUDGET_VERSION' then
delete from igw_budget_persons
where proposal_id = p_proposal_id
and version_id = p_version_id;
delete from igw_budget_personnel_cal_amts pbp
where pbp.budget_personnel_detail_id IN (select pb.budget_personnel_detail_id
from igw_budget_personnel_details pb
where pb.proposal_id = p_proposal_id
and pb.version_id = p_version_id);
delete from igw_budget_personnel_details
where proposal_id = p_proposal_id
and version_id = p_version_id;
delete from igw_budget_details_cal_amts
where proposal_id = p_proposal_id
and version_id = p_version_id;
delete from igw_budget_details
where proposal_id = p_proposal_id
and version_id = p_version_id;
delete from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = p_version_id;
delete from igw_prop_rates
where proposal_id = p_proposal_id
and version_id = p_version_id;
elsif p_delete_level = 'BUDGET_PERIOD' then
delete from igw_budget_personnel_cal_amts pbp
where pbp.budget_personnel_detail_id IN (select pb.budget_personnel_detail_id
from igw_budget_personnel_details pb
where pb.proposal_id = p_proposal_id
and pb.version_id = p_version_id
and pb.budget_period_id = p_budget_period_id);
delete from igw_budget_personnel_details
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = p_budget_period_id;
delete from igw_budget_details_cal_amts
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = p_budget_period_id;
delete from igw_budget_details
where proposal_id = p_proposal_id
and version_id = p_version_id
and budget_period_id = p_budget_period_id;
elsif p_delete_level = 'BUDGET_LINE' then
delete from igw_budget_personnel_cal_amts pbp
where pbp.budget_personnel_detail_id IN (select pb.budget_personnel_detail_id
from igw_budget_personnel_details pb
where pb.line_item_id = p_line_item_id);
delete from igw_budget_personnel_details
where line_item_id = p_line_item_id;
delete from igw_budget_details_cal_amts
where line_item_id = p_line_item_id;
elsif p_delete_level = 'BUDGET_PERSONNEL' then
delete from igw_budget_personnel_cal_amts pbp
where pbp.budget_personnel_detail_id = p_budget_personnel_detail_id;
SELECT ibc.proposal_id
, ibc.budget_period_id
, ibc.expenditure_type
, ibc.expenditure_category_flag
, nvl(ibc.line_item_cost,0)+nvl(igw_budget_integration.get_eb_cost_ss(ibc.line_item_id),0) direct_cost
, ibc.line_item_cost
, igw_budget_integration.get_oh_cost_ss(ibc.line_item_id) indirect_cost
, ibp.start_date
, ibp.end_date
FROM igw_budgets ib
, igw_budget_periods ibp
, igw_budget_details ibc
WHERE ib.proposal_id = ibp.proposal_id
AND ib.version_id = ibp.version_id
AND ib.final_version_flag = 'Y'
AND ibp.proposal_id = ibc.proposal_id
AND ibp.version_id = ibc.version_id
AND ibp.budget_period_id = ibc.budget_period_id
AND ib.proposal_id = ibc.proposal_id
AND ib.version_id = ibc.version_id
AND ib.proposal_id = p_proposal_id;
SELECT ibc.proposal_id
, ibc.budget_period_id
, ibc.expenditure_type
, ibc.expenditure_category_flag
, nvl(ibc.line_item_cost,0)+nvl(ibc.eb_cost,0) direct_cost
, ibc.oh_cost indirect_cost
, ibp.start_date
, ibp.end_date
FROM igw_budgets ib
, igw_budget_periods ibp
, igw_budget_complete_v ibc
WHERE ib.proposal_id = ibp.proposal_id
AND ib.version_id = ibp.version_id
AND ib.final_version_flag = 'Y'
AND ibp.proposal_id = ibc.proposal_id
AND ibp.version_id = ibc.version_id
AND ibp.budget_period_id = ibc.budget_period_id
AND ib.proposal_id = ibc.proposal_id
AND ib.version_id = ibc.version_id
AND ib.proposal_id = p_proposal_id;
SELECT ibc.budget_period_id
, sum(igw_budget_integration.get_oh_cost_ss(ibc.line_item_id)) indirect_cost
FROM igw_budgets ib
, igw_budget_periods ibp
, igw_budget_details ibc
WHERE ib.proposal_id = ibp.proposal_id
AND ib.version_id = ibp.version_id
AND ib.final_version_flag = 'Y'
AND ibp.proposal_id = ibc.proposal_id
AND ibp.version_id = ibc.version_id
AND ibp.budget_period_id = ibc.budget_period_id
AND ib.proposal_id = ibc.proposal_id
AND ib.version_id = ibc.version_id
AND ib.proposal_id = p_proposal_id
GROUP BY ibc.budget_period_id;
SELECT ibc.budget_period_id
, sum(ibc.oh_cost) indirect_cost
FROM igw_budgets ib
, igw_budget_periods ibp
, igw_budget_complete_v ibc
WHERE ib.proposal_id = ibp.proposal_id
AND ib.version_id = ibp.version_id
AND ib.final_version_flag = 'Y'
AND ibp.proposal_id = ibc.proposal_id
AND ibp.version_id = ibc.version_id
AND ibp.budget_period_id = ibc.budget_period_id
AND ib.proposal_id = ibc.proposal_id
AND ib.version_id = ibc.version_id
AND ib.proposal_id = p_proposal_id
GROUP BY ibc.budget_period_id;
SELECT version_id
INTO l_version_id
FROM igw_budgets
WHERE proposal_id = p_proposal_id
AND final_version_flag = 'Y';
select project_id, task_id
into l_project_id, l_task_id
from igw_project_fundings
where proposal_installment_id = p_proposal_installment_id
AND ROWNUM < 2;
select ia.award_id
into l_award_id
from igw_awards ia,
igw_installments ii
where ii.proposal_installment_id = p_proposal_installment_id
and ii.proposal_award_id = ia.proposal_award_id;
select pbem.time_phased_type_code
, pbem.entry_level_code
into l_time_phased_type_code
, l_entry_level_code
from pa_projects_all pp
, pa_project_types_all ppt
, pa_budget_entry_methods pbem
where pp.project_id = l_project_id
and pp.project_type = ppt.project_type
and ppt.cost_budget_entry_method_code = pbem.budget_entry_method_code;
select nvl(preaward_date, start_date_active), end_date_active
into l_awd_start_date, l_awd_end_date
from gms_awards_all
where award_id = l_award_id;
select nvl(start_date,l_awd_start_date), nvl(completion_date,l_awd_end_date)
into l_proj_start_date, l_proj_end_date
from pa_projects_all
where project_id = l_project_id;
l_msg_data := 'after project date selection';
select task_id
into l_task_id
from pa_tasks_top_v
where project_id = l_project_id
and wbs_sort_order = (select min(wbs_sort_order) from pa_tasks_top_v where project_id = l_project_id);
select task_id
into l_task_id
from pa_tasks_lowest_v
where project_id = l_project_id
and wbs_sort_order = (select min(wbs_sort_order) from pa_tasks_lowest_v where project_id = l_project_id);
select task_id
into l_task_id
from pa_tasks_lowest_v
where project_id = l_project_id
and top_task_id = l_task_id
and wbs_sort_order = (select min(wbs_sort_order)
from pa_tasks_lowest_v
where project_id = l_project_id
and top_task_id = l_task_id);
select task_id
into l_task_id
from pa_tasks
where project_id = l_project_id
and (PA_TASK_UTILS.CHECK_CHILD_EXISTS(TASK_ID) = 0 or TOP_TASK_ID = task_id)
and top_task_id = l_task_id
and rownum < 2;
select igw_award_budget_s.nextval into l_award_budget_id from dual;
select period_name
into l_period_name
from pa_budget_periods_v pv
where period_type_code = l_time_phased_type_code
and period_start_date >= l_budget_start_date
and period_end_date <= l_budget_end_date
and rownum < 2;
igw_award_budgets_tbh.insert_row(
p_award_budget_id => l_award_budget_id
,p_proposal_installment_id => p_proposal_installment_id
,p_budget_period_id => rec_budget_details.budget_period_id
,p_expenditure_type_cat => rec_budget_details.expenditure_type
,p_expenditure_category_flag => rec_budget_details.expenditure_category_flag
,p_budget_amount => rec_budget_details.direct_cost
,p_indirect_flag => 'N'
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_period_name => l_period_name
,p_start_date => rec_budget_details.start_date
,p_end_date => rec_budget_details.end_date
,p_transferred_flag => 'N'
,x_rowid => x_rowid
,x_return_status => l_return_status);
select start_date, end_date
into l_start_date, l_end_date
from igw_installments
where proposal_installment_id = p_proposal_installment_id;
igw_award_budgets_tbh.insert_row(
p_award_budget_id => l_award_budget_id
,p_proposal_installment_id => p_proposal_installment_id
,p_budget_period_id => rec_budget_details.budget_period_id
,p_expenditure_type_cat => rec_budget_details.expenditure_type
,p_expenditure_category_flag => rec_budget_details.expenditure_category_flag
,p_budget_amount => rec_budget_details.direct_cost
,p_indirect_flag => 'N'
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_period_name => null
,p_start_date => l_start_date -- rec_budget_details.start_date
,p_end_date => l_end_date -- rec_budget_details.end_date
,p_transferred_flag => 'N'
,x_rowid => x_rowid
,x_return_status => l_return_status);
igw_award_budgets_tbh.insert_row(
p_award_budget_id => l_award_budget_id
,p_proposal_installment_id => p_proposal_installment_id
,p_budget_period_id => rec_budget_details.budget_period_id
,p_expenditure_type_cat => rec_budget_details.expenditure_type
,p_expenditure_category_flag => rec_budget_details.expenditure_category_flag
,p_budget_amount => rec_budget_details.direct_cost
,p_indirect_flag => 'N'
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_period_name => null
,p_start_date => l_budget_start_date
,p_end_date => l_budget_end_date
,p_transferred_flag => 'N'
,x_rowid => x_rowid
,x_return_status => l_return_status);
select igw_award_budget_s.nextval into l_award_budget_id from dual;
select start_date, end_date
into l_start_date, l_end_date
from igw_budget_periods
where proposal_id = p_proposal_id
and version_id = l_version_id
and budget_period_id = rec_budget_indirect_cost.budget_period_id;
select period_name
into l_period_name
from pa_budget_periods_v
where period_type_code = l_time_phased_type_code
and period_start_date >= l_budget_start_date
and period_end_date <= l_budget_end_date
and rownum < 2;
igw_award_budgets_tbh.insert_row(
p_award_budget_id => l_award_budget_id
,p_proposal_installment_id => p_proposal_installment_id
,p_budget_period_id => rec_budget_indirect_cost.budget_period_id
,p_expenditure_type_cat => null
,p_expenditure_category_flag => null
,p_budget_amount => rec_budget_indirect_cost.indirect_cost
,p_indirect_flag => 'Y'
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_period_name => l_period_name
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_transferred_flag => 'N'
,x_rowid => x_rowid
,x_return_status => l_return_status);
select start_date, end_date
into l_start_date, l_end_date
from igw_installments
where proposal_installment_id = p_proposal_installment_id;
igw_award_budgets_tbh.insert_row(
p_award_budget_id => l_award_budget_id
,p_proposal_installment_id => p_proposal_installment_id
,p_budget_period_id => rec_budget_indirect_cost.budget_period_id
,p_expenditure_type_cat => null
,p_expenditure_category_flag => null
,p_budget_amount => rec_budget_indirect_cost.indirect_cost
,p_indirect_flag => 'Y'
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_period_name => null
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_transferred_flag => 'N'
,x_rowid => x_rowid
,x_return_status => l_return_status);
igw_award_budgets_tbh.insert_row(
p_award_budget_id => l_award_budget_id
,p_proposal_installment_id => p_proposal_installment_id
,p_budget_period_id => rec_budget_indirect_cost.budget_period_id
,p_expenditure_type_cat => null
,p_expenditure_category_flag => null
,p_budget_amount => rec_budget_indirect_cost.indirect_cost
,p_indirect_flag => 'Y'
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_period_name => null
,p_start_date => l_budget_start_date
,p_end_date => l_budget_end_date
,p_transferred_flag => 'N'
,x_rowid => x_rowid
,x_return_status => l_return_status);
select version_id
into l_final_version
from igw_budgets
where proposal_id = p_proposal_id
and final_version_flag = 'Y';
select count(*)
into l_award_budget_count
from igw_award_budgets
where proposal_installment_id = p_proposal_installment_id
and transferred_flag = 'N';
select rate_class_id
into x_rate_class_id
from igw_rate_classes
where description = p_rate_class_name;
select final_version_flag
into l_final_version_flag
from igw_budgets
where proposal_id = p_proposal_id
and version_id <> nvl(p_version_id,0)
and final_version_flag = 'Y';
select line_item_id
from igw_budget_details
where proposal_id = p_proposal_id
and version_id = p_version_id;
select distinct proposal_form_number
into x_proposal_form_number
from igw_report_seed_header_v
where proposal_form_number = p_proposal_form_number;
select proposal_start_date, proposal_end_date
into l_start_date, l_end_date
from igw_proposals_all
where proposal_id = p_proposal_id;
select nvl(max(version_id),0)+1
into l_version_id
from igw_budgets
where proposal_id = p_proposal_id;
igw_budgets_tbh.insert_row(
p_proposal_id => p_proposal_id
,p_version_id => l_version_id
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_total_cost => p_total_cost
,p_total_direct_cost => p_total_direct_cost
,p_total_indirect_cost => p_total_indirect_cost
,p_cost_sharing_amount => p_cost_sharing_amount
,p_underrecovery_amount => p_underrecovery_amount
,p_residual_funds => p_residual_funds
,p_total_cost_limit => p_total_cost_limit
,p_oh_rate_class_id => l_rate_class_id
,p_proposal_form_number => p_proposal_form_number
,p_comments => p_comments
,p_final_version_flag => p_final_version_flag
,p_budget_type_code => p_budget_type_code
,p_enter_budget_at_period_level => p_enter_budget_at_period_level
,p_apply_inflation_setup_rates => p_apply_inflation_setup_rates
,p_apply_eb_setup_rates => p_apply_eb_setup_rates
,p_apply_oh_setup_rates => p_apply_oh_setup_rates
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,x_rowid => x_rowid
,x_return_status => l_return_status);
igw_budget_periods_tbh.insert_row(
p_proposal_id => p_proposal_id
,p_version_id => l_version_id
,p_budget_period_id => l_budget_period
,p_start_date => l_period_start_date
,p_end_date => l_period_end_date
,p_total_cost => 0
,p_total_direct_cost => 0
,p_total_indirect_cost => 0
,p_cost_sharing_amount => 0
,p_underrecovery_amount => 0
,p_total_cost_limit => 0
,p_program_income => 0
,p_program_income_source => null
,x_rowid => x_rowid
,x_return_status => l_return_status);
igw_budget_periods_tbh.insert_row(
p_proposal_id => p_proposal_id
,p_version_id => l_version_id
,p_budget_period_id => l_budget_period
,p_start_date => l_period_start_date
,p_end_date => l_period_end_date
,p_total_cost => 0
,p_total_direct_cost => 0
,p_total_indirect_cost => 0
,p_cost_sharing_amount => 0
,p_underrecovery_amount => 0
,p_total_cost_limit => 0
,p_program_income => 0
,p_program_income_source => null
,x_rowid => x_rowid
,x_return_status => l_return_status);
procedure update_budget_version
(p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_proposal_id NUMBER
,p_version_id NUMBER
,p_start_date DATE
,p_end_date DATE
,p_total_cost NUMBER
,p_total_direct_cost NUMBER
,p_total_indirect_cost NUMBER
,p_cost_sharing_amount NUMBER
,p_underrecovery_amount NUMBER
,p_residual_funds NUMBER
,p_total_cost_limit NUMBER
,p_oh_rate_class_id NUMBER
,p_oh_rate_class_name VARCHAR2
,p_proposal_form_number VARCHAR2
,p_comments VARCHAR2
,p_final_version_flag VARCHAR2
,p_budget_type_code VARCHAR2 := 'PROPOSAL_BUDGET'
,p_enter_budget_at_period_level VARCHAR2
,p_apply_inflation_setup_rates VARCHAR2
,p_apply_eb_setup_rates VARCHAR2
,p_apply_oh_setup_rates VARCHAR2
,p_attribute_category VARCHAR2 := null
,p_attribute1 VARCHAR2 := null
,p_attribute2 VARCHAR2 := null
,p_attribute3 VARCHAR2 := null
,p_attribute4 VARCHAR2 := null
,p_attribute5 VARCHAR2 := null
,p_attribute6 VARCHAR2 := null
,p_attribute7 VARCHAR2 := null
,p_attribute8 VARCHAR2 := null
,p_attribute9 VARCHAR2 := null
,p_attribute10 VARCHAR2 := null
,p_attribute11 VARCHAR2 := null
,p_attribute12 VARCHAR2 := null
,p_attribute13 VARCHAR2 := null
,p_attribute14 VARCHAR2 := null
,p_attribute15 VARCHAR2 := null
,p_record_version_number IN NUMBER
,p_rowid IN ROWID
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
cursor c_rate_class is
select oh_rate_class_id
from igw_budgets
where rowid = p_rowid;
l_api_name VARCHAR2(30) := 'UPDATE_BUDGET_VERSION';
SAVEPOINT update_budget_version;
SELECT 'x' INTO l_dummy
FROM igw_budgets
WHERE ((proposal_id = p_proposal_id AND version_id = p_version_id)
OR rowid = p_rowid)
AND record_version_number = p_record_version_number;
igw_budgets_tbh.update_row(
p_rowid => p_rowid
,p_proposal_id => p_proposal_id
,p_version_id => p_version_id
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_total_cost => p_total_cost
,p_total_direct_cost => p_total_direct_cost
,p_total_indirect_cost => p_total_indirect_cost
,p_cost_sharing_amount => p_cost_sharing_amount
,p_underrecovery_amount => p_underrecovery_amount
,p_residual_funds => p_residual_funds
,p_total_cost_limit => p_total_cost_limit
,p_oh_rate_class_id => l_rate_class_id
,p_proposal_form_number => p_proposal_form_number
,p_comments => p_comments
,p_final_version_flag => p_final_version_flag
,p_budget_type_code => p_budget_type_code
,p_enter_budget_at_period_level => p_enter_budget_at_period_level
,p_apply_inflation_setup_rates => p_apply_inflation_setup_rates
,p_apply_eb_setup_rates => p_apply_eb_setup_rates
,p_apply_oh_setup_rates => p_apply_oh_setup_rates
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_record_version_number => p_record_version_number
,x_return_status => l_return_status);
update igw_budget_details
set apply_inflation_flag = p_apply_inflation_setup_rates
where proposal_id = p_proposal_id
and version_id = p_version_id;
ROLLBACK TO update_budget_version;
ROLLBACK TO update_budget_version;
ROLLBACK TO update_budget_version;
END; --UPDATE BUDGET VERSIONS
procedure delete_budget_version
(p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_proposal_id IN NUMBER
,p_version_id IN NUMBER
,p_record_version_number IN NUMBER
,p_rowid IN ROWID
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)is
l_api_name VARCHAR2(30) := 'DELETE_BUDGET_VERSION';
SAVEPOINT delete_budget_version;
SELECT 'x' INTO l_dummy
FROM igw_budgets
WHERE ((proposal_id = p_proposal_id AND version_id = p_version_id)
OR rowid = p_rowid)
AND record_version_number = p_record_version_number;
igw_budgets_tbh.delete_row (
p_rowid => p_rowid,
p_proposal_id => p_proposal_id,
p_version_id => p_version_id,
p_record_version_number => p_record_version_number,
x_return_status => l_return_status);
p_delete_level => 'BUDGET_VERSION'
,p_proposal_id => p_proposal_id
,p_version_id => p_version_id
,x_return_status => l_return_status);
ROLLBACK TO delete_budget_version;
ROLLBACK TO delete_budget_version;
ROLLBACK TO delete_budget_version;
END; --DELETE BUDGET VERSION