1 PACKAGE BODY pqp_rates_history_calc AS
2 /* $Header: pqrthcal.pkb 120.6.12010000.5 2008/08/05 14:23:40 ubhat ship $ */
3 --
4 -- Package Variables
5 -- do not include globals below this line in clear_cache
6 g_package_name VARCHAR2(31) := 'pqp_rates_history_calc.';
7 hr_application_error EXCEPTION;
8 g_debug BOOLEAN;
9 PRAGMA EXCEPTION_INIT(hr_application_error, -20001);
10
11
12 -- include globals below this line in clear_cache
13 -- always group globals with reference to the subprograms that use them
14
15 -- cache for rates_history function
16 g_business_group_id pay_element_types_f.business_group_id%TYPE;
17 g_legislation_code pay_element_types_f.legislation_code%TYPE;
18 g_cache_rate_type_name fnd_lookup_values.meaning%TYPE;
19 g_cache_rate_type_code fnd_lookup_values.lookup_code%TYPE;
20
21 -- cache for get_bus_group_id
22 g_cache_assignment_id per_all_assignments_f.assignment_id%TYPE;
23 g_cache_business_group_id per_all_assignments_f.business_group_id%TYPE;
24
25 -- cache for get_element_entry_value
26 g_geev_element_type_id pay_element_types_f.element_type_id%TYPE;
27
28
29 -- cursors
30
31 CURSOR csr_input_value_id
32 (p_element_type_id NUMBER
33 ,p_input_value_name_in_caps VARCHAR2
34 ,p_effective_date DATE
35 ) IS
36 SELECT input_value_id
37 ,default_value
38 ,lookup_type
39 ,value_set_id
40 FROM pay_input_values_f
41 WHERE element_type_id = p_element_type_id
42 AND UPPER(name) = UPPER(p_input_value_name_in_caps)
43 AND p_effective_date
44 BETWEEN effective_start_date
45 AND effective_end_date;
46
47
48 CURSOR csr_element_entry_value
49 (p_assignment_id IN NUMBER
50 ,p_element_type_id IN NUMBER
51 ,p_input_value_id IN NUMBER
52 ,p_effective_date IN DATE
53 ) IS
54 SELECT eev.screen_entry_value
55 ,liv.default_value
56 FROM pay_element_entries_f ele
57 ,pay_element_links_f lnk
58 ,pay_link_input_values_f liv
59 ,pay_element_entry_values_f eev
60 WHERE ele.assignment_id = p_assignment_id
61 AND ele.entry_type = 'E'
62 AND p_effective_date
63 BETWEEN ele.effective_start_date
64 AND ele.effective_end_date
65 AND eev.element_entry_id = ele.element_entry_id
66 AND lnk.element_link_id = ele.element_link_id
67 AND lnk.element_type_id = p_element_type_id
68 AND p_effective_date
69 BETWEEN eev.effective_start_date
70 AND eev.effective_end_date
71 AND eev.input_value_id = p_input_value_id
72 AND liv.element_link_id = lnk.element_link_id
73 AND liv.input_value_id = p_input_value_id
74 AND p_effective_date
75 BETWEEN liv.effective_start_date
76 AND liv.effective_end_date
77 AND p_effective_date
78 BETWEEN lnk.effective_start_date
79 AND lnk.effective_end_date;
80
81
82 -- Cursor to check if an element is linked to a assignment
83 CURSOR csr_element_entry
84 (p_assignment_id IN NUMBER
85 ,p_element_type_id IN NUMBER
86 ,p_effective_date IN DATE
87 ) IS
88 SELECT pee.element_entry_id
89 FROM pay_element_links_f pel
90 ,pay_element_entries_f pee
91 WHERE pel.element_type_id = p_element_type_id
92 AND p_effective_date
93 BETWEEN pel.effective_start_date
94 AND pel.effective_end_date
95 AND pee.element_link_id = pel.element_link_id
96 AND pee.assignment_id = p_assignment_id
97 AND p_effective_date
98 BETWEEN pee.effective_start_date
99 AND pee.effective_end_date
100 AND p_effective_date
101 BETWEEN pel.effective_start_date
102 AND pel.effective_end_date;
103
104
105 CURSOR csr_given_element_entry_value
106 (p_element_entry_id IN NUMBER
107 ,p_input_value_id IN NUMBER
108 ,p_effective_date IN DATE
109 ) IS
110 SELECT eev.screen_entry_value
111 ,liv.default_value
112 FROM pay_element_entries_f ele
113 ,pay_element_links_f lnk
114 ,pay_link_input_values_f liv
115 ,pay_element_entry_values_f eev
116 WHERE ele.element_entry_id = p_element_entry_id
117 AND p_effective_date
118 BETWEEN ele.effective_start_date
119 AND ele.effective_end_date
120 AND eev.element_entry_id = ele.element_entry_id
121 AND lnk.element_link_id = ele.element_link_id
122 AND lnk.element_type_id = ele.element_type_id
123 AND p_effective_date
124 BETWEEN eev.effective_start_date
125 AND eev.effective_end_date
126 AND eev.input_value_id = p_input_value_id
127 AND liv.element_link_id = lnk.element_link_id
128 AND liv.input_value_id = p_input_value_id
129 AND p_effective_date
130 BETWEEN liv.effective_start_date
131 AND liv.effective_end_date
132 AND p_effective_date
133 BETWEEN lnk.effective_start_date
134 AND lnk.effective_end_date;
135
136
137
138
139 --
140 --
141 --
142 PROCEDURE debug(
143 p_trace_message IN VARCHAR2
144 ,p_trace_location IN NUMBER DEFAULT NULL
145 )
146 IS
147 BEGIN
148 pqp_utilities.debug(p_trace_message, p_trace_location);
149 END debug;
150 --
151 --
152 --
153 PROCEDURE debug(p_trace_number IN NUMBER)
154 IS
155 BEGIN
156 pqp_utilities.debug(p_trace_number);
157 END debug;
158 --
159 --
160 --
161 PROCEDURE debug(p_trace_date IN DATE)
162 IS
163 BEGIN
164 pqp_utilities.debug(p_trace_date);
165 END debug;
166 --
167 --
168 --
169 PROCEDURE debug_enter(
170 p_proc_name IN VARCHAR2
171 ,p_trace_on IN VARCHAR2 DEFAULT NULL
172 )
173 IS
174 BEGIN
175 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
176 END debug_enter;
177 --
178 --
179 --
180 PROCEDURE debug_exit(
181 p_proc_name IN VARCHAR2
182 ,p_trace_off IN VARCHAR2 DEFAULT NULL
183 )
184 IS
185 BEGIN
186 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
187 END debug_exit;
188 --
189 --
190 --
191 PROCEDURE debug_others(
192 p_proc_name IN VARCHAR2
193 ,p_proc_step IN NUMBER DEFAULT NULL
194 )
195 IS
196 BEGIN
197 pqp_utilities.debug_others(p_proc_name, p_proc_step);
198 END debug_others;
199 --
200 --
201 --
202 PROCEDURE check_error_code
203 (p_error_code IN NUMBER
204 ,p_error_message IN VARCHAR2
205 )
206 IS
207 BEGIN
208 pqp_utilities.check_error_code(p_error_code, p_error_message);
209 END;
210 --
211 --
212 --
213 PROCEDURE clear_cache
214 IS
215 BEGIN
216 -- cache for rates_history function
217 g_business_group_id := NULL;--pay_element_types_f.business_group_id%TYPE;
218 g_legislation_code := NULL;--pay_element_types_f.business_group_id%TYPE;
219 g_cache_rate_type_name := NULL;--fnd_lookup_values.meaning%TYPE;
220 g_cache_rate_type_code := NULL;--fnd_lookup_values.lookup_code%TYPE;
221
222 -- cache for get_bus_group_id
223 g_cache_assignment_id := NULL;--per_all_assignments_f.assignment_id%TYPE;
224 g_cache_business_group_id := NULL;--per_all_assignments_f.business_group_id%TYPE;
225
226 END clear_cache;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |--------------------------< get_bus_grp_id >------------------------------|
230 -- ----------------------------------------------------------------------------
231 --
232 -- Description: This function returns the business group id for the given assignment Id.
233 --
234 --
235 FUNCTION get_bus_grp_id(p_assignment_id IN NUMBER)
236 RETURN NUMBER
237 IS
238
239 l_proc_step NUMBER(20,10):=0;
240 l_proc_name VARCHAR2(61):=
241 g_package_name||'get_bus_grp_id';
242
243 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
244
245 CURSOR csr_get_bus_grp_id
246 IS
247 SELECT business_group_id
248 FROM per_all_assignments_f
249 WHERE assignment_id = p_assignment_id;
250 BEGIN
251
252 IF g_debug THEN
253 debug_enter(l_proc_name);
254 debug('p_assignment_id:'||p_assignment_id);
255 debug('g_cache_assignment_id:'||g_cache_assignment_id);
256 debug('g_cache_business_group_id:'||g_cache_business_group_id);
257 END IF;
258
259 IF p_assignment_id <> g_cache_assignment_id
260 OR
261 g_cache_assignment_id IS NULL
262 OR
263 g_cache_business_group_id IS NULL
264 THEN
265 l_proc_step := 5;
266 IF g_debug THEN
267 debug(l_proc_name,l_proc_step);
268 END IF;
269
270 OPEN csr_get_bus_grp_id;
271 FETCH csr_get_bus_grp_id INTO l_business_group_id;
272 IF csr_get_bus_grp_id%FOUND
273 THEN
274 l_proc_step := 10;
275 IF g_debug THEN
276 debug(l_proc_name,l_proc_step);
277 END IF;
278 g_cache_assignment_id := p_assignment_id;
279 g_cache_business_group_id := l_business_group_id;
280 ELSE
281 l_proc_step := 15;
282 IF g_debug THEN
283 debug(l_proc_name,l_proc_step);
284 END IF;
285 g_cache_assignment_id := NULL;
286 g_cache_business_group_id := NULL;
287 END IF;
288 CLOSE csr_get_bus_grp_id;
289
290 END IF;
291
292 l_business_group_id := g_cache_business_group_id;
293
294 IF g_debug THEN
295 debug('l_business_group_id:'||l_business_group_id);
296 debug_exit(l_proc_name);
297 END IF;
298
299 RETURN l_business_group_id;
300 EXCEPTION
301 WHEN OTHERS THEN
302 clear_cache;
303 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
304 debug_others(l_proc_name,l_proc_step);
305 IF g_debug THEN
306 debug('Leaving: '||l_proc_name,-999);
307 END IF;
308 fnd_message.raise_error;
309 ELSE
310 RAISE;
311 END IF;
312 END get_bus_grp_id;
313 --
314 --
315 -- ----------------------------------------------------------------------------
316 -- |--------------------------< get_element_attributes >----------------------|
317 -- ----------------------------------------------------------------------------
318 --
319 -- Description: Returns all the rates history attribution held at the
320 -- element level.
321 -- Added extra atributes as the Context is extended to have additional
322 -- Fields to store calculation information etc.
323 --
324 -- pqpgbtp1.pkb: pqp_rates_history_calc.get_element_attributes
325 PROCEDURE get_element_attributes(
326 p_element_type_extra_info_id IN NUMBER
327 ,p_service_history OUT NOCOPY VARCHAR2
328 ,p_fte OUT NOCOPY VARCHAR2
329 ,p_pay_source_value OUT NOCOPY VARCHAR2
330 ,p_qualifier OUT NOCOPY VARCHAR2
331 ,p_from_time_dim OUT NOCOPY VARCHAR2
332 ,p_calculation_type OUT NOCOPY VARCHAR2
333 ,p_calculation_value OUT NOCOPY VARCHAR2
334 ,p_input_value OUT NOCOPY VARCHAR2
335 ,p_linked_to_assignment OUT NOCOPY VARCHAR2
336 ,p_term_time_yes_no OUT NOCOPY VARCHAR2
337 ,p_sum_multiple_entries_yn OUT NOCOPY VARCHAR2
338 ,p_lookup_input_values_yn OUT NOCOPY VARCHAR2
339 ,p_column_name_source_type OUT NOCOPY VARCHAR2
340 ,p_column_name_source_name OUT NOCOPY VARCHAR2
341 ,p_row_name_source_type OUT NOCOPY VARCHAR2
342 ,p_row_name_source_name OUT NOCOPY VARCHAR2
343 )
344 IS
345
346 --
347 -- Cursor to get values from element_attribution EIT
348 -- Added extra atributes as the Context is extended to have additional
349 -- Fields to store calculation information etc.
350
351 CURSOR c_element_attributes
352 IS
353 SELECT pei.eei_information1 from_time_dimension
354 ,pei.eei_information2 pay_source_value
355 ,pei.eei_information3 qualifier
356 ,pei.eei_information4 fte
357 ,pei.eei_information5 termtime
358 ,pei.eei_information7 calc_type
359 ,pei.eei_information8 calc_value
360 ,pei.eei_information9 input_value
361 ,NVL(pei.eei_information10
362 ,decode(pei.eei_information2,'IV','Y','N')) link_to_assign
363 ,NVL(pei.eei_information12,'Y') term_time_yes_no -- ! be careful
364 ,NVL(pei.eei_information13,'N') sum_multiple_entries_yn
365 ,NVL(pei.eei_information14,'N') lookup_input_values_yn
366 ,pei.eei_information16 column_name_source_type
367 ,pei.eei_information17 column_name_source_name
368 ,pei.eei_information18 row_name_source_type
369 ,pei.eei_information19 row_name_source_name
370 FROM pay_element_type_extra_info pei
371 WHERE pei.element_type_extra_info_id = p_element_type_extra_info_id;
372
373 l_proc_step NUMBER(20,10):=0;
374 l_proc_name VARCHAR2(61)
375 := g_package_name || 'get_element_attributes';
376
377 l_service_history fnd_lookups.lookup_code%TYPE;
378 l_fte fnd_lookups.lookup_code%TYPE;
379 l_pay_source_value fnd_lookups.lookup_code%TYPE;
380 l_qualifier pay_element_types_f.element_name%TYPE;
381 l_from_time_dim fnd_lookups.lookup_code%TYPE;
382 l_calc_type fnd_lookups.lookup_code%TYPE;
383 l_calc_value fnd_lookups.lookup_code%TYPE;
384 l_input_value fnd_lookups.lookup_code%TYPE;
385 l_check_link_to_assignment_yn fnd_lookups.lookup_code%TYPE;
386 l_term_time_yes_no fnd_lookups.lookup_code%TYPE;
387 l_sum_multiple_entries_yn fnd_lookup_values.lookup_code%TYPE;
388 l_lookup_input_values_yn fnd_lookup_values.lookup_code%TYPE;
389 l_column_name_source_type pay_element_type_extra_info.eei_information16%TYPE;
390 l_column_name_source_name pay_element_type_extra_info.eei_information17%TYPE;
391 l_row_name_source_type pay_element_type_extra_info.eei_information18%TYPE;
392 l_row_name_source_name pay_element_type_extra_info.eei_information19%TYPE;
393
394
395
396 BEGIN
397
398 g_debug := hr_utility.debug_enabled;
399
400 IF g_debug THEN
401 debug_enter(l_proc_name);
402 debug('p_element_type_extra_info_id:'||p_element_type_extra_info_id);
403 END IF;
404
405 OPEN c_element_attributes;
406 FETCH c_element_attributes
407 INTO l_from_time_dim
408 ,l_pay_source_value
409 ,l_qualifier
410 ,l_fte
411 ,l_service_history
412 ,l_calc_type
413 ,l_calc_value
414 ,l_input_value
415 ,l_check_link_to_assignment_yn
416 ,l_term_time_yes_no
417 ,l_sum_multiple_entries_yn
418 ,l_lookup_input_values_yn
419 ,l_column_name_source_type
420 ,l_column_name_source_name
421 ,l_row_name_source_type
422 ,l_row_name_source_name
423 ;
424 CLOSE c_element_attributes;
425
426 l_proc_step := 10;
427 IF g_debug THEN
428 debug(l_proc_name,l_proc_step);
429 END IF;
430
431 p_from_time_dim := l_from_time_dim;
432 p_pay_source_value := l_pay_source_value;
433 p_qualifier := l_qualifier;
434 p_fte := l_fte;
435 p_service_history := l_service_history;
436 p_calculation_type := l_calc_type ;
437 p_calculation_value := l_calc_value ;
438 p_input_value := l_input_value ;
439 p_linked_to_assignment := l_check_link_to_assignment_yn ;
440 p_term_time_yes_no := l_term_time_yes_no;
441 p_sum_multiple_entries_yn := l_sum_multiple_entries_yn;
442 p_lookup_input_values_yn := l_lookup_input_values_yn;
443 p_column_name_source_type := l_column_name_source_type;
444 p_column_name_source_name := l_column_name_source_name;
445 p_row_name_source_type := l_row_name_source_type;
446 p_row_name_source_name := l_row_name_source_name;
447
448
449
450 l_proc_step := 20;
451 IF g_debug THEN
452 debug('p_from_time_dim:'||p_from_time_dim );
453 debug('p_pay_source_value:'||p_pay_source_value );
454 debug('p_qualifier:'||p_qualifier );
455 debug('p_fte:'||p_fte );
456 debug('p_service_history:'||p_service_history );
457 debug('p_calculation_type:'||p_calculation_type );
458 debug('p_calculation_value:'||p_calculation_value );
459 debug('p_input_value:'||p_input_value );
460 debug('p_linked_to_assignment:'||p_linked_to_assignment );
461 debug('p_sum_multiple_entries_yn:'||p_sum_multiple_entries_yn );
462 debug('p_lookup_input_values_yn:'||p_lookup_input_values_yn );
463 debug_exit(l_proc_name);
464 END IF;
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 clear_cache;
469 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
470 debug_others(l_proc_name,l_proc_step);
471 IF g_debug THEN
472 debug('Leaving: '||l_proc_name,-999);
473 END IF;
474 fnd_message.raise_error;
475 ELSE
476 RAISE;
477 END IF;
478 END get_element_attributes;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |--------------------------< service_history_factor >----------------------|
482 -- ----------------------------------------------------------------------------
483 --
484 -- Description: Returns the service history factor, calculated by
485 -- matching length of continuous service against the service history
486 -- banding defined for the employee's contract
487 --
488 FUNCTION service_history_factor(p_assignment_id IN NUMBER, p_date IN DATE)
489 RETURN NUMBER
490 IS
491 l_proc_step NUMBER(20,10):=0;
492 l_proc_name VARCHAR2(61)
493 := g_package_name || 'service_history_factor';
494 l_contract_type VARCHAR2(80);
495 l_service_length NUMBER;
496 l_service_factor VARCHAR2(80);
497 l_lower VARCHAR2(80);
498 l_upper VARCHAR2(80);
499 l_column_name VARCHAR2(80);
500 l_business_group_id pay_user_column_instances_f.business_group_id%TYPE;
501
502 --
503 -- Cursor to get Service History Factor
504 --
505 CURSOR c_service_factor(p_contract_type VARCHAR2, p_column_name VARCHAR2)
506 IS
507 SELECT sci.VALUE
508 ,lci.VALUE
509 ,uci.VALUE
510 FROM pay_user_column_instances_f sci
511 ,pay_user_columns sc
512 ,pay_user_column_instances_f uci
513 ,pay_user_columns uc
514 ,pay_user_column_instances_f lci
515 ,pay_user_columns lc
516 ,pay_user_tables ut
517 ,pay_user_rows_f ur
518 WHERE ut.user_table_name = 'PQP_CONTRACT_TYPES'
519 AND ur.user_table_id = ut.user_table_id
520 AND UPPER(ur.row_low_range_or_name) = UPPER(p_contract_type)
521 AND UPPER(sc.user_column_name) =
522 UPPER(p_column_name || ' ADJUSTMENT FACTOR')
523 AND sc.user_table_id = ut.user_table_id
524 AND sci.user_column_id = sc.user_column_id
525 AND ur.user_row_id = sci.user_row_id
526 AND UPPER(uc.user_column_name) =
527 UPPER(p_column_name || ' UPPER LIMIT')
528 AND uc.user_table_id = ut.user_table_id
529 AND uci.user_column_id = uc.user_column_id
530 AND ur.user_row_id = uci.user_row_id
531 AND UPPER(lc.user_column_name) =
532 UPPER(p_column_name || ' LOWER LIMIT')
533 AND lc.user_table_id = ut.user_table_id
534 AND lci.user_column_id = lc.user_column_id
535 AND ur.user_row_id = lci.user_row_id
536 AND (
537 (
538 sci.business_group_id IS NOT NULL
539 AND sci.business_group_id = l_business_group_id
540 )
541 OR (
542 sci.legislation_code IS NOT NULL
543 AND sci.business_group_id IS NULL
544 )
545 OR (
546 sci.business_group_id IS NULL
547 AND sci.legislation_code IS NULL
548 )
549 )
550 AND (
551 (
552 uci.business_group_id IS NOT NULL
553 AND uci.business_group_id = l_business_group_id
554 )
555 OR (
556 uci.legislation_code IS NOT NULL
557 AND uci.business_group_id IS NULL
558 )
559 OR (
560 uci.business_group_id IS NULL
561 AND uci.legislation_code IS NULL
562 )
563 )
564 AND (
565 (
566 lci.business_group_id IS NOT NULL
567 AND lci.business_group_id = l_business_group_id
568 )
569 OR (
570 lci.legislation_code IS NOT NULL
571 AND lci.business_group_id IS NULL
572 )
573 OR (
574 lci.business_group_id IS NULL
575 AND lci.legislation_code IS NULL
576 )
577 )
578 AND (
579 (
580 ur.business_group_id IS NOT NULL
581 AND ur.business_group_id = l_business_group_id
582 )
583 OR (
584 ur.legislation_code IS NOT NULL
585 AND ur.business_group_id IS NULL
586 )
587 OR (
588 ur.business_group_id IS NULL AND ur.legislation_code IS NULL
589 )
590 )
591 AND p_date BETWEEN ur.effective_start_date AND ur.effective_end_date
592 AND p_date BETWEEN sci.effective_start_date AND sci.effective_end_date
593 AND p_date BETWEEN uci.effective_start_date AND uci.effective_end_date
594 AND p_date BETWEEN lci.effective_start_date AND lci.effective_end_date;
595
596 --
597 -- Cursor to get contract type
598 --
599 CURSOR c_contract_type
600 IS
601 SELECT contract_type
602 FROM pqp_assignment_attributes_f
603 WHERE assignment_id = p_assignment_id
604 AND p_date BETWEEN effective_start_date AND effective_end_date;
605 BEGIN
606 --
607 g_debug := hr_utility.debug_enabled;
608 IF g_debug THEN
609 debug_enter(l_proc_name);
610 END IF;
611
612 --
613 l_business_group_id := get_bus_grp_id(p_assignment_id => p_assignment_id);
614 --
615
616 OPEN c_contract_type;
617 FETCH c_contract_type INTO l_contract_type;
618 CLOSE c_contract_type;
619
620 IF l_contract_type IS NULL
621 THEN
622 --
623 -- Added a new message for contract type not found
624 -- BUG 3454641
625 hr_utility.set_message(8303, 'PQP_230113_AAT_MISSING_CONTRCT');
626 -- ver 115.35 : anshghos : setting token value
627 fnd_message.set_token('EFFECTIVEDATE',fnd_date.date_to_canonical(p_date));
628 hr_utility.raise_error;
629 -- RETURN 0;
630 --
631 END IF;
632
633 l_service_length :=
634 pqp_service_history_calc_pkg.calculate_continuous_service
635 (p_assignment_id => p_assignment_id
636 ,p_calculation_date => p_date);
637 --
638 -- Service history function returns result in days, whereas band details
639 -- are held in years on the contract type. So, we must convert the figure.
640 --
641
642 l_service_length := l_service_length / 365;
643
644 FOR l_band_number IN 1 .. 5
645 LOOP
646 --
647 l_column_name := 'Service History Band ' || TO_CHAR(l_band_number);
648 OPEN c_service_factor(l_contract_type, l_column_name);
649 FETCH c_service_factor INTO l_service_factor, l_lower, l_upper;
650 CLOSE c_service_factor;
651 EXIT WHEN l_service_length BETWEEN l_lower AND l_upper;
652 l_service_factor := 0;
653 --
654 END LOOP;
655
656 hr_utility.set_location('Leaving:' || l_proc_name, 20);
657 RETURN l_service_factor;
658 EXCEPTION
659 WHEN OTHERS THEN
660 clear_cache;
661 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
662 debug_others(l_proc_name,l_proc_step);
663 IF g_debug THEN
664 debug('Leaving: '||l_proc_name,-999);
665 END IF;
666 fnd_message.raise_error;
667 ELSE
668 RAISE;
669 END IF;
670 END service_history_factor;
671 -- ----------------------------------------------------------------------------
672 -- |--------------------------< get_annualization_factor >--------------------------|
673 -- ----------------------------------------------------------------------------
674 --
675 -- Description: Get the contract value from the table PQP_CONTRACT_TYPES
676 -- for a given contract type and contract attribute
677 --
678 FUNCTION get_annualization_factor
679 (p_assignment_id IN NUMBER
680 ,p_effective_date IN DATE
681 ,p_business_group_id IN NUMBER
682 ,p_contract_type IN VARCHAR2
683 ,p_time_dimension IN VARCHAR2
684 ) RETURN NUMBER
685 IS
686
687 CURSOR csr_current_pay_frequency
688 (p_assignment_id NUMBER
689 ,p_effective_date DATE
690 )
691 IS
692 SELECT types.number_per_fiscal_year annualization_factor
693 FROM per_all_assignments_f assign
694 ,per_time_periods period
695 ,per_time_period_types types
696 WHERE assign.assignment_id = p_assignment_id
697 AND period.payroll_id = assign.payroll_id
698 AND p_effective_date BETWEEN period.start_date
699 AND period.end_date
700 AND types.period_type = period.period_type
701 AND p_effective_date BETWEEN assign.effective_start_date
702 AND assign.effective_end_date;
703
704
705 CURSOR csr_number_per_fiscal_year
706 (p_period_type IN VARCHAR2
707 ) IS
708 SELECT number_per_fiscal_year
709 FROM per_time_period_types
710 WHERE period_type = p_period_type;
711
712 l_proc_step NUMBER(20,10):=0;
713 l_proc_name VARCHAR2(61):=
714 g_package_name||'get_annualization_factor';
715
716 l_dim_annualization_factor NUMBER;
717 l_current_pay_frequency per_time_period_types.number_per_fiscal_year%TYPE;
718 l_biweekly_pay_frequency per_time_period_types.number_per_fiscal_year%TYPE:=26;
719 l_weekly_pay_frequency per_time_period_types.number_per_fiscal_year%TYPE:=52;
720 l_monthly_pay_frequency per_time_period_types.number_per_fiscal_year%TYPE:=12;
721 l_base_frequency per_time_period_types.number_per_fiscal_year%TYPE:=12;
722 l_pay_frequency_factor NUMBER:= 1;
723 l_contract_factor NUMBER;
724 l_column_name pay_user_columns.user_column_name%TYPE;
725
726 BEGIN
727
728 g_debug := hr_utility.debug_enabled;
729 IF g_debug THEN
730 debug_enter(l_proc_name);
731 debug('p_assignment_id:'||p_assignment_id);
732 debug('p_effective_date:'||fnd_date.date_to_canonical(p_effective_date));
733 debug('p_business_group_id:'||p_business_group_id);
734 debug('p_contract_type:'||p_contract_type);
735 debug('p_time_dimension:'||p_time_dimension);
736 END IF;
737
738 -- the time dimension here could be both source or to
739 -- the purpose of the following statements is to return a conversion factor
740 -- for a given time dimension. The conversion factor may then be used
741 -- for either to or fro conversions. Some dimensions like O are only
742 -- "to" time dimensions. Tho there is no restriction on our part to do so.
743
744
745 IF p_time_dimension = 'A'
746 THEN
747 l_proc_step := 10;
748 IF g_debug THEN
749 debug(l_proc_name,l_proc_step);
750 END IF;
751 l_dim_annualization_factor := 1;
752 ELSE
753 l_proc_step := 20;
754 IF g_debug THEN
755 debug(l_proc_name,l_proc_step);
756 END IF;
757 IF p_time_dimension <> 'PAY'
758 THEN
759 l_proc_step := 20;
760 IF g_debug THEN
761 debug(l_proc_name,l_proc_step);
762 END IF;
763 l_column_name := hr_general.decode_lookup('PQP_TIME_DIMENSION_FACTORS',p_time_dimension);
764 ELSE
765 -- get the assignments pay frequency annualization factor
766 OPEN csr_current_pay_frequency(p_assignment_id,p_effective_date);
767 FETCH csr_current_pay_frequency INTO l_current_pay_frequency;
768 CLOSE csr_current_pay_frequency;
769
770 l_proc_step := 30;
771 IF g_debug THEN
772 debug(l_proc_name,l_proc_step);
773 END IF;
774
775 -- mod payfrequency,26 (hence use biweekly)
776 IF MOD(l_current_pay_frequency,l_biweekly_pay_frequency) = 0
777 THEN
778 -- pay frequency is a weekly multiple
779 --e.g. for a Bi-Week = Periodic Value * (Weekly Payroll Divisor * 26/52) =
780 -- Periodic Value * (Weekly Payroll Divisor * 1/2)
781 -- i.e. for Bi-Week = 52.14 * l_annualization_factor = 26 / l_week_ann_factor=52
782
783 l_proc_step := 35;
784 IF g_debug THEN
785 debug(l_proc_name,l_proc_step);
786 END IF;
787
788 l_column_name := 'WEEKLY PAYROLL DIVISOR';
789 l_base_frequency := l_weekly_pay_frequency;
790
791 ELSE
792 -- pay frequency is a monthly multiple
793 -- e.g. for a Quarter Period
794 -- l_multiplier = 12 * 4 / 12
795
796 l_proc_step := 40;
797 IF g_debug THEN
798 debug(l_proc_name,l_proc_step);
799 END IF;
800
801 l_column_name := 'MONTHLY PAYROLL DIVISOR';
802 l_base_frequency := l_monthly_pay_frequency;
803
804 END IF;
805
806 l_pay_frequency_factor := l_current_pay_frequency/l_base_frequency;
807
808 END IF; -- p_time_dimension <> 'PAY'
809
810 IF g_debug THEN
811 debug('l_column_name:'||l_column_name);
812 END IF;
813
814 IF l_column_name IS NOT NULL
815 THEN
816
817 -- Get the factor value from the contracts table
818 BEGIN
819 l_contract_factor := fnd_number.canonical_to_number(
820 hruserdt.get_table_value
821 (p_bus_group_id => p_business_group_id
822 ,p_table_name => c_contract_table_name
823 ,p_col_name => l_column_name
824 ,p_row_value => p_contract_type
825 ,p_effective_date => p_effective_date
826 ));
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 l_contract_factor := NULL;
830 END;
831 END IF; -- End if of column name is not null check ...
832
833 -- the dimension annualization factor is the factor defined in the contract
834 -- multiplied by the pay frequency conversion, if any
835
836 l_proc_step := 40;
837 IF g_debug THEN
838 debug('l_contract_factor:'||l_contract_factor);
839 debug('l_pay_frequency_factor:'||l_pay_frequency_factor);
840 END IF;
841
842 l_dim_annualization_factor := l_contract_factor * NVL(l_pay_frequency_factor,1);
843
844 END IF; -- IF p_time_dimension = 'A'
845
846
847 IF l_dim_annualization_factor IS NULL
848 THEN
849 l_proc_step := 50;
850 IF g_debug THEN
851 debug(l_proc_name,l_proc_step);
852 END IF;
853
854 hr_utility.set_message(8303, 'PQP_230513_INVALID_CNTRCT_TYPE');
855 hr_utility.raise_error;
856
857 END IF;
858
859 IF g_debug THEN
860 debug('l_dim_annualization_factor:'||l_dim_annualization_factor);
861 debug_exit(l_proc_name);
862 END IF;
863
864 RETURN l_dim_annualization_factor;
865
866 EXCEPTION
867 WHEN OTHERS THEN
868 clear_cache;
869 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
870 debug_others(l_proc_name,l_proc_step);
871 IF g_debug THEN
872 debug('Leaving: '||l_proc_name,-999);
873 END IF;
874 fnd_message.raise_error;
875 ELSE
876 RAISE;
877 END IF;
878 END get_annualization_factor;
879 --
880 --
881 -- ----------------------------------------------------------------------------
882 -- |--------------------------< convert_values >------------------------------|
883 -- ----------------------------------------------------------------------------
884 --
885 -- Description: Do time dimension, fte, service hist conversion if necessary
886 --
887 -- dependency : pqpgbtp1.pkb: l_value := pqp_rates_history_calc.convert_values
888 FUNCTION convert_values
889 (p_assignment_id IN NUMBER
890 ,p_date IN DATE
891 ,p_value IN NUMBER
892 ,p_to_time_dim IN VARCHAR2
893 ,p_from_time_dim IN VARCHAR2
894 ,p_fte IN VARCHAR2
895 ,p_service_history IN VARCHAR2
896 ,p_term_time_yes_no IN VARCHAR2
897 ,p_contract_type IN VARCHAR2 DEFAULT NULL
898 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
899 ) RETURN NUMBER
900 IS
901 l_proc_step NUMBER(20,10):=0;
902 l_proc_name VARCHAR2(61)
903 := g_package_name||'convert_values';
904
905 l_column_name VARCHAR2(80);
906 l_divisor NUMBER;
907 l_multiplier NUMBER;
908 l_annual_value NUMBER;
909 l_annual_hours NUMBER;
910 l_annual_term_time_hours NUMBER;
911 l_term_time_adjustment NUMBER;
912 l_fte_value NUMBER;
913 l_service_history_factor NUMBER;
914 l_element_rate NUMBER;
915 l_business_group_id pay_user_tables.business_group_id%TYPE;
916
917 CURSOR csr_get_contract_type
918 IS
919 SELECT contract_type
920 FROM pqp_assignment_attributes_f
921 WHERE assignment_id = p_assignment_id
922 AND p_date BETWEEN effective_start_date
923 AND effective_end_date;
924
925 l_contract_type pay_user_rows_f.row_low_range_or_name%TYPE;
926 l_to_time_dimension fnd_lookup_values.lookup_code%TYPE;
927
928 BEGIN
929
930 g_debug := hr_utility.debug_enabled;
931 IF g_debug THEN
932 debug_enter(l_proc_name);
933 debug('p_assignment_id:'||p_assignment_id);
934 debug('p_date:'||fnd_date.date_to_canonical(p_date));
935 debug('p_value:'||p_value);
936 debug('p_to_time_dim:'||p_to_time_dim);
937 debug('p_from_time_dim:'||p_from_time_dim);
938 debug('p_fte:'||p_fte);
939 debug('p_service_history:'||p_service_history);
940 debug('p_term_time_yes_no:'||p_term_time_yes_no);
941 debug('p_contract_type:'||p_contract_type);
942 debug('p_contract_type_usage:'||p_contract_type_usage);
943 END IF;
944
945 -- Get the business group id by passing the Assignment Id
946 l_business_group_id := get_bus_grp_id(p_assignment_id => p_assignment_id);
947
948 l_proc_step := 10;
949 IF g_debug THEN
950 debug(l_proc_name,l_proc_step);
951 END IF;
952
953 l_contract_type := NULL;
954
955 IF p_contract_type IS NOT NULL
956 AND
957 p_contract_type_usage = c_overrides_asg_contract
958 THEN
959 -- don't fetch asg contract use the passed contract
960 l_proc_step := 12;
961 IF g_debug THEN
962 debug(l_proc_name,l_proc_step);
963 END IF;
964
965 l_contract_type := p_contract_type;
966 ELSE
967 -- use assignment contract and if a default is supplied
968 -- then use default where assignment contract is not found
969 l_proc_step := 14;
970 IF g_debug THEN
971 debug(l_proc_name,l_proc_step);
972 END IF;
973 OPEN csr_get_contract_type;
974 FETCH csr_get_contract_type INTO l_contract_type;
975 IF csr_get_contract_type%NOTFOUND
976 OR
977 l_contract_type is NULL
978 THEN
979 l_proc_step := 16;
980 IF g_debug THEN
981 debug(l_proc_name,l_proc_step);
982 END IF;
983
984 IF p_contract_type IS NOT NULL
985 AND
986 p_contract_type_usage = c_defaults_asg_contract
987 THEN
988 l_proc_step := 18;
989 IF g_debug THEN
990 debug(l_proc_name,l_proc_step);
991 END IF;
992 l_contract_type := p_contract_type;
993 END IF;
994 END IF;
995 CLOSE csr_get_contract_type;
996 --
997 END IF; -- IF contract is overriden
998
999 l_proc_step := 20;
1000 IF g_debug THEN
1001 debug(l_proc_name,l_proc_step);
1002 debug('l_contract_type:'||l_contract_type);
1003 END IF;
1004
1005 -- if the "to time dimension" is not provided the user has requested
1006 -- the same time dimension has the source
1007 -- note this can only take place if the rate was an 'E' element type
1008
1009 l_to_time_dimension := NVL(p_to_time_dim,p_from_time_dim);
1010
1011 IF p_from_time_dim <> l_to_time_dimension
1012 THEN
1013
1014 IF l_contract_type IS NULL
1015 THEN
1016 hr_utility.set_message(8303, 'PQP_230113_AAT_MISSING_CONTRCT');
1017 -- ver 115.35 : anshghos : setting token value
1018 fnd_message.set_token('EFFECTIVEDATE',fnd_date.date_to_canonical(p_date));
1019 hr_utility.raise_error;
1020 END IF;
1021
1022 -- Modified code to improve performance
1023 -- BUG 3454641
1024 -- Call local function to get the multiplier value
1025
1026 l_multiplier := get_annualization_factor
1027 (p_assignment_id => p_assignment_id
1028 ,p_business_group_id => l_business_group_id
1029 ,p_effective_date => p_date
1030 ,p_contract_type => l_contract_type
1031 ,p_time_dimension => p_from_time_dim
1032 );
1033
1034 -- Convert source time dimension to annual value
1035
1036 l_annual_value := p_value * l_multiplier;
1037
1038 l_proc_step := 30;
1039 IF g_debug THEN
1040 debug(l_proc_name,l_proc_step);
1041 END IF;
1042
1043 -- Call local function to get the divisor value
1044
1045 l_divisor := get_annualization_factor
1046 (p_assignment_id => p_assignment_id
1047 ,p_business_group_id => l_business_group_id
1048 ,p_effective_date => p_date
1049 ,p_contract_type => l_contract_type
1050 ,p_time_dimension => l_to_time_dimension
1051 );
1052
1053 l_proc_step := 35;
1054 IF g_debug THEN
1055 debug(l_proc_name,l_proc_step);
1056 END IF;
1057
1058 -- Convert annual value to requested time dimension
1059
1060 l_element_rate := l_annual_value / l_divisor;
1061
1062 -- Commented out the following lines of code
1063 -- to improve performance
1064 -- BUG 3454641
1065
1066 ELSE
1067
1068 -- Requested dimension was same as stored dimension.
1069 -- Therefore no conversion required.
1070
1071 l_element_rate := p_value;
1072
1073 END IF; -- End if of p_from_time_dim <> p_to_time_dim check ...
1074
1075
1076 l_proc_step := 50;
1077 IF g_debug THEN
1078 debug(l_proc_name,l_proc_step);
1079 debug('l_element_rate:'||l_element_rate);
1080 END IF;
1081
1082 --
1083 -- Adjust figure for term time hours, if necessary
1084 --
1085 IF p_term_time_yes_no = 'Y'
1086 THEN
1087
1088 -- BUG FIX 3570444
1089 -- Handle exception no_data_found explicitly
1090 -- for old customers who have set up rates history prior
1091 -- to adding the new segment term time hours
1092 --
1093 -- Comment out the following code
1094 -- use function to get the value from UDT instead
1095 -- BUG 3454641
1096 BEGIN
1097
1098 l_annual_term_time_hours :=
1099 fnd_number.canonical_to_number( hruserdt.get_table_value
1100 (p_bus_group_id => l_business_group_id
1101 ,p_table_name => c_contract_table_name
1102 ,p_col_name => 'ANNUAL TERM TIME HOURS'
1103 ,p_row_value => l_contract_type
1104 ,p_effective_date => p_date
1105 ));
1106 EXCEPTION
1107 WHEN NO_DATA_FOUND THEN
1108 l_proc_step := 65;
1109 IF g_debug THEN
1110 debug(l_proc_name,l_proc_step);
1111 END IF;
1112 l_annual_term_time_hours := NULL;
1113 END;
1114
1115 IF g_debug THEN
1116 debug('l_annual_term_time_hours:'||l_annual_term_time_hours);
1117 END IF;
1118
1119 IF l_annual_term_time_hours IS NOT NULL
1120 THEN
1121
1122 --
1123 -- Comment out the following code
1124 -- use function to get the value from UDT instead
1125 -- BUG 3454641
1126
1127 BEGIN
1128 l_annual_hours :=
1129 fnd_number.canonical_to_number( hruserdt.get_table_value
1130 (p_bus_group_id => l_business_group_id
1131 ,p_table_name => c_contract_table_name
1132 ,p_col_name => 'ANNUAL HOURS'
1133 ,p_row_value => l_contract_type
1134 ,p_effective_date => p_date
1135 ));
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 l_proc_step := 75;
1139 IF g_debug THEN
1140 debug(l_proc_name,l_proc_step);
1141 END IF;
1142 l_annual_hours := NULL;
1143 END;
1144
1145 IF g_debug THEN
1146 debug('l_annual_hours:'||l_annual_hours);
1147 END IF;
1148
1149 IF l_annual_hours IS NOT NULL
1150 AND l_annual_hours <> l_annual_term_time_hours
1151 THEN
1152
1153 l_term_time_adjustment := l_annual_term_time_hours / l_annual_hours;
1154
1155 IF g_debug THEN
1156 debug('l_term_time_adjustment:'||l_term_time_adjustment);
1157 END IF;
1158
1159 l_element_rate := l_element_rate * l_term_time_adjustment;
1160
1161 END IF;
1162 --
1163 END IF;
1164 --
1165 END IF; -- p_term_time_yes_no = 'Y'
1166
1167 l_proc_step := 85;
1168 IF g_debug THEN
1169 debug(l_proc_name,l_proc_step);
1170 debug('l_element_rate:'||l_element_rate);
1171 END IF;
1172
1173 --
1174 -- Apply FTE and Service History if needed
1175 --
1176
1177 IF p_fte = 'Y'
1178 --
1179 -- BUGFix 2895930 , if the p_fte = "Yes - Exlcuding Hourly Rates"
1180 -- then apply FTE only when the "to time dimension" is not Hourly.
1181 --
1182 -- For backward compatibility if the FTE switch is Yes then
1183 -- we still apply the FTE , regardless of the time dimension.
1184 -- this is because some customers may have implemented workarounds
1185 -- such has creating multiple elements or dividing the hourly rate
1186 -- back up by FTE in their custom code or formulae.
1187 --
1188 OR ( p_fte = 'H' AND p_to_time_dim NOT IN ( 'H','O' ) )
1189 THEN
1190
1191 l_fte_value :=
1192 pqp_fte_utilities.get_fte_value
1193 (p_assignment_id => p_assignment_id
1194 ,p_calculation_date => p_date
1195 );
1196
1197 IF g_debug THEN
1198 debug('l_fte_value:'||l_fte_value);
1199 END IF;
1200
1201 l_element_rate := l_element_rate * NVL(l_fte_value, 1);
1202
1203 END IF;
1204
1205 l_proc_step := 92;
1206 IF g_debug THEN
1207 debug(l_proc_name,l_proc_step);
1208 debug('l_element_rate:'||l_element_rate);
1209 END IF;
1210
1211
1212 IF p_service_history = 'Y'
1213 THEN
1214
1215 l_service_history_factor :=
1216 service_history_factor
1217 (p_assignment_id => p_assignment_id
1218 ,p_date => p_date
1219 );
1220
1221 IF g_debug THEN
1222 debug('l_service_history_factor(%age):'||l_service_history_factor);
1223 END IF;
1224
1225 l_element_rate :=
1226 l_element_rate +
1227 ((l_service_history_factor / 100) * l_element_rate );
1228
1229 END IF;
1230
1231 IF g_debug THEN
1232 debug('l_element_rate:'||l_element_rate);
1233 debug_exit(l_proc_name);
1234 END IF;
1235
1236 RETURN l_element_rate;
1237
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240 clear_cache;
1241 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1242 debug_others(l_proc_name,l_proc_step);
1243 IF g_debug THEN
1244 debug('Leaving: '||l_proc_name,-999);
1245 END IF;
1246 fnd_message.raise_error;
1247 ELSE
1248 RAISE;
1249 END IF;
1250 END convert_values;
1251 -- ----------------------------------------------------------------------------
1252 -- |-------------------------< apply_factor_or_percentage >----------------------------|
1253 -- ----------------------------------------------------------------------------
1254 FUNCTION apply_factor_or_percentage
1255 (p_assignment_id IN NUMBER
1256 ,p_rate IN NUMBER
1257 ,p_type_factor_or_percentage IN VARCHAR2
1258 ,p_factor_or_percentage_value IN NUMBER
1259 ,p_element_type_id IN NUMBER
1260 ,p_input_value IN VARCHAR2
1261 ,p_effective_date IN DATE
1262 ,p_lookup_input_values_yn IN VARCHAR2
1263 ) RETURN NUMBER
1264 IS
1265
1266 l_proc_step NUMBER(20,10):=0;
1267 l_proc_name VARCHAR2(61):=
1268 g_package_name||'apply_factor_or_percentage';
1269
1270 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1271 l_input_value_default_txt pay_input_values_f.default_value%TYPE;
1272 l_input_value_lookup_type pay_input_values_f.lookup_type%TYPE;
1273 l_input_value_value_set_id pay_input_values_f.value_set_id%TYPE;
1274 l_link_default_value_txt pay_link_input_values_f.default_value%TYPE;
1275 l_entry_value_txt pay_element_entry_values_f.screen_entry_value%TYPE;
1276 l_factor_or_percentage NUMBER;
1277 l_modified_rate NUMBER;
1278
1279 BEGIN
1280
1281 g_debug := hr_utility.debug_enabled;
1282
1283 IF g_debug THEN
1284 debug_enter(l_proc_name);
1285 debug('p_assignment_id:'||p_assignment_id);
1286 debug('p_rate:'||p_rate);
1287 debug('p_type_factor_or_percentage:'||p_type_factor_or_percentage);
1288 debug('p_factor_or_percentage_value:'||p_factor_or_percentage_value);
1289 debug('p_element_type_id:'||p_element_type_id);
1290 debug('p_input_value:'||p_input_value);
1291 debug('p_effective_date:'||p_effective_date);
1292 END IF;
1293
1294 IF p_input_value IS NOT NULL
1295 THEN
1296
1297 OPEN csr_input_value_id
1298 (p_element_type_id => p_element_type_id
1299 ,p_input_value_name_in_caps => UPPER(p_input_value)
1300 ,p_effective_date => p_effective_date
1301 );
1302 FETCH csr_input_value_id
1303 INTO l_input_value_id
1304 ,l_input_value_default_txt
1305 ,l_input_value_lookup_type
1306 ,l_input_value_value_set_id;
1307 CLOSE csr_input_value_id;
1308
1309 IF g_debug THEN
1310 debug('l_input_value_id:'||l_input_value_id);
1311 debug('l_input_value_default_txt:'||l_input_value_default_txt);
1312 debug('l_input_value_lookup_type:'||l_input_value_lookup_type);
1313 debug('l_input_value_value_set_id:'||l_input_value_value_set_id);
1314 END IF;
1315
1316 OPEN csr_element_entry_value
1317 (p_assignment_id => p_assignment_id
1318 ,p_element_type_id => p_element_type_id
1319 ,p_input_value_id => l_input_value_id
1320 ,p_effective_date => p_effective_date
1321 );
1322 FETCH csr_element_entry_value
1323 INTO l_entry_value_txt, l_link_default_value_txt;
1324 CLOSE csr_element_entry_value;
1325
1326 IF g_debug THEN
1327 debug('l_entry_value_txt:'||l_entry_value_txt);
1328 debug('l_link_default_value_txt:'||l_link_default_value_txt);
1329 END IF;
1330
1331 -- hot default the entry value to use
1332 -- i.e. if entry value is null, use link default
1333 -- if link default is null, use input value default
1334
1335 l_entry_value_txt :=
1336 NVL(l_entry_value_txt
1337 ,NVL(l_link_default_value_txt
1338 ,l_input_value_default_txt
1339 )
1340 );
1341
1342 IF ( l_input_value_lookup_type IS NOT NULL
1343 OR
1344 l_input_value_value_set_id IS NOT NULL
1345 )
1346 AND
1347 p_lookup_input_values_yn = 'Y' -- for backward compatbility
1348 AND
1349 l_entry_value_txt IS NOT NULL
1350 THEN
1351
1352 l_entry_value_txt :=
1353 pay_ele_shd.convert_lookups(l_input_value_id, l_entry_value_txt);
1354
1355 END IF; -- IF ( l_input_value_lookup_type IS NOT NULL
1356
1357 l_factor_or_percentage := fnd_number.canonical_to_number(l_entry_value_txt);
1358
1359 IF g_debug THEN
1360 debug('l_factor_or_percentage:'||l_factor_or_percentage);
1361 END IF;
1362
1363
1364 ELSE -- IF p_input_name IS not null then
1365
1366 l_factor_or_percentage := p_factor_or_percentage_value ;
1367
1368 END IF; -- IF p_input_name IS not null then
1369
1370 IF g_debug THEN
1371 debug('l_factor_or_percentage:'||l_factor_or_percentage);
1372 END IF;
1373
1374 IF p_type_factor_or_percentage = 'PERCENT'
1375 THEN
1376
1377 l_modified_rate := p_rate * ( l_factor_or_percentage / 100 ) ;
1378
1379 ELSIF p_type_factor_or_percentage = 'FACTOR' THEN
1380
1381 l_modified_rate := p_rate * l_factor_or_percentage ;
1382
1383 END IF;
1384
1385 l_modified_rate := NVL(l_modified_rate,0);
1386
1387 IF g_debug THEN
1388 debug('l_modified_rate:'||l_modified_rate);
1389 debug_exit(l_proc_name);
1390 END IF;
1391
1392 RETURN l_modified_rate;
1393
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396 clear_cache;
1397 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1398 debug_others(l_proc_name,l_proc_step);
1399 IF g_debug THEN
1400 debug('Leaving: '||l_proc_name,-999);
1401 END IF;
1402 fnd_message.raise_error;
1403 ELSE
1404 RAISE;
1405 END IF;
1406 END apply_factor_or_percentage;
1407 --
1408 --
1409 --
1410 FUNCTION get_element_entry_value
1411 (p_element_type_id IN NUMBER
1412 ,p_element_entry_id IN NUMBER
1413 ,p_input_value_name IN VARCHAR2
1414 ,p_effective_date IN DATE
1415 ,p_lookup_input_values_yn IN VARCHAR2
1416 ) RETURN pay_element_entry_values_f.screen_entry_value%TYPE
1417 IS
1418
1419 l_proc_step NUMBER(20,10);
1420 l_proc_name VARCHAR2(61):=
1421 g_package_name||'get_element_entry_value';
1422
1423 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1424 l_input_value_default_txt pay_input_values_f.default_value%TYPE;
1425 l_input_value_lookup_type pay_input_values_f.lookup_type%TYPE;
1426 l_input_value_value_set_id pay_input_values_f.value_set_id%TYPE;
1427 l_link_default_value_txt pay_link_input_values_f.default_value%TYPE;
1428 l_entry_value_txt pay_element_entry_values_f.screen_entry_value%TYPE;
1429
1430 BEGIN
1431
1432 debug_enter(l_proc_name);
1433 IF g_debug THEN
1434 debug('p_element_type_id:'||p_element_type_id);
1435 debug('p_input_value_name:'||p_input_value_name);
1436 debug('p_effective_date:'||fnd_date.date_to_canonical(p_effective_date));
1437 debug('p_lookup_input_values_yn:'||p_lookup_input_values_yn);
1438 END IF;
1439
1440 OPEN csr_input_value_id
1441 (p_element_type_id => p_element_type_id
1442 ,p_input_value_name_in_caps => p_input_value_name
1443 ,p_effective_date => p_effective_date
1444 );
1445 FETCH csr_input_value_id
1446 INTO l_input_value_id
1447 ,l_input_value_default_txt
1448 ,l_input_value_lookup_type
1449 ,l_input_value_value_set_id;
1450 CLOSE csr_input_value_id;
1451
1452 IF g_debug THEN
1453 debug('l_input_value_id:'||l_input_value_id);
1454 debug('l_input_value_default_txt:'||l_input_value_default_txt);
1455 debug('l_input_value_lookup_type:'||l_input_value_lookup_type);
1456 debug('l_input_value_value_set_id:'||l_input_value_value_set_id);
1457 END IF;
1458
1459 OPEN csr_given_element_entry_value
1460 (p_element_entry_id => p_element_entry_id
1461 ,p_input_value_id => l_input_value_id
1462 ,p_effective_date => p_effective_date
1463 );
1464 FETCH csr_given_element_entry_value INTO l_entry_value_txt, l_link_default_value_txt;
1465 CLOSE csr_given_element_entry_value;
1466
1467 IF g_debug THEN
1468 debug('l_entry_value_txt:'||l_entry_value_txt);
1469 debug('l_link_default_value_txt:'||l_link_default_value_txt);
1470 END IF;
1471
1472 -- hot default the entry value to use
1473 -- i.e. if entry value is null, use link default
1474 -- if link default is null, use input value default
1475
1476 l_entry_value_txt := NVL(l_entry_value_txt
1477 ,NVL(l_link_default_value_txt
1478 ,l_input_value_default_txt
1479 )
1480 );
1481 l_proc_step := 10;
1482 IF g_debug THEN
1483 debug(l_proc_name,l_proc_step);
1484 debug('l_entry_value_txt:'||l_entry_value_txt);
1485 END IF;
1486
1487 IF ( l_input_value_lookup_type IS NOT NULL
1488 OR
1489 l_input_value_value_set_id IS NOT NULL
1490 )
1491 AND
1492 p_lookup_input_values_yn = 'Y' -- for backward compatbility
1493 AND
1494 l_entry_value_txt IS NOT NULL
1495 THEN
1496
1497 l_entry_value_txt :=
1498 pay_ele_shd.convert_lookups(l_input_value_id, l_entry_value_txt);
1499
1500 END IF; -- IF ( l_input_value_lookup_type IS NOT NULL
1501
1502 IF g_debug THEN
1503 debug('l_entry_value_txt:'||l_entry_value_txt);
1504 END IF;
1505
1506 debug_exit(l_proc_name);
1507 RETURN l_entry_value_txt;
1508
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 clear_cache;
1512 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1513 debug_others(l_proc_name,l_proc_step);
1514 IF g_debug THEN
1515 debug('Leaving: '||l_proc_name,-999);
1516 END IF;
1517 fnd_message.raise_error;
1518 ELSE
1519 RAISE;
1520 END IF;
1521 END get_element_entry_value;
1522 --
1523 --
1524 --
1525 FUNCTION get_user_table_value
1526 (p_business_group_id IN NUMBER
1527 ,p_table_name IN VARCHAR2
1528 ,p_column_name IN VARCHAR2
1529 ,p_row_value IN VARCHAR2
1530 ,p_effective_date IN DATE
1531 ) RETURN NUMBER
1532 IS
1533 l_proc_step NUMBER(20,10);
1534 l_proc_name VARCHAR2(61):=
1535 g_package_name||'get_user_table_value';
1536
1537 l_value NUMBER;
1538
1539 BEGIN
1540
1541 debug_enter(l_proc_name);
1542
1543 IF g_debug THEN
1544 debug('p_business_group_id:'||p_business_group_id);
1545 debug('p_table_name:'||p_table_name);
1546 debug('p_column_name:'||p_column_name);
1547 debug('p_row_value:'||p_row_value);
1548 debug('p_effective_date:'||fnd_date.date_to_canonical(p_effective_date));
1549 END IF;
1550
1551 BEGIN -- wrapping the hruserdt.get_table_value call
1552
1553 l_value :=
1554 TO_NUMBER
1555 (hruserdt.get_table_value
1556 (p_bus_group_id => p_business_group_id
1557 ,p_table_name => p_table_name
1558 ,p_col_name => p_column_name
1559 ,p_row_value => p_row_value
1560 ,p_effective_date => p_effective_date
1561 )
1562 );
1563
1564 l_proc_step := 10;
1565 IF g_debug THEN
1566 debug('l_value:'||l_value);
1567 END IF;
1568
1569 EXCEPTION
1570 WHEN NO_DATA_FOUND THEN
1571 l_proc_step := 15;
1572 IF g_debug THEN
1573 debug(l_proc_name,l_proc_step);
1574 END IF;
1575 l_value := 0;
1576 NULL;
1577 END; -- wrapping the get_table_value
1578
1579 IF g_debug THEN
1580 debug('l_value:'||l_value);
1581 END IF;
1582 debug_exit(l_proc_name);
1583 RETURN l_value;
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 clear_cache;
1588 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1589 debug_others(l_proc_name,l_proc_step);
1590 IF g_debug THEN
1591 debug('Leaving: '||l_proc_name,-999);
1592 END IF;
1593 fnd_message.raise_error;
1594 ELSE
1595 RAISE;
1596 END IF;
1597 END get_user_table_value;
1598 --
1599 -- ----------------------------------------------------------------------------
1600 -- |--------------------------< process_element >-----------------------------|
1601 -- ----------------------------------------------------------------------------
1602 --
1603 -- Description: Retrive all necessary data, and calculate the
1604 -- applicable rate of pay.
1605 -- Paramaters Added for Calculation Factors and Term-Time Hours Check
1606 --
1607 -- pqpgbtp1.pkb: l_element_rate := pqp_rates_history_calc.process_element
1608 FUNCTION process_element(
1609 p_assignment_id IN NUMBER
1610 ,p_date IN DATE
1611 ,p_element_type_id IN NUMBER
1612 ,p_to_time_dim IN VARCHAR2
1613 ,p_fte IN VARCHAR2
1614 ,p_service_history IN VARCHAR2
1615 ,p_pay_source_value IN VARCHAR2
1616 ,p_qualifier IN VARCHAR2
1617 ,p_from_time_dim IN VARCHAR2
1618 ,p_calculation_type IN VARCHAR2
1619 ,p_calculation_value IN NUMBER
1620 ,p_input_value IN VARCHAR2
1621 ,p_term_time_yes_no IN VARCHAR2
1622 ,p_sum_multiple_entries_yn IN VARCHAR2
1623 ,p_lookup_input_values_yn IN VARCHAR2
1624 ,p_column_name_source_type IN VARCHAR2
1625 ,p_column_name_source_name IN VARCHAR2
1626 ,p_row_name_source_type IN VARCHAR2
1627 ,p_row_name_source_name IN VARCHAR2
1628 ,p_contract_type IN VARCHAR2 DEFAULT NULL
1629 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
1630 )
1631 RETURN NUMBER
1632 IS
1633
1634 l_proc_step NUMBER(20,10);
1635 l_proc_name VARCHAR2(61)
1636 := g_package_name ||'process_element';
1637
1638 l_processed_element_rate NUMBER;
1639 l_fetched_rate NUMBER;
1640 l_step_ceiling per_spinal_points.spinal_point%TYPE;
1641 l_step_ceiling_rate NUMBER;
1642 l_user_col_name VARCHAR2(50);
1643 l_business_group_id per_assignments_f.business_group_id%TYPE;
1644 l_error_message VARCHAR2(200) ;
1645 l_error_code NUMBER ;
1646 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1647 l_input_value_default_txt pay_input_values_f.default_value%TYPE;
1648 l_input_value_lookup_type pay_input_values_f.lookup_type%TYPE;
1649 l_input_value_value_set_id pay_input_values_f.value_set_id%TYPE;
1650 l_link_default_value_txt pay_link_input_values_f.default_value%TYPE;
1651 l_entry_value_txt pay_element_entry_values_f.screen_entry_value%TYPE;
1652 l_row_name pay_user_rows_f.ROW_LOW_RANGE_OR_NAME%TYPE;
1653 l_column_name pay_user_columns.USER_COLUMN_NAME%TYPE;
1654 l_element_entry csr_element_entry%ROWTYPE;
1655 --
1656 -- Cursor to get rate from spinal point
1657 --
1658 CURSOR csr_spinal_pay_scale
1659 IS
1660 SELECT TO_NUMBER(pgr.VALUE)
1661 FROM pay_grade_rules_f pgr
1662 ,pay_rates pr
1663 ,per_spinal_point_placements_f spp
1664 ,per_spinal_point_steps_f sps
1665 WHERE spp.assignment_id = p_assignment_id
1666 AND UPPER(pr.NAME) = UPPER(p_qualifier)
1667 AND pgr.rate_type = 'SP'
1668 AND pr.rate_type = 'SP'
1669 AND pgr.business_group_id = l_business_group_id
1670 AND pr.business_group_id = l_business_group_id
1671 AND spp.business_group_id = l_business_group_id
1672 AND sps.business_group_id = l_business_group_id
1673 AND pgr.rate_id = pr.rate_id
1674 AND spp.step_id = sps.step_id
1675 AND sps.spinal_point_id = pgr.grade_or_spinal_point_id
1676 AND p_date BETWEEN spp.effective_start_date AND spp.effective_end_date
1677 AND p_date BETWEEN sps.effective_start_date AND sps.effective_end_date
1678 AND p_date BETWEEN pgr.effective_start_date AND pgr.effective_end_date;
1679
1680 --
1681 --Cursor to get rate from Spinal Point in case of Qualifier with a wildcard
1682 --
1683 CURSOR csr_spinal_pay_scale_like
1684 IS
1685 SELECT TO_NUMBER(pgr.VALUE)
1686 FROM pay_grade_rules_f pgr
1687 ,pay_rates pr
1688 ,per_spinal_point_placements_f spp
1689 ,per_spinal_point_steps_f sps
1690 WHERE spp.assignment_id = p_assignment_id
1691 AND UPPER(pr.NAME) LIKE UPPER(p_qualifier)
1692 AND pgr.rate_type = 'SP'
1693 AND pr.rate_type = 'SP'
1694 AND pgr.business_group_id = l_business_group_id
1695 AND pr.business_group_id = l_business_group_id
1696 AND spp.business_group_id = l_business_group_id
1697 AND sps.business_group_id = l_business_group_id
1698 AND pgr.rate_id = pr.rate_id
1699 AND spp.step_id = sps.step_id
1700 AND sps.spinal_point_id = pgr.grade_or_spinal_point_id
1701 AND p_date BETWEEN spp.effective_start_date AND spp.effective_end_date
1702 AND p_date BETWEEN sps.effective_start_date AND sps.effective_end_date
1703 AND p_date BETWEEN pgr.effective_start_date AND pgr.effective_end_date
1704 ORDER BY pgr.effective_start_date DESC;
1705
1706 --
1707 -- Cursor to get rate from grade scale
1708 --
1709 CURSOR csr_grade_rate
1710 IS
1711 SELECT TO_NUMBER(pgr.VALUE)
1712 FROM per_assignments_f paf, pay_grade_rules_f pgr, pay_rates pr
1713 WHERE paf.assignment_id = p_assignment_id
1714 AND paf.grade_id = pgr.grade_or_spinal_point_id
1715 AND pgr.rate_type = 'G'
1716 AND pgr.rate_id = pr.rate_id
1717 AND pr.rate_type = 'G'
1718 AND paf.business_group_id = l_business_group_id
1719 AND pr.business_group_id = l_business_group_id
1720 AND pr.business_group_id = l_business_group_id
1721 AND UPPER(pr.NAME) = UPPER(p_qualifier)
1722 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date
1723 AND p_date BETWEEN pgr.effective_start_date AND pgr.effective_end_date;
1724
1725 --
1726 --Cursor to get rate from grade scale in case of Qualifier with a wildcard.
1727 --
1728 CURSOR csr_grade_rate_like
1729 IS
1730 SELECT TO_NUMBER(pgr.VALUE)
1731 FROM per_assignments_f paf, pay_grade_rules_f pgr, pay_rates pr
1732 WHERE paf.assignment_id = p_assignment_id
1733 AND paf.grade_id = pgr.grade_or_spinal_point_id
1734 AND pgr.rate_type = 'G'
1735 AND pgr.rate_id = pr.rate_id
1736 AND pr.rate_type = 'G'
1737 AND paf.business_group_id = l_business_group_id
1738 AND pr.business_group_id = l_business_group_id
1739 AND pr.business_group_id = l_business_group_id
1740 AND UPPER(pr.NAME) LIKE UPPER(p_qualifier)
1741 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date
1742 AND p_date BETWEEN pgr.effective_start_date AND pgr.effective_end_date
1743 ORDER BY pgr.effective_start_date DESC;
1744
1745 --
1746 -- Cursor to get rate from global value
1747 --
1748 CURSOR csr_global_value IS
1749 SELECT TO_NUMBER(ffg.global_value)
1750 FROM ff_globals_f ffg
1751 ,per_business_groups_perf pbg
1752 WHERE UPPER(ffg.GLOBAL_NAME) = UPPER(p_qualifier)
1753 AND pbg.business_group_id = l_business_group_id
1754 AND (
1755 (ffg.business_group_id = l_business_group_id
1756 )
1757 OR
1758 ( ffg.business_group_id IS NULL
1759 AND
1760 ffg.legislation_code = pbg.legislation_code
1761 )
1762 OR
1763 (
1764 ffg.business_group_id IS NULL
1765 AND
1766 ffg.legislation_code IS NULL
1767 )
1768 )
1769 AND p_date
1770 BETWEEN ffg.effective_start_date
1771 AND ffg.effective_end_date;
1772
1773
1774
1775 BEGIN
1776
1777 g_debug := hr_utility.debug_enabled;
1778 IF g_debug THEN
1779 debug_enter(l_proc_name);
1780 debug('p_assignment_id:'||p_assignment_id); --IN NUMBER
1781 debug('p_date:'||fnd_date.date_to_canonical(p_date)); --IN DATE
1782 debug('p_element_type_id:'||p_element_type_id); --IN NUMBER
1783 debug('p_to_time_dim:'||p_to_time_dim); --IN VARCHAR2
1784 debug('p_fte:'||p_fte); --IN VARCHAR2
1785 debug('p_service_history:'||p_service_history); --IN VARCHAR2
1786 debug('p_pay_source_value:'||p_pay_source_value); --IN VARCHAR2
1787 debug('p_qualifier:'||p_qualifier); --IN VARCHAR2
1788 debug('p_from_time_dim:'||p_from_time_dim); --IN VARCHAR2
1789 debug('p_calculation_type:'||p_calculation_type); --IN VARCHAR2
1790 debug('p_calculation_value:'||p_calculation_value); --IN NUMBER
1791 debug('p_input_value:'||p_input_value); --IN VARCHAR2
1792 debug('p_term_time_yes_no:'||p_term_time_yes_no); --IN VARCHAR2
1793 debug('p_sum_multiple_entries_yn:'||p_sum_multiple_entries_yn);
1794 debug('p_lookup_input_values_yn:'||p_lookup_input_values_yn);
1795 debug('p_contract_type:'||p_contract_type);
1796 debug('p_contract_type_usage:'||p_contract_type_usage);
1797 debug('p_column_name_source_type:'||p_column_name_source_type);
1798 debug('p_column_name_source_name:'||p_column_name_source_name);
1799 debug('p_row_name_source_type:'||p_row_name_source_type);
1800 debug('p_row_name_source_name:'||p_row_name_source_name);
1801
1802 END IF;
1803
1804 --
1805 l_business_group_id := get_bus_grp_id(p_assignment_id => p_assignment_id);
1806
1807
1808 l_proc_step := 10;
1809 IF g_debug THEN
1810 debug(l_proc_name,l_proc_step);
1811 END IF;
1812
1813 IF p_pay_source_value = 'SP'
1814 THEN
1815
1816 --
1817 OPEN csr_spinal_pay_scale;
1818 FETCH csr_spinal_pay_scale INTO l_fetched_rate;
1819 l_processed_element_rate := l_fetched_rate;
1820 FETCH csr_spinal_pay_scale INTO l_fetched_rate;
1821
1822 IF csr_spinal_pay_scale%FOUND
1823 THEN
1824 l_proc_step := 25;
1825 IF g_debug THEN
1826 debug(l_proc_name,l_proc_step);
1827 END IF;
1828
1829 CLOSE csr_spinal_pay_scale;
1830 hr_utility.set_message(8303, 'PQP_230508_MULTIPLR_SCL_RATES');
1831 hr_utility.raise_error;
1832 --
1833 END IF;
1834
1835 IF csr_spinal_pay_scale%ROWCOUNT = 0
1836 THEN
1837 l_proc_step := 30;
1838 IF g_debug THEN
1839 debug(l_proc_name,l_proc_step);
1840 END IF;
1841 OPEN csr_spinal_pay_scale_like;
1842 FETCH csr_spinal_pay_scale_like INTO l_processed_element_rate;
1843 CLOSE csr_spinal_pay_scale_like;
1844 --
1845 END IF;
1846
1847 CLOSE csr_spinal_pay_scale;
1848 --
1849 ELSIF p_pay_source_value = 'GR'
1850 THEN
1851
1852 OPEN csr_grade_rate;
1853 FETCH csr_grade_rate INTO l_fetched_rate;
1854 l_processed_element_rate := l_fetched_rate;
1855 FETCH csr_grade_rate INTO l_fetched_rate;
1856
1857 IF csr_grade_rate%FOUND
1858 THEN
1859 l_proc_step := 50;
1860 IF g_debug THEN
1861 debug(l_proc_name,l_proc_step);
1862 END IF;
1863 CLOSE csr_grade_rate;
1864 hr_utility.set_message(8303, 'PQP_230509_MULTIPLE_GRD_RATES');
1865 hr_utility.raise_error;
1866 --
1867 END IF;
1868
1869 IF csr_grade_rate%ROWCOUNT = 0
1870 THEN
1871 l_proc_step := 55;
1872 IF g_debug THEN
1873 debug(l_proc_name,l_proc_step);
1874 END IF;
1875 OPEN csr_grade_rate_like;
1876 FETCH csr_grade_rate_like INTO l_processed_element_rate;
1877 CLOSE csr_grade_rate_like;
1878 --
1879 END IF;
1880
1881 CLOSE csr_grade_rate;
1882 --
1883 ELSIF p_pay_source_value = 'GV'
1884 THEN
1885
1886 OPEN csr_global_value;
1887 FETCH csr_global_value INTO l_processed_element_rate;
1888 CLOSE csr_global_value;
1889 --
1890 ELSIF p_pay_source_value = 'IV'
1891 THEN
1892 -- potential for caching exists in this section
1893
1894 OPEN csr_input_value_id
1895 (p_element_type_id => p_element_type_id
1896 ,p_input_value_name_in_caps => UPPER(p_qualifier)
1897 ,p_effective_date => p_date
1898 );
1899 FETCH csr_input_value_id
1900 INTO l_input_value_id
1901 ,l_input_value_default_txt
1902 ,l_input_value_lookup_type
1903 ,l_input_value_value_set_id;
1904 CLOSE csr_input_value_id;
1905
1906 IF g_debug THEN
1907 debug('l_input_value_id:'||l_input_value_id);
1908 debug('l_input_value_default_txt:'||l_input_value_default_txt);
1909 debug('l_input_value_lookup_type:'||l_input_value_lookup_type);
1910 debug('l_input_value_value_set_id:'||l_input_value_value_set_id);
1911 END IF;
1912
1913
1914 IF g_debug THEN
1915 debug('l_input_value_default_txt:'||l_input_value_default_txt);
1916 END IF;
1917
1918 l_processed_element_rate := 0;
1919 l_fetched_rate := 0;
1920 OPEN csr_element_entry_value
1921 (p_assignment_id => p_assignment_id
1922 ,p_element_type_id => p_element_type_id
1923 ,p_input_value_id => l_input_value_id
1924 ,p_effective_date => p_date
1925 );
1926 LOOP
1927 FETCH csr_element_entry_value
1928 INTO l_entry_value_txt, l_link_default_value_txt;
1929 EXIT WHEN csr_element_entry_value%NOTFOUND;
1930
1931
1932 IF g_debug THEN
1933 debug('l_entry_value_txt:'||l_entry_value_txt);
1934 debug('l_link_default_value_txt:'||l_link_default_value_txt);
1935 END IF;
1936
1937
1938 IF g_debug THEN
1939 debug('l_entry_value_txt:'||l_entry_value_txt);
1940 debug('l_link_default_value_txt:'||l_link_default_value_txt);
1941 END IF;
1942
1943
1944 -- hot default the entry value to use
1945 -- i.e. if entry value is null, use link default
1946 -- if link default is null, use input value default
1947
1948 l_entry_value_txt := NVL(l_entry_value_txt
1949 ,NVL(l_link_default_value_txt
1950 ,l_input_value_default_txt
1951 )
1952 );
1953
1954 IF ( l_input_value_lookup_type IS NOT NULL
1955 OR
1956 l_input_value_value_set_id IS NOT NULL
1957 )
1958 AND
1959 p_lookup_input_values_yn = 'Y' -- for backward compatbility
1960 AND
1961 l_entry_value_txt IS NOT NULL
1962 THEN
1963
1964 l_entry_value_txt :=
1965 pay_ele_shd.convert_lookups(l_input_value_id, l_entry_value_txt);
1966
1967 END IF; -- IF ( l_input_value_lookup_type IS NOT NULL
1968
1969 IF l_entry_value_txt IS NOT NULL
1970 THEN
1971 l_fetched_rate := fnd_number.canonical_to_number(l_entry_value_txt);
1972 ELSE
1973 l_fetched_rate := 0;
1974 END IF;
1975
1976 IF g_debug THEN
1977 debug('l_fetched_rate:'||l_fetched_rate);
1978 END IF;
1979
1980 l_processed_element_rate := l_processed_element_rate + l_fetched_rate;
1981
1982 IF g_debug THEN
1983 debug('l_processed_element_rate:'||l_processed_element_rate);
1984 END IF;
1985
1986 IF p_sum_multiple_entries_yn = 'N'
1987 THEN
1988 EXIT; -- quit loop after first iteration for backward compatibility
1989 END IF;
1990
1991 END LOOP;
1992 CLOSE csr_element_entry_value;
1993
1994 IF g_debug THEN
1995 debug('l_processed_element_rate:'||l_processed_element_rate);
1996 END IF;
1997
1998 ELSIF p_pay_source_value = 'RT'
1999 THEN
2000
2001 g_rounding_precision := 38;
2002 l_error_code :=
2003 rates_history
2004 (p_assignment_id => p_assignment_id
2005 ,p_calculation_date => p_date
2006 ,p_name => p_qualifier
2007 ,p_rt_element => 'R'
2008 ,p_to_time_dim => p_from_time_dim
2009 ,p_rate => l_processed_element_rate
2010 ,p_error_message => l_error_message
2011 ,p_contract_type => p_contract_type
2012 ,p_contract_type_usage => p_contract_type_usage
2013 );
2014 g_rounding_precision := 5;
2015
2016 IF l_error_code < 0
2017 THEN
2018 check_error_code(l_error_code,l_error_message);
2019 END IF;
2020
2021 ELSIF p_pay_source_value = 'EN'
2022 THEN
2023
2024 g_rounding_precision := 38;
2025 l_error_code :=
2026 rates_history
2027 (p_assignment_id => p_assignment_id
2028 ,p_calculation_date => p_date
2029 ,p_name => p_qualifier
2030 ,p_rt_element => 'E'
2031 ,p_to_time_dim => p_from_time_dim
2032 ,p_rate => l_processed_element_rate
2033 ,p_error_message => l_error_message
2034 ,p_contract_type => p_contract_type
2035 ,p_contract_type_usage => p_contract_type_usage
2036 );
2037 g_rounding_precision := 5;
2038
2039 IF l_error_code < 0
2040 THEN
2041 check_error_code(l_error_code,l_error_message);
2042 END IF;
2043
2044 ELSIF p_pay_source_value = 'TV'
2045 THEN
2046
2047 l_proc_step := 90;
2048 IF g_debug THEN
2049 debug(l_proc_name,l_proc_step);
2050 END IF;
2051
2052 -- at this stage we have the business_group, the table name in the qualifier
2053 -- column and row names maybe unknown
2054 -- to determine column and row names we need to check their source type first
2055 -- if its "Named in an Input Value" then we need to check entry value
2056 -- link value default, input value default , decode if lookup input values is Yes
2057 -- then use the column and row names.
2058
2059 IF p_column_name_source_type = 'IV'
2060 OR
2061 p_row_name_source_type = 'IV'
2062 THEN
2063
2064 l_proc_step := 92;
2065 IF g_debug THEN
2066 debug(l_proc_name,l_proc_step);
2067 END IF;
2068
2069 l_processed_element_rate := 0;
2070 l_fetched_rate := 0;
2071 OPEN csr_element_entry
2072 (p_assignment_id => p_assignment_id
2073 ,p_element_type_id => p_element_type_id
2074 ,p_effective_date => p_date
2075 );
2076 LOOP
2077 FETCH csr_element_entry INTO l_element_entry;
2078 EXIT WHEN csr_element_entry%NOTFOUND;
2079
2080 IF p_column_name_source_type = 'IV'
2081 THEN
2082
2083 l_proc_step := 95;
2084 IF g_debug THEN
2085 debug(l_proc_name,l_proc_step);
2086 END IF;
2087
2088 l_column_name :=
2089 get_element_entry_value
2090 (p_element_type_id => p_element_type_id
2091 ,p_element_entry_id => l_element_entry.element_entry_id
2092 ,p_input_value_name => p_column_name_source_name
2093 ,p_effective_date => p_date
2094 ,p_lookup_input_values_yn => p_lookup_input_values_yn
2095 );
2096 ELSE
2097 l_column_name := p_column_name_source_name;
2098 END IF;
2099
2100 IF p_row_name_source_type = 'IV' THEN
2101
2102 l_proc_step := 100;
2103 IF g_debug THEN
2104 debug(l_proc_name,l_proc_step);
2105 END IF;
2106
2107 l_row_name :=
2108 get_element_entry_value
2109 (p_element_type_id => p_element_type_id
2110 ,p_element_entry_id => l_element_entry.element_entry_id
2111 ,p_input_value_name => p_row_name_source_name
2112 ,p_effective_date => p_date
2113 ,p_lookup_input_values_yn => p_lookup_input_values_yn
2114 );
2115 ELSE
2116 l_row_name := p_row_name_source_name;
2117 END IF;
2118
2119
2120 l_proc_step := 102;
2121 IF g_debug THEN
2122 debug(l_proc_name,l_proc_step);
2123 END IF;
2124
2125
2126 l_fetched_rate :=
2127 get_user_table_value
2128 (p_business_group_id => l_business_group_id
2129 ,p_table_name => p_qualifier
2130 ,p_column_name => l_column_name
2131 ,p_row_value => l_row_name
2132 ,p_effective_date => p_date
2133 );
2134
2135 l_processed_element_rate := l_processed_element_rate + l_fetched_rate;
2136
2137 IF p_sum_multiple_entries_yn = 'N'
2138 THEN
2139 EXIT; -- quit loop after first iteration for backward compatibility
2140 END IF;
2141
2142 END LOOP; -- EXIT WHEN csr_element_entry%NOTFOUND;
2143 CLOSE csr_element_entry;
2144
2145 ELSE
2146 -- donot have to check whether element is linked
2147 -- row and column name are explictlity defined
2148 -- so can do a get_table_direct and exit from loop.
2149
2150 l_proc_step := 105;
2151 IF g_debug THEN
2152 debug(l_proc_name,l_proc_step);
2153 END IF;
2154
2155
2156 l_processed_element_rate :=
2157 get_user_table_value
2158 (p_business_group_id => l_business_group_id
2159 ,p_table_name => p_qualifier
2160 ,p_column_name => p_column_name_source_name
2161 ,p_row_value => p_row_name_source_name
2162 ,p_effective_date => p_date
2163 );
2164
2165 END IF; -- IF p_column_name_source_type = 'IV' OR ...
2166
2167 ELSE
2168
2169 l_proc_step := 195;
2170 IF g_debug THEN
2171 debug(l_proc_name,l_proc_step);
2172 END IF;
2173 hr_utility.set_message(8303, 'PQP_230510_INVALID_PAY_SRC_VAL');
2174 hr_utility.raise_error;
2175 --
2176 END IF;
2177
2178 l_proc_step := 200;
2179 IF g_debug THEN
2180 debug(l_proc_name,l_proc_step);
2181 debug('l_processed_element_rate:'||l_processed_element_rate);
2182 END IF;
2183
2184
2185 IF l_processed_element_rate IS NOT NULL
2186 THEN
2187
2188 -- call convert values function
2189 -- Added Term-Time Hours Check
2190 l_processed_element_rate :=
2191 convert_values(
2192 p_assignment_id => p_assignment_id
2193 ,p_date => p_date
2194 ,p_value => l_processed_element_rate
2195 ,p_to_time_dim => p_to_time_dim
2196 ,p_from_time_dim => p_from_time_dim
2197 ,p_fte => p_fte
2198 ,p_service_history => p_service_history
2199 ,p_term_time_yes_no => p_term_time_yes_no
2200 ,p_contract_type => p_contract_type
2201 ,p_contract_type_usage => p_contract_type_usage
2202 );
2203
2204 l_proc_step := 210;
2205 IF g_debug THEN
2206 debug(l_proc_name,l_proc_step);
2207 END IF;
2208
2209 -- If Pay Source Value is Rate Type or Element Name then
2210 -- consider the Calculation Part and apply it on Rate Calculated
2211
2212 IF p_pay_source_value IN ('RT','EN')
2213 THEN
2214
2215 l_processed_element_rate :=
2216 apply_factor_or_percentage
2217 (p_assignment_id => p_assignment_id
2218 ,p_rate => l_processed_element_rate
2219 ,p_type_factor_or_percentage => p_calculation_type
2220 ,p_factor_or_percentage_value => p_calculation_value
2221 ,p_element_type_id => p_element_type_id
2222 ,p_input_value => p_input_value
2223 ,p_effective_date => p_date
2224 ,p_lookup_input_values_yn => p_lookup_input_values_yn
2225 );
2226
2227 END IF; -- IF p_pay_source_value in ('RT','EN')
2228
2229 ELSE
2230
2231 l_proc_step := 220;
2232 IF g_debug THEN
2233 debug(l_proc_name,l_proc_step);
2234 END IF;
2235
2236 l_processed_element_rate := 0;
2237
2238 END IF; -- IF l_processed_element_rate IS NOT NULL
2239
2240 IF g_debug THEN
2241 debug('l_processed_element_rate:'||l_processed_element_rate);
2242 debug_exit(l_proc_name);
2243 END IF;
2244 RETURN l_processed_element_rate;
2245
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248 clear_cache;
2249 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2250 debug_others(l_proc_name,l_proc_step);
2251 IF g_debug THEN
2252 debug('Leaving: '||l_proc_name,-999);
2253 END IF;
2254 fnd_message.raise_error;
2255 ELSE
2256 RAISE;
2257 END IF;
2258 END process_element;
2259 --
2260 -- ----------------------------------------------------------------------------
2261 -- |--------------------------< rates_history >------------------------------|
2262 -- ----------------------------------------------------------------------------
2263 --
2264 -- Description: Top level function, returning rate of pay. Can be used
2265 -- for single element or rate type.
2266 --
2267 FUNCTION rates_history
2268 (p_assignment_id IN NUMBER
2269 ,p_calculation_date IN DATE
2270 ,p_name IN VARCHAR2
2271 ,p_rt_element IN VARCHAR2
2272 ,p_to_time_dim IN VARCHAR2
2273 ,p_rate IN OUT NOCOPY NUMBER
2274 ,p_error_message IN OUT NOCOPY VARCHAR2
2275 ,p_contract_type IN VARCHAR2 DEFAULT NULL
2276 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
2277 ) RETURN NUMBER
2278 IS
2279 l_proc_step NUMBER(20,10);
2280 l_proc_name VARCHAR2(61)
2281 := g_package_name ||'rates_history';
2282
2283
2284 CURSOR csr_rate_type_code
2285 (p_rate_type_name VARCHAR2
2286 ) IS
2287 SELECT lookup_code
2288 FROM hr_lookups hrl
2289 WHERE hrl.lookup_type = 'PQP_RATE_TYPE'
2290 AND UPPER(hrl.meaning) = p_rate_type_name;
2291
2292
2293 CURSOR csr_element_attribute_id
2294 (p_element_type_id NUMBER
2295 ) IS
2296 SELECT eei.element_type_extra_info_id
2297 FROM pay_element_type_extra_info eei
2298 WHERE eei.element_type_id = p_element_type_id
2299 AND eei.information_type = 'PQP_UK_ELEMENT_ATTRIBUTION';
2300
2301
2302 l_csr_element_set csr_element_set_typ;
2303 this_element csr_element_type_id%ROWTYPE;
2304 l_business_group_id pay_element_types_f.business_group_id%TYPE;
2305 l_legislation_code pay_element_types_f.legislation_code%TYPE;
2306 l_rate_name fnd_lookup_values.meaning%TYPE;
2307 l_rate_code fnd_lookup_values.meaning%TYPE;
2308 l_element_attribution_id NUMBER;
2309 l_element_entry csr_element_entry%ROWTYPE;
2310
2311 l_fte fnd_lookup_values.lookup_code%TYPE;
2312 l_service_history fnd_lookup_values.lookup_code%TYPE;
2313 l_pay_source_value fnd_lookup_values.lookup_code%TYPE;
2314 l_qualifier pay_element_types_f.element_name%type;
2315 l_from_time_dimension fnd_lookups.lookup_code%TYPE;
2316 l_element_rate NUMBER;
2317 l_total_rate NUMBER;
2318 l_rate_nc NUMBER;
2319 l_error_mesg_nc fnd_new_messages.message_text%TYPE;
2320 l_calc_type fnd_lookup_values.lookup_code%TYPE;
2321 l_calc_value fnd_lookup_values.lookup_code%TYPE;
2322 l_input_value fnd_lookup_values.lookup_code%TYPE;
2323 l_check_link_to_assignment_yn fnd_lookup_values.lookup_code%TYPE;
2324 l_term_time_yes_no fnd_lookup_values.lookup_code%TYPE;
2325 l_linked_to_assignment_yn fnd_lookup_values.lookup_code%TYPE;
2326 l_sum_multiple_entries_yn fnd_lookup_values.lookup_code%TYPE;
2327 l_lookup_input_values_yn fnd_lookup_values.lookup_code%TYPE;
2328 l_column_name_source_type pay_element_type_extra_info.eei_information16%TYPE;
2329 l_column_name_source_name pay_element_type_extra_info.eei_information17%TYPE;
2330 l_row_name_source_type pay_element_type_extra_info.eei_information18%TYPE;
2331 l_row_name_source_name pay_element_type_extra_info.eei_information19%TYPE;
2332
2333 BEGIN
2334
2335 g_debug := hr_utility.debug_enabled;
2336
2337 IF g_debug THEN
2338 debug_enter(l_proc_name);
2339 debug('p_assignment_id:'||p_assignment_id);
2340 debug('p_calculation_date:'||
2341 fnd_date.date_to_canonical(p_calculation_date));
2342 debug('p_name:'||p_name);
2343 debug('p_rt_element:'||p_rt_element);
2344 debug('p_to_time_dim:'||p_to_time_dim);
2345 debug('p_rate(INOUT):'||p_rate);
2346 debug('p_error_message(INOUT):'||p_error_message);
2347 debug('p_contract_type:'||p_contract_type);
2348 debug('p_contract_type_usage:'||p_contract_type_usage);
2349 END IF;
2350
2351 -- nocopy changes
2352 l_rate_nc := p_rate;
2353 l_error_mesg_nc := p_error_message;
2354
2355 l_business_group_id := get_bus_grp_id(p_assignment_id => p_assignment_id);
2356
2357 l_proc_step := 10;
2358 IF g_debug THEN
2359 debug('l_business_group_id:'||l_business_group_id);
2360 debug('g_business_group_id:'||g_business_group_id);
2361 debug('g_legislation_code:'||g_legislation_code);
2362 debug(l_proc_name,l_proc_step);
2363 END IF;
2364
2365 IF g_business_group_id IS NULL -- this caching should be in get_leg...code
2366 OR
2367 g_legislation_code IS NULL
2368 OR
2369 g_business_group_id <> l_business_group_id
2370 THEN
2371 l_proc_step := 15;
2372 IF g_debug THEN
2373 debug(l_proc_name,l_proc_step);
2374 END IF;
2375 g_business_group_id := l_business_group_id;
2376 g_legislation_code := pqp_utilities.pqp_get_legislation_code(l_business_group_id);
2377 END IF;
2378
2379 l_legislation_code := g_legislation_code;
2380
2381 IF g_debug THEN
2382 debug('l_legislation_code:'||l_legislation_code);
2383 END IF;
2384
2385 l_rate_name := UPPER(p_name);
2386
2387 IF p_rt_element = 'R'
2388 THEN
2389
2390 IF g_debug THEN
2391 debug('g_cache_rate_type_name:'||g_cache_rate_type_name);
2392 debug('g_cache_rate_type_code:'||g_cache_rate_type_code);
2393 debug('l_rate_name:'||l_rate_name);
2394 END IF;
2395
2396 IF g_cache_rate_type_name <> l_rate_name
2397 OR
2398 g_cache_rate_type_code IS NULL
2399 OR
2400 g_cache_rate_type_name IS NULL
2401 THEN
2402
2403 OPEN csr_rate_type_code(l_rate_name);
2404 FETCH csr_rate_type_code INTO l_rate_code;
2405 IF csr_rate_type_code%FOUND
2406 THEN
2407 l_proc_step := 30;
2408 IF g_debug THEN
2409 debug(l_proc_name,l_proc_step);
2410 END IF;
2411 g_cache_rate_type_code := l_rate_code;
2412 g_cache_rate_type_name := l_rate_name;
2413 ELSE
2414 l_proc_step := 35;
2415 IF g_debug THEN
2416 debug(l_proc_name,l_proc_step);
2417 END IF;
2418 g_cache_rate_type_code := NULL; -- must do
2419 g_cache_rate_type_name := NULL; -- must do
2420 l_rate_code := NULL; -- must do
2421 l_rate_name := NULL; -- must do
2422 END IF; -- g_cache_rate_type_name
2423 CLOSE csr_rate_type_code;
2424
2425 END IF; -- IF g_cache_rate_type_code IS NULL
2426
2427 l_rate_code := g_cache_rate_type_code;
2428
2429 ELSE -- p_rt_element = 'E'
2430
2431 l_proc_step := 40;
2432 IF g_debug THEN
2433 debug(l_proc_name,l_proc_step);
2434 END IF;
2435
2436 l_rate_code := l_rate_name;
2437
2438 END IF; -- IF p_rt_element = 'R'
2439
2440 IF g_debug THEN
2441 debug('l_rate_code:'||l_rate_code);
2442 debug('l_rate_name:'||l_rate_name);
2443 END IF;
2444 --
2445 -- Loop for each element in a rate type (element set)
2446 --
2447 l_total_rate := 0;
2448
2449
2450 IF l_rate_code IS NOT NULL
2451 THEN
2452
2453 IF p_rt_element = 'R'
2454 THEN
2455
2456 OPEN l_csr_element_set FOR
2457 SELECT ele.element_type_id
2458 FROM pay_element_type_extra_info eei
2459 ,pay_element_types_f ele
2460 WHERE eei.information_type = 'PQP_UK_RATE_TYPE'
2461 AND ele.element_type_id = eei.element_type_id
2462 AND p_calculation_date
2463 BETWEEN ele.effective_start_date
2464 AND ele.effective_end_date
2465 AND eei.eei_information1 = l_rate_code
2466 AND (
2467 ( ele.business_group_id IS NOT NULL
2468 AND
2469 ele.business_group_id = l_business_group_id
2470 )
2471 OR
2472 ( ele.legislation_code = l_legislation_code
2473 AND
2474 ele.business_group_id IS NULL
2475 )
2476 OR
2477 ( ele.legislation_code IS NULL
2478 AND
2479 ele.business_group_id IS NULL
2480 )
2481 );
2482 ELSE
2483
2484 OPEN l_csr_element_set FOR
2485 SELECT ele.element_type_id
2486 FROM pay_element_types_f ele
2487 WHERE UPPER(ele.element_name) = l_rate_name
2488 AND (
2489 ( ele.business_group_id = l_business_group_id
2490 )
2491 OR
2492 ( ele.legislation_code = l_legislation_code
2493 AND
2494 ele.business_group_id IS NULL
2495 )
2496 OR
2497 ( ele.legislation_code IS NULL
2498 AND
2499 ele.business_group_id IS NULL
2500 )
2501 )
2502 AND p_calculation_date BETWEEN ele.effective_start_date
2503 AND ele.effective_end_date;
2504
2505 END IF; -- IF p_rt_element = 'R'
2506
2507 LOOP
2508 FETCH l_csr_element_set INTO this_element;
2509 EXIT WHEN l_csr_element_set%NOTFOUND;
2510
2511 l_proc_step := 65;
2512 IF g_debug THEN
2513 debug(l_proc_name,l_proc_step);
2514 debug('this_element.element_type_id:' ||this_element.element_type_id);
2515 END IF;
2516
2517 OPEN csr_element_attribute_id(this_element.element_type_id);
2518 LOOP
2519 FETCH csr_element_attribute_id INTO l_element_attribution_id;
2520 EXIT WHEN csr_element_attribute_id%NOTFOUND;
2521 -- IF csr_element_attribute_id%FOUND THEN
2522
2523 IF g_debug THEN
2524 debug('l_element_attribution_id:' ||l_element_attribution_id);
2525 END IF;
2526
2527 get_element_attributes(
2528 p_element_type_extra_info_id => l_element_attribution_id
2529 ,p_service_history => l_service_history
2530 ,p_fte => l_fte
2531 ,p_pay_source_value => l_pay_source_value
2532 ,p_qualifier => l_qualifier
2533 ,p_from_time_dim => l_from_time_dimension
2534 ,p_calculation_type => l_calc_type
2535 ,p_calculation_value => l_calc_value
2536 ,p_input_value => l_input_value
2537 ,p_linked_to_assignment => l_check_link_to_assignment_yn
2538 ,p_term_time_yes_no => l_term_time_yes_no
2539 ,p_sum_multiple_entries_yn => l_sum_multiple_entries_yn
2540 ,p_lookup_input_values_yn => l_lookup_input_values_yn
2541 ,p_column_name_source_type => l_column_name_source_type
2542 ,p_column_name_source_name => l_column_name_source_name
2543 ,p_row_name_source_type => l_row_name_source_type
2544 ,p_row_name_source_name => l_row_name_source_name
2545 );
2546 --
2547
2548 l_proc_step := 75;
2549 IF g_debug THEN
2550 debug(l_proc_name,l_proc_step);
2551 debug('l_from_time_dimension:'||l_from_time_dimension);
2552 debug('l_pay_source_value:'||l_pay_source_value);
2553 debug('l_qualifier:'||l_qualifier);
2554 debug('l_calc_type:'||l_calc_type);
2555 debug('l_calc_value:'||l_calc_value);
2556 debug('l_input_value:'||l_input_value);
2557 debug('l_check_link_to_assignment_yn:'||l_check_link_to_assignment_yn);
2558 debug('l_fte:'||l_fte);
2559 debug('l_service_history:'||l_service_history);
2560 debug('l_sum_multiple_entries_yn:'||l_sum_multiple_entries_yn);
2561 debug('l_lookup_input_values_yn:'||l_lookup_input_values_yn);
2562 debug('l_column_name_source_type:'||l_column_name_source_type);
2563 debug('l_column_name_source_name:'||l_column_name_source_name);
2564 debug('l_row_name_source_type:'||l_row_name_source_type);
2565 debug('l_row_name_source_name:'||l_row_name_source_name);
2566 END IF;
2567
2568
2569 -- The value Linked to Assignment is Yes indicates that
2570 -- the element should be considered only if it is linked to
2571 -- assignment
2572
2573 IF l_check_link_to_assignment_yn = 'Y'
2574 THEN
2575 -- Checking whether linked to Assignment
2576
2577 OPEN csr_element_entry
2578 (p_assignment_id => p_assignment_id
2579 ,p_element_type_id => this_element.element_type_id
2580 ,p_effective_date => p_calculation_date
2581 );
2582 FETCH csr_element_entry INTO l_element_entry;
2583 IF csr_element_entry%NOTFOUND
2584 THEN
2585 l_proc_step := 85;
2586 IF g_debug THEN
2587 debug(l_proc_name,l_proc_step);
2588 END IF;
2589 -- The element is not linked to assignment
2590 l_linked_to_assignment_yn := 'N';
2591 ELSE
2592 l_linked_to_assignment_yn := 'Y';
2593 END IF ;
2594 CLOSE csr_element_entry;
2595
2596 ELSE -- IF l_check_link_to_assignment_yn = 'Y'
2597
2598 -- Element Need not be Linked to Assignment
2599 -- hence deem as "linked" !
2600 l_linked_to_assignment_yn := 'Y' ;
2601
2602 END IF ; -- IF l_check_link_to_assignment_yn = 'Y'
2603
2604 IF g_debug THEN
2605 debug('l_linked_to_assignment_yn:'||l_linked_to_assignment_yn);
2606 END IF;
2607
2608 IF l_linked_to_assignment_yn = 'Y'
2609 THEN
2610
2611 l_element_rate :=
2612 process_element
2613 (p_assignment_id => p_assignment_id
2614 ,p_date => p_calculation_date
2615 ,p_element_type_id => this_element.element_type_id
2616 ,p_to_time_dim => p_to_time_dim
2617 ,p_fte => l_fte
2618 ,p_service_history => l_service_history
2619 ,p_pay_source_value => l_pay_source_value
2620 ,p_qualifier => l_qualifier
2621 ,p_from_time_dim => l_from_time_dimension
2622 ,p_calculation_type => l_calc_type
2623 ,p_calculation_value => fnd_number.canonical_to_number(l_calc_value)
2624 ,p_input_value => l_input_value
2625 ,p_term_time_yes_no => l_term_time_yes_no
2626 ,p_sum_multiple_entries_yn => l_sum_multiple_entries_yn
2627 ,p_lookup_input_values_yn => l_lookup_input_values_yn
2628 ,p_column_name_source_type => l_column_name_source_type
2629 ,p_column_name_source_name => l_column_name_source_name
2630 ,p_row_name_source_type => l_row_name_source_type
2631 ,p_row_name_source_name => l_row_name_source_name
2632 ,p_contract_type => p_contract_type
2633 ,p_contract_type_usage => p_contract_type_usage
2634 );
2635
2636 l_proc_step := 100;
2637 IF g_debug THEN
2638 debug(l_proc_name,l_proc_step);
2639 debug('l_element_rate:'||l_element_rate);
2640 END IF;
2641
2642 l_total_rate := l_total_rate + l_element_rate;
2643
2644 IF g_debug THEN
2645 debug('l_total_rate:'||l_element_rate);
2646 END IF;
2647
2648 END IF ; -- IF l_linked_to_assignment_yn = 'Y' THEN -- process_element
2649
2650 END LOOP; -- EXIT WHEN csr_element_attribute_id%NOTFOUND;
2651 --END IF; -- IF csr_element_attribute_id%FOUND THEN -- get_element_attributes
2652 CLOSE csr_element_attribute_id;
2653
2654 l_proc_step := 110;
2655 IF g_debug THEN
2656 debug(l_proc_name,l_proc_step);
2657 END IF;
2658
2659 END LOOP; -- FETCH l_csr_element_set INTO this_element
2660 CLOSE l_csr_element_set;
2661
2662 END IF; -- IF l_rate_code IS NOT NULL
2663
2664 l_proc_step := 110;
2665 IF g_debug THEN
2666 debug('l_total_rate:'||l_total_rate);
2667 END IF;
2668
2669 p_rate := ROUND(l_total_rate, g_rounding_precision);
2670 -- p_rate := l_total_rate;
2671
2672 p_error_message := NULL;
2673 IF g_debug THEN
2674 debug('p_rate:'||p_rate);
2675 debug('p_error_message:'||p_error_message);
2676 debug_exit(l_proc_name);
2677 END IF;
2678 RETURN 0;
2679
2680 EXCEPTION
2681 WHEN OTHERS THEN
2682 clear_cache;
2683 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2684 debug_others(l_proc_name,l_proc_step);
2685 IF g_debug THEN
2686 debug('Leaving: '||l_proc_name,-999);
2687 END IF;
2688 fnd_message.raise_error;
2689 ELSE
2690 RAISE;
2691 END IF;
2692 END rates_history;
2693 -- ----------------------------------------------------------------------------
2694 -- |-------------------------< get_historic_rate >----------------------------|
2695 -- ----------------------------------------------------------------------------
2696 --
2697 -- Description: Top level function, returning rate of pay. Can be used
2698 -- for single element or rate type.
2699 -- Formula Function: GET_HISTORIC_RATE (aliased RATES_HISTORY) maps to this spec
2700 -- p_effective_date DEFAULT session effective date,
2701 -- if the session date is no not set then
2702 -- system date
2703 -- p_time_dimension DEFAULT the same as the source time dimension
2704 -- for the element
2705 -- p_rate_type_or_element DEFAULT c_default_type_of_rate = 'E'
2706 -- p_contract_type DEFAULT Null , if no contract type is supplied
2707 -- then one is expected to exist
2708 -- at the assignment level
2709 -- p_contract_type_usage DEFAULT g_default_contract_type_usage = 'OVERRIDE'
2710 -- override the assignment contract with
2711 -- the one specified in p_contract_type
2712 FUNCTION get_historic_rate
2713 (p_assignment_id IN NUMBER
2714 ,p_rate_name IN VARCHAR2
2715 ,p_effective_date IN DATE DEFAULT NULL
2716 ,p_time_dimension IN VARCHAR2 DEFAULT NULL
2717 ,p_rate_type_or_element IN VARCHAR2 DEFAULT c_default_type_of_rate
2718 ,p_contract_type IN VARCHAR2 DEFAULT NULL
2719 ,p_contract_type_usage IN VARCHAR2 DEFAULT g_default_contract_type_usage
2720 ) RETURN NUMBER
2721 IS
2722
2723 l_proc_step NUMBER(20,10):=0;
2724 l_proc_name VARCHAR2(61):=
2725 g_package_name||'get_historic_rate';
2726
2727 l_historic_rate NUMBER;
2728 l_effective_date DATE;
2729 l_error_code fnd_new_messages.message_number%TYPE;
2730 l_error_message fnd_new_messages.message_text%TYPE;
2731
2732
2733 BEGIN
2734
2735 g_debug := hr_utility.debug_enabled;
2736 IF g_debug THEN
2737 debug('p_assignment_id:'||p_assignment_id);
2738 debug('p_rate_name:'||p_rate_name);
2739 debug('p_effective_date:'||fnd_date.date_to_canonical(p_effective_date));
2740 debug('p_time_dimension:'||
2741 NVL(p_time_dimension,'ISNULL_SAME_AS_FROM'));
2742 debug('p_rate_type_or_element:'||
2743 NVL(p_rate_type_or_element,'ISNULL_WILL_USE_'||c_default_type_of_rate));
2744 debug('p_contract_type:'||p_contract_type);
2745 debug('p_contract_type_usage:'||
2746 NVL(p_contract_type_usage,'ISNULL_WILL_USE_'||g_default_contract_type_usage));
2747 END IF;
2748
2749 IF p_effective_date IS NULL
2750 THEN
2751 l_effective_date := HR_GBNICAR.NICAR_SESSION_DATE(0);
2752 ELSE
2753 l_effective_date := p_effective_date;
2754 END IF;
2755
2756 IF g_debug THEN
2757 debug('l_effective_date:'||l_effective_date);
2758 END IF;
2759
2760 l_error_code :=
2761 rates_history
2762 (p_assignment_id => p_assignment_id
2763 ,p_calculation_date => l_effective_date
2764 ,p_name => UPPER(p_rate_name)
2765 ,p_rt_element => NVL(p_rate_type_or_element,c_default_type_of_rate)
2766 ,p_to_time_dim => p_time_dimension
2767 ,p_rate => l_historic_rate
2768 ,p_error_message => l_error_message
2769 ,p_contract_type => p_contract_type
2770 ,p_contract_type_usage => NVL(p_contract_type_usage,g_default_contract_type_usage)
2771 );
2772
2773 l_proc_step := 20;
2774 IF g_debug THEN
2775 debug(l_proc_name,l_proc_step);
2776 END IF;
2777
2778 IF l_error_code < 0
2779 THEN
2780 check_error_code(l_error_code,l_error_message);
2781 END IF;
2782
2783 IF g_debug THEN
2784 debug('l_historic_rate:'||l_historic_rate);
2785 END IF;
2786
2787 RETURN l_historic_rate;
2788
2789 EXCEPTION
2790 WHEN OTHERS THEN
2791 clear_cache;
2792 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2793 debug_others(l_proc_name,l_proc_step);
2794 IF g_debug THEN
2795 debug('Leaving: '||l_proc_name,-999);
2796 END IF;
2797 fnd_message.raise_error;
2798 ELSE
2799 RAISE;
2800 END IF;
2801 END get_historic_rate;
2802
2803
2804 END pqp_rates_history_calc;
[Home] [Help]