[Home] [Help]
1: package body PA_FP_ELEMENTS_PUB as
2: /* $Header: PAFPELPB.pls 120.4.12020000.3 2013/04/04 07:22:59 djambhek ship $ */
3:
4: l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_elements_pub';
5: g_plsql_max_array_size NUMBER := 200;
1: package body PA_FP_ELEMENTS_PUB as
2: /* $Header: PAFPELPB.pls 120.4.12020000.3 2013/04/04 07:22:59 djambhek ship $ */
3:
4: l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_elements_pub';
5: g_plsql_max_array_size NUMBER := 200;
6:
7: /*==================================================================================================
8: REFRESH_FP_ELEMENTS: This procedure is used to refresh the existing FP Elements records i.e.
45: BEGIN
46:
47: FND_MSG_PUB.initialize;
48: IF P_PA_DEBUG_MODE = 'Y' THEN
49: pa_debug.init_err_stack('PA_FP_ELEMENTS_PUB.Refresh_FP_Elements');
50: END IF;
51: fnd_profile.get('pa_debug_MODE',l_debug_mode);
52: l_debug_mode := NVL(l_debug_mode, 'Y');
53: IF P_PA_DEBUG_MODE = 'Y' THEN
71: /* Depending on the Planning Level, i.e 'COST', PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE or PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, delete the
72: fp_elements for the Proj_FP_Options_ID and then call the Insert_Default procedure
73: to insert into fp_elements. */
74:
75: pa_debug.g_err_stage := 'Deleting records from pa_fp_elements and calling insert_Default';
76: IF P_PA_DEBUG_MODE = 'Y' THEN
77: pa_debug.write('Refresh_FP_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
78: END IF;
79:
174: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175: x_msg_count := 1;
176: x_msg_data := SQLERRM;
177: FND_MSG_PUB.add_exc_msg
178: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Refresh_FP_Elements'
179: ,p_procedure_name => pa_debug.G_Err_Stack );
180: IF P_PA_DEBUG_MODE = 'Y' THEN
181: pa_debug.write('Refresh_FP_Elements: ' || l_module_name,SQLERRM,4);
182: pa_debug.write('Refresh_FP_Elements: ' || l_module_name,pa_debug.G_Err_Stack,4);
195: -> If the Source FP Option is passed, then details are got from the Source FP Option and inserted
196: for the Target FP Option.
197:
198: Bug 2920954 :- This is an existing api that has been modified to include the resource selection and
199: resource planning level parameters to pa_fp_elements_pub.insert_default api. P_copy_mode has been
200: added as a parameter to this api. If copying elements for baselined version, only the elements with
201: plan amounts need to copied.
202:
203: For bug 2976168. Copy the elements from excluded_elements table if the copy mode is not B
225: l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
226: l_cost_planning_level pa_proj_fp_options.COST_FIN_PLAN_LEVEL_CODE%TYPE;
227: l_rev_planning_level pa_proj_fp_options.REVENUE_FIN_PLAN_LEVEL_CODE%TYPE;
228: l_from_proj_fp_option_id pa_proj_fp_options.PROJ_FP_OPTIONS_ID%TYPE;
229: l_from_element_type pa_fp_elements.ELEMENT_TYPE%TYPE;
230: l_to_fin_plan_type_id pa_proj_fp_options.FIN_PLAN_TYPE_ID%TYPE;
231: l_to_fin_plan_version_id pa_proj_fp_options.FIN_PLAN_VERSION_ID%TYPE;
232: l_to_project_id pa_projects.project_id%TYPE;
233: l_from_project_id pa_projects.project_id%TYPE;
260: BEGIN
261:
262: FND_MSG_PUB.initialize;
263: IF P_PA_DEBUG_MODE = 'Y' THEN
264: pa_debug.init_err_stack('PA_FP_ELEMENTS_PUB.Copy_Elements');
265: END IF;
266: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
267: l_debug_mode := NVL(l_debug_mode, 'Y');
268: IF P_PA_DEBUG_MODE = 'Y' THEN
312:
313: l_stage := 200;
314: IF (p_from_proj_fp_options_id IS NOT NULL) THEN
315: /* If the Source Proj FP Option is passed, then the records have to be copied
316: from pa_fp_elements for the Source FP Option and the Source Element Type. */
317: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Source Proj FP Option is passed.';
318: IF P_PA_DEBUG_MODE = 'Y' THEN
319: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
320: END IF;
328: l_par_fp_option_id := PA_PROJ_FP_OPTIONS_PUB.Get_Parent_FP_Option_ID(p_to_proj_fp_options_id);
329:
330:
331: IF (l_par_fp_option_id IS NOT NULL) THEN
332: /* Since Parent FP Option is found, records have to be copied from pa_fp_elements
333: for the Parent FP Option and the Target Element Type. */
334: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Parent FP Option is not null.';
335: IF P_PA_DEBUG_MODE = 'Y' THEN
336: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
390:
391:
392: ELSE
393: /* Parent Proj Option ID not found, so Insert Default */
394: /* First delete the records from pa_fp_elements and then insert the Default
395: Values into PA_FP_Elements table. */
396:
397: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Parent FP Option is null, hence insert_default.';
398: IF P_PA_DEBUG_MODE = 'Y' THEN
391:
392: ELSE
393: /* Parent Proj Option ID not found, so Insert Default */
394: /* First delete the records from pa_fp_elements and then insert the Default
395: Values into PA_FP_Elements table. */
396:
397: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Parent FP Option is null, hence insert_default.';
398: IF P_PA_DEBUG_MODE = 'Y' THEN
399: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
526: IF (l_from_proj_fp_option_id IS NOT NULL AND l_from_element_type IS NOT NULL) THEN
527: l_stage := 600;
528:
529: /* Get the values of the Plan_Type_ID and Plan_Version_ID of the Target
530: FP Option to be used while inserting records into pa_fp_elements. */
531:
532: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting info from to option id.';
533: IF P_PA_DEBUG_MODE = 'Y' THEN
534: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
574:
575: IF (l_from_planning_level <> l_to_planning_level OR
576: l_from_resource_list_id <> l_to_resource_list_id) THEN
577:
578: pa_debug.g_err_stage := 'Bug# 2684787: PA_FP_ELEMENTS_PUB.COPY_ELEMENTS being called with ' ||
579: 'incompatible planning levels/resource list ids..';
580: IF P_PA_DEBUG_MODE = 'Y' THEN
581: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,4);
582: END IF;
588: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
589: END IF;
590:
591:
592: /* Delete the records from pa_fp_elements for the Target Proj FP Option and Target Element Type
593: before inserting records into pa_fp_elements. */
594: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Deleting the Elements from FP Elements';
595: IF P_PA_DEBUG_MODE = 'Y' THEN
596: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
589: END IF;
590:
591:
592: /* Delete the records from pa_fp_elements for the Target Proj FP Option and Target Element Type
593: before inserting records into pa_fp_elements. */
594: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Deleting the Elements from FP Elements';
595: IF P_PA_DEBUG_MODE = 'Y' THEN
596: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
597: END IF;
601: ,x_return_status => x_return_status
602: ,x_msg_count => x_msg_count
603: ,x_msg_data => x_msg_data);
604:
605: /* Get the records from pa_fp_elements for the Proj FP Option and the Element Type
606: and insert into PA_FP_ELEMENTS. */
607: l_stage :=700;
608:
609: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting records into PA_FP_ELEMENTS';
602: ,x_msg_count => x_msg_count
603: ,x_msg_data => x_msg_data);
604:
605: /* Get the records from pa_fp_elements for the Proj FP Option and the Element Type
606: and insert into PA_FP_ELEMENTS. */
607: l_stage :=700;
608:
609: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting records into PA_FP_ELEMENTS';
610: IF P_PA_DEBUG_MODE = 'Y' THEN
605: /* Get the records from pa_fp_elements for the Proj FP Option and the Element Type
606: and insert into PA_FP_ELEMENTS. */
607: l_stage :=700;
608:
609: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting records into PA_FP_ELEMENTS';
610: IF P_PA_DEBUG_MODE = 'Y' THEN
611: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
612: END IF;
613:
614: -- IF source and target projects are same
615:
616: IF l_from_project_id = l_to_project_id THEN
617:
618: INSERT INTO pa_fp_elements
619: (PROJ_FP_ELEMENTS_ID
620: ,PROJ_FP_OPTIONS_ID
621: ,PROJECT_ID
622: ,FIN_PLAN_TYPE_ID
637: ,LAST_UPDATED_BY
638: ,CREATION_DATE
639: ,CREATED_BY
640: ,LAST_UPDATE_LOGIN)
641: SELECT pa_fp_elements_s.nextval
642: ,p_to_proj_fp_options_id
643: ,project_id
644: ,l_to_fin_plan_type_id
645: ,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_to_element_type)
659: ,fnd_global.user_id
660: ,sysdate
661: ,fnd_global.user_id
662: ,fnd_global.login_id
663: FROM pa_fp_elements
664: WHERE proj_fp_options_id = l_from_proj_fp_option_id
665: AND element_type = decode(l_from_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,l_from_element_type)
666: AND NVL(plan_amount_exists_flag,'N') = decode(p_copy_mode,PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_BASELINED,'Y',NVL(plan_amount_exists_flag,'N')); /* Bug 2920954 */
667:
673: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': projects are different.';
674: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
675: END IF;
676:
677: INSERT INTO pa_fp_elements
678: (PROJ_FP_ELEMENTS_ID
679: ,PROJ_FP_OPTIONS_ID
680: ,PROJECT_ID
681: ,FIN_PLAN_TYPE_ID
697: ,CREATION_DATE
698: ,CREATED_BY
699: ,LAST_UPDATE_LOGIN)
700: SELECT
701: pa_fp_elements_s.nextval
702: ,p_to_proj_fp_options_id
703: ,l_to_project_id
704: ,l_to_fin_plan_type_id
705: ,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_to_element_type)
719: ,fnd_global.user_id
720: ,sysdate
721: ,fnd_global.user_id
722: ,fnd_global.login_id
723: FROM pa_fp_elements fp,
724: pa_tasks source_pt,
725: pa_tasks target_pt
726: WHERE fp.proj_fp_options_id = l_from_proj_fp_option_id
727: AND fp.task_id = source_pt.task_id
761: IF (l_all_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT) OR
762: (l_cost_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT ) OR
763: (l_revenue_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT )
764: THEN
765: INSERT INTO pa_fp_elements
766: (PROJ_FP_ELEMENTS_ID
767: ,PROJ_FP_OPTIONS_ID
768: ,PROJECT_ID
769: ,FIN_PLAN_TYPE_ID
784: ,LAST_UPDATED_BY
785: ,CREATION_DATE
786: ,CREATED_BY
787: ,LAST_UPDATE_LOGIN)
788: SELECT pa_fp_elements_s.nextval
789: ,p_to_proj_fp_options_id
790: ,l_to_project_id
791: ,l_to_fin_plan_type_id
792: ,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_to_element_type)
806: ,fnd_global.user_id
807: ,sysdate
808: ,fnd_global.user_id
809: ,fnd_global.login_id
810: FROM pa_fp_elements fp
811: WHERE fp.proj_fp_options_id = l_from_proj_fp_option_id
812: AND fp.task_id = 0
813: AND element_type = decode(l_from_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,l_from_element_type);
814:
878: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
879: x_msg_count := 1;
880: x_msg_data := SQLERRM;
881: FND_MSG_PUB.add_exc_msg
882: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Copy_Elements'
883: ,p_procedure_name => pa_debug.G_Err_Stack );
884: IF P_PA_DEBUG_MODE = 'Y' THEN
885: pa_debug.write('Copy_Elements: ' || l_module_name,SQLERRM,4);
886: pa_debug.write('Copy_Elements: ' || l_module_name,pa_debug.G_Err_Stack,4);
928: l_debug_mode VARCHAR2(30);
929: l_stage NUMBER := 100;
930: l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
931: l_resource_list_id pa_proj_fp_options.ALL_RESOURCE_LIST_ID%TYPE;
932: -- Bug 2920954 l_res_planning_level pa_fp_elements.RESOURCE_PLANNING_LEVEL%TYPE;
933:
934: /* start of variables defined for Bug 2920954*/
935: l_select_res_auto_flag pa_proj_fp_options.select_cost_res_auto_flag%TYPE;
936: l_res_planning_level pa_proj_fp_options.cost_res_planning_level%TYPE;
935: l_select_res_auto_flag pa_proj_fp_options.select_cost_res_auto_flag%TYPE;
936: l_res_planning_level pa_proj_fp_options.cost_res_planning_level%TYPE;
937: /*end of variables defined for Bug 2920954*/
938:
939: l_resource_list_member_id CONSTANT pa_fp_elements.RESOURCE_LIST_MEMBER_ID%TYPE := 0;
940: l_task_planning_level_top CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP;
941: l_task_planning_level_low CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE
942: := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST;
943: l_res_planned_for_task CONSTANT pa_fp_elements.RESOURCES_PLANNED_FOR_TASK%TYPE := 'N';
936: l_res_planning_level pa_proj_fp_options.cost_res_planning_level%TYPE;
937: /*end of variables defined for Bug 2920954*/
938:
939: l_resource_list_member_id CONSTANT pa_fp_elements.RESOURCE_LIST_MEMBER_ID%TYPE := 0;
940: l_task_planning_level_top CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP;
941: l_task_planning_level_low CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE
942: := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST;
943: l_res_planned_for_task CONSTANT pa_fp_elements.RESOURCES_PLANNED_FOR_TASK%TYPE := 'N';
944: l_plan_amt_exists_flag CONSTANT pa_fp_elements.PLAN_AMOUNT_EXISTS_FLAG%TYPE := 'N';
937: /*end of variables defined for Bug 2920954*/
938:
939: l_resource_list_member_id CONSTANT pa_fp_elements.RESOURCE_LIST_MEMBER_ID%TYPE := 0;
940: l_task_planning_level_top CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP;
941: l_task_planning_level_low CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE
942: := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST;
943: l_res_planned_for_task CONSTANT pa_fp_elements.RESOURCES_PLANNED_FOR_TASK%TYPE := 'N';
944: l_plan_amt_exists_flag CONSTANT pa_fp_elements.PLAN_AMOUNT_EXISTS_FLAG%TYPE := 'N';
945:
939: l_resource_list_member_id CONSTANT pa_fp_elements.RESOURCE_LIST_MEMBER_ID%TYPE := 0;
940: l_task_planning_level_top CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP;
941: l_task_planning_level_low CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE
942: := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST;
943: l_res_planned_for_task CONSTANT pa_fp_elements.RESOURCES_PLANNED_FOR_TASK%TYPE := 'N';
944: l_plan_amt_exists_flag CONSTANT pa_fp_elements.PLAN_AMOUNT_EXISTS_FLAG%TYPE := 'N';
945:
946: /* Bug 2586647*/
947: l_res_list_is_uncategorized PA_RESOURCE_LISTS_ALL_BG.UNCATEGORIZED_FLAG%TYPE;
940: l_task_planning_level_top CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP;
941: l_task_planning_level_low CONSTANT pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE
942: := PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST;
943: l_res_planned_for_task CONSTANT pa_fp_elements.RESOURCES_PLANNED_FOR_TASK%TYPE := 'N';
944: l_plan_amt_exists_flag CONSTANT pa_fp_elements.PLAN_AMOUNT_EXISTS_FLAG%TYPE := 'N';
945:
946: /* Bug 2586647*/
947: l_res_list_is_uncategorized PA_RESOURCE_LISTS_ALL_BG.UNCATEGORIZED_FLAG%TYPE;
948: l_is_resource_list_grouped VARCHAR2(1);
959: pa_tasks.TASK_ID%TYPE INDEX BY BINARY_INTEGER;
960: TYPE l_top_task_id_tbl_typ IS TABLE OF
961: pa_tasks.TOP_TASK_ID%TYPE INDEX BY BINARY_INTEGER;
962: TYPE l_top_plan_level_tbl_typ IS TABLE OF
963: PA_FP_ELEMENTS.TOP_TASK_PLANNING_LEVEL%TYPE INDEX BY BINARY_INTEGER;
964: TYPE l_plannable_flag_tbl_typ IS TABLE OF
965: PA_FP_ELEMENTS.PLANNABLE_FLAG%TYPE INDEX BY BINARY_INTEGER;
966:
967: l_task_id_tbl l_task_id_tbl_typ ;
961: pa_tasks.TOP_TASK_ID%TYPE INDEX BY BINARY_INTEGER;
962: TYPE l_top_plan_level_tbl_typ IS TABLE OF
963: PA_FP_ELEMENTS.TOP_TASK_PLANNING_LEVEL%TYPE INDEX BY BINARY_INTEGER;
964: TYPE l_plannable_flag_tbl_typ IS TABLE OF
965: PA_FP_ELEMENTS.PLANNABLE_FLAG%TYPE INDEX BY BINARY_INTEGER;
966:
967: l_task_id_tbl l_task_id_tbl_typ ;
968: l_top_task_id_tbl l_top_task_id_tbl_typ ;
969: l_top_plan_level_tbl l_top_plan_level_tbl_typ;
967: l_task_id_tbl l_task_id_tbl_typ ;
968: l_top_task_id_tbl l_top_task_id_tbl_typ ;
969: l_top_plan_level_tbl l_top_plan_level_tbl_typ;
970: l_plannable_flag_tbl l_plannable_flag_tbl_typ;
971: l_dummy_task_id_tbl pa_fp_elements_pub.l_task_id_tbl_typ;
972:
973: /* Cursor for Top and Lowest Tasks */
974: /* M24-08: Modified this cursor as it was previously inserting top and lowest task with plannable
975: flag as 'N'
1014: AND task_id = top_task_id;
1015:
1016: BEGIN
1017:
1018: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Insert_Default');
1019: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1020: l_debug_mode := NVL(l_debug_mode, 'Y');
1021: IF P_PA_DEBUG_MODE = 'Y' THEN
1022: pa_debug.set_process('Insert_Default: ' || 'PLSQL','LOG',l_debug_mode);
1178:
1179: l_stage := 400;
1180:
1181:
1182: /* The values that are inserted into the table PA_FP_ELEMENTS depending on the the planning level.
1183: The values of the columns task_id, top_task_id, top_task_planning_level, plannable_flag to be
1184: inserted into the table would depend on the Planning Level. */
1185:
1186: --
1183: The values of the columns task_id, top_task_id, top_task_planning_level, plannable_flag to be
1184: inserted into the table would depend on the Planning Level. */
1185:
1186: --
1187: -- References to PA_FP_ELEMENTS table have been commented as records are no longer inserted in it
1188: --Comment START.
1189:
1190: /*
1191: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Bulk Inserting records into PA_FP_Elements';
1187: -- References to PA_FP_ELEMENTS table have been commented as records are no longer inserted in it
1188: --Comment START.
1189:
1190: /*
1191: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Bulk Inserting records into PA_FP_Elements';
1192: IF P_PA_DEBUG_MODE = 'Y' THEN
1193: pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
1194: END IF;
1195: IF l_planning_level IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_LOWEST,PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_M)
1226: pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
1227: END IF;
1228:
1229: FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
1230: -- Bulk Insert records into PA_FP_ELEMENTS table for the details fetched
1231: -- from the above tables.
1232: INSERT INTO pa_fp_elements
1233: (PROJ_FP_ELEMENTS_ID
1234: ,PROJ_FP_OPTIONS_ID
1228:
1229: FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
1230: -- Bulk Insert records into PA_FP_ELEMENTS table for the details fetched
1231: -- from the above tables.
1232: INSERT INTO pa_fp_elements
1233: (PROJ_FP_ELEMENTS_ID
1234: ,PROJ_FP_OPTIONS_ID
1235: ,PROJECT_ID
1236: ,FIN_PLAN_TYPE_ID
1252: ,CREATION_DATE
1253: ,CREATED_BY
1254: ,LAST_UPDATE_LOGIN)
1255: VALUES
1256: (pa_fp_elements_s.nextval
1257: ,p_proj_fp_options_id
1258: ,l_project_id
1259: ,l_fin_plan_type_id
1260: ,p_element_type
1311: pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
1312: END IF;
1313:
1314: FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
1315: -- Bulk Insert records into PA_FP_ELEMENTS table for the details fetched
1316: -- from the above tables.
1317: INSERT INTO pa_fp_elements
1318: (PROJ_FP_ELEMENTS_ID
1319: ,PROJ_FP_OPTIONS_ID
1313:
1314: FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
1315: -- Bulk Insert records into PA_FP_ELEMENTS table for the details fetched
1316: -- from the above tables.
1317: INSERT INTO pa_fp_elements
1318: (PROJ_FP_ELEMENTS_ID
1319: ,PROJ_FP_OPTIONS_ID
1320: ,PROJECT_ID
1321: ,FIN_PLAN_TYPE_ID
1337: ,CREATION_DATE
1338: ,CREATED_BY
1339: ,LAST_UPDATE_LOGIN)
1340: VALUES
1341: (pa_fp_elements_s.nextval
1342: ,p_proj_fp_options_id
1343: ,l_project_id
1344: ,l_fin_plan_type_id
1345: ,p_element_type
1371: ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN -- Planning Level is Project
1372:
1373: l_stage := 700;
1374:
1375: -- No records will be inserted into pa_fp_elements if the Planning Level is 'Project'
1376:
1377: pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Nothing to be done for Planning Level at PROJECT';
1378: IF P_PA_DEBUG_MODE = 'Y' THEN
1379: pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
1398: pa_debug.g_err_stage := TO_CHAR(l_stage)||'Calling add_resources_automatically';
1399: pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
1400: END IF;
1401:
1402: PA_FP_ELEMENTS_PUB.Add_resources_automatically
1403: ( p_proj_fp_options_id => p_proj_fp_options_id
1404: ,p_element_type => p_element_type
1405: ,p_fin_plan_level_code => l_planning_level
1406: ,p_resource_list_id => l_resource_list_id
1441: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442: x_msg_count := 1;
1443: x_msg_data := SQLERRM;
1444: FND_MSG_PUB.add_exc_msg
1445: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Insert_Default'
1446: ,p_procedure_name => pa_debug.G_Err_Stack );
1447: IF P_PA_DEBUG_MODE = 'Y' THEN
1448: pa_debug.write('Insert_Default: ' || l_module_name,SQLERRM,4);
1449: pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.G_Err_Stack,4);
1453: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1454: END Insert_Default;
1455:
1456: /*==================================================================================================
1457: DELETE_ELEMENTS: This procedure is used to delete records from PA_FP_ELEMENTS table for a
1458: particular Proj FP Options ID depending on the Element Type and the Element Level.
1459: - If element_type is BOTH, delete both the cost and revenue planning elements.
1460: - If the element_level is 'TASK', then delete all the task elements and corresponding resources.
1461: - If the element_level is resource, delete on the resources for all the task elements
1479: l_debug_mode VARCHAR2(30);
1480:
1481: BEGIN
1482:
1483: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Delete_Elements');
1484: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1485: l_debug_mode := NVL(l_debug_mode, 'Y');
1486: IF P_PA_DEBUG_MODE = 'Y' THEN
1487: pa_debug.set_process('Delete_Elements: ' || 'PLSQL','LOG',l_debug_mode);
1488: END IF;
1489:
1490: x_return_status := FND_API.G_RET_STS_SUCCESS;
1491:
1492: /* Delete the records from the table PA_FP_Elements based on the Element_Type and
1493: the Element_Level. If the Element_Type is 'BOTH' then both the COST and
1494: REVENUE Planning Elements have to be deleted. */
1495:
1496: pa_debug.g_err_stage := 'Deleting Elements from PA_FP_Elements';
1492: /* Delete the records from the table PA_FP_Elements based on the Element_Type and
1493: the Element_Level. If the Element_Type is 'BOTH' then both the COST and
1494: REVENUE Planning Elements have to be deleted. */
1495:
1496: pa_debug.g_err_stage := 'Deleting Elements from PA_FP_Elements';
1497: IF P_PA_DEBUG_MODE = 'Y' THEN
1498: pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
1499: END IF;
1500:
1506: IF P_PA_DEBUG_MODE = 'Y' THEN
1507: pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
1508: END IF;
1509:
1510: DELETE FROM pa_fp_elements
1511: WHERE proj_fp_options_id = p_proj_fp_options_id
1512: AND element_type = decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_element_type)
1513: AND p_element_level = PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK ;
1514:
1528: IF P_PA_DEBUG_MODE = 'Y' THEN
1529: pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
1530: END IF;
1531:
1532: DELETE FROM pa_fp_elements
1533: WHERE proj_fp_options_id = p_proj_fp_options_id
1534: AND element_type = decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_element_type)
1535: AND p_element_level = PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_RESOURCE
1536: AND resource_list_member_id <> 0;
1547: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1548: x_msg_count := 1;
1549: x_msg_data := SQLERRM;
1550: FND_MSG_PUB.add_exc_msg
1551: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Delete_Elements'
1552: ,p_procedure_name => pa_debug.G_Err_Stack );
1553: IF P_PA_DEBUG_MODE = 'Y' THEN
1554: pa_debug.write('Delete_Elements: ' || l_module_name,SQLERRM,4);
1555: pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.G_Err_Stack,4);
1560: END Delete_Elements;
1561:
1562:
1563: /*==================================================================================================
1564: DELETE_ELEMENT: This procedure is used to delete records from PA_FP_ELEMENTS table for a
1565: particular task_id and resource_list_member_id.
1566: If resource_list_member_id is populated then only resource level element will be deleted.
1567: Else if task_id is lowest task and its top task does not have any other tasks then the
1568: input task_id as well as its top task will be deleted.
1598: l_row_update_count NUMBER;
1599:
1600: BEGIN
1601:
1602: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Delete_Element');
1603: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1604: l_debug_mode := NVL(l_debug_mode, 'Y');
1605: IF P_PA_DEBUG_MODE = 'Y' THEN
1606: pa_debug.set_process('Delete_Element: ' || 'PLSQL','LOG',l_debug_mode);
1631:
1632: IF (p_resource_list_member_id = l_uncat_res_list_mem_id) THEN
1633:
1634: /* If its an uncategorized resource then task level record needs to be deleted.
1635: task level records in pa_fp_elements always have resource list member id as zero. */
1636:
1637: pa_debug.g_err_stage := 'Deleting Elements for the task';
1638: IF P_PA_DEBUG_MODE = 'Y' THEN
1639: pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
1638: IF P_PA_DEBUG_MODE = 'Y' THEN
1639: pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
1640: END IF;
1641:
1642: DELETE FROM pa_fp_elements
1643: WHERE proj_fp_options_id = l_proj_fp_options_id
1644: AND task_id = p_task_id
1645: AND resource_list_member_id = 0
1646: RETURNING top_task_id into l_top_task_id; --Bug 2774779
1650: --as this record would have been deleted already.
1651:
1652: IF l_top_task_id <> p_task_id THEN
1653:
1654: update pa_fp_elements
1655: set plan_amount_exists_flag = 'N',
1656: record_version_number = record_version_number + 1,
1657: last_update_date = sysdate,
1658: last_updated_by = FND_GLOBAL.USER_ID,
1661: and task_id = l_top_task_id
1662: and not exists
1663: (
1664: select 1
1665: from pa_fp_elements
1666: where top_task_id = l_top_task_id
1667: and task_id <> l_top_task_id
1668: and proj_fp_options_id = l_proj_fp_options_id
1669: and nvl(plan_amount_exists_flag,'N') = 'Y'
1684: IF P_PA_DEBUG_MODE = 'Y' THEN
1685: pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
1686: END IF;
1687:
1688: DELETE FROM pa_fp_elements
1689: WHERE proj_fp_options_id = l_proj_fp_options_id -- included for Bug 3062798
1690: AND fin_plan_version_id = p_budget_version_id
1691: AND task_id = p_task_id
1692: AND resource_list_member_id = p_resource_list_member_id
1696: -- Maintain the plan amount exists flag for the top task and lowest task elements.
1697: -- The following update also takes care of the situation where resource are planned for the
1698: -- Top Task and not the lowest task.
1699:
1700: update pa_fp_elements
1701: set plan_amount_exists_flag = 'N',
1702: record_version_number = record_version_number + 1,
1703: last_update_date = sysdate,
1704: last_updated_by = FND_GLOBAL.USER_ID,
1708: and task_id = p_task_id
1709: and not exists
1710: (
1711: select 1
1712: from pa_fp_elements
1713: where task_id = p_task_id
1714: and proj_fp_options_id = l_proj_fp_options_id
1715: and resource_list_member_id <> 0
1716: and nvl(plan_amount_exists_flag,'N') = 'Y'
1724:
1725:
1726: IF p_task_id <> l_top_task_id and l_row_update_count > 0 then
1727:
1728: update pa_fp_elements
1729: set plan_amount_exists_flag = 'N',
1730: record_version_number = record_version_number + 1,
1731: last_update_date = sysdate,
1732: last_updated_by = FND_GLOBAL.USER_ID,
1736: and task_id = l_top_task_id
1737: and not exists
1738: (
1739: select 1
1740: from pa_fp_elements
1741: where top_task_id = l_top_task_id
1742: and proj_fp_options_id = l_proj_fp_options_id
1743: and resource_list_member_id <> 0
1744: and nvl(plan_amount_exists_flag,'N') = 'Y'
1766: SELECT 'Y'
1767: INTO l_resource_exists_flag
1768: FROM dual
1769: WHERE exists (select 1
1770: from pa_fp_elements fp
1771: where proj_fp_options_id = l_proj_fp_options_id
1772: and fp.task_id = p_task_id
1773: and fp.resource_list_member_id <> 0);
1774: EXCEPTION
1782: IF P_PA_DEBUG_MODE = 'Y' THEN
1783: pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
1784: END IF;
1785:
1786: UPDATE pa_fp_elements
1787: SET resources_planned_for_task = 'N',
1788: record_version_number = record_version_number + 1,
1789: last_update_date = sysdate,
1790: last_updated_by = FND_GLOBAL.USER_ID,
1807: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1808: x_msg_count := 1;
1809: x_msg_data := SQLERRM;
1810: FND_MSG_PUB.add_exc_msg
1811: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Delete_Element'
1812: ,p_procedure_name => pa_debug.G_Err_Stack );
1813: IF P_PA_DEBUG_MODE = 'Y' THEN
1814: pa_debug.write('Delete_Element: ' || l_module_name,SQLERRM,4);
1815: pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.G_Err_Stack,4);
1821: END Delete_Element;
1822:
1823:
1824: /*==================================================================================================
1825: This procedure inserts records into PA_FP_ELEMENTS in BULK
1826: ==================================================================================================*/
1827:
1828:
1829: PROCEDURE Insert_Bulk_Rows (
1869: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
1870: END IF;
1871:
1872: /*
1873: * Bulk Insert records into PA_FP_ELEMENTS table for the records fetched
1874: * from cursor top_task_cur.
1875: */
1876: pa_debug.g_err_stage := TO_CHAR(l_stage)||': In Insert_Bulk_Rows';
1877: IF P_PA_DEBUG_MODE = 'Y' THEN
1877: IF P_PA_DEBUG_MODE = 'Y' THEN
1878: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
1879: END IF;
1880:
1881: pa_debug.g_err_stage := TO_CHAR(l_stage)||': Bulk inserting into PA_FP_ELEMENTS';
1882: IF P_PA_DEBUG_MODE = 'Y' THEN
1883: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
1884: END IF;
1885:
1884: END IF;
1885:
1886: FORALL i in p_top_task_id_tbl.first..p_top_task_id_tbl.last
1887:
1888: INSERT INTO pa_fp_elements
1889: (PROJ_FP_ELEMENTS_ID
1890: ,PROJ_FP_OPTIONS_ID
1891: ,PROJECT_ID
1892: ,FIN_PLAN_TYPE_ID
1908: ,CREATION_DATE
1909: ,CREATED_BY
1910: ,LAST_UPDATE_LOGIN)
1911: VALUES
1912: (pa_fp_elements_s.nextval
1913: ,p_proj_fp_options_id
1914: ,p_project_id
1915: ,p_fin_plan_type_id
1916: ,p_element_type
1938: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1939: x_msg_count := 1;
1940: x_msg_data := SQLERRM;
1941: FND_MSG_PUB.add_exc_msg
1942: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Insert_Bulk_Rows'
1943: ,p_procedure_name => pa_debug.G_Err_Stack );
1944: IF P_PA_DEBUG_MODE = 'Y' THEN
1945: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,SQLERRM,4);
1946: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.G_Err_Stack,4);
1991: END IF;
1992:
1993:
1994: /*
1995: * Bulk Insert records into PA_FP_ELEMENTS table for the records fetched
1996: * from cursor top_task_cur.
1997: */
1998: pa_debug.g_err_stage := TO_CHAR(l_stage)||': In Insert_Bulk_Rows_Res';
1999: IF P_PA_DEBUG_MODE = 'Y' THEN
2045: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2046: x_msg_count := 1;
2047: x_msg_data := SQLERRM;
2048: FND_MSG_PUB.add_exc_msg
2049: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Insert_Bulk_Rows_Res'
2050: ,p_procedure_name => pa_debug.G_Err_Stack );
2051: IF P_PA_DEBUG_MODE = 'Y' THEN
2052: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,SQLERRM,4);
2053: pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.G_Err_Stack,4);
2059: END Insert_Bulk_Rows_Res;
2060:
2061:
2062: /*============================================================================
2063: This api makes use of PA_FP_ELEMENTS and enters only user_entered records
2064: if they aren't already present in PA_RESOURCE_ASSIGNMENTS. Uncategorised
2065: resource_list has been dealt separately, as in this case, we can avoid a
2066: table join with pa_resource_list_members.It also deals the case when
2067: planning level is project and resource_list is uncategorised, in which
2064: if they aren't already present in PA_RESOURCE_ASSIGNMENTS. Uncategorised
2065: resource_list has been dealt separately, as in this case, we can avoid a
2066: table join with pa_resource_list_members.It also deals the case when
2067: planning level is project and resource_list is uncategorised, in which
2068: case the given version doesn't have a record in PA_FP_ELEMENTS.
2069:
2070: Bug 2920954 - To create ras for a particular task and control deletion
2071: of resource assignments, p_task_id and p_res_del_req_flag parameters have
2072: been introduced.
2127: SELECT fp.task_id --task_id
2128: ,l_uncat_rlmid --resource_list_member_id
2129: ,l_track_as_labor_flag
2130: ,l_unit_of_measure /* Modified for bug #2586307. */
2131: FROM pa_fp_elements fp
2132: WHERE proj_fp_options_id = l_proj_fp_options_id /* included for bug 3062798*/
2133: AND fin_plan_version_id = p_plan_version_id
2134: AND plannable_flag = 'Y'
2135: AND fp.task_id = Nvl(p_task_id,fp.task_id) /* Bug 2920954 */
2146: ,prlm.track_as_labor_flag
2147: ,decode(prlm.track_as_labor_flag,'Y',PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
2148: 'N',decode(pr.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,NULL,pr.unit_of_measure)
2149: ) unit_of_measure /* Modified for bug #2586307 */
2150: FROM pa_fp_elements fp, pa_resource_list_members prlm, pa_resources pr
2151: WHERE proj_fp_options_id = l_proj_fp_options_id /* included for bug 3062798*/
2152: AND fin_plan_version_id = p_plan_version_id
2153: AND fp.resource_list_member_id <> 0 -- select only resource level records
2154: AND fp.plannable_flag = 'Y' --resource is plannable
2169: FROM pa_resource_assignments pra
2170: WHERE pra.budget_version_id = p_plan_version_id
2171: AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
2172: AND NOT EXISTS (SELECT 1
2173: FROM pa_fp_elements fpe
2174: WHERE proj_fp_options_id = l_proj_fp_options_id /* included for bug 3062798*/
2175: AND fpe.fin_plan_version_id = p_plan_version_id
2176: AND fpe.task_id = pra.task_id
2177: AND fpe.resource_list_member_id = decode(pra.resource_list_member_id,l_uncat_rlmid,
2180: BEGIN
2181:
2182: x_msg_count := 0;
2183: x_return_status := FND_API.G_RET_STS_SUCCESS;
2184: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Create_Enterable_Resources');
2185: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2186: l_debug_mode := NVL(l_debug_mode, 'Y');
2187: IF P_PA_DEBUG_MODE = 'Y' THEN
2188: pa_debug.set_process('create_enterable_resources: ' || 'PLSQL','LOG',l_debug_mode);
2224: FROM pa_budget_versions
2225: WHERE budget_version_id = p_plan_version_id;
2226:
2227: -- 3062798 fetch options_id and use in the cursors to
2228: -- avoid full table scan on pa_fp_elements
2229:
2230: SELECT proj_fp_options_id
2231: INTO l_proj_fp_options_id
2232: FROM pa_proj_fp_options
2297: l_fp_level_code := pa_fin_plan_utils.get_fin_plan_level_code(
2298: p_fin_plan_version_id => p_plan_version_id);
2299:
2300: /* #2615837: If the resources have been unchecked in the pages, then the elements are
2301: deleted from pa_fp_elements but not from resource_assignments. These records have
2302: to be deleted from pa_resource_assignments also else they will be once again
2303: available in the Edit Plan page. */
2304:
2305: /* Should NOT be done when planning level is project and resource list is uncategorized
2334:
2335: ELSE
2336:
2337: /* In all other cases, records have to be deleted from pa_resource_assignments
2338: which do not exist in pa_fp_elements. */
2339:
2340: pa_debug.g_err_stage:= 'fetching resource assignments that should be deleted';
2341: IF P_PA_DEBUG_MODE = 'Y' THEN
2342: pa_debug.write('create_enterable_resources: ' || l_module_name,pa_debug.g_err_stage,3);
2578:
2579: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2580: x_msg_count := 1;
2581: x_msg_data := SQLERRM;
2582: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
2583: ,p_procedure_name => 'CREATE_ENTERABLE_RESOURCES');
2584: pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
2585: IF P_PA_DEBUG_MODE = 'Y' THEN
2586: pa_debug.write('create_enterable_resources: ' || l_module_name,pa_debug.g_err_stage,5);
2595: is already available in database.
2596: ==================================================================================================*/
2597: FUNCTION get_element_id (
2598: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2599: ,p_element_type IN pa_fp_elements.element_type%TYPE
2600: ,p_task_id IN pa_tasks.task_id%TYPE
2601: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2602: RETURN pa_fp_elements.proj_fp_elements_id%TYPE
2603: IS
2598: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2599: ,p_element_type IN pa_fp_elements.element_type%TYPE
2600: ,p_task_id IN pa_tasks.task_id%TYPE
2601: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2602: RETURN pa_fp_elements.proj_fp_elements_id%TYPE
2603: IS
2604:
2605: l_proj_fp_elements_id pa_fp_elements.proj_fp_elements_id%TYPE := -99;
2606: BEGIN
2601: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2602: RETURN pa_fp_elements.proj_fp_elements_id%TYPE
2603: IS
2604:
2605: l_proj_fp_elements_id pa_fp_elements.proj_fp_elements_id%TYPE := -99;
2606: BEGIN
2607:
2608: SELECT proj_fp_elements_id
2609: INTO l_proj_fp_elements_id
2606: BEGIN
2607:
2608: SELECT proj_fp_elements_id
2609: INTO l_proj_fp_elements_id
2610: FROM pa_fp_elements fpe
2611: WHERE fpe.proj_fp_options_id = p_proj_fp_options_id
2612: AND fpe.element_type = p_element_type
2613: AND fpe.task_id = p_task_id
2614: AND fpe.resource_list_member_id = p_resource_list_member_id;
2628: case an element is already available in database.
2629: ==================================================================================================*/
2630: FUNCTION get_element_plannable_flag (
2631: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2632: ,p_element_type IN pa_fp_elements.element_type%TYPE
2633: ,p_task_id IN pa_tasks.task_id%TYPE
2634: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2635: RETURN pa_fp_elements.plannable_flag%TYPE
2636: IS
2631: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2632: ,p_element_type IN pa_fp_elements.element_type%TYPE
2633: ,p_task_id IN pa_tasks.task_id%TYPE
2634: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2635: RETURN pa_fp_elements.plannable_flag%TYPE
2636: IS
2637:
2638: l_plannable_flag pa_fp_elements.plannable_flag%TYPE := 'N';
2639: BEGIN
2634: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2635: RETURN pa_fp_elements.plannable_flag%TYPE
2636: IS
2637:
2638: l_plannable_flag pa_fp_elements.plannable_flag%TYPE := 'N';
2639: BEGIN
2640:
2641: SELECT plannable_flag
2642: INTO l_plannable_flag
2639: BEGIN
2640:
2641: SELECT plannable_flag
2642: INTO l_plannable_flag
2643: FROM pa_fp_elements fpe
2644: WHERE fpe.proj_fp_options_id = p_proj_fp_options_id
2645: AND fpe.element_type = p_element_type
2646: AND fpe.task_id = p_task_id
2647: AND fpe.resource_list_member_id = p_resource_list_member_id;
2661: in case an elementis already available in database.
2662: ==================================================================================================*/
2663: FUNCTION get_plan_amount_exists_flag (
2664: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2665: ,p_element_type IN pa_fp_elements.element_type%TYPE
2666: ,p_task_id IN pa_tasks.task_id%TYPE
2667: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2668: RETURN pa_fp_elements.plan_amount_exists_flag%TYPE
2669: IS
2664: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2665: ,p_element_type IN pa_fp_elements.element_type%TYPE
2666: ,p_task_id IN pa_tasks.task_id%TYPE
2667: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2668: RETURN pa_fp_elements.plan_amount_exists_flag%TYPE
2669: IS
2670:
2671: l_plan_amount_exists_flag pa_fp_elements.plan_amount_exists_flag%TYPE := 'N';
2672: BEGIN
2667: ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
2668: RETURN pa_fp_elements.plan_amount_exists_flag%TYPE
2669: IS
2670:
2671: l_plan_amount_exists_flag pa_fp_elements.plan_amount_exists_flag%TYPE := 'N';
2672: BEGIN
2673:
2674: SELECT plan_amount_exists_flag
2675: INTO l_plan_amount_exists_flag
2672: BEGIN
2673:
2674: SELECT plan_amount_exists_flag
2675: INTO l_plan_amount_exists_flag
2676: FROM pa_fp_elements fpe
2677: WHERE fpe.proj_fp_options_id = p_proj_fp_options_id
2678: AND fpe.element_type = p_element_type
2679: AND fpe.task_id = p_task_id
2680: AND fpe.resource_list_member_id = p_resource_list_member_id;
2695: FUNCTION get_resource_planning_level(
2696: p_parent_member_id IN pa_resource_list_members.parent_member_id%TYPE
2697: ,p_uncategorized_flag IN pa_resource_lists.uncategorized_flag%TYPE
2698: ,p_grouped_flag IN VARCHAR2)
2699: RETURN pa_fp_elements.resource_planning_level%TYPE IS
2700:
2701: l_resource_planning_level pa_fp_elements.resource_planning_level%TYPE;
2702:
2703: BEGIN
2697: ,p_uncategorized_flag IN pa_resource_lists.uncategorized_flag%TYPE
2698: ,p_grouped_flag IN VARCHAR2)
2699: RETURN pa_fp_elements.resource_planning_level%TYPE IS
2700:
2701: l_resource_planning_level pa_fp_elements.resource_planning_level%TYPE;
2702:
2703: BEGIN
2704: IF p_uncategorized_flag = 'N' THEN
2705: IF p_grouped_flag = 'Y' THEN
2747: for the current budget version.
2748: ===================================================================*/
2749: PROCEDURE Create_elements_from_version(
2750: p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2751: ,p_element_type IN pa_fp_elements.element_type%TYPE
2752: ,p_from_version_id IN pa_budget_versions.budget_version_id%TYPE
2753: ,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE
2754: ,x_mixed_resource_planned_flag OUT NOCOPY VARCHAR2 -- new parameter for Bug :- 2625872 --File.Sql.39 bug 4440895
2755: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2795: ---- variables added for Bug :- 2625872 ----
2796:
2797: CURSOR fp_options_cur(
2798: c_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
2799: ,c_element_type IN pa_fp_elements.element_type%TYPE) IS
2800: SELECT project_id
2801: ,fin_plan_type_id
2802: ,fin_plan_version_id
2803: ,PA_FIN_PLAN_UTILS.GET_OPTION_PLANNING_LEVEL(c_proj_fp_options_id,c_element_type) fin_plan_level_code
2825: WHERE budget_version_id = c_from_version_id
2826: AND NVL(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) =
2827: PA_FP_CONSTANTS_PKG.G_USER_ENTERED
2828: AND prlm.resource_list_member_id = pra.resource_list_member_id
2829: AND NOT EXISTS(select 'x' from pa_fp_elements e
2830: where e.proj_fp_options_id = p_proj_fp_options_id
2831: and e.element_Type = p_element_Type
2832: and e.task_id = 0
2833: and e.resource_list_member_id = pra.resource_list_member_id);
2853: AND pt.task_id = pra.task_id
2854: AND NVL(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) =
2855: PA_FP_CONSTANTS_PKG.G_USER_ENTERED
2856: AND prlm.resource_list_member_id = pra.resource_list_member_id
2857: AND NOT EXISTS(select 'x' from pa_fp_elements e
2858: where e.proj_fp_options_id = p_proj_fp_options_id
2859: and e.element_Type = p_element_Type
2860: and e.task_id = pra.task_id
2861: and e.resource_list_member_id = pra.resource_list_member_id)
2920: WHERE budget_version_id = c_from_version_id
2921: AND pt.task_id = pra.task_id
2922: AND prlm.resource_list_member_id = pra.resource_list_member_id
2923: AND NVL(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
2924: AND NOT EXISTS(select 'x' from pa_fp_elements e
2925: where e.proj_fp_options_id = p_proj_fp_options_id
2926: and e.element_Type = p_element_Type
2927: and e.task_id = pra.task_id
2928: and e.resource_list_member_id = 0);
2941: ,NULL resource_planning_level
2942: ,'N' plannable_flag
2943: ,NULL resources_planned_for_task
2944: ,'Y' plan_amount_exists_flag
2945: FROM pa_fp_elements pfe
2946: ,pa_tasks pt
2947: WHERE pfe.proj_fp_options_id = c_proj_fp_options_id
2948: AND pfe.element_type = p_element_type
2949: AND pt.task_id = pfe.task_id
2948: AND pfe.element_type = p_element_type
2949: AND pt.task_id = pfe.task_id
2950: AND pt.top_task_id <> pfe.task_id
2951: AND NOT EXISTS (SELECT 'x' -- not exists clause added for bug#2803724
2952: FROM pa_fp_elements e
2953: WHERE e.proj_fp_options_id = p_proj_fp_options_id
2954: AND e.element_Type = p_element_Type
2955: AND e.task_id = pt.top_task_id
2956: AND e.resource_list_member_id = 0 );
2958: -- The following procedure calls insert bulk rows api
2959:
2960: PROCEDURE Call_Insert_Bulk_Rows_Elements IS
2961: BEGIN
2962: PA_FP_ELEMENTS_PUB.Insert_Bulk_Rows (
2963: p_proj_fp_options_id => p_proj_fp_options_id
2964: ,p_project_id => fp_options_rec.project_id
2965: ,p_fin_plan_type_id => fp_options_rec.fin_plan_type_id
2966: ,p_element_type => p_element_type
3066: IF l_uncategorized_flag = 'N' THEN
3067:
3068: -- Case:- project level planning and categorised resource list
3069:
3070: --Fetch and insert resource level records into pa_fp_elements.
3071:
3072: IF p_pa_debug_mode = 'Y' THEN
3073: pa_debug.g_err_stage := 'Opening resources_for_proj_level_cur';
3074: pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3132:
3133: IF l_uncategorized_flag = 'N' THEN
3134:
3135: --CASE :- task level planning and categorised resource list
3136: --Fetch and insert resource level records into pa_fp_elements.
3137:
3138: IF p_pa_debug_mode = 'Y' THEN
3139: pa_debug.g_err_stage := 'Opening resources_for_task_level_cur';
3140: pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3200: pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3201: END IF;
3202: END IF;
3203:
3204: --Fetch and insert task level records into pa_fp_elements
3205:
3206: IF p_pa_debug_mode = 'Y' THEN
3207: pa_debug.g_err_stage := 'Opening task_level_elements_cur';
3208: pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3341: RAISE;
3342: END Create_elements_from_version;
3343:
3344: /*==================================================================================================
3345: refresh_res_list_changes: This procedure is used to delete resource elements from PA_FP_ELEMENTS
3346: table for a particular Proj FP Options ID depending on the Element Type when the resource list is
3347: changed in the plan settings page. After deleting the resource records, it sets the
3348: resource planning level for the task records to 'R' if the resource list is categorized or to NULL
3349: if it is not categorized
3352: parameter and resource planning level for automatic resource selection
3353: ==================================================================================================*/
3354: PROCEDURE refresh_res_list_changes (
3355: p_proj_fp_options_id IN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE
3356: ,p_element_type IN PA_FP_ELEMENTS.ELEMENT_TYPE%TYPE /* COST,REVENUE,ALL,BOTH */
3357: ,p_cost_resource_list_id IN PA_PROJ_FP_OPTIONS.COST_RESOURCE_LIST_ID%TYPE
3358: ,p_rev_resource_list_id IN PA_PROJ_FP_OPTIONS.REVENUE_RESOURCE_LIST_ID%TYPE
3359: ,p_all_resource_list_id IN PA_PROJ_FP_OPTIONS.ALL_RESOURCE_LIST_ID%TYPE
3360: /* Bug 2920954 start of new parameters added for post fp-K one off patch */
3378: l_res_list_is_uncategorized PA_RESOURCE_LISTS_ALL_BG.UNCATEGORIZED_FLAG%TYPE;
3379: l_is_resource_list_grouped VARCHAR2(1);
3380: l_group_resource_type_id PA_RESOURCE_LISTS_ALL_BG.GROUP_RESOURCE_TYPE_ID%TYPE;
3381: l_resource_list_id PA_PROJ_FP_OPTIONS.ALL_RESOURCE_LIST_ID%TYPE;
3382: l_res_planning_level PA_FP_ELEMENTS.RESOURCE_PLANNING_LEVEL%TYPE;
3383: l_fin_plan_level_code PA_PROJ_FP_OPTIONS.COST_FIN_PLAN_LEVEL_CODE%TYPE;
3384: l_stage NUMBER := 100;
3385:
3386: l_dummy_task_id_tbl pa_fp_elements_pub.l_task_id_tbl_typ;
3382: l_res_planning_level PA_FP_ELEMENTS.RESOURCE_PLANNING_LEVEL%TYPE;
3383: l_fin_plan_level_code PA_PROJ_FP_OPTIONS.COST_FIN_PLAN_LEVEL_CODE%TYPE;
3384: l_stage NUMBER := 100;
3385:
3386: l_dummy_task_id_tbl pa_fp_elements_pub.l_task_id_tbl_typ;
3387:
3388: BEGIN
3389:
3390: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.refresh_res_list_changes');
3386: l_dummy_task_id_tbl pa_fp_elements_pub.l_task_id_tbl_typ;
3387:
3388: BEGIN
3389:
3390: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.refresh_res_list_changes');
3391: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3392: l_debug_mode := NVL(l_debug_mode, 'Y');
3393: IF P_PA_DEBUG_MODE = 'Y' THEN
3394: pa_debug.set_process('refresh_res_list_changes: ' || 'PLSQL','LOG',l_debug_mode);
3422: END IF;
3423:
3424:
3425:
3426: /* Delete the records from the table PA_FP_Elements based on the Element_Type and
3427: for Element level RESOURCE. */
3428:
3429: pa_debug.g_err_stage := 'Deleting Elements from PA_FP_Elements';
3430: IF P_PA_DEBUG_MODE = 'Y' THEN
3425:
3426: /* Delete the records from the table PA_FP_Elements based on the Element_Type and
3427: for Element level RESOURCE. */
3428:
3429: pa_debug.g_err_stage := 'Deleting Elements from PA_FP_Elements';
3430: IF P_PA_DEBUG_MODE = 'Y' THEN
3431: pa_debug.write('refresh_res_list_changes: ' || l_module_name,pa_debug.g_err_stage,3);
3432: END IF;
3433:
3484: P_ELEMENT_TYPE => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST);
3485:
3486: /* If automatic resource addition is enabled, call add_resources_automatically api for entire option */
3487:
3488: PA_FP_ELEMENTS_PUB.Add_resources_automatically
3489: ( p_proj_fp_options_id => p_proj_fp_options_id
3490: ,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
3491: ,p_fin_plan_level_code => l_fin_plan_level_code
3492: ,p_resource_list_id => p_cost_resource_list_id
3523: /*Added the condition resources_planned_for_task = 'N' for bug 2676456 so that
3524: the resource plannned for task column in planning elements page shows correct value
3525: */
3526:
3527: UPDATE pa_fp_elements
3528: SET resource_planning_level = l_res_planning_level
3529: ,resources_planned_for_task = 'N'
3530: ,record_version_number = record_version_number + 1
3531: ,last_update_date = sysdate
3577: P_ELEMENT_TYPE => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE);
3578:
3579: /* If automatic resource addition is enabled, call add_resources_automatically api for entire option */
3580:
3581: PA_FP_ELEMENTS_PUB.Add_resources_automatically
3582: ( p_proj_fp_options_id => p_proj_fp_options_id
3583: ,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
3584: ,p_fin_plan_level_code => l_fin_plan_level_code
3585: ,p_resource_list_id => p_rev_resource_list_id
3610: IF P_PA_DEBUG_MODE = 'Y' THEN
3611: pa_debug.write('refresh_res_list_changes: ' || l_module_name,pa_debug.g_err_stage,3);
3612: END IF;
3613:
3614: UPDATE pa_fp_elements
3615: SET resource_planning_level = l_res_planning_level
3616: ,resources_planned_for_task = 'N' --for bug 2676456
3617: ,record_version_number = record_version_number + 1
3618: ,last_update_date = sysdate
3664: P_ELEMENT_TYPE => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL);
3665:
3666: /* If automatic resource addition is enabled, call add_resources_automatically api for entire option */
3667:
3668: PA_FP_ELEMENTS_PUB.Add_resources_automatically
3669: ( p_proj_fp_options_id => p_proj_fp_options_id
3670: ,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
3671: ,p_fin_plan_level_code => l_fin_plan_level_code
3672: ,p_resource_list_id => p_all_resource_list_id
3697: IF P_PA_DEBUG_MODE = 'Y' THEN
3698: pa_debug.write('refresh_res_list_changes: ' || l_module_name,pa_debug.g_err_stage,3);
3699: END IF;
3700:
3701: UPDATE pa_fp_elements
3702: SET resource_planning_level = l_res_planning_level
3703: ,resources_planned_for_task = 'N' --for bug 2676456
3704: ,record_version_number = record_version_number + 1
3705: ,last_update_date = sysdate
3735: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3736: x_msg_count := 1;
3737: x_msg_data := SQLERRM;
3738: FND_MSG_PUB.add_exc_msg
3739: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB.refresh_res_list_changes'
3740: ,p_procedure_name => pa_debug.G_Err_Stack );
3741: IF P_PA_DEBUG_MODE = 'Y' THEN
3742: pa_debug.write('refresh_res_list_changes: ' || l_module_name,SQLERRM,4);
3743: pa_debug.write('refresh_res_list_changes: ' || l_module_name,pa_debug.G_Err_Stack,4);
3790: /* Bug 2677597 - declaration change end */
3791:
3792: l_project_id pa_projects_all.project_id%TYPE;
3793: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3794: l_element_type pa_fp_elements.element_type%TYPE;
3795: l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
3796: l_mixed_resource_planned_flag VARCHAR2(1); -- Added for Bug:- 2625872
3797:
3798: --Bug # 3507156 : Patchset M: B and F impact changes : AMG
3854: BEGIN
3855:
3856: x_msg_count := 0;
3857: x_return_status := FND_API.G_RET_STS_SUCCESS;
3858: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.CREATE_ASSGMT_FROM_ROLLUPTMP');
3859: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3860: l_debug_mode := NVL(l_debug_mode, 'Y');
3861: IF P_PA_DEBUG_MODE = 'Y' THEN
3862: pa_debug.set_process('CREATE_ASSGMT_FROM_ROLLUPTMP: ' || 'PLSQL','LOG',l_debug_mode);
4025:
4026: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4027: x_msg_count := 1;
4028: x_msg_data := SQLERRM;
4029: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
4030: ,p_procedure_name => 'CREATE_ASSGMT_FROM_ROLLUPTMP');
4031: pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4032: IF P_PA_DEBUG_MODE = 'Y' THEN
4033: pa_debug.write('CREATE_ASSGMT_FROM_ROLLUPTMP: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4215: BEGIN
4216:
4217: x_msg_count := 0;
4218: x_return_status := FND_API.G_RET_STS_SUCCESS;
4219: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Create_CI_Resource_Assignments');
4220: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4221: l_debug_mode := NVL(l_debug_mode, 'Y');
4222: IF P_PA_DEBUG_MODE = 'Y' THEN
4223: pa_debug.set_process('Create_CI_Resource_Assignments: ' || 'PLSQL','LOG',l_debug_mode);
4509:
4510: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4511: x_msg_count := 1;
4512: x_msg_data := SQLERRM;
4513: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
4514: ,p_procedure_name => 'Create_CI_Resource_Assignments');
4515: pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4516: IF P_PA_DEBUG_MODE = 'Y' THEN
4517: pa_debug.write('Create_CI_Resource_Assignments: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4532:
4533: NOTE(S):-
4534: 1. If the option planning level is project, the task_id tbl should
4535: contain one and only one record and that should be zero as we
4536: enter 0(zero) for task_id column in pa_fp_elements for project
4537: level planning options.
4538: ==================================================================*/
4539:
4540: PROCEDURE Add_resources_automatically
4538: ==================================================================*/
4539:
4540: PROCEDURE Add_resources_automatically
4541: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
4542: ,p_element_type IN pa_fp_elements.element_type%TYPE
4543: ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
4544: ,p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE
4545: ,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
4546: ,p_entire_option IN VARCHAR2
4543: ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
4544: ,p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE
4545: ,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
4546: ,p_entire_option IN VARCHAR2
4547: ,p_element_task_id_tbl IN pa_fp_elements_pub.l_task_id_tbl_typ
4548: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4549: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4550: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4551: AS
4579: based on the element type etc., for an option_id
4580: */
4581: CURSOR proj_fp_options_info_cur
4582: ( c_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE
4583: ,c_element_type pa_fp_elements.element_type%TYPE)
4584: IS
4585: SELECT project_id
4586: ,fin_plan_type_id
4587: ,fin_plan_version_id
4596: resource addition in the context of entire option
4597: */
4598: CURSOR all_plannable_tasks_cur
4599: ( c_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE
4600: ,c_element_type pa_fp_elements.element_type%TYPE)
4601: IS
4602: SELECT task_id
4603: ,top_task_id
4604: FROM pa_fp_elements
4600: ,c_element_type pa_fp_elements.element_type%TYPE)
4601: IS
4602: SELECT task_id
4603: ,top_task_id
4604: FROM pa_fp_elements
4605: WHERE proj_fp_options_id = c_proj_fp_options_id
4606: AND element_type = c_element_type
4607: AND resource_list_member_id = 0
4608: AND plannable_flag = 'Y';
4799:
4800: BEGIN
4801: SELECT top_task_id
4802: INTO l_top_task_id_tbl(l_task_tbl_index)
4803: FROM pa_fp_elements
4804: WHERE proj_fp_options_id = p_proj_fp_options_id
4805: AND element_type = p_element_type
4806: AND task_id = l_task_id_tbl(l_task_tbl_index)
4807: AND resource_list_member_id = 0
4901: END IF;
4902:
4903: /*
4904: For each task_id in the task_id table we need to insert
4905: all the resource_list_memebers fetched in pa_fp_elements table.
4906: */
4907:
4908: IF p_pa_debug_mode = 'Y' THEN
4909: pa_debug.g_err_stage:= 'for each task in task_id_tbl inserting all the rlmids fetched';
4915:
4916: /* Insert all the resource_list_members fetched for each task */
4917:
4918: FORALL l_rlm_tbl_index IN l_res_list_mem_id_tbl.first .. l_res_list_mem_id_tbl.last
4919: INSERT INTO pa_fp_elements
4920: (PROJ_FP_ELEMENTS_ID
4921: ,PROJ_FP_OPTIONS_ID
4922: ,PROJECT_ID
4923: ,FIN_PLAN_TYPE_ID
4939: ,CREATION_DATE
4940: ,CREATED_BY
4941: ,LAST_UPDATE_LOGIN)
4942: VALUES
4943: (pa_fp_elements_s.nextval
4944: ,p_proj_fp_options_id
4945: ,proj_fp_options_info_rec.project_id
4946: ,proj_fp_options_info_rec.fin_plan_type_id
4947: ,p_element_type
4973:
4974: IF l_res_list_mem_id_tbl.count > 0
4975: THEN
4976: FORALL l_task_tbl_index IN l_task_id_tbl.first .. l_task_id_tbl.last
4977: UPDATE pa_fp_elements
4978: SET resources_planned_for_task = 'Y'
4979: ,resource_planning_level = p_res_planning_level
4980: ,record_version_number = record_version_number + 1
4981: ,last_update_date = sysdate
5018: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5019: x_msg_count := 1;
5020: x_msg_data := SQLERRM;
5021: FND_MSG_PUB.add_exc_msg
5022: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
5023: ,p_procedure_name => 'ADD_RESOURCES_AUTOMATICALLY'
5024: ,p_error_text => sqlerrm);
5025: pa_debug.reset_curr_function;
5026: RAISE;
5026: RAISE;
5027: END Add_resources_automatically;
5028:
5029: /* Bug 2920954 - This procedure deletes all the planning elements
5030: (pa_fp_elements/pa_resource_assignments) of this task and all
5031: its child tasks. This is called during the task deletion. These
5032: tasks would have plannable plan_amount_exists_flag as 'N'. Its
5033: assumed that the check apis would have been called to ensure
5034: that deletion of p_task_id is allowed. One main check in the check api
5142: x_msg_count := 1;
5143: x_msg_data := SQLERRM;
5144:
5145: FND_MSG_PUB.add_exc_msg
5146: ( p_pkg_name => 'pa_Fp_elements_pub'
5147: ,p_procedure_name => 'delete_task_elements'
5148: ,p_error_text => x_msg_data);
5149:
5150: IF l_debug_mode = 'Y' THEN
5155: RAISE;
5156: END delete_task_elements;
5157: /*
5158: For bug 2976168.
5159: This API is called from pa_fp_elements_pub.make_new_tasks_plannable api for an option and element_type.
5160: This API will be used to decide whether to insert a task in fp elements table or not. This api will also
5161: provide the plannable flag and task planning level of all the tasks that are eligible for insertion.
5162: */
5163: PROCEDURE Get_Task_Element_Attributes
5161: provide the plannable flag and task planning level of all the tasks that are eligible for insertion.
5162: */
5163: PROCEDURE Get_Task_Element_Attributes
5164: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5165: ,p_element_type IN pa_fp_elements.element_type%TYPE
5166: ,p_task_id IN pa_fp_elements.task_id%TYPE
5167: ,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
5168: ,p_task_level IN VARCHAR2
5169: ,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
5162: */
5163: PROCEDURE Get_Task_Element_Attributes
5164: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5165: ,p_element_type IN pa_fp_elements.element_type%TYPE
5166: ,p_task_id IN pa_fp_elements.task_id%TYPE
5167: ,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
5168: ,p_task_level IN VARCHAR2
5169: ,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
5170: ,x_task_inclusion_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5163: PROCEDURE Get_Task_Element_Attributes
5164: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5165: ,p_element_type IN pa_fp_elements.element_type%TYPE
5166: ,p_task_id IN pa_fp_elements.task_id%TYPE
5167: ,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
5168: ,p_task_level IN VARCHAR2
5169: ,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
5170: ,x_task_inclusion_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5171: ,x_task_plannable_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5198: Other Number - Sql Error */
5199:
5200: --Cursors required for this procedure
5201:
5202: --This cursor is used to know whether a task already exists in pa_fp_elements or not
5203: CURSOR task_element_info_cur (
5204: c_task_id pa_fp_elements.task_id%TYPE)
5205: IS
5206: SELECT pfe.top_task_planning_level,
5200: --Cursors required for this procedure
5201:
5202: --This cursor is used to know whether a task already exists in pa_fp_elements or not
5203: CURSOR task_element_info_cur (
5204: c_task_id pa_fp_elements.task_id%TYPE)
5205: IS
5206: SELECT pfe.top_task_planning_level,
5207: pfe.plannable_flag
5208: FROM pa_fp_elements pfe
5204: c_task_id pa_fp_elements.task_id%TYPE)
5205: IS
5206: SELECT pfe.top_task_planning_level,
5207: pfe.plannable_flag
5208: FROM pa_fp_elements pfe
5209: WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
5210: AND pfe.element_type = p_element_type
5211: AND pfe.task_id = c_task_id
5212: AND pfe.resource_list_member_id = 0;
5275: END IF;
5276:
5277: --Check if the task is already included as a plannable element.
5278: IF l_debug_mode = 'Y' THEN
5279: pa_debug.g_err_stage:= 'Check if task is already plannable(existence in pa_fp_elements)';
5280: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5281: END IF;
5282:
5283: -- If p_task_id already exists in pa_fp_elements for the option_id and element_type, no further
5279: pa_debug.g_err_stage:= 'Check if task is already plannable(existence in pa_fp_elements)';
5280: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5281: END IF;
5282:
5283: -- If p_task_id already exists in pa_fp_elements for the option_id and element_type, no further
5284: -- processing is required.
5285:
5286: OPEN task_element_info_cur(p_task_id);
5287: FETCH task_element_info_cur INTO task_element_info_rec;
5287: FETCH task_element_info_cur INTO task_element_info_rec;
5288: IF task_element_info_cur%NOTFOUND THEN
5289: l_continue_processing := 'Y';
5290: IF l_debug_mode = 'Y' THEN
5291: pa_debug.g_err_stage:= 'task doesnt exists in pa_fp_elements. Proceeding further..';
5292: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5293: END IF;
5294: ELSE
5295: l_continue_processing := 'N';
5294: ELSE
5295: l_continue_processing := 'N';
5296: x_task_inclusion_flag := 'N';
5297: IF l_debug_mode = 'Y' THEN
5298: pa_debug.g_err_stage:= 'task is already plannable and exists in pa_fp_elements. No processing required...';
5299: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5300: END IF;
5301: END IF;
5302: CLOSE task_element_info_cur;
5367:
5368: IF l_child_task_exists = 1 THEN /* Child task exsists */
5369:
5370: /* This is a TOP task which is being created only now. But the planning
5371: level is L/M. Since we always have top task records in pa_fp_elements,
5372: this top task should be inserted in pa_fp_elements with plannable flag
5373: as N. Resource elements should not be added for this top task */
5374:
5375: x_task_inclusion_flag := 'Y';
5368: IF l_child_task_exists = 1 THEN /* Child task exsists */
5369:
5370: /* This is a TOP task which is being created only now. But the planning
5371: level is L/M. Since we always have top task records in pa_fp_elements,
5372: this top task should be inserted in pa_fp_elements with plannable flag
5373: as N. Resource elements should not be added for this top task */
5374:
5375: x_task_inclusion_flag := 'Y';
5376: x_task_plannable_flag := 'N' ;
5434:
5435: /* Note that we dont expect this case to happen since our assumption is that
5436: the top task record would be first called to be made plannable and then the
5437: lowest task. If we need to handle this case, we have to first insert the
5438: p_top_task_id record into pa_fp_elements and then the p_task_id record. */
5439:
5440: x_task_inclusion_flag := 'N';
5441:
5442: CLOSE task_element_info_cur;
5441:
5442: CLOSE task_element_info_cur;
5443:
5444: IF l_debug_mode = 'Y' THEN
5445: pa_debug.g_err_stage:= 'Top Task not found in pa_fp_elements and hence x_task_inclusion_flag ' || x_task_inclusion_flag;
5446: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5447: END IF;
5448:
5449: ELSE
5524: x_msg_count := 1;
5525: x_msg_data := SQLERRM;
5526:
5527: FND_MSG_PUB.add_exc_msg
5528: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
5529: ,p_procedure_name => 'Get_Task_Element_Attributes'
5530: ,p_error_text => x_msg_data);
5531:
5532: IF l_debug_mode = 'Y' THEN
5552: is an impacted task or a child task of impacted task */
5553:
5554: PROCEDURE add_tasks_to_option
5555: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5556: ,p_element_type IN pa_fp_elements.element_type%TYPE
5557: ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
5558: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5559: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5560: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5553:
5554: PROCEDURE add_tasks_to_option
5555: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5556: ,p_element_type IN pa_fp_elements.element_type%TYPE
5557: ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
5558: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5559: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5560: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5561: AS
5599:
5600: proj_fp_options_rec proj_fp_options_cur%ROWTYPE;
5601:
5602:
5603: l_task_id_tbl pa_fp_elements_pub.l_task_id_tbl_typ;
5604:
5605: l_task_plannable_flag VARCHAR2(1); /* represents the plannable of the task to
5606: be inserted*/
5607:
5606: be inserted*/
5607:
5608: --For Bug 2976168.
5609: l_task_inclusion_flag VARCHAR2(1); /*Required to know whether the task can be is
5610: eligible for inserting into pa_fp_elements or not*/
5611: CURSOR ci_version_info_cur
5612: (c_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE)
5613: IS
5614: SELECT bv.ci_id,
5632: ci_impacted_tasks_rec ci_impacted_tasks_cur%ROWTYPE;
5633:
5634: l_continue_processing VARCHAR2(1);
5635: l_ci_impacted_tasks_tbl PA_PLSQL_DATATYPES.NumTabTyp;
5636: l_top_task_planning_level pa_fp_elements.top_task_planning_level%TYPE;
5637: L_PROCEDURE_NAME CONSTANT VARCHAR2(100):='add_task_to_option :'||l_module_name;
5638:
5639: BEGIN
5640: x_msg_count := 0;
5787:
5788: /* If the parent task is a top task,
5789:
5790: 1. We need to remove only the resources elements attached
5791: to the parent task from pa_fp_elements.
5792:
5793: 2. Since we always have the top task record of a plannable
5794: task in pa_fp_elements, we shouldnt delete the top task
5795: record. We just have to set the plannable flag
5790: 1. We need to remove only the resources elements attached
5791: to the parent task from pa_fp_elements.
5792:
5793: 2. Since we always have the top task record of a plannable
5794: task in pa_fp_elements, we shouldnt delete the top task
5795: record. We just have to set the plannable flag
5796: of this task to N. */
5797:
5798: DELETE pa_fp_elements pfe
5794: task in pa_fp_elements, we shouldnt delete the top task
5795: record. We just have to set the plannable flag
5796: of this task to N. */
5797:
5798: DELETE pa_fp_elements pfe
5799: WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
5800: AND pfe.element_type = p_element_type
5801: AND pfe.task_id = p_tasks_tbl(i).parent_task_id
5802: AND pfe.resource_list_member_id <> 0;
5801: AND pfe.task_id = p_tasks_tbl(i).parent_task_id
5802: AND pfe.resource_list_member_id <> 0;
5803:
5804: IF l_debug_mode = 'Y' THEN
5805: pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records deleted from pa_fp_elements';
5806: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5807: END IF;
5808:
5809: UPDATE pa_fp_elements pfe
5805: pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records deleted from pa_fp_elements';
5806: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5807: END IF;
5808:
5809: UPDATE pa_fp_elements pfe
5810: SET pfe.plannable_flag = 'N',
5811: pfe.tmp_plannable_flag = 'N',
5812: pfe.resources_planned_for_task = Null,
5813: pfe.record_version_number = pfe.record_version_number + 1,
5819: AND pfe.task_id = p_tasks_tbl(i).parent_task_id
5820: AND pfe.resource_list_member_id = 0;
5821:
5822: IF l_debug_mode = 'Y' THEN
5823: pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records updated in pa_fp_elements';
5824: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5825: END IF;
5826:
5827: IF proj_fp_options_rec.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION THEN
5848: Null;
5849:
5850: END IF;
5851:
5852: INSERT INTO PA_FP_ELEMENTS
5853: (PROJ_FP_ELEMENTS_ID
5854: ,PROJ_FP_OPTIONS_ID
5855: ,PROJECT_ID
5856: ,FIN_PLAN_TYPE_ID
5872: ,CREATION_DATE
5873: ,CREATED_BY
5874: ,LAST_UPDATE_LOGIN)
5875: VALUES
5876: (pa_fp_elements_s.nextval
5877: ,p_proj_fp_options_id
5878: ,proj_fp_options_rec.project_id
5879: ,proj_fp_options_rec.fin_plan_type_id
5880: ,p_element_type
5914: END IF;
5915:
5916: l_task_id_tbl(1) := p_tasks_tbl(i).task_id;
5917:
5918: PA_FP_ELEMENTS_PUB.ADD_RESOURCES_AUTOMATICALLY
5919: ( p_proj_fp_options_id => p_proj_fp_options_id
5920: ,p_element_type => p_element_type
5921: ,p_fin_plan_level_code => proj_fp_options_rec.fin_plan_level_code
5922: ,p_resource_list_id => proj_fp_options_rec.resource_list_id
5957: pa_debug.g_err_stage:= 'Calling create_enterable_resources...';
5958: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5959: END IF;
5960:
5961: PA_FP_ELEMENTS_PUB.create_enterable_resources
5962: ( p_plan_version_id => proj_fp_options_rec.fin_plan_version_id
5963: ,p_res_del_req_flag => 'N' /* Since deletion of resource assignments has already been done in this flow */
5964: ,x_return_status => x_return_status
5965: ,x_msg_count => x_msg_count
6013: x_msg_count := 1;
6014: x_msg_data := SQLERRM;
6015:
6016: FND_MSG_PUB.add_exc_msg
6017: ( p_pkg_name => 'pa_fp_elements_pub'
6018: ,p_procedure_name => 'add_task_to_option'
6019: ,p_error_text => x_msg_data);
6020:
6021: IF l_debug_mode = 'Y' THEN
6036: pa_fin_plan_maint_ver_global.resubmit_concurrent_request */
6037:
6038: PROCEDURE make_new_tasks_plannable
6039: ( p_project_id IN pa_projects_all.project_id%TYPE
6040: ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
6041: ,p_refresh_fp_options_tbl IN PA_PLSQL_DATATYPES.IdTabTyp
6042: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6043: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
6044: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
6127:
6128: FOR i IN p_tasks_tbl.first .. p_tasks_tbl.last
6129: LOOP
6130: /* If the task is a middle level task delete all the references of that task from
6131: pa_fp_elements and pa_resource_assignments */
6132:
6133: IF l_debug_mode = 'Y' THEN
6134: pa_debug.g_err_stage:= 'task_id ' || p_tasks_tbl(i).task_id;
6135: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6138: END IF;
6139:
6140: IF p_tasks_tbl(i).task_level = L_TASK_LEVEL_MIDDLE THEN
6141:
6142: --Delete the task references from pa_fp_elements
6143: FORALL k IN p_refresh_fp_options_tbl.first .. p_refresh_fp_options_tbl.last
6144: DELETE
6145: FROM pa_fp_elements
6146: WHERE task_id = p_tasks_tbl(i).task_id
6141:
6142: --Delete the task references from pa_fp_elements
6143: FORALL k IN p_refresh_fp_options_tbl.first .. p_refresh_fp_options_tbl.last
6144: DELETE
6145: FROM pa_fp_elements
6146: WHERE task_id = p_tasks_tbl(i).task_id
6147: AND proj_fp_options_id = p_refresh_fp_options_tbl(k); /* We are deleting irrespective of element_type */
6148:
6149: IF l_debug_mode = 'Y' THEN
6146: WHERE task_id = p_tasks_tbl(i).task_id
6147: AND proj_fp_options_id = p_refresh_fp_options_tbl(k); /* We are deleting irrespective of element_type */
6148:
6149: IF l_debug_mode = 'Y' THEN
6150: pa_debug.g_err_stage:= 'No of records deleted from pa_fp_elements ' ||SQL%ROWCOUNT;
6151: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6152: END IF;
6153:
6154: --Delete the task references from pa_resource_assignments
6324: x_msg_count := 1;
6325: x_msg_data := SQLERRM;
6326:
6327: FND_MSG_PUB.add_exc_msg
6328: ( p_pkg_name => 'pa_fp_elements_pub'
6329: ,p_procedure_name => 'make_new_tasks_plannable'
6330: ,p_error_text => x_msg_data);
6331:
6332: IF l_debug_mode = 'Y' THEN
6510:
6511:
6512: IF l_middle_task_tbl.exists(p_task_id) THEN
6513:
6514: /* Middle level task and this need NOT be inserted into pa_fp_elements */
6515:
6516: return 'N';
6517:
6518: ELSIF p_top_task_id = p_task_id THEN
6761: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6762:
6763: END IF;
6764:
6765: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for impacted task id */
6766:
6767: /* For Bug 2976168. Modified the call to make new task plannable api.
6768:
6769: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6765: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for impacted task id */
6766:
6767: /* For Bug 2976168. Modified the call to make new task plannable api.
6768:
6769: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6770: ( p_project_id => p_project_id
6771: ,p_task_id => p_impacted_tasks_tbl(i).impacted_task_id
6772: ,x_return_status => x_return_status
6773: ,x_msg_count => x_msg_count
6783: l_wbs_refresh_tasks_tbl(1).parent_task_id := task_info_rec.parent_task_id;
6784: l_wbs_refresh_tasks_tbl(1).top_task_id := task_info_rec.top_task_id;
6785: l_wbs_refresh_tasks_tbl(1).task_level := task_info_rec.task_level;
6786:
6787: PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
6788: ( p_project_id => p_project_id
6789: ,p_tasks_tbl => l_wbs_refresh_tasks_tbl
6790: ,p_refresh_fp_options_tbl => l_all_fp_options_tbl
6791: ,x_return_status => x_return_status
6867: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6868:
6869: END IF;
6870:
6871: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for old parent task id */
6872:
6873: /* Bug 2976168. Changed the way make_new_task_plannable is called
6874:
6875: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6871: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for old parent task id */
6872:
6873: /* Bug 2976168. Changed the way make_new_task_plannable is called
6874:
6875: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6876: ( p_project_id => p_project_id
6877: ,p_task_id => p_impacted_tasks_tbl(i).old_parent_task_id
6878: ,x_return_status => x_return_status
6879: ,x_msg_count => x_msg_count
6888: l_wbs_refresh_tasks_tbl(1).parent_task_id := task_info_rec.parent_task_id;
6889: l_wbs_refresh_tasks_tbl(1).top_task_id := task_info_rec.top_task_id;
6890: l_wbs_refresh_tasks_tbl(1).task_level := task_info_rec.task_level;
6891:
6892: PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
6893: ( p_project_id => p_project_id
6894: ,p_tasks_tbl => l_wbs_refresh_tasks_tbl
6895: ,p_refresh_fp_options_tbl => l_all_fp_options_tbl
6896: ,x_return_status => x_return_status
6930:
6931: IF pa_task_utils.get_top_task_id(x_task_id => p_impacted_tasks_tbl(i).new_parent_task_id)
6932: <> p_impacted_tasks_tbl(i).new_parent_task_id THEN
6933:
6934: /* Check if it exists in pa_fp_elements. If yes, then its a lowest task */
6935:
6936: IF pa_fin_plan_utils.check_task_in_fp_option(p_task_id => p_impacted_tasks_tbl(i).new_parent_task_id) = 'Y' THEN
6937: /* Delete planning elements and resource assignments for new parent task id.
6938: Pls note that delete task elements deletes the task and its children from
6936: IF pa_fin_plan_utils.check_task_in_fp_option(p_task_id => p_impacted_tasks_tbl(i).new_parent_task_id) = 'Y' THEN
6937: /* Delete planning elements and resource assignments for new parent task id.
6938: Pls note that delete task elements deletes the task and its children from
6939: all plan options. Hence we cannot call it since it might delete a
6940: plannable impacted task also from pa_fp_elements and pa_resource_assignments */
6941:
6942: IF l_debug_mode = 'Y' THEN
6943:
6944: pa_debug.g_err_stage:= 'Deleting task fp elements for new parent task id';
6945: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6946:
6947: END IF;
6948:
6949: DELETE FROM pa_fp_elements e
6950: WHERE e.task_id = p_impacted_tasks_tbl(i).new_parent_task_id;
6951:
6952: l_records_deleted := sql%rowcount;
6953:
7019: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
7020:
7021: END IF;
7022:
7023: /* Calling pa_fp_elements_pub.delete_task_elements for impacted task id */
7024: PA_FP_ELEMENTS_PUB.Delete_task_elements
7025: ( p_task_id => p_impacted_Tasks_tbl(i).impacted_task_id
7026: ,x_return_status => x_return_status
7027: ,x_msg_count => x_msg_count
7020:
7021: END IF;
7022:
7023: /* Calling pa_fp_elements_pub.delete_task_elements for impacted task id */
7024: PA_FP_ELEMENTS_PUB.Delete_task_elements
7025: ( p_task_id => p_impacted_Tasks_tbl(i).impacted_task_id
7026: ,x_return_status => x_return_status
7027: ,x_msg_count => x_msg_count
7028: ,x_msg_data => x_msg_data);
7089:
7090: /*
7091: Bug 2976168. Changed the way make_new_task_plannable is called
7092:
7093: PA_FP_ELEMENTS_PUB.make_new_task_plannable
7094: ( p_project_id => p_project_id
7095: ,p_task_id => p_impacted_tasks_tbl(i).old_parent_task_id
7096: ,x_return_status => x_return_status
7097: ,x_msg_count => x_msg_count
7106: l_wbs_refresh_tasks_tbl(1).parent_task_id := task_info_rec.parent_task_id;
7107: l_wbs_refresh_tasks_tbl(1).top_task_id := task_info_rec.top_task_id;
7108: l_wbs_refresh_tasks_tbl(1).task_level := task_info_rec.task_level;
7109:
7110: PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
7111: ( p_project_id => p_project_id
7112: ,p_tasks_tbl => l_wbs_refresh_tasks_tbl
7113: ,p_refresh_fp_options_tbl => l_all_fp_options_tbl
7114: ,x_return_status => x_return_status
7229: x_msg_count := 1;
7230: x_msg_data := SQLERRM;
7231:
7232: FND_MSG_PUB.add_exc_msg
7233: ( p_pkg_name => 'pa_fp_elements_pub'
7234: ,p_procedure_name => 'maintain_plannable_tasks'
7235: ,p_error_text => x_msg_data);
7236:
7237: IF l_debug_mode = 'Y' THEN
7242: END IF;
7243: RAISE;
7244: END maintain_plannable_tasks;
7245:
7246: End PA_FP_ELEMENTS_PUB;