1 PACKAGE BODY PA_FP_ROLLUP_PKG as
2 /* $Header: PAFPRLPB.pls 120.3.12020000.3 2013/06/11 09:26:26 bpottipa ship $ */
3
4 l_module_name VARCHAR2(100) := 'pa.plsql.pa_fp_rollup_pkg';
5 g_plsql_max_array_size NUMBER := 200;
6 g_first_ra_id pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE;
7
8 -- Bug Fix: 4569365. Removed MRC code.
9 -- g_mrc_exception EXCEPTION; /* FPB2: MRC */
10
11 /* when called in context of entire version g_first_ra_id will be set to zero else it will be set to
12 the current value of resource assigmnet id in the pa_resource_assignment_s sequence to track as
13 which resource assignments are inserted in this run */
14
15 /*=================================================================================================
16 POPULATE_LOCAL_VARS: This is a common api which takes care of populating the local variables
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
26 ,x_uncat_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
27 ,x_uncat_rlm_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
28 ,x_rl_group_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
29 ,x_planning_level OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
30 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
31 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
32 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
33
34 l_uncat_res_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
35 l_uncat_res_list_mem_id pa_resource_list_members.RESOURCE_LIST_MEMBER_ID%TYPE;
36 l_uncat_res_id pa_resource_list_members.RESOURCE_ID%TYPE;
37 l_uncat_track_as_labor_flg pa_resource_assignments.TRACK_AS_LABOR_FLAG%TYPE;
38 l_err_code NUMBER;
39 l_err_stage VARCHAR2(100);
40 l_err_stack VARCHAR2(1000);
41
42 l_msg_count NUMBER := 0;
43 l_data VARCHAR2(2000);
44 l_msg_data VARCHAR2(2000);
45 l_msg_index_out NUMBER;
46 l_return_status VARCHAR2(2000);
47 l_debug_mode VARCHAR2(30);
48
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);
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 ';
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
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');
80 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
81 END IF;
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
91 SELECT pbv.resource_list_id
92 ,prl.uncategorized_flag
93 ,prl.group_resource_type_id
94 ,pbv.project_id
95 INTO x_resource_list_id
96 ,x_uncat_flag
97 ,x_rl_group_type_id
98 ,x_project_id
99 FROM pa_budget_versions pbv
100 ,pa_resource_lists prl
101 WHERE budget_version_id = p_budget_version_id
102 AND pbv.resource_list_id = prl.resource_list_id; /* M21-AUG: Join was missing */
103
104
105 /* Only if the Resource List is uncategorized, set the x_uncat_rlm_id
106 as the uncat resource list member id else it will remain as default 0. */
107
108 IF (x_uncat_flag = 'Y') THEN
109
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
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
121 ,p_resource_id => l_uncat_res_id
122 ,p_track_as_labor_flag => l_uncat_track_as_labor_flg
123 ,p_err_code => l_err_code
124 ,p_err_stage => l_err_stage
125 ,p_err_stack => l_err_stack);
126
127 x_uncat_rlm_id := l_uncat_res_list_mem_id;
128
129 ELSE
130
131 x_uncat_rlm_id := 0;
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
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
143 SELECT decode(fin_plan_preference_code, PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,cost_fin_plan_level_code,
144 PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,revenue_fin_plan_level_code,
145 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SAME,all_fin_plan_level_code) planning_level
146 INTO x_planning_level
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;
156 IF l_msg_count = 1 THEN
157 PA_INTERFACE_UTILS_PUB.get_messages
158 (p_encoded => FND_API.G_TRUE,
159 p_msg_index => 1,
160 p_msg_count => l_msg_count,
161 p_msg_data => l_msg_data,
162 p_data => l_data,
163 p_msg_index_out => l_msg_index_out);
164 x_msg_data := l_data;
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
183 raise FND_API.G_EXC_UNEXPECTED_ERROR;
184
185 END POPULATE_LOCAL_VARS;
186
187
188 /*=================================================================================================
189 INSERT_PARENT_REC_TMP: This is a common api which is used to insert records into pa_fp_ra_map_tmp
190 based on the Level of the records being inserted (i.e Resource Group level, Task level and Parent
191 Task level) for rollup of amounts into the Denorm table. This procedure is called from
192 Refresh_Period_Denorm and Insert_Parent_Rec_Tmp.
193 =================================================================================================*/
194 PROCEDURE INSERT_PARENT_REC_TMP(p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
195 ,PX_INSERTING_RES_GROUP_LEVEL IN OUT NOCOPY boolean --File.Sql.39 bug 4440895
196 ,PX_INSERTING_TASK_LEVEL IN OUT NOCOPY boolean --File.Sql.39 bug 4440895
197 ,PX_INSERTING_PARENT_TASK_LEVEL IN OUT NOCOPY boolean --File.Sql.39 bug 4440895
198 ,p_curr_rollup_level IN NUMBER) IS
199
200 l_debug_mode VARCHAR2(30);
201 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
202 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
203 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
204 l_uncat_rlm_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
205 l_project_id pa_projects.project_id%TYPE;
206 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
207
208 l_msg_count NUMBER := 0;
209 l_msg_data VARCHAR2(2000);
210 l_return_status VARCHAR2(2000);
211
212 BEGIN
213
214 /* #2723515: Added call to Populate_Local_Vars to get the uncat rlm id
215 which will be used down the line to retrieve task level records. */
216
217 populate_local_vars(p_budget_version_id => p_budget_version_id,
218 x_project_id => l_project_id,
219 x_resource_list_id => l_resource_list_id,
220 x_uncat_flag => l_uncat_flag,
221 x_uncat_rlm_id => l_uncat_rlm_id,
222 x_rl_group_type_id => l_rl_group_type_id,
223 x_planning_level => l_planning_level,
224 x_return_status => l_return_status,
225 x_msg_count => l_msg_count,
226 x_msg_data => l_msg_data);
227
228 -- The variable isn't set when the refresh _period_denorm api is called
229 -- from refresh_period_profile api.
230
231 IF g_first_ra_id IS NULL THEN
232 g_first_ra_id := 0;
233 END IF;
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
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
252 /* we need to insert only last that is resource level records in tmp table.
253 This is required because in case resource list is grouped users can enter amounts at
254 resource group level.
255 */
256 INSERT INTO PA_FP_RA_MAP_TMP
257 (RESOURCE_ASSIGNMENT_ID
258 ,PARENT_ASSIGNMENT_ID
259 ,UNIT_OF_MEASURE)
260 SELECT resource_assignment_id
261 ,parent_assignment_id
262 ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
263 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
264 FROM pa_resource_assignments pra, pa_resource_list_members prlm
265 WHERE pra.budget_version_id = p_budget_version_id
266 AND pra.resource_list_member_id = prlm.resource_list_member_id
267 AND prlm.parent_member_id IS NOT NULL
268 AND pra.parent_assignment_id > g_first_ra_id;
269
270 PX_INSERTING_RES_GROUP_LEVEL := false;
271 PX_INSERTING_TASK_LEVEL := true;
272
273
274 ELSIF PX_INSERTING_TASK_LEVEL THEN
275 /* When inserting task level records we need to select those records from resource assignments
276 for which parent member id is null. These could be either resource group level records or
277 resource level records depending upon whether resource list is grouped or not.
278 */
279 INSERT INTO PA_FP_RA_MAP_TMP
280 (RESOURCE_ASSIGNMENT_ID
281 ,PARENT_ASSIGNMENT_ID
282 ,UNIT_OF_MEASURE)
283 SELECT resource_assignment_id
284 ,parent_assignment_id
285 ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
286 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
287 FROM pa_resource_assignments pra, pa_resource_list_members prlm
288 WHERE pra.budget_version_id = p_budget_version_id
289 AND pra.resource_list_member_id = prlm.resource_list_member_id
290 AND prlm.parent_member_id IS NULL
291 AND pra.parent_assignment_id > g_first_ra_id;
292
293 PX_INSERTING_PARENT_TASK_LEVEL := true;
294 PX_INSERTING_TASK_LEVEL := false;
295 PX_INSERTING_RES_GROUP_LEVEL := false;
296
297 ELSIF PX_INSERTING_PARENT_TASK_LEVEL THEN
298 /* in this case we should start with last level in wbs and then go up the ladder. This is to avoid
299 selecting the same parent twice due to differnece in wbs across various branches
300 */
301 INSERT INTO pa_fp_ra_map_tmp
302 (RESOURCE_ASSIGNMENT_ID
303 ,PARENT_ASSIGNMENT_ID
304 ,UNIT_OF_MEASURE)
305 SELECT pra.resource_assignment_id
306 ,pra.parent_assignment_id
307 ,decode(resource_assignment_type,PA_FP_CONSTANTS_PKG.G_ROLLED_UP,
308 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,pra.unit_of_measure) unit_of_measure
309 FROM pa_resource_assignments pra, pa_tasks pt
310 WHERE pra.budget_version_id = p_budget_version_id
311 AND pra.task_id = pt.task_id
312 AND pra.resource_list_member_id in (0,l_uncat_rlm_id) -- Added for bug #2723515
313 and pt.wbs_level = p_curr_rollup_level
314 AND pra.parent_assignment_id > g_first_ra_id;
315
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
325 EXCEPTION
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;
335
336 END Insert_Parent_Rec_Tmp;
337
338 /*====================================================================================================
339 ROLLUP_BUDGET_VERSION: This is the main API which will do whatever necessary for doing rollup into
340 pa_resource_assignments, PA_PROJ_PERIODS_DENORM and pa_budget_versions_tables.
341
342
343 r11.5 FP.M Developement ----------------------------------
344
345 08-JAN-2004 jwhite Bug 3362316
346 Extensively rewrote Rollup_Budget_Version. Purged
347 most of the obsolete logic because there was
348 a lot of it.
349
350
351 - Replaced pre-M rollup logic with two-level rollup:
352 a) pa_budget_lines to pa_resource_assignments
353 b) pa_resource_assignments to pa_budget_versions
354
355 - For p_entire_version = 'N', gutted all logic for
356 this condition. All logic was obsolete.
357
358 18-FEB-2004 jwhite Bug 3441943
359 For the Rollup_Budget_Version procedure, modified cursor and
360 update logic to separately total people (labor) and equiment quantities.
361
362
363
364
365 ===================================================================================================*/
366
367 PROCEDURE ROLLUP_BUDGET_VERSION(
368 p_budget_version_id IN NUMBER
369 ,p_entire_version IN VARCHAR2
370 ,p_context IN VARCHAR2
371 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
372 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
373 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
374
375 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
376 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
377 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
378 l_uncat_rlm_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
379 l_project_id pa_projects.project_id%TYPE;
380 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
381 l_proj_raw_cost pa_resource_assignments.TOTAL_PROJECT_RAW_COST%TYPE;
382 l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
383 l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
384 /* l_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE; -- bug 3441943 */
385 l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
386 l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
387 l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
388 l_rec_insert NUMBER;
389 l_data_source VARCHAR2(20);
390 l_proj_raw_cost_tbl l_proj_raw_cost_tbl_typ;
391 l_proj_burd_cost_tbl l_proj_burd_cost_tbl_typ;
392 l_proj_revenue_tbl l_proj_revenue_tbl_typ;
393 l_projfunc_raw_cost_tbl l_projfunc_raw_cost_tbl_typ;
394 l_projfunc_burd_cost_tbl l_projfunc_burd_cost_tbl_typ;
395 l_projfunc_revenue_tbl l_projfunc_revenue_tbl_typ;
396 l_quantity_tbl l_quantity_tbl_typ;
397 l_uom_tbl l_unit_of_measure_tbl_typ;
398 l_ra_id_tbl l_ra_id_tbl_typ;
399
400 l_period_profile_id PA_BUDGET_VERSIONS.PERIOD_PROFILE_ID%TYPE;
401
402 l_msg_count NUMBER := 0;
403 l_data VARCHAR2(2000);
404 l_msg_data VARCHAR2(2000);
405 l_msg_index_out NUMBER;
406 l_return_status VARCHAR2(2000);
407 l_debug_mode VARCHAR2(30);
408
409 -- Bug 3362316, 08-JAN-2004: ---------------------
410
411 l_org_forecast_flag PA_PROJECT_TYPES_ALL.org_project_flag%TYPE;
412
413 -- End, Bug 3362316, 08-JAN-2004: ---------------------
414
415 -- Bug 3441943, 18-FEB-2004: New Vars to sum people (labor) and equipment quantities ------------
416
417 l_labor_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
418 l_equip_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
419
420 -- End, Bug 3441943, 18-FEB-2004: New Vars to sum people and equipment quantities -------
421
422
423 /* #2800670: Added the following cursor instead of a Single Select so that
424 if the Project Level rolled up record does not exist then the select
425 does not give an error. */
426
427 -- Bug 3362316, 08-JAN-2004: changed to query all version assignment records --------------------------
428
429 -- Bug 3441943, 18-FEB-2004: Added DECODE to separately sum people (labor) and equipment quantities ------------
430
431 -- Bug 3968340, 29-OCT-2004: Added DECODE to rollup quantity only if UOM is hours
432
433 CURSOR c_proj_level_amounts(p_budget_version_id IN NUMBER) IS
434 SELECT sum(nvl(total_project_raw_cost,0))
435 ,sum(nvl(total_project_burdened_cost,0))
436 ,sum(nvl(total_project_revenue,0))
437 ,sum(nvl(total_plan_raw_cost,0))
438 ,sum(nvl(total_plan_burdened_cost,0))
439 ,sum(nvl(total_plan_revenue,0))
440 ,sum(decode(RESOURCE_CLASS_CODE, 'PEOPLE', decode(unit_of_measure,'HOURS',nvl(total_plan_quantity,0),0),0 ) )
441 ,sum(decode(RESOURCE_CLASS_CODE, 'EQUIPMENT',decode(unit_of_measure,'HOURS',nvl(total_plan_quantity,0),0),0 ) )
442 FROM pa_resource_assignments
443 WHERE budget_version_id = p_budget_version_id;
444
445
446 -- End, Bug 3441943, 18-FEB-2004: Added DECODE to separately sum people and equipment quantities ---------
447
448 -- End, Bug 3362316, 08-JAN-2004: changed to query all version assignment records -----------------------
449
450 --For bug 3489929
451 CURSOR c_res_amt_diffs IS
452 SELECT resource_assignment_id
453 ,sum(nvl(project_raw_cost,0) - nvl(old_proj_raw_cost,0)) project_raw_cost_diff
454 ,sum(nvl(project_burdened_cost,0) - nvl(old_proj_burdened_cost,0)) project_burdened_cost_diff
455 ,sum(nvl(project_revenue,0) - nvl(old_proj_revenue,0)) project_revenue_diff
456 ,sum(nvl(projfunc_raw_cost,0) - nvl(old_projfunc_raw_cost,0)) projfunc_raw_cost_diff
457 ,sum(nvl(projfunc_burdened_cost,0) - nvl(old_projfunc_burdened_cost,0)) projfunc_burdened_cost_diff
458 ,sum(nvl(projfunc_revenue,0) - nvl(old_projfunc_revenue,0)) projfunc_revenue_diff
459 ,sum(nvl(quantity,0) - nvl(old_quantity,0)) quantity_diff
460 FROM PA_FP_ROLLUP_TMP
461 GROUP BY resource_assignment_id;
462
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
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;
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';
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
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');
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 */
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
514 INTO l_period_profile_id
515 FROM PA_BUDGET_VERSIONS
516 WHERE budget_version_id = p_budget_version_id;
517
518
519 IF (p_entire_version = 'Y') THEN
520
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
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'
539 THEN NULL;
540 ELSE
541 UPDATE pa_resource_assignments pra
542 SET (parent_assignment_id
543 ,total_project_raw_cost
544 ,total_project_burdened_cost
545 ,total_project_revenue
546 ,total_plan_raw_cost
547 ,total_plan_burdened_cost
548 ,total_plan_revenue
549 ,total_plan_quantity) =
550 (SELECT NULL
551 ,sum(nvl(project_raw_cost,0))
552 ,sum(nvl(project_burdened_cost,0))
553 ,sum(nvl(project_revenue,0))
554 ,sum(nvl(raw_cost,0))
555 ,sum(nvl(burdened_cost,0))
556 ,sum(nvl(revenue,0))
557 ,sum(nvl(quantity,0))
558 FROM pa_budget_lines pbl
559 WHERE pbl.resource_assignment_id = pra.resource_assignment_id
560 --and pbl.cost_rejection_code IS NULL --commented these conditions of rejection codes for Bug #14177908
561 --and pbl.revenue_rejection_code IS NULL
562 --and pbl.burden_rejection_code IS NULL
563 --and pbl.other_rejection_code IS NULL
564 --and pbl.pc_cur_conv_rejection_code IS NULL
565 --and pbl.pfc_cur_conv_rejection_code IS NULL
566 and pbl.budget_version_id = p_budget_version_id ) --Added for bug 4141042
567 WHERE budget_version_id = p_budget_version_id;
568 --AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED; For bug 3668727
569 END IF; --end of changes for bug 4160258.
570
571 -- End, Bug 3362316, 08-JAN-2004: Obsolete for FP.M; Added Update ---------------------
572
573
574 -- Bug 3362316, 08-JAN-2004: Added Conditional Logic for ORG_FORECAST ---------------------
575
576
577
578 IF l_period_profile_id IS NOT NULL
579 THEN
580
581
582
583 BEGIN
584 SELECT org_project_flag
585 INTO l_org_forecast_flag
586 FROM pa_budget_versions v
587 , pa_projects_all p
588 , pa_project_types_all pt
589 WHERE v.budget_version_id = p_budget_version_id
590 AND v.project_id = p.project_id
591 AND p.project_type = pt.project_type
592 /* Bug fix: 4510784 AND Nvl(p.org_id, -99) = nvl(pt.org_id,-99); */ /* Bug 4193069*/
593 AND p.org_id = pt.org_id;
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 l_org_forecast_flag := NULL;
597 END;
598
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
608 REFRESH_PERIOD_DENORM(p_budget_version_id => p_budget_version_id
609 ,x_return_status => x_return_status
610 ,x_msg_count => x_msg_count
611 ,x_msg_data => x_msg_data);
612
613
614 END IF; -- (l_org_forecast_flag = 'Y'
615
616
617
618 END IF; -- l_period_profile_id IS NOT NULL
619
620
621 -- End, Bug 3362316, 08-JAN-2004: Added Conditional Logic for ORG_FORECAST ------------------
622 ELSIF p_entire_version = 'N' THEN
623
624
625 --Update the amounts for the resource assignments available in pa_fp_rollup_tmp . Bug 3489929
626 OPEN c_res_amt_diffs;
627 FETCH c_res_amt_diffs BULK COLLECT INTO
628 l_ra_id_tbl
629 ,l_proj_raw_cost_tbl
630 ,l_proj_burd_cost_tbl
631 ,l_proj_revenue_tbl
632 ,l_projfunc_raw_cost_tbl
633 ,l_projfunc_burd_cost_tbl
634 ,l_projfunc_revenue_tbl
635 ,l_quantity_tbl;
636 CLOSE c_res_amt_diffs;
637
638 IF l_ra_id_tbl.COUNT > 0 THEN
639
640 FORALL i IN l_ra_id_tbl.first..l_ra_id_tbl.last
641 UPDATE pa_resource_assignments
642 SET TOTAL_PROJECT_RAW_COST = nvl(TOTAL_PROJECT_RAW_COST,0) + l_proj_raw_cost_tbl(i)
643 ,TOTAL_PROJECT_BURDENED_COST = nvl(TOTAL_PROJECT_BURDENED_COST,0) + l_proj_burd_cost_tbl(i)
644 ,TOTAL_PROJECT_REVENUE = nvl(TOTAL_PROJECT_REVENUE,0) + l_proj_revenue_tbl(i)
645 ,TOTAL_PLAN_RAW_COST = nvl(TOTAL_PLAN_RAW_COST,0) + l_projfunc_raw_cost_tbl(i)
646 ,TOTAL_PLAN_BURDENED_COST = nvl(TOTAL_PLAN_BURDENED_COST,0) + l_projfunc_burd_cost_tbl(i)
647 ,TOTAL_PLAN_REVENUE = nvl(TOTAL_PLAN_REVENUE,0) + l_projfunc_revenue_tbl(i)
648 ,TOTAL_PLAN_QUANTITY = nvl(TOTAL_PLAN_QUANTITY,0) + l_quantity_tbl(i)
649 WHERE resource_assignment_id = l_ra_id_tbl(i);
650 END IF;
651
652 END IF; -- p_entire_version = 'Y'
653
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
663 /* #2593261: Added the total_plan_quantity column which was missed out
664 earlier, because of which labor_quantity was not being updated
665 in pa_budget_versions. */
666
667
668 /* Opening a cursor to get the project levee amounts. */
669
670
671 -- Bug 3362316, 08-JAN-2004: Removed l_uncat_rlm_id parameter ---------------
672
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
682
683 -- Bug 3441943, 18-FEB-2004: New Vars to sum people (labor) and equipment quantities ------------
684
685 FETCH c_proj_level_amounts INTO
686 l_proj_raw_cost
687 ,l_proj_burdened_cost
688 ,l_proj_revenue
689 ,l_projfunc_raw_cost
690 ,l_projfunc_burdened_cost
691 ,l_projfunc_revenue
692 ,l_labor_quantity
693 ,l_equip_quantity;
694
695 -- End,Bug 3441943, 18-FEB-2004: New Vars to sum people (labor) and equipment quantities ------------
696
697 --Commented out this condition for bug 3801879. The budget versions table should always be
698 --updated with the sum of amounts in the budget lines table in this API.
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
708 -- Bug 3441943, 18-FEB-2004: New Vars for people (labor) and equipment quantities ------------
709
710 UPDATE pa_budget_versions
711 SET raw_cost = l_projfunc_raw_cost
712 ,burdened_cost = l_projfunc_burdened_cost
713 ,revenue = l_projfunc_revenue
714 ,total_project_raw_cost = l_proj_raw_cost
715 ,total_project_burdened_cost = l_proj_burdened_cost
716 ,total_project_revenue = l_proj_revenue
717 ,labor_quantity = l_labor_quantity
718 ,equipment_quantity = l_equip_quantity
719 ,last_update_date = SYSDATE -- Added for bug 3394907
720 ,last_updated_by = FND_GLOBAL.user_id -- Added for bug 3394907
721 ,last_update_login = FND_GLOBAL.login_id -- Added for bug 3394907
722 WHERE budget_version_id = p_budget_version_id;
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
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;
740 IF l_msg_count = 1 THEN
741 PA_INTERFACE_UTILS_PUB.get_messages
742 (p_encoded => FND_API.G_TRUE,
743 p_msg_index => 1,
744 p_msg_count => l_msg_count,
745 p_msg_data => l_msg_data,
746 p_data => l_data,
747 p_msg_index_out => l_msg_index_out);
748 x_msg_data := l_data;
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
767 raise FND_API.G_EXC_UNEXPECTED_ERROR;
768 END ROLLUP_BUDGET_VERSION;
769
770 /*===================================================================================================
771 REFRESH_RESOURCE_ASSIGNMENTS: This API refreshes the complete resource assignments table.
772 It does following deletes all rolled up records from pa_resource_assignments.Update amounts
773 from pa_budget_lines on all user_entered records. Insert parents for all the records with amounts.
774 Stamps the parent_assignment_id on all the records.
775 This procedure returns without any action in case there are no records in PA_RESOURCE_ASSIGNMENTS.
776 ===================================================================================================*/
777
778 PROCEDURE REFRESH_RESOURCE_ASSIGNMENTS(p_budget_version_id IN NUMBER
779 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
780 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
781 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
782
783 /* #2697999: For the Resource group, UOM is dependent on the track as labor flag
784 of the corresponding resource list member id. If the track as labor flag is
785 'Y', then unit_of_measure is 'HOURS', else it is NULL.
786 For all other rolled up records, the uom is 'HOURS' and track as labor flag is
787 'Y'. */
788
789 CURSOR Cur_Res_Level(c_budget_version_id IN NUMBER) is
790 SELECT pra.task_id
791 ,prlm.parent_member_id resource_list_member_id
792 ,sum(nvl(pra.total_project_raw_cost,0))
793 ,sum(nvl(pra.total_project_burdened_cost,0))
794 ,sum(nvl(pra.total_project_revenue,0))
795 ,sum(nvl(pra.total_plan_raw_cost,0))
796 ,sum(nvl(pra.total_plan_burdened_cost,0))
797 ,sum(nvl(pra.total_plan_revenue,0))
798 ,SUM(DECODE(parent_prlm.track_as_labor_flag,'Y',
799 NVL(DECODE(pra.unit_of_measure,'HOURS',
800 pra.total_plan_quantity,0),0)
801 ,NULL)) total_plan_quantity -- Modified for bug #2697999
802 ,MAX(DECODE(parent_prlm.track_as_labor_flag,'Y',
803 'HOURS',NULL)) unit_of_measure -- Modified for bug #2697999
804 ,MAX(parent_prlm.track_as_labor_flag) track_as_labor_flag
805 -- Modified for bug #2697999
806 FROM pa_resource_assignments pra
807 ,pa_resource_list_members prlm
808 ,pa_resource_list_members parent_prlm -- Added for bug #2697999
809 WHERE pra.budget_version_id = c_budget_version_id
810 AND pra.resource_list_member_id <> 0
811 AND pra.resource_list_member_id = prlm.resource_list_member_id
812 AND prlm.parent_member_id = parent_prlm.resource_list_member_id
813 AND prlm.parent_member_id IS NOT NULL
814 AND (pra.total_plan_quantity IS NOT NULL -- Added for bug #2784520
815 OR pra.total_plan_raw_cost IS NOT NULL
816 OR pra.total_plan_revenue IS NOT NULL
817 OR pra.total_plan_burdened_cost IS NOT NULL)
818 GROUP BY pra.task_id, prlm.parent_member_id;
819
820 CURSOR Cur_Task_Level(c_budget_version_id IN NUMBER) is
821 SELECT task_id
822 ,0 resource_list_member_id
823 ,sum(nvl(pra.total_project_raw_cost,0))
824 ,sum(nvl(pra.total_project_burdened_cost,0))
825 ,sum(nvl(pra.total_project_revenue,0))
826 ,sum(nvl(pra.total_plan_raw_cost,0))
827 ,sum(nvl(pra.total_plan_burdened_cost,0))
828 ,sum(nvl(pra.total_plan_revenue,0))
829 ,sum(nvl(decode(pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
830 pra.total_plan_quantity,0),0)) quantity
831 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for bug #2697999
832 ,'Y' -- Modified for bug #2697999
833 FROM pa_resource_assignments pra
834 WHERE pra.budget_version_id = c_budget_version_id
835 AND pra.resource_list_member_id <> 0
836 AND pra.resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
837 AND (pra.total_plan_quantity IS NOT NULL -- Added for bug #2784520
838 OR pra.total_plan_raw_cost IS NOT NULL
839 OR pra.total_plan_revenue IS NOT NULL
840 OR pra.total_plan_burdened_cost IS NOT NULL)
841 GROUP BY task_id;
842
843 CURSOR Cur_Parent_Task(c_uncat_rlm_id IN NUMBER,
844 c_curr_res_assignment_id IN NUMBER,
845 c_budget_version_id IN NUMBER,
846 c_process_wbs_level IN NUMBER) IS
847 SELECT pt.PARENT_TASK_ID task_id
848 ,0 resource_list_member_id
849 ,sum(nvl(pra.total_project_raw_cost,0))
850 ,sum(nvl(pra.total_project_burdened_cost,0))
851 ,sum(nvl(pra.total_project_revenue,0))
852 ,sum(nvl(pra.total_plan_raw_cost,0))
853 ,sum(nvl(pra.total_plan_burdened_cost,0))
854 ,sum(nvl(pra.total_plan_revenue,0))
855 ,sum(nvl(pra.total_plan_quantity,0)) quantity /* no decode required on quantity */
856 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for bug #2697999
857 ,'Y' -- Modified for bug #2697999
858 FROM pa_resource_assignments pra
859 ,pa_tasks pt
860 WHERE pra.budget_version_id = c_budget_version_id
861 AND pra.resource_list_member_id IN (c_uncat_rlm_id,0)
862 -- AND resource_assignment_id > c_curr_res_assignment_id /* mano: this is wrong with new logic */
863 AND pt.wbs_level = c_process_wbs_level /* added due to bug during UT */
864 AND pra.task_id = pt.task_id
865 AND pt.parent_task_id IS NOT NULL /* M23-08 missed even after review comment */
866 AND (pra.total_plan_quantity IS NOT NULL -- Added for bug #2784520
867 OR pra.total_plan_raw_cost IS NOT NULL
868 OR pra.total_plan_revenue IS NOT NULL
869 OR pra.total_plan_burdened_cost IS NOT NULL)
870 GROUP BY pt.parent_task_id;
871
872 /* #2800670: Added the following cursors to get the project level amounts
873 instead of using Singular Selects which might return a No_Data_Found. */
874
875
876 /* Planning Level is Project. Hence looking at User Entered records.
877 Project Level record should be created when the amounts exist at
878 User Entered level. */
879 CURSOR c_proj_level_amts1(p_budget_version_id IN NUMBER) IS
880 SELECT pra.project_id
881 ,0 task_id
882 ,0 resource_list_member_id
883 ,sum(pra.total_project_raw_cost)
884 ,sum(pra.total_project_burdened_cost)
885 ,sum(pra.total_project_revenue)
886 ,sum(pra.total_plan_raw_cost)
887 ,sum(pra.total_plan_burdened_cost)
888 ,sum(pra.total_plan_revenue)
889 ,sum(decode(unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,
890 nvl(total_plan_quantity,0),0)) quantity
891 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS unit_of_measure --Modified for bug #2697999
892 ,'Y' track_as_labor_flag --Modified for bug #2697999
893 FROM pa_resource_assignments pra
894 WHERE pra.budget_version_id = p_budget_Version_id
895 AND pra.resource_assignment_type = 'USER_ENTERED'
896 AND (pra.total_plan_quantity IS NOT NULL
897 OR pra.total_plan_raw_cost IS NOT NULL
898 OR pra.total_plan_revenue IS NOT NULL
899 OR pra.total_plan_burdened_cost IS NOT NULL)
900 GROUP BY pra.project_id;
901
902
903 /* Planning Level not Project. */
904 CURSOR c_proj_level_amts2(p_budget_version_id IN NUMBER ,
905 l_uncat_rlm_id IN NUMBER ) IS
906 SELECT pra.project_id
907 ,0 task_id
908 ,0 resource_list_member_id
909 ,sum(pra.total_project_raw_cost)
910 ,sum(pra.total_project_burdened_cost)
911 ,sum(pra.total_project_revenue)
912 ,sum(pra.total_plan_raw_cost)
913 ,sum(pra.total_plan_burdened_cost)
914 ,sum(pra.total_plan_revenue)
915 ,sum(nvl(total_plan_quantity,0)) quantity
916 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS unit_of_measure --Modified for bug #2697999
917 ,'Y' track_as_labor_flag --Modified for bug #2697999
918 FROM pa_resource_assignments pra,
919 pa_tasks pt
920 WHERE pra.budget_version_id = p_budget_Version_id
921 AND pra.resource_list_member_id in (l_uncat_rlm_id,0)
922 AND pra.project_id = pt.project_id -- Fixed for #2807678
923 AND pra.task_id = pt.task_id
924 AND pra.task_id = pt.top_task_id
925 GROUP BY pra.project_id;
926
927 /* #2808442: Cursor to check if there are any Budget Lines for the Version. */
928
929 CURSOR c_budget_lines_exist(p_budget_version_id IN NUMBER) IS
930 SELECT 1
931 FROM DUAL
932 WHERE EXISTS (SELECT resource_assignment_id
933 FROM pa_budget_lines
934 WHERE budget_version_id = p_budget_version_id);
935
936 l_budget_line_exists NUMBER;
937
938 l_proj_raw_cost_tbl l_proj_raw_cost_tbl_typ;
939 l_proj_burd_cost_tbl l_proj_burd_cost_tbl_typ;
940 l_proj_revenue_tbl l_proj_revenue_tbl_typ;
941
942 l_projfunc_raw_cost_tbl l_projfunc_raw_cost_tbl_typ;
943 l_projfunc_burd_cost_tbl l_projfunc_burd_cost_tbl_typ;
944 l_projfunc_revenue_tbl l_projfunc_revenue_tbl_typ;
945
946 l_quantity_tbl l_quantity_tbl_typ;
947
948 l_task_id_tbl l_task_id_tbl_typ;
949 l_res_list_mem_id_tbl l_res_list_mem_id_tbl_typ;
950 l_unit_of_measure_tbl l_unit_of_measure_tbl_typ;
951 l_track_as_labor_flag_tbl l_track_as_labor_flag_tbl_typ;
952
953 l_curr_res_assignment_id pa_resource_assignments.resource_assignment_id%TYPE ;
954 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
955 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
956 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
957 l_uncat_rlm_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
958 l_project_id pa_projects.project_id%TYPE;
959 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
960
961 l_proj_raw_cost pa_resource_assignments.total_project_raw_cost%TYPE;
962 l_proj_burd_cost pa_resource_assignments.total_project_burdened_cost%TYPE;
963 l_proj_revenue pa_resource_assignments.total_project_revenue%TYPE;
964 l_projfunc_raw_cost pa_resource_assignments.total_plan_raw_cost%TYPE;
965 l_projfunc_burd_cost pa_resource_assignments.total_plan_burdened_cost%TYPE;
966 l_proj_func_revenue pa_resource_assignments.total_plan_revenue%TYPE;
967
968 l_quantity pa_resource_assignments.total_plan_quantity%TYPE;
969
970 l_uom pa_resource_assignments.unit_of_measure%TYPE;
971 l_track_labor_flag pa_resource_assignments.track_as_labor_flag%TYPE;
972 l_proj_ra_id pa_resource_assignments.resource_assignment_id%TYPE ;
973
974 /* Variables to be used for debugging purpose */
975
976 l_msg_count NUMBER := 0;
977 l_data VARCHAR2(2000);
978 l_msg_data VARCHAR2(2000);
979 l_msg_index_out NUMBER;
980 l_return_status VARCHAR2(2000);
981 l_debug_mode VARCHAR2(10);
982
983 l_curr_wbs_level NUMBER := null;
984 l_continue_processing_flag VARCHAR2(1) := 'Y';
985
986 l_count NUMBER;
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');
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';
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. */
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
1028 populate_local_vars(p_budget_version_id => p_budget_version_id,
1029 x_project_id => l_project_id,
1030 x_resource_list_id => l_resource_list_id,
1031 x_uncat_flag => l_uncat_flag,
1032 x_uncat_rlm_id => l_uncat_rlm_id,
1033 x_rl_group_type_id => l_rl_group_type_id,
1034 x_planning_level => l_planning_level,
1035 x_return_status => x_return_status,
1036 x_msg_count => x_msg_count,
1037 x_msg_data => x_msg_data);
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
1047 DELETE FROM pa_resource_assignments
1048 WHERE budget_version_id = p_budget_version_id
1049 AND resource_assignment_type = PA_FP_CONSTANTS_PKG.G_ROLLED_UP;
1050
1051 /* Update the pa_resource_assignments USER_ENTERED records set
1052 parent_assignment_id as null. Also update the amounts. Amounts need to
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
1062 UPDATE pa_resource_assignments pra
1063 SET (parent_assignment_id
1064 ,total_project_raw_cost
1065 ,total_project_burdened_cost
1066 ,total_project_revenue
1067 ,total_plan_raw_cost
1068 ,total_plan_burdened_cost
1069 ,total_plan_revenue
1070 ,total_plan_quantity) =
1071 (SELECT NULL
1072 ,sum(nvl(project_raw_cost,0))
1073 ,sum(nvl(project_burdened_cost,0))
1074 ,sum(nvl(project_revenue,0))
1075 ,sum(nvl(raw_cost,0))
1076 ,sum(nvl(burdened_cost,0))
1077 ,sum(nvl(revenue,0))
1078 ,sum(nvl(quantity,0))
1079 FROM pa_budget_lines pbl
1080 WHERE pbl.resource_assignment_id = pra.resource_assignment_id)
1081 WHERE budget_version_id = p_budget_version_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
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;
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;
1110 RETURN;
1111 END IF;
1112 CLOSE c_budget_lines_exist;
1113
1114 IF (l_uncat_flag <> 'Y') THEN /* If Resource List is not uncategorized */
1115 IF nvl(l_rl_group_type_id,0) <> 0 THEN /* If Res List is grouped */
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
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
1134 ,l_res_list_mem_id_tbl
1135 ,l_proj_raw_cost_tbl
1136 ,l_proj_burd_cost_tbl
1137 ,l_proj_revenue_tbl
1138 ,l_projfunc_raw_cost_tbl
1139 ,l_projfunc_burd_cost_tbl
1140 ,l_projfunc_revenue_tbl
1141 ,l_quantity_tbl
1142 ,l_unit_of_measure_tbl
1143 ,l_track_as_labor_flag_tbl
1144 LIMIT g_plsql_max_array_size;
1145
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
1155 Insert_Bulk_Rows_Res(p_project_id => l_project_id
1156 ,p_plan_version_id => p_budget_version_id
1157 ,p_task_id_tbl => l_task_id_tbl
1158 ,p_res_list_mem_id_tbl => l_res_list_mem_id_tbl
1159 ,p_proj_raw_cost_tbl => l_proj_raw_cost_tbl
1160 ,p_proj_burdened_cost_tbl => l_proj_burd_cost_tbl
1161 ,p_proj_revenue_tbl => l_proj_revenue_tbl
1162 ,p_projfunc_raw_cost_tbl => l_projfunc_raw_cost_tbl
1163 ,p_projfunc_burd_cost_tbl => l_projfunc_burd_cost_tbl
1164 ,p_projfunc_revenue_tbl => l_projfunc_revenue_tbl
1165 ,p_quantity_tbl => l_quantity_tbl
1166 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl
1167 ,p_track_as_labor_flag_tbl => l_track_as_labor_flag_tbl
1168 ,x_return_status => x_return_status
1169 ,x_msg_count => x_msg_count
1170 ,x_msg_data => x_msg_data );
1171
1172
1173 END IF; /* end of only if something is fetched */
1174
1175 EXIT WHEN nvl(l_task_id_tbl.last,0) < g_plsql_max_array_size;
1176
1177 END LOOP;
1178 CLOSE Cur_Res_Level;
1179
1180 END IF; /* Resource List Grouped */
1181
1182 /* Clear the PL/SQL tables to be used later */
1183 l_task_id_tbl.delete;
1184 l_res_list_mem_id_tbl.delete;
1185 l_proj_raw_cost_tbl.delete;
1186 l_proj_burd_cost_tbl.delete;
1187 l_proj_revenue_tbl.delete;
1188 l_projfunc_raw_cost_tbl.delete;
1189 l_projfunc_burd_cost_tbl.delete;
1190 l_projfunc_revenue_tbl.delete;
1191 l_quantity_tbl.delete;
1192 l_unit_of_measure_tbl.delete;
1193 l_track_as_labor_flag_tbl.delete;
1194
1195 /* If planning_level is not 'PROJECT'then Inserting Task Level records for all USER_ENTERED
1196 level records. This step need to be done whether resource list is grouped or not. We select
1197 USER_ENTEREDrecords only because for certain records amounts could be entered at resource
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
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
1216 ,l_res_list_mem_id_tbl
1217 ,l_proj_raw_cost_tbl
1218 ,l_proj_burd_cost_tbl
1219 ,l_proj_revenue_tbl
1220 ,l_projfunc_raw_cost_tbl
1221 ,l_projfunc_burd_cost_tbl
1222 ,l_projfunc_revenue_tbl
1223 ,l_quantity_tbl
1224 ,l_unit_of_measure_tbl
1225 ,l_track_as_labor_flag_tbl
1226 LIMIT g_plsql_max_array_size;
1227
1228 IF nvl(l_task_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
1229
1230 Insert_Bulk_Rows_Res(p_project_id => l_project_id
1231 ,p_plan_version_id => p_budget_version_id
1232 ,p_task_id_tbl => l_task_id_tbl
1233 ,p_res_list_mem_id_tbl => l_res_list_mem_id_tbl
1234 ,p_proj_raw_cost_tbl => l_proj_raw_cost_tbl
1235 ,p_proj_burdened_cost_tbl => l_proj_burd_cost_tbl
1236 ,p_proj_revenue_tbl => l_proj_revenue_tbl
1237 ,p_projfunc_raw_cost_tbl => l_projfunc_raw_cost_tbl
1238 ,p_projfunc_burd_cost_tbl => l_projfunc_burd_cost_tbl
1239 ,p_projfunc_revenue_tbl => l_projfunc_revenue_tbl
1240 ,p_quantity_tbl => l_quantity_tbl
1241 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl
1242 ,p_track_as_labor_flag_tbl => l_track_as_labor_flag_tbl
1243 ,x_return_status => x_return_status
1244 ,x_msg_count => x_msg_count
1245 ,x_msg_data => x_msg_data );
1246
1247 END IF; /* end of only if something is fetched */
1248
1249 EXIT WHEN nvl(l_task_id_tbl.last,0) < g_plsql_max_array_size;
1250
1251 END LOOP;
1252 CLOSE Cur_Task_Level;
1253
1254 END IF; /* l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT*/
1255
1256 END IF; /* If Resource List is not uncategorized */
1257
1258
1259 /* Clear the PL/SQL tables to be used later */
1260 l_task_id_tbl.delete;
1261 l_res_list_mem_id_tbl.delete;
1262 l_proj_raw_cost_tbl.delete;
1263 l_proj_burd_cost_tbl.delete;
1264 l_proj_revenue_tbl.delete;
1265 l_projfunc_raw_cost_tbl.delete;
1266 l_projfunc_burd_cost_tbl.delete;
1267 l_projfunc_revenue_tbl.delete;
1268 l_quantity_tbl.delete;
1269 l_unit_of_measure_tbl.delete;
1270 l_track_as_labor_flag_tbl.delete;
1271
1272 /* If planning_level is not 'project'or 'top_task'then we need to insert Parent Task
1273 Level records. */
1274
1275 IF l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1276 PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP) THEN
1277 /* Insert Parent Level Records */
1278
1279 /* Initialise the variable l_cur_res_assignment_id to 0. */
1280 l_curr_res_assignment_id := 0;
1281
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
1291 l_curr_wbs_level := null;
1292
1293 select max(wbs_level)
1294 into l_curr_wbs_level
1295 from pa_tasks
1296 where project_id = l_project_id;
1297
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
1307 OPEN Cur_Parent_Task(l_uncat_rlm_id,
1308 l_curr_res_assignment_id,
1309 p_budget_version_id,
1310 l_curr_wbs_level);
1311
1312 /* Get the current/next value of sequence for resource assignment
1313 id into l_curr_res_assignment_id just after opening the cursor. */
1314
1315 IF l_curr_res_assignment_id = 0 THEN
1316 SELECT PA_RESOURCE_ASSIGNMENTS_S.nextval
1317 INTO l_curr_res_assignment_id
1318 FROM dual;
1319 ELSE
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
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
1337 ,l_res_list_mem_id_tbl
1338 ,l_proj_raw_cost_tbl
1339 ,l_proj_burd_cost_tbl
1340 ,l_proj_revenue_tbl
1341 ,l_projfunc_raw_cost_tbl
1342 ,l_projfunc_burd_cost_tbl
1343 ,l_projfunc_revenue_tbl
1344 ,l_quantity_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
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
1363 ,p_plan_version_id => p_budget_version_id
1364 ,p_task_id_tbl => l_task_id_tbl
1365 ,p_res_list_mem_id_tbl => l_res_list_mem_id_tbl
1366 ,p_proj_raw_cost_tbl => l_proj_raw_cost_tbl
1367 ,p_proj_burdened_cost_tbl => l_proj_burd_cost_tbl
1368 ,p_proj_revenue_tbl => l_proj_revenue_tbl
1369 ,p_projfunc_raw_cost_tbl => l_projfunc_raw_cost_tbl
1370 ,p_projfunc_burd_cost_tbl => l_projfunc_burd_cost_tbl
1371 ,p_projfunc_revenue_tbl => l_projfunc_revenue_tbl
1372 ,p_quantity_tbl => l_quantity_tbl
1373 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl
1374 ,p_track_as_labor_flag_tbl => l_track_as_labor_flag_tbl
1375 ,x_return_status => x_return_status
1376 ,x_msg_count => x_msg_count
1377 ,x_msg_data => x_msg_data );
1378
1379 END IF; /* end of only if something is fetched */
1380
1381 EXIT WHEN nvl(l_task_id_tbl.last,0) < g_plsql_max_array_size;
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
1391 l_curr_wbs_level := l_curr_wbs_level - 1;
1392
1393 END LOOP; /* End Loop of the outer loop */
1394
1395 END IF; /* l_planning_level not in PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT,
1396 PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP*/
1397
1398 /* Creating a project level record in pa_resource_assignment table. Resource
1399 list member id should be l_uncat_rlm_id for such record. Store the
1400 resource_assignment_id of this record in l_proj_ra_id for later use. */
1401
1402 /* Clear the PL/SQL tables to be used later */
1403 l_task_id_tbl.delete;
1404 l_res_list_mem_id_tbl.delete;
1405 l_proj_raw_cost_tbl.delete;
1406 l_proj_burd_cost_tbl.delete;
1407 l_proj_revenue_tbl.delete;
1408 l_projfunc_raw_cost_tbl.delete;
1409 l_projfunc_burd_cost_tbl.delete;
1410 l_projfunc_revenue_tbl.delete;
1411 l_quantity_tbl.delete;
1412 l_unit_of_measure_tbl.delete;
1413 l_track_as_labor_flag_tbl.delete;
1414
1415
1416 /* #2598502: Modified the following condition. */
1417
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
1427 NULL;
1428
1429 ELSE
1430
1431 /* #2598502: If the Planning Level is 'Project' then we should not
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
1441 OPEN c_proj_level_amts1(p_budget_version_id);
1442
1443 FETCH c_proj_level_amts1 INTO
1444 l_project_id
1445 ,l_task_id_tbl(1)
1446 ,l_res_list_mem_id_tbl(1)
1447 ,l_proj_raw_cost_tbl(1)
1448 ,l_proj_burd_cost_tbl(1)
1449 ,l_proj_revenue_tbl(1)
1450 ,l_projfunc_raw_cost_tbl(1)
1451 ,l_projfunc_burd_cost_tbl(1)
1452 ,l_projfunc_revenue_tbl(1)
1453 ,l_quantity_tbl(1)
1454 ,l_unit_of_measure_tbl(1)
1455 ,l_track_as_labor_flag_tbl(1);
1456
1457 CLOSE c_proj_level_amts1;
1458
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
1468 OPEN c_proj_level_amts2(p_budget_version_id,l_uncat_rlm_id);
1469
1470 FETCH c_proj_level_amts2 INTO
1471 l_project_id
1472 ,l_task_id_tbl(1)
1473 ,l_res_list_mem_id_tbl(1)
1474 ,l_proj_raw_cost_tbl(1)
1475 ,l_proj_burd_cost_tbl(1)
1476 ,l_proj_revenue_tbl(1)
1477 ,l_projfunc_raw_cost_tbl(1)
1478 ,l_projfunc_burd_cost_tbl(1)
1479 ,l_projfunc_revenue_tbl(1)
1480 ,l_quantity_tbl(1)
1481 ,l_unit_of_measure_tbl(1)
1482 ,l_track_as_labor_flag_tbl(1);
1483
1484 CLOSE c_proj_level_amts2;
1485
1486 END IF; /* l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT */
1487
1488 -- bug#2659956 : Added the IF condition to avoid the issue of
1489 -- PA_RESOURCE_ASSIGNMENTS_S.CURRVAL not yet defined ,when
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
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
1508 fetched in the PL/SQL tables. */
1509
1510 IF nvl(l_task_id_tbl.last,0) > 0 THEN
1511
1512 Insert_Bulk_Rows_Res(p_project_id => l_project_id
1513 ,p_plan_version_id => p_budget_version_id
1514 ,p_task_id_tbl => l_task_id_tbl
1515 ,p_res_list_mem_id_tbl => l_res_list_mem_id_tbl
1516 ,p_proj_raw_cost_tbl => l_proj_raw_cost_tbl
1517 ,p_proj_burdened_cost_tbl => l_proj_burd_cost_tbl
1518 ,p_proj_revenue_tbl => l_proj_revenue_tbl
1519 ,p_projfunc_raw_cost_tbl => l_projfunc_raw_cost_tbl
1520 ,p_projfunc_burd_cost_tbl => l_projfunc_burd_cost_tbl
1521 ,p_projfunc_revenue_tbl => l_projfunc_revenue_tbl
1522 ,p_quantity_tbl => l_quantity_tbl
1523 ,p_unit_of_measure_tbl => l_unit_of_measure_tbl
1524 ,p_track_as_labor_flag_tbl => l_track_as_labor_flag_tbl
1525 ,x_return_status => x_return_status
1526 ,x_msg_count => x_msg_count
1527 ,x_msg_data => x_msg_data );
1528
1529 select pa_resource_assignments_s.currval
1530 into l_proj_ra_id
1531 from dual;
1532
1533 END IF;
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
1543 UPDATE_RES_PARENT_ASSIGN_ID(p_budget_version_id => p_budget_version_id
1544 ,p_proj_ra_id => l_proj_ra_id
1545 ,x_return_status => x_return_status
1546 ,x_msg_count => x_msg_count
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;
1556 IF l_msg_count = 1 THEN
1557 PA_INTERFACE_UTILS_PUB.get_messages
1558 (p_encoded => FND_API.G_TRUE,
1559 p_msg_index => 1,
1560 p_msg_count => l_msg_count,
1561 p_msg_data => l_msg_data,
1562 p_data => l_data,
1563 p_msg_index_out => l_msg_index_out);
1564 x_msg_data := l_data;
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
1583 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1584 END REFRESH_RESOURCE_ASSIGNMENTS;
1585
1586
1587 /*==============================================================================================
1588 UPDATE_RES_PARENT_ASSIGN_ID: This api will be stamping the parent assignment id on all records.
1589 ==============================================================================================*/
1590
1591 PROCEDURE UPDATE_RES_PARENT_ASSIGN_ID
1592 (p_budget_version_id IN NUMBER
1593 ,p_proj_ra_id IN NUMBER
1594 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1595 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1596 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1597
1598 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
1599 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
1600 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
1601 l_uncat_rlm_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
1602 l_project_id pa_projects.project_id%TYPE;
1603 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
1604
1605 l_msg_count NUMBER := 0;
1606 l_data VARCHAR2(2000);
1607 l_msg_data VARCHAR2(2000);
1608 l_msg_index_out NUMBER;
1609 l_return_status VARCHAR2(2000);
1610 l_debug_mode VARCHAR2(30);
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');
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';
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. */
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');
1643 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
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
1653 populate_local_vars(p_budget_version_id => p_budget_version_id,
1654 x_project_id => l_project_id,
1655 x_resource_list_id => l_resource_list_id,
1656 x_uncat_flag => l_uncat_flag,
1657 x_uncat_rlm_id => l_uncat_rlm_id,
1658 x_rl_group_type_id => l_rl_group_type_id,
1659 x_planning_level => l_planning_level,
1660 x_return_status => x_return_status,
1661 x_msg_count => x_msg_count,
1662 x_msg_data => x_msg_data);
1663
1664 /* SET PARENT ASSIGNMENT ID FOR ALL TASK LEVEL RECORDS ONLY IF PLANNING_LEVEL
1665 IS NOT 'PROJECT'or 'TOP_TASK'. */
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
1675 UPDATE pa_resource_assignments pra1
1676 SET parent_assignment_id =
1677 (SELECT resource_assignment_id
1678 FROM pa_resource_assignments pra2
1679 ,pa_tasks t
1680 WHERE pra2.task_id = t.parent_task_id
1681 AND pra1.task_id = t.task_id
1682 AND pra2.budget_version_id = p_budget_version_id
1683 AND pra2.resource_list_member_id = 0
1684 AND pra2.project_id = pra1.project_id ) -- Bug 2814165
1685 WHERE budget_version_id = p_budget_version_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
1695
1696 END IF;
1697
1698 /* IF RESOURCE LIST ATTACHED IS NOT UNCATEGORIZED AND RESOURCE LIST IS GROUPED THEN
1699 SET PARENT ASSIGNMENT ID FOR RESOURCE LEVEL RECORDS. */
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
1709 UPDATE pa_resource_assignments pra1
1710 SET parent_assignment_id =
1711 (SELECT resource_assignment_id
1712 FROM pa_resource_assignments pra2
1713 ,pa_resource_list_members prlm
1714 WHERE pra1.resource_list_member_id = prlm.resource_list_member_id
1715 AND pra1.task_id = pra2.task_id
1716 AND pra2.resource_list_member_id = prlm.parent_member_id
1717 AND pra2.budget_version_id = p_budget_version_id
1718 AND pra2.resource_list_member_id <> 0)
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
1728 END IF;
1729
1730 /* FOR RECORDS NOT YET UPDATED CAN BE THE RESOURCE LEVEL RECORDS FOR WHICH TASK LEVEL
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
1740 /* Do the following only when Planning Level is not 'Project' */
1741
1742 IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1743
1744 UPDATE pa_resource_assignments pra1
1745 SET parent_assignment_id =
1746 (SELECT resource_assignment_id
1747 FROM pa_resource_assignments pra2
1748 WHERE pra1.task_id = pra2.task_id
1749 AND pra2.resource_list_member_id = 0
1750 AND pra2.budget_version_id = p_budget_version_id
1751 AND pra2.project_id = pra1.project_id ) -- Bug 2814165
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
1761 END IF;
1762
1763 END IF; /* l_uncat_flag <> 'Y'*/
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
1773 IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
1774
1775 UPDATE pa_resource_assignments pra
1776 SET parent_assignment_id = p_proj_ra_id
1777 WHERE task_id in
1778 (SELECT top_task_id
1779 FROM pa_tasks
1780 WHERE project_id = l_project_id)
1781 AND budget_version_id = p_budget_version_id
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
1791
1792 END IF;
1793
1794 /* UPDATE THE RESOURCE/RESOURCE GROUP LEVEL RECORDS WITH PROJECT LEVEL RECORD'S
1795 RA ID AS PARENT_ASSIGNMENT_ID. THIS IS APPLICABLE ONLY WHEN PLANNING LEVEL IS PROJECT AND
1796 RESOURCE LIST IS ATTACHED. UPDATE ONLY THOSE RECORDS FOR WHICH PARENT_ASSIGNMENT_ID IS NULL. */
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
1806 UPDATE pa_resource_assignments pra
1807 SET parent_assignment_id = p_proj_ra_id
1808 WHERE parent_assignment_id is null
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;
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
1826 WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
1827 l_msg_count := FND_MSG_PUB.count_msg;
1828 IF l_msg_count = 1 THEN
1829 PA_INTERFACE_UTILS_PUB.get_messages
1830 (p_encoded => FND_API.G_TRUE,
1831 p_msg_index => 1,
1832 p_msg_count => l_msg_count,
1833 p_msg_data => l_msg_data,
1834 p_data => l_data,
1835 p_msg_index_out => l_msg_index_out);
1836 x_msg_data := l_data;
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
1855 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1856
1857 END UPDATE_RES_PARENT_ASSIGN_ID;
1858
1859 /*==================================================================================================
1860 INSERT_BULK_ROWS_RES: This procedure inserts records into PA_FP_ELEMENTS in BULK mode.
1861 ===================================================================================================*/
1862
1863 PROCEDURE Insert_Bulk_Rows_Res (
1864 p_project_id IN NUMBER
1865 ,p_plan_version_id IN NUMBER
1866 ,p_task_id_tbl IN l_task_id_tbl_typ
1867 ,p_res_list_mem_id_tbl IN l_res_list_mem_id_tbl_typ
1868 ,p_proj_raw_cost_tbl IN l_proj_raw_cost_tbl_typ
1869 ,p_proj_burdened_cost_tbl IN l_proj_burd_cost_tbl_typ
1870 ,p_proj_revenue_tbl IN l_proj_revenue_tbl_typ
1871 ,p_projfunc_raw_cost_tbl IN l_projfunc_raw_cost_tbl_typ
1872 ,p_projfunc_burd_cost_tbl IN l_projfunc_burd_cost_tbl_typ
1873 ,p_projfunc_revenue_tbl IN l_projfunc_revenue_tbl_typ
1874 ,p_quantity_tbl IN l_quantity_tbl_typ
1875 ,p_unit_of_measure_tbl IN l_unit_of_measure_tbl_typ
1876 ,p_track_as_labor_flag_tbl IN l_track_as_labor_flag_tbl_typ
1877 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1878 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1879 ,x_msg_data OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
1880
1881 l_msg_count NUMBER := 0;
1882 l_data VARCHAR2(2000);
1883 l_msg_data VARCHAR2(2000);
1884 l_msg_index_out NUMBER;
1885 l_return_status VARCHAR2(2000);
1886 l_debug_mode VARCHAR2(30);
1887
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');
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 ';
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
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
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
1926 FORALL i in p_task_id_tbl.first..p_task_id_tbl.last
1927
1928 INSERT INTO pa_resource_assignments
1929 (RESOURCE_ASSIGNMENT_ID
1930 ,BUDGET_VERSION_ID
1931 ,PROJECT_ID
1932 ,TASK_ID
1933 ,RESOURCE_LIST_MEMBER_ID
1934 ,TOTAL_PROJECT_RAW_COST
1935 ,TOTAL_PROJECT_BURDENED_COST
1936 ,TOTAL_PROJECT_REVENUE
1937 ,TOTAL_PLAN_QUANTITY
1938 ,TOTAL_PLAN_RAW_COST
1939 ,TOTAL_PLAN_BURDENED_COST
1940 ,TOTAL_PLAN_REVENUE
1941 ,LAST_UPDATE_DATE
1942 ,LAST_UPDATED_BY
1943 ,CREATION_DATE
1944 ,CREATED_BY
1945 ,LAST_UPDATE_LOGIN
1946 ,UNIT_OF_MEASURE
1947 ,TRACK_AS_LABOR_FLAG
1948 ,PROJECT_ASSIGNMENT_ID
1949 ,RESOURCE_ASSIGNMENT_TYPE )
1950 VALUES
1951 (PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL
1952 ,p_plan_version_id -- BUDGET_VERSION_ID
1953 ,p_project_id -- PROJECT_ID
1954 ,p_task_id_tbl(i) -- TASK_ID
1955 ,nvl(p_res_list_mem_id_tbl(i),0) -- RESOURCE_LIST_MEMBER_ID
1956 ,p_proj_raw_cost_tbl(i)
1957 ,p_proj_burdened_cost_tbl(i)
1958 ,p_proj_revenue_tbl(i)
1959 ,p_quantity_tbl(i)
1960 ,p_projfunc_raw_cost_tbl(i)
1961 ,p_projfunc_burd_cost_tbl(i)
1962 ,p_projfunc_revenue_tbl(i)
1963 ,sysdate
1964 ,fnd_global.user_id
1965 ,sysdate
1966 ,fnd_global.user_id
1967 ,fnd_global.login_id
1968 ,p_unit_of_measure_tbl(i)
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;
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;
1984 IF l_msg_count = 1 THEN
1985 PA_INTERFACE_UTILS_PUB.get_messages
1986 (p_encoded => FND_API.G_TRUE,
1987 p_msg_index => 1,
1988 p_msg_count => l_msg_count,
1989 p_msg_data => l_msg_data,
1990 p_data => l_data,
1991 p_msg_index_out => l_msg_index_out);
1992 x_msg_data := l_data;
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
2011 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2012 END Insert_Bulk_Rows_Res;
2013
2014 /**************************************************************************************
2015 REFRESH_PERIOD_DENORM: This api does complete rollup of period denorm. It completely
2016 refreshes the table for entered and rollup records both. It does following
2017 - delete all rolled up records from denorm table.
2018 - refreshes the amount at user entered level.
2019 - insert parent records with amounts.
2020 ***************************************************************************************/
2021
2022 PROCEDURE REFRESH_PERIOD_DENORM(p_budget_version_id IN NUMBER
2023 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2024 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2025 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2026
2027 l_res_id_tbl l_ra_id_tbl_typ;
2028 l_par_id_tbl l_par_id_tbl_typ;
2029 l_unit_of_measure_tbl l_unit_of_measure_tbl_typ;
2030
2031 /* Declare the local variables. to get the values of OUT parameters. */
2032
2033 l_period_profile_id pa_budget_versions.PERIOD_PROFILE_ID%TYPE;
2034 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
2035 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
2036 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
2037 l_uncat_rlm_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
2038 l_project_id pa_projects.project_id%TYPE;
2039 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
2040 l_data_source VARCHAR2(20);
2041
2042 l_msg_count NUMBER := 0;
2043 l_data VARCHAR2(2000);
2044 l_msg_data VARCHAR2(2000);
2045 l_msg_index_out NUMBER;
2046 l_return_status VARCHAR2(2000);
2047 l_debug_mode VARCHAR2(30);
2048
2049 l_curr_rollup_level NUMBER := 0;
2050
2051 L_INSERTING_RES_GROUP_LEVEL boolean := false;
2052 L_INSERTING_TASK_LEVEL boolean := false;
2053 L_INSERTING_PARENT_TASK_LEVEL boolean := false;
2054
2055 l_proj_currency_code pa_projects_all.project_currency_code%TYPE;
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');
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 ';
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. */
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');
2088 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
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
2098 populate_local_vars(p_budget_version_id => p_budget_version_id,
2099 x_project_id => l_project_id,
2100 x_resource_list_id => l_resource_list_id,
2101 x_uncat_flag => l_uncat_flag,
2102 x_uncat_rlm_id => l_uncat_rlm_id,
2103 x_rl_group_type_id => l_rl_group_type_id,
2104 x_planning_level => l_planning_level,
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
2114 SELECT 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
2124 /* #2801522: Getting the project currency code for storing in the 'QAUNTITY' record. */
2125
2126 SELECT project_currency_code
2127 INTO l_proj_currency_code
2128 FROM pa_projects_all
2129 WHERE project_id = l_project_id;
2130
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
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
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
2153 ,p_data_source => l_data_source
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.
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)
2165 */
2166
2167 IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
2168 l_curr_rollup_level := 0;
2169 ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP THEN
2170 l_curr_rollup_level := 1;
2171 ELSE /* planning level is lowest task or top and lowest */
2172 select max(wbs_level)
2173 into l_curr_rollup_level
2174 from pa_tasks
2175 where project_id = l_project_id;
2176 END IF;
2177
2178 IF l_uncat_flag <> 'Y' THEN /* resource list is attached */
2179 l_curr_rollup_level := l_curr_rollup_level + 1;
2180 IF l_rl_group_type_id <> 0 THEN /* if resource attached is grouped */
2181 L_INSERTING_RES_GROUP_LEVEL := true;
2182 l_curr_rollup_level := l_curr_rollup_level + 1;
2183 ELSE
2184 L_INSERTING_TASK_LEVEL := true;
2185 END IF;
2186 ELSE
2187 L_INSERTING_PARENT_TASK_LEVEL := true;
2188 END IF;
2189
2190 /* If the amount_type_code is QUANTITY in pa_proj_periods_denorm, then the records
2191 with unit_of_measure as that in the temp table are rolled up. Hence the temp
2192 table should contain the uom as HOURS for the first user_enetered_level so that
2193 the Quantity in the next upper level records are automatically inserted correctly. */
2194
2195 /* Perform the following steps in a loop until there are no records in the temp
2196 table PA_FP_RA_MAP_TMP */
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
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
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
2226 INSERT INTO PA_PROJ_PERIODS_DENORM(
2227 PROJECT_ID
2228 ,BUDGET_VERSION_ID
2229 ,RESOURCE_ASSIGNMENT_ID
2230 ,PARENT_ASSIGNMENT_ID
2231 ,OBJECT_ID
2232 ,OBJECT_TYPE_CODE
2233 ,PERIOD_PROFILE_ID
2234 ,AMOUNT_TYPE_CODE
2235 ,AMOUNT_SUBTYPE_CODE
2236 ,AMOUNT_TYPE_ID
2237 ,AMOUNT_SUBTYPE_ID
2238 ,CURRENCY_TYPE
2239 ,CURRENCY_CODE
2240 ,PRECEDING_periods_amount
2241 ,SUCCEEDING_periods_amount
2242 ,PRIOR_PERIOD_AMOUNT
2243 ,PERIOD_AMOUNT1
2244 ,PERIOD_AMOUNT2
2245 ,PERIOD_AMOUNT3
2246 ,PERIOD_AMOUNT4
2247 ,PERIOD_AMOUNT5
2248 ,PERIOD_AMOUNT6
2249 ,PERIOD_AMOUNT7
2250 ,PERIOD_AMOUNT8
2251 ,PERIOD_AMOUNT9
2252 ,PERIOD_AMOUNT10
2253 ,PERIOD_AMOUNT11
2254 ,PERIOD_AMOUNT12
2255 ,PERIOD_AMOUNT13
2256 ,PERIOD_AMOUNT14
2257 ,PERIOD_AMOUNT15
2258 ,PERIOD_AMOUNT16
2259 ,PERIOD_AMOUNT17
2260 ,PERIOD_AMOUNT18
2261 ,PERIOD_AMOUNT19
2262 ,PERIOD_AMOUNT20
2263 ,PERIOD_AMOUNT21
2264 ,PERIOD_AMOUNT22
2265 ,PERIOD_AMOUNT23
2266 ,PERIOD_AMOUNT24
2267 ,PERIOD_AMOUNT25
2268 ,PERIOD_AMOUNT26
2269 ,PERIOD_AMOUNT27
2270 ,PERIOD_AMOUNT28
2271 ,PERIOD_AMOUNT29
2272 ,PERIOD_AMOUNT30
2273 ,PERIOD_AMOUNT31
2274 ,PERIOD_AMOUNT32
2275 ,PERIOD_AMOUNT33
2276 ,PERIOD_AMOUNT34
2277 ,PERIOD_AMOUNT35
2278 ,PERIOD_AMOUNT36
2279 ,PERIOD_AMOUNT37
2280 ,PERIOD_AMOUNT38
2281 ,PERIOD_AMOUNT39
2282 ,PERIOD_AMOUNT40
2283 ,PERIOD_AMOUNT41
2284 ,PERIOD_AMOUNT42
2285 ,PERIOD_AMOUNT43
2286 ,PERIOD_AMOUNT44
2287 ,PERIOD_AMOUNT45
2288 ,PERIOD_AMOUNT46
2289 ,PERIOD_AMOUNT47
2290 ,PERIOD_AMOUNT48
2291 ,PERIOD_AMOUNT49
2292 ,PERIOD_AMOUNT50
2293 ,PERIOD_AMOUNT51
2294 ,PERIOD_AMOUNT52
2295 ,LAST_UPDATE_DATE
2296 ,LAST_UPDATED_BY
2297 ,CREATION_DATE
2298 ,CREATED_BY
2299 ,LAST_UPDATE_LOGIN
2300 )
2301 SELECT ppd.project_id
2302 ,ppd.budget_version_id
2303 ,tmp.parent_assignment_id resource_assignment_id
2304 ,NULL -- parent_Assignment_id
2305 ,tmp.parent_assignment_id -- #2723515: object_id should be the same as ra id
2306 ,object_type_code
2307 ,period_profile_id
2308 ,amount_type_code
2309 ,amount_subtype_code
2310 ,amount_type_id
2311 ,amount_subtype_id
2312 ,currency_type
2313 ,decode(amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2314 l_proj_currency_code,currency_code) --#2801522:For Qty, store Proj Curr Code
2315 ,sum(nvl(preceding_periods_amount,0))
2316 ,sum(nvl(succeeding_periods_amount,0))
2317 ,sum(nvl(prior_period_amount,0))
2318 ,sum(nvl(period_amount1,0)) period_amount1
2319 ,sum(nvl(period_amount2,0)) period_amount2
2320 ,sum(nvl(period_amount3,0)) period_amount3
2321 ,sum(nvl(period_amount4,0)) period_amount4
2322 ,sum(nvl(period_amount5,0)) period_amount5
2323 ,sum(nvl(period_amount6,0)) period_amount6
2324 ,sum(nvl(period_amount7,0)) period_amount7
2325 ,sum(nvl(period_amount8,0)) period_amount8
2326 ,sum(nvl(period_amount9,0)) period_amount9
2327 ,sum(nvl(period_amount10,0)) period_amount10
2328 ,sum(nvl(period_amount11,0)) period_amount11
2329 ,sum(nvl(period_amount12,0)) period_amount12
2330 ,sum(nvl(period_amount13,0)) period_amount13
2331 ,sum(nvl(period_amount14,0)) period_amount14
2332 ,sum(nvl(period_amount15,0)) period_amount15
2333 ,sum(nvl(period_amount16,0)) period_amount16
2334 ,sum(nvl(period_amount17,0)) period_amount17
2335 ,sum(nvl(period_amount18,0)) period_amount18
2336 ,sum(nvl(period_amount19,0)) period_amount19
2337 ,sum(nvl(period_amount20,0)) period_amount20
2338 ,sum(nvl(period_amount21,0)) period_amount21
2339 ,sum(nvl(period_amount22,0)) period_amount22
2340 ,sum(nvl(period_amount23,0)) period_amount23
2341 ,sum(nvl(period_amount24,0)) period_amount24
2342 ,sum(nvl(period_amount25,0)) period_amount25
2343 ,sum(nvl(period_amount26,0)) period_amount26
2344 ,sum(nvl(period_amount27,0)) period_amount27
2345 ,sum(nvl(period_amount28,0)) period_amount28
2346 ,sum(nvl(period_amount29,0)) period_amount29
2347 ,sum(nvl(period_amount30,0)) period_amount30
2348 ,sum(nvl(period_amount31,0)) period_amount31
2349 ,sum(nvl(period_amount32,0)) period_amount32
2350 ,sum(nvl(period_amount33,0)) period_amount33
2351 ,sum(nvl(period_amount34,0)) period_amount34
2352 ,sum(nvl(period_amount35,0)) period_amount35
2353 ,sum(nvl(period_amount36,0)) period_amount36
2354 ,sum(nvl(period_amount37,0)) period_amount37
2355 ,sum(nvl(period_amount38,0)) period_amount38
2356 ,sum(nvl(period_amount39,0)) period_amount39
2357 ,sum(nvl(period_amount40,0)) period_amount40
2358 ,sum(nvl(period_amount41,0)) period_amount41
2359 ,sum(nvl(period_amount42,0)) period_amount42
2360 ,sum(nvl(period_amount43,0)) period_amount43
2361 ,sum(nvl(period_amount44,0)) period_amount44
2362 ,sum(nvl(period_amount45,0)) period_amount45
2363 ,sum(nvl(period_amount46,0)) period_amount46
2364 ,sum(nvl(period_amount47,0)) period_amount47
2365 ,sum(nvl(period_amount48,0)) period_amount48
2366 ,sum(nvl(period_amount49,0)) period_amount49
2367 ,sum(nvl(period_amount50,0)) period_amount50
2368 ,sum(nvl(period_amount51,0)) period_amount51
2369 ,sum(nvl(period_amount52,0)) period_amount52
2370 ,sysdate
2371 ,fnd_global.user_id
2372 ,sysdate
2373 ,fnd_global.user_id
2374 ,fnd_global.login_id
2375 FROM pa_fp_ra_map_tmp tmp,
2376 pa_proj_periods_denorm ppd
2377 WHERE tmp.resource_assignment_id = ppd.resource_assignment_id
2378 AND ppd.budget_version_id = p_budget_version_id -- performance bug 2802862
2379 AND ((currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
2380 (amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
2381 currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
2382 AND decode(ppd.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
2383 tmp.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
2384 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2385 GROUP BY tmp.parent_assignment_id, currency_type,
2386 currency_code, amount_type_code, amount_subtype_code,
2387 amount_type_id,amount_subtype_id,
2388 ppd.project_id ,ppd.budget_version_id,
2389 /*object_id,*/ object_type_code, period_profile_id ; -- bug 2740741
2390
2391 /*************** comment for the above change ************************
2392 Object_id is popultated same as resource_assignment_id.The intention
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
2402 /* set the exist condition */
2403 l_curr_rollup_level := l_curr_rollup_level - 1;
2404
2405 END LOOP; /* End Loop for the whole cycle of insertion of records */
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
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
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;
2431 IF l_msg_count = 1 THEN
2432 PA_INTERFACE_UTILS_PUB.get_messages
2433 (p_encoded => FND_API.G_TRUE,
2434 p_msg_index => 1,
2435 p_msg_count => l_msg_count,
2436 p_msg_data => l_msg_data,
2437 p_data => l_data,
2438 p_msg_index_out => l_msg_index_out);
2439 x_msg_data := l_data;
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
2458 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2459 END REFRESH_PERIOD_DENORM;
2460
2461
2462 /***********************************************************************************************
2463 UPDATE_DENORM_PARENT_ASSIGN_ID: This procedure updates the Parent Assignment ID on the
2464 pa_proj_periods_denorm table.
2465 ***********************************************************************************************/
2466 PROCEDURE UPDATE_DENORM_PARENT_ASSIGN_ID(
2467 p_budget_version_id IN NUMBER
2468 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2469 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2470 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2471
2472 l_msg_count NUMBER := 0;
2473 l_data VARCHAR2(2000);
2474 l_msg_data VARCHAR2(2000);
2475 l_msg_index_out NUMBER;
2476 l_return_status VARCHAR2(2000);
2477 l_debug_mode VARCHAR2(30);
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');
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
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 =
2504 (SELECT 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;
2514
2515 EXCEPTION
2516 WHEN OTHERS THEN
2517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2518 x_msg_count := 1;
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
2528 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2529 END UPDATE_DENORM_PARENT_ASSIGN_ID;
2530
2531 /***********************************************************************************************
2532 INSERT_MISSING_RES_PARENTS: This api creates missing parents for the records in input
2533 temp table. For newly created parents it also updates the parent assignment id.This api will
2534 just create the records and will not update the amounts.
2535 ***********************************************************************************************/
2536
2537 PROCEDURE INSERT_MISSING_RES_PARENTS(p_budget_version_id IN NUMBER
2538 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2539 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2540 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2541
2542 /* Added for the bug #2622594. */
2543 CURSOR cur_parent_ra_id(c_budget_version_id IN NUMBER
2544 ,c_project_id IN NUMBER ) IS --bug#2708524
2545 SELECT pra1.resource_assignment_id child_ra_id , pra2.resource_assignment_id parent_ra_id
2546 FROM pa_resource_assignments pra1,
2547 pa_resource_assignments pra2,
2548 pa_resource_list_members prlm
2549 WHERE pra1.resource_list_member_id = prlm.resource_list_member_id
2550 AND pra2.resource_list_member_id = prlm.parent_member_id
2551 AND pra1.task_id = pra2.task_id
2552 AND pra2.budget_version_id = c_budget_version_id
2553 AND pra1.budget_version_id = c_budget_version_id
2554 AND pra2.project_id = c_project_id --bug#2708524
2555 AND pra1.project_id = c_project_id --bug#2771574
2556 AND pra1.resource_list_member_id <> 0
2557 AND pra1.parent_assignment_id IS NULL /* manokuma: added during unit testing */
2558 AND pra1.resource_assignment_id IN
2559 (SELECT resource_assignment_id FROM pa_fp_ra_map_tmp);
2560
2561 /* #2697999: Modified the below cursor to fetch the uom and track_as_labor_flag for
2562 the Resource group. If the Track as labor flag is 'Y', only then the UOM
2563 is populated as 'HOURS' for the res grp, else it is populated as NULL.
2564 For all the other rolled up records the uom is 'HOURS' and track as labor flag
2565 is 'Y'. */
2566
2567 CURSOR cur_parent_res_rec(c_budget_version_id IN NUMBER
2568 ,c_project_id IN NUMBER ) IS --bug#2708524
2569 SELECT TASK_ID
2570 ,prlm.PARENT_MEMBER_ID RESOURCE_LIST_MEMBER_ID
2571 ,max(decode(parent_prlm.track_as_labor_flag,'Y',
2572 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
2573 ,NULL)) unit_of_measure -- Added for bug #2697999
2574 ,max(parent_prlm.track_as_labor_flag) track_as_labor_flag
2575 -- Added for bug #2697999
2576 FROM pa_resource_assignments pra
2577 ,pa_resource_list_members prlm
2578 ,pa_resource_list_members parent_prlm
2579 WHERE pra.project_id = c_project_id --bug#2708524
2580 AND pra.budget_version_id = c_budget_version_id
2581 AND pra.resource_list_member_id <> 0
2582 AND pra.resource_list_member_id = prlm.resource_list_member_id
2583 AND prlm.parent_member_id = parent_prlm.resource_list_member_id
2584 AND prlm.parent_member_id is not null
2585 AND resource_assignment_id in
2586 (SELECT resource_assignment_id FROM pa_fp_ra_map_tmp)
2587 GROUP BY pra.task_id, prlm.parent_member_id;
2588
2589 CURSOR cur_task_rec(c_budget_version_id IN NUMBER
2590 ,c_project_id IN NUMBER) IS --bug#2708524
2591 SELECT task_id
2592 FROM pa_resource_assignments pra
2593 WHERE pra.budget_version_id = c_budget_version_id
2594 AND pra.project_id = c_project_id
2595 AND pra.resource_list_member_id <> 0
2596 AND pra.resource_assignment_id IN
2597 (SELECT resource_assignment_id FROM pa_fp_ra_map_tmp)
2598 GROUP BY task_id;
2599
2600 CURSOR cur_parent_task_rec(c_curr_wbs_level IN NUMBER
2601 ,c_budget_version_id IN NUMBER --bug#2708524
2602 ,c_project_id IN NUMBER) IS --bug#2708524
2603 SELECT PARENT_TASK_ID TASK_ID
2604 FROM pa_resource_assignments pra
2605 ,pa_tasks pt
2606 WHERE pra.resource_assignment_id IN
2607 (select resource_assignment_id FROM pa_fp_ra_map_tmp )
2608 AND pra.project_id = c_project_id --bug#2708524
2609 AND pra.budget_version_id = c_budget_version_id --bug#2708524
2610 AND pra.task_id = pt.task_id
2611 AND pt.parent_task_id IS NOT NULL
2612 AND pt.wbs_level = c_curr_wbs_level
2613 GROUP BY pt.parent_task_id;
2614
2615 l_continue_processing_flag VARCHAR2(1);
2616 l_uncat_rlm_id pa_resource_assignments.RESOURCE_LIST_MEMBER_ID%TYPE;
2617 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
2618 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
2619 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
2620 l_project_id pa_projects.project_id%TYPE;
2621 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
2622
2623 l_ra_id_tbl l_ra_id_tbl_typ;
2624 l_parent_res_id_tbl l_ra_id_tbl_typ;
2625 l_parent_ra_id_tbl l_par_id_tbl_typ;
2626 l_task_id_tbl l_task_id_tbl_typ;
2627 l_res_list_mem_id_tbl l_res_list_mem_id_tbl_typ;
2628 l_proj_count_rec NUMBER;
2629 l_proj_ra_id pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE;
2630 l_unit_of_measure_tbl l_unit_of_measure_tbl_typ;
2631 l_track_as_labor_flag_tbl l_track_as_labor_flag_tbl_typ;
2632
2633 l_msg_count NUMBER := 0;
2634 l_data VARCHAR2(2000);
2635 l_msg_data VARCHAR2(2000);
2636 l_msg_index_out NUMBER;
2637 l_return_status VARCHAR2(2000);
2638 l_debug_mode VARCHAR2(30);
2639
2640 l_curr_wbs_level NUMBER;
2641
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');
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 ';
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. */
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');
2674 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
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
2684 populate_local_vars(p_budget_version_id => p_budget_version_id,
2685 x_project_id => l_project_id,
2686 x_resource_list_id => l_resource_list_id,
2687 x_uncat_flag => l_uncat_flag,
2688 x_uncat_rlm_id => l_uncat_rlm_id,
2689 x_rl_group_type_id => l_rl_group_type_id,
2690 x_planning_level => l_planning_level,
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
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
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
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
2725 resource level records. Using a cursor instead of a direct update. */
2726
2727 /* UPDATE pa_resource_assignments pra1
2728 SET parent_assignment_id =
2729 (SELECT resource_assignment_id
2730 FROM pa_resource_assignments pra2, pa_resource_list_members prlm
2731 WHERE pra1.resource_list_member_id = prlm.resource_list_member_id
2732 AND pra2.resource_list_member_id = prlm.parent_member_id
2733 AND pra1.task_id = pra2.task_id
2734 AND pra2.budget_version_id = p_budget_version_id)
2735 WHERE budget_version_id = p_budget_version_id
2736 AND resource_list_member_id <> 0
2737 AND parent_assignment_id IS NULL -- manokuma: added during unit testing
2738 AND resource_assignment_id in
2739 (select resource_assignment_id from pa_fp_ra_map_tmp)
2740 RETURNING pra1.resource_assignment_id, pra1.parent_assignment_id
2741 BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl; */
2742
2743 OPEN cur_parent_ra_id(p_budget_version_id,l_project_id);
2744
2745 FETCH cur_parent_ra_id
2746 BULK COLLECT INTO l_ra_id_tbl, l_parent_ra_id_tbl;
2747
2748 IF nvl(l_ra_id_tbl.last,0) > 0 THEN
2749
2750 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
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
2760 END IF;
2761
2762 CLOSE cur_parent_ra_id;
2763
2764 IF nvl(l_ra_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
2765
2766 /* Delete the records from pa_fp_ra_map_tmp table where Resource Assignments are
2767 present in the PL/SQL table returned and the Parent ID is NOT NULL. */
2768
2769 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
2770
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
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);
2791
2792 /* #2697999: Added two more PL/SQL tables to fetch the uom and track as labor flag */
2793 FETCH cur_parent_res_rec BULK COLLECT INTO
2794 l_task_id_tbl, l_res_list_mem_id_tbl,l_unit_of_measure_tbl,l_track_as_labor_flag_tbl;
2795 EXIT WHEN nvl(l_task_id_tbl.last,0) <= 0; /* manokuma changes during ut cur_parent_res_rec%NOTFOUND; */
2796
2797 IF nvl(l_task_id_tbl.last,0) > 0 THEN
2798
2799 FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
2800
2801 INSERT INTO pa_resource_assignments
2802 (RESOURCE_ASSIGNMENT_ID
2803 ,BUDGET_VERSION_ID
2804 ,PROJECT_ID
2805 ,TASK_ID
2806 ,RESOURCE_LIST_MEMBER_ID
2807 ,LAST_UPDATE_DATE
2808 ,LAST_UPDATED_BY
2809 ,CREATION_DATE
2810 ,CREATED_BY
2811 ,LAST_UPDATE_LOGIN
2812 ,UNIT_OF_MEASURE
2813 ,TRACK_AS_LABOR_FLAG
2814 ,PROJECT_ASSIGNMENT_ID
2815 ,RESOURCE_ASSIGNMENT_TYPE )
2816 VALUES
2817 (PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL
2818 ,p_budget_version_id
2819 ,l_project_id
2820 ,l_task_id_tbl(i)
2821 ,l_res_list_mem_id_tbl(i)
2822 ,sysdate
2823 ,fnd_global.user_id
2824 ,sysdate
2825 ,fnd_global.user_id
2826 ,fnd_global.login_id
2827 ,l_unit_of_measure_tbl(i)
2828 ,l_track_as_labor_flag_tbl(i)
2829 ,-1
2830 ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
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
2840
2841 /* Insert the parent returned into l_parent_ra_id_tbl
2842 into the Temp table pa_fp_ra_map_tmp. */
2843
2844 IF nvl(l_parent_res_id_tbl.last,0) > 0 THEN
2845
2846 FORALL i in l_parent_res_id_tbl.first..l_parent_res_id_tbl.last
2847
2848 INSERT INTO pa_fp_ra_map_tmp
2849 (RESOURCE_ASSIGNMENT_ID)
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
2859 CLOSE cur_parent_res_rec; /* M21-08: a wrong cursor was closed here */
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
2869
2870 /* If resource list is not grouped (group_resource_type_id = 0) then the temp table will
2871 contain only resource level records. For these records we need to insert task level
2872 records only. Following step is common for resource group level as well as resource level. */
2873
2874 IF l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
2875
2876 /* Creating a Loop to continuously stamp the parent_assignment_id. By the end of this
2877 loop, pa_fp_ra_map_tmp table will contain only task level records. */
2878 l_task_id_tbl.delete; /* Deleting records from TASKID pl/sql table so that it can be used later. */
2879
2880 LOOP
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
2890 SET parent_assignment_id =
2891 (SELECT resource_assignment_id
2892 FROM pa_resource_assignments pra2
2893 WHERE pra2.task_id = pra1.task_id
2894 AND pra2.resource_list_member_id = 0
2895 AND pra2.budget_version_id = p_budget_version_id)
2896 WHERE resource_assignment_id in
2897 (select resource_assignment_id from pa_fp_ra_map_tmp)
2898 AND pra1.resource_list_member_id <> 0
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
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. */
2910
2911 IF nvl(l_ra_id_tbl.last,0) > 0 THEN
2912
2913 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
2914
2915 DELETE FROM pa_fp_ra_map_tmp
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
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
2934 FETCH cur_task_rec BULK COLLECT INTO l_task_id_tbl;
2935 EXIT WHEN nvl(l_task_id_tbl.last,0) <= 0; /* manokuma changed during ut cur_task_rec%NOTFOUND; */
2936
2937 IF nvl(l_task_id_tbl.last,0) > 0 THEN
2938
2939 FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
2940 INSERT INTO PA_RESOURCE_ASSIGNMENTS
2941 (RESOURCE_ASSIGNMENT_ID
2942 ,BUDGET_VERSION_ID
2943 ,PROJECT_ID
2944 ,TASK_ID
2945 ,RESOURCE_LIST_MEMBER_ID
2946 ,LAST_UPDATE_DATE
2947 ,LAST_UPDATED_BY
2948 ,CREATION_DATE
2949 ,CREATED_BY
2950 ,LAST_UPDATE_LOGIN
2951 ,UNIT_OF_MEASURE
2952 ,TRACK_AS_LABOR_FLAG
2953 ,PROJECT_ASSIGNMENT_ID
2954 ,RESOURCE_ASSIGNMENT_TYPE )
2955 VALUES
2956 (PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL
2957 ,p_budget_version_id
2958 ,l_project_id
2959 ,l_task_id_tbl(i)
2960 ,0 -- res_list_mem_id is 0 for tasks
2961 ,sysdate
2962 ,fnd_global.user_id
2963 ,sysdate
2964 ,fnd_global.user_id
2965 ,fnd_global.login_id
2966 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
2967 ,'Y' -- Modified for #2697999
2968 ,-1
2969 ,PA_FP_CONSTANTS_PKG.G_ROLLED_UP)
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
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. */
2981
2982 IF nvl(l_ra_id_tbl.last,0) > 0 THEN
2983
2984 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
2985
2986 INSERT INTO pa_fp_ra_map_tmp(resource_assignment_id)
2987 VALUES (l_ra_id_tbl(i));
2988
2989 END IF;
2990
2991 CLOSE cur_task_rec;
2992
2993 END LOOP;
2994
2995 END IF; /* l_planning_level <> PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT*/
2996
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
3006 l_task_id_tbl.delete;
3007
3008 BEGIN
3009
3010 SELECT 'Y'
3011 INTO l_continue_processing_flag
3012 FROM dual
3013 WHERE EXISTS (SELECT 1
3014 FROM pa_fp_ra_map_tmp);
3015
3016 EXCEPTION
3017
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
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
3036
3037 /* If task planning level for the version is not 'TOP_TASK'or 'PROJECT' THEN Insert middle
3038 level tasks and top task records into PA_RESOURCE_ASSIGNMENTS. */
3039
3040 l_curr_wbs_level := 0;
3041 select max(wbs_level)
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
3051 LOOP
3052 EXIT WHEN l_curr_wbs_level = 0; /* manokuma: changed during ut cur_parent_task_rec%NOTFOUND; */
3053
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
3063 l_ra_id_tbl.delete;
3064 l_parent_ra_id_tbl.delete;
3065
3066 UPDATE /*+ INDEX(pra1 PA_RESOURCE_ASSIGNMENTS_U1)*/ pa_resource_assignments pra1 --Bug 2782166
3067 SET parent_assignment_id =
3068 (select resource_assignment_id
3069 from pa_resource_assignments pra2
3070 ,pa_tasks t
3071 where pra2.task_id = t.parent_task_id
3072 and pra1.task_id = t.task_id
3073 and pra2.budget_version_id = p_budget_version_id) /* manokuma: fixed during ut */
3074 WHERE resource_assignment_id in
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
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. */
3086
3087 IF nvl(l_ra_id_tbl.last,0) > 0 THEN
3088 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3089
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;
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;
3108 OPEN cur_parent_task_rec(l_curr_wbs_level,p_budget_version_id,l_project_id);
3109
3110 FETCH cur_parent_task_rec BULK COLLECT INTO l_task_id_tbl;
3111
3112 IF nvl(l_task_id_tbl.last,0) > 0 THEN -- if parent records found
3113 FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
3114 INSERT INTO PA_RESOURCE_ASSIGNMENTS
3115 (RESOURCE_ASSIGNMENT_ID
3116 ,BUDGET_VERSION_ID
3117 ,PROJECT_ID
3118 ,TASK_ID
3119 ,RESOURCE_LIST_MEMBER_ID
3120 ,LAST_UPDATE_DATE
3121 ,LAST_UPDATED_BY
3122 ,CREATION_DATE
3123 ,CREATED_BY
3124 ,LAST_UPDATE_LOGIN
3125 ,UNIT_OF_MEASURE
3126 ,TRACK_AS_LABOR_FLAG
3127 ,PROJECT_ASSIGNMENT_ID
3128 ,RESOURCE_ASSIGNMENT_TYPE )
3129 VALUES
3130 (PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL
3131 ,p_budget_version_id
3132 ,l_project_id
3133 ,l_task_id_tbl(i)
3134 ,0 -- res_list_mem_id is 0 for tasks
3135 ,sysdate
3136 ,fnd_global.user_id
3137 ,sysdate
3138 ,fnd_global.user_id
3139 ,fnd_global.login_id
3140 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
3141 ,'Y' -- Modified for #2697999
3142 ,-1
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
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
3154 Task record, there is no parent found and hence the above INSERT will not be executed.
3155 The PL/SQL table l_ra_id_tbl will be holding the previous value i.e. one task lower than
3156 the Top task.
3157 If the Insert into the Temp Table is done outside the IF condition irrespective of the
3158 above Insert, then the Lower level task record is again inserted into the Temp Table
3159 because of which the update statement to update the Parent Assignment for the Top Task
3160 level record is executed even for the lower task and the amounts are not updated for
3161 the Top Task record because of wrong stamping of the parent assignment id on the lower
3162 level task record. */
3163
3164 /* Insert the newly generated resource assignment ids into pa_fp_ra_map_tmp table. */
3165
3166 IF nvl(l_ra_id_tbl.last,0) > 0 THEN
3167
3168 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
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
3178 END IF;
3179
3180 END IF; -- if parent records found
3181
3182 l_curr_wbs_level := l_curr_wbs_level - 1;
3183
3184 CLOSE cur_parent_task_rec; /* manokuma: during ut review moved this to inside loop */
3185
3186 END LOOP; /* End of the Repetitive loop. */
3187
3188 END IF; /* l_planning_level NOT IN (PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP,PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT) */
3189
3190 END IF; /* l_continue_procesing_flag = 'Y'*/
3191
3192
3193 /* By now, all parent task level records would have been inserted into PA_RESOURCE_ASSIGNMENTS
3194 table with parent_assignment_id stamped correctly. PA_RA_MAP temp table will contain only
3195 top task level records. In case planning level is project or top task and resource list is
3196 attached then pa_fp_ra_map_tmp table will contain resource/resource group level records with
3197 task_id as 0. */
3198
3199 /* For all the records in pa_fp_ra_map_tmp table we need to insert project level records.
3200 Check if a Project level record exists for the Budget Version */
3201
3202 /* Bug 2647043 : This needs to be done only when planning level is not project and resource
3203 attached is not categorized as in this case the user entered record is project
3204 level record.
3205 */
3206
3207 /* Bug #2597846: The fix mentioned above for the bug #2647043 is being modified.
3208 The Project Level record should not be created, if the Planning Level is 'Project'
3209 and the Uncat Flag is 'Y'. In all other cases, the Project Level Record has to be
3210 created. */
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
3220 NULL;
3221
3222 ELSE
3223
3224 BEGIN
3225 SELECT resource_assignment_id
3226 INTO l_proj_ra_id
3227 FROM pa_resource_assignments
3228 WHERE budget_version_id = p_budget_version_id
3229 AND task_id = 0
3230 AND resource_list_member_id IN (l_uncat_rlm_id,0);
3231
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
3241 INSERT INTO pa_resource_assignments
3242 (RESOURCE_ASSIGNMENT_ID
3243 ,BUDGET_VERSION_ID
3244 ,PROJECT_ID
3245 ,TASK_ID
3246 ,RESOURCE_LIST_MEMBER_ID
3247 ,LAST_UPDATE_DATE
3248 ,LAST_UPDATED_BY
3249 ,CREATION_DATE
3250 ,CREATED_BY
3251 ,LAST_UPDATE_LOGIN
3252 ,UNIT_OF_MEASURE
3253 ,TRACK_AS_LABOR_FLAG
3254 ,PROJECT_ASSIGNMENT_ID
3255 ,RESOURCE_ASSIGNMENT_TYPE)
3256 VALUES
3257 (PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL
3258 ,p_budget_version_id
3259 ,l_project_id
3260 ,0
3261 ,0 -- res_list_mem_id is 0 for tasks
3262 ,sysdate
3263 ,fnd_global.user_id
3264 ,sysdate
3265 ,fnd_global.user_id
3266 ,fnd_global.login_id
3267 ,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS -- Modified for #2697999
3268 ,'Y' -- Modified for #2697999
3269 ,-1
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
3279 END;
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
3289 UPDATE PA_RESOURCE_ASSIGNMENTS
3290 SET parent_assignment_id = l_proj_ra_id
3291 WHERE resource_assignment_id IN
3292 (SELECT resource_assignment_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
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
3308 l_msg_count := FND_MSG_PUB.count_msg;
3309 IF l_msg_count = 1 THEN
3310 PA_INTERFACE_UTILS_PUB.get_messages
3311 (p_encoded => FND_API.G_TRUE,
3312 p_msg_index => 1,
3313 p_msg_count => l_msg_count,
3314 p_msg_data => l_msg_data,
3315 p_data => l_data,
3316 p_msg_index_out => l_msg_index_out);
3317 x_msg_data := l_data;
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
3336 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3337 END INSERT_MISSING_RES_PARENTS;
3338
3339 /***********************************************************************************************
3340 ROLLUP_RES_ASSIGNMENT_AMOUNTS: This API will take input from PA_FP_ROLLUP_TMP table
3341 and will update rollup amount for each level in pa_resource_assignments table.
3342 Before this API is called, INSERT_MISSING_RES_PARENTS would have created parents for all the
3343 records affected in PA_RESOURCE_ASSIGNMENTS. This API will just rollup the amounts.
3344 Pre-requisite: For an existing element users should have populated old and new amount fields
3345 into the temp table PA_FP_ROLLUP_TMP before calling this API.
3346 For a new element (thru excel sheets) users will populate old as null and new
3347 as the current value.
3348 ***********************************************************************************************/
3349
3350 PROCEDURE ROLLUP_RES_ASSIGNMENT_AMOUNTS(p_budget_version_id IN NUMBER
3351 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3352 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3353 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3354
3355 /* Cursor to Select all data from PA_FP_ROLLUP_TMP table, grouped by resource_assignment_id
3356 and take sum of amount diffs (use nvl for new and old amounts) in project and project
3357 functional currencies. */
3358
3359 CURSOR c_res_amt_diffs IS
3360 SELECT resource_assignment_id
3361 ,sum(nvl(project_raw_cost,0) - nvl(old_proj_raw_cost,0)) project_raw_cost_diff
3362 ,sum(nvl(project_burdened_cost,0) - nvl(old_proj_burdened_cost,0)) project_burdened_cost_diff
3363 ,sum(nvl(project_revenue,0) - nvl(old_proj_revenue,0)) project_revenue_diff
3364 ,sum(nvl(projfunc_raw_cost,0) - nvl(old_projfunc_raw_cost,0)) projfunc_raw_cost_diff
3365 ,sum(nvl(projfunc_burdened_cost,0) - nvl(old_projfunc_burdened_cost,0)) projfunc_burdened_cost_diff
3366 ,sum(nvl(projfunc_revenue,0) - nvl(old_projfunc_revenue,0)) projfunc_revenue_diff
3367 ,sum(nvl(quantity,0) - nvl(old_quantity,0)) quantity_diff
3368 FROM PA_FP_ROLLUP_TMP
3369 GROUP BY resource_assignment_id;
3370
3371 l_proj_raw_cost_tbl l_proj_raw_cost_tbl_typ;
3372 l_proj_burd_cost_tbl l_proj_burd_cost_tbl_typ;
3373 l_proj_revenue_tbl l_proj_revenue_tbl_typ;
3374 l_projfunc_raw_cost_tbl l_projfunc_raw_cost_tbl_typ;
3375 l_projfunc_burd_cost_tbl l_projfunc_burd_cost_tbl_typ;
3376 l_projfunc_revenue_tbl l_projfunc_revenue_tbl_typ;
3377 l_quantity_tbl l_quantity_tbl_typ;
3378 l_uom_tbl l_unit_of_measure_tbl_typ;
3379 l_ra_id_tbl l_ra_id_tbl_typ;
3380 l_par_id_tbl l_par_id_tbl_typ;
3381 l_upd_user_entered_rec_flg VARCHAR2(1);
3382 l_upd_rec NUMBER;
3383
3384 l_msg_count NUMBER := 0;
3385 l_data VARCHAR2(2000);
3386 l_msg_data VARCHAR2(2000);
3387 l_msg_index_out NUMBER;
3388 l_return_status VARCHAR2(2000);
3389 l_debug_mode VARCHAR2(30);
3390
3391 l_index NUMBER; /* M21-AUG: added for correcting the exit condition for loop */
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');
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 ';
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
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');
3423 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
3424 END IF;
3425
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
3435 l_ra_id_tbl
3436 ,l_proj_raw_cost_tbl
3437 ,l_proj_burd_cost_tbl
3438 ,l_proj_revenue_tbl
3439 ,l_projfunc_raw_cost_tbl
3440 ,l_projfunc_burd_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
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. */
3452
3453 l_upd_user_entered_rec_flg := null;
3454
3455 LOOP
3456 if (l_upd_user_entered_rec_flg is null) then
3457 l_upd_user_entered_rec_flg := 'Y';
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
3467 IF nvl(l_ra_id_tbl.last,0) >= 1 THEN
3468
3469 IF l_upd_user_entered_rec_flg = 'Y' THEN
3470
3471 /* The flag l_updating_user_entered_flag is being used to populate the quantity
3472 correctly. If we are updating the User_Entered records, then we directly add
3473 the difference in quantity to the resource_assignment_quantity.
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
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
3485 adding all the amount diffs */
3486 UPDATE pa_resource_assignments
3487 SET TOTAL_PROJECT_RAW_COST = nvl(TOTAL_PROJECT_RAW_COST,0) + l_proj_raw_cost_tbl(i)
3488 ,TOTAL_PROJECT_BURDENED_COST = nvl(TOTAL_PROJECT_BURDENED_COST,0) + l_proj_burd_cost_tbl(i)
3489 ,TOTAL_PROJECT_REVENUE = nvl(TOTAL_PROJECT_REVENUE,0) + l_proj_revenue_tbl(i)
3490 ,TOTAL_PLAN_RAW_COST = nvl(TOTAL_PLAN_RAW_COST,0) + l_projfunc_raw_cost_tbl(i)
3491 ,TOTAL_PLAN_BURDENED_COST = nvl(TOTAL_PLAN_BURDENED_COST,0) + l_projfunc_burd_cost_tbl(i)
3492 ,TOTAL_PLAN_REVENUE = nvl(TOTAL_PLAN_REVENUE,0) + l_projfunc_revenue_tbl(i)
3493 ,TOTAL_PLAN_QUANTITY = nvl(TOTAL_PLAN_QUANTITY,0) + l_quantity_tbl(i)
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
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
3512 /* Update pa_resource_assignments for the records found in last step
3513 adding all the amount diffs */
3514 UPDATE pa_resource_assignments
3515 SET TOTAL_PROJECT_RAW_COST = nvl(TOTAL_PROJECT_RAW_COST,0) + l_proj_raw_cost_tbl(i)
3516 ,TOTAL_PROJECT_BURDENED_COST = nvl(TOTAL_PROJECT_BURDENED_COST,0) + l_proj_burd_cost_tbl(i)
3517 ,TOTAL_PROJECT_REVENUE = nvl(TOTAL_PROJECT_REVENUE,0) + l_proj_revenue_tbl(i)
3518 ,TOTAL_PLAN_RAW_COST = nvl(TOTAL_PLAN_RAW_COST,0) + l_projfunc_raw_cost_tbl(i)
3519 ,TOTAL_PLAN_BURDENED_COST = nvl(TOTAL_PLAN_BURDENED_COST,0) + l_projfunc_burd_cost_tbl(i)
3520 ,TOTAL_PLAN_REVENUE = nvl(TOTAL_PLAN_REVENUE,0) + l_projfunc_revenue_tbl(i)
3521 ,TOTAL_PLAN_QUANTITY = nvl(TOTAL_PLAN_QUANTITY,0) + decode(l_uom_tbl(i),
3522 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS,l_quantity_tbl(i),0)
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
3532 END IF;
3533
3534 END IF;
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
3544 l_ra_id_tbl.delete;
3545
3546 l_index := 1; -- initialize to zero.
3547
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
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);
3564
3565 -- Bug#2767271
3566 -- This re-assignment of table values is required to
3567 -- have consistency in the mapping of record in l_ra_id_tbl
3568 -- and other amount/quantity tables.For a project having
3569 -- tasks at not some wbs level this is required.
3570 -- Added the re-assignment of the uom tbl also.
3571
3572 l_proj_raw_cost_tbl(l_index) := l_proj_raw_cost_tbl(i) ;
3573 l_proj_burd_cost_tbl(l_index) := l_proj_burd_cost_tbl(i) ;
3574 l_proj_revenue_tbl(l_index) := l_proj_revenue_tbl(i) ;
3575 l_projfunc_raw_cost_tbl(l_index) := l_projfunc_raw_cost_tbl(i) ;
3576 l_projfunc_burd_cost_tbl(l_index) := l_projfunc_burd_cost_tbl(i);
3577 l_projfunc_revenue_tbl(l_index) := l_projfunc_revenue_tbl(i) ;
3578 l_quantity_tbl(l_index) := l_quantity_tbl(i) ;
3579 l_uom_tbl(l_index) := l_uom_tbl(i) ;
3580
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
3590 END LOOP;
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
3600 EXIT;
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;
3610
3611 EXCEPTION
3612 WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
3613 l_msg_count := FND_MSG_PUB.count_msg;
3614 IF l_msg_count = 1 THEN
3615 PA_INTERFACE_UTILS_PUB.get_messages
3616 (p_encoded => FND_API.G_TRUE,
3617 p_msg_index => 1,
3618 p_msg_count => l_msg_count,
3619 p_msg_data => l_msg_data,
3620 p_data => l_data,
3621 p_msg_index_out => l_msg_index_out);
3622 x_msg_data := l_data;
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
3641 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3642 END ROLLUP_RES_ASSIGNMENT_AMOUNTS;
3643
3644 /***********************************************************************************************
3645 INSERT_MISSING_PARENT_DENORM: This API will only insert the parent level records for all the
3646 records for which rollup API is called. It expects input in pa_fp_ra_map_tmp table. This API
3647 creates parent record for each currency type and amount type available for child level
3648 records.
3649 Prerequisite: The USER_ENTERED level records have to be updated with all amounts and
3650 currencies.
3651 Records have to be inserted into pa_fp_ra_map_tmp table. This API will be called
3652 only if there are some records in pa_fp_ra_map_tmp table.
3653 ***********************************************************************************************/
3654
3655 PROCEDURE INSERT_MISSING_PARENT_DENORM(p_budget_version_id IN NUMBER
3656 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3657 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3658 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3659
3660 l_msg_count NUMBER := 0;
3661 l_data VARCHAR2(2000);
3662 l_msg_data VARCHAR2(2000);
3663 l_msg_index_out NUMBER;
3664 l_return_status VARCHAR2(2000);
3665 l_debug_mode VARCHAR2(30);
3666
3667
3668 l_ra_id_tbl l_ra_id_tbl_typ;
3669 l_object_id_tbl l_object_id_tbl_typ;
3670 l_object_type_code_tbl l_obj_typ_code_tbl_typ;
3671 l_amount_type_code_tbl l_amount_type_code_tbl_typ;
3672 l_amount_subtype_code_tbl l_amount_subtype_code_tbl_typ;
3673 l_amount_type_id_tbl l_amount_type_id_tbl_typ;
3674 l_amount_subtype_id_tbl l_amount_subtype_id_tbl_typ;
3675 l_currency_type_tbl l_currency_type_tbl_typ;
3676 l_currency_code_tbl l_currency_code_tbl_typ;
3677
3678
3679 l_uncat_rlm_id pa_resource_assignments.RESOURCE_LIST_MEMBER_ID%TYPE;
3680 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
3681 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
3682 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
3683 l_project_id pa_projects.project_id%TYPE;
3684 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
3685 l_proj_currency_code pa_projects_all.project_currency_code%TYPE;
3686
3687 l_period_profile_id pa_budget_versions.period_profile_id%TYPE;
3688
3689 l_curr_rollup_level NUMBER := 0;
3690
3691 L_INSERTING_RES_GROUP_LEVEL boolean := false;
3692 L_INSERTING_TASK_LEVEL boolean := false;
3693 L_INSERTING_PARENT_TASK_LEVEL boolean := false;
3694
3695 CURSOR pd_denorm_par_cur(l_proj_currency_code IN VARCHAR2) IS
3696 SELECT distinct pra.parent_assignment_id,
3697 /* two resource assignment could share the same parent hence distinct */
3698 pra.parent_assignment_id, -- #2723515: object_id should be the ra id
3699 ppd.object_type_code,
3700 ppd.amount_type_code,
3701 ppd.amount_subtype_code,
3702 ppd.amount_type_id,
3703 ppd.amount_subtype_id,
3704 ppd.currency_type,
3705 decode(ppd.amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
3706 l_proj_currency_code,ppd.currency_code) --#2801522:For Qty, store Proj Curr Code
3707 FROM pa_resource_assignments pra,
3708 pa_proj_periods_denorm ppd
3709 WHERE ppd.budget_version_id = p_budget_version_id -- #2839138
3710 AND pra.resource_assignment_id = ppd.resource_assignment_id
3711 AND ppd.object_type_code = PA_FP_CONSTANTS_PKG.G_OBJECT_TYPE_RES_ASSIGNMENT -- #2839138
3712 AND ppd.object_id = ppd.resource_assignment_id -- #2839138
3713 AND ((ppd.currency_type <> PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION) OR
3714 (ppd.amount_type_code = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY AND
3715 ppd.currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION))
3716 AND pra.resource_assignment_id in
3717 (SELECT resource_assignment_id
3718 FROM pa_fp_ra_map_tmp)
3719 AND pra.parent_assignment_id IS NOT NULL;
3720
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');
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 ';
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. */
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');
3753 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
3754 END IF;
3755
3756 populate_local_vars(p_budget_version_id => p_budget_version_id,
3757 x_project_id => l_project_id,
3758 x_resource_list_id => l_resource_list_id,
3759 x_uncat_flag => l_uncat_flag,
3760 x_uncat_rlm_id => l_uncat_rlm_id,
3761 x_rl_group_type_id => l_rl_group_type_id,
3762 x_planning_level => l_planning_level,
3763 x_return_status => x_return_status,
3764 x_msg_count => x_msg_count,
3765 x_msg_data => x_msg_data);
3766
3767
3768 SELECT period_profile_id
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
3778 /* #2801522: Getting the project currency code for storing in the 'QAUNTITY' record. */
3779
3780 SELECT project_currency_code
3781 INTO l_proj_currency_code
3782 FROM pa_projects_all
3783 WHERE project_id = l_project_id;
3784
3785 /* set total number of levels in rollup.
3786 Total number of level = number of levels in WBS + resource group (in case resource list is grouped)
3787 + resource (in case resource list attached) + 1 (project level)
3788 */
3789
3790 IF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
3791 l_curr_rollup_level := 0;
3792 ELSIF l_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_TOP THEN
3793 l_curr_rollup_level := 1;
3794 ELSE /* planning level is lowest task or top and lowest */
3795 select max(wbs_level)
3796 into l_curr_rollup_level
3797 from pa_tasks
3798 where project_id = l_project_id;
3799 END IF;
3800
3801 IF l_uncat_flag <> 'Y' THEN /* resource list is attached */
3802 l_curr_rollup_level := l_curr_rollup_level + 1;
3803 IF l_rl_group_type_id <> 0 THEN /* if resource attached is grouped */
3804 L_INSERTING_RES_GROUP_LEVEL := true;
3805 l_curr_rollup_level := l_curr_rollup_level + 1;
3806 ELSE
3807 L_INSERTING_TASK_LEVEL := true;
3808 END IF;
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
3818 LOOP
3819 EXIT WHEN l_curr_rollup_level = 0;
3820
3821 /* Insert parents for the records in pa_fp_ra_map_tmp table. As of now the amounts are
3822 inserted as NULL. For each parent we need to insert records for each currency type
3823 and amount type for which their child exists. */
3824 INSERT_PARENT_REC_TMP(p_budget_version_id => p_budget_version_id
3825 ,PX_INSERTING_RES_GROUP_LEVEL => L_INSERTING_RES_GROUP_LEVEL
3826 ,PX_INSERTING_TASK_LEVEL => L_INSERTING_TASK_LEVEL
3827 ,PX_INSERTING_PARENT_TASK_LEVEL => L_INSERTING_PARENT_TASK_LEVEL
3828 ,p_curr_rollup_level => l_curr_rollup_level);
3829
3830 OPEN pd_denorm_par_cur(l_proj_currency_code);
3831
3832 FETCH pd_denorm_par_cur BULK COLLECT INTO
3833 l_ra_id_tbl
3834 ,l_object_id_tbl
3835 ,l_object_type_code_tbl
3836 ,l_amount_type_code_tbl
3837 ,l_amount_subtype_code_tbl
3838 ,l_amount_type_id_tbl
3839 ,l_amount_subtype_id_tbl
3840 ,l_currency_type_tbl
3841 ,l_currency_code_tbl;
3842
3843 CLOSE pd_denorm_par_cur;
3844
3845 IF nvl(l_ra_id_tbl.last,0) > 0 THEN
3846
3847 FORALL i in l_ra_id_tbl.first..l_ra_id_tbl.last
3848 INSERT INTO pa_proj_periods_denorm
3849 (RESOURCE_ASSIGNMENT_ID
3850 ,PROJECT_ID
3851 ,BUDGET_VERSION_ID
3852 ,PARENT_ASSIGNMENT_ID
3853 ,OBJECT_ID
3854 ,OBJECT_TYPE_CODE
3855 ,PERIOD_PROFILE_ID
3856 ,AMOUNT_TYPE_CODE
3857 ,AMOUNT_SUBTYPE_CODE
3858 ,AMOUNT_TYPE_ID
3859 ,AMOUNT_SUBTYPE_ID
3860 ,CURRENCY_TYPE
3861 ,CURRENCY_CODE
3862 ,LAST_UPDATE_DATE
3863 ,LAST_UPDATED_BY
3864 ,CREATION_DATE
3865 ,CREATED_BY
3866 ,LAST_UPDATE_LOGIN)
3867 VALUES
3868 (l_ra_id_tbl(i)
3869 ,l_project_id
3870 ,p_budget_version_id
3871 ,null
3872 ,l_object_id_tbl(i)
3873 ,l_object_type_code_tbl(i)
3874 ,l_period_profile_id
3875 ,l_amount_type_code_tbl(i)
3876 ,l_amount_subtype_code_tbl(i)
3877 ,l_amount_type_id_tbl(i)
3878 ,l_amount_subtype_id_tbl(i)
3879 ,l_currency_type_tbl(i)
3880 ,l_currency_code_tbl(i)
3881 ,sysdate
3882 ,fnd_global.user_id
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;
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;
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
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;
3917
3918 EXCEPTION
3919 WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
3920 l_msg_count := FND_MSG_PUB.count_msg;
3921 IF l_msg_count = 1 THEN
3922 PA_INTERFACE_UTILS_PUB.get_messages
3923 (p_encoded => FND_API.G_TRUE,
3924 p_msg_index => 1,
3925 p_msg_count => l_msg_count,
3926 p_msg_data => l_msg_data,
3927 p_data => l_data,
3928 p_msg_index_out => l_msg_index_out);
3929 x_msg_data := l_data;
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
3948 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3949 END INSERT_MISSING_PARENT_DENORM;
3950
3951
3952 /***********************************************************************************************
3953 ROLLUP_DENORM_AMOUNTS: This API assumes that all parent level records for the updated records
3954 are available in denorm table. This API takes sum of amounts at child level records and
3955 updates the amounts on the parent records.
3956 ***********************************************************************************************/
3957
3958 PROCEDURE ROLLUP_DENORM_AMOUNTS(p_budget_version_id IN NUMBER
3959 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3960 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3961 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3962
3963 l_first_level VARCHAR2(1) := NULL;
3964 l_parent_ra_id_tbl l_par_id_tbl_typ;
3965
3966 l_msg_count NUMBER := 0;
3967 l_data VARCHAR2(2000);
3968 l_msg_data VARCHAR2(2000);
3969 l_msg_index_out NUMBER;
3970 l_return_status VARCHAR2(2000);
3971 l_debug_mode VARCHAR2(30);
3972 l_upd_rec NUMBER;
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');
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 ';
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. */
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');
4005 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4006 END IF;
4007
4008 /* Inserting the parent level records that need to be updated into pa_fp_ra_map_tmp
4009 from Resource_Assignments and those that are present in the pa_fp_rollup_tmp
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
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
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.
4030 But always populate unit_of_measure as HOURS for second level of records. The flag
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
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
4049 ,succeeding_periods_amount
4050 ,prior_period_amount
4051 ,period_amount1
4052 ,period_amount2
4053 ,period_amount3
4054 ,period_amount4
4055 ,period_amount5
4056 ,period_amount6
4057 ,period_amount7
4058 ,period_amount8
4059 ,period_amount9
4060 ,period_amount10
4061 ,period_amount11
4062 ,period_amount12
4063 ,period_amount13
4064 ,period_amount14
4065 ,period_amount15
4066 ,period_amount16
4067 ,period_amount17
4068 ,period_amount18
4069 ,period_amount19
4070 ,period_amount20
4071 ,period_amount21
4072 ,period_amount22
4073 ,period_amount23
4074 ,period_amount24
4075 ,period_amount25
4076 ,period_amount26
4077 ,period_amount27
4078 ,period_amount28
4079 ,period_amount29
4080 ,period_amount30
4081 ,period_amount31
4082 ,period_amount32
4083 ,period_amount33
4084 ,period_amount34
4085 ,period_amount35
4086 ,period_amount36
4087 ,period_amount37
4088 ,period_amount38
4089 ,period_amount39
4090 ,period_amount40
4091 ,period_amount41
4092 ,period_amount42
4093 ,period_amount43
4094 ,period_amount44
4095 ,period_amount45
4096 ,period_amount46
4097 ,period_amount47
4098 ,period_amount48
4099 ,period_amount49
4100 ,period_amount50
4101 ,period_amount51
4102 ,period_amount52) =
4103 (SELECT sum(nvl(preceding_periods_amount,0))
4104 ,sum(nvl(succeeding_periods_amount,0))
4105 ,sum(nvl(prior_period_amount,0))
4106 ,sum(nvl(period_amount1,0))
4107 ,sum(nvl(period_amount2,0))
4108 ,sum(nvl(period_amount3,0))
4109 ,sum(nvl(period_amount4,0))
4110 ,sum(nvl(period_amount5,0))
4111 ,sum(nvl(period_amount6,0))
4112 ,sum(nvl(period_amount7,0))
4113 ,sum(nvl(period_amount8,0))
4114 ,sum(nvl(period_amount9,0))
4115 ,sum(nvl(period_amount10,0))
4116 ,sum(nvl(period_amount11,0))
4117 ,sum(nvl(period_amount12,0))
4118 ,sum(nvl(period_amount13,0))
4119 ,sum(nvl(period_amount14,0))
4120 ,sum(nvl(period_amount15,0))
4121 ,sum(nvl(period_amount16,0))
4122 ,sum(nvl(period_amount17,0))
4123 ,sum(nvl(period_amount18,0))
4124 ,sum(nvl(period_amount19,0))
4125 ,sum(nvl(period_amount20,0))
4126 ,sum(nvl(period_amount21,0))
4127 ,sum(nvl(period_amount22,0))
4128 ,sum(nvl(period_amount23,0))
4129 ,sum(nvl(period_amount24,0))
4130 ,sum(nvl(period_amount25,0))
4131 ,sum(nvl(period_amount26,0))
4132 ,sum(nvl(period_amount27,0))
4133 ,sum(nvl(period_amount28,0))
4134 ,sum(nvl(period_amount29,0))
4135 ,sum(nvl(period_amount30,0))
4136 ,sum(nvl(period_amount31,0))
4137 ,sum(nvl(period_amount32,0))
4138 ,sum(nvl(period_amount33,0))
4139 ,sum(nvl(period_amount34,0))
4140 ,sum(nvl(period_amount35,0))
4141 ,sum(nvl(period_amount36,0))
4142 ,sum(nvl(period_amount37,0))
4143 ,sum(nvl(period_amount38,0))
4144 ,sum(nvl(period_amount39,0))
4145 ,sum(nvl(period_amount40,0))
4146 ,sum(nvl(period_amount41,0))
4147 ,sum(nvl(period_amount42,0))
4148 ,sum(nvl(period_amount43,0))
4149 ,sum(nvl(period_amount44,0))
4150 ,sum(nvl(period_amount45,0))
4151 ,sum(nvl(period_amount46,0))
4152 ,sum(nvl(period_amount47,0))
4153 ,sum(nvl(period_amount48,0))
4154 ,sum(nvl(period_amount49,0))
4155 ,sum(nvl(period_amount50,0))
4156 ,sum(nvl(period_amount51,0))
4157 ,sum(nvl(period_amount52,0))
4158 FROM PA_PROJ_PERIODS_DENORM ppd2, pa_resource_assignments pra
4159 WHERE ppd1.resource_assignment_id = ppd2.parent_assignment_id
4160 AND ppd1.currency_type = ppd2.currency_type
4161 AND ppd1.currency_code = decode(ppd2.amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4162 ppd1.currency_code,ppd2.currency_code) --#2801522:Dont check curr code for Qty
4163 AND ppd1.amount_type_id = ppd2.amount_type_id
4164 AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4165 AND decode(ppd2.amount_type_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4166 pra.unit_of_measure,PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS) =
4167 PA_FP_CONSTANTS_PKG.G_UNIT_OF_MEASURE_HOURS
4168 AND ppd2.resource_assignment_id = pra.resource_assignment_id -- Modified for 2801522
4169 )
4170 WHERE ppd1.budget_version_id = p_budget_version_id -- #2839138
4171 AND ppd1.resource_assignment_id in
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
4181 l_first_level := 'N';
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
4191 UPDATE PA_PROJ_PERIODS_DENORM ppd1
4192 SET (preceding_periods_amount
4193 ,succeeding_periods_amount
4194 ,prior_period_amount
4195 ,period_amount1
4196 ,period_amount2
4197 ,period_amount3
4198 ,period_amount4
4199 ,period_amount5
4200 ,period_amount6
4201 ,period_amount7
4202 ,period_amount8
4203 ,period_amount9
4204 ,period_amount10
4205 ,period_amount11
4206 ,period_amount12
4207 ,period_amount13
4208 ,period_amount14
4209 ,period_amount15
4210 ,period_amount16
4211 ,period_amount17
4212 ,period_amount18
4213 ,period_amount19
4214 ,period_amount20
4215 ,period_amount21
4216 ,period_amount22
4217 ,period_amount23
4218 ,period_amount24
4219 ,period_amount25
4220 ,period_amount26
4221 ,period_amount27
4222 ,period_amount28
4223 ,period_amount29
4224 ,period_amount30
4225 ,period_amount31
4226 ,period_amount32
4227 ,period_amount33
4228 ,period_amount34
4229 ,period_amount35
4230 ,period_amount36
4231 ,period_amount37
4232 ,period_amount38
4233 ,period_amount39
4234 ,period_amount40
4235 ,period_amount41
4236 ,period_amount42
4237 ,period_amount43
4238 ,period_amount44
4239 ,period_amount45
4240 ,period_amount46
4241 ,period_amount47
4242 ,period_amount48
4243 ,period_amount49
4244 ,period_amount50
4245 ,period_amount51
4246 ,period_amount52) =
4247 (SELECT sum(nvl(preceding_periods_amount,0))
4248 ,sum(nvl(succeeding_periods_amount,0))
4249 ,sum(nvl(prior_period_amount,0))
4250 ,sum(nvl(period_amount1,0))
4251 ,sum(nvl(period_amount2,0))
4252 ,sum(nvl(period_amount3,0))
4253 ,sum(nvl(period_amount4,0))
4254 ,sum(nvl(period_amount5,0))
4255 ,sum(nvl(period_amount6,0))
4256 ,sum(nvl(period_amount7,0))
4257 ,sum(nvl(period_amount8,0))
4258 ,sum(nvl(period_amount9,0))
4259 ,sum(nvl(period_amount10,0))
4260 ,sum(nvl(period_amount11,0))
4261 ,sum(nvl(period_amount12,0))
4262 ,sum(nvl(period_amount13,0))
4263 ,sum(nvl(period_amount14,0))
4264 ,sum(nvl(period_amount15,0))
4265 ,sum(nvl(period_amount16,0))
4266 ,sum(nvl(period_amount17,0))
4267 ,sum(nvl(period_amount18,0))
4268 ,sum(nvl(period_amount19,0))
4269 ,sum(nvl(period_amount20,0))
4270 ,sum(nvl(period_amount21,0))
4271 ,sum(nvl(period_amount22,0))
4272 ,sum(nvl(period_amount23,0))
4273 ,sum(nvl(period_amount24,0))
4274 ,sum(nvl(period_amount25,0))
4275 ,sum(nvl(period_amount26,0))
4276 ,sum(nvl(period_amount27,0))
4277 ,sum(nvl(period_amount28,0))
4278 ,sum(nvl(period_amount29,0))
4279 ,sum(nvl(period_amount30,0))
4280 ,sum(nvl(period_amount31,0))
4281 ,sum(nvl(period_amount32,0))
4282 ,sum(nvl(period_amount33,0))
4283 ,sum(nvl(period_amount34,0))
4284 ,sum(nvl(period_amount35,0))
4285 ,sum(nvl(period_amount36,0))
4286 ,sum(nvl(period_amount37,0))
4287 ,sum(nvl(period_amount38,0))
4288 ,sum(nvl(period_amount39,0))
4289 ,sum(nvl(period_amount40,0))
4290 ,sum(nvl(period_amount41,0))
4291 ,sum(nvl(period_amount42,0))
4292 ,sum(nvl(period_amount43,0))
4293 ,sum(nvl(period_amount44,0))
4294 ,sum(nvl(period_amount45,0))
4295 ,sum(nvl(period_amount46,0))
4296 ,sum(nvl(period_amount47,0))
4297 ,sum(nvl(period_amount48,0))
4298 ,sum(nvl(period_amount49,0))
4299 ,sum(nvl(period_amount50,0))
4300 ,sum(nvl(period_amount51,0))
4301 ,sum(nvl(period_amount52,0))
4302 FROM PA_PROJ_PERIODS_DENORM ppd2
4303 WHERE ppd1.resource_assignment_id = ppd2.parent_assignment_id
4304 AND ppd1.currency_type = ppd2.currency_type
4305 AND ppd1.currency_code = decode(ppd2.amount_subtype_code,PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_QUANTITY,
4306 ppd1.currency_code,ppd2.currency_code) --#2801522:Dont check curr code for Qty
4307 AND ppd1.amount_type_id = ppd2.amount_type_id
4308 AND ppd1.amount_subtype_id = ppd2.amount_subtype_id
4309 )
4310 WHERE ppd1.budget_version_id = p_budget_version_id -- #2839138
4311 AND ppd1.resource_assignment_id in
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
4321 l_upd_rec := nvl(l_parent_ra_id_tbl.last,0);
4322
4323 END IF;
4324
4325 EXIT WHEN l_upd_rec = 0;
4326
4327 DELETE FROM pa_fp_ra_map_tmp;
4328
4329 IF nvl(l_parent_ra_id_tbl.last,0) >= 1 THEN
4330
4331 FORALL i IN l_parent_ra_id_tbl.first..l_parent_ra_id_tbl.last
4332
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
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;
4351 IF l_msg_count = 1 THEN
4352 PA_INTERFACE_UTILS_PUB.get_messages
4353 (p_encoded => FND_API.G_TRUE,
4354 p_msg_index => 1,
4355 p_msg_count => l_msg_count,
4356 p_msg_data => l_msg_data,
4357 p_data => l_data,
4358 p_msg_index_out => l_msg_index_out);
4359 x_msg_data := l_data;
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
4378 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4379 END ROLLUP_DENORM_AMOUNTS;
4380
4381 /***********************************************************************************************
4382 DELETE_ELEMENT: Given a resource assignment id and txn currency code this API will delete the
4383 element from budget lines table and also from resource assignments table. This API will also
4384 do the necessary so that amounts get rolled up to higher level
4385 ***********************************************************************************************/
4386
4387 PROCEDURE DELETE_ELEMENT(p_budget_version_id IN NUMBER
4388 ,p_resource_assignment_id IN NUMBER
4389 ,p_txn_currency_code IN VARCHAR2
4390 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4391 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4392 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
4393
4394
4395 l_uncat_rlm_id pa_resource_assignments.RESOURCE_LIST_MEMBER_ID%TYPE;
4396 l_resource_list_id pa_resource_lists.RESOURCE_LIST_ID%TYPE;
4397 l_uncat_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
4398 l_rl_group_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
4399 l_project_id pa_projects.project_id%TYPE;
4400 l_planning_level pa_proj_fp_options.ALL_FIN_PLAN_LEVEL_CODE%TYPE;
4401
4402 l_parent_assignment_id pa_resource_assignments.PARENT_ASSIGNMENT_ID%TYPE;
4403 l_rec_exists VARCHAR2(1);
4404 l_child_res_count NUMBER;
4405 l_proj_raw_cost pa_resource_assignments.TOTAL_PROJECT_RAW_COST%TYPE;
4406 l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
4407 l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
4408 l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
4409 l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
4410 l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
4411 l_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
4412 l_records_deleted NUMBER;
4413
4414 l_msg_count NUMBER := 0;
4415 l_data VARCHAR2(2000);
4416 l_msg_data VARCHAR2(2000);
4417 l_msg_index_out NUMBER;
4418 l_return_status VARCHAR2(2000);
4419 l_debug_mode VARCHAR2(30);
4420
4421 l_task_id pa_tasks.task_id%type;
4422 l_resource_list_member_id pa_resource_list_members.resource_list_member_id%type;
4423
4424 --Bug # 2615807.
4425 l_project_currency_code pa_projects_all.project_currency_code%type;
4426 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%type;
4427
4428
4429 l_resource_assignment_id_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4430 l_period_name_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4431 l_start_date_tbl SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type();
4432 l_end_date_tbl SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type();
4433 l_txn_currency_code_tbl SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type();
4434 l_txn_raw_cost_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4435 l_txn_burdened_cost_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4436 l_txn_revenue_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4437 l_project_raw_cost_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4438 l_project_burdened_cost_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4439 l_project_revenue_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4440 l_raw_cost_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4441 l_burdened_cost_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4442 l_revenue_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4443 l_cost_rejection_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4444 l_revenue_rejection_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4445 l_burden_rejection_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4446 l_other_rejection_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4447 l_pc_cur_conv_rej_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4448 l_pfc_cur_conv_rej_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4449 l_quantity_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4450 l_rbs_element_id_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4451 l_task_id_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type();
4452 l_res_class_code_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type();
4453 l_rate_based_flag_tbl SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type();
4454 l_cbs_element_id_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(); --bug#16911079
4455
4456 BEGIN
4457
4458 -- Set the error stack.
4459 pa_debug.set_err_stack('PA_FP_ROLLUP_PKG.Delete_Element');
4460
4461 -- Get the Debug mode into local variable and set it to 'Y'if its NULL
4462 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4463 l_debug_mode := NVL(l_debug_mode, 'Y');
4464
4465 -- Initialize the return status to success
4466 x_return_status := FND_API.G_RET_STS_SUCCESS;
4467
4468 IF P_PA_DEBUG_MODE = 'Y' THEN
4469 pa_debug.set_process('DELETE_ELEMENT: ' || 'PLSQL','LOG',l_debug_mode);
4470 END IF;
4471
4472 pa_debug.g_err_stage := 'In PA_FP_ROLLUP_PKG.Delete_Element ';
4473 IF P_PA_DEBUG_MODE = 'Y' THEN
4474 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4475 END IF;
4476
4477
4478 /* Check for Budget Version ID not being NULL. */
4479 IF ( p_budget_version_id IS NULL) THEN
4480 pa_debug.g_err_stage := 'Err- Budget Version ID cannot be NULL.';
4481 IF P_PA_DEBUG_MODE = 'Y' THEN
4482 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4483 END IF;
4484 x_return_status := FND_API.G_RET_STS_ERROR;
4485 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4486 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4487 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4488 END IF;
4489
4490 /* Check for Resource Assignment ID not being NULL. */
4491 IF ( p_resource_assignment_id IS NULL) THEN
4492 pa_debug.g_err_stage := 'Err- Resource Assignment ID cannot be NULL.';
4493 IF P_PA_DEBUG_MODE = 'Y' THEN
4494 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4495 END IF;
4496 x_return_status := FND_API.G_RET_STS_ERROR;
4497 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4498 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4499 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4500 END IF;
4501
4502 /* Populate the local variables. */
4503
4504 pa_debug.g_err_stage := 'calling populate_local_vars';
4505 IF P_PA_DEBUG_MODE = 'Y' THEN
4506 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4507 END IF;
4508
4509 populate_local_vars(p_budget_version_id => p_budget_version_id,
4510 x_project_id => l_project_id,
4511 x_resource_list_id => l_resource_list_id,
4512 x_uncat_flag => l_uncat_flag,
4513 x_uncat_rlm_id => l_uncat_rlm_id,
4514 x_rl_group_type_id => l_rl_group_type_id,
4515 x_planning_level => l_planning_level,
4516 x_return_status => x_return_status,
4517 x_msg_count => x_msg_count,
4518 x_msg_data => x_msg_data);
4519
4520 --Bug # 2615807.
4521 select project_currency_code,projfunc_currency_code
4522 into l_project_currency_code,l_projfunc_currency_code
4523 from pa_projects_all
4524 where project_id = l_project_id;
4525
4526 IF (p_txn_currency_code IS NULL) THEN
4527
4528 pa_debug.g_err_stage := 'Transaction Currency Code is NULL';
4529 IF P_PA_DEBUG_MODE = 'Y' THEN
4530 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4531 END IF;
4532
4533 DELETE FROM pa_fp_rollup_tmp; /* M20-AUG: delete from rollup_tmp should be unconditional */
4534
4535 /* At this point, l_parent_assginment_id contains the assignment id of an impacted
4536 parent under which some other (undeleted) children exist. Hence Rollup has to be
4537 done for the parent level records. */
4538
4539 /* For the Parent Assignment ID, populating the Rollup Table PA_FP_ROLLUP_TMP, with
4540 the old and new amounts. Call Rollup_Resource_Assignment_Amounts to roll up the
4541 Resource Assignments data and Rollup_Denorm_Amounts to roll up the Denorm data.*/
4542
4543 pa_debug.g_err_stage := 'Insert records into Rollup Temp Table with the amounts';
4544 IF P_PA_DEBUG_MODE = 'Y' THEN
4545 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4546 END IF;
4547
4548 /* M20-AUG: we have to insert all the records for the deleted resource assignment */
4549
4550 INSERT INTO PA_FP_ROLLUP_TMP
4551 ( BUDGET_LINE_ID /* FPB2 */
4552 ,OLD_START_DATE /* FPB2 */
4553 ,START_DATE /* FPB2 */
4554 ,RESOURCE_ASSIGNMENT_ID
4555 ,PARENT_ASSIGNMENT_ID
4556 ,OLD_PROJ_RAW_COST
4557 ,OLD_PROJ_BURDENED_COST
4558 ,OLD_PROJ_REVENUE
4559 ,OLD_PROJFUNC_RAW_COST
4560 ,OLD_PROJFUNC_BURDENED_COST
4561 ,OLD_PROJFUNC_REVENUE
4562 ,OLD_QUANTITY
4563 ,PROJECT_RAW_COST
4564 ,PROJECT_BURDENED_COST
4565 ,PROJECT_REVENUE
4566 ,PROJFUNC_RAW_COST
4567 ,PROJFUNC_BURDENED_COST
4568 ,PROJFUNC_REVENUE
4569 ,TXN_RAW_COST
4570 ,TXN_BURDENED_COST
4571 ,TXN_REVENUE
4572 ,QUANTITY
4573 ,DELETE_FLAG
4574 ,PROJECT_CURRENCY_CODE --Bug # 2615807
4575 ,PROJFUNC_CURRENCY_CODE) --Bug # 2615807
4576 SELECT bl.budget_line_id /* FPB2 */
4577 ,bl.start_Date /* FPB2 */
4578 ,bl.start_Date /* FPB2 */
4579 ,bl.resource_assignment_id
4580 ,pra.parent_assignment_id
4581 ,bl.project_raw_cost old_proj_raw_cost
4582 ,bl.project_burdened_cost old_proj_burdened_cost
4583 ,bl.project_revenue old_proj_revenue
4584 ,bl.raw_cost old_projfunc_raw_cost
4585 ,bl.burdened_cost old_projfunc_burdened_cost
4586 ,bl.revenue old_projfunc_revenue
4587 ,bl.quantity old_quantity
4588 ,null project_raw_cost
4589 ,null project_burdened_cost
4590 ,null project_revenue
4591 ,null projfunc_raw_cost
4592 ,null projfunc_burdened_cost
4593 ,null projfunc_revenue
4594 ,null txn_raw_cost
4595 ,null txn_burdened_cost
4596 ,null txn_revenue
4597 ,null quantity
4598 ,'Y' delete_flag
4599 ,l_project_currency_code --Bug # 2615807
4600 ,l_projFunc_currency_code --Bug # 2615807
4601 FROM pa_budget_lines bl
4602 ,pa_resource_assignments pra
4603 WHERE bl.resource_assignment_id = p_resource_assignment_id
4604 AND pra.resource_assignment_id = bl.resource_assignment_id
4605 /* FPB2: Removed grouped by and null handling in select columns as
4606 budget_line_id needs to be included
4607 GROUP BY bl.resource_assignment_id
4608 ,pra.parent_assignment_id */ ;
4609
4610 pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records in rollup tmp';
4611 IF P_PA_DEBUG_MODE = 'Y' THEN
4612 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4613 END IF;
4614
4615 ELSE
4616
4617 pa_debug.g_err_stage := 'Transaction Currency Code is Not Null';
4618 IF P_PA_DEBUG_MODE = 'Y' THEN
4619 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4620 END IF;
4621
4622 /* Since the details of the records being deleted are required for rolling
4623 up data, we need to get the amounts from the Budget Lines table for the
4624 resource assignment that is being deleted. */
4625
4626 /* First, delete the records from pa_fp_rollup_tmp if any. */
4627
4628 DELETE FROM pa_fp_rollup_tmp;
4629
4630 pa_debug.g_err_stage := 'Insert Records into Rollup Temp Table for txn currency';
4631 IF P_PA_DEBUG_MODE = 'Y' THEN
4632 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4633 END IF;
4634
4635 INSERT INTO pa_fp_rollup_tmp
4636 ( budget_line_id /* FPB2 */
4637 ,old_start_date /* FPB2 */
4638 ,start_date /* FPB2 */
4639 ,resource_assignment_id
4640 ,txn_currency_code
4641 ,delete_flag
4642 ,old_proj_raw_cost
4643 ,old_proj_burdened_cost
4644 ,old_proj_revenue
4645 ,old_projfunc_raw_cost
4646 ,old_projfunc_burdened_cost
4647 ,old_projfunc_revenue
4648 ,old_quantity
4649 ,project_raw_cost
4650 ,project_burdened_cost
4651 ,project_revenue
4652 ,projfunc_raw_cost
4653 ,projfunc_burdened_cost
4654 ,projfunc_revenue
4655 ,quantity
4656 ,project_currency_code --Bug#2615807
4657 ,projFunc_currency_code) --Bug#2615807
4658 SELECT budget_line_id /* FPB2 */
4659 ,start_date /* FPB2 */
4660 ,start_date /* FPB2 */
4661 ,resource_assignment_id
4662 ,txn_currency_code
4663 ,'Y'
4664 ,project_raw_cost
4665 ,project_burdened_cost
4666 ,revenue
4667 ,raw_cost
4668 ,burdened_cost
4669 ,revenue
4670 ,quantity
4671 ,decode(txn_currency_code,p_txn_currency_code,0,project_raw_cost)
4672 ,decode(txn_currency_code,p_txn_currency_code,0,project_burdened_cost)
4673 ,decode(txn_currency_code,p_txn_currency_code,0,project_revenue)
4674 ,decode(txn_currency_code,p_txn_currency_code,0,raw_cost)
4675 ,decode(txn_currency_code,p_txn_currency_code,0,burdened_cost)
4676 ,decode(txn_currency_code,p_txn_currency_code,0,revenue)
4677 ,decode(txn_currency_code,p_txn_currency_code,0,quantity)
4678 ,l_project_currency_code --Bug#2615807
4679 ,l_projFunc_currency_code --Bug#2615807
4680 FROM pa_budget_lines
4681 WHERE resource_assignment_id = p_resource_assignment_id
4682 /* FPB2: Removed grouped by and null handling in select columns as
4683 budget_line_id needs to be included
4684 GROUP BY resource_assignment_id, txn_currency_code */ ;
4685
4686 pa_debug.g_err_stage := 'inserted ' || sql%rowcount || ' records into rollup tmp';
4687 IF P_PA_DEBUG_MODE = 'Y' THEN
4688 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4689 END IF;
4690
4691 END IF;
4692
4693 Select
4694 a.resource_assignment_id
4695 ,a.period_name
4696 ,a.start_date
4697 ,a.end_date
4698 ,a.txn_currency_code
4699 ,-a.txn_raw_cost
4700 ,-a.txn_burdened_cost
4701 ,-a.txn_revenue
4702 ,-a.project_raw_cost
4703 ,-a.project_burdened_cost
4704 ,-a.project_revenue
4705 ,-a.raw_cost
4706 ,-a.burdened_cost
4707 ,-a.revenue
4708 ,a.cost_rejection_code
4709 ,a.revenue_rejection_code
4710 ,a.burden_rejection_code
4711 ,a.other_rejection_code
4712 ,a.pc_cur_conv_rejection_code
4713 ,a.pfc_cur_conv_rejection_code
4714 ,-a.quantity
4715 ,b.rbs_element_id
4716 ,b.task_id
4717 ,b.resource_class_code
4718 ,b.rate_based_flag
4719 ,b.cbs_element_id --bug#16911079
4720 Bulk Collect Into
4721 l_resource_assignment_id_tbl
4722 ,l_period_name_tbl
4723 ,l_start_date_tbl
4724 ,l_end_date_tbl
4725 ,l_txn_currency_code_tbl
4726 ,l_txn_raw_cost_tbl
4727 ,l_txn_burdened_cost_tbl
4728 ,l_txn_revenue_tbl
4729 ,l_project_raw_cost_tbl
4730 ,l_project_burdened_cost_tbl
4731 ,l_project_revenue_tbl
4732 ,l_raw_cost_tbl
4733 ,l_burdened_cost_tbl
4734 ,l_revenue_tbl
4735 ,l_cost_rejection_code_tbl
4736 ,l_revenue_rejection_code_tbl
4737 ,l_burden_rejection_code_tbl
4738 ,l_other_rejection_code_tbl
4739 ,l_pc_cur_conv_rej_code_tbl
4740 ,l_pfc_cur_conv_rej_code_tbl
4741 ,l_quantity_tbl
4742 ,l_rbs_element_id_tbl
4743 ,l_task_id_tbl
4744 ,l_res_class_code_tbl
4745 ,l_rate_based_flag_tbl
4746 ,l_cbs_element_id_tbl --bug#16911079
4747 From
4748 pa_budget_lines a,
4749 pa_resource_assignments b
4750 Where a.resource_assignment_id = b.resource_assignment_id
4751 and b.budget_version_id = p_budget_version_id
4752 and b.resource_assignment_id = p_resource_assignment_id
4753 and a.txn_currency_code = nvl(p_txn_currency_code,a.txn_currency_code);
4754
4755 IF l_resource_assignment_id_tbl.count > 0 THEN
4756
4757 IF P_PA_DEBUG_MODE = 'Y' THEN
4758 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,'There are budget lines to be deleted... ',3);
4759 END IF;
4760
4761 pa_planning_transaction_utils.call_update_rep_lines_api
4762 (
4763 p_source => 'PL-SQL'
4764 ,p_budget_version_id => p_budget_version_id
4765 ,p_resource_assignment_id_tbl => l_resource_assignment_id_tbl
4766 ,p_cbs_element_id_tbl => l_cbs_element_id_tbl --bug#16911079
4767 ,p_period_name_tbl => l_period_name_tbl
4768 ,p_start_date_tbl => l_start_date_tbl
4769 ,p_end_date_tbl => l_end_date_tbl
4770 ,p_txn_currency_code_tbl => l_txn_currency_code_tbl
4771 ,p_txn_raw_cost_tbl => l_txn_raw_cost_tbl
4772 ,p_txn_burdened_cost_tbl => l_txn_burdened_cost_tbl
4773 ,p_txn_revenue_tbl => l_txn_revenue_tbl
4774 ,p_project_raw_cost_tbl => l_project_raw_cost_tbl
4775 ,p_project_burdened_cost_tbl => l_project_burdened_cost_tbl
4776 ,p_project_revenue_tbl => l_project_revenue_tbl
4777 ,p_raw_cost_tbl => l_raw_cost_tbl
4778 ,p_burdened_cost_tbl => l_burdened_cost_tbl
4779 ,p_revenue_tbl => l_revenue_tbl
4780 ,p_cost_rejection_code_tbl => l_cost_rejection_code_tbl
4781 ,p_revenue_rejection_code_tbl => l_revenue_rejection_code_tbl
4782 ,p_burden_rejection_code_tbl => l_burden_rejection_code_tbl
4783 ,p_other_rejection_code => l_other_rejection_code_tbl
4784 ,p_pc_cur_conv_rej_code_tbl => l_pc_cur_conv_rej_code_tbl
4785 ,p_pfc_cur_conv_rej_code_tbl => l_pfc_cur_conv_rej_code_tbl
4786 ,p_quantity_tbl => l_quantity_tbl
4787 ,p_rbs_element_id_tbl => l_rbs_element_id_tbl
4788 ,p_task_id_tbl => l_task_id_tbl
4789 ,p_res_class_code_tbl => l_res_class_code_tbl
4790 ,p_rate_based_flag_tbl => l_rate_based_flag_tbl
4791 ,x_return_status => x_return_status
4792 ,x_msg_count => x_msg_count
4793 ,x_msg_data => x_msg_data);
4794
4795 IF x_return_Status <> FND_API.G_RET_STS_SUCCESS THEN
4796 IF P_PA_DEBUG_MODE = 'Y' THEN
4797 pa_debug.g_err_stage := 'pa_planning_transaction_utils.call_update_rep_lines_api errored .... ' || x_msg_data;
4798 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,5);
4799 END IF;
4800 RAISE PA_FP_ROLLUP_PKG.Invalid_Arg_Exc;
4801 END IF;
4802
4803 END IF;
4804
4805 IF (p_txn_currency_code IS NULL) THEN
4806
4807 /* Transaction Currency Code is NULL and hence we can delete records
4808 from the tables PA_BUDGET_LINES */
4809
4810 DELETE FROM pa_budget_lines
4811 WHERE resource_assignment_id = p_resource_assignment_id;
4812
4813 l_records_deleted := sql%rowcount;
4814
4815 pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4816 IF P_PA_DEBUG_MODE = 'Y' THEN
4817 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4818 END IF;
4819
4820 -- Bug Fix: 4569365. Removed MRC code.
4821 /*
4822 IF l_records_deleted > 0 THEN
4823 -- FPB2: MRC
4824
4825 IF PA_MRC_FINPLAN. G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
4826 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
4827 (x_return_status => x_return_status,
4828 x_msg_count => x_msg_count,
4829 x_msg_data => x_msg_data);
4830 END IF;
4831
4832 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
4833 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
4834
4835 PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES
4836 (p_fin_plan_version_id => p_budget_version_id,
4837 p_entire_version => 'N',
4838 x_return_status => x_return_status,
4839 x_msg_count => x_msg_count,
4840 x_msg_data => x_msg_data);
4841 END IF;
4842
4843 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4844 RAISE g_mrc_exception;
4845 END IF;
4846
4847 END IF;
4848 */
4849
4850 DELETE FROM pa_resource_assignments
4851 WHERE resource_assignment_id = p_resource_assignment_id
4852 RETURNING parent_assignment_id, task_id, resource_list_member_id
4853 INTO l_parent_assignment_id
4854 ,l_task_id
4855 ,l_resource_list_member_id;
4856
4857 pa_debug.g_err_stage := 'Calling Rollup_budget_versions';
4858 IF P_PA_DEBUG_MODE = 'Y' THEN
4859 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4860 END IF;
4861
4862 ROLLUP_BUDGET_VERSION(p_budget_version_id => p_budget_version_id
4863 ,p_entire_version => 'N'
4864 ,x_return_status => x_return_status
4865 ,x_msg_count => x_msg_count
4866 ,x_msg_data => x_msg_data);
4867
4868 DELETE FROM pa_proj_periods_denorm
4869 WHERE resource_assignment_id = p_resource_assignment_id;
4870
4871 pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4872 IF P_PA_DEBUG_MODE = 'Y' THEN
4873 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4874 END IF;
4875
4876 ELSE
4877
4878 /* Deleting records from pa_budget_lines (for resource_assignment_id
4879 and txn_currency_code) and pa_proj_periods_denorm (for 'TRANSACTION'
4880 currency type. */
4881
4882 DELETE FROM pa_budget_lines
4883 WHERE resource_assignment_id = p_resource_assignment_id
4884 AND txn_currency_code = p_txn_currency_code;
4885
4886 l_records_deleted := sql%rowcount;
4887 pa_debug.g_err_stage := 'deleted ' || l_records_deleted || ' records from budget lines';
4888 IF P_PA_DEBUG_MODE = 'Y' THEN
4889 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4890 END IF;
4891
4892 -- Bug Fix: 4569365. Removed MRC code.
4893 /*
4894 IF l_records_deleted > 0 THEN
4895
4896 -- FPB2: MRC
4897
4898 IF PA_MRC_FINPLAN. G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
4899 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
4900 (x_return_status => x_return_status,
4901 x_msg_count => x_msg_count,
4902 x_msg_data => x_msg_data);
4903 END IF;
4904
4905 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
4906 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
4907
4908 PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES
4909 (p_fin_plan_version_id => p_budget_version_id,
4910 p_entire_version => 'N',
4911 x_return_status => x_return_status,
4912 x_msg_count => x_msg_count,
4913 x_msg_data => x_msg_data);
4914 END IF;
4915
4916 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4917 RAISE g_mrc_exception;
4918 END IF;
4919
4920 END IF;
4921 */
4922
4923 /* Call the Rollup API so that the parents and the subsequent parents
4924 are rolled up. */
4925
4926 pa_debug.g_err_stage := 'Calling Rollup_Budget_Version';
4927 IF P_PA_DEBUG_MODE = 'Y' THEN
4928 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4929 END IF;
4930
4931 ROLLUP_BUDGET_VERSION(p_budget_version_id => p_budget_version_id
4932 ,p_entire_version => 'N'
4933 ,x_return_status => x_return_status
4934 ,x_msg_count => x_msg_count
4935 ,x_msg_data => x_msg_data);
4936
4937 /* Delete from pa_proj_periods_denorm. */
4938
4939 DELETE FROM pa_proj_periods_denorm
4940 WHERE resource_assignment_id = p_resource_assignment_id
4941 AND currency_type = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_TRANSACTION
4942 AND currency_code = p_txn_currency_code;
4943
4944 pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
4945
4946 IF P_PA_DEBUG_MODE = 'Y' THEN
4947 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4948 END IF;
4949
4950 /* Check if there are any more Budget Lines existing for this resource. */
4951
4952 BEGIN
4953
4954 SELECT 'Y'
4955 INTO l_rec_exists /* PK: use exists */
4956 FROM dual
4957 WHERE exists
4958 (SELECT 1
4959 FROM pa_budget_lines bl
4960 WHERE resource_assignment_id = p_resource_assignment_id
4961 AND ROWNUM = 1);
4962
4963 EXCEPTION
4964 WHEN NO_DATA_FOUND THEN
4965 l_rec_exists := 'N';
4966
4967 END;
4968
4969
4970 IF (nvl(l_rec_exists,'N') = 'Y') THEN
4971
4972 /* If Budget Lines exist for the resource_assignment_id, Setting the Parent
4973 Assignment ID to NULL as no more processing is required for this case. */
4974
4975 pa_debug.g_err_stage := 'Budget Lines exist for the Parent Assignment ID';
4976 IF P_PA_DEBUG_MODE = 'Y' THEN
4977 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4978 END IF;
4979
4980 l_parent_assignment_id := NULL;
4981
4982 ELSE
4983
4984 /* If there are no budget lines then this assignment id eligible for deletion.
4985 Get the parent assignment id in l_parent_assignment_id */
4986
4987 pa_debug.g_err_stage := 'No Budget Lines for Parent Assignment ID';
4988 IF P_PA_DEBUG_MODE = 'Y' THEN
4989 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
4990 END IF;
4991
4992 DELETE FROM pa_resource_assignments
4993 WHERE resource_assignment_id = p_resource_assignment_id
4994 RETURNING parent_assignment_id, task_id, resource_list_member_id
4995 INTO l_parent_assignment_id
4996 ,l_task_id
4997 ,l_resource_list_member_id;
4998
4999 pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from res assignment';
5000 IF P_PA_DEBUG_MODE = 'Y' THEN
5001 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5002 END IF;
5003
5004 DELETE FROM pa_proj_periods_denorm
5005 WHERE resource_assignment_id = p_resource_assignment_id;
5006
5007 pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' records from denorm';
5008 IF P_PA_DEBUG_MODE = 'Y' THEN
5009 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5010 END IF;
5011
5012 END IF;
5013
5014 END IF; /* txn_currency_code IS NULL */
5015
5016 /* bug 2649117 Moved the LOOP out of the l_parent_assignment_id condition
5017 Else when parent assignment id becomes null it was going in an infinite loop
5018 */
5019 LOOP
5020 IF (l_parent_assignment_id IS NOT NULL) THEN
5021
5022 pa_debug.g_err_stage := 'Parent Assignment ID is NOT NULL';
5023 IF P_PA_DEBUG_MODE = 'Y' THEN
5024 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5025 END IF;
5026
5027 l_child_res_count := 0;
5028
5029 SELECT count(1)
5030 INTO l_child_res_count
5031 FROM pa_resource_assignments pra
5032 WHERE pra.parent_assignment_id = l_parent_assignment_id;
5033
5034 IF l_child_res_count = 0 THEN
5035
5036 /* If no child is found for this Parent Assignment ID,then delete this
5037 resource_assignment_id record from pa_resource_assignments and pa
5038 pa_proj_periods_denorm. Get the parent of this resource assignment id.
5039 Continue this loop until some parent is found with child records. */
5040
5041 pa_debug.g_err_stage := 'no child found. deleting the parent';
5042 IF P_PA_DEBUG_MODE = 'Y' THEN
5043 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5044 END IF;
5045
5046 /* the delete below is moved before the next delete as after the next delte
5047 l_parent_assignment_id value will change
5048 */
5049
5050 DELETE FROM pa_proj_periods_denorm
5051 WHERE resource_assignment_id = l_parent_assignment_id;
5052 /* WHERE resource_assignment_id = p_resource_assignment_id during bug fix 2649117 found that it
5053 was p_resource_assignment_id */
5054
5055 DELETE FROM pa_resource_assignments
5056 WHERE resource_assignment_id = l_parent_assignment_id
5057 RETURNING parent_assignment_id INTO l_parent_assignment_id;
5058
5059 pa_debug.g_err_stage := 'deleted ' || sql%rowcount || ' from denorm';
5060 IF P_PA_DEBUG_MODE = 'Y' THEN
5061 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5062 END IF;
5063
5064 ELSE /* Child records are found */
5065
5066 /* Resource Assignments need to be rolled up from this point.
5067 So, exit the loop. */
5068 pa_debug.g_err_stage := 'some child found. no action';
5069 IF P_PA_DEBUG_MODE = 'Y' THEN
5070 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5071 END IF;
5072
5073 EXIT;
5074 END IF;
5075 ELSE
5076 /* bug 2649117 added else and the exit condition. So that when
5077 no parent is found it exits out of the loop
5078 */
5079 pa_debug.g_err_stage := 'parent assignment id is NULL';
5080 IF P_PA_DEBUG_MODE = 'Y' THEN
5081 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,pa_debug.g_err_stage,3);
5082 END IF;
5083
5084 EXIT;
5085 END IF;
5086 END LOOP;
5087
5088 pa_debug.reset_err_stack;
5089
5090 EXCEPTION
5091 WHEN PA_FP_ROLLUP_PKG.Invalid_Arg_Exc THEN
5092 l_msg_count := FND_MSG_PUB.count_msg;
5093 IF l_msg_count = 1 THEN
5094 PA_INTERFACE_UTILS_PUB.get_messages
5095 (p_encoded => FND_API.G_TRUE,
5096 p_msg_index => 1,
5097 p_msg_count => l_msg_count,
5098 p_msg_data => l_msg_data,
5099 p_data => l_data,
5100 p_msg_index_out => l_msg_index_out);
5101 x_msg_data := l_data;
5102 x_msg_count := l_msg_count;
5103 ELSE
5104 x_msg_count := l_msg_count;
5105 END IF;
5106 pa_debug.reset_err_stack;
5107 RAISE;
5108 WHEN OTHERS THEN
5109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5110 x_msg_count := 1;
5111 x_msg_data := SQLERRM;
5112 FND_MSG_PUB.add_exc_msg
5113 ( p_pkg_name => 'PA_FP_ROLLUP_PKG'
5114 ,p_procedure_name => 'Delete_Element');
5115 IF P_PA_DEBUG_MODE = 'Y' THEN
5116 pa_debug.write('DELETE_ELEMENT: ' || l_module_name,SQLERRM,5);
5117 END IF;
5118 pa_debug.reset_err_stack;
5119
5120 raise FND_API.G_EXC_UNEXPECTED_ERROR;
5121 END DELETE_ELEMENT;
5122
5123 END PA_FP_ROLLUP_PKG;
[Home] [Help]