1 PACKAGE pqp_absval_pkg AS
2 /* $Header: pqabsbal.pkh 120.6.12000000.1 2007/01/16 03:29:39 appldev noship $ */
3
4 TYPE rec_plan_information IS RECORD(
5 plan_name pay_element_type_extra_info.eei_information1%TYPE
6 ,scheme_period_type pay_element_type_extra_info.eei_information3%TYPE
7 ,scheme_period_duration pay_element_type_extra_info.eei_information4%TYPE
8 ,scheme_period_uom pay_element_type_extra_info.eei_information5%TYPE
9 ,scheme_period_start pay_element_type_extra_info.eei_information6%TYPE
10 ,scheme_period_end pay_element_type_extra_info.eei_information7%TYPE
11 ,absence_days_type pay_element_type_extra_info.eei_information8%TYPE
12 ,entitlement_parameters_UDT_id pay_element_type_extra_info.eei_information9%TYPE
13 ,entitlement_calendar_UDT_id pay_element_type_extra_info.eei_information10%TYPE
14 ,daily_rate_UOM pay_element_type_extra_info.eei_information11%TYPE
15 ,daily_rate_earnings_period pay_element_type_extra_info.eei_information12%TYPE
16 ,daily_rate_divisor pay_element_type_extra_info.eei_information13%TYPE
17 ,element_type pay_element_type_extra_info.eei_information14%TYPE
18 ,absence_pay_info_src_pay_comp pay_element_type_extra_info.eei_information15%TYPE
19 ,primary_absence_info_ele pay_element_type_extra_info.eei_information16%TYPE
20 ,default_work_pattern_name pay_element_type_extra_info.eei_information17%TYPE
21 ,absence_types_list_name pay_element_type_extra_info.eei_information18%TYPE
22 ,absence_overlap_rule pay_element_type_extra_info.eei_information26%TYPE
23 ,entitlement_band_names_list pay_element_type_extra_info.eei_information28%TYPE
24 ,calendar_rule_names_list pay_element_type_extra_info.eei_information27%TYPE
25 ,absence_pay_plan_category pay_element_type_extra_info.eei_information30%TYPE -- Added for CS
26 ,dual_rolling_period_duration pay_element_type_extra_info.eei_information20%TYPE
27 ,dual_rolling_period_uom pay_element_type_extra_info.eei_information21%TYPE
28 -- Adding for LG/PT
29 ,track_part_timers pay_element_type_extra_info.eei_information21%TYPE
30 ,absence_schedule_work_pattern pay_element_type_extra_info.eei_information21%TYPE
31 ,plan_types_to_extend_period pay_element_type_extra_info.eei_information21%TYPE
32 );
33
34 TYPE r_entitlements
35 IS RECORD(
36 band VARCHAR2(30)
37 ,meaning VARCHAR2(80)
38 ,entitlement NUMBER
39 ,duration NUMBER
40 ,duration_in_hours NUMBER
41 ,duration_per_week NUMBER
42 -- LG/PT
43 ,fte_hours NUMBER
44 );
45
46 TYPE r_duration_summary
47 IS RECORD(
48 assignment_id pqp_gap_duration_summary.assignment_id%type
49 ,gap_absence_plan_id pqp_gap_duration_summary.gap_absence_plan_id%type
50 ,summary_type pqp_gap_duration_summary.summary_type%type
51 ,gap_level pqp_gap_duration_summary.gap_level%type
52 ,date_start pqp_gap_duration_summary.date_start%type
53 ,date_end pqp_gap_duration_summary.date_end%type
54 ,duration_in_days pqp_gap_duration_summary.duration_in_days%type
55 ,duration_in_hours pqp_gap_duration_summary.duration_in_hours%type
56 ,gap_duration_summary_id pqp_gap_duration_summary.gap_duration_summary_id%type
57 ,object_version_number pqp_gap_duration_summary.object_version_number%type
58 ,action_type VARCHAR2(2)
59 );
60
61 TYPE r_absence_balance
62 Is Record(
63 gap_absence_plan_id pqp_gap_duration_summary.gap_absence_plan_id%Type
64 ,gap_level pqp_gap_duration_summary.gap_level%Type
65 ,entitlement_granted NUMBER(10,5)
66 ,entitlement_used_to_date NUMBER(10,5)
67 ,entitlement_used_by_abs NUMBER(10,5)
68 ,entitlement_remaining NUMBER(10,5)
69 ,fte NUMBER(25,5)
70 ,working_days_per_week NUMBER(10,5)
71 ,action_type VARCHAR2(2)
72 );
73
74 TYPE r_gap_level
75 IS RECORD(
76 gap_level VARCHAR2(100)
77 ,gap_duration_summary_id NUMBER(15,0)
78 ,object_version_number NUMBER(15,0)
79 ,action_type VARCHAR2(2)
80 );
81
82
83 TYPE t_entitlements IS TABLE OF r_entitlements
84 INDEX BY BINARY_INTEGER;
85
86
87 TYPE t_daily_absences IS TABLE OF pqp_gda_shd.g_rec_type
88 INDEX BY BINARY_INTEGER;
89
90 TYPE t_duration_summary IS TABLE OF r_duration_summary
91 INDEX BY BINARY_INTEGER;
92
93 TYPE t_absence_balance IS TABLE OF r_absence_balance
94 INDEX BY BINARY_INTEGER;
95
96 TYPE t_gap_level IS TABLE OF r_gap_level
97 INDEX BY BINARY_INTEGER;
98
99 --
100 CURSOR csr_absence_dates
101 (p_absence_attendance_id IN NUMBER
102 )
103 IS
104 SELECT paa.date_start
105 ,paa.date_end
106 FROM per_absence_attendances paa
107 WHERE paa.absence_attendance_id = p_absence_attendance_id;
108 --
109 CURSOR csr_gap_absence_plan
110 (p_absence_attendance_id IN NUMBER
111 ,p_pl_id IN NUMBER
112 )
113 IS
114 SELECT gap.gap_absence_plan_id
115 ,gap.last_gap_daily_absence_date
116 ,gap.object_version_number
117 FROM pqp_gap_absence_plans gap
118 WHERE gap.absence_attendance_id = p_absence_attendance_id
119 AND gap.pl_id = p_pl_id;
120 --
121 CURSOR csr_gap_daily_absences_exists
122 (p_gap_absence_plan_id IN NUMBER
123 )
124 IS
125 SELECT gda.gap_absence_plan_id
126 FROM pqp_gap_daily_absences gda
127 WHERE gda.gap_absence_plan_id = p_gap_absence_plan_id
128 AND ROWNUM < 2;
129 --
130 CURSOR csr_first_entitled_day_of_band
131 (p_gap_absence_plan_id IN NUMBER
132 ,p_level_of_entitlement IN VARCHAR2
133 )
134 IS
135 SELECT gda.absence_date
136 FROM pqp_gap_daily_absences gda
137 WHERE gda.gap_absence_plan_id = p_gap_absence_plan_id
138 AND gda.level_of_entitlement = p_level_of_entitlement
139 ORDER BY gda.absence_date ASC;
140
141
142 --
143 -- the perf version of this query would be
144 -- query the table using gap_absence_plan_id
145 -- and absence_date between sot and eot
146 -- and rownum < 2
147 -- because these two columns are indexed
148 -- and by default Oracle always reads the index
149 -- from the ascending end it would return the
150 -- minimum date of a given level of ent
151 --
152 -- but this approach is not guranteed hence
153 -- use of ORDER BY asc
154 --
155
156
157
158
159 CURSOR csr_get_days_to_extend ( p_business_group_id NUMBER
160 ,p_assignment_id NUMBER
161 ,p_period_start_date DATE
162 ,p_period_end_date DATE
163 ,p_lookup_type VARCHAR2)
164 IS
165 SELECT SUM(
166 DECODE(
167 SIGN(paa.date_end - p_period_end_date)
168 ,1, p_period_end_date
169 ,paa.date_end
170 )
171 - DECODE(
172 SIGN(paa.date_start - p_period_start_date)
173 ,1, paa.date_start
174 ,p_period_start_date
175 )
176 + 1
177 ) cnt
178 FROM hr_lookups hrl
179 ,ben_pl_f bp
180 ,per_absence_attendances paa
181 ,pqp_gap_absence_plans gap
182 WHERE hrl.lookup_type = p_lookup_type --'PQP_GAP_PLAN_TYPES_TO_EXTEND'
183 and (p_period_start_date between
184 NVL(hrl.start_date_active, p_period_start_date)
185 and NVL(hrl.end_date_active, p_period_end_date)
186 OR
187 p_period_end_date between
188 NVL(hrl.start_date_active, p_period_start_date)
189 and NVL(hrl.end_date_active, p_period_end_date)
190 )
191 and bp.pl_typ_id = hrl.lookup_code
192 and paa.business_group_id = p_business_group_id
193 and (p_period_start_date between
194 paa.date_start and paa.date_end
195 OR
196 p_period_end_date between
197 paa.date_start and paa.date_end
198 OR
199 paa.date_end between
200 p_period_start_date and p_period_end_date
201 )
202
203 --and (paa.date_start between
204 -- p_period_start_date and p_period_end_date
205 -- OR
206 -- paa.date_end between
207 -- p_period_start_date and p_period_end_date
208 -- )
209 and gap.pl_id = bp.pl_id
210 and gap.absence_attendance_id = paa.absence_attendance_id
211 and gap.assignment_id = p_assignment_id ;
212
213
214
215 CURSOR csr_get_wp ( p_assignment_id NUMBER
216 ,p_business_group_id NUMBER
217 ,p_effective_date DATE)
218 IS
219 SELECT work_pattern
220 FROM pqp_assignment_attributes_f paa
221 WHERE assignment_id = p_assignment_id
222 AND business_group_id = p_business_group_id
223 AND p_effective_date BETWEEN paa.effective_start_date
224 AND paa.effective_end_date ;
225
226 CURSOR csr_sum_level_entit_duration
227 (p_gap_absence_id IN NUMBER
228 ,p_level_of_entitlement IN VARCHAR
229 ,p_absence_date IN DATE
230 )
231 IS
232 SELECT SUM(gda.duration)
233 FROM pqp_gap_daily_absences gda
234 ,pqp_gap_absence_plans plans
235 WHERE gda.gap_absence_plan_id = plans.gap_absence_plan_id
236 AND plans.absence_attendance_id = p_gap_absence_id
237 AND gda.level_of_entitlement = p_level_of_entitlement
238 AND gda.absence_date <= p_absence_date ;
239
240 FUNCTION get_scheme_start_date
241 (p_assignment_id IN NUMBER
242 ,p_scheme_period_type IN VARCHAR2
243 ,p_scheme_period_duration IN VARCHAR2
244 ,p_scheme_period_uom IN VARCHAR2
245 ,p_fixed_year_start_date IN VARCHAR2
246 ,p_balance_effective_date IN DATE
247 ) RETURN DATE;
248
249 PROCEDURE get_plan_extra_info_n_cache_it
250 (p_pl_id IN NUMBER
251 ,p_plan_information IN OUT NOCOPY rec_plan_information
252 ,p_business_group_id IN NUMBER
253 ,p_assignment_id IN NUMBER
254 ,p_effective_date IN DATE
255 );
256
257 PROCEDURE get_param_value
258 (p_output_type IN ff_exec.outputs_t
259 ,p_name IN VARCHAR2
260 ,p_datatype OUT NOCOPY VARCHAR2
261 ,p_value OUT NOCOPY VARCHAR2
262 );
263
264 PROCEDURE get_absence_part_days
265 (p_absence_id IN NUMBER
266 ,p_part_start_day OUT NOCOPY NUMBER
267 ,p_part_end_day OUT NOCOPY NUMBER
268 ,p_part_day_UOM OUT NOCOPY VARCHAR2
269 );
270
271 FUNCTION get_adjusted_scheme_start_date
272 (p_assignment_id IN NUMBER
273 ,p_scheme_start_date IN DATE
274 ,p_pl_typ_id IN NUMBER
275 ,p_scheme_period_overlap_rule IN VARCHAR2
276 ) RETURN DATE;
277
278 PROCEDURE get_absences_taken_to_date
279 (p_assignment_id IN NUMBER
280 -- ,p_absence_date_start IN DATE
281 ,p_effective_date IN DATE
282 ,p_business_group_id IN NUMBER DEFAULT NULL
283 -- Added p_business_group_id for CS
284 ,p_pl_typ_id IN NUMBER
285 ,p_scheme_period_overlap_rule IN VARCHAR2
286 ,p_scheme_period_type IN VARCHAR2
287 ,p_scheme_period_duration IN VARCHAR2
288 ,p_scheme_period_uom IN VARCHAR2
289 ,p_scheme_period_start IN VARCHAR2
290 ,p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
291 ,p_absences_taken_to_date IN OUT NOCOPY pqp_absval_pkg.t_entitlements -- Added for CS
292 ,p_dualrolling_4_year IN BOOLEAN DEFAULT FALSE
293 ,p_override_scheme_start_date IN DATE DEFAULT NULL
294 ,p_plan_types_to_extend_period IN VARCHAR2 DEFAULT NULL --LG/PT
295 ,p_entitlement_uom IN VARCHAR2 DEFAULT NULL
296 ,p_default_wp IN VARCHAR2 DEFAULT NULL
300 );
297 ,p_absence_schedule_wp IN VARCHAR2 DEFAULT NULL
298 ,p_track_part_timers IN VARCHAR2 DEFAULT NULL
299 ,p_absence_start_date IN DATE
301
302 -- PROCEDURE get_entitlements_remaining
303 -- (p_entitlements IN pqp_absval_pkg.t_entitlements
304 -- ,p_absences_taken_to_date IN pqp_absval_pkg.t_entitlements
305 -- ,p_entitlement_UOM IN VARCHAR2
306 -- ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements
307 -- LG/PT
308 -- ,p_track_part_timers IN VARCHAR2 DEFAULT 'N'
309 -- );
310
311
312 PROCEDURE get_entitlements_remaining
313 (p_assignment_id IN NUMBER -- LG/PT
314 ,p_effective_date IN DATE -- LG/PT
315 ,p_entitlements IN pqp_absval_pkg.t_entitlements
316 ,p_absences_taken_to_date IN pqp_absval_pkg.t_entitlements
317 ,p_entitlement_UOM IN VARCHAR2
318 ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements--t_ent_run_balance
319 ,p_is_full_timer IN BOOLEAN DEFAULT NULL
320 -- ,p_avg_working_days_assignment IN NUMBER --LG/PT
321 -- ,p_avg_working_days_standard IN NUMBER -- LG/PT
322 -- ,p_message OUT NOCOPY VARCHAR2
323 -- LG/PT
324 -- ,p_track_part_timers IN VARCHAR2 DEFAULT 'N'
325 ) ;
326
327
328 PROCEDURE generate_daily_absences
329 (p_assignment_id IN NUMBER
330 ,p_business_group_id IN NUMBER
331 ,p_absence_attendance_id IN NUMBER
332 ,p_default_work_pattern_name IN VARCHAR2
333 ,p_calendar_user_table_id IN NUMBER
334 ,p_calendar_rules_list IN VARCHAR2
335 ,p_generate_start_date IN DATE
336 ,p_generate_end_date IN DATE
337 ,p_absence_start_date IN DATE
338 ,p_absence_end_date IN DATE
339 ,p_entitlement_UOM IN VARCHAR2
340 ,p_payment_UOM IN VARCHAR2
341 ,p_output_type IN ff_exec.outputs_t
342 ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements
343 ,p_daily_absences IN OUT NOCOPY pqp_absval_pkg.t_daily_absences
344 ,p_error_code OUT NOCOPY NUMBER
345 ,p_message OUT NOCOPY VARCHAR2
346 ,p_working_days_per_week IN NUMBER DEFAULT NULL
347 ,p_fte IN NUMBER DEFAULT 1 -- LG/PT
348 ,p_override_work_pattern IN VARCHAR2 DEFAULT NULL
349 ,p_pl_id IN NUMBER DEFAULT NULL
350 ,p_scheme_period_type IN VARCHAR2 DEFAULT NULL
351 ,p_is_assignment_wp IN BOOLEAN
352 );
353
354 PROCEDURE write_daily_absences
355 (p_daily_absences IN pqp_absval_pkg.t_daily_absences
356 ,p_gap_absence_plan_id IN pqp_gap_absence_plans.gap_absence_plan_id%TYPE
357 );
358
359 PROCEDURE create_absence_plan_details
360 (p_assignment_id IN NUMBER
361 ,p_person_id IN NUMBER
362 ,p_business_group_id IN NUMBER
363 ,p_absence_id IN NUMBER
364 ,p_absence_date_start IN DATE
365 ,p_absence_date_end IN DATE
366 ,p_pl_id IN NUMBER
367 ,p_pl_typ_id IN NUMBER
368 ,p_element_type_id IN NUMBER
369 ,p_create_start_date IN DATE
370 ,p_create_end_date IN DATE
371 ,p_output_type IN ff_exec.outputs_t
372 ,p_error_code OUT NOCOPY NUMBER
373 ,p_message OUT NOCOPY VARCHAR2
374 );
375
376 PROCEDURE delete_absence_plan_details
377 (p_assignment_id IN NUMBER
378 ,p_business_group_id IN NUMBER
379 ,p_plan_id IN NUMBER
380 ,p_absence_id IN NUMBER
381 ,p_delete_start_date IN DATE
382 ,p_delete_end_date IN DATE
383 ,p_error_code OUT NOCOPY NUMBER
384 ,p_message OUT NOCOPY VARCHAR2
385 );
386
387 PROCEDURE update_absence_plan_details
388 (p_assignment_id IN NUMBER
389 ,p_person_id IN NUMBER
390 ,p_business_group_id IN NUMBER
391 ,p_absence_id IN NUMBER
392 ,p_absence_date_start IN DATE
393 ,p_absence_date_end IN DATE
394 ,p_pl_id IN NUMBER
395 ,p_pl_typ_id IN NUMBER
396 ,p_element_type_id IN NUMBER
397 ,p_update_start_date IN DATE
398 ,p_update_end_date IN DATE
399 ,p_output_type IN ff_exec.outputs_t
400 ,p_error_code OUT NOCOPY NUMBER
401 ,p_message OUT NOCOPY VARCHAR2
402 );
403
404 PROCEDURE get_absences_taken
405 (p_assignment_id IN NUMBER
406 ,p_pl_typ_id IN NUMBER
407 ,p_range_from_date IN DATE --not absence start and end dates
408 ,p_range_to_date IN DATE --period for which sum is taken
409 ,p_absences_taken IN OUT NOCOPY pqp_absval_pkg.t_entitlements
410 -- ,p_message OUT NOCOPY VARCHAR2
411 ) ;
412
416 -- ,p_business_group_id IN NUMBER
413 ------------------ For LG/PT
414 -- FUNCTION get_entitlements
415 -- (p_assignment_id IN NUMBER
417 -- ,p_effective_date IN DATE
418 -- ,p_pl_id IN NUMBER
419 -- ,p_entitlement_table_id IN NUMBER
420 -- ,p_benefits_length_of_service IN NUMBER
421 -- ,p_band_entitlements OUT NOCOPY pqp_absval_pkg.t_entitlements
422 -- ,p_entitlement_bands_list_name IN VARCHAR2 DEFAULT
423 -- 'PQP_GAP_ENTITLEMENT_BANDS'
424 -- ) RETURN NUMBER ;
425
426 FUNCTION get_assignment_work_pattern (
427 p_business_group_id IN NUMBER
428 ,p_assignment_id IN NUMBER
429 ,p_effective_date IN DATE
430 ,p_default_wp IN VARCHAR2
431 ,p_contract_wp IN VARCHAR2
432 ,p_is_assignment_wp OUT NOCOPY BOOLEAN)
433 RETURN VARCHAR2 ;
434
435 FUNCTION get_average_days_per_week(
436 p_business_group_id IN NUMBER
437 ,p_effective_date IN DATE
438 ,p_work_pattern IN VARCHAR2 )
439 RETURN NUMBER ;
440
441 FUNCTION get_absence_standard_ft_wp(
442 p_business_group_id IN NUMBER
443 ,p_assignment_id IN NUMBER
444 ,p_effective_date IN DATE
445 ,p_absence_schedule_wp IN VARCHAR2
446 ,p_default_wp IN VARCHAR2
447 ,p_entitlement_uom IN VARCHAR2
448 ,p_contract_wp OUT NOCOPY VARCHAR2 )
449 RETURN VARCHAR2 ;
450
451 FUNCTION get_contract_level_wp (
452 p_business_group_id IN NUMBER
453 ,p_assignment_id IN NUMBER
454 ,p_effective_date IN DATE )
455 RETURN VARCHAR2 ;
456
457 FUNCTION get_calendar_days_to_extend(
458 p_period_start_date IN DATE
459 ,p_period_end_date IN DATE
460 ,p_assignment_id IN NUMBER
461 ,p_business_group_id IN NUMBER
462 ,p_pl_typ_id IN NUMBER
463 ,p_count_nopay_days IN BOOLEAN
464 ,p_plan_types_lookup_type IN VARCHAR2
465 )
466 RETURN NUMBER ;
467
468
469 PROCEDURE get_factors (
470 p_business_group_id IN NUMBER
471 ,p_effective_date IN DATE
472 ,p_assignment_id IN NUMBER
473 ,p_entitlement_uom IN VARCHAR2
474 ,p_default_wp IN VARCHAR2
475 ,p_absence_schedule_wp IN VARCHAR2
476 ,p_track_part_timers IN VARCHAR2
477 ,p_current_factor OUT NOCOPY NUMBER
478 ,p_ft_factor OUT NOCOPY NUMBER
479 ,p_working_days_per_week OUT NOCOPY NUMBER
480 ,p_fte OUT NOCOPY NUMBER
481 ,p_FT_absence_wp OUT NOCOPY VARCHAR2
482 ,p_FT_working_wp OUT NOCOPY VARCHAR2
483 ,p_assignment_wp OUT NOCOPY VARCHAR2
484 ,p_is_full_timer OUT NOCOPY BOOLEAN
485 ,p_is_assignment_wp OUT NOCOPY BOOLEAN
486 ) ;
487
488 PROCEDURE convert_entitlements
489 ( p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
490 ,p_current_factor IN NUMBER
491 ,p_ft_factor IN NUMBER
492 ) ;
493
494
495
496
497 PROCEDURE write_absence_summary
498 (p_gap_absence_plan_id IN NUMBER
499 ,p_assignment_id IN NUMBER
500 ,p_entitlement_granted IN pqp_absval_pkg.t_entitlements
501 ,p_entitlement_used_to_date IN pqp_absval_pkg.t_entitlements
502 ,p_entitlement_remaining IN pqp_absval_pkg.t_entitlements
503 ,p_fte IN NUMBER DEFAULT 1
504 ,p_working_days_per_week IN NUMBER DEFAULT NULL
505 ,p_entitlement_uom IN VARCHAR2
506 ,p_update IN BOOLEAN
507 );
508
509 ------------------
510
511
512 END pqp_absval_pkg;
[Home] [Help]