[Home] [Help]
PACKAGE BODY: APPS.HXC_FF_DICT
Source
4 g_debug boolean :=hr_utility.debug_enabled;
1 Package Body hxc_ff_dict as
2 /* $Header: hxcffpkg.pkb 120.5 2011/07/14 11:29:21 bbayragi ship $ */
3
5
6 function formula(
7 p_formula_id in number
8 , p_resource_id in number
9 , p_submission_date in date
10 , p_ss_timecard_hours in number
11 , p_period_start_date in date
12 , p_period_end_date in date
13 , p_db_pre_period_start in date
14 , p_db_pre_period_end in date
15 , p_db_post_period_start in date
16 , p_db_post_period_end in date
17 , p_db_ref_period_start in date
18 , p_db_ref_period_end in date
19 , p_duration_in_days in number
20 , p_param_rec in r_param )
21 return ff_exec.outputs_t is
22 --
23 l_inputs ff_exec.inputs_t;
24 l_outputs ff_exec.outputs_t;
25
26 l_proc varchar2(72);
27
28 l_assignment_id per_assignments_f.assignment_id%TYPE;
29
30 CURSOR csr_get_asg_id IS
31 SELECT asg.assignment_id
32 FROM per_assignments_f asg
33 WHERE asg.person_id = p_resource_id
34 AND p_submission_date BETWEEN
35 asg.effective_start_date AND asg.effective_end_date
36 AND asg.primary_flag = 'Y'
37 AND asg.assignment_type in ('E','C');
38
39 begin -- formula
40
41 g_debug:=hr_utility.debug_enabled;
42 if g_debug then
43 l_proc := g_package||'.formula';
44 hr_utility.set_location ('Entering '||l_proc,10);
45 hr_utility.set_location ('Before Init Formula '||l_proc,20);
46 end if;
47 ff_utils.set_debug(127);
48 ff_exec.init_formula
49 (p_formula_id => p_formula_id,
50 p_effective_date => p_submission_date,
51 p_inputs => l_inputs,
52 p_outputs => l_outputs);
53 if g_debug then
54 hr_utility.set_location ('After Init Formula '||l_proc,30);
55 end if;
56
57 -- check the cache for the assignment id
58 -- (should always be there now)
59
60 IF ( hxc_time_entry_rules_utils_pkg.g_assignment_info.EXISTS ( p_resource_id ) )
61 THEN
62
63 -- set assignment id from cache
64
65 l_assignment_id := hxc_time_entry_rules_utils_pkg.g_assignment_info(p_resource_id).assignment_id;
66
67 ELSE
68
69 OPEN csr_get_asg_id;
70 FETCH csr_get_asg_id INTO l_assignment_id;
71
72 IF csr_get_asg_id%NOTFOUND
73 THEN
74
75 CLOSE csr_get_asg_id;
76
77 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
78 fnd_message.set_token('PROCEDURE', l_proc);
79 fnd_message.set_token('STEP','assignment context');
80 fnd_message.raise_error;
81
82 END IF;
83
84 CLOSE csr_get_asg_id;
85
86 END IF;
87
88
89 -- NOTE that we use special parameter values in order to state which
90 -- array locations we put the values into, this is because of the caching
91 -- mechanism that formula uses.
92 --
93 if g_debug then
94 hr_utility.set_location ('First Position'||l_inputs.first,10);
95 hr_utility.set_location ('Last Position'||l_inputs.last,10);
96 end if;
97 --
98 -- Account for case where formula has no contexts or inputs
99 --
100 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
101 --
102 if g_debug then
103 hr_utility.set_location ('Current Context'||l_inputs(l_count).name,10);
104 end if;
105 -- *** CONTEXTS ****
106
107 if l_inputs(l_count).name = 'DATE_EARNED'
108 then
109
110 l_inputs(l_count).value := to_char(p_period_start_date, 'YYYY/MM/DD HH24:MI:SS');
111
112 elsif l_inputs(l_count).name = 'ASSIGNMENT_ID'
113 then
114
115 l_inputs(l_count).value := to_char(l_assignment_id);
116
117 -- *** INPUTS ****
118
119 elsif l_inputs(l_count).name = 'RESOURCE_ID' then
120
121 l_inputs(l_count).value := nvl(p_resource_id, -1);
122
126 --
123 elsif l_inputs(l_count).name = 'TIMECARD_HRS' then
124 --
125 l_inputs(l_count).value := p_ss_timecard_hours;
127 elsif l_inputs(l_count).name = 'SUBMISSION_DATE' then
128 --
129 -- Note that you must pass the date as a string, that is because
130 -- of the canonical date change of 11.5
131 --
132 if g_debug then
133 hr_utility.set_location ('Submission Date '||to_char(p_submission_date),10);
134 end if;
135 -- Still the fast formula does't accept the full canonical form.
136 -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_submission_date);
137 l_inputs(l_count).value := to_char(p_submission_date, 'YYYY/MM/DD HH24:MI:SS');
138
139 elsif l_inputs(l_count).name = 'PERIOD_START_DATE' then
140 l_inputs(l_count).value := to_char(p_period_start_date, 'YYYY/MM/DD HH24:MI:SS');
141
142 elsif l_inputs(l_count).name = 'PERIOD_END_DATE' then
143 l_inputs(l_count).value := to_char(p_period_end_date, 'YYYY/MM/DD HH24:MI:SS');
144
145 elsif l_inputs(l_count).name = 'DB_PRE_PERIOD_START' then
146 l_inputs(l_count).value := to_char(p_db_pre_period_start, 'YYYY/MM/DD HH24:MI:SS');
147
148 elsif l_inputs(l_count).name = 'DB_PRE_PERIOD_END' then
149 l_inputs(l_count).value := to_char(p_db_pre_period_end, 'YYYY/MM/DD HH24:MI:SS');
150
151 elsif l_inputs(l_count).name = 'DB_POST_PERIOD_START' then
152 l_inputs(l_count).value := to_char(p_db_post_period_start, 'YYYY/MM/DD HH24:MI:SS');
153
154 elsif l_inputs(l_count).name = 'DB_POST_PERIOD_END' then
155 l_inputs(l_count).value := to_char(p_db_post_period_end, 'YYYY/MM/DD HH24:MI:SS');
156
157 elsif l_inputs(l_count).name = 'DB_REF_PERIOD_START' then
158 l_inputs(l_count).value := to_char(p_db_ref_period_start, 'YYYY/MM/DD HH24:MI:SS');
159
160 elsif l_inputs(l_count).name = 'DB_REF_PERIOD_END' then
161 l_inputs(l_count).value := to_char(p_db_ref_period_end, 'YYYY/MM/DD HH24:MI:SS');
162
163 elsif l_inputs(l_count).name = 'DURATION_IN_DAYS' then
164 l_inputs(l_count).value := to_char(p_duration_in_days);
165
166 elsif l_inputs(l_count).name = 'DATE_EARNED' then
167 l_inputs(l_count).value := to_char(p_submission_date, 'YYYY/MM/DD HH24:MI:SS');
168
169 elsif l_inputs(l_count).name = p_param_rec.param1 then
170 --
171 l_inputs(l_count).value := p_param_rec.param1_value;
172 --
173 elsif l_inputs(l_count).name = p_param_rec.param2 then
174 --
175 l_inputs(l_count).value := p_param_rec.param2_value;
176 --
177 elsif l_inputs(l_count).name = p_param_rec.param3 then
178 --
179 l_inputs(l_count).value := p_param_rec.param3_value;
180 --
181 elsif l_inputs(l_count).name = p_param_rec.param4 then
182 --
183 l_inputs(l_count).value := p_param_rec.param4_value;
184 --
185 elsif l_inputs(l_count).name = p_param_rec.param5 then
186 --
187 l_inputs(l_count).value := p_param_rec.param5_value;
188 --
189 elsif l_inputs(l_count).name = p_param_rec.param6 then
190 --
191 l_inputs(l_count).value := p_param_rec.param6_value;
192 --
193 elsif l_inputs(l_count).name = p_param_rec.param7 then
194 --
195 l_inputs(l_count).value := p_param_rec.param7_value;
196 --
197 elsif l_inputs(l_count).name = p_param_rec.param8 then
198 --
199 l_inputs(l_count).value := p_param_rec.param8_value;
200 --
201 elsif l_inputs(l_count).name = p_param_rec.param9 then
202 --
203 l_inputs(l_count).value := p_param_rec.param9_value;
204 --
205 elsif l_inputs(l_count).name = p_param_rec.param10 then
206 --
207 l_inputs(l_count).value := p_param_rec.param10_value;
208 --
209 elsif l_inputs(l_count).name = p_param_rec.param11 then
210 --
211 l_inputs(l_count).value := p_param_rec.param11_value;
212 --
213 elsif l_inputs(l_count).name = p_param_rec.param12 then
214 --
215 l_inputs(l_count).value := p_param_rec.param12_value;
216 --
217 elsif l_inputs(l_count).name = p_param_rec.param13 then
218 --
219 l_inputs(l_count).value := p_param_rec.param13_value;
220 --
221 elsif l_inputs(l_count).name = p_param_rec.param14 then
222 --
223 l_inputs(l_count).value := p_param_rec.param14_value;
224 --
225 elsif l_inputs(l_count).name = p_param_rec.param15 then
226 --
227 l_inputs(l_count).value := p_param_rec.param15_value;
228 --
229 end if;
230 --
231 end loop;
232 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
233 --
234 if g_debug then
235 hr_utility.set_location ('GAZ Current Context: '||l_inputs(l_count).name||' Value: '||l_inputs(l_count).value ,999);
236 end if;
237 --
238 END LOOP;
239 --
240 -- Ok we have loaded the input record now run the formula.
241 --
242 ff_utils.set_debug(127);
243 ff_exec.run_formula(p_inputs => l_inputs,
244 p_outputs => l_outputs);
245 --
246 if g_debug then
247 hr_utility.set_location ('Leaving '||l_proc,10);
248 end if;
249 for l_count in nvl(l_outputs.first,0)..nvl(l_outputs.last,-1) loop
250 --
251 if g_debug then
252 hr_utility.set_location ('GAZ Current Context: '||l_outputs(l_count).name||' Value: '||l_outputs(l_count).value ,999);
253 end if;
254 --
255 END LOOP;
256 return l_outputs;
257
258 EXCEPTION WHEN OTHERS THEN
259
260 if g_debug then
261 hr_utility.trace('gazza - error is '||SQLERRM);
262 end if;
263
267 end formula;
264 raise_application_error(-20000,'ORA'||sqlcode||':'||sqlerrm);
265
266
268
269
270 PROCEDURE decode_formula_segments (
271 p_formula_name VARCHAR2
272 , p_rule_rec hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
273 , p_param_rec IN OUT NOCOPY r_param
274 , p_period_value IN OUT NOCOPY NUMBER
275 , p_reference_value IN OUT NOCOPY NUMBER
276 , p_consider_zero_hours IN OUT NOCOPY VARCHAR2 )
277 IS
278
279 CURSOR csr_get_flex_segments ( p_formula_name ff_formulas_f.formula_name%TYPE ) IS
280 SELECT df.end_user_column_name
281 , df.application_column_name
282 FROM
283 fnd_descr_Flex_Column_usages df
284 WHERE
285 df.application_id = 809 AND
286 df.descriptive_flexfield_name = 'OTL Formulas' AND
287 df.descriptive_flex_context_code= p_formula_name
288 ORDER BY
289 df.application_column_name;
290
291 l_proc VARCHAR2(72);
292
293
294
295 PROCEDURE set_pto_time_category ( p_accrual_plan_id NUMBER ) IS
296
297 -- Modified for bug 12426197
298 CURSOR csr_get_time_category_id IS
299 SELECT htc.time_category_id
300 , htc.time_category_name
301 FROM hxc_time_categories htc
302 , pay_accrual_plans pap
303 WHERE
304 pap.accrual_plan_id = p_accrual_plan_id
305 AND (
306 ( htc.time_category_name = SUBSTR('OTL_DEC_'||pap.accrual_plan_name,1,90))
307 OR
308 ( htc.time_category_name = SUBSTR('OTL_INC_'||pap.accrual_plan_name,1,90))
309 );
310
311 l_tc_id NUMBER(15);
312 l_tc_name VARCHAR2(90);
313 l_proc VARCHAR2(72);
314
315 BEGIN
316
317
318
319 if g_debug then
320 l_proc := g_package||'.set_pto_time_category';
321 hr_utility.set_location('Processing '||l_proc, 10);
322 end if;
323
324 OPEN csr_get_time_category_id;
325 FETCH csr_get_time_category_id INTO l_tc_id, l_tc_name;
326
327 IF ( csr_get_time_category_id%NOTFOUND )
328 THEN
329
330 CLOSE csr_get_time_category_id;
331
332 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
333 fnd_message.set_token('PROCEDURE', l_proc);
334 fnd_message.set_token('STEP','PTO Time Category');
335 fnd_message.raise_error;
336
337 END IF;
338
339 WHILE csr_get_time_category_id%FOUND
340 LOOP
341
342 IF ( l_tc_name like 'OTL_DEC_%' )
343 THEN
344
345 hxc_time_category_utils_pkg.g_time_category_id := l_tc_id;
346
347 ELSIF ( l_tc_name like 'OTL_INC_%' )
348 THEN
349
350 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id := l_tc_id;
351
352 END IF;
353
354 FETCH csr_get_time_category_id INTO l_tc_id, l_tc_name;
355
356 END LOOP;
357
358 CLOSE csr_get_time_category_id;
359
360 END set_pto_time_category;
361
362
363 BEGIN
364
365 g_debug:=hr_utility.debug_enabled;
366 if g_debug then
367 l_proc:= g_package||'.decode_formula_segments';
368 hr_utility.set_location('Entering '||l_proc, 10);
369 end if;
370
371 -- initialise g_time_category_id
372
373 hxc_time_category_utils_pkg.g_time_category_id := NULL;
374
375 FOR r_seg IN csr_get_flex_segments ( p_formula_name => p_formula_name )
376 LOOP
377
378 if g_debug then
379 hr_utility.set_location('Processing '||l_proc, 10);
380 end if;
381
382 IF ( r_seg.application_column_name = 'ATTRIBUTE1' )
383 THEN
384 if g_debug then
385 hr_utility.trace('');
386 hr_utility.trace('attribute 1 param is '||r_seg.end_user_column_name);
387 hr_utility.trace('attribute 1 param value is '||p_rule_rec.attribute1);
388 end if;
389
390 p_param_rec.param1_value := p_rule_rec.attribute1;
391 p_param_rec.param1 := UPPER(r_seg.end_user_column_name);
392
393 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
394 THEN
395 p_period_value := TO_NUMBER(p_rule_rec.attribute1);
396
397 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
398 THEN
399 p_reference_value := TO_NUMBER(p_rule_rec.attribute1);
400
401 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
402 THEN
403 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute1);
404
405 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
406 THEN
407 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute1) );
408
409 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
410 THEN
411 p_consider_zero_hours := NVL(p_rule_rec.attribute1, 'Y');
412
413 END IF;
414
415 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE2' )
416 THEN
417 if g_debug then
418 hr_utility.trace('');
419 hr_utility.trace('attribute 2 param is '||r_seg.end_user_column_name);
420 hr_utility.trace('attribute 2 param value is '||p_rule_rec.attribute2);
421 end if;
422
423 p_param_rec.param2_value := p_rule_rec.attribute2;
424 p_param_rec.param2 := UPPER(r_seg.end_user_column_name);
425
426 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
427 THEN
428 p_period_value := TO_NUMBER(p_rule_rec.attribute2);
429
430 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
431 THEN
432 p_reference_value := TO_NUMBER(p_rule_rec.attribute2);
433
437
434 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
435 THEN
436 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute2);
438 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
439 THEN
440 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute2) );
441
442 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
443 THEN
444 p_consider_zero_hours := NVL(p_rule_rec.attribute2, 'Y');
445
446 END IF;
447
448 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE3' )
449 THEN
450 if g_debug then
451 hr_utility.trace('');
452 hr_utility.trace('attribute 3 param is '||r_seg.end_user_column_name);
453 hr_utility.trace('attribute 3 param value is '||p_rule_rec.attribute3);
454 end if;
455
456 p_param_rec.param3_value := p_rule_rec.attribute3;
457 p_param_rec.param3 := UPPER(r_seg.end_user_column_name);
458
459 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
460 THEN
461 p_period_value := TO_NUMBER(p_rule_rec.attribute3);
462
463 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
464 THEN
465 p_reference_value := TO_NUMBER(p_rule_rec.attribute3);
466
467 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
468 THEN
469 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute3);
470
471 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
472 THEN
473 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute3) );
474
475 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
476 THEN
477 p_consider_zero_hours := NVL(p_rule_rec.attribute3, 'Y');
478
479 END IF;
480
481 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE4' )
482 THEN
483 p_param_rec.param4_value := p_rule_rec.attribute4;
484 p_param_rec.param4 := r_seg.end_user_column_name;
485
486 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
487 THEN
488 p_period_value := TO_NUMBER(p_rule_rec.attribute4);
489
490 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
491 THEN
492 p_reference_value := TO_NUMBER(p_rule_rec.attribute4);
493
494 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
495 THEN
496 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute4);
497
498 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
499 THEN
500 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute4) );
501
502 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
503 THEN
504 p_consider_zero_hours := NVL(p_rule_rec.attribute4, 'Y');
505
506 END IF;
507
508 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE5' )
509 THEN
510 p_param_rec.param5_value := p_rule_rec.attribute5;
511 p_param_rec.param5 := r_seg.end_user_column_name;
512
513 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
514 THEN
515 p_period_value := TO_NUMBER(p_rule_rec.attribute5);
516
517 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
518 THEN
519 p_reference_value := TO_NUMBER(p_rule_rec.attribute5);
520
521 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
522 THEN
523 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute5);
524
525 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
526 THEN
527 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute5) );
528
529 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
530 THEN
531 p_consider_zero_hours := NVL(p_rule_rec.attribute5, 'Y');
532
533 END IF;
534
535 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE6' )
536 THEN
537 p_param_rec.param6_value := p_rule_rec.attribute6;
538 p_param_rec.param6 := r_seg.end_user_column_name;
539
540 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
541 THEN
542 p_period_value := TO_NUMBER(p_rule_rec.attribute6);
543
544 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
545 THEN
546 p_reference_value := TO_NUMBER(p_rule_rec.attribute6);
547
548 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
549 THEN
550 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute6);
551
552 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
553 THEN
554 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute6) );
555
556 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
557 THEN
558 p_consider_zero_hours := NVL(p_rule_rec.attribute6, 'Y');
559
560 END IF;
561
562 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE7' )
563 THEN
564 p_param_rec.param7_value := p_rule_rec.attribute7;
565 p_param_rec.param7 := r_seg.end_user_column_name;
566
567 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
568 THEN
569 p_period_value := TO_NUMBER(p_rule_rec.attribute7);
570
571 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
572 THEN
573 p_reference_value := TO_NUMBER(p_rule_rec.attribute7);
574
575 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
576 THEN
577 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute7);
578
579 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
580 THEN
584 THEN
581 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute7) );
582
583 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
585 p_consider_zero_hours := NVL(p_rule_rec.attribute7, 'Y');
586
587 END IF;
588
589 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE8' )
590 THEN
591 p_param_rec.param8_value := p_rule_rec.attribute8;
592 p_param_rec.param8 := r_seg.end_user_column_name;
593
594 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
595 THEN
596 p_period_value := TO_NUMBER(p_rule_rec.attribute8);
597
598 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
599 THEN
600 p_reference_value := TO_NUMBER(p_rule_rec.attribute8);
601
602 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
603 THEN
604 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute8);
605
606 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
607 THEN
608 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute8) );
609
610 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
611 THEN
612 p_consider_zero_hours := NVL(p_rule_rec.attribute8, 'Y');
613
614 END IF;
615
616 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE9' )
617 THEN
618 p_param_rec.param9_value := p_rule_rec.attribute9;
619 p_param_rec.param9 := r_seg.end_user_column_name;
620
621 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
622 THEN
623 p_period_value := TO_NUMBER(p_rule_rec.attribute9);
624
625 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
626 THEN
627 p_reference_value := TO_NUMBER(p_rule_rec.attribute9);
628
629 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
630 THEN
631 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute9);
632
633 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
634 THEN
635 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute9) );
636
637 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
638 THEN
639 p_consider_zero_hours := NVL(p_rule_rec.attribute9, 'Y');
640
641 END IF;
642
643 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE10' )
644 THEN
645 p_param_rec.param10_value := p_rule_rec.attribute10;
646 p_param_rec.param10 := r_seg.end_user_column_name;
647
648 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
649 THEN
650 p_period_value := TO_NUMBER(p_rule_rec.attribute10);
651
652 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
653 THEN
654 p_reference_value := TO_NUMBER(p_rule_rec.attribute10);
655
656 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
657 THEN
658 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute10);
659
660 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
661 THEN
662 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute10) );
663
664 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
665 THEN
666 p_consider_zero_hours := NVL(p_rule_rec.attribute10, 'Y');
667
668 END IF;
669
670 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE11' )
671 THEN
672 p_param_rec.param11_value := p_rule_rec.attribute11;
673 p_param_rec.param11 := r_seg.end_user_column_name;
674
675 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
676 THEN
677 p_period_value := TO_NUMBER(p_rule_rec.attribute11);
678
679 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
680 THEN
681 p_reference_value := TO_NUMBER(p_rule_rec.attribute11);
682
683 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
684 THEN
685 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute11);
686
687 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
688 THEN
689 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute11) );
690
691 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
692 THEN
693 p_consider_zero_hours := NVL(p_rule_rec.attribute11, 'Y');
694
695 END IF;
696
697 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE12' )
698 THEN
699 p_param_rec.param12_value := p_rule_rec.attribute12;
700 p_param_rec.param12 := r_seg.end_user_column_name;
701
702 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
703 THEN
704 p_period_value := TO_NUMBER(p_rule_rec.attribute12);
705
706 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
707 THEN
708 p_reference_value := TO_NUMBER(p_rule_rec.attribute12);
709
710 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
711 THEN
712 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute12);
713
714 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
715 THEN
716 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute12) );
717
718 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
719 THEN
720 p_consider_zero_hours := NVL(p_rule_rec.attribute12, 'Y');
721
725 THEN
722 END IF;
723
724 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE13' )
726 p_param_rec.param13_value := p_rule_rec.attribute13;
727 p_param_rec.param13 := r_seg.end_user_column_name;
728
729 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
730 THEN
731 p_period_value := TO_NUMBER(p_rule_rec.attribute13);
732
733 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
734 THEN
738 THEN
735 p_reference_value := TO_NUMBER(p_rule_rec.attribute13);
736
737 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
739 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute13);
740
741 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
742 THEN
743 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute13) );
744
745 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
746 THEN
747 p_consider_zero_hours := NVL(p_rule_rec.attribute13, 'Y');
748
749 END IF;
750
751 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE14' )
752 THEN
753 p_param_rec.param14_value := p_rule_rec.attribute14;
754 p_param_rec.param14 := r_seg.end_user_column_name;
755
756 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
757 THEN
758 p_period_value := TO_NUMBER(p_rule_rec.attribute14);
759
760 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
761 THEN
762 p_reference_value := TO_NUMBER(p_rule_rec.attribute14);
763
764 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
765 THEN
766 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute14);
767
768 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
769 THEN
770 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute14) );
771
772 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
773 THEN
774 p_consider_zero_hours := NVL(p_rule_rec.attribute14, 'Y');
775
776 END IF;
777
778 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE15' )
779 THEN
780 p_param_rec.param15_value := p_rule_rec.attribute15;
781 p_param_rec.param15 := r_seg.end_user_column_name;
782
783 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
784 THEN
785 p_period_value := TO_NUMBER(p_rule_rec.attribute15);
786
787 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
788 THEN
789 p_reference_value := TO_NUMBER(p_rule_rec.attribute15);
790
791 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
792 THEN
793 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute15);
794
795 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
796 THEN
797 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute15) );
798
799 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
800 THEN
801 p_consider_zero_hours := NVL(p_rule_rec.attribute15, 'Y');
802
803 END IF;
804
805 ELSE
806 fnd_message.set_name('HXC', 'HXC_WTD_INVALID_FORMULA_DDF');
807 fnd_message.set_token('FORMULA', p_rule_rec.formula_name);
808 fnd_message.raise_error;
809 END IF;
810
811 END LOOP; -- csr_get_flex_segments
812
813 if g_debug then
814 hr_utility.set_location('Leaving '||l_proc, 20);
815 end if;
816
817
818 END decode_formula_segments;
819
820
821
822 FUNCTION execute_approval_formula (
823 p_resource_id NUMBER
824 , p_period_start_date DATE
825 , p_period_end_date DATE
826 , p_tc_period_start_date DATE
827 , p_tc_period_end_date DATE
828 , p_rule_rec hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
829 , p_message_table IN OUT NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE )
830 RETURN varchar2 IS
831
832 l_proc VARCHAR2(72);
833
834 l_result VARCHAR2(1);
835 l_param_rec r_param;
836 l_outputs ff_exec.outputs_t;
837
838 l_timecard_hrs NUMBER := 0;
839 l_reference_period NUMBER(10);
840 l_consider_zero_hours VARCHAR2(10);
841 l_period_id NUMBER(15);
842 l_period_tab hxc_time_entry_rules_utils_pkg.t_period;
843
844 l_period_start DATE;
845 l_period_start_date DATE;
846 l_period_end_date DATE;
847 l_period_type hxc_recurring_periods.period_type%TYPE;
848 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
849
850 -- GPM v115.19
851
855 SELECT MIN( asg.effective_start_date)
852 l_submission_date DATE;
853
854 CURSOR csr_get_first_asg_date ( p_resource_id NUMBER ) IS
856 FROM per_assignments_f asg
857 WHERE asg.person_id = p_resource_id
858 AND asg.primary_flag = 'Y'
859 AND asg.assignment_type in ('E','C');
860
861
862
863 FUNCTION fix_periods ( p_period_tab hxc_time_entry_rules_utils_pkg.t_period )
864 RETURN hxc_time_entry_rules_utils_pkg.t_period IS
865
866 l_period_tab hxc_time_entry_rules_utils_pkg.t_period;
867
868 BEGIN
869
870 l_period_tab := p_period_tab;
871
872 FOR x IN l_period_tab.FIRST .. l_period_tab.LAST
873 LOOP
874
875 IF ( l_period_tab(x).db_pre_period_start IS NULL AND
876 l_period_tab(x).db_pre_period_end IS NULL AND
877 l_period_tab(x).db_post_period_start IS NULL AND
878 l_period_tab(x).db_post_period_end IS NULL )
879 THEN
880
881 l_period_tab(x).db_pre_period_start := l_period_tab(x).period_start;
882 l_period_tab(x).db_pre_period_end := l_period_tab(x).period_end;
883
884 l_period_tab(x).db_post_period_start := NULL;
885 l_period_tab(x).db_post_period_end := NULL;
886 l_period_tab(x).period_start := NULL;
887 l_period_tab(x).period_end := NULL;
888
889 ELSIF ( l_period_tab(x).db_pre_period_start IS NULL AND
890 l_period_tab(x).db_pre_period_end IS NULL )
891 THEN
892
893 l_period_tab(x).db_pre_period_start := l_period_tab(x).period_start;
894 l_period_tab(x).db_pre_period_end := l_period_tab(x).db_post_period_end;
895
896 l_period_tab(x).db_post_period_start := NULL;
897 l_period_tab(x).db_post_period_end := NULL;
898 l_period_tab(x).period_start := NULL;
899 l_period_tab(x).period_end := NULL;
900
901 ELSIF ( l_period_tab(x).db_post_period_start IS NULL AND
902 l_period_tab(x).db_post_period_end IS NULL )
903 THEN
904
905 l_period_tab(x).db_pre_period_end := l_period_tab(x).period_end;
906
907 l_period_tab(x).db_post_period_start := NULL;
908 l_period_tab(x).db_post_period_end := NULL;
909 l_period_tab(x).period_start := NULL;
910 l_period_tab(x).period_end := NULL;
911
912 ELSE
913
914 l_period_tab(x).db_pre_period_end := l_period_tab(x).db_post_period_end;
915
916 l_period_tab(x).db_post_period_start := NULL;
917 l_period_tab(x).db_post_period_end := NULL;
918 l_period_tab(x).period_start := NULL;
919 l_period_tab(x).period_end := NULL;
920
921 END IF;
922
926 hr_utility.trace(' TC period start is :'||TO_CHAR(l_period_tab(x).period_start, 'DD-MON-YY HH24:MI:SS'));
923 if g_debug then
924 hr_utility.trace('');
925 hr_utility.trace(' ********** Fix Periods ************** ');
927 hr_utility.trace(' TC period end is :'||TO_CHAR(l_period_tab(x).period_end, 'DD-MON-YY HH24:MI:SS'));
928 hr_utility.trace(' pre TC period start is :'||TO_CHAR(l_period_tab(x).db_pre_period_start, 'DD-MON-YY HH24:MI:SS'));
929 hr_utility.trace(' pre TC period end is :'||TO_CHAR(l_period_tab(x).db_pre_period_end, 'DD-MON-YY HH24:MI:SS'));
930 hr_utility.trace(' post TC period start is:'||TO_CHAR(l_period_tab(x).db_post_period_start, 'DD-MON-YY HH24:MI:SS'));
931 hr_utility.trace(' post TC period end is :'||TO_CHAR(l_period_tab(x).db_post_period_end, 'DD-MON-YY HH24:MI:SS'));
932 end if;
933
934 END LOOP;
935
936 RETURN l_period_tab;
937
938 END fix_periods;
939
940 BEGIN
941
942 g_debug:=hr_utility.debug_enabled;
943 if g_debug then
944 l_proc := g_package||'.execute_approval_formula';
945 hr_utility.set_location('Entering '||l_proc, 10);
946 end if;
947
948 decode_formula_segments (
949 p_formula_name => p_rule_rec.formula_name
950 , p_rule_rec => p_rule_rec
951 , p_param_rec => l_param_rec
952 , p_period_value => l_period_id
953 , p_reference_value => l_reference_period
954 , p_consider_zero_hours => l_consider_zero_hours );
955
956 if g_debug then
957 hr_utility.set_location('Processing '||l_proc, 30);
958 end if;
959
960 IF ( l_period_id IS NULL )
961 THEN
962
963 if g_debug then
964 hr_utility.set_location('Processing '||l_proc, 40);
965 end if;
966
967
968 -- no period id entered via TER thus use application period
969
973 hr_utility.trace('Application approval period start is '||TO_CHAR(p_period_start_date, 'DD-MON-YY HH24:MI:SS'));
970 l_period_end_date := TO_DATE(TO_CHAR(p_period_end_date, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
971
972 if g_debug then
974 hr_utility.trace('Application approval period end is ' ||TO_CHAR(l_period_end_date, 'DD-MON-YY HH24:MI:SS'));
975 end if;
976
977 hxc_time_entry_rules_utils_pkg.calc_timecard_periods (
978 p_timecard_period_start => p_tc_period_start_date
979 , p_timecard_period_end => p_tc_period_end_date
980 , p_period_start_date => p_period_start_date
981 , p_period_end_date => l_period_end_date
982 , p_duration_in_days => 100 -- arbitrary
983 , p_periods_tab => l_period_tab );
984
985 ELSE -- user entered period via TER thus override application period
986
987 if g_debug then
988 hr_utility.set_location('Processing '||l_proc, 50);
989 end if;
990
991 /* NOTE: This code pulled from hxc_time_Entry_rules_utils_pkg */
992
993
994 OPEN hxc_time_entry_rules_utils_pkg.csr_get_period_info ( p_recurring_period_id => l_period_id );
995 FETCH hxc_time_entry_rules_utils_pkg.csr_get_period_info INTO l_period_type, l_duration_in_days, l_period_start;
996 CLOSE hxc_time_entry_rules_utils_pkg.csr_get_period_info;
997
998 if g_debug then
999 hr_utility.trace('');
1000 hr_utility.trace('*********** Period Info ************');
1001 hr_utility.trace('period type is '||l_period_type);
1002 hr_utility.trace('duration in days is '||TO_CHAR(l_duration_in_days));
1003 hr_utility.trace('period start date is '||TO_CHAR(l_period_start,'DD-MON-YY HH24:MI:SS'));
1004 end if;
1005
1006 -- gaz - remove this when function changed
1007
1008 IF ( UPPER(l_period_type) = 'WEEK' )
1009 THEN
1010 l_duration_in_days := 7;
1011
1012 ELSIF( UPPER(l_period_type) = 'BI-WEEK')
1013 THEN
1014 l_duration_in_days := 14;
1015 END IF;
1016
1017 IF ( l_duration_in_days IS NOT NULL )
1018 THEN
1019
1020 l_period_start_date := l_period_start +
1021 (l_duration_in_days *
1022 FLOOR(((p_tc_period_start_date - l_period_start)/l_duration_in_days)));
1023
1024 l_period_end_date := l_period_start_date + l_duration_in_days - 1;
1025
1026 ELSE
1027
1028 -- Call application specific function to generate the period
1029 -- start and end dates from the period type.
1030
1031 hr_generic_util.get_period_dates
1032 (p_rec_period_start_date => l_period_start
1033 ,p_period_type => l_period_type
1034 ,p_current_date => p_tc_period_start_date
1035 ,p_period_start_date => l_period_start_date
1036 ,p_period_end_date => l_period_end_date);
1037
1038 l_duration_in_days := ( l_period_end_date - l_period_start_date ) + 1;
1039
1040 END IF;
1041
1042 -- now add time component to l_period_end
1043
1044 l_period_end_date := TO_DATE(TO_CHAR(l_period_end_date, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1045
1046 if g_debug then
1047 Hr_utility.trace('');
1048 hr_utility.trace('*********** Period Start and End ************');
1049 hr_utility.trace('period start date is '||TO_CHAR(l_period_start_date,'DD-MON-YY HH24:MI:SS'));
1050 hr_utility.trace('period end date is '||TO_CHAR(l_period_end_date,'DD-MON-YY HH24:MI:SS'));
1051 hr_utility.trace('duration in days is '||TO_CHAR(l_duration_in_days));
1052 end if;
1053
1054 -- now build up table of time entry rule periods that the timecard
1055 -- may span
1056
1057 hxc_time_entry_rules_utils_pkg.calc_timecard_periods (
1058 p_timecard_period_start => p_tc_period_start_date
1059 , p_timecard_period_end => p_tc_period_end_date
1060 , p_period_start_date => l_period_start_date
1061 , p_period_end_date => l_period_end_date
1062 , p_duration_in_days => l_duration_in_days
1063 , p_periods_tab => l_period_tab );
1064
1065 END IF; -- ( l_period_id IS NOT NULL )
1066
1067 if g_debug then
1068 hr_utility.set_location('Processing '||l_proc, 60);
1069 end if;
1070
1071 -- set submission date to be within valid assignment
1072
1073 OPEN csr_get_first_asg_date ( p_resource_id );
1074 FETCH csr_get_first_asg_date INTO l_submission_date;
1075 CLOSE csr_get_first_asg_date;
1076
1077 -- Since we are re-using code from TER which assumes we calculated
1078 -- hours from the TC we need to manipulate the table of periods
1079 -- to calculate all hours from the database.
1080
1081 l_period_tab := fix_periods ( l_period_tab );
1082
1083 -- now loop through the periods to execute the formula
1084
1085 FOR p IN l_period_tab.FIRST .. l_period_tab.LAST
1086 LOOP
1087
1088 if g_debug then
1089 hr_utility.set_location('Processing '||l_proc, 70);
1090 end if;
1091
1092 l_timecard_hrs := 0;
1093
1094 if g_debug then
1095 hr_utility.set_location('Processing '||l_proc, 80);
1096 end if;
1097
1101 hr_utility.trace('Before call to execute formula');
1098 -- Execute the formula
1099
1100 if g_debug then
1102 end if;
1103
1104 -- GPM v115.19
1105 -- take the greater of the first assignment date or the period start date
1106
1107 l_submission_date := GREATEST( l_submission_date, l_period_tab(p).db_pre_period_start );
1108
1109 l_outputs := hxc_ff_dict.formula(
1110 p_formula_id => p_rule_rec.formula_id
1111 , p_resource_id => p_resource_id
1112 , p_submission_date => l_submission_date -- GPM v115.19
1113 , p_ss_timecard_hours => l_timecard_hrs
1114 , p_db_pre_period_start => l_period_tab(p).db_pre_period_start
1115 , p_db_pre_period_end => l_period_tab(p).db_pre_period_end
1116 , p_db_post_period_start => l_period_tab(p).db_post_period_start
1117 , p_db_post_period_end => l_period_tab(p).db_post_period_end
1118 , p_db_ref_period_start => l_period_tab(p).db_ref_period_start
1119 , p_db_ref_period_end => l_period_tab(p).db_ref_period_end
1120 , p_duration_in_days => 1
1121 , p_param_rec => l_param_rec );
1122
1123 if g_debug then
1124 hr_utility.set_location('Processing '||l_proc, 100);
1125 end if;
1126
1127 --
1128 if g_debug then
1129 hr_utility.trace('After call to execute formula');
1130 end if;
1131 --
1132 -- Analyze the outputs
1133 --
1134 FOR l_count IN l_outputs.FIRST .. l_outputs.LAST
1135 LOOP
1136 --
1137 IF (l_outputs(l_count).name = 'TO_APPROVE') THEN
1138
1139 if g_debug then
1140 hr_utility.set_location('Processing '||l_proc, 110);
1141 end if;
1142
1143 l_result := l_outputs(l_count).value;
1144
1145 ELSIF ( l_outputs(l_count).name = 'RULE_STATUS' )
1146 THEN
1147
1148 -- since time entry rule formulas can potentially also be used
1149 -- in approvals translate the time entry rule return
1150 -- value to a value the approvals code can
1151 -- understand. RULE_STATUS=E is an exception in the
1152 -- time entry rule world thus is to approve = Y
1153
1154 IF ( l_outputs(l_count).value = 'E' )
1155 THEN
1156 l_result := 'Y';
1157 ELSE
1158 l_result := 'N';
1159 END IF;
1160
1161 END IF;
1162
1163 END LOOP; -- formula outputs loop
1164
1165 -- in the case where we execute the formula many times
1166 -- exit when the result is Y
1167
1168 IF ( l_result = 'Y' )
1169 THEN
1170 EXIT;
1171 END IF;
1172
1173 IF ( p = 1 AND l_period_id IS NULL )
1174 THEN
1175 if g_debug then
1176 hr_utility.set_location('Processing '||l_proc, 90);
1177 end if;
1178
1179 -- remember only want to do this once for the
1180 -- application approval period
1181
1182 EXIT;
1183 END IF;
1184
1185 END LOOP; -- period loop
1186
1187 if g_debug then
1188 hr_utility.set_location('Processing '||l_proc, 120);
1189 end if;
1190
1191
1192 -- we used to populate the message table here but the approval
1193 -- process does not currently support ANY message being returned
1194 -- for the formulas executed in the date interdependcy rules
1195
1196 if g_debug then
1197 hr_utility.set_location('Processing '||l_proc, 130);
1198 end if;
1199
1200 if g_debug then
1201 hr_utility.trace('l result is '||l_result);
1202 end if;
1203
1204 RETURN l_result;
1205
1206 EXCEPTION WHEN OTHERS THEN
1207
1208 if g_debug then
1209 hr_utility.trace('In exception : '||SQLERRM);
1210 end if;
1211
1212 raise;
1213
1214 END execute_approval_formula;
1215
1216 FUNCTION get_formula_segment_value (
1217 p_param_rec r_param
1218 , p_param fnd_descr_flex_column_usages.end_user_column_name%TYPE ) RETURN hxc_time_entry_rules.attribute1%TYPE IS
1219
1220 l_proc varchar2(72) := g_package||'.get_formula_segment_value';
1221 l_param_value hxc_time_entry_rules.attribute1%TYPE;
1222
1223 BEGIN
1224
1225 IF ( p_param = p_param_rec.param1 )
1226 THEN
1227 l_param_value := p_param_rec.param1_value;
1228
1229 ELSIF ( p_param = p_param_rec.param2 )
1230 THEN
1231 l_param_value := p_param_rec.param2_value;
1232
1233 ELSIF ( p_param = p_param_rec.param3 )
1234 THEN
1235 l_param_value := p_param_rec.param3_value;
1236
1237 ELSIF ( p_param = p_param_rec.param4 )
1238 THEN
1239 l_param_value := p_param_rec.param4_value;
1240
1241 ELSIF ( p_param = p_param_rec.param5 )
1242 THEN
1243 l_param_value := p_param_rec.param5_value;
1244
1245 ELSIF ( p_param = p_param_rec.param6 )
1246 THEN
1247 l_param_value := p_param_rec.param6_value;
1248
1249 ELSIF ( p_param = p_param_rec.param7 )
1250 THEN
1251 l_param_value := p_param_rec.param7_value;
1252
1253 ELSIF ( p_param = p_param_rec.param8 )
1254 THEN
1255 l_param_value := p_param_rec.param8_value;
1256
1257 ELSIF ( p_param = p_param_rec.param9 )
1258 THEN
1259 l_param_value := p_param_rec.param9_value;
1260
1261 ELSIF ( p_param = p_param_rec.param10 )
1262 THEN
1263 l_param_value := p_param_rec.param10_value;
1264
1265 ELSIF ( p_param = p_param_rec.param11 )
1266 THEN
1267 l_param_value := p_param_rec.param11_value;
1268
1269 ELSIF ( p_param = p_param_rec.param12 )
1270 THEN
1271 l_param_value := p_param_rec.param12_value;
1272
1273 ELSIF ( p_param = p_param_rec.param13 )
1274 THEN
1275 l_param_value := p_param_rec.param13_value;
1276
1277 ELSIF ( p_param = p_param_rec.param14 )
1278 THEN
1279 l_param_value := p_param_rec.param14_value;
1280
1281 ELSIF ( p_param = p_param_rec.param15 )
1282 THEN
1283 l_param_value := p_param_rec.param15_value;
1284
1285 ELSE
1286
1287 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1288 fnd_message.set_token('PROCEDURE', l_proc);
1289 fnd_message.set_token('STEP','Invalid formula param name');
1290 fnd_message.raise_error;
1291
1292 END IF;
1293
1294 RETURN l_param_value;
1295
1296 END get_formula_segment_value;
1297
1298
1299 end hxc_ff_dict;