[Home] [Help]
45: PRAGMA exception_init(Resource_Busy,-00054);
46:
47: CURSOR source_fp_opt_cur IS
48: SELECT record_version_number
49: FROM PA_PROJ_FP_OPTIONS
50: WHERE fin_plan_version_id = p_source_plan_version_id
51: FOR UPDATE NOWAIT;
52:
53: CURSOR source_bdgt_vers_cur IS
57: FOR UPDATE NOWAIT;
58:
59: CURSOR target_fp_opt_cur IS
60: SELECT record_version_number
61: FROM PA_PROJ_FP_OPTIONS
62: WHERE fin_plan_version_id = p_target_plan_version_id
63: FOR UPDATE NOWAIT;
64:
65: CURSOR target_bdgt_vers_cur IS
80: IF P_PA_DEBUG_MODE = 'Y' THEN
81: pa_debug.set_process('PLSQL','LOG',l_debug_mode);
82: END IF;
83: /*
84: * Acquire lock on pa_proj_fp_options and pa_budget_versions so that
85: * no other process would be able to modify these tables and all
86: * underlying child tables
87: */
88:
86: * underlying child tables
87: */
88:
89: IF P_PA_DEBUG_MODE = 'Y' THEN
90: pa_debug.g_err_stage:='Acquiring lock on pa_proj_fp_options';
91: pa_debug.write('Acquire_Locks_For_Copy_Plan: ' || g_module_name,pa_debug.g_err_stage,3);
92: END IF;
93:
94: OPEN source_fp_opt_cur;
105: OPEN target_bdgt_vers_cur;
106:
107: /*
108: * Increment the record_version_number of target version in
109: * pa_budget_versions and pa_proj_fp_options
110: */
111:
112: IF P_PA_DEBUG_MODE = 'Y' THEN
113: pa_debug.g_err_stage:='Incrementing record version number of target version in pa_proj_fp_options';
109: * pa_budget_versions and pa_proj_fp_options
110: */
111:
112: IF P_PA_DEBUG_MODE = 'Y' THEN
113: pa_debug.g_err_stage:='Incrementing record version number of target version in pa_proj_fp_options';
114: pa_debug.write('Acquire_Locks_For_Copy_Plan: ' || g_module_name,pa_debug.g_err_stage,3);
115: END IF;
116:
117: UPDATE PA_PROJ_FP_OPTIONS
113: pa_debug.g_err_stage:='Incrementing record version number of target version in pa_proj_fp_options';
114: pa_debug.write('Acquire_Locks_For_Copy_Plan: ' || g_module_name,pa_debug.g_err_stage,3);
115: END IF;
116:
117: UPDATE PA_PROJ_FP_OPTIONS
118: SET record_version_number = record_version_number+1
119: WHERE fin_plan_version_id=p_target_plan_version_id;
120:
121: IF P_PA_DEBUG_MODE = 'Y' THEN
248: l_msg_index_out NUMBER;
249: l_return_status VARCHAR2(2000);
250: l_debug_mode VARCHAR2(30);
251:
252: l_source_fp_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE ;
253: l_target_fp_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE ;
254: --l_locked_by_person_id pa_budget_versions.locked_by_person_id%TYPE;
255: l_project_id pa_projects_all.project_id%TYPE;
256: l_source_resource_list_id pa_budget_versions.resource_list_id%TYPE;
249: l_return_status VARCHAR2(2000);
250: l_debug_mode VARCHAR2(30);
251:
252: l_source_fp_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE ;
253: l_target_fp_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE ;
254: --l_locked_by_person_id pa_budget_versions.locked_by_person_id%TYPE;
255: l_project_id pa_projects_all.project_id%TYPE;
256: l_source_resource_list_id pa_budget_versions.resource_list_id%TYPE;
257: l_baselined_resource_list_id pa_budget_versions.resource_list_id%TYPE;
261: l_baselined_version_id pa_budget_versions.budget_version_id%TYPE;
262: l_target_plan_version_id pa_budget_versions.budget_version_id%TYPE;
263: l_version_type pa_budget_versions.version_type%TYPE;
264: l_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE;
265: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
266: l_target_appr_rev_plan_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
267:
268: -- Start of Variables defined for bug 2729498
269:
308: l_pc_rev_exchange_rate_tbl pc_rev_exchange_rate_tbl_typ;
309: l_pfc_cost_exchange_rate_tbl pfc_cost_exchange_rate_tbl_typ;
310: l_pfc_rev_exchange_rate_tbl pfc_rev_exchange_rate_tbl_typ;
311:
312: l_source_plan_in_mc_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
313: l_source_appr_rev_plan_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE; /* Bug 3276128 */
314:
315: -- End of plsql tables defined for bug#2729191
316:
309: l_pfc_cost_exchange_rate_tbl pfc_cost_exchange_rate_tbl_typ;
310: l_pfc_rev_exchange_rate_tbl pfc_rev_exchange_rate_tbl_typ;
311:
312: l_source_plan_in_mc_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
313: l_source_appr_rev_plan_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE; /* Bug 3276128 */
314:
315: -- End of plsql tables defined for bug#2729191
316:
317: -- Start of cursors defined for bug#2729191
332: ,project_cost_rate_date
333: ,project_rev_rate_type
334: ,project_rev_rate_date_type
335: ,project_rev_rate_date
336: FROM PA_PROJ_FP_OPTIONS
337: WHERE fin_plan_version_id = p_target_plan_version_id;
338:
339: target_fp_options_rec target_fp_options_cur%ROWTYPE;
340:
407: ,nvl(approved_rev_plan_type_flag,'N') /* Bug#3276128 */
408: INTO l_source_fp_pref_code
409: ,l_source_plan_in_mc_flag -- Bug#2729191
410: ,l_source_appr_rev_plan_flag /* Bug#3276128 */
411: FROM pa_proj_fp_options
412: WHERE fin_plan_version_id=p_source_plan_version_id;
413:
414: IF P_PA_DEBUG_MODE = 'Y' THEN
415: pa_debug.g_err_stage:='Fetching the target plan preference code';
417: END IF;
418:
419: SELECT fin_plan_preference_code,nvl(approved_rev_plan_type_flag,'N')
420: INTO l_target_fp_pref_code,l_target_appr_rev_plan_flag
421: FROM pa_proj_fp_options
422: WHERE fin_plan_version_id=p_target_plan_version_id;
423:
424: IF P_PA_DEBUG_MODE = 'Y' THEN
425: pa_debug.g_err_stage:='Checking the compatability of the plans';
502: INTO l_project_id
503: FROM PA_BUDGET_VERSIONS
504: WHERE budget_version_id = p_target_plan_version_id;
505:
506: --Acquire lock on pa_proj_fp_options and pa_budget_versions so that
507: --no other process would be able to modify these tables and all
508: --underlying child tables
509:
510: Acquire_Locks_For_Copy_Plan(
634: pa_debug.g_err_stage:='Updating the target proj fp option with the MC attributes';
635: pa_debug.write('Copy_Plan: ' || g_module_name,pa_debug.g_err_stage,3);
636: END IF;
637:
638: UPDATE PA_PROJ_FP_OPTIONS
639: SET plan_in_multi_curr_flag = target_fp_options_rec.plan_in_multi_curr_flag
640: ,projfunc_cost_rate_type = target_fp_options_rec.projfunc_cost_rate_type
641: ,projfunc_cost_rate_date_type = target_fp_options_rec.projfunc_cost_rate_date_type
642: ,projfunc_cost_rate_date = target_fp_options_rec.projfunc_cost_rate_date
781: /*===========================================================================
782: This function is used to return fin plan amount type for given plan version
783: ===========================================================================*/
784: FUNCTION Get_Fin_Plan_Amount_Type (
785: p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE)
786: RETURN VARCHAR2
787: IS
788: l_amount_type VARCHAR2(30);
789: BEGIN
791: ,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, 'A'
792: ,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, 'R'
793: ,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, 'C')
794: INTO l_amount_type
795: FROM PA_PROJ_FP_OPTIONS
796: WHERE fin_plan_version_id = p_fin_plan_version_id;
797:
798: RETURN l_amount_type;
799: END Get_Fin_Plan_Amount_Type;
803: This procedure is used to acquire all the required locks for copy_actual
804: ==============================================================================*/
805:
806: PROCEDURE Acquire_Locks_For_Copy_Actual(
807: p_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
808: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
809: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
810: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
811: AS
818: pragma exception_init(Resource_Busy,-00054);
819:
820: CURSOR fp_opt_cur IS
821: SELECT record_version_number
822: FROM PA_PROJ_FP_OPTIONS
823: WHERE fin_plan_version_id = p_plan_version_id
824: FOR UPDATE NOWAIT;
825:
826: CURSOR bdgt_ver_cur IS
839: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
840: l_debug_mode := NVL(l_debug_mode, 'Y');
841:
842: /*
843: * Acquire lock on pa_proj_fp_options and pa_budget_versions so that
844: * no other process would be able to modify these tables and all
845: * underlying child tables
846: */
847:
845: * underlying child tables
846: */
847:
848: IF P_PA_DEBUG_MODE = 'Y' THEN
849: pa_debug.g_err_stage:='Acquiring lock on pa_proj_fp_options';
850: pa_debug.write('Acquire_Locks_For_Copy_Actual: ' || g_module_name,pa_debug.g_err_stage,3);
851: END IF;
852:
853: OPEN fp_opt_cur;
859:
860: OPEN bdgt_ver_cur;
861:
862: --Increment the record_version_number in pa_budget_versions and
863: --pa_proj_fp_options
864:
865: IF P_PA_DEBUG_MODE = 'Y' THEN
866: pa_debug.g_err_stage:='Incrementing record version number of plan version pa_proj_fp_options';
867: pa_debug.write('Acquire_Locks_For_Copy_Actual: ' || g_module_name,pa_debug.g_err_stage,3);
862: --Increment the record_version_number in pa_budget_versions and
863: --pa_proj_fp_options
864:
865: IF P_PA_DEBUG_MODE = 'Y' THEN
866: pa_debug.g_err_stage:='Incrementing record version number of plan version pa_proj_fp_options';
867: pa_debug.write('Acquire_Locks_For_Copy_Actual: ' || g_module_name,pa_debug.g_err_stage,3);
868: END IF;
869:
870: UPDATE PA_PROJ_FP_OPTIONS
866: pa_debug.g_err_stage:='Incrementing record version number of plan version pa_proj_fp_options';
867: pa_debug.write('Acquire_Locks_For_Copy_Actual: ' || g_module_name,pa_debug.g_err_stage,3);
868: END IF;
869:
870: UPDATE PA_PROJ_FP_OPTIONS
871: SET record_version_number = record_version_number+1
872: WHERE fin_plan_version_id=p_plan_version_id;
873:
874: IF P_PA_DEBUG_MODE = 'Y' THEN
1028: l_adj_percentage NUMBER;
1029: l_source_project_id pa_projects_all.project_id%TYPE;
1030: l_target_project_id pa_projects_all.project_id%TYPE;
1031: l_version_name pa_budget_versions.version_name%TYPE;
1032: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
1033: l_current_profile_id pa_budget_versions.period_profile_id%TYPE;
1034: l_max_version pa_budget_versions.version_number%TYPE;
1035: l_version_number pa_budget_versions.version_number%TYPE;
1036: l_version_type pa_budget_versions.version_type%TYPE;
1055:
1056: l_wbs_struct_version_id pa_budget_versions.project_structure_version_id%TYPE;
1057: l_source_cur_planning_period pa_budget_versions.current_planning_period%TYPE;
1058: l_target_cur_planning_period pa_budget_versions.current_planning_period%TYPE;
1059: l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
1060:
1061: l_gl_start_period gl_periods.period_name%TYPE;
1062: l_gl_end_period gl_periods.period_name%TYPE;
1063: l_gl_start_Date VARCHAR2(100);
1689: PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, 'Y',
1690: PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, 'Y') --revenue_flag
1691: INTO l_cost_flag
1692: ,l_revenue_flag
1693: FROM pa_proj_fp_options
1694: WHERE fin_plan_version_id = px_target_version_id;
1695:
1696: --Set the cost and revenue flags to 'N' if adj percentage is nonzero.
1697:
2264: l_revenue_flag pa_fin_plan_amount_sets.revenue_flag%TYPE;
2265:
2266: l_tmp NUMBER;
2267: l_source_project_id pa_projects_all.project_id%TYPE;
2268: l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
2269: l_control_flag pa_resource_lists_all_bg.control_flag%TYPE;
2270: l_resource_list_id pa_proj_fp_options.cost_resource_list_id%TYPE;
2271:
2272: l_project_org pa_projects_all.carrying_out_organization_id%TYPE; -- bug 6161031
2266: l_tmp NUMBER;
2267: l_source_project_id pa_projects_all.project_id%TYPE;
2268: l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
2269: l_control_flag pa_resource_lists_all_bg.control_flag%TYPE;
2270: l_resource_list_id pa_proj_fp_options.cost_resource_list_id%TYPE;
2271:
2272: l_project_org pa_projects_all.carrying_out_organization_id%TYPE; -- bug 6161031
2273:
2274:
2345: ,l_source_project_id
2346: ,l_fin_plan_level_code
2347: ,l_resource_list_id
2348: ,l_control_flag
2349: FROM pa_proj_fp_options pfot,--target
2350: pa_proj_fp_options pfos,--source
2351: pa_resource_lists_all_bg rl
2352: WHERE pfot.fin_plan_version_id=p_target_plan_version_id
2353: AND pfos.fin_plan_version_id=p_source_plan_version_id
2346: ,l_fin_plan_level_code
2347: ,l_resource_list_id
2348: ,l_control_flag
2349: FROM pa_proj_fp_options pfot,--target
2350: pa_proj_fp_options pfos,--source
2351: pa_resource_lists_all_bg rl
2352: WHERE pfot.fin_plan_version_id=p_target_plan_version_id
2353: AND pfos.fin_plan_version_id=p_source_plan_version_id
2354: AND rl.resource_list_id=nvl(pfot.cost_resource_list_id, nvl(pfot.revenue_resource_list_id,pfot.all_resource_list_id));
4080: ----3. Update rate based flag for RAs in the target version if any RAs have to be converted to non rate based
4081: ----4. Update the budget lines with the missing amounts
4082: PROCEDURE derv_missing_amts_chk_neg_qty
4083: (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
4084: p_targ_pref_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE,
4085: p_source_version_type IN pa_budget_versions.version_type%TYPE,
4086: p_target_version_type IN pa_budget_versions.version_type%TYPE,
4087: p_src_plan_class_code IN pa_fin_plan_types_b.plan_class_code%TYPE,
4088: p_derv_rates_missing_amts_flag IN VARCHAR2,
4112: l_burd_cost_override_rate_tbl SYSTEM.pa_num_tbl_type;
4113: l_bill_override_rate_tbl SYSTEM.pa_num_tbl_type;
4114: l_non_rb_ra_id_tbl SYSTEM.pa_num_tbl_type;
4115: l_bl_rb_flag_chg_tbl SYSTEM.pa_varchar2_1_tbl_type;
4116: l_target_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
4117: l_prev_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
4118: l_temp_flag VARCHAR2(1);
4119: l_init_quantity_tbl SYSTEM.pa_num_tbl_type;
4120: l_txn_currency_code_tbl SYSTEM.pa_varchar2_15_tbl_type;
4742: AND pbv.fin_plan_type_id = pfb.fin_plan_type_id;
4743: -- Bug 3927244
4744:
4745: --Bug 4290043
4746: l_target_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
4747:
4748:
4749: BEGIN
4750:
4824: ,fin_plan_preference_code
4825: INTO l_revenue_flag
4826: ,l_cost_flag
4827: ,l_target_pref_code
4828: FROM pa_proj_fp_options
4829: WHERE fin_plan_version_id=p_target_plan_version_id;
4830: /*
4831: pa_debug.g_err_stage:='l_revenue_flag ='||l_revenue_flag;
4832: IF P_PA_DEBUG_MODE = 'Y' THEN
5351: ,PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME ,'-99') --copy both cost and revenue
5352: ,project_id
5353: INTO l_ignore_amount_type
5354: ,l_target_project_id
5355: FROM pa_proj_fp_options
5356: WHERE fin_plan_version_id=p_target_plan_version_id;
5357: /*
5358: pa_debug.g_err_stage:='l_ignore_amount_type = '||l_ignore_amount_type;
5359: IF P_PA_DEBUG_MODE = 'Y' THEN
5983: PA_BUDGET_CORE.COPY_BUDGETS_FROM_PROJECT
5984: Bug# - 2981655 - Please see bug for the complete discussion about
5985: this bug. The core is, when copy_project is done with copy_budget_flag
5986: as N, we still have to copy the header level informations
5987: pa_proj_fp_options, pa_fp_txn_currencies, period profile information.
5988: Also, we should not be copying the planning elements for any of the
5989: copied options since when copy_budget_flag is N, tasks may not have
5990: been copied from the source project to the target project.
5991:
6026:
6027: l_source_template_flag pa_projects_all.template_flag%TYPE;
6028: l_source_current_flag pa_budget_versions.current_flag%TYPE;
6029: l_source_version_id pa_budget_versions.budget_version_id%TYPE;
6030: l_source_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6031: l_source_record_version_num pa_budget_versions.record_version_number%TYPE;
6032: l_source_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6033:
6034: l_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
6028: l_source_current_flag pa_budget_versions.current_flag%TYPE;
6029: l_source_version_id pa_budget_versions.budget_version_id%TYPE;
6030: l_source_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6031: l_source_record_version_num pa_budget_versions.record_version_number%TYPE;
6032: l_source_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6033:
6034: l_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
6035:
6036: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
6030: l_source_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6031: l_source_record_version_num pa_budget_versions.record_version_number%TYPE;
6032: l_source_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6033:
6034: l_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
6035:
6036: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
6037: l_target_profile_id pa_budget_versions.period_profile_id%TYPE;
6038: l_target_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
6034: l_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
6035:
6036: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
6037: l_target_profile_id pa_budget_versions.period_profile_id%TYPE;
6038: l_target_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
6039: l_target_record_version_num pa_budget_versions.record_version_number%TYPE;
6040: l_version_type pa_budget_versions.version_type%TYPE;
6041: l_funding_exists_flag VARCHAR2(1);
6042:
6040: l_version_type pa_budget_versions.version_type%TYPE;
6041: l_funding_exists_flag VARCHAR2(1);
6042:
6043:
6044: l_fp_option_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE;
6045: l_plan_in_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
6046: l_appr_cost_plan_type_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
6047: l_appr_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
6048: l_struct_elem_version_id pa_proj_element_versions.element_version_id%TYPE;
6041: l_funding_exists_flag VARCHAR2(1);
6042:
6043:
6044: l_fp_option_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE;
6045: l_plan_in_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
6046: l_appr_cost_plan_type_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
6047: l_appr_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
6048: l_struct_elem_version_id pa_proj_element_versions.element_version_id%TYPE;
6049: l_budget_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
6042:
6043:
6044: l_fp_option_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE;
6045: l_plan_in_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
6046: l_appr_cost_plan_type_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
6047: l_appr_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
6048: l_struct_elem_version_id pa_proj_element_versions.element_version_id%TYPE;
6049: l_budget_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
6050: l_src_budget_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
6043:
6044: l_fp_option_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE;
6045: l_plan_in_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
6046: l_appr_cost_plan_type_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
6047: l_appr_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
6048: l_struct_elem_version_id pa_proj_element_versions.element_version_id%TYPE;
6049: l_budget_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
6050: l_src_budget_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
6051:
6247: ,l_source_version_id
6248: ,l_source_fp_preference_code
6249: ,l_source_fin_plan_type_id
6250: ,l_plan_in_multi_curr_flag
6251: FROM pa_proj_fp_options
6252: WHERE proj_fp_options_id = l_proj_fp_options_id_tbl(l_index);
6253:
6254: IF P_PA_DEBUG_MODE = 'Y' THEN
6255: pa_debug.g_err_stage := 'Option level code = '||l_fp_option_level_code;
6303: END IF;/* Added for bug 2986930 */
6304: END IF;
6305:
6306: IF l_fp_option_level_code <> PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION THEN
6307: --Create equivalent fp option in pa_proj_fp_options for target
6308:
6309: IF P_PA_DEBUG_MODE = 'Y' THEN
6310: pa_debug.g_err_stage := 'Calling Create_Fp_Option';
6311: pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
6310: pa_debug.g_err_stage := 'Calling Create_Fp_Option';
6311: pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
6312: END IF;
6313:
6314: PA_PROJ_FP_OPTIONS_PUB.Create_Fp_Option (
6315: px_target_proj_fp_option_id => l_target_proj_fp_options_id
6316: ,p_source_proj_fp_option_id => l_proj_fp_options_id_tbl(l_index)
6317: ,p_target_fp_option_level_code => l_fp_option_level_code --same as source
6318: ,p_target_fp_preference_code => l_source_fp_preference_code
6345: ( NVL(p_agreement_amount,-1) < 0 AND ( NVL(l_baseline_funding_flag,'N') = 'Y' AND NVl(l_ar_exists,'N') <> 'Y' ) ) OR
6346: ( NVL(p_agreement_amount,-1) < 0 AND NVL(l_baseline_funding_flag,'N') = 'N' )
6347: THEN
6348: /* Code addition for Bug 2986930 ends */
6349: --Create equivalent fp option in pa_proj_fp_options for target
6350:
6351: IF P_PA_DEBUG_MODE = 'Y' THEN
6352: pa_debug.g_err_stage := 'Calling Create_Fp_Option';
6353: pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
6352: pa_debug.g_err_stage := 'Calling Create_Fp_Option';
6353: pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
6354: END IF;
6355:
6356: PA_PROJ_FP_OPTIONS_PUB.Create_Fp_Option (
6357: px_target_proj_fp_option_id => l_target_proj_fp_options_id
6358: ,p_source_proj_fp_option_id => l_proj_fp_options_id_tbl(l_index)
6359: ,p_target_fp_option_level_code => l_fp_option_level_code --same as source
6360: ,p_target_fp_preference_code => l_source_fp_preference_code
6865: l_msg_index_out NUMBER;
6866: l_debug_mode VARCHAR2(30);
6867:
6868: l_index NUMBER;
6869: l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6870: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
6871: l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
6872: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6873:
6866: l_debug_mode VARCHAR2(30);
6867:
6868: l_index NUMBER;
6869: l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6870: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
6871: l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
6872: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6873:
6874: l_proj_fp_options_id_tbl PROJ_FP_OPTIONS_ID_TBL_TYP;
6867:
6868: l_index NUMBER;
6869: l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6870: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
6871: l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
6872: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6873:
6874: l_proj_fp_options_id_tbl PROJ_FP_OPTIONS_ID_TBL_TYP;
6875:
6868: l_index NUMBER;
6869: l_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
6870: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
6871: l_fin_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
6872: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6873:
6874: l_proj_fp_options_id_tbl PROJ_FP_OPTIONS_ID_TBL_TYP;
6875:
6876: CURSOR cur_for_fp_options(c_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE) IS
6872: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
6873:
6874: l_proj_fp_options_id_tbl PROJ_FP_OPTIONS_ID_TBL_TYP;
6875:
6876: CURSOR cur_for_fp_options(c_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE) IS
6877: SELECT pfo.proj_fp_options_id
6878: ,pfo.fin_plan_type_id
6879: ,pfo.fin_plan_preference_code
6880: FROM pa_proj_fp_options pfo
6876: CURSOR cur_for_fp_options(c_level_code pa_proj_fp_options.fin_plan_option_level_code%TYPE) IS
6877: SELECT pfo.proj_fp_options_id
6878: ,pfo.fin_plan_type_id
6879: ,pfo.fin_plan_preference_code
6880: FROM pa_proj_fp_options pfo
6881: ,pa_fin_plan_types_b fin
6882: WHERE project_id = p_source_project_id
6883: AND fin_plan_option_level_code = c_level_code
6884: AND pfo.fin_plan_type_id = fin.fin_plan_type_id(+)
7216: Private procedure (not available in specification) used by COPY_BUDGET_LINES
7217: (with shift days logic) to insert shifted periods data into pa_fp_cpy_period_tmp */
7218:
7219: PROCEDURE populate_cpy_periods_tmp(p_budget_version_id PA_BUDGET_LINES.budget_version_id%type,
7220: p_period_type PA_PROJ_FP_OPTIONS.cost_time_phased_code%TYPE,
7221: p_shift_periods number) AS
7222: cursor bl_periods is
7223: SELECT distinct bl.period_name,bl.start_date
7224: FROM pa_budget_lines bl
7319: l_return_status VARCHAR2(2000);
7320: l_debug_mode VARCHAR2(30);
7321:
7322: l_shift_days NUMBER;
7323: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
7324: l_target_budget_entry_level pa_proj_fp_options.all_fin_plan_level_code%TYPE; /* bug2726011 */
7325: l_target_proj_start_date DATE; /*bug2726011*/
7326: l_target_proj_completion_date DATE; /*bug2726011*/
7327:
7320: l_debug_mode VARCHAR2(30);
7321:
7322: l_shift_days NUMBER;
7323: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
7324: l_target_budget_entry_level pa_proj_fp_options.all_fin_plan_level_code%TYPE; /* bug2726011 */
7325: l_target_proj_start_date DATE; /*bug2726011*/
7326: l_target_proj_completion_date DATE; /*bug2726011*/
7327:
7328: /* Bug# 2634726 */
8742: l_adj_percentage NUMBER;
8743: l_copy_mode VARCHAR2(1) := p_copy_mode;
8744: l_shift_days NUMBER :=0;--SHOULD BE REMOVED LATER
8745: l_targ_budget_version_id pa_budget_versions.budget_version_id%TYPE;
8746: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
8747: l_targ_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
8748: l_wp_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
8749: l_targ_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
8750: l_pji_rollup_required VARCHAR(1);
8743: l_copy_mode VARCHAR2(1) := p_copy_mode;
8744: l_shift_days NUMBER :=0;--SHOULD BE REMOVED LATER
8745: l_targ_budget_version_id pa_budget_versions.budget_version_id%TYPE;
8746: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
8747: l_targ_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
8748: l_wp_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
8749: l_targ_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
8750: l_pji_rollup_required VARCHAR(1);
8751:
8745: l_targ_budget_version_id pa_budget_versions.budget_version_id%TYPE;
8746: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
8747: l_targ_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
8748: l_wp_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
8749: l_targ_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
8750: l_pji_rollup_required VARCHAR(1);
8751:
8752: -- pjdvdsn1 compile issues, 03-FEB-2004, jwhite -------------------------
8753:
8973: BEGIN
8974:
8975: SELECT proj_fp_options_id
8976: INTO l_src_proj_fp_options_id
8977: FROM pa_proj_fp_options
8978: WHERE fin_plan_version_id=l_src_budget_version_id;
8979:
8980: EXCEPTION
8981: WHEN NO_DATA_FOUND THEN
8980: EXCEPTION
8981: WHEN NO_DATA_FOUND THEN
8982:
8983: IF l_debug_mode = 'Y' THEN
8984: pa_debug.g_err_stage:='No data found in pa_proj_fp_options for fin_plan_version_id '
8985: ||l_src_budget_version_id;
8986: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
8987: END IF;
8988: RAISE;
8994: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8995: END IF;
8996:
8997: --Call the method to create the FP Option
8998: PA_PROJ_FP_OPTIONS_PUB.Create_Fp_Option
8999: (
9000: px_target_proj_fp_option_id => l_targ_proj_fp_options_id
9001: ,p_source_proj_fp_option_id => l_src_proj_fp_options_id
9002: ,p_target_fp_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION
9020:
9021: --Derive the multi currency flag so as to call the copy planning txn currencies
9022: SELECT plan_in_multi_curr_flag
9023: INTO l_targ_multi_curr_flag
9024: FROM pa_proj_fp_options
9025: WHERE proj_fp_options_id=l_targ_proj_fp_options_id;
9026:
9027: IF l_debug_mode = 'Y' THEN
9028: pa_debug.g_err_stage:='Calling copy fp txn currencies';
9471:
9472: l_etc_start_date pa_budget_versions.etc_start_date%TYPE;
9473:
9474: --Bug 4290043.
9475: l_targ_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
9476: l_source_version_type pa_budget_versions.version_type%TYPE;
9477: l_target_version_type pa_budget_versions.version_type%TYPE;
9478: l_src_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
9479:
9541: ,fin_plan_preference_code
9542: INTO l_revenue_flag
9543: ,l_cost_flag
9544: ,l_targ_pref_code
9545: FROM pa_proj_fp_options
9546: WHERE fin_plan_version_id=p_target_plan_version_id;
9547:
9548: --Bug 3927244
9549: --Bug 4290043. Selected version type too
10044: the budgets related data from source published version.
10045: Synchronisation involves:
10046: 1) pa_fp_txn_currencies
10047: 2) rate schedules, generation options and plan settings data
10048: (pa_proj_fp_options)
10049:
10050: Stating some of the business rules for clarity:
10051: i) If there is a published version, time phasing can not be changed
10052: ii) Planning resource list can change only if existing resource list is
10054: data. Please note that in this case, only 'PEOPLE' resource class assignments
10055: would be present.
10056: iii) RBS can be different only if existing RBS is null.
10057:
10058: Bug 3725414: In update to pa_proj_fp_options, rbs_version_id column is missing
10059:
10060: Bug 4101153: Current Planning period should always get the value from the source version and
10061: not from the workplan plan type option. Removed the update to current planning period
10062:
10123: ,pfo.cost_job_rate_sch_id
10124: ,pfo.cost_non_labor_res_rate_sch_id
10125: ,pfo.cost_res_class_rate_sch_id
10126: ,pfo.cost_burden_rate_sch_id
10127: FROM pa_proj_fp_options pfo
10128: ,pa_fin_plan_types_b fpt
10129: WHERE pfo.project_id = p_project_id
10130: AND pfo.fin_plan_type_id = fpt.fin_plan_type_id
10131: AND fpt.use_for_workplan_flag = 'Y'
10136: CURSOR wp_version_options_cur IS
10137: SELECT pfo.proj_fp_options_id
10138: ,pfo.rbs_version_id
10139: ,pfo.cost_resource_list_id
10140: FROM pa_proj_fp_options pfo
10141: ,pa_budget_versions bv
10142: WHERE bv.budget_version_id = p_wp_version_id
10143: AND bv.project_id = pfo.project_id
10144: AND pfo.fin_plan_version_id = bv.budget_version_id;
10232: ,last_update_login = FND_GLOBAL.login_id
10233: ,record_version_number = record_version_number + 1
10234: WHERE budget_version_id = p_wp_version_id;
10235:
10236: -- Update pa_proj_fp_options entity
10237: UPDATE pa_proj_fp_options
10238: SET track_workplan_costs_flag = parent_plan_type_rec.track_workplan_costs_flag
10239: ,plan_in_multi_curr_flag = parent_plan_type_rec.plan_in_multi_curr_flag
10240: ,margin_derived_from_code = parent_plan_type_rec.margin_derived_from_code
10233: ,record_version_number = record_version_number + 1
10234: WHERE budget_version_id = p_wp_version_id;
10235:
10236: -- Update pa_proj_fp_options entity
10237: UPDATE pa_proj_fp_options
10238: SET track_workplan_costs_flag = parent_plan_type_rec.track_workplan_costs_flag
10239: ,plan_in_multi_curr_flag = parent_plan_type_rec.plan_in_multi_curr_flag
10240: ,margin_derived_from_code = parent_plan_type_rec.margin_derived_from_code
10241: ,factor_by_code = parent_plan_type_rec.factor_by_code