1 PACKAGE BODY pqp_fedhr_uspay_int_utils AS
2 /* $Header: pqpfhexr.pkb 120.1 2006/01/16 03:36:26 asubrahm noship $ */
3
4 TYPE element_cross_rec IS RECORD
5 (
6 element_new_name pay_element_types_f.element_name%TYPE ,
7 element_type_id pay_element_types_f.element_type_id%TYPE ,
8 element_old_name pay_element_types_f.element_name%TYPE ,
9 pay_basis VARCHAR2(50)
10 );
11
12 TYPE element_cross_tab IS TABLE OF element_cross_rec
13 INDEX BY BINARY_INTEGER;
14 t_element_cross element_cross_tab;
15
16 TYPE paybasis_to_salbasis IS RECORD
17 (
18 Pay_Basis VARCHAR2(3),
19 Sal_Basis VARCHAR2(80)
20 );
21
22 TYPE pay_to_sal IS TABLE OF paybasis_to_salbasis
23 INDEX BY BINARY_INTEGER;
24 t_pb_to_sb pay_to_sal;
25
26
27 g_package_name VARCHAR2(50) := 'pqp_fedhr_uspay_int_utils.';
28 -- ***************************************************************************
29 --
30 -- ----------------------------------------------------------------------------
31 -- |---------------------< return_new_element_name >--------------------------|
32 -- ----------------------------------------------------------------------------
33 -- {Start Of Comments}
34 --
35 -- Description:
36 -- This function returns a new element name if an entry is present in
37 -- pqp_configuration_values table. Otherwise this function returns the
38 -- same element name as passed to it as input parameter.
39 -- Prerequisites:
40 -- None.
41 --
42 -- In Parameters:
43 --
44 -- Post Success:
45 -- This function will return an element name.
46 --
47 -- Post Failure:
48 --
49 -- Developer Implementation Notes:
50 -- None.
51 --
52 -- Access Status:
53 -- Internal Development Use Only.
54 --
55 -- {End Of Comments}
56 -- ----------------------------------------------------------------------------
57 FUNCTION return_new_element_name
58 (
59 p_fedhr_element_name IN VARCHAR2,
60 p_business_group_id IN NUMBER,
61 p_effective_date IN DATE,
62 p_pay_basis IN VARCHAR2
63 ) RETURN VARCHAR2 IS
64
65 CURSOR chk_pqp_config_tab(p_business_group_id
66 per_business_groups.business_group_id%TYPE,
67 p_pay_basis VARCHAR2) IS
68 SELECT ghr_general.return_NUMBER(pcv.pcv_information2) element_type_id,
69 pcv.pcv_information1 element_old_name,
70 NVL(pcv.pcv_information3,'NULL') pay_basis
71 FROM pqp_configuration_values pcv
72 WHERE pcv.pcv_information_category = 'PQP_FEDHR_ELEMENT'
73 AND pcv.business_group_id = p_business_group_id
74 AND NVL(pcv_information3,'NULL') = NVL(p_pay_basis,'NULL')
75 -- picking Ele name based on Sal Basis esp BSR
76 ORDER BY pcv.pcv_information1;
77
78 CURSOR element_type_cursor(p_element_type_id
79 pay_element_types_f.element_type_id%TYPE,
80 p_business_group_id
81 per_business_groups.business_group_id%TYPE,
82 p_effective_date DATE) IS
83 SELECT pet.element_name element_new_name
84 FROM pay_element_types_f pet
85 WHERE pet.business_group_id = p_business_group_id
86 AND pet.element_type_id = p_element_type_id
87 AND pet.business_group_id = p_business_group_id
88 AND p_effective_date BETWEEN pet.effective_start_date AND
89 pet.effective_end_date;
90
91 l_element_id NUMBER;
92
93 l_element_name pay_element_types_f.element_name%TYPE;
94
95 l_counter NUMBER;
96 l_tab_count NUMBER;
97 l_proc_name VARCHAR2(50) := 'return_new_element_name';
98 l_pay_basis VARCHAR2(10) :='NULL';
99 BEGIN
100 hr_utility.set_location('Entering ' || g_package_name || l_proc_name, 10);
101
102 -- is_script_run function will return TRUE, if GHR and Payroll were already
103 -- installed. Because this is the only way pqp_configuration_values table
104 -- will have any row of cv_information_category = 'PQP_FEDHR_ELEMENT'
105 -- We are checking for only one element(Federal Awards) and not for all
106 -- the elements for the performance reasons. We assume that if one element is
107 -- present then all the elements will be automatically present.
108
109 IF (p_fedhr_element_name = 'Basic Salary Rate') THEN
110 l_pay_basis := p_pay_basis;
111 ELSE
112 l_pay_basis := 'NULL';
113 END IF;
114
115 IF (pqp_fedhr_uspay_int_utils.is_script_run
116 (p_business_group_id => p_business_group_id,
117 p_fedhr_element_name => 'Basic Salary Rate') = TRUE)
118 THEN
119 -- {
120 l_counter := 0;
121 l_element_id := NULL;
122 l_tab_count := t_element_cross.COUNT;
123
124 IF (l_tab_count > 0) THEN -- Hit in the cache table.
125 -- {
126
127 -- This portion will be executed second time and onwards employee re-runs the
128 -- RPA process.
129
130 hr_utility.trace('In If - cache ');
131 FOR l_counter IN 1..l_tab_count LOOP
132 -- To make this search faster a Binary Search can be used, instead of liner
133 -- search. But linear search should be okay, as there will not be more than
134 -- 35 rows.
135
136 hr_utility.trace(t_element_cross(l_counter).element_old_name);
137 IF (t_element_cross(l_counter).element_old_name =
138 p_fedhr_element_name AND
139 t_element_cross(l_counter).pay_basis =
140 NVL(l_pay_basis, 'NULL'))
141 THEN
142 hr_utility.trace('Element Name Hit');
143
144 -- Fill in the variables with the details present in PL/SQL
145 -- table.
146
147 l_element_id := t_element_cross(l_counter).element_type_id;
148 l_element_name:=t_element_cross(l_counter).element_new_name;
149 END IF;
150 END LOOP;
151 --}
152 END IF;
153
154 IF (l_element_name IS NULL)
155 THEN
156 -- This condition will be true in either of the following cases
157 -- 1. For the first time, If user did not run an RPA and no caching has occured.
158 -- 2. The previous IF condition did not return a result. This can happen in
159 -- a case, where user ran an RPA process and t_element_cross gets filled. But
160 -- 5 minutes later user creates a mapping for a new element. Then l_element_name
161 -- will be null, even if t_element_cross has some rows, then this IF portion
162 -- will be executed, and the new element mapping will be found out.
163 --{
164 -- This portion will be executed for the first time when employee
165 -- runs the RPA process to fill in the cache.
166
167 hr_utility.trace('In Else - Cursor');
168 -- modified the cursor to pick the elements (BSR esp) based on
169 -- Sal_Basis
170
171 FOR c_pqp_config IN chk_pqp_config_tab (p_business_group_id =>
172 p_business_group_id ,
173 p_pay_basis => l_pay_basis)
174 LOOP
175 l_counter := l_counter + 1;
176 t_element_cross(l_counter).element_type_id :=
177 c_pqp_config.element_type_id;
178
179 t_element_cross(l_counter).element_old_name :=
180 c_pqp_config.element_old_name ;
181 t_element_cross(l_counter).pay_basis :=
182 c_pqp_config.pay_basis ;
183 -- Filling the pl/sql table.
184 FOR c_pet IN element_type_cursor(p_element_type_id =>
185 t_element_cross(l_counter).element_type_id,
186 p_business_group_id =>
187 p_business_group_id,
188 p_effective_date =>
189 p_effective_date)
190 LOOP
191 t_element_cross(l_counter).element_new_name :=
192 c_pet.element_new_name ;
193 END LOOP;
194
195 hr_utility.trace (t_element_cross(l_counter).element_old_name);
196
197 IF(t_element_cross(l_counter).element_old_name =
198 p_fedhr_element_name AND
199 t_element_cross(l_counter).pay_basis =
200 NVL(l_pay_basis, 'NULL'))
201 THEN
202 hr_utility.trace ('Element name found');
203 l_element_id :=t_element_cross(l_counter).element_type_id;
204 l_element_name:=t_element_cross(l_counter).element_new_name;
205 hr_utility.set_location('Element name is :'||l_element_name,15);
206 END IF;
207 END LOOP;
208 --}
209 END IF;
210 IF l_element_name IS NULL
211 THEN
212 hr_utility.set_message(800, 'HR_7465_PLK_NOT_ELGBLE_ELE_NME');
213 hr_utility.set_message_token('ELEMENT_NAME', p_fedhr_element_name);
214 hr_utility.raise_error;
215 -- RETURN(p_fedhr_element_name);
216 ELSE
217 RETURN l_element_name;
218 END IF;
219 -- }
220 ELSE -- is_script_run returns FALSE. Just return the passed element name.
221 -- {
222 RETURN p_fedhr_element_name;
223 -- }
224 END IF;
225 hr_utility.set_location ('Leaving ' || g_package_name || l_proc_name, 100);
226 END;
227 -- ***************************************************************************
228 --
229 -- ----------------------------------------------------------------------------
230 -- |---------------------< return_new_element_name >--------------------------|
231 -- ----------------------------------------------------------------------------
232 -- {Start Of Comments}
233 --
234 -- Description:
235 -- This function returns a new element name if an entry is present in
236 -- pqp_configuration_values table. Otherwise this function returns the
237 -- same element name as passed to it as input parameter.
238 -- Prerequisites:
239 -- None.
240 --
241 -- In Parameters:
242 --
243 -- Post Success:
244 -- This function will return an element name.
245 --
246 -- Post Failure:
247 --
248 -- Developer Implementation Notes:
249 -- None.
250 --
251 -- Access Status:
252 -- Internal Development Use Only.
253 --
254 -- {End Of Comments}
255 -- ----------------------------------------------------------------------------
256 FUNCTION return_new_element_name
257 (
258 p_salary_basis IN VARCHAR2 ,
259 p_business_group_id IN NUMBER ,
260 p_effective_date IN DATE
261 ) RETURN VARCHAR2 IS
262
263 CURSOR chk_per_pay_bases(p_business_group_id
264 per_business_groups.business_group_id%TYPE,
265 p_salary_basis VARCHAR2,
266 p_effective_date DATE) IS
267 SELECT element_name
268 FROM per_pay_bases ppb,
269 pay_element_types_f pet,
270 pay_input_values_f piv
271 WHERE ppb.business_group_id = p_business_group_id
272 AND ppb.business_group_id = pet.business_group_id
273 AND ppb.business_group_id = piv.business_group_id
274 AND ppb.name = p_salary_basis
275 AND ppb.input_value_id = piv.input_value_id
276 AND piv.element_type_id = pet.element_type_id
277 AND p_effective_date BETWEEN piv.effective_start_date AND
278 piv.effective_end_date
279 AND p_effective_date BETWEEN pet.effective_start_date AND
280 pet.effective_end_date;
281
282 l_element_name pay_element_types_f.element_name%TYPE;
283 l_proc_name VARCHAR2(50) := 'return_new_element_name';
284 BEGIN
285 hr_utility.set_location('Entering ' || g_package_name || l_proc_name, 10);
286
287 IF (pqp_fedhr_uspay_int_utils.is_script_run
288 (p_business_group_id => p_business_group_id,
289 p_fedhr_element_name => 'Basic Salary Rate') = TRUE)
290 THEN
291 -- {
292 FOR c_ppb IN chk_per_pay_bases (p_business_group_id =>
293 p_business_group_id ,
294 p_salary_basis => p_salary_basis,
295 p_effective_date => p_effective_date)
296 LOOP
297 l_element_name := c_ppb.element_name;
298 END LOOP;
299 RETURN l_element_name;
300 -- }
301 ELSE -- is_script_run returns FALSE. Just return the passed element name.
302 -- {
303 RETURN NULL;
304 -- }
305 END IF;
306 hr_utility.set_location ('Leaving ' || g_package_name || l_proc_name, 100);
307 END;
308 -- ***************************************************************************
309 --
310 -- ----------------------------------------------------------------------------
311 -- |---------------------< return_new_element_name >--------------------------|
312 -- ----------------------------------------------------------------------------
313 -- {Start Of Comments}
314 --
315 -- Description:
316 -- This function returns a new element name if an entry is present in
317 -- pqp_configuration_values table. Otherwise this function returns the
318 -- same element name as passed to it as input parameter.
319 -- Prerequisites:
320 -- None.
321 --
322 -- In Parameters:
323 --
324 -- Post Success:
325 -- This function will return an element name.
326 --
327 -- Post Failure:
328 --
329 -- Developer Implementation Notes:
330 -- None.
331 --
332 -- Access Status:
333 -- Internal Development Use Only.
334 --
335 -- {End Of Comments}
336 -- ----------------------------------------------------------------------------
337 FUNCTION return_new_element_name
338 (
339 p_assignment_id IN VARCHAR2 ,
340 p_business_group_id IN NUMBER ,
341 p_effective_date IN DATE
342 ) RETURN VARCHAR2 IS
343 CURSOR chk_per_pay_bases(p_business_group_id
344 per_business_groups.business_group_id%TYPE,
345 p_salary_basis_id NUMBER,
346 p_effective_date DATE) IS
347 SELECT element_name
348 FROM per_pay_bases ppb,
349 pay_element_types_f pet,
350 pay_input_values_f piv
351 WHERE ppb.business_group_id = p_business_group_id
352 AND ppb.business_group_id = pet.business_group_id
353 AND ppb.business_group_id = piv.business_group_id
354 AND ppb.pay_basis_id = p_salary_basis_id
355 AND ppb.input_value_id = piv.input_value_id
356 AND piv.element_type_id = pet.element_type_id
357 AND p_effective_date BETWEEN piv.effective_start_date AND
358 piv.effective_end_date
359 AND p_effective_date BETWEEN pet.effective_start_date AND
360 pet.effective_end_date;
361
362 CURSOR get_sal_basis_id(p_assignment_id NUMBER ,
363 p_business_group_id
364 per_business_groups.business_group_id%TYPE,
365 p_effective_date DATE) IS
366 SELECT pay_basis_id
367 FROM per_all_assignments_f
368 WHERE assignment_id = p_assignment_id
369 AND business_group_id = p_business_group_id
370 AND p_effective_date BETWEEN effective_start_date
371 AND effective_end_date;
372
373 l_element_name pay_element_types_f.element_name%TYPE;
374 l_proc_name VARCHAR2(50) := 'return_new_element_name';
375 l_sal_basis_id NUMBER;
376 BEGIN
377 hr_utility.set_location('Entering ' || g_package_name || l_proc_name, 10);
378
379 IF (pqp_fedhr_uspay_int_utils.is_script_run
380 (p_business_group_id => p_business_group_id,
381 p_fedhr_element_name => 'Basic Salary Rate') = TRUE)
382 THEN
383 -- {
384 FOR c_assgn IN get_sal_basis_id(p_assignment_id => p_assignment_id,
385 p_business_group_id =>
386 p_business_group_id,
387 p_effective_date => p_effective_date)
388 LOOP
389 l_sal_basis_id := c_assgn.pay_basis_id;
390 END LOOP;
391 FOR c_ppb IN chk_per_pay_bases (p_business_group_id =>
392 p_business_group_id ,
393 p_salary_basis_id => l_sal_basis_id,
394 p_effective_date => p_effective_date)
395 LOOP
396 l_element_name := c_ppb.element_name;
397 END LOOP;
398 RETURN l_element_name;
399 -- }
400 ELSE -- is_script_run returns FALSE. Just return the passed element name.
401 -- {
402 RETURN NULL;
403 -- }
404 END IF;
405 hr_utility.set_location ('Leaving ' || g_package_name || l_proc_name, 100);
406 END;
407 -- ***************************************************************************
408 --
409 -- ----------------------------------------------------------------------------
410 -- |---------------------------< is_script_run >------------------------------|
411 -- ----------------------------------------------------------------------------
412 -- {Start Of Comments}
413 --
414 -- Description:
415 -- This function return a TRUE if rows are present in pqp_configuration_values
416 -- table. Otherwise this function returns false.
417 -- Prerequisites:
418 -- None.
419 --
420 -- In Parameters:
421 --
422 -- Post Success:
423 -- This function will return a TRUE or FALSE.
424 --
425 -- Post Failure:
426 --
427 -- Developer Implementation Notes:
428 -- None.
429 --
430 -- Access Status:
431 -- Internal Development Use Only.
432 --
433 -- {End Of Comments}
434 -- ----------------------------------------------------------------------------
435 FUNCTION is_script_run
436 (
437 p_fedhr_element_name IN VARCHAR2,
438 p_business_group_id IN NUMBER
439 ) RETURN BOOLEAN IS
440
441 CURSOR chk_pqp_config_tab
442 IS
443 SELECT pcv_information_category
444 FROM pqp_configuration_values
445 WHERE pcv_information_category = 'PQP_FEDHR_ELEMENT'
446 AND business_group_id = p_business_group_id
447 AND pcv_information1 = p_fedhr_element_name
448 AND ROWNUM < 2;
449
450 l_info_category pqp_configuration_values.pcv_information_category%TYPE;
451 l_proc_name VARCHAR2(50) := 'is_script_run';
452
453 BEGIN
454 hr_utility.set_location ('Entering ' || g_package_name || l_proc_name, 10);
455
456 -- The following loop gets the count of rows present in pqp_configuration_values
457 -- table.
458 l_info_category := NULL;
459 FOR c_pqp_config IN chk_pqp_config_tab
460 LOOP
461 l_info_category := c_pqp_config.pcv_information_category;
462 hr_utility.trace('In cursor loop');
463 END LOOP;
464
465 -- If count > 0, that means some rows were present in pqp_configuration_Values
466 -- table. Therefore this function returns TRUE.
467
468 IF l_info_category IS NOT NULL
469 THEN
470 hr_utility.trace('True');
471 RETURN TRUE;
472 ELSE
473 hr_utility.trace('False');
474 RETURN FALSE;
475 END IF;
476 hr_utility.set_location ('Leaving ' || g_package_name || l_proc_name, 100);
477 END;
478
479 -- ***************************************************************************
480 --
481 -- ----------------------------------------------------------------------------
482 -- |---------------------------is_ele_link_exists >----------------------------|
483 -- ----------------------------------------------------------------------------
484 -- {Start Of Comments}
485 --
486 -- Description:
487 -- This function return a TRUE if the element link exists.
488 -- Otherwise this function returns false.
489 -- Prerequisites:
490 -- None.
491 --
492 -- In Parameters:
493 --
494 -- Post Success:
495 -- This function will return a TRUE or FALSE.
496 --
497 -- Post Failure:
498 --
499 -- Developer Implementation Notes:
500 -- None.
501 --
502 -- Access Status:
503 -- Internal Development Use Only.
504 --
505 -- {End Of Comments}
506 -- ----------------------------------------------------------------------------
507
508 FUNCTION is_ele_link_exists
509 (
510 p_ele_name IN VARCHAR2,
511 p_legislation_code IN VARCHAR2 DEFAULT NULL,
512 p_bg_id IN NUMBER DEFAULT NULL
513 )
514 RETURN BOOLEAN IS
515
516 l_ele_type_id NUMBER;
517 l_eli_flag BOOLEAN;
518
519 CURSOR cur_ele_link_in_biz(p_ele_name VARCHAR2, p_business_group_id IN NUMBER)
520 IS
521 SELECT DISTINCT eli.element_link_id link_id
522 FROM pay_element_types_f elt,
523 pay_element_links_f eli
524 WHERE elt.element_type_id = eli.element_type_id
525 AND UPPER(elt.element_name) = UPPER(p_ele_name)
526 AND eli.business_group_id = p_business_group_id
527 AND elt.business_group_id = p_business_group_id;
528
529 CURSOR cur_ele_link_in_leg(p_ele_name IN VARCHAR2,
530 p_legislation_code IN VARCHAR2,
531 p_business_group_id IN NUMBER)
532 IS
533 SELECT DISTINCT eli.element_link_id link_id
534 FROM pay_element_types_f elt,
535 pay_element_links_f eli
536 WHERE elt.element_type_id = eli.element_type_id
537 AND UPPER(elt.element_name) = UPPER(p_ele_name)
538 AND elt.business_group_id IS NULL
539 AND elt.legislation_code = p_legislation_code
540 AND eli.business_group_id = p_business_group_id;
541
542 -- eli.business_group can be added if we want to check for the link only in that business group;
543 --
544 BEGIN
545 --
546 IF (p_legislation_code IS NOT NULL) THEN
547 FOR ele_link_rec IN cur_ele_link_in_leg(p_ele_name, 'US', p_bg_id)
548 LOOP
549 IF ele_link_rec.link_id IS NOT NULL
550 THEN
551 l_eli_flag := TRUE;
552 ELSE
553 l_eli_flag := FALSE;
554 END IF;
555 END LOOP;
556 ELSE
557 FOR ele_link_rec IN cur_ele_link_in_biz(p_ele_name,p_bg_id)
558 LOOP
559 IF ele_link_rec.link_id IS NOT NULL
560 THEN
561 l_eli_flag := TRUE;
562 ELSE
563 l_eli_flag := FALSE;
564 END IF;
565 END LOOP;
566 END IF;
567
568 RETURN(l_eli_flag);
569
570 END;
571
572 -- ***************************************************************************
573 --
574 -- ----------------------------------------------------------------------------
575 -- |-------------------------<pay_basis_to_sal_basis >------------------------|
576 -- ----------------------------------------------------------------------------
577 -- {Start Of Comments}
578 --
579 -- Description:
580 -- This function provides the mapping of Pay basisd to Salary Basis.
581 --
582 -- Prerequisites:
583 -- None.
584 --
585 -- In Parameters:
586 --
587 -- Post Success:
588 -- This function will return Salary Basis for the given Pay Basis.
589 --
590 -- Post Failure:
591 --
592 -- Developer Implementation Notes:
593 -- None.
594 --
595 -- Access Status:
596 -- Internal Development Use Only.
597 --
598 -- {End Of Comments}
599 -- ----------------------------------------------------------------------------
600
601 FUNCTION pay_basis_to_sal_basis
602 (
603 p_pay_basis IN VARCHAR2,
604 p_sal_basis OUT NOCOPY VARCHAR2
605 ) RETURN VARCHAR2 IS
606
607 l_pay_basis VARCHAR2(80);
608 l_sal_basis VARCHAR2(80);
609 l_info_cat VARCHAR2(80);
610
611 CURSOR cur_pb_to_sb
612 IS
613 SELECT pcv_information1 pb,
614 pcv_information2 sb
615 FROM pqp_configuration_values pcv
616 WHERE pcv_information_category='PQP_PAY_SALARY_BASIS_MAPPING';
617
618 l_index NUMBER:=0;
619
620 --
621 BEGIN
622 --
623
624 FOR pb_to_sb IN cur_pb_to_sb
625 LOOP
626 l_index:=l_index+1;
627 t_pb_to_sb(l_index).pay_basis:=pb_to_sb.pb;
628 t_pb_to_sb(l_index).sal_basis:=pb_to_sb.sb;
629 END LOOP;
630
631 FOR l_index IN 1..t_pb_to_sb.COUNT
632 LOOP
633 IF (t_pb_to_sb(l_index).pay_basis = p_pay_basis)
634 THEN
635 l_sal_basis:=t_pb_to_sb(l_index).sal_basis;
636 END IF;
637 END LOOP;
638
639 RETURN(l_sal_basis);
640 --
641 END; -- End of pay basis mapping
642 --
643
644 -- ----------------------------------------------------------------------------
645 -- |---------------------< return_old_element_name >--------------------------|
646 -- ----------------------------------------------------------------------------
647 -- {Start Of Comments}
648 --
649 -- Description:
650 -- This function returns a federal element name if an entry is present in
651 -- pqp_configuration_values table. Otherwise this function returns the
652 -- same element name as passed to it as input parameter.
653 -- Prerequisites:
654 -- None.
655 --
656 -- In Parameters:
657 --
658 -- Post Success:
659 -- This function will return federal element name.
660 --
661 -- Post Failure:
662 --
663 -- Developer Implementation Notes:
664 -- None.
665 --
666 -- Access Status:
667 -- Internal Development Use Only.
668 --
669 -- {End Of Comments}
670 -- ----------------------------------------------------------------------------
671
672 FUNCTION return_old_element_name
673 (
674 p_agency_element_name IN VARCHAR2,
675 p_business_group_id IN NUMBER,
676 p_effective_date IN DATE
677 ) RETURN VARCHAR2 IS
678
679 l_element_type_id pay_element_types_f.element_type_id%type;
680
681 CURSOR chk_element_type_id IS
682 SELECT ele.element_type_id element_type_id
683 FROM pay_element_types_f ele
684 WHERE ele.element_name = p_agency_element_name
685 AND business_group_id = p_business_group_id
686 AND p_effective_date BETWEEN ele.effective_start_date
687 AND ele.effective_end_date;
688
689 CURSOR chk_pqp_config_tab(p_business_group_id per_business_groups.business_group_id%TYPE) IS
690 SELECT ghr_general.return_NUMBER(pcv.pcv_information2) element_type_id,
691 pcv.pcv_information1 element_old_name,
692 NVL(pcv.pcv_information3,'NULL') pay_basis
693 FROM pqp_configuration_values pcv
694 WHERE pcv.pcv_information_category = 'PQP_FEDHR_ELEMENT'
695 AND pcv.business_group_id = p_business_group_id
696 AND ghr_general.return_number(pcv.pcv_information2) = l_element_type_id
697 ORDER BY pcv.pcv_information1;
698
699 /****
700 CURSOR element_type_cursor(p_element_type_id
701 pay_element_types_f.element_type_id%TYPE,
702 p_business_group_id
703 per_business_groups.business_group_id%TYPE,
704 p_effective_date DATE) IS
705 SELECT pet.element_name element_new_name
706 FROM pay_element_types_f pet
707 WHERE pet.business_group_id = p_business_group_id
708 AND pet.element_type_id = p_element_type_id
709 AND pet.business_group_id = p_business_group_id
710 AND p_effective_date BETWEEN pet.effective_start_date AND
711 pet.effective_end_date;
712 ****/
713 l_element_id NUMBER;
714
715 l_element_name pay_element_types_f.element_name%TYPE;
716
717 l_counter NUMBER;
718 l_tab_count NUMBER;
719 l_proc_name VARCHAR2(50) := 'return_old_element_name';
720 l_pay_basis VARCHAR2(10) := 'NULL';
721 BEGIN
722 hr_utility.set_location('Entering ' || g_package_name || l_proc_name, 10);
723
724 FOR chk_element_type_rec IN chk_element_type_id
725 LOOP
726 l_element_type_id := chk_element_type_rec.element_type_id;
727 END LOOP;
728
729 -- is_script_run function will return TRUE, if GHR and Payroll were already
730 -- installed. Because this is the only way pqp_configuration_values table
731 -- will have any row of cv_information_category = 'PQP_FEDHR_ELEMENT'
732 -- We are checking for only one element(Federal Awards) and not for all
733 -- the elements for the performance reasons. We assume that if one element is
734 -- present then all the elements will be automatically present.
735
736 IF (pqp_fedhr_uspay_int_utils.is_script_run
737 (p_business_group_id => p_business_group_id,
738 p_fedhr_element_name => 'Basic Salary Rate') = TRUE)
739 THEN
740 -- {
741 l_counter := 0;
742 l_element_id := NULL;
743 l_element_name := NULL;
744 l_tab_count := t_element_cross.COUNT;
745
746 IF (l_tab_count > 0) THEN -- Hit in the cache table.
747 -- {
748
749 -- This portion will be executed second time and onwards employee re-runs the
750 -- RPA process.
751
752 hr_utility.trace('In If - cache ');
753 FOR l_counter IN 1..l_tab_count LOOP
754 -- To make this search faster a Binary Search can be used, instead of liner
755 -- search. But linear search should be okay, as there will not be more than
756 -- 35 rows.
757
758 hr_utility.trace(t_element_cross(l_counter).element_new_name);
759 IF (t_element_cross(l_counter).element_new_name =
760 p_agency_element_name )
761 THEN
762 hr_utility.trace('New Element Name Hit');
763
764 -- Fill in the variables with the details present in PL/SQL
765 -- table.
766
767 l_element_id := t_element_cross(l_counter).element_type_id;
768 l_element_name := t_element_cross(l_counter).element_old_name;
769 END IF;
770 END LOOP;
771 --}
772 END IF;
773
774 IF (l_element_name IS NULL)
775 THEN
776 -- This condition will be true in either of the following cases
777 -- 1. For the first time, If user did not run an RPA and no caching has occured.
778 -- 2. The previous IF condition did not return a result. This can happen in
779 -- a case, where user ran an RPA process and t_element_cross gets filled. But
780 -- 5 minutes later user creates a mapping for a new element. Then l_element_name
781 -- will be null, even if t_element_cross has some rows, then this IF portion
782 -- will be executed, and the new element mapping will be found out.
783 --{
784 -- This portion will be executed for the first time when employee
785 -- runs the RPA process to fill in the cache.
786
787 hr_utility.trace('In Else - Cursor');
788 -- modified the cursor to pick the elements (BSR esp) based on
789 -- Sal_Basis
790
791 FOR c_pqp_config IN chk_pqp_config_tab (p_business_group_id => p_business_group_id )
792 LOOP
793 l_counter := l_counter + 1;
794 t_element_cross(l_counter).element_type_id :=
795 c_pqp_config.element_type_id;
796
797 t_element_cross(l_counter).element_old_name :=
798 c_pqp_config.element_old_name ;
799 t_element_cross(l_counter).pay_basis :=
800 c_pqp_config.pay_basis ;
801 -- Filling the pl/sql table.
802 t_element_cross(l_counter).element_new_name :=
803 p_agency_element_name ;
804
805 hr_utility.trace (t_element_cross(l_counter).element_old_name);
806
807 IF (t_element_cross(l_counter).element_new_name =
808 p_agency_element_name )
809 THEN
810 hr_utility.trace ('Element name found');
811 l_element_id :=t_element_cross(l_counter).element_type_id;
812 l_element_name:=t_element_cross(l_counter).element_old_name;
813 hr_utility.set_location('Element name is :'||l_element_name,15);
814 END IF;
815 END LOOP;
816 --}
817 END IF;
818 IF l_element_name IS NULL
819 THEN
820 RETURN(p_agency_element_name);
821 ELSE
822 RETURN l_element_name;
823 END IF;
824 -- }
825 ELSE -- is_script_run returns FALSE. Just return the passed element name.
826 -- {
827 RETURN p_agency_element_name;
828 -- }
829 END IF;
830 hr_utility.set_location ('Leaving ' || g_package_name || l_proc_name, 100);
831 END return_old_element_name;
832 END pqp_fedhr_uspay_int_utils;
[Home] [Help]