[Home] [Help]
PACKAGE BODY: APPS.PA_PROCESS_ACCUM_BUDGETS_RES
Source
1 PACKAGE BODY PA_PROCESS_ACCUM_BUDGETS_RES AS
2 /* $Header: PABURESB.pls 120.2 2005/08/31 11:08:08 vmangulu noship $ */
3
4 -- Modified on 10/29/98 by S Sanckar to include a new procedure
5 -- Process_all_res_bud that updates the raw_cost, burdened_cost, quantity,
6 -- labor_quantity and revenue values for baselined and original budgets
7 -- This will update the amount columns for period_to_date and prior_period
8 -- and year_to_date apart from inception_to_date periods.
9
10 Procedure Process_all_res_bud
11 (x_project_id In Number,
12 x_task_id In Number,
13 x_resource_list_id In Number,
14 x_resource_list_Member_id In Number,
15 x_resource_id In Number,
16 x_resource_list_assignment_id In Number,
17 x_rollup_qty_flag In Varchar2,
18 x_budget_type_code In Varchar2,
19 X_Base_Raw_Cost_ptd In Number,
20 X_Base_Burdened_Cost_ptd In Number,
21 X_Base_Revenue_ptd In Number,
22 X_Base_Quantity_ptd In Number,
23 X_Base_Labor_Hours_ptd In Number,
24 X_Base_Raw_Cost_pp In Number,
25 X_Base_Burdened_Cost_pp In Number,
26 X_Base_Revenue_pp In Number,
27 X_Base_Quantity_pp In Number,
28 X_Base_Labor_Hours_pp In Number,
29 X_Base_Raw_Cost_ytd In Number,
30 X_Base_Burdened_Cost_ytd In Number,
31 X_Base_Revenue_ytd In Number,
32 X_Base_Quantity_ytd In Number,
33 X_Base_Labor_Hours_ytd In Number,
34 X_Base_Raw_Cost_itd In Number,
35 X_Base_Burdened_Cost_itd In Number,
36 X_Base_Revenue_itd In Number,
37 X_Base_Quantity_itd In Number,
38 X_Base_Labor_Hours_itd In Number,
39 X_Base_Unit_Of_Measure In Varchar2,
40 X_Orig_Raw_Cost_ptd In Number,
41 X_Orig_Burdened_Cost_ptd In Number,
42 X_Orig_Revenue_ptd In Number,
43 X_Orig_Quantity_ptd In Number,
44 X_Orig_Labor_Hours_ptd In Number,
45 X_Orig_Raw_Cost_pp In Number,
46 X_Orig_Burdened_Cost_pp In Number,
47 X_Orig_Revenue_pp In Number,
48 X_Orig_Quantity_pp In Number,
49 X_Orig_Labor_Hours_pp In Number,
50 X_Orig_Raw_Cost_ytd In Number,
51 X_Orig_Burdened_Cost_ytd In Number,
52 X_Orig_Revenue_ytd In Number,
53 X_Orig_Quantity_ytd In Number,
54 X_Orig_Labor_Hours_ytd In Number,
55 X_Orig_Raw_Cost_itd In Number,
56 X_Orig_Burdened_Cost_itd In Number,
57 X_Orig_Revenue_itd In Number,
58 X_Orig_Quantity_itd In Number,
59 X_Orig_Labor_Hours_itd In Number,
60 X_Orig_Unit_Of_Measure In Varchar2,
61 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
62 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
63 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
64 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
65
66 Recs_processed Number := 0;
67 V_Accum_id Number := 0;
68 V_task_array task_id_tabtype;
69 v_noof_tasks Number := 0;
70 V_Orig_Qty_ptd Number := 0;
71 V_Orig_Qty_pp Number := 0;
72 V_Orig_Qty_ytd Number := 0;
73 V_Orig_Qty_itd Number := 0;
74 V_Base_Qty_ptd Number := 0;
75 V_Base_Qty_pp Number := 0;
76 V_Base_Qty_ytd Number := 0;
77 V_Base_Qty_itd Number := 0;
78 Res_Recs_processed Number := 0;
79 V_Old_Stack Varchar2(630);
80
81 Begin
82
83 V_Old_Stack := x_err_stack;
84 x_err_stack :=
85 x_err_stack ||'->PA_PROCESS_ACCUM_BUDGETS_RES.Process_all_res_bud';
86
87 pa_debug.debug(x_err_stack);
88
89 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
90 -- Resource is 'Y'
91
92 If x_rollup_qty_flag = 'Y' Then
93 V_Base_Qty_ptd := X_Base_Quantity_ptd;
94 V_Base_Qty_pp := X_Base_Quantity_pp ;
95 V_Base_Qty_ytd := X_Base_Quantity_ytd;
96 V_Base_Qty_itd := X_Base_Quantity_itd;
97 V_Orig_Qty_ptd := X_Orig_Quantity_ptd;
98 V_Orig_Qty_pp := X_Orig_Quantity_pp ;
99 V_Orig_Qty_ytd := X_Orig_Quantity_ytd;
100 V_Orig_Qty_itd := X_Orig_Quantity_itd;
101 Else
102 V_Base_Qty_ptd := 0;
103 V_Base_Qty_pp := 0;
104 V_Base_Qty_ytd := 0;
105 V_Base_Qty_itd := 0;
106 V_Orig_Qty_ptd := 0;
107 V_Orig_Qty_pp := 0;
108 V_Orig_Qty_ytd := 0;
109 V_Orig_Qty_itd := 0;
110 End If;
111
112 -- The following Update statement updates all records in the given task
113 -- WBS hierarchy.It will update only the Project-task-resource combination
114 -- records and the Project-Resource level record(Task id = 0 and
115 -- Resourcelist member id <> 0)
116
117 Update PA_PROJECT_ACCUM_BUDGETS PAB SET
118 BASE_RAW_COST_ITD = NVL(BASE_RAW_COST_ITD,0) +
119 NVL(X_Base_Raw_Cost_itd,0),
120 BASE_RAW_COST_YTD = NVL(BASE_RAW_COST_YTD,0) +
121 NVL(X_Base_Raw_Cost_ytd,0),
122 BASE_RAW_COST_PTD = NVL(BASE_RAW_COST_PTD,0) +
123 NVL(X_Base_Raw_Cost_ptd,0),
124 BASE_RAW_COST_PP = NVL(BASE_RAW_COST_PP,0) +
125 NVL(X_Base_Raw_Cost_pp,0),
126 ORIG_RAW_COST_ITD = NVL(ORIG_RAW_COST_ITD,0) +
127 NVL(X_Orig_Raw_Cost_itd,0),
128 ORIG_RAW_COST_YTD = NVL(ORIG_RAW_COST_YTD,0) +
129 NVL(X_Orig_Raw_Cost_ytd,0),
130 ORIG_RAW_COST_PTD = NVL(ORIG_RAW_COST_PTD,0) +
131 NVL(X_Orig_Raw_Cost_ptd,0),
132 ORIG_RAW_COST_PP = NVL(ORIG_RAW_COST_PP,0) +
133 NVL(X_Orig_Raw_Cost_pp,0),
134 BASE_BURDENED_COST_ITD = NVL(BASE_BURDENED_COST_ITD,0) +
135 NVL(X_Base_Burdened_Cost_itd,0),
136 BASE_BURDENED_COST_YTD = NVL(BASE_BURDENED_COST_YTD,0) +
137 NVL(X_Base_Burdened_Cost_ytd,0),
138 BASE_BURDENED_COST_PTD = NVL(BASE_BURDENED_COST_PTD,0) +
139 NVL(X_Base_Burdened_Cost_ptd,0),
140 BASE_BURDENED_COST_PP = NVL(BASE_BURDENED_COST_PP,0) +
141 NVL(X_Base_Burdened_Cost_pp,0),
142 ORIG_BURDENED_COST_ITD = NVL(ORIG_BURDENED_COST_ITD,0) +
143 NVL(X_Orig_Burdened_Cost_itd,0),
144 ORIG_BURDENED_COST_YTD = NVL(ORIG_BURDENED_COST_YTD,0) +
145 NVL(X_Orig_Burdened_Cost_ytd,0),
146 ORIG_BURDENED_COST_PTD = NVL(ORIG_BURDENED_COST_PTD,0) +
147 NVL(X_Orig_Burdened_Cost_ptd,0),
148 ORIG_BURDENED_COST_PP = NVL(ORIG_BURDENED_COST_PP,0) +
149 NVL(X_Orig_Burdened_Cost_pp,0),
150 BASE_LABOR_HOURS_ITD = NVL(BASE_LABOR_HOURS_ITD,0) +
151 NVL(X_Base_Labor_Hours_itd,0),
152 BASE_LABOR_HOURS_YTD = NVL(BASE_LABOR_HOURS_YTD,0) +
153 NVL(X_Base_Labor_Hours_ytd,0),
154 BASE_LABOR_HOURS_PTD = NVL(BASE_LABOR_HOURS_PTD,0) +
155 NVL(X_Base_Labor_Hours_ptd,0),
156 BASE_LABOR_HOURS_PP = NVL(BASE_LABOR_HOURS_PP,0) +
157 NVL(X_Base_Labor_Hours_pp,0),
158 ORIG_LABOR_HOURS_ITD = NVL(ORIG_LABOR_HOURS_ITD,0) +
159 NVL(X_Orig_Labor_Hours_itd,0),
160 ORIG_LABOR_HOURS_YTD = NVL(ORIG_LABOR_HOURS_YTD,0) +
161 NVL(X_Orig_Labor_Hours_ytd,0),
162 ORIG_LABOR_HOURS_PTD = NVL(ORIG_LABOR_HOURS_PTD,0) +
163 NVL(X_Orig_Labor_Hours_ptd,0),
164 ORIG_LABOR_HOURS_PP = NVL(ORIG_LABOR_HOURS_PP,0) +
165 NVL(X_Orig_Labor_Hours_pp,0),
166 BASE_QUANTITY_ITD = NVL(BASE_QUANTITY_ITD,0) +
167 NVL(V_Base_Qty_itd,0),
168 BASE_QUANTITY_YTD = NVL(BASE_QUANTITY_YTD,0) +
169 NVL(V_Base_Qty_ytd,0),
170 BASE_QUANTITY_PTD = NVL(BASE_QUANTITY_PTD,0) +
171 NVL(V_Base_Qty_ptd,0),
172 BASE_QUANTITY_PP = NVL(BASE_QUANTITY_PP,0) +
173 NVL(V_Base_Qty_pp,0),
174 ORIG_QUANTITY_ITD = NVL(ORIG_QUANTITY_ITD,0) +
175 NVL(V_Orig_Qty_itd,0),
176 ORIG_QUANTITY_YTD = NVL(ORIG_QUANTITY_YTD,0) +
177 NVL(V_Orig_Qty_ytd,0),
178 ORIG_QUANTITY_PTD = NVL(ORIG_QUANTITY_PTD,0) +
179 NVL(V_Orig_Qty_ptd,0),
180 ORIG_QUANTITY_PP = NVL(ORIG_QUANTITY_PP,0) +
181 NVL(V_Orig_Qty_pp,0),
182 BASE_REVENUE_ITD = NVL(BASE_REVENUE_ITD,0) +
183 NVL(X_Base_Revenue_itd,0),
184 BASE_REVENUE_YTD = NVL(BASE_REVENUE_YTD,0) +
185 NVL(X_Base_Revenue_ytd,0),
186 BASE_REVENUE_PTD = NVL(BASE_REVENUE_PTD,0) +
187 NVL(X_Base_Revenue_ptd,0),
188 BASE_REVENUE_PP = NVL(BASE_REVENUE_PP,0) +
189 NVL(X_Base_Revenue_pp,0),
190 ORIG_REVENUE_ITD = NVL(ORIG_REVENUE_ITD,0) +
191 NVL(X_Orig_Revenue_itd,0),
192 ORIG_REVENUE_YTD = NVL(ORIG_REVENUE_YTD,0) +
193 NVL(X_Orig_Revenue_ytd,0),
194 ORIG_REVENUE_PTD = NVL(ORIG_REVENUE_PTD,0) +
195 NVL(X_Orig_Revenue_ptd,0),
196 ORIG_REVENUE_PP = NVL(ORIG_REVENUE_PP,0) +
197 NVL(X_Orig_Revenue_pp,0),
198 BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
199 ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
200 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
201 LAST_UPDATE_DATE = Trunc(Sysdate),
202 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
203 Where Budget_Type_Code = x_Budget_type_code
204 And (PAB.Project_Accum_id In
205 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
206 Where Pah.Project_id = x_project_id and
207 pah.Resource_list_member_id = x_resource_list_member_id and
208 Pah.Task_id in (select 0 from sys.dual union
209 Select Pt.Task_Id from PA_TASKS pt
210 start with pt.task_id = x_task_id
211 connect by prior pt.parent_task_id = pt.task_id))) ;
212
213 x_recs_processed := Recs_processed;
214 -- Restore the old x_err_stack;
215
216 x_err_stack := V_Old_Stack;
217 Exception
218 When Others Then
219 x_err_code := SQLCODE;
220 RAISE;
221 End Process_all_res_bud;
222
223 Procedure Process_it_yt_pt_res_bud
224 (x_project_id In Number,
225 x_task_id In Number,
226 x_resource_list_id in Number,
227 x_resource_list_Member_id in Number,
228 x_resource_id in Number,
229 x_resource_list_assignment_id in Number,
230 x_rollup_qty_flag In Varchar2,
231 x_budget_type_code In Varchar2,
232 x_current_period In Varchar2,
233 X_Base_Revenue In Number,
234 X_Base_Raw_Cost In Number,
235 X_Base_Burdened_Cost In Number,
236 X_Base_Labor_Hours In Number,
237 X_Base_Quantity In Number,
238 X_Base_Unit_Of_Measure In Varchar2,
239 X_Orig_Revenue In Number,
240 X_Orig_Raw_Cost In Number,
241 X_Orig_Burdened_Cost In Number,
242 X_Orig_Labor_Hours In Number,
243 X_Orig_Quantity In Number,
244 X_Orig_Unit_Of_Measure In Varchar2,
245 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
246 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
247 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
248 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
249
250
251 -- Process_it_yt_pt_res_bud - Processes ITD,YTD and PTD amounts in the
252 -- PA_PROJECT_ACCUM_BUDGETS table. For the
253 -- given Project-Task-Resource combination,records are
254 -- created/updated and rolled up to all the
255 -- higher level tasks. The Project-Resource records
256 -- are also created/updated.
257 Recs_processed Number := 0;
258 V_Accum_id Number := 0;
259 V_task_array task_id_tabtype;
260 v_noof_tasks Number := 0;
261 V_Orig_Qty Number := 0;
262 V_Base_Qty Number := 0;
263 Res_Recs_processed Number := 0;
264 V_Old_Stack Varchar2(630);
265
266 Begin
267
268 V_Old_Stack := x_err_stack;
269 x_err_stack :=
270 x_err_stack ||'->PA_PROCESS_ACCUM_BUDGETS_RES.Process_it_yt_pt_res_bud';
271
272 pa_debug.debug(x_err_stack);
273
274 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
275 -- Resource is 'Y'
276
277 If x_rollup_qty_flag = 'Y' Then
278 V_Base_Qty := X_Base_Quantity;
279 V_Orig_Qty := X_Orig_Quantity;
280 Else
281 V_Base_Qty := 0;
282 V_Orig_Qty := 0;
283 End If;
284
285 -- The follwing Update statement updates all records in the given task
286 -- WBS hierarchy.It will update only the Project-task-resource combination
287 -- records and the Project-Resource level record(Task id = 0 and
288 -- Resourcelist member id <> 0)
289
290 Update PA_PROJECT_ACCUM_BUDGETS PAB SET
291 BASE_RAW_COST_ITD = BASE_RAW_COST_ITD + X_Base_Raw_Cost,
292 BASE_RAW_COST_YTD = BASE_RAW_COST_YTD + X_Base_Raw_Cost,
293 BASE_RAW_COST_PTD = BASE_RAW_COST_PTD + X_Base_Raw_Cost,
294 ORIG_RAW_COST_ITD = ORIG_RAW_COST_ITD + X_Orig_Raw_Cost,
295 ORIG_RAW_COST_YTD = ORIG_RAW_COST_YTD + X_Orig_Raw_Cost,
296 ORIG_RAW_COST_PTD = ORIG_RAW_COST_PTD + X_Orig_Raw_Cost,
297 BASE_BURDENED_COST_ITD = BASE_BURDENED_COST_ITD +
298 X_Base_Burdened_Cost,
299 BASE_BURDENED_COST_YTD = BASE_BURDENED_COST_YTD +
300 X_Base_Burdened_Cost,
301 BASE_BURDENED_COST_PTD = BASE_BURDENED_COST_PTD +
302 X_Base_Burdened_Cost,
303 ORIG_BURDENED_COST_ITD = ORIG_BURDENED_COST_ITD +
304 X_Orig_Burdened_Cost,
305 ORIG_BURDENED_COST_YTD = ORIG_BURDENED_COST_YTD +
306 X_Orig_Burdened_Cost,
307 ORIG_BURDENED_COST_PTD = ORIG_BURDENED_COST_PTD +
308 X_Orig_Burdened_Cost,
309 BASE_LABOR_HOURS_ITD = BASE_LABOR_HOURS_ITD + X_Base_Labor_Hours,
310 BASE_LABOR_HOURS_YTD = BASE_LABOR_HOURS_YTD + X_Base_Labor_Hours,
311 BASE_LABOR_HOURS_PTD = BASE_LABOR_HOURS_PTD + X_Base_Labor_Hours,
312 ORIG_LABOR_HOURS_ITD = ORIG_LABOR_HOURS_ITD + X_Orig_Labor_Hours,
313 ORIG_LABOR_HOURS_YTD = ORIG_LABOR_HOURS_YTD + X_Orig_Labor_Hours,
314 ORIG_LABOR_HOURS_PTD = ORIG_LABOR_HOURS_PTD + X_Orig_Labor_Hours,
315 BASE_QUANTITY_ITD = BASE_QUANTITY_ITD + V_Base_Qty,
316 BASE_QUANTITY_YTD = BASE_QUANTITY_YTD + V_Base_Qty,
317 BASE_QUANTITY_PTD = BASE_QUANTITY_PTD + V_Base_Qty,
318 ORIG_QUANTITY_ITD = ORIG_QUANTITY_ITD + V_Orig_Qty,
319 ORIG_QUANTITY_YTD = ORIG_QUANTITY_YTD + V_Orig_Qty,
320 ORIG_QUANTITY_PTD = ORIG_QUANTITY_PTD + V_Orig_Qty,
321 BASE_REVENUE_ITD = BASE_REVENUE_ITD + X_Base_Revenue,
322 BASE_REVENUE_YTD = BASE_REVENUE_YTD + X_Base_Revenue,
323 BASE_REVENUE_PTD = BASE_REVENUE_PTD + X_Base_Revenue,
324 ORIG_REVENUE_ITD = ORIG_REVENUE_ITD + X_Orig_Revenue,
325 ORIG_REVENUE_YTD = ORIG_REVENUE_YTD + X_Orig_Revenue,
326 ORIG_REVENUE_PTD = ORIG_REVENUE_PTD + X_Orig_Revenue,
327 BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
328 ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
329 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
330 LAST_UPDATE_DATE = Trunc(Sysdate),
331 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
332 Where Budget_Type_Code = x_Budget_type_code
333 And (PAB.Project_Accum_id In
334 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
335 Where Pah.Project_id = x_project_id and
336 pah.Resource_list_member_id = x_resource_list_member_id and
337 Pah.Task_id in (select 0 from sys.dual union
338 Select Pt.Task_Id from PA_TASKS pt
339 start with pt.task_id = x_task_id
340 connect by prior pt.parent_task_id = pt.task_id))) ;
341
342 x_recs_processed := Recs_processed;
343 -- Restore the old x_err_stack;
344
345 x_err_stack := V_Old_Stack;
346 Exception
347 When Others Then
348 x_err_code := SQLCODE;
349 RAISE;
350 End Process_it_yt_pt_res_bud;
351
352 Procedure Process_it_yt_pp_res_bud
353 (x_project_id In Number,
354 x_task_id In Number,
355 x_resource_list_id in Number,
356 x_resource_list_Member_id in Number,
357 x_resource_id in Number,
358 x_resource_list_assignment_id in Number,
359 x_rollup_qty_flag In Varchar2,
360 x_budget_type_code In Varchar2,
361 x_current_period In Varchar2,
362 X_Base_Revenue In Number,
363 X_Base_Raw_Cost In Number,
364 X_Base_Burdened_Cost In Number,
365 X_Base_Labor_Hours In Number,
366 X_Base_Quantity In Number,
367 X_Base_Unit_Of_Measure In Varchar2,
368 X_Orig_Revenue In Number,
369 X_Orig_Raw_Cost In Number,
370 X_Orig_Burdened_Cost In Number,
371 X_Orig_Labor_Hours In Number,
372 X_Orig_Quantity In Number,
373 X_Orig_Unit_Of_Measure In Varchar2,
374 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
375 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
376 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
377 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
378
379
380 -- Process_it_yt_pp_res_bud - Processes ITD,YTD and PP amounts in the
381 -- PA_PROJECT_ACCUM_BUDGETS table. For the
382 -- given Project-Task-Resource combination,records are
383 -- created/updated and rolled up to all the
384 -- higher level tasks. The Project-Resource records
385 -- are also created/updated.
386 Recs_processed Number := 0;
387 V_Accum_id Number := 0;
388 V_task_array task_id_tabtype;
389 v_noof_tasks Number := 0;
390 V_Orig_Qty Number := 0;
391 V_Base_Qty Number := 0;
392 Res_Recs_processed Number := 0;
393 V_Old_Stack Varchar2(630);
394
395 Begin
396 V_Old_Stack := x_err_stack;
397 x_err_stack :=
398 x_err_stack ||'->PA_PROCESS_ACCUM_BUDGETS_RES.Process_it_yt_pp_res_bud';
399
400 pa_debug.debug(x_err_stack);
401
402 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
403 -- Resource is 'Y'
404
405 If x_rollup_qty_flag = 'Y' Then
406 V_Base_Qty := X_Base_Quantity;
407 V_Orig_Qty := X_Orig_Quantity;
408 Else
409 V_Base_Qty := 0;
410 V_Orig_Qty := 0;
411 End If;
412
413 -- The follwing Update statement updates all records in the given task
414 -- WBS hierarchy.It will update only the Project-task-resource combination
415 -- records and the Project-Resource level record(Task id = 0 and
416 -- Resourcelist member id <> 0)
417
418 Update PA_PROJECT_ACCUM_BUDGETS PAB SET
419 BASE_RAW_COST_ITD = BASE_RAW_COST_ITD + X_Base_Raw_Cost,
420 BASE_RAW_COST_YTD = BASE_RAW_COST_YTD + X_Base_Raw_Cost,
421 BASE_RAW_COST_PP = BASE_RAW_COST_PP + X_Base_Raw_Cost,
422 ORIG_RAW_COST_ITD = ORIG_RAW_COST_ITD + X_Orig_Raw_Cost,
423 ORIG_RAW_COST_YTD = ORIG_RAW_COST_YTD + X_Orig_Raw_Cost,
424 ORIG_RAW_COST_PP = ORIG_RAW_COST_PP + X_Orig_Raw_Cost,
425 BASE_BURDENED_COST_ITD = BASE_BURDENED_COST_ITD +
426 X_Base_Burdened_Cost,
427 BASE_BURDENED_COST_YTD = BASE_BURDENED_COST_YTD +
428 X_Base_Burdened_Cost,
429 BASE_BURDENED_COST_PP = BASE_BURDENED_COST_PP +
430 X_Base_Burdened_Cost,
431 ORIG_BURDENED_COST_ITD = ORIG_BURDENED_COST_ITD +
432 X_Orig_Burdened_Cost,
433 ORIG_BURDENED_COST_YTD = ORIG_BURDENED_COST_YTD +
434 X_Orig_Burdened_Cost,
435 ORIG_BURDENED_COST_PP = ORIG_BURDENED_COST_PP +
436 X_Orig_Burdened_Cost,
437 BASE_LABOR_HOURS_ITD = BASE_LABOR_HOURS_ITD + X_Base_Labor_Hours,
438 BASE_LABOR_HOURS_YTD = BASE_LABOR_HOURS_YTD + X_Base_Labor_Hours,
439 BASE_LABOR_HOURS_PP = BASE_LABOR_HOURS_PP + X_Base_Labor_Hours,
440 ORIG_LABOR_HOURS_ITD = ORIG_LABOR_HOURS_ITD + X_Orig_Labor_Hours,
441 ORIG_LABOR_HOURS_YTD = ORIG_LABOR_HOURS_YTD + X_Orig_Labor_Hours,
442 ORIG_LABOR_HOURS_PP = ORIG_LABOR_HOURS_PP + X_Orig_Labor_Hours,
443 BASE_QUANTITY_ITD = BASE_QUANTITY_ITD + V_Base_Qty,
444 BASE_QUANTITY_YTD = BASE_QUANTITY_YTD + V_Base_Qty,
445 BASE_QUANTITY_PP = BASE_QUANTITY_PP + V_Base_Qty,
446 ORIG_QUANTITY_ITD = ORIG_QUANTITY_ITD + V_Orig_Qty,
447 ORIG_QUANTITY_YTD = ORIG_QUANTITY_YTD + V_Orig_Qty,
448 ORIG_QUANTITY_PP = ORIG_QUANTITY_PP + V_Orig_Qty,
449 BASE_REVENUE_ITD = BASE_REVENUE_ITD + X_Base_Revenue,
450 BASE_REVENUE_YTD = BASE_REVENUE_YTD + X_Base_Revenue,
451 BASE_REVENUE_PP = BASE_REVENUE_PP + X_Base_Revenue,
452 ORIG_REVENUE_ITD = ORIG_REVENUE_ITD + X_Orig_Revenue,
453 ORIG_REVENUE_YTD = ORIG_REVENUE_YTD + X_Orig_Revenue,
454 ORIG_REVENUE_PP = ORIG_REVENUE_PP + X_Orig_Revenue,
455 BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
456 ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
457 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
458 LAST_UPDATE_DATE = Trunc(Sysdate),
459 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
460 Where Budget_Type_Code = x_Budget_type_code
461 And (PAB.Project_Accum_id In
462 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
463 Where Pah.Project_id = x_project_id and
464 pah.Resource_list_member_id = x_resource_list_member_id and
465 Pah.Task_id in (select 0 from sys.dual union
466 Select Pt.Task_Id from PA_TASKS pt
467 start with pt.task_id = x_task_id
468 connect by prior pt.parent_task_id = pt.task_id))) ;
469 Recs_processed := Recs_processed + SQL%ROWCOUNT;
470
471 x_recs_processed := Recs_processed;
472 -- Restore the old x_err_stack;
473
474 x_err_stack := V_Old_Stack;
475 Exception
476 When Others Then
477 x_err_code := SQLCODE;
478 RAISE;
479 End Process_it_yt_pp_res_bud;
480
481 Procedure Process_it_pp_res_bud
482 (x_project_id In Number,
483 x_task_id In Number,
484 x_resource_list_id in Number,
485 x_resource_list_Member_id in Number,
486 x_resource_id in Number,
487 x_resource_list_assignment_id in Number,
488 x_rollup_qty_flag In Varchar2,
489 x_budget_type_code In Varchar2,
490 x_current_period In Varchar2,
491 X_Base_Revenue In Number,
492 X_Base_Raw_Cost In Number,
493 X_Base_Burdened_Cost In Number,
494 X_Base_Labor_Hours In Number,
495 X_Base_Quantity In Number,
496 X_Base_Unit_Of_Measure In Varchar2,
497 X_Orig_Revenue In Number,
498 X_Orig_Raw_Cost In Number,
499 X_Orig_Burdened_Cost In Number,
500 X_Orig_Labor_Hours In Number,
501 X_Orig_Quantity In Number,
502 X_Orig_Unit_Of_Measure In Varchar2,
503 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
504 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
505 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
506 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
507
508
509 -- Process_it_pp_res_bud - Processes ITD and PP amounts in the
510 -- PA_PROJECT_ACCUM_BUDGETS table. For the
511 -- given Project-Task-Resource combination,records are
512 -- created/updated and rolled up to all the
513 -- higher level tasks. The Project-Resource records
514 -- are also created/updated.
515
516 Recs_processed Number := 0;
517 V_Accum_id Number := 0;
518 V_task_array task_id_tabtype;
519 v_noof_tasks Number := 0;
520 V_Orig_Qty Number := 0;
521 V_Base_Qty Number := 0;
522 Res_Recs_processed Number := 0;
523 V_Old_Stack Varchar2(630);
524
525 Begin
526 V_Old_Stack := x_err_stack;
527 x_err_stack :=
528 x_err_stack ||'->PA_PROCESS_ACCUM_BUDGETS_RES.Process_it_pp_res_bud';
529
530 pa_debug.debug(x_err_stack);
531
532 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
533 -- Resource is 'Y'
534
535 If x_rollup_qty_flag = 'Y' Then
536 V_Base_Qty := X_Base_Quantity;
537 V_Orig_Qty := X_Orig_Quantity;
538 Else
539 V_Base_Qty := 0;
540 V_Orig_Qty := 0;
541 End If;
542
543 -- The follwing Update statement updates all records in the given task
544 -- WBS hierarchy.It will update only the Project-task-resource combination
545 -- records and the Project-Resource level record(Task id = 0 and
546 -- Resourcelist member id <> 0)
547
548 Update PA_PROJECT_ACCUM_BUDGETS PAB SET
549 BASE_RAW_COST_ITD = BASE_RAW_COST_ITD + X_Base_Raw_Cost,
550 BASE_RAW_COST_PP = BASE_RAW_COST_PP + X_Base_Raw_Cost,
551 ORIG_RAW_COST_ITD = ORIG_RAW_COST_ITD + X_Orig_Raw_Cost,
552 ORIG_RAW_COST_PP = ORIG_RAW_COST_PP + X_Orig_Raw_Cost,
553 BASE_BURDENED_COST_ITD = BASE_BURDENED_COST_ITD +
554 X_Base_Burdened_Cost,
555 BASE_BURDENED_COST_PP = BASE_BURDENED_COST_PP +
556 X_Base_Burdened_Cost,
557 ORIG_BURDENED_COST_ITD = ORIG_BURDENED_COST_ITD +
558 X_Orig_Burdened_Cost,
559 ORIG_BURDENED_COST_PP = ORIG_BURDENED_COST_PP +
560 X_Orig_Burdened_Cost,
561 BASE_LABOR_HOURS_ITD = BASE_LABOR_HOURS_ITD + X_Base_Labor_Hours,
562 BASE_LABOR_HOURS_PP = BASE_LABOR_HOURS_PP + X_Base_Labor_Hours,
563 ORIG_LABOR_HOURS_ITD = ORIG_LABOR_HOURS_ITD + X_Orig_Labor_Hours,
564 ORIG_LABOR_HOURS_PP = ORIG_LABOR_HOURS_PP + X_Orig_Labor_Hours,
565 BASE_QUANTITY_ITD = BASE_QUANTITY_ITD + V_Base_Qty,
566 BASE_QUANTITY_PP = BASE_QUANTITY_PP + V_Base_Qty,
567 ORIG_QUANTITY_ITD = ORIG_QUANTITY_ITD + V_Orig_Qty,
568 ORIG_QUANTITY_PP = ORIG_QUANTITY_PP + V_Orig_Qty,
569 BASE_REVENUE_ITD = BASE_REVENUE_ITD + X_Base_Revenue,
570 BASE_REVENUE_PP = BASE_REVENUE_PP + X_Base_Revenue,
571 ORIG_REVENUE_ITD = ORIG_REVENUE_ITD + X_Orig_Revenue,
572 ORIG_REVENUE_PP = ORIG_REVENUE_PP + X_Orig_Revenue,
573 BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
574 ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
575 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
576 LAST_UPDATE_DATE = Trunc(Sysdate),
577 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
578 Where Budget_Type_Code = x_Budget_type_code
579 And (PAB.Project_Accum_id In
580 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
581 Where Pah.Project_id = x_project_id and
582 pah.Resource_list_member_id = x_resource_list_member_id and
583 Pah.Task_id in (select 0 from sys.dual union
584 Select Pt.Task_Id from PA_TASKS pt
585 start with pt.task_id = x_task_id
586 connect by prior pt.parent_task_id = pt.task_id))) ;
587 Recs_processed := Recs_processed + SQL%ROWCOUNT;
588
589 x_recs_processed := Recs_processed;
590 -- Restore the old x_err_stack;
591
592 x_err_stack := V_Old_Stack;
593 Exception
594 When Others Then
595 x_err_code := SQLCODE;
596 RAISE;
597 End Process_it_pp_res_bud;
598
599 Procedure Process_it_yt_res_bud
600 (x_project_id In Number,
601 x_task_id In Number,
602 x_resource_list_id in Number,
603 x_resource_list_Member_id in Number,
604 x_resource_id in Number,
605 x_resource_list_assignment_id in Number,
606 x_rollup_qty_flag In Varchar2,
607 x_budget_type_code In Varchar2,
608 x_current_period In Varchar2,
609 X_Base_Revenue In Number,
610 X_Base_Raw_Cost In Number,
611 X_Base_Burdened_Cost In Number,
612 X_Base_Labor_Hours In Number,
613 X_Base_Quantity In Number,
614 X_Base_Unit_Of_Measure In Varchar2,
615 X_Orig_Revenue In Number,
616 X_Orig_Raw_Cost In Number,
617 X_Orig_Burdened_Cost In Number,
618 X_Orig_Labor_Hours In Number,
619 X_Orig_Quantity In Number,
620 X_Orig_Unit_Of_Measure In Varchar2,
621 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
622 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
623 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
624 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
625
626
627 -- Process_it_yt_res_bud - Processes ITD and YTD amounts in the
628 -- PA_PROJECT_ACCUM_BUDGETS table. For the
629 -- given Project-Task-Resource combination,records are
630 -- created/updated and rolled up to all the
631 -- higher level tasks. The Project-Resource records
632 -- are also created/updated.
633 Recs_processed Number := 0;
634 V_Accum_id Number := 0;
635 V_task_array task_id_tabtype;
636 v_noof_tasks Number := 0;
637 V_Orig_Qty Number := 0;
638 V_Base_Qty Number := 0;
639 Res_Recs_processed Number := 0;
640 V_Old_Stack Varchar2(630);
641
642 Begin
643 V_Old_Stack := x_err_stack;
644 x_err_stack :=
645 x_err_stack ||'->PA_PROCESS_ACCUM_BUDGETS_RES.Process_it_yt_res_bud';
646
647 pa_debug.debug(x_err_stack);
648
649 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
650 -- Resource is 'Y'
651
652 If x_rollup_qty_flag = 'Y' Then
653 V_Base_Qty := X_Base_Quantity;
654 V_Orig_Qty := X_Orig_Quantity;
655 Else
656 V_Base_Qty := 0;
657 V_Orig_Qty := 0;
658 End If;
659
660 -- The follwing Update statement updates all records in the given task
661 -- WBS hierarchy.It will update only the Project-task-resource combination
662 -- records and the Project-Resource level record(Task id = 0 and
663 -- Resourcelist member id <> 0)
664
665 Update PA_PROJECT_ACCUM_BUDGETS PAB SET
666 BASE_RAW_COST_ITD = BASE_RAW_COST_ITD + X_Base_Raw_Cost,
667 BASE_RAW_COST_YTD = BASE_RAW_COST_YTD + X_Base_Raw_Cost,
668 ORIG_RAW_COST_ITD = ORIG_RAW_COST_ITD + X_Orig_Raw_Cost,
669 ORIG_RAW_COST_YTD = ORIG_RAW_COST_YTD + X_Orig_Raw_Cost,
670 BASE_BURDENED_COST_ITD = BASE_BURDENED_COST_ITD +
671 X_Base_Burdened_Cost,
672 BASE_BURDENED_COST_YTD = BASE_BURDENED_COST_YTD +
673 X_Base_Burdened_Cost,
674 ORIG_BURDENED_COST_ITD = ORIG_BURDENED_COST_ITD +
675 X_Orig_Burdened_Cost,
676 ORIG_BURDENED_COST_YTD = ORIG_BURDENED_COST_YTD +
677 X_Orig_Burdened_Cost,
678 BASE_LABOR_HOURS_ITD = BASE_LABOR_HOURS_ITD + X_Base_Labor_Hours,
679 BASE_LABOR_HOURS_YTD = BASE_LABOR_HOURS_YTD + X_Base_Labor_Hours,
680 ORIG_LABOR_HOURS_ITD = ORIG_LABOR_HOURS_ITD + X_Orig_Labor_Hours,
681 ORIG_LABOR_HOURS_YTD = ORIG_LABOR_HOURS_YTD + X_Orig_Labor_Hours,
682 BASE_QUANTITY_ITD = BASE_QUANTITY_ITD + V_Base_Qty,
683 BASE_QUANTITY_YTD = BASE_QUANTITY_YTD + V_Base_Qty,
684 ORIG_QUANTITY_ITD = ORIG_QUANTITY_ITD + V_Orig_Qty,
685 ORIG_QUANTITY_YTD = ORIG_QUANTITY_YTD + V_Orig_Qty,
686 BASE_REVENUE_ITD = BASE_REVENUE_ITD + X_Base_Revenue,
687 BASE_REVENUE_YTD = BASE_REVENUE_YTD + X_Base_Revenue,
688 ORIG_REVENUE_ITD = ORIG_REVENUE_ITD + X_Orig_Revenue,
689 ORIG_REVENUE_YTD = ORIG_REVENUE_YTD + X_Orig_Revenue,
690 BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
691 ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
692 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
693 LAST_UPDATE_DATE = Trunc(Sysdate),
694 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
695 Where Budget_Type_Code = x_Budget_type_code
696 And (PAB.Project_Accum_id In
697 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
698 Where Pah.Project_id = x_project_id and
699 pah.Resource_list_member_id = x_resource_list_member_id and
700 Pah.Task_id in (select 0 from sys.dual union
701 Select Pt.Task_Id from PA_TASKS pt
702 start with pt.task_id = x_task_id
703 connect by prior pt.parent_task_id = pt.task_id))) ;
704 Recs_processed := Recs_processed + SQL%ROWCOUNT;
705
706 x_recs_processed := Recs_processed;
707 -- Restore the old x_err_stack;
708
709 x_err_stack := V_Old_Stack;
710 Exception
711 When Others Then
712 x_err_code := SQLCODE;
713 RAISE;
714 End Process_it_yt_res_bud;
715
716 Procedure Process_it_res_bud
717 (x_project_id In Number,
718 x_task_id In Number,
719 x_resource_list_id in Number,
720 x_resource_list_Member_id in Number,
721 x_resource_id in Number,
722 x_resource_list_assignment_id in Number,
723 x_rollup_qty_flag In Varchar2,
724 x_budget_type_code In Varchar2,
725 x_current_period In Varchar2,
726 X_Base_Revenue In Number,
727 X_Base_Raw_Cost In Number,
728 X_Base_Burdened_Cost In Number,
729 X_Base_Labor_Hours In Number,
730 X_Base_Quantity In Number,
731 X_Base_Unit_Of_Measure In Varchar2,
732 X_Orig_Revenue In Number,
733 X_Orig_Raw_Cost In Number,
734 X_Orig_Burdened_Cost In Number,
735 X_Orig_Labor_Hours In Number,
736 X_Orig_Quantity In Number,
737 X_Orig_Unit_Of_Measure In Varchar2,
738 X_Recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
739 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
740 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
741 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
742
743
744 -- Process_it_res_bud - Processes ITD amounts in the
745 -- PA_PROJECT_ACCUM_BUDGETS table. For the
746 -- given Project-Task-Resource combination,records are
747 -- created/updated and rolled up to all the
748 -- higher level tasks. The Project-Resource records
749 -- are also created/updated.
750 Recs_processed Number := 0;
751 V_Accum_id Number := 0;
752 V_task_array task_id_tabtype;
753 v_noof_tasks Number := 0;
754 V_Orig_Qty Number := 0;
755 V_Base_Qty Number := 0;
756 Res_Recs_processed Number := 0;
757 V_Old_Stack Varchar2(630);
758
759 Begin
760 V_Old_Stack := x_err_stack;
761 x_err_stack :=
762 x_err_stack ||'->PA_PROCESS_ACCUM_BUDGETS_RES.Process_it_res_bud';
763
764 pa_debug.debug(x_err_stack);
765
766 -- Quantity would be rolledup only if the Rollup_Quantity_flag against the
767 -- Resource is 'Y'
768
769 If x_rollup_qty_flag = 'Y' Then
770 V_Base_Qty := X_Base_Quantity;
771 V_Orig_Qty := X_Orig_Quantity;
772 Else
773 V_Base_Qty := 0;
774 V_Orig_Qty := 0;
775 End If;
776
777 -- The follwing Update statement updates all records in the given task
778 -- WBS hierarchy.It will update only the Project-task-resource combination
779 -- records and the Project-Resource level record(Task id = 0 and
780 -- Resourcelist member id <> 0)
781
782 Update PA_PROJECT_ACCUM_BUDGETS PAB SET
783 BASE_RAW_COST_ITD = BASE_RAW_COST_ITD + X_Base_Raw_Cost,
784 ORIG_RAW_COST_ITD = ORIG_RAW_COST_ITD + X_Orig_Raw_Cost,
785 BASE_BURDENED_COST_ITD = BASE_BURDENED_COST_ITD +
786 X_Base_Burdened_Cost,
787 ORIG_BURDENED_COST_ITD = ORIG_BURDENED_COST_ITD +
788 X_Orig_Burdened_Cost,
789 BASE_LABOR_HOURS_ITD = BASE_LABOR_HOURS_ITD + X_Base_Labor_Hours,
790 ORIG_LABOR_HOURS_ITD = ORIG_LABOR_HOURS_ITD + X_Orig_Labor_Hours,
791 BASE_QUANTITY_ITD = BASE_QUANTITY_ITD + V_Base_Qty,
792 ORIG_QUANTITY_ITD = ORIG_QUANTITY_ITD + V_Orig_Qty,
793 BASE_REVENUE_ITD = BASE_REVENUE_ITD + X_Base_Revenue,
794 ORIG_REVENUE_ITD = ORIG_REVENUE_ITD + X_Orig_Revenue,
795 BASE_UNIT_OF_MEASURE = X_Base_Unit_of_Measure,
796 ORIG_UNIT_OF_MEASURE = X_Orig_Unit_of_Measure,
797 LAST_UPDATED_BY = pa_proj_accum_main.x_last_updated_by,
798 LAST_UPDATE_DATE = Trunc(Sysdate),
799 LAST_UPDATE_LOGIN = pa_proj_accum_main.x_last_update_login
800 Where Budget_Type_Code = x_Budget_type_code
801 And (PAB.Project_Accum_id In
802 (Select Pah.Project_Accum_id from PA_PROJECT_ACCUM_HEADERS PAH
803 Where Pah.Project_id = x_project_id and
804 pah.Resource_list_member_id = x_resource_list_member_id and
805 Pah.Task_id in (select 0 from sys.dual union
806 Select Pt.Task_Id from PA_TASKS pt
807 start with pt.task_id = x_task_id
808 connect by prior pt.parent_task_id = pt.task_id))) ;
809 Recs_processed := Recs_processed + SQL%ROWCOUNT;
810
811 x_recs_processed := Recs_processed;
812 -- Restore the old x_err_stack;
813
814 x_err_stack := V_Old_Stack;
815
816 Exception
817 When Others Then
818 x_err_code := SQLCODE;
819 RAISE;
820 End Process_it_res_bud;
821 END;