[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT record_version_number
FROM PA_PROJ_FP_OPTIONS
WHERE fin_plan_version_id = p_source_plan_version_id
FOR UPDATE NOWAIT;
SELECT record_version_number
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_source_plan_version_id
FOR UPDATE NOWAIT;
SELECT record_version_number
FROM PA_PROJ_FP_OPTIONS
WHERE fin_plan_version_id = p_target_plan_version_id
FOR UPDATE NOWAIT;
SELECT record_version_number
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_target_plan_version_id
FOR UPDATE NOWAIT;
UPDATE PA_PROJ_FP_OPTIONS
SET record_version_number = record_version_number+1
WHERE fin_plan_version_id=p_target_plan_version_id;
UPDATE PA_BUDGET_VERSIONS
SET record_version_number = record_version_number+1
WHERE budget_version_id = p_target_plan_version_id;
plan and then will call copy_version and delete version helper apis.
4/16/2004 Raja FP M Phase II Copy Plan does not copy 'rate schedules',
and 'Generation Options' sub tab data.
========================================================================*/
PROCEDURE Copy_Plan(
p_source_plan_version_id IN NUMBER
,p_target_plan_version_id IN NUMBER
,p_adj_percentage IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_adj_percentage NUMBER;
SELECT proj_fp_options_id
,project_id
,fin_plan_type_id
,plan_in_multi_curr_flag
,projfunc_cost_rate_type
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_cost_rate_type
,project_cost_rate_date_type
,project_cost_rate_date
,project_rev_rate_type
,project_rev_rate_date_type
,project_rev_rate_date
FROM PA_PROJ_FP_OPTIONS
WHERE fin_plan_version_id = p_target_plan_version_id;
SELECT txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,project_cost_exchange_rate
,project_rev_exchange_rate
,projfunc_cost_exchange_rate
,projfunc_rev_exchange_rate
FROM PA_FP_TXN_CURRENCIES
WHERE fin_plan_version_id = p_target_plan_version_id
AND proj_fp_options_id = target_fp_options_rec.proj_fp_options_id; -- bug 2779637
SELECT fin_plan_preference_code
,plan_in_multi_curr_flag -- Bug#2729191
,nvl(approved_rev_plan_type_flag,'N') /* Bug#3276128 */
INTO l_source_fp_pref_code
,l_source_plan_in_mc_flag -- Bug#2729191
,l_source_appr_rev_plan_flag /* Bug#3276128 */
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_source_plan_version_id;
SELECT fin_plan_preference_code,nvl(approved_rev_plan_type_flag,'N')
INTO l_target_fp_pref_code,l_target_appr_rev_plan_flag
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_target_plan_version_id;
SELECT locked_by_person_id
,project_id
INTO l_locked_by_person_id
,l_project_id
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_target_plan_version_id;
SELECT project_id
INTO l_project_id
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_target_plan_version_id;
and txn currencies. But, delete_version_helper followed by copy_version
override the MC flag and txn currencies for the target version.
Since this being a specific case to copy plan, store the required data
locally and update the target version once copy_version is complete */
IF ((target_fp_options_rec.plan_in_multi_curr_flag = 'Y' AND l_source_plan_in_mc_flag = 'N') OR
(l_source_plan_in_mc_flag = 'Y' and l_target_appr_rev_plan_flag = 'Y' )) -- added for 3156057
THEN
OPEN target_txn_currencies_cur;
pa_debug.g_err_stage:='Calling the delete version api';
PA_FIN_PLAN_PUB.DELETE_VERSION_HELPER(
p_budget_version_id => p_target_plan_version_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
DELETE FROM pa_fp_txn_currencies
WHERE fin_plan_version_id = p_target_plan_version_id
AND proj_fp_options_id = target_fp_options_rec.proj_fp_options_id; -- bug 2779637
UPDATE PA_PROJ_FP_OPTIONS
SET plan_in_multi_curr_flag = target_fp_options_rec.plan_in_multi_curr_flag
,projfunc_cost_rate_type = target_fp_options_rec.projfunc_cost_rate_type
,projfunc_cost_rate_date_type = target_fp_options_rec.projfunc_cost_rate_date_type
,projfunc_cost_rate_date = target_fp_options_rec.projfunc_cost_rate_date
,projfunc_rev_rate_type = target_fp_options_rec.projfunc_rev_rate_type
,projfunc_rev_rate_date_type = target_fp_options_rec.projfunc_rev_rate_date_type
,projfunc_rev_rate_date = target_fp_options_rec.projfunc_rev_rate_date
,project_cost_rate_type = target_fp_options_rec.project_cost_rate_type
,project_cost_rate_date_type = target_fp_options_rec.project_cost_rate_date_type
,project_cost_rate_date = target_fp_options_rec.project_cost_rate_date
,project_rev_rate_type = target_fp_options_rec.project_rev_rate_type
,project_rev_rate_date_type = target_fp_options_rec.project_rev_rate_date_type
,project_rev_rate_date = target_fp_options_rec.project_rev_rate_date
WHERE fin_plan_version_id = p_target_plan_version_id;
pa_debug.g_err_stage:='Inserting the txn currencies of the target version
present earlier to copy version';
INSERT INTO PA_FP_TXN_CURRENCIES (
fp_txn_currency_id
,proj_fp_options_id
,project_id
,fin_plan_type_id
,fin_plan_version_id
,txn_currency_code
,default_rev_curr_flag
,default_cost_curr_flag
,default_all_curr_flag
,project_currency_flag
,projfunc_currency_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,project_cost_exchange_rate
,project_rev_exchange_rate
,projfunc_cost_exchange_rate
,projfunc_rev_exchange_rate )
SELECT
pa_fp_txn_currencies_s.NEXTVAL
,target_fp_options_rec.proj_fp_options_id
,target_fp_options_rec.project_id
,target_fp_options_rec.fin_plan_type_id
,p_target_plan_version_id
,l_txn_currency_code_tbl(i)
,l_default_rev_curr_flag_tbl(i)
,l_default_cost_curr_flag_tbl(i)
,l_default_all_curr_flag_tbl(i)
,l_project_currency_flag_tbl(i)
,l_projfunc_currency_flag_tbl(i)
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,l_pc_cost_exchange_rate_tbl(i)
,l_pc_rev_exchange_rate_tbl(i)
,l_pfc_cost_exchange_rate_tbl(i)
,l_pfc_rev_exchange_rate_tbl(i)
FROM DUAL;
SELECT DECODE(fin_plan_preference_code
,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, 'A'
,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, 'R'
,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, 'C')
INTO l_amount_type
FROM PA_PROJ_FP_OPTIONS
WHERE fin_plan_version_id = p_fin_plan_version_id;
SELECT record_version_number
FROM PA_PROJ_FP_OPTIONS
WHERE fin_plan_version_id = p_plan_version_id
FOR UPDATE NOWAIT;
SELECT record_version_number
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = p_plan_version_id
FOR UPDATE NOWAIT;
UPDATE PA_PROJ_FP_OPTIONS
SET record_version_number = record_version_number+1
WHERE fin_plan_version_id=p_plan_version_id;
UPDATE PA_BUDGET_VERSIONS
SET record_version_number = record_version_number+1
WHERE budget_version_id = p_plan_version_id ;
- INSERT INTO PA_BUDGET_VERSIONS (
- UPDATE pa_budget_versions
29-JAN-2004 sgoteti Bug 3354518: Added the parameter
p_struct_elem_version_id
16-APR-2004 rravipat Bug 3354518 FP M Phase II Development
When copy_budget_version is called during copy plan, amount
generation related columnslike last_amt_gen_date would be
updated as null. This is because none of the init columns are
copied from source version to target.
10-Jun-05 Bug 4337221: dbora
if the calling context is workplan, then derive the adjustment percentage
always as 0, so that the version level rolled up quantity and cost amounts get
copied from the source version, as it is to the target version.
--Bug 4290043.Included parameter to indicate whether to copy actual info or not.
=========================================================================*/
PROCEDURE Copy_Budget_Version(
p_source_project_id IN NUMBER
,p_target_project_id IN NUMBER
,p_source_version_id IN NUMBER
,p_copy_mode IN VARCHAR2
,p_adj_percentage IN NUMBER
,p_calling_module IN VARCHAR2
,p_shift_days IN NUMBER
,p_copy_actuals_flag IN VARCHAR2
,px_target_version_id IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_struct_elem_version_id IN pa_budget_versions.budget_version_id%TYPE --Bug 3354518
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
AS
-- Variables to be used for debugging purpose
l_msg_count NUMBER := 0;
SELECT resource_list_id /* Added for bug# 2757847 */
,labor_quantity /* Added for the bug #2645579. */
,raw_cost
,burdened_cost
,revenue
,pm_product_code
,pm_budget_reference
,wf_status_code
,adw_notify_flag
,NULL --prc_generated_flag --Bug 5099353
,plan_run_date
,plan_processing_code
,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_cost_in
,total_tp_cost_out
,total_tp_revenue_in
,total_tp_revenue_out
,total_project_raw_cost
,total_project_burdened_cost
,total_project_revenue
,period_profile_id /* Added for #2587671 */
,object_type_code
,object_id
-- ,primary_cost_forecast_flag FP M Phase II Dev changes this column should not be updated
-- ,primary_rev_forecast_flag FP M Phase II Dev changes this column should not be updated
-- ,rev_partially_impl_flag FP M Phase II Dev changes this column should not be updated
,equipment_quantity
,pji_summarized_flag
,wp_version_flag
,current_planning_period
,period_mask_id
,last_amt_gen_date
,actual_amts_thru_period
,project_structure_version_id
,etc_start_date --Bug 3927244
FROM pa_budget_versions
WHERE budget_version_id = p_source_version_id;
SELECT pfb.plan_class_code,pbv.project_id
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 NVL(p_target_project_id,project_id)
,NVL(p_source_project_id,project_id)
,version_name
,fin_plan_type_id
,version_type
,ci_id
,current_planning_period
INTO l_target_project_id
,l_source_project_id
,l_version_name
,l_fin_plan_type_id
,l_version_type
,l_ci_id
,l_source_cur_planning_period
FROM pa_budget_versions
WHERE budget_version_id = p_source_version_id;
SELECT NVL(MAX(version_number),0)
INTO l_max_version
FROM pa_budget_versions
WHERE project_id = l_project_id
AND fin_plan_type_id = l_fin_plan_type_id
AND budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,
PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED); */
an update would be done to the pa_budget_versions directly.
FND_MESSAGE.SET_NAME('PA','PA_FP_COPY_MESSAGE');
SELECT NVL(MAX(version_number),0)
INTO l_max_version
FROM pa_budget_versions
WHERE project_id = l_project_id
AND fin_plan_type_id = l_fin_plan_type_id
AND budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_BASELINED;
SELECT employee_id
INTO l_baselined_by_person_id
FROM fnd_user
where user_id = FND_GLOBAL.USER_ID;
SELECT pa_budget_versions_s.NEXTVAL
INTO px_target_version_id
FROM DUAL;
Select 'Y'
Into l_exists
From pa_budget_versions a
Where project_structure_version_id = l_wbs_struct_version_id
And wp_version_flag = 'Y'
And exists (select 'x' from pa_budget_versions b where b.budget_version_id = p_source_version_id and b.wp_version_flag = 'Y') ;
INSERT INTO PA_BUDGET_VERSIONS (
budget_version_id
,project_id
,budget_type_code
,version_number
,budget_status_code
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,current_flag
,original_flag
,current_original_flag
,resource_accumulated_flag
,resource_list_id
,version_name
,budget_entry_method_code
,baselined_by_person_id
,baselined_date
,change_reason_code
,labor_quantity
,labor_unit_of_measure
,raw_cost
,burdened_cost
,revenue
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,first_budget_period
,pm_product_code
,pm_budget_reference
,wf_status_code
,adw_notify_flag
,prc_generated_flag
,plan_run_date
,plan_processing_code
,period_profile_id
,fin_plan_type_id
,parent_plan_version_id
,project_structure_version_id
,current_working_flag
,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
,version_type
,total_tp_cost_in
,total_tp_cost_out
,total_tp_revenue_in
,total_tp_revenue_out
,record_version_number
,request_id
,total_project_raw_cost
,total_project_burdened_cost
,total_project_revenue
,locked_by_person_id
,approved_cost_plan_type_flag
,approved_rev_plan_type_flag
,process_update_wbs_flag
,object_type_code
,object_id
,primary_cost_forecast_flag
,primary_rev_forecast_flag
,rev_partially_impl_flag
,equipment_quantity
,pji_summarized_flag
,wp_version_flag
,current_planning_period
,period_mask_id
,last_amt_gen_date
,actual_amts_thru_period
,ci_id -- Raja FP M 06 JUl 04 bug 3677924
,etc_start_date -- Bug 3763322
)
SELECT px_target_version_id
,l_target_project_id
,pbv.budget_type_code
,l_version_number --local_variable
,l_budget_status_code --local_variable
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,l_current_flag --local_variable
,DECODE(p_copy_mode, PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING ,'N',
DECODE (l_version_number,1,'Y','N')) --original_flag
,DECODE(p_copy_mode, PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING ,'N',
DECODE (l_version_number,1,'Y','N')) --current_original_flag
,'N' --resource_accumulated_flag
,pbv.resource_list_id
,SUBSTR(l_version_name,1,60) --local_variable
,pbv.budget_entry_method_code
,l_baselined_by_person_id --local_variable
,l_baselined_date --local_variable
,pbv.change_reason_code
,pbv.labor_quantity
,pbv.labor_unit_of_measure
,DECODE(l_adj_percentage, 0, pbv.raw_cost, NULL)
,DECODE(l_adj_percentage, 0, pbv.burdened_cost, NULL)
,DECODE(l_adj_percentage, 0, pbv.revenue, NULL)
,pbv.description
,pbv.attribute_category
,pbv.attribute1
,pbv.attribute2
,pbv.attribute3
,pbv.attribute4
,pbv.attribute5
,pbv.attribute6
,pbv.attribute7
,pbv.attribute8
,pbv.attribute9
,pbv.attribute10
,pbv.attribute11
,pbv.attribute12
,pbv.attribute13
,pbv.attribute14
,pbv.attribute15
,pbv.first_budget_period
,pbv.pm_product_code
,pbv.pm_budget_reference
,NULL --Bug 5532326 : wf_status_code is not copied
,pbv.adw_notify_flag
,NULL --pbv.prc_generated_flag --Bug 5099353
,pbv.plan_run_date
,NULL -- bug 3079891, 01-AUG-03, jwhite: replaced pbv.plan_processing_code
,period_profile_id
,pbv.fin_plan_type_id
,pbv.parent_plan_version_id
,nvl(l_wbs_struct_version_id,project_structure_version_id) -- Raja nvl should be removed post april 07
,l_current_working_flag --local_variable
,pbv.total_borrowed_revenue
,pbv.total_revenue_adj
,pbv.total_lent_resource_cost
,pbv.total_cost_adj
,pbv.total_unassigned_time_cost
,pbv.total_utilization_percent
,pbv.total_utilization_hours
,pbv.total_utilization_adj
,pbv.total_capacity
,pbv.total_head_count
,pbv.total_head_count_adj
,pbv.version_type
,pbv.total_tp_cost_in
,pbv.total_tp_cost_out
,pbv.total_tp_revenue_in
,pbv.total_tp_revenue_out
,1 --record_version_number
,NULL -- bug 3079891, 01-AUG-03, jwhite: replaced fnd_global.conc_request_id
,DECODE(l_adj_percentage, 0,
pbv.total_project_raw_cost, NULL)
,DECODE(l_adj_percentage, 0,
pbv.total_project_burdened_cost, NULL)
,DECODE(l_adj_percentage, 0,
pbv.total_project_revenue, NULL)
,NULL --locked_by_person_id
,approved_cost_plan_type_flag
,approved_rev_plan_type_flag
,l_refresh_required_flag
,pbv.object_type_code
,l_target_project_id -- object_id bug 3594111
,pbv.primary_cost_forecast_flag
,pbv.primary_rev_forecast_flag
,pbv.rev_partially_impl_flag
,pbv.equipment_quantity
,'N'--This should always be N as the PJI API will be called later and that API will look at this flag
--Summarization will happen only if this flag has 'N' as value.
,pbv.wp_version_flag
,l_target_cur_planning_period -- 3/30/2004 FP M Phase II Dev Changes
,pbv.period_mask_id
,pbv.last_amt_gen_date --Bug 4228859
,decode(p_copy_actuals_flag,'N',null,pbv.actual_amts_thru_period) -- Bug 3927244
,l_ci_id -- Raja FP M 06 JUl 04 bug 3677924
,decode(p_copy_actuals_flag,'N',null,pbv.etc_start_date) -- Bug 3927244
FROM PA_BUDGET_VERSIONS pbv
WHERE pbv.budget_version_id = p_source_version_id;
ELSE --if target_version_id is passed then we update the version
IF p_calling_module = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FIN_PLAN THEN
--To decide what amounts are to be copied from source to target
--version set local flags using target fin plan preference code
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := 'Get values into local flags';
SELECT DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, 'Y',
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, 'N',
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, 'Y') --cost_flag
,DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, 'N',
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, 'Y',
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, 'Y') --revenue_flag
INTO l_cost_flag
,l_revenue_flag
FROM pa_proj_fp_options
WHERE fin_plan_version_id = px_target_version_id;
UPDATE pa_budget_versions
SET resource_list_id = l_bv_rec.resource_list_id /* Added for bug# 2757847 */
,labor_quantity = l_bv_rec.labor_quantity /* Added for bug# 2645579 */
,raw_cost = DECODE(l_adj_percentage, 0, DECODE(l_cost_flag,'Y',l_bv_rec.raw_cost,NULL), NULL)
,burdened_cost = DECODE(l_adj_percentage, 0, DECODE(l_cost_flag,'Y',l_bv_rec.burdened_cost,NULL), NULL)
,revenue = DECODE(l_adj_percentage, 0, DECODE(l_revenue_flag,'Y',l_bv_rec.revenue,NULL), NULL)
,pm_product_code = l_bv_rec.pm_product_code
,pm_budget_reference = l_bv_rec.pm_budget_reference
-- Bug 5532326. This column should not be copied in copy flow
--,wf_status_code = l_bv_rec.wf_status_code
,adw_notify_flag = l_bv_rec.adw_notify_flag
,prc_generated_flag = NULL --l_bv_rec.prc_generated_flag --Bug 5099353
,plan_run_date = l_bv_rec.plan_run_date
,plan_processing_code = NULL --l_bv_rec.plan_processing_code fix for bug 4463404
,total_borrowed_revenue = l_bv_rec.total_borrowed_revenue
,total_revenue_adj = l_bv_rec.total_revenue_adj
,total_lent_resource_cost = l_bv_rec.total_lent_resource_cost
,total_cost_adj = l_bv_rec.total_cost_adj
,total_unassigned_time_cost = l_bv_rec.total_unassigned_time_cost
,total_utilization_percent = l_bv_rec.total_utilization_percent
,total_utilization_hours = l_bv_rec.total_utilization_hours
,total_utilization_adj = l_bv_rec.total_utilization_adj
,total_capacity = l_bv_rec.total_capacity
,total_head_count = l_bv_rec.total_head_count
,total_head_count_adj = l_bv_rec.total_head_count_adj
,total_tp_cost_in = l_bv_rec.total_tp_cost_in
,total_tp_cost_out = l_bv_rec.total_tp_cost_out
,total_tp_revenue_in = l_bv_rec.total_tp_revenue_in
,total_tp_revenue_out = l_bv_rec.total_tp_revenue_out
,record_version_number = record_version_number + 1
,request_id = NULL --FND_GLOBAL.conc_request_id fix for bug 4463404
,total_project_raw_cost = DECODE(l_adj_percentage, 0, DECODE(l_cost_flag,'Y',l_bv_rec.total_project_raw_cost,NULL), NULL)
,total_project_burdened_cost = DECODE(l_adj_percentage, 0, DECODE(l_cost_flag,'Y',l_bv_rec.total_project_burdened_cost,NULL), NULL)
,total_project_revenue = DECODE(l_adj_percentage, 0, DECODE(l_revenue_flag,'Y',l_bv_rec.total_project_revenue,NULL), NULL)
,object_type_code = l_bv_rec.object_type_code
,object_id = l_bv_rec.object_id
-- FP M Phase II ,primary_cost_forecast_flag = l_bv_rec.primary_cost_forecast_flag
-- FP M Phase II ,primary_rev_forecast_flag = l_bv_rec.primary_rev_forecast_flag
-- FP M Phase II ,rev_partially_impl_flag = l_bv_rec.rev_partially_impl_flag
,equipment_quantity = l_bv_rec.equipment_quantity
,pji_summarized_flag = l_bv_rec.pji_summarized_flag
,wp_version_flag = l_bv_rec.wp_version_flag
,current_planning_period = l_bv_rec.current_planning_period
,period_mask_id = l_bv_rec.period_mask_id
-- Bug 3927244
,last_amt_gen_date = l_bv_rec.last_amt_gen_date --Bug 4228859
,actual_amts_thru_period = decode(p_copy_actuals_flag,'N',null,l_bv_rec.actual_amts_thru_period)
,etc_start_date = decode(p_copy_actuals_flag,'N',null,l_bv_rec.etc_start_date)
-- End: Bug 3927244
,project_structure_version_id = l_bv_rec.project_structure_version_id
WHERE budget_version_id = px_target_version_id;
X_last_update_login => FND_GLOBAL.LOGIN_ID,
X_program_application_id => FND_GLOBAL.PROG_APPL_ID);
initialised as 0. Select should not be fired
Bug 3615617 - FP M IB2 changes - Raja
For workplan context target rlm id would be passed for each source resource assignment
=====================================================================================*/
PROCEDURE create_res_task_maps(
p_context IN VARCHAR2 --Can be WORKPLAN, BUDGET
,p_src_ra_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_src_elem_ver_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_targ_elem_ver_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_targ_proj_assmt_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
,p_targ_rlm_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE -- Bug 3615617
,p_planning_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_planning_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_schedule_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,p_schedule_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
i NUMBER;
DELETE FROM pa_fp_ra_map_tmp;
SELECT wbs_element_version_id
INTO l_src_elem_ver_id
FROM pa_resource_assignments
WHERE resource_assignment_id=p_src_ra_id_tbl(i);
pa_debug.g_err_stage:='About to bulk insert into pa_fp_ra_map_tmp';
INSERT INTO pa_fp_ra_map_tmp
( source_res_assignment_id
,target_res_assignment_id
,source_task_id
,target_task_id
,system_reference1
,system_reference2
,system_reference3
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
,system_reference4 -- Bug 3615617
)
SELECT pra.resource_assignment_id
,pa_resource_assignments_s.nextval
,pra.task_id
,pra.task_id
,NULL
,NULL
,p_targ_proj_assmt_id_tbl(i)
,p_planning_start_date_tbl(i)
,p_planning_end_date_tbl(i)
,l_schedule_start_date_tbl(i)
,l_schedule_end_date_tbl(i)
,p_targ_rlm_id_tbl(i) -- Bug 3615617
FROM pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_src_ra_id_tbl(i);
INSERT INTO pa_fp_ra_map_tmp
( source_res_assignment_id
,target_res_assignment_id
,source_task_id
,target_task_id
,system_reference1
,system_reference2
,system_reference3
,planning_start_date
,planning_end_date
,schedule_start_date
,schedule_end_date
,system_reference4 -- Bug 3615617
)
SELECT pra.resource_assignment_id
,pa_resource_assignments_s.nextval
,pra.task_id
,pelm.proj_element_id
,pra.wbs_element_version_id
,l_targ_elem_ver_id_tbl(i)
,p_targ_proj_assmt_id_tbl(i)
,p_planning_start_date_tbl(i)
,p_planning_end_date_tbl(i)
,l_schedule_start_date_tbl(i)
,l_schedule_end_date_tbl(i)
,p_targ_rlm_id_tbl(i) -- Bug 3615617
FROM pa_resource_assignments pra
,pa_proj_element_versions pelm
WHERE pra.resource_assignment_id = p_src_ra_id_tbl(i)
AND pelm.element_version_id=l_targ_elem_ver_id_tbl(i);
This api will insert resource_assignments for the target_version based
upon the PA_FP_RA_MAP_TABLE which contains both source_res_assignment_id,
its parent and the corresponding target_res_assignment_id.This api will
populate appropriate amounts based upon the target version plan
preference code.If adjustment percentage is not zero then all amounts
will be copied as null and will be populated by the rollup api
--r11.5 FP.M Developement ----------------------------------
--
--08-JAN-04 jwhite - Bug 3362316
-- Rewrote Copy_Resource_Assignments
-- for new FP.M columns.
--
3/28/2004 Raja FP M Phase II Dev Effort Copy Project Impact
If resource list is a project specific resource list, target resource
list member id should be derived using resource alias and target project id
5/13/2004 Raja FP M IB2 changes Bug 3615617
Logic to derive target resource list memer id has been moved to
create_res_task_maps api. Target resource list member id is part
of pa_fp_ra_map_tmp table. System_reference4 column has the value.
--Added parameter p_rbs_map_diff_flag for Bug 3974569. This parameter can be passed as Y if the RBS mapping of
--the target resource assignments is different from that of the source resource assignments.If this is passed as Y then
---->1.copy resource assignments will look at pa_rbs_plans_out_tmp table for rbs_element_id and txn_accum_header_id
---->of target resource assignments and it assumes that source_id in pa_rbs_plans_out_tmp corresponds to the
----> resource_assignment_id in the source budget version.
--Bug 3948128. Included scheduled_delay in the list of columns that would get copied.
--Bug 4200168: Changed the logic so that the copy is not based on pa_fp_ra_map_tmp when
--the API is called for copying resource assignments between budget versions
===========================================================================*/
PROCEDURE Copy_Resource_Assignments(
p_source_plan_version_id IN NUMBER
,p_target_plan_version_id IN NUMBER
,p_adj_percentage IN NUMBER
,p_rbs_map_diff_flag IN VARCHAR2 DEFAULT 'N'
,p_calling_context IN VARCHAR2 DEFAULT NULL -- Bug 4065314
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER :=0;
SELECT DECODE(pfot.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(pfot.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
,pfot.project_id
,pfos.project_id
,nvl(pfos.cost_fin_plan_level_code, nvl(pfos.revenue_fin_plan_level_code,pfos.all_fin_plan_level_code))
,nvl(pfos.cost_resource_list_id, nvl(pfos.revenue_resource_list_id,pfos.all_resource_list_id))
,nvl(rl.control_flag,'N')
INTO l_cost_flag
,l_revenue_flag
,l_target_project_id
,l_source_project_id
,l_fin_plan_level_code
,l_resource_list_id
,l_control_flag
FROM pa_proj_fp_options pfot,--target
pa_proj_fp_options pfos,--source
pa_resource_lists_all_bg rl
WHERE pfot.fin_plan_version_id=p_target_plan_version_id
AND pfos.fin_plan_version_id=p_source_plan_version_id
AND rl.resource_list_id=nvl(pfot.cost_resource_list_id, nvl(pfot.revenue_resource_list_id,pfot.all_resource_list_id));
pa_debug.g_err_stage:='Using the First RA Insert';
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
,resource_rate_based_flag --IPM Arch Enhancements Bug 4865563
,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
--parent assignment id in the target resource assignments contain source resource assignment id
--Bug 4200168
,pra.resource_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.resource_rate_based_flag --IPM Arch Enhacement Bug 4865563
,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 ;
pa_debug.g_err_stage:='Using the Second RA Insert';
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
,resource_rate_based_flag -- IPM Arch Enhacements Bug 4865563
,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
--parent assignment id in the target resource assignments contain source resource assignment id
--Bug 4200168
,pra.resource_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.resource_rate_based_flag --IPM Arch Enhancement Bug 4865563
,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:='Using the Third RA Insert';
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
,resource_rate_based_flag --IPM Arch Enhacements Bug 4865563
,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
pa_resource_assignments_s.nextval
,p_target_plan_version_id
,l_target_project_id
,pra.task_id
,pra.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
,pra.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 in the target resource assignments contain source resource assignment id
--Bug 4200168
,pra.resource_assignment_id
,pra.wbs_element_version_id
,pra.rbs_element_id
,pra.planning_start_date -- Planning start date of the target (Bug 3354518)
,pra.planning_end_date -- Planning end date of the target (Bug 3354518)
,pra.schedule_start_date
,pra.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
,pra.sp_fixed_date
,pra.person_type_code
,pra.rate_based_flag
,pra.resource_rate_based_flag --IPM Arch Enhacement Bug 4865563
,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_RESOURCE_ASSIGNMENTS pra
WHERE pra.budget_version_id = p_source_plan_version_id
AND pra.project_id = l_source_project_id ; -- Bug 4493425
pa_debug.g_err_stage:='Using the Fourth RA Insert';
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
,resource_rate_based_flag --IPM Arch Enhacements Bug 4865563
,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
pa_resource_assignments_s.nextval
,p_target_plan_version_id
,l_target_project_id
,pra.task_id
,prlmt.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
,pra.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 in the target resource assignments contain source resource assignment id
--Bug 4200168
,pra.resource_assignment_id
,pra.wbs_element_version_id
,pra.rbs_element_id
,pra.planning_start_date -- Planning start date of the target (Bug 3354518)
,pra.planning_end_date -- Planning end date of the target (Bug 3354518)
,pra.schedule_start_date
,pra.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
,pra.sp_fixed_date
,pra.person_type_code
,pra.rate_based_flag
,pra.resource_rate_based_flag --IPM Arch Enhancement Bug 4865563
,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_RESOURCE_ASSIGNMENTS pra,
pa_resource_list_members prlms,
pa_resource_list_members prlmt
WHERE pra.budget_version_id = p_source_plan_version_id
AND prlms.resource_list_member_id=pra.resource_list_member_id
AND prlms.resource_list_id=l_resource_list_id
AND prlms.object_id=l_source_project_id
AND prlms.object_type='PROJECT'
AND prlmt.resource_list_id=l_resource_list_id
AND prlmt.object_id=l_target_project_id
AND prlmt.object_type='PROJECT'
AND prlmt.alias=prlms.alias;
pa_debug.g_err_stage:='Using the FIFTH RA Insert';
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
,resource_rate_based_flag --IPM Arch Enhancements Bug 4865563
,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
pa_resource_assignments_s.nextval
,p_target_plan_version_id
,l_target_project_id
,pelm.target_task_id
,prlmt.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
,pra.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 in the target resource assignments contain source resource assignment id
--Bug 4200168
,pra.resource_assignment_id
,pra.wbs_element_version_id
,pra.rbs_element_id
,pra.planning_start_date -- Planning start date of the target (Bug 3354518)
,pra.planning_end_date -- Planning end date of the target (Bug 3354518)
,pra.schedule_start_date
,pra.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
,pra.sp_fixed_date
,pra.person_type_code
,pra.rate_based_flag
,pra.resource_rate_based_flag --IPM Arch Enhancements Bug 4865563
,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_RESOURCE_ASSIGNMENTS pra,
(SELECT TO_NUMBER(attribute15) source_task_id,
proj_element_id target_task_id
FROM pa_proj_elements
WHERE project_id = l_target_project_id
AND object_type = 'PA_TASKS'
UNION ALL
SELECT 0 source_task_id,
0 target_task_id
FROM dual) pelm,
pa_resource_list_members prlms,
pa_resource_list_members prlmt
WHERE pra.budget_version_id = p_source_plan_version_id
AND prlms.resource_list_member_id=pra.resource_list_member_id
AND prlms.resource_list_id=l_resource_list_id
AND prlms.object_id=l_source_project_id
AND prlms.object_type='PROJECT'
AND prlmt.resource_list_id=l_resource_list_id
AND prlmt.object_id=l_target_project_id
AND prlmt.object_type='PROJECT'
AND prlmt.alias=prlms.alias
AND pelm.source_task_id=pra.task_id;
pa_debug.g_err_stage:='Using the Sixth RA Insert';
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
,resource_rate_based_flag --IPM Arch Enhancements Bug 4865563
,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
pa_resource_assignments_s.nextval
,p_target_plan_version_id
,l_target_project_id
,pelm.target_task_id
,pra.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
,pra.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 in the target resource assignments contain source resource assignment id
--Bug 4200168
,pra.resource_assignment_id
,pra.wbs_element_version_id
,pra.rbs_element_id
,pra.planning_start_date -- Planning start date of the target (Bug 3354518)
,pra.planning_end_date -- Planning end date of the target (Bug 3354518)
,pra.schedule_start_date
,pra.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
,pra.sp_fixed_date
,pra.person_type_code
,pra.rate_based_flag
,pra.resource_rate_based_flag --IPM Arch Enhancements Bug 4865563
,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_RESOURCE_ASSIGNMENTS pra,
(SELECT TO_NUMBER(attribute15) source_task_id,
proj_element_id target_task_id
FROM pa_proj_elements
WHERE project_id = l_target_project_id
AND object_type='PA_TASKS'
UNION ALL
SELECT 0 source_task_id,
0 target_task_id
FROM dual) pelm
WHERE pra.budget_version_id= p_source_plan_version_id
AND pelm.source_task_id=pra.task_id;
pa_debug.g_err_stage:='No. of records inserted into PRA '||l_tmp;
select ppa.carrying_out_organization_id
into l_project_org
from pa_projects_all ppa,
pa_budget_versions pbv
where pbv.budget_version_id = p_target_plan_version_id
and pbv.project_id = ppa.project_id;
update pa_resource_assignments
set organization_id=l_project_org
where resource_assignment_id in
(select pra.resource_assignment_id
from pa_resource_assignments pra,
pa_resource_list_members prlm
where pra.budget_version_id=p_target_plan_version_id
and pra.resource_list_member_id=prlm.resource_list_member_id
and pra.job_id = prlm.job_id
and prlm.organization_id is null);
SELECT bl.budget_line_id
,bl.resource_assignment_id
,nvl(bl.quantity,0)
,nvl(bl.txn_raw_cost,0)
,nvl(bl.txn_burdened_cost,0)
,nvl(bl.txn_revenue,0)
,nvl(ra.rate_based_flag,'N') rate_based_flag
,nvl(bl.init_quantity,0)
,bl.txn_currency_code
BULK COLLECT INTO
l_bl_id_tbl
,l_ra_id_tbl
,l_quantity_tbl
,l_txn_raw_cost_tbl
,l_txn_burdened_cost_tbl
,l_txn_revenue_tbl
,l_rate_based_flag_tbl
,l_init_quantity_tbl
,l_txn_currency_code_tbl
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE bl.resource_assignment_id=ra.resource_assignment_id
AND bl.budget_version_id=p_budget_version_id
AND ra.budget_version_id=p_budget_version_id
ORDER BY bl.resource_assignment_id ,bl.quantity NULLS FIRST;
UPDATE pa_budget_lines
SET txn_cost_rate_override = DECODE(cost_rejection_code, NULL,l_raw_cost_override_rate_tbl(kk),txn_cost_rate_override),
burden_cost_rate_override = DECODE(burden_rejection_code, NULL,l_burd_cost_override_rate_tbl(kk),burden_cost_rate_override)
WHERE budget_line_id = l_bl_id_tbl(kk);
UPDATE pa_budget_lines
SET txn_cost_rate_override = DECODE(cost_rejection_code, NULL,l_raw_cost_override_rate_tbl(kk),txn_cost_rate_override),
burden_cost_rate_override = DECODE(burden_rejection_code, NULL,l_burd_cost_override_rate_tbl(kk),burden_cost_rate_override),
txn_revenue = DECODE(revenue_rejection_code, NULL,l_txn_revenue_tbl(kk),txn_revenue),
txn_bill_rate_override = DECODE(revenue_rejection_code, NULL,l_bill_override_rate_tbl(kk),txn_bill_rate_override)
WHERE budget_line_id = l_bl_id_tbl(kk);
UPDATE pa_budget_lines
SET txn_bill_rate_override = DECODE(revenue_rejection_code, NULL,l_bill_override_rate_tbl(kk),txn_bill_rate_override)
WHERE budget_line_id = l_bl_id_tbl(kk);
UPDATE pa_budget_lines
SET quantity = l_quantity_tbl(kk),
txn_raw_cost = DECODE(cost_rejection_code, NULL,l_txn_raw_cost_tbl(kk),txn_raw_cost),
txn_burdened_cost = DECODE(burden_rejection_code, NULL,l_txn_burdened_cost_tbl(kk),txn_burdened_cost),
txn_cost_rate_override = DECODE(cost_rejection_code, NULL,l_raw_cost_override_rate_tbl(kk),txn_cost_rate_override),
burden_cost_rate_override = DECODE(burden_rejection_code, NULL,l_burd_cost_override_rate_tbl(kk),burden_cost_rate_override),
txn_bill_rate_override = DECODE(revenue_rejection_code, NULL,l_bill_override_rate_tbl(kk),txn_bill_rate_override)
WHERE budget_line_id = l_bl_id_tbl(kk);
UPDATE pa_resource_assignments
SET rate_based_flag = 'N'
,unit_of_measure = 'DOLLARS'
WHERE resource_assignment_id=l_non_rb_ra_id_tbl(kk);
UPDATE pa_budget_lines
SET quantity = l_quantity_tbl(kk),
txn_raw_cost = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_txn_raw_cost_tbl(kk),
DECODE(cost_rejection_code,
NULL,l_txn_raw_cost_tbl(kk),
txn_raw_cost)),
txn_burdened_cost = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_txn_burdened_cost_tbl(kk),
DECODE(burden_rejection_code,
NULL,l_txn_burdened_cost_tbl(kk),
txn_burdened_cost)),
txn_cost_rate_override = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_raw_cost_override_rate_tbl(kk),
DECODE(cost_rejection_code,
NULL,l_raw_cost_override_rate_tbl(kk),
txn_cost_rate_override)),
burden_cost_rate_override = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_burd_cost_override_rate_tbl(kk),
DECODE(burden_rejection_code,
NULL,l_burd_cost_override_rate_tbl(kk),
burden_cost_rate_override)),
cost_rejection_code = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',NULL,
cost_rejection_code),
burden_rejection_code = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',NULL,
burden_rejection_code)
WHERE budget_line_id = l_bl_id_tbl(kk);
UPDATE pa_budget_lines
SET quantity = l_quantity_tbl(kk),
txn_revenue = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_txn_revenue_tbl(kk),
DECODE(revenue_rejection_code,
NULL,l_txn_revenue_tbl(kk),
txn_revenue)),
txn_bill_rate_override = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_bill_override_rate_tbl(kk),
DECODE(revenue_rejection_code,
NULL,l_bill_override_rate_tbl(kk),
txn_bill_rate_override)),
revenue_rejection_code = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',NULL,
revenue_rejection_code)
WHERE budget_line_id = l_bl_id_tbl(kk);
UPDATE pa_budget_lines
SET quantity = l_quantity_tbl(kk),
txn_raw_cost = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_txn_raw_cost_tbl(kk),
DECODE(cost_rejection_code,
NULL,l_txn_raw_cost_tbl(kk),
txn_raw_cost)),
txn_burdened_cost = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_txn_burdened_cost_tbl(kk),
DECODE(burden_rejection_code,
NULL,l_txn_burdened_cost_tbl(kk),
txn_burdened_cost)),
txn_revenue = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_txn_revenue_tbl(kk),
DECODE(revenue_rejection_code,
NULL,l_txn_revenue_tbl(kk),
txn_revenue)),
txn_cost_rate_override = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_raw_cost_override_rate_tbl(kk),
DECODE(cost_rejection_code,
NULL,l_raw_cost_override_rate_tbl(kk),
txn_cost_rate_override)),
burden_cost_rate_override = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_burd_cost_override_rate_tbl(kk),
DECODE(burden_rejection_code,
NULL,l_burd_cost_override_rate_tbl(kk),
burden_cost_rate_override)),
txn_bill_rate_override = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',l_bill_override_rate_tbl(kk),
DECODE(revenue_rejection_code,
NULL,l_bill_override_rate_tbl(kk),
txn_bill_rate_override)),
cost_rejection_code = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',NULL,
cost_rejection_code),
burden_rejection_code = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',NULL,
burden_rejection_code),
revenue_rejection_code = DECODE (l_bl_rb_flag_chg_tbl(kk),
'Y',NULL,
revenue_rejection_code)
WHERE budget_line_id = l_bl_id_tbl(kk);
This api inserts budget lines for target using source budget lines. If
the adjustment percentage is zero, this api will copy from source to
target version without modifying any amounts. If adjustment percentage is
non-zero,then we don't copy project and project functional columns as
these need to be converted again and might cause rounding issues
This is an overloaded procedure
-- r11.5 FP.M Developement ----------------------------------
--
-- 08-JAN-04 jwhite - Bug 3362316
-- Rewrote Copy_Budget_Lines
--
--Bug 4290043. Introduced the paramters p_copy_actuals_flag and p_derv_rates_missing_amts_flag.
--These will be passed from copy_version API. p_copy_actuals_flag indicates whether to copy the
--actuals from the source version or not. p_derv_rates_missing_amts_flag indicates whether the
--target version contains missing amounts rates which should be derived after copy
=========================================================================*/
PROCEDURE Copy_Budget_Lines(
p_source_plan_version_id IN NUMBER
,p_target_plan_version_id IN NUMBER
,p_adj_percentage IN NUMBER
,p_copy_actuals_flag IN VARCHAR2
,p_derv_rates_missing_amts_flag IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER :=0;
SELECT pfb.plan_class_code,nvl(pbv.wp_version_flag,'N'),etc_start_date,pbv.version_type,pbv.project_id
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')
,fin_plan_preference_code
INTO l_revenue_flag
,l_cost_flag
,l_target_pref_code
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
,display_quantity --IPM Arch Enhancement Bug 4865563.
,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 pa_budget_lines_s.nextval /* FPB2 */
,p_target_plan_version_id /* FPB2 */
,pra.resource_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
,pbl.display_quantity --IPM Arch Enhancement Bug 4865563.
,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
,pbl.attribute_category
,pbl.attribute1
,pbl.attribute2
,pbl.attribute3
,pbl.attribute4
,pbl.attribute5
,pbl.attribute6
,pbl.attribute7
,pbl.attribute8
,pbl.attribute9
,pbl.attribute10
,pbl.attribute11
,pbl.attribute12
,pbl.attribute13
,pbl.attribute14
,pbl.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
,pbl.pm_product_code
,pbl.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(p_copy_actuals_flag,'N',NULL,pbl.init_quantity) --init_quantity
,decode(p_copy_actuals_flag,'N',NULL,pbl.init_quantity_source) --init_quantity_source
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.init_raw_cost),NULL) --init_raw_cost
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.init_burdened_cost),NULL) --init_burdened_cost
,DECODE(l_revenue_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.init_revenue),NULL) --init_revenue
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.init_raw_cost_source),NULL) --init_raw_cost_source
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.init_burdened_cost_source),NULL) --init_burdened_cost_source
,DECODE(l_revenue_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.init_revenue_source),NULL) --init_revenue_source
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.project_init_raw_cost),NULL) --project_init_raw_cost
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.project_init_burdened_cost),NULL) --project_init_burdened_cost
,DECODE(l_revenue_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.project_init_revenue),NULL) --project_init_revenue
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.txn_init_raw_cost),NULL) --txn_init_raw_cost
,DECODE(l_cost_flag,'Y',decode(p_copy_actuals_flag,'N',NULL,pbl.txn_init_burdened_cost),NULL) --txn_init_burdened_cost
,DECODE(l_revenue_flag,'Y',decode(p_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',DECODE(p_derv_rates_missing_amts_flag,'N',txn_standard_bill_rate,NULL),NULL) --txn_standard_bill_rate
,Decode(l_cost_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',txn_standard_cost_rate,NULL),NULL) --txn_standard_cost_rate
,Decode(l_cost_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',txn_cost_rate_override,NULL),NULL) --txn_cost_rate_override
,Decode(l_cost_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',burden_cost_rate,NULL),NULL) --burden_cost_rate
,Decode(l_revenue_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',txn_bill_rate_override,NULL),NULL) --txn_bill_rate_override
,Decode(l_cost_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',burden_cost_rate_override,NULL),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_resource_assignments pra
WHERE pbl.resource_assignment_id = pra.parent_assignment_id
AND pbl.budget_version_id = p_source_plan_version_id
AND pra.budget_version_id = p_target_plan_version_id
AND pra.project_id = l_target_project_id; -- Bug 4493425.
pa_debug.g_err_stage:='No. of Budget lines inserted '||l_temp;
INSERT INTO pa_fp_bl_map_tmp
(source_budget_line_id,
target_budget_line_id)
SELECT pbls.budget_line_id,
pblt.budget_line_id
FROM pa_budget_lines pblt,
pa_budget_lines pbls,
pa_resource_assignments prat
WHERE pblt.budget_version_id=p_target_plan_version_id
AND prat.budget_version_id=p_target_plan_version_id
AND prat.project_id = l_target_project_id -- Bug 4493425.
AND prat.resource_assignment_id=pblt.resource_assignment_id
AND prat.parent_assignment_id=pbls.resource_assignment_id
AND pblt.start_date=pbls.start_date
AND pblt.txn_currency_code=pbls.txn_currency_code;
pa_debug.g_err_stage:='No. of mrc mappling lines inserted '||l_temp;
SELECT DECODE(fin_plan_preference_code
,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY ,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ,'-99') --copy both cost and revenue
,project_id
INTO l_ignore_amount_type
,l_target_project_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_target_plan_version_id;
INSERT INTO PA_PROJ_PERIODS_DENORM(
budget_version_id
,project_id
,resource_assignment_id
,object_id
,object_type_code
,period_profile_id
,amount_type_code
,amount_subtype_code
,amount_type_id
,amount_subtype_id
,currency_type
,currency_code
,preceding_periods_amount
,succeeding_periods_amount
,prior_period_amount
,period_amount1
,period_amount2
,period_amount3
,period_amount4
,period_amount5
,period_amount6
,period_amount7
,period_amount8
,period_amount9
,period_amount10
,period_amount11
,period_amount12
,period_amount13
,period_amount14
,period_amount15
,period_amount16
,period_amount17
,period_amount18
,period_amount19
,period_amount20
,period_amount21
,period_amount22
,period_amount23
,period_amount24
,period_amount25
,period_amount26
,period_amount27
,period_amount28
,period_amount29
,period_amount30
,period_amount31
,period_amount32
,period_amount33
,period_amount34
,period_amount35
,period_amount36
,period_amount37
,period_amount38
,period_amount39
,period_amount40
,period_amount41
,period_amount42
,period_amount43
,period_amount44
,period_amount45
,period_amount46
,period_amount47
,period_amount48
,period_amount49
,period_amount50
,period_amount51
,period_amount52
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,parent_assignment_id)
SELECT p_target_plan_version_id --budget_version_id
,l_target_project_id --project_id
,pfrmt.target_res_assignment_id --resource_assignment_id
/* Bug# 2677867 - Object_id shoudl always be res assgnmnt id irrespect of FP or ORG_FCST
,DECODE(p_calling_module
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_ORG_FORECAST ,pfrmt.target_res_assignment_id
,PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FIN_PLAN , -1) --object_id
*/
,pfrmt.target_res_assignment_id
,PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT --object_type_code
,period_profile_id
,amount_type_code
,amount_subtype_code
,amount_type_id
,amount_subtype_id
,currency_type
,currency_code
,preceding_periods_amount
,succeeding_periods_amount
,prior_period_amount
,period_amount1
,period_amount2
,period_amount3
,period_amount4
,period_amount5
,period_amount6
,period_amount7
,period_amount8
,period_amount9
,period_amount10
,period_amount11
,period_amount12
,period_amount13
,period_amount14
,period_amount15
,period_amount16
,period_amount17
,period_amount18
,period_amount19
,period_amount20
,period_amount21
,period_amount22
,period_amount23
,period_amount24
,period_amount25
,period_amount26
,period_amount27
,period_amount28
,period_amount29
,period_amount30
,period_amount31
,period_amount32
,period_amount33
,period_amount34
,period_amount35
,period_amount36
,period_amount37
,period_amount38
,period_amount39
,period_amount40
,period_amount41
,period_amount42
,period_amount43
,period_amount44
,period_amount45
,period_amount46
,period_amount47
,period_amount48
,period_amount49
,period_amount50
,period_amount51
,period_amount52
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,pfrmt.parent_assignment_id --parent_assignment_id
FROM PA_PROJ_PERIODS_DENORM pppd
,PA_FP_RA_MAP_TMP pfrmt
WHERE budget_version_id = p_source_plan_version_id
AND pppd.resource_assignment_id = pfrmt.source_res_assignment_id
AND pppd.object_type_code = PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT
AND pppd.amount_type_code <> l_ignore_amount_type;
SELECT 1
FROM pa_project_fundings
WHERE project_id = x_new_project_id;
Reframed the select to use EXISTS instead of DISTINCT
*/
SELECT t.budget_type_code
,t.budget_amount_code
FROM pa_budget_types t
WHERE EXISTS ( SELECT 1
FROM pa_budget_versions v
WHERE v.project_id = x_orig_project_id
AND v.budget_type_code = t.budget_type_code)
ORDER BY t.budget_type_code;
SELECT budget_version_id
INTO x_new_budget_ver_id
FROM pa_budget_versions
WHERE project_id = x_new_project_id
AND budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING
AND budget_type_code = c1_rec.budget_type_code;
SELECT 'Y'
FROM dual
WHERE EXISTS(SELECT NULL
FROM pa_budget_versions
WHERE budget_version_id = p_source_ver_id
AND approved_rev_plan_type_flag = 'Y');
SELECT baseline_funding_flag
FROM pa_projects
WHERE project_id = p_source_project_id;
SELECT template_flag
INTO l_source_template_flag
FROM pa_projects_all
WHERE project_id = p_source_project_id;
SELECT 'Y'
INTO l_funding_exists_flag
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pa_project_fundings
WHERE project_id = p_target_project_id);
SELECT fin_plan_option_level_code
,fin_plan_version_id
,fin_plan_preference_code
,fin_plan_type_id
,plan_in_multi_curr_flag
INTO l_fp_option_level_code
,l_source_version_id
,l_source_fp_preference_code
,l_source_fin_plan_type_id
,l_plan_in_multi_curr_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_proj_fp_options_id_tbl(l_index);
,x_last_update_login => FND_GLOBAL.LOGIN_ID
,x_program_application_id => FND_GLOBAL.PROG_APPL_ID()
,x_program_id => NULL
,x_request_id => NULL
,x_automatically_added_flag => NULL);
DELETE pa_resource_asgn_curr_tmp;
/*Inserting into temp table */
INSERT INTO pa_resource_asgn_curr_tmp
(RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
txn_raw_cost_rate_override,
txn_burden_cost_rate_override,
txn_bill_rate_override)
SELECT
pra.resource_assignment_id,
rac.txn_currency_code,
rac.txn_raw_cost_rate_override,
rac.txn_burden_cost_rate_override,
rac.txn_bill_rate_override
FROM
pa_resource_asgn_curr rac,
pa_resource_assignments pra
WHERE
pra.budget_version_id = l_target_version_id and
rac.budget_version_id = l_source_version_id and
pra.parent_assignment_id = rac.resource_assignment_id;
l_budget_version_ids.delete;
l_src_budget_version_id_tbl.delete;
SELECT current_flag
,record_version_number
INTO l_source_current_flag
,l_source_record_version_num
FROM pa_budget_versions
WHERE budget_version_id = l_source_version_id;
SELECT record_version_number
,version_type
INTO l_target_record_version_num
,l_version_type
FROM pa_budget_versions
WHERE budget_version_id = l_target_version_id;
SELECT pfo.proj_fp_options_id
,pfo.fin_plan_type_id
,pfo.fin_plan_preference_code
FROM pa_proj_fp_options pfo
,pa_fin_plan_types_b fin
WHERE project_id = p_source_project_id
AND fin_plan_option_level_code = c_level_code
AND pfo.fin_plan_type_id = fin.fin_plan_type_id(+)
AND nvl(fin.use_for_workplan_flag,'N')<>'Y';
(with shift days logic) to insert shifted periods data into pa_fp_cpy_period_tmp */
PROCEDURE populate_cpy_periods_tmp(p_budget_version_id PA_BUDGET_LINES.budget_version_id%type,
p_period_type PA_PROJ_FP_OPTIONS.cost_time_phased_code%TYPE,
p_shift_periods number) AS
cursor bl_periods is
SELECT distinct bl.period_name,bl.start_date
FROM pa_budget_lines bl
WHERE budget_version_id = p_budget_version_id;
DELETE FROM pa_fp_cpy_periods_tmp;
INSERT INTO pa_fp_cpy_periods_tmp
(PA_PERIOD_NAME
,GL_PERIOD_NAME
,PERIOD_NAME
,START_DATE
,END_DATE)
VALUES
(decode(p_period_type,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P,i.period_name,'-99')
,decode(p_period_type,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G,i.period_name,'-99')
,l_period_name
,l_start_date
,l_end_date);
This api inserts budget lines for target using source budget lines. If
the shift days are zero, this api will copy from source to target version
without shifting any periods. If shift days are non-zero,then we shift
periods according to the existing businees rules. project and projfunc
currencies amounts copied as NULL and would be populated by
convert_txn_currency api.
This is an overloaded procedure as of now used during copying projects.
21-Sep-04 Raja Bug 3841942
During copy project flow, for non-time phased budgets
start and end date should be same as planning start and
end date of the resource assignment
2) If shift days i/p is not sufficient enough to cause
shift in periods changed the code to behave as if
shift days is zero.
=========================================================================*/
PROCEDURE Copy_Budget_Lines(
p_source_project_id IN NUMBER
,p_target_project_id IN NUMBER
,p_source_plan_version_id IN NUMBER
,p_target_plan_version_id IN NUMBER
,p_shift_days IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER :=0;
pa_debug.g_err_stage:='Inserting into pa_budget_lines';
pa_debug.g_err_stage := 'Selecting project start and completion dates';
SELECT start_date,
completion_date
INTO l_target_proj_start_date,
l_target_proj_completion_date
FROM pa_projects p
WHERE p.project_id = p_target_project_id;
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 = p_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 = p_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_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
,display_quantity --IPM Arch Enhancement Bug 4865563.
,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
-- 3/28/2004 FP M phase II Copy Project Impact
,txn_standard_cost_rate
,txn_cost_rate_override
,cost_ind_compiled_set_id
,txn_standard_bill_rate
,txn_bill_rate_override
,txn_markup_percent
,txn_markup_percent_override
,txn_discount_percentage
,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
,burden_cost_rate
,burden_cost_rate_override
,pc_cur_conv_rejection_code
,pfc_cur_conv_rejection_code
)
SELECT pa_budget_lines_s.nextval /* FPB2 */
,p_target_plan_version_id /* FPB2 */
,pra.resource_assignment_id
,DECODE(l_target_time_phased_code,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N,nvl(l_target_proj_start_date , pbl.start_date + l_shift_days), --Bug 4739375,l_target_proj_start_date.--bug 3841942 l_target_proj_start_date,
pbl.start_date ) -- start_date
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
-- Commented by skkoppul for bug 7238582 and replaced this with the decode statement below
-- ,DECODE(l_target_time_phased_code,
-- PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N, nvl(l_target_proj_completion_date, pbl.end_date + l_shift_days), --Bug 4739375,l_target_proj_completion_date,--bug 3841942 l_target_proj_completion_date,
-- pbl.end_date ) -- end_date
-- Default end date with start date if start date > end date else leave end date as is
,DECODE(SIGN(DECODE(l_target_time_phased_code,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N,
nvl(l_target_proj_start_date, pbl.start_date + l_shift_days), pbl.start_date)
-
DECODE(l_target_time_phased_code,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N,
nvl(l_target_proj_completion_date, pbl.end_date + l_shift_days), pbl.end_date )),
1,
DECODE(l_target_time_phased_code,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N,
nvl(l_target_proj_start_date, pbl.start_date + l_shift_days), pbl.start_date),
DECODE(l_target_time_phased_code,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N,
nvl(l_target_proj_completion_date, pbl.end_date + l_shift_days), pbl.end_date)) -- end_date
,pbl.period_name
,pbl.quantity
,pbl.display_quantity --IPM Arch Enhancement Bug 4865563.
,NULL --raw_cost
,NULL --burdened_cost
,NULL --revenue
,NULL --change_reason_code
,pbl.description
,pbl.attribute_category
,pbl.attribute1
,pbl.attribute2
,pbl.attribute3
,pbl.attribute4
,pbl.attribute5
,pbl.attribute6
,pbl.attribute7
,pbl.attribute8
,pbl.attribute9
,pbl.attribute10
,pbl.attribute11
,pbl.attribute12
,pbl.attribute13
,pbl.attribute14
,pbl.attribute15
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --raw_cost_souce
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --burdened_cost_source
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --quantity_source
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --revenue source
,NULL --pm_product_code
,NULL --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
,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
,pbl.projfunc_currency_code
,pbl.projfunc_cost_rate_type
,pbl.projfunc_cost_exchange_rate
,pbl.projfunc_cost_rate_date_type
,pbl.projfunc_cost_rate_date
,pbl.projfunc_rev_rate_type
,pbl.projfunc_rev_exchange_rate
,pbl.projfunc_rev_rate_date_type
,pbl.projfunc_rev_rate_date
,pbl.project_currency_code
,pbl.project_cost_rate_type
,pbl.project_cost_exchange_rate
,pbl.project_cost_rate_date_type
,pbl.project_cost_rate_date
,NULL --project_raw_cost
,NULL --project_burdened_cost
,pbl.project_rev_rate_type
,pbl.project_rev_exchange_rate
,pbl.project_rev_rate_date_type
,pbl.project_rev_rate_date
,NULL --project_revenue
,txn_raw_cost
,txn_burdened_cost
,txn_currency_code
,txn_revenue
,NULL --bucketing_period_code
-- 3/28/2004 FP M phase II Copy Project Impact
,NULL -- txn_standard_cost_rate
,nvl(txn_cost_rate_override,txn_standard_cost_rate) -- txn_cost_rate_override
,cost_ind_compiled_set_id
,NULL -- txn_standard_bill_rate
,nvl(txn_bill_rate_override,txn_standard_bill_rate) -- txn_bill_rate_override
,NULL -- txn_markup_percent
,nvl(txn_markup_percent_override,txn_markup_percent)-- txn_markup_percent_override
,txn_discount_percentage
,transfer_price_rate
,NULL -- init_quantity
,NULL -- init_quantity_source
,NULL -- init_raw_cost
,NULL -- init_burdened_cost
,NULL -- init_revenue
,NULL -- init_raw_cost_source
,NULL -- init_burdened_cost_source
,NULL -- init_revenue_source
,NULL -- project_init_raw_cost
,NULL -- project_init_burdened_cost
,NULL -- project_init_revenue
,NULL -- txn_init_raw_cost
,NULL -- txn_init_burdened_cost
,NULL -- txn_init_revenue
,NULL -- burden_cost_rate
,nvl(burden_cost_rate_override,burden_cost_rate) -- burden_cost_rate_override
,NULL -- pc_cur_conv_rejection_code
,NULL -- pfc_cur_conv_rejection_code
FROM PA_BUDGET_LINES pbl
,pa_resource_assignments pra
WHERE pbl.resource_assignment_id = pra.parent_assignment_id
AND pbl.budget_version_id = p_source_plan_version_id
AND pra.budget_version_id=p_target_plan_version_id;
UPDATE pa_resource_assignments pra
SET (pra.planning_start_date , pra.planning_end_date , pra.sp_fixed_date)
= (SELECT least(pra.planning_start_date + l_shift_days,
nvl(min(bl.start_date) , pra.planning_start_date + l_shift_days)),
greatest(pra.planning_end_date + l_shift_days,
nvl(max(bl.end_date) , pra.planning_end_date + l_shift_days)),
greatest(least(pra.sp_fixed_date + l_shift_days , pra.planning_end_date + l_shift_days),
pra.planning_start_date + l_shift_days)
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = pra.resource_assignment_id
)
WHERE pra.budget_version_id = p_target_plan_version_id;
/* Bug 5846751: Commented the above update and added a new update to derive the Resource Assignment's
dates similar to that of the Budget Lines start and end dates in the above INSERT statement. */
update pa_resource_assignments pra
set (pra.planning_start_date, pra.planning_end_date,pra.sp_fixed_date)
= ( select nvl(min(bl.start_date),nvl(l_target_proj_start_date, pra.planning_start_date + l_shift_days)),
-- skkoppul - bug 7626463 : commented the line below and added decode statement
-- Default end date with start date if start date > end date else leave end date as is
--nvl(min(bl.end_date),nvl(l_target_proj_completion_date, pra.planning_end_date + l_shift_days)),
DECODE(SIGN(nvl(min(bl.start_date),nvl(l_target_proj_start_date, pra.planning_start_date + l_shift_days))
-
nvl(min(bl.end_date),nvl(l_target_proj_completion_date, pra.planning_end_date + l_shift_days))),
1,
nvl(min(bl.start_date),nvl(l_target_proj_start_date, pra.planning_start_date + l_shift_days)),
nvl(min(bl.end_date),nvl(l_target_proj_completion_date, pra.planning_end_date + l_shift_days))), -- end_date
decode(pra.sp_fixed_date,null,null,nvl(min(bl.start_date),nvl(l_target_proj_start_date, pra.sp_fixed_date + l_shift_days)))
from pa_budget_lines bl
where bl.resource_assignment_id = pra.resource_assignment_id
)
where pra.budget_version_id = p_target_plan_version_id;
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
,display_quantity --IPM Arch Enhancement Bug 4865563
,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
-- 3/28/2004 FP M phase II Copy Project Impact
,txn_standard_cost_rate
,txn_cost_rate_override
,cost_ind_compiled_set_id
,txn_standard_bill_rate
,txn_bill_rate_override
,txn_markup_percent
,txn_markup_percent_override
,txn_discount_percentage
,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
,burden_cost_rate
,burden_cost_rate_override
,pc_cur_conv_rejection_code
,pfc_cur_conv_rejection_code
)
SELECT pa_budget_lines_s.nextval /* FPB2 */
,p_target_plan_version_id /* FPB2 */
,pra.resource_assignment_id
,pptmp.start_date
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,pptmp.end_date
,pptmp.period_name
,pbl.quantity
,pbl.display_quantity --IPM Arch Enhancement Bug 4865563
,NULL --raw_cost
,NULL --burdened_cost
,NULL --revenue
,NULL --change_reason_code
,pbl.description
,pbl.attribute_category
,pbl.attribute1
,pbl.attribute2
,pbl.attribute3
,pbl.attribute4
,pbl.attribute5
,pbl.attribute6
,pbl.attribute7
,pbl.attribute8
,pbl.attribute9
,pbl.attribute10
,pbl.attribute11
,pbl.attribute12
,pbl.attribute13
,pbl.attribute14
,pbl.attribute15
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --raw_cost_souce
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --burdened_cost_source
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --quantity_source
,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M --revenue source
,NULL --pm_product_code
,NULL --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
,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
,pbl.projfunc_currency_code
,pbl.projfunc_cost_rate_type
,pbl.projfunc_cost_exchange_rate
,pbl.projfunc_cost_rate_date_type
,pbl.projfunc_cost_rate_date
,pbl.projfunc_rev_rate_type
,pbl.projfunc_rev_exchange_rate
,pbl.projfunc_rev_rate_date_type
,pbl.projfunc_rev_rate_date
,pbl.project_currency_code
,pbl.project_cost_rate_type
,pbl.project_cost_exchange_rate
,pbl.project_cost_rate_date_type
,pbl.project_cost_rate_date
,NULL --project_raw_cost
,NULL --project_burdened_cost
,pbl.project_rev_rate_type
,pbl.project_rev_exchange_rate
,pbl.project_rev_rate_date_type
,pbl.project_rev_rate_date
,NULL --project_revenue
,txn_raw_cost
,txn_burdened_cost
,txn_currency_code
,txn_revenue
,NULL --bucketing_period_code
,NULL -- txn_standard_cost_rate
,nvl(txn_cost_rate_override,txn_standard_cost_rate) -- txn_cost_rate_override
,cost_ind_compiled_set_id
,NULL -- txn_standard_bill_rate
,nvl(txn_bill_rate_override,txn_standard_bill_rate) -- txn_bill_rate_override
,NULL -- txn_markup_percent
,nvl(txn_markup_percent_override,txn_markup_percent)-- txn_markup_percent_override
,txn_discount_percentage
,transfer_price_rate
,NULL -- init_quantity
,NULL -- init_quantity_source
,NULL -- init_raw_cost
,NULL -- init_burdened_cost
,NULL -- init_revenue
,NULL -- init_raw_cost_source
,NULL -- init_burdened_cost_source
,NULL -- init_revenue_source
,NULL -- project_init_raw_cost
,NULL -- project_init_burdened_cost
,NULL -- project_init_revenue
,NULL -- txn_init_raw_cost
,NULL -- txn_init_burdened_cost
,NULL -- txn_init_revenue
,NULL -- burden_cost_rate
,nvl(burden_cost_rate_override,burden_cost_rate) -- burden_cost_rate_override
,NULL -- pc_cur_conv_rejection_code
,NULL -- pfc_cur_conv_rejection_code
FROM PA_BUDGET_LINES pbl
,pa_resource_assignments pra
,PA_FP_CPY_PERIODS_TMP pptmp /* Bug# 2634726 */
WHERE pra.parent_assignment_id = pbl.resource_assignment_id
AND decode(l_target_time_phased_code,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P, pptmp.pa_period_name,
PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G, pptmp.gl_period_name) = pbl.period_name
AND pbl.budget_version_id = p_source_plan_version_id
AND pra.budget_version_id=p_target_plan_version_id;
update pa_resource_assignments pra
set (pra.planning_start_date, pra.planning_end_date,pra.sp_fixed_date)
= ( select least(pra.planning_start_date+l_shift_days,
nvl(min(bl.start_date),pra.planning_start_date+l_shift_days)),
greatest(pra.planning_end_date+l_shift_days,
nvl(max(bl.end_date),pra.planning_end_date+l_shift_days)),
greatest(least(pra.sp_fixed_date + l_shift_days, pra.planning_end_date+ l_shift_days),
pra.planning_start_date+ l_shift_days)
from pa_budget_lines bl
where bl.resource_assignment_id = pra.resource_assignment_id
)
where pra.budget_version_id = p_target_plan_version_id;
SELECT period_profile_id
FROM pa_proj_period_profiles pp
WHERE pp.project_id = p_source_project_id
AND pp.current_flag = 'Y';
SELECT number_of_periods
,plan_period_type
,period_profile_type
,period_name1 -- profile_start_period
,profile_end_period_name
,period1_start_date -- profile start date
,current_flag
FROM pa_proj_period_profiles
WHERE period_profile_id = c_period_profile_id ;
SELECT b.period_set_name
,DECODE(source_profile_info_rec.plan_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA ,pa_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL ,accounted_period_type) --accounted_period_type
,DECODE(source_profile_info_rec.plan_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA ,pa_period_type,
PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL ,NULL) --pa_period_type
INTO l_period_set_name
,l_accounted_period_type
,l_pa_period_type
FROM pa_projects_all p
-- MOAC changes
-- replaced with pa_implementations_all table.
--,pa_implementations a
,pa_implementations_all a
,gl_sets_of_books b
WHERE p.project_id = p_target_project_id
-- MOAC changes
-- removed the nvl around the org_id.
-- AND NVL(p.Org_Id,-99) = NVL(a.Org_Id,-99)
AND p.Org_Id =a.Org_Id
AND a.set_of_books_id = b.set_of_books_id;
SELECT period_name
,start_date
INTO l_start_period
,l_start_period_start_date
FROM pa_periods
WHERE TRUNC(source_profile_info_rec.period1_start_date + p_shift_days) BETWEEN start_date AND end_date;
SELECT period_name
,start_date
INTO l_start_period
,l_start_period_start_date
FROM gl_period_statuses g
,pa_implementations i
WHERE g.application_id = pa_period_process_pkg.application_id
AND g.set_of_books_id = i.set_of_books_id
AND g.adjustment_period_flag = 'N'
AND TRUNC(source_profile_info_rec.period1_start_date + p_shift_days) BETWEEN start_date AND end_date;
SELECT selv.element_version_id
,telv.element_version_id
FROM pa_proj_element_versions telv
,pa_proj_element_versions selv
,pa_proj_elements spe
,pa_proj_elements tpe
WHERE spe.project_id=p_source_project_id
AND tpe.project_id=p_target_project_id
AND spe.element_number=tpe.element_number
AND tpe.object_type='PA_TASKS'
AND spe.object_type='PA_TASKS'
AND telv.proj_element_id = tpe.proj_element_id
AND selv.proj_element_id =spe.proj_element_id
AND selv.parent_structure_version_id=c_src_struct_ver_id
AND telv.parent_structure_version_id=c_targ_struct_ver_id
AND EXISTS (SELECT task_id
FROM pa_resource_assignments pra
WHERE pra.budget_version_id=c_source_plan_version_id
AND pra.task_id=spe.proj_element_id);
SELECT fin_plan_type_id
INTO l_wp_plan_type_id
FROM pa_fin_plan_types_b
WHERE use_for_workplan_flag='Y';
SELECT proj_fp_options_id
INTO l_src_proj_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id=l_src_budget_version_id;
SELECT plan_in_multi_curr_flag
INTO l_targ_multi_curr_flag
FROM pa_proj_fp_options
WHERE proj_fp_options_id=l_targ_proj_fp_options_id;
l_budget_version_ids.delete;
l_src_budget_version_id_tbl.delete;
PA_FP_COPY_FROM_PKG.Update_Plan_Setup_For_WP_Copy(
p_project_id => p_target_project_id
,p_wp_version_id => l_targ_budget_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage:='Update_Plan_Setup_For_WP_Copy returned error';
pa_debug.g_err_stage:='Update_Plan_Setup_For_WP_Copy returned error';
PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api
( p_source => 'PA_FP_RA_MAP_TMP'
,p_budget_version_id => l_targ_budget_version_id
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
pa_debug.g_err_stage:='PA_PLANNING_TRANSACTION_UTILS.call_update_rep_lines_api returned error';
SELECT distinct pbl.resource_assignment_id resource_assignment_id, pbl.start_date start_date,pra.rate_based_flag
FROM pa_budget_lines pbl, pa_resource_assignments pra
WHERE pbl.budget_version_id = p_source_plan_version_id
AND pra.resource_assignment_id = pbl.resource_assignment_id;
SELECT
budget_line_id
,nvl(DECODE(p_rate_based_flag,'N',DECODE(p_cost_flag,'Y',nvl(raw_cost,0),nvl(revenue,0)),quantity),0) quantity
,nvl(raw_cost,0)
,nvl(burdened_cost,0)
,nvl(revenue,0)
,change_reason_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,nvl(project_raw_cost,0)
,nvl(project_burdened_cost,0)
,nvl(project_revenue,0)
,txn_currency_code
,projfunc_currency_code
,project_currency_code
,end_date
,period_name
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND start_date = p_start_date
AND cost_rejection_code is null
AND burden_rejection_code is null
AND revenue_rejection_code is null
AND other_rejection_code is null
AND pc_cur_conv_rejection_code is null
AND pfc_cur_conv_rejection_code is null
ORDER BY txn_currency_code;
l_budget_line_idTab.delete;
l_quantityTab.delete;
l_raw_costTab.delete;
l_burdened_costTab.delete;
l_revenueTab.delete;
l_change_reason_codeTab.delete;
l_descriptionTab.delete;
l_attribute_categoryTab.delete;
l_attribute1Tab.delete;
l_attribute2Tab.delete;
l_attribute3Tab.delete;
l_attribute4Tab.delete;
l_attribute5Tab.delete;
l_attribute6Tab.delete;
l_attribute7Tab.delete;
l_attribute8Tab.delete;
l_attribute9Tab.delete;
l_attribute10Tab.delete;
l_attribute11Tab.delete;
l_attribute12Tab.delete;
l_attribute13Tab.delete;
l_attribute14Tab.delete;
l_attribute15Tab.delete;
l_project_raw_costTab.delete;
l_project_burdened_costTab.delete;
l_project_revenueTab.delete;
l_txn_currency_codeTab.delete;
l_projfunc_currency_codeTab.delete;
l_project_currency_codeTab.delete;
l_end_dateTab.delete;
l_period_nameTab.delete;
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_AND_REV_SAME , 'Y','N')
,fin_plan_preference_code
INTO l_revenue_flag
,l_cost_flag
,l_targ_pref_code
FROM pa_proj_fp_options
WHERE fin_plan_version_id=p_target_plan_version_id;
SELECT etc_start_date,
version_type
INTO l_etc_start_date,
l_target_version_type
FROM pa_budget_versions
WHERE budget_version_id=p_target_plan_version_id;
SELECT pbv.version_type,
fin.plan_class_code
INTO l_source_version_type,
l_src_plan_class_code
FROM pa_budget_versions pbv,
pa_fin_plan_types_b fin
WHERE pbv.fin_plan_type_id=fin.fin_plan_type_id
AND pbv.budget_version_id = p_source_plan_version_id;
DELETE FROM PA_FP_BL_MAP_TMP;
SELECT pa_budget_lines_s.nextval
INTO l_target_budget_line_id
FROM dual;
INSERT INTO PA_FP_BL_MAP_TMP
( source_budget_line_id
,target_budget_line_id
)
VALUES (l_budget_line_idTab(j),
l_target_budget_line_id
);
INSERT INTO PA_BUDGET_LINES(
budget_line_id
,budget_version_id
,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 l_target_budget_line_id
,p_target_plan_version_id
,pra.resource_assignment_id
,rec_group_source_budget_lines.start_date
,sysdate -- last_update_date
,fnd_global.user_id -- last_updated_by
,sysdate -- creation_date
,fnd_global.user_id -- created_by
,fnd_global.login_id -- last_update_login
,l_end_dateTab(l_ref)
,l_period_nameTab(l_ref)
,l_quantity_tot
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y', l_raw_cost_tot,NULL),NULL) -- raw_cost
,DECODE(l_adj_percentage,0,DECODE(l_cost_flag,'Y', l_burdened_cost_tot,NULL),NULL) -- burdened_cost
,DECODE(l_adj_percentage,0,DECODE(l_revenue_flag,'Y', l_revenue_tot,NULL),NULL) -- revenue
,l_change_reason_codeTab(l_ref) -- change_reason_code
,l_descriptionTab(l_ref)-- description
,l_attribute_categoryTab(l_ref)
,l_attribute1Tab(l_ref)
,l_attribute2Tab(l_ref)
,l_attribute3Tab(l_ref)
,l_attribute4Tab(l_ref)
,l_attribute5Tab(l_ref)
,l_attribute6Tab(l_ref)
,l_attribute7Tab(l_ref)
,l_attribute8Tab(l_ref)
,l_attribute9Tab(l_ref)
,l_attribute10Tab(l_ref)
,l_attribute11Tab(l_ref)
,l_attribute12Tab(l_ref)
,l_attribute13Tab(l_ref)
,l_attribute14Tab(l_ref)
,l_attribute15Tab(l_ref)
,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
,null -- pm_product_code
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null -- head_count_adj
,l_projfunc_currency_codeTab(l_ref)
,DECODE(l_cost_flag,'Y','User',NULL) -- projfunc_cost_rate_type
,DECODE(l_cost_flag,'Y',1,NULL) -- projfunc_cost_exchange_rate
,null -- projfunc_cost_rate_date_type
,null -- projfunc_cost_rate_date
,'User' -- projfunc_rev_rate_type
,1 -- projfunc_rev_exchange_rate
,null -- projfunc_rev_rate_date_type
,null -- projfunc_rev_rate_date
,l_project_currency_codeTab(l_ref)
,DECODE(l_cost_flag,'Y','User',NULL) -- project_cost_rate_type
,DECODE(l_cost_flag,'Y',l_project_cost_exchange_rate,NULL) -- project_cost_exchange_rate
,null -- project_cost_rate_date_type
,null -- project_cost_rate_date
,DECODE(l_adj_percentage,0,
DECODE(l_cost_flag,'Y', l_project_raw_cost_tot,NULL),NULL) --project_raw_cost
,DECODE(l_adj_percentage,0,
DECODE(l_cost_flag,'Y', l_project_burdened_cost_tot,NULL),NULL) -- project_burdened_cost
,'User' -- project_rev_rate_type
,l_proj_rev_ex_rate -- project_rev_exchange_rate
,null -- project_rev_rate_date_type
,null -- project_rev_rate_date
,DECODE(l_adj_percentage,0,
DECODE(l_revenue_flag,'Y', l_project_revenue_tot,NULL),NULL) -- project_revenue
,DECODE(l_cost_flag,'Y',
decode(GREATEST(rec_group_source_budget_lines.start_date,NVL(l_etc_start_date,rec_group_source_budget_lines.start_date)),rec_group_source_budget_lines.start_date
,l_raw_cost_tot*(1+l_adj_percentage),l_raw_cost_tot),NULL) -- txn_raw_cost
,DECODE(l_cost_flag,'Y',
decode(GREATEST(rec_group_source_budget_lines.start_date,NVL(l_etc_start_date,rec_group_source_budget_lines.start_date)),rec_group_source_budget_lines.start_date
,l_burdened_cost_tot*(1+l_adj_percentage),l_burdened_cost_tot),NULL) -- txn_burdened_cost
,l_projfunc_currency_codeTab(l_ref) -- txn_currency_code
,DECODE(l_revenue_flag,'Y',
decode(GREATEST(rec_group_source_budget_lines.start_date,NVL(l_etc_start_date,rec_group_source_budget_lines.start_date)),rec_group_source_budget_lines.start_date
,l_revenue_tot*(1+l_adj_percentage),l_revenue_tot),NULL) -- txn_revenue
,null -- bucketing_period_code
,null -- transfer_price_rate
,NULL --init_quantity
,NULL --init_quantity_source
,NULL --init_raw_cost
,NULL --init_burdened_cost
,NULL --init_revenue
,NULL --init_raw_cost_source
,NULL --init_burdened_cost_source
,NULL --init_revenue_source
,NULL --project_init_raw_cost
,NULL --project_init_burdened_cost
,NULL --project_init_revenue
,NULL --txn_init_raw_cost
,NULL --txn_init_burdened_cost
,NULL --txn_init_revenue
,null -- txn_markup_percent
,null -- txn_markup_percent_override
,null -- txn_discount_percentage
,null -- txn_standard_bill_rate
,null -- txn_standard_cost_rate
,DECODE(l_cost_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',l_txn_cost_rate_override,NULL),NULL) -- txn_cost_rate_override
,null -- burden_cost_rate
,DECODE(l_revenue_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',l_txn_bill_rate_override,NULL),NULL) -- txn_bill_rate_override
,DECODE(l_cost_flag,'Y',DECODE(p_derv_rates_missing_amts_flag,'N',l_burden_cost_rate_override,NULL),NULL) -- burden_cost_rate_override
,null -- cost_ind_compiled_set_id
,null -- pc_cur_conv_rejection_code
,null -- pfc_cur_conv_rejection_code
FROM pa_resource_assignments pra
WHERE rec_group_source_budget_lines.resource_assignment_id = pra.parent_assignment_id
AND pra.budget_version_id=p_target_plan_version_id;
Bug 3725414: In update to pa_proj_fp_options, rbs_version_id column is missing
Bug 4101153: Current Planning period should always get the value from the source version and
not from the workplan plan type option. Removed the update to current planning period
Bug 4337221: dbora- Excluded the quantity and cost amount columns from the update
statement on pa_budget_versions, so that the quantity and cost amount columns gets
copied as it is from the source version retaining the version level rolled up figures.
==============================================================================*/
PROCEDURE Update_Plan_Setup_For_WP_Copy(
p_project_id IN pa_projects_all.project_id%TYPE
,p_wp_version_id IN pa_budget_versions.fin_plan_type_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
--Start of variables used for debugging
l_return_status VARCHAR2(1);
SELECT pfo.proj_fp_options_id
,pfo.track_workplan_costs_flag
,pfo.plan_in_multi_curr_flag
,pfo.rbs_version_id
,pfo.margin_derived_from_code
,pfo.factor_by_code
,pfo.cost_resource_list_id
,pfo.select_cost_res_auto_flag
,pfo.cost_time_phased_code
,pfo.cost_period_mask_id
,pfo.projfunc_cost_rate_type
,pfo.projfunc_cost_rate_date_type
,pfo.projfunc_cost_rate_date
,pfo.project_cost_rate_type
,pfo.project_cost_rate_date_type
,pfo.project_cost_rate_date
,pfo.use_planning_rates_flag
,pfo.res_class_raw_cost_sch_id
,pfo.cost_emp_rate_sch_id
,pfo.cost_job_rate_sch_id
,pfo.cost_non_labor_res_rate_sch_id
,pfo.cost_res_class_rate_sch_id
,pfo.cost_burden_rate_sch_id
FROM pa_proj_fp_options pfo
,pa_fin_plan_types_b fpt
WHERE pfo.project_id = p_project_id
AND pfo.fin_plan_type_id = fpt.fin_plan_type_id
AND fpt.use_for_workplan_flag = 'Y'
AND pfo.fin_plan_option_level_code = 'PLAN_TYPE';
SELECT pfo.proj_fp_options_id
,pfo.rbs_version_id
,pfo.cost_resource_list_id
FROM pa_proj_fp_options pfo
,pa_budget_versions bv
WHERE bv.budget_version_id = p_wp_version_id
AND bv.project_id = pfo.project_id
AND pfo.fin_plan_version_id = bv.budget_version_id;
p_function =>'PA_FP_COPY_FROM_PKG.Update_Plan_Setup_For_WP_Copy'
,p_debug_mode => l_debug_mode );
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
p_value1 => 'PA_FP_COPY_FROM_PKG.Update_Plan_Setup_For_WP_Copy');
UPDATE pa_budget_versions
SET resource_list_id = parent_plan_type_rec.cost_resource_list_id
,period_mask_id = parent_plan_type_rec.cost_period_mask_id
/* Bug 4337221: removed from the update
,raw_cost = 0
,burdened_cost = 0
,total_project_raw_cost = 0
,total_project_burdened_cost = 0
,labor_quantity = 0
,equipment_quantity = 0
*/
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
,record_version_number = record_version_number + 1
WHERE budget_version_id = p_wp_version_id;
UPDATE pa_proj_fp_options
SET track_workplan_costs_flag = parent_plan_type_rec.track_workplan_costs_flag
,plan_in_multi_curr_flag = parent_plan_type_rec.plan_in_multi_curr_flag
,margin_derived_from_code = parent_plan_type_rec.margin_derived_from_code
,factor_by_code = parent_plan_type_rec.factor_by_code
,cost_resource_list_id = parent_plan_type_rec.cost_resource_list_id
,select_cost_res_auto_flag = parent_plan_type_rec.select_cost_res_auto_flag
,cost_time_phased_code = parent_plan_type_rec.cost_time_phased_code
,cost_period_mask_id = parent_plan_type_rec.cost_period_mask_id
,projfunc_cost_rate_type = parent_plan_type_rec.projfunc_cost_rate_type
,projfunc_cost_rate_date_type = parent_plan_type_rec.projfunc_cost_rate_date_type
,projfunc_cost_rate_date = parent_plan_type_rec.projfunc_cost_rate_date
,project_cost_rate_type = parent_plan_type_rec.project_cost_rate_type
,project_cost_rate_date_type = parent_plan_type_rec.project_cost_rate_date_type
,project_cost_rate_date = parent_plan_type_rec.project_cost_rate_date
,use_planning_rates_flag = parent_plan_type_rec.use_planning_rates_flag
,res_class_raw_cost_sch_id = parent_plan_type_rec.res_class_raw_cost_sch_id
,cost_emp_rate_sch_id = parent_plan_type_rec.cost_emp_rate_sch_id
,cost_job_rate_sch_id = parent_plan_type_rec.cost_job_rate_sch_id
,cost_non_labor_res_rate_sch_id = parent_plan_type_rec.cost_non_labor_res_rate_sch_id
,cost_res_class_rate_sch_id = parent_plan_type_rec.cost_res_class_rate_sch_id
,cost_burden_rate_sch_id = parent_plan_type_rec.cost_burden_rate_sch_id
,rbs_version_id = parent_plan_type_rec.rbs_version_id -- Bug 3725414
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE proj_fp_options_id = wp_version_options_rec.proj_fp_options_id;
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,3);
UPDATE pa_resource_assignments
SET resource_list_member_id = l_people_res_class_rlm_id
WHERE budget_version_id = p_wp_version_id
AND resource_class_code = 'PEOPLE'
AND resource_class_flag = 'Y';
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
UPDATE pa_resource_assignments
SET rbs_element_id = l_rbs_element_id_tbl(i)
,txn_accum_header_id = l_txn_accum_header_id_tbl(i)
,record_version_number = record_version_number + 1
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
WHERE budget_version_id = p_wp_version_id
AND resource_assignment_id = l_txn_source_id_tbl(i);
pa_debug.g_err_stage:='Exiting Update_Plan_Setup_For_WP_Copy';
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
,p_procedure_name => 'Update_Plan_Setup_For_WP_Copy');
pa_debug.write('Update_Plan_Setup_For_WP_Copy: ' || g_module_name,pa_debug.g_err_stage,5);
END Update_Plan_Setup_For_WP_Copy;