DBA Data[Home] [Help]

PACKAGE BODY: APPS.PA_REP_UTILS_SUMM_PKG

Source


1 PACKAGE BODY PA_REP_UTILS_SUMM_PKG as
2 /* $Header: PARRSUMB.pls 120.0.12010000.2 2009/05/26 12:46:52 nisinha ship $ */
3 
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option  variable initialization for bug#2674619 */
5 
6 /*
7  * This variable is populated by the public procedure of this
8  * package.
9  */
10 l_balance_type_code            VARCHAR2(30);
11 
12 /*
13  * Cache all object types for insert.
14  */
15 l_org_c      VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_ORG_C;
16 l_orguc_c    VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_ORGUC_C;
17 l_orgwt_c    VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_ORGWT_C;
18 l_res_c      VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RES_C;
19 l_resuco_c   VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RESUCO_C;
20 l_resucr_c   VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RESUCR_C;
21 l_reswt_c    VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_RESWT_C;
22 l_utildet_c  VARCHAR2(15) := PA_REP_UTIL_GLOB.G_OBJ_TYPE_C.G_UTILDET_C;
23 /*
24  * End Caching object types.
25  */
26 
27 /*
28  * Cache Amount Type Id.
29  */
30 l_tot_hrs_id              pa_amount_types_b.amount_type_id%TYPE
31               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_hrs_id;
32 l_tot_prov_hrs_id         pa_amount_types_b.amount_type_id%TYPE
33               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_prvhrs_id;
34 l_tot_wght_hrs_people_id  pa_amount_types_b.amount_type_id%TYPE
35               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_wtdhrs_people_id;
36 l_tot_wght_hrs_org_id     pa_amount_types_b.amount_type_id%TYPE
37               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_wtdhrs_org_id;
38 l_prov_wght_hrs_people_id pa_amount_types_b.amount_type_id%TYPE
39               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_prvwtdhrs_people_id;
40 l_prov_wght_hrs_org_id    pa_amount_types_b.amount_type_id%TYPE
41               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_prvwtdhrs_org_id;
42 l_red_cap_id              pa_amount_types_b.amount_type_id%TYPE
43               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_reducedcap_id;
44 l_tot_cap_id              pa_amount_types_b.amount_type_id%TYPE
45               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_cap_id;
46 /*
47  * End Caching.
48  */
49 
50 /*
51  * Delete Flag identifying whether any deleted record exists in
52  * pa_rep_util_summ_tmp.
53  */
54  l_delete_flag       VARCHAR2(1);
55 
56 /*
57  * Cache the Expenditure Org Id.
58  */
59 l_exp_org_id   NUMBER := PA_REP_UTIL_GLOB.G_implementation_details.G_org_id;
60 
61 /*
62  * Cache the concurrent program related globals.
63  */
64 l_last_updated_by   NUMBER := PA_REP_UTIL_GLOB.G_who_columns.G_last_updated_by;
65 l_created_by        NUMBER := PA_REP_UTIL_GLOB.G_who_columns.G_created_by;
66 l_creation_date     DATE   := PA_REP_UTIL_GLOB.G_who_columns.G_creation_date;
67 l_last_update_date  DATE   := PA_REP_UTIL_GLOB.G_who_columns.G_last_update_date;
68 l_last_update_login NUMBER := PA_REP_UTIL_GLOB.G_who_columns.G_program_application_id;
69 l_request_id        NUMBER := PA_REP_UTIL_GLOB.G_who_columns.G_request_id;
70 l_program_id        NUMBER := PA_REP_UTIL_GLOB.G_who_columns.G_program_id;
71 l_program_application_id   NUMBER
72                     := PA_REP_UTIL_GLOB.G_who_columns.G_program_application_id;
73 
74 /*
75  * End Caching Who Columns.
76  */
77 
78 /*
79  * Cache period set name and UOM.
80  */
81 --l_period_set_name  gl_sets_of_books.period_set_name%TYPE
82 --               := PA_REP_UTIL_GLOB.G_implementation_details.G_period_set_name;
83 l_gl_period_set_name  gl_sets_of_books.period_set_name%TYPE
84                := PA_REP_UTIL_GLOB.G_implementation_details.G_gl_period_set_name; -- Bug 3434019
85 l_pa_period_set_name  gl_sets_of_books.period_set_name%TYPE
86                := PA_REP_UTIL_GLOB.G_implementation_details.G_pa_period_set_name; -- Bug 3434019
87 l_unit_of_measure   VARCHAR2(10) := PA_REP_UTIL_GLOB.G_UNIT_OF_MEASURE_HRS_C;
88 
89 /*
90  * Cache Period Type.
91  */
92 l_gl_c  VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_GL_C;
93 l_pa_c  VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_PA_C;
94 l_ge_c  VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_GE_C;
95 /*
96  * End Caching Period Type.
97  */
98 
99 /*
100  * Dummy period set name and period name.
101  */
102 l_dummy_period_set_name   VARCHAR2(15) := PA_REP_UTIL_GLOB.G_DUMMY_C;
103 l_dummy_period_name       VARCHAR2(15) := PA_REP_UTIL_GLOB.G_DUMMY_C;
104 l_dummy_ge_date           DATE         := PA_REP_UTIL_GLOB.G_DUMMY_DATE_C;
105 
106 /*
107  * Cache org level direct amount type Id.
108  */
109 l_dirct_tot_hrs_id            pa_amount_types_b.amount_type_id%TYPE
110            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_hrs_id;
111 l_dirct_tot_prov_hrs_id       pa_amount_types_b.amount_type_id%TYPE
112            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_prvhrs_id;
113 l_dirct_tot_wght_hrs_org_id   pa_amount_types_b.amount_type_id%TYPE
114            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_wtdhrs_org_id;
115 l_dirct_prov_wght_hrs_org_id  pa_amount_types_b.amount_type_id%TYPE
116            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_prvwtdhrs_org_id;
117 l_dirct_cap_id                pa_amount_types_b.amount_type_id%TYPE
118            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_cap_id;
119 l_dirct_reduce_cap_id         pa_amount_types_b.amount_type_id%TYPE
120            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_reducedcap_id;
121 /*
122  * End Cache org level direct amount type Id.
123  */
124 
125 /*
126  * Cache Incremental Method flag.
127  */
128 l_org_rollup_method    VARCHAR2(1)
129                      := PA_REP_UTIL_GLOB.G_input_parameters.G_org_rollup_method;
130 /*
131  * Cache Actual and Forecast balance type Constants.
132  */
133 l_actual_c      VARCHAR2(15) := PA_REP_UTIL_GLOB.G_BAL_TYPE_C.G_ACTUALS_C;
134 l_forecast_c    VARCHAR2(15) := PA_REP_UTIL_GLOB.G_BAL_TYPE_C.G_FORECAST_C;
135 
136 /*
137  * Predefination of local procedure.
138  */
139 PROCEDURE populate_incremental_rollup;
140 
141 /*
142  * This procedure reads data from global table, summarize by
143  * PA_PERIOD ,GL Period or Global Expenditure week based on the
144  * global setup data. This procedure has two steps -
145  *   1. It loads data into a temporary table pa_rep_util_summ_tmp
146  *      from global PL/SQL Table.
147  *   2. Summarize the data by period depending on setup and populate
148  *      a PL/SQL Table.
149  */
150 
151 PROCEDURE summarize_by_period
152 IS
153    i          PLS_INTEGER;
154 BEGIN
155 
156    PA_DEBUG.set_curr_function('summarize_by_period');
157    /*
158     * Step 1 - Populate the global temporary table from individual PL/SQL Table
159     * in bulk.
160     */
161 
162     /*
163      * Separate the SQL for delete flag = 'A' and <>'A'
164      * 'A' - means all records from pa_rep_util_summ0_tmp should be processed.
165      * 'F' - means only non deleted record will be processed .
166      */
167 
168    IF ( l_delete_flag <> 'A')
169    THEN
170 
171       /*
172        * Populate pa_rep_util_summ_tmp for Total Hours.
173        */
174        IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
175       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Hours ';
176       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
177       END IF;
178       INSERT INTO pa_rep_util_summ_tmp
179        (  RECORD_TYPE
180           ,EXPENDITURE_ORGANIZATION_ID
181           ,PERSON_ID
182           ,ASSIGNMENT_ID
183           ,WORK_TYPE_ID
184           ,ORG_UTIL_CATEGORY_ID
185           ,RES_UTIL_CATEGORY_ID
186           ,EXPENDITURE_TYPE
187           ,EXPENDITURE_TYPE_CLASS
188           ,PA_PERIOD_NAME
189           ,PA_PERIOD_NUM
190           ,PA_PERIOD_YEAR
191           ,PA_QUARTER_NUMBER
192           ,GL_PERIOD_NAME
193           ,GL_PERIOD_NUM
194           ,GL_PERIOD_YEAR
195           ,GL_QUARTER_NUMBER
196           ,GLOBAL_EXP_PERIOD_END_DATE
197           ,GLOBAL_EXP_YEAR
198           ,GLOBAL_EXP_MONTH_NUMBER
199           ,AMOUNT_TYPE_ID
200           ,PERIOD_BALANCE
201           ,OBJECT_ID
202           ,VERSION_ID
203           ,OBJECT_TYPE_CODE
204           ,BALANCE_TYPE_CODE
205           ,EXPENDITURE_ORG_ID
206           ,PERIOD_TYPE
207           ,PERIOD_SET_NAME
208           ,PERIOD_NAME
209           ,PERIOD_NUM
210           ,PERIOD_YEAR
211           ,QUARTER_OR_MONTH_NUMBER
212           ,UNIT_OF_MEASURE
213           ,SUMM_LEVEL_FLAG
214           ,PROCESS_MODE_FLAG
215           )
216       SELECT 'TMP1',
217              EXPENDITURE_ORGANIZATION_ID,
218              PERSON_ID,
219              ASSIGNMENT_ID,
220              WORK_TYPE_ID,
221              ORG_UTIL_CATEGORY_ID,
222              RES_UTIL_CATEGORY_ID,
223              EXPENDITURE_TYPE,
224              EXPENDITURE_TYPE_CLASS,
225              PA_PERIOD_NAME,
226              PA_PERIOD_NUM,
227              PA_PERIOD_YEAR,
228              PA_QUARTER_NUMBER,
229              GL_PERIOD_NAME,
230              GL_PERIOD_NUM,
231              GL_PERIOD_YEAR,
232              GL_QUARTER_NUMBER,
233              GLOBAL_EXP_PERIOD_END_DATE,
234              GLOBAL_EXP_YEAR,
235              GLOBAL_EXP_MONTH_NUMBER,
236              l_tot_hrs_id,
237              TOTAL_HOURS,
238              NULL,
239              NULL,
240              NULL,
241              NULL,
242              NULL,
243              NULL,
244              NULL,
245              NULL,
246              NULL,
247              NULL,
248              NULL,
249              NULL,
250              'N',
251              'NN'
252        FROM  pa_rep_util_summ0_tmp
253        WHERE DELETE_FLAG     = 'N'
254        AND   TOTAL_HOURS     <> 0;
255 
256 
257       IF (l_balance_type_code <> l_actual_c) THEN
258 
259         /*
260          * Populate pa_rep_util_summ_tmp for Total Provisional Hours.
261          */
262         IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
263         PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Prov Hours';
264         PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
265 	END IF;
266 
267         INSERT INTO pa_rep_util_summ_tmp
268          (  RECORD_TYPE
269             ,EXPENDITURE_ORGANIZATION_ID
270             ,PERSON_ID
271             ,ASSIGNMENT_ID
272             ,WORK_TYPE_ID
273             ,ORG_UTIL_CATEGORY_ID
274             ,RES_UTIL_CATEGORY_ID
275             ,EXPENDITURE_TYPE
276             ,EXPENDITURE_TYPE_CLASS
277             ,PA_PERIOD_NAME
278             ,PA_PERIOD_NUM
279             ,PA_PERIOD_YEAR
280             ,PA_QUARTER_NUMBER
281             ,GL_PERIOD_NAME
282             ,GL_PERIOD_NUM
283             ,GL_PERIOD_YEAR
284             ,GL_QUARTER_NUMBER
285             ,GLOBAL_EXP_PERIOD_END_DATE
286             ,GLOBAL_EXP_YEAR
287             ,GLOBAL_EXP_MONTH_NUMBER
288             ,AMOUNT_TYPE_ID
289             ,PERIOD_BALANCE
290             ,OBJECT_ID
291             ,VERSION_ID
292             ,OBJECT_TYPE_CODE
293             ,BALANCE_TYPE_CODE
294             ,EXPENDITURE_ORG_ID
295             ,PERIOD_TYPE
296             ,PERIOD_SET_NAME
297             ,PERIOD_NAME
298             ,PERIOD_NUM
299             ,PERIOD_YEAR
300             ,QUARTER_OR_MONTH_NUMBER
301             ,UNIT_OF_MEASURE
302             ,SUMM_LEVEL_FLAG
303             ,PROCESS_MODE_FLAG)
304         SELECT 'TMP1',
305                EXPENDITURE_ORGANIZATION_ID,
306                PERSON_ID,
307                ASSIGNMENT_ID,
308                WORK_TYPE_ID,
309                ORG_UTIL_CATEGORY_ID,
310                RES_UTIL_CATEGORY_ID,
311                EXPENDITURE_TYPE,
312                EXPENDITURE_TYPE_CLASS,
313                PA_PERIOD_NAME,
314                PA_PERIOD_NUM,
315                PA_PERIOD_YEAR,
316                PA_QUARTER_NUMBER,
317                GL_PERIOD_NAME,
318                GL_PERIOD_NUM,
319                GL_PERIOD_YEAR,
320                GL_QUARTER_NUMBER,
321                GLOBAL_EXP_PERIOD_END_DATE,
322                GLOBAL_EXP_YEAR,
323                GLOBAL_EXP_MONTH_NUMBER,
324                l_tot_prov_hrs_id,
325                TOTAL_PROV_HOURS,
326                NULL,
327                NULL,
328                NULL,
329                NULL,
330                NULL,
331                NULL,
332                NULL,
333                NULL,
334                NULL,
335                NULL,
336                NULL,
337                NULL,
338                'N',
339                'NN'
340          FROM  pa_rep_util_summ0_tmp
341          WHERE DELETE_FLAG     = 'N'
342          AND   TOTAL_PROV_HOURS <> 0;
343       END IF;
344 
345       /*
346        * Populate pa_rep_util_summ_tmp for Total Weighted Hours - People.
347        */
348       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
349       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Weighted Hours-People';
350       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
351       END IF;
352 
353       INSERT INTO pa_rep_util_summ_tmp
354        (  RECORD_TYPE
355           ,EXPENDITURE_ORGANIZATION_ID
356           ,PERSON_ID
357           ,ASSIGNMENT_ID
358           ,WORK_TYPE_ID
359           ,ORG_UTIL_CATEGORY_ID
360           ,RES_UTIL_CATEGORY_ID
361           ,EXPENDITURE_TYPE
362           ,EXPENDITURE_TYPE_CLASS
363           ,PA_PERIOD_NAME
364           ,PA_PERIOD_NUM
365           ,PA_PERIOD_YEAR
366           ,PA_QUARTER_NUMBER
367           ,GL_PERIOD_NAME
368           ,GL_PERIOD_NUM
369           ,GL_PERIOD_YEAR
370           ,GL_QUARTER_NUMBER
371           ,GLOBAL_EXP_PERIOD_END_DATE
372           ,GLOBAL_EXP_YEAR
373           ,GLOBAL_EXP_MONTH_NUMBER
374           ,AMOUNT_TYPE_ID
375           ,PERIOD_BALANCE
376           ,OBJECT_ID
377           ,VERSION_ID
378           ,OBJECT_TYPE_CODE
379           ,BALANCE_TYPE_CODE
380           ,EXPENDITURE_ORG_ID
381           ,PERIOD_TYPE
382           ,PERIOD_SET_NAME
383           ,PERIOD_NAME
384           ,PERIOD_NUM
385           ,PERIOD_YEAR
386           ,QUARTER_OR_MONTH_NUMBER
387           ,UNIT_OF_MEASURE
388           ,SUMM_LEVEL_FLAG
389           ,PROCESS_MODE_FLAG)
390       SELECT 'TMP1',
391              EXPENDITURE_ORGANIZATION_ID,
392              PERSON_ID,
393              ASSIGNMENT_ID,
394              WORK_TYPE_ID,
395              ORG_UTIL_CATEGORY_ID,
396              RES_UTIL_CATEGORY_ID,
397              EXPENDITURE_TYPE,
398              EXPENDITURE_TYPE_CLASS,
399              PA_PERIOD_NAME,
400              PA_PERIOD_NUM,
401              PA_PERIOD_YEAR,
402              PA_QUARTER_NUMBER,
403              GL_PERIOD_NAME,
404              GL_PERIOD_NUM,
405              GL_PERIOD_YEAR,
406              GL_QUARTER_NUMBER,
407              GLOBAL_EXP_PERIOD_END_DATE,
408              GLOBAL_EXP_YEAR,
409              GLOBAL_EXP_MONTH_NUMBER,
410              l_tot_wght_hrs_people_id,
411              TOTAL_WGHTED_HOURS_PEOPLE,
412              NULL,
413              NULL,
414              NULL,
415              NULL,
416              NULL,
417              NULL,
418              NULL,
419              NULL,
420              NULL,
421              NULL,
422              NULL,
423              NULL,
424              'N',
425              'NN'
426        FROM  pa_rep_util_summ0_tmp
427        WHERE DELETE_FLAG     = 'N'
428        AND   TOTAL_WGHTED_HOURS_PEOPLE <> 0;
429 
430       /*
431        * Populate pa_rep_util_summ_tmp for Total Weighted Hours -Organization.
432        */
433       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
434       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Weighted Hours-Org';
435       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
436       END IF;
437 
438       INSERT INTO pa_rep_util_summ_tmp
439        (  RECORD_TYPE
440           ,EXPENDITURE_ORGANIZATION_ID
441           ,PERSON_ID
442           ,ASSIGNMENT_ID
443           ,WORK_TYPE_ID
444           ,ORG_UTIL_CATEGORY_ID
445           ,RES_UTIL_CATEGORY_ID
446           ,EXPENDITURE_TYPE
447           ,EXPENDITURE_TYPE_CLASS
448           ,PA_PERIOD_NAME
449           ,PA_PERIOD_NUM
450           ,PA_PERIOD_YEAR
451           ,PA_QUARTER_NUMBER
452           ,GL_PERIOD_NAME
453           ,GL_PERIOD_NUM
454           ,GL_PERIOD_YEAR
455           ,GL_QUARTER_NUMBER
456           ,GLOBAL_EXP_PERIOD_END_DATE
457           ,GLOBAL_EXP_YEAR
458           ,GLOBAL_EXP_MONTH_NUMBER
459           ,AMOUNT_TYPE_ID
460           ,PERIOD_BALANCE
461           ,OBJECT_ID
462           ,VERSION_ID
463           ,OBJECT_TYPE_CODE
464           ,BALANCE_TYPE_CODE
465           ,EXPENDITURE_ORG_ID
466           ,PERIOD_TYPE
467           ,PERIOD_SET_NAME
468           ,PERIOD_NAME
469           ,PERIOD_NUM
470           ,PERIOD_YEAR
471           ,QUARTER_OR_MONTH_NUMBER
472           ,UNIT_OF_MEASURE
473           ,SUMM_LEVEL_FLAG
474           ,PROCESS_MODE_FLAG)
475       SELECT 'TMP1',
476              EXPENDITURE_ORGANIZATION_ID,
477              PERSON_ID,
478              ASSIGNMENT_ID,
479              WORK_TYPE_ID,
480              ORG_UTIL_CATEGORY_ID,
481              RES_UTIL_CATEGORY_ID,
482              EXPENDITURE_TYPE,
483              EXPENDITURE_TYPE_CLASS,
484              PA_PERIOD_NAME,
485              PA_PERIOD_NUM,
486              PA_PERIOD_YEAR,
487              PA_QUARTER_NUMBER,
488              GL_PERIOD_NAME,
489              GL_PERIOD_NUM,
490              GL_PERIOD_YEAR,
491              GL_QUARTER_NUMBER,
492              GLOBAL_EXP_PERIOD_END_DATE,
493              GLOBAL_EXP_YEAR,
494              GLOBAL_EXP_MONTH_NUMBER,
495              l_tot_wght_hrs_org_id,
496              TOTAL_WGHTED_HOURS_ORG,
497              NULL,
498              NULL,
499              NULL,
500              NULL,
501              NULL,
502              NULL,
503              NULL,
504              NULL,
505              NULL,
506              NULL,
507              NULL,
508              NULL,
509              'N',
510              'NN'
511        FROM  pa_rep_util_summ0_tmp
512        WHERE DELETE_FLAG     = 'N'
513        AND   TOTAL_WGHTED_HOURS_ORG <> 0;
514 
515       IF (l_balance_type_code <> l_actual_c) THEN
516         /*
517          * Populate pa_rep_util_summ_tmp for Prov Weighted Hours -People.
518          */
519         IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
520         PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Prov Weighted Hours-People';
521         PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
522 	END IF;
523 
524         INSERT INTO pa_rep_util_summ_tmp
525          (  RECORD_TYPE
526             ,EXPENDITURE_ORGANIZATION_ID
527             ,PERSON_ID
528             ,ASSIGNMENT_ID
529             ,WORK_TYPE_ID
530             ,ORG_UTIL_CATEGORY_ID
531             ,RES_UTIL_CATEGORY_ID
532             ,EXPENDITURE_TYPE
533             ,EXPENDITURE_TYPE_CLASS
534             ,PA_PERIOD_NAME
535             ,PA_PERIOD_NUM
536             ,PA_PERIOD_YEAR
537             ,PA_QUARTER_NUMBER
538             ,GL_PERIOD_NAME
539             ,GL_PERIOD_NUM
540             ,GL_PERIOD_YEAR
541             ,GL_QUARTER_NUMBER
542             ,GLOBAL_EXP_PERIOD_END_DATE
543             ,GLOBAL_EXP_YEAR
544             ,GLOBAL_EXP_MONTH_NUMBER
545             ,AMOUNT_TYPE_ID
546             ,PERIOD_BALANCE
547             ,OBJECT_ID
548             ,VERSION_ID
549             ,OBJECT_TYPE_CODE
550             ,BALANCE_TYPE_CODE
551             ,EXPENDITURE_ORG_ID
552             ,PERIOD_TYPE
553             ,PERIOD_SET_NAME
554             ,PERIOD_NAME
555             ,PERIOD_NUM
556             ,PERIOD_YEAR
557             ,QUARTER_OR_MONTH_NUMBER
558             ,UNIT_OF_MEASURE
559             ,SUMM_LEVEL_FLAG
560             ,PROCESS_MODE_FLAG)
561         SELECT 'TMP1',
562                EXPENDITURE_ORGANIZATION_ID,
563                PERSON_ID,
564                ASSIGNMENT_ID,
565                WORK_TYPE_ID,
566                ORG_UTIL_CATEGORY_ID,
567                RES_UTIL_CATEGORY_ID,
568                EXPENDITURE_TYPE,
569                EXPENDITURE_TYPE_CLASS,
570                PA_PERIOD_NAME,
571                PA_PERIOD_NUM,
572                PA_PERIOD_YEAR,
573                PA_QUARTER_NUMBER,
574                GL_PERIOD_NAME,
575                GL_PERIOD_NUM,
576                GL_PERIOD_YEAR,
577                GL_QUARTER_NUMBER,
578                GLOBAL_EXP_PERIOD_END_DATE,
579                GLOBAL_EXP_YEAR,
580                GLOBAL_EXP_MONTH_NUMBER,
581                l_prov_wght_hrs_people_id,
582                PROV_WGHTED_HOURS_PEOPLE,
583                NULL,
584                NULL,
585                NULL,
586                NULL,
587                NULL,
588                NULL,
589                NULL,
590                NULL,
591                NULL,
592                NULL,
593                NULL,
594                NULL,
595                'N',
596                'NN'
597          FROM  pa_rep_util_summ0_tmp
598          WHERE DELETE_FLAG     = 'N'
599          AND   PROV_WGHTED_HOURS_PEOPLE <> 0;
600 
601      /*
602       * Populate pa_rep_util_summ_tmp for Prov Weighted Hours -Organization.
603       */
604      IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
605      PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Prov Weighted Hours-Org';
606      PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
607      END IF;
608 
609      INSERT INTO pa_rep_util_summ_tmp
610          (  RECORD_TYPE
611             ,EXPENDITURE_ORGANIZATION_ID
612             ,PERSON_ID
613             ,ASSIGNMENT_ID
614             ,WORK_TYPE_ID
615             ,ORG_UTIL_CATEGORY_ID
616             ,RES_UTIL_CATEGORY_ID
617             ,EXPENDITURE_TYPE
618             ,EXPENDITURE_TYPE_CLASS
619             ,PA_PERIOD_NAME
620             ,PA_PERIOD_NUM
621             ,PA_PERIOD_YEAR
622             ,PA_QUARTER_NUMBER
623             ,GL_PERIOD_NAME
624             ,GL_PERIOD_NUM
625             ,GL_PERIOD_YEAR
626             ,GL_QUARTER_NUMBER
627             ,GLOBAL_EXP_PERIOD_END_DATE
628             ,GLOBAL_EXP_YEAR
629             ,GLOBAL_EXP_MONTH_NUMBER
630             ,AMOUNT_TYPE_ID
631             ,PERIOD_BALANCE
632             ,OBJECT_ID
633             ,VERSION_ID
634             ,OBJECT_TYPE_CODE
635             ,BALANCE_TYPE_CODE
636             ,EXPENDITURE_ORG_ID
637             ,PERIOD_TYPE
638             ,PERIOD_SET_NAME
639             ,PERIOD_NAME
640             ,PERIOD_NUM
641             ,PERIOD_YEAR
642             ,QUARTER_OR_MONTH_NUMBER
643             ,UNIT_OF_MEASURE
644             ,SUMM_LEVEL_FLAG
645             ,PROCESS_MODE_FLAG)
646         SELECT 'TMP1',
647                EXPENDITURE_ORGANIZATION_ID,
648                PERSON_ID,
649                ASSIGNMENT_ID,
650                WORK_TYPE_ID,
651                ORG_UTIL_CATEGORY_ID,
652                RES_UTIL_CATEGORY_ID,
653                EXPENDITURE_TYPE,
654                EXPENDITURE_TYPE_CLASS,
655                PA_PERIOD_NAME,
656                PA_PERIOD_NUM,
657                PA_PERIOD_YEAR,
658                PA_QUARTER_NUMBER,
659                GL_PERIOD_NAME,
660                GL_PERIOD_NUM,
661                GL_PERIOD_YEAR,
662                GL_QUARTER_NUMBER,
663                GLOBAL_EXP_PERIOD_END_DATE,
664                GLOBAL_EXP_YEAR,
665                GLOBAL_EXP_MONTH_NUMBER,
666                l_prov_wght_hrs_org_id,
667                PROV_WGHTED_HOURS_ORG,
668                NULL,
669                NULL,
670                NULL,
671                NULL,
672                NULL,
673                NULL,
674                NULL,
675                NULL,
676                NULL,
677                NULL,
678                NULL,
679                NULL,
680                'N',
681                'NN'
682          FROM  pa_rep_util_summ0_tmp
683          WHERE DELETE_FLAG     = 'N'
684          AND   PROV_WGHTED_HOURS_ORG <> 0;
685 
686       END IF;
687       /*
688        * Populate pa_rep_util_summ_tmp for Reduce Capacity.
689        */
690       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
691       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Reduce Capacity';
692       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
693       END IF;
694 
695       INSERT INTO pa_rep_util_summ_tmp
696        (  RECORD_TYPE
697           ,EXPENDITURE_ORGANIZATION_ID
698           ,PERSON_ID
699           ,ASSIGNMENT_ID
700           ,WORK_TYPE_ID
701           ,ORG_UTIL_CATEGORY_ID
702           ,RES_UTIL_CATEGORY_ID
703           ,EXPENDITURE_TYPE
704           ,EXPENDITURE_TYPE_CLASS
705           ,PA_PERIOD_NAME
706           ,PA_PERIOD_NUM
707           ,PA_PERIOD_YEAR
708           ,PA_QUARTER_NUMBER
709           ,GL_PERIOD_NAME
710           ,GL_PERIOD_NUM
711           ,GL_PERIOD_YEAR
712           ,GL_QUARTER_NUMBER
713           ,GLOBAL_EXP_PERIOD_END_DATE
714           ,GLOBAL_EXP_YEAR
715           ,GLOBAL_EXP_MONTH_NUMBER
716           ,AMOUNT_TYPE_ID
717           ,PERIOD_BALANCE
718           ,OBJECT_ID
719           ,VERSION_ID
720           ,OBJECT_TYPE_CODE
721           ,BALANCE_TYPE_CODE
722           ,EXPENDITURE_ORG_ID
723           ,PERIOD_TYPE
724           ,PERIOD_SET_NAME
725           ,PERIOD_NAME
726           ,PERIOD_NUM
727           ,PERIOD_YEAR
728           ,QUARTER_OR_MONTH_NUMBER
729           ,UNIT_OF_MEASURE
730           ,SUMM_LEVEL_FLAG
731           ,PROCESS_MODE_FLAG)
732       SELECT 'TMP1',
733              EXPENDITURE_ORGANIZATION_ID,
734              PERSON_ID,
735              ASSIGNMENT_ID,
736              WORK_TYPE_ID,
737              ORG_UTIL_CATEGORY_ID,
738              RES_UTIL_CATEGORY_ID,
739              EXPENDITURE_TYPE,
740              EXPENDITURE_TYPE_CLASS,
741              PA_PERIOD_NAME,
742              PA_PERIOD_NUM,
743              PA_PERIOD_YEAR,
744              PA_QUARTER_NUMBER,
745              GL_PERIOD_NAME,
746              GL_PERIOD_NUM,
747              GL_PERIOD_YEAR,
748              GL_QUARTER_NUMBER,
749              GLOBAL_EXP_PERIOD_END_DATE,
750              GLOBAL_EXP_YEAR,
751              GLOBAL_EXP_MONTH_NUMBER,
752              l_red_cap_id,
753              REDUCE_CAPACITY,
754              NULL,
755              NULL,
756              NULL,
757              NULL,
758              NULL,
759              NULL,
760              NULL,
761              NULL,
762              NULL,
763              NULL,
764              NULL,
765              NULL,
766              'N',
767              'NN'
768        FROM  pa_rep_util_summ0_tmp
769        WHERE DELETE_FLAG     = 'N'
770        AND   REDUCE_CAPACITY <> 0;
771 
772     ELSE
773       /*
774        * Here we process all records of pa_rep_util_summ0_tmp.
775        */
776 
777       /*
778        * Populate pa_rep_util_summ_tmp for Total Hours.
779        */
780 
781       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
782       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Hours ';
783       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
784       END IF;
785 
786       INSERT INTO pa_rep_util_summ_tmp
787        (  RECORD_TYPE
788           ,EXPENDITURE_ORGANIZATION_ID
789           ,PERSON_ID
790           ,ASSIGNMENT_ID
791           ,WORK_TYPE_ID
792           ,ORG_UTIL_CATEGORY_ID
793           ,RES_UTIL_CATEGORY_ID
794           ,EXPENDITURE_TYPE
795           ,EXPENDITURE_TYPE_CLASS
796           ,PA_PERIOD_NAME
797           ,PA_PERIOD_NUM
798           ,PA_PERIOD_YEAR
799           ,PA_QUARTER_NUMBER
800           ,GL_PERIOD_NAME
801           ,GL_PERIOD_NUM
802           ,GL_PERIOD_YEAR
803           ,GL_QUARTER_NUMBER
804           ,GLOBAL_EXP_PERIOD_END_DATE
805           ,GLOBAL_EXP_YEAR
806           ,GLOBAL_EXP_MONTH_NUMBER
807           ,AMOUNT_TYPE_ID
808           ,PERIOD_BALANCE
809           ,OBJECT_ID
810           ,VERSION_ID
811           ,OBJECT_TYPE_CODE
812           ,BALANCE_TYPE_CODE
813           ,EXPENDITURE_ORG_ID
814           ,PERIOD_TYPE
815           ,PERIOD_SET_NAME
816           ,PERIOD_NAME
817           ,PERIOD_NUM
818           ,PERIOD_YEAR
819           ,QUARTER_OR_MONTH_NUMBER
820           ,UNIT_OF_MEASURE
821           ,SUMM_LEVEL_FLAG
822           ,PROCESS_MODE_FLAG
823           )
824       SELECT 'TMP1',
825              EXPENDITURE_ORGANIZATION_ID,
826              PERSON_ID,
827              ASSIGNMENT_ID,
828              WORK_TYPE_ID,
829              ORG_UTIL_CATEGORY_ID,
830              RES_UTIL_CATEGORY_ID,
831              EXPENDITURE_TYPE,
832              EXPENDITURE_TYPE_CLASS,
833              PA_PERIOD_NAME,
834              PA_PERIOD_NUM,
835              PA_PERIOD_YEAR,
836              PA_QUARTER_NUMBER,
837              GL_PERIOD_NAME,
838              GL_PERIOD_NUM,
839              GL_PERIOD_YEAR,
840              GL_QUARTER_NUMBER,
841              GLOBAL_EXP_PERIOD_END_DATE,
842              GLOBAL_EXP_YEAR,
843              GLOBAL_EXP_MONTH_NUMBER,
844              l_tot_hrs_id,
845              TOTAL_HOURS,
846              NULL,
847              NULL,
848              NULL,
849              NULL,
850              NULL,
851              NULL,
852              NULL,
853              NULL,
854              NULL,
855              NULL,
856              NULL,
857              NULL,
858              'N',
859              'NN'
860        FROM  pa_rep_util_summ0_tmp
861        WHERE TOTAL_HOURS     <> 0;
862 
863 
864       IF (l_balance_type_code <> l_actual_c) THEN
865         /*
866          * Populate pa_rep_util_summ_tmp for Total Provisional Hours.
867          */
868          IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
869          PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Prov Hours';
870          PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
871 	 END IF;
872 
873          INSERT INTO pa_rep_util_summ_tmp
874           (  RECORD_TYPE
875              ,EXPENDITURE_ORGANIZATION_ID
876              ,PERSON_ID
877              ,ASSIGNMENT_ID
878              ,WORK_TYPE_ID
879              ,ORG_UTIL_CATEGORY_ID
880              ,RES_UTIL_CATEGORY_ID
881              ,EXPENDITURE_TYPE
882              ,EXPENDITURE_TYPE_CLASS
883              ,PA_PERIOD_NAME
884              ,PA_PERIOD_NUM
885              ,PA_PERIOD_YEAR
886              ,PA_QUARTER_NUMBER
887              ,GL_PERIOD_NAME
888              ,GL_PERIOD_NUM
889              ,GL_PERIOD_YEAR
890              ,GL_QUARTER_NUMBER
891              ,GLOBAL_EXP_PERIOD_END_DATE
892              ,GLOBAL_EXP_YEAR
893              ,GLOBAL_EXP_MONTH_NUMBER
894              ,AMOUNT_TYPE_ID
895              ,PERIOD_BALANCE
896              ,OBJECT_ID
897              ,VERSION_ID
898              ,OBJECT_TYPE_CODE
899              ,BALANCE_TYPE_CODE
900              ,EXPENDITURE_ORG_ID
901              ,PERIOD_TYPE
902              ,PERIOD_SET_NAME
903              ,PERIOD_NAME
904              ,PERIOD_NUM
905              ,PERIOD_YEAR
906              ,QUARTER_OR_MONTH_NUMBER
907              ,UNIT_OF_MEASURE
908              ,SUMM_LEVEL_FLAG
909              ,PROCESS_MODE_FLAG)
910          SELECT 'TMP1',
911                 EXPENDITURE_ORGANIZATION_ID,
912                 PERSON_ID,
913                 ASSIGNMENT_ID,
914                 WORK_TYPE_ID,
915                 ORG_UTIL_CATEGORY_ID,
916                 RES_UTIL_CATEGORY_ID,
917                 EXPENDITURE_TYPE,
918                 EXPENDITURE_TYPE_CLASS,
919                 PA_PERIOD_NAME,
920                 PA_PERIOD_NUM,
921                 PA_PERIOD_YEAR,
922                 PA_QUARTER_NUMBER,
923                 GL_PERIOD_NAME,
924                 GL_PERIOD_NUM,
925                 GL_PERIOD_YEAR,
926                 GL_QUARTER_NUMBER,
927                 GLOBAL_EXP_PERIOD_END_DATE,
928                 GLOBAL_EXP_YEAR,
929                 GLOBAL_EXP_MONTH_NUMBER,
930                 l_tot_prov_hrs_id,
931                 TOTAL_PROV_HOURS,
932                 NULL,
933                 NULL,
934                 NULL,
935                 NULL,
936                 NULL,
937                 NULL,
938                 NULL,
939                 NULL,
940                 NULL,
941                 NULL,
942                 NULL,
943                 NULL,
944                 'N',
945                 'NN'
946           FROM  pa_rep_util_summ0_tmp
947           WHERE TOTAL_PROV_HOURS <> 0;
948 
949       END IF;
950 
951       /*
952        * Populate pa_rep_util_summ_tmp for Total Weighted Hours - People.
953        */
954       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
955       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Weighted Hours-People';
956       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
957       END IF;
958 
959       INSERT INTO pa_rep_util_summ_tmp
960        (  RECORD_TYPE
961           ,EXPENDITURE_ORGANIZATION_ID
962           ,PERSON_ID
963           ,ASSIGNMENT_ID
964           ,WORK_TYPE_ID
965           ,ORG_UTIL_CATEGORY_ID
966           ,RES_UTIL_CATEGORY_ID
967           ,EXPENDITURE_TYPE
968           ,EXPENDITURE_TYPE_CLASS
969           ,PA_PERIOD_NAME
970           ,PA_PERIOD_NUM
971           ,PA_PERIOD_YEAR
972           ,PA_QUARTER_NUMBER
973           ,GL_PERIOD_NAME
974           ,GL_PERIOD_NUM
975           ,GL_PERIOD_YEAR
976           ,GL_QUARTER_NUMBER
977           ,GLOBAL_EXP_PERIOD_END_DATE
978           ,GLOBAL_EXP_YEAR
979           ,GLOBAL_EXP_MONTH_NUMBER
980           ,AMOUNT_TYPE_ID
981           ,PERIOD_BALANCE
982           ,OBJECT_ID
983           ,VERSION_ID
984           ,OBJECT_TYPE_CODE
985           ,BALANCE_TYPE_CODE
986           ,EXPENDITURE_ORG_ID
987           ,PERIOD_TYPE
988           ,PERIOD_SET_NAME
989           ,PERIOD_NAME
990           ,PERIOD_NUM
991           ,PERIOD_YEAR
992           ,QUARTER_OR_MONTH_NUMBER
993           ,UNIT_OF_MEASURE
994           ,SUMM_LEVEL_FLAG
995           ,PROCESS_MODE_FLAG)
996       SELECT 'TMP1',
997              EXPENDITURE_ORGANIZATION_ID,
998              PERSON_ID,
999              ASSIGNMENT_ID,
1000              WORK_TYPE_ID,
1001              ORG_UTIL_CATEGORY_ID,
1002              RES_UTIL_CATEGORY_ID,
1003              EXPENDITURE_TYPE,
1004              EXPENDITURE_TYPE_CLASS,
1005              PA_PERIOD_NAME,
1006              PA_PERIOD_NUM,
1007              PA_PERIOD_YEAR,
1008              PA_QUARTER_NUMBER,
1009              GL_PERIOD_NAME,
1010              GL_PERIOD_NUM,
1011              GL_PERIOD_YEAR,
1012              GL_QUARTER_NUMBER,
1013              GLOBAL_EXP_PERIOD_END_DATE,
1014              GLOBAL_EXP_YEAR,
1015              GLOBAL_EXP_MONTH_NUMBER,
1016              l_tot_wght_hrs_people_id,
1017              TOTAL_WGHTED_HOURS_PEOPLE,
1018              NULL,
1019              NULL,
1020              NULL,
1021              NULL,
1022              NULL,
1023              NULL,
1024              NULL,
1025              NULL,
1026              NULL,
1027              NULL,
1028              NULL,
1029              NULL,
1030              'N',
1031              'NN'
1032        FROM  pa_rep_util_summ0_tmp
1033        WHERE TOTAL_WGHTED_HOURS_PEOPLE <> 0;
1034 
1035       /*
1036        * Populate pa_rep_util_summ_tmp for Total Weighted Hours -Organization.
1037        */
1038       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1039       PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Weighted Hours-Org';
1040       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1041       END IF;
1042       INSERT INTO pa_rep_util_summ_tmp
1043        (  RECORD_TYPE
1044           ,EXPENDITURE_ORGANIZATION_ID
1045           ,PERSON_ID
1046           ,ASSIGNMENT_ID
1047           ,WORK_TYPE_ID
1048           ,ORG_UTIL_CATEGORY_ID
1049           ,RES_UTIL_CATEGORY_ID
1050           ,EXPENDITURE_TYPE
1051           ,EXPENDITURE_TYPE_CLASS
1052           ,PA_PERIOD_NAME
1053           ,PA_PERIOD_NUM
1054           ,PA_PERIOD_YEAR
1055           ,PA_QUARTER_NUMBER
1056           ,GL_PERIOD_NAME
1057           ,GL_PERIOD_NUM
1058           ,GL_PERIOD_YEAR
1059           ,GL_QUARTER_NUMBER
1060           ,GLOBAL_EXP_PERIOD_END_DATE
1061           ,GLOBAL_EXP_YEAR
1062           ,GLOBAL_EXP_MONTH_NUMBER
1063           ,AMOUNT_TYPE_ID
1064           ,PERIOD_BALANCE
1065           ,OBJECT_ID
1066           ,VERSION_ID
1067           ,OBJECT_TYPE_CODE
1068           ,BALANCE_TYPE_CODE
1069           ,EXPENDITURE_ORG_ID
1070           ,PERIOD_TYPE
1071           ,PERIOD_SET_NAME
1072           ,PERIOD_NAME
1073           ,PERIOD_NUM
1074           ,PERIOD_YEAR
1075           ,QUARTER_OR_MONTH_NUMBER
1076           ,UNIT_OF_MEASURE
1077           ,SUMM_LEVEL_FLAG
1078           ,PROCESS_MODE_FLAG)
1079       SELECT 'TMP1',
1080              EXPENDITURE_ORGANIZATION_ID,
1081              PERSON_ID,
1082              ASSIGNMENT_ID,
1083              WORK_TYPE_ID,
1084              ORG_UTIL_CATEGORY_ID,
1085              RES_UTIL_CATEGORY_ID,
1086              EXPENDITURE_TYPE,
1087              EXPENDITURE_TYPE_CLASS,
1088              PA_PERIOD_NAME,
1089              PA_PERIOD_NUM,
1090              PA_PERIOD_YEAR,
1091              PA_QUARTER_NUMBER,
1092              GL_PERIOD_NAME,
1093              GL_PERIOD_NUM,
1094              GL_PERIOD_YEAR,
1095              GL_QUARTER_NUMBER,
1096              GLOBAL_EXP_PERIOD_END_DATE,
1097              GLOBAL_EXP_YEAR,
1098              GLOBAL_EXP_MONTH_NUMBER,
1099              l_tot_wght_hrs_org_id,
1100              TOTAL_WGHTED_HOURS_ORG,
1101              NULL,
1102              NULL,
1103              NULL,
1104              NULL,
1105              NULL,
1106              NULL,
1107              NULL,
1108              NULL,
1109              NULL,
1110              NULL,
1111              NULL,
1112              NULL,
1113              'N',
1114              'NN'
1115        FROM  pa_rep_util_summ0_tmp
1116        WHERE TOTAL_WGHTED_HOURS_ORG <> 0;
1117 
1118       IF (l_balance_type_code <> l_actual_c) THEN
1119 
1120         /*
1121          * Populate pa_rep_util_summ_tmp for Prov Weighted Hours -People.
1122          */
1123         IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1124         PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Prov Weighted Hours-People';
1125         PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1126 	END IF;
1127 
1128         INSERT INTO pa_rep_util_summ_tmp
1129          (  RECORD_TYPE
1130             ,EXPENDITURE_ORGANIZATION_ID
1131             ,PERSON_ID
1132             ,ASSIGNMENT_ID
1133             ,WORK_TYPE_ID
1134             ,ORG_UTIL_CATEGORY_ID
1135             ,RES_UTIL_CATEGORY_ID
1136             ,EXPENDITURE_TYPE
1137             ,EXPENDITURE_TYPE_CLASS
1138             ,PA_PERIOD_NAME
1139             ,PA_PERIOD_NUM
1140             ,PA_PERIOD_YEAR
1141             ,PA_QUARTER_NUMBER
1142             ,GL_PERIOD_NAME
1143             ,GL_PERIOD_NUM
1144             ,GL_PERIOD_YEAR
1145             ,GL_QUARTER_NUMBER
1146             ,GLOBAL_EXP_PERIOD_END_DATE
1147             ,GLOBAL_EXP_YEAR
1148             ,GLOBAL_EXP_MONTH_NUMBER
1149             ,AMOUNT_TYPE_ID
1150             ,PERIOD_BALANCE
1151             ,OBJECT_ID
1152             ,VERSION_ID
1153             ,OBJECT_TYPE_CODE
1154             ,BALANCE_TYPE_CODE
1155             ,EXPENDITURE_ORG_ID
1156             ,PERIOD_TYPE
1157             ,PERIOD_SET_NAME
1158             ,PERIOD_NAME
1159             ,PERIOD_NUM
1160             ,PERIOD_YEAR
1161             ,QUARTER_OR_MONTH_NUMBER
1162             ,UNIT_OF_MEASURE
1163             ,SUMM_LEVEL_FLAG
1164             ,PROCESS_MODE_FLAG)
1165         SELECT 'TMP1',
1166                EXPENDITURE_ORGANIZATION_ID,
1167                PERSON_ID,
1168                ASSIGNMENT_ID,
1169                WORK_TYPE_ID,
1170                ORG_UTIL_CATEGORY_ID,
1171                RES_UTIL_CATEGORY_ID,
1172                EXPENDITURE_TYPE,
1173                EXPENDITURE_TYPE_CLASS,
1174                PA_PERIOD_NAME,
1175                PA_PERIOD_NUM,
1176                PA_PERIOD_YEAR,
1177                PA_QUARTER_NUMBER,
1178                GL_PERIOD_NAME,
1179                GL_PERIOD_NUM,
1180                GL_PERIOD_YEAR,
1181                GL_QUARTER_NUMBER,
1182                GLOBAL_EXP_PERIOD_END_DATE,
1183                GLOBAL_EXP_YEAR,
1184                GLOBAL_EXP_MONTH_NUMBER,
1185                l_prov_wght_hrs_people_id,
1186                PROV_WGHTED_HOURS_PEOPLE,
1187                NULL,
1188                NULL,
1189                NULL,
1190                NULL,
1191                NULL,
1192                NULL,
1193                NULL,
1194                NULL,
1195                NULL,
1196                NULL,
1197                NULL,
1198                NULL,
1199                'N',
1200                'NN'
1201          FROM  pa_rep_util_summ0_tmp
1202          WHERE PROV_WGHTED_HOURS_PEOPLE <> 0;
1203 
1204      /*
1205       * Populate pa_rep_util_summ_tmp for Prov Weighted Hours -Organization.
1206       */
1207      IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1208      PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp for Prov Weighted Hours-Org';
1209      PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1210      END IF;
1211 
1212      INSERT INTO pa_rep_util_summ_tmp
1213          (  RECORD_TYPE
1214             ,EXPENDITURE_ORGANIZATION_ID
1215             ,PERSON_ID
1216             ,ASSIGNMENT_ID
1217             ,WORK_TYPE_ID
1218             ,ORG_UTIL_CATEGORY_ID
1219             ,RES_UTIL_CATEGORY_ID
1220             ,EXPENDITURE_TYPE
1221             ,EXPENDITURE_TYPE_CLASS
1222             ,PA_PERIOD_NAME
1223             ,PA_PERIOD_NUM
1224             ,PA_PERIOD_YEAR
1225             ,PA_QUARTER_NUMBER
1226             ,GL_PERIOD_NAME
1227             ,GL_PERIOD_NUM
1228             ,GL_PERIOD_YEAR
1229             ,GL_QUARTER_NUMBER
1230             ,GLOBAL_EXP_PERIOD_END_DATE
1231             ,GLOBAL_EXP_YEAR
1232             ,GLOBAL_EXP_MONTH_NUMBER
1233             ,AMOUNT_TYPE_ID
1234             ,PERIOD_BALANCE
1235             ,OBJECT_ID
1236             ,VERSION_ID
1237             ,OBJECT_TYPE_CODE
1238             ,BALANCE_TYPE_CODE
1239             ,EXPENDITURE_ORG_ID
1240             ,PERIOD_TYPE
1241             ,PERIOD_SET_NAME
1242             ,PERIOD_NAME
1243             ,PERIOD_NUM
1244             ,PERIOD_YEAR
1245             ,QUARTER_OR_MONTH_NUMBER
1246             ,UNIT_OF_MEASURE
1247             ,SUMM_LEVEL_FLAG
1248             ,PROCESS_MODE_FLAG)
1249         SELECT 'TMP1',
1250                EXPENDITURE_ORGANIZATION_ID,
1251                PERSON_ID,
1252                ASSIGNMENT_ID,
1253                WORK_TYPE_ID,
1254                ORG_UTIL_CATEGORY_ID,
1255                RES_UTIL_CATEGORY_ID,
1256                EXPENDITURE_TYPE,
1257                EXPENDITURE_TYPE_CLASS,
1258                PA_PERIOD_NAME,
1259                PA_PERIOD_NUM,
1260                PA_PERIOD_YEAR,
1261                PA_QUARTER_NUMBER,
1262                GL_PERIOD_NAME,
1263                GL_PERIOD_NUM,
1264                GL_PERIOD_YEAR,
1265                GL_QUARTER_NUMBER,
1266                GLOBAL_EXP_PERIOD_END_DATE,
1267                GLOBAL_EXP_YEAR,
1268                GLOBAL_EXP_MONTH_NUMBER,
1269                l_prov_wght_hrs_org_id,
1270                PROV_WGHTED_HOURS_ORG,
1271                NULL,
1272                NULL,
1273                NULL,
1274                NULL,
1275                NULL,
1276                NULL,
1277                NULL,
1278                NULL,
1279                NULL,
1280                NULL,
1281                NULL,
1282                NULL,
1283                'N',
1284                'NN'
1285          FROM  pa_rep_util_summ0_tmp
1286          WHERE PROV_WGHTED_HOURS_ORG <> 0;
1287 
1288       END IF;
1289 
1290       /*
1291        * Populate pa_rep_util_summ_tmp for Reduce Capacity.
1292        */
1293       IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1294       PA_DEBUG.g_err_stage:='Populate pa_rep_util_summ_tmp for Reduce Capacity';
1295       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1296       END IF;
1297 
1298       INSERT INTO pa_rep_util_summ_tmp
1299        (  RECORD_TYPE
1300           ,EXPENDITURE_ORGANIZATION_ID
1301           ,PERSON_ID
1302           ,ASSIGNMENT_ID
1303           ,WORK_TYPE_ID
1304           ,ORG_UTIL_CATEGORY_ID
1305           ,RES_UTIL_CATEGORY_ID
1306           ,EXPENDITURE_TYPE
1307           ,EXPENDITURE_TYPE_CLASS
1308           ,PA_PERIOD_NAME
1309           ,PA_PERIOD_NUM
1310           ,PA_PERIOD_YEAR
1311           ,PA_QUARTER_NUMBER
1312           ,GL_PERIOD_NAME
1313           ,GL_PERIOD_NUM
1314           ,GL_PERIOD_YEAR
1315           ,GL_QUARTER_NUMBER
1316           ,GLOBAL_EXP_PERIOD_END_DATE
1317           ,GLOBAL_EXP_YEAR
1318           ,GLOBAL_EXP_MONTH_NUMBER
1319           ,AMOUNT_TYPE_ID
1320           ,PERIOD_BALANCE
1321           ,OBJECT_ID
1322           ,VERSION_ID
1323           ,OBJECT_TYPE_CODE
1324           ,BALANCE_TYPE_CODE
1325           ,EXPENDITURE_ORG_ID
1326           ,PERIOD_TYPE
1327           ,PERIOD_SET_NAME
1328           ,PERIOD_NAME
1329           ,PERIOD_NUM
1330           ,PERIOD_YEAR
1331           ,QUARTER_OR_MONTH_NUMBER
1332           ,UNIT_OF_MEASURE
1333           ,SUMM_LEVEL_FLAG
1334           ,PROCESS_MODE_FLAG)
1335       SELECT 'TMP1',
1336              EXPENDITURE_ORGANIZATION_ID,
1337              PERSON_ID,
1338              ASSIGNMENT_ID,
1339              WORK_TYPE_ID,
1340              ORG_UTIL_CATEGORY_ID,
1341              RES_UTIL_CATEGORY_ID,
1342              EXPENDITURE_TYPE,
1343              EXPENDITURE_TYPE_CLASS,
1344              PA_PERIOD_NAME,
1345              PA_PERIOD_NUM,
1346              PA_PERIOD_YEAR,
1347              PA_QUARTER_NUMBER,
1348              GL_PERIOD_NAME,
1349              GL_PERIOD_NUM,
1350              GL_PERIOD_YEAR,
1351              GL_QUARTER_NUMBER,
1352              GLOBAL_EXP_PERIOD_END_DATE,
1353              GLOBAL_EXP_YEAR,
1354              GLOBAL_EXP_MONTH_NUMBER,
1355              l_red_cap_id,
1356              REDUCE_CAPACITY,
1357              NULL,
1358              NULL,
1359              NULL,
1360              NULL,
1361              NULL,
1362              NULL,
1363              NULL,
1364              NULL,
1365              NULL,
1366              NULL,
1367              NULL,
1368              NULL,
1369              'N',
1370              'NN'
1371        FROM  pa_rep_util_summ0_tmp
1372        WHERE REDUCE_CAPACITY <> 0;
1373 
1374    END IF;
1375 
1376    /*
1377     * Step 2 - Populate PL/SQL Table after summarizing data from global temporary
1378     *          table pa_rep_util_summ_tmp based on setup.
1379     */
1380 
1381    /*
1382     * Summarization by PA period if enabled.
1383     */
1384 
1385    IF ( PA_REP_UTIL_GLOB.G_util_option_details.G_pa_period_flag = 'Y')
1386    THEN
1387        IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1388        PA_DEBUG.g_err_stage:='Summarization By PA Period';
1389        PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1390        END IF;
1391 
1392        INSERT INTO  pa_rep_util_summ_tmp
1393        (   record_type,
1394            object_id,
1395            version_id,
1396            period_type,
1397            period_set_name,
1398            period_name,
1399            global_exp_period_end_date,
1400            amount_type_id,
1401            unit_of_measure,
1402            period_balance,
1403            period_num,
1404            period_year,
1405            quarter_or_month_number,
1406            balance_type_code,
1407            object_type_code,
1408            expenditure_org_id,
1409            expenditure_organization_id,
1410            person_id,
1411            assignment_id,
1412            work_type_id,
1413            org_util_category_id,
1414            res_util_category_id,
1415            expenditure_type,
1416            expenditure_type_class,
1417            summ_level_flag,
1418            process_mode_flag,
1419            pa_period_name,
1420            pa_period_num,
1421            pa_period_year,
1422            pa_quarter_number,
1423            gl_period_name,
1424            gl_period_num,
1425            gl_period_year,
1426            gl_quarter_number,
1427            global_exp_year,
1428            global_exp_month_number)
1429        SELECT 'TMP1A',
1430               NULL,
1431               -1,
1432               l_pa_c,
1433 --            l_period_set_name,
1434               l_pa_period_set_name, -- Bug 3434019
1435               PA_PERIOD_NAME,
1436               l_dummy_ge_date,
1437               AMOUNT_TYPE_ID,
1438               l_unit_of_measure,
1439               sum(PERIOD_BALANCE),
1440               MAX(PA_PERIOD_NUM),
1441               MAX(PA_PERIOD_YEAR),
1442               MAX(PA_QUARTER_NUMBER),
1443               l_balance_type_code,
1444               l_utildet_c,
1445               l_exp_org_id,
1446               EXPENDITURE_ORGANIZATION_ID,
1447               PERSON_ID,
1448               ASSIGNMENT_ID,
1449               WORK_TYPE_ID,
1450               ORG_UTIL_CATEGORY_ID,
1451               RES_UTIL_CATEGORY_ID,
1452               EXPENDITURE_TYPE,
1453               EXPENDITURE_TYPE_CLASS,
1454               'U',
1455               'II',
1456               NULL,
1457               NULL,
1458               NULL,
1459               NULL,
1460               NULL,
1461               NULL,
1462               NULL,
1463               NULL,
1464               NULL,
1465               NULL
1466          FROM  pa_rep_util_summ_tmp
1467 -- mpuvathi: changed line below so that it is an FTS instead of an index scan
1468 --         WHERE record_type = 'TMP1'
1469          WHERE process_mode_flag = 'NN'
1470          GROUP BY AMOUNT_TYPE_ID,PA_PERIOD_NAME,EXPENDITURE_ORGANIZATION_ID,
1471                   PERSON_ID,ASSIGNMENT_ID,WORK_TYPE_ID,ORG_UTIL_CATEGORY_ID,
1472                   RES_UTIL_CATEGORY_ID,EXPENDITURE_TYPE,EXPENDITURE_TYPE_CLASS;
1473 
1474    END IF;
1475 
1476 
1477    /*
1478     * Summarization by GL period if enabled.
1479     */
1480 
1481    IF ( PA_REP_UTIL_GLOB.G_util_option_details.G_gl_period_flag = 'Y')
1482    THEN
1483        IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1484        PA_DEBUG.g_err_stage:='Summarization By GL Period';
1485        PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1486        END IF;
1487 
1488        INSERT INTO  pa_rep_util_summ_tmp
1489        (   record_type,
1490            object_id,
1491            version_id,
1492            period_type,
1493            period_set_name,
1494            period_name,
1495            global_exp_period_end_date,
1496            amount_type_id,
1497            unit_of_measure,
1498            period_balance,
1499            period_num,
1500            period_year,
1501            quarter_or_month_number,
1502            balance_type_code,
1503            object_type_code,
1504            expenditure_org_id,
1505            expenditure_organization_id,
1506            person_id,
1507            assignment_id,
1508            work_type_id,
1509            org_util_category_id,
1510            res_util_category_id,
1511            expenditure_type,
1512            expenditure_type_class,
1513            summ_level_flag,
1514            process_mode_flag,
1515            pa_period_name,
1516            pa_period_num,
1517            pa_period_year,
1518            pa_quarter_number,
1519            gl_period_name,
1520            gl_period_num,
1521            gl_period_year,
1522            gl_quarter_number,
1523            global_exp_year,
1524            global_exp_month_number)
1525        SELECT 'TMP1A',
1526               NULL,
1527               -1,
1528               l_gl_c,
1529 --            l_period_set_name,
1530               l_gl_period_set_name, -- Bug 3434019
1531               GL_PERIOD_NAME,
1532               l_dummy_ge_date,
1533               AMOUNT_TYPE_ID,
1534               l_unit_of_measure,
1535               sum(PERIOD_BALANCE),
1536               MAX(GL_PERIOD_NUM),
1537               MAX(GL_PERIOD_YEAR),
1538               MAX(GL_QUARTER_NUMBER),
1539               l_balance_type_code,
1540               l_utildet_c,
1541               l_exp_org_id,
1542               EXPENDITURE_ORGANIZATION_ID,
1543               PERSON_ID,
1544               ASSIGNMENT_ID,
1545               WORK_TYPE_ID,
1546               ORG_UTIL_CATEGORY_ID,
1547               RES_UTIL_CATEGORY_ID,
1548               EXPENDITURE_TYPE,
1549               EXPENDITURE_TYPE_CLASS,
1550               'U',
1551               'II',
1552               NULL,
1553               NULL,
1554               NULL,
1555               NULL,
1556               NULL,
1557               NULL,
1558               NULL,
1559               NULL,
1560               NULL,
1561               NULL
1562          FROM pa_rep_util_summ_tmp
1563 -- mpuvathi: changed line below so that it is an FTS instead of an index scan
1564 --         WHERE record_type = 'TMP1'
1565          WHERE process_mode_flag = 'NN'
1566          GROUP BY AMOUNT_TYPE_ID,GL_PERIOD_NAME,EXPENDITURE_ORGANIZATION_ID,
1567                   PERSON_ID,ASSIGNMENT_ID,WORK_TYPE_ID,ORG_UTIL_CATEGORY_ID,
1568                   RES_UTIL_CATEGORY_ID,EXPENDITURE_TYPE,EXPENDITURE_TYPE_CLASS;
1569 
1570    END IF;
1571 
1572    /*
1573     * Summarization by Global Expenditure week if enabled.
1574     */
1575 
1576    IF (PA_REP_UTIL_GLOB.G_util_option_details.G_ge_period_flag = 'Y')
1577    THEN
1578        IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1579        PA_DEBUG.g_err_stage:='Summarization By GE Period';
1580        PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1581        END IF;
1582 
1583 
1584        INSERT INTO  pa_rep_util_summ_tmp
1585        (   record_type,
1586            object_id,
1587            version_id,
1588            period_type,
1589            period_set_name,
1590            period_name,
1591            global_exp_period_end_date,
1592            amount_type_id,
1593            unit_of_measure,
1594            period_balance,
1595            period_num,
1596            period_year,
1597            quarter_or_month_number,
1598            balance_type_code,
1599            object_type_code,
1600            expenditure_org_id,
1601            expenditure_organization_id,
1602            person_id,
1603            assignment_id,
1604            work_type_id,
1605            org_util_category_id,
1606            res_util_category_id,
1607            expenditure_type,
1608            expenditure_type_class,
1609            summ_level_flag,
1610            process_mode_flag,
1611            pa_period_name,
1612            pa_period_num,
1613            pa_period_year,
1614            pa_quarter_number,
1615            gl_period_name,
1616            gl_period_num,
1617            gl_period_year,
1618            gl_quarter_number,
1619            global_exp_year,
1620            global_exp_month_number)
1621        SELECT 'TMP1A',
1622               NULL,
1623               -1,
1624               l_ge_c,
1625               l_dummy_period_set_name,
1626               l_dummy_period_name,
1627               GLOBAL_EXP_PERIOD_END_DATE,
1628               AMOUNT_TYPE_ID,
1629               l_unit_of_measure,
1630               sum(PERIOD_BALANCE),
1631               NULL,
1632               max(GLOBAL_EXP_YEAR),
1633               max(GLOBAL_EXP_MONTH_NUMBER),
1634               l_balance_type_code,
1635               l_utildet_c,
1636               l_exp_org_id,
1637               EXPENDITURE_ORGANIZATION_ID,
1638               PERSON_ID,
1639               ASSIGNMENT_ID,
1640               WORK_TYPE_ID,
1641               ORG_UTIL_CATEGORY_ID,
1642               RES_UTIL_CATEGORY_ID,
1643               EXPENDITURE_TYPE,
1644               EXPENDITURE_TYPE_CLASS,
1645               'U',
1646               'II',
1647               NULL,
1648               NULL,
1649               NULL,
1650               NULL,
1651               NULL,
1652               NULL,
1653               NULL,
1654               NULL,
1655               NULL,
1656               NULL
1657          FROM pa_rep_util_summ_tmp
1658 -- mpuvathi: changed line below so that it is an FTS instead of an index scan
1659 --         WHERE record_type = 'TMP1'
1660          WHERE process_mode_flag = 'NN'
1661          GROUP BY AMOUNT_TYPE_ID,GLOBAL_EXP_PERIOD_END_DATE,
1662                   EXPENDITURE_ORGANIZATION_ID,
1663                   PERSON_ID,ASSIGNMENT_ID,WORK_TYPE_ID,ORG_UTIL_CATEGORY_ID,
1664                   RES_UTIL_CATEGORY_ID,EXPENDITURE_TYPE,EXPENDITURE_TYPE_CLASS;
1665    END IF;
1666 
1667    PA_DEBUG.Reset_curr_function;
1668 
1669 EXCEPTION
1670    WHEN OTHERS
1671    THEN
1672    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1673       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1674       PA_DEBUG.log_message(SQLERRM);
1675       END IF;
1676      raise;
1677 
1678 END summarize_by_period;
1679 
1680 PROCEDURE summarize_temp_data_by_res
1681 IS
1682 BEGIN
1683    /*
1684     * Summarize the lowlevel records to Resource level.
1685     */
1686    PA_DEBUG.set_curr_function('summarize_temp_data_by_res');
1687    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1688    PA_DEBUG.g_err_stage := 'Summarization at resource level';
1689    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1690    END IF;
1691 
1692    INSERT INTO pa_rep_util_summ_tmp
1693       (   record_type,
1694           object_id,
1695           version_id,
1696           object_type_code,
1697           balance_type_code,
1698           expenditure_org_id,
1699           expenditure_organization_id,
1700           person_id,
1701           assignment_id,
1702           work_type_id,
1703           org_util_category_id,
1704           res_util_category_id,
1705           period_type,
1706           period_set_name,
1707           period_name,
1708           global_exp_period_end_date,
1709           period_year,
1710           quarter_or_month_number,
1711           unit_of_measure,
1712           amount_type_id,
1713           period_balance,
1714           period_num,
1715           expenditure_type,
1716           expenditure_type_class,
1717           summ_level_flag,
1718           process_mode_flag)
1719     SELECT 'TMP2',
1720            NULL,
1721            -1,
1722            decode(to_char(grouping(org_util_category_id))||
1723                   to_char(grouping(res_util_category_id))||
1724                   to_char(grouping(work_type_id)),
1725                   '111',l_res_c,  /* Expenditure Organization level */
1726                   '011',l_resuco_c,  /* Expenditure Organization ,
1727                                         Organization Utilization Level */
1728                   '101',l_resucr_c,  /* Expenditure Organization,
1729                                         Resource Utilization Level */
1730                   '000',l_reswt_c), /*  Expenditure Organization
1731                                         Organization Utilization
1732                                         Resource Utilization Level */
1733            l_balance_type_code,
1734            l_exp_org_id,
1735            expenditure_organization_id,
1736            person_id,
1737            -1,
1738            nvl(work_type_id,-1),
1739            nvl(org_util_category_id,-1),
1740            nvl(res_util_category_id,-1),
1741            period_type,
1742            max(period_set_name),
1743            period_name,
1744            global_exp_period_end_date,
1745            max(period_year),
1746            max(quarter_or_month_number),
1747            l_unit_of_measure,
1748            amount_type_id,
1749            sum(period_balance),
1750            max(period_num),
1751            NULL,
1752            NULL,
1753            decode(to_char(grouping(org_util_category_id))||
1754                   to_char(grouping(res_util_category_id))||
1755                   to_char(grouping(work_type_id)),
1756                   '111','Q',  /* Expenditure Organization level */
1757                   '011','P',  /* Expenditure Organization ,
1758                                  Organization Utilization Level */
1759                   '101','P',  /* Expenditure Organization,
1760                                  Resource Utilization Level */
1761                   '000','R'), /*  Expenditure Organization
1762                                  Organization Utilization
1763                                  Resource Utilization Level */
1764             'II'
1765        from  pa_rep_util_summ_tmp
1766        WHERE record_type = 'TMP1A'
1767        AND   process_mode_flag = 'II'
1768        AND   summ_level_flag = 'U'
1769 AND ASSIGNMENT_ID IS NOT NULL     /*bug#8344802*/
1770        group by period_type,
1771                 period_name,
1772                 global_exp_period_end_date,
1773                 amount_type_id,
1774                 expenditure_organization_id,
1775                 person_id,
1776                 cube(org_util_category_id,
1777                      res_util_category_id,
1778                      work_type_id)
1779         having (to_char(grouping(org_util_category_id))||
1780                 to_char(grouping(res_util_category_id))||
1781                 to_char(grouping(work_type_id)))
1782         in ( '111','011', '101', '000');
1783 
1784    PA_DEBUG.Reset_curr_function;
1785 
1786 EXCEPTION
1787    WHEN OTHERS
1788    THEN
1789    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1790         PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1791         PA_DEBUG.log_message(SQLERRM);
1792 	END IF;
1793         raise;
1794 END summarize_temp_data_by_res;
1795 
1796 /*
1797  * Insert Records for Utilization Capacity.
1798  */
1799 PROCEDURE populate_tmp_for_capacity
1800 IS
1801 
1802    CURSOR capacity_api_input_cur(p_start_date DATE,p_end_date DATE)
1803    IS
1804     SELECT distinct
1805 		   resource_organization_id
1806 		   , person_id
1807 		   , resource_id
1808 		   , resource_effective_start_date
1809 		   , resource_effective_end_date
1810     FROM  pa_resources_denorm
1811     WHERE resource_org_id = PA_REP_UTIL_GLOB.GetOrgId
1812     AND   utilization_flag = 'Y'
1813     AND   (
1814             (RESOURCE_EFFECTIVE_START_DATE BETWEEN p_start_date AND p_end_date)
1815           OR
1816             (RESOURCE_EFFECTIVE_END_DATE   BETWEEN p_start_date AND p_end_date)
1817           OR
1818             (p_start_date BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE)
1819           );
1820 
1821 
1822    /*
1823     * Define PL/SQL Table for input and return values.
1824     */
1825     l_in_exp_orgz_tab       PA_PLSQL_DATATYPES.IdTabTyp;
1826     l_in_person_id_tab      PA_PLSQL_DATATYPES.IdTabTyp;
1827     l_in_resource_id_tab    PA_PLSQL_DATATYPES.IdTabTyp;
1828     l_in_res_eff_s_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1829     l_in_res_eff_e_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1830     l_out_exp_orgz_tab      PA_PLSQL_DATATYPES.IdTabTyp;
1831     l_out_person_id_tab     PA_PLSQL_DATATYPES.IdTabTyp;
1832     l_period_type_tab       PA_PLSQL_DATATYPES.Char30TabTyp;
1833     l_period_name_tab       PA_PLSQL_DATATYPES.Char30TabTyp;
1834     l_global_exp_date_tab   PA_PLSQL_DATATYPES.DateTabTyp;
1835     l_period_year_tab       PA_PLSQL_DATATYPES.NumTabTyp;
1836     l_qm_number_tab         PA_PLSQL_DATATYPES.NumTabTyp;
1837     l_period_num_tab        PA_PLSQL_DATATYPES.NumTabTyp;
1838     l_period_balance_tab    PA_PLSQL_DATATYPES.NumTabTyp;
1839 
1840    /*
1841     * Define other variable to be used in this procedure
1842     */
1843     l_return_status         VARCHAR2(10);
1844     l_msg_count             NUMBER;
1845     l_msg_data              VARCHAR2(240);
1846     I                       PLS_INTEGER;
1847     l_last_fetch            VARCHAR2(1):='N';
1848     l_this_fetch            NUMBER:=0;
1849     l_totally_fetched       NUMBER:=0;
1850     l_run_start_date        DATE;
1851     l_run_end_date          DATE;
1852 --  l_period_set_name       VARCHAR2(15) := PA_REP_UTIL_GLOB.GetPeriodSetName;
1853     l_gl_period_set_name    VARCHAR2(15) := PA_REP_UTIL_GLOB.G_implementation_details.G_gl_period_set_name; -- Bug 3434019
1854     l_pa_period_set_name    VARCHAR2(15) := PA_REP_UTIL_GLOB.G_implementation_details.G_pa_period_set_name; -- Bug 3434019
1855     l_gl_c  VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_GL_C; -- Bug 3434019
1856     l_pa_c  VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_PA_C; -- Bug 3434019
1857 
1858 BEGIN
1859 
1860 
1861    PA_DEBUG.set_curr_function('populate_tmp_for_capacity');
1862    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1863    PA_DEBUG.g_err_stage := 'Setting the start and end dates of the run';
1864    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1865    END IF;
1866     /*
1867      * First figure out which balance_type is the current call for so as
1868      * to set the appropriate start and end dates for the run
1869      */
1870      IF l_balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals  then
1871             l_run_start_date := PA_REP_UTIL_GLOB.G_input_parameters.G_ac_start_date;
1872             l_run_end_date   := PA_REP_UTIL_GLOB.G_input_parameters.G_ac_end_date;
1873      ELSIF l_balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast  then
1874             l_run_start_date := PA_REP_UTIL_GLOB.G_input_parameters.G_fc_start_date;
1875             l_run_end_date   := PA_REP_UTIL_GLOB.G_input_parameters.G_fc_end_date;
1876      END IF;
1877 
1878     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1879    PA_DEBUG.g_err_stage := 'Opening the Cursor';
1880    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1881    END IF;
1882 
1883    OPEN capacity_api_input_cur(l_run_start_date,l_run_end_date);
1884 
1885    LOOP
1886 
1887     /*
1888      * Clear all PL/SQL table.
1889      */
1890      IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1891     PA_DEBUG.g_err_stage := 'Clearing PL/SQL Table';
1892     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1893     END IF;
1894 
1895     l_in_exp_orgz_tab.delete;
1896     l_in_person_id_tab.delete;
1897     l_in_resource_id_tab.delete;
1898     l_in_res_eff_s_date_tab.delete;
1899     l_in_res_eff_e_date_tab.delete;
1900     l_out_exp_orgz_tab.delete;
1901     l_out_person_id_tab.delete;
1902     l_period_type_tab.delete;
1903     l_period_name_tab.delete;
1904     l_global_exp_date_tab.delete;
1905     l_period_year_tab.delete;
1906     l_qm_number_tab.delete;
1907     l_period_num_tab.delete;
1908     l_period_balance_tab.delete;
1909 
1910     /*
1911      * Fetch 100 records at a time.
1912      */
1913      IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1914     PA_DEBUG.g_err_stage := 'Fetching 100 records at a time in PL/SQL Table';
1915     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1916     END IF;
1917 
1918     FETCH capacity_api_input_cur  BULK COLLECT
1919     INTO l_in_exp_orgz_tab
1920          , l_in_person_id_tab
1921          , l_in_resource_id_tab
1922          , l_in_res_eff_s_date_tab
1923          , l_in_res_eff_e_date_tab  LIMIT 100;
1924 
1925 
1926     /*
1927      *  To check the rows fetched in this fetch
1928      */
1929       l_this_fetch := capacity_api_input_cur%ROWCOUNT - l_totally_fetched;
1930       l_totally_fetched := capacity_api_input_cur%ROWCOUNT;
1931 
1932     /*
1933      *  Check if this fetch has 0 rows returned (ie last fetch was even 100)
1934      *  This could happen in 2 cases
1935      *      1) this fetch is the very first fetch with 0 rows returned
1936      *   OR 2) the last fetch returned an even 100 rows
1937      *  If either then EXIT without any processing
1938      */
1939         IF  l_this_fetch = 0 then
1940                 EXIT;
1941         END IF;
1942 
1943     /*
1944      *  Check if this fetch is the last fetch
1945      *  If so then set the flag l_last_fetch so as to exit after processing
1946      */
1947         IF  l_this_fetch < 100  then
1948               l_last_fetch := 'Y';
1949         ELSE
1950               l_last_fetch := 'N';
1951         END IF;
1952 
1953 
1954     /*
1955      * Call CV's API in loop get value.
1956      */
1957 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1958     PA_DEBUG.g_err_stage := 'Calling Capacity API for 100 records at a time';
1959     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1960     END IF;
1961 
1962     PA_FORECAST_GRC_PVT.Get_Capacity_Vector(
1963                         p_OU_id                   => l_exp_org_id
1964                         , p_exp_org_id_tab        => l_in_exp_orgz_tab
1965                         , p_person_id_tab         => l_in_person_id_tab
1966                         , p_resource_id_tab       => l_in_resource_id_tab
1967                         , p_in_res_eff_s_date_tab => l_in_res_eff_s_date_tab
1968                         , p_in_res_eff_e_date_tab => l_in_res_eff_e_date_tab
1969                         , p_balance_type_code     => l_balance_type_code
1970                         , p_run_start_date        => l_run_start_date
1971                         , p_run_end_date          => l_run_end_date
1972                         , x_resource_capacity_tab => l_period_balance_tab
1973                         , x_exp_orgz_id_tab       => l_out_exp_orgz_tab
1974                         , x_person_id_tab         => l_out_person_id_tab
1975                         , x_period_type_tab       => l_period_type_tab
1976                         , x_period_name_tab       => l_period_name_tab
1977                         , x_global_exp_date_tab   => l_global_exp_date_tab
1978                         , x_period_year_tab       => l_period_year_tab
1979                         , x_qm_number_tab         => l_qm_number_tab
1980                         , x_period_num_tab        => l_period_num_tab
1981                         , x_return_status         => l_return_status
1982                         , x_msg_count             => l_msg_count
1983                         , x_msg_data              => l_msg_data);
1984 
1985 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1986     PA_DEBUG.g_err_stage := 'Inserting Records into pa_rep_util_summ_tmp for capacity';
1987     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1988     END IF;
1989 
1990     FORALL I in l_out_person_id_tab.FIRST..l_out_person_id_tab.LAST
1991       INSERT INTO pa_rep_util_summ_tmp
1992         (   record_type,
1993             object_id,
1994             version_id,
1995             object_type_code,
1996             balance_type_code,
1997             expenditure_org_id,
1998             expenditure_organization_id,
1999             person_id,
2000             assignment_id,
2001             work_type_id,
2002             org_util_category_id,
2003             res_util_category_id,
2004             period_type,
2005             period_set_name,
2006             period_name,
2007             global_exp_period_end_date,
2008             period_year,
2009             quarter_or_month_number,
2010             unit_of_measure,
2011             amount_type_id,
2012             period_balance,
2013             period_num,
2014             expenditure_type,
2015             expenditure_type_class,
2016             summ_level_flag,
2017             process_mode_flag)
2018        VALUES (
2019             'TMP2',
2020             NULL,
2021             -1,
2022             l_res_c,
2023             l_balance_type_code,
2024             l_exp_org_id,
2025             l_out_exp_orgz_tab(I),
2026             l_out_person_id_tab(I),
2027             -1,
2028             -1,
2029             -1,
2030             -1,
2031             l_period_type_tab(I),
2032 --          DECODE(l_period_name_tab(I)
2033 --                 , l_dummy_period_name, l_dummy_period_set_name
2034 --                 , l_period_set_name) ,
2035             DECODE(l_period_name_tab(I)
2036                    , l_dummy_period_name, l_dummy_period_set_name, decode(l_period_type_tab(I), l_gl_c
2037                    , l_gl_period_set_name, l_pa_period_set_name) ), -- Bug 3434019
2038             l_period_name_tab(I),
2039             l_global_exp_date_tab(I),
2040             l_period_year_tab(I),
2041             l_qm_number_tab(I),
2042             l_unit_of_measure,
2043             l_tot_cap_id,
2044             l_period_balance_tab(I),
2045             l_period_num_tab(I),
2046             NULL,
2047             NULL,
2048             'S',
2049             'II');
2050 
2051     /*
2052      *  Check if this loop is the last set of 100
2053      *  If so then EXIT;
2054      */
2055         IF l_last_fetch='Y' THEN
2056                EXIT;
2057         END IF;
2058 
2059    END LOOP;
2060 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2061    PA_DEBUG.g_err_stage := 'Closing the cursor';
2062    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2063    END IF;
2064 
2065    CLOSE capacity_api_input_cur;
2066 
2067    PA_DEBUG.Reset_curr_function;
2068 
2069 EXCEPTION
2070    WHEN OTHERS
2071    THEN
2072    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2073       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2074       PA_DEBUG.log_message(SQLERRM);
2075       END IF;
2076       raise;
2077 END populate_tmp_for_capacity;
2078 
2079 
2080 /*
2081  * Summarize the resource level records to Organization level.
2082  */
2083 PROCEDURE summarize_temp_data_by_org
2084 IS
2085 BEGIN
2086    PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.summarize_temp_data_by_org');
2087    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2088    PA_DEBUG.g_err_stage := 'Summarizing at Organization level';
2089    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2090    END IF;
2091 
2092    INSERT INTO pa_rep_util_summ_tmp
2093       (   record_type,
2094           object_id,
2095           version_id,
2096           object_type_code,
2097           balance_type_code,
2098           expenditure_org_id,
2099           expenditure_organization_id,
2100           person_id,
2101           assignment_id,
2102           work_type_id,
2103           org_util_category_id,
2104           res_util_category_id,
2105           period_type,
2106           period_set_name,
2107           period_name,
2108           global_exp_period_end_date,
2109           period_year,
2110           quarter_or_month_number,
2111           unit_of_measure,
2112           amount_type_id,
2113           period_balance,
2114           period_num,
2115           expenditure_type,
2116           expenditure_type_class,
2117           summ_level_flag,
2118           process_mode_flag)
2119     SELECT 'TMP2',
2120            NULL,
2121            -1,
2122            decode(grouping(org_util_category_id)||grouping(work_type_id),
2123                   '11',l_org_c,
2124                   '01',l_orguc_c,
2125                   '00',l_orgwt_c),
2126            l_balance_type_code,
2127            l_exp_org_id,
2128            expenditure_organization_id,
2129            -1,
2130            -1,
2131            nvl(work_type_id,-1),
2132            nvl(org_util_category_id,-1),
2133            -1,
2134            period_type,
2135 --         DECODE(period_type
2136 --                , l_ge_c, l_dummy_period_set_name
2137 --                , l_period_set_name) ,
2138            DECODE(period_type
2139                   , l_ge_c, l_dummy_period_set_name, l_gl_c, l_gl_period_set_name
2140                   , l_pa_period_set_name) , -- Bug 3434019
2141            period_name,
2142            global_exp_period_end_date,
2143            max(period_year),
2144            max(quarter_or_month_number),
2145            l_unit_of_measure,
2146            /*
2147             * Convert the Utilization and resource level amount type
2148             * to Direct organization level amount types.
2149             */
2150            decode(amount_type_id,l_tot_hrs_id,l_dirct_tot_hrs_id,
2151                          l_tot_prov_hrs_id,l_dirct_tot_prov_hrs_id,
2152                          l_tot_wght_hrs_org_id,l_dirct_tot_wght_hrs_org_id,
2153                          l_prov_wght_hrs_org_id,l_dirct_prov_wght_hrs_org_id,
2154                          l_red_cap_id,l_dirct_reduce_cap_id,
2155                          l_tot_cap_id,l_dirct_cap_id),
2156            sum(period_balance),
2157            max(period_num),
2158            NULL,
2159            NULL,
2160            'O',
2161            'II'
2162        from  pa_rep_util_summ_tmp
2163        where summ_level_flag in ( 'R','S')
2164        and   record_type = 'TMP2'
2165        and   process_mode_flag = 'II'
2166        and   amount_type_id not in (l_tot_wght_hrs_people_id,
2167                                     l_prov_wght_hrs_people_id)
2168        group by period_type,
2169                 period_name,
2170                 global_exp_period_end_date,
2171                 amount_type_id,
2172                 expenditure_organization_id,
2173                 summ_level_flag,
2174                 rollup(org_util_category_id,
2175                        work_type_id)
2176        having ((summ_level_flag = 'S'
2177           and grouping(org_util_category_id)||grouping(work_type_id) = '11')
2178           or  ( summ_level_flag = 'R'));
2179 
2180        PA_DEBUG.Reset_curr_function;
2181 
2182 EXCEPTION
2183    WHEN OTHERS
2184    THEN
2185    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2186       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2187       PA_DEBUG.log_message(SQLERRM);
2188       END IF;
2189       raise;
2190 END summarize_temp_data_by_org;
2191 
2192 /** This procedure will  find the object Id for each record of
2193     pa_rep_util_summ_tmp with record type = 'TMP2' and process_mode_flag = 'II'
2194 	and populate it.  If not found, generate an object id **/
2195 
2196 
2197 PROCEDURE populate_object_entity IS
2198 
2199 BEGIN
2200 
2201    PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.summarize_temp_data_by_org');
2202 
2203    /*
2204     * Update the pa_rep_util_summ_tmp with matching object Id.
2205     */
2206     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2207    PA_DEBUG.g_err_stage := 'Update pa_rep_util_summ_tmp with matching object Id';
2208    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2209    END IF;
2210 
2211    UPDATE pa_rep_util_summ_tmp T
2212    SET    (T.object_id,T.process_mode_flag)
2213                      = ( select OB.object_id ,'UI'
2214                          from   pa_objects OB
2215                          where  OB.OBJECT_TYPE_CODE   = T.OBJECT_TYPE_CODE
2216                          and    OB.BALANCE_TYPE_CODE  = T.BALANCE_TYPE_CODE
2217                          and    OB.EXPENDITURE_ORG_ID = T.EXPENDITURE_ORG_ID
2218                          and    OB.EXPENDITURE_ORGANIZATION_ID
2219                                 = T.EXPENDITURE_ORGANIZATION_ID
2220                          and    OB.PERSON_ID          = T.PERSON_ID
2221                          and    OB.ASSIGNMENT_ID      = T.ASSIGNMENT_ID
2222                          and    OB.WORK_TYPE_ID       = T.WORK_TYPE_ID
2223                          and    OB.ORG_UTIL_CATEGORY_ID
2224                                 = T.ORG_UTIL_CATEGORY_ID
2225                          and    OB.RES_UTIL_CATEGORY_ID
2226                                 = T.RES_UTIL_CATEGORY_ID
2227                          and    nvl(OB.EXPENDITURE_TYPE,'-1')
2228                                 = nvl(T.EXPENDITURE_TYPE,'-1')
2229                          and    nvl(OB.EXPENDITURE_TYPE_CLASS,'-1')
2230                                 = nvl(T.EXPENDITURE_TYPE_CLASS,'-1')
2231                          and    OB.PROJECT_ORG_ID          = -1
2232                          and    OB.PROJECT_ORGANIZATION_ID = -1
2233                          and    OB.PROJECT_ID              = -1
2234                          and    OB.TASK_ID                 = -1)
2235    WHERE   T.record_type    = 'TMP2'
2236 -- mpuvathi: since all are 'II' till now
2237    AND     T.process_mode_flag = 'II'
2238    ;
2239 
2240    /*
2241     * Populate pa_rep_util_summ_tmp with unique key for pa_objects.
2242     */
2243 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2244    PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp with unique key for pa_objects';
2245    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2246    END IF;
2247 
2248    INSERT INTO pa_rep_util_summ_tmp
2249       (   record_type,
2250           object_id,
2251           version_id,
2252           object_type_code,
2253           balance_type_code,
2254           expenditure_org_id,
2255           expenditure_organization_id,
2256           person_id,
2257           assignment_id,
2258           work_type_id,
2259           org_util_category_id,
2260           res_util_category_id,
2261           period_type,
2262           period_set_name,
2263           period_name,
2264           global_exp_period_end_date,
2265           period_year,
2266           quarter_or_month_number,
2267           unit_of_measure,
2268           amount_type_id,
2269           period_balance,
2270           period_num,
2271           expenditure_type,
2272           expenditure_type_class,
2273           summ_level_flag,
2274           process_mode_flag)
2275     SELECT 'TMP3',
2276            pa_objects_s.nextval,
2277            NULL,
2278            T1.object_type_code,
2279            T1.balance_type_code,
2280            T1.expenditure_org_id,
2281            T1.expenditure_organization_id,
2282            T1.person_id,
2283            T1.assignment_id,
2284            T1.work_type_id,
2285            T1.org_util_category_id,
2286            T1.res_util_category_id,
2287            NULL,
2288            NULL,
2289            NULL,
2290            NULL,
2291            NULL,
2292            NULL,
2293            NULL,
2294            NULL,
2295            NULL,
2296            NULL,
2297            T1.expenditure_type,
2298            T1.expenditure_type_class,
2299            'H',
2300            'HH'
2301      FROM  pa_rep_util_summ_tmp T1
2302      WHERE T1.record_type = 'TMP2'
2303 -- mpuvathi: since even process_mode_flag would have been updated to NULL
2304      AND   T1.process_mode_flag is NULL
2305      AND   T1.object_id  IS NULL
2306      AND   T1.rowid      in (SELECT max(T2.rowid)
2307                              FROM   pa_rep_util_summ_tmp T2
2308 -- mpuvathi: since even process_mode_flag would have been updated to NULL
2309                              WHERE  T2.process_mode_flag is NULL
2310                              AND    T2.object_id  IS NULL
2311                              AND    T2.record_type = 'TMP2'
2312                              GROUP BY
2313                                      T2.OBJECT_TYPE_CODE
2314                                    , T2.BALANCE_TYPE_CODE
2315                                    , T2.EXPENDITURE_ORGANIZATION_ID
2316                                    , T2.PERSON_ID
2317                                    , T2.ASSIGNMENT_ID
2318                                    , T2.WORK_TYPE_ID
2319                                    , T2.ORG_UTIL_CATEGORY_ID
2320                                    , T2.RES_UTIL_CATEGORY_ID
2321                                )
2322 ;
2323 
2324 
2325    /*
2326     * Populate the other records of pa_rep_util_summ_tmp with
2327     * record type = 'TMP2' and null object Id.
2328     */
2329 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2330    PA_DEBUG.g_err_stage := 'Populate the other records of pa_rep_util_summ_tmp with record type =TMP2 and null object Id';
2331    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2332    END IF;
2333 
2334    UPDATE  pa_rep_util_summ_tmp T1
2335    SET     (T1.object_id ,
2336             T1.process_mode_flag) = ( SELECT T2.object_id,'II'
2337                                       FROM   pa_rep_util_summ_tmp T2
2338                                       WHERE  T1.OBJECT_TYPE_CODE
2339                                         = T2.OBJECT_TYPE_CODE
2340                                       AND    T1.BALANCE_TYPE_CODE
2341                                         = T2.BALANCE_TYPE_CODE
2342                                       AND    T1.EXPENDITURE_ORGANIZATION_ID
2343                                         = T2.EXPENDITURE_ORGANIZATION_ID
2344                                       AND    T1.PERSON_ID      = T2.PERSON_ID
2345 --                                      AND    T1.ASSIGNMENT_ID  = T2.ASSIGNMENT_ID
2346                                       AND    T1.WORK_TYPE_ID   = T2.WORK_TYPE_ID
2347                                       AND    T1.ORG_UTIL_CATEGORY_ID
2348                                         = T2.ORG_UTIL_CATEGORY_ID
2349                                       AND    T1.RES_UTIL_CATEGORY_ID
2350                                         = T2.RES_UTIL_CATEGORY_ID
2351 --                                      AND    nvl(T1.EXPENDITURE_TYPE,'-1') = nvl(T2.EXPENDITURE_TYPE,'-1')
2352 --                                      AND    nvl(T1.EXPENDITURE_TYPE_CLASS,'-1') = nvl(T2.EXPENDITURE_TYPE_CLASS,'-1')
2353                                       AND    T2.record_type = 'TMP3'
2354 									  AND    T2.process_mode_flag = 'HH'
2355 									)
2356    WHERE   T1.record_type       = 'TMP2'
2357 -- mpuvathi: since even process_mode_flag would have been updated to NULL
2358    AND     T1.process_mode_flag is NULL
2359 --   AND     nvl(T1.process_mode_flag,'II') <> 'UI'
2360    AND     T1.object_id         IS NULL
2361    ;
2362 
2363 
2364    /*
2365     * Insert New Objects in PA_OBJECTS.
2366     */
2367 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2368    PA_DEBUG.g_err_stage := 'Insert New Objects in PA_OBJECTS';
2369    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2370    END IF;
2371 
2372    INSERT INTO PA_OBJECTS
2373    (  OBJECT_ID,
2374       OBJECT_TYPE_CODE,
2375       BALANCE_TYPE_CODE,
2376       PROJECT_ORG_ID,
2377       PROJECT_ORGANIZATION_ID,
2378       PROJECT_ID,
2379       TASK_ID,
2380       EXPENDITURE_ORG_ID,
2381       EXPENDITURE_ORGANIZATION_ID,
2382       PERSON_ID,
2383       ASSIGNMENT_ID,
2384       WORK_TYPE_ID,
2385       ORG_UTIL_CATEGORY_ID,
2386       RES_UTIL_CATEGORY_ID,
2387       EXPENDITURE_TYPE,
2388       EXPENDITURE_TYPE_CLASS,
2389       LAST_UPDATE_DATE,
2390       LAST_UPDATED_BY,
2391       CREATION_DATE,
2392       CREATED_BY,
2393       LAST_UPDATE_LOGIN,
2394       REQUEST_ID,
2395       PROGRAM_APPLICATION_ID,
2396       PROGRAM_ID,
2397       PROGRAM_UPDATE_DATE)
2398   SELECT  OBJECT_ID,
2399           OBJECT_TYPE_CODE,
2400           BALANCE_TYPE_CODE,
2401           -1,
2402           -1,
2403           -1,
2404           -1,
2405           l_exp_org_id,
2406           EXPENDITURE_ORGANIZATION_ID,
2407           PERSON_ID,
2408           ASSIGNMENT_ID,
2409           WORK_TYPE_ID,
2410           ORG_UTIL_CATEGORY_ID,
2411           RES_UTIL_CATEGORY_ID,
2412           EXPENDITURE_TYPE,
2413           EXPENDITURE_TYPE_CLASS,
2414           l_last_update_date,
2415           l_last_updated_by,
2416           l_creation_date,
2417           l_created_by,
2418           l_last_update_login,
2419           l_request_id,
2420           l_program_application_id,
2421           l_program_id,
2422           l_creation_date
2423     FROM  pa_rep_util_summ_tmp
2424     WHERE record_type   = 'TMP3'
2425     AND   summ_level_flag = 'H'
2426     AND   process_mode_flag = 'HH'
2427 	AND   object_type_code <> l_utildet_c
2428     ;
2429 
2430     PA_DEBUG.Reset_curr_function;
2431 
2432 EXCEPTION
2433    WHEN OTHERS
2434    THEN
2435    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2436       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2437       PA_DEBUG.log_message(SQLERRM);
2438       END IF;
2439         raise;
2440 END populate_object_entity;
2441 
2442 /*
2443  * This procedure checks whether any matching record exists in
2444  * PA_SUMM_BALANCES, if yes, it will update the record. If no,it
2445  * will insert a new record in PA_SUMM_BALANCES.
2446  */
2447 
2448 PROCEDURE populate_balance_entity IS
2449 
2450    /*
2451     * Define PL/SQL Table for holding the fetched records from the cursor
2452     * before inserting into the global temporary table pa_rep_util_summ0_tmp
2453     */
2454           L_PERIOD_BALANCE_TAB          PA_PLSQL_DATATYPES.NumTabTyp;
2455           L_OBJECT_ID_TAB               PA_PLSQL_DATATYPES.IdTabTyp;
2456           L_VERSION_ID_TAB              PA_PLSQL_DATATYPES.IdTabTyp;
2457           L_OBJECT_TYPE_CODE_TAB        PA_PLSQL_DATATYPES.CHAR15TabTyp;
2458           L_PERIOD_TYPE_TAB             PA_PLSQL_DATATYPES.CHAR15TabTyp;
2459           L_PERIOD_SET_NAME_TAB         PA_PLSQL_DATATYPES.CHAR15TabTyp;
2460           L_PERIOD_NAME_TAB             PA_PLSQL_DATATYPES.CHAR15TabTyp;
2461           L_GLOBAL_EXP_END_DATE_TAB     PA_PLSQL_DATATYPES.DateTabTyp;
2462           L_PERIOD_YEAR_TAB             PA_PLSQL_DATATYPES.NumTabTyp;
2463           L_QUARTER_OR_MONTH_NUMBER_TAB PA_PLSQL_DATATYPES.NumTabTyp;
2464           L_AMOUNT_TYPE_ID_TAB          PA_PLSQL_DATATYPES.IdTabTyp;
2465 
2466 l_tot_cap_id              pa_amount_types_b.amount_type_id%TYPE
2467               := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_cap_id;
2468 l_dirct_cap_id                pa_amount_types_b.amount_type_id%TYPE
2469            := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_cap_id;
2470 l_sub_org_cap_id                  pa_amount_types_b.amount_type_id%TYPE
2471           := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_cap_id;
2472 l_org_tot_cap_id              pa_amount_types_b.amount_type_id%TYPE
2473               := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_cap_id;
2474 
2475 l_totally_fetched NUMBER := 0;
2476 
2477   CURSOR cur_update_bal
2478   IS
2479     SELECT
2480           SUM(T.period_balance)           period_balance
2481           , T.OBJECT_ID                   OBJECT_ID
2482           , T.VERSION_ID                  VERSION_ID
2483           , T.OBJECT_TYPE_CODE            OBJECT_TYPE_CODE
2484           , T.PERIOD_TYPE                 PERIOD_TYPE
2485           , T.PERIOD_SET_NAME             PERIOD_SET_NAME
2486           , T.PERIOD_NAME                 PERIOD_NAME
2487           , T.GLOBAL_EXP_PERIOD_END_DATE  GLOBAL_EXP_PERIOD_END_DATE
2488           , T.PERIOD_YEAR                 PERIOD_YEAR
2489           , T.QUARTER_OR_MONTH_NUMBER     QUARTER_OR_MONTH_NUMBER
2490           , T.AMOUNT_TYPE_ID              AMOUNT_TYPE_ID
2491     FROM  pa_rep_util_summ_tmp T
2492     WHERE
2493           T.RECORD_TYPE = 'TMP4'
2494       AND T.PROCESS_MODE_FLAG = 'U'
2495     GROUP BY
2496           T.OBJECT_ID
2497           , T.VERSION_ID
2498           , T.OBJECT_TYPE_CODE
2499           , T.PERIOD_TYPE
2500           , T.PERIOD_SET_NAME
2501           , T.PERIOD_NAME
2502           , T.GLOBAL_EXP_PERIOD_END_DATE
2503           , T.PERIOD_YEAR
2504           , T.QUARTER_OR_MONTH_NUMBER
2505           , T.AMOUNT_TYPE_ID
2506     ;
2507 
2508     rec_update_bal  cur_update_bal%ROWTYPE;
2509 
2510 BEGIN
2511 
2512    PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.populate_balance_entity');
2513 
2514    /*
2515     * Update the global temporary table for successful update.
2516     */
2517     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2518    PA_DEBUG.g_err_stage := 'Update the global temporary table for successful update1';
2519    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2520    END IF;
2521 
2522    UPDATE pa_rep_util_summ_tmp B
2523    SET    B.process_mode_flag    = 'U'
2524           , B.record_type        = 'TMP4'
2525    WHERE  exists( SELECT T.period_balance
2526                   FROM   pa_summ_balances T
2527                   WHERE  T.OBJECT_ID        = B.OBJECT_ID
2528                   AND    T.VERSION_ID       = B.VERSION_ID
2529                   AND    T.OBJECT_TYPE_CODE = B.OBJECT_TYPE_CODE
2530                   AND    T.PERIOD_TYPE      = B.PERIOD_TYPE
2531                   AND    T.PERIOD_SET_NAME  = B.PERIOD_SET_NAME
2532                   AND    T.PERIOD_NAME      = B.PERIOD_NAME
2533                   AND    T.GLOBAL_EXP_PERIOD_END_DATE
2534                          = B.GLOBAL_EXP_PERIOD_END_DATE
2535                   AND    T.PERIOD_YEAR      = B.PERIOD_YEAR
2536                   AND    T.QUARTER_OR_MONTH_NUMBER
2537                          = B.QUARTER_OR_MONTH_NUMBER
2538                   AND    T.AMOUNT_TYPE_ID   = B.AMOUNT_TYPE_ID)
2539    AND   B.RECORD_TYPE      = 'TMP2'
2540 --   AND   nvl(B.process_mode_flag,'II') <> 'II'
2541    AND   B.process_mode_flag = 'UI'
2542    ;
2543 
2544     /*
2545      * Clear all PL/SQL table.
2546      */
2547      IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2548     PA_DEBUG.g_err_stage := 'Clearing PL/SQL Table';
2549     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2550     END IF;
2551 
2552     L_PERIOD_BALANCE_TAB.delete;
2553     L_OBJECT_ID_TAB.delete;
2554     L_VERSION_ID_TAB.delete;
2555     L_OBJECT_TYPE_CODE_TAB.delete;
2556     L_PERIOD_TYPE_TAB.delete;
2557     L_PERIOD_SET_NAME_TAB.delete;
2558     L_PERIOD_NAME_TAB.delete;
2559     L_GLOBAL_EXP_END_DATE_TAB.delete;
2560     L_PERIOD_YEAR_TAB.delete;
2561     L_QUARTER_OR_MONTH_NUMBER_TAB.delete;
2562     L_AMOUNT_TYPE_ID_TAB.delete;
2563 
2564    /*
2565     * Update the balance entity for existing records of pa_rep_util_summ_tmp
2566     * marked for update.
2567     */
2568 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2569    PA_DEBUG.g_err_stage := 'Update the balance entity for existing records of pa_rep_util_summ_tmp  marked for update';
2570    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2571    END IF;
2572 
2573    IF cur_update_bal%ISOPEN then
2574       CLOSE cur_update_bal;
2575    END IF;
2576 
2577    OPEN cur_update_bal;
2578 
2579    FETCH cur_update_bal BULK COLLECT
2580    INTO
2581        L_PERIOD_BALANCE_TAB
2582        , L_OBJECT_ID_TAB
2583        , L_VERSION_ID_TAB
2584        , L_OBJECT_TYPE_CODE_TAB
2585        , L_PERIOD_TYPE_TAB
2586        , L_PERIOD_SET_NAME_TAB
2587        , L_PERIOD_NAME_TAB
2588        , L_GLOBAL_EXP_END_DATE_TAB
2589        , L_PERIOD_YEAR_TAB
2590        , L_QUARTER_OR_MONTH_NUMBER_TAB
2591        , L_AMOUNT_TYPE_ID_TAB
2592        ;
2593    l_totally_fetched := cur_update_bal%ROWCOUNT;
2594 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2595     PA_DEBUG.g_err_stage := 'Records totally fetched from cur_update_bal'||l_totally_fetched||L_PERIOD_BALANCE_TAB.COUNT;
2596     PA_DEBUG.Log_Message( p_message => PA_DEBUG.g_err_stage);
2597     PA_DEBUG.g_err_stage := 'Before updating PA_SUMM_BALANCES from cur_update_bal';
2598     PA_DEBUG.Log_Message( p_message => PA_DEBUG.g_err_stage);
2599     END IF;
2600 
2601 
2602     IF L_PERIOD_BALANCE_TAB.COUNT > 0 then
2603            FORALL I in L_PERIOD_BALANCE_TAB.FIRST..L_PERIOD_BALANCE_TAB.LAST
2604            UPDATE pa_summ_balances  B
2605                   set B.period_balance = (L_PERIOD_BALANCE_TAB(I)+
2606                                          DECODE(B.amount_type_id
2607                                          , l_tot_cap_id     , 0
2608                                          , l_dirct_cap_id   , 0
2609                                          , l_org_tot_cap_id , 0
2610                                          , l_sub_org_cap_id , 0
2611                                          , B.period_balance)
2612                                          )
2613            WHERE L_OBJECT_ID_TAB(I)             = B.OBJECT_ID
2614            AND L_VERSION_ID_TAB(I)              = B.VERSION_ID
2615            AND L_OBJECT_TYPE_CODE_TAB(I)        = B.OBJECT_TYPE_CODE
2616            AND L_PERIOD_TYPE_TAB(I)             = B.PERIOD_TYPE
2617            AND L_PERIOD_SET_NAME_TAB(I)         = B.PERIOD_SET_NAME
2618            AND L_PERIOD_NAME_TAB(I)             = B.PERIOD_NAME
2619            AND L_GLOBAL_EXP_END_DATE_TAB(I)     = B.GLOBAL_EXP_PERIOD_END_DATE
2620            AND L_PERIOD_YEAR_TAB(I)             = B.PERIOD_YEAR
2621            AND L_QUARTER_OR_MONTH_NUMBER_TAB(I) = B.QUARTER_OR_MONTH_NUMBER
2622            AND L_AMOUNT_TYPE_ID_TAB(I)          = B.AMOUNT_TYPE_ID
2623            ;
2624     END IF;
2625     CLOSE cur_update_bal;
2626 
2627 
2628    /*
2629     * Insert new balance records from pa_rep_util_summ_tmp if needed.
2630     */
2631     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2632    PA_DEBUG.g_err_stage := 'Insert new balance records from pa_rep_util_summ_tmp if needed.';
2633    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2634    END IF;
2635 
2636    INSERT INTO pa_summ_balances
2637    ( OBJECT_ID,
2638      VERSION_ID,
2639      OBJECT_TYPE_CODE,
2640      PERIOD_TYPE,
2641      PERIOD_SET_NAME ,
2642      PERIOD_NAME,
2643      GLOBAL_EXP_PERIOD_END_DATE,
2644      PERIOD_YEAR,
2645      QUARTER_OR_MONTH_NUMBER,
2646      AMOUNT_TYPE_ID,
2647      PERIOD_NUM,
2648      UNIT_OF_MEASURE ,
2649      PERIOD_BALANCE,
2650      PVDR_CURRENCY_CODE,
2651      PVDR_PERIOD_BALANCE)
2652    SELECT OBJECT_ID,
2653        -1,
2654        max(OBJECT_TYPE_CODE),
2655        PERIOD_TYPE,
2656        max(nvl(PERIOD_SET_NAME,l_dummy_period_set_name)),
2657        nvl(PERIOD_NAME,l_dummy_period_name),
2658        nvl(GLOBAL_EXP_PERIOD_END_DATE,l_dummy_ge_date),
2659        max(PERIOD_YEAR),
2660        max(QUARTER_OR_MONTH_NUMBER),
2661        AMOUNT_TYPE_ID,
2662        max(PERIOD_NUM),
2663        max(UNIT_OF_MEASURE),
2664        sum(PERIOD_BALANCE),
2665        NULL,
2666        NULL
2667    FROM   pa_rep_util_summ_tmp
2668    WHERE  RECORD_TYPE       = 'TMP2'
2669 -- mpuvathi: for both UI and II
2670    AND    PROCESS_MODE_FLAG in ('UI' , 'II')
2671    AND    object_type_code <> l_utildet_c
2672    GROUP BY OBJECT_ID, PERIOD_TYPE, PERIOD_NAME,
2673          GLOBAL_EXP_PERIOD_END_DATE, AMOUNT_TYPE_ID;
2674 
2675    PA_DEBUG.Reset_curr_function;
2676 
2677 EXCEPTION
2678     WHEN OTHERS
2679     THEN
2680     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2681       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2682       PA_DEBUG.log_message(SQLERRM);
2683       END IF;
2684         raise;
2685 END populate_balance_entity;
2686 
2687 
2688 /*
2689  * This will populate the PA_REP_UTIL_SUMM_TMP table for incremental rollup.
2690  */
2691 PROCEDURE populate_incremental_rollup
2692 IS
2693 /*
2694  * Cache sub org and total level amount types.
2695  */
2696 l_org_tot_hrs_id     pa_amount_types_b.amount_type_id%TYPE
2697                := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_hrs_id;
2698 l_org_tot_wght_hrs_org_id     pa_amount_types_b.amount_type_id%TYPE
2699                := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_wtdhrs_org_id;
2700 l_org_tot_prov_hrs_id         pa_amount_types_b.amount_type_id%TYPE
2701                := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_prvhrs_id;
2702 l_org_prov_wght_hrs_org_id    pa_amount_types_b.amount_type_id%TYPE
2703               := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_prvwtdhrs_org_id;
2704 l_org_tot_cap_id              pa_amount_types_b.amount_type_id%TYPE
2705               := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_cap_id;
2706 l_org_tot_reducedcap_id       pa_amount_types_b.amount_type_id%TYPE
2707               := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_reducedcap_id;
2708 l_sub_org_tot_hrs_id         pa_amount_types_b.amount_type_id%TYPE
2709               := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_hrs_id;
2710 l_sub_org_tot_prov_hrs_id    pa_amount_types_b.amount_type_id%TYPE
2711               := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_prvhrs_id;
2712 l_sub_org_tot_wght_hrs_org_id    pa_amount_types_b.amount_type_id%TYPE
2713           := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_wtdhrs_org_id;
2714 l_sub_org_prov_wght_hrs_org_id    pa_amount_types_b.amount_type_id%TYPE
2715           := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_prvwtdhrs_org_id;
2716 l_sub_org_cap_id                  pa_amount_types_b.amount_type_id%TYPE
2717           := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_cap_id;
2718 l_sub_org_reducedcap_id           pa_amount_types_b.amount_type_id%TYPE
2719           := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_reducedcap_id;
2720 /** End Cache sub org and total level amount types **/
2721 
2722 
2723 BEGIN
2724 
2725    PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.populate_incremental_rollup');
2726 
2727    /*
2728     * populate PA_REP_UTIL_SUMM_TMP for total hours.
2729     */
2730     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2731    PA_DEBUG.g_err_stage := 'populate PA_REP_UTIL_SUMM_TMP for total hours';
2732    PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2733    END IF;
2734 
2735    INSERT INTO pa_rep_util_summ_tmp
2736       (   record_type,
2737           object_id,
2738           version_id,
2739           object_type_code,
2740           balance_type_code,
2741           expenditure_org_id,
2742           expenditure_organization_id,
2743           person_id,
2744           assignment_id,
2745           work_type_id,
2746           org_util_category_id,
2747           res_util_category_id,
2748           period_type,
2749           period_set_name,
2750           period_name,
2751           global_exp_period_end_date,
2752           period_year,
2753           quarter_or_month_number,
2754           unit_of_measure,
2755           amount_type_id,
2756           period_balance,
2757           period_num,
2758           expenditure_type,
2759           expenditure_type_class,
2760           summ_level_flag,
2761           process_mode_flag)
2762     SELECT 'TMP2',
2763            tmp.object_id,
2764            tmp.version_id,
2765            tmp.object_type_code,
2766            tmp.balance_type_code,
2767            tmp.expenditure_org_id,
2768            org.parent_organization_id,
2769            tmp.person_id,
2770            tmp.assignment_id,
2771            tmp.work_type_id,
2772            tmp.org_util_category_id,
2773            tmp.res_util_category_id,
2774            tmp.period_type,
2775            tmp.period_set_name,
2776            tmp.period_name,
2777            tmp.global_exp_period_end_date,
2778            tmp.period_year,
2779            tmp.quarter_or_month_number,
2780            tmp.unit_of_measure,
2781            decode(dummytab.dummy_col,'S',
2782             decode(tmp.amount_type_id,l_dirct_tot_hrs_id,l_sub_org_tot_hrs_id,
2783                    l_dirct_tot_prov_hrs_id,l_sub_org_tot_prov_hrs_id,
2784                    l_dirct_tot_wght_hrs_org_id,l_sub_org_tot_wght_hrs_org_id,
2785                    l_dirct_prov_wght_hrs_org_id,l_sub_org_prov_wght_hrs_org_id,
2786                    l_dirct_cap_id,l_sub_org_cap_id,
2787                    l_dirct_reduce_cap_id,l_sub_org_reducedcap_id),
2788             decode(tmp.amount_type_id,l_dirct_tot_hrs_id,l_org_tot_hrs_id,
2789                    l_dirct_tot_prov_hrs_id,l_org_tot_prov_hrs_id,
2790                    l_dirct_tot_wght_hrs_org_id,l_org_tot_wght_hrs_org_id,
2791                    l_dirct_prov_wght_hrs_org_id,l_org_prov_wght_hrs_org_id,
2792                    l_dirct_cap_id,l_org_tot_cap_id,
2793                    l_dirct_reduce_cap_id,l_org_tot_reducedcap_id)),
2794            tmp.period_balance,
2795            tmp.period_num,
2796            tmp.expenditure_type,
2797            tmp.expenditure_type_class,
2798            'O',
2799            'II'
2800        from  pa_rep_util_summ_tmp tmp,
2801              pa_org_hierarchy_denorm org,
2802              pa_implementations imp,
2803             (select 'T' dummy_col from dual union select 'S' from dual) dummytab
2804        where tmp.summ_level_flag  = 'O'
2805        and   tmp.record_type = 'TMP2'
2806 --  new line below
2807        and   tmp.process_mode_flag = 'II'
2808        and   org.pa_org_use_type = 'REPORTING'
2809        and   org.org_id          = l_exp_org_id
2810        and   imp.org_structure_version_id = org.org_hierarchy_version_id
2811        and   org.child_organization_id =  tmp.expenditure_organization_id
2812        and   ((dummytab.dummy_col = 'S'
2813        and    org.child_organization_id <> org.parent_organization_id)
2814        or    (dummytab.dummy_col = 'T'));
2815 
2816   PA_DEBUG.Reset_curr_function;
2817 
2818 EXCEPTION
2819   WHEN OTHERS
2820   THEN
2821   IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2822       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2823       PA_DEBUG.log_message(SQLERRM);
2824       END IF;
2825        raise;
2826 END populate_incremental_rollup;
2827 
2828 PROCEDURE populate_summ_entity(P_Balance_Type_Code IN VARCHAR2,
2829                                p_process_method IN VARCHAR2)
2830 IS
2831 
2832 BEGIN
2833 
2834    PA_DEBUG.set_curr_function('populate_summ_entity');
2835 
2836   /*
2837    * Assign P_Balance_Type_Code to package variable for future use.
2838    */
2839   l_balance_type_code := P_Balance_Type_Code;
2840 
2841   /*
2842    * Assign p_process_method to package variable for future use.
2843    */
2844   l_delete_flag       := p_process_method;
2845 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2846   PA_DEBUG.g_err_stage := 'Summarize the Data by Period ';
2847   PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2848   END IF;
2849   /*
2850    * Summarize the data period wise from Global PL/SQL Table  and
2851    * populate global temprary table with periodwise summarized data.
2852    */
2853   summarize_by_period;
2854 
2855 
2856 
2857 
2858   /*
2859    * Call the actual procedure to summarize data from global temporary
2860    * table.
2861    */
2862    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2863   PA_DEBUG.g_err_stage := 'Summarize the Data by Object Type ';
2864   PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2865   END IF;
2866 
2867   summarize_temp_data_by_res;
2868 
2869   IF PA_REP_UTIL_GLOB.G_is_this_first_fetch = 'Y'  THEN
2870           populate_tmp_for_capacity;
2871           PA_REP_UTIL_GLOB.G_is_this_first_fetch := 'N';
2872   END IF;
2873 
2874   summarize_temp_data_by_org;
2875 
2876   /*
2877    * If incremental rollup is enabled, populate the pa_rep_util_summ_tmp.
2878    */
2879   IF (l_org_rollup_method = 'I')
2880   THEN
2881   IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2882     PA_DEBUG.g_err_stage := 'Processing Incremental Rollup';
2883     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2884     END IF;
2885     populate_incremental_rollup;
2886     IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2887     PA_DEBUG.g_err_stage := 'After Processing Incremental Rollup';
2888     PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2889     END IF;
2890 
2891   END IF;
2892 
2893   /*
2894    * Populate the object entity from pa_rep_util_summ_tmp
2895    * for record_type='TMP2'.
2896    */
2897    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2898   PA_DEBUG.g_err_stage := 'Before calling populate_object_entity';
2899   PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2900   END IF;
2901 
2902   populate_object_entity;
2903 
2904   /*
2905    * Populate the balance entity from pa_rep_util_summ_tmp
2906    * for record_type='TMP2'.
2907    */
2908    IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2909   PA_DEBUG.g_err_stage := 'Before calling populate_balance_entity';
2910   PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2911   END IF;
2912 
2913   populate_balance_entity;
2914 
2915   IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2916   PA_DEBUG.g_err_stage := 'After calling populate_balance_entity';
2917   PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2918   END IF;
2919 
2920   PA_DEBUG.Reset_curr_function;
2921 
2922 EXCEPTION
2923   WHEN OTHERS
2924   THEN
2925   IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2926       PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2927       PA_DEBUG.log_message(SQLERRM);
2928       END IF;
2929        raise;
2930 
2931 END populate_summ_entity;
2932 
2933 END PA_REP_UTILS_SUMM_PKG;