[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_UTILS
Source
1 PACKAGE BODY pay_in_utils AS
2 /* $Header: pyinutil.pkb 120.24.12020000.2 2013/04/02 11:07:17 pthummal ship $ */
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 ,p_payroll_id NUMBER
1017 ,p_organization_id NUMBER
1018 ,p_position_id NUMBER
1019 ,p_job_id NUMBER
1020 ,p_grade_id NUMBER
1021 ,p_location_id NUMBER
1022 ,p_pay_basis_id NUMBER
1023 ,p_employment_category VARCHAR2
1024 )
1025 IS
1026 SELECT element_link_id,
1027 payroll_id,
1028 link_to_all_payrolls_flag,
1029 organization_id,
1030 position_id,
1031 job_id,
1032 grade_id,
1033 location_id,
1034 pay_basis_id,
1035 employment_category,
1036 people_group_id
1037 FROM pay_element_links_f
1038 WHERE element_type_id = p_element_type_id
1039 AND business_group_id = p_bg_id
1040 AND (payroll_id = p_payroll_id OR payroll_id is null)
1041 AND (organization_id = p_organization_id OR organization_id is null)
1042 AND (position_id = p_position_id OR position_id is null)
1043 AND (job_id = p_job_id OR job_id is null)
1044 AND (grade_id = p_grade_id OR grade_id is null)
1045 AND (location_id = p_location_id OR location_id is null)
1046 AND (pay_basis_id = p_pay_basis_id OR pay_basis_id is null)
1047 AND (employment_category = p_employment_category OR employment_category is null)
1048 AND link_to_all_payrolls_flag in ('N','Y')
1049 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1050
1051 CURSOR c_assignment_details(p_assignment_id NUMBER
1052 ,p_effective_date DATE
1053 )
1054 IS
1055 SELECT payroll_id,
1056 organization_id,
1057 position_id,
1058 job_id,
1059 grade_id,
1060 location_id,
1061 pay_basis_id,
1062 employment_category
1063 FROM per_assignments_f
1064 WHERE assignment_id = p_assignment_id
1065 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1066
1067
1068 CURSOR c_link_usage(p_assignment_id NUMBER
1069 ,p_element_link_id NUMBER
1070 ,p_effective_date DATE
1071 )
1072 IS
1073 SELECT 1
1074 FROM pay_assignment_link_usages_f
1075 WHERE assignment_id = p_assignment_id
1076 AND element_link_id = p_element_link_id
1077 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1078
1079
1080 l_element_link_id pay_element_links_f.element_link_id%TYPE;
1081 l_link_payroll_id pay_element_links_f.payroll_id%TYPE;
1082 l_link_to_all_payrolls_flag pay_element_links_f.link_to_all_payrolls_flag%TYPE;
1083 l_link_org_id pay_element_links_f.organization_id%TYPE;
1084 l_link_pos_id pay_element_links_f.position_id%TYPE;
1085 l_link_job_id pay_element_links_f.job_id%TYPE;
1086 l_link_grd_id pay_element_links_f.grade_id%TYPE;
1087 l_link_loc_id pay_element_links_f.location_id%TYPE;
1088 l_link_pay_basis_id pay_element_links_f.pay_basis_id%TYPE;
1089 l_link_emp_catg_id pay_element_links_f.employment_category%TYPE;
1090 l_link_pep_grp_id pay_element_links_f.people_group_id%TYPE;
1091
1092 l_asg_payroll_id per_all_assignments_f.payroll_id%TYPE;
1093 l_asg_org_id per_all_assignments_f.organization_id%TYPE;
1094 l_asg_pos_id per_all_assignments_f.position_id%TYPE;
1095 l_asg_job_id per_all_assignments_f.job_id%TYPE;
1096 l_asg_grd_id per_all_assignments_f.grade_id%TYPE;
1097 l_asg_loc_id per_all_assignments_f.location_id%TYPE;
1098 l_asg_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
1099 l_asg_emp_catg_id per_all_assignments_f.employment_category%TYPE;
1100 l_bus_grp_id NUMBER;
1101
1102
1103 l_flag NUMBER;
1104
1105 BEGIN
1106 OPEN c_bg;
1107 FETCH c_bg INTO l_bus_grp_id;
1108 CLOSE c_bg;
1109
1110
1111 OPEN c_assignment_details(p_assignment_id,p_effective_date);
1112 FETCH c_assignment_details INTO l_asg_payroll_id,
1113 l_asg_org_id,
1114 l_asg_pos_id,
1115 l_asg_job_id,
1116 l_asg_grd_id,
1117 l_asg_loc_id,
1118 l_asg_pay_basis_id,
1119 l_asg_emp_catg_id;
1120 CLOSE c_assignment_details;
1121
1122 OPEN c_element_link_details(p_element_type_id,p_effective_date,l_bus_grp_id,
1123 l_asg_payroll_id,l_asg_org_id,l_asg_pos_id,l_asg_job_id,
1124 l_asg_grd_id,l_asg_loc_id,l_asg_pay_basis_id,l_asg_emp_catg_id) ;
1125 FETCH c_element_link_details INTO l_element_link_id,
1126 l_link_payroll_id,
1127 l_link_to_all_payrolls_flag,
1128 l_link_org_id,
1129 l_link_pos_id,
1130 l_link_job_id,
1131 l_link_grd_id,
1132 l_link_loc_id,
1133 l_link_pay_basis_id,
1134 l_link_emp_catg_id,
1135 l_link_pep_grp_id;
1136 CLOSE c_element_link_details;
1137
1138
1139
1140 OPEN c_link_usage(p_assignment_id,l_element_link_id,p_effective_date);
1141 FETCH c_link_usage INTO l_flag;
1142 CLOSE c_link_usage;
1143
1144 IF ((l_link_payroll_id IS NOT NULL AND l_link_payroll_id = l_asg_payroll_id)
1145 OR
1146 (l_link_to_all_payrolls_flag ='Y' AND l_asg_payroll_id IS NOT NULL)
1147 OR
1148 (l_link_payroll_id IS NULL AND l_link_to_all_payrolls_flag ='N')
1149 )
1150 AND (l_link_pep_grp_id IS NULL OR 1 = l_flag)
1151 THEN
1152 RETURN l_element_link_id;
1153 ELSE
1154 RETURN NULL;
1155 END IF;
1156 END get_element_link_id;
1157
1158 -------------------------------------------------------------------------
1159 -- --
1160 -- Name : chk_element_link --
1161 -- Type : FUNCTION --
1162 -- Access : Public --
1163 -- Description : This function checks if an assignment is eligible --
1164 -- for an element as on a given date. --
1165 -- Parameters : --
1166 -- IN : p_element_name VARCHAR2 --
1167 -- p_assignment_id NUMBER --
1168 -- p_effective_date DATE --
1169 -- OUT : p_element_link_id NUMBER --
1170 -- RETURN : VARCHAR2 --
1171 -- Change History : --
1172 --------------------------------------------------------------------------
1173 -- Rev# Date Userid Description --
1174 --------------------------------------------------------------------------
1175 -- 1.0 27/12/04 lnagaraj Created this function --
1176 --------------------------------------------------------------------------
1177 FUNCTION chk_element_link(p_element_name IN VARCHAR2
1178 ,p_assignment_id IN NUMBER
1179 ,p_effective_date IN DATE
1180 ,p_element_link_id OUT NOCOPY NUMBER)
1181 RETURN VARCHAR2
1182 IS
1183 CURSOR csr_element_type_id
1184 IS
1185 SELECT element_type_id
1186 FROM pay_element_types_f
1187 WHERE (legislation_code = 'IN' OR business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
1188 AND element_name = p_element_name
1189 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1190
1191 /*
1192 CURSOR csr_element_link_details
1193 IS
1194 SELECT link.element_link_id
1195 FROM per_assignments_f assgn
1196 , pay_element_links_f link
1197 , pay_element_types_f types
1198 WHERE assgn.assignment_id = p_assignment_id
1199 AND ((link.payroll_id IS NOT NULL AND link.payroll_id = assgn.payroll_id) OR
1200 (link.link_to_all_payrolls_flag = 'Y' AND assgn.payroll_id IS NOT NULL) OR
1201 (link.payroll_id IS NULL AND link.link_to_all_payrolls_flag = 'N'))
1202 AND (link.organization_id = assgn.organization_id OR link.organization_id IS NULL)
1203 AND (link.position_id = assgn.position_id OR link.position_id IS NULL)
1204 AND (link.job_id = assgn.job_id OR link.job_id IS NULL)
1205 AND (link.grade_id = assgn.grade_id OR link.grade_id IS NULL)
1206 AND (link.location_id = assgn.location_id OR link.location_id IS NULL)
1207 AND (link.pay_basis_id = assgn.pay_basis_id OR link.pay_basis_id IS NULL)
1208 AND (link.employment_category = assgn.employment_category OR link.employment_category IS NULL)
1209 AND (link.people_group_id IS NULL OR
1210 EXISTS ( SELECT 1 FROM pay_assignment_link_usages_f usage
1211 WHERE usage.assignment_id = assgn.assignment_id
1212 AND usage.element_link_id = link.element_link_id
1213 AND p_effective_date BETWEEN usage.effective_start_date AND usage.effective_end_date
1214 ))
1215 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
1216 AND link.business_group_id = assgn.business_group_id
1217 AND link.element_type_id = types.element_type_id
1218 AND types.element_name = p_element_name
1219 AND p_effective_date BETWEEN assgn.effective_start_date
1220 AND assgn.effective_end_date
1221 AND p_effective_date BETWEEN link.effective_start_date
1222 AND link.effective_end_date
1223 AND p_effective_date BETWEEN types.effective_start_date
1224 AND types.effective_end_date;
1225 */
1226 p_message VARCHAR2(30);
1227 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1228
1229 BEGIN
1230 p_message :='SUCCESS';
1231 /*
1232 OPEN csr_element_link_details;
1233 FETCH csr_element_link_details INTO p_element_link_id ;
1234 CLOSE csr_element_link_details;
1235 */
1236 OPEN csr_element_type_id;
1237 FETCH csr_element_type_id INTO l_element_type_id ;
1238 CLOSE csr_element_type_id;
1239
1240 p_element_link_id := get_element_link_id(p_assignment_id
1241 ,p_effective_date
1242 ,l_element_type_id
1243 );
1244
1245 IF p_element_link_id IS NULL THEN
1246 --
1247 p_message := 'PER_IN_MISSING_LINK';
1248 --
1249 END IF;
1250
1251 RETURN p_message;
1252
1253 END chk_element_link;
1254
1255 --------------------------------------------------------------------------
1256 -- --
1257 -- Name : GET_EE_VALUE --
1258 -- Type : FUNCTION --
1259 -- Access : Public --
1260 -- Description : Function to return the element entry value --
1261 -- --
1262 -- Parameters : --
1263 -- IN : p_element_entry_id NUMBER --
1264 -- p_input_name VARCHAR2 --
1265 -- p_effective_date DATE --
1266 -- RETURN : VARCHAR2 --
1267 ---------------------------------------------------------------------------
1268 FUNCTION get_ee_value
1269 (p_element_entry_id IN NUMBER
1270 ,p_input_name IN VARCHAR2
1271 ,p_effective_date IN DATE
1272 )
1273 RETURN VARCHAR2
1274 IS
1275 CURSOR c_entry_value
1276 IS
1277 SELECT val.screen_entry_value
1278 FROM pay_element_entry_values_f val
1279 ,pay_input_values_f inputs
1280 WHERE val.input_value_id = inputs.input_value_id
1281 AND val.element_entry_id = p_element_entry_id
1282 AND inputs.name = p_input_name
1283 AND inputs.legislation_code = 'IN'
1284 AND p_effective_date between val.effective_start_date AND val.effective_end_date
1285 AND p_effective_date between inputs.effective_start_date AND inputs.effective_end_date;
1286 --
1287 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE := NULL;
1288 BEGIN
1289
1290 OPEN c_entry_value;
1291 FETCH c_entry_value INTO l_screen_entry_value;
1292 CLOSE c_entry_value;
1293
1294 RETURN l_screen_entry_value;
1295
1296 END get_ee_value;
1297
1298 --------------------------------------------------------------------------
1299 -- --
1300 -- Name : GET_EE_VALUE --
1301 -- Type : FUNCTION --
1302 -- Access : Public --
1303 -- Description : Function to return the element entry value --
1304 -- --
1305 -- Parameters : --
1306 -- IN : p_element_entry_id NUMBER --
1307 -- p_input_name VARCHAR2 --
1308 -- RETURN : VARCHAR2 --
1309 ---------------------------------------------------------------------------
1310 FUNCTION get_ee_value
1311 (p_element_entry_id IN NUMBER
1312 ,p_input_name IN VARCHAR2
1313 )
1314 RETURN VARCHAR2
1315 IS
1316 CURSOR c_entry_value
1317 IS
1318 SELECT val.screen_entry_value
1319 FROM pay_element_entry_values_f val
1320 ,pay_input_values_f inputs
1321 WHERE val.input_value_id = inputs.input_value_id
1322 AND val.element_entry_id = p_element_entry_id
1323 AND inputs.name = p_input_name
1324 AND inputs.legislation_code = 'IN';
1325 --
1326 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE := NULL;
1327 BEGIN
1328 set_location(g_debug, 'Entered '|| g_package||'.get_ee_value',1);
1329 OPEN c_entry_value;
1330 FETCH c_entry_value INTO l_screen_entry_value;
1331 CLOSE c_entry_value;
1332 set_location(g_debug, 'Leaving '|| g_package||'.get_ee_value',5);
1333 RETURN l_screen_entry_value;
1334
1335 END get_ee_value;
1336
1337
1338 --------------------------------------------------------------------------
1339 -- --
1340 -- Name : GET_ACTION_TYPE --
1341 -- Type : FUNCTION --
1342 -- Access : Public --
1343 -- Description : Function to return the action_type of an asact_id --
1344 -- --
1345 -- Parameters : --
1346 -- IN : p_assignment_action_id NUMBER --
1347 -- RETURN : VARCHAR2 --
1348 ---------------------------------------------------------------------------
1349 FUNCTION get_action_type
1350 (p_assignment_action_id IN NUMBER
1351 )
1352 RETURN VARCHAR2
1353 IS
1354 CURSOR c_act_type IS
1355 SELECT 'L'
1356 FROM pay_payroll_actions ppa
1357 ,pay_assignment_actions paa
1358 WHERE paa.payroll_action_id = ppa.payroll_action_id
1359 AND paa.assignment_action_id = p_assignment_action_id
1360 AND EXISTS (SELECT 1
1361 FROM pay_payroll_Actions ppa2
1362 WHERE ppa2.effective_date >= ppa.effective_date
1363 AND ppa2.action_type IN ('R','Q')
1364 AND ppa2.action_status = 'C') ;
1365
1366 l_act_type pay_payroll_actions.action_type%TYPE;
1367 BEGIN
1368
1369 OPEN c_act_type;
1370 FETCH c_act_type
1371 INTO l_act_type;
1372 IF c_act_type%NOTFOUND THEN
1373 l_act_type := 'K';
1374 END IF;
1375 CLOSE c_act_type;
1376 RETURN l_act_type;
1377
1378 END get_action_type;
1379
1380 --------------------------------------------------------------------------
1381 -- --
1382 -- Name : GET_TAX_UNIT_ID --
1383 -- Type : FUNCTION --
1384 -- Access : Public --
1385 -- Description : Function to return the tax unit id for an assignment--
1386 -- --
1387 -- Parameters : --
1388 -- IN : p_assignment_id NUMBER --
1389 -- p_effective_date DATE --
1390 -- RETURN : VARCHAR2 --
1391 ---------------------------------------------------------------------------
1392 FUNCTION get_tax_unit_id
1393 (p_assignment_id IN NUMBER
1394 ,p_effective_date DATE
1395 )
1396 RETURN VARCHAR2
1397 IS
1398 CURSOR c_gre_id
1399 IS
1400 SELECT scl.segment1
1401 FROM per_assignments_f asg,
1402 hr_soft_coding_keyflex scl
1403 WHERE asg.assignment_id = p_assignment_id
1404 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1405 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
1406
1407 l_tax_unit_id hr_soft_coding_keyflex.segment1%TYPE;
1408 BEGIN
1409 OPEN c_gre_id;
1410 FETCH c_gre_id INTO l_tax_unit_id;
1411 CLOSE c_gre_id;
1412
1413 RETURN l_tax_unit_id;
1414 END get_tax_unit_id;
1415
1416 --------------------------------------------------------------------------
1417 -- Name : GET_FORMULA_ID --
1418 -- Type : FUNCTION --
1419 -- Access : Private --
1420 -- Description : Function to fetch the formula_id --
1421 -- Parameters : --
1422 -- IN : p_effective_date DATE --
1423 -- : p_formula_name VARCHAR2 --
1424 -- OUT : N/A --
1425 -- RETURN : Number --
1426 --------------------------------------------------------------------------
1427 FUNCTION get_formula_id
1428 (p_effective_date IN DATE
1429 ,p_formula_name IN VARCHAR2
1430 )
1431 RETURN NUMBER
1432 IS
1433 l_formula_id NUMBER ;
1434 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_formula_id';
1435 l_message VARCHAR2(255);
1436 BEGIN
1437 g_debug := hr_utility.debug_enabled;
1438 set_location(g_debug, 'Entering: '||l_procedure,10);
1439
1440 SELECT formula_id
1441 INTO l_formula_id
1442 FROM ff_formulas_f
1443 WHERE legislation_code = 'IN'
1444 AND formula_name = p_formula_name
1445 AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
1446
1447 trace('Formula Id',l_formula_id);
1448
1449 set_location(g_debug, 'Leaving: '||l_procedure,20);
1450 RETURN l_formula_id;
1451
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 set_location(g_debug, 'Leaving: '||l_procedure,30);
1455 l_message := pay_in_utils.get_pay_message
1456 ('PER_IN_ORACLE_GENERIC_ERROR',
1457 'FUNCTION:'||l_procedure,
1458 'SQLERRMC:'||SQLERRM);
1459 trace('SQLERRM',l_message);
1460 RAISE ;
1461
1462 END get_formula_id;
1463
1464 --------------------------------------------------------------------------
1465 -- Name : GET_ELEMENT_TYPE_ID --
1466 -- Type : FUNCTION --
1467 -- Access : Public --
1468 -- Description : Function to fetch the element_type_id --
1469 -- Parameters : --
1470 -- IN : p_effective_date DATE --
1471 -- : p_element_name VARCHAR2 --
1472 -- OUT : N/A --
1473 -- RETURN : Number --
1474 --------------------------------------------------------------------------
1475 FUNCTION get_element_type_id
1476 (p_effective_date IN DATE
1477 ,p_element_name IN VARCHAR2
1478 )
1479 RETURN NUMBER
1480 IS
1481
1482 l_element_id NUMBER ;
1483 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_element_type_id';
1484 l_message VARCHAR2(255);
1485 BEGIN
1486 g_debug := hr_utility.debug_enabled;
1487 set_location(g_debug, 'Entering: '||l_procedure,10);
1488
1489 SELECT element_type_id
1490 INTO l_element_id
1491 FROM pay_element_types_f
1492 WHERE element_name = p_element_name
1493 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1494
1495 trace('Element Type Id',l_element_id);
1496
1497 set_location(g_debug, 'Leaving: '||l_procedure,20);
1498 RETURN l_element_id;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 set_location(g_debug, 'Leaving: '||l_procedure,30);
1503 l_message := pay_in_utils.get_pay_message
1504 ('PER_IN_ORACLE_GENERIC_ERROR',
1505 'FUNCTION:'||l_procedure,
1506 'SQLERRMC:'||SQLERRM);
1507 trace('SQLERRM',l_message);
1508 RETURN TO_NUMBER(NULL);
1509 RAISE ;
1510
1511 END get_element_type_id;
1512
1513 --------------------------------------------------------------------------
1514 -- Name : GET_BALANCE_TYPE_ID --
1515 -- Type : FUNCTION --
1516 -- Access : Public --
1517 -- Description : Function to fetch the balance_type_id --
1518 -- Parameters : --
1519 -- IN : p_balance_name VARCHAR2 --
1520 -- OUT : N/A --
1521 -- RETURN : Number --
1522 --------------------------------------------------------------------------
1523 FUNCTION get_balance_type_id
1524 (p_balance_name IN VARCHAR2
1525 )
1526 RETURN NUMBER
1527 IS
1528 l_balance_id NUMBER ;
1529 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_balance_type_id';
1530 l_message VARCHAR2(255);
1531 BEGIN
1532 g_debug := hr_utility.debug_enabled;
1533 set_location(g_debug, 'Entering: '||l_procedure,10);
1534
1535 SELECT balance_type_id
1536 INTO l_balance_id
1537 FROM pay_balance_types
1538 WHERE legislation_code = 'IN'
1539 AND balance_name = p_balance_name;
1540
1541 trace('Balance Type Id',l_balance_id);
1542
1543 set_location(g_debug, 'Leaving: '||l_procedure,20);
1544 RETURN l_balance_id;
1545
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 set_location(g_debug, 'Leaving: '||l_procedure,30);
1549 l_message := pay_in_utils.get_pay_message
1550 ('PER_IN_ORACLE_GENERIC_ERROR',
1551 'FUNCTION:'||l_procedure,
1552 'SQLERRMC:'||SQLERRM);
1553 trace('SQLERRM',l_message);
1554 RETURN TO_NUMBER(NULL);
1555 RAISE ;
1556
1557 END get_balance_type_id;
1558
1559 --------------------------------------------------------------------------
1560 -- Name : GET_INPUT_VALUE_ID --
1561 -- Type : FUNCTION --
1562 -- Access : Public --
1563 -- Description : Function to fetch the input_value_id --
1564 -- Parameters : --
1565 -- IN : p_effective_date DATE --
1566 -- : p_element_id NUMBER --
1567 -- : p_input_value VARCHAR2 --
1568 -- OUT : N/A --
1569 -- RETURN : Number --
1570 --------------------------------------------------------------------------
1571 FUNCTION get_input_value_id
1572 (p_effective_date IN DATE
1573 ,p_element_id IN NUMBER
1574 ,p_input_value IN VARCHAR2
1575 )
1576 RETURN NUMBER
1577 IS
1578 CURSOR c_input_value_id
1579 IS
1580 SELECT input_value_id
1581 FROM pay_input_values_f
1582 WHERE element_type_id = p_element_id
1583 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1584 AND NAME = p_input_value;
1585
1586 l_input_value_id NUMBER ;
1587 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_input_value_id';
1588 l_message VARCHAR2(255);
1589 BEGIN
1590 g_debug := hr_utility.debug_enabled;
1591 set_location(g_debug, 'Entering: '||l_procedure,10);
1592
1593 OPEN c_input_value_id;
1594 FETCH c_input_value_id INTO l_input_value_id;
1595 IF (c_input_value_id%NOTFOUND)
1596 THEN
1597 set_location(g_debug, 'l_input_value_id: NULL ',15);
1598 END IF;
1599 CLOSE c_input_value_id;
1600
1601 trace('Input Value Id',l_input_value_id);
1602
1603 set_location(g_debug, 'Leaving: '||l_procedure,20);
1604 RETURN l_input_value_id;
1605
1606 EXCEPTION
1607 WHEN OTHERS THEN
1608 set_location(g_debug, 'Leaving: '||l_procedure,30);
1609 l_message := pay_in_utils.get_pay_message
1610 ('PER_IN_ORACLE_GENERIC_ERROR',
1611 'FUNCTION:'||l_procedure,
1612 'SQLERRMC:'||SQLERRM);
1613 trace('SQLERRM',l_message);
1614 RETURN TO_NUMBER(NULL);
1615 RAISE ;
1616
1617 END get_input_value_id;
1618
1619 --------------------------------------------------------------------------
1620 -- Name : GET_INPUT_VALUE_ID --
1621 -- Type : FUNCTION --
1622 -- Access : Public --
1623 -- Description : Function to fetch the input_value_id --
1624 -- Parameters : --
1625 -- IN : p_effective_date DATE --
1626 -- : p_element_id NUMBER --
1627 -- : p_input_value VARCHAR2 --
1628 -- OUT : N/A --
1629 -- RETURN : Number --
1630 --------------------------------------------------------------------------
1631 FUNCTION get_input_value_id
1632 (p_effective_date IN DATE
1633 ,p_element_name IN VARCHAR2
1634 ,p_input_value IN VARCHAR2
1635 )
1636 RETURN NUMBER
1637 IS
1638
1639 l_input_value_id NUMBER ;
1640 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_input_value_id';
1641 l_message VARCHAR2(255);
1642 BEGIN
1643 g_debug := hr_utility.debug_enabled;
1644 set_location(g_debug, 'Entering: '||l_procedure,10);
1645
1646 l_input_value_id := get_input_value_id
1647 (p_effective_date
1648 ,get_element_type_id(p_effective_date, p_element_name)
1649 ,p_input_value);
1650
1651 trace('Input Value Id',l_input_value_id);
1652
1653 set_location(g_debug, 'Leaving: '||l_procedure,20);
1654 RETURN l_input_value_id;
1655
1656 EXCEPTION
1657 WHEN OTHERS THEN
1658 set_location(g_debug, 'Leaving: '||l_procedure,30);
1659 l_message := pay_in_utils.get_pay_message
1660 ('PER_IN_ORACLE_GENERIC_ERROR',
1661 'FUNCTION:'||l_procedure,
1662 'SQLERRMC:'||SQLERRM);
1663 trace('SQLERRM',l_message);
1664 RETURN TO_NUMBER(NULL);
1665 RAISE ;
1666
1667 END get_input_value_id;
1668
1669 --------------------------------------------------------------------------
1670 -- Name : GET_TEMPLATE_ID --
1671 -- Type : FUNCTION --
1672 -- Access : Private --
1673 -- Description : Procedure to fetch the template_id --
1674 -- Parameters : --
1675 -- IN : p_template_name VARCHAR2 --
1676 -- OUT : p_template_id NUMBER --
1677 -- RETURN : N/A --
1678 --------------------------------------------------------------------------
1679 FUNCTION get_template_id
1680 (p_template_name IN VARCHAR2
1681 )
1682 RETURN NUMBER
1683 IS
1684 l_template_id NUMBER;
1685 l_procedure VARCHAR2(100):= g_package||'get_template_id';
1686 l_message VARCHAR2(1000);
1687 BEGIN
1688 set_location(g_debug,'Entering: '||l_procedure,10);
1689
1690 SELECT template_id
1691 INTO l_template_id
1692 FROM pay_element_templates
1693 WHERE template_name = p_template_name
1694 AND legislation_code = 'IN';
1695
1696 trace('Template Id',l_template_id);
1697
1698 set_location(g_debug,'Leaving: '||l_procedure,20);
1699
1700 RETURN l_template_id;
1701
1702 EXCEPTION
1703 WHEN NO_DATA_FOUND THEN
1704 RETURN TO_NUMBER(NULL);
1705 WHEN OTHERS THEN
1706 set_location(g_debug, 'Leaving: '||l_procedure,30);
1707 l_message := pay_in_utils.get_pay_message
1708 ('PER_IN_ORACLE_GENERIC_ERROR',
1709 'FUNCTION:'||l_procedure,
1710 'SQLERRMC:'||SQLERRM);
1711 trace('SQLERRM',l_message);
1712 RAISE ;
1713
1714 END get_template_id;
1715
1716 --------------------------------------------------------------------------
1717 -- Name : INS_FORM_RES_RULE --
1718 -- Type : PROCEDURE --
1719 -- Access : Private --
1720 -- Description : Procedure to update element details in post-process --
1721 -- Parameters : --
1722 -- IN : p_business_group_id NUMBER --
1723 -- : p_effective_date DATE --
1724 -- : p_status_processing_rule_id NUMBER --
1725 -- : p_result_name VARCHAR2 --
1726 -- : p_result_rule_type VARCHAR2 --
1727 -- : p_element_name VARCHAR2 --
1728 -- : p_input_value_name VARCHAR2 --
1729 -- : p_severity_level VARCHAR2 --
1730 -- : p_element_type_id NUMBER --
1731 -- OUT : N/A --
1732 -- RETURN : N/A --
1733 --------------------------------------------------------------------------
1734 PROCEDURE ins_form_res_rule
1735 (
1736 p_business_group_id NUMBER,
1737 p_effective_date DATE ,
1738 p_status_processing_rule_id NUMBER,
1739 p_result_name VARCHAR2,
1740 p_result_rule_type VARCHAR2,
1741 p_element_name VARCHAR2 DEFAULT NULL,
1742 p_input_value_name VARCHAR2 DEFAULT NULL,
1743 p_severity_level VARCHAR2 DEFAULT NULL,
1744 p_element_type_id NUMBER DEFAULT NULL
1745 )
1746 IS
1747
1748 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
1749 v_form_res_rule_id NUMBER;
1750 l_input_value_id pay_formula_result_rules_f.input_value_id%TYPE;
1751 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1752 l_procedure CONSTANT VARCHAR2(100):= g_package||'ins_form_res_rule';
1753 l_message VARCHAR2(1000);
1754
1755 BEGIN
1756 set_location(g_debug,'Entering : '||l_procedure,10);
1757
1758 IF p_result_rule_type IN('D','U') THEN
1759
1760 set_location(g_debug,l_procedure,20);
1761
1762 l_input_value_id :=
1763 get_input_value_id(p_effective_date
1764 ,p_element_type_id
1765 ,p_input_value_name);
1766
1767 ELSIF p_result_rule_type IN ('I') THEN
1768
1769 set_location(g_debug,l_procedure,30);
1770 l_element_type_id :=
1771 get_element_type_id(p_effective_date
1772 ,p_element_name);
1773
1774 l_input_value_id :=
1775 get_input_value_id(p_effective_date
1776 ,l_element_type_id
1777 ,p_input_value_name);
1778
1779 END IF;
1780
1781 set_location(g_debug,l_procedure,40);
1782 SELECT pay_formula_result_rules_s.nextval
1783 INTO v_form_res_rule_id
1784 FROM sys.dual;
1785
1786 set_location(g_debug,l_procedure,50);
1787 INSERT INTO pay_formula_result_rules_f
1788 (formula_result_rule_id,
1789 effective_start_date,
1790 effective_end_date,
1791 business_group_id,
1792 status_processing_rule_id,
1793 result_name,
1794 result_rule_type,
1795 severity_level,
1796 input_value_id,
1797 last_update_date,
1798 last_updated_by,
1799 last_update_login,
1800 created_by,
1801 creation_date,
1802 element_type_id)
1803 VALUES
1804 (v_form_res_rule_id,
1805 p_effective_date,
1806 c_end_of_time,
1807 p_business_group_id,
1808 p_status_processing_rule_id,
1809 upper(p_result_name),
1810 p_result_rule_type,
1811 p_severity_level,
1812 l_input_value_id,
1813 trunc(sysdate),
1814 -1,
1815 -1,
1816 -1,
1817 trunc(sysdate),
1818 decode(p_result_rule_type,
1819 'D',p_element_type_id,
1820 'S',p_element_type_id,
1821 'I',l_element_type_id,
1822 'U',p_element_type_id,null));
1823
1824 set_location(g_debug, 'Leaving: '||l_procedure,60);
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 set_location(g_debug, 'Leaving: '||l_procedure,70);
1829 l_message := get_pay_message
1830 ('PER_IN_ORACLE_GENERIC_ERROR',
1831 'FUNCTION:'||l_procedure,
1832 'SQLERRMC:'||SQLERRM);
1833 trace('SQLERRM',l_message);
1834 RAISE ;
1835
1836 END ins_form_res_rule;
1837
1838 --------------------------------------------------------------------------
1839 -- Name : DEL_FORM_RES_RULE --
1840 -- Type : PROCEDURE --
1841 -- Access : Private --
1842 -- Description : Procedure to delete formula setup for elements --
1843 -- Parameters : --
1844 -- IN : p_element_type_id_id NUMBER --
1845 -- : p_effective_date DATE --
1846 -- OUT : N/A --
1847 -- RETURN : N/A --
1848 --------------------------------------------------------------------------
1849 PROCEDURE del_form_res_rule
1850 (p_element_type_id IN NUMBER,
1851 p_effective_date IN DATE
1852 )
1853 IS
1854 CURSOR csr_sr IS
1855 SELECT rowid
1856 ,status_processing_rule_id
1857 ,effective_start_date
1858 FROM pay_status_processing_rules_f psr
1859 WHERE psr.element_type_id = p_element_type_id
1860 AND p_effective_date BETWEEN psr.effective_start_date
1861 AND psr.effective_end_date;
1862
1863 CURSOR csr_fr (p_status_rule_id IN NUMBER )
1864 IS
1865 SELECT ROWID
1866 ,formula_result_rule_id
1867 ,effective_start_date
1868 FROM pay_formula_result_rules_f
1869 WHERE status_processing_rule_id = p_status_rule_id
1870 AND p_effective_date BETWEEN effective_start_date
1871 AND effective_end_date;
1872
1873 l_procedure CONSTANT VARCHAR2(100):= g_package||'del_form_res_rule';
1874 l_message VARCHAR2(1000);
1875
1876 BEGIN
1877
1878 set_location(g_debug,'Entering : '||l_procedure,10);
1879 FOR j IN csr_sr
1880 LOOP
1881 set_location(g_debug,l_procedure,20);
1882
1883 IF g_debug THEN
1884 trace('Status Rule Id ',j.status_processing_rule_id);
1885 trace('Effective Date ',to_char(j.effective_start_date,'DD-Mon-YYYY'));
1886 END IF ;
1887
1888 FOR k IN csr_fr(j.status_processing_rule_id)
1889 LOOP
1890
1891 set_location(g_debug,l_procedure,30);
1892
1893 IF g_debug THEN
1894 trace('Result Rule Id ',k.formula_result_rule_id);
1895 trace('Effective Date ',to_char(k.effective_start_date,'DD-Mon-YYYY'));
1896 END IF ;
1897
1898 pay_formula_result_rules_pkg.delete_row(k.rowid);
1899
1900 set_location(g_debug,l_procedure,40);
1901
1902 END LOOP ; -- csr_fr ends
1903
1904 set_location(g_debug,l_procedure,50);
1905
1906 pay_status_rules_pkg.delete_row
1907 ( x_rowid => j.rowid
1908 , p_session_date => j.effective_start_date
1909 , p_delete_mode => hr_api.g_zap
1910 , p_status_processing_rule_id => j.status_processing_rule_id
1911 );
1912
1913 set_location(g_debug,l_procedure,60);
1914
1915 END LOOP; -- csr_sr ends
1916 set_location(g_debug,'Leaving : '||l_procedure,70);
1917
1918 END del_form_res_rule;
1919
1920 --------------------------------------------------------------------------
1921 -- Name : DELETE_BALANCE_FEEDS --
1922 -- Type : Procedure --
1923 -- Access : Public --
1924 -- Description : Procedure to fetch the template_id --
1925 -- Parameters : --
1926 -- IN : p_template_name VARCHAR2 --
1927 -- OUT : p_template_id NUMBER --
1928 -- RETURN : N/A --
1929 --------------------------------------------------------------------------
1930 PROCEDURE delete_balance_feeds
1931 (p_balance_name IN VARCHAR2
1932 ,p_element_name IN VARCHAR2
1933 ,p_input_value_name IN VARCHAR2
1934 ,p_effective_date IN DATE
1935 )
1936 IS
1937
1938 CURSOR csr_bf IS
1939 SELECT balance_feed_id, object_version_number
1940 FROM pay_balance_feeds_f
1941 WHERE balance_type_id = get_balance_type_id (p_balance_name)
1942 AND input_value_id = get_input_value_id (p_effective_date, p_element_name, p_input_value_name)
1943 AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
1944
1945 l_bf_id NUMBER ;
1946 l_ovn NUMBER ;
1947 l_start DATE ;
1948 l_end DATE ;
1949 l_warn BOOLEAN ;
1950 l_procedure CONSTANT VARCHAR2(100):= g_package||'delete_balance_feeds';
1951 l_message VARCHAR2(1000);
1952
1953 BEGIN
1954
1955 set_location(g_debug,'Entering : '||l_procedure,10);
1956
1957 OPEN csr_bf;
1958 FETCH csr_bf INTO l_bf_id, l_ovn;
1959 IF csr_bf%NOTFOUND THEN
1960 set_location(g_debug,'Leaving : '||l_procedure,15);
1961 RETURN ;
1962 END IF ;
1963 CLOSE csr_bf;
1964 set_location(g_debug,l_procedure,20);
1965
1966 pay_balance_feeds_api.delete_balance_feed
1967 (
1968 p_effective_date => p_effective_date
1969 ,p_datetrack_delete_mode => hr_api.g_delete
1970 ,p_balance_feed_id => l_bf_id
1971 ,p_object_version_number => l_ovn
1972 ,p_effective_start_date => l_start
1973 ,p_effective_end_date => l_end
1974 ,p_exist_run_result_warning => l_warn
1975 );
1976
1977 set_location(g_debug,'Leaving : '||l_procedure,30);
1978
1979 END delete_balance_feeds;
1980
1981 --------------------------------------------------------------------------
1982 -- Name : GET_PERSON_NAME --
1983 -- Type : FUNCTION --
1984 -- Access : Private --
1985 -- Description : Function to fetch the person name based on person id--
1986 -- Parameters : --
1987 -- IN : p_person_id IN NUMBER --
1988 -- : p_effective_date IN DATE --
1989 -- RETURN : VARCHAR2 --
1990 --------------------------------------------------------------------------
1991 FUNCTION get_person_name
1992 (p_person_id IN NUMBER
1993 ,p_effective_date IN DATE
1994 )
1995 RETURN VARCHAR2
1996 IS
1997 CURSOR c_person_name
1998 IS
1999 SELECT full_name
2000 FROM per_people_f
2001 WHERE person_id = p_person_id
2002 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
2003
2004 l_full_name per_all_people_f.full_name%TYPE;
2005 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_person_name';
2006
2007 BEGIN
2008 set_location(g_debug,'Entering : '||l_procedure,10);
2009
2010 OPEN c_person_name;
2011 FETCH c_person_name INTO l_full_name;
2012 CLOSE c_person_name;
2013
2014 set_location(g_debug,l_procedure,20);
2015
2016 IF g_debug THEN
2017 trace('Full Name is ',l_full_name);
2018 END IF ;
2019 set_location(g_debug,'Leaving : '||l_procedure,30);
2020
2021 RETURN l_full_name;
2022 END get_person_name;
2023
2024 --------------------------------------------------------------------------
2025 -- Name : GET_ORGANIZATION_NAME --
2026 -- Type : FUNCTION --
2027 -- Access : Private --
2028 -- Description : Function to fetch the organization name --
2029 -- Parameters : --
2030 -- IN : p_organization_id IN NUMBER --
2031 -- RETURN : VARCHAR2 --
2032 --------------------------------------------------------------------------
2033 FUNCTION get_organization_name
2034 (p_organization_id IN NUMBER
2035 )
2036 RETURN VARCHAR2
2037 IS
2038 CURSOR c_organization_name
2039 IS
2040 SELECT name
2041 FROM hr_organization_units
2042 WHERE organization_id = p_organization_id;
2043
2044 l_org_name hr_organization_units.name%TYPE;
2045 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_organization_name';
2046
2047 BEGIN
2048 set_location(g_debug,'Entering : '||l_procedure,10);
2049
2050 OPEN c_organization_name;
2051 FETCH c_organization_name INTO l_org_name;
2052 CLOSE c_organization_name;
2053
2054 set_location(g_debug,l_procedure,20);
2055
2056 IF g_debug THEN
2057 trace('Full Name is ',l_org_name);
2058 END IF ;
2059 set_location(g_debug,'Leaving : '||l_procedure,30);
2060
2061 RETURN l_org_name;
2062 END get_organization_name;
2063
2064 --------------------------------------------------------------------------
2065 -- Name : GET_PAYMENT_NAME --
2066 -- Type : FUNCTION --
2067 -- Access : Private --
2068 -- Description : Function to fetch the payment method name --
2069 -- Parameters : --
2070 -- IN : P_PAYMENT_TYPE_ID IN NUMBER --
2071 -- RETURN : VARCHAR2 --
2072 --------------------------------------------------------------------------
2073 FUNCTION get_payment_name
2074 (p_payment_type_id IN NUMBER
2075 )
2076 RETURN VARCHAR2
2077 IS
2078 CURSOR c_payment_method_name
2079 IS
2080 SELECT pptl.payment_type_name
2081 FROM pay_payment_types ppt
2082 ,pay_payment_types_tl pptl
2083 WHERE ppt.payment_type_id = pptl.payment_type_id
2084 AND ppt.territory_code = 'IN'
2085 AND ppt.category <> 'MT'
2086 AND pptl.language = USERENV('LANG')
2087 AND ppt.payment_type_id = p_payment_type_id;
2088
2089 l_payment_mthd_name pay_payment_types_tl.payment_type_name%TYPE;
2090 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_payment_name';
2091 BEGIN
2092 set_location(g_debug,'Entering : '||l_procedure,10);
2093
2094 OPEN c_payment_method_name;
2095 FETCH c_payment_method_name INTO l_payment_mthd_name;
2096 CLOSE c_payment_method_name;
2097
2098 set_location(g_debug,l_procedure,20);
2099
2100 IF g_debug THEN
2101 trace('Payment Method Name is ',l_payment_mthd_name);
2102 END IF ;
2103 set_location(g_debug,'Leaving : '||l_procedure,30);
2104
2105 RETURN l_payment_mthd_name;
2106
2107 END get_payment_name;
2108
2109 --------------------------------------------------------------------------
2110 -- Name : GET_BANK_NAME --
2111 -- Type : FUNCTION --
2112 -- Access : Private --
2113 -- Description : Function to fetch the bank name --
2114 -- Parameters : --
2115 -- IN : p_org_information_id IN NUMBER --
2116 -- RETURN : VARCHAR2 --
2117 --------------------------------------------------------------------------
2118 FUNCTION get_bank_name
2119 (p_org_information_id IN NUMBER
2120 )
2121 RETURN VARCHAR2
2122 IS
2123 CURSOR c_bank_name
2124 IS
2125 SELECT hr_general.decode_lookup('IN_BANK',org_information1)
2126 FROM hr_organization_information
2127 WHERE org_information_context = 'PER_IN_CHALLAN_BANK'
2128 AND org_information_id = p_org_information_id;
2129
2130 l_bank_name VARCHAR2(300);
2131 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_bank_name';
2132 BEGIN
2133 set_location(g_debug,'Entering : '||l_procedure,10);
2134
2135 OPEN c_bank_name;
2136 FETCH c_bank_name INTO l_bank_name;
2137 CLOSE c_bank_name;
2138
2139 set_location(g_debug,l_procedure,20);
2140
2141 IF g_debug THEN
2142 trace('Bank Name is ',l_bank_name);
2143 END IF ;
2144 set_location(g_debug,'Leaving : '||l_procedure,30);
2145
2146 RETURN l_bank_name;
2147
2148 END get_bank_name;
2149
2150 --------------------------------------------------------------------------
2151 -- Name : GET_ADDR_DFF_DETAILS --
2152 -- Type : FUNCTION --
2153 -- Access : Private --
2154 -- Description : Function to fetch the segments of 'Personal Address --
2155 -- : ' Information' DFF for IN localization. --
2156 -- Parameters : --
2157 -- IN : p_address_id IN NUMBER --
2158 -- : p_segment_no IN VARCHAR2 --
2159 -- RETURN : VARCHAR2 --
2160 --------------------------------------------------------------------------
2161 FUNCTION get_addr_dff_details
2162 (p_address_id IN NUMBER
2163 ,p_segment_no IN VARCHAR2
2164 )
2165 RETURN VARCHAR2
2166 IS
2167 CURSOR c_personal_addr_dff_details
2168 IS
2169 SELECT DECODE(p_segment_no,'1',add_information13
2170 ,'2',add_information14
2171 ,'3',hr_general.decode_lookup('IN_STATES',add_information15)
2172 ,hr_general.decode_lookup('YES_NO',add_information16)
2173 )
2174 FROM per_addresses
2175 WHERE address_id = p_address_id
2176 AND style = 'IN';
2177
2178 l_seg_value VARCHAR2(300);
2179 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_addr_dff_details';
2180 BEGIN
2181 set_location(g_debug,'Entering : '||l_procedure,10);
2182
2183 OPEN c_personal_addr_dff_details;
2184 FETCH c_personal_addr_dff_details INTO l_seg_value;
2185 CLOSE c_personal_addr_dff_details;
2186
2187 set_location(g_debug,l_procedure,20);
2188
2189 IF g_debug THEN
2190 trace('Segment Value is ',l_seg_value);
2191 END IF ;
2192 set_location(g_debug,'Leaving : '||l_procedure,30);
2193
2194 RETURN l_seg_value;
2195
2196 END get_addr_dff_details;
2197
2198 --------------------------------------------------------------------------
2199 -- Name : GET_ARCHIVE_REF_NUM --
2200 -- Type : FUNCTION --
2201 -- Access : Private --
2202 -- Description : Function to fetch the form 24Q or 24QC ref number. --
2203 -- Parameters : --
2204 -- IN : p_year IN VARCHAR2 --
2205 -- : p_quarter IN VARCHAR2 --
2206 -- : p_return_type IN VARCHAR2 --
2207 -- : p_organization_id IN NUMBER --
2208 -- : p_action_context_id IN NUMBER --
2209 -- RETURN : VARCHAR2 --
2210 --------------------------------------------------------------------------
2211 FUNCTION get_archive_ref_num
2212 (p_year IN VARCHAR2,
2213 p_quarter IN VARCHAR2,
2214 p_return_type IN VARCHAR2,
2215 p_organization_id IN NUMBER,
2216 p_action_context_id IN NUMBER
2217 )
2218 RETURN VARCHAR2
2219 IS
2220 CURSOR c_archive_ref_number
2221 IS
2222 SELECT action_information30
2223 FROM pay_action_information
2224 WHERE action_information3 = p_year || p_quarter
2225 AND action_context_type = 'PA'
2226 AND action_information_category = DECODE(p_return_type,'O','IN_24Q_ORG','IN_24QC_ORG')
2227 AND action_information30 IS NOT NULL
2228 AND action_information1 = p_organization_id
2229 AND action_context_id = p_action_context_id
2230 ORDER BY action_information30 DESC;
2231
2232 l_archive_ref_number VARCHAR2(300);
2233 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_archive_ref_num';
2234 BEGIN
2235 set_location(g_debug,'Entering : '||l_procedure,10);
2236
2237 OPEN c_archive_ref_number;
2238 FETCH c_archive_ref_number INTO l_archive_ref_number;
2239 CLOSE c_archive_ref_number;
2240
2241 set_location(g_debug,l_procedure,20);
2242
2243 IF g_debug THEN
2244 trace('Archive Reference Number is ',l_archive_ref_number);
2245 END IF ;
2246 set_location(g_debug,'Leaving : '||l_procedure,30);
2247
2248 RETURN l_archive_ref_number;
2249
2250 END get_archive_ref_num;
2251
2252 --------------------------------------------------------------------------
2253 -- Name : get_processing_type --
2254 -- Type : Function --
2255 -- Access : Public --
2256 -- Description : Function to get processing type --
2257 -- (Non-recurring/Recurring) of an element --
2258 -- Parameters : --
2259 -- IN : p_element_type_id NUMBER --
2260 -- p_business_group_id NUMBER --
2261 -- p_earned_date DATE --
2262 --------------------------------------------------------------------------
2263 FUNCTION get_processing_type
2264 (p_element_type_id IN NUMBER
2265 ,p_business_group_id IN NUMBER
2266 ,p_earned_date IN DATE
2267 )
2268 RETURN VARCHAR IS
2269
2270 CURSOR c_processing_type IS
2271 SELECT processing_type
2272 FROM pay_element_types_f
2273 WHERE element_type_id = p_element_type_id
2274 AND business_group_id = p_business_group_id
2275 AND p_earned_date BETWEEN effective_start_date AND effective_end_date;
2276
2277 l_processing_type VARCHAR2(5);
2278 l_procedure CONSTANT VARCHAR2(100):= g_package ||'get_processing_type';
2279
2280 BEGIN
2281
2282 set_location(g_debug,'Entering : '||l_procedure,10);
2283
2284 IF (g_debug)THEN
2285 trace('**************************************************','********************');
2286 set_location(g_debug,'Input Paramters value is',20);
2287 trace('p_element_type_id',p_element_type_id);
2288 trace('p_business_group_id',p_business_group_id);
2289 trace('p_earned_date',p_earned_date);
2290 trace('**************************************************','********************');
2291 END IF;
2292
2293 OPEN c_processing_type;
2294 FETCH c_processing_type INTO l_processing_type;
2295 CLOSE c_processing_type;
2296
2297 IF (g_debug)THEN
2298 trace('**************************************************','********************');
2299 trace('l_processing_type is ',l_processing_type);
2300 trace('**************************************************','********************');
2301 END IF;
2302
2303 set_location(g_debug,'Leaving : '||l_procedure,30);
2304 RETURN l_processing_type;
2305
2306 END get_processing_type;
2307
2308 --------------------------------------------------------------------------
2309 -- Name : chk_business_group --
2310 -- Type : FUNCTION --
2311 -- Access : Public --
2312 -- Description : Function to check the business group as per the --
2313 -- : profile value. --
2314 -- Parameters : --
2315 -- IN : p_assignment_id IN NUMBER --
2316 -- RETURN : VARCHAR2 --
2317 --------------------------------------------------------------------------
2318 FUNCTION chk_business_group
2319 (p_assignment_id IN NUMBER
2320 )
2321 RETURN NUMBER
2322 IS
2323 CURSOR c_asg_bg_id
2324 IS
2325 SELECT 1
2326 FROM per_assignments_f
2327 WHERE assignment_id = p_assignment_id
2328 AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2329 ORDER BY effective_end_date DESC;
2330
2331 l_bg_flag NUMBER := -1;
2332 l_procedure CONSTANT VARCHAR2(100):= g_package ||'chk_business_group';
2333 BEGIN
2334 set_location(g_debug,'Entering : '||l_procedure,10);
2335
2336 OPEN c_asg_bg_id;
2337 FETCH c_asg_bg_id INTO l_bg_flag;
2338 CLOSE c_asg_bg_id;
2339
2340 set_location(g_debug,l_procedure,20);
2341
2342 IF g_debug THEN
2343 trace('l_bg_flag is ',l_bg_flag);
2344 END IF ;
2345
2346 IF (l_bg_flag = -1)
2347 THEN
2348 set_location(g_debug,'Leaving : '||l_procedure,35);
2349 RETURN -1;
2350 ELSE
2351 set_location(g_debug,'Leaving : '||l_procedure,40);
2352 RETURN p_assignment_id;
2353 END IF;
2354 END chk_business_group;
2355
2356 --------------------------------------------------------------------------
2357 -- Name : GET_SECONDARY_CLASSIFICATION --
2358 -- Type : Function --
2359 -- Access : Public --
2360 -- Description : Function to fetch the secondary classification --
2361 -- Parameters : --
2362 -- IN : p_element_type_id NUMBER --
2363 -- OUT : p_date_earned DATE --
2364 -- RETURN : NUMBER --
2365 --------------------------------------------------------------------------
2366 FUNCTION get_secondary_classification
2367 (p_element_type_id NUMBER
2368 ,p_date_earned DATE
2369 )
2370 RETURN NUMBER
2371 IS
2372 CURSOR c_element_sec_classification
2373 IS
2374 SELECT DECODE(pec.classification_name
2375 ,'Monetary Perquisite',1
2376 ,'Non Monetary Perquisite',2
2377 ,-1
2378 )
2379 FROM pay_sub_classification_rules_f pscr
2380 ,pay_element_classifications pec
2381 WHERE pscr.classification_id = pec.classification_id
2382 AND pec.parent_classification_id =
2383 (SELECT classification_id FROM pay_element_classifications
2384 WHERE classification_name = 'Perquisites'
2385 AND legislation_code = 'IN'
2386 )
2387 AND element_type_id = p_element_type_id
2388 AND p_date_earned BETWEEN pscr.effective_start_date
2389 AND pscr.effective_end_date;
2390
2391 l_sec_classification NUMBER;
2392 BEGIN
2393 OPEN c_element_sec_classification;
2394 FETCH c_element_sec_classification INTO l_sec_classification;
2395 CLOSE c_element_sec_classification;
2396
2397 RETURN l_sec_classification;
2398 END get_secondary_classification;
2399
2400 --------------------------------------------------------------------------
2401 -- Name : GET_CONFIGURATION_INFO --
2402 -- Type : Procedure --
2403 -- Access : Public --
2404 -- Description : Procedure to fetch the configuartion information --
2405 -- Parameters : --
2406 -- IN : p_element_type_id NUMBER --
2407 -- OUT : p_date_earned DATE --
2408 -- RETURN : NUMBER --
2409 --------------------------------------------------------------------------
2410 FUNCTION get_configuration_info
2411 (p_element_type_id NUMBER
2412 ,p_date_earned DATE
2413 )
2414 RETURN VARCHAR2
2415 IS
2416 CURSOR c_config_info
2417 IS
2418 SELECT pet.configuration_information2
2419 FROM pay_element_types_f pee
2420 ,pay_element_templates pet
2421 ,pay_shadow_element_types pset
2422 WHERE pee.element_type_id = p_element_type_id
2423 AND pee.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2424 AND pee.element_name = pset.element_name
2425 AND pset.template_id = pet.template_id
2426 AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date;
2427
2428 l_config_info pay_element_templates.configuration_information2%TYPE;
2429
2430 BEGIN
2431 OPEN c_config_info;
2432 FETCH c_config_info INTO l_config_info;
2433 CLOSE c_config_info;
2434 RETURN l_config_info;
2435 END get_configuration_info;
2436 --------------------------------------------------------------------------
2437 -- Name : GET_ELEMENT_ENTRY_END_DATE --
2438 -- Type : Procedure --
2439 -- Access : Public --
2440 -- Description : Procedure to fetch the element entry date --
2441 -- Parameters : --
2442 -- IN : p_element_entry_id NUMBER --
2443 -- RETURN : DATE --
2444 --------------------------------------------------------------------------
2445 FUNCTION get_element_entry_end_date
2446 (p_element_entry_id NUMBER
2447 )
2448 RETURN DATE
2449 IS
2450 CURSOR c_get_ee_end_date
2451 IS
2452 SELECT effective_end_date
2453 FROM pay_element_entries_f
2454 WHERE element_entry_id = p_element_entry_id;
2455
2456 l_ee_end_date pay_element_entries_f.effective_end_date%TYPE;
2457 BEGIN
2458 OPEN c_get_ee_end_date;
2459 FETCH c_get_ee_end_date INTO l_ee_end_date;
2460 CLOSE c_get_ee_end_date;
2461
2462 RETURN l_ee_end_date;
2463 END get_element_entry_end_date;
2464
2465 --------------------------------------------------------------------------
2466 -- Name : GET_CONTACT_RELATIONSHIP --
2467 -- Type : fUNCTION --
2468 -- Access : Public --
2469 -- Description : Gets the relationship between 2 person --
2470 -- Parameters : --
2471 -- IN : p_asg_id NUMBER --
2472 -- : p_contact_person_id NUMBER --
2473 -- RETURN : VARCHAR2 --
2474
2475 --------------------------------------------------------------------------
2476 FUNCTION get_contact_relationship
2477 (p_asg_id NUMBER
2478 ,p_contact_person_id NUMBER
2479 )
2480 RETURN VARCHAR2
2481 IS
2482 CURSOR c_relationship
2483 IS
2484 SELECT hr_general.decode_lookup('CONTACT',RELATION.CONTACT_TYPE)
2485 FROM per_contact_relationships relation,
2486 per_all_people_f ppf,
2487 per_all_assignments_f asg
2488 WHERE relation.contact_person_id = p_contact_person_id
2489 AND relation.person_id = ppf.person_id
2490 AND asg.person_id = ppf.person_id
2491 AND asg.assignment_id = p_asg_id
2492 AND SYSDATE >= relation.date_start
2493 AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date
2494 AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
2495
2496
2497 l_relation hr_lookups.meaning%TYPE;
2498 l_emp_person_id NUMBER;
2499
2500
2501 BEGIN
2502 l_emp_person_id := get_person_id(p_asg_id,SYSDATE);
2503 IF (l_emp_person_id = p_contact_person_id) THEN
2504 l_relation := 'Self';
2505 ELSE
2506 OPEN c_relationship;
2507 FETCH c_relationship INTO l_relation;
2508 CLOSE c_relationship;
2509 END IF;
2510 RETURN l_relation;
2511 END get_contact_relationship;
2512
2513 --------------------------------------------------------------------------
2514 -- Name : GET_HIRE_DATE --
2515 -- Type : FUNCTION --
2516 -- Access : Public --
2517 -- Description : Gets the hiredate of a assignment --
2518 -- Parameters : --
2519 -- IN : p_assignment_id NUMBER --
2520 -- RETURN : DATE --
2521
2522 --------------------------------------------------------------------------
2523 FUNCTION get_hire_date(p_assignment_id NUMBER)
2524 RETURN DATE
2525 IS
2526 CURSOR csr_hire_date
2527 IS
2528 SELECT MAX(pos.date_start)
2529 FROM per_periods_of_service pos
2530 ,per_people_f ppf
2531 ,per_assignments_f paf
2532 WHERE pos.person_id = ppf.person_id
2533 AND ppf.person_id = paf.person_id
2534 AND pos.date_start between paf.effective_start_date and paf.effective_end_date
2535 AND paf.assignment_id = p_assignment_id;
2536
2537 l_hire_date DATE;
2538
2539 BEGIN
2540 OPEN csr_hire_date;
2541 FETCH csr_hire_date INTO l_hire_date;
2542 CLOSE csr_hire_date;
2543
2544 RETURN l_hire_date;
2545 END get_hire_date;
2546
2547 --------------------------------------------------------------------------
2548 -- Name : GET_POSITION_NAME --
2549 -- Type : FUNCTION --
2550 -- Access : Public --
2551 -- Description : Gets the position of a assignment --
2552 -- Parameters : --
2553 -- IN : p_assignment_id NUMBER --
2554 -- IN : p_effective_date DATE --
2555 -- RETURN : VARCHAR2 --
2556
2557 --------------------------------------------------------------------------
2558 FUNCTION get_position_name
2559 (p_assignment_id NUMBER
2560 ,p_effective_date DATE
2561 )
2562 RETURN VARCHAR2
2563 IS
2564 CURSOR csr_positions
2565 IS
2566 SELECT pos.name
2567 FROM per_positions pos,
2568 per_assignments_f asg
2569 WHERE pos.position_id = asg.position_id
2570 AND asg.assignment_id = p_assignment_id
2571 AND p_effective_date BETWEEN asg.effective_start_date
2572 AND asg.effective_end_date;
2573
2574 l_position per_positions.name%TYPE;
2575
2576 BEGIN
2577 OPEN csr_positions;
2578 FETCH csr_positions INTO l_position;
2579 CLOSE csr_positions;
2580
2581 RETURN l_position;
2582
2583 END get_position_name;
2584
2585 --------------------------------------------------------------------------
2586 -- Name : GET_AGE --
2587 -- Type : fUNCTION --
2588 -- Access : Public --
2589 -- Description : Gets the age of a person as on a date --
2590 -- Parameters : --
2591 -- IN : p_person_id NUMBER --
2592 -- : p_effective_date DATE --
2593 -- RETURN : NUMBER --
2594
2595 --------------------------------------------------------------------------
2596
2597 FUNCTION get_age(p_person_id in number
2598 ,p_effective_date in date)
2599 RETURN NUMBER
2600 IS
2601
2602 Cursor c_dob is
2603 select ppf.date_of_birth
2604 from per_people_f ppf
2605 where ppf.person_id = p_person_id
2606 and p_effective_date BETWEEN ppf.effective_start_date
2607 AND ppf.effective_end_date;
2608
2609 l_dob date;
2610 l_age number;
2611
2612 l_procedure VARCHAR2(250);
2613 l_message VARCHAR2(250);
2614 BEGIN
2615 g_debug := hr_utility.debug_enabled;
2616 l_procedure := g_package ||'get_age';
2617 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2618 IF (g_debug)
2619 THEN
2620 pay_in_utils.trace('**************************************************','********************');
2621 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2622 pay_in_utils.trace('p_person_id',p_person_id);
2623 pay_in_utils.trace('p_effective_date',p_effective_date);
2624 END IF;
2625
2626
2627 Open c_dob;
2628 Fetch c_dob into l_dob;
2629 Close c_dob;
2630
2631
2632 l_age := trunc((p_effective_date - l_dob)/365);
2633
2634 IF (g_debug)
2635 THEN
2636 pay_in_utils.trace('l_age',l_age);
2637 END IF;
2638
2639 pay_in_utils.trace('**************************************************','********************');
2640 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2641
2642 Return l_age;
2643
2644 END get_age;
2645
2646 --------------------------------------------------------------------------
2647 -- Name : GET_LTC_BLOCK --
2648 -- Type : fUNCTION --
2649 -- Access : Public --
2650 -- Description : Gets the current LTC Block --
2651 -- Parameters : --
2652 -- IN : p_effective_date DATE --
2653 -- RETURN : VARCHAR2 --
2654
2655 --------------------------------------------------------------------------
2656
2657 FUNCTION get_ltc_block
2658 (p_effective_date DATE
2659 )
2660 RETURN VARCHAR2
2661 IS
2662 CURSOR csr_ltc_block
2663 IS
2664 SELECT lookup_code
2665 FROM hr_lookups hrl
2666 WHERE HRL.lookup_type = 'IN_LTC_BLOCK'
2667 AND p_effective_date BETWEEN TO_DATE(SUBSTR(HRL.MEANING,1,11),'DD-MM-YYYY')
2668 AND TO_DATE(SUBSTR(HRL.MEANING,15,11),'DD-MM-YYYY');
2669
2670 l_ltc_block hr_lookups.lookup_code%TYPE;
2671
2672 BEGIN
2673 OPEN csr_ltc_block;
2674 FETCH csr_ltc_block INTO l_ltc_block;
2675 CLOSE csr_ltc_block;
2676 RETURN l_ltc_block;
2677
2678 END get_ltc_block;
2679
2680 --------------------------------------------------------------------------
2681 -- Name : GET_PREV_LTC_BLOCK --
2682 -- Type : fUNCTION --
2683 -- Access : Public --
2684 -- Description : Gets the previous LTC Block --
2685 -- Parameters : --
2686 -- IN : p_effective_date DATE --
2687 -- RETURN : VARCHAR2 --
2688
2689 --------------------------------------------------------------------------
2690
2691 FUNCTION get_prev_ltc_block
2692 (p_effective_date DATE
2693 )
2694 RETURN VARCHAR2
2695 IS
2696 CURSOR csr_ltc_block(l_effective_date DATE)
2697 IS
2698 SELECT lookup_code
2699 FROM hr_lookups hrl
2700 WHERE HRL.lookup_type = 'IN_LTC_BLOCK'
2701 AND l_effective_date BETWEEN TO_DATE(SUBSTR(HRL.MEANING,1,11),'DD-MM-YYYY')
2702 AND TO_DATE(SUBSTR(HRL.MEANING,15,11),'DD-MM-YYYY');
2703
2704 l_ltc_block hr_lookups.lookup_code%TYPE;
2705 l_effective_date DATE;
2706
2707 BEGIN
2708 l_effective_date := add_months(p_effective_date,-48);
2709
2710 OPEN csr_ltc_block(l_effective_date);
2711 FETCH csr_ltc_block INTO l_ltc_block;
2712 CLOSE csr_ltc_block;
2713 RETURN l_ltc_block;
2714
2715 END get_prev_ltc_block;
2716
2717 --------------------------------------------------------------------------
2718 -- Name : UPD_PUBLIC_SECTOR_PAY_PERIODS --
2719 -- Type : PROCEDURE --
2720 -- Access : Public --
2721 -- Description : Modifies the payroll time periods in accordance to --
2722 -- the public sector requirementi.e.. March salary to --
2723 -- be paid in April and remaining periods salary be --
2724 -- paid in same month. --
2725 -- Parameters : --
2726 -- OUT : p_errbuf NOCOPY VARCHAR2 --
2727 -- OUT : p_retcode NOCOPY NUMBER --
2728 -- IN : p_payroll_id NUMBER --
2729 -- IN : p_offset NUMBER --
2730 --------------------------------------------------------------------------
2731
2732 PROCEDURE UPD_PUBLIC_SECTOR_PAY_PERIODS(p_errbuf OUT NOCOPY VARCHAR2,
2733 p_retcode OUT NOCOPY NUMBER,
2734 p_payroll_id IN NUMBER,
2735 p_offset IN NUMBER) IS
2736
2737
2738 l_procedure VARCHAR2(250);
2739 l_payroll_actions NUMBER;
2740 l_public_sector_flag VARCHAR2(1);
2741 l_date_offset NUMBER;
2742
2743 BEGIN
2744
2745 l_procedure := g_package ||'UPD_PUBLIC_SECTOR_PAY_PERIODS';
2746 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
2747 g_debug := hr_utility.debug_enabled;
2748
2749 if g_debug then
2750 hr_utility.trace('---------------------------------------');
2751 hr_utility.trace('p_payroll_id :'||p_payroll_id);
2752 hr_utility.trace('p_offset : '||p_offset);
2753 end if ;
2754
2755
2756
2757 select count(1) into l_payroll_actions from pay_payroll_actions
2758 where payroll_id=p_payroll_id
2759 and action_type in ('R', 'Q', 'B', 'V');
2760
2761 if l_payroll_actions > 0 then
2762
2763 pay_in_utils.set_location(g_debug,'Inside: '||l_procedure, 20);
2764 hr_utility.set_location('Leaving: '||l_procedure,25);
2765
2766 fnd_message.set_name('PER','PER_IN_PAYROLL_ACTIONS_EXIST');
2767 fnd_message.set_token('PAYROLL_ID', p_payroll_id);
2768 fnd_file.put(fnd_file.log, FND_MESSAGE.GET);
2769 hr_utility.raise_error;
2770 end if;
2771
2772 select nvl(PRL_INFORMATION1,'N'),nvl(PAY_DATE_OFFSET,0) into l_public_sector_flag,l_date_offset
2773 from pay_payrolls_f
2774 where PAYROLL_ID=p_payroll_id
2775 and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
2776
2777 if l_public_sector_flag ='Y' then
2778
2779 pay_in_utils.set_location(g_debug,'Inside: '||l_procedure, 30);
2780
2781 if l_date_offset =0 then
2782 update per_time_periods
2783 set PERIOD_NAME= decode(to_char(end_date,'MM'),'03',1,'04',2,'05',3,'06',4,'07',5,'08',6,'09',7,'10',8,'11',9,'12',10,'01',11,'02',12)||' '||to_char(end_date,'YYYY')||' Calendar Month',
2784 PERIOD_NUM=decode(to_char(end_date,'MM'),'03',1,'04',2,'05',3,'06',4,'07',5,'08',6,'09',7,'10',8,'11',9,'12',10,'01',11,'02',12),
2785 REGULAR_PAYMENT_DATE=decode(to_char(end_date,'MM'),'03',end_date + p_offset,end_date)
2786 where PAYROLL_ID=p_payroll_id;
2787
2788 if g_debug then
2789 hr_utility.trace('---------------------------------------');
2790 hr_utility.trace('Period Definition is successfully modified for Public Sector Requirement for payroll id -'||p_payroll_id);
2791 hr_utility.trace('with an offset '||p_offset||' for March period.');
2792 end if ;
2793
2794 fnd_message.set_name('PER','PER_IN_PUBLIC_PAYROLL_SUCCESS');
2795 fnd_message.set_token('PAYROLL_ID', p_payroll_id);
2796 fnd_file.put(fnd_file.log, FND_MESSAGE.GET);
2797 pay_in_utils.set_location(g_debug,'Inside: '||l_procedure, 40);
2798 RETURN;
2799 else
2800 if g_debug then
2801 hr_utility.trace('---------------------------------------');
2802 hr_utility.trace('Period Definition is not modified for Public Sector Requirement for payroll id -'||p_payroll_id);
2803 hr_utility.trace('as offset is non-zero value for the payroll.');
2804 end if ;
2805
2806 pay_in_utils.set_location(g_debug,'Inside: '||l_procedure, 45);
2807 fnd_message.set_name('PER','PER_IN_DISALLOW_PERIOD_MODIFY');
2808 fnd_message.set_token('PAYROLL_ID', p_payroll_id);
2809 fnd_file.put(fnd_file.log, FND_MESSAGE.GET);
2810 hr_utility.raise_error;
2811 end if;
2812 else
2813 pay_in_utils.set_location(g_debug,'Inside: '||l_procedure, 50);
2814
2815 fnd_message.set_name('PER','PER_IN_PUBLIC_FLAG_UNSELECTED');
2816 fnd_message.set_token('PAYROLL_ID', p_payroll_id);
2817 fnd_file.put(fnd_file.log, FND_MESSAGE.GET);
2818 hr_utility.raise_error;
2819 end if;
2820
2821 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 60);
2822
2823 EXCEPTION
2824 WHEN OTHERS THEN
2825 p_errbuf := hr_utility.get_message;
2826 p_retcode := 2;
2827 END UPD_PUBLIC_SECTOR_PAY_PERIODS;
2828
2829 BEGIN
2830 g_package := 'pay_in_utils.';
2831
2832 END pay_in_utils;