[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_CORE1
Source
1 package body pa_budget_core1 as
2 -- $Header: PAXBUBDB.pls 120.6.12010000.4 2008/09/29 14:22:56 bifernan ship $\
3
4 -- Bug Fix: 4569365. Removed MRC code.
5 -- g_mrc_exception EXCEPTION;
6 --History
7 -- xx-xxx-xxxx who? - Created
8 --
9 -- 14-FEB-2005 jwhite Bug 4176179
10 -- Modifed a select statement in this procedure
11 -- to address some performance issues.
12 --
13
14 --Notes
15 --
16 -- For Copy_Actual, no modifications were made for
17 -- the FP model. Instead, the pa_budget_lines_v_pkg.insert_row
18 -- procedures, which is called often by Copy_Actual, was
19 -- modified to address new FP specs for budget lines.
20 --
21
22 procedure copy_actual (x_project_id in number,
23 x_version_id in number,
24 x_budget_entry_method_code in varchar2,
25 x_resource_list_id in number,
26 x_start_period in varchar2,
27 x_end_period in varchar2,
28 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
29 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
30 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
31 is
32 -- Standard who
33 x_created_by number(15);
34 x_last_update_login number(15);
35
36 x_entry_level_code varchar2(30);
37 x_categorization_code varchar2(30);
38 x_time_phased_type_code varchar2(30);
39 x_start_period_start_date date;
40 x_end_period_end_date date;
41 x_task_id number;
42 x_uncat_res_list_member_id number;
43 x_uncat_unit_of_measure varchar2(30);
44 x_uncat_track_as_labor_flag varchar2(2);
45 x_raw_cost number;
46 x_burdened_cost number;
47 x_revenue number;
48 x_quantity number;
49 /* Bug 6509313 Following 6 variables are added*/
50 x_new_raw_cost number;
51 x_new_burdened_cost number;
52 x_new_revenue number;
53 x_new_quantity number;
54 x_new_assignment_id number;
55 x_new_row_id rowid;
56 x_labor_hours number;
57 x_unit_of_measure varchar2(30);
58 x_resource_assignment_id number;
59 x_raw_cost_total number;
60 x_burdened_cost_total number;
61 x_revenue_total number;
62 x_quantity_total number;
63 x_labor_hours_total number;
64 x_dummy1 number;
65 x_dummy2 number;
66 x_dummy3 number;
67 x_dummy4 number;
68 x_dummy5 number;
69 x_dummy6 number;
70 x_rowid rowid;
71 old_stack varchar2(630);
72 x_budget_amount_code PA_BUDGET_TYPES.BUDGET_AMOUNT_CODE%TYPE;
73 /* Bug 2107130 Following 5 variables are added */
74 x_cost_quantity_flag pa_budget_entry_methods.cost_quantity_flag%TYPE;
75 x_raw_cost_flag pa_budget_entry_methods.raw_cost_flag%TYPE;
76 x_burdened_cost_flag pa_budget_entry_methods.burdened_cost_flag%TYPE;
77 x_rev_quantity_flag pa_budget_entry_methods.rev_quantity_flag%TYPE;
78 x_revenue_flag pa_budget_entry_methods.revenue_flag%TYPE;
79
80 -- record definition
81 type period_type is
82 record (period_name varchar2(30),
83 start_date date,
84 end_date date);
85
86 period_rec period_type;
87
88 -- cursor definition
89
90 cursor pa_cursor is
91 select period_name,
92 start_date,
93 end_date
94 from pa_periods
95 where start_date between x_start_period_start_date
96 and x_end_period_end_date;
97
98 cursor gl_cursor is
99 select p.period_name,
100 p.start_date,
101 p.end_date
102 from gl_period_statuses p,
103 pa_implementations i
104 where p.application_id = pa_period_process_pkg.application_id
105 and p.set_of_books_id = i.set_of_books_id
106 and p.adjustment_period_flag = 'N' -- Added for bug 3688017
107 and p.start_date between x_start_period_start_date
108 and x_end_period_end_date;
109
110 cursor get_budget_amount_code is
111 select budget_amount_code
112 from pa_budget_versions b,pa_budget_types t
113 where b.budget_version_id = x_version_id
114 and b.budget_type_code = t.budget_type_code;
115
116 -- Added for bug 3896747
117 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');
118
119 /* variables added for Bug 4889056 */
120 l_period_name PA_PLSQL_DATATYPES.Char240TabTyp;
121 l_start_date PA_PLSQL_DATATYPES.DateTabTyp;
122 l_end_date PA_PLSQL_DATATYPES.DateTabTyp;
123
124 l_resource_list_member_id PA_PLSQL_DATATYPES.IdTabTyp;
125 l_resource_id PA_PLSQL_DATATYPES.IdTabTyp;
126 l_track_as_labor_flag PA_PLSQL_DATATYPES.Char1TabTyp;
127
128 l_task_id PA_PLSQL_DATATYPES.IdTabTyp;
129 x_billable_raw_cost NUMBER;
130 x_billable_burdened_cost NUMBER;
131 x_billable_quantity NUMBER;
132 x_billable_labor_hours NUMBER;
133 x_cmt_raw_cost NUMBER;
134 x_cmt_burdened_cost NUMBER;
135 l_check_flag NUMBER; /* Added for Bug 6509313*/
136
137 TmpActTab pa_budget_core1.CopyActualTabTyp;
138 /* variables added for Bug 4889056 */
139
140 begin
141
142 open get_budget_amount_code;
143 fetch get_budget_amount_code into x_budget_amount_code;
144 close get_budget_amount_code;
145
146 x_err_code := 0;
147 old_stack := x_err_stack;
148 x_err_stack := x_err_stack || '->copy_actual';
149
150 -- Added for bug 3896747
151 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
152 fnd_file.put_line(1,x_err_stack);
153 End if;
154
155 x_created_by := FND_GLOBAL.USER_ID;
156 x_last_update_login := FND_GLOBAL.LOGIN_ID;
157
158 savepoint before_copy_actual;
159
160 x_err_stage := 'get budget entry method <' || x_budget_entry_method_code
161 || '>';
162
163 -- Added for bug 3896747
164 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
165 fnd_file.put_line(1,x_err_stage);
166 End if;
167 /* Bug# 2107130 Modified the following select statement */
168
169 select entry_level_code, categorization_code,
170 time_phased_type_code, cost_quantity_flag,
171 raw_cost_flag, burdened_cost_flag,
172 rev_quantity_flag, revenue_flag
173 into x_entry_level_code, x_categorization_code,
174 x_time_phased_type_code, x_cost_quantity_flag,
175 x_raw_cost_flag, x_burdened_cost_flag,
176 x_rev_quantity_flag, x_revenue_flag
177 from pa_budget_entry_methods
178 where budget_entry_method_code = x_budget_entry_method_code;
179
180 if ( (x_time_phased_type_code = 'N')
181 or (x_time_phased_type_code = 'R')) then
182 x_err_code := 10;
183 x_err_stage := 'PA_BU_INVALID_TIME_PHASED';
184 -- Added for bug 3896747
185 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
186 fnd_file.put_line(1,x_err_stage);
187 End if;
188 return;
189 end if;
190
191
192 x_err_stage := 'get uncategorized resource list member id';
193
194 -- Added for bug 3896747
195 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
196 fnd_file.put_line(1,x_err_stage);
197 End if;
198 -- FP.M Resource LIst Data Model Impact Changes, 09-JUN-04, jwhite -----------------------------
199
200 -- Augmented original code with additional filter
201
202 /* -- Original Logic
203
204
205 -- Added pa_implementations table and corr join for bug 1763100
206
207 select m.resource_list_member_id,
208 m.track_as_labor_flag,
209 r.unit_of_measure
210 into x_uncat_res_list_member_id,
211 x_uncat_track_as_labor_flag,
212 x_uncat_unit_of_measure
213 from pa_resources r,
214 pa_resource_list_members m,
215 pa_implementations i,
216 pa_resource_lists l
217 where l.uncategorized_flag = 'Y'
218 and l.resource_list_id = m.resource_list_id
219 and i.business_group_id = l.business_group_id
220 and m.resource_id = r.resource_id;
221
222 */
223
224 -- FP.M Data Model Logic
225
226
227 -- bug 4176179, 14-FEB-2005, jwhite -----------------------------------
228 -- Added two more FP.M financial element reletated filters to improve
229 -- performance.
230
231 select m.resource_list_member_id,
232 m.track_as_labor_flag,
233 r.unit_of_measure
234 into x_uncat_res_list_member_id,
235 x_uncat_track_as_labor_flag,
236 x_uncat_unit_of_measure
237 from pa_resources r,
238 pa_resource_list_members m,
239 pa_implementations i,
240 pa_resource_lists l
241 where l.uncategorized_flag = 'Y'
242 and l.resource_list_id = m.resource_list_id
243 and i.business_group_id = l.business_group_id
244 and m.resource_id = r.resource_id
245 and m.resource_class_code = 'FINANCIAL_ELEMENTS'
246 AND m.resource_class_id = 4 /* bug 4176179 */
247 AND m.resource_class_flag = 'Y'; /* bug 4176179 */
248
249 -- end bug 4176179, 14-FEB-2005, jwhite -----------------------------------
250
251 -- End: FP.M Resource LIst Data Model Impact Changes -----------------------------
252
253
254
255
256
257
258 x_err_stage := 'get start date of periods <' || x_start_period
259 || '><' || x_end_period
260 || '>';
261
262 -- Added for bug 3896747
263 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
264 fnd_file.put_line(1,x_err_stage);
265 End if;
266
267 if (x_time_phased_type_code = 'P') then
268
269 select start_date
270 into x_start_period_start_date
271 from pa_periods
272 where period_name = x_start_period;
273
274 select end_date
275 into x_end_period_end_date
276 from pa_periods
277 where period_name = x_end_period;
278
279 else
280 select start_date
281 into x_start_period_start_date
282 from gl_period_statuses p,
283 pa_implementations i
284 where p.period_name = x_start_period
285 and p.application_id = pa_period_process_pkg.application_id
286 and p.set_of_books_id = i.set_of_books_id;
287
288 select end_date
289 into x_end_period_end_date
290 from gl_period_statuses p,
291 pa_implementations i
292 where p.period_name = x_end_period
293 and p.application_id = pa_period_process_pkg.application_id
294 and p.set_of_books_id = i.set_of_books_id;
295
296 end if;
297
298 x_err_stage := 'delete budget lines <' || to_char(x_version_id)
299 || '><' || x_start_period
300 || '><' || x_end_period
301 || '>';
302
303 -- Added for bug 3896747
304 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
305 fnd_file.put_line(1,x_err_stage);
306 End if;
307 -- Bug Fix: 4569365. Removed MRC code.
308 -- pa_mrc_finplan.g_calling_module := PA_MRC_FINPLAN.G_COPY_ACTUALS; /* FPB2: MRC */
309
310 for bl_rec in (
311 select rowid
312 from pa_budget_lines l
313 where l.resource_assignment_id in
314 (select a.resource_assignment_id
315 from pa_resource_assignments a
316 where a.budget_version_id = x_version_id)
317 and l.start_date between x_start_period_start_date and
318 x_end_period_end_date) loop
319
320 pa_budget_lines_v_pkg.delete_row(X_Rowid => bl_rec.rowid);
321 -- Bug Fix: 4569365. Removed MRC code.
322 -- ,X_mrc_flag => 'Y'); /* FPB2: Added x_mrc_flag for MRC changes */
323 end loop;
324
325 -- process every period between the starting period and ending period
326
327 /* Code added for Bug 4889056 - Start Part 1 */
328
329 if (x_entry_level_code = 'P') then
330
331 if (x_categorization_code = 'N') then
332
333 -- project level, uncategorized
334 if (x_time_phased_type_code = 'P') then
335 select period_name,
336 start_date,
337 end_date
338 bulk collect into
339 l_period_name,
340 l_start_date,
341 l_end_date
342 from pa_periods
343 where start_date between x_start_period_start_date
344 and x_end_period_end_date;
345
346 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
347 SELECT
348 sum(tot_revenue),
349 sum(tot_raw_cost),
350 sum(tot_burdened_cost),
351 sum(tot_quantity),
352 sum(tot_labor_hours),
353 sum(tot_billable_raw_cost),
354 sum(tot_billable_burdened_cost),
355 sum(tot_billable_quantity),
356 sum(tot_billable_labor_hours),
357 sum(tot_cmt_raw_cost),
358 sum(tot_cmt_burdened_cost),
359 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
360 INTO
361 x_revenue,
362 x_raw_cost,
363 x_burdened_cost,
364 x_quantity,
365 x_labor_hours,
366 x_billable_raw_cost,
367 x_billable_burdened_cost,
368 x_billable_quantity,
369 x_billable_labor_hours,
370 x_cmt_raw_cost,
371 x_cmt_burdened_cost,
372 x_unit_of_measure
373 FROM
374 pa_txn_accum pta
375 WHERE
376 pta.project_id = x_project_id
377 AND EXISTS
378 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
379 WHERE
380 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
381 )
382 AND pta.pa_period = l_period_name(i);
383
384 TmpActTab(i).period_name := l_period_name(i);
385 TmpActTab(i).start_date := l_start_date(i);
386 TmpActTab(i).end_date := l_end_date(i);
387 TmpActTab(i).REVENUE := x_revenue;
388 TmpActTab(i).RAW_COST := x_raw_cost;
389 TmpActTab(i).BURDENED_COST := x_burdened_cost;
390 TmpActTab(i).QUANTITY := x_quantity;
391 TmpActTab(i).LABOR_HOURS := x_labor_hours;
392 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
393 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
394 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
395 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
396 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
397 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
398 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
399
400 END LOOP;
401 else -- x_time_phased_type_code = 'G'
402
403 select p.period_name,
404 p.start_date,
405 p.end_date
406 bulk collect into
407 l_period_name,
408 l_start_date,
409 l_end_date
410 from gl_period_statuses p,
411 pa_implementations i
412 where p.application_id = pa_period_process_pkg.application_id
413 and p.set_of_books_id = i.set_of_books_id
414 and p.adjustment_period_flag = 'N'
415 and p.start_date between x_start_period_start_date
416 and x_end_period_end_date;
417
418 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
419 SELECT
420 sum(tot_revenue),
421 sum(tot_raw_cost),
422 sum(tot_burdened_cost),
423 sum(tot_quantity),
424 sum(tot_labor_hours),
425 sum(tot_billable_raw_cost),
426 sum(tot_billable_burdened_cost),
427 sum(tot_billable_quantity),
428 sum(tot_billable_labor_hours),
429 sum(tot_cmt_raw_cost),
430 sum(tot_cmt_burdened_cost),
431 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
432 INTO
433 x_revenue,
434 x_raw_cost,
435 x_burdened_cost,
436 x_quantity,
437 x_labor_hours,
438 x_billable_raw_cost,
439 x_billable_burdened_cost,
440 x_billable_quantity,
441 x_billable_labor_hours,
442 x_cmt_raw_cost,
443 x_cmt_burdened_cost,
444 x_unit_of_measure
445 FROM
446 pa_txn_accum pta
447 WHERE
448 pta.project_id = x_project_id
449 AND EXISTS
450 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
451 WHERE
452 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
453 )
454 AND pta.gl_period = l_period_name(i);
455 TmpActTab(i).period_name := l_period_name(i);
456 TmpActTab(i).start_date := l_start_date(i);
457 TmpActTab(i).end_date := l_end_date(i);
458 TmpActTab(i).REVENUE := x_revenue;
459 TmpActTab(i).RAW_COST := x_raw_cost;
460 TmpActTab(i).BURDENED_COST := x_burdened_cost;
461 TmpActTab(i).QUANTITY := x_quantity;
462 TmpActTab(i).LABOR_HOURS := x_labor_hours;
463 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
464 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
465 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
466 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
467 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
468 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
469 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
470 END LOOP;
471 end if;
472
473 For j in TmpActTab.FIRST..TmpActTab.LAST LOOP
474 if x_budget_amount_code = 'C' then
475 TmpActTab(j).revenue := null;
476
477 if x_cost_quantity_flag = 'N' then
478 TmpActTab(j).labor_hours := null;
479 x_uncat_unit_of_measure := null;
480 end if;
481
482 if x_raw_cost_flag = 'N' then
483 TmpActTab(j).raw_cost := null;
484 end if;
485
486 if x_burdened_cost_flag = 'N' then
487 TmpActTab(j).burdened_cost := null;
488 end if;
489
490 else
491 TmpActTab(j).raw_cost := null;
492 TmpActTab(j).burdened_cost := null;
493
494 if x_rev_quantity_flag = 'N' then
495 TmpActTab(j).labor_hours := null;
496 x_uncat_unit_of_measure := null;
497 end if;
498
499 if x_revenue_flag = 'N' then
500 TmpActTab(j).revenue := null;
501 end if;
502
503 end if;
504
505 if ( (nvl(TmpActTab(j).labor_hours,0) <> 0)
506 or (nvl(TmpActTab(j).raw_cost,0) <> 0)
507 or (nvl(TmpActTab(j).burdened_cost,0) <> 0)
508 or (nvl(TmpActTab(j).revenue,0) <> 0)) then
509
510 /* Added for bug 6509313 */
511
512 BEGIN
513 l_check_flag := 0;
514 select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
515 , (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
516 , (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
517 , (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
518 , pbl.resource_assignment_id
519 , pbl.rowid
520 into x_new_quantity,
521 x_new_raw_cost,
522 x_new_burdened_cost,
523 x_new_revenue,
524 x_new_assignment_id,
525 x_new_row_id
526 from pa_budget_lines pbl
527 where pbl.resource_assignment_id in (
528 select distinct pbl1.resource_assignment_id
529 from pa_budget_lines pbl1,
530 pa_resource_assignments pra,
531 pa_resource_list_members p1,
532 pa_resource_list_members p2
533 where pra.resource_list_member_id = p2.resource_list_member_id
534 and p1.parent_member_id = p2.resource_list_member_id
535 and p1.resource_list_member_id = x_uncat_res_list_member_id
536 and pbl1.resource_assignment_id = pra.resource_assignment_id
537 and pra.budget_version_id = x_version_id
538 and pbl1.period_name = TmpActTab(j).period_name
539 )
540 and pbl.budget_version_id = x_version_id
541 and pbl.period_name = TmpActTab(j).period_name ;
542 EXCEPTION
543 WHEN NO_DATA_FOUND THEN
544 l_check_flag := 1;
545 WHEN OTHERS THEN
546 l_check_flag := 2;
547 END;
548
549 IF l_check_flag = 0 THEN
550
551 rollup_amounts_rg(
552 X_Resource_Assignment_Id => x_resource_assignment_id,
553 X_Budget_Version_Id => x_version_id,
554 X_Project_Id => x_project_id,
555 X_Task_Id => 0,
556 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
557 X_Start_Date => TmpActTab(j).start_date,
558 X_End_Date => TmpActTab(j).end_date,
559 X_Period_Name => TmpActTab(j).period_name,
560 X_Quantity => x_labor_hours,
561 X_Unit_Of_Measure => x_uncat_unit_of_measure,
562 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
563 X_Raw_Cost => TmpActTab(j).raw_cost,
564 X_Burdened_Cost => TmpActTab(j).burdened_cost,
565 X_Revenue => TmpActTab(j).revenue
566 );
567
568 pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
569 X_Resource_Assignment_Id => x_new_assignment_id,
570 X_Budget_Version_Id => x_version_id,
571 X_Project_Id => x_project_id,
572 X_Task_Id => 0,
573 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
574 X_Resource_Id => NULL,
575 X_Resource_Id_Old => NULL,
576 X_Description => NULL,
577 X_Start_Date => TmpActTab(j).start_date ,
578 X_End_Date => TmpActTab(j).end_date,
579 X_Period_Name => TmpActTab(j).period_name,
580 X_Quantity => x_new_quantity,
581 X_Quantity_Old => TmpActTab(j).labor_hours,
582 X_Unit_Of_Measure => x_uncat_unit_of_measure,
583 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
584 X_Raw_Cost => x_new_raw_cost,
585 X_Raw_Cost_Old => TmpActTab(j).raw_cost,
586 X_Burdened_Cost => x_new_burdened_cost,
587 X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
588 X_Revenue => x_new_revenue,
589 X_Revenue_Old => TmpActTab(j).revenue,
590 X_Change_Reason_Code => NULL,
591 X_Last_Update_Date => sysdate,
592 X_Last_Updated_By => x_created_by,
593 X_Last_Update_Login => x_last_update_login,
594 X_Attribute_Category => NULL,
595 X_Attribute1 => NULL,
596 X_Attribute2 => NULL,
597 X_Attribute3 => NULL,
598 X_Attribute4 => NULL,
599 X_Attribute5 => NULL,
600 X_Attribute6 => NULL,
601 X_Attribute7 => NULL,
602 X_Attribute8 => NULL,
603 X_Attribute9 => NULL,
604 X_Attribute10 => NULL,
605 X_Attribute11 => NULL,
606 X_Attribute12 => NULL,
607 X_Attribute13 => NULL,
608 X_Attribute14 => NULL,
609 X_Attribute15 => NULL,
610 -- X_mrc_flag => 'Y', -- Removed MRC code.
611 X_Calling_Process => 'PR',
612 X_raw_cost_source => 'A',
613 X_burdened_cost_source => 'A',
614 X_quantity_source => 'A',
615 X_revenue_source => 'A' );
616
617 END IF;
618 if (l_check_flag = 1)
619 THEN
620 rollup_amounts_rg(
621 X_Resource_Assignment_Id => x_resource_assignment_id,
622 X_Budget_Version_Id => x_version_id,
623 X_Project_Id => x_project_id,
624 X_Task_Id => 0,
625 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
626 X_Start_Date => TmpActTab(j).start_date,
627 X_End_Date => TmpActTab(j).end_date,
628 X_Period_Name => TmpActTab(j).period_name,
629 X_Quantity => x_labor_hours,
630 X_Unit_Of_Measure => x_uncat_unit_of_measure,
631 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
632 X_Raw_Cost => TmpActTab(j).raw_cost,
633 X_Burdened_Cost => TmpActTab(j).burdened_cost,
634 X_Revenue => TmpActTab(j).revenue
635 );
636 /* Ends added for 6509313 */
637
638 pa_budget_lines_v_pkg.insert_row (
639 X_Rowid => x_rowid,
640 X_Resource_Assignment_Id => x_resource_assignment_id,
641 X_Budget_Version_Id => x_version_id,
642 X_Project_Id => x_project_id,
643 X_Task_Id => 0,
644 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
645 X_Description => NULL,
646 X_Start_Date => TmpActTab(j).start_date,
647 X_End_Date => TmpActTab(j).end_date,
648 X_Period_Name => TmpActTab(j).period_name,
649 X_Quantity => TmpActTab(j).labor_hours,
650 X_Unit_Of_Measure => x_uncat_unit_of_measure,
651 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
652 X_Raw_Cost => TmpActTab(j).raw_cost,
653 X_Burdened_Cost => TmpActTab(j).burdened_cost,
654 X_Revenue => TmpActTab(j).revenue,
655 X_Change_Reason_Code => NULL,
656 X_Last_Update_Date => sysdate,
657 X_Last_Updated_By => x_created_by,
658 X_Creation_Date => sysdate,
659 X_Created_By => x_created_by,
660 X_Last_Update_Login => x_last_update_login,
661 X_Attribute_Category => NULL,
662 X_Attribute1 => NULL,
663 X_Attribute2 => NULL,
664 X_Attribute3 => NULL,
665 X_Attribute4 => NULL,
666 X_Attribute5 => NULL,
667 X_Attribute6 => NULL,
668 X_Attribute7 => NULL,
669 X_Attribute8 => NULL,
670 X_Attribute9 => NULL,
671 X_Attribute10 => NULL,
672 X_Attribute11 => NULL,
673 X_Attribute12 => NULL,
674 X_Attribute13 => NULL,
675 X_Attribute14 => NULL,
676 X_Attribute15 => NULL,
677 X_Calling_Process => 'PR',
678 X_Pm_Product_Code => NULL,
679 X_Pm_Budget_Line_Reference => NULL,
680 X_raw_cost_source => 'A',
681 X_burdened_cost_source => 'A',
682 X_quantity_source => 'A',
683 X_revenue_source => 'A' --,
684 --X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
685 );
686 end if; -- added for bug 6509313
687 end if;
688 END LOOP;
689 /* Code added for Bug 4889056- End Part 1 */
690 /*
691 if (x_time_phased_type_code = 'P') then
692 open pa_cursor;
693 else
694 open gl_cursor;
695 end if;
696
697 loop -- period
698
699 if (x_time_phased_type_code = 'P') then
700 fetch pa_cursor into period_rec ;
701 exit when pa_cursor%NOTFOUND;
702 else
703 fetch gl_cursor into period_rec;
704 exit when gl_cursor%NOTFOUND;
705 end if;
706
707 x_err_stage := 'process period <' || period_rec.period_name
708 || '><' || x_time_phased_type_code
709 || '>';
710
711 -- Added for bug 3896747
712 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
713 fnd_file.put_line(1,x_err_stage);
714 End if;
715
716 if (x_entry_level_code = 'P') then
717
718 if (x_categorization_code = 'N') then
719 -- project level, uncategorized
720 x_quantity := 0;
721 x_raw_cost := 0;
722 x_burdened_cost := 0;
723 x_revenue := 0;
724 x_labor_hours := 0;
725 x_unit_of_measure := NULL;
726
727 pa_accum_api.get_proj_accum_actuals(x_project_id,
728 NULL,
729 NULL,
730 x_time_phased_type_code,
731 period_rec.period_name,
732 period_rec.start_date,
733 period_rec.end_date,
734 x_revenue,
735 x_raw_cost,
736 x_burdened_cost,
737 x_quantity,
738 x_labor_hours,
739 x_dummy1,
740 x_dummy2,
741 x_dummy3,
742 x_dummy4,
743 x_dummy5,
744 x_dummy6,
745 x_unit_of_measure,
746 x_err_stage,
747 x_err_code
748 );
749
750 if (x_err_code <> 0) then
751 rollback to before_copy_actual;
752 return;
753 end if;
754
755 -- Fix for Bug # 556131
756 if x_budget_amount_code = 'C' then
757 x_revenue := null;
758
759 -- Bug# 2107130 Following three if/end if statement are added
760 if x_cost_quantity_flag = 'N' then
761 x_labor_hours := null;
762 x_uncat_unit_of_measure := null;
763 end if;
764
765 if x_raw_cost_flag = 'N' then
766 x_raw_cost := null;
767 end if;
768
769 if x_burdened_cost_flag = 'N' then
770 x_burdened_cost := null;
771 end if;
772
773 else
774 x_raw_cost := null;
775 x_burdened_cost := null;
776
777 -- Bug# 2107130 Following two if/end if statement are added
778 if x_rev_quantity_flag = 'N' then
779 x_labor_hours := null;
780 x_uncat_unit_of_measure := null;
781 end if;
782
783 if x_revenue_flag = 'N' then
784 x_revenue := null;
785 end if;
786
787 end if;
788
789 if ( (nvl(x_labor_hours,0) <> 0) -- Changed for bug 2107130
790 or (nvl(x_raw_cost,0) <> 0)
791 or (nvl(x_burdened_cost,0) <> 0)
792 or (nvl(x_revenue,0) <> 0)) then
793
794 -- ***** Bug # 2021295 - BEGIN *****
795
796 PAXBUEBU:COPY ACTUALS DOES NOT PICK UP ACTUAL REVENUE FOR WORK/EVENT BUDGET
797 Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
798 from "Positional Parameter Passing" to "Named Parameter Passing".
799
800
801 pa_budget_lines_v_pkg.insert_row (
802 X_Rowid => x_rowid,
803 X_Resource_Assignment_Id => x_resource_assignment_id,
804 X_Budget_Version_Id => x_version_id,
805 X_Project_Id => x_project_id,
806 X_Task_Id => 0,
807 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
808 X_Description => NULL,
809 X_Start_Date => period_rec.start_date,
810 X_End_Date => period_rec.end_date,
811 X_Period_Name => period_rec.period_name,
812 X_Quantity => x_labor_hours, -- Changed for bug# 2107130
813 X_Unit_Of_Measure => x_uncat_unit_of_measure,
814 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
815 X_Raw_Cost => x_raw_cost,
816 X_Burdened_Cost => x_burdened_cost,
817 X_Revenue => x_revenue,
818 X_Change_Reason_Code => NULL,
819 X_Last_Update_Date => sysdate,
820 X_Last_Updated_By => x_created_by,
821 X_Creation_Date => sysdate,
822 X_Created_By => x_created_by,
823 X_Last_Update_Login => x_last_update_login,
824 X_Attribute_Category => NULL,
825 X_Attribute1 => NULL,
826 X_Attribute2 => NULL,
827 X_Attribute3 => NULL,
828 X_Attribute4 => NULL,
829 X_Attribute5 => NULL,
830 X_Attribute6 => NULL,
831 X_Attribute7 => NULL,
832 X_Attribute8 => NULL,
833 X_Attribute9 => NULL,
834 X_Attribute10 => NULL,
835 X_Attribute11 => NULL,
836 X_Attribute12 => NULL,
837 X_Attribute13 => NULL,
838 X_Attribute14 => NULL,
839 X_Attribute15 => NULL,
840 X_Calling_Process => 'PR',
841 X_Pm_Product_Code => NULL,
842 X_Pm_Budget_Line_Reference => NULL,
843 X_raw_cost_source => 'A',
844 X_burdened_cost_source => 'A',
845 X_quantity_source => 'A',
846 X_revenue_source => 'A');
847 -- Bug Fix: 4569365. Removed MRC code.
848 --,X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
849 -- );
850 -- ***** Bug # 2021295 - END *****
851
852 if (x_err_code <> 0) then
853 rollback to before_copy_actual;
854 return;
855 end if;
856
857 end if;
858 */ -- End of commented code part 1
859 else
860
861
862 -- FP.M Resource LIst Data Model Impact Changes, 09-JUN-04, jwhite -----------------------------
863 -- Augmented original LOOP SQL to filter out planning resource list members
864 -- " and nvl(m.migration_code, 'M') = 'M' "
865
866 -- project level, categorized
867 /* Begin of part 2 - for BUg 4889056 */
868 if (x_time_phased_type_code = 'P') then
869 select p.period_name,
870 p.start_date,
871 p.end_date,
872 m.resource_list_member_id,
873 m.resource_id,
874 m.track_as_labor_flag
875 bulk collect into
876 l_period_name,
877 l_start_date,
878 l_end_date,
879 l_resource_list_member_id,
880 l_resource_id,
881 l_track_as_labor_flag
882 from pa_periods p,
883 pa_resource_list_members m
884 where m.resource_list_id = x_resource_list_id
885 and nvl(m.migration_code, 'M') = 'M'
886 and not exists
887 (select 1
888 from pa_resource_list_members m1
889 where m1.parent_member_id = m.resource_list_member_id)
890 and p.start_date between x_start_period_start_date
891 and x_end_period_end_date;
892
893 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
894 SELECT
895 sum(tot_revenue),
896 sum(tot_raw_cost),
897 sum(tot_burdened_cost),
898 sum(tot_quantity),
899 sum(tot_labor_hours),
900 sum(tot_billable_raw_cost),
901 sum(tot_billable_burdened_cost),
902 sum(tot_billable_quantity),
903 sum(tot_billable_labor_hours),
904 sum(tot_cmt_raw_cost),
905 sum(tot_cmt_burdened_cost),
906 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
907 INTO
908 x_revenue,
909 x_raw_cost,
910 x_burdened_cost,
911 x_quantity,
912 x_labor_hours,
913 x_billable_raw_cost,
914 x_billable_burdened_cost,
915 x_billable_quantity,
916 x_billable_labor_hours,
917 x_cmt_raw_cost,
918 x_cmt_burdened_cost,
919 x_unit_of_measure
920 FROM
921 pa_txn_accum pta
922 WHERE
923 pta.project_id = x_project_id
924 AND EXISTS
925 ( SELECT 'Yes'
926 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
927 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
928 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
929 ( -- Fetch both 2nd level and group level resource list member
930 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
931 FROM PA_RESOURCE_LIST_MEMBERS PRLM
932 WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
933 or
934 PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
935 )
936 )
937 AND EXISTS
938 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
939 WHERE
940 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
941 )
942 AND pta.pa_period = l_period_name(i) ;
943
944 TmpActTab(i).period_name := l_period_name(i);
945 TmpActTab(i).start_date := l_start_date(i);
946 TmpActTab(i).end_date := l_end_date(i);
947 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
948 TmpActTab(i).resource_id := l_resource_id(i);
949 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
950 TmpActTab(i).REVENUE := x_revenue;
951 TmpActTab(i).RAW_COST := x_raw_cost;
952 TmpActTab(i).BURDENED_COST := x_burdened_cost;
953 TmpActTab(i).QUANTITY := x_quantity;
954 TmpActTab(i).LABOR_HOURS := x_labor_hours;
955 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
956 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
957 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
958 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
959 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
960 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
961 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
962 END LOOP;
963 else -- x_time_phased_type_code = 'G'
964
965 select p.period_name,
966 p.start_date,
967 p.end_date,
968 m.resource_list_member_id,
969 m.resource_id,
970 m.track_as_labor_flag
971 bulk collect into
972 l_period_name,
973 l_start_date,
974 l_end_date,
975 l_resource_list_member_id,
976 l_resource_id,
977 l_track_as_labor_flag
978 from gl_period_statuses p,
979 pa_implementations i,
980 pa_resource_list_members m
981 where m.resource_list_id = x_resource_list_id
982 and not exists
983 (select 1
984 from pa_resource_list_members m1
985 where m1.parent_member_id = m.resource_list_member_id)
986 and p.application_id = pa_period_process_pkg.application_id
987 and p.set_of_books_id = i.set_of_books_id
988 and p.adjustment_period_flag = 'N'
989 and p.start_date between x_start_period_start_date
990 and x_end_period_end_date;
991
992 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
993 SELECT
994 sum(tot_revenue),
995 sum(tot_raw_cost),
996 sum(tot_burdened_cost),
997 sum(tot_quantity),
998 sum(tot_labor_hours),
999 sum(tot_billable_raw_cost),
1000 sum(tot_billable_burdened_cost),
1001 sum(tot_billable_quantity),
1002 sum(tot_billable_labor_hours),
1003 sum(tot_cmt_raw_cost),
1004 sum(tot_cmt_burdened_cost),
1005 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
1006 INTO
1007 x_revenue,
1008 x_raw_cost,
1009 x_burdened_cost,
1010 x_quantity,
1011 x_labor_hours,
1012 x_billable_raw_cost,
1013 x_billable_burdened_cost,
1014 x_billable_quantity,
1015 x_billable_labor_hours,
1016 x_cmt_raw_cost,
1017 x_cmt_burdened_cost,
1018 x_unit_of_measure
1019 FROM
1020 pa_txn_accum pta
1021 WHERE
1022 pta.project_id = x_project_id
1023 AND EXISTS
1024 ( SELECT 'Yes'
1025 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
1026 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1027 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
1028 ( -- Fetch both 2nd level and group level resource list member
1029 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
1030 FROM PA_RESOURCE_LIST_MEMBERS PRLM
1031 WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
1032 or
1033 PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
1034 )
1035 )
1036 AND EXISTS
1037 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
1038 WHERE
1039 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
1040 )
1041 AND pta.gl_period = l_period_name(i);
1042
1043 TmpActTab(i).period_name := l_period_name(i);
1044 TmpActTab(i).start_date := l_start_date(i);
1045 TmpActTab(i).end_date := l_end_date(i);
1046 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
1047 TmpActTab(i).resource_id := l_resource_id(i);
1048 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
1049 TmpActTab(i).REVENUE := x_revenue;
1050 TmpActTab(i).RAW_COST := x_raw_cost;
1051 TmpActTab(i).BURDENED_COST := x_burdened_cost;
1052 TmpActTab(i).QUANTITY := x_quantity;
1053 TmpActTab(i).LABOR_HOURS := x_labor_hours;
1054 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
1055 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
1056 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
1057 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
1058 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
1059 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
1060 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
1061 END LOOP;
1062 end if;
1063
1064 For j in TmpActTab.FIRST..TmpActTab.LAST LOOP
1065 if x_budget_amount_code = 'C' then
1066 TmpActTab(j).revenue:= null;
1067
1068 if x_cost_quantity_flag = 'N' then
1069 TmpActTab(j).quantity := null;
1070 TmpActTab(j).unit_of_measure := null;
1071 end if;
1072
1073 if x_raw_cost_flag = 'N' then
1074 TmpActTab(j).raw_cost := null;
1075 end if;
1076
1077 if x_burdened_cost_flag = 'N' then
1078 TmpActTab(j).burdened_cost := null;
1079 end if;
1080
1081 else
1082 TmpActTab(j).raw_cost := null;
1083 TmpActTab(j).burdened_cost := null;
1084
1085 if x_rev_quantity_flag = 'N' then
1086 TmpActTab(j).quantity := null;
1087 TmpActTab(j).unit_of_measure := null;
1088 end if;
1089
1090 if x_revenue_flag = 'N' then
1091 TmpActTab(j).revenue := null;
1092 end if;
1093
1094 end if;
1095
1096 if ( (nvl(TmpActTab(j).quantity,0) <> 0)
1097 or (nvl(TmpActTab(j).raw_cost,0) <> 0)
1098 or (nvl(TmpActTab(j).burdened_cost,0) <> 0)
1099 or (nvl(TmpActTab(j).revenue,0) <> 0)) then
1100
1101 /* Added for bug 6509313 */
1102
1103
1104 BEGIN
1105 l_check_flag :=0;
1106 select (NVL(quantity, 0) + nvl(TmpActTab(j).quantity, 0))
1107 , (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
1108 , (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
1109 , (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
1110 , pbl.resource_assignment_id
1111 , pbl.rowid
1112 into x_new_quantity,
1113 x_new_raw_cost,
1114 x_new_burdened_cost,
1115 x_new_revenue,
1116 x_new_assignment_id,
1117 x_new_row_id
1118 from pa_budget_lines pbl
1119 where pbl.resource_assignment_id in (
1120 select distinct pbl1.resource_assignment_id
1121 from pa_budget_lines pbl1,
1122 pa_resource_assignments pra,
1123 pa_resource_list_members p1,
1124 pa_resource_list_members p2
1125 where pra.resource_list_member_id = p2.resource_list_member_id
1126 and p1.parent_member_id = p2.resource_list_member_id
1127 and p1.resource_list_member_id = TmpActTab(j).resource_list_member_id
1128 and pbl1.resource_assignment_id = pra.resource_assignment_id
1129 and pra.budget_version_id = x_version_id
1130 and pbl1.period_name = TmpActTab(j).period_name
1131 )
1132 and pbl.budget_version_id = x_version_id
1133 and pbl.period_name = TmpActTab(j).period_name ;
1134
1135 EXCEPTION
1136 WHEN NO_DATA_FOUND THEN
1137 l_check_flag :=1;
1138 WHEN OTHERS THEN
1139 l_check_flag :=2;
1140 END;
1141
1142 IF l_check_flag =0 THEN
1143
1144 rollup_amounts_rg(
1145 X_Resource_Assignment_Id => x_resource_assignment_id,
1146 X_Budget_Version_Id => x_version_id,
1147 X_Project_Id => x_project_id,
1148 X_Task_Id => 0,
1149 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
1150 X_Start_Date => TmpActTab(j).start_date,
1151 X_End_Date => TmpActTab(j).end_date,
1152 X_Period_Name => TmpActTab(j).period_name,
1153 X_Quantity => x_new_quantity,
1154 X_Unit_Of_Measure => x_uncat_unit_of_measure,
1155 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
1156 X_Raw_Cost => TmpActTab(j).raw_cost,
1157 X_Burdened_Cost => TmpActTab(j).burdened_cost,
1158 X_Revenue => TmpActTab(j).revenue
1159 );
1160
1161 pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
1162 X_Resource_Assignment_Id => x_new_assignment_id,
1163 X_Budget_Version_Id => x_version_id,
1164 X_Project_Id => x_project_id,
1165 X_Task_Id => 0,
1166 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
1167 X_Resource_Id => NULL,
1168 X_Resource_Id_Old => NULL,
1169 X_Description => NULL,
1170 X_Start_Date => TmpActTab(j).start_date,
1171 X_End_Date => TmpActTab(j).end_date,
1172 X_Period_Name => TmpActTab(j).period_name,
1173 X_Quantity => x_new_quantity,
1174 X_Quantity_Old => TmpActTab(j).quantity,
1175 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
1176 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
1177 X_Raw_Cost => x_new_raw_cost,
1178 X_Raw_Cost_Old => TmpActTab(j).raw_cost,
1179 X_Burdened_Cost => x_new_burdened_cost,
1180 X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
1181 X_Revenue => x_new_revenue,
1182 X_Revenue_Old => TmpActTab(j).revenue,
1183 X_Change_Reason_Code => NULL,
1184 X_Last_Update_Date => sysdate,
1185 X_Last_Updated_By => x_created_by,
1186 X_Last_Update_Login => x_last_update_login,
1187 X_Attribute_Category => NULL,
1188 X_Attribute1 => NULL,
1189 X_Attribute2 => NULL,
1190 X_Attribute3 => NULL,
1191 X_Attribute4 => NULL,
1192 X_Attribute5 => NULL,
1193 X_Attribute6 => NULL,
1194 X_Attribute7 => NULL,
1195 X_Attribute8 => NULL,
1196 X_Attribute9 => NULL,
1197 X_Attribute10 => NULL,
1198 X_Attribute11 => NULL,
1199 X_Attribute12 => NULL,
1200 X_Attribute13 => NULL,
1201 X_Attribute14 => NULL,
1202 X_Attribute15 => NULL,
1203 -- X_mrc_flag => 'Y', -- Removed MRC code.
1204 X_Calling_Process => 'PR',
1205 X_raw_cost_source => 'A',
1206 X_burdened_cost_source => 'A',
1207 X_quantity_source => 'A',
1208 X_revenue_source => 'A' );
1209 END IF;
1210
1211 if (l_check_flag = 1)
1212 THEN
1213 rollup_amounts_rg(
1214 X_Resource_Assignment_Id => x_resource_assignment_id,
1215 X_Budget_Version_Id => x_version_id,
1216 X_Project_Id => x_project_id,
1217 X_Task_Id => 0,
1218 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
1219 X_Start_Date => TmpActTab(j).start_date,
1220 X_End_Date => TmpActTab(j).end_date,
1221 X_Period_Name => TmpActTab(j).period_name,
1222 X_Quantity => TmpActTab(j).quantity,
1223 X_Unit_Of_Measure => x_uncat_unit_of_measure,
1224 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
1225 X_Raw_Cost => TmpActTab(j).raw_cost,
1226 X_Burdened_Cost => TmpActTab(j).burdened_cost,
1227 X_Revenue => TmpActTab(j).revenue
1228 );
1229 /* Ends added for 6509313 */
1230
1231 pa_budget_lines_v_pkg.insert_row (
1232 X_Rowid => x_rowid,
1233 X_Resource_Assignment_Id => x_resource_assignment_id,
1234 X_Budget_Version_Id => x_version_id,
1235 X_Project_Id => x_project_id,
1236 X_Task_Id => 0,
1237 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
1238 X_Description => NULL,
1239 X_Start_Date => TmpActTab(j).start_date,
1240 X_End_Date => TmpActTab(j).end_date,
1241 X_Period_Name => TmpActTab(j).period_name,
1242 X_Quantity => TmpActTab(j).quantity,
1243 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
1244 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
1245 X_Raw_Cost => TmpActTab(j).raw_cost,
1246 X_Burdened_Cost => TmpActTab(j).burdened_cost,
1247 X_Revenue => TmpActTab(j).revenue,
1248 X_Change_Reason_Code => NULL,
1249 X_Last_Update_Date => sysdate,
1250 X_Last_Updated_By => x_created_by,
1251 X_Creation_Date => sysdate,
1252 X_Created_By => x_created_by,
1253 X_Last_Update_Login => x_last_update_login,
1254 X_Attribute_Category => NULL,
1255 X_Attribute1 => NULL,
1256 X_Attribute2 => NULL,
1257 X_Attribute3 => NULL,
1258 X_Attribute4 => NULL,
1259 X_Attribute5 => NULL,
1260 X_Attribute6 => NULL,
1261 X_Attribute7 => NULL,
1262 X_Attribute8 => NULL,
1263 X_Attribute9 => NULL,
1264 X_Attribute10 => NULL,
1265 X_Attribute11 => NULL,
1266 X_Attribute12 => NULL,
1267 X_Attribute13 => NULL,
1268 X_Attribute14 => NULL,
1269 X_Attribute15 => NULL,
1270 X_Calling_Process => 'PR',
1271 X_Pm_Product_Code => NULL,
1272 X_Pm_Budget_Line_Reference => NULL,
1273 X_raw_cost_source => 'A',
1274 X_burdened_cost_source => 'A',
1275 X_quantity_source => 'A',
1276 X_revenue_source => 'A' --,
1277 --X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
1278 );
1279
1280 end if;-- added for bug 6509313
1281 end if;
1282 END LOOP;
1283 end if;
1284 /* End of part 2 for Bug 4889056- */
1285 /* commenting part for project level, categorized
1286 for res_rec in (select m.resource_list_member_id,
1287 m.resource_id,
1288 m.track_as_labor_flag
1289 from pa_resource_list_members m
1290 where m.resource_list_id = x_resource_list_id
1291 and nvl(m.migration_code, 'M') = 'M'
1292 and not exists
1293 (select 1
1294 from pa_resource_list_members m1
1295 where m1.parent_member_id =
1296 m.resource_list_member_id)
1297 ) loop
1298
1299 x_err_stage := 'process period and resource <'
1300 || period_rec.period_name
1301 || '><' || to_char(res_rec.resource_list_member_id)
1302 || '>';
1303
1304 -- Added for bug 3896747
1305 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
1306 fnd_file.put_line(1,x_err_stage);
1307 End if;
1308
1309 x_quantity := 0;
1310 x_raw_cost := 0;
1311 x_burdened_cost := 0;
1312 x_revenue := 0;
1313 x_labor_hours := 0;
1314 x_unit_of_measure := NULL;
1315
1316 pa_accum_api.get_proj_accum_actuals(x_project_id,
1317 NULL,
1318 res_rec.resource_list_member_id,
1319 x_time_phased_type_code,
1320 period_rec.period_name,
1321 period_rec.start_date,
1322 period_rec.end_date,
1323 x_revenue,
1324 x_raw_cost,
1325 x_burdened_cost,
1326 x_quantity,
1327 x_labor_hours,
1328 x_dummy1,
1329 x_dummy2,
1330 x_dummy3,
1331 x_dummy4,
1332 x_dummy5,
1333 x_dummy6,
1334 x_unit_of_measure,
1335 x_err_stage,
1336 x_err_code
1337 );
1338
1339 if (x_err_code <> 0) then
1340 rollback to before_copy_actual;
1341 return;
1342 end if;
1343
1344 -- Fix for Bug # 556131
1345 if x_budget_amount_code = 'C' then
1346 x_revenue := null;
1347
1348 -- Bug# 2107130 Following three if/end if statement are added
1349 if x_cost_quantity_flag = 'N' then
1350 x_quantity := null;
1351 x_unit_of_measure := null;
1352 end if;
1353
1354 if x_raw_cost_flag = 'N' then
1355 x_raw_cost := null;
1356 end if;
1357
1358 if x_burdened_cost_flag = 'N' then
1359 x_burdened_cost := null;
1360 end if;
1361
1362 else
1363 x_raw_cost := null;
1364 x_burdened_cost := null;
1365
1366 -- Bug# 2107130 Following two if/end if statement are added
1367 if x_rev_quantity_flag = 'N' then
1368 x_quantity := null;
1369 x_unit_of_measure := null;
1370 end if;
1371
1372 if x_revenue_flag = 'N' then
1373 x_revenue := null;
1374 end if;
1375
1376 end if;
1377
1378 if ( (nvl(x_quantity,0) <> 0)
1379 or (nvl(x_raw_cost,0) <> 0)
1380 or (nvl(x_burdened_cost,0) <> 0)
1381 or (nvl(x_revenue,0) <> 0)) then
1382
1383 -- ***** Bug # 2021295 - BEGIN *****
1384
1385 PAXBUEBU:COPY ACTUALS DOES NOT PICK UP ACTUAL REVENUE FOR WORK/EVENT BUDGET
1386 Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
1387 from "Positional Parameter Passing" to "Named Parameter Passing".
1388
1389
1390 pa_budget_lines_v_pkg.insert_row (
1391 X_Rowid => x_rowid,
1392 X_Resource_Assignment_Id => x_resource_assignment_id,
1393 X_Budget_Version_Id => x_version_id,
1394 X_Project_Id => x_project_id,
1395 X_Task_Id => 0,
1396 X_Resource_List_Member_Id => res_rec.resource_list_member_id,
1397 X_Description => NULL,
1398 X_Start_Date => period_rec.start_date,
1399 X_End_Date => period_rec.end_date,
1400 X_Period_Name => period_rec.period_name,
1401 X_Quantity => x_quantity,
1402 X_Unit_Of_Measure => x_unit_of_measure,
1403 X_Track_As_Labor_Flag => res_rec.track_as_labor_flag,
1404 X_Raw_Cost => x_raw_cost,
1405 X_Burdened_Cost => x_burdened_cost,
1406 X_Revenue => x_revenue,
1407 X_Change_Reason_Code => NULL,
1408 X_Last_Update_Date => sysdate,
1409 X_Last_Updated_By => x_created_by,
1410 X_Creation_Date => sysdate,
1411 X_Created_By => x_created_by,
1412 X_Last_Update_Login => x_last_update_login,
1413 X_Attribute_Category => NULL,
1414 X_Attribute1 => NULL,
1415 X_Attribute2 => NULL,
1416 X_Attribute3 => NULL,
1417 X_Attribute4 => NULL,
1418 X_Attribute5 => NULL,
1419 X_Attribute6 => NULL,
1420 X_Attribute7 => NULL,
1421 X_Attribute8 => NULL,
1422 X_Attribute9 => NULL,
1423 X_Attribute10 => NULL,
1424 X_Attribute11 => NULL,
1425 X_Attribute12 => NULL,
1426 X_Attribute13 => NULL,
1427 X_Attribute14 => NULL,
1428 X_Attribute15 => NULL,
1429 X_Calling_Process => 'PR',
1430 X_Pm_Product_Code => NULL,
1431 X_Pm_Budget_Line_Reference => NULL,
1432 X_raw_cost_source => 'A',
1433 X_burdened_cost_source => 'A',
1434 X_quantity_source => 'A',
1435 X_revenue_source => 'A');
1436 -- Bug Fix: 4569365. Removed MRC code.
1437 --,X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
1438 -- );
1439 -- ***** Bug # 2021295 - END *****
1440
1441 if (x_err_code <> 0) then
1442 rollback to before_copy_actual;
1443 return;
1444 end if;
1445
1446 end if;
1447
1448 end loop; -- resource
1449
1450 end if;
1451 */
1452 /* begin of part 3 - Bug 4889056*/
1453 elsif (x_entry_level_code = 'T') then
1454
1455 if (x_categorization_code = 'N') then
1456
1457 -- lowest level task, uncategorized
1458 if (x_time_phased_type_code = 'P') then
1459 select p.period_name,
1460 p.start_date,
1461 p.end_date,
1462 t.task_id
1463 bulk collect into
1464 l_period_name,
1465 l_start_date,
1466 l_end_date,
1467 l_task_id
1468 from pa_periods p,
1469 pa_tasks t
1470 where t.project_id = x_project_id
1471 and t.task_id = t.top_task_id
1472 and p.start_date between x_start_period_start_date
1473 and x_end_period_end_date;
1474
1475 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
1476 SELECT
1477 sum(tot_revenue),
1478 sum(tot_raw_cost),
1479 sum(tot_burdened_cost),
1480 sum(tot_quantity),
1481 sum(tot_labor_hours),
1482 sum(tot_billable_raw_cost),
1483 sum(tot_billable_burdened_cost),
1484 sum(tot_billable_quantity),
1485 sum(tot_billable_labor_hours),
1486 sum(tot_cmt_raw_cost),
1487 sum(tot_cmt_burdened_cost),
1488 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
1489 INTO
1490 x_revenue,
1491 x_raw_cost,
1492 x_burdened_cost,
1493 x_quantity,
1494 x_labor_hours,
1495 x_billable_raw_cost,
1496 x_billable_burdened_cost,
1497 x_billable_quantity,
1498 x_billable_labor_hours,
1499 x_cmt_raw_cost,
1500 x_cmt_burdened_cost,
1501 x_unit_of_measure
1502 FROM
1503 pa_txn_accum pta
1504 WHERE
1505 pta.project_id = x_project_id
1506 AND pta.task_id IN
1507 (SELECT
1508 task_id
1509 FROM
1510 pa_tasks
1511 CONNECT BY PRIOR task_id = parent_task_id
1512 START WITH task_id = l_task_id(i)
1513 )
1514 AND EXISTS
1515 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
1516 WHERE
1517 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
1518 )
1519 AND pta.pa_period = l_period_name(i) ;
1520
1521 TmpActTab(i).period_name := l_period_name(i);
1522 TmpActTab(i).start_date := l_start_date(i);
1523 TmpActTab(i).end_date := l_end_date(i);
1524 TmpActTab(i).task_id := l_task_id(i);
1525 TmpActTab(i).REVENUE := x_revenue;
1526 TmpActTab(i).RAW_COST := x_raw_cost;
1527 TmpActTab(i).BURDENED_COST := x_burdened_cost;
1528 TmpActTab(i).QUANTITY := x_quantity;
1529 TmpActTab(i).LABOR_HOURS := x_labor_hours;
1530 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
1531 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
1532 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
1533 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
1534 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
1535 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
1536 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
1537 END LOOP;
1538 else -- x_time_phased_type_code = 'G'
1539
1540 select p.period_name,
1541 p.start_date,
1542 p.end_date,
1543 t.task_id
1544 bulk collect into
1545 l_period_name,
1546 l_start_date,
1547 l_end_date,
1548 l_task_id
1549 from gl_period_statuses p,
1550 pa_implementations i,
1551 pa_tasks t
1552 where t.project_id = x_project_id
1553 and t.task_id = t.top_task_id
1554 and p.application_id = pa_period_process_pkg.application_id
1555 and p.set_of_books_id = i.set_of_books_id
1556 and p.adjustment_period_flag = 'N'
1557 and p.start_date between x_start_period_start_date
1558 and x_end_period_end_date;
1559
1560 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
1561 SELECT
1562 sum(tot_revenue),
1563 sum(tot_raw_cost),
1564 sum(tot_burdened_cost),
1565 sum(tot_quantity),
1566 sum(tot_labor_hours),
1567 sum(tot_billable_raw_cost),
1568 sum(tot_billable_burdened_cost),
1569 sum(tot_billable_quantity),
1570 sum(tot_billable_labor_hours),
1571 sum(tot_cmt_raw_cost),
1572 sum(tot_cmt_burdened_cost),
1573 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
1574 INTO
1575 x_revenue,
1576 x_raw_cost,
1577 x_burdened_cost,
1578 x_quantity,
1579 x_labor_hours,
1580 x_billable_raw_cost,
1581 x_billable_burdened_cost,
1582 x_billable_quantity,
1583 x_billable_labor_hours,
1584 x_cmt_raw_cost,
1585 x_cmt_burdened_cost,
1586 x_unit_of_measure
1587 FROM
1588 pa_txn_accum pta
1589 WHERE
1590 pta.project_id = x_project_id
1591 AND pta.task_id IN
1592 (SELECT
1593 task_id
1594 FROM
1595 pa_tasks
1596 CONNECT BY PRIOR task_id = parent_task_id
1597 START WITH task_id = l_task_id(i)
1598 )
1599 AND EXISTS
1600 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
1601 WHERE
1602 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
1603 )
1604 AND pta.gl_period = l_period_name(i);
1605
1606 TmpActTab(i).period_name := l_period_name(i);
1607 TmpActTab(i).start_date := l_start_date(i);
1608 TmpActTab(i).end_date := l_end_date(i);
1609 TmpActTab(i).task_id := l_task_id(i);
1610 TmpActTab(i).REVENUE := x_revenue;
1611 TmpActTab(i).RAW_COST := x_raw_cost;
1612 TmpActTab(i).BURDENED_COST := x_burdened_cost;
1613 TmpActTab(i).QUANTITY := x_quantity;
1614 TmpActTab(i).LABOR_HOURS := x_labor_hours;
1615 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
1616 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
1617 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
1618 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
1619 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
1620 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
1621 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
1622 END LOOP;
1623 end if;
1624
1625 For j in TmpActTab.FIRST..TmpActTab.LAST LOOP
1626 if x_budget_amount_code = 'C' then
1627 TmpActTab(j).revenue := null;
1628
1629 if x_cost_quantity_flag = 'N' then
1630 TmpActTab(j).labor_hours := null;
1631 x_uncat_unit_of_measure := null;
1632 end if;
1633
1634 if x_raw_cost_flag = 'N' then
1635 TmpActTab(j).raw_cost := null;
1636 end if;
1637
1638 if x_burdened_cost_flag = 'N' then
1639 TmpActTab(j).burdened_cost := null;
1640 end if;
1641
1642 else
1643 TmpActTab(j).raw_cost := null;
1644 TmpActTab(j).burdened_cost := null;
1645
1646 if x_rev_quantity_flag = 'N' then
1647 TmpActTab(j).labor_hours := null;
1648 x_uncat_unit_of_measure := null;
1649 end if;
1650
1651 if x_revenue_flag = 'N' then
1652 TmpActTab(j).revenue := null;
1653 end if;
1654
1655 end if;
1656
1657 if ( (nvl(TmpActTab(j).labor_hours,0) <> 0)
1658 or (nvl(TmpActTab(j).raw_cost,0) <> 0)
1659 or (nvl(TmpActTab(j).burdened_cost,0) <> 0)
1660 or (nvl(TmpActTab(j).revenue,0) <> 0)) then
1661
1662 /* Added for bug 6509313 */
1663
1664 BEGIN
1665 l_check_flag := 0;
1666 select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
1667 , (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
1668 , (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
1669 , (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
1670 , pbl.resource_assignment_id
1671 , pbl.rowid
1672 into x_new_quantity,
1673 x_new_raw_cost,
1674 x_new_burdened_cost,
1675 x_new_revenue,
1676 x_new_assignment_id,
1677 x_new_row_id
1678 from pa_budget_lines pbl
1679 where pbl.resource_assignment_id in (
1680 select distinct pbl1.resource_assignment_id
1681 from pa_budget_lines pbl1,
1682 pa_resource_assignments pra,
1683 pa_resource_list_members p1,
1684 pa_resource_list_members p2
1685 where pra.resource_list_member_id = p2.resource_list_member_id
1686 and p1.parent_member_id = p2.resource_list_member_id
1687 and p1.resource_list_member_id = x_uncat_res_list_member_id
1688 and pbl1.resource_assignment_id = pra.resource_assignment_id
1689 and pra.budget_version_id = x_version_id
1690 and pra.task_id = TmpActTab(j).task_id
1691 and pbl1.period_name = TmpActTab(j).period_name
1692 )
1693 and pbl.budget_version_id = x_version_id
1694 and pbl.period_name =TmpActTab(j).period_name ;
1695 EXCEPTION
1696 WHEN NO_DATA_FOUND THEN
1697 l_check_flag:=1;
1698 WHEN OTHERS THEN
1699 l_check_flag:=2;
1700 END;
1701
1702 IF l_check_flag = 0 THEN
1703
1704 rollup_amounts_rg(
1705 X_Resource_Assignment_Id => x_resource_assignment_id,
1706 X_Budget_Version_Id => x_version_id,
1707 X_Project_Id => x_project_id,
1708 X_Task_Id => TmpActTab(j).task_id,
1709 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
1710 X_Start_Date => TmpActTab(j).start_date,
1711 X_End_Date => TmpActTab(j).end_date,
1712 X_Period_Name => TmpActTab(j).period_name,
1713 X_Quantity => TmpActTab(j).labor_hours,
1714 X_Unit_Of_Measure => x_uncat_unit_of_measure,
1715 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
1716 X_Raw_Cost => TmpActTab(j).raw_cost,
1717 X_Burdened_Cost => TmpActTab(j).burdened_cost,
1718 X_Revenue => TmpActTab(j).revenue
1719 );
1720
1721 pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
1722 X_Resource_Assignment_Id => x_new_assignment_id,
1723 X_Budget_Version_Id => x_version_id,
1724 X_Project_Id => x_project_id,
1725 X_Task_Id => TmpActTab(j).task_id,
1726 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
1727 X_Resource_Id => NULL,
1728 X_Resource_Id_Old => NULL,
1729 X_Description => NULL,
1730 X_Start_Date => TmpActTab(j).start_date,
1731 X_End_Date => TmpActTab(j).end_date,
1732 X_Period_Name => TmpActTab(j).period_name,
1733 X_Quantity => x_new_quantity,
1734 X_Quantity_Old => TmpActTab(j).labor_hours,
1735 X_Unit_Of_Measure => x_uncat_unit_of_measure,
1736 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
1737 X_Raw_Cost => x_new_raw_cost,
1738 X_Raw_Cost_Old => TmpActTab(j).raw_cost,
1739 X_Burdened_Cost => x_new_burdened_cost,
1740 X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
1741 X_Revenue => x_new_revenue,
1742 X_Revenue_Old => TmpActTab(j).revenue,
1743 X_Change_Reason_Code => NULL,
1744 X_Last_Update_Date => sysdate,
1745 X_Last_Updated_By => x_created_by,
1746 X_Last_Update_Login => x_last_update_login,
1747 X_Attribute_Category => NULL,
1748 X_Attribute1 => NULL,
1749 X_Attribute2 => NULL,
1750 X_Attribute3 => NULL,
1751 X_Attribute4 => NULL,
1752 X_Attribute5 => NULL,
1753 X_Attribute6 => NULL,
1754 X_Attribute7 => NULL,
1755 X_Attribute8 => NULL,
1756 X_Attribute9 => NULL,
1757 X_Attribute10 => NULL,
1758 X_Attribute11 => NULL,
1759 X_Attribute12 => NULL,
1760 X_Attribute13 => NULL,
1761 X_Attribute14 => NULL,
1762 X_Attribute15 => NULL,
1763 -- X_mrc_flag => 'Y', -- Removed MRC code.
1764 X_Calling_Process => 'PR',
1765 X_raw_cost_source => 'A',
1766 X_burdened_cost_source => 'A',
1767 X_quantity_source => 'A',
1768 X_revenue_source => 'A' );
1769 end if;
1770
1771 if (l_check_flag = 1) THEN
1772 rollup_amounts_rg(
1773 X_Resource_Assignment_Id => x_resource_assignment_id,
1774 X_Budget_Version_Id => x_version_id,
1775 X_Project_Id => x_project_id,
1776 X_Task_Id => TmpActTab(j).task_id,
1777 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
1778 X_Start_Date => TmpActTab(j).start_date,
1779 X_End_Date => TmpActTab(j).end_date,
1780 X_Period_Name => TmpActTab(j).period_name,
1781 X_Quantity => TmpActTab(j).labor_hours,
1782 X_Unit_Of_Measure => x_uncat_unit_of_measure,
1783 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
1784 X_Raw_Cost => TmpActTab(j).raw_cost,
1785 X_Burdened_Cost => TmpActTab(j).burdened_cost,
1786 X_Revenue => TmpActTab(j).revenue
1787 );
1788 /* Ends added for 6509313 */
1789
1790 pa_budget_lines_v_pkg.insert_row (
1791 X_Rowid => x_rowid,
1792 X_Resource_Assignment_Id => x_resource_assignment_id,
1793 X_Budget_Version_Id => x_version_id,
1794 X_Project_Id => x_project_id,
1795 X_Task_Id => TmpActTab(j).task_id,
1796 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
1797 X_Description => NULL,
1798 X_Start_Date => TmpActTab(j).start_date,
1799 X_End_Date => TmpActTab(j).end_date,
1800 X_Period_Name => TmpActTab(j).period_name,
1801 X_Quantity => TmpActTab(j).labor_hours,
1802 X_Unit_Of_Measure => x_uncat_unit_of_measure,
1803 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
1804 X_Raw_Cost => TmpActTab(j).raw_cost,
1805 X_Burdened_Cost => TmpActTab(j).burdened_cost,
1806 X_Revenue => TmpActTab(j).revenue,
1807 X_Change_Reason_Code => NULL,
1808 X_Last_Update_Date => sysdate,
1809 X_Last_Updated_By => x_created_by,
1810 X_Creation_Date => sysdate,
1811 X_Created_By => x_created_by,
1812 X_Last_Update_Login => x_last_update_login,
1813 X_Attribute_Category => NULL,
1814 X_Attribute1 => NULL,
1815 X_Attribute2 => NULL,
1816 X_Attribute3 => NULL,
1817 X_Attribute4 => NULL,
1818 X_Attribute5 => NULL,
1819 X_Attribute6 => NULL,
1820 X_Attribute7 => NULL,
1821 X_Attribute8 => NULL,
1822 X_Attribute9 => NULL,
1823 X_Attribute10 => NULL,
1824 X_Attribute11 => NULL,
1825 X_Attribute12 => NULL,
1826 X_Attribute13 => NULL,
1827 X_Attribute14 => NULL,
1828 X_Attribute15 => NULL,
1829 X_Calling_Process => 'PR',
1830 X_Pm_Product_Code => NULL,
1831 X_Pm_Budget_Line_Reference => NULL,
1832 X_raw_cost_source => 'A',
1833 X_burdened_cost_source => 'A',
1834 X_quantity_source => 'A',
1835 X_revenue_source => 'A' --,
1836 --X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
1837 );
1838
1839 end if;
1840 end if; -- added for bug 6509313
1841 End Loop;
1842 else
1843
1844 -- top level task, categorized
1845 if (x_time_phased_type_code = 'P') then
1846 select p.period_name,
1847 p.start_date,
1848 p.end_date,
1849 t.task_id,
1850 m.resource_list_member_id,
1851 m.resource_id,
1852 m.track_as_labor_flag
1853 bulk collect into
1854 l_period_name,
1855 l_start_date,
1856 l_end_date,
1857 l_task_id,
1858 l_resource_list_member_id,
1859 l_resource_id,
1860 l_track_as_labor_flag
1861 from pa_periods p,
1862 pa_tasks t,
1863 pa_resource_list_members m
1864 where m.resource_list_id = x_resource_list_id
1865 and nvl(m.migration_code, 'M') = 'M'
1866 and not exists
1867 (select 1
1868 from pa_resource_list_members m1
1869 where m1.parent_member_id =
1870 m.resource_list_member_id)
1871 and t.project_id = x_project_id
1872 and t.task_id = t.top_task_id
1873 and p.start_date between x_start_period_start_date
1874 and x_end_period_end_date;
1875
1876 x_err_stage := 'PA: Period Before Calling the For Loop';
1877 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
1878 x_err_stage := 'PA: Period Inside the For Loop';
1879 SELECT
1880 sum(tot_revenue),
1881 sum(tot_raw_cost),
1882 sum(tot_burdened_cost),
1883 sum(tot_quantity),
1884 sum(tot_labor_hours),
1885 sum(tot_billable_raw_cost),
1886 sum(tot_billable_burdened_cost),
1887 sum(tot_billable_quantity),
1888 sum(tot_billable_labor_hours),
1889 sum(tot_cmt_raw_cost),
1890 sum(tot_cmt_burdened_cost),
1891 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
1892 INTO
1893 x_revenue,
1894 x_raw_cost,
1895 x_burdened_cost,
1896 x_quantity,
1897 x_labor_hours,
1898 x_billable_raw_cost,
1899 x_billable_burdened_cost,
1900 x_billable_quantity,
1901 x_billable_labor_hours,
1902 x_cmt_raw_cost,
1903 x_cmt_burdened_cost,
1904 x_unit_of_measure
1905 FROM
1906 pa_txn_accum pta
1907 WHERE
1908 pta.project_id = x_project_id
1909 AND pta.task_id IN
1910 (SELECT
1911 task_id
1912 FROM
1913 pa_tasks
1914 CONNECT BY PRIOR task_id = parent_task_id
1915 START WITH task_id = l_task_id(i)
1916 )
1917 AND EXISTS
1918 ( SELECT 'Yes'
1919 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
1920 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1921 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
1922 ( -- Fetch both 2nd level and group level resource list member
1923 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
1924 FROM PA_RESOURCE_LIST_MEMBERS PRLM
1925 WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
1926 or
1927 PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
1928 )
1929 )
1930 AND EXISTS
1931 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
1932 WHERE
1933 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
1934 )
1935 AND pta.pa_period = l_period_name(i) ;
1936
1937 x_err_stage := 'PA: Period Before inserting into TmpActTab';
1938 TmpActTab(i).period_name := l_period_name(i);
1939 TmpActTab(i).start_date := l_start_date(i);
1940 TmpActTab(i).end_date := l_end_date(i);
1941 TmpActTab(i).task_id := l_task_id(i);
1942 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
1943 TmpActTab(i).resource_id := l_resource_id(i);
1944 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
1945 TmpActTab(i).REVENUE := x_revenue;
1946 TmpActTab(i).RAW_COST := x_raw_cost;
1947 TmpActTab(i).BURDENED_COST := x_burdened_cost;
1948 TmpActTab(i).QUANTITY := x_quantity;
1949 TmpActTab(i).LABOR_HOURS := x_labor_hours;
1950 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
1951 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
1952 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
1953 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
1954 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
1955 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
1956 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
1957 x_err_stage := 'PA: Period After inserting into TmpActTab';
1958 END LOOP;
1959 else -- x_time_phased_type_code = 'G'
1960
1961 select p.period_name,
1962 p.start_date,
1963 p.end_date,
1964 t.task_id,
1965 m.resource_list_member_id,
1966 m.resource_id,
1967 m.track_as_labor_flag
1968 bulk collect into
1969 l_period_name,
1970 l_start_date,
1971 l_end_date,
1972 l_task_id,
1973 l_resource_list_member_id,
1974 l_resource_id,
1975 l_track_as_labor_flag
1976 from gl_period_statuses p,
1977 pa_implementations i,
1978 pa_tasks t,
1979 pa_resource_list_members m
1980 where m.resource_list_id = x_resource_list_id
1981 and not exists
1982 (select 1
1983 from pa_resource_list_members m1
1984 where m1.parent_member_id =
1985 m.resource_list_member_id)
1986 and t.project_id = x_project_id
1987 and t.task_id = t.top_task_id
1988 and p.application_id = pa_period_process_pkg.application_id
1989 and p.set_of_books_id = i.set_of_books_id
1990 and p.adjustment_period_flag = 'N'
1991 and p.start_date between x_start_period_start_date
1992 and x_end_period_end_date;
1993
1994 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
1995 SELECT
1996 sum(tot_revenue),
1997 sum(tot_raw_cost),
1998 sum(tot_burdened_cost),
1999 sum(tot_quantity),
2000 sum(tot_labor_hours),
2001 sum(tot_billable_raw_cost),
2002 sum(tot_billable_burdened_cost),
2003 sum(tot_billable_quantity),
2004 sum(tot_billable_labor_hours),
2005 sum(tot_cmt_raw_cost),
2006 sum(tot_cmt_burdened_cost),
2007 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
2008 INTO
2009 x_revenue,
2010 x_raw_cost,
2011 x_burdened_cost,
2012 x_quantity,
2013 x_labor_hours,
2014 x_billable_raw_cost,
2015 x_billable_burdened_cost,
2016 x_billable_quantity,
2017 x_billable_labor_hours,
2018 x_cmt_raw_cost,
2019 x_cmt_burdened_cost,
2020 x_unit_of_measure
2021 FROM
2022 pa_txn_accum pta
2023 WHERE
2024 pta.project_id = x_project_id
2025 AND pta.task_id IN
2026 (SELECT
2027 task_id
2028 FROM
2029 pa_tasks
2030 CONNECT BY PRIOR task_id = parent_task_id
2031 START WITH task_id = l_task_id(i)
2032 )
2033 AND EXISTS
2034 ( SELECT 'Yes'
2035 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
2036 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
2037 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
2038 ( -- Fetch both 2nd level and group level resource list member
2039 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
2040 FROM PA_RESOURCE_LIST_MEMBERS PRLM
2041 WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
2042 or
2043 PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
2044 )
2045 )
2046 AND EXISTS
2047 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
2048 WHERE
2049 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
2050 )
2051 AND pta.gl_period = l_period_name(i);
2052
2053 x_err_stage := 'Before inserting into TmpActTab';
2054 TmpActTab(i).period_name := l_period_name(i);
2055 TmpActTab(i).start_date := l_start_date(i);
2056 TmpActTab(i).end_date := l_end_date(i);
2057 TmpActTab(i).task_id := l_task_id(i);
2058 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
2059 TmpActTab(i).resource_id := l_resource_id(i);
2060 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
2061 TmpActTab(i).REVENUE := x_revenue;
2062 TmpActTab(i).RAW_COST := x_raw_cost;
2063 TmpActTab(i).BURDENED_COST := x_burdened_cost;
2064 TmpActTab(i).QUANTITY := x_quantity;
2065 TmpActTab(i).LABOR_HOURS := x_labor_hours;
2066 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
2067 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
2068 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
2069 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
2070 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
2071 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
2072 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
2073 x_err_stage := 'After inserting into TmpActTab';
2074 END LOOP;
2075 end if;
2076
2077 For j in TmpActTab.FIRST..TmpActTab.LAST LOOP
2078 if x_budget_amount_code = 'C' then
2079 TmpActTab(j).revenue := null;
2080
2081 if x_cost_quantity_flag = 'N' then
2082 TmpActTab(j).quantity := null;
2083 TmpActTab(j).unit_of_measure := null;
2084 end if;
2085
2086 if x_raw_cost_flag = 'N' then
2087 TmpActTab(j).raw_cost := null;
2088 end if;
2089
2090 if x_burdened_cost_flag = 'N' then
2091 TmpActTab(j).burdened_cost := null;
2092 end if;
2093
2094 else
2095 TmpActTab(j).raw_cost := null;
2096 TmpActTab(j).burdened_cost := null;
2097
2098 if x_rev_quantity_flag = 'N' then
2099 TmpActTab(j).quantity := null;
2100 TmpActTab(j).unit_of_measure := null;
2101 end if;
2102
2103 if x_revenue_flag = 'N' then
2104 TmpActTab(j).revenue := null;
2105 end if;
2106
2107 end if;
2108
2109 if ( (nvl(TmpActTab(j).quantity,0) <> 0)
2110 or (nvl(TmpActTab(j).raw_cost,0) <> 0)
2111 or (nvl(TmpActTab(j).burdened_cost,0) <> 0)
2112 or (nvl(TmpActTab(j).revenue,0) <> 0)) then
2113
2114 /* Added for bug 6509313 */
2115
2116 BEGIN
2117 l_check_flag := 0;
2118 select (NVL(quantity, 0) + nvl(TmpActTab(j).quantity, 0))
2119 , (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
2120 , (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
2121 , (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
2122 , pbl.resource_assignment_id
2123 , pbl.rowid
2124 into x_new_quantity,
2125 x_new_raw_cost,
2126 x_new_burdened_cost,
2127 x_new_revenue,
2128 x_new_assignment_id,
2129 x_new_row_id
2130 from pa_budget_lines pbl
2131 where pbl.resource_assignment_id in (
2132 select distinct pbl1.resource_assignment_id
2133 from pa_budget_lines pbl1,
2134 pa_resource_assignments pra,
2135 pa_resource_list_members p1,
2136 pa_resource_list_members p2
2137 where pra.resource_list_member_id = p2.resource_list_member_id
2138 and p1.parent_member_id = p2.resource_list_member_id
2139 and p1.resource_list_member_id = TmpActTab(j).resource_list_member_id
2140 and pbl1.resource_assignment_id = pra.resource_assignment_id
2141 and pra.budget_version_id = x_version_id
2142 and pra.task_id = TmpActTab(j).task_id
2143 and pbl1.period_name = TmpActTab(j).period_name
2144 )
2145 and pbl.budget_version_id = x_version_id
2146 and pbl.period_name = TmpActTab(j).period_name ;
2147
2148 EXCEPTION
2149 WHEN NO_DATA_FOUND THEN
2150 l_check_flag := 1;
2151 WHEN OTHERS THEN
2152 l_check_flag := 2;
2153 END;
2154
2155
2156 IF l_check_flag = 0 then
2157
2158 rollup_amounts_rg(
2159 X_Resource_Assignment_Id => x_resource_assignment_id,
2160 X_Budget_Version_Id => x_version_id,
2161 X_Project_Id => x_project_id,
2162 X_Task_Id => TmpActTab(j).task_id,
2163 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
2164 X_Start_Date => TmpActTab(j).start_date,
2165 X_End_Date => TmpActTab(j).end_date,
2166 X_Period_Name => TmpActTab(j).period_name,
2167 X_Quantity => TmpActTab(j).quantity,
2168 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
2169 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
2170 X_Raw_Cost => TmpActTab(j).raw_cost,
2171 X_Burdened_Cost => TmpActTab(j).burdened_cost,
2172 X_Revenue => TmpActTab(j).revenue
2173 );
2174
2175 pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
2176 X_Resource_Assignment_Id => x_new_assignment_id,
2177 X_Budget_Version_Id => x_version_id,
2178 X_Project_Id => x_project_id,
2179 X_Task_Id => TmpActTab(j).task_id,
2180 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
2181 X_Resource_Id => NULL,
2182 X_Resource_Id_Old => NULL,
2183 X_Description => NULL,
2184 X_Start_Date => TmpActTab(j).start_date,
2185 X_End_Date => TmpActTab(j).end_date,
2186 X_Period_Name => TmpActTab(j).period_name,
2187 X_Quantity => x_new_quantity,
2188 X_Quantity_Old => TmpActTab(j).quantity,
2189 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
2190 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
2191 X_Raw_Cost => x_new_raw_cost,
2192 X_Raw_Cost_Old => TmpActTab(j).raw_cost,
2193 X_Burdened_Cost => x_new_burdened_cost,
2194 X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
2195 X_Revenue => x_new_revenue,
2196 X_Revenue_Old => TmpActTab(j).revenue,
2197 X_Change_Reason_Code => NULL,
2198 X_Last_Update_Date => sysdate,
2199 X_Last_Updated_By => x_created_by,
2200 X_Last_Update_Login => x_last_update_login,
2201 X_Attribute_Category => NULL,
2202 X_Attribute1 => NULL,
2203 X_Attribute2 => NULL,
2204 X_Attribute3 => NULL,
2205 X_Attribute4 => NULL,
2206 X_Attribute5 => NULL,
2207 X_Attribute6 => NULL,
2208 X_Attribute7 => NULL,
2209 X_Attribute8 => NULL,
2210 X_Attribute9 => NULL,
2211 X_Attribute10 => NULL,
2212 X_Attribute11 => NULL,
2213 X_Attribute12 => NULL,
2214 X_Attribute13 => NULL,
2215 X_Attribute14 => NULL,
2216 X_Attribute15 => NULL,
2217 -- X_mrc_flag => 'Y', -- Removed MRC code.
2218 X_Calling_Process => 'PR',
2219 X_raw_cost_source => 'A',
2220 X_burdened_cost_source => 'A',
2221 X_quantity_source => 'A',
2222 X_revenue_source => 'A' );
2223 end if;
2224
2225 if (l_check_flag = 1)
2226 then
2227 rollup_amounts_rg(
2228 X_Resource_Assignment_Id => x_resource_assignment_id,
2229 X_Budget_Version_Id => x_version_id,
2230 X_Project_Id => x_project_id,
2231 X_Task_Id => TmpActTab(j).task_id,
2232 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
2233 X_Start_Date => TmpActTab(j).start_date,
2234 X_End_Date => TmpActTab(j).end_date,
2235 X_Period_Name => TmpActTab(j).period_name,
2236 X_Quantity => TmpActTab(j).quantity,
2237 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
2238 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
2239 X_Raw_Cost => TmpActTab(j).raw_cost,
2240 X_Burdened_Cost => TmpActTab(j).burdened_cost,
2241 X_Revenue => TmpActTab(j).revenue
2242 );
2243 /* Ends added for 6509313 */
2244
2245 pa_budget_lines_v_pkg.insert_row (
2246 X_Rowid => x_rowid,
2247 X_Resource_Assignment_Id => x_resource_assignment_id,
2248 X_Budget_Version_Id => x_version_id,
2249 X_Project_Id => x_project_id,
2250 X_Task_Id => TmpActTab(j).task_id,
2251 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
2252 X_Description => NULL,
2253 X_Start_Date => TmpActTab(j).start_date,
2254 X_End_Date => TmpActTab(j).end_date,
2255 X_Period_Name => TmpActTab(j).period_name,
2256 X_Quantity => TmpActTab(j).quantity,
2257 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
2258 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
2259 X_Raw_Cost => TmpActTab(j).raw_cost,
2260 X_Burdened_Cost => TmpActTab(j).burdened_cost,
2261 X_Revenue => TmpActTab(j).revenue,
2262 X_Change_Reason_Code => NULL,
2263 X_Last_Update_Date => sysdate,
2264 X_Last_Updated_By => x_created_by,
2265 X_Creation_Date => sysdate,
2266 X_Created_By => x_created_by,
2267 X_Last_Update_Login => x_last_update_login,
2268 X_Attribute_Category => NULL,
2269 X_Attribute1 => NULL,
2270 X_Attribute2 => NULL,
2271 X_Attribute3 => NULL,
2272 X_Attribute4 => NULL,
2273 X_Attribute5 => NULL,
2274 X_Attribute6 => NULL,
2275 X_Attribute7 => NULL,
2276 X_Attribute8 => NULL,
2277 X_Attribute9 => NULL,
2278 X_Attribute10 => NULL,
2279 X_Attribute11 => NULL,
2280 X_Attribute12 => NULL,
2281 X_Attribute13 => NULL,
2282 X_Attribute14 => NULL,
2283 X_Attribute15 => NULL,
2284 X_Calling_Process => 'PR',
2285 X_Pm_Product_Code => NULL,
2286 X_Pm_Budget_Line_Reference => NULL,
2287 X_raw_cost_source => 'A',
2288 X_burdened_cost_source => 'A',
2289 X_quantity_source => 'A',
2290 X_revenue_source => 'A' --,
2291 --X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
2292 );
2293
2294 end if; -- added for bug 6509313
2295 end if;
2296 End Loop;
2297 end if; -- categorized
2298 /* end of part 3 - Bug 4889056*/
2299 /* elsif (x_entry_level_code = 'T') then
2300
2301 -- go through every top level task
2302 for top_task_rec in (select t.task_id
2303 from pa_tasks t
2304 where t.project_id = x_project_id
2305 and t.task_id = t.top_task_id) loop
2306
2307 x_raw_cost:= 0;
2308 x_burdened_cost:= 0;
2309 x_revenue:= 0;
2310 x_quantity := 0;
2311 x_labor_hours:= 0;
2312
2313 if (x_categorization_code = 'N') then
2314
2315 -- lowest level task, uncategorized
2316 x_quantity := 0;
2317 x_raw_cost := 0;
2318 x_burdened_cost := 0;
2319 x_revenue := 0;
2320 x_labor_hours := 0;
2321 x_unit_of_measure := NULL;
2322
2323 pa_accum_api.get_proj_accum_actuals(x_project_id,
2324 top_task_rec.task_id,
2325 NULL,
2326 x_time_phased_type_code,
2327 period_rec.period_name,
2328 period_rec.start_date,
2329 period_rec.end_date,
2330 x_revenue,
2331 x_raw_cost,
2332 x_burdened_cost,
2333 x_quantity,
2334 x_labor_hours,
2335 x_dummy1,
2336 x_dummy2,
2337 x_dummy3,
2338 x_dummy4,
2339 x_dummy5,
2340 x_dummy6,
2341 x_unit_of_measure,
2342 x_err_stage,
2343 x_err_code
2344 );
2345
2346 if (x_err_code <> 0) then
2347 rollback to before_copy_actual;
2348 return;
2349 end if;
2350
2351 -- Fix for Bug # 556131
2352 if x_budget_amount_code = 'C' then
2353 x_revenue := null;
2354
2355 -- Bug# 2107130 Following three if/end if statement are added
2356 if x_cost_quantity_flag = 'N' then
2357 x_labor_hours := null;
2358 x_uncat_unit_of_measure := null;
2359 end if;
2360
2361 if x_raw_cost_flag = 'N' then
2362 x_raw_cost := null;
2363 end if;
2364
2365 if x_burdened_cost_flag = 'N' then
2366 x_burdened_cost := null;
2367 end if;
2368
2369 else
2370 x_raw_cost := null;
2371 x_burdened_cost := null;
2372
2373 -- Bug# 2107130 Following two if/end if statement are added
2374 if x_rev_quantity_flag = 'N' then
2375 x_labor_hours := null;
2376 x_uncat_unit_of_measure := null;
2377 end if;
2378
2379 if x_revenue_flag = 'N' then
2380 x_revenue := null;
2381 end if;
2382
2383 end if;
2384
2385 if ( (nvl(x_labor_hours,0) <> 0) -- Changed for bug# 2107130
2386 or (nvl(x_raw_cost,0) <> 0)
2387 or (nvl(x_burdened_cost,0) <> 0)
2388 or (nvl(x_revenue,0) <> 0)) then
2389
2390 -- ***** Bug # 2021295 - BEGIN *****
2391
2392 PAXBUEBU:COPY ACTUALS DOES NOT PICK UP ACTUAL REVENUE FOR WORK/EVENT BUDGET
2393 Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
2394 from "Positional Parameter Passing" to "Named Parameter Passing".
2395
2396
2397 pa_budget_lines_v_pkg.insert_row (
2398 X_Rowid => x_rowid,
2399 X_Resource_Assignment_Id => x_resource_assignment_id,
2400 X_Budget_Version_Id => x_version_id,
2401 X_Project_Id => x_project_id,
2402 X_Task_Id => top_task_rec.task_id,
2403 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
2404 X_Description => NULL,
2405 X_Start_Date => period_rec.start_date,
2406 X_End_Date => period_rec.end_date,
2407 X_Period_Name => period_rec.period_name,
2408 X_Quantity => x_labor_hours, -- Changed for bug# 2107130
2409 X_Unit_Of_Measure => x_uncat_unit_of_measure,
2410 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
2411 X_Raw_Cost => x_raw_cost,
2412 X_Burdened_Cost => x_burdened_cost,
2413 X_Revenue => x_revenue,
2414 X_Change_Reason_Code => NULL,
2415 X_Last_Update_Date => sysdate,
2416 X_Last_Updated_By => x_created_by,
2417 X_Creation_Date => sysdate,
2418 X_Created_By => x_created_by,
2419 X_Last_Update_Login => x_last_update_login,
2420 X_Attribute_Category => NULL,
2421 X_Attribute1 => NULL,
2422 X_Attribute2 => NULL,
2423 X_Attribute3 => NULL,
2424 X_Attribute4 => NULL,
2425 X_Attribute5 => NULL,
2426 X_Attribute6 => NULL,
2427 X_Attribute7 => NULL,
2428 X_Attribute8 => NULL,
2429 X_Attribute9 => NULL,
2430 X_Attribute10 => NULL,
2431 X_Attribute11 => NULL,
2432 X_Attribute12 => NULL,
2433 X_Attribute13 => NULL,
2434 X_Attribute14 => NULL,
2435 X_Attribute15 => NULL,
2436 X_Calling_Process => 'PR',
2437 X_Pm_Product_Code => NULL,
2438 X_Pm_Budget_Line_Reference => NULL,
2439 X_raw_cost_source => 'A',
2440 X_burdened_cost_source => 'A',
2441 X_quantity_source => 'A',
2442 X_revenue_source => 'A');
2443 -- Bug Fix: 4569365. Removed MRC code.
2444 --,X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
2445 -- );
2446 -- ***** Bug # 2021295 - END *****
2447
2448 if (x_err_code <> 0) then
2449 rollback to before_copy_actual;
2450 return;
2451 end if;
2452
2453 end if;
2454
2455 else
2456
2457 -- top level task, categorized
2458 for res_rec in (select m.resource_list_member_id,
2459 m.resource_id,
2460 m.track_as_labor_flag
2461 from pa_resource_list_members m
2462 where m.resource_list_id =
2463 x_resource_list_id
2464 and nvl(m.migration_code, 'M') = 'M'
2465 and not exists
2466 (select 1
2467 from pa_resource_list_members m1
2468 where m1.parent_member_id =
2469 m.resource_list_member_id)
2470 ) loop
2471
2472 x_quantity:= 0;
2473 x_raw_cost:= 0;
2474 x_burdened_cost:= 0;
2475 x_revenue:= 0;
2476 x_labor_hours:= 0;
2477 x_unit_of_measure := NULL;
2478
2479 x_err_stage := 'process period/task/resource <'
2480 || period_rec.period_name
2481 || '><' || to_char(top_task_rec.task_id)
2482 || '><'
2483 || to_char(res_rec.resource_list_member_id)
2484 || '>';
2485
2486 -- Added for bug 3896747
2487 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
2488 fnd_file.put_line(1,x_err_stage);
2489 End if;
2490 pa_accum_api.get_proj_accum_actuals(x_project_id,
2491 top_task_rec.task_id,
2492 res_rec.resource_list_member_id,
2493 x_time_phased_type_code,
2494 period_rec.period_name,
2495 period_rec.start_date,
2496 period_rec.end_date,
2497 x_revenue,
2498 x_raw_cost,
2499 x_burdened_cost,
2500 x_quantity,
2501 x_labor_hours,
2502 x_dummy1,
2503 x_dummy2,
2504 x_dummy3,
2505 x_dummy4,
2506 x_dummy5,
2507 x_dummy6,
2508 x_unit_of_measure,
2509 x_err_stage,
2510 x_err_code
2511 );
2512
2513 if (x_err_code <> 0) then
2514 rollback to before_copy_actual;
2515 return;
2516 end if;
2517
2518 -- Fix for Bug # 556131
2519 if x_budget_amount_code = 'C' then
2520 x_revenue := null;
2521
2522 -- Bug# 2107130 Following three if/end if statement are added
2523 if x_cost_quantity_flag = 'N' then
2524 x_quantity := null;
2525 x_unit_of_measure := null;
2526 end if;
2527
2528 if x_raw_cost_flag = 'N' then
2529 x_raw_cost := null;
2530 end if;
2531
2532 if x_burdened_cost_flag = 'N' then
2533 x_burdened_cost := null;
2534 end if;
2535
2536 else
2537 x_raw_cost := null;
2538 x_burdened_cost := null;
2539
2540 Bug# 2107130 Following two if/end if statement are added
2541 if x_rev_quantity_flag = 'N' then
2542 x_quantity := null;
2543 x_unit_of_measure := null;
2544 end if;
2545
2546 if x_revenue_flag = 'N' then
2547 x_revenue := null;
2548 end if;
2549
2550 end if;
2551
2552 if ( (nvl(x_quantity,0) <> 0)
2553 or (nvl(x_raw_cost,0) <> 0)
2554 or (nvl(x_burdened_cost,0) <> 0)
2555 or (nvl(x_revenue,0) <> 0)) then
2556
2557 -- ***** Bug # 2021295 - BEGIN *****
2558
2559 PAXBUEBU:COPY ACTUALS DOES NOT PICK UP ACTUAL REVENUE FOR WORK/EVENT BUDGET
2560 Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
2561 from "Positional Parameter Passing" to "Named Parameter Passing".
2562
2563
2564 pa_budget_lines_v_pkg.insert_row (
2565 X_Rowid => x_rowid,
2566 X_Resource_Assignment_Id => x_resource_assignment_id,
2567 X_Budget_Version_Id => x_version_id,
2568 X_Project_Id => x_project_id,
2569 X_Task_Id => top_task_rec.task_id,
2570 X_Resource_List_Member_Id => res_rec.resource_list_member_id,
2571 X_Description => NULL,
2572 X_Start_Date => period_rec.start_date,
2573 X_End_Date => period_rec.end_date,
2574 X_Period_Name => period_rec.period_name,
2575 X_Quantity => x_quantity,
2576 X_Unit_Of_Measure => x_unit_of_measure,
2577 X_Track_As_Labor_Flag => res_rec.track_as_labor_flag,
2578 X_Raw_Cost => x_raw_cost,
2579 X_Burdened_Cost => x_burdened_cost,
2580 X_Revenue => x_revenue,
2581 X_Change_Reason_Code => NULL,
2582 X_Last_Update_Date => sysdate,
2583 X_Last_Updated_By => x_created_by,
2584 X_Creation_Date => sysdate,
2585 X_Created_By => x_created_by,
2586 X_Last_Update_Login => x_last_update_login,
2587 X_Attribute_Category => NULL,
2588 X_Attribute1 => NULL,
2589 X_Attribute2 => NULL,
2590 X_Attribute3 => NULL,
2591 X_Attribute4 => NULL,
2592 X_Attribute5 => NULL,
2593 X_Attribute6 => NULL,
2594 X_Attribute7 => NULL,
2595 X_Attribute8 => NULL,
2596 X_Attribute9 => NULL,
2597 X_Attribute10 => NULL,
2598 X_Attribute11 => NULL,
2599 X_Attribute12 => NULL,
2600 X_Attribute13 => NULL,
2601 X_Attribute14 => NULL,
2602 X_Attribute15 => NULL,
2603 X_Calling_Process => 'PR',
2604 X_Pm_Product_Code => NULL,
2605 X_Pm_Budget_Line_Reference => NULL,
2606 X_raw_cost_source => 'A',
2607 X_burdened_cost_source => 'A',
2608 X_quantity_source => 'A',
2609 X_revenue_source => 'A');
2610 -- Bug Fix: 4569365. Removed MRC code.
2611 -- X_mrc_flag => 'Y' FPB2: Added x_mrc_flag for MRC changes
2612 -- );
2613 ***** Bug # 2021295 - END *****
2614
2615 if (x_err_code <> 0) then
2616 rollback to before_copy_actual;
2617 return;
2618 end if;
2619
2620 end if;
2621
2622 end loop; -- resource
2623
2624 end if; -- categorized
2625
2626 end loop; -- top task
2627 */ -- End of commented code for Part 3 4889056
2628
2629 else -- 'L' or 'M'
2630 -- go through every lowest level task
2631 /* Begin of part 4 - Bug 4889056 */
2632 if (x_categorization_code = 'N') then
2633 -- lowest level task, uncategorized
2634 if (x_time_phased_type_code = 'P') then
2635 select p.period_name,
2636 p.start_date,
2637 p.end_date,
2638 t.task_id
2639 bulk collect into
2640 l_period_name,
2641 l_start_date,
2642 l_end_date,
2643 l_task_id
2644 from pa_periods p,
2645 pa_tasks t
2646 where t.project_id = x_project_id
2647 and not exists
2648 (select 1
2649 from pa_tasks t1
2650 where t1.parent_task_id = t.task_id)
2651 and p.start_date between x_start_period_start_date
2652 and x_end_period_end_date;
2653
2654 x_err_stage := 'PA: Period Before Calling the For Loop';
2655 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
2656 x_err_stage := 'PA: Period Inside the For Loop';
2657 SELECT
2658 sum(tot_revenue),
2659 sum(tot_raw_cost),
2660 sum(tot_burdened_cost),
2661 sum(tot_quantity),
2662 sum(tot_labor_hours),
2663 sum(tot_billable_raw_cost),
2664 sum(tot_billable_burdened_cost),
2665 sum(tot_billable_quantity),
2666 sum(tot_billable_labor_hours),
2667 sum(tot_cmt_raw_cost),
2668 sum(tot_cmt_burdened_cost),
2669 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
2670 INTO
2671 x_revenue,
2672 x_raw_cost,
2673 x_burdened_cost,
2674 x_quantity,
2675 x_labor_hours,
2676 x_billable_raw_cost,
2677 x_billable_burdened_cost,
2678 x_billable_quantity,
2679 x_billable_labor_hours,
2680 x_cmt_raw_cost,
2681 x_cmt_burdened_cost,
2682 x_unit_of_measure
2683 FROM
2684 pa_txn_accum pta
2685 WHERE
2686 pta.project_id = x_project_id
2687 AND pta.task_id IN
2688 (SELECT
2689 task_id
2690 FROM
2691 pa_tasks
2692 CONNECT BY PRIOR task_id = parent_task_id
2693 START WITH task_id = l_task_id(i)
2694 )
2695 AND EXISTS
2696 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
2697 WHERE
2698 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
2699 )
2700 AND pta.pa_period = l_period_name(i) ;
2701
2702 x_err_stage := 'PA: Period Before inserting into tmp table';
2703 TmpActTab(i).period_name := l_period_name(i);
2704 TmpActTab(i).start_date := l_start_date(i);
2705 TmpActTab(i).end_date := l_end_date(i);
2706 TmpActTab(i).task_id := l_task_id(i);
2707 TmpActTab(i).REVENUE := x_revenue;
2708 TmpActTab(i).RAW_COST := x_raw_cost;
2709 TmpActTab(i).BURDENED_COST := x_burdened_cost;
2710 TmpActTab(i).QUANTITY := x_quantity;
2711 TmpActTab(i).LABOR_HOURS := x_labor_hours;
2712 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
2713 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
2714 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
2715 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
2716 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
2717 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
2718 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
2719 x_err_stage := 'PA: Period After inserting into tmp table';
2720 END LOOP;
2721 else -- x_time_phased_type_code = 'G'
2722
2723 select p.period_name,
2724 p.start_date,
2725 p.end_date,
2726 t.task_id
2727 bulk collect into
2728 l_period_name,
2729 l_start_date,
2730 l_end_date,
2731 l_task_id
2732 from gl_period_statuses p,
2733 pa_implementations i,
2734 pa_tasks t
2735 where t.project_id = x_project_id
2736 and not exists
2737 (select 1
2738 from pa_tasks t1
2739 where t1.parent_task_id = t.task_id)
2740 and p.application_id = pa_period_process_pkg.application_id
2741 and p.set_of_books_id = i.set_of_books_id
2742 and p.adjustment_period_flag = 'N'
2743 and p.start_date between x_start_period_start_date
2744 and x_end_period_end_date;
2745
2746 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
2747 SELECT
2748 sum(tot_revenue),
2749 sum(tot_raw_cost),
2750 sum(tot_burdened_cost),
2751 sum(tot_quantity),
2752 sum(tot_labor_hours),
2753 sum(tot_billable_raw_cost),
2754 sum(tot_billable_burdened_cost),
2755 sum(tot_billable_quantity),
2756 sum(tot_billable_labor_hours),
2757 sum(tot_cmt_raw_cost),
2758 sum(tot_cmt_burdened_cost),
2759 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
2760 INTO
2761 x_revenue,
2762 x_raw_cost,
2763 x_burdened_cost,
2764 x_quantity,
2765 x_labor_hours,
2766 x_billable_raw_cost,
2767 x_billable_burdened_cost,
2768 x_billable_quantity,
2769 x_billable_labor_hours,
2770 x_cmt_raw_cost,
2771 x_cmt_burdened_cost,
2772 x_unit_of_measure
2773 FROM
2774 pa_txn_accum pta
2775 WHERE
2776 pta.project_id = x_project_id
2777 AND pta.task_id IN
2778 (SELECT
2779 task_id
2780 FROM
2781 pa_tasks
2782 CONNECT BY PRIOR task_id = parent_task_id
2783 START WITH task_id = l_task_id(i)
2784 )
2785 AND EXISTS
2786 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
2787 WHERE
2788 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
2789 )
2790 AND pta.gl_period = l_period_name(i);
2791
2792 TmpActTab(i).period_name := l_period_name(i);
2793 TmpActTab(i).start_date := l_start_date(i);
2794 TmpActTab(i).end_date := l_end_date(i);
2795 TmpActTab(i).task_id := l_task_id(i);
2796 /* Commented for Bug 6933201- This is uncategorized block and below field have no significance.
2797 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
2798 TmpActTab(i).resource_id := l_resource_id(i);
2799 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
2800 */
2801 TmpActTab(i).REVENUE := x_revenue;
2802 TmpActTab(i).RAW_COST := x_raw_cost;
2803 TmpActTab(i).BURDENED_COST := x_burdened_cost;
2804 TmpActTab(i).QUANTITY := x_quantity;
2805 TmpActTab(i).LABOR_HOURS := x_labor_hours;
2806 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
2807 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
2808 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
2809 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
2810 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
2811 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
2812 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
2813 END LOOP;
2814 end if;
2815
2816 For j in TmpActTab.FIRST..TmpActTab.LAST LOOP
2817 if x_budget_amount_code = 'C' then
2818 TmpActTab(j).revenue := null;
2819
2820 if x_cost_quantity_flag = 'N' then
2821 TmpActTab(j).labor_hours := null;
2822 x_uncat_unit_of_measure := null;
2823 end if;
2824
2825 if x_raw_cost_flag = 'N' then
2826 TmpActTab(j).raw_cost := null;
2827 end if;
2828
2829 if x_burdened_cost_flag = 'N' then
2830 TmpActTab(j).burdened_cost := null;
2831 end if;
2832
2833 else
2834 TmpActTab(j).raw_cost := null;
2835 TmpActTab(j).burdened_cost := null;
2836
2837 if x_rev_quantity_flag = 'N' then
2838 TmpActTab(j).labor_hours := null;
2839 x_uncat_unit_of_measure := null;
2840 end if;
2841
2842 if x_revenue_flag = 'N' then
2843 TmpActTab(j).revenue := null;
2844 end if;
2845
2846 end if;
2847
2848 if ( (nvl(TmpActTab(j).labor_hours,0) <> 0)
2849 or (nvl(TmpActTab(j).raw_cost,0) <> 0)
2850 or (nvl(TmpActTab(j).burdened_cost,0) <> 0)
2851 or (nvl(TmpActTab(j).revenue,0) <> 0)) then
2852
2853 /* Added for bug 6509313 */
2854
2855 BEGIN
2856 l_check_flag:=0;
2857 select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
2858 , (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
2859 , (NVL(burdened_cost,0) + nvl(TmpActTab(j).revenue, 0))
2860 , (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
2861 , pbl.resource_assignment_id
2862 , pbl.rowid
2863 into x_new_quantity,
2864 x_new_raw_cost,
2865 x_new_burdened_cost,
2866 x_new_revenue,
2867 x_new_assignment_id,
2868 x_new_row_id
2869 from pa_budget_lines pbl
2870 where pbl.resource_assignment_id in (
2871 select distinct pbl1.resource_assignment_id
2872 from pa_budget_lines pbl1,
2873 pa_resource_assignments pra,
2874 pa_resource_list_members p1,
2875 pa_resource_list_members p2
2876 where pra.resource_list_member_id = p2.resource_list_member_id
2877 and p1.parent_member_id = p2.resource_list_member_id
2878 and p1.resource_list_member_id = x_uncat_res_list_member_id
2879 and pbl1.resource_assignment_id = pra.resource_assignment_id
2880 and pra.budget_version_id = x_version_id
2881 and pra.task_id = TmpActTab(j).task_id
2882 and pbl1.period_name = TmpActTab(j).period_name
2883 )
2884 and pbl.budget_version_id = x_version_id
2885 and pbl.period_name = TmpActTab(j).period_name ;
2886 EXCEPTION
2887 WHEN no_data_found THEN
2888 l_check_flag :=1;
2889 WHEN OTHERS THEN
2890 l_check_flag :=2;
2891 END;
2892
2893 if l_check_flag = 0 then
2894
2895 rollup_amounts_rg(
2896 X_Resource_Assignment_Id => x_resource_assignment_id,
2897 X_Budget_Version_Id => x_version_id,
2898 X_Project_Id => x_project_id,
2899 X_Task_Id => TmpActTab(j).task_id,
2900 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
2901 X_Start_Date => TmpActTab(j).start_date,
2902 X_End_Date => TmpActTab(j).end_date,
2903 X_Period_Name => TmpActTab(j).period_name,
2904 X_Quantity => TmpActTab(j).labor_hours,
2905 X_Unit_Of_Measure => x_uncat_unit_of_measure,
2906 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
2907 X_Raw_Cost => TmpActTab(j).raw_cost,
2908 X_Burdened_Cost => TmpActTab(j).burdened_cost,
2909 X_Revenue => TmpActTab(j).revenue
2910 );
2911
2912 pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
2913 X_Resource_Assignment_Id => x_new_assignment_id,
2914 X_Budget_Version_Id => x_version_id,
2915 X_Project_Id => x_project_id,
2916 X_Task_Id => TmpActTab(j).task_id,
2917 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
2918 X_Resource_Id => NULL,
2919 X_Resource_Id_Old => NULL,
2920 X_Description => NULL,
2921 X_Start_Date => TmpActTab(j).start_date,
2922 X_End_Date => TmpActTab(j).end_date,
2923 X_Period_Name => TmpActTab(j).period_name,
2924 X_Quantity => x_new_quantity,
2925 X_Quantity_Old => TmpActTab(j).labor_hours,
2926 X_Unit_Of_Measure => x_uncat_unit_of_measure,
2927 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
2928 X_Raw_Cost => x_new_raw_cost,
2929 X_Raw_Cost_Old => TmpActTab(j).raw_cost,
2930 X_Burdened_Cost => x_new_burdened_cost,
2931 X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
2932 X_Revenue => x_new_revenue,
2933 X_Revenue_Old => TmpActTab(j).revenue,
2934 X_Change_Reason_Code => NULL,
2935 X_Last_Update_Date => sysdate,
2936 X_Last_Updated_By => x_created_by,
2937 X_Last_Update_Login => x_last_update_login,
2938 X_Attribute_Category => NULL,
2939 X_Attribute1 => NULL,
2940 X_Attribute2 => NULL,
2941 X_Attribute3 => NULL,
2942 X_Attribute4 => NULL,
2943 X_Attribute5 => NULL,
2944 X_Attribute6 => NULL,
2945 X_Attribute7 => NULL,
2946 X_Attribute8 => NULL,
2947 X_Attribute9 => NULL,
2948 X_Attribute10 => NULL,
2949 X_Attribute11 => NULL,
2950 X_Attribute12 => NULL,
2951 X_Attribute13 => NULL,
2952 X_Attribute14 => NULL,
2953 X_Attribute15 => NULL,
2954 -- X_mrc_flag => 'Y', -- Removed MRC code.
2955 X_Calling_Process => 'PR',
2956 X_raw_cost_source => 'A',
2957 X_burdened_cost_source => 'A',
2958 X_quantity_source => 'A',
2959 X_revenue_source => 'A' );
2960 end if;
2961
2962 if (l_check_flag = 1) THEN
2963
2964 rollup_amounts_rg(
2965 X_Resource_Assignment_Id => x_resource_assignment_id,
2966 X_Budget_Version_Id => x_version_id,
2967 X_Project_Id => x_project_id,
2968 X_Task_Id => TmpActTab(j).task_id,
2969 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
2970 X_Start_Date => TmpActTab(j).start_date,
2971 X_End_Date => TmpActTab(j).end_date,
2972 X_Period_Name => TmpActTab(j).period_name,
2973 X_Quantity => TmpActTab(j).labor_hours,
2974 X_Unit_Of_Measure => x_uncat_unit_of_measure,
2975 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
2976 X_Raw_Cost => TmpActTab(j).raw_cost,
2977 X_Burdened_Cost => TmpActTab(j).burdened_cost,
2978 X_Revenue => TmpActTab(j).revenue
2979 );
2980 /* Ends added for 6509313 */
2981
2982 pa_budget_lines_v_pkg.insert_row (
2983 X_Rowid => x_rowid,
2984 X_Resource_Assignment_Id => x_resource_assignment_id,
2985 X_Budget_Version_Id => x_version_id,
2986 X_Project_Id => x_project_id,
2987 X_Task_Id => TmpActTab(j).task_id,
2988 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
2989 X_Description => NULL,
2990 X_Start_Date => TmpActTab(j).start_date,
2991 X_End_Date => TmpActTab(j).end_date,
2992 X_Period_Name => TmpActTab(j).period_name,
2993 X_Quantity => TmpActTab(j).labor_hours,
2994 X_Unit_Of_Measure => x_uncat_unit_of_measure,
2995 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
2996 X_Raw_Cost => TmpActTab(j).raw_cost,
2997 X_Burdened_Cost => TmpActTab(j).burdened_cost,
2998 X_Revenue => TmpActTab(j).revenue,
2999 X_Change_Reason_Code => NULL,
3000 X_Last_Update_Date => sysdate,
3001 X_Last_Updated_By => x_created_by,
3002 X_Creation_Date => sysdate,
3003 X_Created_By => x_created_by,
3004 X_Last_Update_Login => x_last_update_login,
3005 X_Attribute_Category => NULL,
3006 X_Attribute1 => NULL,
3007 X_Attribute2 => NULL,
3008 X_Attribute3 => NULL,
3009 X_Attribute4 => NULL,
3010 X_Attribute5 => NULL,
3011 X_Attribute6 => NULL,
3012 X_Attribute7 => NULL,
3013 X_Attribute8 => NULL,
3014 X_Attribute9 => NULL,
3015 X_Attribute10 => NULL,
3016 X_Attribute11 => NULL,
3017 X_Attribute12 => NULL,
3018 X_Attribute13 => NULL,
3019 X_Attribute14 => NULL,
3020 X_Attribute15 => NULL,
3021 X_Calling_Process => 'PR',
3022 X_Pm_Product_Code => NULL,
3023 X_Pm_Budget_Line_Reference => NULL,
3024 X_raw_cost_source => 'A',
3025 X_burdened_cost_source => 'A',
3026 X_quantity_source => 'A',
3027 X_revenue_source => 'A' --,
3028 --X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
3029 );
3030 end if;-- added for bug 6509313
3031 end if;
3032 End Loop;
3033 else
3034
3035 -- lowest level task, categorized
3036 x_err_stage := 'lowest level task, categorized';
3037 if (x_time_phased_type_code = 'P') then
3038 select p.period_name,
3039 p.start_date,
3040 p.end_date,
3041 t.task_id,
3042 m.resource_list_member_id,
3043 m.resource_id,
3044 m.track_as_labor_flag
3045 bulk collect into
3046 l_period_name,
3047 l_start_date,
3048 l_end_date,
3049 l_task_id,
3050 l_resource_list_member_id,
3051 l_resource_id,
3052 l_track_as_labor_flag
3053 from pa_periods p,
3054 pa_tasks t,
3055 pa_resource_list_members m
3056 where m.resource_list_id = x_resource_list_id
3057 and nvl(m.migration_code, 'M') = 'M'
3058 and not exists
3059 (select 1
3060 from pa_resource_list_members m1
3061 where m1.parent_member_id =
3062 m.resource_list_member_id)
3063 and t.project_id = x_project_id
3064 and not exists
3065 (select 1
3066 from pa_tasks t1
3067 where t1.parent_task_id = t.task_id)
3068 and p.start_date between x_start_period_start_date
3069 and x_end_period_end_date;
3070
3071 x_err_stage := 'lowest level task, categorized: Before For Loop';
3072 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
3073 x_err_stage := 'lowest level task, categorized: Inside For Loop';
3074 SELECT
3075 sum(tot_revenue),
3076 sum(tot_raw_cost),
3077 sum(tot_burdened_cost),
3078 sum(tot_quantity),
3079 sum(tot_labor_hours),
3080 sum(tot_billable_raw_cost),
3081 sum(tot_billable_burdened_cost),
3082 sum(tot_billable_quantity),
3083 sum(tot_billable_labor_hours),
3084 sum(tot_cmt_raw_cost),
3085 sum(tot_cmt_burdened_cost),
3086 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
3087 INTO
3088 x_revenue,
3089 x_raw_cost,
3090 x_burdened_cost,
3091 x_quantity,
3092 x_labor_hours,
3093 x_billable_raw_cost,
3094 x_billable_burdened_cost,
3095 x_billable_quantity,
3096 x_billable_labor_hours,
3097 x_cmt_raw_cost,
3098 x_cmt_burdened_cost,
3099 x_unit_of_measure
3100 FROM
3101 pa_txn_accum pta
3102 WHERE
3103 pta.project_id = x_project_id
3104 AND pta.task_id IN
3105 (SELECT
3106 task_id
3107 FROM
3108 pa_tasks
3109 CONNECT BY PRIOR task_id = parent_task_id
3110 START WITH task_id = l_task_id(i)
3111 )
3112 AND EXISTS
3113 ( SELECT 'Yes'
3114 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
3115 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
3116 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
3117 ( -- Fetch both 2nd level and group level resource list member
3118 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
3119 FROM PA_RESOURCE_LIST_MEMBERS PRLM
3120 WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
3121 or
3122 PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
3123 )
3124 )
3125 AND EXISTS
3126 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
3127 WHERE
3128 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
3129 )
3130 AND pta.pa_period = l_period_name(i) ;
3131
3132 x_err_stage := 'lowest level task, categorized: Before inserting in Tmp table '||i;
3133 TmpActTab(i).period_name := l_period_name(i);
3134 TmpActTab(i).start_date := l_start_date(i);
3135 TmpActTab(i).end_date := l_end_date(i);
3136 TmpActTab(i).task_id := l_task_id(i);
3137 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
3138 TmpActTab(i).resource_id := l_resource_id(i);
3139 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
3140 TmpActTab(i).REVENUE := x_revenue;
3141 TmpActTab(i).RAW_COST := x_raw_cost;
3142 TmpActTab(i).BURDENED_COST := x_burdened_cost;
3143 TmpActTab(i).QUANTITY := x_quantity;
3144 TmpActTab(i).LABOR_HOURS := x_labor_hours;
3145 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
3146 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
3147 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
3148 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
3149 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
3150 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
3151 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
3152 x_err_stage := 'lowest level task, categorized: After inserting in Tmp table';
3153 END LOOP;
3154 else -- x_time_phased_type_code = 'G'
3155
3156 select p.period_name,
3157 p.start_date,
3158 p.end_date,
3159 t.task_id,
3160 m.resource_list_member_id,
3161 m.resource_id,
3162 m.track_as_labor_flag
3163 bulk collect into
3164 l_period_name,
3165 l_start_date,
3166 l_end_date,
3167 l_task_id,
3168 l_resource_list_member_id,
3169 l_resource_id,
3170 l_track_as_labor_flag
3171 from gl_period_statuses p,
3172 pa_implementations i,
3173 pa_tasks t,
3174 pa_resource_list_members m
3175 where m.resource_list_id = x_resource_list_id
3176 and not exists
3177 (select 1
3178 from pa_resource_list_members m1
3179 where m1.parent_member_id =
3180 m.resource_list_member_id)
3181 and t.project_id = x_project_id
3182 and not exists
3183 (select 1
3184 from pa_tasks t1
3185 where t1.parent_task_id = t.task_id)
3186 and p.application_id = pa_period_process_pkg.application_id
3187 and p.set_of_books_id = i.set_of_books_id
3188 and p.adjustment_period_flag = 'N'
3189 and p.start_date between x_start_period_start_date
3190 and x_end_period_end_date;
3191
3192 FOR i in l_period_name.FIRST..l_period_name.LAST LOOP
3193 SELECT
3194 sum(tot_revenue),
3195 sum(tot_raw_cost),
3196 sum(tot_burdened_cost),
3197 sum(tot_quantity),
3198 sum(tot_labor_hours),
3199 sum(tot_billable_raw_cost),
3200 sum(tot_billable_burdened_cost),
3201 sum(tot_billable_quantity),
3202 sum(tot_billable_labor_hours),
3203 sum(tot_cmt_raw_cost),
3204 sum(tot_cmt_burdened_cost),
3205 Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
3206 INTO
3207 x_revenue,
3208 x_raw_cost,
3209 x_burdened_cost,
3210 x_quantity,
3211 x_labor_hours,
3212 x_billable_raw_cost,
3213 x_billable_burdened_cost,
3214 x_billable_quantity,
3215 x_billable_labor_hours,
3216 x_cmt_raw_cost,
3217 x_cmt_burdened_cost,
3218 x_unit_of_measure
3219 FROM
3220 pa_txn_accum pta
3221 WHERE
3222 pta.project_id = x_project_id
3223 AND pta.task_id IN
3224 (SELECT
3225 task_id
3226 FROM
3227 pa_tasks
3228 CONNECT BY PRIOR task_id = parent_task_id
3229 START WITH task_id = l_task_id(i)
3230 )
3231 AND EXISTS
3232 ( SELECT 'Yes'
3233 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
3234 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
3235 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
3236 ( -- Fetch both 2nd level and group level resource list member
3237 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
3238 FROM PA_RESOURCE_LIST_MEMBERS PRLM
3239 WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
3240 or
3241 PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
3242 )
3243 )
3244 AND EXISTS
3245 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
3246 WHERE
3247 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
3248 )
3249 AND pta.gl_period = l_period_name(i);
3250
3251 TmpActTab(i).period_name := l_period_name(i);
3252 TmpActTab(i).start_date := l_start_date(i);
3253 TmpActTab(i).end_date := l_start_date(i);
3254 TmpActTab(i).task_id := l_task_id(i);
3255 TmpActTab(i).resource_list_member_id := l_resource_list_member_id(i);
3256 TmpActTab(i).resource_id := l_resource_id(i);
3257 TmpActTab(i).track_as_labor_flag := l_track_as_labor_flag(i);
3258 TmpActTab(i).REVENUE := x_revenue;
3259 TmpActTab(i).RAW_COST := x_raw_cost;
3260 TmpActTab(i).BURDENED_COST := x_burdened_cost;
3261 TmpActTab(i).QUANTITY := x_quantity;
3262 TmpActTab(i).LABOR_HOURS := x_labor_hours;
3263 TmpActTab(i).BILLABLE_RAW_COST := x_billable_raw_cost;
3264 TmpActTab(i).BILLABLE_BURDENED_COST := x_billable_burdened_cost;
3265 TmpActTab(i).BILLABLE_QUANTITY := x_billable_quantity;
3266 TmpActTab(i).BILLABLE_LABOR_HOURS := x_billable_labor_hours;
3267 TmpActTab(i).CMT_RAW_COST := x_cmt_raw_cost;
3268 TmpActTab(i).CMT_BURDENED_COST := x_cmt_burdened_cost;
3269 TmpActTab(i).UNIT_OF_MEASURE := x_unit_of_measure;
3270 END LOOP;
3271 end if;
3272
3273 For j in TmpActTab.FIRST..TmpActTab.LAST LOOP
3274 if x_budget_amount_code = 'C' then
3275 TmpActTab(j).revenue:= null;
3276
3277 /* Bug# 2107130 Following three if/end if statement are added */
3278 if x_cost_quantity_flag = 'N' then
3279 TmpActTab(j).quantity := null;
3280 TmpActTab(j).unit_of_measure := null;
3281 end if;
3282
3283 if x_raw_cost_flag = 'N' then
3284 TmpActTab(j).raw_cost := null;
3285 end if;
3286
3287 if x_burdened_cost_flag = 'N' then
3288 TmpActTab(j).burdened_cost := null;
3289 end if;
3290
3291 else
3292 TmpActTab(j).raw_cost := null;
3293 TmpActTab(j).burdened_cost := null;
3294
3295 /* Bug# 2107130 Following two if/end if statement are added */
3296 if x_rev_quantity_flag = 'N' then
3297 TmpActTab(j).quantity := null;
3298 TmpActTab(j).unit_of_measure := null;
3299 end if;
3300
3301 if x_revenue_flag = 'N' then
3302 TmpActTab(j).revenue := null;
3303 end if;
3304
3305 end if;
3306
3307 if ( (nvl(TmpActTab(j).quantity,0) <> 0)
3308 or (nvl(TmpActTab(j).raw_cost,0) <> 0)
3309 or (nvl(TmpActTab(j).burdened_cost,0) <> 0)
3310 or (nvl(TmpActTab(j).revenue,0) <> 0)) then
3311
3312 /* Added for bug 6509313 */
3313
3314 BEGIN
3315 l_check_flag :=0;
3316 select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
3317 , (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
3318 , (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
3319 , (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
3320 , pbl.resource_assignment_id
3321 , pbl.rowid
3322 into x_new_quantity,
3323 x_new_raw_cost,
3324 x_new_burdened_cost,
3325 x_new_revenue,
3326 x_new_assignment_id,
3327 x_new_row_id
3328 from pa_budget_lines pbl
3329 where pbl.resource_assignment_id in (
3330 select distinct pbl1.resource_assignment_id
3331 from pa_budget_lines pbl1,
3332 pa_resource_assignments pra,
3333 pa_resource_list_members p1,
3334 pa_resource_list_members p2
3335 where pra.resource_list_member_id = p2.resource_list_member_id
3336 and p1.parent_member_id = p2.resource_list_member_id
3337 and p1.resource_list_member_id = TmpActTab(j).resource_list_member_id
3338 and pbl1.resource_assignment_id = pra.resource_assignment_id
3339 and pra.budget_version_id = x_version_id
3340 and pra.task_id = TmpActTab(j).task_id
3341 and pbl1.period_name = TmpActTab(j).period_name
3342 )
3343 and pbl.budget_version_id = x_version_id
3344 and pbl.period_name = TmpActTab(j).period_name ;
3345 EXCEPTION
3346 WHEN no_data_found THEN
3347 l_check_flag:=1;
3348 WHEN OTHERS THEN
3349 l_check_flag:=2;
3350 END;
3351
3352 if l_check_flag = 0 then
3353
3354
3355 rollup_amounts_rg(
3356 X_Resource_Assignment_Id => x_resource_assignment_id,
3357 X_Budget_Version_Id => x_version_id,
3358 X_Project_Id => x_project_id,
3359 X_Task_Id => TmpActTab(j).task_id,
3360 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
3361 X_Start_Date => TmpActTab(j).start_date,
3362 X_End_Date => TmpActTab(j).end_date,
3363 X_Period_Name => TmpActTab(j).period_name,
3364 X_Quantity => TmpActTab(j).quantity,
3365 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
3366 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
3367 X_Raw_Cost => TmpActTab(j).raw_cost,
3368 X_Burdened_Cost => TmpActTab(j).burdened_cost,
3369 X_Revenue => TmpActTab(j).revenue
3370 );
3371
3372
3373 pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
3374 X_Resource_Assignment_Id => x_new_assignment_id,
3375 X_Budget_Version_Id => x_version_id,
3376 X_Project_Id => x_project_id,
3377 X_Task_Id => TmpActTab(j).task_id,
3378 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
3379 X_Resource_Id => NULL,
3380 X_Resource_Id_Old => NULL,
3381 X_Description => NULL,
3382 X_Start_Date => TmpActTab(j).start_date,
3383 X_End_Date => TmpActTab(j).end_date,
3384 X_Period_Name => TmpActTab(j).period_name,
3385 X_Quantity => x_new_quantity,
3386 X_Quantity_Old => TmpActTab(j).quantity,
3387 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
3388 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
3389 X_Raw_Cost => x_new_raw_cost,
3390 X_Raw_Cost_Old => TmpActTab(j).raw_cost,
3391 X_Burdened_Cost => x_new_burdened_cost,
3392 X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
3393 X_Revenue => x_new_revenue,
3394 X_Revenue_Old => TmpActTab(j).revenue,
3395 X_Change_Reason_Code => NULL,
3396 X_Last_Update_Date => sysdate,
3397 X_Last_Updated_By => x_created_by,
3398 X_Last_Update_Login => x_last_update_login,
3399 X_Attribute_Category => NULL,
3400 X_Attribute1 => NULL,
3401 X_Attribute2 => NULL,
3402 X_Attribute3 => NULL,
3403 X_Attribute4 => NULL,
3404 X_Attribute5 => NULL,
3405 X_Attribute6 => NULL,
3406 X_Attribute7 => NULL,
3407 X_Attribute8 => NULL,
3408 X_Attribute9 => NULL,
3409 X_Attribute10 => NULL,
3410 X_Attribute11 => NULL,
3411 X_Attribute12 => NULL,
3412 X_Attribute13 => NULL,
3413 X_Attribute14 => NULL,
3414 X_Attribute15 => NULL,
3415 -- X_mrc_flag => 'Y', -- Removed MRC code.
3416 X_Calling_Process => 'PR',
3417 X_raw_cost_source => 'A',
3418 X_burdened_cost_source => 'A',
3419 X_quantity_source => 'A',
3420 X_revenue_source => 'A' );
3421
3422 end if;
3423
3424 if (l_check_flag = 1)
3425 THEN
3426 rollup_amounts_rg(
3427 X_Resource_Assignment_Id => x_resource_assignment_id,
3428 X_Budget_Version_Id => x_version_id,
3429 X_Project_Id => x_project_id,
3430 X_Task_Id => TmpActTab(j).task_id,
3431 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
3432 X_Start_Date => TmpActTab(j).start_date,
3433 X_End_Date => TmpActTab(j).end_date,
3434 X_Period_Name => TmpActTab(j).period_name,
3435 X_Quantity => TmpActTab(j).quantity,
3436 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
3437 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
3438 X_Raw_Cost => TmpActTab(j).raw_cost,
3439 X_Burdened_Cost => TmpActTab(j).burdened_cost,
3440 X_Revenue => TmpActTab(j).revenue
3441 );
3442 /* Ends added for 6509313 */
3443
3444 pa_budget_lines_v_pkg.insert_row (
3445 X_Rowid => x_rowid,
3446 X_Resource_Assignment_Id => x_resource_assignment_id,
3447 X_Budget_Version_Id => x_version_id,
3448 X_Project_Id => x_project_id,
3449 X_Task_Id => TmpActTab(j).task_id,
3450 X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
3451 X_Description => NULL,
3452 X_Start_Date => TmpActTab(j).start_date,
3453 X_End_Date => TmpActTab(j).end_date,
3454 X_Period_Name => TmpActTab(j).period_name,
3455 X_Quantity => TmpActTab(j).quantity,
3456 X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
3457 X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
3458 X_Raw_Cost => TmpActTab(j).raw_cost,
3459 X_Burdened_Cost => TmpActTab(j).burdened_cost,
3460 X_Revenue => TmpActTab(j).revenue,
3461 X_Change_Reason_Code => NULL,
3462 X_Last_Update_Date => sysdate,
3463 X_Last_Updated_By => x_created_by,
3464 X_Creation_Date => sysdate,
3465 X_Created_By => x_created_by,
3466 X_Last_Update_Login => x_last_update_login,
3467 X_Attribute_Category => NULL,
3468 X_Attribute1 => NULL,
3469 X_Attribute2 => NULL,
3470 X_Attribute3 => NULL,
3471 X_Attribute4 => NULL,
3472 X_Attribute5 => NULL,
3473 X_Attribute6 => NULL,
3474 X_Attribute7 => NULL,
3475 X_Attribute8 => NULL,
3476 X_Attribute9 => NULL,
3477 X_Attribute10 => NULL,
3478 X_Attribute11 => NULL,
3479 X_Attribute12 => NULL,
3480 X_Attribute13 => NULL,
3481 X_Attribute14 => NULL,
3482 X_Attribute15 => NULL,
3483 X_Calling_Process => 'PR',
3484 X_Pm_Product_Code => NULL,
3485 X_Pm_Budget_Line_Reference => NULL,
3486 X_raw_cost_source => 'A',
3487 X_burdened_cost_source => 'A',
3488 X_quantity_source => 'A',
3489 X_revenue_source => 'A'--,
3490 --X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
3491 );
3492 end if;-- added for bug 6509313
3493 end if;
3494 End Loop;
3495 end if;
3496
3497 end if;
3498 /* End of part 4 for BUg 4889056 */
3499 /* Begin of commented code
3500 for task_rec in (select t.task_id
3501 from pa_tasks t
3502 where t.project_id = x_project_id
3503 and not exists
3504 (select 1
3505 from pa_tasks t1
3506 where t1.parent_task_id = t.task_id)
3507
3508 ) loop
3509
3510 if (x_categorization_code = 'N') then
3511 -- lowest level task, uncategorized
3512 x_quantity := 0;
3513 x_raw_cost := 0;
3514 x_burdened_cost := 0;
3515 x_revenue := 0;
3516 x_labor_hours := 0;
3517 x_unit_of_measure := NULL;
3518
3519 pa_accum_api.get_proj_accum_actuals(x_project_id,
3520 task_rec.task_id,
3521 NULL,
3522 x_time_phased_type_code,
3523 period_rec.period_name,
3524 period_rec.start_date,
3525 period_rec.end_date,
3526 x_revenue,
3527 x_raw_cost,
3528 x_burdened_cost,
3529 x_quantity,
3530 x_labor_hours,
3531 x_dummy1,
3532 x_dummy2,
3533 x_dummy3,
3534 x_dummy4,
3535 x_dummy5,
3536 x_dummy6,
3537 x_unit_of_measure,
3538 x_err_stage,
3539 x_err_code
3540 );
3541
3542 if (x_err_code <> 0) then
3543 rollback to before_copy_actual;
3544 return;
3545 end if;
3546
3547 if x_budget_amount_code = 'C' then
3548 x_revenue := null;
3549
3550 -- Bug# 2107130 Following three if/end if statement are added
3551 if x_cost_quantity_flag = 'N' then
3552 x_labor_hours := null;
3553 x_uncat_unit_of_measure := null;
3554 end if;
3555
3556 if x_raw_cost_flag = 'N' then
3557 x_raw_cost := null;
3558 end if;
3559
3560 if x_burdened_cost_flag = 'N' then
3561 x_burdened_cost := null;
3562 end if;
3563
3564 else
3565 x_raw_cost := null;
3566 x_burdened_cost := null;
3567
3568 -- Bug# 2107130 Following two if/end if statement are added
3569 if x_rev_quantity_flag = 'N' then
3570 x_labor_hours := null;
3571 x_uncat_unit_of_measure := null;
3572 end if;
3573
3574 if x_revenue_flag = 'N' then
3575 x_revenue := null;
3576 end if;
3577
3578 end if;
3579
3580 if ( (nvl(x_labor_hours,0) <> 0) -- Changed for Bug 2107130
3581 or (nvl(x_raw_cost,0) <> 0)
3582 or (nvl(x_burdened_cost,0) <> 0)
3583 or (nvl(x_revenue,0) <> 0)) then
3584
3585 -- ***** Bug # 2021295 - BEGIN *****
3586
3587 PAXBUEBU:COPY ACTUALS DOES NOT PICK UP ACTUAL REVENUE FOR WORK/EVENT BUDGET
3588 Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
3589 from "Positional Parameter Passing" to "Named Parameter Passing".
3590
3591
3592 pa_budget_lines_v_pkg.insert_row (
3593 X_Rowid => x_rowid,
3594 X_Resource_Assignment_Id => x_resource_assignment_id,
3595 X_Budget_Version_Id => x_version_id,
3596 X_Project_Id => x_project_id,
3597 X_Task_Id => task_rec.task_id,
3598 X_Resource_List_Member_Id => x_uncat_res_list_member_id,
3599 X_Description => NULL,
3600 X_Start_Date => period_rec.start_date,
3601 X_End_Date => period_rec.end_date,
3602 X_Period_Name => period_rec.period_name,
3603 X_Quantity => x_labor_hours, -- Changed for bug# 2107130
3604 X_Unit_Of_Measure => x_uncat_unit_of_measure,
3605 X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
3606 X_Raw_Cost => x_raw_cost,
3607 X_Burdened_Cost => x_burdened_cost,
3608 X_Revenue => x_revenue,
3609 X_Change_Reason_Code => NULL,
3610 X_Last_Update_Date => sysdate,
3611 X_Last_Updated_By => x_created_by,
3612 X_Creation_Date => sysdate,
3613 X_Created_By => x_created_by,
3614 X_Last_Update_Login => x_last_update_login,
3615 X_Attribute_Category => NULL,
3616 X_Attribute1 => NULL,
3617 X_Attribute2 => NULL,
3618 X_Attribute3 => NULL,
3619 X_Attribute4 => NULL,
3620 X_Attribute5 => NULL,
3621 X_Attribute6 => NULL,
3622 X_Attribute7 => NULL,
3623 X_Attribute8 => NULL,
3624 X_Attribute9 => NULL,
3625 X_Attribute10 => NULL,
3626 X_Attribute11 => NULL,
3627 X_Attribute12 => NULL,
3628 X_Attribute13 => NULL,
3629 X_Attribute14 => NULL,
3630 X_Attribute15 => NULL,
3631 X_Calling_Process => 'PR',
3632 X_Pm_Product_Code => NULL,
3633 X_Pm_Budget_Line_Reference => NULL,
3634 X_raw_cost_source => 'A',
3635 X_burdened_cost_source => 'A',
3636 X_quantity_source => 'A',
3637 X_revenue_source => 'A');
3638 -- Bug Fix: 4569365. Removed MRC code.
3639 -- X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
3640 -- );
3641 -- ***** Bug # 2021295 - END ****
3642
3643 if (x_err_code <> 0) then
3644 rollback to before_copy_actual;
3645 return;
3646 end if;
3647
3648 end if;
3649
3650 else
3651
3652 -- lowest level task, categorized
3653 for res_rec in (select m.resource_list_member_id,
3654 m.resource_id,
3655 m.track_as_labor_flag
3656 from pa_resource_list_members m
3657 where m.resource_list_id =
3658 x_resource_list_id
3659 and nvl(m.migration_code, 'M') = 'M'
3660 and not exists
3661 (select 1
3662 from pa_resource_list_members m1
3663 where m1.parent_member_id =
3664 m.resource_list_member_id)
3665 ) loop
3666
3667 x_err_stage := 'process period/task/resource <'
3668 || period_rec.period_name
3669 || '><' || to_char(task_rec.task_id)
3670 || '><' || to_char(res_rec.resource_list_member_id)
3671 || '>';
3672
3673 -- Added for bug 3896747
3674 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
3675 fnd_file.put_line(1,x_err_stage);
3676 End if;
3677
3678 x_quantity := 0;
3679 x_raw_cost := 0;
3680 x_burdened_cost := 0;
3681 x_revenue := 0;
3682 x_labor_hours := 0;
3683 x_unit_of_measure := NULL;
3684
3685 pa_accum_api.get_proj_accum_actuals(x_project_id,
3686 task_rec.task_id,
3687 res_rec.resource_list_member_id,
3688 x_time_phased_type_code,
3689 period_rec.period_name,
3690 period_rec.start_date,
3691 period_rec.end_date,
3692 x_revenue,
3693 x_raw_cost,
3694 x_burdened_cost,
3695 x_quantity,
3696 x_labor_hours,
3697 x_dummy1,
3698 x_dummy2,
3699 x_dummy3,
3700 x_dummy4,
3701 x_dummy5,
3702 x_dummy6,
3703 x_unit_of_measure,
3704 x_err_stage,
3705 x_err_code
3706 );
3707
3708 if (x_err_code <> 0) then
3709 rollback to before_copy_actual;
3710 return;
3711 end if;
3712
3713 if x_budget_amount_code = 'C' then
3714 x_revenue := null;
3715
3716 -- Bug# 2107130 Following three if/end if statement are added
3717 if x_cost_quantity_flag = 'N' then
3718 x_quantity := null;
3719 x_unit_of_measure := null;
3720 end if;
3721
3722 if x_raw_cost_flag = 'N' then
3723 x_raw_cost := null;
3724 end if;
3725
3726 if x_burdened_cost_flag = 'N' then
3727 x_burdened_cost := null;
3728 end if;
3729
3730 else
3731 x_raw_cost := null;
3732 x_burdened_cost := null;
3733
3734 -- Bug# 2107130 Following two if/end if statement are added
3735 if x_rev_quantity_flag = 'N' then
3736 x_quantity := null;
3737 x_unit_of_measure := null;
3738 end if;
3739
3740 if x_revenue_flag = 'N' then
3741 x_revenue := null;
3742 end if;
3743
3744 end if;
3745
3746 if ( (nvl(x_quantity,0) <> 0)
3747 or (nvl(x_raw_cost,0) <> 0)
3748 or (nvl(x_burdened_cost,0) <> 0)
3749 or (nvl(x_revenue,0) <> 0)) then
3750
3751 -- ***** Bug # 2021295 - BEGIN *****
3752
3753 PAXBUEBU:COPY ACTUALS DOES NOT PICK UP ACTUAL REVENUE FOR WORK/EVENT BUDGET
3754 Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
3755 from "Positional Parameter Passing" to "Named Parameter Passing".
3756
3757
3758 pa_budget_lines_v_pkg.insert_row (
3759 X_Rowid => x_rowid,
3760 X_Resource_Assignment_Id => x_resource_assignment_id,
3761 X_Budget_Version_Id => x_version_id,
3762 X_Project_Id => x_project_id,
3763 X_Task_Id => task_rec.task_id,
3764 X_Resource_List_Member_Id => res_rec.resource_list_member_id,
3765 X_Description => NULL,
3766 X_Start_Date => period_rec.start_date,
3767 X_End_Date => period_rec.end_date,
3768 X_Period_Name => period_rec.period_name,
3769 X_Quantity => x_quantity,
3770 X_Unit_Of_Measure => x_unit_of_measure,
3771 X_Track_As_Labor_Flag => res_rec.track_as_labor_flag,
3772 X_Raw_Cost => x_raw_cost,
3773 X_Burdened_Cost => x_burdened_cost,
3774 X_Revenue => x_revenue,
3775 X_Change_Reason_Code => NULL,
3776 X_Last_Update_Date => sysdate,
3777 X_Last_Updated_By => x_created_by,
3778 X_Creation_Date => sysdate,
3779 X_Created_By => x_created_by,
3780 X_Last_Update_Login => x_last_update_login,
3781 X_Attribute_Category => NULL,
3782 X_Attribute1 => NULL,
3783 X_Attribute2 => NULL,
3784 X_Attribute3 => NULL,
3785 X_Attribute4 => NULL,
3786 X_Attribute5 => NULL,
3787 X_Attribute6 => NULL,
3788 X_Attribute7 => NULL,
3789 X_Attribute8 => NULL,
3790 X_Attribute9 => NULL,
3791 X_Attribute10 => NULL,
3792 X_Attribute11 => NULL,
3793 X_Attribute12 => NULL,
3794 X_Attribute13 => NULL,
3795 X_Attribute14 => NULL,
3796 X_Attribute15 => NULL,
3797 X_Calling_Process => 'PR',
3798 X_Pm_Product_Code => NULL,
3799 X_Pm_Budget_Line_Reference => NULL,
3800 X_raw_cost_source => 'A',
3801 X_burdened_cost_source => 'A',
3802 X_quantity_source => 'A',
3803 X_revenue_source => 'A');
3804 -- Bug Fix: 4569365. Removed MRC code.
3805 -- X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
3806 -- );
3807 -- ***** Bug # 2021295 - END ****
3808
3809 if (x_err_code <> 0) then
3810 rollback to before_copy_actual;
3811 return;
3812 end if;
3813
3814 end if;
3815
3816 end loop; -- resource
3817
3818 end if;
3819
3820 end loop; -- task
3821
3822 end if;
3823
3824 end loop; -- period
3825
3826
3827 if (x_time_phased_type_code = 'P') then
3828 close pa_cursor;
3829 else
3830 close gl_cursor;
3831 end if; */ --End of commented code
3832 -- Bug Fix: 4569365. Removed MRC code.
3833 -- pa_mrc_finplan.g_calling_module := null; /* FPB2: MRC */
3834
3835 x_err_stack := old_stack;
3836
3837 exception
3838 when others then
3839 x_err_code := SQLCODE;
3840 -- Bug Fix: 4569365. Removed MRC code.
3841 -- pa_mrc_finplan.g_calling_module := null; /* FPB2: MRC */
3842 return;
3843 end copy_actual;
3844
3845 /* Starts added for bug # 6509313 */
3846
3847 PROCEDURE rollup_amounts_rg(
3848 X_Resource_Assignment_Id IN OUT NOCOPY NUMBER,
3849 X_Budget_Version_Id NUMBER,
3850 X_Project_Id NUMBER,
3851 X_Task_Id NUMBER,
3852 X_Resource_List_Member_Id IN OUT NOCOPY NUMBER,
3853 X_Start_Date DATE,
3854 X_End_Date DATE,
3855 X_Period_Name VARCHAR2,
3856 X_Quantity NUMBER,
3857 X_Unit_Of_Measure VARCHAR2,
3858 X_Track_As_Labor_Flag VARCHAR2,
3859 X_Raw_Cost NUMBER,
3860 X_Burdened_Cost NUMBER,
3861 X_Revenue NUMBER
3862 )
3863 IS
3864 --BUG 6509313 Start
3865 cursor get_parent_member(x_child_member_id number) is
3866 select p2.resource_list_member_id parent_member_id
3867 from pa_resource_list_members p1,
3868 pa_resource_list_members p2
3869 where p1.parent_member_id = p2.resource_list_member_id
3870 and p1.resource_list_member_id = x_child_member_id; -- child id
3871
3872 cursor parent_amounts(l_parent_id number,
3873 x_budget_version_id number,
3874 x_task_id number
3875 )
3876 is
3877 select pbl.resource_assignment_id resource_assignment_id
3878 from pa_budget_lines pbl,
3879 pa_resource_assignments pra
3880 where pra.resource_list_member_id = l_parent_id
3881 and pbl.resource_assignment_id = pra.resource_assignment_id
3882 and pra.budget_version_id = x_budget_version_id
3883 and nvl(pra.task_id, 0) = nvl(x_task_id, 0) ;
3884
3885 parent_rec parent_amounts%ROWTYPE;
3886
3887 l_parent_id number;
3888
3889 pragma autonomous_transaction;
3890
3891 BEGIN
3892 open get_parent_member(X_Resource_List_Member_Id);
3893 fetch get_parent_member INTO l_parent_id;
3894
3895 open parent_amounts(l_parent_id,
3896 X_Budget_Version_Id,
3897 X_Task_Id);
3898
3899 FETCH parent_amounts INTO parent_rec;
3900
3901 if (parent_amounts%FOUND) Then
3902 X_Resource_Assignment_Id := parent_rec.resource_assignment_id;
3903 X_Resource_List_Member_Id := l_parent_id;
3904 end if;
3905 close parent_amounts;
3906
3907 close get_parent_member;
3908
3909 EXCEPTION
3910 WHEN OTHERS THEN
3911 NULL;
3912
3913 END rollup_amounts_rg;
3914
3915 -------------------------------------------------------------------------------------
3916 -- This procedure is used by the baseline procedure to copy budget lines and
3917 -- resource assignments from a source (draft) budget version to the destination
3918 -- (baselined) budget version for a single project
3919 --
3920 -- Notes
3921 -- !!! This procedure does NOT copy lines for FP plan types !!!
3922 --
3923 -- This procedure only supports r11.5.7 Budgets. Minimal modifications
3924 -- have been made to copy new FP currency codes and so on.
3925 --
3926 -- History
3927 --
3928 -- 30-MAY-01 jwhite As per Budget Integration development, added
3929 -- the following columns to copy_draft_lines
3930 -- procedure.
3931 -- 1. X_Code_Combination_Id
3932 -- 2. X_CCID_Gen_Status_Code
3933 -- 3. X_CCID_Gen_Rej_Message
3934 --
3935 --
3936 -- 27-JUN-2002 jwhite Bug 1877119
3937 -- For the Copy_Lines procedure, add new column
3938 -- for insert into pa_resource_assignments:
3939 -- project_assignment_id, default -1.
3940 --
3941 -- 13-AUG-2002 jwhite To prevent FP model queries from breaking,
3942 -- added the following columns to the insert:
3943 -- a. projfunc_currency_code
3944 -- b. project_currency_code
3945 -- c. txn_currency_code
3946 --
3947 -- Additionally, the following filter was added for
3948 -- pa_resource_assignments:
3949 -- NVL(RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED'
3950 --
3951
3952
3953 procedure copy_draft_lines (x_src_version_id in number,
3954 x_time_phased_type_code in varchar2,
3955 x_entry_level_code in varchar2,
3956 x_dest_version_id in number,
3957 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
3958 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3959 x_err_stack in out NOCOPY varchar2, --File.Sql.39 bug 4440895
3960 x_pm_flag in varchar2 )
3961 is
3962 -- Standard who
3963 x_created_by NUMBER(15);
3964 x_last_update_login NUMBER(15);
3965
3966 old_stack varchar2(630);
3967
3968 x_msg_count NUMBER := 0;
3969 x_msg_data VARCHAR2(2000);
3970 x_return_status VARCHAR2(2000);
3971
3972 l_target_is_baselined VARCHAR2(1);
3973
3974 begin
3975
3976 x_err_code := 0;
3977 old_stack := x_err_stack;
3978 x_err_stack := x_err_stack || '->copy_draft_lines';
3979
3980 x_created_by := FND_GLOBAL.USER_ID;
3981 x_last_update_login := FND_GLOBAL.LOGIN_ID;
3982
3983 -- Bug 3266168: commented-out savepoint since this procedure is called from a procedure with a savepoint.
3984 --savepoint before_copy_draft_lines;
3985
3986 begin
3987 select 'Y'
3988 into l_target_is_baselined
3989 from pa_budget_versions
3990 where budget_status_code = 'B'
3991 and budget_version_id = x_dest_version_id;
3992 exception
3993 when no_data_found then
3994 l_target_is_baselined := 'N';
3995 end;
3996
3997 x_err_stage := 'copy resource assignment <' || to_char(x_src_version_id)
3998 || '>' ;
3999
4000 insert into pa_resource_assignments
4001 (resource_assignment_id,
4002 budget_version_id,
4003 project_id,
4004 task_id,
4005 resource_list_member_id,
4006 last_update_date,
4007 last_updated_by,
4008 creation_date,
4009 created_by,
4010 last_update_login,
4011 unit_of_measure,
4012 track_as_labor_flag,
4013 project_assignment_id,
4014 RESOURCE_ASSIGNMENT_TYPE
4015 )
4016 select pa_resource_assignments_s.nextval,
4017 x_dest_version_id,
4018 s.project_id,
4019 s.task_id,
4020 s.resource_list_member_id,
4021 SYSDATE,
4022 x_created_by,
4023 SYSDATE,
4024 x_created_by,
4025 x_last_update_login,
4026 s.unit_of_measure,
4027 s.track_as_labor_flag,
4028 -1,
4029 s.RESOURCE_ASSIGNMENT_TYPE
4030 from
4031 pa_resource_assignments s
4032 where s.budget_version_id = x_src_version_id
4033 and NVL(s.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
4034
4035 -- Bug Fix: 4569365. Removed MRC code.
4036 x_err_stage := 'calling populate_bl_map_tmp <' ||to_char(x_src_version_id)
4037 || '>' ;
4038
4039 -- FPB2: MRC
4040 /* MRC Elimination changes: PA_MRC_FINPLAN.populate_bl_map_tmp */
4041 PA_FIN_PLAN_UTILS2.populate_bl_map_tmp
4042 (p_source_fin_plan_version_id => x_src_version_id,
4043 x_return_status => x_return_status,
4044 x_msg_count => x_msg_count,
4045 x_msg_data => x_msg_data);
4046
4047 x_err_stage := 'copy budget lines <' ||to_char(x_src_version_id)
4048 || '>' ;
4049
4050 insert into pa_budget_lines
4051 (budget_line_id, /* FPB2 during changes for MRC */
4052 budget_version_id, /* FPB2 */
4053 resource_assignment_id,
4054 start_date,
4055 last_update_date,
4056 last_updated_by,
4057 creation_date,
4058 created_by,
4059 last_update_login,
4060 end_date,
4061 period_name,
4062 quantity,
4063 raw_cost,
4064 burdened_cost,
4065 revenue,
4066 change_reason_code,
4067 description,
4068 attribute_category,
4069 attribute1,
4070 attribute2,
4071 attribute3,
4072 attribute4,
4073 attribute5,
4074 attribute6,
4075 attribute7,
4076 attribute8,
4077 attribute9,
4078 attribute10,
4079 attribute11,
4080 attribute12,
4081 attribute13,
4082 attribute14,
4083 attribute15,
4084 pm_product_code,
4085 pm_budget_line_reference,
4086 raw_cost_source,
4087 burdened_cost_source,
4088 quantity_source,
4089 revenue_source,
4090 Code_Combination_Id,
4091 CCID_Gen_Status_Code,
4092 CCID_Gen_Rej_Message,
4093 projfunc_currency_code,
4094 project_currency_code,
4095 txn_currency_code
4096 )
4097 select
4098 bmt.target_budget_line_id, /* FPB2 */
4099 da.budget_version_id, /* FPB2 */
4100 da.resource_assignment_id,
4101 l.start_date,
4102 SYSDATE,
4103 x_created_by,
4104 SYSDATE,
4105 x_created_by,
4106 x_last_update_login,
4107 l.end_date,
4108 l.period_name,
4109 l.quantity,
4110 l.raw_cost,
4111 l.burdened_cost,
4112 l.revenue,
4113 l.change_reason_code,
4114 l.description,
4115 l.attribute_category,
4116 l.attribute1,
4117 l.attribute2,
4118 l.attribute3,
4119 l.attribute4,
4120 l.attribute5,
4121 l.attribute6,
4122 l.attribute7,
4123 l.attribute8,
4124 l.attribute9,
4125 l.attribute10,
4126 l.attribute11,
4127 l.attribute12,
4128 l.attribute13,
4129 l.attribute14,
4130 l.attribute15,
4131 decode(x_pm_flag,'Y',l.pm_product_code,NULL),
4132 decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
4133 'B',
4134 'B',
4135 'B',
4136 'B',
4137 l.Code_Combination_Id,
4138 l.CCID_Gen_Status_Code,
4139 l.CCID_Gen_Rej_Message,
4140 l.projfunc_currency_code,
4141 l.project_currency_code,
4142 l.txn_currency_code
4143 from pa_budget_lines l,
4144 pa_resource_assignments sa,
4145 pa_resource_assignments da,
4146 pa_fp_bl_map_tmp bmt /* FPB2 */
4147 where l.resource_assignment_id = sa.resource_assignment_id
4148 and sa.budget_version_id = x_src_version_id
4149 and sa.task_id = da.task_id
4150 and sa.project_id = da.project_id
4151 and sa.resource_list_member_id = da.resource_list_member_id
4152 and da.budget_version_id = x_dest_version_id
4153 and NVL(sa.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED'
4154 and bmt.source_budget_line_id = l.budget_line_id /* FPB2: MRC */ ;
4155 -- Bug Fix: 4569365. Removed MRC code.
4156 /* FPB2: MRC */
4157 /*******************************
4158 BEGIN
4159
4160 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
4161 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
4162 (x_return_status => x_return_status,
4163 x_msg_count => x_msg_count,
4164 x_msg_data => x_msg_data);
4165 END IF;
4166
4167 -- Bug 2676494
4168
4169 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS THEN
4170 IF PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
4171 -- This api is called only by baseline api
4172 PA_MRC_FINPLAN.COPY_MC_BUDGET_LINES
4173 (p_source_fin_plan_version_id => x_src_version_id,
4174 p_target_fin_plan_version_id => x_dest_version_id,
4175 x_return_status => x_return_status,
4176 x_msg_count => x_msg_count,
4177 x_msg_data => x_msg_data);
4178 ELSIF (PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'B' AND l_target_is_baselined = 'Y') THEN
4179 PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES
4180 (p_fin_plan_version_id => x_dest_version_id, -- Target version should be passed
4181 p_entire_version => 'Y',
4182 x_return_status => x_return_status,
4183 x_msg_count => x_msg_count,
4184 x_msg_data => x_msg_data);
4185
4186 END IF;
4187 END IF;
4188
4189 --Bug 2676494
4190
4191 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4192 RAISE g_mrc_exception;
4193 END IF;
4194
4195
4196 END;
4197 *************************************/
4198
4199
4200 x_err_stack := old_stack;
4201
4202
4203 exception
4204 when others then
4205 x_err_code := SQLCODE;
4206 --rollback to before_copy_draft_lines;
4207 return;
4208
4209 end copy_draft_lines;
4210
4211 /*------------------------------------------------------------------------------------------------------------------
4212 Added for performance issue
4213 ------------------------------------------------------------------------------------------------------------------*/
4214 function get_first_accum_period ( x_project_id in number,
4215 x_budget_type_code in varchar2)
4216 return date is
4217
4218 cursor get_info is
4219 select pbv.resource_list_id,
4220 pbem.time_phased_type_code,
4221 pbv.budget_version_id
4222 from pa_budget_versions pbv,
4223 pa_budget_entry_methods pbem
4224 where pbem.budget_entry_method_code = pbv.budget_entry_method_code
4225 and pbv.project_id = x_project_id
4226 and pbv.budget_type_code = x_budget_type_code
4227 and pbv.budget_status_code = 'W';
4228
4229 cursor get_budget_amount_code(x_version_id pa_budget_versions.budget_version_id%type) is
4230 select budget_amount_code
4231 from pa_budget_versions b,
4232 pa_budget_types t
4233 where b.budget_version_id = x_version_id
4234 and b.budget_type_code = t.budget_type_code;
4235
4236 l_resource_list_id pa_resource_lists_all_bg.resource_list_id%type;
4237 l_time_phased_type_code pa_budget_entry_methods.time_phased_type_code%type;
4238 l_start_period_name pa_periods_all.period_name%type;
4239 l_start_period_date pa_periods_all.start_date%type;
4240 l_budget_version_id pa_budget_versions.budget_version_id%type;
4241 l_budget_amount_code pa_budget_types.budget_amount_code%type;
4242 x_err_code number;
4243 x_err_stage varchar2(2000);
4244 x_err_stack varchar2(2000);
4245 x_process_flag varchar2(1);
4246 Begin
4247 x_process_flag := 'N';
4248 If g_project_id is not null and
4249 g_budget_type_code is not null then
4250 if x_project_id <> g_project_id or --changed the condition for bug 6134042
4251 x_budget_type_code <> g_budget_type_code then
4252 x_process_flag := 'Y';
4253 Else
4254 x_process_flag := 'N';
4255 End if;
4256 elsif
4257 g_project_id is null and
4258 g_budget_type_code is null then
4259 x_process_flag := 'Y';
4260 end if;
4261
4262 If x_process_flag = 'Y' then
4263 g_project_id := x_project_id;
4264 g_budget_type_code := x_budget_type_code;
4265
4266 Open get_info;
4267 Fetch get_info into l_resource_list_id, l_time_phased_type_code, l_budget_version_id;
4268 Close get_info;
4269
4270 open get_budget_amount_code(l_budget_version_id);
4271 fetch get_budget_amount_code into l_budget_amount_code;
4272 close get_budget_amount_code;
4273
4274 pa_accum_utils.get_first_accum_period(x_project_id,
4275 l_resource_list_id,
4276 l_budget_amount_code,
4277 l_time_phased_type_code,
4278 l_start_period_name,
4279 l_start_period_date,
4280 x_err_code,
4281 x_err_stage,
4282 x_err_stack);
4283
4284
4285 if (x_err_code <> 0) then
4286 g_project_id := NULL;
4287 g_budget_type_code := NULL;
4288 return null;
4289 end if;
4290 g_start_period_date := l_start_period_date;
4291 end if;
4292
4293 Return g_start_period_date;
4294
4295 Exception
4296 When Others Then
4297 g_project_id := NULL;
4298 g_budget_type_code := NULL;
4299 Return NULL;
4300 end get_first_accum_period;
4301
4302 /*********************************************************************************************
4303 Autonomous transaction is used as the value should appear in the database. Based on this value
4304 copy actuals is allowed or restricted through budget form and/or concurrent request
4305 *********************************************************************************************/
4306 procedure update_budget_version (x_request_id number default null,
4307 x_budget_version_id pa_budget_versions.budget_version_id%type)
4308 is
4309 pragma autonomous_transaction;
4310 begin
4311 update pa_budget_versions
4312 set request_id = x_request_id
4313 where budget_version_id = x_budget_version_id;
4314 commit;
4315 end;
4316
4317 /*********************************************************************************************
4318 Wrapper over procedure copy actuals. It will be called from the concurrent request
4319 PRC: Copy Actuals
4320 *********************************************************************************************/
4321 procedure copy_actuals1 ( errbuf IN OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
4322 retcode IN OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
4323 x_project_id in number,
4324 x_budget_type_code in varchar2,
4325 x_start_period in varchar2,
4326 x_end_period in varchar2)
4327 is
4328
4329 cursor get_budget_info is
4330 select pbv.resource_list_id,
4331 pbv.budget_entry_method_code,
4332 pbv.budget_version_id
4333 from pa_budget_versions pbv
4334 where pbv.project_id = x_project_id
4335 and pbv.budget_type_code = x_budget_type_code
4336 and pbv.budget_status_code = 'W';
4337
4338 x_err_code number;
4339 x_err_stack varchar2(2000);
4340 x_err_stage varchar2(2000);
4341 l_resource_list_id pa_resource_lists_all_bg.resource_list_id%type;
4342 l_budget_entry_method_code pa_budget_entry_methods.time_phased_type_code%type;
4343 l_budget_version_id pa_budget_versions.budget_version_id%type;
4344
4345
4346 l_start_period_date pa_periods_all.start_date%type;
4347 l_end_period_date pa_periods_all.end_date%type;
4348
4349 l_request_id number;
4350
4351 exc_wrong_period_set exception;
4352 exc_copy_actual exception;
4353
4354 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');
4355
4356 begin
4357 --Initializing global variable
4358 g_calling_mode := 'CONCURRENT REQUEST';
4359
4360 -- Print the input parameter values
4361 fnd_file.put_line(1, 'x_project_id :'||x_project_id);
4362 fnd_file.put_line(1, 'x_budget_type_code :'||x_budget_type_code);
4363 fnd_file.put_line(1, 'x_start_period :'||x_start_period);
4364 fnd_file.put_line(1, 'x_end_period :'||x_end_period);
4365 fnd_file.put_line(1, 'x_debug_mode :'||p_debug_mode);
4366
4367 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4368 fnd_file.put_line(1, 'Calling Copy Actuals');
4369 End if;
4370
4371 select request_id
4372 into l_request_id
4373 from pa_budget_versions
4374 where project_id = x_project_id
4375 and budget_type_code = x_budget_type_code
4376 and budget_status_code = 'W';
4377
4378 If nvl(l_request_id,-1) = -99 then
4379 raise exc_copy_actual;
4380 else
4381
4382 select period_start_date
4383 into l_start_period_date
4384 from pa_budget_periods_v
4385 where period_name = x_start_period;
4386
4387 select period_start_date
4388 into l_end_period_date
4389 from pa_budget_periods_v
4390 where period_name = x_end_period;
4391
4392 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4393 fnd_file.put_line(1, 'Start Period :'||l_start_period_date||' End Period :'||l_end_period_date);
4394 End if;
4395
4396 If l_start_period_date <= l_end_period_date then
4397
4398
4399 Open get_budget_info;
4400 Fetch get_budget_info into l_resource_list_id, l_budget_entry_method_code, l_budget_version_id;
4401 Close get_budget_info;
4402
4403 update_budget_version( x_request_id => -99,
4404 x_budget_version_id => l_budget_version_id);
4405
4406 pa_budget_core1.copy_actual( x_project_id,
4407 l_budget_version_id,
4408 l_budget_entry_method_code,
4409 l_resource_list_id,
4410 x_start_period,
4411 x_end_period,
4412 x_err_code,
4413 x_err_stage,
4414 x_err_stack);
4415 retcode := x_err_code;
4416 errbuf := x_err_stack;
4417 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4418 fnd_file.put_line(1, errbuf);
4419 End if;
4420
4421 update pa_budget_versions
4422 set request_id = NULL
4423 where budget_version_id = l_budget_version_id;
4424
4425 else
4426 raise exc_wrong_period_set;
4427 end if;
4428
4429 end if;
4430
4431 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4432 fnd_file.put_line(1, 'After Copy Actuals');
4433 End if;
4434
4435 exception
4436 when exc_copy_actual then
4437 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4438 fnd_file.put_line(1, 'Copy actual is not allowed. It is being performed by other program for this project and budget type');
4439 End if;
4440 null;
4441 when exc_wrong_period_set then
4442 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4443 fnd_file.put_line(1, 'Copy actual is not allowed. Start period cannot be greater than the end period');
4444 End if;
4445 null;
4446 when others then
4447 If p_debug_mode = 'Y' and g_calling_mode = 'CONCURRENT REQUEST' then
4448 fnd_file.put_line(1, sqlerrm);
4449 End if;
4450 null;
4451 end copy_actuals1;
4452
4453
4454 /*------------------------------------------------------------------------------------------------------------------
4455 Added for performance issue
4456 ------------------------------------------------------------------------------------------------------------------*/
4457
4458 end pa_budget_core1 ;