[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select bv.project_id,
DECODE(p_view_currency_type,
'PROJ', pa.project_currency_code,
'PROJFUNC', pa.projfunc_currency_code,
'TXN')
into l_project_id,
l_projfunc_currency_code
from pa_budget_versions bv,
pa_projects_all pa
where bv.budget_version_id = p_orgfcst_version_id and
bv.project_id = pa.project_id;
select nvl(org_id,-99)
into l_org_id
from pa_projects_all
where project_id = l_project_id;
select fin_plan_start_date, fin_plan_end_date, fin_plan_type_id
-- default_amount_type_code,
-- default_amount_subtype_code
into ll_plan_start_date,ll_plan_end_date, l_fin_plan_type_id
-- l_default_amount_type_code,
-- l_default_amount_subtype_code
from pa_proj_fp_options
where fin_plan_version_id = p_orgfcst_version_id;
select proj_fp_options_id,
margin_derived_from_code,
report_labor_hrs_from_code
into x_plan_fp_options_id, -- OUTPUT: x_plan_fp_options_id
l_margin_derived_from_code,
l_labor_hours_from_code
from pa_proj_fp_options
where project_id = l_project_id and
fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code='PLAN_TYPE';
select po.cost_amount_set_id,
po.revenue_amount_set_id,
po.all_amount_set_id,
bv.version_type,
-- po.factor_by_code,
po.fin_plan_preference_code
into l_cost_amount_set_id,
l_rev_amount_set_id,
l_all_amount_set_id,
l_version_type,
-- x_factor_by_code,
l_plan_pref_code
from pa_budget_versions bv,
pa_proj_fp_options po
where bv.budget_version_id = p_orgfcst_version_id and
bv.fin_plan_type_id = po.fin_plan_type_id and
po.project_id = l_project_id and
po.fin_plan_option_level_code = 'PLAN_TYPE';
select nvl(po.factor_by_code, 1)
into x_factor_by_code -- OUTPUT: x_factor_by_code
from pa_proj_fp_options po
where po.project_id = l_project_id and
po.fin_plan_option_level_code = 'PROJECT';
select nvl(cost_qty_flag,'N'),
nvl(raw_cost_flag, 'N'),
nvl(burdened_cost_flag, 'N'),
nvl(revenue_flag, 'N'),
'N',
'N'
into l_display_quantity,
l_display_rawcost,
l_display_burdcost,
l_display_revenue,
l_display_margin,
l_display_marginpct
from pa_fin_plan_amount_sets
where fin_plan_amount_set_id = l_cost_amount_set_id;
select nvl(revenue_qty_flag, 'N'),
nvl(raw_cost_flag, 'N'),
nvl(burdened_cost_flag, 'N'),
nvl(revenue_flag, 'N'),
'N',
'N'
into l_display_quantity,
l_display_rawcost,
l_display_burdcost,
l_display_revenue,
l_display_margin,
l_display_marginpct
from pa_fin_plan_amount_sets
where fin_plan_amount_set_id = l_rev_amount_set_id;
select nvl(all_qty_flag, 'N'),
nvl(raw_cost_flag, 'N'),
nvl(burdened_cost_flag, 'N'),
nvl(revenue_flag, 'N'),
'Y',
'Y'
into l_display_quantity,
l_display_rawcost,
l_display_burdcost,
l_display_revenue,
l_display_margin,
l_display_marginpct
from pa_fin_plan_amount_sets
where fin_plan_amount_set_id = l_all_amount_set_id;
select DECODE(cost_as.cost_qty_flag,
'Y', 'Y',
DECODE(rev_as.revenue_qty_flag,
'Y', 'Y',
'N')),
nvl(cost_as.raw_cost_flag, 'N'),
nvl(cost_as.burdened_cost_flag, 'N'),
nvl(rev_as.revenue_flag, 'N'),
'Y',
'Y'
into l_display_quantity,
l_display_rawcost,
l_display_burdcost,
l_display_revenue,
l_display_margin,
l_display_marginpct
from pa_fin_plan_amount_sets cost_as,
pa_fin_plan_amount_sets rev_as
where cost_as.fin_plan_amount_set_id = l_cost_amount_set_id and
rev_as.fin_plan_amount_set_id = l_rev_amount_set_id;
select approved_rev_plan_type_flag
into x_ar_flag -- OUTPUT: x_ar_flag
from pa_proj_fp_options
where project_id = l_project_id and
fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code='PLAN_TYPE';
select DECODE(version_type,
'REVENUE', 'R',
'COST', 'C',
null),
nvl(approved_rev_plan_type_flag, 'N')
into l_cost_or_revenue,
x_ar_flag -- OUTPUT: x_ar_flag
from pa_budget_versions
where budget_version_id = p_orgfcst_version_id;
select pp.plan_period_type,
pp.period1_start_date,
pp.period_profile_id
into ll_plan_period_type,
pp_plan_start_date,
l_period_profile_id
from pa_proj_period_profiles pp,
pa_budget_versions pbv
where pbv.budget_version_id = p_orgfcst_version_id
and pbv.period_profile_id = pp.period_profile_id;
select min(start_date) into pp_plan_start_date from
pa_budget_lines
where budget_version_id = p_orgfcst_version_id;
select decode(nvl(cost_time_phased_code,
nvl(revenue_time_phased_code, all_time_phased_code)), 'G', 'GL', 'P', 'PA', 'N')
into ll_plan_period_type -- OUTPUT: ll_plan_period_type
from pa_proj_fp_options
where project_id = l_project_id and
fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code='PLAN_TYPE';
select min(start_date), max(end_date)
into ll_plan_start_date, ll_plan_end_date
from pa_budget_lines
where resource_assignment_id in (select
resource_assignment_id from
pa_budget_versions where budget_version_id = p_orgfcst_version_id);
select min(start_date), max(end_date)
into ll_plan_start_date, ll_plan_end_date
from pa_budget_lines
where budget_version_id = p_orgfcst_version_id;
select gp.START_DATE INTO l_start_date
from gl_periods gp,
gl_sets_of_books gsb,
pa_implementations_all pia,
pa_projects_all ppa
where
gp.period_set_name = gsb.period_set_name
and gp.period_type = gsb.accounted_period_type
and gsb.set_of_books_id = pia.set_of_books_id
and pia.org_id = ppa.org_id
and ppa.project_id = l_project_id
and sysdate between gp.START_DATE and gp.END_DATE;
select gp.START_DATE INTO l_start_date
from gl_periods gp,
pa_implementations_all pia,
pa_projects_all ppa
where
gp.period_set_name = pia.period_set_name
and gp.period_type = pia.pa_period_type
and pia.org_id = ppa.org_id
and ppa.project_id = l_project_id
and sysdate between gp.START_DATE and gp.END_DATE;
-- delete residual data in the temp tables
DELETE from PA_FIN_VP_AMTS_VIEW_TMP;
DELETE from PA_FIN_VP_PDS_VIEW_TMP;
SELECT NVL(rl.uncategorized_flag, 'N')
INTO l_uncategorized_flag
FROM pa_budget_versions bv,
pa_resource_lists_all_bg rl
WHERE bv.budget_version_id = p_orgfcst_version_id AND
bv.resource_list_id = rl.resource_list_id;
DELETE FROM pa_fin_vp_amts_view_tmp
WHERE project_id=l_project_id AND
resource_list_member_id > 0;
DELETE FROM pa_fin_vp_pds_view_tmp tmp1
WHERE tmp1.project_id=l_project_id AND
tmp1.resource_list_member_id = 0
and exists (select 1 from pa_fin_vp_pds_view_tmp tmp2
where tmp2.task_id=tmp1.task_id
and tmp2.resource_list_member_id>0);
select nvl(all_time_phased_code, 'N')
into x_time_phase_code -- OUTPUT: x_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID;
select plan_processing_code,
request_id
into l_plan_processing_code,
l_request_id
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID;
select nvl(cost_time_phased_code, 'N')
into x_time_phase_code -- OUTPUT: x_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select plan_processing_code,
request_id
into l_plan_processing_code,
l_request_id
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select nvl(revenue_time_phased_code, 'N')
into x_time_phase_code -- OUTPUT: x_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select plan_processing_code,
request_id
into l_plan_processing_code,
l_request_id
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select nvl(cost_time_phased_code, 'N')
into l_cost_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select nvl(revenue_time_phased_code, 'N')
into l_rev_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select plan_processing_code,
request_id
into l_plan_processing_code,
l_request_id
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select plan_processing_code,
request_id
into l_plan_processing_code2,
l_request_id
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select record_version_number, -- OUTPUT: x_cost_rv_number
budget_status_code -- OUTPUT: x_cost_budget_status_code
into x_cost_rv_number,
x_cost_budget_status_code
from pa_budget_versions
--4/16/03 where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select record_version_number, -- OUTPUT: x_rev_rv_number
budget_status_code -- OUTPUT: x_rev_budget_status_code
into x_rev_rv_number,
x_rev_budget_status_code
from pa_budget_versions
-- where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select bv.budget_version_id,
po.proj_fp_options_id,
NVL(po.plan_in_multi_curr_flag, 'N') as plan_in_multi_curr_flag
from pa_budget_versions bv,
pa_proj_fp_options po
where bv.project_id = p_project_id and
bv.ci_id = p_ci_id and
bv.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code='PLAN_VERSION';
select projfunc_currency_code
into x_view_currency_code
from pa_projects_all
where project_id = p_project_id;
select fin_plan_type_id,
proj_fp_options_id
into l_fin_plan_type_id,
l_proj_fp_options_id
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = ci_rec.budget_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
select DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
nvl(bv.resource_list_id,0),
nvl(bv.budget_status_code, 'W'),
DECODE(bv.budget_status_code,
'B', 'B',
'W'),
DECODE(bv.version_type,
'COST', 'C',
'REVENUE', 'R',
'N'),
bv.record_version_number,
nvl(bv.approved_cost_plan_type_flag, 'N'),
nvl(bv.approved_rev_plan_type_flag, 'N'),
nvl(rl.uncategorized_flag, 'N')
into l_grouping_type,
l_resource_list_id,
x_budget_status_code,
l_working_or_baselined,
l_cost_or_revenue,
l_rv_number,
l_ac_flag,
l_ar_flag,
l_uncategorized_flag
from pa_budget_versions bv,
pa_resource_lists_all_bg rl
where bv.budget_version_id = ci_rec.budget_version_id and
bv.resource_list_id = rl.resource_list_id;
select projfunc_currency_code
into x_view_currency_code
from pa_projects_all
where project_id = p_project_id;
select project_currency_code
into x_view_currency_code
from pa_projects_all
where project_id = p_project_id;
select fin_plan_preference_code
into l_fp_preference_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 report_labor_hrs_from_code,
margin_derived_from_code
into l_report_labor_hrs_from_code,
l_margin_derived_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 all_fin_plan_level_code
into l_cost_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select cost_fin_plan_level_code
into l_cost_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select revenue_fin_plan_level_code
into l_rev_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
rl.resource_list_id,
bv.record_version_number,
nvl(rl.uncategorized_flag, 'N')
into l_compl_grouping_type,
l_compl_resource_list_id,
l_compl_rv_number,
l_compl_uncategorized_flag
from pa_budget_versions bv,
pa_resource_lists_all_bg rl
where bv.budget_version_id = ci_rec.budget_version_id and
bv.resource_list_id = rl.resource_list_id;
select cost_fin_plan_level_code
into l_cost_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select revenue_fin_plan_level_code
into l_rev_planning_level
from pa_proj_fp_options
where proj_fp_options_id = ci_rec.proj_fp_options_id;
select DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
rl.resource_list_id,
bv.record_version_number,
nvl(rl.uncategorized_flag, 'N')
into l_compl_grouping_type,
l_compl_resource_list_id,
l_compl_rv_number,
l_compl_uncategorized_flag
from pa_budget_versions bv,
pa_resource_lists_all_bg rl
where bv.budget_version_id = ci_rec.budget_version_id and
bv.resource_list_id = rl.resource_list_id;
select cost_fin_plan_level_code
into l_cost_planning_level
from pa_proj_fp_options
where proj_fp_options_id = ci_rec.proj_fp_options_id;
select revenue_fin_plan_level_code
into l_rev_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
rl.resource_list_id,
bv.record_version_number,
nvl(rl.uncategorized_flag, 'N')
into l_compl_grouping_type,
l_compl_resource_list_id,
l_compl_rv_number,
l_compl_uncategorized_flag
from pa_budget_versions bv,
pa_resource_lists_all_bg rl
where bv.budget_version_id = ci_rec.budget_version_id and
bv.resource_list_id = rl.resource_list_id;
select cost_fin_plan_level_code
into l_cost_planning_level
from pa_proj_fp_options
where proj_fp_options_id = ci_rec.proj_fp_options_id;
select revenue_fin_plan_level_code
into l_rev_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
rl.resource_list_id,
bv.record_version_number,
nvl(rl.uncategorized_flag, 'N')
into l_compl_grouping_type,
l_compl_resource_list_id,
l_compl_rv_number,
l_compl_uncategorized_flag
from pa_budget_versions bv,
pa_resource_lists_all_bg rl
where bv.budget_version_id = ci_rec.budget_version_id and
bv.resource_list_id = rl.resource_list_id;
select cost_fin_plan_level_code
into l_cost_planning_level
from pa_proj_fp_options
where proj_fp_options_id = l_proj_fp_options_id;
select revenue_fin_plan_level_code
into l_rev_planning_level
from pa_proj_fp_options
where proj_fp_options_id = ci_rec.proj_fp_options_id;
select bv.project_id,
DECODE(p_view_currency_type,
'PROJ', pa.project_currency_code,
'PROJFUNC', pa.projfunc_currency_code,
'TXN')
into l_project_id,
l_projfunc_currency_code
from pa_budget_versions bv,
pa_projects_all pa
where bv.budget_version_id = pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID and
bv.project_id = pa.project_id;
select nvl(org_id,-99)
into l_org_id
from pa_projects_all
where project_id = l_project_id;
select fin_plan_start_date, fin_plan_end_date, fin_plan_type_id
-- default_amount_type_code,
-- default_amount_subtype_code
into ll_plan_start_date,ll_plan_end_date, l_fin_plan_type_id
-- l_default_amount_type_code,
-- l_default_amount_subtype_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID;
select proj_fp_options_id,
margin_derived_from_code,
report_labor_hrs_from_code
into x_plan_fp_options_id, -- OUTPUT: x_plan_fp_options_id
l_margin_derived_from_code,
l_labor_hours_from_code
from pa_proj_fp_options
where project_id = l_project_id and
fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code='PLAN_TYPE';
select DECODE(bv.version_type,
'COST', po.cost_amount_set_id,
'REVENUE', po.revenue_amount_set_id,
po.all_amount_set_id),
bv.version_type,
po.factor_by_code
into l_amount_set_id,
l_version_type,
x_factor_by_code -- OUTPUT: x_factor_by_code
from pa_budget_versions bv,
pa_proj_fp_options po
where bv.budget_version_id = pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID and
bv.fin_plan_type_id = po.fin_plan_type_id and
po.project_id = l_project_id and
po.fin_plan_option_level_code = 'PLAN_TYPE';
select DECODE(l_version_type,
'COST', nvl(cost_qty_flag,'N'),
'REVENUE', nvl(revenue_qty_flag, 'N'),
nvl(all_qty_flag, 'N')),
nvl(raw_cost_flag, 'N'),
nvl(burdened_cost_flag, 'N'),
nvl(revenue_flag, 'N'),
DECODE(l_version_type,
'ALL', 'Y',
'N'),
DECODE(l_version_type,
'ALL', 'Y',
'N')
into l_display_quantity,
l_display_rawcost,
l_display_burdcost,
l_display_revenue,
l_display_margin,
l_display_marginpct
from pa_fin_plan_amount_sets
where fin_plan_amount_set_id = l_amount_set_id;
select approved_rev_plan_type_flag
into x_ar_flag -- OUTPUT: x_ar_flag
from pa_proj_fp_options
where project_id = l_project_id and
fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code='PLAN_TYPE';
select DECODE(version_type,
'REVENUE', 'R',
'COST', 'C',
null),
nvl(budget_status_code, 'W'),
nvl(approved_rev_plan_type_flag, 'N')
into l_cost_or_revenue,
x_budget_status_code, -- OUTPUT: x_budget_status_code
x_ar_flag -- OUTPUT: x_ar_flag
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID;
select pp.plan_period_type,
pp.period1_start_date,
pp.period_profile_id
into ll_plan_period_type,
pp_plan_start_date,
l_period_profile_id
from pa_proj_period_profiles pp,
pa_budget_versions pbv
where pbv.budget_version_id = pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID
and pp.period_profile_id = pbv.period_profile_id;
select min(start_date) into pp_plan_start_date from
pa_budget_lines
where budget_version_id = pa_fp_view_plans_pub.G_FP_VIEW_VERSION_ID;
select decode(nvl(cost_time_phased_code,
nvl(revenue_time_phased_code, all_time_phased_code)), 'G', 'GL', 'P', 'PA', 'N')
into ll_plan_period_type -- OUTPUT: ll_plan_period_type
from pa_proj_fp_options
where project_id = l_project_id and
fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code='PLAN_TYPE';
-- delete residual data in the temp tables
DELETE from PA_FIN_VP_AMTS_VIEW_TMP;
DELETE from PA_FIN_VP_PDS_VIEW_TMP;
select nvl(all_time_phased_code, 'N')
into x_time_phase_code -- OUTPUT: x_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_ALL_VERSION_ID;
select nvl(cost_time_phased_code, 'N')
into x_time_phase_code -- OUTPUT: x_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select nvl(revenue_time_phased_code, 'N')
into x_time_phase_code -- OUTPUT: x_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select nvl(cost_time_phased_code, 'N')
into l_cost_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select nvl(revenue_time_phased_code, 'N')
into l_rev_time_phase_code
from pa_proj_fp_options
where fin_plan_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select record_version_number, -- OUTPUT: x_cost_rv_number
budget_status_code -- OUTPUT: x_budget_status_code
into x_cost_rv_number,
x_budget_status_code
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select record_version_number, -- OUTPUT: x_rev_rv_number
budget_status_code -- OUTPUT: x_budget_status_code
into x_rev_rv_number,
x_budget_status_code
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
SELECT * FROM(
SELECT start_date,end_date
FROM pa_fp_periods_tmp_v
WHERE start_date >= l_start_date
order by start_date
)
where rownum <= l_rownum;
SELECT start_date, end_date
FROM pa_fp_periods_tmp_v
WHERE start_date > l_start_date and
start_date between pa_fp_view_plans_pub.G_FP_PLAN_START_DATE and
pa_fp_view_plans_pub.G_FP_PLAN_END_DATE
order by start_date;
SELECT start_date
FROM pa_fp_periods_tmp_v
WHERE start_date < l_start_date and
start_date between pa_fp_view_plans_pub.G_FP_PLAN_START_DATE and
pa_fp_view_plans_pub.G_FP_PLAN_END_DATE
order by start_date desc;
select * from pa_resource_assignments where budget_version_id=p_budget_version_id;
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
po.fin_plan_preference_code = 'REVENUE_ONLY' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_working_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
po.fin_plan_preference_code = 'REVENUE_ONLY' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
po.fin_plan_preference_code = 'COST_ONLY' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_working_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
po.fin_plan_preference_code = 'COST_ONLY' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
-- po.fin_plan_preference_code = 'COST_AND_REV_SEP' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.version_type = 'REVENUE' and
bv.current_working_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
-- po.fin_plan_preference_code = 'COST_AND_REV_SEP' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.version_type = 'COST' and
bv.current_working_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
-- po.fin_plan_preference_code = 'COST_AND_REV_SEP' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.version_type = 'REVENUE' and
bv.current_flag = 'Y';
select bv.budget_version_id,
po.proj_fp_options_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_type_id = l_fin_plan_type_id and -- same plan type
-- po.fin_plan_preference_code = 'COST_AND_REV_SEP' and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.fin_plan_version_id = bv.budget_version_id and
bv.version_type = 'COST' and
bv.current_flag = 'Y';
select DECODE(budget_status_code,
'B', 'B',
'W'),
fin_plan_type_id
into l_working_or_baselined,
l_fin_plan_type_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select fin_plan_preference_code
into l_fp_preference_code
from pa_proj_fp_options
where fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code = 'PLAN_TYPE' and
project_id = p_project_id;
select version_name,
version_number
into l_cost_version_name,
l_cost_version_number
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id1
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select period_profile_id
into l_period_profile_id2
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_COST_VERSION_ID;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = pa_fp_view_plans_pub.G_FP_REV_VERSION_ID;
select DECODE(budget_status_code,
'B', 'B',
'W'),
fin_plan_type_id
into l_working_or_baselined,
l_fin_plan_type_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select fin_plan_preference_code
into l_fp_preference_code
from pa_proj_fp_options
where fin_plan_type_id = l_fin_plan_type_id and
fin_plan_option_level_code = 'PLAN_TYPE' and
project_id = p_project_id;
select version_name,
version_number
into l_cost_version_name,
l_cost_version_number
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id1
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id2
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id1
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id2
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id1
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id2
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id1
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select period_profile_id
into l_period_profile_id2
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_cost_version_number,
l_cost_version_name
from pa_budget_versions
where budget_version_id = l_compl_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select version_number,
version_name
into l_rev_version_number,
l_rev_version_name
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select t.task_id from pa_tasks t
START WITH t.task_id = p_task_id
CONNECT BY prior t.parent_task_id = t.task_id;
select rowid, p.* from pa_fin_vp_pds_view_tmp p
order by row_level desc;
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.budget_version_id,
ra.resource_assignment_id,
-1 as revenue_budget_version_id, -- revenue_budget_version_id
-1 as revenue_res_assignment_id, -- revenue_resource_assignment_id
pa_fp_view_plans_util.assign_element_name
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as element_name, -- element_name
pa_fp_view_plans_util.assign_element_level
(ra.project_id,
ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id) as element_level, -- element_level
DECODE(pa_fp_view_plans_pub.G_FP_CALC_QUANTITY_FROM,
'REVENUE', 0,
-- ra.total_utilization_hours) as labor_hours,
ra.total_plan_quantity) as labor_hours,
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost, -- burdened_cost
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost, -- raw_cost
0 as revenue, -- revenue
0 as margin, -- margin
0 as margin_percent, -- margin_percent
DECODE(ra.resource_assignment_type,
'ROLLED_UP', 'N',
'USER_ENTERED', 'Y',
'Y') as line_editable_flag, -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as row_level,
pa_fp_view_plans_util.assign_parent_element
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as parent_element_name,
ra.unit_of_measure
from pa_resource_assignments ra
where ra.budget_version_id = p_cost_budget_version_id and
((ra.resource_assignment_type = 'USER_ENTERED' and
exists (select 1 from pa_budget_lines bl
where bl.budget_version_id = ra.budget_version_id and
bl.resource_assignment_id = ra.resource_assignment_id)) or
ra.resource_assignment_type = 'ROLLED_UP');
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
-1 as cost_budget_version_id, -- cost_budget_version_id
-1 as cost_res_assignment_id, -- cost_resource_assignment_id
ra.budget_version_id,
ra.resource_assignment_id,
pa_fp_view_plans_util.assign_element_name
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as element_name, -- element_name
pa_fp_view_plans_util.assign_element_level
(ra.project_id,
ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id) as element_level, -- element_level
DECODE(pa_fp_view_plans_pub.G_FP_CALC_QUANTITY_FROM,
-- 'REVENUE', ra.total_utilization_hours,
'REVENUE', ra.total_plan_quantity,
0) as labor_hours, -- labor_hrs: 0 if not reported from this COST/REVENUE version
0 as burdened_cost, -- burdened_cost
0 as raw_cost, -- raw_cost
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_revenue,
ra.total_plan_revenue) as revenue, -- revenue
0 as margin, -- margin: leave this null until we visit the row again
0 as margin_percent, -- margin_percent: leave this null until we visit the row again
DECODE(ra.resource_assignment_type,
'ROLLED_UP', 'N',
'USER_ENTERED', 'Y',
'Y'), -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as row_level,
pa_fp_view_plans_util.assign_parent_element
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as parent_element_name,
ra.unit_of_measure
from pa_resource_assignments ra
where ra.budget_version_id = p_rev_budget_version_id and
((ra.resource_assignment_type = 'USER_ENTERED' and
exists (select 1 from pa_budget_lines bl
where bl.budget_version_id = ra.budget_version_id and
bl.resource_assignment_id = ra.resource_assignment_id)) or
ra.resource_assignment_type = 'ROLLED_UP');
select DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJFUNC', 'PROJ_FUNCTIONAL',
'PROJ', 'PROJECT',
'TRANSACTION')
into l_currency_type
from dual;
select NVL(po.report_labor_hrs_from_code, 'COST'),
pa_fp_view_plans_pub.G_DEFAULT_AMOUNT_TYPE_CODE,
pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE,
-- po.default_amount_type_code,
-- po.default_amount_subtype_code
bv.period_profile_id
into l_report_labor_hrs_from_code,
l_default_amount_type_code,
l_default_amount_subtype_code,
l_period_profile_id
from pa_proj_fp_options po,
pa_budget_versions bv
where bv.budget_version_id = p_cost_budget_version_id and
bv.fin_plan_type_id = po.fin_plan_type_id and
po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_TYPE';
/* First Insert the project level record */
select
l_c_project_id(1),
0,
0,
l_c_cost_budget_version_id(1),
-2,
-2,
pa_fp_view_plans_util.assign_element_name
(l_c_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_c_project_id(1),
l_c_cost_budget_version_id(1),
0,
0),
0,
0,-- as burdened_cost,
0,-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_c_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_c_project_id(1),
0,
0) ,
l_c_unit_of_measure(1),
pa_fp_view_plans_pub.has_child_rows
(l_c_project_id(1),
l_c_cost_budget_version_id(1),
-1,
0,
0,
null,
'A')
into
l_c_project_id(l_inc_count),
l_c_task_id(l_inc_count),
l_c_resource_list_member_id(l_inc_count),
l_c_cost_budget_version_id(l_inc_count),
l_c_cost_res_assignment_id(l_inc_count),
l_c_revenue_res_assignment_id(l_inc_count),
l_c_element_name(l_inc_count),
l_c_element_level(l_inc_count),
l_c_labor_hours(l_inc_count),
l_c_burdened_cost(l_inc_count),
l_c_raw_cost(l_inc_count),
l_c_revenue(l_inc_count),
l_c_margin(l_inc_count),
l_c_margin_percent(l_inc_count),
l_c_line_editable_flag(l_inc_count),
l_c_row_level(l_inc_count),
l_c_parent_element_name(l_inc_count),
l_c_unit_of_measure(l_inc_count),
l_c_has_child_element(l_inc_count)
from dual;
/* Now loop through all the resource assignments fetched and insert the parent record if its not found or rollup the amounts if its found -- COST*/
for i in 1..l_loop_count loop
/* rolling up the project level amounts */
if (l_c_task_id(l_loop_count+1) = 0 and l_c_resource_list_member_id(l_loop_count+1) = 0) then
l_c_burdened_cost(l_loop_count+1) := l_c_burdened_cost(l_loop_count+1) + l_c_burdened_cost(i);
/* Parent task level record is not available, insert now */
l_inc_count := l_inc_count + 1;
select
l_c_project_id(i),
task_rec.task_id,
0,
l_c_cost_budget_version_id(i),
-2,
-2,
pa_fp_view_plans_util.assign_element_name
(l_c_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_c_project_id(i),
l_c_cost_budget_version_id(i),
task_rec.task_id,
0),
l_c_labor_hours(i),
l_c_burdened_cost(i),-- as burdened_cost,
l_c_raw_cost(i),-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_c_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_c_project_id(i),
task_rec.task_id,
0) ,
l_c_unit_of_measure(i),
pa_fp_view_plans_pub.has_child_rows
(l_c_project_id(i),
l_c_cost_budget_version_id(i),
-1,
task_rec.task_id,
0,
null,
'A')
into
l_c_project_id(l_inc_count),
l_c_task_id(l_inc_count),
l_c_resource_list_member_id(l_inc_count),
l_c_cost_budget_version_id(l_inc_count),
l_c_cost_res_assignment_id(l_inc_count),
l_c_revenue_res_assignment_id(l_inc_count),
l_c_element_name(l_inc_count),
l_c_element_level(l_inc_count),
l_c_labor_hours(l_inc_count),
l_c_burdened_cost(l_inc_count),
l_c_raw_cost(l_inc_count),
l_c_revenue(l_inc_count),
l_c_margin(l_inc_count),
l_c_margin_percent(l_inc_count),
l_c_line_editable_flag(l_inc_count),
l_c_row_level(l_inc_count),
l_c_parent_element_name(l_inc_count),
l_c_unit_of_measure(l_inc_count),
l_c_has_child_element(l_inc_count)
from dual;
/* First Insert the project level record */
select
l_r_project_id(1),
0,
0,
l_r_cost_budget_version_id(1),
-2,
-2,
l_r_revenue_budget_version_id(1),
pa_fp_view_plans_util.assign_element_name
(l_r_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_r_project_id(1),
l_r_cost_budget_version_id(1),
0,
0),
0,
0,-- as burdened_cost,
0,-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_r_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_r_project_id(1),
0,
0) ,
l_r_unit_of_measure(1),
pa_fp_view_plans_pub.has_child_rows
(l_r_project_id(1),
l_r_cost_budget_version_id(1),
-1,
0,
0,
null,
'A')
into
l_r_project_id(l_inc_count),
l_r_task_id(l_inc_count),
l_r_resource_list_member_id(l_inc_count),
l_r_cost_budget_version_id(l_inc_count),
l_r_cost_res_assignment_id(l_inc_count),
l_r_revenue_res_assignment_id(l_inc_count),
l_r_revenue_budget_version_id(l_inc_count),
l_r_element_name(l_inc_count),
l_r_element_level(l_inc_count),
l_r_labor_hours(l_inc_count),
l_r_burdened_cost(l_inc_count),
l_r_raw_cost(l_inc_count),
l_r_revenue(l_inc_count),
l_r_margin(l_inc_count),
l_r_margin_percent(l_inc_count),
l_r_line_editable_flag(l_inc_count),
l_r_row_level(l_inc_count),
l_r_parent_element_name(l_inc_count),
l_r_unit_of_measure(l_inc_count),
l_r_has_child_element(l_inc_count)
from dual;
/* Now loop through all the resource assignments fetched and insert the parent record if its not found or rollup the amounts if its found -- REVENUE*/
for i in 1..l_loop_count loop
/* rolling up the project level amounts */
if (l_r_task_id(l_loop_count+1) = 0 and l_r_resource_list_member_id(l_loop_count+1) = 0) then
l_r_burdened_cost(l_loop_count+1) := l_r_burdened_cost(l_loop_count+1) + l_r_burdened_cost(i);
/* Parent task level record is not available, insert now */
l_inc_count := l_inc_count + 1;
select
l_r_project_id(i),
task_rec.task_id,
0,
l_r_cost_budget_version_id(i),
-2,
-2,
l_r_revenue_budget_version_id(i),
pa_fp_view_plans_util.assign_element_name
(l_r_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_r_project_id(i),
l_r_cost_budget_version_id(i),
task_rec.task_id,
0),
l_r_labor_hours(i),
l_r_burdened_cost(i),-- as burdened_cost,
l_r_raw_cost(i),-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_r_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_r_project_id(i),
task_rec.task_id,
0) ,
l_r_unit_of_measure(i),
pa_fp_view_plans_pub.has_child_rows
(l_r_project_id(i),
l_r_cost_budget_version_id(i),
-1,
task_rec.task_id,
0,
null,
'A')
into
l_r_project_id(l_inc_count),
l_r_task_id(l_inc_count),
l_r_resource_list_member_id(l_inc_count),
l_r_cost_budget_version_id(l_inc_count),
l_r_cost_res_assignment_id(l_inc_count),
l_r_revenue_res_assignment_id(l_inc_count),
l_r_revenue_budget_version_id(l_inc_count),
l_r_element_name(l_inc_count),
l_r_element_level(l_inc_count),
l_r_labor_hours(l_inc_count),
l_r_burdened_cost(l_inc_count),
l_r_raw_cost(l_inc_count),
l_r_revenue(l_inc_count),
l_r_margin(l_inc_count),
l_r_margin_percent(l_inc_count),
l_r_line_editable_flag(l_inc_count),
l_r_row_level(l_inc_count),
l_r_parent_element_name(l_inc_count),
l_r_unit_of_measure(l_inc_count),
l_r_has_child_element(l_inc_count)
from dual;
l_r_project_id.delete(j);
l_r_task_id.delete(j);
l_r_resource_list_member_id.delete(j);
l_r_cost_budget_version_id.delete(j);
l_r_cost_res_assignment_id.delete(j);
l_r_revenue_budget_version_id.delete(j);
l_r_revenue_res_assignment_id.delete(j);
l_r_element_name.delete(j);
l_r_element_level.delete(j);
l_r_labor_hours.delete(j);
l_r_burdened_cost.delete(j);
l_r_revenue.delete(j);
l_r_margin.delete(j);
l_r_margin_percent.delete(j);
l_r_line_editable_flag.delete(j);
-- after transferring data, delete row from REVENUE PL/SQL table:
-- to do this, we need to shift up all subsequent rows, and delete the very last row
if j=l_r_project_id.last then
-- We've reached the last row; delete it
l_r_project_id.delete(j);
l_r_task_id.delete(j);
l_r_resource_list_member_id.delete(j);
l_r_cost_budget_version_id.delete(j);
l_r_cost_res_assignment_id.delete(j);
l_r_revenue_budget_version_id.delete(j);
l_r_revenue_res_assignment_id.delete(j);
l_r_element_name.delete(j);
l_r_element_level.delete(j);
l_r_labor_hours.delete(j);
l_r_burdened_cost.delete(j);
l_r_revenue.delete(j);
l_r_margin.delete(j);
l_r_margin_percent.delete(j);
l_r_line_editable_flag.delete(j);
l_r_raw_cost.delete(j);
l_r_row_level.delete(j);
l_r_parent_element_name.delete(j);
l_r_unit_of_measure.delete(j);
-- shift up all rows after deleted row
l_r_project_id(j) := l_r_project_id(j+1);
end if; -- found complement in loop: SHIFT ALL SUBSEQUENT ROWS UP AND DELETE LAST ROW
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit dummy insert
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted rows
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
insert into pa_fin_vp_amts_view_tmp
(project_id,
task_id,
resource_list_member_id,
element_name,
element_level,
labor_hours,
burdened_cost,
raw_cost,
revenue,
margin,
margin_percent,
editable_flag,
row_level,
parent_element_name,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
unit_of_measure,
has_child_element) values
(l_c_project_id(x),
l_c_task_id(x),
l_c_resource_list_member_id(x),
l_c_element_name(x),
l_c_element_level(x),
l_c_labor_hours(x),
l_c_burdened_cost(x),
l_c_raw_cost(x),
l_c_revenue(x),
l_c_margin(x),
l_c_margin_percent(x),
l_c_line_editable_flag(x),
l_c_row_level(x),
l_c_parent_element_name(x),
l_c_cost_res_assignment_id(x),
l_c_revenue_res_assignment_id(x),
-1,
l_c_unit_of_measure(x),
l_c_has_child_element(x));
insert into pa_fin_vp_amts_view_tmp
(project_id,
task_id,
resource_list_member_id,
element_name,
element_level,
labor_hours,
burdened_cost,
raw_cost,
revenue,
margin,
margin_percent,
editable_flag,
row_level,
parent_element_name,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
unit_of_measure,
has_child_element) values
(l_r_project_id(y),
l_r_task_id(y),
l_r_resource_list_member_id(y),
l_r_element_name(y),
l_r_element_level(y),
l_r_labor_hours(y),
0, -- burdened_cost
0, -- raw_cost
l_r_revenue(y),
0, -- margin
0, -- margin_percent
l_r_line_editable_flag(y),
l_r_row_level(y),
l_r_parent_element_name(y),
-1,
l_r_revenue_res_assignment_id(y),
-1,
l_r_unit_of_measure(y),
'Y'); -- PERFORMANCE LIABILITY: FOR UNMARRIED REVENUE ROWS, WE SAY ALWAYS HAVE CHILD
insert into pa_fin_vp_pds_view_tmp
(project_id,
task_id,
resource_list_member_id,
uom,
element_name,
element_level,
editable_flag,
row_level,
parent_element_name,
amount_type,
amount_subtype,
amount_type_id,
amount_subtype_id,
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,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
preceding_periods_amount,
succeeding_periods_amount,
has_child_element,
project_total) values
(l_pd_project_id(z),
l_pd_task_id(z),
l_pd_resource_list_member_id(z),
l_pd_unit_of_measure(z),
l_pd_element_name(z),
l_pd_element_level(z),
l_pd_line_editable_flag(z),
l_pd_row_level(z),
l_pd_parent_element_name(z),
l_pd_amount_type(z),
l_pd_amount_subtype(z),
l_pd_amount_type_id(z),
l_pd_amount_subtype_id(z),
l_pd_period_1(z),
l_pd_period_2(z),
l_pd_period_3(z),
l_pd_period_4(z),
l_pd_period_5(z),
l_pd_period_6(z),
l_pd_period_7(z),
l_pd_period_8(z),
l_pd_period_9(z),
l_pd_period_10(z),
l_pd_period_11(z),
l_pd_period_12(z),
l_pd_period_13(z),
l_pd_cost_res_assignment_id(z),
l_pd_revenue_res_assignment_id(z),
-1,
l_pd_preceding(z),
l_pd_succeeding(z),
l_pd_has_child_element(z),
l_pd_project_total(z));
select 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
into
t_period_amount1,
t_period_amount2,
t_period_amount3,
t_period_amount4,
t_period_amount5,
t_period_amount6,
t_period_amount7,
t_period_amount8,
t_period_amount9,
t_period_amount10,
t_period_amount11,
t_period_amount12,
t_period_amount13
from pa_fin_vp_pds_view_tmp
where rowid = rollup_rec.rowid;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is not null
and p.element_name = rollup_rec.parent_element_name
and p.element_name is not null;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is null
and p.element_name is null
and (p.project_id,p.task_id, p.resource_list_member_id,row_level)
in (select p1.project_id, p1.task_id, p1.resource_list_member_id, p1.row_level
from pa_fin_vp_pds_view_tmp p1
where p1.element_name = (select p2.parent_element_name from
pa_fin_vp_pds_view_tmp p2
where p2.row_level = rollup_rec.row_level
and p2.element_name is not null
and p2.project_id = rollup_rec.project_id
and p2.task_id = rollup_rec.task_id
and p2.resource_list_member_id = rollup_rec.resource_list_member_id
and rownum<2
)
);
select t.task_id from pa_tasks t
START WITH t.task_id = p_task_id
CONNECT BY prior t.parent_task_id = t.task_id;
select rowid, p.* from pa_fin_vp_pds_view_tmp p
order by row_level desc;
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.budget_version_id,
ra.resource_assignment_id,
pa_fp_view_plans_util.assign_element_name
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as element_name, -- element_name
pa_fp_view_plans_util.assign_element_level
(ra.project_id,
ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id) as element_level, -- element_level
-- ra.total_utilization_hours as labor_hours,
ra.total_plan_quantity as labor_hours,
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost, -- burdened_cost
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost, -- raw_cost
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_revenue,
ra.total_plan_revenue) as revenue, -- revenue
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
'R', (ra.total_project_revenue - ra.total_project_raw_cost),
(ra.total_project_revenue - ra.total_project_burdened_cost)),
DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
'R', (ra.total_plan_revenue - total_plan_raw_cost),
(ra.total_plan_revenue - ra.total_plan_burdened_cost))) as margin, -- margin
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ',
DECODE(ra.total_project_revenue,
0, 0,
null, null,
DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
'R', (ra.total_project_revenue - ra.total_project_raw_cost)/
ra.total_project_revenue,
(ra.total_project_revenue - ra.total_project_burdened_cost)/
ra.total_project_revenue)),
DECODE(ra.total_plan_revenue,
0, 0,
null, null,
DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
'R', (ra.total_plan_revenue - ra.total_plan_raw_cost)/
ra.total_plan_revenue,
(ra.total_plan_revenue - ra.total_plan_burdened_cost)/
ra.total_plan_revenue))) as margin_percent, -- margin_percent
DECODE(ra.resource_assignment_type,
'ROLLED_UP', 'N',
'USER_ENTERED', 'Y',
'Y') as line_editable_flag, -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as row_level,
pa_fp_view_plans_util.assign_parent_element
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as parent_element_name,
ra.unit_of_measure,
pa_fp_view_plans_pub.has_child_rows
(p_project_id,
p_budget_version_id,
-1,
ra.task_id,
ra.resource_list_member_id,
null,
'A') as has_child_element
from pa_resource_assignments ra,
pa_proj_fp_options po
where ra.budget_version_id = p_budget_version_id and
ra.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code='PLAN_VERSION' and
((ra.resource_assignment_type = 'USER_ENTERED' and
exists (select 1 from pa_budget_lines bl
where bl.budget_version_id = ra.budget_version_id and
bl.resource_assignment_id = ra.resource_assignment_id)) or
ra.resource_assignment_type = 'ROLLED_UP');
select DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJFUNC', 'PROJ_FUNCTIONAL',
'PROJ', 'PROJECT',
'TRANSACTION')
into l_currency_type
from dual;
select NVL(po.report_labor_hrs_from_code, 'COST'),
pa_fp_view_plans_pub.G_DEFAULT_AMOUNT_TYPE_CODE,
pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE,
-- po.default_amount_type_code,
-- po.default_amount_subtype_code
bv.period_profile_id
into l_report_labor_hrs_from_code,
l_default_amount_type_code,
l_default_amount_subtype_code,
l_period_profile_id
from pa_proj_fp_options po,
pa_budget_versions bv
where bv.budget_version_id = p_budget_version_id and
bv.fin_plan_type_id = po.fin_plan_type_id and
po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_TYPE';
/* First Insert the project level record */
select
l_project_id(1),
0,
0,
l_budget_version_id(1),
-2,
pa_fp_view_plans_util.assign_element_name
(l_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_project_id(1),
l_budget_version_id(1),
0,
0),
0,
0,-- as burdened_cost,
0,-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_project_id(1),
0,
0) ,
l_unit_of_measure(1),
pa_fp_view_plans_pub.has_child_rows
(l_project_id(1),
l_budget_version_id(1),
-1,
0,
0,
null,
'A')
into
l_project_id(l_inc_count),
l_task_id(l_inc_count),
l_resource_list_member_id(l_inc_count),
l_budget_version_id(l_inc_count),
l_res_assignment_id(l_inc_count),
l_element_name(l_inc_count),
l_element_level(l_inc_count),
l_labor_hours(l_inc_count),
l_burdened_cost(l_inc_count),
l_raw_cost(l_inc_count),
l_revenue(l_inc_count),
l_margin(l_inc_count),
l_margin_percent(l_inc_count),
l_line_editable_flag(l_inc_count),
l_row_level(l_inc_count),
l_parent_element_name(l_inc_count),
l_unit_of_measure(l_inc_count),
l_has_child_element(l_inc_count)
from dual;
/* Now loop through all the resource assignments fetched and insert the parent record if its not found or rollup the amounts if its found */
for i in 1..l_loop_count loop
/* rolling up the project level amounts */
if (l_task_id(l_loop_count+1) = 0 and l_resource_list_member_id(l_loop_count+1) = 0) then
l_burdened_cost(l_loop_count+1) := l_burdened_cost(l_loop_count+1) + l_burdened_cost(i);
/* Parent task level record is not available, insert now */
l_inc_count := l_inc_count + 1;
select
l_project_id(i),
task_rec.task_id,
0,
l_budget_version_id(i),
-2,
pa_fp_view_plans_util.assign_element_name
(l_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_project_id(i),
l_budget_version_id(i),
task_rec.task_id,
0),
l_labor_hours(i),
l_burdened_cost(i),-- as burdened_cost,
l_raw_cost(i),-- as raw_cost,
l_revenue(i),
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_project_id(i),
task_rec.task_id,
0) ,
l_unit_of_measure(i),
pa_fp_view_plans_pub.has_child_rows
(l_project_id(i),
l_budget_version_id(i),
-1,
task_rec.task_id,
0,
null,
'A')
into
l_project_id(l_inc_count),
l_task_id(l_inc_count),
l_resource_list_member_id(l_inc_count),
l_budget_version_id(l_inc_count),
l_res_assignment_id(l_inc_count),
l_element_name(l_inc_count),
l_element_level(l_inc_count),
l_labor_hours(l_inc_count),
l_burdened_cost(l_inc_count),
l_raw_cost(l_inc_count),
l_revenue(l_inc_count),
l_margin(l_inc_count),
l_margin_percent(l_inc_count),
l_line_editable_flag(l_inc_count),
l_row_level(l_inc_count),
l_parent_element_name(l_inc_count),
l_unit_of_measure(l_inc_count),
l_has_child_element(l_inc_count)
from dual;
insert into pa_fin_vp_amts_view_tmp
(project_id,
task_id,
resource_list_member_id,
element_name,
element_level,
labor_hours,
burdened_cost,
raw_cost,
revenue,
margin,
margin_percent,
editable_flag,
row_level,
parent_element_name,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
unit_of_measure,
has_child_element) values
(l_project_id(i),
l_task_id(i),
l_resource_list_member_id(i),
l_element_name(i),
l_element_level(i),
l_labor_hours(i),
l_burdened_cost(i),
l_raw_cost(i),
l_revenue(i),
l_margin(i),
l_margin_percent(i),
l_line_editable_flag(i),
l_row_level(i),
l_parent_element_name(i),
-1,
-1,
l_res_assignment_id(i),
l_unit_of_measure(i),
l_has_child_element(i));
update pa_fin_vp_amts_view_tmp
set margin_percent = DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
'R', DECODE(NVL(revenue,0),0,0,(revenue - raw_cost)/revenue),
DECODE(NVL(revenue,0),0,0,(revenue - burdened_cost)/revenue)),
margin = DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
'R', (revenue - raw_cost), (revenue - burdened_cost))
where resource_list_member_id = 0;
insert into pa_fin_vp_pds_view_tmp
(project_id,
task_id,
resource_list_member_id,
uom,
element_name,
element_level,
editable_flag,
row_level,
parent_element_name,
amount_type,
amount_subtype,
amount_type_id,
amount_subtype_id,
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,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
preceding_periods_amount,
succeeding_periods_amount,
has_child_element,
project_total) values
(l_pd_project_id(z),
l_pd_task_id(z),
l_pd_resource_list_member_id(z),
l_pd_unit_of_measure(z),
l_pd_element_name(z),
l_pd_element_level(z),
l_pd_line_editable_flag(z),
l_pd_row_level(z),
l_pd_parent_element_name(z),
l_pd_amount_type(z),
l_pd_amount_subtype(z),
l_pd_amount_type_id(z),
l_pd_amount_subtype_id(z),
l_pd_period_1(z),
l_pd_period_2(z),
l_pd_period_3(z),
l_pd_period_4(z),
l_pd_period_5(z),
l_pd_period_6(z),
l_pd_period_7(z),
l_pd_period_8(z),
l_pd_period_9(z),
l_pd_period_10(z),
l_pd_period_11(z),
l_pd_period_12(z),
l_pd_period_13(z),
-1,
-1,
l_pd_res_assignment_id(z),
l_pd_preceding(z),
l_pd_succeeding(z),
l_pd_has_child_element(z),
l_pd_project_total(z));
select 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
into
t_period_amount1,
t_period_amount2,
t_period_amount3,
t_period_amount4,
t_period_amount5,
t_period_amount6,
t_period_amount7,
t_period_amount8,
t_period_amount9,
t_period_amount10,
t_period_amount11,
t_period_amount12,
t_period_amount13
from pa_fin_vp_pds_view_tmp
where rowid = rollup_rec.rowid;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_type = rollup_rec.amount_type
and rollup_rec.element_name is not null
and p.element_name = rollup_rec.parent_element_name
and p.element_name is not null;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is null
and p.element_name is null
and (p.project_id,p.task_id, p.resource_list_member_id,row_level)
in (select p1.project_id, p1.task_id, p1.resource_list_member_id, p1.row_level
from pa_fin_vp_pds_view_tmp p1
where p1.element_name = (select p2.parent_element_name from
pa_fin_vp_pds_view_tmp p2
where p2.row_level = rollup_rec.row_level
and p2.element_name is not null
and p2.project_id = rollup_rec.project_id
and p2.task_id = rollup_rec.task_id
and p2.resource_list_member_id = rollup_rec.resource_list_member_id
and rownum<2
)
);
select t.task_id from pa_tasks t
START WITH t.task_id = p_task_id
CONNECT BY prior t.parent_task_id = t.task_id;
select rowid, p.* from pa_fin_vp_pds_view_tmp p
order by row_level desc;
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.budget_version_id,
ra.resource_assignment_id,
pa_fp_view_plans_util.assign_element_name
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as element_name, -- element_name
pa_fp_view_plans_util.assign_element_level
(ra.project_id,
ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id) as element_level, -- element_level
DECODE(po.report_labor_hrs_from_code,
'REVENUE', 0,
-- ra.total_utilization_hours) as labor_hours,
ra.total_plan_quantity) as labor_hours,
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost, -- burdened_cost
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost, -- raw_cost
0 as revenue, -- revenue
0 as margin, -- margin
0 as margin_percent, -- margin_percent
DECODE(ra.resource_assignment_type,
'ROLLED_UP', 'N',
'USER_ENTERED', 'Y',
'Y') as line_editable_flag, -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as row_level,
pa_fp_view_plans_util.assign_parent_element
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as parent_element_name,
ra.unit_of_measure,
pa_fp_view_plans_pub.has_child_rows
(p_project_id,
p_budget_version_id,
-1,
ra.task_id,
ra.resource_list_member_id,
null,
'A') as has_child_element
from pa_resource_assignments ra,
pa_proj_fp_options po
where ra.budget_version_id = p_budget_version_id and
ra.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code='PLAN_VERSION' and
((ra.resource_assignment_type = 'USER_ENTERED' and
exists (select 1 from pa_budget_lines bl
where bl.budget_version_id = ra.budget_version_id and
bl.resource_assignment_id = ra.resource_assignment_id)) or
ra.resource_assignment_type = 'ROLLED_UP');
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.budget_version_id,
ra.resource_assignment_id,
pa_fp_view_plans_util.assign_element_name
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as element_name, -- element_name
pa_fp_view_plans_util.assign_element_level
(ra.project_id,
ra.budget_version_id,
ra.task_id,
ra.resource_list_member_id) as element_level, -- element_level
DECODE(po.report_labor_hrs_from_code,
'COST', 0,
-- ra.total_utilization_hours) as labor_hours,
ra.total_plan_quantity) as labor_hours,
0 as burdened_cost, -- burdened_cost
0 as raw_cost, -- raw_cost
DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJ', ra.total_project_revenue,
ra.total_plan_revenue) as revenue, -- revenue
0 as margin, -- margin
0 as margin_percent, -- margin_percent
DECODE(ra.resource_assignment_type,
'ROLLED_UP', 'N',
'USER_ENTERED', 'Y',
'Y') as line_editable_flag, -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as row_level,
pa_fp_view_plans_util.assign_parent_element
(ra.project_id,
ra.task_id,
ra.resource_list_member_id) as parent_element_name,
ra.unit_of_measure,
pa_fp_view_plans_pub.has_child_rows
(p_project_id,
p_budget_version_id,
-1,
ra.task_id,
ra.resource_list_member_id,
null,
'A') as has_child_element
from pa_resource_assignments ra,
pa_proj_fp_options po
where ra.budget_version_id = p_budget_version_id and
ra.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code='PLAN_VERSION' and
((ra.resource_assignment_type = 'USER_ENTERED' and
exists (select 1 from pa_budget_lines bl
where bl.budget_version_id = ra.budget_version_id and
bl.resource_assignment_id = ra.resource_assignment_id)) or
ra.resource_assignment_type = 'ROLLED_UP');
select DECODE(pa_fp_view_plans_pub.G_FP_CURRENCY_TYPE,
'PROJFUNC', 'PROJ_FUNCTIONAL',
'PROJ', 'PROJECT',
'TRANSACTION')
into l_currency_type
from dual;
select NVL(po.report_labor_hrs_from_code, 'COST'),
-- po.default_amount_type_code,
-- po.default_amount_subtype_code
pa_fp_view_plans_pub.G_DEFAULT_AMOUNT_TYPE_CODE,
pa_fp_view_plans_pub.G_DEFAULT_AMT_SUBTYPE_CODE,
bv.period_profile_id
into l_report_labor_hrs_from_code,
l_default_amount_type_code,
l_default_amount_subtype_code,
l_period_profile_id
from pa_proj_fp_options po,
pa_budget_versions bv
where bv.budget_version_id = p_budget_version_id and
bv.fin_plan_type_id = po.fin_plan_type_id and
po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_TYPE';
/* Insert the project/task level records here */
l_loop_count := nvl(l_project_id.last,-1);
/* First Insert the project level record */
select
l_project_id(1),
0,
0,
l_budget_version_id(1),
-2,
pa_fp_view_plans_util.assign_element_name
(l_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_project_id(1),
l_budget_version_id(1),
0,
0),
0,
0,-- as burdened_cost,
0,-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_project_id(1),
0,
0) ,
l_unit_of_measure(1),
pa_fp_view_plans_pub.has_child_rows
(l_project_id(1),
l_budget_version_id(1),
-1,
0,
0,
null,
'A')
into
l_project_id(l_inc_count),
l_task_id(l_inc_count),
l_resource_list_member_id(l_inc_count),
l_budget_version_id(l_inc_count),
l_res_assignment_id(l_inc_count),
l_element_name(l_inc_count),
l_element_level(l_inc_count),
l_labor_hours(l_inc_count),
l_burdened_cost(l_inc_count),
l_raw_cost(l_inc_count),
l_revenue(l_inc_count),
l_margin(l_inc_count),
l_margin_percent(l_inc_count),
l_line_editable_flag(l_inc_count),
l_row_level(l_inc_count),
l_parent_element_name(l_inc_count),
l_unit_of_measure(l_inc_count),
l_has_child_element(l_inc_count)
from dual;
/* Now loop through all the resource assignments fetched and insert the parent record if its not found or rollup the amounts if its found */
for i in 1..l_loop_count loop
/* rolling up the project level amounts */
if (l_task_id(l_loop_count+1) = 0 and l_resource_list_member_id(l_loop_count+1) = 0) then
l_burdened_cost(l_loop_count+1) := l_burdened_cost(l_loop_count+1) + l_burdened_cost(i);
/* Parent task level record is not available, insert now */
l_inc_count := l_inc_count + 1;
select
l_project_id(i),
task_rec.task_id,
0,
l_budget_version_id(i),
-2,
pa_fp_view_plans_util.assign_element_name
(l_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_project_id(i),
l_budget_version_id(i),
task_rec.task_id,
0),
l_labor_hours(i),
l_burdened_cost(i),-- as burdened_cost,
l_raw_cost(i),-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_project_id(i),
task_rec.task_id,
0) ,
l_unit_of_measure(i),
pa_fp_view_plans_pub.has_child_rows
(l_project_id(i),
l_budget_version_id(i),
-1,
task_rec.task_id,
0,
null,
'A')
into
l_project_id(l_inc_count),
l_task_id(l_inc_count),
l_resource_list_member_id(l_inc_count),
l_budget_version_id(l_inc_count),
l_res_assignment_id(l_inc_count),
l_element_name(l_inc_count),
l_element_level(l_inc_count),
l_labor_hours(l_inc_count),
l_burdened_cost(l_inc_count),
l_raw_cost(l_inc_count),
l_revenue(l_inc_count),
l_margin(l_inc_count),
l_margin_percent(l_inc_count),
l_line_editable_flag(l_inc_count),
l_row_level(l_inc_count),
l_parent_element_name(l_inc_count),
l_unit_of_measure(l_inc_count),
l_has_child_element(l_inc_count)
from dual;
/* Ends- Insert the project/task level records */
/* Ends added code for 7514054 */
l_err_stage := 300;
l_pd_line_editable_flag(l_row_number) := 'N'; --do not edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
insert into pa_fin_vp_amts_view_tmp
(project_id,
task_id,
resource_list_member_id,
element_name,
element_level,
labor_hours,
burdened_cost,
raw_cost,
revenue,
margin,
margin_percent,
editable_flag,
row_level,
parent_element_name,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
unit_of_measure,
has_child_element) values
(l_project_id(i),
l_task_id(i),
l_resource_list_member_id(i),
l_element_name(i),
l_element_level(i),
nvl(l_labor_hours(i), 0),
nvl(l_burdened_cost(i), 0),
nvl(l_raw_cost(i), 0),
null, -- revenue
null, -- margin
null, -- margin percent
l_line_editable_flag(i),
l_row_level(i),
l_parent_element_name(i),
l_res_assignment_id(i),
-1,
-1,
l_unit_of_measure(i),
l_has_child_element(i));
insert into pa_fin_vp_pds_view_tmp
(project_id,
task_id,
resource_list_member_id,
uom,
element_name,
element_level,
editable_flag,
row_level,
parent_element_name,
amount_type,
amount_subtype,
amount_type_id,
amount_subtype_id,
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,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
preceding_periods_amount,
succeeding_periods_amount,
has_child_element,
project_total) values
(l_pd_project_id(z),
l_pd_task_id(z),
l_pd_resource_list_member_id(z),
l_pd_unit_of_measure(z),
l_pd_element_name(z),
l_pd_element_level(z),
l_pd_line_editable_flag(z),
l_pd_row_level(z),
l_pd_parent_element_name(z),
l_pd_amount_type(z),
l_pd_amount_subtype(z),
l_pd_amount_type_id(z),
l_pd_amount_subtype_id(z),
nvl(l_pd_period_1(z), 0),
nvl(l_pd_period_2(z), 0),
nvl(l_pd_period_3(z), 0),
nvl(l_pd_period_4(z), 0),
nvl(l_pd_period_5(z), 0),
nvl(l_pd_period_6(z), 0),
nvl(l_pd_period_7(z), 0),
nvl(l_pd_period_8(z), 0),
nvl(l_pd_period_9(z), 0),
nvl(l_pd_period_10(z), 0),
nvl(l_pd_period_11(z), 0),
nvl(l_pd_period_12(z), 0),
nvl(l_pd_period_13(z), 0),
l_pd_res_assignment_id(z),
-1,
-1,
l_pd_preceding(z),
l_pd_succeeding(z),
l_pd_has_child_element(z),
l_pd_project_total(z));
select 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
into
t_period_amount1,
t_period_amount2,
t_period_amount3,
t_period_amount4,
t_period_amount5,
t_period_amount6,
t_period_amount7,
t_period_amount8,
t_period_amount9,
t_period_amount10,
t_period_amount11,
t_period_amount12,
t_period_amount13
from pa_fin_vp_pds_view_tmp
where rowid = rollup_rec.rowid;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is not null
and p.element_name = rollup_rec.parent_element_name
and p.element_name is not null;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is null
and p.element_name is null
and (p.project_id,p.task_id, p.resource_list_member_id,row_level)
in (select p1.project_id, p1.task_id, p1.resource_list_member_id, p1.row_level
from pa_fin_vp_pds_view_tmp p1
where p1.element_name = (select p2.parent_element_name from
pa_fin_vp_pds_view_tmp p2
where p2.row_level = rollup_rec.row_level
and p2.element_name is not null
and p2.project_id = rollup_rec.project_id
and p2.task_id = rollup_rec.task_id
and p2.resource_list_member_id = rollup_rec.resource_list_member_id
and rownum<2
)
);
/* Insert the project/task level records here */
l_loop_count := nvl(l_project_id.last,-1);
/* First Insert the project level record */
select
l_project_id(1),
0,
0,
l_budget_version_id(1),
-2,
pa_fp_view_plans_util.assign_element_name
(l_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_project_id(1),
l_budget_version_id(1),
0,
0),
0,
0,-- as burdened_cost,
0,-- as raw_cost,
0,
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_project_id(1),
0,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_project_id(1),
0,
0) ,
l_unit_of_measure(1),
pa_fp_view_plans_pub.has_child_rows
(l_project_id(1),
l_budget_version_id(1),
-1,
0,
0,
null,
'A')
into
l_project_id(l_inc_count),
l_task_id(l_inc_count),
l_resource_list_member_id(l_inc_count),
l_budget_version_id(l_inc_count),
l_res_assignment_id(l_inc_count),
l_element_name(l_inc_count),
l_element_level(l_inc_count),
l_labor_hours(l_inc_count),
l_burdened_cost(l_inc_count),
l_raw_cost(l_inc_count),
l_revenue(l_inc_count),
l_margin(l_inc_count),
l_margin_percent(l_inc_count),
l_line_editable_flag(l_inc_count),
l_row_level(l_inc_count),
l_parent_element_name(l_inc_count),
l_unit_of_measure(l_inc_count),
l_has_child_element(l_inc_count)
from dual;
/* Now loop through all the resource assignments fetched and insert the parent record if its not found or rollup the amounts if its found */
for i in 1..l_loop_count loop
/* rolling up the project level amounts */
if (l_task_id(l_loop_count+1) = 0 and l_resource_list_member_id(l_loop_count+1) = 0) then
l_revenue(l_loop_count+1) := l_revenue(l_loop_count+1) + l_revenue(i);
/* Parent task level record is not available, insert now */
l_inc_count := l_inc_count + 1;
select
l_project_id(i),
task_rec.task_id,
0,
l_budget_version_id(i),
-2,
pa_fp_view_plans_util.assign_element_name
(l_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_element_level
(l_project_id(i),
l_budget_version_id(i),
task_rec.task_id,
0),
l_labor_hours(i),
0,-- as burdened_cost,
0,-- as raw_cost,
l_revenue(i),
0, -- margin
0, -- margin_percent
'N', -- line_editable_flag
pa_fp_view_plans_util.assign_row_level
(l_project_id(i),
task_rec.task_id,
0) ,
pa_fp_view_plans_util.assign_parent_element
(l_project_id(i),
task_rec.task_id,
0) ,
l_unit_of_measure(i),
pa_fp_view_plans_pub.has_child_rows
(l_project_id(i),
l_budget_version_id(i),
-1,
task_rec.task_id,
0,
null,
'A')
into
l_project_id(l_inc_count),
l_task_id(l_inc_count),
l_resource_list_member_id(l_inc_count),
l_budget_version_id(l_inc_count),
l_res_assignment_id(l_inc_count),
l_element_name(l_inc_count),
l_element_level(l_inc_count),
l_labor_hours(l_inc_count),
l_burdened_cost(l_inc_count),
l_raw_cost(l_inc_count),
l_revenue(l_inc_count),
l_margin(l_inc_count),
l_margin_percent(l_inc_count),
l_line_editable_flag(l_inc_count),
l_row_level(l_inc_count),
l_parent_element_name(l_inc_count),
l_unit_of_measure(l_inc_count),
l_has_child_element(l_inc_count)
from dual;
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
l_pd_line_editable_flag(l_row_number) := 'N'; -- cannot edit inserted row
insert into pa_fin_vp_amts_view_tmp
(project_id,
task_id,
resource_list_member_id,
element_name,
element_level,
labor_hours,
burdened_cost,
raw_cost,
revenue,
margin,
margin_percent,
editable_flag,
row_level,
parent_element_name,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
unit_of_measure,
has_child_element) values
(l_project_id(i),
l_task_id(i),
l_resource_list_member_id(i),
l_element_name(i),
l_element_level(i),
nvl(l_labor_hours(i), 0),
null, -- burdened_cost
null, -- raw_cost
nvl(l_revenue(i), 0),
null, -- margin
null, -- margin_percent
l_line_editable_flag(i),
l_row_level(i),
l_parent_element_name(i),
-1,
l_res_assignment_id(i),
-1,
l_unit_of_measure(i),
l_has_child_element(i));
insert into pa_fin_vp_pds_view_tmp
(project_id,
task_id,
resource_list_member_id,
uom,
element_name,
element_level,
editable_flag,
row_level,
parent_element_name,
amount_type,
amount_subtype,
amount_type_id,
amount_subtype_id,
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,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
preceding_periods_amount,
succeeding_periods_amount,
has_child_element,
project_total) values
(l_pd_project_id(z),
l_pd_task_id(z),
l_pd_resource_list_member_id(z),
l_pd_unit_of_measure(z),
l_pd_element_name(z),
l_pd_element_level(z),
l_pd_line_editable_flag(z),
l_pd_row_level(z),
l_pd_parent_element_name(z),
l_pd_amount_type(z),
l_pd_amount_subtype(z),
l_pd_amount_type_id(z),
l_pd_amount_subtype_id(z),
nvl(l_pd_period_1(z), 0),
nvl(l_pd_period_2(z), 0),
nvl(l_pd_period_3(z), 0),
nvl(l_pd_period_4(z), 0),
nvl(l_pd_period_5(z), 0),
nvl(l_pd_period_6(z), 0),
nvl(l_pd_period_7(z), 0),
nvl(l_pd_period_8(z), 0),
nvl(l_pd_period_9(z), 0),
nvl(l_pd_period_10(z), 0),
nvl(l_pd_period_11(z), 0),
nvl(l_pd_period_12(z), 0),
nvl(l_pd_period_13(z), 0),
-1,
l_pd_res_assignment_id(z),
-1,
l_pd_preceding(z),
l_pd_succeeding(z),
l_pd_has_child_element(z),
l_pd_project_total(z));
select 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
into
t_period_amount1,
t_period_amount2,
t_period_amount3,
t_period_amount4,
t_period_amount5,
t_period_amount6,
t_period_amount7,
t_period_amount8,
t_period_amount9,
t_period_amount10,
t_period_amount11,
t_period_amount12,
t_period_amount13
from pa_fin_vp_pds_view_tmp
where rowid = rollup_rec.rowid;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is not null
and p.element_name = rollup_rec.parent_element_name
and p.element_name is not null;
update pa_fin_vp_pds_view_tmp p
set p.period_amount1 = nvl(p.period_amount1, 0)+nvl(t_period_amount1,0),
p.period_amount2 = nvl(p.period_amount2, 0)+nvl(t_period_amount2, 0),
p.period_amount3 = nvl(p.period_amount3, 0)+nvl(t_period_amount3, 0),
p.period_amount4 = nvl(p.period_amount4, 0)+nvl(t_period_amount4, 0),
p.period_amount5 = nvl(p.period_amount5, 0)+nvl(t_period_amount5, 0),
p.period_amount6 = nvl(p.period_amount6, 0)+nvl(t_period_amount6, 0),
p.period_amount7 = nvl(p.period_amount7, 0)+nvl(t_period_amount7, 0),
p.period_amount8 = nvl(p.period_amount8, 0)+nvl(t_period_amount8, 0),
p.period_amount9 = nvl(p.period_amount9, 0)+nvl(t_period_amount9, 0),
p.period_amount10 = nvl(p.period_amount10, 0)+nvl(t_period_amount10, 0),
p.period_amount11 = nvl(p.period_amount11, 0)+nvl(t_period_amount11, 0),
p.period_amount12 = nvl(p.period_amount12, 0)+nvl(t_period_amount12, 0),
p.period_amount13 = nvl(p.period_amount13, 0)+nvl(t_period_amount13, 0)
where p.row_level < rollup_rec.row_level
and p.amount_subtype = rollup_rec.amount_subtype
and rollup_rec.element_name is null
and p.element_name is null
and (p.project_id,p.task_id, p.resource_list_member_id,row_level)
in (select p1.project_id, p1.task_id, p1.resource_list_member_id, p1.row_level
from pa_fin_vp_pds_view_tmp p1
where p1.element_name = (select p2.parent_element_name from
pa_fin_vp_pds_view_tmp p2
where p2.row_level = rollup_rec.row_level
and p2.element_name is not null
and p2.project_id = rollup_rec.project_id
and p2.task_id = rollup_rec.task_id
and p2.resource_list_member_id = rollup_rec.resource_list_member_id
and rownum<2
)
);
select ra.task_id -- doesn't matter what we select here
from pa_resource_assignments ra,
pa_tasks t
where (ra.budget_version_id in (p_budget_version_id1, p_budget_version_id2) and
ra.task_id=0 and
not (ra.resource_list_member_id in (0,pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id))) or
(ra.budget_version_id in (p_budget_version_id1, p_budget_version_id2) and
ra.task_id <> 0 and
ra.task_id = t.task_id and
t.parent_task_id is null);
select 1 from dual where exists (
select ra.task_id -- doesn't matter what we select here
from pa_resource_assignments ra
where ra.budget_version_id in (p_budget_version_id1, p_budget_version_id2)
and (
( ra.task_id=0
and not (ra.resource_list_member_id in (0,pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id)))
or
(exists (select 1
from pa_tasks pt
where pt.task_id = pt.top_task_id
and pt.task_id = ra.task_id))
));
select ra.task_id
from pa_resource_assignments ra,
pa_tasks t
where (ra.budget_version_id in(p_budget_version_id1, p_budget_version_id2) and
ra.task_id=p_task_id and
not (ra.resource_list_member_id in (0,pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id))) or
(ra.budget_version_id in (p_budget_version_id1, p_budget_version_id2) and
ra.task_id = t.task_id and t.parent_task_id = p_task_id);
select 1 from dual where exists (
select ra.task_id
from pa_resource_assignments ra
where ra.budget_version_id in(p_budget_version_id1, p_budget_version_id2) and
(
(ra.task_id=p_task_id and
not (ra.resource_list_member_id in
(0,pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id)
)
)
or
(exists (select 1
from pa_tasks t
where ra.task_id = t.task_id
and t.parent_task_id = p_task_id)
)
));
select resource_list_member_id
from pa_resource_list_members
where parent_member_id = p_resource_list_member_id;
select nvl(parent_member_id, -99),
resource_list_id
into l_res_parent_member_id,
l_resource_list_id
from pa_resource_list_members
where resource_list_member_id = p_resource_list_member_id;