[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;