[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete and recreate the FP Elements Records based on the Planning Levels passed to this procedure.
Bug :- 2920954 This is an existing api that has been modified to insert resource elements for the
default task elements based on the automatic resource selection parameter and resource planning
level for automatic resource selection. Currently only the defaul task elements are created based
on the input planning level and resource list id.
==================================================================================================*/
P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
,p_select_cost_res_auto_flag IN pa_proj_fp_options.select_cost_res_auto_flag%TYPE
,p_cost_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
,p_select_rev_res_auto_flag IN pa_proj_fp_options.select_rev_res_auto_flag%TYPE
,p_revenue_res_planning_level IN pa_proj_fp_options.revenue_res_planning_level%TYPE
,p_select_all_res_auto_flag IN pa_proj_fp_options.select_all_res_auto_flag%TYPE
,p_all_res_planning_level IN pa_proj_fp_options.all_res_planning_level%TYPE
/*Bug :- 2920954 end of new parameters added for post fp-K one off patch */
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
l_msg_count NUMBER := 0;
/* 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
fp_elements for the Proj_FP_Options_ID and then call the Insert_Default procedure
to insert into fp_elements. */
pa_debug.g_err_stage := 'Deleting records from pa_fp_elements and calling insert_Default';
pa_debug.g_err_stage := 'Deleting and inserting for Cost Planning Level';
delete_elements(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
,p_element_level => PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
insert_default(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
,p_planning_level => p_cost_planning_level
,p_resource_list_id => p_cost_resource_list_id
,p_select_res_auto_flag => p_select_cost_res_auto_flag /* Bug 2920954*/
,p_res_planning_level => p_cost_res_planning_level /* Bug 2920954*/
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Deleting and inserting for Revenue Planning Level';
delete_elements(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
,p_element_level => PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
insert_default(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
,p_planning_level => p_revenue_planning_level
,p_resource_list_id => p_revenue_resource_list_id
,p_select_res_auto_flag => p_select_rev_res_auto_flag /* Bug 2920954*/
,p_res_planning_level => p_revenue_res_planning_level /* Bug 2920954*/
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := 'Deleting and inserting for All Planning Level';
delete_elements(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
,p_element_level => PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
insert_default(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL
,p_planning_level => p_all_planning_level
,p_resource_list_id => p_all_resource_list_id
,p_select_res_auto_flag => p_select_all_res_auto_flag /* Bug 2920954*/
,p_res_planning_level => p_all_res_planning_level /* Bug 2920954*/
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
Defaults are inserted for the new Proj FP Option.
-> If the Source FP Option is passed, then details are got from the Source FP Option and inserted
for the Target FP Option.
Bug 2920954 :- This is an existing api that has been modified to include the resource selection and
resource planning level parameters to pa_fp_elements_pub.insert_default api. P_copy_mode has been
added as a parameter to this api. If copying elements for baselined version, only the elements with
plan amounts need to copied.
For bug 2976168. Copy the elements from excluded_elements table if the copy mode is not B
and only when the source exists
==================================================================================================*/
PROCEDURE Copy_Elements (
p_from_proj_fp_options_id IN NUMBER
,p_from_element_type IN VARCHAR2
,p_to_proj_fp_options_id IN NUMBER
,p_to_element_type IN VARCHAR2
,p_to_resource_list_id IN NUMBER
,p_copy_mode IN VARCHAR2 /* Bug 2920954 */
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
l_debug_mode VARCHAR2(30);
l_select_res_auto_flag PA_PROJ_FP_OPTIONS.select_cost_res_auto_flag%TYPE;
l_select_cost_res_auto_flag PA_PROJ_FP_OPTIONS.select_cost_res_auto_flag%TYPE;
l_select_rev_res_auto_flag PA_PROJ_FP_OPTIONS.select_rev_res_auto_flag%TYPE;
SELECT pfo_src.fin_plan_preference_code
,pfo_target.fin_plan_preference_code
INTO l_source_preference_code
,l_target_preference_code
FROM PA_PROJ_FP_OPTIONS pfo_src
,PA_PROJ_FP_OPTIONS pfo_target
WHERE pfo_src.proj_fp_options_id = l_from_proj_fp_option_id
AND pfo_target.proj_fp_options_id = p_to_proj_fp_options_id;
/* Parent Proj Option ID not found, so Insert Default */
/* First delete the records from pa_fp_elements and then insert the Default
Values into PA_FP_Elements table. */
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Parent FP Option is null, hence insert_default.';
Delete_Elements(p_proj_fp_options_id => p_to_proj_fp_options_id
,p_element_type => p_to_element_type
,p_element_level => PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK -- 'TASK' /* M20-08: changed to null */
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
/* Insert Default values for the proj_fp_option_id, element_type and planning_level. */
IF (p_to_element_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH) THEN
l_stage := 400;
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting Default Values for Both - COST
and REVENUE.';
SELECT cost_fin_plan_level_code
,revenue_fin_plan_level_code
,select_cost_res_auto_flag /* Bug 2920954 */
,cost_res_planning_level /* Bug 2920954 */
,select_rev_res_auto_flag /* Bug 2920954 */
,revenue_res_planning_level /* Bug 2920954 */
INTO l_cost_planning_level
,l_rev_planning_level
,l_select_cost_res_auto_flag /* Bug 2920954 */
,l_cost_res_planning_level /* Bug 2920954 */
,l_select_rev_res_auto_flag /* Bug 2920954 */
,l_revenue_res_planning_level /* Bug 2920954 */
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_to_proj_fp_options_id;
/* Call Insert_Default twice, once with Element_Type as 'COST' and then as PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
as the case of element_type being 'BOTH' is not handled in Insert_Default. */
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': calling insert default for cost.';
Insert_Default(p_proj_fp_options_id => p_to_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST
,p_planning_level => l_cost_planning_level
,p_resource_list_id => p_to_resource_list_id
,p_select_res_auto_flag => l_select_cost_res_auto_flag /* Bug 2920954 */
,p_res_planning_level => l_cost_res_planning_level /* Bug 2920954 */
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': calling insert default for revenue.';
Insert_Default(p_proj_fp_options_id => p_to_proj_fp_options_id
,p_element_type => PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE
,p_planning_level => l_rev_planning_level
,p_resource_list_id => p_to_resource_list_id
,p_select_res_auto_flag => l_select_rev_res_auto_flag /* Bug 2920954 */
,p_res_planning_level => l_revenue_res_planning_level /* Bug 2920954 */
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting Default Values for either COST
OR REVENUE.';
/* M20-AUG: replaced select with call to fin plan utils */
l_planning_level := PA_FIN_PLAN_UTILS.get_option_planning_level(p_to_proj_fp_options_id ,l_planning_level);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, select_cost_res_auto_flag
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,select_rev_res_auto_flag
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, select_all_res_auto_flag
,NULL) select_res_auto_flag
,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_res_planning_level
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,revenue_res_planning_level
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_res_planning_level
,NULL) res_planning_level
INTO l_select_res_auto_flag
,l_res_planning_level
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_to_proj_fp_options_id;
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': calling insert default for element type.';
Insert_Default(p_proj_fp_options_id => p_to_proj_fp_options_id
,p_element_type => p_to_element_type
,p_planning_level => l_planning_level
,p_resource_list_id => p_to_resource_list_id
,p_select_res_auto_flag => l_select_res_auto_flag /* Bug 2920954 */
,p_res_planning_level => l_res_planning_level /* Bug 2920954 */
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
FP Option to be used while inserting records into pa_fp_elements. */
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': getting info from to option id.';
SELECT fin_plan_type_id, fin_plan_version_id,project_id,
DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_fin_plan_level_code) planning_level,
DECODE(fin_plan_preference_code,
PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME, all_resource_list_id,
PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY, cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY, revenue_resource_list_id) resource_list_id
INTO l_to_fin_plan_type_id, l_to_fin_plan_version_id,l_to_project_id, l_to_planning_level,
l_to_resource_list_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_to_proj_fp_options_id;
SELECT project_id,
DECODE(l_from_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_fin_plan_level_code) plan_type_planning_level,
DECODE(l_from_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_resource_list_id) plan_type_resource_list_id
INTO l_from_project_id, l_from_planning_level,l_from_resource_list_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_from_proj_fp_option_id;
/* Delete the records from pa_fp_elements for the Target Proj FP Option and Target Element Type
before inserting records into pa_fp_elements. */
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Deleting the Elements from FP Elements';
Delete_Elements(p_proj_fp_options_id => p_to_proj_fp_options_id
,p_element_type => p_to_element_type
,p_element_level => PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK -- 'TASK'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
and insert into PA_FP_ELEMENTS. */
l_stage :=700;
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting records into PA_FP_ELEMENTS';
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
SELECT pa_fp_elements_s.nextval
,p_to_proj_fp_options_id
,project_id
,l_to_fin_plan_type_id
,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_to_element_type)
,l_to_fin_plan_version_id
,task_id
,top_task_id
,resource_list_member_id
,top_task_planning_level
,resource_planning_level
,plannable_flag
,resources_planned_for_task
,NVL(plan_amount_exists_flag,'N') /* Bug 2966275 its better to store to as 'N' */
,plannable_flag /* Same as plannable_flag */
,top_task_planning_level /* Same as top_task_planning_level */
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
FROM pa_fp_elements
WHERE proj_fp_options_id = l_from_proj_fp_option_id
AND element_type = decode(l_from_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,l_from_element_type)
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 */
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
SELECT
pa_fp_elements_s.nextval
,p_to_proj_fp_options_id
,l_to_project_id
,l_to_fin_plan_type_id
,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_to_element_type)
,l_to_fin_plan_version_id
,target_pt.task_id
,target_pt.top_task_id
,resource_list_member_id
,top_task_planning_level
,resource_planning_level
,plannable_flag
,resources_planned_for_task
,NVL(plan_amount_exists_flag,'N') /* Bug 2966275 its better to store to as 'N' */
,plannable_flag /* Same as plannable_flag */
,top_task_planning_level /* Same as top_task_planning_level */
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
FROM pa_fp_elements fp,
pa_tasks source_pt,
pa_tasks target_pt
WHERE fp.proj_fp_options_id = l_from_proj_fp_option_id
AND fp.task_id = source_pt.task_id
AND source_pt.task_number = target_pt.task_number
AND target_pt.project_id = l_to_project_id
--AND source_pt.project_id = l_from_project_id /* Bug# 2688544 */ Commented for bug 2814165
AND element_type = decode(l_from_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,l_from_element_type);
SELECT all_fin_plan_level_code
,cost_fin_plan_level_code
,revenue_fin_plan_level_code
INTO l_all_fin_plan_level_code
,l_cost_fin_plan_level_code
,l_revenue_fin_plan_level_code
FROM pa_proj_fp_options
WHERE proj_fp_options_id = l_from_proj_fp_option_id;
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
SELECT pa_fp_elements_s.nextval
,p_to_proj_fp_options_id
,l_to_project_id
,l_to_fin_plan_type_id
,decode(p_to_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_to_element_type)
,l_to_fin_plan_version_id
,fp.task_id
,fp.top_task_id
,resource_list_member_id
,top_task_planning_level
,resource_planning_level
,plannable_flag
,resources_planned_for_task
,NVL(plan_amount_exists_flag,'N') /* Bug 2966275 its better to store to as 'N' */
,plannable_flag /* Same as plannable_flag */
,top_task_planning_level /* Same as top_task_planning_level */
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
FROM pa_fp_elements fp
WHERE fp.proj_fp_options_id = l_from_proj_fp_option_id
AND fp.task_id = 0
AND element_type = decode(l_from_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,l_from_element_type);
INSERT_DEFAULT: This procedure is used to insert records into FP Elements. This procedure is
called from Copy_Elements and Refresh_FP_Elements.
-> The insertion of records is based on the Planning Level passed to this procedure. The planning
level coud be at Top and Lowest Task or only Top Tasks.
-> Two different cursors are created for this purpose, one for Top and Lowest Tasks and
one for only Top Tasks.
NOTE:- Input parameter p_res_planning_level refers to the resource planning level
Bug 2920954 :- This is an existing api that has been modified to insert resource elements for the
default task elements based on the i/p parameters for automatic resource selection and resource
planning level for automatic resource selection.
==================================================================================================*/
PROCEDURE Insert_Default (
p_proj_fp_options_id IN NUMBER
,p_element_type IN VARCHAR2
,p_planning_level IN VARCHAR2
,p_resource_list_id IN NUMBER
/* Bug 2920954 start of parameters added for post fp-K one off patch */
,p_select_res_auto_flag IN pa_proj_fp_options.select_cost_res_auto_flag%TYPE
,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
/* Bug 2920954 end of parameters added for post fp-K one off patch */
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
l_project_id pa_proj_fp_options.PROJECT_ID%TYPE;
l_select_res_auto_flag pa_proj_fp_options.select_cost_res_auto_flag%TYPE;
/* M24-08: Modified this cursor as it was previously inserting top and lowest task with plannable
flag as 'N'
Now first union will select only those top tasks for which any lowest task exists.
Second union will select all Lowest and 'Top and Lowest' Tasks. If task id is same
as top and lowest task then planning level will be lowest else null
*/
CURSOR top_low_tasks_cur(p_project_id NUMBER) is
/* Bug 3106741 for performance improvement Order By removed, UNION replaced with UNION ALL */
SELECT task_id task_id
,top_task_id top_task_id
,l_task_planning_level_low top_task_planning_level
,'N' plannable_flag
FROM pa_tasks t1
WHERE project_id = p_project_id
AND task_id = top_task_id
AND exists (SELECT 'x'
FROM pa_tasks t2
WHERE t2.parent_task_id = t1.task_id)
UNION ALL -- bug 3106741 UNION
SELECT task_id task_id
,top_task_id top_task_id
,decode(task_id,top_task_id,l_task_planning_level_low,null) top_task_planning_level
,'Y' plannable_flag
FROM pa_tasks t1
WHERE project_id = p_project_id
AND not exists (SELECT 'x'
FROM pa_tasks t2
WHERE t2.parent_task_id = t1.task_id);
SELECT task_id task_id
,top_task_id top_task_id
,l_task_planning_level_top top_task_planning_level
,'Y' plannable_flag
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = top_task_id;
pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Insert_Default');
pa_debug.set_process('Insert_Default: ' || 'PLSQL','LOG',l_debug_mode);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT decode(p_element_type,'COST',cost_fin_plan_level_code,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,revenue_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL,all_fin_plan_level_code,NULL)
INTO l_planning_level
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST,cost_resource_list_id
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,revenue_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL,all_resource_list_id,NULL)
INTO l_resource_list_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
IF (p_select_res_auto_flag IS NULL) AND (p_res_planning_level IS NULL)
THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Auto res addition params not passed getting from option.';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, select_cost_res_auto_flag
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,select_rev_res_auto_flag
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, select_all_res_auto_flag
,NULL) select_res_auto_flag
,decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_res_planning_level
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE,revenue_res_planning_level
,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_res_planning_level
,NULL) res_planning_level
INTO l_select_res_auto_flag
,l_res_planning_level
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
l_select_res_auto_flag := p_select_res_auto_flag;
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT project_id, fin_plan_type_id, fin_plan_version_id
INTO l_project_id, l_fin_plan_type_id, l_fin_plan_version_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = p_proj_fp_options_id;
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
/* SELECT decode(uncategorized_flag,'Y',NULL,PA_FP_CONSTANTS_PKG.G_RESOURCE_PLANNING_LEVEL_R)
INTO l_res_planning_level
FROM pa_resource_lists_all_bg R1, pa_implementations_all I
WHERE R1.resource_list_id = l_resource_list_id
AND R1.business_group_id = I.business_group_id;
/* Fix for 2586647. Commented the above select. Replaced with the following call. */
PA_FIN_PLAN_UTILS.GET_RESOURCE_LIST_INFO(
P_RESOURCE_LIST_ID => l_resource_list_id,
X_RES_LIST_IS_UNCATEGORIZED => l_res_list_is_uncategorized,
X_IS_RESOURCE_LIST_GROUPED => l_is_resource_list_grouped,
X_GROUP_RESOURCE_TYPE_ID => l_group_resource_type_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
If auto res selection is chosen, resource planning level for the task should be
res_planning_level chosen on the plan_settings page.
*/
IF (l_select_res_auto_flag <> 'Y')
THEN /* Bug 2920954 */
IF l_res_list_is_uncategorized = 'N' THEN
l_res_planning_level := PA_FP_CONSTANTS_PKG.G_RESOURCE_PLANNING_LEVEL_R;
/* The values that are inserted into the table PA_FP_ELEMENTS depending on the the planning level.
The values of the columns task_id, top_task_id, top_task_planning_level, plannable_flag to be
inserted into the table would depend on the Planning Level. */
---- Bug # 3507156
-- References to PA_FP_ELEMENTS table have been commented as records are no longer inserted in it
--Comment START.
/*
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Bulk Inserting records into PA_FP_Elements';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting records for Top and Lowest Tasks.';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
VALUES
(pa_fp_elements_s.nextval
,p_proj_fp_options_id
,l_project_id
,l_fin_plan_type_id
,p_element_type
,l_fin_plan_version_id
,l_task_id_tbl(i)
,l_top_task_id_tbl(i)
,l_resource_list_member_id
,l_top_plan_level_tbl(i)
,l_res_planning_level
,l_plannable_flag_tbl(i)
,l_res_planned_for_task
,l_plan_amt_exists_flag
,l_plannable_flag_tbl(i) -- Same as plannable_flag
,l_top_plan_level_tbl(i) -- Same as top_task_planning_level
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': inserted ' || sql%rowcount || ' records';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Inserting records for Top Tasks only.';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,3);
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
VALUES
(pa_fp_elements_s.nextval
,p_proj_fp_options_id
,l_project_id
,l_fin_plan_type_id
,p_element_type
,l_fin_plan_version_id
,l_task_id_tbl(i)
,l_top_task_id_tbl(i)
,l_resource_list_member_id
,l_top_plan_level_tbl(i)
,l_res_planning_level
,l_plannable_flag_tbl(i)
,l_res_planned_for_task
,l_plan_amt_exists_flag
,l_plannable_flag_tbl(i) -- Same as plannable_flag
,l_top_plan_level_tbl(i) -- Same as top_task_planning_level
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
IF l_select_res_auto_flag = 'Y'
THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_stage)||'Calling add_resources_automatically';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
pa_debug.g_err_stage := TO_CHAR(l_stage)||': End of Insert_Default';
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.g_err_stage,1);
( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Insert_Default'
,p_procedure_name => pa_debug.G_Err_Stack );
pa_debug.write('Insert_Default: ' || l_module_name,SQLERRM,4);
pa_debug.write('Insert_Default: ' || l_module_name,pa_debug.G_Err_Stack,4);
END Insert_Default;
DELETE_ELEMENTS: This procedure is used to delete records from PA_FP_ELEMENTS table for a
particular Proj FP Options ID depending on the Element Type and the Element Level.
- If element_type is BOTH, delete both the cost and revenue planning elements.
- If the element_level is 'TASK', then delete all the task elements and corresponding resources.
- If the element_level is resource, delete on the resources for all the task elements
Bug 2976168. Delete from pa_fp_excluded_elements also
==================================================================================================*/
PROCEDURE Delete_Elements (
p_proj_fp_options_id IN NUMBER
,p_element_type IN VARCHAR2 /* COST,REVENUE,ALL,BOTH */
,p_element_level IN VARCHAR2 /* TASK,RESOURCE */
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
l_msg_count NUMBER := 0;
pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Delete_Elements');
pa_debug.set_process('Delete_Elements: ' || 'PLSQL','LOG',l_debug_mode);
/* Delete the records from the table PA_FP_Elements based on the Element_Type and
the Element_Level. If the Element_Type is 'BOTH' then both the COST and
REVENUE Planning Elements have to be deleted. */
pa_debug.g_err_stage := 'Deleting Elements from PA_FP_Elements';
pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
/* If Element Level is 'TASK', then delete FP Elements with Level as 'TASK' */
pa_debug.g_err_stage := 'Deleting Elements for the Element Level as TASK';
pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
DELETE FROM pa_fp_elements
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_element_type)
AND p_element_level = PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK ;
/* For bug 2976168. Delete from pa_fp_excluded_elements also */
DELETE FROM pa_fp_excluded_elements
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_element_type)
AND p_element_level = PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_TASK ;
/* If Element Level is 'RESOURCE', then delete FP Elements with Level as
'RESOURCE' and where the resource_list_memeber_id is not 0 */
pa_debug.g_err_stage := 'Deleting Elements for the Element Level as RESOURCE';
pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
DELETE FROM pa_fp_elements
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = decode(p_element_type,PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH,element_type,p_element_type)
AND p_element_level = PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_RESOURCE
AND resource_list_member_id <> 0;
pa_debug.g_err_stage := 'End of Delete_Elements';
pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.g_err_stage,1);
( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Delete_Elements'
,p_procedure_name => pa_debug.G_Err_Stack );
pa_debug.write('Delete_Elements: ' || l_module_name,SQLERRM,4);
pa_debug.write('Delete_Elements: ' || l_module_name,pa_debug.G_Err_Stack,4);
END Delete_Elements;
DELETE_ELEMENT: This procedure is used to delete records from PA_FP_ELEMENTS table for a
particular task_id and resource_list_member_id.
If resource_list_member_id is populated then only resource level element will be deleted.
Else if task_id is lowest task and its top task does not have any other tasks then the
input task_id as well as its top task will be deleted.
==================================================================================================*/
PROCEDURE Delete_Element (
p_task_id IN NUMBER
,p_resource_list_member_id IN NUMBER
,p_budget_version_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
l_msg_count NUMBER := 0;
l_row_update_count NUMBER;
pa_debug.set_err_stack('PA_FP_ELEMENTS_PUB.Delete_Element');
pa_debug.set_process('Delete_Element: ' || 'PLSQL','LOG',l_debug_mode);
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT proj_fp_options_id
INTO l_proj_fp_options_id
FROM pa_proj_fp_options pfo
WHERE fin_plan_version_id = p_budget_version_id;
/* If its an uncategorized resource then task level record needs to be deleted.
task level records in pa_fp_elements always have resource list member id as zero. */
pa_debug.g_err_stage := 'Deleting Elements for the task';
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
DELETE FROM pa_fp_elements
WHERE proj_fp_options_id = l_proj_fp_options_id
AND task_id = p_task_id
AND resource_list_member_id = 0
RETURNING top_task_id into l_top_task_id; --Bug 2774779
update pa_fp_elements
set plan_amount_exists_flag = 'N',
record_version_number = record_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where proj_fp_options_id = l_proj_fp_options_id
and task_id = l_top_task_id
and not exists
(
select 1
from pa_fp_elements
where top_task_id = l_top_task_id
and task_id <> l_top_task_id
and proj_fp_options_id = l_proj_fp_options_id
and nvl(plan_amount_exists_flag,'N') = 'Y'
);
pa_debug.g_err_stage := 'Number of rows updated for plan amount exists flag : '||sql%rowcount;
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,3);
/* If its a normal resource from a resource list then we need to delete the resource
level element from fp elements. */
pa_debug.g_err_stage := 'Deleting Elements for the RESOURCE';
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
DELETE FROM pa_fp_elements
WHERE proj_fp_options_id = l_proj_fp_options_id -- included for Bug 3062798
AND fin_plan_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_resource_list_member_id
RETURNING top_task_id into l_top_task_id; --Bug 2774779
update pa_fp_elements
set plan_amount_exists_flag = 'N',
record_version_number = record_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where proj_fp_options_id = l_proj_fp_options_id
and resource_list_member_id = 0
and task_id = p_task_id
and not exists
(
select 1
from pa_fp_elements
where task_id = p_task_id
and proj_fp_options_id = l_proj_fp_options_id
and resource_list_member_id <> 0
and nvl(plan_amount_exists_flag,'N') = 'Y'
);
l_row_update_count := sql%rowcount;
pa_debug.g_err_stage := 'Number of rows updated for plan amount exists flag : '|| l_row_update_count;
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,3);
IF p_task_id <> l_top_task_id and l_row_update_count > 0 then
update pa_fp_elements
set plan_amount_exists_flag = 'N',
record_version_number = record_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where proj_fp_options_id = l_proj_fp_options_id
and resource_list_member_id = 0
and task_id = l_top_task_id
and not exists
(
select 1
from pa_fp_elements
where top_task_id = l_top_task_id
and proj_fp_options_id = l_proj_fp_options_id
and resource_list_member_id <> 0
and nvl(plan_amount_exists_flag,'N') = 'Y'
);
pa_debug.g_err_stage := 'Number of rows updated for plan amount exists flag : '||sql%rowcount;
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
SELECT 'Y'
INTO l_resource_exists_flag
FROM dual
WHERE exists (select 1
from pa_fp_elements fp
where proj_fp_options_id = l_proj_fp_options_id
and fp.task_id = p_task_id
and fp.resource_list_member_id <> 0);
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
UPDATE pa_fp_elements
SET resources_planned_for_task = 'N',
record_version_number = record_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE proj_fp_options_id = l_proj_fp_options_id
AND task_id = p_task_id
AND resource_list_member_id = 0;
pa_debug.g_err_stage := 'End of Delete_Elements';
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.g_err_stage,1);
( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Delete_Element'
,p_procedure_name => pa_debug.G_Err_Stack );
pa_debug.write('Delete_Element: ' || l_module_name,SQLERRM,4);
pa_debug.write('Delete_Element: ' || l_module_name,pa_debug.G_Err_Stack,4);
END Delete_Element;
This procedure inserts records into PA_FP_ELEMENTS in BULK
==================================================================================================*/
PROCEDURE Insert_Bulk_Rows (
p_proj_fp_options_id IN NUMBER
,p_project_id IN NUMBER
,p_fin_plan_type_id IN NUMBER
,p_element_type IN VARCHAR2
,p_plan_version_id IN NUMBER
,p_task_id_tbl IN l_task_id_tbl_typ
,p_top_task_id_tbl IN l_top_task_id_tbl_typ
,p_res_list_mem_id_tbl IN l_res_list_mem_id_tbl_typ
,p_task_planning_level_tbl IN l_task_planning_level_tbl_typ
,p_res_planning_level_tbl IN l_res_planning_level_tbl_typ
,p_plannable_flag_tbl IN l_plannable_flag_tbl_typ
,p_res_planned_for_task_tbl IN l_res_planned_for_task_tbl_typ
,p_planamount_exists_tbl IN l_planamount_exists_tbl_typ
,p_res_uncategorized_flag IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
l_stage NUMBER :=100;
pa_debug.set_err_stack('PA_FP_ELELEMNTS_PUB.Insert_Bulk_Rows');
pa_debug.set_process('Insert_Bulk_Rows: ' || 'PLSQL','LOG',l_debug_mode);
pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_ELELEMNTS_PUB.Insert_Bulk_Rows ';
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
* Bulk Insert records into PA_FP_ELEMENTS table for the records fetched
* from cursor top_task_cur.
*/
pa_debug.g_err_stage := TO_CHAR(l_stage)||': In Insert_Bulk_Rows';
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
pa_debug.g_err_stage := TO_CHAR(l_stage)||': Bulk inserting into PA_FP_ELEMENTS';
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
VALUES
(pa_fp_elements_s.nextval
,p_proj_fp_options_id
,p_project_id
,p_fin_plan_type_id
,p_element_type
,p_plan_version_id
,p_task_id_tbl(i)
,p_top_task_id_tbl(i)
,decode(p_res_uncategorized_flag,'Y',0,p_res_list_mem_id_tbl(i))
,p_task_planning_level_tbl(i)
,p_res_planning_level_tbl(i)
,p_plannable_flag_tbl(i)
,p_res_planned_for_task_tbl(i)
,p_planamount_exists_tbl(i)
,p_plannable_flag_tbl(i)
,p_task_planning_level_tbl(i)
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Insert_Bulk_Rows'
,p_procedure_name => pa_debug.G_Err_Stack );
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,SQLERRM,4);
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.G_Err_Stack,4);
END Insert_Bulk_Rows;
This procedure inserts records into PA_RESOURCE_ASSIGNMENTS in BULK
==================================================================================================*/
PROCEDURE Insert_Bulk_Rows_Res (
p_project_id IN NUMBER
,p_plan_version_id IN NUMBER
,p_task_id_tbl IN l_task_id_tbl_typ
,p_res_list_mem_id_tbl IN l_res_list_mem_id_tbl_typ
,p_unit_of_measure_tbl IN l_unit_of_measure_tbl_typ
,p_track_as_labor_flag_tbl IN l_track_as_labor_flag_tbl_typ
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
l_stage NUMBER :=100;
pa_debug.set_err_stack('PA_FP_ELELEMNTS_PUB.Insert_Bulk_Rows_Res');
pa_debug.set_process('Insert_Bulk_Rows: ' || 'PLSQL','LOG',l_debug_mode);
pa_debug.g_err_stage := TO_CHAR(l_stage)||':In PA_FP_ELELEMNTS_PUB.Insert_Bulk_Rows_Res ';
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
* Bulk Insert records into PA_FP_ELEMENTS table for the records fetched
* from cursor top_task_cur.
*/
pa_debug.g_err_stage := TO_CHAR(l_stage)||': In Insert_Bulk_Rows_Res';
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
pa_debug.g_err_stage := TO_CHAR(l_stage)||': Bulk inserting into PA_RESOURCE_ASSIGNMENTS';
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.g_err_stage,2);
INSERT INTO pa_resource_assignments
(RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,UNIT_OF_MEASURE
,TRACK_AS_LABOR_FLAG
,PROJECT_ASSIGNMENT_ID
,RESOURCE_ASSIGNMENT_TYPE )
VALUES
(PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL
,p_plan_version_id -- BUDGET_VERSION_ID
,p_project_id -- PROJECT_ID
,p_task_id_tbl(i) -- TASK_ID
,p_res_list_mem_id_tbl(i) -- RESOURCE_LIST_MEMBER_ID
,sysdate -- LAST_UPDATE_DATE
,fnd_global.user_id -- LAST_UPDATED_BY
,sysdate -- CREATION_DATE
,fnd_global.user_id -- CREATED_BY
,fnd_global.login_id -- LAST_UPDATE_LOGIN
,p_unit_of_measure_tbl(i) -- UNIT_OF_MEASURE
,p_track_as_labor_flag_tbl(i) -- TRACK_AS_LABOR_FLAG
,-1 -- PROJECT_ASSIGNMENT_ID
,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) ; -- RESOURCE_ASSIGNMENT_TYPE
( p_pkg_name => 'PA_FP_ELEMENTS_PUB.Insert_Bulk_Rows_Res'
,p_procedure_name => pa_debug.G_Err_Stack );
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,SQLERRM,4);
pa_debug.write('Insert_Bulk_Rows: ' || l_module_name,pa_debug.G_Err_Stack,4);
END Insert_Bulk_Rows_Res;
SELECT 0 --task_id
,l_uncat_rlmid --resource_list_member_id
,l_track_as_labor_flag
,l_unit_of_measure /* Modified for bug #2586307. */
FROM DUAL
WHERE NOT EXISTS ( SELECT 'x'
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_plan_version_id
AND ra.task_id = 0
AND ra.resource_list_member_id =
l_uncat_rlmid);
SELECT fp.task_id --task_id
,l_uncat_rlmid --resource_list_member_id
,l_track_as_labor_flag
,l_unit_of_measure /* Modified for bug #2586307. */
FROM pa_fp_elements fp
WHERE proj_fp_options_id = l_proj_fp_options_id /* included for bug 3062798*/
AND fin_plan_version_id = p_plan_version_id
AND plannable_flag = 'Y'
AND fp.task_id = Nvl(p_task_id,fp.task_id) /* Bug 2920954 */
AND NOT EXISTS ( SELECT 'x'
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = fp.fin_plan_version_id
AND ra.project_id = fp.project_id
AND ra.task_id = fp.task_id
AND ra.resource_list_member_id = l_uncat_rlmid);
SELECT fp.task_id
,fp.resource_list_member_id
,prlm.track_as_labor_flag
,decode(prlm.track_as_labor_flag,'Y',PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
'N',decode(pr.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,NULL,pr.unit_of_measure)
) unit_of_measure /* Modified for bug #2586307 */
FROM pa_fp_elements fp, pa_resource_list_members prlm, pa_resources pr
WHERE proj_fp_options_id = l_proj_fp_options_id /* included for bug 3062798*/
AND fin_plan_version_id = p_plan_version_id
AND fp.resource_list_member_id <> 0 -- select only resource level records
AND fp.plannable_flag = 'Y' --resource is plannable
AND fp.resource_list_member_id = prlm.resource_list_member_id
AND pr.resource_id = prlm.resource_id
AND fp.task_id = Nvl(p_task_id,fp.task_id) /* Bug 2920954 */
AND NOT EXISTS ( SELECT 'x'
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = fp.fin_plan_version_id
AND ra.project_id = fp.project_id
AND ra.task_id = fp.task_id
AND ra.resource_list_member_id =
fp.resource_list_member_id);
SELECT pra.resource_assignment_id
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = p_plan_version_id
AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
AND NOT EXISTS (SELECT 1
FROM pa_fp_elements fpe
WHERE proj_fp_options_id = l_proj_fp_options_id /* included for bug 3062798*/
AND fpe.fin_plan_version_id = p_plan_version_id
AND fpe.task_id = pra.task_id
AND fpe.resource_list_member_id = decode(pra.resource_list_member_id,l_uncat_rlmid,
0,pra.resource_list_member_id)
);
SELECT resource_list_id
,project_id
INTO l_resource_list_id
,l_project_id
FROM pa_budget_versions
WHERE budget_version_id = p_plan_version_id;
SELECT proj_fp_options_id
INTO l_proj_fp_options_id
FROM pa_proj_fp_options
WHERE fin_plan_version_id = p_plan_version_id;
SELECT NVL(uncategorized_flag,'N')
INTO l_uncat_flag
FROM pa_resource_lists
WHERE resource_list_id = l_resource_list_id;
deleted from pa_fp_elements but not from resource_assignments. These records have
to be deleted from pa_resource_assignments also else they will be once again
available in the Edit Plan page. */
/* Should NOT be done when planning level is project and resource list is uncategorized
as there needs to be one record existing in pa_resource_assignments for this case. */
/* Bug #2634979: Modified the logic of deleting records from pa_resource_assignments.
If the Planning level is 'Project' and the resource list is uncategorized, then
the records for the Plan Version ID have to be deleted except the project level
records and the records with uncategorized resource list member id.
This will handle the case where the plannning level has been modified to 'Project',
and the resource list has been changed to an uncategorized resource list.
In this case the old records have to be deleted from resource assignments and new
resource assignments need to be created after the new FP elements are define. */
/* Bug 2920954 - Deletion of resource assignments would be done only if p_res_del_req_flag is Y */
IF p_res_del_req_flag = 'Y' THEN
IF (l_fp_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT AND
l_uncat_flag = 'Y' ) THEN
pa_debug.g_err_stage:= 'Deleting resource assignments';
DELETE FROM pa_resource_assignments
WHERE budget_version_id = p_plan_version_id
AND (task_id <> 0 or resource_list_member_id <> l_uncat_rlmid);
/* In all other cases, records have to be deleted from pa_resource_assignments
which do not exist in pa_fp_elements. */
pa_debug.g_err_stage:= 'fetching resource assignments that should be deleted';
DELETE FROM pa_resource_assignments
WHERE resource_assignment_id = l_ra_id_tbl(i);
pa_debug.g_err_stage := 'Deleted ' || sql%rowcount || ' records';
pa_debug.g_err_stage:= 'fetching cursor values and doing bulk insert';
Insert_Bulk_Rows_Res(
p_project_id =>l_project_id
,p_plan_version_id =>p_plan_version_id
,p_task_id_tbl =>l_task_id_tbl
,p_res_list_mem_id_tbl =>l_rlmid_tbl
,p_unit_of_measure_tbl =>l_uom_tbl
,p_track_as_labor_flag_tbl=>l_track_as_labor_flag_tbl
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data );
pa_debug.g_err_stage:= 'fetching cursor values and doing bulk insert';
Insert_Bulk_Rows_Res(
p_project_id =>l_project_id
,p_plan_version_id =>p_plan_version_id
,p_task_id_tbl =>l_task_id_tbl
,p_res_list_mem_id_tbl =>l_rlmid_tbl
,p_unit_of_measure_tbl =>l_uom_tbl
,p_track_as_labor_flag_tbl=>l_track_as_labor_flag_tbl
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data );
pa_debug.g_err_stage:= 'fetching cursor values and doing bulk insert';
Insert_Bulk_Rows_Res(
p_project_id =>l_project_id
,p_plan_version_id =>p_plan_version_id
,p_task_id_tbl =>l_task_id_tbl
,p_res_list_mem_id_tbl =>l_rlmid_tbl
,p_unit_of_measure_tbl =>l_uom_tbl
,p_track_as_labor_flag_tbl=>l_track_as_labor_flag_tbl
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data );
SELECT proj_fp_elements_id
INTO l_proj_fp_elements_id
FROM pa_fp_elements fpe
WHERE fpe.proj_fp_options_id = p_proj_fp_options_id
AND fpe.element_type = p_element_type
AND fpe.task_id = p_task_id
AND fpe.resource_list_member_id = p_resource_list_member_id;
SELECT plannable_flag
INTO l_plannable_flag
FROM pa_fp_elements fpe
WHERE fpe.proj_fp_options_id = p_proj_fp_options_id
AND fpe.element_type = p_element_type
AND fpe.task_id = p_task_id
AND fpe.resource_list_member_id = p_resource_list_member_id;
SELECT plan_amount_exists_flag
INTO l_plan_amount_exists_flag
FROM pa_fp_elements fpe
WHERE fpe.proj_fp_options_id = p_proj_fp_options_id
AND fpe.element_type = p_element_type
AND fpe.task_id = p_task_id
AND fpe.resource_list_member_id = p_resource_list_member_id;
elements that are not inserted already are chosen for insertion.
Bug :- 2625872, In the new budgets model,for a given task the user can
plan either at resource level or resource group level butn't both.
As this api is also being used to upgrade budget_versions from old model
to new model, we should check if mixed resource planning level exists
for the current budget version.
===================================================================*/
PROCEDURE Create_elements_from_version(
p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
,p_element_type IN pa_fp_elements.element_type%TYPE
,p_from_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE
,x_mixed_resource_planned_flag OUT NOCOPY VARCHAR2 -- new parameter for Bug :- 2625872 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_return_status VARCHAR2(2000);
SELECT project_id
,fin_plan_type_id
,fin_plan_version_id
,PA_FIN_PLAN_UTILS.GET_OPTION_PLANNING_LEVEL(c_proj_fp_options_id,c_element_type) fin_plan_level_code
FROM pa_proj_fp_options
WHERE proj_fp_options_id = c_proj_fp_options_id;
SELECT 0 task_id
,0 top_task_id
,pra.resource_list_member_id resource_list_member_id
,NULL top_task_planning_level
,NULL resource_planning_level
,'Y' plannable_flag
,NULL resources_planned_for_task
,'Y' plan_amount_exists_flag
,DECODE(prlm.parent_member_id, NULL, 'G','R') resource_level -- Bug :- 2625872
FROM pa_resource_assignments pra
,pa_resource_list_members prlm
WHERE budget_version_id = c_from_version_id
AND NVL(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) =
PA_FP_CONSTANTS_PKG.G_USER_ENTERED
AND prlm.resource_list_member_id = pra.resource_list_member_id
AND NOT EXISTS(select 'x' from pa_fp_elements e
where e.proj_fp_options_id = p_proj_fp_options_id
and e.element_Type = p_element_Type
and e.task_id = 0
and e.resource_list_member_id = pra.resource_list_member_id);
SELECT pra.task_id task_id
,pt.top_task_id top_task_id
,pra.resource_list_member_id resource_list_member_id
,NULL top_task_planning_level
,NULL resource_planning_level
,'Y' plannable_flag
,NULL resources_planned_for_task
,'Y' plan_amount_exists_flag
,DECODE(prlm.parent_member_id, NULL, 'G','R') resource_level -- Bug :- 2625872
FROM pa_resource_assignments pra
,pa_tasks pt
,pa_resource_list_members prlm
WHERE budget_version_id = c_from_version_id
AND pt.task_id = pra.task_id
AND NVL(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) =
PA_FP_CONSTANTS_PKG.G_USER_ENTERED
AND prlm.resource_list_member_id = pra.resource_list_member_id
AND NOT EXISTS(select 'x' from pa_fp_elements e
where e.proj_fp_options_id = p_proj_fp_options_id
and e.element_Type = p_element_Type
and e.task_id = pra.task_id
and e.resource_list_member_id = pra.resource_list_member_id)
ORDER BY pra.task_id ;
SELECT DISTINCT pra.task_id task_id
,pt.top_task_id top_task_id
,0 resource_list_member_id
/* Bug 3019572
,DECODE(fp_options_rec.fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_LOWEST, PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST,
PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_M, DECODE(pra.task_id, pt.top_task_id, PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST),
DECODE(pra.task_id,pt.top_task_id,PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP,
PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST)) top_task_planning_level
*/
,DECODE(pra.task_id,
pt.top_task_id,
DECODE(fp_options_rec.fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_LOWEST,
PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST,
PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,
PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP,
PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_M,
DECODE(pa_task_utils.check_child_exists(pra.task_id),
1, PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_TOP,
PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST)
),
null
) top_task_planning_level
,get_resource_planning_level( prlm.parent_member_id
,l_uncategorized_flag
,l_grouped_flag ) resource_planning_level
,'Y' plannable_flag
,DECODE(l_uncategorized_flag,'Y',NULL,'Y') resources_planned_for_task
,'Y' plan_amount_exists_flag
FROM pa_resource_assignments pra
,pa_tasks pt
,pa_resource_list_members prlm
WHERE budget_version_id = c_from_version_id
AND pt.task_id = pra.task_id
AND prlm.resource_list_member_id = pra.resource_list_member_id
AND NVL(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_USER_ENTERED) = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
AND NOT EXISTS(select 'x' from pa_fp_elements e
where e.proj_fp_options_id = p_proj_fp_options_id
and e.element_Type = p_element_Type
and e.task_id = pra.task_id
and e.resource_list_member_id = 0);
SELECT DISTINCT pt.top_task_id task_id
,pt.top_task_id top_task_id
,0 resource_list_member_id
,PA_FP_CONSTANTS_PKG.G_TASK_PLAN_LEVEL_LOWEST top_task_planning_level
,NULL resource_planning_level
,'N' plannable_flag
,NULL resources_planned_for_task
,'Y' plan_amount_exists_flag
FROM pa_fp_elements pfe
,pa_tasks pt
WHERE pfe.proj_fp_options_id = c_proj_fp_options_id
AND pfe.element_type = p_element_type
AND pt.task_id = pfe.task_id
AND pt.top_task_id <> pfe.task_id
AND NOT EXISTS (SELECT 'x' -- not exists clause added for bug#2803724
FROM pa_fp_elements e
WHERE e.proj_fp_options_id = p_proj_fp_options_id
AND e.element_Type = p_element_Type
AND e.task_id = pt.top_task_id
AND e.resource_list_member_id = 0 );
PROCEDURE Call_Insert_Bulk_Rows_Elements IS
BEGIN
PA_FP_ELEMENTS_PUB.Insert_Bulk_Rows (
p_proj_fp_options_id => p_proj_fp_options_id
,p_project_id => fp_options_rec.project_id
,p_fin_plan_type_id => fp_options_rec.fin_plan_type_id
,p_element_type => p_element_type
,p_plan_version_id => fp_options_rec.fin_plan_version_id
,p_task_id_tbl => l_task_id_tbl
,p_top_task_id_tbl => l_top_task_id_tbl
,p_res_list_mem_id_tbl => l_res_list_member_id_tbl
,p_task_planning_level_tbl => l_top_task_planning_level_tbl
,p_res_planning_level_tbl => l_res_planning_level_tbl
,p_plannable_flag_tbl => l_plannable_flag_tbl
,p_res_planned_for_task_tbl => l_res_planned_for_task_tbl
,p_planamount_exists_tbl => l_plan_amount_exists_flag_tbl
,p_res_uncategorized_flag => NULL
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END Call_Insert_Bulk_Rows_Elements;
pa_debug.g_err_stage := 'Calling call_insert_bulk_rows_elements';
Call_Insert_Bulk_Rows_Elements;
Call_Insert_Bulk_Rows_Elements;
Call_Insert_Bulk_Rows_Elements;
Call_Insert_Bulk_Rows_Elements;
refresh_res_list_changes: This procedure is used to delete resource elements from PA_FP_ELEMENTS
table for a particular Proj FP Options ID depending on the Element Type when the resource list is
changed in the plan settings page. After deleting the resource records, it sets the
resource planning level for the task records to 'R' if the resource list is categorized or to NULL
if it is not categorized
Bug 2920954 :- This api has been modifed to insert resource elements for the already selected task
or project elements based on the input resource list id and the automatic resource selection
parameter and resource planning level for automatic resource selection
==================================================================================================*/
PROCEDURE refresh_res_list_changes (
p_proj_fp_options_id IN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE
,p_element_type IN PA_FP_ELEMENTS.ELEMENT_TYPE%TYPE /* COST,REVENUE,ALL,BOTH */
,p_cost_resource_list_id IN PA_PROJ_FP_OPTIONS.COST_RESOURCE_LIST_ID%TYPE
,p_rev_resource_list_id IN PA_PROJ_FP_OPTIONS.REVENUE_RESOURCE_LIST_ID%TYPE
,p_all_resource_list_id IN PA_PROJ_FP_OPTIONS.ALL_RESOURCE_LIST_ID%TYPE
/* Bug 2920954 start of new parameters added for post fp-K one off patch */
,p_select_cost_res_auto_flag IN pa_proj_fp_options.select_cost_res_auto_flag%TYPE
,p_cost_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
,p_select_rev_res_auto_flag IN pa_proj_fp_options.select_rev_res_auto_flag%TYPE
,p_revenue_res_planning_level IN pa_proj_fp_options.revenue_res_planning_level%TYPE
,p_select_all_res_auto_flag IN pa_proj_fp_options.select_all_res_auto_flag%TYPE
,p_all_res_planning_level IN pa_proj_fp_options.all_res_planning_level%TYPE
/* Bug 2920954 end of new parameters added for post fp-K one off patch */
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
l_msg_count NUMBER := 0;
/* Delete the records from the table PA_FP_Elements based on the Element_Type and
for Element level RESOURCE. */
pa_debug.g_err_stage := 'Deleting Elements from PA_FP_Elements';
delete_elements(p_proj_fp_options_id => p_proj_fp_options_id
,p_element_type => p_element_type
,p_element_level => PA_FP_CONSTANTS_PKG.G_ELEMENT_LEVEL_RESOURCE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
then update the resource planning level for the task records to 'R'. If the
resource list is not categorized make the resource planning level NULL */
IF (p_element_type =PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST OR
p_element_type =PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_BOTH) THEN
IF (p_cost_resource_list_id IS NULL) THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Cost Resource List Id is NULL.';
IF p_select_cost_res_auto_flag = 'Y'
THEN /* Bug 2920954 */
/* p_cost_res_planning_level should be either 'R'/'G' */
IF p_cost_res_planning_level NOT IN ('R','G')
THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Cost Auto Res Plan Level is Invalid';
UPDATE pa_fp_elements
SET resource_planning_level = l_res_planning_level
,resources_planned_for_task = 'N'
,record_version_number = record_version_number + 1
,last_update_date = sysdate
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_login = FND_GLOBAL.LOGIN_ID
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST;
IF p_select_rev_res_auto_flag = 'Y'
THEN /* Bug 2920954 */
/* p_revenue_res_planning_level should be either 'R'/'G' */
IF p_revenue_res_planning_level NOT IN ('R','G')
THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- Revenue Auto Res Plan Level is Invalid';
UPDATE pa_fp_elements
SET resource_planning_level = l_res_planning_level
,resources_planned_for_task = 'N' --for bug 2676456
,record_version_number = record_version_number + 1
,last_update_date = sysdate
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_login = FND_GLOBAL.LOGIN_ID
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE;
IF p_select_all_res_auto_flag = 'Y'
THEN /* Bug 2920954 */
/* p_all_res_planning_level should be either 'R'/'G' */
IF p_all_res_planning_level NOT IN ('R','G')
THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.g_err_stage := TO_CHAR(l_Stage)||': Err- All Auto Res Plan Level is Invalid';
UPDATE pa_fp_elements
SET resource_planning_level = l_res_planning_level
,resources_planned_for_task = 'N' --for bug 2676456
,record_version_number = record_version_number + 1
,last_update_date = sysdate
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_login = FND_GLOBAL.LOGIN_ID
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = p_element_type;
select distinct system_reference1 task_id
,system_reference2 resource_list_member_id
,system_reference4 unit_of_measure
,system_reference5 track_as_labor_flag
,decode(rollup.system_reference1,0,0,pelm.element_version_id) wbs_element_version_id
/* included null columns after UT */
-- ,null proj_raw_cost /* Bug 2677597 */
-- ,null proj_burdened_cost
-- ,null proj_revenue
-- ,null projfunc_raw_cost
-- ,null projfunc_burd_cost
-- ,null projfunc_revenue
-- ,null quantity
from pa_fp_rollup_tmp rollup
,pa_proj_element_versions pelm
where not exists
(
select pra.resource_assignment_id
from pa_resource_assignments pra
where pra.task_id=rollup.system_reference1
and pra.resource_list_member_id = rollup.system_reference2
and pra.budget_version_id = c_version_id
)
and decode(rollup.system_reference1,0,c_parent_structure_version_id,rollup.system_reference1)
= decode(rollup.system_reference1,0,pelm.element_version_id,pelm.proj_element_id) -- Bug 3655290
and pelm.parent_structure_version_id = c_parent_structure_version_id
order by task_id,resource_list_member_id;
SELECT pfp.plan_class_code FROM pa_fin_plan_types_b pfp,pa_budget_versions pbv
WHERE pfp.FIN_PLAN_TYPE_ID = pbv.FIN_PLAN_TYPE_ID
AND pbv.budget_version_id =p_fin_plan_version_id ;
select opt.project_id,
opt.proj_fp_options_id,
pbv.version_type, -- Version type and element type are used interchangeably.
pbv.resource_list_id
into
l_project_id
,l_fp_options_id
,l_element_type
,l_resource_list_id
from pa_proj_fp_options opt,pa_budget_versions pbv
where opt.fin_plan_version_id = pbv.budget_version_id
and pbv.budget_version_id = p_fin_plan_version_id;
update pa_fp_rollup_tmp rollup
set resource_assignment_id =
(
select resource_assignment_id
from pa_resource_assignments ra
where ra.budget_version_id = p_fin_plan_version_id
and ra.task_id = rollup.system_reference1
and ra.resource_list_member_id = system_reference2
);
pa_debug.g_err_stage:= 'No of records updated in rollup tmp-> ' || sql%rowcount;
insertion of a record into PA_RESOURCE_ASSIGNMENTS package.
===============================================================================*/
PROCEDURE Insert_Resource_Assignment(
p_project_id IN pa_resource_assignments.project_id%TYPE
,p_budget_version_id IN pa_resource_assignments.budget_version_id%TYPE
,p_task_id IN pa_resource_assignments.task_id%TYPE
,p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE
,p_unit_of_measure IN pa_resource_assignments.unit_of_measure%TYPE
,p_track_as_labor_flag IN pa_resource_assignments.track_as_labor_flag%TYPE )
AS
l_row_id rowid;
PA_FP_RESOURCE_ASSIGNMENTS_PKG.Insert_Row
( px_resource_assignment_id => l_resource_assignment_id
,p_budget_version_id => p_budget_version_id
,p_project_id => p_project_id
,p_task_id => p_task_id
,p_resource_list_member_id => p_resource_list_member_id
,p_unit_of_measure => p_unit_of_measure
,p_track_as_labor_flag => p_track_as_labor_flag
,p_standard_bill_rate => NULL
,p_average_bill_rate => NULL
,p_average_cost_rate => NULL
,p_project_assignment_id => -1
,p_plan_error_code => NULL
,p_total_plan_revenue => NULL
,p_total_plan_raw_cost => NULL
,p_total_plan_burdened_cost => NULL
,p_total_plan_quantity => NULL
,p_average_discount_percentage => NULL
,p_total_borrowed_revenue => NULL
,p_total_tp_revenue_in => NULL
,p_total_tp_revenue_out => NULL
,p_total_revenue_adj => NULL
,p_total_lent_resource_cost => NULL
,p_total_tp_cost_in => NULL
,p_total_tp_cost_out => NULL
,p_total_cost_adj => NULL
,p_total_unassigned_time_cost => NULL
,p_total_utilization_percent => NULL
,p_total_utilization_hours => NULL
,p_total_utilization_adj => NULL
,p_total_capacity => NULL
,p_total_head_count => NULL
,p_total_head_count_adj => NULL
,p_resource_assignment_type => PA_FP_CONSTANTS_PKG.G_USER_ENTERED
,p_total_project_raw_cost => NULL
,p_total_project_burdened_cost => NULL
,p_total_project_revenue => NULL
,p_parent_assignment_id => NULL
,x_row_id => l_row_id
,x_return_status => l_return_status);
pa_debug.g_err_stage:= 'Exception while inserting a row into pa_resource_assignments;';
pa_debug.write('Insert_Resource_Assignment: ' || l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
END Insert_Resource_Assignment;
SELECT parent_task_id,
top_task_id
FROM pa_tasks
WHERE task_id = c_impacted_task_id;
SELECT pra.resource_assignment_id,
pra.wbs_element_version_id, -- This column is selected so that it can be passed, to create_res_task_maps. One for source and one for target.
pra.wbs_element_version_id, -- This would be null for budgets and forecasts!
pra.project_assignment_id, -- This would be -1 for Budgets and Forecasts
pra.planning_start_date,
pra.planning_end_date,
pra.schedule_start_date,
pra.schedule_end_date,
pra.resource_list_member_id -- Bug 3615617
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id;
SELECT pra.resource_assignment_id,
pra.wbs_element_version_id, -- This column is selected so that it can be passed, to create_res_task_maps. One for source and one for target.
pra.wbs_element_version_id, -- This would be null for budgets and forecasts!
pra.project_assignment_id, -- This would be -1 for Budgets and Forecasts
pra.planning_start_date,
pra.planning_end_date,
pra.schedule_start_date,
pra.schedule_end_date,
pra.resource_list_member_id -- Bug 3615617
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = l_ci_apprv_cw_bv_id
AND pra.task_id IN (SELECT t.task_id
FROM pa_tasks t
WHERE t.project_id = p_project_id
CONNECT BY PRIOR t.task_id = t.parent_task_id
START WITH t.task_id = c_task_id);
SELECT bv.fin_plan_type_id
INTO l_fin_plan_type_id
FROM pa_budget_versions bv
WHERE budget_version_id = p_budget_version_id;
/* No record are there to be inserted. Ideally, control should never come
* here since is_create_ci_version_Allowed should have caught this case and
* thrown an error! */
null;
/* No record are there to be inserted. Ideally, control should never come here since
* is_create_ci_version_Allowed should have caught this case and thrown an error! */
null;
When automatic resource selection is enabled for an option,this
api inserts resources or resource group elements to a project
or a pl/sql table of tasks based on the option planning level that
is passed.
The api can also be called for an entire option in which case
resource/ resource groups elements would be added to all the
plannable tasks for that element type,fp option combination.
NOTE(S):-
1. If the option planning level is project, the task_id tbl should
contain one and only one record and that should be zero as we
enter 0(zero) for task_id column in pa_fp_elements for project
level planning options.
==================================================================*/
PROCEDURE Add_resources_automatically
( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
,p_element_type IN pa_fp_elements.element_type%TYPE
,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
,p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE
,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
,p_entire_option IN VARCHAR2
,p_element_task_id_tbl IN pa_fp_elements_pub.l_task_id_tbl_typ
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER := 0;
SELECT project_id
,fin_plan_type_id
,fin_plan_version_id
FROM pa_proj_fp_options
WHERE proj_fp_options_id = c_proj_fp_options_id;
SELECT task_id
,top_task_id
FROM pa_fp_elements
WHERE proj_fp_options_id = c_proj_fp_options_id
AND element_type = c_element_type
AND resource_list_member_id = 0
AND plannable_flag = 'Y';
SELECT resource_list_member_id
FROM pa_resource_list_members
WHERE resource_list_id = c_resource_list_id
AND resource_type_code <> PA_FP_CONSTANTS_PKG.G_UNCLASSIFIED
AND enabled_flag='Y' -- bug 3289243
AND display_flag='Y'; -- bug 3289243
SELECT resource_list_member_id
FROM pa_resource_list_members
WHERE resource_list_id = c_resource_list_id
AND resource_type_code <> PA_FP_CONSTANTS_PKG.G_UNCLASSIFIED
AND enabled_flag='Y' -- bug 3289243
AND display_flag='Y' -- bug 3289243
AND parent_member_id IS NOT NULL; -- to filter all the resource group level records
SELECT resource_list_member_id
FROM pa_resource_list_members
WHERE resource_list_id = c_resource_list_id
AND resource_type_code <> PA_FP_CONSTANTS_PKG.G_UNCLASSIFIED
AND enabled_flag='Y' -- bug 3289243
AND display_flag='Y' -- bug 3289243
AND parent_member_id IS NULL; -- to filter all the resource level records
SELECT top_task_id
INTO l_top_task_id_tbl(l_task_tbl_index)
FROM pa_fp_elements
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = p_element_type
AND task_id = l_task_id_tbl(l_task_tbl_index)
AND resource_list_member_id = 0
AND plannable_flag = 'Y';
For each task_id in the task_id table we need to insert
all the resource_list_memebers fetched in pa_fp_elements table.
*/
IF p_pa_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'for each task in task_id_tbl inserting all the rlmids fetched';
/* Insert all the resource_list_members fetched for each task */
FORALL l_rlm_tbl_index IN l_res_list_mem_id_tbl.first .. l_res_list_mem_id_tbl.last
INSERT INTO pa_fp_elements
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
VALUES
(pa_fp_elements_s.nextval
,p_proj_fp_options_id
,proj_fp_options_info_rec.project_id
,proj_fp_options_info_rec.fin_plan_type_id
,p_element_type
,proj_fp_options_info_rec.fin_plan_version_id
,l_task_id_tbl(l_task_tbl_index) -- task_id
,l_top_task_id_tbl(l_task_tbl_index) -- top_task_id
,l_res_list_mem_id_tbl(l_rlm_tbl_index) -- resource_list_member_id
,NULL -- top_task_planning_level
,NULL -- resource_planning_level
,'Y' -- plannable_flag
,NULL -- resources_planned_for_task
,'N' -- plan_amount_exists_flag
,'Y' -- tmp_plannable_flag
,NULL -- tmp_top_task_planning_level
,1 -- record_version_number
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id);
pa_debug.g_err_stage:= 'Bulk updating all the tasks to reflect resource selection status';
UPDATE pa_fp_elements
SET resources_planned_for_task = 'Y'
,resource_planning_level = p_res_planning_level
,record_version_number = record_version_number + 1
,last_update_date = sysdate
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_login = FND_GLOBAL.LOGIN_ID
WHERE proj_fp_options_id = p_proj_fp_options_id
AND element_type = p_element_type
AND task_id = l_task_id_tbl(l_task_tbl_index)
AND resource_list_member_id = 0;
/* Bug 2920954 - This procedure deletes all the planning elements
(pa_fp_elements/pa_resource_assignments) of this task and all
its child tasks. This is called during the task deletion. These
tasks would have plannable plan_amount_exists_flag as 'N'. Its
assumed that the check apis would have been called to ensure
that deletion of p_task_id is allowed. One main check in the check api
is that p_task_id should not be present in pa_resource_assignments
of a BASELINED version since we should not be touching RA table
of BASELINED versions. When plan amounts donot exists, pa_proj_periods_denorm
will not contain any data for that task.
Bug 2976168. Delete from pa_fp_excluded_elements */
PROCEDURE Delete_task_elements
( p_task_id IN pa_tasks.task_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_msg_count NUMBER := 0;
l_records_deleted NUMBER;
pa_debug.set_curr_function( p_function => 'delete_task_elements',
p_debug_mode => l_debug_mode );
DELETE FROM pa_resource_assignments r
WHERE r.task_id IN (SELECT t.task_id
FROM pa_tasks t
CONNECT BY PRIOR t.task_id = t.parent_task_id
START WITH t.task_id = p_task_id);
l_records_deleted := sql%rowcount;
pa_debug.g_err_stage:= To_char(l_records_deleted) || ' records deleted.';
pa_debug.g_err_stage:= 'Exiting delete_task_elements';
,p_procedure_name => 'delete_task_elements'
,p_error_text => x_msg_data);
END delete_task_elements;
This API will be used to decide whether to insert a task in fp elements table or not. This api will also
provide the plannable flag and task planning level of all the tasks that are eligible for insertion.
*/
PROCEDURE Get_Task_Element_Attributes
( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
,p_element_type IN pa_fp_elements.element_type%TYPE
,p_task_id IN pa_fp_elements.task_id%TYPE
,p_top_task_id IN pa_fp_elements.top_task_id%TYPE
,p_task_level IN VARCHAR2
,p_option_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
,x_task_inclusion_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_task_plannable_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_top_task_planning_level OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
--Declare the variables which are required as a standard
l_msg_count NUMBER := 0;
SELECT pfe.top_task_planning_level,
pfe.plannable_flag
FROM pa_fp_elements pfe
WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
AND pfe.element_type = p_element_type
AND pfe.task_id = c_task_id
AND pfe.resource_list_member_id = 0;
SELECT 'Y'
FROM pa_fp_excluded_elements pfe
WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
AND pfe.element_type = p_element_type
AND pfe.task_id IN (c_task_id,c_top_task_id);
this top task should be inserted in pa_fp_elements with plannable flag
as N. Resource elements should not be added for this top task */
x_task_inclusion_flag := 'Y';
lowest task. If we need to handle this case, we have to first insert the
p_top_task_id record into pa_fp_elements and then the p_task_id record. */
x_task_inclusion_flag := 'N';
task planning level. If the new task is plannable, task level record is inserted into
fp elements table and if resources are to be added automatically, the procedure
ADD_RESOURCES_AUTOMATICALLY api is called. Also, resource assignments and fp elements that
were present for original task that was earlier plannable but now unplannable is deleted.
Bug 2976168. Changed the signature of the API. Also the logic of deriving is a task is
plannable or not is moved to Get_Task_Element_Attributes Api
Bug 2989900. In case of CI versions, the tasks would be made plannable only if the task
is an impacted task or a child task of impacted task */
PROCEDURE add_tasks_to_option
( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
,p_element_type IN pa_fp_elements.element_type%TYPE
,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER := 0;
SELECT pfo.project_id,
pfo.fin_plan_type_id,
pfo.fin_plan_version_id,
pfo.fin_plan_option_level_code,
DECODE(p_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST,pfo.cost_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL,pfo.all_fin_plan_level_code,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, pfo.revenue_fin_plan_level_code) fin_plan_level_code,
DECODE(p_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST,pfo.select_cost_res_auto_flag,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, pfo.select_all_res_auto_flag,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, pfo.select_rev_res_auto_flag) auto_res_selection_flag,
DECODE(p_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, pfo.cost_res_planning_level,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, pfo.all_res_planning_level,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, pfo.revenue_res_planning_level) auto_res_plan_level,
DECODE(p_element_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, pfo.cost_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, pfo.all_resource_list_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, pfo.revenue_resource_list_id) resource_list_id
FROM pa_proj_fp_options pfo
WHERE pfo.proj_fp_options_id = p_proj_fp_options_id;
be inserted*/
--For Bug 2976168.
l_task_inclusion_flag VARCHAR2(1); /*Required to know whether the task can be is
eligible for inserting into pa_fp_elements or not*/
CURSOR ci_version_info_cur
(c_plan_version_id pa_proj_fp_options.fin_plan_version_id%TYPE)
IS
SELECT bv.ci_id,
impacted_task_id
FROM pa_budget_versions bv,
pa_ci_impacts ci
WHERE budget_version_id = c_plan_version_id
AND bv.ci_id = ci.ci_id
AND bv.ci_id IS NOT NULL;
SELECT task_id
FROM pa_tasks t
WHERE t.project_id = c_project_id
START WITH t.task_id = c_impacted_task_id
CONNECT BY prior t.task_id = t.parent_task_id;
task in pa_fp_elements, we shouldnt delete the top task
record. We just have to set the plannable flag
of this task to N. */
DELETE pa_fp_elements pfe
WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
AND pfe.element_type = p_element_type
AND pfe.task_id = p_tasks_tbl(i).parent_task_id
AND pfe.resource_list_member_id <> 0;
pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records deleted from pa_fp_elements';
UPDATE pa_fp_elements pfe
SET pfe.plannable_flag = 'N',
pfe.tmp_plannable_flag = 'N',
pfe.resources_planned_for_task = Null,
pfe.record_version_number = pfe.record_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE pfe.proj_fp_options_id = p_proj_fp_options_id
AND pfe.element_type = p_element_type
AND pfe.task_id = p_tasks_tbl(i).parent_task_id
AND pfe.resource_list_member_id = 0;
pa_debug.g_err_stage:= to_char(sql%rowcount) || ' records updated in pa_fp_elements';
/* If this option corresponds to a plan version, we should delete the resource assignments also
for p_task_id. */
DELETE pa_resource_assignments pra
WHERE pra.budget_version_id = proj_fp_options_rec.fin_plan_version_id
AND pra.task_id = p_tasks_tbl(i).parent_task_id
AND pra.resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED;
pa_debug.g_err_stage:= 'PLAN_VERSION option. ' || to_char(sql%rowcount) || ' records deleted from pa_resource_assignments';
/* If p_task_id is not a top task then it would not be required to delete here
as we BULK delete in make_new_tasks_plannable api for this case */
Null;
INSERT INTO PA_FP_ELEMENTS
(PROJ_FP_ELEMENTS_ID
,PROJ_FP_OPTIONS_ID
,PROJECT_ID
,FIN_PLAN_TYPE_ID
,ELEMENT_TYPE
,FIN_PLAN_VERSION_ID
,TASK_ID
,TOP_TASK_ID
,RESOURCE_LIST_MEMBER_ID
,TOP_TASK_PLANNING_LEVEL
,RESOURCE_PLANNING_LEVEL
,PLANNABLE_FLAG
,RESOURCES_PLANNED_FOR_TASK
,PLAN_AMOUNT_EXISTS_FLAG
,TMP_PLANNABLE_FLAG
,TMP_TOP_TASK_PLANNING_LEVEL
,RECORD_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
VALUES
(pa_fp_elements_s.nextval
,p_proj_fp_options_id
,proj_fp_options_rec.project_id
,proj_fp_options_rec.fin_plan_type_id
,p_element_type
,proj_fp_options_rec.fin_plan_version_id
,p_tasks_tbl(i).task_id
,p_tasks_tbl(i).top_task_id
,0 -- resource_list_member_id
,l_top_task_planning_level -- top_task_planning_level
,decode(l_task_plannable_flag,
'N',Null,
proj_fp_options_Rec.auto_res_plan_level) -- resource_planning_level
,l_task_plannable_flag -- plannable_flag
,proj_fp_options_rec.auto_res_selection_flag -- resources_planned_for_task
,'N' -- plan_amount_exists_flag
,l_task_plannable_flag -- tmp_plannable_flag
,l_top_task_planning_level -- tmp_top_task_planning_level
,1
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID);
IF proj_fp_options_rec.auto_res_selection_flag = 'Y' THEN
/* We should be adding resources only if p_task_id is a plannable task record.
It should not be added to a top task record that is plannable at lowest task level */
IF l_task_plannable_flag = 'Y' THEN
/* If automatic resource selection is 'Y' for the proj_fp_option/element type,
then resource elements need to be added */
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Calling add_resources_automatically...';
END IF; /* proj_fp_options_rec.auto_res_selection_flag = 'Y' */
SELECT pfo.proj_fp_options_id,
pfo.fin_plan_option_level_code,
pfo.fin_plan_preference_code,
pfo.cost_fin_plan_level_code,
pfo.revenue_fin_plan_level_code,
pfo.all_fin_plan_level_code,
pfo.fin_plan_version_id
FROM pa_proj_fp_options pfo
WHERE pfo.proj_fp_options_id = c_proj_fp_options_id;
/* If the task is a middle level task delete all the references of that task from
pa_fp_elements and pa_resource_assignments */
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'task_id ' || p_tasks_tbl(i).task_id;
DELETE
FROM pa_fp_elements
WHERE task_id = p_tasks_tbl(i).task_id
AND proj_fp_options_id = p_refresh_fp_options_tbl(k); /* We are deleting irrespective of element_type */
pa_debug.g_err_stage:= 'No of records deleted from pa_fp_elements ' ||SQL%ROWCOUNT;
DELETE
FROM pa_resource_assignments pra
WHERE pra.task_id = p_tasks_tbl(i).task_id
AND pra.budget_version_id in (SELECT pfo.fin_plan_version_id
FROM pa_proj_fp_options pfo
WHERE pfo.proj_fp_options_id =
p_refresh_fp_options_tbl(k))
AND pra.resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED;
pa_debug.g_err_stage:= 'No of records deleted from pa_resource_assignments ' ||SQL%ROWCOUNT;
'INSERT','REPARENT','DELETE'
Please note that p_impacted_tasks_tbl has no relation to the impacted task of a CI version
When action is 'INSERT' the plsql record should contain the following:
Impacted_task_id,
New_parent_task_id,
Top_task_id
When the action is 'REPARENT' the plsql record should contain the following:
Impacted_task_id,
Old_parent_task_id,
New_parent_task_id,
Top_task_id
When action is 'DELETE' the plsql record should contain the following:
Impacted_task_id,
Old_parent_task_id,
Top_task_id
Assumptions:
1. A task id cannot be present more than once as impacted_task_id in the
p_impacted_tasks_tbl input parameter.
2. When the action is DELETE, only the task that is deleted is passed in the
plsql table and not all the tasks below the deleted task.
3. The order of task records in the input plsql table p_impacted_tasks_tbl
under a top task is same as the order of the tasks in the WBS, i.e.,
task 2.0 would appear before any of its lowest tasks in the plsql table,
if any. Its ok, if task 3.0 appears after task 4.0. The assumption is that
3.1 cannot appear before 3.0.
4. When action is INSERT and REPARENT, we assume that the operation INSERT/REPARENT
operation has already been done for the tasks. But when action is DELETE,
we assume that the tasks would be deleted only after the bulk api is called.
5. This api would not be called for organization forecasting projects
Bug 2976168. This api is NOT being called now for INSERT and REPARENT. This api will be
called only in the case of DELETE.
*/
PROCEDURE maintain_plannable_tasks
(p_project_id IN pa_projects_all.project_id%TYPE
,p_impacted_tasks_tbl IN l_impacted_task_in_tbl_typ
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
l_msg_count NUMBER := 0;
L_ACTION_INSERT CONSTANT VARCHAR2(30) := 'INSERT';
L_ACTION_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
/* Indicates if task has to made be plannable. Used when action is INSERT */
l_make_task_plannable VARCHAR2(1);
l_records_deleted NUMBER;
select 1
from sys.dual
where exists
(select 1 from pa_proj_fp_options
where project_id = p_project_id);
SELECT pfo.proj_fp_options_id
FROM pa_proj_fp_options pfo
WHERE pfo.project_id = p_project_id
AND pfo.fin_plan_option_level_code <> PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION
UNION ALL
SELECT pfo.proj_fp_options_id
FROM pa_budget_versions bv,
pa_proj_fp_options pfo
WHERE bv.project_id = p_project_id
AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION
AND bv.budget_status_code <> PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_BASELINED /* Should not modify baselined versions */
AND pfo.project_id = bv.project_id
AND pfo.fin_plan_type_id = bv.fin_plan_type_id
AND pfo.fin_plan_version_id = bv.budget_version_id;
SELECT pt.top_task_id top_task_id
,pt.parent_task_id parent_task_id
,DECODE(c_task_id,
pt.top_task_id,L_TASK_LEVEL_TOP,
L_TASK_LEVEL_LOWEST) task_level
FROM pa_tasks pt
WHERE pt.task_id = c_task_id;
/* Middle level task and this need NOT be inserted into pa_fp_elements */
return 'N';
/* Multiple tasks can be inserted, reparented or deleted.
Caching is implemented so that
1. We need not call make_new_tasks_plannable for a task that is already made plannable.
2. We do not have to call delete_task_elements for the new parent task (to make it
unplannable) if it has already been made unplannable.
To achieve this check if the inserted task is a middle level task. Since middle level
task need not be inserted, we can store middle level tasks (new_parent_task_id) in
l_middle_task_tbl plsql table. If l_middle_task_tbl plsql table doesnt contain an
entry for a task id, only then we refer the database to check if the task is a
middle level task and if so, cache it in the plsql table. */
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Identifying middle level tasks by looping the p_impacted_tasks_tbl';
IF p_impacted_tasks_tbl(i).action = L_ACTION_INSERT THEN
IF p_impacted_tasks_tbl(i).impacted_task_id IS NULL OR
(p_impacted_tasks_tbl(i).impacted_task_id <> p_impacted_tasks_tbl(i).top_task_id AND
p_impacted_tasks_tbl(i).new_parent_task_id IS NULL) OR
p_impacted_tasks_tbl(i).top_task_id IS NULL THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'For INSERT action : ' ||
'Impacted_task_id, New_parent_task_id, Top_task_id should be passed';
/* Delete planning elements and resource assignments for new parent task id.
Pls note that delete task elements deletes the task and its children from
all plan options. Hence we cannot call it since it might delete a
plannable impacted task also from pa_fp_elements and pa_resource_assignments */
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Deleting task fp elements for new parent task id';
DELETE FROM pa_fp_elements e
WHERE e.task_id = p_impacted_tasks_tbl(i).new_parent_task_id;
l_records_deleted := sql%rowcount;
pa_debug.g_err_stage:= To_char(l_records_deleted) || ' records deleted.';
IF l_records_deleted <> 0 THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Deleting from pa_resource_assignments for task id ' || to_char(p_impacted_tasks_tbl(i).new_parent_task_id);
DELETE FROM pa_resource_assignments r
WHERE r.task_id = p_impacted_tasks_tbl(i).new_parent_task_id;
l_records_deleted := sql%rowcount;
pa_debug.g_err_stage:= To_char(l_records_deleted) || ' records deleted.';
ELSIF p_impacted_tasks_tbl(i).action = L_ACTION_DELETE THEN
IF p_impacted_tasks_tbl(i).impacted_task_id IS NULL OR
(p_impacted_tasks_tbl(i).impacted_task_id <> p_impacted_tasks_tbl(i).top_task_id AND
p_impacted_tasks_tbl(i).old_parent_task_id IS NULL) OR
p_impacted_tasks_tbl(i).top_task_id IS NULL THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'For DELETE action : ' ||
'Impacted_task_id, old_parent_task_id, Top_task_id should be passed';
/* If impacted task has already been deleted then
nothing needs to be done for this */
IF NOT(l_tasks_removed_tbl.exists(p_impacted_tasks_tbl(i).impacted_task_id)) THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Calling delete_task_elements for impacted task id';
/* Calling pa_fp_elements_pub.delete_task_elements for impacted task id */
PA_FP_ELEMENTS_PUB.Delete_task_elements
( p_task_id => p_impacted_Tasks_tbl(i).impacted_task_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage:= 'Error returned by delete_task_elements for task_id ' ||
p_impacted_tasks_tbl(i).impacted_task_id;
/* Since tasks would not have yet been deleted, we need to check if the new parent would
still be a middle level task after the impacted task id is deleted */
DECLARE
cursor c1 is
select 'N'
from sys.dual
where exists (SELECT null
FROM pa_tasks
where parent_task_id = p_impacted_tasks_tbl(i).old_parent_task_id
and task_id <> p_impacted_tasks_tbl(i).impacted_task_id);
UPDATE pa_proj_fp_options pfo
SET pfo.record_version_number = pfo.record_version_number + 1,
pfo.last_update_date = sysdate,
pfo.last_updated_by = FND_GLOBAL.USER_ID,
pfo.last_update_login = FND_GLOBAL.LOGIN_ID
WHERE pfo.proj_fp_options_id = l_all_fp_options_tbl(i);
/* Since resource assignments might have been deleted and recreated
for the new task, the version has been modified and its
record version number has to be increased */
FORALL i in l_all_fp_options_tbl.first..l_all_fp_options_tbl.last
UPDATE pa_budget_versions bv
SET bv.record_version_number = bv.record_version_number + 1,
bv.last_update_date = sysdate,
bv.last_updated_by = FND_GLOBAL.USER_ID,
bv.last_update_login = FND_GLOBAL.LOGIN_ID
WHERE bv.budget_version_id in (SELECT pfo.fin_plan_version_id
FROM pa_proj_fp_options pfo
WHERE pfo.proj_fp_options_id = l_all_fp_options_tbl(i));