1 PACKAGE BODY PJI_RESOURCE_UTILZ AS
2 /* $Header: PJIPR01B.pls 120.3.12010000.2 2008/10/22 17:43:28 jcgeorge ship $ */
3
4 g_calc_mthd VARCHAR2(30);
5 g_last_summ_date DATE;
6 g_last_summ_period_id NUMBER;
7 g_last_summ_pd_seq NUMBER;
8 g_last_summ_pd_name VARCHAR2(80);
9 g_curr_period_seq NUMBER;
10 g_curr_period_id NUMBER;
11 g_curr_quarter_id NUMBER;
12 g_curr_year_id NUMBER;
13 g_curr_period_name VARCHAR2(100);
14 g_curr_quarter_name VARCHAR2(100);
15 g_curr_year_name VARCHAR2(100);
16 g_prev_yr_period_seq NUMBER;
17 g_forward_periods NUMBER := 3;
18 g_backward_periods NUMBER := 8;
19 g_curr_yr_pd_start_date DATE;
20 g_prev_yr_pd_start_date DATE;
21 g_prev_yr_period_id NUMBER;
22 g_curr_yr_max_sequence NUMBER;
23 g_curr_yr_min_sequence NUMBER;
24
25 /*
26 This procedure is used to get data based on the calendar
27 type that is set in the user profile. This data is used
28 in determining periods that should be shown on the
29 screen to the user
30 */
31
32 PROCEDURE GET_PERIOD_DATA
33 (
34 p_calendar_type IN VARCHAR2
35 ,p_org_id IN NUMBER /* MOAC Changes */
36 ,x_calendar_id OUT NOCOPY /* file.sql.39 change */ NUMBER
37 ,x_accnt_period_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2
38 ,x_sets_of_books_id OUT NOCOPY /* file.sql.39 change */ NUMBER
39 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
40 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
41 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
42 )
43 IS
44 BEGIN
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46
47 IF (p_calendar_type = 'G') THEN
48
49 SELECT fiin.calendar_id,
50 sob.accounted_period_type,
51 sob.set_of_books_id
52 INTO x_calendar_id,
53 x_accnt_period_type,
54 x_sets_of_books_id
55 FROM
56 fii_time_cal_name fiin,
57 pa_implementations imp,
58 gl_sets_of_books sob
59 WHERE
60 imp.set_of_books_id = sob.set_of_books_id
61 AND imp.org_id = p_org_id -- MOAC Changes
62 AND sob.period_set_name = imp.period_set_name
63 AND fiin.period_type = sob.accounted_period_type
64 AND sob.period_set_name = fiin.period_set_name;
65
66 ELSIF (p_calendar_type = 'P') THEN
67
68 SELECT fiin.calendar_id,
69 imp.pa_period_type,
70 imp.set_of_books_id
71 INTO x_calendar_id,
72 x_accnt_period_type,
73 x_sets_of_books_id
74 FROM
75 fii_time_cal_name fiin,
76 pa_implementations imp
77 WHERE
78 imp.period_set_name = fiin.period_set_name
79 AND imp.org_id = p_org_id -- MOAC Changes
80 AND fiin.period_type = imp.pa_period_type;
81
82 ELSIF (p_calendar_type = 'E') THEN
83 SELECT -1
84 INTO x_calendar_id
85 FROM
86 DUAL;
87 END IF;
88
89 END GET_PERIOD_DATA;
90
91 /*
92 This procedure validates and populates the data to be
93 used by the graphs and tables on the Personal Resource
94 Utilization Page. This method is called when we DO NOT
95 NEED to get daily records to calculate expected
96 utilization, i.e., when the last summarization date
97 does not resides in the periods that we are processing
98 */
99 PROCEDURE PJI_POP_SIMPLE_UTILZ_DATA
100 (
101 p_calendar_id IN NUMBER
102 ,p_calendar_type IN VARCHAR2
103 ,p_population_mode IN VARCHAR2
104 ,p_table_amount_type IN NUMBER
105 ,x_return_status OUT NOCOPY /* file.sql.39 change */ NUMBER
106 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
107 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
108 )
109 IS
110
111 l_prev_period_id NUMBER;
112 l_prev_quarter_id NUMBER;
113 l_prev_year_id NUMBER;
114 BEGIN
115
116 delete from pji_pmv_time_dim_tmp;
117
118 IF (p_population_mode = 'GRAPH') THEN
119
120 --Insert records for the current period and corresponding
121 --periods backwards and forward
122 INSERT INTO PJI_PMV_TIME_DIM_TMP
123 (
124 ID,
125 PRIOR_ID,
126 NAME,
127 ORDER_BY_ID,
128 PERIOD_TYPE,
129 AMOUNT_TYPE,
130 CALENDAR_TYPE
131 )
132 SELECT period_id as id,
133 null as prior_id,
134 period_name as name,
135 sequence - g_curr_period_seq as order_by_id,
136 32 as period_type,
137 1 as amount_type,
138 p_calendar_type as calendar_type
139 FROM pji_time_mv
140 WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
141 AND calendar_id = p_calendar_id
142 AND sequence between g_curr_period_seq-g_backward_periods
143 and g_curr_period_seq+g_forward_periods;
144
145 --Update records for the current period in the prior year
146 --and corresponding periods backwards and forward
147
148 UPDATE PJI_PMV_TIME_DIM_TMP pmv
149 SET pmv.PRIOR_ID =
150 ( SELECT period_id as prior_id
151 FROM pji_time_mv lower
152 WHERE
153 lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
154 AND lower.calendar_id = p_calendar_id
155 AND lower.sequence between g_prev_yr_period_seq-g_backward_periods
156 and g_prev_yr_period_seq+g_forward_periods
157 AND pmv.order_by_id = lower.sequence-g_prev_yr_period_seq
158 );
159 ELSIF (p_population_mode = 'TABLE') THEN
160
161 INSERT INTO PJI_PMV_TIME_DIM_TMP
162 (
163 ID,
164 PRIOR_ID,
165 NAME,
166 ORDER_BY_ID,
167 PERIOD_TYPE,
168 AMOUNT_TYPE,
169 CALENDAR_TYPE
170 )
171 SELECT r1.id as id,
172 r1.prior_id as prior_id,
173 r1.name as name,
174 r1.sequence as order_by_id,
175 r1.period_type as period_type,
176 r1.amount_type as amount_type,
177 r1.calendar_type as calendar_type
178 FROM
179 (
180 SELECT period_id as id,
181 null as prior_id,
182 period_name as name,
183 32 as period_type,
184 p_table_amount_type as amount_type,
185 p_calendar_type as calendar_type,
186 1 as sequence
187 FROM pji_time_mv
188 WHERE period_id = g_curr_period_id
189 and calendar_id = p_calendar_id
190 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
191 UNION ALL
192 select period_id as id,
193 null as prior_id,
194 quarter_name as name,
195 64 as period_type,
196 p_table_amount_type as amount_type,
197 p_calendar_type as calendar_type,
198 2 as sequence
199 FROM pji_time_mv
200 WHERE quarter_id = g_curr_quarter_id
201 and calendar_id = p_calendar_id
202 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_QTR', 'FII_TIME_CAL_QTR')
203 UNION ALL
204 select period_id as id,
205 null as prior_id,
206 year as name,
207 128 as period_type,
208 p_table_amount_type as amount_type,
209 p_calendar_type as calendar_type,
210 3 as sequence
211 FROM pji_time_mv
212 WHERE year_id = g_curr_year_id
213 and calendar_id = p_calendar_id
214 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_YEAR', 'FII_TIME_CAL_YEAR')
215 ) r1;
216
217 SELECT period_id,
218 quarter_id,
219 year_id
220 INTO
221 l_prev_period_id,
222 l_prev_quarter_id,
223 l_prev_year_id
224 FROM pji_time_mv
225 WHERE sequence = g_prev_yr_period_seq
226 and calendar_id = p_calendar_id
227 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
228
229 UPDATE PJI_PMV_TIME_DIM_TMP pmv
230 SET pmv.PRIOR_ID = l_prev_period_id
231 where order_by_id = 1;
232
233 UPDATE PJI_PMV_TIME_DIM_TMP pmv
234 SET pmv.PRIOR_ID = l_prev_quarter_id
235 where order_by_id = 2;
236
237 UPDATE PJI_PMV_TIME_DIM_TMP pmv
238 SET pmv.PRIOR_ID = l_prev_year_id
239 where order_by_id = 3;
240
241 END IF;
242
243 END PJI_POP_SIMPLE_UTILZ_DATA;
244
245 /*
246 This procedure validates and populates the data to be
247 used by the graphs and tables on the Personal Resource
248 Utilization Page. This method is called when we NEED to
249 get daily records to calculate expected
250 utilization, i.e., when the last summarization date
251 resides in the periods that we are processing
252 */
253 PROCEDURE PJI_POP_COMPLEX_UTILZ_DATA
254 (
255 p_calendar_id IN NUMBER
256 ,p_calendar_type IN VARCHAR2
257 ,p_population_mode IN VARCHAR2
258 ,p_org_id IN NUMBER
259 ,x_return_status OUT NOCOPY /* file.sql.39 change */ NUMBER
260 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
261 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
262 )
263 IS
264 l_org_id NUMBER;
265 l_curr_period_id_tbl N_TYPE_TAB;
266 l_pop_daily_rec_flag VARCHAR2(1) := 'N';
267 l_last_summ_pd_end_j NUMBER;
268 l_last_summ_pd_name VARCHAR2(100);
269 l_period_type VARCHAR2(100);
270 l_qtr_period_type VARCHAR2(100);
271 l_year_period_type VARCHAR2(100);
272 l_last_sum_pmv_seq NUMBER;
273 l_daily_rec_qtr_flag VARCHAR2(1) := 'N';
274 l_daily_rec_year_flag VARCHAR2(1) := 'N';
275 l_prev_period_id NUMBER;
276 l_prev_quarter_id NUMBER;
277 l_prev_year_id NUMBER;
278
279 BEGIN
280
281 delete from pji_pmv_time_dim_tmp;
282
283 IF (p_calendar_type <> 'E') THEN
284 l_period_type := 'FII_TIME_CAL_PERIOD';
285 ELSE
286 l_period_type := 'FII_TIME_ENT_PERIOD';
287 END IF;
288
289 IF (p_calendar_type <> 'E') THEN
290 l_org_id := p_org_id; -- MOAC Changes
291 PJI_PMV_ENGINE.Convert_Operating_Unit(l_org_id,'TM');
292 END IF;
293
294 IF (p_population_mode = 'GRAPH') THEN
295
296 --Insert records for the current period and corresponding
297 --periods backwards and forward
298 INSERT INTO PJI_PMV_TIME_DIM_TMP
299 (
300 ID,
301 PRIOR_ID,
302 NAME,
303 ORDER_BY_ID,
304 PERIOD_TYPE,
305 AMOUNT_TYPE,
306 CALENDAR_TYPE
307 )
308 SELECT period_id as id,
309 null as prior_id,
310 period_name as name,
311 sequence - g_curr_period_seq as order_by_id,
312 32 as period_type,
313 DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
314 p_calendar_type as calendar_type
315 FROM pji_time_mv
316 WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
317 AND calendar_id = p_calendar_id
318 AND sequence between g_curr_period_seq-g_backward_periods
319 and g_curr_period_seq+g_forward_periods;
320
321 --Update records for the current period in the prior year
322 --and corresponding periods backwards and forward
323
324 UPDATE PJI_PMV_TIME_DIM_TMP pmv
325 SET pmv.PRIOR_ID =
326 ( SELECT period_id as prior_id
327 FROM pji_time_mv lower
328 WHERE
329 lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
330 AND lower.calendar_id = p_calendar_id
331 AND lower.sequence between g_prev_yr_period_seq-g_backward_periods
332 and g_prev_yr_period_seq+g_forward_periods
333 AND pmv.order_by_id = lower.sequence-g_prev_yr_period_seq
334 )
335 WHERE pmv.period_type = 32;
336
337 --Check if the last summarization date period matches
338 --any of the current year periods or not
339 --This is because the last summarization date may fall
340 --either
341 --1. within the 12 periods
342 --2. after 12 periods
343 --3. before 12 periods (in case of smaller periods)
344 SELECT
345 ID
346 BULK COLLECT INTO
347 l_curr_period_id_tbl
348 FROM
349 PJI_PMV_TIME_DIM_TMP pmv
350 where pmv.period_type = 32;
351
352 FOR i in l_curr_period_id_tbl.FIRST.. l_curr_period_id_tbl.LAST
353 LOOP
354 IF g_last_summ_period_id = l_curr_period_id_tbl(i) THEN
355 l_pop_daily_rec_flag := 'Y';
356 END IF;
357 END LOOP;
358
359 --If one needs to populate daily records because the last
360 --summarization date lies in the period for which the
361 --utilization would be calculated then proceed ahead
362
363 IF l_pop_daily_rec_flag = 'Y' THEN
364 --Update the table to set the period id to null
365 --where the last summarization date resides
366
367 UPDATE PJI_PMV_TIME_DIM_TMP pmv
368 SET pmv.ID = null
369 where pmv.id = g_last_summ_period_id
370 and pmv.calendar_type = p_calendar_type
371 and pmv.period_type = 32
372 RETURNING order_by_id
373 INTO l_last_sum_pmv_seq;
374
375 --Call the API to insert daily records
376
377 PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
378 (
379 to_char(g_last_summ_date,'j')
380 , l_period_type
381 , null
382 , null
383 , p_calendar_id
384 , g_last_summ_pd_name
385 , g_last_summ_period_id
386 );
387
388 PJI_PMV_ENGINE.Convert_NFViewBY_AS_OF_DATE
389 (
390 to_char(g_last_summ_date,'j')+1
391 , l_period_type
392 , null
393 , null
394 , p_calendar_id
395 , g_last_summ_pd_name
396 , g_last_summ_period_id
397 );
398
399 --Update sequence for daily records
400 UPDATE PJI_PMV_TIME_DIM_TMP pmv
401 set pmv.order_by_id = l_last_sum_pmv_seq
402 where pmv.period_type = 1 OR pmv.period_type = 16;
403 END IF;
404 ELSIF (p_population_mode = 'TABLE') THEN
405
406 IF (g_last_summ_pd_seq < g_curr_yr_min_sequence) THEN
407 --Call the API to populate time records
408 --We do not need daily records in this case
409 --as the last summarization date doesn't lie
410 --in any of the periods that we are processing
411 PJI_POP_SIMPLE_UTILZ_DATA
412 (
413 p_calendar_id => p_calendar_id
414 ,p_calendar_type => p_calendar_type
415 ,p_population_mode => 'TABLE'
416 ,p_table_amount_type => 0
417 ,x_return_status => x_return_status
418 ,x_msg_count => x_msg_count
419 ,x_msg_data => x_msg_data
420 );
421 ELSE
422 --Now it is sure that the last summarization date
423 --lies in one of the periods of the whole year
424 --that we are populating
425
426 --First process for period
427 IF (g_last_summ_period_id <> g_curr_period_id) THEN
428 INSERT INTO PJI_PMV_TIME_DIM_TMP
429 (
430 ID,
431 PRIOR_ID,
432 NAME,
433 ORDER_BY_ID,
434 PERIOD_TYPE,
435 AMOUNT_TYPE,
436 CALENDAR_TYPE
437 )
438 SELECT period_id as id,
439 null as prior_id,
440 period_name as name,
441 1 as order_by_id,
442 32 as period_type,
443 DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
444 p_calendar_type as calendar_type
445 FROM pji_time_mv
446 WHERE period_id = g_curr_period_id
447 and calendar_id = p_calendar_id
448 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
449 ELSE
450 --Call the API to insert daily records
451
452 PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
453 (
454 to_char(g_last_summ_date,'j')
455 , l_period_type
456 , null
457 , null
458 , p_calendar_id
459 , g_last_summ_pd_name
460 , 1
461 );
462
463 PJI_PMV_ENGINE.Convert_NFViewBY_AS_OF_DATE
464 (
465 to_char(g_last_summ_date,'j')+1
466 , l_period_type
467 , null
468 , null
469 , p_calendar_id
470 , g_last_summ_pd_name
471 , 1
472 );
473 END IF;
474
475 --Then for quarter
476 IF (p_calendar_type <> 'E') THEN
477 l_qtr_period_type := 'FII_TIME_CAL_QTR';
478 ELSE
479 l_qtr_period_type := 'FII_TIME_ENT_QTR';
480 END IF;
481
482 SELECT
483 period_id
484 BULK COLLECT INTO
485 l_curr_period_id_tbl
486 FROM
487 pji_time_mv pt
488 where quarter_id = g_curr_quarter_id
489 and calendar_id = p_calendar_id
490 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
491
492 FOR i in l_curr_period_id_tbl.FIRST.. l_curr_period_id_tbl.LAST
493 LOOP
494 IF g_last_summ_period_id = l_curr_period_id_tbl(i) THEN
495 l_daily_rec_qtr_flag := 'Y';
496 END IF;
497 END LOOP;
498
499 IF (l_daily_rec_qtr_flag <> 'Y') THEN
500 INSERT INTO PJI_PMV_TIME_DIM_TMP
501 (
502 ID,
503 PRIOR_ID,
504 NAME,
505 ORDER_BY_ID,
506 PERIOD_TYPE,
507 AMOUNT_TYPE,
508 CALENDAR_TYPE
509 )
510 SELECT period_id as id,
511 null as prior_id,
512 g_curr_quarter_name as name,
513 2 as order_by_id,
514 32 as period_type,
515 DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
516 p_calendar_type as calendar_type
517 FROM pji_time_mv
518 WHERE quarter_id = g_curr_quarter_id
519 and calendar_id = p_calendar_id
520 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
521 ELSE
522 --Call the API to insert daily records
523
524 PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
525 (
526 to_char(g_last_summ_date,'j')
527 , l_qtr_period_type
528 , null
529 , null
530 , p_calendar_id
531 , g_curr_quarter_name
532 , 2
533 );
534
535 PJI_PMV_ENGINE.Convert_NFViewBY_AS_OF_DATE
536 (
537 to_char(g_last_summ_date,'j')+1
538 , l_qtr_period_type
539 , null
540 , null
541 , p_calendar_id
542 , g_curr_quarter_name
543 , 2
544 );
545 END IF;
546
547 --Then for year
548 IF (p_calendar_type <> 'E') THEN
549 l_year_period_type := 'FII_TIME_CAL_YEAR';
550 ELSE
551 l_year_period_type := 'FII_TIME_ENT_YEAR';
552 END IF;
553
554 --We do not need to check if the summarization date
555 --lies in this year or not because if the program
556 --comes here then it guarantees that the summarization
557 --date lies in the year
558
559 --Call the API to insert daily records
560
561 PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
562 (
563 to_char(g_last_summ_date,'j')
564 , l_year_period_type
565 , null
566 , null
567 , p_calendar_id
568 , g_curr_year_name
569 , 3
570 );
571
572 PJI_PMV_ENGINE.Convert_NFViewBY_AS_OF_DATE
573 (
574 to_char(g_last_summ_date,'j')+1
575 , l_year_period_type
576 , null
577 , null
578 , p_calendar_id
579 , g_curr_year_name
580 , 3
581 );
582
583 --Then for previous year
584 SELECT period_id,
585 quarter_id,
586 year_id
587 INTO
588 l_prev_period_id,
589 l_prev_quarter_id,
590 l_prev_year_id
591 FROM pji_time_mv
592 WHERE sequence = g_prev_yr_period_seq
593 and calendar_id = p_calendar_id
594 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
595
596 INSERT INTO PJI_PMV_TIME_DIM_TMP
597 (
598 ID,
599 PRIOR_ID,
600 NAME,
601 ORDER_BY_ID,
602 PERIOD_TYPE,
603 AMOUNT_TYPE,
604 CALENDAR_TYPE
605 )
606 VALUES
607 (
608 null ,
609 l_prev_period_id,
610 g_curr_period_name,
611 1 ,
612 32 ,
613 1 ,
614 p_calendar_type
615 );
616
617 INSERT INTO PJI_PMV_TIME_DIM_TMP
618 (
619 ID,
620 PRIOR_ID,
621 NAME,
622 ORDER_BY_ID,
623 PERIOD_TYPE,
624 AMOUNT_TYPE,
625 CALENDAR_TYPE
626 )
627 VALUES
628 (
629 null ,
630 l_prev_quarter_id,
631 g_curr_quarter_name,
632 2 ,
633 64 ,
634 1 ,
635 p_calendar_type
636 );
637 INSERT INTO PJI_PMV_TIME_DIM_TMP
638 (
639 ID,
640 PRIOR_ID,
641 NAME,
642 ORDER_BY_ID,
643 PERIOD_TYPE,
644 AMOUNT_TYPE,
645 CALENDAR_TYPE
646 )
647 VALUES
648 (
649 null ,
650 l_prev_year_id,
651 g_curr_year_name,
652 3 ,
653 128 ,
654 1 ,
655 p_calendar_type
656 );
657 END IF;
658 END IF;
659
660 END PJI_POP_COMPLEX_UTILZ_DATA;
661
662 /* This method is the primary method for calculating
663 utilization data and populating the global temporary
664 tables with the utilization data that would be
665 shown in the graph
666
667 This method also initializes the package specific
668 global variables that would be used in utilization
669 calculation for both graph and table utilization
670 data with respected to the graph and table shown on
671 the Personal Resource Utilization Page
672 */
673 PROCEDURE PJI_POP_GRAPH_UTILZ_DATA
674 (
675 p_person_id IN NUMBER
676 ,p_period_id IN NUMBER
677 ,p_period_type IN NUMBER
678 ,p_calendar_type IN VARCHAR2
679 ,p_org_id IN NUMBER
680 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
681 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
682 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
683 )
684 IS
685 l_org_id NUMBER;
686 l_calendar_id NUMBER;
687 l_accnt_period_type VARCHAR2(15);
688 l_sets_of_books_id NUMBER;
689 l_act_utilz_label VARCHAR2(100);
690 l_sched_utilz_label VARCHAR2(100);
691 l_curr_yr_act_utilz_label VARCHAR2(100);
692 l_curr_yr_sched_utilz_label VARCHAR2(100);
693 l_prev_yr_act_utilz_label VARCHAR2(100);
694 l_prev_year_name VARCHAR2(100);
695 BEGIN
696 --Get the crucial parameters
697 GET_PERIOD_DATA
698 (
699 p_calendar_type => p_calendar_type
700 ,p_org_id => p_org_id
701 ,x_calendar_id => l_calendar_id
702 ,x_accnt_period_type => l_accnt_period_type
703 ,x_sets_of_books_id => l_sets_of_books_id
704 ,x_return_status => x_return_status
705 ,x_msg_count => x_msg_count
706 ,x_msg_data => x_msg_data
707 );
708
709 --Get the last summarized date
710 SELECT trunc(to_date(PJI_UTILS.GET_PARAMETER('LAST_FM_EXTR_DATE'),'YYYY/MM/DD'))
711 INTO g_last_summ_date
712 FROM dual;
713
714 IF g_last_summ_date IS NULL THEN
715 RETURN;
716 END IF;
717
718 IF (p_calendar_type = 'E') THEN
719 SELECT period.ent_period_id
720 ,period.name
721 INTO g_last_summ_period_id,
722 g_last_summ_pd_name
723 FROM fii_time_day day,
724 fii_time_ent_period period
725 WHERE report_date = g_last_summ_date
726 AND period.ent_period_id = day.ent_period_id;
727 ELSE
728 SELECT day.cal_period_id
729 ,pmv.period_name
730 INTO g_last_summ_period_id,
731 g_last_summ_pd_name
732 FROM
733 fii_time_cal_day_mv day
734 ,pji_time_mv pmv
735 WHERE
736 report_date = g_last_summ_date
737 and pmv.period_type = 'FII_TIME_CAL_PERIOD' /* Added this condition for bug 4312361 */
738 AND day.cal_period_id = pmv.period_id
739 and pmv.calendar_id = l_calendar_id
740 and day.calendar_id = pmv.calendar_id;
741 END IF;
742
743 --Get the sequence for the last summarized date period
744 SELECT sequence
745 INTO g_last_summ_pd_seq
746 FROM pji_time_mv
747 WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
748 AND period_id = g_last_summ_period_id
749 AND calendar_id = l_calendar_id;
750
751 --Get the sequence and other information for the current selected period
752 SELECT sequence,
753 period_id,
754 period_name,
755 quarter_id,
756 quarter_name,
757 year_id,
758 year
759 INTO g_curr_period_seq,
760 g_curr_period_id,
761 g_curr_period_name,
762 g_curr_quarter_id,
763 g_curr_quarter_name,
764 g_curr_year_id,
765 g_curr_year_name
766 FROM pji_time_mv
767 WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
768 AND period_id = p_period_id
769 AND calendar_id = l_calendar_id;
770
771 --Get the corresponding period from the last year
772
773 IF (p_calendar_type = 'E') THEN
774 --Get start date of current period
775 SELECT start_date
776 INTO g_curr_yr_pd_start_date
777 FROM fii_time_ent_period
778 WHERE ent_period_id = p_period_id;
779
780 --Get corresponding date in the last year
781 SELECT Fii_Time_Api.ent_sd_lysper_end(g_curr_yr_pd_start_date)
782 INTO g_prev_yr_pd_start_date
783 FROM DUAL;
784
785 --Get the corresponding period in the last year
786 SELECT ent_period_id
787 INTO g_prev_yr_period_id
788 FROM fii_time_day
789 WHERE report_date_julian = to_char(g_prev_yr_pd_start_date,'j');
790 ELSE
791 --Get start date of current period
792 SELECT start_date
793 INTO g_curr_yr_pd_start_date
794 FROM fii_time_cal_period
795 WHERE cal_period_id = p_period_id
796 AND calendar_id = l_calendar_id;
797
798 --Get corresponding date in the last year
799 SELECT Fii_Time_Api.cal_sd_lysper_end(g_curr_yr_pd_start_date, l_calendar_id)
800 INTO g_prev_yr_pd_start_date
801 FROM DUAL;
802
803 --Get the corresponding period in the last year
804 SELECT cal_period_id
805 INTO g_prev_yr_period_id
806 FROM fii_time_cal_day_mv
807 WHERE report_date_julian = to_char(g_prev_yr_pd_start_date,'j')
808 and calendar_id = l_calendar_id;
809 END IF;
810
811 --Get the sequence for the last year corresponding period
812 SELECT NVL(sequence,0)
813 INTO g_prev_yr_period_seq
814 FROM pji_time_mv
815 WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
816 AND period_id = g_prev_yr_period_id
817 AND calendar_id = l_calendar_id;
818
819 --Populate the time tables
820 IF (
821 (g_last_summ_pd_seq - g_curr_period_seq) > g_forward_periods
822 ) THEN
823 --Call the API to populate time records
824 --We do not need daily records in this case
825 --as the last summarization date doesn't lie
826 --in any of the periods that we are processing
827 PJI_POP_SIMPLE_UTILZ_DATA
828 (
829 p_calendar_id => l_calendar_id
830 ,p_calendar_type => p_calendar_type
831 ,p_population_mode => 'GRAPH'
832 ,x_return_status => x_return_status
833 ,x_msg_count => x_msg_count
834 ,x_msg_data => x_msg_data
835 );
836 ELSE
837 PJI_POP_COMPLEX_UTILZ_DATA
838 (
839 p_calendar_id => l_calendar_id
840 ,p_org_id => p_org_id
841 ,p_calendar_type => p_calendar_type
842 ,p_population_mode => 'GRAPH'
843 ,x_return_status => x_return_status
844 ,x_msg_count => x_msg_count
845 ,x_msg_data => x_msg_data
846 );
847 END IF;
848
849 SELECT NVL(fnd_profile.value('PA_RES_UTIL_DEF_CALC_METHOD'),'CAPACITY')
850 INTO g_calc_mthd
851 FROM DUAL;
852
853 --Calculate expected utilization and put it
854 --in the global temporary table
855
856 DELETE FROM PJI_RES_UTILZ_TMP2;
857
858 INSERT INTO PJI_RES_UTILZ_TMP2
859 (
860 period_name,
861 curr_yr_actual_utiliz,
862 curr_yr_sched_utiliz,
863 curr_yr_exp_utiliz,
864 prev_yr_utiliz,
865 sequence
866 )
867 SELECT
868 r1.period_name as period_name,
869 round(SUM(r1.actual_utilz) * 100,2) as curr_yr_actual_utiliz,
870 round(SUM(r1.sched_utilz) * 100,2) as curr_yr_sched_utiliz,
871 round(SUM(r1.exp_utilz) * 100,2) as curr_yr_exp_utiliz,
872 round(SUM(r1.prev_yr_utilz) * 100,2) as prev_yr_utiliz,
873 r1.id as sequence
874 FROM
875 (
876 SELECT
877 SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
878 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as actual_utilz,
879 SUM(CONF_WTD_RES_HRS_S)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s)),
880 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
881 SUM(DECODE(pmv.amount_type,1, total_wtd_res_hrs_a,CONF_WTD_RES_HRS_S))/DECODE(SUM(DECODE(g_calc_mthd,
882 'CAPACITY',cur2.capacity_hrs - DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
883 DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s))),
884 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs -
885 DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
886 DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s)))) as exp_utilz,
887 0 as prev_yr_utilz,
888 pmv.name as period_name,
889 pmv.order_by_id as id
890 from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
891 where person_id = p_person_id
892 and pmv.period_type IN (1,16)
893 and pmv.period_type = cur2.period_type_id
894 and pmv.calendar_type = 'C'
895 and pmv.calendar_type = cur2.calendar_type
896 and pmv.id = cur2.time_id
897 group by pmv.name, pmv.order_by_id
898 UNION ALL
899 SELECT
900 SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
901 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as actual_utilz,
902 SUM(CONF_WTD_RES_HRS_S)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s)),
903 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
904 SUM(DECODE(pmv.amount_type,1, total_wtd_res_hrs_a,CONF_WTD_RES_HRS_S))/DECODE(SUM(DECODE(g_calc_mthd,
905 'CAPACITY',cur2.capacity_hrs - DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
906 DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s))),
907 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs -
908 DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
909 DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s)))) as exp_utilz,
910 0 as prev_yr_utilz,
911 pmv.name as period_name,
912 pmv.order_by_id as id
913 from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
914 where person_id = p_person_id
915 and pmv.period_type = 32
916 and pmv.period_type = cur2.period_type_id
917 and pmv.calendar_type = p_calendar_type
918 and pmv.calendar_type = cur2.calendar_type
919 and pmv.id = cur2.time_id
920 and pmv.id is not null
921 group by pmv.name, pmv.order_by_id
922 UNION ALL
923 SELECT
924 0 as actual_utilz,
925 0 as sched_utilz,
926 0 as exp_utilz,
927 SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
928 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as prev_yr_utilz,
929 pmv.name as period_name,
930 pmv.order_by_id as id
931 from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
932 where person_id = p_person_id
933 and pmv.period_type = 32
934 and pmv.period_type = cur2.period_type_id
935 and pmv.calendar_type = p_calendar_type
936 and pmv.calendar_type = cur2.calendar_type
937 and pmv.prior_id = cur2.time_id
938 group by pmv.name, order_by_id
939 ) r1
940 group by r1.period_name, r1.id
941 order by id;
942
943 --Now, we need to get data in a format where
944 --the legend on the graph shows the year names
945 --along with utilization labels
946
947 SELECT meaning
948 INTO l_act_utilz_label
949 FROM pji_lookups
950 WHERE lookup_type = 'PJI_RM_UTILZ_GRAPH_HEADER'
951 and lookup_code = 'ACT_UTILZ';
952
953 SELECT meaning
954 INTO l_sched_utilz_label
955 FROM pji_lookups
956 WHERE lookup_type = 'PJI_RM_UTILZ_GRAPH_HEADER'
957 and lookup_code = 'SCHED_UTILZ';
958
959 SELECT year
960 INTO
961 l_prev_year_name
962 FROM pji_time_mv
963 WHERE sequence = g_prev_yr_period_seq
964 and calendar_id = l_calendar_id
965 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
966
967 l_curr_yr_act_utilz_label := g_curr_year_name || ' ' || l_act_utilz_label;
968 l_curr_yr_sched_utilz_label := g_curr_year_name || ' ' || l_sched_utilz_label;
969 l_prev_yr_act_utilz_label := l_prev_year_name || ' ' || l_act_utilz_label;
970
971
972 INSERT INTO PJI_RES_UTILZ_TMP2
973 (
974 period_name,
975 series_label,
976 value,
977 secondary_sequence,
978 sequence
979 )
980 SELECT DISTINCT r3.period_name,
981 case when r3.tmp_index = 1 then
982 l_curr_yr_act_utilz_label
983 when r3.tmp_index = 2 then
984 l_curr_yr_sched_utilz_label
985 when r3.tmp_index = 3 then
986 l_prev_yr_act_utilz_label
987 end series_label,
988 case when r3.tmp_index = 1 then
989 r3.curr_yr_actual_utiliz
990 when r3.tmp_index = 2 then
991 r3.curr_yr_sched_utiliz
992 when r3.tmp_index = 3 then
993 r3.prev_yr_utiliz
994 end value,
995 case when r3.tmp_index = 1 then
996 1
997 when r3.tmp_index = 2 then
998 2
999 when r3.tmp_index = 3 then
1000 3
1001 end secnd_seq,
1002 r3.sequence
1003 FROM
1004 (
1005 select r1.period_name,
1006 r1.curr_yr_actual_utiliz,
1007 r1.curr_yr_sched_utiliz,
1008 r1.prev_yr_utiliz,
1009 r1.sequence,
1010 r2.tmp_index
1011 FROM
1012 (
1013 select period_name,
1014 curr_yr_actual_utiliz,
1015 curr_yr_sched_utiliz,
1016 prev_yr_utiliz,
1017 sequence
1018 FROM PJI_RES_UTILZ_TMP2
1019 ) r1,
1020 (
1021 SELECT 1 as tmp_index from dual
1022 UNION ALL
1023 SELECT 2 as tmp_index from dual
1024 UNION ALL
1025 SELECT 3 as tmp_index from dual
1026 ) r2
1027 ) r3;
1028
1029 --Delete records that were inserted initially
1030 DELETE FROM PJI_RES_UTILZ_TMP2
1031 WHERE series_label IS NULL;
1032
1033 END PJI_POP_GRAPH_UTILZ_DATA;
1034
1035 /* This method is the primary method for calculating
1036 utilization data and populating the global temporary
1037 tables with the utilization data that would be
1038 shown in the table on the page
1039
1040 This method also uses the package specific
1041 global variables for utilization calculation.
1042 These variables were initialized in the above
1043 API for populating temporary table for data to
1044 be shown in the graph
1045 */
1046 PROCEDURE PJI_POP_TABLE_UTILZ_DATA
1047 (
1048 p_person_id IN NUMBER
1049 ,p_period_id IN NUMBER
1050 ,p_period_type IN NUMBER
1051 ,p_calendar_type IN VARCHAR2
1052 ,p_org_id IN NUMBER
1053 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1054 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1055 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1056 )
1057 IS
1058 l_org_id NUMBER;
1059 l_calendar_id NUMBER;
1060 l_accnt_period_type VARCHAR2(15);
1061 l_sets_of_books_id NUMBER;
1062
1063 BEGIN
1064 --Get the crucial parameters
1065 GET_PERIOD_DATA
1066 (
1067 p_calendar_type => p_calendar_type
1068 ,p_org_id => p_org_id
1069 ,x_calendar_id => l_calendar_id
1070 ,x_accnt_period_type => l_accnt_period_type
1071 ,x_sets_of_books_id => l_sets_of_books_id
1072 ,x_return_status => x_return_status
1073 ,x_msg_count => x_msg_count
1074 ,x_msg_data => x_msg_data
1075 );
1076
1077 SELECT
1078 min(sequence),
1079 max(sequence)
1080 INTO
1081 g_curr_yr_max_sequence,
1082 g_curr_yr_min_sequence
1083 FROM
1084 pji_time_mv pt
1085 WHERE year_id = g_curr_year_id
1086 and calendar_id = l_calendar_id
1087 and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
1088
1089 --Populate the time tables
1090 IF (
1091 g_last_summ_pd_seq > g_curr_yr_max_sequence
1092 ) THEN
1093 --Call the API to populate time records
1094 --We do not need daily records in this case
1095 --as the last summarization date doesn't lie
1096 --in any of the periods that we are processing
1097 PJI_POP_SIMPLE_UTILZ_DATA
1098 (
1099 p_calendar_id => l_calendar_id
1100 ,p_calendar_type => p_calendar_type
1101 ,p_population_mode => 'TABLE'
1102 ,p_table_amount_type => 1
1103 ,x_return_status => x_return_status
1104 ,x_msg_count => x_msg_count
1105 ,x_msg_data => x_msg_data
1106 );
1107 ELSE
1108 PJI_POP_COMPLEX_UTILZ_DATA
1109 (
1110 p_calendar_id => l_calendar_id
1111 ,p_calendar_type => p_calendar_type
1112 ,p_population_mode => 'TABLE'
1113 ,p_org_id => p_org_id
1114 ,x_return_status => x_return_status
1115 ,x_msg_count => x_msg_count
1116 ,x_msg_data => x_msg_data
1117 );
1118 END IF;
1119
1120 --Calculate expected utilization and put it
1121 --in the global temporary table
1122
1123 DELETE FROM PJI_RES_UTILZ_TMP3;
1124
1125 INSERT INTO PJI_RES_UTILZ_TMP3
1126 (
1127 period,
1128 actual_utilization,
1129 sched_utilization,
1130 expected_utilization,
1131 prior_yr_utilization,
1132 sequence
1133 )
1134 SELECT r1.period_name as period,
1135 round(SUM(r1.actual_utilz) * 100,2) as actual_utilization,
1136 round(SUM(r1.sched_utilz) * 100,2) as sched_utilization,
1137 round(SUM(r1.exp_utilz) * 100,2) as expected_utilization,
1138 round(SUM(r1.prev_yr_utilz) * 100,2) as prior_yr_utilization,
1139 r1.id as sequence
1140 FROM
1141 (
1142 SELECT pmv.name as period_name,
1143 SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
1144 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as actual_utilz,
1145 SUM(CONF_WTD_RES_HRS_S)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s)),
1146 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
1147 SUM(DECODE(pmv.amount_type,1, total_wtd_res_hrs_a,CONF_WTD_RES_HRS_S))/DECODE(SUM(DECODE(g_calc_mthd,
1148 'CAPACITY',cur2.capacity_hrs - DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
1149 DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s))),
1150 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs -
1151 DECODE(pmv.amount_type,1,cur2.reduce_capacity_hrs_a,cur2.reduce_capacity_hrs_s),
1152 DECODE(pmv.amount_type,1, cur2.total_hrs_a, cur2.conf_hrs_s)))) as exp_utilz,
1153 0 as prev_yr_utilz,
1154 pmv.order_by_id as id
1155 FROM pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
1156 WHERE person_id = p_person_id
1157 and pmv.period_type = cur2.period_type_id
1158 and pmv.calendar_type = cur2.calendar_type
1159 and pmv.id = cur2.time_id
1160 and pmv.id is not null
1161 group by pmv.name, pmv.order_by_id
1162 UNION ALL
1163 SELECT pmv.name as period_name,
1164 0 as actual_utilz,
1165 0 as sched_utilz,
1166 0 as exp_utilz,
1167 SUM(total_wtd_res_hrs_a)/DECODE(SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a)),
1168 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as prev_yr_utilz,
1169 pmv.order_by_id as id
1170 from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
1171 where person_id = p_person_id
1172 and pmv.period_type = cur2.period_type_id
1173 and pmv.calendar_type = cur2.calendar_type
1174 and pmv.prior_id = cur2.time_id
1175 and pmv.prior_id is not null
1176 group by pmv.name, pmv.order_by_id
1177 ) r1
1178 group by r1.period_name, r1.id;
1179
1180 END PJI_POP_TABLE_UTILZ_DATA;
1181
1182 /* This method is used to populate the periods
1183 that are shown to the user for selection on
1184 the Personal Resource Utilization Page.
1185
1186 The periods are populated in a global temporary
1187 table, and based on whether it is a PA period or
1188 a GL/Enterprise period, the data is shown on the
1189 page either in a LOV or a pop list respectively
1190 */
1191 PROCEDURE PJI_POPULATE_PERIODS
1192 (
1193 p_period_type_id IN NUMBER
1194 ,p_calendar_type IN VARCHAR2
1195 ,p_period_id IN NUMBER
1196 ,p_org_id IN NUMBER
1197 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1198 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1199 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1200 )
1201 IS
1202 l_calendar_id NUMBER;
1203 l_accnt_period_type VARCHAR2(15);
1204 l_sets_of_books_id NUMBER;
1205 l_sequence NUMBER;
1206 l_application_id NUMBER;
1207 l_period_id_tbl N_TYPE_TAB;
1208 l_period_name_tbl V_TYPE_TAB;
1209 l_period_status_tbl V_TYPE_TAB;
1210 l_period_st_dt_tbl N_TYPE_TAB;
1211 BEGIN
1212 DELETE FROM PJI_RES_UTILZ_TMP1;
1213 --fnd_global.apps_initialize(1319, 55211, 1292);
1214
1215 IF (p_calendar_type = 'G') THEN
1216 SELECT pa_period_process_pkg.application_id
1217 INTO l_application_id
1218 FROM dual;
1219 ELSIF (p_calendar_type = 'P') THEN
1220 SELECT application_id
1221 INTO l_application_id
1222 FROM fnd_application
1223 WHERE application_short_name = 'PA';
1224 END IF;
1225
1226 GET_PERIOD_DATA
1227 (
1228 p_calendar_type => p_calendar_type
1229 ,p_org_id => p_org_id
1230 ,x_calendar_id => l_calendar_id
1231 ,x_accnt_period_type => l_accnt_period_type
1232 ,x_sets_of_books_id => l_sets_of_books_id
1233 ,x_return_status => x_return_status
1234 ,x_msg_count => x_msg_count
1235 ,x_msg_data => x_msg_data
1236 );
1237
1238 --Get the sequence for the year
1239 SELECT sequence
1240 INTO l_sequence
1241 FROM pji_time_mv
1242 WHERE
1243 period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_YEAR', 'FII_TIME_CAL_YEAR')
1244 AND calendar_id = l_calendar_id
1245 and year_id in
1246 (
1247 SELECT year_id
1248 FROM pji_time_mv
1249 WHERE period_id = p_period_id
1250 AND calendar_id = l_calendar_id
1251 );
1252
1253 --Get the period id and names (with status)
1254
1255 IF ( p_calendar_type = 'E') THEN
1256 --Get for Enterprise period
1257 --No status for enterprise period because
1258 --it is a period used for reporting and
1259 --not accounting or transaction
1260 SELECT period_id,
1261 period_name,
1262 null,
1263 to_char(period_start_date,'j')
1264 BULK COLLECT INTO
1265 l_period_id_tbl,
1266 l_period_name_tbl,
1267 l_period_status_tbl,
1268 l_period_st_dt_tbl
1269 FROM pji_time_mv
1270 WHERE calendar_id = l_calendar_id
1271 and period_type = 'FII_TIME_ENT_PERIOD'
1272 and year_id in
1273 (
1274 SELECT year_id
1275 FROM pji_time_mv
1276 WHERE period_type = 'FII_TIME_ENT_YEAR'
1277 and calendar_id = l_calendar_id
1278 and sequence in
1279 (l_sequence-1,l_sequence,l_sequence+1)
1280 )
1281 ORDER BY period_start_date;
1282 ELSE
1283 --Get for PA/GL period
1284 SELECT r1.period_id,
1285 r1.period_name,
1286 r2.show_status,
1287 to_char(r1.period_start_date,'j')
1288 BULK COLLECT INTO
1289 l_period_id_tbl,
1290 l_period_name_tbl,
1291 l_period_status_tbl,
1292 l_period_st_dt_tbl
1293 FROM
1294 (
1295 SELECT period_id,
1296 period_name,
1297 period_start_date
1298 FROM pji_time_mv
1299 WHERE calendar_id = l_calendar_id
1300 and period_type = 'FII_TIME_CAL_PERIOD'
1301 and year_id in
1302 (
1303 SELECT year_id
1304 FROM pji_time_mv
1305 WHERE period_type = 'FII_TIME_CAL_YEAR'
1306 and calendar_id = l_calendar_id
1307 and sequence in
1308 (l_sequence-1,l_sequence,l_sequence+1)
1309 )
1310 ) r1, gl_period_statuses_v r2
1311 where r2.period_type = l_accnt_period_type
1312 and r2.set_of_books_id = l_sets_of_books_id
1313 and r2.application_id = l_application_id
1314 and r1.period_name = r2.period_name
1315 ORDER BY r1.period_start_date;
1316 END IF;
1317
1318 FORALL k IN 1.. l_period_id_tbl.count
1319 INSERT INTO PJI_RES_UTILZ_TMP1
1320 (
1321 period_id,
1322 period_name,
1323 period_status,
1324 period_start_date
1325 )
1326 VALUES
1327 (
1328 l_period_id_tbl(k),
1329 l_period_name_tbl(k),
1330 l_period_status_tbl(k),
1331 l_period_st_dt_tbl(k)
1332 );
1333
1334 END PJI_POPULATE_PERIODS;
1335
1336 /*
1337 This API is called from the page to get
1338 all the relevant initialization parameters
1339 that are needed to determine the business
1340 flow and hide/show regions and items on the
1341 page. This is the initialization API that is
1342 called from the Page
1343 */
1344
1345 PROCEDURE PJI_GET_PERIOD_PROFILE_DATA
1346 (
1347 p_org_id IN NUMBER
1348 ,x_period_type_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1349 ,x_calendar_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1350 ,x_person_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1351 ,x_period_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1352 ,x_curr_period_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1353 ,x_curr_period_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1354 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1355 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1356 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1357 )
1358 IS
1359 l_pa_res_util_def_pd_types VARCHAR2(30);
1360 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1361 l_calendar_id NUMBER;
1362 l_accnt_period_type VARCHAR2(15);
1363 l_sets_of_books_id NUMBER;
1364 l_msg_index_out NUMBER; -- -- Bug Ref : 7010273
1365 BEGIN
1366 x_return_status := FND_API.G_RET_STS_SUCCESS;
1367 --Get the site level profile option of the user
1368 SELECT fnd_profile.value('PA_RES_UTIL_DEF_PERIOD_TYPE')
1369 INTO l_pa_res_util_def_pd_types
1370 FROM dual;
1371
1372 IF (l_pa_res_util_def_pd_types = 'GL') THEN
1373 x_period_type_id := 32;
1374 x_calendar_type := 'G';
1375 END IF;
1376
1377 IF (l_pa_res_util_def_pd_types = 'PA') THEN
1378 x_period_type_id := 32;
1379 x_calendar_type := 'P';
1380 END IF;
1381
1382 IF (l_pa_res_util_def_pd_types = 'GE') THEN
1383 x_period_type_id := 32;
1384 x_calendar_type := 'E';
1385 END IF;
1386
1387 IF (p_org_id IS NULL) THEN
1388 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PJI',
1389 p_msg_name => 'PJI_MO_PROFILE_OPTION_NOT_FND');
1390 x_return_status := FND_API.G_RET_STS_ERROR;
1391 RETURN;
1392 END IF;
1393
1394 --Get the resource id
1395 SELECT employee_id
1396 INTO x_person_id
1397 FROM fnd_user
1398 WHERE user_id = l_user_id;
1399
1400 --Get the period type profile option name
1401 SELECT meaning
1402 INTO x_period_type
1403 FROM pa_lookups
1404 WHERE lookup_type = 'PA_RES_UTIL_DEF_PERIOD_TYPES'
1405 and lookup_code = l_pa_res_util_def_pd_types;
1406
1407 --Get the current period id
1408 --First get the calendar_id
1409 GET_PERIOD_DATA
1410 (
1411 p_calendar_type => x_calendar_type
1412 ,p_org_id => p_org_id
1413 ,x_calendar_id => l_calendar_id
1414 ,x_accnt_period_type => l_accnt_period_type
1415 ,x_sets_of_books_id => l_sets_of_books_id
1416 ,x_return_status => x_return_status
1417 ,x_msg_count => x_msg_count
1418 ,x_msg_data => x_msg_data
1419 );
1420
1421 --Bug5872158 Make sure SYSDATE is lesser than or equal to the maximum period end date in pji_time_mv
1422 IF ( x_calendar_type = 'E') THEN
1423 --Get for Enterprise period
1424 SELECT period_id,
1425 period_name
1426 INTO x_curr_period_id,
1427 x_curr_period_name
1428 FROM pji_time_mv
1429 WHERE
1430 (SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
1431 FROM pji_time_mv
1432 WHERE 1=1
1433 AND calendar_id = l_calendar_id
1434 AND period_type = 'FII_TIME_ENT_PERIOD') BETWEEN period_start_date and period_end_date
1435 AND calendar_id = l_calendar_id
1436 AND period_type = 'FII_TIME_ENT_PERIOD';
1437 ELSE
1438 --Get for PA/GL period
1439 SELECT period_id,
1440 period_name
1441 INTO x_curr_period_id,
1442 x_curr_period_name
1443 FROM pji_time_mv
1444 WHERE
1445 (SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
1446 FROM pji_time_mv
1447 WHERE 1=1
1448 AND calendar_id = l_calendar_id
1449 AND period_type = 'FII_TIME_CAL_PERIOD') BETWEEN period_start_date and period_end_date
1450 AND calendar_id = l_calendar_id
1451 AND period_type = 'FII_TIME_CAL_PERIOD';
1452 END IF;
1453 -- Bug Ref : 7010273
1454 EXCEPTION
1455 WHEN NO_DATA_FOUND THEN
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1458 p_msg_name => 'PA_SU_INFO_MISSING');
1459 x_msg_count := FND_MSG_PUB.Count_Msg;
1460 IF ( x_msg_count > 0 ) THEN
1461 PA_INTERFACE_UTILS_PUB.GET_MESSAGES ( p_encoded => FND_API.G_TRUE
1462 ,p_msg_index => 1
1463 ,p_data => x_msg_data
1464 ,p_msg_index_out => l_msg_index_out );
1465 END IF;
1466 END PJI_GET_PERIOD_PROFILE_DATA;
1467
1468 /* This API provides a single point of contact
1469 that is called from the page to populate
1470 all kind of utilization data in global temporary
1471 tables.
1472
1473 This API calls the driver APIs to populate data
1474 in global temporary tables, that would be used
1475 by graph, table and period pop list / LOV on the
1476 Personal Resource Utilization Page
1477 */
1478 PROCEDURE PJI_POP_UTILIZATION_DATA
1479 (
1480 p_person_id IN NUMBER
1481 ,p_period_id IN NUMBER
1482 ,p_period_type IN NUMBER
1483 ,p_calendar_type IN VARCHAR2
1484 ,p_org_id IN NUMBER
1485 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1486 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1487 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1488 )
1489 IS
1490 BEGIN
1491 x_return_status := FND_API.G_RET_STS_SUCCESS;
1492
1493 --Call the API to populate the table PJI_RES_UTILZ_TMP1
1494
1495 PJI_POPULATE_PERIODS
1496 (
1497 p_period_type_id => p_period_type
1498 ,p_calendar_type => p_calendar_type
1499 ,p_period_id => p_period_id
1500 ,p_org_id => p_org_id
1501 ,x_return_status => x_return_status
1502 ,x_msg_count => x_msg_count
1503 ,x_msg_data => x_msg_data
1504 );
1505
1506 --Call the API to populate the table PJI_RES_UTILZ_TMP2
1507 PJI_POP_GRAPH_UTILZ_DATA
1508 (
1509 p_person_id => p_person_id
1510 ,p_period_id => p_period_id
1511 ,p_period_type => p_period_type
1512 ,p_calendar_type => p_calendar_type
1513 ,p_org_id => p_org_id
1514 ,x_return_status => x_return_status
1515 ,x_msg_count => x_msg_count
1516 ,x_msg_data => x_msg_data
1517 );
1518
1519 --Call the API to populate the table PJI_RES_UTILZ_TMP3
1520 PJI_POP_TABLE_UTILZ_DATA
1521 (
1522 p_person_id => p_person_id
1523 ,p_period_id => p_period_id
1524 ,p_period_type => p_period_type
1525 ,p_calendar_type => p_calendar_type
1526 ,p_org_id => p_org_id
1527 ,x_return_status => x_return_status
1528 ,x_msg_count => x_msg_count
1529 ,x_msg_data => x_msg_data
1530 );
1531
1532 COMMIT;
1533
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537
1538 END PJI_POP_UTILIZATION_DATA;
1539
1540 PROCEDURE GET_PERSON_FROM_RES
1541 (
1542 p_resource_id IN NUMBER
1543 ,x_person_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1544 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1545 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1546 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1547 )
1548 IS
1549 l_msg_index_out NUMBER; -- Bug Ref : 7010273
1550 BEGIN
1551 x_return_status := FND_API.G_RET_STS_SUCCESS;
1552
1553 SELECT NVL(person_id,-1)
1554 INTO x_person_id
1555 FROM pa_resource_txn_attributes
1556 WHERE resource_id = p_resource_id;
1557 -- Bug Ref : 7010273
1558 EXCEPTION
1559 WHEN NO_DATA_FOUND THEN
1560 x_return_status := FND_API.G_RET_STS_ERROR;
1561 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1562 p_msg_name => 'PA_SU_INFO_MISSING');
1563 x_msg_count := FND_MSG_PUB.Count_Msg;
1564 IF ( x_msg_count > 0 ) THEN
1565 PA_INTERFACE_UTILS_PUB.GET_MESSAGES ( p_encoded => FND_API.G_TRUE
1566 ,p_msg_index => 1
1567 ,p_data => x_msg_data
1568 ,p_msg_index_out => l_msg_index_out );
1569 END IF;
1570
1571 END GET_PERSON_FROM_RES;
1572
1573 END PJI_RESOURCE_UTILZ;
[Home] [Help]