[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 2005/05/30 18:35:09 appldev noship $ */
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 */
436 END IF;
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);
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,
588 NULL,
585 NULL,
586 NULL,
587 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,
744 PA_QUARTER_NUMBER,
741 PA_PERIOD_NAME,
742 PA_PERIOD_NUM,
743 PA_PERIOD_YEAR,
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
906 ,QUARTER_OR_MONTH_NUMBER
903 ,PERIOD_NAME
904 ,PERIOD_NUM
905 ,PERIOD_YEAR
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
1059 ,GL_QUARTER_NUMBER
1056 ,GL_PERIOD_NAME
1057 ,GL_PERIOD_NUM
1058 ,GL_PERIOD_YEAR
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
1215 ,PERSON_ID
1212 INSERT INTO pa_rep_util_summ_tmp
1213 ( RECORD_TYPE
1214 ,EXPENDITURE_ORGANIZATION_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'
1374 END IF;
1371 FROM pa_rep_util_summ0_tmp
1372 WHERE REDUCE_CAPACITY <> 0;
1373
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)
1528 l_gl_c,
1525 SELECT 'TMP1A',
1526 NULL,
1527 -1,
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);
1678 END summarize_by_period;
1675 END IF;
1676 raise;
1677
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 group by period_type,
1770 period_name,
1771 global_exp_period_end_date,
1772 amount_type_id,
1773 expenditure_organization_id,
1774 person_id,
1775 cube(org_util_category_id,
1776 res_util_category_id,
1777 work_type_id)
1778 having (to_char(grouping(org_util_category_id))||
1779 to_char(grouping(res_util_category_id))||
1780 to_char(grouping(work_type_id)))
1781 in ( '111','011', '101', '000');
1782
1783 PA_DEBUG.Reset_curr_function;
1784
1785 EXCEPTION
1786 WHEN OTHERS
1787 THEN
1788 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1789 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1790 PA_DEBUG.log_message(SQLERRM);
1791 END IF;
1792 raise;
1793 END summarize_temp_data_by_res;
1794
1795 /*
1796 * Insert Records for Utilization Capacity.
1797 */
1798 PROCEDURE populate_tmp_for_capacity
1799 IS
1800
1801 CURSOR capacity_api_input_cur(p_start_date DATE,p_end_date DATE)
1802 IS
1803 SELECT distinct
1804 resource_organization_id
1805 , person_id
1806 , resource_id
1807 , resource_effective_start_date
1808 , resource_effective_end_date
1809 FROM pa_resources_denorm
1810 WHERE resource_org_id = PA_REP_UTIL_GLOB.GetOrgId
1811 AND utilization_flag = 'Y'
1812 AND (
1816 OR
1813 (RESOURCE_EFFECTIVE_START_DATE BETWEEN p_start_date AND p_end_date)
1814 OR
1815 (RESOURCE_EFFECTIVE_END_DATE BETWEEN p_start_date AND p_end_date)
1817 (p_start_date BETWEEN RESOURCE_EFFECTIVE_START_DATE AND RESOURCE_EFFECTIVE_END_DATE)
1818 );
1819
1820
1821 /*
1822 * Define PL/SQL Table for input and return values.
1823 */
1824 l_in_exp_orgz_tab PA_PLSQL_DATATYPES.IdTabTyp;
1825 l_in_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1826 l_in_resource_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1827 l_in_res_eff_s_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1828 l_in_res_eff_e_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1829 l_out_exp_orgz_tab PA_PLSQL_DATATYPES.IdTabTyp;
1830 l_out_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1831 l_period_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1832 l_period_name_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1833 l_global_exp_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
1834 l_period_year_tab PA_PLSQL_DATATYPES.NumTabTyp;
1835 l_qm_number_tab PA_PLSQL_DATATYPES.NumTabTyp;
1836 l_period_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1837 l_period_balance_tab PA_PLSQL_DATATYPES.NumTabTyp;
1838
1839 /*
1840 * Define other variable to be used in this procedure
1841 */
1842 l_return_status VARCHAR2(10);
1843 l_msg_count NUMBER;
1844 l_msg_data VARCHAR2(240);
1845 I PLS_INTEGER;
1846 l_last_fetch VARCHAR2(1):='N';
1847 l_this_fetch NUMBER:=0;
1848 l_totally_fetched NUMBER:=0;
1849 l_run_start_date DATE;
1850 l_run_end_date DATE;
1851 -- l_period_set_name VARCHAR2(15) := PA_REP_UTIL_GLOB.GetPeriodSetName;
1852 l_gl_period_set_name VARCHAR2(15) := PA_REP_UTIL_GLOB.G_implementation_details.G_gl_period_set_name; -- Bug 3434019
1853 l_pa_period_set_name VARCHAR2(15) := PA_REP_UTIL_GLOB.G_implementation_details.G_pa_period_set_name; -- Bug 3434019
1854 l_gl_c VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_GL_C; -- Bug 3434019
1855 l_pa_c VARCHAR2(3) := PA_REP_UTIL_GLOB.G_PERIOD_TYPE_C.G_PA_C; -- Bug 3434019
1856
1857 BEGIN
1858
1859
1860 PA_DEBUG.set_curr_function('populate_tmp_for_capacity');
1861 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1862 PA_DEBUG.g_err_stage := 'Setting the start and end dates of the run';
1863 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1864 END IF;
1865 /*
1866 * First figure out which balance_type is the current call for so as
1867 * to set the appropriate start and end dates for the run
1868 */
1869 IF l_balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals then
1870 l_run_start_date := PA_REP_UTIL_GLOB.G_input_parameters.G_ac_start_date;
1871 l_run_end_date := PA_REP_UTIL_GLOB.G_input_parameters.G_ac_end_date;
1872 ELSIF l_balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast then
1873 l_run_start_date := PA_REP_UTIL_GLOB.G_input_parameters.G_fc_start_date;
1874 l_run_end_date := PA_REP_UTIL_GLOB.G_input_parameters.G_fc_end_date;
1875 END IF;
1876
1877 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1878 PA_DEBUG.g_err_stage := 'Opening the Cursor';
1879 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1880 END IF;
1881
1882 OPEN capacity_api_input_cur(l_run_start_date,l_run_end_date);
1883
1884 LOOP
1885
1886 /*
1887 * Clear all PL/SQL table.
1888 */
1889 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1890 PA_DEBUG.g_err_stage := 'Clearing PL/SQL Table';
1891 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1892 END IF;
1893
1894 l_in_exp_orgz_tab.delete;
1895 l_in_person_id_tab.delete;
1896 l_in_resource_id_tab.delete;
1897 l_in_res_eff_s_date_tab.delete;
1898 l_in_res_eff_e_date_tab.delete;
1899 l_out_exp_orgz_tab.delete;
1900 l_out_person_id_tab.delete;
1901 l_period_type_tab.delete;
1902 l_period_name_tab.delete;
1903 l_global_exp_date_tab.delete;
1904 l_period_year_tab.delete;
1905 l_qm_number_tab.delete;
1906 l_period_num_tab.delete;
1907 l_period_balance_tab.delete;
1908
1909 /*
1910 * Fetch 100 records at a time.
1911 */
1912 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1913 PA_DEBUG.g_err_stage := 'Fetching 100 records at a time in PL/SQL Table';
1914 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1915 END IF;
1916
1917 FETCH capacity_api_input_cur BULK COLLECT
1918 INTO l_in_exp_orgz_tab
1919 , l_in_person_id_tab
1920 , l_in_resource_id_tab
1921 , l_in_res_eff_s_date_tab
1922 , l_in_res_eff_e_date_tab LIMIT 100;
1923
1924
1925 /*
1926 * To check the rows fetched in this fetch
1927 */
1928 l_this_fetch := capacity_api_input_cur%ROWCOUNT - l_totally_fetched;
1929 l_totally_fetched := capacity_api_input_cur%ROWCOUNT;
1930
1931 /*
1932 * Check if this fetch has 0 rows returned (ie last fetch was even 100)
1933 * This could happen in 2 cases
1934 * 1) this fetch is the very first fetch with 0 rows returned
1935 * OR 2) the last fetch returned an even 100 rows
1939 EXIT;
1936 * If either then EXIT without any processing
1937 */
1938 IF l_this_fetch = 0 then
1940 END IF;
1941
1942 /*
1943 * Check if this fetch is the last fetch
1944 * If so then set the flag l_last_fetch so as to exit after processing
1945 */
1946 IF l_this_fetch < 100 then
1947 l_last_fetch := 'Y';
1948 ELSE
1949 l_last_fetch := 'N';
1950 END IF;
1951
1952
1953 /*
1954 * Call CV's API in loop get value.
1955 */
1956 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1957 PA_DEBUG.g_err_stage := 'Calling Capacity API for 100 records at a time';
1958 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1959 END IF;
1960
1961 PA_FORECAST_GRC_PVT.Get_Capacity_Vector(
1962 p_OU_id => l_exp_org_id
1963 , p_exp_org_id_tab => l_in_exp_orgz_tab
1964 , p_person_id_tab => l_in_person_id_tab
1965 , p_resource_id_tab => l_in_resource_id_tab
1966 , p_in_res_eff_s_date_tab => l_in_res_eff_s_date_tab
1967 , p_in_res_eff_e_date_tab => l_in_res_eff_e_date_tab
1968 , p_balance_type_code => l_balance_type_code
1969 , p_run_start_date => l_run_start_date
1970 , p_run_end_date => l_run_end_date
1971 , x_resource_capacity_tab => l_period_balance_tab
1972 , x_exp_orgz_id_tab => l_out_exp_orgz_tab
1973 , x_person_id_tab => l_out_person_id_tab
1974 , x_period_type_tab => l_period_type_tab
1975 , x_period_name_tab => l_period_name_tab
1976 , x_global_exp_date_tab => l_global_exp_date_tab
1977 , x_period_year_tab => l_period_year_tab
1978 , x_qm_number_tab => l_qm_number_tab
1979 , x_period_num_tab => l_period_num_tab
1980 , x_return_status => l_return_status
1981 , x_msg_count => l_msg_count
1982 , x_msg_data => l_msg_data);
1983
1984 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1985 PA_DEBUG.g_err_stage := 'Inserting Records into pa_rep_util_summ_tmp for capacity';
1986 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
1987 END IF;
1988
1989 FORALL I in l_out_person_id_tab.FIRST..l_out_person_id_tab.LAST
1990 INSERT INTO pa_rep_util_summ_tmp
1991 ( record_type,
1992 object_id,
1993 version_id,
1994 object_type_code,
1995 balance_type_code,
1996 expenditure_org_id,
1997 expenditure_organization_id,
1998 person_id,
1999 assignment_id,
2000 work_type_id,
2001 org_util_category_id,
2002 res_util_category_id,
2003 period_type,
2004 period_set_name,
2005 period_name,
2006 global_exp_period_end_date,
2007 period_year,
2008 quarter_or_month_number,
2009 unit_of_measure,
2010 amount_type_id,
2011 period_balance,
2012 period_num,
2013 expenditure_type,
2014 expenditure_type_class,
2015 summ_level_flag,
2016 process_mode_flag)
2017 VALUES (
2018 'TMP2',
2019 NULL,
2020 -1,
2021 l_res_c,
2022 l_balance_type_code,
2023 l_exp_org_id,
2024 l_out_exp_orgz_tab(I),
2025 l_out_person_id_tab(I),
2026 -1,
2027 -1,
2028 -1,
2029 -1,
2030 l_period_type_tab(I),
2031 -- DECODE(l_period_name_tab(I)
2032 -- , l_dummy_period_name, l_dummy_period_set_name
2033 -- , l_period_set_name) ,
2034 DECODE(l_period_name_tab(I)
2035 , l_dummy_period_name, l_dummy_period_set_name, decode(l_period_type_tab(I), l_gl_c
2036 , l_gl_period_set_name, l_pa_period_set_name) ), -- Bug 3434019
2037 l_period_name_tab(I),
2038 l_global_exp_date_tab(I),
2039 l_period_year_tab(I),
2040 l_qm_number_tab(I),
2041 l_unit_of_measure,
2042 l_tot_cap_id,
2043 l_period_balance_tab(I),
2044 l_period_num_tab(I),
2045 NULL,
2046 NULL,
2047 'S',
2048 'II');
2049
2050 /*
2051 * Check if this loop is the last set of 100
2052 * If so then EXIT;
2053 */
2054 IF l_last_fetch='Y' THEN
2055 EXIT;
2056 END IF;
2057
2058 END LOOP;
2059 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2060 PA_DEBUG.g_err_stage := 'Closing the cursor';
2061 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2062 END IF;
2063
2064 CLOSE capacity_api_input_cur;
2065
2066 PA_DEBUG.Reset_curr_function;
2067
2068 EXCEPTION
2072 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2069 WHEN OTHERS
2070 THEN
2071 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2073 PA_DEBUG.log_message(SQLERRM);
2074 END IF;
2075 raise;
2076 END populate_tmp_for_capacity;
2077
2078
2079 /*
2080 * Summarize the resource level records to Organization level.
2081 */
2082 PROCEDURE summarize_temp_data_by_org
2083 IS
2084 BEGIN
2085 PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.summarize_temp_data_by_org');
2086 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2087 PA_DEBUG.g_err_stage := 'Summarizing at Organization level';
2088 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2089 END IF;
2090
2091 INSERT INTO pa_rep_util_summ_tmp
2092 ( record_type,
2093 object_id,
2094 version_id,
2095 object_type_code,
2096 balance_type_code,
2097 expenditure_org_id,
2098 expenditure_organization_id,
2099 person_id,
2100 assignment_id,
2101 work_type_id,
2102 org_util_category_id,
2103 res_util_category_id,
2104 period_type,
2105 period_set_name,
2106 period_name,
2107 global_exp_period_end_date,
2108 period_year,
2109 quarter_or_month_number,
2110 unit_of_measure,
2111 amount_type_id,
2112 period_balance,
2113 period_num,
2114 expenditure_type,
2115 expenditure_type_class,
2116 summ_level_flag,
2117 process_mode_flag)
2118 SELECT 'TMP2',
2119 NULL,
2120 -1,
2121 decode(grouping(org_util_category_id)||grouping(work_type_id),
2122 '11',l_org_c,
2123 '01',l_orguc_c,
2124 '00',l_orgwt_c),
2125 l_balance_type_code,
2126 l_exp_org_id,
2127 expenditure_organization_id,
2128 -1,
2129 -1,
2130 nvl(work_type_id,-1),
2131 nvl(org_util_category_id,-1),
2132 -1,
2133 period_type,
2134 -- DECODE(period_type
2135 -- , l_ge_c, l_dummy_period_set_name
2136 -- , l_period_set_name) ,
2137 DECODE(period_type
2138 , l_ge_c, l_dummy_period_set_name, l_gl_c, l_gl_period_set_name
2139 , l_pa_period_set_name) , -- Bug 3434019
2140 period_name,
2141 global_exp_period_end_date,
2142 max(period_year),
2143 max(quarter_or_month_number),
2144 l_unit_of_measure,
2145 /*
2146 * Convert the Utilization and resource level amount type
2147 * to Direct organization level amount types.
2148 */
2149 decode(amount_type_id,l_tot_hrs_id,l_dirct_tot_hrs_id,
2150 l_tot_prov_hrs_id,l_dirct_tot_prov_hrs_id,
2151 l_tot_wght_hrs_org_id,l_dirct_tot_wght_hrs_org_id,
2152 l_prov_wght_hrs_org_id,l_dirct_prov_wght_hrs_org_id,
2153 l_red_cap_id,l_dirct_reduce_cap_id,
2154 l_tot_cap_id,l_dirct_cap_id),
2155 sum(period_balance),
2156 max(period_num),
2157 NULL,
2158 NULL,
2159 'O',
2160 'II'
2161 from pa_rep_util_summ_tmp
2162 where summ_level_flag in ( 'R','S')
2163 and record_type = 'TMP2'
2164 and process_mode_flag = 'II'
2165 and amount_type_id not in (l_tot_wght_hrs_people_id,
2166 l_prov_wght_hrs_people_id)
2167 group by period_type,
2168 period_name,
2169 global_exp_period_end_date,
2170 amount_type_id,
2171 expenditure_organization_id,
2172 summ_level_flag,
2173 rollup(org_util_category_id,
2174 work_type_id)
2175 having ((summ_level_flag = 'S'
2176 and grouping(org_util_category_id)||grouping(work_type_id) = '11')
2177 or ( summ_level_flag = 'R'));
2178
2179 PA_DEBUG.Reset_curr_function;
2180
2181 EXCEPTION
2182 WHEN OTHERS
2183 THEN
2184 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2185 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2186 PA_DEBUG.log_message(SQLERRM);
2187 END IF;
2188 raise;
2189 END summarize_temp_data_by_org;
2190
2191 /** This procedure will find the object Id for each record of
2192 pa_rep_util_summ_tmp with record type = 'TMP2' and process_mode_flag = 'II'
2193 and populate it. If not found, generate an object id **/
2194
2195
2196 PROCEDURE populate_object_entity IS
2197
2198 BEGIN
2199
2200 PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.summarize_temp_data_by_org');
2201
2202 /*
2203 * Update the pa_rep_util_summ_tmp with matching object Id.
2204 */
2205 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2206 PA_DEBUG.g_err_stage := 'Update pa_rep_util_summ_tmp with matching object Id';
2207 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2208 END IF;
2209
2213 from pa_objects OB
2210 UPDATE pa_rep_util_summ_tmp T
2211 SET (T.object_id,T.process_mode_flag)
2212 = ( select OB.object_id ,'UI'
2214 where OB.OBJECT_TYPE_CODE = T.OBJECT_TYPE_CODE
2215 and OB.BALANCE_TYPE_CODE = T.BALANCE_TYPE_CODE
2216 and OB.EXPENDITURE_ORG_ID = T.EXPENDITURE_ORG_ID
2217 and OB.EXPENDITURE_ORGANIZATION_ID
2218 = T.EXPENDITURE_ORGANIZATION_ID
2219 and OB.PERSON_ID = T.PERSON_ID
2220 and OB.ASSIGNMENT_ID = T.ASSIGNMENT_ID
2221 and OB.WORK_TYPE_ID = T.WORK_TYPE_ID
2222 and OB.ORG_UTIL_CATEGORY_ID
2223 = T.ORG_UTIL_CATEGORY_ID
2224 and OB.RES_UTIL_CATEGORY_ID
2225 = T.RES_UTIL_CATEGORY_ID
2226 and nvl(OB.EXPENDITURE_TYPE,'-1')
2227 = nvl(T.EXPENDITURE_TYPE,'-1')
2228 and nvl(OB.EXPENDITURE_TYPE_CLASS,'-1')
2229 = nvl(T.EXPENDITURE_TYPE_CLASS,'-1')
2230 and OB.PROJECT_ORG_ID = -1
2231 and OB.PROJECT_ORGANIZATION_ID = -1
2232 and OB.PROJECT_ID = -1
2233 and OB.TASK_ID = -1)
2234 WHERE T.record_type = 'TMP2'
2235 -- mpuvathi: since all are 'II' till now
2236 AND T.process_mode_flag = 'II'
2237 ;
2238
2239 /*
2240 * Populate pa_rep_util_summ_tmp with unique key for pa_objects.
2241 */
2242 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2243 PA_DEBUG.g_err_stage := 'Populate pa_rep_util_summ_tmp with unique key for pa_objects';
2244 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2245 END IF;
2246
2247 INSERT INTO pa_rep_util_summ_tmp
2248 ( record_type,
2249 object_id,
2250 version_id,
2251 object_type_code,
2252 balance_type_code,
2253 expenditure_org_id,
2254 expenditure_organization_id,
2255 person_id,
2256 assignment_id,
2257 work_type_id,
2258 org_util_category_id,
2259 res_util_category_id,
2260 period_type,
2261 period_set_name,
2262 period_name,
2263 global_exp_period_end_date,
2264 period_year,
2265 quarter_or_month_number,
2266 unit_of_measure,
2267 amount_type_id,
2268 period_balance,
2269 period_num,
2270 expenditure_type,
2271 expenditure_type_class,
2272 summ_level_flag,
2273 process_mode_flag)
2274 SELECT 'TMP3',
2275 pa_objects_s.nextval,
2276 NULL,
2277 T1.object_type_code,
2278 T1.balance_type_code,
2279 T1.expenditure_org_id,
2280 T1.expenditure_organization_id,
2281 T1.person_id,
2282 T1.assignment_id,
2283 T1.work_type_id,
2284 T1.org_util_category_id,
2285 T1.res_util_category_id,
2286 NULL,
2287 NULL,
2288 NULL,
2289 NULL,
2290 NULL,
2291 NULL,
2292 NULL,
2293 NULL,
2294 NULL,
2295 NULL,
2296 T1.expenditure_type,
2297 T1.expenditure_type_class,
2298 'H',
2299 'HH'
2300 FROM pa_rep_util_summ_tmp T1
2301 WHERE T1.record_type = 'TMP2'
2302 -- mpuvathi: since even process_mode_flag would have been updated to NULL
2303 AND T1.process_mode_flag is NULL
2304 AND T1.object_id IS NULL
2305 AND T1.rowid in (SELECT max(T2.rowid)
2306 FROM pa_rep_util_summ_tmp T2
2307 -- mpuvathi: since even process_mode_flag would have been updated to NULL
2308 WHERE T2.process_mode_flag is NULL
2309 AND T2.object_id IS NULL
2310 AND T2.record_type = 'TMP2'
2311 GROUP BY
2312 T2.OBJECT_TYPE_CODE
2313 , T2.BALANCE_TYPE_CODE
2314 , T2.EXPENDITURE_ORGANIZATION_ID
2315 , T2.PERSON_ID
2316 , T2.ASSIGNMENT_ID
2317 , T2.WORK_TYPE_ID
2318 , T2.ORG_UTIL_CATEGORY_ID
2319 , T2.RES_UTIL_CATEGORY_ID
2320 )
2321 ;
2322
2323
2324 /*
2325 * Populate the other records of pa_rep_util_summ_tmp with
2326 * record type = 'TMP2' and null object Id.
2327 */
2328 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2329 PA_DEBUG.g_err_stage := 'Populate the other records of pa_rep_util_summ_tmp with record type =TMP2 and null object Id';
2330 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2331 END IF;
2332
2333 UPDATE pa_rep_util_summ_tmp T1
2334 SET (T1.object_id ,
2338 = T2.OBJECT_TYPE_CODE
2335 T1.process_mode_flag) = ( SELECT T2.object_id,'II'
2336 FROM pa_rep_util_summ_tmp T2
2337 WHERE T1.OBJECT_TYPE_CODE
2339 AND T1.BALANCE_TYPE_CODE
2340 = T2.BALANCE_TYPE_CODE
2341 AND T1.EXPENDITURE_ORGANIZATION_ID
2342 = T2.EXPENDITURE_ORGANIZATION_ID
2343 AND T1.PERSON_ID = T2.PERSON_ID
2344 -- AND T1.ASSIGNMENT_ID = T2.ASSIGNMENT_ID
2345 AND T1.WORK_TYPE_ID = T2.WORK_TYPE_ID
2346 AND T1.ORG_UTIL_CATEGORY_ID
2347 = T2.ORG_UTIL_CATEGORY_ID
2348 AND T1.RES_UTIL_CATEGORY_ID
2349 = T2.RES_UTIL_CATEGORY_ID
2350 -- AND nvl(T1.EXPENDITURE_TYPE,'-1') = nvl(T2.EXPENDITURE_TYPE,'-1')
2351 -- AND nvl(T1.EXPENDITURE_TYPE_CLASS,'-1') = nvl(T2.EXPENDITURE_TYPE_CLASS,'-1')
2352 AND T2.record_type = 'TMP3'
2353 AND T2.process_mode_flag = 'HH'
2354 )
2355 WHERE T1.record_type = 'TMP2'
2356 -- mpuvathi: since even process_mode_flag would have been updated to NULL
2357 AND T1.process_mode_flag is NULL
2358 -- AND nvl(T1.process_mode_flag,'II') <> 'UI'
2359 AND T1.object_id IS NULL
2360 ;
2361
2362
2363 /*
2364 * Insert New Objects in PA_OBJECTS.
2365 */
2366 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2367 PA_DEBUG.g_err_stage := 'Insert New Objects in PA_OBJECTS';
2368 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2369 END IF;
2370
2371 INSERT INTO PA_OBJECTS
2372 ( OBJECT_ID,
2373 OBJECT_TYPE_CODE,
2374 BALANCE_TYPE_CODE,
2375 PROJECT_ORG_ID,
2376 PROJECT_ORGANIZATION_ID,
2377 PROJECT_ID,
2378 TASK_ID,
2379 EXPENDITURE_ORG_ID,
2380 EXPENDITURE_ORGANIZATION_ID,
2381 PERSON_ID,
2382 ASSIGNMENT_ID,
2383 WORK_TYPE_ID,
2384 ORG_UTIL_CATEGORY_ID,
2385 RES_UTIL_CATEGORY_ID,
2386 EXPENDITURE_TYPE,
2387 EXPENDITURE_TYPE_CLASS,
2388 LAST_UPDATE_DATE,
2389 LAST_UPDATED_BY,
2390 CREATION_DATE,
2391 CREATED_BY,
2392 LAST_UPDATE_LOGIN,
2393 REQUEST_ID,
2394 PROGRAM_APPLICATION_ID,
2395 PROGRAM_ID,
2396 PROGRAM_UPDATE_DATE)
2397 SELECT OBJECT_ID,
2398 OBJECT_TYPE_CODE,
2399 BALANCE_TYPE_CODE,
2400 -1,
2401 -1,
2402 -1,
2403 -1,
2404 l_exp_org_id,
2405 EXPENDITURE_ORGANIZATION_ID,
2406 PERSON_ID,
2407 ASSIGNMENT_ID,
2408 WORK_TYPE_ID,
2409 ORG_UTIL_CATEGORY_ID,
2410 RES_UTIL_CATEGORY_ID,
2411 EXPENDITURE_TYPE,
2412 EXPENDITURE_TYPE_CLASS,
2413 l_last_update_date,
2414 l_last_updated_by,
2415 l_creation_date,
2416 l_created_by,
2417 l_last_update_login,
2418 l_request_id,
2419 l_program_application_id,
2420 l_program_id,
2421 l_creation_date
2422 FROM pa_rep_util_summ_tmp
2423 WHERE record_type = 'TMP3'
2424 AND summ_level_flag = 'H'
2425 AND process_mode_flag = 'HH'
2426 AND object_type_code <> l_utildet_c
2427 ;
2428
2429 PA_DEBUG.Reset_curr_function;
2430
2431 EXCEPTION
2432 WHEN OTHERS
2433 THEN
2434 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2435 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2436 PA_DEBUG.log_message(SQLERRM);
2437 END IF;
2438 raise;
2439 END populate_object_entity;
2440
2441 /*
2442 * This procedure checks whether any matching record exists in
2443 * PA_SUMM_BALANCES, if yes, it will update the record. If no,it
2444 * will insert a new record in PA_SUMM_BALANCES.
2445 */
2446
2447 PROCEDURE populate_balance_entity IS
2448
2449 /*
2450 * Define PL/SQL Table for holding the fetched records from the cursor
2451 * before inserting into the global temporary table pa_rep_util_summ0_tmp
2452 */
2453 L_PERIOD_BALANCE_TAB PA_PLSQL_DATATYPES.NumTabTyp;
2454 L_OBJECT_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
2455 L_VERSION_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
2456 L_OBJECT_TYPE_CODE_TAB PA_PLSQL_DATATYPES.CHAR15TabTyp;
2457 L_PERIOD_TYPE_TAB PA_PLSQL_DATATYPES.CHAR15TabTyp;
2458 L_PERIOD_SET_NAME_TAB PA_PLSQL_DATATYPES.CHAR15TabTyp;
2459 L_PERIOD_NAME_TAB PA_PLSQL_DATATYPES.CHAR15TabTyp;
2460 L_GLOBAL_EXP_END_DATE_TAB PA_PLSQL_DATATYPES.DateTabTyp;
2461 L_PERIOD_YEAR_TAB PA_PLSQL_DATATYPES.NumTabTyp;
2462 L_QUARTER_OR_MONTH_NUMBER_TAB PA_PLSQL_DATATYPES.NumTabTyp;
2463 L_AMOUNT_TYPE_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
2464
2468 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_dir_cap_id;
2465 l_tot_cap_id pa_amount_types_b.amount_type_id%TYPE
2466 := PA_REP_UTIL_GLOB.G_amt_type_details.G_res_cap_id;
2467 l_dirct_cap_id pa_amount_types_b.amount_type_id%TYPE
2469 l_sub_org_cap_id pa_amount_types_b.amount_type_id%TYPE
2470 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_cap_id;
2471 l_org_tot_cap_id pa_amount_types_b.amount_type_id%TYPE
2472 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_cap_id;
2473
2474 l_totally_fetched NUMBER := 0;
2475
2476 CURSOR cur_update_bal
2477 IS
2478 SELECT
2479 SUM(T.period_balance) period_balance
2480 , T.OBJECT_ID OBJECT_ID
2481 , T.VERSION_ID VERSION_ID
2482 , T.OBJECT_TYPE_CODE OBJECT_TYPE_CODE
2483 , T.PERIOD_TYPE PERIOD_TYPE
2484 , T.PERIOD_SET_NAME PERIOD_SET_NAME
2485 , T.PERIOD_NAME PERIOD_NAME
2486 , T.GLOBAL_EXP_PERIOD_END_DATE GLOBAL_EXP_PERIOD_END_DATE
2487 , T.PERIOD_YEAR PERIOD_YEAR
2488 , T.QUARTER_OR_MONTH_NUMBER QUARTER_OR_MONTH_NUMBER
2489 , T.AMOUNT_TYPE_ID AMOUNT_TYPE_ID
2490 FROM pa_rep_util_summ_tmp T
2491 WHERE
2492 T.RECORD_TYPE = 'TMP4'
2493 AND T.PROCESS_MODE_FLAG = 'U'
2494 GROUP BY
2495 T.OBJECT_ID
2496 , T.VERSION_ID
2497 , T.OBJECT_TYPE_CODE
2498 , T.PERIOD_TYPE
2499 , T.PERIOD_SET_NAME
2500 , T.PERIOD_NAME
2501 , T.GLOBAL_EXP_PERIOD_END_DATE
2502 , T.PERIOD_YEAR
2503 , T.QUARTER_OR_MONTH_NUMBER
2504 , T.AMOUNT_TYPE_ID
2505 ;
2506
2507 rec_update_bal cur_update_bal%ROWTYPE;
2508
2509 BEGIN
2510
2511 PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.populate_balance_entity');
2512
2513 /*
2514 * Update the global temporary table for successful update.
2515 */
2516 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2517 PA_DEBUG.g_err_stage := 'Update the global temporary table for successful update1';
2518 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2519 END IF;
2520
2521 UPDATE pa_rep_util_summ_tmp B
2522 SET B.process_mode_flag = 'U'
2523 , B.record_type = 'TMP4'
2524 WHERE exists( SELECT T.period_balance
2525 FROM pa_summ_balances T
2526 WHERE T.OBJECT_ID = B.OBJECT_ID
2527 AND T.VERSION_ID = B.VERSION_ID
2528 AND T.OBJECT_TYPE_CODE = B.OBJECT_TYPE_CODE
2529 AND T.PERIOD_TYPE = B.PERIOD_TYPE
2530 AND T.PERIOD_SET_NAME = B.PERIOD_SET_NAME
2531 AND T.PERIOD_NAME = B.PERIOD_NAME
2532 AND T.GLOBAL_EXP_PERIOD_END_DATE
2533 = B.GLOBAL_EXP_PERIOD_END_DATE
2534 AND T.PERIOD_YEAR = B.PERIOD_YEAR
2535 AND T.QUARTER_OR_MONTH_NUMBER
2536 = B.QUARTER_OR_MONTH_NUMBER
2537 AND T.AMOUNT_TYPE_ID = B.AMOUNT_TYPE_ID)
2538 AND B.RECORD_TYPE = 'TMP2'
2539 -- AND nvl(B.process_mode_flag,'II') <> 'II'
2540 AND B.process_mode_flag = 'UI'
2541 ;
2542
2543 /*
2544 * Clear all PL/SQL table.
2545 */
2546 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2547 PA_DEBUG.g_err_stage := 'Clearing PL/SQL Table';
2548 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2549 END IF;
2550
2551 L_PERIOD_BALANCE_TAB.delete;
2552 L_OBJECT_ID_TAB.delete;
2553 L_VERSION_ID_TAB.delete;
2554 L_OBJECT_TYPE_CODE_TAB.delete;
2555 L_PERIOD_TYPE_TAB.delete;
2556 L_PERIOD_SET_NAME_TAB.delete;
2557 L_PERIOD_NAME_TAB.delete;
2558 L_GLOBAL_EXP_END_DATE_TAB.delete;
2559 L_PERIOD_YEAR_TAB.delete;
2560 L_QUARTER_OR_MONTH_NUMBER_TAB.delete;
2561 L_AMOUNT_TYPE_ID_TAB.delete;
2562
2563 /*
2564 * Update the balance entity for existing records of pa_rep_util_summ_tmp
2565 * marked for update.
2566 */
2567 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2568 PA_DEBUG.g_err_stage := 'Update the balance entity for existing records of pa_rep_util_summ_tmp marked for update';
2569 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2570 END IF;
2571
2572 IF cur_update_bal%ISOPEN then
2573 CLOSE cur_update_bal;
2574 END IF;
2575
2576 OPEN cur_update_bal;
2577
2578 FETCH cur_update_bal BULK COLLECT
2579 INTO
2580 L_PERIOD_BALANCE_TAB
2581 , L_OBJECT_ID_TAB
2582 , L_VERSION_ID_TAB
2583 , L_OBJECT_TYPE_CODE_TAB
2584 , L_PERIOD_TYPE_TAB
2585 , L_PERIOD_SET_NAME_TAB
2586 , L_PERIOD_NAME_TAB
2587 , L_GLOBAL_EXP_END_DATE_TAB
2588 , L_PERIOD_YEAR_TAB
2589 , L_QUARTER_OR_MONTH_NUMBER_TAB
2590 , L_AMOUNT_TYPE_ID_TAB
2591 ;
2592 l_totally_fetched := cur_update_bal%ROWCOUNT;
2593 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2597 PA_DEBUG.Log_Message( p_message => PA_DEBUG.g_err_stage);
2594 PA_DEBUG.g_err_stage := 'Records totally fetched from cur_update_bal'||l_totally_fetched||L_PERIOD_BALANCE_TAB.COUNT;
2595 PA_DEBUG.Log_Message( p_message => PA_DEBUG.g_err_stage);
2596 PA_DEBUG.g_err_stage := 'Before updating PA_SUMM_BALANCES from cur_update_bal';
2598 END IF;
2599
2600
2601 IF L_PERIOD_BALANCE_TAB.COUNT > 0 then
2602 FORALL I in L_PERIOD_BALANCE_TAB.FIRST..L_PERIOD_BALANCE_TAB.LAST
2603 UPDATE pa_summ_balances B
2604 set B.period_balance = (L_PERIOD_BALANCE_TAB(I)+
2605 DECODE(B.amount_type_id
2606 , l_tot_cap_id , 0
2607 , l_dirct_cap_id , 0
2608 , l_org_tot_cap_id , 0
2609 , l_sub_org_cap_id , 0
2610 , B.period_balance)
2611 )
2612 WHERE L_OBJECT_ID_TAB(I) = B.OBJECT_ID
2613 AND L_VERSION_ID_TAB(I) = B.VERSION_ID
2614 AND L_OBJECT_TYPE_CODE_TAB(I) = B.OBJECT_TYPE_CODE
2615 AND L_PERIOD_TYPE_TAB(I) = B.PERIOD_TYPE
2616 AND L_PERIOD_SET_NAME_TAB(I) = B.PERIOD_SET_NAME
2617 AND L_PERIOD_NAME_TAB(I) = B.PERIOD_NAME
2618 AND L_GLOBAL_EXP_END_DATE_TAB(I) = B.GLOBAL_EXP_PERIOD_END_DATE
2619 AND L_PERIOD_YEAR_TAB(I) = B.PERIOD_YEAR
2620 AND L_QUARTER_OR_MONTH_NUMBER_TAB(I) = B.QUARTER_OR_MONTH_NUMBER
2621 AND L_AMOUNT_TYPE_ID_TAB(I) = B.AMOUNT_TYPE_ID
2622 ;
2623 END IF;
2624 CLOSE cur_update_bal;
2625
2626
2627 /*
2628 * Insert new balance records from pa_rep_util_summ_tmp if needed.
2629 */
2630 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2631 PA_DEBUG.g_err_stage := 'Insert new balance records from pa_rep_util_summ_tmp if needed.';
2632 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2633 END IF;
2634
2635 INSERT INTO pa_summ_balances
2636 ( OBJECT_ID,
2637 VERSION_ID,
2638 OBJECT_TYPE_CODE,
2639 PERIOD_TYPE,
2640 PERIOD_SET_NAME ,
2641 PERIOD_NAME,
2642 GLOBAL_EXP_PERIOD_END_DATE,
2643 PERIOD_YEAR,
2644 QUARTER_OR_MONTH_NUMBER,
2645 AMOUNT_TYPE_ID,
2646 PERIOD_NUM,
2647 UNIT_OF_MEASURE ,
2648 PERIOD_BALANCE,
2649 PVDR_CURRENCY_CODE,
2650 PVDR_PERIOD_BALANCE)
2651 SELECT OBJECT_ID,
2652 -1,
2653 max(OBJECT_TYPE_CODE),
2654 PERIOD_TYPE,
2655 max(nvl(PERIOD_SET_NAME,l_dummy_period_set_name)),
2656 nvl(PERIOD_NAME,l_dummy_period_name),
2657 nvl(GLOBAL_EXP_PERIOD_END_DATE,l_dummy_ge_date),
2658 max(PERIOD_YEAR),
2659 max(QUARTER_OR_MONTH_NUMBER),
2660 AMOUNT_TYPE_ID,
2661 max(PERIOD_NUM),
2662 max(UNIT_OF_MEASURE),
2663 sum(PERIOD_BALANCE),
2664 NULL,
2665 NULL
2666 FROM pa_rep_util_summ_tmp
2667 WHERE RECORD_TYPE = 'TMP2'
2668 -- mpuvathi: for both UI and II
2669 AND PROCESS_MODE_FLAG in ('UI' , 'II')
2670 AND object_type_code <> l_utildet_c
2671 GROUP BY OBJECT_ID, PERIOD_TYPE, PERIOD_NAME,
2672 GLOBAL_EXP_PERIOD_END_DATE, AMOUNT_TYPE_ID;
2673
2674 PA_DEBUG.Reset_curr_function;
2675
2676 EXCEPTION
2677 WHEN OTHERS
2678 THEN
2679 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2680 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2681 PA_DEBUG.log_message(SQLERRM);
2682 END IF;
2683 raise;
2684 END populate_balance_entity;
2685
2686
2687 /*
2688 * This will populate the PA_REP_UTIL_SUMM_TMP table for incremental rollup.
2689 */
2690 PROCEDURE populate_incremental_rollup
2691 IS
2692 /*
2693 * Cache sub org and total level amount types.
2694 */
2695 l_org_tot_hrs_id pa_amount_types_b.amount_type_id%TYPE
2696 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_hrs_id;
2697 l_org_tot_wght_hrs_org_id pa_amount_types_b.amount_type_id%TYPE
2698 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_wtdhrs_org_id;
2699 l_org_tot_prov_hrs_id pa_amount_types_b.amount_type_id%TYPE
2700 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_prvhrs_id;
2701 l_org_prov_wght_hrs_org_id pa_amount_types_b.amount_type_id%TYPE
2702 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_prvwtdhrs_org_id;
2703 l_org_tot_cap_id pa_amount_types_b.amount_type_id%TYPE
2704 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_cap_id;
2705 l_org_tot_reducedcap_id pa_amount_types_b.amount_type_id%TYPE
2706 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_tot_reducedcap_id;
2707 l_sub_org_tot_hrs_id pa_amount_types_b.amount_type_id%TYPE
2708 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_hrs_id;
2709 l_sub_org_tot_prov_hrs_id pa_amount_types_b.amount_type_id%TYPE
2710 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_prvhrs_id;
2711 l_sub_org_tot_wght_hrs_org_id pa_amount_types_b.amount_type_id%TYPE
2712 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_wtdhrs_org_id;
2716 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_cap_id;
2713 l_sub_org_prov_wght_hrs_org_id pa_amount_types_b.amount_type_id%TYPE
2714 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_prvwtdhrs_org_id;
2715 l_sub_org_cap_id pa_amount_types_b.amount_type_id%TYPE
2717 l_sub_org_reducedcap_id pa_amount_types_b.amount_type_id%TYPE
2718 := PA_REP_UTIL_GLOB.G_amt_type_details.G_org_sub_reducedcap_id;
2719 /** End Cache sub org and total level amount types **/
2720
2721
2722 BEGIN
2723
2724 PA_DEBUG.set_curr_function('PA_REP_UTILS_SUMM_PKG.populate_incremental_rollup');
2725
2726 /*
2727 * populate PA_REP_UTIL_SUMM_TMP for total hours.
2728 */
2729 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2730 PA_DEBUG.g_err_stage := 'populate PA_REP_UTIL_SUMM_TMP for total hours';
2731 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2732 END IF;
2733
2734 INSERT INTO pa_rep_util_summ_tmp
2735 ( record_type,
2736 object_id,
2737 version_id,
2738 object_type_code,
2739 balance_type_code,
2740 expenditure_org_id,
2741 expenditure_organization_id,
2742 person_id,
2743 assignment_id,
2744 work_type_id,
2745 org_util_category_id,
2746 res_util_category_id,
2747 period_type,
2748 period_set_name,
2749 period_name,
2750 global_exp_period_end_date,
2751 period_year,
2752 quarter_or_month_number,
2753 unit_of_measure,
2754 amount_type_id,
2755 period_balance,
2756 period_num,
2757 expenditure_type,
2758 expenditure_type_class,
2759 summ_level_flag,
2760 process_mode_flag)
2761 SELECT 'TMP2',
2762 tmp.object_id,
2763 tmp.version_id,
2764 tmp.object_type_code,
2765 tmp.balance_type_code,
2766 tmp.expenditure_org_id,
2767 org.parent_organization_id,
2768 tmp.person_id,
2769 tmp.assignment_id,
2770 tmp.work_type_id,
2771 tmp.org_util_category_id,
2772 tmp.res_util_category_id,
2773 tmp.period_type,
2774 tmp.period_set_name,
2775 tmp.period_name,
2776 tmp.global_exp_period_end_date,
2777 tmp.period_year,
2778 tmp.quarter_or_month_number,
2779 tmp.unit_of_measure,
2780 decode(dummytab.dummy_col,'S',
2781 decode(tmp.amount_type_id,l_dirct_tot_hrs_id,l_sub_org_tot_hrs_id,
2782 l_dirct_tot_prov_hrs_id,l_sub_org_tot_prov_hrs_id,
2783 l_dirct_tot_wght_hrs_org_id,l_sub_org_tot_wght_hrs_org_id,
2784 l_dirct_prov_wght_hrs_org_id,l_sub_org_prov_wght_hrs_org_id,
2785 l_dirct_cap_id,l_sub_org_cap_id,
2786 l_dirct_reduce_cap_id,l_sub_org_reducedcap_id),
2787 decode(tmp.amount_type_id,l_dirct_tot_hrs_id,l_org_tot_hrs_id,
2788 l_dirct_tot_prov_hrs_id,l_org_tot_prov_hrs_id,
2789 l_dirct_tot_wght_hrs_org_id,l_org_tot_wght_hrs_org_id,
2790 l_dirct_prov_wght_hrs_org_id,l_org_prov_wght_hrs_org_id,
2791 l_dirct_cap_id,l_org_tot_cap_id,
2792 l_dirct_reduce_cap_id,l_org_tot_reducedcap_id)),
2793 tmp.period_balance,
2794 tmp.period_num,
2795 tmp.expenditure_type,
2796 tmp.expenditure_type_class,
2797 'O',
2798 'II'
2799 from pa_rep_util_summ_tmp tmp,
2800 pa_org_hierarchy_denorm org,
2801 pa_implementations imp,
2802 (select 'T' dummy_col from dual union select 'S' from dual) dummytab
2803 where tmp.summ_level_flag = 'O'
2804 and tmp.record_type = 'TMP2'
2805 -- new line below
2806 and tmp.process_mode_flag = 'II'
2807 and org.pa_org_use_type = 'REPORTING'
2808 and org.org_id = l_exp_org_id
2809 and imp.org_structure_version_id = org.org_hierarchy_version_id
2813 or (dummytab.dummy_col = 'T'));
2810 and org.child_organization_id = tmp.expenditure_organization_id
2811 and ((dummytab.dummy_col = 'S'
2812 and org.child_organization_id <> org.parent_organization_id)
2814
2815 PA_DEBUG.Reset_curr_function;
2816
2817 EXCEPTION
2818 WHEN OTHERS
2819 THEN
2820 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2821 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2822 PA_DEBUG.log_message(SQLERRM);
2823 END IF;
2824 raise;
2825 END populate_incremental_rollup;
2826
2827 PROCEDURE populate_summ_entity(P_Balance_Type_Code IN VARCHAR2,
2828 p_process_method IN VARCHAR2)
2829 IS
2830
2831 BEGIN
2832
2833 PA_DEBUG.set_curr_function('populate_summ_entity');
2834
2835 /*
2836 * Assign P_Balance_Type_Code to package variable for future use.
2837 */
2838 l_balance_type_code := P_Balance_Type_Code;
2839
2840 /*
2841 * Assign p_process_method to package variable for future use.
2842 */
2843 l_delete_flag := p_process_method;
2844 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2845 PA_DEBUG.g_err_stage := 'Summarize the Data by Period ';
2846 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2847 END IF;
2848 /*
2849 * Summarize the data period wise from Global PL/SQL Table and
2850 * populate global temprary table with periodwise summarized data.
2851 */
2852 summarize_by_period;
2853
2854
2855
2856
2857 /*
2858 * Call the actual procedure to summarize data from global temporary
2859 * table.
2860 */
2861 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2862 PA_DEBUG.g_err_stage := 'Summarize the Data by Object Type ';
2863 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2864 END IF;
2865
2866 summarize_temp_data_by_res;
2867
2868 IF PA_REP_UTIL_GLOB.G_is_this_first_fetch = 'Y' THEN
2869 populate_tmp_for_capacity;
2870 PA_REP_UTIL_GLOB.G_is_this_first_fetch := 'N';
2871 END IF;
2872
2873 summarize_temp_data_by_org;
2874
2875 /*
2876 * If incremental rollup is enabled, populate the pa_rep_util_summ_tmp.
2877 */
2878 IF (l_org_rollup_method = 'I')
2879 THEN
2880 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2881 PA_DEBUG.g_err_stage := 'Processing Incremental Rollup';
2882 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2883 END IF;
2884 populate_incremental_rollup;
2885 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2886 PA_DEBUG.g_err_stage := 'After Processing Incremental Rollup';
2887 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2888 END IF;
2889
2890 END IF;
2891
2892 /*
2893 * Populate the object entity from pa_rep_util_summ_tmp
2894 * for record_type='TMP2'.
2895 */
2896 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2897 PA_DEBUG.g_err_stage := 'Before calling populate_object_entity';
2898 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2899 END IF;
2900
2901 populate_object_entity;
2902
2903 /*
2904 * Populate the balance entity from pa_rep_util_summ_tmp
2905 * for record_type='TMP2'.
2906 */
2907 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2908 PA_DEBUG.g_err_stage := 'Before calling populate_balance_entity';
2909 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2910 END IF;
2911
2912 populate_balance_entity;
2913
2914 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2915 PA_DEBUG.g_err_stage := 'After calling populate_balance_entity';
2916 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2917 END IF;
2918
2919 PA_DEBUG.Reset_curr_function;
2920
2921 EXCEPTION
2922 WHEN OTHERS
2923 THEN
2924 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2925 PA_DEBUG.log_message(PA_DEBUG.g_err_stage);
2926 PA_DEBUG.log_message(SQLERRM);
2927 END IF;
2928 raise;
2929
2930 END populate_summ_entity;
2931
2932 END PA_REP_UTILS_SUMM_PKG;