[Home] [Help]
PACKAGE BODY: APPS.HXC_FF_DICT
Source
1 Package Body hxc_ff_dict as
2 /* $Header: hxcffpkg.pkb 120.4 2005/09/23 08:08:37 sechandr noship $ */
3
4 g_debug boolean :=hr_utility.debug_enabled;
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
123 elsif l_inputs(l_count).name = 'TIMECARD_HRS' then
124 --
125 l_inputs(l_count).value := p_ss_timecard_hours;
126 --
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
264 raise_application_error(-20000,'ORA'||sqlcode||':'||sqlerrm);
265
266
267 end formula;
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 CURSOR csr_get_time_category_id IS
298 SELECT htc.time_category_id
299 , htc.time_category_name
300 FROM hxc_time_categories htc
301 , pay_accrual_plans pap
302 WHERE
303 pap.accrual_plan_id = p_accrual_plan_id
304 AND (
305 ( htc.time_category_name like SUBSTR('OTL_DEC_'||pap.accrual_plan_name,1,90)||'%' )
306 OR
307 ( htc.time_category_name like SUBSTR('OTL_INC_'||pap.accrual_plan_name,1,90)||'%' )
308 );
309
310 l_tc_id NUMBER(15);
311 l_tc_name VARCHAR2(90);
312 l_proc VARCHAR2(72);
313
314 BEGIN
315
316
317
318 if g_debug then
319 l_proc := g_package||'.set_pto_time_category';
320 hr_utility.set_location('Processing '||l_proc, 10);
321 end if;
322
323 OPEN csr_get_time_category_id;
324 FETCH csr_get_time_category_id INTO l_tc_id, l_tc_name;
325
326 IF ( csr_get_time_category_id%NOTFOUND )
327 THEN
328
329 CLOSE csr_get_time_category_id;
330
331 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
332 fnd_message.set_token('PROCEDURE', l_proc);
333 fnd_message.set_token('STEP','PTO Time Category');
334 fnd_message.raise_error;
335
336 END IF;
337
338 WHILE csr_get_time_category_id%FOUND
339 LOOP
340
341 IF ( l_tc_name like 'OTL_DEC_%' )
342 THEN
343
344 hxc_time_category_utils_pkg.g_time_category_id := l_tc_id;
345
346 ELSIF ( l_tc_name like 'OTL_INC_%' )
347 THEN
348
349 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id := l_tc_id;
350
351 END IF;
352
353 FETCH csr_get_time_category_id INTO l_tc_id, l_tc_name;
354
355 END LOOP;
356
357 CLOSE csr_get_time_category_id;
358
359 END set_pto_time_category;
360
361
362 BEGIN
363
364 g_debug:=hr_utility.debug_enabled;
365 if g_debug then
366 l_proc:= g_package||'.decode_formula_segments';
367 hr_utility.set_location('Entering '||l_proc, 10);
368 end if;
369
370 -- initialise g_time_category_id
371
372 hxc_time_category_utils_pkg.g_time_category_id := NULL;
373
374 FOR r_seg IN csr_get_flex_segments ( p_formula_name => p_formula_name )
375 LOOP
376
377 if g_debug then
378 hr_utility.set_location('Processing '||l_proc, 10);
379 end if;
380
381 IF ( r_seg.application_column_name = 'ATTRIBUTE1' )
382 THEN
383 if g_debug then
384 hr_utility.trace('');
385 hr_utility.trace('attribute 1 param is '||r_seg.end_user_column_name);
386 hr_utility.trace('attribute 1 param value is '||p_rule_rec.attribute1);
387 end if;
388
389 p_param_rec.param1_value := p_rule_rec.attribute1;
390 p_param_rec.param1 := UPPER(r_seg.end_user_column_name);
391
392 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
393 THEN
394 p_period_value := TO_NUMBER(p_rule_rec.attribute1);
395
396 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
397 THEN
398 p_reference_value := TO_NUMBER(p_rule_rec.attribute1);
399
400 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
401 THEN
402 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute1);
403
404 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
405 THEN
406 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute1) );
407
408 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
409 THEN
410 p_consider_zero_hours := NVL(p_rule_rec.attribute1, 'Y');
411
412 END IF;
413
414 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE2' )
415 THEN
416 if g_debug then
417 hr_utility.trace('');
418 hr_utility.trace('attribute 2 param is '||r_seg.end_user_column_name);
419 hr_utility.trace('attribute 2 param value is '||p_rule_rec.attribute2);
420 end if;
421
422 p_param_rec.param2_value := p_rule_rec.attribute2;
423 p_param_rec.param2 := UPPER(r_seg.end_user_column_name);
424
425 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
426 THEN
427 p_period_value := TO_NUMBER(p_rule_rec.attribute2);
428
429 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
430 THEN
431 p_reference_value := TO_NUMBER(p_rule_rec.attribute2);
432
433 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
434 THEN
435 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute2);
436
437 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
438 THEN
439 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute2) );
440
441 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
442 THEN
443 p_consider_zero_hours := NVL(p_rule_rec.attribute2, 'Y');
444
445 END IF;
446
450 hr_utility.trace('');
447 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE3' )
448 THEN
449 if g_debug then
451 hr_utility.trace('attribute 3 param is '||r_seg.end_user_column_name);
452 hr_utility.trace('attribute 3 param value is '||p_rule_rec.attribute3);
453 end if;
454
455 p_param_rec.param3_value := p_rule_rec.attribute3;
456 p_param_rec.param3 := UPPER(r_seg.end_user_column_name);
457
458 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
459 THEN
460 p_period_value := TO_NUMBER(p_rule_rec.attribute3);
461
462 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
463 THEN
464 p_reference_value := TO_NUMBER(p_rule_rec.attribute3);
465
466 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
467 THEN
468 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute3);
469
470 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
471 THEN
472 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute3) );
473
474 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
475 THEN
476 p_consider_zero_hours := NVL(p_rule_rec.attribute3, 'Y');
477
478 END IF;
479
480 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE4' )
481 THEN
482 p_param_rec.param4_value := p_rule_rec.attribute4;
483 p_param_rec.param4 := r_seg.end_user_column_name;
484
485 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
486 THEN
487 p_period_value := TO_NUMBER(p_rule_rec.attribute4);
488
489 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
490 THEN
491 p_reference_value := TO_NUMBER(p_rule_rec.attribute4);
492
493 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
494 THEN
495 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute4);
496
497 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
498 THEN
499 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute4) );
500
501 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
502 THEN
503 p_consider_zero_hours := NVL(p_rule_rec.attribute4, 'Y');
504
505 END IF;
506
507 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE5' )
508 THEN
509 p_param_rec.param5_value := p_rule_rec.attribute5;
510 p_param_rec.param5 := r_seg.end_user_column_name;
511
512 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
513 THEN
514 p_period_value := TO_NUMBER(p_rule_rec.attribute5);
515
516 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
517 THEN
518 p_reference_value := TO_NUMBER(p_rule_rec.attribute5);
519
520 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
521 THEN
522 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute5);
523
524 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
525 THEN
526 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute5) );
527
528 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
529 THEN
530 p_consider_zero_hours := NVL(p_rule_rec.attribute5, 'Y');
531
532 END IF;
533
534 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE6' )
535 THEN
536 p_param_rec.param6_value := p_rule_rec.attribute6;
537 p_param_rec.param6 := r_seg.end_user_column_name;
538
539 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
540 THEN
541 p_period_value := TO_NUMBER(p_rule_rec.attribute6);
542
543 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
544 THEN
545 p_reference_value := TO_NUMBER(p_rule_rec.attribute6);
546
547 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
548 THEN
549 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute6);
550
551 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
552 THEN
553 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute6) );
554
555 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
556 THEN
557 p_consider_zero_hours := NVL(p_rule_rec.attribute6, 'Y');
558
559 END IF;
560
561 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE7' )
562 THEN
563 p_param_rec.param7_value := p_rule_rec.attribute7;
564 p_param_rec.param7 := r_seg.end_user_column_name;
565
566 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
567 THEN
568 p_period_value := TO_NUMBER(p_rule_rec.attribute7);
569
570 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
571 THEN
572 p_reference_value := TO_NUMBER(p_rule_rec.attribute7);
573
574 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
575 THEN
576 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute7);
577
578 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
579 THEN
580 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute7) );
581
585
582 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
583 THEN
584 p_consider_zero_hours := NVL(p_rule_rec.attribute7, 'Y');
586 END IF;
587
588 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE8' )
589 THEN
590 p_param_rec.param8_value := p_rule_rec.attribute8;
591 p_param_rec.param8 := r_seg.end_user_column_name;
592
593 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
594 THEN
595 p_period_value := TO_NUMBER(p_rule_rec.attribute8);
596
597 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
598 THEN
599 p_reference_value := TO_NUMBER(p_rule_rec.attribute8);
600
601 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
602 THEN
603 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute8);
604
605 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
606 THEN
607 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute8) );
608
609 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
610 THEN
611 p_consider_zero_hours := NVL(p_rule_rec.attribute8, 'Y');
612
613 END IF;
614
615 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE9' )
616 THEN
617 p_param_rec.param9_value := p_rule_rec.attribute9;
618 p_param_rec.param9 := r_seg.end_user_column_name;
619
620 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
621 THEN
622 p_period_value := TO_NUMBER(p_rule_rec.attribute9);
623
624 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
625 THEN
626 p_reference_value := TO_NUMBER(p_rule_rec.attribute9);
627
628 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
629 THEN
630 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute9);
631
632 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
633 THEN
634 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute9) );
635
636 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
637 THEN
638 p_consider_zero_hours := NVL(p_rule_rec.attribute9, 'Y');
639
640 END IF;
641
642 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE10' )
643 THEN
644 p_param_rec.param10_value := p_rule_rec.attribute10;
645 p_param_rec.param10 := r_seg.end_user_column_name;
646
647 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
648 THEN
649 p_period_value := TO_NUMBER(p_rule_rec.attribute10);
650
651 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
652 THEN
653 p_reference_value := TO_NUMBER(p_rule_rec.attribute10);
654
655 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
656 THEN
657 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute10);
658
659 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
660 THEN
661 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute10) );
662
663 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
664 THEN
665 p_consider_zero_hours := NVL(p_rule_rec.attribute10, 'Y');
666
667 END IF;
668
669 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE11' )
670 THEN
671 p_param_rec.param11_value := p_rule_rec.attribute11;
672 p_param_rec.param11 := r_seg.end_user_column_name;
673
674 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
675 THEN
676 p_period_value := TO_NUMBER(p_rule_rec.attribute11);
677
678 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
679 THEN
680 p_reference_value := TO_NUMBER(p_rule_rec.attribute11);
681
682 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
683 THEN
684 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute11);
685
686 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
687 THEN
688 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute11) );
689
690 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
691 THEN
692 p_consider_zero_hours := NVL(p_rule_rec.attribute11, 'Y');
693
694 END IF;
695
696 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE12' )
697 THEN
698 p_param_rec.param12_value := p_rule_rec.attribute12;
699 p_param_rec.param12 := r_seg.end_user_column_name;
700
701 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
702 THEN
703 p_period_value := TO_NUMBER(p_rule_rec.attribute12);
704
705 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
706 THEN
707 p_reference_value := TO_NUMBER(p_rule_rec.attribute12);
708
709 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
710 THEN
711 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute12);
712
713 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
714 THEN
715 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute12) );
716
720
717 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
718 THEN
719 p_consider_zero_hours := NVL(p_rule_rec.attribute12, 'Y');
721 END IF;
722
723 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE13' )
724 THEN
725 p_param_rec.param13_value := p_rule_rec.attribute13;
726 p_param_rec.param13 := r_seg.end_user_column_name;
727
728 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
729 THEN
730 p_period_value := TO_NUMBER(p_rule_rec.attribute13);
731
732 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
733 THEN
734 p_reference_value := TO_NUMBER(p_rule_rec.attribute13);
735
736 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
737 THEN
738 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute13);
739
740 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
741 THEN
742 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute13) );
743
744 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
745 THEN
746 p_consider_zero_hours := NVL(p_rule_rec.attribute13, 'Y');
747
748 END IF;
749
750 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE14' )
751 THEN
752 p_param_rec.param14_value := p_rule_rec.attribute14;
753 p_param_rec.param14 := r_seg.end_user_column_name;
754
755 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
756 THEN
757 p_period_value := TO_NUMBER(p_rule_rec.attribute14);
758
759 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
760 THEN
761 p_reference_value := TO_NUMBER(p_rule_rec.attribute14);
762
763 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
764 THEN
765 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute14);
766
767 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
768 THEN
769 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute14) );
770
771 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
772 THEN
773 p_consider_zero_hours := NVL(p_rule_rec.attribute14, 'Y');
774
775 END IF;
776
777 ELSIF ( r_seg.application_column_name = 'ATTRIBUTE15' )
778 THEN
779 p_param_rec.param15_value := p_rule_rec.attribute15;
780 p_param_rec.param15 := r_seg.end_user_column_name;
781
782 IF ( UPPER(r_seg.end_user_column_name) = 'PERIOD' )
783 THEN
784 p_period_value := TO_NUMBER(p_rule_rec.attribute15);
785
786 ELSIF ( UPPER(r_seg.end_user_column_name) = 'REFERENCE_PERIOD' )
787 THEN
788 p_reference_value := TO_NUMBER(p_rule_rec.attribute15);
789
790 ELSIF ( UPPER(r_seg.end_user_column_name) = 'TIME_CATEGORY' )
791 THEN
792 hxc_time_category_utils_pkg.g_time_category_id := TO_NUMBER(p_rule_rec.attribute15);
793
794 ELSIF ( UPPER(r_seg.end_user_column_name) = 'ACCRUAL_PLAN' )
795 THEN
796 set_pto_time_category ( TO_NUMBER(p_rule_rec.attribute15) );
797
798 ELSIF ( UPPER(r_seg.end_user_column_name) = 'CONSIDER_ZERO_HOURS' )
799 THEN
800 p_consider_zero_hours := NVL(p_rule_rec.attribute15, 'Y');
801
802 END IF;
803
804 ELSE
805 fnd_message.set_name('HXC', 'HXC_WTD_INVALID_FORMULA_DDF');
806 fnd_message.set_token('FORMULA', p_rule_rec.formula_name);
807 fnd_message.raise_error;
808 END IF;
809
810 END LOOP; -- csr_get_flex_segments
811
812 if g_debug then
813 hr_utility.set_location('Leaving '||l_proc, 20);
814 end if;
815
816
817 END decode_formula_segments;
818
819
820
821 FUNCTION execute_approval_formula (
822 p_resource_id NUMBER
823 , p_period_start_date DATE
824 , p_period_end_date DATE
825 , p_tc_period_start_date DATE
826 , p_tc_period_end_date DATE
827 , p_rule_rec hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
828 , p_message_table IN OUT NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE )
829 RETURN varchar2 IS
830
831 l_proc VARCHAR2(72);
832
833 l_result VARCHAR2(1);
834 l_param_rec r_param;
835 l_outputs ff_exec.outputs_t;
836
837 l_timecard_hrs NUMBER := 0;
838 l_reference_period NUMBER(10);
839 l_consider_zero_hours VARCHAR2(10);
840 l_period_id NUMBER(15);
841 l_period_tab hxc_time_entry_rules_utils_pkg.t_period;
842
843 l_period_start DATE;
844 l_period_start_date DATE;
845 l_period_end_date DATE;
846 l_period_type hxc_recurring_periods.period_type%TYPE;
847 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
848
849 -- GPM v115.19
850
851 l_submission_date DATE;
852
853 CURSOR csr_get_first_asg_date ( p_resource_id NUMBER ) IS
854 SELECT MIN( asg.effective_start_date)
855 FROM per_assignments_f asg
856 WHERE asg.person_id = p_resource_id
857 AND asg.primary_flag = 'Y'
858 AND asg.assignment_type in ('E','C');
859
860
861
862 FUNCTION fix_periods ( p_period_tab hxc_time_entry_rules_utils_pkg.t_period )
866
863 RETURN hxc_time_entry_rules_utils_pkg.t_period IS
864
865 l_period_tab hxc_time_entry_rules_utils_pkg.t_period;
867 BEGIN
868
869 l_period_tab := p_period_tab;
870
871 FOR x IN l_period_tab.FIRST .. l_period_tab.LAST
872 LOOP
873
874 IF ( l_period_tab(x).db_pre_period_start IS NULL AND
875 l_period_tab(x).db_pre_period_end IS NULL AND
876 l_period_tab(x).db_post_period_start IS NULL AND
877 l_period_tab(x).db_post_period_end IS NULL )
878 THEN
879
880 l_period_tab(x).db_pre_period_start := l_period_tab(x).period_start;
881 l_period_tab(x).db_pre_period_end := l_period_tab(x).period_end;
882
883 l_period_tab(x).db_post_period_start := NULL;
884 l_period_tab(x).db_post_period_end := NULL;
885 l_period_tab(x).period_start := NULL;
886 l_period_tab(x).period_end := NULL;
887
888 ELSIF ( l_period_tab(x).db_pre_period_start IS NULL AND
889 l_period_tab(x).db_pre_period_end IS NULL )
890 THEN
891
892 l_period_tab(x).db_pre_period_start := l_period_tab(x).period_start;
893 l_period_tab(x).db_pre_period_end := l_period_tab(x).db_post_period_end;
894
895 l_period_tab(x).db_post_period_start := NULL;
896 l_period_tab(x).db_post_period_end := NULL;
897 l_period_tab(x).period_start := NULL;
898 l_period_tab(x).period_end := NULL;
899
900 ELSIF ( l_period_tab(x).db_post_period_start IS NULL AND
901 l_period_tab(x).db_post_period_end IS NULL )
902 THEN
903
904 l_period_tab(x).db_pre_period_end := l_period_tab(x).period_end;
905
906 l_period_tab(x).db_post_period_start := NULL;
907 l_period_tab(x).db_post_period_end := NULL;
908 l_period_tab(x).period_start := NULL;
909 l_period_tab(x).period_end := NULL;
910
911 ELSE
912
913 l_period_tab(x).db_pre_period_end := l_period_tab(x).db_post_period_end;
914
915 l_period_tab(x).db_post_period_start := NULL;
916 l_period_tab(x).db_post_period_end := NULL;
917 l_period_tab(x).period_start := NULL;
918 l_period_tab(x).period_end := NULL;
919
920 END IF;
921
922 if g_debug then
923 hr_utility.trace('');
924 hr_utility.trace(' ********** Fix Periods ************** ');
925 hr_utility.trace(' TC period start is :'||TO_CHAR(l_period_tab(x).period_start, 'DD-MON-YY HH24:MI:SS'));
926 hr_utility.trace(' TC period end is :'||TO_CHAR(l_period_tab(x).period_end, 'DD-MON-YY HH24:MI:SS'));
927 hr_utility.trace(' pre TC period start is :'||TO_CHAR(l_period_tab(x).db_pre_period_start, 'DD-MON-YY HH24:MI:SS'));
928 hr_utility.trace(' pre TC period end is :'||TO_CHAR(l_period_tab(x).db_pre_period_end, 'DD-MON-YY HH24:MI:SS'));
929 hr_utility.trace(' post TC period start is:'||TO_CHAR(l_period_tab(x).db_post_period_start, 'DD-MON-YY HH24:MI:SS'));
930 hr_utility.trace(' post TC period end is :'||TO_CHAR(l_period_tab(x).db_post_period_end, 'DD-MON-YY HH24:MI:SS'));
931 end if;
932
933 END LOOP;
934
935 RETURN l_period_tab;
936
937 END fix_periods;
938
939 BEGIN
940
941 g_debug:=hr_utility.debug_enabled;
942 if g_debug then
943 l_proc := g_package||'.execute_approval_formula';
944 hr_utility.set_location('Entering '||l_proc, 10);
945 end if;
946
947 decode_formula_segments (
948 p_formula_name => p_rule_rec.formula_name
949 , p_rule_rec => p_rule_rec
950 , p_param_rec => l_param_rec
951 , p_period_value => l_period_id
952 , p_reference_value => l_reference_period
953 , p_consider_zero_hours => l_consider_zero_hours );
954
955 if g_debug then
956 hr_utility.set_location('Processing '||l_proc, 30);
957 end if;
958
959 IF ( l_period_id IS NULL )
960 THEN
961
962 if g_debug then
963 hr_utility.set_location('Processing '||l_proc, 40);
964 end if;
965
966
967 -- no period id entered via TER thus use application period
968
969 l_period_end_date := TO_DATE(TO_CHAR(p_period_end_date, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
970
971 if g_debug then
972 hr_utility.trace('Application approval period start is '||TO_CHAR(p_period_start_date, 'DD-MON-YY HH24:MI:SS'));
973 hr_utility.trace('Application approval period end is ' ||TO_CHAR(l_period_end_date, 'DD-MON-YY HH24:MI:SS'));
974 end if;
975
976 hxc_time_entry_rules_utils_pkg.calc_timecard_periods (
977 p_timecard_period_start => p_tc_period_start_date
978 , p_timecard_period_end => p_tc_period_end_date
979 , p_period_start_date => p_period_start_date
980 , p_period_end_date => l_period_end_date
981 , p_duration_in_days => 100 -- arbitrary
982 , p_periods_tab => l_period_tab );
983
984 ELSE -- user entered period via TER thus override application period
985
986 if g_debug then
987 hr_utility.set_location('Processing '||l_proc, 50);
988 end if;
989
990 /* NOTE: This code pulled from hxc_time_Entry_rules_utils_pkg */
991
992
993 OPEN hxc_time_entry_rules_utils_pkg.csr_get_period_info ( p_recurring_period_id => l_period_id );
994 FETCH hxc_time_entry_rules_utils_pkg.csr_get_period_info INTO l_period_type, l_duration_in_days, l_period_start;
995 CLOSE hxc_time_entry_rules_utils_pkg.csr_get_period_info;
996
997 if g_debug then
998 hr_utility.trace('');
1002 hr_utility.trace('period start date is '||TO_CHAR(l_period_start,'DD-MON-YY HH24:MI:SS'));
999 hr_utility.trace('*********** Period Info ************');
1000 hr_utility.trace('period type is '||l_period_type);
1001 hr_utility.trace('duration in days is '||TO_CHAR(l_duration_in_days));
1003 end if;
1004
1005 -- gaz - remove this when function changed
1006
1007 IF ( UPPER(l_period_type) = 'WEEK' )
1008 THEN
1009 l_duration_in_days := 7;
1010
1011 ELSIF( UPPER(l_period_type) = 'BI-WEEK')
1012 THEN
1013 l_duration_in_days := 14;
1014 END IF;
1015
1016 IF ( l_duration_in_days IS NOT NULL )
1017 THEN
1018
1019 l_period_start_date := l_period_start +
1020 (l_duration_in_days *
1021 FLOOR(((p_tc_period_start_date - l_period_start)/l_duration_in_days)));
1022
1023 l_period_end_date := l_period_start_date + l_duration_in_days - 1;
1024
1025 ELSE
1026
1027 -- Call application specific function to generate the period
1028 -- start and end dates from the period type.
1029
1030 hr_generic_util.get_period_dates
1031 (p_rec_period_start_date => l_period_start
1032 ,p_period_type => l_period_type
1033 ,p_current_date => p_tc_period_start_date
1034 ,p_period_start_date => l_period_start_date
1035 ,p_period_end_date => l_period_end_date);
1036
1037 l_duration_in_days := ( l_period_end_date - l_period_start_date ) + 1;
1038
1039 END IF;
1040
1041 -- now add time component to l_period_end
1042
1043 l_period_end_date := TO_DATE(TO_CHAR(l_period_end_date, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1044
1045 if g_debug then
1046 Hr_utility.trace('');
1047 hr_utility.trace('*********** Period Start and End ************');
1048 hr_utility.trace('period start date is '||TO_CHAR(l_period_start_date,'DD-MON-YY HH24:MI:SS'));
1049 hr_utility.trace('period end date is '||TO_CHAR(l_period_end_date,'DD-MON-YY HH24:MI:SS'));
1050 hr_utility.trace('duration in days is '||TO_CHAR(l_duration_in_days));
1051 end if;
1052
1053 -- now build up table of time entry rule periods that the timecard
1054 -- may span
1055
1056 hxc_time_entry_rules_utils_pkg.calc_timecard_periods (
1057 p_timecard_period_start => p_tc_period_start_date
1058 , p_timecard_period_end => p_tc_period_end_date
1059 , p_period_start_date => l_period_start_date
1060 , p_period_end_date => l_period_end_date
1061 , p_duration_in_days => l_duration_in_days
1062 , p_periods_tab => l_period_tab );
1063
1064 END IF; -- ( l_period_id IS NOT NULL )
1065
1066 if g_debug then
1067 hr_utility.set_location('Processing '||l_proc, 60);
1068 end if;
1069
1070 -- set submission date to be within valid assignment
1071
1072 OPEN csr_get_first_asg_date ( p_resource_id );
1073 FETCH csr_get_first_asg_date INTO l_submission_date;
1074 CLOSE csr_get_first_asg_date;
1075
1076 -- Since we are re-using code from TER which assumes we calculated
1077 -- hours from the TC we need to manipulate the table of periods
1078 -- to calculate all hours from the database.
1079
1080 l_period_tab := fix_periods ( l_period_tab );
1081
1082 -- now loop through the periods to execute the formula
1083
1084 FOR p IN l_period_tab.FIRST .. l_period_tab.LAST
1085 LOOP
1086
1087 if g_debug then
1088 hr_utility.set_location('Processing '||l_proc, 70);
1089 end if;
1090
1091 l_timecard_hrs := 0;
1092
1093 if g_debug then
1094 hr_utility.set_location('Processing '||l_proc, 80);
1095 end if;
1096
1097 -- Execute the formula
1098
1099 if g_debug then
1100 hr_utility.trace('Before call to execute formula');
1101 end if;
1102
1103 -- GPM v115.19
1104 -- take the greater of the first assignment date or the period start date
1105
1106 l_submission_date := GREATEST( l_submission_date, l_period_tab(p).db_pre_period_start );
1107
1108 l_outputs := hxc_ff_dict.formula(
1109 p_formula_id => p_rule_rec.formula_id
1110 , p_resource_id => p_resource_id
1111 , p_submission_date => l_submission_date -- GPM v115.19
1112 , p_ss_timecard_hours => l_timecard_hrs
1113 , p_db_pre_period_start => l_period_tab(p).db_pre_period_start
1114 , p_db_pre_period_end => l_period_tab(p).db_pre_period_end
1115 , p_db_post_period_start => l_period_tab(p).db_post_period_start
1116 , p_db_post_period_end => l_period_tab(p).db_post_period_end
1117 , p_db_ref_period_start => l_period_tab(p).db_ref_period_start
1118 , p_db_ref_period_end => l_period_tab(p).db_ref_period_end
1119 , p_duration_in_days => 1
1120 , p_param_rec => l_param_rec );
1121
1122 if g_debug then
1123 hr_utility.set_location('Processing '||l_proc, 100);
1124 end if;
1125
1126 --
1127 if g_debug then
1128 hr_utility.trace('After call to execute formula');
1129 end if;
1130 --
1131 -- Analyze the outputs
1132 --
1133 FOR l_count IN l_outputs.FIRST .. l_outputs.LAST
1134 LOOP
1135 --
1136 IF (l_outputs(l_count).name = 'TO_APPROVE') THEN
1137
1138 if g_debug then
1139 hr_utility.set_location('Processing '||l_proc, 110);
1140 end if;
1141
1142 l_result := l_outputs(l_count).value;
1143
1144 ELSIF ( l_outputs(l_count).name = 'RULE_STATUS' )
1145 THEN
1146
1147 -- since time entry rule formulas can potentially also be used
1148 -- in approvals translate the time entry rule return
1149 -- value to a value the approvals code can
1150 -- understand. RULE_STATUS=E is an exception in the
1151 -- time entry rule world thus is to approve = Y
1152
1153 IF ( l_outputs(l_count).value = 'E' )
1154 THEN
1155 l_result := 'Y';
1156 ELSE
1157 l_result := 'N';
1158 END IF;
1159
1160 END IF;
1161
1162 END LOOP; -- formula outputs loop
1163
1164 -- in the case where we execute the formula many times
1165 -- exit when the result is Y
1166
1167 IF ( l_result = 'Y' )
1168 THEN
1169 EXIT;
1170 END IF;
1171
1172 IF ( p = 1 AND l_period_id IS NULL )
1173 THEN
1174 if g_debug then
1175 hr_utility.set_location('Processing '||l_proc, 90);
1176 end if;
1177
1178 -- remember only want to do this once for the
1179 -- application approval period
1180
1181 EXIT;
1182 END IF;
1183
1184 END LOOP; -- period loop
1185
1186 if g_debug then
1187 hr_utility.set_location('Processing '||l_proc, 120);
1188 end if;
1189
1190
1191 -- we used to populate the message table here but the approval
1192 -- process does not currently support ANY message being returned
1193 -- for the formulas executed in the date interdependcy rules
1194
1195 if g_debug then
1196 hr_utility.set_location('Processing '||l_proc, 130);
1197 end if;
1198
1199 if g_debug then
1200 hr_utility.trace('l result is '||l_result);
1201 end if;
1202
1203 RETURN l_result;
1204
1205 EXCEPTION WHEN OTHERS THEN
1206
1207 if g_debug then
1208 hr_utility.trace('In exception : '||SQLERRM);
1209 end if;
1210
1211 raise;
1212
1213 END execute_approval_formula;
1214
1215 FUNCTION get_formula_segment_value (
1216 p_param_rec r_param
1217 , p_param fnd_descr_flex_column_usages.end_user_column_name%TYPE ) RETURN hxc_time_entry_rules.attribute1%TYPE IS
1218
1219 l_proc varchar2(72) := g_package||'.get_formula_segment_value';
1220 l_param_value hxc_time_entry_rules.attribute1%TYPE;
1221
1222 BEGIN
1223
1224 IF ( p_param = p_param_rec.param1 )
1225 THEN
1226 l_param_value := p_param_rec.param1_value;
1227
1228 ELSIF ( p_param = p_param_rec.param2 )
1229 THEN
1230 l_param_value := p_param_rec.param2_value;
1231
1232 ELSIF ( p_param = p_param_rec.param3 )
1233 THEN
1234 l_param_value := p_param_rec.param3_value;
1235
1236 ELSIF ( p_param = p_param_rec.param4 )
1237 THEN
1238 l_param_value := p_param_rec.param4_value;
1239
1240 ELSIF ( p_param = p_param_rec.param5 )
1241 THEN
1242 l_param_value := p_param_rec.param5_value;
1243
1244 ELSIF ( p_param = p_param_rec.param6 )
1245 THEN
1246 l_param_value := p_param_rec.param6_value;
1247
1248 ELSIF ( p_param = p_param_rec.param7 )
1249 THEN
1250 l_param_value := p_param_rec.param7_value;
1251
1252 ELSIF ( p_param = p_param_rec.param8 )
1253 THEN
1254 l_param_value := p_param_rec.param8_value;
1255
1256 ELSIF ( p_param = p_param_rec.param9 )
1257 THEN
1258 l_param_value := p_param_rec.param9_value;
1259
1260 ELSIF ( p_param = p_param_rec.param10 )
1261 THEN
1262 l_param_value := p_param_rec.param10_value;
1263
1264 ELSIF ( p_param = p_param_rec.param11 )
1265 THEN
1266 l_param_value := p_param_rec.param11_value;
1267
1268 ELSIF ( p_param = p_param_rec.param12 )
1269 THEN
1270 l_param_value := p_param_rec.param12_value;
1271
1272 ELSIF ( p_param = p_param_rec.param13 )
1273 THEN
1274 l_param_value := p_param_rec.param13_value;
1275
1276 ELSIF ( p_param = p_param_rec.param14 )
1277 THEN
1278 l_param_value := p_param_rec.param14_value;
1279
1280 ELSIF ( p_param = p_param_rec.param15 )
1281 THEN
1282 l_param_value := p_param_rec.param15_value;
1283
1284 ELSE
1285
1286 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1287 fnd_message.set_token('PROCEDURE', l_proc);
1288 fnd_message.set_token('STEP','Invalid formula param name');
1289 fnd_message.raise_error;
1290
1291 END IF;
1292
1293 RETURN l_param_value;
1294
1295 END get_formula_segment_value;
1296
1297
1298 end hxc_ff_dict;