1 PACKAGE BODY pqp_gb_gap_ben_formulas AS
2 -- $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
3 --
4 --
5 --
6 PROCEDURE debug
7 (p_trace_message IN VARCHAR2
8 ,p_trace_location IN NUMBER DEFAULT NULL
9 )
10 IS
11 l_padding VARCHAR2(12);
12 l_MAX_MESSAGE_LENGTH NUMBER:= 72;
13 BEGIN
14
15 IF p_trace_location IS NOT NULL THEN
16
17 l_padding := SUBSTR
18 (RPAD(' ',LEAST(g_nested_level,5)*2,' ')
19 ,1,l_MAX_MESSAGE_LENGTH
20 - LEAST(LENGTH(p_trace_message)
21 ,l_MAX_MESSAGE_LENGTH)
22 );
23
24 hr_utility.set_location
25 (l_padding||
26 SUBSTR(p_trace_message
27 ,GREATEST(-LENGTH(p_trace_message),-l_MAX_MESSAGE_LENGTH))
28 ,p_trace_location);
29
30 ELSE
31
32 hr_utility.trace(SUBSTR(p_trace_message,1,250));
33
34 END IF;
35
36 END debug;
37 --
38 --
39 --
40 PROCEDURE debug
41 (p_trace_number IN NUMBER )
42 IS
43 BEGIN
44 debug(fnd_number.number_to_canonical(p_trace_number));
45 END debug;
46 --
47 --
48 --
49 PROCEDURE debug
50 (p_trace_date IN DATE )
51 IS
52 BEGIN
53 debug(fnd_date.date_to_canonical(p_trace_date));
54 END debug;
55 --
56 --
57 --
58 PROCEDURE debug_enter
59 (p_proc_name IN VARCHAR2 DEFAULT NULL
60 ,p_trace_on IN VARCHAR2 DEFAULT NULL
61 )
62 IS
63 -- l_trace_options VARCHAR2(200);
64 BEGIN
65
66 -- --Uncomment this code to run the package with a debug trace
67 --
68 -- IF g_nested_level = 0 -- swtich tracing on/off at the top level only
69 -- AND NVL(p_trace_on,'N') = 'Y'
70 -- THEN
71 --
72 -- hr_utility.trace_on(NULL,'REQID'); -- Pipe name REQIDnnnnnn
73 --
74 -- END IF; -- if nested level = 0
75 --
76 -- --Uncomment this code to run the package with a debug trace
77
78 g_nested_level := g_nested_level + 1;
79 debug('Entered: '||NVL(p_proc_name,g_proc_name),g_nested_level*100);
80
81 END debug_enter;
82 --
83 --
84 --
85 PROCEDURE debug_exit
86 (p_proc_name IN VARCHAR2 DEFAULT NULL
87 ,p_trace_off IN VARCHAR2 DEFAULT NULL
88 ,p_override_trace_location IN NUMBER DEFAULT NULL
89 )
90 IS
91 BEGIN
92
93 debug
94 ('Leaving: '||NVL(p_proc_name,g_proc_name)
95 ,NVL(p_override_trace_location,-g_nested_level*100)
96 );
97
98 g_nested_level := g_nested_level - 1;
99
100 -- --Uncomment this code to run the package with a debug trace
101 --
102 -- IF g_nested_level = 0
103 -- AND NVL(p_trace_off,'Y') = 'Y'
104 -- THEN
105 --
106 -- hr_utility.trace_off;
107 --
108 -- END IF;
109 --
110 -- --Uncomment this code to run the package with a debug trace
111
112 END debug_exit;
113 --
114 --
115 --
116 PROCEDURE create_ben_formulas
117 (p_business_group_id IN NUMBER
118 ,p_effective_date IN DATE
119 ,p_absence_pay_plan_category IN VARCHAR2
120 ,p_base_name IN VARCHAR2
121 ,p_formulas IN OUT NOCOPY pqp_gb_gap_ben_formulas.t_formulas
122 ,p_error_code OUT NOCOPY NUMBER
123 ,p_error_message OUT NOCOPY VARCHAR2
124 )
125 IS
126
127 l_formula_name ff_formulas.formula_name%TYPE;-- VARCHAR2(80)
128 l_formula_type ff_formula_types.formula_type_name%TYPE;-- VARCHAR2(80)
129 l_description ff_formulas.description%TYPE;-- VARCHAR2(240)
130 l_formula_type_id ff_formula_types.formula_type_id%TYPE;
131
132 -- this date is passed as effective start date for LER formulas only.
133 -- In the remaining formulas p_effective_date is passed
134 l_effective_start_date DATE:= fnd_date.canonical_to_date('1951/01/01 00:00:00');
135 --p_effective_date;
136 l_effective_end_date DATE:=
137 fnd_date.canonical_to_date('4712/12/31 00:00:00');
138
139 l_text VARCHAR2(32767);
140
141 l_business_group_name hr_all_organization_units.name%TYPE;--VARCHAR2(240)
142 l_business_group_id ff_formulas.business_group_id%TYPE :=
143 p_business_group_id ;
144 l_legislation_code ff_formulas.legislation_code%TYPE:= NULL;
145 l_formula_count NUMBER:= 1;
146
147 l_base_name pay_element_templates.base_name%TYPE:=
148 TRIM(UPPER(p_base_name));
149
150 l_proc_name VARCHAR2(61):= g_proc_name||'create_ben_formulas';
151
152 BEGIN
153 debug_enter(l_proc_name);
154 debug(l_proc_name,10);
155 p_error_code:= 0;
156 p_error_message := NULL;
157
158 IF g_use_this_functionality THEN
159
160 --
161 -- Start of Person Change Causes Life Event rule for the Absence Delete LER
162 --
163 BEGIN
164
165
166
167 debug(l_proc_name,20);
168 l_formula_name:= -- VARCHAR2(80)
169 SUBSTRB
170 (-- 1 2 3 4 5
171 --1234567890123456789012345678901234567890123456789012345
172 'PQP_GB_BEN_LER_ABSENCE_DELETE_EVENT_PERSON_CHANGES_RULE'
173 --||l_uniquestamp
174 ,1,80);
175
176 debug(l_formula_name);
177
178 OPEN csr_get_formula_id(p_business_group_id,l_formula_name);
179 FETCH csr_get_formula_id INTO l_check_ler_formulas;
180 IF csr_get_formula_id%NOTFOUND THEN
181
182 debug(l_proc_name,22);
183 l_formula_type:= -- VARCHAR2(80)
184 'Person Change Causes Life Event';
185 l_description:= -- VARCHAR2(240)
186 'Sample rule to detect a new or changed absence entry.';
187
188 debug(l_proc_name,25);
189 l_text:='
190 /*==============================================================================
191 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
192 Formula Name: PQP_GB_BEN_LER_ABSENCE_DELETE_EVENT_PERSON_CHANGES_RULE
193 Formula Type: Person Change Causes Life Event
194 Description : Sample formula to detect that an absence has been deleted.
195
196 DISCLAIMER :
197 In future releases of HRMS programs, Oracle Corporation may change or
198 upgrade this formula, and all other definitions for the predefined template
199 of which this formula is a part. We do not guarantee that the formula and the
200 predefined template will provide a ready-made solution to be used in your
201 environment. If the formula does not reflect your business rules, you are
202 responsible for writing a formula of your own to meet your particular
203 requirements. Any use of this formula and the predefined extract is subject to
204 the terms of the Oracle license agreement for the HRMS programs and
205 documentation.
206
207 **Change List
208 ===========
209 Name Date Version Bug Text
210 ============== =========== ======= ======= ===================================
211 rrazdan 29-JUL-2002 110.0 Created.
212 ==============================================================================*/
213 /*
214 Set default values.
215 */
216 DEFAULT FOR BEN_ABA_IN_DATE_START IS ''_DEFAULT_''
217 DEFAULT FOR BEN_ABA_IO_DATE_START IS ''_DEFAULT_''
218 DEFAULT FOR BEN_ABA_IN_DATE_END IS ''_DEFAULT_''
219 DEFAULT FOR BEN_ABA_IO_DATE_END IS ''_DEFAULT_''
220 DEFAULT FOR BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
221 DEFAULT FOR BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
222 /* Other inputs available are
223 DEFAULT FOR BEN_ABA_IN_PERSON_ID IS ''_DEFAULT_''
224 DEFAULT FOR BEN_ABA_IO_PERSON_ID IS ''_DEFAULT_''
225 DEFAULT FOR BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
226 DEFAULT FOR BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
227 DEFAULT FOR BEN_ABA_IN_SICKNESS_START_DATE IS ''_DEFAULT_''
228 DEFAULT FOR BEN_ABA_IO_SICKNESS_START_DATE IS ''_DEFAULT_''
229 DEFAULT FOR BEN_ABA_IN_SICKNESS_END_DATE IS ''_DEFAULT_''
230 DEFAULT FOR BEN_ABA_IO_SICKNESS_END_DATE IS ''_DEFAULT_''
231 DEFAULT FOR BEN_ABA_IN_ABSENCE_DAYS IS ''_DEFAULT_''
232 DEFAULT FOR BEN_ABA_IO_ABSENCE_DAYS IS ''_DEFAULT_''
233 DEFAULT FOR BEN_ABA_IN_ABSENCE_HOURS IS ''_DEFAULT_''
234 DEFAULT FOR BEN_ABA_IO_ABSENCE_HOURS IS ''_DEFAULT_''
235 DEFAULT FOR BEN_ABA_IN_DATE_NOTIFICATION IS ''_DEFAULT_''
236 DEFAULT FOR BEN_ABA_IO_DATE_NOTIFICATION IS ''_DEFAULT_''
237 DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_END IS ''_DEFAULT_''
238 DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_END IS ''_DEFAULT_''
239 DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_START IS ''_DEFAULT_''
240 DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_START IS ''_DEFAULT_''
241 DEFAULT FOR BEN_ABA_IN_TIME_END IS ''_DEFAULT_''
242 DEFAULT FOR BEN_ABA_IO_TIME_END IS ''_DEFAULT_''
243 DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_END IS ''_DEFAULT_''
244 DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_END IS ''_DEFAULT_''
245 DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_START IS ''_DEFAULT_''
246 DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_START IS ''_DEFAULT_''
247 */
248
249 /*
250 Declare Input values.
251
252 NOTE the naming convention followed
253 BEN_ABA_IN_<Column Name> - New Values
254 BEN_ABA_IO_<Column Name> - Old Values
255 */
256
257 INPUTS ARE BEN_ABA_IN_DATE_START(TEXT)
258 ,BEN_ABA_IO_DATE_START(TEXT)
259 ,BEN_ABA_IN_DATE_END(TEXT)
260 ,BEN_ABA_IO_DATE_END(TEXT)
261 ,BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
262 ,BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
263 /* Other inputs available are
264 ,BEN_ABA_IN_PERSON_ID(TEXT)
265 ,BEN_ABA_IO_PERSON_ID(TEXT)
266 ,BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID(TEXT)
267 ,BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID(TEXT)
268 ,BEN_ABA_IN_SICKNESS_START_DATE(TEXT)
269 ,BEN_ABA_IO_SICKNESS_START_DATE(TEXT)
270 ,BEN_ABA_IN_SICKNESS_END_DATE(TEXT)
271 ,BEN_ABA_IO_SICKNESS_END_DATE(TEXT)
272 ,BEN_ABA_IN_ABSENCE_DAYS(TEXT)
273 ,BEN_ABA_IO_ABSENCE_DAYS(TEXT)
274 ,BEN_ABA_IN_ABSENCE_HOURS(TEXT)
275 ,BEN_ABA_IO_ABSENCE_HOURS(TEXT)
276 ,BEN_ABA_IN_DATE_NOTIFICATION(TEXT)
277 ,BEN_ABA_IO_DATE_NOTIFICATION(TEXT)
278 ,BEN_ABA_IN_DATE_PROJECTED_END(TEXT)
279 ,BEN_ABA_IO_DATE_PROJECTED_END(TEXT)
280 ,BEN_ABA_IN_DATE_PROJECTED_START(TEXT)
281 ,BEN_ABA_IO_DATE_PROJECTED_START(TEXT)
282 ,BEN_ABA_IN_TIME_END(TEXT)
283 ,BEN_ABA_IO_TIME_END(TEXT)
284 ,BEN_ABA_IN_TIME_PROJECTED_END(TEXT)
285 ,BEN_ABA_IO_TIME_PROJECTED_END(TEXT)
286 ,BEN_ABA_IN_TIME_PROJECTED_START(TEXT)
287 ,BEN_ABA_IO_TIME_PROJECTED_START(TEXT)
288 */
289
290 /*
291 Initialise values.
292 */
293
294 l_default = ''_DEFAULT_'' /* TEXT */
295 l_yn = ''N'' /* TEXT */
296
297
298 /* 01. Determine the old and new values for ABSENCE_ATTENDANCE_TYPE_ID
299
300 NOTE Though these values are stored as dates or numbers they are made
301 available to the formula as TEXT inputs.
302
303 The dates are available in the default canoncial format of
304 "YYYY/MM/DD HH24:MI:SS".
305 Where required, use TO_DATE to convert text into a date.
306
307 The numbers are available in the default canonical format.
308 Where required, use TO_NUMBER to convert into text into a number.
309 */
310
311 l_absence_type_id_old_value = BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID
312 l_absence_type_id_new_value = BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID
313
314 /* 02a. Check that the new value of absence type id has been defaulted.
315
316 NOTE When an absence is deleted, the absence_type_id new value will be
317 defaulted. To record an absence delete a seperate life event reason has
318 to be setup. To prevent a absence start change from being logged on a
319 delete, an additional check is introduced to ensure that new value is not
320 equal to the default.
321 */
322 IF NOT l_absence_type_id_old_value = l_default
323 AND
324 l_absence_type_id_new_value = l_default
325 THEN
326 (
327 /* 02b. Set the return flag to "Y"es to allow this person change to cause an
328 "absence delete" life event.
329
330 NOTE the default for the flag is "N"o.
331 */
332 l_yn = ''Y''
333 )
334
335 RETURN l_yn
336 ';
337
338 debug(l_proc_name,27);
339 SELECT formula_type_id
340 INTO l_formula_type_id
341 FROM ff_formula_types
342 WHERE formula_type_name = l_formula_type;
343
344 debug(l_proc_name,29);
345 debug(l_effective_start_date||';'||l_effective_end_date,30);
346 debug(l_business_group_id||';'||l_legislation_code,31);
350 INSERT INTO ff_formulas_f
347 debug(l_formula_type_id||';'||l_formula_name,31);
348
349 l_formula_count:= l_formula_count + 1;
351 (formula_id
352 ,effective_start_date
353 ,effective_end_date
354 ,business_group_id
355 ,legislation_code
356 ,formula_type_id
357 ,formula_name
358 ,description
359 ,formula_text
360 ,sticky_flag)
361 VALUES(ff_formulas_s.NEXTVAL
362 ,l_effective_start_date
363 ,l_effective_end_date
364 ,l_business_group_id
365 ,l_legislation_code
366 ,l_formula_type_id
367 ,l_formula_name
368 ,l_description
369 ,NULL
370 ,NULL
371 )
372 -- WHERE NOT EXISTS
373 -- (SELECT 1
374 -- FROM ff_formulas_f
375 -- WHERE formula_name = l_formula_name
376 -- AND business_group_id = l_business_group_id
377 -- )
378 RETURNING formula_id INTO p_formulas(l_formula_count);
379
380 UPDATE ff_formulas_f
381 SET formula_text = l_text
382 WHERE formula_id = p_formulas(l_formula_count)
383 -- formula_name = l_formula_name
384 -- AND business_group_id = l_business_group_id
385 ;
386
387
388 END IF;
389 CLOSE csr_get_formula_id;
390
391 END;
392 --
393 -- End of Person Change Causes Life Event rule for the Absence Delete LER
394 --
395 --
396 -- Start of Person Change Causes Life Event rule for the Absence End LER
397 --
398 BEGIN
399
400 debug(l_proc_name,30);
401 l_formula_name:= -- VARCHAR2(80)
402 SUBSTRB
403 (-- 1 2 3 4 5
404 --1234567890123456789012345678901234567890123456789012
405 'PQP_GB_BEN_LER_ABSENCE_END_EVENT_PERSON_CHANGES_RULE'
406 --||l_uniquestamp
407 ,1,80);
408 debug(l_formula_name);
409 OPEN csr_get_formula_id(p_business_group_id,l_formula_name);
410 FETCH csr_get_formula_id INTO l_check_ler_formulas;
411 IF csr_get_formula_id%NOTFOUND THEN
412 debug(l_proc_name,32);
413 l_formula_type:= -- VARCHAR2(80)
414 'Person Change Causes Life Event';
415 l_description:= -- VARCHAR2(240)
416 'Sample rule to detect a new or changed absence entry.';
417
418 debug(l_proc_name,35);
419 l_text:='
420 /*==============================================================================
421 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
422 Formula Name: PQP_GB_BEN_LER_ABSENCE_END_EVENT_PERSON_CHANGES_RULE
423 Formula Type: Person Change Causes Life Event
424 Description : Sample formula to detect a new or changed absence end date.
425
426 DISCLAIMER :
427 In future releases of HRMS programs, Oracle Corporation may change or
428 upgrade this formula, and all other definitions for the predefined template
429 of which this formula is a part. We do not guarantee that the formula and the
430 predefined template will provide a ready-made solution to be used in your
431 environment. If the formula does not reflect your business rules, you are
432 responsible for writing a formula of your own to meet your particular
433 requirements. Any use of this formula and the predefined extract is subject to
434 the terms of the Oracle license agreement for the HRMS programs and
435 documentation.
436
437 **Change List
438 ===========
439 Name Date Version Bug Text
440 ============== =========== ======= ======= ===================================
441 rrazdan 29-JUL-2002 110.0 Created.
442 ==============================================================================*/
443 /*
444 Set default values.
445 */
446 DEFAULT FOR BEN_ABA_IN_DATE_START IS ''_DEFAULT_''
447 DEFAULT FOR BEN_ABA_IO_DATE_START IS ''_DEFAULT_''
448 DEFAULT FOR BEN_ABA_IN_DATE_END IS ''_DEFAULT_''
449 DEFAULT FOR BEN_ABA_IO_DATE_END IS ''_DEFAULT_''
450 DEFAULT FOR BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
451 DEFAULT FOR BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
452 /* Other inputs available are
453 DEFAULT FOR BEN_ABA_IN_PERSON_ID IS ''_DEFAULT_''
454 DEFAULT FOR BEN_ABA_IO_PERSON_ID IS ''_DEFAULT_''
455 DEFAULT FOR BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
456 DEFAULT FOR BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
457 DEFAULT FOR BEN_ABA_IN_SICKNESS_START_DATE IS ''_DEFAULT_''
458 DEFAULT FOR BEN_ABA_IO_SICKNESS_START_DATE IS ''_DEFAULT_''
459 DEFAULT FOR BEN_ABA_IN_SICKNESS_END_DATE IS ''_DEFAULT_''
460 DEFAULT FOR BEN_ABA_IO_SICKNESS_END_DATE IS ''_DEFAULT_''
461 DEFAULT FOR BEN_ABA_IN_ABSENCE_DAYS IS ''_DEFAULT_''
462 DEFAULT FOR BEN_ABA_IO_ABSENCE_DAYS IS ''_DEFAULT_''
463 DEFAULT FOR BEN_ABA_IN_ABSENCE_HOURS IS ''_DEFAULT_''
464 DEFAULT FOR BEN_ABA_IO_ABSENCE_HOURS IS ''_DEFAULT_''
465 DEFAULT FOR BEN_ABA_IN_DATE_NOTIFICATION IS ''_DEFAULT_''
466 DEFAULT FOR BEN_ABA_IO_DATE_NOTIFICATION IS ''_DEFAULT_''
467 DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_END IS ''_DEFAULT_''
468 DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_END IS ''_DEFAULT_''
469 DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_START IS ''_DEFAULT_''
470 DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_START IS ''_DEFAULT_''
471 DEFAULT FOR BEN_ABA_IN_TIME_END IS ''_DEFAULT_''
475 DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_START IS ''_DEFAULT_''
472 DEFAULT FOR BEN_ABA_IO_TIME_END IS ''_DEFAULT_''
473 DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_END IS ''_DEFAULT_''
474 DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_END IS ''_DEFAULT_''
476 DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_START IS ''_DEFAULT_''
477 */
478
479 /*
480 Declare Input values.
481
482 NOTE the naming convention followed
483 BEN_ABA_IN_<Column Name> - New Values
484 BEN_ABA_IO_<Column Name> - Old Values
485 */
486
487 INPUTS ARE BEN_ABA_IN_DATE_START(TEXT)
488 ,BEN_ABA_IO_DATE_START(TEXT)
489 ,BEN_ABA_IN_DATE_END(TEXT)
490 ,BEN_ABA_IO_DATE_END(TEXT)
491 ,BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
492 ,BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
493 /* Other inputs available are
494 ,BEN_ABA_IN_PERSON_ID(TEXT)
495 ,BEN_ABA_IO_PERSON_ID(TEXT)
496 ,BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID(TEXT)
497 ,BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID(TEXT)
498 ,BEN_ABA_IN_SICKNESS_START_DATE(TEXT)
499 ,BEN_ABA_IO_SICKNESS_START_DATE(TEXT)
500 ,BEN_ABA_IN_SICKNESS_END_DATE(TEXT)
501 ,BEN_ABA_IO_SICKNESS_END_DATE(TEXT)
502 ,BEN_ABA_IN_ABSENCE_DAYS(TEXT)
503 ,BEN_ABA_IO_ABSENCE_DAYS(TEXT)
504 ,BEN_ABA_IN_ABSENCE_HOURS(TEXT)
505 ,BEN_ABA_IO_ABSENCE_HOURS(TEXT)
506 ,BEN_ABA_IN_DATE_NOTIFICATION(TEXT)
507 ,BEN_ABA_IO_DATE_NOTIFICATION(TEXT)
508 ,BEN_ABA_IN_DATE_PROJECTED_END(TEXT)
509 ,BEN_ABA_IO_DATE_PROJECTED_END(TEXT)
510 ,BEN_ABA_IN_DATE_PROJECTED_START(TEXT)
511 ,BEN_ABA_IO_DATE_PROJECTED_START(TEXT)
512 ,BEN_ABA_IN_TIME_END(TEXT)
513 ,BEN_ABA_IO_TIME_END(TEXT)
514 ,BEN_ABA_IN_TIME_PROJECTED_END(TEXT)
515 ,BEN_ABA_IO_TIME_PROJECTED_END(TEXT)
516 ,BEN_ABA_IN_TIME_PROJECTED_START(TEXT)
517 ,BEN_ABA_IO_TIME_PROJECTED_START(TEXT)
518 */
519
520 /*
521 Initialise values.
522 */
523
524 l_default = ''_DEFAULT_'' /* TEXT */
525 l_yn = ''N'' /* TEXT */
526
527
528 /* 01. Determine the old and new values for DATE_START
529
530 NOTE Though these values are stored as dates or numbers they are made
531 available to the formula as TEXT inputs.
532
533 The dates are available in the default canoncial format of
534 "YYYY/MM/DD HH24:MI:SS".
535 Where required, use TO_DATE to convert text into a date.
536
537 The numbers are available in the default canonical format.
538 Where required, use TO_NUMBER to convert into text into a number.
539 */
540
541 l_date_end_old_value = BEN_ABA_IO_DATE_END
542 l_date_end_new_value = BEN_ABA_IN_DATE_END
543 l_absence_type_id_new_value = BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID
544
545 /* 02a. Check that there is a difference between the old and new values of
546 DATE_END.
547
548 NOTE When an absence is deleted, the absence_type_id new value will be
549 defaulted. To record an absence delete a seperate life event reason has
550 to be setup. To prevent a absence start change from being logged on a
551 delete, an additional check is introduced to ensure that new value is not
552 equal to the default.
553 */
554 IF NOT l_date_end_old_value = l_date_end_new_value
555 AND
556 NOT l_absence_type_id_new_value = l_default
557 THEN
558 (
559 /* 02b. Set the return flag to "Y"es to allow this person change to cause an
560 "absence end" life event.
561
562 NOTE the default for the flag is "N"o.
563 */
564 l_yn = ''Y''
565 )
566
567 RETURN l_yn
568 ';
569
570 debug(l_proc_name,37);
571 SELECT formula_type_id
572 INTO l_formula_type_id
573 FROM ff_formula_types
574 WHERE formula_type_name = l_formula_type;
575
576 debug(l_proc_name,39);
577 l_formula_count := l_formula_count + 1;
578 INSERT INTO ff_formulas_f
579 (formula_id
580 ,effective_start_date
581 ,effective_end_date
582 ,business_group_id
583 ,legislation_code
584 ,formula_type_id
585 ,formula_name
586 ,description
587 ,formula_text
588 ,sticky_flag)
589 VALUES(ff_formulas_s.NEXTVAL
590 ,l_effective_start_date
591 ,l_effective_end_date
592 ,l_business_group_id
593 ,l_legislation_code
594 ,l_formula_type_id
595 ,l_formula_name
596 ,l_description
597 ,NULL
598 ,NULL
599 )
600 -- WHERE NOT EXISTS
601 -- (SELECT 1
602 -- FROM ff_formulas_f
603 -- WHERE formula_name = l_formula_name
604 -- AND business_group_id = l_business_group_id
605 -- )
606 RETURNING formula_id INTO p_formulas(l_formula_count);
607
608 UPDATE ff_formulas_f
609 SET formula_text = l_text
610 WHERE formula_id = p_formulas(l_formula_count);
611
612 END IF;
613 CLOSE csr_get_formula_id;
614
615 END;
616 --
620 -- Start of Person Change Causes Life Event rule for the Absence Start LER
617 -- End of Person Change Causes Life Event rule for the Absence End LER
618 --
619 --
621 --
622 BEGIN
623 debug(l_proc_name,40);
624 l_formula_name:= -- VARCHAR2(80)
625 SUBSTRB
626 (-- 1 2 3 4 5 6 7
627 --1234567890123456789012345678901234567890123456789012345678901234567890
628 'PQP_GB_BEN_LER_ABSENCE_START_EVENT_PERSON_CHANGES_RULE'
629 --||l_uniquestamp
630 ,1,80);
631
632 OPEN csr_get_formula_id(p_business_group_id,l_formula_name);
633 FETCH csr_get_formula_id INTO l_check_ler_formulas;
634 IF csr_get_formula_id%NOTFOUND THEN
635 debug(l_proc_name,42);
636 l_formula_type:= -- VARCHAR2(80)
637 'Person Change Causes Life Event';
638 l_description:= -- VARCHAR2(240)
639 'Sample rule to detect a new or changed absence entry.';
640 debug(l_proc_name,45);
641 l_text:='
642 /*==============================================================================
643 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
644 Formula Name: PQP_GB_BEN_LER_ABSENCE_START_EVENT_PERSON_CHANGES_RULE
645 Formula Type: Person Change Causes Life Event
646 Description : Sample formula to detect a new or changed absence entry.
647
648 DISCLAIMER :
649 In future releases of HRMS programs, Oracle Corporation may change or
650 upgrade this formula, and all other definitions for the predefined template
651 of which this formula is a part. We do not guarantee that the formula and the
652 predefined template will provide a ready-made solution to be used in your
653 environment. If the formula does not reflect your business rules, you are
654 responsible for writing a formula of your own to meet your particular
655 requirements. Any use of this formula and the predefined extract is subject to
656 the terms of the Oracle license agreement for the HRMS programs and
657 documentation.
658
659 **Change List
660 ===========
661 Name Date Version Bug Text
662 ============== =========== ======= ======= ===================================
663 rrazdan 29-JUL-2002 110.0 Created.
664 ==============================================================================*/
665 /*
666 Set default values.
667 */
668 DEFAULT FOR BEN_ABA_IN_DATE_START IS ''_DEFAULT_''
669 DEFAULT FOR BEN_ABA_IO_DATE_START IS ''_DEFAULT_''
670 DEFAULT FOR BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
671 DEFAULT FOR BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
672 DEFAULT FOR BEN_ABA_IO_ABS_INFORMATION1 IS ''_DEFAULT_''
673 DEFAULT FOR BEN_ABA_IN_ABS_INFORMATION1 IS ''_DEFAULT_''
674 DEFAULT FOR BEN_ABA_IO_ABS_INFORMATION2 IS ''_DEFAULT_''
675 DEFAULT FOR BEN_ABA_IN_ABS_INFORMATION2 IS ''_DEFAULT_''
676
677 /* Other inputs available are
678 DEFAULT FOR BEN_ABA_IN_PERSON_ID IS ''_DEFAULT_''
679 DEFAULT FOR BEN_ABA_IO_PERSON_ID IS ''_DEFAULT_''
680 DEFAULT FOR BEN_ABA_IN_DATE_END IS ''_DEFAULT_''
681 DEFAULT FOR BEN_ABA_IO_DATE_END IS ''_DEFAULT_''
682 DEFAULT FOR BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
683 DEFAULT FOR BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
684 DEFAULT FOR BEN_ABA_IN_SICKNESS_START_DATE IS ''_DEFAULT_''
685 DEFAULT FOR BEN_ABA_IO_SICKNESS_START_DATE IS ''_DEFAULT_''
686 DEFAULT FOR BEN_ABA_IN_SICKNESS_END_DATE IS ''_DEFAULT_''
687 DEFAULT FOR BEN_ABA_IO_SICKNESS_END_DATE IS ''_DEFAULT_''
688 DEFAULT FOR BEN_ABA_IN_ABSENCE_DAYS IS ''_DEFAULT_''
689 DEFAULT FOR BEN_ABA_IO_ABSENCE_DAYS IS ''_DEFAULT_''
690 DEFAULT FOR BEN_ABA_IN_ABSENCE_HOURS IS ''_DEFAULT_''
691 DEFAULT FOR BEN_ABA_IO_ABSENCE_HOURS IS ''_DEFAULT_''
692 DEFAULT FOR BEN_ABA_IN_DATE_NOTIFICATION IS ''_DEFAULT_''
693 DEFAULT FOR BEN_ABA_IO_DATE_NOTIFICATION IS ''_DEFAULT_''
694 DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_END IS ''_DEFAULT_''
695 DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_END IS ''_DEFAULT_''
696 DEFAULT FOR BEN_ABA_IN_DATE_PROJECTED_START IS ''_DEFAULT_''
697 DEFAULT FOR BEN_ABA_IO_DATE_PROJECTED_START IS ''_DEFAULT_''
698 DEFAULT FOR BEN_ABA_IN_TIME_END IS ''_DEFAULT_''
699 DEFAULT FOR BEN_ABA_IO_TIME_END IS ''_DEFAULT_''
700 DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_END IS ''_DEFAULT_''
701 DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_END IS ''_DEFAULT_''
702 DEFAULT FOR BEN_ABA_IN_TIME_PROJECTED_START IS ''_DEFAULT_''
703 DEFAULT FOR BEN_ABA_IO_TIME_PROJECTED_START IS ''_DEFAULT_''
704 DEFAULT FOR BEN_ABA_IN_ABS_INFORMATION1 IS ''_DEFAULT_''
705 DEFAULT FOR BEN_ABA_IN_ABS_INFORMATION2 IS ''_DEFAULT_''
706 ...
707 DEFAULT FOR BEN_ABA_IN_ABS_INFORMATION30 IS ''_DEFAULT_''
708 DEFAULT FOR BEN_ABA_IO_ABS_INFORMATION1 IS ''_DEFAULT_''
709 DEFAULT FOR BEN_ABA_IO_ABS_INFORMATION2 IS ''_DEFAULT_''
710 ...
711 DEFAULT FOR BEN_ABA_IO_ABS_INFORMATION30 IS ''_DEFAULT_''
712 DEFAULT FOR BEN_ABA_IN_ATTRIBUTE1 IS ''_DEFAULT_''
713 DEFAULT FOR BEN_ABA_IN_ATTRIBUTE2 IS ''_DEFAULT_''
714 ...
715 DEFAULT FOR BEN_ABA_IN_ATTRIBUTE20 IS ''_DEFAULT_''
716 DEFAULT FOR BEN_ABA_IO_ATTRIBUTE1 IS ''_DEFAULT_''
717 DEFAULT FOR BEN_ABA_IO_ATTRIBUTE2 IS ''_DEFAULT_''
718 ...
719 DEFAULT FOR BEN_ABA_IO_ATTRIBUTE20 IS ''_DEFAULT_''
720 */
721
722 /*
726 BEN_ABA_IN_<Column Name> - New Values
723 Declare Input values.
724
725 NOTE the naming convention followed
727 BEN_ABA_IO_<Column Name> - Old Values
728 */
729
730 INPUTS ARE BEN_ABA_IN_DATE_START(TEXT)
731 ,BEN_ABA_IO_DATE_START(TEXT)
732 ,BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
733 ,BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
734 ,BEN_ABA_IO_ABS_INFORMATION1(TEXT)
735 ,BEN_ABA_IN_ABS_INFORMATION1(TEXT)
736 ,BEN_ABA_IO_ABS_INFORMATION2(TEXT)
737 ,BEN_ABA_IN_ABS_INFORMATION2(TEXT)
738 /* Other inputs available are
739 ,BEN_ABA_IN_PERSON_ID(TEXT)
740 ,BEN_ABA_IO_PERSON_ID(TEXT)
741 ,BEN_ABA_IN_DATE_END(TEXT)
742 ,BEN_ABA_IO_DATE_END(TEXT)
743 ,BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID(TEXT)
744 ,BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID(TEXT)
745 ,BEN_ABA_IN_SICKNESS_START_DATE(TEXT)
746 ,BEN_ABA_IO_SICKNESS_START_DATE(TEXT)
747 ,BEN_ABA_IN_SICKNESS_END_DATE(TEXT)
748 ,BEN_ABA_IO_SICKNESS_END_DATE(TEXT)
749 ,BEN_ABA_IN_ABSENCE_DAYS(TEXT)
750 ,BEN_ABA_IO_ABSENCE_DAYS(TEXT)
751 ,BEN_ABA_IN_ABSENCE_HOURS(TEXT)
752 ,BEN_ABA_IO_ABSENCE_HOURS(TEXT)
753 ,BEN_ABA_IN_DATE_NOTIFICATION(TEXT)
754 ,BEN_ABA_IO_DATE_NOTIFICATION(TEXT)
755 ,BEN_ABA_IN_DATE_PROJECTED_END(TEXT)
756 ,BEN_ABA_IO_DATE_PROJECTED_END(TEXT)
757 ,BEN_ABA_IN_DATE_PROJECTED_START(TEXT)
758 ,BEN_ABA_IO_DATE_PROJECTED_START(TEXT)
759 ,BEN_ABA_IN_TIME_END(TEXT)
760 ,BEN_ABA_IO_TIME_END(TEXT)
761 ,BEN_ABA_IN_TIME_PROJECTED_END(TEXT)
762 ,BEN_ABA_IO_TIME_PROJECTED_END(TEXT)
763 ,BEN_ABA_IN_TIME_PROJECTED_START(TEXT)
764 ,BEN_ABA_IO_TIME_PROJECTED_START(TEXT)
765 */
766
767 /*
768 Initialise values.
769 */
770
771 l_default = ''_DEFAULT_'' /* TEXT */
772 l_yn = ''N'' /* TEXT */
773
774 /* Begin Absence Start Date Section
775 */
776
777 /* 01. Determine the old and new values for DATE_START
778
779 NOTE Though these values are stored as dates or numbers they are made
780 available to the formula as TEXT inputs.
781
782 The dates are available in the default canoncial format of
783 "YYYY/MM/DD HH24:MI:SS".
784 Where required, use TO_DATE to convert text into a date.
785
786 The numbers are available in the default canonical format.
787 Where required, use TO_NUMBER to convert into text into a number.
788 */
789
790 l_date_start_old_value = BEN_ABA_IO_DATE_START
791 l_date_start_new_value = BEN_ABA_IN_DATE_START
792 l_absence_type_id_new_value = BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID
793
794 /* 02a. Check that there is a difference between the old and new values of
795 DATE_START.
796
797 NOTE When an absence is deleted, the absence_type_id new value will be
798 defaulted. To record an absence delete a seperate life event reason has
799 to be setup. To prevent a absence start change from being logged on a
800 delete, an additional check is introduced to ensure that new value is not
801 equal to the default.
802 */
803
804 IF NOT l_date_start_old_value = l_date_start_new_value
805 AND
806 NOT l_absence_type_id_new_value = l_default
807 THEN
808 (
809 /* 02b. Set the return flag to "Y"es to allow this person change to cause an
810 "absence start" life event.
811
812 NOTE the default for the flag is "N"o.
813 */
814 l_yn = ''Y''
815 )
816
817 /* End Absence Start Date Section
818 */
819
820 l_debug_message = ''End Absence Start Date Section:''||l_yn
821 l_debug = DEBUG(l_debug_message)
822
823
824 /* Begin Absence Information 1 - Start Date Fraction Section
825 */
826
827 /* 03a. Check that there is a difference between the old and new values of
828 ABS_INFORMATION1.
829
830 NOTE This is an example of how this formula may be extended to trigger the
831 absence start life event reason for other changes on absences.
832
833 First check that life event flag is set to "N", i.e. the previous absence
834 change hasn''t already caused a valid person change to cause the life event.
835 */
836
837 IF l_yn = ''N'' THEN
838 (
839
840 l_abs_information1_old_value = BEN_ABA_IO_ABS_INFORMATION1
841 l_abs_information1_new_value = BEN_ABA_IN_ABS_INFORMATION1
842
843 IF NOT l_abs_information1_old_value = l_abs_information1_new_value
844 AND
845 NOT l_absence_type_id_new_value = l_default
846 THEN
847 (
848 l_yn = ''Y''
849 )
850 )
851
852 /* End Absence Information 1 - Start Date Fraction Section
853 */
854
855 l_debug_message = ''End Absence Information 1 Section:''||l_yn
856 l_debug = DEBUG(l_debug_message)
857
858
859 /* Begin Absence Information 2 - End Date Fraction Section
860 */
861
862 /* 03a. Check that there is a difference between the old and new values of
863 ABS_INFORMATION1.
864
865 NOTE This is an example of how this formula may be extended to trigger the
869 change hasn''t already caused a valid person change to cause the life event.
866 absence start life event reason for other changes on absences.
867
868 First check that life event flag is set to "N", i.e. the previous absence
870 */
871
872 IF l_yn = ''N'' THEN
873 (
874
875 l_abs_information2_old_value = BEN_ABA_IO_ABS_INFORMATION2
876 l_abs_information2_new_value = BEN_ABA_IN_ABS_INFORMATION2
877
878 IF NOT l_abs_information2_old_value = l_abs_information2_new_value
879 AND
880 NOT l_absence_type_id_new_value = l_default
881 THEN
882 (
883 l_yn = ''Y''
884 )
885 )
886
887 /* End Absence Information 2 - End Date Fraction Section
888 */
889
890 /* Begin Absence Days Section
891 */
892
893 /* 03a. Check that there is a difference between the old and new values of
894 ABSENCE_DAYS.
895
896 NOTE This is an example of how this formula may be extended to trigger the
897 absence start life event reason for other changes on absences.
898
899 First check that life event flag is set to "N", i.e. the previous absence
900 change hasn''t already caused a valid person change to cause the life event.
901 */
902
903 /*
904 IF l_yn = ''N'' THEN
905 (
906 l_absence_days_old_value = BEN_ABA_IO_ABSENCE_DAYS
907 l_absence_days_new_value = BEN_ABA_IN_ABSENCE_DAYS
908
909 IF NOT l_absence_days_old_value = l_absence_days_new_value
910 AND
911 NOT l_absence_type_id_new_value = l_default
912 THEN
913 (
914 l_yn = ''Y''
915 )
916 )
917 */
918
919 /* End Absence Days Section
920 */
921
922 RETURN l_yn
923 ';
924 debug(l_proc_name,47);
925 SELECT formula_type_id
926 INTO l_formula_type_id
927 FROM ff_formula_types
928 WHERE formula_type_name = l_formula_type;
929
930 debug(l_proc_name,49);
931 l_formula_count := l_formula_count + 1;
932 INSERT INTO ff_formulas_f
933 (formula_id
934 ,effective_start_date
935 ,effective_end_date
936 ,business_group_id
937 ,legislation_code
938 ,formula_type_id
939 ,formula_name
940 ,description
941 ,formula_text
942 ,sticky_flag)
943 VALUES(ff_formulas_s.NEXTVAL
944 ,l_effective_start_date
945 ,l_effective_end_date
946 ,l_business_group_id
947 ,l_legislation_code
948 ,l_formula_type_id
949 ,l_formula_name
950 ,l_description
951 ,NULL
952 ,NULL
953 )
954 -- WHERE NOT EXISTS
955 -- (SELECT 1
956 -- FROM ff_formulas_f
957 -- WHERE formula_name = l_formula_name
958 -- AND business_group_id = l_business_group_id
959 -- )
960 RETURNING formula_id INTO p_formulas(l_formula_count);
961
962 UPDATE ff_formulas_f
963 SET formula_text = l_text
964 WHERE formula_id = p_formulas(l_formula_count);
965
966 END IF;
967 CLOSE csr_get_formula_id;
968
969 END;
970 --
971 -- End of Person Change Causes Life Event rule for the Absence Start LER
972 --
973 --
974 -- Start of OSP specific section
975 --
976 IF UPPER(p_absence_pay_plan_category) = 'SICKNESS' THEN
977 --
978 -- Start of OSP Participation and Rate Eligibility rule.
979 --
980 BEGIN
981 debug(l_proc_name,50);
982 l_formula_name:= -- VARCHAR2(80)
983 SUBSTRB(l_base_name||
984 -- 1 2 3 4 5
985 --12345678901234567890123456789012345678901234567890
986 '_OSP_PARTICIPATION_ELIGIBILTY_PROFILE_OTHER_RULE'
987 ,1,80);
988 debug(l_proc_name,52);
989 l_formula_type:= -- VARCHAR2(80)
990 'Participation and Rate Eligibility';
991 l_description:= -- VARCHAR2(240)
992 'Sample OSP rule for a benefits elibility profile to check for valid'||
993 ' OSP absence types.';
994 debug(l_proc_name,55);
995 l_text:='
996 /*==============================================================================
997 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
998 Formula Name: PQP_GB_BEN_OSP_PARTICIPATION_ELIGIBILTY_PROFILE_OTHER_RULE
999 Formula Type: Participation and Rate Eligibility
1000 Description : Sample OSP rule for a benefits elibility profile to check for
1001 valid OSP absence types.
1002
1003 NOTE: If you have multiple OSP Plans this formula must either be
1004 extended and/or used with other attribute definitions of the
1005 eligibilty profile to ensure that an employee can only be
1006 eligible to one OSP plan at a time.
1007
1008 DISCLAIMER :
1009 In future releases of HRMS programs, Oracle Corporation may change or
1010 upgrade this formula, and all other definitions for the predefined template
1011 of which this formula is a part. We do not guarantee that the formula and
1012 the predefined template will provide a ready-made solution to be used in your
1013 environment. If the formula does not reflect your business rules, you are
1017 documentation.
1014 responsible for writing a formula of your own to meet your particular
1015 requirements. Any use of this formula and the predefined extract is subject
1016 to the terms of the Oracle license agreement for the HRMS programs and
1018
1019 Change List
1020 ===========
1021 Name Date Version Bug Text
1022 ============== =========== ======= ======= ===================================
1023 rrazdan 29-JUL-2002 110.0 Created.
1024 ==============================================================================*/
1025 /*
1026 Set default values.
1027 */
1028 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY IS ''_DEFAULT_''
1029 DEFAULT FOR BEN_ABS_ABSENCE_TYPE_ID IS -987123654
1030
1031 /*
1032 Initialise values.
1033 */
1034
1035 l_yn = ''N'' /* Text Return value - Not eligible */
1036 l_error = 0 /* Number */
1037 l_absence_type_lookup_code = '' '' /* Text */
1038 l_absence_type_list_name = '' '' /* Text */
1039 l_truncated_yes_no = '' '' /* Text */
1040 l_error_message = '' '' /* Text */
1041 l_absence_type_meaning = '' '' /* Text */
1042 l_absence_category = '' '' /* Text */
1043
1044
1045 /* 01. Determine the type of the current absence.
1046
1047 NOTE we need to convert the type identifier into TEXT as this identifier
1048 is used later as a lookup code.
1049 */
1050
1051 l_absence_type_lookup_code = TO_TEXT(BEN_ABS_ABSENCE_TYPE_ID)
1052
1053 /* 02. Determine the name of the list of eligible absence types for this OSP
1054 plan.
1055
1056 NOTE the absence types "list name" is stored in a flexfield segment on
1057 the element extra information related to current plan.
1058 */
1059
1060 l_error =
1061 PQP_GB_OSP_GET_EXTRA_PLAN_INFORMATION
1062 (''Absence Types List Name''
1063 ,l_absence_type_list_name
1064 ,l_truncated_yes_no
1065 ,l_error_message
1066 )
1067
1068 /* 03a. Check that the absence type is one of those listed in the absence
1069 types list for this OSP plan.
1070
1071 NOTE to check that the absence type is a valid, get the lookup meaning
1072 using the absence type identifier as a lookup code with the absence type
1073 list name as the lookup type.
1074 */
1075 IF l_error = 0 THEN
1076 (
1077 l_absence_type_meaning
1078 = GET_LOOKUP_MEANING
1079 (l_absence_type_list_name
1080 ,l_absence_type_lookup_code
1081 )
1082
1083 /* 03b. Check that the absence type is one of those listed in the absence
1084 types list for this OSP plan.
1085
1086 NOTE if a lookup meaning was found then type of the current
1087 absence is a eligible one. Return eligible flag as "Y"es.
1088 */
1089
1090 IF NOT ISNULL(l_absence_type_meaning) = ''Y'' THEN
1091 (
1092 l_yn = ''Y''
1093 )
1094
1095 )
1096
1097 ELIGIBLE = l_yn
1098 RETURN ELIGIBLE
1099 ';
1100
1101 debug(l_proc_name,57);
1102 SELECT formula_type_id
1103 INTO l_formula_type_id
1104 FROM ff_formula_types
1105 WHERE formula_type_name = l_formula_type;
1106
1107 debug(l_proc_name,59);
1108 l_formula_count := l_formula_count + 1;
1109 INSERT INTO ff_formulas_f
1110 (formula_id
1111 ,effective_start_date
1112 ,effective_end_date
1113 ,business_group_id
1114 ,legislation_code
1115 ,formula_type_id
1116 ,formula_name
1117 ,description
1118 ,formula_text
1119 ,sticky_flag)
1120 VALUES(ff_formulas_s.NEXTVAL
1121 ,p_effective_date
1122 ,l_effective_end_date
1123 ,l_business_group_id
1124 ,l_legislation_code
1125 ,l_formula_type_id
1126 ,l_formula_name
1127 ,l_description
1128 ,NULL
1129 ,NULL
1130 )
1131 -- WHERE NOT EXISTS
1132 -- (SELECT 1
1133 -- FROM ff_formulas_f
1134 -- WHERE formula_name = l_formula_name
1135 -- AND business_group_id = l_business_group_id
1136 -- )
1137 RETURNING formula_id INTO p_formulas(l_formula_count);
1138
1139 UPDATE ff_formulas_f
1140 SET formula_text = l_text
1141 WHERE formula_id = p_formulas(l_formula_count);
1142
1143 END;
1144 --
1145 -- End of OSP Participation and Rate Eligibility rule.
1146 --
1147 --
1148 -- Start of OSP Rate Value Calculation rule.
1149 --
1150 BEGIN
1151
1152 debug(l_proc_name,60);
1153 l_formula_name:= -- VARCHAR2(80)
1154 SUBSTRB(l_base_name||
1155 -- 1 2 3 4 5
1156 --12345678901234567890123456789012345678901234567890
1157 '_OSP_STANDARD_RATES_CALCULATION_METHOD_VALUE_RULE'
1158 --||l_uniquestamp
1159 ,1,80);
1160 debug(l_proc_name,62);
1161 l_formula_type:= -- VARCHAR2(80)
1162 'Rate Value Calculation';
1163 l_description:= -- VARCHAR2(240)
1164 'Sample OSP rule for a benefits standard rate to calculate length of'||
1165 ' service.';
1166
1167 debug(l_proc_name,65);
1168 l_text:='
1172 Formula Type: Rate Value Calculation
1169 /*==============================================================================
1170 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
1171 Formula Name: PQP_GB_BEN_OSP_STANDARD_RATES_CALCULATION_METHOD_VALUE_RULE
1173 Description : Sample OSP rule for a benefits standard rate to calculate length
1174 of service.
1175
1176 DISCLAIMER :
1177 In future releases of HRMS programs, Oracle Corporation may change or
1178 upgrade this formula, and all other definitions for the predefined template
1179 of which this formula is a part. We do not guarantee that the formula and
1180 the predefined template will provide a ready-made solution to be used in your
1181 environment. If the formula does not reflect your business rules, you are
1182 responsible for writing a formula of your own to meet your particular
1183 requirements. Any use of this formula and the predefined extract is subject
1184 to the terms of the Oracle license agreement for the HRMS programs and
1185 documentation.
1186
1187 Change List
1188 ===========
1189 Name Date Version Bug Text
1190 ============== =========== ======= ======= ===================================
1191 rrazdan 29-JUL-2002 110.0 Created.
1192 ==============================================================================*/
1193 /*
1194 Set default values.
1195 */
1196 DEFAULT FOR BEN_ABS_DATE_START IS ''1951/01/01 00:00:00''(date)
1197 DEFAULT FOR EMP_HIRE_DATE IS ''1951/01/01 00:00:00''(date)
1198
1199 /*
1200 Initialise standard default values.
1201 */
1202 l_null = RPAD(''X'',0,''Y'')
1203 l_default = ''_DEFAULT_''
1204 l_default_date = ''1951/01/01 00:00:00''(date)
1205 l_default_canonical_date = ''1951/01/01 00:00:00''
1206 l_default_number = -987123654
1207 l_default_canonical_number = ''-987123654''
1208
1209
1210 l_length_of_service = -987123654
1211
1212 /* 01. Determine the absence start date and the employee hire date.
1213 */
1214 l_absence_start_date = BEN_ABS_DATE_START
1215 l_employee_hire_date = EMP_HIRE_DATE
1216
1217 /* 02a. Check that a absence start date is available for processing.
1218
1219 NOTE If an absence start date was not found, it will be set as default.
1220 This may occur if this rate value calulcation formula is being used in a
1221 plan which does not have an absence "context" available. Please check, and
1222 if required correct,
1223 i. the Option Type of the associated Plan Type is set as "Absences".
1224 ii. the Type of the associated Life Event Reasons are set as "Absence".
1225 */
1226 IF NOT l_absence_start_date = l_default_date THEN
1227 (
1228 /* 02b. Check that a absence start date is available for processing.
1229
1230 NOTE If an employee hire date was not found, it will be defaulted.
1231 This may occur if the the person was not of an eligible person type.
1232 Please check, and if required correct, the associated eligibilty
1233 profile to ensure that only people with a Person Type of "Employee"
1234 are selected for plan enrollment.
1235 */
1236 IF NOT l_employee_hire_date = l_default_date THEN
1237 (
1238 /* 03. Calculate the length of service.
1239
1240 NOTE the sample OSP scheme uses "Months" as the unit of measure for
1241 evaluating length of service based eligibility of OSP entitlements.
1242 The month is rounded down to the nearest interger, by using the
1243 FLOOR function.
1244
1245 */
1246
1247 l_length_of_service
1248 = FLOOR
1249 (MONTHS_BETWEEN
1250 (l_absence_start_date /* later date first */
1251 ,l_employee_hire_date
1252 )
1253 )
1254 )
1255
1256 )
1257
1258 LENGTH_OF_SERVICE = l_length_of_service
1259 RETURN LENGTH_OF_SERVICE
1260 ';
1261
1262 debug(l_proc_name,67);
1263 SELECT formula_type_id
1264 INTO l_formula_type_id
1265 FROM ff_formula_types
1266 WHERE formula_type_name = l_formula_type;
1267
1268 debug(l_proc_name,69);
1269 l_formula_count := l_formula_count + 1;
1270 INSERT INTO ff_formulas_f
1271 (formula_id
1272 ,effective_start_date
1273 ,effective_end_date
1274 ,business_group_id
1275 ,legislation_code
1276 ,formula_type_id
1277 ,formula_name
1278 ,description
1279 ,formula_text
1280 ,sticky_flag)
1281 VALUES(ff_formulas_s.NEXTVAL
1282 ,p_effective_date
1283 ,l_effective_end_date
1284 ,l_business_group_id
1285 ,l_legislation_code
1286 ,l_formula_type_id
1287 ,l_formula_name
1288 ,l_description
1289 ,NULL
1290 ,NULL
1291 )
1292 -- WHERE NOT EXISTS
1293 -- (SELECT 1
1294 -- FROM ff_formulas_f
1295 -- WHERE formula_name = l_formula_name
1296 -- AND business_group_id = l_business_group_id
1297 -- )
1298 RETURNING formula_id INTO p_formulas(l_formula_count);
1299
1300 UPDATE ff_formulas_f
1301 SET formula_text = l_text
1302 WHERE formula_id = p_formulas(l_formula_count);
1303
1304 --hr_utility.trace('Updated '||SQL%ROWCOUNT||' formulae.');
1305
1306
1307 END;
1308 --
1312 -- Start of OSP (Extra Inputs) Rate Value Calculation rule.
1309 -- End of OSP Rate Value Calculation rule.
1310 --
1311 --
1313 --
1314 BEGIN
1315
1316 debug(l_proc_name,70);
1317 l_formula_name:= -- VARCHAR2(80)
1318 SUBSTRB(l_base_name||
1319 -- 1 2 3 4 5
1320 --12345678901234567890123456789012345678901234567890
1321 '_OSP_STANDARD_RATES_GENERAL_EXTRA_INPUT_RULE'
1322 --||l_uniquestamp
1323 ,1,80);
1324 debug(l_proc_name,72);
1325 l_formula_type:= -- VARCHAR2(80)
1326 'Extra Input'; -- to change with new formula type ??BEN??
1327 l_description:= -- VARCHAR2(240)
1328 'Sample OSP rule for a benefits standard rate to feed extra input'||
1329 ' values to an element entry.';
1330
1331 debug(l_proc_name,75);
1332 l_text:='
1333 /*==============================================================================
1334 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
1335 Formula Name: PQP_GB_BEN_OSP_STANDARD_RATES_GENERAL_EXTRA_INPUT_RULE
1336 Formula Type: Rate Value Calculation
1337 Description : Sample OSP rule for a benefits standard rate to feed extra input
1338 values to an element entry.
1339
1340 DISCLAIMER :
1341 In future releases of HRMS programs, Oracle Corporation may change or
1342 upgrade this formula, and all other definitions for the predefined template
1343 of which this formula is a part. We do not guarantee that the formula and
1344 the predefined template will provide a ready-made solution to be used in your
1345 environment. If the formula does not reflect your business rules, you are
1346 responsible for writing a formula of your own to meet your particular
1347 requirements. Any use of this formula and the predefined extract is subject
1348 to the terms of the Oracle license agreement for the HRMS programs and
1349 documentation.
1350
1351 Change List
1352 ===========
1353 Name Date Version Bug Text
1354 ============== =========== ======= ======= ===================================
1355 rrazdan 29-JUL-2002 110.0 Created.
1356 ==============================================================================*/
1357
1358 /*
1359 Set default values for database items.
1360 */
1361 DEFAULT FOR BEN_ABS_ABSENCE_TYPE IS ''_DEFAULT_''
1362 DEFAULT FOR BEN_PLN_PL_ID IS -987123654
1363
1364 /* Other DB Items available
1365 DEFAULT FOR BEN_ABS_ABSENCE_TYPE_ID IS -987123654
1366 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY IS ''_DEFAULT_''
1367 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY_CODE IS ''_DEFAULT_''
1368 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY_ID IS -987123654
1369 DEFAULT FOR BEN_ABS_REASON IS ''_DEFAULT_''
1370 DEFAULT FOR BEN_ABS_REASON_CODE IS ''_DEFAULT_''
1371 DEFAULT FOR BEN_ABS_REASON_ID IS -987123654
1372 DEFAULT FOR BEN_ABS_DATE_START IS ''1951/01/01 00:00:00''(DATE)
1373 DEFAULT FOR BEN_ABS_DATE_END IS ''1951/01/01 00:00:00''(DATE)
1374 DEFAULT FOR BEN_ABS_SICKNESS_START_DATE IS ''1951/01/01 00:00:00''(DATE)
1375 DEFAULT FOR BEN_ABS_SICKNESS_END_DATE IS ''1951/01/01 00:00:00''(DATE)
1376 DEFAULT FOR BEN_ABS_DATE_NOTIFIED IS ''1951/01/01 00:00:00''(DATE)
1377 DEFAULT FOR BEN_SMP_DUE_DATE IS ''1951/01/01 00:00:00''(DATE)
1378 DEFAULT FOR BEN_SMP_MPP_START_DATE IS ''1951/01/01 00:00:00''(DATE)
1379 DEFAULT FOR BEN_SMP_ACTUAL_BIRTH_DATE IS ''1951/01/01 00:00:00''(DATE)
1380 DEFAULT FOR BEN_SMP_LIVE_BIRTH_FLAG IS ''Y''
1381 DEFAULT FOR BEN_SSP_EVIDENCE_DATE IS ''1951/01/01 00:00:00''(DATE)
1382 DEFAULT FOR BEN_SSP_EVIDENCE_SOURCE IS ''_DEFAULT_''
1383 DEFAULT FOR BEN_SSP_MEDICAL_TYPE IS ''SICKNESS''
1384 DEFAULT FOR BEN_SSP_EVIDENCE_STATUS IS ''ACTIVE''
1385 DEFAULT FOR BEN_SSP_SELF_CERTIFICATE IS ''N''
1386
1387 DEFAULT FOR BEN_ABS_ACCEPT_LATE_NOTIFICATION_FLAG IS ''Y''
1388 DEFAULT FOR BEN_ABS_PREGNANCY_RELATED_ILLNESS IS ''N''
1389 DEFAULT FOR BEN_SMP_NOTIFICATION_OF_BIRTH_DATE IS ''1951/01/01 00:00:00''(DATE)
1390 DEFAULT FOR BEN_SSP_EVIDENCE_RECEIVED_DATE IS ''1951/01/01 00:00:00''(DATE)
1391 DEFAULT FOR BEN_SSP_ACCEPT_LATE_EVIDENCE_FLAG IS ''Y''
1392 */
1393
1394 /*
1395 Set default values for formula inputs.
1396 */
1397 DEFAULT FOR BEN_ABS_IV_ABSENCE_ATTENDANCE_ID IS ''_DEFAULT_''
1398 DEFAULT FOR BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
1399 DEFAULT FOR BEN_ABS_IV_DATE_START IS ''_DEFAULT_''
1400 DEFAULT FOR BEN_ABS_IV_DATE_END IS ''_DEFAULT_''
1401 DEFAULT FOR BEN_ABS_IV_ABSENCE_DAYS IS ''_DEFAULT_''
1402
1403 /* Other Inputs Available
1404 DEFAULT FOR BEN_ABS_IV_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
1405 DEFAULT FOR BEN_ABS_IV_ABSENCE_HOURS IS ''_DEFAULT_''
1406 DEFAULT FOR BEN_ABS_IV_DATE_NOTIFICATION IS ''_DEFAULT_''
1407 DEFAULT FOR BEN_ABS_IV_DATE_PROJECTED_END IS ''_DEFAULT_''
1408 DEFAULT FOR BEN_ABS_IV_DATE_PROJECTED_START IS ''_DEFAULT_''
1409 DEFAULT FOR BEN_ABS_IV_SSP1_ISSUED IS ''_DEFAULT_''
1410 DEFAULT FOR BEN_ABS_IV_LINKED_ABSENCE_ID IS ''_DEFAULT_''
1411 DEFAULT FOR BEN_ABS_IV_SICKNESS_START_DATE IS ''_DEFAULT_''
1412 DEFAULT FOR BEN_ABS_IV_SICKNESS_END_DATE IS ''_DEFAULT_''
1413 DEFAULT FOR BEN_ABS_IV_PREGNANCY_RELATED_ILLNESS IS ''_DEFAULT_''
1414 DEFAULT FOR BEN_ABS_IV_MATERNITY_ID IS ''_DEFAULT_''
1415 DEFAULT FOR BEN_PIL_IV_PER_IN_LER_ID IS ''_DEFAULT_''
1419 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE3 IS ''_DEFAULT_''
1416 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE_CATEGORY IS ''_DEFAULT_''
1417 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE1 IS ''_DEFAULT_''
1418 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE2 IS ''_DEFAULT_''
1420 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE4 IS ''_DEFAULT_''
1421 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE5 IS ''_DEFAULT_''
1422 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE6 IS ''_DEFAULT_''
1423 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE7 IS ''_DEFAULT_''
1424 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION_CATEGORY IS ''_DEFAULT_''
1425 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION1 IS ''_DEFAULT_''
1426 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION2 IS ''_DEFAULT_''
1427 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION3 IS ''_DEFAULT_''
1428 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION4 IS ''_DEFAULT_''
1429 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION5 IS ''_DEFAULT_''
1430 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION6 IS ''_DEFAULT_''
1431 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION7 IS ''_DEFAULT_''
1432 */
1433
1434 /*
1435 Declare Input values.
1436
1437 NOTE the naming convention followed
1438 BEN_ABS_IV_<Column Name>
1439 */
1440
1441 INPUTS ARE BEN_ABS_IV_ABSENCE_ATTENDANCE_ID(TEXT)
1442 ,BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
1443 ,BEN_ABS_IV_DATE_START(TEXT)
1444 ,BEN_ABS_IV_DATE_END(TEXT)
1445 ,BEN_ABS_IV_ABSENCE_DAYS(TEXT)
1446 /* Other Inputs Available
1447 ,BEN_ABS_IV_ABS_ATTENDANCE_REASON_ID(TEXT)
1448 ,BEN_ABS_IV_ABSENCE_HOURS(TEXT)
1449 ,BEN_ABS_IV_DATE_NOTIFICATION(TEXT)
1450 ,BEN_ABS_IV_DATE_PROJECTED_END(TEXT)
1451 ,BEN_ABS_IV_DATE_PROJECTED_START(TEXT)
1452 ,BEN_ABS_IV_SSP1_ISSUED(TEXT)
1453 ,BEN_ABS_IV_LINKED_ABSENCE_ID(TEXT)
1454 ,BEN_ABS_IV_SICKNESS_START_DATE(TEXT)
1455 ,BEN_ABS_IV_SICKNESS_END_DATE(TEXT)
1456 ,BEN_ABS_IV_PREGNANCY_RELATED_ILLNESS(TEXT)
1457 ,BEN_ABS_IV_MATERNITY_ID(TEXT)
1458 ,BEN_PIL_IV_PER_IN_LER_ID(TEXT)
1459 ,BEN_ABS_IV_ATTRIBUTE_CATEGORY(TEXT)
1460 ,BEN_ABS_IV_ATTRIBUTE1(TEXT)
1461 ,BEN_ABS_IV_ATTRIBUTE2(TEXT)
1462 ,BEN_ABS_IV_ATTRIBUTE3(TEXT)
1463 ,BEN_ABS_IV_ATTRIBUTE4(TEXT)
1464 ,BEN_ABS_IV_ATTRIBUTE5(TEXT)
1465 ,BEN_ABS_IV_ATTRIBUTE6(TEXT)
1466 ,BEN_ABS_IV_ATTRIBUTE7(TEXT)
1467 ,BEN_ABS_IV_ABS_INFORMATION_CATEGORY(TEXT)
1468 ,BEN_ABS_IV_ABS_INFORMATION1(TEXT)
1469 ,BEN_ABS_IV_ABS_INFORMATION2(TEXT)
1470 ,BEN_ABS_IV_ABS_INFORMATION3(TEXT)
1471 ,BEN_ABS_IV_ABS_INFORMATION4(TEXT)
1472 ,BEN_ABS_IV_ABS_INFORMATION5(TEXT)
1473 ,BEN_ABS_IV_ABS_INFORMATION6(TEXT)
1474 ,BEN_ABS_IV_ABS_INFORMATION7(TEXT)
1475 */
1476
1477
1478 /*
1479 Initialise standard default values.
1480 */
1481 l_null = RPAD(''X'',0,''Y'')
1482 l_default = ''_DEFAULT_''
1483 l_default_date = ''1951/01/01 00:00:00''(date)
1484 l_default_canonical_date = ''1951/01/01 00:00:00''
1485 l_default_number = -987123654
1486 l_default_canonical_number = ''-987123654''
1487
1488
1489
1490 l_absence_id_iv = BEN_ABS_IV_ABSENCE_ATTENDANCE_ID
1491
1492 /* 01a. Check that the absence attendance id input was not defaulted
1493
1494 NOTE If an absence attendance id was not found, it will be set as default.
1495 This may occur if this rate value calulcation formula is being used in a
1496 plan which does not have an absence "context" available.
1497 Please check, and if required correct,
1498 i. the Option Type of the associated Plan Type is set as "Absences".
1499 ii. the Type of the associated Life Event Reasons are set as "Absence".
1500 */
1501 IF NOT l_absence_id_iv = l_default THEN
1502 (
1503 /* 02a. Determine the absence details that need to be fed to element input
1504 values.
1505
1506 NOTE Though these values are stored as dates or numbers they are made
1507 available to the formula as TEXT inputs.
1508
1509 The dates are available in the default canonical format of
1510 "YYYY/MM/DD HH24:MI:SS".
1511 Where required, use TO_DATE to convert text into a date.
1512
1513 The numbers are available in the default canonical format.
1514 Where required, use TO_NUMBER to convert into text into a number.
1515 */
1516
1517 l_absence_id = TO_NUMBER(l_absence_id_iv)
1518
1519 l_plan_id = BEN_PLN_PL_ID
1520
1521 /*l_absence_start_date_dt_iv = l_default_date*/
1522 l_absence_start_date_dt_iv = TO_DATE(BEN_ABS_IV_DATE_START,''YYYY/MM/DD HH24:MI:SS'')
1523
1524 l_absence_start_date = PQP_DATE_TO_DISPLAYDATE(l_absence_start_date_dt_iv)
1525
1526 l_absence_type = BEN_ABS_ABSENCE_TYPE
1527
1528 l_absence_end_date_iv = BEN_ABS_IV_DATE_END
1529
1530 /* 02b. Check that absence end date is available.
1531 */
1532 IF NOT l_absence_end_date_iv = l_default THEN
1533 (
1534 l_absence_end_date
1535 = PQP_DATE_TO_DISPLAYDATE(TO_DATE(l_absence_end_date_iv,''YYYY/MM/DD HH24:MI:SS''))
1536 )
1537 ELSE
1538 (
1539 l_absence_end_date = l_null
1540 )
1541
1542 /* 03. Set the reserved return value "SUBPRIORITY".
1543
1544 NOTE
1545 SUBPRIORITY - is a reserved return value name for this formula type.
1549 Element entry subpriority is used to control the processing order of entries
1546 This value is used the populate the Subpriority field of the element entry,
1547 which the asscoiated standard rate feeds.
1548
1550 of element types with multiple entries allowed.
1551
1552 Absences for OSP/OMP purposes must be processed in the chronological order of
1553 their occurence and not in the order in which they were entered in the
1554 system.
1555
1556 For this purpose, it is recommended that SUBPRIORITY must always be a number
1557 directly proportional to the absence start date.
1558
1559 If multiple element entries are used to process pay for the same absence then
1560 the element designated as the primary element may need to be processed before
1561 the secondary elements.
1562
1563 This formula uses a seeded function,
1564 PQP_GAP_GET_ABSENCE_ELEMENT_ENTRY_SUBPRIORITY, that computes a SUBPRIORITY
1565 based on the Julian value of a given date. The given date being the
1566 absence start date.
1567
1568 */
1569
1570 SUBPRIORITY =
1571 PQP_GAP_GET_ABSENCE_ELEMENT_ENTRY_SUBPRIORITY(l_absence_start_date_dt_iv)
1572
1573
1574 )
1575 /* 02b. Check that the absence attendance id input was not defaulted
1576
1577 NOTE If an absence attendance id was not found, it will be set as default.
1578 all return values should then be returned as null
1579 */
1580 ELSE
1581 (
1582 l_absence_id = l_default_number
1583 l_plan_id = l_default_number
1584 l_absence_start_date = l_null
1585 l_absence_end_date = l_null
1586 l_absence_type = l_null
1587 SUBPRIORITY = TO_NUMBER(l_null)
1588 )
1589
1590 RETURN l_absence_id
1591 ,l_plan_id
1592 ,l_absence_start_date
1593 ,l_absence_end_date
1594 ,l_absence_type
1595 ,SUBPRIORITY
1596 ';
1597
1598 debug(l_proc_name,77);
1599 SELECT formula_type_id
1600 INTO l_formula_type_id
1601 FROM ff_formula_types
1602 WHERE formula_type_name = l_formula_type;
1603
1604 debug(l_proc_name,79);
1605 l_formula_count := l_formula_count + 1;
1606 INSERT INTO ff_formulas_f
1607 (formula_id
1608 ,effective_start_date
1609 ,effective_end_date
1610 ,business_group_id
1611 ,legislation_code
1612 ,formula_type_id
1613 ,formula_name
1614 ,description
1615 ,formula_text
1616 ,sticky_flag)
1617 VALUES(ff_formulas_s.NEXTVAL
1618 ,p_effective_date
1619 ,l_effective_end_date
1620 ,l_business_group_id
1621 ,l_legislation_code
1622 ,l_formula_type_id
1623 ,l_formula_name
1624 ,l_description
1625 ,NULL
1626 ,NULL
1627 )
1628 -- WHERE NOT EXISTS
1629 -- (SELECT 1
1630 -- FROM ff_formulas_f
1631 -- WHERE formula_name = l_formula_name
1632 -- AND business_group_id = l_business_group_id
1633 -- )
1634 RETURNING formula_id INTO p_formulas(l_formula_count);
1635
1636 UPDATE ff_formulas_f
1637 SET formula_text = l_text
1638 WHERE formula_id = p_formulas(l_formula_count);
1639
1640 END;
1641 --
1642 -- End of OSP (Extra Inputs) Rate Value Calculation rule.
1643 --
1644
1645 END IF; -- IF UPPER(p_absence_pay_plan_category) = 'SICKNESS' THEN
1646
1647 IF UPPER(p_absence_pay_plan_category) = 'MATERNITY' THEN
1648 --
1649 -- Start of Participation and Rate Eligibility rule.
1650 --
1651 BEGIN
1652 debug(l_proc_name,80);
1653 l_formula_name:= -- VARCHAR2(80)
1654 SUBSTRB(l_base_name||
1655 -- 1 2 3 4 5
1656 --12345678901234567890123456789012345678901234567890
1657 '_OMP_PARTICIPATION_ELIGIBILTY_PROFILE_OTHER_RULE'
1658 --||l_uniquestamp
1659 ,1,80);
1660 debug(l_proc_name,82);
1661 l_formula_type:= -- VARCHAR2(80)
1662 'Participation and Rate Eligibility';
1663 l_description:= -- VARCHAR2(240)
1664
1665 'Sample OMP rule for a benefits elibility profile to check for valid'||
1666 ' OMP absence types.';
1667
1668 debug(l_proc_name,85);
1669 l_text:='
1670 /*==============================================================================
1671 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
1672 Formula Name: PQP_GB_BEN_OMP_PARTICIPATION_ELIGIBILTY_PROFILE_OTHER_RULE
1673 Formula Type: Participation and Rate Eligibility
1674 Description : Sample OMP rule for a benefits elibility profile to check for
1675 valid OMP absence types.
1676
1677 NOTE: If you have multiple OMP Plans this formula must either be
1678 extended and/or used with other attribute definitions of the
1679 eligibilty profile to ensure that an employee can only be
1680 eligible to one OMP plan at a time.
1681
1682 DISCLAIMER :
1683 In future releases of HRMS programs, Oracle Corporation may change or
1684 upgrade this formula, and all other definitions for the predefined template
1685 of which this formula is a part. We do not guarantee that the formula and
1689 requirements. Any use of this formula and the predefined extract is subject
1686 the predefined template will provide a ready-made solution to be used in your
1687 environment. If the formula does not reflect your business rules, you are
1688 responsible for writing a formula of your own to meet your particular
1690 to the terms of the Oracle license agreement for the HRMS programs and
1691 documentation.
1692
1693 Change List
1694 ===========
1695 Name Date Version Bug Text
1696 ============== =========== ======= ======= ===================================
1697 rrazdan 29-JUL-2002 110.0 Created.
1698 ==============================================================================*/
1699 /*
1700 Set default values.
1701 */
1702 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY IS ''_DEFAULT_''
1703 DEFAULT FOR BEN_ABS_ABSENCE_TYPE_ID IS -987123654
1704
1705 /*
1706 Initialise values.
1707 */
1708
1709 l_yn = ''N'' /* Text Return value - Not eligible */
1710 l_error = 0 /* Number */
1711 l_absence_type_lookup_code = '' '' /* Text */
1712 l_absence_type_list_name = '' '' /* Text */
1713 l_truncated_yes_no = '' '' /* Text */
1714 l_error_message = '' '' /* Text */
1715 l_absence_type_meaning = '' '' /* Text */
1716 l_absence_category = '' '' /* Text */
1717
1718 /* 02. Determine the type of the current absence.
1719
1720 NOTE we need to convert the type identifier into TEXT as this identifier
1721 is used later as a lookup code.
1722 */
1723
1724 l_absence_type_lookup_code = TO_TEXT(BEN_ABS_ABSENCE_TYPE_ID)
1725
1726 /* 03. Determine the name of the list of eligible absence types for this OMP
1727 plan.
1728
1729 NOTE the absence types "list name" is stored in a flexfield segment on
1730 the element extra information related to current plan.
1731 */
1732
1733 l_error_code =
1734 PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
1735 (''Absence Type List Name''
1736 ,l_absence_type_list_name
1737 ,l_truncated_yes_no
1738 ,l_error_message
1739 )
1740
1741 /* 04a. Check that the absence type is one of those listed in the absence
1742 types list for this OMP plan.
1743
1744 NOTE to check that the absence type is a valid, get the lookup meaning
1745 using the absence type identifier as a lookup code with the absence type
1746 list name as the lookup type.
1747 */
1748 IF l_error_code = 0 THEN
1749 (
1750 l_absence_type_meaning
1751 = GET_LOOKUP_MEANING
1752 (l_absence_type_list_name
1753 ,l_absence_type_lookup_code
1754 )
1755
1756 /* 04b. Check that the absence type is one of those listed in the absence
1757 types list for this OMP plan.
1758
1759 NOTE if a lookup meaning was found then type of the current
1760 absence is a eligible one. Return eligible flag as "Y"es.
1761 */
1762
1763 IF NOT ISNULL(l_absence_type_meaning) = ''Y'' THEN
1764 (
1765 l_yn = ''Y''
1766 )
1767
1768 ) /* END IF l_error_code = 0 PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION */
1769
1770 ELIGIBLE = l_yn
1771 RETURN ELIGIBLE
1772 ';
1773 debug(l_proc_name,87);
1774 SELECT formula_type_id
1775 INTO l_formula_type_id
1776 FROM ff_formula_types
1777 WHERE formula_type_name = l_formula_type;
1778
1779 debug(l_proc_name,89);
1780 l_formula_count := l_formula_count + 1;
1781 INSERT INTO ff_formulas_f
1782 (formula_id
1783 ,effective_start_date
1784 ,effective_end_date
1785 ,business_group_id
1786 ,legislation_code
1787 ,formula_type_id
1788 ,formula_name
1789 ,description
1790 ,formula_text
1791 ,sticky_flag)
1792 VALUES(ff_formulas_s.NEXTVAL
1793 ,p_effective_date
1794 ,l_effective_end_date
1795 ,l_business_group_id
1796 ,l_legislation_code
1797 ,l_formula_type_id
1798 ,l_formula_name
1799 ,l_description
1800 ,NULL
1801 ,NULL
1802 )
1803 -- WHERE NOT EXISTS
1804 -- (SELECT 1
1805 -- FROM ff_formulas_f
1806 -- WHERE formula_name = l_formula_name
1807 -- AND business_group_id = l_business_group_id
1808 -- )
1809 RETURNING formula_id INTO p_formulas(l_formula_count);
1810
1811 UPDATE ff_formulas_f
1812 SET formula_text = l_text
1813 WHERE formula_id = p_formulas(l_formula_count);
1814
1815 END;
1816 --
1817 -- End of Participation and Rate Eligibility rule.
1818 --
1819 --
1820 -- Start of Rate Value Calculation rule.
1821 --
1822 BEGIN
1823 debug(l_proc_name,90);
1824 l_formula_name:= -- VARCHAR2(80)
1825 SUBSTRB(l_base_name||
1826 -- 1 2 3 4 5
1827 --12345678901234567890123456789012345678901234567890
1828 '_OMP_STANDARD_RATES_CALCULATION_METHOD_VALUE_RULE'
1829 --||l_uniquestamp
1830 ,1,80);
1831 debug(l_proc_name,92);
1832 l_formula_type:= -- VARCHAR2(80)
1833 'Rate Value Calculation';
1834 l_description:= -- VARCHAR2(240)
1838 l_text:='
1835 'Sample OMP rule for a benefits standard rate to calculate length of'||
1836 ' service.';
1837 debug(l_proc_name,95);
1839 /*==============================================================================
1840 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
1841 Formula Name: PQP_GB_BEN_OMP_STANDARD_RATES_CALCULATION_METHOD_VALUE_RULE
1842 Formula Type: Rate Value Calculation
1843 Description : Sample OMP rule for a benefits standard rate to calculate length
1844 of service.
1845
1846 DISCLAIMER :
1847 In future releases of HRMS programs, Oracle Corporation may change or
1848 upgrade this formula, and all other definitions for the predefined template
1849 of which this formula is a part. We do not guarantee that the formula and
1850 the predefined template will provide a ready-made solution to be used in your
1851 environment. If the formula does not reflect your business rules, you are
1852 responsible for writing a formula of your own to meet your particular
1853 requirements. Any use of this formula and the predefined extract is subject
1854 to the terms of the Oracle license agreement for the HRMS programs and
1855 documentation.
1856
1857 Change List
1858 ===========
1859 Name Date Version Bug Text
1860 ============== =========== ======= ======= ===================================
1861 rrazdan 29-JUL-2002 110.0 Created.
1862 ==============================================================================*/
1863 /*
1864 Set default values.
1865 */
1866 DEFAULT FOR BEN_ABS_DATE_START IS ''1951/01/01 00:00:00''(date)
1867 DEFAULT FOR EMP_HIRE_DATE IS ''1951/01/01 00:00:00''(date)
1868
1869 /*
1870 Initialise standard default values.
1871 */
1872 l_null = RPAD(''X'',0,''Y'')
1873 l_default = ''_DEFAULT_''
1874 l_default_date = ''1951/01/01 00:00:00''(date)
1875 l_default_canonical_date = ''1951/01/01 00:00:00''
1876 l_default_number = -987123654
1877 l_default_canonical_number = ''-987123654''
1878
1879
1880 l_length_of_service = -987123654
1881
1882 /* 01. Determine the absence start date and the employee hire date.
1883 */
1884 l_absence_start_date = BEN_ABS_DATE_START
1885 l_employee_hire_date = EMP_HIRE_DATE
1886
1887 /* 02a. Check that a absence start date is available for processing.
1888
1889 NOTE If an absence start date was not found, it will be set as default.
1890 This may occur if this rate value calulcation formula is being used in a
1891 plan which does not have an absence "context" available. Please check, and if
1892 required correct,
1893 i. the Option Type of the associated Plan Type is set as "Absences".
1894 ii. the Type of the associated Life Event Reasons are set as "Absence".
1895 */
1896 IF NOT l_absence_start_date = l_default_date THEN
1897 (
1898
1899 /* 02b. Check that a absence start date is available for processing.
1900
1901 NOTE If an employee hire date was not found, it will be defaulted.
1902 This may occur if the the person was not of an eligible person type.
1903 Please check, and if required correct, the associated eligibilty
1904 profile to ensure that only people with a Person Type of "Employee"
1905 are selected for plan enrollment.
1906 */
1907 IF NOT l_employee_hire_date = l_default_date THEN
1908 (
1909 /* 03a. Determine the OMP Qualifying date.
1910
1914 "M" - For Maternity Start Date
1911 NOTE As a first step determine the OMP Qualifying Date Type.
1912 This can three possible values
1913 "S" - For SMP Qualifying Date
1915 "P" - For a given duration Prior to the Expected Week of Confinement
1916 (EWC)
1917 */
1918
1919 l_omp_qualifying_date_type = l_default
1920 l_truncated_yes_no = l_default
1921 l_error_message = l_default
1922
1923 l_error_code =
1924 PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
1925 (''OMP Qualifying Date Type''
1926 ,l_omp_qualifying_date_type
1927 ,l_truncated_yes_no
1928 ,l_error_message
1929 )
1930
1931 IF l_error_code = 0 THEN
1932 (
1933 /* 03b. Determine the OMP Qualifying date.
1934
1935 NOTE If the OMP Qualifying Date Type is ''S'' then it means that it
1936 is the same as SMP Qualifying Date. This date is available on the
1937 Maternity form as the field "Qualifying Week".
1938 */
1939
1940 l_omp_qualifying_date = l_default_date
1941
1942 IF l_omp_qualifying_date_type = ''S'' THEN
1943 (
1944
1945 l_smp_qualifying_date_text =
1946 BEN_GET_MATERNITY
1947 (''Qualifying Week''
1948 ,l_error_code
1949 ,l_error_message
1950 )
1951
1952 IF l_error_code = 0 THEN
1953 (
1954 l_omp_qualifying_date = TO_DATE(l_smp_qualifying_date_text,''YYYY/MM/DD HH24:MI:SS'')
1955 )
1956 )
1957
1958 /* 03b. Determine the OMP Qualifying date.
1959
1960 NOTE If the OMP Qualifying Date Type is ''M'' then it means that it
1961 is the same as Maternity Start Date. Maternity Start Date is start
1962 date of the maternity absence.
1963 */
1964
1965 IF l_omp_qualifying_date_type = ''M'' THEN
1966 (
1967 l_omp_qualifying_date = l_absence_start_date
1968 )
1969
1970 /* 03b. Determine the OMP Qualifying date.
1971
1972 NOTE If the OMP Qualifying Date Type is ''P'' then it means that the
1973 OMP Qualifying Date has to be derived as a given period prior to the
1974 expected week of confinement (EWC).
1975
1976 The period is defined as extra information on the OMP Plan(as setup
1977 in the OMP template form). The period is defined as a given
1978 "Duration" of a certain "UOM", prior to the EWC.
1979
1980 EWC is available on the Maternity form.
1981 */
1982
1983
1984 IF l_omp_qualifying_date_type = ''P'' THEN
1985 (
1986
1987 /* 03c. Determine the "Prior to EWC Duration".
1988
1989 NOTE To access the OMP Plan rules and regulations as defined on the
1990 OMP template form. Use PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION.
1991 All values returned by this function are always TEXT datatype. If
1992 the expected value is a NUMBER/DATE appropriate conversions might
1993 be required before using the value.
1994 */
1995
1996 l_prior_to_ewc_duration_text = l_default_canonical_number
1997 l_error_code =
1998 PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
1999 (''Prior to EWC Duration''
2000 ,l_prior_to_ewc_duration_text
2001 ,l_truncated_yes_no
2002 ,l_error_message
2003 )
2004 l_prior_to_ewc_duration = TO_NUMBER(l_prior_to_ewc_duration_text)
2005
2006 /* 03c. Determine the "Prior to EWC UOM".
2007
2008 NOTE To access the OMP Plan rules and regulations as defined on the
2009 OMP template form. Use PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION.
2010 All values returned by this function are always TEXT datatype. If
2011 the expected value is a NUMBER/DATE appropriate conversions might
2012 be required before using the value.
2013
2014 "Prior to EWC UOM" has three possible values.
2015
2016 i. DAYS
2017 ii. WEEKS
2018 iii. MONTHS
2019 */
2020 l_prior_to_ewc_uom = l_default
2021 l_error_code =
2022 PQP_GB_OMP_GET_EXTRA_PLAN_INFORMATION
2023 (''Prior to EWC UOM''
2024 ,l_prior_to_ewc_uom
2025 ,l_truncated_yes_no
2026 ,l_error_message
2027 )
2028
2029 /* 03c. Determine the EWC date.
2030
2031 NOTE This value is available on the Maternity form. To access
2032 details as seen on the Maternity form use the PQP_GET_MATERNITY
2033 function, passing the appropriate field name.
2034 All values returned by the this function are always TEXT
2035 datatype.If the expected value is a NUMBER/DATE appropriate
2036 conversions might be required before using the value.
2037 */
2038 l_ewc_text =
2039 BEN_GET_MATERNITY
2040 (''EWC''
2041 ,l_error_code
2042 ,l_error_message
2043 )
2047 l_ewc = TO_DATE(l_ewc_text,''YYYY/MM/DD HH24:MI:SS'')
2044
2045 IF l_error_code = 0 THEN
2046 (
2048
2049 /* 03c. Calculate the OMP Qualifying Date as
2050
2051 EWC - (Duration) Days
2052 or
2053 EWC - (Duration)*7 Days (If UOM is Weeks)
2054 or
2055 EWC - (Duration) Months
2056 */
2057
2058 IF l_prior_to_ewc_uom = ''DAYS'' THEN
2059 (
2060 l_omp_qualifying_date =
2061 ADD_DAYS
2062 (l_ewc
2063 ,- l_prior_to_ewc_duration
2064 )
2065 )
2066
2067 IF l_prior_to_ewc_uom = ''WEEKS'' THEN
2068 (
2069 l_omp_qualifying_date =
2070 ADD_DAYS
2071 (l_ewc
2072 ,-l_prior_to_ewc_duration * 7
2073 )
2074 )
2075
2076 IF l_prior_to_ewc_uom = ''MONTHS'' THEN
2077 (
2078 l_omp_qualifying_date =
2079 ADD_MONTHS
2080 (l_ewc
2081 ,-l_prior_to_ewc_duration
2082 )
2083 )
2084
2085 ) /* END IF NOT l_error_code = 0 BEN_GET_MATERNITY(EWC) */
2086
2087 ) /* END IF l_omp_qualifying_date_type = ''P'' */
2088
2089 /* 04. Calculate the length of service as the period between the
2090 employee hire date and the OMP qualifying date.
2091
2092 NOTE The length of service for the sample OMP scheme is measured
2093 in weeks.The week is rounded down to the nearest interger, by
2094 using the FLOOR function.
2095 */
2096
2097 IF NOT l_omp_qualifying_date = l_default_date THEN
2098 (
2099 l_length_of_service =
2100 FLOOR(DAYS_BETWEEN(l_omp_qualifying_date,l_employee_hire_date)/7)
2101 )
2102
2103 ) /* END IF NOT l_error_code = 0 OMP Qualifying Date Type */
2104
2105 ) /* END IF NOT l_employee_hire_date = l_default_date */
2106
2107 ) /* END IF NOT l_absence_start_date = l_default_date THEN */
2108
2109 RETURN l_length_of_service
2110 ';
2111
2112 debug(l_proc_name,97);
2113 SELECT formula_type_id
2114 INTO l_formula_type_id
2115 FROM ff_formula_types
2116 WHERE formula_type_name = l_formula_type;
2117
2118 debug(l_proc_name,99);
2119 l_formula_count := l_formula_count + 1;
2120 INSERT INTO ff_formulas_f
2121 (formula_id
2122 ,effective_start_date
2123 ,effective_end_date
2124 ,business_group_id
2125 ,legislation_code
2126 ,formula_type_id
2127 ,formula_name
2128 ,description
2129 ,formula_text
2130 ,sticky_flag)
2131 VALUES(ff_formulas_s.NEXTVAL
2132 ,p_effective_date
2133 ,l_effective_end_date
2134 ,l_business_group_id
2135 ,l_legislation_code
2136 ,l_formula_type_id
2137 ,l_formula_name
2138 ,l_description
2139 ,NULL
2140 ,NULL
2141 )
2142 -- WHERE NOT EXISTS
2143 -- (SELECT 1
2144 -- FROM ff_formulas_f
2145 -- WHERE formula_name = l_formula_name
2146 -- AND business_group_id = l_business_group_id
2147 -- )
2148 RETURNING formula_id INTO p_formulas(l_formula_count);
2149
2150 UPDATE ff_formulas_f
2151 SET formula_text = l_text
2152 WHERE formula_id = p_formulas(l_formula_count);
2153
2154 END;
2155 --
2156 -- End of Rate Value Calculation rule.
2157 --
2158 --
2159 -- Start of (Extra Inputs) Rate Value Calculation rule.
2160 --
2161 BEGIN
2162
2163 debug(l_proc_name,100);
2164 l_formula_name:= -- VARCHAR2(80)
2165 SUBSTRB(l_base_name||
2166 -- 1 2 3 4 5
2167 --12345678901234567890123456789012345678901234567890
2168 '_OMP_STANDARD_RATES_GENERAL_EXTRA_INPUT_RULE'
2169 --||l_uniquestamp
2170 ,1,80);
2171
2172 debug(l_proc_name,102);
2173 l_formula_type:= -- VARCHAR2(80)
2174 'Extra Input'; -- to change with new formula type ??BEN?? -- done
2175 l_description:= -- VARCHAR2(240)
2176 'Sample OMP rule for a benefits standard rate to feed extra input'||
2177 ' values to an element entry.';
2178
2179 debug(l_proc_name,105);
2180 l_text:='
2181 /*==============================================================================
2182 $Header: pqpgbofm.pkb 120.1 2005/10/04 08:31:03 rrazdan noship $
2183 Formula Name: PQP_GB_BEN_OMP_STANDARD_RATES_GENERAL_EXTRA_INPUT_RULE
2184 Formula Type: Rate Value Calculation
2185 Description : Sample OMP rule for a benefits standard rate to feed extra input
2186 values to an element entry.
2187
2188 DISCLAIMER :
2189 In future releases of HRMS programs, Oracle Corporation may change or
2190 upgrade this formula, and all other definitions for the predefined template
2191 of which this formula is a part. We do not guarantee that the formula and
2192 the predefined template will provide a ready-made solution to be used in your
2196 to the terms of the Oracle license agreement for the HRMS programs and
2193 environment. If the formula does not reflect your business rules, you are
2194 responsible for writing a formula of your own to meet your particular
2195 requirements. Any use of this formula and the predefined extract is subject
2197 documentation.
2198
2199 Change List
2200 ===========
2201 Name Date Version Bug Text
2202 ============== =========== ======= ======= ===================================
2203 rrazdan 29-JUL-2002 110.0 Created.
2204 ==============================================================================*/
2205
2206 /********1*********2*********3*********4*********5*********6*********7*********8
2207 123456789012345678901234567890123456789012345678901234567890123456789012345678*/
2208
2209 /*
2210 Set default values for database items.
2211 */
2212 DEFAULT FOR BEN_ABS_ABSENCE_TYPE IS ''_DEFAULT_''
2213 DEFAULT FOR BEN_PLN_PL_ID IS -987123654
2214
2215 /* Other DB Items available
2216 DEFAULT FOR BEN_ABS_ABSENCE_TYPE_ID IS -987123654
2217 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY IS ''_DEFAULT_''
2218 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY_CODE IS ''_DEFAULT_''
2219 DEFAULT FOR BEN_ABS_ABSENCE_CATEGORY_ID IS -987123654
2220 DEFAULT FOR BEN_ABS_REASON IS ''_DEFAULT_''
2221 DEFAULT FOR BEN_ABS_REASON_CODE IS ''_DEFAULT_''
2222 DEFAULT FOR BEN_ABS_REASON_ID IS -987123654
2223 DEFAULT FOR BEN_ABS_DATE_START IS ''1951/01/01 00:00:00''(DATE)
2224 DEFAULT FOR BEN_ABS_DATE_END IS ''1951/01/01 00:00:00''(DATE)
2225 DEFAULT FOR BEN_ABS_SICKNESS_START_DATE IS ''1951/01/01 00:00:00''(DATE)
2226 DEFAULT FOR BEN_ABS_SICKNESS_END_DATE IS ''1951/01/01 00:00:00''(DATE)
2227 DEFAULT FOR BEN_ABS_DATE_NOTIFIED IS ''1951/01/01 00:00:00''(DATE)
2228 DEFAULT FOR BEN_SMP_DUE_DATE IS ''1951/01/01 00:00:00''(DATE)
2229 DEFAULT FOR BEN_SMP_MPP_START_DATE IS ''1951/01/01 00:00:00''(DATE)
2230 DEFAULT FOR BEN_SMP_ACTUAL_BIRTH_DATE IS ''1951/01/01 00:00:00''(DATE)
2231 DEFAULT FOR BEN_SMP_LIVE_BIRTH_FLAG IS ''Y''
2232 DEFAULT FOR BEN_SSP_EVIDENCE_DATE IS ''1951/01/01 00:00:00''(DATE)
2233 DEFAULT FOR BEN_SSP_EVIDENCE_SOURCE IS ''_DEFAULT_''
2234 DEFAULT FOR BEN_SSP_MEDICAL_TYPE IS ''SICKNESS''
2235 DEFAULT FOR BEN_SSP_EVIDENCE_STATUS IS ''ACTIVE''
2236 DEFAULT FOR BEN_SSP_SELF_CERTIFICATE IS ''N''
2237 DEFAULT FOR BEN_ABS_ACCEPT_LATE_NOTIFICATION_FLAG IS ''Y''
2238 DEFAULT FOR BEN_ABS_PREGNANCY_RELATED_ILLNESS IS ''N''
2239 DEFAULT FOR BEN_SSP_ACCEPT_LATE_EVIDENCE_FLAG IS ''Y''
2240 DEFAULT FOR BEN_SMP_NOTIFICATION_OF_BIRTH_DATE IS ''1951/01/01 00:00:00''(DATE)
2241 DEFAULT FOR BEN_SSP_EVIDENCE_RECEIVED_DATE IS ''1951/01/01 00:00:00''(DATE)
2242 */
2243
2244 /*
2245 Set default values for formula inputs.
2246 */
2247 DEFAULT FOR BEN_ABS_IV_ABSENCE_ATTENDANCE_ID IS ''_DEFAULT_''
2248 DEFAULT FOR BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID IS ''_DEFAULT_''
2249 DEFAULT FOR BEN_ABS_IV_DATE_START IS ''_DEFAULT_''
2250 DEFAULT FOR BEN_ABS_IV_DATE_END IS ''_DEFAULT_''
2251 DEFAULT FOR BEN_ABS_IV_ABSENCE_DAYS IS ''_DEFAULT_''
2252 /* Other Inputs Available
2253 DEFAULT FOR BEN_ABS_IV_ABS_ATTENDANCE_REASON_ID IS ''_DEFAULT_''
2254 DEFAULT FOR BEN_ABS_IV_ABSENCE_HOURS IS ''_DEFAULT_''
2255 DEFAULT FOR BEN_ABS_IV_DATE_NOTIFICATION IS ''_DEFAULT_''
2256 DEFAULT FOR BEN_ABS_IV_DATE_PROJECTED_END IS ''_DEFAULT_''
2257 DEFAULT FOR BEN_ABS_IV_DATE_PROJECTED_START IS ''_DEFAULT_''
2258 DEFAULT FOR BEN_ABS_IV_SSP1_ISSUED IS ''_DEFAULT_''
2259 DEFAULT FOR BEN_ABS_IV_LINKED_ABSENCE_ID IS ''_DEFAULT_''
2260 DEFAULT FOR BEN_ABS_IV_SICKNESS_START_DATE IS ''_DEFAULT_''
2261 DEFAULT FOR BEN_ABS_IV_SICKNESS_END_DATE IS ''_DEFAULT_''
2262 DEFAULT FOR BEN_ABS_IV_PREGNANCY_RELATED_ILLNESS IS ''_DEFAULT_''
2263 DEFAULT FOR BEN_ABS_IV_MATERNITY_ID IS ''_DEFAULT_''
2264 DEFAULT FOR BEN_PIL_IV_PER_IN_LER_ID IS ''_DEFAULT_''
2265 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE_CATEGORY IS ''_DEFAULT_''
2266 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE1 IS ''_DEFAULT_''
2267 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE2 IS ''_DEFAULT_''
2268 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE3 IS ''_DEFAULT_''
2269 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE4 IS ''_DEFAULT_''
2270 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE5 IS ''_DEFAULT_''
2271 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE6 IS ''_DEFAULT_''
2272 DEFAULT FOR BEN_ABS_IV_ATTRIBUTE7 IS ''_DEFAULT_''
2273 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION_CATEGORY IS ''_DEFAULT_''
2274 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION1 IS ''_DEFAULT_''
2275 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION2 IS ''_DEFAULT_''
2276 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION3 IS ''_DEFAULT_''
2277 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION4 IS ''_DEFAULT_''
2278 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION5 IS ''_DEFAULT_''
2279 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION6 IS ''_DEFAULT_''
2280 DEFAULT FOR BEN_ABS_IV_ABS_INFORMATION7 IS ''_DEFAULT_''
2281 */
2282
2283 /*
2284 Declare Input values.
2285
2286 NOTE the naming convention followed
2287 BEN_ABS_IV_<Column Name> - New Values
2288 */
2289
2290 INPUTS ARE BEN_ABS_IV_ABSENCE_ATTENDANCE_ID(TEXT)
2291 ,BEN_ABS_IV_ABSENCE_ATTENDANCE_TYPE_ID(TEXT)
2292 ,BEN_ABS_IV_DATE_START(TEXT)
2296 ,BEN_ABS_IV_ABS_ATTENDANCE_REASON_ID(TEXT)
2293 ,BEN_ABS_IV_DATE_END(TEXT)
2294 ,BEN_ABS_IV_ABSENCE_DAYS(TEXT)
2295 /* Other Inputs Available
2297 ,BEN_ABS_IV_ABSENCE_HOURS(TEXT)
2298 ,BEN_ABS_IV_DATE_NOTIFICATION(TEXT)
2299 ,BEN_ABS_IV_DATE_PROJECTED_END(TEXT)
2300 ,BEN_ABS_IV_DATE_PROJECTED_START(TEXT)
2301 ,BEN_ABS_IV_SSP1_ISSUED(TEXT)
2302 ,BEN_ABS_IV_LINKED_ABSENCE_ID(TEXT)
2303 ,BEN_ABS_IV_SICKNESS_START_DATE(TEXT)
2304 ,BEN_ABS_IV_SICKNESS_END_DATE(TEXT)
2305 ,BEN_ABS_IV_PREGNANCY_RELATED_ILLNESS(TEXT)
2306 ,BEN_ABS_IV_MATERNITY_ID(TEXT)
2307 ,BEN_PIL_IV_PER_IN_LER_ID(TEXT)
2308 ,BEN_ABS_IV_ATTRIBUTE_CATEGORY(TEXT)
2309 ,BEN_ABS_IV_ATTRIBUTE1(TEXT)
2310 ,BEN_ABS_IV_ATTRIBUTE2(TEXT)
2311 ,BEN_ABS_IV_ATTRIBUTE3(TEXT)
2312 ,BEN_ABS_IV_ATTRIBUTE4(TEXT)
2313 ,BEN_ABS_IV_ATTRIBUTE5(TEXT)
2314 ,BEN_ABS_IV_ATTRIBUTE6(TEXT)
2315 ,BEN_ABS_IV_ATTRIBUTE7(TEXT)
2316 ,BEN_ABS_IV_ABS_INFORMATION_CATEGORY(TEXT)
2317 ,BEN_ABS_IV_ABS_INFORMATION1(TEXT)
2318 ,BEN_ABS_IV_ABS_INFORMATION2(TEXT)
2319 ,BEN_ABS_IV_ABS_INFORMATION3(TEXT)
2320 ,BEN_ABS_IV_ABS_INFORMATION4(TEXT)
2321 ,BEN_ABS_IV_ABS_INFORMATION5(TEXT)
2322 ,BEN_ABS_IV_ABS_INFORMATION6(TEXT)
2323 ,BEN_ABS_IV_ABS_INFORMATION7(TEXT)
2324 */
2325
2326 /*
2327 Initialise standard default values.
2328 */
2329 l_null = RPAD(''X'',0,''Y'')
2330 l_default = ''_DEFAULT_''
2331 l_default_date = ''1951/01/01 00:00:00''(date)
2332 l_default_canonical_date = ''1951/01/01 00:00:00''
2333 l_default_number = -987123654
2334 l_default_canonical_number = ''-987123654''
2335
2336
2337
2338 l_absence_id_iv = BEN_ABS_IV_ABSENCE_ATTENDANCE_ID
2339
2340 /* 01. Check that the absence attendance id input was not defaulted
2341
2342 NOTE If an absence attendance id was not found, it will be set as default.
2343 This may occur if this rate value calulcation formula is being used in a
2344 plan which does not have an absence "context" available.
2345 Please check, and if required correct,
2346 i. the Option Type of the associated Plan Type is set as "Absences".
2347 ii. the Type of the associated Life Event Reasons are set as "Absence".
2348 */
2349 IF NOT l_absence_id_iv = l_default THEN
2350 (
2351 /* 02a. Determine the absence details that need to be fed to element input
2352 values.
2353
2354 NOTE Though these values are stored as dates or numbers they are made
2355 available to the formula as TEXT inputs.
2356
2357 The dates are available in the default canonical format of
2358 "YYYY/MM/DD HH24:MI:SS".
2359 Where required, use TO_DATE to convert text into a date.
2360
2361 The numbers are available in the default canonical format.
2362 Where required, use TO_NUMBER to convert into text into a number.
2363 */
2364
2365 l_absence_id = TO_NUMBER(l_absence_id_iv)
2366
2367 l_plan_id = BEN_PLN_PL_ID
2368
2369 /*l_absence_start_date_dt_iv = l_default_date*/
2370 l_absence_start_date_dt_iv = TO_DATE(BEN_ABS_IV_DATE_START,''YYYY/MM/DD HH24:MI:SS'')
2371
2372 l_maternity_start_date =
2373 PQP_DATE_TO_DISPLAYDATE(l_absence_start_date_dt_iv)
2374
2375 l_absence_type = BEN_ABS_ABSENCE_TYPE
2376
2377 l_absence_end_date_iv = BEN_ABS_IV_DATE_END
2378
2379 /* 02b. Check that absence end date is available.
2380 */
2381 IF NOT l_absence_end_date_iv = l_default THEN
2382 (
2383 l_maternity_end_date
2384 = PQP_DATE_TO_DISPLAYDATE(TO_DATE(l_absence_end_date_iv,''YYYY/MM/DD HH24:MI:SS''))
2385 )
2386 ELSE
2387 (
2388 l_maternity_end_date = l_null
2389 )
2390
2391 /* 03. Determine the EWC date.
2392
2393 NOTE This value is available on the Maternity form. To access details as
2394 seen on the Maternity form use the PQP_GET_MATERNITY function, passing the
2395 appropriate field name. All values returned by the this function are always
2396 TEXT datatype.If the expected value is a NUMBER/DATE appropriate
2397 conversions might be required before using the value.
2398 */
2399
2400 l_error_code = 0
2401 l_error_message = l_default
2402 l_ewc =
2403 BEN_GET_MATERNITY
2404 (''EWC''
2405 ,l_error_code
2406 ,l_error_message
2407 )
2408
2409 IF l_error_code = 0 THEN
2410 (
2411 l_ewc = PQP_DATE_TO_DISPLAYDATE(TO_DATE(l_ewc,''YYYY/MM/DD HH24:MI:SS''))
2412 )
2413
2414 /* 04. Set the reserved return value "SUBPRIORITY".
2415
2416 NOTE
2417 SUBPRIORITY - is a reserved return value name for this formula type.
2418 This value is used the populate the Subpriority field of the element entry,
2419 which the asscoiated standard rate feeds.
2420
2421 Element entry subpriority is used to control the processing order of
2422 entries of element types with multiple entries allowed.
2423
2424 Absences for OSP/OMP purposes must be processed in the chronological order
2425 of their occurence and not in the order in which they were entered in the
2426 system.
2427
2431 If multiple element entries are used to process pay for the same absence
2428 For this purpose, it is recommended that SUBPRIORITY must always be a
2429 number directly proportional to the absence start date.
2430
2432 then the element designated as the primary element may need to be processed
2433 before the secondary elements.
2434
2435 This formula uses a seeded function,
2436 PQP_GAP_GET_ABSENCE_ELEMENT_ENTRY_SUBPRIORITY, that computes a SUBPRIORITY
2437 based on the Julian value of a given date. The given date being the
2438 absence start date.
2439 */
2440 SUBPRIORITY =
2441 PQP_GAP_GET_ABSENCE_ELEMENT_ENTRY_SUBPRIORITY(l_absence_start_date_dt_iv)
2442
2443 )
2444 /* 02b. Check that the absence attendance id input was not defaulted
2445
2446 NOTE If an absence attendance id was not found, it will be set as default.
2447 all return values should then be returned as null or default.
2448 */
2449 ELSE
2450 (
2451 l_absence_id = l_default_number
2452 l_plan_id = l_default_number
2453 l_maternity_start_date = l_null
2454 l_maternity_end_date = l_null
2455 l_absence_type = l_null
2456 l_ewc = l_null
2457 SUBPRIORITY = TO_NUMBER(l_null)
2458 )
2459
2460 RETURN l_absence_id
2461 ,l_plan_id
2462 ,l_maternity_start_date
2463 ,l_maternity_end_date
2464 ,l_absence_type
2465 ,l_ewc
2466 ,SUBPRIORITY
2467 ';
2468
2469 debug(l_proc_name,107);
2470 SELECT formula_type_id
2471 INTO l_formula_type_id
2472 FROM ff_formula_types
2473 WHERE formula_type_name = l_formula_type;
2474
2475 --DELETE FROM ff_formulas_f WHERE formula_name = l_formula_name;
2476
2477 debug(l_proc_name,109);
2478 l_formula_count := l_formula_count + 1;
2479 INSERT INTO ff_formulas_f
2480 (formula_id
2481 ,effective_start_date
2482 ,effective_end_date
2483 ,business_group_id
2484 ,legislation_code
2485 ,formula_type_id
2486 ,formula_name
2487 ,description
2488 ,formula_text
2489 ,sticky_flag)
2490 VALUES(ff_formulas_s.NEXTVAL
2491 ,p_effective_date
2492 ,l_effective_end_date
2493 ,l_business_group_id
2494 ,l_legislation_code
2495 ,l_formula_type_id
2496 ,l_formula_name
2497 ,l_description
2498 ,NULL
2499 ,NULL
2500 )
2501 -- WHERE NOT EXISTS
2502 -- (SELECT 1
2503 -- FROM ff_formulas_f
2504 -- WHERE formula_name = l_formula_name
2505 -- AND business_group_id = l_business_group_id
2506 -- )
2507 RETURNING formula_id INTO p_formulas(l_formula_count);
2508
2509 UPDATE ff_formulas_f
2510 SET formula_text = l_text
2511 WHERE formula_id = p_formulas(l_formula_count);
2512
2513 END;
2514 --
2515 -- End of (Extra Inputs) Rate Value Calculation rule.
2516 --
2517 END IF; --IF UPPER(p_absence_pay_plan_category) = 'MATERNITY' THEN
2518
2519 END IF; -- if g_use_this_functionality then
2520
2521 debug(l_proc_name,110);
2522 debug_exit(l_proc_name);
2523
2524 EXCEPTION
2525
2526 WHEN OTHERS THEN
2527 debug(SQLCODE);
2528 debug(SQLERRM);
2529 debug(l_proc_name,-10);
2533
2530 debug_exit(l_proc_name);
2531 RAISE;
2532 END create_ben_formulas;
2534 --
2535 --
2536 --
2537 PROCEDURE delete_ben_formulas
2538 (p_business_group_id IN NUMBER
2539 ,p_effective_date IN DATE
2540 ,p_absence_pay_plan_category IN VARCHAR2
2541 ,p_base_name IN VARCHAR2
2542 ,p_error_code OUT NOCOPY NUMBER
2543 ,p_error_message OUT NOCOPY VARCHAR2
2544 )
2545 IS
2546
2547 l_proc_name VARCHAR2(61):= g_proc_name||'delete_ben_formulas';
2548
2549
2550 l_formula_name_prefix VARCHAR2(100);
2551 l_formula_name ff_formulas_f.formula_name%TYPE;
2552
2553 BEGIN
2554 debug_enter(l_proc_name); -- comment to switch tracing on
2555
2556 p_error_code := 0;
2557 p_error_message := NULL;
2558
2559 IF g_use_this_functionality THEN
2560
2561 IF UPPER(p_absence_pay_plan_category) = 'SICKNESS' THEN
2562 l_formula_name_prefix := UPPER(p_base_name)||'_OSP_';
2563 debug(l_proc_name,10);
2564 ELSE
2565 l_formula_name_prefix := UPPER(p_base_name)||'_OMP_';
2566 debug(l_proc_name,15);
2567 END IF;
2568
2569 debug(l_proc_name,30);
2570
2571 l_formula_name := l_formula_name_prefix||
2572 'PARTICIPATION_ELIGIBILTY_PROFILE_OTHER_RULE';
2573 debug(l_formula_name);
2574
2575 FOR l_formula IN csr_get_formula_id(p_business_group_id,l_formula_name)
2576 LOOP
2577 debug(l_formula.formula_id);
2578 pqp_utilities.delete_formula
2579 (p_formula_id => l_formula.formula_id
2580 ,p_error_code => p_error_code
2581 ,p_error_message => p_error_message
2582 );
2583 debug(l_proc_name,35);
2584 END LOOP;
2585
2586 debug(l_proc_name,40);
2587
2588 l_formula_name := l_formula_name_prefix||
2589 'STANDARD_RATES_CALCULATION_METHOD_VALUE_RULE';
2590 debug(l_formula_name);
2591
2592 FOR l_formula IN csr_get_formula_id(p_business_group_id,l_formula_name)
2593 LOOP
2594 debug(l_formula.formula_id);
2595 pqp_utilities.delete_formula
2596 (p_formula_id => l_formula.formula_id
2597 ,p_error_code => p_error_code
2598 ,p_error_message => p_error_message
2599 );
2600 debug(l_proc_name,45);
2601 END LOOP;
2602
2603 debug(l_proc_name,50);
2604
2605 l_formula_name := l_formula_name_prefix||
2606 'STANDARD_RATES_GENERAL_EXTRA_INPUT_RULE';
2607 debug(l_formula_name);
2608
2609 FOR l_formula IN csr_get_formula_id(p_business_group_id,l_formula_name)
2610 LOOP
2611 debug(l_formula.formula_id);
2612 pqp_utilities.delete_formula
2613 (p_formula_id => l_formula.formula_id
2614 ,p_error_code => p_error_code
2615 ,p_error_message => p_error_message
2616 );
2617 debug(l_proc_name,55);
2618 END LOOP;
2619
2620
2621 END IF;-- if g_use_this_functionality then
2622
2623 debug_exit(l_proc_name);
2624
2625 EXCEPTION
2626
2627 WHEN OTHERS THEN
2628 debug(SQLCODE);
2629 debug(SQLERRM);
2630 debug(l_proc_name,-10);
2631 debug_exit(l_proc_name);
2632 RAISE;
2633 END delete_ben_formulas;
2634 --
2635 --
2636 --
2637 BEGIN
2638 g_use_this_functionality := FALSE;
2639 END pqp_gb_gap_ben_formulas;
[Home] [Help]