[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_INDIA_BENEFITS
Source
1 PACKAGE body PAY_IN_INDIA_BENEFITS AS
2 /* $Header: pyinmed.pkb 120.18.12020000.5 2012/07/04 23:13:18 amnaraya ship $ */
3 --
4 -- Global Variables Section
5 --
6 g_legislation_code VARCHAR2(3);
7 g_approval_info_type VARCHAR2(40);
8 g_element_value_list t_element_values_tab;
9 g_list_index NUMBER;
10 g_assignment_id per_all_assignments_f.assignment_id%TYPE;
11 g_index_assignment_id per_all_assignments_f.assignment_id%TYPE;
12 g_is_valid BOOLEAN;
13 g_index_values_valid BOOLEAN;
14 g_package CONSTANT VARCHAR2(100) := 'pay_in_india_benefits.';
15 g_debug BOOLEAN;
16 --
17 -- The following type is declared to store all
18 -- the inputs values of tax elements.
19 --
20 type t_input_values_rec is record
21 (input_name pay_input_values_f.name%TYPE
22 ,input_value_id pay_input_values_f.input_value_id%TYPE
23 ,input_value pay_element_entry_values.screen_entry_value%TYPE);
24
25 type t_input_values_tab is table of t_input_values_rec
26 index by binary_integer;
27
28 PROCEDURE create_ltc_element
29 (
30 P_LTCBLOCK IN VARCHAR2
31 ,P_PLACE_FROM IN VARCHAR2
32 ,P_PLACE_TO IN VARCHAR2
33 ,P_MODE_CLASS IN VARCHAR2
34 ,P_CARRY_OVER IN VARCHAR2 DEFAULT NULL
35 ,P_SUBMITTED IN NUMBER
36 ,P_EXEMPTED IN NUMBER DEFAULT NULL
37 ,P_ELEMENT_ENTRY_ID IN OUT NOCOPY NUMBER
38 ,P_START_DATE IN DATE
39 ,P_END_DATE IN DATE
40 ,P_BILL_NUM IN VARCHAR2 DEFAULT NULL
41 ,P_EE_COMMENTS IN VARCHAR2 DEFAULT NULL
42 ,P_ER_COMMENTS IN VARCHAR2 DEFAULT NULL
43 ,P_LAST_UPDATED_DATE IN DATE
44 ,P_ASSIGNMENT_ID IN NUMBER
45 ,P_EMPLOYEE_ID IN NUMBER
46 ,P_ASSIGNMENT_EXTRA_INFO_ID IN NUMBER
47 ,P_ENTRY_DATE IN DATE DEFAULT NULL
48 ,p_employer_Contribution IN NUMBER
49 ,p_emp_element_entry_id IN NUMBER
50 ,p_emp_carry_over_flag IN VARCHAR2 DEFAULT NULL
51 ,p_create_flag OUT NOCOPY VARCHAR2
52 ,p_warnings OUT NOCOPY VARCHAR2
53 )
54 IS
55 CURSOR c_element_name(p_business_group_id NUMBER)
56 IS
57 SELECT hoi.org_information3
58 FROM hr_organization_information hoi
59 WHERE hoi.organization_id = p_business_group_id
60 AND org_information_context='PER_IN_REIMBURSE_ELEMENTS';
61
62
63
64 --Get Element Details (type id and link id)
65 CURSOR csr_element_details(p_assignment_id NUMBER
66 ,p_effective_date DATE
67 ,p_element_name VARCHAR2
68 )
69 IS
70 SELECT types.element_type_id
71 ,link.element_link_id
72 FROM per_assignments_f assgn
73 , pay_element_links_f link
74 , pay_element_types_f types
75 WHERE assgn.assignment_id = p_assignment_id
76 AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
77 ,P_ENTRY_DATE
78 ,types.element_type_id
79 )
80 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
81 AND link.business_group_id = assgn.business_group_id
82 AND link.element_type_id = types.element_type_id
83 AND types.element_type_id = p_element_name
84 AND p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
85 AND p_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
86 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
87
88
89 CURSOR c_input_rec(p_element_type_id NUMBER
93 SELECT inputs.name name
90 ,p_effective_date DATE
91 )
92 IS
94 , inputs.input_value_id id
95 , inputs.default_value value
96 FROM pay_element_types_f types
97 , pay_input_values_f inputs
98 WHERE types.element_type_id = p_element_type_id
99 AND inputs.element_type_id = types.element_type_id
100 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
101 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
102 ORDER BY inputs.display_sequence;
103
104
105
106
107 CURSOR c_get_ele_object_version(p_element_entryid NUMBER
108 )
109 IS
110 SELECT object_version_number
111 ,effective_start_date
112 FROM pay_element_entries_f pee
113 WHERE element_entry_id = p_element_entryid;
114 -- AND TO_CHAR(p_effective_date,'RRRRMM') = TO_CHAR(pee.effective_start_date,'RRRRMM') ;
115
116 CURSOR c_get_screen_value (p_element_entryid NUMBER
117 ,p_input NUMBER )
118 IS
119 SELECT screen_entry_value
120 FROM pay_element_entry_values_f
121 WHERE element_entry_id = p_element_entryid
122 AND input_value_id = p_input;
123
124
125 CURSOR c_check_element_entry(p_element_type_id NUMBER
126 ,p_effective_date DATE )
127 IS
128 SELECT pee.element_entry_id
129 FROM pay_element_entries_f pee
130 WHERE pee.element_type_id = p_element_type_id
131 AND pee.assignment_id = p_assignment_id
132 AND TO_CHAR(p_effective_date,'RRRRMM') = TO_CHAR(pee.effective_start_date,'RRRRMM') ;
133
134 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
135 IS
136 SELECT element_type_id
137 FROM pay_element_entries_f
138 WHERE element_entry_id = p_element_entryid;
139
140 CURSOR c_get_prev_amts
141 IS
142 SELECT pae.aei_information9,
143 pae.aei_information10
144 FROM per_assignment_extra_info pae
145 WHERE pae.assignment_extra_info_id = p_assignment_extra_info_id;
146
147 CURSOR c_element(p_element_type_id NUMBER)
148 IS
149 SELECT element_name
150 FROM pay_element_types_f
151 WHERE element_type_id = p_element_type_id ;
152
153 CURSOR c_ltc_carry_over(p_element_entry_id NUMBER)
154 IS SELECT nvl(peev.screen_entry_value,'N')
155 FROM pay_element_entry_values_f peev,
156 pay_input_values_f piv
157 WHERE peev.element_entry_id = p_element_entry_id
158 AND peev.input_value_id = piv.input_value_id
159 AND piv.name ='Carryover from Prev Block';
160
161 --Variables Initialization
162 TYPE t_input_values_rec IS RECORD
163 (input_name pay_input_values_f.name%TYPE
164 ,input_value_id pay_input_values_f.input_value_id%TYPE
165 ,value pay_input_values_f.default_value%TYPE
166 );
167
168 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
169
170 l_procedure VARCHAR2(250);
171 l_message VARCHAR2(250);
172 l_warnings BOOLEAN;
173 l_input_values_rec t_input_values_tab;
174 l_ben_input_values_rec t_input_values_tab;
175 l_business_group_id NUMBER;
176 l_medical_bill_element hr_organization_information.org_information2%TYPE ;
177 l_ltc_element hr_organization_information.org_information2%TYPE ;
178 l_element pay_element_types.element_name%TYPE ;
179 l_count NUMBER;
180 l_assignment_id NUMBER;
181 l_ltc_element_type_id NUMBER;
182 l_bill_element_link_id NUMBER;
183 l_ltc_element_link_id NUMBER;
184 l_element_entry_id NUMBER := NULL ;
185 l_start_date DATE ;
186 l_end_date DATE ;
187 l_effective_start_date DATE ;
188 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
189 l_entry_information5 pay_element_entries_f.entry_information2%TYPE ;
190 l_entry_information3 pay_element_entries_f.entry_information2%TYPE ;
191 l_submitted NUMBER ;
192 l_prev_submitted NUMBER ;
193 l_check NUMBER :=0;
194 l_exempted_amount NUMBER ;
195 l_prev_exempted_amount NUMBER ;
196 l_session NUMBER ;
197 l_carry_over VARCHAR2(10);
198 l_carry_over_flag VARCHAR2(10);
199 l_create_flag VARCHAR2(10) :='N';
200
201
202
203
204 BEGIN
205
206 fnd_msg_pub.initialize; --13767307
207 g_debug := hr_utility.debug_enabled;
208 p_warnings := 'TRUE';
209 l_procedure := g_package ||'create_ltc_element';
210 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
211 IF (g_debug)
212 THEN
213 pay_in_utils.trace('**************************************************','********************');
214 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
215 pay_in_utils.trace('P_LTCBLOCK',TO_CHAR (P_LTCBLOCK));
216 pay_in_utils.trace('P_PLACE_FROM',TO_CHAR (P_PLACE_FROM));
217 pay_in_utils.trace('P_PLACE_TO',TO_CHAR (P_PLACE_TO));
218 pay_in_utils.trace('P_MODE_CLASS',TO_CHAR (P_MODE_CLASS));
222 pay_in_utils.trace('P_ELEMENT_ENTRY_ID',TO_CHAR (P_ELEMENT_ENTRY_ID));
219 pay_in_utils.trace('P_CARRY_OVER',TO_CHAR (P_CARRY_OVER));
220 pay_in_utils.trace('P_SUBMITTED',TO_CHAR (P_SUBMITTED));
221 pay_in_utils.trace('P_EXEMPTED',TO_CHAR (P_EXEMPTED));
223 pay_in_utils.trace('P_START_DATE',TO_CHAR (P_START_DATE));
224 pay_in_utils.trace('P_END_DATE',TO_CHAR (P_END_DATE));
225 pay_in_utils.trace('P_BILL_NUM',TO_CHAR (P_BILL_NUM));
226 pay_in_utils.trace('P_EE_COMMENTS',TO_CHAR (P_EE_COMMENTS));
227 pay_in_utils.trace('P_ER_COMMENTS',TO_CHAR (P_ER_COMMENTS));
228 pay_in_utils.trace('P_LAST_UPDATED_DATE',TO_CHAR (P_LAST_UPDATED_DATE));
229 pay_in_utils.trace('P_ASSIGNMENT_ID',TO_CHAR (P_ASSIGNMENT_ID));
230 pay_in_utils.trace('P_EMPLOYEE_ID',TO_CHAR (P_EMPLOYEE_ID));
231 pay_in_utils.trace('P_ASSIGNMENT_EXTRA_INFO_ID ',TO_CHAR (P_ASSIGNMENT_EXTRA_INFO_ID));
232 pay_in_utils.trace('P_ENTRY_DATE ',TO_CHAR (P_ENTRY_DATE));
233
234
235 END IF;
236
237 l_business_group_id := pay_in_med_web_adi.get_bg_id();
238
239 IF (g_debug)
240 THEN
241 pay_in_utils.trace('l_business_group_id',TO_CHAR (l_business_group_id));
242 END IF;
243
244 BEGIN
245 SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),P_ENTRY_DATE);
249 END ;
250
251
252 IF ((p_element_entry_id IS NOT NULL))
253 THEN
254
255 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
256
257 pay_in_utils.trace('p_element_entry_id',p_element_entry_id);
258 pay_in_utils.trace('P_ENTRY_DATE',P_ENTRY_DATE);
259 OPEN c_get_ele_object_version(p_element_entry_id) ;
260 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
261 pay_in_utils.trace('l_object_version_no',l_object_version_no);
262 pay_in_utils.trace('l_effective_start_date',l_effective_start_date);
263 CLOSE c_get_ele_object_version ;
264
265 OPEN c_get_ele_type_id(p_element_entry_id);
266 FETCH c_get_ele_type_id INTO l_ltc_element_type_id ;
267 CLOSE c_get_ele_type_id ;
268
269 l_count := 1;
270 FOR c_rec IN c_input_rec(l_ltc_element_type_id,l_effective_start_date)
271 LOOP
272 l_input_values_rec(l_count).input_name := c_rec.name;
273 l_input_values_rec(l_count).input_value_id := c_rec.id;
274 l_input_values_rec(l_count).value := c_rec.value;
275
276 IF (g_debug)
277 THEN
278 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
279 END IF;
280
281 l_count := l_count + 1;
282 END LOOP;
283
284
285 OPEN c_get_screen_value(p_element_entry_id, l_input_values_rec(3).input_value_id);
286 FETCH c_get_screen_value INTO l_entry_information3 ;
287 CLOSE c_get_screen_value ;
288
289 IF (g_debug)
290 THEN
291 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
292 END IF;
293
294
295 OPEN c_get_screen_value(p_element_entry_id, l_input_values_rec(5).input_value_id);
296 FETCH c_get_screen_value INTO l_entry_information5 ;
297 CLOSE c_get_screen_value ;
298
299 IF (g_debug)
300 THEN
301 pay_in_utils.trace('l_entry_information5',TO_CHAR (l_entry_information5));
302 END IF;
303
304 OPEN c_get_prev_amts ;
305 FETCH c_get_prev_amts INTO l_prev_submitted, l_prev_exempted_amount;
306 CLOSE c_get_prev_amts ;
307
308 IF (g_debug)
309 THEN
310 pay_in_utils.trace('l_prev_submitted',TO_CHAR (l_prev_submitted));
311 pay_in_utils.trace('l_prev_exempted_amount',TO_CHAR (l_prev_exempted_amount));
312 END IF;
313
314
315 l_submitted := NVL(P_EXEMPTED,0) + NVL(l_entry_information3,0) - NVL(l_prev_exempted_amount,0) ;
316 l_submitted := GREATEST (l_submitted,0);
317 if (p_emp_element_entry_id = p_element_entry_id) THEN
318 l_exempted_amount := p_employer_Contribution;
319 l_carry_over_flag := p_emp_carry_over_flag;
320 ELSE
321 if(p_employer_Contribution <> 0) then
322 l_exempted_amount := NVL(p_employer_Contribution,0) - NVL(l_prev_exempted_amount,0) ;
323 else
324 l_exempted_amount := NVL(P_EXEMPTED,0) - NVL(l_prev_exempted_amount,0) ;
325 end if;
326 l_exempted_amount := GREATEST (l_exempted_amount,0);
327 l_carry_over_flag := p_emp_carry_over_flag;
328 END IF;
329 pay_element_entry_api.update_element_entry
330 (p_datetrack_update_mode => hr_api.g_correction
331 ,p_effective_date => l_effective_start_date
332 ,p_business_group_id => l_business_group_id
333 ,p_element_entry_id => p_element_entry_id
334 ,p_object_version_number => l_object_version_no
335 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
336 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
337 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
338 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
339 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
340 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
344 ,p_entry_value1 => l_input_values_rec(1).value
341 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
342 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
343 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
345 ,p_entry_value2 => l_input_values_rec(2).value
346 ,p_entry_value3 => l_submitted
347 ,p_entry_value4 => P_LTCBLOCK
348 ,p_entry_value5 => l_exempted_amount
349 ,p_entry_value6 => l_carry_over_flag
350 ,p_entry_value7 => l_input_values_rec(7).value
351 ,p_entry_value8 => l_input_values_rec(8).value
352 ,p_entry_value9 => l_input_values_rec(9).value
353 ,p_effective_start_date => l_start_date
354 ,p_effective_end_date => l_end_date
355 ,p_update_warning => l_warnings
356 );
357
358
359
360 l_object_version_no := NULL ;
361
362
363 ELSIF ((p_element_entry_id IS NULL) )
364 THEN
365
366
367 OPEN c_element_name(l_business_group_id);
368 FETCH c_element_name INTO l_ltc_element ;
369 CLOSE c_element_name ;
370
371 IF (g_debug)
372 THEN
373 pay_in_utils.trace('l_ltc_element ',TO_CHAR (l_ltc_element));
374 END IF;
375
376
377 OPEN csr_element_details(p_assignment_id, p_entry_date, l_ltc_element) ;
378 FETCH csr_element_details INTO l_ltc_element_type_id, l_ltc_element_link_id ;
379 CLOSE csr_element_details ;
380
381
382 IF (g_debug)
383 THEN
384 pay_in_utils.trace('l_ltc_element_type_id ',TO_CHAR (l_ltc_element_type_id));
385 pay_in_utils.trace('l_ltc_element_link_id ',TO_CHAR (l_ltc_element_link_id));
386 pay_in_utils.trace('p_entry_date ',TO_CHAR (p_entry_date));
387 END IF;
388
389 OPEN c_check_element_entry(l_ltc_element_type_id, p_entry_date);
390 FETCH c_check_element_entry INTO l_element_entry_id ;
391 pay_in_utils.trace('l_element_entry_id',l_element_entry_id);
392 CLOSE c_check_element_entry ;
393
394
395 OPEN c_ltc_carry_over(l_element_entry_id);
396 FETCH c_ltc_carry_over INTO l_carry_over;
397 pay_in_utils.trace('l_carry_over',l_carry_over);
398
399 CLOSE c_ltc_carry_over;
400
401 IF (l_element_entry_id IS NOT NULL AND l_carry_over = nvl(P_CARRY_OVER,'N'))THEN
402
403 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,40);
404 pay_in_utils.trace('l_element_entry_id',l_element_entry_id);
405 pay_in_utils.trace('P_ENTRY_DATE',P_ENTRY_DATE);
406 OPEN c_get_ele_object_version(l_element_entry_id) ;
407 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
408 pay_in_utils.trace('l_object_version_no',l_object_version_no);
409 pay_in_utils.trace('l_effective_start_date',l_effective_start_date);
410 CLOSE c_get_ele_object_version ;
411
412
413
414
415 l_count := 1;
416 FOR c_rec IN c_input_rec(l_ltc_element_type_id,l_effective_start_date)
417 LOOP
418 l_input_values_rec(l_count).input_name := c_rec.name;
419 l_input_values_rec(l_count).input_value_id := c_rec.id;
420 l_input_values_rec(l_count).value := c_rec.value;
421
422 IF (g_debug)
423 THEN
424 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
425 END IF;
426
427 l_count := l_count + 1;
428 END LOOP;
429
430 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(3).input_value_id);
431 FETCH c_get_screen_value INTO l_entry_information3 ;
432 CLOSE c_get_screen_value ;
433
434 IF (g_debug)
435 THEN
436 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
437 END IF;
438
439
440 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(5).input_value_id);
441 FETCH c_get_screen_value INTO l_entry_information5 ;
442 CLOSE c_get_screen_value ;
443
444 IF (g_debug)
445 THEN
446 pay_in_utils.trace('l_entry_information5',TO_CHAR (l_entry_information5));
447 END IF;
448
449
450
451 IF (g_debug)
452 THEN
453 pay_in_utils.trace('P_EXEMPTED',TO_CHAR (P_EXEMPTED));
454 END IF;
455
456 l_submitted := NVL(P_EXEMPTED,0) + NVL(l_entry_information3,0) ;
457 l_submitted := GREATEST (l_submitted,0);
458 if (p_emp_element_entry_id = l_element_entry_id) THEN
459 l_exempted_amount := p_employer_Contribution;
460 l_carry_over_flag := p_emp_carry_over_flag;
461 ELSE
462 if (p_employer_Contribution<>0) then
463 l_exempted_amount := NVL(p_employer_Contribution,0) ;
464 else
465 l_exempted_amount := NVL(P_EXEMPTED,0) ;
466 end if;
467 l_exempted_amount := GREATEST (l_exempted_amount,0);
468 l_carry_over_flag := p_emp_carry_over_flag;
469 END IF;
470
471 pay_element_entry_api.update_element_entry
472 (p_datetrack_update_mode => hr_api.g_correction
473 ,p_effective_date => l_effective_start_date
474 ,p_business_group_id => l_business_group_id
475 ,p_element_entry_id => l_element_entry_id
476 ,p_object_version_number => l_object_version_no
480 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
477 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
478 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
479 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
481 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
482 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
483 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
484 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
485 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
486 ,p_entry_value1 => l_input_values_rec(1).value
487 ,p_entry_value2 => l_input_values_rec(2).value
488 ,p_entry_value3 => l_submitted
489 ,p_entry_value4 => P_LTCBLOCK
490 ,p_entry_value5 => l_exempted_amount
491 ,p_entry_value6 => l_carry_over_flag
492 ,p_entry_value7 => l_input_values_rec(7).value
493 ,p_entry_value8 => l_input_values_rec(8).value
494 ,p_entry_value9 => l_input_values_rec(9).value
495 ,p_effective_start_date => l_start_date
496 ,p_effective_end_date => l_end_date
497 ,p_update_warning => l_warnings
498 );
499
500
501
502 l_object_version_no := NULL ;
503
504
505
506 ELSE
507
508
509
510 IF l_ltc_element_link_id IS NULL THEN
511
512 OPEN c_element(TO_NUMBER(l_ltc_element));
513 FETCH c_element INTO l_element;
514 CLOSE c_element;
515
516 hr_utility.set_message(800, 'PER_IN_MISSING_LINK');
517 hr_utility.set_message_token('ELEMENT_NAME', l_element);
518
519 hr_utility.raise_error;
520 END IF;
521
522 --Populate the input value id, name records
523
524 l_count := 1;
525 FOR c_rec IN c_input_rec(l_ltc_element_type_id,p_entry_date)
526 LOOP
527 l_ben_input_values_rec(l_count).input_name := c_rec.name;
528 l_ben_input_values_rec(l_count).input_value_id := c_rec.id;
529 l_ben_input_values_rec(l_count).value := c_rec.value;
530
531 IF (g_debug)
532 THEN
533 pay_in_utils.trace('Input Value Name:'||l_count,TO_CHAR (c_rec.name));
534 END IF;
535
536 l_count := l_count + 1;
537 END LOOP;
538
539 pay_in_utils.set_location(g_debug,'Creating Benefit Element Entries: '||l_procedure,50);
540
541
542
543 IF (P_EXEMPTED IS NOT NULL) THEN
544 l_exempted_amount := nvl(p_employer_contribution,P_EXEMPTED);
545 pay_element_entry_api.create_element_entry
546 (p_effective_date => p_entry_date
547 ,p_business_group_id => l_business_group_id
548 ,p_assignment_id => p_assignment_id
549 ,p_element_link_id => l_ltc_element_link_id
550 ,p_entry_type => 'E'
551 ,p_input_value_id1 => l_ben_input_values_rec(1).input_value_id
552 ,p_input_value_id2 => l_ben_input_values_rec(2).input_value_id
553 ,p_input_value_id3 => l_ben_input_values_rec(3).input_value_id
554 ,p_input_value_id4 => l_ben_input_values_rec(4).input_value_id
555 ,p_input_value_id5 => l_ben_input_values_rec(5).input_value_id
556 ,p_input_value_id6 => l_ben_input_values_rec(6).input_value_id
557 ,p_input_value_id7 => l_ben_input_values_rec(7).input_value_id
558 ,p_input_value_id8 => l_ben_input_values_rec(8).input_value_id
559 ,p_input_value_id9 => l_ben_input_values_rec(9).input_value_id
560 ,p_entry_value1 => l_ben_input_values_rec(1).value
561 ,p_entry_value2 => l_ben_input_values_rec(2).value
562 ,p_entry_value3 => P_EXEMPTED
563 ,p_entry_value4 => P_LTCBLOCK
564 ,p_entry_value5 => l_exempted_amount
565 ,p_entry_value6 => p_emp_carry_over_flag
566 ,p_entry_value7 => l_ben_input_values_rec(7).value
567 ,p_entry_value8 => l_ben_input_values_rec(8).value
568 ,p_entry_value9 => l_ben_input_values_rec(9).value
569 ,p_effective_start_date => l_start_date
570 ,p_effective_end_date => l_end_date
571 ,p_element_entry_id => l_element_entry_id
572 ,p_object_version_number => l_object_version_no
573 ,p_create_warning => l_warnings
574 );
575 l_create_flag :='Y';
576 END IF;
577 pay_in_utils.set_location(g_debug,'Benefit Element Creation Completed'||l_procedure,60);
578
579
580
581 END IF ;
582 END IF;
583
584
585 pay_in_utils.set_location(g_debug,'Updating Assignment_Extra_Info: '||l_procedure,70);
586
587 IF P_EMPLOYEE_ID <> 0 THEN /* To prevent this from Self Service page and execute only for web adi*/
588
589 UPDATE per_assignment_extra_info
590 SET AEI_INFORMATION10 = P_EXEMPTED
594 END IF;
591 ,AEI_INFORMATION6 = P_ER_COMMENTS
592 ,AEI_INFORMATION11 = nvl(l_element_entry_id,p_element_entry_id)
593 WHERE ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;
595
596 p_element_entry_id := nvl(l_element_entry_id,p_element_entry_id);
597
598
599
600 pay_in_utils.trace('**************************************************','********************');
601 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
602 p_warnings := 'FALSE';
603 EXCEPTION
604 WHEN OTHERS THEN
605 fnd_msg_pub.add_exc_msg
606 (p_pkg_name => g_package
607 ,p_procedure_name => 'create_ltc_element'
608 ,p_error_text => substr(sqlerrm, 1, 240)
609 );
610 END create_ltc_element;
611
612 --------------------------------------------------------------------------
613 -- --
614 -- Name : GET_MED_SUBMITTED --
615 -- Type : FUNCTION --
616 -- Access : Public --
617 -- Description : Function to get the total claim amount of approved --
618 -- or unapproved or all the medical bills for an --
619 -- assignment in a tax year. --
620 -- Used in 'Change Medical Payment' tabular summary --
621 -- --
622 ---------------------------------------------------------------------------
623
624
625 FUNCTION get_med_submitted(p_assignment_id NUMBER
626 ,p_tax_yr VARCHAR2
627 ,p_created_from DATE DEFAULT NULL
628 ,p_created_to DATE DEFAULT NULL
629 ,p_approval_status VARCHAR2 DEFAULT NULL)
630 RETURN NUMBER
631 IS
632
633 CURSOR csr_submitted_exempt IS
634 SELECT SUM(fnd_number.canonical_to_number(pae.aei_information6)) submitted
635 FROM per_assignment_extra_info pae
636 WHERE pae.assignment_id = p_assignment_id
637 AND pae.aei_information1 = p_tax_yr
638 AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
639 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
640 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
641 AND fnd_date.canonical_to_date(pae.aei_information3) >=
642 (select min(effective_start_date)
643 from per_assignments_f
644 where assignment_id = p_assignment_id)
645 AND pae.aei_information7 IS NOT NULL;
646
647 CURSOR csr_submitted_unexempt IS
648 SELECT SUM(fnd_number.canonical_to_number(pae.aei_information6)) submitted
649 FROM per_assignment_extra_info pae
650 WHERE pae.assignment_id = p_assignment_id
651 AND pae.aei_information1 = p_tax_yr
652 AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
653 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
654 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
655 AND fnd_date.canonical_to_date(pae.aei_information3) >=
656 (select min(effective_start_date)
657 from per_assignments_f
658 where assignment_id = p_assignment_id)
659 AND pae.aei_information7 IS NULL;
660
661 l_submitted NUMBER;
662 l_submitted_exempt NUMBER;
663 l_submitted_unexempt NUMBER;
664
665 BEGIN
666 l_submitted :=0;
667
668
669 OPEN csr_submitted_exempt;
670 FETCH csr_submitted_exempt INTO l_submitted_exempt;
671 CLOSE csr_submitted_exempt;
672
673 OPEN csr_submitted_unexempt;
674 FETCH csr_submitted_unexempt INTO l_submitted_unexempt;
675 CLOSE csr_submitted_unexempt;
676
677 IF p_approval_status = 'APPR' THEN
678 l_submitted := NVL(l_submitted_exempt,0);
679 ELSIF p_approval_status = 'UNAPPR' THEN
680 l_submitted := NVL(l_submitted_unexempt,0);
681 ELSE
682 l_submitted := NVL(l_submitted_exempt,0) + NVL(l_submitted_unexempt,0);
683 END IF;
684
685
686 RETURN l_submitted;
687
688 END get_med_submitted;
689
690 --------------------------------------------------------------------------
691 -- --
692 -- Name : GET_LTC_SUBMITTED --
693 -- Type : FUNCTION --
694 -- Access : Public --
695 -- Description : Function to get the total claim amount of approved --
696 -- or unapproved or all --
697 -- the LTC bills for an assignment in a LTC Block. --
698 -- Used in 'Change LTC Payment' tabular summary --
699 -- --
700 ---------------------------------------------------------------------------
701
702
703 FUNCTION get_ltc_submitted(p_assignment_id NUMBER
704 ,p_tax_yr VARCHAR2
705 ,p_created_from DATE DEFAULT NULL
706 ,p_created_to DATE DEFAULT NULL
707 ,p_approval_status VARCHAR2 DEFAULT NULL
708 ,p_carry_over VARCHAR2)
709 RETURN NUMBER
710 IS
711
712 CURSOR csr_submitted_exempt IS
713 SELECT SUM(fnd_number.canonical_to_number(pae.aei_information9)) submitted
714 FROM per_assignment_extra_info pae
715 WHERE pae.assignment_id = p_assignment_id
716 AND pae.aei_information1 = p_tax_yr
717 AND pae.aei_information_category ='PER_IN_LTC_BILLS'
718 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
719 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
720 AND pae.aei_information10 IS NOT NULL
721 AND fnd_date.canonical_to_date(pae.aei_information13) >=
722 (select min(effective_start_date)
723 from per_assignments_f
724 where assignment_id = p_assignment_id)
725 AND NVL(pae.aei_information18,'N') =nvl(p_carry_over,'N') ;
726
727 CURSOR csr_submitted_unexempt IS
728 SELECT SUM(fnd_number.canonical_to_number(pae.aei_information9)) submitted
729 FROM per_assignment_extra_info pae
730 WHERE pae.assignment_id = p_assignment_id
731 AND pae.aei_information1 = p_tax_yr
732 AND pae.aei_information_category ='PER_IN_LTC_BILLS'
733 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
734 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
735 AND pae.aei_information10 IS NULL
736 AND fnd_date.canonical_to_date(pae.aei_information13) >=
737 (select min(effective_start_date)
738 from per_assignments_f
739 where assignment_id = p_assignment_id)
740 AND NVL(pae.aei_information18,'N') = nvl(p_carry_over,'N');
741
742 l_submitted NUMBER;
743 l_submitted_exempt NUMBER;
744 l_submitted_unexempt NUMBER;
745
746 BEGIN
747 l_submitted :=0;
748
749
750 OPEN csr_submitted_exempt;
751 FETCH csr_submitted_exempt INTO l_submitted_exempt;
752 CLOSE csr_submitted_exempt;
753
754 OPEN csr_submitted_unexempt;
755 FETCH csr_submitted_unexempt INTO l_submitted_unexempt;
756 CLOSE csr_submitted_unexempt;
757
758 IF p_approval_status = 'APPR' THEN
759 l_submitted := NVL(l_submitted_exempt,0);
760 ELSIF p_approval_status = 'UNAPPR' THEN
761 l_submitted := NVL(l_submitted_unexempt,0);
762 ELSE
763 l_submitted := NVL(l_submitted_exempt,0) + NVL(l_submitted_unexempt,0);
764 END IF;
765
766
767 RETURN l_submitted;
768
769 END get_ltc_submitted;
770
771 --------------------------------------------------------------------------
772 -- --
773 -- Name : GET_MED_EXEMPTED --
774 -- Type : FUNCTION --
775 -- Access : Public --
776 -- Description : Function to get the total exempted amount of all the --
777 -- approved or unapproved or all --
778 -- the LTC bills for an assignment in a LTC Block. --
779 -- Used in 'Change LTC Payment' tabular summary --
780 -- --
781 ---------------------------------------------------------------------------
782
783 FUNCTION get_med_exempted(p_assignment_id NUMBER
784 ,p_tax_yr VARCHAR2
785 ,p_created_from DATE DEFAULT NULL
786 ,p_created_to DATE DEFAULT NULL
787 ,p_approval_status VARCHAR2 DEFAULT NULL)
788 RETURN NUMBER
789 IS
790
791 CURSOR csr_submitted_exempt IS
792 SELECT sum(nvl(fnd_number.canonical_to_number(nvl(aei_information7,0)),0)) approved
793 FROM per_assignment_extra_info pae
794 WHERE pae.assignment_id = p_assignment_id
795 AND pae.aei_information1 = p_tax_yr
796 AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
797 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
798 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
799 AND fnd_date.canonical_to_date(pae.aei_information3) >=
800 (select min(effective_start_date)
801 from per_assignments_f
802 where assignment_id = p_assignment_id)
803 AND pae.aei_information7 IS NOT NULL;
804
805 CURSOR csr_submitted_unexempt IS
806 SELECT sum(nvl(fnd_number.canonical_to_number(nvl(aei_information7,0)),0)) approved
807 FROM per_assignment_extra_info pae
808 WHERE pae.assignment_id = p_assignment_id
809 AND pae.aei_information1 = p_tax_yr
810 AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
811 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
815 from per_assignments_f
812 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
813 AND fnd_date.canonical_to_date(pae.aei_information3) >=
814 (select min(effective_start_date)
816 where assignment_id = p_assignment_id)
817 AND pae.aei_information7 IS NULL;
818
819 l_exempted NUMBER;
820
821 BEGIN
822
823 l_exempted :=0;
824
825
826 OPEN csr_submitted_exempt;
827 FETCH csr_submitted_exempt INTO l_exempted;
828 CLOSE csr_submitted_exempt;
829
830
831 IF p_approval_status = 'APPR' THEN
832 l_exempted := NVL(l_exempted,0);
833 ELSIF p_approval_status = 'UNAPPR' THEN
834 l_exempted :=0;
835 ELSE
836 l_exempted := NVL(l_exempted,0);
837 END IF;
838
839
840 RETURN l_exempted;
841
842 END get_med_exempted ;
843
844 ---------------------------------------------------------------------------
845 -- --
846 -- Name : GET_LTC_EXEMPTED --
847 -- Type : FUNCTION --
848 -- Access : Public --
849 -- Description : Function to get the total exempted amount of approved --
850 -- or unapproved or all --
851 -- the LTC bills for an assignment in a LTC Block. --
852 -- Used in 'Change LTC Payment' tabular summary --
853 -- --
854 ---------------------------------------------------------------------------
855
856
857 FUNCTION get_ltc_exempted(p_assignment_id NUMBER
858 ,p_tax_yr VARCHAR2
859 ,p_created_from DATE DEFAULT NULL
860 ,p_created_to DATE DEFAULT NULL
861 ,p_approval_status VARCHAR2 DEFAULT NULL
862 ,p_carry_over VARCHAR2)
863 RETURN NUMBER
864 IS
865
866 CURSOR csr_submitted_exempt IS
867 SELECT SUM(fnd_number.canonical_to_number(pae.aei_information10)) submitted
868 FROM per_assignment_extra_info pae
869 WHERE pae.assignment_id = p_assignment_id
870 AND pae.aei_information1 = p_tax_yr
871 AND pae.aei_information_category ='PER_IN_LTC_BILLS'
872 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
873 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
874 AND fnd_date.canonical_to_date(pae.aei_information13) >=
875 (select min(effective_start_date)
876 from per_assignments_f
877 where assignment_id = p_assignment_id)
878 AND pae.aei_information10 IS NOT NULL
879 and NVL(pae.aei_information18,'N') = nvl(p_carry_over,'N');
880
881
882 l_exempted NUMBER;
883
884 BEGIN
885 l_exempted :=0;
886
887
888 OPEN csr_submitted_exempt;
889 FETCH csr_submitted_exempt INTO l_exempted;
890 CLOSE csr_submitted_exempt;
891
892
893
894 IF p_approval_status = 'APPR' THEN
895 l_exempted := NVL(l_exempted,0);
896 ELSIF p_approval_status = 'UNAPPR' THEN
897 l_exempted := 0;
898 ELSE
899 l_exempted := NVL(l_exempted,0);
900 END IF;
901
902
903 RETURN l_exempted;
904
905
906 END get_ltc_exempted;
907
908
909 --------------------------------------------------------------------------
910 -- --
911 -- Name : GET_MED_BILL_DATE --
912 -- Type : FUNCTION --
913 -- Access : Public --
914 -- Description : The procedure is responsible for returning the --
915 -- freeze period details like start date, along with --
916 -- a flag to indicate if it is the freeze period. --
917 -- --
918 -- --
919 -- Change History : --
920 --------------------------------------------------------------------------
921 -- Rev# Date Userid Description --
922 --------------------------------------------------------------------------
923 -- 1.0 24-Sep-2004 PUCHIL Initial Version --
924 --------------------------------------------------------------------------
925
926
927
928
929 FUNCTION get_med_bill_date(p_assignment_id NUMBER
930 ,p_tax_yr VARCHAR2
931 ,p_created_from DATE DEFAULT NULL
932 ,p_created_to DATE DEFAULT NULL
933 ,p_approval_status VARCHAR2 DEFAULT NULL)
934 RETURN VARCHAR2
935 IS
936
937
938
939 CURSOR csr_submitted_exempt IS
940 SELECT 'Y'
941 FROM per_assignment_extra_info pae
942 WHERE pae.assignment_id = p_assignment_id
943 AND pae.aei_information1 = p_tax_yr
944 AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
945 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
946 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
947 AND fnd_date.canonical_to_date(pae.aei_information3) >=
948 (select min(effective_start_date)
949 from per_assignments_f
950 where assignment_id = p_assignment_id)
951 AND pae.aei_information7 IS NOT NULL;
952
953 CURSOR csr_submitted_unexempt IS
954 SELECT 'Y'
955 FROM per_assignment_extra_info pae
956 WHERE pae.assignment_id = p_assignment_id
957 AND pae.aei_information1 = p_tax_yr
958 AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
959 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
960 AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
961 AND fnd_date.canonical_to_date(pae.aei_information3) >=
962 (select min(effective_start_date)
963 from per_assignments_f
964 where assignment_id = p_assignment_id)
965 AND pae.aei_information7 IS NULL;
966
967 l_submitted VARCHAR2(10);
968 l_submitted_exempt VARCHAR2(10);
969 l_submitted_unexempt VARCHAR2(10);
970
971 BEGIN
972 l_submitted :='N';
973
974
975 OPEN csr_submitted_exempt;
976 FETCH csr_submitted_exempt INTO l_submitted_exempt;
977 CLOSE csr_submitted_exempt;
978
979 OPEN csr_submitted_unexempt;
980 FETCH csr_submitted_unexempt INTO l_submitted_unexempt;
981 CLOSE csr_submitted_unexempt;
982
983 IF p_approval_status = 'APPR' THEN
984 l_submitted := NVL(l_submitted_exempt,'N');
985 ELSIF p_approval_status = 'UNAPPR' THEN
986 l_submitted := NVL(l_submitted_unexempt,'N');
987 ELSE
988 IF l_submitted_exempt ='N' and l_submitted_unexempt ='N' THEN
989 l_submitted := 'N';
990 ELSE
991 l_submitted := 'Y';
992 END IF;
993 END IF;
994
995
996 RETURN l_submitted;
997
998 END get_med_bill_date ;
999
1000 FUNCTION get_last_updated_date
1001 (p_assignment_id IN NUMBER
1002 ,p_block IN VARCHAR2
1003 ,p_asg_info_type IN VARCHAR2
1004 ,p_created_from IN DATE DEFAULT NULL
1005 ,p_created_to IN DATE DEFAULT NULL
1006 ,p_approved IN VARCHAR2 DEFAULT NULL
1007 ,p_carry_over IN VARCHAR2 default null)
1008 RETURN DATE
1009 IS
1010 /* CHANGE THIS CURSOR TO INCLUDE APPROVAL STATUS LATER */
1011 CURSOR csr_get_med_date_appr
1012 IS
1013 SELECT MAX(extra.last_update_date)
1014 FROM per_assignment_extra_info extra
1015 WHERE extra.information_type = 'PER_IN_MEDICAL_BILLS'
1016 and extra.aei_information1 = p_block
1020 AND fnd_date.canonical_to_date(extra.aei_information3) >=
1017 AND extra.assignment_id = p_assignment_id
1018 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
1019 and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
1021 (select min(effective_start_date)
1022 from per_assignments_f
1023 where assignment_id = p_assignment_id)
1024 AND extra.aei_information7 IS not NULL;
1025
1026
1027 CURSOR csr_get_ltc_date_appr
1028 IS
1029 SELECT MAX(extra.last_update_date)
1030 FROM per_assignment_extra_info extra
1031 WHERE extra.information_type = 'PER_IN_LTC_BILLS'
1032 and extra.aei_information1 = p_block
1033 AND extra.assignment_id = p_assignment_id
1034 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
1035 and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
1036 and NVL(extra.aei_information18,'N') = nvl(p_carry_over,'N')
1037 AND fnd_date.canonical_to_date(extra.aei_information13) >=
1038 (select min(effective_start_date)
1039 from per_assignments_f
1040 where assignment_id = p_assignment_id)
1041 and extra.aei_information10 is not null;
1042
1043 CURSOR csr_get_med_date_unappr
1044 IS
1045 SELECT MAX(extra.last_update_date)
1046 FROM per_assignment_extra_info extra
1047 WHERE extra.information_type = 'PER_IN_MEDICAL_BILLS'
1048 and extra.aei_information1 = p_block
1049 AND extra.assignment_id = p_assignment_id
1050 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
1051 and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
1052 AND fnd_date.canonical_to_date(extra.aei_information3) >=
1053 (select min(effective_start_date)
1054 from per_assignments_f
1055 where assignment_id = p_assignment_id)
1056 AND extra.aei_information7 IS NULL;
1057
1058
1059
1060 CURSOR csr_get_ltc_date_unappr
1061 IS
1062 SELECT MAX(extra.last_update_date)
1063 FROM per_assignment_extra_info extra
1064 WHERE extra.information_type = 'PER_IN_LTC_BILLS'
1065 and extra.aei_information1 = p_block
1066 AND extra.assignment_id = p_assignment_id
1067 AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
1068 and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
1069 and NVL(extra.aei_information18,'N') = nvl(p_carry_over,'N')
1070 AND fnd_date.canonical_to_date(extra.aei_information13) >=
1071 (select min(effective_start_date)
1072 from per_assignments_f
1073 where assignment_id = p_assignment_id)
1074 and extra.aei_information10 is null;
1075
1076 --
1077 l_updated_date DATE;
1078 l_procedure VARCHAR(100);
1079 l_upd_date_unappr date;
1080 l_upd_date_appr date;
1081
1082
1083 --
1084 BEGIN
1085 --
1086 l_procedure := g_package || 'get_last_updated_date';
1087 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1088
1089 IF g_debug THEN
1090 pay_in_utils.trace('**************************************************','********************');
1091 pay_in_utils.trace('p_assignment_id ',p_assignment_id);
1092 pay_in_utils.trace('p_block ', p_block);
1093 pay_in_utils.trace('p_asg_info_type Date: ', p_asg_info_type);
1094 pay_in_utils.trace('**************************************************','********************');
1095 END IF;
1096
1097 --
1098
1099 --
1100
1101 IF p_asg_info_type ='PER_IN_MEDICAL_BILLS' THEN
1102 OPEN csr_get_med_date_appr;
1103 FETCH csr_get_med_date_appr INTO l_upd_date_appr;
1104 CLOSE csr_get_med_date_appr;
1105
1106 OPEN csr_get_med_date_unappr;
1107 FETCH csr_get_med_date_unappr INTO l_upd_date_unappr;
1108 CLOSE csr_get_med_date_unappr;
1109
1110 ELSE
1111 OPEN csr_get_ltc_date_appr;
1112 FETCH csr_get_ltc_date_appr INTO l_upd_date_appr;
1113 CLOSE csr_get_ltc_date_appr;
1114
1115 OPEN csr_get_med_date_unappr;
1116 FETCH csr_get_med_date_unappr INTO l_upd_date_unappr;
1117 CLOSE csr_get_med_date_unappr;
1118
1119 END IF;
1120
1121 IF p_approved = 'APPR' THEN
1122 l_updated_date := l_upd_date_appr;
1123 ELSIF p_approved = 'UNAPPR' THEN
1124 l_updated_date := l_upd_date_unappr;
1125 ELSE
1126 l_updated_date := GREATEST(l_upd_date_appr,l_upd_date_unappr);
1127 END IF;
1128 --
1129 IF g_debug THEN
1130 pay_in_utils.trace('**************************************************','********************');
1131 pay_in_utils.trace('l_updated_date',l_updated_date);
1132 pay_in_utils.trace('**************************************************','********************');
1133 END IF;
1134
1135 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,90);
1136 --
1137 RETURN l_updated_date;
1138
1139
1140 END get_last_updated_date;
1141
1142
1143 FUNCTION get_entry_value(p_assignment_id IN NUMBER
1144 ,p_entry_id IN NUMBER
1145 ,p_input_name IN VARCHAR2
1146 ,p_date IN DATE)
1147 RETURN VARCHAR2 IS
1148
1149 CURSOR c_get_value
1150 IS
1151 SELECT screen_entry_value
1152 FROM pay_element_entry_values_f peev,
1153 pay_element_entries_f pev,
1154 pay_element_types_f pet,
1155 pay_input_values_f piv
1159 AND peev.element_entry_id = p_entry_id
1156 WHERE pev.assignment_id = p_assignment_id
1157 AND pev.element_type_id = pet.element_type_id
1158 AND pev.element_entry_id = peev.element_entry_id
1160 AND piv.name = p_input_name
1161 AND piv.input_value_id = peev.input_value_id
1162 AND SYSDATE BETWEEN pet.effective_start_date AND pet.effective_end_date
1163 AND SYSDATE BETWEEN piv.effective_start_date AND piv.effective_end_date
1164 AND p_date BETWEEN pev.effective_start_date AND pev.effective_end_date
1165 AND p_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
1166
1167 l_value varchar2(300);
1168
1169 BEGIN
1170
1171 OPEN c_get_value;
1172 FETCH c_get_value INTO l_value;
1173 CLOSE c_get_value;
1174
1175 RETURN l_value;
1176 END get_entry_value;
1177
1178
1179
1180
1181 FUNCTION get_relationship(p_person_id IN NUMBER
1182 ,p_business_group_id IN NUMBER)
1183 RETURN VARCHAR2
1184 IS
1185
1186 CURSOR csr_relation
1187 IS
1188 SELECT hr_general.decode_lookup('CONTACT',relation.contact_type)
1189 FROM per_contact_relationships relation,
1190 per_all_people_f ppf
1191 WHERE ppf.person_id = relation.contact_person_id
1192 AND relation.contact_person_id = p_person_id
1193 AND ppf.business_group_id = p_business_group_id;
1194
1195 l_relationship VARCHAR2(200);
1196
1197 BEGIN
1198
1199 OPEN csr_relation;
1200 FETCH csr_relation INTO l_relationship;
1201 CLOSE csr_relation;
1202
1203 RETURN l_relationship;
1204
1205 END get_relationship;
1206
1207 PROCEDURE get_element_type_id(p_element_flag IN VARCHAR2
1208 ,p_business_group_id in number
1209 ,p_element_type_id OUT NOCOPY NUMBER)
1210 is
1211 CURSOR csr_element_ids IS
1212 SELECT DECODE(p_element_flag,'MEDBILL',org.org_information1,'MEDPAY',org.org_information2,'LTC_ELE',org.org_information3)
1213 FROM hr_organization_information org,
1214 hr_organization_units unit
1215 WHERE org.org_information_context = 'PER_IN_REIMBURSE_ELEMENTS'
1216 AND org.organization_id =unit.organization_id
1217 AND unit.business_group_id = p_business_group_id;
1218
1219
1220 BEGIN
1221 OPEN csr_element_ids;
1222 FETCH csr_element_ids INTO p_element_type_id;
1223 CLOSE csr_element_ids;
1224
1225
1226 END get_element_type_id;
1227
1228 PROCEDURE set_profile(p_person_id IN NUMBER)
1229 IS
1230 BEGIN
1231
1232 fnd_profile.PUT('PER_PERSON_ID',p_person_id);
1233 END set_profile;
1234
1235
1236
1237 PROCEDURE delete_medical_bill_entry(p_asg_extra_info_id IN NUMBER)
1238 IS
1239 CURSOR csr_exists
1240 IS
1241 SELECT '1'
1242 FROM per_assignment_extra_info
1243 WHERE assignment_extra_info_id = p_asg_extra_info_id;
1244
1245 l_ovn NUMBER;
1246 l_exists VARCHAR2(1);
1247 BEGIN
1248 OPEN csr_exists;
1249 FETCH csr_exists INTO l_exists;
1250 IF csr_exists%FOUND THEN
1251
1252 DELETE FROM per_assignment_extra_info
1253 WHERE assignment_extra_info_id = p_asg_extra_info_id;
1254 END IF;
1255
1256 CLOSE csr_exists ;
1257
1258
1259 END delete_medical_bill_entry;
1260
1261 PROCEDURE medical_bill_entry(p_asg_id IN NUMBER
1262 ,p_financial_yr IN VARCHAR2 DEFAULT NULL /* needed mainly for PU*/
1263 ,p_bill_date IN DATE DEFAULT NULL
1264 ,p_person_id IN NUMBER
1265 ,p_con_person_id IN NUMBER DEFAULT NULL
1266 ,p_old_bill_amt IN NUMBER DEFAULT NULL
1267 ,p_new_bill_amt IN NUMBER DEFAULT NULL
1268 ,p_old_exempt_amt IN NUMBER DEFAULT NULL
1269 ,p_new_exempt_amt IN NUMBER DEFAULT NULL
1270 ,p_element_entry_id IN NUMBER DEFAULT NULL
1271 ,p_bill_number IN VARCHAR2 DEFAULT NULL
1272 ,p_asg_extra_info_id IN NUMBER DEFAULT NULL
1273 ,p_ovn IN NUMBER DEFAULT NULL
1274 ,p_business_group_id IN NUMBER
1275 ,p_element_entry_date IN DATE
1276 ,p_super_user IN VARCHAR2
1277 ,p_ee_comments IN VARCHAR2
1278 ,p_er_comments IN VARCHAR2
1279 )
1280 IS
1281
1282
1283
1284 CURSOR csr_get_ovn(l_asg_extra_info_d NUMBER)
1285 IS
1286 SELECT object_version_number
1287 FROM per_assignment_extra_info
1288 WHERE assignment_extra_info_id = l_asg_extra_info_d;
1289
1290
1291 l_bill_amt NUMBER ;
1292 l_ovn NUMBER;
1293 l_extra_id_out NUMBER;
1294 l_ovn_out NUMBER;
1295 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1296
1297 l_person_profile_id NUMBER;
1298 l_bg_profile_id NUMBER;
1299
1300
1301
1302 BEGIN
1303
1304 g_debug := hr_utility.debug_enabled;
1305
1306 OPEN csr_get_ovn(p_asg_extra_info_id);
1307 FETCH csr_get_ovn into l_ovn;
1308 CLOSE csr_get_ovn;
1309
1310 IF (g_debug)
1311 THEN
1312 pay_in_utils.trace('**************************************************','********************');
1313 pay_in_utils.set_location(g_debug,'Input Paramters values are',20);
1314 pay_in_utils.trace('p_asg_id',TO_CHAR (p_asg_id));
1315 pay_in_utils.trace('p_financial_yr',TO_CHAR (p_financial_yr));
1316 pay_in_utils.trace('p_bill_date',TO_CHAR (p_bill_date));
1317 pay_in_utils.trace('p_person_id',TO_CHAR (p_person_id));
1321 pay_in_utils.trace('p_new_exempt_amt',TO_CHAR (p_new_exempt_amt));
1318 pay_in_utils.trace('p_con_person_id',TO_CHAR (p_con_person_id));
1319 pay_in_utils.trace('p_old_bill_amt',TO_CHAR (p_old_bill_amt));
1320 pay_in_utils.trace('p_old_exempt_amt',TO_CHAR (p_old_exempt_amt));
1322 pay_in_utils.trace('p_element_entry_id',TO_CHAR (p_element_entry_id));
1323 pay_in_utils.trace('p_bill_number',TO_CHAR (p_bill_number));
1324 pay_in_utils.trace('p_asg_extra_info_id',TO_CHAR (p_asg_extra_info_id));
1325 pay_in_utils.trace('p_ovn',TO_CHAR (p_ovn));
1326 pay_in_utils.trace('p_business_group_id',TO_CHAR (p_business_group_id));
1327 pay_in_utils.trace('p_element_entry_date',TO_CHAR (p_element_entry_date));
1328 pay_in_utils.trace('p_super_user',TO_CHAR (p_super_user));
1329 pay_in_utils.trace('p_ee_comments',TO_CHAR(p_ee_comments));
1330 pay_in_utils.trace('p_er_comments',TO_CHAR(p_er_comments));
1331
1332 END IF;
1333
1334 IF p_super_user ='Y'
1335 THEN
1336 pay_in_med_web_adi.create_medical
1337 (p_tax_year => p_financial_yr
1338 ,p_bill_date => p_bill_date
1339 ,p_name => p_con_person_id
1340 ,p_bill_number => p_bill_number
1341 ,p_bill_amount => p_new_bill_amt
1342 ,p_approved_bill_amount => p_new_exempt_amt
1343 ,p_employee_remarks => p_ee_comments
1344 ,p_employer_remarks => p_er_comments
1345 ,p_element_entry_id => p_element_entry_id
1346 ,p_assignment_id => p_asg_id
1347 ,p_employee_id =>''
1348 ,p_employee_name =>''
1349 ,p_assignment_extra_info_id => p_asg_extra_info_id
1350 ,p_entry_date => p_element_entry_date
1351 );
1352 ELSE
1353 fnd_profile.PUT('PER_PERSON_ID',p_con_person_id);
1354
1355 select fnd_profile.value('PER_PERSON_ID') into l_person_profile_id from dual;
1356 select fnd_profile.value('PER_BUSINESS_GROUP_ID') into l_bg_profile_id from dual;
1357
1358 IF (g_debug)
1359 THEN
1360 pay_in_utils.set_location(g_debug,'PER_PERSON_ID'||TO_CHAR(l_person_profile_id),20);
1361 pay_in_utils.set_location(g_debug,'PER_BUSINESS_GROUP_ID'||TO_CHAR(l_bg_profile_id),20);
1362 END IF;
1363
1364
1365
1366
1367
1368
1369 IF p_asg_extra_info_id is null THEN
1370 IF (g_debug)
1371 THEN
1372 pay_in_utils.set_location(g_debug,'Creating new assignment extra information',20);
1373 END IF;
1374
1375 hr_assignment_extra_info_api.create_assignment_extra_info(
1376 p_assignment_id => p_asg_id,
1377 p_information_type => 'PER_IN_MEDICAL_BILLS',
1378 p_aei_information_category => 'PER_IN_MEDICAL_BILLS',
1379 p_aei_information1 => p_financial_yr,
1380 p_aei_information2 => '',
1381 p_aei_information3 => fnd_date.date_to_canonical(p_bill_date),
1382 p_aei_information4 => p_person_id,
1383 p_aei_information5 => p_bill_number,
1384 p_aei_information6 => fnd_number.number_to_canonical(p_new_bill_amt) ,
1385 p_aei_information8 => p_ee_comments,
1386 p_aei_information9 => p_er_comments,
1387 p_aei_information10 => l_element_entry_id,
1388 p_assignment_extra_info_id => l_extra_id_out,
1389 p_object_version_number => l_ovn_out
1390 );
1391
1392
1393 ELSE
1394
1395 IF (g_debug)
1396 THEN
1397 pay_in_utils.set_location(g_debug,'Updating AEI'||TO_CHAR(p_asg_extra_info_id),20);
1398 END IF;
1399
1400 hr_assignment_extra_info_api.update_assignment_extra_info(
1401 p_aei_information1 => p_financial_yr,
1402 p_aei_information2 => '',
1403 p_aei_information3 => fnd_date.date_to_canonical(p_bill_date),
1404 p_aei_information4 => p_con_person_id,
1405 p_aei_information5 => p_bill_number,
1406 p_aei_information6 => fnd_number.number_to_canonical(p_new_bill_amt) ,
1407 p_aei_information8 => p_ee_comments,
1408 p_aei_information9 => p_er_comments,
1409 p_aei_information10 => l_element_entry_id,
1410 p_assignment_extra_info_id => p_asg_extra_info_id,
1411 p_object_version_number => l_ovn);
1412 END IF;
1413
1414 END IF;
1415
1416 END medical_bill_entry;
1417
1418
1419
1420 PROCEDURE ltc_bill_entry( p_asg_id IN NUMBER
1421 ,p_ltc_block IN VARCHAR2 DEFAULT NULL /* needed mainly for PU*/
1422 ,p_ben_name IN VARCHAR2 DEFAULT NULL
1423 ,p_place_from IN VARCHAR2 DEFAULT NULL
1424 ,p_bill_number IN VARCHAR2 DEFAULT NULL
1425 ,p_ee_comments IN VARCHAR2
1426 ,p_er_comments IN VARCHAR2
1427 ,p_place_to IN VARCHAR2 DEFAULT NULL
1428 ,p_travel_mode IN VARCHAR2 DEFAULT NULL
1429 ,p_bill_amt IN NUMBER DEFAULT NULL
1430 ,p_exempt_amt IN NUMBER DEFAULT NULL
1431 ,p_element_entry_id IN OUT NOCOPY NUMBER
1432 ,p_start_date IN DATE
1433 ,p_end_date IN DATE
1434 ,p_carry_over_flag IN VARCHAR2 DEFAULT NULL
1435 ,p_asg_extra_info_id IN NUMBER DEFAULT NULL
1436 ,p_element_entry_date IN DATE
1440 ,p_emp_element_entry_id IN NUMBER
1437 ,p_super_user IN VARCHAR2
1438 ,p_person_id IN NUMBER
1439 ,p_employer_Contribution IN NUMBER
1441 ,p_emp_carry_over_flag IN VARCHAR2 DEFAULT NULL
1442 ,p_create_flag OUT NOCOPY VARCHAR2
1443 , p_warnings OUT NOCOPY VARCHAR2
1444 )
1445 IS
1446
1447 l_procedure VARCHAR2(250);
1448 l_create_flag VARCHAR2(10);
1449 l_warnings VARCHAR2(250);
1450 BEGIN
1451
1452 g_debug := hr_utility.debug_enabled;
1453 l_procedure := g_package ||'ltc_bill_entry';
1454 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1455
1456 p_warnings := 'TRUE';
1457
1458 IF (p_element_entry_id =0) THEN
1459 p_element_entry_id :='';
1460 END IF;
1461
1462 IF (g_debug)
1463 THEN
1464 pay_in_utils.trace('**************************************************','********************');
1465 pay_in_utils.set_location(g_debug,'Input Paramters values are',20);
1466 pay_in_utils.trace('p_asg_id',TO_CHAR (p_asg_id));
1467 pay_in_utils.trace('p_ltc_block',TO_CHAR (p_ltc_block));
1468 pay_in_utils.trace('p_ben_name',TO_CHAR (p_ben_name));
1469 pay_in_utils.trace('p_place_from',TO_CHAR (p_place_from));
1470 pay_in_utils.trace('p_bill_number',TO_CHAR (p_bill_number));
1471 pay_in_utils.trace('p_ee_comments',TO_CHAR (p_ee_comments));
1472 pay_in_utils.trace('p_er_comments',TO_CHAR (p_er_comments));
1473 pay_in_utils.trace('p_place_to',TO_CHAR (p_place_to));
1474 pay_in_utils.trace('p_travel_mode',TO_CHAR (p_travel_mode));
1475 pay_in_utils.trace('p_bill_amt',TO_CHAR (p_bill_amt));
1476 pay_in_utils.trace('p_exempt_amt',TO_CHAR (p_exempt_amt));
1477 pay_in_utils.trace('p_element_entry_id',TO_CHAR (p_element_entry_id));
1478 pay_in_utils.trace('p_start_date',TO_CHAR (p_start_date));
1479 pay_in_utils.trace('p_end_date',TO_CHAR (p_end_date));
1480 pay_in_utils.trace('p_carry_over_flag',TO_CHAR (p_carry_over_flag));
1481 pay_in_utils.trace('p_asg_extra_info_id',TO_CHAR(p_asg_extra_info_id));
1482 pay_in_utils.trace('p_element_entry_date',TO_CHAR(p_element_entry_date));
1483 pay_in_utils.trace('p_super_user',TO_CHAR(p_super_user));
1484 pay_in_utils.trace('p_person_id',TO_CHAR(p_person_id));
1485
1486 END IF;
1487
1488
1489
1490
1491 IF p_super_user ='Y'
1492 THEN
1493 create_ltc_element
1494 (p_ltcblock => p_ltc_block
1495 ,p_place_from => p_place_from
1496 ,p_place_to => p_place_to
1497 ,p_mode_class => p_travel_mode
1498 ,p_carry_over => p_carry_over_flag
1499 ,p_submitted => p_bill_amt
1500 ,p_exempted => p_exempt_amt
1501 ,p_element_entry_id => p_element_entry_id
1502 ,p_start_date => p_start_date
1503 ,p_end_date => p_end_date
1504 ,p_bill_num => p_bill_number
1505 ,p_ee_comments => p_ee_comments
1506 ,p_er_comments => p_er_comments
1507 ,p_last_updated_date => ''
1508 ,p_assignment_id => p_asg_id
1509 ,p_employee_id => 0
1510 ,p_assignment_extra_info_id => p_asg_extra_info_id
1511 ,p_entry_date => p_element_entry_date
1512 ,p_employer_Contribution => p_employer_Contribution
1513 ,p_emp_element_entry_id => p_emp_element_entry_id
1514 ,p_emp_carry_over_flag => p_emp_carry_over_flag
1515 ,p_create_flag => l_create_flag
1516 ,p_warnings => l_warnings
1517 );
1518 ELSE
1519 NULL;
1520 END IF;
1521
1522
1523
1524 pay_in_utils.trace('l_warnings',l_warnings);
1525
1526 pay_in_utils.trace('**************************************************','********************');
1527 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
1528 p_warnings := l_warnings;
1529 pay_in_utils.trace('p_warnings',p_warnings);
1530
1531 END ltc_bill_entry;
1532
1533
1534
1535
1536
1537 FUNCTION get_ltc_balance (p_asg_id IN NUMBER,
1538 p_ltc_block IN VARCHAR2,
1539 p_balance_name IN VARCHAR2)
1540 RETURN NUMBER
1541 IS
1542 CURSOR csr_ltc_run_result( p_asg_action_id IN NUMBER,
1543 p_balance_type_id IN VARCHAR2)
1544 IS
1545 SELECT sum(nvl(target.result_value,0) )
1546 FROM pay_run_result_values TARGET
1547 ,pay_balance_feeds_f FEED
1548 ,pay_run_results RR
1549 ,pay_assignment_actions ASSACT
1550 ,pay_assignment_actions BAL_ASSACT
1551 ,pay_payroll_actions PACT
1552 ,pay_payroll_actions BACT
1553 ,pay_input_values_f piv
1554 ,pay_run_result_values srcVal
1555 ,pay_input_values_f srcInp
1556 where BAL_ASSACT.assignment_action_id = p_asg_action_id
1557 AND BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
1558 AND FEED.input_value_id = TARGET.input_value_id
1559 AND nvl(TARGET.result_value, '0') <> '0'
1560 AND TARGET.run_result_id = RR.run_result_id
1561 AND RR.assignment_action_id = ASSACT.assignment_action_id
1562 AND ASSACT.payroll_action_id = PACT.payroll_action_id
1563 AND PACT.effective_date between
1564 FEED.effective_start_date AND FEED.effective_end_date
1568 AND feed.input_value_id = piv.input_value_id
1565 AND RR.status in ('P','PA')
1566 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
1567 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
1569 AND feed.balance_type_id = p_balance_type_id
1570 AND srcVal.run_result_id = RR.run_result_id
1571 AND srcVal.result_value = p_ltc_block
1572 AND srcVal.input_value_id = srcInp.input_value_id
1573 AND srcInp.name = 'LTC Journey Block';
1574
1575
1576
1577
1578 CURSOR csr_max_assact_id(p_asg_id IN NUMBER)
1579 IS
1580 SELECT assignment_action_id
1581 FROM pay_assignment_actions paa,
1582 pay_payroll_actions ppa
1583 WHERE paa.assignment_id = p_asg_id
1584 AND paa.payroll_action_id = PPA.PAYROLL_ACTION_ID
1585 AND ppa.action_type in('R','Q','B','I')
1586 AND paa.source_action_id IS NOT NULL
1587 ORDER BY paa.action_sequence DESC;
1588
1589
1590 CURSOR csr_balance_type_id(p_balance_name IN VARCHAR2) IS
1591 SELECT balance_type_id
1592 FROM pay_balance_types pbt
1593 WHERE pbt.balance_name = p_balance_name
1594 AND legislation_code ='IN';
1595
1596
1597 l_balance_type_id NUMBER;
1598 l_max_assact_id NUMBER;
1599 l_pay_value NUMBER;
1600 l_taxable_adjust NUMBER;
1601 l_emplr_contr NUMBER;
1602 p_value NUMBER;
1603
1604 BEGIN
1605
1606
1607 OPEN csr_balance_type_id('Earnings');
1608 FETCH csr_balance_type_id INTO l_balance_type_id ;
1609 CLOSE csr_balance_type_id;
1610
1611 OPEN csr_max_assact_id(p_asg_id);
1612 FETCH csr_max_assact_id INTO l_max_assact_id;
1613 CLOSE csr_max_assact_id;
1614
1615
1616
1617 IF l_max_assact_id IS NOT NULL THEN
1618 OPEN csr_ltc_run_result(l_max_assact_id,l_balance_type_id);
1619 FETCH csr_ltc_run_result INTO l_pay_value;
1620 CLOSE csr_ltc_run_result;
1621 END IF;
1622
1623
1624 OPEN csr_balance_type_id('Salary under Section 17');
1625 FETCH csr_balance_type_id INTO l_balance_type_id ;
1626 CLOSE csr_balance_type_id;
1627
1628 IF l_max_assact_id IS NOT NULL THEN
1629 OPEN csr_ltc_run_result(l_max_assact_id,l_balance_type_id);
1630 FETCH csr_ltc_run_result INTO l_taxable_adjust;
1631 CLOSE csr_ltc_run_result;
1632 END IF;
1633
1634
1635 OPEN csr_balance_type_id('Employer Contribution for LTC');
1636 FETCH csr_balance_type_id INTO l_balance_type_id ;
1637 CLOSE csr_balance_type_id;
1638
1639 IF l_max_assact_id IS NOT NULL THEN
1640 OPEN csr_ltc_run_result(l_max_assact_id,l_balance_type_id);
1641 FETCH csr_ltc_run_result INTO l_emplr_contr;
1642 CLOSE csr_ltc_run_result;
1643 END IF;
1644
1645 IF (p_balance_name = 'Salary under Section 17') THEN
1646 p_value := nvl(l_emplr_contr,0) - (nvl(l_pay_value,0) - nvl(l_taxable_adjust,0));
1647 ELSE
1648 p_value := nvl(l_emplr_contr,0);
1649 END IF;
1650 if(p_value = 0) then
1651 return 0;
1652 end if;
1653 RETURN p_value;
1654
1655 END get_ltc_balance;
1656
1657
1658 FUNCTION get_medical_balance( p_asg_id IN NUMBER,
1659 p_tax_year IN VARCHAR2,
1660 p_balance_name IN VARCHAR2)
1661 RETURN NUMBER IS
1662 CURSOR csr_get_max_assact(p_year_start DATE
1663 ,p_year_end DATE) IS
1664 SELECT assignment_action_id
1665 FROM pay_assignment_actions paa,
1666 pay_payroll_actions ppa
1667 WHERE paa.assignment_id = p_asg_id
1668 AND paa.payroll_action_id = PPA.PAYROLL_ACTION_ID
1669 AND ppa.action_type in('R','Q','B','I')
1670 AND ppa.effective_date BETWEEN p_year_start AND p_year_end
1671 AND paa.source_action_id IS NOT NULL
1672 ORDER BY paa.action_sequence DESC;
1673
1674 CURSOR csr_exists(p_year_start DATE
1675 ,p_year_end DATE) IS
1676 SELECT assignment_action_id
1677 FROM pay_assignment_actions paa,
1678 pay_payroll_actions ppa
1679 WHERE paa.assignment_id = p_asg_id
1680 AND paa.payroll_action_id = PPA.PAYROLL_ACTION_ID
1681 AND ppa.action_type in('R','Q','B','I')
1682 AND ppa.effective_date BETWEEN p_year_start AND p_year_end
1683 AND paa.source_action_id IS NOT NULL
1684 and exists(select '1' from pay_run_results prr
1685 where source_id in (select distinct(aei_information11)
1686 from per_assignment_extra_info
1687 where information_type ='PER_IN_MEDICAL_BILLS')
1688 and prr.assignment_action_id = paa.assignment_action_id) ;
1689
1690 CURSOR csr_defined_balance_id(p_balance_name VARCHAR2,
1691 p_dimension_name VARCHAR2)
1692 IS
1693 SELECT pdb.defined_balance_id
1694 FROM pay_defined_balances pdb
1695 ,pay_balance_types pbt
1696 ,pay_balance_dimensions pbd
1697 WHERE pbt.balance_name = p_balance_name
1698 AND pbd.dimension_name = p_dimension_name
1699 AND pdb.balance_type_id = pbt.balance_type_id
1700 AND pbt.legislation_code = 'IN'
1701 AND pbd.legislation_code = 'IN'
1702 AND pdb.legislation_code = 'IN'
1703 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
1704
1705
1706 l_max_assact_id NUMBER;
1707 l_defined_balance_id NUMBER;
1708 p_year_start DATE;
1709 p_year_end DATE;
1710 l_value NUMBER;
1711
1715 p_year_end := to_date('31-03-'||substr(p_tax_year,6,4),'DD-MM-YYYY');
1712
1713 BEGIN
1714 p_year_start := to_date('01-04-'||substr(p_tax_year,1,4),'DD-MM-YYYY');
1716
1717 OPEN csr_exists(p_year_start,p_year_end);
1718 FETCH csr_exists INTO l_max_assact_id;
1719 IF csr_exists%NOTFOUND THEN
1720 l_value := 0;
1721 RETURN l_value;
1722 END IF;
1723 CLOSE csr_exists;
1724
1725 OPEN csr_get_max_assact(p_year_start,p_year_end);
1726 FETCH csr_get_max_assact INTO l_max_assact_id;
1727 CLOSE csr_get_max_assact;
1728
1729 l_value := 0;
1730
1731
1732
1733 OPEN csr_defined_balance_id('Medical Reimbursement Amount','_ASG_YTD');
1734 FETCH csr_defined_balance_id INTO l_defined_balance_id;
1735 CLOSE csr_defined_balance_id;
1736
1737 l_value := pay_balance_pkg.get_value(l_defined_balance_id,l_max_assact_id);
1738
1739 IF (p_balance_name = 'Medical Reimbursement Amount') THEN
1740 if (l_value = 0) then
1741 return 0;
1742 else
1743 RETURN l_value;
1744 end if;
1745 ELSE
1746 OPEN csr_defined_balance_id('Salary under Section 17','_ASG_COMP_PTD');
1747 FETCH csr_defined_balance_id INTO l_defined_balance_id;
1748 CLOSE csr_defined_balance_id;
1749
1750
1751 l_value := l_value + pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id,
1752 p_assignment_Action_id => l_max_assact_id,
1753 p_tax_unit_id => null,
1754 p_jurisdiction_code => null,
1755 p_source_id => null,
1756 p_source_text => null,
1757 p_tax_group => null,
1758 p_date_earned => null,
1759 p_get_rr_route => null,
1760 p_get_rb_route => null,
1761 p_source_text2 =>'Employees Welfare Expense');
1762 RETURN l_value;
1763 END IF;
1764
1765 -- l_value := pay_balance_pkg.get_value(l_defined_balance_id,l_max_assact_id)
1766
1767 RETURN l_value;
1768 END get_medical_balance;
1769
1770
1771 PROCEDURE is_locked( p_person_id IN NUMBER
1772 ,p_ltc_or_med IN VARCHAR2
1773 ,p_locked OUT NOCOPY VARCHAR2 )
1774 IS
1775
1776 CURSOR lock_dtls
1777 IS
1778 SELECT NVL(DECODE(p_ltc_or_med,'LTC',org_information1,'MED',org_information2),'N') lock_flag
1779 FROM
1780 hr_organization_information org
1781 ,per_people_f person
1782 WHERE
1783 org.org_information_context = 'PER_IN_BENEFITS_DECL_INFO'
1784 AND org.organization_id = person.business_group_id
1785 AND person.person_id = p_person_id
1786 AND SYSDATE BETWEEN person.effective_start_date
1787 AND person.effective_end_date ;
1788
1789 l_proc VARCHAR2(120);
1790 l_procedure VARCHAR(100);
1791 l_message VARCHAR2(250);
1792 --
1793 BEGIN
1794 --
1795 l_procedure := g_package || 'is_locked';
1796 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1797
1798 IF g_debug THEN
1799 pay_in_utils.trace('**************************************************','********************');
1800 pay_in_utils.trace('p_person_id',p_person_id);
1801 pay_in_utils.trace('**************************************************','********************');
1802 END IF;
1803 p_locked := 'N';
1804 --
1805
1806 --
1807 OPEN lock_dtls;
1808 FETCH lock_dtls INTO p_locked;
1809 CLOSE lock_dtls;
1810 --
1811 pay_in_utils.set_location(g_debug, l_proc, 20);
1812 --
1813
1814
1815 IF g_debug THEN
1816 pay_in_utils.trace('**************************************************','********************');
1817 pay_in_utils.trace('p_locked ',p_locked);
1818 pay_in_utils.trace('**************************************************','********************');
1819 END IF;
1820
1821 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1822
1823 --
1824
1825 END is_locked;
1826
1827 PROCEDURE update_ltc_empCont(
1828 p_benefit IN NUMBER
1829 ,p_carry IN VARCHAR2
1830 ,p_assignment_id IN NUMBER
1831 ,p_element_entry_id IN NUMBER DEFAULT NULL
1832 )
1833 IS
1834 BEGIN
1835 UPDATE per_assignment_extra_info
1836 SET
1837 AEI_INFORMATION21 = p_benefit,
1838 AEI_INFORMATION18 = p_carry
1839 where AEI_INFORMATION11 = p_element_entry_id
1840 --and ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id
1841 and assignment_id = p_assignment_id;
1842 END;
1843
1844 PROCEDURE update_ltc_element
1845 (
1846 p_employee_number IN VARCHAR2
1847 ,p_full_name IN VARCHAR2
1848 ,p_start_date IN DATE
1849 ,p_effective_end_date IN DATE DEFAULT NULL
1850 ,p_fare IN NUMBER
1851 ,p_blockYr IN VARCHAR2
1852 ,p_carry IN VARCHAR2
1853 ,p_benefit IN NUMBER
1854 ,p_assignment_id IN NUMBER
1855 ,p_element_entry_id IN NUMBER DEFAULT NULL
1856 ,p_warnings OUT NOCOPY VARCHAR2
1857 )
1858 IS
1859
1860
1861
1862
1866 IS
1863 CURSOR c_input_rec(p_element_type_id NUMBER
1864 ,p_effective_date DATE
1865 )
1867 SELECT inputs.name name
1868 , inputs.input_value_id id
1869 , inputs.default_value value
1870 FROM pay_element_types_f types
1871 , pay_input_values_f inputs
1872 WHERE types.element_type_id = p_element_type_id
1873 AND inputs.element_type_id = types.element_type_id
1874 --AND inputs.legislation_code = 'IN'
1875 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
1876 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
1877 ORDER BY inputs.display_sequence;
1878
1879
1880
1881
1882 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
1883 IS
1884 SELECT object_version_number
1885 FROM pay_element_entries_f
1886 WHERE element_entry_id = p_element_entryid;
1887
1888
1889
1890 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
1891 IS
1892 SELECT element_type_id
1893 FROM pay_element_entries_f
1894 WHERE element_entry_id = p_element_entryid;
1895
1896
1897 --Variables Initialization
1898 TYPE t_input_values_rec IS RECORD
1899 (input_name pay_input_values_f.name%TYPE
1900 ,input_value_id pay_input_values_f.input_value_id%TYPE
1901 ,value pay_input_values_f.default_value%TYPE
1902 );
1903
1904 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
1905
1906 l_procedure VARCHAR2(250);
1907 l_warnings BOOLEAN;
1908 l_input_values_rec t_input_values_tab;
1909 l_business_group_id NUMBER;
1910 l_count NUMBER;
1911 l_element_type_id NUMBER;
1912 l_start_date DATE ;
1913 l_end_date DATE ;
1914 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
1915 l_session NUMBER ;
1916
1917
1918
1919 BEGIN
1920
1921 fnd_msg_pub.initialize; --13767307
1922 p_warnings := 'TRUE';
1923 g_debug := hr_utility.debug_enabled;
1924 l_procedure := g_package ||'update_ltc_element';
1925 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1926 IF (g_debug)
1927 THEN
1928 pay_in_utils.trace('**************************************************','********************');
1929 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1930 pay_in_utils.trace('p_employee_number',TO_CHAR (p_employee_number));
1931 pay_in_utils.trace('p_full_name',TO_CHAR (p_full_name));
1932 pay_in_utils.trace('p_start_date',TO_CHAR (p_start_date));
1933 pay_in_utils.trace('p_effective_end_date',TO_CHAR (p_effective_end_date));
1934 pay_in_utils.trace('p_fare',TO_CHAR (p_fare));
1935 pay_in_utils.trace('p_blockYr',TO_CHAR (p_blockYr));
1936 pay_in_utils.trace('p_carry',TO_CHAR (p_carry));
1937 pay_in_utils.trace('p_benefit',TO_CHAR (p_benefit));
1938 pay_in_utils.trace('p_assignment_id',TO_CHAR (p_assignment_id));
1939 pay_in_utils.trace(' p_element_entry_id',TO_CHAR (p_element_entry_id));
1940
1941 END IF;
1942
1943 BEGIN
1944 SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
1945 EXCEPTION
1946 WHEN NO_DATA_FOUND THEN
1947 INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),p_start_date);
1948 END ;
1949
1950 l_business_group_id := pay_in_med_web_adi.get_bg_id();
1951
1952 IF (g_debug)
1953 THEN
1954 pay_in_utils.trace('Business Group:',TO_CHAR (l_business_group_id));
1955 END IF;
1956
1957
1958 IF ((p_element_entry_id IS NOT NULL))
1959 THEN
1960
1961 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
1962
1963 OPEN c_get_ele_object_version(p_element_entry_id) ;
1964 FETCH c_get_ele_object_version INTO l_object_version_no ;
1965 CLOSE c_get_ele_object_version ;
1966
1967 OPEN c_get_ele_type_id(p_element_entry_id);
1968 FETCH c_get_ele_type_id INTO l_element_type_id ;
1969 CLOSE c_get_ele_type_id ;
1970
1971 l_count := 1;
1972 FOR c_rec IN c_input_rec(l_element_type_id,p_start_date)
1973 LOOP
1974 l_input_values_rec(l_count).input_name := c_rec.name;
1975 l_input_values_rec(l_count).input_value_id := c_rec.id;
1976 l_input_values_rec(l_count).value := c_rec.value;
1977
1978 IF (g_debug)
1979 THEN
1980 pay_in_utils.trace('Input Values Name:'||l_count,TO_CHAR (c_rec.name));
1981 END IF;
1982
1983 l_count := l_count + 1;
1984 END LOOP;
1985
1986
1987
1988 pay_element_entry_api.update_element_entry
1989 (p_datetrack_update_mode => hr_api.g_correction
1990 ,p_effective_date => p_start_date
1991 ,p_business_group_id => l_business_group_id
1992 ,p_element_entry_id => p_element_entry_id
1993 ,p_object_version_number => l_object_version_no
1994 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1995 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1996 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1997 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1998 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1999 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
2000 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
2001 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
2002 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
2003 ,p_entry_value1 => l_input_values_rec(1).value
2004 ,p_entry_value2 => l_input_values_rec(2).value
2005 ,p_entry_value3 => p_fare
2006 ,p_entry_value4 => p_blockYr
2007 ,p_entry_value5 => p_benefit
2008 ,p_entry_value6 => p_carry
2009 ,p_entry_value7 => l_input_values_rec(7).value
2010 ,p_entry_value8 => l_input_values_rec(8).value
2011 ,p_entry_value9 => l_input_values_rec(9).value
2012 ,p_effective_start_date => l_start_date
2013 ,p_effective_end_date => l_end_date
2014 ,p_update_warning => l_warnings
2015 );
2016
2017
2018 END IF ;
2019
2020 UPDATE per_assignment_extra_info
2021 SET AEI_INFORMATION18 = p_carry
2022 , AEI_INFORMATION21 = p_benefit
2023 where AEI_INFORMATION11 = p_element_entry_id
2024 and assignment_id = p_assignment_id;
2025
2026 p_warnings := 'FALSE';
2027 pay_in_utils.trace('**************************************************','********************');
2028 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 fnd_msg_pub.add_exc_msg
2032 (p_pkg_name => g_package
2033 ,p_procedure_name => 'update_ltc_element'
2034 ,p_error_text => substr(sqlerrm, 1, 240)
2035 );
2036
2037
2038 END update_ltc_element;
2039
2040 END pay_in_india_benefits;