[Home] [Help]
PACKAGE BODY: APPS.PJI_RM_SUM_AVL
Source
1 PACKAGE BODY PJI_RM_SUM_AVL AS
2 /* $Header: PJISR04B.pls 120.7 2006/05/03 17:15:08 appldev noship $ */
3
4 --Defining Global PL/SQL Table variables for bulk insert in PJI_RM_AGGR_AVL3
5 g_exp_organization_id_in_tbl N_TYPE_TAB;
6 g_exp_org_id_in_tbl N_TYPE_TAB;
7 g_period_type_id_in_tbl N_TYPE_TAB;
8 g_time_id_in_tbl N_TYPE_TAB;
9 g_person_id_in_tbl N_TYPE_TAB;
10 g_calendar_type_in_tbl V_TYPE_TAB;
11 g_threshold_in_tbl N_TYPE_TAB;
12 g_as_of_date_in_tbl N_TYPE_TAB;
13 g_bckt_1_cs_in_tbl N_TYPE_TAB;
14 g_bckt_2_cs_in_tbl N_TYPE_TAB;
15 g_bckt_3_cs_in_tbl N_TYPE_TAB;
16 g_bckt_4_cs_in_tbl N_TYPE_TAB;
17 g_bckt_5_cs_in_tbl N_TYPE_TAB;
18 g_bckt_1_cm_in_tbl N_TYPE_TAB;
19 g_bckt_2_cm_in_tbl N_TYPE_TAB;
20 g_bckt_3_cm_in_tbl N_TYPE_TAB;
21 g_bckt_4_cm_in_tbl N_TYPE_TAB;
22 g_bckt_5_cm_in_tbl N_TYPE_TAB;
23 g_total_res_cnt_in_tbl N_TYPE_TAB;
24
25 --Defining Global PL/SQL Table variables for bulk insert in PJI_RM_AGGR_AVL4
26 gw_exp_organization_id_in_tbl N_TYPE_TAB;
27 gw_exp_org_id_in_tbl N_TYPE_TAB;
28 gw_period_type_id_in_tbl N_TYPE_TAB;
29 gw_time_id_in_tbl N_TYPE_TAB;
30 gw_person_id_in_tbl N_TYPE_TAB;
31 gw_calendar_type_in_tbl V_TYPE_TAB;
32 gw_threshold_in_tbl N_TYPE_TAB;
33 gw_as_of_date_in_tbl N_TYPE_TAB;
34 gw_availability_in_tbl N_TYPE_TAB;
35 gw_total_res_cnt_in_tbl N_TYPE_TAB;
36
37 --Defining Global variables for storing buckets
38
39 g_avl_res_cnt_1 PJI_RM_AGGR_AVL2.avl_res_count_bkt1%TYPE;
40 g_avl_res_cnt_2 PJI_RM_AGGR_AVL2.avl_res_count_bkt2%TYPE;
41 g_avl_res_cnt_3 PJI_RM_AGGR_AVL2.avl_res_count_bkt3%TYPE;
42 g_avl_res_cnt_4 PJI_RM_AGGR_AVL2.avl_res_count_bkt4%TYPE;
43 g_avl_res_cnt_5 PJI_RM_AGGR_AVL2.avl_res_count_bkt5%TYPE;
44
45 --Package level global Variables to store the resource
46 --availability buckets values
47 g_bucket_1_min NUMBER(15);
48 g_bucket_2_min NUMBER(15);
49 g_bucket_3_min NUMBER(15);
50 g_bucket_4_min NUMBER(15);
51 g_bucket_5_min NUMBER(15);
52 g_bucket_1_max NUMBER(15);
53 g_bucket_2_max NUMBER(15);
54 g_bucket_3_max NUMBER(15);
55 g_bucket_4_max NUMBER(15);
56 g_bucket_5_max NUMBER(15);
57
58 --Defining global variable for storing threshold value
59 g_no_of_user_def_threshold NUMBER(15);
60
61 --Defning global variable for storing minimum week day
62 g_min_wk_j_st_date NUMBER(15);
63
64 --Defning global variable for storing number of user
65 --defined rolling weeks
66 g_no_of_roll_week NUMBER(15);
67
68 g_curr_res_left_count NUMBER := 0;
69
70 PROCEDURE INIT_PCKG_GLOBAL_VARS
71 IS
72 l_min_bucket_tbl N_TYPE_TAB;
73 l_max_bucket_tbl N_TYPE_TAB;
74 BEGIN
75 --Get all the user defined bucket values
76 SELECT pmb.from_value,
77 pmb.to_value
78 BULK COLLECT INTO
79 l_min_bucket_tbl,
80 l_max_bucket_tbl
81 FROM pji_mt_buckets pmb
82 WHERE pmb.bucket_set_code = 'PJI_RES_AVL_DAYS'
83 ORDER BY pmb.seq;
84
85 g_bucket_1_min := l_min_bucket_tbl(1);
86 g_bucket_2_min := l_min_bucket_tbl(2);
87 g_bucket_3_min := l_min_bucket_tbl(3);
88 g_bucket_4_min := l_min_bucket_tbl(4);
89 g_bucket_5_min := l_min_bucket_tbl(5);
90
91 g_bucket_1_max := l_max_bucket_tbl(1);
92 g_bucket_2_max := l_max_bucket_tbl(2);
93 g_bucket_3_max := l_max_bucket_tbl(3);
94 g_bucket_4_max := l_max_bucket_tbl(4);
95 g_bucket_5_max := power(2,49);
96
97 --Get the number of thresholds being used in the system set up
98 SELECT count(*)
99 INTO g_no_of_user_def_threshold
100 FROM PJI_MT_BUCKETS
101 WHERE BUCKET_SET_CODE = 'PJI_RESOURCE_AVAILABILITY';
102
103 --Get the minimum of julian start day from fii_time_day
104 SELECT MIN(to_char(fiik.start_date,'j'))
105 INTO g_min_wk_j_st_date
106 FROM fii_time_week fiik;
107
108 --Get the number of rolling weeks being used in the system set up
109 SELECT rolling_weeks
110 INTO g_no_of_roll_week
111 FROM PJI_SYSTEM_SETTINGS;
112
113 END INIT_PCKG_GLOBAL_VARS;
114
115 PROCEDURE POP_ROLL_WEEK_OFFSET
116 IS
117 l_roll_week_offset_cnt NUMBER(15) := 0;
118 l_no_of_roll_week NUMBER := 0;
119 BEGIN
120 --Check if the table is already populated or not
121 SELECT count(*)
122 INTO l_roll_week_offset_cnt
123 FROM PJI_ROLL_WEEK_OFFSET;
124
125 -- If already populated then return
126 IF (l_roll_week_offset_cnt <> 0 )THEN
127 RETURN;
128 END IF;
129
130 --Otherwise populate the table
131
132 --Get the number of rolling weeks being used in the system set up
133 SELECT rolling_weeks
134 INTO l_no_of_roll_week
135 FROM PJI_SYSTEM_SETTINGS;
136
137 FOR i in 1.. l_no_of_roll_week-1
138 LOOP
139 INSERT INTO PJI_ROLL_WEEK_OFFSET
140 (
141 GLOBAL_SEQUENCE_ID,
142 OFFSET
143 )
144 VALUES
145 (
146 i,
147 -(l_no_of_roll_week - i)
148 );
149 END LOOP;
150 INSERT INTO PJI_ROLL_WEEK_OFFSET
151 (
152 GLOBAL_SEQUENCE_ID,
153 OFFSET
154 )
155 VALUES
156 (
157 l_no_of_roll_week,
158 0
159 );
160 FOR j in 1.. l_no_of_roll_week-1
161 LOOP
162 INSERT INTO PJI_ROLL_WEEK_OFFSET
163 (
164 GLOBAL_SEQUENCE_ID,
165 OFFSET
166 )
167 VALUES
168 (
169 l_no_of_roll_week + j,
170 j
171 );
172 END LOOP;
173
174 -- implicit commit
175 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
176 tabname => 'PJI_ROLL_WEEK_OFFSET',
177 percent => 100,
178 degree => 1);
179
180 COMMIT;
181
182 END POP_ROLL_WEEK_OFFSET;
183
184 PROCEDURE CALCULATE_BUCKET_VALUE
185 (
186 p_res_cnt IN NUMBER
187 ,x_bckt_1 OUT NOCOPY NUMBER
188 ,x_bckt_2 OUT NOCOPY NUMBER
189 ,x_bckt_3 OUT NOCOPY NUMBER
190 ,x_bckt_4 OUT NOCOPY NUMBER
191 ,x_bckt_5 OUT NOCOPY NUMBER
192 )
193 IS
194 BEGIN
195
196 --Processing for FIRST bucket
197 IF (p_res_cnt >= g_bucket_1_min
198 AND p_res_cnt <= g_bucket_1_max) THEN
199 -- This resource contributes to this bucket NOW
200 -- So change is 1
201 x_bckt_1 := 1;
202 ELSE
203 x_bckt_1 := 0;
204 END IF;
205
206 --Processing for SECOND bucket
207 IF (p_res_cnt >= g_bucket_2_min
208 AND p_res_cnt <= g_bucket_2_max) THEN
209 -- This resource contributes to this bucket NOW
210 -- So change is 1
211 x_bckt_2 := 1;
212 ELSE
213 x_bckt_2 := 0;
214 END IF;
215
216 --Processing for THIRD bucket
217 IF (p_res_cnt >= g_bucket_3_min
218 AND p_res_cnt <= g_bucket_3_max) THEN
219 -- This resource contributes to this bucket NOW
220 -- So change is 1
221 x_bckt_3 := 1;
222 ELSE
223 x_bckt_3 := 0;
224 END IF;
225
226 --Processing for FOURTH bucket
227 IF (p_res_cnt >= g_bucket_4_min
228 AND p_res_cnt <= g_bucket_4_max) THEN
229 -- This resource contributes to this bucket NOW
230 -- So change is 1
231 x_bckt_4 := 1;
232 ELSE
233 x_bckt_4 := 0;
234 END IF;
235
236 --Processing for FIFTH bucket
237 IF (p_res_cnt >= g_bucket_5_min
238 AND p_res_cnt <= g_bucket_5_max) THEN
239 -- This resource contributes to this bucket NOW
240 -- So change is 1
241 x_bckt_5 := 1;
242 ELSE
243 x_bckt_5 := 0;
244 END IF;
245
246 END CALCULATE_BUCKET_VALUE;
247
248 PROCEDURE CALC_CS_RES_CNT_VALUE
249 (
250 p_res_cnt_tbl IN OUT NOCOPY N_TYPE_TAB
251 )
252 IS
253 BEGIN
254 IF (g_avl_res_cnt_1 = 0) THEN
255 p_res_cnt_tbl(11) := GREATEST (p_res_cnt_tbl(6), NVL(p_res_cnt_tbl(11),0));
256
257 --Since availability is 0 on current day
258 --therefore,setting the consecutive count to 0
259 p_res_cnt_tbl(6) := 0;
260 END IF;
261
262 IF (g_avl_res_cnt_2 = 0) THEN
263 p_res_cnt_tbl(12) := GREATEST (p_res_cnt_tbl(7), NVL(p_res_cnt_tbl(12),0));
264
265 --Since availability is 0 on current day
266 --therefore,setting the consecutive count to 0
267 p_res_cnt_tbl(7) := 0;
268 END IF;
269
270 IF (g_avl_res_cnt_3 = 0) THEN
271 p_res_cnt_tbl(13) := GREATEST (p_res_cnt_tbl(8), NVL(p_res_cnt_tbl(13),0));
272
273 --Since availability is 0 on current day
274 --therefore,setting the consecutive count to 0
275 p_res_cnt_tbl(8) := 0;
276 END IF;
277
278 IF (g_avl_res_cnt_4 = 0) THEN
279 p_res_cnt_tbl(14) := GREATEST (p_res_cnt_tbl(9), NVL(p_res_cnt_tbl(14),0));
280
281 --Since availability is 0 on current day
282 --therefore,setting the consecutive count to 0
283 p_res_cnt_tbl(9) := 0;
284 END IF;
285
286 IF (g_avl_res_cnt_5 = 0) THEN
287 p_res_cnt_tbl(15) := GREATEST (p_res_cnt_tbl(10), NVL(p_res_cnt_tbl(15),0));
288
289 --Since availability is 0 on current day
290 --therefore,setting the consecutive count to 0
291 p_res_cnt_tbl(10) := 0;
292 END IF;
293
294 END CALC_CS_RES_CNT_VALUE;
295
296 PROCEDURE DEL_GLOBAL_RS_AVL3_TBL
297 IS
298 BEGIN
299 g_exp_organization_id_in_tbl.DELETE;
300 g_exp_org_id_in_tbl.DELETE;
301 g_period_type_id_in_tbl.DELETE;
302 g_time_id_in_tbl.DELETE;
303 g_person_id_in_tbl.DELETE;
304 g_calendar_type_in_tbl.DELETE;
305 g_threshold_in_tbl.DELETE;
306 g_as_of_date_in_tbl.DELETE;
307 g_bckt_1_cs_in_tbl.DELETE;
308 g_bckt_2_cs_in_tbl.DELETE;
309 g_bckt_3_cs_in_tbl.DELETE;
310 g_bckt_4_cs_in_tbl.DELETE;
311 g_bckt_5_cs_in_tbl.DELETE;
312 g_bckt_1_cm_in_tbl.DELETE;
313 g_bckt_2_cm_in_tbl.DELETE;
314 g_bckt_3_cm_in_tbl.DELETE;
315 g_bckt_4_cm_in_tbl.DELETE;
316 g_bckt_5_cm_in_tbl.DELETE;
317 g_total_res_cnt_in_tbl.DELETE;
318
319 END DEL_GLOBAL_RS_AVL3_TBL;
320
321 PROCEDURE BULK_INSERT_RS_AVL3
322 (
323 p_exp_organization_id_in_tbl IN N_TYPE_TAB,
324 p_exp_org_id_in_tbl IN N_TYPE_TAB,
325 p_period_type_id_in_tbl IN N_TYPE_TAB,
326 p_time_id_in_tbl IN N_TYPE_TAB,
327 p_person_id_in_tbl IN N_TYPE_TAB,
328 p_calendar_type_in_tbl IN V_TYPE_TAB,
329 p_threshold_in_tbl IN N_TYPE_TAB,
330 p_as_of_date_in_tbl IN N_TYPE_TAB,
331 p_bckt_1_cs_in_tbl IN N_TYPE_TAB,
332 p_bckt_2_cs_in_tbl IN N_TYPE_TAB,
333 p_bckt_3_cs_in_tbl IN N_TYPE_TAB,
334 p_bckt_4_cs_in_tbl IN N_TYPE_TAB,
335 p_bckt_5_cs_in_tbl IN N_TYPE_TAB,
336 p_bckt_1_cm_in_tbl IN N_TYPE_TAB,
337 p_bckt_2_cm_in_tbl IN N_TYPE_TAB,
338 p_bckt_3_cm_in_tbl IN N_TYPE_TAB,
339 p_bckt_4_cm_in_tbl IN N_TYPE_TAB,
340 p_bckt_5_cm_in_tbl IN N_TYPE_TAB,
341 p_total_res_cnt_in_tbl IN N_TYPE_TAB,
342 p_run_mode IN VARCHAR2,
343 p_blind_insert_flag IN VARCHAR2
344 )
345 IS
346
347 --Defining local variables
348 l_curr_count NUMBER := 0;
349 l_max_count NUMBER := 200;
350 BEGIN
351 IF (p_blind_insert_flag = 'Y') THEN
352 IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
353 FORALL k IN 1.. g_exp_organization_id_in_tbl.count
354 INSERT INTO PJI_RM_AGGR_AVL3
355 (
356 EXPENDITURE_ORGANIZATION_ID,
357 EXPENDITURE_ORG_ID,
358 PERIOD_TYPE_ID,
359 TIME_ID ,
360 PERSON_ID,
361 CALENDAR_TYPE,
362 THRESHOLD,
363 AS_OF_DATE,
364 BCKT_1_CS,
365 BCKT_2_CS,
366 BCKT_3_CS,
367 BCKT_4_CS,
368 BCKT_5_CS,
369 BCKT_1_CM,
370 BCKT_2_CM,
371 BCKT_3_CM,
372 BCKT_4_CM,
373 BCKT_5_CM,
374 TOTAL_RES_COUNT
375 )
376 VALUES
377 (
378 g_exp_organization_id_in_tbl(k),
379 g_exp_org_id_in_tbl(k),
380 g_period_type_id_in_tbl(k),
381 g_time_id_in_tbl(k),
382 g_person_id_in_tbl(k),
383 g_calendar_type_in_tbl(k),
384 g_threshold_in_tbl(k),
385 g_as_of_date_in_tbl(k),
386 -g_bckt_1_cs_in_tbl(k),
387 -g_bckt_2_cs_in_tbl(k),
388 -g_bckt_3_cs_in_tbl(k),
389 -g_bckt_4_cs_in_tbl(k),
390 -g_bckt_5_cs_in_tbl(k),
391 -g_bckt_1_cm_in_tbl(k),
392 -g_bckt_2_cm_in_tbl(k),
393 -g_bckt_3_cm_in_tbl(k),
394 -g_bckt_4_cm_in_tbl(k),
395 -g_bckt_5_cm_in_tbl(k),
396 -g_total_res_cnt_in_tbl(k)
397 );
398 ELSE
399 FORALL k IN 1.. g_exp_organization_id_in_tbl.count
400 INSERT INTO PJI_RM_AGGR_AVL3
401 (
402 EXPENDITURE_ORGANIZATION_ID,
403 EXPENDITURE_ORG_ID,
404 PERIOD_TYPE_ID,
405 TIME_ID ,
406 PERSON_ID,
407 CALENDAR_TYPE,
408 THRESHOLD,
409 AS_OF_DATE,
410 BCKT_1_CS,
411 BCKT_2_CS,
412 BCKT_3_CS,
413 BCKT_4_CS,
414 BCKT_5_CS,
415 BCKT_1_CM,
416 BCKT_2_CM,
417 BCKT_3_CM,
418 BCKT_4_CM,
419 BCKT_5_CM,
420 TOTAL_RES_COUNT
421 )
422 VALUES
423 (
424 g_exp_organization_id_in_tbl(k),
425 g_exp_org_id_in_tbl(k),
426 g_period_type_id_in_tbl(k),
427 g_time_id_in_tbl(k),
428 g_person_id_in_tbl(k),
429 g_calendar_type_in_tbl(k),
430 g_threshold_in_tbl(k),
431 g_as_of_date_in_tbl(k),
432 g_bckt_1_cs_in_tbl(k),
433 g_bckt_2_cs_in_tbl(k),
434 g_bckt_3_cs_in_tbl(k),
435 g_bckt_4_cs_in_tbl(k),
436 g_bckt_5_cs_in_tbl(k),
437 g_bckt_1_cm_in_tbl(k),
438 g_bckt_2_cm_in_tbl(k),
439 g_bckt_3_cm_in_tbl(k),
440 g_bckt_4_cm_in_tbl(k),
441 g_bckt_5_cm_in_tbl(k),
442 g_total_res_cnt_in_tbl(k)
443 );
444 END IF;
445
446 DEL_GLOBAL_RS_AVL3_TBL;
447
448 RETURN;
449
450 END IF;
451
452 FOR i in p_exp_organization_id_in_tbl.FIRST.. p_exp_organization_id_in_tbl.LAST
453 LOOP
454 --Assigning passed PL/SQL table values to local PL/SQL Tables
455 --Before that get the last count of the local table
456 l_curr_count := g_exp_organization_id_in_tbl.COUNT;
457
458 g_exp_organization_id_in_tbl(l_curr_count + 1) := p_exp_organization_id_in_tbl(i);
459 g_exp_org_id_in_tbl(l_curr_count + 1) := p_exp_org_id_in_tbl(i);
460 g_period_type_id_in_tbl(l_curr_count + 1) := p_period_type_id_in_tbl(i);
461 g_time_id_in_tbl(l_curr_count + 1) := p_time_id_in_tbl(i);
462 g_person_id_in_tbl(l_curr_count + 1) := p_person_id_in_tbl(i);
463 g_calendar_type_in_tbl(l_curr_count + 1) := p_calendar_type_in_tbl(i);
464 g_threshold_in_tbl(l_curr_count + 1) := p_threshold_in_tbl(i);
465 g_as_of_date_in_tbl(l_curr_count + 1) := p_as_of_date_in_tbl(i);
466 g_bckt_1_cs_in_tbl(l_curr_count + 1) := p_bckt_1_cs_in_tbl(i);
467 g_bckt_2_cs_in_tbl(l_curr_count + 1) := p_bckt_2_cs_in_tbl(i);
468 g_bckt_3_cs_in_tbl(l_curr_count + 1) := p_bckt_3_cs_in_tbl(i);
469 g_bckt_4_cs_in_tbl(l_curr_count + 1) := p_bckt_4_cs_in_tbl(i);
470 g_bckt_5_cs_in_tbl(l_curr_count + 1) := p_bckt_5_cs_in_tbl(i);
471 g_bckt_1_cm_in_tbl(l_curr_count + 1) := p_bckt_1_cm_in_tbl(i);
472 g_bckt_2_cm_in_tbl(l_curr_count + 1) := p_bckt_2_cm_in_tbl(i);
473 g_bckt_3_cm_in_tbl(l_curr_count + 1) := p_bckt_3_cm_in_tbl(i);
474 g_bckt_4_cm_in_tbl(l_curr_count + 1) := p_bckt_4_cm_in_tbl(i);
475 g_bckt_5_cm_in_tbl(l_curr_count + 1) := p_bckt_5_cm_in_tbl(i);
476 g_total_res_cnt_in_tbl(l_curr_count + 1) := p_total_res_cnt_in_tbl(i);
477 END LOOP;
478
479 IF (g_exp_organization_id_in_tbl.COUNT >= l_max_count) THEN
480 IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
481 FORALL k IN 1.. g_exp_organization_id_in_tbl.count
482 INSERT INTO PJI_RM_AGGR_AVL3
483 (
484 EXPENDITURE_ORGANIZATION_ID,
485 EXPENDITURE_ORG_ID,
486 PERIOD_TYPE_ID,
487 TIME_ID ,
488 PERSON_ID,
489 CALENDAR_TYPE,
490 THRESHOLD,
491 AS_OF_DATE,
492 BCKT_1_CS,
493 BCKT_2_CS,
494 BCKT_3_CS,
495 BCKT_4_CS,
496 BCKT_5_CS,
497 BCKT_1_CM,
498 BCKT_2_CM,
499 BCKT_3_CM,
500 BCKT_4_CM,
501 BCKT_5_CM,
502 TOTAL_RES_COUNT
503 )
504 VALUES
505 (
506 g_exp_organization_id_in_tbl(k),
507 g_exp_org_id_in_tbl(k),
508 g_period_type_id_in_tbl(k),
509 g_time_id_in_tbl(k),
510 g_person_id_in_tbl(k),
511 g_calendar_type_in_tbl(k),
512 g_threshold_in_tbl(k),
513 g_as_of_date_in_tbl(k),
514 -g_bckt_1_cs_in_tbl(k),
515 -g_bckt_2_cs_in_tbl(k),
516 -g_bckt_3_cs_in_tbl(k),
517 -g_bckt_4_cs_in_tbl(k),
518 -g_bckt_5_cs_in_tbl(k),
519 -g_bckt_1_cm_in_tbl(k),
520 -g_bckt_2_cm_in_tbl(k),
521 -g_bckt_3_cm_in_tbl(k),
522 -g_bckt_4_cm_in_tbl(k),
523 -g_bckt_5_cm_in_tbl(k),
524 -g_total_res_cnt_in_tbl(k)
525 );
526 ELSE
527 FORALL k IN 1.. g_exp_organization_id_in_tbl.count
528 INSERT INTO PJI_RM_AGGR_AVL3
529 (
530 EXPENDITURE_ORGANIZATION_ID,
531 EXPENDITURE_ORG_ID,
532 PERIOD_TYPE_ID,
533 TIME_ID ,
534 PERSON_ID,
535 CALENDAR_TYPE,
536 THRESHOLD,
537 AS_OF_DATE,
538 BCKT_1_CS,
539 BCKT_2_CS,
540 BCKT_3_CS,
541 BCKT_4_CS,
542 BCKT_5_CS,
543 BCKT_1_CM,
544 BCKT_2_CM,
545 BCKT_3_CM,
546 BCKT_4_CM,
547 BCKT_5_CM,
548 TOTAL_RES_COUNT
549 )
550 VALUES
551 (
552 g_exp_organization_id_in_tbl(k),
553 g_exp_org_id_in_tbl(k),
554 g_period_type_id_in_tbl(k),
555 g_time_id_in_tbl(k),
556 g_person_id_in_tbl(k),
557 g_calendar_type_in_tbl(k),
558 g_threshold_in_tbl(k),
559 g_as_of_date_in_tbl(k),
560 g_bckt_1_cs_in_tbl(k),
561 g_bckt_2_cs_in_tbl(k),
562 g_bckt_3_cs_in_tbl(k),
563 g_bckt_4_cs_in_tbl(k),
564 g_bckt_5_cs_in_tbl(k),
565 g_bckt_1_cm_in_tbl(k),
566 g_bckt_2_cm_in_tbl(k),
567 g_bckt_3_cm_in_tbl(k),
568 g_bckt_4_cm_in_tbl(k),
569 g_bckt_5_cm_in_tbl(k),
570 g_total_res_cnt_in_tbl(k)
571 );
572 END IF;
573
574 DEL_GLOBAL_RS_AVL3_TBL;
575 END IF;
576
577 END BULK_INSERT_RS_AVL3;
578
579 PROCEDURE PREPARE_TO_INS_INTO_AVL3
580 (
581 p_exp_organization_id IN PJI_RM_AGGR_AVL2.expenditure_organization_id%TYPE,
582 p_exp_org_id IN PJI_RM_AGGR_AVL2.expenditure_org_id%TYPE,
583 p_person_id IN PJI_RM_AGGR_AVL2.person_id%TYPE,
584 p_time_id IN PJI_RM_AGGR_AVL2.time_id%TYPE,
585 p_curr_pd IN NUMBER,
586 p_as_of_date IN NUMBER,
587 p_pd_org_st_date IN NUMBER,
588 p_period_type_id IN NUMBER,
589 p_calendar_type IN VARCHAR2,
590 p_res_cnt_tbl IN N_TYPE_TAB,
591 p_run_mode IN VARCHAR2,
592 p_blind_insert_flag IN VARCHAR2,
593 x_zero_bkt_cnt_flag OUT NOCOPY VARCHAR2
594 )
595 IS
596
597 --Defining PL/SQL Table variables for bulk insert in PJI_RM_AGGR_AVL3
598 l_worker_id_in_tbl N_TYPE_TAB;
599 l_exp_organization_id_in_tbl N_TYPE_TAB;
600 l_exp_org_id_in_tbl N_TYPE_TAB;
601 l_period_type_id_in_tbl N_TYPE_TAB;
602 l_time_id_in_tbl N_TYPE_TAB;
603 l_person_id_in_tbl N_TYPE_TAB;
604 l_calendar_type_in_tbl V_TYPE_TAB;
605 l_threshold_in_tbl N_TYPE_TAB;
606 l_as_of_date_in_tbl N_TYPE_TAB;
607 l_pd_org_st_date_in_tbl N_TYPE_TAB;
608 l_bckt_1_cs_in_tbl N_TYPE_TAB;
609 l_bckt_2_cs_in_tbl N_TYPE_TAB;
610 l_bckt_3_cs_in_tbl N_TYPE_TAB;
611 l_bckt_4_cs_in_tbl N_TYPE_TAB;
612 l_bckt_5_cs_in_tbl N_TYPE_TAB;
613 l_bckt_1_cm_in_tbl N_TYPE_TAB;
614 l_bckt_2_cm_in_tbl N_TYPE_TAB;
615 l_bckt_3_cm_in_tbl N_TYPE_TAB;
616 l_bckt_4_cm_in_tbl N_TYPE_TAB;
617 l_bckt_5_cm_in_tbl N_TYPE_TAB;
618 l_total_res_cnt_in_tbl N_TYPE_TAB;
619 --Variables for holding resource count values
620 l_res_cnt_cs NUMBER := 0;
621 l_res_cnt_cm NUMBER := 0;
622 BEGIN
623 x_zero_bkt_cnt_flag := 'Y';
624 IF (p_blind_insert_flag = 'Y') THEN
625 BULK_INSERT_RS_AVL3
626 (
627 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
628 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
629 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
630 p_time_id_in_tbl => l_time_id_in_tbl,
631 p_person_id_in_tbl => l_person_id_in_tbl,
632 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
633 p_threshold_in_tbl => l_threshold_in_tbl,
634 p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
635 p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
636 p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
637 p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
638 p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
639 p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
640 p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
641 p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
642 p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
643 p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
644 p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
645 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
646 p_run_mode => p_run_mode,
647 p_blind_insert_flag => p_blind_insert_flag
648 );
649 ELSE
650 --Push this record in a PL/SQL table and will
651 --BULK INSERT at the end of processing
652 FOR j in 1.. g_no_of_user_def_threshold
653 LOOP
654 --DBMS_OUTPUT.PUT_LINE('3');
655 l_exp_organization_id_in_tbl(j) :=p_exp_organization_id;
656 l_exp_org_id_in_tbl(j) :=p_exp_org_id;
657 l_period_type_id_in_tbl(j) :=p_period_type_id;
658 l_time_id_in_tbl(j) :=p_time_id;
659 l_person_id_in_tbl(j) :=p_person_id;
660 l_calendar_type_in_tbl(j) :=p_calendar_type;
661 l_threshold_in_tbl(j) :=j;
662 l_as_of_date_in_tbl(j) := p_as_of_date;
663 l_pd_org_st_date_in_tbl(j) := p_pd_org_st_date;
664 l_res_cnt_cs := p_res_cnt_tbl(j+10);
665 l_res_cnt_cm := p_res_cnt_tbl(j);
666 l_total_res_cnt_in_tbl(j) := 1;
667
668 --Populate PL/SQL Table values for consecutive counts
669 CALCULATE_BUCKET_VALUE
670 (
671 p_res_cnt => l_res_cnt_cs
672 ,x_bckt_1 => l_bckt_1_cs_in_tbl(j)
673 ,x_bckt_2 => l_bckt_2_cs_in_tbl(j)
674 ,x_bckt_3 => l_bckt_3_cs_in_tbl(j)
675 ,x_bckt_4 => l_bckt_4_cs_in_tbl(j)
676 ,x_bckt_5 => l_bckt_5_cs_in_tbl(j)
677 );
678
679 --Populate PL/SQL Table values for consecutive counts
680 CALCULATE_BUCKET_VALUE
681 (
682 p_res_cnt => l_res_cnt_cm
683 ,x_bckt_1 => l_bckt_1_cm_in_tbl(j)
684 ,x_bckt_2 => l_bckt_2_cm_in_tbl(j)
685 ,x_bckt_3 => l_bckt_3_cm_in_tbl(j)
686 ,x_bckt_4 => l_bckt_4_cm_in_tbl(j)
687 ,x_bckt_5 => l_bckt_5_cm_in_tbl(j)
688 );
689
690 END LOOP;
691
692 FOR j in 1.. g_no_of_user_def_threshold
693 LOOP
694 IF ( l_bckt_1_cm_in_tbl(j) <> 0
695 OR l_bckt_2_cm_in_tbl(j) <> 0
696 OR l_bckt_3_cm_in_tbl(j) <> 0
697 OR l_bckt_4_cm_in_tbl(j) <> 0
698 OR l_bckt_5_cm_in_tbl(j) <> 0
699 OR l_bckt_1_cm_in_tbl(j) <> 0
700 OR l_bckt_2_cm_in_tbl(j) <> 0
701 OR l_bckt_3_cm_in_tbl(j) <> 0
702 OR l_bckt_4_cm_in_tbl(j) <> 0
703 OR l_bckt_5_cm_in_tbl(j) <> 0
704 ) THEN
705 x_zero_bkt_cnt_flag := 'N';
706 EXIT;
707 END IF;
708 END LOOP;
709
710 IF (x_zero_bkt_cnt_flag = 'N') THEN
711 --Call the bulk insert to insert rows for this particular
712 --period and person id for all thresholds
713
714 BULK_INSERT_RS_AVL3
715 (
716 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
717 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
718 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
719 p_time_id_in_tbl => l_time_id_in_tbl,
720 p_person_id_in_tbl => l_person_id_in_tbl,
721 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
722 p_threshold_in_tbl => l_threshold_in_tbl,
723 p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
724 p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
725 p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
726 p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
727 p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
728 p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
729 p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
730 p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
731 p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
732 p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
733 p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
734 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
735 p_run_mode => p_run_mode,
736 p_blind_insert_flag => p_blind_insert_flag
737 );
738
739 --If the period has not changed then only org has changed
740 --so post the reversal on as of date
741 IF (p_time_id = p_curr_pd) THEN
742 --Negate the bucket values for current date
743 FOR j in 1.. g_no_of_user_def_threshold
744 LOOP
745 l_bckt_1_cs_in_tbl(j) := -l_bckt_1_cs_in_tbl(j);
746 l_bckt_2_cs_in_tbl(j) := -l_bckt_2_cs_in_tbl(j);
747 l_bckt_3_cs_in_tbl(j) := -l_bckt_3_cs_in_tbl(j);
748 l_bckt_4_cs_in_tbl(j) := -l_bckt_4_cs_in_tbl(j);
749 l_bckt_5_cs_in_tbl(j) := -l_bckt_5_cs_in_tbl(j);
750 l_bckt_1_cm_in_tbl(j) := -l_bckt_1_cm_in_tbl(j);
751 l_bckt_2_cm_in_tbl(j) := -l_bckt_2_cm_in_tbl(j);
752 l_bckt_3_cm_in_tbl(j) := -l_bckt_3_cm_in_tbl(j);
753 l_bckt_4_cm_in_tbl(j) := -l_bckt_4_cm_in_tbl(j);
754 l_bckt_5_cm_in_tbl(j) := -l_bckt_5_cm_in_tbl(j);
755 l_total_res_cnt_in_tbl(j) := -l_total_res_cnt_in_tbl(j);
756 END LOOP;
757
758 BULK_INSERT_RS_AVL3
759 (
760 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
761 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
762 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
763 p_time_id_in_tbl => l_time_id_in_tbl,
764 p_person_id_in_tbl => l_person_id_in_tbl,
765 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
766 p_threshold_in_tbl => l_threshold_in_tbl,
767 p_as_of_date_in_tbl => l_as_of_date_in_tbl,
768 p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
769 p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
770 p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
771 p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
772 p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
773 p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
774 p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
775 p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
776 p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
777 p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
778 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
779 p_run_mode => p_run_mode,
780 p_blind_insert_flag => p_blind_insert_flag
781 );
782 END IF;
783 ELSIF (x_zero_bkt_cnt_flag = 'Y') THEN
784 --If the PERIOD HAS CHANGED AND the counts are
785 --zero, we still need to POST the values
786 --for total resources.
787 IF (p_time_id <> p_curr_pd) THEN
788 --the bucket values are zero
789
790 BULK_INSERT_RS_AVL3
791 (
792 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
793 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
794 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
795 p_time_id_in_tbl => l_time_id_in_tbl,
796 p_person_id_in_tbl => l_person_id_in_tbl,
797 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
798 p_threshold_in_tbl => l_threshold_in_tbl,
799 p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
800 p_bckt_1_cs_in_tbl => l_bckt_1_cs_in_tbl,
801 p_bckt_2_cs_in_tbl => l_bckt_2_cs_in_tbl,
802 p_bckt_3_cs_in_tbl => l_bckt_3_cs_in_tbl,
803 p_bckt_4_cs_in_tbl => l_bckt_4_cs_in_tbl,
804 p_bckt_5_cs_in_tbl => l_bckt_5_cs_in_tbl,
805 p_bckt_1_cm_in_tbl => l_bckt_1_cm_in_tbl,
806 p_bckt_2_cm_in_tbl => l_bckt_2_cm_in_tbl,
807 p_bckt_3_cm_in_tbl => l_bckt_3_cm_in_tbl,
808 p_bckt_4_cm_in_tbl => l_bckt_4_cm_in_tbl,
809 p_bckt_5_cm_in_tbl => l_bckt_5_cm_in_tbl,
810 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
811 p_run_mode => p_run_mode,
812 p_blind_insert_flag => p_blind_insert_flag
813 );
814 END IF;
815
816 END IF;
817 END IF;
818 END PREPARE_TO_INS_INTO_AVL3;
819
820 PROCEDURE DEL_GLOBAL_RS_AVL4_TBL
821 IS
822 BEGIN
823 gw_exp_organization_id_in_tbl.DELETE;
824 gw_exp_org_id_in_tbl.DELETE;
825 gw_period_type_id_in_tbl.DELETE;
826 gw_time_id_in_tbl.DELETE;
827 gw_person_id_in_tbl.DELETE;
828 gw_calendar_type_in_tbl.DELETE;
829 gw_threshold_in_tbl.DELETE;
830 gw_availability_in_tbl.DELETE;
831 gw_total_res_cnt_in_tbl.DELETE;
832
833 END DEL_GLOBAL_RS_AVL4_TBL;
834
835 PROCEDURE BULK_INSERT_RS_AVL4
836 (
837 p_exp_organization_id_in_tbl IN N_TYPE_TAB,
838 p_exp_org_id_in_tbl IN N_TYPE_TAB,
839 p_period_type_id_in_tbl IN N_TYPE_TAB,
840 p_time_id_in_tbl IN N_TYPE_TAB,
841 p_person_id_in_tbl IN N_TYPE_TAB,
842 p_calendar_type_in_tbl IN V_TYPE_TAB,
843 p_threshold_in_tbl IN N_TYPE_TAB,
844 p_as_of_date_in_tbl IN N_TYPE_TAB,
845 p_availability_in_tbl IN N_TYPE_TAB,
846 p_total_res_cnt_in_tbl IN N_TYPE_TAB,
847 p_run_mode IN VARCHAR2,
848 p_blind_insert_flag IN VARCHAR2
849 )
850 IS
851
852 --Defining local variables
853 l_curr_count NUMBER := 0;
854 l_max_count NUMBER := 200;
855 BEGIN
856 IF (p_blind_insert_flag = 'Y') THEN
857 IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
858 FORALL k IN 1.. gw_exp_organization_id_in_tbl.count
859 INSERT INTO PJI_RM_AGGR_AVL4
860 (
861 EXPENDITURE_ORGANIZATION_ID,
862 EXPENDITURE_ORG_ID,
863 PERIOD_TYPE_ID,
864 TIME_ID ,
865 PERSON_ID,
866 CALENDAR_TYPE,
867 THRESHOLD,
868 AS_OF_DATE,
869 AVAILABILITY,
870 TOTAL_RES_COUNT
871 )
872 VALUES
873 (
874 gw_exp_organization_id_in_tbl(k),
875 gw_exp_org_id_in_tbl(k),
876 gw_period_type_id_in_tbl(k),
877 gw_time_id_in_tbl(k),
878 gw_person_id_in_tbl(k),
879 gw_calendar_type_in_tbl(k),
880 gw_threshold_in_tbl(k),
881 gw_as_of_date_in_tbl(k),
882 -gw_availability_in_tbl(k),
883 -gw_total_res_cnt_in_tbl(k)
884 );
885 ELSE
886 FORALL k IN 1.. gw_exp_organization_id_in_tbl.count
887 INSERT INTO PJI_RM_AGGR_AVL4
888 (
889 EXPENDITURE_ORGANIZATION_ID,
890 EXPENDITURE_ORG_ID,
891 PERIOD_TYPE_ID,
892 TIME_ID ,
893 PERSON_ID,
894 CALENDAR_TYPE,
895 THRESHOLD,
896 AS_OF_DATE,
897 AVAILABILITY,
898 TOTAL_RES_COUNT
899 )
900 VALUES
901 (
902 gw_exp_organization_id_in_tbl(k),
903 gw_exp_org_id_in_tbl(k),
904 gw_period_type_id_in_tbl(k),
905 gw_time_id_in_tbl(k),
906 gw_person_id_in_tbl(k),
907 gw_calendar_type_in_tbl(k),
908 gw_threshold_in_tbl(k),
909 gw_as_of_date_in_tbl(k),
910 gw_availability_in_tbl(k),
911 gw_total_res_cnt_in_tbl(k)
912 );
913 END IF;
914
915 DEL_GLOBAL_RS_AVL4_TBL;
916
917 RETURN;
918
919 END IF;
920
921 FOR i in p_exp_organization_id_in_tbl.FIRST.. p_exp_organization_id_in_tbl.LAST
922 LOOP
923 --Assigning passed PL/SQL table values to local PL/SQL Tables
924 --Before that get the last count of the local table
925 l_curr_count := gw_exp_organization_id_in_tbl.COUNT;
926
927 gw_exp_organization_id_in_tbl(l_curr_count + 1) := p_exp_organization_id_in_tbl(i);
928 gw_exp_org_id_in_tbl(l_curr_count + 1) := p_exp_org_id_in_tbl(i);
929 gw_period_type_id_in_tbl(l_curr_count + 1) := p_period_type_id_in_tbl(i);
930 gw_time_id_in_tbl(l_curr_count + 1) := p_time_id_in_tbl(i);
931 gw_person_id_in_tbl(l_curr_count + 1) := p_person_id_in_tbl(i);
932 gw_calendar_type_in_tbl(l_curr_count + 1) := p_calendar_type_in_tbl(i);
933 gw_threshold_in_tbl(l_curr_count + 1) := p_threshold_in_tbl(i);
934 gw_as_of_date_in_tbl(l_curr_count + 1) := p_as_of_date_in_tbl(i);
935 gw_availability_in_tbl(l_curr_count + 1) := p_availability_in_tbl(i);
936 gw_total_res_cnt_in_tbl(l_curr_count + 1) := p_total_res_cnt_in_tbl(i);
937 END LOOP;
938
939 IF (gw_exp_organization_id_in_tbl.COUNT >= l_max_count) THEN
940 IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
941 FORALL k IN 1.. gw_exp_organization_id_in_tbl.count
942 INSERT INTO PJI_RM_AGGR_AVL4
943 (
944 EXPENDITURE_ORGANIZATION_ID,
945 EXPENDITURE_ORG_ID,
946 PERIOD_TYPE_ID,
947 TIME_ID ,
948 PERSON_ID,
949 CALENDAR_TYPE,
950 THRESHOLD,
951 AS_OF_DATE,
952 AVAILABILITY,
953 TOTAL_RES_COUNT
954 )
955 VALUES
956 (
957 gw_exp_organization_id_in_tbl(k),
958 gw_exp_org_id_in_tbl(k),
959 gw_period_type_id_in_tbl(k),
960 gw_time_id_in_tbl(k),
961 gw_person_id_in_tbl(k),
962 gw_calendar_type_in_tbl(k),
963 gw_threshold_in_tbl(k),
964 gw_as_of_date_in_tbl(k),
965 -gw_availability_in_tbl(k),
966 -gw_total_res_cnt_in_tbl(k)
967 );
968 ELSE
969 FORALL k IN 1.. gw_exp_organization_id_in_tbl.count
970 INSERT INTO PJI_RM_AGGR_AVL4
971 (
972 EXPENDITURE_ORGANIZATION_ID,
973 EXPENDITURE_ORG_ID,
974 PERIOD_TYPE_ID,
975 TIME_ID ,
976 PERSON_ID,
977 CALENDAR_TYPE,
978 THRESHOLD,
979 AS_OF_DATE,
980 AVAILABILITY,
981 TOTAL_RES_COUNT
982 )
983 VALUES
984 (
985 gw_exp_organization_id_in_tbl(k),
986 gw_exp_org_id_in_tbl(k),
987 gw_period_type_id_in_tbl(k),
988 gw_time_id_in_tbl(k),
989 gw_person_id_in_tbl(k),
990 gw_calendar_type_in_tbl(k),
991 gw_threshold_in_tbl(k),
992 gw_as_of_date_in_tbl(k),
993 gw_availability_in_tbl(k),
994 gw_total_res_cnt_in_tbl(k)
995 );
996 END IF;
997 DEL_GLOBAL_RS_AVL4_TBL;
998 END IF;
999
1000 END BULK_INSERT_RS_AVL4;
1001
1002 PROCEDURE PREPARE_TO_INS_INTO_AVL4
1003 (
1004 p_exp_organization_id IN PJI_RM_AGGR_AVL2.expenditure_organization_id%TYPE,
1005 p_exp_org_id IN PJI_RM_AGGR_AVL2.expenditure_org_id%TYPE,
1006 p_person_id IN PJI_RM_AGGR_AVL2.person_id%TYPE,
1007 p_time_id IN PJI_RM_AGGR_AVL2.time_id%TYPE,
1008 p_curr_pd IN NUMBER,
1009 p_as_of_date IN NUMBER,
1010 p_pd_org_st_date IN NUMBER,
1011 p_period_type_id IN NUMBER,
1012 p_calendar_type IN VARCHAR2,
1013 p_res_cnt_tbl IN N_TYPE_TAB,
1014 p_run_mode IN VARCHAR2,
1015 p_blind_insert_flag IN VARCHAR2,
1016 x_zero_bkt_cnt_flag OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019
1020 --Defining PL/SQL Table variables for bulk insert in PJI_RM_AGGR_AVL4
1021 l_exp_organization_id_in_tbl N_TYPE_TAB;
1022 l_exp_org_id_in_tbl N_TYPE_TAB;
1023 l_period_type_id_in_tbl N_TYPE_TAB;
1024 l_time_id_in_tbl N_TYPE_TAB;
1025 l_person_id_in_tbl N_TYPE_TAB;
1026 l_calendar_type_in_tbl V_TYPE_TAB;
1027 l_threshold_in_tbl N_TYPE_TAB;
1028 l_as_of_date_in_tbl N_TYPE_TAB;
1029 l_pd_org_st_date_in_tbl N_TYPE_TAB;
1030 l_availability_in_tbl N_TYPE_TAB;
1031 l_total_res_cnt_in_tbl N_TYPE_TAB;
1032
1033 --Variables for holding resource count values
1034 l_res_cnt NUMBER := 0;
1035 BEGIN
1036 x_zero_bkt_cnt_flag := 'Y';
1037 IF (p_blind_insert_flag = 'Y') THEN
1038 BULK_INSERT_RS_AVL4
1039 (
1040 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
1041 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
1042 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
1043 p_time_id_in_tbl => l_time_id_in_tbl,
1044 p_person_id_in_tbl => l_person_id_in_tbl,
1045 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
1046 p_threshold_in_tbl => l_threshold_in_tbl,
1047 p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
1048 p_availability_in_tbl => l_availability_in_tbl,
1049 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
1050 p_run_mode => p_run_mode,
1051 p_blind_insert_flag => p_blind_insert_flag
1052 );
1053 ELSE
1054 --Push this record in a PL/SQL table and will
1055 --BULK INSERT at the end of processing
1056 FOR j in 1.. g_no_of_user_def_threshold
1057 LOOP
1058 l_exp_organization_id_in_tbl(j) :=p_exp_organization_id;
1059 l_exp_org_id_in_tbl(j) :=p_exp_org_id;
1060 l_period_type_id_in_tbl(j) :=p_period_type_id;
1061 l_time_id_in_tbl(j) :=p_time_id;
1062 l_person_id_in_tbl(j) :=p_person_id;
1063 l_calendar_type_in_tbl(j) :=p_calendar_type;
1064 l_threshold_in_tbl(j) :=j;
1065 l_as_of_date_in_tbl(j) := p_as_of_date;
1066 l_pd_org_st_date_in_tbl(j) := p_pd_org_st_date;
1067 l_res_cnt := p_res_cnt_tbl(j);
1068 l_availability_in_tbl(j) := sign(l_res_cnt);
1069 l_total_res_cnt_in_tbl(j) := 1;
1070 END LOOP;
1071
1072 FOR j in 1.. g_no_of_user_def_threshold
1073 LOOP
1074 IF ( l_availability_in_tbl(j) <> 0
1075 ) THEN
1076 x_zero_bkt_cnt_flag := 'N';
1077 EXIT;
1078 END IF;
1079 END LOOP;
1080 IF (x_zero_bkt_cnt_flag = 'N') THEN
1081 --Call the bulk insert to insert rows for this particular
1082 --period and person id for all thresholds
1083
1084 BULK_INSERT_RS_AVL4
1085 (
1086 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
1087 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
1088 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
1089 p_time_id_in_tbl => l_time_id_in_tbl,
1090 p_person_id_in_tbl => l_person_id_in_tbl,
1091 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
1092 p_threshold_in_tbl => l_threshold_in_tbl,
1093 p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
1094 p_availability_in_tbl => l_availability_in_tbl,
1095 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
1096 p_run_mode => p_run_mode,
1097 p_blind_insert_flag => p_blind_insert_flag
1098 );
1099 --If the period has not changed then only org has changed
1100 --so post the reversal on as of date
1101 IF (p_time_id = p_curr_pd) THEN
1102 --Negate the bucket values for current date
1103 FOR j in 1.. g_no_of_user_def_threshold
1104 LOOP
1105 l_availability_in_tbl(j) := -l_availability_in_tbl(j);
1106 l_total_res_cnt_in_tbl(j) := -l_total_res_cnt_in_tbl(j);
1107 END LOOP;
1108
1109 BULK_INSERT_RS_AVL4
1110 (
1111 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
1112 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
1113 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
1114 p_time_id_in_tbl => l_time_id_in_tbl,
1115 p_person_id_in_tbl => l_person_id_in_tbl,
1116 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
1117 p_threshold_in_tbl => l_threshold_in_tbl,
1118 p_as_of_date_in_tbl => l_as_of_date_in_tbl,
1119 p_availability_in_tbl => l_availability_in_tbl,
1120 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
1121 p_run_mode => p_run_mode,
1122 p_blind_insert_flag => p_blind_insert_flag
1123 );
1124 END IF;
1125 ELSIF (x_zero_bkt_cnt_flag = 'Y') THEN
1126 --If the PERIOD HAS CHANGED AND the counts are
1127 --zero, we still need to POST the values
1128 --for total resources.
1129 IF (p_time_id <> p_curr_pd) THEN
1130 --the bucket values are zero
1131
1132 BULK_INSERT_RS_AVL4
1133 (
1134 p_exp_organization_id_in_tbl => l_exp_organization_id_in_tbl,
1135 p_exp_org_id_in_tbl => l_exp_org_id_in_tbl,
1136 p_period_type_id_in_tbl => l_period_type_id_in_tbl,
1137 p_time_id_in_tbl => l_time_id_in_tbl,
1138 p_person_id_in_tbl => l_person_id_in_tbl,
1139 p_calendar_type_in_tbl => l_calendar_type_in_tbl,
1140 p_threshold_in_tbl => l_threshold_in_tbl,
1141 p_as_of_date_in_tbl => l_pd_org_st_date_in_tbl,
1142 p_availability_in_tbl => l_availability_in_tbl,
1143 p_total_res_cnt_in_tbl => l_total_res_cnt_in_tbl,
1144 p_run_mode => p_run_mode,
1145 p_blind_insert_flag => p_blind_insert_flag
1146 );
1147 END IF;
1148 END IF;
1149 END IF;
1150
1151 END PREPARE_TO_INS_INTO_AVL4;
1152
1153 PROCEDURE CALCULATE_RES_AVL
1154 (
1155 p_worker_id IN NUMBER,
1156 p_person_id IN NUMBER,
1157 p_run_mode IN VARCHAR2,
1158 x_return_status OUT NOCOPY VARCHAR2
1159 )
1160 IS
1161 --Defining PL/SQL Table variables
1162 l_worker_id_tbl N_TYPE_TAB;
1163 l_exp_organization_id_tbl N_TYPE_TAB;
1164 l_exp_org_id_tbl N_TYPE_TAB;
1165 l_person_id_tbl N_TYPE_TAB;
1166 l_time_id_tbl N_TYPE_TAB;
1167 l_week_id_tbl N_TYPE_TAB;
1168 l_ent_period_id_tbl N_TYPE_TAB;
1169 l_ent_qtr_id_tbl N_TYPE_TAB;
1170 l_gl_period_id_tbl N_TYPE_TAB;
1171 l_gl_qtr_id_tbl N_TYPE_TAB;
1172 l_roll_x_week_1_tbl N_TYPE_TAB;
1173 l_roll_x_week_2_tbl N_TYPE_TAB;
1174 l_roll_x_week_3_tbl N_TYPE_TAB;
1175 l_roll_x_week_4_tbl N_TYPE_TAB;
1176 l_roll_x_week_5_tbl N_TYPE_TAB;
1177 l_roll_x_week_6_tbl N_TYPE_TAB;
1178 l_roll_x_week_7_tbl N_TYPE_TAB;
1179 l_roll_x_week_8_tbl N_TYPE_TAB;
1180 l_roll_x_week_9_tbl N_TYPE_TAB;
1181 l_roll_x_week_10_tbl N_TYPE_TAB;
1182 l_roll_x_week_11_tbl N_TYPE_TAB;
1183 l_roll_x_week_12_tbl N_TYPE_TAB;
1184 l_roll_x_week_13_tbl N_TYPE_TAB;
1185 l_avl_res_cnt_1_tbl N_TYPE_TAB;
1186 l_avl_res_cnt_2_tbl N_TYPE_TAB;
1187 l_avl_res_cnt_3_tbl N_TYPE_TAB;
1188 l_avl_res_cnt_4_tbl N_TYPE_TAB;
1189 l_avl_res_cnt_5_tbl N_TYPE_TAB;
1190
1191 --Defining Local variables for local use
1192 l_worker_id PJI_RM_AGGR_AVL2.worker_id%TYPE;
1193 l_exp_organization_id PJI_RM_AGGR_AVL2.expenditure_organization_id%TYPE;
1194 l_exp_org_id PJI_RM_AGGR_AVL2.expenditure_org_id%TYPE;
1195 l_person_id PJI_RM_AGGR_AVL2.person_id%TYPE;
1196 l_time_id PJI_RM_AGGR_AVL2.time_id%TYPE;
1197 l_week_id PJI_RM_AGGR_AVL2.week_id%TYPE;
1198 l_ent_period_id PJI_RM_AGGR_AVL2.ent_period%TYPE;
1199 l_ent_qtr_id PJI_RM_AGGR_AVL2.ent_qtr%TYPE;
1200 l_gl_period_id PJI_RM_AGGR_AVL2.gl_period%TYPE;
1201 l_gl_qtr_id PJI_RM_AGGR_AVL2.gl_qtr%TYPE;
1202 l_roll_x_week1 PJI_RM_AGGR_AVL2.roll_x_week_1%TYPE;
1203 l_roll_x_week2 PJI_RM_AGGR_AVL2.roll_x_week_2%TYPE;
1204 l_roll_x_week3 PJI_RM_AGGR_AVL2.roll_x_week_3%TYPE;
1205 l_roll_x_week4 PJI_RM_AGGR_AVL2.roll_x_week_4%TYPE;
1206 l_roll_x_week5 PJI_RM_AGGR_AVL2.roll_x_week_5%TYPE;
1207 l_roll_x_week6 PJI_RM_AGGR_AVL2.roll_x_week_6%TYPE;
1208 l_roll_x_week7 PJI_RM_AGGR_AVL2.roll_x_week_7%TYPE;
1209 l_roll_x_week8 PJI_RM_AGGR_AVL2.roll_x_week_8%TYPE;
1210 l_roll_x_week9 PJI_RM_AGGR_AVL2.roll_x_week_9%TYPE;
1211 l_roll_x_week10 PJI_RM_AGGR_AVL2.roll_x_week_10%TYPE;
1212 l_roll_x_week11 PJI_RM_AGGR_AVL2.roll_x_week_11%TYPE;
1213 l_roll_x_week12 PJI_RM_AGGR_AVL2.roll_x_week_12%TYPE;
1214 l_roll_x_week13 PJI_RM_AGGR_AVL2.roll_x_week_13%TYPE;
1215
1216 --Defining PL/SQL Local variables for local use
1217 --Need to store old value of the previous row
1218 --when processing records row by row from
1219 --PJI_RM_AGGR_AVL2 table
1220 l_old_worker_id PJI_RM_AGGR_AVL2.worker_id%TYPE := -1;
1221 l_old_exp_orgnztion_id PJI_RM_AGGR_AVL2.expenditure_organization_id%TYPE := -1;
1222 l_old_exp_org_id PJI_RM_AGGR_AVL2.expenditure_org_id%TYPE := -1;
1223 l_old_person_id PJI_RM_AGGR_AVL2.person_id%TYPE := -1;
1224 l_old_week_id PJI_RM_AGGR_AVL2.week_id%TYPE := -1;
1225 l_old_ent_period_id PJI_RM_AGGR_AVL2.ent_period%TYPE := -1;
1226 l_old_ent_qtr_id PJI_RM_AGGR_AVL2.ent_qtr%TYPE := -1;
1227 l_old_gl_period_id PJI_RM_AGGR_AVL2.gl_period%TYPE := -1;
1228 l_old_gl_qtr_id PJI_RM_AGGR_AVL2.gl_qtr%TYPE := -1;
1229 l_old_roll_x_week1 PJI_RM_AGGR_AVL2.roll_x_week_1%TYPE := -1;
1230 l_old_roll_x_week2 PJI_RM_AGGR_AVL2.roll_x_week_2%TYPE := -1;
1231 l_old_roll_x_week3 PJI_RM_AGGR_AVL2.roll_x_week_3%TYPE := -1;
1232 l_old_roll_x_week4 PJI_RM_AGGR_AVL2.roll_x_week_4%TYPE := -1;
1233 l_old_roll_x_week5 PJI_RM_AGGR_AVL2.roll_x_week_5%TYPE := -1;
1234 l_old_roll_x_week6 PJI_RM_AGGR_AVL2.roll_x_week_6%TYPE := -1;
1235 l_old_roll_x_week7 PJI_RM_AGGR_AVL2.roll_x_week_7%TYPE := -1;
1236 l_old_roll_x_week8 PJI_RM_AGGR_AVL2.roll_x_week_8%TYPE := -1;
1237 l_old_roll_x_week9 PJI_RM_AGGR_AVL2.roll_x_week_9%TYPE := -1;
1238 l_old_roll_x_week10 PJI_RM_AGGR_AVL2.roll_x_week_10%TYPE := -1;
1239 l_old_roll_x_week11 PJI_RM_AGGR_AVL2.roll_x_week_11%TYPE := -1;
1240 l_old_roll_x_week12 PJI_RM_AGGR_AVL2.roll_x_week_12%TYPE := -1;
1241 l_old_roll_x_week13 PJI_RM_AGGR_AVL2.roll_x_week_13%TYPE := -1;
1242
1243 --Defining local variables for storing resource counts for ALL periods
1244 /* Important Point */
1245 /* We are maintaing PL/SQL tables for storing
1246 resource counts for NEW
1247 VALUES for EACH PERIOD. The description of
1248 tables are as follows:
1249 --> NEW Records
1250 [LENGTH = 15
1251 {
1252 5 (no of thresholds)
1253 x
1254 3
1255 (
1256 1 for cumulative count +
1257 1 for current consecutive count +
1258 1 for "previous and greater than" current consecutive count
1259 )
1260 }
1261 ]
1262 */
1263
1264 l_week_res_cnt_tbl N_TYPE_TAB;
1265
1266 l_ent_pd_res_cnt_tbl N_TYPE_TAB;
1267 l_ent_qtr_res_cnt_tbl N_TYPE_TAB;
1268
1269 l_gl_pd_res_cnt_tbl N_TYPE_TAB;
1270 l_gl_qtr_res_cnt_tbl N_TYPE_TAB;
1271
1272 l_roll_x_wk1_res_cnt_tbl N_TYPE_TAB;
1273 l_roll_x_wk2_res_cnt_tbl N_TYPE_TAB;
1274 l_roll_x_wk3_res_cnt_tbl N_TYPE_TAB;
1275 l_roll_x_wk4_res_cnt_tbl N_TYPE_TAB;
1276 l_roll_x_wk5_res_cnt_tbl N_TYPE_TAB;
1277 l_roll_x_wk6_res_cnt_tbl N_TYPE_TAB;
1278 l_roll_x_wk7_res_cnt_tbl N_TYPE_TAB;
1279 l_roll_x_wk8_res_cnt_tbl N_TYPE_TAB;
1280 l_roll_x_wk9_res_cnt_tbl N_TYPE_TAB;
1281 l_roll_x_wk10_res_cnt_tbl N_TYPE_TAB;
1282 l_roll_x_wk11_res_cnt_tbl N_TYPE_TAB;
1283 l_roll_x_wk12_res_cnt_tbl N_TYPE_TAB;
1284 l_roll_x_wk13_res_cnt_tbl N_TYPE_TAB;
1285
1286 --These are variables to store the count to determine posting
1287 --dates of the availability buckets
1288 l_week_count NUMBER(15) := 0;
1289
1290 l_ent_pd_count NUMBER(15) := 0;
1291 l_ent_qtr_count NUMBER(15) := 0;
1292
1293 l_gl_pd_count NUMBER(15) := 0;
1294 l_gl_qtr_count NUMBER(15) := 0;
1295
1296 l_roll_x_wk1_count NUMBER(15) := 0;
1297 l_roll_x_wk2_count NUMBER(15) := 0;
1298 l_roll_x_wk3_count NUMBER(15) := 0;
1299 l_roll_x_wk4_count NUMBER(15) := 0;
1300 l_roll_x_wk5_count NUMBER(15) := 0;
1301 l_roll_x_wk6_count NUMBER(15) := 0;
1302 l_roll_x_wk7_count NUMBER(15) := 0;
1303 l_roll_x_wk8_count NUMBER(15) := 0;
1304 l_roll_x_wk9_count NUMBER(15) := 0;
1305 l_roll_x_wk10_count NUMBER(15) := 0;
1306 l_roll_x_wk11_count NUMBER(15) := 0;
1307 l_roll_x_wk12_count NUMBER(15) := 0;
1308 l_roll_x_wk13_count NUMBER(15) := 0;
1309
1310 --These are variables to store the date of the posting
1311 --date for the non-zero availability buckets.
1312 l_start_date_org_week NUMBER(15) := 0;
1313
1314 l_start_date_org_ent_pd NUMBER(15) := 0;
1315 l_start_date_org_ent_qtr NUMBER(15) := 0;
1316
1317 l_start_date_org_gl_pd NUMBER(15) := 0;
1318 l_start_date_org_gl_qtr NUMBER(15) := 0;
1319
1320 l_start_date_org_roll_x_wk1 NUMBER(15) := 0;
1321 l_start_date_org_roll_x_wk2 NUMBER(15) := 0;
1322 l_start_date_org_roll_x_wk3 NUMBER(15) := 0;
1323 l_start_date_org_roll_x_wk4 NUMBER(15) := 0;
1324 l_start_date_org_roll_x_wk5 NUMBER(15) := 0;
1325 l_start_date_org_roll_x_wk6 NUMBER(15) := 0;
1326 l_start_date_org_roll_x_wk7 NUMBER(15) := 0;
1327 l_start_date_org_roll_x_wk8 NUMBER(15) := 0;
1328 l_start_date_org_roll_x_wk9 NUMBER(15) := 0;
1329 l_start_date_org_roll_x_wk10 NUMBER(15) := 0;
1330 l_start_date_org_roll_x_wk11 NUMBER(15) := 0;
1331 l_start_date_org_roll_x_wk12 NUMBER(15) := 0;
1332 l_start_date_org_roll_x_wk13 NUMBER(15) := 0;
1333
1334 --These are variables to store the organization change event
1335 --for each period type
1336 l_week_org_change_flag VARCHAR2(1):= 'N';
1337
1338 l_ent_pd_org_change_flag VARCHAR2(1):= 'N';
1339 l_ent_qtr_org_change_flag VARCHAR2(1):= 'N';
1340
1341 l_gl_pd_org_change_flag VARCHAR2(1):= 'N';
1342 l_gl_qtr_org_change_flag VARCHAR2(1):= 'N';
1343
1344 l_roll_x_wk1_org_change_flag VARCHAR2(1):= 'N';
1345 l_roll_x_wk2_org_change_flag VARCHAR2(1):= 'N';
1346 l_roll_x_wk3_org_change_flag VARCHAR2(1):= 'N';
1347 l_roll_x_wk4_org_change_flag VARCHAR2(1):= 'N';
1348 l_roll_x_wk5_org_change_flag VARCHAR2(1):= 'N';
1349 l_roll_x_wk6_org_change_flag VARCHAR2(1):= 'N';
1350 l_roll_x_wk7_org_change_flag VARCHAR2(1):= 'N';
1351 l_roll_x_wk8_org_change_flag VARCHAR2(1):= 'N';
1352 l_roll_x_wk9_org_change_flag VARCHAR2(1):= 'N';
1353 l_roll_x_wk10_org_change_flag VARCHAR2(1):= 'N';
1354 l_roll_x_wk11_org_change_flag VARCHAR2(1):= 'N';
1355 l_roll_x_wk12_org_change_flag VARCHAR2(1):= 'N';
1356 l_roll_x_wk13_org_change_flag VARCHAR2(1):= 'N';
1357
1358 -- Other Local Variables
1359 l_bulk_fetch_count NUMBER := 0;
1360 l_count NUMBER := 1;
1361 l_max_count NUMBER := 200;
1362 l_dummy_res_tbl N_TYPE_TAB;
1363 l_zero_bkt_cnt_flag VARCHAR2(1):= 'Y';
1364
1365 --To make sure that the last valid record for a period
1366 --is processed correctly, a dummy record is inserted in
1367 --the cursor with negative values
1368 Cursor Res_cur IS
1369 SELECT
1370 WORKER_ID,
1371 EXPENDITURE_ORGANIZATION_ID,
1372 EXPENDITURE_ORG_ID,
1373 PERSON_ID,
1374 TIME_ID,
1375 WEEK_ID,
1376 ENT_PERIOD,
1377 ENT_QTR ,
1378 GL_PERIOD,
1379 GL_QTR ,
1380 ROLL_X_WEEK_1,
1381 ROLL_X_WEEK_2,
1382 ROLL_X_WEEK_3,
1383 ROLL_X_WEEK_4,
1384 ROLL_X_WEEK_5,
1385 ROLL_X_WEEK_6,
1386 ROLL_X_WEEK_7,
1387 ROLL_X_WEEK_8,
1388 ROLL_X_WEEK_9,
1389 ROLL_X_WEEK_10,
1390 ROLL_X_WEEK_11,
1391 ROLL_X_WEEK_12,
1392 ROLL_X_WEEK_13,
1393 AVL_RES_COUNT_BKT1,
1394 AVL_RES_COUNT_BKT2,
1395 AVL_RES_COUNT_BKT3,
1396 AVL_RES_COUNT_BKT4,
1397 AVL_RES_COUNT_BKT5
1398 FROM
1399 PJI_RM_AGGR_AVL2 avl2
1400 WHERE avl2.person_id = p_person_id
1401 UNION ALL
1402 SELECT
1403 p_worker_id as WORKER_ID,
1404 power(2,49) as EXPENDITURE_ORGANIZATION_ID,
1405 power(2,49) as EXPENDITURE_ORG_ID,
1406 power(2,49) as PERSON_ID,
1407 power(2,49) as TIME_ID,
1408 -power(2,49) as WEEK_ID,
1409 -power(2,49) as ENT_PERIOD,
1410 -power(2,49) as ENT_QTR ,
1411 -power(2,49) as GL_PERIOD,
1412 -power(2,49) as GL_QTR ,
1413 365243 as ROLL_X_WEEK_1,
1414 365243 as ROLL_X_WEEK_2,
1415 365243 as ROLL_X_WEEK_3,
1416 365243 as ROLL_X_WEEK_4,
1417 365243 as ROLL_X_WEEK_5,
1418 365243 as ROLL_X_WEEK_6,
1419 365243 as ROLL_X_WEEK_7,
1420 365243 as ROLL_X_WEEK_8,
1421 365243 as ROLL_X_WEEK_9,
1422 365243 as ROLL_X_WEEK_10,
1423 365243 as ROLL_X_WEEK_11,
1424 365243 as ROLL_X_WEEK_12,
1425 365243 as ROLL_X_WEEK_13,
1426 -power(2,0) as AVL_RES_COUNT_BKT1,
1427 -power(2,0) as AVL_RES_COUNT_BKT2,
1428 -power(2,0) as AVL_RES_COUNT_BKT3,
1429 -power(2,0) as AVL_RES_COUNT_BKT4,
1430 -power(2,0) as AVL_RES_COUNT_BKT5
1431 FROM
1432 DUAL
1433 ORDER BY TIME_ID,
1434 EXPENDITURE_ORGANIZATION_ID;
1435
1436 BEGIN
1437
1438 delete from PJI_RM_AGGR_AVL1 where worker_id = p_worker_id;
1439 delete from PJI_RM_AGGR_AVL2 where worker_id = p_worker_id;
1440
1441 x_return_status := FND_API.G_RET_STS_SUCCESS;
1442 savepoint before_calc_starts;
1443 /*PHASE 1*/
1444 /*
1445 The idea is to determine all different periods and quarters for
1446 both enterprise and GL periods that are affected by the
1447 incremental change in the availability data.
1448 So, for Enterprise periods, we select from PJI_RM_AGGR_RES2
1449 and join it to FII_TIME_DAY to get appropriate distinct records
1450 for the periods/quarters/weeks affected. Similarly, for GL periods,
1451 we select from PJI_RM_AGGR_RES2 and join it to
1452 FII_TIME_CAL_DAY_MV and PJI_ORG_EXTR_INFO to get appropriate
1453 distinct records for the periods/quarters affected.
1454 */
1455 /*
1456 Populate AVL1 only for old fact records
1457 and use the stored values from AVL5
1458 to get values in AVL1 for the new (after
1459 the change is applied) fact records
1460 */
1461
1462 IF (p_run_mode = 'OLD_FACT_RECORDS') THEN
1463
1464 --Insert into TMP1 table
1465 INSERT INTO PJI_RM_AGGR_AVL1
1466 (
1467 EXPENDITURE_ORG_ID,
1468 WORKER_ID,
1469 PERSON_ID,
1470 CALENDAR_TYPE,
1471 GL_CALENDAR_ID,
1472 PERIOD_TYPE_ID,
1473 PERIOD_ID,
1474 PERIOD_TYPE
1475 )
1476 SELECT
1477 cur1.expenditure_org_id as expenditure_org_id,
1478 cur1.worker_id as worker_id,
1479 cur1.person_id as person_id,
1480 cur1.calendar_type as calendar_type,
1481 cur1.gl_calendar_id as gl_calendar_id,
1482 cur1.period_type_id as period_type_id,
1483 cur1.period_id as period_id,
1484 cur1.period_type as period_type
1485 FROM
1486 (
1487 SELECT /*+ no_merge(rt1) */
1488 DISTINCT
1489 rt1.expenditure_org_id as expenditure_org_id,
1490 rt1.worker_id as worker_id,
1491 rt1.person_id as person_id,
1492 rt1.calendar_type as calendar_type,
1493 rt1.gl_calendar_id as gl_calendar_id,
1494 case when rt2.tmp_index = 1 then
1495 16
1496 when rt2.tmp_index = 2 then
1497 64
1498 end period_type_id,
1499 case when rt2.tmp_index = 1 then
1500 rt1.week_id
1501 when rt2.tmp_index = 2 then
1502 rt1.qtr_id
1503 end period_id,
1504 case when rt2.tmp_index = 1 then
1505 'W'
1506 when rt2.tmp_index = 2 then
1507 'E'
1508 end period_type
1509 FROM
1510 (
1511 SELECT /*+ ordered
1512 index(tmp2. PJI_RM_AGGR_RES2_N1)
1513 full(fiit) use_hash(fiit) */
1514 DISTINCT
1515 tmp2.expenditure_org_id as expenditure_org_id,
1516 p_worker_id as worker_id,
1517 tmp2.person_id as person_id,
1518 tmp2.calendar_type as calendar_type,
1519 tmp2.gl_calendar_id as gl_calendar_id,
1520 fiit.ent_qtr_id as qtr_id,
1521 (to_char(fiit.week_start_date,'j') - g_min_wk_j_st_date)/7 + 1 as week_id
1522 FROM
1523 PJI_RM_AGGR_RES2 tmp2,
1524 FII_TIME_DAY fiit
1525 WHERE
1526 tmp2.person_id = p_person_id
1527 and tmp2.time_id = fiit.report_date_julian
1528 and (
1529 ABS(nvl(tmp2.capacity_hrs, 0)) + ABS(nvl(tmp2.available_res_count_bkt1_s, 0)) +
1530 ABS(nvl(tmp2.available_res_count_bkt2_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt3_s, 0)) +
1531 ABS(nvl(tmp2.available_res_count_bkt4_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt5_s, 0))
1532 ) > 0
1533 ) rt1,
1534 (
1535 SELECT 1 as tmp_index from dual
1536 UNION ALL
1537 SELECT 2 as tmp_index from dual
1538 ) rt2
1539 UNION ALL
1540 SELECT /*+ ordered
1541 index(tmp2, PJI_RM_AGGR_RES2_N1)
1542 index(fiit, FII_TIME_CAL_DAY_MV_U1) */
1543 DISTINCT
1544 tmp2.expenditure_org_id as expenditure_org_id,
1545 p_worker_id as worker_id,
1546 tmp2.person_id as person_id,
1547 tmp2.calendar_type as calendar_type,
1548 tmp2.gl_calendar_id as gl_calendar_id,
1549 64 as period_type_id,
1550 fiit.cal_qtr_id as period_id,
1551 'G' as period_type
1552 FROM
1553 PJI_RM_AGGR_RES2 tmp2,
1554 FII_TIME_CAL_DAY_MV fiit
1555 WHERE
1556 tmp2.person_id = p_person_id
1557 and to_date(to_char(tmp2.time_id), 'J') = fiit.report_date
1558 and (
1559 ABS(nvl(tmp2.capacity_hrs, 0)) + ABS(nvl(tmp2.available_res_count_bkt1_s, 0)) +
1560 ABS(nvl(tmp2.available_res_count_bkt2_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt3_s, 0)) +
1561 ABS(nvl(tmp2.available_res_count_bkt4_s, 0)) + ABS(nvl(tmp2.available_res_count_bkt5_s, 0))
1562 )> 0
1563 and tmp2.gl_calendar_id = fiit.calendar_id
1564 ) cur1;
1565
1566 --Insert this is AVL5 table so that we can pick up
1567 --the values from this table in the second run after
1568 --changes are applied to the fact table
1569
1570 INSERT INTO PJI_RM_AGGR_AVL5
1571 (
1572 EXPENDITURE_ORG_ID,
1573 PERSON_ID,
1574 CALENDAR_TYPE,
1575 GL_CALENDAR_ID,
1576 PERIOD_TYPE_ID,
1577 PERIOD_ID,
1578 PERIOD_TYPE
1579 )
1580 SELECT
1581 avl1.expenditure_org_id as expenditure_org_id,
1582 avl1.person_id as person_id,
1583 avl1.calendar_type as calendar_type,
1584 avl1.gl_calendar_id as gl_calendar_id,
1585 avl1.period_type_id as period_type_id,
1586 avl1.period_id as period_id,
1587 avl1.period_type as period_type
1588 FROM PJI_RM_AGGR_AVL1 avl1;
1589 ELSE
1590 --This is for the next run when changes
1591 --have been applied to the fact table
1592 INSERT INTO PJI_RM_AGGR_AVL1
1593 (
1594 EXPENDITURE_ORG_ID,
1595 WORKER_ID,
1596 PERSON_ID,
1597 CALENDAR_TYPE,
1598 GL_CALENDAR_ID,
1599 PERIOD_TYPE_ID,
1600 PERIOD_ID,
1601 PERIOD_TYPE
1602 )
1603 SELECT
1604 avl5.expenditure_org_id as expenditure_org_id,
1605 p_worker_id as worker_id,
1606 avl5.person_id as person_id,
1607 avl5.calendar_type as calendar_type,
1608 avl5.gl_calendar_id as gl_calendar_id,
1609 avl5.period_type_id as period_type_id,
1610 avl5.period_id as period_id,
1611 avl5.period_type as period_type
1612 FROM PJI_RM_AGGR_AVL5 avl5
1613 WHERE avl5.person_id = p_person_id;
1614 END IF;
1615
1616 /*PHASE 2*/
1617 /*
1618 The basic idea for phase 2 is to determine the earliest
1619 and the latest dates that are affected across all periods.
1620 So, once this is determined, we store records as a union
1621 of all days that are affected across all periods. The same
1622 day may actually affect a period in enterprise period but
1623 may not affect a period in GL. We store that information
1624 in a matrix table that has a completely denormalized schema.
1625 */
1626 --Populate TMP2 table
1627 INSERT INTO PJI_RM_AGGR_AVL2
1628 (
1629 WORKER_ID,
1630 EXPENDITURE_ORGANIZATION_ID,
1631 EXPENDITURE_ORG_ID,
1632 PERSON_ID,
1633 TIME_ID,
1634 WEEK_ID,
1635 ENT_PERIOD,
1636 ENT_QTR ,
1637 GL_PERIOD,
1638 GL_QTR ,
1639 ROLL_X_WEEK_1,
1640 ROLL_X_WEEK_2,
1641 ROLL_X_WEEK_3,
1642 ROLL_X_WEEK_4,
1643 ROLL_X_WEEK_5,
1644 ROLL_X_WEEK_6,
1645 ROLL_X_WEEK_7,
1646 ROLL_X_WEEK_8,
1647 ROLL_X_WEEK_9,
1648 ROLL_X_WEEK_10,
1649 ROLL_X_WEEK_11,
1650 ROLL_X_WEEK_12,
1651 ROLL_X_WEEK_13,
1652 AVL_RES_COUNT_BKT1,
1653 AVL_RES_COUNT_BKT2,
1654 AVL_RES_COUNT_BKT3,
1655 AVL_RES_COUNT_BKT4,
1656 AVL_RES_COUNT_BKT5
1657 )
1658 SELECT /*+ full(cur2) use_hash(cur2) index(fct, PJI_RM_RES_F_N2) */
1659 cur2.worker_id as worker_id,
1660 fct.expenditure_organization_id,
1661 fct.expenditure_org_id,
1662 cur2.person_id as person_id,
1663 cur2.time_id as time_id,
1664 cur2.week_id as week_id,
1665 cur2.ent_period as ent_period,
1666 cur2.ent_qtr as ent_qtr,
1667 cur2.gl_period as gl_period,
1668 cur2.gl_qtr as gl_qtr,
1669 cur2.ROLL_X_WEEK_1 as ROLL_X_WEEK_1,
1670 cur2.ROLL_X_WEEK_2 as ROLL_X_WEEK_2,
1671 cur2.ROLL_X_WEEK_3 as ROLL_X_WEEK_3,
1672 cur2.ROLL_X_WEEK_4 as ROLL_X_WEEK_4,
1673 cur2.ROLL_X_WEEK_5 as ROLL_X_WEEK_5,
1674 cur2.ROLL_X_WEEK_6 as ROLL_X_WEEK_6,
1675 cur2.ROLL_X_WEEK_7 as ROLL_X_WEEK_7,
1676 cur2.ROLL_X_WEEK_8 as ROLL_X_WEEK_8,
1677 cur2.ROLL_X_WEEK_9 as ROLL_X_WEEK_9,
1678 cur2.ROLL_X_WEEK_10 as ROLL_X_WEEK_10,
1679 cur2.ROLL_X_WEEK_11 as ROLL_X_WEEK_11,
1680 cur2.ROLL_X_WEEK_12 as ROLL_X_WEEK_12,
1681 cur2.ROLL_X_WEEK_13 as ROLL_X_WEEK_13,
1682 NVL(fct.AVAILABLE_RES_COUNT_BKT1_S, 0) as AVL_RES_COUNT_BKT1,
1683 NVL(fct.AVAILABLE_RES_COUNT_BKT2_S, 0) as AVL_RES_COUNT_BKT2,
1684 NVL(fct.AVAILABLE_RES_COUNT_BKT3_S, 0) as AVL_RES_COUNT_BKT3,
1685 NVL(fct.AVAILABLE_RES_COUNT_BKT4_S, 0) as AVL_RES_COUNT_BKT4,
1686 NVL(fct.AVAILABLE_RES_COUNT_BKT5_S, 0) as AVL_RES_COUNT_BKT5
1687 FROM
1688 (
1689 SELECT
1690 cur1.worker_id as worker_id,
1691 cur1.person_id as person_id,
1692 cur1.time_id as time_id,
1693 sum(cur1.week_id) as week_id,
1694 sum(cur1.ent_period) as ent_period,
1695 sum(cur1.ent_qtr) as ent_qtr,
1696 sum(cur1.gl_period) as gl_period,
1697 sum(cur1.gl_qtr) as gl_qtr,
1698 sum(cur1.ROLL_X_WEEK_1) as ROLL_X_WEEK_1,
1699 sum(cur1.ROLL_X_WEEK_2) as ROLL_X_WEEK_2,
1700 sum(cur1.ROLL_X_WEEK_3) as ROLL_X_WEEK_3,
1701 sum(cur1.ROLL_X_WEEK_4) as ROLL_X_WEEK_4,
1702 sum(cur1.ROLL_X_WEEK_5) as ROLL_X_WEEK_5,
1703 sum(cur1.ROLL_X_WEEK_6) as ROLL_X_WEEK_6,
1704 sum(cur1.ROLL_X_WEEK_7) as ROLL_X_WEEK_7,
1705 sum(cur1.ROLL_X_WEEK_8) as ROLL_X_WEEK_8,
1706 sum(cur1.ROLL_X_WEEK_9) as ROLL_X_WEEK_9,
1707 sum(cur1.ROLL_X_WEEK_10) as ROLL_X_WEEK_10,
1708 sum(cur1.ROLL_X_WEEK_11) as ROLL_X_WEEK_11,
1709 sum(cur1.ROLL_X_WEEK_12) as ROLL_X_WEEK_12,
1710 sum(cur1.ROLL_X_WEEK_13) as ROLL_X_WEEK_13
1711 FROM
1712 (
1713 SELECT /*+ cardinality(avl_tmp1, 1) cache(fiit) */ DISTINCT
1714 p_worker_id as worker_id,
1715 avl_tmp1.person_id as person_id,
1716 fiit.report_date_julian as time_id,
1717 0 as week_id,
1718 fiit.ent_period_id as ent_period,
1719 fiit.ent_qtr_id as ent_qtr,
1720 0 as gl_period,
1721 0 as gl_qtr,
1722 0 as ROLL_X_WEEK_1,
1723 0 as ROLL_X_WEEK_2,
1724 0 as ROLL_X_WEEK_3,
1725 0 as ROLL_X_WEEK_4,
1726 0 as ROLL_X_WEEK_5,
1727 0 as ROLL_X_WEEK_6,
1728 0 as ROLL_X_WEEK_7,
1729 0 as ROLL_X_WEEK_8,
1730 0 as ROLL_X_WEEK_9,
1731 0 as ROLL_X_WEEK_10,
1732 0 as ROLL_X_WEEK_11,
1733 0 as ROLL_X_WEEK_12,
1734 0 as ROLL_X_WEEK_13
1735 FROM
1736 PJI_RM_AGGR_AVL1 avl_tmp1,
1737 FII_TIME_DAY fiit
1738 WHERE
1739 avl_tmp1.PERIOD_ID = fiit.ENT_QTR_ID
1740 and avl_tmp1.period_type_id = 64
1741 and avl_tmp1.period_type = 'E'
1742 and avl_tmp1.worker_id = p_worker_id
1743 UNION ALL
1744 SELECT /*+ cardinality(avl_tmp1, 1) */
1745 DISTINCT
1746 p_worker_id as worker_id,
1747 avl_tmp1.person_id as person_id,
1748 fiit.report_date_julian as time_id,
1749 0 as week_id,
1750 0 as ent_period,
1751 0 as ent_qtr,
1752 fiit.cal_period_id as gl_period,
1753 fiit.cal_qtr_id as gl_qtr,
1754 0 as ROLL_X_WEEK_1,
1755 0 as ROLL_X_WEEK_2,
1756 0 as ROLL_X_WEEK_3,
1757 0 as ROLL_X_WEEK_4,
1758 0 as ROLL_X_WEEK_5,
1759 0 as ROLL_X_WEEK_6,
1760 0 as ROLL_X_WEEK_7,
1761 0 as ROLL_X_WEEK_8,
1762 0 as ROLL_X_WEEK_9,
1763 0 as ROLL_X_WEEK_10,
1764 0 as ROLL_X_WEEK_11,
1765 0 as ROLL_X_WEEK_12,
1766 0 as ROLL_X_WEEK_13
1767 FROM
1768 PJI_RM_AGGR_AVL1 avl_tmp1,
1769 FII_TIME_CAL_PERIOD per,
1770 FII_TIME_CAL_DAY_MV fiit
1771 WHERE
1772 avl_tmp1.PERIOD_ID = per.CAL_QTR_ID
1773 and avl_tmp1.gl_calendar_id = per.calendar_id
1774 and per.cal_period_id = fiit.cal_period_id
1775 and per.calendar_id = fiit.calendar_id
1776 and avl_tmp1.period_type_id = 64
1777 and avl_tmp1.period_type = 'G'
1778 and avl_tmp1.worker_id = p_worker_id
1779 UNION ALL
1780 SELECT
1781 cur.worker_id as worker_id,
1782 cur.person_id as person_id,
1783 fiid.report_date_julian as time_id,
1784 cur.week_id as week_id,
1785 0 as ent_period,
1786 0 as ent_qtr,
1787 0 as gl_period,
1788 0 as gl_qtr,
1789 cur.rw1 as ROLL_X_WEEK_1,
1790 cur.rw2 as ROLL_X_WEEK_2,
1791 cur.rw3 as ROLL_X_WEEK_3,
1792 cur.rw4 as ROLL_X_WEEK_4,
1793 cur.rw5 as ROLL_X_WEEK_5,
1794 cur.rw6 as ROLL_X_WEEK_6,
1795 cur.rw7 as ROLL_X_WEEK_7,
1796 cur.rw8 as ROLL_X_WEEK_8,
1797 cur.rw9 as ROLL_X_WEEK_9,
1798 cur.rw10 as ROLL_X_WEEK_10,
1799 cur.rw11 as ROLL_X_WEEK_11,
1800 cur.rw12 as ROLL_X_WEEK_12,
1801 cur.rw13 as ROLL_X_WEEK_13
1802 FROM
1803 (
1804 SELECT /*+ no_merge cardinality(1) */
1805 cur4.worker_id as worker_id,
1806 cur4.person_id as person_id,
1807 fweek.week_id as period_id,
1808 fweek1.week_id as week_id,
1809 cur4.rw1 as rw1,
1810 cur4.rw2 as rw2,
1811 cur4.rw3 as rw3,
1812 cur4.rw4 as rw4,
1813 cur4.rw5 as rw5,
1814 cur4.rw6 as rw6,
1815 cur4.rw7 as rw7,
1816 cur4.rw8 as rw8,
1817 cur4.rw9 as rw9,
1818 cur4.rw10 as rw10,
1819 cur4.rw11 as rw11,
1820 cur4.rw12 as rw12,
1821 cur4.rw13 as rw13
1822 FROM
1823 (
1824 SELECT
1825 cur5.worker_id as worker_id,
1826 cur5.person_id as person_id,
1827 cur5.sequence_id as period_id,
1828 sum(NVL(cur5.week_id,0)) as week_id,
1829 DECODE(sign(g_no_of_roll_week-0),1,( (FLOOR(( cur5.sequence_id - 0 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 0 ) * decode( sign(sum(cur5.rw1_flag)), 0, NULL, 1),null) rw1
1830 , DECODE(sign(g_no_of_roll_week-1),1,( (FLOOR(( cur5.sequence_id - 1 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 1 ) * decode( sign(sum(cur5.rw2_flag)), 0, NULL, 1),null) rw2
1831 , DECODE(sign(g_no_of_roll_week-2),1,( (FLOOR(( cur5.sequence_id - 2 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 2 ) * decode( sign(sum(cur5.rw3_flag)), 0, NULL, 1),null) rw3
1832 , DECODE(sign(g_no_of_roll_week-3),1,( (FLOOR(( cur5.sequence_id - 3 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 3 ) * decode( sign(sum(cur5.rw4_flag)), 0, NULL, 1),null) rw4
1833 , DECODE(sign(g_no_of_roll_week-4),1,( (FLOOR(( cur5.sequence_id - 4 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 4 ) * decode( sign(sum(cur5.rw5_flag)), 0, NULL, 1),null) rw5
1834 , DECODE(sign(g_no_of_roll_week-5),1,( (FLOOR(( cur5.sequence_id - 5 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 5 ) * decode( sign(sum(cur5.rw6_flag)), 0, NULL, 1),null) rw6
1835 , DECODE(sign(g_no_of_roll_week-6),1,( (FLOOR(( cur5.sequence_id - 6 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 6 ) * decode( sign(sum(cur5.rw7_flag)), 0, NULL, 1),null) rw7
1836 , DECODE(sign(g_no_of_roll_week-7),1,( (FLOOR(( cur5.sequence_id - 7 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 7 ) * decode( sign(sum(cur5.rw8_flag)), 0, NULL, 1),null) rw8
1837 , DECODE(sign(g_no_of_roll_week-8),1,( (FLOOR(( cur5.sequence_id - 8 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 8 ) * decode( sign(sum(cur5.rw9_flag)), 0, NULL, 1),null) rw9
1838 , DECODE(sign(g_no_of_roll_week-9),1,( (FLOOR(( cur5.sequence_id - 9 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 9 ) * decode( sign(sum(cur5.rw10_flag)), 0, NULL, 1),null) rw10
1839 , DECODE(sign(g_no_of_roll_week-10),1,( (FLOOR(( cur5.sequence_id - 10 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 10 ) * decode( sign(sum(cur5.rw11_flag)), 0, NULL, 1),null) rw11
1840 , DECODE(sign(g_no_of_roll_week-11),1,( (FLOOR(( cur5.sequence_id - 11 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 11 ) * decode( sign(sum(cur5.rw12_flag)), 0, NULL, 1),null) rw12
1841 , DECODE(sign(g_no_of_roll_week-12),1,( (FLOOR(( cur5.sequence_id - 12 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 12 ) * decode( sign(sum(cur5.rw13_flag)), 0, NULL, 1),null) rw13
1842 FROM
1843 (
1844 SELECT DISTINCT
1845 p_worker_id as worker_id,
1846 w.person_id as person_id,
1847 w.period_id + rw.offset sequence_id
1848 , case when rw.offset = 0 then
1849 w.period_id
1850 else
1851 null
1852 end as week_id
1853 , DECODE(sign(g_no_of_roll_week-0),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 0 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 0 ))/g_no_of_roll_week) )),null) rw1_flag
1854 , DECODE(sign(g_no_of_roll_week-1),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 1 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 1 ))/g_no_of_roll_week) )),null) rw2_flag
1855 , DECODE(sign(g_no_of_roll_week-2),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 2 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 2 ))/g_no_of_roll_week) )),null) rw3_flag
1856 , DECODE(sign(g_no_of_roll_week-3),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 3 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 3 ))/g_no_of_roll_week) )),null) rw4_flag
1857 , DECODE(sign(g_no_of_roll_week-4),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 4 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 4 ))/g_no_of_roll_week) )),null) rw5_flag
1858 , DECODE(sign(g_no_of_roll_week-5),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 5 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 5 ))/g_no_of_roll_week) )),null) rw6_flag
1859 , DECODE(sign(g_no_of_roll_week-6),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 6 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 6 ))/g_no_of_roll_week) )),null) rw7_flag
1860 , DECODE(sign(g_no_of_roll_week-7),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 7 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 7 ))/g_no_of_roll_week) )),null) rw8_flag
1861 , DECODE(sign(g_no_of_roll_week-8),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 8 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 8 ))/g_no_of_roll_week) )),null) rw9_flag
1862 , DECODE(sign(g_no_of_roll_week-9),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 9 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 9 ))/g_no_of_roll_week) )),null) rw10_flag
1863 , DECODE(sign(g_no_of_roll_week-10),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 10 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 10 ))/g_no_of_roll_week) )),null) rw11_flag
1864 , DECODE(sign(g_no_of_roll_week-11),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 11 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 11 ))/g_no_of_roll_week) )),null) rw12_flag
1865 , DECODE(sign(g_no_of_roll_week-12),1,1-abs(sign( FLOOR((w.period_id-( (FLOOR(( w.period_id + rw.offset - 12 + g_no_of_roll_week )/g_no_of_roll_week)-1) * g_no_of_roll_week + 12 ))/g_no_of_roll_week) )),null) rw13_flag
1866 FROM
1867 PJI_RM_AGGR_AVL1 w
1868 , PJI_ROLL_WEEK_OFFSET rw
1869 WHERE
1870 w.period_type = 'W'
1871 and w.worker_id = p_worker_id
1872 ) cur5
1873 GROUP BY worker_id,
1874 person_id,
1875 sequence_id
1876 ) cur4,
1877 (
1878 SELECT /*+ cache(fiit) */
1879 fiit.week_id as week_id,
1880 (to_char(fiit.start_date,'j') - g_min_wk_j_st_date)/7 + 1 as sequence_id
1881 FROM
1882 FII_TIME_WEEK fiit
1883 ) fweek,
1884 (
1885 SELECT /*+ cache(fiii) */
1886 fiii.week_id as week_id,
1887 (to_char(fiii.start_date,'j') - g_min_wk_j_st_date)/7 + 1 as sequence_id
1888 FROM
1889 FII_TIME_WEEK fiii
1890 ) fweek1
1891 WHERE
1892 cur4.period_id = fweek.sequence_id
1893 and cur4.week_id = fweek1.sequence_id (+)
1894 ) cur
1895 , FII_TIME_DAY fiid
1896 WHERE
1897 cur.PERIOD_ID = fiid.WEEK_ID
1898 ) cur1
1899 GROUP BY
1900 cur1.worker_id,
1901 cur1.person_id,
1902 cur1.time_id
1903 ) cur2,
1904 PJI_RM_RES_F fct
1905 WHERE
1906 cur2.time_id = fct.time_id
1907 and cur2.person_id = fct.person_id
1908 and 'C' = fct.calendar_type
1909 and 0 <> nvl(fct.capacity_hrs, 0)
1910 and p_person_id = fct.person_id;
1911
1912 /*PHASE 3*/
1913 /*
1914 The basic idea for phase 3 is to determine the availability
1915 of resources for the user defined buckets for each period
1916 type. So, we do a row by row processing for all the records
1917 entered in PJI_RM_AGGR_AVL2 table and based on the affect
1918 it has on each period (enterprise quarter, enterprise period,
1919 GL period, GL quarter, Rolling Weeks), we process for each
1920 period type separately.
1921 Different processing is done for Cumulative buckets and
1922 consecutive buckets within the same period.
1923 */
1924 --Initializing PL/SQL Static tables for storing
1925 --resource count values for each period
1926
1927 FOR i_r in 1.. 15
1928 LOOP
1929 --Storing resource count values for week
1930 l_week_res_cnt_tbl(i_r) := 0;
1931
1932 --Storing resource count values for enterprise periods / quarters
1933 l_ent_pd_res_cnt_tbl(i_r) := 0;
1934 l_ent_qtr_res_cnt_tbl(i_r) := 0;
1935
1936 --Storing resource count values for enterprise periods / quarters
1937 l_gl_pd_res_cnt_tbl(i_r) := 0;
1938 l_gl_qtr_res_cnt_tbl(i_r) := 0;
1939
1940 --Storing resource count values for Rolling wks
1941 l_roll_x_wk1_res_cnt_tbl(i_r) := 0;
1942 l_roll_x_wk2_res_cnt_tbl(i_r) := 0;
1943 l_roll_x_wk3_res_cnt_tbl(i_r) := 0;
1944 l_roll_x_wk4_res_cnt_tbl(i_r) := 0;
1945 l_roll_x_wk5_res_cnt_tbl(i_r) := 0;
1946 l_roll_x_wk6_res_cnt_tbl(i_r) := 0;
1947 l_roll_x_wk7_res_cnt_tbl(i_r) := 0;
1948 l_roll_x_wk8_res_cnt_tbl(i_r) := 0;
1949 l_roll_x_wk9_res_cnt_tbl(i_r) := 0;
1950 l_roll_x_wk10_res_cnt_tbl(i_r) := 0;
1951 l_roll_x_wk11_res_cnt_tbl(i_r) := 0;
1952 l_roll_x_wk12_res_cnt_tbl(i_r) := 0;
1953 l_roll_x_wk13_res_cnt_tbl(i_r) := 0;
1954 END LOOP;
1955
1956 OPEN Res_cur;
1957 LOOP
1958 -- Delete existing records from the PL/SQL tables
1959 l_worker_id_tbl.DELETE;
1960 l_exp_organization_id_tbl.DELETE;
1961 l_exp_org_id_tbl.DELETE;
1962 l_person_id_tbl.DELETE;
1963 l_time_id_tbl.DELETE;
1964 l_week_id_tbl.DELETE;
1965 l_ent_period_id_tbl.DELETE;
1966 l_ent_qtr_id_tbl.DELETE;
1967 l_gl_period_id_tbl.DELETE;
1968 l_gl_qtr_id_tbl.DELETE;
1969 l_roll_x_week_1_tbl.DELETE;
1970 l_roll_x_week_2_tbl.DELETE;
1971 l_roll_x_week_3_tbl.DELETE;
1972 l_roll_x_week_4_tbl.DELETE;
1973 l_roll_x_week_5_tbl.DELETE;
1974 l_roll_x_week_6_tbl.DELETE;
1975 l_roll_x_week_7_tbl.DELETE;
1976 l_roll_x_week_8_tbl.DELETE;
1977 l_roll_x_week_9_tbl.DELETE;
1978 l_roll_x_week_10_tbl.DELETE;
1979 l_roll_x_week_11_tbl.DELETE;
1980 l_roll_x_week_12_tbl.DELETE;
1981 l_roll_x_week_13_tbl.DELETE;
1982 l_avl_res_cnt_1_tbl.DELETE;
1983 l_avl_res_cnt_2_tbl.DELETE;
1984 l_avl_res_cnt_3_tbl.DELETE;
1985 l_avl_res_cnt_4_tbl.DELETE;
1986 l_avl_res_cnt_5_tbl.DELETE;
1987
1988 FETCH Res_cur
1989 BULK COLLECT INTO
1990 l_worker_id_tbl,
1991 l_exp_organization_id_tbl,
1992 l_exp_org_id_tbl,
1993 l_person_id_tbl,
1994 l_time_id_tbl,
1995 l_week_id_tbl,
1996 l_ent_period_id_tbl,
1997 l_ent_qtr_id_tbl,
1998 l_gl_period_id_tbl,
1999 l_gl_qtr_id_tbl,
2000 l_roll_x_week_1_tbl,
2001 l_roll_x_week_2_tbl,
2002 l_roll_x_week_3_tbl,
2003 l_roll_x_week_4_tbl,
2004 l_roll_x_week_5_tbl,
2005 l_roll_x_week_6_tbl,
2006 l_roll_x_week_7_tbl,
2007 l_roll_x_week_8_tbl,
2008 l_roll_x_week_9_tbl,
2009 l_roll_x_week_10_tbl,
2010 l_roll_x_week_11_tbl,
2011 l_roll_x_week_12_tbl,
2012 l_roll_x_week_13_tbl,
2013 l_avl_res_cnt_1_tbl,
2014 l_avl_res_cnt_2_tbl,
2015 l_avl_res_cnt_3_tbl,
2016 l_avl_res_cnt_4_tbl,
2017 l_avl_res_cnt_5_tbl
2018 LIMIT l_max_count;
2019 l_bulk_fetch_count := l_exp_organization_id_tbl.count;
2020
2021 IF (l_bulk_fetch_count = 0) THEN
2022 EXIT;
2023 END IF;
2024 FOR i in l_exp_organization_id_tbl.FIRST.. l_exp_organization_id_tbl.LAST
2025 LOOP
2026 --Assigning PL/SQL table values to local variables
2027
2028 l_worker_id := l_worker_id_tbl(i);
2029 l_exp_organization_id := l_exp_organization_id_tbl(i);
2030 l_exp_org_id := l_exp_org_id_tbl(i);
2031 l_person_id := l_person_id_tbl(i);
2032 l_time_id := l_time_id_tbl(i);
2033 l_week_id := l_week_id_tbl(i);
2034 l_ent_period_id := l_ent_period_id_tbl(i);
2035 l_ent_qtr_id := l_ent_qtr_id_tbl(i);
2036 l_gl_period_id := l_gl_period_id_tbl(i);
2037 l_gl_qtr_id := l_gl_qtr_id_tbl(i);
2038 l_roll_x_week1 := l_roll_x_week_1_tbl(i);
2039 l_roll_x_week2 := l_roll_x_week_2_tbl(i);
2040 l_roll_x_week3 := l_roll_x_week_3_tbl(i);
2041 l_roll_x_week4 := l_roll_x_week_4_tbl(i);
2042 l_roll_x_week5 := l_roll_x_week_5_tbl(i);
2043 l_roll_x_week6 := l_roll_x_week_6_tbl(i);
2044 l_roll_x_week7 := l_roll_x_week_7_tbl(i);
2045 l_roll_x_week8 := l_roll_x_week_8_tbl(i);
2046 l_roll_x_week9 := l_roll_x_week_9_tbl(i);
2047 l_roll_x_week10 := l_roll_x_week_10_tbl(i);
2048 l_roll_x_week11 := l_roll_x_week_11_tbl(i);
2049 l_roll_x_week12 := l_roll_x_week_12_tbl(i);
2050 l_roll_x_week13 := l_roll_x_week_13_tbl(i);
2051 g_avl_res_cnt_1 := l_avl_res_cnt_1_tbl(i);
2052 g_avl_res_cnt_2 := l_avl_res_cnt_2_tbl(i);
2053 g_avl_res_cnt_3 := l_avl_res_cnt_3_tbl(i);
2054 g_avl_res_cnt_4 := l_avl_res_cnt_4_tbl(i);
2055 g_avl_res_cnt_5 := l_avl_res_cnt_5_tbl(i);
2056
2057 --Processing starts now
2058 --First for ENTERPRISE PERIOD
2059 IF (l_ent_period_id <> 0 AND l_ent_period_id IS NOT NULL) THEN
2060 IF (l_old_ent_period_id < 0
2061 OR l_old_person_id < 0
2062 OR l_old_exp_orgnztion_id < 0
2063 ) THEN
2064 -- Do Nothing
2065 -- This is just to make sure that nothing is
2066 -- executed for the very first time the program
2067 -- comes in the Cursor
2068 --DBMS_OUTPUT.PUT_LINE('1');
2069 NULL;
2070 ELSIF (l_ent_period_id <> l_old_ent_period_id
2071 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2072 ) THEN
2073 --All records for this enterprise period
2074 --and person ids are processed. So, now determine
2075 --the buckets
2076 /*
2077 For consecutive records this processing is being done
2078 because if the last record in the counting of
2079 resource counts is 1, the previous count for consecutive
2080 availability will not get replaced with the new count.
2081 */
2082
2083 l_ent_pd_res_cnt_tbl(11) := GREATEST (l_ent_pd_res_cnt_tbl(6), NVL(l_ent_pd_res_cnt_tbl(11),0));
2084 l_ent_pd_res_cnt_tbl(12) := GREATEST (l_ent_pd_res_cnt_tbl(7), NVL(l_ent_pd_res_cnt_tbl(12),0));
2085 l_ent_pd_res_cnt_tbl(13) := GREATEST (l_ent_pd_res_cnt_tbl(8), NVL(l_ent_pd_res_cnt_tbl(13),0));
2086 l_ent_pd_res_cnt_tbl(14) := GREATEST (l_ent_pd_res_cnt_tbl(9), NVL(l_ent_pd_res_cnt_tbl(14),0));
2087 l_ent_pd_res_cnt_tbl(15) := GREATEST (l_ent_pd_res_cnt_tbl(10), NVL(l_ent_pd_res_cnt_tbl(15),0));
2088
2089 --INSERT Records for this particular
2090 --person id and enterprise period in
2091 --PJI_RM_AGGR_AVL3 table
2092
2093 PREPARE_TO_INS_INTO_AVL3
2094 (
2095 p_exp_organization_id => l_old_exp_orgnztion_id,
2096 p_exp_org_id => l_old_exp_org_id,
2097 p_person_id => l_old_person_id,
2098 p_time_id => l_old_ent_period_id,
2099 p_curr_pd => l_ent_period_id,
2100 p_as_of_date => l_time_id,
2101 p_pd_org_st_date => l_start_date_org_ent_pd,
2102 p_period_type_id => 32,
2103 p_calendar_type => 'E',
2104 p_res_cnt_tbl => l_ent_pd_res_cnt_tbl,
2105 p_run_mode => p_run_mode,
2106 p_blind_insert_flag => 'N',
2107 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2108 );
2109
2110 IF (l_ent_period_id = l_old_ent_period_id) THEN
2111 l_ent_pd_org_change_flag := 'Y';
2112 ELSE
2113 l_ent_pd_count := 0;
2114 END IF;
2115 --After insert SET ALL count and values to 0
2116 FOR m in l_ent_pd_res_cnt_tbl.FIRST.. l_ent_pd_res_cnt_tbl.LAST
2117 LOOP
2118 l_ent_pd_res_cnt_tbl(m) := 0;
2119 END LOOP;
2120 END IF;
2121 --Processing for cumulative records
2122 l_ent_pd_res_cnt_tbl(1) := NVL(l_ent_pd_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2123 l_ent_pd_res_cnt_tbl(2) := NVL(l_ent_pd_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2124 l_ent_pd_res_cnt_tbl(3) := NVL(l_ent_pd_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2125 l_ent_pd_res_cnt_tbl(4) := NVL(l_ent_pd_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2126 l_ent_pd_res_cnt_tbl(5) := NVL(l_ent_pd_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2127
2128 --Processing for consecutive records
2129 l_ent_pd_res_cnt_tbl(6) := NVL(l_ent_pd_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2130 l_ent_pd_res_cnt_tbl(7) := NVL(l_ent_pd_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2131 l_ent_pd_res_cnt_tbl(8) := NVL(l_ent_pd_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2132 l_ent_pd_res_cnt_tbl(9) := NVL(l_ent_pd_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2133 l_ent_pd_res_cnt_tbl(10):= NVL(l_ent_pd_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2134
2135 --Store the starting day of the period
2136 IF (l_ent_pd_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2137 NULL;
2138 ELSIF (l_ent_pd_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2139 l_start_date_org_ent_pd := l_time_id;
2140 ELSIF (l_ent_pd_count = 0) THEN
2141 BEGIN
2142 SELECT to_char(fiit.start_date,'j')
2143 INTO l_start_date_org_ent_pd
2144 FROM fii_time_ent_period fiit
2145 WHERE ent_period_id = l_ent_period_id;
2146 EXCEPTION
2147 WHEN NO_DATA_FOUND THEN
2148 NULL;
2149 END;
2150 END IF;
2151 --Call API to compare and store consecutive counts
2152 --for the resource
2153 CALC_CS_RES_CNT_VALUE
2154 (
2155 p_res_cnt_tbl => l_ent_pd_res_cnt_tbl
2156 );
2157 --Assigning current enterprise period id to
2158 --old period id local variable
2159 l_old_ent_period_id := l_ent_period_id;
2160 l_ent_pd_count := l_ent_pd_count + 1;
2161 l_ent_pd_org_change_flag:= 'N';
2162 END IF;
2163 /* End of Processing for ENTERPRISE PERIOD */
2164
2165 --For ENTERPRISE QUARTER
2166 IF (l_ent_qtr_id <> 0 AND l_ent_qtr_id IS NOT NULL) THEN
2167 IF (l_old_ent_qtr_id < 0
2168 OR l_old_person_id < 0
2169 OR l_old_exp_orgnztion_id < 0
2170 ) THEN
2171 -- Do Nothing
2172 -- This is just to make sure that nothing is
2173 -- executed for the very first time the program
2174 -- comes in the Cursor
2175 --DBMS_OUTPUT.PUT_LINE('1');
2176 NULL;
2177 ELSIF (l_ent_qtr_id <> l_old_ent_qtr_id
2178 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2179 ) THEN
2180 --All records for this ENTERPRISE QUARTER
2181 --and person ids are processed. So, now determine
2182 --the buckets
2183 /*
2184 For consecutive records this processing is being done
2185 because if the last record in the counting of
2186 resource counts is 1, the previous count for consecutive
2187 availability will not get replaced with the new count.
2188 */
2189
2190 l_ent_qtr_res_cnt_tbl(11) := GREATEST (l_ent_qtr_res_cnt_tbl(6), NVL(l_ent_qtr_res_cnt_tbl(11),0));
2191 l_ent_qtr_res_cnt_tbl(12) := GREATEST (l_ent_qtr_res_cnt_tbl(7), NVL(l_ent_qtr_res_cnt_tbl(12),0));
2192 l_ent_qtr_res_cnt_tbl(13) := GREATEST (l_ent_qtr_res_cnt_tbl(8), NVL(l_ent_qtr_res_cnt_tbl(13),0));
2193 l_ent_qtr_res_cnt_tbl(14) := GREATEST (l_ent_qtr_res_cnt_tbl(9), NVL(l_ent_qtr_res_cnt_tbl(14),0));
2194 l_ent_qtr_res_cnt_tbl(15) := GREATEST (l_ent_qtr_res_cnt_tbl(10), NVL(l_ent_qtr_res_cnt_tbl(15),0));
2195
2196 --INSERT Records for this particular
2197 --person id and ENTERPRISE QUARTER in
2198 --PJI_RM_AGGR_AVL3 table
2199
2200 PREPARE_TO_INS_INTO_AVL3
2201 (
2202 p_exp_organization_id => l_old_exp_orgnztion_id,
2203 p_exp_org_id => l_old_exp_org_id,
2204 p_person_id => l_old_person_id,
2205 p_time_id => l_old_ent_qtr_id,
2206 p_curr_pd => l_ent_qtr_id,
2207 p_as_of_date => l_time_id,
2208 p_pd_org_st_date => l_start_date_org_ent_qtr,
2209 p_period_type_id => 64,
2210 p_calendar_type => 'E',
2211 p_res_cnt_tbl => l_ent_qtr_res_cnt_tbl,
2212 p_run_mode => p_run_mode,
2213 p_blind_insert_flag => 'N',
2214 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2215 );
2216
2217 IF (l_ent_qtr_id = l_old_ent_qtr_id) THEN
2218 l_ent_qtr_org_change_flag := 'Y';
2219 ELSE
2220 l_ent_qtr_count := 0;
2221 END IF;
2222 --After insert SET ALL count and values to 0
2223 FOR m in l_ent_qtr_res_cnt_tbl.FIRST.. l_ent_qtr_res_cnt_tbl.LAST
2224 LOOP
2225 l_ent_qtr_res_cnt_tbl(m) := 0;
2226 END LOOP;
2227 END IF;
2228 --Processing for cumulative records
2229 l_ent_qtr_res_cnt_tbl(1) := NVL(l_ent_qtr_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2230 l_ent_qtr_res_cnt_tbl(2) := NVL(l_ent_qtr_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2231 l_ent_qtr_res_cnt_tbl(3) := NVL(l_ent_qtr_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2232 l_ent_qtr_res_cnt_tbl(4) := NVL(l_ent_qtr_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2233 l_ent_qtr_res_cnt_tbl(5) := NVL(l_ent_qtr_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2234
2235 --Processing for consecutive records
2236 l_ent_qtr_res_cnt_tbl(6) := NVL(l_ent_qtr_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2237 l_ent_qtr_res_cnt_tbl(7) := NVL(l_ent_qtr_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2238 l_ent_qtr_res_cnt_tbl(8) := NVL(l_ent_qtr_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2239 l_ent_qtr_res_cnt_tbl(9) := NVL(l_ent_qtr_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2240 l_ent_qtr_res_cnt_tbl(10):= NVL(l_ent_qtr_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2241
2242 --Store the starting day of the period
2243 IF (l_ent_qtr_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2244 NULL;
2245 ELSIF (l_ent_qtr_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2246 l_start_date_org_ent_qtr := l_time_id;
2247 ELSIF (l_ent_qtr_count = 0) THEN
2248 BEGIN
2249 SELECT to_char(fiit.start_date,'j')
2250 INTO l_start_date_org_ent_qtr
2251 FROM fii_time_ent_qtr fiit
2252 WHERE ent_qtr_id = l_ent_qtr_id;
2253 EXCEPTION
2254 WHEN NO_DATA_FOUND THEN
2255 NULL;
2256 END;
2257 END IF;
2258 --Call API to compare and store consecutive counts
2259 --for the resource
2260 CALC_CS_RES_CNT_VALUE
2261 (
2262 p_res_cnt_tbl => l_ent_qtr_res_cnt_tbl
2263 );
2264 --Assigning current ENTERPRISE QUARTER id to
2265 --old period id local variable
2266 l_old_ent_qtr_id := l_ent_qtr_id;
2267 l_ent_qtr_count := l_ent_qtr_count + 1;
2268 l_ent_qtr_org_change_flag:= 'N';
2269 END IF;
2270 /* End of Processing for ENTERPRISE QUARTER */
2271
2272 --For GL PERIOD
2273 IF (l_gl_period_id <> 0 AND l_gl_period_id IS NOT NULL) THEN
2274 IF (l_old_gl_period_id < 0
2275 OR l_old_person_id < 0
2276 OR l_old_exp_orgnztion_id < 0
2277 ) THEN
2278 -- Do Nothing
2279 -- This is just to make sure that nothing is
2280 -- executed for the very first time the program
2281 -- comes in the Cursor
2282 --DBMS_OUTPUT.PUT_LINE('1');
2283 NULL;
2284 ELSIF (l_gl_period_id <> l_old_gl_period_id
2285 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2286 ) THEN
2287 --All records for this GL PERIOD
2288 --and person ids are processed. So, now determine
2289 --the buckets
2290 /*
2291 For consecutive records this processing is being done
2292 because if the last record in the counting of
2293 resource counts is 1, the previous count for consecutive
2294 availability will not get replaced with the new count.
2295 */
2296
2297 l_gl_pd_res_cnt_tbl(11) := GREATEST (l_gl_pd_res_cnt_tbl(6), NVL(l_gl_pd_res_cnt_tbl(11),0));
2298 l_gl_pd_res_cnt_tbl(12) := GREATEST (l_gl_pd_res_cnt_tbl(7), NVL(l_gl_pd_res_cnt_tbl(12),0));
2299 l_gl_pd_res_cnt_tbl(13) := GREATEST (l_gl_pd_res_cnt_tbl(8), NVL(l_gl_pd_res_cnt_tbl(13),0));
2300 l_gl_pd_res_cnt_tbl(14) := GREATEST (l_gl_pd_res_cnt_tbl(9), NVL(l_gl_pd_res_cnt_tbl(14),0));
2301 l_gl_pd_res_cnt_tbl(15) := GREATEST (l_gl_pd_res_cnt_tbl(10), NVL(l_gl_pd_res_cnt_tbl(15),0));
2302
2303 --INSERT Records for this particular
2304 --person id and GL PERIOD in
2305 --PJI_RM_AGGR_AVL3 table
2306
2307 PREPARE_TO_INS_INTO_AVL3
2308 (
2309 p_exp_organization_id => l_old_exp_orgnztion_id,
2310 p_exp_org_id => l_old_exp_org_id,
2311 p_person_id => l_old_person_id,
2312 p_time_id => l_old_gl_period_id,
2313 p_curr_pd => l_gl_period_id,
2314 p_as_of_date => l_time_id,
2315 p_pd_org_st_date => l_start_date_org_gl_pd,
2316 p_period_type_id => 32,
2317 p_calendar_type => 'G',
2318 p_res_cnt_tbl => l_gl_pd_res_cnt_tbl,
2319 p_run_mode => p_run_mode,
2320 p_blind_insert_flag => 'N',
2321 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2322 );
2323
2324 IF (l_gl_period_id = l_old_gl_period_id) THEN
2325 l_gl_pd_org_change_flag := 'Y';
2326 ELSE
2327 l_gl_pd_count := 0;
2328 END IF;
2329 --After insert SET ALL count and values to 0
2330 FOR m in l_gl_pd_res_cnt_tbl.FIRST.. l_gl_pd_res_cnt_tbl.LAST
2331 LOOP
2332 l_gl_pd_res_cnt_tbl(m) := 0;
2333 END LOOP;
2334 END IF;
2335 --Processing for cumulative records
2336 l_gl_pd_res_cnt_tbl(1) := NVL(l_gl_pd_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2337 l_gl_pd_res_cnt_tbl(2) := NVL(l_gl_pd_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2338 l_gl_pd_res_cnt_tbl(3) := NVL(l_gl_pd_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2339 l_gl_pd_res_cnt_tbl(4) := NVL(l_gl_pd_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2340 l_gl_pd_res_cnt_tbl(5) := NVL(l_gl_pd_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2341
2342 --Processing for consecutive records
2343 l_gl_pd_res_cnt_tbl(6) := NVL(l_gl_pd_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2344 l_gl_pd_res_cnt_tbl(7) := NVL(l_gl_pd_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2345 l_gl_pd_res_cnt_tbl(8) := NVL(l_gl_pd_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2346 l_gl_pd_res_cnt_tbl(9) := NVL(l_gl_pd_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2347 l_gl_pd_res_cnt_tbl(10):= NVL(l_gl_pd_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2348
2349 --Store the starting day of the period
2350 IF (l_gl_pd_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2351 NULL;
2352 ELSIF (l_gl_pd_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2353 l_start_date_org_gl_pd := l_time_id;
2354 ELSIF (l_gl_pd_count = 0) THEN
2355 BEGIN
2356 SELECT to_char(fiit.start_date,'j')
2357 INTO l_start_date_org_gl_pd
2358 FROM fii_time_cal_period fiit
2359 WHERE cal_period_id = l_gl_period_id;
2360 EXCEPTION
2361 WHEN NO_DATA_FOUND THEN
2362 NULL;
2363 END;
2364 END IF;
2365 --Call API to compare and store consecutive counts
2366 --for the resource
2367 CALC_CS_RES_CNT_VALUE
2368 (
2369 p_res_cnt_tbl => l_gl_pd_res_cnt_tbl
2370 );
2371 --Assigning current GL PERIOD id to
2372 --old period id local variable
2373 l_old_gl_period_id := l_gl_period_id;
2374 l_gl_pd_count := l_gl_pd_count + 1;
2375 l_gl_pd_org_change_flag:= 'N';
2376 END IF;
2377 /* End of Processing for GL PERIOD */
2378
2379 --For GL QUARTER
2380 IF (l_gl_qtr_id <> 0 AND l_gl_qtr_id IS NOT NULL) THEN
2381 IF (l_old_gl_qtr_id < 0
2382 OR l_old_person_id < 0
2383 OR l_old_exp_orgnztion_id < 0
2384 ) THEN
2385 -- Do Nothing
2386 -- This is just to make sure that nothing is
2387 -- executed for the very first time the program
2388 -- comes in the Cursor
2389 --DBMS_OUTPUT.PUT_LINE('1');
2390 NULL;
2391 ELSIF (l_gl_qtr_id <> l_old_gl_qtr_id
2392 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2393 ) THEN
2394 --All records for this GL QUARTER
2395 --and person ids are processed. So, now determine
2396 --the buckets
2397 /*
2398 For consecutive records this processing is being done
2399 because if the last record in the counting of
2400 resource counts is 1, the previous count for consecutive
2401 availability will not get replaced with the new count.
2402 */
2403
2404 l_gl_qtr_res_cnt_tbl(11) := GREATEST (l_gl_qtr_res_cnt_tbl(6), NVL(l_gl_qtr_res_cnt_tbl(11),0));
2405 l_gl_qtr_res_cnt_tbl(12) := GREATEST (l_gl_qtr_res_cnt_tbl(7), NVL(l_gl_qtr_res_cnt_tbl(12),0));
2406 l_gl_qtr_res_cnt_tbl(13) := GREATEST (l_gl_qtr_res_cnt_tbl(8), NVL(l_gl_qtr_res_cnt_tbl(13),0));
2407 l_gl_qtr_res_cnt_tbl(14) := GREATEST (l_gl_qtr_res_cnt_tbl(9), NVL(l_gl_qtr_res_cnt_tbl(14),0));
2408 l_gl_qtr_res_cnt_tbl(15) := GREATEST (l_gl_qtr_res_cnt_tbl(10), NVL(l_gl_qtr_res_cnt_tbl(15),0));
2409
2410 --INSERT Records for this particular
2411 --person id and GL QUARTER in
2412 --PJI_RM_AGGR_AVL3 table
2413
2414 PREPARE_TO_INS_INTO_AVL3
2415 (
2416 p_exp_organization_id => l_old_exp_orgnztion_id,
2417 p_exp_org_id => l_old_exp_org_id,
2418 p_person_id => l_old_person_id,
2419 p_time_id => l_old_gl_qtr_id,
2420 p_curr_pd => l_gl_qtr_id,
2421 p_as_of_date => l_time_id,
2422 p_pd_org_st_date => l_start_date_org_gl_qtr,
2423 p_period_type_id => 64,
2424 p_calendar_type => 'G',
2425 p_res_cnt_tbl => l_gl_qtr_res_cnt_tbl,
2426 p_run_mode => p_run_mode,
2427 p_blind_insert_flag => 'N',
2428 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2429 );
2430
2431 IF (l_gl_qtr_id = l_old_gl_qtr_id) THEN
2432 l_gl_qtr_org_change_flag := 'Y';
2433 ELSE
2434 l_gl_qtr_count := 0;
2435 END IF;
2436 --After insert SET ALL count and values to 0
2437 FOR m in l_gl_qtr_res_cnt_tbl.FIRST.. l_gl_qtr_res_cnt_tbl.LAST
2438 LOOP
2439 l_gl_qtr_res_cnt_tbl(m) := 0;
2440 END LOOP;
2441 END IF;
2442 --Processing for cumulative records
2443 l_gl_qtr_res_cnt_tbl(1) := NVL(l_gl_qtr_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2444 l_gl_qtr_res_cnt_tbl(2) := NVL(l_gl_qtr_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2445 l_gl_qtr_res_cnt_tbl(3) := NVL(l_gl_qtr_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2446 l_gl_qtr_res_cnt_tbl(4) := NVL(l_gl_qtr_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2447 l_gl_qtr_res_cnt_tbl(5) := NVL(l_gl_qtr_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2448
2449 --Processing for consecutive records
2450 l_gl_qtr_res_cnt_tbl(6) := NVL(l_gl_qtr_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2451 l_gl_qtr_res_cnt_tbl(7) := NVL(l_gl_qtr_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2452 l_gl_qtr_res_cnt_tbl(8) := NVL(l_gl_qtr_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2453 l_gl_qtr_res_cnt_tbl(9) := NVL(l_gl_qtr_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2454 l_gl_qtr_res_cnt_tbl(10):= NVL(l_gl_qtr_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2455
2456 --Store the starting day of the period
2457 IF (l_gl_qtr_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2458 NULL;
2459 ELSIF (l_gl_qtr_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2460 l_start_date_org_gl_qtr := l_time_id;
2461 ELSIF (l_gl_qtr_count = 0) THEN
2462 BEGIN
2463 SELECT to_char(fiit.start_date,'j')
2464 INTO l_start_date_org_gl_qtr
2465 FROM fii_time_cal_qtr fiit
2466 WHERE cal_qtr_id = l_gl_qtr_id;
2467 EXCEPTION
2468 WHEN NO_DATA_FOUND THEN
2469 NULL;
2470 END;
2471 END IF;
2472 --Call API to compare and store consecutive counts
2473 --for the resource
2474 CALC_CS_RES_CNT_VALUE
2475 (
2476 p_res_cnt_tbl => l_gl_qtr_res_cnt_tbl
2477 );
2478 --Assigning current GL QUARTER id to
2479 --old period id local variable
2480 l_old_gl_qtr_id := l_gl_qtr_id;
2481 l_gl_qtr_count := l_gl_qtr_count + 1;
2482 l_gl_qtr_org_change_flag:= 'N';
2483 END IF;
2484 /* End of Processing for GL QUARTER */
2485
2486 -- For ROLLING WEEK 1
2487 IF (l_roll_x_week1 > 0 AND l_roll_x_week1 IS NOT NULL) THEN
2488 IF (l_old_roll_x_week1 < 0
2489 OR l_old_person_id < 0
2490 OR l_old_exp_orgnztion_id < 0
2491 ) THEN
2492 -- Do Nothing
2493 -- This is just to make sure that nothing is
2494 -- executed for the very first time the program
2495 -- comes in the Cursor
2496 --DBMS_OUTPUT.PUT_LINE('1');
2497 NULL;
2498 ELSIF (l_roll_x_week1 <> l_old_roll_x_week1
2499 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2500 ) THEN
2501 --All records for this ROLLING WEEK 1
2502 --and person ids are processed. So, now determine
2503 --the buckets
2504 /*
2505 For consecutive records this processing is being done
2506 because if the last record in the counting of
2507 resource counts is 1, the previous count for consecutive
2508 availability will not get replaced with the new count.
2509 */
2510
2511 l_roll_x_wk1_res_cnt_tbl(11) := GREATEST (l_roll_x_wk1_res_cnt_tbl(6), NVL(l_roll_x_wk1_res_cnt_tbl(11),0));
2512 l_roll_x_wk1_res_cnt_tbl(12) := GREATEST (l_roll_x_wk1_res_cnt_tbl(7), NVL(l_roll_x_wk1_res_cnt_tbl(12),0));
2513 l_roll_x_wk1_res_cnt_tbl(13) := GREATEST (l_roll_x_wk1_res_cnt_tbl(8), NVL(l_roll_x_wk1_res_cnt_tbl(13),0));
2514 l_roll_x_wk1_res_cnt_tbl(14) := GREATEST (l_roll_x_wk1_res_cnt_tbl(9), NVL(l_roll_x_wk1_res_cnt_tbl(14),0));
2515 l_roll_x_wk1_res_cnt_tbl(15) := GREATEST (l_roll_x_wk1_res_cnt_tbl(10), NVL(l_roll_x_wk1_res_cnt_tbl(15),0));
2516
2517 --INSERT Records for this particular
2518 --person id and ROLLING WEEK 1 in
2519 --PJI_RM_AGGR_AVL3 table
2520
2521 PREPARE_TO_INS_INTO_AVL3
2522 (
2523 p_exp_organization_id => l_old_exp_orgnztion_id,
2524 p_exp_org_id => l_old_exp_org_id,
2525 p_person_id => l_old_person_id,
2526 p_time_id => l_old_roll_x_week1,
2527 p_curr_pd => l_roll_x_week1,
2528 p_as_of_date => l_time_id,
2529 p_pd_org_st_date => l_start_date_org_roll_x_wk1,
2530 p_period_type_id => 16,
2531 p_calendar_type => 'E',
2532 p_res_cnt_tbl => l_roll_x_wk1_res_cnt_tbl,
2533 p_run_mode => p_run_mode,
2534 p_blind_insert_flag => 'N',
2535 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2536 );
2537
2538 IF (l_roll_x_week1 = l_old_roll_x_week1) THEN
2539 l_roll_x_wk1_org_change_flag := 'Y';
2540 ELSE
2541 l_roll_x_wk1_count := 0;
2542 END IF;
2543 --After insert SET ALL count and values to 0
2544 FOR m in l_roll_x_wk1_res_cnt_tbl.FIRST.. l_roll_x_wk1_res_cnt_tbl.LAST
2545 LOOP
2546 l_roll_x_wk1_res_cnt_tbl(m) := 0;
2547 END LOOP;
2548 END IF;
2549 --Processing for cumulative records
2550 l_roll_x_wk1_res_cnt_tbl(1) := NVL(l_roll_x_wk1_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2551 l_roll_x_wk1_res_cnt_tbl(2) := NVL(l_roll_x_wk1_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2552 l_roll_x_wk1_res_cnt_tbl(3) := NVL(l_roll_x_wk1_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2553 l_roll_x_wk1_res_cnt_tbl(4) := NVL(l_roll_x_wk1_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2554 l_roll_x_wk1_res_cnt_tbl(5) := NVL(l_roll_x_wk1_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2555
2556 --Processing for consecutive records
2557 l_roll_x_wk1_res_cnt_tbl(6) := NVL(l_roll_x_wk1_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2558 l_roll_x_wk1_res_cnt_tbl(7) := NVL(l_roll_x_wk1_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2559 l_roll_x_wk1_res_cnt_tbl(8) := NVL(l_roll_x_wk1_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2560 l_roll_x_wk1_res_cnt_tbl(9) := NVL(l_roll_x_wk1_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2561 l_roll_x_wk1_res_cnt_tbl(10):= NVL(l_roll_x_wk1_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2562
2563 --Store the starting day of the period
2564 IF (l_roll_x_wk1_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2565 NULL;
2566 ELSIF (l_roll_x_wk1_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2567 l_start_date_org_roll_x_wk1 := l_time_id;
2568 ELSIF (l_roll_x_wk1_count = 0) THEN
2569 BEGIN
2570 SELECT to_char(fiit.start_date,'j')
2571 INTO l_start_date_org_roll_x_wk1
2572 FROM FII_TIME_WEEK fiit
2573 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week1 - 1) * 7) + -- Bug#4903567
2574 g_min_wk_j_st_date), 'J');
2575 EXCEPTION
2576 WHEN NO_DATA_FOUND THEN
2577 NULL;
2578 END;
2579 END IF;
2580 --Call API to compare and store consecutive counts
2581 --for the resource
2582 CALC_CS_RES_CNT_VALUE
2583 (
2584 p_res_cnt_tbl => l_roll_x_wk1_res_cnt_tbl
2585 );
2586 --Assigning current ROLLING WEEK 1 id to
2587 --old period id local variable
2588 l_old_roll_x_week1 := l_roll_x_week1;
2589 l_roll_x_wk1_count := l_roll_x_wk1_count + 1;
2590 l_roll_x_wk1_org_change_flag:= 'N';
2591 END IF;
2592 /* End of Processing for ROLLING WEEK 1 */
2593
2594 -- For ROLLING WEEK 2
2595 IF (l_roll_x_week2 > 0 AND l_roll_x_week2 IS NOT NULL) THEN
2596 IF (l_old_roll_x_week2 < 0
2597 OR l_old_person_id < 0
2598 OR l_old_exp_orgnztion_id < 0
2599 ) THEN
2600 -- Do Nothing
2601 -- This is just to make sure that nothing is
2602 -- executed for the very first time the program
2603 -- comes in the Cursor
2604 --DBMS_OUTPUT.PUT_LINE('1');
2605 NULL;
2606 ELSIF (l_roll_x_week2 <> l_old_roll_x_week2
2607 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2608 ) THEN
2609 --All records for this ROLLING WEEK 2
2610 --and person ids are processed. So, now determine
2611 --the buckets
2612 /*
2613 For consecutive records this processing is being done
2614 because if the last record in the counting of
2615 resource counts is 1, the previous count for consecutive
2616 availability will not get replaced with the new count.
2617 */
2618
2619 l_roll_x_wk2_res_cnt_tbl(11) := GREATEST (l_roll_x_wk2_res_cnt_tbl(6), NVL(l_roll_x_wk2_res_cnt_tbl(11),0));
2620 l_roll_x_wk2_res_cnt_tbl(12) := GREATEST (l_roll_x_wk2_res_cnt_tbl(7), NVL(l_roll_x_wk2_res_cnt_tbl(12),0));
2621 l_roll_x_wk2_res_cnt_tbl(13) := GREATEST (l_roll_x_wk2_res_cnt_tbl(8), NVL(l_roll_x_wk2_res_cnt_tbl(13),0));
2622 l_roll_x_wk2_res_cnt_tbl(14) := GREATEST (l_roll_x_wk2_res_cnt_tbl(9), NVL(l_roll_x_wk2_res_cnt_tbl(14),0));
2623 l_roll_x_wk2_res_cnt_tbl(15) := GREATEST (l_roll_x_wk2_res_cnt_tbl(10), NVL(l_roll_x_wk2_res_cnt_tbl(15),0));
2624
2625 --INSERT Records for this particular
2626 --person id and ROLLING WEEK 2 in
2627 --PJI_RM_AGGR_AVL3 table
2628
2629 PREPARE_TO_INS_INTO_AVL3
2630 (
2631 p_exp_organization_id => l_old_exp_orgnztion_id,
2632 p_exp_org_id => l_old_exp_org_id,
2633 p_person_id => l_old_person_id,
2634 p_time_id => l_old_roll_x_week2,
2635 p_curr_pd => l_roll_x_week2,
2636 p_as_of_date => l_time_id,
2637 p_pd_org_st_date => l_start_date_org_roll_x_wk2,
2638 p_period_type_id => 16,
2639 p_calendar_type => 'E',
2640 p_res_cnt_tbl => l_roll_x_wk2_res_cnt_tbl,
2641 p_run_mode => p_run_mode,
2642 p_blind_insert_flag => 'N',
2643 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2644 );
2645
2646 IF (l_roll_x_week2 = l_old_roll_x_week2) THEN
2647 l_roll_x_wk2_org_change_flag := 'Y';
2648 ELSE
2649 l_roll_x_wk2_count := 0;
2650 END IF;
2651 --After insert SET ALL count and values to 0
2652 FOR m in l_roll_x_wk2_res_cnt_tbl.FIRST.. l_roll_x_wk2_res_cnt_tbl.LAST
2653 LOOP
2654 l_roll_x_wk2_res_cnt_tbl(m) := 0;
2655 END LOOP;
2656 END IF;
2657 --Processing for cumulative records
2658 l_roll_x_wk2_res_cnt_tbl(1) := NVL(l_roll_x_wk2_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2659 l_roll_x_wk2_res_cnt_tbl(2) := NVL(l_roll_x_wk2_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2660 l_roll_x_wk2_res_cnt_tbl(3) := NVL(l_roll_x_wk2_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2661 l_roll_x_wk2_res_cnt_tbl(4) := NVL(l_roll_x_wk2_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2662 l_roll_x_wk2_res_cnt_tbl(5) := NVL(l_roll_x_wk2_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2663
2664 --Processing for consecutive records
2665 l_roll_x_wk2_res_cnt_tbl(6) := NVL(l_roll_x_wk2_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2666 l_roll_x_wk2_res_cnt_tbl(7) := NVL(l_roll_x_wk2_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2667 l_roll_x_wk2_res_cnt_tbl(8) := NVL(l_roll_x_wk2_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2668 l_roll_x_wk2_res_cnt_tbl(9) := NVL(l_roll_x_wk2_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2669 l_roll_x_wk2_res_cnt_tbl(10):= NVL(l_roll_x_wk2_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2670
2671 --Store the starting day of the period
2672 IF (l_roll_x_wk2_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2673 NULL;
2674 ELSIF (l_roll_x_wk2_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2675 l_start_date_org_roll_x_wk2 := l_time_id;
2676 ELSIF (l_roll_x_wk2_count = 0) THEN
2677 BEGIN
2678 SELECT to_char(fiit.start_date,'j')
2679 INTO l_start_date_org_roll_x_wk2
2680 FROM FII_TIME_WEEK fiit
2681 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week2 - 1) * 7) + -- Bug#4903567
2682 g_min_wk_j_st_date), 'J');
2683 EXCEPTION
2684 WHEN NO_DATA_FOUND THEN
2685 NULL;
2686 END;
2687 END IF;
2688 --Call API to compare and store consecutive counts
2689 --for the resource
2690 CALC_CS_RES_CNT_VALUE
2691 (
2692 p_res_cnt_tbl => l_roll_x_wk2_res_cnt_tbl
2693 );
2694 --Assigning current ROLLING WEEK 2 id to
2695 --old period id local variable
2696 l_old_roll_x_week2 := l_roll_x_week2;
2697 l_roll_x_wk2_count := l_roll_x_wk2_count + 1;
2698 l_roll_x_wk2_org_change_flag:= 'N';
2699 END IF;
2700 /* End of Processing for ROLLING WEEK 2 */
2701
2702 -- For ROLLING WEEK 3
2703 IF (l_roll_x_week3 > 0 AND l_roll_x_week3 IS NOT NULL) THEN
2704 IF (l_old_roll_x_week3 < 0
2705 OR l_old_person_id < 0
2706 OR l_old_exp_orgnztion_id < 0
2707 ) THEN
2708 -- Do Nothing
2709 -- This is just to make sure that nothing is
2710 -- executed for the very first time the program
2711 -- comes in the Cursor
2712 --DBMS_OUTPUT.PUT_LINE('1');
2713 NULL;
2714 ELSIF (l_roll_x_week3 <> l_old_roll_x_week3
2715 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2716 ) THEN
2717 --All records for this ROLLING WEEK 3
2718 --and person ids are processed. So, now determine
2719 --the buckets
2720 /*
2721 For consecutive records this processing is being done
2722 because if the last record in the counting of
2723 resource counts is 1, the previous count for consecutive
2724 availability will not get replaced with the new count.
2725 */
2726
2727 l_roll_x_wk3_res_cnt_tbl(11) := GREATEST (l_roll_x_wk3_res_cnt_tbl(6), NVL(l_roll_x_wk3_res_cnt_tbl(11),0));
2728 l_roll_x_wk3_res_cnt_tbl(12) := GREATEST (l_roll_x_wk3_res_cnt_tbl(7), NVL(l_roll_x_wk3_res_cnt_tbl(12),0));
2729 l_roll_x_wk3_res_cnt_tbl(13) := GREATEST (l_roll_x_wk3_res_cnt_tbl(8), NVL(l_roll_x_wk3_res_cnt_tbl(13),0));
2730 l_roll_x_wk3_res_cnt_tbl(14) := GREATEST (l_roll_x_wk3_res_cnt_tbl(9), NVL(l_roll_x_wk3_res_cnt_tbl(14),0));
2731 l_roll_x_wk3_res_cnt_tbl(15) := GREATEST (l_roll_x_wk3_res_cnt_tbl(10), NVL(l_roll_x_wk3_res_cnt_tbl(15),0));
2732
2733 --INSERT Records for this particular
2734 --person id and ROLLING WEEK 3 in
2735 --PJI_RM_AGGR_AVL3 table
2736
2737 PREPARE_TO_INS_INTO_AVL3
2738 (
2739 p_exp_organization_id => l_old_exp_orgnztion_id,
2740 p_exp_org_id => l_old_exp_org_id,
2741 p_person_id => l_old_person_id,
2742 p_time_id => l_old_roll_x_week3,
2743 p_curr_pd => l_roll_x_week3,
2744 p_as_of_date => l_time_id,
2745 p_pd_org_st_date => l_start_date_org_roll_x_wk3,
2746 p_period_type_id => 16,
2747 p_calendar_type => 'E',
2748 p_res_cnt_tbl => l_roll_x_wk3_res_cnt_tbl,
2749 p_run_mode => p_run_mode,
2750 p_blind_insert_flag => 'N',
2751 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2752 );
2753
2754 IF (l_roll_x_week3 = l_old_roll_x_week3) THEN
2755 l_roll_x_wk3_org_change_flag := 'Y';
2756 ELSE
2757 l_roll_x_wk3_count := 0;
2758 END IF;
2759 --After insert SET ALL count and values to 0
2760 FOR m in l_roll_x_wk3_res_cnt_tbl.FIRST.. l_roll_x_wk3_res_cnt_tbl.LAST
2761 LOOP
2762 l_roll_x_wk3_res_cnt_tbl(m) := 0;
2763 END LOOP;
2764 END IF;
2765 --Processing for cumulative records
2766 l_roll_x_wk3_res_cnt_tbl(1) := NVL(l_roll_x_wk3_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2767 l_roll_x_wk3_res_cnt_tbl(2) := NVL(l_roll_x_wk3_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2768 l_roll_x_wk3_res_cnt_tbl(3) := NVL(l_roll_x_wk3_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2769 l_roll_x_wk3_res_cnt_tbl(4) := NVL(l_roll_x_wk3_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2770 l_roll_x_wk3_res_cnt_tbl(5) := NVL(l_roll_x_wk3_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2771
2772 --Processing for consecutive records
2773 l_roll_x_wk3_res_cnt_tbl(6) := NVL(l_roll_x_wk3_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2774 l_roll_x_wk3_res_cnt_tbl(7) := NVL(l_roll_x_wk3_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2775 l_roll_x_wk3_res_cnt_tbl(8) := NVL(l_roll_x_wk3_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2776 l_roll_x_wk3_res_cnt_tbl(9) := NVL(l_roll_x_wk3_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2777 l_roll_x_wk3_res_cnt_tbl(10):= NVL(l_roll_x_wk3_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2778
2779 --Store the starting day of the period
2780 IF (l_roll_x_wk3_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2781 NULL;
2782 ELSIF (l_roll_x_wk3_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2783 l_start_date_org_roll_x_wk3 := l_time_id;
2784 ELSIF (l_roll_x_wk3_count = 0) THEN
2785 BEGIN
2786 SELECT to_char(fiit.start_date,'j')
2787 INTO l_start_date_org_roll_x_wk3
2788 FROM FII_TIME_WEEK fiit
2789 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week3 - 1) * 7) + -- Bug#4903567
2790 g_min_wk_j_st_date), 'J');
2791 EXCEPTION
2792 WHEN NO_DATA_FOUND THEN
2793 NULL;
2794 END;
2795 END IF;
2796 --Call API to compare and store consecutive counts
2797 --for the resource
2798 CALC_CS_RES_CNT_VALUE
2799 (
2800 p_res_cnt_tbl => l_roll_x_wk3_res_cnt_tbl
2801 );
2802 --Assigning current ROLLING WEEK 3 id to
2803 --old period id local variable
2804 l_old_roll_x_week3 := l_roll_x_week3;
2805 l_roll_x_wk3_count := l_roll_x_wk3_count + 1;
2806 l_roll_x_wk3_org_change_flag:= 'N';
2807 END IF;
2808 /* End of Processing for ROLLING WEEK 3 */
2809
2810 -- For ROLLING WEEK 4
2811 IF (l_roll_x_week4 > 0 AND l_roll_x_week4 IS NOT NULL) THEN
2812 IF (l_old_roll_x_week4 < 0
2813 OR l_old_person_id < 0
2814 OR l_old_exp_orgnztion_id < 0
2815 ) THEN
2816 -- Do Nothing
2817 -- This is just to make sure that nothing is
2818 -- executed for the very first time the program
2819 -- comes in the Cursor
2820 --DBMS_OUTPUT.PUT_LINE('1');
2821 NULL;
2822 ELSIF (l_roll_x_week4 <> l_old_roll_x_week4
2823 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2824 ) THEN
2825 --All records for this ROLLING WEEK 4
2826 --and person ids are processed. So, now determine
2827 --the buckets
2828 /*
2829 For consecutive records this processing is being done
2830 because if the last record in the counting of
2831 resource counts is 1, the previous count for consecutive
2832 availability will not get replaced with the new count.
2833 */
2834
2835 l_roll_x_wk4_res_cnt_tbl(11) := GREATEST (l_roll_x_wk4_res_cnt_tbl(6), NVL(l_roll_x_wk4_res_cnt_tbl(11),0));
2836 l_roll_x_wk4_res_cnt_tbl(12) := GREATEST (l_roll_x_wk4_res_cnt_tbl(7), NVL(l_roll_x_wk4_res_cnt_tbl(12),0));
2837 l_roll_x_wk4_res_cnt_tbl(13) := GREATEST (l_roll_x_wk4_res_cnt_tbl(8), NVL(l_roll_x_wk4_res_cnt_tbl(13),0));
2838 l_roll_x_wk4_res_cnt_tbl(14) := GREATEST (l_roll_x_wk4_res_cnt_tbl(9), NVL(l_roll_x_wk4_res_cnt_tbl(14),0));
2839 l_roll_x_wk4_res_cnt_tbl(15) := GREATEST (l_roll_x_wk4_res_cnt_tbl(10), NVL(l_roll_x_wk4_res_cnt_tbl(15),0));
2840
2841 --INSERT Records for this particular
2842 --person id and ROLLING WEEK 4 in
2843 --PJI_RM_AGGR_AVL3 table
2844
2845 PREPARE_TO_INS_INTO_AVL3
2846 (
2847 p_exp_organization_id => l_old_exp_orgnztion_id,
2848 p_exp_org_id => l_old_exp_org_id,
2849 p_person_id => l_old_person_id,
2850 p_time_id => l_old_roll_x_week4,
2851 p_curr_pd => l_roll_x_week4,
2852 p_as_of_date => l_time_id,
2853 p_pd_org_st_date => l_start_date_org_roll_x_wk4,
2854 p_period_type_id => 16,
2855 p_calendar_type => 'E',
2856 p_res_cnt_tbl => l_roll_x_wk4_res_cnt_tbl,
2857 p_run_mode => p_run_mode,
2858 p_blind_insert_flag => 'N',
2859 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2860 );
2861
2862 IF (l_roll_x_week4 = l_old_roll_x_week4) THEN
2863 l_roll_x_wk4_org_change_flag := 'Y';
2864 ELSE
2865 l_roll_x_wk4_count := 0;
2866 END IF;
2867 --After insert SET ALL count and values to 0
2868 FOR m in l_roll_x_wk4_res_cnt_tbl.FIRST.. l_roll_x_wk4_res_cnt_tbl.LAST
2869 LOOP
2870 l_roll_x_wk4_res_cnt_tbl(m) := 0;
2871 END LOOP;
2872 END IF;
2873 --Processing for cumulative records
2874 l_roll_x_wk4_res_cnt_tbl(1) := NVL(l_roll_x_wk4_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2875 l_roll_x_wk4_res_cnt_tbl(2) := NVL(l_roll_x_wk4_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2876 l_roll_x_wk4_res_cnt_tbl(3) := NVL(l_roll_x_wk4_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2877 l_roll_x_wk4_res_cnt_tbl(4) := NVL(l_roll_x_wk4_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2878 l_roll_x_wk4_res_cnt_tbl(5) := NVL(l_roll_x_wk4_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2879
2880 --Processing for consecutive records
2881 l_roll_x_wk4_res_cnt_tbl(6) := NVL(l_roll_x_wk4_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2882 l_roll_x_wk4_res_cnt_tbl(7) := NVL(l_roll_x_wk4_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2883 l_roll_x_wk4_res_cnt_tbl(8) := NVL(l_roll_x_wk4_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2884 l_roll_x_wk4_res_cnt_tbl(9) := NVL(l_roll_x_wk4_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2885 l_roll_x_wk4_res_cnt_tbl(10):= NVL(l_roll_x_wk4_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2886
2887 --Store the starting day of the period
2888 IF (l_roll_x_wk4_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2889 NULL;
2890 ELSIF (l_roll_x_wk4_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2891 l_start_date_org_roll_x_wk4 := l_time_id;
2892 ELSIF (l_roll_x_wk4_count = 0) THEN
2893 BEGIN
2894 SELECT to_char(fiit.start_date,'j')
2895 INTO l_start_date_org_roll_x_wk4
2896 FROM FII_TIME_WEEK fiit
2897 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week4 - 1) * 7) + -- Bug#4903567
2898 g_min_wk_j_st_date), 'J');
2899 EXCEPTION
2900 WHEN NO_DATA_FOUND THEN
2901 NULL;
2902 END;
2903 END IF;
2904 --Call API to compare and store consecutive counts
2905 --for the resource
2906 CALC_CS_RES_CNT_VALUE
2907 (
2908 p_res_cnt_tbl => l_roll_x_wk4_res_cnt_tbl
2909 );
2910 --Assigning current ROLLING WEEK 4 id to
2911 --old period id local variable
2912 l_old_roll_x_week4 := l_roll_x_week4;
2913 l_roll_x_wk4_count := l_roll_x_wk4_count + 1;
2914 l_roll_x_wk4_org_change_flag:= 'N';
2915 END IF;
2916 /* End of Processing for ROLLING WEEK 4 */
2917
2918 -- For ROLLING WEEK 5
2919 IF (l_roll_x_week5 > 0 AND l_roll_x_week5 IS NOT NULL) THEN
2920 IF (l_old_roll_x_week5 < 0
2921 OR l_old_person_id < 0
2922 OR l_old_exp_orgnztion_id < 0
2923 ) THEN
2924 -- Do Nothing
2925 -- This is just to make sure that nothing is
2926 -- executed for the very first time the program
2927 -- comes in the Cursor
2928 --DBMS_OUTPUT.PUT_LINE('1');
2929 NULL;
2930 ELSIF (l_roll_x_week5 <> l_old_roll_x_week5
2931 OR l_exp_organization_id <> l_old_exp_orgnztion_id
2932 ) THEN
2933 --All records for this ROLLING WEEK 5
2934 --and person ids are processed. So, now determine
2935 --the buckets
2936 /*
2937 For consecutive records this processing is being done
2938 because if the last record in the counting of
2939 resource counts is 1, the previous count for consecutive
2940 availability will not get replaced with the new count.
2941 */
2942
2943 l_roll_x_wk5_res_cnt_tbl(11) := GREATEST (l_roll_x_wk5_res_cnt_tbl(6), NVL(l_roll_x_wk5_res_cnt_tbl(11),0));
2944 l_roll_x_wk5_res_cnt_tbl(12) := GREATEST (l_roll_x_wk5_res_cnt_tbl(7), NVL(l_roll_x_wk5_res_cnt_tbl(12),0));
2945 l_roll_x_wk5_res_cnt_tbl(13) := GREATEST (l_roll_x_wk5_res_cnt_tbl(8), NVL(l_roll_x_wk5_res_cnt_tbl(13),0));
2946 l_roll_x_wk5_res_cnt_tbl(14) := GREATEST (l_roll_x_wk5_res_cnt_tbl(9), NVL(l_roll_x_wk5_res_cnt_tbl(14),0));
2947 l_roll_x_wk5_res_cnt_tbl(15) := GREATEST (l_roll_x_wk5_res_cnt_tbl(10), NVL(l_roll_x_wk5_res_cnt_tbl(15),0));
2948
2949 --INSERT Records for this particular
2950 --person id and ROLLING WEEK 5 in
2951 --PJI_RM_AGGR_AVL3 table
2952
2953 PREPARE_TO_INS_INTO_AVL3
2954 (
2955 p_exp_organization_id => l_old_exp_orgnztion_id,
2956 p_exp_org_id => l_old_exp_org_id,
2957 p_person_id => l_old_person_id,
2958 p_time_id => l_old_roll_x_week5,
2959 p_curr_pd => l_roll_x_week5,
2960 p_as_of_date => l_time_id,
2961 p_pd_org_st_date => l_start_date_org_roll_x_wk5,
2962 p_period_type_id => 16,
2963 p_calendar_type => 'E',
2964 p_res_cnt_tbl => l_roll_x_wk5_res_cnt_tbl,
2965 p_run_mode => p_run_mode,
2966 p_blind_insert_flag => 'N',
2967 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
2968 );
2969
2970 IF (l_roll_x_week5 = l_old_roll_x_week5) THEN
2971 l_roll_x_wk5_org_change_flag := 'Y';
2972 ELSE
2973 l_roll_x_wk5_count := 0;
2974 END IF;
2975 --After insert SET ALL count and values to 0
2976 FOR m in l_roll_x_wk5_res_cnt_tbl.FIRST.. l_roll_x_wk5_res_cnt_tbl.LAST
2977 LOOP
2978 l_roll_x_wk5_res_cnt_tbl(m) := 0;
2979 END LOOP;
2980 END IF;
2981 --Processing for cumulative records
2982 l_roll_x_wk5_res_cnt_tbl(1) := NVL(l_roll_x_wk5_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
2983 l_roll_x_wk5_res_cnt_tbl(2) := NVL(l_roll_x_wk5_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
2984 l_roll_x_wk5_res_cnt_tbl(3) := NVL(l_roll_x_wk5_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
2985 l_roll_x_wk5_res_cnt_tbl(4) := NVL(l_roll_x_wk5_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
2986 l_roll_x_wk5_res_cnt_tbl(5) := NVL(l_roll_x_wk5_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
2987
2988 --Processing for consecutive records
2989 l_roll_x_wk5_res_cnt_tbl(6) := NVL(l_roll_x_wk5_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
2990 l_roll_x_wk5_res_cnt_tbl(7) := NVL(l_roll_x_wk5_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
2991 l_roll_x_wk5_res_cnt_tbl(8) := NVL(l_roll_x_wk5_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
2992 l_roll_x_wk5_res_cnt_tbl(9) := NVL(l_roll_x_wk5_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
2993 l_roll_x_wk5_res_cnt_tbl(10):= NVL(l_roll_x_wk5_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
2994
2995 --Store the starting day of the period
2996 IF (l_roll_x_wk5_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
2997 NULL;
2998 ELSIF (l_roll_x_wk5_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
2999 l_start_date_org_roll_x_wk5 := l_time_id;
3000 ELSIF (l_roll_x_wk5_count = 0) THEN
3001 BEGIN
3002 SELECT to_char(fiit.start_date,'j')
3003 INTO l_start_date_org_roll_x_wk5
3004 FROM FII_TIME_WEEK fiit
3005 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week5 - 1) * 7) + -- Bug#4903567
3006 g_min_wk_j_st_date), 'J');
3007 EXCEPTION
3008 WHEN NO_DATA_FOUND THEN
3009 NULL;
3010 END;
3011 END IF;
3012 --Call API to compare and store consecutive counts
3013 --for the resource
3014 CALC_CS_RES_CNT_VALUE
3015 (
3016 p_res_cnt_tbl => l_roll_x_wk5_res_cnt_tbl
3017 );
3018 --Assigning current ROLLING WEEK 5 id to
3019 --old period id local variable
3020 l_old_roll_x_week5 := l_roll_x_week5;
3021 l_roll_x_wk5_count := l_roll_x_wk5_count + 1;
3022 l_roll_x_wk5_org_change_flag:= 'N';
3023 END IF;
3024 /* End of Processing for ROLLING WEEK 5 */
3025
3026 -- For ROLLING WEEK 6
3027 IF (l_roll_x_week6 > 0 AND l_roll_x_week6 IS NOT NULL) THEN
3028 IF (l_old_roll_x_week6 < 0
3029 OR l_old_person_id < 0
3030 OR l_old_exp_orgnztion_id < 0
3031 ) THEN
3032 -- Do Nothing
3033 -- This is just to make sure that nothing is
3034 -- executed for the very first time the program
3035 -- comes in the Cursor
3036 --DBMS_OUTPUT.PUT_LINE('1');
3037 NULL;
3038 ELSIF (l_roll_x_week6 <> l_old_roll_x_week6
3039 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3040 ) THEN
3041 --All records for this ROLLING WEEK 6
3042 --and person ids are processed. So, now determine
3043 --the buckets
3044 /*
3045 For consecutive records this processing is being done
3046 because if the last record in the counting of
3047 resource counts is 1, the previous count for consecutive
3048 availability will not get replaced with the new count.
3049 */
3050
3051 l_roll_x_wk6_res_cnt_tbl(11) := GREATEST (l_roll_x_wk6_res_cnt_tbl(6), NVL(l_roll_x_wk6_res_cnt_tbl(11),0));
3052 l_roll_x_wk6_res_cnt_tbl(12) := GREATEST (l_roll_x_wk6_res_cnt_tbl(7), NVL(l_roll_x_wk6_res_cnt_tbl(12),0));
3053 l_roll_x_wk6_res_cnt_tbl(13) := GREATEST (l_roll_x_wk6_res_cnt_tbl(8), NVL(l_roll_x_wk6_res_cnt_tbl(13),0));
3054 l_roll_x_wk6_res_cnt_tbl(14) := GREATEST (l_roll_x_wk6_res_cnt_tbl(9), NVL(l_roll_x_wk6_res_cnt_tbl(14),0));
3055 l_roll_x_wk6_res_cnt_tbl(15) := GREATEST (l_roll_x_wk6_res_cnt_tbl(10), NVL(l_roll_x_wk6_res_cnt_tbl(15),0));
3056
3057 --INSERT Records for this particular
3058 --person id and ROLLING WEEK 6 in
3059 --PJI_RM_AGGR_AVL3 table
3060
3061 PREPARE_TO_INS_INTO_AVL3
3062 (
3063 p_exp_organization_id => l_old_exp_orgnztion_id,
3064 p_exp_org_id => l_old_exp_org_id,
3065 p_person_id => l_old_person_id,
3066 p_time_id => l_old_roll_x_week6,
3067 p_curr_pd => l_roll_x_week6,
3068 p_as_of_date => l_time_id,
3069 p_pd_org_st_date => l_start_date_org_roll_x_wk6,
3070 p_period_type_id => 16,
3071 p_calendar_type => 'E',
3072 p_res_cnt_tbl => l_roll_x_wk6_res_cnt_tbl,
3073 p_run_mode => p_run_mode,
3074 p_blind_insert_flag => 'N',
3075 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3076 );
3077
3078 IF (l_roll_x_week6 = l_old_roll_x_week6) THEN
3079 l_roll_x_wk6_org_change_flag := 'Y';
3080 ELSE
3081 l_roll_x_wk6_count := 0;
3082 END IF;
3083 --After insert SET ALL count and values to 0
3084 FOR m in l_roll_x_wk6_res_cnt_tbl.FIRST.. l_roll_x_wk6_res_cnt_tbl.LAST
3085 LOOP
3086 l_roll_x_wk6_res_cnt_tbl(m) := 0;
3087 END LOOP;
3088 END IF;
3089 --Processing for cumulative records
3090 l_roll_x_wk6_res_cnt_tbl(1) := NVL(l_roll_x_wk6_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3091 l_roll_x_wk6_res_cnt_tbl(2) := NVL(l_roll_x_wk6_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3092 l_roll_x_wk6_res_cnt_tbl(3) := NVL(l_roll_x_wk6_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3093 l_roll_x_wk6_res_cnt_tbl(4) := NVL(l_roll_x_wk6_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3094 l_roll_x_wk6_res_cnt_tbl(5) := NVL(l_roll_x_wk6_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3095
3096 --Processing for consecutive records
3097 l_roll_x_wk6_res_cnt_tbl(6) := NVL(l_roll_x_wk6_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3098 l_roll_x_wk6_res_cnt_tbl(7) := NVL(l_roll_x_wk6_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3099 l_roll_x_wk6_res_cnt_tbl(8) := NVL(l_roll_x_wk6_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3100 l_roll_x_wk6_res_cnt_tbl(9) := NVL(l_roll_x_wk6_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3101 l_roll_x_wk6_res_cnt_tbl(10):= NVL(l_roll_x_wk6_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3102
3103 --Store the starting day of the period
3104 IF (l_roll_x_wk6_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3105 NULL;
3106 ELSIF (l_roll_x_wk6_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3107 l_start_date_org_roll_x_wk6 := l_time_id;
3108 ELSIF (l_roll_x_wk6_count = 0) THEN
3109 BEGIN
3110 SELECT to_char(fiit.start_date,'j')
3111 INTO l_start_date_org_roll_x_wk6
3112 FROM FII_TIME_WEEK fiit
3113 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week6 - 1) * 7) + -- Bug#4903567
3114 g_min_wk_j_st_date), 'J');
3115 EXCEPTION
3116 WHEN NO_DATA_FOUND THEN
3117 NULL;
3118 END;
3119 END IF;
3120 --Call API to compare and store consecutive counts
3121 --for the resource
3122 CALC_CS_RES_CNT_VALUE
3123 (
3124 p_res_cnt_tbl => l_roll_x_wk6_res_cnt_tbl
3125 );
3126 --Assigning current ROLLING WEEK 6 id to
3127 --old period id local variable
3128 l_old_roll_x_week6 := l_roll_x_week6;
3129 l_roll_x_wk6_count := l_roll_x_wk6_count + 1;
3130 l_roll_x_wk6_org_change_flag:= 'N';
3131 END IF;
3132 /* End of Processing for ROLLING WEEK 6 */
3133
3134 -- For ROLLING WEEK 7
3135 IF (l_roll_x_week7 > 0 AND l_roll_x_week7 IS NOT NULL) THEN
3136 IF (l_old_roll_x_week7 < 0
3137 OR l_old_person_id < 0
3138 OR l_old_exp_orgnztion_id < 0
3139 ) THEN
3140 -- Do Nothing
3141 -- This is just to make sure that nothing is
3142 -- executed for the very first time the program
3143 -- comes in the Cursor
3144 --DBMS_OUTPUT.PUT_LINE('1');
3145 NULL;
3146 ELSIF (l_roll_x_week7 <> l_old_roll_x_week7
3147 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3148 ) THEN
3149 --All records for this ROLLING WEEK 7
3150 --and person ids are processed. So, now determine
3151 --the buckets
3152 /*
3153 For consecutive records this processing is being done
3154 because if the last record in the counting of
3155 resource counts is 1, the previous count for consecutive
3156 availability will not get replaced with the new count.
3157 */
3158
3159 l_roll_x_wk7_res_cnt_tbl(11) := GREATEST (l_roll_x_wk7_res_cnt_tbl(6), NVL(l_roll_x_wk7_res_cnt_tbl(11),0));
3160 l_roll_x_wk7_res_cnt_tbl(12) := GREATEST (l_roll_x_wk7_res_cnt_tbl(7), NVL(l_roll_x_wk7_res_cnt_tbl(12),0));
3161 l_roll_x_wk7_res_cnt_tbl(13) := GREATEST (l_roll_x_wk7_res_cnt_tbl(8), NVL(l_roll_x_wk7_res_cnt_tbl(13),0));
3162 l_roll_x_wk7_res_cnt_tbl(14) := GREATEST (l_roll_x_wk7_res_cnt_tbl(9), NVL(l_roll_x_wk7_res_cnt_tbl(14),0));
3163 l_roll_x_wk7_res_cnt_tbl(15) := GREATEST (l_roll_x_wk7_res_cnt_tbl(10), NVL(l_roll_x_wk7_res_cnt_tbl(15),0));
3164
3165 --INSERT Records for this particular
3166 --person id and ROLLING WEEK 7 in
3167 --PJI_RM_AGGR_AVL3 table
3168
3169 PREPARE_TO_INS_INTO_AVL3
3170 (
3171 p_exp_organization_id => l_old_exp_orgnztion_id,
3172 p_exp_org_id => l_old_exp_org_id,
3173 p_person_id => l_old_person_id,
3174 p_time_id => l_old_roll_x_week7,
3175 p_curr_pd => l_roll_x_week7,
3176 p_as_of_date => l_time_id,
3177 p_pd_org_st_date => l_start_date_org_roll_x_wk7,
3178 p_period_type_id => 16,
3179 p_calendar_type => 'E',
3180 p_res_cnt_tbl => l_roll_x_wk7_res_cnt_tbl,
3181 p_run_mode => p_run_mode,
3182 p_blind_insert_flag => 'N',
3183 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3184 );
3185
3186 IF (l_roll_x_week7 = l_old_roll_x_week7) THEN
3187 l_roll_x_wk7_org_change_flag := 'Y';
3188 ELSE
3189 l_roll_x_wk7_count := 0;
3190 END IF;
3191 --After insert SET ALL count and values to 0
3192 FOR m in l_roll_x_wk7_res_cnt_tbl.FIRST.. l_roll_x_wk7_res_cnt_tbl.LAST
3193 LOOP
3194 l_roll_x_wk7_res_cnt_tbl(m) := 0;
3195 END LOOP;
3196 END IF;
3197 --Processing for cumulative records
3198 l_roll_x_wk7_res_cnt_tbl(1) := NVL(l_roll_x_wk7_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3199 l_roll_x_wk7_res_cnt_tbl(2) := NVL(l_roll_x_wk7_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3200 l_roll_x_wk7_res_cnt_tbl(3) := NVL(l_roll_x_wk7_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3201 l_roll_x_wk7_res_cnt_tbl(4) := NVL(l_roll_x_wk7_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3202 l_roll_x_wk7_res_cnt_tbl(5) := NVL(l_roll_x_wk7_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3203
3204 --Processing for consecutive records
3205 l_roll_x_wk7_res_cnt_tbl(6) := NVL(l_roll_x_wk7_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3206 l_roll_x_wk7_res_cnt_tbl(7) := NVL(l_roll_x_wk7_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3207 l_roll_x_wk7_res_cnt_tbl(8) := NVL(l_roll_x_wk7_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3208 l_roll_x_wk7_res_cnt_tbl(9) := NVL(l_roll_x_wk7_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3209 l_roll_x_wk7_res_cnt_tbl(10):= NVL(l_roll_x_wk7_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3210
3211 --Store the starting day of the period
3212 IF (l_roll_x_wk7_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3213 NULL;
3214 ELSIF (l_roll_x_wk7_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3215 l_start_date_org_roll_x_wk7 := l_time_id;
3216 ELSIF (l_roll_x_wk7_count = 0) THEN
3217 BEGIN
3218 SELECT to_char(fiit.start_date,'j')
3219 INTO l_start_date_org_roll_x_wk7
3220 FROM FII_TIME_WEEK fiit
3221 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week7 - 1) * 7) + -- Bug#4903567
3222 g_min_wk_j_st_date), 'J');
3223 EXCEPTION
3224 WHEN NO_DATA_FOUND THEN
3225 NULL;
3226 END;
3227 END IF;
3228 --Call API to compare and store consecutive counts
3229 --for the resource
3230 CALC_CS_RES_CNT_VALUE
3231 (
3232 p_res_cnt_tbl => l_roll_x_wk7_res_cnt_tbl
3233 );
3234 --Assigning current ROLLING WEEK 7 id to
3235 --old period id local variable
3236 l_old_roll_x_week7 := l_roll_x_week7;
3237 l_roll_x_wk7_count := l_roll_x_wk7_count + 1;
3238 l_roll_x_wk7_org_change_flag:= 'N';
3239 END IF;
3240 /* End of Processing for ROLLING WEEK 7 */
3241
3242 -- For ROLLING WEEK 8
3243 IF (l_roll_x_week8 > 0 AND l_roll_x_week8 IS NOT NULL) THEN
3244 IF (l_old_roll_x_week8 < 0
3245 OR l_old_person_id < 0
3246 OR l_old_exp_orgnztion_id < 0
3247 ) THEN
3248 -- Do Nothing
3249 -- This is just to make sure that nothing is
3250 -- executed for the very first time the program
3251 -- comes in the Cursor
3252 --DBMS_OUTPUT.PUT_LINE('1');
3253 NULL;
3254 ELSIF (l_roll_x_week8 <> l_old_roll_x_week8
3255 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3256 ) THEN
3257 --All records for this ROLLING WEEK 8
3258 --and person ids are processed. So, now determine
3259 --the buckets
3260 /*
3261 For consecutive records this processing is being done
3262 because if the last record in the counting of
3263 resource counts is 1, the previous count for consecutive
3264 availability will not get replaced with the new count.
3265 */
3266
3267 l_roll_x_wk8_res_cnt_tbl(11) := GREATEST (l_roll_x_wk8_res_cnt_tbl(6), NVL(l_roll_x_wk8_res_cnt_tbl(11),0));
3268 l_roll_x_wk8_res_cnt_tbl(12) := GREATEST (l_roll_x_wk8_res_cnt_tbl(7), NVL(l_roll_x_wk8_res_cnt_tbl(12),0));
3269 l_roll_x_wk8_res_cnt_tbl(13) := GREATEST (l_roll_x_wk8_res_cnt_tbl(8), NVL(l_roll_x_wk8_res_cnt_tbl(13),0));
3270 l_roll_x_wk8_res_cnt_tbl(14) := GREATEST (l_roll_x_wk8_res_cnt_tbl(9), NVL(l_roll_x_wk8_res_cnt_tbl(14),0));
3271 l_roll_x_wk8_res_cnt_tbl(15) := GREATEST (l_roll_x_wk8_res_cnt_tbl(10), NVL(l_roll_x_wk8_res_cnt_tbl(15),0));
3272
3273 --INSERT Records for this particular
3274 --person id and ROLLING WEEK 8 in
3275 --PJI_RM_AGGR_AVL3 table
3276
3277 PREPARE_TO_INS_INTO_AVL3
3278 (
3279 p_exp_organization_id => l_old_exp_orgnztion_id,
3280 p_exp_org_id => l_old_exp_org_id,
3281 p_person_id => l_old_person_id,
3282 p_time_id => l_old_roll_x_week8,
3283 p_curr_pd => l_roll_x_week8,
3284 p_as_of_date => l_time_id,
3285 p_pd_org_st_date => l_start_date_org_roll_x_wk8,
3286 p_period_type_id => 16,
3287 p_calendar_type => 'E',
3288 p_res_cnt_tbl => l_roll_x_wk8_res_cnt_tbl,
3289 p_run_mode => p_run_mode,
3290 p_blind_insert_flag => 'N',
3291 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3292 );
3293
3294 IF (l_roll_x_week8 = l_old_roll_x_week8) THEN
3295 l_roll_x_wk8_org_change_flag := 'Y';
3296 ELSE
3297 l_roll_x_wk8_count := 0;
3298 END IF;
3299 --After insert SET ALL count and values to 0
3300 FOR m in l_roll_x_wk8_res_cnt_tbl.FIRST.. l_roll_x_wk8_res_cnt_tbl.LAST
3301 LOOP
3302 l_roll_x_wk8_res_cnt_tbl(m) := 0;
3303 END LOOP;
3304 END IF;
3305 --Processing for cumulative records
3306 l_roll_x_wk8_res_cnt_tbl(1) := NVL(l_roll_x_wk8_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3307 l_roll_x_wk8_res_cnt_tbl(2) := NVL(l_roll_x_wk8_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3308 l_roll_x_wk8_res_cnt_tbl(3) := NVL(l_roll_x_wk8_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3309 l_roll_x_wk8_res_cnt_tbl(4) := NVL(l_roll_x_wk8_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3310 l_roll_x_wk8_res_cnt_tbl(5) := NVL(l_roll_x_wk8_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3311
3312 --Processing for consecutive records
3313 l_roll_x_wk8_res_cnt_tbl(6) := NVL(l_roll_x_wk8_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3314 l_roll_x_wk8_res_cnt_tbl(7) := NVL(l_roll_x_wk8_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3315 l_roll_x_wk8_res_cnt_tbl(8) := NVL(l_roll_x_wk8_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3316 l_roll_x_wk8_res_cnt_tbl(9) := NVL(l_roll_x_wk8_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3317 l_roll_x_wk8_res_cnt_tbl(10):= NVL(l_roll_x_wk8_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3318
3319 --Store the starting day of the period
3320 IF (l_roll_x_wk8_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3321 NULL;
3322 ELSIF (l_roll_x_wk8_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3323 l_start_date_org_roll_x_wk8 := l_time_id;
3324 ELSIF (l_roll_x_wk8_count = 0) THEN
3325 BEGIN
3326 SELECT to_char(fiit.start_date,'j')
3327 INTO l_start_date_org_roll_x_wk8
3328 FROM FII_TIME_WEEK fiit
3329 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week8 - 1) * 7) + -- Bug#4903567
3330 g_min_wk_j_st_date), 'J');
3331 EXCEPTION
3332 WHEN NO_DATA_FOUND THEN
3333 NULL;
3334 END;
3335 END IF;
3336 --Call API to compare and store consecutive counts
3337 --for the resource
3338 CALC_CS_RES_CNT_VALUE
3339 (
3340 p_res_cnt_tbl => l_roll_x_wk8_res_cnt_tbl
3341 );
3342 --Assigning current ROLLING WEEK 8 id to
3343 --old period id local variable
3344 l_old_roll_x_week8 := l_roll_x_week8;
3345 l_roll_x_wk8_count := l_roll_x_wk8_count + 1;
3346 l_roll_x_wk8_org_change_flag:= 'N';
3347 END IF;
3348 /* End of Processing for ROLLING WEEK 8 */
3349
3350 -- For ROLLING WEEK 9
3351 IF (l_roll_x_week9 > 0 AND l_roll_x_week9 IS NOT NULL) THEN
3352 IF (l_old_roll_x_week9 < 0
3353 OR l_old_person_id < 0
3354 OR l_old_exp_orgnztion_id < 0
3355 ) THEN
3356 -- Do Nothing
3357 -- This is just to make sure that nothing is
3358 -- executed for the very first time the program
3359 -- comes in the Cursor
3360 --DBMS_OUTPUT.PUT_LINE('1');
3361 NULL;
3362 ELSIF (l_roll_x_week9 <> l_old_roll_x_week9
3363 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3364 ) THEN
3365 --All records for this ROLLING WEEK 9
3366 --and person ids are processed. So, now determine
3367 --the buckets
3368 /*
3369 For consecutive records this processing is being done
3370 because if the last record in the counting of
3371 resource counts is 1, the previous count for consecutive
3372 availability will not get replaced with the new count.
3373 */
3374
3375 l_roll_x_wk9_res_cnt_tbl(11) := GREATEST (l_roll_x_wk9_res_cnt_tbl(6), NVL(l_roll_x_wk9_res_cnt_tbl(11),0));
3376 l_roll_x_wk9_res_cnt_tbl(12) := GREATEST (l_roll_x_wk9_res_cnt_tbl(7), NVL(l_roll_x_wk9_res_cnt_tbl(12),0));
3377 l_roll_x_wk9_res_cnt_tbl(13) := GREATEST (l_roll_x_wk9_res_cnt_tbl(8), NVL(l_roll_x_wk9_res_cnt_tbl(13),0));
3378 l_roll_x_wk9_res_cnt_tbl(14) := GREATEST (l_roll_x_wk9_res_cnt_tbl(9), NVL(l_roll_x_wk9_res_cnt_tbl(14),0));
3379 l_roll_x_wk9_res_cnt_tbl(15) := GREATEST (l_roll_x_wk9_res_cnt_tbl(10), NVL(l_roll_x_wk9_res_cnt_tbl(15),0));
3380
3381 --INSERT Records for this particular
3382 --person id and ROLLING WEEK 9 in
3383 --PJI_RM_AGGR_AVL3 table
3384
3385 PREPARE_TO_INS_INTO_AVL3
3386 (
3387 p_exp_organization_id => l_old_exp_orgnztion_id,
3388 p_exp_org_id => l_old_exp_org_id,
3389 p_person_id => l_old_person_id,
3390 p_time_id => l_old_roll_x_week9,
3391 p_curr_pd => l_roll_x_week9,
3392 p_as_of_date => l_time_id,
3393 p_pd_org_st_date => l_start_date_org_roll_x_wk9,
3394 p_period_type_id => 16,
3395 p_calendar_type => 'E',
3396 p_res_cnt_tbl => l_roll_x_wk9_res_cnt_tbl,
3397 p_run_mode => p_run_mode,
3398 p_blind_insert_flag => 'N',
3399 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3400 );
3401
3402 IF (l_roll_x_week9 = l_old_roll_x_week9) THEN
3403 l_roll_x_wk9_org_change_flag := 'Y';
3404 ELSE
3405 l_roll_x_wk9_count := 0;
3406 END IF;
3407 --After insert SET ALL count and values to 0
3408 FOR m in l_roll_x_wk9_res_cnt_tbl.FIRST.. l_roll_x_wk9_res_cnt_tbl.LAST
3409 LOOP
3410 l_roll_x_wk9_res_cnt_tbl(m) := 0;
3411 END LOOP;
3412 END IF;
3413 --Processing for cumulative records
3414 l_roll_x_wk9_res_cnt_tbl(1) := NVL(l_roll_x_wk9_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3415 l_roll_x_wk9_res_cnt_tbl(2) := NVL(l_roll_x_wk9_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3416 l_roll_x_wk9_res_cnt_tbl(3) := NVL(l_roll_x_wk9_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3417 l_roll_x_wk9_res_cnt_tbl(4) := NVL(l_roll_x_wk9_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3418 l_roll_x_wk9_res_cnt_tbl(5) := NVL(l_roll_x_wk9_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3419
3420 --Processing for consecutive records
3421 l_roll_x_wk9_res_cnt_tbl(6) := NVL(l_roll_x_wk9_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3422 l_roll_x_wk9_res_cnt_tbl(7) := NVL(l_roll_x_wk9_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3423 l_roll_x_wk9_res_cnt_tbl(8) := NVL(l_roll_x_wk9_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3424 l_roll_x_wk9_res_cnt_tbl(9) := NVL(l_roll_x_wk9_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3425 l_roll_x_wk9_res_cnt_tbl(10):= NVL(l_roll_x_wk9_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3426
3427 --Store the starting day of the period
3428 IF (l_roll_x_wk9_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3429 NULL;
3430 ELSIF (l_roll_x_wk9_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3431 l_start_date_org_roll_x_wk9 := l_time_id;
3432 ELSIF (l_roll_x_wk9_count = 0) THEN
3433 BEGIN
3434 SELECT to_char(fiit.start_date,'j')
3435 INTO l_start_date_org_roll_x_wk9
3436 FROM FII_TIME_WEEK fiit
3437 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week9 - 1) * 7) + -- Bug#4903567
3438 g_min_wk_j_st_date), 'J');
3439 EXCEPTION
3440 WHEN NO_DATA_FOUND THEN
3441 NULL;
3442 END;
3443 END IF;
3444 --Call API to compare and store consecutive counts
3445 --for the resource
3446 CALC_CS_RES_CNT_VALUE
3447 (
3448 p_res_cnt_tbl => l_roll_x_wk9_res_cnt_tbl
3449 );
3450 --Assigning current ROLLING WEEK 9 id to
3451 --old period id local variable
3452 l_old_roll_x_week9 := l_roll_x_week9;
3453 l_roll_x_wk9_count := l_roll_x_wk9_count + 1;
3454 l_roll_x_wk9_org_change_flag:= 'N';
3455 END IF;
3456 /* End of Processing for ROLLING WEEK 9 */
3457
3458 -- For ROLLING WEEK 10
3459 IF (l_roll_x_week10 > 0 AND l_roll_x_week10 IS NOT NULL) THEN
3460 IF (l_old_roll_x_week10 < 0
3461 OR l_old_person_id < 0
3462 OR l_old_exp_orgnztion_id < 0
3463 ) THEN
3464 -- Do Nothing
3465 -- This is just to make sure that nothing is
3466 -- executed for the very first time the program
3467 -- comes in the Cursor
3468 --DBMS_OUTPUT.PUT_LINE('1');
3469 NULL;
3470 ELSIF (l_roll_x_week10 <> l_old_roll_x_week10
3471 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3472 ) THEN
3473 --All records for this ROLLING WEEK 10
3474 --and person ids are processed. So, now determine
3475 --the buckets
3476 /*
3477 For consecutive records this processing is being done
3478 because if the last record in the counting of
3479 resource counts is 1, the previous count for consecutive
3480 availability will not get replaced with the new count.
3481 */
3482
3483 l_roll_x_wk10_res_cnt_tbl(11) := GREATEST (l_roll_x_wk10_res_cnt_tbl(6), NVL(l_roll_x_wk10_res_cnt_tbl(11),0));
3484 l_roll_x_wk10_res_cnt_tbl(12) := GREATEST (l_roll_x_wk10_res_cnt_tbl(7), NVL(l_roll_x_wk10_res_cnt_tbl(12),0));
3485 l_roll_x_wk10_res_cnt_tbl(13) := GREATEST (l_roll_x_wk10_res_cnt_tbl(8), NVL(l_roll_x_wk10_res_cnt_tbl(13),0));
3486 l_roll_x_wk10_res_cnt_tbl(14) := GREATEST (l_roll_x_wk10_res_cnt_tbl(9), NVL(l_roll_x_wk10_res_cnt_tbl(14),0));
3487 l_roll_x_wk10_res_cnt_tbl(15) := GREATEST (l_roll_x_wk10_res_cnt_tbl(10), NVL(l_roll_x_wk10_res_cnt_tbl(15),0));
3488
3489 --INSERT Records for this particular
3490 --person id and ROLLING WEEK 10 in
3491 --PJI_RM_AGGR_AVL3 table
3492
3493 PREPARE_TO_INS_INTO_AVL3
3494 (
3495 p_exp_organization_id => l_old_exp_orgnztion_id,
3496 p_exp_org_id => l_old_exp_org_id,
3497 p_person_id => l_old_person_id,
3498 p_time_id => l_old_roll_x_week10,
3499 p_curr_pd => l_roll_x_week10,
3500 p_as_of_date => l_time_id,
3501 p_pd_org_st_date => l_start_date_org_roll_x_wk10,
3502 p_period_type_id => 16,
3503 p_calendar_type => 'E',
3504 p_res_cnt_tbl => l_roll_x_wk10_res_cnt_tbl,
3505 p_run_mode => p_run_mode,
3506 p_blind_insert_flag => 'N',
3507 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3508 );
3509
3510 IF (l_roll_x_week10 = l_old_roll_x_week10) THEN
3511 l_roll_x_wk10_org_change_flag := 'Y';
3512 ELSE
3513 l_roll_x_wk10_count := 0;
3514 END IF;
3515 --After insert SET ALL count and values to 0
3516 FOR m in l_roll_x_wk10_res_cnt_tbl.FIRST.. l_roll_x_wk10_res_cnt_tbl.LAST
3517 LOOP
3518 l_roll_x_wk10_res_cnt_tbl(m) := 0;
3519 END LOOP;
3520 END IF;
3521 --Processing for cumulative records
3522 l_roll_x_wk10_res_cnt_tbl(1) := NVL(l_roll_x_wk10_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3523 l_roll_x_wk10_res_cnt_tbl(2) := NVL(l_roll_x_wk10_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3524 l_roll_x_wk10_res_cnt_tbl(3) := NVL(l_roll_x_wk10_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3525 l_roll_x_wk10_res_cnt_tbl(4) := NVL(l_roll_x_wk10_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3526 l_roll_x_wk10_res_cnt_tbl(5) := NVL(l_roll_x_wk10_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3527
3528 --Processing for consecutive records
3529 l_roll_x_wk10_res_cnt_tbl(6) := NVL(l_roll_x_wk10_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3530 l_roll_x_wk10_res_cnt_tbl(7) := NVL(l_roll_x_wk10_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3531 l_roll_x_wk10_res_cnt_tbl(8) := NVL(l_roll_x_wk10_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3532 l_roll_x_wk10_res_cnt_tbl(9) := NVL(l_roll_x_wk10_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3533 l_roll_x_wk10_res_cnt_tbl(10):= NVL(l_roll_x_wk10_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3534
3535 --Store the starting day of the period
3536 IF (l_roll_x_wk10_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3537 NULL;
3538 ELSIF (l_roll_x_wk10_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3539 l_start_date_org_roll_x_wk10 := l_time_id;
3540 ELSIF (l_roll_x_wk10_count = 0) THEN
3541 BEGIN
3542 SELECT to_char(fiit.start_date,'j')
3543 INTO l_start_date_org_roll_x_wk10
3544 FROM FII_TIME_WEEK fiit
3545 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week10 - 1) * 7) + -- Bug#4903567
3546 g_min_wk_j_st_date), 'J');
3547 EXCEPTION
3548 WHEN NO_DATA_FOUND THEN
3549 NULL;
3550 END;
3551 END IF;
3552 --Call API to compare and store consecutive counts
3553 --for the resource
3554 CALC_CS_RES_CNT_VALUE
3555 (
3556 p_res_cnt_tbl => l_roll_x_wk10_res_cnt_tbl
3557 );
3558 --Assigning current ROLLING WEEK 10 id to
3559 --old period id local variable
3560 l_old_roll_x_week10 := l_roll_x_week10;
3561 l_roll_x_wk10_count := l_roll_x_wk10_count + 1;
3562 l_roll_x_wk10_org_change_flag:= 'N';
3563 END IF;
3564 /* End of Processing for ROLLING WEEK 10 */
3565
3566 -- For ROLLING WEEK 11
3567 IF (l_roll_x_week11 > 0 AND l_roll_x_week11 IS NOT NULL) THEN
3568 IF (l_old_roll_x_week11 < 0
3569 OR l_old_person_id < 0
3570 OR l_old_exp_orgnztion_id < 0
3571 ) THEN
3572 -- Do Nothing
3573 -- This is just to make sure that nothing is
3574 -- executed for the very first time the program
3575 -- comes in the Cursor
3576 --DBMS_OUTPUT.PUT_LINE('1');
3577 NULL;
3578 ELSIF (l_roll_x_week11 <> l_old_roll_x_week11
3579 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3580 ) THEN
3581 --All records for this ROLLING WEEK 11
3582 --and person ids are processed. So, now determine
3583 --the buckets
3584 /*
3585 For consecutive records this processing is being done
3586 because if the last record in the counting of
3587 resource counts is 1, the previous count for consecutive
3588 availability will not get replaced with the new count.
3589 */
3590
3591 l_roll_x_wk11_res_cnt_tbl(11) := GREATEST (l_roll_x_wk11_res_cnt_tbl(6), NVL(l_roll_x_wk11_res_cnt_tbl(11),0));
3592 l_roll_x_wk11_res_cnt_tbl(12) := GREATEST (l_roll_x_wk11_res_cnt_tbl(7), NVL(l_roll_x_wk11_res_cnt_tbl(12),0));
3593 l_roll_x_wk11_res_cnt_tbl(13) := GREATEST (l_roll_x_wk11_res_cnt_tbl(8), NVL(l_roll_x_wk11_res_cnt_tbl(13),0));
3594 l_roll_x_wk11_res_cnt_tbl(14) := GREATEST (l_roll_x_wk11_res_cnt_tbl(9), NVL(l_roll_x_wk11_res_cnt_tbl(14),0));
3595 l_roll_x_wk11_res_cnt_tbl(15) := GREATEST (l_roll_x_wk11_res_cnt_tbl(10), NVL(l_roll_x_wk11_res_cnt_tbl(15),0));
3596
3597 --INSERT Records for this particular
3598 --person id and ROLLING WEEK 11 in
3599 --PJI_RM_AGGR_AVL3 table
3600
3601 PREPARE_TO_INS_INTO_AVL3
3602 (
3603 p_exp_organization_id => l_old_exp_orgnztion_id,
3604 p_exp_org_id => l_old_exp_org_id,
3605 p_person_id => l_old_person_id,
3606 p_time_id => l_old_roll_x_week11,
3607 p_curr_pd => l_roll_x_week11,
3608 p_as_of_date => l_time_id,
3609 p_pd_org_st_date => l_start_date_org_roll_x_wk11,
3610 p_period_type_id => 16,
3611 p_calendar_type => 'E',
3612 p_res_cnt_tbl => l_roll_x_wk11_res_cnt_tbl,
3613 p_run_mode => p_run_mode,
3614 p_blind_insert_flag => 'N',
3615 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3616 );
3617
3618 IF (l_roll_x_week11 = l_old_roll_x_week11) THEN
3619 l_roll_x_wk11_org_change_flag := 'Y';
3620 ELSE
3621 l_roll_x_wk11_count := 0;
3622 END IF;
3623 --After insert SET ALL count and values to 0
3624 FOR m in l_roll_x_wk11_res_cnt_tbl.FIRST.. l_roll_x_wk11_res_cnt_tbl.LAST
3625 LOOP
3626 l_roll_x_wk11_res_cnt_tbl(m) := 0;
3627 END LOOP;
3628 END IF;
3629 --Processing for cumulative records
3630 l_roll_x_wk11_res_cnt_tbl(1) := NVL(l_roll_x_wk11_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3631 l_roll_x_wk11_res_cnt_tbl(2) := NVL(l_roll_x_wk11_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3632 l_roll_x_wk11_res_cnt_tbl(3) := NVL(l_roll_x_wk11_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3633 l_roll_x_wk11_res_cnt_tbl(4) := NVL(l_roll_x_wk11_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3634 l_roll_x_wk11_res_cnt_tbl(5) := NVL(l_roll_x_wk11_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3635
3636 --Processing for consecutive records
3637 l_roll_x_wk11_res_cnt_tbl(6) := NVL(l_roll_x_wk11_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3638 l_roll_x_wk11_res_cnt_tbl(7) := NVL(l_roll_x_wk11_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3639 l_roll_x_wk11_res_cnt_tbl(8) := NVL(l_roll_x_wk11_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3640 l_roll_x_wk11_res_cnt_tbl(9) := NVL(l_roll_x_wk11_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3641 l_roll_x_wk11_res_cnt_tbl(10):= NVL(l_roll_x_wk11_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3642
3643 --Store the starting day of the period
3644 IF (l_roll_x_wk11_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3645 NULL;
3646 ELSIF (l_roll_x_wk11_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3647 l_start_date_org_roll_x_wk11 := l_time_id;
3648 ELSIF (l_roll_x_wk11_count = 0) THEN
3649 BEGIN
3650 SELECT to_char(fiit.start_date,'j')
3651 INTO l_start_date_org_roll_x_wk11
3652 FROM FII_TIME_WEEK fiit
3653 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week11 - 1) * 7) + -- Bug#4903567
3654 g_min_wk_j_st_date), 'J');
3655 EXCEPTION
3656 WHEN NO_DATA_FOUND THEN
3657 NULL;
3658 END;
3659 END IF;
3660 --Call API to compare and store consecutive counts
3661 --for the resource
3662 CALC_CS_RES_CNT_VALUE
3663 (
3664 p_res_cnt_tbl => l_roll_x_wk11_res_cnt_tbl
3665 );
3666 --Assigning current ROLLING WEEK 11 id to
3667 --old period id local variable
3668 l_old_roll_x_week11 := l_roll_x_week11;
3669 l_roll_x_wk11_count := l_roll_x_wk11_count + 1;
3670 l_roll_x_wk11_org_change_flag:= 'N';
3671 END IF;
3672 /* End of Processing for ROLLING WEEK 11 */
3673
3674 -- For ROLLING WEEK 12
3675 IF (l_roll_x_week12 > 0 AND l_roll_x_week12 IS NOT NULL) THEN
3676 IF (l_old_roll_x_week12 < 0
3677 OR l_old_person_id < 0
3678 OR l_old_exp_orgnztion_id < 0
3679 ) THEN
3680 -- Do Nothing
3681 -- This is just to make sure that nothing is
3682 -- executed for the very first time the program
3683 -- comes in the Cursor
3684 --DBMS_OUTPUT.PUT_LINE('1');
3685 NULL;
3686 ELSIF (l_roll_x_week12 <> l_old_roll_x_week12
3687 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3688 ) THEN
3689 --All records for this ROLLING WEEK 12
3690 --and person ids are processed. So, now determine
3691 --the buckets
3692 /*
3693 For consecutive records this processing is being done
3694 because if the last record in the counting of
3695 resource counts is 1, the previous count for consecutive
3696 availability will not get replaced with the new count.
3697 */
3698
3699 l_roll_x_wk12_res_cnt_tbl(11) := GREATEST (l_roll_x_wk12_res_cnt_tbl(6), NVL(l_roll_x_wk12_res_cnt_tbl(11),0));
3700 l_roll_x_wk12_res_cnt_tbl(12) := GREATEST (l_roll_x_wk12_res_cnt_tbl(7), NVL(l_roll_x_wk12_res_cnt_tbl(12),0));
3701 l_roll_x_wk12_res_cnt_tbl(13) := GREATEST (l_roll_x_wk12_res_cnt_tbl(8), NVL(l_roll_x_wk12_res_cnt_tbl(13),0));
3702 l_roll_x_wk12_res_cnt_tbl(14) := GREATEST (l_roll_x_wk12_res_cnt_tbl(9), NVL(l_roll_x_wk12_res_cnt_tbl(14),0));
3703 l_roll_x_wk12_res_cnt_tbl(15) := GREATEST (l_roll_x_wk12_res_cnt_tbl(10), NVL(l_roll_x_wk12_res_cnt_tbl(15),0));
3704
3705 --INSERT Records for this particular
3706 --person id and ROLLING WEEK 12 in
3707 --PJI_RM_AGGR_AVL3 table
3708
3709 PREPARE_TO_INS_INTO_AVL3
3710 (
3711 p_exp_organization_id => l_old_exp_orgnztion_id,
3712 p_exp_org_id => l_old_exp_org_id,
3713 p_person_id => l_old_person_id,
3714 p_time_id => l_old_roll_x_week12,
3715 p_curr_pd => l_roll_x_week12,
3716 p_as_of_date => l_time_id,
3717 p_pd_org_st_date => l_start_date_org_roll_x_wk12,
3718 p_period_type_id => 16,
3719 p_calendar_type => 'E',
3720 p_res_cnt_tbl => l_roll_x_wk12_res_cnt_tbl,
3721 p_run_mode => p_run_mode,
3722 p_blind_insert_flag => 'N',
3723 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3724 );
3725
3726 IF (l_roll_x_week12 = l_old_roll_x_week12) THEN
3727 l_roll_x_wk12_org_change_flag := 'Y';
3728 ELSE
3729 l_roll_x_wk12_count := 0;
3730 END IF;
3731 --After insert SET ALL count and values to 0
3732 FOR m in l_roll_x_wk12_res_cnt_tbl.FIRST.. l_roll_x_wk12_res_cnt_tbl.LAST
3733 LOOP
3734 l_roll_x_wk12_res_cnt_tbl(m) := 0;
3735 END LOOP;
3736 END IF;
3737 --Processing for cumulative records
3738 l_roll_x_wk12_res_cnt_tbl(1) := NVL(l_roll_x_wk12_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3739 l_roll_x_wk12_res_cnt_tbl(2) := NVL(l_roll_x_wk12_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3740 l_roll_x_wk12_res_cnt_tbl(3) := NVL(l_roll_x_wk12_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3741 l_roll_x_wk12_res_cnt_tbl(4) := NVL(l_roll_x_wk12_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3742 l_roll_x_wk12_res_cnt_tbl(5) := NVL(l_roll_x_wk12_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3743
3744 --Processing for consecutive records
3745 l_roll_x_wk12_res_cnt_tbl(6) := NVL(l_roll_x_wk12_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3746 l_roll_x_wk12_res_cnt_tbl(7) := NVL(l_roll_x_wk12_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3747 l_roll_x_wk12_res_cnt_tbl(8) := NVL(l_roll_x_wk12_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3748 l_roll_x_wk12_res_cnt_tbl(9) := NVL(l_roll_x_wk12_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3749 l_roll_x_wk12_res_cnt_tbl(10):= NVL(l_roll_x_wk12_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3750
3751 --Store the starting day of the period
3752 IF (l_roll_x_wk12_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3753 NULL;
3754 ELSIF (l_roll_x_wk12_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3755 l_start_date_org_roll_x_wk12 := l_time_id;
3756 ELSIF (l_roll_x_wk12_count = 0) THEN
3757 BEGIN
3758 SELECT to_char(fiit.start_date,'j')
3759 INTO l_start_date_org_roll_x_wk12
3760 FROM FII_TIME_WEEK fiit
3761 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week12 - 1) * 7) + -- Bug#4903567
3762 g_min_wk_j_st_date), 'J');
3763 EXCEPTION
3764 WHEN NO_DATA_FOUND THEN
3765 NULL;
3766 END;
3767 END IF;
3768 --Call API to compare and store consecutive counts
3769 --for the resource
3770 CALC_CS_RES_CNT_VALUE
3771 (
3772 p_res_cnt_tbl => l_roll_x_wk12_res_cnt_tbl
3773 );
3774 --Assigning current ROLLING WEEK 12 id to
3775 --old period id local variable
3776 l_old_roll_x_week12 := l_roll_x_week12;
3777 l_roll_x_wk12_count := l_roll_x_wk12_count + 1;
3778 l_roll_x_wk12_org_change_flag:= 'N';
3779 END IF;
3780 /* End of Processing for ROLLING WEEK 12 */
3781
3782 -- For ROLLING WEEK 13
3783 IF (l_roll_x_week13 > 0 AND l_roll_x_week13 IS NOT NULL) THEN
3784 IF (l_old_roll_x_week13 < 0
3785 OR l_old_person_id < 0
3786 OR l_old_exp_orgnztion_id < 0
3787 ) THEN
3788 -- Do Nothing
3789 -- This is just to make sure that nothing is
3790 -- executed for the very first time the program
3791 -- comes in the Cursor
3792 --DBMS_OUTPUT.PUT_LINE('1');
3793 NULL;
3794 ELSIF (l_roll_x_week13 <> l_old_roll_x_week13
3795 OR l_exp_organization_id <> l_old_exp_orgnztion_id
3796 ) THEN
3797 --All records for this ROLLING WEEK 13
3798 --and person ids are processed. So, now determine
3799 --the buckets
3800 /*
3801 For consecutive records this processing is being done
3802 because if the last record in the counting of
3803 resource counts is 1, the previous count for consecutive
3804 availability will not get replaced with the new count.
3805 */
3806
3807 l_roll_x_wk13_res_cnt_tbl(11) := GREATEST (l_roll_x_wk13_res_cnt_tbl(6), NVL(l_roll_x_wk13_res_cnt_tbl(11),0));
3808 l_roll_x_wk13_res_cnt_tbl(12) := GREATEST (l_roll_x_wk13_res_cnt_tbl(7), NVL(l_roll_x_wk13_res_cnt_tbl(12),0));
3809 l_roll_x_wk13_res_cnt_tbl(13) := GREATEST (l_roll_x_wk13_res_cnt_tbl(8), NVL(l_roll_x_wk13_res_cnt_tbl(13),0));
3810 l_roll_x_wk13_res_cnt_tbl(14) := GREATEST (l_roll_x_wk13_res_cnt_tbl(9), NVL(l_roll_x_wk13_res_cnt_tbl(14),0));
3811 l_roll_x_wk13_res_cnt_tbl(15) := GREATEST (l_roll_x_wk13_res_cnt_tbl(10), NVL(l_roll_x_wk13_res_cnt_tbl(15),0));
3812
3813 --INSERT Records for this particular
3814 --person id and ROLLING WEEK 13 in
3815 --PJI_RM_AGGR_AVL3 table
3816
3817 PREPARE_TO_INS_INTO_AVL3
3818 (
3819 p_exp_organization_id => l_old_exp_orgnztion_id,
3820 p_exp_org_id => l_old_exp_org_id,
3821 p_person_id => l_old_person_id,
3822 p_time_id => l_old_roll_x_week13,
3823 p_curr_pd => l_roll_x_week13,
3824 p_as_of_date => l_time_id,
3825 p_pd_org_st_date => l_start_date_org_roll_x_wk13,
3826 p_period_type_id => 16,
3827 p_calendar_type => 'E',
3828 p_res_cnt_tbl => l_roll_x_wk13_res_cnt_tbl,
3829 p_run_mode => p_run_mode,
3830 p_blind_insert_flag => 'N',
3831 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3832 );
3833
3834 IF (l_roll_x_week13 = l_old_roll_x_week13) THEN
3835 l_roll_x_wk13_org_change_flag := 'Y';
3836 ELSE
3837 l_roll_x_wk13_count := 0;
3838 END IF;
3839 --After insert SET ALL count and values to 0
3840 FOR m in l_roll_x_wk13_res_cnt_tbl.FIRST.. l_roll_x_wk13_res_cnt_tbl.LAST
3841 LOOP
3842 l_roll_x_wk13_res_cnt_tbl(m) := 0;
3843 END LOOP;
3844 END IF;
3845 --Processing for cumulative records
3846 l_roll_x_wk13_res_cnt_tbl(1) := NVL(l_roll_x_wk13_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3847 l_roll_x_wk13_res_cnt_tbl(2) := NVL(l_roll_x_wk13_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3848 l_roll_x_wk13_res_cnt_tbl(3) := NVL(l_roll_x_wk13_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3849 l_roll_x_wk13_res_cnt_tbl(4) := NVL(l_roll_x_wk13_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3850 l_roll_x_wk13_res_cnt_tbl(5) := NVL(l_roll_x_wk13_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3851
3852 --Processing for consecutive records
3853 l_roll_x_wk13_res_cnt_tbl(6) := NVL(l_roll_x_wk13_res_cnt_tbl(6),0) + g_avl_res_cnt_1;
3854 l_roll_x_wk13_res_cnt_tbl(7) := NVL(l_roll_x_wk13_res_cnt_tbl(7),0) + g_avl_res_cnt_2;
3855 l_roll_x_wk13_res_cnt_tbl(8) := NVL(l_roll_x_wk13_res_cnt_tbl(8),0) + g_avl_res_cnt_3;
3856 l_roll_x_wk13_res_cnt_tbl(9) := NVL(l_roll_x_wk13_res_cnt_tbl(9),0) + g_avl_res_cnt_4;
3857 l_roll_x_wk13_res_cnt_tbl(10):= NVL(l_roll_x_wk13_res_cnt_tbl(10),0) + g_avl_res_cnt_5;
3858
3859 --Store the starting day of the period
3860 IF (l_roll_x_wk13_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3861 NULL;
3862 ELSIF (l_roll_x_wk13_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3863 l_start_date_org_roll_x_wk13 := l_time_id;
3864 ELSIF (l_roll_x_wk13_count = 0) THEN
3865 BEGIN
3866 SELECT to_char(fiit.start_date,'j')
3867 INTO l_start_date_org_roll_x_wk13
3868 FROM FII_TIME_WEEK fiit
3869 WHERE fiit.start_date = to_date(to_char(((l_roll_x_week13 - 1) * 7) + -- Bug#4903567
3870 g_min_wk_j_st_date), 'J');
3871 EXCEPTION
3872 WHEN NO_DATA_FOUND THEN
3873 NULL;
3874 END;
3875 END IF;
3876 --Call API to compare and store consecutive counts
3877 --for the resource
3878 CALC_CS_RES_CNT_VALUE
3879 (
3880 p_res_cnt_tbl => l_roll_x_wk13_res_cnt_tbl
3881 );
3882 --Assigning current ROLLING WEEK 13 id to
3883 --old period id local variable
3884 l_old_roll_x_week13 := l_roll_x_week13;
3885 l_roll_x_wk13_count := l_roll_x_wk13_count + 1;
3886 l_roll_x_wk13_org_change_flag:= 'N';
3887 END IF;
3888 /* End of Processing for ROLLING WEEK 13 */
3889
3890 --Then for WEEK
3891 --Different from all other periods above because
3892 --this is to put data for current available resources
3893 --in a week for a different report
3894 IF (l_week_id <> 0 AND l_week_id IS NOT NULL) THEN
3895
3896 IF (l_old_week_id < 0
3897 OR l_old_person_id < 0
3898 OR l_old_exp_orgnztion_id < 0) THEN
3899 -- Do Nothing
3900 -- This is just to make sure that nothing is
3901 -- executed for the very first time the program
3902 -- comes in the Cursor
3903 NULL;
3904 ELSIF (l_week_id <> l_old_week_id
3905 OR l_exp_organization_id <> l_old_exp_orgnztion_id) THEN
3906 --All records for this WEEK
3907 --and person id are processed. So, now determine
3908 --the buckets
3909
3910 --INSERT Records for this particular
3911 --person id and WEEK in
3912 --PJI_RM_AGGR_AVL4 table
3913
3914 PREPARE_TO_INS_INTO_AVL4
3915 (
3916 p_exp_organization_id => l_old_exp_orgnztion_id,
3917 p_exp_org_id => l_old_exp_org_id,
3918 p_person_id => l_old_person_id,
3919 p_time_id => l_old_week_id,
3920 p_curr_pd => l_week_id,
3921 p_as_of_date => l_time_id,
3922 p_pd_org_st_date => l_start_date_org_week,
3923 p_period_type_id => 16,
3924 p_calendar_type => 'E',
3925 p_res_cnt_tbl => l_week_res_cnt_tbl,
3926 p_run_mode => p_run_mode,
3927 p_blind_insert_flag => 'N',
3928 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
3929 );
3930
3931 IF (l_week_id = l_old_week_id) THEN
3932 l_week_org_change_flag := 'Y';
3933 ELSE
3934 l_week_count := 0;
3935 END IF;
3936 --After insert SET ALL count and values to 0
3937
3938 FOR m in l_week_res_cnt_tbl.FIRST.. l_week_res_cnt_tbl.LAST
3939 LOOP
3940 l_week_res_cnt_tbl(m) := 0;
3941 END LOOP;
3942 END IF;
3943
3944 --Processing for week availability records
3945 l_week_res_cnt_tbl(1) := NVL(l_week_res_cnt_tbl(1),0) + g_avl_res_cnt_1;
3946 l_week_res_cnt_tbl(2) := NVL(l_week_res_cnt_tbl(2),0) + g_avl_res_cnt_2;
3947 l_week_res_cnt_tbl(3) := NVL(l_week_res_cnt_tbl(3),0) + g_avl_res_cnt_3;
3948 l_week_res_cnt_tbl(4) := NVL(l_week_res_cnt_tbl(4),0) + g_avl_res_cnt_4;
3949 l_week_res_cnt_tbl(5) := NVL(l_week_res_cnt_tbl(5),0) + g_avl_res_cnt_5;
3950
3951 --Store the starting day of the period
3952 IF (l_week_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'Y') THEN
3953 NULL;
3954 ELSIF (l_week_org_change_flag = 'Y' AND l_zero_bkt_cnt_flag = 'N') THEN
3955 l_start_date_org_week := l_time_id;
3956 ELSIF (l_week_count = 0) THEN
3957 BEGIN
3958 SELECT to_char(fiit.start_date,'j')
3959 INTO l_start_date_org_week
3960 FROM fii_time_week fiit
3961 WHERE l_week_id = fiit.week_id;
3962 EXCEPTION
3963 WHEN NO_DATA_FOUND THEN
3964 NULL;
3965 END;
3966 END IF;
3967 --Assigning current WEEK id to
3968 --old period id local variable
3969 l_old_week_id := l_week_id;
3970 l_week_count := l_week_count + 1;
3971 l_week_org_change_flag:= 'N';
3972 END IF;
3973 /* End of Processing for WEEK */
3974 /* After end of processing for ALL PERIODS */
3975 --Store old values for person id, org id and
3976 --organization id
3977
3978 l_old_exp_orgnztion_id := l_exp_organization_id;
3979 l_old_exp_org_id := l_exp_org_id;
3980 l_old_person_id := l_person_id;
3981 END LOOP;
3982 END LOOP;
3983 CLOSE Res_cur;
3984 /*
3985 Make sure that the current records in PL/SQL tables for
3986 inserting in PJI_RM_AGGR_AVL3 and PJI_RM_AGGR_AVL4 table are inserted.
3987 If the number of PL/SQL records did not reach 200
3988 it may not be inserted. So, call the Bulk insert API
3989 with blind insert flag = 'Y' and all variables and
3990 tables as empty. Also, no processing is done on any
3991 of the parameters other than P_BLIND_INSERT_FLAG,
3992 so passing everything as null and dummy PL/SQL tables
3993 */
3994 PREPARE_TO_INS_INTO_AVL3
3995 (
3996 p_exp_organization_id => null,
3997 p_exp_org_id => null,
3998 p_person_id => null,
3999 p_time_id => null,
4000 p_curr_pd => null,
4001 p_as_of_date => null,
4002 p_pd_org_st_date => null,
4003 p_period_type_id => null,
4004 p_calendar_type => null,
4005 p_res_cnt_tbl => l_dummy_res_tbl,
4006 p_run_mode => p_run_mode,
4007 p_blind_insert_flag => 'Y',
4008 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
4009 );
4010
4011 PREPARE_TO_INS_INTO_AVL4
4012 (
4013 p_exp_organization_id => null,
4014 p_exp_org_id => null,
4015 p_person_id => null,
4016 p_time_id => null,
4017 p_curr_pd => null,
4018 p_as_of_date => null,
4019 p_pd_org_st_date => null,
4020 p_period_type_id => null,
4021 p_calendar_type => null,
4022 p_res_cnt_tbl => l_dummy_res_tbl,
4023 p_run_mode => p_run_mode,
4024 p_blind_insert_flag => 'Y',
4025 x_zero_bkt_cnt_flag => l_zero_bkt_cnt_flag
4026 );
4027
4028 /*EXCEPTION
4029 WHEN OTHERS THEN
4030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4031 ROLLBACK TO before_calc_starts;*/
4032 END CALCULATE_RES_AVL;
4033
4034 /**************************************************************************
4035 THE PART BELOW IS THE DRIVER PART FOR CALCULATIONS. IT ACTS LIKE AN
4036 OVERALL MANAGER WHO MONITORS THE RESOURCE AVAILABILITY CALCULATIONS
4037 **************************************************************************/
4038
4039 /*
4040 This procedure gives the current
4041 resource count in the resource status
4042 table that have not been processed
4043 */
4044 PROCEDURE CALC_CURR_RES_COUNT
4045 IS
4046
4047 BEGIN
4048
4049 SELECT COUNT(*)
4050 INTO g_curr_res_left_count
4051 FROM PJI_RM_RES_BATCH_MAP
4052 WHERE worker_status IS NULL
4053 and worker_id IS NULL;
4054
4055 END CALC_CURR_RES_COUNT;
4056
4057 /*
4058 This procedure is used to merge the organization
4059 level records for the RESOURCE AVAILABILITY DURATION
4060 */
4061 PROCEDURE MERGE_ORG_AVL_DUR
4062 (p_worker_id IN NUMBER)
4063 IS
4064 --Defining values for who columns
4065 l_last_update_date DATE := sysdate;
4066 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
4067 l_creation_date DATE := sysdate;
4068 l_created_by NUMBER := FND_GLOBAL.USER_ID;
4069 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
4070 l_process VARCHAR2(30);
4071 BEGIN
4072
4073 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4074
4075 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4076 (
4077 l_process,
4078 'PJI_RM_SUM_AVL.MERGE_ORG_AVL_DUR(p_worker_id);'
4079 )) THEN
4080 RETURN;
4081 END IF;
4082
4083 -- Populate the global variable for minimum
4084 -- start date of week in a julian date
4085
4086 SELECT MIN(to_char(fiik.start_date,'j'))
4087 INTO g_min_wk_j_st_date
4088 FROM fii_time_week fiik;
4089
4090 /* Group all the records based on periods and
4091 MERGE IN the fact table
4092 */
4093 MERGE /*+ parallel(rmr) */ into PJI_AV_ORG_F rmr
4094 USING
4095 (
4096 SELECT
4097 rtmp.EXPENDITURE_ORGANIZATION_ID as EXPENDITURE_ORGANIZATION_ID,
4098 rtmp.EXPENDITURE_ORG_ID as EXPENDITURE_ORG_ID,
4099 rtmp.TIME_ID as TIME_ID,
4100 rtmp.PERIOD_TYPE_ID as PERIOD_TYPE_ID,
4101 rtmp.CALENDAR_TYPE as CALENDAR_TYPE,
4102 rtmp.THRESHOLD as THRESHOLD,
4103 rtmp.AS_OF_DATE as AS_OF_DATE,
4104 sum(rtmp.BCKT_1_CS) BCKT_1_CS,
4105 sum(rtmp.BCKT_2_CS) BCKT_2_CS,
4106 sum(rtmp.BCKT_3_CS) BCKT_3_CS,
4107 sum(rtmp.BCKT_4_CS) BCKT_4_CS,
4108 sum(rtmp.BCKT_5_CS) BCKT_5_CS,
4109 sum(rtmp.BCKT_1_CM) BCKT_1_CM,
4110 sum(rtmp.BCKT_2_CM) BCKT_2_CM,
4111 sum(rtmp.BCKT_3_CM) BCKT_3_CM,
4112 sum(rtmp.BCKT_4_CM) BCKT_4_CM,
4113 sum(rtmp.BCKT_5_CM) BCKT_5_CM,
4114 sum(rtmp.TOTAL_RES_COUNT) TOTAL_RES_COUNT,
4115 l_last_update_date LAST_UPDATE_DATE,
4116 l_last_updated_by LAST_UPDATED_BY,
4117 l_creation_date CREATION_DATE,
4118 l_created_by CREATED_BY,
4119 l_last_update_login LAST_UPDATE_LOGIN
4120 FROM
4121 (
4122 SELECT
4123 rtmp1.EXPENDITURE_ORGANIZATION_ID as EXPENDITURE_ORGANIZATION_ID,
4124 rtmp1.EXPENDITURE_ORG_ID as EXPENDITURE_ORG_ID,
4125 case when rtmp1.period_type_id = 16 then
4126 fwk.WEEK_ID
4127 when rtmp1.period_type_id <> 16 then
4128 rtmp1.TIME_ID
4129 end TIME_ID,
4130 rtmp1.PERIOD_TYPE_ID as PERIOD_TYPE_ID,
4131 rtmp1.PERSON_ID as PERSON_ID,
4132 rtmp1.CALENDAR_TYPE as CALENDAR_TYPE,
4133 rtmp1.THRESHOLD as THRESHOLD,
4134 rtmp1.AS_OF_DATE as AS_OF_DATE,
4135 rtmp1.BCKT_1_CS as BCKT_1_CS,
4136 rtmp1.BCKT_2_CS as BCKT_2_CS,
4137 rtmp1.BCKT_3_CS as BCKT_3_CS,
4138 rtmp1.BCKT_4_CS as BCKT_4_CS,
4139 rtmp1.BCKT_5_CS as BCKT_5_CS,
4140 rtmp1.BCKT_1_CM as BCKT_1_CM,
4141 rtmp1.BCKT_2_CM as BCKT_2_CM,
4142 rtmp1.BCKT_3_CM as BCKT_3_CM,
4143 rtmp1.BCKT_4_CM as BCKT_4_CM,
4144 rtmp1.BCKT_5_CM as BCKT_5_CM,
4145 rtmp1.TOTAL_RES_COUNT as TOTAL_RES_COUNT
4146 FROM
4147 (
4148 SELECT
4149 EXPENDITURE_ORGANIZATION_ID,
4150 EXPENDITURE_ORG_ID,
4151 TIME_ID,
4152 PERIOD_TYPE_ID,
4153 PERSON_ID,
4154 CALENDAR_TYPE,
4155 THRESHOLD,
4156 AS_OF_DATE,
4157 BCKT_1_CS,
4158 BCKT_2_CS,
4159 BCKT_3_CS,
4160 BCKT_4_CS,
4161 BCKT_5_CS,
4162 BCKT_1_CM,
4163 BCKT_2_CM,
4164 BCKT_3_CM,
4165 BCKT_4_CM,
4166 BCKT_5_CM,
4167 TOTAL_RES_COUNT
4168 FROM
4169 PJI_RM_AGGR_AVL3
4170 ) rtmp1,
4171 (
4172 SELECT
4173 fiit.WEEK_ID as WEEK_ID,
4174 (to_char(fiit.start_date,'j') - g_min_wk_j_st_date)/7 + 1 as SEQUENCE_ID
4175 FROM
4176 FII_TIME_WEEK fiit
4177 ORDER BY SEQUENCE_ID
4178 ) fwk
4179 WHERE
4180 rtmp1.time_id = fwk.sequence_id (+)
4181 ORDER BY 1,2,3,4,5
4182 ) rtmp
4183 GROUP BY
4184 rtmp.EXPENDITURE_ORGANIZATION_ID,
4185 rtmp.EXPENDITURE_ORG_ID,
4186 rtmp.PERIOD_TYPE_ID,
4187 rtmp.TIME_ID,
4188 rtmp.CALENDAR_TYPE,
4189 rtmp.THRESHOLD,
4190 rtmp.AS_OF_DATE
4191 ) tmp1
4192 ON
4193 (
4194 tmp1.EXPENDITURE_ORGANIZATION_ID = rmr.EXPENDITURE_ORGANIZATION_ID and
4195 tmp1.EXPENDITURE_ORG_ID = rmr.EXPENDITURE_ORG_ID and
4196 tmp1.PERIOD_TYPE_ID = rmr.PERIOD_TYPE_ID and
4197 tmp1.TIME_ID = rmr.TIME_ID and
4198 tmp1.CALENDAR_TYPE = rmr.CALENDAR_TYPE and
4199 tmp1.THRESHOLD = rmr.THRESHOLD and
4200 tmp1.AS_OF_DATE = rmr.AS_OF_DATE
4201 )
4202 WHEN MATCHED THEN UPDATE SET
4203 rmr.BCKT_1_CS = rmr.BCKT_1_CS + tmp1.BCKT_1_CS,
4204 rmr.BCKT_2_CS = rmr.BCKT_2_CS + tmp1.BCKT_2_CS,
4205 rmr.BCKT_3_CS = rmr.BCKT_3_CS + tmp1.BCKT_3_CS,
4206 rmr.BCKT_4_CS = rmr.BCKT_4_CS + tmp1.BCKT_4_CS,
4207 rmr.BCKT_5_CS = rmr.BCKT_5_CS + tmp1.BCKT_5_CS,
4208 rmr.BCKT_1_CM = rmr.BCKT_1_CM + tmp1.BCKT_1_CM,
4209 rmr.BCKT_2_CM = rmr.BCKT_2_CM + tmp1.BCKT_2_CM,
4210 rmr.BCKT_3_CM = rmr.BCKT_3_CM + tmp1.BCKT_3_CM,
4211 rmr.BCKT_4_CM = rmr.BCKT_4_CM + tmp1.BCKT_4_CM,
4212 rmr.BCKT_5_CM = rmr.BCKT_5_CM + tmp1.BCKT_5_CM,
4213 rmr.TOTAL_RES_COUNT = rmr.TOTAL_RES_COUNT + tmp1.TOTAL_RES_COUNT,
4214 rmr.LAST_UPDATE_DATE = tmp1.LAST_UPDATE_DATE,
4215 rmr.LAST_UPDATED_BY = tmp1.LAST_UPDATED_BY,
4216 rmr.LAST_UPDATE_LOGIN = tmp1.LAST_UPDATE_LOGIN
4217 WHEN NOT MATCHED THEN INSERT
4218 (
4219 rmr.EXPENDITURE_ORGANIZATION_ID,
4220 rmr.EXPENDITURE_ORG_ID,
4221 rmr.PERIOD_TYPE_ID,
4222 rmr.TIME_ID,
4223 rmr.CALENDAR_TYPE,
4224 rmr.THRESHOLD,
4225 rmr.AS_OF_DATE,
4226 rmr.CREATION_DATE,
4227 rmr.CREATED_BY,
4228 rmr.LAST_UPDATE_DATE,
4229 rmr.LAST_UPDATED_BY,
4230 rmr.LAST_UPDATE_LOGIN,
4231 rmr.BCKT_1_CS,
4232 rmr.BCKT_2_CS,
4233 rmr.BCKT_3_CS,
4234 rmr.BCKT_4_CS,
4235 rmr.BCKT_5_CS,
4236 rmr.BCKT_1_CM,
4237 rmr.BCKT_2_CM,
4238 rmr.BCKT_3_CM,
4239 rmr.BCKT_4_CM,
4240 rmr.BCKT_5_CM,
4241 rmr.TOTAL_RES_COUNT
4242 )
4243 values
4244 (
4245 tmp1.EXPENDITURE_ORGANIZATION_ID,
4246 tmp1.EXPENDITURE_ORG_ID,
4247 tmp1.PERIOD_TYPE_ID,
4248 tmp1.TIME_ID,
4249 tmp1.CALENDAR_TYPE,
4250 tmp1.THRESHOLD,
4251 tmp1.AS_OF_DATE,
4252 tmp1.CREATION_DATE,
4253 tmp1.CREATED_BY,
4254 tmp1.LAST_UPDATE_DATE,
4255 tmp1.LAST_UPDATED_BY,
4256 tmp1.LAST_UPDATE_LOGIN,
4257 tmp1.BCKT_1_CS,
4258 tmp1.BCKT_2_CS,
4259 tmp1.BCKT_3_CS,
4260 tmp1.BCKT_4_CS,
4261 tmp1.BCKT_5_CS,
4262 tmp1.BCKT_1_CM,
4263 tmp1.BCKT_2_CM,
4264 tmp1.BCKT_3_CM,
4265 tmp1.BCKT_4_CM,
4266 tmp1.BCKT_5_CM,
4267 tmp1.TOTAL_RES_COUNT
4268 );
4269
4270 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4271 (
4272 l_process,
4273 'PJI_RM_SUM_AVL.MERGE_ORG_AVL_DUR(p_worker_id);'
4274 );
4275
4276 COMMIT;
4277
4278 END MERGE_ORG_AVL_DUR;
4279
4280 /*
4281 This procedure is used to merge the organization level
4282 records for the CURRENT RESOURCE AVAILABILITY
4283 */
4284
4285 PROCEDURE MERGE_CURR_ORG_AVL
4286 (p_worker_id IN NUMBER)
4287 IS
4288 --Defining values for who columns
4289 l_last_update_date DATE := sysdate;
4290 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
4291 l_creation_date DATE := sysdate;
4292 l_created_by NUMBER := FND_GLOBAL.USER_ID;
4293 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
4294 l_process VARCHAR2(30);
4295 BEGIN
4296
4297 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4298
4299 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4300 (
4301 l_process,
4302 'PJI_RM_SUM_AVL.MERGE_CURR_ORG_AVL(p_worker_id);'
4303 )) THEN
4304 RETURN;
4305 END IF;
4306
4307 -- Populate the global variable for minimum
4308 -- start date of week in a julian date
4309
4310 SELECT MIN(to_char(fiik.start_date,'j'))
4311 INTO g_min_wk_j_st_date
4312 FROM fii_time_week fiik;
4313
4314 /* Group all the records based on periods and
4315 MERGE IN the fact table
4316 */
4317 MERGE /*+ parallel(rmr) */ into PJI_CA_ORG_F rmr
4318 USING
4319 (
4320 SELECT
4321 rtmp.EXPENDITURE_ORGANIZATION_ID as EXPENDITURE_ORGANIZATION_ID,
4322 rtmp.EXPENDITURE_ORG_ID as EXPENDITURE_ORG_ID,
4323 rtmp.TIME_ID as TIME_ID,
4324 rtmp.PERIOD_TYPE_ID as PERIOD_TYPE_ID,
4325 rtmp.CALENDAR_TYPE as CALENDAR_TYPE,
4326 rtmp.THRESHOLD as THRESHOLD,
4327 rtmp.AS_OF_DATE as AS_OF_DATE,
4328 sum(rtmp.AVAILABILITY) AVAILABILITY,
4329 sum(rtmp.TOTAL_RES_COUNT) TOTAL_RES_COUNT,
4330 l_last_update_date LAST_UPDATE_DATE,
4331 l_last_updated_by LAST_UPDATED_BY,
4332 l_creation_date CREATION_DATE,
4333 l_created_by CREATED_BY,
4334 l_last_update_login LAST_UPDATE_LOGIN
4335 FROM
4336 PJI_RM_AGGR_AVL4 rtmp
4337 GROUP BY
4338 rtmp.EXPENDITURE_ORGANIZATION_ID,
4339 rtmp.EXPENDITURE_ORG_ID,
4340 rtmp.PERIOD_TYPE_ID,
4341 rtmp.TIME_ID,
4342 rtmp.CALENDAR_TYPE,
4343 rtmp.THRESHOLD,
4344 rtmp.AS_OF_DATE
4345 ) tmp1
4346 ON
4347 (
4348 tmp1.EXPENDITURE_ORGANIZATION_ID = rmr.EXPENDITURE_ORGANIZATION_ID and
4349 tmp1.EXPENDITURE_ORG_ID = rmr.EXPENDITURE_ORG_ID and
4350 tmp1.PERIOD_TYPE_ID = rmr.PERIOD_TYPE_ID and
4351 tmp1.TIME_ID = rmr.TIME_ID and
4352 tmp1.CALENDAR_TYPE = rmr.CALENDAR_TYPE and
4353 tmp1.THRESHOLD = rmr.THRESHOLD and
4354 tmp1.AS_OF_DATE = rmr.AS_OF_DATE
4355 )
4356 WHEN MATCHED THEN UPDATE SET
4357 rmr.AVAILABILITY = rmr.AVAILABILITY + tmp1.AVAILABILITY,
4358 rmr.TOTAL_RES_COUNT = rmr.TOTAL_RES_COUNT + tmp1.TOTAL_RES_COUNT,
4359 rmr.LAST_UPDATE_DATE = tmp1.LAST_UPDATE_DATE,
4360 rmr.LAST_UPDATED_BY = tmp1.LAST_UPDATED_BY,
4361 rmr.LAST_UPDATE_LOGIN = tmp1.LAST_UPDATE_LOGIN
4362 WHEN NOT MATCHED THEN INSERT
4363 (
4364 rmr.EXPENDITURE_ORGANIZATION_ID,
4365 rmr.EXPENDITURE_ORG_ID,
4366 rmr.PERIOD_TYPE_ID,
4367 rmr.TIME_ID,
4368 rmr.CALENDAR_TYPE,
4369 rmr.THRESHOLD,
4370 rmr.AS_OF_DATE,
4371 rmr.CREATION_DATE,
4372 rmr.CREATED_BY,
4373 rmr.LAST_UPDATE_DATE,
4374 rmr.LAST_UPDATED_BY,
4375 rmr.LAST_UPDATE_LOGIN,
4376 rmr.AVAILABILITY,
4377 rmr.TOTAL_RES_COUNT
4378 )
4379 values
4380 (
4381 tmp1.EXPENDITURE_ORGANIZATION_ID,
4382 tmp1.EXPENDITURE_ORG_ID,
4383 tmp1.PERIOD_TYPE_ID,
4384 tmp1.TIME_ID,
4385 tmp1.CALENDAR_TYPE,
4386 tmp1.THRESHOLD,
4387 tmp1.AS_OF_DATE,
4388 tmp1.CREATION_DATE,
4389 tmp1.CREATED_BY,
4390 tmp1.LAST_UPDATE_DATE,
4391 tmp1.LAST_UPDATED_BY,
4392 tmp1.LAST_UPDATE_LOGIN,
4393 tmp1.AVAILABILITY,
4394 tmp1.TOTAL_RES_COUNT
4395 );
4396
4397 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4398 (
4399 l_process,
4400 'PJI_RM_SUM_AVL.MERGE_CURR_ORG_AVL(p_worker_id);'
4401 );
4402
4403 COMMIT;
4404
4405 END MERGE_CURR_ORG_AVL;
4406
4407 /*
4408 This procedure updates the resource status
4409 for which the error occured (if it occured
4410 at all) and processing has not been done.
4411 After this update, the resource can be
4412 picked up by any worker in the current run
4413 */
4414
4415 PROCEDURE UPDATE_RES_STATUS
4416 IS
4417 BEGIN
4418
4419 --Update status table to make sure that any resource
4420 --that was not processed last time is processed this
4421 --time
4422
4423 UPDATE PJI_RM_RES_BATCH_MAP
4424 SET worker_id = null
4425 WHERE worker_status IS NULL
4426 AND worker_id IS NOT NULL;
4427
4428 COMMIT;
4429
4430 END UPDATE_RES_STATUS;
4431
4432 /*
4433 This procedure is called to clean up the temporary
4434 table space allocated for the resource availability
4435 calculations. This would also clean up the status
4436 table for resource calculations
4437 */
4438 PROCEDURE RES_CALC_CLEANUP
4439 (p_worker_id IN NUMBER)
4440 IS
4441 --Defining local variables
4442 l_process VARCHAR2(30);
4443 BEGIN
4444 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4445
4446 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4447 (
4448 l_process,
4449 'PJI_RM_SUM_AVL.RES_CALC_CLEANUP(p_worker_id);'
4450 )) THEN
4451 RETURN;
4452 END IF;
4453
4454 execute immediate ('truncate table ' || PJI_UTILS.get_pji_schema_name || '.PJI_RM_AGGR_AVL1');
4455
4456 execute immediate ('truncate table ' || PJI_UTILS.get_pji_schema_name || '.PJI_RM_AGGR_AVL2');
4457
4458 execute immediate ('truncate table ' || PJI_UTILS.get_pji_schema_name || '.PJI_RM_AGGR_AVL3');
4459
4460 execute immediate ('truncate table ' || PJI_UTILS.get_pji_schema_name || '.PJI_RM_AGGR_AVL4');
4461
4462 execute immediate ('truncate table ' || PJI_UTILS.get_pji_schema_name || '.PJI_RM_AGGR_AVL5');
4463
4464 execute immediate ('truncate table ' || PJI_UTILS.get_pji_schema_name || '.PJI_RM_RES_BATCH_MAP');
4465
4466 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4467 (
4468 l_process,
4469 'PJI_RM_SUM_AVL.RES_CALC_CLEANUP(p_worker_id);'
4470 );
4471 COMMIT;
4472
4473 END RES_CALC_CLEANUP;
4474
4475 /*
4476 This procedure is used to determine the
4477 resources that would be processed by the
4478 worker id passed to this procedure for
4479 1st run, i.e., for OLD fact records.
4480 One the resources are determined the procedure
4481 calls appropriate APIs to get the resource
4482 buckets calculated
4483 */
4484 PROCEDURE START_RES_AVL_CALC_R1
4485 (p_worker_id IN NUMBER)
4486 IS
4487
4488 -- Defining local variables
4489 l_person_id NUMBER := 0;
4490 l_partition NUMBER := 1;
4491 l_try_res_again VARCHAR2(1) := 'N';
4492 l_count_res_status NUMBER := 0;
4493 l_process VARCHAR2(30);
4494 l_row_count NUMBER := 0;
4495 l_parallel_processes NUMBER;
4496
4497 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
4498
4499 BEGIN
4500 --This call in the loop will take care of:
4501 --PHASE 1
4502 --PHASE 2
4503 --PHASE 3
4504 --of the summarization process for resources in the buckets
4505
4506 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4507
4508 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4509 (
4510 l_process,
4511 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R1(p_worker_id);'
4512 )) THEN
4513 RETURN;
4514 END IF;
4515
4516 --Instantiate package level (ONLY) global variables
4517 INIT_PCKG_GLOBAL_VARS;
4518 --POP_ROLL_WEEK_OFFSET;
4519
4520 --Get count from the status table to get the maximum number
4521 --of times the loop should run
4522
4523 SELECT COUNT(*)
4524 INTO l_count_res_status
4525 FROM PJI_RM_RES_BATCH_MAP;
4526
4527 FOR i in 1.. l_count_res_status
4528 LOOP
4529 l_try_res_again := 'N';
4530 pji_utils.write2log(p_worker_id || ': R1: Before updating PJI_RM_RES_BATCH_MAP and returning resource id');
4531 UPDATE PJI_RM_RES_BATCH_MAP
4532 SET worker_id = p_worker_id
4533 WHERE worker_status IS NULL
4534 and worker_id IS NULL
4535 and rownum < 2
4536 RETURNING person_id
4537 INTO l_person_id;
4538 pji_utils.write2log(p_worker_id || ': R1: After updating PJI_RM_RES_BATCH_MAP and returning resource id');
4539 IF SQL%ROWCOUNT <> 0 THEN
4540 COMMIT;
4541 ELSE
4542 CALC_CURR_RES_COUNT;
4543 IF (g_curr_res_left_count = 0) THEN
4544 EXIT;
4545 ELSE
4546 l_try_res_again := 'Y';
4547 END IF;
4548 END IF;
4549
4550 IF(l_try_res_again = 'Y') THEN
4551 --Wait for some time and try again
4552 PJI_PROCESS_UTIL.sleep(PJI_RM_SUM_MAIN.g_process_delay);
4553 ELSE
4554 pji_utils.write2log(p_worker_id || ': R1: Before Calculating availability');
4555 CALCULATE_RES_AVL
4556 (
4557 p_worker_id => p_worker_id,
4558 p_person_id => l_person_id,
4559 p_run_mode => 'OLD_FACT_RECORDS',
4560 x_return_status => l_return_status
4561 );
4562 pji_utils.write2log(p_worker_id || ': R1: After Calculating availability');
4563 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4564 /*
4565 UPDATE PJI_RM_RES_BATCH_MAP
4566 SET worker_id = null
4567 WHERE person_id = l_person_id;
4568
4569 COMMIT;
4570 */
4571 --Raise the error here
4572 raise RAISE_USER_DEF_EXCEPTION;
4573 ELSE
4574 pji_utils.write2log(p_worker_id || ': R1: Before Updating PJI_RM_RES_BATCH_MAP for completion');
4575 UPDATE PJI_RM_RES_BATCH_MAP
4576 SET worker_status = 'C'
4577 WHERE person_id = l_person_id
4578 and worker_id = p_worker_id;
4579 pji_utils.write2log(p_worker_id || ': R1: After Updating PJI_RM_RES_BATCH_MAP for completion');
4580 COMMIT;
4581 END IF;
4582 END IF;
4583 END LOOP;
4584
4585 select count(*)
4586 into l_row_count
4587 from PJI_RM_RES_BATCH_MAP
4588 where nvl(WORKER_STATUS, 'X') <> 'C';
4589
4590 if (l_row_count = 0) then
4591
4592 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
4593
4594 for x in 2 .. l_parallel_processes loop
4595
4596 update PJI_SYSTEM_PRC_STATUS
4597 set STEP_STATUS = 'C'
4598 where PROCESS_NAME = PJI_RM_SUM_MAIN.g_process || to_char(x)
4599 and STEP_NAME = 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R1(p_worker_id);';
4600
4601 commit;
4602
4603 end loop;
4604
4605 end if;
4606
4607 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4608 (
4609 l_process,
4610 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R1(p_worker_id);'
4611 );
4612
4613 COMMIT;
4614 EXCEPTION
4615 WHEN RAISE_USER_DEF_EXCEPTION THEN
4616 RAISE;
4617 WHEN OTHERS THEN
4618 RAISE;
4619 END START_RES_AVL_CALC_R1;
4620
4621 /*
4622 This procedure is used to determine the
4623 resources that would be processed by the
4624 worker id passed to this procedure for
4625 2nd run, i.e., for NEW fact records.
4626 One the resources are determined the procedure
4627 calls appropriate APIs to get the resource
4628 buckets calculated
4629 */
4630 PROCEDURE START_RES_AVL_CALC_R2
4631 (p_worker_id IN NUMBER)
4632 IS
4633
4634 -- Defining local variables
4635 l_person_id NUMBER := 0;
4636 l_partition NUMBER := 1;
4637 l_try_res_again VARCHAR2(1) := 'N';
4638 l_count_res_status NUMBER := 0;
4639 l_process VARCHAR2(30);
4640 l_row_count NUMBER := 0;
4641 l_parallel_processes NUMBER;
4642
4643 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
4644
4645 BEGIN
4646 --This call in the loop will take care of:
4647 --PHASE 1
4648 --PHASE 2
4649 --PHASE 3
4650 --of the summarization process for resources in the buckets
4651
4652 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4653
4654 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4655 (
4656 l_process,
4657 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R2(p_worker_id);'
4658 )) THEN
4659 RETURN;
4660 END IF;
4661
4662 COMMIT;
4663
4664 --Instantiate package level (ONLY) global variables
4665 INIT_PCKG_GLOBAL_VARS;
4666 --POP_ROLL_WEEK_OFFSET;
4667
4668 --Get count from the status table to get the maximum number
4669 --of times the loop should run
4670
4671 SELECT COUNT(*)
4672 INTO l_count_res_status
4673 FROM PJI_RM_RES_BATCH_MAP;
4674
4675 FOR i in 1.. l_count_res_status
4676 LOOP
4677 l_try_res_again := 'N';
4678 pji_utils.write2log(p_worker_id || ': R2: Before updating PJI_RM_RES_BATCH_MAP and returning resource id');
4679 UPDATE PJI_RM_RES_BATCH_MAP
4680 SET worker_id = p_worker_id
4681 WHERE worker_status IS NULL
4682 and worker_id IS NULL
4683 and rownum < 2
4684 RETURNING person_id
4685 INTO l_person_id;
4686 pji_utils.write2log(p_worker_id || ': R2: After updating PJI_RM_RES_BATCH_MAP and returning resource id');
4687 IF SQL%ROWCOUNT <> 0 THEN
4688 COMMIT;
4689 ELSE
4690 CALC_CURR_RES_COUNT;
4691 IF (g_curr_res_left_count = 0) THEN
4692 EXIT;
4693 ELSE
4694 l_try_res_again := 'Y';
4695 END IF;
4696 END IF;
4697
4698 IF(l_try_res_again = 'Y') THEN
4699 --Wait for some time and try again
4700 PJI_PROCESS_UTIL.sleep(PJI_RM_SUM_MAIN.g_process_delay);
4701 ELSE
4702 pji_utils.write2log(p_worker_id || ': R2: Before Calculating availability');
4703 CALCULATE_RES_AVL
4704 (
4705 p_worker_id => p_worker_id,
4706 p_person_id => l_person_id,
4707 p_run_mode => 'NEW_FACT_RECORDS',
4708 x_return_status => l_return_status
4709 );
4710 pji_utils.write2log(p_worker_id || ': R2: After Calculating availability');
4711 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4712 /*
4713 UPDATE PJI_RM_RES_BATCH_MAP
4714 SET worker_id = null
4715 WHERE person_id = l_person_id;
4716
4717 COMMIT;
4718 */
4719 --Raise the error here
4720 raise RAISE_USER_DEF_EXCEPTION;
4721 ELSE
4722 pji_utils.write2log(p_worker_id || ': R2: Before Updating PJI_RM_RES_BATCH_MAP for completion');
4723 UPDATE PJI_RM_RES_BATCH_MAP
4724 SET worker_status = 'C'
4725 WHERE person_id = l_person_id
4726 and worker_id = p_worker_id;
4727 pji_utils.write2log(p_worker_id || ': R2: After Updating PJI_RM_RES_BATCH_MAP for completion');
4728 COMMIT;
4729 END IF;
4730 END IF;
4731 END LOOP;
4732
4733 select count(*)
4734 into l_row_count
4735 from PJI_RM_RES_BATCH_MAP
4736 where nvl(WORKER_STATUS, 'X') <> 'C';
4737
4738 if (l_row_count = 0) then
4739
4740 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
4741
4742 for x in 2 .. l_parallel_processes loop
4743
4744 update PJI_SYSTEM_PRC_STATUS
4745 set STEP_STATUS = 'C'
4746 where PROCESS_NAME = PJI_RM_SUM_MAIN.g_process || to_char(x)
4747 and STEP_NAME = 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R2(p_worker_id);';
4748
4749 commit;
4750
4751 end loop;
4752
4753 end if;
4754
4755 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4756 (
4757 l_process,
4758 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R2(p_worker_id);'
4759 );
4760
4761 COMMIT;
4762 EXCEPTION
4763 WHEN RAISE_USER_DEF_EXCEPTION THEN
4764 RAISE;
4765 WHEN OTHERS THEN
4766 RAISE;
4767 END START_RES_AVL_CALC_R2;
4768
4769 /*
4770 This procedure updates the resource status
4771 table for run 2 with new fact records
4772 */
4773 PROCEDURE UPDATE_RES_STA_FOR_RUN2
4774 (p_worker_id IN NUMBER)
4775 IS
4776 l_process VARCHAR2(30);
4777 l_res_process_cnt NUMBER(15):=0;
4778 l_res_full_cnt NUMBER(15):=0;
4779 BEGIN
4780
4781 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4782
4783 -- implicit commit
4784 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
4785 tabname => 'PJI_RM_AGGR_RES2',
4786 percent => 10,
4787 degree => BIS_COMMON_PARAMETERS.
4788 GET_DEGREE_OF_PARALLELISM);
4789 -- implicit commit
4790 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
4791 tabname => 'PJI_RM_AGGR_RES2',
4792 colname => 'PERSON_ID',
4793 percent => 10,
4794 degree => BIS_COMMON_PARAMETERS.
4795 GET_DEGREE_OF_PARALLELISM);
4796
4797 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4798 (
4799 l_process,
4800 'PJI_RM_SUM_AVL.UPDATE_RES_STA_FOR_RUN2(p_worker_id);'
4801 )) THEN
4802 RETURN;
4803 END IF;
4804
4805 --This update should be done only if the process has run
4806 --for old records BUT HAS NOT run for new records. If the
4807 --process has run for even one new record, then this update
4808 --should not happen
4809 SELECT count(*)
4810 INTO l_res_process_cnt
4811 FROM PJI_RM_RES_BATCH_MAP
4812 where worker_id IS NOT NULL
4813 AND worker_status IS NOT NULL;
4814
4815 SELECT count(*)
4816 INTO l_res_full_cnt
4817 FROM PJI_RM_RES_BATCH_MAP;
4818
4819 IF (l_res_full_cnt = l_res_process_cnt) THEN
4820
4821 --update all resources with null values for
4822 --worker and status to make it available
4823 --for next run with new fact records
4824
4825 UPDATE PJI_RM_RES_BATCH_MAP
4826 SET worker_id = null,
4827 worker_status = null;
4828 END IF;
4829
4830 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4831 (
4832 l_process,
4833 'PJI_RM_SUM_AVL.UPDATE_RES_STA_FOR_RUN2(p_worker_id);'
4834 );
4835
4836 COMMIT;
4837
4838 END UPDATE_RES_STA_FOR_RUN2;
4839
4840 /*
4841 This procedure is used to insert rows
4842 in the resource status table. The
4843 population and constant update of this
4844 status table helps in maintaining dynamic
4845 pooling of workers and also help in starting
4846 process just prior to the point of error
4847 during run time
4848 */
4849
4850 PROCEDURE INS_INTO_RES_STATUS
4851 (p_worker_id IN NUMBER)
4852 IS
4853 --Defining local variables
4854 l_process VARCHAR2(30);
4855 l_count_res_status NUMBER := 0;
4856 BEGIN
4857
4858 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4859
4860 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4861 (
4862 l_process,
4863 'PJI_RM_SUM_AVL.INS_INTO_RES_STATUS(p_worker_id);'
4864 )) THEN
4865 RETURN;
4866 END IF;
4867
4868 SELECT COUNT(*)
4869 INTO l_count_res_status
4870 FROM PJI_RM_RES_BATCH_MAP;
4871
4872 IF (l_count_res_status = 0) THEN
4873 --If this is the first time program is being run then'
4874 --no data would be present, so insert the resources
4875 --one needs to process
4876 INSERT INTO PJI_RM_RES_BATCH_MAP(person_id)
4877 SELECT DISTINCT person_id from PJI_RM_AGGR_RES2;
4878 END IF;
4879
4880 -- implicit commit
4881 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
4882 tabname => 'PJI_RM_RES_BATCH_MAP',
4883 percent => 10,
4884 degree => BIS_COMMON_PARAMETERS.
4885 GET_DEGREE_OF_PARALLELISM);
4886 -- implicit commit
4887 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
4888 tabname => 'PJI_RM_RES_BATCH_MAP',
4889 colname => 'PERSON_ID',
4890 percent => 10,
4891 degree => BIS_COMMON_PARAMETERS.
4892 GET_DEGREE_OF_PARALLELISM);
4893
4894 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4895 (
4896 l_process,
4897 'PJI_RM_SUM_AVL.INS_INTO_RES_STATUS(p_worker_id);'
4898 );
4899
4900 COMMIT;
4901
4902 END INS_INTO_RES_STATUS;
4903
4904 /*
4905 This procedure is used to refresh the
4906 organization level materialized view
4907 for resource duration availability buckets
4908 */
4909
4910 PROCEDURE REFRESH_AV_ORGO_F_MV
4911 (p_worker_id IN NUMBER)
4912 IS
4913 --Defining local variables
4914 l_process VARCHAR2(30);
4915 l_p_degree NUMBER := 0;
4916 l_extraction_type varchar2(30);
4917 l_pji_schema varchar2(30);
4918 l_apps_schema varchar2(30);
4919 l_errbuf varchar2(255);
4920 l_retcode varchar2(255);
4921 BEGIN
4922
4923 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4924
4925 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4926 (
4927 l_process,
4928 'PJI_RM_SUM_AVL.REFRESH_AV_ORGO_F_MV(p_worker_id);'
4929 )) THEN
4930 RETURN;
4931 END IF;
4932 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
4933 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
4934
4935 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
4936 l_extraction_type <> 'PARTIAL') then
4937 return;
4938 end if;
4939
4940 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
4941 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
4942 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
4943 if (l_p_degree = 1) then
4944 l_p_degree := 0;
4945 end if;
4946
4947 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
4948 TABNAME => 'PJI_ORG_DENORM',
4949 PERCENT => 10,
4950 DEGREE => l_p_degree);
4951
4952 IF (l_extraction_type = 'FULL') THEN
4953 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
4954 l_retcode,
4955 'PJI_AV_ORGO_F_MV',
4956 'C',
4957 'N');
4958 ELSE
4959
4960 FND_STATS.GATHER_TABLE_STATS
4961 (OWNNAME => l_pji_schema,
4962 TABNAME => 'MLOG$_PJI_AV_ORG_F',
4963 PERCENT => 10,
4964 DEGREE => l_p_degree);
4965
4966 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
4967 l_retcode,
4968 'PJI_AV_ORGO_F_MV',
4969 'F',
4970 'N');
4971
4972 END IF;
4973
4974 if (l_extraction_type <> 'INCREMENTAL') then
4975 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
4976 tabname => 'PJI_AV_ORGO_F_MV',
4977 percent => 10,
4978 degree => l_p_degree);
4979 end if;
4980
4981 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4982 (
4983 l_process,
4984 'PJI_RM_SUM_AVL.REFRESH_AV_ORGO_F_MV(p_worker_id);'
4985 );
4986
4987 COMMIT;
4988
4989 END REFRESH_AV_ORGO_F_MV;
4990
4991 /*
4992 This procedure is used to refresh the
4993 organization level materialized view
4994 for current resource availability
4995 */
4996
4997 PROCEDURE REFRESH_CA_ORGO_F_MV
4998 (p_worker_id IN NUMBER)
4999 IS
5000 --Defining local variables
5001 l_process VARCHAR2(30);
5002 l_p_degree NUMBER := 0;
5003 l_extraction_type varchar2(30);
5004 l_pji_schema varchar2(30);
5005 l_apps_schema varchar2(30);
5006 l_errbuf varchar2(255);
5007 l_retcode varchar2(255);
5008 BEGIN
5009
5010 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5011
5012 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5013 (
5014 l_process,
5015 'PJI_RM_SUM_AVL.REFRESH_CA_ORGO_F_MV(p_worker_id);'
5016 )) THEN
5017 RETURN;
5018 END IF;
5019 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5020 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5021
5022 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5023 l_extraction_type <> 'PARTIAL') then
5024 return;
5025 end if;
5026
5027 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
5028 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5029 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5030 if (l_p_degree = 1) then
5031 l_p_degree := 0;
5032 end if;
5033
5034 IF (l_extraction_type = 'FULL') THEN
5035 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5036 l_retcode,
5037 'PJI_CA_ORGO_F_MV',
5038 'C',
5039 'N');
5040 ELSE
5041
5042 FND_STATS.GATHER_TABLE_STATS
5043 (OWNNAME => l_pji_schema,
5044 TABNAME => 'MLOG$_PJI_CA_ORG_F',
5045 PERCENT => 10,
5046 DEGREE => l_p_degree);
5047
5048 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5049 l_retcode,
5050 'PJI_CA_ORGO_F_MV',
5051 'F',
5052 'N');
5053
5054 END IF;
5055
5056 if (l_extraction_type <> 'INCREMENTAL') then
5057 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5058 tabname => 'PJI_CA_ORGO_F_MV',
5059 percent => 10,
5060 degree => l_p_degree);
5061 end if;
5062
5063 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5064 (
5065 l_process,
5066 'PJI_RM_SUM_AVL.REFRESH_CA_ORGO_F_MV(p_worker_id);'
5067 );
5068
5069 COMMIT;
5070
5071 END REFRESH_CA_ORGO_F_MV;
5072
5073 END PJI_RM_SUM_AVL;