1 PACKAGE body per_in_perquisite_pkg as
2 /* $Header: peinperq.pkb 120.7.12010000.6 2008/08/06 09:14:47 ubhat ship $ */
3
4 --
5 -- Globals
6 --
7 g_package constant VARCHAR2(100) := 'per_in_perquisite_pkg.' ;
8 g_debug BOOLEAN ;
9
10
11
12 PROCEDURE check_element_entry(p_effective_date IN DATE
13 ,p_element_entry_id IN NUMBER
14 ,p_effective_start_date IN DATE
15 ,p_effective_end_date IN DATE
16 ,p_calling_procedure IN VARCHAR2
17 ,p_message_name OUT NOCOPY VARCHAR2
18 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
19 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
20 ) IS
21 /* Cursor to find the element name of the current element entry */
22
23 CURSOR c_perquisite_name IS
24 SELECT pet.element_information1
25 ,pet.element_type_id
26 ,pee.assignment_id
27 FROM pay_element_types_f pet
28 ,pay_element_entries_f pee
29 WHERE pet.element_type_id =pee.element_type_id
30 AND pee.element_entry_id =p_element_entry_id
31 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
32 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
33
34
35 /* Cursor to find the Screen entry value and entry value id for the current entry on effective date and given an input value */
36 CURSOR c_curr_entry_value (p_element_entry_id NUMBER
37 ,p_input_name VARCHAR2
38 ,p_element_type_id NUMBER)
39 IS
40 SELECT peev.screen_entry_value,
41 peev.element_entry_value_id
42 FROM pay_element_entry_values_f peev
43 ,pay_input_values_f piv
44 WHERE peev.element_entry_id = p_element_entry_id
45 AND piv.name = p_input_name
46 AND peev.input_value_id = piv.input_value_id
47 AND piv.element_Type_id = p_element_type_id
48 AND p_effective_date BETWEEN peev.effective_start_date
49 AND peev.effective_end_date
50 AND p_effective_Date BETWEEN piv.effective_start_date
51 AND piv.effective_end_date;
52
53
54 /* Cursor to find the the global value as on effective date */
55 CURSOR c_global_value(l_global_name VARCHAR2) IS
56 SELECT global_value
57 from ff_globals_f ffg
58 WHERE ffg.global_name = l_global_name
59 AND p_effective_date BETWEEN ffg.effective_start_date AND ffg.effective_end_date;
60
61 /* Cursor to find input value id given the element and input value name*/
62 CURSOR c_input_value_id(p_element_type_id NUMBER
63 ,p_input_name VARCHAR2)
64 IS
65 SELECT piv.input_value_id
66 FROM pay_input_values_f piv
67 WHERE piv.element_type_id = p_element_type_id
68 AND piv.NAME = p_input_name
69 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
70
71
72 /* Cursor to find the number of entries with entry value as 'Motor Car' and that overlap
73 in 'Motor Car' Perquisite */
74 CURSOR c_element_entries( p_element_type_id NUMBER
75 ,p_assignment_id NUMBER
76 ,p_input_value_id NUMBER -- type of automotive
77 ,p_input_value_id_start NUMBER -- benefit start
78 ,p_input_value_id_end NUMBER -- benefit end
79 ,l_benefit_start_date DATE
80 ,l_benefit_end_date DATE)
81 IS
82 SELECT pee.element_entry_id
83 ,pee.effective_start_date
84 FROM pay_element_entries_f pee
85 ,pay_element_entry_values_f peev1
86 ,pay_element_entry_values_f peev2
87 ,pay_element_entry_values_f peev3
88 WHERE pee.assignment_id = p_assignment_id
89 AND pee.element_type_id = p_element_type_id
90 and pee.element_entry_id =peev1.element_entry_id
91 AND peev1.input_value_id = p_input_value_id
92 and peev1.screen_entry_value ='CAR'
93 and pee.element_entry_id =peev2.element_entry_id
94 AND peev2.input_value_id = p_input_value_id_start
95 and pee.element_entry_id =peev3.element_entry_id
96 AND peev3.input_value_id = p_input_value_id_end
97 AND fnd_date.canonical_to_date(peev2.screen_entry_value) <= nvl(l_benefit_end_date,to_date('31-12-4712','DD-MM-YYYY'))
98 AND nvl(fnd_date.canonical_to_date(peev3.screen_entry_value),to_date('31-12-4712','DD-MM-YYYY')) >= l_benefit_start_date
99 AND pee.element_entry_id <> p_element_entry_id -- Bugfix 4049484
100 AND p_effective_date BETWEEN pee.effective_start_date and pee.effective_end_date
101 AND p_effective_date BETWEEN peev1.effective_start_date and peev1.effective_end_date
102 AND p_effective_date BETWEEN peev2.effective_start_date and peev2.effective_end_date
103 AND p_effective_date BETWEEN peev3.effective_start_date and peev3.effective_end_date;
104
105
106 /* Cursor to find the Screen entry value for the given entry on given date and given an input value id */
107 CURSOR c_element_entry_values(p_el_entry_id NUMBER
108 ,p_inp_value_id NUMBER
109 ,p_eff_start_date DATE) IS
110 SELECT peev.screen_entry_value
111 FROM pay_element_entry_values_f peev
112 WHERE peev.element_entry_id = p_el_entry_id
113 AND peev.input_value_id = p_inp_value_id
114 AND p_eff_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
115
116 CURSOR c_element_entry_details(p_element_entry_id NUMBER)
117 IS
118 SELECT pee.effective_start_date
119 ,pet.element_type_id
120 ,pee.assignment_id
121 FROM pay_element_entries_f pee
122 ,pay_element_types_f pet
123 ,pay_element_links_f links
124 WHERE pee.element_entry_id = p_element_entry_id
125 AND pet.element_type_id = links.element_type_id
126 AND links.element_link_id = pee.element_link_id
127 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
128 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
129
130 l_get_migrator_status VARCHAR2(1);
131 l_procedure VARCHAR2(100);
132
133 l_dep_value1 pay_element_entry_values_f.screen_entry_value%TYPE;
134 l_dep_value2 pay_element_entry_values_f.screen_entry_value%TYPE;
135
136 TYPE tab_input_value_id IS TABLE OF pay_input_values_f.input_value_id%TYPE INDEX BY BINARY_INTEGER;
137 l_input_value_id tab_input_value_id;
138
139 TYPE tab_element_entry_value IS TABLE OF pay_element_entry_values_f.screen_entry_value%TYPE INDEX BY BINARY_INTEGER;
140 l_element_entry_value tab_element_entry_value;
141
142 TYPE tab_element_entry_id IS TABLE OF pay_element_entries_f.element_entry_id%TYPE INDEX BY BINARY_INTEGER;
143 l_element_entry_id tab_element_entry_id;
144
145 TYPE tab_element_type_id IS TABLE OF pay_element_types_f.element_type_id%TYPE INDEX BY BINARY_INTEGER;
146 l_element_type_id_tab tab_element_type_id;
147
148 l_entry_value_id NUMBER;
149 i NUMBER ;
150 l_exempted NUMBER;
151 l_element_name pay_element_types_f.element_name %TYPE;
152 l_element_type_id NUMBER;
153 l_assignment_id NUMBER;
154
155
156 p_input_value_id NUMBER;
157 l_gbl_value NUMBER;
158 l_entry_id NUMBER;
159
160 l_element_start_date DATE;
161 l_inputvalue_id NUMBER;
162 --
163 -- Start of private procedures
164 --
165 ---------------------------------------------------------------------------
166 -- --
167 -- Name : CHECK_BENEFIT_DATES --
168 -- Type : Procedure --
169 -- Access : Private --
170 -- Description : Procedure is the driver procedure for the validation--
171 -- of Benefit Dates of a Perquisite. --
172 -- This procedure is the hook procedure for the --
173 -- when an element entry is created --
174 -- Parameters : --
175 -- IN : l_element_entry_id IN number --
176
177 ---------------------------------------------------------------------------
178
179 PROCEDURE check_benefit_dates(l_element_entry_id number)
180 IS
181 l_benefit_start_date pay_element_entry_values_f.screen_entry_value%TYPE;
182 l_benefit_end_date pay_element_entry_values_f.screen_entry_value%TYPE;
183 l_procedure VARCHAR2(100);
184 BEGIN
185
186 g_debug := hr_utility.debug_enabled ;
187 l_procedure := g_package || 'check_benefit_dates' ;
188 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
189
190 if g_debug then
191 pay_in_utils.trace('******************************','********************');
192 pay_in_utils.trace('l_element_entry_id : ',l_element_entry_id);
193 pay_in_utils.trace('******************************','********************');
194 end if;
195
196 p_message_name := 'SUCCESS';
197 pay_in_utils.null_message(p_token_name, p_token_value);
198
199 l_get_migrator_status:=hr_general.g_data_migrator_mode;
200 hr_general.g_data_migrator_mode:='Y';
201 pay_in_utils.set_location(g_debug,l_procedure,20);
202
203 /* Default the benefit Start date to element Entry Start Date */
204 OPEN c_curr_entry_value(p_element_entry_id,'Benefit Start Date',l_element_type_id);
205 FETCH c_curr_entry_value into l_benefit_start_date,l_entry_value_id;
206 if g_debug then
207 pay_in_utils.trace('l_benefit_start_date : ',l_benefit_start_date);
208 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
209 end if;
210 IF l_benefit_start_date IS NULL THEN
211 pay_in_utils.set_location(g_debug,l_procedure,30);
212 UPDATE pay_element_entry_values_f
213 SET screen_entry_value = to_char(p_effective_start_date,'YYYY/MM/DD HH24:MI:SS')
214 WHERE element_entry_value_id =l_entry_value_id
215 AND effective_start_date =p_effective_start_date;
216 END IF;
217 pay_in_utils.set_location(g_debug,l_procedure,40);
218 hr_general.g_data_migrator_mode:=l_get_migrator_status;
219 CLOSE c_curr_entry_value;
220
221 pay_in_utils.set_location(g_debug,l_procedure,50);
222
223 OPEN c_curr_entry_value(p_element_entry_id,'Benefit End Date',l_element_type_id);
224 FETCH c_curr_entry_value INTO l_benefit_end_date,l_entry_value_id;
225 CLOSE c_curr_entry_value;
226
227 if g_debug then
228 pay_in_utils.trace('******************************','********************');
229 pay_in_utils.trace('l_benefit_end_date : ',l_benefit_end_date);
230 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
231 pay_in_utils.trace('******************************','********************');
232 end if;
233
234 pay_in_utils.set_location(g_debug,l_procedure,60);
235
236 /* Check that Benefit End is not earlier than Benefit Start */
237 IF (nvl(TRUNC(to_date(l_benefit_start_date,'YYYY/MM/DD HH24:MI:SS')),p_effective_start_date)> TRUNC(to_date(l_benefit_end_date,'YYYY/MM/DD HH24:MI:SS'))) THEN
238 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
239 p_message_name := 'PER_IN_INCORRECT_DATES';
240 RETURN;
241 END IF;
242 EXCEPTION
243 WHEN OTHERS THEN
244 IF c_curr_entry_value%ISOPEN THEN CLOSE c_curr_entry_value ; END IF;
245 pay_in_utils.set_location(g_debug,'Leaving FROM Exception Block : '||l_procedure,80);
246 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
247 p_token_name(1) := 'FUNCTION';
248 p_token_value(1) := l_procedure;
249 p_token_name(1) := 'SQLERRMC';
250 p_token_value(1) := sqlerrm;
251 END ;
252
253 ---------------------------------------------------------------------------
254 -- --
255 -- Name : CHECK_LOAN_ENTRY --
256 -- Type : Procedure --
257 -- Access : Private --
258 -- Description : Procedure is the driver procedure for the Updating --
259 -- Taxable Flag of Loan Perquisite. --
260 -- Parameters : --
261 -- IN : l_element_name IN VARCHAR2 --
262 -- l_element_type_id IN NUMBER --
263 -- l_assignment_id IN NUMBER --
264 ---------------------------------------------------------------------------
265 PROCEDURE check_loan_entry(l_element_name VARCHAR2,
266 l_element_Type_id NUMBER,
267 l_assignment_id NUMBER) IS
268
269 CURSOR c_entries_start_tax_yr(p_tax_year_start DATE, p_assignment_id NUMBER, p_element_name varchar2) IS
270 SELECT pee.element_entry_id,pet.element_type_id
271 FROM pay_element_entries_f pee,pay_element_types_f pet
272 WHERE pee.assignment_id = p_assignment_id
273 AND pee.element_type_id = pet.element_type_id
274 AND pet.element_information1 = p_element_name
275 GROUP BY pee.element_entry_id,pet.element_type_id
276 HAVING MIN(pee.effective_start_date) >= p_tax_year_start;
277
278 l_tax_year_start DATE;
279 p_cnt number;
280 l_principal_amt_in_tax_yr NUMBER;
281 l_curr_principal_amt NUMBER;
282 l_loan_input_value_id tab_input_value_id;
283 l_tax_input_value_id tab_input_value_id;
284 l_procedure VARCHAR2(100);
285 l_loan_type pay_element_entry_values_f.screen_entry_value%TYPE;
286 l_loan_interest_type pay_element_entry_values_f.screen_entry_value%TYPE;
287
288 BEGIN
289
290 g_debug := hr_utility.debug_enabled;
291 l_procedure := g_package || 'check_loan_entry';
292 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
293
294 if g_debug then
295 pay_in_utils.trace('******************************',
296 '********************');
297 pay_in_utils.trace('l_element_name : ',
298 l_element_name);
299 pay_in_utils.trace('l_element_Type_id : ',
300 l_element_Type_id);
301 pay_in_utils.trace('l_assignment_id : ',
302 l_assignment_id);
303 pay_in_utils.trace('******************************',
304 '********************');
305 end if;
306
307 p_message_name := 'SUCCESS';
308 pay_in_utils.null_message(p_token_name, p_token_value);
309
310 pay_in_utils.set_location(g_debug,
311 'Effective Date: ' || p_effective_date,
312 20);
313
314 /*Check for Additional Information Begins*/
315
316 OPEN c_input_value_id(l_element_type_id, 'Additional Information');
317 FETCH c_input_value_id
318 INTO p_input_value_id;
319 CLOSE c_input_value_id;
320
321 if g_debug then
322 pay_in_utils.trace('p_input_value_id : ',
323 p_input_value_id);
324 end if;
325
326 pay_in_utils.set_location(g_debug, l_procedure, 12);
327 IF p_input_value_id IS NOT NULL THEN
328 /*Check made so that previous created elements without input value 'Additional Information' works.*/
329 OPEN c_element_entry_values(p_element_entry_id,
330 p_input_value_id,
331 p_effective_date);
332 FETCH c_element_entry_values
333 INTO l_loan_interest_type;
334 CLOSE c_element_entry_values;
335
336 if g_debug then
337 pay_in_utils.trace('l_loan_interest_type : ',
338 l_loan_interest_type);
339 end if;
340
341 OPEN c_input_value_id(l_element_type_id, 'Loan Type');
342 FETCH c_input_value_id
343 INTO p_input_value_id;
344 CLOSE c_input_value_id;
345
346 if g_debug then
347 pay_in_utils.trace('p_input_value_id : ',
348 p_input_value_id);
349 end if;
350
351 pay_in_utils.set_location(g_debug, l_procedure, 12);
352
353 OPEN c_element_entry_values(p_element_entry_id,
354 p_input_value_id,
355 p_effective_date);
356 FETCH c_element_entry_values
357 INTO l_loan_type;
358 CLOSE c_element_entry_values;
359
360 IF g_debug THEN
361 pay_in_utils.trace('l_loan_type : ', l_loan_type);
362 END IF;
363
364 IF l_loan_type = 'HOUSING' THEN
365 IF l_loan_interest_type IS NULL THEN
366 pay_in_utils.set_location(g_debug,
367 'Leaving: ' || l_procedure,
368 100);
369 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
370 p_token_name(1) := 'TOKEN1';
371 p_token_value(1) := 'Additional Information';
372 p_token_name(2) := 'TOKEN2';
373 p_token_value(2) := 'Loan Type';
374 p_token_name(3) := 'TOKEN3';
375 p_token_value(3) := 'Housing Loan';
376 RETURN;
377 ELSIF l_loan_interest_type NOT IN
378 ('HL_FIXED_RURAL', 'HL_FIXED_URBAN', 'HL_FLOATING_RURAL',
379 'HL_FLOATING_URBAN') THEN
380 pay_in_utils.set_location(g_debug,
381 'Leaving: ' || l_procedure,
382 100);
383 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
384 p_token_name(1) := 'TOKEN';
385 p_token_value(1) := 'Loan Type and Additional Information';
386 RETURN;
387 END IF;
388 ELSIF l_loan_type = 'CAR' THEN
389 IF l_loan_interest_type IS NULL THEN
390 pay_in_utils.set_location(g_debug,
391 'Leaving: ' || l_procedure,
392 100);
393 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
394 p_token_name(1) := 'TOKEN1';
395 p_token_value(1) := 'Additional Information';
396 p_token_name(2) := 'TOKEN2';
397 p_token_value(2) := 'Loan Type';
398 p_token_name(3) := 'TOKEN3';
399 p_token_value(3) := 'Car Loan';
400 RETURN;
401 ELSIF l_loan_interest_type NOT IN ('CAR_NEW', 'CAR_USED') THEN
402 pay_in_utils.set_location(g_debug,
403 'Leaving: ' || l_procedure,
404 100);
405 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
406 p_token_name(1) := 'TOKEN';
407 p_token_value(1) := 'Loan Type and Additional Information';
408 RETURN;
409 END IF;
410 ELSIF l_loan_type = 'TWOWHEELER' THEN
411 IF l_loan_interest_type IS NULL THEN
412 pay_in_utils.set_location(g_debug,
413 'Leaving: ' || l_procedure,
414 100);
415 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
416 p_token_name(1) := 'TOKEN1';
417 p_token_value(1) := 'Additional Information';
418 p_token_name(2) := 'TOKEN2';
419 p_token_value(2) := 'Loan Type';
420 p_token_name(3) := 'TOKEN3';
421 p_token_value(3) := 'Two Wheeler Loan';
422 RETURN;
423 ELSIF l_loan_interest_type NOT IN ('TWL_FIXED', 'TWL_FLOATING') THEN
424 pay_in_utils.set_location(g_debug,
425 'Leaving: ' || l_procedure,
426 100);
427 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
428 p_token_name(1) := 'TOKEN';
429 p_token_value(1) := 'Loan Type and Additional Information';
430 RETURN;
431 END IF;
432 ELSIF l_loan_type = 'MORTGAGE' THEN
433 IF p_effective_start_date >= TO_DATE('01-04-2008','DD-MM-YYYY') THEN
434 IF l_loan_interest_type IS NULL THEN
435 pay_in_utils.set_location(g_debug,
436 'Leaving: ' || l_procedure,
437 100);
438 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
439 p_token_name(1) := 'TOKEN1';
440 p_token_value(1) := 'Additional Information';
441 p_token_name(2) := 'TOKEN2';
442 p_token_value(2) := 'Loan Type';
443 p_token_name(3) := 'TOKEN3';
444 p_token_value(3) := 'Mortgage Loan';
445 RETURN;
446 ELSIF l_loan_interest_type NOT IN ('MRTAGE_IMMOVABLE', 'MRTAGE_GOLD') THEN
447 pay_in_utils.set_location(g_debug,
448 'Leaving: ' || l_procedure,
449 100);
450 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
451 p_token_name(1) := 'TOKEN';
452 p_token_value(1) := 'Loan Type and Additional Information';
453 RETURN;
454 END IF;
455 END IF;
456 END IF;
457 END IF;
458 /*Check for Additional Information Ends*/
459
460 /* Start - Get entries of loan availed in current tax year */
461 l_tax_year_start := pay_in_tax_utils.get_financial_year_start(p_effective_date);
462 pay_in_utils.set_location(g_debug,
463 'Tax Year Start: ' || l_tax_year_start,
464 30);
465
466 p_cnt := 0;
467 FOR i IN c_entries_start_tax_yr(l_tax_year_start,
468 l_assignment_id,
469 l_element_name) LOOP
470 l_element_entry_id(p_cnt) := i.element_entry_id;
471 l_element_type_id_tab (p_cnt) := i.element_type_id;
472 /*Added for the bugfix 6469684*/
473 OPEN c_input_value_id(l_element_type_id_tab (p_cnt), 'Loan Principal Amount');
474 FETCH c_input_value_id
475 INTO l_loan_input_value_id(p_cnt);
476 CLOSE c_input_value_id;
477
478 OPEN c_input_value_id(l_element_type_id_tab(p_cnt), 'Taxable Flag');
479 FETCH c_input_value_id
480 INTO l_tax_input_value_id(p_cnt);
481 CLOSE c_input_value_id;
482 p_cnt := p_cnt + 1;
483 END LOOP;
484 /* End - Get entries of loan avialed in current tax year */
485
486 pay_in_utils.set_location(g_debug, 'Entry Count: ' || p_cnt, 40);
487 l_principal_amt_in_tax_yr := 0;
488
489 /* Start - Find the sum of Loan Principal Amount*/
490 FOR j IN 0 .. p_cnt - 1 LOOP
491 pay_in_utils.set_location(g_debug,
492 'Entry id and Input value id : ' ||
493 l_element_entry_id(j) || l_loan_input_value_id(j),
494 50);
495
496 OPEN c_element_entry_values(l_element_entry_id(j),
497 l_loan_input_value_id(j),
498 p_effective_date);
499 FETCH c_element_entry_values
500 INTO l_curr_principal_amt;
501
502 l_principal_amt_in_tax_yr := l_principal_amt_in_tax_yr +
503 nvl(l_curr_principal_amt, 0);
504
505 CLOSE c_element_entry_values;
506 END LOOP;
507 /* End - Find the sum of Loan Principal Amount*/
508
509 OPEN c_global_value('IN_MAX_LOAN_AMT_EXEMPTION');
510 FETCH c_global_value
511 INTO l_gbl_value;
512 CLOSE c_global_value;
513
514 pay_in_utils.set_location(g_debug,
515 'Total Principal Amount: ' ||
516 l_principal_amt_in_tax_yr,
517 60);
518 --
519 -- start - Check if exemption limit is exceeded
520 --
521
522
523 l_get_migrator_status := hr_general.g_data_migrator_mode;
524 hr_general.g_data_migrator_mode := 'Y';
525
526 IF l_principal_amt_in_tax_yr > l_gbl_value then
527 /* Update the Taxable Flag to 'Y' when limit is exceeded */
528 FOR j IN 0 .. P_CNT - 1 LOOP
529
530 pay_in_utils.set_location(g_debug,
531 'Changing the following entries : ' ||
532 l_element_entry_id(j),
533 70);
534 UPDATE pay_element_entry_values_f peev
535 SET peev.screen_entry_value = 'Y'
536 WHERE peev.element_entry_id = l_element_entry_id(j)
537 AND peev.input_value_id = l_tax_input_value_id(j)
538 AND p_effective_date BETWEEN peev.effective_start_date and
539 peev.effective_end_date
540 AND nvl(peev.screen_entry_value, 'N') = 'N';
541 END LOOP;
542 ELSE
543 FOR j IN 0 .. P_CNT - 1 LOOP
544 /* Update the Taxable Flag to 'N' when the user accidentally enters incorrect values previously */
545 pay_in_utils.set_location(g_debug,
546 'Changing the following entries : ' ||
547 l_element_entry_id(j),
548 80);
549 UPDATE pay_element_entry_values_f peev
550 SET peev.screen_entry_value = 'N'
551 WHERE peev.element_entry_id = l_element_entry_id(j)
552 AND peev.input_value_id = l_tax_input_value_id(j)
553 AND p_effective_date BETWEEN peev.effective_start_date and
554 peev.effective_end_date
555 AND nvl(peev.screen_entry_value, 'Y') = 'Y';
556 END LOOP;
557
558 END IF;
559
560 hr_general.g_data_migrator_mode := l_get_migrator_status;
561 --
562 -- End - Check if exemption limit is exceeded
563 --
564 /* delete the PL/SQL table */
565 l_element_entry_id.delete;
566 pay_in_utils.set_location(g_debug, 'Leaving: ' || l_procedure, 90);
567 EXCEPTION
568 WHEN OTHERS THEN
569 IF c_entries_start_tax_yr%ISOPEN THEN
570 CLOSE c_entries_start_tax_yr;
571 END IF;
572 IF c_input_value_id%ISOPEN THEN
573 CLOSE c_input_value_id;
574 END IF;
575 IF c_element_entry_values%ISOPEN THEN
576 CLOSE c_element_entry_values;
577 END IF;
578 IF c_global_value%ISOPEN THEN
579 CLOSE c_global_value;
580 END IF;
581
582 pay_in_utils.set_location(g_debug,
583 'Leaving FROM Exception Block : ' ||
584 l_procedure,
585 100);
586 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
587 p_token_name(1) := 'FUNCTION';
588 p_token_value(1) := l_procedure;
589 p_token_name(2) := 'SQLERRMC';
590 p_token_value(2) := sqlerrm;
591 END check_loan_entry;
592
593
594 ---------------------------------------------------------------------------
595 -- --
596 -- Name : CHECK_MOTORCAR_ENTRY --
597 -- Type : Procedure --
598 -- Access : Private --
599 -- Description : Procedure is the driver procedure to validate --
600 -- Motor car entries. --
601 -- Parameters : --
602 -- IN : l_element_name IN VARCHAR2 --
603 -- : l_element_Type_id IN NUMBER --
604 -- : l_assignment_id IN NUMBER --
605
606 ---------------------------------------------------------------------------
607
608 PROCEDURE check_motorcar_entry(l_element_name VARCHAR2
609 ,l_element_Type_id NUMBER
610 ,l_assignment_id NUMBER)
611 IS
612 l_first_count NUMBER;
613 TYPE tab_input_value_name IS TABLE OF pay_input_values_f.name%TYPE INDEX BY BINARY_INTEGER;
614 l_input_value_name tab_input_value_name;
615
616 TYPE tab_effective_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
617 l_eff_start_date tab_effective_date;
618 p_cnt NUMBER;
619 l_procedure VARCHAR2(100);
620 p_input_value_id_start number;
621 p_input_value_id_end number;
622
623 l_benefit_start pay_element_entry_values_f.screen_entry_value%TYPE;
624 l_benefit_end pay_element_entry_values_f.screen_entry_value%TYPE;
625 l_benefit_start_date date;
626 l_benefit_end_date date;
627 l_type_automotive pay_element_entry_values_f.screen_entry_value%TYPE;
628
629 BEGIN
630
631 l_procedure := g_package ||'check_motorcar_entry';
632 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
633
634 if g_debug then
635 pay_in_utils.trace('******************************','********************');
636 pay_in_utils.trace('l_element_name : ',l_element_name);
637 pay_in_utils.trace('l_element_Type_id : ',l_element_Type_id);
638 pay_in_utils.trace('l_assignment_id : ',l_assignment_id);
639 pay_in_utils.trace('******************************','********************');
640 end if;
641
642 p_message_name := 'SUCCESS';
643 pay_in_utils.null_message(p_token_name, p_token_value);
644
645 pay_in_utils.set_location(g_debug,l_procedure,11);
646
647 OPEN c_input_value_id(l_element_type_id
648 ,'Type of Automotive');
649 FETCH c_input_value_id INTO p_input_value_id;
650 CLOSE c_input_value_id;
651
652 if g_debug then
653 pay_in_utils.trace('p_input_value_id : ',p_input_value_id);
654 end if;
655
656 pay_in_utils.set_location(g_debug,l_procedure,12);
657
658 OPEN c_element_entry_values(p_element_entry_id
659 , p_input_value_id
660 , p_effective_date);
661 FETCH c_element_entry_values INTO l_type_automotive;
662 CLOSE c_element_entry_values;
663
664 if g_debug then
665 pay_in_utils.trace('l_type_automotive : ',l_type_automotive);
666 end if;
667
668
669 IF l_type_automotive = 'CAR' THEN -- Bugfix 4049484
670
671 OPEN c_input_value_id(l_element_type_id
672 ,'Benefit Start Date');
673 FETCH c_input_value_id INTO p_input_value_id_start;
674 CLOSE c_input_value_id;
675
676 if g_debug then
677 pay_in_utils.trace('p_input_value_id_start : ',p_input_value_id_start);
678 end if;
679
680 OPEN c_input_value_id(l_element_type_id
681 ,'Benefit End Date');
682 FETCH c_input_value_id INTO p_input_value_id_end;
683 CLOSE c_input_value_id;
684
685 if g_debug then
686 pay_in_utils.trace('p_input_value_id_end : ',p_input_value_id_end);
687 end if;
688
689 OPEN c_element_entry_values(p_element_entry_id
690 , p_input_value_id_start
691 , p_effective_date);
692 FETCH c_element_entry_values INTO l_benefit_start;
693 CLOSE c_element_entry_values;
694
695 if g_debug then
696 pay_in_utils.trace('l_benefit_start : ',l_benefit_start);
697 end if;
698
699 OPEN c_element_entry_values(p_element_entry_id
700 , p_input_value_id_end
701 , p_effective_date);
702 FETCH c_element_entry_values INTO l_benefit_end;
703 CLOSE c_element_entry_values;
704
705 if g_debug then
706 pay_in_utils.trace('l_benefit_end : ',l_benefit_end);
707 end if;
708
709 l_benefit_start_date :=fnd_Date.canonical_to_date(l_benefit_start);
710 l_benefit_end_date :=fnd_Date.canonical_to_date(l_benefit_end);
711
712 if g_debug then
713 pay_in_utils.trace('l_benefit_start_date : ',l_benefit_start_date);
714 pay_in_utils.trace('l_benefit_end_date : ',l_benefit_end_date);
715 end if;
716
717 -- Bugfix 4049484
718 i:=1;
719 l_element_entry_id(0) := p_element_entry_id;
720 l_eff_start_date(0) := l_benefit_start_date;
721
722 /* Get the entries of Motor Car that overlap with the current entry */
723 OPEN c_element_entries(l_element_Type_id,l_assignment_id,p_input_value_id,p_input_value_id_start,p_input_value_id_end,l_benefit_start_date,l_benefit_end_date);
724 LOOP
725 FETCH c_element_entries INTO l_element_entry_id(i),l_eff_start_date(i);
726 EXIT WHEN c_element_entries%NOTFOUND;
727 i :=i+1;
728 END LOOP;
729 CLOSE c_element_entries;
730
731
732 p_cnt := l_element_entry_id.COUNT;
733 /* Start - Perform the following checks when there are more than one entry for Motor Car */
734
735 IF p_cnt > 1 THEN -- Bugfix 4049484
736 pay_in_utils.set_location(g_debug,' Entry count is : '||p_cnt,40);
737
738
739 /* Get the input value id */
740 l_input_value_name(0) := 'Type of Automotive';
741 l_input_value_name(1) := 'Category of Car';
742 l_input_value_name(2) := 'Operational Expenses by';
743 l_input_value_name(3) := 'Usage of Car';
744
745 FOR i in 0..3 LOOP
746 OPEN c_input_value_id(l_element_type_id
747 ,l_input_value_name(i)) ;
748 FETCH c_input_value_id
749 INTO l_input_value_id(i);
750 CLOSE c_input_value_id;
751 END LOOP;
752
753 pay_in_utils.set_location(g_debug,l_procedure,50);
754
755 l_first_count :=0;
756
757 /* LOOP Start */
758 FOR i in 0..p_cnt-1 LOOP
759 pay_in_utils.set_location(g_debug,l_procedure,60);
760
761 IF l_element_entry_value.COUNT>0 THEN
762 l_element_entry_value.delete;
763 END IF;
764
765 pay_in_utils.set_location(g_debug,l_procedure,70);
766
767 FOR j IN 0..3 LOOP
768 OPEN c_element_entry_values(l_element_entry_id(i)
769 , l_input_value_id(j)
770 , l_eff_start_date(i));
771 FETCH c_element_entry_values INTO l_element_entry_value(j);
772 CLOSE c_element_entry_values;
773 END LOOP;
774
775 pay_in_utils.set_location(g_debug,l_procedure,80);
776
777 IF (l_element_entry_value(0) = 'CAR' AND l_element_entry_value(1) = 'OWN_EMPLOYER' AND l_element_entry_value(2) = 'EMPLOYEE' AND l_element_entry_value(3) = 'PARTIAL' )THEN
778 pay_in_utils.set_location(g_debug,l_procedure,90);
779 --
780 -- Check that no more than one entry with the above values exist
781 --
782 IF l_first_count <> 0 THEN
783 pay_in_utils.set_location(g_debug,'Invalid entry Motor Car More than one entry : '||l_procedure,100);
784 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
785 p_token_name(1) := 'TOKEN';
786 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','MOTOR_CAR');
787 RETURN;
788 END IF;
789 l_first_count := l_first_count +1;
790 ELSIF (l_element_entry_value(0) = 'CAR' and l_element_entry_value(1) = 'OWN_EMPLOYER' AND l_element_entry_value(2) = 'EMPLOYER' AND l_element_entry_value(3) ='PRIVATE') THEN
791 NULL;
792 ELSE
793 /* Raise an error for all other combination of values */
794 pay_in_utils.set_location(g_debug,'Invalid entry motor car : '||l_procedure,110);
795 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
796 p_token_name(1) := 'TOKEN';
797 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','MOTOR_CAR');
798 RETURN;
799 END IF;
800
801 END LOOP;
802 /* Loop End */
803 /* Check that exactly one entry satisfies the conditon*/
804 IF l_first_count <>1 THEN
805 pay_in_utils.set_location(g_debug,'Invalid entry in car entry : '||l_procedure,120);
806 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
807 p_token_name(1) := 'TOKEN';
808 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','MOTOR_CAR');
809 RETURN;
810 END IF;
811
812
813 END IF;
814 /* End - Perform the following checks when there are more than one entry for Motor Car */
815 IF l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;
816 IF l_input_value_id.COUNT > 0 THEN l_input_value_id.delete; END IF;
817 IF l_eff_start_date.COUNT > 0 THEN l_eff_start_date.delete; END IF;
818 IF l_element_entry_value.COUNT > 0 THEN l_element_entry_value.delete; END IF;
819 END IF; /* End - Type of Automotive is Motor Car */
820
821 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,130);
822 EXCEPTION
823 WHEN OTHERS THEN
824 IF c_input_value_id%ISOPEN THEN CLOSE c_input_value_id ; END IF;
825 IF c_global_value%ISOPEN THEN CLOSE c_global_value ; END IF;
826 pay_in_utils.set_location(g_debug,'Leaving FROM Exception Block : '||l_procedure,140);
827 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
828 p_token_name(1) := 'FUNCTION';
829 p_token_value(1) := l_procedure;
830 p_token_name(2) := 'SQLERRMC';
831 p_token_value(2) := sqlerrm;
832 END check_motorcar_entry;
833
834 ---------------------------------------------------------------------------
835 -- --
836 -- Name : CHECK_LTC_ENTRY --
837 -- Type : Procedure --
838 -- Access : Private --
839 -- Description : Procedure is the driver procedure to validate --
840 -- Motor car entries. --
841 -- Parameters : --
842 -- IN : l_element_name IN VARCHAR2 --
843 -- : l_element_Type_id IN NUMBER --
844 -- : l_assignment_id IN NUMBER --
845
846 ---------------------------------------------------------------------------
847 PROCEDURE check_ltc_entry(l_element_name VARCHAR2
848 ,l_element_Type_id NUMBER
849 ,l_assignment_id NUMBER)
850 IS
851 /* Cursor to find the LTC Block at the given effective Date */
852 CURSOR c_ltc_block(p_date DATE)
853 IS
854 SELECT hrl.lookup_code
855 ,hrl.meaning
856 FROM hr_lookups hrl
857 WHERE hrl.lookup_type ='IN_LTC_BLOCK'
858 AND to_number(to_char(p_date,'YYYY')) BETWEEN
859 to_number(SUBSTR(HRL.LOOKUP_CODE,1,4)) AND to_number(SUBSTR(HRL.LOOKUP_CODE,8,4)) ;
860
861 /* Cursor to find the LTC Availed in Previous employment given the LTC Block Start and End Dates */
862 CURSOR c_prev_employer_ltc_availed(p_start_date date
863 ,p_end_date date
864 ,p_assignment_id NUMBER)
865 IS
866 SELECT sum(nvl(ppm.pem_information8,0))
867 FROM per_previous_employers ppm,
868 per_all_assignments_f paa
869 WHERE paa.assignment_id = p_assignment_id
870 AND p_effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
871 AND paa.person_id =ppm.person_id
872 AND ppm.end_date BETWEEN p_start_date and p_end_date;
873
874 /* Cursor to Find the LTC Availed in Current Employment given the LTC Block Start and End Dates*/
875 CURSOR c_prev_blk_entry_value(p_element_Type_id NUMBER
876 ,p_start_date DATE
877 ,p_end_date DATE
878 ,p_assignment_id NUMBER
879 ,p_value_input number
880 ,p_blk_input number
881 ,p_prev_block VARCHAR2)
882 IS
883 SELECT count(*)
884 FROM pay_element_entries_f pee
885 ,pay_element_entry_values_f peev1
886 ,pay_element_entry_values_f peev2
887 WHERE pee.assignment_id = p_assignment_id
888 AND pee.element_type_id = p_element_Type_id
889 AND peev1.input_value_id = p_value_input
890 AND peev1.element_entry_id =peev2.element_entry_id
891 AND peev2.input_value_id = p_blk_input
892 AND peev2.screen_entry_value = p_prev_block
893 AND nvl(peev1.screen_entry_value,'N')='N'
894 AND peev1.element_entry_id =pee.element_entry_id
895 AND pee.effective_start_date BETWEEN p_start_date AND p_end_date
896 AND peev1.effective_start_date BETWEEN p_start_date AND p_end_date
897 AND peev2.effective_start_date BETWEEN p_start_date AND p_end_date;
898
899 /* Cursor to find if LTC Exemption is already carried Over from previous Block*/
900 CURSOR c_exemption_availed(p_element_Type_id NUMBER
901 ,p_start_date DATE
902 ,p_end_date DATE
903 ,p_assignment_id NUMBER
904 ,p_value_input NUMBER
905 ,p_blk_input NUMBER
906 ,p_curr_block VARCHAR2)
907 IS
908 SELECT count(*)
909 FROM pay_element_entries_f pee
910 ,pay_element_entry_values_f peev1
911 ,pay_element_entry_values_f peev2
912 WHERE pee.assignment_id = p_assignment_id
913 AND pee.element_type_id = p_element_Type_id
914 AND peev1.input_value_id = p_value_input
915 AND peev1.element_entry_id =peev2.element_entry_id
916 AND peev2.input_value_id = p_blk_input
917 AND peev2.screen_entry_value = p_curr_block
918 AND peev1.screen_entry_value='Y'
919 AND peev1.element_entry_id =pee.element_entry_id
920 AND pee.effective_start_date BETWEEN p_start_date AND p_end_date
921 AND peev1.effective_start_date BETWEEN p_start_date AND p_end_date
922 AND peev2.effective_start_date BETWEEN p_start_date AND p_end_date;
923
924 l_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
925
926
927 l_curr_block HR_LOOKUPS.LOOKUP_CODE%TYPE;
928 l_curr_period HR_LOOKUPS.meaning%TYPE;
929 l_current_year NUMBER;
930 l_current_blk_start NUMBER;
931
932 l_prev_blk_date DATE;
933 l_prev_block HR_LOOKUPS.LOOKUP_CODE%TYPE;
934 l_prev_period HR_LOOKUPS.meaning%TYPE;
935
936 l_carry_over_id NUMBER;
937 l_journey_block_id NUMBER;
938
939 l_cur_emplr_prev_blk NUMBER;
940 l_prev_emplr_curr_blk NUMBER;
941 l_prev_emplr_prev_blk NUMBER;
942 l_exemption NUMBER;
943 l_procedure VARCHAR2(100);
944
945
946 l_prev_start_date DATE;
947 l_prev_end_date DATE;
948 l_curr_start_date DATE;
949 l_curr_end_date DATE;
950 l_max_with_carry_over NUMBER;
951 l_max_ltc NUMBER;
952
953
954 BEGIN
955 l_procedure := g_package ||'check_ltc_entry';
956 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
957
958 if g_debug then
959 pay_in_utils.trace('******************************','********************');
960 pay_in_utils.trace('l_element_name : ',l_element_name);
961 pay_in_utils.trace('l_element_Type_id : ',l_element_Type_id);
962 pay_in_utils.trace('l_assignment_id : ',l_assignment_id);
963 pay_in_utils.trace('******************************','********************');
964 end if;
965
966 p_message_name := 'SUCCESS';
967 pay_in_utils.null_message(p_token_name, p_token_value);
968
969 OPEN c_curr_entry_value(p_element_entry_id,'Carryover from Prev Block',l_element_type_id);
970 FETCH c_curr_entry_value INTO l_entry_value,l_entry_value_id;
971 CLOSE c_curr_entry_value;
972
973 if g_debug then
974 pay_in_utils.trace('l_entry_value : ',l_entry_value);
975 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
976 end if;
977 pay_in_utils.set_location(g_debug,l_procedure,15);
978
979 --
980 -- Find value held in globals before any further Processing
981 -- Fix 3956926
982 --
983
984 OPEN c_global_value('IN_MAX_JOURNEY_BLOCK_LTC');
985 FETCH c_global_value INTO l_max_ltc;
986 CLOSE c_global_value;
987
988 if g_debug then
989 pay_in_utils.trace('l_max_ltc : ',l_max_ltc);
990 end if;
991
992 OPEN c_global_value('IN_JOURNEY_CARRY_OVER');
993 FETCH c_global_value INTO l_max_with_carry_over;
994 CLOSE c_global_value;
995
996 if g_debug then
997 pay_in_utils.trace('l_max_with_carry_over : ',l_max_with_carry_over);
998 end if;
999 --
1000 --Start of Check the value in Carryover from Prev Block
1001 --
1002 IF nvl(l_entry_value,'N') ='Y' THEN
1003
1004 -- Check that the current year is the first year in LTC Block.Otherwise,raise an error
1005 OPEN c_ltc_block(p_effective_date);
1006 FETCH c_ltc_block INTO l_curr_block,l_curr_period;
1007 CLOSE c_ltc_block;
1008
1009 if g_debug then
1010 pay_in_utils.trace('l_curr_block : ',l_curr_block);
1011 pay_in_utils.trace('l_curr_period : ',l_curr_period);
1012 pay_in_utils.trace('p_effective_date : ',p_effective_date);
1013 end if;
1014 pay_in_utils.set_location(g_debug,l_procedure,20);
1015
1016 l_current_year := to_number(to_char(p_effective_date,'YYYY'));
1017 l_current_blk_start := to_number(substr(l_curr_block,1,4));
1018 IF l_current_year <> l_current_blk_start THEN
1019 pay_in_utils.set_location(g_debug,'Leaving.. '||l_procedure,30);
1020 p_message_name := 'PER_IN_LTC_CARRY_OVER'; -- Fix 3956926
1021 RETURN;
1022 END IF;
1023
1024 l_curr_start_date := to_date(substr(l_curr_period,1,11),'DD-MM-YYYY');
1025 l_curr_end_date := to_date(substr(l_curr_period,15,11),'DD-MM-YYYY');
1026
1027 if g_debug then
1028 pay_in_utils.trace('p_effective_start_date : ',p_effective_start_date);
1029 end if;
1030 -- Get the Previous Block start and End Dates
1031 l_prev_blk_date := ADD_MONTHS(p_effective_start_date,-48);
1032 OPEN c_ltc_block(l_prev_blk_date);
1033 FETCH c_ltc_block INTO l_prev_block,l_prev_period;
1034 close c_ltc_block;
1035
1036 if g_debug then
1037 pay_in_utils.trace('l_prev_block : ',l_prev_block);
1038 pay_in_utils.trace('l_prev_period : ',l_prev_period);
1039 end if;
1040 pay_in_utils.set_location(g_debug,l_procedure,40);
1041
1042 l_prev_start_date := to_date(substr(l_prev_period,1,11),'DD-MM-YYYY');
1043 l_prev_end_date := to_date(substr(l_prev_period,15,11),'DD-MM-YYYY');
1044
1045
1046 -- Get LTC Availed in Current employment in previous LTC Block
1047 OPEN c_input_value_id(l_element_Type_id
1048 ,'Carryover from Prev Block');
1049 FETCH c_input_value_id INTO l_carry_over_id;
1050 CLOSE c_input_value_id;
1051
1052 if g_debug then
1053 pay_in_utils.trace('l_carry_over_id : ',l_carry_over_id);
1054 end if;
1055
1056 OPEN c_input_value_id(l_element_Type_id
1057 ,'LTC Journey Block');
1058 FETCH c_input_value_id INTO l_journey_block_id;
1059 CLOSE c_input_value_id;
1060
1061 if g_debug then
1062 pay_in_utils.trace('l_journey_block_id : ',l_journey_block_id);
1063 end if;
1064
1065 pay_in_utils.set_location(g_debug,l_procedure,50);
1066
1067 OPEN c_prev_blk_entry_value(l_element_type_id
1068 ,l_prev_start_date
1069 ,l_prev_end_date
1070 ,l_assignment_id
1071 ,l_carry_over_id
1072 ,l_journey_block_id
1073 ,l_prev_block);
1074 FETCH c_prev_blk_entry_value INTO l_cur_emplr_prev_blk;
1075 CLOSE c_prev_blk_entry_value;
1076
1077 if g_debug then
1078 pay_in_utils.trace('l_cur_emplr_prev_blk : ',l_cur_emplr_prev_blk);
1079 end if;
1080
1081 OPEN c_prev_employer_ltc_availed(l_prev_start_date
1082 ,l_prev_end_date
1083 ,l_assignment_id );
1084 FETCH c_prev_employer_ltc_availed INTO
1085 l_prev_emplr_prev_blk;
1086 CLOSE c_prev_employer_ltc_availed;
1087
1088 if g_debug then
1089 pay_in_utils.trace('l_prev_emplr_prev_blk : ',l_prev_emplr_prev_blk);
1090 end if;
1091
1092 pay_in_utils.set_location(g_debug,l_procedure,60);
1093
1094
1095 /* Check if carry over is valid */
1096 IF ( nvl(l_cur_emplr_prev_blk,0) + nvl(l_prev_emplr_prev_blk,0) >=l_max_ltc) THEN
1097 pay_in_utils.set_location(g_debug,'Leaving...'||l_procedure,70);
1098 p_message_name := 'PER_IN_LTC_EXEMPTION_AVAILED';
1099 RETURN;
1100 END IF;
1101
1102
1103 OPEN c_exemption_availed(l_element_type_id
1104 ,l_curr_start_date
1105 ,l_curr_end_date
1106 ,l_assignment_id
1107 ,l_carry_over_id
1108 ,l_journey_block_id
1109 ,l_curr_block);
1110 FETCH c_exemption_availed INTO l_exemption;
1111 CLOSE c_exemption_availed;
1112
1113 if g_debug then
1114 pay_in_utils.trace('l_exemption : ',l_exemption);
1115 end if;
1116
1117 pay_in_utils.set_location(g_debug,l_procedure,80);
1118
1119 /* Check if Carry Over has already been availed */
1120 IF (nvl(l_exemption,0) > (l_max_with_carry_over - l_max_ltc) ) THEN
1121 pay_in_utils.set_location(g_debug,'Leaving... '||l_procedure,90);
1122 p_message_name := 'PER_IN_LTC_EXEMPTION_AVAILED';
1123 RETURN;
1124 END IF;
1125 pay_in_utils.set_location(g_debug,l_procedure,100);
1126
1127 END IF;
1128
1129 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,110);
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 IF c_curr_entry_value%ISOPEN THEN CLOSE c_curr_entry_value ; END IF;
1133 IF c_ltc_block%ISOPEN THEN CLOSE c_ltc_block ; END IF;
1134 IF c_prev_blk_entry_value%ISOPEN THEN CLOSE c_prev_blk_entry_value ; END IF;
1135 IF c_exemption_availed%ISOPEN THEN CLOSE c_exemption_availed ; END IF;
1136 IF c_input_value_id%ISOPEN THEN CLOSE c_input_value_id ; END IF;
1137 IF c_prev_employer_ltc_availed%ISOPEN THEN CLOSE c_prev_employer_ltc_availed ; END IF;
1138 IF c_global_value%ISOPEN THEN CLOSE c_global_value ; END IF;
1139
1140 pay_in_utils.set_location(g_debug,'Leaving FROM Exception Block : '||l_procedure,120);
1141 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
1142 p_token_name(1) := 'FUNCTION';
1143 p_token_value(1) := l_procedure;
1144 p_token_name(1) := 'SQLERRMC';
1145 p_token_value(1) := sqlerrm;
1146 END check_ltc_entry;
1147 --
1148 -- End of Private Procedures
1149 --
1150
1151 BEGIN
1152
1153 l_procedure := g_package ||'check_entry_value';
1154 g_debug := hr_utility.debug_enabled;
1155 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1156
1157 p_message_name := 'SUCCESS';
1158 pay_in_utils.null_message(p_token_name, p_token_value);
1159
1160 l_get_migrator_status:=hr_general.g_data_migrator_mode;
1161 -- Get the Element Name
1162
1163 OPEN c_perquisite_name;
1164 FETCH c_perquisite_name INTO l_element_name,l_element_Type_id,l_assignment_id;
1165 CLOSE c_perquisite_name;
1166
1167 IF (l_element_name IS NULL)
1168 THEN
1169 /*
1170 Check introduced for Leave Travel Conession
1171 */
1172 OPEN c_element_entry_details(p_element_entry_id);
1173 FETCH c_element_entry_details INTO l_element_start_date,l_element_type_id,l_assignment_id;
1174 CLOSE c_element_entry_details;
1175
1176 l_inputvalue_id := pay_in_utils.get_input_value_id(l_element_start_date
1177 ,l_element_type_id
1178 ,'Component Name'
1179 );
1180
1181 OPEN c_element_entry_values(p_element_entry_id,l_inputvalue_id,l_element_start_date);
1182 FETCH c_element_entry_values INTO l_element_name;
1183 CLOSE c_element_entry_values;
1184
1185 IF (l_element_name IS NULL)
1186 THEN
1187 RETURN;
1188 END IF;
1189
1190 END IF;
1191 pay_in_utils.set_location(g_debug,'Element name is: '||l_element_name,20);
1192 pay_in_utils.set_location(g_debug,'Element_Type_id: '||l_element_Type_id,20);
1193 pay_in_utils.set_location(g_debug,'Assignment_id : '||l_assignment_id,20);
1194
1195
1196 IF l_element_name = 'Loan at Concessional Rate' THEN
1197 check_loan_entry(l_element_name,l_element_Type_id,l_assignment_id);
1198
1199 ELSIF l_element_name = 'Motor Car Perquisite' THEN
1200 check_benefit_dates(l_element_Type_id);
1201 if g_debug then
1202 pay_in_utils.trace('p_message_name : ',p_message_name);
1203 end if;
1204 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1205 check_motorcar_entry(l_element_name,l_element_Type_id,l_assignment_id);
1206 --Bugfix 3982447 Start
1207 if g_debug then
1208 pay_in_utils.trace('p_message_name : ',p_message_name);
1209 end if;
1210 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1211
1212 if g_debug then
1213 pay_in_utils.trace('p_element_entry_id : ',p_element_entry_id);
1214 end if;
1215
1216 OPEN c_curr_entry_value (p_element_entry_id
1217 ,'Category of Car'
1218 ,l_element_type_id );
1219 FETCH c_curr_entry_value INTO l_dep_value1,l_entry_value_id;
1220 CLOSE c_curr_entry_value;
1221
1222 if g_debug then
1223 pay_in_utils.trace('l_dep_value1 : ',l_dep_value1);
1224 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1225 end if;
1226
1227 IF l_dep_value1 ='OWN_EMPLOYEE' THEN
1228 OPEN c_curr_entry_value (p_element_entry_id
1229 ,'Operational Expenses by'
1230 ,l_element_type_id );
1231 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1232 CLOSE c_curr_entry_value;
1233
1234 if g_debug then
1235 pay_in_utils.trace('l_dep_value2 : ',l_dep_value2);
1236 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1237 end if;
1238
1239
1240 IF l_dep_value2 = 'EMPLOYEE' THEN
1241 pay_in_utils.null_message(p_token_name, p_token_value);
1242 p_message_name := 'PER_IN_INVALID_PERQUISITE';
1243 pay_in_utils.set_location(g_debug,'Invalid perquisite ...'||l_procedure,25);
1244 RETURN;
1245 END IF;
1246 END IF;
1247 --Bugfix 3982447 End
1248
1249 ELSIF l_element_name = 'Company Accommodation' THEN
1250 --
1251
1252
1253 -- Start of 'Company Accommodation'
1254 --
1255 check_benefit_dates(l_element_Type_id);
1256
1257 if g_debug then
1258 pay_in_utils.trace('p_message_name : ',p_message_name);
1259 end if;
1260
1261 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1262
1263 OPEN c_curr_entry_value (p_element_entry_id
1264 ,'Property'
1265 ,l_element_type_id );
1266 FETCH c_curr_entry_value INTO l_dep_value1,l_entry_value_id;
1267 CLOSE c_curr_entry_value;
1268
1269 if g_debug then
1270 pay_in_utils.trace('l_dep_value1 : ',l_dep_value1);
1271 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1272 end if;
1273 --
1274 -- Check value interdependency Start
1275 --
1276 IF l_dep_value1 ='RENT' THEN
1277 OPEN c_curr_entry_value (p_element_entry_id
1278 ,'Rent Paid by Employer'
1279 ,l_element_type_id );
1280 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1281 CLOSE c_curr_entry_value;
1282
1283 if g_debug then
1284 pay_in_utils.trace('l_dep_value2 : ',l_dep_value2);
1285 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1286 end if;
1287
1288 IF l_dep_value2 IS NULL or l_dep_value2 = 0 THEN
1289 pay_in_utils.null_message(p_token_name, p_token_value);
1290 p_message_name := 'PER_IN_ENTRY_VALUE_ZERO';
1291 p_token_name(1) := 'TOKEN1';
1292 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','RENT_EMPLOLYER');
1293 p_token_name(2) := 'TOKEN2';
1294 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PROPERTY');
1295 p_token_name(3) := 'TOKEN3';
1296 p_token_value(3) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','LEASED');
1297
1298 pay_in_utils.set_location(g_debug,'Company Accommodation '||l_procedure,25);
1299 RETURN;
1300 END IF;
1301 -- Bugfix 3991117 Start
1302 ELSIF l_dep_value1 = 'OWN' THEN
1303 OPEN c_curr_entry_value (p_element_entry_id
1304 ,'Rent Paid by Employer'
1305 ,l_element_type_id );
1306 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1307 CLOSE c_curr_entry_value;
1308
1309 if g_debug then
1310 pay_in_utils.trace('l_dep_value2 : ',l_dep_value2);
1311 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1312 end if;
1313
1314 IF l_dep_value2 IS NOT NULL AND l_dep_value2 <> 0 THEN
1315 pay_in_utils.null_message(p_token_name, p_token_value);
1316 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
1317 p_token_name(1) := 'TOKEN';
1318 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','COMP_ACC');
1319 pay_in_utils.set_location(g_debug,'Company Accommodation '||l_procedure,28);
1320 RETURN;
1321 END IF;
1322
1323
1324 -- Bugfix 3991117 End
1325 END IF;
1326 --
1327 -- Check value interdependency End
1328 --
1329 --
1330 -- End of Company Accommodaiton
1331 --
1332 ELSIF l_element_name ='Company Movable Assets' THEN
1333 check_benefit_dates(l_element_Type_id);
1334 /* Bug Fix 4533671
1335 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1336 --
1337 -- Start of Company Movable Assets
1338 --
1339 OPEN c_curr_entry_value (p_element_entry_id
1340 ,'Usage'
1341 ,l_element_type_id );
1342 FETCH c_curr_entry_value INTO l_dep_value1,l_entry_value_id;
1343 CLOSE c_curr_entry_value;
1344
1345 --
1346 -- Check value interdependency Start
1347 --
1348 IF l_dep_value1 ='SOLD' THEN
1349 OPEN c_curr_entry_value (p_element_entry_id
1350 ,'Date of Purchase'
1351 ,l_element_type_id );
1352 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1353 CLOSE c_curr_entry_value;
1354
1355 IF l_dep_value2 IS NULL THEN
1356 pay_in_utils.set_location(g_debug,'ltc '||l_procedure,25);
1357 pay_in_utils.null_message(p_token_name, p_token_value);
1358 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1359 p_token_name(1) := 'TOKEN1';
1360 p_token_value(1) := 'Date of Purchase';
1361 p_token_name(2) := 'TOKEN2';
1362 p_token_value(2) := 'Usage';
1363 p_token_name(3) := 'TOKEN3';
1364 p_token_value(3) := 'Sold to Employee';
1365 RETURN;
1366 END IF;
1367 --
1368 -- Check value interdependency End
1369 --
1370 END IF;*/
1371 --
1372 -- End of Company Movable Assets
1373 --
1374
1375
1376 ELSIF l_element_name = 'Leave Travel Concession' THEN
1377 check_ltc_entry(l_element_name,l_element_Type_id,l_assignment_id);
1378 ELSIF l_element_name = 'Free Education' THEN
1379 check_benefit_dates(l_element_Type_id);
1380 END IF;
1381
1382 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
1386 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
1387 p_token_name(1) := 'FUNCTION';
1388 p_token_value(1) := l_procedure;
1389 p_token_name(2) := 'SQLERRMC';
1390 p_token_value(2) := sqlerrm;
1391 END check_element_entry;
1392
1393 END per_in_perquisite_pkg;
[Home] [Help]