1 PACKAGE pqp_gb_osp_functions AS
2 /* $Header: pqpospfn.pkh 120.8.12010000.3 2008/08/05 14:22:48 ubhat ship $ */
3 --
4 -- TYPE r_LOS_based_entitlements IS RECORD
5 -- (band VARCHAR2(30)
6 -- ,meaning VARCHAR2(80)
7 -- ,entitlement NUMBER
8 -- );
9
10 -- TYPE t_LOS_based_entitlements IS TABLE OF r_LOS_based_entitlements
11 -- INDEX BY BINARY_INTEGER;
12
13 g_end_of_time DATE:= hr_api.g_eot; -- required because perf parser doesnot like g_eot
14
15 TYPE r_entitlement_parameters IS RECORD(
16 band VARCHAR2(30)
17 ,meaning VARCHAR2(80)
18 ,entitlement NUMBER
19 ,percentage NUMBER
20 ,earnings_type VARCHAR2(80)
21 );
22
23 TYPE t_entitlement_parameters IS TABLE OF r_entitlement_parameters
24 INDEX BY BINARY_INTEGER;
25
26 -- Cursor to get Lookup Code.
27 CURSOR csr_lookup_code(
28 p_lookup_type IN VARCHAR2
29 ,p_lookup_meaning IN VARCHAR2
30 )
31 IS
32 SELECT lookup_code
33 FROM fnd_lookup_values_vl
34 WHERE lookup_type = p_lookup_type AND meaning = p_lookup_meaning;
35
36 -- Cursor to get absence id for a assignment id.
37 CURSOR csr_absence_id(p_assignment_id IN NUMBER, p_effective_date IN DATE)
38 IS
39 SELECT absence_attendance_id
40 FROM per_all_assignments_f asg,
41 ben_per_in_ler pil
42 ,per_absence_attendances paa
43 WHERE asg.assignment_id = p_assignment_id
44 AND p_effective_date BETWEEN asg.effective_start_date
45 AND asg.effective_end_date
46 AND pil.person_id = asg.person_id
47 AND pil.per_in_ler_stat_cd = 'STRTD'
48 AND paa.absence_attendance_id = pil.trgr_table_pk_id;
49
50 -- Cursor to get Medical Id for a given Absence Attendance Id.
51 CURSOR csr_medical_id(p_absence_id IN NUMBER)
52 IS
53 SELECT medical_id
54 FROM ssp_medicals
55 WHERE absence_attendance_id = p_absence_id;
56
57 -- Cursor to get Maternity Id for a given Absence Attendance Id.
58 CURSOR csr_maternity_id(p_absence_id IN NUMBER)
59 IS
60 SELECT maternity_id
61 FROM per_absence_attendances
62 WHERE absence_attendance_id = p_absence_id;
63
64 -- Cursor to get Absence Type for a Absence Id.
65 CURSOR csr_abs_type(p_absence_attendance_id IN NUMBER)
66 IS
67 SELECT TYPE.NAME
68 FROM per_absence_attendances ABS, per_absence_attendance_types TYPE
69 WHERE ABS.absence_attendance_type_id = TYPE.absence_attendance_type_id
70 AND ABS.absence_attendance_id = p_absence_attendance_id;
71
72 -- Cursor to get absence Category for a Absence Id.
73 CURSOR csr_abs_cat(p_absence_attendance_id IN NUMBER)
74 IS
75 SELECT lkp.meaning
76 FROM per_absence_attendances ABS
77 ,per_absence_attendance_types TYPE
78 ,hr_lookups lkp
79 WHERE ABS.absence_attendance_type_id = TYPE.absence_attendance_type_id
80 AND TYPE.absence_category = lkp.lookup_code
81 AND lkp.lookup_type = 'ABSENCE_CATEGORY'
82 AND ABS.absence_attendance_id = p_absence_attendance_id;
83
84 -- Cursor to get Absence Reason for a Absence Id.
85 CURSOR csr_abs_rea(p_absence_attendance_id IN NUMBER)
86 IS
87 SELECT lkp.meaning
88 FROM per_absence_attendances ABS
89 ,per_abs_attendance_reasons rea
90 ,hr_lookups lkp
91 WHERE ABS.abs_attendance_reason_id = rea.abs_attendance_reason_id
92 AND rea.NAME = lkp.lookup_code
93 AND lookup_type = 'ABSENCE_REASON'
94 AND ABS.absence_attendance_id = p_absence_attendance_id;
95
96 -- Cursor to get Element Type ID for Plan Table Functions.
97 CURSOR csr_plan_element_type(
98 p_pl_id IN NUMBER
99 ,p_information_type IN VARCHAR2
100 )
101 IS
102 SELECT petei.element_type_id
103 FROM pay_element_type_extra_info petei, ben_pl_f bpl, ben_pl_typ_f bpty
104 WHERE UPPER(petei.eei_information19) = 'ABSENCE INFO'
105 AND petei.information_type = p_information_type
106 --'PQP_GB_ABSENCE_PLAN_INFO'
107 AND bpl.pl_typ_id = bpty.pl_typ_id
108 AND bpty.opt_typ_cd = 'ABS'
109 AND petei.eei_information1 = fnd_number.number_to_canonical(bpl.pl_id)
110 AND bpl.pl_id = p_pl_id;
111
112 -- Cursor to get Plan Id from Plan Tables for a given Plan Name.
113 CURSOR csr_plan_id(
114 p_business_group_id IN NUMBER
115 ,p_effective_date IN DATE
116 ,p_pl_name IN VARCHAR2
117 )
118 IS
119 SELECT bpl.pl_id
120 FROM ben_pl_f bpl, ben_pl_typ_f bpty
121 WHERE bpl.pl_typ_id = bpty.pl_typ_id
122 AND bpty.opt_typ_cd = 'ABS'
123 AND bpl.business_group_id = p_business_group_id
124 AND p_effective_date BETWEEN bpl.effective_start_date
125 AND bpl.effective_end_date
126 AND bpl.NAME = p_pl_name;
127
128 -- Cursor For Calendar Occurances
129
130 CURSOR csr_cal_occur(
131 p_date IN DATE
132 ,p_table_id IN NUMBER
133 ,p_calendar_rules_list IN VARCHAR2
134 ,p_filter_value IN VARCHAR2
135 ,p_filter IN VARCHAR2
136 )
137 IS
138 SELECT pur.row_low_range_or_name
139 ,cols.user_column_name
140 ,inst.VALUE
141 FROM pay_user_columns cols
142 ,pay_user_rows_f pur
143 ,pay_user_column_instances_f inst
144 ,hr_lookups lookup
145 WHERE cols.user_table_id = p_table_id
146 AND pur.user_table_id = cols.user_table_id
147 AND pur.user_row_id = inst.user_row_id
148 AND cols.user_column_id = inst.user_column_id
149 AND lookup.lookup_type = p_calendar_rules_list
150 AND cols.user_column_name = lookup.meaning
151 -- check that the day is marked in the calendar, ie a column instance is
152 -- effective on that day
153 AND p_date BETWEEN inst.effective_start_date
154 AND DECODE(
155 inst.effective_end_date
156 -- if the eff end date is the End of Time then
157 -- DECODE it to eff start date such that the column
158 -- instance is treated as effective for only one
159 -- day, ie the start date. e.g.
160 -- a row effective from 01-01-2001 to 31-12-4712
161 -- may represent a holiday of only 01-01-2001
162 , g_end_of_time, inst.effective_start_date
163 -- else this column instance represents a range of
164 -- days (date range) marked in the calendar
165 -- eg a row effective from 01-01-2001 to 14-01-2001
166 -- may represent a 2 week period as a holiday
167 , inst.effective_end_date
168 -- effective end date will never be NULL
169 )
170 AND (
171 (
172 p_filter_value IS NULL
173 AND (
174 (p_filter = 'ALLMATCH'--AND
175 --(inst.value IS NULL OR inst.value IS NOT NULL) --redundant
176 )
177 OR (p_filter = 'EXACTMATCH' AND inst.VALUE IS NULL)
178 OR (p_filter = 'EXCEPT' AND inst.VALUE IS NOT NULL)
179 OR (p_filter = 'ALLEXCEPT' AND inst.VALUE IS NOT NULL)
180 ) -- AND p_filter_value IS NULL
181 ) -- OR p_filter_value IS NULL
182 OR (
183 p_filter_value IS NOT NULL
184 AND (
185 (
186 p_filter = 'ALLMATCH'
187 AND (
188 inst.VALUE IS NULL
189 OR inst.VALUE = p_filter_value
190 )
191 )
192 OR (
193 p_filter = 'EXACTMATCH'
194 AND ( --inst.value IS NOT NULL --redundant as the Equality check
195 --AND
196 inst.VALUE =
197 p_filter_value --exlcudes NULLs automatically
198 )
199 )
200 OR (
201 p_filter = 'ALLEXCEPT'
202 AND (
203 inst.VALUE IS NULL
204 OR inst.VALUE <> p_filter_value
205 )
206 )
207 OR (
208 p_filter = 'EXCEPT'
209 AND ( --inst.value IS NOT NULL --redundant as the INequality check
210 --AND
211 inst.VALUE <>
212 p_filter_value --exlcudes NULLs automatically
213 )
214 )
215 ) -- AND p_filter_value IS NOT NULL
216 ) -- OR p_filter_value IS NULL or NOT
217 ) -- AND in the main WHERE
218 ORDER BY lookup.lookup_code;
219
220 -- CURSOR c_wp_dets(p_assignment_id NUMBER, p_start_date DATE, p_end_date DATE)
221 -- IS
222 -- SELECT *
223 -- FROM pqp_assignment_attributes_f
224 -- WHERE assignment_id = p_assignment_id
225 -- AND (
226 -- (
227 -- p_start_date BETWEEN effective_start_date AND effective_end_date
228 -- )
229 -- OR (
230 -- p_end_date BETWEEN effective_start_date AND effective_end_date
231 -- )
232 -- OR (effective_start_date BETWEEN p_start_date AND p_end_date)
233 -- OR (effective_end_date BETWEEN p_start_date AND p_end_date)
234 -- )
235 -- ORDER BY effective_start_date;
236
237 -- CURSOR c_wp_dets_up(p_assignment_id NUMBER, p_start_date DATE)
238 -- IS
239 -- SELECT *
240 -- FROM pqp_assignment_attributes_f
241 -- WHERE assignment_id = p_assignment_id
242 -- AND (
243 -- (
244 -- p_start_date BETWEEN effective_start_date AND effective_end_date
245 -- )
246 -- OR (effective_start_date > p_start_date)
247 -- )
248 -- ORDER BY effective_start_date;
249
250 -- Cursor to get Number of Holidays in absence Period for a given UDT Id.
251 -- Default is the Default Column that will be seeded in the UDT
252 -- through Template.
253 CURSOR csr_get_hol_abs(
254 p_business_group_id IN NUMBER
255 ,p_abs_start_date IN DATE
256 ,p_abs_end_date IN DATE
257 ,p_table_id IN NUMBER
258 ,p_column_name IN VARCHAR2
259 ,p_value IN VARCHAR2
260 )
261 IS
262 SELECT SUM(
263 DECODE(
264 SIGN(effective_end_date - p_abs_end_date)
265 ,1, p_abs_end_date
266 ,effective_end_date
267 )
268 - DECODE(
269 SIGN(effective_start_date - p_abs_start_date)
270 ,-1, p_abs_start_date
271 ,effective_start_date
272 )
273 + 1
274 ) cnt
275 FROM pay_user_column_instances_f inst, pay_user_columns col
276 WHERE col.user_table_id = p_table_id
277 AND inst.user_column_id = col.user_column_id
278 AND col.user_column_name LIKE p_column_name
279 AND ( p_value IS NULL
280 OR inst.VALUE = p_value)
281 AND (
282 (
283 p_abs_start_date BETWEEN inst.effective_start_date
284 AND inst.effective_end_date
285 )
286 OR (
287 inst.effective_start_date BETWEEN p_abs_start_date
288 AND p_abs_end_date
289 )
290 OR (
291 p_abs_end_date BETWEEN inst.effective_start_date
292 AND inst.effective_end_date
293 )
294 OR (
295 inst.effective_end_date BETWEEN p_abs_start_date
296 AND p_abs_end_date
297 )
298 )
299 AND inst.business_group_id = p_business_group_id;
300
301 -- Cursor to check a date is declared as holiday or not.
302 CURSOR csr_get_work_hol(
303 p_business_group_id IN NUMBER
304 ,p_abs_date IN DATE
305 ,p_table_id IN NUMBER
306 ,p_column_name IN VARCHAR2
307 ,p_value IN VARCHAR2
308 )
309 IS
310 SELECT inst.VALUE
311 FROM pay_user_columns col, pay_user_column_instances_f inst
312 WHERE col.user_table_id = p_table_id
313 AND col.user_column_name LIKE p_column_name
314 AND inst.user_column_id = col.user_column_id
315 AND ( p_value IS NULL OR inst.VALUE = p_value )
316 AND p_abs_date BETWEEN inst.effective_start_date
317 AND inst.effective_end_date
318 AND inst.business_group_id = p_business_group_id;
319
320 -- Cursor to get Entitlement Days. This cursor returns the number of days the
321 -- entitlement is like BAND1, BAND2, EXCLUDED etc. This type qualifier is a
322 -- parameter to the cursor.
323 CURSOR csr_entitled_days(
324 p_absence_attendance_id IN NUMBER
325 ,p_pl_id IN NUMBER
326 ,p_search_start_date IN DATE
327 ,p_search_end_date IN DATE
328 ,p_level_of_entitlement IN VARCHAR2
329 )
330 IS
331 SELECT NVL(SUM(daily.DURATION), 0) days
332 ,NVL(SUM(daily.duration_in_hours),0) hours
333 FROM per_absence_attendances attnd
334 ,pqp_gap_absence_plans plans
335 ,pqp_gap_daily_absences daily
336 WHERE attnd.absence_attendance_id = plans.absence_attendance_id
337 AND plans.gap_absence_plan_id = daily.gap_absence_plan_id
338 AND attnd.absence_attendance_id = p_absence_attendance_id
339 AND plans.pl_id = p_pl_id
340 AND (
341 UPPER(daily.level_of_entitlement) = p_level_of_entitlement
342 OR p_level_of_entitlement IS NULL
343 )
344 AND daily.absence_date BETWEEN GREATEST(
345 NVL(p_search_start_date
346 ,attnd.date_start)
347 ,attnd.date_start
348 )
349 AND LEAST(
350 NVL(p_search_end_date, g_end_of_time)
351 ,g_end_of_time
352 );
353
354 -- Cursor to get Paid Days. This cursor returns the number of days the
355 -- absence can be paid like BAND1, BAND2, EXCLUDED etc. This type qualifier
356 -- is a parameter to the cursor.
357 CURSOR csr_paid_days(
358 p_absence_attendance_id IN NUMBER
359 ,p_pl_id IN NUMBER
360 ,p_search_start_date IN DATE
361 ,p_search_end_date IN DATE
362 ,p_level_of_pay IN VARCHAR2
363 )
364 IS
365 SELECT NVL(SUM(daily.duration),0) days
366 -----Added For Hours
367 ,NVL(SUM(daily.duration_in_hours),0) hours
368 FROM per_absence_attendances attnd
369 ,pqp_gap_absence_plans plans
370 ,pqp_gap_daily_absences daily
371 WHERE attnd.absence_attendance_id = plans.absence_attendance_id
372 AND plans.gap_absence_plan_id = daily.gap_absence_plan_id
373 AND attnd.absence_attendance_id = p_absence_attendance_id
374 AND plans.pl_id = p_pl_id
375 AND ( UPPER(daily.level_of_pay) = p_level_of_pay
376 OR p_level_of_pay IS NULL)
377 AND daily.absence_date BETWEEN GREATEST(
378 NVL(p_search_start_date
379 ,attnd.date_start)
380 ,attnd.date_start
381 )
382 AND LEAST(
383 NVL(p_search_end_date, g_end_of_time)
384 ,g_end_of_time
385 );
386
387 -- Cursor to get Work_pattern Types of qualifier type.
388 CURSOR csr_wp_days(
389 p_absence_attendance_id IN NUMBER
390 ,p_pl_id IN NUMBER
391 ,p_search_start_date IN DATE
392 ,p_search_end_date IN DATE
393 ,p_work_pattern_day_type IN VARCHAR2
394 )
395 IS
396 SELECT NVL(SUM(daily.DURATION), 0)
397 FROM per_absence_attendances attnd
398 ,pqp_gap_absence_plans plans
399 ,pqp_gap_daily_absences daily
400 WHERE attnd.absence_attendance_id = plans.absence_attendance_id
401 AND plans.gap_absence_plan_id = daily.gap_absence_plan_id
402 AND attnd.absence_attendance_id = p_absence_attendance_id
403 AND plans.pl_id = p_pl_id
404 AND (
405 UPPER(daily.work_pattern_day_type) = p_work_pattern_day_type
406 OR p_work_pattern_day_type IS NULL
407 )
408 AND daily.absence_date BETWEEN GREATEST(
409 NVL(p_search_start_date
410 ,attnd.date_start)
411 ,attnd.date_start
412 )
413 AND LEAST(
414 NVL(p_search_end_date, g_end_of_time)
415 ,g_end_of_time
416 );
417
418 -- Cursor to get lookup code information from Lookup Type
419 CURSOR csr_get_lookup_info(c_lookup_type VARCHAR2, c_lookup_code VARCHAR2)
420 IS
421 SELECT meaning
422 ,lookup_code
423 FROM hr_lookups
424 WHERE lookup_type = c_lookup_type
425 AND lookup_code LIKE c_lookup_code
426 AND enabled_flag = 'Y'
427 ORDER BY lookup_code;
428
429 -- Cursor to get No of level of Paid Days for a date range.
430 CURSOR csr_get_level_pay(
431 p_assignment_id NUMBER
432 ,p_business_group_id NUMBER
433 ,p_search_start_date DATE
434 ,p_search_end_date DATE
435 ,p_level_of_pay VARCHAR2
436 )
437 IS
438 SELECT NVL(SUM(pgda.DURATION), 0) balance
439 FROM pqp_gap_daily_absences pgda
440 ,pqp_gap_absence_plans pgap
441 ,per_absence_attendances paa
442 WHERE pgda.gap_absence_plan_id = pgap.gap_absence_plan_id
443 AND pgap.absence_attendance_id = paa.absence_attendance_id
444 AND pgda.absence_date BETWEEN p_search_start_date AND p_search_end_date
445 AND pgap.assignment_id = p_assignment_id
446 AND paa.business_group_id = p_business_group_id
447 AND pgda.level_of_pay = p_level_of_pay;
448
449 -- Cursor to get No of level of Entitlement Days for a date range.
450 CURSOR csr_get_level_ent(
451 p_assignment_id NUMBER
452 ,p_business_group_id NUMBER
453 ,p_search_start_date DATE
454 ,p_search_end_date DATE
455 ,p_level_of_ent VARCHAR2
456 )
457 IS
458 SELECT NVL(SUM(pgda.DURATION), 0) balance
459 FROM pqp_gap_daily_absences pgda
460 ,pqp_gap_absence_plans pgap
461 ,per_absence_attendances paa
462 WHERE pgda.gap_absence_plan_id = pgap.gap_absence_plan_id
463 AND pgap.absence_attendance_id = paa.absence_attendance_id
464 AND pgda.absence_date BETWEEN p_search_start_date AND p_search_end_date
465 AND pgap.assignment_id = p_assignment_id
466 AND paa.business_group_id = p_business_group_id
467 AND pgda.level_of_entitlement = p_level_of_ent;
468
469 -- Cursor to get No of level of Work Pattern Days for a date range.
470 CURSOR csr_get_wp_type_days(
471 p_assignment_id NUMBER
472 ,p_business_group_id NUMBER
473 ,p_search_start_date DATE
474 ,p_search_end_date DATE
475 ,p_wp_day_type VARCHAR2
476 )
477 IS
478 SELECT NVL(SUM(pgda.DURATION), 0) balance
479 FROM pqp_gap_daily_absences pgda
480 ,pqp_gap_absence_plans pgap
481 ,per_absence_attendances paa
482 WHERE pgda.gap_absence_plan_id = pgap.gap_absence_plan_id
483 AND pgap.absence_attendance_id = paa.absence_attendance_id
484 AND pgda.absence_date BETWEEN p_search_start_date AND p_search_end_date
485 AND pgap.assignment_id = p_assignment_id
486 AND paa.business_group_id = p_business_group_id
487 AND pgda.work_pattern_day_type = p_wp_day_type;
488
489 -- Cursor to get the FTE for a given assignment as of a given date.
490 CURSOR csr_get_asg_fte_value
491 (p_assignment_id IN NUMBER
492 ,p_effective_date IN DATE
493 ) IS
494
495 SELECT budget.value
496 FROM per_assignment_budget_values_f budget
497 WHERE budget.assignment_id = p_assignment_id
498 AND budget.unit = 'FTE'
499 AND p_effective_date
500 BETWEEN budget.effective_start_date
501 AND budget.effective_end_date;
502
503 -- SSP SMP Cursors Moved out from body in Aug 2003 release
504 --
505 -- Cursor to get the Qualifying Pattern Calendar Usage Exceptions
506 --
507 --CURSOR c_usage_exceptions
508 -- (p_pattern_id hr_patterns.pattern_id%TYPE
509 -- ) IS
510 --SELECT hpe.pattern_id
511 -- ,exception_name
512 -- ,exception_start_time exception_start_date
513 -- ,exception_end_time exception_end_date
514 --FROM hr_calendars hc
515 -- ,hr_calendar_usages hcu
516 -- ,hr_exception_usages heu
517 -- ,hr_pattern_exceptions hpe
518 --WHERE hc.pattern_id = p_pattern_id
519 --AND hc.calendar_id = hcu.calendar_id
520 --AND hcu.purpose_usage_id = 1
521 --AND hcu.calendar_usage_id = heu.calendar_usage_id
522 --AND heu.exception_id = hpe.exception_id;
523 --
524 -- Cursor to get the Qualifying Pattern Exceptions
525 --
526 --CURSOR c_pattern_exceptions
527 -- (p_pattern_id hr_patterns.pattern_id%TYPE
528 -- ) IS
529 --SELECT hpe.pattern_id
530 -- ,exception_name
531 -- ,exception_start_time exception_start_date
532 -- ,exception_end_time exception_end_date
533 --FROM hr_calendars hc
534 -- ,hr_calendar_usages hcu
535 -- ,hr_exception_usages heu
536 -- ,hr_pattern_exceptions hpe
537 --WHERE hc.pattern_id = p_pattern_id
538 --AND purpose_usage_id = 1
539 --AND hcu.calendar_id = hc.calendar_id
540 --AND hc.calendar_id = heu.calendar_id
541 --AND heu.exception_id = hpe.exception_id;
542 --
543 -- Define a table to holds the row returned from the above
544 -- cursor to store the exceptions for BG
545 --
546 --TYPE t_pat_exceptions IS TABLE OF c_pattern_exceptions%ROWTYPE
547 -- INDEX BY BINARY_INTEGER;
548 --
549 --CURSOR c_pattern
550 -- (p_pattern_id hr_patterns.pattern_id%TYPE
551 -- ) IS
552 --SELECT hp.pattern_id
553 -- ,pattern_name
554 -- ,pattern_start_weekday
555 -- ,pattern_start_time
556 --FROM hr_patterns hp
557 --WHERE hp.pattern_id = p_pattern_id;
558 --
559 -- Cursor to get the Qualifying pattern constructors
560 --
561 -- CURSOR c_pattern_cons
562 -- (p_pattern_id NUMBER
563 -- ) IS
564 -- SELECT hpc.sequence_no seq_no
565 -- ,hpc.availability availability
566 -- ,hpb.pattern_bit_code time_unit
567 -- ,hpb.time_unit_multiplier time_unit_multiplier
568 -- ,hpb.base_time_unit base_time_unit
569 -- FROM hr_patterns hrp
570 -- ,hr_pattern_constructions hpc
571 -- ,hr_pattern_bits hpb
572 -- WHERE hrp.pattern_id = p_pattern_id
573 -- AND hrp.pattern_id = hpc.pattern_id
574 -- AND hpc.pattern_bit_id = hpb.pattern_bit_id
575 -- ORDER BY sequence_no;
576 --
577 ---- Define a table based on rowtype of the above cursor
578 --TYPE pat_cons_t IS TABLE OF c_pattern_cons%ROWTYPE
579 -- INDEX BY BINARY_INTEGER;
580 --
581 -- Cursor to get the Pattern associated with a Person
582 --
583 -- CURSOR c_per_pattern
584 -- (p_person_id per_all_assignments.person_id%TYPE
585 -- ) IS
586 -- SELECT pattern_id
587 -- ,start_date
588 -- ,end_date
589 -- FROM hr_calendar_usages hcu, hr_calendars hc
590 -- WHERE primary_key_value = p_person_id
591 -- AND purpose_usage_id = 2
592 -- AND hcu.calendar_id = hc.calendar_id;
593 --
594 -- Cursor gets Input value ids for the element passed as param
595 --
596 CURSOR csr_inputvalue_ids
597 (p_element_type_id IN NUMBER
598 ,p_effective_date IN DATE
599 ) IS
600 SELECT piv.display_sequence
601 ,pet.element_type_id
602 ,piv.input_value_id id
603 FROM pay_element_types_f pet
604 ,pay_input_values_f piv
605 WHERE pet.element_type_id = p_element_type_id
606 AND p_effective_date
607 BETWEEN pet.effective_start_date
608 AND pet.effective_end_date
609 AND piv.element_type_id = pet.element_type_id
610 AND p_effective_date
611 BETWEEN piv.effective_start_date
612 AND piv.effective_end_date;
613
614 -- Define a table to holds the row returned from the above cursor to store
615 -- the input value Ids
616 TYPE t_input_value_ids IS TABLE OF csr_inputvalue_ids%ROWTYPE
617 INDEX BY BINARY_INTEGER;
618
619
620 CURSOR csr_seeded_element_type -- cache -- context -- if not populated
621 (p_element_name IN pay_element_types_f.element_name%TYPE
622 ) IS
623 SELECT element_type_id
624 FROM pay_element_types_f
625 WHERE element_name = p_element_name
626 AND business_group_id IS NULL
627 AND legislation_code = 'GB'
628 AND ROWNUM < 2; -- there can be more than effective row
629 -- since we are only interested in the surrogate id
630 -- ROWNUM < 2 will do
631 --
632 --
633 --
634 CURSOR csr_element_links
635 (p_element_type_id IN NUMBER
636 ,p_business_group_id IN NUMBER
637 ) IS
638 SELECT ell.element_link_id id
639 FROM pay_element_links_f ell
640 WHERE ell.element_type_id = p_element_type_id
641 AND ell.business_group_id = p_business_group_id;
642
643 TYPE t_element_links IS TABLE OF csr_element_links%ROWTYPE
644 INDEX BY BINARY_INTEGER;
645 --
646 --
647 --
648 CURSOR csr_ssp_entries
649 (p_primary_assignment_id IN NUMBER
650 ,p_element_link_id IN NUMBER
651 ,p_piw_id IN NUMBER
652 -- ,p_amount_iv_id IN NUMBER
653 -- ,p_date_from_iv_id IN NUMBER
654 -- ,p_date_to_iv_id IN NUMBER
655 -- ,p_rate_iv_id IN NUMBER
656 -- ,p_qualifying_days_iv_id IN NUMBER
657 -- ,p_ssp_days_due_iv_id IN NUMBER
658 -- ,p_withheld_days_iv_id IN NUMBER
659 -- ,p_ssp_weeks_iv_id IN NUMBER
660 ) IS
661 SELECT ele.element_entry_id
662 ,ele.effective_start_date
663 ,ele.effective_end_date
664 ,fnd_date.canonical_to_date('3712/12/31 00:00:00') Date_From
665 -- ,( SELECT fnd_date.canonical_to_date(eev.screen_entry_value)
666 -- FROM pay_element_entry_values_f eev
667 -- WHERE eev.element_entry_id = ele.element_entry_id
668 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
669 -- AND eev.effective_end_date
670 -- AND eev.input_value_id = p_date_from_iv_id
671 -- ) Date_From
672 ,fnd_date.canonical_to_date('3712/12/31 00:00:00') Date_To
673 -- ,( SELECT fnd_date.canonical_to_date(eev.screen_entry_value)
674 -- FROM pay_element_entry_values_f eev
675 -- WHERE eev.element_entry_id = ele.element_entry_id
676 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
677 -- AND eev.effective_end_date
678 -- AND eev.input_value_id = p_date_to_iv_id
679 -- ) Date_To
680 ,fnd_number.canonical_to_number('0.0') Amount
681 -- ,( SELECT fnd_number.canonical_to_number(eev.screen_entry_value)
682 -- FROM pay_element_entry_values_f eev
683 -- WHERE eev.element_entry_id = ele.element_entry_id
684 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
685 -- AND eev.effective_end_date
686 -- AND eev.input_value_id = p_amount_iv_id
687 -- ) Amount
688 ,fnd_number.canonical_to_number('0.0') Rate
689 -- ,( SELECT fnd_number.canonical_to_number(eev.screen_entry_value)
690 -- FROM pay_element_entry_values_f eev
691 -- WHERE eev.element_entry_id = ele.element_entry_id
692 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
693 -- AND eev.effective_end_date
694 -- AND eev.input_value_id = p_rate_iv_id
695 -- ) Rate
696 ,fnd_number.canonical_to_number('0.0') Qualifying_days
697 -- ,( SELECT fnd_number.canonical_to_number(eev.screen_entry_value)
698 -- FROM pay_element_entry_values_f eev
699 -- WHERE eev.element_entry_id = ele.element_entry_id
700 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
701 -- AND eev.effective_end_date
702 -- AND eev.input_value_id = p_qualifying_days_iv_id
703 -- ) Qualifying_days
704 ,fnd_number.canonical_to_number('0.0') SSP_days_due
705 -- ,( SELECT fnd_number.canonical_to_number(eev.screen_entry_value)
706 -- FROM pay_element_entry_values_f eev
707 -- WHERE eev.element_entry_id = ele.element_entry_id
708 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
709 -- AND eev.effective_end_date
710 -- AND eev.input_value_id = p_ssp_days_due_iv_id
711 -- ) SSP_days_due
712 ,fnd_number.canonical_to_number('0.0') Withheld_days
713 -- ,( SELECT fnd_number.canonical_to_number(eev.screen_entry_value)
714 -- FROM pay_element_entry_values_f eev
715 -- WHERE eev.element_entry_id = ele.element_entry_id
716 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
717 -- AND eev.effective_end_date
718 -- AND eev.input_value_id = p_withheld_days_iv_id
719 -- ) Withheld_days
720 -- ,fnd_number.canonical_to_number('0.0') SSP_weeks
721 -- ,( SELECT fnd_number.canonical_to_number(eev.screen_entry_value)
722 -- FROM pay_element_entry_values_f eev
723 -- WHERE eev.element_entry_id = ele.element_entry_id
724 -- AND ele.effective_start_date BETWEEN eev.effective_start_date
725 -- AND eev.effective_end_date
726 -- AND eev.input_value_id = p_ssp_week_iv_id
727 -- ) SSP_weeks
728 FROM pay_element_entries_f ele
729 WHERE ele.assignment_id = p_primary_assignment_id -- primary assignment id offline
730 AND ele.element_link_id = p_element_link_id -- run once for evry bg related link
731 AND ele.creator_type = 'S' -- determine piw_id offline
732 AND ele.creator_id = p_piw_id;
733 --
734 --
735 --
736 CURSOR csr_smp_entries
737 (p_maternity_id IN NUMBER
738 ,p_primary_assignment_id IN NUMBER
739 ,p_element_link_id IN NUMBER
740 -- ,p_amount_iv_id IN NUMBER
741 -- ,p_week_commencing_iv_id IN NUMBER
742 -- ,p_rate_iv_id IN NUMBER
743 -- ,p_recoverable_amount_iv_id IN NUMBER
744 ) IS
745 SELECT ele.element_entry_id
746 ,ele.effective_start_date
747 ,ele.effective_end_date
748 ,fnd_number.canonical_to_number('0.0') amount
749 -- ,(SELECT fnd_number.canonical_to_number(piv.screen_entry_value)
750 -- FROM pay_element_entry_values_f piv
751 -- WHERE piv.element_entry_id = ele.element_entry_id
752 -- AND ele.effective_start_date
753 -- BETWEEN piv.effective_start_date
754 -- AND piv.effective_end_date
755 -- AND piv.input_value_id = p_amount_iv_id -- g_smp_input_values(1).id
756 -- ) amount
757 ,fnd_date.canonical_to_date('3712/12/31 00:00:00')week_commencing
758 -- ,(SELECT fnd_date.canonical_to_date(piv.screen_entry_value)
759 -- FROM pay_element_entry_values_f piv
760 -- WHERE piv.element_entry_id = ele.element_entry_id
761 -- AND ele.effective_start_date
762 -- BETWEEN piv.effective_start_date
763 -- AND piv.effective_end_date
764 -- AND piv.input_value_id = p_week_commencing_iv_id -- g_smp_input_values(2).id
765 -- ) week_commencing
766 -- ,rpad(' ',60,' ') rate
767 -- ,(SELECT piv.screen_entry_value rate /* text */
768 -- FROM pay_element_entry_values_f piv
769 -- WHERE piv.element_entry_id = ele.element_entry_id
770 -- AND ele.effective_start_date
771 -- BETWEEN piv.effective_start_date
772 -- AND piv.effective_end_date
773 -- AND piv.input_value_id = p_rate_iv_id -- g_smp_input_values(3).id
774 -- )
775 -- ,fnd_number.canonical_to_number(piv.screen_entry_value) recoverable_amount
776 -- ,(SELECT fnd_number.canonical_to_number(piv.screen_entry_value) recoverable_amount
777 -- FROM pay_element_entry_values_f piv
778 -- WHERE piv.element_entry_id = ele.element_entry_id
779 -- AND ele.effective_start_date
780 -- BETWEEN piv.effective_start_date
781 -- AND piv.effective_end_date
782 -- AND piv.input_value_id = p_recoverable_amount_iv_id -- g_smp_input_values(4).id
783 -- )
784 FROM pay_element_entries_f ele
785 WHERE ele.assignment_id = p_primary_assignment_id -- primary assignment id offline
786 AND ele.element_link_id = p_element_link_id
787 AND ele.creator_type = 'M'
788 AND ele.creator_id = p_maternity_id; -- determine maternity id offline
789 --
790 --
791 --
792 CURSOR get_element_entry_value
793 (p_element_entry_id IN NUMBER
794 ,p_effective_date IN DATE
795 ,p_input_value_id IN NUMBER
796 ) IS
797 SELECT eev.screen_entry_value
798 FROM pay_element_entry_values_f eev
799 WHERE eev.element_entry_id = p_element_entry_id
800 AND p_effective_date
801 BETWEEN eev.effective_start_date
802 AND eev.effective_end_date
803 AND eev.input_value_id = p_input_value_id;
804 --
805 --
806 --
807 CURSOR csr_absence_details
808 (p_absence_attendance_id IN NUMBER
809 ) IS
810 SELECT person_id
811 ,business_group_id
812 ,date_start
813 ,date_end
814 ,sickness_start_date
815 ,sickness_end_date
816 ,date_projected_start
817 ,date_projected_end
818 ,maternity_id -- needed to find SMP element entries
819 ,linked_absence_id -- needed to find SSP element entries
820 FROM per_absence_attendances abs
821 WHERE abs.absence_attendance_id = p_absence_attendance_id;
822 --
823 --
824 --
825 CURSOR csr_max_ssp_period
826 (p_element_type_id IN NUMBER
827 ,p_effective_date IN DATE
828 ) IS
829 SELECT (fnd_number.canonical_to_number(element_information1) * 7) max_value
830 FROM pay_element_types_f
831 WHERE element_type_id = p_element_type_id
832 AND p_effective_date
833 BETWEEN effective_start_date
834 AND effective_end_date;
835 --
836 --
837 --
838 CURSOR csr_absence_primary_assignment
839 (p_absence_id IN NUMBER
840 ) IS
841 SELECT asg.assignment_id
842 FROM per_absence_attendances abs
843 ,per_all_assignments_f asg
844 WHERE abs.absence_attendance_id = p_absence_id
845 AND asg.person_id = abs.person_id
846 AND NVL(abs.date_start,NVL(abs.sickness_start_date,SYSDATE))
847 BETWEEN asg.effective_start_date
848 AND asg.effective_end_date
849 AND asg.primary_flag = 'Y';
850 --
851 --
852 --
853 CURSOR csr_calendar_usages
854 (--p_purpose_usage_id IN NUMBER
855 p_entity_name IN VARCHAR2
856 ,p_primary_key_value IN NUMBER
857 ,p_effective_date IN DATE
858 ) IS
859 SELECT cu.purpose_usage_id
860 ,cu.primary_key_value
861 FROM hr_calendar_usages cu
862 ,hr_pattern_purpose_usages ppu
863 WHERE ppu.pattern_purpose = 'QUALIFYING PATTERN'
864 AND ppu.entity_name = p_entity_name
865 AND cu.purpose_usage_id = ppu.purpose_usage_id
866 AND cu.primary_key_value = p_primary_key_value
867 AND p_effective_date BETWEEN cu.start_date and cu.end_date;
868 --
869 --
870 --
871 CURSOR csr_smp_info
872 (p_smp_element_type_id IN NUMBER
873 ,p_effective_date IN DATE
874 ) IS
875 SELECT fnd_number.canonical_to_number(elt.element_information1) earliest_start_mpp
876 ,fnd_number.canonical_to_number(elt.element_information2) qualifying_week
877 ,fnd_number.canonical_to_number(elt.element_information4) max_mpp_weeks
878 ,fnd_number.canonical_to_number(elt.element_information9) high_rate
879 ,fnd_number.canonical_to_number(elt.element_information10) low_rate
880 ,fnd_number.canonical_to_number(elt.element_information14) weeks_higher_rate
881 FROM pay_element_types_f elt
882 WHERE elt.element_type_id = p_smp_element_type_id
883 AND p_effective_date
884 BETWEEN elt.effective_start_date
885 AND elt.effective_end_date;
886 --
887 --
888 --
889 CURSOR csr_max_smp_period
890 (p_element_type_id IN NUMBER
891 ,p_effective_date IN DATE
892 ) IS
893 SELECT (fnd_number.canonical_to_number(element_information4) * 7) max_value
894 FROM pay_element_types_f
895 WHERE element_type_id = p_element_type_id
896 AND p_effective_date
897 BETWEEN effective_start_date
898 AND effective_end_date;
899 --
900 --
901 --
902 ------------------pqp_get_absence_attendances--------------------
903 FUNCTION pqp_get_absence_attendances(
904 p_absence_attendance_id IN NUMBER
905 ,p_col_name IN VARCHAR2
906 ,p_error_code OUT NOCOPY NUMBER
907 ,p_message OUT NOCOPY VARCHAR2
908 )
909 RETURN VARCHAR2;
910
911 ------------------pqp_get_absence_further_info----------
912 FUNCTION pqp_get_absence_further_info(
913 p_business_group_id IN NUMBER
914 ,p_effective_date IN DATE
915 ,p_absence_attendance_id IN NUMBER
916 ,p_segment_name IN VARCHAR2
917 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
918 ,p_error_code OUT NOCOPY NUMBER
919 ,p_message OUT NOCOPY VARCHAR2
920 )
921 RETURN VARCHAR2;
922
923 ------------------pqp_get_absence_addnl_attr------------------
924 FUNCTION pqp_get_absence_addnl_attr(
925 p_business_group_id IN NUMBER
926 ,p_effective_date IN DATE
927 ,p_absence_attendance_id IN NUMBER
928 ,p_segment_name IN VARCHAR2
929 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
930 ,p_error_code OUT NOCOPY NUMBER
931 ,p_message OUT NOCOPY VARCHAR2
932 )
933 RETURN VARCHAR2;
934
935 ------------------pqp_get_ssp_medicals_details--------------------
936 FUNCTION pqp_get_ssp_medicals_details(
937 p_absence_attendance_id IN NUMBER
938 ,p_col_name IN VARCHAR2
939 ,p_error_code OUT NOCOPY NUMBER
940 ,p_message OUT NOCOPY VARCHAR2
941 )
942 RETURN VARCHAR2;
943
944 ------------------pqp_get_ssp_medical_addnl_attr------------------
945 FUNCTION pqp_get_ssp_medical_addnl_attr(
946 p_business_group_id IN NUMBER
947 ,p_effective_date IN DATE
948 ,p_absence_attendance_id IN NUMBER
949 ,p_segment_name IN VARCHAR2
950 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
951 ,p_error_code OUT NOCOPY NUMBER
952 ,p_message OUT NOCOPY VARCHAR2
953 )
954 RETURN VARCHAR2;
955
956 ------------------pqp_get_ssp_matrnty_details--------------------
957 FUNCTION pqp_get_ssp_matrnty_details(
958 p_absence_attendance_id IN NUMBER
959 ,p_col_name IN VARCHAR2
960 ,p_error_code OUT NOCOPY NUMBER
961 ,p_message OUT NOCOPY VARCHAR2
962 )
963 RETURN VARCHAR2;
964
965 ------------------pqp_get_ssp_matrnty_addnl_attr------------------
966 FUNCTION pqp_get_ssp_matrnty_addnl_attr(
967 p_business_group_id IN NUMBER
968 ,p_effective_date IN DATE
969 ,p_absence_attendance_id IN NUMBER
970 ,p_segment_name IN VARCHAR2
971 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
972 ,p_error_code OUT NOCOPY NUMBER
973 ,p_message OUT NOCOPY VARCHAR2
974 )
975 RETURN VARCHAR2;
976
977 ------------------pqp_get_plan_extra_info------------------
978 FUNCTION pqp_get_plan_extra_info(
979 p_pl_id IN NUMBER
980 ,p_information_type IN VARCHAR2
981 ,p_segment_name IN VARCHAR2
982 ,p_value OUT NOCOPY VARCHAR2
983 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
984 ,p_error_msg OUT NOCOPY VARCHAR2
985 )
986 RETURN NUMBER;
987
988 ------------------pqp_get_other_plan_extra_info------------------
989 FUNCTION pqp_get_other_plan_extra_info(
990 p_business_group_id IN NUMBER
991 ,p_effective_date IN DATE
992 ,p_pl_name IN VARCHAR2
993 ,p_information_type IN VARCHAR2
994 ,p_segment_name IN VARCHAR2
995 ,p_value OUT NOCOPY VARCHAR2
996 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
997 ,p_error_msg OUT NOCOPY VARCHAR2
998 )
999 RETURN NUMBER;
1000
1001 ------------------pqp_get_osp_plan_extra_info------------------
1002 FUNCTION pqp_get_osp_pl_extra_info(
1003 p_pl_id IN NUMBER
1004 ,p_segment_name IN VARCHAR2
1005 ,p_value OUT NOCOPY VARCHAR2
1006 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1007 ,p_error_msg OUT NOCOPY VARCHAR2
1008 )
1009 RETURN NUMBER;
1010
1011 ------------------pqp_get_osp_oth_plan_extra_info------------------
1012 FUNCTION pqp_get_osp_oth_pl_extra_info(
1013 p_business_group_id IN NUMBER
1014 ,p_effective_date IN DATE
1015 ,p_pl_name IN VARCHAR2
1016 ,p_segment_name IN VARCHAR2
1017 ,p_value OUT NOCOPY VARCHAR2
1018 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1019 ,p_error_msg OUT NOCOPY VARCHAR2
1020 )
1021 RETURN NUMBER;
1022
1023 ------------------ben_get_absence_id------------------
1024 FUNCTION ben_get_absence_id(
1025 p_assignment_id IN NUMBER
1026 ,p_effective_date IN DATE
1027 )
1028 RETURN NUMBER;
1029
1030 ------------------ben_get_per_abs_attendances--------------------
1031 FUNCTION ben_get_per_abs_attendances(
1032 p_assignment_id IN NUMBER
1033 ,p_effective_date IN DATE
1034 ,p_col_name IN VARCHAR2
1035 ,p_error_code OUT NOCOPY NUMBER
1036 ,p_message OUT NOCOPY VARCHAR2
1037 )
1038 RETURN VARCHAR2;
1039
1040 ------------------ben_get_absence_further_info----------
1041 FUNCTION ben_get_absence_further_info(
1042 p_business_group_id IN NUMBER
1043 ,p_effective_date IN DATE
1044 ,p_assignment_id IN NUMBER
1045 ,p_segment_name IN VARCHAR2
1046 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1047 ,p_error_code OUT NOCOPY NUMBER
1048 ,p_message OUT NOCOPY VARCHAR2
1049 )
1050 RETURN VARCHAR2;
1051
1052 ------------------ben_get_absence_addnl_attr------------------
1053 FUNCTION ben_get_absence_addnl_attr(
1054 p_business_group_id IN NUMBER
1055 ,p_effective_date IN DATE
1056 ,p_assignment_id IN NUMBER
1057 ,p_segment_name IN VARCHAR2
1058 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1059 ,p_error_code OUT NOCOPY NUMBER
1060 ,p_message OUT NOCOPY VARCHAR2
1061 )
1062 RETURN VARCHAR2;
1063
1064 ------------------ben_get_ssp_medical_details--------------------
1065 FUNCTION ben_get_ssp_medicals_details(
1066 p_assignment_id IN NUMBER
1067 ,p_effective_date IN DATE
1068 ,p_col_name IN VARCHAR2
1069 ,p_error_code OUT NOCOPY NUMBER
1070 ,p_message OUT NOCOPY VARCHAR2
1071 )
1072 RETURN VARCHAR2;
1073
1074 ------------------ben_get_ssp_medical_addnl_attr------------------
1075 FUNCTION ben_get_ssp_medical_addnl_attr(
1076 p_business_group_id IN NUMBER
1077 ,p_effective_date IN DATE
1078 ,p_assignment_id IN NUMBER
1079 ,p_segment_name IN VARCHAR2
1080 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1081 ,p_error_code OUT NOCOPY NUMBER
1082 ,p_message OUT NOCOPY VARCHAR2
1083 )
1084 RETURN VARCHAR2;
1085
1086 ------------------ben_get_ssp_matrnty_details--------------------
1087 FUNCTION ben_get_ssp_matrnty_details(
1088 p_assignment_id IN NUMBER
1089 ,p_effective_date IN DATE
1090 ,p_col_name IN VARCHAR2
1091 ,p_error_code OUT NOCOPY NUMBER
1092 ,p_message OUT NOCOPY VARCHAR2
1093 )
1094 RETURN VARCHAR2;
1095
1096 ------------------ben_get_ssp_matrnty_addnl_attr------------------
1097 FUNCTION ben_get_ssp_matrnty_addnl_attr(
1098 p_business_group_id IN NUMBER
1099 ,p_effective_date IN DATE
1100 ,p_assignment_id IN NUMBER
1101 ,p_segment_name IN VARCHAR2
1102 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1103 ,p_error_code OUT NOCOPY NUMBER
1104 ,p_message OUT NOCOPY VARCHAR2
1105 )
1106 RETURN VARCHAR2;
1107
1108 ------------------------get_lookup_code---------------------------
1109 FUNCTION get_lookup_code(
1110 p_lookup_type IN VARCHAR2
1111 ,p_lookup_meaning IN VARCHAR2
1112 ,p_message OUT NOCOPY VARCHAR2
1113 )
1114 RETURN VARCHAR2;
1115
1116 ------------------get_absence_details--------------------
1117 FUNCTION get_absence_details(
1118 p_absence_attendance_id IN NUMBER
1119 ,p_title IN VARCHAR2
1120 ,p_error_code OUT NOCOPY NUMBER
1121 ,p_message OUT NOCOPY VARCHAR2
1122 )
1123 RETURN VARCHAR2;
1124
1125 ------------------ben_get_absence_details--------------------
1126 FUNCTION ben_get_absence_details(
1127 p_assignment_id IN NUMBER
1128 ,p_effective_date IN DATE
1129 ,p_title IN VARCHAR2
1130 ,p_error_code OUT NOCOPY NUMBER
1131 ,p_message OUT NOCOPY VARCHAR2
1132 )
1133 RETURN VARCHAR2;
1134
1135 ------------------get_medical_details--------------------
1136 FUNCTION get_medical_details(
1137 p_absence_attendance_id IN NUMBER
1138 ,p_title IN VARCHAR2
1139 ,p_error_code OUT NOCOPY NUMBER
1140 ,p_message OUT NOCOPY VARCHAR2
1141 )
1142 RETURN VARCHAR2;
1143
1144 ------------------get_matrnty_details--------------------
1145 FUNCTION get_matrnty_details(
1146 p_absence_attendance_id IN NUMBER
1147 ,p_title IN VARCHAR2
1148 ,p_error_code OUT NOCOPY NUMBER
1149 ,p_message OUT NOCOPY VARCHAR2
1150 )
1151 RETURN VARCHAR2;
1152
1153 ------------------get_LOS_based_entitlements--------------------
1154
1155 FUNCTION get_los_based_entitlements
1156 (p_assignment_id IN NUMBER
1157 ,p_business_group_id IN NUMBER
1158 ,p_effective_date IN DATE
1159 ,p_pl_id IN NUMBER
1160 ,p_absence_pay_plan_class IN VARCHAR2
1161 ,p_entitlement_table_id IN NUMBER
1162 ,p_benefits_length_of_service IN NUMBER
1163 ,p_band_entitlements OUT NOCOPY pqp_absval_pkg.t_entitlements --t_band_info
1164 ,p_error_msg OUT NOCOPY VARCHAR2
1165 ,p_omp_intend_to_return_to_work IN VARCHAR2 DEFAULT 'X'
1166 ,p_entitlement_bands_list_name IN VARCHAR2
1167 DEFAULT 'PQP_GAP_ENTITLEMENT_BANDS'
1168 ,p_is_ent_override IN OUT NOCOPY BOOLEAN
1169 )
1170 RETURN NUMBER;
1171
1172 ------------------pqp_get_band_ent_parameters--------------------
1173
1174 FUNCTION pqp_get_band_ent_parameters(
1175 p_business_group_id IN NUMBER
1176 ,p_effective_date IN DATE
1177 ,p_absence_pay_plan_class IN VARCHAR2
1178 ,p_entitlement_table_id IN NUMBER
1179 ,p_level_of_entitlement IN VARCHAR2
1180 ,p_entitlement_parameters OUT NOCOPY r_entitlement_parameters
1181 ,p_error_msg OUT NOCOPY VARCHAR2
1182 )
1183 RETURN NUMBER;
1184
1185 ------------------get_entitlement_parameters--------------------
1186
1187 FUNCTION get_entitlement_parameters -- pqp_get_entitlement_parameters
1188 (p_business_group_id IN NUMBER
1189 ,p_effective_date IN DATE
1190 ,p_assignment_id IN NUMBER
1191 ,p_pl_id IN NUMBER
1192 ,p_absence_pay_plan_class IN VARCHAR2
1193 ,p_entitlement_table_id IN NUMBER
1194 ,p_benefits_length_of_service IN NUMBER
1195 ,p_entitlement_parameters OUT NOCOPY t_entitlement_parameters
1196 ,p_error_msg OUT NOCOPY VARCHAR2
1197 ,p_omp_intend_to_return_to_work IN VARCHAR2 DEFAULT 'X'
1198 ,p_entitlement_bands_list_name IN VARCHAR2
1199 DEFAULT 'PQP_GAP_ENTITLEMENT_BANDS'
1200 )
1201 RETURN NUMBER;
1202
1203 -------------pqp_get_band_ent_value------------------------------
1204
1205 FUNCTION pqp_get_band_ent_value
1206 (p_business_group_id IN NUMBER
1207 ,p_effective_date IN DATE
1208 ,p_assignment_id IN NUMBER -- Context #3
1209 ,p_element_type_id IN NUMBER -- Context #4
1210 ,p_entitlement_tab_id IN NUMBER
1211 ,p_benefits_length_of_service IN NUMBER
1212 ,p_band1_entitlement OUT NOCOPY NUMBER
1213 ,p_band1_percentage OUT NOCOPY NUMBER
1214 ,p_band2_entitlement OUT NOCOPY NUMBER
1215 ,p_band2_percentage OUT NOCOPY NUMBER
1216 ,p_band3_entitlement OUT NOCOPY NUMBER
1217 ,p_band3_percentage OUT NOCOPY NUMBER
1218 ,p_band4_entitlement OUT NOCOPY NUMBER
1219 ,p_band4_percentage OUT NOCOPY NUMBER
1220 ,p_error_msg OUT NOCOPY VARCHAR2
1221 ,p_entitlement_bands_list_name IN VARCHAR2 DEFAULT
1222 'PQP_GAP_ENTITLEMENT_BANDS'
1223 ,p_override_effective_date IN DATE DEFAULT NULL
1224 ) RETURN NUMBER;
1225
1226 ------------------pqp_get_maternity_id------------------
1227 FUNCTION pqp_get_maternity_id(
1228 p_absence_id IN NUMBER
1229 ,p_message OUT NOCOPY VARCHAR2
1230 )
1231 RETURN NUMBER;
1232
1233 ------------------pqp_get_medical_id------------------
1234 FUNCTION pqp_get_medical_id(
1235 p_absence_id IN NUMBER
1236 ,p_message OUT NOCOPY VARCHAR2
1237 )
1238 RETURN NUMBER;
1239
1240 ----------------pqp_gb_get_no_of_holidays---------------
1241 FUNCTION pqp_gb_get_no_of_holidays(
1242 p_business_group_id IN NUMBER
1243 ,p_abs_start_date IN DATE
1244 ,p_abs_end_date IN DATE
1245 ,p_table_id IN NUMBER DEFAULT NULL
1246 ,p_column_name IN VARCHAR2 DEFAULT NULL
1247 ,p_value IN VARCHAR2 DEFAULT NULL
1248 )
1249 RETURN NUMBER;
1250
1251 ----------------pqp_gb_get_calendar_abs_days---------------
1252 FUNCTION pqp_gb_get_calendar_days(p_start_date IN DATE, p_end_date IN DATE)
1253 RETURN NUMBER;
1254
1255 ----------------pqp_gb_get_cal_abs_hol_days---------------
1256 FUNCTION pqp_gb_get_cal_abs_hol_days(
1257 p_business_group_id IN NUMBER
1258 ,p_abs_start_date IN DATE
1259 ,p_abs_end_date IN DATE
1260 ,p_holidays OUT NOCOPY NUMBER
1261 ,p_table_id IN NUMBER DEFAULT NULL
1262 ,p_column_name IN VARCHAR2 DEFAULT NULL
1263 ,p_value IN VARCHAR2 DEFAULT NULL
1264 )
1265 RETURN NUMBER;
1266
1267 ----------------pqp_gb_get_cal_abs_days---------------
1268 FUNCTION pqp_gb_get_cal_abs_days(
1269 p_business_group_id IN NUMBER
1270 ,p_abs_start_date IN DATE
1271 ,p_abs_end_date IN DATE
1272 ,p_holidays OUT NOCOPY NUMBER
1273 ,p_table_id IN NUMBER DEFAULT NULL
1274 ,p_column_name IN VARCHAR2 DEFAULT NULL
1275 ,p_value IN VARCHAR2 DEFAULT NULL
1276 )
1277 RETURN NUMBER;
1278
1279 ----------------pqp_gb_get_cal_abs_days---------------
1280 FUNCTION pqp_gb_get_no_of_work_holidays(
1281 p_business_group_id IN NUMBER
1282 ,p_work_dates IN pqp_schedule_calculation_pkg.t_working_dates
1283 ,p_table_id IN NUMBER
1284 ,p_column_name IN VARCHAR2
1285 ,p_value IN VARCHAR2
1286 )
1287 RETURN NUMBER;
1288
1289 ----------------pqp_gb_get_work_abs_days_udt---------------
1290 FUNCTION pqp_gb_get_work_abs_days_udt(
1291 p_assignment_id IN NUMBER
1292 ,p_business_group_id IN NUMBER
1293 ,p_start_date IN DATE
1294 ,p_end_date IN DATE
1295 ,p_default_wp IN VARCHAR2
1296 ,p_table_id IN NUMBER
1297 ,p_column_name IN VARCHAR2
1298 ,p_value IN VARCHAR2
1299 ,p_holidays OUT NOCOPY NUMBER
1300 ,p_error_code OUT NOCOPY NUMBER
1301 ,p_error_message OUT NOCOPY VARCHAR2
1302 )
1303 RETURN NUMBER;
1304
1305 ----------------pqp_gb_get_work_abs_days---------------
1306 FUNCTION pqp_gb_get_work_abs_days(
1307 p_assignment_id IN NUMBER
1308 ,p_business_group_id IN NUMBER
1309 ,p_start_date IN DATE
1310 ,p_end_date IN DATE
1311 ,p_holidays OUT NOCOPY NUMBER
1312 ,p_error_code OUT NOCOPY NUMBER
1313 ,p_error_message OUT NOCOPY VARCHAR2
1314 ,p_default_wp IN VARCHAR2
1315 ,p_table_id IN NUMBER DEFAULT NULL
1316 ,p_column_name IN VARCHAR2 DEFAULT NULL
1317 ,p_value IN VARCHAR2 DEFAULT NULL
1318 )
1319 RETURN NUMBER;
1320
1321 -------------pqp_get_omp_band_ent_value------------------------------
1322 FUNCTION pqp_get_omp_band_ent_value
1323 (p_business_group_id IN NUMBER
1324 ,p_effective_date IN DATE
1325 ,p_assignment_id IN NUMBER -- Context #3
1326 ,p_element_type_id IN NUMBER -- Context #4
1327 ,p_entitlement_tab_id IN NUMBER
1328 ,p_benefits_length_of_service IN NUMBER
1329 ,p_return_to_work IN VARCHAR2
1330 ,p_band1_entitlement OUT NOCOPY NUMBER
1331 ,p_band1_percentage OUT NOCOPY NUMBER
1332 ,p_band1_avg_rec_ind OUT NOCOPY VARCHAR2
1333 ,p_band2_entitlement OUT NOCOPY NUMBER
1334 ,p_band2_percentage OUT NOCOPY NUMBER
1335 ,p_band2_avg_rec_ind OUT NOCOPY VARCHAR2
1336 ,p_band3_entitlement OUT NOCOPY NUMBER
1337 ,p_band3_percentage OUT NOCOPY NUMBER
1338 ,p_band3_avg_rec_ind OUT NOCOPY VARCHAR2
1339 ,p_band4_entitlement OUT NOCOPY NUMBER
1340 ,p_band4_percentage OUT NOCOPY NUMBER
1341 ,p_band4_avg_rec_ind OUT NOCOPY VARCHAR2
1342 ,p_error_msg OUT NOCOPY VARCHAR2
1343 ,p_entitlement_bands_list_name IN VARCHAR2 DEFAULT
1344 'PQP_GAP_ENTITLEMENT_BANDS'
1345 ,p_override_effective_date IN DATE DEFAULT NULL
1346 ) RETURN NUMBER;
1347
1348 -------------get_next_working_date------------------------------
1349
1350 FUNCTION get_next_working_date(
1351 p_assignment_id IN NUMBER
1352 ,p_business_group_id IN NUMBER
1353 ,p_date_start IN DATE
1354 ,p_days IN NUMBER
1355 ,p_error_code OUT NOCOPY NUMBER
1356 ,p_error_message OUT NOCOPY VARCHAR2
1357 ,p_default_wp IN VARCHAR2 DEFAULT NULL
1358 ,p_table_id IN NUMBER DEFAULT NULL
1359 ,p_column_name IN VARCHAR2 DEFAULT NULL
1360 ,p_value IN VARCHAR2 DEFAULT NULL
1361 )
1362 RETURN DATE;
1363
1364 ------------------pqp_get_omp_plan_extra_info------------------
1365 FUNCTION pqp_get_omp_pl_extra_info(
1366 p_pl_id IN NUMBER
1367 ,p_segment_name IN VARCHAR2
1368 ,p_value OUT NOCOPY VARCHAR2
1369 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1370 ,p_error_msg OUT NOCOPY VARCHAR2
1371 )
1372 RETURN NUMBER;
1373
1374 ------------------pqp_get_omp_oth_plan_extra_info------------------
1375 FUNCTION pqp_get_omp_oth_pl_extra_info(
1376 p_business_group_id IN NUMBER
1377 ,p_effective_date IN DATE
1378 ,p_pl_name IN VARCHAR2
1379 ,p_segment_name IN VARCHAR2
1380 ,p_value OUT NOCOPY VARCHAR2
1381 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1382 ,p_error_msg OUT NOCOPY VARCHAR2
1383 )
1384 RETURN NUMBER;
1385
1386 ------------ PQP_GB_GET_ABSENCE_SSP ----------------------
1387 FUNCTION pqp_gb_get_absence_ssp
1388 (p_business_group_id IN NUMBER
1389 ,p_assignment_id IN NUMBER
1390 ,p_absence_id IN NUMBER
1391 ,p_start_date IN DATE
1392 ,p_end_date IN DATE
1393 ,p_range_total OUT NOCOPY NUMBER
1394 ,p_absence_total OUT NOCOPY NUMBER
1395 ,p_error_code OUT NOCOPY NUMBER
1396 ,p_error_msg OUT NOCOPY VARCHAR2
1397 ) RETURN NUMBER;
1398
1399 ------------ PQP_GB_GET_ABSENCE_SMP ----------------------
1400 FUNCTION pqp_gb_get_absence_smp
1401 (p_business_group_id IN NUMBER
1402 ,p_assignment_id IN NUMBER
1403 ,p_absence_id IN NUMBER
1404 ,p_start_date IN DATE
1405 ,p_end_date IN DATE
1406 ,p_range_total OUT NOCOPY NUMBER
1407 ,p_absence_total OUT NOCOPY NUMBER
1408 ,p_error_code OUT NOCOPY NUMBER
1409 ,p_error_msg OUT NOCOPY VARCHAR2
1410 ) RETURN NUMBER;
1411
1412 ------------BEN_MATRNTY_DETAILS----------------------
1413 FUNCTION ben_matrnty_details(
1414 p_assignment_id IN NUMBER
1415 ,p_effective_date IN DATE
1416 ,p_title IN VARCHAR2
1417 ,p_error_code OUT NOCOPY NUMBER
1418 ,p_message OUT NOCOPY VARCHAR2
1419 )
1420 RETURN VARCHAR2;
1421
1422 ------------BEN_MEDICAL_DETAILS----------------------
1423 FUNCTION ben_medical_details(
1424 p_assignment_id IN NUMBER
1425 ,p_effective_date IN DATE
1426 ,p_title IN VARCHAR2
1427 ,p_error_code OUT NOCOPY NUMBER
1428 ,p_message OUT NOCOPY VARCHAR2
1429 )
1430 RETURN VARCHAR2;
1431
1432 -----------------------------------------------------------
1433 --This function have three variants for Entitled, Paid, Work Pattern Columns
1434 --p_search_start_date, p_search_end_date should be checked against absence start
1435 -- date
1436 --and absence end date. For search start date greatest (
1437 -- search_start_date,absence_start_date)
1438 --for search end date least(search_end_date,absence_end_date)
1439 FUNCTION get_abs_plan_ent_days_info(
1440 p_absence_attendance_id IN NUMBER
1441 ,p_pl_id IN NUMBER
1442 ,p_error_code OUT NOCOPY NUMBER
1443 ,p_error_message OUT NOCOPY VARCHAR2
1444 ,p_search_start_date IN DATE DEFAULT NULL
1445 ,p_search_end_date IN DATE DEFAULT NULL
1446 ,p_level_of_entitlement IN VARCHAR2 DEFAULT NULL
1447 )
1448 RETURN NUMBER;
1449
1450 ------------------------------------------------------------
1451 FUNCTION get_abs_plan_paid_days_info(
1452 p_absence_attendance_id IN NUMBER
1453 ,p_pl_id IN NUMBER
1454 ,p_error_code OUT NOCOPY NUMBER
1455 ,p_error_message OUT NOCOPY VARCHAR2
1456 ,p_search_start_date IN DATE DEFAULT NULL
1457 ,p_search_end_date IN DATE DEFAULT NULL
1458 ,p_level_of_pay IN VARCHAR2 DEFAULT NULL
1459 )
1460 RETURN NUMBER;
1461
1462 ------------------------------------------------------------
1463 FUNCTION get_abs_plan_wp_info(
1464 p_absence_attendance_id IN NUMBER
1465 ,p_pl_id IN NUMBER
1466 ,p_error_code OUT NOCOPY NUMBER
1467 ,p_error_message OUT NOCOPY VARCHAR2
1468 ,p_search_start_date IN DATE DEFAULT NULL
1469 ,p_search_end_date IN DATE DEFAULT NULL
1470 ,p_work_pattern_day_type IN VARCHAR2 DEFAULT NULL
1471 )
1472 RETURN NUMBER;
1473
1474 -----------------------------------------
1475 FUNCTION get_osp_band_paid_entitlements(
1476 p_absence_attendance_id IN NUMBER
1477 ,p_pl_id IN NUMBER
1478 ,p_band1_entitled OUT NOCOPY NUMBER
1479 ,p_band1_paid OUT NOCOPY NUMBER
1480 ,p_band2_entitled OUT NOCOPY NUMBER
1481 ,p_band2_paid OUT NOCOPY NUMBER
1482 ,p_band3_entitled OUT NOCOPY NUMBER
1483 ,p_band3_paid OUT NOCOPY NUMBER
1484 ,p_band4_entitled OUT NOCOPY NUMBER
1485 ,p_band4_paid OUT NOCOPY NUMBER
1486 ,p_noband_entitled OUT NOCOPY NUMBER
1487 ,p_noband_paid OUT NOCOPY NUMBER
1488 ,p_error_code OUT NOCOPY NUMBER
1489 ,p_error_message OUT NOCOPY VARCHAR2
1490 ,p_search_start_date IN DATE DEFAULT NULL
1491 ,p_search_end_date IN DATE DEFAULT NULL
1492 )
1493 RETURN NUMBER;
1494
1495 -----------------------------------------------
1496 FUNCTION chk_calendar_occurance(
1497 p_date IN DATE
1498 ,p_calendar_table_id IN NUMBER
1499 ,p_calendar_rules_list IN VARCHAR2
1500 ,p_cal_rul_name OUT NOCOPY VARCHAR2
1501 ,p_cal_day_name OUT NOCOPY VARCHAR2
1502 ,p_cal_rule_value OUT NOCOPY VARCHAR2
1503 ,p_error_code OUT NOCOPY NUMBER
1504 ,p_error_message OUT NOCOPY VARCHAR2
1505 ,p_cal_value IN VARCHAR2 DEFAULT NULL
1506 ,p_filter IN VARCHAR2 DEFAULT 'AllMatch'
1507 )
1508 RETURN NUMBER;
1509
1510 ----------------------------------------------------------------
1511 FUNCTION get_band_entitlement_balance(
1512 p_business_group_id IN NUMBER
1513 ,p_effective_date IN DATE
1514 ,p_assignment_id IN NUMBER
1515 ,p_pl_typ_id IN NUMBER
1516 ,p_scheme_calendar_type IN VARCHAR2
1517 ,p_scheme_calendar_duration IN VARCHAR2
1518 ,p_scheme_calendar_uom IN VARCHAR2
1519 ,p_scheme_start_date IN VARCHAR2
1520 ,p_scheme_overlap_rule IN VARCHAR2
1521 ,p_level_of_entitlement IN VARCHAR2
1522 ,p_error_code OUT NOCOPY NUMBER
1523 ,p_error_message OUT NOCOPY VARCHAR2
1524 ,p_days_hours IN VARCHAR2 DEFAULT 'DAYS'
1525 --Added for CS
1526 ,p_default_work_pattern IN VARCHAR2
1527 ,p_plan_types_to_extend_period IN VARCHAR2 -- LG/PT
1528 ,p_entitlement_uom IN VARCHAR2 -- LG/PT
1529 ,p_absence_schedule_wp IN VARCHAR2 -- LG/PT
1530 ,p_track_part_timers IN VARCHAR2 -- LG/PT
1531 ,p_absence_start_date IN DATE DEFAULT NULL
1532
1533 )
1534 RETURN NUMBER;
1535
1536 -----------------------------------------------------
1537 FUNCTION get_band_ent_bal_by_ele_typ_id(
1538 p_business_group_id IN NUMBER
1539 ,p_assignment_id IN NUMBER
1540 ,p_element_type_id IN NUMBER
1541 ,p_effective_date IN DATE
1542 ,p_level_of_entitlement IN VARCHAR2
1543 ,p_error_code OUT NOCOPY NUMBER
1544 ,p_error_message OUT NOCOPY VARCHAR2
1545 ,p_days_hours IN VARCHAR2 DEFAULT 'DAYS'
1546 ,p_absence_start_date IN DATE DEFAULT NULL
1547 )
1548 RETURN NUMBER;
1549
1550 ----------------------------------------------------
1551 FUNCTION get_all_band_ent_balance(
1552 p_business_group_id IN NUMBER
1553 ,p_assignment_id IN NUMBER
1554 ,p_element_type_id IN NUMBER
1555 ,p_effective_date IN DATE
1556 ,p_band1_ent_bal OUT NOCOPY NUMBER
1557 ,p_band2_ent_bal OUT NOCOPY NUMBER
1558 ,p_band3_ent_bal OUT NOCOPY NUMBER
1559 ,p_band4_ent_bal OUT NOCOPY NUMBER
1560 ,p_noband_ent_bal OUT NOCOPY NUMBER
1561 ,p_error_code OUT NOCOPY NUMBER
1562 ,p_error_message OUT NOCOPY VARCHAR2
1563 ,p_absence_start_date IN DATE DEFAULT NULL
1564 )
1565 RETURN NUMBER;
1566
1567 ---------------------------------------------------
1568 FUNCTION get_band_ent_bal_by_pl_id(
1569 p_business_group_id IN NUMBER
1570 ,p_assignment_id IN NUMBER
1571 ,p_pl_id IN NUMBER
1572 ,p_effective_date IN DATE
1573 ,p_level_of_entitlement IN VARCHAR2
1574 ,p_error_code OUT NOCOPY NUMBER
1575 ,p_error_message OUT NOCOPY VARCHAR2
1576 )
1577 RETURN NUMBER;
1578
1579 -------------------------------------------------------------
1580 --function added by sshetty.
1581 FUNCTION get_paid_days_duration(
1582 p_assignment_id IN NUMBER
1583 ,p_business_group_id IN NUMBER
1584 ,p_effective_date IN DATE
1585 ,p_pl_id IN NUMBER
1586 ,p_level_of_pay IN VARCHAR2
1587 ,p_search_start_date IN DATE
1588 ,p_search_end_date IN DATE
1589 ,p_error_code OUT NOCOPY NUMBER
1590 ,p_message OUT NOCOPY VARCHAR2
1591 )
1592 RETURN NUMBER;
1593
1594 --------------------------------------------------------------------
1595 FUNCTION get_entitled_days_duration(
1596 p_assignment_id IN NUMBER
1597 ,p_business_group_id IN NUMBER
1598 ,p_effective_date IN DATE
1599 ,p_pl_id IN NUMBER
1600 ,p_level_of_entitlement IN VARCHAR2
1601 ,p_search_start_date IN DATE
1602 ,p_search_end_date IN DATE
1603 ,p_error_code OUT NOCOPY NUMBER
1604 ,p_message OUT NOCOPY VARCHAR2
1605 )
1606 RETURN NUMBER;
1607
1608 --------------------------------------------------------
1609 FUNCTION get_wp_days_duration(
1610 p_assignment_id IN NUMBER
1611 ,p_business_group_id IN NUMBER
1612 ,p_effective_date IN DATE
1613 ,p_pl_id IN NUMBER
1614 ,p_wp_day_type IN VARCHAR2
1615 ,p_search_start_date IN DATE
1616 ,p_search_end_date IN DATE
1617 ,p_error_code OUT NOCOPY NUMBER
1618 ,p_message OUT NOCOPY VARCHAR2
1619 )
1620 RETURN NUMBER;
1621
1622 --------------------------------------------------------
1623 FUNCTION get_subpriority(
1624 p_business_group_id IN NUMBER
1625 ,p_assignment_id IN NUMBER
1626 ,p_payroll_action_id IN NUMBER
1627 ,p_effective_date IN DATE
1628 ,p_pl_id IN NUMBER
1629 ,p_ler_id IN NUMBER
1630 ,p_absence_start_date IN DATE
1631 )
1632 RETURN NUMBER;
1633
1634 ---------------------------------------------------
1635 FUNCTION get_next_cal_date(
1636 p_business_group_id IN NUMBER
1637 ,p_date_start IN DATE
1638 ,p_days IN NUMBER
1639 ,p_error_code OUT NOCOPY NUMBER
1640 ,p_error_message OUT NOCOPY VARCHAR2
1641 ,p_table_id IN NUMBER DEFAULT NULL
1642 ,p_column_name IN VARCHAR2 DEFAULT NULL
1643 ,p_value IN VARCHAR2 DEFAULT NULL
1644 )
1645 RETURN DATE;
1646
1647 ---------------------------------------------------
1648 -- Added this function for absence DDF context usage
1649 FUNCTION exists_in_gap_lookup(
1650 p_business_group_id IN NUMBER
1651 ,p_lookup_code IN VARCHAR2
1652 ,p_effective_date IN DATE
1653 ,p_lookup_type IN VARCHAR2
1654 DEFAULT 'PQP_GAP_ABSENCE_TYPES_LIST'
1655 )
1656 RETURN BOOLEAN;
1657
1658 ---------------------------------------------------
1659 ---- Added for Daily Absences in OMP from here-----
1660 ---------------------------------------------------
1661
1662 FUNCTION get_omp_all_band_ent_balance(
1663 p_business_group_id IN NUMBER
1664 ,p_assignment_id IN NUMBER
1665 ,p_element_type_id IN NUMBER
1666 ,p_effective_date IN DATE
1667 ,p_band1_ent_bal OUT NOCOPY NUMBER
1668 ,p_band2_ent_bal OUT NOCOPY NUMBER
1669 ,p_band3_ent_bal OUT NOCOPY NUMBER
1670 ,p_band4_ent_bal OUT NOCOPY NUMBER
1671 ,p_noband_ent_bal OUT NOCOPY NUMBER
1672 ,p_error_code OUT NOCOPY NUMBER
1673 ,p_error_message OUT NOCOPY VARCHAR2
1674 )
1675 RETURN NUMBER;
1676
1677 -------------------------------------------------------------
1678 FUNCTION get_omp_band_ent_bal_ele_typ(
1679 p_business_group_id IN NUMBER
1680 ,p_assignment_id IN NUMBER
1681 ,p_element_type_id IN NUMBER
1682 ,p_effective_date IN DATE
1683 ,p_level_of_entitlement IN VARCHAR2
1684 ,p_error_code OUT NOCOPY NUMBER
1685 ,p_error_message OUT NOCOPY VARCHAR2
1686 ,p_days_hours IN VARCHAR2 DEFAULT 'DAYS'
1687 )
1688 RETURN NUMBER;
1689
1690 --------------------------------------------------------------
1691 FUNCTION get_omp_band_ent_bal_pl_typ(
1692 p_business_group_id IN NUMBER
1693 ,p_effective_date IN DATE
1694 ,p_assignment_id IN NUMBER
1695 ,p_pl_typ_id IN NUMBER
1696 ,p_level_of_entitlement IN VARCHAR2
1697 ,p_error_code OUT NOCOPY NUMBER
1698 ,p_error_message OUT NOCOPY VARCHAR2
1699 ,p_days_hours IN VARCHAR2
1700 )
1701 RETURN NUMBER;
1702 ---------------------------------------------------
1703 ---- Added for Daily Absences in OMP End here-----
1704 ---------------------------------------------------
1705
1706 -------------------------------------------------------
1707 ------------- Added for Hours Solution ----------------
1708 -------------------------------------------------------
1709
1710
1711 ----------get_osp_hours_band_paid_ent------------------
1712 FUNCTION get_osp_hours_band_paid_ent(
1713 p_absence_attendance_id IN NUMBER,
1714 p_pl_id IN NUMBER,
1715 p_band1_entitled OUT NOCOPY NUMBER,
1716 p_band2_entitled OUT NOCOPY NUMBER,
1717 p_band3_entitled OUT NOCOPY NUMBER,
1718 p_band4_entitled OUT NOCOPY NUMBER,
1719 p_noband_entitled OUT NOCOPY NUMBER,
1720 p_error_code OUT NOCOPY NUMBER,
1721 p_error_message OUT NOCOPY VARCHAR2,
1722 p_search_start_date IN DATE DEFAULT NULL,
1723 p_search_end_Date IN DATE DEFAULT NULL )
1724 RETURN NUMBER ;
1725
1726 --------------get_all_band_hours_ent_balance------------------
1727
1728 FUNCTION get_all_band_hours_ent_balance(
1729 p_business_group_id IN NUMBER
1730 ,p_assignment_id IN NUMBER
1731 ,p_element_type_id IN NUMBER
1732 ,p_effective_date IN DATE
1733 ,p_band1_ent_bal OUT NOCOPY NUMBER
1734 ,p_band2_ent_bal OUT NOCOPY NUMBER
1735 ,p_band3_ent_bal OUT NOCOPY NUMBER
1736 ,p_band4_ent_bal OUT NOCOPY NUMBER
1737 ,p_noband_ent_bal OUT NOCOPY NUMBER
1738 ,p_error_code OUT NOCOPY NUMBER
1739 ,p_error_message OUT NOCOPY VARCHAR2
1740 )
1741 RETURN NUMBER ;
1742 --------------get_omp_all_band_hours_ent_bal------------------
1743 FUNCTION get_omp_all_band_hours_ent_bal(
1744 p_business_group_id IN NUMBER
1745 ,p_assignment_id IN NUMBER
1746 ,p_element_type_id IN NUMBER
1747 ,p_effective_date IN DATE
1748 ,p_band1_ent_bal OUT NOCOPY NUMBER
1749 ,p_band2_ent_bal OUT NOCOPY NUMBER
1750 ,p_band3_ent_bal OUT NOCOPY NUMBER
1751 ,p_band4_ent_bal OUT NOCOPY NUMBER
1752 ,p_noband_ent_bal OUT NOCOPY NUMBER
1753 ,p_error_code OUT NOCOPY NUMBER
1754 ,p_error_message OUT NOCOPY VARCHAR2
1755 )
1756 RETURN NUMBER ;
1757 -------------------------------------------------------
1758 ------------- Added for Hours Solution ----------------
1759 -------------------------------------------------------
1760 --
1761 --
1762 --
1763 FUNCTION get_first_paid_day
1764 (p_absence_attendance_id IN NUMBER
1765 ,p_pl_id IN NUMBER
1766 ,p_level_of_pay IN VARCHAR2 DEFAULT NULL
1767 ) RETURN DATE;
1768 --
1769 FUNCTION get_last_paid_day
1770 (p_absence_attendance_id IN NUMBER
1771 ,p_pl_id IN NUMBER
1772 ,p_level_of_pay IN VARCHAR2 DEFAULT NULL
1773 ) RETURN DATE;
1774 --
1775 FUNCTION get_first_entitled_day
1776 (p_absence_attendance_id IN NUMBER
1777 ,p_pl_id IN NUMBER
1778 ,p_level_of_entitlement IN VARCHAR2 DEFAULT NULL
1779 ) RETURN DATE;
1780 --
1781 FUNCTION get_last_entitled_day
1782 (p_absence_attendance_id IN NUMBER
1783 ,p_pl_id IN NUMBER
1784 ,p_level_of_entitlement IN VARCHAR2 DEFAULT NULL
1785 ) RETURN DATE;
1786 --
1787
1788 PROCEDURE chk_override_entitlements -- AI and AU USER HOOK PROC pepeihcd.sql
1789 (p_person_extra_info_id IN NUMBER
1790 ,p_person_id IN NUMBER
1791 ,p_information_type IN VARCHAR2
1792 ,p_pei_information_category IN VARCHAR2
1793 ,p_pei_information1 IN VARCHAR2
1794 ,p_pei_information2 IN VARCHAR2
1795 ,p_pei_information3 IN VARCHAR2
1796 ,p_pei_information11 IN VARCHAR2
1797 ,p_pei_information12 IN VARCHAR2
1798 ,p_pei_information13 IN VARCHAR2
1799 ,p_pei_information14 IN VARCHAR2
1800 );
1801
1802 FUNCTION get_absence_ssp
1803 (p_business_group_id IN NUMBER
1804 ,p_assignment_id IN NUMBER
1805 ,p_absence_attendance_id IN NUMBER
1806 ,p_range_start_date IN DATE
1807 ,p_range_end_date IN DATE
1808 ) RETURN NUMBER;
1809
1810 FUNCTION get_period_ssp
1811 (p_business_group_id IN NUMBER -- Context
1812 ,p_assignment_id IN NUMBER -- Context
1813 ,p_range_start_date IN DATE
1814 ,p_range_end_date IN DATE
1815 ) RETURN NUMBER;
1816
1817 FUNCTION get_absence_smp
1818 (p_business_group_id IN NUMBER
1819 ,p_assignment_id IN NUMBER
1820 ,p_absence_attendance_id IN NUMBER
1821 ,p_range_start_date IN DATE
1822 ,p_range_end_date IN DATE
1823 ) RETURN NUMBER;
1824
1825 PROCEDURE clear_cache;
1826
1827 FUNCTION get_absence_paid_days_tp
1828 ( p_assignment_id IN NUMBER
1829 ,p_start_date IN DATE
1830 ,p_end_date IN DATE
1831 ,p_level_of_pay IN VARCHAR2
1832 ) RETURN NUMBER ;
1833
1834 FUNCTION get_all_band_cs_4_yr_ent_bal
1835 ( p_business_group_id IN NUMBER
1836 ,p_assignment_id IN NUMBER
1837 ,p_element_type_id IN NUMBER
1838 ,p_effective_date IN DATE
1839 ,p_band1_ent_bal OUT NOCOPY NUMBER
1840 ,p_band2_ent_bal OUT NOCOPY NUMBER
1841 ,p_band3_ent_bal OUT NOCOPY NUMBER
1842 ,p_band4_ent_bal OUT NOCOPY NUMBER
1843 ,p_noband_ent_bal OUT NOCOPY NUMBER
1844 ,p_error_message OUT NOCOPY VARCHAR2
1845 ) RETURN NUMBER ;
1846
1847
1848 ------------------ For LG/PT
1849 PROCEDURE get_entitlements
1850 (p_assignment_id IN NUMBER
1851 ,p_business_group_id IN NUMBER
1852 ,p_effective_date IN DATE
1853 ,p_pl_id IN NUMBER
1854 ,p_entitlement_table_id IN NUMBER
1855 ,p_benefits_length_of_service IN NUMBER
1856 ,p_band_entitlements OUT NOCOPY pqp_absval_pkg.t_entitlements
1857 ,p_entitlement_bands_list_name IN VARCHAR2 DEFAULT
1858 'PQP_GAP_ENTITLEMENT_BANDS'
1859 ) ;
1860
1861 FUNCTION get_minimum_pay_info
1862 (p_assignment_id IN NUMBER
1863 ,p_business_group_id IN NUMBER
1864 ,p_absence_id IN NUMBER
1865 ,p_minpay_start_date OUT NOCOPY DATE
1866 ,p_minpay_end_date OUT NOCOPY DATE
1867 ) RETURN NUMBER ;
1868
1869 ------------------
1870 FUNCTION get_osp_minimum_pay_rate
1871 (p_assignment_id IN NUMBER
1872 ,p_business_group_id IN NUMBER
1873 ,p_pl_id IN NUMBER
1874 ,p_effective_date IN DATE
1875 ) RETURN NUMBER ;
1876
1877 ------------------
1878
1879 PROCEDURE set_osp_omp_rounding_factors
1880 (p_pl_id IN NUMBER
1881 ,p_pt_entitl_rounding_type OUT NOCOPY VARCHAR2
1882 ,p_pt_rounding_precision OUT NOCOPY NUMBER
1883 ,p_ft_entitl_rounding_type OUT NOCOPY VARCHAR2
1884 ,p_ft_rounding_precision OUT NOCOPY NUMBER
1885 );
1886
1887
1888 FUNCTION chk_absence_belongs_to_person
1889 ( p_assignment_id IN NUMBER
1890 ,p_business_group_id IN NUMBER
1891 ,p_absence_attendance_id IN NUMBER
1892 ) RETURN BOOLEAN ;
1893
1894 FUNCTION get_absence_statutory_pay
1895 (p_business_group_id IN NUMBER
1896 ,p_assignment_id IN NUMBER
1897 ,p_absence_attendance_id IN NUMBER
1898 ,p_start_date IN DATE
1899 ,p_end_date IN DATE
1900 ) RETURN NUMBER ;
1901
1902 PROCEDURE decode_round_config
1903 (p_code IN VARCHAR2
1904 ,p_rounding_type OUT NOCOPY VARCHAR2
1905 ,p_rounding_precision OUT NOCOPY NUMBER
1906 ,p_enb_prorat IN VARCHAR2 DEFAULT 'Y'
1907 );
1908
1909
1910 FUNCTION get_all_band_ent_used_and_rem(
1911 p_business_group_id IN NUMBER
1912 ,p_assignment_id IN NUMBER
1913 ,p_element_type_id IN NUMBER
1914 ,p_date_earned IN DATE
1915 ,p_effective_date IN DATE
1916 ,p_entitlement_tab_id IN NUMBER
1917 ,p_benefits_length_of_service IN NUMBER
1918 ,p_band1_abs_used IN NUMBER
1919 ,p_band2_abs_used IN NUMBER
1920 ,p_band3_abs_used IN NUMBER
1921 ,p_band4_abs_used IN NUMBER
1922 ,p_override_effective_date IN DATE DEFAULT NULL
1923 ,p_scheme_cal_type IN VARCHAR2 DEFAULT 'FIXED'
1924 ,p_band1_ent_used OUT NOCOPY NUMBER
1925 ,p_band2_ent_used OUT NOCOPY NUMBER
1926 ,p_band3_ent_used OUT NOCOPY NUMBER
1927 ,p_band4_ent_used OUT NOCOPY NUMBER
1928 ,p_noband_ent_used OUT NOCOPY NUMBER
1929 ,p_band1_4year_ent_used OUT NOCOPY NUMBER
1930 ,p_band2_4year_ent_used OUT NOCOPY NUMBER
1931 ,p_band3_4year_ent_used OUT NOCOPY NUMBER
1932 ,p_band4_4year_ent_used OUT NOCOPY NUMBER
1933 ,p_noband_4year_ent_used OUT NOCOPY NUMBER
1934 ,p_band1_remaining OUT NOCOPY NUMBER
1935 ,p_band2_remaining OUT NOCOPY NUMBER
1936 ,p_band3_remaining OUT NOCOPY NUMBER
1937 ,p_band4_remaining OUT NOCOPY NUMBER
1938 ,p_band1_percentage OUT NOCOPY NUMBER
1939 ,p_band2_percentage OUT NOCOPY NUMBER
1940 ,p_band3_percentage OUT NOCOPY NUMBER
1941 ,p_band4_percentage OUT NOCOPY NUMBER
1942 ,p_error_msg OUT NOCOPY VARCHAR2
1943 )RETURN NUMBER;
1944
1945
1946 PROCEDURE abs_pension_date_chk( p_date_start IN DATE
1947 ,p_date_end IN DATE
1948 ,p_absence_attendance_id IN NUMBER
1949 ,p_abs_information4 IN VARCHAR2
1950 ,p_abs_information5 IN VARCHAR2
1951 ,p_abs_information6 IN VARCHAR2
1952 -- bug 5975119
1953 ,p_abs_information_category in VARCHAR2 default null
1954 );
1955
1956 PROCEDURE abs_pension_date_check( p_date_start IN DATE
1957 ,p_date_end IN DATE
1958 ,p_abs_information4 IN VARCHAR2
1959 ,p_abs_information5 IN VARCHAR2
1960 ,p_abs_information6 IN VARCHAR2
1961 );
1962
1963 FUNCTION get_ssp_smp_paid_days
1964 ( p_range_start_date IN DATE
1965 ,p_range_end_date IN DATE
1966 ,p_assignment_id IN NUMBER
1967 ) RETURN NUMBER ;
1968
1969 END pqp_gb_osp_functions;
[Home] [Help]