[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Project_Currency_Code, NVL(Baseline_Funding_Flag,'N')
INTO
x_project_currency_code,
x_baseline_funding_flag
FROM
Pa_Projects_All WHERE Project_Id = p_project_id;
SELECT COUNT(*) INTO x_no_of_ci_plan_versions FROM pa_budget_Versions
WHERE project_id = p_project_id AND
nvl(ci_id,-1) = p_ci_id;
SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
WHERE
Project_Id = p_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y' ) ;
SELECT Fin_Plan_Preference_Code,
Report_Labor_Hrs_From_Code,
Fin_Plan_Type_Id
INTO
x_fp_pref_code,
x_report_labor_hours_code,
l_tmp_fin_plan_type_id
FROM Pa_Proj_Fp_Options WHERE
Project_Id = p_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y');
select --typ.cost_col_flag,
--typ.rev_col_flag,
--typ.DIR_COST_REG_FLAG,
-- typ.SUPP_COST_REG_FLAG,
decode(typ.DIR_REG_REV_COL_FLAG,'N','COST_ONLY','COST_AND_REV_SAME')
INTO
x_fp_pref_code
FROM pa_ci_types_b typ,pa_control_items ci
WHERE ci.ci_type_id = typ.ci_type_id
and ci.Project_Id = p_project_id
and ci.ci_id = p_ci_id
and typ.impact_budget_type_code in('DIRECT_COST_ENTRY');
SELECT Nvl(fpo.margin_derived_from_code, 'B'),
bv.plan_processing_code,
bv.request_id
INTO x_margin_derived_from_code,
x_ci_ver_plan_prc_code,
x_request_id
FROM pa_proj_fp_options fpo,
pa_budget_versions bv
WHERE bv.ci_id = p_ci_id
AND bv.version_type in ('COST','ALL')
AND fpo.fin_plan_version_id = bv.budget_version_id
AND bv.project_id = p_project_id;
SELECT bv.plan_processing_code,
bv.request_id
INTO x_ci_ver_plan_prc_code,
x_request_id
FROM pa_budget_versions bv
WHERE bv.ci_id = p_ci_id
AND bv.version_type = 'REVENUE'
AND bv.project_id = p_project_id;
SELECT Nvl(fpo.margin_derived_from_code, 'B'),
bv.plan_processing_code,
bv.request_id
INTO x_margin_derived_from_code,
x_ci_ver_plan_prc_code,
x_request_id
FROM pa_proj_fp_options fpo,
pa_budget_versions bv
WHERE fpo.project_id = p_project_id
AND bv.current_working_flag = 'Y'
AND fpo.fin_plan_version_id = bv.budget_version_id
AND bv.approved_cost_plan_type_flag = 'Y';
SELECT Nvl(fpo.margin_derived_from_code, 'B'),
bv.plan_processing_code,
bv.request_id
INTO x_margin_derived_from_code,
x_ci_ver_plan_prc_code,
x_request_id
FROM pa_proj_fp_options fpo,
pa_budget_versions bv
WHERE bv.ci_id = p_ci_id
AND bv.version_type in ('COST','ALL')
AND fpo.fin_plan_version_id = bv.budget_version_id
AND bv.project_id = p_project_id;
SELECT bv.plan_processing_code,
bv.request_id
INTO x_ci_ver_plan_prc_code,
x_request_id
FROM pa_budget_versions bv
WHERE bv.ci_id = p_ci_id
AND bv.version_type = 'REVENUE'
AND bv.project_id = p_project_id;
SELECT Nvl(fpo.margin_derived_from_code, 'B'),
bv.plan_processing_code,
bv.request_id
INTO x_margin_derived_from_code,
x_ci_ver_plan_prc_code,
x_request_id
FROM pa_proj_fp_options fpo,
pa_budget_versions bv
WHERE fpo.project_id = p_project_id
AND bv.current_working_flag = 'Y'
AND fpo.fin_plan_version_id = bv.budget_version_id
AND bv.approved_cost_plan_type_flag = 'Y';
SELECT Fin_Plan_Preference_Code,
Report_Labor_Hrs_From_Code,
Fin_Plan_Type_Id
INTO
x_fp_pref_code,
x_report_labor_hours_code,
x_fin_plan_type_id_cost
FROM Pa_Proj_Fp_Options WHERE
Project_Id = p_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y';
SELECT Fin_Plan_Type_Id INTO x_fin_plan_type_id_rev
FROM Pa_Proj_Fp_Options WHERE
Project_Id = p_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y';
SELECT NVL(Allow_Rev_Budget_Entry_Flag ,'N'),
NVL(Allow_Cost_Budget_Entry_Flag,'N') INTO
l_rev_budget_flag,
l_cost_budget_flag FROM
Pa_Projects_All p, Pa_Project_Types_All pt WHERE
p.project_id = p_project_id AND
p.project_type = pt.project_type AND
-- MOAC changes
-- removing the nvl from org_id.
-- NVL(p.org_id,-99) = NVL(pt.org_id,-99);
SELECT 'Y' INTO l_fp_allowed_flag FROM
Pa_Projects_All p,
Pa_Project_Types_All pt
WHERE
p.Project_Id = p_project_id AND
p.Project_Type = pt.Project_Type AND
-- MOAC changes
-- removing the nvl from org_id.
-- NVL(p.org_id,-99) = NVL(pt.org_id,-99) AND
p.org_id = pt.org_id AND
( NVL(pt.ALLOW_COST_BUDGET_ENTRY_FLAG,'N') = 'Y' OR
NVL(pt.ALLOW_REV_BUDGET_ENTRY_FLAG,'N') = 'Y' );
SELECT
pacitl.name into x_ci_type_name
FROM pa_control_items paci, pa_ci_types_tl pacitl
WHERE
paci.ci_id = p_ci_id
and paci.ci_type_id = pacitl.ci_type_id
and pacitl.language = userenv('lang');
SELECT pg.agreement_num,
pg.amount,
pg.agreement_currency_code
INTO x_agreement_num,
x_agreement_amount,
x_agreement_currency_code
FROM pa_agreements_all pg, pa_budget_versions bv
WHERE
bv.project_id = p_project_id
and bv.ci_id = p_ci_id
and bv.agreement_id = pg.agreement_id
and bv.version_type in ('REVENUE','ALL'); -- Raja FP M Change Bug 3619687
SELECT
po.fin_plan_preference_code,
po.plan_in_multi_curr_flag,
DECODE
(po.fin_plan_preference_code,
'COST_ONLY',po.cost_fin_plan_level_code,
'REVENUE_ONLY',po.revenue_fin_plan_level_code,
'COST_AND_REV_SAME',po.all_fin_plan_level_code,
'COST_AND_REV_SEP',
DECODE
(bv.version_type,
'COST',po.cost_fin_plan_level_code,
'REVENUE',po.revenue_fin_plan_level_code
)
),
DECODE
(po.fin_plan_preference_code,
'COST_ONLY',po.cost_resource_list_id,
'REVENUE_ONLY',po.revenue_resource_list_id,
'COST_AND_REV_SAME',po.all_resource_list_id,
'COST_AND_REV_SEP',
DECODE
(bv.version_type,
'COST',po.cost_resource_list_id,
'REVENUE',po.revenue_resource_list_id
)
),
DECODE
(po.fin_plan_preference_code,
'COST_ONLY',po.cost_time_phased_code,
'REVENUE_ONLY',po.revenue_time_phased_code,
'COST_AND_REV_SAME',po.all_time_phased_code,
'COST_AND_REV_SEP',
DECODE
(bv.version_type,
'COST',po.cost_time_phased_code,
'REVENUE',po.revenue_time_phased_code
)
),
bv.version_type,
bv.ci_id
INTO
x_fin_plan_pref_code,
x_multi_curr_flag,
x_fin_plan_level_code,
x_resource_list_id,
x_time_phased_code,
x_version_type,
x_ci_id
FROM pa_budget_versions bv, pa_proj_fp_options po
WHERE
bv.budget_version_id = p_budget_version_id
AND po.fin_plan_version_id = bv.budget_version_id
AND po.project_id = p_project_id;
SELECT
NVL(pr.uncategorized_flag,'N'),
NVL(pr.group_resource_type_id,0)
INTO
x_uncategorized_flag,
x_group_res_type_id
FROM pa_resource_lists_all_bg pr
WHERE pr.resource_list_id = x_resource_list_id;
SELECT name
INTO l_targ_pt_name
FROM pa_fin_plan_types_vl fin,
pa_budget_versions pbv
WHERE fin.fin_plan_type_id = pbv.fin_plan_type_id
AND pbv.budget_version_id= l_t_version_id;
SELECT ci_number
INTO l_src_ci_number
FROM pa_control_items
WHERE ci_id=l_s_ci_id;
SELECT NVL(agreement_id,-99), NVL(approved_rev_plan_type_flag,'N')
INTO l_s_agreement_id,l_s_app_rev_flag
FROM pa_budget_versions
where budget_version_id = l_s_version_id;
SELECT NVL(agreement_id,-100), NVL(approved_rev_plan_type_flag,'N')
INTO l_t_agreement_id,l_t_app_rev_flag
FROM pa_budget_versions
where budget_version_id = l_t_version_id;
select ci_id into l_token_ci_id from
pa_budget_versions where
budget_version_id = l_s_version_id;
select ci.ci_number,cit.short_name into
l_ci_number,l_ci_type_name from
pa_control_items ci,
pa_ci_types_tl cit
where ci.ci_id = l_token_ci_id and
cit.ci_type_id = ci.ci_type_id and
cit.language = userenv('LANG');
select DECODE(bv.version_type,'REVENUE',revenue_fin_plan_level_code,
'ALL',all_fin_plan_level_code,null)
from pa_budget_versions bv,
pa_proj_fp_options po,
pa_ci_impacts pci,
pa_projects_all ppa
where bv.project_id = p_project_id
and bv.approved_rev_plan_type_flag = 'Y'
and po.project_id = bv.project_id
and po.fin_plan_type_id = bv.fin_plan_type_id
and po.fin_plan_version_id = bv.budget_version_id
and po.fin_plan_option_level_code = 'PLAN_VERSION'
and pci.ci_id = bv.ci_id
and pci.impact_type_code = 'FINPLAN_REVENUE'
and pci.status_code = 'CI_IMPACT_PENDING'
and ppa.project_id = bv.project_id
and ppa.baseline_funding_flag = 'Y';
select 'Y' from dual
where exists (
select 'x'
from pa_summary_project_fundings fu,
pa_budget_versions bv
where fu.agreement_id = p_agreement_id
and bv.project_id = fu.project_id
and bv.agreement_id = p_agreement_id);
p_msg_name => 'PA_FP_AGR_CI_NO_DELETE');
select 'Y' from dual
where exists (
select 'x'
from pa_summary_project_fundings fu,
pa_budget_versions bv
where fu.agreement_id = p_agreement_id
and bv.project_id = fu.project_id
and bv.agreement_id = p_agreement_id);
SELECT parent_task_id,
top_task_id
FROM pa_tasks
WHERE task_id = c_impacted_task_id;
SELECT COUNT(1)
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id
AND pra.project_id = p_project_id
AND pra.project_assignment_id = -1
AND (pra.task_id=c_task_id OR pra.task_id=p_impacted_task_id));*/
SELECT COUNT(1)
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id
AND pra.project_id = p_project_id
AND pra.project_assignment_id = -1
AND EXISTS ( SELECT 1
FROM pa_tasks t
WHERE t.parent_task_id = p_impacted_task_id
START WITH t.task_id = pra.task_id
CONNECT BY PRIOR t.parent_task_id = t.task_id));
SELECT COUNT(1)
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id
AND pra.project_id = p_project_id
AND pra.project_assignment_id = -1
AND (pra.task_id = p_impacted_task_id or pra.task_id = c_top_task_id));-- /*UT*/fe.top_task_id = c_top_task_id)
SELECT COUNT(1)
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_resource_assignments pra,
pa_tasks t
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id
AND pra.project_id = p_project_id
AND pra.project_assignment_id = -1
AND t.task_id=pra.task_id
AND t.top_task_id = p_impacted_task_id);
SELECT count(1)
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id
AND pra.project_id = p_project_id
AND pra.project_assignment_id = -1);
SELECT proj_fp_options_id,
DECODE(p_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_resource_list_id) resource_list_id,
DECODE(p_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_fin_plan_level_code) plan_type_planning_level
INTO l_proj_fp_options_id,
l_resource_list_id,
l_plan_type_planning_level
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND fin_plan_version_id = l_ci_apprv_cw_bv_id;
SELECT count(task_id)
INTO l_count
FROM pa_tasks
WHERE parent_task_id = p_impacted_task_id;
SELECT a.Agreement_Id INTO x_agreement_id FROM
Pa_Agreements_All a,
Pa_Summary_Project_Fundings spf
WHERE
a.agreement_num = p_agreement_number AND
a.agreement_id = spf.agreement_id AND
spf.project_id = p_project_id AND
NVL(spf.total_unbaselined_amount,0) > 0 AND
ROWNUM < 2;
SELECT NVL(Baseline_Funding_Flag,'N') INTO
l_baseline_Funding_flag
FROM Pa_Projects_All
WHERE
Project_Id = p_project_id;
SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
WHERE
Project_Id = p_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y' ) ;
SELECT fin_plan_preference_code INTO
l_fp_pref_code
FROM pa_proj_fp_options
WHERE
project_id = p_project_id AND
Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y' ) ;
SELECT
bv.version_number,
bv.version_name,
bv.version_type,
NVL(bv.approved_cost_plan_type_flag,'N'),
NVL(bv.approved_rev_plan_type_flag,'N'),
bv.fin_plan_type_id,
patl.name,
pftb.plan_class_code plan_class_code
INTO
x_version_number,
x_version_name,
x_version_type,
x_approved_cost_flag,
x_approved_rev_flag,
x_fin_plan_type_id,
x_plan_type_name,
x_plan_class_code
FROM pa_budget_versions bv, pa_fin_plan_types_tl patl,pa_fin_plan_types_b pftb
WHERE bv.budget_version_id = p_budget_version_id
and bv.project_id = p_project_id
and bv.fin_plan_type_id = patl.fin_plan_type_id
and patl.fin_plan_type_id = pftb.fin_plan_type_id
and patl.language = userenv('LANG');
SELECT project_currency_code
INTO
x_project_currency_code
FROM
Pa_Projects_All
WHERE
project_Id = p_project_id;
SELECT SUM(NVL(total_baselined_amount,0)) INTO
l_funding_amount
FROM
pa_summary_project_fundings
WHERE
project_id = p_project_id AND
agreement_id = p_agreement_id;
Select budget_version_id
from pa_budget_versions bv,
pa_proj_fp_options pfo
where bv.project_id = p_project_id
and bv.fin_plan_type_id = p_fin_plan_type_id
and bv.version_type = p_version_type
and bv.current_working_flag = 'Y'
and bv.ci_id IS NULL
and pfo.project_id = p_project_id
and pfo.fin_plan_type_id= p_fin_plan_type_id
and pfo.fin_plan_version_id IS NULL
and ((DECODE(p_version_type,'COST',bv.approved_cost_plan_type_flag,
'REVENUE',bv.approved_rev_plan_type_flag,
'N') = 'Y')
OR
(p_version_type='ALL' and
pfo.approved_cost_plan_type_flag ='Y' and
pfo.approved_rev_plan_type_flag ='N' and
pfo.fin_plan_preference_code='COST_AND_REV_SAME')
OR
(p_version_type='ALL' and
pfo.approved_cost_plan_type_flag ='N' and
pfo.approved_rev_plan_type_flag ='Y' and
pfo.fin_plan_preference_code='COST_AND_REV_SAME') /* bug 7584903 */
OR
(bv.approved_cost_plan_type_flag = 'Y' and
bv.approved_rev_plan_type_flag = 'Y')) ;
select name,
NVL(Baseline_Funding_Flag,'N')
into l_project_name,
l_baseline_Funding_flag
from pa_projects_all
where project_id = p_project_id;
select name
into l_plan_name
from pa_fin_plan_types_tl
where fin_plan_type_id = p_fin_plan_type_id
and language = userenv('LANG');
select meaning
into l_version_type
from pa_lookups
where lookup_type = 'FIN_PLAN_VER_TYPE'
and lookup_code = p_version_type;
select DECODE(pos.fin_plan_preference_code,'COST_ONLY', pos.cost_time_phased_code,
'REVENUE_ONLY',pos.revenue_time_phased_code,
pos.all_time_phased_code) source_time_phased_code,
DECODE(pot.fin_plan_preference_code,'COST_ONLY', pot.cost_time_phased_code,
'REVENUE_ONLY',pot.revenue_time_phased_code,
pot.all_time_phased_code) target_time_phased_code
from pa_proj_fp_options pos
,pa_proj_fp_options pot
where pos.fin_plan_version_id = p_source_bv_id
and pot.fin_plan_version_id = p_target_bv_id;
is used to validate the LOV selection in
the Advanced Display Options Page
*/
PROCEDURE CHECK_PLAN_VERSION_NAME_OR_ID
(
p_project_id IN NUMBER,
p_budget_version_name IN VARCHAR2,
p_fin_plan_type_id IN NUMBER,
p_version_type IN VARCHAR2,
x_no_of_bv_versions OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_budget_version_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
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
-- Local Variable Declaration
l_debug_mode VARCHAR2(30);
SELECT
count(*)
INTO
x_no_of_bv_versions
FROM pa_budget_versions bv
WHERE bv.version_name = p_budget_version_name
and bv.project_id = p_project_id
and bv.fin_plan_type_id = p_fin_plan_type_id
and bv.version_type = p_version_type;
SELECT
bv.budget_version_id
INTO
x_budget_version_id
FROM pa_budget_versions bv
WHERE bv.version_name = p_budget_version_name
and bv.project_id = p_project_id
and bv.fin_plan_type_id = p_fin_plan_type_id
and bv.version_type = p_version_type;
select nvl(baseline_funding_flag ,'N') into
l_baseline_funding_flag from
pa_projects_all where
project_id = p_project_id;
select ci.ci_number,cit.short_name into
l_ci_number,l_ci_type_name from
pa_control_items ci,
pa_ci_types_tl cit
where ci.ci_id = p_s_ci_id and
cit.ci_type_id = ci.ci_type_id and
cit.language = userenv('LANG');
SELECT fin_plan_type_id
INTO l_ci_aprv_plan_type_id
FROM pa_proj_fp_options po
WHERE
po.project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND DECODE
(p_source_version_type,
'COST',po.approved_cost_plan_type_flag,
'REVENUE',po.approved_rev_plan_type_flag
) = 'Y';
SELECT
DECODE
(p_source_version_type,
'COST',po.cost_fin_plan_level_code,
'REVENUE',po.revenue_fin_plan_level_code,
'ALL',po.all_fin_plan_level_code
),
DECODE
(p_source_version_type,
'COST',po.cost_resource_list_id,
'REVENUE',po.revenue_resource_list_id,
'ALL',po.all_resource_list_id
),
DECODE
(p_source_version_type,
'COST',po.cost_time_phased_code,
'REVENUE',po.revenue_time_phased_code,
'ALL',po.all_time_phased_code
)
INTO
l_target_plan_level_code,
l_target_resource_list_id,
l_target_time_phased_code
FROM pa_proj_fp_options po
WHERE
po.project_id = p_project_id
AND fin_plan_type_id = l_ci_aprv_plan_type_id
AND fin_plan_version_id = l_ci_aprv_cw_bv_id;
SELECT fin_plan_type_id
INTO l_ci_aprv_plan_type_id
FROM pa_proj_fp_options po
WHERE
po.project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND ( NVL(po.approved_rev_plan_type_flag,'N') = 'Y'
OR NVL(po.approved_cost_plan_type_flag,'N') = 'Y' );
SELECT
DECODE
(p_source_version_type,
'COST',po.cost_fin_plan_level_code,
'REVENUE',po.revenue_fin_plan_level_code,
'ALL',po.all_fin_plan_level_code
),
DECODE
(p_source_version_type,
'COST',po.cost_resource_list_id,
'REVENUE',po.revenue_resource_list_id,
'ALL',po.all_resource_list_id
),
DECODE
(p_source_version_type,
'COST',po.cost_time_phased_code,
'REVENUE',po.revenue_time_phased_code,
'ALL',po.all_time_phased_code
)
INTO
l_target_plan_level_code,
l_target_resource_list_id,
l_target_time_phased_code
FROM pa_proj_fp_options po
WHERE
po.project_id = p_project_id
AND fin_plan_type_id = l_ci_aprv_plan_type_id
AND fin_plan_version_id = l_ci_aprv_cw_bv_id;
/* select po.revenue_fin_plan_level_code,
po.revenue_time_phased_code,
po.revenue_resource_list_id into
l_s_bv_fp_level_code,
l_s_bv_time_phased_code,
l_s_bv_resource_list_id
FROM pa_proj_fp_options po,
pa_budget_versions bv
WHERE
bv.project_id = p_project_id and
bv.ci_id = p_s_ci_id and
bv.version_type = 'REVENUE' and
po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
po.fin_plan_type_id = bv.fin_plan_type_id;
the above select is not required as the target values
can be directly copied from the source version. */
l_target_plan_level_code := p_source_plan_level_code;
SELECT
count(*)
INTO
l_no_of_bv_versions
FROM pa_budget_versions bv
WHERE bv.project_id = p_project_id
and bv.ci_id = p_ci_id;
SELECT budget_version_id
,version_type
,fin_plan_type_id
FROM pa_budget_versions
WHERE project_id = p_project_id AND
nvl(ci_id,-1) = p_ci_id;
l_source_id_tbl.DELETE;
This api Identifies whether the impact can be updated to implemented
or not. Included for bug 2681589.
==================================================================*/
--Bug 3550073. Included x_upd_cost_impact_allowed and x_upd_rev_impact_allowed
PROCEDURE FP_CI_VALIDATE_UPDATE_IMPACT
(
p_project_id IN pa_budget_versions.project_id%TYPE,
p_ci_id IN pa_control_items.ci_id%TYPE,
p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_upd_rev_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
AS
l_msg_count NUMBER := 0;
SELECT 'Y'
FROM pa_fp_merged_ctrl_items
WHERE ci_id =c_ci_id
AND project_id=p_project_id
AND plan_version_id=p_target_version_id
AND ci_plan_version_id=NVL(p_source_version_id,ci_plan_version_id)
AND version_type=c_version_type;
pa_debug.set_err_stack('Pa_Fp_Control_Items_Utils.FP_CI_VALIDATE_UPDATE_IMPACT');
SELECT NVL(approved_cost_plan_type_flag,'N'),
NVL(approved_rev_plan_type_flag,'N')
INTO
l_approved_cost_flag,
l_approved_rev_flag
FROM pa_budget_versions
WHERE budget_version_id = p_target_version_id
AND project_id = p_project_id;
SELECT ci_id
INTO l_ci_id
FROM pa_budget_versions
WHERE budget_version_id = p_source_version_id
AND project_id = p_project_id;
pa_debug.g_err_stage:= 'Exiting FP_CI_VALIDATE_UPDATE_IMPACT';
,p_procedure_name => 'FP_CI_VALIDATE_UPDATE_IMPACT'
,p_error_text => x_msg_data);
END FP_CI_VALIDATE_UPDATE_IMPACT;
select fpe.task_id,
fpe.top_task_id,
t.task_name task_name,
t.task_number task_number,
fpe.resource_planning_level,
PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(fpe.task_id)
seq_no,
nvl(fpe.top_task_planning_level,'LOWEST')
top_task_planning_level
from
pa_fp_elements fpe,
pa_tasks t where
fpe.proj_fp_options_id = c_fp_opt_id and
fpe.element_type = c_ver_type and
fpe.task_id = t.task_id and
fpe.resource_list_member_id = 0 and
fpe.plannable_flag = 'Y'
order by seq_no;
select o.proj_fp_options_id,
bv.version_type into
l_source_fp_opt_id,
l_source_ver_type
from pa_proj_fp_options o,
pa_budget_versions bv
where
bv.budget_version_id = p_s_budget_version_id and
bv.fin_plan_type_id = o.fin_plan_type_id and
o.project_id = p_project_id and
o.fin_plan_version_id = bv.budget_version_id;
select o.proj_fp_options_id ,
bv.version_type into
l_target_fp_opt_id,
l_target_ver_type
from pa_proj_fp_options o,
pa_budget_versions bv
where
bv.budget_version_id = p_t_budget_version_id and
bv.fin_plan_type_id = o.fin_plan_type_id and
o.project_id = p_project_id and
o.fin_plan_version_id = bv.budget_version_id;
select ra.resource_list_member_id into l_prj_rlm_id from
pa_resource_assignments ra
where
ra.budget_version_id = p_s_budget_version_id and
nvl(ra.resource_assignment_type,'USER_ENTERED') =
'USER_ENTERED' and
rownum < 2;
select decode(parent_member_id,null,'G','R') into
l_source_plan_level from pa_resource_list_members
where resource_list_member_id = l_prj_rlm_id;
select ra.resource_list_member_id into l_prj_rlm_id from
pa_resource_assignments ra
where
ra.budget_version_id = p_t_budget_version_id and
nvl(ra.resource_assignment_type,'USER_ENTERED') =
'USER_ENTERED' and
rownum < 2;
select decode(parent_member_id,null,'G','R') into
l_target_plan_level from pa_resource_list_members
where resource_list_member_id = l_prj_rlm_id;
select ra.resource_list_member_id into l_prj_rlm_id from
pa_resource_assignments ra
where
ra.budget_version_id = p_t_budget_version_id and
nvl(ra.resource_assignment_type,'USER_ENTERED') =
'USER_ENTERED' and
rownum < 2;
select decode(parent_member_id,null,'G','R') into
l_target_prj_plan_level from pa_resource_list_members
where resource_list_member_id = l_prj_rlm_id;
select nvl(fpe.top_task_planning_level,'LOWEST')
into l_top_task_plan_level
from pa_fp_elements fpe
where
fpe.proj_fp_options_id = l_target_fp_opt_id and
fpe.element_type = l_target_ver_type and
fpe.task_id = c1_rec.task_id and
fpe.resource_list_member_id = 0 and
fpe.plannable_flag = 'Y';
select nvl(fpe.top_task_planning_level,'LOWEST')
into l_top_task_plan_level
from pa_fp_elements fpe
where
fpe.proj_fp_options_id = l_target_fp_opt_id and
fpe.element_type = l_target_ver_type and
fpe.task_id = c1_rec.top_task_id and
fpe.resource_list_member_id = 0 and
fpe.plannable_flag = 'Y';
select task_name,task_number into
l_target_task_name,
l_target_task_number
from pa_Tasks where task_id = l_target_task_id;
select resource_planning_level into l_target_plan_level
from pa_fp_elements
where proj_fp_options_id = l_target_fp_opt_id
and element_type = l_target_ver_type
and resource_list_member_id = 0
and plannable_flag = 'Y'
and task_id = l_target_task_id;
select fpe.task_id task_id,
fpe.top_task_id top_task_id,
t.task_name task_name,
t.task_number task_number,
fpe.resource_planning_level resource_planning_level,
PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(fpe.task_id)
seq_no,
NVL(fpe.top_task_planning_level,'LOWEST')
top_task_planning_level
from
pa_fp_elements fpe,
pa_tasks t where
fpe.proj_fp_options_id = c_fp_opt_id and
fpe.element_type = c_ver_type and
fpe.task_id = t.task_id and
fpe.resource_list_member_id = 0 and
fpe.plannable_flag = 'Y'
order by seq_no;
,x_msg => 'selecting source version fp option details'
,x_log_level => 5);
select o.proj_fp_options_id,
bv.version_type into
l_source_fp_opt_id,
l_source_ver_type
from pa_proj_fp_options o,
pa_budget_versions bv
where
bv.budget_version_id = p_s_budget_version_id and
bv.fin_plan_type_id = o.fin_plan_type_id and
o.project_id = p_project_id and
o.fin_plan_version_id = bv.budget_version_id;
,x_msg => 'selecting target version fp option details'
,x_log_level => 5);
select o.proj_fp_options_id ,
bv.version_type into
l_target_fp_opt_id,
l_target_ver_type
from pa_proj_fp_options o,
pa_budget_versions bv
where
bv.budget_version_id = p_t_budget_version_id and
bv.fin_plan_type_id = o.fin_plan_type_id and
o.project_id = p_project_id and
o.fin_plan_version_id = bv.budget_version_id;
select nvl(fpe.top_task_planning_level,'LOWEST')
into l_top_task_plan_level
from pa_fp_elements fpe
where
fpe.proj_fp_options_id = l_target_fp_opt_id and
fpe.element_type = l_target_ver_type and
fpe.task_id = tsk_rec.task_id and
fpe.resource_list_member_id = 0 and
fpe.plannable_flag = 'Y';
select nvl(fpe.top_task_planning_level,'LOWEST')
into l_top_task_plan_level
from pa_fp_elements fpe
where
fpe.proj_fp_options_id = l_target_fp_opt_id and
fpe.element_type = l_target_ver_type and
fpe.task_id = tsk_rec.top_task_id and
fpe.resource_list_member_id = 0 and
fpe.plannable_flag = 'Y';
select nvl(fpe.top_task_planning_level,'LOWEST')
into l_top_task_plan_level
from pa_fp_elements fpe
where
fpe.proj_fp_options_id = l_target_fp_opt_id and
fpe.element_type = l_target_ver_type and
fpe.task_id = tsk_rec.top_task_id and
fpe.resource_list_member_id = 0;
select task_name,task_number into
l_target_task_name,
l_target_task_number
from pa_Tasks where task_id = tsk_rec.task_id;
SELECT 'Y'
INTO l_return
FROM dual
WHERE
EXISTS (SELECT 'X'
FROM pa_pt_co_impl_statuses ptco,
pa_ci_statuses_v pcs
WHERE ptco.ci_type_id = p_ci_type_id
AND ptco.version_type = p_version_type
AND ptco.ci_type_id = pcs.ci_type_id
AND ptco.status_code=pcs.project_status_code
AND pcs.project_system_status_code <> PA_FP_CONSTANTS_PKG.G_SYS_STATUS_APPROVED
AND ptco.fin_plan_type_id=p_fin_plan_type_id);
SELECT cost_impact_flag, revenue_impact_flag
INTO l_cost_impact_flag, l_rev_impact_flag
FROM PA_CI_TYPES_W_FINPLAN_V
WHERE ci_type_id = p_ci_type_id;
SELECT MEANING,to_number(LOOKUP_CODE)
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'PA_FP_CI_PLAN_SUMMARY'
ORDER BY to_number(LOOKUP_CODE);
SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'PA_FP_CI_PLAN_REFERENCE'
ORDER BY to_number(LOOKUP_CODE);
SELECT fin_plan_type_id,
fin_plan_preference_code,
report_labor_hrs_from_code,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
FROM pa_proj_fp_options
WHERE project_id = c_project_id
AND (approved_cost_plan_type_flag = 'Y' or approved_rev_plan_type_flag = 'Y')
AND fin_plan_option_level_code = 'PLAN_TYPE';
SELECT nvl(sum(decode(pbv.version_type,
c_report_version_type,
nvl(pbv.labor_quantity,0),0)),0),
nvl(sum(decode(pbv.version_type,
c_report_version_type,
nvl(pbv.equipment_quantity,0),0)),0),
nvl(sum(decode(pbv.fin_plan_type_id,
c_ac_fin_plan_type_id,
decode(c_margin_derived_from_code,
'B',nvl(pbv.total_project_burdened_cost,0),
nvl(pbv.total_project_raw_cost,0))
,0)),0) as cost,
nvl(sum(decode(pbv.fin_plan_type_id,
c_ar_fin_plan_type_id,nvl(pbv.total_project_revenue,0),0)),0)
FROM pa_budget_versions pbv
WHERE pbv.project_id = c_project_id
AND pbv.ci_id is null
AND nvl(pbv.current_original_flag,'N') = 'Y'
--Below 2 lines commented for bug 5278200
AND pbv.fin_plan_type_id in (c_ac_fin_plan_type_id,c_ar_fin_plan_type_id)
--AND pbv.fin_plan_type_id is not null
--AND (pbv.approved_cost_plan_type_flag = 'Y' or pbv.approved_rev_plan_type_flag = 'Y')
AND pbv.budget_status_code = 'B';
SELECT nvl(sum(decode(pbv.version_type,
c_report_version_type,
nvl(pbv.labor_quantity,0),0)),0),
nvl(sum(decode(pbv.version_type,
c_report_version_type,
nvl(pbv.equipment_quantity,0),0)),0),
nvl(sum(decode(pbv.fin_plan_type_id,
c_ac_fin_plan_type_id,
decode(c_margin_derived_from_code,
'B',nvl(pbv.total_project_burdened_cost,0),
nvl(pbv.total_project_raw_cost,0))
,0)),0) as cost,
nvl(sum(decode(pbv.fin_plan_type_id,
c_ar_fin_plan_type_id,
nvl(pbv.total_project_revenue,0),0)),0)
FROM pa_budget_versions pbv
WHERE pbv.project_id = c_project_id
AND pbv.ci_id is null
AND nvl(pbv.current_flag,'N') = 'Y'
--Below 2 lines commented for bug 5278200
AND pbv.fin_plan_type_id in (c_ac_fin_plan_type_id,c_ar_fin_plan_type_id)
--AND pbv.fin_plan_type_id is not null
--AND (pbv.approved_cost_plan_type_flag = 'Y' or pbv.approved_rev_plan_type_flag = 'Y')
AND pbv.budget_status_code = 'B';
SELECT nvl(sum(decode(pbv.version_type,
c_report_version_type,
nvl(pbv.labor_quantity,0),0)),0),
nvl(sum(decode(pbv.version_type,
c_report_version_type,
nvl(pbv.equipment_quantity,0),0)),0),
nvl(sum(decode(pbv.fin_plan_type_id,
c_ac_fin_plan_type_id,
decode(c_margin_derived_from_code,
'B',nvl(pbv.total_project_burdened_cost,0),
nvl(pbv.total_project_raw_cost,0)),0)),0) as cost,
nvl(sum(decode(pbv.fin_plan_type_id,
c_ar_fin_plan_type_id,
nvl(pbv.total_project_revenue,0),0)),0)
FROM pa_budget_versions pbv
WHERE pbv.project_id = c_project_id
AND pbv.ci_id is null
AND nvl(pbv.current_working_flag,'N') = 'Y'
--Below 2 lines commented for bug 5278200
AND pbv.fin_plan_type_id in (c_ac_fin_plan_type_id,c_ar_fin_plan_type_id);
Select nvl(sum(decode(c_report_version_type,
'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
,0)),0),
nvl(sum(decode(c_report_version_type,
'COST', decode(merge.version_type, 'COST',nvl(merge.impl_equipment_quantity,0),0),
'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_equipment_quantity,0),0),
'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_equipment_quantity,0),0)
,0)),0),
-- Raja report_version_type should be taken into consideration
-- nvl(sum(nvl(merge.impl_quantity,0)),0),
-- nvl(sum(nvl(merge.impl_equipment_quantity,0)),0),
nvl(sum(decode(c_margin_derived_from_code,
'B',nvl(merge.impl_proj_burdened_cost,0),
nvl(merge.impl_proj_raw_cost,0))),0) as cost,
nvl(sum(nvl(merge.impl_proj_revenue,0)),0)
from pa_fp_merged_ctrl_items merge,
pa_budget_versions pbv
where pbv.project_id = c_project_id
and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
and pbv.current_working_flag = 'Y'
and merge.project_id = c_project_id
and merge.plan_version_id = pbv.budget_version_id
and pbv.ci_id is null
-- and pbv.budget_status_code in ('S','W') -- Bug#3815378
-- Added by Raja, filter all the ci versions included/copied in current baseline version
and not exists(select 1
from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
where pbv1.project_id = c_project_id
and pbv1.fin_plan_type_id in (c_ac_fin_plan_type_id,
c_ar_fin_plan_type_id)
and pbv1.budget_status_code = 'B'
and pbv1.current_flag = 'Y'
and pbv1.ci_id is null
and merge1.project_id = c_project_id
and merge1.plan_version_id = pbv1.budget_version_id
and merge1.ci_plan_version_id = merge.ci_plan_version_id);
Select nvl(sum(decode(c_report_version_type,
'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
,0)),0),
nvl(sum(decode(c_report_version_type,
'COST', decode(merge.version_type, 'COST',nvl(merge.impl_equipment_quantity,0),0),
'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_equipment_quantity,0),0),
'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_equipment_quantity,0),0)
,0)),0),
nvl(sum(decode(c_margin_derived_from_code,
'B',nvl(merge.impl_proj_burdened_cost,0),
nvl(merge.impl_proj_raw_cost,0))),0) as cost,
nvl(sum(decode(pbv.fin_plan_type_id,
c_ar_fin_plan_type_id,nvl(merge.impl_proj_revenue,0),
0)),0)
from pa_fp_merged_ctrl_items merge,
pa_budget_versions pbv
where pbv.project_id = c_project_id
and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
and pbv.current_working_flag = 'Y'
and merge.project_id = c_project_id
and merge.plan_version_id = pbv.budget_version_id
and pbv.ci_id is null
and merge.inclusion_method_code in ('MANUAL','AUTOMATIC');
Select nvl(sum(decode(c_report_version_type,
'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
,0)),0),
nvl(sum(decode(c_report_version_type,
'COST', decode(merge.version_type, 'COST',nvl(merge.impl_equipment_quantity,0),0),
'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_equipment_quantity,0),0),
'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_equipment_quantity,0),0)
,0)),0),
/** Raja report_version_type should be taken into consideration
nvl(sum(nvl(merge.impl_quantity,0)),0),
nvl(sum(nvl(merge.impl_equipment_quantity,0)),0),
**/
nvl(sum(decode(c_margin_derived_from_code,'B'
,nvl(merge.impl_proj_burdened_cost,0)
,nvl(merge.impl_proj_raw_cost,0))),0) as cost,
nvl(sum(decode(pbv.fin_plan_type_id,
c_ar_fin_plan_type_id,nvl(merge.impl_proj_revenue,0),
0)),0)
from pa_fp_merged_ctrl_items merge,
pa_budget_versions pbv
where merge.plan_version_id = pbv.budget_version_id
and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
and pbv.current_flag = 'Y'
and pbv.project_id = c_project_id
and merge.project_id = c_project_id
and pbv.ci_id is null
and pbv.budget_status_code = 'B'
and merge.inclusion_method_code = 'COPIED' -- Bug 3882985
/* Raja filter all the change orders that have been included/copied in the original baseline version */
and not exists(select 1
from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
where pbv1.project_id = c_project_id
and pbv1.fin_plan_type_id in (c_ac_fin_plan_type_id,
c_ar_fin_plan_type_id)
and pbv1.current_original_flag = 'Y'
and pbv1.ci_id is null
and pbv1.budget_status_code = 'B'
and merge1.project_id = c_project_id
and merge1.plan_version_id = pbv1.budget_version_id
-- Raja review and pbv.budget_version_id = pbv1.budget_version_id);
SELECT nvl(sum(pfca.people_effort),0),
nvl(sum(pfca.equipment_effort),0),
nvl(sum(decode(c_margin_derived_from_code,'B',
nvl(pfca.burdened_cost,0),
nvl(pfca.raw_cost,0))),0) as cost,
nvl(sum(nvl(pfca.revenue,0)),0)
from PA_FP_ELIGIBLE_CI_V pfca
where pfca.project_id = c_project_id
and pfca.PROJECT_SYSTEM_STATUS_CODE = c_system_status_code;
SELECT (cost_query.people_effort + revenue_query.people_effort),
(cost_query.equipment_effort + revenue_query.equipment_effort),
(cost_query.cost + revenue_query.cost),
(cost_query.revenue + revenue_query.revenue)
from
(SELECT nvl(sum(decode(pfca.ci_version_type,
c_report_version_type, pfca.people_effort,
0)),0) as people_effort,
nvl(sum(decode(pfca.ci_version_type,
c_report_version_type, pfca.equipment_effort,
0)),0) as equipment_effort,
nvl(sum(decode(c_margin_derived_from_code,'B',
nvl(pfca.burdened_cost,0),
nvl(pfca.raw_cost,0))),0) as cost,
0 as revenue
from (SELECT PBV.PROJECT_ID AS PROJECT_ID
,PBV.BUDGET_VERSION_ID AS CI_VERSION_ID
,PBV.VERSION_TYPE AS CI_VERSION_TYPE
,PCI.CI_ID AS CI_ID
,PCI.SUMMARY AS SUMMARY
,PCS.PROJECT_SYSTEM_STATUS_CODE AS PROJECT_SYSTEM_STATUS_CODE
,PBV.LABOR_QUANTITY AS PEOPLE_EFFORT
,PBV.EQUIPMENT_QUANTITY AS EQUIPMENT_EFFORT
,PBV.TOTAL_PROJECT_RAW_COST AS RAW_COST
,PBV.TOTAL_PROJECT_BURDENED_COST AS BURDENED_COST
FROM pa_budget_versions pbv
,pa_control_items pci
,pa_project_statuses pcs
,pa_ci_types_vl pct
WHERE PBV.CI_ID = PCI.CI_ID
AND PBV.PROJECT_ID = PCI.PROJECT_ID
AND PCI.STATUS_CODE = PCS.PROJECT_STATUS_CODE
AND pct.ci_type_id = pci.ci_type_id
AND pct.ci_type_class_code = 'CHANGE_ORDER') pfca
where pfca.project_id = c_project_id
and pfca.project_system_status_code = c_system_status_code
and pfca.ci_version_type in ('COST', 'ALL')
and not exists(select 1
from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
where merge1.project_id = c_project_id
and merge1.ci_id = pfca.ci_id
and merge1.ci_plan_version_id = pfca.ci_version_id
and merge1.version_type = 'COST'
-- and pbv1.budget_status_code in ('S','W') -- Bug#3815378
and pbv1.current_working_flag = 'Y'
and pbv1.budget_version_id = merge1.plan_version_id
and pbv1.ci_id is null
and pbv1.approved_cost_plan_type_flag = 'Y')) cost_query,
-- Modified revenue Query 3902490 to add calls to get_labor_qty_partial and get_equip_qty_partial
-- for deriving quantity when rev_partially_impl_flag is passed as Y.
(SELECT nvl(sum(nvl(decode(c_report_version_type,'REVENUE',
decode(pfca.REV_PARTIALLY_IMPL_FLAG,'Y',PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial
(pfca.CI_VERSION_TYPE
,c_appr_rev_cw_version_id
,pfca.CI_VERSION_ID
,pfca.people_effort
,'REVENUE')
,pfca.people_effort),
0),
0)
),0) as people_effort,
nvl(sum(nvl(decode(c_report_version_type,'REVENUE',
decode(pfca.REV_PARTIALLY_IMPL_FLAG,'Y',PA_FP_CONTROL_ITEMS_UTILS.get_equip_qty_partial
(pfca.CI_VERSION_TYPE
,c_appr_rev_cw_version_id
,pfca.CI_VERSION_ID
,pfca.equipment_effort
,'REVENUE')
,pfca.equipment_effort),
0),
0)
),0) as equipment_effort,
0 as cost,
nvl(sum(nvl(decode(pfca.REV_PARTIALLY_IMPL_FLAG,'Y',PA_FP_CONTROL_ITEMS_UTILS.get_pc_revenue_partial
(pfca.CI_VERSION_TYPE
,c_appr_rev_cw_version_id
,pfca.CI_VERSION_ID
,pfca.revenue
,'REVENUE')
,pfca.revenue),0)),0) as revenue
from (SELECT PBV.PROJECT_ID AS PROJECT_ID
,PBV.BUDGET_VERSION_ID AS CI_VERSION_ID
,PBV.VERSION_TYPE AS CI_VERSION_TYPE
,PCI.CI_ID AS CI_ID
,PCS.PROJECT_SYSTEM_STATUS_CODE AS PROJECT_SYSTEM_STATUS_CODE
,PBV.LABOR_QUANTITY AS PEOPLE_EFFORT
,PBV.EQUIPMENT_QUANTITY AS EQUIPMENT_EFFORT
,PBV.TOTAL_PROJECT_REVENUE AS REVENUE
,nvl(PBV.REV_PARTIALLY_IMPL_FLAG,'N') AS REV_PARTIALLY_IMPL_FLAG
FROM pa_budget_versions pbv
,pa_control_items pci
,pa_project_statuses pcs
,pa_ci_types_vl pct
WHERE PBV.CI_ID = PCI.CI_ID
AND PBV.PROJECT_ID = PCI.PROJECT_ID
AND PCI.STATUS_CODE = PCS.PROJECT_STATUS_CODE
AND pct.ci_type_id = pci.ci_type_id
AND pct.ci_type_class_code = 'CHANGE_ORDER') pfca
where pfca.project_id = c_project_id
and pfca.project_system_status_code = c_system_status_code
and pfca.ci_version_type in ('REVENUE', 'ALL')
and (pfca.REV_PARTIALLY_IMPL_FLAG = 'Y' OR
not exists(select 1
from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
where merge1.project_id = c_project_id
and merge1.ci_id = pfca.ci_id
and merge1.ci_plan_version_id = pfca.ci_version_id
and merge1.version_type = 'REVENUE'
-- and pbv1.budget_status_code in ('S','W') -- Bug#3815378
and pbv1.current_working_flag = 'Y'
and pbv1.budget_version_id = merge1.plan_version_id
and pbv1.ci_id is null
and pbv1.approved_rev_plan_type_flag = 'Y'))) revenue_query;
SELECT BUDGET_VERSION_ID,VERSION_TYPE
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_STATUS_CODE = 'B'
AND PROJECT_ID = c_project_id
AND CI_ID IS NULL
AND NVL(CURRENT_ORIGINAL_FLAG,'N') = 'Y'
AND FIN_PLAN_TYPE_ID = C_FIN_PLAN_TYPE_ID
AND version_type = c_version_type;
SELECT BUDGET_VERSION_ID,VERSION_TYPE
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_STATUS_CODE = 'B'
AND PROJECT_ID = c_project_id
AND CI_ID IS NULL
AND NVL(CURRENT_FLAG,'N') = 'Y'
AND FIN_PLAN_TYPE_ID = C_FIN_PLAN_TYPE_ID
AND version_type = c_version_type;
SELECT BUDGET_VERSION_ID,VERSION_TYPE
FROM PA_BUDGET_VERSIONS
WHERE PROJECT_ID = c_project_id
AND CI_ID IS NULL
AND NVL(CURRENT_WORKING_FLAG,'N') = 'Y'
-- AND BUDGET_STATUS_CODE in ('S','W') -- Bug#3815378
AND FIN_PLAN_TYPE_ID = C_FIN_PLAN_TYPE_ID
AND version_type = c_version_type;
SELECT project_currency_code
INTO x_project_currency_code
FROM Pa_Projects_All
WHERE project_Id = p_project_id;
SELECT nvl(MARGIN_DERIVED_FROM_CODE,'B')
INTO l_margin_derived_from_code
FROM PA_PROJ_FP_OPTIONS
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND approved_cost_plan_type_flag = 'Y';
SELECT A.BUDGET_VERSION_ID
INTO l_appr_rev_cw_version_id
FROM PA_BUDGET_VERSIONS A
WHERE A.PROJECT_ID = p_project_id
AND A.VERSION_TYPE IN('ALL', 'REVENUE')
AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
AND A.CURRENT_WORKING_FLAG = 'Y'
AND A.CI_ID IS NULL;
SELECT A.BUDGET_VERSION_ID
INTO l_appr_rev_cw_version_id
FROM PA_BUDGET_VERSIONS A
WHERE A.PROJECT_ID = p_project_id
AND A.VERSION_TYPE IN('ALL', 'REVENUE')
--AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
AND A.FIN_PLAN_TYPE_ID IN(select FIN_PLAN_TYPE_ID from
PA_BUDGET_VERSIONS where
budget_version_id in
(p_cost_version_id,p_revenue_version_id))
AND A.CURRENT_WORKING_FLAG = 'Y'
AND A.CI_ID IS NULL;
SELECT fin_plan_type_id,
fin_plan_preference_code,
report_labor_hrs_from_code
INTO
l_fin_plan_type_id_tbl(1),
l_fin_plan_preference_code_tbl(1),
l_rep_lab_from_code_tbl(1)
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND fin_plan_type_id in(select FIN_PLAN_TYPE_ID from
PA_BUDGET_VERSIONS where
budget_version_id in (p_cost_version_id,p_revenue_version_id));
select lookup_code, meaning
from pa_lookupus
where lookup_type = 'PA_FP_CI_PLAN_SUMMARY'
order by to_number(lookup_code);
Description for the lookup type has been updated as well saying that sayijng the code is
used as number internally.*/
IF l_continue_flag = 'Y' THEN
FOR i IN l_lookup_code_tbl.FIRST .. l_lookup_code_tbl.LAST LOOP
x_url_tbl.extend(1);
pa_debug.g_err_stage:='Inserting Data for Adjustments from Prior versions';
pa_debug.g_err_stage:='Inserting Data for Adjustments';
SELECT fin_plan_preference_code,
report_labor_hrs_from_code
INTO l_pref_code_for_pt_of_ver,
l_report_labor_hrs_code
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = l_fin_plan_type_id
AND fin_plan_option_level_code = 'PLAN_TYPE';
SELECT VERSION_TYPE
INTO l_source_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT VERSION_TYPE
INTO l_target_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_budget_version_id;
SELECT labor_quantity
INTO l_labor_quantity
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS
WHERE CI_PLAN_VERSION_ID = p_ci_version_id
AND PLAN_VERSION_ID = p_budget_version_id
AND VERSION_TYPE = 'COST');
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS A
WHERE A.CI_PLAN_VERSION_ID = P_CI_VERSION_ID
AND A.PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND A.VERSION_TYPE = 'REVENUE');
SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_QUANTITY
INTO l_revenue_partial_flag, l_partial_quantity
FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
AND A.PLAN_VERSION_ID = p_budget_version_id
AND A.VERSION_TYPE = 'REVENUE'
AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
SELECT A.BUDGET_VERSION_ID
INTO l_appr_rev_cw_version_id
FROM PA_BUDGET_VERSIONS A
WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
AND A.VERSION_TYPE IN ('REVENUE', 'ALL')
-- Raja review A.VERSION_TYPE = 'REVENUE'
AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
AND CURRENT_WORKING_FLAG = 'Y'
AND A.CI_ID IS NULL;
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS
WHERE CI_PLAN_VERSION_ID = p_ci_version_id
AND PLAN_VERSION_ID = p_budget_version_id
AND VERSION_TYPE = 'COST');
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS A
WHERE A.CI_PLAN_VERSION_ID = P_CI_VERSION_ID
AND A.PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND A.VERSION_TYPE = 'REVENUE');
SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_QUANTITY
INTO l_revenue_partial_flag, l_partial_quantity
FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
AND A.PLAN_VERSION_ID = p_budget_version_id
AND A.VERSION_TYPE = 'REVENUE'
AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
SELECT VERSION_TYPE
INTO l_source_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT VERSION_TYPE
INTO l_target_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_budget_version_id;
SELECT EQUIPMENT_QUANTITY
INTO l_equip_quantity
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS
WHERE CI_PLAN_VERSION_ID = p_ci_version_id
AND PLAN_VERSION_ID = p_budget_version_id
AND VERSION_TYPE = 'COST');
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS A
WHERE A.CI_PLAN_VERSION_ID = P_CI_VERSION_ID
AND A.PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND A.VERSION_TYPE = 'REVENUE');
SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_EQUIPMENT_QUANTITY
INTO l_revenue_partial_flag, l_partial_quantity
FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
AND A.PLAN_VERSION_ID = p_budget_version_id
AND A.VERSION_TYPE = 'REVENUE'
AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
SELECT A.BUDGET_VERSION_ID
INTO l_appr_rev_cw_version_id
FROM PA_BUDGET_VERSIONS A
WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
AND A.VERSION_TYPE = 'REVENUE'
AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
AND CURRENT_WORKING_FLAG = 'Y'
AND A.CI_ID IS NULL;
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS
WHERE CI_PLAN_VERSION_ID = p_ci_version_id
AND PLAN_VERSION_ID = p_budget_version_id
AND VERSION_TYPE = 'COST');
SELECT 'Y'
INTO l_impl_qty_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS A
WHERE A.CI_PLAN_VERSION_ID = P_CI_VERSION_ID
AND A.PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND A.VERSION_TYPE = 'REVENUE');
SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_EQUIPMENT_QUANTITY
INTO l_revenue_partial_flag, l_partial_quantity
FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
AND A.PLAN_VERSION_ID = p_budget_version_id
AND A.VERSION_TYPE = 'REVENUE'
AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
SELECT NVL(approved_rev_plan_type_flag,'N')
INTO l_ci_app_rev_flag
FROM pa_budget_versions
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT TOTAL_PROJECT_REVENUE
INTO l_revenue
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS A
WHERE A.CI_PLAN_VERSION_ID = P_CI_VERSION_ID
AND A.PLAN_VERSION_ID = P_BUDGET_VERSION_ID
AND A.VERSION_TYPE = 'REVENUE');
SELECT VERSION_TYPE
INTO l_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT VERSION_TYPE
INTO l_budget_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_budget_version_id;
SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_PROJ_REVENUE
INTO l_revenue_partial_flag, l_partial_revenue
FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
AND A.PLAN_VERSION_ID = p_budget_version_id
AND A.VERSION_TYPE = 'REVENUE'
AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
SELECT A.BUDGET_VERSION_ID
INTO l_appr_rev_cw_version_id
FROM PA_BUDGET_VERSIONS A
WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
AND A.VERSION_TYPE IN('ALL', 'REVENUE')
-- Raja review AND A.VERSION_TYPE IN('REVENUE')
AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
AND CURRENT_WORKING_FLAG = 'Y'
AND A.CI_ID IS NULL;
SELECT nvl(MARGIN_DERIVED_FROM_CODE,'B')
INTO l_margin_derived_from_code
FROM PA_PROJ_FP_OPTIONS a
WHERE a.FIN_PLAN_VERSION_ID = p_ci_version_id
AND a.fin_plan_option_level_code = 'PLAN_VERSION';
SELECT VERSION_TYPE
INTO l_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_ci_version_id;
SELECT VERSION_TYPE
INTO l_budget_version_type
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_budget_version_id;
Select decode(l_margin_derived_from_code,
'R',total_project_raw_cost
,total_project_burdened_cost)
INTO l_cost
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = p_budget_version_id;
Select l_cost
into l_return_cost
from dual
where not exists (Select 1
from pa_fp_merged_ctrl_items
where plan_version_id = p_budget_version_id
and version_type = 'COST'
and ci_plan_version_id = p_ci_version_id);
SELECT MEANING,to_number(LOOKUP_CODE)
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'PA_FP_CI_NOT_INCLUDED'
ORDER BY to_number(LOOKUP_CODE);
select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
pfca.CI_VERSION_TYPE,
c_budget_version_id,
pfca.CI_VERSION_ID,
pfca.people_effort,
pfca.PT_CT_VERSION_TYPE),0)),0) as people_effort
,nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_equip_qty_partial(
pfca.CI_VERSION_TYPE,
c_budget_version_id,
pfca.CI_VERSION_ID,
pfca.equipment_effort,
pfca.PT_CT_VERSION_TYPE),0)),0) as equipment_effort
,nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_pc_cost(
pfca.CI_VERSION_TYPE,
c_budget_version_id,
pfca.CI_VERSION_ID,
pfca.RAW_COST,
pfca.BURDENED_COST,
pfca.PT_CT_VERSION_TYPE),0)),0) as cost
,nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_pc_revenue_partial(
pfca.CI_VERSION_TYPE,
c_budget_version_id,
pfca.CI_VERSION_ID,
pfca.REVENUE,
pfca.PT_CT_VERSION_TYPE),0)),0) as revenue
from PA_FP_ELIGIBLE_CI_V pfca
where pfca.project_id = c_project_id
and pfca.fin_plan_type_id = c_fin_plan_type_id
and pfca.CI_VERSION_TYPE <>
decode(c_version_type,'COST','REVENUE','REVENUE','COST','ALL','-99')
and decode (pfca.CI_VERSION_TYPE,'ALL',
pfca.PT_CT_VERSION_TYPE,pfca.CI_VERSION_TYPE) = pfca.PT_CT_VERSION_TYPE
-- 3572880 below join necessary when target version type is COST/REV and ci version type
-- is ALL to avoid REV/COST impacts
and pfca.PT_CT_VERSION_TYPE
= decode (c_version_type, 'ALL', pfca.PT_CT_VERSION_TYPE, c_version_type)
and pfca.PROJECT_SYSTEM_STATUS_CODE = c_system_status_code
and ( pfca.REV_PARTIALLY_IMPL_FLAG='Y'
or (pfca.ci_version_type='ALL'
AND DECODE(c_version_type,'ALL',2,1) > (SELECT COUNT(*)
FROM pa_fp_merged_ctrl_items merge1
where merge1.ci_plan_version_id = pfca.ci_version_id
and merge1.plan_version_id = c_budget_version_id
and merge1.project_id = c_project_id))
or (pfca.ci_version_type <> 'ALL'
AND not exists (Select 'X'
from pa_fp_merged_ctrl_items merge2
where merge2.ci_plan_version_id = pfca.ci_version_id
and merge2.plan_version_id = c_budget_version_id
and merge2.version_type = pfca.ci_version_type
and merge2.project_id = c_project_id)));
Select version_type
into l_version_type
from pa_budget_versions
where budget_version_id = p_budget_version_id;
Select fin_plan_type_id
into l_fin_plan_type_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select lookup_code, meaning
from pa_lookupus
where lookup_type = 'PA_FP_CI_NOT_INCLUDED'
order by to_number(lookup_code);
Description for the lookup type has been updated as well saying that
the code is used as number internally.*/
FOR i IN l_lookup_code_tbl.FIRST .. l_lookup_code_tbl.LAST LOOP
x_equipment_hours_tbl.extend(1);
SELECT impact_type_code
FROM pa_ci_impacts
WHERE ci_id = p_ci_id
AND impact_type_code IN ('FINPLAN',
'FINPLAN_COST',
'FINPLAN_REVENUE')
ORDER BY impact_type_code;
SELECT ci_type_id
INTO l_ci_type_id
FROM pa_control_items
WHERE ci_id = p_ci_id
AND project_id = p_project_id;
SELECT cost_impact_flag, revenue_impact_flag
INTO l_cost_impact_flag, l_rev_impact_flag
FROM pa_ci_types_w_finplan_v
WHERE ci_type_id = l_ci_type_id;
SELECT budget_version_id,
Version_type,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
FROM pa_budget_versions
WHERE ci_id =p_ci_id;
X_select_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- The flag which indicates whether the select flag can be checked by default or not --File.Sql.39 bug 4440895
X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
X_partially_impl_flag OUT NOCOPY VARCHAR2, -- A flag that indicates whether a partially implemented CO exists for the plan type or not. Possible values are Y/N --File.Sql.39 bug 4440895
X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
X_autobaseline_project OUT NOCOPY VARCHAR2, -- This flag will be set to Y if the project is enabled for autobaseline --File.Sql.39 bug 4440895
x_disable_baseline_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Disable Baseline Checkbox Flag -- 3735309 --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2, -- Indicates the exit status of the API --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2, -- Indicates the error occurred --File.Sql.39 bug 4440895
X_msg_count OUT NOCOPY NUMBER) -- Indicates the number of error messages --File.Sql.39 bug 4440895
IS
-- All plan types attached to the project(excluding work plan and org forecast plan types)
CURSOR c_plan_types_attached IS
SELECT fin.name
,pfo.fin_plan_type_id
,NVL(pfo.approved_cost_plan_type_flag,'N') approved_cost_plan_type_flag
,NVL(pfo.approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
,fin.plan_class_code
,pfo.fin_plan_preference_code
FROM pa_fin_plan_types_vl fin,
pa_proj_fp_options pfo
WHERE pfo.project_id = p_project_id
AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
AND pfo.fin_plan_type_id = fin.fin_plan_type_id
AND nvl(fin.use_for_workplan_flag,'N') <> 'Y'
And nvl(fin.fin_plan_type_code,'NON-ORG') <> 'ORG_FORECAST'
ORDER BY fin.name;
X_select_flag_tbl := SYSTEM.pa_varchar2_1_tbl_type();
SELECT ci_type_id,status_code
INTO l_ci_type_id,l_status_code
FROM pa_control_items
WHERE ci_id=p_ci_id;
X_select_flag_tbl.extend;
select impl_default_flag
INTO x_select_flag_tbl(i)
from pa_pt_co_impl_statuses
WHERE fin_plan_type_id = c_plan_type_rec.fin_plan_type_id
AND ci_type_id = l_ci_type_id
AND ROWNUM = 1 ;
x_select_flag_tbl(i) := 'N';
SELECT 'Y'
FROM dual
WHERE
EXISTS (SELECT 'X'
FROM pa_pt_co_impl_statuses popt
WHERE popt.status_code =c_status_code
AND popt.version_type = c_impact_type_code
AND popt.fin_plan_type_id = p_fin_plan_type_id
AND popt.ci_type_id = c_ci_type_id);
SELECT 'X'
FROM pa_fp_merged_Ctrl_items
WHERE ci_id=p_ci_id
AND plan_version_id = c_version_id
AND ci_plan_version_id = c_ci_version_id
AND project_id = p_project_id
AND version_type=c_version_type;
SELECT status_code, ci_type_id
INTO l_status_code, l_ci_type_id
FROM pa_control_items
WHERE ci_id=p_ci_id;
SELECT NVL(approved_cost_plan_type_flag,'N'),
NVL(approved_rev_plan_type_flag,'N'),
fin_plan_preference_code
INTO l_t_app_cost_flag,
l_t_app_rev_flag,
l_t_pt_pref_code
FROM pa_proj_fp_options
WHERE project_id=p_project_id
AND fin_plan_type_id=p_fin_plan_type_id
AND fin_plan_version_id IS NULL;
SELECT version_type,current_working_flag
INTO l_version_type,l_current_working_flag
FROM pa_budget_versions
WHERE budget_version_id=p_targ_bv_id;
SELECT approved_rev_plan_type_flag
INTO l_app_rev_plan_type_flag
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND fin_plan_type_id = p_fin_plan_type_id;
SELECT nvl(rev_partially_impl_flag,'N')
INTO x_partially_impl_flag
FROM pa_budget_versions pbv
WHERE pbv.budget_Version_id = nvl(l_ci_rev_version_id,l_ci_all_version_id);
/* SELECT nvl(rev_partially_impl_flag,'N')
INTO x_partially_impl_flag
FROM pa_budget_versions pbv
WHERE pbv.budget_Version_id = decode( nvl(l_rev_budget_version_id,-1),
-1,l_all_budget_version_id,
l_rev_budget_version_id); */
SELECT 'Y'
INTO l_rev_impl_full
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS
WHERE CI_PLAN_VERSION_ID = nvl(l_ci_rev_version_id,l_ci_all_version_id)
AND version_type = 'REVENUE'
AND PLAN_VERSION_ID = decode(nvl(l_rev_budget_version_id,-1),-1,l_all_budget_version_id
,l_rev_budget_version_id));
SELECT nvl(enable_partial_impl_flag,'N')
INTO x_partially_impl_flag
FROM PA_FIN_PLAN_TYPES_B
WHERE fin_plan_type_id = p_fin_plan_type_id;
SELECT 'Y'
INTO l_status_implementable
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM pa_pt_co_impl_statuses
WHERE status_code=p_status_code);
SELECT 'x'
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM pa_fp_merged_ctrl_items
WHERE ci_id=p_ci_id
AND plan_version_id=c_app_cw_ver_id
AND ci_plan_version_id=c_ci_version_id
AND version_type=c_version_type);
SELECT project_currency_code
INTO x_project_currency_code
FROM pa_projects_all
WHERE project_id=p_project_id;
SELECT pps.project_system_status_code
INTO x_ci_status_code
FROM pa_control_items pci,
pa_project_statuses pps
WHERE pci.ci_id=p_ci_id
AND pps.project_status_code=pci.status_code;
SELECT NVL(pfoc.margin_derived_from_code,'B'),
DECODE(nvl(pfoc.plan_in_multi_curr_flag,'N'),
'N',DECODE(nvl(pfor.plan_in_multi_curr_flag,'N'),
'N','N',
'Y'),
'Y'),
NVL(pfoc.cost_resource_list_id,NVL(pfoc.all_resource_list_id,pfoc.revenue_resource_list_id)),
NVL(pfoc.revenue_resource_list_id,NVL(pfoc.all_resource_list_id,pfoc.cost_resource_list_id))
INTO x_report_cost_using,
x_impact_in_mc_flag,
l_ci_resource_list_id1,
l_ci_resource_list_id2
FROM pa_proj_fp_options pfoc,
pa_proj_fp_options pfor
WHERE pfoc.fin_plan_version_id = NVL(l_cost_ci_version_id,NVL(l_all_ci_version_id,l_rev_ci_version_id))
AND pfor.fin_plan_version_id = NVL(l_rev_ci_version_id,NVL(l_all_ci_version_id,l_cost_ci_version_id));
SELECT DECODE(pfo.fin_plan_preference_code,
'REVENUE_ONLY',NULL,
NVL(pfo.margin_derived_from_code,'B')),
NVL(pfo.plan_in_multi_curr_flag,'N'),
NVL(pfo.cost_resource_list_id,NVL(pfo.all_resource_list_id,pfo.revenue_resource_list_id)),
NVL(pfo.cost_resource_list_id,NVL(pfo.all_resource_list_id,pfo.revenue_resource_list_id))
INTO x_report_cost_using,
x_impact_in_mc_flag,
l_ci_resource_list_id1,
l_ci_resource_list_id2
FROM pa_proj_fp_options pfo
WHERE pfo.fin_plan_version_id=NVL(l_rev_ci_version_id,NVL(l_all_ci_version_id,l_cost_ci_version_id)) ;
SELECT pbvc.resource_list_id,
pbvr.resource_list_id
INTO l_targ_resource_list_id1 ,
l_targ_resource_list_id2
FROM pa_budget_versions pbvc,
pa_budget_versions pbvr
WHERE pbvc.budget_version_id=nvl(x_app_cost_cw_ver_id,x_app_rev_cw_ver_id)
AND pbvr.budget_version_id=nvl(x_app_rev_cw_ver_id,x_app_cost_cw_ver_id) ;
SELECT pbv.version_type,
pbv.resource_list_id,
pbv.resource_list_id,
fin.plan_class_code
INTO x_targ_version_type,
l_targ_resource_list_id1 ,
l_targ_resource_list_id2,
x_plan_class_code
FROM pa_budget_versions pbv,
pa_fin_plan_types_b fin
WHERE budget_version_id=p_budget_version_id
AND fin.fin_plan_type_id=pbv.fin_plan_type_id;
SELECT ci.ci_number,ct.name
INTO x_ci_number,x_ci_type
FROM pa_control_items ci,pa_ci_types_vl ct
WHERE ci_id=p_ci_id
AND ci.ci_type_id=ct.ci_type_id;
SELECT budget_version_id,
approved_cost_plan_type_flag,
approved_rev_plan_type_flag
FROM pa_budget_versions pbv
WHERE pbv.project_id=p_project_id
AND pbv.ci_id IS NULL
AND pbv.fin_plan_type_id IS NOT NULL
AND nvl(pbv.wp_version_flag,'N')='N'
AND (pbv.approved_cost_plan_type_flag = 'Y' OR
pbv.approved_rev_plan_type_flag = 'Y' )
AND pbv.current_working_flag = 'Y';
SELECT budget_version_id
INTO l_ci_rev_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND ci_id = p_ci_id
AND Nvl(approved_rev_plan_type_flag, 'N')= 'Y'
AND version_type IN ('REVENUE','ALL');
SELECT budget_version_id
INTO l_cw_bv_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND Nvl(approved_rev_plan_type_flag, 'N')= 'Y'
AND Nvl(current_working_flag, 'N') = 'Y';
SELECT Nvl(impl_agr_revenue,0)
INTO l_impl_agr_rev_amt
FROM pa_fp_merged_ctrl_items
WHERE project_id = p_project_id
AND ci_id = p_ci_id
AND plan_version_id = l_cw_bv_id
AND ci_plan_version_id = l_ci_rev_version_id
AND version_type = 'REVENUE';
SELECT 'Y'
INTO l_is_editplanned_enabled
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_CI_TYPES_V
WHERE CI_TYPE_ID = (SELECT CI_TYPE_ID FROM PA_CONTROL_ITEMS WHERE CI_ID = p_ci_id)
AND (IMPACT_BUDGET_TYPE_CODE = 'EDIT_PLANNED_AMOUNTS' OR
/* Added OR contion Bug 13960686 */
IMPACT_BUDGET_TYPE_CODE = 'DIRECT_COST_ENTRY' )
);/* Changed the Query for E&C 12.1.3 */
SELECT 'Y'
INTO l_is_merged
FROM DUAL
WHERE EXISTS (SELECT 1
FROM PA_FP_MERGED_CTRL_ITEMS
WHERE CI_ID = p_ci_id);
SELECT rac.txn_currency_code,
rac.budget_version_id
INTO x_txn_currency_code,
x_budget_version_id
FROM pa_resource_asgn_curr rac,
pa_budget_versions bv
WHERE bv.project_id = p_project_id
AND bv.ci_id = p_ci_id
AND bv.version_type IN ('REVENUE','ALL')
AND rac.budget_version_id = bv.budget_version_id
AND rownum=1;
FUNCTION validate_fp_ci_type_delete (p_ci_type_id IN pa_ci_types_b.ci_type_id%TYPE)
RETURN VARCHAR2
IS
l_debug_mode VARCHAR2(30);
l_module_name VARCHAR2(30) := 'validate_fp_ci_type_delete';
is_delete_allowed VARCHAR2(1) := 'Y';
pa_debug.g_err_stage:='validate_fp_ci_type_delete - pa_fp_control_items_utils ';
pa_debug.g_err_stage:='Entering validate_fp_ci_type_delete';
p_value1 => 'PAFPCIUB.validate_fp_ci_type_delete');
pa_debug.g_err_stage:='Checkin if delete allowed';
SELECT 'N'
INTO is_delete_allowed
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM pa_pt_co_impl_statuses
WHERE ci_type_id = p_ci_type_id);
RETURN is_delete_allowed;
pa_debug.g_err_stage:='Value returned: ' || is_delete_allowed;
pa_debug.g_err_stage:='Leaving validate_fp_ci_type_delete';
RETURN is_delete_allowed;
,p_procedure_name => 'validate_fp_ci_type_delete');
END validate_fp_ci_type_delete;
SELECT nvl(pfo.approved_rev_plan_type_flag,'N'),
nvl(pfo.fin_plan_preference_code,'N')
INTO l_app_cost_pt_rev_flag,
l_app_cost_pt_pref_code
FROM pa_proj_fp_options pfo
WHERE pfo.project_id = p_project_id
AND pfo.fin_plan_version_id IS NULL
AND pfo.approved_cost_plan_type_flag ='Y';