[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
current_working_flag,
budget_status_code,
plan_processing_code,
locked_by_person_id
into
l_current_working_flag,
l_budget_status_code,
l_plan_processing_code,
l_locked_by_person_id
from
pa_budget_versions
where
budget_version_id = p_budget_version_id;
select pt.fin_plan_type_code
into l_fin_plan_type_code
from pa_proj_fp_options po,
pa_fin_plan_types_b pt /* Bug# 2661650 - _vl to _b/_tl for performance changes */
where po.project_id = p_project_id and
po.fin_plan_version_id = p_budget_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_type_id = pt.fin_plan_type_id;
pa_debug.write_file('Submit_Current_Working: ' || 'selected budget version is not a current working version');
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
update
pa_budget_versions
set
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
budget_status_code = 'S',
record_version_number=record_version_number+1 /* increment record_version_number */
where
budget_version_id=p_budget_version_id;
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid1_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
/* check to see if the old current working budget version has been updated */
/* by someone else already */
/* BUT, need to check if there was an old current working version */
if p_orig_budget_version_id is not null then
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_orig_budget_version_id,
p_record_version_number => p_orig_record_version_number,
x_valid_flag => l_valid2_flag,
x_return_status => x_return_status,
x_error_msg_code => l_error_msg_code);
select
budget_status_code
,fin_plan_type_id
,version_type
into
l_budget_status_code
,l_fin_plan_type_id
,l_version_type
from
pa_budget_versions
where
budget_version_id=p_orig_budget_version_id;
Select budget_version_id
into l_cur_work_bv_id
from pa_budget_versions bv
where bv.project_id = p_project_id
and bv.fin_plan_type_id = l_fin_plan_type_id
and bv.version_type = l_version_type
and bv.current_working_flag = 'Y'
and bv.ci_id IS NULL
and ((DECODE(bv.version_type,'COST',bv.approved_cost_plan_type_flag,
'REVENUE',bv.approved_rev_plan_type_flag,
'N') = 'Y')
OR
(bv.approved_cost_plan_type_flag = 'Y' and
bv.approved_rev_plan_type_flag = 'Y')) ;
select 'Y'
into l_exists
from dual
where exists (select 'x'
from pa_budget_versions bv
,pa_control_items ci
,pa_ci_impacts cp
--For bug 3550073
,pa_ci_statuses_v pcs
,pa_pt_co_impl_statuses pcis
where bv.project_id = p_project_id
and bv.fin_plan_type_id = l_fin_plan_type_id
and bv.version_type = l_version_type
and ci.ci_id = bv.ci_id
and pcs.ci_type_id = ci.ci_type_id
and pcs.project_status_code = ci.status_code
and pcs.project_system_status_code IN ('CI_APPROVED','CI_SUBMITTED')
and cp.ci_id = ci.ci_id
and cp.impact_type_code <> 'FINPLAN'
and cp.impact_type_code = DECODE(bv.version_type,
'COST','FINPLAN_COST',
'REVENUE','FINPLAN_REVENUE',
cp.impact_type_code)
and cp.status_code = 'CI_IMPACT_PENDING'
and pcis.fin_plan_type_id = bv.fin_plan_type_id
and pcis.ci_type_id = ci.ci_type_id
and pcis.version_type = bv.version_type
and pcis.status_code = pcs.project_status_code);
select 'Y'
into l_exists
from dual
where exists
(select 'x'
from pa_fp_eligible_ci_v eligible
where eligible.project_id = p_project_id
and eligible.fin_plan_type_id = l_fin_plan_type_id
and eligible.ci_version_type = l_version_type
and eligible.project_system_status_code IN ('CI_APPROVED','CI_SUBMITTED')
-- filter cis that are already part of target cur working version
and eligible.ci_id not in (select merged.ci_id
from pa_fp_merged_ctrl_items merged
where merged.plan_version_id = p_budget_version_id
and merged.project_id = p_project_id
)
-- filter cis included in current baseline version
and eligible.ci_id not in (select merged.ci_id
from pa_fp_merged_ctrl_items merged,
pa_budget_versions cur_baseline
where cur_baseline.project_id = p_project_id
and cur_baseline.fin_plan_type_id = l_fin_plan_type_id
and cur_baseline.version_type = l_version_type
and cur_baseline.budget_status_code = 'B'
and cur_baseline.current_flag = 'Y'
and merged.plan_version_id = cur_baseline.budget_version_id
and merged.project_id = cur_baseline.project_id));
update
pa_budget_versions
set
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
current_working_flag='N',
record_version_number=record_version_number+1 /* increment record_version_number */
where
budget_version_id=p_orig_budget_version_id;
update
pa_budget_versions
set
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
current_working_flag='Y',
record_version_number=record_version_number+1 /* increment record_version_number */
where
budget_version_id=p_budget_version_id;
select
budget_status_code,
current_working_flag
into
l_budget_status_code,
l_current_working_flag
from
pa_budget_versions
where
budget_version_id = p_budget_version_id;
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
update
pa_budget_versions
set
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
budget_status_code = 'W',
record_version_number=record_version_number+1 /* increment record_version_number */
where
budget_version_id=p_budget_version_id;
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid1_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
/* check to see if the old original baselined budget version has been updated */
/* by someone else already */
/* Bug # 2639285 - Included the check for p_orig_budget_version_id is not null*/
IF p_orig_budget_version_id IS NOT NULL THEN
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_orig_budget_version_id,
p_record_version_number => p_orig_record_version_number,
x_valid_flag => l_valid2_flag,
x_return_status => x_return_status,
x_error_msg_code => l_error_msg_code);
/* Bug # 2639285 - Included the update in case p_orig_budget_version_id is null */
IF p_orig_budget_version_id is null THEN
update pa_budget_versions a
set original_flag = 'Y',
current_original_flag = 'N',
last_update_date = SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
record_version_number = record_version_number + 1
where (a.project_id,a.fin_plan_type_id,a.version_type) =
(select b.project_id,b.fin_plan_type_id,b.version_type
from pa_budget_versions b
where b.budget_version_id = p_budget_version_id)
and a.budget_version_id <> p_budget_version_id
and current_original_flag = 'Y';
update
pa_budget_versions
set
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
original_flag = 'Y',
current_original_flag='N',
record_version_number = record_version_number + 1 /* increment record_version_number */
where
budget_version_id=p_orig_budget_version_id;
update
pa_budget_versions
set
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
original_flag = 'Y',
current_original_flag='Y',
record_version_number = record_version_number + 1 /* increment record_version_number */
where
budget_version_id=p_budget_version_id;
procedure Delete_Version
(p_project_id IN pa_budget_versions.project_id%TYPE,
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
p_context IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
as
l_debug_mode VARCHAR2(30);
l_module_name VARCHAR2(100):='PAFPPUBB.delete_version';
select unique
resource_assignment_id
from
pa_resource_assignments
where
budget_version_id=p_budget_version_id;
pa_debug.init_err_stack('PA_FIN_PLAN_PUB.Delete_Version');
pa_debug.set_process('Delete_Version: ' || 'PLSQL','LOG',l_debug_mode);
pa_debug.write('PA_FIN_PLAN_PUB.Delete_Version: ' || l_module_name,pa_debug.g_err_stage,5);
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
/* we can delete only working versions (cannot delete submitted, baselined) */
select
budget_status_code,
current_working_flag,
fin_plan_type_id,
version_type,
current_original_flag, -- Bug 3354518 FP M
current_flag -- Bug 3354518 FP M
into
l_budget_status_code,
l_current_working_flag,
l_fin_plan_type_id,
l_version_type,
l_current_original_flag, -- Bug 3354518 FP M
l_current_flag -- Bug 3354518 FP M
from
pa_budget_versions
where
budget_version_id = p_budget_version_id;
pa_debug.write_file('Delete_Version: ' || 'budget status code is S');
p_msg_name => 'PA_FP_DELETE_WORKING');
select p.baseline_funding_flag, v.approved_rev_plan_type_flag
into l_baseline_funding_flag, l_approved_rev_plan_type_flag
from pa_projects_all p, pa_budget_versions v
where p.project_id = v.project_id
and v.budget_version_id = p_budget_version_id;
cannot be deleted. */
BEGIN
Select budget_version_id
into l_cur_work_bv_id
from pa_budget_versions bv
where bv.project_id = p_project_id
and bv.fin_plan_type_id = l_fin_plan_type_id
and bv.version_type = l_version_type
and bv.current_working_flag = 'Y'
and bv.ci_id IS NULL
and ((DECODE(bv.version_type,'COST',bv.approved_cost_plan_type_flag,
'REVENUE',bv.approved_rev_plan_type_flag,
'N') = 'Y')
OR
(bv.approved_cost_plan_type_flag = 'Y' and
bv.approved_rev_plan_type_flag = 'Y')) ;
/* The version to be deleted is also the current working approved budget plan
version. Check to see if any financial impact of a change order exists for
this project plan type combination and if so then return an error and do
not delete the current working version (p_budget_version_id) */
BEGIN
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS ( SELECT 'X' from pa_budget_versions pb,pa_control_items pci -- added pa_control_items pci for bug 3741051
WHERE pb.project_id = p_project_id --added the alias name for bug 3741051
AND pb.fin_plan_type_id = l_fin_plan_type_id -- added the alias name for bug 3741051
AND pb.version_type = l_version_type -- added the alias name for bug 3741051
AND pb.project_id = pci.project_id -- added for bug 3741051
AND pb.ci_id = pci.ci_id -- added for bug 3741051
AND pci.status_code <> 'CI_CANCELED'-- added for bug 3741051
);
p_msg_name => 'PA_FP_BV_CI_NO_DELETE');
cannot be deleted. */
END IF;
/* If There are NO Business Rules Violations , Then proceed with Delete Version */
if l_msg_count = 0 then
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('Delete_Version: ' || 'no business errors: continuing with Delete Version');
SAVEPOINT PA_FIN_PLAN_PUB_DELETE;
SELECT fin_plan_type_id
INTO l_wp_fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE use_for_workplan_flag='Y';
/* call Delete_Version_Helper to delete everything but the entry in PA_BUDGET_VERSIONS and PA_PROJ_FP_OPTIONS */
pa_fin_plan_pub.Delete_Version_Helper
(p_project_id => p_project_id,
p_context => p_context,
p_budget_version_id => p_budget_version_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* PA_PROJ_FIN_PLAN_OPTIONS: delete row (keyed on fin_plan_version_id) */
-- Made changes for sql id
IF p_budget_version_id IS NOT NULL THEN
delete
from
pa_proj_fp_options
where
fin_plan_version_id= p_budget_version_id AND
project_id=nvl(p_project_id,project_id) AND
fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id);
delete
from
pa_proj_fp_options
where
fin_plan_version_id=nvl(p_budget_version_id,fin_plan_version_id) AND
project_id=p_project_id AND
(fin_plan_type_id IS NULL OR
fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id));
/* PA_BUDGET_VERSIONS delete row */
/* Bug 4873352 - Split this delete based on i/p parameter null condition
* to avoid FTS - Sql id : 14903057 */
if p_budget_version_id is not null then
delete
from
pa_budget_versions
where
budget_version_id=p_budget_version_id AND
project_id=nvl(p_project_id,project_id) AND
fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id)
returning budget_version_id
bulk collect into l_budget_version_id_tbl ;
delete
from
pa_budget_versions
where
budget_version_id=nvl(p_budget_version_id,budget_version_id) AND
project_id=p_project_id AND
fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id)
returning budget_version_id
bulk collect into l_budget_version_id_tbl ;
delete
from
pa_budget_versions
where
budget_version_id=nvl(p_budget_version_id,budget_version_id) AND
project_id=nvl(p_project_id,project_id) AND
fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id)
returning budget_version_id
bulk collect into l_budget_version_id_tbl ;
/* if the deleted version was the current working version, need to find a replacement */
if l_current_working_flag='Y' then
/* find next most recent version */
select
nvl(max(version_number), 0)
into
l_max_version
from
pa_budget_versions
where
project_id = p_project_id and
fin_plan_type_id = l_fin_plan_type_id and
budget_status_code = 'W' and
version_type = l_version_type and
/* BUG FIX 2638356: do not accidentally select Control Items */
ci_id is null;
update
pa_budget_versions
set
current_working_flag = 'Y',
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id,
record_version_number=record_version_number+1
where
project_id = p_project_id and
fin_plan_type_id = l_fin_plan_type_id and
budget_status_code = 'W' and
version_type = l_version_type and
version_number = l_max_version;
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PA_BUDGET_VERSIONS',
X_pk1_value => to_char(p_budget_version_id),
X_delete_document_flag => 'Y');
pa_debug.write(l_module_name,'Calling PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE ' ,5);
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_budget_version_id_tbl,
x_return_status => l_return_status,
x_msg_code => l_error_msg_code);
pa_debug.write_file('Procedure Delete_Version: rollback_on_error exception');
rollback to PA_FIN_PLAN_PUB_DELETE;
rollback to PA_FIN_PLAN_PUB_DELETE;
p_procedure_name => 'Delete_Version');
end Delete_Version;
procedure Delete_Version_Helper
(p_project_id IN pa_projects_all.project_id%TYPE ,
p_context IN VARCHAR2 ,
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
is
l_debug_mode VARCHAR2(30);
l_module_name VARCHAR2(100):='PAFPPUBB.Delete_Version_Helper';
select unique
resource_assignment_id
from
pa_resource_assignments
where
budget_version_id=p_budget_version_id;
pa_debug.set_err_stack('PA_FIN_PLAN_PUB.Delete_Version_Helper');
pa_debug.set_process('Delete_Version: ' || 'PLSQL','LOG',l_debug_mode);
/* If There are NO Business Rules Violations , Then proceed with Delete Version Helper*/
if l_msg_count = 0 then
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('Delete_Version: ' || 'no business errors: continuing with Delete Version Helper');
SAVEPOINT PA_FIN_PLAN_PUB_DELETE_H;
SELECT fin_plan_type_id
INTO l_wp_fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE use_for_workplan_flag='Y';
SELECT proj_fp_options_id,
fin_plan_version_id
BULK COLLECT INTO
l_proj_fp_options_id_tbl,
l_budget_Version_id_tbl
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_budget_version_id
AND fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id);
SELECT proj_fp_options_id,
fin_plan_version_id
BULK COLLECT INTO
l_proj_fp_options_id_tbl,
l_budget_Version_id_tbl
FROM pa_proj_fp_options
WHERE project_id=p_project_id
AND (fin_plan_type_id = nvl(l_wp_fin_plan_type_id,fin_plan_type_id) OR
fin_plan_type_id IS NULL);
/* PA_FIN_PLAN_ADJ_LINES: delete row (keyed on budget_version_id) */
delete
from
pa_fin_plan_adj_lines
where
budget_version_id=l_budget_Version_id_tbl(i);
/* PA_FP_ADJ_ELEMENTS: delete row (keyed on budget_version_id) */
delete
from
pa_fp_adj_elements
where
budget_version_id=l_budget_Version_id_tbl(i);
/* PA_ORG_FORECAST_LINES: delete row (keyed on budget_version_id as of 2/20/2002) */
delete
from
pa_org_forecast_lines
where
budget_version_id = l_budget_Version_id_tbl(i);
/* PA_ORG_FORECAST_ELEMENTS: delete row (keyed on budget_version_id) */
delete
from
pa_org_fcst_elements
where
budget_version_id=l_budget_Version_id_tbl(i);
delete
from
pa_budget_lines
where
resource_assignment_id=l_resource_assignments_rec.resource_assignment_id;
delete
from
pa_budget_lines
where
budget_version_id=l_budget_Version_id_tbl(i);
/* PA_RESOURCE_ASSIGNMENTS: delete row (keyed on budget_version_id) */
delete
from
pa_resource_assignments
where
budget_version_id=l_budget_Version_id_tbl(i);
delete
from
pa_mc_budget_lines
where
budget_version_id = l_budget_Version_id_tbl(i);
delete
from
pa_fp_txn_currencies
where
proj_fp_options_id = l_proj_fp_options_id_tbl(i); -- bug 2779637
p_calling_module => 'UPDATE_PLAN_TRANSACTION',
p_delete_flag => 'Y',
p_version_level_flag => 'Y',
x_return_status => l_return_status,
x_msg_data => l_msg_count,
x_msg_count => l_msg_data);
SELECT project_id,
ci_id
INTO l_project_id,
l_ci_id
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
pa_debug.write('Delete_Version: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
DELETE FROM pa_fp_merged_ctrl_items
WHERE project_id = l_project_id
AND plan_version_id = l_budget_Version_id_tbl(i);
DELETE FROM pa_fp_merged_ctrl_items
WHERE project_id = l_project_id
AND ci_plan_version_id = l_budget_Version_id_tbl(i);
UPDATE pa_proj_fp_options
SET gen_src_cost_plan_version_id = DECODE(gen_src_cost_plan_version_id
,p_budget_version_id,NULL,gen_src_cost_plan_version_id)
,gen_src_rev_plan_version_id = DECODE(gen_src_rev_plan_version_id
,p_budget_version_id,NULL,gen_src_rev_plan_version_id)
,gen_src_all_plan_version_id = DECODE(gen_src_all_plan_version_id
,p_budget_version_id,NULL,gen_src_all_plan_version_id)
,gen_src_cost_wp_version_id = DECODE(gen_src_cost_wp_version_id
,p_budget_version_id,NULL,gen_src_cost_wp_version_id)
,gen_src_rev_wp_version_id = DECODE(gen_src_rev_wp_version_id
,p_budget_version_id,NULL,gen_src_rev_wp_version_id)
,gen_src_all_wp_version_id = DECODE(gen_src_all_wp_version_id
,p_budget_version_id,NULL,gen_src_all_wp_version_id)
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE project_id = l_project_id
AND fin_plan_option_level_code = 'PLAN_VERSION'
AND (gen_src_cost_plan_version_id = p_budget_version_id OR
gen_src_rev_plan_version_id = p_budget_version_id OR
gen_src_all_plan_version_id = p_budget_version_id OR
gen_src_cost_wp_version_id = p_budget_version_id OR
gen_src_rev_wp_version_id = p_budget_version_id OR
gen_src_all_wp_version_id = p_budget_version_id );
pa_debug.write_file('Procedure Delete_Version_Helper: rollback_on_error exception');
rollback to PA_FIN_PLAN_PUB_DELETE_H;
rollback to PA_FIN_PLAN_PUB_DELETE_H;
p_procedure_name => 'Delete_Version_Helper');
end Delete_Version_Helper;
Select 'Y' from dual
where EXISTS(
select 1
from pa_budget_versions pbv
where pbv.budget_version_id = p_source_version_id and pbv.prc_generated_flag='M')
and NOT EXISTS(
select 1
from PA_FP_UPGRADE_AUDIT pua
where pua.project_id = c_project_id
and pua.proj_fp_options_id_rup = c_source_proj_fp_options_id
and pua.upgraded_flag = 'Y');
Select 'Y' from dual
where exists (select 1 from pa_budget_versions pbv where pbv.budget_version_id = px_target_version_id and pbv.budget_Status_code = 'W' )
and exists (select 1 from pa_budget_lines bl, pa_budget_versions pbv
where pbv.budget_version_id = px_target_version_id
and pbv.budget_version_id = bl.budget_version_id
and (bl.cost_rejection_code IS NOT NULL
OR bl.revenue_rejection_code IS NOT NULL
OR bl.burden_rejection_code IS NOT NULL
OR bl.pfc_cur_conv_rejection_code IS NOT NULL
OR bl.pc_cur_conv_rejection_code IS NOT NULL)
);
SELECT pfo.proj_fp_options_id
,pfo.fin_plan_preference_code
,pfo.project_id
,pfo.fin_plan_type_id
,DECODE(pfo.fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY , PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL),
pfo.plan_in_multi_curr_flag,
nvl(pfo.approved_rev_plan_type_flag,'N'),
pfo.rbs_version_id,
fin.plan_class_code
INTO l_source_proj_fp_options_id
,l_source_fp_preference_code
,l_project_id
,l_source_fin_plan_type_id
,l_source_element_type
,l_source_plan_in_mc_flag
,l_source_appr_rev_plan_flag
,l_source_ver_rbs_version_id -- Bug 3731925
,l_source_plan_class_code
FROM pa_proj_fp_options pfo,
pa_fin_plan_types_b fin
WHERE pfo.fin_plan_version_id = p_source_version_id
AND fin.fin_plan_type_id=pfo.fin_plan_type_id;
SELECT pfo.proj_fp_options_id
,pfo.fin_plan_preference_code
,pfo.fin_plan_type_id
,DECODE(pfo.fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY , PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL),
nvl(pfo.approved_rev_plan_type_flag,'N'),
fin.plan_class_code
INTO l_target_proj_fp_options_id
,l_target_fp_preference_code
,l_target_fin_plan_type_id
,l_target_element_type
,l_target_appr_rev_plan_flag
,l_target_plan_class_code
FROM pa_proj_fp_options pfo,
pa_fin_plan_types_b fin
WHERE pfo.fin_plan_version_id = l_budget_version_id
AND pfo.fin_plan_type_id = fin.fin_plan_type_id;
SELECT pfo.plan_in_multi_curr_flag
INTO l_plan_in_multi_curr_flag
FROM pa_proj_fp_options pfo
WHERE pfo.fin_plan_version_id = l_budget_version_id;
SELECT period_profile_id
INTO l_source_profile_id
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_source_version_id;
SELECT period_profile_id
INTO l_target_profile_id
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = l_budget_version_id;
below update and call to copy_period_denorm and call call_maintain_plan_matrix
instead. We also need to change call_maintain_plan_matrix and create_org_fcst_elements
to fix this issue */
--Update the new budget version/target's period profile id as that of the source version
UPDATE pa_budget_versions
SET period_profile_id = l_source_profile_id
WHERE budget_version_id = l_budget_version_id;
Select rbs_version_id
into l_target_pt_lvl_rbs_version_id
from pa_proj_fp_options opt
where opt.project_id = p_project_id
and opt.fin_plan_type_id = l_target_fin_plan_type_id
and opt.fin_plan_option_level_code = 'PLAN_TYPE';
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_dest_ver_id_tbl,
x_return_status => l_return_status,
x_msg_code => l_error_msg_code);
pa_debug.g_err_stage := 'API PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE returned ERROR 1 '|| l_error_msg_code;
SELECT decode(budget_status_code,'S','W',budget_status_code)
INTO l_source_ver_type_tbl(1)
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_source_version_id;
SELECT budget_status_code
INTO l_dest_ver_type_tbl(1) /* This should always be W since we are inside "IF p_copy_mode = W" */
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = l_budget_version_id;
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid1_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
/* been updated by someone else already */
/* if p_orig_budget_version_id = null then there is currently not a baselined version */
/* in this case, ignore this check
if p_orig_budget_version_id <> null then */
if p_orig_budget_version_id is not null then
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_orig_budget_version_id,
p_record_version_number => p_orig_record_version_number,
x_valid_flag => l_valid2_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
select pt.fin_plan_type_code,
pbv.version_type,
pbv.fin_plan_type_id
into l_fin_plan_type_code,
l_version_type,
l_fin_plan_type_id
from pa_budget_versions pbv,
pa_fin_plan_types_b pt /* Bug# 2661650 - _vl to _b/_tl for performance changes */
where pbv.budget_version_id = p_budget_version_id and
pbv.fin_plan_type_id = pt.fin_plan_type_id ;
update
pa_budget_versions
set
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
budget_status_code = 'W',
record_version_number = record_version_number+1
where
budget_version_id = p_budget_version_id;
update
pa_budget_versions
set
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
current_flag = 'N',
record_version_number = record_version_number+1
where
budget_version_id=p_orig_budget_version_id;
SELECT budget_version_id,
record_version_number
INTO l_orig_budget_version_id,
l_orig_record_version_number
FROM pa_budget_versions
WHERE project_id=p_project_id
AND fin_plan_type_id=l_fin_plan_type_id
AND version_type=l_version_type
AND current_flag='Y';
SELECT COUNT(*)
INTO l_temp
FROM pa_budget_versions
WHERE project_id=p_project_id
AND fin_plan_type_id=l_fin_plan_type_id
AND version_type=l_version_type
AND current_flag='Y';
select
fin_plan_amount_set_id
from
pa_fin_plan_amount_sets
where
amount_set_type_code = 'ALL';
select
proj_fp_options_id
from
pa_proj_fp_options
where
project_id=p_project_id and
fin_plan_type_id=p_fin_plan_type_id and
fin_plan_option_level_code = 'PLAN_TYPE';
select
fin_plan_type_code,
name
into
l_version_type_code,
l_version_type
from
pa_fin_plan_types_vl
where
fin_plan_type_id=p_fin_plan_type_id;
pa_debug.write_file('Create_Version_OrgFcst: ' || 'version type selected with no problems');
select
nvl(max(version_number), 0)
into
l_max_version
from
pa_budget_versions
where
project_id = p_project_id and
fin_plan_type_id = p_fin_plan_type_id and
budget_status_code in ('W', 'S');
pa_debug.write_file('Create_Version_OrgFcst: ' || 'calling pa_fp_budget_versions_pkg.Insert_Row to create a budget version');
select pa_budget_versions_s.nextVal into l_budget_version_id from dual;
pa_fp_budget_versions_pkg.Insert_Row
(px_budget_version_id => l_budget_version_id, /* unique budget_version_id for new version */
p_project_id => p_project_id, /* the ID of the project */
p_budget_type_code => NULL,
p_version_number => l_max_version+1, /* version_number incremented */
p_budget_status_code => 'W', /* 'Working' version */
p_current_flag => 'N', /* 'Working' version */
p_original_flag => 'N', /* 'Working' version */
p_current_original_flag => 'N', /* 'Working' version */
p_resource_accumulated_flag => 'N', /* HARDCODED VALUE */
p_resource_list_id => l_resource_list_id,
p_version_name => p_version_name, /* user-entered value */
p_budget_entry_method_code => NULL,
p_baselined_by_person_id => NULL,
p_baselined_date => NULL,
p_change_reason_code => NULL,
p_labor_quantity => NULL,
p_labor_unit_of_measure => NULL,
p_raw_cost => NULL,
p_burdened_cost => NULL,
p_revenue => NULL,
p_description => p_description, /* user-entered value */
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_first_budget_period => NULL,
p_pm_product_code => NULL,
p_pm_budget_reference => NULL,
p_wf_status_code => NULL,
p_adw_notify_flag => NULL,
p_prc_generated_flag => NULL,
p_plan_run_date => NULL,
p_plan_processing_code => NULL, /* plan_processing_code = null, since we're not running the generate concurrent process*/
p_period_profile_id => l_period_profile_id, /* use newly-generated period_profile_id */
p_fin_plan_type_id => p_fin_plan_type_id,
p_parent_plan_version_id => NULL,
p_project_structure_version_id => NULL,
p_current_working_flag => l_current_working_flag,
p_total_borrowed_revenue => NULL,
p_total_tp_revenue_in => NULL,
p_total_tp_revenue_out => NULL,
p_total_revenue_adj => NULL,
p_total_lent_resource_cost => NULL,
p_total_tp_cost_in => NULL,
p_total_tp_cost_out => NULL,
p_total_cost_adj => NULL,
p_total_unassigned_time_cost => NULL,
p_total_utilization_percent => NULL,
p_total_utilization_hours => NULL,
p_total_utilization_adj => NULL,
p_total_capacity => NULL,
p_total_head_count => NULL,
p_total_head_count_adj => NULL,
p_version_type => l_version_type_code,
p_request_id => NULL, /* will be changed later in the script */
x_row_id => l_row_id,
x_return_status => l_return_status);
select pa_proj_fp_options_s.nextVal into l_proj_fin_plan_options_id from dual;
pa_proj_fp_options_pkg.Insert_Row
(px_proj_fp_options_id => l_proj_fin_plan_options_id,
p_project_id => p_project_id,
p_fin_plan_option_level_code => 'PLAN_TYPE',
p_fin_plan_type_id => p_fin_plan_type_id,
p_fin_plan_start_date => NULL, /* for PLAN_TYPE level */
p_fin_plan_end_date => NULL, /* for PLAN_TYPE level */
p_fin_plan_preference_code => 'COST_AND_REV_SAME', /* for org_forecast */
p_cost_amount_set_id => NULL, /* for org_forecast */
p_revenue_amount_set_id => NULL, /* for org_forecast */
p_all_amount_set_id => l_org_amount_set_id, /* for org_forecast */
p_cost_fin_plan_level_code => NULL, /* for org_forecast */
p_cost_time_phased_code => NULL, /* for org_forecast */
p_cost_resource_list_id => NULL, /* for org_forecast */
p_revenue_fin_plan_level_code => NULL, /* for org_forecast */
p_revenue_time_phased_code => NULL, /* for org_forecast */
p_revenue_resource_list_id => NULL, /* for org_forecast */
p_all_fin_plan_level_code => 'L', /* for org_forecast */
p_all_time_phased_code => l_org_time_phased_code, /* for org_forecast */
p_all_resource_list_id => l_resource_list_id,
p_report_labor_hrs_from_code => 'COST', /* for org_forecast */
p_fin_plan_version_id => NULL, /* use l_budget_version_id only at the VERSION_TYPE level */
x_row_id => l_row_id,
x_return_status => l_return_status);
pa_debug.write_file('Create_Version_OrgFcst: ' || 'error with pa_fp_proj_fplan_options_pkg.Insert_Row: plan_type level');
select pa_proj_fp_options_s.nextVal into l_proj_fin_plan_options_id from dual;
pa_proj_fp_options_pkg.Insert_Row
(px_proj_fp_options_id => l_proj_fin_plan_options_id,
p_project_id => p_project_id,
p_fin_plan_option_level_code => 'PLAN_VERSION',
p_fin_plan_type_id => p_fin_plan_type_id,
p_fin_plan_start_date => l_fcst_start_date,
p_fin_plan_end_date => l_fcst_end_date,
p_fin_plan_preference_code => 'COST_AND_REV_SAME', /* for org_forecast */
p_cost_amount_set_id => NULL, /* for org_forecast */
p_revenue_amount_set_id => NULL, /* for org_forecast */
p_all_amount_set_id => l_org_amount_set_id, /* for org_forecast */
p_cost_fin_plan_level_code => NULL, /* for org_forecast */
p_cost_time_phased_code => NULL, /* for org_forecast */
p_cost_resource_list_id => NULL, /* for org_forecast */
p_revenue_fin_plan_level_code => NULL, /* for org_forecast */
p_revenue_time_phased_code => NULL, /* for org_forecast */
p_revenue_resource_list_id => NULL, /* for org_forecast */
p_all_fin_plan_level_code => 'L', /* for org_forecast */
p_all_time_phased_code => l_org_time_phased_code, /* for org_forecast */
p_all_resource_list_id => l_resource_list_id,
p_report_labor_hrs_from_code => 'COST', /* for org_forecast */
p_fin_plan_version_id => l_budget_version_id,
x_row_id => l_row_id,
x_return_status => l_return_status);
pa_debug.write_file('Create_Version_OrgFcst: ' || 'error with pa_fp_proj_fplan_options_pkg.Insert_Row: plan_version level');
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
select
budget_status_code
into
l_budget_status_code
from
pa_budget_versions
where
budget_version_id=p_budget_version_id;
SELECT pp.org_id
,bv.budget_status_code
INTO l_org_id
,l_budget_status_code
FROM pa_projects_all pp
,pa_budget_versions bv
WHERE pp.project_id = bv.project_id
AND bv.budget_version_id = p_budget_version_id;
update
pa_budget_versions
set
request_id = l_request_id,
plan_processing_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P, /* "Generation in process" */
record_version_number = record_version_number + 1
where
budget_version_id = p_budget_version_id;
procedure Update_Version
(p_project_id IN pa_budget_versions.project_id%TYPE
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_record_version_number IN pa_budget_versions.record_version_number%TYPE
,p_version_name IN pa_budget_versions.version_name%TYPE
,p_description IN pa_budget_versions.description%TYPE
,p_change_reason_code IN pa_budget_versions.change_reason_code%TYPE
-- Start of additional columns for Bug :- 3088010
,p_attribute_category IN pa_budget_versions.attribute_category%TYPE
,p_attribute1 IN pa_budget_versions.attribute1%TYPE
,p_attribute2 IN pa_budget_versions.attribute2%TYPE
,p_attribute3 IN pa_budget_versions.attribute3%TYPE
,p_attribute4 IN pa_budget_versions.attribute4%TYPE
,p_attribute5 IN pa_budget_versions.attribute5%TYPE
,p_attribute6 IN pa_budget_versions.attribute6%TYPE
,p_attribute7 IN pa_budget_versions.attribute7%TYPE
,p_attribute8 IN pa_budget_versions.attribute8%TYPE
,p_attribute9 IN pa_budget_versions.attribute9%TYPE
,p_attribute10 IN pa_budget_versions.attribute10%TYPE
,p_attribute11 IN pa_budget_versions.attribute11%TYPE
,p_attribute12 IN pa_budget_versions.attribute12%TYPE
,p_attribute13 IN pa_budget_versions.attribute13%TYPE
,p_attribute14 IN pa_budget_versions.attribute14%TYPE
,p_attribute15 IN pa_budget_versions.attribute15%TYPE
-- End of additional columns for Bug :- 3088010
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
is
l_debug_mode VARCHAR2(30);
pa_debug.init_err_stack('PA_FIN_PLAN_PUB.Update_Version');
pa_debug.set_process('Update_Version: ' || 'PLSQL','LOG',l_debug_mode);
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
/* If There are NO Business Rules Violations , Then proceed with Update Version */
if l_msg_count = 0 then
SAVEPOINT PA_FIN_PLAN_PUB_UPDATE_VERSION;
UPDATE
PA_BUDGET_VERSIONS
SET
record_version_number = l_record_version_number,
version_name = p_version_name,
description = p_description,
change_reason_code = p_change_reason_code,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
/* Code addition for bug 3088010 starts */
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15
/* Code addition for bug 3088010 ends */
WHERE
budget_version_id = p_budget_version_id;
rollback to PA_FIN_PLAN_PUB_UPDATE_VERSION;
p_procedure_name => 'Update_Version');
end Update_Version;
select
resource_assignment_id
from
pa_resource_assignments
where
budget_version_id=p_source_version_id;
select
forecast_element_id
from
pa_org_fcst_elements
where
budget_version_id=p_source_version_id;
select
forecast_line_id
from
pa_org_forecast_lines
where
forecast_element_id=l_fe_rec.forecast_element_id;
select
adj_element_id
from
pa_fp_adj_elements
where
budget_version_id = p_source_version_id and
resource_assignment_id = l_ra_rec.resource_assignment_id;
select target_res_assignment_id
into l_resource_assignment_id
from pa_fp_ra_map_tmp
where source_res_assignment_id = l_ra_rec.resource_assignment_id;
/* PA_FP_ADJ_ELEMENTS: Insert new row for all existing rows with the same budget_version_id */
open l_ae_csr;
select pa_fp_adj_elements_s.nextVal into l_adj_element_id from dual;
insert into pa_fp_adj_elements(
adj_element_id,
project_id,
budget_version_id,
resource_assignment_id,
task_id,
adjustment_reason_code,
adjustment_comments,
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date)
select
l_adj_element_id, /* use newly-created adj_element_id */
ae.project_id,
p_target_version_id, /* use newly-created budget_version_id */
l_resource_assignment_id, /* use newly-created resource_assignment_id */
ae.task_id,
ae.adjustment_reason_code,
ae.adjustment_comments,
SYSDATE, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
FND_GLOBAL.login_id, /* last_update_login */
FND_GLOBAL.user_id, /* last_updated_by */
SYSDATE /* last_update_date */
from
pa_fp_adj_elements ae
where
ae.adj_element_id = l_ae_rec.adj_element_id;
/* PA_FIN_PLAN_ADJ_LINES: Insert new row for all rows with the old adj_element_id*/
insert into pa_fin_plan_adj_lines (
adj_element_id,
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date,
fin_plan_adj_line_id,
project_id,
task_id,
budget_version_id,
resource_assignment_id,
period_name,
start_date,
end_date,
raw_cost_adjustment,
burdened_cost_adjustment,
revenue_adjustment,
utilization_adjustment,
head_count_adjustment)
select
l_adj_element_id, /* use newly-created adj_element_id */
SYSDATE, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
FND_GLOBAL.login_id, /* last_update_login */
FND_GLOBAL.user_id, /* last_updated_by */
SYSDATE, /* last_update_date */
pa_fin_plan_adj_lines_s.nextVal, /* use nextVal for fin_plan_adj_line_id */
al.project_id,
al.task_id,
p_target_version_id, /* use newly-created budget_version_id */
l_resource_assignment_id, /* use newly-created resource assignment id */
al.period_name,
al.start_date,
al.end_date,
al.raw_cost_adjustment,
al.burdened_cost_adjustment,
al.revenue_adjustment,
al.utilization_adjustment,
al.head_count_adjustment
from
pa_fin_plan_adj_lines al
where
al.adj_element_id=l_ae_rec.adj_element_id;
/* PA_PROJECT_PERIODS_DENORM: Insert a new row for every record whose budget_version_id and
resource_assignment_id match our old ones */
/* key on OBJECT_ID = adj_element_id of the original version */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('Create_Org_Fcst_Elements: ' || 'inserting into pa_project_periods_denorm');
insert into pa_proj_periods_denorm (
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date,
budget_version_id,
resource_assignment_id,
object_id,
object_type_code,
period_profile_id,
amount_type_code,
amount_subtype_code,
amount_type_id,
amount_subtype_id,
currency_type,
currency_code,
preceding_periods_amount,
succeeding_periods_amount,
prior_period_amount,
period_amount1,
period_amount2,
period_amount3,
period_amount4,
period_amount5,
period_amount6,
period_amount7,
period_amount8,
period_amount9,
period_amount10,
period_amount11,
period_amount12,
period_amount13,
period_amount14,
period_amount15,
period_amount16,
period_amount17,
period_amount18,
period_amount19,
period_amount20,
period_amount21,
period_amount22,
period_amount23,
period_amount24,
period_amount25,
period_amount26,
period_amount27,
period_amount28,
period_amount29,
period_amount30,
period_amount31,
period_amount32,
period_amount33,
period_amount34,
period_amount35,
period_amount36,
period_amount37,
period_amount38,
period_amount39,
period_amount40,
period_amount41,
period_amount42,
period_amount43,
period_amount44,
period_amount45,
period_amount46,
period_amount47,
period_amount48,
period_amount49,
period_amount50,
period_amount51,
period_amount52,
project_id,
parent_assignment_id)
select
SYSDATE, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
FND_GLOBAL.login_id, /* last_update_login */
FND_GLOBAL.user_id, /* last_updated_by */
SYSDATE, /* last_update_date */
p_target_version_id, /* use newly-created budget_version_id */
ppd.resource_assignment_id, /* copy over resource_assignment_id */
l_adj_element_id, /* object_id is the newly-created adj_element_id */
ppd.object_type_code,
ppd.period_profile_id,
ppd.amount_type_code,
ppd.amount_subtype_code,
ppd.amount_type_id,
ppd.amount_subtype_id,
ppd.currency_type,
ppd.currency_code,
ppd.preceding_periods_amount,
ppd.succeeding_periods_amount,
ppd.prior_period_amount,
ppd.period_amount1,
ppd.period_amount2,
ppd.period_amount3,
ppd.period_amount4,
ppd.period_amount5,
ppd.period_amount6,
ppd.period_amount7,
ppd.period_amount8,
ppd.period_amount9,
ppd.period_amount10,
ppd.period_amount11,
ppd.period_amount12,
ppd.period_amount13,
ppd.period_amount14,
ppd.period_amount15,
ppd.period_amount16,
ppd.period_amount17,
ppd.period_amount18,
ppd.period_amount19,
ppd.period_amount20,
ppd.period_amount21,
ppd.period_amount22,
ppd.period_amount23,
ppd.period_amount24,
ppd.period_amount25,
ppd.period_amount26,
ppd.period_amount27,
ppd.period_amount28,
ppd.period_amount29,
ppd.period_amount30,
ppd.period_amount31,
ppd.period_amount32,
ppd.period_amount33,
ppd.period_amount34,
ppd.period_amount35,
ppd.period_amount36,
ppd.period_amount37,
ppd.period_amount38,
ppd.period_amount39,
ppd.period_amount40,
ppd.period_amount41,
ppd.period_amount42,
ppd.period_amount43,
ppd.period_amount44,
ppd.period_amount45,
ppd.period_amount46,
ppd.period_amount47,
ppd.period_amount48,
ppd.period_amount49,
ppd.period_amount50,
ppd.period_amount51,
ppd.period_amount52,
p_project_id, --passed value
NULL --Org_Forecast doesn't have rollup
from
pa_proj_periods_denorm ppd
where
ppd.budget_version_id = p_source_version_id and
ppd.object_id = l_ae_rec.adj_element_id;
/* PA_ORG_FCST_ELEMENTS: Insert a new row for each row that contained the old budget version */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('Create_Org_Fcst_Elements: ' || 'insert into pa_org_forecast_elements');
select pa_org_fcst_elements_s.nextVal into l_forecast_element_id from dual;
insert into pa_org_fcst_elements (
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date,
forecast_element_id,
organization_id,
org_id,
budget_version_id,
project_id,
task_id,
provider_receiver_code,
other_organization_id,
other_org_id,
txn_project_id,
assignment_id,
resource_id,
record_version_number)
select
SYSDATE, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
FND_GLOBAL.login_id, /* last_update_login */
FND_GLOBAL.user_id, /* last_updated_by */
SYSDATE, /* last_update_date */
l_forecast_element_id, /* use newly-generated forecast_element_id */
fe.organization_id,
fe.org_id,
p_target_version_id, /* use newly-generated budget_version_id */
fe.project_id,
fe.task_id,
fe.provider_receiver_code,
fe.other_organization_id,
fe.other_org_id,
fe.txn_project_id,
fe.assignment_id,
fe.resource_id,
1 /* record_version_number = 1 */
from
pa_org_fcst_elements fe
where
forecast_element_id=l_fe_rec.forecast_element_id;
insert into pa_org_forecast_lines (
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date,
forecast_line_id,
forecast_element_id,
project_id,
task_id,
period_name,
start_date,
end_date,
quantity,
raw_cost,
burdened_cost,
tp_cost_in,
tp_cost_out,
revenue,
tp_revenue_in,
tp_revenue_out,
record_version_number,
borrowed_revenue,
lent_resource_cost,
unassigned_time_cost,
budget_version_id)
select
SYSDATE, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
FND_GLOBAL.login_id, /* last_update_login */
FND_GLOBAL.user_id, /* last_updated_by */
SYSDATE, /* last_update_date */
pa_org_forecast_lines_s.nextVal, /* use nextVal to generate next forecast_line_id */
l_forecast_element_id, /* use newly-created forecast_element_id */
fl.project_id,
fl.task_id,
fl.period_name,
fl.start_date,
fl.end_date,
fl.quantity,
fl.raw_cost,
fl.burdened_cost,
fl.tp_cost_in,
fl.tp_cost_out,
fl.revenue,
fl.tp_revenue_in,
fl.tp_revenue_out,
1, /* record_version_number */
fl.borrowed_revenue,
fl.lent_resource_cost,
fl.unassigned_time_cost,
p_target_version_id
from
pa_org_forecast_lines fl
where
fl.forecast_line_id=l_fl1_rec.forecast_line_id;
/* PA_PROJECT_PERIODS_DENORM: Insert a new row for every record whose budget_version_id and resource_assignment_id match our old ones */
/* key on OBJECT_ID = forecast_element_id of the original version */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('Create_Org_Fcst_Elements: ' || 'inserting into pa_project_periods_denorm');
insert into pa_proj_periods_denorm (
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date,
budget_version_id,
resource_assignment_id,
object_id,
object_type_code,
period_profile_id,
amount_type_code,
amount_subtype_code,
amount_type_id,
amount_subtype_id,
currency_type,
currency_code,
preceding_periods_amount,
succeeding_periods_amount,
prior_period_amount,
period_amount1,
period_amount2,
period_amount3,
period_amount4,
period_amount5,
period_amount6,
period_amount7,
period_amount8,
period_amount9,
period_amount10,
period_amount11,
period_amount12,
period_amount13,
period_amount14,
period_amount15,
period_amount16,
period_amount17,
period_amount18,
period_amount19,
period_amount20,
period_amount21,
period_amount22,
period_amount23,
period_amount24,
period_amount25,
period_amount26,
period_amount27,
period_amount28,
period_amount29,
period_amount30,
period_amount31,
period_amount32,
period_amount33,
period_amount34,
period_amount35,
period_amount36,
period_amount37,
period_amount38,
period_amount39,
period_amount40,
period_amount41,
period_amount42,
period_amount43,
period_amount44,
period_amount45,
period_amount46,
period_amount47,
period_amount48,
period_amount49,
period_amount50,
period_amount51,
period_amount52,
project_id,
parent_assignment_id)
select
SYSDATE, /* creation_date */
FND_GLOBAL.user_id, /* created_by */
FND_GLOBAL.login_id, /* last_update_login */
FND_GLOBAL.user_id, /* last_updated_by */
SYSDATE, /* last_update_date */
p_target_version_id, /* use newly-created budget_version_id */
ppd.resource_assignment_id, /* use the existing resource_assignment_id */
l_forecast_element_id, /* object_id is the newly-created forecast_element_id */
ppd.object_type_code,
ppd.period_profile_id,
ppd.amount_type_code,
ppd.amount_subtype_code,
ppd.amount_type_id,
ppd.amount_subtype_id,
ppd.currency_type,
ppd.currency_code,
ppd.preceding_periods_amount,
ppd.succeeding_periods_amount,
ppd.prior_period_amount,
ppd.period_amount1,
ppd.period_amount2,
ppd.period_amount3,
ppd.period_amount4,
ppd.period_amount5,
ppd.period_amount6,
ppd.period_amount7,
ppd.period_amount8,
ppd.period_amount9,
ppd.period_amount10,
ppd.period_amount11,
ppd.period_amount12,
ppd.period_amount13,
ppd.period_amount14,
ppd.period_amount15,
ppd.period_amount16,
ppd.period_amount17,
ppd.period_amount18,
ppd.period_amount19,
ppd.period_amount20,
ppd.period_amount21,
ppd.period_amount22,
ppd.period_amount23,
ppd.period_amount24,
ppd.period_amount25,
ppd.period_amount26,
ppd.period_amount27,
ppd.period_amount28,
ppd.period_amount29,
ppd.period_amount30,
ppd.period_amount31,
ppd.period_amount32,
ppd.period_amount33,
ppd.period_amount34,
ppd.period_amount35,
ppd.period_amount36,
ppd.period_amount37,
ppd.period_amount38,
ppd.period_amount39,
ppd.period_amount40,
ppd.period_amount41,
ppd.period_amount42,
ppd.period_amount43,
ppd.period_amount44,
ppd.period_amount45,
ppd.period_amount46,
ppd.period_amount47,
ppd.period_amount48,
ppd.period_amount49,
ppd.period_amount50,
ppd.period_amount51,
ppd.period_amount52,
p_project_id, /* project_id */
NULL --as Org_Fcst doen't have rollup
from
pa_proj_periods_denorm ppd
where
ppd.budget_version_id = p_source_version_id and
ppd.object_id = l_fe_rec.forecast_element_id;
SELECT proj_fp_options_id
,project_id
,fin_plan_option_level_code
,fin_plan_preference_code
,plan_in_multi_curr_flag
,approved_cost_plan_type_flag
,approved_rev_plan_type_flag
,all_fin_plan_level_code
,all_time_phased_code
,all_resource_list_id
,all_amount_set_id
,all_current_planning_period
,all_period_mask_id
,RBS_VERSION_ID
,select_all_res_auto_flag
,cost_fin_plan_level_code
,cost_time_phased_code
,cost_resource_list_id
,cost_amount_set_id
,select_cost_res_auto_flag
,cost_current_planning_period
,cost_period_mask_id
,revenue_fin_plan_level_code
,revenue_resource_list_id
,revenue_time_phased_code
,revenue_amount_set_id
,select_rev_res_auto_flag
,primary_cost_forecast_flag
,primary_rev_forecast_flag
,rev_current_planning_period
,rev_period_mask_id
FROM pa_proj_fp_options
WHERE project_id = c_project_id
AND fin_plan_type_id = c_fin_plan_type_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT pfo.proj_fp_options_id
, pfo.project_id
, pfo.fin_plan_option_level_code
, pfo.fin_plan_preference_code
, pfo.plan_in_multi_curr_flag
, pfo.approved_cost_plan_type_flag
, pfo.approved_rev_plan_type_flag
, pfo.all_fin_plan_level_code
, pfo.all_time_phased_code
, pfo.all_resource_list_id
, pfo.all_amount_set_id
, pfo.all_current_planning_period
, pfo.all_period_mask_id
, pfo.rbs_version_id
, pfo.select_all_res_auto_flag
, pfo.cost_fin_plan_level_code
, pfo.cost_time_phased_code
, pfo.cost_resource_list_id
, pfo.cost_amount_set_id
, pfo.select_cost_res_auto_flag
, pfo.cost_current_planning_period
, pfo.cost_period_mask_id
, pfo.revenue_fin_plan_level_code
, pfo.revenue_resource_list_id
, pfo.revenue_time_phased_code
, pfo.revenue_amount_set_id
, pfo.select_rev_res_auto_flag
, pfo.rev_current_planning_period
, pfo.rev_period_mask_id
, pfo.primary_cost_forecast_flag
, pfo.primary_rev_forecast_flag
, bv.actual_amts_thru_period
, bv.project_structure_version_id
FROM pa_proj_fp_options pfo, pa_budget_versions bv
WHERE pfo.project_id = c_project_id
AND pfo.fin_plan_type_id = c_fin_plan_type_id
AND pfo.fin_plan_version_id = c_fin_plan_version_id
AND bv.budget_version_id = c_fin_plan_version_id
AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
,select_res_auto_flag PA_PROJ_FP_OPTIONS.select_cost_res_auto_flag%TYPE
,source_fp_options_id PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE
,version_type PA_BUDGET_VERSIONS.version_type%TYPE
,project_structure_version_id PA_BUDGET_VERSIONS.project_structure_version_id%TYPE
,rbs_version_id PA_PROJ_FP_OPTIONS.rbs_version_id%TYPE
,primary_cost_forecast_flag PA_BUDGET_VERSIONS.primary_cost_forecast_flag%TYPE
,primary_rev_forecast_flag PA_BUDGET_VERSIONS.primary_rev_forecast_flag%TYPE
,actual_amts_thru_period PA_BUDGET_VERSIONS.actual_amts_thru_period%TYPE := NULL
);
new_version_info_rec.select_res_auto_flag := plan_version_info_rec.select_cost_res_auto_flag;
new_version_info_rec.select_res_auto_flag := plan_version_info_rec.select_rev_res_auto_flag;
new_version_info_rec.select_res_auto_flag := plan_version_info_rec.select_all_res_auto_flag;
new_version_info_rec.select_res_auto_flag := plan_type_info_rec.select_cost_res_auto_flag;
new_version_info_rec.select_res_auto_flag := plan_type_info_rec.select_rev_res_auto_flag;
new_version_info_rec.select_res_auto_flag := plan_type_info_rec.select_all_res_auto_flag;
SELECT nvl(control_flag,'Y'),
nvl(uncategorized_flag,'N')
INTO l_res_list_control_flag,
l_res_list_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = p_resource_list_id;
new_version_info_rec.select_res_auto_flag := 'N';
SELECT nvl(uncategorized_flag,'N')
INTO l_res_list_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = new_version_info_rec.resource_list_id;
/* Bug 2668667 , in the following select clause, version type condition is necessary
in the following sceniaro. If the plan type is attached is cost_and_rev_sep and
a cost version is created and then a revenue version is created. Now , the revenue
version should also be set as current_working_version. */
SELECT budget_version_id
INTO l_dummy_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = new_version_info_rec.version_type -- Bug :- 2668667
AND current_working_flag = 'Y';
SELECT pa_budget_versions_s.NEXTVAL
INTO l_new_budget_version_id
FROM DUAL;
pa_debug.g_err_stage:='Calling budget_versions table handler to insert new row ';
SELECT use_for_workplan_flag
INTO l_wp_version_flag
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id;
Select 'Y'
Into l_exists
From pa_budget_versions
Where project_structure_version_id =
nvl(p_struct_elem_version_id,new_version_info_rec.project_structure_version_id)
And wp_version_flag = 'Y'
And exists (select 'x' from pa_budget_versions b
where b.budget_version_id =
nvl(l_ci_apprv_cw_bv_id,l_fin_plan_version_id)
and b.wp_version_flag = 'Y') ;
pa_fp_budget_versions_pkg.Insert_Row
(px_budget_version_id => l_new_budget_version_id, -- unique budget_version_id for new version
p_project_id => p_project_id, -- the ID of the project
p_budget_type_code => NULL,
p_version_number => l_max_version_number+1, -- version_number incremented
p_budget_status_code => 'W', -- 'Working' version
p_current_flag => 'N', -- 'Working' version
p_original_flag => 'N', -- 'Working' version
p_current_original_flag => 'N', -- 'Working' version
p_resource_accumulated_flag => 'N', -- HARDCODED VALUE
p_resource_list_id => new_version_info_rec.resource_list_id,
p_version_name => p_version_name, -- user-entered value
p_budget_entry_method_code => NULL,
p_baselined_by_person_id => NULL,
p_baselined_date => NULL,
p_change_reason_code => NULL,
p_labor_quantity => NULL,
p_labor_unit_of_measure => 'HOURS',
p_raw_cost => NULL,
p_burdened_cost => NULL,
p_revenue => NULL,
p_description => p_description, -- user-entered value
--Bug3088010 start: Changed NULL to the parameters passed in to this api
p_attribute_category => p_attribute_category, --NULL,
p_attribute1 => p_attribute1, --NULL,
p_attribute2 => p_attribute2, --NULL,
p_attribute3 => p_attribute3, --NULL,
p_attribute4 => p_attribute4, --NULL,
p_attribute5 => p_attribute5, --NULL,
p_attribute6 => p_attribute6, --NULL,
p_attribute7 => p_attribute7, --NULL,
p_attribute8 => p_attribute8, --NULL,
p_attribute9 => p_attribute9, --NULL,
p_attribute10 => p_attribute10, --NULL,
p_attribute11 => p_attribute11, --NULL,
p_attribute12 => p_attribute12, --NULL,
p_attribute13 => p_attribute13, --NULL,
p_attribute14 => p_attribute14, --NULL,
p_attribute15 => p_attribute15, --NULL,
--Bug3088010 end: Changed NULL to the parameters passed in to this api
p_first_budget_period => NULL,
p_pm_product_code => p_pm_product_code, --NULL, --Bug 5403751
p_pm_budget_reference => NULL,
p_wf_status_code => NULL,
p_adw_notify_flag => NULL,
p_prc_generated_flag => NULL,
p_plan_run_date => NULL,
p_plan_processing_code => NULL,
p_fin_plan_type_id => p_fin_plan_type_id,
p_parent_plan_version_id => NULL,
p_project_structure_version_id => nvl(p_struct_elem_version_id,new_version_info_rec.project_structure_version_id),
p_current_working_flag => l_current_working_flag,
p_total_borrowed_revenue => NULL,
p_total_tp_revenue_in => NULL,
p_total_tp_revenue_out => NULL,
p_total_revenue_adj => NULL,
p_total_lent_resource_cost => NULL,
p_total_tp_cost_in => NULL,
p_total_tp_cost_out => NULL,
p_total_cost_adj => NULL,
p_total_unassigned_time_cost => NULL,
p_total_utilization_percent => NULL,
p_total_utilization_hours => NULL,
p_total_utilization_adj => NULL,
p_total_capacity => NULL,
p_total_head_count => NULL,
p_total_head_count_adj => NULL,
p_version_type => new_version_info_rec.version_type,
p_request_id => FND_GLOBAL.conc_request_id,
p_total_project_raw_cost => NULL,
p_total_project_burdened_cost => NULL,
p_total_project_revenue => NULL,
p_locked_by_person_id => NULL,
p_approved_cost_plan_type_flag => new_version_info_rec.approved_cost_plan_type_flag,
p_approved_rev_plan_type_flag => new_version_info_rec.approved_rev_plan_type_flag,
p_est_project_raw_cost => p_est_proj_raw_cost,
p_est_project_burdened_cost => p_est_proj_bd_cost,
p_est_project_revenue => p_est_proj_revenue,
p_est_quantity => p_est_qty,
p_est_equip_qty => p_est_equip_qty,
p_est_projfunc_raw_cost => NULL,
p_est_projfunc_burdened_cost => NULL,
p_est_projfunc_revenue => NULL,
p_ci_id => p_ci_id,
p_agreement_id => p_agreement_id,
p_refresh_required_flag => NULL, -- redundant in patchset M
p_object_type_code => 'PROJECT',
p_object_id => p_project_id,
p_primary_cost_forecast_flag => new_version_info_rec.primary_cost_forecast_flag,
p_primary_rev_forecast_flag => new_version_info_rec.PRIMARY_REV_FORECAST_FLAG,
p_rev_partially_impl_flag => 'N',
p_equipment_quantity => NULL,
p_pji_summarized_flag => 'N',
p_wp_version_flag => l_WP_VERSION_FLAG,
p_current_planning_period => new_version_info_rec.CURRENT_PLANNING_PERIOD,
p_period_mask_id => new_version_info_rec.PERIOD_MASK_ID,
p_actual_amts_thru_period => new_version_info_rec.actual_amts_thru_period,
p_last_amt_gen_date => NULL,
x_row_id => l_row_id,
x_return_status => x_return_status);
UPDATE pa_proj_fp_options
SET cost_amount_set_id = NVL(p_amount_set_id,new_version_info_rec.amount_set_id),
plan_in_multi_curr_flag = new_version_info_rec.plan_in_multi_curr_flag,
cost_fin_plan_level_code = new_version_info_rec.fin_plan_level_code,
cost_time_phased_code = new_version_info_rec.time_phased_code,
cost_resource_list_id = new_version_info_rec.resource_list_id,
select_cost_res_auto_flag = new_version_info_rec.select_res_auto_flag,
cost_current_planning_period = new_version_info_rec.current_planning_period,
cost_period_mask_id = new_version_info_rec.period_mask_id,
rbs_version_id = Decode(p_ci_id, null, rbs_version_id, null) -- bug 3867302
WHERE proj_fp_options_id = l_new_proj_fp_options_id;
UPDATE pa_proj_fp_options
SET revenue_amount_set_id = NVL(p_amount_set_id,new_version_info_rec.amount_set_id),
plan_in_multi_curr_flag = new_version_info_rec.plan_in_multi_curr_flag,
revenue_fin_plan_level_code = new_version_info_rec.fin_plan_level_code,
revenue_time_phased_code = new_version_info_rec.time_phased_code,
revenue_resource_list_id = new_version_info_rec.resource_list_id,
select_rev_res_auto_flag = new_version_info_rec.select_res_auto_flag,
rev_current_planning_period = new_version_info_rec.current_planning_period,
rev_period_mask_id = new_version_info_rec.period_mask_id,
rbs_version_id = Decode(p_ci_id, null, rbs_version_id, null) -- bug 3867302
WHERE proj_fp_options_id = l_new_proj_fp_options_id;
UPDATE pa_proj_fp_options
SET all_amount_set_id = NVL(p_amount_set_id,new_version_info_rec.amount_set_id),
plan_in_multi_curr_flag = new_version_info_rec.plan_in_multi_curr_flag,
all_fin_plan_level_code = new_version_info_rec.fin_plan_level_code,
all_time_phased_code = new_version_info_rec.time_phased_code,
all_resource_list_id = new_version_info_rec.resource_list_id,
select_all_res_auto_flag = new_version_info_rec.select_res_auto_flag,
all_current_planning_period = new_version_info_rec.current_planning_period,
all_period_mask_id = new_version_info_rec.period_mask_id,
rbs_version_id = Decode(p_ci_id, null, rbs_version_id, null) -- bug 3867302
WHERE proj_fp_options_id = l_new_proj_fp_options_id;
l_budget_version_ids.delete;
update pa_fp_txn_currencies
set project_rev_exchange_rate = l_project_bil_exchange_rate
where proj_fp_options_id = l_new_proj_fp_options_id
and txn_currency_code = l_agreement_currency_code;
update pa_fp_txn_currencies
set projfunc_rev_exchange_rate = l_projfunc_bil_exchange_rate
where proj_fp_options_id = l_new_proj_fp_options_id
and txn_currency_code = l_agreement_currency_code;
UPDATE pa_proj_fp_options
SET PROJECT_REV_RATE_TYPE = l_project_bil_rate_type
,PROJECT_REV_RATE_DATE_TYPE = l_project_bil_rate_date_code
,PROJECT_REV_RATE_DATE = l_project_bil_rate_date
,PROJFUNC_REV_RATE_TYPE = l_projfunc_bil_rate_type
,PROJFUNC_REV_RATE_DATE_TYPE = l_projfunc_bil_rate_date_code
,PROJFUNC_REV_RATE_DATE = l_projfunc_bil_rate_date
WHERE proj_fp_options_id = l_new_proj_fp_options_id;
UPDATE Pa_Budget_Versions SET
est_projfunc_raw_cost = l_est_projfunc_raw_cost,
est_projfunc_burdened_cost = l_est_projfunc_bd_cost,
est_projfunc_revenue = l_est_projfunc_revenue
WHERE Budget_Version_Id = l_new_budget_version_id;
UPDATE pa_resource_assignments
SET rbs_element_id = l_rbs_element_id_tbl(j)
,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = l_new_budget_version_id
AND resource_assignment_id = l_txn_source_id_tbl(j);
UPDATE pa_resource_assignments
SET rbs_element_id = null
,txn_accum_header_id = null
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = l_new_budget_version_id;
* to insert those resource assignment with default applicable currency
*/
PA_FIN_PLAN_PUB.create_default_plan_txn_rec
(p_budget_version_id => l_new_budget_version_id,
p_calling_module => 'COPY_PLAN',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT start_date
INTO x_start_period_start_date
FROM PA_PERIODS
WHERE p_profile_start_date BETWEEN start_date AND end_date;
SELECT MIN(START_DATE)
INTO x_start_period_start_date
FROM PA_PERIODS;
SELECT end_date
INTO x_end_period_end_date
FROM PA_PERIODS
WHERE p_profile_end_date BETWEEN start_date AND end_date;
SELECT MAX(end_date)
INTO x_end_period_end_date
FROM PA_PERIODS;
SELECT start_date
INTO x_start_period_start_date
FROM GL_PERIOD_STATUSES g
,PA_IMPLEMENTATIONS i
WHERE adjustment_period_flag = 'N'
AND g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id
AND p_profile_start_date BETWEEN g.start_date AND g.end_date;
SELECT MIN(start_date)
INTO x_start_period_start_date
FROM GL_PERIOD_STATUSES g
,PA_IMPLEMENTATIONS i
WHERE adjustment_period_flag = 'N'
AND g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id;
SELECT end_date
INTO x_end_period_end_date
FROM GL_PERIOD_STATUSES g
,PA_IMPLEMENTATIONS i
WHERE adjustment_period_flag = 'N'
AND g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id
AND p_profile_end_date BETWEEN g.start_date AND g.end_date;
SELECT MAX(end_date)
INTO x_end_period_end_date--Selected the max(end_date) into x_end_period_end_date. Bug 3329002.
FROM GL_PERIOD_STATUSES g
,PA_IMPLEMENTATIONS i
WHERE adjustment_period_flag = 'N'
AND g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id;
SELECT start_date
INTO l_profile_start_date
FROM pa_periods a
WHERE 51= (SELECT COUNT(*) FROM pa_periods b
WHERE a.start_date < b.start_date
AND b.start_date <= p_profile_end_date );
SELECT MIN(start_date)
INTO l_profile_start_date
FROM pa_periods;
SELECT start_date
INTO l_profile_start_date
FROM GL_PERIOD_STATUSES a
,PA_IMPLEMENTATIONS i
WHERE a.application_id = pa_period_process_pkg.application_id
AND a.set_of_books_id = i.set_of_books_id
AND a.adjustment_period_flag = 'N'
AND 51= (SELECT COUNT(*) FROM GL_PERIOD_STATUSES b
,PA_IMPLEMENTATIONS i2
WHERE b.adjustment_period_flag = 'N'
AND b.application_id = pa_period_process_pkg.application_id
AND b.set_of_books_id = i2.set_of_books_id
AND a.start_date < b.start_date
AND b.start_date <= p_profile_end_date);
SELECT MIN(start_date)
INTO l_profile_start_date
FROM GL_PERIOD_STATUSES a
,PA_IMPLEMENTATIONS i
WHERE a.application_id = pa_period_process_pkg.application_id
AND a.set_of_books_id = i.set_of_books_id
AND a.adjustment_period_flag = 'N';
SELECT end_date
INTO l_profile_end_date
FROM GL_PERIOD_STATUSES a
,PA_IMPLEMENTATIONS i
WHERE a.application_id = pa_period_process_pkg.application_id
AND a.set_of_books_id = i.set_of_books_id
AND a.adjustment_period_flag = 'N'
AND 51= (SELECT COUNT(*) FROM GL_PERIOD_STATUSES b
,PA_IMPLEMENTATIONS i2
WHERE b.adjustment_period_flag = 'N'
AND b.application_id = pa_period_process_pkg.application_id
AND b.set_of_books_id = i2.set_of_books_id
AND a.start_date > b.start_date
AND b.start_date >= p_profile_start_date);
SELECT MAX(end_date)
INTO l_profile_end_date
FROM GL_PERIOD_STATUSES a
,PA_IMPLEMENTATIONS i
WHERE a.application_id = pa_period_process_pkg.application_id
AND a.set_of_books_id = i.set_of_books_id
AND a.adjustment_period_flag = 'N';
SELECT end_date
INTO l_profile_end_date
FROM pa_periods a
WHERE 51= (SELECT COUNT(*) FROM pa_periods b
WHERE a.start_date > b.start_date
AND b.start_date >= p_profile_start_date );
SELECT MAX(end_date)
INTO l_profile_end_date
FROM pa_periods;
SELECT start_date
,completion_date
INTO l_project_start_date
,l_project_completion_date
FROM pa_projects_all
WHERE project_id = p_project_id;
pa_debug.g_err_stage := 'Selecting start and end dates from pa_budget_lines of the project';
SELECT MIN (pbl.start_date)
,MAX(pbl.end_date)
INTO l_start_date
,l_end_date
FROM pa_budget_versions pbv --bug#2708524 pa_resource_assignments pra
,pa_budget_lines pbl
WHERE pbv.project_id = p_project_id
AND pbl.budget_version_id = pbv.budget_version_id
AND PA_FIN_PLAN_UTILS.GET_TIME_PHASED_CODE(pbv.budget_version_id)
= DECODE(p_period_type,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P) ;
SELECT b.period_set_name
,DECODE(p_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA ,pa_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL ,accounted_period_type) --accounted_period_type
,DECODE(p_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA ,pa_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL ,NULL) --pa_period_type
INTO l_period_set_name
,l_accounted_period_type
,l_pa_period_type
FROM pa_implementations a
,gl_sets_of_books b
WHERE a.set_of_books_id = b.set_of_books_id;
SELECT count(*)
INTO l_number_of_periods
FROM PA_PERIODS
WHERE start_date BETWEEN TRUNC(SYSDATE) AND l_end_date; /*2690087*/
SELECT count(*)
INTO l_number_of_periods
FROM GL_PERIOD_STATUSES a
,PA_IMPLEMENTATIONS i
WHERE a.application_id = pa_period_process_pkg.application_id
AND a.set_of_books_id = i.set_of_books_id
AND a.adjustment_period_flag = 'N'
AND start_date BETWEEN TRUNC(SYSDATE) AND l_end_date; -- Bug :- 2623941, last condition has been put for 2623941
SELECT start_date
INTO l_profile_start_date
FROM GL_PERIOD_STATUSES g
,PA_IMPLEMENTATIONS i
WHERE adjustment_period_flag = 'N'
AND g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id
AND TRUNC(SYSDATE) BETWEEN g.start_date AND g.end_date; /* Bug:- 2690087 */
SELECT start_date
INTO l_profile_start_date
FROM PA_PERIODS
WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date; /* Bug:- 2690087 */
PROCEDURE INSERT_PLAN_LINES_TMP_BULK
(p_res_assignment_tbl IN p_res_assignment_tbl_typ
,p_period_name_tbl IN p_period_name_tbl_typ
,p_start_date_tbl IN p_start_date_tbl_typ
,p_end_date_tbl IN p_end_date_tbl_typ
,p_currency_type IN pa_proj_periods_denorm.currency_type%TYPE
,p_currency_code_tbl IN p_currency_code_tbl_typ
,p_quantity_tbl IN p_quantity_tbl_typ
,p_raw_cost_tbl IN p_cost_tbl_typ
,p_burdened_cost_tbl IN p_cost_tbl_typ
,p_revenue_tbl IN p_cost_tbl_typ
,p_old_quantity_tbl IN p_quantity_tbl_typ
,p_old_raw_cost_tbl IN p_cost_tbl_typ
,p_old_burdened_cost_tbl IN p_cost_tbl_typ
,p_old_revenue_tbl IN p_cost_tbl_typ
,p_margin_tbl IN p_cost_tbl_typ
,p_margin_percent_tbl IN p_cost_tbl_typ
,p_old_margin_tbl IN p_cost_tbl_typ
,p_old_margin_percent_tbl IN p_cost_tbl_typ
,p_buck_period_code_tbl IN p_buck_period_code_tbl_typ
,p_parent_assignment_id_tbl IN p_res_assignment_tbl_typ
,p_delete_flag_tbl IN p_delete_flag_tbl_typ
,p_source_txn_curr_code_tbl IN p_currency_code_tbl_typ
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
l_stage NUMBER :=100 ;
pa_debug.set_err_stack('PA_FIN_PLAN_PUB.INSERT_PLAN_LINES_TMP_BULK');
pa_debug.set_process('INSERT_PLAN_LINES_TMP_BULK: ' || 'PLSQL','LOG',l_debug_mode);
pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FIN_PLAN_PUB.INSERT_PLAN_LINES_TMP_BULK ';
pa_debug.write('INSERT_PLAN_LINES_TMP_BULK: ' || l_module_name,pa_debug.g_err_stage,2);
* Bulk Insert records into PA_FP_ELEMENTS table for the records fetched
* from cursor top_task_cur.
*/
pa_debug.g_err_stage := TO_CHAR(l_stage)||': INSERT into fin plan lines tmp';
pa_debug.write('INSERT_PLAN_LINES_TMP_BULK: ' || l_module_name,pa_debug.g_err_stage,2);
INSERT INTO PA_FIN_PLAN_LINES_TMP (
OBJECT_ID
,OBJECT_TYPE_CODE
,RESOURCE_ASSIGNMENT_ID
,PERIOD_NAME
,START_DATE
,END_DATE
,CURRENCY_TYPE
,CURRENCY_CODE
,QUANTITY
,RAW_COST
,BURDENED_COST
,REVENUE
,OLD_QUANTITY
,OLD_RAW_COST
,OLD_BURDENED_COST
,OLD_REVENUE
,MARGIN
,MARGIN_PERCENTAGE
,OLD_MARGIN
,OLD_MARGIN_PERCENTAGE
,BUCKETING_PERIOD_CODE
,PARENT_ASSIGNMENT_ID
,DELETE_FLAG
,SOURCE_TXN_CURRENCY_CODE
)
VALUES ( p_res_assignment_tbl(i) /* Bug# 2677867-Object id should not be -1 even for FP */
,PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT
,p_res_assignment_tbl(i)
,p_period_name_tbl(i)
,p_start_date_tbl(i)
,p_end_date_tbl(i)
,p_currency_type
,p_currency_code_tbl(i)
,p_quantity_tbl(i)
,p_raw_cost_tbl(i)
,p_burdened_cost_tbl(i)
,p_revenue_tbl(i)
,p_old_quantity_tbl(i) /* Bug # 2738047 : Corrected the order of the table */
,p_old_raw_cost_tbl(i)
,p_old_burdened_cost_tbl(i)
,p_old_revenue_tbl(i)
,p_margin_tbl(i)
,p_margin_percent_tbl(i)
,p_old_margin_tbl(i)
,p_old_margin_percent_tbl(i)
,p_buck_period_code_tbl(i)
,p_parent_assignment_id_tbl(i)
,p_delete_flag_tbl(i)
,p_source_txn_curr_code_tbl(i)) ;
pa_debug.g_err_stage := TO_CHAR(l_stage)||': INSERTED ' || sql%rowcount || ' recs into fin plan lines tmp';
pa_debug.write('INSERT_PLAN_LINES_TMP_BULK: ' || l_module_name,pa_debug.g_err_stage,2);
pa_debug.write('INSERT_PLAN_LINES_TMP_BULK: ' || l_module_name,SQLERRM,4);
pa_debug.write('INSERT_PLAN_LINES_TMP_BULK: ' || l_module_name,pa_debug.G_Err_Stack,4);
END INSERT_PLAN_LINES_TMP_BULK ;
Budget Lines with appropriate values and call Maintain Matrix API to update the
USER_ENTERED level records into pa_proj_periods_denorm table.
---------------------------------------------------------------------------------------------*/
PROCEDURE Call_Maintain_Plan_Matrix (
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_data_source IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER := 0;
l_delete_flag_tbl p_delete_flag_tbl_typ;
SELECT pbl.resource_assignment_id
,period_name
,start_date
,end_date
,txn_currency_code
,project_currency_code
,projfunc_currency_code
,quantity
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,null txn_margin
,null txn_margin_percent
,project_raw_cost
,project_burdened_cost
,project_revenue
,(project_revenue - decode(l_margin_derived_from_code,'R',project_raw_cost
,'B',project_burdened_cost)) project_margin
,((project_revenue - decode(l_margin_derived_from_code,'R',project_raw_cost
,'B',project_burdened_cost))/
decode(project_revenue,0,NULL,project_revenue))*100
project_margin_percentage
,raw_cost
,burdened_cost
,revenue
,(revenue - decode(l_margin_derived_from_code,'R',raw_cost
,'B',burdened_cost)) projfunc_margin
,((revenue - decode(l_margin_derived_from_code,'R',raw_cost
,'B',burdened_cost))/
decode(revenue,0,NULL,revenue))*100 projfunc_margin_percentage
,null old_quantity
,null old_txn_raw_cost
,null old_txn_burdened_cost
,null old_txn_revenue
,null old_txn_margin
,null old_txn_margin_percent
,null old_proj_raw_cost
,null old_proj_burdened_cost
,null old_proj_revenue
,null old_proj_margin
,null old_proj_margin_percent
,null old_projfunc_raw_cost
,null old_projfunc_burdened_cost
,null old_projfunc_revenue
,null old_projfunc_margin
,null old_projfunc_margin_percent
,bucketing_period_code
,pra.parent_assignment_id
,null delete_flag
FROM pa_resource_assignments pra, pa_budget_lines pbl
WHERE pra.budget_version_id = p_budget_version_id
AND pra.resource_assignment_id = pbl.resource_assignment_id;
SELECT frt.resource_assignment_id
,period_name
,start_date
,end_date
,txn_currency_code
,project_currency_code
,projfunc_currency_code
,quantity
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,null txn_margin
,null txn_margin_percent
,project_raw_cost
,project_burdened_cost
,project_revenue
,(project_revenue - decode(l_margin_derived_from_code,'R',project_raw_cost
,'B',project_burdened_cost)) project_margin
,((project_revenue - decode(l_margin_derived_from_code,'R',project_raw_cost
,'B',project_burdened_cost))/
decode(project_revenue,0,NULL,project_revenue))*100
project_margin_percentage
,projfunc_raw_cost
,projfunc_burdened_cost
,projfunc_revenue
,(projfunc_revenue - decode(l_margin_derived_from_code,'R',projfunc_raw_cost
,'B',projfunc_burdened_cost)) projfunc_margin
,((projfunc_revenue - decode(l_margin_derived_from_code,'R',projfunc_raw_cost
,'B',projfunc_burdened_cost))/
decode(projfunc_revenue,0,NULL,projfunc_revenue))*100
projfunc_margin_percentage
,old_quantity
,NULL old_txn_raw_cost
,NULL old_txn_burdened_cost
,null old_txn_revenue
,null old_txn_margin
,null old_txn_margin_percent
,old_proj_raw_cost
,old_proj_burdened_cost
,old_proj_revenue
,(old_proj_revenue - decode(l_margin_derived_from_code,'R',old_proj_raw_cost
,'B',old_proj_burdened_cost)) old_project_margin
,((old_proj_revenue - decode(l_margin_derived_from_code,'R',old_proj_raw_cost
,'B',old_proj_burdened_cost))/
decode(old_proj_revenue,0,NULL,old_proj_revenue))*100
old_project_margin_percentage
,old_projfunc_raw_cost
,old_projfunc_burdened_cost
,old_projfunc_revenue
,(old_projfunc_revenue - decode(l_margin_derived_from_code,'R',old_projfunc_raw_cost
,'B',old_projfunc_burdened_cost)) projfunc_margin
,((old_projfunc_revenue - decode(l_margin_derived_from_code,'R',old_projfunc_raw_cost
,'B',old_projfunc_burdened_cost))/
decode(old_projfunc_revenue,0,NULL,old_projfunc_revenue))*100
old_projfunc_margin_percentage
,bucketing_period_code
,pra.parent_assignment_id
,delete_flag
FROM pa_resource_assignments pra, pa_fp_rollup_tmp frt
WHERE pra.budget_version_id = p_budget_version_id
AND pra.resource_assignment_id = frt.resource_assignment_id;
SELECT project_id,
period_profile_id
INTO l_project_id,
l_period_profile_id
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT fin_plan_preference_code, margin_derived_from_code
INTO l_fp_preference_code, l_margin_derived_from_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_budget_version_id;
delete from PA_FIN_PLAN_LINES_TMP;
,l_delete_flag_tbl
LIMIT l_plsql_max_array_size;
,l_delete_flag_tbl
LIMIT l_plsql_max_array_size;
/* Insert the Transaction, Project and Project Functional currency
columns as rows into the Lines Temp table one after the other selecting
from the Budget Lines. So, three inserts statements are required. */
/* Inserting the Transaction Currency records into the Lines Temp table with
the Amount Type code as 'TRANSACTION'. */
pa_debug.g_err_stage := 'Inserting Transaction Currency Records';
/* insert txn amounts */
pa_debug.g_err_stage := 'calling insert_plan_lines_tmp_bulk for txn curr';
insert_plan_lines_tmp_bulk(
p_res_assignment_tbl => l_res_assignment_tbl
,p_period_name_tbl => l_period_name_tbl
,p_start_date_tbl => l_start_date_tbl
,p_end_date_tbl => l_end_date_tbl
,p_currency_type => PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION
,p_currency_code_tbl => l_txn_curr_code_tbl
,p_quantity_tbl => l_quantity_tbl
,p_raw_cost_tbl => l_txn_raw_cost_tbl
,p_burdened_cost_tbl => l_txn_burdened_cost_tbl
,p_revenue_tbl => l_txn_revenue_tbl
,p_old_quantity_tbl => l_old_quantity_tbl
,p_old_raw_cost_tbl => l_old_txn_raw_cost_tbl
,p_old_burdened_cost_tbl => l_old_txn_burdened_cost_tbl
,p_old_revenue_tbl => l_old_txn_revenue_tbl
,p_margin_tbl => l_txn_margin_tbl
,p_margin_percent_tbl => l_txn_margin_percent_tbl
,p_old_margin_tbl => l_old_txn_margin_tbl
,p_old_margin_percent_tbl => l_old_txn_margin_percent_tbl
,p_buck_period_code_tbl => l_buck_period_code_tbl
,p_parent_assignment_id_tbl => l_parent_assignment_tbl
,p_delete_flag_tbl => l_delete_flag_tbl
,p_source_txn_curr_code_tbl => l_txn_curr_code_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
/* insert project amounts */
pa_debug.g_err_stage := 'calling insert_plan_lines_tmp_bulk for proj curr';
insert_plan_lines_tmp_bulk(
p_res_assignment_tbl => l_res_assignment_tbl
,p_period_name_tbl => l_period_name_tbl
,p_start_date_tbl => l_start_date_tbl
,p_end_date_tbl => l_end_date_tbl
,p_currency_type => PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_PROJECT
,p_currency_code_tbl => l_proj_curr_code_tbl
,p_quantity_tbl => l_quantity_tbl
,p_raw_cost_tbl => l_proj_raw_cost_tbl
,p_burdened_cost_tbl => l_proj_burdened_cost_tbl
,p_revenue_tbl => l_proj_revenue_tbl
,p_old_quantity_tbl => l_old_quantity_tbl
,p_old_raw_cost_tbl => l_old_proj_raw_cost_tbl
,p_old_burdened_cost_tbl => l_old_proj_burd_cost_tbl
,p_old_revenue_tbl => l_old_proj_revenue_tbl
,p_margin_tbl => l_proj_margin_tbl
,p_margin_percent_tbl => l_proj_margin_percent_tbl
,p_old_margin_tbl => l_old_proj_margin_tbl
,p_old_margin_percent_tbl => l_old_proj_margin_percent_tbl
,p_buck_period_code_tbl => l_buck_period_code_tbl
,p_parent_assignment_id_tbl => l_parent_assignment_tbl
,p_delete_flag_tbl => l_delete_flag_tbl
,p_source_txn_curr_code_tbl => l_txn_curr_code_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
/* insert project functional amounts */
pa_debug.g_err_stage := 'calling insert_plan_lines_tmp_bulk for projfunc curr';
insert_plan_lines_tmp_bulk(
p_res_assignment_tbl => l_res_assignment_tbl
,p_period_name_tbl => l_period_name_tbl
,p_start_date_tbl => l_start_date_tbl
,p_end_date_tbl => l_end_date_tbl
,p_currency_type => PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_PROJFUNC
,p_currency_code_tbl => l_projfunc_curr_code_tbl
,p_quantity_tbl => l_quantity_tbl
,p_raw_cost_tbl => l_projfunc_raw_cost_tbl
,p_burdened_cost_tbl => l_projfunc_burd_cost_tbl
,p_revenue_tbl => l_projfunc_revenue_tbl
,p_old_quantity_tbl => l_old_quantity_tbl
,p_old_raw_cost_tbl => l_old_projfunc_raw_cost_tbl
,p_old_burdened_cost_tbl => l_old_projfunc_burd_cost_tbl
,p_old_revenue_tbl => l_old_projfunc_revenue_tbl
,p_margin_tbl => l_projfunc_margin_tbl
,p_margin_percent_tbl => l_projfunc_margin_percent_tbl
,p_old_margin_tbl => l_old_projfunc_margin_tbl
,p_old_margin_percent_tbl => l_old_projfunc_margin_pct_tbl
,p_buck_period_code_tbl => l_buck_period_code_tbl
,p_parent_assignment_id_tbl => l_parent_assignment_tbl
,p_delete_flag_tbl => l_delete_flag_tbl
,p_source_txn_curr_code_tbl => l_txn_curr_code_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
END LOOP; -- loop for bulk insert
SELECT version_type
INTO l_budget_version_type
FROM PA_BUDGET_VERSIONS
WHERE pa_budget_versions.budget_version_id = p_budget_version_id;
This api would be called in the context of workplan. This procedure deletes
any exsiting res list assignment and creates new assignment for the input
resource list.
==============================================================================*/
PROCEDURE Refresh_res_list_assignment (
p_project_id IN pa_budget_versions.project_id%TYPE
,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
AS
--Start of variables used for debugging
l_return_status VARCHAR2(1);
SELECT resource_list_assignment_id
INTO l_existing_rl_assignment_id
FROM pa_resource_list_assignments
WHERE project_id = p_project_id
AND resource_list_id = p_resource_list_id
AND used_in_wp_flag = 'Y' ;
PA_RES_LIST_ASSIGNMENTS.Delete_Rl_Assgmt(
X_Resource_list_Assgmt_id => l_existing_rl_assignment_id
,X_err_code => l_err_code
,X_err_stage => l_err_stage
,x_err_stack => l_err_stack );
* budget version id and inserts records in the new IPM table PA_RESOURCE_ASGN_CURR
* for the resource assignments which do not have budget lines and hence
* not taken care by the new plannig transaction level entiy maintenance API.
* These resource assignments are inserted with default currency as applicable.
*
* This API is called from upgrade_budget_versions API[PAFPUPGB.pls].
*/
PROCEDURE create_default_plan_txn_rec
(p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
p_calling_module IN VARCHAR2,
p_ra_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(), /* 7161809 */
p_curr_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE(), /* 7161809 */
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(2000);
SELECT ra.resource_assignment_id
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND NOT EXISTS (SELECT 'X'
FROM pa_resource_asgn_curr rac
WHERE rac.resource_assignment_id = ra.resource_assignment_id
AND rac.budget_version_id = ra.budget_version_id);
if (p_calling_module = 'UPDATE_PLAN_TRANSACTION') then /* janani */
l_def_plan_txn_ra_id_tbl := p_ra_id_tbl;
SELECT pbv.ci_id,
pbv.agreement_id,
pbv.approved_rev_plan_type_flag,
ppa.project_currency_code,
ppa.projfunc_currency_code
INTO l_ci_id,
l_agreement_id,
l_app_rev_flag,
l_proj_curr_code,
l_pfunc_curr_code
FROM pa_budget_versions pbv,
pa_projects_all ppa
WHERE pbv.budget_version_id = p_budget_version_id
AND ppa.project_id = pbv.project_id;
SELECT agreement_currency_code
INTO l_agr_curr_code
FROM pa_agreements_all
WHERE agreement_id = l_agreement_id;
* in this case, when user visits the change order page, he has to select a valid
* agreement before proceeding. In that case, all the existing budget lines for the revenue/all
* change order is deleted and new budget lines would be created with the agreement currency
* as the txn currency. But, to create the planning transaction with the default currency,
* in the new entity for change orders, which do not have an agreement yet, we are defaulting
* PFC as the txn currency. Anyway, this would be deleted, when user selects a valid agreement.
*/
l_def_txn_curr_code := l_pfunc_curr_code;
DELETE pa_resource_asgn_curr_tmp;
/* bulk insert the left over resource assignments into the new table with default txn currency
* derived above */
FORALL k IN l_def_plan_txn_ra_id_tbl.FIRST .. l_def_plan_txn_ra_id_tbl.LAST
INSERT INTO pa_resource_asgn_curr_tmp
(RA_TXN_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE)
VALUES
(pa_resource_asgn_curr_s.nextval,
p_budget_version_id,
l_def_plan_txn_ra_id_tbl(k),
/*l_def_txn_curr_code); */
/* calling the maintenance api to insert data into the new planning transaction level table */
if (p_calling_module = 'UPDATE_PLAN_TRANSACTION') then /* janani */
pa_res_asg_currency_pub.maintain_data(
p_fp_cols_rec => l_fp_cols_rec,
p_calling_module => p_calling_module,
p_delete_flag => 'N',
p_copy_flag => 'N',
p_src_version_id => NULL,
p_copy_mode => NULL,
p_rollup_flag => 'Y',
p_version_level_flag => 'N',
p_called_mode => 'SELF_SERVICE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END IF; -- if there is ra_id to be inserted