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