[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_ACCUM_MAIN
Source
1 PACKAGE BODY Pa_Proj_Accum_Main AS
2 /* $Header: PARESMNB.pls 120.7.12010000.4 2008/10/17 12:48:22 rballamu ship $ */
3
4 x_run_id NUMBER;
5 x_status NUMBER;
6 x_stage NUMBER;
7
8 -- Local function. Checks that the project number specified by
9 -- the user is of valid project type if one specified.
10 --
11
12 FUNCTION check_valid_project(p_proj_num VARCHAR2, p_project_type VARCHAR2) RETURN NUMBER IS
13 x_return VARCHAR2(1);
14
15 BEGIN
16
17 SELECT 'X'
18 INTO x_return
19 FROM pa_projects proj
20 WHERE proj.project_type = p_project_type
21 AND proj.segment1 = p_proj_num;
22
23 IF x_return IS NULL THEN
24 RETURN 0;
25 ELSE
26 RETURN 1;
27 END IF;
28
29 /* Added for Bug 2628197 */
30 EXCEPTION
31 WHEN OTHERS THEN
32 RETURN 0;
33
34 END check_valid_project;
35
36 --
37 -- Local procedure. Use the MIN and MAX values of the project number
38 -- if the user did not specify the range when submitting the report
39 --
40 /* Modified for bug 2543021.
41 PROCEDURE Get_Project_Num_Range (
42 p_proj_num_from IN VARCHAR2,
43 p_proj_num_to IN VARCHAR2,
44 p_proj_num_from_out OUT VARCHAR2,
45 p_proj_num_to_out OUT VARCHAR2 ) IS
46
47 BEGIN
48
49 IF (p_proj_num_from IS NULL) OR
50 (p_proj_num_to IS NULL) THEN
51
52 SELECT min(segment1), max(segment1)
53 INTO p_proj_num_from_out,
54 p_proj_num_to_out
55 FROM pa_projects;
56
57 END IF;
58
59 IF (p_proj_num_from IS NOT NULL) THEN
60 p_proj_num_from_out := p_proj_num_from;
61 END IF;
62
63 IF (p_proj_num_to IS NOT NULL) THEN
64 p_proj_num_to_out := p_proj_num_to;
65 END IF;
66
67 END Get_Project_Num_Range;
68 */
69
70 PROCEDURE Get_Project_Num_Range (
71 p_proj_num_from IN VARCHAR2,
72 p_proj_num_to IN VARCHAR2,
73 p_proj_num_from_out OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
74 p_proj_num_to_out OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
75 p_proj_type IN VARCHAR2 ) IS
76
77 p_proj_num_from_temp pa_projects.segment1%TYPE;
78
79 BEGIN
80
81 IF (p_proj_num_from IS NULL) OR
82 (p_proj_num_to IS NULL) THEN
83
84 SELECT MIN(segment1), MAX(segment1)
85 INTO p_proj_num_from_out,
86 p_proj_num_to_out
87 FROM pa_projects
88 WHERE project_type = NVL(p_proj_type, project_type);
89
90 END IF;
91
92 IF p_proj_type IS NULL THEN -- project_type is not specified
93
94 IF (p_proj_num_from IS NOT NULL) THEN
95 p_proj_num_from_out := p_proj_num_from;
96 END IF;
97
98 IF (p_proj_num_to IS NOT NULL) THEN
99 p_proj_num_to_out := p_proj_num_to;
100 END IF;
101
102 ELSE -- project_type is specified
103
104 IF (p_proj_num_from IS NOT NULL AND p_proj_num_to IS NULL) THEN
105
106 -- Check if the from project number is of the project_type specified
107 IF check_valid_project(p_proj_num_from,p_proj_type) = 1 THEN
108 p_proj_num_from_out := p_proj_num_from;
109 ELSE
110 SELECT MIN(segment1)
111 INTO p_proj_num_from_temp
112 FROM pa_projects
113 WHERE project_type = p_proj_type
114 AND segment1 BETWEEN p_proj_num_from AND p_proj_num_to_out;
115 END IF;
116 END IF;
117
118 IF (p_proj_num_to IS NOT NULL AND p_proj_num_from IS NULL) THEN
119
120 -- Check if the To project number is of the project_type specified
121 IF check_valid_project(p_proj_num_to,p_proj_type) = 1 THEN
122 p_proj_num_to_out := p_proj_num_to;
123 ELSE
124 SELECT MAX(segment1)
125 INTO p_proj_num_to_out
126 FROM pa_projects
127 WHERE project_type = p_proj_type
128 AND segment1 BETWEEN p_proj_num_from_out AND p_proj_num_to;
129 END IF;
130 END IF;
131
132 IF (p_proj_num_to IS NOT NULL AND p_proj_num_from IS NOT NULL) THEN
133
134 -- Check if the from project number is of the project_type specified
135 IF check_valid_project(p_proj_num_from,p_proj_type) = 1 THEN
136 p_proj_num_from_out := p_proj_num_from;
137 ELSE
138 SELECT MIN(segment1)
139 INTO p_proj_num_from_out
140 FROM pa_projects
141 WHERE project_type = p_proj_type;
142 END IF;
143
144 -- Check if the To project number is of the project_type specified
145 IF check_valid_project(p_proj_num_to,p_proj_type) = 1 THEN
146 p_proj_num_to_out := p_proj_num_to;
147 ELSE
148 SELECT MAX(segment1)
149 INTO p_proj_num_to_out
150 FROM pa_projects
151 WHERE project_type = p_proj_type;
152 END IF;
153
154 END IF;
155
156 IF p_proj_num_from_temp IS NOT NULL THEN
157 p_proj_num_from_out := p_proj_num_from_temp;
158 END IF;
159 END IF;
160
161 END Get_Project_Num_Range;
162
163
164 -- Initialize PROCEDURE
165 -- This procedure initializes global variables for all projects
166 -- Added following 3 new variables for Burden Cost Accounting
167 --
168 -- History
169 --
170 -- 31-JUL-03 jwhite For patchset 'L' Reburdening Enhancement, added this
171 -- IN-parm to the accum_cdls procedure:
172 -- x_cdl_line_type VARCHAR2
173 -- Also, added FND_PROFILE.value call to Initialization
174 -- procedure for conditional processing:
175 -- Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening
176 --
177 -- 01-JUL-04 sacgupta Added call to new procedure get_pa_period_info1
178 -- This procedure initilaize global variables whose
179 -- values are same for all the projects
180 --
181
182 PROCEDURE initialize
183
184 IS
185 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
186
187 BEGIN
188
189 x_old_stack := x_err_stack;
190 x_err_stack := x_err_stack ||'->pa_proj_accum_main.initialize';
191 x_err_code := 0;
192 x_err_stage := 'Summary Accumulation Initialization Procedure';
193
194 -- This procedure initialize the variables for all projects
195 -- get the implementation option and accumulation period information
196
197 -- Get the Accumulation type from PA_IMPLEMENTATIONS
198 -- whether Accumulate by PA_PERIOD or GL_PERIOD
199 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
200 Pa_Debug.debug(x_err_stack);
201 END IF;
202
203 --- Getting the commit size--------------
204 IF Fnd_Profile.value('PA_NUM_EXP_ITEMS_PER_SET') IS NOT NULL THEN
205 Pa_Proj_Accum_Main.x_commit_size := Fnd_Profile.value('PA_NUM_EXP_ITEMS_PER_SET');
206 ELSE
207 Pa_Proj_Accum_Main.x_commit_size := 500;
208 END IF;
209
210 Pa_Accum_Utils.get_impl_option
211 (x_impl_option,
212 x_err_stack,
213 x_err_stage,
214 x_err_code);
215 -- Setting the package variables and global variables so that
216 -- the values can be cached - Performance Phase II changes 594289
217
218 Pa_Utils.Set_business_group_id ;
219 Pa_Currency.Set_currency_info ;
220
221
222 -- Get the information pertaining to the Current PA PERIOD
223
224 Pa_Accum_Utils.get_current_period_info
225 (x_current_pa_period,
226 x_current_gl_period,
227 x_current_pa_start_date,
228 x_current_pa_end_date,
229 x_current_gl_start_date,
230 x_current_gl_end_date,
231 x_current_year,
232 x_err_stack,
233 x_err_stage,
234 x_err_code);
235 -- Caching pa_period and gl_period names,
236 -- to be used in the view pa_commitment_txns_v
237
238 Pa_Accum_Utils.g_current_pa_period := x_current_pa_period ;
239 Pa_Accum_Utils.g_current_gl_period := x_current_gl_period ;
240
241 -- Getting current period year's start and end dates
242
243 Pa_Accum_Utils.Get_period_year_Info
244 (x_current_gl_period,
245 x_period_yr_start_date,
246 x_err_stack,
247 x_err_stage,
248 x_err_code );
249
250 IF x_impl_option = 'PA' THEN
251 x_period_yr_end_date := x_current_pa_end_date;
252 END IF;
253
254 IF x_impl_option = 'GL' THEN
255 x_period_yr_end_date := x_current_gl_end_date;
256 END IF;
257
258 -- Start for performance bug 3653978
259
260 Pa_Accum_Utils.get_pa_period_info1
261 (x_impl_option,
262 x_current_pa_start_date,
263 x_current_gl_start_date,
264 x_prev_pa_period,
265 x_prev_gl_period,
266 x_prev_pa_year,
267 x_prev_gl_year,
268 x_prev_pa_start_date,
269 x_prev_pa_end_date,
270 x_prev_gl_start_date,
271 x_prev_gl_end_date,
272 x_err_stack,
273 x_err_stage,
274 x_err_code);
275
276 -- Set the current period info based on implementation option
277
278 IF x_impl_option = 'PA' THEN
279 x_Current_period := x_current_pa_period;
280 x_Prev_period := x_prev_pa_period;
281 x_current_start_date := x_current_pa_start_date;
282 x_current_end_date := x_current_pa_end_date;
283 x_prev_start_date := x_prev_pa_start_date;
284 x_prev_end_date := x_prev_pa_end_date;
285 x_prev_year := x_prev_pa_year;
286 ELSIF x_impl_option = 'GL' THEN
287 x_Current_period := x_current_gl_period;
288 x_Prev_period := x_prev_gl_period;
289 x_current_start_date := x_current_gl_start_date;
290 x_current_end_date := x_current_gl_end_date;
291 x_prev_start_date := x_prev_gl_start_date;
292 x_prev_end_date := x_prev_gl_end_date;
293 x_prev_year := x_prev_gl_year;
294 END IF;
295
296 -- End for performance bug 3653978
297
298 -- 05-AUG-2003, jwhite: For patchset 'L' Reburdening Enhancement
299 -- This profile value is initialized to 'N' in the package spec.
300 -- This profile is used to conditionally execute the accum_clds procedure
301 -- for incremental burden rows.
302
303
304 --Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening := NVL(FND_PROFILE.value('PA_ENHANCED_BURDENING'), 'N');
305 Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening := pa_utils2.IsEnhancedBurdeningEnabled; /*4278940 */
306
307 IF ( P_DEBUG_MODE = 'Y' )
308 THEN
309 Pa_Debug.debug('proj_accum, Initialize: G_PA_Enhanced_Burdening - '|| Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening );
310 END IF;
311
312
313 -- --------------------------------------------------------------------------
314
315
316 -- # of records processed
317
318 x_recs_processed := 0;
319
320 x_err_stack := x_old_stack;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 x_err_code := SQLCODE;
325 RAISE;
326 END initialize;
327
328 -- This procedure initialize the variables for each project
329
330 PROCEDURE initialize_project (x_project_id IN NUMBER,
331 x_accum_period_type_changed OUT NOCOPY BOOLEAN) --File.Sql.39 bug 4440895
332 IS
333 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
334 l_accum_period_type_changed BOOLEAN;
335
336
337 BEGIN
338
339 x_old_stack := x_err_stack;
340 x_err_stack := x_err_stack ||'->pa_proj_accum_main.initialize_project';
341 x_err_code := 0;
342 x_err_stage :=
343 'Summary Accumulation Initialization Procedure for Project_id= ' || x_project_id;
344
345 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
346 Pa_Debug.debug('initialize_project: ' || x_err_stack);
347 END IF;
348
349 /* Commented for performance bug 3653978
350 -- Initialize the global variables before use for each project
351
352 x_prev_accum_period := NULL;
353 x_current_period := NULL;
354 x_prev_period := NULL;
355 x_prev_pa_period := NULL;
356 x_prev_gl_period := NULL;
357
358 x_proj_level_accum_id := 0;
359 x_prev_year := 0;
360 x_prev_pa_year := 0;
361 x_prev_gl_year := 0;
362 x_prev_accum_year := 0;
363
364 x_current_start_date := NULL;
365 x_current_end_date := NULL;
366 x_prev_start_date := NULL;
367 x_prev_end_date := NULL;
368 x_prev_pa_start_date := NULL;
369 x_prev_pa_end_date := NULL;
370 x_prev_gl_start_date := NULL;
371 x_prev_gl_end_date := NULL;
372 x_prev_accum_start_date := NULL;
373 x_prev_accum_end_date := NULL;
374 x_prev_prev_accum_period := NULL;
375
376 */
377 -- Added for performance bug 3653978
378 -- Initialize the global variables before use for each project
379 x_prev_accum_period := NULL;
380
381 x_proj_level_accum_id := 0;
382
383 -- Create/Get the Project level header record for this project
384
385 Pa_Accum_Utils.proj_level_record
386 (x_project_id,
387 x_current_pa_period,
388 x_current_gl_period,
389 x_impl_option,
390 x_proj_level_accum_id,
391 x_prev_accum_period,
392 x_err_stack,
393 x_err_stage,
394 x_err_code);
395
396 -- Get the other details from PA_PERIOD
397 -- (PREVIOUS period,previously accumulated period etc )
398
399 /* Commented out as part of performance fix 3653978
400 Pa_Accum_Utils.get_pa_period_info
401 (x_impl_option,
402 x_prev_accum_period,
403 x_current_pa_period,
404 x_current_gl_period ,
405 x_current_pa_start_date,
406 x_current_pa_end_date,
407 x_current_gl_start_date,
408 x_current_gl_end_date,
409 x_prev_pa_period,
410 x_prev_gl_period,
411 x_prev_pa_year,
412 x_prev_gl_year,
413 x_prev_accum_year,
414 x_prev_pa_start_date,
415 x_prev_pa_end_date,
416 x_prev_gl_start_date,
417 x_prev_gl_end_date,
418 x_prev_accum_start_date,
419 x_prev_accum_end_date,
420 x_prev_prev_accum_period,
421 l_accum_period_type_changed,
422 x_err_stack,
423 x_err_stage,
424 x_err_code);
425
426 x_accum_period_type_changed := l_accum_period_type_changed;
427
428 -- Set the current period info based on implementation option
429
430 IF x_impl_option = 'PA' THEN
431 x_Current_period := x_current_pa_period;
432 x_Prev_period := x_prev_pa_period;
433 x_current_start_date := x_current_pa_start_date;
434 x_current_end_date := x_current_pa_end_date;
435 x_prev_start_date := x_prev_pa_start_date;
436 x_prev_end_date := x_prev_pa_end_date;
437 x_prev_year := x_prev_pa_year;
438 ELSIF x_impl_option = 'GL' THEN
439 x_Current_period := x_current_gl_period;
440 x_Prev_period := x_prev_gl_period;
441 x_current_start_date := x_current_gl_start_date;
442 x_current_end_date := x_current_gl_end_date;
443 x_prev_start_date := x_prev_gl_start_date;
444 x_prev_end_date := x_prev_gl_end_date;
445 x_prev_year := x_prev_gl_year;
446 END IF;
447 */
448
449 -- Added for performance bug 3653978.
450 -- If the project had been previously accumulated, then get the details
451 -- pertaining to the previously accumulated period
452
453 If x_prev_accum_period is not Null Then
454 If PA_PROJ_ACCUM_MAIN.x_prev_accum_period1 is null or
455 PA_PROJ_ACCUM_MAIN.x_prev_accum_period1 <> x_prev_accum_period Then
456
457 PA_PROJ_ACCUM_MAIN.x_prev_accum_period1 := x_prev_accum_period;
458
459 -- Initialize the global variables before use for each project
460 x_prev_accum_year := 0;
461
462 x_prev_accum_start_date := NULL;
463 x_prev_accum_end_date := NULL;
464 x_prev_prev_accum_period := NULL;
465
466 Pa_Accum_Utils.get_pa_period_info2
467 (x_impl_option,
468 x_prev_accum_period,
469 x_prev_accum_year,
470 x_prev_accum_start_date,
471 x_prev_accum_end_date,
472 x_prev_prev_accum_period,
473 l_accum_period_type_changed,
474 x_err_stack,
475 x_err_stage,
476 x_err_code);
477
478 x_accum_period_type_changed := l_accum_period_type_changed;
479 End If;
480 End If;
481
482 -- End of changes for performance bug 3653978
483
484 x_err_stack := x_old_stack;
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 x_err_code := SQLCODE;
489 RAISE;
490 END initialize_project;
491
492 PROCEDURE check_reporting_period
493 (status IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
494 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
495 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
496 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
497 IS
498 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
499
500 BEGIN
501
502 x_old_stack := x_err_stack;
503 x_err_stack := x_err_stack ||'->pa_proj_accum_main.check_reporting_period';
504 x_err_code := 0;
505 x_err_stage := 'Checking If Reporting Period Moved Backwards in Time';
506
507 -- This procedure checks if the reporting period was moved
508 -- backwards in time during the process run
509
510 -- if the period did moved backward, then we will abort the process
511 -- If the period was moved forward, it is OK
512
513 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
514 Pa_Debug.debug('check_reporting_period: ' || x_err_stack);
515 END IF;
516
517 -- Get the information pertaining to the Current PA PERIOD
518
519 Pa_Accum_Utils.get_current_period_info
520 (x_new_current_pa_period,
521 x_new_current_gl_period,
522 x_new_current_pa_start_date,
523 x_new_current_pa_end_date,
524 x_new_current_gl_start_date,
525 x_new_current_gl_end_date,
526 x_new_current_year,
527 x_err_stack,
528 x_err_stage,
529 x_err_code);
530
531 IF x_impl_option = 'PA' THEN
532 IF (x_new_current_pa_end_date < x_current_pa_end_date) THEN
533 -- Yes, Reporting period was moved backwards
534 status := 'Y';
535 ELSE
536 status := 'N';
537 END IF;
538 ELSIF x_impl_option = 'GL' THEN
539 IF (x_new_current_gl_end_date < x_current_gl_end_date) THEN
540 -- Yes, Reporting period was moved backwards
541 status := 'Y';
542 ELSE
543 status := 'N';
544 END IF;
545 END IF;
546
547 x_err_stack := x_old_stack;
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 x_err_code := SQLCODE;
552 RAISE;
553 END check_reporting_period;
554
555 -- project_num_from : start project # : mandatory
556 -- project_num_to : end project # : mandatory
557 -- actual_cost_flag : 'Y' or 'N' : mandatory
558 -- revenue_flag : 'Y' or 'N' : mandatory
559 -- system_linkage_function : system_linkage_function : optional
560 -- budgets_flag : 'Y' or 'N' : mandatory
561 -- budget_type_code : Budget Type Code: optional
562 -- commitments_flag : 'Y' or 'N' : mandatory
563 -- mode : 'I' for incremental : mandatory
564
565 FUNCTION action_exists
566 (x_action_flag IN VARCHAR2,
567 x_actions_in IN action_tbl_type,
568 x_actions_cnt IN BINARY_INTEGER)
569 RETURN VARCHAR2
570 IS
571 valid_flag VARCHAR2(1) := 'N' ;
572 BEGIN
573
574 FOR cnt_action IN 1 .. x_actions_cnt LOOP
575
576 IF x_actions_in(cnt_action).action_flag = x_action_flag THEN
577 valid_flag := 'Y' ;
578 END IF ;
579
580 END LOOP ;
581
582 RETURN valid_flag ;
583
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 valid_flag := 'N' ;
587 RETURN valid_flag ;
588 WHEN OTHERS THEN
589 x_err_code := SQLCODE;
590 RAISE;
591 END action_exists ;
592
593 FUNCTION check_period_flags
594 (x_project_id IN NUMBER,
595 x_impl_option IN VARCHAR2,
596 x_current_end_date IN DATE,
597 x_action_flag IN VARCHAR2,
598 x_closed_date IN DATE,
599 x_current_start_date IN DATE)
600 RETURN VARCHAR2
601 IS
602
603 x_ret_flag VARCHAR2(1) := 'N' ;
604 l_prev_accum_period VARCHAR2(20) ;
605 l_prev_accum_end_date DATE ;
606 l_prev_year NUMBER ;
607
608 BEGIN
609
610 SELECT DISTINCT accum_period
611 INTO l_prev_accum_period
612 FROM pa_project_accum_headers
613 WHERE project_id = x_project_id
614 AND Task_id = 0
615 AND Resource_List_member_id = 0 ;
616
617 IF l_prev_accum_period IS NOT NULL THEN
618 -- project previously not accumulated
619
620 BEGIN
621
622 IF x_action_flag = 'BK' OR x_action_flag = 'PR' THEN
623
624 -- Check for Period Moved Backwards (BK) or Forwards (PR)
625
626 IF x_impl_option = 'PA' THEN
627
628 BEGIN
629 SELECT end_date
630 INTO l_prev_accum_end_date
631 FROM pa_periods
632 WHERE period_name = l_prev_accum_period ;
633 EXCEPTION
634 WHEN OTHERS THEN
635 x_ret_flag := 'N' ;
636 RETURN x_ret_flag ;
637 END ;
638
639 ELSIF x_impl_option = 'GL' THEN
640
641 BEGIN
642 SELECT DISTINCT gl_end_date
643 INTO l_prev_accum_end_date
644 FROM pa_periods_v
645 WHERE gl_period_name = l_prev_accum_period ;
646 EXCEPTION
647 WHEN OTHERS THEN
648 x_ret_flag := 'N' ;
649 RETURN x_ret_flag ;
650 END ;
651
652 END IF ;
653
654 IF x_action_flag = 'BK' THEN
655
656 IF( TRUNC(l_prev_accum_end_date) >
657 TRUNC(x_current_end_date) ) THEN
658 x_ret_flag := 'Y' ;
659 END IF ;
660
661 END IF;
662
663 IF x_action_flag = 'PR' THEN
664
665 IF( TRUNC(l_prev_accum_end_date) <
666 TRUNC(x_current_end_date) ) THEN
667 x_ret_flag := 'Y';
668 END IF;
669
670 END IF ;
671
672 END IF ;
673
674 IF x_action_flag = 'PT' THEN
675 -- Check for Period Type Change (PT)
676
677 IF x_impl_option = 'PA' THEN
678
679 BEGIN
680 SELECT 'N'
681 INTO x_ret_flag
682 FROM dual
683 WHERE EXISTS (SELECT 'x'
684 FROM pa_periods
685 WHERE period_name =
686 l_prev_accum_period) ;
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 x_ret_flag := 'Y' ;
690 RETURN x_ret_flag ;
691 WHEN OTHERS THEN
692 x_ret_flag := 'N' ;
693 RETURN x_ret_flag ;
694 END ;
695
696 ELSIF x_impl_option = 'GL' THEN
697
698 BEGIN
699 SELECT 'N'
700 INTO x_ret_flag
701 FROM dual
702 WHERE EXISTS (SELECT 'x'
703 FROM pa_periods_v
704 WHERE gl_period_name =
705 l_prev_accum_period) ;
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 x_ret_flag := 'Y' ;
709 RETURN x_ret_flag ;
710 WHEN OTHERS THEN
711 x_ret_flag := 'N' ;
712 RETURN x_ret_flag ;
713 END ;
714
715 END IF ;
716
717 END IF ;
718
719 IF x_action_flag = 'CL' THEN
720 -- Check for Closed Projects (CL)
721
722 IF (x_closed_date IS NOT NULL)
723 AND ( Pa_Proj_Accum_Main.x_period_yr_start_date IS NOT NULL)
724 AND ( TRUNC(x_closed_date) <
725 TRUNC(Pa_Proj_Accum_Main.x_period_yr_start_date)) THEN
726 x_ret_flag := 'Y' ;
727
728 END IF ;
729
730 END IF ;
731 END ;
732
733 END IF ;
734
735 RETURN x_ret_flag ;
736
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 x_ret_flag := 'N' ;
740 RETURN x_ret_flag ;
741 WHEN OTHERS THEN
742 x_ret_flag := 'N' ;
743 RETURN x_ret_flag ;
744
745 END check_period_flags ;
746
747 PROCEDURE proj_accum
748 ( errbuf IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
749 retcode IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
750 x_project_num_from IN VARCHAR2,
751 x_project_num_to IN VARCHAR2,
752 x_end_date_through IN DATE,
753 x_mode IN VARCHAR2,
754 x_actual_cost_flag IN VARCHAR2,
755 x_system_linkage_function IN VARCHAR2,
756 x_revenue_flag IN VARCHAR2,
757 x_budgets_flag IN VARCHAR2,
758 x_budget_type_code IN VARCHAR2,
759 x_commitments_flag IN VARCHAR2,
760 x_grouping_id IN NUMBER,
761 x_summ_context IN VARCHAR2,
762 x_delete_temp_table IN VARCHAR2,
763 x_project_type IN VARCHAR2 -- for bug 2543021
764 )
765 IS
766
767 /* Added Debug Profile Option variable initialization for bug#2674619 */
768 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
769
770
771
772 x_log_summ_mode VARCHAR2(1) := 'N';
773
774 x_CdlStart DATE := NULL; /* Start time for accum_cdls */
775 x_CdlEnd DATE := NULL; /* End time for accum_cdls */
776 x_RdlStart DATE := NULL; /* Start time for accum_revenue */
777 x_RdlEnd DATE := NULL; /* End time for accum_revenue */
778 x_CmtcrStart DATE := NULL; /* Start time for create_commitments */
779 x_CmtcrEnd DATE := NULL; /* End time for create_commitments */
780 x_CmtaccStart DATE := NULL; /* Start time for accum_commitments */
781 x_CmtaccEnd DATE := NULL; /* End time for accum_commitments */
782 x_InitStart DATE := NULL; /* Start time for Initialize */
783 x_InitEnd DATE := NULL; /* End time for Initialize */
784 x_BudStart DATE := NULL; /* Start time for process_budget_txns */
785 x_BudEnd DATE := NULL; /* End time for process_budget_txns */
786
787 x_MapTxnStart DATE := NULL; /* Start time for Map Txns */
788 x_MapTxnEnd DATE := NULL; /* End time for Map Txns */
789 x_PrcCmtStart DATE := NULL; /* Start time process_txn_accum_cmt */
790 x_PrcCmtEnd DATE := NULL; /* End time process_txn_accum_cmt */
791 x_PrcTxnStart DATE := NULL; /* Start time for process_txn_accum */
792 x_PrcTxnEnd DATE := NULL; /* End time for process_txn_accum */
793 x_PrjSumStart DATE := NULL; /* Start time for Project Summary */
794 x_PrjSumEnd DATE := NULL; /* End time for Project Summary */
795
796 x_InsertStart DATE := NULL; /* Start time for Insert onto */
797 /* pa_projects_for_accum table */
798 x_InsertEnd DATE := NULL; /* End time for Insert onto */
799 /* pa_projects_for_accum table */
800
801 l_accum_period_type_changed BOOLEAN;
802 l_project_num_from VARCHAR2(25);
803 l_project_num_to VARCHAR2(25);
804 l_current_start_date DATE ;
805 l_current_end_date DATE ;
806 l_project_id NUMBER ;
807 l_segment1 VARCHAR2(25);
808 l_sum_exception_code pa_project_accum_headers.sum_exception_code%TYPE := NULL;
809 l_invalid_proj_cnt NUMBER := 0;
810 tmp_bud_type_code VARCHAR2(30);
811 x_actions_in action_tbl_type ;
812 x_actions_cnt BINARY_INTEGER ;
813 table_index BINARY_INTEGER;
814 accumlation_period_changed BOOLEAN;
815
816 -- Declare cursor for Projects
817
818 -- CS is for existance of unaccumulated cdls for that project
819 -- RV is for existance of unaccumulated rdls for the project
820 -- CM is for existance of unaccumulated commitments
821 -- BD is for existance of unaccumulated budgets
822 -- RL is in case, the resource list associated with the
823 -- project has changed
824 -- TR is in case tasks have been restructured for that project
825 -- CL is for closed projects which have not been accumulated before
826 -- BK is when the current reporting period is moved backwards
827 -- PT is when the accumulation period type has changed,
828 -- since the previous accumulation
829 -- PR is when the current reporting period is moved forward.
830 -- TX is when unaccumulated transactions (cdls and rdls)
831 -- exist in pa_txn_accum
832 -- TM is when unaccumulated commitments exist in pa_txn_accum
833
834 CURSOR all_projs IS
835 SELECT DISTINCT pfa.project_id, pfa.segment1
836 FROM pa_projects_for_accum pfa
837 WHERE pfa.request_id = x_request_id
838 AND pfa.segment1
839 BETWEEN l_project_num_from AND l_project_num_to
840 AND pfa.action_flag IN ('CS', 'RV', 'RL', 'CM', 'BD', 'PR', 'TX', 'TM')
841 AND NOT EXISTS (SELECT pf.project_id
842 FROM pa_projects_for_accum pf
843 WHERE pf.request_id = pfa.request_id
844 AND pf.segment1 = pfa.segment1 -- Bug3653978. replaced pfa.project_id with pfa.segment1
845 AND pf.action_flag IN
846 ('PT', 'CL', 'BK', 'TR'));
847 /* Commenting out for performance bug 3653978.
848 AND EXISTS (SELECT pa.project_id -- for bug 2543021
849 FROM pa_projects pa
850 WHERE pa.project_id = pfa.project_id
851 AND pa.project_type = NVL(x_project_type,project_type));
852 */
853 /*Added for bug 5635857*/
854 CURSOR all_cm_projs IS
855 SELECT DISTINCT pfa.project_id
856 FROM pa_projects_for_accum pfa
857 WHERE pfa.request_id = x_request_id
858 AND pfa.segment1
859 BETWEEN l_project_num_from AND l_project_num_to
860 AND pfa.action_flag = 'CM'
861 AND NOT EXISTS (SELECT pf.project_id
862 FROM pa_projects_for_accum pf
863 WHERE pf.request_id = pfa.request_id
864 AND pf.segment1 = pfa.segment1 -- Bug3783746. replaced pf.project_id with pfa.segment1
865 AND pf.action_flag IN
866 ('PT', 'CL', 'BK', 'TR'));
867
868 CURSOR action_found IS
869 SELECT pfa.action_flag
870 FROM pa_projects_for_accum pfa
871 WHERE pfa.segment1 = l_segment1
872 AND pfa.request_id = x_request_id ;
873
874 CURSOR not_valid_proj IS
875 SELECT pfa.project_id, pfa.segment1, pfa.action_flag
876 FROM pa_projects_for_accum pfa
877 WHERE pfa.request_id = x_request_id
878 AND pfa.action_flag IN ('PT', 'CL', 'BK', 'TR')
879 ORDER BY pfa.segment1, pfa.action_flag ;
880
881 CURSOR Get_all_Budget_Types_cur(l_project_id NUMBER) IS
882 SELECT DISTINCT use_code Budget_Type_Code
883 FROM pa_resource_list_uses_v
884 WHERE project_id = l_project_id
885 AND budget_type_yn = 'Y'
886 AND use_code = NVL(Tmp_Bud_Type_Code,use_code);
887
888 CURSOR new_resource_list (l_project_id NUMBER) IS
889 SELECT DISTINCT resource_list_id
890 FROM pa_resource_list_assignments
891 WHERE project_id = l_project_id
892 AND NVL(resource_list_accumulated_flag,'N') = 'N';
893
894 /* Added for bug 1751445 Cause Performance */
895 /*Commented for perf bug #3672175 and redefined below */
896 /* CURSOR all_valid_proj IS
897 SELECT DISTINCT proj.project_id, proj.segment1
898 FROM pa_projects_for_accum_v proj
899 WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
900 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
901 AND NOT EXISTS (SELECT 1
902 FROM pa_projects_for_accum proj_invalid
903 WHERE proj_invalid.project_id = proj.project_id
904 AND proj_invalid.action_flag IN ('PT','CL','BK','TR')
905 AND proj_invalid.request_id+0 = x_request_id);*/
906 CURSOR all_valid_proj IS
907 SELECT
908 proj.project_id ,
909 proj.segment1 ,proj.project_type
910 FROM pa_projects proj
911 WHERE proj.segment1 BETWEEN pa_accum_utils.Get_project_info('F')
912 AND pa_accum_utils.Get_project_info('T')
913 AND pa_accum_utils.Get_context_info = 'REGULAR'
914 AND NVL(proj.template_flag,'N') <> 'Y'
915 AND proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
916 AND proj.project_type = NVL(x_project_type, project_type)
917 AND NOT EXISTS (SELECT 1
918 FROM pa_projects_for_accum proj_invalid
919 WHERE proj_invalid.project_id = proj.project_id
920 AND proj_invalid.action_flag IN ('PT','CL','BK','TR')
921 AND proj_invalid.request_id = x_request_id)
922 UNION
923 SELECT proj.project_id , proj.segment1 ,proj.project_type
924 FROM pa_projects proj
925 WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
926 AND EXISTS ( select 1 from pa_alloc_txn_details alloc
927 where alloc.project_id = proj.project_id
928 AND alloc.run_id = pa_accum_utils.Get_grouping_id)
929 AND pa_accum_utils.Get_context_info = 'AUTO_ALLOCATION'
930 AND proj.project_type = NVL(x_project_type, project_type)
931 AND NOT EXISTS (SELECT 1
932 FROM pa_projects_for_accum proj_invalid
933 WHERE proj_invalid.project_id = proj.project_id
934 AND proj_invalid.action_flag IN ('PT','CL','BK','TR')
935 AND proj_invalid.request_id = x_request_id); /*Code redefined for bug#3672175 ends here*/
936 filter_proj not_valid_proj%ROWTYPE ;
937 valid_proj all_projs%ROWTYPE ;
938 valid_action action_found%ROWTYPE ;
939
940 budget_type_rec Get_all_Budget_Types_cur%ROWTYPE;
941 new_resource_list_rec new_resource_list%ROWTYPE;
942 x_date_through DATE;
943
944 l_project_id_tbl pa_plsql_datatypes.idTabTyp; /*Added for bug 5635857*/
945
946 BEGIN
947
948 x_old_stack := x_err_stack;
949 x_err_stack := x_err_stack ||'->pa_proj_accum_main.Proj_Accum';
950 x_err_code := 0;
951
952 x_summ_process := 'UP';
953 x_err_stage := 'Project Accumulation Process';
954 x_log_summ_mode := Fnd_Profile.value('PA_LOG_SUMM_MODE');
955
956 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
957 Pa_Debug.debug('proj_accum: ' || x_err_stack);
958 END IF;
959
960 -- Grants Management Integrated Commitment Processing ---------------------
961 -- added 30-MAY-2003, jwhite
962
963 G_GMS_Enabled := GMS_PA_API3.grants_enabled;
964
965 IF ( P_DEBUG_MODE = 'Y' )
966 THEN
967 Pa_Debug.debug('proj_accum: G_GMS_Enabled = ' || G_GMS_Enabled);
968 END IF;
969
970
971 -- -------------------------------------------------------------------------
972
973
974
975 -- Validate Parameters
976 Get_Project_Num_Range( x_project_num_from,
977 x_project_num_to,
978 l_project_num_from,
979 l_project_num_to,
980 x_project_type );
981
982 IF NVL(x_actual_cost_flag, 'N') = 'N'
983 AND NVL(x_revenue_flag, 'N') = 'N'
984 AND NVL(x_budgets_flag, 'N') = 'N'
985 AND NVL(x_commitments_flag, 'N') = 'N' THEN
986
987 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
988 Pa_Debug.debug('proj_accum: ' || 'Nothing selected for accumulation');
989 END IF;
990 errbuf := 'Nothing selected for accumulation ';
991 retcode := 2;
992 RETURN;
993 END IF;
994 IF (x_mode <> 'I' ) THEN
995
996 errbuf := 'Please Select Accumulation mode as (I)ncremental';
997 retcode := 3;
998 RETURN;
999 END IF;
1000
1001 -- assume the process does not return an error
1002
1003 retcode :=0;
1004
1005 -- all the parameters are validated now
1006
1007 -- Call the Initialization procedure
1008
1009 Pa_Proj_Accum_Main.initialize;
1010
1011 IF x_impl_option = 'PA' THEN
1012 l_current_start_date := x_current_pa_start_date ;
1013 l_current_end_date := x_current_pa_end_date ;
1014 ELSIF x_impl_option = 'GL' THEN
1015 l_current_start_date := x_current_gl_start_date ;
1016 l_current_end_date := x_current_gl_end_date ;
1017 END IF ;
1018
1019 -- Value for Global variables G_start_proj, G_end_proj, G_context
1020 -- and G_grouping_id being set
1021 Pa_Accum_Utils.G_start_proj := l_project_num_from;
1022 Pa_Accum_Utils.G_end_proj := l_project_num_to;
1023 Pa_Accum_Utils.G_context := x_summ_context;
1024 Pa_Accum_Utils.G_grouping_id := x_grouping_id;
1025
1026 -- inserts records onto pa_projects_for_accum
1027
1028 BEGIN
1029
1030 DELETE FROM pa_projects_for_accum pfa
1031 WHERE pfa.request_id = x_request_id
1032 AND pfa.segment1
1033 BETWEEN l_project_num_from AND l_project_num_to ;
1034
1035 /* Bug1751445 Replaced union with union all. Also commented out call to pa_check_commitments */
1036
1037 INSERT INTO pa_projects_for_accum
1038 (project_id, request_id, action_flag, segment1, exception_flag)
1039 SELECT proj.project_id, x_request_id request_id,
1040 'CS' action_flag, proj.segment1, 'N'
1041 FROM pa_projects_for_accum_v proj
1042 WHERE proj.segment1
1043 BETWEEN l_project_num_from AND l_project_num_to
1044 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1045 AND x_actual_cost_flag = 'Y'
1046 AND EXISTS (SELECT 'x'
1047 FROM pa_cost_distribution_lines_all cdl
1048 WHERE cdl.project_id = proj.project_id
1049 AND (cdl.line_type = 'R' OR
1050 ( cdl.line_type = 'I' and Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening = 'Y')
1051 ) /*4278940 */
1052 AND cdl.resource_accumulated_flag='N')
1053 UNION ALL
1054 SELECT proj.project_id, x_request_id request_id,
1055 'RV' action_flag, proj.segment1, 'N'
1056 FROM pa_projects_for_accum_v proj
1057 WHERE proj.segment1
1058 BETWEEN l_project_num_from AND l_project_num_to
1059 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1060 AND x_revenue_flag = 'Y'
1061 AND EXISTS (SELECT 'x'
1062 FROM pa_draft_revenues dr
1063 WHERE dr.project_id = proj.project_id
1064 AND NVL(dr.resource_accumulated_flag,'S') =
1065 DECODE(x_mode,'I', 'S', 'F',
1066 NVL(dr.resource_accumulated_flag,'S'),'S')
1067 AND dr.released_date IS NOT NULL)
1068 UNION ALL
1069 SELECT proj.project_id, x_request_id request_id,
1070 'RL' action_flag, proj.segment1, 'N'
1071 FROM pa_projects_for_accum_v proj
1072 WHERE proj.segment1
1073 BETWEEN l_project_num_from AND l_project_num_to
1074 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1075 AND EXISTS (SELECT 'x'
1076 FROM pa_resource_list_assignments rla
1077 WHERE rla.project_id = proj.project_id
1078 AND NVL(rla.resource_list_accumulated_flag,
1079 'N') = 'N')
1080 /* UNION ALL
1081 SELECT proj.project_id, x_request_id request_id,
1082 'CM' action_flag, proj.segment1, 'N'
1083 FROM pa_projects_for_accum_v proj
1084 WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
1085 AND x_commitments_flag = 'Y'
1086 AND PA_CHECK_COMMITMENTS.commitments_changed(proj.project_id) = 'Y' */
1087 UNION ALL
1088 SELECT proj.project_id, x_request_id request_id,
1089 'BD' action_flag, proj.segment1, 'N'
1090 FROM pa_projects_for_accum_v proj
1091 WHERE proj.segment1
1092 BETWEEN l_project_num_from AND l_project_num_to
1093 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1094 AND x_budgets_flag = 'Y'
1095 AND EXISTS (SELECT 'x'
1096 FROM pa_budget_versions bud
1097 WHERE bud.project_id = proj.project_id
1098 AND bud.current_flag = 'Y'
1099 AND bud.resource_accumulated_flag = 'N')
1100 UNION ALL
1101 SELECT proj.project_id, x_request_id request_id,
1102 'TR' action_flag, proj.segment1, 'Y'
1103 FROM pa_projects_for_accum_v proj
1104 WHERE proj.segment1
1105 BETWEEN l_project_num_from AND l_project_num_to
1106 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1107 AND EXISTS (SELECT 'x'
1108 FROM pa_project_accum_headers pah
1109 WHERE pah.project_id = proj.project_id
1110 AND NVL(pah.tasks_restructured_flag, 'N') = 'Y')
1111 UNION ALL
1112 SELECT proj.project_id, x_request_id request_id,
1113 'PR' action_flag, proj.segment1, 'N'
1114 FROM pa_projects_for_accum_v proj
1115 WHERE proj.segment1
1116 BETWEEN l_project_num_from AND l_project_num_to
1117 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1118 AND Pa_Proj_Accum_Main.check_period_flags
1119 (proj.project_id, x_impl_option, l_current_end_date, 'PR',
1120 proj.closed_date, l_current_start_date) = 'Y'
1121 UNION ALL
1122 SELECT proj.project_id, x_request_id request_id,
1123 'BK' action_flag, proj.segment1, 'Y'
1124 FROM pa_projects_for_accum_v proj
1125 WHERE proj.segment1
1126 BETWEEN l_project_num_from AND l_project_num_to
1127 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1128 AND Pa_Proj_Accum_Main.check_period_flags
1129 (proj.project_id, x_impl_option, l_current_end_date, 'BK',
1130 proj.closed_date, l_current_start_date) = 'Y'
1131 UNION ALL
1132 SELECT proj.project_id, x_request_id request_id,
1133 'CL' action_flag, proj.segment1, 'N'
1134 FROM pa_projects_for_accum_v proj
1135 WHERE proj.segment1
1136 BETWEEN l_project_num_from AND l_project_num_to
1137 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1138 AND Pa_Proj_Accum_Main.check_period_flags
1139 (proj.project_id, x_impl_option, l_current_end_date, 'CL',
1140 proj.closed_date, l_current_start_date) = 'Y'
1141 UNION ALL
1142 SELECT proj.project_id, x_request_id request_id,
1143 'PT' action_flag, proj.segment1, 'Y'
1144 FROM pa_projects_for_accum_v proj
1145 WHERE proj.segment1
1146 BETWEEN l_project_num_from AND l_project_num_to
1147 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1148 AND Pa_Proj_Accum_Main.check_period_flags
1149 (proj.project_id, x_impl_option, l_current_end_date, 'PT',
1150 proj.closed_date, l_current_start_date) = 'Y'
1151 UNION ALL
1152 SELECT proj.project_id, x_request_id request_id, 'TX' action_flag,
1153 proj.segment1, 'N'
1154 FROM pa_projects_for_accum_v proj
1155 WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
1156 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1157 AND (x_actual_cost_flag = 'Y' OR x_revenue_flag = 'Y')
1158 AND EXISTS (SELECT 'x'
1159 FROM pa_txn_accum txn
1160 WHERE txn.actual_cost_rollup_flag = 'Y'
1161 AND txn.project_id = proj.project_id
1162 AND x_actual_cost_flag = 'Y'
1163 UNION ALL
1164 SELECT 'x'
1165 FROM pa_txn_accum txn_r
1166 WHERE txn_r.revenue_rollup_flag = 'Y'
1167 AND txn_r.project_id = proj.project_id
1168 AND x_revenue_flag = 'Y')
1169 UNION ALL
1170 SELECT proj.project_id, x_request_id request_id, 'TM' action_flag,
1171 proj.segment1, 'N'
1172 FROM pa_projects_for_accum_v proj
1173 WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
1174 AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
1175 AND x_commitments_flag = 'Y'
1176 AND EXISTS (SELECT 'x'
1177 FROM pa_txn_accum txn_m
1178 WHERE txn_m.cmt_rollup_flag = 'Y'
1179 AND txn_m.project_id = proj.project_id
1180 AND x_commitments_flag = 'Y')
1181 ;
1182
1183 /* Added for Bug 1751445 */
1184
1185 /* Added for bug 5353471 */
1186
1187 IF x_commitments_flag = 'Y' THEN
1188
1189 FOR recs IN all_valid_proj LOOP
1190
1191 INSERT INTO pa_projects_for_accum
1192 (project_id, request_id, action_flag, segment1, exception_flag)
1193 SELECT recs.project_id, x_request_id, 'CM', recs.segment1, 'N'
1194 FROM dual
1195 WHERE Pa_Check_Commitments.commitments_changed(recs.project_id) = 'Y';
1196
1197 END LOOP;
1198
1199 END IF;
1200
1201 COMMIT WORK ;
1202
1203 EXCEPTION
1204 WHEN NO_DATA_FOUND THEN
1205 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1206 Pa_Debug.debug('proj_accum: ' || 'No Records to be Summarized');
1207 END IF;
1208 NULL ;
1209 WHEN OTHERS THEN
1210 x_err_code := SQLCODE;
1211 RAISE;
1212 END ;
1213 -- end of insert pa_projects_for_accum
1214
1215 x_date_through := x_end_date_through;
1216 IF x_end_date_through >= x_current_pa_end_date OR x_end_date_through IS NULL THEN
1217 x_date_through := x_end_pa_date;
1218 END IF;
1219
1220 -- select only required projects and not all
1221
1222 BEGIN
1223
1224 FOR filter_proj IN not_valid_proj LOOP
1225
1226 Pa_Proj_Accum_Main.lock_proj_level_header_record
1227 (filter_proj.project_id);
1228
1229 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1230 Pa_Debug.debug('proj_accum: ' || '**** PLEASE REFRESH THE PROJECT '
1231 || filter_proj.segment1 || ' ****',
1232 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1233 END IF;
1234
1235 IF filter_proj.action_flag = 'PT' THEN
1236 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1237 Pa_Debug.debug('proj_accum: ' || '***** THE ACCUMULATION PERIOD ' ||
1238 'TYPE HAS CHANGED SINCE THE LAST TIME '||
1239 'THE PROJECT WAS ACCUMULATED *****',
1240 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1241 END IF;
1242 errbuf := 'Accumulation Period Type has changed' ;
1243 l_sum_exception_code := 'PA_SUM_ACCUM_PERIOD_CHANGED';
1244
1245 ELSIF filter_proj.action_flag = 'TR' THEN
1246 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1247 Pa_Debug.debug('proj_accum: ' || '************* TASKS ARE ' ||
1248 'RESTRUCTURED FOR THE PROJECT *************',
1249 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1250 END IF;
1251 errbuf := 'Tasks have been Restructured' ;
1252 l_sum_exception_code := 'PA_SUM_TASKS_RESTRUCTURED';
1253
1254 ELSIF filter_proj.action_flag = 'BK' THEN
1255 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1256 Pa_Debug.debug('proj_accum: ' || '********** REPORTING PERIOD ' ||
1257 'WAS MOVED BACKWARDS IN TIME **********',
1258 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1259 END IF;
1260 errbuf := 'Reporting Period has moved backwards' ;
1261 l_sum_exception_code := 'PA_SUM_RPT_PERIOD_BACKWARD';
1262
1263 ELSIF filter_proj.action_flag = 'CL' THEN
1264 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1265 Pa_Debug.debug('proj_accum: ' || '****** PROJECT IS CLOSED AND ' ||
1266 'NOT ELIGIBLE FOR ACCUMULATION *******',
1267 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1268 END IF;
1269 errbuf := 'Project is closed' ;
1270 l_sum_exception_code := 'PA_SUM_PROJECT_CLOSED';
1271
1272 END IF ;
1273 IF (x_proj_header_locked = TRUE) THEN
1274 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1275 Pa_Debug.debug('proj_accum: ' || 'Cannot update summarization exception for project'||
1276 filter_proj.segment1,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1277 END IF;
1278 ELSE
1279 UPDATE pa_project_accum_headers
1280 SET sum_exception_code = l_sum_exception_code
1281 WHERE project_id = filter_proj.project_id
1282 AND task_id = 0
1283 AND resource_list_member_id = 0;
1284
1285 l_invalid_proj_cnt := l_invalid_proj_cnt + 1;
1286 END IF;
1287
1288 END LOOP ;
1289
1290 IF (l_invalid_proj_cnt > 0) THEN
1291 COMMIT WORK;
1292 END IF;
1293
1294 EXCEPTION
1295 WHEN NO_DATA_FOUND THEN
1296 NULL ;
1297 WHEN OTHERS THEN
1298 x_err_code := SQLCODE;
1299 DELETE FROM pa_projects_for_accum
1300 WHERE request_id = x_request_id ;
1301 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1302 Pa_Debug.debug('proj_accum: ' || 'Exception Generated By Oracle Error: ' ||
1303 errbuf,Pa_Debug.DEBUG_LEVEL_EXCEPTION );
1304 Pa_Debug.debug('proj_accum: ' || 'Skipping the accumulation for project ' ||
1305 filter_proj.segment1,
1306 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
1307 END IF;
1308 RAISE;
1309
1310 END ;
1311
1312 -- end of select valid projects
1313 --Bug 5635857. Select the pending commitments for eligible projects and insert them
1314 --into the temoporary table pa_commitment_txns_tmp so that they can be used later
1315 -- in the code
1316 --Note that if the below condition is changed then the code for updating the commitments
1317 --should also be changed in the FOR loop
1318
1319 /* Modified for Bug#6408874 - START */
1320
1321 IF x_commitments_flag = 'Y' THEN
1322
1323
1324 populate_cmt_tmp_table( p_project_num_from => l_project_num_from,
1325 p_project_num_to => l_project_num_to,
1326 p_system_linkage_function => x_system_linkage_function,
1327 p_refresh_flag => 'N', -- for Bug# 7175975
1328 p_project_type => NULL); -- for Bug# 7175975
1329
1330 END IF;
1331 /* Modified for Bug#6408874 - END */
1332
1333 FOR valid_proj IN all_projs LOOP
1334
1335 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1336 Pa_Debug.debug('proj_accum: ' || 'Processing Project Id = ' ||
1337 TO_CHAR(valid_proj.project_id));
1338 END IF;
1339 tmp_bud_type_code := x_budget_type_code;
1340 ------------Acquire lock on the project-----------
1341 IF Pa_Debug.acquire_user_lock('PA_SUMM:'||TO_CHAR(valid_proj.project_id)) <> 0 THEN
1342 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1343 Pa_Debug.debug('proj_accum: ' || 'Could not acquire lock on the Project. Skipping ...');
1344 END IF;
1345 GOTO skip_project;
1346 ELSE
1347 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1348 Pa_Debug.debug('proj_accum: ' || 'Locking the project for summarization ...');
1349 END IF;
1350 END IF;
1351 -------------------------------------------------
1352
1353 l_project_id := valid_proj.project_id ;
1354 l_segment1 := valid_proj.segment1 ;
1355 x_actions_cnt := 0 ;
1356 x_actions_in.DELETE ; -- Initializing the PL/SQL table
1357
1358 -- Assign values onto the PL/SQL table for this project
1359
1360 FOR valid_action IN action_found LOOP
1361 x_actions_cnt := x_actions_cnt + 1 ;
1362 x_actions_in(x_actions_cnt).action_flag :=
1363 valid_action.action_flag ;
1364 END LOOP ;
1365
1366 Initialize_Project(l_project_id,
1367 l_accum_period_type_changed) ;
1368
1369 --Bug 5060439. Null out the sum_exception_code. It might have got populated in the
1370 --previous run
1371 UPDATE pa_project_accum_headers
1372 SET sum_exception_code=NULL
1373 WHERE project_accum_id=x_proj_level_accum_id;
1374
1375
1376 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1377 Pa_Debug.debug('proj_accum: ' || 'Refreshing new resource lists assigned '||
1378 'to the project, If any');
1379 END IF;
1380
1381 IF Pa_Proj_Accum_Main.action_exists
1382 (x_action_flag => 'RL',
1383 x_actions_in => x_actions_in,
1384 x_actions_cnt => x_actions_cnt) = 'Y' THEN
1385
1386 FOR new_resource_list_rec IN
1387 new_resource_list(l_project_id) LOOP
1388 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1389 Pa_Debug.debug('proj_accum: ' || 'Refreshing resource list id = ' ||
1390 TO_CHAR(new_resource_list_rec.resource_list_id) );
1391 END IF;
1392
1393 IF ( x_prev_accum_period IS NOT NULL ) THEN
1394
1395 -- Create resource Map
1396 Pa_Res_Accums.map_txns
1397 (l_project_id,
1398 new_resource_list_rec.resource_list_id,
1399 'F', -- x_mode = FULL
1400 x_err_stage,
1401 x_err_code);
1402
1403 -- refresh the resource list upto
1404 -- last accumulation period
1405 x_summ_process := 'RL';
1406 Pa_Maint_Project_Accums.process_txn_accum
1407 (l_project_id,
1408 x_impl_option,
1409 x_proj_level_accum_id,
1410 x_prev_accum_period,
1411 x_prev_prev_accum_period,
1412 x_prev_year,
1413 x_prev_accum_period,
1414 x_prev_accum_start_date,
1415 x_prev_accum_end_date,
1416 'Y',
1417 'Y',
1418 'Y',
1419 new_resource_list_rec.resource_list_id,
1420 x_err_stack,
1421 x_err_stage,
1422 x_err_code) ;
1423 x_summ_process := 'UP';
1424
1425 -- pa_refresh_res_lists.process_res_lists
1426 -- (l_project_id,
1427 -- new_resource_list_rec.resource_list_id,
1428 -- x_prev_accum_period,
1429 -- x_prev_prev_accum_period,
1430 -- x_prev_year,
1431 -- x_prev_accum_start_date,
1432 -- x_prev_accum_end_date,
1433 -- x_err_stack,
1434 -- x_err_stage,
1435 -- x_err_code);
1436 END IF;
1437
1438 END LOOP;
1439
1440 END IF ;
1441
1442 x_summ_process := 'UP';
1443 Pa_Txn_Accums.get_accum_configurations
1444 (l_project_id,
1445 x_err_stage,
1446 x_err_code);
1447
1448 -- Get the summary number from database tables to
1449 -- PL/SQL table
1450
1451 table_index := Pa_Accum_Srw.number_of_projects + 1 ;
1452 Pa_Accum_Srw.get_project_summary_numbers
1453 (x_proj_level_accum_id,
1454 'PRE',
1455 table_index,
1456 x_err_stack,
1457 x_err_stage,
1458 x_err_code) ;
1459 -- Get project Budgets
1460 Pa_Accum_Srw.get_project_budget_numbers
1461 (x_proj_level_accum_id,
1462 'PRE',
1463 x_err_stack,
1464 x_err_stage,
1465 x_err_code) ;
1466
1467 IF (x_actual_cost_flag = 'Y' AND
1468 (Pa_Proj_Accum_Main.action_exists
1469 (x_action_flag => 'CS',
1470 x_actions_in => x_actions_in,
1471 x_actions_cnt => x_actions_cnt) = 'Y')) THEN
1472
1473 -- incrementally accumulate actuals txns to PA_TXN_ACCUM
1474 IF x_log_summ_mode = 'Y' THEN
1475 x_CdlStart := SYSDATE; /* Start time for CDLs */
1476 END IF;
1477
1478 Pa_Txn_Accums.accum_cdls
1479 (l_project_id, -- start_project_id
1480 l_project_id, -- end_project_id
1481 x_start_pa_date, -- start_pa_period
1482 x_date_through, -- end_pa_period
1483 x_system_linkage_function,
1484 x_mode,
1485 'R',
1486 x_err_stage,
1487 x_err_code) ;
1488
1489 -- 05-AUG-2003, jwhite: For patchset 'L' Reburdening Enhancement
1490 IF (Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening = 'Y')
1491 THEN
1492
1493 IF ( P_DEBUG_MODE = 'Y' )
1494 THEN
1495 Pa_Debug.debug('proj_accum: Call Accum_Cdls with x_cdl_line_type = I' );
1496 END IF;
1497
1498
1499 Pa_Txn_Accums.accum_cdls
1500 (l_project_id, -- start_project_id
1501 l_project_id, -- end_project_id
1502 x_start_pa_date, -- start_pa_period
1503 x_date_through, -- end_pa_period
1504 x_system_linkage_function,
1505 x_mode,
1506 'I',
1507 x_err_stage,
1508 x_err_code) ;
1509 END IF;
1510 -- --------------------------------------------------------------
1511
1512 IF x_log_summ_mode = 'Y' THEN
1513 x_CdlEnd := SYSDATE; /* End time for CDLs */
1514 END IF;
1515
1516 END IF ;
1517
1518 IF (x_revenue_flag = 'Y' AND
1519 (Pa_Proj_Accum_Main.action_exists
1520 (x_action_flag => 'RV',
1521 x_actions_in => x_actions_in,
1522 x_actions_cnt => x_actions_cnt) = 'Y')) THEN
1523
1524 IF x_log_summ_mode = 'Y' THEN
1525 x_RdlStart := SYSDATE; /* Start time for RDLs */
1526 END IF;
1527 Pa_Txn_Accums.accum_revenue
1528 (l_project_id, -- start_project_id
1529 l_project_id, -- end_project_id
1530 x_start_pa_date, -- start_pa_period
1531 x_date_through, -- end_pa_period
1532 x_mode,
1533 x_err_stage,
1534 x_err_code) ;
1535 IF x_log_summ_mode = 'Y' THEN
1536 x_RdlEnd := SYSDATE; /* End time for RDLs */
1537 END IF;
1538
1539 END IF ;
1540
1541 IF (x_commitments_flag = 'Y' AND
1542 (Pa_Proj_Accum_Main.action_exists
1543 (x_action_flag => 'CM',
1544 x_actions_in => x_actions_in,
1545 x_actions_cnt => x_actions_cnt) = 'Y')) THEN
1546
1547 Pa_Delete_Accum_Recs.delete_project_commitments
1548 (l_project_id,
1549 x_err_stack,
1550 x_err_stage,
1551 x_err_code) ;
1552 -- Initialize the commitment figures in
1553 -- pa_txn_accum to Zero for commitments
1554
1555 Pa_Txn_Accums.refresh_txn_accum
1556 (l_project_id, -- start_project_id
1557 l_project_id, -- end_project_id
1558 x_start_pa_date, -- start_pa_period
1559 x_end_pa_date, -- end_pa_period
1560 'M', -- Txn_type for Commitments
1561 x_system_linkage_function,
1562 x_err_stage,
1563 x_err_code) ;
1564
1565 -- Create new commitments in the pa_commitment_txns
1566 IF x_log_summ_mode = 'Y' THEN
1567 x_CmtcrStart := SYSDATE; --Start time for CMT creation
1568 END IF;
1569 Pa_Txn_Accums.create_cmt_txns
1570 (l_project_id, -- start_project_id
1571 l_project_id, -- end_project_id
1572 x_system_linkage_function,
1573 x_err_stage,
1574 x_err_code,
1575 'Y' ) ; --x_use_tmp_table bug 5635857
1576 IF x_log_summ_mode = 'Y' THEN
1577 x_CmtcrEnd := SYSDATE; --End time for CMT creation
1578 END IF;
1579
1580 -- Create summarized burden commitment transactions
1581 Pa_Burden_Costing.create_burden_cmt_transaction
1582 (x_project_id=>l_project_id, /* added for bug 2734747 */
1583 status=>x_status,
1584 stage=>x_stage ,
1585 x_run_id=>x_run_id);
1586
1587 -- accumulate the commitments from
1588 -- pa_commitment_txns to pa_txn_accum
1589 IF x_log_summ_mode = 'Y' THEN
1590 x_CmtaccStart := SYSDATE; /* Start time for CMT accum */
1591 END IF;
1592 Pa_Txn_Accums.accum_commitments
1593 (l_project_id, -- start_project_id
1594 l_project_id, -- end_project_id
1595 x_system_linkage_function,
1596 x_err_stage,
1597 x_err_code) ;
1598 IF x_log_summ_mode = 'Y' THEN
1599 x_CmtaccEnd := SYSDATE; /* End time for CMT accum */
1600 END IF;
1601
1602 END IF;
1603
1604 IF Pa_Proj_Accum_Main.action_exists
1605 (x_action_flag => 'PR',
1606 x_actions_in => x_actions_in,
1607 x_actions_cnt => x_actions_cnt) = 'Y' THEN
1608
1609 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1610 Pa_Debug.debug('proj_accum: ' || 'Accumulation Period has changed'||
1611 ', since last accumulation');
1612 END IF;
1613
1614 -- Initialize summary level numbers
1615 accumlation_period_changed := TRUE ;
1616
1617 IF x_log_summ_mode = 'Y' THEN
1618 x_InitStart := SYSDATE; --Initialize actuals
1619 END IF;
1620 Pa_Maint_Project_Accums.initialize_actuals
1621 (l_project_id,
1622 x_proj_level_accum_id,
1623 x_impl_option,
1624 x_current_period,
1625 x_prev_period,
1626 x_prev_accum_period,
1627 x_current_year,
1628 x_prev_year,
1629 x_prev_accum_year,
1630 x_current_start_date,
1631 x_current_end_date,
1632 x_prev_start_date,
1633 x_prev_end_date,
1634 x_prev_accum_start_date,
1635 x_prev_accum_end_date,
1636 x_err_stack,
1637 x_err_stage,
1638 x_err_code) ;
1639 IF x_log_summ_mode = 'Y' THEN
1640 x_InitEnd := SYSDATE; /* Initialize actuals, CMTs */
1641 END IF;
1642
1643 -- Commitments will be initialized only if :
1644 -- Commitments have not been asked to be processed by the user
1645 -- Or Commitments have been asked to be processed and there are
1646 -- no new commitments to be processed
1647
1648 -- IF (x_commitments_flag <> 'Y')
1649 -- OR (x_commitments_flag = 'Y' AND
1650 -- (pa_proj_accum_main.action_exists
1651 -- (x_action_flag => 'CM',
1652 -- x_actions_in => x_actions_in,
1653 -- x_actions_cnt => x_actions_cnt) <> 'Y' AND
1654 -- pa_proj_accum_main.action_exists
1655 -- (x_action_flag => 'TM',
1656 -- x_actions_in => x_actions_in,
1657 -- x_actions_cnt => x_actions_cnt) <> 'Y')) THEN
1658
1659 -- -- initialize commitment figures
1660 -- pa_maint_project_accums.initialize_commitments
1661 -- (l_project_id,
1662 -- x_proj_level_accum_id,
1663 -- x_impl_option,
1664 -- x_current_period,
1665 -- x_prev_period,
1666 -- x_prev_accum_period,
1667 -- x_current_year,
1668 -- x_prev_year,
1669 -- x_prev_accum_year,
1670 -- x_current_start_date,
1671 -- x_current_end_date,
1672 -- x_prev_start_date,
1673 -- x_prev_end_date,
1674 -- x_prev_accum_start_date,
1675 -- x_prev_accum_end_date,
1676 -- x_err_stack,
1677 -- x_err_stage,
1678 -- x_err_code) ;
1679 -- IF x_log_summ_mode = 'Y' THEN
1680 -- x_InitEnd := SYSDATE; /* Initialize actuals */
1681 -- END IF;
1682
1683 -- END IF ;
1684
1685
1686 -- we will ignore the input budget type code
1687 -- in this case
1688 tmp_bud_type_code := NULL ;
1689
1690 END IF ;
1691
1692 IF Pa_Proj_Accum_Main.action_exists
1693 (x_action_flag => 'PR',
1694 x_actions_in => x_actions_in,
1695 x_actions_cnt => x_actions_cnt) = 'Y'
1696 OR (x_budgets_flag = 'Y' AND
1697 Pa_Proj_Accum_Main.action_exists
1698 (x_action_flag => 'BD',
1699 x_actions_in => x_actions_in,
1700 x_actions_cnt => x_actions_cnt) = 'Y') THEN
1701
1702 -- Mark all budgets to be accumulated
1703 -- When the accumulation period is changed
1704
1705 UPDATE Pa_Budget_Versions
1706 SET Resource_Accumulated_Flag ='N'
1707 WHERE Project_id = l_project_id
1708 AND (Current_Flag = 'Y' OR current_original_flag = 'Y');
1709
1710 Pa_Delete_Accum_Recs.delete_project_budgets
1711 (l_project_id,
1712 tmp_bud_type_code,
1713 x_err_stack,
1714 x_err_stage,
1715 x_err_code) ;
1716
1717
1718 -- Process the transactions by reading
1719 -- PA_BUDGET_BY_PA_PERIOD_V
1720
1721 IF x_log_summ_mode = 'Y' THEN
1722 -- Start time for Process Budget Txns
1723 x_BudStart := SYSDATE;
1724 END IF;
1725
1726 Pa_Maint_Project_Budgets.process_budget_txns
1727 (l_project_id,
1728 x_impl_option,
1729 x_proj_level_accum_id,
1730 tmp_bud_type_code,
1731 x_current_period,
1732 x_prev_period,
1733 x_current_year,
1734 x_prev_accum_period,
1735 x_current_start_date,
1736 x_current_end_date,
1737 x_err_stack,
1738 x_err_stage,
1739 x_err_code) ;
1740
1741 IF x_log_summ_mode = 'Y' THEN
1742 -- End time for Process Budget Txns
1743 x_BudEnd := SYSDATE;
1744 END IF;
1745
1746 END IF ;
1747
1748 Pa_Accum_Utils.update_proj_accum_header
1749 (x_proj_level_accum_id,
1750 x_current_period,
1751 x_err_stack,
1752 x_err_stage,
1753 x_err_code);
1754
1755 IF Pa_Proj_Accum_Main.action_exists
1756 (x_action_flag => 'CS',
1757 x_actions_in => x_actions_in,
1758 x_actions_cnt => x_actions_cnt) = 'Y'
1759 OR Pa_Proj_Accum_Main.action_exists
1760 (x_action_flag => 'RV',
1761 x_actions_in => x_actions_in,
1762 x_actions_cnt => x_actions_cnt) = 'Y'
1763 OR Pa_Proj_Accum_Main.action_exists
1764 (x_action_flag => 'CM',
1765 x_actions_in => x_actions_in,
1766 x_actions_cnt => x_actions_cnt) = 'Y'
1767 OR Pa_Proj_Accum_Main.action_exists
1768 (x_action_flag => 'TM',
1769 x_actions_in => x_actions_in,
1770 x_actions_cnt => x_actions_cnt) = 'Y'
1771 OR Pa_Proj_Accum_Main.action_exists
1772 (x_action_flag => 'TX',
1773 x_actions_in => x_actions_in,
1774 x_actions_cnt => x_actions_cnt) = 'Y' THEN
1775
1776 IF x_log_summ_mode = 'Y' THEN
1777 x_MapTxnStart := SYSDATE; --Start time for Map Txns
1778 END IF;
1779 Pa_Res_Accums.map_txns
1780 (l_project_id,
1781 NULL, -- resource_list_id
1782 x_mode,
1783 x_err_stage,
1784 x_err_code) ;
1785 IF x_log_summ_mode = 'Y' THEN
1786 x_MapTxnEnd := SYSDATE; --End time for Map Txns
1787 END IF;
1788
1789 -- IF (pa_proj_accum_main.action_exists
1790 -- (x_action_flag => 'CM',
1791 -- x_actions_in => x_actions_in,
1792 -- x_actions_cnt => x_actions_cnt) = 'Y'
1793 -- AND x_commitments_flag = 'Y')
1794 -- OR pa_proj_accum_main.action_exists
1795 -- (x_action_flag => 'PR',
1796 -- x_actions_in => x_actions_in,
1797 -- x_actions_cnt => x_actions_cnt) = 'Y'
1798 -- OR (pa_proj_accum_main.action_exists
1799 -- (x_action_flag => 'TM',
1800 -- x_actions_in => x_actions_in,
1801 -- x_actions_cnt => x_actions_cnt) = 'Y'
1802 -- AND x_commitments_flag = 'Y')
1803 -- THEN
1804 --
1805 -- -- rollup the commitments from pa_txn_accum to
1806 -- -- pa_project_accum_commitments
1807 -- IF x_log_summ_mode = 'Y' THEN
1808 -- -- Start time for Process txn accum cmt
1809 -- x_PrcCmtStart := SYSDATE;
1810 -- END IF;
1811 -- pa_maint_project_commitments.process_txn_accum_cmt
1812 -- (l_project_id,
1813 -- x_impl_option,
1814 -- x_proj_level_accum_id,
1815 -- x_current_period,
1816 -- x_prev_period,
1817 -- x_current_year,
1818 -- x_prev_accum_period,
1819 -- x_current_start_date,
1820 -- x_current_end_date,
1821 -- x_err_stack,
1822 -- x_err_stage,
1823 -- x_err_code) ;
1824 -- IF x_log_summ_mode = 'Y' THEN
1825 -- -- End time for Process txn accum cmt
1826 -- x_PrcCmtEnd := SYSDATE;
1827 -- END IF;
1828 --
1829 -- END IF ;
1830
1831 IF (Pa_Proj_Accum_Main.action_exists
1832 (x_action_flag => 'CS',
1833 x_actions_in => x_actions_in,
1834 x_actions_cnt => x_actions_cnt) = 'Y'
1835 AND x_actual_cost_flag = 'Y')
1836 OR (Pa_Proj_Accum_Main.action_exists
1837 (x_action_flag => 'RV',
1838 x_actions_in => x_actions_in,
1839 x_actions_cnt => x_actions_cnt) = 'Y'
1840 AND x_revenue_flag = 'Y')
1841 OR Pa_Proj_Accum_Main.action_exists
1842 (x_action_flag => 'TX',
1843 x_actions_in => x_actions_in,
1844 x_actions_cnt => x_actions_cnt) = 'Y'
1845 OR (Pa_Proj_Accum_Main.action_exists
1846 (x_action_flag => 'CM',
1847 x_actions_in => x_actions_in,
1848 x_actions_cnt => x_actions_cnt) = 'Y'
1849 AND x_commitments_flag = 'Y')
1850 OR (Pa_Proj_Accum_Main.action_exists
1851 (x_action_flag => 'TM',
1852 x_actions_in => x_actions_in,
1853 x_actions_cnt => x_actions_cnt) = 'Y'
1854 AND x_commitments_flag = 'Y') THEN
1855
1856 -- rollup the actuals from pa_txn_accum to
1857 -- pa_project_accum_actuals
1858 -- Process the transactions by reading PA_TXN_ACCUM
1859 IF x_log_summ_mode = 'Y' THEN
1860 -- Start time for Process Txn Accum
1861 x_PrcTxnStart := SYSDATE;
1862 x_PrcCmtStart := SYSDATE;
1863 END IF;
1864 Pa_Maint_Project_Accums.process_txn_accum
1865 (l_project_id,
1866 x_impl_option,
1867 x_proj_level_accum_id,
1868 x_current_period,
1869 x_prev_period,
1870 x_current_year,
1871 x_prev_accum_period,
1872 x_current_start_date,
1873 x_current_end_date,
1874 x_actual_cost_flag,
1875 x_revenue_flag,
1876 x_commitments_flag,
1877 NULL,
1878 x_err_stack,
1879 x_err_stage,
1880 x_err_code) ;
1881 IF x_log_summ_mode = 'Y' THEN
1882 -- End time for Process Txn Accum
1883 x_PrcTxnEnd := SYSDATE;
1884 x_PrcCmtEnd := SYSDATE;
1885 END IF;
1886
1887 END IF ;
1888
1889 END IF ;
1890
1891 -- Delete any extraneous project records in the
1892 -- Project_accum_headers without Actuals,
1893 -- commitments and Budgets
1894
1895 Pa_Delete_Accum_Recs.delete_project_accum_headers
1896 (l_project_id,
1897 x_err_stack,
1898 x_err_stage,
1899 x_err_code) ;
1900
1901 -- Get the summary number from database tables
1902 -- to PL/SQL table
1903
1904 IF x_log_summ_mode = 'Y' THEN
1905 x_PrjSumStart := SYSDATE; /* Start time for Summary */
1906 END IF;
1907 Pa_Accum_Srw.get_project_summary_numbers
1908 (x_proj_level_accum_id,
1909 'POST',
1910 table_index,
1911 x_err_stack,
1912 x_err_stage,
1913 x_err_code) ;
1914
1915 Pa_Accum_Srw.number_of_projects := table_index ;
1916
1917 Pa_Accum_Srw.get_project_budget_numbers
1918 (x_proj_level_accum_id,
1919 'POST',
1920 x_err_stack,
1921 x_err_stage,
1922 x_err_code) ;
1923
1924 IF x_log_summ_mode = 'Y' THEN
1925 x_PrjSumEnd := SYSDATE; /* End time for Summary */
1926
1927 -- Inserting into the temporary table pa_accum_txn_time
1928 -- the various start and end time values
1929
1930 INSERT INTO pa_accum_txn_time
1931 (Project_id,
1932 Project_num,
1933 Request_id,
1934 Attribute1,
1935 Attribute2,
1936 Attribute3,
1937 Attribute4,
1938 Attribute5,
1939 Attribute6,
1940 Attribute7,
1941 Attribute8,
1942 Attribute9,
1943 Attribute10,
1944 Attribute11,
1945 Attribute12,
1946 Attribute13,
1947 Attribute14,
1948 Attribute15,
1949 Attribute16,
1950 Attribute17,
1951 Attribute18,
1952 Attribute19,
1953 Attribute20
1954 )
1955 VALUES
1956 (l_project_id,
1957 l_segment1,
1958 x_request_id,
1959 x_CdlStart,
1960 x_CdlEnd,
1961 x_RdlStart,
1962 x_RdlEnd,
1963 x_CmtCrStart,
1964 x_CmtCrEnd,
1965 x_CmtAccStart,
1966 x_CmtAccEnd,
1967 x_InitStart,
1968 x_InitEnd,
1969 x_BudStart,
1970 x_BudEnd,
1971 x_MapTxnStart,
1972 x_MapTxnEnd,
1973 x_PrcCmtStart,
1974 x_PrcCmtEnd,
1975 x_PrcTxnStart,
1976 x_PrcTxnEnd,
1977 x_PrjSumStart,
1978 x_PrjSumEnd
1979 );
1980 END IF;
1981
1982 COMMIT WORK; -- we are done with this project now
1983
1984 IF Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(valid_proj.project_id)) = 0 THEN
1985 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1986 Pa_Debug.debug('proj_accum: ' || 'Releasing the lock on project ...');
1987 END IF;
1988 END IF;
1989
1990 -- Re-Initializing start and end variables
1991
1992 IF x_log_summ_mode = 'Y' THEN
1993 x_CdlStart := NULL; /* Start time for accum_cdls */
1994 x_CdlEnd := NULL; /* End time for accum_cdls */
1995 x_RdlStart := NULL; /* Start time for accum_revenue */
1996 x_RdlEnd := NULL; /* End time for accum_revenue */
1997 x_CmtcrStart := NULL; /* Start time for create_commitments */
1998 x_CmtcrEnd := NULL; /* End time for create_commitments */
1999 x_CmtaccStart := NULL; /* Start time for accum_commitments */
2000 x_CmtaccEnd := NULL; /* End time for accum_commitments */
2001 x_InitStart := NULL; -- Start time for Initialize Actuals and CMTs
2002 x_InitEnd := NULL; -- End time for Initialize Actuals and CMTs
2003 x_BudStart := NULL; /* Start time for process_budget_txns */
2004 x_BudEnd := NULL; /* End time for process_budget_txns */
2005
2006 x_MapTxnStart := NULL; /* Start time for Map Txns */
2007 x_MapTxnEnd := NULL; /* End time for Map Txns */
2008 x_PrcCmtStart := NULL; /* Start time process_txn_accum_cmt */
2009 x_PrcCmtEnd := NULL; /* End time process_txn_accum_cmt */
2010 x_PrcTxnStart := NULL; /* Start time for process_txn_accum */
2011 x_PrcTxnEnd := NULL; /* End time for process_txn_accum */
2012 x_PrjSumStart := NULL; /* Start time for Project Summary */
2013 x_PrjSumEnd := NULL; /* End time for Project Summary */
2014 END IF;
2015
2016 <<skip_project>>
2017 NULL ;
2018
2019 END LOOP ;
2020
2021 IF x_delete_temp_table = 'Y' THEN
2022 DELETE FROM pa_projects_for_accum WHERE request_id = x_request_id
2023 AND exception_flag = 'N';
2024 ELSE
2025 DELETE FROM pa_projects_for_accum WHERE request_id = x_request_id;
2026 END IF;
2027
2028 COMMIT WORK ;
2029
2030 EXCEPTION
2031
2032 WHEN OTHERS THEN
2033 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
2034 x_err_code := SQLCODE;
2035 retcode := x_err_code;
2036 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
2037 ROLLBACK WORK;
2038 DELETE FROM pa_projects_for_accum
2039 WHERE request_id = x_request_id ;
2040 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2041 Pa_Debug.debug('proj_accum: ' || 'Exception Generated By Oracle Error: ' ||
2042 errbuf ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2043 END IF;
2044 COMMIT WORK;
2045 --raise;
2046 RETURN;
2047 END proj_accum;
2048
2049 -- Procedure for project refresh
2050
2051 -- project_num_from : start project # : mandatory
2052 -- project_num_to : end project # : mandatory
2053
2054 PROCEDURE proj_refresh
2055 ( errbuf IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2056 retcode IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2057 x_project_num_from IN VARCHAR2,
2058 x_project_num_to IN VARCHAR2,
2059 x_end_date_through IN DATE,
2060 x_project_type IN VARCHAR2 -- for bug 2543021
2061 )
2062 IS
2063
2064 /* Added Debug Profile Option variable initialization for bug#2674619 */
2065
2066 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2067
2068
2069
2070 l_accum_period_type_changed BOOLEAN;
2071
2072 x_log_summ_mode VARCHAR2(1) := 'N';
2073
2074 x_CdlStart DATE := NULL; /* Start time for accum_cdls */
2075 x_CdlEnd DATE := NULL; /* End time for accum_cdls */
2076 x_RdlStart DATE := NULL; /* Start time for accum_revenue */
2077 x_RdlEnd DATE := NULL; /* End time for accum_revenue */
2078 x_CmtcrStart DATE := NULL; /* Start time for create_commitments */
2079 x_CmtcrEnd DATE := NULL; /* End time for create_commitments */
2080 x_CmtaccStart DATE := NULL; /* Start time for accum_commitments */
2081 x_CmtaccEnd DATE := NULL; /* End time for accum_commitments */
2082
2083 x_MapTxnStart DATE := NULL; /* Start time for Map Txns */
2084 x_MapTxnEnd DATE := NULL; /* End time for Map Txns */
2085 x_PrcTxnStart DATE := NULL; /* Start time for process_txn_accum */
2086 x_PrcTxnEnd DATE := NULL; /* End time for process_txn_accum */
2087 x_PrcCmtStart DATE := NULL; /* Start time process_txn_accum_cmt */
2088 x_PrcCmtEnd DATE := NULL; /* End time process_txn_accum_cmt */
2089
2090 x_BudStart DATE := NULL; /* Start time for process_budget_txns */
2091 x_BudEnd DATE := NULL; /* End time for process_budget_txns */
2092 x_DelAccStart DATE := NULL; /* Start time - Delete ProjAccHeaders */
2093 x_DelAccEnd DATE := NULL; /* End time - Delete ProjAccHeaders */
2094 x_PrjSumStart DATE := NULL; /* Start time for Project Summary */
2095 x_PrjSumEnd DATE := NULL; /* End time for Project Summary */
2096
2097 l_project_num_from VARCHAR2(25);
2098 l_project_num_to VARCHAR2(25);
2099 l_project_id NUMBER;
2100 tmp_budget_type_code VARCHAR2(30) ;
2101
2102 -- Declare cursor for Projects
2103
2104 CURSOR selprjs IS
2105 SELECT
2106 ppr.project_id,
2107 ppr.segment1,
2108 ppr.closed_date
2109 FROM
2110 pa_projects ppr
2111 WHERE
2112 NVL(ppr.template_flag,'N') <> 'Y'
2113 AND ppr.segment1 BETWEEN l_project_num_from AND l_project_num_to
2114 AND ppr.project_type = NVL(x_project_type, project_type) -- for bug 2543021
2115 AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code) = 'N';
2116
2117 -- cursor for getting the budget_type_code for the given project_id
2118
2119 CURSOR Get_all_Budget_Types_cur(x_project_id NUMBER) IS
2120 SELECT DISTINCT use_code Budget_Type_Code
2121 FROM
2122 pa_resource_list_uses_v
2123 WHERE project_id = x_project_id
2124 AND budget_type_yn = 'Y';
2125
2126 projrec selprjs%ROWTYPE;
2127 budget_type_rec Get_all_Budget_Types_cur%ROWTYPE;
2128 table_index BINARY_INTEGER; -- index for the table for reporting
2129 x_date_through DATE;
2130
2131 BEGIN
2132
2133 x_old_stack := x_err_stack;
2134 x_err_stack := x_err_stack ||'->pa_proj_accum_main.proj_refresh';
2135 x_err_code := 0;
2136
2137 x_summ_process := 'RF';
2138 x_err_stage := 'Project refresh Process';
2139 x_log_summ_mode := Fnd_Profile.value('PA_LOG_SUMM_MODE');
2140
2141 IF (x_end_date_through IS NULL OR x_end_date_through >= x_current_pa_end_date) THEN
2142 x_date_through := x_end_pa_date;
2143 ELSE
2144 x_date_through := x_end_date_through;
2145 END IF;
2146
2147 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2148 Pa_Debug.debug('proj_refresh: ' || x_err_stack);
2149 END IF;
2150
2151
2152 -- Grants Management Integrated Commitment Processing ---------------------
2153 -- added 30-MAY-2003, jwhite
2154
2155 G_GMS_Enabled := GMS_PA_API3.grants_enabled;
2156
2157 IF ( P_DEBUG_MODE = 'Y' )
2158 THEN
2159 Pa_Debug.debug('proj_accum: G_GMS_Enabled = ' || G_GMS_Enabled);
2160 END IF;
2161
2162
2163 -- -------------------------------------------------------------------------
2164
2165
2166
2167 -- Validate Parameters
2168 Get_Project_Num_Range( x_project_num_from,
2169 x_project_num_to,
2170 l_project_num_from,
2171 l_project_num_to,
2172 x_project_type ); -- for bug 2543021
2173
2174 -- assume the process does not return an error
2175 retcode :=0;
2176
2177 -- Call the Initialization procedure
2178
2179 Pa_Proj_Accum_Main.initialize;
2180 -- Added below for Bug# 7175975
2181 populate_cmt_tmp_table(p_project_num_from => x_project_num_from,
2182 p_project_num_to => x_project_num_to,
2183 p_system_linkage_function => NULL,
2184 p_refresh_flag => 'Y',
2185 p_project_type => x_project_type);
2186 -- End for Bug# 7175975
2187
2188 FOR projrec IN selprjs LOOP
2189
2190 BEGIN
2191
2192 -- This PL/SQL block corresponds to processing for each project
2193
2194 l_project_id := projrec.project_id;
2195 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2196 Pa_Debug.debug('proj_refresh: ' || 'Refreshing accumulation for Project Id = '
2197 || TO_CHAR(projrec.project_id));
2198 END IF;
2199
2200 -- Initialize the Global variable in the loop for each project
2201 -- No need to check if the accumulation period type has been changed
2202 -- because the project is being refreshed
2203 Pa_Proj_Accum_Main.initialize_project(projrec.project_id,
2204 l_accum_period_type_changed);
2205
2206 -- Try to Lock the project level record
2207 IF Pa_Debug.acquire_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) <> 0 THEN
2208 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2209 Pa_Debug.debug('proj_refresh: ' || 'Could not acquire lock on the Project. Skipping ...');
2210 END IF;
2211 GOTO next_project;
2212 ELSE
2213 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2214 Pa_Debug.debug('proj_refresh: ' || 'Locking the project for summarization ...');
2215 END IF;
2216 END IF;
2217 -------------------------------------------------
2218
2219 -- Get the accum configuration
2220
2221 Pa_Txn_Accums.get_accum_configurations
2222 (projrec.project_id,
2223 x_err_stage,
2224 x_err_code);
2225
2226 -- Get the summary number from database tables to PL/SQL table
2227
2228 table_index := Pa_Accum_Srw.number_of_projects + 1;
2229
2230 Pa_Accum_Srw.get_project_summary_numbers
2231 (x_proj_level_accum_id,
2232 'PRE',
2233 table_index,
2234 x_err_stack,
2235 x_err_stage,
2236 x_err_code);
2237
2238 -- Get project Budgets
2239 Pa_Accum_Srw.get_project_budget_numbers
2240 (x_proj_level_accum_id,
2241 'PRE',
2242 x_err_stack,
2243 x_err_stage,
2244 x_err_code);
2245
2246 -- refresh the resource maps for the resource list assignments
2247 -- for the entire project
2248
2249 Pa_Res_Accums.delete_res_maps_on_prj_id
2250 (projrec.project_id,
2251 NULL, -- x_resource_list_id
2252 x_err_stage,
2253 x_err_code);
2254 Pa_Res_Accums.delete_resource_accum_details
2255 (NULL, -- x_resource_list_assignment_id
2256 NULL, -- x_resource_list_id
2257 projrec.project_id,
2258 x_err_stage,
2259 x_err_code);
2260
2261 -- refresh summary transaction accum
2262
2263 Pa_Delete_Accum_Recs.delete_project_actuals
2264 (projrec.project_id,
2265 x_err_stack,
2266 x_err_stage,
2267 x_err_code);
2268
2269 Pa_Delete_Accum_Recs.delete_project_commitments
2270 (projrec.project_id,
2271 x_err_stack,
2272 x_err_stage,
2273 x_err_code);
2274
2275 ------- updating resource_accumulated_flag if through_date is null
2276 IF x_end_date_through IS NOT NULL THEN
2277 Pa_Txn_Accums.update_resource_flag
2278 (projrec.project_id,
2279 projrec.project_id,
2280 x_start_pa_date,
2281 x_end_pa_date,
2282 x_err_stage,
2283 x_err_code);
2284 END IF;
2285 -- refresh the actuals txns to PA_TXN_ACCUM
2286
2287 Pa_Txn_Accums.refresh_txn_accum
2288 (projrec.project_id, -- start_project_id
2289 projrec.project_id, -- end_project_id
2290 x_start_pa_date, -- start_pa_period
2291 x_end_pa_date, -- end_pa_period
2292 'C', -- Tyansaction_type for CDLs
2293 NULL,
2294 x_err_stage,
2295 x_err_code);
2296
2297 Pa_Txn_Accums.refresh_txn_accum
2298 (projrec.project_id, -- start_project_id
2299 projrec.project_id, -- end_project_id
2300 x_start_pa_date, -- start_pa_period
2301 x_end_pa_date, -- end_pa_period
2302 'R', -- Transaction_type for Rev.
2303 NULL, -- system_linakge_funtion
2304 -- cannot be specified for Rev.
2305 x_err_stage,
2306 x_err_code);
2307
2308 -- Now reaccumulate the actuals
2309
2310 IF x_log_summ_mode = 'Y' THEN
2311 x_CdlStart := SYSDATE; /* Start time for CDLs */
2312 END IF;
2313
2314 Pa_Txn_Accums.accum_cdls
2315 (projrec.project_id, -- start_project_id
2316 projrec.project_id, -- end_project_id
2317 x_start_pa_date, -- start_pa_period
2318 x_date_through, -- end_pa_period
2319 NULL,
2320 'F',
2321 'R',
2322 x_err_stage,
2323 x_err_code);
2324
2325 -- 05-AUG-2003, jwhite: For patchset 'L' Reburdening Enhancement
2326 IF (Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening = 'Y')
2327 THEN
2328
2329 IF ( P_DEBUG_MODE = 'Y' )
2330 THEN
2331 Pa_Debug.debug('proj_accum: Call Accum_Cdls with x_cdl_line_type = I' );
2332 END IF;
2333
2334 Pa_Txn_Accums.accum_cdls
2335 (projrec.project_id, -- start_project_id
2336 projrec.project_id, -- end_project_id
2337 x_start_pa_date, -- start_pa_period
2338 x_date_through, -- end_pa_period
2339 NULL,
2340 'F',
2341 'I',
2342 x_err_stage,
2343 x_err_code);
2344
2345 END IF;
2346 -- --------------------------------------------------------------
2347
2348
2349
2350 IF x_log_summ_mode = 'Y' THEN
2351 x_CdlEnd := SYSDATE; /* End time for CDLs */
2352
2353 x_RdlStart := SYSDATE; /* Start time for RDLs */
2354 END IF;
2355
2356 Pa_Txn_Accums.accum_revenue
2357 (projrec.project_id, -- start_project_id
2358 projrec.project_id, -- end_project_id
2359 x_start_pa_date, -- start_pa_period
2360 x_date_through, -- end_pa_period
2361 'F',
2362 x_err_stage,
2363 x_err_code);
2364 IF x_log_summ_mode = 'Y' THEN
2365 x_RdlEnd := SYSDATE; /* End time for RDLs */
2366 END IF;
2367
2368 -- now refresh the commitments
2369 -- please note that the commitments are always refreshed
2370
2371 Pa_Txn_Accums.refresh_txn_accum
2372 (projrec.project_id, -- start_project_id
2373 projrec.project_id, -- end_project_id
2374 x_start_pa_date, -- start_pa_period
2375 x_end_pa_date, -- end_pa_period
2376 'M', -- Transaction_type for Commitments
2377 NULL,
2378 x_err_stage,
2379 x_err_code);
2380
2381 IF x_log_summ_mode = 'Y' THEN
2382 x_CmtCrStart := SYSDATE; /* Start time for CMTs creation */
2383 END IF;
2384
2385 Pa_Txn_Accums.create_cmt_txns
2386 (projrec.project_id, -- start_project_id
2387 projrec.project_id, -- end_project_id
2388 NULL,
2389 x_err_stage,
2390 x_err_code,
2391 'Y'); --x_use_tmp_table bug 5635857, changed to Y for Bug# 7175975
2392 IF x_log_summ_mode = 'Y' THEN
2393 x_CmtCrEnd := SYSDATE; /* End time for CMT creation */
2394
2395 x_CmtAccStart := SYSDATE; /* Start time for CMT accumulation */
2396 END IF;
2397
2398 -- Create summarized burden commitment transactions
2399 Pa_Burden_Costing.create_burden_cmt_transaction
2400 (x_project_id=>projrec.project_id, /* added for bug 2734747 */
2401 status=>x_status,
2402 stage=>x_stage ,
2403 x_run_id=>x_run_id);
2404
2405 Pa_Txn_Accums.accum_commitments
2406 (projrec.project_id, -- start_project_id
2407 projrec.project_id, -- end_project_id
2408 NULL,
2409 x_err_stage,
2410 x_err_code);
2411 IF x_log_summ_mode = 'Y' THEN
2412 x_CmtAccEnd := SYSDATE; /* End time for CMT accumulation */
2413
2414 -- Create resource Map
2415
2416 x_MapTxnStart := SYSDATE; /* Start time for Map Txns */
2417 END IF;
2418
2419 Pa_Res_Accums.map_txns
2420 (projrec.project_id,
2421 NULL, -- resource_list_id
2422 'F',
2423 x_err_stage,
2424 x_err_code);
2425 IF x_log_summ_mode = 'Y' THEN
2426 x_MapTxnEnd := SYSDATE; /* End time for Map Txns */
2427 x_PrcTxnStart := SYSDATE; /* Start time for process_txn_accum */
2428 x_PrcCmtStart := SYSDATE; /* Start time process_txn_accum_cmt */
2429 END IF;
2430
2431 -- refresh actuals
2432 -- Process the transactions by reading PA_TXN_ACCUM
2433 Pa_Maint_Project_Accums.process_txn_accum
2434 ( projrec.project_id,
2435 x_impl_option,
2436 x_proj_level_accum_id,
2437 x_current_period,
2438 x_prev_period,
2439 x_current_year,
2440 x_prev_accum_period,
2441 x_current_start_date,
2442 x_current_end_date,
2443 'Y',
2444 'Y',
2445 'Y',
2446 NULL,
2447 x_err_stack,
2448 x_err_stage,
2449 x_err_code);
2450 IF x_log_summ_mode = 'Y' THEN
2451 x_PrcTxnEnd := SYSDATE; /* Start time for process_txn_accum */
2452 x_PrcCmtEnd := SYSDATE; /* End time process_txn_accum_cmt */
2453 END IF;
2454
2455 -- refresh commitment
2456 -- pa_maint_project_commitments.process_txn_accum_cmt
2457 -- (projrec.project_id,
2458 -- x_impl_option,
2459 -- x_proj_level_accum_id,
2460 -- x_current_period,
2461 -- x_prev_period,
2462 -- x_current_year,
2463 -- x_prev_accum_period,
2464 -- x_current_start_date,
2465 -- x_current_end_date,
2466 -- x_err_stack,
2467 -- x_err_stage,
2468 -- x_err_code);
2469 IF x_log_summ_mode = 'Y' THEN
2470 x_PrcCmtEnd := SYSDATE; /* End time process_txn_accum_cmt */
2471 END IF;
2472
2473 Pa_Accum_Utils.update_proj_accum_header
2474 (x_proj_level_accum_id,
2475 x_current_period,
2476 x_err_stack,
2477 x_err_stage,
2478 x_err_code);
2479
2480 -- Update the tasks_restructured_flag to 'N'
2481
2482 Pa_Accum_Utils.update_tasks_restructured_flag (x_proj_level_accum_id,
2483 'N',
2484 x_err_stack,
2485 x_err_stage,
2486 x_err_code);
2487
2488 -- Check if the reporting period was moved backwards
2489 check_reporting_period
2490 (x_reporting_period_status, x_err_stack, x_err_stage, x_err_code);
2491
2492 IF(x_reporting_period_status = 'Y') THEN
2493
2494 -- Stop processing and exit the procedure return from the procedure
2495 -- after a rollback;
2496 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2497 Pa_Debug.debug('proj_refresh: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2498 Pa_Debug.debug('proj_refresh: ' || '******Reporting Period Moved Backward In Time During Process Run',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2499 Pa_Debug.debug('proj_refresh: ' || '******The Accumulation for the project '|| projrec.segment1 || ' Rolled Back',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2500 Pa_Debug.debug('proj_refresh: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2501 END IF;
2502 IF Pa_Debug.release_user_lock( 'PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
2503 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2504 Pa_Debug.debug('proj_refresh: ' || 'Released lock on project ...');
2505 END IF;
2506 END IF;
2507 errbuf := 'Reporting Period Moved Backward In Time During Process Run';
2508 retcode := 2;
2509 ROLLBACK WORK;
2510 RETURN;
2511 END IF; -- IF(x_reporting_period_status = 'Y')
2512
2513
2514 UPDATE Pa_Budget_Versions
2515 SET Resource_Accumulated_Flag ='N'
2516 WHERE Project_id = projrec.project_id AND
2517 (Current_Flag = 'Y' OR current_original_flag = 'Y');
2518
2519 Pa_Delete_Accum_Recs.delete_project_budgets
2520 (projrec.project_id,
2521 NULL,
2522 x_err_stack,
2523 x_err_stage,
2524 x_err_code);
2525
2526 -- Process the transactions by reading PA_BUDGET_BY_PA_PERIOD_V
2527 IF x_log_summ_mode = 'Y' THEN
2528 x_BudStart := SYSDATE; /* Start time for Process_budget_txns */
2529 END IF;
2530 Pa_Maint_Project_Budgets.process_budget_txns
2531 (projrec.project_id,
2532 x_impl_option,
2533 x_proj_level_accum_id,
2534 NULL,
2535 x_current_period,
2536 x_prev_period,
2537 x_current_year,
2538 x_prev_accum_period,
2539 x_current_start_date,
2540 x_current_end_date,
2541 x_err_stack,
2542 x_err_stage,
2543 x_err_code);
2544 IF x_log_summ_mode = 'Y' THEN
2545 x_BudEnd := SYSDATE; /* End time for Process_budget_txns */
2546 END IF;
2547
2548 -- Delete any extraneous project records in the Project_accum_headers
2549 -- without Actuals,commitments and Budgets
2550
2551 IF x_log_summ_mode = 'Y' THEN
2552 x_DelAccStart := SYSDATE; /* Start time for Delete Project_accum_headers */
2553 END IF;
2554 Pa_Delete_Accum_Recs.delete_project_accum_headers
2555 (projrec.project_id ,
2556 x_err_stack,
2557 x_err_stage,
2558 x_err_code);
2559 IF x_log_summ_mode = 'Y' THEN
2560 x_DelAccEnd := SYSDATE; /* End time for Delete Project_accum_headers */
2561
2562 x_PrjSumStart := SYSDATE; /* Start time for Summary */
2563 END IF;
2564
2565 -- Get the summary number from database tables to PL/SQL table
2566
2567 Pa_Accum_Srw.get_project_summary_numbers
2568 (x_proj_level_accum_id,
2569 'POST',
2570 table_index,
2571 x_err_stack,
2572 x_err_stage,
2573 x_err_code);
2574 IF x_log_summ_mode = 'Y' THEN
2575 x_PrjSumEnd := SYSDATE; /* End time for Summary */
2576 END IF;
2577
2578 Pa_Accum_Srw.number_of_projects := table_index;
2579
2580 Pa_Accum_Srw.get_project_budget_numbers
2581 (x_proj_level_accum_id,
2582 'POST',
2583 x_err_stack,
2584 x_err_stage,
2585 x_err_code);
2586
2587
2588 <<next_project>>
2589
2590 -- Check if the reporting period was moved backwards
2591 check_reporting_period
2592 (x_reporting_period_status, x_err_stack, x_err_stage, x_err_code);
2593
2594 IF(x_reporting_period_status = 'Y') THEN
2595 -- Stop processing and exit the procedure return from the procedure
2596 -- after a rollback;
2597 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2598 Pa_Debug.debug('proj_refresh: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2599 Pa_Debug.debug('proj_refresh: ' || '******Reporting Period Moved Backward In Time During Process Run',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2600 Pa_Debug.debug('proj_refresh: ' || '******The Accumulation for the project '|| projrec.segment1 || ' Rolled Back',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2601 Pa_Debug.debug('proj_refresh: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2602 END IF;
2603 IF Pa_Debug.release_user_lock( 'PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
2604 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2605 Pa_Debug.debug('proj_refresh: ' || 'Released lock on project ...');
2606 END IF;
2607 END IF;
2608 errbuf := 'Reporting Period Moved Backward In Time During Process Run';
2609 retcode := 2;
2610 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2611 Pa_Debug.debug('proj_refresh: ' || 'Here');
2612 END IF;
2613 ROLLBACK WORK;
2614 RETURN;
2615 END IF; -- IF(x_reporting_period_status = 'Y')
2616
2617 -- Inserting into the temporary table pa_accum_txn_time
2618 -- the various start and end time values
2619
2620 IF x_log_summ_mode = 'Y' THEN
2621 INSERT INTO pa_accum_txn_time
2622 (Project_id,
2623 Project_num,
2624 Request_id,
2625 Attribute1,
2626 Attribute2,
2627 Attribute3,
2628 Attribute4,
2629 Attribute5,
2630 Attribute6,
2631 Attribute7,
2632 Attribute8,
2633 Attribute9,
2634 Attribute10,
2635 Attribute11,
2636 Attribute12,
2637 Attribute13,
2638 Attribute14,
2639 Attribute15,
2640 Attribute16,
2641 Attribute17,
2642 Attribute18,
2643 Attribute19,
2644 Attribute20
2645 )
2646 VALUES
2647 (projrec.project_id,
2648 projrec.segment1,
2649 x_request_id,
2650 x_CdlStart,
2651 x_CdlEnd,
2652 x_RdlStart,
2653 x_RdlEnd,
2654 x_CmtCrStart,
2655 x_CmtCrEnd,
2656 x_CmtAccStart,
2657 x_CmtAccEnd,
2658 x_MapTxnStart,
2659 x_MapTxnEnd,
2660 x_PrcTxnStart,
2661 x_PrcTxnEnd,
2662 x_PrcCmtStart,
2663 x_PrcCmtEnd,
2664 x_BudStart,
2665 x_BudEnd,
2666 x_DelAccStart,
2667 x_DelAccEnd,
2668 x_PrjSumStart,
2669 x_PrjSumEnd
2670 );
2671 END IF;
2672
2673 COMMIT WORK; -- we are done with this project now
2674 IF Pa_Debug.release_user_lock( 'PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
2675 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2676 Pa_Debug.debug('proj_refresh: ' || 'Released lock on project ...');
2677 END IF;
2678 END IF;
2679
2680 -- Re-initializing start and end variables
2681
2682 IF x_log_summ_mode = 'Y' THEN
2683
2684 x_CdlStart := NULL; /* Start time for accum_cdls */
2685 x_CdlEnd := NULL; /* End time for accum_cdls */
2686 x_RdlStart := NULL; /* Start time for accum_revenue */
2687 x_RdlEnd := NULL; /* End time for accum_revenue */
2688 x_CmtcrStart := NULL; /* Start time for create_commitments */
2689 x_CmtcrEnd := NULL; /* End time for create_commitments */
2690 x_CmtaccStart:= NULL; /* Start time for accum_commitments */
2691 x_CmtaccEnd := NULL; /* End time for accum_commitments */
2692
2693 x_MapTxnStart:= NULL; /* Start time for Map Txns */
2694 x_MapTxnEnd := NULL; /* End time for Map Txns */
2695 x_PrcTxnStart:= NULL; /* Start time for process_txn_accum */
2696 x_PrcTxnEnd := NULL; /* End time for process_txn_accum */
2697 x_PrcCmtStart:= NULL; /* Start time process_txn_accum_cmt */
2698 x_PrcCmtEnd := NULL; /* End time process_txn_accum_cmt */
2699
2700 x_BudStart := NULL; /* Start time for process_budget_txns */
2701 x_BudEnd := NULL; /* End time for process_budget_txns */
2702 x_DelAccStart:= NULL; /* Start time for Delete ProjAccHead */
2703 x_DelAccEnd := NULL; /* End time for Delete ProjAccHead */
2704 x_PrjSumStart:= NULL; /* Start time for Project Summary */
2705 x_PrjSumEnd := NULL; /* End time for Project Summary */
2706
2707 END IF;
2708
2709 EXCEPTION
2710
2711 WHEN OTHERS THEN
2712 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
2713 x_err_code := SQLCODE;
2714 retcode := x_err_code;
2715 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
2716 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2717 Pa_Debug.debug('proj_refresh: ' || 'Exception Generated By Oracle Error: ' || errbuf,Pa_Debug.DEBUG_LEVEL_EXCEPTION );
2718 Pa_Debug.debug('proj_refresh: ' || 'Skipping the accumulation for project ' || projrec.segment1 ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2719 END IF;
2720
2721 -- Exception handling for database errors (other than 1400 and 1489)
2722 IF x_err_code*-1 BETWEEN 1400 AND 1489 THEN
2723 ROLLBACK WORK;
2724 ELSE
2725 RAISE;
2726 END IF;
2727
2728 END; -- end of PL/SQL block for each project
2729
2730 END LOOP;
2731
2732 x_err_stack := x_Old_Stack;
2733 RETURN;
2734
2735 EXCEPTION
2736
2737 WHEN OTHERS THEN
2738 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
2739 x_err_code := SQLCODE;
2740 retcode := x_err_code;
2741 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
2742 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2743 Pa_Debug.debug('proj_refresh: ' || 'Exception Generated By Oracle Error: ' || errbuf ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2744 END IF;
2745 ROLLBACK WORK;
2746 RETURN;
2747 END proj_refresh;
2748
2749 -- project_num_from : start project # : mandatory
2750 -- project_num_to : end project # : mandatory
2751 -- Resource_list_id : resource_list_id : optional
2752
2753 PROCEDURE ref_rl_accum
2754 ( errbuf IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2755 retcode IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2756 x_project_num_from IN VARCHAR2,
2757 x_project_num_to IN VARCHAR2,
2758 x_resource_list_id IN NUMBER
2759 )
2760 IS
2761 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
2762 l_accum_period_type_changed BOOLEAN;
2763 l_sum_exception_code pa_project_accum_headers.sum_exception_code%TYPE := NULL;
2764 l_current_start_date DATE := NULL;
2765 l_current_end_date DATE := NULL;
2766
2767 -- Declare cursor for Projects
2768
2769 l_project_num_from VARCHAR2(25);
2770 l_project_num_to VARCHAR2(25);
2771 l_project_id NUMBER;
2772
2773 CURSOR selprjs IS
2774 SELECT
2775 ppr.project_id,
2776 ppr.segment1,
2777 ppr.closed_date
2778 FROM
2779 pa_projects ppr
2780 WHERE
2781 NVL(ppr.template_flag,'N') <> 'Y'
2782 AND ppr.segment1 BETWEEN l_project_num_from AND l_project_num_to
2783 AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code)= 'N';
2784
2785 projrec selprjs%ROWTYPE;
2786 x_tasks_restructured_flag VARCHAR2(1);
2787
2788 BEGIN
2789
2790 x_err_code := 0;
2791 x_old_stack := x_err_stack;
2792
2793 x_summ_process := 'RL';
2794 x_err_stack := x_err_stack || '->pa_proj_accum_main.ref_rl_accum';
2795 x_err_stage := 'Refresh Resource List accumulation Process';
2796
2797 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2798 Pa_Debug.debug('ref_rl_accum: ' || x_err_stack);
2799 END IF;
2800
2801 -- Validate Parameters
2802 Get_Project_Num_Range( x_project_num_from,
2803 x_project_num_to,
2804 l_project_num_from,
2805 l_project_num_to,
2806 NULL ); --for bug 2543021
2807
2808 -- assume the process does not return an error
2809 retcode :=0;
2810
2811 -- Call the Initialization procedure
2812
2813 Pa_Proj_Accum_Main.initialize;
2814
2815 FOR projrec IN selprjs LOOP
2816
2817 BEGIN
2818 l_sum_exception_code := NULL;
2819 l_project_id := projrec.project_id;
2820 -- This PL/SQL block corresponds to processing for each project
2821
2822 ------------Acquire lock on the project-----------
2823 IF Pa_Debug.acquire_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) <> 0 THEN
2824 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2825 Pa_Debug.debug('ref_rl_accum: ' || 'Could not acquire lock on the Project. Skipping ...');
2826 END IF;
2827 GOTO next_project;
2828 ELSE
2829 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2830 Pa_Debug.debug('ref_rl_accum: ' || 'Locking the project for summarization ...');
2831 END IF;
2832 END IF;
2833 -------------------------------------------------
2834
2835 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2836 Pa_Debug.debug('ref_rl_accum: ' || 'Refreshing resource list id = ' ||
2837 NVL(TO_CHAR(x_resource_list_id),'ALL') || ' For Project Id = '
2838 || TO_CHAR(projrec.project_id));
2839 END IF;
2840
2841 -- Initialize the Global variable in the loop for each project
2842 Pa_Proj_Accum_Main.initialize_project(projrec.project_id,
2843 l_accum_period_type_changed);
2844 IF x_impl_option = 'PA' THEN
2845 l_current_start_date := x_current_pa_start_date;
2846 l_current_end_date := x_current_pa_end_date;
2847 ELSIF x_impl_option = 'GL' THEN
2848 l_current_start_date := x_current_gl_start_date;
2849 l_current_end_date := x_current_gl_end_date;
2850 END IF;
2851
2852 --
2853 -- Check to see if the accumulation period type has changed. If so, the
2854 -- project must be refreshed. Bug #572031
2855 --
2856 IF (Pa_Proj_Accum_Main.check_period_flags(projrec.project_id,x_impl_option,l_current_end_date,
2857 'PT',projrec.closed_date,l_current_start_date)='Y') THEN
2858 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2859 Pa_Debug.debug('ref_rl_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2860 Pa_Debug.debug('ref_rl_accum: ' || '*****THE ACCUMULATION PERIOD TYPE HAS CHANGED SINCE THE LAST TIME PROJECT ' || projrec.segment1 || ' WAS ACCUMULATED '|| '******',
2861 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2862 Pa_Debug.debug('ref_rl_accum: ' || '*****PLEASE REFRESH THE PROJECT ******', Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2863 Pa_Debug.debug('ref_rl_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2864 END IF;
2865 -- Stop processing and exit the procedure return from the procedure
2866 -- after a rollback;
2867 errbuf := 'The Project ' || projrec.segment1 || 'Must Be Refreshed, Because the accumulation period type has changed since the last time the project was summarized';
2868 -- retcode := 6;
2869 l_sum_exception_code := 'PA_SUM_ACCUM_PERIOD_CHANGED';
2870 -- ROLLBACK WORK;
2871 -- return;
2872 END IF;
2873
2874
2875 -- If the tasks restructured flag = 'Y', then put to debug message into the logfile
2876 BEGIN
2877 x_tasks_restructured_flag := 'N';
2878
2879 SELECT 'Y'
2880 INTO x_tasks_restructured_flag
2881 FROM sys.dual
2882 WHERE EXISTS (SELECT 'x'
2883 FROM pa_project_accum_headers pah
2884 WHERE pah.project_id = projrec.project_id
2885 AND NVL(pah.tasks_restructured_flag,'N') = 'Y');
2886
2887 IF ( x_tasks_restructured_flag = 'Y') THEN
2888 -- Add a message into the Log file
2889 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2890 Pa_Debug.debug('ref_rl_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2891 Pa_Debug.debug('ref_rl_accum: ' || '*****THE PROJECT ' || projrec.segment1 || ' TASKS ARE RESTRUCTURED, PLEASE REFRESH THE PROJECT ******',
2892 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2893 Pa_Debug.debug('ref_rl_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2894 END IF;
2895 -- Stop processing and exit the procedure return from the procedure
2896 -- after a rollback;
2897 errbuf := 'The Project ' || projrec.segment1 || 'Must Be Refreshed, Because the Tasks for this Project were Restructured';
2898 l_sum_exception_code := 'PA_SUM_TASKS_RESTRUCTURED';
2899 -- retcode := 6;
2900 -- ROLLBACK WORK;
2901 -- return;
2902 END IF;
2903 EXCEPTION WHEN NO_DATA_FOUND THEN
2904 NULL;
2905 END;
2906 IF (l_sum_exception_code IS NOT NULL) THEN
2907 IF (x_proj_header_locked = TRUE) THEN
2908 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2909 Pa_Debug.debug('ref_rl_accum: ' || 'Cannot update summarization exception for project'||projrec.segment1,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
2910 END IF;
2911 ELSE
2912 UPDATE pa_project_accum_headers
2913 SET sum_exception_code = l_sum_exception_code
2914 WHERE project_id = projrec.project_id
2915 AND task_id = 0
2916 AND resource_list_member_id = 0;
2917 COMMIT WORK;
2918 END IF;
2919 IF Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
2920 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2921 Pa_Debug.debug('ref_rl_accum: ' || 'Releasing the lock on project ...');
2922 END IF;
2923 END IF;
2924 GOTO next_project;
2925 END IF;
2926 -- refresh the resource maps for the resource list assignments
2927 Pa_Res_Accums.delete_res_maps_on_prj_id
2928 (projrec.project_id,
2929 x_resource_list_id,
2930 x_err_stage,
2931 x_err_code);
2932
2933 Pa_Res_Accums.delete_resource_accum_details
2934 (NULL,
2935 x_resource_list_id,
2936 projrec.project_id,
2937 x_err_stage,
2938 x_err_code);
2939
2940 Pa_Delete_Accum_Recs.delete_res_list_actuals
2941 (projrec.project_id,
2942 x_resource_list_id,
2943 x_err_stack,
2944 x_err_stage,
2945 x_err_code);
2946
2947 Pa_Delete_Accum_Recs.delete_res_list_commitments
2948 (projrec.project_id,
2949 x_resource_list_id,
2950 x_err_stack,
2951 x_err_stage,
2952 x_err_code);
2953
2954 -- Create resource Map
2955 Pa_Res_Accums.map_txns
2956 (projrec.project_id,
2957 x_resource_list_id, -- resource_list_id
2958 'F', -- x_mode = FULL
2959 x_err_stage,
2960 x_err_code);
2961
2962 IF ( x_prev_accum_period IS NULL ) THEN
2963 -- refresh the resource list upto current period
2964 Pa_Maint_Project_Accums.process_txn_accum
2965 ( projrec.project_id,
2966 x_impl_option,
2967 x_proj_level_accum_id,
2968 x_current_period,
2969 x_prev_period,
2970 x_current_year,
2971 x_prev_accum_period,
2972 x_current_start_date,
2973 x_current_end_date,
2974 'Y',
2975 'Y',
2976 'Y',
2977 x_resource_list_id,
2978 x_err_stack,
2979 x_err_stage,
2980 x_err_code);
2981
2982 -- pa_refresh_res_lists.process_res_lists
2983 -- (projrec.project_id,
2984 -- x_resource_list_id,
2985 -- x_current_period,
2986 -- x_prev_period,
2987 -- x_current_year,
2988 -- x_current_start_date,
2989 -- x_current_end_date,
2990 -- x_err_stack,
2991 -- x_err_stage,
2992 -- x_err_code);
2993 ELSE
2994 -- refresh the resource list upto last accumulation period
2995 Pa_Maint_Project_Accums.process_txn_accum
2996 (projrec.project_id,
2997 x_impl_option,
2998 x_proj_level_accum_id,
2999 x_prev_accum_period,
3000 x_prev_prev_accum_period,
3001 x_prev_year,
3002 x_prev_accum_period,
3003 x_prev_accum_start_date,
3004 x_prev_accum_end_date,
3005 'Y',
3006 'Y',
3007 'Y',
3008 x_resource_list_id,
3009 x_err_stack,
3010 x_err_stage,
3011 x_err_code) ;
3012
3013 -- pa_refresh_res_lists.process_res_lists
3014 -- (projrec.project_id,
3015 -- x_resource_list_id,
3016 -- x_prev_accum_period,
3017 -- x_prev_prev_accum_period,
3018 -- x_prev_year,
3019 -- x_prev_accum_start_date,
3020 -- x_prev_accum_end_date,
3021 -- x_err_stack,
3022 -- x_err_stage,
3023 -- x_err_code);
3024 END IF;
3025
3026 <<next_project>>
3027
3028 -- Check if the reporting period was moved backwards
3029 check_reporting_period
3030 (x_reporting_period_status, x_err_stack, x_err_stage, x_err_code);
3031
3032 IF(x_reporting_period_status = 'Y') THEN
3033
3034 -- Stop processing and exit the procedure return from the procedure
3035 -- after a rollback;
3036 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3037 Pa_Debug.debug('ref_rl_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3038 Pa_Debug.debug('ref_rl_accum: ' || '******Reporting Period Moved Backward In Time During Process Run',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3039 Pa_Debug.debug('ref_rl_accum: ' || '******The Accumulation for the project '|| projrec.segment1 || ' Rolled Back',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3040 Pa_Debug.debug('ref_rl_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3041 END IF;
3042 IF Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
3043 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3044 Pa_Debug.debug('ref_rl_accum: ' || 'Releasing the lock on project ...');
3045 END IF;
3046 END IF;
3047 errbuf := 'Reporting Period Moved Backward In Time During Process Run';
3048 retcode := 2;
3049 ROLLBACK WORK;
3050 RETURN;
3051 END IF; -- IF(x_reporting_period_status = 'Y')
3052
3053 COMMIT WORK; -- we are done with this project now
3054 IF Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
3055 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3056 Pa_Debug.debug('ref_rl_accum: ' || 'Releasing the lock on project ...');
3057 END IF;
3058 END IF;
3059
3060
3061 EXCEPTION
3062
3063 WHEN OTHERS THEN
3064 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
3065 x_err_code := SQLCODE;
3066 retcode := x_err_code;
3067 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
3068 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3069 Pa_Debug.debug('ref_rl_accum: ' || 'Exception Generated By Oracle Error: ' || errbuf ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3070 Pa_Debug.debug('ref_rl_accum: ' || 'Skipping the accumulation for project ' || projrec.segment1 ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3071 END IF;
3072
3073 -- Exception handling for database errors (other than 1400 and 1489)
3074 IF x_err_code*-1 BETWEEN 1400 AND 1489 THEN
3075 ROLLBACK WORK;
3076 ELSE
3077 RAISE;
3078 END IF;
3079
3080 END; -- end of PL/SQL block for each project
3081
3082 END LOOP;
3083
3084 x_err_stack := x_old_stack;
3085 RETURN;
3086
3087 EXCEPTION
3088
3089 WHEN OTHERS THEN
3090 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
3091 x_err_code := SQLCODE;
3092 retcode := x_err_code;
3093 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
3094 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3095 Pa_Debug.debug('ref_rl_accum: ' || 'Exception Generated By Oracle Error: ' || errbuf ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3096 END IF;
3097 ROLLBACK WORK;
3098 RETURN;
3099 END ref_rl_accum;
3100
3101
3102
3103 -- project_num_from : start project # : mandatory
3104 -- project_num_to : end project # : mandatory
3105 -- start_pa_period : start_pa_period : optional
3106 -- end_pa_period : end_pa_period : optional
3107 -- system_linkage_function: : optional
3108
3109 PROCEDURE build_txn_accum
3110 ( errbuf IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3111 retcode IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3112 x_project_num_from IN VARCHAR2,
3113 x_project_num_to IN VARCHAR2,
3114 x_start_pa_period IN VARCHAR2,
3115 x_end_pa_period IN VARCHAR2,
3116 x_system_linkage_function IN VARCHAR2
3117 )
3118 IS
3119 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
3120 l_accum_period_type_changed BOOLEAN;
3121 -- Declare cursor for Projects
3122
3123 l_project_num_from VARCHAR2(25);
3124 l_project_num_to VARCHAR2(25);
3125 l_project_id NUMBER;
3126
3127 CURSOR selprjs IS
3128 SELECT
3129 ppr.project_id,
3130 ppr.segment1,
3131 ppr.closed_date
3132 FROM
3133 pa_projects ppr
3134 WHERE
3135 NVL(ppr.template_flag,'N') <> 'Y'
3136 AND ppr.segment1 BETWEEN l_project_num_from AND l_project_num_to
3137 AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code)= 'N';
3138
3139 projrec selprjs%ROWTYPE;
3140 x_mode VARCHAR2(1);
3141 table_index BINARY_INTEGER; -- index for the table for reporting
3142
3143
3144 BEGIN
3145
3146 x_err_code := 0;
3147 x_err_stage := 'Build Transaction accumulation Process';
3148
3149 x_summ_process := 'RT';
3150 x_old_stack := x_err_stack;
3151 x_err_stack := x_err_stack || '->pa_proj_accum_main.build_txn_accum';
3152
3153 x_mode := 'F';
3154
3155 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3156 Pa_Debug.debug('build_txn_accum: ' || x_err_stack);
3157 END IF;
3158
3159 -- Validate Parameters
3160 Get_Project_Num_Range( x_project_num_from,
3161 x_project_num_to,
3162 l_project_num_from,
3163 l_project_num_to,
3164 NULL ); --for bug 2543021
3165
3166 IF (x_start_pa_period IS NOT NULL AND x_end_pa_period IS NULL ) THEN
3167
3168 errbuf := 'End PA period must be specified when start PA period is given ';
3169 retcode := 2;
3170 RETURN;
3171 END IF;
3172
3173 -- assume the process does not return an error
3174 retcode :=0;
3175
3176 -- all the parameters are validated now
3177
3178 -- Call the Initialization procedure
3179
3180 Pa_Proj_Accum_Main.initialize;
3181
3182 -- Get the start pa_date and end pa_date for processing
3183
3184 IF ( x_start_pa_period IS NOT NULL ) THEN
3185 BEGIN
3186 SELECT start_date INTO x_start_pa_date
3187 FROM pa_periods
3188 WHERE period_name = x_start_pa_period;
3189
3190 EXCEPTION
3191 WHEN NO_DATA_FOUND THEN
3192 NULL;
3193 WHEN OTHERS THEN
3194 errbuf := 'Error Occured while getting the start_date for the start pa_period';
3195 retcode := 3;
3196 RETURN;
3197 END;
3198 BEGIN
3199 SELECT end_date INTO x_end_pa_date
3200 FROM pa_periods
3201 WHERE period_name = x_end_pa_period;
3202
3203 EXCEPTION
3204 WHEN NO_DATA_FOUND THEN
3205 NULL;
3206 WHEN OTHERS THEN
3207 errbuf := 'Error Occured while getting the end_date for the end pa_period';
3208 retcode := 4;
3209 RETURN;
3210 END;
3211
3212 END IF; -- IF ( x_start_pa_period IS NOT NULL )
3213
3214 FOR projrec IN selprjs LOOP
3215
3216 BEGIN
3217
3218 -- This PL/SQL block corresponds to processing for each project
3219 ------------Acquire lock on the project-----------
3220 IF Pa_Debug.acquire_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) <> 0 THEN
3221 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3222 Pa_Debug.debug('build_txn_accum: ' || 'Could not acquire lock on the Project. Skipping ...');
3223 END IF;
3224 GOTO next_project;
3225 ELSE
3226 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3227 Pa_Debug.debug('build_txn_accum: ' || 'Locking the project for summarization ...');
3228 END IF;
3229 END IF;
3230 -------------------------------------------------
3231 l_project_id := projrec.project_id;
3232 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3233 Pa_Debug.debug('build_txn_accum: ' || 'Refreshing Transaction Accumulation for the Project Id = '
3234 || TO_CHAR(projrec.project_id));
3235 END IF;
3236
3237 -- Initialize the Global variable in the loop for each project
3238 -- No need to check if the accumulation period type has changed because
3239 -- the project is being refreshed
3240 Pa_Proj_Accum_Main.initialize_project(projrec.project_id, l_accum_period_type_changed);
3241
3242 -- Get the accum configuration
3243
3244 Pa_Txn_Accums.get_accum_configurations
3245 ( projrec.project_id,
3246 x_err_stage,
3247 x_err_code);
3248
3249 -- Get the summary number from database tables to PL/SQL table
3250
3251 table_index := Pa_Accum_Srw.number_of_projects + 1;
3252
3253 Pa_Accum_Srw.get_project_txn_numbers
3254 (projrec.project_id, --- project Id
3255 'PRE',
3256 table_index,
3257 x_err_stack,
3258 x_err_stage,
3259 x_err_code);
3260
3261 -- refresh the resource maps for the resource list assignments
3262 -- for the entire project
3263
3264 Pa_Res_Accums.delete_res_maps_on_prj_id
3265 (projrec.project_id,
3266 NULL,
3267 x_err_stage,
3268 x_err_code);
3269
3270 Pa_Res_Accums.delete_resource_accum_details
3271 (NULL,
3272 NULL,
3273 projrec.project_id,
3274 x_err_stage,
3275 x_err_code);
3276
3277 -- refresh summary transaction accum
3278
3279 Pa_Delete_Accum_Recs.delete_project_actuals
3280 (projrec.project_id,
3281 x_err_stack,
3282 x_err_stage,
3283 x_err_code);
3284
3285 Pa_Delete_Accum_Recs.delete_project_commitments
3286 (projrec.project_id,
3287 x_err_stack,
3288 x_err_stage,
3289 x_err_code);
3290
3291 -- refresh and accumulate the actuals txns to PA_TXN_ACCUM
3292 -- First call the refresh APIs
3293
3294 Pa_Txn_Accums.refresh_txn_accum
3295 (projrec.project_id, -- start_project_id
3296 projrec.project_id, -- end_project_id
3297 x_start_pa_date, -- start_pa_period
3298 x_end_pa_date, -- end_pa_period
3299 'C', -- Tyansaction_type for CDLs
3300 x_system_linkage_function,
3301 x_err_stage,
3302 x_err_code);
3303
3304 Pa_Txn_Accums.refresh_txn_accum
3305 (projrec.project_id, -- start_project_id
3306 projrec.project_id, -- end_project_id
3307 x_start_pa_date, -- start_pa_period
3308 x_end_pa_date, -- end_pa_period
3309 'R', -- Transaction_type for Rev.
3310 NULL, -- system_linakge_funtion
3311 -- cannot be specified for Rev.
3312 x_err_stage,
3313 x_err_code);
3314
3315 Pa_Txn_Accums.accum_cdls
3316 (projrec.project_id, -- start_project_id
3317 projrec.project_id, -- end_project_id
3318 x_start_pa_date, -- start_pa_period
3319 x_end_pa_date, -- end_pa_period
3320 x_system_linkage_function,
3321 x_mode,
3322 'R',
3323 x_err_stage,
3324 x_err_code);
3325
3326
3327 -- 05-AUG-2003, jwhite: For patchset 'L' Reburdening Enhancement
3328 IF (Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening = 'Y')
3329 THEN
3330
3331
3332 IF ( P_DEBUG_MODE = 'Y' )
3333 THEN
3334 Pa_Debug.debug('build_txn_accum: Call Accum_Cdls with x_cdl_line_type = I' );
3335 END IF;
3336
3337 Pa_Txn_Accums.accum_cdls
3338 (projrec.project_id, -- start_project_id
3339 projrec.project_id, -- end_project_id
3340 x_start_pa_date, -- start_pa_period
3341 x_end_pa_date, -- end_pa_period
3342 x_system_linkage_function,
3343 x_mode,
3344 'I',
3345 x_err_stage,
3346 x_err_code);
3347
3348 END IF;
3349 -- --------------------------------------------------------------
3350
3351
3352 Pa_Txn_Accums.accum_revenue
3353 (projrec.project_id, -- start_project_id
3354 projrec.project_id, -- end_project_id
3355 x_start_pa_date, -- start_pa_period
3356 x_end_pa_date, -- end_pa_period
3357 x_mode,
3358 x_err_stage,
3359 x_err_code);
3360
3361 -- Move the tot_ figures into i_ figures for other transactions
3362 -- which are not refreshed currently
3363
3364 Pa_Txn_Accums.update_act_txn_accum
3365 ( projrec.project_id,
3366 projrec.project_id,
3367 x_err_stage,
3368 x_err_code);
3369
3370 -- please note that the budgets and commitments are always refreshed
3371
3372 -- introduce the budgets API here
3373 -- first refresh the commitments
3374
3375 Pa_Txn_Accums.refresh_txn_accum
3376 (projrec.project_id, -- start_project_id
3377 projrec.project_id, -- end_project_id
3378 x_start_pa_date, -- start_pa_period
3379 x_end_pa_date, -- end_pa_period
3380 'M', -- Transaction_type for Commitments
3381 x_system_linkage_function,
3382 x_err_stage,
3383 x_err_code);
3384
3385 Pa_Txn_Accums.create_cmt_txns
3386 (projrec.project_id, -- start_project_id
3387 projrec.project_id, -- end_project_id
3388 x_system_linkage_function,
3389 x_err_stage,
3390 x_err_code,
3391 'N'); --x_use_tmp_table bug 5635857
3392
3393 -- Create summarized burden commitment transactions
3394 Pa_Burden_Costing.create_burden_cmt_transaction
3395 (x_project_id=>projrec.project_id, /* added for bug 2734747 */
3396 status=>x_status,
3397 stage=>x_stage ,
3398 x_run_id=>x_run_id);
3399
3400 Pa_Txn_Accums.accum_commitments
3401 (projrec.project_id, -- start_project_id
3402 projrec.project_id, -- end_project_id
3403 x_system_linkage_function,
3404 x_err_stage,
3405 x_err_code);
3406
3407 Pa_Accum_Utils.update_proj_accum_header
3408 (x_proj_level_accum_id,
3409 x_current_period,
3410 x_err_stack,
3411 x_err_stage,
3412 x_err_code);
3413
3414 -- get the summary number post processing
3415 Pa_Accum_Srw.get_project_txn_numbers
3416 (projrec.project_id, --- project Id
3417 'POST',
3418 table_index,
3419 x_err_stack,
3420 x_err_stage,
3421 x_err_code);
3422
3423 Pa_Accum_Srw.number_of_projects := table_index;
3424
3425 <<next_project>>
3426
3427 -- Check if the reporting period was moved backwards
3428 check_reporting_period
3429 (x_reporting_period_status, x_err_stack, x_err_stage, x_err_code);
3430
3431 IF(x_reporting_period_status = 'Y') THEN
3432
3433 -- Stop processing and exit the procedure return from the procedure
3434 -- after a rollback;
3435 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3436 Pa_Debug.debug('build_txn_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3437 Pa_Debug.debug('build_txn_accum: ' || '******Reporting Period Moved Backward In Time During Process Run',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3438 Pa_Debug.debug('build_txn_accum: ' || '******The Accumulation for the project '|| projrec.segment1 || ' Rolled Back',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3439 Pa_Debug.debug('build_txn_accum: ' || '*********************************************************************************',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3440 END IF;
3441 IF Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
3442 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3443 Pa_Debug.debug('build_txn_accum: ' || 'Releasing the lock on project ...');
3444 END IF;
3445 END IF;
3446 errbuf := 'Reporting Period Moved Backward In Time During Process Run';
3447 retcode := 5;
3448 ROLLBACK WORK;
3449 RETURN;
3450 END IF; -- IF(x_reporting_period_status = 'Y')
3451
3452 COMMIT WORK; -- we are done with this project now
3453 IF Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(projrec.project_id)) = 0 THEN
3454 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3455 Pa_Debug.debug('build_txn_accum: ' || 'Releasing the lock on project ...');
3456 END IF;
3457 END IF;
3458
3459 EXCEPTION
3460
3461 WHEN OTHERS THEN
3462 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
3463 x_err_code := SQLCODE;
3464 retcode := x_err_code;
3465 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
3466 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3467 Pa_Debug.debug('build_txn_accum: ' || 'Exception Generated By Oracle Error: ' || errbuf ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3468 Pa_Debug.debug('build_txn_accum: ' || 'Skipping the accumulation for project ' || projrec.segment1 ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3469 END IF;
3470
3471 -- Exception handling for database errors (other than 1400 and 1489)
3472 IF x_err_code*-1 BETWEEN 1400 AND 1489 THEN
3473 ROLLBACK WORK;
3474 ELSE
3475 RAISE;
3476 END IF;
3477
3478 END; -- end of PL/SQL block for each project
3479
3480 END LOOP;
3481
3482 x_err_stack := x_old_stack;
3483 RETURN;
3484
3485 EXCEPTION
3486
3487 WHEN OTHERS THEN
3488 rel_lock := Pa_Debug.release_user_lock('PA_SUMM:'||TO_CHAR(l_project_id));
3489 x_err_code := SQLCODE;
3490 retcode := x_err_code;
3491 errbuf := SUBSTR(SQLERRM(SQLCODE),1,512);
3492 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3493 Pa_Debug.debug('build_txn_accum: ' || 'Exception Generated By Oracle Error: ' || errbuf ,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3494 END IF;
3495 ROLLBACK WORK;
3496 RETURN;
3497 END build_txn_accum;
3498
3499 PROCEDURE lock_proj_level_header_record
3500 (x_project_id IN NUMBER)
3501 IS
3502 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
3503 dummy_project_id NUMBER;
3504 BEGIN
3505 x_old_stack := x_err_stack;
3506 x_err_stack :=
3507 x_err_stack ||'->pa_proj_accum_main.lock_proj_level_header_record';
3508 x_err_code := 0;
3509 x_err_stage :=
3510 'locking the project level header record for project_id= '||x_project_id;
3511
3512 -- Try to Lock the project level header record with NOWAIT. If some
3513 -- other process is locking the record then raise an exception.
3514
3515 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3516 Pa_Debug.debug('lock_proj_level_header_record: ' || x_err_stack);
3517 END IF;
3518
3519 x_proj_header_locked := FALSE; -- Assume project header is not locked
3520
3521 SELECT
3522 pah.project_id
3523 INTO
3524 dummy_project_id
3525 FROM
3526 pa_project_accum_headers pah
3527 WHERE
3528 pah.project_id = x_project_id
3529 AND pah.task_id = 0
3530 AND pah.resource_list_id = 0
3531 AND pah.resource_list_member_id = 0
3532 AND pah.resource_id = 0
3533 FOR UPDATE OF project_id NOWAIT;
3534
3535 x_err_stack := x_old_stack;
3536
3537 EXCEPTION
3538 -- It may be possible that the project level header record is locked
3539 -- by some other process.
3540
3541 WHEN RECORD_ALREADY_LOCKED THEN
3542 x_proj_header_locked := TRUE;
3543 x_err_stack := x_old_stack;
3544
3545 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3546 Pa_Debug.debug('lock_proj_level_header_record: ' || '******************************************************************************************************',
3547 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3548 Pa_Debug.debug('lock_proj_level_header_record: ' || '****Unable to lock the Project Level Header Record for the Project_id '|| TO_CHAR(x_project_id) ||
3549 ' Skipping this Project for processing ****',Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3550 Pa_Debug.debug('lock_proj_level_header_record: ' || '******************************************************************************************************',
3551 Pa_Debug.DEBUG_LEVEL_EXCEPTION);
3552 END IF;
3553
3554 WHEN OTHERS THEN
3555 x_err_code := SQLCODE;
3556 RAISE;
3557 END lock_proj_level_header_record;
3558
3559 -- Function to get the end date of the current period --
3560 FUNCTION Get_curr_end_date
3561 RETURN DATE
3562 IS
3563 BEGIN
3564 RETURN Pa_Proj_Accum_Main.x_current_end_date;
3565 EXCEPTION
3566 WHEN OTHERS THEN
3567 RAISE;
3568 END Get_curr_end_date;
3569
3570 /*
3571 --Bug 5635857. This procedure will populate pa_commitment_txns_tmp table from
3572 --pa_commitment_txns_v. This table will later be used in processing
3573 --Dynamic Insert statement is used since otherwise its not possible to get and insert
3574 --the data from pa_commitment_txns_v for all the projects in one go.
3575 PROCEDURE populate_cmt_tmp_table
3576 (p_project_id_tbl IN pa_plsql_datatypes.idTabTyp,
3577 p_system_linkage_function IN VARCHAR2)
3578 IS
3579
3580 x_last_updated_by NUMBER(15);
3581 x_last_update_date NUMBER(15);
3582 x_created_by NUMBER(15);
3583 x_last_update_login NUMBER(15);
3584 x_request_id NUMBER(15);
3585 x_program_application_id NUMBER(15);
3586 x_program_id NUMBER(15);
3587 l_sql VARCHAR2(32700);
3588 l_proj_id_condition VARCHAR2(32700);
3589 l_current_pa_period_flag Varchar2(1);
3590
3591 BEGIN
3592
3593 DELETE FROM pa_commitment_txns_tmp;
3594
3595 x_last_updated_by := NVL(FND_GLOBAL.USER_ID,-1);
3596 x_last_update_date := NVL(FND_GLOBAL.USER_ID,-1);
3597 x_created_by := NVL(FND_GLOBAL.USER_ID,-1);
3598 x_last_update_login := NVL(FND_GLOBAL.LOGIN_ID,-1);
3599 x_request_id := NVL(FND_GLOBAL.CONC_REQUEST_ID,-1);
3600 x_program_application_id := NVL(FND_GLOBAL.PROG_APPL_ID,-1);
3601 x_program_id := NVL(FND_GLOBAL.CONC_PROGRAM_ID,-1);
3602
3603 l_sql := NULL;
3604 l_proj_id_condition := NULL;
3605
3606 FOR i IN 1..p_project_id_tbl.COUNT LOOP
3607
3608 IF l_sql IS NULL THEN
3609
3610 l_sql:='INSERT INTO pa_commitment_txns_tmp ' ||
3611 '( PROJECT_ID, '||
3612 'TASK_ID, '||
3613 'TRANSACTION_SOURCE, '||
3614 'LINE_TYPE, '||
3615 'CMT_NUMBER, '||
3616 'CMT_DISTRIBUTION_ID, '||
3617 'CMT_HEADER_ID, '||
3618 'DESCRIPTION, '||
3619 'EXPENDITURE_ITEM_DATE, '||
3620 'PA_PERIOD, '||
3621 'GL_PERIOD, '||
3622 'CMT_LINE_NUMBER, '||
3623 'CMT_CREATION_DATE, '||
3624 'CMT_APPROVED_DATE, '||
3625 'CMT_REQUESTOR_NAME, '||
3626 'CMT_BUYER_NAME, '||
3627 'CMT_APPROVED_FLAG, '||
3628 'CMT_PROMISED_DATE, '||
3629 'CMT_NEED_BY_DATE, '||
3630 'ORGANIZATION_ID, '||
3631 'VENDOR_ID, '||
3632 'VENDOR_NAME, '||
3633 'EXPENDITURE_TYPE, '||
3634 'EXPENDITURE_CATEGORY, '||
3635 'REVENUE_CATEGORY, '||
3636 'SYSTEM_LINKAGE_FUNCTION, '||
3637 'UNIT_OF_MEASURE, '||
3638 'UNIT_PRICE, '||
3639 'CMT_IND_COMPILED_SET_ID, '||
3640 'TOT_CMT_QUANTITY, '||
3641 'QUANTITY_ORDERED, '||
3642 'AMOUNT_ORDERED, '||
3643 'ORIGINAL_QUANTITY_ORDERED, '||
3644 'ORIGINAL_AMOUNT_ORDERED, '||
3645 'QUANTITY_CANCELLED, '||
3646 'AMOUNT_CANCELLED, '||
3647 'QUANTITY_DELIVERED, '||
3648 'QUANTITY_INVOICED, '||
3649 'AMOUNT_INVOICED, '||
3650 'QUANTITY_OUTSTANDING_DELIVERY, '||
3651 'AMOUNT_OUTSTANDING_DELIVERY, '||
3652 'QUANTITY_OUTSTANDING_INVOICE, '||
3653 'AMOUNT_OUTSTANDING_INVOICE, '||
3654 'QUANTITY_OVERBILLED, '||
3655 'AMOUNT_OVERBILLED, '||
3656 'ORIGINAL_TXN_REFERENCE1, '||
3657 'ORIGINAL_TXN_REFERENCE2, '||
3658 'ORIGINAL_TXN_REFERENCE3, '||
3659 'LAST_UPDATE_DATE, '||
3660 'LAST_UPDATED_BY, '||
3661 'CREATION_DATE, '||
3662 'CREATED_BY, '||
3663 'LAST_UPDATE_LOGIN, '||
3664 'REQUEST_ID, '||
3665 'PROGRAM_APPLICATION_ID, '||
3666 'PROGRAM_ID, '||
3667 'PROGRAM_UPDATE_DATE, '||
3668 'acct_raw_cost, '||
3669 'acct_burdened_cost, '||
3670 'denom_currency_code, '||
3671 'denom_raw_cost, '||
3672 'denom_burdened_cost, '||
3673 'acct_currency_code, '||
3674 'acct_rate_date, '||
3675 'acct_rate_type, '||
3676 'acct_exchange_rate, '||
3677 'receipt_currency_code, '||
3678 'receipt_currency_amount, '||
3679 'receipt_exchange_rate, '||
3680 'INVENTORY_ITEM_ID, '||
3681 'UOM_CODE, '||
3682 'wip_resource_id, '||
3683 'resource_class '||
3684 ') '||
3685 'SELECT '||
3686 'pctv.project_id, '||
3687 'pctv.task_id, '||
3688 'pctv.transaction_source, '||
3689 'decode(pctv.line_type,''P'',''P'',''R'',''R'',''I'',''I'',''O''), '||
3690 'pctv.cmt_number, '||
3691 'pctv.cmt_distribution_id, '||
3692 'pctv.cmt_header_id, '||
3693 'pctv.description, '||
3694 'pctv.expenditure_item_date, '||
3695 'pctv.pa_period, '||
3696 'pctv.gl_period, '||
3697 'pctv.cmt_line_number, '||
3698 'pctv.cmt_creation_date, '||
3699 'pctv.cmt_approved_date, '||
3700 'pctv.cmt_requestor_name, '||
3701 'pctv.cmt_buyer_name, '||
3702 'pctv.cmt_approved_flag, '||
3703 'pctv.cmt_promised_date, '||
3704 'pctv.cmt_need_by_date, '||
3705 'pctv.organization_id, '||
3706 'pctv.vendor_id, '||
3707 'pctv.vendor_name, '||
3708 'pctv.expenditure_type, '||
3709 'pctv.expenditure_category, '||
3710 'pctv.revenue_category, '||
3711 'pctv.system_linkage_function, '||
3712 'pctv.unit_of_measure, '||
3713 'pctv.unit_price, '||
3714 'pctv.cmt_ind_compiled_set_id, '||
3715 'pctv.tot_cmt_quantity, '||
3716 'pctv.quantity_ordered, '||
3717 'pctv.amount_ordered, '||
3718 'pctv.original_quantity_ordered, '||
3719 'pctv.original_amount_ordered, '||
3720 'pctv.quantity_cancelled, '||
3721 'pctv.amount_cancelled, '||
3722 'pctv.quantity_delivered, '||
3723 'pctv.quantity_invoiced, '||
3724 'pctv.amount_invoiced, '||
3725 'pctv.quantity_outstanding_delivery, '||
3726 'pctv.amount_outstanding_delivery, '||
3727 'pctv.quantity_outstanding_invoice, '||
3728 'pctv.amount_outstanding_invoice, '||
3729 'pctv.quantity_overbilled, '||
3730 'pctv.amount_overbilled, '||
3731 'pctv.original_txn_reference1, '||
3732 'pctv.original_txn_reference2, '||
3733 'pctv.original_txn_reference3, '||
3734 'SYSDATE, '||
3735 x_last_updated_by||', '||
3736 'SYSDATE, '||
3737 x_created_by||', '||
3738 x_last_update_login||', '||
3739 x_request_id||', '||
3740 x_program_application_id||', '||
3741 x_program_id||', '||
3742 'NULL, '||
3743 'pctv.acct_raw_cost, '||
3744 'pctv.acct_burdened_cost, '||
3745 'pctv.denom_currency_code, '||
3746 'pctv.denom_raw_cost, '||
3747 'pctv.denom_burdened_cost, '||
3748 'pctv.acct_currency_code, '||
3749 'pctv.acct_rate_date, '||
3750 'pctv.acct_rate_type, '||
3751 'pctv.acct_exchange_rate, '||
3752 'pctv.receipt_currency_code, '||
3753 'pctv.receipt_currency_amount, '||
3754 'pctv.receipt_exchange_rate, '||
3755 'pctv.INVENTORY_ITEM_ID, '||
3756 'pctv.UOM_CODE, '||
3757 'pctv.wip_resource_id, '||
3758 'pctv.resource_class '||
3759 'FROM '||
3760 'pa_commitment_txns_v pctv '||
3761 'WHERE ' ;
3762 IF p_system_linkage_function IS NOT NULL THEN
3763
3764 l_sql := l_sql || 'NVL(pctv.system_linkage_function,''X'') = '''||
3765 p_system_linkage_function ||''' AND ';
3766 END IF;
3767 l_sql := l_sql || ' pctv.project_id IN (';
3768
3769 END IF;
3770
3771 IF l_proj_id_condition IS NULL THEN
3772
3773 l_proj_id_condition := p_project_id_tbl(i);
3774
3775 ELSE
3776
3777 l_proj_id_condition := l_proj_id_condition ||', '|| p_project_id_tbl(i);
3778
3779 END IF;
3780
3781 IF LENGTH (l_sql || l_proj_id_condition) >32000 THEN
3782
3783 l_sql := l_sql || l_proj_id_condition ||' )';
3784
3785 EXECUTE IMMEDIATE l_sql;
3786
3787 l_sql := NULL;
3788 l_proj_id_condition := NULL;
3789
3790 END IF;
3791
3792 END LOOP;
3793
3794
3795 IF NVL(LENGTH(l_sql||l_proj_id_condition),0)>0 THEN
3796
3797 l_sql := l_sql || l_proj_id_condition ||' )';
3798
3799 EXECUTE IMMEDIATE l_sql;
3800
3801 l_sql := NULL;
3802 l_proj_id_condition := NULL;
3803
3804 END IF;
3805
3806 END populate_cmt_tmp_table; */
3807
3808 /* Procedure added for bug#6408874 */
3809 PROCEDURE populate_cmt_tmp_table
3810 (p_project_num_from IN VARCHAR2,
3811 p_project_num_to IN VARCHAR2,
3812 p_system_linkage_function IN VARCHAR2,
3813 p_refresh_flag IN VARCHAR2, -- Added for Bug# 7175975
3814 p_project_type IN VARCHAR2) -- Added for Bug# 7175975
3815 IS
3816
3817 x_last_updated_by NUMBER(15);
3818 x_last_update_date NUMBER(15);
3819 x_created_by NUMBER(15);
3820 x_last_update_login NUMBER(15);
3821 x_request_id NUMBER(15);
3822 x_program_application_id NUMBER(15);
3823 x_program_id NUMBER(15);
3824 l_sql VARCHAR2(32700);
3825 l_proj_id_condition VARCHAR2(32700);
3826
3827 L_CNT NUMBER;
3828 TmpProjectTab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); --bug 6408874
3829 BEGIN
3830
3831 DELETE FROM pa_commitment_txns_tmp;
3832 DELETE FROM pa_pjm_po_commitments_tmp; /* Bug 6408874 */
3833 DELETE FROM pa_pjm_req_commitments_tmp; /* Bug 6408874 */
3834 -- DELETE FROM pa_fp_ra_map_tmp;
3835
3836 x_last_updated_by := NVL(FND_GLOBAL.USER_ID,-1);
3837 x_last_update_date := NVL(FND_GLOBAL.USER_ID,-1);
3838 x_created_by := NVL(FND_GLOBAL.USER_ID,-1);
3839 x_last_update_login := NVL(FND_GLOBAL.LOGIN_ID,-1);
3840 x_request_id := NVL(FND_GLOBAL.CONC_REQUEST_ID,-1);
3841 x_program_application_id := NVL(FND_GLOBAL.PROG_APPL_ID,-1);
3842 x_program_id := NVL(FND_GLOBAL.CONC_PROGRAM_ID,-1);
3843
3844 l_sql := NULL;
3845 l_proj_id_condition := NULL;
3846
3847 IF (p_refresh_flag = 'N') THEN --Condition via bug 7175975
3848 -- bug 6408874. Introduced BULK Processing
3849 SELECT DISTINCT pfa.project_id project_id
3850 bulk collect into TmpProjectTab
3851 FROM pa_projects_for_accum pfa
3852 WHERE pfa.request_id = x_request_id
3853 AND pfa.segment1 BETWEEN p_project_num_from AND p_project_num_to
3854 AND pfa.action_flag = 'CM'
3855 AND NOT EXISTS (SELECT pf.project_id
3856 FROM pa_projects_for_accum pf
3857 WHERE pf.request_id = pfa.request_id
3858 AND pf.segment1 = pfa.segment1
3859 AND pf.action_flag IN ('PT', 'CL', 'BK', 'TR'));
3860 -- Added below for Bug# 7175975
3861 ELSIF (p_refresh_flag = 'Y') THEN
3862 SELECT ppr.project_id
3863 BULK COLLECT INTO TmpProjectTab
3864 FROM pa_projects ppr
3865 WHERE NVL(ppr.template_flag,'N') <> 'Y'
3866 AND ppr.segment1 BETWEEN p_project_num_from AND p_project_num_to
3867 AND ppr.project_type = NVL(p_project_type, project_type)
3868 AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code) = 'N';
3869 END IF;
3870 -- End for Bug# 7175975
3871
3872 /* Bug 6408874 */
3873 forall i in 1..TmpProjectTab.count --commented for bug 6408874, put back for bug 7491714
3874 insert into pa_pjm_req_commitments_tmp
3875 ( project_id
3876 ,task_id
3877 ,req_number
3878 ,req_distribution_id
3879 ,requisition_header_id
3880 ,item_description
3881 ,expenditure_item_date
3882 ,req_line
3883 ,creation_date
3884 ,requestor_name
3885 ,approved_flag
3886 ,need_by_date
3887 ,expenditure_organization_id
3888 ,vendor_id
3889 ,vendor_name
3890 ,expenditure_type
3891 ,expenditure_category
3892 ,revenue_category
3893 ,unit_of_measure
3894 ,unit_price
3895 ,amount
3896 ,quantity
3897 ,denom_currency_code
3898 ,denom_amount
3899 ,acct_currency_code
3900 ,acct_rate_date
3901 ,acct_rate_type
3902 ,acct_exchange_rate
3903 ,inventory_item_id
3904 ,inventory_item_name
3905 ,uom_code
3906 ,wip_resource_id
3907 ,wip_resource_name
3908 )
3909 select
3910 pmprd.project_id
3911 ,pmprd.task_id
3912 ,pmprd.req_number
3913 ,pmprd.req_distribution_id
3914 ,pmprd.requisition_header_id
3915 ,pmprd.item_description
3916 ,pmprd.expenditure_item_date
3917 ,pmprd.req_line
3918 ,pmprd.creation_date
3919 ,pmprd.requestor_name
3920 ,pmprd.approved_flag
3921 ,pmprd.need_by_date
3922 ,pmprd.expenditure_organization_id
3923 ,pmprd.vendor_id
3924 ,pmprd.vendor_name
3925 ,pmprd.expenditure_type
3926 ,pmprd.expenditure_category
3927 ,pmprd.revenue_category
3928 ,pmprd.unit_of_measure
3929 ,pmprd.unit_price
3930 ,pmprd.amount
3931 ,pmprd.quantity
3932 ,pmprd.denom_currency_code
3933 ,pmprd.denom_amount
3934 ,pmprd.acct_currency_code
3935 ,pmprd.acct_rate_date
3936 ,pmprd.acct_rate_type
3937 ,pmprd.acct_exchange_rate
3938 ,pmprd.inventory_item_id
3939 ,pmprd.inventory_item_name
3940 ,pmprd.uom_code
3941 ,pmprd.wip_resource_id
3942 ,pmprd.wip_resource_name
3943 from pjm_req_commitments_v pmprd -- bug 7491714
3944 where pmprd.project_id = TmpProjectTab(i);
3945 -- commented for bug 6408874
3946 -- where pmprd.project_id = TmpProjectTab(i);
3947
3948
3949 forall i in 1..TmpProjectTab.count -- commented for bug 6408874, back for bug 7491714
3950 insert into pa_pjm_po_commitments_tmp
3951 (project_id
3952 ,task_id
3953 ,po_number
3954 ,po_distribution_id
3955 ,po_header_id
3956 ,item_description
3957 ,expenditure_item_date
3958 ,po_line
3959 ,creation_date
3960 ,approved_date
3961 ,requestor_name
3962 ,buyer_name
3963 ,approved_flag
3964 ,expenditure_organization_id
3965 ,vendor_id
3966 ,vendor_name
3967 ,expenditure_type
3968 ,expenditure_category
3969 ,revenue_category
3970 ,unit_of_measure
3971 ,unit_price
3972 ,amount_outstanding_delivery
3973 ,quantity_outstanding_delivery
3974 ,quantity_ordered
3975 ,amount_ordered
3976 ,original_quantity_ordered
3977 ,original_amount_ordered
3978 ,quantity_cancelled
3979 ,amount_cancelled
3980 ,quantity_delivered
3981 ,quantity_invoiced
3982 ,amount_invoiced
3983 ,quantity_outstanding_invoice
3984 ,amount_outstanding_invoice
3985 ,quantity_overbilled
3986 ,amount_overbilled
3987 ,denom_currency_code
3988 ,denom_amt_outstanding_delivery
3989 ,acct_currency_code
3990 ,acct_rate_date
3991 ,acct_rate_type
3992 ,acct_exchange_rate
3993 ,inventory_item_id
3994 ,inventory_item_name
3995 ,uom_code
3996 ,wip_resource_id
3997 ,wip_resource_name
3998 )
3999 select
4000 pmppd.project_id,
4001 pmppd.task_id,
4002 pmppd.po_number,
4003 pmppd.po_distribution_id,
4004 pmppd.po_header_id,
4005 pmppd.item_description,
4006 pmppd.expenditure_item_date,
4007 pmppd.po_line,
4008 pmppd.creation_date,
4009 pmppd.approved_date,
4010 pmppd.requestor_name,
4011 pmppd.buyer_name,
4012 pmppd.approved_flag,
4013 pmppd.expenditure_organization_id,
4014 pmppd.vendor_id,
4015 pmppd.vendor_name,
4016 pmppd.expenditure_type,
4017 pmppd.expenditure_category,
4018 pmppd.revenue_category,
4019 pmppd.unit_of_measure,
4020 pmppd.unit_price,
4021 pmppd.amount_outstanding_delivery,
4022 pmppd.quantity_outstanding_delivery,
4023 pmppd.quantity_ordered,
4024 pmppd.amount_ordered,
4025 pmppd.original_quantity_ordered,
4026 pmppd.original_amount_ordered,
4027 pmppd.quantity_cancelled,
4028 pmppd.amount_cancelled,
4029 pmppd.quantity_delivered,
4030 pmppd.quantity_invoiced,
4031 pmppd.amount_invoiced,
4032 pmppd.quantity_outstanding_invoice,
4033 pmppd.amount_outstanding_invoice,
4034 pmppd.quantity_overbilled,
4035 pmppd.amount_overbilled,
4036 pmppd.denom_currency_code,
4037 pmppd.denom_amt_outstanding_delivery,
4038 pmppd.acct_currency_code,
4039 pmppd.acct_rate_date,
4040 pmppd.acct_rate_type,
4041 pmppd.acct_exchange_rate,
4042 pmppd.inventory_item_id,
4043 pmppd.inventory_item_name,
4044 pmppd.uom_code,
4045 pmppd.wip_resource_id,
4046 pmppd.wip_resource_name
4047 from pjm_po_commitments_v pmppd
4048 where pmppd.project_id = TmpProjectTab(i); -- bug 7491714
4049 -- commented for bug 6408874
4050 -- where pmppd.project_id = TmpProjectTab(i);
4051
4052 /* Bug 6408874 */
4053
4054 forall i in 1..TmpProjectTab.count
4055 INSERT INTO pa_commitment_txns_tmp
4056 (PROJECT_ID,
4057 TASK_ID,
4058 TRANSACTION_SOURCE,
4059 LINE_TYPE,
4060 CMT_NUMBER,
4061 CMT_DISTRIBUTION_ID,
4062 CMT_HEADER_ID,
4063 DESCRIPTION,
4064 EXPENDITURE_ITEM_DATE,
4065 PA_PERIOD,
4066 GL_PERIOD,
4067 CMT_LINE_NUMBER,
4068 CMT_CREATION_DATE,
4069 CMT_APPROVED_DATE,
4070 CMT_REQUESTOR_NAME,
4071 CMT_BUYER_NAME,
4072 CMT_APPROVED_FLAG,
4073 CMT_PROMISED_DATE,
4074 CMT_NEED_BY_DATE,
4075 ORGANIZATION_ID,
4076 VENDOR_ID,
4077 VENDOR_NAME,
4078 EXPENDITURE_TYPE,
4079 EXPENDITURE_CATEGORY,
4080 REVENUE_CATEGORY,
4081 SYSTEM_LINKAGE_FUNCTION,
4082 UNIT_OF_MEASURE,
4083 UNIT_PRICE,
4084 CMT_IND_COMPILED_SET_ID,
4085 TOT_CMT_QUANTITY,
4086 QUANTITY_ORDERED,
4087 AMOUNT_ORDERED,
4088 ORIGINAL_QUANTITY_ORDERED,
4089 ORIGINAL_AMOUNT_ORDERED,
4090 QUANTITY_CANCELLED,
4091 AMOUNT_CANCELLED,
4092 QUANTITY_DELIVERED,
4093 QUANTITY_INVOICED,
4094 AMOUNT_INVOICED,
4095 QUANTITY_OUTSTANDING_DELIVERY,
4096 AMOUNT_OUTSTANDING_DELIVERY,
4097 QUANTITY_OUTSTANDING_INVOICE,
4098 AMOUNT_OUTSTANDING_INVOICE,
4099 QUANTITY_OVERBILLED,
4100 AMOUNT_OVERBILLED,
4101 ORIGINAL_TXN_REFERENCE1,
4102 ORIGINAL_TXN_REFERENCE2,
4103 ORIGINAL_TXN_REFERENCE3,
4104 LAST_UPDATE_DATE,
4105 LAST_UPDATED_BY,
4106 CREATION_DATE,
4107 CREATED_BY,
4108 LAST_UPDATE_LOGIN,
4109 REQUEST_ID,
4110 PROGRAM_APPLICATION_ID,
4111 PROGRAM_ID,
4112 PROGRAM_UPDATE_DATE,
4113 acct_raw_cost,
4114 acct_burdened_cost,
4115 denom_currency_code,
4116 denom_raw_cost,
4117 denom_burdened_cost,
4118 acct_currency_code,
4119 acct_rate_date,
4120 acct_rate_type,
4121 acct_exchange_rate,
4122 receipt_currency_code,
4123 receipt_currency_amount,
4124 receipt_exchange_rate,
4125 inventory_item_id,
4126 uom_code,
4127 wip_resource_id,
4128 resource_class
4129 )
4130 SELECT
4131 pctv.project_id,
4132 pctv.task_id,
4133 pctv.transaction_source,
4134 pctv.line_type,
4135 pctv.cmt_number,
4136 pctv.cmt_distribution_id,
4137 pctv.cmt_header_id,
4138 pctv.description,
4139 pctv.expenditure_item_date,
4140 pctv.pa_period,
4141 pctv.gl_period,
4142 pctv.cmt_line_number,
4143 pctv.cmt_creation_date,
4144 pctv.cmt_approved_date,
4145 pctv.cmt_requestor_name,
4146 pctv.cmt_buyer_name,
4147 pctv.cmt_approved_flag,
4148 pctv.cmt_promised_date,
4149 pctv.cmt_need_by_date,
4150 pctv.organization_id,
4151 pctv.vendor_id,
4152 pctv.vendor_name,
4153 pctv.expenditure_type,
4154 pctv.expenditure_category,
4155 pctv.revenue_category,
4156 pctv.system_linkage_function,
4157 pctv.unit_of_measure,
4158 pctv.unit_price,
4159 pctv.cmt_ind_compiled_set_id,
4160 pctv.tot_cmt_quantity,
4161 pctv.quantity_ordered,
4162 pctv.amount_ordered,
4163 pctv.original_quantity_ordered,
4164 pctv.original_amount_ordered,
4165 pctv.quantity_cancelled,
4166 pctv.amount_cancelled,
4167 pctv.quantity_delivered,
4168 pctv.quantity_invoiced,
4169 pctv.amount_invoiced,
4170 pctv.quantity_outstanding_delivery,
4171 pctv.amount_outstanding_delivery,
4172 pctv.quantity_outstanding_invoice,
4173 pctv.amount_outstanding_invoice,
4174 pctv.quantity_overbilled,
4175 pctv.amount_overbilled,
4176 pctv.original_txn_reference1,
4177 pctv.original_txn_reference2,
4178 pctv.original_txn_reference3,
4179 SYSDATE,
4180 x_last_updated_by,
4181 SYSDATE,
4182 x_created_by,
4183 x_last_update_login,
4184 x_request_id,
4185 x_program_application_id,
4186 x_program_id,
4187 NULL,
4188 pctv.acct_raw_cost,
4189 pctv.acct_burdened_cost,
4190 pctv.denom_currency_code,
4191 pctv.denom_raw_cost,
4192 pctv.denom_burdened_cost,
4193 pctv.acct_currency_code,
4194 pctv.acct_rate_date,
4195 pctv.acct_rate_type,
4196 pctv.acct_exchange_rate,
4197 pctv.receipt_currency_code,
4198 pctv.receipt_currency_amount,
4199 pctv.receipt_exchange_rate,
4200 pctv.inventory_item_id,
4201 pctv.uom_code,
4202 pctv.wip_resource_id,
4203 pctv.resource_class
4204 FROM pa_commitments_v pctv
4205 WHERE pctv.project_id = TmpProjectTab(i) --tmp.project_id
4206 AND NVL(pctv.system_linkage_function,'X') = NVL(p_system_linkage_function,NVL(pctv.system_linkage_function,'X'));
4207
4208 END populate_cmt_tmp_table;
4209
4210 /* Procedure added for bug#6408874 */
4211
4212 END Pa_Proj_Accum_Main;