[Home] [Help]
PACKAGE BODY: APPS.PA_PROCESS_ACCUM_ACTUALS_RES
Source
1 PACKAGE BODY PA_PROCESS_ACCUM_ACTUALS_RES AS
2 /* $Header: PAACRESB.pls 120.2 2005/08/31 11:08:00 vmangulu noship $ */
3
4 -- This Procedure - Processes ITD,YTD and PTD amounts in the
5 -- PA_PROJECT_ACCUM_ACTUALS table. For the
9 -- are also created/updated.
6 -- given Project-Task-Resource combination,records are
7 -- created/updated and rolled up to all the
8 -- higher level tasks. The Project-Resource records
10
11 Procedure Process_it_yt_pt_res
12 (x_project_id In Number,
13 x_task_id In Number,
14 x_resource_list_id in Number,
15 x_resource_list_Member_id in Number,
16 x_resource_id in Number,
17 x_resource_list_assignment_id in Number,
18 x_track_as_labor_flag In Varchar2,
19 x_rollup_Qty_flag In Varchar2,
20 x_unit_of_measure In Varchar2,
21 x_current_period In Varchar2,
22 X_Revenue In Number,
23 X_Raw_Cost In Number,
24 X_Burdened_Cost In Number,
25 X_Labor_Hours In Number,
26 X_Quantity In Number,
27 X_Billable_Quantity In Number,
28 X_Billable_Raw_Cost In Number,
29 X_Billable_Burdened_Cost In Number,
30 X_Billable_Labor_Hours In Number,
31 x_actual_cost_flag In Varchar2,
32 x_revenue_flag In Varchar2,
33 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
34 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
35 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
36 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
37
38
39 Recs_processed Number := 0;
40 Res_Recs_processed Number := 0;
41 V_Old_Stack Varchar2(630);
42
43 Begin
44 V_Old_Stack := x_err_stack;
45 x_err_stack :=
46 X_err_stack ||'->PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_yt_pt_res';
47 pa_debug.debug(x_err_stack);
48
49 -- The follwing Update statement updates all records in the given task
50 -- WBS hierarchy.It will update only the Project-task-resource combination
51 -- records and the Project-Resource level record(Task id = 0 and
52 -- Resourcelist member id <> 0)
53
54 IF(x_actual_cost_flag = 'Y') THEN
55 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
56 RAW_COST_ITD = RAW_COST_ITD + X_Raw_Cost,
57 RAW_COST_YTD = RAW_COST_YTD + X_Raw_Cost,
58 RAW_COST_PTD = RAW_COST_PTD + X_Raw_Cost,
59 BILLABLE_RAW_COST_ITD = BILLABLE_RAW_COST_ITD +
60 X_Billable_Raw_Cost,
61 BILLABLE_RAW_COST_YTD = BILLABLE_RAW_COST_YTD +
62 X_Billable_Raw_Cost,
63 BILLABLE_RAW_COST_PTD = BILLABLE_RAW_COST_PTD +
64 X_Billable_Raw_Cost,
65 BURDENED_COST_ITD = BURDENED_COST_ITD + X_Burdened_Cost,
66 BURDENED_COST_YTD = BURDENED_COST_YTD + X_Burdened_Cost,
67 BURDENED_COST_PTD = BURDENED_COST_PTD + X_Burdened_Cost,
68 BILLABLE_BURDENED_COST_ITD = BILLABLE_BURDENED_COST_ITD +
69 X_Billable_Burdened_Cost,
70 BILLABLE_BURDENED_COST_YTD = BILLABLE_BURDENED_COST_YTD +
71 X_Billable_Burdened_Cost,
72 BILLABLE_BURDENED_COST_PTD = BILLABLE_BURDENED_COST_PTD +
73 X_Billable_Burdened_Cost,
74 LABOR_HOURS_ITD = LABOR_HOURS_ITD + X_Labor_Hours,
75 LABOR_HOURS_YTD = LABOR_HOURS_YTD + X_Labor_Hours,
76 LABOR_HOURS_PTD = LABOR_HOURS_PTD + X_Labor_Hours,
77 BILLABLE_LABOR_HOURS_ITD = BILLABLE_LABOR_HOURS_ITD +
78 X_Billable_Labor_Hours,
79 BILLABLE_LABOR_HOURS_YTD = BILLABLE_LABOR_HOURS_YTD +
80 X_Billable_Labor_Hours,
81 BILLABLE_LABOR_HOURS_PTD = BILLABLE_LABOR_HOURS_PTD +
82 X_Billable_Labor_Hours,
83 QUANTITY_ITD = QUANTITY_ITD + X_Quantity,
84 QUANTITY_YTD = QUANTITY_YTD + X_Quantity,
85 QUANTITY_PTD = QUANTITY_PTD + X_Quantity,
86 BILLABLE_QUANTITY_ITD = BILLABLE_QUANTITY_ITD + X_Billable_Quantity,
87 BILLABLE_QUANTITY_YTD = BILLABLE_QUANTITY_YTD + X_Billable_Quantity,
88 BILLABLE_QUANTITY_PTD = BILLABLE_QUANTITY_PTD + X_Billable_Quantity,
89 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
90 LAST_UPDATE_DATE = Trunc(Sysdate),
91 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
92 Where PAA.Project_Accum_id In
93 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
94 Where Pah.Project_id = x_project_id and
95 pah.Resource_list_member_id = x_resource_list_Member_id and
96 Pah.Task_id in (select 0 from sys.dual union
97 Select Pt.Task_Id from PA_TASKS pt
98 start with pt.task_id = x_task_id
99 connect by prior pt.parent_task_id = pt.task_id));
100 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
101 END IF;
102 IF(x_revenue_flag = 'Y') THEN
103 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
104 REVENUE_ITD = REVENUE_ITD + X_Revenue,
105 REVENUE_YTD = REVENUE_YTD + X_Revenue,
106 REVENUE_PTD = REVENUE_PTD + X_Revenue,
107 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
108 LAST_UPDATE_DATE = Trunc(Sysdate),
109 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
110 Where PAA.Project_Accum_id In
111 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
112 Where Pah.Project_id = x_project_id and
113 pah.Resource_list_member_id = x_resource_list_Member_id and
114 Pah.Task_id in (select 0 from sys.dual union
115 Select Pt.Task_Id from PA_TASKS pt
116 start with pt.task_id = x_task_id
117 connect by prior pt.parent_task_id = pt.task_id));
118 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
119 END IF;
120
121 -- Restore the old x_err_stack;
122 x_err_stack := V_Old_Stack;
123 Exception
124 When Others Then
125 x_err_code := SQLCODE;
126 RAISE;
127 End Process_it_yt_pt_res;
128
129 -- This procedure - Processes ITD,YTD and PP amounts in the
130 -- PA_PROJECT_ACCUM_ACTUALS table. For the
131 -- given Project-Task-Resource combination,records are
132 -- created/updated and rolled up to all the
133 -- higher level tasks. The Project-Resource records
134 -- are also created/updated.
135
136 Procedure Process_it_yt_pp_res
137 (x_project_id In Number,
138 x_task_id In Number,
139 x_resource_list_id in Number,
140 x_resource_list_Member_id in Number,
141 x_resource_id in Number,
142 x_resource_list_assignment_id in Number,
143 x_track_as_labor_flag In Varchar2,
144 x_rollup_Qty_flag In Varchar2,
145 x_unit_of_measure In Varchar2,
146 x_current_period In Varchar2,
147 X_Revenue In Number,
148 X_Raw_Cost In Number,
149 X_Burdened_Cost In Number,
150 X_Labor_Hours In Number,
151 X_Quantity In Number,
152 X_Billable_Quantity In Number,
153 X_Billable_Raw_Cost In Number,
154 X_Billable_Burdened_Cost In Number,
155 X_Billable_Labor_Hours In Number,
156 x_actual_cost_flag In Varchar2,
157 x_revenue_flag In Varchar2,
158 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
159 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
160 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
161 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
162
163 Recs_processed Number := 0;
164 Res_Recs_processed Number := 0;
165 V_Old_Stack Varchar2(630);
166
167 Begin
168
169 V_Old_Stack := x_err_stack;
170 x_err_stack :=
171 X_err_stack ||'->PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_yt_pp_res';
172
173 pa_debug.debug(x_err_stack);
174
175 -- The follwing Update statement updates all records in the given task
176 -- WBS hierarchy.It will update only the Project-task-resource combination
177 -- records and the Project-Resource level record(Task id = 0 and
178 -- Resourcelist member id <> 0)
179 IF(x_actual_cost_flag = 'Y') THEN
180 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
181 RAW_COST_ITD = RAW_COST_ITD + X_Raw_Cost,
182 RAW_COST_YTD = RAW_COST_YTD + X_Raw_Cost,
183 RAW_COST_PP = RAW_COST_PP + X_Raw_Cost,
184 BILLABLE_RAW_COST_ITD = BILLABLE_RAW_COST_ITD +
185 X_Billable_Raw_Cost,
186 BILLABLE_RAW_COST_YTD = BILLABLE_RAW_COST_YTD +
187 X_Billable_Raw_Cost,
188 BILLABLE_RAW_COST_PP = BILLABLE_RAW_COST_PP +
189 X_Billable_Raw_Cost,
190 BURDENED_COST_ITD = BURDENED_COST_ITD + X_Burdened_Cost,
191 BURDENED_COST_YTD = BURDENED_COST_YTD + X_Burdened_Cost,
192 BURDENED_COST_PP = BURDENED_COST_PP + X_Burdened_Cost,
193 BILLABLE_BURDENED_COST_ITD = BILLABLE_BURDENED_COST_ITD +
194 X_Billable_Burdened_Cost,
195 BILLABLE_BURDENED_COST_YTD = BILLABLE_BURDENED_COST_YTD +
196 X_Billable_Burdened_Cost,
197 BILLABLE_BURDENED_COST_PP = BILLABLE_BURDENED_COST_PP +
198 X_Billable_Burdened_Cost,
199 LABOR_HOURS_ITD = LABOR_HOURS_ITD + X_Labor_Hours,
200 LABOR_HOURS_YTD = LABOR_HOURS_YTD + X_Labor_Hours,
201 LABOR_HOURS_PP = LABOR_HOURS_PP + X_Labor_Hours,
202 BILLABLE_LABOR_HOURS_ITD = BILLABLE_LABOR_HOURS_ITD +
203 X_Billable_Labor_Hours,
204 BILLABLE_LABOR_HOURS_YTD = BILLABLE_LABOR_HOURS_YTD +
205 X_Billable_Labor_Hours,
206 BILLABLE_LABOR_HOURS_PP = BILLABLE_LABOR_HOURS_PP +
207 X_Billable_Labor_Hours,
208 QUANTITY_ITD = QUANTITY_ITD + X_Quantity,
209 QUANTITY_YTD = QUANTITY_YTD + X_Quantity,
210 QUANTITY_PP = QUANTITY_PP + X_Quantity,
211 BILLABLE_QUANTITY_ITD = BILLABLE_QUANTITY_ITD +
212 X_Billable_Quantity,
213 BILLABLE_QUANTITY_YTD = BILLABLE_QUANTITY_YTD +
214 X_Billable_Quantity,
215 BILLABLE_QUANTITY_PP = BILLABLE_QUANTITY_PP +
216 X_Billable_Quantity,
217 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
218 LAST_UPDATE_DATE = Trunc(Sysdate),
219 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
220 Where PAA.Project_Accum_id In
221 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
222 Where Pah.Project_id = x_project_id and
223 pah.Resource_list_member_id = x_resource_list_Member_id and
224 Pah.Task_id in (select 0 from sys.dual union
225 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 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
229 END IF;
230 IF(x_revenue_flag = 'Y') THEN
231 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
232 REVENUE_ITD = REVENUE_ITD + X_Revenue,
233 REVENUE_YTD = REVENUE_YTD + X_Revenue,
234 REVENUE_PP = REVENUE_PP + X_Revenue,
235 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
236 LAST_UPDATE_DATE = Trunc(Sysdate),
237 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
238 Where PAA.Project_Accum_id In
239 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
240 Where Pah.Project_id = x_project_id and
241 pah.Resource_list_member_id = x_resource_list_Member_id and
242 Pah.Task_id in (select 0 from sys.dual
243 union Select Pt.Task_Id from PA_TASKS pt
244 start with pt.task_id = x_task_id
245 connect by prior pt.parent_task_id = pt.task_id));
246 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
247 END IF;
248
249 -- Restore the old x_err_stack;
250 x_err_stack := V_Old_Stack;
251 Exception
252 When Others Then
253 x_err_code := SQLCODE;
254 RAISE;
255
256 End Process_it_yt_pp_res;
257
258 -- Process_it_pp_res - Processes ITD and PP amounts in the
259 -- PA_PROJECT_ACCUM_ACTUALS table. For the
260 -- given Project-Task-Resource combination,records are
261 -- created/updated and rolled up to all the
262 -- higher level tasks. The Project-Resource records
263 -- are also created/updated.
264
265 Procedure Process_it_pp_res
266 (x_project_id In Number,
267 x_task_id In Number,
268 x_resource_list_id in Number,
269 x_resource_list_Member_id in Number,
270 x_resource_id in Number,
271 x_resource_list_assignment_id in Number,
272 x_track_as_labor_flag In Varchar2,
273 x_rollup_Qty_flag In Varchar2,
274 x_unit_of_measure In Varchar2,
275 x_current_period In Varchar2,
276 X_Revenue In Number,
277 X_Raw_Cost In Number,
278 X_Burdened_Cost In Number,
279 X_Labor_Hours In Number,
280 X_Quantity In Number,
281 X_Billable_Quantity In Number,
282 X_Billable_Raw_Cost In Number,
283 X_Billable_Burdened_Cost In Number,
284 X_Billable_Labor_Hours In Number,
285 x_actual_cost_flag In Varchar2,
286 x_revenue_flag In Varchar2,
287 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
288 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
289 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
290 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
291
292 Recs_processed Number := 0;
293 Res_Recs_processed Number := 0;
294 V_Old_Stack Varchar2(630);
295
296 Begin
297 V_Old_Stack := x_err_stack;
298 x_err_stack :=
299 X_err_stack ||'->PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_pp_res';
300
301 pa_debug.debug(x_err_stack);
302
303 -- The follwing Update statement updates all records in the given task
304 -- WBS hierarchy.It will update only the Project-task-resource combination
305 -- records and the Project-Resource level record(Task id = 0 and
306 -- Resourcelist member id <> 0)
307 IF(x_actual_cost_flag = 'Y') THEN
308 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
309 RAW_COST_ITD = RAW_COST_ITD + X_Raw_Cost,
310 RAW_COST_PP = RAW_COST_PP + X_Raw_Cost,
311 BILLABLE_RAW_COST_ITD = BILLABLE_RAW_COST_ITD +
312 X_Billable_Raw_Cost,
313 BILLABLE_RAW_COST_PP = BILLABLE_RAW_COST_PP +
314 X_Billable_Raw_Cost,
315 BURDENED_COST_ITD = BURDENED_COST_ITD + X_Burdened_Cost,
316 BURDENED_COST_PP = BURDENED_COST_PP + X_Burdened_Cost,
317 BILLABLE_BURDENED_COST_ITD = BILLABLE_BURDENED_COST_ITD +
318 X_Billable_Burdened_Cost,
319 BILLABLE_BURDENED_COST_PP = BILLABLE_BURDENED_COST_PP +
320 X_Billable_Burdened_Cost,
321 LABOR_HOURS_ITD = LABOR_HOURS_ITD + X_Labor_Hours,
322 LABOR_HOURS_PP = LABOR_HOURS_PP + X_Labor_Hours,
323 BILLABLE_LABOR_HOURS_ITD = BILLABLE_LABOR_HOURS_ITD +
324 X_Billable_Labor_Hours,
325 BILLABLE_LABOR_HOURS_PP = BILLABLE_LABOR_HOURS_PP +
326 X_Billable_Labor_Hours,
327 QUANTITY_ITD = QUANTITY_ITD + X_Quantity,
328 QUANTITY_PP = QUANTITY_PP + X_Quantity,
329 BILLABLE_QUANTITY_ITD = BILLABLE_QUANTITY_ITD + X_Billable_Quantity,
330 BILLABLE_QUANTITY_PP = BILLABLE_QUANTITY_PP + X_Billable_Quantity,
331 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
332 LAST_UPDATE_DATE = Trunc(Sysdate),
333 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
334 Where PAA.Project_Accum_id In
335 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
336 Where Pah.Project_id = x_project_id and
337 pah.Resource_list_member_id = x_resource_list_Member_id and
338 Pah.Task_id in (select 0 from sys.dual union
339 Select Pt.Task_Id from PA_TASKS pt
340 start with pt.task_id = x_task_id
341 connect by prior pt.parent_task_id = pt.task_id));
342 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
343 END IF;
344 IF(x_revenue_flag = 'Y') THEN
345 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
346 REVENUE_ITD = REVENUE_ITD + X_Revenue,
347 REVENUE_PP = REVENUE_PP + X_Revenue,
348 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
349 LAST_UPDATE_DATE = Trunc(Sysdate),
350 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
351 Where PAA.Project_Accum_id In
352 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
353 Where Pah.Project_id = x_project_id and
354 pah.Resource_list_member_id = x_resource_list_Member_id and
355 Pah.Task_id in (select 0 from sys.dual union
356 Select Pt.Task_Id from PA_TASKS pt
360 END IF;
357 start with pt.task_id = x_task_id
358 connect by prior pt.parent_task_id = pt.task_id));
359 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
361
362 -- Restore the old x_err_stack;
363 x_err_stack := V_Old_Stack;
364 Exception
365 When Others Then
366 x_err_code := SQLCODE;
367 RAISE ;
368 End Process_it_pp_res;
369
370 -- This procedure - Processes ITD and YTD amounts in the
371 -- PA_PROJECT_ACCUM_ACTUALS table. For the
372 -- given Project-Task-Resource combination,records are
373 -- created/updated and rolled up to all the
374 -- higher level tasks. The Project-Resource records
375 -- are also created/updated.
376
377 Procedure Process_it_yt_res
378 (x_project_id In Number,
379 x_task_id In Number,
380 x_resource_list_id in Number,
381 x_resource_list_Member_id in Number,
382 x_resource_id in Number,
383 x_resource_list_assignment_id in Number,
384 x_track_as_labor_flag In Varchar2,
385 x_rollup_Qty_flag In Varchar2,
386 x_unit_of_measure In Varchar2,
387 x_current_period In Varchar2,
388 X_Revenue In Number,
389 X_Raw_Cost In Number,
390 X_Burdened_Cost In Number,
391 X_Labor_Hours In Number,
392 X_Quantity In Number,
393 X_Billable_Quantity In Number,
394 X_Billable_Raw_Cost In Number,
395 X_Billable_Burdened_Cost In Number,
396 X_Billable_Labor_Hours In Number,
397 x_actual_cost_flag In Varchar2,
398 x_revenue_flag In Varchar2,
399 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
400 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
401 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
402 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
403
404 Recs_processed Number := 0;
405 Res_Recs_processed Number := 0;
406 V_Old_Stack Varchar2(630);
407 Begin
408 V_Old_Stack := x_err_stack;
409 x_err_stack :=
410 X_err_stack ||'->PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_yt_res';
411
412 pa_debug.debug(x_err_stack);
413
414 -- The follwing Update statement updates all records in the given task
415 -- WBS hierarchy.It will update only the Project-task-resource combination
416 -- records and the Project-Resource level record(Task id = 0 and
417 -- Resourcelist member id <> 0)
418 IF(x_actual_cost_flag = 'Y') THEN
419 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
420 RAW_COST_ITD = RAW_COST_ITD + X_Raw_Cost,
421 RAW_COST_YTD = RAW_COST_YTD + X_Raw_Cost,
422 BILLABLE_RAW_COST_ITD = BILLABLE_RAW_COST_ITD +
423 X_Billable_Raw_Cost,
424 BILLABLE_RAW_COST_YTD = BILLABLE_RAW_COST_YTD +
425 X_Billable_Raw_Cost,
426 BURDENED_COST_ITD = BURDENED_COST_ITD + X_Burdened_Cost,
427 BURDENED_COST_YTD = BURDENED_COST_YTD + X_Burdened_Cost,
428 BILLABLE_BURDENED_COST_ITD = BILLABLE_BURDENED_COST_ITD +
429 X_Billable_Burdened_Cost,
430 BILLABLE_BURDENED_COST_YTD = BILLABLE_BURDENED_COST_YTD +
431 X_Billable_Burdened_Cost,
432 LABOR_HOURS_ITD = LABOR_HOURS_ITD + X_Labor_Hours,
433 LABOR_HOURS_YTD = LABOR_HOURS_YTD + X_Labor_Hours,
434 BILLABLE_LABOR_HOURS_ITD = BILLABLE_LABOR_HOURS_ITD +
435 X_Billable_Labor_Hours,
436 BILLABLE_LABOR_HOURS_YTD = BILLABLE_LABOR_HOURS_YTD +
437 X_Billable_Labor_Hours,
438 QUANTITY_ITD = QUANTITY_ITD + X_Quantity,
439 QUANTITY_YTD = QUANTITY_YTD + X_Quantity,
440 BILLABLE_QUANTITY_ITD = BILLABLE_QUANTITY_ITD + X_Billable_Quantity,
441 BILLABLE_QUANTITY_YTD = BILLABLE_QUANTITY_YTD + X_Billable_Quantity,
442 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
443 LAST_UPDATE_DATE = Trunc(Sysdate),
444 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
445 Where PAA.Project_Accum_id In
446 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
447 Where Pah.Project_id = x_project_id and
448 pah.Resource_list_member_id = x_resource_list_Member_id and
449 Pah.Task_id in (select 0 from sys.dual union
450 Select Pt.Task_Id from PA_TASKS pt
451 start with pt.task_id = x_task_id
452 connect by prior pt.parent_task_id = pt.task_id));
453 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
454 END IF;
455 IF(x_revenue_flag = 'Y') THEN
456 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
457 REVENUE_ITD = REVENUE_ITD + X_Revenue,
458 REVENUE_YTD = REVENUE_YTD + X_Revenue,
459 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
460 LAST_UPDATE_DATE = Trunc(Sysdate),
461 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
462 Where PAA.Project_Accum_id In
463 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
464 Where Pah.Project_id = x_project_id and
465 pah.Resource_list_member_id = x_resource_list_Member_id and
466 Pah.Task_id in (select 0 from sys.dual union
467 Select Pt.Task_Id from PA_TASKS pt
468 start with pt.task_id = x_task_id
469 connect by prior pt.parent_task_id = pt.task_id));
470 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
471 END IF;
472
473 -- Restore the old x_err_stack;
474 x_err_stack := V_Old_Stack;
475
476 Exception
477 When Others Then
478 x_err_code := SQLCODE;
479 RAISE ;
480 End Process_it_yt_res;
481
482 -- This procedure - Processes ITD amounts in the
483 -- PA_PROJECT_ACCUM_ACTUALS table. For the
484 -- given Project-Task-Resource combination,records are
485 -- created/updated and rolled up to all the
486 -- higher level tasks. The Project-Resource records
487 -- are also created/updated.
488
489 Procedure Process_it_res
490 (x_project_id In Number,
491 x_task_id In Number,
492 x_resource_list_id in Number,
493 x_resource_list_Member_id in Number,
494 x_resource_id in Number,
495 x_resource_list_assignment_id in Number,
496 x_track_as_labor_flag In Varchar2,
497 x_rollup_Qty_flag In Varchar2,
498 x_unit_of_measure In Varchar2,
499 x_current_period In Varchar2,
500 X_Revenue In Number,
501 X_Raw_Cost In Number,
502 X_Burdened_Cost In Number,
503 X_Labor_Hours In Number,
504 X_Quantity In Number,
505 X_Billable_Quantity In Number,
506 X_Billable_Raw_Cost In Number,
507 X_Billable_Burdened_Cost In Number,
508 X_Billable_Labor_Hours In Number,
509 x_actual_cost_flag In Varchar2,
510 x_revenue_flag In Varchar2,
511 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
512 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
513 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
514 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
515
516 Recs_processed Number := 0;
517 Res_Recs_processed Number := 0;
518 V_Old_Stack Varchar2(630);
519 Begin
520 V_Old_Stack := x_err_stack;
521 x_err_stack :=
522 X_err_stack ||'->PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_res';
523
524 pa_debug.debug(x_err_stack);
525
526 -- The follwing Update statement updates all records in the given task
527 -- WBS hierarchy.It will update only the Project-task-resource combination
528 -- records and the Project-Resource level record(Task id = 0 and
529 -- Resourcelist member id <> 0)
530 IF(x_actual_cost_flag = 'Y') THEN
531 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
532 RAW_COST_ITD = RAW_COST_ITD + X_Raw_Cost,
533 BILLABLE_RAW_COST_ITD = BILLABLE_RAW_COST_ITD +
534 X_Billable_Raw_Cost,
535 BURDENED_COST_ITD = BURDENED_COST_ITD + X_Burdened_Cost,
536 BILLABLE_BURDENED_COST_ITD = BILLABLE_BURDENED_COST_ITD +
537 X_Billable_Burdened_Cost,
538 LABOR_HOURS_ITD = LABOR_HOURS_ITD + X_Labor_Hours,
539 BILLABLE_LABOR_HOURS_ITD = BILLABLE_LABOR_HOURS_ITD +
540 X_Billable_Labor_Hours,
541 QUANTITY_ITD = QUANTITY_ITD + X_Quantity,
542 BILLABLE_QUANTITY_ITD = BILLABLE_QUANTITY_ITD + X_Billable_Quantity,
543 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
544 LAST_UPDATE_DATE = Trunc(Sysdate),
545 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
546 Where PAA.Project_Accum_id In
547 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
548 Where Pah.Project_id = x_project_id and
549 pah.Resource_list_member_id = x_resource_list_Member_id and
550 Pah.Task_id in (select 0 from sys.dual union
551 Select Pt.Task_Id from PA_TASKS pt
552 start with pt.task_id = x_task_id
553 connect by prior pt.parent_task_id = pt.task_id));
554 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
555 END IF;
556 IF(x_revenue_flag = 'Y') THEN
557 Update PA_PROJECT_ACCUM_ACTUALS PAA SET
558 REVENUE_ITD = REVENUE_ITD + X_Revenue,
559 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
560 LAST_UPDATE_DATE = Trunc(Sysdate),
561 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
562 Where PAA.Project_Accum_id In
563 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
564 Where Pah.Project_id = x_project_id and
565 pah.Resource_list_member_id = x_resource_list_Member_id and
566 Pah.Task_id in (select 0 from sys.dual union
567 Select Pt.Task_Id from PA_TASKS pt
568 start with pt.task_id = x_task_id
569 connect by prior pt.parent_task_id = pt.task_id));
570 x_recs_processed := Recs_processed + SQL%ROWCOUNT;
571 END IF;
572
573 -- Restore the old x_err_stack;
574 x_err_stack := V_Old_Stack;
575 Exception
576 When Others Then
577 x_err_code := SQLCODE;
578 RAISE ;
579 End Process_it_res;
580
581 Procedure Insert_Headers_res (X_project_id In Number,
582 x_task_id In Number,
583 x_resource_list_id in Number,
584 x_resource_list_Member_id in Number,
585 x_resource_id in Number,
586 x_resource_list_assignment_id in Number,
587 x_current_period In Varchar2,
588 x_accum_id In Number,
589 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
590 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
591 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
592
593 -- Insert_Headers_res - Inserts Header records in the
594 -- PA_PROJECT_ACCUM_HEADERS table for the given
595 -- Project-Task-Resource combination
596
597 V_Old_Stack Varchar2(630);
598 Begin
599
600 V_Old_Stack := x_err_stack;
601 x_err_stack :=
602 x_err_stack||'->PA_PROCESS_ACCUM_ACTUALS_RES.Insert_Headers_res';
603 pa_debug.debug(x_err_stack);
604
605 Insert into PA_PROJECT_ACCUM_HEADERS
606 (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
607 RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
608 RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
609 REQUEST_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN )
610 Values (x_Accum_id,X_project_id,x_task_id,
611 x_current_period,
612 x_resource_id,x_resource_list_id,
613 x_resource_list_assignment_id,x_resource_list_Member_id,
614 pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),pa_proj_accum_main.x_request_id,trunc(sysdate),
615 pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login );
616
617 -- Restore the old x_err_stack;
618 x_err_stack := V_Old_Stack;
619 Exception when dup_val_on_index then
620 null;
621 When Others Then
622 x_err_code := SQLCODE;
623 RAISE ;
624 End Insert_Headers_res ;
625
626 END PA_PROCESS_ACCUM_ACTUALS_RES;