[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select period_name, start_date , end_date
from PA_periods
where start_date > x_start_period_date
order by start_date ;
select period_name, start_date , end_date
from PA_periods
where start_date < x_start_period_date
order by start_date desc;
select period_name, start_date , 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'
and start_date > x_start_period_date
order by start_date ;
select period_name, start_date , 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'
and start_date < x_start_period_date
order by start_date desc;
select count(*)
into number_period
from pa_periods
where start_date > x_start_period_date;
select count(*)
into number_period
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 start_date > x_start_period_date;
select count(*)
into number_period
from pa_periods
where start_date < x_start_period_date;
select count(*)
into number_period
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 start_date < x_start_period_date;
select count(1) -1
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 count(1) -1
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 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_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 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 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 start_date and end_date;
x_last_update_login number(15);
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select v.project_id, v.budget_type_code, v.resource_list_id,
t.project_type_class_code,time_phased_type_code,
entry_level_code,v.pm_product_code
into x_project_id, x_budget_type_code, x_resource_list_id,
x_project_type_class_code,x_time_phased_type_code,
x_entry_level_code, x_pm_product_code
from pa_project_types_all t,
pa_projects_all p,
pa_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
and nvl(p.org_id, -99) = nvl(t.org_id, -99);
x_err_stage := 'Calling pa_billing_core.update_funding';
pa_billing_core.update_funding(
x_project_id,
x_funding_level, -- Funding level
x_err_code,
x_err_stage,
x_err_stack);
print_msg('End of update_funding:errCode['||x_err_code||']ErrStage['||x_err_stage||']');
x_err_stage := 'Calling pa_billing_core.update_funding for AC budget';
pa_billing_core.update_funding(
x_project_id,
x_funding_level, -- Funding level
x_err_code,
x_err_stage,
x_err_stack);
print_msg('End of update_funding:errCode['||x_err_code||']ErrStage['||x_err_stage||']');
x_err_stage := 'update current version <' || to_char(x_project_id) || '><'
|| x_budget_type_code || '>';
update pa_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 budget_type_code = x_budget_type_code
and current_original_flag = 'Y'
and budget_status_code = 'B'; -- added for bug 9950839
update pa_budget_versions
set current_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 budget_type_code = x_budget_type_code
and current_flag = 'Y';
select nvl(max(version_number), 0)
into max_version
from pa_budget_versions
where project_id = x_project_id
and budget_type_code = x_budget_type_code
and budget_status_code = 'B';
select pa_budget_versions_s.nextval
into x_dest_version_id
from sys.dual;
insert into pa_budget_versions(
budget_version_id,
project_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,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
)
select
x_dest_version_id,
v.project_id,
v.budget_type_code,
max_version + 1,
'B',
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
'Y',
'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,
-- pa_currency.round_currency_amt(v.raw_cost),
-- pa_currency.round_currency_amt(v.burdened_cost),
-- pa_currency.round_currency_amt(v.revenue),
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,
decode(v.budget_type_code,'AC','Y','N'),
decode(v.budget_type_code,'AR','Y','N')
from pa_budget_versions v
where budget_version_id = x_draft_version_id;
x_err_stage:= 'Non-Time Phase: Project Level update';
-- Added nvl in the below select query for the bug 11891595
select start_date,nvl(completion_date,PA_BUDGET_CHECK_PVT.Get_project_end_date) -- Changed for the bug 13344742
into v_project_start_date,
v_project_completion_date
from pa_projects_all
where project_id = x_project_id;
update pa_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 pa_resource_assignments
where budget_version_id = x_dest_version_id)
and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
x_err_stage:= 'Non-Time Phase: Task Level update';
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 bl_rec in (select start_date,
completion_date ,
resource_assignment_id
from pa_tasks t ,pa_resource_assignments r
where t.task_id = r.task_id
and r.budget_version_id = x_dest_version_id) loop
bl_rec.start_date := nvl(bl_rec.start_date,v_project_start_date);
UPDATE pa_budget_lines
SET start_date = bl_rec.start_date,
end_date = bl_rec.completion_date
WHERE resource_assignment_id = bl_rec.resource_assignment_id
AND ((start_date <> bl_rec.start_date) or (end_date <> bl_rec.completion_date));
* shows ORA-01400: cannot insert NULL into (PA."PA_WF_PROCESSES.ENTITY_KEY2)
* error. so execute this api in a begin , end block and set the error status
*/
BEGIN
-- Copy attachments for every draft budget copied
x_err_stage:= 'Calling fnd_attached_documents2_pkg.copy_attachments';
procedure copy_without_delete (p_src_version_id in number,
p_amount_change_pct in number,
p_rounding_precision in number,
p_dest_project_id in number,
p_dest_budget_type_code in varchar2,
x_err_code in out NOCOPY number, -- added NOCOPY to pass GSCC errors for bug 5838587
x_err_stage in out NOCOPY varchar2, -- added NOCOPY to pass GSCC errors for bug 5838587
x_err_stack in out NOCOPY varchar2) -- added NOCOPY to pass GSCC errors for bug 5838587
is
old_stack varchar2(630);
l_last_update_login number;
x_err_stack := x_err_stack || '->copy_without_delete';
l_last_update_login := FND_GLOBAL.LOGIN_ID;
select m.time_phased_type_code,
m.entry_level_code,
v.resource_list_id
into l_src_time_phased_type_code,
l_src_entry_level_code,
l_src_resource_list_id
from pa_budget_entry_methods m,
pa_budget_versions v
where v.budget_version_id = p_src_version_id
and v.budget_entry_method_code = m.budget_entry_method_code;
select resource_list_id
into l_baselined_resource_list_id
from pa_budget_versions
where budget_version_id = l_baselined_version_id;
select pa_budget_versions_s.nextval
into l_dest_version_id
from sys.dual;
insert into pa_budget_versions(
budget_version_id,
project_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,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
)
select
l_dest_version_id,
p_dest_project_id,
p_dest_budget_type_code,
1,
'W',
SYSDATE,
l_created_by,
SYSDATE,
l_created_by,
l_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,
v.first_budget_period,
NULL,
decode(p_dest_budget_type_code,'AC','Y','N'), /* Bug 5076424 */
decode(p_dest_budget_type_code,'AR','Y','N') /* Bug 5076424 */
from pa_budget_versions v
where v.budget_version_id = p_src_version_id;
SELECT 'Y' into l_cls_prds_exists
FROM pa_budget_lines l
, gl_period_statuses s
, pa_implementations i
, pa_budget_versions v
where s.application_id = pa_period_process_pkg.application_id
and i.set_of_books_id = s.set_of_books_id
and l.budget_version_id=v.budget_version_id
and s.closing_status in ('C','P')
and l.period_name = s.period_name
and v.budget_version_id = l_dest_version_id
and rownum < 2;
select m.time_phased_type_code,
m.entry_level_code,
v.resource_list_id
into l_dest_time_phased_type_code,
l_dest_entry_level_code,
l_dest_resource_list_id
from pa_budget_entry_methods m,
pa_budget_versions v
where v.budget_version_id = l_dest_version_id
and v.budget_entry_method_code = m.budget_entry_method_code;
update pa_budget_versions
set (version_number,
budget_status_code,
last_update_date,
last_updated_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,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
)=(
select
1,
'W',
SYSDATE,
l_created_by,
l_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,
v.first_budget_period,
NULL,
decode(p_dest_budget_type_code,'AC','Y','N'),
decode(p_dest_budget_type_code,'AR','Y','N')
from pa_budget_versions v
where v.budget_version_id = p_src_version_id
)
where budget_version_id = l_dest_version_id;
fnd_attached_documents2_pkg.delete_attachments('PA_BUDGET_VERSIONS',
l_dest_version_id,
null, null, null, null,
'Y') ;
x_err_stage := 'About to delete budget lines in closed gl periods in dest';
delete from pa_budget_lines
where budget_version_id=l_dest_version_id
and period_name not in (select s.period_name
from gl_period_statuses s
, pa_implementations i
where s.application_id = pa_period_process_pkg.application_id
and i.set_of_books_id = s.set_of_books_id
and s.closing_status in ('C','P'));
INSERT INTO pa_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,
project_assignment_id,
RESOURCE_ASSIGNMENT_TYPE
)
SELECT pa_resource_assignments_s.nextval,
l_dest_version_id,
sa.project_id,
sa.task_id,
sa.resource_list_member_id,
SYSDATE,
l_created_by,
SYSDATE,
l_created_by,
l_last_update_login,
sa.unit_of_measure,
sa.track_as_labor_flag,
-1,
sa.RESOURCE_ASSIGNMENT_TYPE
FROM
pa_resource_assignments sa,
pa_resource_assignments da
WHERE sa.budget_version_id = p_src_version_id
AND da.budget_version_id(+) = l_dest_version_id
AND sa.project_assignment_id=-1
AND da.project_assignment_id(+)=-1
AND sa.project_id=p_dest_project_id
AND da.project_id(+)=p_dest_project_id
AND sa.task_id=da.task_id(+)
AND sa.resource_list_member_id=da.resource_list_member_id(+)
AND da.resource_assignment_id IS NULL;
x_err_stage := 'About to insert budget lines to dest';
INSERT INTO pa_budget_lines
(budget_line_id,
budget_version_id,
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,
projfunc_currency_code,
project_currency_code,
txn_currency_code
)
select
pa_budget_lines_s.nextval,
l_dest_version_id,
dra.resource_assignment_id,
pbl.start_date,
sysdate,
l_created_by,
sysdate,
l_created_by,
l_last_update_login,
pbl.end_date,
pbl.period_name,
pbl.quantity,
round(pbl.raw_cost * p_amount_change_pct, p_rounding_precision),
round(pbl.burdened_cost * p_amount_change_pct, p_rounding_precision),
round(pbl.revenue * p_amount_change_pct, p_rounding_precision),
pbl.change_reason_code,
pbl.description,
pbl.attribute_category,
pbl.attribute1,
pbl.attribute2,
pbl.attribute3,
pbl.attribute4,
pbl.attribute5,
pbl.attribute6,
pbl.attribute7,
pbl.attribute8,
pbl.attribute9,
pbl.attribute10,
pbl.attribute11,
pbl.attribute12,
pbl.attribute13,
pbl.attribute14,
pbl.attribute15,
pbl.pm_product_code,
pbl.pm_budget_line_reference,
'B',
'B',
'B',
'B',
pbl.projfunc_currency_code,
pbl.project_currency_code,
pbl.txn_currency_code
from pa_budget_lines pbl,
pa_resource_assignments sra,
pa_resource_assignments dra
where dra.budget_version_id = l_dest_version_id
and sra.budget_version_id = p_src_version_id
and pbl.budget_version_id = p_src_version_id
and sra.resource_assignment_id=pbl.resource_assignment_id
and sra.resource_list_member_id=dra.resource_list_member_id
and sra.task_id=dra.task_id
and pbl.period_name not in (select s.period_name
from gl_period_statuses s
, pa_implementations_all i
where s.application_id = pa_period_process_pkg.application_id
and i.set_of_books_id = s.set_of_books_id
and s.closing_status in ('C','P'));
x_err_stage := 'About to delete unused resource assignments from dest';
delete from pa_resource_assignments
where budget_version_id = l_dest_version_id
and resource_assignment_id not in
(select distinct resource_assignment_id
from pa_budget_lines
where budget_version_id = l_dest_version_id);
end copy_without_delete;
x_last_update_login number;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select start_date
into g_project_start_date
from pa_projects_all a,pa_budget_versions b /*Modified for 6320792 */
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 pa_budget_versions
where budget_version_id = x_src_version_id;
select resource_list_id
into x_baselined_resource_list_id
from pa_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 || '>' ;
PA_BUDGET_UTILS.delete_draft(x_dest_version_id,
x_err_code,
x_err_stage,
x_err_stack);
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,
pa_budget_versions v
where v.budget_version_id = x_src_version_id
and v.budget_entry_method_code = m.budget_entry_method_code;
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 period_name = x_first_budget_period;
select pa_budget_versions_s.nextval
into x_dest_version_id
from sys.dual;
insert into pa_budget_versions(
budget_version_id,
project_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,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
)
select
x_dest_version_id,
x_dest_project_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,
decode(x_dest_budget_type_code, 'AC', 'Y', 'N'), --Bug 5081715.
decode(x_dest_budget_type_code, 'AR', 'Y', 'N') --Bug 5081715.
from pa_budget_versions v
where v.budget_version_id = x_src_version_id;
x_last_update_login NUMBER(15);
l_last_update_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
l_last_updated_by_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
l_last_update_login_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select 'Y'
into l_version_is_baselined
from pa_budget_versions
where budget_status_code = 'B'
and budget_version_id = x_dest_version_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,
pa_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 pa_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,
project_assignment_id,
RESOURCE_ASSIGNMENT_TYPE
)
SELECT pa_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,
-1,
sa.RESOURCE_ASSIGNMENT_TYPE
FROM
pa_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
pa_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
AND NVL(sa.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
INSERT INTO pa_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,
project_assignment_id,
RESOURCE_ASSIGNMENT_TYPE
)
SELECT pa_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,
-1,
sa.RESOURCE_ASSIGNMENT_TYPE
FROM
pa_resource_assignments sa,
pa_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
AND NVL(sa.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
select project_id
into l_project_id
from pa_budget_versions
where budget_version_id = x_src_version_id;
DELETE FROM pa_fp_cpy_periods_tmp;
SELECT p.start_date
INTO l_min_start_date
FROM pa_projects_all p /*Modified for bug 6320792 */
WHERE p.project_id = l_project_id;
SELECt min(t.start_date)
INTO l_min_start_date
FROM pa_tasks t
WHERE t.project_id = l_project_id;
SELECT min(bl.start_date)
INTO l_min_start_Date
FROM pa_budget_lines bl
WHERE bl.budget_version_id = x_src_version_id;
(select l.resource_assignment_id, l.start_date, l.end_date,a.task_id, l.period_name
from pa_budget_lines l,
pa_resource_assignments a
where a.budget_version_id = x_src_version_id
and a.resource_assignment_id = l.resource_assignment_id
and NVL(a.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED'
) 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 pa_fp_cpy_periods_tmp
(PA_PERIOD_NAME
,GL_PERIOD_NAME
,PERIOD_NAME
,START_DATE
,END_DATE)
VALUES
(decode(x_time_phased_type_code,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P,budget_line_row.period_name,'-99')
,decode(x_time_phased_type_code,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G,budget_line_row.period_name,'-99')
,x_period_name
,x_start_date
,x_end_date);
INSERT INTO pa_budget_lines
(budget_line_id, /* FPB2
budget_version_id, /* FPB2
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,
projfunc_currency_code,
project_currency_code,
txn_currency_code
)
select
bmt.target_budget_line_id, /* FPB2
x_dest_version_id, /* FPB2
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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
from pa_budget_lines l,
pa_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2
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
and l.budget_line_id = bmt.source_budget_line_id /* FPB2
;
insert into pa_budget_lines
(budget_line_id, /* FPB2
budget_version_id, /* FPB2
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,
projfunc_currency_code,
project_currency_code,
txn_currency_code
)
select
bmt.target_budget_line_id, /* FPB2
x_dest_version_id, /* FPB2
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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
from pa_budget_lines l,
pa_resource_assignments sa,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2
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
and l.budget_line_id = bmt.source_budget_line_id /* FPB2
;
select
bmt.target_budget_line_id, /* FPB2 */
x_dest_version_id, /* FPB2 */
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,
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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
bulk collect into
l_budget_line_id_tbl,
l_budget_version_id_tbl,
l_resource_assignment_id_tbl,
l_start_date_tbl,
l_last_update_date_tbl,
l_last_updated_by_tbl,
l_creation_date_tbl,
l_created_by_tbl,
l_last_update_login_tbl,
l_end_date_tbl,
l_period_name_tbl,
l_quantity_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_change_reason_code_tbl,
l_description_tbl,
l_attribute_category_tbl,
l_attribute1_tbl,
l_attribute2_tbl,
l_attribute3_tbl,
l_attribute4_tbl,
l_attribute5_tbl,
l_attribute6_tbl,
l_attribute7_tbl,
l_attribute8_tbl,
l_attribute9_tbl,
l_attribute10_tbl,
l_attribute11_tbl,
l_attribute12_tbl,
l_attribute13_tbl,
l_attribute14_tbl,
l_attribute15_tbl,
l_pm_product_code_tbl,
l_pm_budget_line_reference_tbl,
l_raw_cost_source_tbl,
l_burdened_cost_source_tbl,
l_quantity_source_tbl,
l_revenue_source_tbl,
l_projfunc_currency_code_tbl,
l_project_currency_code_tbl,
l_txn_currency_code_tbl
from pa_budget_lines l,
pa_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2 */
where l.budget_version_id = x_src_version_id
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
and l.budget_line_id = bmt.source_budget_line_id; /* FPB2 */
select
bmt.target_budget_line_id, /* FPB2 */
x_dest_version_id, /* FPB2 */
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,
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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
bulk collect into
l_budget_line_id_tbl,
l_budget_version_id_tbl,
l_resource_assignment_id_tbl,
l_start_date_tbl,
l_last_update_date_tbl,
l_last_updated_by_tbl,
l_creation_date_tbl,
l_created_by_tbl,
l_last_update_login_tbl,
l_end_date_tbl,
l_period_name_tbl,
l_quantity_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_change_reason_code_tbl,
l_description_tbl,
l_attribute_category_tbl,
l_attribute1_tbl,
l_attribute2_tbl,
l_attribute3_tbl,
l_attribute4_tbl,
l_attribute5_tbl,
l_attribute6_tbl,
l_attribute7_tbl,
l_attribute8_tbl,
l_attribute9_tbl,
l_attribute10_tbl,
l_attribute11_tbl,
l_attribute12_tbl,
l_attribute13_tbl,
l_attribute14_tbl,
l_attribute15_tbl,
l_pm_product_code_tbl,
l_pm_budget_line_reference_tbl,
l_raw_cost_source_tbl,
l_burdened_cost_source_tbl,
l_quantity_source_tbl,
l_revenue_source_tbl,
l_projfunc_currency_code_tbl,
l_project_currency_code_tbl,
l_txn_currency_code_tbl
from pa_budget_lines l,
pa_resource_assignments sa,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2 */
where l.budget_version_id = x_src_version_id
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
and l.budget_line_id = bmt.source_budget_line_id; /* FPB2 */
select
bmt.target_budget_line_id, /* FPB2 */
x_dest_version_id, /* FPB2 */
da.resource_assignment_id,
pptmp.start_date,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
pptmp.end_date,
pptmp.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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
bulk collect into
l_budget_line_id_tbl,
l_budget_version_id_tbl,
l_resource_assignment_id_tbl,
l_start_date_tbl,
l_last_update_date_tbl,
l_last_updated_by_tbl,
l_creation_date_tbl,
l_created_by_tbl,
l_last_update_login_tbl,
l_end_date_tbl,
l_period_name_tbl,
l_quantity_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_change_reason_code_tbl,
l_description_tbl,
l_attribute_category_tbl,
l_attribute1_tbl,
l_attribute2_tbl,
l_attribute3_tbl,
l_attribute4_tbl,
l_attribute5_tbl,
l_attribute6_tbl,
l_attribute7_tbl,
l_attribute8_tbl,
l_attribute9_tbl,
l_attribute10_tbl,
l_attribute11_tbl,
l_attribute12_tbl,
l_attribute13_tbl,
l_attribute14_tbl,
l_attribute15_tbl,
l_pm_product_code_tbl,
l_pm_budget_line_reference_tbl,
l_raw_cost_source_tbl,
l_burdened_cost_source_tbl,
l_quantity_source_tbl,
l_revenue_source_tbl,
l_projfunc_currency_code_tbl,
l_project_currency_code_tbl,
l_txn_currency_code_tbl
from pa_budget_lines l,
pa_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt, /* FPB2 */
PA_FP_CPY_PERIODS_TMP pptmp
where l.budget_version_id = x_src_version_id
and l.resource_assignment_id = sa.resource_assignment_id
and decode(x_time_phased_type_code,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P, pptmp.pa_period_name,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G, pptmp.gl_period_name) = l.period_name
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
and l.budget_line_id = bmt.source_budget_line_id; /* FPB2 */
select
bmt.target_budget_line_id, /* FPB2 */
x_dest_version_id, /* FPB2 */
da.resource_assignment_id,
pptmp.start_date,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
pptmp.end_date,
pptmp.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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
bulk collect into
l_budget_line_id_tbl,
l_budget_version_id_tbl,
l_resource_assignment_id_tbl,
l_start_date_tbl,
l_last_update_date_tbl,
l_last_updated_by_tbl,
l_creation_date_tbl,
l_created_by_tbl,
l_last_update_login_tbl,
l_end_date_tbl,
l_period_name_tbl,
l_quantity_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_change_reason_code_tbl,
l_description_tbl,
l_attribute_category_tbl,
l_attribute1_tbl,
l_attribute2_tbl,
l_attribute3_tbl,
l_attribute4_tbl,
l_attribute5_tbl,
l_attribute6_tbl,
l_attribute7_tbl,
l_attribute8_tbl,
l_attribute9_tbl,
l_attribute10_tbl,
l_attribute11_tbl,
l_attribute12_tbl,
l_attribute13_tbl,
l_attribute14_tbl,
l_attribute15_tbl,
l_pm_product_code_tbl,
l_pm_budget_line_reference_tbl,
l_raw_cost_source_tbl,
l_burdened_cost_source_tbl,
l_quantity_source_tbl,
l_revenue_source_tbl,
l_projfunc_currency_code_tbl,
l_project_currency_code_tbl,
l_txn_currency_code_tbl
from pa_budget_lines l,
pa_resource_assignments sa,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt, /* FPB2 */
PA_FP_CPY_PERIODS_TMP pptmp
where l.budget_version_id = x_src_version_id
and l.resource_assignment_id = sa.resource_assignment_id
and decode(x_time_phased_type_code,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P, pptmp.pa_period_name,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G, pptmp.gl_period_name) = l.period_name
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
and l.budget_line_id = bmt.source_budget_line_id; /* FPB2 */
select
bmt.target_budget_line_id, /* FPB2 */
x_dest_version_id, /* FPB2 */
da.resource_assignment_id,
(l.start_date + Nvl(x_shift_days, 0)),
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
(l.end_date + Nvl(x_shift_days, 0)),
l.period_name, -- would be null
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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
bulk collect into
l_budget_line_id_tbl,
l_budget_version_id_tbl,
l_resource_assignment_id_tbl,
l_start_date_tbl,
l_last_update_date_tbl,
l_last_updated_by_tbl,
l_creation_date_tbl,
l_created_by_tbl,
l_last_update_login_tbl,
l_end_date_tbl,
l_period_name_tbl,
l_quantity_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_change_reason_code_tbl,
l_description_tbl,
l_attribute_category_tbl,
l_attribute1_tbl,
l_attribute2_tbl,
l_attribute3_tbl,
l_attribute4_tbl,
l_attribute5_tbl,
l_attribute6_tbl,
l_attribute7_tbl,
l_attribute8_tbl,
l_attribute9_tbl,
l_attribute10_tbl,
l_attribute11_tbl,
l_attribute12_tbl,
l_attribute13_tbl,
l_attribute14_tbl,
l_attribute15_tbl,
l_pm_product_code_tbl,
l_pm_budget_line_reference_tbl,
l_raw_cost_source_tbl,
l_burdened_cost_source_tbl,
l_quantity_source_tbl,
l_revenue_source_tbl,
l_projfunc_currency_code_tbl,
l_project_currency_code_tbl,
l_txn_currency_code_tbl
from pa_budget_lines l,
pa_resource_assignments sa,
pa_tasks st,
pa_tasks dt,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2 */
where l.budget_version_id = x_src_version_id
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
and l.budget_line_id = bmt.source_budget_line_id; /* FPB2 */
select
bmt.target_budget_line_id, /* FPB2 */
x_dest_version_id, /* FPB2 */
da.resource_assignment_id,
(l.start_date + Nvl(x_shift_days, 0)),
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
(l.end_date + Nvl(x_shift_days, 0)),
l.period_name, -- would be null
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',
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
bulk collect into
l_budget_line_id_tbl,
l_budget_version_id_tbl,
l_resource_assignment_id_tbl,
l_start_date_tbl,
l_last_update_date_tbl,
l_last_updated_by_tbl,
l_creation_date_tbl,
l_created_by_tbl,
l_last_update_login_tbl,
l_end_date_tbl,
l_period_name_tbl,
l_quantity_tbl,
l_raw_cost_tbl,
l_burdened_cost_tbl,
l_revenue_tbl,
l_change_reason_code_tbl,
l_description_tbl,
l_attribute_category_tbl,
l_attribute1_tbl,
l_attribute2_tbl,
l_attribute3_tbl,
l_attribute4_tbl,
l_attribute5_tbl,
l_attribute6_tbl,
l_attribute7_tbl,
l_attribute8_tbl,
l_attribute9_tbl,
l_attribute10_tbl,
l_attribute11_tbl,
l_attribute12_tbl,
l_attribute13_tbl,
l_attribute14_tbl,
l_attribute15_tbl,
l_pm_product_code_tbl,
l_pm_budget_line_reference_tbl,
l_raw_cost_source_tbl,
l_burdened_cost_source_tbl,
l_quantity_source_tbl,
l_revenue_source_tbl,
l_projfunc_currency_code_tbl,
l_project_currency_code_tbl,
l_txn_currency_code_tbl
from pa_budget_lines l,
pa_resource_assignments sa,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2 */
where l.budget_version_id = x_src_version_id
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
and l.budget_line_id = bmt.source_budget_line_id; /* FPB2 */
INSERT INTO pa_budget_lines
(budget_line_id, /* FPB2 */
budget_version_id, /* FPB2 */
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,
projfunc_currency_code,
project_currency_code,
txn_currency_code
)
VALUES
(l_budget_line_id_tbl(i),
l_budget_version_id_tbl(i),
l_resource_assignment_id_tbl(i),
l_start_date_tbl(i),
l_last_update_date_tbl(i),
l_last_updated_by_tbl(i),
l_creation_date_tbl(i),
l_created_by_tbl(i),
l_last_update_login_tbl(i),
l_end_date_tbl(i),
l_period_name_tbl(i),
l_quantity_tbl(i),
l_raw_cost_tbl(i),
l_burdened_cost_tbl(i),
l_revenue_tbl(i),
l_change_reason_code_tbl(i),
l_description_tbl(i),
l_attribute_category_tbl(i),
l_attribute1_tbl(i),
l_attribute2_tbl(i),
l_attribute3_tbl(i),
l_attribute4_tbl(i),
l_attribute5_tbl(i),
l_attribute6_tbl(i),
l_attribute7_tbl(i),
l_attribute8_tbl(i),
l_attribute9_tbl(i),
l_attribute10_tbl(i),
l_attribute11_tbl(i),
l_attribute12_tbl(i),
l_attribute13_tbl(i),
l_attribute14_tbl(i),
l_attribute15_tbl(i),
l_pm_product_code_tbl(i),
l_pm_budget_line_reference_tbl(i),
l_raw_cost_source_tbl(i),
l_burdened_cost_source_tbl(i),
l_quantity_source_tbl(i),
l_revenue_source_tbl(i),
l_projfunc_currency_code_tbl(i),
l_project_currency_code_tbl(i),
l_txn_currency_code_tbl(i));
l_budget_line_id_tbl.DELETE;
l_budget_version_id_tbl.DELETE;
l_resource_assignment_id_tbl.DELETE;
l_start_date_tbl.DELETE;
l_last_update_date_tbl.DELETE;
l_last_updated_by_tbl.DELETE;
l_creation_date_tbl.DELETE;
l_created_by_tbl.DELETE;
l_last_update_login_tbl.DELETE;
l_end_date_tbl.DELETE;
l_period_name_tbl.DELETE;
l_quantity_tbl.DELETE;
l_raw_cost_tbl.DELETE;
l_burdened_cost_tbl.DELETE;
l_revenue_tbl.DELETE;
l_change_reason_code_tbl.DELETE;
l_description_tbl.DELETE;
l_attribute_category_tbl.DELETE;
l_attribute1_tbl.DELETE;
l_attribute2_tbl.DELETE;
l_attribute3_tbl.DELETE;
l_attribute4_tbl.DELETE;
l_attribute5_tbl.DELETE;
l_attribute6_tbl.DELETE;
l_attribute7_tbl.DELETE;
l_attribute8_tbl.DELETE;
l_attribute9_tbl.DELETE;
l_attribute10_tbl.DELETE;
l_attribute11_tbl.DELETE;
l_attribute12_tbl.DELETE;
l_attribute13_tbl.DELETE;
l_attribute14_tbl.DELETE;
l_attribute15_tbl.DELETE;
l_pm_product_code_tbl.DELETE;
l_pm_budget_line_reference_tbl.DELETE;
l_raw_cost_source_tbl.DELETE;
l_burdened_cost_source_tbl.DELETE;
l_quantity_source_tbl.DELETE;
l_revenue_source_tbl.DELETE;
l_projfunc_currency_code_tbl.DELETE;
l_project_currency_code_tbl.DELETE;
l_txn_currency_code_tbl.DELETE;