1 PACKAGE body per_in_perquisite_pkg as
2 /* $Header: peinperq.pkb 120.15 2012/01/19 09:17:53 rpahune 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 = 'EDUCATION' THEN
433 IF p_effective_start_date >= TO_DATE('01-04-2009','DD-MM-YYYY') THEN
434 IF l_loan_interest_type NOT IN ('EL_BOYS','EL_GIRLS','SBI_EL_BOYS','SBI_EL_GIRLS') THEN
435 pay_in_utils.set_location(g_debug,
436 'Leaving: ' || l_procedure,
437 100);
438 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
439 p_token_name(1) := 'TOKEN';
440 p_token_value(1) := 'Loan Type and Additional Information';
441 RETURN;
442 END IF;
443 END IF;
444 ELSIF l_loan_type = 'MORTGAGE' THEN
445 IF p_effective_start_date >= TO_DATE('01-04-2008','DD-MM-YYYY') THEN
446 IF l_loan_interest_type IS NULL THEN
447 pay_in_utils.set_location(g_debug,
448 'Leaving: ' || l_procedure,
449 100);
450 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
451 p_token_name(1) := 'TOKEN1';
452 p_token_value(1) := 'Additional Information';
453 p_token_name(2) := 'TOKEN2';
454 p_token_value(2) := 'Loan Type';
455 p_token_name(3) := 'TOKEN3';
456 p_token_value(3) := 'Mortgage Loan';
457 RETURN;
458 ELSIF l_loan_interest_type NOT IN ('MRTAGE_IMMOVABLE', 'MRTAGE_GOLD') THEN
459 pay_in_utils.set_location(g_debug,
460 'Leaving: ' || l_procedure,
461 100);
462 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
463 p_token_name(1) := 'TOKEN';
464 p_token_value(1) := 'Loan Type and Additional Information';
465 RETURN;
466 END IF;
467 END IF;
468 END IF;
469 END IF;
470 /*Check for Additional Information Ends*/
471
472 /* Start - Get entries of loan availed in current tax year */
473 l_tax_year_start := pay_in_tax_utils.get_financial_year_start(p_effective_date);
474 pay_in_utils.set_location(g_debug,
475 'Tax Year Start: ' || l_tax_year_start,
476 30);
477
478 p_cnt := 0;
479 FOR i IN c_entries_start_tax_yr(l_tax_year_start,
480 l_assignment_id,
481 l_element_name) LOOP
482 l_element_entry_id(p_cnt) := i.element_entry_id;
483 l_element_type_id_tab (p_cnt) := i.element_type_id;
484 /*Added for the bugfix 6469684*/
485 OPEN c_input_value_id(l_element_type_id_tab (p_cnt), 'Loan Principal Amount');
486 FETCH c_input_value_id
487 INTO l_loan_input_value_id(p_cnt);
488 CLOSE c_input_value_id;
489
490 OPEN c_input_value_id(l_element_type_id_tab(p_cnt), 'Taxable Flag');
491 FETCH c_input_value_id
492 INTO l_tax_input_value_id(p_cnt);
493 CLOSE c_input_value_id;
494 p_cnt := p_cnt + 1;
495 END LOOP;
496 /* End - Get entries of loan avialed in current tax year */
497
498 pay_in_utils.set_location(g_debug, 'Entry Count: ' || p_cnt, 40);
499 l_principal_amt_in_tax_yr := 0;
500
501 /* Start - Find the sum of Loan Principal Amount*/
502 FOR j IN 0 .. p_cnt - 1 LOOP
503 pay_in_utils.set_location(g_debug,
504 'Entry id and Input value id : ' ||
505 l_element_entry_id(j) || l_loan_input_value_id(j),
506 50);
507
508 OPEN c_element_entry_values(l_element_entry_id(j),
509 l_loan_input_value_id(j),
510 p_effective_date);
511 FETCH c_element_entry_values
512 INTO l_curr_principal_amt;
513
514 l_principal_amt_in_tax_yr := l_principal_amt_in_tax_yr +
515 nvl(l_curr_principal_amt, 0);
516
517 CLOSE c_element_entry_values;
518 END LOOP;
519 /* End - Find the sum of Loan Principal Amount*/
520
521 OPEN c_global_value('IN_MAX_LOAN_AMT_EXEMPTION');
522 FETCH c_global_value
523 INTO l_gbl_value;
524 CLOSE c_global_value;
525
526 pay_in_utils.set_location(g_debug,
527 'Total Principal Amount: ' ||
528 l_principal_amt_in_tax_yr,
529 60);
530 --
531 -- start - Check if exemption limit is exceeded
532 --
533
534
535 l_get_migrator_status := hr_general.g_data_migrator_mode;
536 hr_general.g_data_migrator_mode := 'Y';
537
538 IF l_principal_amt_in_tax_yr > l_gbl_value then
539 /* Update the Taxable Flag to 'Y' when limit is exceeded */
540 FOR j IN 0 .. P_CNT - 1 LOOP
541
542 pay_in_utils.set_location(g_debug,
543 'Changing the following entries : ' ||
544 l_element_entry_id(j),
545 70);
546 UPDATE pay_element_entry_values_f peev
547 SET peev.screen_entry_value = 'Y'
548 WHERE peev.element_entry_id = l_element_entry_id(j)
549 AND peev.input_value_id = l_tax_input_value_id(j)
550 AND p_effective_date BETWEEN peev.effective_start_date and
551 peev.effective_end_date
552 AND nvl(peev.screen_entry_value, 'N') = 'N';
553 END LOOP;
554 ELSE
555 FOR j IN 0 .. P_CNT - 1 LOOP
556 /* Update the Taxable Flag to 'N' when the user accidentally enters incorrect values previously */
557 pay_in_utils.set_location(g_debug,
558 'Changing the following entries : ' ||
559 l_element_entry_id(j),
560 80);
561 UPDATE pay_element_entry_values_f peev
562 SET peev.screen_entry_value = 'N'
563 WHERE peev.element_entry_id = l_element_entry_id(j)
564 AND peev.input_value_id = l_tax_input_value_id(j)
565 AND p_effective_date BETWEEN peev.effective_start_date and
566 peev.effective_end_date
567 AND nvl(peev.screen_entry_value, 'Y') = 'Y';
568 END LOOP;
569
570 END IF;
571
572 hr_general.g_data_migrator_mode := l_get_migrator_status;
573 --
574 -- End - Check if exemption limit is exceeded
575 --
576 /* delete the PL/SQL table */
577 l_element_entry_id.delete;
578 pay_in_utils.set_location(g_debug, 'Leaving: ' || l_procedure, 90);
579 EXCEPTION
580 WHEN OTHERS THEN
581 IF c_entries_start_tax_yr%ISOPEN THEN
582 CLOSE c_entries_start_tax_yr;
583 END IF;
584 IF c_input_value_id%ISOPEN THEN
585 CLOSE c_input_value_id;
586 END IF;
587 IF c_element_entry_values%ISOPEN THEN
588 CLOSE c_element_entry_values;
589 END IF;
590 IF c_global_value%ISOPEN THEN
591 CLOSE c_global_value;
592 END IF;
593
594 pay_in_utils.set_location(g_debug,
595 'Leaving FROM Exception Block : ' ||
596 l_procedure,
597 100);
598 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
599 p_token_name(1) := 'FUNCTION';
600 p_token_value(1) := l_procedure;
601 p_token_name(2) := 'SQLERRMC';
602 p_token_value(2) := sqlerrm;
603 END check_loan_entry;
604
605
606 ---------------------------------------------------------------------------
607 -- --
608 -- Name : CHECK_MOTORCAR_ENTRY --
609 -- Type : Procedure --
610 -- Access : Private --
611 -- Description : Procedure is the driver procedure to validate --
612 -- Motor car entries. --
613 -- Parameters : --
614 -- IN : l_element_name IN VARCHAR2 --
615 -- : l_element_Type_id IN NUMBER --
616 -- : l_assignment_id IN NUMBER --
617
618 ---------------------------------------------------------------------------
619
620 PROCEDURE check_motorcar_entry(l_element_name VARCHAR2
621 ,l_element_Type_id NUMBER
622 ,l_assignment_id NUMBER)
623 IS
624 l_first_count NUMBER;
625 TYPE tab_input_value_name IS TABLE OF pay_input_values_f.name%TYPE INDEX BY BINARY_INTEGER;
626 l_input_value_name tab_input_value_name;
627
628 TYPE tab_effective_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
629 l_eff_start_date tab_effective_date;
630 p_cnt NUMBER;
631 l_procedure VARCHAR2(100);
632 p_input_value_id_start number;
633 p_input_value_id_end number;
634
635 l_benefit_start pay_element_entry_values_f.screen_entry_value%TYPE;
636 l_benefit_end pay_element_entry_values_f.screen_entry_value%TYPE;
637 l_benefit_start_date date;
638 l_benefit_end_date date;
639 l_type_automotive pay_element_entry_values_f.screen_entry_value%TYPE;
640
641 BEGIN
642
643 l_procedure := g_package ||'check_motorcar_entry';
644 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
645
646 if g_debug then
647 pay_in_utils.trace('******************************','********************');
648 pay_in_utils.trace('l_element_name : ',l_element_name);
649 pay_in_utils.trace('l_element_Type_id : ',l_element_Type_id);
650 pay_in_utils.trace('l_assignment_id : ',l_assignment_id);
651 pay_in_utils.trace('******************************','********************');
652 end if;
653
654 p_message_name := 'SUCCESS';
655 pay_in_utils.null_message(p_token_name, p_token_value);
656
657 pay_in_utils.set_location(g_debug,l_procedure,11);
658
659 OPEN c_input_value_id(l_element_type_id
660 ,'Type of Automotive');
661 FETCH c_input_value_id INTO p_input_value_id;
662 CLOSE c_input_value_id;
663
664 if g_debug then
665 pay_in_utils.trace('p_input_value_id : ',p_input_value_id);
666 end if;
667
668 pay_in_utils.set_location(g_debug,l_procedure,12);
669
670 OPEN c_element_entry_values(p_element_entry_id
671 , p_input_value_id
672 , p_effective_date);
673 FETCH c_element_entry_values INTO l_type_automotive;
674 CLOSE c_element_entry_values;
675
676 if g_debug then
677 pay_in_utils.trace('l_type_automotive : ',l_type_automotive);
678 end if;
679
680
681 IF l_type_automotive = 'CAR' THEN -- Bugfix 4049484
682
683 OPEN c_input_value_id(l_element_type_id
684 ,'Benefit Start Date');
685 FETCH c_input_value_id INTO p_input_value_id_start;
686 CLOSE c_input_value_id;
687
688 if g_debug then
689 pay_in_utils.trace('p_input_value_id_start : ',p_input_value_id_start);
690 end if;
691
692 OPEN c_input_value_id(l_element_type_id
693 ,'Benefit End Date');
694 FETCH c_input_value_id INTO p_input_value_id_end;
695 CLOSE c_input_value_id;
696
697 if g_debug then
698 pay_in_utils.trace('p_input_value_id_end : ',p_input_value_id_end);
699 end if;
700
701 OPEN c_element_entry_values(p_element_entry_id
702 , p_input_value_id_start
703 , p_effective_date);
704 FETCH c_element_entry_values INTO l_benefit_start;
705 CLOSE c_element_entry_values;
706
707 if g_debug then
708 pay_in_utils.trace('l_benefit_start : ',l_benefit_start);
709 end if;
710
711 OPEN c_element_entry_values(p_element_entry_id
712 , p_input_value_id_end
713 , p_effective_date);
714 FETCH c_element_entry_values INTO l_benefit_end;
715 CLOSE c_element_entry_values;
716
717 if g_debug then
718 pay_in_utils.trace('l_benefit_end : ',l_benefit_end);
719 end if;
720
721 l_benefit_start_date :=fnd_Date.canonical_to_date(l_benefit_start);
722 l_benefit_end_date :=fnd_Date.canonical_to_date(l_benefit_end);
723
724 if g_debug then
725 pay_in_utils.trace('l_benefit_start_date : ',l_benefit_start_date);
726 pay_in_utils.trace('l_benefit_end_date : ',l_benefit_end_date);
727 end if;
728
729 -- Bugfix 4049484
730 i:=1;
731 l_element_entry_id(0) := p_element_entry_id;
732 l_eff_start_date(0) := l_benefit_start_date;
733
734 /* Get the entries of Motor Car that overlap with the current entry */
735 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);
736 LOOP
737 FETCH c_element_entries INTO l_element_entry_id(i),l_eff_start_date(i);
738 EXIT WHEN c_element_entries%NOTFOUND;
739 i :=i+1;
740 END LOOP;
741 CLOSE c_element_entries;
742
743
744 p_cnt := l_element_entry_id.COUNT;
745 /* Start - Perform the following checks when there are more than one entry for Motor Car */
746
747 IF p_cnt > 1 THEN -- Bugfix 4049484
748 pay_in_utils.set_location(g_debug,' Entry count is : '||p_cnt,40);
749
750
751 /* Get the input value id */
752 l_input_value_name(0) := 'Type of Automotive';
753 l_input_value_name(1) := 'Category of Car';
754 l_input_value_name(2) := 'Operational Expenses by';
755 l_input_value_name(3) := 'Usage of Car';
756
757 FOR i in 0..3 LOOP
758 OPEN c_input_value_id(l_element_type_id
759 ,l_input_value_name(i)) ;
760 FETCH c_input_value_id
761 INTO l_input_value_id(i);
762 CLOSE c_input_value_id;
763 END LOOP;
764
765 pay_in_utils.set_location(g_debug,l_procedure,50);
766
767 l_first_count :=0;
768
769 /* LOOP Start */
770 FOR i in 0..p_cnt-1 LOOP
771 pay_in_utils.set_location(g_debug,l_procedure,60);
772
773 IF l_element_entry_value.COUNT>0 THEN
774 l_element_entry_value.delete;
775 END IF;
776
777 pay_in_utils.set_location(g_debug,l_procedure,70);
778
779 FOR j IN 0..3 LOOP
780 OPEN c_element_entry_values(l_element_entry_id(i)
781 , l_input_value_id(j)
782 , l_eff_start_date(i));
783 FETCH c_element_entry_values INTO l_element_entry_value(j);
784 CLOSE c_element_entry_values;
785 END LOOP;
786
787 pay_in_utils.set_location(g_debug,l_procedure,80);
788
789 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
790 pay_in_utils.set_location(g_debug,l_procedure,90);
791 --
792 -- Check that no more than one entry with the above values exist
793 --
794 IF l_first_count <> 0 THEN
795 pay_in_utils.set_location(g_debug,'Invalid entry Motor Car More than one entry : '||l_procedure,100);
796 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
797 p_token_name(1) := 'TOKEN';
798 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','MOTOR_CAR');
799 RETURN;
800 END IF;
801 l_first_count := l_first_count +1;
802 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
803 NULL;
804 ELSE
805 /* Raise an error for all other combination of values */
806 pay_in_utils.set_location(g_debug,'Invalid entry motor car : '||l_procedure,110);
807 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
808 p_token_name(1) := 'TOKEN';
809 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','MOTOR_CAR');
810 RETURN;
811 END IF;
812
813 END LOOP;
814 /* Loop End */
815 /* Check that exactly one entry satisfies the conditon*/
816 IF l_first_count <>1 THEN
817 pay_in_utils.set_location(g_debug,'Invalid entry in car entry : '||l_procedure,120);
818 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
819 p_token_name(1) := 'TOKEN';
820 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','MOTOR_CAR');
821 RETURN;
822 END IF;
823
824
825 END IF;
826 /* End - Perform the following checks when there are more than one entry for Motor Car */
827 IF l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;
828 IF l_input_value_id.COUNT > 0 THEN l_input_value_id.delete; END IF;
829 IF l_eff_start_date.COUNT > 0 THEN l_eff_start_date.delete; END IF;
830 IF l_element_entry_value.COUNT > 0 THEN l_element_entry_value.delete; END IF;
831 END IF; /* End - Type of Automotive is Motor Car */
832
833 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,130);
834 EXCEPTION
835 WHEN OTHERS THEN
836 IF c_input_value_id%ISOPEN THEN CLOSE c_input_value_id ; END IF;
837 IF c_global_value%ISOPEN THEN CLOSE c_global_value ; END IF;
838 pay_in_utils.set_location(g_debug,'Leaving FROM Exception Block : '||l_procedure,140);
839 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
840 p_token_name(1) := 'FUNCTION';
841 p_token_value(1) := l_procedure;
842 p_token_name(2) := 'SQLERRMC';
843 p_token_value(2) := sqlerrm;
844 END check_motorcar_entry;
845
846 ---------------------------------------------------------------------------
847 -- --
848 -- Name : CHECK_LTC_ENTRY --
849 -- Type : Procedure --
850 -- Access : Private --
851 -- Description : Procedure is the driver procedure to validate --
852 -- Motor car entries. --
853 -- Parameters : --
854 -- IN : l_element_name IN VARCHAR2 --
855 -- : l_element_Type_id IN NUMBER --
856 -- : l_assignment_id IN NUMBER --
857
858 ---------------------------------------------------------------------------
859 PROCEDURE check_ltc_entry(l_element_name VARCHAR2
860 ,l_element_Type_id NUMBER
861 ,l_assignment_id NUMBER)
862 IS
863 /* Cursor to find the LTC Block at the given effective Date */
864 CURSOR c_ltc_block(p_date DATE)
865 IS
866 SELECT hrl.lookup_code
867 ,hrl.meaning
868 FROM hr_lookups hrl
869 WHERE hrl.lookup_type ='IN_LTC_BLOCK'
870 AND to_number(to_char(p_date,'YYYY')) BETWEEN
871 to_number(SUBSTR(HRL.LOOKUP_CODE,1,4)) AND to_number(SUBSTR(HRL.LOOKUP_CODE,8,4)) ;
872
873 /* Cursor to find the LTC Availed in Previous employment given the LTC Block Start and End Dates */
874 CURSOR c_prev_employer_ltc_availed(p_start_date date
875 ,p_end_date date
876 ,p_assignment_id NUMBER)
877 IS
878 SELECT sum(nvl(ppm.pem_information8,0))
879 FROM per_previous_employers ppm,
880 per_all_assignments_f paa
881 WHERE paa.assignment_id = p_assignment_id
882 AND p_effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
883 AND paa.person_id =ppm.person_id
884 AND ppm.end_date BETWEEN p_start_date and p_end_date;
885
886 /* Cursor to Find the LTC Availed in Current Employment given the LTC Block Start and End Dates*/
887 CURSOR c_prev_blk_entry_value(p_element_Type_id NUMBER
888 ,p_start_date DATE
889 ,p_end_date DATE
890 ,p_assignment_id NUMBER
891 ,p_value_input number
892 ,p_blk_input number
893 ,p_prev_block VARCHAR2)
894 IS
895 SELECT count(*)
896 FROM pay_element_entries_f pee
897 ,pay_element_entry_values_f peev1
898 ,pay_element_entry_values_f peev2
899 WHERE pee.assignment_id = p_assignment_id
900 AND pee.element_type_id = p_element_Type_id
901 AND peev1.input_value_id = p_value_input
902 AND peev1.element_entry_id =peev2.element_entry_id
903 AND peev2.input_value_id = p_blk_input
904 AND peev2.screen_entry_value = p_prev_block
905 AND nvl(peev1.screen_entry_value,'N')='N'
906 AND peev1.element_entry_id =pee.element_entry_id
907 AND pee.effective_start_date BETWEEN p_start_date AND p_end_date
908 AND peev1.effective_start_date BETWEEN p_start_date AND p_end_date
909 AND peev2.effective_start_date BETWEEN p_start_date AND p_end_date;
910
911 /* Cursor to find if LTC Exemption is already carried Over from previous Block*/
912 CURSOR c_exemption_availed(p_element_Type_id NUMBER
913 ,p_start_date DATE
914 ,p_end_date DATE
915 ,p_assignment_id NUMBER
916 ,p_value_input NUMBER
917 ,p_blk_input NUMBER
918 ,p_curr_block VARCHAR2)
919 IS
920 SELECT count(*)
921 FROM pay_element_entries_f pee
922 ,pay_element_entry_values_f peev1
923 ,pay_element_entry_values_f peev2
924 WHERE pee.assignment_id = p_assignment_id
925 AND pee.element_type_id = p_element_Type_id
926 AND peev1.input_value_id = p_value_input
927 AND peev1.element_entry_id =peev2.element_entry_id
928 AND peev2.input_value_id = p_blk_input
929 AND peev2.screen_entry_value = p_curr_block
930 AND peev1.screen_entry_value='Y'
931 AND peev1.element_entry_id =pee.element_entry_id
932 AND pee.effective_start_date BETWEEN p_start_date AND p_end_date
933 AND peev1.effective_start_date BETWEEN p_start_date AND p_end_date
934 AND peev2.effective_start_date BETWEEN p_start_date AND p_end_date;
935
936 l_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
937
938
939 l_curr_block HR_LOOKUPS.LOOKUP_CODE%TYPE;
940 l_curr_period HR_LOOKUPS.meaning%TYPE;
941 l_current_year NUMBER;
942 l_current_blk_start NUMBER;
943
944 l_prev_blk_date DATE;
945 l_prev_block HR_LOOKUPS.LOOKUP_CODE%TYPE;
946 l_prev_period HR_LOOKUPS.meaning%TYPE;
947
948 l_carry_over_id NUMBER;
949 l_journey_block_id NUMBER;
950
951 l_cur_emplr_prev_blk NUMBER;
952 l_prev_emplr_curr_blk NUMBER;
953 l_prev_emplr_prev_blk NUMBER;
954 l_exemption NUMBER;
955 l_procedure VARCHAR2(100);
956
957
958 l_prev_start_date DATE;
959 l_prev_end_date DATE;
960 l_curr_start_date DATE;
961 l_curr_end_date DATE;
962 l_max_with_carry_over NUMBER;
963 l_max_ltc NUMBER;
964
965
966 BEGIN
967 l_procedure := g_package ||'check_ltc_entry';
968 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
969
970 if g_debug then
971 pay_in_utils.trace('******************************','********************');
972 pay_in_utils.trace('l_element_name : ',l_element_name);
973 pay_in_utils.trace('l_element_Type_id : ',l_element_Type_id);
974 pay_in_utils.trace('l_assignment_id : ',l_assignment_id);
975 pay_in_utils.trace('******************************','********************');
976 end if;
977
978 p_message_name := 'SUCCESS';
979 pay_in_utils.null_message(p_token_name, p_token_value);
980
981 OPEN c_curr_entry_value(p_element_entry_id,'Carryover from Prev Block',l_element_type_id);
982 FETCH c_curr_entry_value INTO l_entry_value,l_entry_value_id;
983 CLOSE c_curr_entry_value;
984
985 if g_debug then
986 pay_in_utils.trace('l_entry_value : ',l_entry_value);
987 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
988 end if;
989 pay_in_utils.set_location(g_debug,l_procedure,15);
990
991 --
992 -- Find value held in globals before any further Processing
993 -- Fix 3956926
994 --
995
996 OPEN c_global_value('IN_MAX_JOURNEY_BLOCK_LTC');
997 FETCH c_global_value INTO l_max_ltc;
998 CLOSE c_global_value;
999
1000 if g_debug then
1001 pay_in_utils.trace('l_max_ltc : ',l_max_ltc);
1002 end if;
1003
1004 OPEN c_global_value('IN_JOURNEY_CARRY_OVER');
1005 FETCH c_global_value INTO l_max_with_carry_over;
1006 CLOSE c_global_value;
1007
1008 if g_debug then
1009 pay_in_utils.trace('l_max_with_carry_over : ',l_max_with_carry_over);
1010 end if;
1011 --
1012 --Start of Check the value in Carryover from Prev Block
1013 --
1014 IF nvl(l_entry_value,'N') ='Y' THEN
1015
1016 -- Check that the current year is the first year in LTC Block.Otherwise,raise an error
1017 OPEN c_ltc_block(p_effective_date);
1018 FETCH c_ltc_block INTO l_curr_block,l_curr_period;
1019 CLOSE c_ltc_block;
1020
1021 if g_debug then
1022 pay_in_utils.trace('l_curr_block : ',l_curr_block);
1023 pay_in_utils.trace('l_curr_period : ',l_curr_period);
1024 pay_in_utils.trace('p_effective_date : ',p_effective_date);
1025 end if;
1026 pay_in_utils.set_location(g_debug,l_procedure,20);
1027
1028 l_current_year := to_number(to_char(p_effective_date,'YYYY'));
1029 l_current_blk_start := to_number(substr(l_curr_block,1,4));
1030 IF l_current_year <> l_current_blk_start THEN
1031 pay_in_utils.set_location(g_debug,'Leaving.. '||l_procedure,30);
1032 p_message_name := 'PER_IN_LTC_CARRY_OVER'; -- Fix 3956926
1033 RETURN;
1034 END IF;
1035
1036 l_curr_start_date := to_date(substr(l_curr_period,1,11),'DD-MM-YYYY');
1037 l_curr_end_date := to_date(substr(l_curr_period,15,11),'DD-MM-YYYY');
1038
1039 if g_debug then
1040 pay_in_utils.trace('p_effective_start_date : ',p_effective_start_date);
1041 end if;
1042 -- Get the Previous Block start and End Dates
1043 l_prev_blk_date := ADD_MONTHS(p_effective_start_date,-48);
1044 OPEN c_ltc_block(l_prev_blk_date);
1045 FETCH c_ltc_block INTO l_prev_block,l_prev_period;
1046 close c_ltc_block;
1047
1048 if g_debug then
1049 pay_in_utils.trace('l_prev_block : ',l_prev_block);
1050 pay_in_utils.trace('l_prev_period : ',l_prev_period);
1051 end if;
1052 pay_in_utils.set_location(g_debug,l_procedure,40);
1053
1054 l_prev_start_date := to_date(substr(l_prev_period,1,11),'DD-MM-YYYY');
1055 l_prev_end_date := to_date(substr(l_prev_period,15,11),'DD-MM-YYYY');
1056
1057
1058 -- Get LTC Availed in Current employment in previous LTC Block
1059 OPEN c_input_value_id(l_element_Type_id
1060 ,'Carryover from Prev Block');
1061 FETCH c_input_value_id INTO l_carry_over_id;
1062 CLOSE c_input_value_id;
1063
1064 if g_debug then
1065 pay_in_utils.trace('l_carry_over_id : ',l_carry_over_id);
1066 end if;
1067
1068 OPEN c_input_value_id(l_element_Type_id
1069 ,'LTC Journey Block');
1070 FETCH c_input_value_id INTO l_journey_block_id;
1071 CLOSE c_input_value_id;
1072
1073 if g_debug then
1074 pay_in_utils.trace('l_journey_block_id : ',l_journey_block_id);
1075 end if;
1076
1077 pay_in_utils.set_location(g_debug,l_procedure,50);
1078
1079 OPEN c_prev_blk_entry_value(l_element_type_id
1080 ,l_prev_start_date
1081 ,l_prev_end_date
1082 ,l_assignment_id
1083 ,l_carry_over_id
1084 ,l_journey_block_id
1085 ,l_prev_block);
1086 FETCH c_prev_blk_entry_value INTO l_cur_emplr_prev_blk;
1087 CLOSE c_prev_blk_entry_value;
1088
1089 if g_debug then
1090 pay_in_utils.trace('l_cur_emplr_prev_blk : ',l_cur_emplr_prev_blk);
1091 end if;
1092
1093 OPEN c_prev_employer_ltc_availed(l_prev_start_date
1094 ,l_prev_end_date
1095 ,l_assignment_id );
1096 FETCH c_prev_employer_ltc_availed INTO
1097 l_prev_emplr_prev_blk;
1098 CLOSE c_prev_employer_ltc_availed;
1099
1100 if g_debug then
1101 pay_in_utils.trace('l_prev_emplr_prev_blk : ',l_prev_emplr_prev_blk);
1102 end if;
1103
1104 pay_in_utils.set_location(g_debug,l_procedure,60);
1105
1106
1107 /* Check if carry over is valid */
1108 IF ( nvl(l_cur_emplr_prev_blk,0) + nvl(l_prev_emplr_prev_blk,0) >=l_max_ltc) THEN
1109 pay_in_utils.set_location(g_debug,'Leaving...'||l_procedure,70);
1110 p_message_name := 'PER_IN_LTC_EXEMPTION_AVAILED';
1111 RETURN;
1112 END IF;
1113
1114
1115 OPEN c_exemption_availed(l_element_type_id
1116 ,l_curr_start_date
1117 ,l_curr_end_date
1118 ,l_assignment_id
1119 ,l_carry_over_id
1120 ,l_journey_block_id
1121 ,l_curr_block);
1122 FETCH c_exemption_availed INTO l_exemption;
1123 CLOSE c_exemption_availed;
1124
1125 if g_debug then
1126 pay_in_utils.trace('l_exemption : ',l_exemption);
1127 end if;
1128
1129 pay_in_utils.set_location(g_debug,l_procedure,80);
1130
1131 /* Check if Carry Over has already been availed */
1132 IF (nvl(l_exemption,0) > (l_max_with_carry_over - l_max_ltc) ) THEN
1133 pay_in_utils.set_location(g_debug,'Leaving... '||l_procedure,90);
1134 p_message_name := 'PER_IN_LTC_EXEMPTION_AVAILED';
1135 RETURN;
1136 END IF;
1137 pay_in_utils.set_location(g_debug,l_procedure,100);
1138
1139 END IF;
1140
1141 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,110);
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144 IF c_curr_entry_value%ISOPEN THEN CLOSE c_curr_entry_value ; END IF;
1145 IF c_ltc_block%ISOPEN THEN CLOSE c_ltc_block ; END IF;
1146 IF c_prev_blk_entry_value%ISOPEN THEN CLOSE c_prev_blk_entry_value ; END IF;
1147 IF c_exemption_availed%ISOPEN THEN CLOSE c_exemption_availed ; END IF;
1148 IF c_input_value_id%ISOPEN THEN CLOSE c_input_value_id ; END IF;
1149 IF c_prev_employer_ltc_availed%ISOPEN THEN CLOSE c_prev_employer_ltc_availed ; END IF;
1150 IF c_global_value%ISOPEN THEN CLOSE c_global_value ; END IF;
1151
1152 pay_in_utils.set_location(g_debug,'Leaving FROM Exception Block : '||l_procedure,120);
1153 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
1154 p_token_name(1) := 'FUNCTION';
1155 p_token_value(1) := l_procedure;
1156 p_token_name(1) := 'SQLERRMC';
1157 p_token_value(1) := sqlerrm;
1158 END check_ltc_entry;
1159 --
1160 -- End of Private Procedures
1161 --
1162
1163 BEGIN
1164
1165 l_procedure := g_package ||'check_entry_value';
1166 g_debug := hr_utility.debug_enabled;
1167 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1168
1169 p_message_name := 'SUCCESS';
1170 pay_in_utils.null_message(p_token_name, p_token_value);
1171
1172 l_get_migrator_status:=hr_general.g_data_migrator_mode;
1173 -- Get the Element Name
1174
1175 OPEN c_perquisite_name;
1176 FETCH c_perquisite_name INTO l_element_name,l_element_Type_id,l_assignment_id;
1177 CLOSE c_perquisite_name;
1178
1179 IF (l_element_name IS NULL)
1180 THEN
1181 /*
1182 Check introduced for Leave Travel Conession
1183 */
1184 OPEN c_element_entry_details(p_element_entry_id);
1185 FETCH c_element_entry_details INTO l_element_start_date,l_element_type_id,l_assignment_id;
1186 CLOSE c_element_entry_details;
1187
1188 l_inputvalue_id := pay_in_utils.get_input_value_id(l_element_start_date
1189 ,l_element_type_id
1190 ,'Component Name'
1191 );
1192
1193 OPEN c_element_entry_values(p_element_entry_id,l_inputvalue_id,l_element_start_date);
1194 FETCH c_element_entry_values INTO l_element_name;
1195 CLOSE c_element_entry_values;
1196
1197 IF (l_element_name IS NULL)
1198 THEN
1199 RETURN;
1200 END IF;
1201
1202 END IF;
1203 pay_in_utils.set_location(g_debug,'Element name is: '||l_element_name,20);
1204 pay_in_utils.set_location(g_debug,'Element_Type_id: '||l_element_Type_id,20);
1205 pay_in_utils.set_location(g_debug,'Assignment_id : '||l_assignment_id,20);
1206
1207
1208 IF l_element_name = 'Loan at Concessional Rate' THEN
1209 check_loan_entry(l_element_name,l_element_Type_id,l_assignment_id);
1210
1211 ELSIF l_element_name = 'Motor Car Perquisite' THEN
1212 check_benefit_dates(l_element_Type_id);
1213 if g_debug then
1214 pay_in_utils.trace('p_message_name : ',p_message_name);
1215 end if;
1216 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1217 check_motorcar_entry(l_element_name,l_element_Type_id,l_assignment_id);
1218 --Bugfix 3982447 Start
1219 if g_debug then
1220 pay_in_utils.trace('p_message_name : ',p_message_name);
1221 end if;
1222 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1223
1224 if g_debug then
1225 pay_in_utils.trace('p_element_entry_id : ',p_element_entry_id);
1226 end if;
1227
1228 OPEN c_curr_entry_value (p_element_entry_id
1229 ,'Category of Car'
1230 ,l_element_type_id );
1231 FETCH c_curr_entry_value INTO l_dep_value1,l_entry_value_id;
1232 CLOSE c_curr_entry_value;
1233
1234 if g_debug then
1235 pay_in_utils.trace('l_dep_value1 : ',l_dep_value1);
1236 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1237 end if;
1238
1239 IF l_dep_value1 ='OWN_EMPLOYEE' THEN
1240 OPEN c_curr_entry_value (p_element_entry_id
1241 ,'Operational Expenses by'
1242 ,l_element_type_id );
1243 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1244 CLOSE c_curr_entry_value;
1245
1246 if g_debug then
1247 pay_in_utils.trace('l_dep_value2 : ',l_dep_value2);
1248 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1249 end if;
1250
1251
1252 IF l_dep_value2 = 'EMPLOYEE' THEN
1253 pay_in_utils.null_message(p_token_name, p_token_value);
1254 p_message_name := 'PER_IN_INVALID_PERQUISITE';
1255 pay_in_utils.set_location(g_debug,'Invalid perquisite ...'||l_procedure,25);
1256 RETURN;
1257 END IF;
1258 END IF;
1259 --Bugfix 3982447 End
1260
1261 ELSIF l_element_name = 'Company Accommodation' THEN
1262 --
1263
1264
1265 -- Start of 'Company Accommodation'
1266 --
1267 check_benefit_dates(l_element_Type_id);
1268
1269 if g_debug then
1270 pay_in_utils.trace('p_message_name : ',p_message_name);
1271 end if;
1272
1273 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1274
1275 OPEN c_curr_entry_value (p_element_entry_id
1276 ,'Property'
1277 ,l_element_type_id );
1278 FETCH c_curr_entry_value INTO l_dep_value1,l_entry_value_id;
1279 CLOSE c_curr_entry_value;
1280
1281 if g_debug then
1282 pay_in_utils.trace('l_dep_value1 : ',l_dep_value1);
1283 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1284 end if;
1285 --
1286 -- Check value interdependency Start
1287 --
1288 IF l_dep_value1 ='RENT' THEN
1289 OPEN c_curr_entry_value (p_element_entry_id
1290 ,'Rent Paid by Employer'
1291 ,l_element_type_id );
1292 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1293 CLOSE c_curr_entry_value;
1294
1295 if g_debug then
1296 pay_in_utils.trace('l_dep_value2 : ',l_dep_value2);
1297 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1298 end if;
1299
1300 IF l_dep_value2 IS NULL or l_dep_value2 = 0 THEN
1301 pay_in_utils.null_message(p_token_name, p_token_value);
1302 p_message_name := 'PER_IN_ENTRY_VALUE_ZERO';
1303 p_token_name(1) := 'TOKEN1';
1304 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','RENT_EMPLOLYER');
1305 p_token_name(2) := 'TOKEN2';
1306 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PROPERTY');
1307 p_token_name(3) := 'TOKEN3';
1308 p_token_value(3) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','LEASED');
1309
1310 pay_in_utils.set_location(g_debug,'Company Accommodation '||l_procedure,25);
1311 RETURN;
1312 END IF;
1313 -- Bugfix 3991117 Start
1314 ELSIF l_dep_value1 = 'OWN' THEN
1315 OPEN c_curr_entry_value (p_element_entry_id
1316 ,'Rent Paid by Employer'
1317 ,l_element_type_id );
1318 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1319 CLOSE c_curr_entry_value;
1320
1321 if g_debug then
1322 pay_in_utils.trace('l_dep_value2 : ',l_dep_value2);
1323 pay_in_utils.trace('l_entry_value_id : ',l_entry_value_id);
1324 end if;
1325
1326 IF l_dep_value2 IS NOT NULL AND l_dep_value2 <> 0 THEN
1327 pay_in_utils.null_message(p_token_name, p_token_value);
1328 p_message_name := 'PER_IN_INVALID_ELEMENT_ENTRY';
1329 p_token_name(1) := 'TOKEN';
1330 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','COMP_ACC');
1331 pay_in_utils.set_location(g_debug,'Company Accommodation '||l_procedure,28);
1332 RETURN;
1333 END IF;
1334
1335
1336 -- Bugfix 3991117 End
1337 END IF;
1338 --
1339 -- Check value interdependency End
1340 --
1341 --
1342 -- End of Company Accommodaiton
1343 --
1344 ELSIF l_element_name ='Company Movable Assets' THEN
1345 check_benefit_dates(l_element_Type_id);
1346 /* Bug Fix 4533671
1347 IF p_message_name <>'SUCCESS' THEN RETURN; END IF;
1348 --
1349 -- Start of Company Movable Assets
1350 --
1351 OPEN c_curr_entry_value (p_element_entry_id
1352 ,'Usage'
1353 ,l_element_type_id );
1354 FETCH c_curr_entry_value INTO l_dep_value1,l_entry_value_id;
1355 CLOSE c_curr_entry_value;
1356
1357 --
1358 -- Check value interdependency Start
1359 --
1360 IF l_dep_value1 ='SOLD' THEN
1361 OPEN c_curr_entry_value (p_element_entry_id
1362 ,'Date of Purchase'
1363 ,l_element_type_id );
1364 FETCH c_curr_entry_value INTO l_dep_value2,l_entry_value_id;
1365 CLOSE c_curr_entry_value;
1366
1367 IF l_dep_value2 IS NULL THEN
1368 pay_in_utils.set_location(g_debug,'ltc '||l_procedure,25);
1369 pay_in_utils.null_message(p_token_name, p_token_value);
1370 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1371 p_token_name(1) := 'TOKEN1';
1372 p_token_value(1) := 'Date of Purchase';
1373 p_token_name(2) := 'TOKEN2';
1374 p_token_value(2) := 'Usage';
1375 p_token_name(3) := 'TOKEN3';
1376 p_token_value(3) := 'Sold to Employee';
1377 RETURN;
1378 END IF;
1379 --
1380 -- Check value interdependency End
1381 --
1382 END IF;*/
1383 --
1384 -- End of Company Movable Assets
1385 --
1386
1387
1388 ELSIF l_element_name = 'Leave Travel Concession' THEN
1389 check_ltc_entry(l_element_name,l_element_Type_id,l_assignment_id);
1390 ELSIF l_element_name = 'Free Education' THEN
1391 check_benefit_dates(l_element_Type_id);
1392 END IF;
1393
1394 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
1398 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
1399 p_token_name(1) := 'FUNCTION';
1400 p_token_value(1) := l_procedure;
1401 p_token_name(2) := 'SQLERRMC';
1402 p_token_value(2) := sqlerrm;
1403 END check_element_entry;
1404
1405 END per_in_perquisite_pkg;
[Home] [Help]