1 PACKAGE BODY hri_oltp_disc_wrkfrc AS
2 /* $Header: hriodwrk.pkb 115.4 2003/08/04 04:58:59 cbridge noship $ */
3
4 ---------------------------
5 -- Package global variables
6 ---------------------------
7 /* Values for last calc_abv lookup call */
8 g_bmt_code VARCHAR2(30);
9 g_bmt_meaning VARCHAR2(80);
10
11 g_last_bg_id per_business_groups.business_group_id%type;
12 g_last_formula_id ff_formulas_f.formula_id%type;
13 g_last_formula_name ff_formulas_f.formula_name%type;
14
15 /* Holds value passed to Discoverer when fast formulas do not exist */
16 g_no_valid_formula number := to_number(null);
17 /* Holds value passed to Discoverer when fast formulas do not exist or are not compiled */
18 g_no_valid_formula_id number := to_number(null);
19
20
21 /******************************************************************************/
22 /* Public function to determine the Id of a FastFormula */
23 /******************************************************************************/
24 FUNCTION get_formula_id(p_business_group_id IN NUMBER
25 ,p_formula_name IN VARCHAR2)
26 RETURN NUMBER IS
27
28 l_formula_id ff_formulas_f.formula_id%type := 0;
29
30 CURSOR customer_formula_csr IS
31 SELECT formula_id
32 FROM ff_formulas_x
33 WHERE formula_name = 'BUDGET_' || p_formula_name
34 AND business_group_id = p_business_group_id;
35
36 CURSOR template_formula_csr IS
37 SELECT formula_id
38 FROM ff_formulas_x
39 WHERE formula_name = 'TEMPLATE_' || p_formula_name
40 AND business_group_id IS NULL;
41
42 BEGIN
43
44 IF (p_formula_name IS NULL) OR (p_business_group_id IS NULL) THEN
45 /* Fast formula depends upon business group and formula name */
46 RETURN(0);
47 ELSE
48
49 IF (p_formula_name = g_last_formula_name) AND
50 (p_business_group_id = g_last_bg_id) THEN
51 RETURN(g_last_formula_id);
52 ELSE
53 OPEN customer_formula_csr;
54 FETCH customer_formula_csr INTO l_formula_id;
55 IF customer_formula_csr%FOUND THEN
56 CLOSE customer_formula_csr;
57 g_last_formula_name := p_formula_name;
58 g_last_bg_id := p_business_group_id;
59 g_last_formula_id := l_formula_id;
60 RETURN(l_formula_id);
61 ELSE
62 CLOSE customer_formula_csr;
63 OPEN template_formula_csr;
64 FETCH template_formula_csr INTO l_formula_id;
65 CLOSE template_formula_csr;
66 g_last_formula_name := p_formula_name;
67 g_last_bg_id := p_business_group_id;
68 g_last_formula_id := l_formula_id;
69 RETURN(l_formula_id);
70 END IF;
71 END IF;
72 END IF;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76
77 RETURN(0);
78
79 END get_formula_id;
80
81
82 /******************************************************************************/
83 /* Public function to determine the appropriate FastFormula Id to be used for */
84 /* calculating manpower actuals */
85 /******************************************************************************/
86 FUNCTION get_manpower_formula_id(p_business_group_id IN NUMBER
87 ,p_budget_measurement_code IN VARCHAR2)
88 RETURN NUMBER IS
89
90 l_return_value NUMBER;
91
92 BEGIN
93
94 l_return_value := get_formula_id
95 (p_business_group_id => p_business_group_id,
96 p_formula_name => p_budget_measurement_code);
97
98 IF (l_return_value = 0) THEN
99 RETURN (g_no_valid_formula_id);
100 ELSE
101 RETURN l_return_value;
102 END IF;
103
104 END get_manpower_formula_id;
105
106 /******************************************************************************/
107 /* Public function to calculate manpower actuals for a single assignment */
108 /******************************************************************************/
109 FUNCTION get_ff_actual_value(p_budget_id IN NUMBER
110 ,p_formula_id IN NUMBER
111 ,p_grade_id IN NUMBER DEFAULT NULL
112 ,p_job_id IN NUMBER DEFAULT NULL
113 ,p_organization_id IN NUMBER DEFAULT NULL
114 ,p_position_id IN NUMBER DEFAULT NULL
115 ,p_time_period_id IN NUMBER)
116 RETURN NUMBER IS
117
118 CURSOR budget_csr IS
119 SELECT
120 b.unit
121 ,b.business_group_id
122 FROM per_budgets b
123 WHERE b.budget_id = p_budget_id;
124
125 CURSOR time_period_csr IS
126 SELECT tp.end_date
127 FROM per_time_periods tp
128 WHERE tp.time_period_id = p_time_period_id;
129
130 /* 115.22 - replaced NVL logic with ORs */
131 /*----------------------------------------------------------------------------*/
132 /* Bug 2483207 - To fix high cost SQL it was necessary to split out the one */
133 /* main cursor into five cursors. Depending on the values of the parameters */
134 /* passed in a different cursor is used. The most selective parameter to use */
135 /* is p_position_id, so this is checked first, followed by grade, job and */
136 /* organization. If all parameters are null, then business group id is used. */
137 /* Note that the option of using this index is disabled in the former four */
138 /* cursors. This is to prevent an inefficient query plan being used. */
139 /*----------------------------------------------------------------------------*/
140 /* To be accessed if p_position_id is not null */
141 CURSOR pos_assignment_csr(
142 p_business_group_id NUMBER
143 ,p_grade_id NUMBER
144 ,p_job_id NUMBER
145 ,p_organization_id NUMBER
146 ,p_position_id NUMBER
147 ,p_period_end_date DATE) is
148 SELECT asg.assignment_id
149 FROM per_assignments_f asg
150 ,per_assignment_status_types ast
151 WHERE p_position_id = asg.position_id
152 AND (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
153 AND (p_job_id IS NULL OR asg.job_id = p_job_id)
154 AND (p_grade_id IS NULL OR asg.grade_id = p_grade_id)
155 AND asg.business_group_id + 0 = p_business_group_id
156 AND asg.assignment_type = 'E'
157 AND p_period_end_date BETWEEN asg.effective_start_date
158 AND asg.effective_end_date
159 AND asg.assignment_status_type_id = ast.assignment_status_type_id
160 AND ast.per_system_status = 'ACTIVE_ASSIGN';
161
162 /* If p_position_id is null, then use this if p_grade_id is not null */
163 CURSOR grd_assignment_csr(
164 p_business_group_id NUMBER
165 ,p_grade_id NUMBER
166 ,p_job_id NUMBER
167 ,p_organization_id NUMBER
168 ,p_period_end_date DATE) is
169 SELECT asg.assignment_id
170 FROM per_assignments_f asg
171 ,per_assignment_status_types ast
172 WHERE (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
173 AND (p_job_id IS NULL OR asg.job_id = p_job_id)
174 AND p_grade_id = asg.grade_id
175 AND asg.business_group_id + 0 = p_business_group_id
176 AND asg.assignment_type = 'E'
177 AND p_period_end_date BETWEEN asg.effective_start_date
178 AND asg.effective_end_date
179 AND asg.assignment_status_type_id = ast.assignment_status_type_id
180 AND ast.per_system_status = 'ACTIVE_ASSIGN';
181
182 /* If position and grade are null, access this with p_job_id if not null */
183 CURSOR job_assignment_csr(
184 p_business_group_id NUMBER
185 ,p_job_id NUMBER
186 ,p_organization_id NUMBER
187 ,p_period_end_date DATE) is
188 SELECT asg.assignment_id
189 FROM per_assignments_f asg
190 ,per_assignment_status_types ast
191 WHERE (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
192 AND p_job_id = asg.job_id
193 AND asg.business_group_id + 0 = p_business_group_id
194 AND asg.assignment_type = 'E'
195 AND p_period_end_date BETWEEN asg.effective_start_date
196 AND asg.effective_end_date
197 AND asg.assignment_status_type_id = ast.assignment_status_type_id
198 AND ast.per_system_status = 'ACTIVE_ASSIGN';
199
200 /* If position, grade and job are null, use p_organization_id if not null */
201 CURSOR org_assignment_csr(
202 p_business_group_id NUMBER
203 ,p_organization_id NUMBER
204 ,p_period_end_date DATE) is
205 SELECT asg.assignment_id
206 FROM per_assignments_f asg
207 ,per_assignment_status_types ast
208 WHERE p_organization_id = asg.organization_id
209 AND asg.business_group_id + 0 = p_business_group_id
210 AND asg.assignment_type = 'E'
211 AND p_period_end_date BETWEEN asg.effective_start_date
212 AND asg.effective_end_date
213 AND asg.assignment_status_type_id = ast.assignment_status_type_id
214 AND ast.per_system_status = 'ACTIVE_ASSIGN';
215
216 /* If organization, position, grade and job are null, use p_business_group_id */
217 CURSOR bgr_assignment_csr(
218 p_business_group_id NUMBER
219 ,p_period_end_date DATE) is
220 SELECT asg.assignment_id
221 FROM per_assignments_f asg
222 ,per_assignment_status_types ast
223 WHERE asg.business_group_id = p_business_group_id
224 AND asg.assignment_type = 'E'
225 AND p_period_end_date BETWEEN asg.effective_start_date
226 AND asg.effective_end_date
227 AND asg.assignment_status_type_id = ast.assignment_status_type_id
228 AND ast.per_system_status = 'ACTIVE_ASSIGN';
229
230 l_actuals NUMBER := 0;
231 l_actuals_total NUMBER := 0;
232 l_assignment_id per_all_assignments_f.assignment_id%type;
233 l_budget_type_code per_budgets.unit%type;
234 l_business_group_id per_budgets.business_group_id%type;
235 l_formula_id ff_formulas_f.formula_id%type;
236 l_grade_id per_budget_elements.grade_id%type;
237 l_job_id per_budget_elements.job_id%type;
238 l_organization_id per_budget_elements.organization_id%type;
239 l_period_end_date per_time_periods.end_date%type;
240 l_position_id per_budget_elements.position_id%type;
241
242 BEGIN
243
244 -- Return zero if any of the mandatory input parameters is null
245 IF (p_budget_id IS NULL) OR (p_formula_id IS NULL) OR (p_time_period_id IS NULL) THEN
246 RETURN 0;
247 END IF;
248
249 -- Get Budget Type Code and confirm budget exists
250 OPEN budget_csr;
251 FETCH budget_csr INTO l_budget_type_code, l_business_group_id;
252 IF budget_csr%FOUND THEN
253 CLOSE budget_csr;
254 ELSE
255 CLOSE budget_csr;
256 RETURN(0);
257 END IF;
258
259 -- Get End Date of the time period
260 OPEN time_period_csr;
261 FETCH time_period_csr INTO l_period_end_date;
262 IF time_period_csr%FOUND THEN
263 CLOSE time_period_csr;
264 ELSE
265 CLOSE time_period_csr;
266 RETURN(0);
267 END IF;
268
269 /* JRHYDE - 1999/11/05 Bug -
270 Call to get_ff_actual_value replaced with call to GetBudgetValue function
271 that tests the ABV first then if that does not exist it calls the
272 Fast Formula (thus being much quicker than calling the ff for each row) */
273
274 -- Sum the budget values for all relevant assignments
275
276 /*----------------------------------------------------------------------------*/
277 /* Bug 2483207 - To fix high cost SQL it was necessary to split out the one */
278 /* main cursor into five cursors. Depending on the values of the parameters */
279 /* passed in a different cursor is used. The most selective parameter to use */
280 /* is p_position_id, so this is checked first, followed by grade, job and */
281 /* organization. If all parameters are null, then business group id is used. */
282 /* Note that the option of using this index is disabled in the former four */
283 /* cursors. This is to prevent an inefficient query plan being used. */
284 /*----------------------------------------------------------------------------*/
285
286 IF (p_position_id IS NOT NULL) THEN
287
288 FOR assignment_rec in pos_assignment_csr(
289 l_business_group_id
290 ,p_grade_id
291 ,p_job_id
292 ,p_organization_id
293 ,p_position_id
294 ,l_period_end_date) LOOP
295
296 l_assignment_id := assignment_rec.assignment_id;
297
298 l_actuals := HrFastAnswers.GetBudgetValue(
299 p_budget_metric_formula_id => p_formula_id
300 ,p_budget_metric => l_budget_type_code
301 ,p_assignment_id => l_assignment_id
302 ,p_effective_date => l_period_end_date
303 ,p_session_date => sysdate );
304
305 l_actuals_total := l_actuals_total + l_actuals;
306
307 END LOOP;
308
309 ELSIF (p_grade_id IS NOT NULL) THEN
310
311 FOR assignment_rec in grd_assignment_csr(
312 l_business_group_id
313 ,p_grade_id
314 ,p_job_id
315 ,p_organization_id
316 ,l_period_end_date) LOOP
317
318 l_assignment_id := assignment_rec.assignment_id;
319
320 l_actuals := HrFastAnswers.GetBudgetValue(
321 p_budget_metric_formula_id => p_formula_id
322 ,p_budget_metric => l_budget_type_code
323 ,p_assignment_id => l_assignment_id
324 ,p_effective_date => l_period_end_date
325 ,p_session_date => sysdate );
326
327 l_actuals_total := l_actuals_total + l_actuals;
328
329 END LOOP;
330
331 ELSIF (p_job_id IS NOT NULL) THEN
332
333 FOR assignment_rec in job_assignment_csr(
334 l_business_group_id
335 ,p_job_id
336 ,p_organization_id
337 ,l_period_end_date
338 ) LOOP
339
340 l_assignment_id := assignment_rec.assignment_id;
341
342 l_actuals := HrFastAnswers.GetBudgetValue(
343 p_budget_metric_formula_id => p_formula_id
344 ,p_budget_metric => l_budget_type_code
345 ,p_assignment_id => l_assignment_id
346 ,p_effective_date => l_period_end_date
347 ,p_session_date => sysdate );
348
349 l_actuals_total := l_actuals_total + l_actuals;
350
351 END LOOP;
352
353 ELSIF (p_organization_id IS NOT NULL) THEN
354
355 FOR assignment_rec in org_assignment_csr(
356 l_business_group_id
357 ,p_organization_id
358 ,l_period_end_date) LOOP
359
360 l_assignment_id := assignment_rec.assignment_id;
361
362 l_actuals := HrFastAnswers.GetBudgetValue(
363 p_budget_metric_formula_id => p_formula_id
364 ,p_budget_metric => l_budget_type_code
365 ,p_assignment_id => l_assignment_id
366 ,p_effective_date => l_period_end_date
367 ,p_session_date => sysdate );
368
369 l_actuals_total := l_actuals_total + l_actuals;
370
371 END LOOP;
372
373 ELSE
374
375 FOR assignment_rec in bgr_assignment_csr(
376 l_business_group_id
377 ,l_period_end_date) LOOP
378
379 l_assignment_id := assignment_rec.assignment_id;
380
381 l_actuals := HrFastAnswers.GetBudgetValue(
382 p_budget_metric_formula_id => p_formula_id
383 ,p_budget_metric => l_budget_type_code
384 ,p_assignment_id => l_assignment_id
385 ,p_effective_date => l_period_end_date
386 ,p_session_date => sysdate );
387
388 l_actuals_total := l_actuals_total + l_actuals;
389
390 END LOOP;
391
392 END IF;
393
394 RETURN(l_actuals_total);
395
396 EXCEPTION
397 WHEN OTHERS THEN
398
399 RETURN(0);
400
401 END get_ff_actual_value;
402
403 /******************************************************************************/
404 /* Function to get an assignment budget value for an assignment */
405 /******************************************************************************/
406 FUNCTION get_asg_budget_value(p_budget_metric_formula_id IN NUMBER
407 ,p_budget_metric IN VARCHAR2
408 ,p_assignment_id IN NUMBER
409 ,p_effective_date IN DATE
410 ,p_session_date IN DATE )
411 RETURN NUMBER IS
412
413 l_budget_value NUMBER;
414
415 BEGIN
416
417 l_budget_value := HrFastAnswers.GetBudgetValue
418 ( p_budget_metric_formula_id => p_budget_metric_formula_id
419 , p_budget_metric => p_budget_metric
420 , p_assignment_id => p_assignment_id
421 , p_effective_date => p_effective_date
422 , p_session_date => p_session_date );
423
424 RETURN l_budget_value;
425
426 EXCEPTION
427 WHEN hrfastanswers.ff_not_compiled THEN
428 RETURN g_no_valid_formula;
429
430 WHEN hrfastanswers.ff_not_exist THEN
431 RETURN g_no_valid_formula;
432
433 END get_asg_budget_value;
434
435
436 /******************************************************************************/
437 /* cbridge, 28/06/2001 , pqh budgets support function for */
438 /* hrfv_workforce_budgets business view */
439 /* Public function to calculate workforce actuals for a single assignment */
440 /* using new PQH budgets schema model */
441 /* bug enhancement 1317484 */
442 /******************************************************************************/
443 FUNCTION get_ff_actual_value_pqh
444 (p_budget_id IN NUMBER
445 ,p_business_group_id IN NUMBER
446 ,p_grade_id IN NUMBER DEFAULT NULL
447 ,p_job_id IN NUMBER DEFAULT NULL
448 ,p_organization_id IN NUMBER DEFAULT NULL
449 ,p_position_id IN NUMBER DEFAULT NULL
450 ,p_time_period_id IN NUMBER
451 ,p_budget_metric IN VARCHAR2
452 )
453 RETURN NUMBER IS
454
455 cursor budget_csr is
456 select pst1.system_type_cd unit1_name
457 , pst2.system_type_cd unit2_name
458 , pst3.system_type_cd unit3_name
459 , bgt.business_group_id business_group_id
460 from pqh_budgets bgt
461 , per_shared_types_vl pst1
462 , per_shared_types_vl pst2
463 , per_shared_types_vl pst3
464 where bgt.budget_id = p_budget_id
465 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
466 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
467 AND bgt.budget_unit3_id = pst3.shared_type_id (+);
468
469 cursor time_period_csr is
470 select tp.end_date
471 from per_time_periods tp, pqh_budget_periods bpr
472 where bpr.budget_period_id = p_time_period_id
473 and tp.time_period_id = bpr.end_time_period_id;
474
475 /* 115.22 - replaced NVL logic with ORs */
476 /*----------------------------------------------------------------------------*/
477 /* Bug 2483207 - To fix high cost SQL it was necessary to split out the one */
478 /* main cursor into five cursors. Depending on the values of the parameters */
479 /* passed in a different cursor is used. The most selective parameter to use */
480 /* is p_position_id, so this is checked first, followed by grade, job and */
481 /* organization. If all parameters are null, then business group id is used. */
482 /* Note that the option of using this index is disabled in the former four */
483 /* cursors. This is to prevent an inefficient query plan being used. */
484 /*----------------------------------------------------------------------------*/
485 /* To be used if p_position_id is not null */
486 cursor pos_assignment_csr(
487 p_business_group_id NUMBER
488 ,p_grade_id NUMBER
489 ,p_job_id NUMBER
490 ,p_organization_id NUMBER
491 ,p_position_id NUMBER
492 ,p_period_end_date DATE
493 ) is
494 select asg.assignment_id
495 from per_assignments_f asg
496 ,per_assignment_status_types ast
497 where (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
498 and p_position_id = asg.position_id
499 and (p_job_id IS NULL OR asg.job_id = p_job_id)
500 and (p_grade_id IS NULL OR asg.grade_id = p_grade_id)
501 and asg.business_group_id + 0 = p_business_group_id
502 and asg.assignment_type = 'E'
503 and p_period_end_date between asg.effective_start_date and
504 asg.effective_end_date
505 and asg.assignment_status_type_id = ast.assignment_status_type_id
506 and ast.per_system_status = 'ACTIVE_ASSIGN';
507
508 /* To be used if p_grade_id is not null */
509 cursor grd_assignment_csr(
510 p_business_group_id NUMBER
511 ,p_grade_id NUMBER
512 ,p_job_id NUMBER
513 ,p_organization_id NUMBER
514 ,p_period_end_date DATE
515 ) is
516 select asg.assignment_id
517 from per_assignments_f asg
518 ,per_assignment_status_types ast
519 where (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
520 and (p_job_id IS NULL OR asg.job_id = p_job_id)
521 and p_grade_id = asg.grade_id
522 and asg.business_group_id + 0 = p_business_group_id
523 and asg.assignment_type = 'E'
524 and p_period_end_date between asg.effective_start_date and
525 asg.effective_end_date
526 and asg.assignment_status_type_id = ast.assignment_status_type_id
527 and ast.per_system_status = 'ACTIVE_ASSIGN';
528
529 /* To be used if p_job_id is not null */
530 cursor job_assignment_csr(
531 p_business_group_id NUMBER
532 ,p_job_id NUMBER
533 ,p_organization_id NUMBER
534 ,p_period_end_date DATE
535 ) is
536 select asg.assignment_id
537 from per_assignments_f asg
538 ,per_assignment_status_types ast
539 where (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
540 and p_job_id = asg.job_id
541 and asg.business_group_id + 0 = p_business_group_id
542 and asg.assignment_type = 'E'
543 and p_period_end_date between asg.effective_start_date and
544 asg.effective_end_date
545 and asg.assignment_status_type_id = ast.assignment_status_type_id
546 and ast.per_system_status = 'ACTIVE_ASSIGN';
547
548 /* To be used if p_organization_id is not null */
549 cursor org_assignment_csr(
550 p_business_group_id NUMBER
551 ,p_organization_id NUMBER
552 ,p_period_end_date DATE
553 ) is
554 select asg.assignment_id
555 from per_assignments_f asg
556 ,per_assignment_status_types ast
557 where p_organization_id = asg.organization_id
558 and asg.business_group_id + 0 = p_business_group_id
559 and asg.assignment_type = 'E'
560 and p_period_end_date between asg.effective_start_date and
561 asg.effective_end_date
562 and asg.assignment_status_type_id = ast.assignment_status_type_id
563 and ast.per_system_status = 'ACTIVE_ASSIGN';
564
565 /* To be used if all parameters are null */
566 cursor bgr_assignment_csr(
567 p_business_group_id NUMBER
568 ,p_period_end_date DATE
569 ) is
570 select asg.assignment_id
571 from per_assignments_f asg
572 ,per_assignment_status_types ast
573 where asg.business_group_id = p_business_group_id
574 and asg.assignment_type = 'E'
575 and p_period_end_date between asg.effective_start_date and
576 asg.effective_end_date
577 and asg.assignment_status_type_id = ast.assignment_status_type_id
578 and ast.per_system_status = 'ACTIVE_ASSIGN';
579
580 l_actuals number := 0;
581 l_actuals_total number := 0;
582 l_assignment_id per_all_assignments_f.assignment_id%type;
583 l_budget_type_code1 per_budgets.unit%type;
584 l_budget_type_code2 per_budgets.unit%type;
585 l_budget_type_code3 per_budgets.unit%type;
586 l_business_group_id per_budgets.business_group_id%type;
587 l_formula_id ff_formulas_f.formula_id%type;
588 l_grade_id per_budget_elements.grade_id%type;
589 l_job_id per_budget_elements.job_id%type;
590 l_organization_id per_budget_elements.organization_id%type;
591 l_period_end_date per_time_periods.end_date%type;
592 l_position_id per_budget_elements.position_id%type;
593 p_formula_id ff_formulas_x.formula_id%type;
594
595 BEGIN
596
597 -- Return zero if any of the mandatory input parameters is null
598
599 p_formula_id := get_manpower_formula_id
600 (p_business_group_id => p_business_group_id
601 , p_budget_measurement_code => p_budget_metric);
602
603 if (p_budget_id is null) or (p_formula_id is null) or (p_time_period_id is null) then
604 return(0);
605 else
606
607 -- Get Budget Type Code and confirm budget exists
608
609 open budget_csr;
610 fetch budget_csr into
611 l_budget_type_code1,l_budget_type_code2,l_budget_type_code3, l_business_group_id;
612
613 if budget_csr%found then
614
615 if (p_budget_metric = l_budget_type_code1) or
616 (p_budget_metric = l_budget_type_code2) or
617 (p_budget_metric = l_budget_type_code3) then
618 close budget_csr;
619 else
620 close budget_csr;
621 return(0);
622 end if;
623
624 else
625 close budget_csr;
626 return(0);
627 end if;
628
629 -- Get End Date of the time period
630
631 open time_period_csr;
632 fetch time_period_csr into l_period_end_date;
633 close time_period_csr;
634
635 -- JRHYDE - 1999/11/05 Bug -
636 -- Call to get_ff_actual_value replaced with call to GetBudgetValue function
637 -- that tests the ABV first then if that does not exist it calls the
638 -- Fast Formula (thus being much quicker than calling the ff for each row)
639
640 /*----------------------------------------------------------------------------*/
641 /* Bug 2483207 - To fix high cost SQL it was necessary to split out the one */
642 /* main cursor into five cursors. Depending on the values of the parameters */
643 /* passed in a different cursor is used. The most selective parameter to use */
644 /* is p_position_id, so this is checked first, followed by grade, job and */
645 /* organization. If all parameters are null, then business group id is used. */
646 /* Note that the option of using this index is disabled in the former four */
647 /* cursors. This is to prevent an inefficient query plan being used. */
648 /*----------------------------------------------------------------------------*/
649
650 IF (p_position_id IS NOT NULL) THEN
651
652 FOR assignment_rec IN pos_assignment_csr(
653 l_business_group_id
654 ,p_grade_id
655 ,p_job_id
656 ,p_organization_id
657 ,p_position_id
658 ,l_period_end_date
659 ) LOOP
660
661 l_assignment_id := assignment_rec.assignment_id;
662
663 BEGIN /* cbridge bug 1875197 */
664 l_actuals := HrFastAnswers.GetBudgetValue(
665 p_budget_metric_formula_id => p_formula_id
666 ,p_budget_metric => p_budget_metric
667 ,p_assignment_id => l_assignment_id
668 ,p_effective_date => l_period_end_date
669 ,p_session_date => sysdate );
670 EXCEPTION
671 WHEN OTHERS THEN
672 -- fast formula not compiled for an assignment that has no abv,
673 -- so need to trap exception.
674 l_actuals := 0;
675 END;
676
677 l_actuals_total := l_actuals_total + l_actuals;
678
679 END LOOP;
680
681 ELSIF (p_grade_id IS NOT NULL) THEN
682
683 FOR assignment_rec IN grd_assignment_csr(
684 l_business_group_id
685 ,p_grade_id
686 ,p_job_id
687 ,p_organization_id
688 ,l_period_end_date
689 ) LOOP
690
691 l_assignment_id := assignment_rec.assignment_id;
692
693 BEGIN /* cbridge bug 1875197 */
694 l_actuals := HrFastAnswers.GetBudgetValue(
695 p_budget_metric_formula_id => p_formula_id
696 ,p_budget_metric => p_budget_metric
697 ,p_assignment_id => l_assignment_id
698 ,p_effective_date => l_period_end_date
699 ,p_session_date => sysdate );
700 EXCEPTION
701 WHEN OTHERS THEN
702 -- fast formula not compiled for an assignment that has no abv,
703 -- so need to trap exception.
704 l_actuals := 0;
705 END;
706
707 l_actuals_total := l_actuals_total + l_actuals;
708
709 END LOOP;
710
711 ELSIF (p_job_id IS NOT NULL) THEN
712
713 FOR assignment_rec IN job_assignment_csr(
714 l_business_group_id
715 ,p_job_id
716 ,p_organization_id
717 ,l_period_end_date
718 ) LOOP
719
720 l_assignment_id := assignment_rec.assignment_id;
721
722 BEGIN /* cbridge bug 1875197 */
723 l_actuals := HrFastAnswers.GetBudgetValue(
724 p_budget_metric_formula_id => p_formula_id
725 ,p_budget_metric => p_budget_metric
726 ,p_assignment_id => l_assignment_id
727 ,p_effective_date => l_period_end_date
728 ,p_session_date => sysdate );
729 EXCEPTION
730 WHEN OTHERS THEN
731 -- fast formula not compiled for an assignment that has no abv,
732 -- so need to trap exception.
733 l_actuals := 0;
734 END;
735
736 l_actuals_total := l_actuals_total + l_actuals;
737
738 END LOOP;
739
740 ELSIF (p_organization_id IS NOT NULL) THEN
741
742 FOR assignment_rec IN org_assignment_csr(
743 l_business_group_id
744 ,p_organization_id
745 ,l_period_end_date
746 ) LOOP
747
748 l_assignment_id := assignment_rec.assignment_id;
749
750 BEGIN /* cbridge bug 1875197 */
751 l_actuals := HrFastAnswers.GetBudgetValue(
752 p_budget_metric_formula_id => p_formula_id
753 ,p_budget_metric => p_budget_metric
754 ,p_assignment_id => l_assignment_id
755 ,p_effective_date => l_period_end_date
756 ,p_session_date => sysdate );
757 EXCEPTION
758 WHEN OTHERS THEN
759 -- fast formula not compiled for an assignment that has no abv,
760 -- so need to trap exception.
761 l_actuals := 0;
762 END;
763
764 l_actuals_total := l_actuals_total + l_actuals;
765
766 END LOOP;
767
768 ELSE
769
770 FOR assignment_rec IN bgr_assignment_csr(
771 l_business_group_id
772 ,l_period_end_date
773 ) LOOP
774
775 l_assignment_id := assignment_rec.assignment_id;
776
777 BEGIN /* cbridge bug 1875197 */
778 l_actuals := HrFastAnswers.GetBudgetValue(
779 p_budget_metric_formula_id => p_formula_id
780 ,p_budget_metric => p_budget_metric
781 ,p_assignment_id => l_assignment_id
782 ,p_effective_date => l_period_end_date
783 ,p_session_date => sysdate );
784 EXCEPTION
785 WHEN OTHERS THEN
786 -- fast formula not compiled for an assignment that has no abv,
787 -- so need to trap exception.
788 l_actuals := 0;
789 END;
790
791 l_actuals_total := l_actuals_total + l_actuals;
792
793 END LOOP;
794
795 END IF;
796
797 -- Sum the budget values for all relevant assignments
798
799 return(l_actuals_total);
800
801 end if;
802
803 EXCEPTION
804 when others then
805 return(0);
806
807 END get_ff_actual_value_pqh;
808
809
810 /******************************************************************************/
811 /* Function returning the number of direct reports for a person on a date */
812 /******************************************************************************/
813 FUNCTION direct_reports
814 (p_person_id IN NUMBER
815 ,p_effective_start_date IN DATE
816 ,p_effective_end_date IN DATE)
817 RETURN NUMBER IS
818 v_person_id NUMBER;
819 v_effective_start_date DATE;
820 v_effective_end_date DATE;
821 v_direct_reports NUMBER;
822 BEGIN
823 v_person_id := p_person_id;
824 v_effective_start_date := p_effective_start_date;
825 v_effective_end_date := p_effective_end_date;
826
827 -- 17-OCT-2001, bug 2052714, fixed to exclude terminated direct reports from the count
828 --
829
830 SELECT count(*) INTO v_direct_reports
831 FROM per_all_assignments_f asg, per_all_people_f peo
832 WHERE asg.supervisor_id = v_person_id
833 AND v_effective_end_date BETWEEN asg.effective_start_date
834 AND asg.effective_end_date
835 AND asg.person_id = peo.person_id
836 AND v_effective_end_date
837 BETWEEN peo.effective_start_date AND peo.effective_end_date
838 AND peo.current_employee_flag = 'Y' ;
839
840 RETURN (v_direct_reports);
841 END direct_reports;
842
843 /******************************************************************************/
844 /* This function will return the lookup code given the meaning for a lookup */
845 /* type of budget measurement type */
846 /******************************************************************************/
847 PROCEDURE cache_bmt_code(p_bmt_meaning IN VARCHAR2) IS
848
849 CURSOR bmt_code_csr IS
850 SELECT lookup_code
851 FROM hr_standard_lookups
852 WHERE lookup_type = 'BUDGET_MEASUREMENT_TYPE'
853 AND meaning = p_bmt_meaning;
854
855 BEGIN
856
857 /* Store new BMT code in global */
858 OPEN bmt_code_csr;
859 FETCH bmt_code_csr INTO g_bmt_code;
860 CLOSE bmt_code_csr;
861
862 /* Store meaning for new BMT code in global */
863 g_bmt_meaning := p_bmt_meaning;
864
865 END cache_bmt_code;
866
867 /******************************************************************************/
868 /* Calculates the ABV given a BMT meaning, business group and assignment */
869 /******************************************************************************/
870 FUNCTION calc_abv_lookup(p_assignment_id IN NUMBER,
871 p_business_group_id IN NUMBER,
872 p_bmt_meaning IN VARCHAR2,
873 p_effective_date IN DATE)
874 RETURN NUMBER IS
875
876 BEGIN
877
878 IF (p_bmt_meaning = g_bmt_meaning) THEN
879 null;
880 ELSE
881 cache_bmt_code(p_bmt_meaning => p_bmt_meaning);
882 END IF;
883
884 RETURN (hri_bpl_abv.calc_abv
885 (p_assignment_id => p_assignment_id,
886 p_business_group_id => p_business_group_id,
887 p_budget_type => g_bmt_code,
888 p_effective_date => p_effective_date));
889
890 EXCEPTION
891 WHEN OTHERS THEN
892 RETURN to_number(null);
893 END calc_abv_lookup;
894
895 /******************************************************************************/
896 /* Calculates the ABV given a BMT meaning, business group and assignment */
897 /******************************************************************************/
898 FUNCTION calc_abv_lookup(p_assignment_id IN NUMBER,
899 p_business_group_id IN NUMBER,
900 p_bmt_meaning IN VARCHAR2,
901 p_effective_date IN DATE,
902 p_primary_flag IN VARCHAR2)
903 RETURN NUMBER IS
904
905 BEGIN
906
907 IF (p_bmt_meaning = g_bmt_meaning) THEN
908 null;
909 ELSE
910 cache_bmt_code(p_bmt_meaning => p_bmt_meaning);
911 END IF;
912
913 RETURN (hri_bpl_abv.calc_abv
914 (p_assignment_id => p_assignment_id,
915 p_business_group_id => p_business_group_id,
916 p_budget_type => g_bmt_code,
917 p_effective_date => p_effective_date,
918 p_primary_flag => p_primary_flag));
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 RETURN to_number(null);
923 END calc_abv_lookup;
924
925 -- cbridge, 09-JAN-02, new function to return pqh budget
926 -- actual values for a given budget on an effective_date
927 FUNCTION get_ff_actual_value_pqh
928 (p_budget_id IN NUMBER
929 ,p_business_group_id IN NUMBER
930 ,p_grade_id IN NUMBER DEFAULT NULL
931 ,p_job_id IN NUMBER DEFAULT NULL
932 ,p_organization_id IN NUMBER DEFAULT NULL
933 ,p_position_id IN NUMBER DEFAULT NULL
934 ,p_effective_date IN DATE
935 ,p_budget_metric IN VARCHAR2
936 )
937 RETURN NUMBER IS
938
939 CURSOR budget_csr is
940 SELECT pst1.system_type_cd unit1_name
941 , pst2.system_type_cd unit2_name
942 , pst3.system_type_cd unit3_name
943 , bgt.business_group_id business_group_id
944 FROM pqh_budgets bgt
945 , per_shared_types_vl pst1
946 , per_shared_types_vl pst2
947 , per_shared_types_vl pst3
948 WHERE bgt.budget_id = p_budget_id
949 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
950 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
951 AND bgt.budget_unit3_id = pst3.shared_type_id (+);
952
953
954 /* To be used if p_position_id is not null */
955 cursor pos_assignment_csr(
956 p_business_group_id NUMBER
957 ,p_grade_id NUMBER
958 ,p_job_id NUMBER
959 ,p_organization_id NUMBER
960 ,p_position_id NUMBER
961 ,p_effective_date DATE
962 ) is
963 select asg.assignment_id
964 from per_assignments_f asg
965 ,per_assignment_status_types ast
966 where (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
967 and p_position_id = asg.position_id
968 and (p_job_id IS NULL OR asg.job_id = p_job_id)
969 and (p_grade_id IS NULL OR asg.grade_id = p_grade_id)
970 and asg.business_group_id = p_business_group_id
971 and asg.assignment_type = 'E'
972 and p_effective_date between asg.effective_start_date and
973 asg.effective_end_date
974 and asg.assignment_status_type_id = ast.assignment_status_type_id
975 and ast.per_system_status = 'ACTIVE_ASSIGN';
976
977 /* To be used if p_grade_id is not null */
978 cursor grd_assignment_csr(
979 p_business_group_id NUMBER
980 ,p_grade_id NUMBER
981 ,p_job_id NUMBER
982 ,p_organization_id NUMBER
983 ,p_effective_date DATE
984 ) is
985 select asg.assignment_id
986 from per_assignments_f asg
987 ,per_assignment_status_types ast
988 where (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
989 and (p_job_id IS NULL OR asg.job_id = p_job_id)
990 and p_grade_id = asg.grade_id
991 and asg.business_group_id = p_business_group_id
992 and asg.assignment_type = 'E'
993 and p_effective_date between asg.effective_start_date and
994 asg.effective_end_date
995 and asg.assignment_status_type_id = ast.assignment_status_type_id
996 and ast.per_system_status = 'ACTIVE_ASSIGN';
997
998 /* To be used if p_job_id is not null */
999 cursor job_assignment_csr(
1000 p_business_group_id NUMBER
1001 ,p_job_id NUMBER
1002 ,p_organization_id NUMBER
1003 ,p_effective_date DATE
1004 ) is
1005 select asg.assignment_id
1006 from per_assignments_f asg
1007 ,per_assignment_status_types ast
1008 where (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
1009 and p_job_id = asg.job_id
1010 and asg.business_group_id = p_business_group_id
1011 and asg.assignment_type = 'E'
1012 and p_effective_date between asg.effective_start_date and
1013 asg.effective_end_date
1014 and asg.assignment_status_type_id = ast.assignment_status_type_id
1015 and ast.per_system_status = 'ACTIVE_ASSIGN';
1016
1017 /* To be used if p_organization_id is not null */
1018 cursor org_assignment_csr(
1019 p_business_group_id NUMBER
1020 ,p_organization_id NUMBER
1021 ,p_effective_date DATE
1022 ) is
1023 select asg.assignment_id
1024 from per_assignments_f asg
1025 ,per_assignment_status_types ast
1026 where p_organization_id = asg.organization_id
1027 and asg.business_group_id = p_business_group_id
1028 and asg.assignment_type = 'E'
1029 and p_effective_date between asg.effective_start_date and
1030 asg.effective_end_date
1031 and asg.assignment_status_type_id = ast.assignment_status_type_id
1032 and ast.per_system_status = 'ACTIVE_ASSIGN';
1033
1034 /* To be used if all parameters are null */
1035 cursor bgr_assignment_csr(
1036 p_business_group_id NUMBER
1037 ,p_effective_date DATE
1038 ) is
1039 select asg.assignment_id
1040 from per_assignments_f asg
1041 ,per_assignment_status_types ast
1042 where asg.business_group_id = p_business_group_id
1043 and asg.assignment_type = 'E'
1044 and p_effective_date between asg.effective_start_date and
1045 asg.effective_end_date
1046 and asg.assignment_status_type_id = ast.assignment_status_type_id
1047 and ast.per_system_status = 'ACTIVE_ASSIGN';
1048
1049 l_actuals number := 0;
1050 l_actuals_total number := 0;
1051 l_assignment_id per_all_assignments_f.assignment_id%type;
1052 l_budget_type_code1 per_budgets.unit%type;
1053 l_budget_type_code2 per_budgets.unit%type;
1054 l_budget_type_code3 per_budgets.unit%type;
1055 l_business_group_id per_budgets.business_group_id%type;
1056 l_formula_id ff_formulas_f.formula_id%type;
1057 l_grade_id per_budget_elements.grade_id%type;
1058 l_job_id per_budget_elements.job_id%type;
1059 l_organization_id per_budget_elements.organization_id%type;
1060 l_period_end_date per_time_periods.end_date%type;
1061 l_position_id per_budget_elements.position_id%type;
1062 p_formula_id ff_formulas_x.formula_id%type;
1063
1064 BEGIN
1065
1066
1067 p_formula_id := get_manpower_formula_id
1068 (p_business_group_id => p_business_group_id
1069 , p_budget_measurement_code => p_budget_metric);
1070
1071 if (p_budget_id is null) or (p_formula_id is null) or (p_effective_date is null) then
1072 return(0);
1073 else
1074
1075 -- Get Budget Type Code and confirm budget exists
1076
1077 open budget_csr;
1078 fetch budget_csr into
1079 l_budget_type_code1,l_budget_type_code2,l_budget_type_code3, l_business_group_id;
1080
1081 if budget_csr%found then
1082
1083 if (p_budget_metric = l_budget_type_code1) or
1084 (p_budget_metric = l_budget_type_code2) or
1085 (p_budget_metric = l_budget_type_code3) then
1086 close budget_csr;
1087 else
1088 close budget_csr;
1089 return(0);
1090 end if;
1091
1092 else
1093 close budget_csr;
1094 return(0);
1095 end if;
1096
1097 IF (p_position_id IS NOT NULL) THEN
1098
1099 FOR assignment_rec IN pos_assignment_csr(
1100 l_business_group_id
1101 ,p_grade_id
1102 ,p_job_id
1103 ,p_organization_id
1104 ,p_position_id
1105 ,p_effective_date
1106 ) LOOP
1107
1108 l_assignment_id := assignment_rec.assignment_id;
1109
1110 BEGIN /* cbridge bug 1875197 */
1111 l_actuals := HrFastAnswers.GetBudgetValue(
1112 p_budget_metric_formula_id => p_formula_id
1113 ,p_budget_metric => p_budget_metric
1114 ,p_assignment_id => l_assignment_id
1115 ,p_effective_date => p_effective_date
1116 ,p_session_date => sysdate );
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 -- fast formula not compiled for an assignment that has no abv,
1120 -- so need to trap exception.
1121 l_actuals := 0;
1122 END;
1123
1124 l_actuals_total := l_actuals_total + l_actuals;
1125
1126 END LOOP;
1127
1128 ELSIF (p_grade_id IS NOT NULL) THEN
1129
1130 FOR assignment_rec IN grd_assignment_csr(
1131 l_business_group_id
1132 ,p_grade_id
1133 ,p_job_id
1134 ,p_organization_id
1135 ,p_effective_date
1136 ) LOOP
1137
1138 l_assignment_id := assignment_rec.assignment_id;
1139
1140 BEGIN /* cbridge bug 1875197 */
1141 l_actuals := HrFastAnswers.GetBudgetValue(
1142 p_budget_metric_formula_id => p_formula_id
1143 ,p_budget_metric => p_budget_metric
1144 ,p_assignment_id => l_assignment_id
1145 ,p_effective_date => p_effective_date
1146 ,p_session_date => sysdate );
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 -- fast formula not compiled for an assignment that has no abv,
1150 -- so need to trap exception.
1151 l_actuals := 0;
1152 END;
1153
1154 l_actuals_total := l_actuals_total + l_actuals;
1155
1156 END LOOP;
1157
1158 ELSIF (p_job_id IS NOT NULL) THEN
1159
1160 FOR assignment_rec IN job_assignment_csr(
1161 l_business_group_id
1162 ,p_job_id
1163 ,p_organization_id
1164 ,p_effective_date
1165 ) LOOP
1166
1167 l_assignment_id := assignment_rec.assignment_id;
1168
1169
1170 BEGIN /* cbridge bug 1875197 */
1171 l_actuals := HrFastAnswers.GetBudgetValue(
1172 p_budget_metric_formula_id => p_formula_id
1173 ,p_budget_metric => p_budget_metric
1174 ,p_assignment_id => l_assignment_id
1175 ,p_effective_date => p_effective_date
1176 ,p_session_date => sysdate );
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 -- fast formula not compiled for an assignment that has no abv,
1180 -- so need to trap exception.
1181 l_actuals := 0;
1182 END;
1183
1184 l_actuals_total := l_actuals_total + l_actuals;
1185
1186 END LOOP;
1187
1188 ELSIF (p_organization_id IS NOT NULL) THEN
1189
1190
1191 FOR assignment_rec IN org_assignment_csr(
1192 l_business_group_id
1193 ,p_organization_id
1194 ,p_effective_date
1195 ) LOOP
1196
1197 l_assignment_id := assignment_rec.assignment_id;
1198
1199 BEGIN /* cbridge bug 1875197 */
1200 l_actuals := HrFastAnswers.GetBudgetValue(
1201 p_budget_metric_formula_id => p_formula_id
1202 ,p_budget_metric => p_budget_metric
1203 ,p_assignment_id => l_assignment_id
1204 ,p_effective_date => p_effective_date
1205 ,p_session_date => sysdate );
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 -- fast formula not compiled for an assignment that has no abv,
1209 -- so need to trap exception.
1210 l_actuals := 0;
1211 END;
1212
1213 l_actuals_total := l_actuals_total + l_actuals;
1214
1215 END LOOP;
1216
1217 ELSE
1218
1219 FOR assignment_rec IN bgr_assignment_csr(
1220 l_business_group_id
1221 ,p_effective_date
1222 ) LOOP
1223
1224 l_assignment_id := assignment_rec.assignment_id;
1225
1226 BEGIN /* cbridge bug 1875197 */
1227 l_actuals := HrFastAnswers.GetBudgetValue(
1228 p_budget_metric_formula_id => p_formula_id
1229 ,p_budget_metric => p_budget_metric
1230 ,p_assignment_id => l_assignment_id
1231 ,p_effective_date => p_effective_date
1232 ,p_session_date => sysdate );
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 -- fast formula not compiled for an assignment that has no abv,
1236 -- so need to trap exception.
1237 l_actuals := 0;
1238 END;
1239
1240 l_actuals_total := l_actuals_total + l_actuals;
1241
1242 END LOOP;
1243
1244 END IF;
1245
1246 -- Sum the budget values for all relevant assignments
1247
1248 return(l_actuals_total);
1249
1250 end if;
1251
1252 EXCEPTION
1253 when others then
1254 return(0);
1255
1256 END get_ff_actual_value_pqh;
1257
1258 -- returns period of time, in months, of the persons period of service
1259 -- taking into account breaks in service for employee rehires
1260 FUNCTION get_period_service_in_months(p_person_id IN NUMBER
1261 ,p_period_of_service_id IN NUMBER
1262 ,p_effective_date IN DATE) RETURN NUMBER
1263
1264 IS
1265
1266 CURSOR get_pps_months_cur IS
1267 SELECT sum(months_between(least(nvl(actual_termination_date + 1,
1268 p_effective_date + 1), p_effective_date + 1), date_start)) total_months
1269 FROM per_periods_of_service
1270 WHERE person_id = p_person_id
1271 AND date_start <= p_effective_date
1272 AND period_of_service_id <= p_period_of_service_id;
1273
1274
1275 l_period_service_months NUMBER :=0;
1276
1277 BEGIN
1278
1279 OPEN get_pps_months_cur;
1280 FETCH get_pps_months_cur INTO l_period_service_months;
1281 CLOSE get_pps_months_cur;
1282
1283 RETURN (l_period_service_months);
1284
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 BEGIN
1288 IF get_pps_months_cur%ISOPEN THEN
1289 CLOSE get_pps_months_cur;
1290 END IF;
1291 RETURN (l_period_service_months);
1292 END; -- exception
1293
1294 END get_period_service_in_months;
1295
1296 -- returns period of time, in years, of the persons period of service
1297 -- taking into account breaks in service for employee rehires
1298 FUNCTION get_period_service_in_years(p_person_id IN NUMBER
1299 ,p_period_of_service_id IN NUMBER
1300 ,p_effective_date IN DATE) RETURN NUMBER
1301
1302 IS
1303
1304 CURSOR get_pps_years_cur IS
1305 SELECT sum(months_between
1306 (least(nvl(actual_termination_date + 1, p_effective_date + 1),
1307 p_effective_date + 1), date_start)) / 12 total_years
1308 FROM per_periods_of_service
1309 WHERE person_id = p_person_id
1310 AND date_start <= p_effective_date
1311 AND period_of_service_id <= p_period_of_service_id;
1312
1313 l_period_service_years NUMBER :=0;
1314
1315 BEGIN
1316
1317 OPEN get_pps_years_cur;
1318 FETCH get_pps_years_cur INTO l_period_service_years;
1319 CLOSE get_pps_years_cur;
1320
1321 RETURN (l_period_service_years);
1322
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 BEGIN
1326 IF get_pps_years_cur%ISOPEN THEN
1327 CLOSE get_pps_years_cur;
1328 END IF;
1329 RETURN (l_period_service_years);
1330 END; -- exception
1331
1332 END get_period_service_in_years;
1333
1334
1335 END hri_oltp_disc_wrkfrc;
[Home] [Help]