[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_FF_PKG
Source
1 PACKAGE BODY pay_in_ff_pkg AS
2 /* $Header: pyindedn.pkb 120.13.12010000.3 2008/10/10 07:26:15 mdubasi ship $ */
3
4 g_debug BOOLEAN;
5 g_package CONSTANT VARCHAR2(100) := 'pay_in_ff_pkg.';
6 p_token_name pay_in_utils.char_tab_type;
7 p_token_value pay_in_utils.char_tab_type;
8
9 --------------------------------------------------------------------------
10 -- Name : CHECK_RETAINER --
11 -- Type : Function --
12 -- Access : Public --
13 -- Description : Function checks if the employee is excluded or --
14 -- or not. Returns 1 if excluded elso 0 --
15 -- --
16 -- --
17 -- Parameters : --
18 -- IN : p_assignment_id IN NUMBER --
19 -- p_payroll_action_id IN VARCHAR2 --
20 -- p_effective_date IN DATE --
21 -- --
22 -- Version Date Author Bug Description --
23 -- =====================================================================--
24 -- 1.0 15-Jun-04 ABHJAIN 3683543 Initial Version --
25 -- 1.1 02-Aug-04 VGSRINIV 3807912 Changed the lookup code for --
26 -- Employee Category and increased --
27 -- the size of l_emp_cat to 10 --
28 --------------------------------------------------------------------------
29 FUNCTION check_retainer (p_assignment_id IN NUMBER
30 ,p_payroll_action_id IN NUMBER)
31 RETURN NUMBER IS
32 CURSOR c_emp_cat IS
33 SELECT nvl(paa.employee_category,'X')
34 FROM per_all_assignments_f paa,
35 pay_payroll_actions ppa
36 WHERE paa.assignment_id = p_assignment_id
37 AND ppa.payroll_action_id = p_payroll_action_id
38 AND paa.payroll_id = ppa.payroll_id
39 AND ppa.effective_date BETWEEN paa.effective_start_date
40 AND paa.effective_end_date;
41
42 l_emp_cat VARCHAR2(10);
43 l_procedure VARCHAR2(100);
44 l_message VARCHAR2(1000);
45 BEGIN
46 g_debug:= hr_utility.debug_enabled;
47 l_procedure:= g_package ||'check_retainer';
48
49 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
50
51 IF g_debug THEN
52 pay_in_utils.trace('**************************************************','********************');
53 pay_in_utils.trace('p_assignment_id',p_assignment_id);
54 pay_in_utils.trace('p_payroll_action_id',p_payroll_action_id);
55 pay_in_utils.trace('**************************************************','********************');
56 END IF;
57 OPEN c_emp_cat;
58 FETCH c_emp_cat INTO l_emp_cat;
59 CLOSE c_emp_cat;
60
61 IF g_debug THEN
62 pay_in_utils.trace('l_emp_cat',l_emp_cat);
63 END IF;
64
65 IF (l_emp_cat = 'IN_RE') THEN
66 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
67 RETURN 1;
68 ELSE
69 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
70 RETURN 0;
71 END IF;
72
73 EXCEPTION
74 WHEN OTHERS THEN
75 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
76 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
77 hr_utility.trace(l_message);
78 RETURN NULL;
79 END check_retainer;
80
81 --------------------------------------------------------------------------
82 -- Name : CHECK_EDLI --
83 -- Type : Function --
84 -- Access : Public --
85 -- Description : Function finds if the EDLI option is Yes or No --
86 -- for the employer . --
87 -- --
88 -- Parameters : --
89 -- IN : p_assignment_id IN NUMBER --
90 -- p_effective_date IN DATE --
91 -- --
92 -- --
93 -- Version Date Author Bug Description --
94 -- =====================================================================--
95 -- 115.0 15-Jun-04 ABHJAIN 3683543 Initial Version --
96 -- --
97 --------------------------------------------------------------------------
98 FUNCTION check_edli(p_assignment_id IN NUMBER
99 ,p_effective_date IN DATE)
100 RETURN VARCHAR2 IS
101 CURSOR c_get_edli IS
102 SELECT hoi.org_information7
103 FROM hr_organization_information hoi,
104 hr_soft_coding_keyflex hsk,
105 per_all_assignments_f paa
106 WHERE hoi.org_information_context = 'PER_IN_PF_DF'
107 AND hoi.organization_id = hsk.segment2
108 AND hsk.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
109 AND paa.assignment_id = p_assignment_id
110 AND p_effective_date BETWEEN paa.effective_start_date
111 AND paa.effective_end_date;
112 l_procedure VARCHAR2(100);
113 l_edli VARCHAR2(1);
114 l_message VARCHAR2(1000);
115 BEGIN
116 g_debug:= hr_utility.debug_enabled;
117 l_procedure:= g_package ||'check_retainer';
118
119 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
120
121 IF g_debug THEN
122 pay_in_utils.trace('**************************************************','********************');
123 pay_in_utils.trace('p_assignment_id',p_assignment_id);
124 pay_in_utils.trace('p_effective_date',to_char(p_effective_date,'dd/mm/yyyy'));
125 pay_in_utils.trace('**************************************************','********************');
126 END IF;
127 OPEN c_get_edli;
128 FETCH c_get_edli INTO l_edli;
129 CLOSE c_get_edli;
130
131 IF g_debug THEN
132 pay_in_utils.trace('l_edli',l_edli);
133 END IF;
134
135 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,20);
136 RETURN l_edli;
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
141 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
142 hr_utility.trace(l_message);
143 RETURN NULL;
144 END check_edli;
145
146 --------------------------------------------------------------------------
147 -- Name : GET_ESI_CONT_AMT --
148 -- Type : Function --
149 -- Access : Public --
150 -- Description : Function finds the ESI Eligibile salary at the --
151 -- start of Contribution period/first pay period --
152 -- whichever falls later --
153 -- Parameters : --
154 -- IN : p_assignment_action_id IN NUMBER --
155 -- p_assignment_id IN NUMBER --
156 -- p_date_earned IN DATE --
157 -- p_eligible_amt IN NUMBER --
158 -- --
159 -- Version Date Author Bug Description --
160 -- =====================================================================--
161 -- 115.0 04-Aug-2004 lnagaraj 3723655 Initial Version --
162 -- 115.1 12-Aug-2004 lnagaraj Changed cusor get_date --
163 -- used _ASG_MTD instead of _ASG_RUN--
164 -- 115.2 25-Aug-2004 lnagaraj 3844554 Used _ASG_PTD instead of --
165 -- _ASG_MTD --
166 -- 115.3 23-Sep-2004 statkar 3861752 Support for +ve offsets --
167 -- 115.4 21-Oct-2005 lnagaraj 4680066 Added 'csr_bal_init' to --
168 -- consider balance init --
169 -- 115.5 07-Jun-2006 lnagaraj 6116283 Included last_day function --
170 --------------------------------------------------------------------------
171 FUNCTION get_esi_cont_amt(p_assignment_action_id IN NUMBER
172 ,p_assignment_id IN NUMBER
173 ,p_date_earned IN DATE
174 ,p_eligible_amt IN NUMBER
175 )
176 RETURN NUMBER IS
177
178 /* This cursor returns the defined balance id of 'ESI_ELIGIBLE_SALARY_ASG_PTD' */
179 CURSOR get_defined_bal_id IS
180 SELECT pdb.defined_balance_id
181 FROM pay_defined_balances pdb,
182 pay_balance_dimensions pbd,
183 pay_balance_types pbt
184 WHERE pbt.balance_name ='ESI Eligible Salary'
185 AND pbd.database_item_suffix='_ASG_PTD' /* Bugfix 3844554 */
186 AND pbt.legislation_code='IN'
187 AND pdb.balance_type_id =pbt.balance_type_id
188 AND pdb.balance_dimension_id =pbd.balance_dimension_id;
189
190 /* This cursor returns the latest of first pay period date and contribution period start date */
191 CURSOR get_date(l_start date) IS
192 SELECT GREATEST( MIN(ppa.date_earned),l_start)
193 FROM pay_payroll_actions ppa,
194 pay_assignment_actions paa,
195 per_all_assignments_f paf
196 WHERE paf.assignment_id =p_assignment_id
197 AND paf.assignment_id=paa.assignment_id
198 AND paa.payroll_action_id =ppa.payroll_action_id;
199
200 -- Cursor for Max Assignment Action ID for the contribution period
201 CURSOR csr_casact (l_virtual_date IN DATE) IS
202 SELECT paa.assignment_action_id
203 FROM pay_payroll_actions ppa
204 ,pay_assignment_actions paa
205 WHERE paa.payroll_action_id = ppa.payroll_action_id
206 AND paa.assignment_id = p_assignment_id
207 AND last_day(ppa.date_earned) = l_virtual_date
208 AND paa.source_action_id IS NULL
209 AND ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')-- Added as a part of bug fix 4774108
210 AND ppa.action_type in ('Q','R','I','B')
211 ORDER BY paa.action_sequence DESC ;
212
213
214 -- Store the contribution Start Periods
215 l_half_year_start1 VARCHAR2(7);
216 l_half_year_start2 VARCHAR2(7);
217
218 l_month NUMBER;
219 l_year NUMBER;
220 l_start DATE;
221 l_esi_cont_date DATE;
222 l_esi_contr_month DATE;
223 l_esi_eligible_amt NUMBER;
224 l_defined_balance_id NUMBER;
225 l_date_earned DATE;
226 l_start_period DATE;
227
228 l_procedure VARCHAR2(100);
229 l_virtual_asact_id NUMBER;
230 l_message VARCHAR2(1000);
231
232 BEGIN
233 g_debug:= hr_utility.debug_enabled;
234 l_procedure := g_package ||'get_esi_cont_amt';
235 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
236
237 l_half_year_start1:='01-04-';
238 l_half_year_start2:='01-10-';
239
240 IF g_debug THEN
241 pay_in_utils.trace('**************************************************','********************');
242 pay_in_utils.trace ('p_assignment_action_id ',to_char(p_assignment_action_id));
243 pay_in_utils.trace ('p_assignment_id ',to_char(p_assignment_id));
244 pay_in_utils.trace ('p_date_earned ',to_char(p_date_earned,'DD-MM-YYYY'));
245 pay_in_utils.trace ('p_eligible_amt ',to_char(p_eligible_amt));
246 pay_in_utils.trace('**************************************************','********************');
247 END IF;
248
249 l_month :=TO_NUMBER(TO_CHAR(p_date_earned,'mm'));
250 l_year := TO_NUMBER(TO_CHAR(p_date_earned,'yyyy'));
251
252 pay_in_utils.set_location(g_debug,l_procedure, 20);
253
254 IF l_month BETWEEN 4 AND 9 THEN
255 l_start := TO_DATE(l_half_year_start1||TO_CHAR(l_year),'dd-mm-yyyy');
256 ELSIF l_month BETWEEN 10 and 12 THEN
257 l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year),'dd-mm-yyyy');
258 ELSE
259 l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year-1),'dd-mm-yyyy');
260 END IF;
261
262 pay_in_utils.set_location(g_debug,l_procedure, 30);
263
264 OPEN get_date(l_start);
265 FETCH get_date INTO l_esi_cont_date;
266 CLOSE get_date;
267
268 pay_in_utils.set_location(g_debug,l_procedure, 40);
269
270 --Get the last date of the month to be considered for ESI Eligibility
271 l_esi_contr_month := last_day(l_esi_cont_date);
272
273 --Get the last date of the current pay period
274 l_start_period := last_day(p_date_earned);
275
276 IF g_debug THEN
277 pay_in_utils.trace ('l_esi_contr_month : ' , to_char(l_esi_contr_month,'DD-MM-YYYY'));
278 pay_in_utils.trace ('l_start_period : ' , to_char(l_start_period,'DD-MM-YYYY'));
279 END IF;
280
281 pay_in_utils.set_location(g_debug,l_procedure, 50);
282
283 --
284 -- IF Current month is to considered for finding ESI Eligibility
285 -- THEN return back the input value(p_eligible_amt)
286 -- ELSE
287 -- Call pay_balance_pkg.get_value
288 --
289
290 IF l_start_period =l_esi_contr_month THEN
291
292 l_esi_eligible_amt :=p_eligible_amt;
293 pay_in_utils.set_location(g_debug,l_procedure, 60);
294
295 ELSE
296 pay_in_utils.set_location(g_debug,l_procedure, 70);
297 OPEN get_defined_bal_id;
298 FETCH get_defined_bal_id INTO l_defined_balance_id;
299 CLOSE get_defined_bal_id;
300
301
302 OPEN csr_casact (l_esi_contr_month);
303 FETCH csr_casact
304 INTO l_virtual_asact_id;
305 CLOSE csr_casact;
306
307
308 pay_in_utils.set_location(g_debug,'Virtual ASACT ID : '||to_char(l_virtual_asact_id),80);
309
310 l_esi_eligible_amt := pay_balance_pkg.get_value(
311 p_defined_balance_id => l_defined_balance_id,
312 p_assignment_action_id => l_virtual_asact_id
313 );
314
315 pay_in_utils.set_location(g_debug,l_procedure, 80);
316
317 END IF;
318
319 IF g_debug THEN
320 pay_in_utils.trace ('l_esi_eligible_amt : ' , l_esi_eligible_amt);
321 END IF;
322
323 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
324 RETURN l_esi_eligible_amt;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
329 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 100);
330 hr_utility.trace(l_message);
331 RETURN NULL;
332 END get_esi_cont_amt;
333
334
335 --------------------------------------------------------------------------
336 -- Name : GET_ESI_DISABILITY_DETAILS --
337 -- Type : Function --
338 -- Access : Public --
339 -- Description : Function finds the Disability Details at the --
340 -- start of Contribution period/first pay period --
341 -- whichever falls later --
342 -- Parameters : --
343 -- IN : p_assignment_id IN NUMBER --
344 -- p_date_earned IN DATE --
345 -- --
346 -- Version Date Author Bug Description --
347 -- =====================================================================--
348 -- 115.0 04-Sep-2008 mdubasi 7357358 Initial Version --
349 --------------------------------------------------------------------------
350 FUNCTION get_esi_disability_details( p_assignment_id in number
351 ,p_date_earned in date
352 ,p_disable_proof out NOCOPY varchar2)
353 Return Number is
354
355 /* This cursor returns the Disable Proof */
356 Cursor c_disab_details(l_esi_cont_date date) is
357 select pdf.dis_information1
358 from per_disabilities_f pdf,
359 per_all_assignments_f paa
360 where paa.assignment_id = p_assignment_id
361 and paa.person_id = pdf.person_id
362 and l_esi_cont_date between paa.effective_start_date and paa.effective_end_date
363 and l_esi_cont_date between pdf.effective_start_date and pdf.effective_end_date
364 order by nvl(pdf.dis_information1,'N') desc;
365
366 /* This cursor returns the latest of first pay period date and contribution period start date */
367 CURSOR get_date(l_start date) IS
368 SELECT GREATEST( MIN(ppa.date_earned),l_start)
369 FROM pay_payroll_actions ppa,
370 pay_assignment_actions paa,
371 per_all_assignments_f paf
372 WHERE paf.assignment_id =p_assignment_id
373 AND paf.assignment_id=paa.assignment_id
374 AND paa.payroll_action_id =ppa.payroll_action_id;
375
376 -- Store the contribution Start Periods
377 l_half_year_start1 VARCHAR2(7);
378 l_half_year_start2 VARCHAR2(7);
379
380 l_month NUMBER;
381 l_year NUMBER;
382 l_start DATE;
383 l_esi_cont_date DATE;
384 l_proof Varchar2(10);
385 l_procedure VARCHAR2(250);
386 l_message VARCHAR2(250);
387
388 BEGIN
389 g_debug := hr_utility.debug_enabled;
390 l_procedure := g_package ||'get_esi_disability_details';
391 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
392
393 l_half_year_start1:='01-04-';
394 l_half_year_start2:='01-10-';
395
396 IF g_debug THEN
397 pay_in_utils.trace('**************************************************','********************');
398 pay_in_utils.trace ('p_assignment_id ',to_char(p_assignment_id));
399 pay_in_utils.trace ('p_date_earned ',to_char(p_date_earned,'DD-MM-YYYY'));
400 pay_in_utils.trace('**************************************************','********************');
401 END IF;
402
403 l_month := TO_NUMBER(TO_CHAR(p_date_earned,'mm'));
404 l_year := TO_NUMBER(TO_CHAR(p_date_earned,'yyyy'));
405
406 pay_in_utils.set_location(g_debug,l_procedure, 20);
407
408 IF l_month BETWEEN 4 AND 9 THEN
409 l_start := TO_DATE(l_half_year_start1||TO_CHAR(l_year),'dd-mm-yyyy');
410 ELSIF l_month BETWEEN 10 and 12 THEN
411 l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year),'dd-mm-yyyy');
412 ELSE
413 l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year-1),'dd-mm-yyyy');
414 END IF;
415
416 pay_in_utils.set_location(g_debug,l_procedure, 30);
417
418 /*To get latest of first pay period date and contribution period start date*/
419
420 OPEN get_date(l_start);
421 FETCH get_date INTO l_esi_cont_date;
422 CLOSE get_date;
423
424 pay_in_utils.set_location(g_debug,l_procedure, 40);
425
426 IF g_debug THEN
427 pay_in_utils.trace ('l_esi_cont_date : ' , to_char(l_esi_cont_date,'DD-MM-YYYY'));
428 END IF;
429
430 pay_in_utils.set_location(g_debug,l_procedure, 50);
431
432 l_proof := 'N';
433
434 Open c_disab_details(l_esi_cont_date);
435 Fetch c_disab_details into l_proof;
436 Close c_disab_details;
437
438 p_disable_proof := l_proof;
439
440 pay_in_utils.set_location(g_debug,l_procedure, 60);
441
442
443 IF g_debug THEN
444 pay_in_utils.trace('p_disable_proof',p_disable_proof);
445 END IF;
446
447 pay_in_utils.set_location(g_debug,l_procedure, 70);
448
449
450 pay_in_utils.trace('**************************************************','********************');
451 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
452 Return 0;
453
454 END get_esi_disability_details;
455
456
457 --------------------------------------------------------------------------
458 -- Name : ROUND_TO_5PAISE --
459 -- Type : Function --
460 -- Access : Public --
461 -- Description : Function to round to next higher multiple of 5 paise--
462 -- Parameters : --
463 -- IN : p_number IN NUMBER --
464 -- Version Date Author Bug Description --
465 -- =====================================================================--
466 -- 115.0 04-Aug-2004 lnagaraj 3723655 Initial Version --
467 -- 115.1 25-Aug-2004 lnagaraj 3849905 Rounded the number to 2 places--
468 -- before rounding to the next --
469 -- five paise --
470
471 --------------------------------------------------------------------------
472
473 FUNCTION round_to_5paise( p_number in number)
474 RETURN NUMBER IS
475 n NUMBER;
476 l_number NUMBER;
477 l_procedure VARCHAR2(100);
478 BEGIN
479 g_debug := hr_utility.debug_enabled;
480 l_procedure := g_package ||'round_to_5paise';
481 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
482
483
484 IF g_debug THEN
485 pay_in_utils.trace('p_number',to_char(p_number));
486 END IF;
487
488 l_number := ROUND(p_number,2);
489 N := CEIL (l_number*10)/10;
490
491 IF g_debug THEN
492 pay_in_utils.trace('N',to_char(N));
493 END IF;
494
495 IF (N-l_number) >= 0.05 THEN
496 RETURN (N-0.05);
497 ELSE
498 RETURN N;
499 END IF;
500
501 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
502
503 END ROUND_TO_5PAISE;
504
505 --------------------------------------------------------------------------
506 -- Name : GET_ACCRUAL_PLAN --
507 -- Type : Function --
508 -- Access : Private --
509 -- Description : Function to fetch the Accrual Plan id of the --
510 -- accrual category passed --
511 -- IN : p_assignment_id NUMBER --
512 -- p_effective_date DATE --
513 -- p_plan_category VARCHAR2 --
514 -- OUT : p_message VARCHAR2 --
515 -- plan_id NUMBER --
516 -- --
517 -- Version Date Author Bug Description --
518 -- =====================================================================--
519 -- 115.0 19-Oct-2004 Vgsriniv 3847355 Initial Version --
520
521 --------------------------------------------------------------------------
522 FUNCTION get_accrual_plan ( p_assignment_id IN NUMBER
523 ,p_effective_date IN DATE
524 ,p_plan_category IN VARCHAR2
525 ,p_message OUT NOCOPY VARCHAR2
526 )
527 RETURN NUMBER
528 IS
529
530 l_accrual_plan_id NUMBER := NULL;
531 l_dummy NUMBER ;
532 l_procedure VARCHAR2(100);
533 l_message VARCHAR2(1000);
534
535 CURSOR csr_get_accrual_plan_id(p_assignment_id NUMBER
536 ,p_effective_date DATE
537 ,p_plan_category VARCHAR2) IS
538 SELECT pap.accrual_plan_id
539 FROM pay_accrual_plans pap,
540 pay_element_entries_f pee,
541 pay_element_links_f pel,
542 pay_element_types_f pet
543 WHERE pee.assignment_id = p_assignment_id
544 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
545 AND pel.element_link_id = pee.element_link_id
546 AND pel.element_type_id = pet.element_type_id
547 AND pap.accrual_plan_element_type_id = pet.element_type_id
548 AND pap.accrual_category = p_plan_category ;
549
550 BEGIN
551 g_debug := hr_utility.debug_enabled;
552 l_procedure := g_package ||'get_accrual_plan';
553 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
554
555
556 IF g_debug THEN
557 pay_in_utils.trace('**************************************************','********************');
558 pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id ));
559 pay_in_utils.trace('p_effective_date',to_char(p_effective_date));
560 pay_in_utils.trace('p_plan_category ',to_char(p_plan_category ));
561 pay_in_utils.trace('**************************************************','********************');
562 END IF;
563
564 OPEN csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
565 FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
566
567 IF csr_get_accrual_plan_id%NOTFOUND
568 THEN
569 p_message := 'ERROR';
570 CLOSE csr_get_accrual_plan_id;
571 ELSE
572 p_message := 'SUCCESS';
573 CLOSE csr_get_accrual_plan_id;
574 END IF ;
575
576 IF g_debug THEN
577 pay_in_utils.trace('**************************************************','********************');
578 pay_in_utils.trace('p_message ',p_message);
579 pay_in_utils.trace('l_accrual_plan_id ',to_char(l_accrual_plan_id ));
580 pay_in_utils.trace('**************************************************','********************');
581 END IF;
582
583 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
584
585 RETURN l_accrual_plan_id;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
590 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
591 hr_utility.trace(l_message);
592 RETURN NULL;
593
594 END get_accrual_plan;
595
596 --------------------------------------------------------------------------
597 -- Name : GET_NET_ACCRUAL --
598 -- Type : Function --
599 -- Access : Public --
600 -- Description : Function to fetch the accrued leave balance as of --
601 -- the date passed --
602 -- IN : p_assignment_id NUMBER --
603 -- p_payroll_id NUMBER --
604 -- p_business_group_id NUMBER --
605 -- p_calculation_date DATE --
606 -- p_plan_category VARCHAR2 --
607 -- OUT : p_message VARCHAR2 --
608 -- accrual NUMBER --
609 -- --
610 -- Version Date Author Bug Description --
611 -- =====================================================================--
612 -- 115.0 19-Oct-2004 Vgsriniv 3847355 Initial Version --
613
614 --------------------------------------------------------------------------
615
616 FUNCTION get_net_accrual ( p_assignment_id IN NUMBER
617 ,p_payroll_id IN NUMBER
618 ,p_business_group_id IN NUMBER
619 ,p_calculation_date IN DATE
620 ,p_plan_category IN VARCHAR2
621 ,p_message OUT NOCOPY VARCHAR2
622 )
623 RETURN NUMBER
624 IS
625 l_plan_id NUMBER;
626 l_accrued_leave NUMBER := NULL;
627 l_start_date DATE := NULL;
628 l_end_date DATE := NULL;
629 l_accrual_end_date DATE := NULL;
630 l_accrual NUMBER := NULL;
631 l_procedure VARCHAR2(100);
632 l_message VARCHAR2(1000);
633
634 BEGIN
635
636 g_debug:= hr_utility.debug_enabled;
637 l_procedure := g_package ||'get_net_accrual';
638 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
639
640 IF g_debug THEN
641 pay_in_utils.trace('**************************************************','********************');
642 pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id ));
643 pay_in_utils.trace('p_payroll_id ',to_char(p_payroll_id ));
644 pay_in_utils.trace('p_business_group_id',to_char(p_business_group_id));
645 pay_in_utils.trace('p_calculation_date ',to_char(p_calculation_date,'yyyy-mm-dd'));
646 pay_in_utils.trace('p_plan_category ',p_plan_category);
647 pay_in_utils.trace('**************************************************','********************');
648
649 END IF;
650
651 l_plan_id := get_accrual_plan ( p_assignment_id => p_assignment_id
652 ,p_effective_date => p_calculation_date
653 ,p_plan_category => p_plan_category
654 ,p_message => p_message
655 );
656
657 per_accrual_calc_functions.get_net_accrual(
658 p_assignment_id => p_assignment_id
659 ,p_plan_id => l_plan_id
660 ,p_payroll_id => p_payroll_id
661 ,p_business_group_id => p_business_group_id
662 ,p_calculation_date => p_calculation_date
663 ,p_start_date => l_start_date
664 ,p_end_date => l_end_date
665 ,p_accrual_end_date => l_accrual_end_date
666 ,p_accrual => l_accrual
667 ,p_net_entitlement => l_accrued_leave) ;
668
669 IF g_debug THEN
670 pay_in_utils.trace('**************************************************','********************');
671 pay_in_utils.trace('l_accrued_leave ',to_char(l_accrued_leave));
672 pay_in_utils.trace('p_message ',p_message);
673 pay_in_utils.trace('**************************************************','********************');
674 END IF;
675 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
676 RETURN l_accrued_leave;
677 EXCEPTION
678 WHEN OTHERS THEN
679 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
680 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
681 hr_utility.trace(l_message);
682 RETURN NULL;
683
684
685 END get_net_accrual;
686 --------------------------------------------------------------------------
687 -- Name : GET_PERIOD_NUMBER --
688 -- Type : Function --
689 -- Access : Public --
690 -- Description : Function to fetch the period number of the date --
691 -- passed --
692 -- IN : p_payroll_id NUMBER --
693 -- p_date DATE --
694 -- OUT : Period Number NUMBER --
695 -- --
696 -- Version Date Author Bug Description --
697 -- =====================================================================--
698 -- 115.0 19-Oct-2004 Vgsriniv 3847355 Initial Version --
699 -- 115.1 12-May-2005 rpalli 3919203 Modified code to support --
700 -- offset payrolls --
701 --------------------------------------------------------------------------
702
703 FUNCTION get_period_number (p_payroll_id IN NUMBER
704 ,p_term_date IN DATE )
705 RETURN NUMBER IS
706
707 CURSOR c_period_number IS
708 SELECT decode(to_char(TPERIOD.end_date,'MM'),'04',1,'05',2,'06',3,
709 '07',4,'08',5,'09',6,
710 '10',7,'11',8,'12',9,
711 '01',10,'02',11,'03',12)
712 FROM per_time_periods TPERIOD,
713 per_time_period_types TPTYPE
714 WHERE TPERIOD.payroll_id = p_payroll_id
715 AND TPTYPE.period_type = TPERIOD.period_type
716 AND p_term_date between TPERIOD.start_date and TPERIOD.end_date;
717
718 l_period_num NUMBER;
719 l_procedure VARCHAR2(100);
720 l_message VARCHAR2(1000);
721
722 BEGIN
723 g_debug := hr_utility.debug_enabled;
724 l_procedure := g_package ||'get_period_number';
725 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
726
727
728 IF g_debug THEN
729 pay_in_utils.trace('**************************************************','********************');
730 pay_in_utils.trace('p_payroll_id',to_char(p_payroll_id ));
731 pay_in_utils.trace('p_term_date ',to_char(p_term_date,'yyyy-mm-dd'));
732 pay_in_utils.trace('**************************************************','********************');
733 END IF;
734
735 l_period_num := 12;
736
737 OPEN c_period_number;
738 FETCH c_period_number INTO l_period_num;
739 CLOSE c_period_number;
740
741 IF g_debug THEN
742 pay_in_utils.trace('l_period_num',to_char(l_period_num ));
743 END IF;
744
745 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
746
747 RETURN l_period_num;
748
749 EXCEPTION
750 WHEN OTHERS THEN
751 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
752 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
753 hr_utility.trace(l_message);
754 RETURN NULL;
755
756
757 END get_period_number;
758
759 --------------------------------------------------------------------------
760 -- Name : SEC_80DD_PERCENT --
761 -- Type : Function --
762 -- Access : Public --
763 -- Description : Function to check if Sec 80 DD element is attached --
764 -- with 80-100 percent disability --
765 -- IN : p_assignment_id NUMBER --
766 -- p_date DATE --
767 -- OUT : Y/N VARCHAR2 --
768 -- --
769 -- Version Date Author Bug Description --
770 -- ========|============|=======|========|==================================--
771 -- 115.0 04-Nov-2004 Vgsriniv 3936280 Initial Version --
772
773 --------------------------------------------------------------------------
774
775 FUNCTION sec_80dd_percent ( p_assignment_id IN per_all_assignments_f.assignment_id%type
776 ,p_date_earned IN date)
777 RETURN VARCHAR2 IS
778
779 CURSOR c_80dd_80_percent is
780 SELECT count (*)
781 FROM pay_element_entry_values_f pev,
782 pay_element_entries_f pee,
783 pay_element_types_f pet,
784 pay_input_values_f piv
785 WHERE pet.element_name like 'Deduction under Section 80DD'
786 AND pet.legislation_code = 'IN'
787 AND pet.element_type_id = piv.element_type_id
788 AND piv.name = 'Disability Percentage'
789 AND piv.input_value_id = pev.input_value_id
790 AND pev.screen_entry_value = '80100'
791 AND pev.element_entry_id = pee.element_entry_id
792 AND pee.assignment_id = p_assignment_id
793 AND pee.element_type_id = pet.element_type_id
794 AND pee.entry_type = 'E'
795 AND p_date_earned BETWEEN pev.effective_start_date AND pev.effective_end_date
796 AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
797 AND p_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
798 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
799
800 l_80_percent number;
801 l_procedure varchar2(100);
802
803 BEGIN
804 g_debug := hr_utility.debug_enabled;
805 l_procedure := g_package ||'sec_80dd_percent';
806 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
807
808
809 IF g_debug THEN
810 pay_in_utils.trace('**************************************************','********************');
811 pay_in_utils.trace('p_assignment_id',to_char(p_assignment_id ));
812 pay_in_utils.trace('p_date_earned ',to_char(p_date_earned,'yyyy-mm-dd'));
813 pay_in_utils.trace('**************************************************','********************');
814 END IF;
815
816 OPEN c_80dd_80_percent;
817 FETCH c_80dd_80_percent INTO l_80_percent;
818 CLOSE c_80dd_80_percent;
819
820
821 IF g_debug THEN
822 pay_in_utils.trace('l_80_percent',l_80_percent);
823 END IF;
824
825 IF l_80_percent > 0 THEN
826 RETURN 'Y';
827 ELSE
828 RETURN 'N';
829 END IF;
830
831 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
832
833 END sec_80dd_percent;
834 --------------------------------------------------------------------------
835 -- --
836 -- Name : check_father_husband_name --
837 -- Type : PROCEDURE --
838 -- Access : Public --
839 -- Description : Procedure to check contact details as the case maybe--
840 -- Parameters : --
841 -- IN : p_effective_date DATE --
842 -- p_assignment_id NUMBER --
843 -- p_calling_procedure VARCHAR2 --
844 -- OUT: --
845 -- p_message_name VARCHAR2 --
846 -- p_token_value VARCHAR2 --
847 -- --
848 -- Change History : --
849 --------------------------------------------------------------------------
850 -- Rev# Date Userid Description --
851 --------------------------------------------------------------------------
852 -- 1.0 01-Jan-05 aaagarwa Created this procedure --
853 -- 1.1 25-Jul-05 snekkala Modified to make father name mandatory of --
854 -- gender and spouse mandatory if gender is --
855 -- Female and Marital Status is Married --
856 -- 1.2 25-Jul-05 snekkala Removed GSCC Errors --
857 --------------------------------------------------------------------------
858 PROCEDURE check_father_husband_name
859 (p_assignment_id IN NUMBER
860 ,p_effective_date IN DATE
861 ,p_message_name OUT NOCOPY VARCHAR2
862 ,p_token_value OUT NOCOPY VARCHAR2)
863 IS
864 /* Cursor to find the contact details */
865 CURSOR csr_contacts
866 IS
867 SELECT DISTINCT(DECODE(pcr.contact_type,'JP_FT','Father','F','Father','S','Spouse')) relation_type
868 , ppf.sex sex
869 , ppf.marital_status marital_status
870 FROM per_people_f ppf
871 , per_assignments_f paf
872 , per_contact_relationships pcr
873 WHERE paf.assignment_id = p_assignment_id
874 AND paf.person_id = ppf.person_id
875 AND pcr.person_id = ppf.person_id
876 AND pcr.contact_type IN ('JP_FT','F',DECODE(ppf.marital_status,'M','S'))
877 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
878 AND p_effective_date BETWEEN paf.effective_start_date AND ppf.effective_end_date
879 AND p_effective_date BETWEEN pcr.date_start AND NVL(pcr.date_end,TO_DATE('4712/12/31','YYYY/MM/DD'))
880 ORDER BY DECODE(pcr.contact_type,'JP_FT','Father','F','Father','S','Spouse');
881
882 l_sex VARCHAR2(10);
883 l_name VARCHAR2(100);
884 l_procedure VARCHAR2(100);
885 l_marital_status VARCHAR2(10);
886 l_father_exists NUMBER;
887 l_spouse_exists NUMBER;
888
889 BEGIN
890
891 g_debug := hr_utility.debug_enabled ;
892 l_procedure := g_package||'check_father_husband_name' ;
893 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
894
895 IF g_debug THEN
896 pay_in_utils.trace('**************************************************','********************');
897 pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id));
898 pay_in_utils.trace('p_effective_date ',to_char(p_effective_date,'yyyy-mm-dd'));
899 pay_in_utils.trace('**************************************************','********************');
900 END IF;
901
902 p_message_name := 'SUCCESS';
903 p_token_value:=NULL;
904
905 l_father_exists :=0;
906 l_spouse_exists :=0;
907
908 FOR i in csr_contacts
909 LOOP
910 IF i.sex ='F' AND i.marital_status = 'M' AND i.relation_type ='Spouse' THEN
911 l_spouse_exists := 1;
912 ELSIF i.relation_type = 'Father' THEN
913 l_father_exists := 1;
914 END IF;
915 l_sex := i.sex;
916 l_marital_status := i.marital_status;
917 END LOOP;
918
919 IF l_father_exists = 0 THEN
920 p_message_name := 'PER_IN_CONTACT_DETAILS';
921 p_token_value := 'Father';
922 ELSIF l_sex = 'F' AND l_marital_status = 'M' AND l_spouse_exists = 0 THEN
923 p_message_name := 'PER_IN_CONTACT_DETAILS';
924 p_token_value := 'Spouse';
925 END IF;
926
927 IF g_debug THEN
928 pay_in_utils.trace('**************************************************','********************');
929 pay_in_utils.trace('p_message_name ',p_message_name);
930 pay_in_utils.trace('p_token_value ',p_token_value);
931 pay_in_utils.trace('**************************************************','********************');
932 END IF;
933 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
934 RETURN;
935
936 END check_father_husband_name;
937 --------------------------------------------------------------------------
938 -- --
939 -- Name : CHECK_GRE_UPDATE --
940 -- Type : PROCEDURE --
941 -- Access : Public --
942 -- Description : Procedure to validate GRE/Legal Entity Changes --
943 -- Parameters : --
944 -- IN : p_effective_date DATE --
945 -- p_dt_mode VARCHAR2 --
946 -- p_assignment_id NUMBER --
947 -- p_gre_org VARCHAR2 --
948 -- OUT : p_message VARCHAR2 --
949 -- Change History : --
950 --------------------------------------------------------------------------
951 -- Rev# Date Userid Description --
952 --------------------------------------------------------------------------
953 PROCEDURE check_gre_update
954 (p_effective_date IN DATE
955 ,p_dt_mode IN VARCHAR2
956 ,p_assignment_id IN NUMBER
957 ,p_gre_org IN VARCHAR2
958 ,p_pf_org IN VARCHAR2
959 ,p_esi_org IN VARCHAR2
960 ,p_gre IN NUMBER
961 ,p_pf IN NUMBER
962 ,p_esi IN NUMBER
963 ,p_message OUT NOCOPY VARCHAR2
964 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
965 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
966 )
967 IS
968 -- The cursor to obtain the maximum payroll run date for an assignment in a BG.
969 CURSOR c_max_pay_date
970 IS
971 SELECT ppa.date_earned
972 FROM pay_payroll_actions ppa
973 ,pay_assignment_actions paa
974 WHERE ppa.payroll_action_id = paa.payroll_action_id
975 AND ppa.action_type IN ('Q','R')
976 AND ppa.action_status = 'C'
977 AND paa.source_action_id IS NULL
978 AND ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
979 AND paa.assignment_id = p_assignment_id
980 ORDER BY ppa.date_earned DESC;
981 -- The cursor to obtain start and end date of the effective date's month.
982 CURSOR c_payroll_month_dates
983 IS
984 SELECT ADD_MONTHS(LAST_DAY(p_effective_date),-1)+1
985 ,LAST_DAY(p_effective_date)
986 FROM dual;
987
988
989 -- The cursor to find out the total no of GRE/Legal entity chnages in a given month.
990 CURSOR c_gre_changes(p_start_date DATE
991 ,p_end_date DATE
992 )
993 IS
994 SELECT COUNT(DISTINCT scl.segment1)
995 FROM per_all_assignments_f asg
996 ,hr_soft_coding_keyflex scl
997 WHERE asg.assignment_id = p_assignment_id
998 AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
999 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1000 AND ( asg.effective_start_date BETWEEN p_start_date AND p_end_date
1001 OR
1002 asg.effective_end_date BETWEEN p_start_date AND p_end_date
1003 );
1004
1005 -- The cursor to find the organization id of the GRE/Legal entity id
1006 CURSOR c_org_id(p_org_name hr_organization_units.name%type)
1007 IS
1008 SELECT organization_id
1009 FROM hr_organization_units
1010 WHERE NAME = p_org_name
1011 AND business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1012
1013 -- The cursor to find the most recent 'assignment start date' for the effective date
1014 CURSOR c_asg_start_date
1015 IS
1016 SELECT asg.effective_start_date
1017 FROM per_all_assignments_f asg
1018 WHERE p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1019 AND asg.assignment_id = p_assignment_id;
1020
1021 -- The cursor to find the presence of an employee in the now selected GRE/Legal
1022 -- entity id in earlier part of the current month.
1023 CURSOR c_flag(p_latest_org_id NUMBER
1024 ,p_start_date DATE
1025 ,p_end_date DATE
1026
1027 )
1028 IS
1029 SELECT 1
1030 FROM per_all_assignments_f asg
1031 ,hr_soft_coding_keyflex scl
1032 WHERE asg.assignment_id = p_assignment_id
1033 AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1034 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1035 AND scl.segment1 = p_latest_org_id
1036 AND ( asg.effective_start_date BETWEEN p_start_date AND p_end_date
1037 OR
1038 asg.effective_end_date BETWEEN p_start_date AND p_end_date
1039 );
1040
1041 l_max_date_earned DATE;
1042 l_start_date DATE;
1043 l_end_date DATE;
1044 l_org_id NUMBER;
1045 l_gre_org_id NUMBER;
1046 l_pf_org_id NUMBER;
1047 l_esi_org_id NUMBER;
1048 l_count NUMBER;
1049 l_le_start_date DATE;
1050 l_le_end_date DATE;
1051 l_flag NUMBER;
1052 l_asg_start_date DATE;
1053 l_procedure varchar2(100);
1054
1055 BEGIN
1056 g_debug := hr_utility.debug_enabled;
1057 l_procedure := g_package ||'check_gre_update';
1058 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1059
1060
1061 IF g_debug THEN
1062 pay_in_utils.trace('**************************************************','********************');
1063 pay_in_utils.trace('p_effective_date',to_char(p_effective_date,'yyyy-mm-dd'));
1064 pay_in_utils.trace('p_dt_mode ',p_dt_mode);
1065 pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id));
1066 pay_in_utils.trace('p_gre_org ',p_gre_org);
1067 pay_in_utils.trace('p_pf_org ',p_pf_org );
1068 pay_in_utils.trace('p_esi_org ',p_esi_org);
1069 pay_in_utils.trace('p_gre ',to_char(p_gre));
1070 pay_in_utils.trace('p_pf ',to_char(p_pf));
1071 pay_in_utils.trace('p_esi ',to_char(p_esi));
1072 pay_in_utils.trace('**************************************************','********************');
1073 END IF;
1074
1075 OPEN c_max_pay_date;
1076 FETCH c_max_pay_date INTO l_max_date_earned;
1077 CLOSE c_max_pay_date;
1078
1079 OPEN c_asg_start_date;
1080 FETCH c_asg_start_date INTO l_asg_start_date;
1081 CLOSE c_asg_start_date;
1082
1083
1084
1085 OPEN c_payroll_month_dates;
1086 FETCH c_payroll_month_dates INTO l_start_date,l_end_date;
1087 CLOSE c_payroll_month_dates;
1088
1089 OPEN c_org_id(p_gre_org);--Current GRE Organization
1090 FETCH c_org_id INTO l_gre_org_id;
1091 CLOSE c_org_id;
1092
1093 OPEN c_org_id(p_pf_org);--Current PF Organization
1094 FETCH c_org_id INTO l_pf_org_id;
1095 IF c_org_id%NOTFOUND THEN
1096 l_pf_org_id := -99;
1097 END IF;
1098 CLOSE c_org_id;
1099
1100 OPEN c_org_id(p_esi_org);--Current ESI Organization
1101 FETCH c_org_id INTO l_esi_org_id;
1102 IF c_org_id%NOTFOUND THEN
1103 l_esi_org_id := -99;
1104 END IF;
1105 CLOSE c_org_id;
1106
1107 OPEN c_gre_changes(l_start_date,l_end_date);--Total Changes in the GRE
1108 FETCH c_gre_changes INTO l_count;
1109 CLOSE c_gre_changes;
1110
1111 p_message := 'SUCCESS';
1112
1113
1114 IF (l_max_date_earned > l_asg_start_date)
1115 THEN
1116 IF (p_gre IS NOT NULL) AND (p_gre <> l_gre_org_id)
1117 THEN
1118 p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1119 ELSIF (p_pf IS NOT NULL) AND (p_pf <> l_pf_org_id)
1120 THEN
1121 p_message := 'PER_IN_SCL_CHANGE_FORBIDDEN';
1122 p_token_name(1) := 'ORG';
1123 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PF_ORG');
1124 ELSIF (p_esi IS NOT NULL) AND (p_esi <> l_esi_org_id)
1125 THEN
1126 hr_utility.trace('ven_Inside ESI Error message condition');
1127 p_message := 'PER_IN_SCL_CHANGE_FORBIDDEN';
1128 p_token_name(1) := 'ORG';
1129 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','ESI_ORG');
1130 ELSE
1131 IF g_debug THEN
1132 pay_in_utils.trace('p_message ',p_message);
1133 END IF;
1134 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1135 RETURN;
1136 END IF;
1137 ELSIF (l_count > 2)
1138 THEN
1139 p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1140 ELSIF (l_count = 2)
1141 THEN
1142 l_le_start_date := pay_in_tax_utils.le_start_date(l_org_id,p_assignment_id,p_effective_date);
1143 l_le_end_date := pay_in_tax_utils.le_end_date(l_org_id,p_assignment_id,p_effective_date);
1144
1145 OPEN c_flag(l_org_id,l_start_date,l_le_start_date - 1);
1146 FETCH c_flag INTO l_flag;
1147 CLOSE c_flag;
1148
1149 IF (l_flag IS NULL)
1150 THEN
1151 OPEN c_flag(l_org_id,l_le_end_date + 1,l_end_date);
1152 FETCH c_flag INTO l_flag;
1153 CLOSE c_flag;
1154
1155 IF (l_flag IS NULL) THEN
1156 IF g_debug THEN
1157 pay_in_utils.trace('p_message ',p_message );
1158 END IF;
1159 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,30);
1160 RETURN;
1161 ELSE
1162 p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1163 END IF;
1164 ELSE
1165 p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1166 END IF;
1167 ELSE
1168 IF g_debug THEN
1169 pay_in_utils.trace('p_message ',p_message );
1170 END IF;
1171 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,40);
1172 RETURN;
1173 END IF;
1174
1175 END check_gre_update;
1176 --------------------------------------------------------------------------
1177 -- --
1178 -- Name : CHECK_PF_UPDATE --
1179 -- Type : PROCEDURE --
1180 -- Access : Public --
1181 -- Description : Procedure to create PF ELement Entries --
1182 -- Parameters : --
1183 -- IN : p_effective_date DATE --
1184 -- p_dt_mode VARCHAR2 --
1185 -- p_assignment_id NUMBER --
1186 -- p_pf_org VARCHAR2 --
1187 -- OUT : p_message VARCHAR2 --
1188 -- Change History : --
1189 --------------------------------------------------------------------------
1190 -- Rev# Date Userid Description --
1191 --------------------------------------------------------------------------
1192 -- 1.0 05-Dec-04 statkar Created this function --
1193 -- 1.1 27-Dec-04 lnagaraj Used pay_in_utils.chk_element_link --
1194 -- 1.2 24-Mar-05 aaagarwa Modified cursor c_pf --
1195 -- 1.3 10-Apr-05 abhjain Removed the automatic element entry code --
1196 -- 1.4 25-Jul-05 snekkala Removed check for PF organization --
1197 -- 1.5 18-Aug-05 abhjain Commented the call to check_father_husband_name--
1198 --------------------------------------------------------------------------
1199 PROCEDURE check_pf_update
1200 (p_effective_date IN DATE
1201 ,p_dt_mode IN VARCHAR2
1202 ,p_assignment_id IN NUMBER
1203 ,p_gre_org IN VARCHAR2
1204 ,p_pf_org IN VARCHAR2
1205 ,p_esi_org IN VARCHAR2
1206 ,p_message OUT NOCOPY VARCHAR2
1207 ,p_gre IN NUMBER
1208 ,p_pf IN NUMBER
1209 ,p_esi IN NUMBER
1210 )
1211 IS
1212
1213 l_procedure VARCHAR2(100);
1214 l_message_name VARCHAR2(100);
1215 l_token_value VARCHAR2(10);
1216
1217 BEGIN
1218 g_debug := hr_utility.debug_enabled;
1219 l_procedure := g_package ||'check_pf_update';
1220 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
1221
1222 IF g_debug THEN
1223 pay_in_utils.trace('**************************************************','********************');
1224 pay_in_utils.trace('p_effective_date',to_char(p_effective_date,'yyyy-mm-dd'));
1225 pay_in_utils.trace('p_dt_mode ',p_dt_mode );
1226 pay_in_utils.trace('p_assignment_id',to_char(p_assignment_id));
1227 pay_in_utils.trace('p_gre_org ',p_gre_org );
1228 pay_in_utils.trace('p_pf_org ',p_pf_org );
1229 pay_in_utils.trace('p_esi_org ',p_esi_org );
1230 pay_in_utils.trace('p_gre ',to_char(p_gre));
1231 pay_in_utils.trace('p_pf ',to_char(p_pf));
1232 pay_in_utils.trace('p_esi ',to_char(p_esi));
1233 pay_in_utils.trace('**************************************************','********************');
1234 END IF;
1235
1236 l_message_name:='SUCCESS';
1237 pay_in_utils.null_message(p_token_name, p_token_value);
1238 /*
1239 check_father_husband_name
1240 (p_assignment_id =>p_assignment_id
1241 ,p_effective_date =>p_effective_date
1242 ,p_message_name =>l_message_name
1243 ,p_token_value =>l_token_value
1244 );
1245 IF l_message_name = 'PER_IN_CONTACT_DETAILS' THEN
1246 hr_utility.set_message(800, 'PER_IN_CONTACT_DETAILS');
1247 hr_utility.set_message_token('RELATION',l_token_value);
1248 hr_utility.raise_error;
1249 ELSIF l_message_name <> 'SUCCESS' THEN
1250 hr_utility.set_message(800, l_message_name);
1251 hr_utility.raise_error;
1252 END IF;
1253 */
1254 check_gre_update
1255 (p_effective_date => p_effective_date
1256 ,p_dt_mode => p_dt_mode
1257 ,p_assignment_id => p_assignment_id
1258 ,p_gre_org => p_gre_org
1259 ,p_pf_org => p_pf_org
1260 ,p_esi_org => p_esi_org
1261 ,p_gre => p_gre
1262 ,p_pf => p_pf
1263 ,p_esi => p_esi
1264 ,p_message => l_message_name
1265 ,p_token_name => p_token_name
1266 ,p_token_value => p_token_value
1267 );
1268
1269 IF l_message_name <> 'SUCCESS' THEN
1270 pay_in_utils.raise_message(800, l_message_name, p_token_name, p_token_value);
1271 END IF;
1272
1273 IF g_debug THEN
1274 pay_in_utils.trace('p_message',p_message);
1275 END IF;
1276
1277 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,170);
1278
1279 END check_pf_update;
1280
1281 --------------------------------------------------------------------------
1282 -- --
1283 -- Name : CHECK_ESI_UPDATE --
1284 -- Type : PROCEDURE --
1285 -- Access : Public --
1286 -- Description : Procedure to create PF ELement Entries --
1287 -- Parameters : --
1288 -- IN : p_effective_date DATE --
1289 -- p_dt_mode VARCHAR2 --
1290 -- p_assignment_id NUMBER --
1291 -- p_esi_org VARCHAR2 --
1292 -- OUT : p_message VARCHAR2 --
1293 -- Change History : --
1294 --------------------------------------------------------------------------
1295 -- Rev# Date Userid Description --
1296 --------------------------------------------------------------------------
1297 -- 1.0 05-Dec-04 statkar Created this function --
1298 -- 1.1 14-Dec-04 aaagarwa Added code for deleting ESI element entry --
1299 -- when payroll has not been run. --
1300 -- 1.2 27-Dec-04 lnagaraj Used pay_in_utils.chk_element_link --
1301 -- 1.3 24-Mar-05 aaagarwa Modified the cursor c_esi --
1302 -- 1.4 10-Apr-05 abhjain NULLed out the procedure --
1303 --------------------------------------------------------------------------
1304 PROCEDURE check_esi_update
1305 (p_effective_date IN DATE
1306 ,p_dt_mode IN VARCHAR2
1307 ,p_assignment_id IN NUMBER
1308 ,p_esi_org IN VARCHAR2
1309 ,p_message OUT NOCOPY VARCHAR2
1310 )
1311 IS
1312 BEGIN
1313
1314 NULL;
1315
1316 END check_esi_update;
1317
1318 --------------------------------------------------------------------------
1319 -- --
1320 -- Name : in_reset_input --
1321 -- Type : FUNCTION --
1322 -- Access : Public --
1323 -- Description : Procedure to reset remarks of PF Information element--
1324 -- Parameters : --
1325 -- IN : p_assignment_id NUMBER --
1326 -- p_element_entry_id NUMBER --
1327 -- p_business_group_id NUMBER --
1328 -- p_element_type_id NUMBER --
1329 -- p_date_earned DATE --
1330 -- --
1331 -- Change History : --
1332 --------------------------------------------------------------------------
1333 -- Rev# Date Userid Description --
1334 --------------------------------------------------------------------------
1335 -- 1.0 02-Jan-05 aaagarwa Created this procedure --
1336 --------------------------------------------------------------------------
1337
1338 Function in_reset_input_values(
1339 p_assignment_id NUMBER
1340 ,p_business_group_id NUMBER
1341 ,p_element_type_id NUMBER
1342 ,p_element_entry_id NUMBER
1343 ,p_date DATE
1344 ,p_input_value VARCHAR2)
1345 RETURN NUMBER IS
1346 Cursor c_ovn
1347 IS
1348 Select object_version_number
1349 From pay_element_entries_f
1350 Where element_type_id = p_element_type_id
1351 And assignment_id = p_assignment_id
1352 And p_date Between effective_start_date and effective_end_date;
1353
1354 Cursor c_input_value_id
1355 IS
1356 Select input_value_id
1357 From pay_input_values_f
1358 Where element_type_id = p_element_type_id
1359 And p_date Between effective_start_date AND effective_end_date
1360 And name=p_input_value;
1361
1362 l_input_val_id NUMBER;
1363 l_ovn NUMBER;
1364 l_effective_start_date DATE;
1365 l_effective_end_date DATE;
1366 l_warning BOOLEAN;
1367 l_procedure varchar2(100);
1368
1369 BEGIN
1370 g_debug := hr_utility.debug_enabled;
1371 l_procedure := g_package ||'in_reset_input_values';
1372 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1373
1374
1375 IF g_debug THEN
1376 pay_in_utils.trace('**************************************************','********************');
1377 pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id ));
1378 pay_in_utils.trace('p_business_group_id',to_char(p_business_group_id));
1379 pay_in_utils.trace('p_element_type_id ',to_char(p_element_type_id ));
1380 pay_in_utils.trace('p_element_entry_id ',to_char(p_element_entry_id ));
1381 pay_in_utils.trace('p_date ',to_char(p_date,'yyyy-mm-dd'));
1382 pay_in_utils.trace('p_input_value ',p_input_value);
1383 pay_in_utils.trace('**************************************************','********************');
1384 END IF;
1385
1386 OPEN c_ovn;
1387 FETCH c_ovn INTO l_ovn;
1388 CLOSE c_ovn;
1389
1390 OPEN c_input_value_id;
1391 FETCH c_input_value_id INTO l_input_val_id;
1392 CLOSE c_input_value_id;
1393
1394 pay_element_entry_api.update_element_entry
1395 (p_datetrack_update_mode => 'UPDATE'
1396 ,p_effective_date => p_date+1
1397 ,p_business_group_id => p_business_group_id
1398 ,p_element_entry_id => p_element_entry_id
1399 ,p_object_version_number => l_ovn
1400 ,p_input_value_id4 => l_input_val_id
1401 ,p_entry_value4 => ' '
1402 ,p_effective_start_date => l_effective_start_date
1403 ,p_effective_end_date => l_effective_end_date
1404 ,p_update_warning => l_warning
1405 );
1406
1407 IF g_debug THEN
1408 pay_in_utils.trace('1','1');
1409 END IF;
1410
1411 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1412
1413 RETURN 1;
1414 EXCEPTION
1415 When OTHERS THEN
1416 BEGIN
1417 IF g_debug THEN
1418 pay_in_utils.trace('0','0');
1419 END IF;
1420
1421 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1422
1423 RETURN 0;
1424 END;
1425 END in_reset_input_values;
1426
1427 --------------------------------------------------------------------------
1428 -- Name : check_pf_location --
1429 -- Type : Procedure --
1430 -- Access : Public --
1431 -- Description : Proc to be called for validation --
1432 -- Parameters : --
1433 -- IN : p_organization_id IN NUMBER --
1434 -- Change History : --
1435 --------------------------------------------------------------------------
1436 -- Rev# Date Userid Description --
1437 --------------------------------------------------------------------------
1438 -- 1.0 24-Jan-05 aaagarwa Created this procedure for validating loc --
1439 --------------------------------------------------------------------------
1440 PROCEDURE check_pf_location
1441 (p_organization_id IN NUMBER
1442 ,p_calling_procedure IN VARCHAR2
1443 ,p_message_name OUT NOCOPY VARCHAR2
1444 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1445 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
1446 IS
1447 l_procedure VARCHAR2(100);
1448
1449 CURSOR csr_loc IS
1450 SELECT location_id
1451 FROM hr_all_organization_units
1452 WHERE organization_id = p_organization_id;
1453
1454 l_location_id hr_all_organization_units.location_id%TYPE;
1455
1456 BEGIN
1457 g_debug := hr_utility.debug_enabled;
1458 l_procedure := g_package ||'check_pf_location';
1459 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1460
1461 p_message_name := 'SUCCESS';
1462 pay_in_utils.null_message(p_token_name, p_token_value);
1463
1464
1465 IF g_debug THEN
1466 pay_in_utils.trace('**************************************************','********************');
1467 pay_in_utils.trace('p_organization_id ',to_char(p_organization_id));
1468 pay_in_utils.trace('p_calling_procedure',p_calling_procedure);
1469 pay_in_utils.trace('**************************************************','********************');
1470 END IF;
1471
1472 OPEN csr_loc ;
1473 FETCH csr_loc
1474 INTO l_location_id;
1475 pay_in_utils.set_location(g_debug,l_procedure,20);
1476
1477 IF l_location_id IS NULL THEN
1478 CLOSE csr_loc;
1479 pay_in_utils.set_location(g_debug,l_procedure,30);
1480 p_message_name := 'PER_IN_NO_STATE_ENTERED';
1481 RETURN;
1482 END IF;
1483 CLOSE csr_loc;
1484
1485 IF g_debug THEN
1486 pay_in_utils.trace('p_message_name ',p_message_name );
1487 END IF;
1488 pay_in_utils.set_location(g_debug,l_procedure,40);
1489 RETURN;
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
1494 p_token_name(1) := 'FUNCTION';
1495 p_token_value(1) := l_procedure;
1496 p_token_name(2) := 'SQLERRMC';
1497 p_token_value(2) := sqlerrm;
1498 RETURN;
1499
1500 END check_pf_location;
1501 --------------------------------------------------------------------------
1502 -- --
1503 -- Name : GET_LWF_STATE --
1504 -- Type : FUNCTION --
1505 -- Access : Public --
1506 -- Description : Function to return the state associated with factory--
1507 -- /Establishement Org --
1508 -- Parameters : --
1509 -- IN : p_org VARCHAR2 --
1510 -- OUT : N/A --
1511 -- Return : VARCHAR2 --
1512 -- Change History : --
1513 --------------------------------------------------------------------------
1514 -- Rev# Date Userid Description --
1515 --------------------------------------------------------------------------
1516 -- 1.0 25-OCT-07 sivanara Created this function --
1517 --------------------------------------------------------------------------
1518
1519 FUNCTION get_lwf_state (p_organization_id IN NUMBER)
1520 RETURN VARCHAR2
1521 IS
1522 l_message VARCHAR2(255);
1523 CURSOR csr_state IS
1524 SELECT hl.loc_information16
1525 FROM hr_all_organization_units hou
1526 ,hr_locations hl
1527 WHERE hou.organization_id = p_organization_id
1528 AND hou.location_id = hl.location_id
1529 AND hl.style = 'IN';
1530 --
1531 l_state hr_lookups.lookup_code%TYPE;
1532 l_procedure VARCHAR2(100);
1533 BEGIN
1534 l_procedure := g_package||'get_lwf_state';
1535 g_debug := hr_utility.debug_enabled;
1536 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure, 10);
1537 OPEN csr_state ;
1538 FETCH csr_state INTO l_state;
1539 pay_in_utils.set_location (g_debug,'l_state = '||l_state,20);
1540 CLOSE csr_state;
1541 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1542 RETURN l_state;
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1546 pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 40);
1547 hr_utility.trace(l_message);
1548 RETURN NULL;
1549 END get_lwf_state;
1550
1551 --------------------------------------------------------------------------
1552 -- Name : get_org_id --
1553 -- Type : Function --
1554 -- Access : Public --
1555 -- Description : Function to get the Org Id of PF/ESI/PT Organization--
1556 -- on a particular date --
1557 -- Parameters : --
1558 -- IN : p_assignment_id IN NUMBER --
1559 -- p_business_group_id IN NUMBER --
1560 -- p_date IN DATE --
1561 -- p_org_type IN VARCHAR2 --
1562 -- Change History : --
1563 --------------------------------------------------------------------------
1564 -- Rev# Date Userid Description --
1565 --------------------------------------------------------------------------
1566 -- 1.0 08-Apr-05 abhjain Created this function to get the org id --
1567 --------------------------------------------------------------------------
1568 FUNCTION get_org_id(p_assignment_id IN NUMBER
1569 ,p_business_group_id IN NUMBER
1570 ,p_date IN DATE
1571 ,p_org_type IN VARCHAR2)
1572 RETURN NUMBER
1573 IS
1574 CURSOR cur_org (p_assignment_id NUMBER
1575 ,p_business_group_id NUMBER
1576 ,p_date DATE)
1577 IS
1578 SELECT hsc.segment2
1579 ,hsc.segment3
1580 ,hsc.segment4
1581 FROM per_assignments_f paf
1582 ,hr_soft_coding_keyflex hsc
1583 WHERE paf.assignment_id = p_assignment_id
1584 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
1585 AND paf.business_group_id = p_business_group_id
1586 AND p_date BETWEEN paf.effective_start_date
1587 AND paf.effective_end_date;
1588
1589 l_segment2 hr_soft_coding_keyflex.segment1%TYPE;
1590 l_segment3 hr_soft_coding_keyflex.segment1%TYPE;
1591 l_segment4 hr_soft_coding_keyflex.segment1%TYPE;
1592 l_message VARCHAR2(255);
1593 l_procedure VARCHAR2(100);
1594
1595 BEGIN
1596
1597 l_procedure := g_package||'get_org_id';
1598 g_debug := hr_utility.debug_enabled;
1599 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure, 10);
1600
1601 IF g_debug THEN
1602 pay_in_utils.trace('**************************************************','********************');
1603 pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id));
1604 pay_in_utils.trace('p_business_group_id',to_char(p_business_group_id));
1605 pay_in_utils.trace('p_date ',to_char(p_date,'yyyy-mm-dd'));
1606 pay_in_utils.trace('p_org_type ',p_org_type );
1607 pay_in_utils.trace('**************************************************','********************');
1608 END IF;
1609
1610 OPEN cur_org (p_assignment_id
1611 ,p_business_group_id
1612 ,p_date);
1613 FETCH cur_org into l_segment2
1614 ,l_segment3
1615 ,l_segment4;
1616 pay_in_utils.set_location (g_debug,'l_segment2 = '||l_segment2,20);
1617 pay_in_utils.set_location (g_debug,'l_segment3 = '||l_segment3,30);
1618 pay_in_utils.set_location (g_debug,'l_segment4 = '||l_segment4,40);
1619 CLOSE cur_org;
1620
1621 IF p_org_type = 'PF' THEN
1622 return to_number(l_segment2);
1623 ELSIF p_org_type = 'PT' THEN
1624 return to_number(l_segment3);
1625 ELSIF p_org_type = 'ESI' THEN
1626 return to_number(l_segment4);
1627 END IF;
1628
1629 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1630
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1634 pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 30);
1635 hr_utility.trace(l_message);
1636 RETURN NULL;
1637
1638
1639 END get_org_id;
1640
1641 END pay_in_ff_pkg;
1642