[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_UTILS
Source
1 PACKAGE BODY pay_in_utils AS
2 /* $Header: pyinutil.pkb 120.22 2008/01/12 17:12:23 lnagaraj noship $ */
3 g_debug BOOLEAN;
4 g_package VARCHAR2(20);
5
6 ----------------------------------------------------------------------------
7 -- --
8 -- Name : GET_MAX_ACT_SEQUENCE --
9 -- Type : Function --
10 -- Access : Public --
11 -- Description : This function returns the maximum action sequence --
12 -- for a given assignment id and process type --
13 -- Parameters : --
14 -- IN : p_assignment_id NUMBER --
15 -- p_process_type VARCHAR2 --
16 -- p_effective_date DATE --
17 -- --
18 ---------------------------------------------------------------------------
19 FUNCTION get_max_act_sequence(p_assignment_id IN NUMBER
20 ,p_process_type IN VARCHAR2
21 ,p_effective_date IN DATE
22 )
23 RETURN NUMBER
24 IS
25 CURSOR c_max_act_seq
26 IS
27 SELECT MAX(paa.action_sequence)
28 FROM pay_assignment_actions paa
29 ,pay_payroll_actions ppa
30 WHERE paa.payroll_action_id = ppa.payroll_action_id
31 AND ppa.action_type = p_process_type
32 AND paa.assignment_id = p_assignment_id
33 AND TRUNC(ppa.effective_date,'MM') = TRUNC(p_effective_date,'MM');
34
35 l_max_act_seq NUMBER := NULL;
36 l_procedure VARCHAR2(50);
37 l_message VARCHAR2(300);
38 --
39 BEGIN
40 g_debug := hr_utility.debug_enabled;
41 l_procedure := g_package||'get_max_act_sequence';
42 --
43 set_location(g_debug, 'Entered '|| l_procedure,10);
44 trace('Assignment ID ',p_assignment_id );
45 trace('Process Type ',p_process_type );
46 trace('Effective Date',p_effective_date);
47
48 OPEN c_max_act_seq;
49 FETCH c_max_act_seq INTO l_max_act_seq;
50 CLOSE c_max_act_seq;
51
52 trace('Maximum Action Sequence',l_max_act_seq);
53
54 set_location(g_debug, 'Leaving '|| l_procedure,30);
55 RETURN l_max_act_seq;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
60 set_location(g_debug,' Leaving : '||l_procedure, 40);
61 trace(l_message,null);
62 RAISE;
63 END get_max_act_sequence;
64
65 --------------------------------------------------------------------------
66 -- --
67 -- Name : SET_LOCATION --
68 -- Type : PROCEDURE --
69 -- Access : Public --
70 -- Description : Procedure to set the location based on the trace --
71 -- --
72 -- Parameters : --
73 -- IN : p_message VARCHAR2 --
74 -- p_step number --
75 -- p_trace VARCHAR2 --
76 -- --
77 -- Change History : --
78 --------------------------------------------------------------------------
79 -- Rev# Date Userid Description --
80 --------------------------------------------------------------------------
81 -- 1.0 30/12/02 statkar Created this function --
82 --------------------------------------------------------------------------
83 PROCEDURE set_location (p_trace IN BOOLEAN
84 ,p_message IN VARCHAR2
85 ,p_step IN INTEGER
86 )
87 IS
88 BEGIN
89 IF p_trace THEN
90 hr_utility.set_location(SUBSTR('INLOG: '||p_message,1,72), p_step);
91 END IF;
92
93 END set_location;
94
95 --------------------------------------------------------------------------
96 -- --
97 -- Name : TRACE --
98 -- Type : PROCEDURE --
99 -- Access : Public --
100 -- Description : Procedure to set the trace --
101 -- --
102 -- Parameters : --
103 -- IN : p_message varchar2 --
104 -- p_value varchar2 --
105 -- --
106 ---------------------------------------------------------------------------
107 PROCEDURE trace (p_message IN VARCHAR2
108 ,p_value IN VARCHAR2)
109 IS
110 BEGIN
111 hr_utility.trace(RPAD(SUBSTR('INTRC: '||p_message,1,60),60,' ')||': '||p_value);
112 END trace;
113
114 --------------------------------------------------------------------------
115 -- --
116 -- Name : TRACE --
117 -- Type : Function --
118 -- Access : Public --
119 -- Description : Function to set the trace in Fast formulas --
120 -- --
121 -- Parameters : --
122 -- IN : p_message varchar2 --
123 -- p_value varchar2 --
124 -- --
125 ---------------------------------------------------------------------------
126 FUNCTION trace (p_message IN VARCHAR2
127 ,p_value IN VARCHAR2) RETURN NUMBER
128 IS
129 BEGIN
130 trace(p_message,p_value);
131 RETURN 0;
132 END trace;
133
134 --------------------------------------------------------------------------
135 -- --
136 -- Name : GET_PAY_MESSAGE --
137 -- Type : FUNCTION --
138 -- Access : Public --
139 -- Description : Function to construct the message for FF --
140 -- This function is used to obtain a message. --
141 -- The token parameters must be of the form --
142 -- 'TOKEN_NAME:TOKEN_VALUE' i.e. --
143 -- If you want to set the value of a token called --
144 -- FUNCTION to CN_PHF_CALCULATION the token parameter --
145 -- would be 'FUNCTION:CN_PHF_CALCULATION' --
146 -- --
147 -- Parameters : --
148 -- IN : p_message_name VARCHAR2 --
149 -- p_token1 VARCHAR2 --
150 -- p_token2 VARCHAR2 --
151 -- p_token3 VARCHAR2 --
152 -- p_token4 VARCHAR2 --
153 -- RETURN : VARCHAR2 --
154 -- --
155 -- Change History : --
156 --------------------------------------------------------------------------
157 -- Rev# Date Userid Description --
158 --------------------------------------------------------------------------
159 -- 1.0 28-Aug-2004 statkar Created --
160 ---------------------------------------------------------------------------
161 FUNCTION get_pay_message
162 (p_message_name IN VARCHAR2
163 ,p_token1 IN VARCHAR2 DEFAULT NULL
164 ,p_token2 IN VARCHAR2 DEFAULT NULL
165 ,p_token3 IN VARCHAR2 DEFAULT NULL
166 ,p_token4 IN VARCHAR2 DEFAULT NULL
167 )
168 RETURN VARCHAR2
169
170 IS
171 l_message VARCHAR2(2000);
172 l_token_name VARCHAR2(20);
173 l_token_value VARCHAR2(80);
174 l_colon_position NUMBER;
175 l_proc VARCHAR2(50);
176 --
177 BEGIN
178 g_debug := hr_utility.debug_enabled;
179 l_proc := g_package||'get_pay_message';
180 --
181 set_location(g_debug, 'Entered '||l_proc,5);
182 set_location(g_debug, '. Message Name: '||p_message_name,40);
183
184 hr_utility.set_message(800,p_message_name);
185
186 IF p_token1 IS NOT NULL THEN
187 /* Obtain token 1 name and value */
188 l_colon_position := INSTR(p_token1,':');
189 l_token_name := SUBSTR(p_token1,1,l_colon_position-1);
190 l_token_value := SUBSTR(SUBSTR(p_token1,l_colon_position+1,LENGTH(p_token1)) ,1,77);
191 hr_utility.set_message_token(l_token_name,l_token_value);
192 set_location(g_debug,'. Token1: '||l_token_name||'. Value: '||l_token_value,50);
193 END IF;
194
195 IF p_token2 IS NOT NULL THEN
196 /* Obtain token 2 name and value */
197 l_colon_position := INSTR(p_token2,':');
198 l_token_name := SUBSTR(p_token2,1,l_colon_position-1);
199 l_token_value := SUBSTR(SUBSTR(p_token2,l_colon_position+1,LENGTH(p_token2)) ,1,77);
200 hr_utility.set_message_token(l_token_name,l_token_value);
201 set_location(g_debug,'. Token2: '||l_token_name||'. Value: '||l_token_value,60);
202 END IF;
203
204 IF p_token3 IS NOT NULL THEN
205 /* Obtain token 3 name and value */
206 l_colon_position := INSTR(p_token3,':');
207 l_token_name := SUBSTR(p_token3,1,l_colon_position-1);
208 l_token_value := SUBSTR(SUBSTR(p_token3,l_colon_position+1,LENGTH(p_token3)) ,1,77);
209 hr_utility.set_message_token(l_token_name,l_token_value);
210 set_location(g_debug,'. Token3: '||l_token_name||'. Value: '||l_token_value,70);
211 END IF;
212
213 IF p_token4 IS NOT NULL THEN
214 /* Obtain token 4 name and value */
215 l_colon_position := INSTR(p_token4,':');
216 l_token_name := SUBSTR(p_token4,1,l_colon_position-1);
217 l_token_value := SUBSTR(SUBSTR(p_token4,l_colon_position+1,LENGTH(p_token4)) ,1,77);
218 hr_utility.set_message_token(l_token_name,l_token_value);
219 set_location(g_debug,'. Token4: '||l_token_name||'. Value: '||l_token_value,80);
220 END IF;
221
222 l_message := SUBSTRB(hr_utility.get_message,1,250);
223
224 set_location(g_debug,'leaving '||l_proc,100);
225 RETURN l_message;
226
227 END get_pay_message;
228
229 --------------------------------------------------------------------------
230 -- --
231 -- Name : NULL_MESSAGES --
232 -- Type : PROCEDURE --
233 -- Access : Public --
234 -- Description : Function to null the messages --
235 -- Parameters : --
236 -- Change History : --
237 --------------------------------------------------------------------------
238 -- Rev# Date Userid Description --
239 --------------------------------------------------------------------------
240 -- 1.0 28-AUG-04 statkar Created this function --
241 --------------------------------------------------------------------------
242 PROCEDURE null_message
243 (p_token_name IN OUT NOCOPY pay_in_utils.char_tab_type
244 ,p_token_value IN OUT NOCOPY pay_in_utils.char_tab_type)
245 IS
246
247 BEGIN
248 p_token_name.delete;
249 p_token_value.delete;
250 RETURN;
251 END null_message;
252 --------------------------------------------------------------------------
253 -- --
254 -- Name : RAISE_MESSAGE --
255 -- Type : PROCEDURE --
256 -- Access : Public --
257 -- Description : Function to set and raise the messages --
258 -- Parameters : --
259 -- Change History : --
260 --------------------------------------------------------------------------
261 -- Rev# Date Userid Description --
262 --------------------------------------------------------------------------
263 -- 1.0 28-AUG-04 statkar Created this function --
264 --------------------------------------------------------------------------
265 PROCEDURE raise_message
266 (p_application_id IN NUMBER
267 ,p_message_name IN VARCHAR2
268 ,p_token_name IN OUT NOCOPY pay_in_utils.char_tab_type
269 ,p_token_value IN OUT NOCOPY pay_in_utils.char_tab_type)
270 IS
271 cnt NUMBER;
272 BEGIN
273 IF p_message_name IS NOT NULL AND p_message_name <> 'SUCCESS' THEN
274 cnt:= p_token_name.count;
275 hr_utility.set_message(p_application_id, p_message_name);
276 FOR i IN 1..cnt
277 LOOP
278 hr_utility.set_message_token(p_token_name(i),p_token_value(i));
279 END LOOP;
280 hr_utility.raise_error;
281 END IF;
282
283 END raise_message;
284
285 --------------------------------------------------------------------------
286 -- --
287 -- Name : GET_USER_TABLE_VALUE --
288 -- Type : FUNCTION --
289 -- Access : Public --
290 -- Description : Function to fetch the user table value --
291 -- --
292 -- Parameters : --
293 -- IN : p_business_group_id NUMBER --
294 -- p_table_name VARCHAR2 --
295 -- p_column_name VARCHAR2 --
296 -- p_row_name VARCHAR2 --
297 -- p_row_value VARCHAR2 --
298 -- RETURN : VARCHAR2 --
299 -- OUT : p_message VARCHAR2 --
300 -- --
301 -- Change History : --
302 --------------------------------------------------------------------------
303 -- Rev# Date Userid Description --
304 --------------------------------------------------------------------------
305 -- 1.0 20-May-03 statkar Created this function --
306 -- 1.1 24-Sep-04 statkar 3902024 - Changed p_row_name to p_row_value-
307 ---------------------------------------------------------------------------
308 FUNCTION get_user_table_value
309 (p_business_group_id IN NUMBER
310 ,p_table_name IN VARCHAR2
311 ,p_column_name IN VARCHAR2
312 ,p_row_name IN VARCHAR2
313 ,p_row_value IN VARCHAR2
314 ,p_effective_date IN DATE
315 ,p_message OUT NOCOPY VARCHAR2
316 )
317 RETURN VARCHAR2
318 IS
319 l_value pay_user_column_instances_f.value%TYPE;
320 l_proc VARCHAR2(100);
321 BEGIN
322 l_proc := g_package||'get_user_table_value';
323 g_debug := hr_utility.debug_enabled;
324
325 set_location(g_debug, 'Entering : '||l_proc,10);
326
327 l_value := hruserdt.get_table_value
328 ( p_bus_group_id => p_business_group_id
329 ,p_table_name => p_table_name
330 ,p_col_name => p_column_name
331 ,p_row_value => p_row_value
332 ,p_effective_date => p_effective_date
333 );
334
335 p_message := 'SUCCESS';
336
337 set_location(g_debug, 'Leaving : '||l_proc,20);
338
339 RETURN l_value;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 p_message := get_pay_message('PER_IN_USER_TABLE_INCOMPLETE','TABLE:'||p_table_name,'COLUMN:'||p_column_name,'VALUE:'||p_row_value);
344 trace (p_message,null);
345 RETURN -1;
346
347 END get_user_table_value;
348
349
350 --------------------------------------------------------------------------
351 -- Name : GET_RUN_TYPE_NAME --
352 -- Type : Function --
353 -- Access : Public --
354 -- Description : Function checks the Run Type used for the Payroll --
355 -- Run --
356 -- --
357 -- --
358 -- Parameters : --
359 -- IN : p_payroll_action_id IN NUMBER --
360 -- --
361 -- Version Date Author Bug Description --
362 -- =====================================================================--
363 -- 115.0 20-Sep-04 ABHJAIN 3683543 Initial Version --
364 -- --
365 --------------------------------------------------------------------------
366
367
368 FUNCTION get_run_type_name (p_payroll_action_id IN NUMBER
369 ,p_assignment_action_id IN NUMBER)
370 RETURN VARCHAR2 IS
371 l_run_type_name VARCHAR2(80);
372 BEGIN
373
374 SELECT prt.run_type_name into l_run_type_name
375 FROM pay_run_types_f prt,
376 pay_payroll_actions ppa,
377 pay_assignment_actions paa
378 WHERE ppa.payroll_action_id = p_payroll_action_id
379 AND paa.payroll_action_id = ppa.payroll_action_id
380 AND prt.run_type_id = paa.run_type_id
381 AND paa.assignment_action_id = p_assignment_action_id
382 AND ppa.effective_date between prt.effective_start_date
383 and prt.effective_end_date;
384
385 RETURN (l_run_type_name);
386
387 EXCEPTION
388
389 WHEN OTHERS THEN
390 RETURN NVL(l_run_type_name,'-1');
391
392 END get_run_type_name ;
393
394 --------------------------------------------------------------------------
395 -- --
396 -- Name : GET_EFFECTIVE_DATE --
397 -- Type : FUNCTION --
398 -- Access : Public --
399 -- Description : Function returns the calculates the effective date --
400 -- based on the following conditions: --
401 -- 1) If effective date is passed returns the same --
402 -- 2) Else use the System date. --
403 -- --
404 -- Parameters : --
405 -- IN : p_effective_date DATE --
406 -- --
407 -- Change History : --
408 --------------------------------------------------------------------------
409 -- Rev# Date Userid Description --
410 --------------------------------------------------------------------------
411 -- 1.0 24/09/04 puchil Created this function --
412 --------------------------------------------------------------------------
413 FUNCTION get_effective_date(p_effective_date IN DATE)
414 RETURN DATE
415 IS
416 --
417 l_proc VARCHAR2(120);
418 l_effective_date DATE;
419 --
420 BEGIN
421 --
422 l_proc := g_package||'.get_effective_date';
423 set_location(g_debug, l_proc, 10);
424 --
425 -- If the effective date is passed as null then
426 -- use the system date for calculation.
427 --
428 IF p_effective_date is null THEN
429 --
430 l_effective_date := to_date(to_char(sysdate, 'DD-MM-YYYY'), 'DD-MM-YYYY');
431 set_location(g_debug, l_proc, 20);
432 --
433 ELSE
434 --
435 l_effective_date := p_effective_date;
436 set_location(g_debug, l_proc, 30);
437 --
438 END IF;
439 --
440 set_location(g_debug, l_proc, 40);
441 RETURN l_effective_date;
442 --
443 END get_effective_date;
444
445 --------------------------------------------------------------------------
446 -- --
447 -- Name : GET_PERSON_ID --
448 -- Type : FUNCTION --
449 -- Access : Public --
450 -- Description : Function returns the person_id of the assignment --
451 -- as of effective date. IF effective date is null --
452 -- then details are retrieved as of sysdate. --
453 -- --
454 -- Parameters : --
455 -- IN : p_assignment_id NUMBER --
456 -- p_effective_date DATE --
457 -- --
458 -- Change History : --
459 --------------------------------------------------------------------------
460 -- Rev# Date Userid Description --
461 --------------------------------------------------------------------------
462 -- 1.0 30/12/02 puchil Created this function --
463 --------------------------------------------------------------------------
464 FUNCTION get_person_id
465 (p_assignment_id IN per_assignments_f.assignment_id%TYPE
466 ,p_effective_date IN DATE default null)
467 RETURN per_assignments_f.person_id%TYPE
468 IS
469 --
470 CURSOR csr_person_details(c_effective_date IN DATE)
471 IS
472 SELECT person_id
473 FROM per_assignments_f
474 WHERE assignment_id = p_assignment_id
475 AND c_effective_date BETWEEN effective_start_date
476 AND effective_end_date;
477 --
478 l_proc VARCHAR2(100);
479 l_effective_date DATE;
480 l_person_id per_assignments_f.person_id%TYPE;
481 --
482 BEGIN
483 --
484 l_proc := g_package||'get_person_id';
485 g_debug := hr_utility.debug_enabled;
486 set_location(g_debug, 'Entering: ' || l_proc, 10);
487 --
488 IF g_debug THEN
489 --
490 trace('Assignment ID : ', p_assignment_id);
491 trace('Effective Date: ', p_effective_date);
492 --
493 END IF;
494 --
495 IF p_assignment_id IS NULL THEN
496 --
497 set_location(g_debug, 'Leaving: '||l_proc, 20);
498 RETURN NULL;
499 --
500 END IF;
501 --
502 l_effective_date := get_effective_date(p_effective_date);
503 --
504 OPEN csr_person_details(l_effective_date);
505 FETCH csr_person_details INTO l_person_id;
506 CLOSE csr_person_details;
507 --
508 set_location(g_debug, 'Leaving: '||l_proc, 50);
509 --
510 RETURN l_person_id;
511 --
512 EXCEPTION
513 WHEN OTHERS THEN
514 RETURN null;
515
516 END get_person_id;
517
518 --------------------------------------------------------------------------
519 -- --
520 -- Name : GET_ASSIGNMENT_ID --
521 -- Type : FUNCTION --
522 -- Access : Public --
523 -- Description : Function returns the assignment_if of the person --
524 -- as of effective date. IF effective date is null --
525 -- then details are retrieved as of sysdate. --
526 -- --
527 -- Parameters : --
528 -- IN : p_person_id NUMBER --
529 -- p_effective_date DATE --
530 -- --
531 -- Change History : --
532 --------------------------------------------------------------------------
533 -- Rev# Date Userid Description --
534 --------------------------------------------------------------------------
535 -- 1.0 30/12/02 puchil Created this function --
536 --------------------------------------------------------------------------
537 FUNCTION get_assignment_id
538 (p_person_id IN per_people_f.person_id%TYPE
539 ,p_effective_date IN DATE default null)
540 RETURN per_assignments_f.assignment_id%TYPE
541 IS
542 --
543 CURSOR csr_assignment_details(c_effective_date IN DATE)
544 IS
545 SELECT assignment_id
546 FROM per_assignments_f
547 WHERE person_id = p_person_id
548 AND c_effective_date BETWEEN effective_start_date
549 AND effective_end_date;
550 --
551 l_proc VARCHAR2(100);
552 l_effective_date DATE;
553 l_assignment_id per_assignments_f.assignment_id%TYPE;
554 --
555 BEGIN
556 --
557 l_proc := g_package||'.get_assignment_id';
558 g_debug := hr_utility.debug_enabled;
559 set_location(g_debug, 'Entering: ' || l_proc, 10);
560 --
561 IF g_debug THEN
562 --
563 trace('Assignment ID : ' , p_person_id);
564 trace('Effective Date: ' , p_effective_date);
565 --
566 END IF;
567 --
568 IF p_person_id IS NULL THEN
569 --
570 set_location(g_debug, 'Leaving: '||l_proc, 20);
571 RETURN NULL;
572 --
573 END IF;
574 --
575 l_effective_date := get_effective_date(p_effective_date);
576 --
577 OPEN csr_assignment_details(l_effective_date);
578 FETCH csr_assignment_details INTO l_assignment_id;
579 CLOSE csr_assignment_details;
580 --
581 set_location(g_debug, 'Leaving: '||l_proc, 30);
582 --
583 RETURN l_assignment_id;
584 --
585 EXCEPTION
586 WHEN OTHERS THEN
587 RETURN null;
588
589 END get_assignment_id;
590
591 --------------------------------------------------------------------------
592 -- --
593 -- Name : NEXT_TAX_YEAR --
594 -- Type : FUNCTION --
595 -- Access : Public --
596 -- Description : Function returns the beginning of the next finan- --
597 -- cial year calculated based of the p_date input. --
598 -- --
599 -- Parameters : --
600 -- IN : p_date DATE --
601 -- --
602 -- Change History : --
603 --------------------------------------------------------------------------
604 -- Rev# Date Userid Description --
605 --------------------------------------------------------------------------
606 -- 1.0 23/09/04 puchil Created this function --
607 --------------------------------------------------------------------------
608 FUNCTION next_tax_year(p_date IN DATE)
609 RETURN DATE
610 IS
611 l_year number(4);
612 l_start DATE;
613 l_start_dd_mm VARCHAR2(6);
614 BEGIN
615 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
616 l_start_dd_mm := '01-04-';
617
618 IF p_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy') THEN
619 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year+1),'dd-mm-yyyy');
620 ELSE
621 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy');
622 END IF;
623 RETURN l_start;
624 END next_tax_year;
625
626 -------------------------------------------------------------------------
627 -- --
628 -- Name : validate_dates --
629 -- Type : FUNCTION --
630 -- Access : Public --
631 -- Description : This function checks if the effective end date is --
632 -- greater than or equal to effective start date . --
633 -- Parameters : --
634 -- IN : p_effective_start_date IN DATE --
635 -- p_effective_end_date IN DATE --
636 -- RETURN : BOOLEAN --
637 -- Change History : --
638 --------------------------------------------------------------------------
639 -- Rev# Date Userid Description --
640 --------------------------------------------------------------------------
641 -- 1.0 27/09/04 statkar Created this function --
642 --------------------------------------------------------------------------
643 FUNCTION validate_dates(p_start_date IN DATE,
644 p_end_date IN DATE)
645 RETURN BOOLEAN
646 IS
647 l_end_date DATE;
648 BEGIN
649 l_end_date := NVL(p_end_date,to_date('31-12-4712','DD-MM-YYYY'));
650
651 IF l_end_date < p_start_date THEN
652 RETURN FALSE;
653 ELSE
654 RETURN TRUE;
655 END IF;
656 END validate_dates;
657
658 -------------------------------------------------------------------------
659 -- --
660 -- Name : chk_org_class --
661 -- Type : FUNCTION --
662 -- Access : Public --
663 -- Description : This function checks if the organization passed has --
664 -- the classification enabled --
665 -- Parameters : --
666 -- IN : p_effective_start_date IN DATE --
667 -- p_effective_end_date IN DATE --
668 -- RETURN : BOOLEAN --
669 -- Change History : --
670 --------------------------------------------------------------------------
671 -- Rev# Date Userid Description --
672 --------------------------------------------------------------------------
673 -- 1.0 27/09/04 statkar Created this function --
674 --------------------------------------------------------------------------
675 FUNCTION chk_org_class(p_organization_id IN NUMBER,
676 p_org_class IN VARCHAR2)
677 RETURN BOOLEAN
678 IS
679 CURSOR csr_exists IS
680 SELECT '1'
681 FROM hr_organization_information
682 WHERE organization_id = p_organization_id
683 AND org_information_context = 'CLASS'
684 AND org_information1 = p_org_class
685 AND org_information2 = 'Y';
686
687 l_dummy VARCHAR2(1);
688
689 BEGIN
690
691 OPEN csr_exists;
692 FETCH csr_exists
693 INTO l_dummy;
694
695 IF csr_exists%NOTFOUND OR l_dummy IS NULL THEN
696 RETURN FALSE;
697 ELSE
698 RETURN TRUE;
699 END IF;
700 CLOSE csr_exists;
701
702 END chk_org_class;
703
704 -------------------------------------------------------------------------
705 -- --
706 -- Name : number_to_words --
707 -- Type : FUNCTION --
708 -- Access : Public --
709 -- Description : This function returns the passed number after --
710 -- converting it into words --
711 -- Parameters : --
712 -- IN : p_value IN NUMBER --
713 -- RETURN : VARCHAR2 --
714 -- Change History : --
715 --------------------------------------------------------------------------
716 -- Rev# Date Userid Description --
717 --------------------------------------------------------------------------
718 -- 1.0 02/11/04 abhjain Created this function --
719 -- 1.1 04/07/05 abhjain Corrected the function --
720 --------------------------------------------------------------------------
721 FUNCTION number_to_words(p_value IN NUMBER)
722 RETURN VARCHAR2
723 is
724 num1 NUMBER(12,2);
725 numy NUMBER(12,2);
726 l_num_temp NUMBER;
727 l_num_temp1 NUMBER(2);
728 l_num_temp2 NUMBER(12,2);
729 l_num_temp3 NUMBER;
730 money NUMBER;
731 num2 VARCHAR2(20);
732 money_str VARCHAR2(200);
733 t_str VARCHAR2(200);
734 l_char1 VARCHAR2(20);
735 l_char2 VARCHAR2(20);
736 BEGIN
737 num1 := p_value;
738 money := num1;
739 num1 := floor(num1/1000000000);
740 IF num1 > 0 THEN
741 num2:= to_char(to_date(num1,'j'),'jsp');
742 money_str := num2||' hundred';
743 END IF;
744 num1 := mod(money,1000000000);
745 money := num1;
746 l_num_temp3 := floor(num1/10000000);
747 IF l_num_temp3 > 0 THEN
748 t_str := to_char(to_date( l_num_temp3 ,'j'),'jsp');
749 money_str := money_str||' '|| t_str||' crore';
750 num1 := mod(num1,10000000);
751 END IF;
752 l_num_temp3 := floor(num1/100000);
753 IF l_num_temp3 > 0 THEN
754 t_str := to_char(to_date( l_num_temp3 ,'j'),'jsp');
755 money_str := money_str||' '||t_str||' lac';
756 num1 := mod(num1,100000);
757 END IF;
758 l_num_temp3 := floor(num1/1000);
759 IF l_num_temp3 > 0 THEN
760 t_str := to_char(to_date( l_num_temp3 ,'j'),'jsp');
761 money_str := money_str||' '||t_str||' thousand';
762 num1 := mod(num1,1000);
763 END IF;
764 l_num_temp3 := floor(num1/100);
765 IF l_num_temp3 > 0 THEN
766 t_str := to_char(to_date( l_num_temp3 ,'j'),'jsp');
767 money_str := money_str||' '||t_str||' hundred';
768 num1 := mod(num1,100);
769 END IF;
770 num1 := floor(num1);
771 IF num1 > 0 THEN
772 t_str := to_char(to_date(num1,'j'),'jsp');
773 money_str := money_str ||' '|| t_str;
774 END IF;
775 l_num_temp2 := mod(p_value,1)*100;
776 IF l_num_temp2 > 0 THEN
777 l_char2 := to_char(to_Date(l_num_temp2,'j'),'jsp');
778 RETURN (replace('Rupees'||nvl(money_str, ' Zero')||' and '||l_char2||' paise only', '-', ' '));
779 ELSE
780 RETURN(replace('Rupees'||nvl(money_str, ' Zero')||' and zero paise only', '-', ' '));
781 END IF;
782
783
784 END number_to_words ;
785
786 -------------------------------------------------------------------------
787 -- --
788 -- Name : encode_html_string --
789 -- Type : FUNCTION --
790 -- Access : Public --
791 -- Description : This procedure encodes the HTML literals . --
792 -- --
793 -- Parameters : --
794 -- IN : p_value IN NUMBER --
795 -- RETURN : VARCHAR2 --
796 --------------------------------------------------------------------------
797 -- Change History : --
798 --------------------------------------------------------------------------
799 -- Rev# Date Userid Bug Description --
800 --------------------------------------------------------------------------
801 -- 1.0 22/12/04 aaagarwa 4070869 Created this function --
802 --------------------------------------------------------------------------
803
804 FUNCTION encode_html_string(p_value IN VARCHAR2)
805 RETURN VARCHAR2
806 IS
807 TYPE html_rec IS RECORD
808 (html_char VARCHAR2(2)
809 ,encoded VARCHAR2(10)
810 );
811
812 TYPE html_char_tab IS TABLE OF html_rec INDEX BY binary_integer;
813
814 char_list html_char_tab;
815 i NUMBER;
816 l_value VARCHAR2(1000);
817 begin
818 IF p_value IS NULL then
819 RETURN null;
820 END IF;
821
822 char_list(0).html_char:='&';
823 char_list(0).encoded:='&';
824
825 char_list(1).html_char:='>';
826 char_list(1).encoded:='>';
827
828 char_list(2).html_char:='<';
829 char_list(2).encoded:='<';
830
831 i:=0;
832 l_value := p_value;
833 while(i<char_list.count())
834 LOOP
835 l_value:=replace(l_value,char_list(i).html_char,char_list(i).encoded);
836 i:=i+1;
837 END LOOP;
838
839 RETURN l_value;
840
841 END encode_html_string;
842
843
844
845 --------------------------------------------------------------------------
846 -- --
847 -- Name : GET_SCL_SEGMENT_ON_DATE --
848 -- Type : Function --
849 -- Access : Public --
850 -- Description : Returns --
851 -- Parameters : --
852 -- IN : p_assigment_id VARCHAR2 --
853 -- : p_business_group_id NUMBER --
854 -- : p_date DATE --
855 -- : p_column VARCHAR2 --
856 -- OUT : N/A --
857 -- RETURN : VARCHAR2 --
858 -- --
859 --------------------------------------------------------------------------
860
861 FUNCTION get_scl_segment_on_date(p_assignment_id IN NUMBER
862 ,p_business_group_id IN NUMBER
863 ,p_date IN DATE
864 ,p_column IN VARCHAR2)
865 RETURN VARCHAR2
866 IS
867 CURSOR cur_scl_value (p_assignment_id NUMBER
868 ,p_business_group_id NUMBER
869 ,p_date DATE)
870 IS
871 SELECT hsc.segment1
872 ,hsc.segment2
873 ,hsc.segment3
874 ,hsc.segment4
875 ,hsc.segment5
876 ,hsc.segment6
877 ,hsc.segment7
878 ,hsc.segment8
879 ,hsc.segment9
880 ,hsc.segment10
881 ,hsc.segment11
882 ,hsc.segment12
883 FROM per_assignments_f paf
884 ,hr_soft_coding_keyflex hsc
885 WHERE paf.assignment_id = p_assignment_id
886 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
887 AND paf.business_group_id = p_business_group_id
888 AND p_date BETWEEN paf.effective_start_date
889 AND paf.effective_end_date;
890
891 l_segment1 hr_soft_coding_keyflex.segment1%TYPE;
892 l_segment2 hr_soft_coding_keyflex.segment1%TYPE;
893 l_segment3 hr_soft_coding_keyflex.segment1%TYPE;
894 l_segment4 hr_soft_coding_keyflex.segment1%TYPE;
895 l_segment5 hr_soft_coding_keyflex.segment1%TYPE;
896 l_segment6 hr_soft_coding_keyflex.segment1%TYPE;
897 l_segment7 hr_soft_coding_keyflex.segment1%TYPE;
898 l_segment8 hr_soft_coding_keyflex.segment1%TYPE;
899 l_segment9 hr_soft_coding_keyflex.segment1%TYPE;
900 l_segment10 hr_soft_coding_keyflex.segment1%TYPE;
901 l_segment11 hr_soft_coding_keyflex.segment1%TYPE;
902 l_segment12 hr_soft_coding_keyflex.segment1%TYPE;
903 l_message VARCHAR2(255);
904 l_procedure VARCHAR2(100);
905
906 BEGIN
907
908 l_procedure := g_package||'get_org_id';
909 g_debug := hr_utility.debug_enabled;
910
911 set_location(g_debug,'Entering : '||l_procedure, 10);
912
913 OPEN cur_scl_value (p_assignment_id
914 ,p_business_group_id
915 ,p_date);
916 FETCH cur_scl_value into l_segment1
917 ,l_segment2
918 ,l_segment3
919 ,l_segment4
920 ,l_segment5
921 ,l_segment6
922 ,l_segment7
923 ,l_segment8
924 ,l_segment9
925 ,l_segment10
926 ,l_segment11
927 ,l_segment12;
928
929 set_location (g_debug,'l_segment1 = '||l_segment1,10);
930 set_location (g_debug,'l_segment2 = '||l_segment2,20);
931 set_location (g_debug,'l_segment3 = '||l_segment3,30);
932 set_location (g_debug,'l_segment4 = '||l_segment4,40);
933 set_location (g_debug,'l_segment5 = '||l_segment5,50);
934 set_location (g_debug,'l_segment6 = '||l_segment6,60);
935 set_location (g_debug,'l_segment7 = '||l_segment7,70);
936 set_location (g_debug,'l_segment8 = '||l_segment8,80);
937 set_location (g_debug,'l_segment9 = '||l_segment9,90);
938 set_location (g_debug,'l_segment10 = '||l_segment10,100);
939 set_location (g_debug,'l_segment11 = '||l_segment11,110);
940 set_location (g_debug,'l_segment12 = '||l_segment12,120);
941 CLOSE cur_scl_value;
942
943 IF p_column = 'segment1' THEN
944 RETURN l_segment1;
945 ELSIF p_column = 'segment2' THEN
946 RETURN l_segment2;
947 ELSIF p_column = 'segment3' THEN
948 RETURN l_segment3;
949 ELSIF p_column = 'segment4' THEN
950 RETURN l_segment4;
951 ELSIF p_column = 'segment5' THEN
952 RETURN l_segment5;
953 ELSIF p_column = 'segment6' THEN
954 RETURN l_segment6;
955 ELSIF p_column = 'segment7' THEN
956 RETURN l_segment7;
957 ELSIF p_column = 'segment8' THEN
958 RETURN l_segment8;
959 ELSIF p_column = 'segment9' THEN
960 RETURN l_segment9;
961 ELSIF p_column = 'segment10' THEN
962 RETURN l_segment10;
963 ELSIF p_column = 'segment11' THEN
964 RETURN l_segment11;
965 ELSIF p_column = 'segment12' THEN
966 RETURN NVL(l_segment12,'0');
967 END IF;
968
969 set_location(g_debug,'Leaving : '||l_procedure, 30);
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
974 set_location(g_debug,' Leaving : '||l_procedure, 30);
975 trace(l_message,null);
976 RETURN NULL;
977
978
979 END get_scl_segment_on_date;
980 -------------------------------------------------------------------------
981 -- --
982 -- Name : get_element_link_id --
983 -- Type : FUNCTION --
984 -- Access : Public --
985 -- Description : This function returns element link id for an --
986 -- assignment for an element as on a given date --
987 -- Parameters : --
988 -- IN : p_assignment_id NUMBER --
989 -- p_effective_date DATE --
990 -- p_element_type_id NUMBER --
991 -- RETURN : NUMBER --
992 -- Change History : --
993 --------------------------------------------------------------------------
994 -- Rev# Date Userid Description --
995 --------------------------------------------------------------------------
996 -- 1.0 06/12/05 aaagarwa Created this function --
997 --------------------------------------------------------------------------
998 FUNCTION get_element_link_id(p_assignment_id IN NUMBER
999 ,p_effective_date IN DATE
1000 ,p_element_type_id IN NUMBER
1001 )
1002 RETURN NUMBER
1003 IS
1004
1005 CURSOR c_bg
1006 IS
1007 SELECT business_group_id
1008 FROM per_assignments_f
1009 where assignment_id = p_assignment_id
1010 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1011
1012
1013 CURSOR c_element_link_details(p_element_type_id NUMBER
1014 ,p_effective_date DATE
1015 ,p_bg_id NUMBER
1016 )
1017 IS
1018 SELECT element_link_id,
1019 payroll_id,
1020 link_to_all_payrolls_flag,
1021 organization_id,
1022 position_id,
1023 job_id,
1024 grade_id,
1025 location_id,
1026 pay_basis_id,
1027 employment_category,
1028 people_group_id
1029 FROM pay_element_links_f
1030 WHERE element_type_id = p_element_type_id
1031 and business_group_id = p_bg_id
1032 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1033
1034 CURSOR c_assignment_details(p_assignment_id NUMBER
1035 ,p_effective_date DATE
1036 )
1037 IS
1038 SELECT payroll_id,
1039 organization_id,
1040 position_id,
1041 job_id,
1042 grade_id,
1043 location_id,
1044 pay_basis_id,
1045 employment_category
1046 FROM per_assignments_f
1047 WHERE assignment_id = p_assignment_id
1048 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1049
1050
1051 CURSOR c_link_usage(p_assignment_id NUMBER
1052 ,p_element_link_id NUMBER
1053 ,p_effective_date DATE
1054 )
1055 IS
1056 SELECT 1
1057 FROM pay_assignment_link_usages_f
1058 WHERE assignment_id = p_assignment_id
1059 AND element_link_id = p_element_link_id
1060 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1061
1062
1063 l_element_link_id pay_element_links_f.element_link_id%TYPE;
1064 l_link_payroll_id pay_element_links_f.payroll_id%TYPE;
1065 l_link_to_all_payrolls_flag pay_element_links_f.link_to_all_payrolls_flag%TYPE;
1066 l_link_org_id pay_element_links_f.organization_id%TYPE;
1067 l_link_pos_id pay_element_links_f.position_id%TYPE;
1068 l_link_job_id pay_element_links_f.job_id%TYPE;
1069 l_link_grd_id pay_element_links_f.grade_id%TYPE;
1070 l_link_loc_id pay_element_links_f.location_id%TYPE;
1071 l_link_pay_basis_id pay_element_links_f.pay_basis_id%TYPE;
1072 l_link_emp_catg_id pay_element_links_f.employment_category%TYPE;
1073 l_link_pep_grp_id pay_element_links_f.people_group_id%TYPE;
1074
1075 l_asg_payroll_id per_all_assignments_f.payroll_id%TYPE;
1076 l_asg_org_id per_all_assignments_f.organization_id%TYPE;
1077 l_asg_pos_id per_all_assignments_f.position_id%TYPE;
1078 l_asg_job_id per_all_assignments_f.job_id%TYPE;
1079 l_asg_grd_id per_all_assignments_f.grade_id%TYPE;
1080 l_asg_loc_id per_all_assignments_f.location_id%TYPE;
1081 l_asg_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
1082 l_asg_emp_catg_id per_all_assignments_f.employment_category%TYPE;
1083 l_bus_grp_id NUMBER;
1084
1085
1086 l_flag NUMBER;
1087
1088 BEGIN
1089 OPEN c_bg;
1090 FETCH c_bg INTO l_bus_grp_id;
1091 CLOSE c_bg;
1092
1093 OPEN c_element_link_details(p_element_type_id,p_effective_date,l_bus_grp_id);
1094 FETCH c_element_link_details INTO l_element_link_id,
1095 l_link_payroll_id,
1096 l_link_to_all_payrolls_flag,
1097 l_link_org_id,
1098 l_link_pos_id,
1099 l_link_job_id,
1100 l_link_grd_id,
1101 l_link_loc_id,
1102 l_link_pay_basis_id,
1103 l_link_emp_catg_id,
1104 l_link_pep_grp_id;
1105 CLOSE c_element_link_details;
1106
1107 OPEN c_assignment_details(p_assignment_id,p_effective_date);
1108 FETCH c_assignment_details INTO l_asg_payroll_id,
1109 l_asg_org_id,
1110 l_asg_pos_id,
1111 l_asg_job_id,
1112 l_asg_grd_id,
1113 l_asg_loc_id,
1114 l_asg_pay_basis_id,
1115 l_asg_emp_catg_id;
1116 CLOSE c_assignment_details;
1117
1118 OPEN c_link_usage(p_assignment_id,l_element_link_id,p_effective_date);
1119 FETCH c_link_usage INTO l_flag;
1120 CLOSE c_link_usage;
1121
1122 IF ((l_link_payroll_id IS NOT NULL AND l_link_payroll_id = l_asg_payroll_id)
1123 OR
1124 (l_link_to_all_payrolls_flag ='Y' AND l_asg_payroll_id IS NOT NULL)
1125 OR
1126 (l_link_payroll_id IS NULL AND l_link_to_all_payrolls_flag ='N')
1127 )
1128 AND (l_link_org_id = l_asg_org_id OR l_link_org_id IS NULL)
1129 AND (l_link_pos_id = l_asg_pos_id OR l_link_pos_id IS NULL)
1130 AND (l_link_job_id = l_asg_job_id OR l_link_job_id IS NULL)
1131 AND (l_link_grd_id = l_asg_grd_id OR l_link_grd_id IS NULL)
1132 AND (l_link_loc_id = l_asg_loc_id OR l_link_loc_id IS NULL)
1133 AND (l_link_pay_basis_id = l_asg_pay_basis_id OR l_link_pay_basis_id IS NULL)
1134 AND (l_link_emp_catg_id = l_asg_emp_catg_id OR l_link_emp_catg_id IS NULL)
1135 AND (l_link_pep_grp_id IS NULL OR 1 = l_flag)
1136 THEN
1137 RETURN l_element_link_id;
1138 ELSE
1139 RETURN NULL;
1140 END IF;
1141 END get_element_link_id;
1142
1143 -------------------------------------------------------------------------
1144 -- --
1145 -- Name : chk_element_link --
1146 -- Type : FUNCTION --
1147 -- Access : Public --
1148 -- Description : This function checks if an assignment is eligible --
1149 -- for an element as on a given date. --
1150 -- Parameters : --
1151 -- IN : p_element_name VARCHAR2 --
1152 -- p_assignment_id NUMBER --
1153 -- p_effective_date DATE --
1154 -- OUT : p_element_link_id NUMBER --
1155 -- RETURN : VARCHAR2 --
1156 -- Change History : --
1157 --------------------------------------------------------------------------
1158 -- Rev# Date Userid Description --
1159 --------------------------------------------------------------------------
1160 -- 1.0 27/12/04 lnagaraj Created this function --
1161 --------------------------------------------------------------------------
1162 FUNCTION chk_element_link(p_element_name IN VARCHAR2
1163 ,p_assignment_id IN NUMBER
1164 ,p_effective_date IN DATE
1165 ,p_element_link_id OUT NOCOPY NUMBER)
1166 RETURN VARCHAR2
1167 IS
1168 CURSOR csr_element_type_id
1169 IS
1170 SELECT element_type_id
1171 FROM pay_element_types_f
1172 WHERE (legislation_code = 'IN' OR business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
1173 AND element_name = p_element_name
1174 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1175
1176 /*
1177 CURSOR csr_element_link_details
1178 IS
1179 SELECT link.element_link_id
1180 FROM per_assignments_f assgn
1181 , pay_element_links_f link
1182 , pay_element_types_f types
1183 WHERE assgn.assignment_id = p_assignment_id
1184 AND ((link.payroll_id IS NOT NULL AND link.payroll_id = assgn.payroll_id) OR
1185 (link.link_to_all_payrolls_flag = 'Y' AND assgn.payroll_id IS NOT NULL) OR
1186 (link.payroll_id IS NULL AND link.link_to_all_payrolls_flag = 'N'))
1187 AND (link.organization_id = assgn.organization_id OR link.organization_id IS NULL)
1188 AND (link.position_id = assgn.position_id OR link.position_id IS NULL)
1189 AND (link.job_id = assgn.job_id OR link.job_id IS NULL)
1190 AND (link.grade_id = assgn.grade_id OR link.grade_id IS NULL)
1191 AND (link.location_id = assgn.location_id OR link.location_id IS NULL)
1192 AND (link.pay_basis_id = assgn.pay_basis_id OR link.pay_basis_id IS NULL)
1193 AND (link.employment_category = assgn.employment_category OR link.employment_category IS NULL)
1194 AND (link.people_group_id IS NULL OR
1195 EXISTS ( SELECT 1 FROM pay_assignment_link_usages_f usage
1196 WHERE usage.assignment_id = assgn.assignment_id
1197 AND usage.element_link_id = link.element_link_id
1198 AND p_effective_date BETWEEN usage.effective_start_date AND usage.effective_end_date
1199 ))
1200 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
1201 AND link.business_group_id = assgn.business_group_id
1202 AND link.element_type_id = types.element_type_id
1203 AND types.element_name = p_element_name
1204 AND p_effective_date BETWEEN assgn.effective_start_date
1205 AND assgn.effective_end_date
1206 AND p_effective_date BETWEEN link.effective_start_date
1207 AND link.effective_end_date
1208 AND p_effective_date BETWEEN types.effective_start_date
1209 AND types.effective_end_date;
1210 */
1211 p_message VARCHAR2(30);
1212 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1213
1214 BEGIN
1215 p_message :='SUCCESS';
1216 /*
1217 OPEN csr_element_link_details;
1218 FETCH csr_element_link_details INTO p_element_link_id ;
1219 CLOSE csr_element_link_details;
1220 */
1221 OPEN csr_element_type_id;
1222 FETCH csr_element_type_id INTO l_element_type_id ;
1223 CLOSE csr_element_type_id;
1224
1225 p_element_link_id := get_element_link_id(p_assignment_id
1226 ,p_effective_date
1227 ,l_element_type_id
1228 );
1229
1230 IF p_element_link_id IS NULL THEN
1231 --
1232 p_message := 'PER_IN_MISSING_LINK';
1233 --
1234 END IF;
1235
1236 RETURN p_message;
1237
1238 END chk_element_link;
1239
1240 --------------------------------------------------------------------------
1241 -- --
1242 -- Name : GET_EE_VALUE --
1243 -- Type : FUNCTION --
1244 -- Access : Public --
1245 -- Description : Function to return the element entry value --
1246 -- --
1247 -- Parameters : --
1248 -- IN : p_element_entry_id NUMBER --
1249 -- p_input_name VARCHAR2 --
1250 -- p_effective_date DATE --
1251 -- RETURN : VARCHAR2 --
1252 ---------------------------------------------------------------------------
1253 FUNCTION get_ee_value
1254 (p_element_entry_id IN NUMBER
1255 ,p_input_name IN VARCHAR2
1256 ,p_effective_date IN DATE
1257 )
1258 RETURN VARCHAR2
1259 IS
1260 CURSOR c_entry_value
1261 IS
1262 SELECT val.screen_entry_value
1263 FROM pay_element_entry_values_f val
1264 ,pay_input_values_f inputs
1265 WHERE val.input_value_id = inputs.input_value_id
1266 AND val.element_entry_id = p_element_entry_id
1267 AND inputs.name = p_input_name
1268 AND inputs.legislation_code = 'IN'
1269 AND p_effective_date between val.effective_start_date AND val.effective_end_date
1270 AND p_effective_date between inputs.effective_start_date AND inputs.effective_end_date;
1271 --
1272 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE := NULL;
1273 BEGIN
1274
1275 OPEN c_entry_value;
1276 FETCH c_entry_value INTO l_screen_entry_value;
1277 CLOSE c_entry_value;
1278
1279 RETURN l_screen_entry_value;
1280
1281 END get_ee_value;
1282
1283 --------------------------------------------------------------------------
1284 -- --
1285 -- Name : GET_EE_VALUE --
1286 -- Type : FUNCTION --
1287 -- Access : Public --
1288 -- Description : Function to return the element entry value --
1289 -- --
1290 -- Parameters : --
1291 -- IN : p_element_entry_id NUMBER --
1292 -- p_input_name VARCHAR2 --
1293 -- RETURN : VARCHAR2 --
1294 ---------------------------------------------------------------------------
1295 FUNCTION get_ee_value
1296 (p_element_entry_id IN NUMBER
1297 ,p_input_name IN VARCHAR2
1298 )
1299 RETURN VARCHAR2
1300 IS
1301 CURSOR c_entry_value
1302 IS
1303 SELECT val.screen_entry_value
1304 FROM pay_element_entry_values_f val
1305 ,pay_input_values_f inputs
1306 WHERE val.input_value_id = inputs.input_value_id
1307 AND val.element_entry_id = p_element_entry_id
1308 AND inputs.name = p_input_name
1309 AND inputs.legislation_code = 'IN';
1310 --
1311 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE := NULL;
1312 BEGIN
1313 set_location(g_debug, 'Entered '|| g_package||'.get_ee_value',1);
1314 OPEN c_entry_value;
1315 FETCH c_entry_value INTO l_screen_entry_value;
1316 CLOSE c_entry_value;
1317 set_location(g_debug, 'Leaving '|| g_package||'.get_ee_value',5);
1318 RETURN l_screen_entry_value;
1319
1320 END get_ee_value;
1321
1322
1323 --------------------------------------------------------------------------
1324 -- --
1325 -- Name : GET_ACTION_TYPE --
1326 -- Type : FUNCTION --
1327 -- Access : Public --
1328 -- Description : Function to return the action_type of an asact_id --
1329 -- --
1330 -- Parameters : --
1331 -- IN : p_assignment_action_id NUMBER --
1332 -- RETURN : VARCHAR2 --
1333 ---------------------------------------------------------------------------
1334 FUNCTION get_action_type
1335 (p_assignment_action_id IN NUMBER
1336 )
1337 RETURN VARCHAR2
1338 IS
1339 CURSOR c_act_type IS
1340 SELECT 'L'
1341 FROM pay_payroll_actions ppa
1342 ,pay_assignment_actions paa
1343 WHERE paa.payroll_action_id = ppa.payroll_action_id
1344 AND paa.assignment_action_id = p_assignment_action_id
1345 AND EXISTS (SELECT 1
1346 FROM pay_payroll_Actions ppa2
1347 WHERE ppa2.effective_date >= ppa.effective_date
1348 AND ppa2.action_type IN ('R','Q')
1349 AND ppa2.action_status = 'C') ;
1350
1351 l_act_type pay_payroll_actions.action_type%TYPE;
1352 BEGIN
1353
1354 OPEN c_act_type;
1355 FETCH c_act_type
1356 INTO l_act_type;
1357 IF c_act_type%NOTFOUND THEN
1358 l_act_type := 'K';
1359 END IF;
1360 CLOSE c_act_type;
1361 RETURN l_act_type;
1362
1363 END get_action_type;
1364
1365 --------------------------------------------------------------------------
1366 -- --
1367 -- Name : GET_TAX_UNIT_ID --
1368 -- Type : FUNCTION --
1369 -- Access : Public --
1370 -- Description : Function to return the tax unit id for an assignment--
1371 -- --
1372 -- Parameters : --
1373 -- IN : p_assignment_id NUMBER --
1374 -- p_effective_date DATE --
1375 -- RETURN : VARCHAR2 --
1376 ---------------------------------------------------------------------------
1377 FUNCTION get_tax_unit_id
1378 (p_assignment_id IN NUMBER
1379 ,p_effective_date DATE
1380 )
1381 RETURN VARCHAR2
1382 IS
1383 CURSOR c_gre_id
1384 IS
1385 SELECT scl.segment1
1386 FROM per_assignments_f asg,
1387 hr_soft_coding_keyflex scl
1388 WHERE asg.assignment_id = p_assignment_id
1389 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1390 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
1391
1392 l_tax_unit_id hr_soft_coding_keyflex.segment1%TYPE;
1393 BEGIN
1394 OPEN c_gre_id;
1395 FETCH c_gre_id INTO l_tax_unit_id;
1396 CLOSE c_gre_id;
1397
1398 RETURN l_tax_unit_id;
1399 END get_tax_unit_id;
1400
1401 --------------------------------------------------------------------------
1402 -- Name : GET_FORMULA_ID --
1403 -- Type : FUNCTION --
1404 -- Access : Private --
1405 -- Description : Function to fetch the formula_id --
1406 -- Parameters : --
1407 -- IN : p_effective_date DATE --
1408 -- : p_formula_name VARCHAR2 --
1409 -- OUT : N/A --
1410 -- RETURN : Number --
1411 --------------------------------------------------------------------------
1412 FUNCTION get_formula_id
1413 (p_effective_date IN DATE
1414 ,p_formula_name IN VARCHAR2
1415 )
1416 RETURN NUMBER
1417 IS
1418 l_formula_id NUMBER ;
1419 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_formula_id';
1420 l_message VARCHAR2(255);
1421 BEGIN
1422 g_debug := hr_utility.debug_enabled;
1423 set_location(g_debug, 'Entering: '||l_procedure,10);
1424
1425 SELECT formula_id
1426 INTO l_formula_id
1427 FROM ff_formulas_f
1428 WHERE legislation_code = 'IN'
1429 AND formula_name = p_formula_name
1430 AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
1431
1432 trace('Formula Id',l_formula_id);
1433
1434 set_location(g_debug, 'Leaving: '||l_procedure,20);
1435 RETURN l_formula_id;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 set_location(g_debug, 'Leaving: '||l_procedure,30);
1440 l_message := pay_in_utils.get_pay_message
1441 ('PER_IN_ORACLE_GENERIC_ERROR',
1442 'FUNCTION:'||l_procedure,
1443 'SQLERRMC:'||SQLERRM);
1444 trace('SQLERRM',l_message);
1445 RAISE ;
1446
1447 END get_formula_id;
1448
1449 --------------------------------------------------------------------------
1450 -- Name : GET_ELEMENT_TYPE_ID --
1451 -- Type : FUNCTION --
1452 -- Access : Public --
1453 -- Description : Function to fetch the element_type_id --
1454 -- Parameters : --
1455 -- IN : p_effective_date DATE --
1456 -- : p_element_name VARCHAR2 --
1457 -- OUT : N/A --
1458 -- RETURN : Number --
1459 --------------------------------------------------------------------------
1460 FUNCTION get_element_type_id
1461 (p_effective_date IN DATE
1462 ,p_element_name IN VARCHAR2
1463 )
1464 RETURN NUMBER
1465 IS
1466
1467 l_element_id NUMBER ;
1468 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_element_type_id';
1469 l_message VARCHAR2(255);
1470 BEGIN
1471 g_debug := hr_utility.debug_enabled;
1472 set_location(g_debug, 'Entering: '||l_procedure,10);
1473
1474 SELECT element_type_id
1475 INTO l_element_id
1476 FROM pay_element_types_f
1477 WHERE element_name = p_element_name
1478 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1479
1480 trace('Element Type Id',l_element_id);
1481
1482 set_location(g_debug, 'Leaving: '||l_procedure,20);
1483 RETURN l_element_id;
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 set_location(g_debug, 'Leaving: '||l_procedure,30);
1488 l_message := pay_in_utils.get_pay_message
1489 ('PER_IN_ORACLE_GENERIC_ERROR',
1490 'FUNCTION:'||l_procedure,
1491 'SQLERRMC:'||SQLERRM);
1492 trace('SQLERRM',l_message);
1493 RETURN TO_NUMBER(NULL);
1494 RAISE ;
1495
1496 END get_element_type_id;
1497
1498 --------------------------------------------------------------------------
1499 -- Name : GET_BALANCE_TYPE_ID --
1500 -- Type : FUNCTION --
1501 -- Access : Public --
1502 -- Description : Function to fetch the balance_type_id --
1503 -- Parameters : --
1504 -- IN : p_balance_name VARCHAR2 --
1505 -- OUT : N/A --
1506 -- RETURN : Number --
1507 --------------------------------------------------------------------------
1508 FUNCTION get_balance_type_id
1509 (p_balance_name IN VARCHAR2
1510 )
1511 RETURN NUMBER
1512 IS
1513 l_balance_id NUMBER ;
1514 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_balance_type_id';
1515 l_message VARCHAR2(255);
1516 BEGIN
1517 g_debug := hr_utility.debug_enabled;
1518 set_location(g_debug, 'Entering: '||l_procedure,10);
1519
1520 SELECT balance_type_id
1521 INTO l_balance_id
1522 FROM pay_balance_types
1523 WHERE legislation_code = 'IN'
1524 AND balance_name = p_balance_name;
1525
1526 trace('Balance Type Id',l_balance_id);
1527
1528 set_location(g_debug, 'Leaving: '||l_procedure,20);
1529 RETURN l_balance_id;
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 set_location(g_debug, 'Leaving: '||l_procedure,30);
1534 l_message := pay_in_utils.get_pay_message
1535 ('PER_IN_ORACLE_GENERIC_ERROR',
1536 'FUNCTION:'||l_procedure,
1537 'SQLERRMC:'||SQLERRM);
1538 trace('SQLERRM',l_message);
1539 RETURN TO_NUMBER(NULL);
1540 RAISE ;
1541
1542 END get_balance_type_id;
1543
1544 --------------------------------------------------------------------------
1545 -- Name : GET_INPUT_VALUE_ID --
1546 -- Type : FUNCTION --
1547 -- Access : Public --
1548 -- Description : Function to fetch the input_value_id --
1549 -- Parameters : --
1550 -- IN : p_effective_date DATE --
1551 -- : p_element_id NUMBER --
1552 -- : p_input_value VARCHAR2 --
1553 -- OUT : N/A --
1554 -- RETURN : Number --
1555 --------------------------------------------------------------------------
1556 FUNCTION get_input_value_id
1557 (p_effective_date IN DATE
1558 ,p_element_id IN NUMBER
1559 ,p_input_value IN VARCHAR2
1560 )
1561 RETURN NUMBER
1562 IS
1563 CURSOR c_input_value_id
1564 IS
1565 SELECT input_value_id
1566 FROM pay_input_values_f
1567 WHERE element_type_id = p_element_id
1568 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1569 AND NAME = p_input_value;
1570
1571 l_input_value_id NUMBER ;
1572 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_input_value_id';
1573 l_message VARCHAR2(255);
1574 BEGIN
1575 g_debug := hr_utility.debug_enabled;
1576 set_location(g_debug, 'Entering: '||l_procedure,10);
1577
1578 OPEN c_input_value_id;
1579 FETCH c_input_value_id INTO l_input_value_id;
1580 IF (c_input_value_id%NOTFOUND)
1581 THEN
1582 set_location(g_debug, 'l_input_value_id: NULL ',15);
1583 END IF;
1584 CLOSE c_input_value_id;
1585
1586 trace('Input Value Id',l_input_value_id);
1587
1588 set_location(g_debug, 'Leaving: '||l_procedure,20);
1589 RETURN l_input_value_id;
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 set_location(g_debug, 'Leaving: '||l_procedure,30);
1594 l_message := pay_in_utils.get_pay_message
1595 ('PER_IN_ORACLE_GENERIC_ERROR',
1596 'FUNCTION:'||l_procedure,
1597 'SQLERRMC:'||SQLERRM);
1598 trace('SQLERRM',l_message);
1599 RETURN TO_NUMBER(NULL);
1600 RAISE ;
1601
1602 END get_input_value_id;
1603
1604 --------------------------------------------------------------------------
1605 -- Name : GET_INPUT_VALUE_ID --
1606 -- Type : FUNCTION --
1607 -- Access : Public --
1608 -- Description : Function to fetch the input_value_id --
1609 -- Parameters : --
1610 -- IN : p_effective_date DATE --
1611 -- : p_element_id NUMBER --
1612 -- : p_input_value VARCHAR2 --
1613 -- OUT : N/A --
1614 -- RETURN : Number --
1615 --------------------------------------------------------------------------
1616 FUNCTION get_input_value_id
1617 (p_effective_date IN DATE
1618 ,p_element_name IN VARCHAR2
1619 ,p_input_value IN VARCHAR2
1620 )
1621 RETURN NUMBER
1622 IS
1623
1624 l_input_value_id NUMBER ;
1625 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_input_value_id';
1626 l_message VARCHAR2(255);
1627 BEGIN
1628 g_debug := hr_utility.debug_enabled;
1629 set_location(g_debug, 'Entering: '||l_procedure,10);
1630
1631 l_input_value_id := get_input_value_id
1632 (p_effective_date
1633 ,get_element_type_id(p_effective_date, p_element_name)
1634 ,p_input_value);
1635
1636 trace('Input Value Id',l_input_value_id);
1637
1638 set_location(g_debug, 'Leaving: '||l_procedure,20);
1639 RETURN l_input_value_id;
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 set_location(g_debug, 'Leaving: '||l_procedure,30);
1644 l_message := pay_in_utils.get_pay_message
1645 ('PER_IN_ORACLE_GENERIC_ERROR',
1646 'FUNCTION:'||l_procedure,
1647 'SQLERRMC:'||SQLERRM);
1648 trace('SQLERRM',l_message);
1649 RETURN TO_NUMBER(NULL);
1650 RAISE ;
1651
1652 END get_input_value_id;
1653
1654 --------------------------------------------------------------------------
1655 -- Name : GET_TEMPLATE_ID --
1656 -- Type : FUNCTION --
1657 -- Access : Private --
1658 -- Description : Procedure to fetch the template_id --
1659 -- Parameters : --
1660 -- IN : p_template_name VARCHAR2 --
1661 -- OUT : p_template_id NUMBER --
1662 -- RETURN : N/A --
1663 --------------------------------------------------------------------------
1664 FUNCTION get_template_id
1665 (p_template_name IN VARCHAR2
1666 )
1667 RETURN NUMBER
1668 IS
1669 l_template_id NUMBER;
1670 l_procedure VARCHAR2(100):= g_package||'get_template_id';
1671 l_message VARCHAR2(1000);
1672 BEGIN
1673 set_location(g_debug,'Entering: '||l_procedure,10);
1674
1675 SELECT template_id
1676 INTO l_template_id
1677 FROM pay_element_templates
1678 WHERE template_name = p_template_name
1679 AND legislation_code = 'IN';
1680
1681 trace('Template Id',l_template_id);
1682
1683 set_location(g_debug,'Leaving: '||l_procedure,20);
1684
1685 RETURN l_template_id;
1686
1687 EXCEPTION
1688 WHEN NO_DATA_FOUND THEN
1689 RETURN TO_NUMBER(NULL);
1690 WHEN OTHERS THEN
1691 set_location(g_debug, 'Leaving: '||l_procedure,30);
1692 l_message := pay_in_utils.get_pay_message
1693 ('PER_IN_ORACLE_GENERIC_ERROR',
1694 'FUNCTION:'||l_procedure,
1695 'SQLERRMC:'||SQLERRM);
1696 trace('SQLERRM',l_message);
1697 RAISE ;
1698
1699 END get_template_id;
1700
1701 --------------------------------------------------------------------------
1702 -- Name : INS_FORM_RES_RULE --
1703 -- Type : PROCEDURE --
1704 -- Access : Private --
1705 -- Description : Procedure to update element details in post-process --
1706 -- Parameters : --
1707 -- IN : p_business_group_id NUMBER --
1708 -- : p_effective_date DATE --
1709 -- : p_status_processing_rule_id NUMBER --
1710 -- : p_result_name VARCHAR2 --
1711 -- : p_result_rule_type VARCHAR2 --
1712 -- : p_element_name VARCHAR2 --
1713 -- : p_input_value_name VARCHAR2 --
1714 -- : p_severity_level VARCHAR2 --
1715 -- : p_element_type_id NUMBER --
1716 -- OUT : N/A --
1717 -- RETURN : N/A --
1718 --------------------------------------------------------------------------
1719 PROCEDURE ins_form_res_rule
1720 (
1721 p_business_group_id NUMBER,
1722 p_effective_date DATE ,
1723 p_status_processing_rule_id NUMBER,
1724 p_result_name VARCHAR2,
1725 p_result_rule_type VARCHAR2,
1726 p_element_name VARCHAR2 DEFAULT NULL,
1727 p_input_value_name VARCHAR2 DEFAULT NULL,
1728 p_severity_level VARCHAR2 DEFAULT NULL,
1729 p_element_type_id NUMBER DEFAULT NULL
1730 )
1731 IS
1732
1733 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
1734 v_form_res_rule_id NUMBER;
1735 l_input_value_id pay_formula_result_rules_f.input_value_id%TYPE;
1736 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1737 l_procedure CONSTANT VARCHAR2(100):= g_package||'ins_form_res_rule';
1738 l_message VARCHAR2(1000);
1739
1740 BEGIN
1741 set_location(g_debug,'Entering : '||l_procedure,10);
1742
1743 IF p_result_rule_type IN('D','U') THEN
1744
1745 set_location(g_debug,l_procedure,20);
1746
1747 l_input_value_id :=
1748 get_input_value_id(p_effective_date
1749 ,p_element_type_id
1750 ,p_input_value_name);
1751
1752 ELSIF p_result_rule_type IN ('I') THEN
1753
1754 set_location(g_debug,l_procedure,30);
1755 l_element_type_id :=
1756 get_element_type_id(p_effective_date
1757 ,p_element_name);
1758
1759 l_input_value_id :=
1760 get_input_value_id(p_effective_date
1761 ,l_element_type_id
1762 ,p_input_value_name);
1763
1764 END IF;
1765
1766 set_location(g_debug,l_procedure,40);
1767 SELECT pay_formula_result_rules_s.nextval
1768 INTO v_form_res_rule_id
1769 FROM sys.dual;
1770
1771 set_location(g_debug,l_procedure,50);
1772 INSERT INTO pay_formula_result_rules_f
1773 (formula_result_rule_id,
1774 effective_start_date,
1775 effective_end_date,
1776 business_group_id,
1777 status_processing_rule_id,
1778 result_name,
1779 result_rule_type,
1780 severity_level,
1781 input_value_id,
1782 last_update_date,
1783 last_updated_by,
1784 last_update_login,
1785 created_by,
1786 creation_date,
1787 element_type_id)
1788 VALUES
1789 (v_form_res_rule_id,
1790 p_effective_date,
1791 c_end_of_time,
1792 p_business_group_id,
1793 p_status_processing_rule_id,
1794 upper(p_result_name),
1795 p_result_rule_type,
1796 p_severity_level,
1797 l_input_value_id,
1798 trunc(sysdate),
1799 -1,
1800 -1,
1801 -1,
1802 trunc(sysdate),
1803 decode(p_result_rule_type,
1804 'D',p_element_type_id,
1805 'S',p_element_type_id,
1806 'I',l_element_type_id,
1807 'U',p_element_type_id,null));
1808
1809 set_location(g_debug, 'Leaving: '||l_procedure,60);
1810
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 set_location(g_debug, 'Leaving: '||l_procedure,70);
1814 l_message := get_pay_message
1815 ('PER_IN_ORACLE_GENERIC_ERROR',
1816 'FUNCTION:'||l_procedure,
1817 'SQLERRMC:'||SQLERRM);
1818 trace('SQLERRM',l_message);
1819 RAISE ;
1820
1821 END ins_form_res_rule;
1822
1823 --------------------------------------------------------------------------
1824 -- Name : DEL_FORM_RES_RULE --
1825 -- Type : PROCEDURE --
1826 -- Access : Private --
1827 -- Description : Procedure to delete formula setup for elements --
1828 -- Parameters : --
1829 -- IN : p_element_type_id_id NUMBER --
1830 -- : p_effective_date DATE --
1831 -- OUT : N/A --
1832 -- RETURN : N/A --
1833 --------------------------------------------------------------------------
1834 PROCEDURE del_form_res_rule
1835 (p_element_type_id IN NUMBER,
1836 p_effective_date IN DATE
1837 )
1838 IS
1839 CURSOR csr_sr IS
1840 SELECT rowid
1841 ,status_processing_rule_id
1842 ,effective_start_date
1843 FROM pay_status_processing_rules_f psr
1844 WHERE psr.element_type_id = p_element_type_id
1845 AND p_effective_date BETWEEN psr.effective_start_date
1846 AND psr.effective_end_date;
1847
1848 CURSOR csr_fr (p_status_rule_id IN NUMBER )
1849 IS
1850 SELECT ROWID
1851 ,formula_result_rule_id
1852 ,effective_start_date
1853 FROM pay_formula_result_rules_f
1854 WHERE status_processing_rule_id = p_status_rule_id
1855 AND p_effective_date BETWEEN effective_start_date
1856 AND effective_end_date;
1857
1858 l_procedure CONSTANT VARCHAR2(100):= g_package||'del_form_res_rule';
1859 l_message VARCHAR2(1000);
1860
1861 BEGIN
1862
1863 set_location(g_debug,'Entering : '||l_procedure,10);
1864 FOR j IN csr_sr
1865 LOOP
1866 set_location(g_debug,l_procedure,20);
1867
1868 IF g_debug THEN
1869 trace('Status Rule Id ',j.status_processing_rule_id);
1870 trace('Effective Date ',to_char(j.effective_start_date,'DD-Mon-YYYY'));
1871 END IF ;
1872
1873 FOR k IN csr_fr(j.status_processing_rule_id)
1874 LOOP
1875
1876 set_location(g_debug,l_procedure,30);
1877
1878 IF g_debug THEN
1879 trace('Result Rule Id ',k.formula_result_rule_id);
1880 trace('Effective Date ',to_char(k.effective_start_date,'DD-Mon-YYYY'));
1881 END IF ;
1882
1883 pay_formula_result_rules_pkg.delete_row(k.rowid);
1884
1885 set_location(g_debug,l_procedure,40);
1886
1887 END LOOP ; -- csr_fr ends
1888
1889 set_location(g_debug,l_procedure,50);
1890
1891 pay_status_rules_pkg.delete_row
1892 ( x_rowid => j.rowid
1893 , p_session_date => j.effective_start_date
1894 , p_delete_mode => hr_api.g_zap
1895 , p_status_processing_rule_id => j.status_processing_rule_id
1896 );
1897
1898 set_location(g_debug,l_procedure,60);
1899
1900 END LOOP; -- csr_sr ends
1901 set_location(g_debug,'Leaving : '||l_procedure,70);
1902
1903 END del_form_res_rule;
1904
1905 --------------------------------------------------------------------------
1906 -- Name : DELETE_BALANCE_FEEDS --
1907 -- Type : Procedure --
1908 -- Access : Public --
1909 -- Description : Procedure to fetch the template_id --
1910 -- Parameters : --
1911 -- IN : p_template_name VARCHAR2 --
1912 -- OUT : p_template_id NUMBER --
1913 -- RETURN : N/A --
1914 --------------------------------------------------------------------------
1915 PROCEDURE delete_balance_feeds
1916 (p_balance_name IN VARCHAR2
1917 ,p_element_name IN VARCHAR2
1918 ,p_input_value_name IN VARCHAR2
1919 ,p_effective_date IN DATE
1920 )
1921 IS
1922
1923 CURSOR csr_bf IS
1924 SELECT balance_feed_id, object_version_number
1925 FROM pay_balance_feeds_f
1926 WHERE balance_type_id = get_balance_type_id (p_balance_name)
1927 AND input_value_id = get_input_value_id (p_effective_date, p_element_name, p_input_value_name)
1928 AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
1929
1930 l_bf_id NUMBER ;
1931 l_ovn NUMBER ;
1932 l_start DATE ;
1933 l_end DATE ;
1934 l_warn BOOLEAN ;
1935 l_procedure CONSTANT VARCHAR2(100):= g_package||'delete_balance_feeds';
1936 l_message VARCHAR2(1000);
1937
1938 BEGIN
1939
1940 set_location(g_debug,'Entering : '||l_procedure,10);
1941
1942 OPEN csr_bf;
1943 FETCH csr_bf INTO l_bf_id, l_ovn;
1944 IF csr_bf%NOTFOUND THEN
1945 set_location(g_debug,'Leaving : '||l_procedure,15);
1946 RETURN ;
1947 END IF ;
1948 CLOSE csr_bf;
1949 set_location(g_debug,l_procedure,20);
1950
1951 pay_balance_feeds_api.delete_balance_feed
1952 (
1953 p_effective_date => p_effective_date
1954 ,p_datetrack_delete_mode => hr_api.g_delete
1955 ,p_balance_feed_id => l_bf_id
1956 ,p_object_version_number => l_ovn
1957 ,p_effective_start_date => l_start
1958 ,p_effective_end_date => l_end
1959 ,p_exist_run_result_warning => l_warn
1960 );
1961
1962 set_location(g_debug,'Leaving : '||l_procedure,30);
1963
1964 END delete_balance_feeds;
1965
1966 --------------------------------------------------------------------------
1967 -- Name : GET_PERSON_NAME --
1968 -- Type : FUNCTION --
1969 -- Access : Private --
1970 -- Description : Function to fetch the person name based on person id--
1971 -- Parameters : --
1972 -- IN : p_person_id IN NUMBER --
1973 -- : p_effective_date IN DATE --
1974 -- RETURN : VARCHAR2 --
1975 --------------------------------------------------------------------------
1976 FUNCTION get_person_name
1977 (p_person_id IN NUMBER
1978 ,p_effective_date IN DATE
1979 )
1980 RETURN VARCHAR2
1981 IS
1982 CURSOR c_person_name
1983 IS
1984 SELECT full_name
1985 FROM per_people_f
1986 WHERE person_id = p_person_id
1987 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1988
1989 l_full_name per_all_people_f.full_name%TYPE;
1990 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_person_name';
1991
1992 BEGIN
1993 set_location(g_debug,'Entering : '||l_procedure,10);
1994
1995 OPEN c_person_name;
1996 FETCH c_person_name INTO l_full_name;
1997 CLOSE c_person_name;
1998
1999 set_location(g_debug,l_procedure,20);
2000
2001 IF g_debug THEN
2002 trace('Full Name is ',l_full_name);
2003 END IF ;
2004 set_location(g_debug,'Leaving : '||l_procedure,30);
2005
2006 RETURN l_full_name;
2007 END get_person_name;
2008
2009 --------------------------------------------------------------------------
2010 -- Name : GET_ORGANIZATION_NAME --
2011 -- Type : FUNCTION --
2012 -- Access : Private --
2013 -- Description : Function to fetch the organization name --
2014 -- Parameters : --
2015 -- IN : p_organization_id IN NUMBER --
2016 -- RETURN : VARCHAR2 --
2017 --------------------------------------------------------------------------
2018 FUNCTION get_organization_name
2019 (p_organization_id IN NUMBER
2020 )
2021 RETURN VARCHAR2
2022 IS
2023 CURSOR c_organization_name
2024 IS
2025 SELECT name
2026 FROM hr_organization_units
2027 WHERE organization_id = p_organization_id;
2028
2029 l_org_name hr_organization_units.name%TYPE;
2030 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_organization_name';
2031
2032 BEGIN
2033 set_location(g_debug,'Entering : '||l_procedure,10);
2034
2035 OPEN c_organization_name;
2036 FETCH c_organization_name INTO l_org_name;
2037 CLOSE c_organization_name;
2038
2039 set_location(g_debug,l_procedure,20);
2040
2041 IF g_debug THEN
2042 trace('Full Name is ',l_org_name);
2043 END IF ;
2044 set_location(g_debug,'Leaving : '||l_procedure,30);
2045
2046 RETURN l_org_name;
2047 END get_organization_name;
2048
2049 --------------------------------------------------------------------------
2050 -- Name : GET_PAYMENT_NAME --
2051 -- Type : FUNCTION --
2052 -- Access : Private --
2053 -- Description : Function to fetch the payment method name --
2054 -- Parameters : --
2055 -- IN : P_PAYMENT_TYPE_ID IN NUMBER --
2056 -- RETURN : VARCHAR2 --
2057 --------------------------------------------------------------------------
2058 FUNCTION get_payment_name
2059 (p_payment_type_id IN NUMBER
2060 )
2061 RETURN VARCHAR2
2062 IS
2063 CURSOR c_payment_method_name
2064 IS
2065 SELECT pptl.payment_type_name
2066 FROM pay_payment_types ppt
2067 ,pay_payment_types_tl pptl
2068 WHERE ppt.payment_type_id = pptl.payment_type_id
2069 AND ppt.territory_code = 'IN'
2070 AND ppt.category <> 'MT'
2071 AND pptl.language = USERENV('LANG')
2072 AND ppt.payment_type_id = p_payment_type_id;
2073
2074 l_payment_mthd_name pay_payment_types_tl.payment_type_name%TYPE;
2075 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_payment_name';
2076 BEGIN
2077 set_location(g_debug,'Entering : '||l_procedure,10);
2078
2079 OPEN c_payment_method_name;
2080 FETCH c_payment_method_name INTO l_payment_mthd_name;
2081 CLOSE c_payment_method_name;
2082
2083 set_location(g_debug,l_procedure,20);
2084
2085 IF g_debug THEN
2086 trace('Payment Method Name is ',l_payment_mthd_name);
2087 END IF ;
2088 set_location(g_debug,'Leaving : '||l_procedure,30);
2089
2090 RETURN l_payment_mthd_name;
2091
2092 END get_payment_name;
2093
2094 --------------------------------------------------------------------------
2095 -- Name : GET_BANK_NAME --
2096 -- Type : FUNCTION --
2097 -- Access : Private --
2098 -- Description : Function to fetch the bank name --
2099 -- Parameters : --
2100 -- IN : p_org_information_id IN NUMBER --
2101 -- RETURN : VARCHAR2 --
2102 --------------------------------------------------------------------------
2103 FUNCTION get_bank_name
2104 (p_org_information_id IN NUMBER
2105 )
2106 RETURN VARCHAR2
2107 IS
2108 CURSOR c_bank_name
2109 IS
2110 SELECT hr_general.decode_lookup('IN_BANK',org_information1)
2111 FROM hr_organization_information
2112 WHERE org_information_context = 'PER_IN_CHALLAN_BANK'
2113 AND org_information_id = p_org_information_id;
2114
2115 l_bank_name VARCHAR2(300);
2116 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_bank_name';
2117 BEGIN
2118 set_location(g_debug,'Entering : '||l_procedure,10);
2119
2120 OPEN c_bank_name;
2121 FETCH c_bank_name INTO l_bank_name;
2122 CLOSE c_bank_name;
2123
2124 set_location(g_debug,l_procedure,20);
2125
2126 IF g_debug THEN
2127 trace('Bank Name is ',l_bank_name);
2128 END IF ;
2129 set_location(g_debug,'Leaving : '||l_procedure,30);
2130
2131 RETURN l_bank_name;
2132
2133 END get_bank_name;
2134
2135 --------------------------------------------------------------------------
2136 -- Name : GET_ADDR_DFF_DETAILS --
2137 -- Type : FUNCTION --
2138 -- Access : Private --
2139 -- Description : Function to fetch the segments of 'Personal Address --
2140 -- : ' Information' DFF for IN localization. --
2141 -- Parameters : --
2142 -- IN : p_address_id IN NUMBER --
2143 -- : p_segment_no IN VARCHAR2 --
2144 -- RETURN : VARCHAR2 --
2145 --------------------------------------------------------------------------
2146 FUNCTION get_addr_dff_details
2147 (p_address_id IN NUMBER
2148 ,p_segment_no IN VARCHAR2
2149 )
2150 RETURN VARCHAR2
2151 IS
2152 CURSOR c_personal_addr_dff_details
2153 IS
2154 SELECT DECODE(p_segment_no,'1',add_information13
2155 ,'2',add_information14
2156 ,'3',hr_general.decode_lookup('IN_STATES',add_information15)
2157 ,hr_general.decode_lookup('YES_NO',add_information16)
2158 )
2159 FROM per_addresses
2160 WHERE address_id = p_address_id
2161 AND style = 'IN';
2162
2163 l_seg_value VARCHAR2(300);
2164 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_addr_dff_details';
2165 BEGIN
2166 set_location(g_debug,'Entering : '||l_procedure,10);
2167
2168 OPEN c_personal_addr_dff_details;
2169 FETCH c_personal_addr_dff_details INTO l_seg_value;
2170 CLOSE c_personal_addr_dff_details;
2171
2172 set_location(g_debug,l_procedure,20);
2173
2174 IF g_debug THEN
2175 trace('Segment Value is ',l_seg_value);
2176 END IF ;
2177 set_location(g_debug,'Leaving : '||l_procedure,30);
2178
2179 RETURN l_seg_value;
2180
2181 END get_addr_dff_details;
2182
2183 --------------------------------------------------------------------------
2184 -- Name : GET_ARCHIVE_REF_NUM --
2185 -- Type : FUNCTION --
2186 -- Access : Private --
2187 -- Description : Function to fetch the form 24Q or 24QC ref number. --
2188 -- Parameters : --
2189 -- IN : p_year IN VARCHAR2 --
2190 -- : p_quarter IN VARCHAR2 --
2191 -- : p_return_type IN VARCHAR2 --
2192 -- : p_organization_id IN NUMBER --
2193 -- : p_action_context_id IN NUMBER --
2194 -- RETURN : VARCHAR2 --
2195 --------------------------------------------------------------------------
2196 FUNCTION get_archive_ref_num
2197 (p_year IN VARCHAR2,
2198 p_quarter IN VARCHAR2,
2199 p_return_type IN VARCHAR2,
2200 p_organization_id IN NUMBER,
2201 p_action_context_id IN NUMBER
2202 )
2203 RETURN VARCHAR2
2204 IS
2205 CURSOR c_archive_ref_number
2206 IS
2207 SELECT action_information30
2208 FROM pay_action_information
2209 WHERE action_information3 = p_year || p_quarter
2210 AND action_context_type = 'PA'
2211 AND action_information_category = DECODE(p_return_type,'O','IN_24Q_ORG','IN_24QC_ORG')
2212 AND action_information30 IS NOT NULL
2213 AND action_information1 = p_organization_id
2214 AND action_context_id = p_action_context_id
2215 ORDER BY action_information30 DESC;
2216
2217 l_archive_ref_number VARCHAR2(300);
2218 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_archive_ref_num';
2219 BEGIN
2220 set_location(g_debug,'Entering : '||l_procedure,10);
2221
2222 OPEN c_archive_ref_number;
2223 FETCH c_archive_ref_number INTO l_archive_ref_number;
2224 CLOSE c_archive_ref_number;
2225
2226 set_location(g_debug,l_procedure,20);
2227
2228 IF g_debug THEN
2229 trace('Archive Reference Number is ',l_archive_ref_number);
2230 END IF ;
2231 set_location(g_debug,'Leaving : '||l_procedure,30);
2232
2233 RETURN l_archive_ref_number;
2234
2235 END get_archive_ref_num;
2236
2237 --------------------------------------------------------------------------
2238 -- Name : get_processing_type --
2239 -- Type : Function --
2240 -- Access : Public --
2241 -- Description : Function to get processing type --
2242 -- (Non-recurring/Recurring) of an element --
2243 -- Parameters : --
2244 -- IN : p_element_type_id NUMBER --
2245 -- p_business_group_id NUMBER --
2246 -- p_earned_date DATE --
2247 --------------------------------------------------------------------------
2248 FUNCTION get_processing_type
2249 (p_element_type_id IN NUMBER
2250 ,p_business_group_id IN NUMBER
2251 ,p_earned_date IN DATE
2252 )
2253 RETURN VARCHAR IS
2254
2255 CURSOR c_processing_type IS
2256 SELECT processing_type
2257 FROM pay_element_types_f
2258 WHERE element_type_id = p_element_type_id
2259 AND business_group_id = p_business_group_id
2260 AND p_earned_date BETWEEN effective_start_date AND effective_end_date;
2261
2262 l_processing_type VARCHAR2(5);
2263 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_processing_type';
2264
2265 BEGIN
2266
2267 set_location(g_debug,'Entering : '||l_procedure,10);
2268
2269 IF (g_debug)THEN
2270 trace('**************************************************','********************');
2271 set_location(g_debug,'Input Paramters value is',20);
2272 trace('p_element_type_id',p_element_type_id);
2273 trace('p_business_group_id',p_business_group_id);
2274 trace('p_earned_date',p_earned_date);
2275 trace('**************************************************','********************');
2276 END IF;
2277
2278 OPEN c_processing_type;
2279 FETCH c_processing_type INTO l_processing_type;
2280 CLOSE c_processing_type;
2281
2282 IF (g_debug)THEN
2283 trace('**************************************************','********************');
2284 trace('l_processing_type is ',l_processing_type);
2285 trace('**************************************************','********************');
2286 END IF;
2287
2288 set_location(g_debug,'Leaving : '||l_procedure,30);
2289 RETURN l_processing_type;
2290
2291 END get_processing_type;
2292
2293 --------------------------------------------------------------------------
2294 -- Name : chk_business_group --
2295 -- Type : FUNCTION --
2296 -- Access : Public --
2297 -- Description : Function to check the business group as per the --
2298 -- : profile value. --
2299 -- Parameters : --
2300 -- IN : p_assignment_id IN NUMBER --
2301 -- RETURN : VARCHAR2 --
2302 --------------------------------------------------------------------------
2303 FUNCTION chk_business_group
2304 (p_assignment_id IN NUMBER
2305 )
2306 RETURN NUMBER
2307 IS
2308 CURSOR c_asg_bg_id
2309 IS
2310 SELECT 1
2311 FROM per_assignments_f
2312 WHERE assignment_id = p_assignment_id
2313 AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2314 ORDER BY effective_end_date DESC;
2315
2316 l_bg_flag NUMBER := -1;
2317 l_procedure CONSTANT VARCHAR2(100):= g_package ||'chk_business_group';
2318 BEGIN
2319 set_location(g_debug,'Entering : '||l_procedure,10);
2320
2321 OPEN c_asg_bg_id;
2322 FETCH c_asg_bg_id INTO l_bg_flag;
2323 CLOSE c_asg_bg_id;
2324
2325 set_location(g_debug,l_procedure,20);
2326
2327 IF g_debug THEN
2328 trace('l_bg_flag is ',l_bg_flag);
2329 END IF ;
2330
2331 IF (l_bg_flag = -1)
2332 THEN
2333 set_location(g_debug,'Leaving : '||l_procedure,35);
2334 RETURN -1;
2335 ELSE
2336 set_location(g_debug,'Leaving : '||l_procedure,40);
2337 RETURN p_assignment_id;
2338 END IF;
2339 END chk_business_group;
2340
2341 --------------------------------------------------------------------------
2342 -- Name : GET_SECONDARY_CLASSIFICATION --
2343 -- Type : Function --
2344 -- Access : Public --
2345 -- Description : Function to fetch the secondary classification --
2346 -- Parameters : --
2347 -- IN : p_element_type_id NUMBER --
2348 -- OUT : p_date_earned DATE --
2349 -- RETURN : NUMBER --
2350 --------------------------------------------------------------------------
2351 FUNCTION get_secondary_classification
2352 (p_element_type_id NUMBER
2353 ,p_date_earned DATE
2354 )
2355 RETURN NUMBER
2356 IS
2357 CURSOR c_element_sec_classification
2358 IS
2359 SELECT DECODE(pec.classification_name
2360 ,'Monetary Perquisite',1
2361 ,'Non Monetary Perquisite',2
2362 ,-1
2363 )
2364 FROM pay_sub_classification_rules_f pscr
2365 ,pay_element_classifications pec
2366 WHERE pscr.classification_id = pec.classification_id
2367 AND pec.parent_classification_id =
2368 (SELECT classification_id FROM pay_element_classifications
2369 WHERE classification_name = 'Perquisites'
2370 AND legislation_code = 'IN'
2371 )
2372 AND element_type_id = p_element_type_id
2373 AND p_date_earned BETWEEN pscr.effective_start_date
2374 AND pscr.effective_end_date;
2375
2376 l_sec_classification NUMBER;
2377 BEGIN
2378 OPEN c_element_sec_classification;
2379 FETCH c_element_sec_classification INTO l_sec_classification;
2380 CLOSE c_element_sec_classification;
2381
2382 RETURN l_sec_classification;
2383 END get_secondary_classification;
2384
2385 --------------------------------------------------------------------------
2386 -- Name : GET_CONFIGURATION_INFO --
2387 -- Type : Procedure --
2388 -- Access : Public --
2389 -- Description : Procedure to fetch the configuartion information --
2390 -- Parameters : --
2391 -- IN : p_element_type_id NUMBER --
2392 -- OUT : p_date_earned DATE --
2393 -- RETURN : NUMBER --
2394 --------------------------------------------------------------------------
2395 FUNCTION get_configuration_info
2396 (p_element_type_id NUMBER
2397 ,p_date_earned DATE
2398 )
2399 RETURN VARCHAR2
2400 IS
2401 CURSOR c_config_info
2402 IS
2403 SELECT pet.configuration_information2
2404 FROM pay_element_types_f pee
2405 ,pay_element_templates pet
2406 ,pay_shadow_element_types pset
2407 WHERE pee.element_type_id = p_element_type_id
2408 AND pee.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2409 AND pee.element_name = pset.element_name
2410 AND pset.template_id = pet.template_id
2411 AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date;
2412
2413 l_config_info pay_element_templates.configuration_information2%TYPE;
2414
2415 BEGIN
2416 OPEN c_config_info;
2417 FETCH c_config_info INTO l_config_info;
2418 CLOSE c_config_info;
2419 RETURN l_config_info;
2420 END get_configuration_info;
2421 --------------------------------------------------------------------------
2422 -- Name : GET_ELEMENT_ENTRY_END_DATE --
2423 -- Type : Procedure --
2424 -- Access : Public --
2425 -- Description : Procedure to fetch the element entry date --
2426 -- Parameters : --
2427 -- IN : p_element_entry_id NUMBER --
2428 -- RETURN : DATE --
2429 --------------------------------------------------------------------------
2430 FUNCTION get_element_entry_end_date
2431 (p_element_entry_id NUMBER
2432 )
2433 RETURN DATE
2434 IS
2435 CURSOR c_get_ee_end_date
2436 IS
2437 SELECT effective_end_date
2438 FROM pay_element_entries_f
2439 WHERE element_entry_id = p_element_entry_id;
2440
2441 l_ee_end_date pay_element_entries_f.effective_end_date%TYPE;
2442 BEGIN
2443 OPEN c_get_ee_end_date;
2444 FETCH c_get_ee_end_date INTO l_ee_end_date;
2445 CLOSE c_get_ee_end_date;
2446
2447 RETURN l_ee_end_date;
2448 END get_element_entry_end_date;
2449
2450 --------------------------------------------------------------------------
2451 -- Name : GET_CONTACT_RELATIONSHIP --
2452 -- Type : fUNCTION --
2453 -- Access : Public --
2454 -- Description : Gets the relationship between 2 person --
2455 -- Parameters : --
2456 -- IN : p_asg_id NUMBER --
2457 -- : p_contact_person_id NUMBER --
2458 -- RETURN : VARCHAR2 --
2459
2460 --------------------------------------------------------------------------
2461 FUNCTION get_contact_relationship
2462 (p_asg_id NUMBER
2463 ,p_contact_person_id NUMBER
2464 )
2465 RETURN VARCHAR2
2466 IS
2467 CURSOR c_relationship
2468 IS
2469 SELECT hr_general.decode_lookup('CONTACT',RELATION.CONTACT_TYPE)
2470 FROM per_contact_relationships relation,
2471 per_all_people_f ppf,
2472 per_all_assignments_f asg
2473 WHERE relation.contact_person_id = p_contact_person_id
2474 AND relation.person_id = ppf.person_id
2475 AND asg.person_id = ppf.person_id
2476 AND asg.assignment_id = p_asg_id
2477 AND SYSDATE >= relation.date_start
2478 AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date
2479 AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
2480
2481
2482 l_relation hr_lookups.meaning%TYPE;
2483 l_emp_person_id NUMBER;
2484
2485
2486 BEGIN
2487 l_emp_person_id := get_person_id(p_asg_id,SYSDATE);
2488 IF (l_emp_person_id = p_contact_person_id) THEN
2489 l_relation := 'Self';
2490 ELSE
2491 OPEN c_relationship;
2492 FETCH c_relationship INTO l_relation;
2493 CLOSE c_relationship;
2494 END IF;
2495 RETURN l_relation;
2496 END get_contact_relationship;
2497
2498 --------------------------------------------------------------------------
2499 -- Name : GET_HIRE_DATE --
2500 -- Type : FUNCTION --
2501 -- Access : Public --
2502 -- Description : Gets the hiredate of a assignment --
2503 -- Parameters : --
2504 -- IN : p_assignment_id NUMBER --
2505 -- RETURN : DATE --
2506
2507 --------------------------------------------------------------------------
2508 FUNCTION get_hire_date(p_assignment_id NUMBER)
2509 RETURN DATE
2510 IS
2511 CURSOR csr_hire_date
2512 IS
2513 SELECT MAX(pos.date_start)
2514 FROM per_periods_of_service pos
2515 ,per_people_f ppf
2516 ,per_assignments_f paf
2517 WHERE pos.person_id = ppf.person_id
2518 AND ppf.person_id = paf.person_id
2519 AND pos.date_start between paf.effective_start_date and paf.effective_end_date
2520 AND paf.assignment_id = p_assignment_id;
2521
2522 l_hire_date DATE;
2523
2524 BEGIN
2525 OPEN csr_hire_date;
2526 FETCH csr_hire_date INTO l_hire_date;
2527 CLOSE csr_hire_date;
2528
2529 RETURN l_hire_date;
2530 END get_hire_date;
2531
2532 --------------------------------------------------------------------------
2533 -- Name : GET_POSITION_NAME --
2534 -- Type : FUNCTION --
2535 -- Access : Public --
2536 -- Description : Gets the position of a assignment --
2537 -- Parameters : --
2538 -- IN : p_assignment_id NUMBER --
2539 -- IN : p_effective_date DATE --
2540 -- RETURN : VARCHAR2 --
2541
2542 --------------------------------------------------------------------------
2543 FUNCTION get_position_name
2544 (p_assignment_id NUMBER
2545 ,p_effective_date DATE
2546 )
2547 RETURN VARCHAR2
2548 IS
2549 CURSOR csr_positions
2550 IS
2551 SELECT pos.name
2552 FROM per_positions pos,
2553 per_assignments_f asg
2554 WHERE pos.position_id = asg.position_id
2555 AND asg.assignment_id = p_assignment_id
2556 AND p_effective_date BETWEEN asg.effective_start_date
2557 AND asg.effective_end_date;
2558
2559 l_position per_positions.name%TYPE;
2560
2561 BEGIN
2562 OPEN csr_positions;
2563 FETCH csr_positions INTO l_position;
2564 CLOSE csr_positions;
2565
2566 RETURN l_position;
2567
2568 END get_position_name;
2569
2570 --------------------------------------------------------------------------
2571 -- Name : GET_AGE --
2572 -- Type : fUNCTION --
2573 -- Access : Public --
2574 -- Description : Gets the age of a person as on a date --
2575 -- Parameters : --
2576 -- IN : p_person_id NUMBER --
2577 -- : p_effective_date DATE --
2578 -- RETURN : NUMBER --
2579
2580 --------------------------------------------------------------------------
2581
2582 FUNCTION get_age(p_person_id in number
2583 ,p_effective_date in date)
2584 RETURN NUMBER
2585 IS
2586
2587 Cursor c_dob is
2588 select ppf.date_of_birth
2589 from per_people_f ppf
2590 where ppf.person_id = p_person_id
2591 and p_effective_date BETWEEN ppf.effective_start_date
2592 AND ppf.effective_end_date;
2593
2594 l_dob date;
2595 l_age number;
2596
2597 l_procedure VARCHAR2(250);
2598 l_message VARCHAR2(250);
2599 BEGIN
2600 g_debug := hr_utility.debug_enabled;
2601 l_procedure := g_package ||'get_age';
2602 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2603 IF (g_debug)
2604 THEN
2605 pay_in_utils.trace('**************************************************','********************');
2606 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2607 pay_in_utils.trace('p_person_id',p_person_id);
2608 pay_in_utils.trace('p_effective_date',p_effective_date);
2609 END IF;
2610
2611
2612 Open c_dob;
2613 Fetch c_dob into l_dob;
2614 Close c_dob;
2615
2616
2617 l_age := trunc((p_effective_date - l_dob)/365);
2618
2619 IF (g_debug)
2620 THEN
2621 pay_in_utils.trace('l_age',l_age);
2622 END IF;
2623
2624 pay_in_utils.trace('**************************************************','********************');
2625 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2626
2627 Return l_age;
2628
2629 END get_age;
2630
2631 --------------------------------------------------------------------------
2632 -- Name : GET_LTC_BLOCK --
2633 -- Type : fUNCTION --
2634 -- Access : Public --
2635 -- Description : Gets the current LTC Block --
2636 -- Parameters : --
2637 -- IN : p_effective_date DATE --
2638 -- RETURN : VARCHAR2 --
2639
2640 --------------------------------------------------------------------------
2641
2642 FUNCTION get_ltc_block
2643 (p_effective_date DATE
2644 )
2645 RETURN VARCHAR2
2646 IS
2647 CURSOR csr_ltc_block
2648 IS
2649 SELECT lookup_code
2650 FROM hr_lookups hrl
2651 WHERE HRL.lookup_type = 'IN_LTC_BLOCK'
2652 AND p_effective_date BETWEEN TO_DATE(SUBSTR(HRL.MEANING,1,11),'DD-MM-YYYY')
2653 AND TO_DATE(SUBSTR(HRL.MEANING,15,11),'DD-MM-YYYY');
2654
2655 l_ltc_block hr_lookups.lookup_code%TYPE;
2656
2657 BEGIN
2658 OPEN csr_ltc_block;
2659 FETCH csr_ltc_block INTO l_ltc_block;
2660 CLOSE csr_ltc_block;
2661 RETURN l_ltc_block;
2662
2663 END get_ltc_block;
2664
2665 --------------------------------------------------------------------------
2666 -- Name : GET_PREV_LTC_BLOCK --
2667 -- Type : fUNCTION --
2668 -- Access : Public --
2669 -- Description : Gets the previous LTC Block --
2670 -- Parameters : --
2671 -- IN : p_effective_date DATE --
2672 -- RETURN : VARCHAR2 --
2673
2674 --------------------------------------------------------------------------
2675
2676 FUNCTION get_prev_ltc_block
2677 (p_effective_date DATE
2678 )
2679 RETURN VARCHAR2
2680 IS
2681 CURSOR csr_ltc_block(l_effective_date DATE)
2682 IS
2683 SELECT lookup_code
2684 FROM hr_lookups hrl
2685 WHERE HRL.lookup_type = 'IN_LTC_BLOCK'
2686 AND l_effective_date BETWEEN TO_DATE(SUBSTR(HRL.MEANING,1,11),'DD-MM-YYYY')
2687 AND TO_DATE(SUBSTR(HRL.MEANING,15,11),'DD-MM-YYYY');
2688
2689 l_ltc_block hr_lookups.lookup_code%TYPE;
2690 l_effective_date DATE;
2691
2692 BEGIN
2693 l_effective_date := add_months(p_effective_date,-48);
2694
2695 OPEN csr_ltc_block(l_effective_date);
2696 FETCH csr_ltc_block INTO l_ltc_block;
2697 CLOSE csr_ltc_block;
2698 RETURN l_ltc_block;
2699
2700 END get_prev_ltc_block;
2701
2702 BEGIN
2703 g_package := 'pay_in_utils.';
2704
2705 END pay_in_utils;