[Home] [Help]
1: package body PA_FP_ELEMENTS_PUB as
2: /* $Header: PAFPELPB.pls 120.2 2005/09/26 11:30:51 rnamburi noship $ */
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.2 2005/09/26 11:30:51 rnamburi noship $ */
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
3849: BEGIN
3850:
3851: x_msg_count := 0;
3852: x_return_status := FND_API.G_RET_STS_SUCCESS;
3853: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.CREATE_ASSGMT_FROM_ROLLUPTMP');
3854: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3855: l_debug_mode := NVL(l_debug_mode, 'Y');
3856: IF P_PA_DEBUG_MODE = 'Y' THEN
3857: pa_debug.set_process('CREATE_ASSGMT_FROM_ROLLUPTMP: ' || 'PLSQL','LOG',l_debug_mode);
4016:
4017: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4018: x_msg_count := 1;
4019: x_msg_data := SQLERRM;
4020: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
4021: ,p_procedure_name => 'CREATE_ASSGMT_FROM_ROLLUPTMP');
4022: pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4023: IF P_PA_DEBUG_MODE = 'Y' THEN
4024: pa_debug.write('CREATE_ASSGMT_FROM_ROLLUPTMP: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4206: BEGIN
4207:
4208: x_msg_count := 0;
4209: x_return_status := FND_API.G_RET_STS_SUCCESS;
4210: pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Create_CI_Resource_Assignments');
4211: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4212: l_debug_mode := NVL(l_debug_mode, 'Y');
4213: IF P_PA_DEBUG_MODE = 'Y' THEN
4214: pa_debug.set_process('Create_CI_Resource_Assignments: ' || 'PLSQL','LOG',l_debug_mode);
4500:
4501: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4502: x_msg_count := 1;
4503: x_msg_data := SQLERRM;
4504: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
4505: ,p_procedure_name => 'Create_CI_Resource_Assignments');
4506: pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4507: IF P_PA_DEBUG_MODE = 'Y' THEN
4508: pa_debug.write('Create_CI_Resource_Assignments: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4523:
4524: NOTE(S):-
4525: 1. If the option planning level is project, the task_id tbl should
4526: contain one and only one record and that should be zero as we
4527: enter 0(zero) for task_id column in pa_fp_elements for project
4528: level planning options.
4529: ==================================================================*/
4530:
4531: PROCEDURE Add_resources_automatically
4529: ==================================================================*/
4530:
4531: PROCEDURE Add_resources_automatically
4532: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
4533: ,p_element_type IN pa_fp_elements.element_type%TYPE
4534: ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
4535: ,p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE
4536: ,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
4537: ,p_entire_option IN VARCHAR2
4534: ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
4535: ,p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE
4536: ,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
4537: ,p_entire_option IN VARCHAR2
4538: ,p_element_task_id_tbl IN pa_fp_elements_pub.l_task_id_tbl_typ
4539: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4540: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4541: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4542: AS
4570: based on the element type etc., for an option_id
4571: */
4572: CURSOR proj_fp_options_info_cur
4573: ( c_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE
4574: ,c_element_type pa_fp_elements.element_type%TYPE)
4575: IS
4576: SELECT project_id
4577: ,fin_plan_type_id
4578: ,fin_plan_version_id
4587: resource addition in the context of entire option
4588: */
4589: CURSOR all_plannable_tasks_cur
4590: ( c_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE
4591: ,c_element_type pa_fp_elements.element_type%TYPE)
4592: IS
4593: SELECT task_id
4594: ,top_task_id
4595: FROM pa_fp_elements
4591: ,c_element_type pa_fp_elements.element_type%TYPE)
4592: IS
4593: SELECT task_id
4594: ,top_task_id
4595: FROM pa_fp_elements
4596: WHERE proj_fp_options_id = c_proj_fp_options_id
4597: AND element_type = c_element_type
4598: AND resource_list_member_id = 0
4599: AND plannable_flag = 'Y';
4790:
4791: BEGIN
4792: SELECT top_task_id
4793: INTO l_top_task_id_tbl(l_task_tbl_index)
4794: FROM pa_fp_elements
4795: WHERE proj_fp_options_id = p_proj_fp_options_id
4796: AND element_type = p_element_type
4797: AND task_id = l_task_id_tbl(l_task_tbl_index)
4798: AND resource_list_member_id = 0
4892: END IF;
4893:
4894: /*
4895: For each task_id in the task_id table we need to insert
4896: all the resource_list_memebers fetched in pa_fp_elements table.
4897: */
4898:
4899: IF p_pa_debug_mode = 'Y' THEN
4900: pa_debug.g_err_stage:= 'for each task in task_id_tbl inserting all the rlmids fetched';
4906:
4907: /* Insert all the resource_list_members fetched for each task */
4908:
4909: FORALL l_rlm_tbl_index IN l_res_list_mem_id_tbl.first .. l_res_list_mem_id_tbl.last
4910: INSERT INTO pa_fp_elements
4911: (PROJ_FP_ELEMENTS_ID
4912: ,PROJ_FP_OPTIONS_ID
4913: ,PROJECT_ID
4914: ,FIN_PLAN_TYPE_ID
4930: ,CREATION_DATE
4931: ,CREATED_BY
4932: ,LAST_UPDATE_LOGIN)
4933: VALUES
4934: (pa_fp_elements_s.nextval
4935: ,p_proj_fp_options_id
4936: ,proj_fp_options_info_rec.project_id
4937: ,proj_fp_options_info_rec.fin_plan_type_id
4938: ,p_element_type
4964:
4965: IF l_res_list_mem_id_tbl.count > 0
4966: THEN
4967: FORALL l_task_tbl_index IN l_task_id_tbl.first .. l_task_id_tbl.last
4968: UPDATE pa_fp_elements
4969: SET resources_planned_for_task = 'Y'
4970: ,resource_planning_level = p_res_planning_level
4971: ,record_version_number = record_version_number + 1
4972: ,last_update_date = sysdate
5009: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5010: x_msg_count := 1;
5011: x_msg_data := SQLERRM;
5012: FND_MSG_PUB.add_exc_msg
5013: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
5014: ,p_procedure_name => 'ADD_RESOURCES_AUTOMATICALLY'
5015: ,p_error_text => sqlerrm);
5016: pa_debug.reset_curr_function;
5017: RAISE;
5017: RAISE;
5018: END Add_resources_automatically;
5019:
5020: /* Bug 2920954 - This procedure deletes all the planning elements
5021: (pa_fp_elements/pa_resource_assignments) of this task and all
5022: its child tasks. This is called during the task deletion. These
5023: tasks would have plannable plan_amount_exists_flag as 'N'. Its
5024: assumed that the check apis would have been called to ensure
5025: that deletion of p_task_id is allowed. One main check in the check api
5133: x_msg_count := 1;
5134: x_msg_data := SQLERRM;
5135:
5136: FND_MSG_PUB.add_exc_msg
5137: ( p_pkg_name => 'pa_Fp_elements_pub'
5138: ,p_procedure_name => 'delete_task_elements'
5139: ,p_error_text => x_msg_data);
5140:
5141: IF l_debug_mode = 'Y' THEN
5146: RAISE;
5147: END delete_task_elements;
5148: /*
5149: For bug 2976168.
5150: This API is called from pa_fp_elements_pub.make_new_tasks_plannable api for an option and element_type.
5151: This API will be used to decide whether to insert a task in fp elements table or not. This api will also
5152: provide the plannable flag and task planning level of all the tasks that are eligible for insertion.
5153: */
5154: PROCEDURE Get_Task_Element_Attributes
5152: provide the plannable flag and task planning level of all the tasks that are eligible for insertion.
5153: */
5154: PROCEDURE Get_Task_Element_Attributes
5155: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5156: ,p_element_type IN pa_fp_elements.element_type%TYPE
5157: ,p_task_id IN pa_fp_elements.task_id%TYPE
5158: ,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
5159: ,p_task_level IN VARCHAR2
5160: ,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
5153: */
5154: PROCEDURE Get_Task_Element_Attributes
5155: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5156: ,p_element_type IN pa_fp_elements.element_type%TYPE
5157: ,p_task_id IN pa_fp_elements.task_id%TYPE
5158: ,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
5159: ,p_task_level IN VARCHAR2
5160: ,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
5161: ,x_task_inclusion_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5154: PROCEDURE Get_Task_Element_Attributes
5155: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5156: ,p_element_type IN pa_fp_elements.element_type%TYPE
5157: ,p_task_id IN pa_fp_elements.task_id%TYPE
5158: ,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
5159: ,p_task_level IN VARCHAR2
5160: ,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
5161: ,x_task_inclusion_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5162: ,x_task_plannable_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5189: Other Number - Sql Error */
5190:
5191: --Cursors required for this procedure
5192:
5193: --This cursor is used to know whether a task already exists in pa_fp_elements or not
5194: CURSOR task_element_info_cur (
5195: c_task_id pa_fp_elements.task_id%TYPE)
5196: IS
5197: SELECT pfe.top_task_planning_level,
5191: --Cursors required for this procedure
5192:
5193: --This cursor is used to know whether a task already exists in pa_fp_elements or not
5194: CURSOR task_element_info_cur (
5195: c_task_id pa_fp_elements.task_id%TYPE)
5196: IS
5197: SELECT pfe.top_task_planning_level,
5198: pfe.plannable_flag
5199: FROM pa_fp_elements pfe
5195: c_task_id pa_fp_elements.task_id%TYPE)
5196: IS
5197: SELECT pfe.top_task_planning_level,
5198: pfe.plannable_flag
5199: FROM pa_fp_elements pfe
5200: WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
5201: AND pfe.element_type = p_element_type
5202: AND pfe.task_id = c_task_id
5203: AND pfe.resource_list_member_id = 0;
5266: END IF;
5267:
5268: --Check if the task is already included as a plannable element.
5269: IF l_debug_mode = 'Y' THEN
5270: pa_debug.g_err_stage:= 'Check if task is already plannable(existence in pa_fp_elements)';
5271: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5272: END IF;
5273:
5274: -- If p_task_id already exists in pa_fp_elements for the option_id and element_type, no further
5270: pa_debug.g_err_stage:= 'Check if task is already plannable(existence in pa_fp_elements)';
5271: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5272: END IF;
5273:
5274: -- If p_task_id already exists in pa_fp_elements for the option_id and element_type, no further
5275: -- processing is required.
5276:
5277: OPEN task_element_info_cur(p_task_id);
5278: FETCH task_element_info_cur INTO task_element_info_rec;
5278: FETCH task_element_info_cur INTO task_element_info_rec;
5279: IF task_element_info_cur%NOTFOUND THEN
5280: l_continue_processing := 'Y';
5281: IF l_debug_mode = 'Y' THEN
5282: pa_debug.g_err_stage:= 'task doesnt exists in pa_fp_elements. Proceeding further..';
5283: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5284: END IF;
5285: ELSE
5286: l_continue_processing := 'N';
5285: ELSE
5286: l_continue_processing := 'N';
5287: x_task_inclusion_flag := 'N';
5288: IF l_debug_mode = 'Y' THEN
5289: pa_debug.g_err_stage:= 'task is already plannable and exists in pa_fp_elements. No processing required...';
5290: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5291: END IF;
5292: END IF;
5293: CLOSE task_element_info_cur;
5358:
5359: IF l_child_task_exists = 1 THEN /* Child task exsists */
5360:
5361: /* This is a TOP task which is being created only now. But the planning
5362: level is L/M. Since we always have top task records in pa_fp_elements,
5363: this top task should be inserted in pa_fp_elements with plannable flag
5364: as N. Resource elements should not be added for this top task */
5365:
5366: x_task_inclusion_flag := 'Y';
5359: IF l_child_task_exists = 1 THEN /* Child task exsists */
5360:
5361: /* This is a TOP task which is being created only now. But the planning
5362: level is L/M. Since we always have top task records in pa_fp_elements,
5363: this top task should be inserted in pa_fp_elements with plannable flag
5364: as N. Resource elements should not be added for this top task */
5365:
5366: x_task_inclusion_flag := 'Y';
5367: x_task_plannable_flag := 'N' ;
5425:
5426: /* Note that we dont expect this case to happen since our assumption is that
5427: the top task record would be first called to be made plannable and then the
5428: lowest task. If we need to handle this case, we have to first insert the
5429: p_top_task_id record into pa_fp_elements and then the p_task_id record. */
5430:
5431: x_task_inclusion_flag := 'N';
5432:
5433: CLOSE task_element_info_cur;
5432:
5433: CLOSE task_element_info_cur;
5434:
5435: IF l_debug_mode = 'Y' THEN
5436: pa_debug.g_err_stage:= 'Top Task not found in pa_fp_elements and hence x_task_inclusion_flag ' || x_task_inclusion_flag;
5437: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5438: END IF;
5439:
5440: ELSE
5515: x_msg_count := 1;
5516: x_msg_data := SQLERRM;
5517:
5518: FND_MSG_PUB.add_exc_msg
5519: ( p_pkg_name => 'PA_FP_ELEMENTS_PUB'
5520: ,p_procedure_name => 'Get_Task_Element_Attributes'
5521: ,p_error_text => x_msg_data);
5522:
5523: IF l_debug_mode = 'Y' THEN
5543: is an impacted task or a child task of impacted task */
5544:
5545: PROCEDURE add_tasks_to_option
5546: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5547: ,p_element_type IN pa_fp_elements.element_type%TYPE
5548: ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
5549: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5550: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5551: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5544:
5545: PROCEDURE add_tasks_to_option
5546: ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
5547: ,p_element_type IN pa_fp_elements.element_type%TYPE
5548: ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
5549: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5550: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5551: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5552: AS
5590:
5591: proj_fp_options_rec proj_fp_options_cur%ROWTYPE;
5592:
5593:
5594: l_task_id_tbl pa_fp_elements_pub.l_task_id_tbl_typ;
5595:
5596: l_task_plannable_flag VARCHAR2(1); /* represents the plannable of the task to
5597: be inserted*/
5598:
5597: be inserted*/
5598:
5599: --For Bug 2976168.
5600: l_task_inclusion_flag VARCHAR2(1); /*Required to know whether the task can be is
5601: eligible for inserting into pa_fp_elements or not*/
5602: CURSOR ci_version_info_cur
5603: (c_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE)
5604: IS
5605: SELECT bv.ci_id,
5623: ci_impacted_tasks_rec ci_impacted_tasks_cur%ROWTYPE;
5624:
5625: l_continue_processing VARCHAR2(1);
5626: l_ci_impacted_tasks_tbl PA_PLSQL_DATATYPES.NumTabTyp;
5627: l_top_task_planning_level pa_fp_elements.top_task_planning_level%TYPE;
5628: L_PROCEDURE_NAME CONSTANT VARCHAR2(100):='add_task_to_option :'||l_module_name;
5629:
5630: BEGIN
5631: x_msg_count := 0;
5778:
5779: /* If the parent task is a top task,
5780:
5781: 1. We need to remove only the resources elements attached
5782: to the parent task from pa_fp_elements.
5783:
5784: 2. Since we always have the top task record of a plannable
5785: task in pa_fp_elements, we shouldnt delete the top task
5786: record. We just have to set the plannable flag
5781: 1. We need to remove only the resources elements attached
5782: to the parent task from pa_fp_elements.
5783:
5784: 2. Since we always have the top task record of a plannable
5785: task in pa_fp_elements, we shouldnt delete the top task
5786: record. We just have to set the plannable flag
5787: of this task to N. */
5788:
5789: DELETE pa_fp_elements pfe
5785: task in pa_fp_elements, we shouldnt delete the top task
5786: record. We just have to set the plannable flag
5787: of this task to N. */
5788:
5789: DELETE pa_fp_elements pfe
5790: WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
5791: AND pfe.element_type = p_element_type
5792: AND pfe.task_id = p_tasks_tbl(i).parent_task_id
5793: AND pfe.resource_list_member_id <> 0;
5792: AND pfe.task_id = p_tasks_tbl(i).parent_task_id
5793: AND pfe.resource_list_member_id <> 0;
5794:
5795: IF l_debug_mode = 'Y' THEN
5796: pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records deleted from pa_fp_elements';
5797: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5798: END IF;
5799:
5800: UPDATE pa_fp_elements pfe
5796: pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records deleted from pa_fp_elements';
5797: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5798: END IF;
5799:
5800: UPDATE pa_fp_elements pfe
5801: SET pfe.plannable_flag = 'N',
5802: pfe.tmp_plannable_flag = 'N',
5803: pfe.resources_planned_for_task = Null,
5804: pfe.record_version_number = pfe.record_version_number + 1,
5810: AND pfe.task_id = p_tasks_tbl(i).parent_task_id
5811: AND pfe.resource_list_member_id = 0;
5812:
5813: IF l_debug_mode = 'Y' THEN
5814: pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records updated in pa_fp_elements';
5815: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5816: END IF;
5817:
5818: IF proj_fp_options_rec.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION THEN
5839: Null;
5840:
5841: END IF;
5842:
5843: INSERT INTO PA_FP_ELEMENTS
5844: (PROJ_FP_ELEMENTS_ID
5845: ,PROJ_FP_OPTIONS_ID
5846: ,PROJECT_ID
5847: ,FIN_PLAN_TYPE_ID
5863: ,CREATION_DATE
5864: ,CREATED_BY
5865: ,LAST_UPDATE_LOGIN)
5866: VALUES
5867: (pa_fp_elements_s.nextval
5868: ,p_proj_fp_options_id
5869: ,proj_fp_options_rec.project_id
5870: ,proj_fp_options_rec.fin_plan_type_id
5871: ,p_element_type
5905: END IF;
5906:
5907: l_task_id_tbl(1) := p_tasks_tbl(i).task_id;
5908:
5909: PA_FP_ELEMENTS_PUB.ADD_RESOURCES_AUTOMATICALLY
5910: ( p_proj_fp_options_id => p_proj_fp_options_id
5911: ,p_element_type => p_element_type
5912: ,p_fin_plan_level_code => proj_fp_options_rec.fin_plan_level_code
5913: ,p_resource_list_id => proj_fp_options_rec.resource_list_id
5948: pa_debug.g_err_stage:= 'Calling create_enterable_resources...';
5949: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
5950: END IF;
5951:
5952: PA_FP_ELEMENTS_PUB.create_enterable_resources
5953: ( p_plan_version_id => proj_fp_options_rec.fin_plan_version_id
5954: ,p_res_del_req_flag => 'N' /* Since deletion of resource assignments has already been done in this flow */
5955: ,x_return_status => x_return_status
5956: ,x_msg_count => x_msg_count
6004: x_msg_count := 1;
6005: x_msg_data := SQLERRM;
6006:
6007: FND_MSG_PUB.add_exc_msg
6008: ( p_pkg_name => 'pa_fp_elements_pub'
6009: ,p_procedure_name => 'add_task_to_option'
6010: ,p_error_text => x_msg_data);
6011:
6012: IF l_debug_mode = 'Y' THEN
6027: pa_fin_plan_maint_ver_global.resubmit_concurrent_request */
6028:
6029: PROCEDURE make_new_tasks_plannable
6030: ( p_project_id IN pa_projects_all.project_id%TYPE
6031: ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
6032: ,p_refresh_fp_options_tbl IN PA_PLSQL_DATATYPES.IdTabTyp
6033: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6034: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
6035: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
6118:
6119: FOR i IN p_tasks_tbl.first .. p_tasks_tbl.last
6120: LOOP
6121: /* If the task is a middle level task delete all the references of that task from
6122: pa_fp_elements and pa_resource_assignments */
6123:
6124: IF l_debug_mode = 'Y' THEN
6125: pa_debug.g_err_stage:= 'task_id ' || p_tasks_tbl(i).task_id;
6126: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6129: END IF;
6130:
6131: IF p_tasks_tbl(i).task_level = L_TASK_LEVEL_MIDDLE THEN
6132:
6133: --Delete the task references from pa_fp_elements
6134: FORALL k IN p_refresh_fp_options_tbl.first .. p_refresh_fp_options_tbl.last
6135: DELETE
6136: FROM pa_fp_elements
6137: WHERE task_id = p_tasks_tbl(i).task_id
6132:
6133: --Delete the task references from pa_fp_elements
6134: FORALL k IN p_refresh_fp_options_tbl.first .. p_refresh_fp_options_tbl.last
6135: DELETE
6136: FROM pa_fp_elements
6137: WHERE task_id = p_tasks_tbl(i).task_id
6138: AND proj_fp_options_id = p_refresh_fp_options_tbl(k); /* We are deleting irrespective of element_type */
6139:
6140: IF l_debug_mode = 'Y' THEN
6137: WHERE task_id = p_tasks_tbl(i).task_id
6138: AND proj_fp_options_id = p_refresh_fp_options_tbl(k); /* We are deleting irrespective of element_type */
6139:
6140: IF l_debug_mode = 'Y' THEN
6141: pa_debug.g_err_stage:= 'No of records deleted from pa_fp_elements ' ||SQL%ROWCOUNT;
6142: pa_debug.write(L_PROCEDURE_NAME,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6143: END IF;
6144:
6145: --Delete the task references from pa_resource_assignments
6315: x_msg_count := 1;
6316: x_msg_data := SQLERRM;
6317:
6318: FND_MSG_PUB.add_exc_msg
6319: ( p_pkg_name => 'pa_fp_elements_pub'
6320: ,p_procedure_name => 'make_new_tasks_plannable'
6321: ,p_error_text => x_msg_data);
6322:
6323: IF l_debug_mode = 'Y' THEN
6501:
6502:
6503: IF l_middle_task_tbl.exists(p_task_id) THEN
6504:
6505: /* Middle level task and this need NOT be inserted into pa_fp_elements */
6506:
6507: return 'N';
6508:
6509: ELSIF p_top_task_id = p_task_id THEN
6752: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6753:
6754: END IF;
6755:
6756: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for impacted task id */
6757:
6758: /* For Bug 2976168. Modified the call to make new task plannable api.
6759:
6760: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6756: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for impacted task id */
6757:
6758: /* For Bug 2976168. Modified the call to make new task plannable api.
6759:
6760: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6761: ( p_project_id => p_project_id
6762: ,p_task_id => p_impacted_tasks_tbl(i).impacted_task_id
6763: ,x_return_status => x_return_status
6764: ,x_msg_count => x_msg_count
6774: l_wbs_refresh_tasks_tbl(1).parent_task_id := task_info_rec.parent_task_id;
6775: l_wbs_refresh_tasks_tbl(1).top_task_id := task_info_rec.top_task_id;
6776: l_wbs_refresh_tasks_tbl(1).task_level := task_info_rec.task_level;
6777:
6778: PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
6779: ( p_project_id => p_project_id
6780: ,p_tasks_tbl => l_wbs_refresh_tasks_tbl
6781: ,p_refresh_fp_options_tbl => l_all_fp_options_tbl
6782: ,x_return_status => x_return_status
6858: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6859:
6860: END IF;
6861:
6862: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for old parent task id */
6863:
6864: /* Bug 2976168. Changed the way make_new_task_plannable is called
6865:
6866: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6862: /* Calling pa_fp_elements_pub.make_new_tasks_plannable for old parent task id */
6863:
6864: /* Bug 2976168. Changed the way make_new_task_plannable is called
6865:
6866: PA_FP_ELEMENTS_PUB.make_new_task_plannable
6867: ( p_project_id => p_project_id
6868: ,p_task_id => p_impacted_tasks_tbl(i).old_parent_task_id
6869: ,x_return_status => x_return_status
6870: ,x_msg_count => x_msg_count
6879: l_wbs_refresh_tasks_tbl(1).parent_task_id := task_info_rec.parent_task_id;
6880: l_wbs_refresh_tasks_tbl(1).top_task_id := task_info_rec.top_task_id;
6881: l_wbs_refresh_tasks_tbl(1).task_level := task_info_rec.task_level;
6882:
6883: PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
6884: ( p_project_id => p_project_id
6885: ,p_tasks_tbl => l_wbs_refresh_tasks_tbl
6886: ,p_refresh_fp_options_tbl => l_all_fp_options_tbl
6887: ,x_return_status => x_return_status
6921:
6922: IF pa_task_utils.get_top_task_id(x_task_id => p_impacted_tasks_tbl(i).new_parent_task_id)
6923: <> p_impacted_tasks_tbl(i).new_parent_task_id THEN
6924:
6925: /* Check if it exists in pa_fp_elements. If yes, then its a lowest task */
6926:
6927: IF pa_fin_plan_utils.check_task_in_fp_option(p_task_id => p_impacted_tasks_tbl(i).new_parent_task_id) = 'Y' THEN
6928: /* Delete planning elements and resource assignments for new parent task id.
6929: Pls note that delete task elements deletes the task and its children from
6927: IF pa_fin_plan_utils.check_task_in_fp_option(p_task_id => p_impacted_tasks_tbl(i).new_parent_task_id) = 'Y' THEN
6928: /* Delete planning elements and resource assignments for new parent task id.
6929: Pls note that delete task elements deletes the task and its children from
6930: all plan options. Hence we cannot call it since it might delete a
6931: plannable impacted task also from pa_fp_elements and pa_resource_assignments */
6932:
6933: IF l_debug_mode = 'Y' THEN
6934:
6935: pa_debug.g_err_stage:= 'Deleting task fp elements for new parent task id';
6936: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
6937:
6938: END IF;
6939:
6940: DELETE FROM pa_fp_elements e
6941: WHERE e.task_id = p_impacted_tasks_tbl(i).new_parent_task_id;
6942:
6943: l_records_deleted := sql%rowcount;
6944:
7010: pa_debug.write(l_module_name,pa_debug.g_err_stage,L_DEBUG_LEVEL3);
7011:
7012: END IF;
7013:
7014: /* Calling pa_fp_elements_pub.delete_task_elements for impacted task id */
7015: PA_FP_ELEMENTS_PUB.Delete_task_elements
7016: ( p_task_id => p_impacted_Tasks_tbl(i).impacted_task_id
7017: ,x_return_status => x_return_status
7018: ,x_msg_count => x_msg_count
7011:
7012: END IF;
7013:
7014: /* Calling pa_fp_elements_pub.delete_task_elements for impacted task id */
7015: PA_FP_ELEMENTS_PUB.Delete_task_elements
7016: ( p_task_id => p_impacted_Tasks_tbl(i).impacted_task_id
7017: ,x_return_status => x_return_status
7018: ,x_msg_count => x_msg_count
7019: ,x_msg_data => x_msg_data);
7080:
7081: /*
7082: Bug 2976168. Changed the way make_new_task_plannable is called
7083:
7084: PA_FP_ELEMENTS_PUB.make_new_task_plannable
7085: ( p_project_id => p_project_id
7086: ,p_task_id => p_impacted_tasks_tbl(i).old_parent_task_id
7087: ,x_return_status => x_return_status
7088: ,x_msg_count => x_msg_count
7097: l_wbs_refresh_tasks_tbl(1).parent_task_id := task_info_rec.parent_task_id;
7098: l_wbs_refresh_tasks_tbl(1).top_task_id := task_info_rec.top_task_id;
7099: l_wbs_refresh_tasks_tbl(1).task_level := task_info_rec.task_level;
7100:
7101: PA_FP_ELEMENTS_PUB.make_new_tasks_plannable
7102: ( p_project_id => p_project_id
7103: ,p_tasks_tbl => l_wbs_refresh_tasks_tbl
7104: ,p_refresh_fp_options_tbl => l_all_fp_options_tbl
7105: ,x_return_status => x_return_status
7220: x_msg_count := 1;
7221: x_msg_data := SQLERRM;
7222:
7223: FND_MSG_PUB.add_exc_msg
7224: ( p_pkg_name => 'pa_fp_elements_pub'
7225: ,p_procedure_name => 'maintain_plannable_tasks'
7226: ,p_error_text => x_msg_data);
7227:
7228: IF l_debug_mode = 'Y' THEN
7233: END IF;
7234: RAISE;
7235: END maintain_plannable_tasks;
7236:
7237: End PA_FP_ELEMENTS_PUB;