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