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