1 PACKAGE BODY hr_disc_calculations AS
2 /* $Header: hrdicalc.pkb 115.26 2002/08/22 09:21:39 jtitmas ship $ */
3
4
5 /***********************/
6 /* RECRUITMENT SECTION */
7 /***********************/
8
9 /******************************************************************************/
10 /* This function returns the number of applicants who have been hired into a */
11 /* vacancy */
12 /******************************************************************************/
13 FUNCTION vacancy_hires(p_vacancy IN VARCHAR2,
14 p_business_group IN VARCHAR2,
15 p_requisition IN VARCHAR2,
16 p_applicant_number IN VARCHAR2)
17 RETURN NUMBER IS
18
19 l_return_value NUMBER;
20
21 BEGIN
22
23 l_return_value := hri_oltp_disc_rctmnt.get_vacancy_hire_count
24 (p_vacancy => p_vacancy,
25 p_business_group => p_business_group,
26 p_requisition => p_requisition,
27 p_applicant_number => p_applicant_number);
28
29 RETURN l_return_value;
30
31 END vacancy_hires;
32
33
34 /******************************************************************************/
35 /* This function returns the number of applicants who have been made an offer */
36 /* for a vacancy */
37 /******************************************************************************/
38 FUNCTION vacancy_offers(p_vacancy IN VARCHAR2,
39 p_business_group IN VARCHAR2,
40 p_requisition IN VARCHAR2)
41 RETURN NUMBER IS
42
43 l_return_value NUMBER;
44
45 BEGIN
46
47 l_return_value := hri_oltp_disc_rctmnt.get_vacancy_offer_count
48 (p_vacancy => p_vacancy,
49 p_business_group => p_business_group,
50 p_requisition => p_requisition);
51
52 RETURN l_return_value;
53
54 END vacancy_offers;
55
56
57 /******************************************************************************/
58 /* This function returns the number of applicants who have been hired via the */
59 /* recruitment activity */
60 /******************************************************************************/
61 FUNCTION rec_activity_hires(p_rec_activity IN VARCHAR2,
62 p_business_group IN VARCHAR2,
63 p_applicant_number IN VARCHAR2)
64 RETURN NUMBER IS
65
66 l_return_value NUMBER;
67
68 BEGIN
69
70 l_return_value := hri_oltp_disc_rctmnt.get_rec_act_hire_count
71 (p_rec_activity => p_rec_activity,
72 p_business_group => p_business_group,
73 p_applicant_number => p_applicant_number);
74
75 RETURN l_return_value;
76
77 END rec_activity_hires;
78
79
80 /******************************************************************************/
81 /* This function returns the number of applicants who have been made offers */
82 /* via the recruitment activity */
83 /******************************************************************************/
84 FUNCTION rec_activity_offers(p_rec_activity IN VARCHAR2,
85 p_business_group IN VARCHAR2)
86 RETURN NUMBER IS
87
88 l_return_value NUMBER;
89
90 BEGIN
91
92 l_return_value := hri_oltp_disc_rctmnt.get_rec_act_offer_count
93 (p_rec_activity => p_rec_activity,
94 p_business_group => p_business_group);
95
96 RETURN l_return_value;
97
98 END rec_activity_offers;
99
100
101 /******************************************************************************/
102 /* This function returns the number of applicants who have been hired into */
103 /* the vacancy via the recruitment activity */
104 /******************************************************************************/
105 FUNCTION rec_activity_vacancy_hires(p_rec_activity IN VARCHAR2,
106 p_vacancy IN VARCHAR2,
107 p_business_group IN VARCHAR2,
108 p_applicant_number IN VARCHAR2)
109 RETURN NUMBER IS
110
111 l_return_value NUMBER;
112
113 BEGIN
114
115 l_return_value := hri_oltp_disc_rctmnt.get_rec_act_vac_hire_count
116 (p_rec_activity => p_rec_activity,
117 p_vacancy => p_vacancy,
118 p_business_group => p_business_group,
119 p_applicant_number => p_applicant_number);
120
121 RETURN l_return_value;
122
123 END rec_activity_vacancy_hires;
124
125
126 /******************************************************************************/
127 /* This function returns the number of applicants who have been made offers */
128 /* for the vacancy via the recruitment activity */
129 /******************************************************************************/
130 FUNCTION rec_activity_vacancy_offers(p_rec_activity IN VARCHAR2,
131 p_vacancy IN VARCHAR2,
132 p_business_group IN VARCHAR2)
133 RETURN NUMBER IS
134
135 l_return_value NUMBER;
136
137 BEGIN
138
139 l_return_value := hri_oltp_disc_rctmnt.get_rec_act_vac_offer_count
140 (p_rec_activity => p_rec_activity,
141 p_vacancy => p_vacancy,
142 p_business_group => p_business_group);
143
144 RETURN l_return_value;
145
146 END rec_activity_vacancy_offers;
147
148
149 /******************************************************************************/
150 /* This function returns the hiring cost per head of hiring employees who are */
151 /* still employed */
152 /******************************************************************************/
153 FUNCTION hiring_cost_current_emp(p_rec_act_id IN NUMBER,
154 p_actual_cost IN NUMBER)
155 RETURN NUMBER IS
156
157 l_return_value NUMBER;
158
159 BEGIN
160
161 l_return_value := hri_oltp_disc_rctmnt.get_hiring_cost_current_emp
162 (p_rec_act_id => p_rec_act_id,
163 p_actual_cost => p_actual_cost);
164
165 RETURN l_return_value;
166
167 END hiring_cost_current_emp;
168
169 /* function active_vacancy */
170
171 FUNCTION active_vacancy( p_date_from IN DATE,
172 p_date_to IN DATE)
173 RETURN VARCHAR2 IS
174
175 l_return_value VARCHAR2(30);
176
177 BEGIN
178
179 l_return_value := hri_oltp_disc_rctmnt.check_active_vacancy
180 (p_date_from => p_date_from,
181 p_date_to => p_date_to);
182
183 RETURN l_return_value;
184
185 END active_vacancy;
186
187
188 /*********************/
189 /* WORKFORCE SECTION */
190 /*********************/
191
192 /******************************************************************************/
193 /* Public function to determine the appropriate FastFormula Id to be used for */
194 /* calculating manpower actuals */
195 /******************************************************************************/
196 FUNCTION get_manpower_formula_id(p_business_group_id IN NUMBER
197 ,p_budget_measurement_code IN VARCHAR2)
198 RETURN NUMBER IS
199
200 l_return_value VARCHAR2(30);
201
202 BEGIN
203
204 l_return_value := hri_oltp_disc_wrkfrc.get_manpower_formula_id
205 (p_business_group_id => p_business_group_id,
206 p_budget_measurement_code => p_budget_measurement_code);
207
208 RETURN l_return_value;
209
210 END get_manpower_formula_id;
211
212
213 /******************************************************************************/
214 /* Public function to calculate manpower actuals for a single assignment */
215 /******************************************************************************/
216 FUNCTION get_ff_actual_value(p_budget_id IN NUMBER
217 ,p_formula_id IN NUMBER
218 ,p_grade_id IN NUMBER DEFAULT NULL
219 ,p_job_id IN NUMBER DEFAULT NULL
220 ,p_organization_id IN NUMBER DEFAULT NULL
221 ,p_position_id IN NUMBER DEFAULT NULL
222 ,p_time_period_id IN NUMBER)
223 RETURN NUMBER IS
224
225 l_return_value NUMBER;
226
227 BEGIN
228
229 l_return_value := hri_oltp_disc_wrkfrc.get_ff_actual_value
230 (p_budget_id => p_budget_id,
231 p_formula_id => p_formula_id,
232 p_grade_id => p_grade_id,
233 p_job_id => p_job_id,
234 p_organization_id => p_organization_id,
235 p_position_id => p_position_id,
236 p_time_period_id => p_time_period_id);
237
238 RETURN l_return_value;
239
240 END get_ff_actual_value;
241
242
243 /********************/
244 /* TRAINING SECTION */
245 /********************/
246
247 /******************************************************************************/
248 /* Public function to calculate the Budget Cost of a training event */
249 /******************************************************************************/
250 FUNCTION get_event_budget_cost(p_event_id IN NUMBER)
251 RETURN NUMBER IS
252
253 l_return_value NUMBER;
254
255 BEGIN
256
257 l_return_value := hri_oltp_disc_training.get_event_budget_cost
258 (p_event_id => p_event_id);
259
260 RETURN l_return_value;
261
262 END get_event_budget_cost;
263
264
265 /******************************************************************************/
266 /* Public function to calculate the Actual Cost of a training event */
267 /******************************************************************************/
268 FUNCTION get_event_actual_cost(p_event_id IN NUMBER)
269 RETURN NUMBER IS
270
271 l_return_value NUMBER;
272
273 BEGIN
274
275 l_return_value := hri_oltp_disc_training.get_event_actual_cost
276 (p_event_id => p_event_id);
277
278 RETURN l_return_value;
279
280 END get_event_actual_cost;
281
282
283 /******************************************************************************/
284 /* Public function to calculate the Total Revenue generated by a training */
285 /* event */
286 /******************************************************************************/
287 FUNCTION get_event_revenue(p_event_id IN NUMBER)
288 RETURN NUMBER IS
289
290 l_return_value NUMBER;
291
292 BEGIN
293
294 l_return_value := hri_oltp_disc_training.get_event_revenue
295 (p_event_id => p_event_id);
296
297 RETURN l_return_value;
298
299 END get_event_revenue;
300
301
302 /******************************************************************************/
303 /* Private function to calculate the Internal Revenue generated by a training */
304 /* event for a particular delegate booking where the delegate attended the */
305 /* event */
306 /******************************************************************************/
307 FUNCTION get_att_int_rev_booking(p_event_id IN NUMBER,
308 p_booking_id IN NUMBER)
309 RETURN NUMBER IS
310
311 l_return_value NUMBER;
312
313 BEGIN
314
315 l_return_value := hri_oltp_disc_training.get_att_int_rev_booking
316 (p_event_id => p_event_id,
317 p_booking_id => p_booking_id);
318
319 RETURN l_return_value;
320
321 END get_att_int_rev_booking;
322
323
324 /******************************************************************************/
325 /* Private function to calculate the External Revenue generated by a training */
326 /* event for a particular delegate booking where the delegate attended the */
327 /* event */
328 /******************************************************************************/
329 FUNCTION get_att_ext_rev_booking(p_event_id IN NUMBER,
330 p_booking_id IN NUMBER)
331 RETURN NUMBER IS
332
333 l_return_value NUMBER;
334
335 BEGIN
336
337 l_return_value := hri_oltp_disc_training.get_att_ext_rev_booking
338 (p_event_id => p_event_id,
339 p_booking_id => p_booking_id);
340
341 RETURN l_return_value;
342
343 END get_att_ext_rev_booking;
344
345
346 /******************************************************************************/
347 /* Private function to calculate the Internal Revenue generated by a training */
348 /* event for a particular delegate booking where the delegate did not attend */
349 /* the event */
350 /******************************************************************************/
351 FUNCTION get_non_att_int_rev_booking(p_event_id IN NUMBER,
352 p_booking_id IN NUMBER)
353 RETURN NUMBER IS
354
358
355 l_return_value NUMBER;
356
357 BEGIN
359 l_return_value := hri_oltp_disc_training.get_non_att_int_rev_booking
360 (p_event_id => p_event_id,
361 p_booking_id => p_booking_id);
362
363 RETURN l_return_value;
364
365 END get_non_att_int_rev_booking;
366
367
368 /******************************************************************************/
369 /* Private function to calculate the External Revenue generated by a training */
370 /* event for a particular delegate booking where the delegate did not attend */
371 /* the event */
372 /******************************************************************************/
373 FUNCTION get_non_att_ext_rev_booking(p_event_id IN NUMBER,
374 p_booking_id IN NUMBER)
375 RETURN NUMBER IS
376
377 l_return_value NUMBER;
378
379 BEGIN
380
381 l_return_value := hri_oltp_disc_training.get_non_att_ext_rev_booking
382 (p_event_id => p_event_id,
383 p_booking_id => p_booking_id);
384
385 RETURN l_return_value;
386
387 END get_non_att_ext_rev_booking;
388
389
390 /******************************************************************************/
391 /* Public function to convert Training Duration FROM one set of units to */
392 /* another */
393 /******************************************************************************/
394 FUNCTION training_convert_duration(p_formula_id IN NUMBER
395 ,p_from_duration IN NUMBER
396 ,p_from_duration_units IN VARCHAR2
397 ,p_to_duration_units IN VARCHAR2
398 ,p_activity_version_name IN VARCHAR2
399 ,p_event_name IN VARCHAR2
400 ,p_session_date IN DATE)
401 RETURN NUMBER IS
402
403 l_return_value NUMBER;
404
405 BEGIN
406
407 l_return_value := hri_oltp_disc_training.convert_training_duration
408 (p_formula_id => p_formula_id,
409 p_from_duration => p_from_duration,
410 p_from_duration_units => p_from_duration_units,
411 p_to_duration_units => p_to_duration_units,
412 p_activity_version_name => p_activity_version_name,
413 p_event_name => p_event_name,
414 p_session_date => p_session_date);
415
416 RETURN l_return_value;
417
418 END training_convert_duration;
419
420
421 /******************************************************************************/
422 /* Public function to determine the Id of a FastFormula */
423 /******************************************************************************/
424 FUNCTION get_formula_id(p_business_group_id IN NUMBER
425 ,p_formula_name IN VARCHAR2)
426 RETURN NUMBER IS
427
428 l_return_value NUMBER;
429
430 BEGIN
431
432 l_return_value := hri_oltp_disc_wrkfrc.get_formula_id
433 (p_business_group_id => p_business_group_id,
434 p_formula_name => p_formula_name);
435
436 RETURN l_return_value;
437
438 END get_formula_id;
439
440
441 /******************************************************************************/
442 /* Function to get an assignment budget value for an assignment */
443 /******************************************************************************/
444 FUNCTION get_asg_budget_value(p_budget_metric_formula_id IN NUMBER
445 ,p_budget_metric IN VARCHAR2
446 ,p_assignment_id IN NUMBER
447 ,p_effective_date IN DATE
448 ,p_session_date IN DATE )
449 RETURN NUMBER IS
450
451 l_return_value NUMBER;
452
453 BEGIN
454
455 l_return_value := hri_oltp_disc_wrkfrc.get_asg_budget_value
456 (p_budget_metric_formula_id => p_budget_metric_formula_id,
457 p_budget_metric => p_budget_metric,
458 p_assignment_id => p_assignment_id,
459 p_effective_date => p_effective_date,
460 p_session_date => p_session_date);
461
462 RETURN l_return_value;
463
464 END get_asg_budget_value;
465
466
467 /******************************************************************************/
468 /* cbridge, 28/06/2001 , pqh budgets support function for */
469 /* hrfv_workforce_budgets business view */
470 /* Public function to calculate workforce actuals for a single assignment */
471 /* using new PQH budgets schema model */
472 /* bug enhancement 1317484 */
473 /******************************************************************************/
477 ,p_grade_id IN NUMBER DEFAULT NULL
474 FUNCTION get_ff_actual_value_pqh
475 (p_budget_id IN NUMBER
476 ,p_business_group_id IN NUMBER
478 ,p_job_id IN NUMBER DEFAULT NULL
479 ,p_organization_id IN NUMBER DEFAULT NULL
480 ,p_position_id IN NUMBER DEFAULT NULL
481 ,p_time_period_id IN NUMBER
482 ,p_budget_metric IN VARCHAR2
483 )
484 RETURN NUMBER IS
485
486 l_return_value NUMBER;
487
488 BEGIN
489
490 l_return_value := hri_oltp_disc_wrkfrc.get_ff_actual_value_pqh
491 (p_budget_id => p_budget_id,
492 p_business_group_id => p_business_group_id,
493 p_grade_id => p_grade_id,
494 p_job_id => p_job_id,
495 p_organization_id => p_organization_id,
496 p_position_id => p_position_id,
497 p_time_period_id => p_time_period_id,
498 p_budget_metric => p_budget_metric);
499
500 RETURN l_return_value;
501
502 END get_ff_actual_value_pqh;
503
504
505 /******************************************************************************/
506 /* Function returning the number of direct reports for a person on a date */
507 /******************************************************************************/
508 FUNCTION direct_reports
509 (p_person_id IN NUMBER
510 ,p_effective_start_date IN DATE
511 ,p_effective_end_date IN DATE)
512 RETURN NUMBER IS
513
514 l_return_value NUMBER;
515
516 BEGIN
517
518 l_return_value := hri_oltp_disc_wrkfrc.direct_reports
519 (p_person_id => p_person_id,
520 p_effective_start_date => p_effective_start_date,
521 p_effective_end_date => p_effective_end_date);
522
523 RETURN l_return_value;
524
525 END direct_reports;
526
527
528 /******************/
529 /* SALARY SECTION */
530 /******************/
531
532 /******************************************************************************/
533 /* This function will return the previous salary proposal of a given */
534 /* pay_proposal_id, it is called from the Oracle Internal workbooks that */
535 /* display previous salary. */
536 /* */
537 /* The function was found to be the most performant way of returning an */
538 /* employees previous salary proposal amount for a given employees */
539 /* pay_proposal_id. */
540 /******************************************************************************/
541 FUNCTION get_prev_salary_pro_amount(p_pay_proposal_id NUMBER)
542 RETURN NUMBER IS
543
544 l_return_value NUMBER;
545
546 BEGIN
547
548 l_return_value := hri_oltp_disc_salary.get_prev_salary_pro_amount
549 (p_pay_proposal_id => p_pay_proposal_id);
550
551 RETURN l_return_value;
552
553 END get_prev_salary_pro_amount;
554
555
556 /******************************************************************************/
557 /* Gets the annual salary for an assignment on a given date */
558 /******************************************************************************/
559 FUNCTION get_annual_salary_as_of_date(p_effective_date DATE
560 , p_assignment_id NUMBER)
561 RETURN NUMBER IS
562
563 l_return_value NUMBER;
564
565 BEGIN
566
567 l_return_value := hri_oltp_disc_salary.get_annual_salary_as_of_date
568 (p_effective_date => p_effective_date,
569 p_assignment_id => p_assignment_id);
570
571 RETURN l_return_value;
572
573 END get_annual_salary_as_of_date;
574
575 END hr_disc_calculations;
[Home] [Help]