[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* package global to be used during updates */
-- To check on, whether to print debug messages in log file or not
L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
select 'X'
from pa_projects
where project_id = p_project_id_in;
select project_id
from pa_projects
where segment1 = p_project_number_in;
select 'X'
from pa_tasks
where project_id = p_project_id_in
and task_id = p_task_id_in;
select task_id
from pa_tasks
where task_number = p_task_number_in
and project_id = p_project_id_in;
select 'X'
from gms_awards
where award_id = p_award_id_in;
select award_id
from gms_awards
where award_number = p_award_number_in;
select 'X'
from pa_resource_lists
where resource_list_id = p_resource_list_id_in;
select resource_list_id
from pa_resource_lists
where name = p_resource_list_name_in
and NVL(migration_code,'M') ='M'; -- Bug 3626671
select 'X'
from pa_resource_list_members
where resource_list_member_id = p_resource_list_member_id_in;
select resource_list_member_id
from pa_resource_list_members
where alias = p_reslist_member_alias_in
and resource_list_id = p_resource_list_id_in
and NVL(migration_code,'M') ='M'; -- Bug 3626671
select (nvl(gbal.actual_period_to_date,0) + nvl(gbal.encumb_period_to_date,0))
from gms_budget_versions gbv,
gms_budget_lines gbl,
gms_resource_assignments gra,
gms_balances gbal
where gbv.budget_version_id = gra.budget_version_id
and gra.resource_assignment_id = gbl.resource_assignment_id
and gbal.award_id = gbv.award_id
and gbal.project_id = gbv.project_id
and gbal.budget_version_id = gbv.budget_version_id
and gbal.task_id = gra.task_id
--and gbal.resource_list_member_id = gra.resource_list_member_id
--and gbal.start_date = gbl.start_date
and gbv.award_id = p_award_id
and gbv.project_id = p_project_id
and gbl.start_date = p_start_date
and gra.resource_list_member_id = p_resource_list_member_id
and gra.task_id = p_task_id
and gbv.budget_status_code = 'B'
and gbv.current_flag ='Y'
and gbal.balance_type in ('REQ', 'PO', 'AP', 'ENC', 'EXP');
select 1
from dual
where exists(
select 'x'
from gms_budgetary_controls
where project_id = p_project_id
and award_id = p_award_id
and funds_control_level_code = 'B');
select
draft_array.budget_entry_method_code,
baselined_array.budget_entry_method_code,
draft_array.resource_list_id,
baselined_array.resource_list_id,
draft_array.resource_list_member_id,
draft_array.project_id,
draft_array.task_id,
draft_array.start_date,
draft_array.period_name,
baselined_array.burdened_cost,
draft_array.burdened_cost,
nvl(draft_array.burdened_cost,0) - nvl(baselined_array.burdened_cost,0) amt_diff
from
(select gra_b.resource_list_member_id resource_list_member_id,
gra_b.project_id project_id,
gra_b.task_id task_id,
gbl_b.burdened_cost burdened_cost,
gbl_b.start_date start_date,
gbl_b.period_name period_name,
gbv_b.budget_entry_method_code budget_entry_method_code,
gbv_b.resource_list_id resource_list_id
from gms_budget_versions gbv_b,
gms_budget_lines gbl_b,
gms_resource_assignments gra_b
where gbv_b.budget_version_id = gra_b.budget_version_id
and gra_b.resource_assignment_id = gbl_b.resource_assignment_id
and gbv_b.award_id = p_award_id
and gbv_b.project_id = p_project_id
and gbv_b.budget_status_code = 'B'
and gbv_b.current_flag ='Y'
) baselined_array,
(select gra_d.resource_list_member_id resource_list_member_id,
gra_d.project_id project_id,
gra_d.task_id task_id,
gbl_d.burdened_cost burdened_cost,
gbl_d.start_date start_date,
gbl_d.period_name period_name,
gbv_d.budget_entry_method_code budget_entry_method_code,
gbv_d.resource_list_id resource_list_id
from gms_budget_versions gbv_d,
gms_budget_lines gbl_d,
gms_resource_assignments gra_d
where gbv_d.budget_version_id = gra_d.budget_version_id
and gra_d.resource_assignment_id = gbl_d.resource_assignment_id
and gbv_d.award_id = p_award_id
and gbv_d.project_id = p_project_id
and gbv_d.budget_status_code in ('W','S')
) draft_array
where baselined_array.project_id = draft_array.project_id(+)
and baselined_array.resource_list_member_id = draft_array.resource_list_member_id (+)
and baselined_array.task_id = draft_array.task_id(+)
and baselined_array.start_date = draft_array.start_date(+);
x_last_update_login number;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
update gms_budget_versions v
set (labor_quantity,
labor_unit_of_measure,
raw_cost,
burdened_cost,
last_update_date,
last_updated_by,
last_update_login
)
=
(select sum(nvl(to_number(decode(a.track_as_labor_flag,
'Y', l.quantity, NULL)),0)),
-- decode(a.track_as_labor_flag, 'Y', a.unit_of_measure, NULL),
'HOURS', -- V4 uses HOURS as the only labor unit
pa_currency.round_currency_amt(sum(nvl(l.raw_cost, 0))),
pa_currency.round_currency_amt(sum(nvl(l.burdened_cost, 0))),
SYSDATE,
x_created_by,
x_last_update_login
from gms_resource_assignments a,
gms_budget_lines l
where a.budget_version_id = v.budget_version_id
and a.resource_assignment_id = l.resource_assignment_id
)
where budget_version_id = x_budget_version_id;
select gi.installment_id,
gspf.total_funding_amount total_funding_amount,
trunc(gi.start_date_active) start_date_active,
nvl(trunc(ga.preaward_date),trunc(gi.start_date_active)) start_date_active_preawd, -- for Bug: 1906414
trunc(gi.end_date_active) end_date_active
from gms_installments gi,
gms_awards ga,
gms_summary_project_fundings gspf
where gi.installment_id = gspf.installment_id
and ga.award_id = gi.award_id
and gi.award_id = x_award_id
and gspf.project_id = x_project_id
and ( (x_budget_funding_level = 'T' and gspf.task_id = x_top_task_id)
or x_budget_funding_level = 'P')
order by trunc(gi.end_date_active) , trunc(gi.start_date_active),sign(total_funding_amount) desc; ---Modified for bug#12398545
select trunc(gbl.start_date) start_date,
trunc(gbl.end_date) end_date,
sum(NVL(gbl.burdened_cost,0)) burdened_cost,
decode(sum(NVL(gbl.burdened_cost,0)), abs(sum(NVL(gbl.burdened_cost,0))), 1, 0) N
from gms_budget_versions gbv,
gms_resource_assignments gra,
gms_budget_lines gbl
where gbv.budget_version_id = gra.budget_version_id
and gra.resource_assignment_id = gbl.resource_assignment_id
and gbv.project_id = x_project_id
and gbv.award_id = x_award_id
and budget_status_code = 'W' -- since we are dealing with a draft budget ONLY
and ( x_budget_funding_level = 'T' and exists (select 1 from pa_tasks pat
where pat.top_task_id = x_top_task_id
and pat.task_id = gra.task_id)
or x_budget_funding_level = 'P')
group by trunc(start_date), trunc(end_date)
order by 4,1,2 asc; /* 6846582, also added decode(Column 'N') in select clause */
select trunc(gbl.start_date) start_date,
trunc(gbl.end_date) end_date,
sum(NVL(gbl.burdened_cost,0)) burdened_cost
from gms_budget_versions gbv,
gms_resource_assignments gra,
gms_budget_lines gbl
where gbv.budget_version_id = gra.budget_version_id
and gra.resource_assignment_id = gbl.resource_assignment_id
and gbv.project_id = x_project_id
and gbv.award_id = x_award_id
and budget_status_code = 'W' -- since we are dealing with a draft budget ONLY
and ( x_budget_funding_level = 'T' and exists (select 1 from pa_tasks pat
where pat.top_task_id = x_top_task_id
and pat.task_id = gra.task_id)
or x_budget_funding_level = 'P')
group by trunc(start_date), trunc(end_date)
order by sum(gbl.burdened_cost) asc;
select nvl(burdened_cost,0)
into l_total_budget_amount
from gms_budget_versions
where -- budget_status_code in ('W','S') commented for the bug 6860267 and added below condition
budget_version_id = x_budget_version_id
and award_id = x_award_id
and project_id = x_project_id;
select sum(nvl(total_funding_amount,0))
into l_total_funding_amount
from gms_summary_project_fundings gspf,
gms_installments gi
where gspf.installment_id = gi.installment_id
and gi.award_id = x_award_id
and gspf.project_id = x_project_id;
X_Install_Total.delete;
X_Budget_Total.delete;
select top_Task_id
into x_top_task_id
from pa_tasks
where task_id = x_task_id;
select decode(entry_level_code, 'P','P','T')
into x_entry_level_code
from gms_budget_versions gbv,
pa_budget_entry_methods pbem
where gbv.budget_Version_id = x_budget_version_id
and pbem.budget_entry_method_code = gbv.budget_entry_method_code;
select decode (task_id, NULL, 'P','T')
into x_funding_level
from gms_installments gi,
gms_summary_project_fundings gspf
where gi.installment_id = gspf.installment_id
and gi.award_id = x_award_id
and gspf.project_id = x_project_id
and rownum = 1;
select alias
into x_resource_name
from pa_resource_list_members
where resource_list_member_id = x_resource_List_Member_Id;
select segment1
into x_project_number
from pa_projects
where project_id = x_project_id;
select time_phased_type_code, categorization_code
into x_period_type, x_categorization_code
from pa_budget_entry_methods pbem,
gms_budget_versions gbv
where gbv.budget_entry_method_code = pbem.budget_entry_method_code
and gbv.budget_version_id = x_budget_version_id;
select period_name
into x_period_name
from pa_budget_periods_v
where period_start_date = x_start_date
and period_end_date = x_end_date
and period_type_code = x_period_type;
select gbv.budget_version_id
, gbl.start_date
, gbl.end_date
from gms_budget_versions gbv
, gms_resource_assignments gra
, gms_budget_lines gbl
where gbv.budget_version_id = gra.budget_version_id
and gra.resource_assignment_id = gbl.resource_assignment_id
and gbv.project_id = p_project_id
and gbv.award_id = p_award_id
and gra.task_id = p_task_id
and gra.resource_list_member_id = p_resource_list_member_id
and gbv.current_flag = 'Y';/* bug 6444258*/
select gbv.budget_version_id
, gbl.start_date
, gbl.end_date
from gms_budget_versions gbv
, gms_resource_assignments gra
, gms_budget_lines gbl
where gbv.budget_version_id = gra.budget_version_id
and gra.resource_assignment_id = gbl.resource_assignment_id
and gbv.project_id = p_project_id
and gbv.award_id <> p_award_id
and gra.task_id = p_task_id
and gra.resource_list_member_id = p_resource_list_member_id
and gbv.current_flag = 'Y';
select time_phased_type_code
from gms_budget_versions gbv,
pa_budget_entry_methods pbem
where gbv.budget_entry_method_code = pbem.budget_entry_method_code
and gbv.budget_version_id = p_budget_version_id;
select budget_version_id,budget_entry_method_code
from gms_budget_versions
where project_id = p_project_id
and award_id <> p_award_id
and budget_status_code = 'B'
and current_flag = 'Y';
select budget_entry_method_code
from gms_budget_versions
where project_id = p_project_id
and award_id = p_award_id
and budget_status_code in ('W','S');
select categorization_code
from pa_budget_entry_methods
where budget_entry_method_code = p_budget_entry_method_code;
select resource_list_id
from gms_budget_versions
where project_id = p_project_id
and award_id = p_award_id
and budget_status_code in ('W','S');
select uncategorized_flag
from pa_resource_lists prl,
pa_implementations pi
where prl.business_group_id = pi.business_group_id
and prl.resource_list_id = p_resource_list_id;
select budget_version_id
from gms_budget_versions
where budget_status_code = 'B'
and project_id = p_project_id
and award_id = p_award_id;
SELECT *
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'BUDGET CHANGE REASON'
AND lookup_code = p_change_reason_code;
SELECT budget_version_id
, budget_status_code
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code IN ('W','S');
SELECT 'x'
FROM gms_budget_versions bv
, gms_resource_assignments ra
, gms_budget_lines bl
WHERE bv.budget_version_id = p_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
SELECT period_name
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_period_type_code;
IF gms_security.allow_update (x_award_id => l_award_id ) = 'N' THEN
-- The user does not have update privileges on this award
-- Hence , raise error
gms_error_pkg.gms_message(x_err_name => 'GMS_AWD_SECURITY_ENFORCED_UPD',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
select resource_list_id
into l_baselined_resource_list_id
from gms_budget_versions
where budget_version_id = l_baselined_version_id;
gms_budget_pub.delete_draft_budget
( p_api_version_number => 1.0
,p_pm_product_code => 'GMS'
,p_project_id => l_project_id
,p_award_id => l_award_id
,p_budget_type_code => p_budget_type_code
,x_err_code => x_err_code
,x_err_stage => x_err_stage --x_err_stage => x_err_code -- bug fix : 3004115
,x_err_stack => x_err_stack);
if x_err_code <> 0 -- this err code is from delete_draft_budget
then
gms_error_pkg.gms_message(x_err_name => 'GMS_DELETE_DRAFT_FAIL',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
insert into gms_budget_versions(
budget_version_id,
project_id,
award_id,
budget_type_code,
version_number,
budget_status_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
current_flag,
original_flag,
current_original_flag,
resource_accumulated_flag,
resource_list_id,
version_name,
budget_entry_method_code,
baselined_by_person_id,
baselined_date,
change_reason_code,
labor_quantity,
labor_unit_of_measure,
raw_cost,
burdened_cost,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
pm_product_code,
pm_budget_reference,
wf_status_code,
first_budget_period ) -- Bug 3104308
select
gms_budget_versions_s.nextval,
l_project_id,
l_award_id,
p_budget_type_code,
1,
'W',
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
'N',
'N',
'N',
'N',
l_resource_list_id,
l_budget_version_name,
p_entry_method_code,
NULL,
NULL,
p_change_reason_code,
NULL,
NULL,
NULL,
NULL,
l_description,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
p_pm_product_code,
p_pm_budget_reference,
NULL,
l_first_budget_period -- Bug 3104308
from sys.dual;
Select award_number ,org_id
into l_award_number ,l_org_id
from gms_awards_all
where award_id = p_award_id;
Select award_id
into l_award_id
from gms_awards_all
where award_number = p_award_number;
SELECT 1
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 1
FROM gms_resource_assignments
WHERE budget_version_id = p_budget_version_id;
SELECT budget_version_id, budget_status_code
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('W','S');
SELECT 1
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code = 'B';
SELECT v.resource_list_id,
t.project_type_class_code
FROM pa_project_types t,
pa_projects p,
gms_budget_versions v
WHERE v.budget_version_id = p_draft_version_id
AND v.project_id = p.project_id
AND p.project_type = t.project_type;
SELECT v.resource_list_id,
t.project_type_class_code,
prl.group_resource_type_id
FROM pa_project_types t,
pa_projects p,
pa_resource_lists prl,
gms_budget_versions v
WHERE v.budget_version_id = p_draft_version_id
AND v.project_id = p.project_id
AND prl.resource_list_id = v.resource_list_id
AND p.project_type = t.project_type;
SELECT pbem.time_phased_type_code, pbem.entry_level_code
FROM gms_budget_versions gbv,
pa_budget_entry_methods pbem
WHERE gbv.budget_version_id = p_budget_version_id
AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
SELECT award_number
FROM gms_awards_v
WHERE award_id = p_award_id;
SELECT segment1
FROM pa_projects
WHERE project_id = p_project_id;
SELECT 1
FROM gms_notifications
WHERE award_id = p_award_id
AND event_type = 'BUDGET_BASELINE';
SELECT BUDGET_WF_ENABLED_FLAG
FROM gms_awards
WHERE award_id = p_award_id;
SELECT 'x'
FROM gms_budget_versions
WHERE budget_version_id = p_budget_version_id
FOR UPDATE NOWAIT;
UPDATE gms_budget_versions
SET fc_required_flag = 'Y'
WHERE award_id = l_award_id
AND project_id = l_project_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('W','S');
UPDATE gms_budget_versions
SET fc_required_flag = 'N'
WHERE award_id = l_award_id
AND project_id = l_project_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('W','S');
UPDATE gms_budget_versions
SET --budget_status_code = 'S',
WF_status_code = 'IN_ROUTE',
conc_request_id = l_conc_request_id
WHERE budget_version_id = l_budget_version_id;
x_err_stage := 'GMS_BUDGET_PUB.BASELINE_BUDGET - In gms_budget_balance.update_gms_balance';
gms_budget_balance.update_gms_balance( x_project_id => l_project_id
, x_award_id => l_award_id
, x_mode => 'S'
, ERRBUF => x_err_stage
, RETCODE => l_fc_return_code);
UPDATE gms_budget_versions
SET budget_status_code = 'S',
WF_status_code = NULL,
conc_request_id = l_conc_request_id,
current_original_flag = l_mark_as_original -- Added for Bug:1578992
WHERE budget_version_id = l_budget_version_id;
select bv.budget_version_id,
bem.entry_level_code
into l_prev_baselined_version_id,
l_prev_entry_level_code
from gms_budget_versions bv,
pa_budget_entry_methods bem
where bv.award_id = l_award_id
and bv.project_id = l_project_id
and bv.budget_type_code = p_budget_type_code
and bv.budget_status_code = 'B'
and bv.current_flag = 'R'
and bv.budget_entry_method_code = bem.budget_entry_method_code;
gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Calling gms_budget_balance.update_gms_balance','C');
gms_budget_balance.update_gms_balance( x_project_id => l_project_id
, x_award_id => l_award_id
, x_mode => 'B'
, ERRBUF => x_err_stage
, RETCODE => l_fc_return_code);
UPDATE gms_budget_versions
SET budget_status_code = 'W', conc_request_id = l_conc_request_id
WHERE budget_version_id = l_budget_version_id;
update gms_budget_versions
set current_flag = 'N'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = p_budget_type_code
and budget_status_code = 'B'
and current_flag = 'R';
update gms_budget_versions
set current_flag = 'Y'
where budget_version_id = ( select max(budget_version_id)
from gms_budget_versions
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = p_budget_type_code);
select budget_version_id
into l_baselined_version_id
from gms_budget_versions
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = p_budget_type_code
and budget_status_code = 'B'
and current_flag = 'Y';
select distinct packet_id
into l_packet_id
from gms_bc_packets
where budget_version_id = l_baselined_version_id;
update gms_budget_versions
set current_flag = 'Y'
where budget_version_id = l_prev_baselined_version_id;
update gms_budget_versions
set current_flag = 'N'
where budget_version_id = l_baselined_version_id;
UPDATE gms_budget_versions
SET budget_status_code = 'S'
WHERE budget_version_id = l_budget_version_id;
SELECT profile_option_value
INTO l_user_profile_value1
FROM fnd_profile_options p,
fnd_profile_option_values v
WHERE p.profile_option_name = 'PA_SUPER_PROJECT'
AND v.profile_option_id = p.profile_option_id
AND v.level_id = 10004
AND v.level_value = fnd_global.user_id;
SELECT profile_option_value
INTO l_user_profile_value2
FROM fnd_profile_options p,
fnd_profile_option_values v
WHERE p.profile_option_name = 'PA_SUPER_PROJECT_VIEW'
AND v.profile_option_id = p.profile_option_id
AND v.level_id = 10004
AND v.level_value = fnd_global.user_id;
update gms_budget_versions
set current_flag = 'Y'
where budget_version_id = l_prev_baselined_version_id;
update gms_budget_versions
set current_flag = 'N'
where budget_version_id = l_baselined_version_id;
UPDATE gms_budget_versions
SET budget_status_code = 'S'
WHERE budget_version_id = l_budget_version_id;
update gms_budget_versions
set current_flag = 'Y'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = p_budget_type_code
and budget_status_code = 'B'
and current_flag = 'R';
update gms_budget_versions
set current_flag = 'Y'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = p_budget_type_code
and budget_status_code = 'B'
and current_flag = 'R';
SELECT *
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT budget_version_id, budget_entry_method_code, resource_list_id
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_status_code = 'W';
SELECT budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
IF gms_security.allow_update (x_award_id => l_award_id ) = 'N' THEN
-- The user does not have update privileges on this award
-- Hence , raise error
gms_error_pkg.gms_message(x_err_name => 'GMS_AWD_SECURITY_ENFORCED_UPD',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
NULL; --we don't insert budget lines with all zero's
select resource_assignment_id
into l_resource_assignment_id
from gms_resource_assignments
where budget_version_id = l_budget_version_id
and project_id = l_project_id
and NVL(task_id, 0) = NVL(l_task_id, 0) -- was p_pa_task_id
and resource_list_member_id = p_resource_list_member_id;
select gms_resource_assignments_s.nextval
into l_resource_assignment_id
from sys.dual;
insert into gms_resource_assignments
(resource_assignment_id,
budget_version_id,
project_id,
task_id,
resource_list_member_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unit_of_measure,
track_as_labor_flag)
values ( l_resource_assignment_id,
l_budget_version_id,
l_project_id,
l_task_id,
-- p_resource_list_member_id, commented for bug 3891250
l_member_id, -- Added for bug 3891250
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_unit_of_measure,
l_track_as_labor_flag);
insert into gms_budget_lines
(resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost,
change_reason_code,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
pm_product_code,
pm_budget_line_reference,
quantity_source,
raw_cost_source,
burdened_cost_source
)
values (
l_resource_assignment_id,
l_budget_start_date,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_budget_end_date,
l_period_name,
l_quantity,
pa_currency.round_currency_amt(l_raw_cost),
pa_currency.round_currency_amt(l_burdened_cost),
-- p_change_reason_code,
NULL, -- change_reason_code only applicable upon update
l_description,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
p_pm_product_code,
p_pm_budget_line_reference,
p_quantity_source,
p_raw_cost_source,
p_burdened_cost_source
);
PROCEDURE delete_draft_budget
( p_api_version_number IN NUMBER
,x_err_code IN OUT NOCOPY NUMBER
,x_err_stage IN OUT NOCOPY VARCHAR2
,x_err_stack IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_init_msg_list IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_pm_product_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_project_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_project_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_award_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_award_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR )
IS
CURSOR l_budget_version_csr
( p_project_id NUMBER
, p_award_id NUMBER
, p_budget_type_code VARCHAR2 )
IS
SELECT budget_version_id
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('W','S');-- Bug 1831122
SELECT 1
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 'x'
FROM gms_budget_versions bv
, gms_resource_assignments ra
, gms_budget_lines bl
WHERE bv.budget_version_id = p_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'delete_draft_budget';
x_err_stack := x_err_stack ||'-> Delete_Draft_Budget';
SAVEPOINT delete_draft_budget_pub;
l_module_name := 'GMS_PM_DELETE_DRAFT_BUDGET';
IF gms_security.allow_update (x_award_id => l_award_id ) = 'N' THEN
-- The user does not have update privileges on this award
-- Hence , raise error
gms_error_pkg.gms_message(x_err_name => 'GMS_AWD_SECURITY_ENFORCED_UPD',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
/** for b1_rec in ( select rowid
from gms_budget_lines
where resource_assignment_id
in
(select resource_assignment_id
from gms_resource_assignments
where budget_version_id = l_budget_version_id))
**/
for b1_rec in ( select gbl.rowid,
gra.resource_list_member_id,
gra.task_id,
gbl.start_date,
gbl.period_name
from gms_resource_assignments gra,
gms_budget_lines gbl
where gbl.resource_assignment_id = gra.resource_assignment_id
and gra.budget_version_id = l_budget_version_id )
loop
gms_budget_pub.delete_budget_line
( p_api_version_number => 1.0
,p_pm_product_code => 'GMS'
,p_project_id => l_project_id
,p_award_id => l_award_id
,p_budget_type_code => p_budget_type_code
,p_task_id => b1_rec.task_id
,p_resource_list_member_id => b1_rec.resource_list_member_id
,p_start_date => b1_rec.start_date
,p_period_name => b1_rec.period_name
,x_err_code => x_err_code
,x_err_stage => x_err_stage
,x_err_stack => x_err_stack);
gms_error_pkg.gms_message(x_err_name => 'GMS_DELETE_BUDGET_LINE_FAIL',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
delete gms_budget_versions
where budget_version_id = l_budget_version_id;
gms_error_pkg.gms_message(x_err_name => 'GMS_DELETE_DRAFT_FAIL',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
SAVEPOINT delete_draft_budget_pub;
ROLLBACK TO delete_draft_budget_pub;
END delete_draft_budget;
PROCEDURE delete_budget_line
( p_api_version_number IN NUMBER
,x_err_code IN OUT NOCOPY NUMBER
,x_err_stage IN OUT NOCOPY VARCHAR2
,x_err_stack IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_init_msg_list IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_pm_product_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_project_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_project_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_award_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_award_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_task_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_task_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_resource_alias IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_resource_list_member_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_start_date IN DATE := GMS_BUDGET_PUB.G_PA_MISS_DATE
,p_period_name IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR )
IS
CURSOR l_budget_version_csr
( p_project_id NUMBER
, p_award_id NUMBER
, p_budget_type_code VARCHAR2 )
IS
SELECT budget_version_id
, resource_list_id
, budget_entry_method_code
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('W','S');--Bug 1831122
SELECT 1
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT resource_assignment_id
FROM gms_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_member_id;
SELECT rowidtochar(rowid)
FROM gms_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND trunc(start_date) = nvl(trunc(p_start_date),trunc(start_date));
SELECT prlm.resource_list_member_id
FROM pa_resource_lists prl
, pa_resource_list_members prlm
, pa_implementations pi
WHERE prl.resource_list_id = prlm.resource_list_id
AND prl.business_group_id = pi.business_group_id
AND prl.uncategorized_flag='Y'
and NVL(prl.migration_code,'M') ='M' -- Bug 3626671
and NVL(prlm.migration_code,'M') ='M'; -- Bug 3626671;
SELECT time_phased_type_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code;
SELECT trunc(period_start_date), trunc(period_end_date) -- added end_date which is required in validate_budget()
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_time_phased_type_code;
SELECT 1
FROM pa_budget_periods_v
WHERE trunc(period_start_date) = trunc(p_start_date)
AND period_type_code = p_time_phased_type_code;
SELECT 'x'
FROM gms_budget_lines
WHERE rowid = p_budget_line_rowid
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'delete_budget_line';
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
x_err_stack := x_err_stack ||'-> Delete_Budget_Line';
SAVEPOINT delete_budget_line_pub;
l_module_name := 'GMS_PM_DELETE_BUDGET_LINE';
IF gms_security.allow_update (x_award_id => l_award_id ) = 'N' THEN
-- The user does not have update privileges on this award
-- Hence , raise error
gms_error_pkg.gms_message(x_err_name => 'GMS_AWD_SECURITY_ENFORCED_UPD',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
--, then all periods will be deleted
END IF;
select l.raw_cost,
l.burdened_cost,
l.quantity,
l.resource_assignment_id,
a.track_as_labor_flag
into l_raw_cost,
l_burdened_cost,
l_quantity,
l_resource_assignment_id,
l_track_as_labor_flag
from gms_resource_assignments a,
gms_budget_lines l
where l.rowid = l_budget_line_rowid
and l.resource_assignment_id = a.resource_assignment_id;
delete from gms_budget_lines
where rowid = l_budget_line_rowid;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
select budget_version_id
into l_budget_version_id
from gms_resource_assignments
where resource_assignment_id = l_resource_assignment_id;
delete gms_resource_assignments
where resource_assignment_id = l_resource_assignment_id
and not exists
(select 1
from gms_budget_lines
where resource_assignment_id = l_resource_assignment_id);
update gms_budget_versions
set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(l_raw_cost,0) ),
burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) - nvl(l_burdened_cost,0) ),
labor_quantity = (to_number(
decode(l_track_as_labor_flag,
'Y', nvl(labor_quantity,0) - nvl(l_quantity,0),
nvl(labor_quantity,0))) ),
last_update_date = SYSDATE,
last_update_login = l_last_update_login,
last_updated_by = l_last_updated_by
where budget_version_id = l_budget_version_id;
ROLLBACK TO delete_budget_line_pub;
ROLLBACK TO delete_budget_line_pub;
ROLLBACK TO delete_budget_line_pub;
END delete_budget_line;
PROCEDURE update_budget
( p_api_version_number IN NUMBER
,x_err_code IN OUT NOCOPY NUMBER
,x_err_stage IN OUT NOCOPY VARCHAR2
,x_err_stack IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_init_msg_list IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_pm_product_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_project_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_project_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_award_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_award_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_change_reason_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_description IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_budget_status_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_version_number IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_current_flag IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_original_flag IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_current_original_flag IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_resource_accumulated_flag IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_resource_list_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_version_name IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_budget_entry_method_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_baselined_by_person_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_baselined_date IN DATE := GMS_BUDGET_PUB.G_PA_MISS_DATE
,p_quantity IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_unit_of_measure IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_raw_cost IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_burdened_cost IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_attribute_category IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_first_budget_period IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_wf_status_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR)
IS
--needed to check the validity of the incoming budget type
CURSOR l_budget_type_csr
(p_budget_type_code VARCHAR2 )
IS
SELECT budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 'X'
FROM gms_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND start_date = p_budget_start_date;
SELECT budget_version_id
, budget_entry_method_code
, resource_list_id
, change_reason_code
, description
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('W','S');
SELECT time_phased_type_code
, entry_level_code
, categorization_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code;
SELECT resource_assignment_id
FROM gms_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_member_id;
SELECT period_name
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_period_type_code;
SELECT prlm.resource_list_member_id
FROM pa_resource_lists prl
, pa_resource_list_members prlm
, pa_implementations pi
WHERE prl.resource_list_id = prlm.resource_list_id
AND prl.business_group_id = pi.business_group_id
AND prl.uncategorized_flag='Y'
and NVL(prl.migration_code,'M') ='M' -- Bug 3626671
and NVL(prlm.migration_code,'M') ='M'; -- Bug 3626671;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'BUDGET CHANGE REASON'
AND lookup_code = p_change_reason_code;
SELECT 'x'
FROM gms_budget_versions bv
, gms_resource_assignments ra
, gms_budget_lines bl
WHERE bv.budget_version_id = p_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'update_budget';
l_update_yes_flag VARCHAR2(1);
x_err_stack := x_err_stack ||'-> Update_budget';
SAVEPOINT update_budget_pub;
l_module_name := 'GMS_PM_UPDATE_BUDGET';
IF gms_security.allow_update (x_award_id => l_award_id ) = 'N' THEN
-- The user does not have update privileges on this award
-- Hence , raise error
gms_error_pkg.gms_message(x_err_name => 'GMS_AWD_SECURITY_ENFORCED_UPD',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
UPDATE GMS_BUDGET_VERSIONS
SET change_reason_code = decode(p_change_reason_code,
GMS_BUDGET_PUB.G_MISS_CHAR,
change_reason_code,
p_change_reason_code),
budget_entry_method_code = decode(p_budget_entry_method_code,
GMS_BUDGET_PUB.G_MISS_CHAR,
budget_entry_method_code,
p_budget_entry_method_code),
resource_list_id = decode(p_resource_list_id,
GMS_BUDGET_PUB.G_MISS_NUM,
resource_list_id,
p_resource_list_id),
current_original_flag = decode(p_current_original_flag,
GMS_BUDGET_PUB.G_MISS_CHAR,
current_original_flag,
p_current_original_flag),
budget_status_code = decode(p_budget_status_code,
GMS_BUDGET_PUB.G_MISS_CHAR,
budget_status_code,
p_budget_status_code),
version_name = decode(p_version_name,
GMS_BUDGET_PUB.G_MISS_CHAR,
version_name,
p_version_name),
description = decode(p_description,
GMS_BUDGET_PUB.G_MISS_CHAR,
description,
p_description),
attribute_category = decode(p_attribute_category,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute_category,
p_attribute_category),
attribute1 = decode(p_attribute1,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute1,
p_attribute1),
attribute2 = decode(p_attribute2,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute2,
p_attribute2),
attribute3 = decode(p_attribute3,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute3,
p_attribute3),
attribute4 = decode(p_attribute4,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute4,
p_attribute4),
attribute5 = decode(p_attribute5,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute5,
p_attribute5),
attribute6 = decode(p_attribute6,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute6,
p_attribute6),
attribute7 = decode(p_attribute7,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute7,
p_attribute7),
attribute8 = decode(p_attribute8,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute8,
p_attribute8),
attribute9 = decode(p_attribute9,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute9,
p_attribute9),
attribute10 = decode(p_attribute10,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute10,
p_attribute10),
attribute11 = decode(p_attribute11,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute11),
attribute12 = decode(p_attribute12,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute12,
p_attribute12),
attribute13 = decode(p_attribute13,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute13,
p_attribute13),
attribute14 = decode(p_attribute14,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute14,
p_attribute14),
attribute15 = decode(p_attribute15,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute15,
p_attribute15),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
first_budget_period = decode(p_first_budget_period,
GMS_BUDGET_PUB.G_MISS_CHAR,
first_budget_period,
p_first_budget_period ) --Bug 3104308 : Added first_budget_period column in update
WHERE budget_version_id = l_budget_version_id;
ROLLBACK TO update_budget_pub;
END update_budget;
PROCEDURE update_budget_line
( p_api_version_number IN NUMBER
,x_err_code IN OUT NOCOPY NUMBER
,x_err_stage IN OUT NOCOPY VARCHAR2
,x_err_stack IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_init_msg_list IN VARCHAR2 := GMS_BUDGET_PUB.G_GMS_FALSE
,p_pm_product_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_project_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_project_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_task_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_task_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_award_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_award_number IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_resource_alias IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_resource_list_member_id IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_budget_start_date IN DATE := GMS_BUDGET_PUB.G_PA_MISS_DATE
,p_budget_end_date IN DATE := GMS_BUDGET_PUB.G_PA_MISS_DATE
,p_period_name IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_description IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_change_reason_code IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_raw_cost IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_burdened_cost IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_quantity IN NUMBER := GMS_BUDGET_PUB.G_PA_MISS_NUM
,p_unit_of_measure IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_track_as_labor_flag IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute_category IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_raw_cost_source IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_burdened_cost_source IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
,p_quantity_source IN VARCHAR2 := GMS_BUDGET_PUB.G_PA_MISS_CHAR
)
IS
--and check for valid resource_list / member combination
CURSOR l_resource_csr
(p_resource_list_member_id NUMBER
,p_resource_list_id NUMBER)
IS
SELECT 'X'
FROM pa_resource_list_members
WHERE resource_list_id = p_resource_list_id
AND resource_list_member_id = p_resource_list_member_id;
SELECT *
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'BUDGET CHANGE REASON'
AND lookup_code = p_change_reason_code;
SELECT trunc(period_start_date), trunc(period_end_date)
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_period_type_code;
SELECT budget_version_id
, budget_entry_method_code
, resource_list_id
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code IN ('W','S');
SELECT prlm.resource_list_member_id
FROM pa_resource_lists prl
, pa_resource_list_members prlm
, pa_implementations pi
WHERE prl.resource_list_id = prlm.resource_list_id
AND prl.business_group_id = pi.business_group_id
AND prl.uncategorized_flag='Y'
and NVL(prl.migration_code,'M') ='M' -- Bug 3626671
and NVL(prlm.migration_code,'M') ='M'; -- Bug 3626671;
SELECT resource_assignment_id
FROM gms_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_member_id;
SELECT rowidtochar(rowid)
FROM gms_budget_lines
WHERE resource_assignment_id = p_resource_assigment_id
AND trunc(start_date) = nvl(trunc(p_budget_start_date),trunc(start_date));
SELECT 'x'
FROM gms_budget_lines
WHERE rowid = p_budget_line_rowid
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'update_budget_line';
BEGIN -- update_budget_line
-- dbms_output.put_line('GMS_BUDGET_PUB.UPDATE_BUDGET_LINE - start');
x_err_stack := x_err_stack ||'-> Update_Budget_Line';
SAVEPOINT update_budget_line_pub;
l_module_name := 'GMS_PM_UPDATE_BUDGET_LINE';
IF gms_security.allow_update (x_award_id => l_award_id ) = 'N' THEN
-- The user does not have update privileges on this award
-- Hence , raise error
gms_error_pkg.gms_message(x_err_name => 'GMS_AWD_SECURITY_ENFORCED_UPD',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
UPDATE GMS_BUDGET_LINES
SET change_reason_code = decode(p_change_reason_code,
GMS_BUDGET_PUB.G_MISS_CHAR,
change_reason_code,
p_change_reason_code),
burdened_cost = decode(p_burdened_cost,
GMS_BUDGET_PUB.G_MISS_NUM,
burdened_cost,
p_burdened_cost),
description = decode(p_description,
GMS_BUDGET_PUB.G_MISS_CHAR,
description,
p_description),
attribute_category = decode(p_attribute_category,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute_category,
p_attribute_category),
attribute1 = decode(p_attribute1,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute1,
p_attribute1),
attribute2 = decode(p_attribute2,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute2,
p_attribute2),
attribute3 = decode(p_attribute3,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute3,
p_attribute3),
attribute4 = decode(p_attribute4,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute4,
p_attribute4),
attribute5 = decode(p_attribute5,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute5,
p_attribute5),
attribute6 = decode(p_attribute6,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute6,
p_attribute6),
attribute7 = decode(p_attribute7,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute7,
p_attribute7),
attribute8 = decode(p_attribute8,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute8,
p_attribute8),
attribute9 = decode(p_attribute9,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute9,
p_attribute9),
attribute10 = decode(p_attribute10,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute10,
p_attribute10),
attribute11 = decode(p_attribute11,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute11),
attribute12 = decode(p_attribute12,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute12,
p_attribute12),
attribute13 = decode(p_attribute13,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute13,
p_attribute13),
attribute14 = decode(p_attribute14,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute14,
p_attribute14),
attribute15 = decode(p_attribute15,
GMS_BUDGET_PUB.G_MISS_CHAR,
attribute15,
p_attribute15),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE resource_assignment_id = l_resource_assignment_id
AND start_date = l_budget_start_date
AND end_date = l_budget_end_date;
ROLLBACK TO update_budget_line_pub;
ROLLBACK TO update_budget_line_pub;
ROLLBACK TO update_budget_line_pub;
END update_budget_line;