1 PACKAGE BODY PA_PROCESS_ACCUM_CMT_RES AS
2 /* $Header: PACMTREB.pls 120.2 2005/08/31 11:08:16 vmangulu noship $ */
3
4 Procedure Process_it_yt_pt_cmt_res
5 (x_project_id In Number,
6 x_task_id In Number,
7 x_resource_list_id in Number,
8 x_resource_list_Member_id in Number,
9 x_resource_id in Number,
10 x_resource_list_assignment_id in Number,
11 x_track_as_labor_flag In Varchar2,
12 x_rollup_qty_flag In Varchar2,
13 x_unit_of_measure In Varchar2,
14 x_current_period In Varchar2,
15 X_Raw_Cost In Number,
16 X_Burdened_Cost In Number,
17 X_Quantity In Number,
18 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
19 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
20 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
21 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
22
23 -- Process_it_yt_pt_cmt_res - Processes ITD,YTD and PTD amounts in the
24 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
25 -- given Project-Task-Resource combination,records are
26 -- created/updated and rolled up to all the
27 -- higher level tasks. The Project-Resource records
28 -- are also created/updated.
29
30 CURSOR Proj_Res_level_Cur IS
31 SELECT Project_Accum_Id
32 FROM
33 PA_PROJECT_ACCUM_HEADERS
34 WHERE Project_id = X_project_id
35 AND Task_Id = 0
36 AND Resource_list_Member_id = X_resource_list_member_id;
37
38 V_task_array task_id_tabtype;
39 Recs_processed Number := 0;
40 V_Accum_id Number := 0;
41 v_noof_tasks Number := 0;
42 V_Qty Number := 0;
43 Res_Recs_processed Number := 0;
44 V_Old_Stack Varchar2(630);
45
46 Begin
47
48 V_Old_Stack := x_err_stack;
49 x_err_stack :=
50 x_err_stack ||'->PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_pt_cmt_res';
51
52 pa_debug.debug(x_err_stack);
53
54 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
55 -- project,task and resource combination.It is possible that there might be a
56 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
57 -- no corresponding detail record. The procedure called below,will
58 -- check for the existence of the detail records and if not available
59 -- would create it.
60
61 PA_ACCUM_UTILS.Check_Cmt_Details
62 (x_project_id,
63 x_task_id,
64 x_resource_list_Member_id,
65 Res_recs_processed,
66 x_err_stack,
67 x_err_stage,
68 x_err_code);
69
70 Recs_processed := Recs_processed + Res_recs_processed;
71
72 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
73 -- project and Resource combination. It is possible that there might be a
74 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
75 -- no corresponding detail record. The procedure called below,will
76 -- check for the existence of the detail records and if not available
77 -- would create it.
78
79 PA_ACCUM_UTILS.Check_Cmt_Details
80 (x_project_id,
81 0,
82 x_resource_list_Member_id,
83 Res_recs_processed,
84 x_err_stack,
85 x_err_stage,
86 x_err_code);
87
88 Recs_processed := Recs_processed + Res_recs_processed;
89
90 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
91 -- Resource is 'Y'
92
93 If x_rollup_qty_flag = 'Y' Then
94 V_Qty := X_Quantity;
95 Else
96 V_Qty := 0;
97 End If;
98
99
100 -- The follwing Update statement updates all records in the given task
101 -- WBS hierarchy.It will update only the Project-task-resource combination
102 -- records and the Project-Resource level record(Task id = 0 and
103 -- Resourcelist member id <> 0)
104
105 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
106 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
107 CMT_RAW_COST_YTD = CMT_RAW_COST_YTD + X_Raw_Cost,
108 CMT_RAW_COST_PTD = CMT_RAW_COST_PTD + X_Raw_Cost,
109 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
110 CMT_BURDENED_COST_YTD = CMT_BURDENED_COST_YTD + X_Burdened_Cost,
111 CMT_BURDENED_COST_PTD = CMT_BURDENED_COST_PTD + X_Burdened_Cost,
112 CMT_QUANTITY_ITD = CMT_QUANTITY_ITD + V_Qty,
113 CMT_QUANTITY_YTD = CMT_QUANTITY_YTD + V_Qty,
114 CMT_QUANTITY_PTD = CMT_QUANTITY_PTD + V_Qty,
115 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
116 LAST_UPDATE_DATE = Trunc(Sysdate),
117 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
118 Where PAA.Project_Accum_id In
119 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
120 Where Pah.Project_id = x_project_id and
121 pah.Resource_list_member_id = x_resource_list_Member_id and
122 Pah.Task_id in ( select 0 from sys.dual union
123 Select Pt.Task_Id from PA_TASKS pt
124 start with pt.task_id = x_task_id
125 connect by prior pt.parent_task_id = pt.task_id));
126 Recs_processed := Recs_processed + SQL%ROWCOUNT;
127
128 -- Initially, the above statement might not Update any rows
129 -- since the Project-Task-Resource combinations or
130 -- Project-Resource combinations might not have been created.
131 -- We shall be creating them below.
132 -- The following procedure would return all the tasks in the given task
133 -- WBS hierarchy, including the given task, which do not have a header
134 -- record . The return parameter is an array of records.
135
136 v_noof_tasks := 0;
137 Get_all_higher_tasks_cmt_res (x_project_id ,
138 X_task_id ,
139 x_resource_list_member_id,
140 v_task_array,
141 v_noof_tasks,
142 x_err_stack,
143 x_err_stage,
144 x_err_code);
145
146
147 -- If the above procedure had returned any tasks , then we need to insert
148 -- header record and commitments record.We need to process the tasks one by one
149 -- since we require the Accum_id for each detail record.
150 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
151 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
152 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
153 -- in the Project_accum_commitments table. The next time , if the given task
154 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
155 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
156 -- two records would have been processed by the Update statements.
157
158 If v_noof_tasks > 0 Then
159 For i in 1..v_noof_tasks LOOP
160 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval
161 into V_Accum_id
162 From Dual;
163 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
164 (X_project_id,
165 v_task_array(i),
166 x_resource_list_id ,
167 x_resource_list_Member_id ,
168 x_resource_id ,
169 x_resource_list_assignment_id ,
170 x_current_period,
171 v_accum_id,
172 x_err_stack,
173 x_err_stage,
174 x_err_code);
175
176 Recs_processed := Recs_processed + 1;
177 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
178 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
179 CMT_RAW_COST_PTD,
180 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
181 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
182 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
183 CMT_QUANTITY_PTD,
184 CMT_UNIT_OF_MEASURE,
185 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
186 LAST_UPDATE_LOGIN) Values
187 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,0,X_Raw_Cost,
188 X_Burdened_Cost,X_Burdened_Cost,
189 0,X_Burdened_Cost,
190 V_Qty,V_Qty,0,V_Qty,
191 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
192 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
193 Recs_processed := Recs_processed + 1;
194 END LOOP;
195 End If;
196
197 -- This will check for the Project-Resource combination in the Header records
198 -- and if not present create the Header and Detail records for commitments
199
200 Open Proj_Res_level_Cur;
201 Fetch Proj_Res_level_Cur Into V_Accum_Id;
202 If Proj_Res_level_Cur%NOTFOUND Then
203 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
204 From Dual;
205 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
206 (X_project_id,
207 0,
208 x_resource_list_id ,
209 x_resource_list_Member_id ,
210 x_resource_id ,
211 x_resource_list_assignment_id ,
212 x_current_period,
213 v_accum_id,
214 x_err_stack,
215 x_err_stage,
216 x_err_code);
217
218 Recs_processed := Recs_processed + 1;
219 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
220 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
221 CMT_RAW_COST_PTD,
222 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
223 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
224 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
225 CMT_QUANTITY_PTD,
226 CMT_UNIT_OF_MEASURE,
227 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
228 LAST_UPDATE_LOGIN) Values
229 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,0,X_Raw_Cost,
230 X_Burdened_Cost,X_Burdened_Cost,
231 0,X_Burdened_Cost,
232 V_Qty,V_Qty,0,V_Qty,
233 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
234 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
235 Recs_processed := Recs_processed + 1;
236 End If;
237 Close Proj_Res_level_Cur;
238 x_recs_processed := Recs_processed;
239 -- Restore the old x_err_stack;
240
241 x_err_stack := V_Old_Stack;
242 Exception
243 When Others Then
244 x_err_code := SQLCODE;
245 RAISE;
246 End Process_it_yt_pt_cmt_res;
247
248 Procedure Process_it_yt_pp_cmt_res
249 (x_project_id In Number,
250 x_task_id In Number,
251 x_resource_list_id in Number,
252 x_resource_list_Member_id in Number,
253 x_resource_id in Number,
254 x_resource_list_assignment_id in Number,
255 x_track_as_labor_flag In Varchar2,
256 x_rollup_qty_flag In Varchar2,
257 x_unit_of_measure In Varchar2,
258 x_current_period In Varchar2,
259 X_Raw_Cost In Number,
260 X_Burdened_Cost In Number,
261 X_Quantity In Number,
262 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
263 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
264 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
265 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
266
267 -- Process_it_yt_pp_cmt_res - Processes ITD,YTD and PP amounts in the
268 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
269 -- given Project-Task-Resource combination,records are
270 -- created/updated and rolled up to all the
271 -- higher level tasks. The Project-Resource records
272 -- are also created/updated.
273
274 CURSOR Proj_Res_level_Cur IS
275 SELECT Project_Accum_Id
276 FROM
277 PA_PROJECT_ACCUM_HEADERS
278 WHERE Project_id = X_project_id
279 AND Task_Id = 0
280 AND Resource_list_Member_id = X_resource_list_member_id;
281
282 V_task_array task_id_tabtype;
283 Recs_processed Number := 0;
284 V_Accum_id Number := 0;
285 v_noof_tasks Number := 0;
286 V_Qty Number := 0;
287 Res_Recs_processed Number := 0;
288 V_Old_Stack Varchar2(630);
289
290 Begin
291 V_Old_Stack := x_err_stack;
292 x_err_stack :=
293 x_err_stack ||'->PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_pp_cmt_res';
294
295 pa_debug.debug(x_err_stack);
296
297 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
298 -- project,task and resource combination.It is possible that there might be a
299 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
300 -- no corresponding detail record. The procedure called below,will
301 -- check for the existence of the detail records and if not available
302 -- would create it.
303
304 PA_ACCUM_UTILS.Check_Cmt_Details
305 (x_project_id,
306 x_task_id,
307 x_resource_list_Member_id,
308 Res_recs_processed,
309 x_err_stack,
310 x_err_stage,
311 x_err_code);
312
313 Recs_processed := Recs_processed + Res_recs_processed;
314
315 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
316 -- project and Resource combination. It is possible that there might be a
317 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
318 -- no corresponding detail record. The procedure called below,will
319 -- check for the existence of the detail records and if not available
320 -- would create it.
321
322 PA_ACCUM_UTILS.Check_Cmt_Details
323 (x_project_id,
324 0,
325 x_resource_list_Member_id,
326 Res_recs_processed,
327 x_err_stack,
328 x_err_stage,
329 x_err_code);
330
331 Recs_processed := Recs_processed + Res_recs_processed;
332
333 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
334 -- Resource is 'Y'
335
336 If x_rollup_qty_flag = 'Y' Then
337 V_Qty := X_Quantity;
338 Else
339 V_Qty := 0;
340 End If;
341
342
343 -- The follwing Update statement updates all records in the given task
344 -- WBS hierarchy.It will update only the Project-task-resource combination
345 -- records and the Project-Resource level record(Task id = 0 and
346 -- Resourcelist member id <> 0)
347
348 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
349 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
350 CMT_RAW_COST_YTD = CMT_RAW_COST_YTD + X_Raw_Cost,
351 CMT_RAW_COST_PP = CMT_RAW_COST_PP + X_Raw_Cost,
352 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
353 CMT_BURDENED_COST_YTD = CMT_BURDENED_COST_YTD + X_Burdened_Cost,
354 CMT_BURDENED_COST_PP = CMT_BURDENED_COST_PP + X_Burdened_Cost,
355 CMT_QUANTITY_ITD = CMT_QUANTITY_ITD + V_Qty,
356 CMT_QUANTITY_YTD = CMT_QUANTITY_YTD + V_Qty,
357 CMT_QUANTITY_PP = CMT_QUANTITY_PP + V_Qty,
358 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
359 LAST_UPDATE_DATE = Trunc(Sysdate),
360 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
361 Where PAA.Project_Accum_id In
362 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
363 Where Pah.Project_id = x_project_id and
364 pah.Resource_list_member_id = x_resource_list_Member_id and
365 Pah.Task_id in (select 0 from sys.dual union
366 Select Pt.Task_Id from PA_TASKS pt
367 start with pt.task_id = x_task_id
368 connect by prior pt.parent_task_id = pt.task_id));
369 Recs_processed := Recs_processed + SQL%ROWCOUNT;
370
371 -- Initially, the above statement might not Update any rows
372 -- since the Project-Task-Resource combinations or
373 -- Project-Resource combinations might not have been created.
374 -- We shall be creating them below.
375 -- The following procedure would return all the tasks in the given task
376 -- WBS hierarchy, including the given task, which do not have a header
377 -- record . The return parameter is an array of records.
378
379 v_noof_tasks := 0;
380 Get_all_higher_tasks_cmt_res (x_project_id ,
381 X_task_id ,
382 x_resource_list_member_id,
383 v_task_array,
384 v_noof_tasks,
385 x_err_stack,
386 x_err_stage,
387 x_err_code);
388
389
390 -- If the above procedure had returned any tasks , then we need to insert
391 -- header record and commitments record.We need to process the tasks one by one
392 -- since we require the Accum_id for each detail record.
393 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
394 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
395 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
396 -- in the Project_accum_commitments table. The next time , if the given task
397 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
398 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
399 -- two records would have been processed by the Update statements.
400
401 If v_noof_tasks > 0 Then
402 For i in 1..v_noof_tasks LOOP
403 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
404 From Dual;
405 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
406 (X_project_id,
407 v_task_array(i),
408 x_resource_list_id ,
409 x_resource_list_Member_id ,
410 x_resource_id ,
411 x_resource_list_assignment_id ,
412 x_current_period,
413 v_accum_id,
414 x_err_stack,
415 x_err_stage,
416 x_err_code);
417
418 Recs_processed := Recs_processed + 1;
419 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
420 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
421 CMT_RAW_COST_PTD,
422 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
423 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
424 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
425 CMT_QUANTITY_PTD,
426 CMT_UNIT_OF_MEASURE,
427 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
428 LAST_UPDATE_LOGIN) Values
429 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,X_Raw_Cost,0,
430 X_Burdened_Cost,X_Burdened_Cost,
431 X_Burdened_Cost,0,
432 V_Qty,V_Qty,V_Qty,0,
433 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
434 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
435 Recs_processed := Recs_processed + 1;
436 END LOOP;
437 End If;
438
439 -- This will check for the Project-Resource combination in the Header records
440 -- and if not present create the Header and Detail records for commitments
441
442 Open Proj_Res_level_Cur;
443 Fetch Proj_Res_level_Cur Into V_Accum_Id;
444 If Proj_Res_level_Cur%NOTFOUND Then
445 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
446 From Dual;
447 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
448 (X_project_id,
449 0,
450 x_resource_list_id ,
451 x_resource_list_Member_id ,
452 x_resource_id ,
453 x_resource_list_assignment_id ,
454 x_current_period,
455 v_accum_id,
456 x_err_stack,
457 x_err_stage,
458 x_err_code);
459
460 Recs_processed := Recs_processed + 1;
461
462 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
463 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
464 CMT_RAW_COST_PTD,
465 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
466 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
467 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
468 CMT_QUANTITY_PTD,
469 CMT_UNIT_OF_MEASURE,
470 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
471 LAST_UPDATE_LOGIN) Values
472 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,X_Raw_Cost,0,
473 X_Burdened_Cost,X_Burdened_Cost,
474 X_Burdened_Cost,0,
475 V_Qty,V_Qty,V_Qty,0,
476 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
477 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
478 Recs_processed := Recs_processed + 1;
479 End If;
480 Close Proj_Res_level_Cur;
481 x_recs_processed := Recs_processed;
482 -- Restore the old x_err_stack;
483
484 x_err_stack := V_Old_Stack;
485
486 Exception
487 When Others Then
488 x_err_code := SQLCODE;
489 RAISE;
490 End Process_it_yt_pp_cmt_res;
491
492 Procedure Process_it_pp_cmt_res
493 (x_project_id In Number,
494 x_task_id In Number,
495 x_resource_list_id in Number,
496 x_resource_list_Member_id in Number,
497 x_resource_id in Number,
498 x_resource_list_assignment_id in Number,
499 x_track_as_labor_flag In Varchar2,
500 x_rollup_qty_flag In Varchar2,
501 x_unit_of_measure In Varchar2,
502 x_current_period In Varchar2,
503 X_Raw_Cost In Number,
504 X_Burdened_Cost In Number,
505 X_Quantity In Number,
506 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
507 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
508 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
509 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
510
511
512 -- Process_it_pp_cmt_res - Processes ITD and PP amounts in the
513 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
514 -- given Project-Task-Resource combination,records are
515 -- created/updated and rolled up to all the
516 -- higher level tasks. The Project-Resource records
517 -- are also created/updated.
518
519 CURSOR Proj_Res_level_Cur IS
520 SELECT Project_Accum_Id
521 FROM
522 PA_PROJECT_ACCUM_HEADERS
523 WHERE Project_id = X_project_id
524 AND Task_Id = 0
525 AND Resource_list_Member_id = X_resource_list_member_id;
526
527 V_task_array task_id_tabtype;
528 Recs_processed Number := 0;
529 V_Accum_id Number := 0;
530 v_noof_tasks Number := 0;
531 V_Qty Number := 0;
532 Res_Recs_processed Number := 0;
533 V_Old_Stack Varchar2(630);
534
535 Begin
536 V_Old_Stack := x_err_stack;
537 x_err_stack :=
538 x_err_stack ||'->PA_PROCESS_ACCUM_CMT_RES.Process_it_pp_cmt_res';
539
540 pa_debug.debug(x_err_stack);
541
542 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
543 -- project,task and resource combination.It is possible that there might be a
544 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
545 -- no corresponding detail record. The procedure called below,will
546 -- check for the existence of the detail records and if not available
547 -- would create it.
548
549 PA_ACCUM_UTILS.Check_Cmt_Details
550 (x_project_id,
551 x_task_id,
552 x_resource_list_Member_id,
553 Res_recs_processed,
554 x_err_stack,
555 x_err_stage,
556 x_err_code);
557
558 Recs_processed := Recs_processed + Res_recs_processed;
559
560 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
561 -- project and Resource combination. It is possible that there might be a
562 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
563 -- no corresponding detail record. The procedure called below,will
564 -- check for the existence of the detail records and if not available
565 -- would create it.
566
567 PA_ACCUM_UTILS.Check_Cmt_Details
568 (x_project_id,
569 0,
570 x_resource_list_Member_id,
571 Res_recs_processed,
572 x_err_stack,
573 x_err_stage,
574 x_err_code);
575
576 Recs_processed := Recs_processed + Res_recs_processed;
577
578 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
579 -- Resource is 'Y'
580
581 If x_rollup_qty_flag = 'Y' Then
582 V_Qty := X_Quantity;
583 Else
584 V_Qty := 0;
585 End If;
586
587
588
589 -- The follwing Update statement updates all records in the given task
590 -- WBS hierarchy.It will update only the Project-task-resource combination
591 -- records and the Project-Resource level record(Task id = 0 and
592 -- Resourcelist member id <> 0)
593
594 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
595 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
596 CMT_RAW_COST_PP = CMT_RAW_COST_PP + X_Raw_Cost,
597 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
598 CMT_BURDENED_COST_PP = CMT_BURDENED_COST_PP + X_Burdened_Cost,
599 CMT_QUANTITY_ITD = CMT_QUANTITY_ITD + V_Qty,
600 CMT_QUANTITY_PP = CMT_QUANTITY_PP + V_Qty,
601 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
602 LAST_UPDATE_DATE = Trunc(Sysdate),
603 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
604 Where PAA.Project_Accum_id In
605
606 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
607 Where Pah.Project_id = x_project_id and
608 pah.Resource_list_member_id = x_resource_list_Member_id and
609 Pah.Task_id in (select 0 from sys.dual union
610 Select Pt.Task_Id from PA_TASKS pt
611 start with pt.task_id = x_task_id
612 connect by prior pt.parent_task_id = pt.task_id));
613 Recs_processed := Recs_processed + SQL%ROWCOUNT;
614 v_noof_tasks := 0;
615
616 -- Initially, the above statement might not Update any rows
617 -- since the Project-Task-Resource combinations or
618 -- Project-Resource combinations might not have been created.
619 -- We shall be creating them below.
620 -- The following procedure would return all the tasks in the given task
621 -- WBS hierarchy, including the given task, which do not have a header
622 -- record . The return parameter is an array of records.
623
624 Get_all_higher_tasks_cmt_res (x_project_id ,
625 X_task_id ,
626 x_resource_list_member_id,
627 v_task_array,
628 v_noof_tasks,
629 x_err_stack,
630 x_err_stage,
631 x_err_code);
632
633
634 -- If the above procedure had returned any tasks , then we need to insert
635 -- header record and commitments record.We need to process the tasks one by one
636 -- since we require the Accum_id for each detail record.
637 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
638 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
639 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
640 -- in the Project_accum_commitments table. The next time , if the given task
641 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
642 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
643 -- two records would have been processed by the Update statements.
644
645 If v_noof_tasks > 0 Then
646 For i in 1..v_noof_tasks LOOP
647 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
648 From Dual;
649 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
650 (X_project_id,
651 v_task_array(i),
652 x_resource_list_id ,
653 x_resource_list_Member_id ,
654 x_resource_id ,
655 x_resource_list_assignment_id ,
656 x_current_period,
657 v_accum_id,
658 x_err_stack,
659 x_err_stage,
660 x_err_code);
661
662 Recs_processed := Recs_processed + 1;
663 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
664 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
665 CMT_RAW_COST_PTD,
666 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
667 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
668 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
669 CMT_QUANTITY_PTD,
670 CMT_UNIT_OF_MEASURE,
671 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
672 LAST_UPDATE_LOGIN) Values
673 (V_Accum_id,X_Raw_Cost,0,X_Raw_Cost,0,
674 X_Burdened_Cost,0,
675 X_Burdened_Cost,0,
676 V_Qty,0,V_Qty,0,
677 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
678 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
679 Recs_processed := Recs_processed + 1;
680 END LOOP;
681 End If;
682
683 -- This will check for the Project-Resource combination in the Header records
684 -- and if not present create the Header and Detail records for commitments
685
686 Open Proj_Res_level_Cur;
687 Fetch Proj_Res_level_Cur Into V_Accum_Id;
688 If Proj_Res_level_Cur%NOTFOUND Then
689 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
690 From Dual;
691 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
692 (X_project_id,
693 0,
694 x_resource_list_id ,
695 x_resource_list_Member_id ,
696 x_resource_id ,
697 x_resource_list_assignment_id ,
698 x_current_period,
699 v_accum_id,
700 x_err_stack,
701 x_err_stage,
702 x_err_code);
703
704 Recs_processed := Recs_processed + 1;
705 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
706 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
707 CMT_RAW_COST_PTD,
708 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
709 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
710 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
711 CMT_QUANTITY_PTD,
712 CMT_UNIT_OF_MEASURE,
713 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
714 LAST_UPDATE_LOGIN) Values
715 (V_Accum_id,X_Raw_Cost,0,X_Raw_Cost,0,
716 X_Burdened_Cost,0,
717 X_Burdened_Cost,0,
718 V_Qty,0,V_Qty,0,
719 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
720 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
721 Recs_processed := Recs_processed + 1;
722 End If;
723
724 Close Proj_Res_level_Cur;
725 x_recs_processed := Recs_processed;
726 -- Restore the old x_err_stack;
727
728 x_err_stack := V_Old_Stack;
729
730 Exception
731 When Others Then
732 x_err_code := SQLCODE;
733 RAISE ;
734 End Process_it_pp_cmt_res;
735
736 Procedure Process_it_yt_cmt_res
737 (x_project_id In Number,
738 x_task_id In Number,
739 x_resource_list_id in Number,
740 x_resource_list_Member_id in Number,
741 x_resource_id in Number,
742 x_resource_list_assignment_id in Number,
743 x_track_as_labor_flag In Varchar2,
744 x_rollup_qty_flag In Varchar2,
745 x_unit_of_measure In Varchar2,
746 x_current_period In Varchar2,
747 X_Raw_Cost In Number,
748 X_Burdened_Cost In Number,
749 X_Quantity In Number,
750 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
751 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
752 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
753 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
754
755
756 -- Process_it_yt_cmt_res - Processes ITD and YTD amounts in the
757 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
758 -- given Project-Task-Resource combination,records are
759 -- created/updated and rolled up to all the
760 -- higher level tasks. The Project-Resource records
761 -- are also created/updated.
762
763 CURSOR Proj_Res_level_Cur IS
764 SELECT Project_Accum_Id
765 FROM
766 PA_PROJECT_ACCUM_HEADERS
767 WHERE Project_id = X_project_id
768 AND Task_Id = 0
769 AND Resource_list_Member_id = X_resource_list_member_id;
770
771 V_task_array task_id_tabtype;
772 Recs_processed Number := 0;
773 V_Accum_id Number := 0;
774 v_noof_tasks Number := 0;
775 V_Qty Number := 0;
776 Res_Recs_processed Number := 0;
777 V_Old_Stack Varchar2(630);
778
779 Begin
780
781 V_Old_Stack := x_err_stack;
782 x_err_stack :=
783 x_err_stack ||'->PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_cmt_res';
784
785 pa_debug.debug(x_err_stack);
786
787 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
788 -- project,task and resource combination.It is possible that there might be a
789 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
790 -- no corresponding detail record. The procedure called below,will
791 -- check for the existence of the detail records and if not available
792 -- would create it.
793
794 PA_ACCUM_UTILS.Check_Cmt_Details
795 (x_project_id,
796 x_task_id,
797 x_resource_list_Member_id,
798 Res_recs_processed,
799 x_err_stack,
800 x_err_stage,
801 x_err_code);
802
803 Recs_processed := Recs_processed + Res_recs_processed;
804
805 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
806 -- project and Resource combination. It is possible that there might be a
807 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
808 -- no corresponding detail record. The procedure called below,will
809 -- check for the existence of the detail records and if not available
810 -- would create it.
811
812 PA_ACCUM_UTILS.Check_Cmt_Details
813 (x_project_id,
814 0,
815 x_resource_list_Member_id,
816 Res_recs_processed,
817 x_err_stack,
818 x_err_stage,
819 x_err_code);
820
821 Recs_processed := Recs_processed + Res_recs_processed;
822
823 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
824 -- Resource is 'Y'
825
826 If x_rollup_qty_flag = 'Y' Then
827 V_Qty := X_Quantity;
828 Else
829 V_Qty := 0;
830 End If;
831
832
833 -- The follwing Update statement updates all records in the given task
834 -- WBS hierarchy.It will update only the Project-task-resource combination
835 -- records and the Project-Resource level record(Task id = 0 and
836 -- Resourcelist member id <> 0)
837
838 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
839 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
840 CMT_RAW_COST_YTD = CMT_RAW_COST_YTD + X_Raw_Cost,
841 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
842 CMT_BURDENED_COST_YTD = CMT_BURDENED_COST_YTD + X_Burdened_Cost,
843 CMT_QUANTITY_ITD = CMT_QUANTITY_ITD + V_Qty,
844 CMT_QUANTITY_YTD = CMT_QUANTITY_YTD + V_Qty,
845 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
846 LAST_UPDATE_DATE = Trunc(Sysdate),
847 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
848 Where PAA.Project_Accum_id In
849 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
850 Where Pah.Project_id = x_project_id and
851 pah.Resource_list_member_id = x_resource_list_Member_id and
852 Pah.Task_id in (select 0 from sys.dual union
853 Select Pt.Task_Id from PA_TASKS pt
854 start with pt.task_id = x_task_id
855 connect by prior pt.parent_task_id = pt.task_id));
856 Recs_processed := Recs_processed + SQL%ROWCOUNT;
857
858 -- Initially, the above statement might not Update any rows
859 -- since the Project-Task-Resource combinations or
860 -- Project-Resource combinations might not have been created.
861 -- We shall be creating them below.
862 -- The following procedure would return all the tasks in the given task
863 -- WBS hierarchy, including the given task, which do not have a header
864 -- record . The return parameter is an array of records.
865
866 v_noof_tasks := 0;
867 Get_all_higher_tasks_cmt_res (x_project_id ,
868 X_task_id ,
869 x_resource_list_member_id,
870 v_task_array,
871 v_noof_tasks,
872 x_err_stack,
873 x_err_stage,
874 x_err_code);
875
876
877 -- If the above procedure had returned any tasks , then we need to insert
878 -- header record and commitments record.We need to process the tasks one by one
879 -- since we require the Accum_id for each detail record.
880 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
881 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
882 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
883 -- in the Project_accum_commitments table. The next time , if the given task
884 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
885 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
886 -- two records would have been processed by the Update statements.
887
888 If v_noof_tasks > 0 Then
889 For i in 1..v_noof_tasks LOOP
890 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
891 From Dual;
892 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
893 (X_project_id,
894 v_task_array(i),
895 x_resource_list_id ,
896 x_resource_list_Member_id ,
897 x_resource_id ,
898 x_resource_list_assignment_id ,
899 x_current_period,
900 v_accum_id,
901 x_err_stack,
902 x_err_stage,
903 x_err_code);
904
905 Recs_processed := Recs_processed + 1;
906 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
907 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
908 CMT_RAW_COST_PTD,
909 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
910 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
911 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
912 CMT_QUANTITY_PTD,
913 CMT_UNIT_OF_MEASURE,
914 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
915 LAST_UPDATE_LOGIN) Values
916 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,0,0,
917 X_Burdened_Cost,X_Burdened_Cost,
918 0,0,
919 V_Qty,V_Qty,0,0,
920 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
921 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
922 Recs_processed := Recs_processed + 1;
923 END LOOP;
924 End If;
925
926 -- This will check for the Project-Resource combination in the Header records
927 -- and if not present create the Header and Detail records for commitments
928
929 Open Proj_Res_level_Cur;
930 Fetch Proj_Res_level_Cur Into V_Accum_Id;
931 If Proj_Res_level_Cur%NOTFOUND Then
932 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
933 From Dual;
934 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
935 (X_project_id,
936 0,
937 x_resource_list_id ,
938 x_resource_list_Member_id ,
939 x_resource_id ,
940 x_resource_list_assignment_id ,
941 x_current_period,
942 v_accum_id,
943 x_err_stack,
944 x_err_stage,
945 x_err_code);
946
947 Recs_processed := Recs_processed + 1;
948 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
949 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
950 CMT_RAW_COST_PTD,
951 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
952 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
953 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
954 CMT_QUANTITY_PTD,
955 CMT_UNIT_OF_MEASURE,
956 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
957 LAST_UPDATE_LOGIN) Values
958 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,0,0,
959 X_Burdened_Cost,X_Burdened_Cost,
960 0,0,
961 V_Qty,V_Qty,0,0,
962 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
963 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
964 Recs_processed := Recs_processed + 1;
965 End If;
966 Close Proj_Res_level_Cur;
967 x_recs_processed := Recs_processed;
968 -- Restore the old x_err_stack;
969 x_err_stack := V_Old_Stack;
970
971 Exception
972 When Others Then
973 x_err_code := SQLCODE;
974 RAISE;
975 End Process_it_yt_cmt_res;
976
977 Procedure Process_it_cmt_res
978 (x_project_id In Number,
979 x_task_id In Number,
980 x_resource_list_id in Number,
981 x_resource_list_Member_id in Number,
982 x_resource_id in Number,
983 x_resource_list_assignment_id in Number,
984 x_track_as_labor_flag In Varchar2,
985 x_rollup_qty_flag In Varchar2,
986 x_unit_of_measure In Varchar2,
987 x_current_period In Varchar2,
988 X_Raw_Cost In Number,
989 X_Burdened_Cost In Number,
990 X_Quantity In Number,
991 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
992 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
993 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
994 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
995
996
997 -- Process_it_cmt_res - Processes ITD amounts in the
998 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
999 -- given Project-Task-Resource combination,records are
1000 -- created/updated and rolled up to all the
1001 -- higher level tasks. The Project-Resource records
1002 -- are also created/updated.
1003
1004 CURSOR Proj_Res_level_Cur IS
1005 SELECT Project_Accum_Id
1006 FROM
1007 PA_PROJECT_ACCUM_HEADERS
1008 WHERE Project_id = X_project_id
1009 AND Task_Id = 0
1010 AND Resource_list_Member_id = X_resource_list_member_id;
1011
1012 Recs_processed Number := 0;
1013 V_Accum_id Number := 0;
1014 V_task_array task_id_tabtype;
1015 v_noof_tasks Number := 0;
1016 V_Qty Number := 0;
1017 Res_Recs_processed Number := 0;
1018 V_Old_Stack Varchar2(630);
1019
1020 Begin
1021
1022 V_Old_Stack := x_err_stack;
1023 x_err_stack :=
1024 x_err_stack ||'->PA_PROCESS_ACCUM_CMT_RES.Process_it_cmt_res';
1025
1026 pa_debug.debug(x_err_stack);
1027
1028 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
1029 -- project,task and resource combination.It is possible that there might be a
1030 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
1031 -- no corresponding detail record. The procedure called below,will
1032 -- check for the existence of the detail records and if not available
1033 -- would create it.
1034
1035 PA_ACCUM_UTILS.Check_Cmt_Details
1036 (x_project_id,
1037 x_task_id,
1038 x_resource_list_Member_id,
1039 Res_recs_processed,
1040 x_err_stack,
1041 x_err_stage,
1042 x_err_code);
1043
1044 Recs_processed := Recs_processed + Res_recs_processed;
1045
1046 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
1047 -- project and Resource combination. It is possible that there might be a
1048 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
1049 -- no corresponding detail record. The procedure called below,will
1050 -- check for the existence of the detail records and if not available
1051 -- would create it.
1052
1053 PA_ACCUM_UTILS.Check_Cmt_Details
1054 (x_project_id,
1055 0,
1056 x_resource_list_Member_id,
1057 Res_recs_processed,
1058 x_err_stack,
1059 x_err_stage,
1060 x_err_code);
1061
1062 Recs_processed := Recs_processed + Res_recs_processed;
1063
1064 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
1065 -- Resource is 'Y'
1066
1067 If x_rollup_qty_flag = 'Y' Then
1068 V_Qty := X_Quantity;
1069 Else
1070 V_Qty := 0;
1071 End If;
1072
1073
1074 -- The follwing Update statement updates all records in the given task
1075 -- WBS hierarchy.It will update only the Project-task-resource combination
1076 -- records and the Project-Resource level record(Task id = 0 and
1077 -- Resourcelist member id <> 0)
1078
1079 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
1080 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
1081 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
1082 CMT_QUANTITY_ITD = CMT_QUANTITY_ITD + V_Qty,
1083 LAST_UPDATE_DATE = Trunc(Sysdate),
1084 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
1085 Where PAA.Project_Accum_id In
1086 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
1087 Where Pah.Project_id = x_project_id and
1088 pah.Resource_list_member_id = x_resource_list_Member_id and
1089 Pah.Task_id in (select 0 from sys.dual union
1090 Select Pt.Task_Id from PA_TASKS pt
1091 start with pt.task_id = x_task_id
1092 connect by prior pt.parent_task_id = pt.task_id));
1093 Recs_processed := Recs_processed + SQL%ROWCOUNT;
1094
1095 -- Initially, the above statement might not Update any rows
1096 -- since the Project-Task-Resource combinations or
1097 -- Project-Resource combinations might not have been created.
1098 -- We shall be creating them below.
1099 -- The following procedure would return all the tasks in the given task
1100 -- WBS hierarchy, including the given task, which do not have a header
1101 -- record . The return parameter is an array of records.
1102
1103 v_noof_tasks := 0;
1104 Get_all_higher_tasks_cmt_res (x_project_id ,
1105 X_task_id ,
1106 x_resource_list_member_id,
1107 v_task_array,
1108 v_noof_tasks,
1109 x_err_stack,
1110 x_err_stage,
1111 x_err_code);
1112
1113
1114 -- If the above procedure had returned any tasks , then we need to insert
1115 -- header record and commitments record.We need to process the tasks one by one
1116 -- since we require the Accum_id for each detail record.
1117 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
1118 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
1119 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
1120 -- in the Project_accum_commitments table. The next time , if the given task
1121 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
1122 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
1123 -- two records would have been processed by the Update statements.
1124
1125 If v_noof_tasks > 0 Then
1126 For i in 1..v_noof_tasks LOOP
1127 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
1128 From Dual;
1129 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
1130 (X_project_id,
1131 v_task_array(i),
1132 x_resource_list_id ,
1133 x_resource_list_Member_id ,
1134 x_resource_id ,
1135 x_resource_list_assignment_id ,
1136 x_current_period,
1137 v_accum_id,
1138 x_err_stack,
1139 x_err_stage,
1140 x_err_code);
1141
1142 Recs_processed := Recs_processed + 1;
1143 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
1144 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
1145 CMT_RAW_COST_PTD,
1146 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
1147 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
1148 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
1149 CMT_QUANTITY_PTD,
1150 CMT_UNIT_OF_MEASURE,
1151 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
1152 LAST_UPDATE_LOGIN) Values
1153 (V_Accum_id,X_Raw_Cost,0,0,0,
1154 X_Burdened_Cost,0,0,0,
1155 V_Qty,0,0,0,
1156 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
1157 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
1158 Recs_processed := Recs_processed + 1;
1159 END LOOP;
1160 End If;
1161
1162 -- This will check for the Project-Resource combination in the Header records
1163 -- and if not present create the Header and Detail records for commitments
1164
1165 Open Proj_Res_level_Cur;
1166 Fetch Proj_Res_level_Cur Into V_Accum_Id;
1167 If Proj_Res_level_Cur%NOTFOUND Then
1168 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
1169 From Dual;
1170 PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res
1171 (X_project_id,
1172 0,
1173 x_resource_list_id ,
1174 x_resource_list_Member_id ,
1175 x_resource_id ,
1176 x_resource_list_assignment_id ,
1177 x_current_period,
1178 v_accum_id,
1179 x_err_stack,
1180 x_err_stage,
1181 x_err_code);
1182
1183 Recs_processed := Recs_processed + 1;
1184 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
1185 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
1186 CMT_RAW_COST_PTD,
1187 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
1188 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
1189 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,
1190 CMT_QUANTITY_PTD,
1191 CMT_UNIT_OF_MEASURE,
1192 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
1193 LAST_UPDATE_LOGIN) Values
1194 (V_Accum_id,X_Raw_Cost,0,0,0,
1195 X_Burdened_Cost,0,0,0,
1196 V_Qty,0,0,0,
1197 X_Unit_Of_Measure,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
1198 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
1199 Recs_processed := Recs_processed + 1;
1200 End If;
1201 Close Proj_Res_level_Cur;
1202 x_recs_processed := Recs_processed;
1203 -- Restore the old x_err_stack;
1204
1205 x_err_stack := V_Old_Stack;
1206 Exception
1207 When Others Then
1208 x_err_code := SQLCODE;
1209 RAISE ;
1210 End Process_it_cmt_res;
1211
1212 Procedure Get_all_higher_tasks_cmt_res (x_project_id in Number,
1213 X_task_id in Number,
1214 x_resource_list_member_id In Number,
1215 x_task_array Out NOCOPY task_id_tabtype, --File.Sql.39 bug 4440895
1216 x_noof_tasks Out NOCOPY number, --File.Sql.39 bug 4440895
1217 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1218 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1219 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
1220
1221 -- Get_all_higher_tasks_cmt_res - For the given Task Id returns all the
1222 -- higher level tasks in the WBS (including the given
1223 -- task) which are not in PA_PROJECT_ACCUM_HEADERS
1224 -- (Tasks with the given Resource )
1225
1226 CURSOR Tasks_Cur IS
1227 SELECT task_id
1228 FROM pa_tasks pt
1229 WHERE project_id = x_project_id
1230 AND NOT EXISTS
1231 (SELECT 'x'
1232 FROM
1233 pa_project_accum_headers pah
1234 WHERE pah.project_id = x_project_id
1235 AND pah.task_id = pt.task_id
1236 AND pah.resource_list_member_id = x_resource_list_member_id)
1237 START WITH task_id = x_task_id
1238 CONNECT BY PRIOR parent_task_id = task_id;
1239
1240 v_noof_tasks Number := 0;
1241 Task_Rec Tasks_Cur%ROWTYPE;
1242
1243 V_Old_Stack Varchar2(630);
1244 Begin
1245
1246 V_Old_Stack := x_err_stack;
1247 x_err_stack :=
1248 x_err_stack ||'->PA_PROCESS_ACCUM_CMT_RES.Get_all_higher_tasks_cmt_res';
1249
1250 pa_debug.debug(x_err_stack);
1251
1252 For Task_Rec IN Tasks_Cur LOOP
1253 v_noof_tasks := v_noof_tasks + 1;
1254 x_task_array(v_noof_tasks) := Task_Rec.Task_id;
1255 END LOOP;
1256 x_noof_tasks := v_noof_tasks;
1257
1258 -- Restore the old x_err_stack;
1259
1260 x_err_stack := V_Old_Stack;
1261 Exception
1262 When Others Then
1263 x_err_code := SQLCODE;
1264 RAISE ;
1265 end Get_all_higher_tasks_cmt_res;
1266
1267 END PA_PROCESS_ACCUM_CMT_RES;
[Home] [Help]