[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select period_name, period_start_date , period_end_date
from pa_budget_periods_v
where period_type_code= x_period_type
and period_start_date > x_start_period_date
order by period_start_date ;
select period_name, period_start_date , period_end_date
from PA_budget_periods_v
where period_type_code= x_period_type
and period_start_date < x_start_period_date
order by period_start_date desc;
select count(*)
into number_period
from pa_budget_periods_v
where period_type_code= x_period_type
and period_start_date > x_start_period_date;
select count(*)
into number_period
from pa_budget_periods_v
where period_type_code= x_period_type
and period_start_date < x_start_period_date;
select p.period_name, p.start_date, p.end_date
bulk collect into t_period_name, t_start_date, t_end_date
from pa_periods p
order by p.start_date;
select p.period_name, p.start_date, p.end_date
bulk collect into t_period_name, t_start_date, t_end_date
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
order by p.start_date;
t_period_name.delete;
t_start_date.delete;
t_end_date.delete;
select count(1) -1
from pa_budget_periods_v
where period_type_code= x_period_type
and period_start_date between least(x_period_start_date1,x_period_start_date2) and greatest(x_period_start_date1,x_period_start_date2);
select start_date
into x_period_start_date1
from pa_periods
where x_start_date1 between start_date and end_date;
select start_date
into x_period_start_date2
from pa_periods
where x_start_date2 between start_date and end_date;
select count(1) - 1
into x_periods
from pa_periods
where start_date between least(x_period_start_date1, x_period_start_date2)
and greatest(x_period_start_date1, x_period_start_date2);
select p.start_date
into x_period_start_date1
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
and x_start_date1 between p.start_date and p.end_date;
select p.start_date
into x_period_start_date2
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
and x_start_date2 between p.start_date and p.end_date;
select count(1) - 1
into x_periods
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
and p.start_date between least(x_period_start_date1,x_period_start_date2)
and greatest(x_period_start_date1,x_period_start_date2);
x_last_update_login number(15);
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select v.project_id, v.award_id, v.budget_type_code, v.resource_list_id,
t.project_type_class_code,time_phased_type_code,
entry_level_code
into x_project_id, x_award_id, x_budget_type_code, x_resource_list_id,
x_project_type_class_code,x_time_phased_type_code,
x_entry_level_code
from pa_project_types t,
pa_projects p,
gms_budget_versions v,
pa_budget_entry_methods b
where v.budget_version_id = x_draft_version_id
and v.project_id = p.project_id
and b.budget_entry_method_code = v.budget_entry_method_code
and p.project_type = t.project_type;
x_err_stage := 'update current version <' || to_char(x_project_id) || '><'
|| x_budget_type_code || '>';
update gms_budget_versions
set original_flag = 'Y',
current_original_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code = x_budget_type_code
and current_original_flag = 'Y';
update gms_budget_versions
set current_flag = 'R',
last_update_date = SYSDATE,
last_updated_by = x_created_by,
last_update_login = x_last_update_login
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code = x_budget_type_code
and current_flag = 'Y';
select nvl(max(version_number), 0)
into max_version
from gms_budget_versions
where project_id = x_project_id
and award_id = x_award_id
and budget_type_code = x_budget_type_code
and budget_status_code = 'B';
select gms_budget_versions_s.nextval
into x_dest_version_id
from sys.dual;
gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Inserting records into gms_budget_versions','C');
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,
revenue,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
first_budget_period,
pm_product_code,
pm_budget_reference,
wf_status_code )
select
x_dest_version_id,
v.project_id,
v.award_id,
v.budget_type_code,
max_version + 1,
'B',
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
-- 'Y',
'N', -- 29-May-2000
'N',
x_mark_as_original,
'N',
v.resource_list_id,
v.version_name,
v.budget_entry_method_code,
v_emp_id,
SYSDATE,
v.change_reason_code,
(v.labor_quantity),
v.labor_unit_of_measure,
v.raw_cost,
v.burdened_cost,
v.revenue,
v.description,
v.attribute_category,
v.attribute1,
v.attribute2,
v.attribute3,
v.attribute4,
v.attribute5,
v.attribute6,
v.attribute7,
v.attribute8,
v.attribute9,
v.attribute10,
v.attribute11,
v.attribute12,
v.attribute13,
v.attribute14,
v.attribute15,
first_budget_period,
pm_product_code,
pm_budget_reference,
NULL
from gms_budget_versions v
where budget_version_id = x_draft_version_id;
update gms_budget_lines
set start_date= v_project_start_date,
end_date = v_project_completion_date
where resource_assignment_id in
(select resource_assignment_id
from gms_resource_assignments
where budget_version_id = x_dest_version_id)
and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
update gms_budget_lines
set start_date= v_project_start_date,
end_date = v_project_completion_date
where resource_assignment_id in
(select resource_assignment_id
from gms_resource_assignments
where budget_version_id = x_draft_version_id)
and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
select start_date,completion_date
into v_project_start_date,
v_project_completion_date
from pa_projects_all
where project_id = x_project_id;
for b1_rec in (select t.task_id, resource_assignment_id
from pa_tasks t , gms_resource_assignments r
where t.task_id = r.task_id
and r.budget_version_id = x_dest_version_id) loop
-- Added the call to gms_budget_utils.get_valid_period_dates() for Bug: 2592747
gms_budget_utils.get_valid_period_dates(
x_err_code => x_err_code,
x_err_stage => x_err_stage,
p_project_id => x_project_id,
p_task_id => b1_rec.task_id,
p_award_id => x_award_id,
p_time_phased_type_code => x_time_phased_type_code,
p_entry_level_code => x_entry_level_code,
p_period_name_in => null,
p_budget_start_date_in => null,
p_budget_end_date_in => null,
p_period_name_out => x_period_name,
p_budget_start_date_out => x_budget_start_date,
p_budget_end_date_out => x_budget_end_date);
update gms_budget_lines
set start_date = x_budget_start_date,
end_date = x_budget_end_date
where resource_assignment_id = b1_rec.resource_assignment_id
and ((start_date <> x_budget_start_date) or (end_date <> x_budget_end_date));
for b2_rec in (select t.task_id, resource_assignment_id
from pa_tasks t , gms_resource_assignments r
where t.task_id = r.task_id
and r.budget_version_id = x_draft_version_id) loop
-- Added the call to gms_budget_utils.get_valid_period_dates() for Bug: 2592747
gms_budget_utils.get_valid_period_dates(
x_err_code => x_err_code,
x_err_stage => x_err_stage,
p_project_id => x_project_id,
p_task_id => b2_rec.task_id,
p_award_id => x_award_id,
p_time_phased_type_code => x_time_phased_type_code,
p_entry_level_code => x_entry_level_code,
p_period_name_in => null,
p_budget_start_date_in => null,
p_budget_end_date_in => null,
p_period_name_out => x_period_name,
p_budget_start_date_out => x_budget_start_date,
p_budget_end_date_out => x_budget_end_date);
update gms_budget_lines
set start_date = x_budget_start_date,
end_date = x_budget_end_date
where resource_assignment_id = b2_rec.resource_assignment_id
and ((start_date <> x_budget_start_date) or (end_date <> x_budget_end_date));
x_last_update_login number;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select start_date
into g_project_start_date
from pa_projects a, gms_budget_versions b
where b.budget_version_id = x_src_version_id
and a.project_id = b.project_id;
select resource_list_id,first_budget_period
into x_src_resource_list_id, x_first_budget_period
from gms_budget_versions
where budget_version_id = x_src_version_id;
select resource_list_id
into x_baselined_resource_list_id
from gms_budget_versions
where budget_version_id = x_baselined_version_id;
x_err_stage := 'delete old draft budget <' || to_char(x_dest_project_id)
|| '><' || x_dest_budget_type_code || '>' ;
-- draft budget exists, delete it
-- GMS_BUDGET_UTILS.delete_draft(x_dest_version_id,
GMS_BUDGET_PUB.delete_draft_budget(
p_api_version_number => 1.0,
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack,
p_pm_product_code => 'GMS',
p_project_id => x_dest_project_id,
p_award_id => x_dest_award_id,
p_budget_type_code => x_dest_budget_type_code);
gms_error_pkg.gms_message( x_err_name => 'GMS_DELETE_DRAFT_FAILED',
x_err_code => x_err_code,
x_err_buff => x_err_stage);
select m.time_phased_type_code,
m.entry_level_code
into x_time_phased_type_code,
x_entry_level_code
from pa_budget_entry_methods m,
gms_budget_versions v
where v.budget_version_id = x_src_version_id
and v.budget_entry_method_code = m.budget_entry_method_code;
select period_start_date
into x_fbp_start_date
from pa_budget_periods_v
where period_type_code= x_time_phased_type_code
and period_name = x_first_budget_period;
select start_date
into x_fbp_start_date
from pa_periods
where period_name = x_first_budget_period;
select start_date
into x_fbp_start_date
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
and p.period_name = x_first_budget_period;
select gms_budget_versions_s.nextval
into x_dest_version_id
from sys.dual;
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,
revenue,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
first_budget_period,
wf_status_code
)
select
x_dest_version_id,
x_dest_project_id,
x_dest_award_id,
x_dest_budget_type_code,
1,
'W',
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
'N',
'N',
'N',
'N',
v.resource_list_id,
v.version_name,
v.budget_entry_method_code,
NULL,
NULL,
v.change_reason_code,
NULL,
NULL,
NULL,
NULL,
NULL,
v.description,
v.attribute_category,
v.attribute1,
v.attribute2,
v.attribute3,
v.attribute4,
v.attribute5,
v.attribute6,
v.attribute7,
v.attribute8,
v.attribute9,
v.attribute10,
v.attribute11,
v.attribute12,
v.attribute13,
v.attribute14,
v.attribute15,
x_first_budget_period,
NULL
from gms_budget_versions v
where v.budget_version_id = x_src_version_id;
x_last_update_login NUMBER(15);
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select m.time_phased_type_code,
m.entry_level_code
into x_time_phased_type_code,
x_entry_level_code
from pa_budget_entry_methods m,
gms_budget_versions v
where v.budget_version_id = x_src_version_id
and v.budget_entry_method_code = m.budget_entry_method_code;
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)
select gms_resource_assignments_s.nextval,
x_dest_version_id,
dt.project_id,
dt.task_id,
sa.resource_list_member_id,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
sa.unit_of_measure,
sa.track_as_labor_flag
from
gms_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
gms_budget_versions dv
where sa.budget_version_id = x_src_version_id
and sa.project_id = st.project_id
and sa.task_id = st.task_id
and st.task_number = dt.task_number
and dt.project_id = dv.project_id
and dv.budget_version_id = x_dest_version_id;
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)
select gms_resource_assignments_s.nextval,
x_dest_version_id,
dv.project_id,
0,
sa.resource_list_member_id,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
sa.unit_of_measure,
sa.track_as_labor_flag
from
gms_resource_assignments sa,
gms_budget_versions dv
where sa.budget_version_id = x_src_version_id
and sa.task_id = 0
and dv.budget_version_id = x_dest_version_id;
(select l.resource_assignment_id, l.start_date, l.end_date,a.task_id
from gms_budget_lines l,
gms_resource_assignments a
where a.budget_version_id = x_src_version_id
and a.resource_assignment_id = l.resource_assignment_id
) loop
x_period_name := NULL;
select start_date
into x_task_start_date
from pa_tasks
where task_id = budget_line_row.task_id;
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,
revenue,
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,
raw_cost_source,
burdened_cost_source,
quantity_source,
revenue_source
)
select
da.resource_assignment_id,
decode(x_start_date, NULL, l.start_date, x_start_date),
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
decode(x_end_date, NULL, l.end_date, x_end_date),
decode(x_period_name, NULL, l.period_name, x_period_name),
l.quantity,
round(l.raw_cost * amount_change_pct, rounding_precision),
round(l.burdened_cost * amount_change_pct, rounding_precision),
round(l.revenue * amount_change_pct, rounding_precision),
l.change_reason_code,
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,
decode(x_pm_flag,'Y',l.pm_product_code,NULL),
decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
'B',
'B',
'B',
'B'
from gms_budget_lines l,
gms_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
gms_resource_assignments da
where l.resource_assignment_id =
budget_line_row.resource_assignment_id
and l.start_date = budget_line_row.start_date
and l.resource_assignment_id = sa.resource_assignment_id
and sa.budget_version_id = x_src_version_id
and sa.task_id = st.task_id
and sa.project_id = st.project_id
and sa.resource_list_member_id = da.resource_list_member_id
and st.task_number = dt.task_number
and dt.task_id = da.task_id
and dt.project_id = da.project_id
and da.budget_version_id = x_dest_version_id;
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,
revenue,
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,
raw_cost_source,
burdened_cost_source,
quantity_source,
revenue_source
)
select
da.resource_assignment_id,
decode(x_start_date, NULL, l.start_date, x_start_date),
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
decode(x_end_date, NULL, l.end_date, x_end_date),
decode(x_period_name, NULL, l.period_name, x_period_name),
l.quantity,
round(l.raw_cost * amount_change_pct, rounding_precision),
round(l.burdened_cost * amount_change_pct, rounding_precision),
round(l.revenue * amount_change_pct, rounding_precision),
l.change_reason_code,
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,
decode(x_pm_flag,'Y',l.pm_product_code,NULL),
decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
'B',
'B',
'B',
'B'
from gms_budget_lines l,
gms_resource_assignments sa,
gms_resource_assignments da
where l.resource_assignment_id =
budget_line_row.resource_assignment_id
and l.start_date = budget_line_row.start_date
and l.resource_assignment_id = sa.resource_assignment_id
and sa.budget_version_id = x_src_version_id
and sa.task_id = 0
and sa.resource_list_member_id = da.resource_list_member_id
and da.task_id = 0
and da.budget_version_id = x_dest_version_id;
SELECT task_id,resource_list_member_id
FROM gms_resource_assignments gra, gms_budget_lines gbl
WHERE gra.resource_assignment_id = gbl.resource_assignment_id
AND gra.budget_version_id = draft_bvid;
SELECT gbv.budget_entry_method_code, resource_list_id,
entry_level_code
FROM gms_budget_versions gbv, pa_budget_entry_methods pbem
WHERE gbv.budget_version_id = p_budget_version
AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
SELECT MIN (start_date), MAX (end_date)
FROM gms_resource_assignments gra, gms_budget_lines gbl
WHERE gra.resource_assignment_id = gbl.resource_assignment_id
AND gra.budget_version_id = draft_bvid;
SELECT MIN (start_date), MAX (end_date)
FROM gms_balances
WHERE budget_version_id = bal_bvid;
SELECT 1
INTO dummy
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM pa_tasks
WHERE project_id = x_project_id
AND task_id = x_task_id
AND task_id = top_task_id
AND EXISTS (select 1
FROM pa_tasks
where nvl(parent_task_id,0) = x_task_id
and project_id = x_project_id));
SELECT 1
INTO dummy
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM pa_resource_list_members prl
WHERE prl.resource_list_member_id = x_rlmid
AND prl.parent_member_id IS NULL );
SELECT period_name
FROM pa_budget_periods_v
WHERE period_type_code = p_time_phase_type_code
AND p_period_date BETWEEN period_start_date AND period_end_date;
SELECT MIN (start_date), MAX (end_date)
INTO x_gbal_start_date, x_gbal_end_date
FROM gms_balances gb, gms_budget_versions gbv
WHERE gb.budget_version_id = gbv.budget_version_id
AND gb.project_id = x_project_id
AND gb.award_id = x_award_id
AND gbv.award_id = gb.award_id
AND gbv.project_id = gb.project_id
AND gbv.current_flag = 'Y'
AND gb.actual_period_to_date IS NOT NULL
AND gb.encumb_period_to_date IS NOT NULL
GROUP BY gb.budget_version_id;
SELECT MIN (start_date), MAX (end_date)
INTO x_pdb_start_date, x_pdb_end_date
FROM gms_resource_assignments gra,
gms_budget_versions gbv,
gms_budget_lines gbl
WHERE gbv.budget_version_id = gra.budget_version_id
AND gbv.budget_version_id = x_version_id
AND gra.resource_assignment_id = gbl.resource_assignment_id
GROUP BY gbv.budget_version_id;
select period_name
into x_start_period_name
from pa_periods
where x_start_date between start_date and end_date;
select period_name
into x_end_period_name
from pa_periods
where x_end_date between start_date and end_date;
select p.period_name
into x_start_period_name
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
and x_start_date between p.start_date and p.end_date;
select p.period_name
into x_end_period_name
from gl_period_statuses p, pa_implementations i
where i.set_of_books_id = p.set_of_books_id
and p.application_id = pa_period_process_pkg.application_id
and p.adjustment_period_flag = 'N'
and x_end_date between p.start_date and p.end_date;
x_last_update_login number(15);
select period_name,
start_date,
end_date
from pa_periods
where start_date between x_start_period_start_date
and x_end_period_end_date;
select p.period_name,
p.start_date,
p.end_date
from gl_period_statuses p,
pa_implementations i
where p.application_id = 101
and p.set_of_books_id = i.set_of_books_id
and p.start_date between x_start_period_start_date
and x_end_period_end_date
and p.adjustment_period_flag = 'N'; -- 7653209 ;
SELECT budget_version_id
FROM gms_budget_versions
WHERE award_id = x_award_id
AND project_id = x_project_id
AND current_flag = 'Y';
select budget_amount_code
from gms_budget_versions b, pa_budget_types t
where b.budget_version_id = x_version_id
and b.budget_type_code = t.budget_type_code;
select gra.task_id,
gra.resource_list_member_id,
gbl.period_name,
gbl.start_date
from gms_resource_assignments gra,
gms_budget_lines gbl
where gbl.resource_assignment_id = gra.resource_assignment_id
and gra.budget_version_id = p_budget_version_id
and gbl.start_date between p_start_date and p_end_date;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select entry_level_code, categorization_code,
time_phased_type_code
into x_entry_level_code, x_categorization_code,
x_time_phased_type_code
from pa_budget_entry_methods
where budget_entry_method_code = x_budget_entry_method_code;
select m.resource_list_member_id,
m.track_as_labor_flag,
r.unit_of_measure
into x_uncat_res_list_member_id,
x_uncat_track_as_labor_flag,
x_uncat_unit_of_measure
from pa_resources r,
pa_resource_list_members m,
--gms_implementations gia, -- Commented out NOCOPY for Bug:2113499
pa_implementations pi,
pa_resource_lists l
where l.uncategorized_flag = 'Y'
and l.resource_list_id = m.resource_list_id
--and gia.org_id = l.business_group_id -- Commented out NOCOPY for Bug:2113499
and pi.business_group_id = l.business_group_id
and m.resource_id = r.resource_id
AND NVL(m.migration_code,'M') ='M' -- Bug 3626671
AND NVL(l.migration_code,'M') ='M'; -- Bug 3626671
select start_date
into x_start_period_start_date
from pa_periods
where period_name = x_start_period;
select end_date
into x_end_period_end_date
from pa_periods
where period_name = x_end_period;
select start_date
into x_start_period_start_date
from gl_period_statuses p,
pa_implementations i
where p.period_name = x_start_period
and p.application_id = 101
and p.set_of_books_id = i.set_of_books_id;
select end_date
into x_end_period_end_date
from gl_period_statuses p,
pa_implementations i
where p.period_name = x_end_period
and p.application_id = 101
and p.set_of_books_id = i.set_of_books_id;
x_err_stage := 'delete budget lines <' || to_char(x_version_id)
|| '><' || x_start_period
|| '><' || x_end_period
|| '>';
select rowid
from gms_budget_lines l
where l.resource_assignment_id in
(select a.resource_assignment_id
from gms_resource_assignments a
where a.budget_version_id = x_version_id)
and l.start_date between x_start_period_start_date and
x_end_period_end_date) loop
*/
for budget_lines_rec in budget_lines_csr(x_version_id
,x_start_period_start_date
,x_end_period_end_date)
loop
gms_budget_pub.delete_budget_line(
p_api_version_number => 1.0,
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack=> x_err_stack,
p_pm_product_code => 'GMS',
p_project_id => x_project_id,
p_award_id => x_award_id,
p_budget_type_code => 'AC', -- changed from C to AC
p_task_id => budget_lines_rec.task_id,
p_resource_list_member_id => budget_lines_rec.resource_list_member_id,
p_start_date => budget_lines_rec.start_date,
p_period_name => budget_lines_rec.period_name
);
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);
for res_rec in (select m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
from pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id
AND NVL(m1.migration_code,'M') ='M') -- Bug 3626671
and exists (select 1 -- Bug 1831151
from gms_balances gb
where budget_version_id = x_current_budget_version_id
and gb.resource_list_member_id=m.resource_list_member_id)
AND NVL(m.migration_code,'M') ='M') -- Bug 3626671
loop
x_err_stage := 'process period and resource <'
|| period_rec.period_name
|| '><' || to_char(res_rec.resource_list_member_id)
|| '>';
for top_task_rec in (select t.task_id
from pa_tasks t
where t.project_id = x_project_id
and t.task_id = t.top_task_id
and EXISTS ( SELECT 1 -- added for bug 1831151
FROM gms_balances gb
WHERE gb.budget_version_id =
x_current_budget_version_id
AND gb.top_task_id = t.task_id )) loop
x_burdened_cost:= 0;
for res_rec in (select m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
from pa_resource_list_members m
where m.resource_list_id =
x_resource_list_id
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id
AND NVL(m1.migration_code,'M') ='M') -- Bug 3626671
AND EXISTS ( SELECT 1-- added for bug 1831151
FROM gms_balances gb
WHERE budget_version_id =
x_current_budget_version_id
AND gb.resource_list_member_id =
m.resource_list_member_id)
AND NVL(m.migration_code,'M') ='M') loop -- Bug 3626671
x_burdened_cost:= 0;
for task_rec in (select t.task_id
from pa_tasks t
where t.project_id = x_project_id
and not exists
(select 1
from pa_tasks t1
where t1.parent_task_id = t.task_id)
AND EXISTS ( SELECT 1 --bug 1831151
FROM gms_balances
WHERE budget_version_id =
x_current_budget_version_id
AND task_id = t.task_id)
) loop
if (x_categorization_code = 'N') then
x_burdened_cost := 0;
for res_rec in (select m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
from pa_resource_list_members m
where m.resource_list_id =
x_resource_list_id
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id
AND NVL(m1.migration_code,'M') ='M') -- Bug 3626671
AND EXISTS ( SELECT 1 -- Bug 1831151
FROM gms_balances gb
WHERE budget_version_id =
x_current_budget_version_id
AND gb.resource_list_member_id =
m.resource_list_member_id )
AND NVL(m.migration_code,'M') ='M') loop -- Bug 3626671
x_err_stage := 'process period/task/resource <'
|| period_rec.period_name
|| '><' || to_char(task_rec.task_id)
|| '><' || to_char(res_rec.resource_list_member_id)
|| '>';
SELECT gmsb.actual_period_to_date tot_burdened_cost,
gmsb.actual_period_to_date tot_billable_burdened_cost
FROM gms_balances gmsb, pa_periods pp
WHERE gmsb.project_id = x_project_id
AND gmsb.budget_version_id = x_current_budget_version_id
AND gmsb.task_id = DECODE (
x_task_id,
NULL, gmsb.task_id,
0, gmsb.task_id,
gmsb.top_task_id,gmsb.task_id,
x_task_id
)
AND x_period_type = 'P'
AND pp.start_date >= gmsb.start_date
AND pp.end_date <= gmsb.end_date
AND gmsb.balance_type = 'EXP'
AND pp.start_date BETWEEN NVL (x_prd_start_date, pp.start_date)
AND NVL (x_prd_end_date, pp.end_date);
SELECT gmsb.actual_period_to_date tot_burdened_cost,
gmsb.actual_period_to_date tot_billable_burdened_cost
FROM pa_implementations imp,
gl_period_statuses glp,
gms_balances gmsb
WHERE gmsb.project_id = x_project_id
AND gmsb.budget_version_id = x_current_budget_version_id
AND gmsb.task_id = DECODE (
x_task_id,
NULL, gmsb.task_id,
0, gmsb.task_id,
gmsb.top_task_id,gmsb.task_id ,
x_task_id
)
AND x_period_type = 'G'
AND gmsb.balance_type = 'EXP'
AND glp.set_of_books_id = imp.set_of_books_id
AND glp.application_id = 101
AND glp.start_date >= gmsb.start_date
AND glp.end_date <= gmsb.end_date
AND glp.adjustment_period_flag = 'N'
AND glp.start_date BETWEEN NVL (x_prd_start_date, glp.start_date)
AND NVL (x_prd_end_date, glp.end_date);
SELECT gmsb.actual_period_to_date tot_burdened_cost,
gmsb.actual_period_to_date tot_billable_burdened_cost
FROM gms_balances gmsb, pa_periods pp
WHERE gmsb.project_id = x_project_id
AND gmsb.budget_version_id = x_current_budget_version_id
AND gmsb.task_id = DECODE (
x_task_id,
NULL, gmsb.task_id,
0, gmsb.task_id,
gmsb.top_task_id,gmsb.task_id ,
x_task_id
)
AND gmsb.resource_list_member_id = x_resource_list_member_id
AND x_period_type = 'P'
AND gmsb.balance_type = 'EXP'
AND pp.start_date >= gmsb.start_date
AND pp.end_date <= gmsb.end_date
AND pp.start_date BETWEEN NVL (x_prd_start_date, pp.start_date)
AND NVL (x_prd_end_date, pp.end_date)
AND NVL (gmsb.actual_period_to_date, 0) <> 0;
SELECT gmsb.actual_period_to_date tot_burdened_cost,
gmsb.actual_period_to_date tot_billable_burdened_cost
FROM pa_implementations imp,
gl_period_statuses glp,
gms_balances gmsb
WHERE gmsb.project_id = x_project_id
AND gmsb.budget_version_id = x_current_budget_version_id
AND gmsb.task_id = DECODE (
x_task_id,
NULL, gmsb.task_id,
0, gmsb.task_id,
gmsb.top_task_id,gmsb.task_id ,
x_task_id
)
AND gmsb.resource_list_member_id = x_resource_list_member_id
AND x_period_type = 'G'
AND gmsb.balance_type = 'EXP'
AND glp.set_of_books_id = imp.set_of_books_id
AND glp.application_id = 101
AND glp.start_date >= gmsb.start_date
AND glp.end_date <= gmsb.end_date
AND glp.adjustment_period_flag = 'N'
AND glp.start_date BETWEEN NVL (x_prd_start_date, glp.start_date)
AND NVL (x_prd_end_date, glp.end_date)
AND NVL (gmsb.actual_period_to_date, 0) <> 0;
x_last_update_login NUMBER(15);
x_last_update_login := FND_GLOBAL.LOGIN_ID;
gms_error_pkg.gms_debug('GMS_BUDGET_CORE.COPY_DRAFT_LINES- Inserting records into gms_resource_assignments','C');
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)
select gms_resource_assignments_s.nextval,
x_dest_version_id,
s.project_id,
s.task_id,
s.resource_list_member_id,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
s.unit_of_measure,
s.track_as_labor_flag
from
gms_resource_assignments s
where s.budget_version_id = x_src_version_id;
gms_error_pkg.gms_debug('GMS_BUDGET_CORE.COPY_DRAFT_LINES- Inserting records into gms_budget_lines','C');
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,
revenue,
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,
raw_cost_source,
burdened_cost_source,
quantity_source,
revenue_source
)
select
da.resource_assignment_id,
l.start_date,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
l.end_date,
l.period_name,
l.quantity,
l.raw_cost,
l.burdened_cost,
l.revenue,
l.change_reason_code,
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,
decode(x_pm_flag,'Y',l.pm_product_code,NULL),
decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
'B',
'B',
'B',
'B'
from gms_budget_lines l,
gms_resource_assignments sa,
gms_resource_assignments da
where l.resource_assignment_id = sa.resource_assignment_id
and sa.budget_version_id = x_src_version_id
and sa.task_id = da.task_id
and sa.project_id = da.project_id
and sa.resource_list_member_id = da.resource_list_member_id
and da.budget_version_id = x_dest_version_id;