[Home] [Help]
108: ,pfo.rbs_version_id rbs_version_id
109: ,pbv.ci_id ci_id
110: ,pbv.etc_start_date etc_start_date
111: ,pbv.wp_version_flag wp_version_flag
112: FROM pa_proj_fp_options pfo
113: ,pa_budget_versions pbv
114: WHERE pfo.fin_plan_version_id=p_budget_version_id
115: AND pbv.budget_version_id=p_budget_version_id;
116:
1296: l_debug_mode VARCHAR2(30);
1297: --End of variables used for debugging
1298:
1299: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
1300: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1301: l_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
1302: l_rlm_id_tbl_count NUMBER := 0;
1303: l_elem_version_id_count NUMBER := 0;
1304: l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
1300: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1301: l_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
1302: l_rlm_id_tbl_count NUMBER := 0;
1303: l_elem_version_id_count NUMBER := 0;
1304: l_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
1305: l_people_res_class_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
1306: l_equip_res_class_rlm_id pa_resource_list_members.resource_list_member_id%TYPE; -- Bug 3749516 dummy Variable
1307: l_fin_res_class_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
1308: l_mat_res_class_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
1444: l_ra_id_del_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1445: l_ra_id_del_count NUMBER;
1446: --End of variables for Variable for TA Validations for p_context = TASK_ASSIGNMENTS
1447:
1448: l_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
1449: l_spread_amounts_for_ver VARCHAR2(1);
1450: l_index NUMBER := 1;
1451: l_spread_amount_flags_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
1452: l_delete_budget_lines_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
1499: l_rlm_id_no_of_rows NUMBER;
1500: l_elem_ver_id_no_of_rows NUMBER;
1501: l_ppl_index NUMBER;
1502: l_amount_exists VARCHAR2(1);
1503: l_rbs_version_id pa_proj_fp_options.rbs_version_id%TYPE;
1504:
1505: l_proj_curr_code pa_projects_all.project_currency_code%TYPE;
1506: l_proj_func_curr_code pa_projects_all.projfunc_currency_code%TYPE;
1507:
2258: SELECT nvl(cost_resource_list_id, nvl(revenue_resource_list_id, all_resource_list_id))
2259: ,rbs_version_id
2260: INTO l_resource_list_id
2261: ,l_rbs_version_id
2262: FROM pa_proj_fp_options
2263: WHERE fin_plan_version_id=l_budget_version_id;
2264:
2265: IF l_debug_mode = 'Y' THEN
2266: pa_debug.g_err_stage:='Fetching resource List id - l_resource_list_id : '|| l_resource_list_id;
7556: INDEX BY BINARY_INTEGER;
7557:
7558: l_txn_curr_code_tbl l_txn_curr_code_table;
7559:
7560: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%type;
7561: l_project_id pa_proj_fp_options.project_id%type;
7562: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%type;
7563: l_pc pa_projects_all.project_currency_code%type;
7564: l_pfc pa_projects_all.projfunc_currency_code%type;
7557:
7558: l_txn_curr_code_tbl l_txn_curr_code_table;
7559:
7560: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%type;
7561: l_project_id pa_proj_fp_options.project_id%type;
7562: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%type;
7563: l_pc pa_projects_all.project_currency_code%type;
7564: l_pfc pa_projects_all.projfunc_currency_code%type;
7565: --Code addition for bug#4200168 ends here.
7558: l_txn_curr_code_tbl l_txn_curr_code_table;
7559:
7560: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%type;
7561: l_project_id pa_proj_fp_options.project_id%type;
7562: l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%type;
7563: l_pc pa_projects_all.project_currency_code%type;
7564: l_pfc pa_projects_all.projfunc_currency_code%type;
7565: --Code addition for bug#4200168 ends here.
7566:
8204: WHERE BL.BUDGET_VERSION_ID = l_targ_budget_version_id
8205: AND NOT EXISTS
8206: (SELECT 1
8207: FROM PA_FP_TXN_CURRENCIES TC
8208: ,PA_PROJ_FP_OPTIONS pfo -- Bug 4872216
8209: WHERE tc.fin_plan_version_id =
8210: l_targ_budget_version_id
8211: AND pfo.project_id = p_target_project_id --4872216
8212: AND pfo.fin_plan_version_id =
8216: AND TC.txn_currency_code = BL.txn_currency_code);
8217:
8218: select proj_fp_options_id, project_id, fin_plan_type_id
8219: INTO l_proj_fp_options_id, l_project_id, l_fin_plan_type_id
8220: from pa_proj_fp_options
8221: WHERE fin_plan_version_id = l_targ_budget_version_id;
8222:
8223: select project_currency_code, projfunc_currency_code
8224: INTO l_pc, l_pfc
10013: --End of variables used for debugging
10014:
10015: l_wp_type_id NUMBER :=0;
10016: l_proj_wp_type_exists NUMBER :=0;
10017: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
10018: l_projfunc_currency_code pa_projects_all.projfunc_currency_code%type;
10019: l_proj_currency_code pa_projects_all.project_currency_code%type;
10020: l_appr_rev_plan_type_flag pa_fin_plan_types_b.approved_rev_plan_type_flag %TYPE;
10021:
10018: l_projfunc_currency_code pa_projects_all.projfunc_currency_code%type;
10019: l_proj_currency_code pa_projects_all.project_currency_code%type;
10020: l_appr_rev_plan_type_flag pa_fin_plan_types_b.approved_rev_plan_type_flag %TYPE;
10021:
10022: l_plan_in_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
10023: l_src_fp_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
10024: BEGIN
10025:
10026: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
10019: l_proj_currency_code pa_projects_all.project_currency_code%type;
10020: l_appr_rev_plan_type_flag pa_fin_plan_types_b.approved_rev_plan_type_flag %TYPE;
10021:
10022: l_plan_in_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
10023: l_src_fp_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
10024: BEGIN
10025:
10026: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
10027: l_debug_mode := NVL(l_debug_mode, 'N');
10101: INTO l_proj_wp_type_exists
10102: FROM DUAL
10103: WHERE EXISTS(
10104: SELECT 1
10105: FROM pa_proj_fp_options
10106: WHERE fin_plan_type_id = l_wp_type_id
10107: AND project_id = p_targ_project_id
10108: AND fin_plan_option_level_code = 'PLAN_TYPE');
10109:
10114:
10115: ---------------------------------------------------------
10116: -- If workplan_type is not already attched for the
10117: -- passed project_id then a record is created in
10118: -- pa_proj_fp_options and the default fp txn currencies
10119: -- are created.
10120: ---------------------------------------------------------
10121:
10122: IF l_proj_wp_type_exists = 0 THEN
10130:
10131: BEGIN
10132: SELECT proj_fp_options_id
10133: INTO l_src_fp_option_id
10134: FROM pa_proj_fp_options
10135: WHERE project_id=p_src_project_id
10136: AND fin_plan_type_id=l_wp_type_id
10137: AND fin_plan_option_level_code = 'PLAN_TYPE';
10138: EXCEPTION
10144: END IF;
10145:
10146:
10147: IF l_debug_mode = 'Y' THEN
10148: pa_debug.g_err_stage:='Calling API pa_proj_fp_options_pub.create_fp_option';
10149: pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.Add_wp_plan_type: ' || g_module_name,pa_debug.g_err_stage,3);
10150: END IF;
10151:
10152: IF l_debug_mode = 'Y' THEN
10154: pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.Add_wp_plan_type: ' || g_module_name,pa_debug.g_err_stage,3);
10155: END IF;
10156:
10157:
10158: pa_proj_fp_options_pub.Create_FP_Option (
10159: px_target_proj_fp_option_id => l_proj_fp_options_id
10160: ,p_source_proj_fp_option_id => l_src_fp_option_id
10161: ,p_target_fp_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
10162: ,p_target_fp_preference_code => PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY
10168: ,x_msg_data => l_msg_data);
10169:
10170: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10171: IF l_debug_mode = 'Y' THEN
10172: pa_debug.g_err_stage:='Called API pa_proj_fp_options_pub.Create_FP_Option returned error';
10173: pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.Add_wp_plan_type: ' || g_module_name,pa_debug.g_err_stage,5);
10174: END IF;
10175: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
10176: END IF;
10176: END IF;
10177:
10178: SELECT plan_in_multi_curr_flag
10179: INTO l_plan_in_multi_curr_flag
10180: FROM pa_proj_fp_options
10181: WHERE proj_fp_options_id = l_proj_fp_options_id;
10182:
10183: IF l_debug_mode = 'Y' THEN
10184: pa_debug.g_err_stage:='Calling API pa_fp_txn_currencies_pub.Copy_Fp_Txn_Currencies';
10751: ,'ALL', all_fin_plan_level_code) fin_plan_level_code
10752: ,pfo.rbs_version_id rbs_version_id
10753: ,pfo.fin_plan_type_id fin_plan_type_id
10754: ,bv.ci_id ci_id
10755: FROM pa_proj_fp_options pfo, pa_budget_versions bv
10756: WHERE bv.project_id = pfo.project_id
10757: AND pfo.fin_plan_version_id = bv.budget_version_id
10758: AND bv.budget_version_id = p_budget_version_id;
10759:
11326: PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, pfo.all_resource_list_id,
11327: PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, pfo.cost_resource_list_id,
11328: PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, pfo.revenue_resource_list_id) resource_list_id
11329: from pa_budget_versions bv,
11330: pa_proj_fp_options pfo
11331: where bv.budget_version_id = c_budget_version_id
11332: and pfo.project_id = bv.project_id
11333: and pfo.fin_plan_type_id = bv.fin_plan_type_id
11334: and pfo.fin_plan_version_id = bv.budget_version_id;
11697: /*=============================================================================
11698: This api is called upon save from Additional Workplan Options page.
11699: For versioning disabled case working version should be updated with values
11700: that of parent plan type record. The changes include deleting all the
11701: existing budget lines, resource assignments. Pa_proj_fp_options and
11702: pa_budget_versions should be updated with changed values.
11703:
11704: Bug 3595063 For a shared structure, update current working version with the
11705: new settings.
11803: ,pfo.cost_job_rate_sch_id
11804: ,pfo.cost_non_labor_res_rate_sch_id
11805: ,pfo.cost_res_class_rate_sch_id
11806: ,pfo.cost_burden_rate_sch_id
11807: FROM pa_proj_fp_options pfo
11808: ,pa_fin_plan_types_b fpt
11809: WHERE pfo.project_id = p_project_id
11810: AND pfo.fin_plan_type_id = fpt.fin_plan_type_id
11811: AND fpt.use_for_workplan_flag = 'Y'
11822: ,pfo.proj_fp_options_id
11823: ,bv.project_structure_version_id
11824: FROM pa_budget_versions bv,
11825: pa_proj_elem_ver_structure ver,
11826: pa_proj_fp_options pfo
11827: WHERE bv.project_id = p_project_id
11828: AND bv.wp_version_flag = 'Y'
11829: AND bv.project_id = ver.project_id
11830: AND bv.project_structure_version_id = ver.element_version_id
11837: SELECT bv.budget_version_id
11838: ,pfo.proj_fp_options_id
11839: FROM pa_budget_versions bv,
11840: pa_proj_elem_ver_structure ver,
11841: pa_proj_fp_options pfo
11842: WHERE bv.project_id = p_project_id
11843: AND bv.wp_version_flag = 'Y'
11844: AND bv.project_id = ver.project_id
11845: AND bv.project_structure_version_id = ver.element_version_id
11868: CURSOR all_workplan_versions_cur IS
11869: SELECT bv.budget_version_id
11870: ,pfo.proj_fp_options_id
11871: FROM pa_budget_versions bv
11872: ,pa_proj_fp_options pfo
11873: WHERE bv.project_id = p_project_id
11874: AND bv.wp_version_flag = 'Y'
11875: AND pfo.fin_plan_version_id = bv.budget_version_id
11876: AND pfo.project_id = bv.project_id;
12043: processing done for working wp versions in the loop below (delete planning transactions etc
12044: and we want to ensure this update is done after the processing */
12045:
12046: /* We also need to update the resource list id in the pa_budget_versions table as well as the
12047: pa_proj_fp_options table .*/
12048: FORALL i IN l_pub_proj_fp_options_id_tbl.first .. l_pub_proj_fp_options_id_tbl.last
12049: UPDATE pa_proj_fp_options
12050: SET cost_resource_list_id = parent_plan_type_rec.cost_resource_list_id
12051: ,record_version_number = record_version_number + 1
12045:
12046: /* We also need to update the resource list id in the pa_budget_versions table as well as the
12047: pa_proj_fp_options table .*/
12048: FORALL i IN l_pub_proj_fp_options_id_tbl.first .. l_pub_proj_fp_options_id_tbl.last
12049: UPDATE pa_proj_fp_options
12050: SET cost_resource_list_id = parent_plan_type_rec.cost_resource_list_id
12051: ,record_version_number = record_version_number + 1
12052: ,last_update_date = SYSDATE
12053: ,last_updated_by = FND_GLOBAL.user_id
12071: IF nvl(l_budget_version_id_tbl.count, 0) > 0 THEN
12072:
12073: -- Update proj_fp_options data for all the working versions in bulk
12074: FORALL i IN l_proj_fp_options_id_tbl.first .. l_proj_fp_options_id_tbl.last
12075: UPDATE pa_proj_fp_options
12076: SET track_workplan_costs_flag = parent_plan_type_rec.track_workplan_costs_flag
12077: ,plan_in_multi_curr_flag = parent_plan_type_rec.plan_in_multi_curr_flag
12078: -- Raja ,rbs_version_id = parent_plan_type_rec.rbs_version_id
12079: ,margin_derived_from_code = parent_plan_type_rec.margin_derived_from_code
12343: IF nvl(l_budget_version_id_tbl.count,0) > 0 THEN
12344:
12345: -- Bulk update all the versions with the new RBS header
12346: FORALL i IN l_budget_version_id_tbl.first .. l_budget_version_id_tbl.last
12347: UPDATE pa_proj_fp_options
12348: SET rbs_version_id = parent_plan_type_rec.rbs_version_id
12349: ,record_version_number = record_version_number + 1
12350: ,last_update_date = SYSDATE
12351: ,last_updated_by = FND_GLOBAL.user_id
12599: CURSOR working_budget_Versions_cur IS
12600: SELECT o.proj_fp_options_id,
12601: o.fin_plan_version_id,
12602: bv.resource_list_id
12603: FROM pa_proj_fp_options o,
12604: pa_budget_versions bv
12605: WHERE o.project_id = p_project_id
12606: AND o.fin_plan_type_id = p_fin_plan_type_id
12607: AND o.fin_plan_version_id = bv.budget_version_id
12614: ,bv.resource_list_id
12615: ,o.fin_plan_type_id
12616: ,bv.ci_id
12617: FROM pa_budget_versions bv,
12618: pa_proj_fp_options o
12619: WHERE bv.project_id = o.project_id
12620: AND bv.fin_plan_type_id = o.fin_plan_type_id
12621: AND bv.budget_version_id = o.fin_plan_version_id
12622: AND bv.budget_version_id = p_budget_version_id;
12683:
12684: -- fetch plan type level rbs version id
12685: Select rbs_version_id
12686: into l_rbs_version_id
12687: from pa_proj_fp_options
12688: where project_id = p_project_id
12689: and fin_plan_type_id = p_fin_plan_type_id
12690: and fin_plan_option_level_code = 'PLAN_TYPE';
12691:
12739: END IF;
12740:
12741: -- bulk update all the budget versions with the rbs version id
12742: forall i in l_proj_fp_options_id_tbl.first .. l_proj_fp_options_id_tbl.last
12743: update pa_proj_fp_options
12744: set rbs_version_id = l_rbs_version_id
12745: ,record_version_number = record_version_number + 1
12746: ,last_update_date = SYSDATE
12747: ,last_updated_by = FND_GLOBAL.user_id