[Home] [Help]
PACKAGE BODY: APPS.PA_REFRESH_RES_LISTS
Source
1 PACKAGE body PA_REFRESH_RES_LISTS AS
2 /* $Header: PAACREFB.pls 120.1 2005/08/19 16:14:06 mwasowic noship $ */
3 TYPE resource_list_id_tabtype IS
4 TABLE OF PA_RESOURCE_LIST_ASSIGNMENTS.RESOURCE_LIST_ID%TYPE
5 INDEX BY BINARY_INTEGER;
6
7 -- Process_All_Res_Lists - This procedure accumulates the Actuals and
8 -- Commitments for a given Resource lists for a Project
9
10 Procedure Process_Res_Lists (x_project_id in Number,
11 x_resource_list_id In Number,
12 x_current_period in Varchar2,
13 x_prev_period in Varchar2,
14 x_current_year in Number,
15 x_current_start_date In Date,
16 x_current_end_date In Date,
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 -- This Cursor fetches all Resource lists assigned to the project
23
24 CURSOR Reslist_assgmt_Cur IS
25 Select Distinct Resource_list_id
26 FROM
27 PA_RESOURCE_LIST_ASSIGNMENTS
28 Where Project_id = X_project_id
29 and resource_list_id = NVL(x_resource_list_id,resource_list_id);
30
31 v_Res_accum_txn_accum_id Number := 0;
32 v_Resource_list_id Number := 0;
33
34 -- This Cursor gets all Resources from the PA_RESOURCE_ACCUM_DETAILS
35 -- pertaining to the Resource list
36
37 CURSOR Res_accum_Cur IS
38 Select
39 Para.RESOURCE_LIST_ASSIGNMENT_ID,
40 Para.RESOURCE_LIST_ID,
41 Para.RESOURCE_LIST_MEMBER_ID,
42 Para.RESOURCE_ID,
43 Parl.TRACK_AS_LABOR_FLAG,
44 Par.ROLLUP_QUANTITY_FLAG,
45 Par.UNIT_OF_MEASURE
46 from
47 PA_RESOURCE_ACCUM_DETAILS Para,
48 PA_RESOURCES Par,
49 PA_RESOURCE_LIST_MEMBERS Parl
50 Where Para.Txn_Accum_id = v_Res_accum_txn_accum_id and
51 Para.Resource_list_id = v_Resource_list_id and
52 Para.Resource_list_id = Parl.Resource_list_id and
53 Para.Resource_list_member_id = Parl.Resource_list_member_id and
54 nvl(parl.migration_code,'-99') <> 'N' and
55 Para.Resource_id = Par.Resource_Id ;
56
57 -- This cursor reads all transactions from the PA_TXN_ACCUM
58
59 CURSOR All_PA_Txn_Accum_Cur is
60 SELECT DISTINCT
61 PTA.TXN_ACCUM_ID,
62 PTA.TASK_ID,
63 PTA.PA_PERIOD,
64 PTA.GL_PERIOD,
65 NVL(PTA.TOT_REVENUE,0) TOT_REVENUE ,
66 NVL(PTA.TOT_RAW_COST,0) TOT_RAW_COST ,
67 NVL(PTA.TOT_BURDENED_COST,0) TOT_BURDENED_COST,
68 NVL(PTA.TOT_QUANTITY,0) TOT_QUANTITY ,
69 NVL(PTA.TOT_LABOR_HOURS,0) TOT_LABOR_HOURS,
70 NVL(PTA.TOT_BILLABLE_RAW_COST,0) TOT_BILLABLE_RAW_COST,
71 NVL(PTA.TOT_BILLABLE_BURDENED_COST,0) TOT_BILLABLE_BURDENED_COST,
72 NVL(PTA.TOT_BILLABLE_QUANTITY,0) TOT_BILLABLE_QUANTITY,
73 NVL(PTA.TOT_BILLABLE_LABOR_HOURS,0) TOT_BILLABLE_LABOR_HOURS,
74 NVL(PTA.TOT_CMT_RAW_COST,0) TOT_CMT_RAW_COST,
75 NVL(PTA.TOT_CMT_BURDENED_COST,0) TOT_CMT_BURDENED_COST,
76 NVL(PTA.TOT_CMT_QUANTITY,0) TOT_CMT_QUANTITY,
77 PAP.PERIOD_YEAR
78 FROM
79 PA_TXN_ACCUM PTA,
80 PA_PERIODS_V PAP
81 WHERE PTA.Project_Id = x_project_id
82 AND PTA.PA_PERIOD = PAP.PERIOD_NAME
83 AND PAP.PA_END_DATE <= x_current_end_date;
84
85 x_resource_list_array resource_list_id_tabtype;
86 x_res_list_rec Reslist_assgmt_Cur%ROWTYPE;
87 x_all_txn_accum_rec All_PA_Txn_Accum_Cur%ROWTYPE;
88 x_res_accum_rec Res_accum_Cur%ROWTYPE;
89 v_err_code Number := 0;
90 x_recs_processed number := 0;
91 tot_recs_processed Number := 0;
92 No_of_res_lists Number := 0;
93 x_quantity NUMBER :=0;
94 x_billable_quantity NUMBER :=0;
95 V_Old_Stack Varchar2(630);
96
97 Begin
98
99 V_Old_Stack := x_err_stack;
100 x_err_stack :=
101 x_err_stack ||'->PA_REFRESH_RES_LISTS.Process_All_Res_Lists';
102 pa_debug.debug(x_err_stack);
103
104 -- Fetch all resource lists assigned to the project
105
106 FOR x_res_list_rec IN Reslist_assgmt_Cur LOOP
107 No_of_res_lists := No_of_res_lists + 1;
108 x_resource_list_array(No_of_res_lists) :=
109 x_res_list_rec.Resource_list_id;
110 END LOOP;
111
112 IF No_of_res_lists > 0 Then -- (IF #1)
113
114 -- Read All txn_accum records and process Actuals as well as Commitments
115
116 FOR x_all_txn_accum_rec IN All_PA_Txn_Accum_Cur LOOP
117 v_Res_accum_txn_accum_id := x_all_txn_accum_rec.Txn_Accum_Id;
118 FOR i in 1..No_of_res_lists LOOP
119 v_resource_list_id := x_resource_list_array(i);
120 -- Fetch the Resource Accum records
121
122 FOR Res_Accum_rec in Res_accum_Cur LOOP
123
124 pa_maint_project_accums.create_accum_actuals_res
125 (x_project_id,
126 x_all_txn_accum_rec.task_id,
127 Res_Accum_Rec.resource_list_id ,
128 Res_Accum_Rec.resource_list_Member_id ,
129 Res_Accum_Rec.resource_id ,
130 Res_Accum_Rec.resource_list_assignment_id ,
131 x_current_period,
132 x_Recs_processed,
133 x_err_stack,
134 x_err_stage,
135 x_err_code);
136 IF ( Res_Accum_Rec.rollup_Quantity_flag = 'Y') THEN
137 x_quantity := x_all_txn_accum_rec.TOT_QUANTITY;
138 x_billable_quantity := x_all_txn_accum_rec.TOT_BILLABLE_QUANTITY;
139 ELSE
140 x_quantity := 0;
141 x_billable_quantity :=0;
142 END IF;
143
144 -- Fetched period = current period
145 -- (Update only ITD,YTD and PTD figures)-Task level figures with resources
146 -- and Project-Resources - ACTUALS
147
148 IF (x_all_txn_accum_rec.PA_PERIOD = x_current_period ) or --(IF #2)
149 (x_all_txn_accum_rec.GL_PERIOD = x_current_period ) Then
150
151 PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_yt_pt_res
152 (x_project_id,
153 x_all_txn_accum_rec.task_id,
154 Res_Accum_Rec.resource_list_id ,
155 Res_Accum_Rec.resource_list_Member_id ,
156 Res_Accum_Rec.resource_id ,
157 Res_Accum_Rec.resource_list_assignment_id ,
158 Res_Accum_Rec.track_as_labor_flag ,
159 Res_Accum_Rec.rollup_Quantity_flag ,
160 Res_Accum_Rec.unit_of_measure ,
161 x_current_period ,
162 x_all_txn_accum_rec.TOT_REVENUE,
163 x_all_txn_accum_rec.TOT_RAW_COST,
164 x_all_txn_accum_rec.TOT_BURDENED_COST,
165 x_all_txn_accum_rec.TOT_LABOR_HOURS,
166 x_quantity,
167 x_billable_quantity,
168 x_all_txn_accum_rec.TOT_BILLABLE_RAW_COST,
169 x_all_txn_accum_rec.TOT_BILLABLE_BURDENED_COST,
170 x_all_txn_accum_rec.TOT_BILLABLE_LABOR_HOURS,
171 'Y', -- x_actual_cost_flag
172 'Y', -- x_revenue_flag
173 X_Recs_processed ,
174 x_err_stack,
175 x_err_stage,
176 x_err_code);
177 tot_recs_processed := tot_recs_processed + x_recs_processed;
178
179 -- Fetched period = current period
180 -- (Update only ITD,YTD and PTD figures)-Task level figures with resources
181 -- and Project-Resources - COMMITMENTS
182
183 PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_pt_cmt_res
184 (x_project_id,
185 x_all_txn_accum_rec.task_id,
186 Res_Accum_Rec.resource_list_id ,
187 Res_Accum_Rec.resource_list_Member_id ,
188 Res_Accum_Rec.resource_id ,
189 Res_Accum_Rec.resource_list_assignment_id ,
190 Res_Accum_Rec.track_as_labor_flag ,
191 Res_Accum_Rec.rollup_Quantity_flag ,
192 Res_Accum_Rec.unit_of_measure ,
193 x_current_period ,
194 x_all_txn_accum_rec.TOT_CMT_RAW_COST,
195 x_all_txn_accum_rec.TOT_CMT_BURDENED_COST,
196 x_all_txn_accum_rec.TOT_CMT_QUANTITY,
197 X_Recs_processed ,
198 x_err_stack,
199 x_err_stage,
200 x_err_code);
201
202 tot_recs_processed := tot_recs_processed + x_recs_processed;
203 ELSIF -- (for IF #2)
204 -- Fetched period = Previous period
205 (x_all_txn_accum_rec.PA_PERIOD = x_prev_period )
206 or (x_all_txn_accum_rec.GL_PERIOD = x_prev_period ) Then
207 IF x_all_txn_accum_rec.PERIOD_YEAR = x_current_year Then --(IF #3)
208
209 -- Fetched period = previous period and fetched year = current year
210 -- (Update only ITD,YTD and PP figures )- Task level figures with resources
211 -- and Project-Resources - ACTUALS
212
213 PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_yt_pp_res
214 (x_project_id,
215 x_all_txn_accum_rec.task_id,
216 Res_Accum_Rec.resource_list_id ,
217 Res_Accum_Rec.resource_list_Member_id ,
218 Res_Accum_Rec.resource_id ,
219 Res_Accum_Rec.resource_list_assignment_id ,
220 Res_Accum_Rec.track_as_labor_flag ,
221 Res_Accum_Rec.rollup_Quantity_flag ,
222 Res_Accum_Rec.unit_of_measure ,
223 x_current_period ,
224 x_all_txn_accum_rec.TOT_REVENUE,
225 x_all_txn_accum_rec.TOT_RAW_COST,
226 x_all_txn_accum_rec.TOT_BURDENED_COST,
227 x_all_txn_accum_rec.TOT_LABOR_HOURS,
228 x_quantity,
229 x_billable_quantity,
230 x_all_txn_accum_rec.TOT_BILLABLE_RAW_COST,
231 x_all_txn_accum_rec.TOT_BILLABLE_BURDENED_COST,
232 x_all_txn_accum_rec.TOT_BILLABLE_LABOR_HOURS,
233 'Y', -- x_actual_cost_flag
234 'Y', -- x_revenue_flag
235 X_Recs_processed ,
236 x_err_stack,
237 x_err_stage,
238 x_err_code);
239
240 tot_recs_processed := tot_recs_processed + x_recs_processed;
241
242 -- Fetched period = previous period and fetched year = current year
243 -- (Update only ITD,YTD and PP figures )- Task level figures with resources
244 -- and Project-Resources - COMMITMENTS
245
246 PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_pp_cmt_res
247 (x_project_id,
248 x_all_txn_accum_rec.task_id,
249 Res_Accum_Rec.resource_list_id ,
250 Res_Accum_Rec.resource_list_Member_id ,
251 Res_Accum_Rec.resource_id ,
252 Res_Accum_Rec.resource_list_assignment_id ,
253 Res_Accum_Rec.track_as_labor_flag ,
254 Res_Accum_Rec.rollup_Quantity_flag ,
255 Res_Accum_Rec.unit_of_measure ,
256 x_current_period ,
257 x_all_txn_accum_rec.TOT_CMT_RAW_COST,
258 x_all_txn_accum_rec.TOT_CMT_BURDENED_COST,
259 x_all_txn_accum_rec.TOT_CMT_QUANTITY,
260 X_Recs_processed ,
261 x_err_stack,
262 x_err_stage,
263 x_err_code);
264
265 tot_recs_processed := tot_recs_processed + x_recs_processed;
266 ELSE -- (for IF #3)
267 -- Fetched period = previous period but fetched year != current year
268 -- (Update only ITD and PP figures )-Task level figures with resources
269 -- and Project-Resources - ACTUALS
270
271 PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_pp_res
272 (x_project_id,
273 x_all_txn_accum_rec.task_id,
274 Res_Accum_Rec.resource_list_id ,
275 Res_Accum_Rec.resource_list_Member_id ,
276 Res_Accum_Rec.resource_id ,
277 Res_Accum_Rec.resource_list_assignment_id ,
278 Res_Accum_Rec.track_as_labor_flag ,
279 Res_Accum_Rec.rollup_Quantity_flag ,
280 Res_Accum_Rec.unit_of_measure ,
281 x_current_period ,
282 x_all_txn_accum_rec.TOT_REVENUE,
283 x_all_txn_accum_rec.TOT_RAW_COST,
284 x_all_txn_accum_rec.TOT_BURDENED_COST,
285 x_all_txn_accum_rec.TOT_LABOR_HOURS,
286 x_quantity,
287 x_billable_quantity,
288 x_all_txn_accum_rec.TOT_BILLABLE_RAW_COST,
289 x_all_txn_accum_rec.TOT_BILLABLE_BURDENED_COST,
290 x_all_txn_accum_rec.TOT_BILLABLE_LABOR_HOURS,
291 'Y', -- x_actual_cost_flag
292 'Y', -- x_revenue_flag
293 X_Recs_processed ,
294 x_err_stack,
295 x_err_stage,
296 x_err_code);
297
298 tot_recs_processed := tot_recs_processed + x_recs_processed;
299
300 -- Fetched period = previous period but fetched year != current year
301 -- (Update only ITD and PP figures )-Task level figures with resources
302 -- and Project-Resources - COMMITMENTS
303
304 PA_PROCESS_ACCUM_CMT_RES.Process_it_pp_cmt_res
305 (x_project_id,
306 x_all_txn_accum_rec.task_id,
307 Res_Accum_Rec.resource_list_id ,
308 Res_Accum_Rec.resource_list_Member_id ,
309 Res_Accum_Rec.resource_id ,
310 Res_Accum_Rec.resource_list_assignment_id ,
311 Res_Accum_Rec.track_as_labor_flag ,
312 Res_Accum_Rec.rollup_Quantity_flag ,
313 Res_Accum_Rec.unit_of_measure ,
314 x_current_period ,
315 x_all_txn_accum_rec.TOT_CMT_RAW_COST,
316 x_all_txn_accum_rec.TOT_CMT_BURDENED_COST,
317 x_all_txn_accum_rec.TOT_CMT_QUANTITY,
318 X_Recs_processed ,
319 x_err_stack,
320 x_err_stage,
321 x_err_code);
322
323 tot_recs_processed := tot_recs_processed + x_recs_processed;
324 END IF; -- (IF #3)
325 ELSE -- (for IF #2)
326 IF x_all_txn_accum_rec.PERIOD_YEAR = x_current_year Then --(IF #4)
327
328 -- Fetched period != current or previous period but fetched year =
329 -- current year
330 -- (Update only ITD and YTD figures)- Task level figures with resources
331 -- and Project-Resources - ACTUALS
332
333 PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_yt_res
334 (x_project_id,
335 x_all_txn_accum_rec.task_id,
336 Res_Accum_Rec.resource_list_id ,
337 Res_Accum_Rec.resource_list_Member_id ,
338 Res_Accum_Rec.resource_id ,
339 Res_Accum_Rec.resource_list_assignment_id ,
340 Res_Accum_Rec.track_as_labor_flag ,
341 Res_Accum_Rec.rollup_Quantity_flag ,
342 Res_Accum_Rec.unit_of_measure ,
343 x_current_period ,
344 x_all_txn_accum_rec.TOT_REVENUE,
345 x_all_txn_accum_rec.TOT_RAW_COST,
346 x_all_txn_accum_rec.TOT_BURDENED_COST,
347 x_all_txn_accum_rec.TOT_LABOR_HOURS,
348 x_quantity,
349 x_billable_quantity,
350 x_all_txn_accum_rec.TOT_BILLABLE_RAW_COST,
351 x_all_txn_accum_rec.TOT_BILLABLE_BURDENED_COST,
352 x_all_txn_accum_rec.TOT_BILLABLE_LABOR_HOURS,
353 'Y', -- x_actual_cost_flag
354 'Y', -- x_revenue_flag
355 X_Recs_processed ,
356 x_err_stack,
357 x_err_stage,
358 x_err_code);
359
360 tot_recs_processed := tot_recs_processed + x_recs_processed;
361
362 -- Fetched period != current or previous period but fetched year =
363 -- current year
364 -- (Update only ITD and YTD figures)- Task level figures with resources
365 -- and Project-Resources - COMMITMENTS
366
367 PA_PROCESS_ACCUM_CMT_RES.Process_it_yt_cmt_res
368 (x_project_id,
369 x_all_txn_accum_rec.task_id,
370 Res_Accum_Rec.resource_list_id ,
371 Res_Accum_Rec.resource_list_Member_id ,
372 Res_Accum_Rec.resource_id ,
373 Res_Accum_Rec.resource_list_assignment_id ,
374 Res_Accum_Rec.track_as_labor_flag ,
375 Res_Accum_Rec.rollup_Quantity_flag ,
376 Res_Accum_Rec.unit_of_measure ,
377 x_current_period ,
378 x_all_txn_accum_rec.TOT_CMT_RAW_COST,
379 x_all_txn_accum_rec.TOT_CMT_BURDENED_COST,
380 x_all_txn_accum_rec.TOT_CMT_QUANTITY,
381 X_Recs_processed ,
382 x_err_stack,
383 x_err_stage,
384 x_err_code);
385
386 tot_recs_processed := tot_recs_processed + x_recs_processed;
387 ELSE -- (If #4)
388 -- Fetched period != current or previous period and fetched year !=
389 -- current year (Update only ITD figures )-
390 -- Task level figures with resources
391 -- and Project-Resources - ACTUALS
392
393 PA_PROCESS_ACCUM_ACTUALS_RES.Process_it_res
394 (x_project_id,
395 x_all_txn_accum_rec.task_id,
396 Res_Accum_Rec.resource_list_id ,
397 Res_Accum_Rec.resource_list_Member_id ,
398 Res_Accum_Rec.resource_id ,
399 Res_Accum_Rec.resource_list_assignment_id ,
400 Res_Accum_Rec.track_as_labor_flag ,
401 Res_Accum_Rec.rollup_Quantity_flag ,
402 Res_Accum_Rec.unit_of_measure ,
403 x_current_period ,
404 x_all_txn_accum_rec.TOT_REVENUE,
405 x_all_txn_accum_rec.TOT_RAW_COST,
406 x_all_txn_accum_rec.TOT_BURDENED_COST,
407 x_all_txn_accum_rec.TOT_LABOR_HOURS,
408 x_quantity,
409 x_billable_quantity,
410 x_all_txn_accum_rec.TOT_BILLABLE_RAW_COST,
411 x_all_txn_accum_rec.TOT_BILLABLE_BURDENED_COST,
412 x_all_txn_accum_rec.TOT_BILLABLE_LABOR_HOURS,
413 'Y', -- x_actual_cost_flag
414 'Y', -- x_revenue_flag
415 X_Recs_processed ,
416 x_err_stack,
417 x_err_stage,
418 x_err_code);
419
420 tot_recs_processed := tot_recs_processed + x_recs_processed;
421
422 -- Fetched period != current or previous period and fetched year !=
423 -- current year (Update only ITD figures )-
424 -- Task level figures with resources
425 -- and Project-Resources - COMMITMENTS
426
427 PA_PROCESS_ACCUM_CMT_RES.Process_it_cmt_res
428 (x_project_id,
429 x_all_txn_accum_rec.task_id,
430 Res_Accum_Rec.resource_list_id ,
431 Res_Accum_Rec.resource_list_Member_id ,
432 Res_Accum_Rec.resource_id ,
433 Res_Accum_Rec.resource_list_assignment_id ,
434 Res_Accum_Rec.track_as_labor_flag ,
435 Res_Accum_Rec.rollup_Quantity_flag ,
436 Res_Accum_Rec.unit_of_measure ,
437 x_current_period ,
438 x_all_txn_accum_rec.TOT_CMT_RAW_COST,
439 x_all_txn_accum_rec.TOT_CMT_BURDENED_COST,
440 x_all_txn_accum_rec.TOT_CMT_QUANTITY,
441 X_Recs_processed ,
442 x_err_stack,
443 x_err_stage,
444 x_err_code);
445
446 tot_recs_processed := tot_recs_processed + x_recs_processed;
447 END IF; -- (IF # 4)
448 END IF; -- (IF # 2)
449 END LOOP; -- (Res_Accum_rec in Res_accum_Cur LOOP )
450 END LOOP; -- (1..No_of_res_lists LOOP )
451 END LOOP; -- (x_all_txn_accum_rec IN All_PA_Txn_Accum_Cur LOOP )
452
453
454 -- Now Update the Resource list assignement record, to set the
455 -- Resource list as Accumulated
456
457 Update PA_RESOURCE_LIST_ASSIGNMENTS
458 SET
459 RESOURCE_LIST_ACCUMULATED_FLAG = 'Y'
460 Where
461 PROJECT_ID = x_project_id
462 AND RESOURCE_LIST_ID = NVL(x_resource_list_id, RESOURCE_LIST_ID);
463
464 END IF; -- (IF #1)
465
466 -- Restore the old x_err_stack;
467
468 x_err_stack := V_Old_Stack;
469 Exception
470 When Others Then
471 x_err_code := SQLCODE;
472 RAISE ;
473 End Process_Res_Lists;
474
475 End PA_REFRESH_RES_LISTS;