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