[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select txn_currency_code
from pa_fp_txn_currencies txncurr,
pa_proj_fp_options pfo
where pfo.fin_plan_version_id = l_budget_version_id and
txncurr.proj_fp_options_id = pfo.proj_fp_options_id and
not (txn_currency_code in
(select distinct txn_currency_code
from pa_budget_lines bl
where bl.resource_assignment_id = p_resource_assignment_id));
select budget_version_id
into l_budget_version_id
from pa_resource_assignments
where resource_assignment_id = p_resource_assignment_id;
select task_name
into l_task_name
from pa_tasks
where task_id = p_task_id;
select task_number
into l_task_number
from pa_tasks
where task_id = p_task_id;
select name
into l_resource_name
from pa_resources
where resource_id = p_resource_id;
SELECT nvl(bv.approved_cost_plan_type_flag, 'N'),
nvl(bv.approved_rev_plan_type_flag, 'N')
INTO l_ac_flag,
l_ar_flag
FROM pa_budget_versions bv
WHERE bv.budget_version_id = l_version_id;
select projfunc_currency_code
into x_project_currency
from pa_projects_all
where project_id = p_project_id;
select project_currency_code
into x_project_currency
from pa_projects_all
where project_id = p_project_id;
select fin_plan_type_id,
NVL(plan_in_multi_curr_flag, 'N'),
proj_fp_options_id
into l_fin_plan_type_id,
l_multi_curr_flag,
l_proj_fp_options_id
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = l_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
select proj_fp_options_id,
fin_plan_preference_code
into x_plan_type_fp_options_id,
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 name
into x_plan_type_name
from pa_fin_plan_types_tl
where fin_plan_type_id = l_fin_plan_type_id and
language = USERENV('LANG');
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 DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
rl.resource_list_id,
bv.record_version_number,
nvl(rl.uncategorized_flag, 'N'),
DECODE(bv.budget_status_code,
'B', 'B',
'W')
INTO l_grouping_type,
l_resource_list_id,
l_rv_number,
l_uncategorized_flag,
l_c_budget_status_code
FROM pa_budget_versions bv,
pa_resource_lists_all_bg rl
WHERE bv.budget_version_id = p_cost_version_id and
bv.resource_list_id = rl.resource_list_id;
SELECT DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
rl.resource_list_id,
bv.record_version_number,
nvl(rl.uncategorized_flag, 'N'),
DECODE(bv.budget_status_code,
'B', 'B',
'W')
INTO l_compl_grouping_type,
l_compl_resource_list_id,
l_compl_rv_number,
l_compl_uncategorized_flag,
l_r_budget_status_code
FROM pa_budget_versions bv,
pa_resource_lists_all_bg rl
WHERE bv.budget_version_id = p_rev_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 fin_plan_version_id = p_rev_version_id;
SELECT all_fin_plan_level_code
INTO l_cost_planning_level
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_cost_version_id;
SELECT revenue_fin_plan_level_code
INTO l_rev_planning_level
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_rev_version_id;
SELECT cost_fin_plan_level_code
INTO l_cost_planning_level
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_cost_version_id;
select DECODE(rl.group_resource_type_id,
0, 'NONGROUPED',
'GROUPED'),
bv.resource_list_id,
nvl(bv.budget_status_code, 'W'),
DECODE(bv.budget_status_code,
'B', 'B',
'W'),
DECODE(bv.version_type,
'COST', 'C',
'REVENUE', 'R',
'N'),
nvl(bv.current_working_flag, '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,
x_current_working_flag,
x_record_version_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 = p_budget_version_id and
bv.resource_list_id = rl.resource_list_id;
select projfunc_currency_code
into x_project_currency
from pa_projects_all
where project_id = p_project_id;
select ci_id,
agreement_id
into l_ci_id,
l_agreement_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
select nvl (agreement_currency_code, 'ANY')
into l_agreement_currency_code
from pa_agreements_all
where agreement_id = l_agreement_id;
select project_currency_code
into x_project_currency
from pa_projects_all
where project_id = p_project_id;
select fin_plan_type_id,
NVL(plan_in_multi_curr_flag, 'N'),
proj_fp_options_id
into l_fin_plan_type_id,
l_multi_curr_flag,
l_proj_fp_options_id
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = p_budget_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
select proj_fp_options_id,
fin_plan_preference_code
into x_plan_type_fp_options_id,
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 name
into x_plan_type_name
from pa_fin_plan_types_tl
where fin_plan_type_id = l_fin_plan_type_id and
language = USERENV('LANG');
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 version_type
into l_version_type
from pa_budget_versions
where budget_version_id = p_budget_version_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 ra_cost.project_id,
ra_cost.task_id,
ra_cost.resource_list_member_id,
bl_cost.resource_assignment_id,
-1 as compl_resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type, -- used in wrapper view to decide how to handle
-- parent_member_id = null cases
bl_cost.txn_currency_code,
decode((NVL(ra_cost.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS UNIT_OF_MEASURE, -- ra_cost.unit_of_measure, bug 3463685
SUM(nvl(bl_cost.quantity,0)) as quantity,
SUM(nvl(bl_cost.txn_burdened_cost,0)) as burdened_cost,
SUM(nvl(bl_cost.txn_raw_cost,0)) as raw_cost,
0 as revenue,
0 as margin,
0 as margin_percent
from pa_resource_assignments ra_cost,
pa_budget_lines bl_cost,
pa_resource_list_members rlm,
pa_resources pr -- added for bug 3463685
where ra_cost.budget_version_id = p_cost_version_id and
ra_cost.resource_assignment_type = 'USER_ENTERED' and
ra_cost.resource_assignment_id = bl_cost.resource_assignment_id and
ra_cost.resource_list_member_id = rlm.resource_list_member_id and
rlm.resource_id = pr.resource_id and -- added for bug 3463685
(p_filter_task_id = -1 or ra_cost.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
(p_filter_txncurrency = 'ALL' or bl_cost.txn_currency_code = p_filter_txncurrency)
--(bl_cost.txn_raw_cost is not null or bl_cost.txn_burdened_cost is not null)
group by ra_cost.project_id,
ra_cost.task_id,
ra_cost.resource_list_member_id,
bl_cost.resource_assignment_id,
bl_cost.txn_currency_code,
decode((NVL(ra_cost.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) -- ra_cost.unit_of_measure bug 3463685
UNION
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
-1 as compl_resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
ftc.txn_currency_code as txn_currency_code,
DECODE((NVL(ra.track_as_labor_flag,'N')), 'Y' , 'HOURS' , pr.unit_of_measure) as UNIT_OF_MEASURE, -- ra.unit_of_measure, bug 3463685
ra.total_plan_quantity as quantity,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue,
ra.total_plan_revenue) as revenue, -- null
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue - ra.total_project_raw_cost,
ra.total_plan_revenue - ra.total_plan_raw_cost) as margin, -- null
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT',
DECODE(ra.total_project_revenue,
0, 0,
(ra.total_project_revenue - ra.total_project_raw_cost)/
ra.total_project_revenue),
DECODE(ra.total_plan_revenue,
0, 0,
(ra.total_plan_revenue - ra.total_plan_raw_cost)/
ra.total_plan_revenue)) as margin_percent -- null
from pa_resource_assignments ra,
pa_resource_list_members rlm,
pa_fp_txn_currencies ftc,
pa_resources pr -- added for bug 3463685
where ra.budget_version_id = p_cost_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_list_member_id = rlm.resource_list_member_id and
rlm.resource_id = pr.resource_id and -- added for bug 3463685
ra.budget_version_id = ftc.fin_plan_version_id and
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
ftc.default_cost_curr_flag = 'Y' and
(p_filter_txncurrency = 'ALL' or
ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
not exists(select bl.resource_assignment_id from pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id) and
p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
select ra_revenue.project_id,
ra_revenue.task_id,
ra_revenue.resource_list_member_id,
bl_revenue.resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping as grouping_type,
bl_revenue.txn_currency_code,
DECODE((NVL(ra_revenue.track_as_labor_flag,'N')), 'Y' , 'HOURS' , pr.unit_of_measure) as UNIT_OF_MEASURE, -- ra_revenue.unit_of_measure, bug 3463685
SUM(nvl(bl_revenue.quantity,0)) as quantity,
-- 0 as burdened_cost,
-- 0 as raw_cost,
SUM(nvl(bl_revenue.txn_revenue,0)) as revenue
-- 0 as margin,
-- 0 as margin_percent
from pa_resource_assignments ra_revenue,
pa_budget_lines bl_revenue,
pa_resource_list_members rlm,
pa_resources pr -- Added for bug 3463685
where ra_revenue.budget_version_id = p_revenue_version_id and
ra_revenue.resource_assignment_type = 'USER_ENTERED' and
ra_revenue.resource_assignment_id = bl_revenue.resource_assignment_id and
ra_revenue.resource_list_member_id = rlm.resource_list_member_id and
pr.resource_id = rlm.resource_id and -- added for bug 3463685
(p_filter_task_id = -1 or ra_revenue.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and /* Bug 2843566 - changed cost function to rev function */
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and /* Bug 2843566 - changed cost function to rev
function */
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
(p_filter_txncurrency = 'ALL' or bl_revenue.txn_currency_code = p_filter_txncurrency)
--bl_revenue.txn_revenue is not null
group by ra_revenue.project_id,
ra_revenue.task_id,
ra_revenue.resource_list_member_id,
bl_revenue.resource_assignment_id,
bl_revenue.txn_currency_code,
DECODE((NVL(ra_revenue.track_as_labor_flag,'N')), 'Y' , 'HOURS' , pr.unit_of_measure)-- pr.unit_of_measure --ra_revenue.unit_of_measure bug 3463685
UNION
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping as grouping_type,
ftc.txn_currency_code as txn_currency_code,
DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) as unit_of_measure, -- ra.unit_of_measure, bug 3463685
ra.total_plan_quantity as quantity,
-- 0 as burdened_cost,
-- 0 as raw_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue,
ra.total_plan_revenue) as revenue -- null
-- 0 as margin,
-- 0 as margin_percent
from pa_resource_assignments ra,
pa_resource_list_members rlm,
pa_fp_txn_currencies ftc,
pa_resources pr -- added for bug 3463685
where ra.budget_version_id = p_revenue_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_list_member_id = rlm.resource_list_member_id and
pr.resource_id = rlm.resource_id and -- bug 3463685
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and /* Bug 2843566 - changed cost function to rev
function */
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and /* Bug 2843566 - changed cost function to rev
function */
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
ra.budget_version_id = ftc.fin_plan_version_id and
ftc.default_rev_curr_flag = 'Y' and
(p_filter_txncurrency = 'ALL' or
ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
not exists(select bl.resource_assignment_id from pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id) and
p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
bl.resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
bl.txn_currency_code,
DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) as unit_of_measure, -- ra.unit_of_measure, bug 3463685
SUM(nvl(bl.quantity,0)) as quantity,
SUM(nvl(bl.txn_burdened_cost,0)) as burdened_cost,
SUM(nvl(bl.txn_raw_cost,0)) as raw_cost,
SUM(nvl(bl.txn_revenue,0)) as revenue,
DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
'R', SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_raw_cost,0)),
SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_burdened_cost,0))) as margin,
DECODE(SUM(nvl(bl.txn_revenue,0)),
0, 0,
null, 0,
DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
'R', (SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_raw_cost,0)))/
SUM(nvl(bl.txn_revenue,0)),
(SUM(nvl(bl.txn_revenue,0)) - SUM(nvl(bl.txn_burdened_cost,0)))/
SUM(nvl(bl.txn_revenue,0)))) as margin_percent
from pa_resource_assignments ra,
pa_budget_lines bl,
pa_resource_list_members rlm,
pa_resources pr -- Added for bug 3463685
where ra.budget_version_id = p_both_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_assignment_id = bl.resource_assignment_id and
ra.resource_list_member_id = rlm.resource_list_member_id and
pr.resource_id = rlm.resource_id and -- added for bug 3463685
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
(p_filter_txncurrency = 'ALL' or bl.txn_currency_code = p_filter_txncurrency)
group by ra.project_id,
ra.task_id,
ra.resource_list_member_id,
bl.resource_assignment_id,
bl.txn_currency_code,
DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) --ra.unit_of_measure bug 3463685
UNION
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
ftc.txn_currency_code as txn_currency_code,
DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS unit_of_measure, -- ra.unit_of_measure, bug 3463685
ra.total_plan_quantity as quantity,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost, -- null
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost, -- null
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue,
ra.total_plan_revenue) as revenue, -- null
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue - ra.total_project_raw_cost,
ra.total_plan_revenue - total_plan_raw_cost) as margin, -- null
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT',
DECODE(ra.total_project_revenue,
0, 0,
(ra.total_project_revenue - ra.total_project_raw_cost)/
ra.total_project_revenue),
DECODE(ra.total_plan_revenue,
0, 0,
(ra.total_plan_revenue - ra.total_plan_raw_cost)/
ra.total_plan_revenue)) as margin_percent -- null
from pa_resource_assignments ra,
pa_resource_list_members rlm,
pa_fp_txn_currencies ftc,
pa_resources pr -- Added for bug 3463685
where ra.budget_version_id = p_both_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_list_member_id = rlm.resource_list_member_id and
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
pr.resource_id = rlm.resource_id and -- bug 3463685
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) and
ra.budget_version_id = ftc.fin_plan_version_id and
ftc.default_all_curr_flag = 'Y' and
(p_filter_txncurrency = 'ALL' or
ftc.txn_currency_code = p_filter_txncurrency) and -- bug fix 2697775
not exists(select bl.resource_assignment_id from pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id) and
p_page_mode = c_edit_mode; /* p_page_mode condition included for bug 2710844 */
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
-1 as compl_resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
p_project_currency as txn_currency_code,
DECODE((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS unit_of_measure, -- ra.unit_of_measure, bug 3463685
ra.total_plan_quantity as quantity,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost,
0 as revenue,
0 as margin,
0 as margin_percent
from pa_resource_assignments ra,
pa_resource_list_members rlm,
pa_resources pr -- added for bug 3463685
where ra.budget_version_id = p_cost_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_list_member_id = rlm.resource_list_member_id and
pr.resource_id = rlm.resource_id and -- added for bug 3463685
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) 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
union
select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping as grouping_type,
p_project_currency as txn_currency_code,
decode((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS UNIT_OF_MEASURE, -- ra.unit_of_measure, bug 3463685
total_plan_quantity as quantity,
-- 0 as burdened_cost,
-- 0 as raw_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue,
ra.total_plan_revenue) as revenue
-- 0 as margin,
-- 0 as margin_percent
from pa_resource_assignments ra,
pa_resource_list_members rlm,
pa_resources pr -- Added for bug 3463685
where ra.budget_version_id = p_revenue_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_list_member_id = rlm.resource_list_member_id and
pr.resource_id = rlm.resource_id and -- bug 3463685
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Rev_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) 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
union
select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
select ra.project_id,
ra.task_id,
ra.resource_list_member_id,
ra.resource_assignment_id,
pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping as grouping_type,
p_project_currency as txn_currency_code,
decode((NVL(ra.track_as_labor_flag,'N')),'Y','HOURS',pr.unit_of_measure) AS UNIT_OF_MEASURE, -- ra.unit_of_measure, bug 3463685
ra.total_plan_quantity as quantity,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_burdened_cost,
ra.total_plan_burdened_cost) as burdened_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_raw_cost,
ra.total_plan_raw_cost) as raw_cost,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT', ra.total_project_revenue,
ra.total_plan_revenue) as revenue,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT',
DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
'R', ra.total_project_revenue - ra.total_project_raw_cost,
ra.total_project_revenue - ra.total_project_burdened_cost),
DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
'R', ra.total_plan_revenue - ra.total_plan_raw_cost,
ra.total_plan_revenue - ra.total_plan_burdened_cost)) as margin,
DECODE(pa_fp_view_plans_txn_pub.G_DISPLAY_CURRENCY_TYPE,
'PROJECT',
DECODE(ra.total_project_revenue,
null, null,
0, 0,
DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
'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_project_revenue,
null, null,
0, 0,
DECODE(pa_fp_view_plans_txn_pub.Get_Derive_Margin_From_Code,
'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
from pa_resource_assignments ra,
pa_resource_list_members rlm,
pa_resources pr -- Added for bug 3463685
where ra.budget_version_id = p_both_version_id and
ra.resource_assignment_type = 'USER_ENTERED' and
ra.resource_list_member_id = rlm.resource_list_member_id and
pr.resource_id = rlm.resource_id and -- added for bug 3463685
(p_filter_task_id = -1 or ra.task_id = p_filter_task_id) and
-- bug fix 2774764: there could be a mix of GROUPED and UNGROUPED resources
(p_filter_resource_id = -1 or
rlm.parent_member_id = p_filter_resource_id or
(rlm.parent_member_id is null and
rlm.resource_id = (select resource_id
from pa_resource_list_members
where resource_list_member_id = p_filter_resource_id))) and
/*
(p_filter_resource_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id = p_filter_resource_id) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_resource_id)) and
*/
(p_filter_rlm_id = -1 or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'NONGROUPED' and
rlm.parent_member_id is null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id)) or
(pa_fp_view_plans_txn_pub.Get_Cost_Version_Grouping = 'GROUPED' and
rlm.parent_member_id is not null and
rlm.resource_id = p_filter_rlm_id and
(rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Cost_Resource_List_Id or
rlm.resource_list_id = pa_fp_view_plans_txn_pub.Get_Revenue_Resource_List_Id))) 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
union
select 1 from dual where p_page_mode = c_edit_mode); /* exists condition included for bug 2710844 */
delete from PA_FP_TXN_LINES_TMP where project_id is not null;
select nvl(plan_in_multi_curr_flag, 'N')
into l_rev_multi_curr_flag
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = p_both_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
insert into PA_FP_TXN_LINES_TMP
(project_id,
task_id,
resource_list_member_id,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
grouping_type,
txn_currency_code,
unit_of_measure,
quantity,
revenue,
burdened_cost,
raw_cost,
margin,
margin_pct) values
(l_c_project_id_tab(c),
l_c_task_id_tab(c),
l_c_res_list_member_id_tab(c),
-1, -- cost_resource_assignment_id
-1, -- rev_resource_assignment_id
l_c_res_assignment_id_tab(c), -- all_resource_assignment_id
l_c_grouping_tab(c),
l_c_txn_currency_code_tab(c),
l_c_unit_of_measure_tab(c),
l_c_quantity_tab(c), -- always display the quantity from the version
l_c_revenue_tab(c),
l_c_burdened_cost_tab(c),
l_c_raw_cost_tab(c),
l_c_margin_tab(c),
l_c_margin_pct_tab(c));
select nvl(plan_in_multi_curr_flag, 'N')
into l_cost_multi_curr_flag
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = p_cost_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
insert into PA_FP_TXN_LINES_TMP
(project_id,
task_id,
resource_list_member_id,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
grouping_type,
txn_currency_code,
unit_of_measure,
quantity,
revenue,
burdened_cost,
raw_cost,
margin,
margin_pct) values
(l_c_project_id_tab(c),
l_c_task_id_tab(c),
l_c_res_list_member_id_tab(c),
l_c_res_assignment_id_tab(c), -- cost_resource_assignment_id
l_cr_res_assignment_id_tab(c), -- revenue_resource_assignment_id = -1
-1, -- all_resource_assignment_id
l_c_grouping_tab(c),
l_c_txn_currency_code_tab(c),
l_c_unit_of_measure_tab(c),
l_c_quantity_tab(c), -- always display the quantity from the version
null, -- null for revenue
l_c_burdened_cost_tab(c),
l_c_raw_cost_tab(c),
null, -- null for margin
null); -- null for margin_pct
select nvl(plan_in_multi_curr_flag, 'N')
into l_rev_multi_curr_flag
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = p_revenue_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
insert into PA_FP_TXN_LINES_TMP
(project_id,
task_id,
resource_list_member_id,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
grouping_type,
txn_currency_code,
unit_of_measure,
quantity,
revenue,
burdened_cost,
raw_cost,
margin,
margin_pct) values
(l_r_project_id_tab(r),
l_r_task_id_tab(r),
l_r_res_list_member_id_tab(r),
-1, -- cost_resource_assignment_id
l_r_res_assignment_id_tab(r), -- rev_resource_assignment_id
-1, -- all_resource_assignment_id
l_r_grouping_tab(r),
l_r_txn_currency_code_tab(r),
l_r_unit_of_measure_tab(r),
l_r_quantity_tab(r), -- always display the quantity from the version
l_r_revenue_tab(r),
null, -- null for burdened_cost
null, -- null for raw cost
null, -- null for margin
null); -- null for margin_pct
select nvl(plan_in_multi_curr_flag, 'N')
into l_cost_multi_curr_flag
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = p_cost_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
select nvl(plan_in_multi_curr_flag, 'N')
into l_rev_multi_curr_flag
from pa_proj_fp_options
where project_id = p_project_id and
fin_plan_version_id = p_revenue_version_id and
fin_plan_option_level_code = 'PLAN_VERSION';
l_r_project_id_tab.delete(j);
l_r_task_id_tab.delete(j);
l_r_res_list_member_id_tab.delete(j);
l_r_res_assignment_id_tab.delete(j);
l_r_txn_currency_code_tab.delete(j);
l_r_unit_of_measure_tab.delete(j);
l_r_quantity_tab.delete(j);
l_r_revenue_tab.delete(j);
l_r_project_id_tab.delete(j);
l_r_task_id_tab.delete(j);
l_r_res_list_member_id_tab.delete(j);
l_r_res_assignment_id_tab.delete(j);
l_r_txn_currency_code_tab.delete(j);
l_r_unit_of_measure_tab.delete(j);
l_r_quantity_tab.delete(j);
l_r_revenue_tab.delete(j);
insert into PA_FP_TXN_LINES_TMP
(project_id,
task_id,
resource_list_member_id,
cost_resource_assignment_id,
rev_resource_assignment_id,
all_resource_assignment_id,
grouping_type,
txn_currency_code,
unit_of_measure,
quantity,
revenue,
burdened_cost,
raw_cost,
margin,
margin_pct) values
(l_c_project_id_tab(c),
l_c_task_id_tab(c),
l_c_res_list_member_id_tab(c),
l_c_res_assignment_id_tab(c), -- cost_res_assignment_id
l_cr_res_assignment_id_tab(c), -- rev_res_assignment_id
-1, -- all_res_assignment_id
l_c_grouping_tab(c),
l_c_txn_currency_code_tab(c),
l_c_unit_of_measure_tab(c),
l_c_quantity_tab(c),
l_c_revenue_tab(c), -- null values already present where needed
l_c_burdened_cost_tab(c), -- null values already present where needed
l_c_raw_cost_tab(c), -- null values already present where needed
l_c_margin_tab(c), -- null values already present where needed
l_c_margin_pct_tab(c)); -- null values already present where needed
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 project_currency_code
into x_project_currency
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'),
bv.agreement_id
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,
l_agreement_id
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 nvl (agreement_currency_code, 'ANY')
into l_agreement_currency_code
from pa_agreements_all
where agreement_id = l_agreement_id;
select projfunc_currency_code
into x_project_currency
from pa_projects_all
where project_id = p_project_id;
select project_currency_code
into x_project_currency
from pa_projects_all
where project_id = p_project_id;
select proj_fp_options_id,
fin_plan_preference_code
into x_plan_type_fp_options_id,
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 fin_plan_type_id,
proj_fp_options_id
into l_fin_plan_type_id2,
l_proj_fp_options_id2
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 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_id2 and
fin_plan_option_level_code = 'PLAN_TYPE';
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 budget_line_id,
resource_assignment_id,
start_date,
end_date,
period_name,
txn_currency_code,
pm_product_code,
quantity,
txn_raw_cost,
txn_burdened_cost,
txn_revenue
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
ORDER BY resource_assignment_id;
/* Delete the PL/SQL tables which are being populated manually. */
l_task_id_tbl.delete;
l_res_list_member_id_tbl.delete;
l_resource_id_tbl.delete;
required to call the CAll_Client_Extensions API. The select
has to be done only once for a RA ID and so caching the RA ID.
Since l_prev_res_assignment_id has been initialised to -99,
the below condition will be satisfied even for the first time we
enter into this loop. */
IF l_prev_res_assignment_id <> l_ra_id_tbl(i) THEN
/* Fetch the details of the Resource Assignment if not fetched
already. */
SELECT pra.task_id,
pra.resource_list_member_id,
pra.project_id,
prlm.resource_id,
prlm.resource_list_id
INTO l_task_id,
l_rlm_id,
l_project_id,
l_resource_id,
l_resource_list_id
FROM pa_resource_assignments pra,
pa_resource_list_members prlm
WHERE pra.resource_assignment_id = l_ra_id_tbl(i)
AND prlm.resource_list_member_id = pra.resource_list_member_id;
and Quantity accordingly. Bulk update the amounts on the Budget Lines table. */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := 'Updating the Budget Line amounts';
UPDATE pa_budget_lines
SET txn_raw_cost = l_txn_raw_cost_tbl(i)
,txn_burdened_cost = l_txn_burdened_cost_tbl(i)
,txn_revenue = l_txn_revenue_tbl(i)
,quantity = l_quantity_tbl(i)
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_line_id = l_budget_line_id_tbl(i);
pa_debug.g_err_stage := 'Updated - '||sql%rowcount||' records';