17: based on a budget version id. These local variables are required for processing in other APIs.
18: Hence this procedure is called wherever local variables need to be populated.
19: =================================================================================================*/
20:
21: P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
22:
23: PROCEDURE POPULATE_LOCAL_VARS(p_budget_version_id IN NUMBER
24: ,x_project_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
25: ,x_resource_list_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
49: BEGIN
50:
51: -- Set the error stack.
52:
53: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Populate_Local_Vars');
54:
55: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
56:
57: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
53: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Populate_Local_Vars');
54:
55: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
56:
57: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
58: l_debug_mode := NVL(l_debug_mode, 'Y');
59:
60: -- Initialize the return status to success
61: x_return_status := FND_API.G_RET_STS_SUCCESS;
58: l_debug_mode := NVL(l_debug_mode, 'Y');
59:
60: -- Initialize the return status to success
61: x_return_status := FND_API.G_RET_STS_SUCCESS;
62: IF P_PA_DEBUG_MODE = 'Y' THEN
63: pa_debug.set_process('POPULATE_LOCAL_VARS: ' || 'PLSQL','LOG',l_debug_mode);
64: END IF;
65:
66: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Populate_Local_Vars ';
59:
60: -- Initialize the return status to success
61: x_return_status := FND_API.G_RET_STS_SUCCESS;
62: IF P_PA_DEBUG_MODE = 'Y' THEN
63: pa_debug.set_process('POPULATE_LOCAL_VARS: ' || 'PLSQL','LOG',l_debug_mode);
64: END IF;
65:
66: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Populate_Local_Vars ';
67: IF P_PA_DEBUG_MODE = 'Y' THEN
62: IF P_PA_DEBUG_MODE = 'Y' THEN
63: pa_debug.set_process('POPULATE_LOCAL_VARS: ' || 'PLSQL','LOG',l_debug_mode);
64: END IF;
65:
66: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Populate_Local_Vars ';
67: IF P_PA_DEBUG_MODE = 'Y' THEN
68: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,2);
69: END IF;
70:
63: pa_debug.set_process('POPULATE_LOCAL_VARS: ' || 'PLSQL','LOG',l_debug_mode);
64: END IF;
65:
66: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Populate_Local_Vars ';
67: IF P_PA_DEBUG_MODE = 'Y' THEN
68: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,2);
69: END IF;
70:
71: /* Check for Budget Version ID not being NULL. */
64: END IF;
65:
66: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Populate_Local_Vars ';
67: IF P_PA_DEBUG_MODE = 'Y' THEN
68: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,2);
69: END IF;
70:
71: /* Check for Budget Version ID not being NULL. */
72: IF ( p_budget_version_id IS NULL) THEN
69: END IF;
70:
71: /* Check for Budget Version ID not being NULL. */
72: IF ( p_budget_version_id IS NULL) THEN
73: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
74: IF P_PA_DEBUG_MODE = 'Y' THEN
75: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,5);
76: END IF;
77: x_return_status := FND_API.G_RET_STS_ERROR;
70:
71: /* Check for Budget Version ID not being NULL. */
72: IF ( p_budget_version_id IS NULL) THEN
73: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
74: IF P_PA_DEBUG_MODE = 'Y' THEN
75: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,5);
76: END IF;
77: x_return_status := FND_API.G_RET_STS_ERROR;
78: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
71: /* Check for Budget Version ID not being NULL. */
72: IF ( p_budget_version_id IS NULL) THEN
73: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
74: IF P_PA_DEBUG_MODE = 'Y' THEN
75: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,5);
76: END IF;
77: x_return_status := FND_API.G_RET_STS_ERROR;
78: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
79: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
82:
83: /* Populating the Resource List ID, Uncategorized flag of the Resource List and the Group Type ID
84: of the Resource list attached to the Budget version. */
85:
86: pa_debug.g_err_stage := 'Getting the value of the local variables.';
87: IF P_PA_DEBUG_MODE = 'Y' THEN
88: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
89: END IF;
90:
83: /* Populating the Resource List ID, Uncategorized flag of the Resource List and the Group Type ID
84: of the Resource list attached to the Budget version. */
85:
86: pa_debug.g_err_stage := 'Getting the value of the local variables.';
87: IF P_PA_DEBUG_MODE = 'Y' THEN
88: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
89: END IF;
90:
91: SELECT pbv.resource_list_id
84: of the Resource list attached to the Budget version. */
85:
86: pa_debug.g_err_stage := 'Getting the value of the local variables.';
87: IF P_PA_DEBUG_MODE = 'Y' THEN
88: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
89: END IF;
90:
91: SELECT pbv.resource_list_id
92: ,prl.uncategorized_flag
110: /* Populating the variable l_uncat_rlm_id which contains the Resource List Member ID if
111: the Resource List attached is uncategorized, else its value is 0. */
112:
113: /* M21-AUG: made call to this procedure parameterized */
114: pa_debug.g_err_stage := 'calling pa_get_resource.get_uncateg_resource_info';
115: IF P_PA_DEBUG_MODE = 'Y' THEN
116: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
117: END IF;
118:
111: the Resource List attached is uncategorized, else its value is 0. */
112:
113: /* M21-AUG: made call to this procedure parameterized */
114: pa_debug.g_err_stage := 'calling pa_get_resource.get_uncateg_resource_info';
115: IF P_PA_DEBUG_MODE = 'Y' THEN
116: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
117: END IF;
118:
119: pa_get_resource.get_uncateg_resource_info(p_resource_list_id => l_uncat_res_list_id
112:
113: /* M21-AUG: made call to this procedure parameterized */
114: pa_debug.g_err_stage := 'calling pa_get_resource.get_uncateg_resource_info';
115: IF P_PA_DEBUG_MODE = 'Y' THEN
116: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
117: END IF;
118:
119: pa_get_resource.get_uncateg_resource_info(p_resource_list_id => l_uncat_res_list_id
120: ,p_resource_list_member_id => l_uncat_res_list_mem_id
132:
133: END IF;
134:
135: /* Getting the Planning Level of the Budget Version ID. */
136: pa_debug.g_err_stage := 'calling pa_fin_plan_utils.Get_Fin_Plan_Level_Code';
137: IF P_PA_DEBUG_MODE = 'Y' THEN
138: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
139: END IF;
140:
133: END IF;
134:
135: /* Getting the Planning Level of the Budget Version ID. */
136: pa_debug.g_err_stage := 'calling pa_fin_plan_utils.Get_Fin_Plan_Level_Code';
137: IF P_PA_DEBUG_MODE = 'Y' THEN
138: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
139: END IF;
140:
141: x_planning_level := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(p_budget_version_id);
134:
135: /* Getting the Planning Level of the Budget Version ID. */
136: pa_debug.g_err_stage := 'calling pa_fin_plan_utils.Get_Fin_Plan_Level_Code';
137: IF P_PA_DEBUG_MODE = 'Y' THEN
138: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,pa_debug.g_err_stage,3);
139: END IF;
140:
141: x_planning_level := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(p_budget_version_id);
142: /* M23-AUG: changed following select to function call
147: FROM pa_proj_fp_options
148: WHERE fin_plan_version_id = p_budget_version_id;
149: */
150:
151: pa_debug.reset_err_stack;
152:
153: EXCEPTION
154: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
155: l_msg_count := FND_MSG_PUB.count_msg;
165: x_msg_count := l_msg_count;
166: ELSE
167: x_msg_count := l_msg_count;
168: END IF;
169: pa_debug.reset_err_stack;
170: RAISE;
171: WHEN OTHERS THEN
172: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173: x_msg_count := 1;
174: x_msg_data := SQLERRM;
175: FND_MSG_PUB.add_exc_msg
176: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
177: ,p_procedure_name => 'POPULATE_LOCAL_VARS');
178: IF P_PA_DEBUG_MODE = 'Y' THEN
179: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,SQLERRM,5);
180: END IF;
181: pa_debug.reset_err_stack;
182:
175: FND_MSG_PUB.add_exc_msg
176: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
177: ,p_procedure_name => 'POPULATE_LOCAL_VARS');
178: IF P_PA_DEBUG_MODE = 'Y' THEN
179: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,SQLERRM,5);
180: END IF;
181: pa_debug.reset_err_stack;
182:
183: raise FND_API.G_EXC_UNEXPECTED_ERROR;
177: ,p_procedure_name => 'POPULATE_LOCAL_VARS');
178: IF P_PA_DEBUG_MODE = 'Y' THEN
179: pa_debug.write('POPULATE_LOCAL_VARS: ' || l_module_name,SQLERRM,5);
180: END IF;
181: pa_debug.reset_err_stack;
182:
183: raise FND_API.G_EXC_UNEXPECTED_ERROR;
184:
185: END POPULATE_LOCAL_VARS;
234:
235: /* delete older records not to be used in this table. */
236: DELETE from pa_fp_ra_map_tmp;
237:
238: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records';
239: IF P_PA_DEBUG_MODE = 'Y' THEN
240: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
241: END IF;
242:
235: /* delete older records not to be used in this table. */
236: DELETE from pa_fp_ra_map_tmp;
237:
238: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records';
239: IF P_PA_DEBUG_MODE = 'Y' THEN
240: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
241: END IF;
242:
243: /* Insert Parent Records into PA_FP_RA_MAP_TMP table with the system_reference1
236: DELETE from pa_fp_ra_map_tmp;
237:
238: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records';
239: IF P_PA_DEBUG_MODE = 'Y' THEN
240: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
241: END IF;
242:
243: /* Insert Parent Records into PA_FP_RA_MAP_TMP table with the system_reference1
244: as that passed to this procedure. */
242:
243: /* Insert Parent Records into PA_FP_RA_MAP_TMP table with the system_reference1
244: as that passed to this procedure. */
245:
246: pa_debug.g_err_stage := 'Inserting recs into pa_fp_ra_map_tmp';
247: IF P_PA_DEBUG_MODE = 'Y' THEN
248: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
249: END IF;
250:
243: /* Insert Parent Records into PA_FP_RA_MAP_TMP table with the system_reference1
244: as that passed to this procedure. */
245:
246: pa_debug.g_err_stage := 'Inserting recs into pa_fp_ra_map_tmp';
247: IF P_PA_DEBUG_MODE = 'Y' THEN
248: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
249: END IF;
250:
251: IF PX_INSERTING_RES_GROUP_LEVEL THEN
244: as that passed to this procedure. */
245:
246: pa_debug.g_err_stage := 'Inserting recs into pa_fp_ra_map_tmp';
247: IF P_PA_DEBUG_MODE = 'Y' THEN
248: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
249: END IF;
250:
251: IF PX_INSERTING_RES_GROUP_LEVEL THEN
252: /* we need to insert only last that is resource level records in tmp table.
316: END IF;
317:
318: /* we need to insert only those records which match the current wbs level */
319:
320: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records now deleting previous level records ';
321: IF P_PA_DEBUG_MODE = 'Y' THEN
322: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
323: END IF;
324:
317:
318: /* we need to insert only those records which match the current wbs level */
319:
320: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records now deleting previous level records ';
321: IF P_PA_DEBUG_MODE = 'Y' THEN
322: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
323: END IF;
324:
325: EXCEPTION
318: /* we need to insert only those records which match the current wbs level */
319:
320: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records now deleting previous level records ';
321: IF P_PA_DEBUG_MODE = 'Y' THEN
322: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,pa_debug.g_err_stage,3);
323: END IF;
324:
325: EXCEPTION
326: WHEN OTHERS THEN
326: WHEN OTHERS THEN
327: FND_MSG_PUB.add_exc_msg
328: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
329: ,p_procedure_name => 'Insert_Parent_Rec_Tmp');
330: IF P_PA_DEBUG_MODE = 'Y' THEN
331: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,SQLERRM,5);
332: END IF;
333:
334: raise FND_API.G_EXC_UNEXPECTED_ERROR;
327: FND_MSG_PUB.add_exc_msg
328: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
329: ,p_procedure_name => 'Insert_Parent_Rec_Tmp');
330: IF P_PA_DEBUG_MODE = 'Y' THEN
331: pa_debug.write('INSERT_PARENT_REC_TMP: ' || l_module_name,SQLERRM,5);
332: END IF;
333:
334: raise FND_API.G_EXC_UNEXPECTED_ERROR;
335:
463: BEGIN
464:
465: -- Initialize and Set the error stack.
466: FND_MSG_PUB.initialize;
467: IF P_PA_DEBUG_MODE = 'Y' THEN
468: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Budget_Version');
469: END IF;
470:
471: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
464:
465: -- Initialize and Set the error stack.
466: FND_MSG_PUB.initialize;
467: IF P_PA_DEBUG_MODE = 'Y' THEN
468: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Budget_Version');
469: END IF;
470:
471: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
472: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
468: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Budget_Version');
469: END IF;
470:
471: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
472: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
473: l_debug_mode := NVL(l_debug_mode, 'Y');
474:
475: -- Initialize the return status to success
476: x_return_status := FND_API.G_RET_STS_SUCCESS;
474:
475: -- Initialize the return status to success
476: x_return_status := FND_API.G_RET_STS_SUCCESS;
477:
478: IF P_PA_DEBUG_MODE = 'Y' THEN
479: pa_debug.set_process('ROLLUP_BUDGET_VERSION: ' || 'PLSQL','LOG',l_debug_mode);
480: END IF;
481: x_msg_count := 0;
482:
475: -- Initialize the return status to success
476: x_return_status := FND_API.G_RET_STS_SUCCESS;
477:
478: IF P_PA_DEBUG_MODE = 'Y' THEN
479: pa_debug.set_process('ROLLUP_BUDGET_VERSION: ' || 'PLSQL','LOG',l_debug_mode);
480: END IF;
481: x_msg_count := 0;
482:
483: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Budget_Version';
479: pa_debug.set_process('ROLLUP_BUDGET_VERSION: ' || 'PLSQL','LOG',l_debug_mode);
480: END IF;
481: x_msg_count := 0;
482:
483: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Budget_Version';
484: IF P_PA_DEBUG_MODE = 'Y' THEN
485: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
486: END IF;
487:
480: END IF;
481: x_msg_count := 0;
482:
483: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Budget_Version';
484: IF P_PA_DEBUG_MODE = 'Y' THEN
485: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
486: END IF;
487:
488: /* Check for Budget Version ID not being NULL. */
481: x_msg_count := 0;
482:
483: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Budget_Version';
484: IF P_PA_DEBUG_MODE = 'Y' THEN
485: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
486: END IF;
487:
488: /* Check for Budget Version ID not being NULL. */
489: IF ( p_budget_version_id IS NULL) THEN
486: END IF;
487:
488: /* Check for Budget Version ID not being NULL. */
489: IF ( p_budget_version_id IS NULL) THEN
490: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
491: IF P_PA_DEBUG_MODE = 'Y' THEN
492: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
493: END IF;
494: x_return_status := FND_API.G_RET_STS_ERROR;
487:
488: /* Check for Budget Version ID not being NULL. */
489: IF ( p_budget_version_id IS NULL) THEN
490: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
491: IF P_PA_DEBUG_MODE = 'Y' THEN
492: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
493: END IF;
494: x_return_status := FND_API.G_RET_STS_ERROR;
495: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
488: /* Check for Budget Version ID not being NULL. */
489: IF ( p_budget_version_id IS NULL) THEN
490: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
491: IF P_PA_DEBUG_MODE = 'Y' THEN
492: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,5);
493: END IF;
494: x_return_status := FND_API.G_RET_STS_ERROR;
495: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
496: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
496: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
497: RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
498: END IF;
499:
500: pa_debug.g_err_stage := 'Budget Version ID is '||p_budget_version_id;
501: IF P_PA_DEBUG_MODE = 'Y' THEN
502: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
503: END IF;
504:
497: RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
498: END IF;
499:
500: pa_debug.g_err_stage := 'Budget Version ID is '||p_budget_version_id;
501: IF P_PA_DEBUG_MODE = 'Y' THEN
502: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
503: END IF;
504:
505: /* Populate Local Variables */
498: END IF;
499:
500: pa_debug.g_err_stage := 'Budget Version ID is '||p_budget_version_id;
501: IF P_PA_DEBUG_MODE = 'Y' THEN
502: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
503: END IF;
504:
505: /* Populate Local Variables */
506: /* M21-AUG: made call parameterized */
503: END IF;
504:
505: /* Populate Local Variables */
506: /* M21-AUG: made call parameterized */
507: pa_debug.g_err_stage := 'calling populate_local_vars';
508: IF P_PA_DEBUG_MODE = 'Y' THEN
509: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
510: END IF;
511:
504:
505: /* Populate Local Variables */
506: /* M21-AUG: made call parameterized */
507: pa_debug.g_err_stage := 'calling populate_local_vars';
508: IF P_PA_DEBUG_MODE = 'Y' THEN
509: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
510: END IF;
511:
512:
505: /* Populate Local Variables */
506: /* M21-AUG: made call parameterized */
507: pa_debug.g_err_stage := 'calling populate_local_vars';
508: IF P_PA_DEBUG_MODE = 'Y' THEN
509: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
510: END IF;
511:
512:
513: SELECT PERIOD_PROFILE_ID
521:
522:
523: g_first_ra_id := 0;
524:
525: pa_debug.g_err_stage := 'Update All Version Resource_Assignments';
526: IF P_PA_DEBUG_MODE = 'Y' THEN
527: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
528: END IF;
529:
522:
523: g_first_ra_id := 0;
524:
525: pa_debug.g_err_stage := 'Update All Version Resource_Assignments';
526: IF P_PA_DEBUG_MODE = 'Y' THEN
527: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
528: END IF;
529:
530: -- Bug 3362316, 08-JAN-2004: Purged Obsolete for FP.M. Added Update ---------------------
523: g_first_ra_id := 0;
524:
525: pa_debug.g_err_stage := 'Update All Version Resource_Assignments';
526: IF P_PA_DEBUG_MODE = 'Y' THEN
527: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
528: END IF;
529:
530: -- Bug 3362316, 08-JAN-2004: Purged Obsolete for FP.M. Added Update ---------------------
531:
528: END IF;
529:
530: -- Bug 3362316, 08-JAN-2004: Purged Obsolete for FP.M. Added Update ---------------------
531:
532: pa_debug.g_err_stage := 'Update All Version Resource_Assignments';
533: IF P_PA_DEBUG_MODE = 'Y' THEN
534: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
535: END IF;
536:
529:
530: -- Bug 3362316, 08-JAN-2004: Purged Obsolete for FP.M. Added Update ---------------------
531:
532: pa_debug.g_err_stage := 'Update All Version Resource_Assignments';
533: IF P_PA_DEBUG_MODE = 'Y' THEN
534: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
535: END IF;
536:
537: --code modified for bug 4160258.
530: -- Bug 3362316, 08-JAN-2004: Purged Obsolete for FP.M. Added Update ---------------------
531:
532: pa_debug.g_err_stage := 'Update All Version Resource_Assignments';
533: IF P_PA_DEBUG_MODE = 'Y' THEN
534: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
535: END IF;
536:
537: --code modified for bug 4160258.
538: IF p_context IS NOT NULL AND p_context = 'DELETE_RA'
599: IF (l_org_forecast_flag = 'Y')
600: THEN
601:
602:
603: pa_debug.g_err_stage := 'Entire Version is Y , calling Refresh_Period_Denorm';
604: IF P_PA_DEBUG_MODE = 'Y' THEN
605: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
606: END IF;
607:
600: THEN
601:
602:
603: pa_debug.g_err_stage := 'Entire Version is Y , calling Refresh_Period_Denorm';
604: IF P_PA_DEBUG_MODE = 'Y' THEN
605: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
606: END IF;
607:
608: REFRESH_PERIOD_DENORM(p_budget_version_id => p_budget_version_id
601:
602:
603: pa_debug.g_err_stage := 'Entire Version is Y , calling Refresh_Period_Denorm';
604: IF P_PA_DEBUG_MODE = 'Y' THEN
605: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
606: END IF;
607:
608: REFRESH_PERIOD_DENORM(p_budget_version_id => p_budget_version_id
609: ,x_return_status => x_return_status
654:
655: /* Update Budget Versions Table with rolled up amounts. Update budget versions table
656: using top_task level records from pa_resource_assignments. */
657:
658: pa_debug.g_err_stage := 'selecting Amounts from project level record';
659: IF P_PA_DEBUG_MODE = 'Y' THEN
660: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
661: END IF;
662:
655: /* Update Budget Versions Table with rolled up amounts. Update budget versions table
656: using top_task level records from pa_resource_assignments. */
657:
658: pa_debug.g_err_stage := 'selecting Amounts from project level record';
659: IF P_PA_DEBUG_MODE = 'Y' THEN
660: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
661: END IF;
662:
663: /* #2593261: Added the total_plan_quantity column which was missed out
656: using top_task level records from pa_resource_assignments. */
657:
658: pa_debug.g_err_stage := 'selecting Amounts from project level record';
659: IF P_PA_DEBUG_MODE = 'Y' THEN
660: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
661: END IF;
662:
663: /* #2593261: Added the total_plan_quantity column which was missed out
664: earlier, because of which labor_quantity was not being updated
673: OPEN c_proj_level_amounts(p_budget_version_id);
674:
675: -- End, Bug 3362316, 08-JAN-2004: Removed l_uncat_rlm_id parameter ---------------
676:
677: IF P_PA_DEBUG_MODE = 'Y' THEN
678: pa_debug.g_err_stage := 'fetching project level amounts';
679: pa_debug.write('ROLLUP_BUDGET_VERSION: '||l_module_name,pa_debug.g_err_stage,3);
680: END IF;
681:
674:
675: -- End, Bug 3362316, 08-JAN-2004: Removed l_uncat_rlm_id parameter ---------------
676:
677: IF P_PA_DEBUG_MODE = 'Y' THEN
678: pa_debug.g_err_stage := 'fetching project level amounts';
679: pa_debug.write('ROLLUP_BUDGET_VERSION: '||l_module_name,pa_debug.g_err_stage,3);
680: END IF;
681:
682:
675: -- End, Bug 3362316, 08-JAN-2004: Removed l_uncat_rlm_id parameter ---------------
676:
677: IF P_PA_DEBUG_MODE = 'Y' THEN
678: pa_debug.g_err_stage := 'fetching project level amounts';
679: pa_debug.write('ROLLUP_BUDGET_VERSION: '||l_module_name,pa_debug.g_err_stage,3);
680: END IF;
681:
682:
683: -- Bug 3441943, 18-FEB-2004: New Vars to sum people (labor) and equipment quantities ------------
699:
700: --IF (l_proj_raw_cost IS NOT NULL) THEN
701: /* If records exist at the Top Task Level. */
702:
703: pa_debug.g_err_stage := 'updating project level amounts on budget version';
704: IF P_PA_DEBUG_MODE = 'Y' THEN
705: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
706: END IF;
707:
700: --IF (l_proj_raw_cost IS NOT NULL) THEN
701: /* If records exist at the Top Task Level. */
702:
703: pa_debug.g_err_stage := 'updating project level amounts on budget version';
704: IF P_PA_DEBUG_MODE = 'Y' THEN
705: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
706: END IF;
707:
708: -- Bug 3441943, 18-FEB-2004: New Vars for people (labor) and equipment quantities ------------
701: /* If records exist at the Top Task Level. */
702:
703: pa_debug.g_err_stage := 'updating project level amounts on budget version';
704: IF P_PA_DEBUG_MODE = 'Y' THEN
705: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
706: END IF;
707:
708: -- Bug 3441943, 18-FEB-2004: New Vars for people (labor) and equipment quantities ------------
709:
723:
724: -- End, Bug 3441943, 18-FEB-2004: New Vars for people (labor) and equipment quantities ------------
725:
726:
727: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' budget version';
728: IF P_PA_DEBUG_MODE = 'Y' THEN
729: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
730: END IF;
731:
724: -- End, Bug 3441943, 18-FEB-2004: New Vars for people (labor) and equipment quantities ------------
725:
726:
727: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' budget version';
728: IF P_PA_DEBUG_MODE = 'Y' THEN
729: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
730: END IF;
731:
732: --END IF; -- l_proj_raw_cost IS NOT NULL)
725:
726:
727: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' budget version';
728: IF P_PA_DEBUG_MODE = 'Y' THEN
729: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,pa_debug.g_err_stage,3);
730: END IF;
731:
732: --END IF; -- l_proj_raw_cost IS NOT NULL)
733:
731:
732: --END IF; -- l_proj_raw_cost IS NOT NULL)
733:
734: CLOSE c_proj_level_amounts;
735: pa_debug.reset_err_stack;
736:
737: EXCEPTION
738: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
739: l_msg_count := FND_MSG_PUB.count_msg;
749: x_msg_count := l_msg_count;
750: ELSE
751: x_msg_count := l_msg_count;
752: END IF;
753: pa_debug.reset_err_stack;
754: RAISE;
755: WHEN OTHERS THEN
756: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757: x_msg_count := 1;
758: x_msg_data := SQLERRM;
759: FND_MSG_PUB.add_exc_msg
760: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
761: ,p_procedure_name => 'Rollup_Budget_Version');
762: IF P_PA_DEBUG_MODE = 'Y' THEN
763: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,SQLERRM,5);
764: END IF;
765: pa_debug.reset_err_stack;
766:
759: FND_MSG_PUB.add_exc_msg
760: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
761: ,p_procedure_name => 'Rollup_Budget_Version');
762: IF P_PA_DEBUG_MODE = 'Y' THEN
763: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,SQLERRM,5);
764: END IF;
765: pa_debug.reset_err_stack;
766:
767: raise FND_API.G_EXC_UNEXPECTED_ERROR;
761: ,p_procedure_name => 'Rollup_Budget_Version');
762: IF P_PA_DEBUG_MODE = 'Y' THEN
763: pa_debug.write('ROLLUP_BUDGET_VERSION: ' || l_module_name,SQLERRM,5);
764: END IF;
765: pa_debug.reset_err_stack;
766:
767: raise FND_API.G_EXC_UNEXPECTED_ERROR;
768: END ROLLUP_BUDGET_VERSION;
769:
987:
988: BEGIN
989:
990: -- Set the error stack.
991: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments');
992:
993: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
994: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
995: l_debug_mode := NVL(l_debug_mode, 'Y');
990: -- Set the error stack.
991: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments');
992:
993: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
994: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
995: l_debug_mode := NVL(l_debug_mode, 'Y');
996:
997: -- Initialize the return status to success
998: x_return_status := FND_API.G_RET_STS_SUCCESS;
996:
997: -- Initialize the return status to success
998: x_return_status := FND_API.G_RET_STS_SUCCESS;
999:
1000: IF P_PA_DEBUG_MODE = 'Y' THEN
1001: pa_debug.set_process('REFRESH_RESOURCE_ASSIGNMENTS: ' || 'PLSQL','LOG',l_debug_mode);
1002: END IF;
1003:
1004: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments';
997: -- Initialize the return status to success
998: x_return_status := FND_API.G_RET_STS_SUCCESS;
999:
1000: IF P_PA_DEBUG_MODE = 'Y' THEN
1001: pa_debug.set_process('REFRESH_RESOURCE_ASSIGNMENTS: ' || 'PLSQL','LOG',l_debug_mode);
1002: END IF;
1003:
1004: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments';
1005: IF P_PA_DEBUG_MODE = 'Y' THEN
1000: IF P_PA_DEBUG_MODE = 'Y' THEN
1001: pa_debug.set_process('REFRESH_RESOURCE_ASSIGNMENTS: ' || 'PLSQL','LOG',l_debug_mode);
1002: END IF;
1003:
1004: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments';
1005: IF P_PA_DEBUG_MODE = 'Y' THEN
1006: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1007: END IF;
1008:
1001: pa_debug.set_process('REFRESH_RESOURCE_ASSIGNMENTS: ' || 'PLSQL','LOG',l_debug_mode);
1002: END IF;
1003:
1004: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments';
1005: IF P_PA_DEBUG_MODE = 'Y' THEN
1006: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1007: END IF;
1008:
1009:
1002: END IF;
1003:
1004: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Resource_Assignments';
1005: IF P_PA_DEBUG_MODE = 'Y' THEN
1006: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1007: END IF;
1008:
1009:
1010: /* Check for Budget Version ID not being NULL. */
1008:
1009:
1010: /* Check for Budget Version ID not being NULL. */
1011: IF ( p_budget_version_id IS NULL) THEN
1012: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
1013: IF P_PA_DEBUG_MODE = 'Y' THEN
1014: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,5);
1015: END IF;
1016: x_return_status := FND_API.G_RET_STS_ERROR;
1009:
1010: /* Check for Budget Version ID not being NULL. */
1011: IF ( p_budget_version_id IS NULL) THEN
1012: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
1013: IF P_PA_DEBUG_MODE = 'Y' THEN
1014: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,5);
1015: END IF;
1016: x_return_status := FND_API.G_RET_STS_ERROR;
1017: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1010: /* Check for Budget Version ID not being NULL. */
1011: IF ( p_budget_version_id IS NULL) THEN
1012: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
1013: IF P_PA_DEBUG_MODE = 'Y' THEN
1014: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,5);
1015: END IF;
1016: x_return_status := FND_API.G_RET_STS_ERROR;
1017: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1018: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1019: RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
1020: END IF;
1021:
1022: /* Populate the local variables for usage. */
1023: pa_debug.g_err_stage := 'calling populate_local_vars';
1024: IF P_PA_DEBUG_MODE = 'Y' THEN
1025: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1026: END IF;
1027:
1020: END IF;
1021:
1022: /* Populate the local variables for usage. */
1023: pa_debug.g_err_stage := 'calling populate_local_vars';
1024: IF P_PA_DEBUG_MODE = 'Y' THEN
1025: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1026: END IF;
1027:
1028: populate_local_vars(p_budget_version_id => p_budget_version_id,
1021:
1022: /* Populate the local variables for usage. */
1023: pa_debug.g_err_stage := 'calling populate_local_vars';
1024: IF P_PA_DEBUG_MODE = 'Y' THEN
1025: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1026: END IF;
1027:
1028: populate_local_vars(p_budget_version_id => p_budget_version_id,
1029: x_project_id => l_project_id,
1038:
1039: /* Delete all ROLLED_UP records from pa_resource_assignments for the
1040: budget_version_id. */
1041:
1042: pa_debug.g_err_stage := 'deleting rolled up records from pa_resource_assignments';
1043: IF P_PA_DEBUG_MODE = 'Y' THEN
1044: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1045: END IF;
1046:
1039: /* Delete all ROLLED_UP records from pa_resource_assignments for the
1040: budget_version_id. */
1041:
1042: pa_debug.g_err_stage := 'deleting rolled up records from pa_resource_assignments';
1043: IF P_PA_DEBUG_MODE = 'Y' THEN
1044: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1045: END IF;
1046:
1047: DELETE FROM pa_resource_assignments
1040: budget_version_id. */
1041:
1042: pa_debug.g_err_stage := 'deleting rolled up records from pa_resource_assignments';
1043: IF P_PA_DEBUG_MODE = 'Y' THEN
1044: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1045: END IF;
1046:
1047: DELETE FROM pa_resource_assignments
1048: WHERE budget_version_id = p_budget_version_id
1053: be updated as in case of copy from and copy actual amounts may not be updated.
1054: We assume that in certain other cases also it may be difficult to update amounts
1055: before calling this api.*/
1056:
1057: pa_debug.g_err_stage := 'Updating the Parent_Assignment_ID of User Entered records';
1058: IF P_PA_DEBUG_MODE = 'Y' THEN
1059: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1060: END IF;
1061:
1054: We assume that in certain other cases also it may be difficult to update amounts
1055: before calling this api.*/
1056:
1057: pa_debug.g_err_stage := 'Updating the Parent_Assignment_ID of User Entered records';
1058: IF P_PA_DEBUG_MODE = 'Y' THEN
1059: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1060: END IF;
1061:
1062: UPDATE pa_resource_assignments pra
1055: before calling this api.*/
1056:
1057: pa_debug.g_err_stage := 'Updating the Parent_Assignment_ID of User Entered records';
1058: IF P_PA_DEBUG_MODE = 'Y' THEN
1059: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1060: END IF;
1061:
1062: UPDATE pa_resource_assignments pra
1063: SET (parent_assignment_id
1082: AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED;
1083:
1084: l_count := sql%rowcount; /* 2598502 - Used local variable for sql%rowcount */
1085:
1086: pa_debug.g_err_stage := 'updated ' || l_count || ' records';
1087: IF P_PA_DEBUG_MODE = 'Y' THEN
1088: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1089: END IF;
1090:
1083:
1084: l_count := sql%rowcount; /* 2598502 - Used local variable for sql%rowcount */
1085:
1086: pa_debug.g_err_stage := 'updated ' || l_count || ' records';
1087: IF P_PA_DEBUG_MODE = 'Y' THEN
1088: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1089: END IF;
1090:
1091: IF l_count = 0 THEN
1084: l_count := sql%rowcount; /* 2598502 - Used local variable for sql%rowcount */
1085:
1086: pa_debug.g_err_stage := 'updated ' || l_count || ' records';
1087: IF P_PA_DEBUG_MODE = 'Y' THEN
1088: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1089: END IF;
1090:
1091: IF l_count = 0 THEN
1092: pa_debug.g_err_stage := 'resource assignments table does not have any records. Hence returning';
1088: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1089: END IF;
1090:
1091: IF l_count = 0 THEN
1092: pa_debug.g_err_stage := 'resource assignments table does not have any records. Hence returning';
1093: IF P_PA_DEBUG_MODE = 'Y' THEN
1094: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1095: END IF;
1096: pa_debug.reset_err_stack;
1089: END IF;
1090:
1091: IF l_count = 0 THEN
1092: pa_debug.g_err_stage := 'resource assignments table does not have any records. Hence returning';
1093: IF P_PA_DEBUG_MODE = 'Y' THEN
1094: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1095: END IF;
1096: pa_debug.reset_err_stack;
1097: RETURN;
1090:
1091: IF l_count = 0 THEN
1092: pa_debug.g_err_stage := 'resource assignments table does not have any records. Hence returning';
1093: IF P_PA_DEBUG_MODE = 'Y' THEN
1094: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1095: END IF;
1096: pa_debug.reset_err_stack;
1097: RETURN;
1098: END IF;
1092: pa_debug.g_err_stage := 'resource assignments table does not have any records. Hence returning';
1093: IF P_PA_DEBUG_MODE = 'Y' THEN
1094: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1095: END IF;
1096: pa_debug.reset_err_stack;
1097: RETURN;
1098: END IF;
1099:
1100: /* #2808442: Check if any Budget Lines exist for the Budget Version. Return without doing
1101: Rollup in case there are no budget lines for the budget version.*/
1102: OPEN c_budget_lines_exist(p_budget_Version_id);
1103: FETCH c_budget_lines_exist INTO l_budget_line_exists;
1104: IF c_budget_lines_exist%NOTFOUND THEN
1105: IF P_PA_DEBUG_MODE = 'Y' THEN
1106: pa_debug.g_err_stage := 'No budget lines found for the budget version. Hence returning';
1107: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1108: END IF;
1109: pa_debug.reset_err_stack;
1102: OPEN c_budget_lines_exist(p_budget_Version_id);
1103: FETCH c_budget_lines_exist INTO l_budget_line_exists;
1104: IF c_budget_lines_exist%NOTFOUND THEN
1105: IF P_PA_DEBUG_MODE = 'Y' THEN
1106: pa_debug.g_err_stage := 'No budget lines found for the budget version. Hence returning';
1107: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1108: END IF;
1109: pa_debug.reset_err_stack;
1110: RETURN;
1103: FETCH c_budget_lines_exist INTO l_budget_line_exists;
1104: IF c_budget_lines_exist%NOTFOUND THEN
1105: IF P_PA_DEBUG_MODE = 'Y' THEN
1106: pa_debug.g_err_stage := 'No budget lines found for the budget version. Hence returning';
1107: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1108: END IF;
1109: pa_debug.reset_err_stack;
1110: RETURN;
1111: END IF;
1105: IF P_PA_DEBUG_MODE = 'Y' THEN
1106: pa_debug.g_err_stage := 'No budget lines found for the budget version. Hence returning';
1107: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1108: END IF;
1109: pa_debug.reset_err_stack;
1110: RETURN;
1111: END IF;
1112: CLOSE c_budget_lines_exist;
1113:
1116:
1117: /* If Resource List is not uncategorized and Resource List is not grouped,
1118: then Resource Groups have to be inserted for the Resource Level records. */
1119:
1120: pa_debug.g_err_stage := 'Opening Cur_Res_Level cursor';
1121: IF P_PA_DEBUG_MODE = 'Y' THEN
1122: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1123: END IF;
1124:
1117: /* If Resource List is not uncategorized and Resource List is not grouped,
1118: then Resource Groups have to be inserted for the Resource Level records. */
1119:
1120: pa_debug.g_err_stage := 'Opening Cur_Res_Level cursor';
1121: IF P_PA_DEBUG_MODE = 'Y' THEN
1122: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1123: END IF;
1124:
1125: OPEN Cur_Res_Level(p_budget_version_id);
1118: then Resource Groups have to be inserted for the Resource Level records. */
1119:
1120: pa_debug.g_err_stage := 'Opening Cur_Res_Level cursor';
1121: IF P_PA_DEBUG_MODE = 'Y' THEN
1122: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1123: END IF;
1124:
1125: OPEN Cur_Res_Level(p_budget_version_id);
1126: LOOP
1123: END IF;
1124:
1125: OPEN Cur_Res_Level(p_budget_version_id);
1126: LOOP
1127: pa_debug.g_err_stage := 'Fetching from Cur_Res_Level';
1128: IF P_PA_DEBUG_MODE = 'Y' THEN
1129: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1130: END IF;
1131:
1124:
1125: OPEN Cur_Res_Level(p_budget_version_id);
1126: LOOP
1127: pa_debug.g_err_stage := 'Fetching from Cur_Res_Level';
1128: IF P_PA_DEBUG_MODE = 'Y' THEN
1129: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1130: END IF;
1131:
1132: FETCH Cur_Res_Level BULK COLLECT INTO
1125: OPEN Cur_Res_Level(p_budget_version_id);
1126: LOOP
1127: pa_debug.g_err_stage := 'Fetching from Cur_Res_Level';
1128: IF P_PA_DEBUG_MODE = 'Y' THEN
1129: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1130: END IF;
1131:
1132: FETCH Cur_Res_Level BULK COLLECT INTO
1133: l_task_id_tbl
1146: IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1147:
1148: /* Call a common API to bulk insert into pa_resource_assignments. */
1149:
1150: pa_debug.g_err_stage := 'got ' || l_task_id_tbl.last || ' records ' || 'calling Insert_Bulk_Rows_Res';
1151: IF P_PA_DEBUG_MODE = 'Y' THEN
1152: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1153: END IF;
1154:
1147:
1148: /* Call a common API to bulk insert into pa_resource_assignments. */
1149:
1150: pa_debug.g_err_stage := 'got ' || l_task_id_tbl.last || ' records ' || 'calling Insert_Bulk_Rows_Res';
1151: IF P_PA_DEBUG_MODE = 'Y' THEN
1152: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1153: END IF;
1154:
1155: Insert_Bulk_Rows_Res(p_project_id => l_project_id
1148: /* Call a common API to bulk insert into pa_resource_assignments. */
1149:
1150: pa_debug.g_err_stage := 'got ' || l_task_id_tbl.last || ' records ' || 'calling Insert_Bulk_Rows_Res';
1151: IF P_PA_DEBUG_MODE = 'Y' THEN
1152: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1153: END IF;
1154:
1155: Insert_Bulk_Rows_Res(p_project_id => l_project_id
1156: ,p_plan_version_id => p_budget_version_id
1198: group or resource level. Hence it makes our task easy to look only at user entered records. */
1199:
1200: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1201:
1202: pa_debug.g_err_stage := 'Inserting the Task Level Records';
1203: IF P_PA_DEBUG_MODE = 'Y' THEN
1204: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1205: END IF;
1206:
1199:
1200: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1201:
1202: pa_debug.g_err_stage := 'Inserting the Task Level Records';
1203: IF P_PA_DEBUG_MODE = 'Y' THEN
1204: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1205: END IF;
1206:
1207: OPEN Cur_Task_Level(p_budget_version_id);
1200: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1201:
1202: pa_debug.g_err_stage := 'Inserting the Task Level Records';
1203: IF P_PA_DEBUG_MODE = 'Y' THEN
1204: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1205: END IF;
1206:
1207: OPEN Cur_Task_Level(p_budget_version_id);
1208: LOOP
1206:
1207: OPEN Cur_Task_Level(p_budget_version_id);
1208: LOOP
1209:
1210: pa_debug.g_err_stage := 'fetching from Cur_Task_Level ';
1211: IF P_PA_DEBUG_MODE = 'Y' THEN
1212: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1213: END IF;
1214:
1207: OPEN Cur_Task_Level(p_budget_version_id);
1208: LOOP
1209:
1210: pa_debug.g_err_stage := 'fetching from Cur_Task_Level ';
1211: IF P_PA_DEBUG_MODE = 'Y' THEN
1212: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1213: END IF;
1214:
1215: FETCH Cur_Task_Level BULK COLLECT INTO l_task_id_tbl
1208: LOOP
1209:
1210: pa_debug.g_err_stage := 'fetching from Cur_Task_Level ';
1211: IF P_PA_DEBUG_MODE = 'Y' THEN
1212: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1213: END IF;
1214:
1215: FETCH Cur_Task_Level BULK COLLECT INTO l_task_id_tbl
1216: ,l_res_list_mem_id_tbl
1282: /* Creating a Loop where the steps of insertion into PA_Resource_Assignments
1283: for the Parent_Task records and then selecting records for the Resource Assignments
1284: for the records that have been created. */
1285:
1286: pa_debug.g_err_stage := 'Inserting the Parent Task Level Records';
1287: IF P_PA_DEBUG_MODE = 'Y' THEN
1288: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1289: END IF;
1290:
1283: for the Parent_Task records and then selecting records for the Resource Assignments
1284: for the records that have been created. */
1285:
1286: pa_debug.g_err_stage := 'Inserting the Parent Task Level Records';
1287: IF P_PA_DEBUG_MODE = 'Y' THEN
1288: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1289: END IF;
1290:
1291: l_curr_wbs_level := null;
1284: for the records that have been created. */
1285:
1286: pa_debug.g_err_stage := 'Inserting the Parent Task Level Records';
1287: IF P_PA_DEBUG_MODE = 'Y' THEN
1288: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1289: END IF;
1290:
1291: l_curr_wbs_level := null;
1292:
1298: LOOP
1299: EXIT WHEN l_curr_wbs_level = 1;
1300:
1301:
1302: pa_debug.g_err_stage := 'opening Cur_Parent_Task';
1303: IF P_PA_DEBUG_MODE = 'Y' THEN
1304: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1305: END IF;
1306:
1299: EXIT WHEN l_curr_wbs_level = 1;
1300:
1301:
1302: pa_debug.g_err_stage := 'opening Cur_Parent_Task';
1303: IF P_PA_DEBUG_MODE = 'Y' THEN
1304: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1305: END IF;
1306:
1307: OPEN Cur_Parent_Task(l_uncat_rlm_id,
1300:
1301:
1302: pa_debug.g_err_stage := 'opening Cur_Parent_Task';
1303: IF P_PA_DEBUG_MODE = 'Y' THEN
1304: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1305: END IF;
1306:
1307: OPEN Cur_Parent_Task(l_uncat_rlm_id,
1308: l_curr_res_assignment_id,
1320: SELECT PA_RESOURCE_ASSIGNMENTS_S.currval
1321: INTO l_curr_res_assignment_id
1322: FROM dual;
1323: END IF;
1324: pa_debug.g_err_stage := 'l_curr_res_assignment_id = ' || l_curr_res_assignment_id;
1325: IF P_PA_DEBUG_MODE = 'Y' THEN
1326: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1327: END IF;
1328:
1321: INTO l_curr_res_assignment_id
1322: FROM dual;
1323: END IF;
1324: pa_debug.g_err_stage := 'l_curr_res_assignment_id = ' || l_curr_res_assignment_id;
1325: IF P_PA_DEBUG_MODE = 'Y' THEN
1326: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1327: END IF;
1328:
1329: LOOP
1322: FROM dual;
1323: END IF;
1324: pa_debug.g_err_stage := 'l_curr_res_assignment_id = ' || l_curr_res_assignment_id;
1325: IF P_PA_DEBUG_MODE = 'Y' THEN
1326: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1327: END IF;
1328:
1329: LOOP
1330: pa_debug.g_err_stage := 'fetching from Cur_Parent_Task ';
1326: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1327: END IF;
1328:
1329: LOOP
1330: pa_debug.g_err_stage := 'fetching from Cur_Parent_Task ';
1331: IF P_PA_DEBUG_MODE = 'Y' THEN
1332: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1333: END IF;
1334:
1327: END IF;
1328:
1329: LOOP
1330: pa_debug.g_err_stage := 'fetching from Cur_Parent_Task ';
1331: IF P_PA_DEBUG_MODE = 'Y' THEN
1332: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1333: END IF;
1334:
1335: FETCH Cur_Parent_Task BULK COLLECT INTO
1328:
1329: LOOP
1330: pa_debug.g_err_stage := 'fetching from Cur_Parent_Task ';
1331: IF P_PA_DEBUG_MODE = 'Y' THEN
1332: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1333: END IF;
1334:
1335: FETCH Cur_Parent_Task BULK COLLECT INTO
1336: l_task_id_tbl
1345: ,l_unit_of_measure_tbl
1346: ,l_track_as_labor_flag_tbl
1347: LIMIT g_plsql_max_array_size;
1348:
1349: pa_debug.g_err_stage := 'fetched ' || l_task_id_tbl.last || ' records';
1350: IF P_PA_DEBUG_MODE = 'Y' THEN
1351: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1352: END IF;
1353:
1346: ,l_track_as_labor_flag_tbl
1347: LIMIT g_plsql_max_array_size;
1348:
1349: pa_debug.g_err_stage := 'fetched ' || l_task_id_tbl.last || ' records';
1350: IF P_PA_DEBUG_MODE = 'Y' THEN
1351: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1352: END IF;
1353:
1354: IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1347: LIMIT g_plsql_max_array_size;
1348:
1349: pa_debug.g_err_stage := 'fetched ' || l_task_id_tbl.last || ' records';
1350: IF P_PA_DEBUG_MODE = 'Y' THEN
1351: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1352: END IF;
1353:
1354: IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1355:
1352: END IF;
1353:
1354: IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1355:
1356: pa_debug.g_err_stage := 'l_task_id_tbl.last = ' || l_task_id_tbl.last || ' inserting in ra tbl';
1357: IF P_PA_DEBUG_MODE = 'Y' THEN
1358: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1359: END IF;
1360:
1353:
1354: IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1355:
1356: pa_debug.g_err_stage := 'l_task_id_tbl.last = ' || l_task_id_tbl.last || ' inserting in ra tbl';
1357: IF P_PA_DEBUG_MODE = 'Y' THEN
1358: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1359: END IF;
1360:
1361:
1354: IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1355:
1356: pa_debug.g_err_stage := 'l_task_id_tbl.last = ' || l_task_id_tbl.last || ' inserting in ra tbl';
1357: IF P_PA_DEBUG_MODE = 'Y' THEN
1358: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1359: END IF;
1360:
1361:
1362: Insert_Bulk_Rows_Res(p_project_id => l_project_id
1382:
1383: END LOOP; /* End Loop of the Cursor. */
1384:
1385: CLOSE Cur_Parent_Task;
1386: pa_debug.g_err_stage := 'closing Cur_Parent_Task';
1387: IF P_PA_DEBUG_MODE = 'Y' THEN
1388: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1389: END IF;
1390:
1383: END LOOP; /* End Loop of the Cursor. */
1384:
1385: CLOSE Cur_Parent_Task;
1386: pa_debug.g_err_stage := 'closing Cur_Parent_Task';
1387: IF P_PA_DEBUG_MODE = 'Y' THEN
1388: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1389: END IF;
1390:
1391: l_curr_wbs_level := l_curr_wbs_level - 1;
1384:
1385: CLOSE Cur_Parent_Task;
1386: pa_debug.g_err_stage := 'closing Cur_Parent_Task';
1387: IF P_PA_DEBUG_MODE = 'Y' THEN
1388: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1389: END IF;
1390:
1391: l_curr_wbs_level := l_curr_wbs_level - 1;
1392:
1418: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1419: IF l_uncat_flag = 'Y' THEN
1420: /* For project level and resource list none we don't need to create proejct
1421: level record */
1422: pa_debug.g_err_stage := 'Not creating a Project Level Record';
1423: IF P_PA_DEBUG_MODE = 'Y' THEN
1424: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1425: END IF;
1426:
1419: IF l_uncat_flag = 'Y' THEN
1420: /* For project level and resource list none we don't need to create proejct
1421: level record */
1422: pa_debug.g_err_stage := 'Not creating a Project Level Record';
1423: IF P_PA_DEBUG_MODE = 'Y' THEN
1424: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1425: END IF;
1426:
1427: NULL;
1420: /* For project level and resource list none we don't need to create proejct
1421: level record */
1422: pa_debug.g_err_stage := 'Not creating a Project Level Record';
1423: IF P_PA_DEBUG_MODE = 'Y' THEN
1424: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1425: END IF;
1426:
1427: NULL;
1428:
1432: join with PA_TASKS table as there would be no record in PA_TASKS
1433: table with Task_ID as 0. We should also have no condition for
1434: checking the resource list member id. */
1435:
1436: pa_debug.g_err_stage := 'Creating Project Level Record - 1';
1437: IF P_PA_DEBUG_MODE = 'Y' THEN
1438: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1439: END IF;
1440:
1433: table with Task_ID as 0. We should also have no condition for
1434: checking the resource list member id. */
1435:
1436: pa_debug.g_err_stage := 'Creating Project Level Record - 1';
1437: IF P_PA_DEBUG_MODE = 'Y' THEN
1438: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1439: END IF;
1440:
1441: OPEN c_proj_level_amts1(p_budget_version_id);
1434: checking the resource list member id. */
1435:
1436: pa_debug.g_err_stage := 'Creating Project Level Record - 1';
1437: IF P_PA_DEBUG_MODE = 'Y' THEN
1438: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1439: END IF;
1440:
1441: OPEN c_proj_level_amts1(p_budget_version_id);
1442:
1459: END IF; /* l_uncat_flag = 'Y' */
1460:
1461: ELSE /* Planning Level is not Project. */
1462:
1463: pa_debug.g_err_stage := 'Creating Project Level Record - 2';
1464: IF P_PA_DEBUG_MODE = 'Y' THEN
1465: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1466: END IF;
1467:
1460:
1461: ELSE /* Planning Level is not Project. */
1462:
1463: pa_debug.g_err_stage := 'Creating Project Level Record - 2';
1464: IF P_PA_DEBUG_MODE = 'Y' THEN
1465: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1466: END IF;
1467:
1468: OPEN c_proj_level_amts2(p_budget_version_id,l_uncat_rlm_id);
1461: ELSE /* Planning Level is not Project. */
1462:
1463: pa_debug.g_err_stage := 'Creating Project Level Record - 2';
1464: IF P_PA_DEBUG_MODE = 'Y' THEN
1465: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1466: END IF;
1467:
1468: OPEN c_proj_level_amts2(p_budget_version_id,l_uncat_rlm_id);
1469:
1490: -- planning level is project and resource list is uncategorized.
1491:
1492: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1493: AND l_uncat_flag = 'Y' THEN
1494: pa_debug.g_err_stage := 'project level uncategorized. hence not inserting project level record';
1495: IF P_PA_DEBUG_MODE = 'Y' THEN
1496: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1497: END IF;
1498:
1491:
1492: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1493: AND l_uncat_flag = 'Y' THEN
1494: pa_debug.g_err_stage := 'project level uncategorized. hence not inserting project level record';
1495: IF P_PA_DEBUG_MODE = 'Y' THEN
1496: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1497: END IF;
1498:
1499: NULL;
1492: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1493: AND l_uncat_flag = 'Y' THEN
1494: pa_debug.g_err_stage := 'project level uncategorized. hence not inserting project level record';
1495: IF P_PA_DEBUG_MODE = 'Y' THEN
1496: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1497: END IF;
1498:
1499: NULL;
1500: ELSE
1498:
1499: NULL;
1500: ELSE
1501:
1502: pa_debug.g_err_stage := 'calling Insert_Bulk_Rows_Res';
1503: IF P_PA_DEBUG_MODE = 'Y' THEN
1504: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1505: END IF;
1506:
1499: NULL;
1500: ELSE
1501:
1502: pa_debug.g_err_stage := 'calling Insert_Bulk_Rows_Res';
1503: IF P_PA_DEBUG_MODE = 'Y' THEN
1504: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1505: END IF;
1506:
1507: /* Call Insert_Bulk_Rows_Res only if there are any records
1500: ELSE
1501:
1502: pa_debug.g_err_stage := 'calling Insert_Bulk_Rows_Res';
1503: IF P_PA_DEBUG_MODE = 'Y' THEN
1504: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1505: END IF;
1506:
1507: /* Call Insert_Bulk_Rows_Res only if there are any records
1508: fetched in the PL/SQL tables. */
1534:
1535: /* Calling the procedure UPDATE_RES_PARENT_ASSIGN_ID to update the parent assignment
1536: IDs of all the records. */
1537:
1538: pa_debug.g_err_stage := 'Calling UPDATE_RES_PARENT_ASSIGN_ID to update the Parent Assignment IDs';
1539: IF P_PA_DEBUG_MODE = 'Y' THEN
1540: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1541: END IF;
1542:
1535: /* Calling the procedure UPDATE_RES_PARENT_ASSIGN_ID to update the parent assignment
1536: IDs of all the records. */
1537:
1538: pa_debug.g_err_stage := 'Calling UPDATE_RES_PARENT_ASSIGN_ID to update the Parent Assignment IDs';
1539: IF P_PA_DEBUG_MODE = 'Y' THEN
1540: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1541: END IF;
1542:
1543: UPDATE_RES_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
1536: IDs of all the records. */
1537:
1538: pa_debug.g_err_stage := 'Calling UPDATE_RES_PARENT_ASSIGN_ID to update the Parent Assignment IDs';
1539: IF P_PA_DEBUG_MODE = 'Y' THEN
1540: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,pa_debug.g_err_stage,3);
1541: END IF;
1542:
1543: UPDATE_RES_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
1544: ,p_proj_ra_id => l_proj_ra_id
1547: ,x_msg_data => x_msg_data);
1548:
1549: END IF; /* l_planning_level = PROJECT AND l_uncat_flag = 'Y' THEN */
1550:
1551: pa_debug.reset_err_stack;
1552:
1553: EXCEPTION
1554: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
1555: l_msg_count := FND_MSG_PUB.count_msg;
1565: x_msg_count := l_msg_count;
1566: ELSE
1567: x_msg_count := l_msg_count;
1568: END IF;
1569: pa_debug.reset_err_stack;
1570: RAISE;
1571: WHEN OTHERS THEN
1572: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1573: x_msg_count := 1;
1574: x_msg_data := SQLERRM;
1575: FND_MSG_PUB.add_exc_msg
1576: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
1577: ,p_procedure_name => 'REFRESH_RESOURCE_ASSIGNMENTS');
1578: IF P_PA_DEBUG_MODE = 'Y' THEN
1579: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,SQLERRM,5);
1580: END IF;
1581: pa_debug.reset_err_stack;
1582:
1575: FND_MSG_PUB.add_exc_msg
1576: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
1577: ,p_procedure_name => 'REFRESH_RESOURCE_ASSIGNMENTS');
1578: IF P_PA_DEBUG_MODE = 'Y' THEN
1579: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,SQLERRM,5);
1580: END IF;
1581: pa_debug.reset_err_stack;
1582:
1583: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1577: ,p_procedure_name => 'REFRESH_RESOURCE_ASSIGNMENTS');
1578: IF P_PA_DEBUG_MODE = 'Y' THEN
1579: pa_debug.write('REFRESH_RESOURCE_ASSIGNMENTS: ' || l_module_name,SQLERRM,5);
1580: END IF;
1581: pa_debug.reset_err_stack;
1582:
1583: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1584: END REFRESH_RESOURCE_ASSIGNMENTS;
1585:
1611:
1612: BEGIN
1613:
1614: -- Set the error stack.
1615: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID');
1616:
1617: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
1618: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1619: l_debug_mode := NVL(l_debug_mode, 'Y');
1614: -- Set the error stack.
1615: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID');
1616:
1617: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
1618: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1619: l_debug_mode := NVL(l_debug_mode, 'Y');
1620:
1621: -- Initialize the return status to success
1622: x_return_status := FND_API.G_RET_STS_SUCCESS;
1620:
1621: -- Initialize the return status to success
1622: x_return_status := FND_API.G_RET_STS_SUCCESS;
1623:
1624: IF P_PA_DEBUG_MODE = 'Y' THEN
1625: pa_debug.set_process('UPDATE_RES_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
1626: END IF;
1627:
1628: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID';
1621: -- Initialize the return status to success
1622: x_return_status := FND_API.G_RET_STS_SUCCESS;
1623:
1624: IF P_PA_DEBUG_MODE = 'Y' THEN
1625: pa_debug.set_process('UPDATE_RES_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
1626: END IF;
1627:
1628: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID';
1629: IF P_PA_DEBUG_MODE = 'Y' THEN
1624: IF P_PA_DEBUG_MODE = 'Y' THEN
1625: pa_debug.set_process('UPDATE_RES_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
1626: END IF;
1627:
1628: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID';
1629: IF P_PA_DEBUG_MODE = 'Y' THEN
1630: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1631: END IF;
1632:
1625: pa_debug.set_process('UPDATE_RES_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
1626: END IF;
1627:
1628: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID';
1629: IF P_PA_DEBUG_MODE = 'Y' THEN
1630: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1631: END IF;
1632:
1633:
1626: END IF;
1627:
1628: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.UPDATE_RES_PARENT_ASSIGN_ID';
1629: IF P_PA_DEBUG_MODE = 'Y' THEN
1630: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1631: END IF;
1632:
1633:
1634: /* Check for Budget Version ID not being NULL. */
1632:
1633:
1634: /* Check for Budget Version ID not being NULL. */
1635: IF ( p_budget_version_id IS NULL) THEN
1636: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
1637: IF P_PA_DEBUG_MODE = 'Y' THEN
1638: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,5);
1639: END IF;
1640: x_return_status := FND_API.G_RET_STS_ERROR;
1633:
1634: /* Check for Budget Version ID not being NULL. */
1635: IF ( p_budget_version_id IS NULL) THEN
1636: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
1637: IF P_PA_DEBUG_MODE = 'Y' THEN
1638: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,5);
1639: END IF;
1640: x_return_status := FND_API.G_RET_STS_ERROR;
1641: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1634: /* Check for Budget Version ID not being NULL. */
1635: IF ( p_budget_version_id IS NULL) THEN
1636: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
1637: IF P_PA_DEBUG_MODE = 'Y' THEN
1638: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,5);
1639: END IF;
1640: x_return_status := FND_API.G_RET_STS_ERROR;
1641: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1642: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1644: END IF;
1645:
1646: /* Populate the local variables. */
1647:
1648: pa_debug.g_err_stage := 'calling populate_local_vars';
1649: IF P_PA_DEBUG_MODE = 'Y' THEN
1650: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1651: END IF;
1652:
1645:
1646: /* Populate the local variables. */
1647:
1648: pa_debug.g_err_stage := 'calling populate_local_vars';
1649: IF P_PA_DEBUG_MODE = 'Y' THEN
1650: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1651: END IF;
1652:
1653: populate_local_vars(p_budget_version_id => p_budget_version_id,
1646: /* Populate the local variables. */
1647:
1648: pa_debug.g_err_stage := 'calling populate_local_vars';
1649: IF P_PA_DEBUG_MODE = 'Y' THEN
1650: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1651: END IF;
1652:
1653: populate_local_vars(p_budget_version_id => p_budget_version_id,
1654: x_project_id => l_project_id,
1666:
1667: IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1668: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1669:
1670: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1671: IF P_PA_DEBUG_MODE = 'Y' THEN
1672: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1673: END IF;
1674:
1667: IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1668: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1669:
1670: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1671: IF P_PA_DEBUG_MODE = 'Y' THEN
1672: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1673: END IF;
1674:
1675: UPDATE pa_resource_assignments pra1
1668: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1669:
1670: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1671: IF P_PA_DEBUG_MODE = 'Y' THEN
1672: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1673: END IF;
1674:
1675: UPDATE pa_resource_assignments pra1
1676: SET parent_assignment_id =
1686: AND resource_list_member_id IN (l_uncat_rlm_id,0)
1687: AND parent_assignment_id is null
1688: AND task_id <> 0;
1689:
1690: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1691: IF P_PA_DEBUG_MODE = 'Y' THEN
1692: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1693: END IF;
1694:
1687: AND parent_assignment_id is null
1688: AND task_id <> 0;
1689:
1690: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1691: IF P_PA_DEBUG_MODE = 'Y' THEN
1692: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1693: END IF;
1694:
1695:
1688: AND task_id <> 0;
1689:
1690: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1691: IF P_PA_DEBUG_MODE = 'Y' THEN
1692: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1693: END IF;
1694:
1695:
1696: END IF;
1700:
1701: IF l_uncat_flag <> 'Y'THEN /* Res List not uncategorized */
1702: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
1703:
1704: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Resource Level recs';
1705: IF P_PA_DEBUG_MODE = 'Y' THEN
1706: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1707: END IF;
1708:
1701: IF l_uncat_flag <> 'Y'THEN /* Res List not uncategorized */
1702: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
1703:
1704: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Resource Level recs';
1705: IF P_PA_DEBUG_MODE = 'Y' THEN
1706: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1707: END IF;
1708:
1709: UPDATE pa_resource_assignments pra1
1702: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
1703:
1704: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Resource Level recs';
1705: IF P_PA_DEBUG_MODE = 'Y' THEN
1706: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1707: END IF;
1708:
1709: UPDATE pa_resource_assignments pra1
1710: SET parent_assignment_id =
1719: WHERE budget_version_id = p_budget_version_id
1720: AND resource_list_member_id <> 0
1721: AND parent_assignment_id is null;
1722:
1723: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1724: IF P_PA_DEBUG_MODE = 'Y' THEN
1725: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1726: END IF;
1727:
1720: AND resource_list_member_id <> 0
1721: AND parent_assignment_id is null;
1722:
1723: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1724: IF P_PA_DEBUG_MODE = 'Y' THEN
1725: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1726: END IF;
1727:
1728: END IF;
1721: AND parent_assignment_id is null;
1722:
1723: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1724: IF P_PA_DEBUG_MODE = 'Y' THEN
1725: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1726: END IF;
1727:
1728: END IF;
1729:
1731: RECORDS ARE THE PARENTS. (IN CASE RESOURCE LIST IS GROUPED THEN RESOURCE GROUP LEVEL
1732: ELSE RESOURCE LEVEL) UPDATE PARENT MEMBER ID FOR SUCH RECORDS. THIS STEP NEED TO BE
1733: EXECUTED ONLY IF RESOURCE LIST ATTACHED IS NOT UNCATEGORIZED. */
1734:
1735: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1736: IF P_PA_DEBUG_MODE = 'Y' THEN
1737: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1738: END IF;
1739:
1732: ELSE RESOURCE LEVEL) UPDATE PARENT MEMBER ID FOR SUCH RECORDS. THIS STEP NEED TO BE
1733: EXECUTED ONLY IF RESOURCE LIST ATTACHED IS NOT UNCATEGORIZED. */
1734:
1735: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1736: IF P_PA_DEBUG_MODE = 'Y' THEN
1737: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1738: END IF;
1739:
1740: /* Do the following only when Planning Level is not 'Project' */
1733: EXECUTED ONLY IF RESOURCE LIST ATTACHED IS NOT UNCATEGORIZED. */
1734:
1735: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs';
1736: IF P_PA_DEBUG_MODE = 'Y' THEN
1737: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1738: END IF;
1739:
1740: /* Do the following only when Planning Level is not 'Project' */
1741:
1752: WHERE budget_version_id = p_budget_version_id
1753: AND resource_list_member_id <> 0
1754: AND parent_assignment_id is null;
1755:
1756: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1757: IF P_PA_DEBUG_MODE = 'Y' THEN
1758: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1759: END IF;
1760:
1753: AND resource_list_member_id <> 0
1754: AND parent_assignment_id is null;
1755:
1756: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1757: IF P_PA_DEBUG_MODE = 'Y' THEN
1758: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1759: END IF;
1760:
1761: END IF;
1754: AND parent_assignment_id is null;
1755:
1756: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1757: IF P_PA_DEBUG_MODE = 'Y' THEN
1758: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1759: END IF;
1760:
1761: END IF;
1762:
1764:
1765: /* UPDATE THE TOP_TASK LEVEL RECORDS WITH PROJECT LEVEL RECORD'S RA ID AS PARENT_ASSIGNMENT_ID.
1766: THIS IS APPLICABLE ONLY WHEN PLANNING LEVEL IS NOT PROJECT. */
1767:
1768: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Top Task Level recs';
1769: IF P_PA_DEBUG_MODE = 'Y' THEN
1770: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1771: END IF;
1772:
1765: /* UPDATE THE TOP_TASK LEVEL RECORDS WITH PROJECT LEVEL RECORD'S RA ID AS PARENT_ASSIGNMENT_ID.
1766: THIS IS APPLICABLE ONLY WHEN PLANNING LEVEL IS NOT PROJECT. */
1767:
1768: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Top Task Level recs';
1769: IF P_PA_DEBUG_MODE = 'Y' THEN
1770: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1771: END IF;
1772:
1773: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1766: THIS IS APPLICABLE ONLY WHEN PLANNING LEVEL IS NOT PROJECT. */
1767:
1768: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Top Task Level recs';
1769: IF P_PA_DEBUG_MODE = 'Y' THEN
1770: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1771: END IF;
1772:
1773: IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1774:
1782: AND project_id = l_project_id -- bug#2708524
1783: AND resource_list_member_id IN (l_uncat_rlm_id,0)
1784: AND parent_assignment_id is null;
1785:
1786: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1787: IF P_PA_DEBUG_MODE = 'Y' THEN
1788: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1789: END IF;
1790:
1783: AND resource_list_member_id IN (l_uncat_rlm_id,0)
1784: AND parent_assignment_id is null;
1785:
1786: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1787: IF P_PA_DEBUG_MODE = 'Y' THEN
1788: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1789: END IF;
1790:
1791:
1784: AND parent_assignment_id is null;
1785:
1786: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1787: IF P_PA_DEBUG_MODE = 'Y' THEN
1788: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1789: END IF;
1790:
1791:
1792: END IF;
1797:
1798: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1799: AND l_uncat_flag <> 'Y' THEN
1800:
1801: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Res/Res Grp level recs';
1802: IF P_PA_DEBUG_MODE = 'Y' THEN
1803: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1804: END IF;
1805:
1798: IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT
1799: AND l_uncat_flag <> 'Y' THEN
1800:
1801: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Res/Res Grp level recs';
1802: IF P_PA_DEBUG_MODE = 'Y' THEN
1803: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1804: END IF;
1805:
1806: UPDATE pa_resource_assignments pra
1799: AND l_uncat_flag <> 'Y' THEN
1800:
1801: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for Res/Res Grp level recs';
1802: IF P_PA_DEBUG_MODE = 'Y' THEN
1803: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1804: END IF;
1805:
1806: UPDATE pa_resource_assignments pra
1807: SET parent_assignment_id = p_proj_ra_id
1809: AND budget_version_id = p_budget_version_id -- bug 2760675, missing version_id join condition
1810: AND resource_list_member_id <> 0
1811: AND task_id = 0;
1812:
1813: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1814: IF P_PA_DEBUG_MODE = 'Y' THEN
1815: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1816: END IF;
1817: END IF;
1810: AND resource_list_member_id <> 0
1811: AND task_id = 0;
1812:
1813: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1814: IF P_PA_DEBUG_MODE = 'Y' THEN
1815: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1816: END IF;
1817: END IF;
1818:
1811: AND task_id = 0;
1812:
1813: pa_debug.g_err_stage := 'Updated ' || sql%rowcount || ' records' ;
1814: IF P_PA_DEBUG_MODE = 'Y' THEN
1815: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1816: END IF;
1817: END IF;
1818:
1819: pa_debug.g_err_stage := 'end of UPDATE_RES_PARENT_ASSIGN_ID' ;
1815: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1816: END IF;
1817: END IF;
1818:
1819: pa_debug.g_err_stage := 'end of UPDATE_RES_PARENT_ASSIGN_ID' ;
1820: IF P_PA_DEBUG_MODE = 'Y' THEN
1821: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1822: END IF;
1823: pa_debug.reset_err_stack;
1816: END IF;
1817: END IF;
1818:
1819: pa_debug.g_err_stage := 'end of UPDATE_RES_PARENT_ASSIGN_ID' ;
1820: IF P_PA_DEBUG_MODE = 'Y' THEN
1821: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1822: END IF;
1823: pa_debug.reset_err_stack;
1824:
1817: END IF;
1818:
1819: pa_debug.g_err_stage := 'end of UPDATE_RES_PARENT_ASSIGN_ID' ;
1820: IF P_PA_DEBUG_MODE = 'Y' THEN
1821: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1822: END IF;
1823: pa_debug.reset_err_stack;
1824:
1825: EXCEPTION
1819: pa_debug.g_err_stage := 'end of UPDATE_RES_PARENT_ASSIGN_ID' ;
1820: IF P_PA_DEBUG_MODE = 'Y' THEN
1821: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
1822: END IF;
1823: pa_debug.reset_err_stack;
1824:
1825: EXCEPTION
1826: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
1827: l_msg_count := FND_MSG_PUB.count_msg;
1837: x_msg_count := l_msg_count;
1838: ELSE
1839: x_msg_count := l_msg_count;
1840: END IF;
1841: pa_debug.reset_err_stack;
1842: RAISE;
1843: WHEN OTHERS THEN
1844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845: x_msg_count := 1;
1846: x_msg_data := SQLERRM;
1847: FND_MSG_PUB.add_exc_msg
1848: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
1849: ,p_procedure_name => 'UPDATE_RES_PARENT_ASSIGN_ID');
1850: IF P_PA_DEBUG_MODE = 'Y' THEN
1851: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,SQLERRM,5);
1852: END IF;
1853: pa_debug.reset_err_stack;
1854:
1847: FND_MSG_PUB.add_exc_msg
1848: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
1849: ,p_procedure_name => 'UPDATE_RES_PARENT_ASSIGN_ID');
1850: IF P_PA_DEBUG_MODE = 'Y' THEN
1851: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,SQLERRM,5);
1852: END IF;
1853: pa_debug.reset_err_stack;
1854:
1855: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1849: ,p_procedure_name => 'UPDATE_RES_PARENT_ASSIGN_ID');
1850: IF P_PA_DEBUG_MODE = 'Y' THEN
1851: pa_debug.write('UPDATE_RES_PARENT_ASSIGN_ID: ' || l_module_name,SQLERRM,5);
1852: END IF;
1853: pa_debug.reset_err_stack;
1854:
1855: raise FND_API.G_EXC_UNEXPECTED_ERROR;
1856:
1857: END UPDATE_RES_PARENT_ASSIGN_ID;
1888:
1889: BEGIN
1890:
1891: -- Set the error stack.
1892: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res');
1893:
1894: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
1895: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1896: l_debug_mode := NVL(l_debug_mode, 'Y');
1891: -- Set the error stack.
1892: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res');
1893:
1894: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
1895: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1896: l_debug_mode := NVL(l_debug_mode, 'Y');
1897:
1898: -- Initialize the return status to success
1899: x_return_status := FND_API.G_RET_STS_SUCCESS;
1897:
1898: -- Initialize the return status to success
1899: x_return_status := FND_API.G_RET_STS_SUCCESS;
1900:
1901: IF P_PA_DEBUG_MODE = 'Y' THEN
1902: pa_debug.set_process('Insert_Bulk_Rows_Res: ' || 'PLSQL','LOG',l_debug_mode);
1903: END IF;
1904:
1905: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res ';
1898: -- Initialize the return status to success
1899: x_return_status := FND_API.G_RET_STS_SUCCESS;
1900:
1901: IF P_PA_DEBUG_MODE = 'Y' THEN
1902: pa_debug.set_process('Insert_Bulk_Rows_Res: ' || 'PLSQL','LOG',l_debug_mode);
1903: END IF;
1904:
1905: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res ';
1906: IF P_PA_DEBUG_MODE = 'Y' THEN
1901: IF P_PA_DEBUG_MODE = 'Y' THEN
1902: pa_debug.set_process('Insert_Bulk_Rows_Res: ' || 'PLSQL','LOG',l_debug_mode);
1903: END IF;
1904:
1905: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res ';
1906: IF P_PA_DEBUG_MODE = 'Y' THEN
1907: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1908: END IF;
1909:
1902: pa_debug.set_process('Insert_Bulk_Rows_Res: ' || 'PLSQL','LOG',l_debug_mode);
1903: END IF;
1904:
1905: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res ';
1906: IF P_PA_DEBUG_MODE = 'Y' THEN
1907: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1908: END IF;
1909:
1910:
1903: END IF;
1904:
1905: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Bulk_Rows_Res ';
1906: IF P_PA_DEBUG_MODE = 'Y' THEN
1907: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1908: END IF;
1909:
1910:
1911: /* Bulk Insert records into PA_RESOURCE_ASSIGNMENTS table for the records fetched
1910:
1911: /* Bulk Insert records into PA_RESOURCE_ASSIGNMENTS table for the records fetched
1912: from cursor top_task_cur. */
1913:
1914: pa_debug.g_err_stage := 'In Insert_Bulk_Rows_Res';
1915: IF P_PA_DEBUG_MODE = 'Y' THEN
1916: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1917: END IF;
1918:
1911: /* Bulk Insert records into PA_RESOURCE_ASSIGNMENTS table for the records fetched
1912: from cursor top_task_cur. */
1913:
1914: pa_debug.g_err_stage := 'In Insert_Bulk_Rows_Res';
1915: IF P_PA_DEBUG_MODE = 'Y' THEN
1916: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1917: END IF;
1918:
1919: pa_debug.g_err_stage := 'Bulk inserting into PA_RESOURCE_ASSIGNMENTS';
1912: from cursor top_task_cur. */
1913:
1914: pa_debug.g_err_stage := 'In Insert_Bulk_Rows_Res';
1915: IF P_PA_DEBUG_MODE = 'Y' THEN
1916: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1917: END IF;
1918:
1919: pa_debug.g_err_stage := 'Bulk inserting into PA_RESOURCE_ASSIGNMENTS';
1920: IF P_PA_DEBUG_MODE = 'Y' THEN
1915: IF P_PA_DEBUG_MODE = 'Y' THEN
1916: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1917: END IF;
1918:
1919: pa_debug.g_err_stage := 'Bulk inserting into PA_RESOURCE_ASSIGNMENTS';
1920: IF P_PA_DEBUG_MODE = 'Y' THEN
1921: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1922: END IF;
1923:
1916: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1917: END IF;
1918:
1919: pa_debug.g_err_stage := 'Bulk inserting into PA_RESOURCE_ASSIGNMENTS';
1920: IF P_PA_DEBUG_MODE = 'Y' THEN
1921: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1922: END IF;
1923:
1924: IF nvl(p_task_id_tbl.LAST,0) > 0 THEN
1917: END IF;
1918:
1919: pa_debug.g_err_stage := 'Bulk inserting into PA_RESOURCE_ASSIGNMENTS';
1920: IF P_PA_DEBUG_MODE = 'Y' THEN
1921: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1922: END IF;
1923:
1924: IF nvl(p_task_id_tbl.LAST,0) > 0 THEN
1925:
1969: ,p_track_as_labor_flag_tbl(i)
1970: ,-1
1971: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP) ;
1972:
1973: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
1974: IF P_PA_DEBUG_MODE = 'Y' THEN
1975: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1976: END IF;
1977: END IF;
1970: ,-1
1971: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP) ;
1972:
1973: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
1974: IF P_PA_DEBUG_MODE = 'Y' THEN
1975: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1976: END IF;
1977: END IF;
1978:
1971: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP) ;
1972:
1973: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
1974: IF P_PA_DEBUG_MODE = 'Y' THEN
1975: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1976: END IF;
1977: END IF;
1978:
1979: pa_debug.reset_err_stack;
1975: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,pa_debug.g_err_stage,3);
1976: END IF;
1977: END IF;
1978:
1979: pa_debug.reset_err_stack;
1980:
1981: EXCEPTION
1982: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
1983: l_msg_count := FND_MSG_PUB.count_msg;
1993: x_msg_count := l_msg_count;
1994: ELSE
1995: x_msg_count := l_msg_count;
1996: END IF;
1997: pa_debug.reset_err_stack;
1998: RAISE;
1999: WHEN OTHERS THEN
2000: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2001: x_msg_count := 1;
2002: x_msg_data := SQLERRM;
2003: FND_MSG_PUB.add_exc_msg
2004: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
2005: ,p_procedure_name => 'Insert_Bulk_Rows_Res') ;
2006: IF P_PA_DEBUG_MODE = 'Y' THEN
2007: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,SQLERRM,5);
2008: END IF;
2009: pa_debug.reset_err_stack;
2010:
2003: FND_MSG_PUB.add_exc_msg
2004: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
2005: ,p_procedure_name => 'Insert_Bulk_Rows_Res') ;
2006: IF P_PA_DEBUG_MODE = 'Y' THEN
2007: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,SQLERRM,5);
2008: END IF;
2009: pa_debug.reset_err_stack;
2010:
2011: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2005: ,p_procedure_name => 'Insert_Bulk_Rows_Res') ;
2006: IF P_PA_DEBUG_MODE = 'Y' THEN
2007: pa_debug.write('Insert_Bulk_Rows_Res: ' || l_module_name,SQLERRM,5);
2008: END IF;
2009: pa_debug.reset_err_stack;
2010:
2011: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2012: END Insert_Bulk_Rows_Res;
2013:
2056:
2057: BEGIN
2058:
2059: -- Set the error stack.
2060: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Refresh_Period_Denorm');
2061:
2062: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
2063: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2064: l_debug_mode := NVL(l_debug_mode, 'Y');
2059: -- Set the error stack.
2060: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Refresh_Period_Denorm');
2061:
2062: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
2063: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2064: l_debug_mode := NVL(l_debug_mode, 'Y');
2065:
2066: -- Initialize the return status to success
2067: x_return_status := FND_API.G_RET_STS_SUCCESS;
2065:
2066: -- Initialize the return status to success
2067: x_return_status := FND_API.G_RET_STS_SUCCESS;
2068:
2069: IF P_PA_DEBUG_MODE = 'Y' THEN
2070: pa_debug.set_process('REFRESH_PERIOD_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
2071: END IF;
2072:
2073: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Period_Denorm ';
2066: -- Initialize the return status to success
2067: x_return_status := FND_API.G_RET_STS_SUCCESS;
2068:
2069: IF P_PA_DEBUG_MODE = 'Y' THEN
2070: pa_debug.set_process('REFRESH_PERIOD_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
2071: END IF;
2072:
2073: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Period_Denorm ';
2074: IF P_PA_DEBUG_MODE = 'Y' THEN
2069: IF P_PA_DEBUG_MODE = 'Y' THEN
2070: pa_debug.set_process('REFRESH_PERIOD_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
2071: END IF;
2072:
2073: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Period_Denorm ';
2074: IF P_PA_DEBUG_MODE = 'Y' THEN
2075: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2076: END IF;
2077:
2070: pa_debug.set_process('REFRESH_PERIOD_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
2071: END IF;
2072:
2073: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Period_Denorm ';
2074: IF P_PA_DEBUG_MODE = 'Y' THEN
2075: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2076: END IF;
2077:
2078:
2071: END IF;
2072:
2073: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Refresh_Period_Denorm ';
2074: IF P_PA_DEBUG_MODE = 'Y' THEN
2075: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2076: END IF;
2077:
2078:
2079: /* Check for Budget Version ID not being NULL. */
2077:
2078:
2079: /* Check for Budget Version ID not being NULL. */
2080: IF ( p_budget_version_id IS NULL) THEN
2081: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
2082: IF P_PA_DEBUG_MODE = 'Y' THEN
2083: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,5);
2084: END IF;
2085: x_return_status := FND_API.G_RET_STS_ERROR;
2078:
2079: /* Check for Budget Version ID not being NULL. */
2080: IF ( p_budget_version_id IS NULL) THEN
2081: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
2082: IF P_PA_DEBUG_MODE = 'Y' THEN
2083: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,5);
2084: END IF;
2085: x_return_status := FND_API.G_RET_STS_ERROR;
2086: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2079: /* Check for Budget Version ID not being NULL. */
2080: IF ( p_budget_version_id IS NULL) THEN
2081: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
2082: IF P_PA_DEBUG_MODE = 'Y' THEN
2083: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,5);
2084: END IF;
2085: x_return_status := FND_API.G_RET_STS_ERROR;
2086: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2087: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2089: END IF;
2090:
2091: /* Populate the local variables. */
2092: /* M21-AUG: made call parameterized */
2093: pa_debug.g_err_stage := 'calling populate_local_vars';
2094: IF P_PA_DEBUG_MODE = 'Y' THEN
2095: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2096: END IF;
2097:
2090:
2091: /* Populate the local variables. */
2092: /* M21-AUG: made call parameterized */
2093: pa_debug.g_err_stage := 'calling populate_local_vars';
2094: IF P_PA_DEBUG_MODE = 'Y' THEN
2095: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2096: END IF;
2097:
2098: populate_local_vars(p_budget_version_id => p_budget_version_id,
2091: /* Populate the local variables. */
2092: /* M21-AUG: made call parameterized */
2093: pa_debug.g_err_stage := 'calling populate_local_vars';
2094: IF P_PA_DEBUG_MODE = 'Y' THEN
2095: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2096: END IF;
2097:
2098: populate_local_vars(p_budget_version_id => p_budget_version_id,
2099: x_project_id => l_project_id,
2105: x_return_status => x_return_status,
2106: x_msg_count => x_msg_count,
2107: x_msg_data => x_msg_data);
2108:
2109: pa_debug.g_err_stage := 'selecting period profile id from budget versions ';
2110: IF P_PA_DEBUG_MODE = 'Y' THEN
2111: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2112: END IF;
2113:
2106: x_msg_count => x_msg_count,
2107: x_msg_data => x_msg_data);
2108:
2109: pa_debug.g_err_stage := 'selecting period profile id from budget versions ';
2110: IF P_PA_DEBUG_MODE = 'Y' THEN
2111: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2112: END IF;
2113:
2114: SELECT period_profile_id
2107: x_msg_data => x_msg_data);
2108:
2109: pa_debug.g_err_stage := 'selecting period profile id from budget versions ';
2110: IF P_PA_DEBUG_MODE = 'Y' THEN
2111: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2112: END IF;
2113:
2114: SELECT period_profile_id
2115: INTO l_period_profile_id
2115: INTO l_period_profile_id
2116: FROM pa_budget_versions
2117: WHERE budget_version_id = p_budget_version_id;
2118:
2119: pa_debug.g_err_stage := 'period profile id = ' || l_period_profile_id ;
2120: IF P_PA_DEBUG_MODE = 'Y' THEN
2121: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2122: END IF;
2123:
2116: FROM pa_budget_versions
2117: WHERE budget_version_id = p_budget_version_id;
2118:
2119: pa_debug.g_err_stage := 'period profile id = ' || l_period_profile_id ;
2120: IF P_PA_DEBUG_MODE = 'Y' THEN
2121: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2122: END IF;
2123:
2124: /* #2801522: Getting the project currency code for storing in the 'QAUNTITY' record. */
2117: WHERE budget_version_id = p_budget_version_id;
2118:
2119: pa_debug.g_err_stage := 'period profile id = ' || l_period_profile_id ;
2120: IF P_PA_DEBUG_MODE = 'Y' THEN
2121: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2122: END IF;
2123:
2124: /* #2801522: Getting the project currency code for storing in the 'QAUNTITY' record. */
2125:
2131: IF (l_period_profile_id IS NOT NULL) THEN
2132:
2133: /* Delete all the records from PA_PROJ_PERIODS_DENORM as new resource assignment IDs
2134: would have been generated. */
2135: pa_debug.g_err_stage := 'period profile id not null deleting denorm records ';
2136: IF P_PA_DEBUG_MODE = 'Y' THEN
2137: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2138: END IF;
2139:
2132:
2133: /* Delete all the records from PA_PROJ_PERIODS_DENORM as new resource assignment IDs
2134: would have been generated. */
2135: pa_debug.g_err_stage := 'period profile id not null deleting denorm records ';
2136: IF P_PA_DEBUG_MODE = 'Y' THEN
2137: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2138: END IF;
2139:
2140: DELETE FROM pa_proj_periods_denorm
2133: /* Delete all the records from PA_PROJ_PERIODS_DENORM as new resource assignment IDs
2134: would have been generated. */
2135: pa_debug.g_err_stage := 'period profile id not null deleting denorm records ';
2136: IF P_PA_DEBUG_MODE = 'Y' THEN
2137: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2138: END IF;
2139:
2140: DELETE FROM pa_proj_periods_denorm
2141: WHERE budget_version_id = p_budget_version_id;
2142:
2143: /* Call call_maintain_plan_matrix API with data source as 'BUDGET_LINES' to dump all budget lines
2144: into PA_PROJ_PERIODS_DENORM table. */
2145:
2146: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records. Calling CALL_MAINTAIN_PLAN_MATRIX' ;
2147: IF P_PA_DEBUG_MODE = 'Y' THEN
2148: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2149: END IF;
2150:
2143: /* Call call_maintain_plan_matrix API with data source as 'BUDGET_LINES' to dump all budget lines
2144: into PA_PROJ_PERIODS_DENORM table. */
2145:
2146: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records. Calling CALL_MAINTAIN_PLAN_MATRIX' ;
2147: IF P_PA_DEBUG_MODE = 'Y' THEN
2148: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2149: END IF;
2150:
2151: l_data_source := PA_FP_CONSTANTS_PKG.G_DATA_SOURCE_BUDGET_LINE;
2144: into PA_PROJ_PERIODS_DENORM table. */
2145:
2146: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records. Calling CALL_MAINTAIN_PLAN_MATRIX' ;
2147: IF P_PA_DEBUG_MODE = 'Y' THEN
2148: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2149: END IF;
2150:
2151: l_data_source := PA_FP_CONSTANTS_PKG.G_DATA_SOURCE_BUDGET_LINE;
2152: PA_FIN_PLAN_PUB.CALL_MAINTAIN_PLAN_MATRIX(p_budget_version_id => p_budget_version_id
2154: ,x_return_status => x_return_status
2155: ,x_msg_count => x_msg_count
2156: ,x_msg_data => x_msg_data);
2157:
2158: pa_debug.g_err_stage := 'returned from CALL_MAINTAIN_PLAN_MATRIX' ;
2159: IF P_PA_DEBUG_MODE = 'Y' THEN
2160: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2161: END IF;
2162: /* set total number of levels in rollup.
2155: ,x_msg_count => x_msg_count
2156: ,x_msg_data => x_msg_data);
2157:
2158: pa_debug.g_err_stage := 'returned from CALL_MAINTAIN_PLAN_MATRIX' ;
2159: IF P_PA_DEBUG_MODE = 'Y' THEN
2160: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2161: END IF;
2162: /* set total number of levels in rollup.
2163: Total number of level = number of levels in WBS + resource group (in case resource list is grouped)
2156: ,x_msg_data => x_msg_data);
2157:
2158: pa_debug.g_err_stage := 'returned from CALL_MAINTAIN_PLAN_MATRIX' ;
2159: IF P_PA_DEBUG_MODE = 'Y' THEN
2160: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2161: END IF;
2162: /* set total number of levels in rollup.
2163: Total number of level = number of levels in WBS + resource group (in case resource list is grouped)
2164: + resource (in case resource list attached) + 1 (project level)
2197:
2198: LOOP
2199: EXIT WHEN l_curr_rollup_level = 0;
2200:
2201: pa_debug.g_err_stage := 'Inserting into PA_FP_RA_MAP_TMP for User Entered recs';
2202: IF P_PA_DEBUG_MODE = 'Y' THEN
2203: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2204: END IF;
2205:
2198: LOOP
2199: EXIT WHEN l_curr_rollup_level = 0;
2200:
2201: pa_debug.g_err_stage := 'Inserting into PA_FP_RA_MAP_TMP for User Entered recs';
2202: IF P_PA_DEBUG_MODE = 'Y' THEN
2203: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2204: END IF;
2205:
2206: /* Call the procedure insert_parent_rec_temp to insert the parent
2199: EXIT WHEN l_curr_rollup_level = 0;
2200:
2201: pa_debug.g_err_stage := 'Inserting into PA_FP_RA_MAP_TMP for User Entered recs';
2202: IF P_PA_DEBUG_MODE = 'Y' THEN
2203: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2204: END IF;
2205:
2206: /* Call the procedure insert_parent_rec_temp to insert the parent
2207: records into the pa_fp_ra_map_tmp table so that they can be
2206: /* Call the procedure insert_parent_rec_temp to insert the parent
2207: records into the pa_fp_ra_map_tmp table so that they can be
2208: processed in the next loop; */
2209:
2210: pa_debug.g_err_stage := 'Calling Insert_Parent_Rec_Tmp';
2211: IF P_PA_DEBUG_MODE = 'Y' THEN
2212: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2213: END IF;
2214:
2207: records into the pa_fp_ra_map_tmp table so that they can be
2208: processed in the next loop; */
2209:
2210: pa_debug.g_err_stage := 'Calling Insert_Parent_Rec_Tmp';
2211: IF P_PA_DEBUG_MODE = 'Y' THEN
2212: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2213: END IF;
2214:
2215: INSERT_PARENT_REC_TMP(p_budget_version_id => p_budget_version_id
2208: processed in the next loop; */
2209:
2210: pa_debug.g_err_stage := 'Calling Insert_Parent_Rec_Tmp';
2211: IF P_PA_DEBUG_MODE = 'Y' THEN
2212: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2213: END IF;
2214:
2215: INSERT_PARENT_REC_TMP(p_budget_version_id => p_budget_version_id
2216: ,PX_INSERTING_RES_GROUP_LEVEL => L_INSERTING_RES_GROUP_LEVEL
2217: ,PX_INSERTING_TASK_LEVEL => L_INSERTING_TASK_LEVEL
2218: ,PX_INSERTING_PARENT_TASK_LEVEL => L_INSERTING_PARENT_TASK_LEVEL
2219: ,p_curr_rollup_level => l_curr_rollup_level);
2220:
2221: pa_debug.g_err_stage := 'Inserting into PA_Proj_Periods_Denorm';
2222: IF P_PA_DEBUG_MODE = 'Y' THEN
2223: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2224: END IF;
2225:
2218: ,PX_INSERTING_PARENT_TASK_LEVEL => L_INSERTING_PARENT_TASK_LEVEL
2219: ,p_curr_rollup_level => l_curr_rollup_level);
2220:
2221: pa_debug.g_err_stage := 'Inserting into PA_Proj_Periods_Denorm';
2222: IF P_PA_DEBUG_MODE = 'Y' THEN
2223: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2224: END IF;
2225:
2226: INSERT INTO PA_PROJ_PERIODS_DENORM(
2219: ,p_curr_rollup_level => l_curr_rollup_level);
2220:
2221: pa_debug.g_err_stage := 'Inserting into PA_Proj_Periods_Denorm';
2222: IF P_PA_DEBUG_MODE = 'Y' THEN
2223: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2224: END IF;
2225:
2226: INSERT INTO PA_PROJ_PERIODS_DENORM(
2227: PROJECT_ID
2393: was to group by parent assignment id and not resource_assignment_id.
2394: So, the object_id has been commented from the group by list.
2395: *************** comment for the above change ************************/
2396:
2397: pa_debug.g_err_stage := 'Inserted ' || sql%rowcount || ' records into denorm table';
2398: IF P_PA_DEBUG_MODE = 'Y' THEN
2399: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2400: END IF;
2401:
2394: So, the object_id has been commented from the group by list.
2395: *************** comment for the above change ************************/
2396:
2397: pa_debug.g_err_stage := 'Inserted ' || sql%rowcount || ' records into denorm table';
2398: IF P_PA_DEBUG_MODE = 'Y' THEN
2399: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2400: END IF;
2401:
2402: /* set the exist condition */
2395: *************** comment for the above change ************************/
2396:
2397: pa_debug.g_err_stage := 'Inserted ' || sql%rowcount || ' records into denorm table';
2398: IF P_PA_DEBUG_MODE = 'Y' THEN
2399: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2400: END IF;
2401:
2402: /* set the exist condition */
2403: l_curr_rollup_level := l_curr_rollup_level - 1;
2406:
2407: /* Call the procedure UPDATE_DENORM_PARENT_ASSIGN_ID to update the Parent
2408: Assignment IDs on the Denorm Table. */
2409:
2410: pa_debug.g_err_stage := 'Calling UPDATE_DENORM_PARENT_ASSIGN_ID';
2411: IF P_PA_DEBUG_MODE = 'Y' THEN
2412: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2413: END IF;
2414: UPDATE_DENORM_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
2407: /* Call the procedure UPDATE_DENORM_PARENT_ASSIGN_ID to update the Parent
2408: Assignment IDs on the Denorm Table. */
2409:
2410: pa_debug.g_err_stage := 'Calling UPDATE_DENORM_PARENT_ASSIGN_ID';
2411: IF P_PA_DEBUG_MODE = 'Y' THEN
2412: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2413: END IF;
2414: UPDATE_DENORM_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
2415: ,x_return_status => x_return_status
2408: Assignment IDs on the Denorm Table. */
2409:
2410: pa_debug.g_err_stage := 'Calling UPDATE_DENORM_PARENT_ASSIGN_ID';
2411: IF P_PA_DEBUG_MODE = 'Y' THEN
2412: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2413: END IF;
2414: UPDATE_DENORM_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
2415: ,x_return_status => x_return_status
2416: ,x_msg_count => x_msg_count
2417: ,x_msg_data => x_msg_data);
2418:
2419: END IF; /* END IF for period_profile_id */
2420:
2421: pa_debug.g_err_stage := 'end of refresh_period_denorm';
2422: IF P_PA_DEBUG_MODE = 'Y' THEN
2423: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2424: END IF;
2425:
2418:
2419: END IF; /* END IF for period_profile_id */
2420:
2421: pa_debug.g_err_stage := 'end of refresh_period_denorm';
2422: IF P_PA_DEBUG_MODE = 'Y' THEN
2423: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2424: END IF;
2425:
2426: pa_debug.reset_err_stack;
2419: END IF; /* END IF for period_profile_id */
2420:
2421: pa_debug.g_err_stage := 'end of refresh_period_denorm';
2422: IF P_PA_DEBUG_MODE = 'Y' THEN
2423: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2424: END IF;
2425:
2426: pa_debug.reset_err_stack;
2427:
2422: IF P_PA_DEBUG_MODE = 'Y' THEN
2423: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
2424: END IF;
2425:
2426: pa_debug.reset_err_stack;
2427:
2428: EXCEPTION
2429: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
2430: l_msg_count := FND_MSG_PUB.count_msg;
2440: x_msg_count := l_msg_count;
2441: ELSE
2442: x_msg_count := l_msg_count;
2443: END IF;
2444: pa_debug.reset_err_stack;
2445: RAISE;
2446: WHEN OTHERS THEN
2447: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2448: x_msg_count := 1;
2449: x_msg_data := SQLERRM;
2450: FND_MSG_PUB.add_exc_msg
2451: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
2452: ,p_procedure_name => 'Refresh_Period_Denorm');
2453: IF P_PA_DEBUG_MODE = 'Y' THEN
2454: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,SQLERRM,5);
2455: END IF;
2456: pa_debug.reset_err_stack;
2457:
2450: FND_MSG_PUB.add_exc_msg
2451: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
2452: ,p_procedure_name => 'Refresh_Period_Denorm');
2453: IF P_PA_DEBUG_MODE = 'Y' THEN
2454: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,SQLERRM,5);
2455: END IF;
2456: pa_debug.reset_err_stack;
2457:
2458: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2452: ,p_procedure_name => 'Refresh_Period_Denorm');
2453: IF P_PA_DEBUG_MODE = 'Y' THEN
2454: pa_debug.write('REFRESH_PERIOD_DENORM: ' || l_module_name,SQLERRM,5);
2455: END IF;
2456: pa_debug.reset_err_stack;
2457:
2458: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2459: END REFRESH_PERIOD_DENORM;
2460:
2478:
2479: BEGIN
2480:
2481: -- Set the error stack.
2482: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Update_Denorm_Parent_Assign_ID');
2483:
2484: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
2485: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2486: l_debug_mode := NVL(l_debug_mode, 'Y');
2481: -- Set the error stack.
2482: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Update_Denorm_Parent_Assign_ID');
2483:
2484: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
2485: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2486: l_debug_mode := NVL(l_debug_mode, 'Y');
2487:
2488: -- Initialize the return status to success
2489: x_return_status := FND_API.G_RET_STS_SUCCESS;
2487:
2488: -- Initialize the return status to success
2489: x_return_status := FND_API.G_RET_STS_SUCCESS;
2490:
2491: IF P_PA_DEBUG_MODE = 'Y' THEN
2492: pa_debug.set_process('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
2493: END IF;
2494:
2495: /* Updating the records present in the table pa_proj_periods_denorm with the Parent
2488: -- Initialize the return status to success
2489: x_return_status := FND_API.G_RET_STS_SUCCESS;
2490:
2491: IF P_PA_DEBUG_MODE = 'Y' THEN
2492: pa_debug.set_process('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || 'PLSQL','LOG',l_debug_mode);
2493: END IF;
2494:
2495: /* Updating the records present in the table pa_proj_periods_denorm with the Parent
2496: Assignment ID. */
2493: END IF;
2494:
2495: /* Updating the records present in the table pa_proj_periods_denorm with the Parent
2496: Assignment ID. */
2497: pa_debug.g_err_stage := 'updating parents on denorm table';
2498: IF P_PA_DEBUG_MODE = 'Y' THEN
2499: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2500: END IF;
2501:
2494:
2495: /* Updating the records present in the table pa_proj_periods_denorm with the Parent
2496: Assignment ID. */
2497: pa_debug.g_err_stage := 'updating parents on denorm table';
2498: IF P_PA_DEBUG_MODE = 'Y' THEN
2499: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2500: END IF;
2501:
2502: UPDATE pa_proj_periods_denorm ppd
2495: /* Updating the records present in the table pa_proj_periods_denorm with the Parent
2496: Assignment ID. */
2497: pa_debug.g_err_stage := 'updating parents on denorm table';
2498: IF P_PA_DEBUG_MODE = 'Y' THEN
2499: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2500: END IF;
2501:
2502: UPDATE pa_proj_periods_denorm ppd
2503: SET ppd.parent_assignment_id =
2505: FROM pa_resource_assignments pra
2506: WHERE pra.resource_assignment_id = ppd.resource_assignment_id)
2507: WHERE ppd.budget_version_id = p_budget_version_id; /* M21-AUG: added this condition */
2508:
2509: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2510: IF P_PA_DEBUG_MODE = 'Y' THEN
2511: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2512: END IF;
2513: pa_debug.reset_err_stack;
2506: WHERE pra.resource_assignment_id = ppd.resource_assignment_id)
2507: WHERE ppd.budget_version_id = p_budget_version_id; /* M21-AUG: added this condition */
2508:
2509: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2510: IF P_PA_DEBUG_MODE = 'Y' THEN
2511: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2512: END IF;
2513: pa_debug.reset_err_stack;
2514:
2507: WHERE ppd.budget_version_id = p_budget_version_id; /* M21-AUG: added this condition */
2508:
2509: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2510: IF P_PA_DEBUG_MODE = 'Y' THEN
2511: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2512: END IF;
2513: pa_debug.reset_err_stack;
2514:
2515: EXCEPTION
2509: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2510: IF P_PA_DEBUG_MODE = 'Y' THEN
2511: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,pa_debug.g_err_stage,3);
2512: END IF;
2513: pa_debug.reset_err_stack;
2514:
2515: EXCEPTION
2516: WHEN OTHERS THEN
2517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2519: x_msg_data := SQLERRM;
2520: FND_MSG_PUB.add_exc_msg
2521: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
2522: ,p_procedure_name => 'UPDATE_DENORM_PARENT_ASSIGN_ID');
2523: IF P_PA_DEBUG_MODE = 'Y' THEN
2524: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,SQLERRM,5);
2525: END IF;
2526: pa_debug.reset_err_stack;
2527:
2520: FND_MSG_PUB.add_exc_msg
2521: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
2522: ,p_procedure_name => 'UPDATE_DENORM_PARENT_ASSIGN_ID');
2523: IF P_PA_DEBUG_MODE = 'Y' THEN
2524: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,SQLERRM,5);
2525: END IF;
2526: pa_debug.reset_err_stack;
2527:
2528: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2522: ,p_procedure_name => 'UPDATE_DENORM_PARENT_ASSIGN_ID');
2523: IF P_PA_DEBUG_MODE = 'Y' THEN
2524: pa_debug.write('UPDATE_DENORM_PARENT_ASSIGN_ID: ' || l_module_name,SQLERRM,5);
2525: END IF;
2526: pa_debug.reset_err_stack;
2527:
2528: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2529: END UPDATE_DENORM_PARENT_ASSIGN_ID;
2530:
2642:
2643: BEGIN
2644:
2645: -- Set the error stack.
2646: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents');
2647:
2648: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
2649: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2650: l_debug_mode := NVL(l_debug_mode, 'Y');
2645: -- Set the error stack.
2646: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents');
2647:
2648: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
2649: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2650: l_debug_mode := NVL(l_debug_mode, 'Y');
2651:
2652: -- Initialize the return status to success
2653: x_return_status := FND_API.G_RET_STS_SUCCESS;
2651:
2652: -- Initialize the return status to success
2653: x_return_status := FND_API.G_RET_STS_SUCCESS;
2654:
2655: IF P_PA_DEBUG_MODE = 'Y' THEN
2656: pa_debug.set_process('INSERT_MISSING_RES_PARENTS: ' || 'PLSQL','LOG',l_debug_mode);
2657: END IF;
2658:
2659: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents ';
2652: -- Initialize the return status to success
2653: x_return_status := FND_API.G_RET_STS_SUCCESS;
2654:
2655: IF P_PA_DEBUG_MODE = 'Y' THEN
2656: pa_debug.set_process('INSERT_MISSING_RES_PARENTS: ' || 'PLSQL','LOG',l_debug_mode);
2657: END IF;
2658:
2659: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents ';
2660: IF P_PA_DEBUG_MODE = 'Y' THEN
2655: IF P_PA_DEBUG_MODE = 'Y' THEN
2656: pa_debug.set_process('INSERT_MISSING_RES_PARENTS: ' || 'PLSQL','LOG',l_debug_mode);
2657: END IF;
2658:
2659: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents ';
2660: IF P_PA_DEBUG_MODE = 'Y' THEN
2661: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2662: END IF;
2663:
2656: pa_debug.set_process('INSERT_MISSING_RES_PARENTS: ' || 'PLSQL','LOG',l_debug_mode);
2657: END IF;
2658:
2659: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents ';
2660: IF P_PA_DEBUG_MODE = 'Y' THEN
2661: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2662: END IF;
2663:
2664:
2657: END IF;
2658:
2659: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Res_Parents ';
2660: IF P_PA_DEBUG_MODE = 'Y' THEN
2661: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2662: END IF;
2663:
2664:
2665: /* Check for Budget Version ID not being NULL. */
2663:
2664:
2665: /* Check for Budget Version ID not being NULL. */
2666: IF ( p_budget_version_id IS NULL) THEN
2667: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
2668: IF P_PA_DEBUG_MODE = 'Y' THEN
2669: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,5);
2670: END IF;
2671: x_return_status := FND_API.G_RET_STS_ERROR;
2664:
2665: /* Check for Budget Version ID not being NULL. */
2666: IF ( p_budget_version_id IS NULL) THEN
2667: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
2668: IF P_PA_DEBUG_MODE = 'Y' THEN
2669: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,5);
2670: END IF;
2671: x_return_status := FND_API.G_RET_STS_ERROR;
2672: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2665: /* Check for Budget Version ID not being NULL. */
2666: IF ( p_budget_version_id IS NULL) THEN
2667: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
2668: IF P_PA_DEBUG_MODE = 'Y' THEN
2669: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,5);
2670: END IF;
2671: x_return_status := FND_API.G_RET_STS_ERROR;
2672: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2673: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2675: END IF;
2676:
2677: /* Populate the local variables. */
2678:
2679: pa_debug.g_err_stage := 'calling populate_local_vars';
2680: IF P_PA_DEBUG_MODE = 'Y' THEN
2681: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2682: END IF;
2683:
2676:
2677: /* Populate the local variables. */
2678:
2679: pa_debug.g_err_stage := 'calling populate_local_vars';
2680: IF P_PA_DEBUG_MODE = 'Y' THEN
2681: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2682: END IF;
2683:
2684: populate_local_vars(p_budget_version_id => p_budget_version_id,
2677: /* Populate the local variables. */
2678:
2679: pa_debug.g_err_stage := 'calling populate_local_vars';
2680: IF P_PA_DEBUG_MODE = 'Y' THEN
2681: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2682: END IF;
2683:
2684: populate_local_vars(p_budget_version_id => p_budget_version_id,
2685: x_project_id => l_project_id,
2691: x_return_status => x_return_status,
2692: x_msg_count => x_msg_count,
2693: x_msg_data => x_msg_data);
2694:
2695: pa_debug.g_err_stage := 'checking if resource list is attached and is grouped';
2696: IF P_PA_DEBUG_MODE = 'Y' THEN
2697: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2698: END IF;
2699:
2692: x_msg_count => x_msg_count,
2693: x_msg_data => x_msg_data);
2694:
2695: pa_debug.g_err_stage := 'checking if resource list is attached and is grouped';
2696: IF P_PA_DEBUG_MODE = 'Y' THEN
2697: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2698: END IF;
2699:
2700: IF l_uncat_flag <> 'Y' THEN /* if Resource List is not uncategorized */
2693: x_msg_data => x_msg_data);
2694:
2695: pa_debug.g_err_stage := 'checking if resource list is attached and is grouped';
2696: IF P_PA_DEBUG_MODE = 'Y' THEN
2697: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2698: END IF;
2699:
2700: IF l_uncat_flag <> 'Y' THEN /* if Resource List is not uncategorized */
2701:
2698: END IF;
2699:
2700: IF l_uncat_flag <> 'Y' THEN /* if Resource List is not uncategorized */
2701:
2702: pa_debug.g_err_stage := 'resource list is attached';
2703: IF P_PA_DEBUG_MODE = 'Y' THEN
2704: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2705: END IF;
2706:
2699:
2700: IF l_uncat_flag <> 'Y' THEN /* if Resource List is not uncategorized */
2701:
2702: pa_debug.g_err_stage := 'resource list is attached';
2703: IF P_PA_DEBUG_MODE = 'Y' THEN
2704: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2705: END IF;
2706:
2707: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
2700: IF l_uncat_flag <> 'Y' THEN /* if Resource List is not uncategorized */
2701:
2702: pa_debug.g_err_stage := 'resource list is attached';
2703: IF P_PA_DEBUG_MODE = 'Y' THEN
2704: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2705: END IF;
2706:
2707: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
2708:
2705: END IF;
2706:
2707: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
2708:
2709: pa_debug.g_err_stage := 'resource list is grouped';
2710: IF P_PA_DEBUG_MODE = 'Y' THEN
2711: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2712: END IF;
2713:
2706:
2707: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
2708:
2709: pa_debug.g_err_stage := 'resource list is grouped';
2710: IF P_PA_DEBUG_MODE = 'Y' THEN
2711: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2712: END IF;
2713:
2714: LOOP
2707: IF nvl(l_rl_group_type_id,0) <> 0 THEN /* Res List is grouped */
2708:
2709: pa_debug.g_err_stage := 'resource list is grouped';
2710: IF P_PA_DEBUG_MODE = 'Y' THEN
2711: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2712: END IF;
2713:
2714: LOOP
2715:
2715:
2716: /* Update parent_assignment_id on pa_resource_assignments for the resource level records
2717: for which parents are inserted in last step. */
2718:
2719: pa_debug.g_err_stage := 'Updating Parent_Assignment_IDs for Recs having parents */';
2720: IF P_PA_DEBUG_MODE = 'Y' THEN
2721: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2722: END IF;
2723:
2716: /* Update parent_assignment_id on pa_resource_assignments for the resource level records
2717: for which parents are inserted in last step. */
2718:
2719: pa_debug.g_err_stage := 'Updating Parent_Assignment_IDs for Recs having parents */';
2720: IF P_PA_DEBUG_MODE = 'Y' THEN
2721: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2722: END IF;
2723:
2724: /* Bug #2622594: Modified the logic of updating the resource asignments for the
2717: for which parents are inserted in last step. */
2718:
2719: pa_debug.g_err_stage := 'Updating Parent_Assignment_IDs for Recs having parents */';
2720: IF P_PA_DEBUG_MODE = 'Y' THEN
2721: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2722: END IF;
2723:
2724: /* Bug #2622594: Modified the logic of updating the resource asignments for the
2725: resource level records. Using a cursor instead of a direct update. */
2751: UPDATE pa_resource_assignments
2752: SET parent_assignment_id = l_parent_ra_id_tbl(i)
2753: WHERE resource_assignment_id = l_ra_id_tbl(i);
2754:
2755: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2756: IF P_PA_DEBUG_MODE = 'Y' THEN
2757: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2758: END IF;
2759:
2752: SET parent_assignment_id = l_parent_ra_id_tbl(i)
2753: WHERE resource_assignment_id = l_ra_id_tbl(i);
2754:
2755: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2756: IF P_PA_DEBUG_MODE = 'Y' THEN
2757: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2758: END IF;
2759:
2760: END IF;
2753: WHERE resource_assignment_id = l_ra_id_tbl(i);
2754:
2755: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2756: IF P_PA_DEBUG_MODE = 'Y' THEN
2757: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2758: END IF;
2759:
2760: END IF;
2761:
2771: DELETE FROM pa_fp_ra_map_tmp
2772: WHERE resource_assignment_id = l_ra_id_tbl(i)
2773: AND l_parent_ra_id_tbl(i) IS NOT NULL;
2774:
2775: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from ra map tmp';
2776: IF P_PA_DEBUG_MODE = 'Y' THEN
2777: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2778: END IF;
2779:
2772: WHERE resource_assignment_id = l_ra_id_tbl(i)
2773: AND l_parent_ra_id_tbl(i) IS NOT NULL;
2774:
2775: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from ra map tmp';
2776: IF P_PA_DEBUG_MODE = 'Y' THEN
2777: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2778: END IF;
2779:
2780:
2773: AND l_parent_ra_id_tbl(i) IS NOT NULL;
2774:
2775: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from ra map tmp';
2776: IF P_PA_DEBUG_MODE = 'Y' THEN
2777: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2778: END IF;
2779:
2780:
2781: END IF;
2782:
2783: /* For the resource level records in pa_fp_ra_map_tmp, Insert the
2784: resource group level records. */
2785:
2786: pa_debug.g_err_stage := 'Inserting Resource Group Level records';
2787: IF P_PA_DEBUG_MODE = 'Y' THEN
2788: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2789: END IF;
2790: OPEN cur_parent_res_rec(p_budget_version_id,l_project_id);
2783: /* For the resource level records in pa_fp_ra_map_tmp, Insert the
2784: resource group level records. */
2785:
2786: pa_debug.g_err_stage := 'Inserting Resource Group Level records';
2787: IF P_PA_DEBUG_MODE = 'Y' THEN
2788: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2789: END IF;
2790: OPEN cur_parent_res_rec(p_budget_version_id,l_project_id);
2791:
2784: resource group level records. */
2785:
2786: pa_debug.g_err_stage := 'Inserting Resource Group Level records';
2787: IF P_PA_DEBUG_MODE = 'Y' THEN
2788: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2789: END IF;
2790: OPEN cur_parent_res_rec(p_budget_version_id,l_project_id);
2791:
2792: /* #2697999: Added two more PL/SQL tables to fetch the uom and track as labor flag */
2831: RETURNING resource_assignment_id
2832: BULK COLLECT INTO l_parent_res_id_tbl;
2833:
2834: END IF;
2835: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
2836: IF P_PA_DEBUG_MODE = 'Y' THEN
2837: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2838: END IF;
2839:
2832: BULK COLLECT INTO l_parent_res_id_tbl;
2833:
2834: END IF;
2835: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
2836: IF P_PA_DEBUG_MODE = 'Y' THEN
2837: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2838: END IF;
2839:
2840:
2833:
2834: END IF;
2835: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
2836: IF P_PA_DEBUG_MODE = 'Y' THEN
2837: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2838: END IF;
2839:
2840:
2841: /* Insert the parent returned into l_parent_ra_id_tbl
2850: VALUES
2851: (l_parent_res_id_tbl(i));
2852: END IF;
2853:
2854: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
2855: IF P_PA_DEBUG_MODE = 'Y' THEN
2856: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2857: END IF;
2858:
2851: (l_parent_res_id_tbl(i));
2852: END IF;
2853:
2854: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
2855: IF P_PA_DEBUG_MODE = 'Y' THEN
2856: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2857: END IF;
2858:
2859: CLOSE cur_parent_res_rec; /* M21-08: a wrong cursor was closed here */
2852: END IF;
2853:
2854: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
2855: IF P_PA_DEBUG_MODE = 'Y' THEN
2856: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2857: END IF;
2858:
2859: CLOSE cur_parent_res_rec; /* M21-08: a wrong cursor was closed here */
2860:
2860:
2861: END LOOP; /* Close of the loop */
2862: END IF; /* Res List is grouped */
2863:
2864: pa_debug.g_err_stage := 'done processing when resource list is grouped';
2865: IF P_PA_DEBUG_MODE = 'Y' THEN
2866: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2867: END IF;
2868:
2861: END LOOP; /* Close of the loop */
2862: END IF; /* Res List is grouped */
2863:
2864: pa_debug.g_err_stage := 'done processing when resource list is grouped';
2865: IF P_PA_DEBUG_MODE = 'Y' THEN
2866: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2867: END IF;
2868:
2869:
2862: END IF; /* Res List is grouped */
2863:
2864: pa_debug.g_err_stage := 'done processing when resource list is grouped';
2865: IF P_PA_DEBUG_MODE = 'Y' THEN
2866: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2867: END IF;
2868:
2869:
2870: /* If resource list is not grouped (group_resource_type_id = 0) then the temp table will
2881:
2882: /* If task level records are already present in the PA_Resource_Assignments table,
2883: then parent_assignment_id needs to be stamped on pa_Resource_assignments table. */
2884:
2885: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for resource level records';
2886: IF P_PA_DEBUG_MODE = 'Y' THEN
2887: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2888: END IF;
2889: UPDATE pa_resource_assignments pra1
2882: /* If task level records are already present in the PA_Resource_Assignments table,
2883: then parent_assignment_id needs to be stamped on pa_Resource_assignments table. */
2884:
2885: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for resource level records';
2886: IF P_PA_DEBUG_MODE = 'Y' THEN
2887: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2888: END IF;
2889: UPDATE pa_resource_assignments pra1
2890: SET parent_assignment_id =
2883: then parent_assignment_id needs to be stamped on pa_Resource_assignments table. */
2884:
2885: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for resource level records';
2886: IF P_PA_DEBUG_MODE = 'Y' THEN
2887: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2888: END IF;
2889: UPDATE pa_resource_assignments pra1
2890: SET parent_assignment_id =
2891: (SELECT resource_assignment_id
2899: AND pra1.budget_version_id = p_budget_version_id
2900: RETURNING resource_assignment_id, parent_assignment_id
2901: BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
2902:
2903: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2904: IF P_PA_DEBUG_MODE = 'Y' THEN
2905: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2906: END IF;
2907:
2900: RETURNING resource_assignment_id, parent_assignment_id
2901: BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
2902:
2903: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2904: IF P_PA_DEBUG_MODE = 'Y' THEN
2905: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2906: END IF;
2907:
2908: /* Delete the records from pa_fp_ra_map_tmp table where Resource Assignments are
2901: BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
2902:
2903: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
2904: IF P_PA_DEBUG_MODE = 'Y' THEN
2905: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2906: END IF;
2907:
2908: /* Delete the records from pa_fp_ra_map_tmp table where Resource Assignments are
2909: present in the PL/SQL table returned and the Parent ID is NOT NULL. */
2916: WHERE resource_assignment_id = l_ra_id_tbl(i)
2917: AND l_parent_ra_id_tbl(i) IS NOT NULL;
2918: END IF;
2919:
2920: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from ra map tmp';
2921: IF P_PA_DEBUG_MODE = 'Y' THEN
2922: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2923: END IF;
2924:
2917: AND l_parent_ra_id_tbl(i) IS NOT NULL;
2918: END IF;
2919:
2920: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from ra map tmp';
2921: IF P_PA_DEBUG_MODE = 'Y' THEN
2922: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2923: END IF;
2924:
2925: /* Insert task level records for the records available in pa_fp_ra_map_tmp table
2918: END IF;
2919:
2920: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from ra map tmp';
2921: IF P_PA_DEBUG_MODE = 'Y' THEN
2922: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2923: END IF;
2924:
2925: /* Insert task level records for the records available in pa_fp_ra_map_tmp table
2926: (as of now only those records are available for which task level records do not exist). */
2924:
2925: /* Insert task level records for the records available in pa_fp_ra_map_tmp table
2926: (as of now only those records are available for which task level records do not exist). */
2927:
2928: pa_debug.g_err_stage := 'Inserting Task Level Records';
2929: IF P_PA_DEBUG_MODE = 'Y' THEN
2930: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2931: END IF;
2932: OPEN cur_task_rec(p_budget_version_id,l_project_id);
2925: /* Insert task level records for the records available in pa_fp_ra_map_tmp table
2926: (as of now only those records are available for which task level records do not exist). */
2927:
2928: pa_debug.g_err_stage := 'Inserting Task Level Records';
2929: IF P_PA_DEBUG_MODE = 'Y' THEN
2930: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2931: END IF;
2932: OPEN cur_task_rec(p_budget_version_id,l_project_id);
2933:
2926: (as of now only those records are available for which task level records do not exist). */
2927:
2928: pa_debug.g_err_stage := 'Inserting Task Level Records';
2929: IF P_PA_DEBUG_MODE = 'Y' THEN
2930: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2931: END IF;
2932: OPEN cur_task_rec(p_budget_version_id,l_project_id);
2933:
2934: FETCH cur_task_rec BULK COLLECT INTO l_task_id_tbl;
2970: RETURNING resource_assignment_id
2971: BULK COLLECT INTO l_ra_id_tbl;
2972: END IF;
2973:
2974: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
2975: IF P_PA_DEBUG_MODE = 'Y' THEN
2976: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2977: END IF;
2978:
2971: BULK COLLECT INTO l_ra_id_tbl;
2972: END IF;
2973:
2974: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
2975: IF P_PA_DEBUG_MODE = 'Y' THEN
2976: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2977: END IF;
2978:
2979: /* Insert the newly generated resource assignment ids into pa_fp_ra_map_tmp table as for these
2972: END IF;
2973:
2974: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
2975: IF P_PA_DEBUG_MODE = 'Y' THEN
2976: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
2977: END IF;
2978:
2979: /* Insert the newly generated resource assignment ids into pa_fp_ra_map_tmp table as for these
2980: records either parents need to be find or inserted. */
2997: END IF; /* if Resource List is not uncategorized */
2998:
2999: /* The Following steps have to be done irrespective of whether the Resource List is attached or not.*/
3000:
3001: pa_debug.g_err_stage := 'now processing task level records. Inserting parent task level records for these';
3002: IF P_PA_DEBUG_MODE = 'Y' THEN
3003: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3004: END IF;
3005:
2998:
2999: /* The Following steps have to be done irrespective of whether the Resource List is attached or not.*/
3000:
3001: pa_debug.g_err_stage := 'now processing task level records. Inserting parent task level records for these';
3002: IF P_PA_DEBUG_MODE = 'Y' THEN
3003: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3004: END IF;
3005:
3006: l_task_id_tbl.delete;
2999: /* The Following steps have to be done irrespective of whether the Resource List is attached or not.*/
3000:
3001: pa_debug.g_err_stage := 'now processing task level records. Inserting parent task level records for these';
3002: IF P_PA_DEBUG_MODE = 'Y' THEN
3003: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3004: END IF;
3005:
3006: l_task_id_tbl.delete;
3007:
3018: WHEN NO_DATA_FOUND THEN
3019: l_continue_processing_flag := 'N';
3020: END;
3021:
3022: pa_debug.g_err_stage := 'l_continue_processing_flag is '||l_continue_processing_flag;
3023: IF P_PA_DEBUG_MODE = 'Y' THEN
3024: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3025: END IF;
3026:
3019: l_continue_processing_flag := 'N';
3020: END;
3021:
3022: pa_debug.g_err_stage := 'l_continue_processing_flag is '||l_continue_processing_flag;
3023: IF P_PA_DEBUG_MODE = 'Y' THEN
3024: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3025: END IF;
3026:
3027: IF (l_continue_processing_flag = 'Y') THEN /* Only if there are records existing in pa_fp_ra_map_tmp */
3020: END;
3021:
3022: pa_debug.g_err_stage := 'l_continue_processing_flag is '||l_continue_processing_flag;
3023: IF P_PA_DEBUG_MODE = 'Y' THEN
3024: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3025: END IF;
3026:
3027: IF (l_continue_processing_flag = 'Y') THEN /* Only if there are records existing in pa_fp_ra_map_tmp */
3028:
3025: END IF;
3026:
3027: IF (l_continue_processing_flag = 'Y') THEN /* Only if there are records existing in pa_fp_ra_map_tmp */
3028:
3029: pa_debug.g_err_stage := 'there are task level records to be processed';
3030: IF P_PA_DEBUG_MODE = 'Y' THEN
3031: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3032: END IF;
3033:
3026:
3027: IF (l_continue_processing_flag = 'Y') THEN /* Only if there are records existing in pa_fp_ra_map_tmp */
3028:
3029: pa_debug.g_err_stage := 'there are task level records to be processed';
3030: IF P_PA_DEBUG_MODE = 'Y' THEN
3031: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3032: END IF;
3033:
3034: IF (l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,
3027: IF (l_continue_processing_flag = 'Y') THEN /* Only if there are records existing in pa_fp_ra_map_tmp */
3028:
3029: pa_debug.g_err_stage := 'there are task level records to be processed';
3030: IF P_PA_DEBUG_MODE = 'Y' THEN
3031: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3032: END IF;
3033:
3034: IF (l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,
3035: PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT)) THEN
3042: into l_curr_wbs_level
3043: from pa_tasks
3044: where project_id = l_project_id;
3045:
3046: pa_debug.g_err_stage := 'l_curr_wbs_level =' || l_curr_wbs_level ;
3047: IF P_PA_DEBUG_MODE = 'Y' THEN
3048: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3049: END IF;
3050:
3043: from pa_tasks
3044: where project_id = l_project_id;
3045:
3046: pa_debug.g_err_stage := 'l_curr_wbs_level =' || l_curr_wbs_level ;
3047: IF P_PA_DEBUG_MODE = 'Y' THEN
3048: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3049: END IF;
3050:
3051: LOOP
3044: where project_id = l_project_id;
3045:
3046: pa_debug.g_err_stage := 'l_curr_wbs_level =' || l_curr_wbs_level ;
3047: IF P_PA_DEBUG_MODE = 'Y' THEN
3048: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3049: END IF;
3050:
3051: LOOP
3052: EXIT WHEN l_curr_wbs_level = 0; /* manokuma: changed during ut cur_parent_task_rec%NOTFOUND; */
3054:
3055: /* Before starting the insert we need to check if parent task records already exists or not
3056: and if yes then update PARENT_ASSIGNMENT_ID */
3057:
3058: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for task level records';
3059: IF P_PA_DEBUG_MODE = 'Y' THEN
3060: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3061: END IF;
3062:
3055: /* Before starting the insert we need to check if parent task records already exists or not
3056: and if yes then update PARENT_ASSIGNMENT_ID */
3057:
3058: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for task level records';
3059: IF P_PA_DEBUG_MODE = 'Y' THEN
3060: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3061: END IF;
3062:
3063: l_ra_id_tbl.delete;
3056: and if yes then update PARENT_ASSIGNMENT_ID */
3057:
3058: pa_debug.g_err_stage := 'Updating the Parent Assignment IDs for task level records';
3059: IF P_PA_DEBUG_MODE = 'Y' THEN
3060: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3061: END IF;
3062:
3063: l_ra_id_tbl.delete;
3064: l_parent_ra_id_tbl.delete;
3075: (select resource_assignment_id from pa_fp_ra_map_tmp)
3076: RETURNING resource_assignment_id, parent_assignment_id
3077: BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
3078:
3079: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records ';
3080: IF P_PA_DEBUG_MODE = 'Y' THEN
3081: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3082: END IF;
3083:
3076: RETURNING resource_assignment_id, parent_assignment_id
3077: BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
3078:
3079: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records ';
3080: IF P_PA_DEBUG_MODE = 'Y' THEN
3081: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3082: END IF;
3083:
3084: /* Delete the records from pa_fp_ra_map_tmp table where Resource Assignments are
3077: BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
3078:
3079: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records ';
3080: IF P_PA_DEBUG_MODE = 'Y' THEN
3081: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3082: END IF;
3083:
3084: /* Delete the records from pa_fp_ra_map_tmp table where Resource Assignments are
3085: present in the PL/SQL table returned and the Parent ID is NOT NULL. */
3090: DELETE FROM pa_fp_ra_map_tmp
3091: WHERE resource_assignment_id = l_ra_id_tbl(i)
3092: AND l_parent_ra_id_tbl(i) IS NOT NULL;
3093:
3094: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from map tmp';
3095: IF P_PA_DEBUG_MODE = 'Y' THEN
3096: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3097: END IF;
3098: END IF;
3091: WHERE resource_assignment_id = l_ra_id_tbl(i)
3092: AND l_parent_ra_id_tbl(i) IS NOT NULL;
3093:
3094: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from map tmp';
3095: IF P_PA_DEBUG_MODE = 'Y' THEN
3096: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3097: END IF;
3098: END IF;
3099:
3092: AND l_parent_ra_id_tbl(i) IS NOT NULL;
3093:
3094: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from map tmp';
3095: IF P_PA_DEBUG_MODE = 'Y' THEN
3096: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3097: END IF;
3098: END IF;
3099:
3100: /* Insert the Parent task level records for the records available in pa_fp_ra_map_tmp table */
3098: END IF;
3099:
3100: /* Insert the Parent task level records for the records available in pa_fp_ra_map_tmp table */
3101:
3102: pa_debug.g_err_stage := 'Inserting the Parent Task Level records';
3103: IF P_PA_DEBUG_MODE = 'Y' THEN
3104: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3105: END IF;
3106:
3099:
3100: /* Insert the Parent task level records for the records available in pa_fp_ra_map_tmp table */
3101:
3102: pa_debug.g_err_stage := 'Inserting the Parent Task Level records';
3103: IF P_PA_DEBUG_MODE = 'Y' THEN
3104: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3105: END IF;
3106:
3107: l_task_id_tbl.delete;
3100: /* Insert the Parent task level records for the records available in pa_fp_ra_map_tmp table */
3101:
3102: pa_debug.g_err_stage := 'Inserting the Parent Task Level records';
3103: IF P_PA_DEBUG_MODE = 'Y' THEN
3104: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3105: END IF;
3106:
3107: l_task_id_tbl.delete;
3108: OPEN cur_parent_task_rec(l_curr_wbs_level,p_budget_version_id,l_project_id);
3143: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
3144: RETURNING resource_assignment_id
3145: BULK COLLECT INTO l_ra_id_tbl;
3146:
3147: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3148: IF P_PA_DEBUG_MODE = 'Y' THEN
3149: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3150: END IF;
3151:
3144: RETURNING resource_assignment_id
3145: BULK COLLECT INTO l_ra_id_tbl;
3146:
3147: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3148: IF P_PA_DEBUG_MODE = 'Y' THEN
3149: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3150: END IF;
3151:
3152: /* #2697890: Moved the following code for inserting into pa_fp_ra_map_tmp from outside the
3145: BULK COLLECT INTO l_ra_id_tbl;
3146:
3147: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3148: IF P_PA_DEBUG_MODE = 'Y' THEN
3149: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3150: END IF;
3151:
3152: /* #2697890: Moved the following code for inserting into pa_fp_ra_map_tmp from outside the
3153: IF statement to inside the IF statement. The last time this loop is executed for the Top
3169:
3170: INSERT INTO pa_fp_ra_map_tmp(resource_assignment_id)
3171: VALUES (l_ra_id_tbl(i));
3172:
3173: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
3174: IF P_PA_DEBUG_MODE = 'Y' THEN
3175: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3176: END IF;
3177:
3170: INSERT INTO pa_fp_ra_map_tmp(resource_assignment_id)
3171: VALUES (l_ra_id_tbl(i));
3172:
3173: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
3174: IF P_PA_DEBUG_MODE = 'Y' THEN
3175: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3176: END IF;
3177:
3178: END IF;
3171: VALUES (l_ra_id_tbl(i));
3172:
3173: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
3174: IF P_PA_DEBUG_MODE = 'Y' THEN
3175: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3176: END IF;
3177:
3178: END IF;
3179:
3211:
3212: IF ((l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT)
3213: AND l_uncat_flag = 'Y' ) THEN
3214:
3215: pa_debug.g_err_stage := 'Not inserting Project Level Record';
3216: IF P_PA_DEBUG_MODE = 'Y' THEN
3217: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3218: END IF;
3219:
3212: IF ((l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT)
3213: AND l_uncat_flag = 'Y' ) THEN
3214:
3215: pa_debug.g_err_stage := 'Not inserting Project Level Record';
3216: IF P_PA_DEBUG_MODE = 'Y' THEN
3217: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3218: END IF;
3219:
3220: NULL;
3213: AND l_uncat_flag = 'Y' ) THEN
3214:
3215: pa_debug.g_err_stage := 'Not inserting Project Level Record';
3216: IF P_PA_DEBUG_MODE = 'Y' THEN
3217: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3218: END IF;
3219:
3220: NULL;
3221:
3232: EXCEPTION
3233:
3234: WHEN NO_DATA_FOUND THEN /* Project Level Record not found. Insert a Project Level record */
3235:
3236: pa_debug.g_err_stage := 'Inserting Project Level Record';
3237: IF P_PA_DEBUG_MODE = 'Y' THEN
3238: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3239: END IF;
3240:
3233:
3234: WHEN NO_DATA_FOUND THEN /* Project Level Record not found. Insert a Project Level record */
3235:
3236: pa_debug.g_err_stage := 'Inserting Project Level Record';
3237: IF P_PA_DEBUG_MODE = 'Y' THEN
3238: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3239: END IF;
3240:
3241: INSERT INTO pa_resource_assignments
3234: WHEN NO_DATA_FOUND THEN /* Project Level Record not found. Insert a Project Level record */
3235:
3236: pa_debug.g_err_stage := 'Inserting Project Level Record';
3237: IF P_PA_DEBUG_MODE = 'Y' THEN
3238: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3239: END IF;
3240:
3241: INSERT INTO pa_resource_assignments
3242: (RESOURCE_ASSIGNMENT_ID
3270: ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
3271: RETURNING resource_assignment_id
3272: INTO l_proj_ra_id;
3273:
3274: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3275: IF P_PA_DEBUG_MODE = 'Y' THEN
3276: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3277: END IF;
3278:
3271: RETURNING resource_assignment_id
3272: INTO l_proj_ra_id;
3273:
3274: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3275: IF P_PA_DEBUG_MODE = 'Y' THEN
3276: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3277: END IF;
3278:
3279: END;
3272: INTO l_proj_ra_id;
3273:
3274: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in res assignments';
3275: IF P_PA_DEBUG_MODE = 'Y' THEN
3276: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3277: END IF;
3278:
3279: END;
3280:
3280:
3281: /* Update all PA_RESOURCE_ASSIGNMENTS for resource_assignment id in pa_fp_ra_map_tmp table
3282: with parent_assignment_id as that obtained earlier. */
3283:
3284: pa_debug.g_err_stage := 'updating top records with project level record as parent ';
3285: IF P_PA_DEBUG_MODE = 'Y' THEN
3286: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3287: END IF;
3288:
3281: /* Update all PA_RESOURCE_ASSIGNMENTS for resource_assignment id in pa_fp_ra_map_tmp table
3282: with parent_assignment_id as that obtained earlier. */
3283:
3284: pa_debug.g_err_stage := 'updating top records with project level record as parent ';
3285: IF P_PA_DEBUG_MODE = 'Y' THEN
3286: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3287: END IF;
3288:
3289: UPDATE PA_RESOURCE_ASSIGNMENTS
3282: with parent_assignment_id as that obtained earlier. */
3283:
3284: pa_debug.g_err_stage := 'updating top records with project level record as parent ';
3285: IF P_PA_DEBUG_MODE = 'Y' THEN
3286: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3287: END IF;
3288:
3289: UPDATE PA_RESOURCE_ASSIGNMENTS
3290: SET parent_assignment_id = l_proj_ra_id
3293: FROM pa_fp_ra_map_tmp)
3294: AND project_id = l_project_id --bug#2708524
3295: AND budget_version_id = p_budget_version_id ; --bug#2708524
3296:
3297: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records. end of INSERT_MISSING_RES_PARENTS';
3298: IF P_PA_DEBUG_MODE = 'Y' THEN
3299: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3300: END IF;
3301:
3294: AND project_id = l_project_id --bug#2708524
3295: AND budget_version_id = p_budget_version_id ; --bug#2708524
3296:
3297: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records. end of INSERT_MISSING_RES_PARENTS';
3298: IF P_PA_DEBUG_MODE = 'Y' THEN
3299: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3300: END IF;
3301:
3302: END IF; /* planning level = project and resource list is uncategorized */
3295: AND budget_version_id = p_budget_version_id ; --bug#2708524
3296:
3297: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records. end of INSERT_MISSING_RES_PARENTS';
3298: IF P_PA_DEBUG_MODE = 'Y' THEN
3299: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3300: END IF;
3301:
3302: END IF; /* planning level = project and resource list is uncategorized */
3303: pa_debug.reset_err_stack;
3299: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,pa_debug.g_err_stage,3);
3300: END IF;
3301:
3302: END IF; /* planning level = project and resource list is uncategorized */
3303: pa_debug.reset_err_stack;
3304:
3305:
3306: EXCEPTION
3307: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
3318: x_msg_count := l_msg_count;
3319: ELSE
3320: x_msg_count := l_msg_count;
3321: END IF;
3322: pa_debug.reset_err_stack;
3323: RAISE;
3324: WHEN OTHERS THEN
3325: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3326: x_msg_count := 1;
3327: x_msg_data := SQLERRM;
3328: FND_MSG_PUB.add_exc_msg
3329: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
3330: ,p_procedure_name => 'Insert_Missing_Res_Parents');
3331: IF P_PA_DEBUG_MODE = 'Y' THEN
3332: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,SQLERRM,5);
3333: END IF;
3334: pa_debug.reset_err_stack;
3335:
3328: FND_MSG_PUB.add_exc_msg
3329: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
3330: ,p_procedure_name => 'Insert_Missing_Res_Parents');
3331: IF P_PA_DEBUG_MODE = 'Y' THEN
3332: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,SQLERRM,5);
3333: END IF;
3334: pa_debug.reset_err_stack;
3335:
3336: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3330: ,p_procedure_name => 'Insert_Missing_Res_Parents');
3331: IF P_PA_DEBUG_MODE = 'Y' THEN
3332: pa_debug.write('INSERT_MISSING_RES_PARENTS: ' || l_module_name,SQLERRM,5);
3333: END IF;
3334: pa_debug.reset_err_stack;
3335:
3336: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3337: END INSERT_MISSING_RES_PARENTS;
3338:
3392:
3393: BEGIN
3394:
3395: -- Set the error stack.
3396: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts');
3397:
3398: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3399: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3400: l_debug_mode := NVL(l_debug_mode, 'Y');
3395: -- Set the error stack.
3396: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts');
3397:
3398: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3399: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3400: l_debug_mode := NVL(l_debug_mode, 'Y');
3401:
3402: -- Initialize the return status to success
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3401:
3402: -- Initialize the return status to success
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3404:
3405: IF P_PA_DEBUG_MODE = 'Y' THEN
3406: pa_debug.set_process('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3407: END IF;
3408:
3409: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts ';
3402: -- Initialize the return status to success
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3404:
3405: IF P_PA_DEBUG_MODE = 'Y' THEN
3406: pa_debug.set_process('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3407: END IF;
3408:
3409: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts ';
3410: IF P_PA_DEBUG_MODE = 'Y' THEN
3405: IF P_PA_DEBUG_MODE = 'Y' THEN
3406: pa_debug.set_process('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3407: END IF;
3408:
3409: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts ';
3410: IF P_PA_DEBUG_MODE = 'Y' THEN
3411: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3412: END IF;
3413:
3406: pa_debug.set_process('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3407: END IF;
3408:
3409: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts ';
3410: IF P_PA_DEBUG_MODE = 'Y' THEN
3411: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3412: END IF;
3413:
3414: /* Check for Budget Version ID not being NULL. */
3407: END IF;
3408:
3409: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Res_Assignment_Amounts ';
3410: IF P_PA_DEBUG_MODE = 'Y' THEN
3411: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3412: END IF;
3413:
3414: /* Check for Budget Version ID not being NULL. */
3415: IF ( p_budget_version_id IS NULL) THEN
3412: END IF;
3413:
3414: /* Check for Budget Version ID not being NULL. */
3415: IF ( p_budget_version_id IS NULL) THEN
3416: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3417: IF P_PA_DEBUG_MODE = 'Y' THEN
3418: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,5);
3419: END IF;
3420: x_return_status := FND_API.G_RET_STS_ERROR;
3413:
3414: /* Check for Budget Version ID not being NULL. */
3415: IF ( p_budget_version_id IS NULL) THEN
3416: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3417: IF P_PA_DEBUG_MODE = 'Y' THEN
3418: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,5);
3419: END IF;
3420: x_return_status := FND_API.G_RET_STS_ERROR;
3421: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3414: /* Check for Budget Version ID not being NULL. */
3415: IF ( p_budget_version_id IS NULL) THEN
3416: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3417: IF P_PA_DEBUG_MODE = 'Y' THEN
3418: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,5);
3419: END IF;
3420: x_return_status := FND_API.G_RET_STS_ERROR;
3421: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3422: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3426: OPEN c_res_amt_diffs;
3427:
3428: /* Bulk collect the amount diffs into PL/SQL tables */
3429:
3430: pa_debug.g_err_stage := 'Bulk collecting the amount diffs into PL/SQL tables';
3431: IF P_PA_DEBUG_MODE = 'Y' THEN
3432: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3433: END IF;
3434: FETCH c_res_amt_diffs BULK COLLECT INTO
3427:
3428: /* Bulk collect the amount diffs into PL/SQL tables */
3429:
3430: pa_debug.g_err_stage := 'Bulk collecting the amount diffs into PL/SQL tables';
3431: IF P_PA_DEBUG_MODE = 'Y' THEN
3432: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3433: END IF;
3434: FETCH c_res_amt_diffs BULK COLLECT INTO
3435: l_ra_id_tbl
3428: /* Bulk collect the amount diffs into PL/SQL tables */
3429:
3430: pa_debug.g_err_stage := 'Bulk collecting the amount diffs into PL/SQL tables';
3431: IF P_PA_DEBUG_MODE = 'Y' THEN
3432: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3433: END IF;
3434: FETCH c_res_amt_diffs BULK COLLECT INTO
3435: l_ra_id_tbl
3436: ,l_proj_raw_cost_tbl
3441: ,l_projfunc_revenue_tbl
3442: ,l_quantity_tbl;
3443: CLOSE c_res_amt_diffs;
3444:
3445: pa_debug.g_err_stage := 'fetched ' || sql%rowcount || ' records';
3446: IF P_PA_DEBUG_MODE = 'Y' THEN
3447: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3448: END IF;
3449:
3442: ,l_quantity_tbl;
3443: CLOSE c_res_amt_diffs;
3444:
3445: pa_debug.g_err_stage := 'fetched ' || sql%rowcount || ' records';
3446: IF P_PA_DEBUG_MODE = 'Y' THEN
3447: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3448: END IF;
3449:
3450: /* Creating a Loop to keep updating the amount difference on the resource
3443: CLOSE c_res_amt_diffs;
3444:
3445: pa_debug.g_err_stage := 'fetched ' || sql%rowcount || ' records';
3446: IF P_PA_DEBUG_MODE = 'Y' THEN
3447: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3448: END IF;
3449:
3450: /* Creating a Loop to keep updating the amount difference on the resource
3451: assignments and their parent assignments until no parent records are found. */
3458: else
3459: l_upd_user_entered_rec_flg := 'N';
3460: end if;
3461:
3462: pa_debug.g_err_stage := 'l_upd_user_entered_rec_flg = ' || l_upd_user_entered_rec_flg;
3463: IF P_PA_DEBUG_MODE = 'Y' THEN
3464: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3465: END IF;
3466:
3459: l_upd_user_entered_rec_flg := 'N';
3460: end if;
3461:
3462: pa_debug.g_err_stage := 'l_upd_user_entered_rec_flg = ' || l_upd_user_entered_rec_flg;
3463: IF P_PA_DEBUG_MODE = 'Y' THEN
3464: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3465: END IF;
3466:
3467: IF nvl(l_ra_id_tbl.last,0) >= 1 THEN
3460: end if;
3461:
3462: pa_debug.g_err_stage := 'l_upd_user_entered_rec_flg = ' || l_upd_user_entered_rec_flg;
3463: IF P_PA_DEBUG_MODE = 'Y' THEN
3464: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3465: END IF;
3466:
3467: IF nvl(l_ra_id_tbl.last,0) >= 1 THEN
3468:
3474: But if the records are not USER_ENTERED, we have to consider
3475: the quantities only if the Unit OF Measure is HOURS. Hence the Update stmt
3476: has been split into two depending on the value of the flag. */
3477:
3478: pa_debug.g_err_stage := 'Updating amounts on pa_resource_assignments for user_entered recs- 1';
3479: IF P_PA_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3481: END IF;
3482:
3475: the quantities only if the Unit OF Measure is HOURS. Hence the Update stmt
3476: has been split into two depending on the value of the flag. */
3477:
3478: pa_debug.g_err_stage := 'Updating amounts on pa_resource_assignments for user_entered recs- 1';
3479: IF P_PA_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3481: END IF;
3482:
3483: FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3476: has been split into two depending on the value of the flag. */
3477:
3478: pa_debug.g_err_stage := 'Updating amounts on pa_resource_assignments for user_entered recs- 1';
3479: IF P_PA_DEBUG_MODE = 'Y' THEN
3480: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3481: END IF;
3482:
3483: FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3484: /* Update pa_resource_assignments for the records found in last step
3494: WHERE resource_assignment_id = l_ra_id_tbl(i)
3495: RETURNING parent_assignment_id, unit_of_measure
3496: BULK COLLECT INTO l_par_id_tbl, l_uom_tbl;
3497:
3498: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
3499: IF P_PA_DEBUG_MODE = 'Y' THEN
3500: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3501: END IF;
3502:
3495: RETURNING parent_assignment_id, unit_of_measure
3496: BULK COLLECT INTO l_par_id_tbl, l_uom_tbl;
3497:
3498: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
3499: IF P_PA_DEBUG_MODE = 'Y' THEN
3500: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3501: END IF;
3502:
3503: -- l_upd_rec := SQL%ROWCOUNT; Not required now.
3496: BULK COLLECT INTO l_par_id_tbl, l_uom_tbl;
3497:
3498: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
3499: IF P_PA_DEBUG_MODE = 'Y' THEN
3500: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3501: END IF;
3502:
3503: -- l_upd_rec := SQL%ROWCOUNT; Not required now.
3504: ELSE
3502:
3503: -- l_upd_rec := SQL%ROWCOUNT; Not required now.
3504: ELSE
3505:
3506: pa_debug.g_err_stage := 'Updating amounts on pa_resource_assignments for rolled up recs ';
3507: IF P_PA_DEBUG_MODE = 'Y' THEN
3508: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3509: END IF;
3510:
3503: -- l_upd_rec := SQL%ROWCOUNT; Not required now.
3504: ELSE
3505:
3506: pa_debug.g_err_stage := 'Updating amounts on pa_resource_assignments for rolled up recs ';
3507: IF P_PA_DEBUG_MODE = 'Y' THEN
3508: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3509: END IF;
3510:
3511: FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3504: ELSE
3505:
3506: pa_debug.g_err_stage := 'Updating amounts on pa_resource_assignments for rolled up recs ';
3507: IF P_PA_DEBUG_MODE = 'Y' THEN
3508: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3509: END IF;
3510:
3511: FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3512: /* Update pa_resource_assignments for the records found in last step
3523: WHERE resource_assignment_id = l_ra_id_tbl(i)
3524: RETURNING parent_assignment_id
3525: BULK COLLECT INTO l_par_id_tbl;
3526:
3527: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
3528: IF P_PA_DEBUG_MODE = 'Y' THEN
3529: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3530: END IF;
3531:
3524: RETURNING parent_assignment_id
3525: BULK COLLECT INTO l_par_id_tbl;
3526:
3527: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
3528: IF P_PA_DEBUG_MODE = 'Y' THEN
3529: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3530: END IF;
3531:
3532: END IF;
3525: BULK COLLECT INTO l_par_id_tbl;
3526:
3527: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
3528: IF P_PA_DEBUG_MODE = 'Y' THEN
3529: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3530: END IF;
3531:
3532: END IF;
3533:
3535:
3536: /* Put back the parent records from l_par_id_tbl into l_ra_id_tbl so that
3537: they can be processed again in the loop. */
3538:
3539: pa_debug.g_err_stage := 'Putting the Parent Assignment IDs into the original table';
3540: IF P_PA_DEBUG_MODE = 'Y' THEN
3541: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3542: END IF;
3543:
3536: /* Put back the parent records from l_par_id_tbl into l_ra_id_tbl so that
3537: they can be processed again in the loop. */
3538:
3539: pa_debug.g_err_stage := 'Putting the Parent Assignment IDs into the original table';
3540: IF P_PA_DEBUG_MODE = 'Y' THEN
3541: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3542: END IF;
3543:
3544: l_ra_id_tbl.delete;
3537: they can be processed again in the loop. */
3538:
3539: pa_debug.g_err_stage := 'Putting the Parent Assignment IDs into the original table';
3540: IF P_PA_DEBUG_MODE = 'Y' THEN
3541: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3542: END IF;
3543:
3544: l_ra_id_tbl.delete;
3545:
3548: IF nvl(l_par_id_tbl.last,0) >= 1 THEN
3549:
3550: FOR i in l_par_id_tbl.first..l_par_id_tbl.last
3551: LOOP
3552: pa_debug.g_err_stage := 'i = ' || i || ' l_par_id_tbl.first = ' || l_par_id_tbl.first || ' l_par_id_tbl.last = ' || l_par_id_tbl.last;
3553: IF P_PA_DEBUG_MODE = 'Y' THEN
3554: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3555: END IF;
3556:
3549:
3550: FOR i in l_par_id_tbl.first..l_par_id_tbl.last
3551: LOOP
3552: pa_debug.g_err_stage := 'i = ' || i || ' l_par_id_tbl.first = ' || l_par_id_tbl.first || ' l_par_id_tbl.last = ' || l_par_id_tbl.last;
3553: IF P_PA_DEBUG_MODE = 'Y' THEN
3554: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3555: END IF;
3556:
3557: pa_debug.g_err_stage := 'l_par_id_tbl(i) = ' || l_par_id_tbl(i);
3550: FOR i in l_par_id_tbl.first..l_par_id_tbl.last
3551: LOOP
3552: pa_debug.g_err_stage := 'i = ' || i || ' l_par_id_tbl.first = ' || l_par_id_tbl.first || ' l_par_id_tbl.last = ' || l_par_id_tbl.last;
3553: IF P_PA_DEBUG_MODE = 'Y' THEN
3554: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3555: END IF;
3556:
3557: pa_debug.g_err_stage := 'l_par_id_tbl(i) = ' || l_par_id_tbl(i);
3558: IF P_PA_DEBUG_MODE = 'Y' THEN
3553: IF P_PA_DEBUG_MODE = 'Y' THEN
3554: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3555: END IF;
3556:
3557: pa_debug.g_err_stage := 'l_par_id_tbl(i) = ' || l_par_id_tbl(i);
3558: IF P_PA_DEBUG_MODE = 'Y' THEN
3559: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3560: END IF;
3561:
3554: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3555: END IF;
3556:
3557: pa_debug.g_err_stage := 'l_par_id_tbl(i) = ' || l_par_id_tbl(i);
3558: IF P_PA_DEBUG_MODE = 'Y' THEN
3559: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3560: END IF;
3561:
3562: IF l_par_id_tbl(i) IS NOT NULL THEN
3555: END IF;
3556:
3557: pa_debug.g_err_stage := 'l_par_id_tbl(i) = ' || l_par_id_tbl(i);
3558: IF P_PA_DEBUG_MODE = 'Y' THEN
3559: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3560: END IF;
3561:
3562: IF l_par_id_tbl(i) IS NOT NULL THEN
3563: l_ra_id_tbl(l_index) := l_par_id_tbl(i);
3581:
3582: l_index := l_index + 1;
3583: END IF;
3584:
3585: pa_debug.g_err_stage := 'l_index = ' || l_index;
3586: IF P_PA_DEBUG_MODE = 'Y' THEN
3587: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3588: END IF;
3589:
3582: l_index := l_index + 1;
3583: END IF;
3584:
3585: pa_debug.g_err_stage := 'l_index = ' || l_index;
3586: IF P_PA_DEBUG_MODE = 'Y' THEN
3587: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3588: END IF;
3589:
3590: END LOOP;
3583: END IF;
3584:
3585: pa_debug.g_err_stage := 'l_index = ' || l_index;
3586: IF P_PA_DEBUG_MODE = 'Y' THEN
3587: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3588: END IF;
3589:
3590: END LOOP;
3591:
3591:
3592: END IF;
3593:
3594: IF l_index = 1 THEN /* means no not null parent was found */
3595: pa_debug.g_err_stage := 'exiting ' || ' l_index = ' || l_index;
3596: IF P_PA_DEBUG_MODE = 'Y' THEN
3597: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3598: END IF;
3599:
3592: END IF;
3593:
3594: IF l_index = 1 THEN /* means no not null parent was found */
3595: pa_debug.g_err_stage := 'exiting ' || ' l_index = ' || l_index;
3596: IF P_PA_DEBUG_MODE = 'Y' THEN
3597: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3598: END IF;
3599:
3600: EXIT;
3593:
3594: IF l_index = 1 THEN /* means no not null parent was found */
3595: pa_debug.g_err_stage := 'exiting ' || ' l_index = ' || l_index;
3596: IF P_PA_DEBUG_MODE = 'Y' THEN
3597: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3598: END IF;
3599:
3600: EXIT;
3601: END IF;
3601: END IF;
3602:
3603: END LOOP;
3604:
3605: pa_debug.g_err_stage := 'end of ROLLUP_RES_ASSIGNMENT_AMOUNTS' ;
3606: IF P_PA_DEBUG_MODE = 'Y' THEN
3607: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3608: END IF;
3609: pa_debug.reset_err_stack;
3602:
3603: END LOOP;
3604:
3605: pa_debug.g_err_stage := 'end of ROLLUP_RES_ASSIGNMENT_AMOUNTS' ;
3606: IF P_PA_DEBUG_MODE = 'Y' THEN
3607: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3608: END IF;
3609: pa_debug.reset_err_stack;
3610:
3603: END LOOP;
3604:
3605: pa_debug.g_err_stage := 'end of ROLLUP_RES_ASSIGNMENT_AMOUNTS' ;
3606: IF P_PA_DEBUG_MODE = 'Y' THEN
3607: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3608: END IF;
3609: pa_debug.reset_err_stack;
3610:
3611: EXCEPTION
3605: pa_debug.g_err_stage := 'end of ROLLUP_RES_ASSIGNMENT_AMOUNTS' ;
3606: IF P_PA_DEBUG_MODE = 'Y' THEN
3607: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3608: END IF;
3609: pa_debug.reset_err_stack;
3610:
3611: EXCEPTION
3612: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
3613: l_msg_count := FND_MSG_PUB.count_msg;
3623: x_msg_count := l_msg_count;
3624: ELSE
3625: x_msg_count := l_msg_count;
3626: END IF;
3627: pa_debug.reset_err_stack;
3628: RAISE;
3629: WHEN OTHERS THEN
3630: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3631: x_msg_count := 1;
3632: x_msg_data := SQLERRM;
3633: FND_MSG_PUB.add_exc_msg
3634: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
3635: ,p_procedure_name => 'Rollup_Res_Assignment_Amounts');
3636: IF P_PA_DEBUG_MODE = 'Y' THEN
3637: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,SQLERRM,5);
3638: END IF;
3639: pa_debug.reset_err_stack;
3640:
3633: FND_MSG_PUB.add_exc_msg
3634: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
3635: ,p_procedure_name => 'Rollup_Res_Assignment_Amounts');
3636: IF P_PA_DEBUG_MODE = 'Y' THEN
3637: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,SQLERRM,5);
3638: END IF;
3639: pa_debug.reset_err_stack;
3640:
3641: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3635: ,p_procedure_name => 'Rollup_Res_Assignment_Amounts');
3636: IF P_PA_DEBUG_MODE = 'Y' THEN
3637: pa_debug.write('ROLLUP_RES_ASSIGNMENT_AMOUNTS: ' || l_module_name,SQLERRM,5);
3638: END IF;
3639: pa_debug.reset_err_stack;
3640:
3641: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3642: END ROLLUP_RES_ASSIGNMENT_AMOUNTS;
3643:
3721: BEGIN
3722:
3723:
3724: -- Set the error stack.
3725: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm');
3726:
3727: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3728: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3729: l_debug_mode := NVL(l_debug_mode, 'Y');
3724: -- Set the error stack.
3725: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm');
3726:
3727: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3728: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3729: l_debug_mode := NVL(l_debug_mode, 'Y');
3730:
3731: -- Initialize the return status to success
3732: x_return_status := FND_API.G_RET_STS_SUCCESS;
3730:
3731: -- Initialize the return status to success
3732: x_return_status := FND_API.G_RET_STS_SUCCESS;
3733:
3734: IF P_PA_DEBUG_MODE = 'Y' THEN
3735: pa_debug.set_process('INSERT_MISSING_PARENT_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
3736: END IF;
3737:
3738: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm ';
3731: -- Initialize the return status to success
3732: x_return_status := FND_API.G_RET_STS_SUCCESS;
3733:
3734: IF P_PA_DEBUG_MODE = 'Y' THEN
3735: pa_debug.set_process('INSERT_MISSING_PARENT_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
3736: END IF;
3737:
3738: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm ';
3739: IF P_PA_DEBUG_MODE = 'Y' THEN
3734: IF P_PA_DEBUG_MODE = 'Y' THEN
3735: pa_debug.set_process('INSERT_MISSING_PARENT_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
3736: END IF;
3737:
3738: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm ';
3739: IF P_PA_DEBUG_MODE = 'Y' THEN
3740: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3741: END IF;
3742:
3735: pa_debug.set_process('INSERT_MISSING_PARENT_DENORM: ' || 'PLSQL','LOG',l_debug_mode);
3736: END IF;
3737:
3738: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm ';
3739: IF P_PA_DEBUG_MODE = 'Y' THEN
3740: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3741: END IF;
3742:
3743:
3736: END IF;
3737:
3738: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Insert_Missing_Parent_Denorm ';
3739: IF P_PA_DEBUG_MODE = 'Y' THEN
3740: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3741: END IF;
3742:
3743:
3744: /* Check for Budget Version ID not being NULL. */
3742:
3743:
3744: /* Check for Budget Version ID not being NULL. */
3745: IF ( p_budget_version_id IS NULL) THEN
3746: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3747: IF P_PA_DEBUG_MODE = 'Y' THEN
3748: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,5);
3749: END IF;
3750: x_return_status := FND_API.G_RET_STS_ERROR;
3743:
3744: /* Check for Budget Version ID not being NULL. */
3745: IF ( p_budget_version_id IS NULL) THEN
3746: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3747: IF P_PA_DEBUG_MODE = 'Y' THEN
3748: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,5);
3749: END IF;
3750: x_return_status := FND_API.G_RET_STS_ERROR;
3751: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3744: /* Check for Budget Version ID not being NULL. */
3745: IF ( p_budget_version_id IS NULL) THEN
3746: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3747: IF P_PA_DEBUG_MODE = 'Y' THEN
3748: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,5);
3749: END IF;
3750: x_return_status := FND_API.G_RET_STS_ERROR;
3751: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3752: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3769: INTO l_period_profile_id
3770: FROM pa_budget_versions
3771: WHERE budget_version_id = p_budget_version_id;
3772:
3773: pa_debug.g_err_stage := 'period profile id = ' || l_period_profile_id ;
3774: IF P_PA_DEBUG_MODE = 'Y' THEN
3775: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3776: END IF;
3777:
3770: FROM pa_budget_versions
3771: WHERE budget_version_id = p_budget_version_id;
3772:
3773: pa_debug.g_err_stage := 'period profile id = ' || l_period_profile_id ;
3774: IF P_PA_DEBUG_MODE = 'Y' THEN
3775: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3776: END IF;
3777:
3778: /* #2801522: Getting the project currency code for storing in the 'QAUNTITY' record. */
3771: WHERE budget_version_id = p_budget_version_id;
3772:
3773: pa_debug.g_err_stage := 'period profile id = ' || l_period_profile_id ;
3774: IF P_PA_DEBUG_MODE = 'Y' THEN
3775: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3776: END IF;
3777:
3778: /* #2801522: Getting the project currency code for storing in the 'QAUNTITY' record. */
3779:
3809: ELSE
3810: L_INSERTING_PARENT_TASK_LEVEL := true;
3811: END IF;
3812:
3813: pa_debug.g_err_stage := 'Inserting Parent Records into pa_proj_periods_denorm in a loop';
3814: IF P_PA_DEBUG_MODE = 'Y' THEN
3815: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3816: END IF;
3817:
3810: L_INSERTING_PARENT_TASK_LEVEL := true;
3811: END IF;
3812:
3813: pa_debug.g_err_stage := 'Inserting Parent Records into pa_proj_periods_denorm in a loop';
3814: IF P_PA_DEBUG_MODE = 'Y' THEN
3815: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3816: END IF;
3817:
3818: LOOP
3811: END IF;
3812:
3813: pa_debug.g_err_stage := 'Inserting Parent Records into pa_proj_periods_denorm in a loop';
3814: IF P_PA_DEBUG_MODE = 'Y' THEN
3815: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3816: END IF;
3817:
3818: LOOP
3819: EXIT WHEN l_curr_rollup_level = 0;
3883: ,sysdate
3884: ,fnd_global.user_id
3885: ,fnd_global.login_id);
3886:
3887: pa_debug.g_err_stage := 'Inserted ' || sql%rowcount || ' records into denorm table';
3888: IF P_PA_DEBUG_MODE = 'Y' THEN
3889: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3890: END IF;
3891: END IF;
3884: ,fnd_global.user_id
3885: ,fnd_global.login_id);
3886:
3887: pa_debug.g_err_stage := 'Inserted ' || sql%rowcount || ' records into denorm table';
3888: IF P_PA_DEBUG_MODE = 'Y' THEN
3889: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3890: END IF;
3891: END IF;
3892: pa_debug.g_err_stage := 'current rollup level = ' || l_curr_rollup_level;
3885: ,fnd_global.login_id);
3886:
3887: pa_debug.g_err_stage := 'Inserted ' || sql%rowcount || ' records into denorm table';
3888: IF P_PA_DEBUG_MODE = 'Y' THEN
3889: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3890: END IF;
3891: END IF;
3892: pa_debug.g_err_stage := 'current rollup level = ' || l_curr_rollup_level;
3893: IF P_PA_DEBUG_MODE = 'Y' THEN
3888: IF P_PA_DEBUG_MODE = 'Y' THEN
3889: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3890: END IF;
3891: END IF;
3892: pa_debug.g_err_stage := 'current rollup level = ' || l_curr_rollup_level;
3893: IF P_PA_DEBUG_MODE = 'Y' THEN
3894: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3895: END IF;
3896:
3889: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3890: END IF;
3891: END IF;
3892: pa_debug.g_err_stage := 'current rollup level = ' || l_curr_rollup_level;
3893: IF P_PA_DEBUG_MODE = 'Y' THEN
3894: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3895: END IF;
3896:
3897: l_curr_rollup_level := l_curr_rollup_level - 1;
3890: END IF;
3891: END IF;
3892: pa_debug.g_err_stage := 'current rollup level = ' || l_curr_rollup_level;
3893: IF P_PA_DEBUG_MODE = 'Y' THEN
3894: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3895: END IF;
3896:
3897: l_curr_rollup_level := l_curr_rollup_level - 1;
3898: END LOOP;
3898: END LOOP;
3899:
3900: /* Update the Parent Assignment IDs of the records that have been entered. */
3901: /* M21-AUG moved this out of the loop */
3902: pa_debug.g_err_stage := 'Calling UPDATE_DENORM_PARENT_ASSIGN_ID';
3903: IF P_PA_DEBUG_MODE = 'Y' THEN
3904: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3905: END IF;
3906: UPDATE_DENORM_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
3899:
3900: /* Update the Parent Assignment IDs of the records that have been entered. */
3901: /* M21-AUG moved this out of the loop */
3902: pa_debug.g_err_stage := 'Calling UPDATE_DENORM_PARENT_ASSIGN_ID';
3903: IF P_PA_DEBUG_MODE = 'Y' THEN
3904: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3905: END IF;
3906: UPDATE_DENORM_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
3907: ,x_return_status => x_return_status
3900: /* Update the Parent Assignment IDs of the records that have been entered. */
3901: /* M21-AUG moved this out of the loop */
3902: pa_debug.g_err_stage := 'Calling UPDATE_DENORM_PARENT_ASSIGN_ID';
3903: IF P_PA_DEBUG_MODE = 'Y' THEN
3904: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3905: END IF;
3906: UPDATE_DENORM_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
3907: ,x_return_status => x_return_status
3908: ,x_msg_count => x_msg_count
3907: ,x_return_status => x_return_status
3908: ,x_msg_count => x_msg_count
3909: ,x_msg_data => x_msg_data);
3910:
3911: pa_debug.g_err_stage := 'end of INSERT_MISSING_PARENT_DENORM';
3912: IF P_PA_DEBUG_MODE = 'Y' THEN
3913: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3914: END IF;
3915:
3908: ,x_msg_count => x_msg_count
3909: ,x_msg_data => x_msg_data);
3910:
3911: pa_debug.g_err_stage := 'end of INSERT_MISSING_PARENT_DENORM';
3912: IF P_PA_DEBUG_MODE = 'Y' THEN
3913: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3914: END IF;
3915:
3916: pa_debug.reset_err_stack;
3909: ,x_msg_data => x_msg_data);
3910:
3911: pa_debug.g_err_stage := 'end of INSERT_MISSING_PARENT_DENORM';
3912: IF P_PA_DEBUG_MODE = 'Y' THEN
3913: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3914: END IF;
3915:
3916: pa_debug.reset_err_stack;
3917:
3912: IF P_PA_DEBUG_MODE = 'Y' THEN
3913: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,pa_debug.g_err_stage,3);
3914: END IF;
3915:
3916: pa_debug.reset_err_stack;
3917:
3918: EXCEPTION
3919: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
3920: l_msg_count := FND_MSG_PUB.count_msg;
3930: x_msg_count := l_msg_count;
3931: ELSE
3932: x_msg_count := l_msg_count;
3933: END IF;
3934: pa_debug.reset_err_stack;
3935: RAISE;
3936: WHEN OTHERS THEN
3937: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3938: x_msg_count := 1;
3939: x_msg_data := SQLERRM;
3940: FND_MSG_PUB.add_exc_msg
3941: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
3942: ,p_procedure_name => 'Insert_Missing_Parent_Denorm');
3943: IF P_PA_DEBUG_MODE = 'Y' THEN
3944: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,'sqlerrm = ' || SQLERRM,5);
3945: END IF;
3946: pa_debug.reset_err_stack;
3947:
3940: FND_MSG_PUB.add_exc_msg
3941: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
3942: ,p_procedure_name => 'Insert_Missing_Parent_Denorm');
3943: IF P_PA_DEBUG_MODE = 'Y' THEN
3944: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,'sqlerrm = ' || SQLERRM,5);
3945: END IF;
3946: pa_debug.reset_err_stack;
3947:
3948: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3942: ,p_procedure_name => 'Insert_Missing_Parent_Denorm');
3943: IF P_PA_DEBUG_MODE = 'Y' THEN
3944: pa_debug.write('INSERT_MISSING_PARENT_DENORM: ' || l_module_name,'sqlerrm = ' || SQLERRM,5);
3945: END IF;
3946: pa_debug.reset_err_stack;
3947:
3948: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3949: END INSERT_MISSING_PARENT_DENORM;
3950:
3973:
3974: BEGIN
3975:
3976: -- Set the error stack.
3977: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts');
3978:
3979: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3980: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3981: l_debug_mode := NVL(l_debug_mode, 'Y');
3976: -- Set the error stack.
3977: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts');
3978:
3979: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
3980: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3981: l_debug_mode := NVL(l_debug_mode, 'Y');
3982:
3983: -- Initialize the return status to success
3984: x_return_status := FND_API.G_RET_STS_SUCCESS;
3982:
3983: -- Initialize the return status to success
3984: x_return_status := FND_API.G_RET_STS_SUCCESS;
3985:
3986: IF P_PA_DEBUG_MODE = 'Y' THEN
3987: pa_debug.set_process('ROLLUP_DENORM_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3988: END IF;
3989:
3990: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts ';
3983: -- Initialize the return status to success
3984: x_return_status := FND_API.G_RET_STS_SUCCESS;
3985:
3986: IF P_PA_DEBUG_MODE = 'Y' THEN
3987: pa_debug.set_process('ROLLUP_DENORM_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3988: END IF;
3989:
3990: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts ';
3991: IF P_PA_DEBUG_MODE = 'Y' THEN
3986: IF P_PA_DEBUG_MODE = 'Y' THEN
3987: pa_debug.set_process('ROLLUP_DENORM_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3988: END IF;
3989:
3990: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts ';
3991: IF P_PA_DEBUG_MODE = 'Y' THEN
3992: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3993: END IF;
3994:
3987: pa_debug.set_process('ROLLUP_DENORM_AMOUNTS: ' || 'PLSQL','LOG',l_debug_mode);
3988: END IF;
3989:
3990: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts ';
3991: IF P_PA_DEBUG_MODE = 'Y' THEN
3992: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3993: END IF;
3994:
3995:
3988: END IF;
3989:
3990: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Rollup_Denorm_Amounts ';
3991: IF P_PA_DEBUG_MODE = 'Y' THEN
3992: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
3993: END IF;
3994:
3995:
3996: /* Check for Budget Version ID not being NULL. */
3994:
3995:
3996: /* Check for Budget Version ID not being NULL. */
3997: IF ( p_budget_version_id IS NULL) THEN
3998: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3999: IF P_PA_DEBUG_MODE = 'Y' THEN
4000: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,5);
4001: END IF;
4002: x_return_status := FND_API.G_RET_STS_ERROR;
3995:
3996: /* Check for Budget Version ID not being NULL. */
3997: IF ( p_budget_version_id IS NULL) THEN
3998: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3999: IF P_PA_DEBUG_MODE = 'Y' THEN
4000: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,5);
4001: END IF;
4002: x_return_status := FND_API.G_RET_STS_ERROR;
4003: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3996: /* Check for Budget Version ID not being NULL. */
3997: IF ( p_budget_version_id IS NULL) THEN
3998: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
3999: IF P_PA_DEBUG_MODE = 'Y' THEN
4000: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,5);
4001: END IF;
4002: x_return_status := FND_API.G_RET_STS_ERROR;
4003: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4004: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4010: (i.e. records that have got updated).*/
4011:
4012: DELETE from pa_fp_ra_map_tmp;
4013:
4014: pa_debug.g_err_stage := 'inserting into map tmp table';
4015: IF P_PA_DEBUG_MODE = 'Y' THEN
4016: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4017: END IF;
4018:
4011:
4012: DELETE from pa_fp_ra_map_tmp;
4013:
4014: pa_debug.g_err_stage := 'inserting into map tmp table';
4015: IF P_PA_DEBUG_MODE = 'Y' THEN
4016: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4017: END IF;
4018:
4019: INSERT into pa_fp_ra_map_tmp(resource_assignment_id)
4012: DELETE from pa_fp_ra_map_tmp;
4013:
4014: pa_debug.g_err_stage := 'inserting into map tmp table';
4015: IF P_PA_DEBUG_MODE = 'Y' THEN
4016: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4017: END IF;
4018:
4019: INSERT into pa_fp_ra_map_tmp(resource_assignment_id)
4020: (SELECT DISTINCT tmp.parent_assignment_id
4019: INSERT into pa_fp_ra_map_tmp(resource_assignment_id)
4020: (SELECT DISTINCT tmp.parent_assignment_id
4021: FROM pa_fp_rollup_tmp tmp);
4022:
4023: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
4024: IF P_PA_DEBUG_MODE = 'Y' THEN
4025: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4026: END IF;
4027:
4020: (SELECT DISTINCT tmp.parent_assignment_id
4021: FROM pa_fp_rollup_tmp tmp);
4022:
4023: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
4024: IF P_PA_DEBUG_MODE = 'Y' THEN
4025: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4026: END IF;
4027:
4028: /* For the first level of records i.e. for the first level parents, if the amount type code
4021: FROM pa_fp_rollup_tmp tmp);
4022:
4023: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records';
4024: IF P_PA_DEBUG_MODE = 'Y' THEN
4025: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4026: END IF;
4027:
4028: /* For the first level of records i.e. for the first level parents, if the amount type code
4029: is QUANTITY, then the uom is that in the table else it is HOURS.
4031: l_first_level is being used for this purpose. */
4032:
4033: l_first_level := 'Y';
4034:
4035: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 1st level';
4036: IF P_PA_DEBUG_MODE = 'Y' THEN
4037: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4038: END IF;
4039: LOOP
4032:
4033: l_first_level := 'Y';
4034:
4035: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 1st level';
4036: IF P_PA_DEBUG_MODE = 'Y' THEN
4037: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4038: END IF;
4039: LOOP
4040: IF l_first_level = 'Y' THEN
4033: l_first_level := 'Y';
4034:
4035: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 1st level';
4036: IF P_PA_DEBUG_MODE = 'Y' THEN
4037: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4038: END IF;
4039: LOOP
4040: IF l_first_level = 'Y' THEN
4041:
4038: END IF;
4039: LOOP
4040: IF l_first_level = 'Y' THEN
4041:
4042: pa_debug.g_err_stage := 'updating period denorm for first level';
4043: IF P_PA_DEBUG_MODE = 'Y' THEN
4044: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4045: END IF;
4046:
4039: LOOP
4040: IF l_first_level = 'Y' THEN
4041:
4042: pa_debug.g_err_stage := 'updating period denorm for first level';
4043: IF P_PA_DEBUG_MODE = 'Y' THEN
4044: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4045: END IF;
4046:
4047: UPDATE PA_PROJ_PERIODS_DENORM ppd1
4040: IF l_first_level = 'Y' THEN
4041:
4042: pa_debug.g_err_stage := 'updating period denorm for first level';
4043: IF P_PA_DEBUG_MODE = 'Y' THEN
4044: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4045: END IF;
4046:
4047: UPDATE PA_PROJ_PERIODS_DENORM ppd1
4048: SET (preceding_periods_amount
4172: (SELECT tmp.resource_assignment_id from pa_fp_ra_map_tmp tmp)
4173: RETURNING parent_assignment_id
4174: BULK COLLECT INTO l_parent_ra_id_tbl;
4175:
4176: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
4177: IF P_PA_DEBUG_MODE = 'Y' THEN
4178: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4179: END IF;
4180:
4173: RETURNING parent_assignment_id
4174: BULK COLLECT INTO l_parent_ra_id_tbl;
4175:
4176: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
4177: IF P_PA_DEBUG_MODE = 'Y' THEN
4178: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4179: END IF;
4180:
4181: l_first_level := 'N';
4174: BULK COLLECT INTO l_parent_ra_id_tbl;
4175:
4176: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
4177: IF P_PA_DEBUG_MODE = 'Y' THEN
4178: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4179: END IF;
4180:
4181: l_first_level := 'N';
4182: l_upd_rec := nvl(l_parent_ra_id_tbl.last,0);
4182: l_upd_rec := nvl(l_parent_ra_id_tbl.last,0);
4183:
4184: ELSE
4185:
4186: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 2nd level';
4187: IF P_PA_DEBUG_MODE = 'Y' THEN
4188: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4189: END IF;
4190:
4183:
4184: ELSE
4185:
4186: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 2nd level';
4187: IF P_PA_DEBUG_MODE = 'Y' THEN
4188: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4189: END IF;
4190:
4191: UPDATE PA_PROJ_PERIODS_DENORM ppd1
4184: ELSE
4185:
4186: pa_debug.g_err_stage := 'Updating the amounts in pa_proj_periods_denorm for 2nd level';
4187: IF P_PA_DEBUG_MODE = 'Y' THEN
4188: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4189: END IF;
4190:
4191: UPDATE PA_PROJ_PERIODS_DENORM ppd1
4192: SET (preceding_periods_amount
4312: (SELECT tmp.resource_assignment_id from pa_fp_ra_map_tmp tmp)
4313: RETURNING parent_assignment_id
4314: BULK COLLECT INTO l_parent_ra_id_tbl;
4315:
4316: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
4317: IF P_PA_DEBUG_MODE = 'Y' THEN
4318: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4319: END IF;
4320:
4313: RETURNING parent_assignment_id
4314: BULK COLLECT INTO l_parent_ra_id_tbl;
4315:
4316: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
4317: IF P_PA_DEBUG_MODE = 'Y' THEN
4318: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4319: END IF;
4320:
4321: l_upd_rec := nvl(l_parent_ra_id_tbl.last,0);
4314: BULK COLLECT INTO l_parent_ra_id_tbl;
4315:
4316: pa_debug.g_err_stage := 'updated ' || sql%rowcount || ' records';
4317: IF P_PA_DEBUG_MODE = 'Y' THEN
4318: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4319: END IF;
4320:
4321: l_upd_rec := nvl(l_parent_ra_id_tbl.last,0);
4322:
4333: INSERT INTO pa_fp_ra_map_tmp
4334: (RESOURCE_ASSIGNMENT_ID)
4335: VALUES (l_parent_ra_id_tbl(i));
4336:
4337: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
4338: IF P_PA_DEBUG_MODE = 'Y' THEN
4339: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4340: END IF;
4341:
4334: (RESOURCE_ASSIGNMENT_ID)
4335: VALUES (l_parent_ra_id_tbl(i));
4336:
4337: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
4338: IF P_PA_DEBUG_MODE = 'Y' THEN
4339: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4340: END IF;
4341:
4342: END IF;
4335: VALUES (l_parent_ra_id_tbl(i));
4336:
4337: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in map tmp';
4338: IF P_PA_DEBUG_MODE = 'Y' THEN
4339: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,pa_debug.g_err_stage,3);
4340: END IF;
4341:
4342: END IF;
4343:
4342: END IF;
4343:
4344: END LOOP;
4345:
4346: pa_debug.reset_err_stack;
4347:
4348: EXCEPTION
4349: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
4350: l_msg_count := FND_MSG_PUB.count_msg;
4360: x_msg_count := l_msg_count;
4361: ELSE
4362: x_msg_count := l_msg_count;
4363: END IF;
4364: pa_debug.reset_err_stack;
4365: RAISE;
4366: WHEN OTHERS THEN
4367: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4368: x_msg_count := 1;
4369: x_msg_data := SQLERRM;
4370: FND_MSG_PUB.add_exc_msg
4371: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
4372: ,p_procedure_name => 'Rollup_Denorm_Amounts');
4373: IF P_PA_DEBUG_MODE = 'Y' THEN
4374: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,SQLERRM,5);
4375: END IF;
4376: pa_debug.reset_err_stack;
4377:
4370: FND_MSG_PUB.add_exc_msg
4371: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
4372: ,p_procedure_name => 'Rollup_Denorm_Amounts');
4373: IF P_PA_DEBUG_MODE = 'Y' THEN
4374: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,SQLERRM,5);
4375: END IF;
4376: pa_debug.reset_err_stack;
4377:
4378: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4372: ,p_procedure_name => 'Rollup_Denorm_Amounts');
4373: IF P_PA_DEBUG_MODE = 'Y' THEN
4374: pa_debug.write('ROLLUP_DENORM_AMOUNTS: ' || l_module_name,SQLERRM,5);
4375: END IF;
4376: pa_debug.reset_err_stack;
4377:
4378: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4379: END ROLLUP_DENORM_AMOUNTS;
4380:
4454:
4455: BEGIN
4456:
4457: -- Set the error stack.
4458: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Delete_Element');
4459:
4460: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
4461: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4462: l_debug_mode := NVL(l_debug_mode, 'Y');
4457: -- Set the error stack.
4458: pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Delete_Element');
4459:
4460: -- Get the Debug mode into local variable and set it to 'Y'if its NULL
4461: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4462: l_debug_mode := NVL(l_debug_mode, 'Y');
4463:
4464: -- Initialize the return status to success
4465: x_return_status := FND_API.G_RET_STS_SUCCESS;
4463:
4464: -- Initialize the return status to success
4465: x_return_status := FND_API.G_RET_STS_SUCCESS;
4466:
4467: IF P_PA_DEBUG_MODE = 'Y' THEN
4468: pa_debug.set_process('DELETE_ELEMENT: ' || 'PLSQL','LOG',l_debug_mode);
4469: END IF;
4470:
4471: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Delete_Element ';
4464: -- Initialize the return status to success
4465: x_return_status := FND_API.G_RET_STS_SUCCESS;
4466:
4467: IF P_PA_DEBUG_MODE = 'Y' THEN
4468: pa_debug.set_process('DELETE_ELEMENT: ' || 'PLSQL','LOG',l_debug_mode);
4469: END IF;
4470:
4471: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Delete_Element ';
4472: IF P_PA_DEBUG_MODE = 'Y' THEN
4467: IF P_PA_DEBUG_MODE = 'Y' THEN
4468: pa_debug.set_process('DELETE_ELEMENT: ' || 'PLSQL','LOG',l_debug_mode);
4469: END IF;
4470:
4471: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Delete_Element ';
4472: IF P_PA_DEBUG_MODE = 'Y' THEN
4473: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4474: END IF;
4475:
4468: pa_debug.set_process('DELETE_ELEMENT: ' || 'PLSQL','LOG',l_debug_mode);
4469: END IF;
4470:
4471: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Delete_Element ';
4472: IF P_PA_DEBUG_MODE = 'Y' THEN
4473: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4474: END IF;
4475:
4476:
4469: END IF;
4470:
4471: pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Delete_Element ';
4472: IF P_PA_DEBUG_MODE = 'Y' THEN
4473: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4474: END IF;
4475:
4476:
4477: /* Check for Budget Version ID not being NULL. */
4475:
4476:
4477: /* Check for Budget Version ID not being NULL. */
4478: IF ( p_budget_version_id IS NULL) THEN
4479: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
4480: IF P_PA_DEBUG_MODE = 'Y' THEN
4481: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4482: END IF;
4483: x_return_status := FND_API.G_RET_STS_ERROR;
4476:
4477: /* Check for Budget Version ID not being NULL. */
4478: IF ( p_budget_version_id IS NULL) THEN
4479: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
4480: IF P_PA_DEBUG_MODE = 'Y' THEN
4481: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4482: END IF;
4483: x_return_status := FND_API.G_RET_STS_ERROR;
4484: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4477: /* Check for Budget Version ID not being NULL. */
4478: IF ( p_budget_version_id IS NULL) THEN
4479: pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
4480: IF P_PA_DEBUG_MODE = 'Y' THEN
4481: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4482: END IF;
4483: x_return_status := FND_API.G_RET_STS_ERROR;
4484: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4485: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4487: END IF;
4488:
4489: /* Check for Resource Assignment ID not being NULL. */
4490: IF ( p_resource_assignment_id IS NULL) THEN
4491: pa_debug.g_err_stage := 'Err- Resource Assignment ID cannot be NULL.';
4492: IF P_PA_DEBUG_MODE = 'Y' THEN
4493: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4494: END IF;
4495: x_return_status := FND_API.G_RET_STS_ERROR;
4488:
4489: /* Check for Resource Assignment ID not being NULL. */
4490: IF ( p_resource_assignment_id IS NULL) THEN
4491: pa_debug.g_err_stage := 'Err- Resource Assignment ID cannot be NULL.';
4492: IF P_PA_DEBUG_MODE = 'Y' THEN
4493: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4494: END IF;
4495: x_return_status := FND_API.G_RET_STS_ERROR;
4496: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4489: /* Check for Resource Assignment ID not being NULL. */
4490: IF ( p_resource_assignment_id IS NULL) THEN
4491: pa_debug.g_err_stage := 'Err- Resource Assignment ID cannot be NULL.';
4492: IF P_PA_DEBUG_MODE = 'Y' THEN
4493: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4494: END IF;
4495: x_return_status := FND_API.G_RET_STS_ERROR;
4496: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4497: p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4499: END IF;
4500:
4501: /* Populate the local variables. */
4502:
4503: pa_debug.g_err_stage := 'calling populate_local_vars';
4504: IF P_PA_DEBUG_MODE = 'Y' THEN
4505: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4506: END IF;
4507:
4500:
4501: /* Populate the local variables. */
4502:
4503: pa_debug.g_err_stage := 'calling populate_local_vars';
4504: IF P_PA_DEBUG_MODE = 'Y' THEN
4505: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4506: END IF;
4507:
4508: populate_local_vars(p_budget_version_id => p_budget_version_id,
4501: /* Populate the local variables. */
4502:
4503: pa_debug.g_err_stage := 'calling populate_local_vars';
4504: IF P_PA_DEBUG_MODE = 'Y' THEN
4505: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4506: END IF;
4507:
4508: populate_local_vars(p_budget_version_id => p_budget_version_id,
4509: x_project_id => l_project_id,
4523: where project_id = l_project_id;
4524:
4525: IF (p_txn_currency_code IS NULL) THEN
4526:
4527: pa_debug.g_err_stage := 'Transaction Currency Code is NULL';
4528: IF P_PA_DEBUG_MODE = 'Y' THEN
4529: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4530: END IF;
4531:
4524:
4525: IF (p_txn_currency_code IS NULL) THEN
4526:
4527: pa_debug.g_err_stage := 'Transaction Currency Code is NULL';
4528: IF P_PA_DEBUG_MODE = 'Y' THEN
4529: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4530: END IF;
4531:
4532: DELETE FROM pa_fp_rollup_tmp; /* M20-AUG: delete from rollup_tmp should be unconditional */
4525: IF (p_txn_currency_code IS NULL) THEN
4526:
4527: pa_debug.g_err_stage := 'Transaction Currency Code is NULL';
4528: IF P_PA_DEBUG_MODE = 'Y' THEN
4529: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4530: END IF;
4531:
4532: DELETE FROM pa_fp_rollup_tmp; /* M20-AUG: delete from rollup_tmp should be unconditional */
4533:
4538: /* For the Parent Assignment ID, populating the Rollup Table PA_FP_ROLLUP_TMP, with
4539: the old and new amounts. Call Rollup_Resource_Assignment_Amounts to roll up the
4540: Resource Assignments data and Rollup_Denorm_Amounts to roll up the Denorm data.*/
4541:
4542: pa_debug.g_err_stage := 'Insert records into Rollup Temp Table with the amounts';
4543: IF P_PA_DEBUG_MODE = 'Y' THEN
4544: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4545: END IF;
4546:
4539: the old and new amounts. Call Rollup_Resource_Assignment_Amounts to roll up the
4540: Resource Assignments data and Rollup_Denorm_Amounts to roll up the Denorm data.*/
4541:
4542: pa_debug.g_err_stage := 'Insert records into Rollup Temp Table with the amounts';
4543: IF P_PA_DEBUG_MODE = 'Y' THEN
4544: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4545: END IF;
4546:
4547: /* M20-AUG: we have to insert all the records for the deleted resource assignment */
4540: Resource Assignments data and Rollup_Denorm_Amounts to roll up the Denorm data.*/
4541:
4542: pa_debug.g_err_stage := 'Insert records into Rollup Temp Table with the amounts';
4543: IF P_PA_DEBUG_MODE = 'Y' THEN
4544: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4545: END IF;
4546:
4547: /* M20-AUG: we have to insert all the records for the deleted resource assignment */
4548:
4605: budget_line_id needs to be included
4606: GROUP BY bl.resource_assignment_id
4607: ,pra.parent_assignment_id */ ;
4608:
4609: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in rollup tmp';
4610: IF P_PA_DEBUG_MODE = 'Y' THEN
4611: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4612: END IF;
4613:
4606: GROUP BY bl.resource_assignment_id
4607: ,pra.parent_assignment_id */ ;
4608:
4609: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in rollup tmp';
4610: IF P_PA_DEBUG_MODE = 'Y' THEN
4611: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4612: END IF;
4613:
4614: ELSE
4607: ,pra.parent_assignment_id */ ;
4608:
4609: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in rollup tmp';
4610: IF P_PA_DEBUG_MODE = 'Y' THEN
4611: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4612: END IF;
4613:
4614: ELSE
4615:
4612: END IF;
4613:
4614: ELSE
4615:
4616: pa_debug.g_err_stage := 'Transaction Currency Code is Not Null';
4617: IF P_PA_DEBUG_MODE = 'Y' THEN
4618: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4619: END IF;
4620:
4613:
4614: ELSE
4615:
4616: pa_debug.g_err_stage := 'Transaction Currency Code is Not Null';
4617: IF P_PA_DEBUG_MODE = 'Y' THEN
4618: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4619: END IF;
4620:
4621: /* Since the details of the records being deleted are required for rolling
4614: ELSE
4615:
4616: pa_debug.g_err_stage := 'Transaction Currency Code is Not Null';
4617: IF P_PA_DEBUG_MODE = 'Y' THEN
4618: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4619: END IF;
4620:
4621: /* Since the details of the records being deleted are required for rolling
4622: up data, we need to get the amounts from the Budget Lines table for the
4625: /* First, delete the records from pa_fp_rollup_tmp if any. */
4626:
4627: DELETE FROM pa_fp_rollup_tmp;
4628:
4629: pa_debug.g_err_stage := 'Insert Records into Rollup Temp Table for txn currency';
4630: IF P_PA_DEBUG_MODE = 'Y' THEN
4631: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4632: END IF;
4633:
4626:
4627: DELETE FROM pa_fp_rollup_tmp;
4628:
4629: pa_debug.g_err_stage := 'Insert Records into Rollup Temp Table for txn currency';
4630: IF P_PA_DEBUG_MODE = 'Y' THEN
4631: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4632: END IF;
4633:
4634: INSERT INTO pa_fp_rollup_tmp
4627: DELETE FROM pa_fp_rollup_tmp;
4628:
4629: pa_debug.g_err_stage := 'Insert Records into Rollup Temp Table for txn currency';
4630: IF P_PA_DEBUG_MODE = 'Y' THEN
4631: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4632: END IF;
4633:
4634: INSERT INTO pa_fp_rollup_tmp
4635: ( budget_line_id /* FPB2 */
4681: /* FPB2: Removed grouped by and null handling in select columns as
4682: budget_line_id needs to be included
4683: GROUP BY resource_assignment_id, txn_currency_code */ ;
4684:
4685: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records into rollup tmp';
4686: IF P_PA_DEBUG_MODE = 'Y' THEN
4687: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4688: END IF;
4689:
4682: budget_line_id needs to be included
4683: GROUP BY resource_assignment_id, txn_currency_code */ ;
4684:
4685: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records into rollup tmp';
4686: IF P_PA_DEBUG_MODE = 'Y' THEN
4687: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4688: END IF;
4689:
4690: END IF;
4683: GROUP BY resource_assignment_id, txn_currency_code */ ;
4684:
4685: pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records into rollup tmp';
4686: IF P_PA_DEBUG_MODE = 'Y' THEN
4687: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4688: END IF;
4689:
4690: END IF;
4691:
4750: and a.txn_currency_code = nvl(p_txn_currency_code,a.txn_currency_code);
4751:
4752: IF l_resource_assignment_id_tbl.count > 0 THEN
4753:
4754: IF P_PA_DEBUG_MODE = 'Y' THEN
4755: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,'There are budget lines to be deleted... ',3);
4756: END IF;
4757:
4758: pa_planning_transaction_utils.call_update_rep_lines_api
4751:
4752: IF l_resource_assignment_id_tbl.count > 0 THEN
4753:
4754: IF P_PA_DEBUG_MODE = 'Y' THEN
4755: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,'There are budget lines to be deleted... ',3);
4756: END IF;
4757:
4758: pa_planning_transaction_utils.call_update_rep_lines_api
4759: (
4788: ,x_msg_count => x_msg_count
4789: ,x_msg_data => x_msg_data);
4790:
4791: IF x_return_Status <> FND_API.G_RET_STS_SUCCESS THEN
4792: IF P_PA_DEBUG_MODE = 'Y' THEN
4793: pa_debug.g_err_stage := 'pa_planning_transaction_utils.call_update_rep_lines_api errored .... ' || x_msg_data;
4794: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4795: END IF;
4796: RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4789: ,x_msg_data => x_msg_data);
4790:
4791: IF x_return_Status <> FND_API.G_RET_STS_SUCCESS THEN
4792: IF P_PA_DEBUG_MODE = 'Y' THEN
4793: pa_debug.g_err_stage := 'pa_planning_transaction_utils.call_update_rep_lines_api errored .... ' || x_msg_data;
4794: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4795: END IF;
4796: RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4797: END IF;
4790:
4791: IF x_return_Status <> FND_API.G_RET_STS_SUCCESS THEN
4792: IF P_PA_DEBUG_MODE = 'Y' THEN
4793: pa_debug.g_err_stage := 'pa_planning_transaction_utils.call_update_rep_lines_api errored .... ' || x_msg_data;
4794: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4795: END IF;
4796: RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4797: END IF;
4798:
4807: WHERE resource_assignment_id = p_resource_assignment_id;
4808:
4809: l_records_deleted := sql%rowcount;
4810:
4811: pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4812: IF P_PA_DEBUG_MODE = 'Y' THEN
4813: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4814: END IF;
4815:
4808:
4809: l_records_deleted := sql%rowcount;
4810:
4811: pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4812: IF P_PA_DEBUG_MODE = 'Y' THEN
4813: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4814: END IF;
4815:
4816: -- Bug Fix: 4569365. Removed MRC code.
4809: l_records_deleted := sql%rowcount;
4810:
4811: pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4812: IF P_PA_DEBUG_MODE = 'Y' THEN
4813: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4814: END IF;
4815:
4816: -- Bug Fix: 4569365. Removed MRC code.
4817: /*
4849: INTO l_parent_assignment_id
4850: ,l_task_id
4851: ,l_resource_list_member_id;
4852:
4853: pa_debug.g_err_stage := 'Calling Rollup_budget_versions';
4854: IF P_PA_DEBUG_MODE = 'Y' THEN
4855: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4856: END IF;
4857:
4850: ,l_task_id
4851: ,l_resource_list_member_id;
4852:
4853: pa_debug.g_err_stage := 'Calling Rollup_budget_versions';
4854: IF P_PA_DEBUG_MODE = 'Y' THEN
4855: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4856: END IF;
4857:
4858: ROLLUP_BUDGET_VERSION(p_budget_version_id => p_budget_version_id
4851: ,l_resource_list_member_id;
4852:
4853: pa_debug.g_err_stage := 'Calling Rollup_budget_versions';
4854: IF P_PA_DEBUG_MODE = 'Y' THEN
4855: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4856: END IF;
4857:
4858: ROLLUP_BUDGET_VERSION(p_budget_version_id => p_budget_version_id
4859: ,p_entire_version => 'N'
4863:
4864: DELETE FROM pa_proj_periods_denorm
4865: WHERE resource_assignment_id = p_resource_assignment_id;
4866:
4867: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4868: IF P_PA_DEBUG_MODE = 'Y' THEN
4869: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4870: END IF;
4871:
4864: DELETE FROM pa_proj_periods_denorm
4865: WHERE resource_assignment_id = p_resource_assignment_id;
4866:
4867: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4868: IF P_PA_DEBUG_MODE = 'Y' THEN
4869: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4870: END IF;
4871:
4872: ELSE
4865: WHERE resource_assignment_id = p_resource_assignment_id;
4866:
4867: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4868: IF P_PA_DEBUG_MODE = 'Y' THEN
4869: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4870: END IF;
4871:
4872: ELSE
4873:
4879: WHERE resource_assignment_id = p_resource_assignment_id
4880: AND txn_currency_code = p_txn_currency_code;
4881:
4882: l_records_deleted := sql%rowcount;
4883: pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4884: IF P_PA_DEBUG_MODE = 'Y' THEN
4885: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4886: END IF;
4887:
4880: AND txn_currency_code = p_txn_currency_code;
4881:
4882: l_records_deleted := sql%rowcount;
4883: pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4884: IF P_PA_DEBUG_MODE = 'Y' THEN
4885: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4886: END IF;
4887:
4888: -- Bug Fix: 4569365. Removed MRC code.
4881:
4882: l_records_deleted := sql%rowcount;
4883: pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4884: IF P_PA_DEBUG_MODE = 'Y' THEN
4885: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4886: END IF;
4887:
4888: -- Bug Fix: 4569365. Removed MRC code.
4889: /*
4918:
4919: /* Call the Rollup API so that the parents and the subsequent parents
4920: are rolled up. */
4921:
4922: pa_debug.g_err_stage := 'Calling Rollup_Budget_Version';
4923: IF P_PA_DEBUG_MODE = 'Y' THEN
4924: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4925: END IF;
4926:
4919: /* Call the Rollup API so that the parents and the subsequent parents
4920: are rolled up. */
4921:
4922: pa_debug.g_err_stage := 'Calling Rollup_Budget_Version';
4923: IF P_PA_DEBUG_MODE = 'Y' THEN
4924: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4925: END IF;
4926:
4927: ROLLUP_BUDGET_VERSION(p_budget_version_id => p_budget_version_id
4920: are rolled up. */
4921:
4922: pa_debug.g_err_stage := 'Calling Rollup_Budget_Version';
4923: IF P_PA_DEBUG_MODE = 'Y' THEN
4924: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4925: END IF;
4926:
4927: ROLLUP_BUDGET_VERSION(p_budget_version_id => p_budget_version_id
4928: ,p_entire_version => 'N'
4936: WHERE resource_assignment_id = p_resource_assignment_id
4937: AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION
4938: AND currency_code = p_txn_currency_code;
4939:
4940: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4941:
4942: IF P_PA_DEBUG_MODE = 'Y' THEN
4943: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4944: END IF;
4938: AND currency_code = p_txn_currency_code;
4939:
4940: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4941:
4942: IF P_PA_DEBUG_MODE = 'Y' THEN
4943: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4944: END IF;
4945:
4946: /* Check if there are any more Budget Lines existing for this resource. */
4939:
4940: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4941:
4942: IF P_PA_DEBUG_MODE = 'Y' THEN
4943: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4944: END IF;
4945:
4946: /* Check if there are any more Budget Lines existing for this resource. */
4947:
4967:
4968: /* If Budget Lines exist for the resource_assignment_id, Setting the Parent
4969: Assignment ID to NULL as no more processing is required for this case. */
4970:
4971: pa_debug.g_err_stage := 'Budget Lines exist for the Parent Assignment ID';
4972: IF P_PA_DEBUG_MODE = 'Y' THEN
4973: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4974: END IF;
4975:
4968: /* If Budget Lines exist for the resource_assignment_id, Setting the Parent
4969: Assignment ID to NULL as no more processing is required for this case. */
4970:
4971: pa_debug.g_err_stage := 'Budget Lines exist for the Parent Assignment ID';
4972: IF P_PA_DEBUG_MODE = 'Y' THEN
4973: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4974: END IF;
4975:
4976: l_parent_assignment_id := NULL;
4969: Assignment ID to NULL as no more processing is required for this case. */
4970:
4971: pa_debug.g_err_stage := 'Budget Lines exist for the Parent Assignment ID';
4972: IF P_PA_DEBUG_MODE = 'Y' THEN
4973: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4974: END IF;
4975:
4976: l_parent_assignment_id := NULL;
4977:
4979:
4980: /* If there are no budget lines then this assignment id eligible for deletion.
4981: Get the parent assignment id in l_parent_assignment_id */
4982:
4983: pa_debug.g_err_stage := 'No Budget Lines for Parent Assignment ID';
4984: IF P_PA_DEBUG_MODE = 'Y' THEN
4985: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4986: END IF;
4987:
4980: /* If there are no budget lines then this assignment id eligible for deletion.
4981: Get the parent assignment id in l_parent_assignment_id */
4982:
4983: pa_debug.g_err_stage := 'No Budget Lines for Parent Assignment ID';
4984: IF P_PA_DEBUG_MODE = 'Y' THEN
4985: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4986: END IF;
4987:
4988: DELETE FROM pa_resource_assignments
4981: Get the parent assignment id in l_parent_assignment_id */
4982:
4983: pa_debug.g_err_stage := 'No Budget Lines for Parent Assignment ID';
4984: IF P_PA_DEBUG_MODE = 'Y' THEN
4985: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4986: END IF;
4987:
4988: DELETE FROM pa_resource_assignments
4989: WHERE resource_assignment_id = p_resource_assignment_id
4991: INTO l_parent_assignment_id
4992: ,l_task_id
4993: ,l_resource_list_member_id;
4994:
4995: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from res assignment';
4996: IF P_PA_DEBUG_MODE = 'Y' THEN
4997: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4998: END IF;
4999:
4992: ,l_task_id
4993: ,l_resource_list_member_id;
4994:
4995: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from res assignment';
4996: IF P_PA_DEBUG_MODE = 'Y' THEN
4997: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4998: END IF;
4999:
5000: DELETE FROM pa_proj_periods_denorm
4993: ,l_resource_list_member_id;
4994:
4995: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from res assignment';
4996: IF P_PA_DEBUG_MODE = 'Y' THEN
4997: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4998: END IF;
4999:
5000: DELETE FROM pa_proj_periods_denorm
5001: WHERE resource_assignment_id = p_resource_assignment_id;
4999:
5000: DELETE FROM pa_proj_periods_denorm
5001: WHERE resource_assignment_id = p_resource_assignment_id;
5002:
5003: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
5004: IF P_PA_DEBUG_MODE = 'Y' THEN
5005: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5006: END IF;
5007:
5000: DELETE FROM pa_proj_periods_denorm
5001: WHERE resource_assignment_id = p_resource_assignment_id;
5002:
5003: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
5004: IF P_PA_DEBUG_MODE = 'Y' THEN
5005: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5006: END IF;
5007:
5008: END IF;
5001: WHERE resource_assignment_id = p_resource_assignment_id;
5002:
5003: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
5004: IF P_PA_DEBUG_MODE = 'Y' THEN
5005: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5006: END IF;
5007:
5008: END IF;
5009:
5014: */
5015: LOOP
5016: IF (l_parent_assignment_id IS NOT NULL) THEN
5017:
5018: pa_debug.g_err_stage := 'Parent Assignment ID is NOT NULL';
5019: IF P_PA_DEBUG_MODE = 'Y' THEN
5020: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5021: END IF;
5022:
5015: LOOP
5016: IF (l_parent_assignment_id IS NOT NULL) THEN
5017:
5018: pa_debug.g_err_stage := 'Parent Assignment ID is NOT NULL';
5019: IF P_PA_DEBUG_MODE = 'Y' THEN
5020: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5021: END IF;
5022:
5023: l_child_res_count := 0;
5016: IF (l_parent_assignment_id IS NOT NULL) THEN
5017:
5018: pa_debug.g_err_stage := 'Parent Assignment ID is NOT NULL';
5019: IF P_PA_DEBUG_MODE = 'Y' THEN
5020: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5021: END IF;
5022:
5023: l_child_res_count := 0;
5024:
5033: resource_assignment_id record from pa_resource_assignments and pa
5034: pa_proj_periods_denorm. Get the parent of this resource assignment id.
5035: Continue this loop until some parent is found with child records. */
5036:
5037: pa_debug.g_err_stage := 'no child found. deleting the parent';
5038: IF P_PA_DEBUG_MODE = 'Y' THEN
5039: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5040: END IF;
5041:
5034: pa_proj_periods_denorm. Get the parent of this resource assignment id.
5035: Continue this loop until some parent is found with child records. */
5036:
5037: pa_debug.g_err_stage := 'no child found. deleting the parent';
5038: IF P_PA_DEBUG_MODE = 'Y' THEN
5039: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5040: END IF;
5041:
5042: /* the delete below is moved before the next delete as after the next delte
5035: Continue this loop until some parent is found with child records. */
5036:
5037: pa_debug.g_err_stage := 'no child found. deleting the parent';
5038: IF P_PA_DEBUG_MODE = 'Y' THEN
5039: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5040: END IF;
5041:
5042: /* the delete below is moved before the next delete as after the next delte
5043: l_parent_assignment_id value will change
5051: DELETE FROM pa_resource_assignments
5052: WHERE resource_assignment_id = l_parent_assignment_id
5053: RETURNING parent_assignment_id INTO l_parent_assignment_id;
5054:
5055: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' from denorm';
5056: IF P_PA_DEBUG_MODE = 'Y' THEN
5057: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5058: END IF;
5059:
5052: WHERE resource_assignment_id = l_parent_assignment_id
5053: RETURNING parent_assignment_id INTO l_parent_assignment_id;
5054:
5055: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' from denorm';
5056: IF P_PA_DEBUG_MODE = 'Y' THEN
5057: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5058: END IF;
5059:
5060: ELSE /* Child records are found */
5053: RETURNING parent_assignment_id INTO l_parent_assignment_id;
5054:
5055: pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' from denorm';
5056: IF P_PA_DEBUG_MODE = 'Y' THEN
5057: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5058: END IF;
5059:
5060: ELSE /* Child records are found */
5061:
5060: ELSE /* Child records are found */
5061:
5062: /* Resource Assignments need to be rolled up from this point.
5063: So, exit the loop. */
5064: pa_debug.g_err_stage := 'some child found. no action';
5065: IF P_PA_DEBUG_MODE = 'Y' THEN
5066: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5067: END IF;
5068:
5061:
5062: /* Resource Assignments need to be rolled up from this point.
5063: So, exit the loop. */
5064: pa_debug.g_err_stage := 'some child found. no action';
5065: IF P_PA_DEBUG_MODE = 'Y' THEN
5066: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5067: END IF;
5068:
5069: EXIT;
5062: /* Resource Assignments need to be rolled up from this point.
5063: So, exit the loop. */
5064: pa_debug.g_err_stage := 'some child found. no action';
5065: IF P_PA_DEBUG_MODE = 'Y' THEN
5066: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5067: END IF;
5068:
5069: EXIT;
5070: END IF;
5071: ELSE
5072: /* bug 2649117 added else and the exit condition. So that when
5073: no parent is found it exits out of the loop
5074: */
5075: pa_debug.g_err_stage := 'parent assignment id is NULL';
5076: IF P_PA_DEBUG_MODE = 'Y' THEN
5077: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5078: END IF;
5079:
5072: /* bug 2649117 added else and the exit condition. So that when
5073: no parent is found it exits out of the loop
5074: */
5075: pa_debug.g_err_stage := 'parent assignment id is NULL';
5076: IF P_PA_DEBUG_MODE = 'Y' THEN
5077: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5078: END IF;
5079:
5080: EXIT;
5073: no parent is found it exits out of the loop
5074: */
5075: pa_debug.g_err_stage := 'parent assignment id is NULL';
5076: IF P_PA_DEBUG_MODE = 'Y' THEN
5077: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5078: END IF;
5079:
5080: EXIT;
5081: END IF;
5080: EXIT;
5081: END IF;
5082: END LOOP;
5083:
5084: pa_debug.reset_err_stack;
5085:
5086: EXCEPTION
5087: WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
5088: l_msg_count := FND_MSG_PUB.count_msg;
5098: x_msg_count := l_msg_count;
5099: ELSE
5100: x_msg_count := l_msg_count;
5101: END IF;
5102: pa_debug.reset_err_stack;
5103: RAISE;
5104: WHEN OTHERS THEN
5105: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5106: x_msg_count := 1;
5107: x_msg_data := SQLERRM;
5108: FND_MSG_PUB.add_exc_msg
5109: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
5110: ,p_procedure_name => 'Delete_Element');
5111: IF P_PA_DEBUG_MODE = 'Y' THEN
5112: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,SQLERRM,5);
5113: END IF;
5114: pa_debug.reset_err_stack;
5115:
5108: FND_MSG_PUB.add_exc_msg
5109: ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
5110: ,p_procedure_name => 'Delete_Element');
5111: IF P_PA_DEBUG_MODE = 'Y' THEN
5112: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,SQLERRM,5);
5113: END IF;
5114: pa_debug.reset_err_stack;
5115:
5116: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5110: ,p_procedure_name => 'Delete_Element');
5111: IF P_PA_DEBUG_MODE = 'Y' THEN
5112: pa_debug.write('DELETE_ELEMENT: ' || l_module_name,SQLERRM,5);
5113: END IF;
5114: pa_debug.reset_err_stack;
5115:
5116: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5117: END DELETE_ELEMENT;
5118: