1 PACKAGE BODY PJI_PMV_UTIL AS
2 -- $Header: PJIRX04B.pls 120.10.12000000.4 2007/03/12 06:04:17 pschandr ship $
3
4 -- Variable added by pschandr
5 -- This label holds the language in which the labels in other global variables are populated
6 -- and translated labels to be displayed in pmv reports.
7 G_User_Lang VARCHAR2(52):='US';
8
9 -- Variables Added by vmangulu
10 -- Following are the global variables used to cache the label codes
11 -- and translated labels to be displayed in pmv reports.
12 G_Measure_Short_Code Measure_Label_Code_Tbl;
13 G_Measure_Label Measure_Label_Tbl;
14
15 -- Following are global variables used to cache full/xtd translated
16 -- string for period types.
17 G_Year_Label VARCHAR2(8):='YEAR';
18 G_Quarter_Label VARCHAR2(8):='QTR';
19 G_Period_Label VARCHAR2(8):='PERIOD';
20 G_Week_Label VARCHAR2(8):='WEEK';
21
22 G_YTD_Label VARCHAR2(8):='YTD';
23 G_QTD_Label VARCHAR2(8):='QTD';
24 G_PTD_Label VARCHAR2(8):='PTD';
25 G_WTD_Label VARCHAR2(8):='WTD';
26
27 G_Prior_Label VARCHAR2(80);
28 G_Budget_Label VARCHAR2(80);
29
30 G_Labor_Units_LT VARCHAR2(30):='PJI_REPORT_LABOR_UNITS';
31
32 G_Graph_Labels_LT VARCHAR2(30):='PJI_PMV_GRAPH_LABELS';
33 G_Budget_Label_LC VARCHAR2(30):='PJI_BUDGET_LABEL';
34 G_Prior_Budget_Label_LC VARCHAR2(30):='PJI_PRIOR_LABEL';
35
36 G_FTE_Level VARCHAR2(8);
37
38 FUNCTION Get_Labor_Unit RETURN VARCHAR2
39 IS
40 l_labor_unit VARCHAR2(80);
41 BEGIN
42 SELECT lkp.meaning
43 INTO l_labor_unit
44 FROM pji_lookups lkp,
45 pji_system_settings setup
46 WHERE lkp.lookup_type = G_Labor_Units_LT
47 AND lkp.lookup_code = setup.report_labor_units;
48
49 RETURN l_labor_unit;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 RETURN 'Hours';
53 END Get_Labor_Unit;
54
55
56 /* ----------------------------------------------------------
57 Function: get_projects
58 This function concatenates all the projects of a
59 person which are non-administrative and the forecast_item
60 details have not been summarized, given a date.
61 ----------------------------------------------------------*/
62 FUNCTION get_projects ( p_person_id IN NUMBER
63 ,p_exp_org_id IN NUMBER
64 ,p_date IN DATE)
65 RETURN VARCHAR2
66 IS
67 l_projects VARCHAR2(500) := '';
68
69 CURSOR projects IS
70 select distinct proj.name
71 from pa_projects_all proj
72 ,pa_forecast_items fi
73 ,pa_forecast_item_details fid
74 where fi.item_date = trunc(p_date)
75 and proj.project_id = fi.project_id
76 and fi.person_id = p_person_id
77 and fi.expenditure_organization_id = p_exp_org_id
78 and fi.forecast_item_id = fid.forecast_item_id
79 and fid.pji_summarized_flag is null
80 and rownum = 1;
81
82 BEGIN
83
84 OPEN projects;
85 FETCH projects INTO l_projects;
86 CLOSE projects;
87
88 RETURN l_projects;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 RETURN null;
93 END get_projects;
94
95
96 /* ------------------------------------------------------
97 Function: Get_Available_From
98 This function returns the date that the person
99 is available_from, given the from_date, the as_of_date,
100 and the availability threshold.
101 ------------------------------------------------------*/
102
103 FUNCTION Get_Available_From (p_person_id IN NUMBER
104 ,p_exp_org_id IN NUMBER
105 ,p_from_date IN NUMBER
106 ,p_as_of_date IN NUMBER
107 ,p_threshold IN NUMBER)
108 RETURN VARCHAR2
109 IS
110 l_j_date NUMBER;
111 l_date DATE;
112 l_start_time NUMBER;
113 l_end_time NUMBER;
114
115 BEGIN
116
117 l_date := to_date(p_from_date, 'J');
118
119 -- subtract a year from the from_date value
120 l_start_time := to_number(to_char(ADD_MONTHS(l_date,(-12)), 'J'));
121 l_end_time := p_as_of_date;
122
123 -- depending on the availability threshold values, the following
124 -- read the data using available_res_count_bkt#_s respectively
125 -- get the max time when the resource is not available
126 -- (where the available_res_count_bkt#_s will be zero)
127
128 IF p_threshold = 1 THEN
129
130 SELECT max(time_id)
131 INTO l_j_date
132 FROM pji_rm_res_f
133 WHERE person_id = p_person_id
134 and expenditure_organization_id = p_exp_org_id
135 and period_type_id = 1
136 and time_id between l_start_time and l_end_time
137 and available_res_count_bkt1_s = 0;
138
139 ELSIF p_threshold = 2 THEN
140
141 SELECT max(time_id)
142 INTO l_j_date
143 FROM pji_rm_res_f
144 WHERE person_id = p_person_id
145 and expenditure_organization_id = p_exp_org_id
146 and period_type_id = 1
147 and time_id between l_start_time and l_end_time
148 and available_res_count_bkt2_s = 0;
149
150 ELSIF p_threshold = 3 THEN
151
152 SELECT max(time_id)
153 INTO l_j_date
154 FROM pji_rm_res_f
155 WHERE person_id = p_person_id
156 and expenditure_organization_id = p_exp_org_id
157 and period_type_id = 1
158 and time_id between l_start_time and l_end_time
159 and available_res_count_bkt3_s = 0;
160
161 ELSIF p_threshold = 4 THEN
162
163 SELECT max(time_id)
164 INTO l_j_date
165 FROM pji_rm_res_f
166 WHERE person_id = p_person_id
167 and expenditure_organization_id = p_exp_org_id
168 and period_type_id = 1
169 and time_id between l_start_time and l_end_time
170 and available_res_count_bkt4_s = 0;
171
172 ELSIF p_threshold = 5 THEN
173
174 SELECT max(time_id)
175 INTO l_j_date
176 FROM pji_rm_res_f
177 WHERE person_id = p_person_id
178 and expenditure_organization_id = p_exp_org_id
179 and period_type_id = 1
180 and time_id between l_start_time and l_end_time
181 and available_res_count_bkt5_s = 0;
182
183 END IF;
184
185 -- if the value is equal to the start or end time, then the code
186 -- could not find the available from date (cannot go beyond
187 -- the time limit), return null
188 IF l_j_date = l_start_time or l_j_date = l_end_time or l_j_date is null THEN
189 RETURN NULL;
190
191 ELSE
192 -- return the date+1 because the next day, the resource
193 -- is available
194 RETURN to_char(to_date(l_j_date, 'J')+1);
195 END IF;
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 RETURN null;
200
201 WHEN OTHERS THEN
202 RETURN null;
203
204 END Get_Available_From;
205
206
207 /* ------------------------------------------------------
208 Function: Get_Next_Asgmt_Date
209 This function returns the date that the person has the
210 next assignment, given the to_date, the as_of_date,
211 and the availability threshold.
212 ------------------------------------------------------*/
213 FUNCTION Get_Next_Asgmt_Date (p_person_id IN NUMBER
214 ,p_exp_org_id IN NUMBER
215 ,p_to_date IN NUMBER
216 ,p_as_of_date IN NUMBER
217 ,p_threshold IN NUMBER)
218 RETURN VARCHAR2
219 IS
220 l_j_date NUMBER;
221 l_date DATE;
222 l_start_time NUMBER;
223 l_end_time NUMBER;
224
225 BEGIN
226
227 l_date := to_date(p_to_date, 'J');
228 l_start_time := p_as_of_date;
229
230 -- add a year to the to_date value
231 l_end_time := to_number(to_char(ADD_MONTHS(l_date, 12), 'J'));
232
233 -- depending on the availability threshold values, the following
234 -- read the data using available_res_count_bkt#_s respectively
235 -- get the min time when the resource is not available
236 -- (where the available_res_count_bkt#_s will be zero)
237
238 IF p_threshold = 1 THEN
239
240 SELECT min(time_id)
241 INTO l_j_date
242 FROM pji_rm_res_f
243 WHERE person_id = p_person_id
244 and expenditure_organization_id = p_exp_org_id
245 and period_type_id = 1
246 and time_id between l_start_time and l_end_time
247 and available_res_count_bkt1_s = 0;
248
249 ELSIF p_threshold = 2 THEN
250
251 SELECT min(time_id)
252 INTO l_j_date
253 FROM pji_rm_res_f
254 WHERE person_id = p_person_id
255 and expenditure_organization_id = p_exp_org_id
256 and period_type_id = 1
257 and time_id between l_start_time and l_end_time
258 and available_res_count_bkt2_s = 0;
259
260 ELSIF p_threshold = 3 THEN
261
262 SELECT min(time_id)
263 INTO l_j_date
264 FROM pji_rm_res_f
265 WHERE person_id = p_person_id
266 and expenditure_organization_id = p_exp_org_id
267 and period_type_id = 1
268 and time_id between l_start_time and l_end_time
269 and available_res_count_bkt3_s = 0;
270
271 ELSIF p_threshold = 4 THEN
272
273 SELECT min(time_id)
274 INTO l_j_date
275 FROM pji_rm_res_f
276 WHERE person_id = p_person_id
277 and expenditure_organization_id = p_exp_org_id
278 and period_type_id = 1
279 and time_id between l_start_time and l_end_time
280 and available_res_count_bkt4_s = 0;
281
282 ELSIF p_threshold = 5 THEN
283
284 SELECT min(time_id)
285 INTO l_j_date
286 FROM pji_rm_res_f
287 WHERE person_id = p_person_id
288 and expenditure_organization_id = p_exp_org_id
289 and period_type_id = 1
290 and time_id between l_start_time and l_end_time
291 and available_res_count_bkt5_s = 0;
292
293 END IF;
294
295
296 -- if the value is equal to the start or end time, then the code
297 -- could not find the next assignment date (cannot go beyond
298 -- the time limit), return null
299 IF l_j_date = l_end_time or l_j_date = l_start_time or l_j_date is null THEN
300 RETURN NULL;
301
302 ELSE
303 -- return the date found (because resource has assignment on that
304 -- day as availability is zero)
305 RETURN to_char(to_date(l_j_date, 'J'));
306 END IF;
307
308
309 EXCEPTION
310 WHEN NO_DATA_FOUND THEN
311 RETURN null;
312
313 WHEN OTHERS THEN
314 RETURN null;
315
316 END Get_Next_Asgmt_Date;
317
318 /* ------------------------------------------------------
319 Function : GetTimeLevelLabel
320 This function returns the concatenated attribute label
321 name long. The function concatenates the label names with
322 year/quarter/period or YTD/QTD/PTD. This function is called
323 from the PMV report and relies on cached values of variables
324 called in the package init section.
325 ------------------------------------------------------*/
326 -- Function GetTimeLevelLabel Follows
327 FUNCTION GetTimeLevelLabel( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
328 , p_Label_Code VARCHAR2 DEFAULT NULL
329 , p_Bit_Mode VARCHAR2 DEFAULT '1')
330 RETURN VARCHAR2 IS
331 l_Time_Level_Value VARCHAR2(80);
332 l_Time_Level VARCHAR2(8);
333 l_Label VARCHAR2(80);
334 l_Bit_Mode NUMBER:=TO_NUMBER(p_Bit_Mode);
335 l_FTE_Level VARCHAR2(8);
336 l_User_Lang VARCHAR2(52);
337 BEGIN
338 --Bug 5598041: Invoke Init procedure if the labels are not populated in the correct language
339 BEGIN
340 SELECT userenv('LANG') INTO l_User_Lang FROM dual;
341 IF G_User_Lang <> l_User_Lang THEN
342 Init;
343 END IF;
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 NULL;
347 END;
348
349 FOR i IN 1..p_page_parameter_tbl.COUNT
350 LOOP
351 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
352 l_Time_Level_Value:=p_page_parameter_tbl(i).parameter_value;
353 END IF;
354 END LOOP;
355 /*
356 ** Determine the time level as full translated strings like
357 ** 'Year/Quarter/Period/Week' for forecast and budget measures.
358 ** For all the other cases consider the time level as to date
359 ** like 'YTD/QTD/PTD/WTD'.
360 */
361 IF l_time_level_value IS NOT NULL THEN
362 IF BITAND(l_Bit_Mode, 2) = 2 THEN
363 CASE (l_time_level_value)
364 WHEN 'FII_TIME_ENT_YEAR' THEN
365 l_Time_Level:='_'||G_Year_Label;
366 WHEN 'FII_TIME_ENT_QTR' THEN
367 l_Time_Level:='_'||G_Quarter_Label;
368 WHEN 'FII_TIME_ENT_PERIOD' THEN
369 l_Time_Level:='_'||G_Period_Label;
370 WHEN 'FII_TIME_CAL_YEAR' THEN
371 l_Time_Level:='_'||G_Year_Label;
372 WHEN 'FII_TIME_CAL_QTR' THEN
373 l_Time_Level:='_'||G_Quarter_Label;
374 WHEN 'FII_TIME_CAL_PERIOD' THEN
375 l_Time_Level:='_'||G_Period_Label;
376 WHEN 'PJI_TIME_PA_PERIOD' THEN
377 l_Time_Level:='_'||G_Period_Label;
378 WHEN 'FII_TIME_WEEK' THEN
379 l_Time_Level:='_'||G_Week_Label;
380 END CASE;
381 ELSIF BITAND(l_Bit_Mode, 1) = 1 THEN
382 CASE (l_time_level_value)
383 WHEN 'FII_TIME_ENT_YEAR' THEN
384 l_Time_Level:='_'||G_YTD_Label;
385 WHEN 'FII_TIME_ENT_QTR' THEN
386 l_Time_Level:='_'||G_QTD_Label;
387 WHEN 'FII_TIME_ENT_PERIOD' THEN
388 l_Time_Level:='_'||G_PTD_Label;
389 WHEN 'FII_TIME_CAL_YEAR' THEN
390 l_Time_Level:='_'||G_YTD_Label;
391 WHEN 'FII_TIME_CAL_QTR' THEN
392 l_Time_Level:='_'||G_QTD_Label;
393 WHEN 'FII_TIME_CAL_PERIOD' THEN
394 l_Time_Level:='_'||G_PTD_Label;
395 WHEN 'PJI_TIME_PA_PERIOD' THEN
396 l_Time_Level:='_'||G_PTD_Label;
397 WHEN 'FII_TIME_WEEK' THEN
398 l_Time_Level:='_'||G_WTD_Label;
399 END CASE;
400 ELSE
401 l_Time_Level:='';
402 END IF;
403 END IF;
404
405 IF BITAND(l_Bit_Mode, 4) = 4 THEN
406 l_FTE_Level:='_'||G_FTE_Level;
407 ELSE
408 l_FTE_Level:='';
409 END IF;
410
411 -- return 'PJI_MSR_'||p_Label_Code||l_Time_Level||l_FTE_Level;
412
413 /*
414 ** Lookup the translated value of the label code
415 ** in the cached global variable.
416 */
417 IF p_Label_Code IS NOT NULL THEN
418 FOR i in 1..G_Measure_Short_Code.LAST LOOP
419 IF 'PJI_MSR_'||p_Label_Code||l_Time_Level||l_FTE_Level = G_Measure_Short_Code(i) THEN
420 l_Label := G_Measure_Label(i);
421 END IF;
422 END LOOP;
423 END IF;
424
425
426 /*
427 ** Return the concatenated values of translated time level
428 ** and the translated label code.
429 */
430 RETURN l_Label;
431 EXCEPTION
432 WHEN OTHERS THEN
433 RETURN NULL;
434 END GetTimeLevelLabel;
435 -- Function GetTimeLevelLabel ends here
436
437 /* ------------------------------------------------------
438 Function : GetPriorLabel
439 This function returns the concatenated attribute label
440 name long. The function concatenates the label names with
441 year/quarter/period or YTD/QTD/PTD. This function is called
442 from the PMV report and relies on cached values of variables
443 called in the package init section.
444 ------------------------------------------------------*/
445 -- Function GetPriorLabel Follows
446 FUNCTION GetPriorLabel( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL )
447 RETURN VARCHAR2 IS
448 l_Label VARCHAR2(80);
449 l_Time_Comparision VARCHAR2(30);
450 l_User_Lang VARCHAR2(52);
451 BEGIN
452 --Bug 5598041: Invoke Init procedure if labels are not populated in the correct language
453 BEGIN
454 SELECT userenv('LANG') INTO l_User_Lang FROM dual;
455 IF G_User_Lang <> l_User_Lang THEN
456 Init;
457 END IF;
458 EXCEPTION
459 WHEN NO_DATA_FOUND THEN
460 NULL;
461 END;
462
463 FOR i IN 1..p_page_parameter_tbl.COUNT
464 LOOP
465 IF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
466 l_Time_Comparision:=p_page_parameter_tbl(i).parameter_value;
467 END IF;
468 END LOOP;
469
470 IF l_Time_Comparision = 'BUDGET' THEN
471 l_Label:=G_Budget_Label;
472 ELSE
473 l_Label:=G_Prior_Label;
474 END IF;
475
476 /*
477 ** Return the correct label back to report.
478 */
479 RETURN l_Label;
480 EXCEPTION
481 WHEN OTHERS THEN
482 RETURN NULL;
483 END GetPriorLabel;
484 -- Function GetPriorLabel ends here
485
486
487
488 /* ----------------------------------------------------------------------
489 Function : RA2_RA5_URL
490 This API is used to concatenate drill across URL link from RA2 to RA5
491 report. It determines the from, to and current date values for RA5.
492 ----------------------------------------------------------------------*/
493
494 FUNCTION RA2_RA5_URL (p_date IN NUMBER,
495 p_week IN VARCHAR2,
496 p_organization IN VARCHAR2,
497 p_operating_unit IN VARCHAR2,
498 p_threshold IN NUMBER)
499
500 RETURN VARCHAR IS
501 l_date DATE;
502 l_is_date DATE;
503 l_start_date DATE;
504 l_Url VARCHAR2(400);
505 l_value VARCHAR2(100);
506
507 CURSOR get_week(l_is_date IN DATE) IS
508 SELECT value, start_date
509 FROM fii_time_week_v
510 WHERE l_is_date between start_date and end_date;
511
512 BEGIN
513
514 l_date:= to_date(p_date, 'J');
515
516 -- get the corresponding week start_date
517 IF p_week = 'W0' THEN
518 l_is_date := l_date;
519 ELSIF p_week = 'W1' THEN
520 l_is_date := l_date + 7;
521 ELSIF p_week = 'W2' THEN
522 l_is_date := l_date + 14;
523 ELSIF p_week = 'W3' THEN
524 l_is_date := l_date + 21;
525 ELSIF p_week = 'W4' THEN
526 l_is_date := l_date + 28;
527 END IF;
528
529 OPEN get_week(l_is_date);
530 FETCH get_week INTO l_value, l_start_date;
531 CLOSE get_week;
532
533 --Bug 5603228, 5603194 Add nls_date_language parameter to to_date function call
534 l_Url := 'AS_OF_DATE=' ||to_char(l_start_date,'DD/MM/YYYY')
535 ||'&pFunctionName=PJI_REP_RA5'
536 ||'&PJI_REP_DIM_18='||to_char(to_date(l_value,'DD/MM/YYYY', 'nls_date_language = AMERICAN'),'DD/MM/YYYY')
537 ||'&PJI_REP_DIM_2='||p_organization
538 ||'&PJI_REP_DIM_1='||p_operating_unit
539 ||'&PJI_REP_DIM_28='||p_threshold
540 ||'&pParamIds=Y';
541
542
543
544 RETURN l_URL;
545 END RA2_RA5_URL;
546
547
548 /* -------------------------------------------------------------------------------------------------
549 Function : Drill_To_Proj_Perf_URL(PROJECT_ID, p_Currency_Record_Type, p_As_of_Date,p_Period_Type)
550 ------------------------------------------------------------------------------------------------*/
551
552
553
554 FUNCTION Drill_To_Proj_Perf_URL( PROJECT_ID IN NUMBER
555 ,p_Currency_Record_Type IN NUMBER
556 ,p_As_Of_Date IN NUMBER
557 ,p_Period_Type IN VARCHAR2)
558 RETURN VARCHAR2 IS
559 l_Url VARCHAR2(1000);
560 BEGIN
561
562 l_Url:= 'paFromPji=Y&pFunctionName=PJI_VIEW_PROJ_PERF'
563 ||'&paProjectId=' ||TO_CHAR(PROJECT_ID)
564 ||'&paAsOfDate=' ||TO_CHAR(p_As_Of_Date)
565 ||'&paCurrencyRecordType=' ||TO_CHAR(p_Currency_Record_Type)
566 ||'&paPeriodType=' ||p_Period_Type
567 ||'&paCstBudgetType=' ||PJI_UTILS.GET_SETUP_PARAMETER('COST_FP_TYPE_ID')
568 ||'&paCstForecastType='||PJI_UTILS.GET_SETUP_PARAMETER('COST_FORECAST_FP_TYPE_ID')
569 ||'&paRevBudgetType=' ||PJI_UTILS.GET_SETUP_PARAMETER('REVENUE_FP_TYPE_ID')
570 ||'&paRevForecastType='||PJI_UTILS.GET_SETUP_PARAMETER('REVENUE_FORECAST_FP_TYPE_ID');
571
572
573 -- dbms_output.put_line('URL = ' || l_Url);
574
575 RETURN l_Url;
576 END Drill_To_Proj_Perf_URL;
577
578
579
580 /* ----------------------------------------------------------------------
581 Function : RA4_RA5_URL
582 This API is used to concatenate drill across URL link from RA4 to RA5
583 report.
584 ----------------------------------------------------------------------*/
585
586 FUNCTION RA4_RA5_URL (p_week IN VARCHAR2,
587 p_organization IN VARCHAR2,
588 p_operating_unit IN VARCHAR2,
589 p_threshold IN NUMBER,
590 p_period_type IN VARCHAR2)
591
592 RETURN VARCHAR IS
593 l_start_date DATE;
594 l_Url VARCHAR2(1000);
595
596 BEGIN
597
598 IF p_period_type='FII_TIME_WEEK'
599 THEN
600
601 select start_date
602 into l_start_date
603 from fii_time_week_v
604 where value = p_week;
605
606 -- constructing the URL string
607 --Bug 5603228, 5603194 Add nls_date_language parameter to to_date function call
608 l_Url:= 'AS_OF_DATE=' ||to_char(l_start_date,'DD/MM/YYYY')
609 ||'&pFunctionName=PJI_REP_RA5'
610 ||'&PJI_REP_DIM_18='||to_char(to_date(p_week,'DD/MM/YYYY', 'nls_date_language = AMERICAN'),'DD/MM/YYYY')
611 ||'&PJI_REP_DIM_2=' ||p_organization
612 ||'&PJI_REP_DIM_28=' ||p_threshold
613 ||'&PJI_REP_DIM_1=' ||p_operating_unit
614 ||'&pParamIds=Y';
615
616 -- dbms_output.put_line('URL = ' || l_Url);
617
618 ELSIF p_period_type='PJI_TIME_PA_PERIOD'
619 THEN
620 L_URL:= NULL;
621 END IF;
622
623 RETURN l_Url;
624 END RA4_RA5_URL;
625
626 /* ----------------------------------------------------
627 Procedure : Redirect_RA2_RA5
628 This API is used to drill across from RA2 to RA5
629 report. It determines the from, to and current date
630 values for RA5.
631 ---------------------------------------------------- */
632
633 PROCEDURE Redirect_RA2_RA5 (p_date IN VARCHAR2,
634 p_week IN VARCHAR2,
635 p_organization IN VARCHAR2,
636 p_operating_unit IN VARCHAR2,
637 p_threshold IN VARCHAR2
638 )
639 IS
640 l_date DATE;
641 l_is_date DATE;
642 l_start_date DATE;
643 l_Url VARCHAR2(400);
644 l_value VARCHAR2(100);
645
646 -- modified to fix bug2505055 (due to fii bug)
647 -- get value from date between start date and end date
648 CURSOR get_week(l_is_date IN DATE) IS
649 SELECT value, start_date
650 FROM fii_time_week_v
651 WHERE l_is_date between start_date and end_date;
652
653 BEGIN
654
655 l_date := FND_DATE.CHARDATE_TO_DATE(p_date);
656
657 -- get the week id and week start date of the p_date parameter
658 /*
659 select week_id, week_start_date
660 into l_week_id, l_week_date
661 from fii_time_day
662 where report_date = l_date; */
663
664 -- get the corresponding week start_date
665 IF p_week = 'W0' THEN
666 l_is_date := l_date;
667 ELSIF p_week = 'W1' THEN
668 l_is_date := l_date + 7;
669 ELSIF p_week = 'W2' THEN
670 l_is_date := l_date + 14;
671 ELSIF p_week = 'W3' THEN
672 l_is_date := l_date + 21;
673 ELSIF p_week = 'W4' THEN
674 l_is_date := l_date + 28;
675 END IF;
676
677 -- get the dates for the beginning of the week (for FROM)
678 -- and the end of the week (for TO)
679 OPEN get_week(l_is_date);
680 FETCH get_week INTO l_value, l_start_date;
681 CLOSE get_week;
682
683 -- constructing the URL string
684 l_Url := '&PJI_REP_DIM_18_FROM=' || l_value ||
685 '&PJI_REP_DIM_18_TO=' || l_value ||
686 '&AS_OF_DATE=' || l_start_date ||
687 '&PJI_REP_DIM_2=' || p_organization ||
688 '&PJI_REP_DIM_28=' || p_threshold ||
689 '&PJI_REP_DIM_1=' || p_operating_unit;
690
691 --dbms_output.put_line('URL = ' || l_Url);
692
693 bisviewer_pub.showreport( pUrlString => 'pRegionCode=PJI_REP_RA5' || l_Url
694 ,pFunctionName => 'PJI_REP_RA5'
695 ,pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID)
696 ,pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID)
697 ,pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
698
699 END Redirect_RA2_RA5;
700
701
702 /* ----------------------------------------------------
703 Procedure : Redirect_RA1_RA4
704 This API is used to drill across from RA1 to RA4
705 report. It determines the as_of_date value for RA4.
706 This is a link for the % Available column.
707 ---------------------------------------------------- */
708 PROCEDURE Redirect_RA1_RA4 (p_organization IN VARCHAR2,
709 p_operating_unit IN VARCHAR2,
710 p_threshold IN VARCHAR2,
711 p_period_type IN VARCHAR2,
712 p_start_time IN VARCHAR2
713 )
714 IS
715 l_Url VARCHAR2(2000);
716 l_date DATE;
717
718 BEGIN
719
720 l_date := to_date(p_start_time, 'j');
721
722 l_Url := '&AS_OF_DATE=' || l_date ||
723 '&PJI_REP_DIM_2=' || p_organization ||
724 '&PJI_REP_DIM_28=' || p_threshold ||
725 '&PJI_PERIOD_TYPE=Enterprise Week&VIEW_BY=TIME+FII_TIME_WEEK';
726
727 IF p_operating_unit is not null then
728 l_Url := l_Url || '&PJI_REP_DIM_1=' || p_operating_unit;
729 END IF;
730
731 --dbms_output.put_line('URL = ' || l_Url);
732
733 bisviewer_pub.showreport('pRegionCode=PJI_REP_RA4' || l_Url
734 ,pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID)
735 ,pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID)
736 ,pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID)
737 ,pFunctionName => 'PJI_REP_RA4'
738 );
739
740 END Redirect_RA1_RA4;
741
742
743 PROCEDURE Redirect_RA4_RA5 (p_week IN VARCHAR2,
744 p_organization IN VARCHAR2,
745 p_operating_unit IN VARCHAR2,
746 p_threshold IN VARCHAR2
747 )
748 IS
749 l_start_date DATE;
750 l_Url VARCHAR2(400);
751
752 BEGIN
753 select start_date
754 into l_start_date
755 from fii_time_week_v
756 where value = p_week;
757
758 -- constructing the URL string
759 l_Url := '&PJI_REP_DIM_18_FROM=' || p_week || '&PJI_REP_DIM_18_TO=' || p_week ||
760 '&AS_OF_DATE=' || l_start_date || '&PJI_REP_DIM_2=' || p_organization ||
761 '&PJI_REP_DIM_28=' || p_threshold || '&PJI_REP_DIM_1=' || p_operating_unit;
762
763
764 --dbms_output.put_line('URL = ' || l_Url);
765
766 bisviewer_pub.showreport( pUrlString => 'pRegionCode=PJI_REP_RA5' || l_Url
767 ,pFunctionName => 'PJI_REP_RA5'
768 ,pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID)
769 ,pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID)
770 ,pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID));
771
772 END Redirect_RA4_RA5;
773
774
775 /* ----------------------------------------------------
776 Procedure : Redirect_RA1_RA5
777 This API is used to drill across from RA1 to RA5
778 report. This is a link for the Available
779 (Hours/Days/Week) column in RA1 report.
780 ---------------------------------------------------- */
781 PROCEDURE Redirect_RA1_RA5 (p_organization IN VARCHAR2,
782 p_operating_unit IN VARCHAR2,
783 p_threshold IN VARCHAR2,
784 p_period_type IN VARCHAR2,
785 p_start_time IN VARCHAR2,
786 p_end_time IN VARCHAR2
787 )
788 IS
789 l_Url VARCHAR2(400);
790 l_date DATE;
791 l_from_date DATE;
792 l_to_date DATE;
793 l_as_of_date DATE;
794 l_val_from VARCHAR2(100);
795 l_val_to VARCHAR2(100);
796 l_period_url VARCHAR2(100);
797
798 BEGIN
799
800 l_from_date := to_date(p_start_time, 'j');
801 l_to_date := to_date(p_end_time, 'j');
802
803 -- get the FROM and TO values for the Year using the dates passed
804 IF p_period_type = 'FII_TIME_ENT_YEAR' THEN
805
806 select name
807 into l_val_from
808 from fii_time_ent_year
809 where start_date = l_from_date;
810
811 select name
812 into l_val_to
813 from fii_time_ent_year
814 where end_date = l_to_date;
815
816 l_period_url := '&PJI_REP_DIM_11_FROM=' || l_val_from || '&PJI_REP_DIM_11_TO=' || l_val_to;
817
818 -- get the FROM and TO values for the Quarter using the dates passed
819 ELSIF p_period_type = 'FII_TIME_ENT_QTR' THEN
820
821 select name
822 into l_val_from
823 from fii_time_ent_qtr
824 where start_date = l_from_date;
825
826 select name
827 into l_val_to
828 from fii_time_ent_qtr
829 where end_date = l_to_date;
830
831 l_period_url := '&PJI_REP_DIM_12_FROM=' || l_val_from || '&PJI_REP_DIM_12_TO=' || l_val_to;
832
833 -- get the FROM and TO values for the Period using the dates passed
834 ELSIF p_period_type = 'FII_TIME_ENT_PERIOD' THEN
835
836 select name
837 into l_val_from
838 from fii_time_ent_period
839 where start_date = l_from_date;
840
841 select name
842 into l_val_to
843 from fii_time_ent_period
844 where end_date = l_to_date;
845
846 l_period_url := '&PJI_REP_DIM_13_FROM=' || l_val_from || '&PJI_REP_DIM_13_TO=' || l_val_to;
847
848 -- get the FROM and TO values for the Week using the dates passed
849 ELSIF p_period_type = 'FII_TIME_WEEK' THEN
850
851 select name
852 into l_val_from
853 from fii_time_week
854 where start_date = l_from_date;
855
856 select name
857 into l_val_to
858 from fii_time_week
859 where end_date = l_to_date;
860
861 l_period_url := '&PJI_REP_DIM_18_FROM=' || l_val_from || '&PJI_REP_DIM_18_TO=' || l_val_to;
862
863 -- get the FROM and TO values for the PA Period using the dates passed
864 ELSIF p_period_type = 'PJI_TIME_PA_PERIOD' THEN
865
866 select value
867 into l_val_from
868 from fii_time_cal_period_v
869 where start_date = l_from_date;
870
871 select value
872 into l_val_to
873 from fii_time_cal_period_v
874 where end_date = l_to_date;
875
876 l_period_url := '&PJI_REP_DIM_17_FROM=' || l_val_from || '&PJI_REP_DIM_17_TO=' || l_val_to;
877
878 END IF;
879
880 -- set the default current date for RA5 to sysdate
881 l_date := trunc(sysdate);
882
883 -- then check whether this sysdate value is between l_from_date and l_to_date
884 IF l_date >= l_from_date AND l_date <= l_to_date THEN
885 l_as_of_date := l_date;
886
887 ELSIF l_date < l_from_date THEN
888 -- sysdate is less than p_from, so set the current date as the
889 -- p_from date value
890 l_as_of_date := l_from_date;
891
892 ELSIF l_date > l_to_date THEN
893 -- sysdate is greater than p_to, so set the current date as the
894 -- p_to date value
895 l_as_of_date := l_to_date;
896
897 END IF;
898
899 l_Url := '&AS_OF_DATE=' || l_as_of_date ||
900 '&PJI_REP_DIM_2=' || p_organization ||
901 '&PJI_REP_DIM_28=' || p_threshold ||
902 l_period_url;
903
904 IF p_operating_unit is not null then
905 l_Url := l_Url || '&PJI_REP_DIM_1=' || p_operating_unit;
906 END IF;
907
908 --dbms_output.put_line('URL = ' || l_Url);
909
910 bisviewer_pub.showreport('pRegionCode=PJI_REP_RA5' || l_Url
911 ,pUserId => icx_sec.getID(icx_sec.PV_WEB_USER_ID)
912 ,pSessionId => icx_sec.getID(icx_sec.PV_SESSION_ID)
913 ,pRespId => icx_sec.getId(icx_sec.PV_RESPONSIBILITY_ID)
914 ,pFunctionName => 'PJI_REP_RA5'
915 );
916
917
918 END Redirect_RA1_RA5;
919
920 --Bug 4599990. This Procedure returns the details of the top organization in the PJI hierarchy over which the user
921 --has security permissions. The parameters are described below
922 --> x_top_org_id : Id of the top org over which the user has security permissions. The id will be 0 if the
923 -- user has permissions to see all the orgz
924 --> x_top_org_name : Name of the top org.This will be populated only if x_insert_top_org is Y
925 --> x_user_assmt_flag : If the top org is obtained from the security profile then the value will be 'N'
926 -- If the top org is obtained from the user assignment then the value will be 'Y'
927 --> x_insert_top_org : Flag indicating whether the user has permissions to the see the top org in the
928 -- hierarchy
929 PROCEDURE get_top_org_details
930 (x_top_org_id OUT nocopy per_security_profiles.organization_id%TYPE,
931 x_top_org_name OUT nocopy hr_all_organization_units_tl.name%TYPE,
932 x_user_assmt_flag OUT nocopy VARCHAR2,
933 x_insert_top_org_flag OUT nocopy VARCHAR2 )
934 IS
935 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
936 l_top_organization_id per_security_profiles.organization_id%TYPE;
937 l_view_all_org_flag per_security_profiles.view_all_organizations_flag%TYPE;
938 l_user_id NUMBER;
939 BEGIN
940
941 l_security_profile_id := fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
942 l_user_id := fnd_global.user_id;
943
944 SELECT organization_id,
945 view_all_organizations_flag ,
946 include_top_organization_flag
947 INTO l_top_organization_id,
948 l_view_all_org_flag,
949 x_insert_top_org_flag
950 FROM per_security_profiles
951 WHERE security_profile_id=l_security_profile_id;
952
953 x_user_assmt_flag := 'N';
954
955 IF l_view_all_org_flag = 'Y' THEN
956
957 l_top_organization_id :=0;
958
959 ELSIF l_top_organization_id IS NULL THEN
960
961 SELECT per.organization_id
962 INTO l_top_organization_id
963 FROM fnd_user fndu,
964 per_all_assignments_f per
965 WHERE fndu.user_id=l_user_id
966 AND fndu.employee_id=per.person_id
967 AND per.primary_flag='Y'
968 AND (SYSDATE BETWEEN per.effective_start_Date AND NVL(per.effective_end_date, SYSDATE + 1));
969
970 x_user_assmt_flag := 'Y';
971
972 END IF;
973
974 x_top_org_id := l_top_organization_id;
975
976 IF x_insert_top_org_flag = 'Y' THEN
977
978 SELECT name
979 INTO x_top_org_name
980 FROM hr_all_organization_units_tl
981 WHERE organization_id = x_top_org_id
982 AND language = USERENV('LANG');
983
984 END IF;
985
986 EXCEPTION
987 WHEN NO_DATA_FOUND THEN
988
989 x_top_org_id :=-1;
990 x_insert_top_org_flag:='N';
991
992 END get_top_org_details;
993
994
995 --Bug 4599990. Modified the code to handle the case where the security profile restricts the organizations
996 --to be displayed to that part of the hierarchy with organization selected in the user assignment as the top org
997 FUNCTION PJI_ORGANIZATION_LOV RETURN PJI_ORGANIZATION_LIST_TBL
998 IS
999 l_Organization_List PJI_ORGANIZATION_LIST_TBL := PJI_ORGANIZATION_LIST_TBL();
1000 l_Count NUMBER;
1001 --Bug 4599990.
1002 l_top_organization_id per_security_profiles.organization_id%TYPE;
1003 l_user_id NUMBER;
1004 l_top_org_name hr_all_organization_units_tl.name%TYPE;
1005 l_user_assmt_flag VARCHAR2(1);
1006 l_insert_top_org_flag VARCHAR2(1);
1007
1008 BEGIN
1009 --Important: Any change in logic made here should be updated in the function PJI_ORGANIZATION_EXISTS also
1010 --Bug 4599990.
1011 l_user_id := fnd_global.user_id;
1012
1013 get_top_org_details(
1014 x_top_org_id => l_top_organization_id,
1015 x_top_org_name => l_top_org_name,
1016 x_user_assmt_flag => l_user_assmt_flag,
1017 x_insert_top_org_flag => l_insert_top_org_flag);
1018
1019 --Bug 4599990. View All Orgz
1020 IF l_top_organization_id = 0 THEN
1021
1022 SELECT
1023 PJI_ORGANIZATION_LIST(
1024 orgd.organization_id_child
1025 , org.name
1026 , orgd.organization_id_parent)
1027 BULK COLLECT INTO l_Organization_List
1028 FROM
1029 per_org_structure_elements orgd
1030 , pji_system_settings pset
1031 , hr_all_organization_units_tl org
1032 WHERE 1=1
1033 AND orgd.org_structure_version_id = pset.org_structure_version_id
1034 AND orgd.organization_id_child = org.organization_id
1035 AND org.language = USERENV('LANG');
1036 l_Count:=l_Organization_List.COUNT;
1037
1038 --Bug 4599990. The security restricts the view to only a part of hierarchy with l_top_organization_id
1039 --as the top org
1040 ELSIF l_user_assmt_flag = 'N' THEN
1041
1042 SELECT
1043 PJI_ORGANIZATION_LIST(
1044 orgd.organization_id_child
1045 , org.name
1046 , orgd.organization_id_parent)
1047 BULK COLLECT INTO l_Organization_List
1048 FROM
1049 per_org_structure_elements orgd
1050 , pji_system_settings pset
1051 , per_organization_list sec
1052 , hr_all_organization_units_tl org
1053 WHERE 1=1
1054 AND orgd.org_structure_version_id = pset.org_structure_version_id
1055 AND orgd.organization_id_child = org.organization_id
1056 AND org.language = USERENV('LANG')
1057 AND orgd.organization_id_child = sec.organization_id
1058 AND sec.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
1059 l_Count:=l_Organization_List.COUNT;
1060
1061 --Bug 4599990. The security restricts the view to only a part of hierarchy with organization on the user
1062 --assignment as the top org
1063 ELSIF l_user_assmt_flag = 'Y' THEN
1064
1065 SELECT
1066 PJI_ORGANIZATION_LIST(
1067 orgd.organization_id_child
1068 , org.name
1069 , orgd.organization_id_parent)
1070 BULK COLLECT INTO l_Organization_List
1071 FROM
1072 per_org_structure_elements orgd
1073 , pji_system_settings pset
1074 , hr_all_organization_units_tl org
1075 WHERE 1=1
1076 AND orgd.org_structure_version_id = pset.org_structure_version_id
1077 AND orgd.organization_id_child = org.organization_id
1078 AND org.language = USERENV('LANG')
1079 START WITH orgd.organization_id_parent=l_top_organization_id
1080 CONNECT BY PRIOR orgd.organization_id_child = orgd.organization_id_parent;
1081 l_Count:=l_Organization_List.COUNT;
1082
1083 END IF;
1084
1085 --Bug 4599990. Insert top org only when the security profile setting allows the user to see top org.
1086 IF l_insert_top_org_flag= 'Y' THEN
1087
1088 --Bug 4599990.In case the user has permissions to see all the orgz then the top org has to be derived.
1089 IF l_top_organization_id=0 THEN
1090
1091 FOR cur_Top_Organization_List IN (
1092 SELECT
1093 orgd.organization_id_child organization_id_child
1094 , org.name name
1095 , orgd.organization_id_parent organization_id_parent
1096 FROM
1097 (select distinct organization_id_parent organization_id_child, NULL organization_id_parent from
1098 per_org_structure_elements p
1099 , pji_system_settings pset
1100 where p.org_structure_version_id = pset.org_structure_version_id
1101 and not exists
1102 (select 1 from
1103 per_org_structure_elements c
1104 where c.organization_id_child = p.organization_id_parent
1105 and c.org_structure_version_id = p.org_structure_version_id)) orgd
1106 , per_organization_list sec
1107 , hr_all_organization_units_tl org
1108 , per_security_profiles prof
1109 WHERE 1=1
1110 AND orgd.organization_id_child = org.organization_id
1111 AND org.language = USERENV('LANG')
1112 AND orgd.organization_id_child = sec.organization_id (+)
1113 AND sec.security_profile_id(+) = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
1114 AND fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL') IS NOT NULL
1115 AND prof.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
1116 AND ( prof.view_all_organizations_flag = 'Y'
1117 OR sec.organization_id IS NOT NULL))
1118 LOOP
1119 l_Count:=l_Count+1;
1120 l_Organization_List.EXTEND;
1121 l_Organization_List(l_Count):=PJI_ORGANIZATION_LIST(NULL,NULL,NULL);
1122 l_Organization_List(l_Count).ID:=cur_Top_Organization_List.organization_id_child;
1123 l_Organization_List(l_Count).VALUE:=cur_Top_Organization_List.name;
1124 l_Organization_List(l_Count).PARENT_ID:=NULL;
1125 NULL;
1126 END LOOP;
1127
1128 --Bug 4599990. If the top org is already derived earlier then it can be used here.
1129 ELSE
1130 l_Count:=l_Count+1;
1131 l_Organization_List.EXTEND;
1132 l_Organization_List(l_Count):=PJI_ORGANIZATION_LIST(NULL,NULL,NULL);
1133 l_Organization_List(l_Count).ID:=l_top_organization_id;
1134 l_Organization_List(l_Count).VALUE:=l_top_org_name;
1135 l_Organization_List(l_Count).PARENT_ID:=NULL;
1136
1137 END IF;
1138
1139 END IF;--IF l_insert_top_org_flag= 'Y' THEN
1140
1141 RETURN l_Organization_List;
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144 RETURN l_Organization_List;
1145 END PJI_ORGANIZATION_LOV;
1146
1147 --This function takes org_id as input parameter and returns the same org_id if it is
1148 --present in the list of PJI organizations. Otherwise it returns null
1149 --This helps to check the access to Performance Reporting from Project List page
1150 FUNCTION PJI_ORGANIZATION_EXISTS(p_org_id IN NUMBER) RETURN NUMBER
1151 IS
1152 l_top_organization_id per_security_profiles.organization_id%TYPE;
1153 l_org_id per_security_profiles.organization_id%TYPE;
1154 l_top_org_name hr_all_organization_units_tl.name%TYPE;
1155 l_user_assmt_flag VARCHAR2(1);
1156 l_insert_top_org_flag VARCHAR2(1);
1157
1158 BEGIN
1159
1160 get_top_org_details(
1161 x_top_org_id => l_top_organization_id,
1162 x_top_org_name => l_top_org_name,
1163 x_user_assmt_flag => l_user_assmt_flag,
1164 x_insert_top_org_flag => l_insert_top_org_flag);
1165
1166 BEGIN
1167 IF l_top_organization_id = 0 THEN
1168 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
1169 (SELECT orgd.organization_id_child
1170 FROM
1171 per_org_structure_elements orgd
1172 , pji_system_settings pset
1173 , hr_all_organization_units org
1174 WHERE 1=1
1175 AND orgd.org_structure_version_id = pset.org_structure_version_id
1176 AND orgd.organization_id_child = org.organization_id
1177 AND org.organization_id = p_org_id);
1178
1179 RETURN l_org_id;
1180 ELSIF l_user_assmt_flag = 'N' THEN
1181
1182 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
1183 (SELECT orgd.organization_id_child
1184 FROM
1185 per_org_structure_elements orgd
1186 , pji_system_settings pset
1187 , per_organization_list sec
1188 , hr_all_organization_units org
1189 WHERE 1=1
1190 AND orgd.org_structure_version_id = pset.org_structure_version_id
1191 AND orgd.organization_id_child = org.organization_id
1192 AND org.organization_id = p_org_id
1193 AND orgd.organization_id_child = sec.organization_id
1194 AND sec.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL'));
1195
1196 RETURN l_org_id;
1197 ELSIF l_user_assmt_flag = 'Y' THEN
1198
1199 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
1200 (SELECT orgd.organization_id_child
1201 FROM
1202 per_org_structure_elements orgd
1203 , pji_system_settings pset
1204 , hr_all_organization_units org
1205 WHERE 1=1
1206 AND orgd.org_structure_version_id = pset.org_structure_version_id
1207 AND orgd.organization_id_child = org.organization_id
1208 AND orgd.organization_id_child = p_org_id
1209 START WITH orgd.organization_id_parent=l_top_organization_id
1210 CONNECT BY PRIOR orgd.organization_id_child = orgd.organization_id_parent);
1211
1212 RETURN l_org_id;
1213 END IF;
1214
1215 EXCEPTION
1216 WHEN NO_DATA_FOUND THEN
1217 NULL;
1218 END;
1219
1220 --Bug 4599990. Insert top org only when the security profile setting allows the user to see top org.
1221 IF l_insert_top_org_flag= 'Y' THEN
1222
1223 --Bug 4599990.In case the user has permissions to see all the orgz then the top org has to be derived.
1224 IF l_top_organization_id=0 THEN
1225
1226 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
1227 (SELECT orgd.organization_id_child
1228 FROM
1229 (select distinct organization_id_parent organization_id_child, NULL organization_id_parent from
1230 per_org_structure_elements p
1231 , pji_system_settings pset
1232 where p.org_structure_version_id = pset.org_structure_version_id
1233 and not exists
1234 (select 1 from
1235 per_org_structure_elements c
1236 where c.organization_id_child = p.organization_id_parent
1237 and c.org_structure_version_id = p.org_structure_version_id)) orgd
1238 , per_organization_list sec
1239 , hr_all_organization_units org
1240 , per_security_profiles prof
1241 WHERE 1=1
1242 AND orgd.organization_id_child = org.organization_id
1243 AND orgd.organization_id_child = p_org_id
1244 AND orgd.organization_id_child = sec.organization_id (+)
1245 AND sec.security_profile_id(+) = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
1246 AND fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL') IS NOT NULL
1247 AND prof.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
1248 AND ( prof.view_all_organizations_flag = 'Y' OR sec.organization_id IS NOT NULL));
1249
1250 RETURN l_org_id;
1251
1252 ELSE
1253 IF l_top_organization_id = p_org_id THEN
1254 RETURN p_org_id;
1255 END IF;
1256
1257 END IF;
1258
1259 END IF;--IF l_insert_top_org_flag= 'Y' THEN
1260 RETURN l_org_id;
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 RETURN NULL;
1264 END PJI_ORGANIZATION_EXISTS;
1265
1266
1267 FUNCTION GET_JOB_LEVEL ( p_person_id NUMBER,
1268 p_as_of_date DATE )
1269 RETURN NUMBER
1270 IS
1271 l_job_level NUMBER;
1272 BEGIN
1273
1274 BEGIN
1275 SELECT resource_job_level
1276 INTO l_job_level
1277 FROM pa_resources_denorm
1278 WHERE person_id = p_person_id
1279 AND p_as_of_date between resource_effective_start_date and resource_effective_end_date;
1280
1281
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN
1284 l_job_level := null;
1285 END;
1286 RETURN l_job_level;
1287
1288 END;
1289
1290 /* ------------------------------------------------------
1291 Procedure : SEED_PJI_STATS
1292 -----------------------------------------------------*/
1293
1294 PROCEDURE SEED_PJI_STATS IS
1295
1296 BEGIN
1297
1298 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_CLS_DIM_TMP',10,10,10);
1299 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_ITD_DIM_TMP',10,10,10);
1300 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_JB_DIM_TMP',10,10,10);
1301 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_ORG_DIM_TMP',10,10,10);
1302 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_JL_DIM_TMP',10,10,10);
1303 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_ORGZ_DIM_TMP',10,10,10);
1304 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_PRJ_DIM_TMP',10,10,10);
1305 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_TIME_DIM_TMP',10,10,10);
1306 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_TCMP_DIM_TMP',10,10,10);
1307 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_UC_DIM_TMP',10,10,10);
1308 FND_STATS.SET_TABLE_STATS('PJI','PJI_PMV_WT_DIM_TMP',10,10,10);
1309
1310 END SEED_PJI_STATS;
1311
1312
1313 /* ------------------------------------------------------
1314 Procedure : Init
1315 This procedure is called only once when the package is
1316 instantiated. The procedure is used to populate the
1317 global variables and these variables are usable
1318 through out the session
1319 ------------------------------------------------------*/
1320 -- Procedure Init starts here
1321 PROCEDURE Init
1322 AS
1323 BEGIN
1324 --Bug 5598041: G_User_Lang will hold the language name in which the labels are being populated
1325 BEGIN
1326 SELECT userenv('LANG')
1327 INTO G_User_Lang
1328 FROM dual;
1329 EXCEPTION
1330 WHEN NO_DATA_FOUND THEN
1331 NULL;
1332 END;
1333
1334 BEGIN
1335 SELECT lookup_code, meaning
1336 BULK COLLECT INTO G_Measure_Short_Code, G_Measure_Label
1337 FROM pji_lookups
1338 WHERE lookup_type LIKE 'PJI_PMV_MSR_LABELS';
1339 EXCEPTION
1340 WHEN NO_DATA_FOUND THEN
1341 NULL;
1342 END;
1343
1344 BEGIN
1345 SELECT setup.report_labor_units
1346 INTO G_FTE_Level
1347 FROM pji_system_settings setup;
1348 EXCEPTION
1349 WHEN NO_DATA_FOUND THEN
1350 NULL;
1351 END;
1352
1353 BEGIN
1354 SELECT meaning INTO G_Budget_Label
1355 FROM pji_lookups
1356 WHERE lookup_type = G_Graph_Labels_LT
1357 AND lookup_code = G_Budget_Label_LC ;
1358 EXCEPTION
1359 WHEN NO_DATA_FOUND THEN
1360 NULL;
1361 END;
1362
1363 BEGIN
1364 SELECT meaning INTO G_Prior_Label
1365 FROM pji_lookups
1366 WHERE lookup_type = G_Graph_Labels_LT
1367 AND lookup_code = G_Prior_Budget_Label_LC ;
1368 EXCEPTION
1369 WHEN NO_DATA_FOUND THEN
1370 NULL;
1371 END;
1372
1373 END;
1374 -- Procedure Init ends here
1375
1376 PROCEDURE hide_parameter (
1377 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1378 hide OUT NOCOPY VARCHAR2)
1379 IS
1380 BEGIN
1381 hide := 'N';
1382 --Hide the dimension level if the operating unit has the value All
1383 FOR i IN 1..p_page_parameter_tbl.count LOOP
1384 IF (p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS') THEN
1385 IF (p_page_parameter_tbl(i).parameter_value = 'All') THEN
1386 hide := 'Y';
1387 ELSE
1388 hide := 'N';
1389 END IF;
1390 EXIT;
1391 END IF;
1392 END LOOP;
1393 END hide_parameter;
1394
1395 -- Initializing Procedure Init
1396 BEGIN
1397 Init;
1398
1399 END PJI_PMV_UTIL;
[Home] [Help]