1 PACKAGE BODY PA_PROCESS_ACCUM_COMMITMENTS AS
2 /* $Header: PACMTSKB.pls 120.2 2005/08/31 11:08:20 vmangulu noship $ */
3
4 -- The procedures are called by
5 -- PA_MAINT_PROJECT_COMMITMENTS.Process_Txn_Accum_Cmt
6
7 Procedure Process_it_yt_pt_tasks_cmt
8 (x_project_id In Number,
9 x_task_id In Number,
10 x_Proj_Accum_id In Number,
11 x_current_period In Varchar2,
12 X_Raw_Cost In Number,
13 X_Burdened_Cost In Number,
14 X_Quantity In Number,
15 X_Unit_Of_Measure In Varchar2,
16 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
17 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
18 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
19 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
20
21
22 -- Process_it_yt_pt_tasks_cmt - Processes ITD,YTD and PTD amounts in the
23 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
24 -- given Project-Task combination,records are
25 -- created/updated and rolled up to all the
26 -- higher level tasks.
27 Recs_processed Number := 0;
28 V_Accum_id Number := 0;
29 V_task_array task_id_tabtype;
30 v_noof_tasks Number := 0;
31 other_recs_processed Number := 0;
32 V_Old_Stack Varchar2(630);
33 Begin
34 V_Old_Stack := x_err_stack;
35 x_err_stack :=
36 x_err_stack ||'->PA_PROCESS_ACCUM_COMMITMENTS.Process_it_yt_pt_tasks_cmt';
37
38 pa_debug.debug(x_err_stack);
39
40 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
41 -- project and task combination. It is possible that there might be a
42 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
43 -- no corresponding detail record. The procedure called below,will
44 -- check for the existence of the detail records and if not available
45 -- would create it.
46
47 PA_ACCUM_UTILS.Check_Cmt_Details
48 (x_project_id,
49 x_task_id,
50 0,
51 other_recs_processed,
52 x_err_stack,
53 x_err_stage,
54 x_err_code);
55
56 Recs_processed := Recs_processed + other_recs_processed;
57
58 -- The follwing Update statement updates all records in the given task
59 -- WBS hierarchy.It will update only the Project-task combination records
60 -- and the Project level record (Task id = 0 and Resourcelist member id = 0)
61
62 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
63 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
64 CMT_RAW_COST_YTD = CMT_RAW_COST_YTD + X_Raw_Cost,
65 CMT_RAW_COST_PTD = CMT_RAW_COST_PTD + X_Raw_Cost,
66 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
67 CMT_BURDENED_COST_YTD = CMT_BURDENED_COST_YTD + X_Burdened_Cost,
68 CMT_BURDENED_COST_PTD = CMT_BURDENED_COST_PTD + X_Burdened_Cost,
69 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
70 LAST_UPDATE_DATE = Trunc(Sysdate),
71 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
72 Where PAA.Project_Accum_id In
73 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
74 Where Pah.Project_id = x_project_id and
75 pah.Resource_list_member_id = 0 and
76 Pah.Task_id in (Select Pt.Task_Id from PA_TASKS pt
77 start with pt.task_id = x_task_id
78 connect by prior pt.parent_task_id = pt.task_id)
79 UNION select to_number(X_Proj_accum_id) from sys.dual );
80 Recs_processed := Recs_processed + SQL%ROWCOUNT;
81
82 -- Initially, the above statement might process just one row,the project level
83 -- row, since the Project-Task combinations might not have been created.
84 -- We shall be creating them below.
85
86 -- The following procedure would return all the tasks in the given task
87 -- WBS hierarchy, including the given task, which do not have a header
88 -- record . The return parameter is an array of records.
89
90 Get_all_higher_tasks_cmt
91 (x_project_id ,
92 X_task_id ,
93 v_task_array,
94 v_noof_tasks,
95 x_err_stack,
96 x_err_stage,
97 x_err_code);
98
99 -- If the above procedure had returned any tasks , then we need to insert
100 -- header record and commitments record. We need to process the tasks one by one
101 -- since we require the Accum_id for each detail record.
102 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
103 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
104 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
105 -- in the Project_accum_commitments table. The next time , if the given task
106 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
107 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
108 -- two records would have been processed by the Update statements.
109
110 IF v_noof_tasks > 0 Then
111 For i in 1..v_noof_tasks LOOP
112 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
113 From sys.Dual;
114 PA_MAINT_PROJECT_ACCUMS.Insert_Headers_tasks (X_project_id,
115 v_task_array(i),
116 x_current_period,
117 v_accum_id,
118 x_err_stack,
119 x_err_stage,
120 x_err_code);
121
122 Recs_processed := Recs_processed + 1;
123 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
124 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
125 CMT_RAW_COST_PTD,
126 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
127 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
128 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
129 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
130 CMT_UNIT_OF_MEASURE,
131 LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
132 LAST_UPDATE_LOGIN) Values
133 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,0,X_Raw_Cost,
134 X_Burdened_Cost,X_Burdened_Cost,0,X_Burdened_Cost,
135 0,0,0,0,
136 X_Unit_Of_Measure,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
137 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
138 Recs_processed := Recs_processed + 1;
139 END LOOP;
140
141 END IF;
142 x_recs_processed := Recs_processed;
143 -- Restore the old x_err_stack;
144
145 x_err_stack := V_Old_Stack;
146 Exception
147 When Others Then
148 x_err_code := SQLCODE;
149 RAISE;
150
151 End Process_it_yt_pt_tasks_cmt;
152
153 Procedure Process_it_yt_pp_tasks_cmt
154 (x_project_id In Number,
155 x_task_id In Number,
156 x_Proj_Accum_id In Number,
157 x_current_period In Varchar2,
158 X_Raw_Cost In Number,
159 X_Burdened_Cost In Number,
160 X_Quantity In Number,
161 X_Unit_Of_Measure In Varchar2,
162 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
163 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
164 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
165 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
166
167
168 -- Process_it_yt_pp_tasks_cmt - Processes ITD,YTD and PP amounts in the
169 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
170 -- given Project-Task combination,records are
171 -- created/updated and rolled up to all the
172 -- higher level tasks.
173
174 Recs_processed Number := 0;
175 V_Accum_id Number := 0;
176 V_task_array task_id_tabtype;
177 v_noof_tasks Number := 0;
178 other_recs_processed Number := 0;
179 V_Old_Stack Varchar2(630);
180
181 Begin
182 V_Old_Stack := x_err_stack;
183 x_err_stack :=
184 x_err_stack ||'->PA_PROCESS_ACCUM_COMMITMENTS.Process_it_yt_pp_tasks_cmt';
185
186
187 pa_debug.debug(x_err_stack);
188
189 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
190 -- project and task combination. It is possible that there might be a
191 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
192 -- no corresponding detail record. The procedure called below,will
193 -- check for the existence of the detail records and if not available
194 -- would create it.
195
196 PA_ACCUM_UTILS.Check_Cmt_Details
197 (x_project_id,
198 x_task_id,
199 0,
200 other_recs_processed,
201 x_err_stack,
202 x_err_stage,
203 x_err_code);
204
205 Recs_processed := Recs_processed + other_recs_processed;
206
207 -- The follwing Update statement updates all records in the given task
208 -- WBS hierarchy.It will update only the Project-task combination records
209 -- and the Project level record (Task id = 0 and Resourcelist member id = 0)
210
211 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
212 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
213 CMT_RAW_COST_YTD = CMT_RAW_COST_YTD + X_Raw_Cost,
214 CMT_RAW_COST_PP = CMT_RAW_COST_PP + X_Raw_Cost,
215 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
216 CMT_BURDENED_COST_YTD = CMT_BURDENED_COST_YTD + X_Burdened_Cost,
217 CMT_BURDENED_COST_PP = CMT_BURDENED_COST_PP + X_Burdened_Cost,
218 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
219 LAST_UPDATE_DATE = Trunc(Sysdate),
220 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
221 Where PAA.Project_Accum_id In
222 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
223 Where Pah.Project_id = x_project_id and
224 pah.Resource_list_member_id = 0 and
225 Pah.Task_id in (Select Pt.Task_Id from PA_TASKS pt
226 start with pt.task_id = x_task_id
227 connect by prior pt.parent_task_id = pt.task_id)
228 UNION select to_number(X_Proj_accum_id) from sys.dual );
229 Recs_processed := Recs_processed + SQL%ROWCOUNT;
230
231 -- If the above procedure had returned any tasks , then we need to insert
232 -- header record and commitments record. We need to process the tasks one by one
233 -- since we require the Accum_id for each detail record.
234 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
235 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
236 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
237 -- in the Project_accum_commitments table. The next time , if the given task
238 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
239 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
240 -- two records would have been processed by the Update statements.
241
242 Get_all_higher_tasks_cmt (
243 x_project_id ,
244 X_task_id ,
245 v_task_array,
246 v_noof_tasks,
247 x_err_stack,
248 x_err_stage,
249 x_err_code);
250
251 If v_noof_tasks > 0 Then
252 For i in 1..v_noof_tasks LOOP
253 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
254 From sys.Dual;
255 PA_MAINT_PROJECT_ACCUMS.Insert_Headers_tasks (X_project_id,
256 v_task_array(i),
257 x_current_period,
258 v_accum_id,
259 x_err_stack,
260 x_err_stage,
261 x_err_code);
262
263 Recs_processed := Recs_processed + 1;
264 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
265 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
266 CMT_RAW_COST_PTD,
267 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
268 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
269 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
270 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
271 CMT_UNIT_OF_MEASURE,
272 LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
273 LAST_UPDATE_LOGIN) Values
274 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,X_Raw_Cost,0,
275 X_Burdened_Cost,X_Burdened_Cost,
276 X_Burdened_Cost,0,
277 0,0,
278 0,0,
279 X_Unit_Of_Measure,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
280 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
281 Recs_processed := Recs_processed + 1;
282 END LOOP;
283
284 End If;
285 x_recs_processed := Recs_processed;
286 -- Restore the old x_err_stack;
287
288 x_err_stack := V_Old_Stack;
289 Exception
290 When Others Then
291 x_err_code := SQLCODE;
292 RAISE;
293
294 End Process_it_yt_pp_tasks_cmt;
295
296 Procedure Process_it_pp_tasks_cmt
297 (x_project_id In Number,
298 x_task_id In Number,
299 x_Proj_Accum_id In Number,
300 x_current_period In Varchar2,
301 X_Raw_Cost In Number,
302 X_Burdened_Cost In Number,
303 X_Quantity In Number,
304 X_Unit_Of_Measure In Varchar2,
305 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
306 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
307 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
308 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
309
310 -- Process_it_pp_tasks_cmt - Processes ITD and PP amounts in the
311 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
312 -- given Project-Task combination,records are
313 -- created/updated and rolled up to all the
314 -- higher level tasks.
315
316 Recs_processed Number := 0;
317 V_Accum_id Number := 0;
318 V_task_array task_id_tabtype;
319 v_noof_tasks Number := 0;
320 other_recs_processed Number := 0;
321 V_Old_Stack Varchar2(630);
322
323 Begin
324
325 V_Old_Stack := x_err_stack;
326 x_err_stack :=
327 x_err_stack ||'->PA_PROCESS_ACCUM_COMMITMENTS.Process_it_pp_tasks_cmt';
328
329 pa_debug.debug(x_err_stack);
330
331 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
332 -- project and task combination. It is possible that there might be a
333 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
334 -- no corresponding detail record. The procedure called below,will
335 -- check for the existence of the detail records and if not available
336 -- would create it.
337
338 PA_ACCUM_UTILS.Check_Cmt_Details
339 (x_project_id,
340 x_task_id,
341 0,
342 other_recs_processed,
343 x_err_stack,
344 x_err_stage,
345 x_err_code);
346
347 Recs_processed := Recs_processed + other_recs_processed;
348
349 -- The follwing Update statement updates all records in the given task
350 -- WBS hierarchy.It will update only the Project-task combination records
351 -- and the Project level record (Task id = 0 and Resourcelist member id = 0)
352
353 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
354 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
355 CMT_RAW_COST_PP = CMT_RAW_COST_PP + X_Raw_Cost,
356 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
357 CMT_BURDENED_COST_PP = CMT_BURDENED_COST_PP + X_Burdened_Cost,
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 = 0 and
365 Pah.Task_id in (Select Pt.Task_Id from PA_TASKS pt
366 start with pt.task_id = x_task_id
367 connect by prior pt.parent_task_id = pt.task_id)
368 UNION select to_number(X_Proj_accum_id) from sys.dual );
369 Recs_processed := Recs_processed + SQL%ROWCOUNT;
370
371 -- Initially, the above statement might process just one row,the project level
372 -- row, since the Project-Task combinations might not have been created.
373 -- We shall be creating them below.
374
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 Get_all_higher_tasks_cmt (
380 x_project_id ,
381 X_task_id ,
382 v_task_array,
383 v_noof_tasks,
384 x_err_stack,
385 x_err_stage,
386 x_err_code);
387
388
389 -- If the above procedure had returned any tasks , then we need to insert
390 -- header record and commitments record. We need to process the tasks one by one
391 -- since we require the Accum_id for each detail record.
392 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
393 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
394 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
395 -- in the Project_accum_commitments table. The next time , if the given task
396 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
397 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
398 -- two records would have been processed by the Update statements.
399
400 If v_noof_tasks > 0 Then
401 For i in 1..v_noof_tasks LOOP
402 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
403 From sys.Dual;
404 PA_MAINT_PROJECT_ACCUMS.Insert_Headers_tasks (X_project_id,
405 v_task_array(i),
406 x_current_period,
407 v_accum_id,
408 x_err_stack,
409 x_err_stage,
410 x_err_code);
411
412 Recs_processed := Recs_processed + 1;
413 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
414 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
415 CMT_RAW_COST_PTD,
416 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
417 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
418 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
419 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
420 CMT_UNIT_OF_MEASURE,
421 LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
422 LAST_UPDATE_LOGIN) Values
423 (V_Accum_id,X_Raw_Cost,0,X_Raw_Cost,0,
424 X_Burdened_Cost,0,X_Burdened_Cost,0,
425 0,0,0,0,
426 X_Unit_Of_Measure,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
427 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
428 Recs_processed := Recs_processed + 1;
429 END LOOP;
430 End If;
431 -- Restore the old x_err_stack;
432
433 x_err_stack := V_Old_Stack;
434 x_recs_processed := Recs_processed;
435
436 Exception
437 When Others Then
438 x_err_code := SQLCODE;
439 RAISE;
440
441 End Process_it_pp_tasks_cmt;
442
443 Procedure Process_it_yt_tasks_cmt
444 (x_project_id In Number,
445 x_task_id In Number,
446 x_Proj_Accum_id In Number,
447 x_current_period In Varchar2,
448 X_Raw_Cost In Number,
449 X_Burdened_Cost In Number,
450 X_Quantity In Number,
451 X_Unit_Of_Measure In Varchar2,
452 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
453 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
454 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
455 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
456
457 -- Process_it_yt_tasks_cmt - Processes ITD and YTD amounts in the
458 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
459 -- given Project-Task combination,records are
460 -- created/updated and rolled up to all the
461 -- higher level tasks.
462
463 Recs_processed Number := 0;
464 V_Accum_id Number := 0;
465 V_task_array task_id_tabtype;
466 v_noof_tasks Number := 0;
467 other_recs_processed Number := 0;
468 V_Old_Stack Varchar2(630);
469
470 Begin
471 V_Old_Stack := x_err_stack;
472 x_err_stack :=
473 x_err_stack ||'->PA_PROCESS_ACCUM_COMMITMENTS.Process_it_yt_tasks_cmt';
474
475 pa_debug.debug(x_err_stack);
476
477 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
478 -- project and task combination. It is possible that there might be a
479 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
480 -- no corresponding detail record. The procedure called below,will
481 -- check for the existence of the detail records and if not available
482 -- would create it.
483
484 PA_ACCUM_UTILS.Check_Cmt_Details
485 (x_project_id,
486 x_task_id,
487 0,
488 other_recs_processed,
489 x_err_stack,
490 x_err_stage,
491 x_err_code);
492
493 Recs_processed := Recs_processed + other_recs_processed;
494
495 -- The follwing Update statement updates all records in the given task
496 -- WBS hierarchy.It will update only the Project-task combination records
497 -- and the Project level record (Task id = 0 and Resourcelist member id = 0)
498
499 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
500 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
501 CMT_RAW_COST_YTD = CMT_RAW_COST_YTD + X_Raw_Cost,
502 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
503 CMT_BURDENED_COST_YTD = CMT_BURDENED_COST_YTD + X_Burdened_Cost,
504 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
505 LAST_UPDATE_DATE = Trunc(Sysdate),
506 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
507 Where PAA.Project_Accum_id In
508 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
509 Where Pah.Project_id = x_project_id and
510 pah.Resource_list_member_id = 0 and
511 Pah.Task_id in (Select Pt.Task_Id from PA_TASKS pt
512 start with pt.task_id = x_task_id
513 connect by prior pt.parent_task_id = pt.task_id)
514 UNION select to_number(X_Proj_accum_id) from sys.dual );
515 Recs_processed := Recs_processed + SQL%ROWCOUNT;
516
517 -- Initially, the above statement might process just one row,the project level
518 -- row, since the Project-Task combinations might not have been created.
519 -- We shall be creating them below.
520
521 -- The following procedure would return all the tasks in the given task
522 -- WBS hierarchy, including the given task, which do not have a header
523 -- record . The return parameter is an array of records.
524
525 Get_all_higher_tasks_cmt (
526 x_project_id ,
527 X_task_id ,
528 v_task_array,
529 v_noof_tasks,
530 x_err_stack,
531 x_err_stage,
532 x_err_code);
533
534
535 -- If the above procedure had returned any tasks , then we need to insert
536 -- header record and commitments record. We need to process the tasks one by one
537 -- since we require the Accum_id for each detail record.
538 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
539 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
540 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
541 -- in the Project_accum_commitments table. The next time , if the given task
542 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
543 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
544 -- two records would have been processed by the Update statements.
545
546 If v_noof_tasks > 0 Then
547 For i in 1..v_noof_tasks LOOP
548 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
549 From sys.Dual;
550 PA_MAINT_PROJECT_ACCUMS.Insert_Headers_tasks (X_project_id,
551 v_task_array(i),
552 x_current_period,
553 v_accum_id,
554 x_err_stack,
555 x_err_stage,
556 x_err_code);
557
558 Recs_processed := Recs_processed + 1;
559 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
560 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
561 CMT_RAW_COST_PTD,
562 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
563 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
564 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
565 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
566 CMT_UNIT_OF_MEASURE,
567 LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
568 LAST_UPDATE_LOGIN) Values
569 (V_Accum_id,X_Raw_Cost,X_Raw_Cost,0,0,
570 X_Burdened_Cost,X_Burdened_Cost,0,0,
571 0,0,0,0,
572 X_Unit_Of_Measure,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
573 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
574 Recs_processed := Recs_processed + 1;
575 END LOOP;
576
577 End If;
578 x_recs_processed := Recs_processed;
579 -- Restore the old x_err_stack;
580
581 x_err_stack := V_Old_Stack;
582 Exception
583 When Others Then
584 x_err_code := SQLCODE;
585 RAISE;
586
587 End Process_it_yt_tasks_cmt;
588
589 Procedure Process_it_tasks_cmt
590 (x_project_id In Number,
591 x_task_id In Number,
592 x_Proj_Accum_id In Number,
593 x_current_period In Varchar2,
594 X_Raw_Cost In Number,
595 X_Burdened_Cost In Number,
596 X_Quantity In Number,
597 X_Unit_Of_Measure In Varchar2,
598 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
599 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
600 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
601 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
602
603 -- Process_it_tasks_cmt - Processes ITD amounts in the
604 -- PA_PROJECT_ACCUM_COMMITMENTS table. For the
605 -- given Project-Task combination,records are
606 -- created/updated and rolled up to all the
607 -- higher level tasks.
608 Recs_processed Number := 0;
609 V_Accum_id Number := 0;
610 V_task_array task_id_tabtype;
611 v_noof_tasks Number := 0;
612 other_recs_processed Number := 0;
613 V_Old_Stack Varchar2(630);
614
615 Begin
616
617 V_Old_Stack := x_err_stack;
618 x_err_stack :=
619 x_err_stack ||'->PA_PROCESS_ACCUM_COMMITMENTS.Process_it_tasks_cmt';
620
621 pa_debug.debug(x_err_stack);
622
623 -- This checks for Commitments record in PA_PROJECT_ACCUM_COMMITMENTS for this
624 -- project and task combination. It is possible that there might be a
625 -- header record for this combination in PA_PROJECT_ACCUM_HEADERS, but
626 -- no corresponding detail record. The procedure called below,will
627 -- check for the existence of the detail records and if not available
628 -- would create it.
629
630 PA_ACCUM_UTILS.Check_Cmt_Details
631 (x_project_id,
632 x_task_id,
633 0,
634 other_recs_processed,
635 x_err_stack,
636 x_err_stage,
637 x_err_code);
638
639 Recs_processed := Recs_processed + other_recs_processed;
640
641 -- The follwing Update statement updates all records in the given task
642 -- WBS hierarchy.It will update only the Project-task combination records
643 -- and the Project level record (Task id = 0 and Resourcelist member id = 0)
644
645 Update PA_PROJECT_ACCUM_COMMITMENTS PAA SET
646 CMT_RAW_COST_ITD = CMT_RAW_COST_ITD + X_Raw_Cost,
647 CMT_BURDENED_COST_ITD = CMT_BURDENED_COST_ITD + X_Burdened_Cost,
648 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
649 LAST_UPDATE_DATE = Trunc(Sysdate),
650 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
651 Where PAA.Project_Accum_id In
652 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
653 Where Pah.Project_id = x_project_id and
654 pah.Resource_list_member_id = 0 and
655 Pah.Task_id in (Select Pt.Task_Id from PA_TASKS pt
656 start with pt.task_id = x_task_id
657 connect by prior pt.parent_task_id = pt.task_id)
658 UNION select to_number(X_Proj_accum_id) from sys.dual );
659 Recs_processed := Recs_processed + SQL%ROWCOUNT;
660
661 -- Initially, the above statement might process just one row,the project level
662 -- row, since the Project-Task combinations might not have been created.
663 -- We shall be creating them below.
664
665 -- The following procedure would return all the tasks in the given task
666 -- WBS hierarchy, including the given task, which do not have a header
667 -- record . The return parameter is an array of records.
668
669 Get_all_higher_tasks_cmt (
670 x_project_id ,
671 X_task_id ,
672 v_task_array,
673 v_noof_tasks,
674 x_err_stack,
675 x_err_stage,
676 x_err_code);
677
678
679 -- If the above procedure had returned any tasks , then we need to insert
680 -- header record and commitments record. We need to process the tasks one by one
681 -- since we require the Accum_id for each detail record.
682 -- Eg: If the given task (the one fetched from PA_TXN_ACCUM) was say
683 -- 1.1.1, then the first time, Get_all_higher_tasks would return,
684 -- 1.1.1, 1.1, and 1. We create three header records and three detail records
685 -- in the Project_accum_commitments table. The next time , if the given task
686 -- is 1.1.2, the Get_all_higher_tasks would return only 1.1.2, since
687 -- 1.1 and 1 are already available in the Pa_project_accum_headers. Those
688 -- two records would have been processed by the Update statements.
689
690 If v_noof_tasks > 0 Then
691 For i in 1..v_noof_tasks LOOP
692 Select PA_PROJECT_ACCUM_HEADERS_S.Nextval into V_Accum_id
693 From sys.Dual;
694 PA_MAINT_PROJECT_ACCUMS.Insert_Headers_tasks (X_project_id,
695 v_task_array(i),
696 x_current_period,
697 v_accum_id,
698 x_err_stack,
699 x_err_stage,
700 x_err_code);
701
702 Recs_processed := Recs_processed + 1;
703 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
704 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
705 CMT_RAW_COST_PTD,
706 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
707 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
708 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
709 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
710 CMT_UNIT_OF_MEASURE,
711 LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
712 LAST_UPDATE_LOGIN) Values
713 (V_Accum_id,X_Raw_Cost,0,0,0,
714 X_Burdened_Cost,0,0,0,
715 0,0,0,0,
716 X_Unit_Of_Measure,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
717 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
718 Recs_processed := Recs_processed + 1;
719 END LOOP;
720
721 End If;
722 x_recs_processed := Recs_processed;
723 -- Restore the old x_err_stack;
724
725 x_err_stack := V_Old_Stack;
726 Exception
727 When Others Then
728 x_err_code := SQLCODE;
729 RAISE;
730
731 End Process_it_tasks_cmt;
732
733 Procedure Get_all_higher_tasks_cmt (x_project_id in Number,
734 X_task_id in Number,
735 x_task_array Out NOCOPY task_id_tabtype, --File.Sql.39 bug 4440895
736 x_noof_tasks Out NOCOPY number, --File.Sql.39 bug 4440895
737 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
738 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
739 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
740
741
742 -- This procedure returns all those tasks from PA_TASKS, which do not
743 -- have a record in PA_PROJECT_ACCUM_HEADERS table, with Resource_List_member_id
744 -- (Project-task level numbers without resources )
745
746 CURSOR Tasks_Cur IS
747 SELECT task_id
748 FROM pa_tasks pt
749 WHERE project_id = x_project_id
750 AND NOT EXISTS
751 (SELECT 'x'
752 FROM
753 pa_project_accum_headers pah
754 WHERE pah.project_id = X_project_id
755 AND pah.task_id = pt.task_id
756 AND pah.resource_list_member_id = 0)
757 START WITH task_id = x_task_id
758 CONNECT BY PRIOR parent_task_id = task_id;
759
760 v_noof_tasks Number := 0;
761
762 V_Old_Stack Varchar2(630);
763 Task_Rec Tasks_Cur%ROWTYPE;
764 Begin
765 V_Old_Stack := x_err_stack;
766 x_err_stack :=
767 x_err_stack ||'->PA_PROCESS_ACCUM_COMMITMENTS.Get_all_higher_tasks_cmt';
768 For Task_Rec IN Tasks_Cur LOOP
769 v_noof_tasks := v_noof_tasks + 1;
770 x_task_array(v_noof_tasks) := Task_Rec.Task_id;
771
772 END LOOP;
773
774 x_noof_tasks := v_noof_tasks;
775 -- Restore the old x_err_stack;
776
777 x_err_stack := V_Old_Stack;
778
779 Exception
780 When Others Then
781 x_err_code := SQLCODE;
782 RAISE;
783
784 end Get_all_higher_tasks_cmt;
785
786 END PA_PROCESS_ACCUM_COMMITMENTS;
[Home] [Help]