[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 05/18/2004 dlai In the select statements to populate x_current_version_id and x_original_version_id,
* joined str.element_version_id to bv.project_structure_version_id instead of
* str.pev_structure_id to bv.project_structure_version_id (bug 3622609)
*/
/* This procedure should be used for the Workplan Task Details page ONLY!
*/
--These variables are internally used by the API get_fin_struct_id. They should not be used by other
--APIs in this package. These are created for bug 3546208
l_edit_plan_project_id pa_projects_all.project_id%TYPE;
select evs.element_version_id -- changed by shyugen
into l_wp_structure_version_id
from pa_proj_element_versions ev,
pa_proj_elem_ver_structure evs
where ev.project_id = p_project_id and
ev.element_version_id = p_element_version_id and
ev.project_id = evs.project_id and -- Added for perf fix - 3961665
ev.parent_structure_version_id = evs.element_version_id;
select budget_version_id
into x_current_version_id
from pa_budget_versions
where project_id = p_project_id and
wp_version_flag = 'Y' and
project_structure_version_id = l_wp_structure_version_id;
select bv.budget_version_id
into x_baselined_version_id
from pa_proj_elem_ver_structure str,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2,
pa_budget_versions bv
where ppev1.element_version_id = p_element_version_id
and ppev1.project_id = ppev2.project_id
and ppev1.proj_element_id = ppev2.proj_element_id -- all the other task versions
and ppev2.parent_structure_version_id = str.element_version_id --the structure version of each task version
and ppev2.project_id = str.project_id
and str.current_flag = 'Y' --the baselined structure version
and str.element_version_id = bv.project_structure_version_id
and bv.wp_version_flag = 'Y';
select bv.budget_version_id
into x_published_version_id
from pa_proj_elem_ver_structure str,
pa_proj_element_versions ppev1,
pa_proj_element_versions ppev2,
pa_budget_versions bv
where ppev1.element_version_id = p_element_version_id
and ppev1.project_id = ppev2.project_id
and ppev1.proj_element_id = ppev2.proj_element_id -- all the other task versions
and ppev2.parent_structure_version_id = str.element_version_id --the structure version of each task version
and ppev2.project_id = str.project_id
and latest_eff_published_flag = 'Y' --the structure version which is latest published
and str.element_version_id = bv.project_structure_version_id
and bv.wp_version_flag = 'Y';
select pt.plan_class_code,
po.fin_plan_preference_code
into l_plan_class_code,
l_fin_plan_pref_code
from pa_budget_versions bv,
pa_fin_plan_types_b pt,
pa_proj_fp_options po
where bv.budget_version_id = p_budget_version_id and
bv.fin_plan_type_id = pt.fin_plan_type_id and
bv.budget_version_id = po.fin_plan_version_id;
select bv.budget_version_id
into x_current_version_id
from pa_proj_fp_options po,
pa_budget_versions bv,
pa_proj_fp_options po2
where po.project_id = p_project_id and
po.fin_plan_version_id = p_budget_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.project_id = po2.project_id and
po.fin_plan_type_id = po2.fin_plan_type_id and
po.fin_plan_preference_code = po2.fin_plan_preference_code and
po2.fin_plan_option_level_code = 'PLAN_VERSION' and
po2.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id
into x_original_version_id
from pa_proj_fp_options po,
pa_budget_versions bv,
pa_proj_fp_options po2
where po.project_id = p_project_id and
po.fin_plan_version_id = p_budget_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.project_id = po2.project_id and
po.fin_plan_type_id = po2.fin_plan_type_id and
po.fin_plan_preference_code = po2.fin_plan_preference_code and
po2.fin_plan_option_level_code = 'PLAN_VERSION' and
po2.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y'; -- bug fix 3630207
select bv.budget_version_id
into x_prior_fcst_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.primary_cost_forecast_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id
into x_prior_fcst_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.primary_rev_forecast_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id
into x_prior_fcst_version_id
from pa_proj_fp_options po,
pa_budget_versions bv,
pa_proj_fp_options po2
where po.project_id = p_project_id and
po.fin_plan_version_id = p_budget_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
po.project_id = po2.project_id and
po.fin_plan_type_id = po2.fin_plan_type_id and
po.fin_plan_preference_code = po2.fin_plan_preference_code and
po2.fin_plan_option_level_code = 'PLAN_VERSION' and
po2.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id
into x_original_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_cost_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y';
select bv.budget_version_id
into x_original_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_rev_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y';
select bv.budget_version_id
into x_original_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_cost_plan_type_flag = 'Y' and
bv.approved_rev_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_original_flag = 'Y';
select bv.budget_version_id
into x_current_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_cost_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select bv.budget_version_id
into x_current_version_id
from pa_proj_fp_options po,
pa_budget_versions bv
where po.project_id = p_project_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bv.approved_rev_plan_type_flag = 'Y' and
po.fin_plan_version_id = bv.budget_version_id and
bv.current_flag = 'Y';
select pa.name || ' (' || pa.segment1 || ')'
into l_return_value
from pa_resource_assignments ra,
pa_projects_all pa
where ra.resource_assignment_id = p_resource_assignment_id and
ra.project_id = pa.project_id;
select pe.name || ' (' || pe.element_number || ')'
into l_return_value
from pa_resource_assignments ra,
pa_proj_elements pe
where ra.resource_assignment_id = p_resource_assignment_id and
ra.task_id = pe.proj_element_id;
* retrieved!. Since the selects which use this function in the below api will
* always return one record, removed the local variable for structure version id
* and included direct function call for retrieving fin struct ver id in the
* selects. */
-- Bug 4057673. Added a parameter p_fin_plan_level_code. It will be either 'P','L' or 'M'
--depending on the planning level of the budget version
FUNCTION get_project_task_level
(p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE) return VARCHAR2 is
l_project_id pa_resource_assignments.project_id%TYPE;
select object_id_to1 from pa_object_relationships
where object_id_to1 = l_element_version_id and
object_id_from1 = PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(l_project_id)
and object_type_from = 'PA_STRUCTURES'
and relationship_type = 'S';
SELECT 'x'
FROM pa_object_relationships
WHERE object_id_to1 = l_element_version_id and
rownum < 2 and
object_type_from = 'PA_TASKS';
select pelm.element_version_id,
ra.resource_list_member_id,
ra.project_id
into l_element_version_id,
l_rlm_id,
l_project_id
from pa_resource_assignments ra,
pa_proj_element_versions pelm
where ra.resource_assignment_id = p_resource_assignment_id
AND pelm.proj_element_id(+)=ra.task_id
AND pelm.parent_structure_Version_id(+)=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(ra.project_id);
select name
into l_return_value
from pa_resource_classes_vl
where resource_class_code = p_res_class_code;
select name
into l_return_value
from pa_res_types_vl
where res_type_code = p_res_type_code;
select meaning
into l_return_value
-- Bug Fix 4452472
-- replaced the _vl with _tl
-- from pa_project_role_types_vl
from pa_project_role_types_tl
where project_role_id = p_project_role_id and
language = userenv('LANG');
select vendor_name
into l_return_value
from po_vendors
where vendor_id = p_supplier_id;
select assignment_name
into l_return_value
from pa_project_assignments
where assignment_id = p_proj_assignment_id;
select name
into l_return_value
-- Bug Fix 4452472
-- replaced the _vl with _tl
-- from pa_spread_curves_vl
from pa_spread_curves_tl
where spread_curve_id = p_spread_curve_id and
language = userenv('LANG');
select cost_type
into l_return_value
from cst_cost_types_v
where cost_type_id = p_mfc_cost_type_id;
select ra.resource_assignment_id
from pa_resource_assignments ra
where ra.project_id = l_project_id and
ra.budget_version_id = p_budget_version_id and
ra.task_id = l_task_id and
ra.resource_list_member_id = l_resource_list_member_id and
ra.unit_of_measure = l_unit_of_measure;
select 'Y'
from pa_budget_lines
where resource_assignment_id = l_resource_assignment_id and
txn_currency_code = p_txn_currency_code;
select ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
SUM(bl.quantity),
SUM(bl.txn_revenue),
SUM(bl.project_revenue), --ra.total_project_revenue,
SUM(bl.revenue), --ra.total_plan_revenue,
SUM(bl.txn_raw_cost),
SUM(bl.project_raw_cost), --ra.total_project_raw_cost,
SUM(bl.raw_cost), --ra.total_plan_raw_cost,
SUM(bl.txn_burdened_cost),
SUM(bl.project_burdened_cost), --ra.total_project_burdened_cost,
SUM(bl.burdened_cost), --ra.total_plan_burdened_cost,
null, -- x_init_rev_rate (TO BE CALCULATED)
DECODE(SUM(bl.quantity),
0, 0,
null, null,
SUM(bl.txn_revenue)/SUM(bl.quantity)),
null, -- x_init_raw_cost_rate (TO BE CALCULATED)
DECODE(SUM(bl.quantity),
0, 0,
null, null,
SUM(bl.txn_raw_cost)/SUM(bl.quantity)),
null, -- x_init_burd_cost_rate (TO BE CALCULATED)
DECODE(SUM(bl.quantity),
0, 0,
null, null,
SUM(bl.txn_burdened_cost)/SUM(bl.quantity)),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', to_number(null),
'REVENUE_ONLY', to_number(null),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', to_number(null),
'REVENUE_ONLY', to_number(null),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
SUM(bl.project_revenue) - SUM(bl.project_raw_cost))),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', to_number(null),
'REVENUE_ONLY', to_number(null),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
SUM(bl.revenue) - SUM(bl.raw_cost))),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', to_number(null),
'REVENUE_ONLY', to_number(null),
DECODE(SUM(bl.project_revenue),
0, 0,
null, to_number(null),
DECODE(po.margin_derived_from_code,
'B', 100*(SUM(bl.project_revenue) - SUM(bl.project_burdened_cost))/SUM(bl.project_revenue),
100*(SUM(bl.project_revenue) - SUM(bl.project_raw_cost)))/SUM(bl.project_revenue))),
DECODE(SUM(bl.quantity) - SUM(nvl(bl.init_quantity,0)),
0, 0,
null, 0,
(SUM(bl.txn_revenue) - SUM(nvl(bl.txn_init_revenue,0)))/(SUM(bl.quantity) - SUM(nvl(bl.init_quantity,0)))),
DECODE(SUM(bl.quantity) - SUM(nvl(init_quantity,0)),
0, 0,
null, 0,
(SUM(bl.txn_raw_cost) - SUM(nvl(bl.txn_init_raw_cost,0)))/(SUM(bl.quantity) - SUM(nvl(init_quantity,0)))),
DECODE(SUM(bl.quantity) - SUM(nvl(init_quantity,0)),
0, 0,
null, 0,
(SUM(bl.txn_burdened_cost) - SUM(nvl(bl.txn_init_burdened_cost,0)))/(SUM(bl.quantity) - SUM(nvl(init_quantity,0))))
from pa_resource_assignments ra,
pa_budget_lines bl,
pa_budget_versions bv,
pa_proj_fp_options po
where ra.resource_assignment_id = l_resource_assignment_id and
ra.resource_assignment_id = bl.resource_assignment_id and
bl.txn_currency_code = p_txn_currency_code and
ra.budget_version_id = bv.budget_version_id and
bv.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION' and
bl.start_date BETWEEN p_line_start_date and p_line_end_date
group by bl.resource_assignment_id,
bl.txn_currency_code,
ra.planning_start_date,
ra.planning_end_date,
po.margin_derived_from_code,
ra.schedule_start_date,
ra.schedule_end_date,
po.fin_plan_preference_code;
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.unit_of_measure
into l_project_id,
l_task_id,
l_resource_list_member_id,
l_unit_of_measure
from pa_resource_assignments ra
where ra.resource_assignment_id = p_resource_assignment_id;
select ra.planning_start_date, -- x_planning_start_date
ra.planning_end_date, -- x_planning_end_date
ra.schedule_start_date, -- x_schedule_start_date
ra.schedule_end_date, -- x_schedule_end_date
to_number(null), -- x_quantity
/*
DECODE(p_txn_currency_code,
p_project_currency_code, ra.total_plan_quantity,
p_projfunc_currency_code, ra.total_plan_quantity,
to_number(null)), -- x_quantity
*/
to_number(null), -- x_revenue_txn_cur
/*
DECODE(p_txn_currency_code,
p_project_currency_code, ra.total_project_revenue,
p_projfunc_currency_code, ra.total_plan_revenue,
to_number(null)), -- x_revenue_txn_cur
*/
to_number(null), /*ra.total_project_revenue,*/ -- x_revenue_proj_cur
to_number(null), /*ra.total_plan_revenue,*/ -- x_revenue_proj_func_cur
to_number(null), -- x_raw_cost_txn_cur
/*
DECODE(p_txn_currency_code,
p_project_currency_code, ra.total_project_raw_cost,
p_projfunc_currency_code, ra.total_plan_raw_cost,
to_number(null)), -- x_raw_cost_txn_cur
*/
to_number(null), /*ra.total_project_raw_cost,*/ -- x_raw_cost_proj_cur
to_number(null), /*ra.total_plan_raw_cost,*/ -- x_raw_cost_proj_func_cur
to_number(null), -- x_burd_cost_txn_cur
/*
DECODE(p_txn_currency_code,
p_project_currency_code, ra.total_project_burdened_cost,
p_projfunc_currency_code, ra.total_plan_burdened_cost,
to_number(null)), -- x_burd_cost_txn_cur
*/
to_number(null), /*ra.total_project_burdened_cost,*/ -- x_burd_cost_proj_cur
to_number(null), /*ra.total_plan_burdened_cost,*/ -- x_burd_cost_proj_func_cur
to_number(null), -- x_margin_txn_cur
/*
DECODE(p_txn_currency_code,
p_project_currency_code, DECODE(po.margin_derived_from_code,
'B', ra.total_project_revenue - ra.total_project_burdened_cost,
ra.total_project_revenue - ra.total_project_raw_cost),
p_projfunc_currency_code, DECODE(po.margin_derived_from_code,
'B', ra.total_plan_revenue - ra.total_plan_burdened_cost,
ra.total_plan_revenue - ra.total_plan_raw_cost),
to_number(null)), -- x_margin_txn_cur
*/
to_number(null),
/* DECODE(po.margin_derived_from_code,
'B', ra.total_project_revenue - ra.total_project_burdened_cost,
ra.total_project_revenue - ra.total_project_raw_cost), -- x_margin_proj_cur
*/
to_number(null),
/* DECODE(po.margin_derived_from_code,
'B', ra.total_plan_revenue - ra.total_plan_burdened_cost,
ra.total_plan_revenue - ra.total_plan_raw_cost), -- x_margin_proj_func_cur
*/
DECODE(po.fin_plan_preference_code,
'COST_ONLY', to_number(null),
'REVENUE_ONLY', to_number(null),
DECODE(nvl(ra.total_project_revenue,0),
0, 0,
DECODE(po.margin_derived_from_code,
'B', 100*(ra.total_project_revenue - ra.total_project_burdened_cost)/ra.total_project_revenue,
100*(ra.total_project_revenue - ra.total_project_raw_cost)/ra.total_project_revenue))) -- x_margin_pct
into x_planning_start_date,
x_planning_end_date,
x_schedule_start_date,
x_schedule_end_date,
x_quantity,
x_revenue_txn_cur,
x_revenue_proj_cur,
x_revenue_proj_func_cur,
x_raw_cost_txn_cur,
x_raw_cost_proj_cur,
x_raw_cost_proj_func_cur,
x_burd_cost_txn_cur,
x_burd_cost_proj_cur,
x_burd_cost_proj_func_cur,
x_margin_txn_cur,
x_margin_proj_cur,
x_margin_proj_func_cur,
x_margin_pct
from pa_resource_assignments ra,
pa_budget_versions bv,
pa_proj_fp_options po
where ra.resource_assignment_id = l_resource_assignment_id and
ra.budget_version_id = bv.budget_version_id and
bv.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION';
SELECT ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
rac.total_display_quantity,
rac.total_txn_revenue,
rac.total_project_revenue, --ra.total_project_revenue,
rac.total_projfunc_revenue, --ra.total_plan_revenue,
rac.total_txn_raw_cost,
rac.total_project_raw_cost, --ra.total_project_raw_cost,
rac.total_projfunc_raw_cost, --ra.total_plan_raw_cost,
rac.total_txn_burdened_cost,
rac.total_project_burdened_cost, --ra.total_project_burdened_cost,
rac.total_projfunc_burdened_cost, --ra.total_plan_burdened_cost,
/*
SUM(bl.quantity),
SUM(bl.txn_revenue),
SUM(bl.project_revenue), --ra.total_project_revenue,
SUM(bl.revenue), --ra.total_plan_revenue,
SUM(bl.txn_raw_cost),
SUM(bl.project_raw_cost), --ra.total_project_raw_cost,
SUM(bl.raw_cost), --ra.total_plan_raw_cost,
SUM(bl.txn_burdened_cost),
SUM(bl.project_burdened_cost), --ra.total_project_burdened_cost,
SUM(bl.burdened_cost), --ra.total_plan_burdened_cost,
*/
NULL, -- x_init_rev_rate (TO BE CALCULATED)
--AVG(nvl(bl.txn_bill_rate_override,bl.txn_standard_bill_rate)),
/*
DECODE(SUM(bl.quantity),
0, 0,
NULL, NULL,
SUM(bl.txn_revenue)/SUM(bl.quantity)),
*/
DECODE(ra.rate_based_flag, 'Y', rac.txn_average_bill_rate, TO_NUMBER(NULL)),
NULL, -- x_init_raw_cost_rate (TO BE CALCULATED)
--AVG(nvl(bl.txn_cost_rate_override,bl.txn_standard_cost_rate)),
/*
DECODE(SUM(bl.quantity),
0, 0,
NULL, NULL,
SUM(bl.txn_raw_cost)/SUM(bl.quantity)),
*/
DECODE(ra.rate_based_flag, 'Y', rac.txn_average_raw_cost_rate, TO_NUMBER(NULL)),
NULL, -- x_init_burd_cost_rate (TO BE CALCULATED)
--AVG(nvl(bl.burden_cost_rate_override,bl.burden_cost_rate)),
/*
DECODE(SUM(bl.quantity),
0, 0,
NULL, NULL,
SUM(bl.txn_burdened_cost)/SUM(bl.quantity)),
*/
DECODE(ra.rate_based_flag, 'Y', rac.txn_average_burden_cost_rate, TO_NUMBER(NULL)),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
-- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))),
'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
rac.total_txn_revenue - rac.total_txn_raw_cost)),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
-- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))),
'B', rac.total_project_revenue - rac.total_project_burdened_cost,
rac.total_project_revenue - rac.total_project_raw_cost)),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.reve nue) - SUM(bl.burdened_cost),
-- SUM(bl.revenue) - SUM(bl.raw_cost))),
'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)),
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
-- DECODE(SUM(bl.project_revenue),
DECODE(rac.total_project_revenue,
0, 0,
NULL, TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', 100*(SUM(bl.project_revenue) - SUM(bl.project_burdened_cost))/SUM(bl.project_revenue),
-- 100*(SUM(bl.project_revenue) - SUM(bl.project_raw_cost)))/SUM(bl.project_revenue))),
'B', 100*(rac.total_project_revenue - rac.total_project_burdened_cost)/rac.total_project_revenue,
100*(rac.total_project_revenue - rac.total_project_raw_cost)/rac.total_project_revenue))),
/*
DECODE(SUM(bl.quantity) - SUM(NVL(bl.init_quantity,0)),
0, 0,
NULL, 0,
(SUM(bl.txn_revenue) - SUM(NVL(bl.txn_init_revenue,0)))/(SUM(bl.quantity) - SUM(NVL(bl.init_quantity,0)))),
*/
DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_bill_rate, TO_NUMBER(NULL)),
/*
DECODE(SUM(bl.quantity) - SUM(NVL(init_quantity,0)),
0, 0,
NULL, 0,
(SUM(bl.txn_raw_cost) - SUM(NVL(bl.txn_init_raw_cost,0)))/(SUM(bl.quantity) - SUM(NVL(init_quantity,0)))),
*/
DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_raw_cost_rate, TO_NUMBER(NULL)),
/*
DECODE(SUM(bl.quantity) - SUM(NVL(init_quantity,0)),
0, 0,
NULL, 0,
(SUM(bl.txn_burdened_cost) - SUM(NVL(bl.txn_init_burdened_cost,0)))/(SUM(bl.quantity) - SUM(NVL(init_quantity,0))))
*/
DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_burden_cost_rate, TO_NUMBER(NULL))
INTO x_planning_start_date,
x_planning_end_date,
x_schedule_start_date,
x_schedule_end_date,
x_quantity,
x_revenue_txn_cur,
x_revenue_proj_cur,
x_revenue_proj_func_cur,
x_raw_cost_txn_cur,
x_raw_cost_proj_cur,
x_raw_cost_proj_func_cur,
x_burd_cost_txn_cur,
x_burd_cost_proj_cur,
x_burd_cost_proj_func_cur,
x_init_rev_rate,
x_avg_rev_rate,
x_init_raw_cost_rate,
x_avg_raw_cost_rate,
x_init_burd_cost_rate,
x_avg_burd_cost_rate,
x_margin_txn_cur,
x_margin_proj_cur,
x_margin_proj_func_cur,
x_margin_pct,
x_etc_avg_rev_rate,
x_etc_avg_raw_cost_rate,
x_etc_avg_burd_cost_rate
FROM pa_resource_assignments ra,
-- pa_budget_lines bl,
pa_resource_asgn_curr rac,
pa_budget_versions bv,
pa_proj_fp_options po
WHERE ra.resource_assignment_id = l_resource_assignment_id AND
-- ra.resource_assignment_id = bl.resource_assignment_id AND
-- bl.txn_currency_code = p_txn_currency_code AND
rac.resource_assignment_id = ra.resource_assignment_id AND
rac.txn_currency_code = p_txn_currency_code AND
ra.budget_version_id = bv.budget_version_id AND
bv.budget_version_id = po.fin_plan_version_id AND
po.fin_plan_option_level_code = 'PLAN_VERSION';
select ra.resource_assignment_id
from pa_resource_assignments ra
where ra.project_id = l_project_id and
ra.budget_version_id = p_budget_version_id and
ra.task_id = l_task_id and
ra.resource_list_member_id = l_resource_list_member_id and
ra.unit_of_measure = l_unit_of_measure;
select 'Y'
from pa_budget_lines
where resource_assignment_id = l_resource_assignment_id and
txn_currency_code = p_txn_currency_code;
SELECT ra.planning_start_date, -- x_planning_start_date
ra.planning_end_date, -- x_planning_end_date
ra.schedule_start_date, -- x_schedule_start_date
ra.schedule_end_date, -- x_schedule_end_date
/* rac.total_init_quantity, -- x_act_quantity
DECODE(ra.rate_based_flag, 'Y', NVL(rac.total_display_quantity,0) - NVL(rac.total_init_quantity,0), 0), -- x_etc_quantity, Bug 5726773
rac.total_display_quantity, -- x_fcst_quantity
rac.total_txn_init_revenue, -- x_act_revenue_txn_cur
rac.total_project_init_revenue, -- x_act_revenue_proj_cur
rac.total_projfunc_init_revenue, -- x_act_revenue_proj_func_cur
NVL(rac.total_txn_revenue,0) - NVL(rac.total_txn_init_revenue,0), -- x_etc_revenue_txn_cur
NVL(rac.total_project_revenue,0) - NVL(rac.total_project_init_revenue,0), -- x_etc_revenue_proj_cur
NVL(rac.total_projfunc_revenue,0) - NVL(rac.total_projfunc_init_revenue,0), -- x_etc_revenue_proj_func_cur
rac.total_txn_revenue, -- x_fcst_revenue_txn_cur
rac.total_project_revenue, -- x_fcst_revenue_proj_cur
rac.total_projfunc_revenue, -- x_fcst_revenue_proj_func_cur
rac.total_txn_init_raw_cost, -- x_act_raw_cost_txn_cur
rac.total_project_init_raw_cost, -- x_act_raw_cost_proj_cur
rac.total_projfunc_init_raw_cost, -- x_act_raw_cost_proj_func_cur
NVL(rac.total_txn_raw_cost,0) - NVL(rac.total_txn_init_raw_cost,0), -- x_etc_raw_cost_txn_cur
NVL(rac.total_project_raw_cost,0) - NVL(rac.total_project_init_raw_cost,0),-- x_etc_raw_cost_proj_cur
NVL(rac.total_projfunc_raw_cost,0) - NVL(rac.total_projfunc_init_raw_cost,0),-- x_etc_raw_cost_proj_func_cur
rac.total_txn_raw_cost, -- x_fcst_raw_cost_txn_cur
rac.total_project_raw_cost, -- x_fcst_raw_cost_proj_cur
rac.total_projfunc_raw_cost, -- x_fcst_raw_cost_proj_func_cur
rac.total_txn_init_burdened_cost, -- x_act_burd_cost_txn_cur
rac.total_project_init_bd_cost, -- x_act_burd_cost_proj_cur
rac.total_projfunc_init_bd_cost, -- x_act_burd_cost_proj_func_cur
NVL(rac.total_txn_burdened_cost,0) - NVL(rac.total_txn_init_burdened_cost,0), -- x_etc_burd_cost_txn_cur
NVL(rac.total_project_burdened_cost,0) - NVL(rac.total_project_init_bd_cost,0), -- x_etc_burd_cost_proj_cur
NVL(rac.total_projfunc_burdened_cost,0) - NVL(rac.total_projfunc_init_bd_cost,0), -- x_etc_burd_cost_proj_func_cur
rac.total_txn_burdened_cost, -- x_fcst_burd_cost_txn_cur
rac.total_project_burdened_cost, -- x_fcst_burd_cost_proj_cur
rac.total_projfunc_burdened_cost, -- x_fcst_burd_cost_proj_func_cur
*/
SUM(bl.init_quantity), -- x_act_quantity
SUM(NVL(bl.quantity,0)) - SUM(NVL(bl.init_quantity,0)), -- x_etc_quantity
SUM(bl.quantity), -- x_fcst_quantity
SUM(txn_init_revenue), -- x_act_revenue_txn_cur
SUM(bl.project_init_revenue), -- x_act_revenue_proj_cur
SUM(bl.init_revenue), -- x_act_revenue_proj_func_cur
SUM(NVL(bl.txn_revenue,0)) - SUM(NVL(bl.txn_init_revenue,0)), -- x_etc_revenue_txn_cur
SUM(NVL(bl.project_revenue,0)) - SUM(NVL(bl.project_init_revenue,0)), -- x_etc_revenue_proj_cur
SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.init_revenue,0)), -- x_etc_revenue_proj_func_cur
SUM(bl.txn_revenue), -- x_fcst_revenue_txn_cur
SUM(bl.project_revenue), -- x_fcst_revenue_proj_cur
SUM(bl.revenue), -- x_fcst_revenue_proj_func_cur
SUM(bl.txn_init_raw_cost), -- x_act_raw_cost_txn_cur
SUM(bl.project_init_raw_cost), -- x_act_raw_cost_proj_cur
SUM(bl.init_raw_cost), -- x_act_raw_cost_proj_func_cur
SUM(NVL(bl.txn_raw_cost,0)) - SUM(NVL(bl.txn_init_raw_cost,0)), -- x_etc_raw_cost_txn_cur
SUM(NVL(bl.project_raw_cost,0)) - SUM(NVL(bl.project_init_raw_cost,0)), -- x_etc_raw_cost_proj_cur
SUM(NVL(bl.raw_cost,0)) - SUM(NVL(bl.init_raw_cost,0)), -- x_etc_raw_cost_proj_func_cur
SUM(bl.txn_raw_cost), -- x_fcst_raw_cost_txn_cur
SUM(bl.project_raw_cost), -- x_fcst_raw_cost_proj_cur
SUM(bl.raw_cost), -- x_fcst_raw_cost_proj_func_cur
SUM(bl.txn_init_burdened_cost), -- x_act_burd_cost_txn_cur
SUM(bl.project_init_burdened_cost), -- x_act_burd_cost_proj_cur
SUM(bl.init_burdened_cost), -- x_act_burd_cost_proj_func_cur
SUM(NVL(bl.txn_burdened_cost,0)) - SUM(NVL(bl.txn_init_burdened_cost,0)), -- x_etc_burd_cost_txn_cur
SUM(NVL(bl.project_burdened_cost,0)) - SUM(NVL(bl.project_init_burdened_cost,0)), -- x_etc_burd_cost_proj_cur
SUM(NVL(bl.burdened_cost,0)) - SUM(NVL(bl.init_burdened_cost,0)), -- x_etc_burd_cost_proj_func_cur
SUM(bl.txn_burdened_cost), -- x_fcst_burd_cost_txn_cur
SUM(bl.project_burdened_cost), -- x_fcst_burd_cost_proj_cur
SUM(bl.burdened_cost), -- x_fcst_burd_cost_proj_func_cur
NULL, -- x_act_rev_rate (TO BE CALCULATED)
NULL, -- x_etc_init_rev_rate (TO BE CALCULATED)
NULL, -- x_etc_avg_rev_rate (TO BE CALCULATED)
NULL, -- x_act_raw_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_init_raw_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_avg_raw_cost_rate (TO BE CALCULATED)
NULL, -- x_act_burd_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_init_burd_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_avg_burd_cost_rate (TO BE CALCULATED)
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.txn_init_revenue) - SUM(bl.txn_init_burdened_cost),
SUM(bl.txn_init_revenue) - SUM(bl.txn_init_raw_cost))), -- x_act_margin_txn_cur
-- 'B', rac.total_txn_init_revenue - rac.total_txn_init_burdened_cost,
-- rac.total_txn_init_revenue - rac.total_txn_init_raw_cost)), -- x_act_margin_txn_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.project_init_revenue) - SUM(bl.project_init_burdened_cost),
SUM(bl.project_init_revenue) - SUM(bl.project_init_raw_cost))), -- x_act_margin_proj_cur
-- 'B', rac.total_project_init_revenue - rac.total_project_init_bd_cost,
-- rac.total_project_init_revenue - rac.total_project_init_raw_cost)), -- x_act_margin_proj_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.init_revenue) - SUM(bl.init_burdened_cost),
SUM(bl.init_revenue) - SUM(bl.init_raw_cost))), -- x_act_margin_proj_func_cur
-- 'B', rac.total_projfunc_init_revenue - rac.total_projfunc_init_bd_cost,
-- rac.total_projfunc_init_revenue - rac.total_projfunc_init_raw_cost)), -- x_act_margin_proj_func_cur
NULL, -- x_etc_margin_txn_cur (TO BE POPULATED)
NULL, -- x_etc_margin_proj_cur (TO BE POPULATED)
NULL, -- x_etc_margin_proj_func_cur (TO BE POPULATED)
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))), -- x_fcst_margin_txn_cur
-- 'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
-- rac.total_txn_revenue - rac.total_txn_raw_cost)), -- x_fcst_margin_txn_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
SUM(bl.project_revenue) - SUM(bl.project_raw_cost))), -- x_fcst_margin_proj_cur
-- 'B', rac.total_project_revenue - rac.total_project_burdened_cost,
-- rac.total_project_revenue - rac.total_project_raw_cost)), -- x_fcst_margin_proj_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
SUM(bl.revenue) - SUM(bl.raw_cost))), -- x_fcst_margin_proj_func_cur
-- 'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
-- rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)), -- x_fcst_margin_proj_func_cur
NULL, -- x_act_margin_pct (TO BE POPULATED)
NULL, -- x_etc_margin_pct (TO BE POPULATED)
NULL, -- x_fcst_margin_pct (TO BE POPULATED)
po.margin_derived_from_code
FROM pa_resource_assignments ra,
pa_budget_lines bl,
-- pa_resource_asgn_curr rac,
pa_budget_versions bv,
pa_proj_fp_options po
WHERE ra.resource_assignment_id = l_resource_assignment_id AND
ra.resource_assignment_id = bl.resource_assignment_id AND
bl.txn_currency_code = p_txn_currency_code AND
-- ra.resource_assignment_id = rac.resource_assignment_id AND
-- rac.txn_currency_code = p_txn_currency_code AND
ra.budget_version_id = bv.budget_version_id AND
bv.budget_version_id = po.fin_plan_version_id AND
po.fin_plan_option_level_code = 'PLAN_VERSION' AND
bl.start_date BETWEEN p_line_start_date AND p_line_end_date
GROUP BY ra.transaction_source_code,
-- ra.init_plan_quantity,
ra.total_plan_quantity,
bl.resource_assignment_id,
bl.txn_currency_code,
ra.planning_start_date,
ra.planning_end_date,
ra.schedule_start_date,
ra.schedule_end_date,
--ra.total_project_revenue,
--ra.total_plan_revenue,
--ra.total_project_raw_cost,
--ra.total_plan_raw_cost,
--ra.total_project_burdened_cost,
--ra.total_plan_burdened_cost,
po.margin_derived_from_code,
po.fin_plan_preference_code;
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.unit_of_measure
into l_project_id,
l_task_id,
l_resource_list_member_id,
l_unit_of_measure
from pa_resource_assignments ra
where ra.resource_assignment_id = p_resource_assignment_id;
select ra.planning_start_date, -- x_planning_start_date
ra.planning_end_date, -- x_planning_end_date
ra.schedule_start_date, -- x_schedule_start_date
ra.schedule_end_date, -- x_schedule_end_date
to_number(null), -- x_act_quantity
to_number(null), -- x_etc_quantity
to_number(null), -- x_fcst_quantity
to_number(null), -- x_act_revenue_txn_cur
to_number(null), -- x_act_revenue_proj_cur
to_number(null), -- x_act_revenue_proj_func_cur
to_number(null), -- x_etc_revenue_txn_cur
to_number(null), -- x_etc_revenue_proj_cur
to_number(null), -- x_etc_revenue_proj_func_cur
to_number(null), -- x_fcst_revenue_txn_cur
to_number(null), -- x_fcst_revenue_proj_cur
to_number(null), -- x_fcst_revenue_proj_func_cur
to_number(null), -- x_act_raw_cost_txn_cur
to_number(null), -- x_act_raw_cost_proj_cur
to_number(null), -- x_act_raw_cost_proj_func_cur
to_number(null), -- x_etc_raw_cost_txn_cur
to_number(null), -- x_etc_raw_cost_proj_cur
to_number(null), -- x_etc_raw_cost_proj_func_cur
to_number(null), -- x_fcst_raw_cost_txn_cur
to_number(null), -- x_fcst_raw_cost_proj_cur
to_number(null), -- x_fcst_raw_cost_proj_func_cur
to_number(null), -- x_act_burd_cost_txn_cur
to_number(null), -- x_act_burd_cost_proj_cur
to_number(null), -- x_act_burd_cost_proj_func_cur
to_number(null), -- x_etc_burd_cost_txn_cur
to_number(null), -- x_etc_burd_cost_proj_cur
to_number(null), -- x_etc_burd_cost_proj_func_cur
to_number(null), -- x_fcst_burd_cost_txn_cur
to_number(null), -- x_fcst_burd_cost_proj_cur
to_number(null), -- x_fcst_burd_cost_proj_func_cur
to_number(null), -- x_act_rev_rate
to_number(null), -- x_etc_init_rev_rate
to_number(null), -- x_etc_avg_rev_rate,
to_number(null), -- x_act_raw_cost_rate
to_number(null), -- x_etc_init_raw_cost_rate
to_number(null), -- x_etc_avg_raw_cost_rate
to_number(null), -- x_act_burd_cost_rate
to_number(null), -- x_etc_init_burd_cost_rate
to_number(null), -- x_etc_avg_burd_cost_rate
to_number(null), -- x_act_margin_txn_cur
to_number(null), -- x_act_margin_proj_cur
to_number(null), -- x_act_margin_proj_func_cur
to_number(null), -- x_etc_margin_txn_cur
to_number(null), -- x_etc_margin_proj_cur
to_number(null), -- x_etc_margin_proj_func_cur
to_number(null), -- x_fcst_margin_txn_cur
to_number(null), -- x_fcst_margin_proj_cur
to_number(null), -- x_fcst_margin_proj_func_cur
to_number(null), -- x_act_margin_pct
to_number(null), -- x_etc_margin_pct
to_number(null), -- x_fcst_margin_pct
po.margin_derived_from_code
into x_planning_start_date,
x_planning_end_date,
x_schedule_start_date,
x_schedule_end_date,
x_act_quantity,
x_etc_quantity,
x_fcst_quantity,
x_act_revenue_txn_cur,
x_act_revenue_proj_cur,
x_act_revenue_proj_func_cur,
x_etc_revenue_txn_cur,
x_etc_revenue_proj_cur,
x_etc_revenue_proj_func_cur,
x_fcst_revenue_txn_cur,
x_fcst_revenue_proj_cur,
x_fcst_revenue_proj_func_cur,
x_act_raw_cost_txn_cur,
x_act_raw_cost_proj_cur,
x_act_raw_cost_proj_func_cur,
x_etc_raw_cost_txn_cur,
x_etc_raw_cost_proj_cur,
x_etc_raw_cost_proj_func_cur,
x_fcst_raw_cost_txn_cur,
x_fcst_raw_cost_proj_cur,
x_fcst_raw_cost_proj_func_cur,
x_act_burd_cost_txn_cur,
x_act_burd_cost_proj_cur,
x_act_burd_cost_proj_func_cur,
x_etc_burd_cost_txn_cur,
x_etc_burd_cost_proj_cur,
x_etc_burd_cost_proj_func_cur,
x_fcst_burd_cost_txn_cur,
x_fcst_burd_cost_proj_cur,
x_fcst_burd_cost_proj_func_cur,
x_act_rev_rate,
x_etc_init_rev_rate,
x_etc_avg_rev_rate,
x_act_raw_cost_rate,
x_etc_init_raw_cost_rate,
x_etc_avg_raw_cost_rate,
x_act_burd_cost_rate,
x_etc_init_burd_cost_rate,
x_etc_avg_burd_cost_rate,
x_act_margin_txn_cur,
x_act_margin_proj_cur,
x_act_margin_proj_func_cur,
x_etc_margin_txn_cur,
x_etc_margin_proj_cur,
x_etc_margin_proj_func_cur,
x_fcst_margin_txn_cur,
x_fcst_margin_proj_cur,
x_fcst_margin_proj_func_cur,
x_act_margin_pct,
x_etc_margin_pct,
x_fcst_margin_pct,
l_margin_derived_from_code
from pa_resource_assignments ra,
pa_budget_versions bv,
pa_proj_fp_options po
where ra.resource_assignment_id = l_resource_assignment_id and
ra.budget_version_id = bv.budget_version_id and
bv.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION';
SELECT ra.planning_start_date, -- x_planning_start_date
ra.planning_end_date, -- x_planning_end_date
ra.schedule_start_date, -- x_schedule_start_date
ra.schedule_end_date, -- x_schedule_end_date
rac.total_init_quantity, -- x_act_quantity
DECODE(ra.rate_based_flag, 'Y', NVL(rac.total_display_quantity,0) - NVL(rac.total_init_quantity,0), 0), -- x_etc_quantity, Bug 5726773
rac.total_display_quantity, -- x_fcst_quantity
rac.total_txn_init_revenue, -- x_act_revenue_txn_cur
rac.total_project_init_revenue, -- x_act_revenue_proj_cur
rac.total_projfunc_init_revenue, -- x_act_revenue_proj_func_cur
NVL(rac.total_txn_revenue,0) - NVL(rac.total_txn_init_revenue,0), -- x_etc_revenue_txn_cur
NVL(rac.total_project_revenue,0) - NVL(rac.total_project_init_revenue,0), -- x_etc_revenue_proj_cur
NVL(rac.total_projfunc_revenue,0) - NVL(rac.total_projfunc_init_revenue,0), -- x_etc_revenue_proj_func_cur
rac.total_txn_revenue, -- x_fcst_revenue_txn_cur
rac.total_project_revenue, -- x_fcst_revenue_proj_cur
rac.total_projfunc_revenue, -- x_fcst_revenue_proj_func_cur
rac.total_txn_init_raw_cost, -- x_act_raw_cost_txn_cur
rac.total_project_init_raw_cost, -- x_act_raw_cost_proj_cur
rac.total_projfunc_init_raw_cost, -- x_act_raw_cost_proj_func_cur
NVL(rac.total_txn_raw_cost,0) - NVL(rac.total_txn_init_raw_cost,0), -- x_etc_raw_cost_txn_cur
NVL(rac.total_project_raw_cost,0) - NVL(rac.total_project_init_raw_cost,0),-- x_etc_raw_cost_proj_cur
NVL(rac.total_projfunc_raw_cost,0) - NVL(rac.total_projfunc_init_raw_cost,0),-- x_etc_raw_cost_proj_func_cur
rac.total_txn_raw_cost, -- x_fcst_raw_cost_txn_cur
rac.total_project_raw_cost, -- x_fcst_raw_cost_proj_cur
rac.total_projfunc_raw_cost, -- x_fcst_raw_cost_proj_func_cur
rac.total_txn_init_burdened_cost, -- x_act_burd_cost_txn_cur
rac.total_project_init_bd_cost, -- x_act_burd_cost_proj_cur
rac.total_projfunc_init_bd_cost, -- x_act_burd_cost_proj_func_cur
NVL(rac.total_txn_burdened_cost,0) - NVL(rac.total_txn_init_burdened_cost,0), -- x_etc_burd_cost_txn_cur
NVL(rac.total_project_burdened_cost,0) - NVL(rac.total_project_init_bd_cost,0), -- x_etc_burd_cost_proj_cur
NVL(rac.total_projfunc_burdened_cost,0) - NVL(rac.total_projfunc_init_bd_cost,0), -- x_etc_burd_cost_proj_func_cur
rac.total_txn_burdened_cost, -- x_fcst_burd_cost_txn_cur
rac.total_project_burdened_cost, -- x_fcst_burd_cost_proj_cur
rac.total_projfunc_burdened_cost, -- x_fcst_burd_cost_proj_func_cur
/*
SUM(bl.init_quantity), -- x_act_quantity
SUM(NVL(bl.quantity,0)) - SUM(NVL(bl.init_quantity,0)), -- x_etc_quantity
SUM(bl.quantity), -- x_fcst_quantity
SUM(txn_init_revenue), -- x_act_revenue_txn_cur
SUM(bl.project_init_revenue), -- x_act_revenue_proj_cur
SUM(bl.init_revenue), -- x_act_revenue_proj_func_cur
SUM(NVL(bl.txn_revenue,0)) - SUM(NVL(bl.txn_init_revenue,0)), -- x_etc_revenue_txn_cur
SUM(NVL(bl.project_revenue,0)) - SUM(NVL(bl.project_init_revenue,0)), -- x_etc_revenue_proj_cur
SUM(NVL(bl.revenue,0)) - SUM(NVL(bl.init_revenue,0)), -- x_etc_revenue_proj_func_cur
SUM(bl.txn_revenue), -- x_fcst_revenue_txn_cur
SUM(bl.project_revenue), -- x_fcst_revenue_proj_cur
SUM(bl.revenue), -- x_fcst_revenue_proj_func_cur
SUM(bl.txn_init_raw_cost), -- x_act_raw_cost_txn_cur
SUM(bl.project_init_raw_cost), -- x_act_raw_cost_proj_cur
SUM(bl.init_raw_cost), -- x_act_raw_cost_proj_func_cur
SUM(NVL(bl.txn_raw_cost,0)) - SUM(NVL(bl.txn_init_raw_cost,0)), -- x_etc_raw_cost_txn_cur
SUM(NVL(bl.project_raw_cost,0)) - SUM(NVL(bl.project_init_raw_cost,0)), -- x_etc_raw_cost_proj_cur
SUM(NVL(bl.raw_cost,0)) - SUM(NVL(bl.init_raw_cost,0)), -- x_etc_raw_cost_proj_func_cur
SUM(bl.txn_raw_cost), -- x_fcst_raw_cost_txn_cur
SUM(bl.project_raw_cost), -- x_fcst_raw_cost_proj_cur
SUM(bl.raw_cost), -- x_fcst_raw_cost_proj_func_cur
SUM(bl.txn_init_burdened_cost), -- x_act_burd_cost_txn_cur
SUM(bl.project_init_burdened_cost), -- x_act_burd_cost_proj_cur
SUM(bl.init_burdened_cost), -- x_act_burd_cost_proj_func_cur
SUM(NVL(bl.txn_burdened_cost,0)) - SUM(NVL(bl.txn_init_burdened_cost,0)), -- x_etc_burd_cost_txn_cur
SUM(NVL(bl.project_burdened_cost,0)) - SUM(NVL(bl.project_init_burdened_cost,0)), -- x_etc_burd_cost_proj_cur
SUM(NVL(bl.burdened_cost,0)) - SUM(NVL(bl.init_burdened_cost,0)), -- x_etc_burd_cost_proj_func_cur
SUM(bl.txn_burdened_cost), -- x_fcst_burd_cost_txn_cur
SUM(bl.project_burdened_cost), -- x_fcst_burd_cost_proj_cur
SUM(bl.burdened_cost), -- x_fcst_burd_cost_proj_func_cur
*/
NULL, -- x_act_rev_rate (TO BE CALCULATED)
NULL, -- x_etc_init_rev_rate (TO BE CALCULATED)
NULL, -- x_etc_avg_rev_rate (TO BE CALCULATED)
NULL, -- x_act_raw_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_init_raw_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_avg_raw_cost_rate (TO BE CALCULATED)
NULL, -- x_act_burd_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_init_burd_cost_rate (TO BE CALCULATED)
NULL, -- x_etc_avg_burd_cost_rate (TO BE CALCULATED)
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.txn_init_revenue) - SUM(bl.txn_init_burdened_cost),
-- SUM(bl.txn_init_revenue) - SUM(bl.txn_init_raw_cost))), -- x_act_margin_txn_cur
'B', rac.total_txn_init_revenue - rac.total_txn_init_burdened_cost,
rac.total_txn_init_revenue - rac.total_txn_init_raw_cost)), -- x_act_margin_txn_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.project_init_revenue) - SUM(bl.project_init_burdened_cost),
-- SUM(bl.project_init_revenue) - SUM(bl.project_init_raw_cost))), -- x_act_margin_proj_cur
'B', rac.total_project_init_revenue - rac.total_project_init_bd_cost,
rac.total_project_init_revenue - rac.total_project_init_raw_cost)), -- x_act_margin_proj_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.init_revenue) - SUM(bl.init_burdened_cost),
-- SUM(bl.init_revenue) - SUM(bl.init_raw_cost))), -- x_act_margin_proj_func_cur
'B', rac.total_projfunc_init_revenue - rac.total_projfunc_init_bd_cost,
rac.total_projfunc_init_revenue - rac.total_projfunc_init_raw_cost)), -- x_act_margin_proj_func_cur
NULL, -- x_etc_margin_txn_cur (TO BE POPULATED)
NULL, -- x_etc_margin_proj_cur (TO BE POPULATED)
NULL, -- x_etc_margin_proj_func_cur (TO BE POPULATED)
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.txn_revenue) - SUM(bl.txn_burdened_cost),
-- SUM(bl.txn_revenue) - SUM(bl.txn_raw_cost))), -- x_fcst_margin_txn_cur
'B', rac.total_txn_revenue - rac.total_txn_burdened_cost,
rac.total_txn_revenue - rac.total_txn_raw_cost)), -- x_fcst_margin_txn_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.project_revenue) - SUM(bl.project_burdened_cost),
-- SUM(bl.project_revenue) - SUM(bl.project_raw_cost))), -- x_fcst_margin_proj_cur
'B', rac.total_project_revenue - rac.total_project_burdened_cost,
rac.total_project_revenue - rac.total_project_raw_cost)), -- x_fcst_margin_proj_cur
DECODE(po.fin_plan_preference_code,
'COST_ONLY', TO_NUMBER(NULL),
'REVENUE_ONLY', TO_NUMBER(NULL),
DECODE(po.margin_derived_from_code,
-- 'B', SUM(bl.revenue) - SUM(bl.burdened_cost),
-- SUM(bl.revenue) - SUM(bl.raw_cost))), -- x_fcst_margin_proj_func_cur
'B', rac.total_projfunc_revenue - rac.total_projfunc_burdened_cost,
rac.total_projfunc_revenue - rac.total_projfunc_raw_cost)), -- x_fcst_margin_proj_func_cur
NULL, -- x_act_margin_pct (TO BE POPULATED)
NULL, -- x_etc_margin_pct (TO BE POPULATED)
NULL, -- x_fcst_margin_pct (TO BE POPULATED)
po.margin_derived_from_code
into x_planning_start_date,
x_planning_end_date,
x_schedule_start_date,
x_schedule_end_date,
x_act_quantity,
x_etc_quantity,
x_fcst_quantity,
x_act_revenue_txn_cur,
x_act_revenue_proj_cur,
x_act_revenue_proj_func_cur,
x_etc_revenue_txn_cur,
x_etc_revenue_proj_cur,
x_etc_revenue_proj_func_cur,
x_fcst_revenue_txn_cur,
x_fcst_revenue_proj_cur,
x_fcst_revenue_proj_func_cur,
x_act_raw_cost_txn_cur,
x_act_raw_cost_proj_cur,
x_act_raw_cost_proj_func_cur,
x_etc_raw_cost_txn_cur,
x_etc_raw_cost_proj_cur,
x_etc_raw_cost_proj_func_cur,
x_fcst_raw_cost_txn_cur,
x_fcst_raw_cost_proj_cur,
x_fcst_raw_cost_proj_func_cur,
x_act_burd_cost_txn_cur,
x_act_burd_cost_proj_cur,
x_act_burd_cost_proj_func_cur,
x_etc_burd_cost_txn_cur,
x_etc_burd_cost_proj_cur,
x_etc_burd_cost_proj_func_cur,
x_fcst_burd_cost_txn_cur,
x_fcst_burd_cost_proj_cur,
x_fcst_burd_cost_proj_func_cur,
x_act_rev_rate,
x_etc_init_rev_rate,
x_etc_avg_rev_rate,
x_act_raw_cost_rate,
x_etc_init_raw_cost_rate,
x_etc_avg_raw_cost_rate,
x_act_burd_cost_rate,
x_etc_init_burd_cost_rate,
x_etc_avg_burd_cost_rate,
x_act_margin_txn_cur,
x_act_margin_proj_cur,
x_act_margin_proj_func_cur,
x_etc_margin_txn_cur,
x_etc_margin_proj_cur,
x_etc_margin_proj_func_cur,
x_fcst_margin_txn_cur,
x_fcst_margin_proj_cur,
x_fcst_margin_proj_func_cur,
x_act_margin_pct,
x_etc_margin_pct,
x_fcst_margin_pct,
l_margin_derived_from_code
FROM pa_resource_assignments ra,
-- pa_budget_lines bl,
pa_resource_asgn_curr rac,
pa_budget_versions bv,
pa_proj_fp_options po
where ra.resource_assignment_id = l_resource_assignment_id and
-- ra.resource_assignment_id = bl.resource_assignment_id and
-- bl.txn_currency_code = p_txn_currency_code and
ra.resource_assignment_id = rac.resource_assignment_id and
rac.txn_currency_code = p_txn_currency_code and
ra.budget_version_id = bv.budget_version_id and
bv.budget_version_id = po.fin_plan_version_id and
po.fin_plan_option_level_code = 'PLAN_VERSION';
select bl.start_date,
bl.end_date,
bl.period_name,
-- bl.quantity,
bl.display_quantity, --IPM
bl.txn_raw_cost,
bl.txn_burdened_cost,
bl.txn_revenue,
bl.init_quantity,
bl.txn_init_raw_cost,
bl.txn_init_burdened_cost,
bl.txn_init_revenue
into x_start_date,
x_end_date,
x_period_name,
x_quantity,
x_txn_raw_cost,
x_txn_burdened_cost,
x_txn_revenue,
x_init_quantity,
x_txn_init_raw_cost,
x_txn_init_burdened_cost,
x_txn_init_revenue
from pa_budget_lines bl
where bl.resource_assignment_id = p_resource_assignment_id and
bl.txn_currency_code = p_txn_currency_code and
bl.start_date BETWEEN p_line_start_date and p_line_end_date and
start_date = (select min(start_date)
from pa_budget_lines
where resource_assignment_id = p_resource_assignment_id and
txn_currency_code = p_txn_currency_code);
select bl.start_date,
bl.end_date,
bl.period_name,
-- bl.quantity,
bl.display_quantity, -- IPM
bl.txn_raw_cost,
bl.txn_burdened_cost,
bl.txn_revenue,
bl.init_quantity,
bl.txn_init_raw_cost,
bl.txn_init_burdened_cost,
bl.txn_init_revenue
into x_start_date,
x_end_date,
x_period_name,
x_quantity,
x_txn_raw_cost,
x_txn_burdened_cost,
x_txn_revenue,
x_init_quantity,
x_txn_init_raw_cost,
x_txn_init_burdened_cost,
x_txn_init_revenue
from pa_budget_lines bl
where bl.resource_assignment_id = p_resource_assignment_id and
bl.txn_currency_code = p_txn_currency_code and
start_date = (select min(start_date)
from pa_budget_lines
where resource_assignment_id = p_resource_assignment_id and
txn_currency_code = p_txn_currency_code);
SELECT 'Y'
INTO l_bl_already_exists
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM PA_BUDGET_LINES PBL,PA_RESOURCE_ASSIGNMENTS PRA,PA_PROJ_ELEMENT_VERSIONS PEV
WHERE PRA.PROJECT_ID = p_project_id
AND PRA.BUDGET_VERSION_ID = p_budget_version_id
AND PRA.RESOURCE_LIST_MEMBER_ID = p_resource_list_member_id_tbl(i)
AND PEV.PROJ_ELEMENT_ID = PRA.TASK_ID
AND PEV.PARENT_STRUCTURE_VERSION_ID = l_structure_version_id
AND PEV.ELEMENT_VERSION_ID = p_task_elem_version_id_tbl(i)
AND PBL.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
AND PBL.TXN_CURRENCY_CODE = p_currency_code_tbl(i)
AND nvl(PRA.CBS_ELEMENT_ID,-1) = nvl(p_cbs_element_id_tbl(i),-1) --bug#16481402
);
SELECT 'Y'
INTO l_bl_already_exists
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM PA_BUDGET_LINES PBL,PA_RESOURCE_ASSIGNMENTS PRA
--,PA_PROJ_ELEMENT_VERSIONS PEV /* Bug 4884718; SQL ID 14903213 */
select version_name
into l_return_value
from pa_budget_versions
where budget_version_id = p_budget_version_id;
SELECT DECODE(wp_version_flag,
'Y',project_structure_version_id,
PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(project_id))
INTO l_edit_plan_struct_id
FROM pa_budget_versions
WHERE budget_Version_id=p_budget_version_id;
select name
into l_return_value
from pa_projects_all
where project_id = p_project_id;
select pe.name
into l_return_value
from pa_proj_elements pe
where pe.proj_element_id = p_wbs_project_element_id;
select pe.name
into l_return_value
from pa_proj_element_versions pev,
pa_proj_elements pe
where pev.element_version_id = p_wbs_element_version_id and
pev.proj_element_id = pe.proj_element_id;
select proj_element_id
into l_return_value
from pa_proj_element_versions
where element_version_id = p_wbs_element_version_id;
select names.resource_name
into l_return_value
from pa_rbs_elements ele,
pa_rbs_element_names_vl names
where ele.rbs_element_id = p_rbs_element_version_id and
ele.rbs_element_name_id = names.rbs_element_name_id;
select pt.plan_class_code,
decode(bv.version_type,'COST','COST_ONLY','REVENUE','REVENUE_ONLY',
'ALL','COST_AND_REV_SAME')
into l_plan_class_code,
l_fin_plan_pref_code
from pa_budget_versions bv,
pa_fin_plan_types_b pt
where bv.budget_version_id = p_plan_version_id and
bv.fin_plan_type_id = pt.fin_plan_type_id;
select bv.budget_version_id,
bv.version_number
into l_curr_fcst_ver_id,
l_curr_fcst_ver_num
from pa_budget_versions bv
where bv.project_id = p_project_id and
bv.primary_cost_forecast_flag = 'Y' and
bv.current_flag = 'Y';
select bv1.budget_version_id
into x_prior_fcst_version_id
from pa_budget_versions bv1
where bv1.project_id = p_project_id
and bv1.primary_cost_forecast_flag = 'Y'
and bv1.budget_status_code = 'B'
/*and bv1.version_number = l_curr_fcst_ver_num - 1;
and bv1.version_number = (select max(bv2.version_number)
from pa_budget_versions bv2
where bv2.project_id = p_project_id
and bv2.primary_cost_forecast_flag = 'Y'
and bv2.budget_status_code = 'B'
and bv2.version_number < l_curr_fcst_ver_num
);
select bv.budget_version_id,
bv.version_number
into l_curr_fcst_ver_id,
l_curr_fcst_ver_num
from pa_budget_versions bv
where bv.project_id = p_project_id and
bv.primary_rev_forecast_flag = 'Y' and
bv.current_flag = 'Y';
select bv1.budget_version_id
into x_prior_fcst_version_id
from pa_budget_versions bv1
where bv1.project_id = p_project_id
and bv1.primary_rev_forecast_flag = 'Y'
and bv1.budget_status_code = 'B'
/*and bv1.version_number = l_curr_fcst_ver_num - 1;
and bv1.version_number = (select max(bv2.version_number)
from pa_budget_versions bv2
where bv2.project_id = p_project_id
and bv2.primary_rev_forecast_flag = 'Y'
and bv2.budget_status_code = 'B'
and bv2.version_number < l_curr_fcst_ver_num
);
select bv.budget_version_id,
bv.version_number
into l_curr_fcst_ver_id,
l_curr_fcst_ver_num
from pa_budget_versions bv
where bv.project_id = p_project_id and
bv.primary_rev_forecast_flag = 'Y' and
bv.primary_cost_forecast_flag = 'Y' and
bv.current_flag = 'Y';
select bv1.budget_version_id
into x_prior_fcst_version_id
from pa_budget_versions bv1
where bv1.project_id = p_project_id
and bv1.primary_rev_forecast_flag = 'Y'
and bv1.primary_cost_forecast_flag = 'Y'
and bv1.budget_status_code = 'B'
/*and bv1.version_number = l_curr_fcst_ver_num - 1;
and bv1.version_number = (select max(bv2.version_number)
from pa_budget_versions bv2
where bv2.project_id = p_project_id
and bv2.primary_rev_forecast_flag = 'Y'
and bv2.primary_cost_forecast_flag = 'Y'
and bv2.budget_status_code = 'B'
and bv2.version_number < l_curr_fcst_ver_num
);
select bv.budget_version_id,
bv.version_number
into l_curr_fcst_ver_id,
l_curr_fcst_ver_num
from pa_budget_versions bv
where bv.project_id = p_project_id and
bv.primary_cost_forecast_flag = 'Y' and
bv.current_flag = 'Y';
select bv1.budget_version_id
into x_prior_fcst_version_id
from pa_budget_versions bv1
where bv1.project_id = p_project_id
and bv1.primary_cost_forecast_flag = 'Y'
and bv1.budget_status_code = 'B'
/*and bv1.version_number = l_curr_fcst_ver_num - 1;
and bv1.version_number = (select max(bv2.version_number)
from pa_budget_versions bv2
where bv2.project_id = p_project_id
and bv2.primary_cost_forecast_flag = 'Y'
and bv2.budget_status_code = 'B'
and bv2.version_number < l_curr_fcst_ver_num
);
select bv.budget_version_id,
bv.version_number
into l_curr_fcst_ver_id,
l_curr_fcst_ver_num
from pa_budget_versions bv
where bv.project_id = p_project_id and
bv.primary_rev_forecast_flag = 'Y' and
bv.current_flag = 'Y';
select bv1.budget_version_id
into x_prior_fcst_version_id
from pa_budget_versions bv1
where bv1.project_id = p_project_id
and bv1.primary_rev_forecast_flag = 'Y'
and bv1.budget_status_code = 'B'
/*and bv1.version_number = l_curr_fcst_ver_num - 1;
and bv1.version_number = (select max(bv2.version_number)
from pa_budget_versions bv2
where bv2.project_id = p_project_id
and bv2.primary_rev_forecast_flag = 'Y'
and bv2.budget_status_code = 'B'
and bv2.version_number < l_curr_fcst_ver_num
);
select bv2.budget_version_id,
bv2.version_number,
bv2.fin_plan_type_id,
bv2.version_type
into l_curr_fcst_ver_id,
l_curr_fcst_ver_num,
l_fp_type_id,
l_version_type
from pa_budget_versions bv1,
pa_budget_versions bv2
where bv1.project_id = p_project_id and
bv1.budget_version_id = p_plan_version_id and
bv1.project_id = bv2.project_id and
bv1.fin_plan_type_id = bv2.fin_plan_type_id and
bv1.version_type = bv2.version_type and
bv2.current_flag = 'Y';
select budget_version_id
into x_prior_fcst_version_id
from pa_budget_versions
where project_id = p_project_id and
fin_plan_type_id = l_fp_type_id and
version_type = l_version_type and
budget_status_code = 'B' and
/* version_number = l_curr_fcst_ver_num - 1;
version_number = (select max(bv1.version_number)
from pa_budget_versions bv1
where bv1.project_id = p_project_id
and bv1.fin_plan_type_id = l_fp_type_id
and bv1.version_type = l_version_type
and bv1.budget_status_code = 'B'
and bv1.version_number < l_curr_fcst_ver_num
);