[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE pa_budget_versions
SET plan_processing_code = 'E'
WHERE budget_version_id = p_budget_version_id;
SELECT atb.amount_type_id
,atb.amount_type_code
FROM pa_amount_types_b atb
WHERE atb.amount_type_class = 'R';
,p_selection_criteria IN VARCHAR2
:= NULL
,p_is_org IN VARCHAR2
:= NULL
,p_organization_id IN hr_organization_units.organization_id%TYPE
:= NULL
,p_is_start_org IN VARCHAR2
:= NULL
,p_starting_organization_id IN hr_organization_units.organization_id%TYPE
:= NULL
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
:= NULL)
IS
/* Local Variables */
/* Error handling local variables */
l_msg_count number := 0;
SELECT
fl.budget_version_id /* FPB2: budget_version_id */
,l_own_resource_assignment_id
,fl.period_name
,fl.start_date
,fl.end_date
,nvl(sum(fl.quantity),0)
,nvl(sum(fl.raw_cost),0)
,nvl(sum(fl.burdened_cost),0)
,nvl(sum(fl.revenue),0)
,nvl(sum(fl.borrowed_revenue),0)
,nvl(sum(fl.tp_revenue_in),0)
,nvl(sum(fl.tp_revenue_out),0)
,nvl(sum(fl.lent_resource_cost),0)
,nvl(sum(fl.tp_cost_in),0)
,nvl(sum(fl.tp_cost_out),0)
,nvl(sum(fl.unassigned_time_cost),0)
FROM pa_org_forecast_lines fl
WHERE fl.budget_version_id = l_budget_version_id
AND fl.project_id = l_project_id
AND fl.task_id = l_own_task_id
GROUP BY fl.period_name
,fl.start_date
,fl.end_date
,fl.budget_version_id; /* FPB2 */
SELECT
bl.budget_version_id /* FPB2: budget_version_id */
,l_proj_resource_assignment_id
,bl.period_name
,bl.start_date
,bl.end_date
,nvl(sum(bl.quantity),0)
,nvl(sum(bl.raw_cost),0)
,nvl(sum(bl.burdened_cost),0)
,nvl(sum(bl.revenue),0)
,nvl(sum(bl.borrowed_revenue),0)
,nvl(sum(bl.tp_revenue_in),0)
,nvl(sum(bl.tp_revenue_out),0)
,nvl(sum(bl.lent_resource_cost),0)
,nvl(sum(bl.tp_cost_in),0)
,nvl(sum(bl.tp_cost_out),0)
,nvl(sum(bl.unassigned_time_cost),0)
,nvl(sum(bl.utilization_percent),0)
,nvl(sum(bl.utilization_hours),0)
,nvl(sum(bl.capacity),0)
,nvl(sum(bl.head_count),0)
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE bl.resource_assignment_id = ra.resource_assignment_id
AND bl.budget_version_id = ra.budget_version_id
AND ra.budget_version_id = l_budget_version_id
AND ra.task_id <> 0
AND bl.budget_version_id = l_budget_version_id /* bug 3106741 */
GROUP BY bl.period_name
,bl.start_date
,bl.end_date
,bl.budget_version_id; /* FPB2 */
SELECT child_organization_id
FROM pa_org_hierarchy_denorm
WHERE pa_org_use_type = 'REPORTING'
and parent_organization_id = p_starting_organization_id
and org_id = l_org_id
and org_hierarchy_version_id = l_org_structure_version_id
order by
parent_level DESC
,child_level DESC
,child_organization_id;
SELECT se.organization_id_child org_id
FROM per_org_structure_elements se
WHERE se.org_structure_version_id = l_org_structure_version_id
CONNECT BY PRIOR se.organization_id_child = se.organization_id_parent
AND se.org_structure_version_id = l_org_structure_version_id
START WITH se.organization_id_parent = p_starting_organization_id
AND se.org_structure_version_id = l_org_structure_version_id
UNION
SELECT p_starting_organization_id FROM DUAL;
SELECT hou.organization_id
,houtl.name
,hou.business_group_id
,hou.location_id
FROM hr_all_organization_units hou,
hr_all_organization_units_tl houtl
WHERE hou.organization_id = p_organization_id
AND houtl.organization_id = hou.organization_id
AND houtl.language = USERENV('LANG');
SELECT pa.child_organization_id
FROM pa_org_hierarchy_denorm pa
WHERE pa.pa_org_use_type = 'REPORTING'
AND pa.parent_level-pa.child_level < 1
AND nvl(pa.org_id,-99) = l_org_id
AND pa.org_hierarchy_version_id = l_org_structure_version_id
AND pa.parent_organization_id = l_organization_id
ORDER BY parent_level desc,
parent_organization_id,
child_level desc,
child_organization_id;
SELECT carrying_out_organization_id,
task_id
FROM pa_tasks
WHERE project_id = l_project_id;
/* Transfer Price Logic as part of Select statement:
Org Context TP Amount Type CC In CC Out Rev In Rev Out
----------- ---------------- ----- ------ ------ -------
Provider COST_TRANSFER 0 Y 0 0
Provider REVENUE_TRANSFER 0 0 Y 0
Receiver COST_TRANSFER Y 0 0 0
Receiver REVENUE_TRANSFER 0 0 0 Y
*/
CURSOR forecast_items IS
SELECT
l_organization_id,
pfi.project_organization_id,
pfi.project_org_id,
pfi.expenditure_organization_id,
pfi.expenditure_org_id,
pfi.project_id txn_project_id,
nvl(pfi.assignment_id,-1),
nvl(pfi.resource_id,-1),
gp.period_name,
gp.start_date,
gp.end_date,
nvl(sum(pfi.item_quantity),0),
DECODE(pfi.forecast_item_type,'U',0,
DECODE(pfi.expenditure_organization_id,pfi.project_organization_id,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_raw_cost),0),0),0),0)) raw_cost,
DECODE(pfi.forecast_item_type,'U',0,
DECODE(pfi.expenditure_organization_id, pfi.project_organization_id,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0),0),0)) burdened_cost,
DECODE(pfi.forecast_item_type,'U',0,
DECODE(pfi.expenditure_organization_id,pfi.project_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,0,
DECODE(pfi.expenditure_organization_id,l_organization_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0)),
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0),0))) lent_resource_cost,
DECODE(pfi.forecast_item_type,'U',
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0),0),0) unassigned_time_cost,
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'COST_TRANSFER',
NVL(SUM(pfi.projfunc_transfer_price),0),0),0),0) tp_cost_in,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'COST_TRANSFER',
NVL(SUM(pfi.expfunc_transfer_price),0),0),0),0) tp_cost_out,
DECODE(pfi.project_organization_id,pfi.expenditure_organization_id,
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
NVL(SUM(pfi.projfunc_revenue),0),0),0),0) revenue,
DECODE(pfi.project_organization_id,pfi.expenditure_organization_id,
DECODE(pfi.project_org_id,l_org_id,0,
DECODE(pfi.project_organization_id,l_organization_id,
NVL(SUM(pfi.projfunc_revenue),0),0)),
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
NVL(SUM(pfi.projfunc_revenue),0),0),0)) borrowed_revenue,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'REVENUE_TRANSFER',
NVL(SUM(pfi.expfunc_transfer_price),0),0),0),0) tp_rev_in,
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'REVENUE_TRANSFER',
NVL(sum(pfi.projfunc_transfer_price),0),0),0),0) tp_rev_out
FROM pa_forecast_items pfi,
gl_date_period_map dpm,
gl_periods gp
WHERE pfi.project_organization_id = l_organization_id
and nvl(pfi.project_org_id,-99) = l_org_id
AND pfi.forecast_item_type in ('A','R','U')
AND pfi.delete_flag = 'N'
AND pfi.error_flag = 'N'
AND pfi.item_date BETWEEN l_fcst_start_date
AND l_fcst_end_date
AND pfi.forecast_amt_calc_flag||'' = 'Y'
AND dpm.period_set_name = l_period_set_name
AND dpm.period_type = l_act_period_type
AND pfi.item_date = dpm.accounting_date
AND dpm.period_set_name = gp.period_set_name
AND dpm.period_type = gp.period_type
AND dpm.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
GROUP BY pfi.project_organization_id,
pfi.project_org_id,
pfi.expenditure_organization_id,
pfi.expenditure_org_id,
pfi.project_id,
nvl(pfi.assignment_id,-1),
nvl(pfi.resource_id,-1),
pfi.tp_amount_type,
pfi.forecast_item_type,
gp.start_date,
gp.end_date,
gp.period_name
UNION ALL -- bug 3106741 changed union to uinon all for performance benefit
SELECT
l_organization_id,
pfi.project_organization_id,
pfi.project_org_id,
pfi.expenditure_organization_id,
pfi.expenditure_org_id,
pfi.project_id txn_project_id,
nvl(pfi.assignment_id,-1),
nvl(pfi.resource_id,-1),
gp.period_name,
gp.start_date,
gp.end_date,
nvl(sum(pfi.item_quantity),0),
DECODE(pfi.forecast_item_type,'U',0,
DECODE(pfi.expenditure_organization_id,pfi.project_organization_id,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_raw_cost),0),0),0),0)) raw_cost,
DECODE(pfi.forecast_item_type,'U',0,
DECODE(pfi.expenditure_organization_id, pfi.project_organization_id,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0),0),0)) burdened_cost,
DECODE(pfi.forecast_item_type,'U',0,
DECODE(pfi.expenditure_organization_id,pfi.project_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,0,
DECODE(pfi.expenditure_organization_id,l_organization_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0)),
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0),0))) lent_resource_cost,
DECODE(pfi.forecast_item_type,'U',
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
NVL(SUM(pfi.expfunc_burdened_cost),0),0),0),0) unassigned_time_cost,
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'COST_TRANSFER',
NVL(SUM(pfi.projfunc_transfer_price),0),0),0),0) tp_cost_in,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'COST_TRANSFER',
NVL(SUM(pfi.expfunc_transfer_price),0),0),0),0) tp_cost_out,
DECODE(pfi.project_organization_id,pfi.expenditure_organization_id,
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
NVL(SUM(pfi.projfunc_revenue),0),0),0),0) revenue,
DECODE(pfi.project_organization_id,pfi.expenditure_organization_id,
DECODE(pfi.project_org_id,l_org_id,0,
DECODE(pfi.project_organization_id,l_organization_id,
NVL(SUM(pfi.projfunc_revenue),0),0)),
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
NVL(SUM(pfi.projfunc_revenue),0),0),0)) borrowed_revenue,
DECODE(pfi.expenditure_organization_id,l_organization_id,
DECODE(pfi.expenditure_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'REVENUE_TRANSFER',
NVL(SUM(pfi.expfunc_transfer_price),0),0),0),0) tp_rev_in,
DECODE(pfi.project_organization_id,l_organization_id,
DECODE(pfi.project_org_id,l_org_id,
DECODE(pfi.tp_amount_type,'REVENUE_TRANSFER',
NVL(sum(pfi.projfunc_transfer_price),0),0),0),0) tp_rev_out
FROM pa_forecast_items pfi,
gl_date_period_map dpm,
gl_periods gp
WHERE pfi.expenditure_organization_id = l_organization_id
and nvl(pfi.expenditure_org_id,-99) = l_org_id
AND pfi.forecast_item_type in ('A','R','U')
AND pfi.delete_flag = 'N'
AND pfi.error_flag = 'N'
AND pfi.item_date BETWEEN l_fcst_start_date
AND l_fcst_end_date
AND pfi.forecast_amt_calc_flag||'' = 'Y'
AND dpm.period_set_name = l_period_set_name
AND dpm.period_type = l_act_period_type
AND pfi.item_date = dpm.accounting_date
AND dpm.period_set_name = gp.period_set_name
AND dpm.period_type = gp.period_type
AND dpm.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
GROUP BY pfi.project_organization_id,
pfi.project_org_id,
pfi.expenditure_organization_id,
pfi.expenditure_org_id,
pfi.project_id,
nvl(pfi.assignment_id,-1),
nvl(pfi.resource_id,-1),
pfi.tp_amount_type,
pfi.forecast_item_type,
gp.start_date,
gp.end_date,
gp.period_name
ORDER BY 1,6,7,8,10,11,2,3,4,5;
SELECT rlm.resource_list_member_id
INTO l_resource_list_member_id
FROM pa_resource_list_members rlm
WHERE rlm.resource_list_id = l_resource_list_id
AND rlm.resource_type_code = 'UNCATEGORIZED';
SELECT substr(l_org_fcst_period_type,1,1)
INTO l_time_phased_code
FROM sys.dual;
SELECT fin_plan_type_id
INTO l_fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE fin_plan_type_code = 'ORG_FORECAST';
SELECT fin_plan_amount_set_id
INTO l_fin_plan_amount_set_id
FROM pa_fin_plan_amount_sets
WHERE amount_set_type_code = 'ALL'
AND fin_plan_amount_set_id = 1; /* Fix for 2744924. */
SELECT meaning
INTO l_bv_version_name
FROM pa_lookups
WHERE lookup_type = 'TRANSLATION'
AND lookup_code = 'AUTO_GEN_PLAN_VERSION';
SELECT 1
INTO l_active_organization
FROM pa_organizations_project_v
WHERE organization_id = l_organization_id
AND active_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(date_from)
AND NVL(date_to, TRUNC(SYSDATE));
SELECT 1
INTO l_active_organization
FROM pa_organizations_project_v
WHERE organization_id = l_organization_id
AND active_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(date_from)
AND NVL(date_to, TRUNC(SYSDATE));
SELECT hou.name
,hou.business_group_id
,hou.location_id
INTO l_org_name
,l_business_group_id
,l_org_location_id
FROM hr_all_organization_units hou
WHERE hou.organization_id = l_organization_id;
SELECT '-'||decode(substr(to_char(l_org_id),1,1),'-',
substr(to_char(l_org_id),2),to_char(l_org_id))||
'-'||to_char(l_business_group_id)
INTO l_bg_org
FROM sys.dual;
SELECT substrb(l_org_name,1,(30 - lengthb(l_bg_org)))||l_bg_org
,substrb(l_org_name,1,(25 - lengthb(l_bg_org)))||l_bg_org
INTO l_project_name
,l_project_number
FROM DUAL;
UPDATE pa_tasks
SET carrying_out_organization_id = l_organization_id
WHERE project_id = l_project_id
AND task_id = l_own_task_id;
UPDATE pa_tasks
SET carrying_out_organization_id = l_organization_id
WHERE project_id = l_project_id
AND task_id = l_own_task_id;
pa_proj_fp_options_pkg.Insert_Row
( px_proj_fp_options_id => l_proj_fp_options_id
,p_project_id => l_project_id
,p_fin_plan_option_level_code => 'PROJECT'
,p_fin_plan_type_id => NULL
,p_fin_plan_start_date => NULL
,p_fin_plan_end_date => NULL
,p_fin_plan_preference_code => 'COST_AND_REV_SAME'
,p_cost_amount_set_id => NULL
,p_revenue_amount_set_id => NULL
,p_all_amount_set_id => 1
,p_cost_fin_plan_level_code => NULL
,p_cost_time_phased_code => NULL
,p_cost_resource_list_id => NULL
,p_revenue_fin_plan_level_code => NULL
,p_revenue_time_phased_code => NULL
,p_revenue_resource_list_id => NULL
,p_all_fin_plan_level_code => 'L'
,p_all_time_phased_code => l_time_phased_code
,p_all_resource_list_id => l_resource_list_id
,p_report_labor_hrs_from_code => 'COST'
,p_fin_plan_version_id => NULL
,x_row_id => l_row_id
,x_return_status => l_return_status);
pa_proj_fp_options_pkg.Insert_Row
( px_proj_fp_options_id => l_proj_fp_options_id
,p_project_id => l_project_id
,p_fin_plan_option_level_code => 'PLAN_TYPE'
,p_fin_plan_type_id => l_fin_plan_type_id
,p_fin_plan_start_date => NULL
,p_fin_plan_end_date => NULL
,p_fin_plan_preference_code => 'COST_AND_REV_SAME'
,p_cost_amount_set_id => NULL
,p_revenue_amount_set_id => NULL
,p_all_amount_set_id => 1
,p_cost_fin_plan_level_code => NULL
,p_cost_time_phased_code => NULL
,p_cost_resource_list_id => NULL
,p_revenue_fin_plan_level_code => NULL
,p_revenue_time_phased_code => NULL
,p_revenue_resource_list_id => NULL
,p_all_fin_plan_level_code => 'L'
,p_all_time_phased_code => l_time_phased_code
,p_all_resource_list_id => l_resource_list_id
,p_report_labor_hrs_from_code => 'COST'
,p_fin_plan_version_id => NULL
,x_row_id => l_row_id
,x_return_status => l_return_status);
SELECT nvl(max(bv.version_number),0) + 1,
DECODE(nvl(max(bv.version_number),0) + 1,1,'Y','N')
INTO l_bv_version_number
,l_current_working_flag
FROM pa_budget_versions bv
WHERE bv.project_id = l_project_id
AND bv.fin_plan_type_id = l_fin_plan_type_id
AND bv.budget_status_code in ('W','S');
pa_fp_budget_versions_pkg.Insert_Row
( px_budget_version_id => l_budget_version_id
,p_project_id => l_project_id
,p_budget_type_code => NULL
,p_version_number => l_bv_version_number
,p_budget_status_code => 'W'
,p_current_flag => 'N'
,p_original_flag => 'N'
,p_current_original_flag => 'N'
,p_resource_accumulated_flag => 'N'
,p_resource_list_id => l_resource_list_id
,p_version_name => l_bv_version_name
,p_plan_run_date => sysdate
,p_plan_processing_code => 'S'
,p_period_profile_id => l_period_profile_id
,p_fin_plan_type_id => l_fin_plan_type_id
,p_current_working_flag => l_current_working_flag
,p_version_type => 'ORG_FORECAST'
,x_row_id => l_row_id
,x_return_status => l_return_status);
pa_proj_fp_options_pkg.Insert_Row
( px_proj_fp_options_id => l_proj_fp_options_id
,p_project_id => l_project_id
,p_fin_plan_option_level_code => 'PLAN_VERSION'
,p_fin_plan_type_id => l_fin_plan_type_id
,p_fin_plan_start_date => l_fcst_start_date
,p_fin_plan_end_date => l_fcst_end_date
,p_fin_plan_preference_code => 'COST_AND_REV_SAME'
,p_cost_amount_set_id => NULL
,p_revenue_amount_set_id => NULL
,p_all_amount_set_id => 1
,p_cost_fin_plan_level_code => NULL
,p_cost_time_phased_code => NULL
,p_cost_resource_list_id => NULL
,p_revenue_fin_plan_level_code => NULL
,p_revenue_time_phased_code => NULL
,p_revenue_resource_list_id => NULL
,p_all_fin_plan_level_code => 'L'
,p_all_time_phased_code => l_time_phased_code
,p_all_resource_list_id => l_resource_list_id
,p_report_labor_hrs_from_code => 'COST'
,p_fin_plan_version_id => l_budget_version_id
,x_row_id => l_row_id
,x_return_status => l_return_status);
SELECT bv.budget_version_id,
bv.project_id,
bv.record_version_number,
bv.period_profile_id,
pa.carrying_out_organization_id,
pa.projfunc_currency_code
INTO l_budget_version_id,
l_project_id,
l_bv_rec_ver_num,
l_period_profile_id,
l_organization_id,
l_org_projfunc_currency_code
FROM pa_budget_versions bv,
pa_projects pa
WHERE bv.budget_version_id = p_budget_version_id
AND pa.project_id = bv.project_id;
UPDATE pa_tasks
SET carrying_out_organization_id = l_organization_id
WHERE project_id = l_project_id
AND task_id = l_own_task_id;
/* DELETE existing data for budget version */
l_stage := 4300;
pa_fin_plan_pub.Delete_Version_Helper
(p_budget_version_id => p_budget_version_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE pa_budget_versions
SET
version_name = DECODE(version_name,null,l_bv_version_name
||'-'||to_char(l_budget_version_id),
version_name)
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
,revenue = 0
,raw_cost = 0
,burdened_cost = 0
,labor_quantity = 0
,total_borrowed_revenue = 0
,total_tp_revenue_in = 0
,total_tp_revenue_out = 0
,total_lent_resource_cost = 0
,total_tp_cost_in = 0
,total_tp_cost_out = 0
,total_unassigned_time_cost = 0
,total_utilization_percent = 0
,total_utilization_hours = 0
,total_capacity = 0
,total_head_count = 0
,total_revenue_adj = 0
,total_cost_adj = 0
,total_utilization_adj = 0
,total_head_count_adj = 0
WHERE budget_version_id = p_budget_version_id;
- Update Resource Assignments
- Update Budget Versions
- Create Proj Denorm for budget lines
*/
l_stage := 5300;
amt_rec.delete;
/* DELETE FROM ALL plsql tables */
/* For forecast items */
l_fi_organization_id_tab.delete;
l_fi_proj_organization_tab.delete;
l_fi_proj_orgid_tab.delete;
l_fi_exp_organization_id_tab.delete;
l_fi_exp_orgid_tab.delete;
l_fi_txn_project_id_tab.delete;
l_fi_assignment_id_tab.delete;
l_fi_resource_id_tab.delete;
l_fi_period_name_tab.delete;
l_fi_start_date_tab.delete;
l_fi_end_date_tab.delete;
l_fi_item_quantity_tab.delete;
l_fi_raw_cost_tab.delete;
l_fi_burdened_cost_tab.delete;
l_fi_tp_cost_in_tab.delete;
l_fi_tp_cost_out_tab.delete;
l_fi_revenue_tab.delete;
l_fi_tp_rev_in_tab.delete;
l_fi_tp_rev_out_tab.delete;
l_fi_borrowed_revenue_tab.delete;
l_fi_lent_resource_cost_tab.delete;
l_fi_unassigned_time_cost_tab.delete;
l_fe_forecast_element_id_tab.delete;
l_fe_organization_id_tab.delete;
l_fe_org_id_tab.delete;
l_fe_budget_version_id_tab.delete;
l_fe_project_id_tab.delete;
l_fe_task_id_tab.delete;
l_fe_pvdr_rcvr_code_tab.delete;
l_fe_other_organization_id_tab.delete;
l_fe_other_org_id_tab.delete;
l_fe_txn_project_id_tab.delete;
l_fe_assignment_id_tab.delete;
l_fe_resource_id_tab.delete;
l_fl_forecast_line_id_tab.delete;
l_fl_forecast_element_id_tab.delete;
l_fl_budget_version_id_tab.delete;
l_fl_project_id_tab.delete;
l_fl_task_id_tab.delete;
l_fl_period_name_tab.delete;
l_fl_start_date_tab.delete;
l_fl_end_date_tab.delete;
l_fl_quantity_tab.delete;
l_fl_raw_cost_tab.delete;
l_fl_burdened_cost_tab.delete;
l_fl_tp_cost_in_tab.delete;
l_fl_tp_cost_out_tab.delete;
l_fl_revenue_tab.delete;
l_fl_tp_rev_in_tab.delete;
l_fl_tp_rev_out_tab.delete;
l_fl_borrowed_revenue_tab.delete;
l_fl_lent_resource_cost_tab.delete;
l_fl_unassigned_time_cost_tab.delete;
l_bl_res_asg_id_tab.delete;
l_bl_budget_version_id_tab.delete; /* FPB2 */
l_bl_start_date_tab.delete;
l_bl_end_date_tab.delete;
l_bl_period_name_tab.delete;
l_bl_quantity_tab.delete;
l_bl_raw_cost_tab.delete;
l_bl_burdened_cost_tab.delete;
l_bl_revenue_tab.delete;
l_bl_borrowed_revenue_tab.delete;
l_bl_tp_revenue_in_tab.delete;
l_bl_tp_revenue_out_tab.delete;
l_bl_lent_resource_cost_tab.delete;
l_bl_tp_cost_in_tab.delete;
l_bl_tp_cost_out_tab.delete;
l_bl_unassigned_time_cost_tab.delete;
l_bl_utilization_percent_tab.delete;
l_bl_utilization_hours_tab.delete;
l_bl_capacity_tab.delete;
l_bl_head_count_tab.delete;
l_fi_organization_id_tab.delete;
l_fi_proj_organization_tab.delete;
l_fi_proj_orgid_tab.delete;
l_fi_exp_organization_id_tab.delete;
l_fi_exp_orgid_tab.delete;
l_fi_txn_project_id_tab.delete;
l_fi_assignment_id_tab.delete;
l_fi_resource_id_tab.delete;
l_fi_period_name_tab.delete;
l_fi_start_date_tab.delete;
l_fi_end_date_tab.delete;
l_fi_item_quantity_tab.delete;
l_fi_raw_cost_tab.delete;
l_fi_burdened_cost_tab.delete;
l_fi_tp_cost_in_tab.delete;
l_fi_tp_cost_out_tab.delete;
l_fi_revenue_tab.delete;
l_fi_tp_rev_in_tab.delete;
l_fi_tp_rev_out_tab.delete;
l_fi_borrowed_revenue_tab.delete;
l_fi_lent_resource_cost_tab.delete;
l_fi_unassigned_time_cost_tab.delete;
/* Transfer Price Logic as part of Select statement:
Org Context TP Amount Type CC In CC Out Rev In Rev Out
----------- -------------- ----- ------ ------ -------
Provider COST 0 Y 0 0
Provider REVENUE 0 0 Y 0
Receiver COST Y 0 0 0
Receiver REVENUE 0 0 0 Y
*/
FOR i in l_fi_organization_id_tab.first..l_fi_organization_id_tab.last LOOP
BEGIN
-- hr_utility.trace('Orgn id=> '||to_char(l_fi_organization_id_tab(i)));
SELECT pa_org_fcst_elements_s.NEXTVAL
INTO l_fe_new_seq
FROM dual;
SELECT pa_org_forecast_lines_s.NEXTVAL
INTO l_fl_new_seq
FROM DUAL;
SELECT pa_org_forecast_lines_s.NEXTVAL
INTO l_fl_new_seq
FROM DUAL;
/* Bulk Insert into Forecast Elements */
pa_debug.g_err_stage := l_stage||': fe_count = ['||l_fe_forecast_element_id_tab.count||']';
INSERT INTO pa_org_fcst_elements
( forecast_element_id
,organization_id
,org_id
,budget_version_id
,project_id
,task_id
,provider_receiver_code
,other_organization_id
,other_org_id
,txn_project_id
,assignment_id
,resource_id
,record_version_number
,creation_date
,created_by
,last_update_login
,last_updated_by
,last_update_date
) VALUES (
l_fe_forecast_element_id_tab(i)
,l_fe_organization_id_tab(i)
,l_fe_org_id_tab(i)
,l_fe_budget_version_id_tab(i)
,l_fe_project_id_tab(i)
,l_fe_task_id_tab(i)
,l_fe_pvdr_rcvr_code_tab(i)
,l_fe_other_organization_id_tab(i)
,l_fe_other_org_id_tab(i)
,l_fe_txn_project_id_tab(i)
,l_fe_assignment_id_tab(i)
,l_fe_resource_id_tab(i)
,1
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,fnd_global.user_id
,sysdate);
': After Inserting Forecast Elements' ;
': Inserted [' || TO_CHAR(l_records_affected) ||
'] Forecast Elements.';
/* Bulk Insert into Forecast Lines */
l_stage := 7400;
INSERT INTO pa_org_forecast_lines
( forecast_line_id
,forecast_element_id
,budget_version_id
,project_id
,task_id
,period_name
,start_date
,end_date
,quantity
,raw_cost
,burdened_cost
,tp_cost_in
,tp_cost_out
,revenue
,tp_revenue_in
,tp_revenue_out
,borrowed_revenue
,lent_resource_cost
,unassigned_time_cost
,record_version_number
,creation_date
,created_by
,last_update_login
,last_updated_by
,last_update_date
) VALUES (
l_fl_forecast_line_id_tab(j)
,l_fl_forecast_element_id_tab(j)
,l_fl_budget_version_id_tab(j)
,l_fl_project_id_tab(j)
,l_fl_task_id_tab(j)
,l_fl_period_name_tab(j)
,l_fl_start_date_tab(j)
,l_fl_end_date_tab(j)
,l_fl_quantity_tab(j)
,l_fl_raw_cost_tab(j)
,l_fl_burdened_cost_tab(j)
,l_fl_tp_cost_in_tab(j)
,l_fl_tp_cost_out_tab(j)
,l_fl_revenue_tab(j)
,l_fl_tp_rev_in_tab(j)
,l_fl_tp_rev_out_tab(j)
,l_fl_borrowed_revenue_tab(j)
,l_fl_lent_resource_cost_tab(j)
,l_fl_unassigned_time_cost_tab(j)
,1
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,fnd_global.user_id
,sysdate);
': After Inserting Forecast Lines' ;
': Inserted [' || TO_CHAR(l_records_affected) ||
'] Forecast Lines.';
DELETE FROM pa_fin_plan_lines_tmp;
INSERT INTO pa_fin_plan_lines_tmp
( resource_assignment_id
,object_id
,object_type_code
,period_name
,start_date
,end_date
,currency_type
,currency_code
,source_txn_currency_code /* Bug 2796261 */
,quantity
,raw_cost
,burdened_cost
,tp_cost_in
,tp_cost_out
,lent_resource_cost
,unassigned_time_cost
,cost_adj
,revenue
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,revenue_adj
,utilization_percent
,utilization_adj
,utilization_hours
,capacity
,head_count
,head_count_adj
,margin
,margin_percentage)
SELECT
-1
,l_fl_forecast_element_id_tab(j)
,'FCST_ELEMENTS'
,l_fl_period_name_tab(j)
,l_fl_start_date_tab(j)
,l_fl_end_date_tab(j)
,'PROJ_FUNCTIONAL'
,l_org_projfunc_currency_code
,l_org_projfunc_currency_code /* 2796261 */
,l_fl_quantity_tab(j)
,l_fl_raw_cost_tab(j)
,l_fl_burdened_cost_tab(j)
,l_fl_tp_cost_in_tab(j)
,l_fl_tp_cost_out_tab(j)
,l_fl_lent_resource_cost_tab(j)
,l_fl_unassigned_time_cost_tab(j)
,0
,l_fl_revenue_tab(j)
,l_fl_borrowed_revenue_tab(j)
,l_fl_tp_rev_in_tab(j)
,l_fl_tp_rev_out_tab(j)
,0
,0
,0
,0
,0
,0
,0
,DECODE(SIGN(
l_fl_revenue_tab(j)+
l_fl_borrowed_revenue_tab(j)+
l_fl_tp_rev_in_tab(j)-
l_fl_tp_rev_out_tab(j)),0,0,-1,0,
(l_fl_revenue_tab(j)+
l_fl_borrowed_revenue_tab(j)+
l_fl_tp_rev_in_tab(j)-
l_fl_tp_rev_out_tab(j)) -
(l_fl_burdened_cost_tab(j)+
l_fl_lent_resource_cost_tab(j)+
l_fl_unassigned_time_cost_tab(j)+
l_fl_tp_cost_in_tab(j)-
l_fl_tp_cost_out_tab(j)))
,DECODE(SIGN(
l_fl_revenue_tab(j)+
l_fl_borrowed_revenue_tab(j)+
l_fl_tp_rev_in_tab(j)-
l_fl_tp_rev_out_tab(j)),0,0,-1,0,
((l_fl_revenue_tab(j)+
l_fl_borrowed_revenue_tab(j)+
l_fl_tp_rev_in_tab(j)-
l_fl_tp_rev_out_tab(j)) -
(l_fl_burdened_cost_tab(j)+
l_fl_lent_resource_cost_tab(j)+
l_fl_unassigned_time_cost_tab(j)+
l_fl_tp_cost_in_tab(j)-
l_fl_tp_cost_out_tab(j)))/
(l_fl_revenue_tab(j)+
l_fl_borrowed_revenue_tab(j)+
l_fl_tp_rev_in_tab(j)-
l_fl_tp_rev_out_tab(j)) * 100)
FROM DUAL;
': After Inserting Fin Plan Lines Tmp' ;
': Inserted [' || TO_CHAR(l_records_affected) ||
'] Fin Plan Lines Tmp.';
l_fi_organization_id_tab.delete;
l_fi_proj_organization_tab.delete;
l_fi_proj_orgid_tab.delete;
l_fi_exp_organization_id_tab.delete;
l_fi_exp_orgid_tab.delete;
l_fi_txn_project_id_tab.delete;
l_fi_assignment_id_tab.delete;
l_fi_resource_id_tab.delete;
l_fi_period_name_tab.delete;
l_fi_start_date_tab.delete;
l_fi_end_date_tab.delete;
l_fi_item_quantity_tab.delete;
l_fi_raw_cost_tab.delete;
l_fi_burdened_cost_tab.delete;
l_fi_tp_cost_in_tab.delete;
l_fi_tp_cost_out_tab.delete;
l_fi_revenue_tab.delete;
l_fi_tp_rev_in_tab.delete;
l_fi_tp_rev_out_tab.delete;
l_fi_borrowed_revenue_tab.delete;
l_fi_lent_resource_cost_tab.delete;
l_fi_unassigned_time_cost_tab.delete;
l_fe_forecast_element_id_tab.delete;
l_fe_organization_id_tab.delete;
l_fe_org_id_tab.delete;
l_fe_budget_version_id_tab.delete;
l_fe_project_id_tab.delete;
l_fe_task_id_tab.delete;
l_fe_pvdr_rcvr_code_tab.delete;
l_fe_other_organization_id_tab.delete;
l_fe_other_org_id_tab.delete;
l_fe_txn_project_id_tab.delete;
l_fe_assignment_id_tab.delete;
l_fe_resource_id_tab.delete;
l_fl_forecast_line_id_tab.delete;
l_fl_forecast_element_id_tab.delete;
l_fl_budget_version_id_tab.delete;
l_fl_project_id_tab.delete;
l_fl_task_id_tab.delete;
l_fl_period_name_tab.delete;
l_fl_start_date_tab.delete;
l_fl_end_date_tab.delete;
l_fl_quantity_tab.delete;
l_fl_raw_cost_tab.delete;
l_fl_burdened_cost_tab.delete;
l_fl_tp_cost_in_tab.delete;
l_fl_tp_cost_out_tab.delete;
l_fl_revenue_tab.delete;
l_fl_tp_rev_in_tab.delete;
l_fl_tp_rev_out_tab.delete;
l_fl_borrowed_revenue_tab.delete;
l_fl_lent_resource_cost_tab.delete;
l_fl_unassigned_time_cost_tab.delete;
amt_rec.delete;
pa_fp_resource_assignments_pkg.Insert_Row
( px_resource_assignment_id => l_own_resource_assignment_id
,p_budget_version_id => l_budget_version_id
,p_project_id => l_project_id
,p_task_id => l_own_task_id
,p_resource_list_member_id => l_resource_list_member_id
,p_unit_of_measure => 'HOURS'
,p_track_as_labor_flag => 'Y'
,p_standard_bill_rate => Null
,p_average_bill_rate => Null
,p_average_cost_rate => Null
,p_project_assignment_id => -1
,p_plan_error_code => Null
,p_total_plan_revenue => 0
,p_total_plan_raw_cost => 0
,p_total_plan_burdened_cost => 0
,p_total_plan_quantity => 0
,p_average_discount_percentage => Null
,p_total_borrowed_revenue => 0
,p_total_tp_revenue_in => 0
,p_total_tp_revenue_out => 0
,p_total_revenue_adj => 0
,p_total_lent_resource_cost => 0
,p_total_tp_cost_in => 0
,p_total_tp_cost_out => 0
,p_total_cost_adj => 0
,p_total_unassigned_time_cost => 0
,p_total_utilization_percent => 0
,p_total_utilization_hours => 0
,p_total_utilization_adj => 0
,p_total_capacity => 0
,p_total_head_count => 0
,p_total_head_count_adj => 0
,p_resource_assignment_type => 'OWN'
,x_row_id => l_row_id
,x_return_status => l_return_status );
l_bl_budget_version_id_tab.delete; /* FPB2: budget_version_id */
l_bl_res_asg_id_tab.delete;
l_bl_start_date_tab.delete;
l_bl_end_date_tab.delete;
l_bl_period_name_tab.delete;
l_bl_quantity_tab.delete;
l_bl_raw_cost_tab.delete;
l_bl_burdened_cost_tab.delete;
l_bl_revenue_tab.delete;
l_bl_borrowed_revenue_tab.delete;
l_bl_tp_revenue_in_tab.delete;
l_bl_tp_revenue_out_tab.delete;
l_bl_lent_resource_cost_tab.delete;
l_bl_tp_cost_in_tab.delete;
l_bl_tp_cost_out_tab.delete;
l_bl_unassigned_time_cost_tab.delete;
l_bl_utilization_percent_tab.delete;
l_bl_utilization_hours_tab.delete;
l_bl_capacity_tab.delete;
l_bl_head_count_tab.delete;
/* Bulk insert into Budget Lines for Own Numbers Phase I */
IF l_bl_res_asg_id_tab.count > 0 THEN
FORALL i in l_bl_res_asg_id_tab.first..l_bl_res_asg_id_tab.last
INSERT INTO pa_budget_lines
( budget_line_id /* FPB2 */
,budget_version_id /* FPB2 */
,txn_currency_code /* FPB4 */
,resource_assignment_id
,period_name
,start_date
,end_date
,quantity
,raw_cost
,burdened_cost
,lent_resource_cost
,unassigned_time_cost
,tp_cost_in
,tp_cost_out
,revenue
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,quantity_source
,raw_cost_source
,burdened_cost_source
,revenue_source
,utilization_percent
,utilization_hours
,capacity
,head_count
,creation_date
,created_by
,last_update_login
,last_updated_by
,last_update_date
) VALUES (
pa_budget_lines_s.nextval /* FPB2 */
,l_bl_budget_version_id_tab(i) /* FPB2 */
,l_org_projfunc_currency_code /* FPB4 */
,l_bl_res_asg_id_tab(i)
,l_bl_period_name_tab(i)
,l_bl_start_date_tab(i)
,l_bl_end_date_tab(i)
,l_bl_quantity_tab(i)
,l_bl_raw_cost_tab(i)
,l_bl_burdened_cost_tab(i)
,l_bl_lent_resource_cost_tab(i)
,l_bl_unassigned_time_cost_tab(i)
,l_bl_tp_cost_in_tab(i)
,l_bl_tp_cost_out_tab(i)
,l_bl_revenue_tab(i)
,l_bl_borrowed_revenue_tab(i)
,l_bl_tp_revenue_in_tab(i)
,l_bl_tp_revenue_out_tab(i)
,'C'
,'C'
,'C'
,'C'
,l_bl_utilization_percent_tab(i)
,l_bl_utilization_hours_tab(i)
,l_bl_capacity_tab(i)
,l_bl_head_count_tab(i)
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,fnd_global.user_id
,sysdate);
/* Need to update Own Level Resource Assignment
from budget Lines */
SELECT nvl(sum(bl.quantity),0)
,nvl(sum(bl.raw_cost),0)
,nvl(sum(bl.burdened_cost),0)
,nvl(sum(bl.lent_resource_cost),0)
,nvl(sum(bl.unassigned_time_cost),0)
,nvl(sum(bl.tp_cost_in),0)
,nvl(sum(bl.tp_cost_out),0)
,nvl(sum(bl.revenue),0)
,nvl(sum(bl.borrowed_revenue),0)
,nvl(sum(bl.tp_revenue_in),0)
,nvl(sum(bl.tp_revenue_out),0)
,nvl(round(avg(bl.utilization_percent)),0)
,nvl(sum(bl.utilization_hours),0)
,nvl(sum(bl.capacity),0)
,nvl(round(avg(bl.head_count)),0)
INTO budget_lines_rec(1).quantity
,budget_lines_rec(1).raw_cost
,budget_lines_rec(1).burdened_cost
,budget_lines_rec(1).lent_resource_cost
,budget_lines_rec(1).unassigned_time_cost
,budget_lines_rec(1).tp_cost_in
,budget_lines_rec(1).tp_cost_out
,budget_lines_rec(1).revenue
,budget_lines_rec(1).borrowed_revenue
,budget_lines_rec(1).tp_revenue_in
,budget_lines_rec(1).tp_revenue_out
,budget_lines_rec(1).utilization_percent
,budget_lines_rec(1).utilization_hours
,budget_lines_rec(1).capacity
,budget_lines_rec(1).head_count
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = l_own_resource_assignment_id;
pa_debug.g_err_stage := l_stage||': Update Resource Assignments for Own Task';
UPDATE pa_resource_assignments
SET
last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
,total_plan_revenue = budget_lines_rec(1).revenue
,total_plan_raw_cost = budget_lines_rec(1).raw_cost
,total_plan_burdened_cost = budget_lines_rec(1).burdened_cost
,total_plan_quantity = budget_lines_rec(1).quantity
,total_borrowed_revenue = budget_lines_rec(1).borrowed_revenue
,total_tp_revenue_in = budget_lines_rec(1).tp_revenue_in
,total_tp_revenue_out = budget_lines_rec(1).tp_revenue_out
,total_lent_resource_cost = budget_lines_rec(1).lent_resource_cost
,total_tp_cost_in = budget_lines_rec(1).tp_cost_in
,total_tp_cost_out = budget_lines_rec(1).tp_cost_out
,total_unassigned_time_cost = budget_lines_rec(1).unassigned_time_cost
,total_utilization_percent = budget_lines_rec(1).utilization_percent
,total_utilization_hours = budget_lines_rec(1).utilization_hours
,total_capacity = budget_lines_rec(1).capacity
,total_head_count = budget_lines_rec(1).head_count
WHERE resource_assignment_id = l_own_resource_assignment_id;
budget_lines_rec.DELETE;
DELETE FROM pa_fin_plan_lines_tmp;
INSERT INTO pa_fin_plan_lines_tmp
( resource_assignment_id
,object_id
,object_type_code
,period_name
,start_date
,end_date
,currency_type
,currency_code
,source_txn_currency_code /* Bug 2796261 */
,quantity
,raw_cost
,burdened_cost
,tp_cost_in
,tp_cost_out
,lent_resource_cost
,unassigned_time_cost
,cost_adj
,revenue
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,revenue_adj
,utilization_percent
,utilization_adj
,utilization_hours
,capacity
,head_count
,head_count_adj
,margin
,margin_percentage)
SELECT
l_bl_res_asg_id_tab(i)
,l_bl_res_asg_id_tab(i)
,'RES_ASSIGNMENT'
,l_bl_period_name_tab(i)
,l_bl_start_date_tab(i)
,l_bl_end_date_tab(i)
,'PROJ_FUNCTIONAL'
,l_org_projfunc_currency_code
,l_org_projfunc_currency_code /* 2796261 */
,l_bl_quantity_tab(i)
,l_bl_raw_cost_tab(i)
,l_bl_burdened_cost_tab(i)
,l_bl_tp_cost_in_tab(i)
,l_bl_tp_cost_out_tab(i)
,l_bl_lent_resource_cost_tab(i)
,l_bl_unassigned_time_cost_tab(i)
,0
,l_bl_revenue_tab(i)
,l_bl_borrowed_revenue_tab(i)
,l_bl_tp_revenue_in_tab(i)
,l_bl_tp_revenue_out_tab(i)
,0
,l_bl_utilization_percent_tab(i)
,0
,l_bl_utilization_hours_tab(i)
,l_bl_capacity_tab(i)
,l_bl_head_count_tab(i)
,0
,DECODE(SIGN(
l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)),0,0,-1,0,
(l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)) -
(l_bl_burdened_cost_tab(i)+
l_bl_lent_resource_cost_tab(i)+
l_bl_unassigned_time_cost_tab(i)+
l_bl_tp_cost_in_tab(i)-
l_bl_tp_cost_out_tab(i)))
,DECODE(SIGN(
l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)),0,0,-1,0,
((l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)) -
(l_bl_burdened_cost_tab(i)+
l_bl_lent_resource_cost_tab(i)+
l_bl_unassigned_time_cost_tab(i)+
l_bl_tp_cost_in_tab(i)-
l_bl_tp_cost_out_tab(i)))/
(l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)) * 100)
FROM DUAL;
': After Inserting Fin Plan Lines Tmp' ;
': Inserted [' || TO_CHAR(l_records_affected) ||
'] Fin Plan Lines Tmp.';
pa_fp_resource_assignments_pkg.Insert_Row
( px_resource_assignment_id => l_proj_resource_assignment_id
,p_budget_version_id => l_budget_version_id
,p_project_id => l_project_id
,p_task_id => 0
,p_resource_list_member_id => l_resource_list_member_id
,p_unit_of_measure => 'HOURS'
,p_track_as_labor_flag => 'Y'
,p_standard_bill_rate => Null
,p_average_bill_rate => Null
,p_average_cost_rate => Null
,p_project_assignment_id => -1
,p_plan_error_code => Null
,p_total_plan_revenue => 0
,p_total_plan_raw_cost => 0
,p_total_plan_burdened_cost => 0
,p_total_plan_quantity => 0
,p_average_discount_percentage => Null
,p_total_borrowed_revenue => 0
,p_total_tp_revenue_in => 0
,p_total_tp_revenue_out => 0
,p_total_revenue_adj => 0
,p_total_lent_resource_cost => 0
,p_total_tp_cost_in => 0
,p_total_tp_cost_out => 0
,p_total_cost_adj => 0
,p_total_unassigned_time_cost => 0
,p_total_utilization_percent => 0
,p_total_utilization_hours => 0
,p_total_utilization_adj => 0
,p_total_capacity => 0
,p_total_head_count => 0
,p_total_head_count_adj => 0
,p_resource_assignment_type => 'PROJECT'
,x_row_id => l_row_id
,x_return_status => l_return_status );
l_bl_budget_version_id_tab.delete; /* FPB2: budget_version_id */
l_bl_res_asg_id_tab.delete;
l_bl_start_date_tab.delete;
l_bl_end_date_tab.delete;
l_bl_period_name_tab.delete;
l_bl_quantity_tab.delete;
l_bl_raw_cost_tab.delete;
l_bl_burdened_cost_tab.delete;
l_bl_revenue_tab.delete;
l_bl_borrowed_revenue_tab.delete;
l_bl_tp_revenue_in_tab.delete;
l_bl_tp_revenue_out_tab.delete;
l_bl_lent_resource_cost_tab.delete;
l_bl_tp_cost_in_tab.delete;
l_bl_tp_cost_out_tab.delete;
l_bl_unassigned_time_cost_tab.delete;
l_bl_utilization_percent_tab.delete;
l_bl_utilization_hours_tab.delete;
l_bl_capacity_tab.delete;
l_bl_head_count_tab.delete;
/* Bulk insert into Budget Lines for Project Numbers */
pa_debug.g_err_stage := l_stage||': Create Budget Lines for Project Level Task';
INSERT INTO pa_budget_lines
( budget_line_id /* FPB2 budget_line_id */
,budget_version_id /* FPB2 */
,txn_currency_code /* FPB4 */
,resource_assignment_id
,period_name
,start_date
,end_date
,quantity
,raw_cost
,burdened_cost
,lent_resource_cost
,unassigned_time_cost
,tp_cost_in
,tp_cost_out
,revenue
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,quantity_source
,raw_cost_source
,burdened_cost_source
,revenue_source
,utilization_percent
,utilization_hours
,capacity
,head_count
,creation_date
,created_by
,last_update_login
,last_updated_by
,last_update_date
) VALUES (
pa_budget_lines_s.nextval /* FPB2: budget_line_id */
,l_bl_budget_version_id_tab(i) /* FPB2: budget_version_id */
,l_org_projfunc_currency_code /* FPB4 */
,l_bl_res_asg_id_tab(i)
,l_bl_period_name_tab(i)
,l_bl_start_date_tab(i)
,l_bl_end_date_tab(i)
,l_bl_quantity_tab(i)
,l_bl_raw_cost_tab(i)
,l_bl_burdened_cost_tab(i)
,l_bl_lent_resource_cost_tab(i)
,l_bl_unassigned_time_cost_tab(i)
,l_bl_tp_cost_in_tab(i)
,l_bl_tp_cost_out_tab(i)
,l_bl_revenue_tab(i)
,l_bl_borrowed_revenue_tab(i)
,l_bl_tp_revenue_in_tab(i)
,l_bl_tp_revenue_out_tab(i)
,'C'
,'C'
,'C'
,'C'
,l_bl_utilization_percent_tab(i)
,l_bl_utilization_hours_tab(i)
,l_bl_capacity_tab(i)
,l_bl_head_count_tab(i)
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,fnd_global.user_id
,sysdate);
/* Need to update Project Level Resource Assignment
from budget Lines */
SELECT nvl(sum(bl.quantity),0)
,nvl(sum(bl.raw_cost),0)
,nvl(sum(bl.burdened_cost),0)
,nvl(sum(bl.lent_resource_cost),0)
,nvl(sum(bl.unassigned_time_cost),0)
,nvl(sum(bl.tp_cost_in),0)
,nvl(sum(bl.tp_cost_out),0)
,nvl(sum(bl.revenue),0)
,nvl(sum(bl.borrowed_revenue),0)
,nvl(sum(bl.tp_revenue_in),0)
,nvl(sum(bl.tp_revenue_out),0)
,nvl(round(avg(bl.utilization_percent)),0)
,nvl(sum(bl.utilization_hours),0)
,nvl(sum(bl.capacity),0)
,nvl(round(avg(bl.head_count)),0)
INTO budget_lines_rec(1).quantity
,budget_lines_rec(1).raw_cost
,budget_lines_rec(1).burdened_cost
,budget_lines_rec(1).lent_resource_cost
,budget_lines_rec(1).unassigned_time_cost
,budget_lines_rec(1).tp_cost_in
,budget_lines_rec(1).tp_cost_out
,budget_lines_rec(1).revenue
,budget_lines_rec(1).borrowed_revenue
,budget_lines_rec(1).tp_revenue_in
,budget_lines_rec(1).tp_revenue_out
,budget_lines_rec(1).utilization_percent
,budget_lines_rec(1).utilization_hours
,budget_lines_rec(1).capacity
,budget_lines_rec(1).head_count
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = l_proj_resource_assignment_id;
pa_debug.g_err_stage := l_stage||': Update Resource Assignments for Project Task';
UPDATE pa_resource_assignments
SET
last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
,total_plan_revenue = budget_lines_rec(1).revenue
,total_plan_raw_cost = budget_lines_rec(1).raw_cost
,total_plan_burdened_cost = budget_lines_rec(1).burdened_cost
,total_plan_quantity = budget_lines_rec(1).quantity
,total_borrowed_revenue = budget_lines_rec(1).borrowed_revenue
,total_tp_revenue_in = budget_lines_rec(1).tp_revenue_in
,total_tp_revenue_out = budget_lines_rec(1).tp_revenue_out
,total_lent_resource_cost = budget_lines_rec(1).lent_resource_cost
,total_tp_cost_in = budget_lines_rec(1).tp_cost_in
,total_tp_cost_out = budget_lines_rec(1).tp_cost_out
,total_unassigned_time_cost = budget_lines_rec(1).unassigned_time_cost
,total_utilization_percent = budget_lines_rec(1).utilization_percent
,total_utilization_hours = budget_lines_rec(1).utilization_hours
,total_capacity = budget_lines_rec(1).capacity
,total_head_count = budget_lines_rec(1).head_count
WHERE resource_assignment_id = l_proj_resource_assignment_id;
pa_debug.g_err_stage := l_stage||': Update Budget Version for Project Level Numbers';
UPDATE pa_budget_versions
SET
record_version_number = record_version_number+1
,version_name = DECODE(version_name,null,l_bv_version_name
||'-'||to_char(l_budget_version_id),
version_name)
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
,revenue = budget_lines_rec(1).revenue
,raw_cost = budget_lines_rec(1).raw_cost
,burdened_cost = budget_lines_rec(1).burdened_cost
,labor_quantity = budget_lines_rec(1).quantity
,total_borrowed_revenue = budget_lines_rec(1).borrowed_revenue
,total_tp_revenue_in = budget_lines_rec(1).tp_revenue_in
,total_tp_revenue_out = budget_lines_rec(1).tp_revenue_out
,total_lent_resource_cost = budget_lines_rec(1).lent_resource_cost
,total_tp_cost_in = budget_lines_rec(1).tp_cost_in
,total_tp_cost_out = budget_lines_rec(1).tp_cost_out
,total_unassigned_time_cost = budget_lines_rec(1).unassigned_time_cost
,total_utilization_percent = budget_lines_rec(1).utilization_percent
,total_utilization_hours = budget_lines_rec(1).utilization_hours
,total_capacity = budget_lines_rec(1).capacity
,total_head_count = budget_lines_rec(1).head_count
,plan_processing_code = 'G'
WHERE budget_version_id = l_budget_version_id;
budget_lines_rec.DELETE;
DELETE FROM pa_fin_plan_lines_tmp;
INSERT INTO pa_fin_plan_lines_tmp
( resource_assignment_id
,object_id
,object_type_code
,period_name
,start_date
,end_date
,currency_type
,currency_code
,source_txn_currency_code /* Bug 2796261 */
,quantity
,raw_cost
,burdened_cost
,tp_cost_in
,tp_cost_out
,lent_resource_cost
,unassigned_time_cost
,cost_adj
,revenue
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,revenue_adj
,utilization_percent
,utilization_adj
,utilization_hours
,capacity
,head_count
,head_count_adj
,margin
,margin_percentage)
SELECT
l_bl_res_asg_id_tab(i)
,l_bl_res_asg_id_tab(i)
,'RES_ASSIGNMENT'
,l_bl_period_name_tab(i)
,l_bl_start_date_tab(i)
,l_bl_end_date_tab(i)
,'PROJ_FUNCTIONAL'
,l_org_projfunc_currency_code
,l_org_projfunc_currency_code /* 2796261 */
,l_bl_quantity_tab(i)
,l_bl_raw_cost_tab(i)
,l_bl_burdened_cost_tab(i)
,l_bl_tp_cost_in_tab(i)
,l_bl_tp_cost_out_tab(i)
,l_bl_lent_resource_cost_tab(i)
,l_bl_unassigned_time_cost_tab(i)
,0
,l_bl_revenue_tab(i)
,l_bl_borrowed_revenue_tab(i)
,l_bl_tp_revenue_in_tab(i)
,l_bl_tp_revenue_out_tab(i)
,0
,l_bl_utilization_percent_tab(i)
,0
,l_bl_utilization_hours_tab(i)
,l_bl_capacity_tab(i)
,l_bl_head_count_tab(i)
,0
,DECODE(SIGN(
l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)),0,0,-1,0,
(l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)) -
(l_bl_burdened_cost_tab(i)+
l_bl_lent_resource_cost_tab(i)+
l_bl_unassigned_time_cost_tab(i)+
l_bl_tp_cost_in_tab(i)-
l_bl_tp_cost_out_tab(i)))
,DECODE(SIGN(
l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)),0,0,-1,0,
((l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)) -
(l_bl_burdened_cost_tab(i)+
l_bl_lent_resource_cost_tab(i)+
l_bl_unassigned_time_cost_tab(i)+
l_bl_tp_cost_in_tab(i)-
l_bl_tp_cost_out_tab(i)))/
(l_bl_revenue_tab(i)+
l_bl_borrowed_revenue_tab(i)+
l_bl_tp_revenue_in_tab(i)-
l_bl_tp_revenue_out_tab(i)) * 100)
FROM DUAL;
': After Inserting Fin Plan Lines Tmp' ;
': Inserted [' || TO_CHAR(l_records_affected) ||
'] Fin Plan Lines Tmp.';
argument2 => '02', -- P_SELECT_CRITERIA
argument3 => NULL, -- P_PROJECT_FLAG
argument4 => NULL, -- P_PROJECT_ID
argument5 => NULL, -- P_ASSIGNMENT_ID
argument6 => 1, -- P_ORGANIZATION_FLAG
argument7 => l_organization_id, -- P_ORGANIZATION_ID
argument8 => NULL, -- P_START_ORGANIZATION_FLAG
argument9 => NULL); -- P_START_ORGANIZATION_ID
argument2 => '03', -- P_SELECT_CRITERIA
argument3 => NULL, -- P_PROJECT_FLAG
argument4 => NULL, -- P_PROJECT_ID
argument5 => NULL, -- P_ASSIGNMENT_ID
argument6 => NULL, -- P_ORGANIZATION_FLAG
argument7 => NULL, -- P_ORGANIZATION_ID
argument8 => 1, -- P_START_ORGANIZATION_FLAG
argument9 => p_starting_organization_id); -- P_START_ORGANIZATION_ID
argument2 => '02', -- P_SELECT_CRITERIA
argument3 => NULL, -- P_PROJECT_FLAG
argument4 => NULL, -- P_PROJECT_ID
argument5 => NULL, -- P_ASSIGNMENT_ID
argument6 => 1, -- P_ORGANIZATION_FLAG
argument7 => l_organization_id, -- P_ORGANIZATION_ID
argument8 => NULL, -- P_START_ORGANIZATION_FLAG
argument9 => NULL); -- P_START_ORGANIZATION_ID
SELECT resource_assignment_id
,pa_resource_assignments_s.nextval
,parent_assignment_id
,task_id
,task_id
--Start of Changes for bug 3354518
,NULL
,NULL
,project_assignment_id
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
--End of Changes for bug 3354518
,resource_list_member_id -- bug 3615617 TARGET_RLM_ID
FROM pa_resource_assignments
WHERE budget_version_id = p_source_plan_version_id;
SELECT pra.resource_assignment_id
,pa_resource_assignments_s.NEXTVAL
,pra.parent_assignment_id
,pra.task_id
,pra.task_id
--Start of Changes for bug 3354518
,NULL
,NULL
,pra.project_assignment_id
,pra.planning_start_date
,pra.planning_end_date
,pra.schedule_start_date
,pra.schedule_end_date
--End of Changes for bug 3354518
,resource_list_member_id -- bug 3615617 TARGET_RLM_ID
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = p_source_plan_version_id;
SELECT pra.resource_assignment_id -- SOURCE_RES_ASSIGNMENT_ID
,pa_resource_assignments_s.nextval -- TARGET_RES_ASSIGNMENT_ID
,pra.parent_assignment_id -- PARENT_ASSIGNMENT_ID
,pra.task_id -- SOURCE_TASK_ID
,target_tasks.task_id -- TARGET_TASK_ID
,NULL -- SOURCE ELEMENT_VERSION_ID
,NULL -- TARGET ELEMENT_VERSION_ID
,-1 -- PROJECT_ASSIGNMENT_ID
,pra.planning_start_date + l_shift_days -- PLANNING_START_DATE
,pra.planning_end_date + l_shift_days -- PLANNING_END_DATE
/* It is assumed that this api and this cursor would not be called for TA/WP flow!
So, the schedule dates are stamped as planning start dates itself */
,pra.planning_start_date + l_shift_days -- SCHEDULE_START_DATE
,pra.planning_end_date + l_shift_days -- SCHEDULE_END_DATE
,target_rlm.resource_list_member_id -- bug 3615617 TARGET_RLM_ID
FROM pa_resource_assignments pra
,pa_tasks source_tasks
,pa_tasks target_tasks
,pa_resource_list_members source_rlm -- bug 3615617
,pa_resource_list_members target_rlm -- bug 3615617
WHERE pra.budget_version_id = p_source_plan_version_id
AND pra.project_id = p_source_project_id --bug#2708524
AND source_tasks.task_id = pra.task_id
AND source_tasks.task_number = target_tasks.task_number
AND target_tasks.project_id = l_target_project_id
AND source_rlm.resource_list_member_id = pra.resource_list_member_id -- bug 3615617
AND target_rlm.resource_list_id = source_rlm.resource_list_id -- bug 3615617
AND target_rlm.alias = source_rlm.alias -- bug 3615617
AND target_rlm.object_type = source_rlm.object_type -- bug 3615617
AND target_rlm.object_id = Decode(target_rlm.object_type, -- bug 3615617
'RESOURCE_LIST', target_rlm.resource_list_id,
'PROJECT', l_target_project_id)
AND pra.task_id <> 0; -- bug 3615617 this is redundant but put for clarity
SELECT resource_assignment_id -- SOURCE_RES_ASSIGNMENT_ID
,pa_resource_assignments_s.nextval -- TARGET_RES_ASSIGNMENT_ID
,NULL -- PARENT_ASSIGNMENT_ID
,0 -- SOURCE_TASK_ID
,0 -- TARGET_TASK_ID
--Start of Changes for bug 3354518
,NULL -- SOURCE ELEMENT_VERSION_ID
,NULL -- TARGET ELEMENT_VERSION_ID
,-1 -- PROJECT_ASSIGNMENT_ID
,planning_start_date + l_shift_days -- PLANNING_START_DATE
,planning_end_date + l_shift_days -- PLANNING_END_DATE
/* It is assumed that this api and this cursor would not be called for TA/WP flow!
So, the schedule dates are stamped as planning start dates itself */
,planning_start_date + l_shift_days -- SCHEDULE_START_DATE
,planning_end_date + l_shift_days -- SCHEDULE_END_DATE
--End of Changes for bug 3354518
,target_rlm.resource_list_member_id -- bug 3615617 TARGET_RLM_ID
FROM pa_resource_assignments pra
,pa_resource_list_members source_rlm -- bug 3615617
,pa_resource_list_members target_rlm -- bug 3615617
WHERE pra.budget_version_id = p_source_plan_version_id
AND pra.task_id = 0
AND source_rlm.resource_list_member_id = pra.resource_list_member_id -- bug 3615617
AND target_rlm.resource_list_id = source_rlm.resource_list_id -- bug 3615617
AND target_rlm.alias = source_rlm.alias -- bug 3615617
AND target_rlm.object_type = source_rlm.object_type -- bug 3615617
AND target_rlm.object_id = Decode(target_rlm.object_type, -- bug 3615617
'RESOURCE_LIST', target_rlm.resource_list_id,
'PROJECT', l_target_project_id) ;
SELECT project_id
INTO l_source_project_id
FROM pa_budget_versions
WHERE budget_version_id = p_source_plan_version_id;
DELETE FROM pa_fp_ra_map_tmp;
INSERT INTO pa_fp_ra_map_tmp
(source_res_assignment_id
,target_res_assignment_id
,resource_assignment_id --parent of source res_assignment_id
,source_task_id
,target_task_id
--Added for bug 3354518
,system_reference1
,system_reference2
,system_reference3
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
,system_reference4) -- Bug 3615617
VALUES ( l_ra_map_tmp_tbl1(i)
,l_ra_map_tmp_tbl2(i)
,l_ra_map_tmp_tbl3(i)
,l_ra_map_tmp_tbl4(i)
,l_ra_map_tmp_tbl5(i)
--Added for bug 3354518
,l_ra_map_tmp_tbl6(i)
,l_ra_map_tmp_tbl7(i)
,l_ra_map_tmp_tbl8(i)
,l_ra_map_tmp_tbl9(i)
,l_ra_map_tmp_tbl10(i)
,l_ra_map_tmp_tbl11(i)
,l_ra_map_tmp_tbl12(i)
,l_ra_map_tmp_tbl13(i)); -- Bug 3615617
pa_debug.g_err_stage := 'Selecting project start date';
SELECT p.start_date
INTO l_start_date
FROM pa_projects p
WHERE p.project_id = l_source_project_id;
pa_debug.g_err_stage := 'Selecting task mininum start date';
SELECt min(t.start_date)
INTO l_start_date
FROM pa_tasks t
WHERE t.project_id = l_source_project_id;
pa_debug.g_err_stage := 'Selecting budget lines minimum start date';
SELECT min(bl.start_date)
INTO l_start_Date
FROM pa_budget_lines bl
WHERE bl.budget_version_id = p_source_plan_version_id;
INSERT INTO pa_fp_ra_map_tmp
(source_res_assignment_id
,target_res_assignment_id
,resource_assignment_id --parent of source res_assignment_id
,source_task_id
,target_task_id
--Added for bug 3354518
,system_reference1
,system_reference2
,system_reference3
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
-- Added for bug 3615617
,system_reference4
)
VALUES (l_ra_map_tmp_tbl1(i)
,l_ra_map_tmp_tbl2(i)
,l_ra_map_tmp_tbl3(i)
,l_ra_map_tmp_tbl4(i)
,l_ra_map_tmp_tbl5(i)
--Added for bug 3354518
,l_ra_map_tmp_tbl6(i)
,l_ra_map_tmp_tbl7(i)
,l_ra_map_tmp_tbl8(i)
,l_ra_map_tmp_tbl9(i)
,l_ra_map_tmp_tbl10(i)
,l_ra_map_tmp_tbl11(i)
,l_ra_map_tmp_tbl12(i)
-- Added for bug 3615617
,l_ra_map_tmp_tbl13(i));
INSERT INTO pa_fp_ra_map_tmp
(source_res_assignment_id
,target_res_assignment_id
,resource_assignment_id --parent of source res_assignment_id
,source_task_id
,target_task_id
--Added for bug 3354518
,system_reference1
,system_reference2
,system_reference3
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
-- Added for bug 3615617
,system_reference4
)
VALUES (l_ra_map_tmp_tbl1(i)
,l_ra_map_tmp_tbl2(i)
,l_ra_map_tmp_tbl3(i)
,l_ra_map_tmp_tbl4(i)
,l_ra_map_tmp_tbl5(i)
--Added for bug 3354518
,l_ra_map_tmp_tbl6(i)
,l_ra_map_tmp_tbl7(i)
,l_ra_map_tmp_tbl8(i)
,l_ra_map_tmp_tbl9(i)
,l_ra_map_tmp_tbl10(i)
,l_ra_map_tmp_tbl11(i)
,l_ra_map_tmp_tbl12(i)
-- Added for bug 3615617
,l_ra_map_tmp_tbl13(i));
SELECT DECODE(fin_plan_preference_code
,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY ,'Y'
,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME , 'Y','N') --cost_flag
,DECODE(fin_plan_preference_code
,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY ,'Y'
,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ,'Y','N')--revenue_flag
,project_id
INTO l_cost_flag
,l_revenue_flag
,l_target_project_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_target_plan_version_id;
INSERT INTO PA_RESOURCE_ASSIGNMENTS(
resource_assignment_id
,budget_version_id
,project_id
,task_id
,resource_list_member_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,unit_of_measure
,track_as_labor_flag
,total_plan_revenue
,total_plan_raw_cost
,total_plan_burdened_cost
,total_plan_quantity
,resource_assignment_type
,total_project_raw_cost
,total_project_burdened_cost
,total_project_revenue
,standard_bill_rate
,average_bill_rate
,average_cost_rate
,project_assignment_id
,plan_error_code
,average_discount_percentage
,total_borrowed_revenue
,total_revenue_adj
,total_lent_resource_cost
,total_cost_adj
,total_unassigned_time_cost
,total_utilization_percent
,total_utilization_hours
,total_utilization_adj
,total_capacity
,total_head_count
,total_head_count_adj
,total_tp_revenue_in
,total_tp_revenue_out
,total_tp_cost_in
,total_tp_cost_out
,parent_assignment_id
,wbs_element_version_id
,rbs_element_id
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
,spread_curve_id
,etc_method_code
,res_type_code
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,fc_res_type_code
,resource_class_code
,organization_id
,job_id
,person_id
,expenditure_type
,expenditure_category
,revenue_category_code
,event_type
,supplier_id
,non_labor_resource
,bom_resource_id
,inventory_item_id
,item_category_id
,record_version_number
,transaction_source_code
,mfc_cost_type_id
,procure_resource_flag
,assignment_description
,incurred_by_res_flag
,rate_job_id
,rate_expenditure_type
,ta_display_flag
,sp_fixed_date
,person_type_code
,rate_based_flag
,use_task_schedule_flag
,rate_exp_func_curr_code
,rate_expenditure_org_id
,incur_by_res_class_code
,incur_by_role_id
,project_role_id
,resource_class_flag
,named_role
,txn_accum_header_id
,scheduled_delay --For Bug 3948128
,CBS_ELEMENT_ID -- bug#16200605
)
SELECT /*+ ORDERED USE_NL(PFRMT,PRA) INDEX(PRA PA_RESOURCE_ASSIGNMENTS_U1)*/ pfrmt.target_res_assignment_id --Bug 2814165
,p_target_plan_version_id
,l_target_project_id
,pfrmt.target_task_id
,pfrmt.system_reference4 -- Bug 3615617 resource_list_member_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,pra.unit_of_measure
,pra.track_as_labor_flag
,DECODE(l_adj_percentage,0,DECODE(l_revenue_flag,'Y',total_plan_revenue,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_plan_raw_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_plan_burdened_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,total_plan_quantity,NULL)
,pra.resource_assignment_type
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_project_raw_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_project_burdened_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_revenue_flag,'Y',total_project_revenue,NULL),NULL)
,standard_bill_rate
,average_bill_rate
,average_cost_rate
,pfrmt.system_reference3 -- Project assignment id of the target (Bug 3354518)
,plan_error_code
,average_discount_percentage
,total_borrowed_revenue
,total_revenue_adj
,total_lent_resource_cost
,total_cost_adj
,total_unassigned_time_cost
,total_utilization_percent
,total_utilization_hours
,total_utilization_adj
,total_capacity
,total_head_count
,total_head_count_adj
,total_tp_revenue_in
,total_tp_revenue_out
,total_tp_cost_in
,total_tp_cost_out
,pfrmt.parent_assignment_id
,pfrmt.system_reference2 -- element version id of the target. (Bug 3354518)
,pra.rbs_element_id
,pfrmt.planning_start_date -- Planning start date of the target (Bug 3354518)
,pfrmt.planning_end_date -- Planning end date of the target (Bug 3354518)
,pfrmt.schedule_start_date
,pfrmt.schedule_end_date
,pra.spread_curve_id
,pra.etc_method_code
,pra.res_type_code
,pra.attribute_category
,pra.attribute1
,pra.attribute2
,pra.attribute3
,pra.attribute4
,pra.attribute5
,pra.attribute6
,pra.attribute7
,pra.attribute8
,pra.attribute9
,pra.attribute10
,pra.attribute11
,pra.attribute12
,pra.attribute13
,pra.attribute14
,pra.attribute15
,pra.attribute16
,pra.attribute17
,pra.attribute18
,pra.attribute19
,pra.attribute20
,pra.attribute21
,pra.attribute22
,pra.attribute23
,pra.attribute24
,pra.attribute25
,pra.attribute26
,pra.attribute27
,pra.attribute28
,pra.attribute29
,pra.attribute30
,pra.fc_res_type_code
,pra.resource_class_code
,pra.organization_id
,pra.job_id
,pra.person_id
,pra.expenditure_type
,pra.expenditure_category
,pra.revenue_category_code
,pra.event_type
,pra.supplier_id
,pra.non_labor_resource
,pra.bom_resource_id
,pra.inventory_item_id
,pra.item_category_id
,1 -- should be 1 in the target version being created
,decode(p_calling_context, 'CREATE_VERSION', NULL, pra.transaction_source_code)
,pra.mfc_cost_type_id
,pra.procure_resource_flag
,pra.assignment_description
,pra.incurred_by_res_flag
,pra.rate_job_id
,pra.rate_expenditure_type
,pra.ta_display_flag
-- Bug 3820625 sp_fixed_date should also move as per planning_start_date
-- Least and greatest are used to make sure that sp_fixed_date is with in planning start and end dates
,greatest(least(pra.sp_fixed_date + (pfrmt.planning_start_date - pra.planning_start_date),
pfrmt.planning_end_date),
pfrmt.planning_start_date)
,pra.person_type_code
,pra.rate_based_flag
,pra.use_task_schedule_flag
,pra.rate_exp_func_curr_code
,pra.rate_expenditure_org_id
,pra.incur_by_res_class_code
,pra.incur_by_role_id
,pra.project_role_id
,pra.resource_class_flag
,pra.named_role
,pra.txn_accum_header_id
,scheduled_delay --For Bug 3948128
,pra.CBS_ELEMENT_ID -- bug#16200605
FROM PA_FP_RA_MAP_TMP pfrmt --Bug 2814165
,PA_RESOURCE_ASSIGNMENTS pra
WHERE pra.resource_assignment_id = pfrmt.source_res_assignment_id
AND pra.budget_version_id = p_source_plan_version_id ;
SELECT COUNT(*)
INTO l_tmp
FROM PA_FP_RA_MAP_TMP;
SELECT COUNT(*)
INTO l_tmp
FROM pa_rbs_plans_out_tmp;
INSERT INTO PA_RESOURCE_ASSIGNMENTS(
resource_assignment_id
,budget_version_id
,project_id
,task_id
,resource_list_member_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,unit_of_measure
,track_as_labor_flag
,total_plan_revenue
,total_plan_raw_cost
,total_plan_burdened_cost
,total_plan_quantity
,resource_assignment_type
,total_project_raw_cost
,total_project_burdened_cost
,total_project_revenue
,standard_bill_rate
,average_bill_rate
,average_cost_rate
,project_assignment_id
,plan_error_code
,average_discount_percentage
,total_borrowed_revenue
,total_revenue_adj
,total_lent_resource_cost
,total_cost_adj
,total_unassigned_time_cost
,total_utilization_percent
,total_utilization_hours
,total_utilization_adj
,total_capacity
,total_head_count
,total_head_count_adj
,total_tp_revenue_in
,total_tp_revenue_out
,total_tp_cost_in
,total_tp_cost_out
,parent_assignment_id
,wbs_element_version_id
,rbs_element_id
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
,spread_curve_id
,etc_method_code
,res_type_code
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,fc_res_type_code
,resource_class_code
,organization_id
,job_id
,person_id
,expenditure_type
,expenditure_category
,revenue_category_code
,event_type
,supplier_id
,non_labor_resource
,bom_resource_id
,inventory_item_id
,item_category_id
,record_version_number
,transaction_source_code
,mfc_cost_type_id
,procure_resource_flag
,assignment_description
,incurred_by_res_flag
,rate_job_id
,rate_expenditure_type
,ta_display_flag
,sp_fixed_date
,person_type_code
,rate_based_flag
,use_task_schedule_flag
,rate_exp_func_curr_code
,rate_expenditure_org_id
,incur_by_res_class_code
,incur_by_role_id
,project_role_id
,resource_class_flag
,named_role
,txn_accum_header_id
,scheduled_delay --For Bug 3948128
,CBS_ELEMENT_ID -- bug#16200605
)
SELECT /*+ ORDERED USE_NL(PFRMT,PRA,RMAP) INDEX(PRA PA_RESOURCE_ASSIGNMENTS_U1)*/ pfrmt.target_res_assignment_id --Bug 2814165
,p_target_plan_version_id
,l_target_project_id
,pfrmt.target_task_id
,pfrmt.system_reference4 -- Bug 3615617 resource_list_member_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,pra.unit_of_measure
,pra.track_as_labor_flag
,DECODE(l_adj_percentage,0,DECODE(l_revenue_flag,'Y',total_plan_revenue,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_plan_raw_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_plan_burdened_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,total_plan_quantity,NULL)
,pra.resource_assignment_type
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_project_raw_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y',total_project_burdened_cost,NULL),NULL)
,DECODE(l_adj_percentage,0,DECODE(l_revenue_flag,'Y',total_project_revenue,NULL),NULL)
,standard_bill_rate
,average_bill_rate
,average_cost_rate
,pfrmt.system_reference3 -- Project assignment id of the target (Bug 3354518)
,plan_error_code
,average_discount_percentage
,total_borrowed_revenue
,total_revenue_adj
,total_lent_resource_cost
,total_cost_adj
,total_unassigned_time_cost
,total_utilization_percent
,total_utilization_hours
,total_utilization_adj
,total_capacity
,total_head_count
,total_head_count_adj
,total_tp_revenue_in
,total_tp_revenue_out
,total_tp_cost_in
,total_tp_cost_out
,pfrmt.parent_assignment_id
,pfrmt.system_reference2 -- element version id of the target. (Bug 3354518)
,rmap.rbs_element_id
,pfrmt.planning_start_date -- Planning start date of the target (Bug 3354518)
,pfrmt.planning_end_date -- Planning end date of the target (Bug 3354518)
,pfrmt.schedule_start_date
,pfrmt.schedule_end_date
,pra.spread_curve_id
,pra.etc_method_code
,pra.res_type_code
,pra.attribute_category
,pra.attribute1
,pra.attribute2
,pra.attribute3
,pra.attribute4
,pra.attribute5
,pra.attribute6
,pra.attribute7
,pra.attribute8
,pra.attribute9
,pra.attribute10
,pra.attribute11
,pra.attribute12
,pra.attribute13
,pra.attribute14
,pra.attribute15
,pra.attribute16
,pra.attribute17
,pra.attribute18
,pra.attribute19
,pra.attribute20
,pra.attribute21
,pra.attribute22
,pra.attribute23
,pra.attribute24
,pra.attribute25
,pra.attribute26
,pra.attribute27
,pra.attribute28
,pra.attribute29
,pra.attribute30
,pra.fc_res_type_code
,pra.resource_class_code
,pra.organization_id
,pra.job_id
,pra.person_id
,pra.expenditure_type
,pra.expenditure_category
,pra.revenue_category_code
,pra.event_type
,pra.supplier_id
,pra.non_labor_resource
,pra.bom_resource_id
,pra.inventory_item_id
,pra.item_category_id
,1 -- should be 1 in the target version being created
,decode(p_calling_context, 'CREATE_VERSION', NULL, pra.transaction_source_code)
,pra.mfc_cost_type_id
,pra.procure_resource_flag
,pra.assignment_description
,pra.incurred_by_res_flag
,pra.rate_job_id
,pra.rate_expenditure_type
,pra.ta_display_flag
-- Bug 3820625 sp_fixed_date should also move as per planning_start_date
-- Least and greatest are used to make sure that sp_fixed_date is with in planning start and end dates
,greatest(least(pra.sp_fixed_date + (pfrmt.planning_start_date - pra.planning_start_date),
pfrmt.planning_end_date),
pfrmt.planning_start_date)
,pra.person_type_code
,pra.rate_based_flag
,pra.use_task_schedule_flag
,pra.rate_exp_func_curr_code
,pra.rate_expenditure_org_id
,pra.incur_by_res_class_code
,pra.incur_by_role_id
,pra.project_role_id
,pra.resource_class_flag
,pra.named_role
,rmap.txn_accum_header_id
,scheduled_delay --For Bug 3948128
,pra.CBS_ELEMENT_ID -- bug#16200605
FROM PA_FP_RA_MAP_TMP pfrmt --Bug 2814165
,PA_RESOURCE_ASSIGNMENTS pra
,pa_rbs_plans_out_tmp rmap
WHERE pra.resource_assignment_id = pfrmt.source_res_assignment_id
AND pra.budget_version_id = p_source_plan_version_id
AND rmap.source_id = pra.resource_assignment_id;
pa_debug.g_err_stage:='No. of records inserted into PRA '||l_tmp;
SELECT pfb.plan_class_code,nvl(pbv.wp_version_flag,'N'),etc_start_date
FROM pa_fin_plan_types_b pfb,
pa_budget_versions pbv
WHERE pbv.budget_version_id = c_budget_version_id
AND pbv.fin_plan_type_id = pfb.fin_plan_type_id;
SELECT DECODE(fin_plan_preference_code -- l_revenue_flag
,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY ,'Y'
,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ,'Y','N')
,DECODE(fin_plan_preference_code -- l_cost_flag
,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY ,'Y'
,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME , 'Y','N')
INTO l_revenue_flag
,l_cost_flag
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_target_plan_version_id;
pa_debug.g_err_stage:='Inserting budget_lines';
INSERT INTO PA_BUDGET_LINES(
budget_line_id /* FPB2 */
,budget_version_id /* FPB2 */
,resource_assignment_id
,start_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,end_date
,period_name
,quantity
,raw_cost
,burdened_cost
,revenue
,change_reason_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,raw_cost_source
,burdened_cost_source
,quantity_source
,revenue_source
,pm_product_code
,pm_budget_line_reference
,cost_rejection_code
,revenue_rejection_code
,burden_rejection_code
,other_rejection_code
,code_combination_id
,ccid_gen_status_code
,ccid_gen_rej_message
,request_id
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,revenue_adj
,lent_resource_cost
,tp_cost_in
,tp_cost_out
,cost_adj
,unassigned_time_cost
,utilization_percent
,utilization_hours
,utilization_adj
,capacity
,head_count
,head_count_adj
,projfunc_currency_code
,projfunc_cost_rate_type
,projfunc_cost_exchange_rate
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_exchange_rate
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_currency_code
,project_cost_rate_type
,project_cost_exchange_rate
,project_cost_rate_date_type
,project_cost_rate_date
,project_raw_cost
,project_burdened_cost
,project_rev_rate_type
,project_rev_exchange_rate
,project_rev_rate_date_type
,project_rev_rate_date
,project_revenue
,txn_raw_cost
,txn_burdened_cost
,txn_currency_code
,txn_revenue
,bucketing_period_code
,transfer_price_rate
,init_quantity
,init_quantity_source
,init_raw_cost
,init_burdened_cost
,init_revenue
,init_raw_cost_source
,init_burdened_cost_source
,init_revenue_source
,project_init_raw_cost
,project_init_burdened_cost
,project_init_revenue
,txn_init_raw_cost
,txn_init_burdened_cost
,txn_init_revenue
,txn_markup_percent
,txn_markup_percent_override
,txn_discount_percentage
,txn_standard_bill_rate
,txn_standard_cost_rate
,txn_cost_rate_override
,burden_cost_rate
,txn_bill_rate_override
,burden_cost_rate_override
,cost_ind_compiled_set_id
,pc_cur_conv_rejection_code
,pfc_cur_conv_rejection_code
)
SELECT bmt.target_budget_line_id /* FPB2 */
,p_target_plan_version_id /* FPB2 */
,pfrmt.target_res_assignment_id
,pbl.start_date
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,pbl.end_date
,pbl.period_name
,pbl.quantity
,DECODE(l_cost_flag,'Y', raw_cost,NULL)
,DECODE(l_cost_flag,'Y', burdened_cost,NULL)
,DECODE(l_revenue_flag,'Y', revenue,NULL)
,pbl.change_reason_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,DECODE(l_cost_flag,'Y',PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,NULL) --raw_cost_souce
,DECODE(l_cost_flag,'Y',PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,NULL) --burdened_cost_source
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P --quantity_source
,DECODE(l_revenue_flag,'Y',PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_COPY_P,NULL) --revenue source
,pm_product_code
,pm_budget_line_reference
,DECODE(l_cost_flag, 'Y',cost_rejection_code, NULL)
,DECODE(l_revenue_flag, 'Y',revenue_rejection_code, NULL)
,DECODE(l_cost_flag,'Y',burden_rejection_code, NULL)
,other_rejection_code
,code_combination_id
,ccid_gen_status_code
,ccid_gen_rej_message
,fnd_global.conc_request_id
,borrowed_revenue
,tp_revenue_in
,tp_revenue_out
,revenue_adj
,lent_resource_cost
,tp_cost_in
,tp_cost_out
,cost_adj
,unassigned_time_cost
,utilization_percent
,utilization_hours
,utilization_adj
,capacity
,head_count
,head_count_adj
,projfunc_currency_code
,DECODE(l_cost_flag,'Y',projfunc_cost_rate_type,NULL)
,DECODE(l_cost_flag,'Y',projfunc_cost_exchange_rate,NULL)
,DECODE(l_cost_flag,'Y',projfunc_cost_rate_date_type,NULL)
,DECODE(l_cost_flag,'Y',projfunc_cost_rate_date,NULL)
,DECODE(l_revenue_flag,'Y',projfunc_rev_rate_type,NULL)
,DECODE(l_revenue_flag,'Y',projfunc_rev_exchange_rate,NULL)
,DECODE(l_revenue_flag,'Y',projfunc_rev_rate_date_type,NULL)
,DECODE(l_revenue_flag,'Y',projfunc_rev_rate_date,NULL)
,project_currency_code
,DECODE(l_cost_flag,'Y',project_cost_rate_type,NULL)
,DECODE(l_cost_flag,'Y',project_cost_exchange_rate,NULL)
,DECODE(l_cost_flag,'Y',project_cost_rate_date_type,NULL)
,DECODE(l_cost_flag,'Y',project_cost_rate_date,NULL)
,DECODE(l_cost_flag,'Y', project_raw_cost,NULL)
,DECODE(l_cost_flag,'Y', project_burdened_cost,NULL)
,DECODE(l_revenue_flag,'Y',project_rev_rate_type,NULL)
,DECODE(l_revenue_flag,'Y',project_rev_exchange_rate,NULL)
,DECODE(l_revenue_flag,'Y',project_rev_rate_date_type,NULL)
,DECODE(l_revenue_flag,'Y',project_rev_rate_date,NULL)
,DECODE(l_revenue_flag,'Y', project_revenue,NULL)
,DECODE(l_cost_flag,'Y',
decode(GREATEST(pbl.start_date,NVL(l_etc_start_date,pbl.start_date)),pbl.start_date,txn_raw_cost*(1+l_adj_percentage),txn_raw_cost),NULL)
,DECODE(l_cost_flag,'Y',
decode(GREATEST(pbl.start_date,NVL(l_etc_start_date,pbl.start_date)),pbl.start_date,txn_burdened_cost*(1+l_adj_percentage),txn_burdened_cost),NULL)
,txn_currency_code
,DECODE(l_revenue_flag,'Y',
decode(GREATEST(pbl.start_date,NVL(l_etc_start_date,pbl.start_date)),pbl.start_date,txn_revenue*(1+l_adj_percentage),txn_revenue),NULL)
,DECODE(l_period_profiles_same_flag,'Y',bucketing_period_code,NULL)
,transfer_price_rate
,decode(l_copy_actuals_flag,'N',NULL,pbl.init_quantity) --init_quantity
,decode(l_copy_actuals_flag,'N',NULL,pbl.init_quantity_source) --init_quantity_source
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.init_raw_cost),NULL) --init_raw_cost
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.init_burdened_cost),NULL) --init_burdened_cost
,DECODE(l_revenue_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.init_revenue),NULL) --init_revenue
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.init_raw_cost_source),NULL) --init_raw_cost_source
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.init_burdened_cost_source),NULL) --init_burdened_cost_source
,DECODE(l_revenue_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.init_revenue_source),NULL) --init_revenue_source
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.project_init_raw_cost),NULL) --project_init_raw_cost
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.project_init_burdened_cost),NULL) --project_init_burdened_cost
,DECODE(l_revenue_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.project_init_revenue),NULL) --project_init_revenue
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.txn_init_raw_cost),NULL) --txn_init_raw_cost
,DECODE(l_cost_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.txn_init_burdened_cost),NULL) --txn_init_burdened_cost
,DECODE(l_revenue_flag,'Y',decode(l_copy_actuals_flag,'N',NULL,pbl.txn_init_revenue),NULL) --txn_init_revenue
,txn_markup_percent
,txn_markup_percent_override
,txn_discount_percentage
,Decode(l_revenue_flag,'Y',txn_standard_bill_rate,null) --txn_standard_bill_rate
,Decode(l_cost_flag,'Y',txn_standard_cost_rate,null) --txn_standard_cost_rate
,Decode(l_cost_flag,'Y',txn_cost_rate_override,null) --txn_cost_rate_override
,Decode(l_cost_flag,'Y',burden_cost_rate,null) --burden_cost_rate
,Decode(l_revenue_flag,'Y',txn_bill_rate_override,null) --txn_bill_rate_override
,Decode(l_cost_flag,'Y',burden_cost_rate_override,null) --burden_cost_rate_override
,cost_ind_compiled_set_id
,Decode(l_adj_percentage,0,pc_cur_conv_rejection_code,null)
,Decode(l_adj_percentage,0,pfc_cur_conv_rejection_code,null)
FROM PA_BUDGET_LINES pbl
,PA_FP_RA_MAP_TMP pfrmt
,pa_fp_bl_map_tmp bmt /* FPB2 */
WHERE pbl.resource_assignment_id = pfrmt.source_res_assignment_id
AND bmt.source_budget_line_id = pbl.budget_line_id /* FPB2 */
AND pbl.budget_version_id = p_source_plan_version_id;