1 PACKAGE BODY PJI_RESOURCE_UTILZ AS
2 /* $Header: PJIPR01B.pls 120.11 2012/02/17 11:58:09 tvala 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 pa_time_cal_name fiin,
57 pa_implementations_all imp, -- Bug Fix 8284858
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 pa_time_cal_name fiin,
76 pa_implementations_all imp -- Bug Fix 8284858
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 null;
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 =
153 lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
150 ( SELECT period_id as prior_id
151 FROM pji_time_mv lower
152 WHERE
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;Commented for bug 13011859 */
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 null;
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,
316 WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
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
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
472 );
469 , p_calendar_id
470 , g_last_summ_pd_name
471 , 1
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;Commented for bug 13011859 */
659
660 END PJI_POP_COMPLEX_UTILZ_DATA;
661
665 shown in the graph
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
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
818
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;
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)),
931 from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
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
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;Commented for bug 13011859 */
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
1089 --Populate the time tables
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
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 ( /* Modified the select for Bug 9898007 *
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)),0, null,
1144 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)),0, null,
1146 SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_s, cur2.conf_hrs_s))) as sched_utilz,
1147 SUM(nvl(total_wtd_res_hrs_a,0) + nvl(conf_wtd_res_hrs_s,0))/DECODE(SUM(DECODE(g_calc_mthd, 'CAPACITY', cur2.capacity_hrs -
1148 (nvl(cur2.reduce_capacity_hrs_a,0) + nvl(cur2.reduce_capacity_hrs_s,0)),
1149 ( nvl(cur2.total_hrs_a,0)+ nvl(cur2.conf_hrs_s,0)))), 0, NULL, SUM( DECODE(g_calc_mthd, 'CAPACITY', cur2.capacity_hrs -
1150 (nvl(cur2.reduce_capacity_hrs_a,0) + nvl(cur2.reduce_capacity_hrs_s,0)),
1151 (nvl(cur2.total_hrs_a,0)+ nvl(cur2.conf_hrs_s,0))))) AS exp_utilz,
1152 0 as prev_yr_utilz,
1153 pmv.order_by_id as id
1154 FROM pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
1155 WHERE person_id = p_person_id
1156 and pmv.period_type = cur2.period_type_id
1157 and pmv.calendar_type = cur2.calendar_type
1158 and pmv.id = cur2.time_id
1159 and pmv.id is not null
1160 group by pmv.name, pmv.order_by_id
1161 UNION ALL
1162 SELECT pmv.name as period_name,
1163 0 as actual_utilz,
1164 0 as sched_utilz,
1165 0 as exp_utilz,
1166 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)),
1167 0, null,SUM(DECODE(g_calc_mthd,'CAPACITY',cur2.capacity_hrs - cur2.reduce_capacity_hrs_a, cur2.total_hrs_a))) as prev_yr_utilz,
1168 pmv.order_by_id as id
1169 from pji_rm_res_f cur2, PJI_PMV_TIME_DIM_TMP pmv
1170 where person_id = p_person_id
1171 and pmv.period_type = cur2.period_type_id
1172 and pmv.calendar_type = cur2.calendar_type
1173 and pmv.prior_id = cur2.time_id
1174 and pmv.prior_id is not null
1175 group by pmv.name, pmv.order_by_id
1176 ) r1
1177 group by r1.period_name, r1.id;Commented for bug 13011859 */
1178
1179 END PJI_POP_TABLE_UTILZ_DATA;
1180
1181 /* This method is used to populate the periods
1182 that are shown to the user for selection on
1183 the Personal Resource Utilization Page.
1184
1185 The periods are populated in a global temporary
1186 table, and based on whether it is a PA period or
1187 a GL/Enterprise period, the data is shown on the
1188 page either in a LOV or a pop list respectively
1189 */
1190 PROCEDURE PJI_POPULATE_PERIODS
1191 (
1192 p_period_type_id IN NUMBER
1193 ,p_calendar_type IN VARCHAR2
1194 ,p_period_id IN NUMBER
1195 ,p_org_id IN NUMBER
1196 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1197 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1198 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1199 )
1200 IS
1201 l_calendar_id NUMBER;
1202 l_accnt_period_type VARCHAR2(15);
1203 l_sets_of_books_id NUMBER;
1204 l_sequence NUMBER;
1205 l_application_id NUMBER;
1206 l_same_pa_gl_period VARCHAR2(1);
1207 l_period_id_tbl N_TYPE_TAB;
1208 l_period_name_tbl V_TYPE_TAB;
1212 null;
1209 l_period_status_tbl V_TYPE_TAB;
1210 l_period_st_dt_tbl N_TYPE_TAB;
1211 BEGIN
1213 /* DELETE FROM PJI_RES_UTILZ_TMP1;
1214 --fnd_global.apps_initialize(1319, 55211, 1292);
1215
1216 IF (p_calendar_type = 'G') THEN
1217 SELECT pa_period_process_pkg.application_id
1218 INTO l_application_id
1219 FROM dual;
1220 ELSIF (p_calendar_type = 'P') THEN
1221 SELECT application_id
1222 INTO l_application_id
1223 FROM fnd_application
1224 WHERE application_short_name = 'PA';
1225 END IF;
1226
1227 GET_PERIOD_DATA
1228 (
1229 p_calendar_type => p_calendar_type
1230 ,p_org_id => p_org_id
1231 ,x_calendar_id => l_calendar_id
1232 ,x_accnt_period_type => l_accnt_period_type
1233 ,x_sets_of_books_id => l_sets_of_books_id
1234 ,x_return_status => x_return_status
1235 ,x_msg_count => x_msg_count
1236 ,x_msg_data => x_msg_data
1237 );
1238
1239 --Get the sequence for the year
1240 SELECT sequence
1241 INTO l_sequence
1242 FROM pji_time_mv
1243 WHERE
1244 period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_YEAR', 'FII_TIME_CAL_YEAR')
1245 AND calendar_id = l_calendar_id
1246 and year_id in
1247 (
1248 SELECT year_id
1249 FROM pji_time_mv
1250 WHERE period_id = p_period_id
1251 AND calendar_id = l_calendar_id
1252 );
1253
1254 --added for bug 12331139
1255 select same_pa_gl_period into
1256 l_same_pa_gl_period
1257 from pa_implementations_all
1258 where org_id = p_org_id;
1259
1260 --Get the period id and names (with status)
1261
1262 IF ( p_calendar_type = 'E') THEN
1263 --Get for Enterprise period
1264 --No status for enterprise period because
1265 --it is a period used for reporting and
1266 --not accounting or transaction
1267 SELECT period_id,
1268 period_name,
1269 null,
1270 to_char(period_start_date,'j')
1271 BULK COLLECT INTO
1272 l_period_id_tbl,
1273 l_period_name_tbl,
1274 l_period_status_tbl,
1275 l_period_st_dt_tbl
1276 FROM pji_time_mv
1277 WHERE calendar_id = l_calendar_id
1278 and period_type = 'FII_TIME_ENT_PERIOD'
1279 and year_id in
1280 (
1281 SELECT year_id
1282 FROM pji_time_mv
1283 WHERE period_type = 'FII_TIME_ENT_YEAR'
1284 and calendar_id = l_calendar_id
1285 and sequence in
1286 (l_sequence-1,l_sequence,l_sequence+1)
1287 )
1288 ORDER BY period_start_date;
1289 ELSE
1290 --Get for PA/GL period
1291 SELECT r1.period_id,
1292 r1.period_name,
1293 r2.show_status,
1294 to_char(r1.period_start_date,'j')
1295 BULK COLLECT INTO
1296 l_period_id_tbl,
1297 l_period_name_tbl,
1298 l_period_status_tbl,
1299 l_period_st_dt_tbl
1300 FROM
1301 (
1302 SELECT period_id,
1303 period_name,
1304 period_start_date
1305 FROM pji_time_mv
1306 WHERE calendar_id = l_calendar_id
1307 and period_type = 'FII_TIME_CAL_PERIOD'
1308 and year_id in
1309 (
1310 SELECT year_id
1311 FROM pji_time_mv
1312 WHERE period_type = 'FII_TIME_CAL_YEAR'
1313 and calendar_id = l_calendar_id
1314 and sequence in
1315 (l_sequence-1,l_sequence,l_sequence+1)
1316 )
1317 ) r1, gl_period_statuses_v r2
1318 where r2.period_type = l_accnt_period_type
1319 and r2.set_of_books_id = l_sets_of_books_id
1320 and r2.application_id = DECODE(l_same_pa_gl_period,'N',l_application_id, 8721) --12331139
1321 and r1.period_name = r2.period_name
1322 ORDER BY r1.period_start_date;
1323 END IF;
1324
1325 FORALL k IN 1.. l_period_id_tbl.count
1326 INSERT INTO PJI_RES_UTILZ_TMP1
1327 (
1328 period_id,
1329 period_name,
1330 period_status,
1331 period_start_date
1332 )
1333 VALUES
1334 (
1335 l_period_id_tbl(k),
1336 l_period_name_tbl(k),
1337 l_period_status_tbl(k),
1338 l_period_st_dt_tbl(k)
1339 );Commented for bug 13011859 */
1340
1341 END PJI_POPULATE_PERIODS;
1342
1343 /*
1344 This API is called from the page to get
1345 all the relevant initialization parameters
1346 that are needed to determine the business
1347 flow and hide/show regions and items on the
1348 page. This is the initialization API that is
1349 called from the Page
1350 */
1351
1352 PROCEDURE PJI_GET_PERIOD_PROFILE_DATA
1353 (
1354 p_org_id IN NUMBER
1355 ,x_period_type_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1356 ,x_calendar_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1357 ,x_person_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1358 ,x_period_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1359 ,x_curr_period_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1360 ,x_curr_period_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1361 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1362 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1363 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1364 )
1365 IS
1366 l_pa_res_util_def_pd_types VARCHAR2(30);
1367 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1368 l_calendar_id NUMBER;
1372 BEGIN
1369 l_accnt_period_type VARCHAR2(15);
1370 l_sets_of_books_id NUMBER;
1371 l_msg_index_out NUMBER; -- -- Bug Ref : 7010273
1373 x_return_status := FND_API.G_RET_STS_SUCCESS;
1374 --Get the site level profile option of the user
1375 SELECT fnd_profile.value('PA_RES_UTIL_DEF_PERIOD_TYPE')
1376 INTO l_pa_res_util_def_pd_types
1377 FROM dual;
1378
1379 IF (l_pa_res_util_def_pd_types = 'GL') THEN
1380 x_period_type_id := 32;
1381 x_calendar_type := 'G';
1382 END IF;
1383
1384 IF (l_pa_res_util_def_pd_types = 'PA') THEN
1385 x_period_type_id := 32;
1386 x_calendar_type := 'P';
1387 END IF;
1388
1389 IF (l_pa_res_util_def_pd_types = 'GE') THEN
1390 x_period_type_id := 32;
1391 x_calendar_type := 'E';
1392 END IF;
1393
1394 IF (p_org_id IS NULL) THEN
1395 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PJI',
1396 p_msg_name => 'PJI_MO_PROFILE_OPTION_NOT_FND');
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 RETURN;
1399 END IF;
1400
1401 --Get the resource id
1402 SELECT employee_id
1403 INTO x_person_id
1404 FROM fnd_user
1405 WHERE user_id = l_user_id;
1406
1407 --Get the period type profile option name
1408 SELECT meaning
1409 INTO x_period_type
1410 FROM pa_lookups
1411 WHERE lookup_type = 'PA_RES_UTIL_DEF_PERIOD_TYPES'
1412 and lookup_code = l_pa_res_util_def_pd_types;
1413
1414 --Get the current period id
1415 --First get the calendar_id
1416 GET_PERIOD_DATA
1417 (
1418 p_calendar_type => x_calendar_type
1419 ,p_org_id => p_org_id
1420 ,x_calendar_id => l_calendar_id
1421 ,x_accnt_period_type => l_accnt_period_type
1422 ,x_sets_of_books_id => l_sets_of_books_id
1423 ,x_return_status => x_return_status
1424 ,x_msg_count => x_msg_count
1425 ,x_msg_data => x_msg_data
1426 );
1427
1428 --Bug5872158 Make sure SYSDATE is lesser than or equal to the maximum period end date in pji_time_mv
1429 /* IF ( x_calendar_type = 'E') THEN
1430 --Get for Enterprise period
1431 SELECT period_id,
1432 period_name
1433 INTO x_curr_period_id,
1434 x_curr_period_name
1435 FROM pji_time_mv
1436 WHERE
1437 (SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
1438 FROM pji_time_mv
1439 WHERE 1=1
1440 AND calendar_id = l_calendar_id
1441 AND period_type = 'FII_TIME_ENT_PERIOD') BETWEEN period_start_date and period_end_date
1442 AND calendar_id = l_calendar_id
1443 AND period_type = 'FII_TIME_ENT_PERIOD';Commented for bug 13011859 *
1444 ELSIF ( x_calendar_type = 'G') THEN --Bug Fix 8284858
1445 --Get for GL period
1446 SELECT period_id,
1447 period_name
1448 INTO x_curr_period_id,
1449 x_curr_period_name
1450 FROM pji_time_mv
1451 WHERE
1452 (SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
1453 FROM pji_time_mv
1454 WHERE 1=1
1455 AND calendar_id = l_calendar_id
1456 AND period_type = 'FII_TIME_CAL_PERIOD') BETWEEN period_start_date and period_end_date
1457 AND calendar_id = l_calendar_id
1458 AND period_type = 'FII_TIME_CAL_PERIOD';
1459 ELSIF ( x_calendar_type = 'P') THEN --Added for Bug Fix 8284858
1460 --Get for PA period
1461 SELECT period_id,
1462 period_name
1463 INTO x_curr_period_id,
1464 x_curr_period_name
1465 FROM pji_time_mv
1466 WHERE
1467 (SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
1468 FROM pji_time_mv
1469 WHERE 1=1
1470 AND calendar_id = l_calendar_id
1471 AND period_type = 'FII_TIME_PA_PERIOD') BETWEEN period_start_date and period_end_date
1472 AND calendar_id = l_calendar_id
1473 AND period_type = 'FII_TIME_PA_PERIOD';
1474 END IF;Commented for bug 13011859 */
1475 -- Bug Ref : 7010273
1476 EXCEPTION
1477 WHEN NO_DATA_FOUND THEN
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1480 p_msg_name => 'PA_SU_INFO_MISSING');
1481 x_msg_count := FND_MSG_PUB.Count_Msg;
1482 IF ( x_msg_count > 0 ) THEN
1483 PA_INTERFACE_UTILS_PUB.GET_MESSAGES ( p_encoded => FND_API.G_TRUE
1484 ,p_msg_index => 1
1485 ,p_data => x_msg_data
1486 ,p_msg_index_out => l_msg_index_out );
1487 END IF;
1488 END PJI_GET_PERIOD_PROFILE_DATA;
1489
1490 /* This API provides a single point of contact
1491 that is called from the page to populate
1492 all kind of utilization data in global temporary
1493 tables.
1494
1495 This API calls the driver APIs to populate data
1496 in global temporary tables, that would be used
1497 by graph, table and period pop list / LOV on the
1498 Personal Resource Utilization Page
1499 */
1500 PROCEDURE PJI_POP_UTILIZATION_DATA
1501 (
1502 p_person_id IN NUMBER
1503 ,p_period_id IN NUMBER
1504 ,p_period_type IN NUMBER
1505 ,p_calendar_type IN VARCHAR2
1506 ,p_org_id IN NUMBER
1510 )
1507 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1508 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1509 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1511 IS
1512 BEGIN
1513 x_return_status := FND_API.G_RET_STS_SUCCESS;
1514
1515 --Call the API to populate the table PJI_RES_UTILZ_TMP1
1516
1517 PJI_POPULATE_PERIODS
1518 (
1519 p_period_type_id => p_period_type
1520 ,p_calendar_type => p_calendar_type
1521 ,p_period_id => p_period_id
1522 ,p_org_id => p_org_id
1523 ,x_return_status => x_return_status
1524 ,x_msg_count => x_msg_count
1525 ,x_msg_data => x_msg_data
1526 );
1527
1528 --Call the API to populate the table PJI_RES_UTILZ_TMP2
1529 PJI_POP_GRAPH_UTILZ_DATA
1530 (
1531 p_person_id => p_person_id
1532 ,p_period_id => p_period_id
1533 ,p_period_type => p_period_type
1534 ,p_calendar_type => p_calendar_type
1535 ,p_org_id => p_org_id
1536 ,x_return_status => x_return_status
1537 ,x_msg_count => x_msg_count
1538 ,x_msg_data => x_msg_data
1539 );
1540
1541 --Call the API to populate the table PJI_RES_UTILZ_TMP3
1542 PJI_POP_TABLE_UTILZ_DATA
1543 (
1544 p_person_id => p_person_id
1545 ,p_period_id => p_period_id
1546 ,p_period_type => p_period_type
1547 ,p_calendar_type => p_calendar_type
1548 ,p_org_id => p_org_id
1549 ,x_return_status => x_return_status
1550 ,x_msg_count => x_msg_count
1551 ,x_msg_data => x_msg_data
1552 );
1553
1554 COMMIT;
1555
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1559
1560 END PJI_POP_UTILIZATION_DATA;
1561
1562 PROCEDURE GET_PERSON_FROM_RES
1563 (
1564 p_resource_id IN NUMBER
1565 ,x_person_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1566 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1567 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1568 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1569 )
1570 IS
1571 l_msg_index_out NUMBER; -- Bug Ref : 7010273
1572 BEGIN
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 SELECT NVL(person_id,-1)
1576 INTO x_person_id
1577 FROM pa_resource_txn_attributes
1578 WHERE resource_id = p_resource_id;
1579 -- Bug Ref : 7010273
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1584 p_msg_name => 'PA_SU_INFO_MISSING');
1585 x_msg_count := FND_MSG_PUB.Count_Msg;
1586 IF ( x_msg_count > 0 ) THEN
1587 PA_INTERFACE_UTILS_PUB.GET_MESSAGES ( p_encoded => FND_API.G_TRUE
1588 ,p_msg_index => 1
1589 ,p_data => x_msg_data
1590 ,p_msg_index_out => l_msg_index_out );
1591 END IF;
1592
1593 END GET_PERSON_FROM_RES;
1594
1595 END PJI_RESOURCE_UTILZ;
[Home] [Help]