[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_MED_WEB_ADI
Source
1 PACKAGE BODY pay_in_med_web_adi AS
2 /* $Header: pyinmadi.pkb 120.8.12010000.2 2008/11/10 09:29:00 rsaharay ship $ */
3 g_package CONSTANT VARCHAR2(100) := 'pay_in_med_web_adi.';
4 g_debug BOOLEAN ;
5
6 --------------------------------------------------------------------------
7 -- --
8 -- Name : GET_BG_ID --
9 -- Type : FUNCTION --
10 -- Access : Public --
11 -- Description : Function to return the business group id --
12 -- --
13 -- Parameters : --
14 -- IN : --
15 -- RETURN : VARCHAR2 --
16 ---------------------------------------------------------------------------
17 FUNCTION get_bg_id
18 RETURN NUMBER
19 IS
20 CURSOR c_bg
21 IS
22 SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
23 FROM dual;
24 --
25 l_bg NUMBER;
26 l_procedure VARCHAR2(250);
27 l_message VARCHAR2(250);
28 BEGIN
29 g_debug := hr_utility.debug_enabled;
30 l_procedure := g_package ||'get_bg_id';
31 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
32
33 OPEN c_bg;
34 FETCH c_bg INTO l_bg;
35 CLOSE c_bg;
36
37 IF (g_debug)
38 THEN
39 pay_in_utils.trace('**************************************************','********************');
40 pay_in_utils.trace('l_bg',l_bg);
41 pay_in_utils.trace('**************************************************','********************');
42 END IF;
43 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
44
45 RETURN l_bg;
46
47 END get_bg_id;
48
49 --------------------------------------------------------------------------
50 -- --
51 -- Name : CREATE_MEDICAL --
52 -- Type : PROCEDURE --
53 -- Access : Public --
54 -- Description : Function to create and update the Medical Bill and --
55 -- Benefit element enrty as per the Med Bill details --
56 -- passed from the Web ADI Excel Sheet. --
57 -- --
58 ---------------------------------------------------------------------------
59 PROCEDURE create_medical
60 (P_TAX_YEAR IN VARCHAR2
61 ,P_MONTH IN VARCHAR2 DEFAULT NULL
62 ,P_BILL_DATE IN DATE
63 ,P_NAME IN VARCHAR2
64 ,P_BILL_NUMBER IN VARCHAR2 DEFAULT NULL
65 ,P_BILL_AMOUNT IN NUMBER DEFAULT NULL
66 ,P_APPROVED_BILL_AMOUNT IN NUMBER
67 ,P_EMPLOYEE_REMARKS IN VARCHAR2 DEFAULT NULL
68 ,P_EMPLOYER_REMARKS IN VARCHAR2 DEFAULT NULL
69 ,P_ELEMENT_ENTRY_ID IN NUMBER DEFAULT NULL
70 ,P_LAST_UPDATED_DATE IN DATE DEFAULT NULL
71 ,P_ASSIGNMENT_ID IN NUMBER
72 ,P_EMPLOYEE_ID IN NUMBER
73 ,P_EMPLOYEE_NAME IN VARCHAR2
74 ,P_ASSIGNMENT_EXTRA_INFO_ID IN NUMBER
75 ,P_ENTRY_DATE IN DATE DEFAULT NULL
76 )
77 IS
78
79 CURSOR c_element_name(p_business_group_id NUMBER)
80 IS
81 SELECT hoi.org_information1
82 ,hoi.org_information2
83 FROM hr_organization_information hoi
84 WHERE hoi.organization_id = p_business_group_id
85 AND org_information_context='PER_IN_REIMBURSE_ELEMENTS';
86
87
88 --Get Element Details (type id and link id)
89 CURSOR csr_element_details(p_assignment_id NUMBER
90 ,p_effective_date DATE
91 ,p_element_name VARCHAR2
92 )
93 IS
94 SELECT types.element_type_id
95 ,link.element_link_id
96 FROM per_assignments_f assgn
97 , pay_element_links_f link
98 , pay_element_types_f types
99 WHERE assgn.assignment_id = p_assignment_id
100 AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
101 ,P_ENTRY_DATE
102 ,types.element_type_id
103 )
104 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
105 AND link.business_group_id = assgn.business_group_id
106 AND link.element_type_id = types.element_type_id
107 AND types.element_type_id = p_element_name
108 AND p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
109 AND p_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
110 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
111
112
113 CURSOR c_input_rec(p_element_type_id NUMBER
114 ,p_effective_date DATE
115 )
116 IS
117 SELECT inputs.name name
118 , inputs.input_value_id id
119 , inputs.default_value value
120 FROM pay_element_types_f types
121 , pay_input_values_f inputs
122 WHERE types.element_type_id = p_element_type_id
123 AND inputs.element_type_id = types.element_type_id
124 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
125 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
126 ORDER BY inputs.display_sequence;
127
128
129
130
131 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
132 IS
133 SELECT object_version_number
134 ,effective_start_date
135 FROM pay_element_entries_f
136 WHERE element_entry_id = p_element_entryid;
137
138 CURSOR c_get_screen_value (p_element_entryid NUMBER
139 ,p_input NUMBER )
140 IS
141 SELECT screen_entry_value
142 FROM pay_element_entry_values_f
143 WHERE element_entry_id = p_element_entryid
144 AND input_value_id = p_input;
145
146
147 CURSOR c_check_element_entry(p_element_type_id NUMBER
148 ,p_effective_date DATE )
149 IS
150 SELECT pee.element_entry_id
151 FROM pay_element_entries_f pee
152 WHERE pee.element_type_id = p_element_type_id
153 AND pee.assignment_id = p_assignment_id
154 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
155
156 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
157 IS
158 SELECT element_type_id
159 FROM pay_element_entries_f
160 WHERE element_entry_id = p_element_entryid;
161
162 CURSOR c_get_prev_amts
163 IS
164 SELECT pae.aei_information7, pae.aei_information11
165 FROM per_assignment_extra_info pae
166 WHERE pae.assignment_extra_info_id = p_assignment_extra_info_id;
167
168 CURSOR c_element(p_element_type_id NUMBER)
169 IS
170 SELECT element_name
171 FROM pay_element_types_f
172 WHERE element_type_id = p_element_type_id ;
173
174
175 --Variables Initialization
176 TYPE t_input_values_rec IS RECORD
177 (input_name pay_input_values_f.name%TYPE
178 ,input_value_id pay_input_values_f.input_value_id%TYPE
179 ,value pay_input_values_f.default_value%TYPE
180 );
181
182 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
183
184 l_procedure VARCHAR2(250);
185 l_message VARCHAR2(250);
186 l_warnings BOOLEAN;
187 l_input_values_rec t_input_values_tab;
188 l_ben_input_values_rec t_input_values_tab;
189 l_business_group_id NUMBER;
190 l_medical_bill_element hr_organization_information.org_information2%TYPE ;
191 l_medical_ben_element hr_organization_information.org_information2%TYPE ;
192 l_element pay_element_types.element_name%TYPE ;
193 l_count NUMBER;
194 l_assignment_id NUMBER;
195 l_bill_element_type_id NUMBER;
196 l_bill_element_link_id NUMBER;
197 l_ben_element_type_id NUMBER;
198 l_ben_element_link_id NUMBER;
199 l_element_entry_id NUMBER := NULL ;
200 l_bill_entry_id NUMBER := NULL ;
201 l_ben_entry_id NUMBER := NULL ;
202 l_start_date DATE ;
203 l_end_date DATE ;
204 l_effective_start_date DATE ;
205 l_ben_start_date DATE ;
206 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
207 l_ben_version_no per_assignment_extra_info.object_version_number%TYPE ;
208 l_entry_information1 pay_element_entries_f.entry_information2%TYPE ;
209 l_entry_information3 pay_element_entries_f.entry_information2%TYPE ;
210 l_entry_information5 pay_element_entries_f.entry_information2%TYPE ;
211 l_entry_information7 pay_element_entries_f.entry_information2%TYPE ;
212 l_check NUMBER :=0;
213 l_approved_bill_amount NUMBER ;
214 l_prev_bill_amount NUMBER ;
215
216
217
218
219 BEGIN
220
221 --hr_utility.trace_on(null,'LNAGARAJ');
222 g_debug := hr_utility.debug_enabled;
223 l_procedure := g_package ||'create_medical';
224 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
225 IF (g_debug)
226 THEN
227 pay_in_utils.trace('**************************************************','********************');
228 pay_in_utils.set_location(g_debug,'Input Paramters values are',20);
229 pay_in_utils.trace('p_tax_year',TO_CHAR (p_tax_year));
230 pay_in_utils.trace('p_month',TO_CHAR (p_month));
231 pay_in_utils.trace('p_bill_date',TO_CHAR (p_bill_date));
232 pay_in_utils.trace('p_name',TO_CHAR (p_name));
233 pay_in_utils.trace('p_bill_number',TO_CHAR (p_bill_number));
234 pay_in_utils.trace('p_bill_amount',TO_CHAR (p_bill_amount));
235 pay_in_utils.trace('p_approved_bill_amount',TO_CHAR (p_approved_bill_amount));
236 pay_in_utils.trace('p_employee_remarks',TO_CHAR (p_employee_remarks));
237 pay_in_utils.trace('p_employer_remarks',TO_CHAR (p_employer_remarks));
238 pay_in_utils.trace('p_element_entry_id',TO_CHAR (p_element_entry_id));
239 pay_in_utils.trace('p_last_updated_date',TO_CHAR (p_last_updated_date));
240 pay_in_utils.trace('p_assignment_id',TO_CHAR (p_assignment_id));
241 pay_in_utils.trace('p_employee_id',TO_CHAR (p_employee_id));
242 pay_in_utils.trace('p_employee_name',TO_CHAR (p_employee_name));
243 pay_in_utils.trace('p_assignment_extra_info_id',TO_CHAR (p_assignment_extra_info_id));
244 pay_in_utils.trace('p_entry_date',TO_CHAR(p_entry_date));
245
246
247 END IF;
248
249 IF P_BILL_AMOUNT < 0 THEN
250 hr_utility.set_message(800, 'PER_IN_BEN_AMOUNT');
251 hr_utility.raise_error;
252 END IF ;
253
254 IF P_APPROVED_BILL_AMOUNT < 0 THEN
255 hr_utility.set_message(800, 'PER_IN_BEN_APPROVED_AMOUNT');
256 hr_utility.raise_error;
257 END IF ;
258
259
260 l_business_group_id := get_bg_id();
261
262 IF (g_debug)
263 THEN
264 pay_in_utils.trace('l_business_group_id',TO_CHAR (l_business_group_id));
265 END IF;
266
267 IF ((p_element_entry_id IS NOT NULL))
268 THEN
269
270 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
271
272 /*Code to change the Medical Bill Entry if the approved amount of a existing medical
273 bill has been changed.*/
274
275 l_element_entry_id := p_element_entry_id;
276
277 OPEN c_get_ele_object_version(p_element_entry_id) ;
278 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
279 CLOSE c_get_ele_object_version ;
280
281 OPEN c_get_ele_type_id(p_element_entry_id);
282 FETCH c_get_ele_type_id INTO l_bill_element_type_id ;
283 CLOSE c_get_ele_type_id ;
284
285 OPEN c_get_prev_amts ;
286 FETCH c_get_prev_amts INTO l_prev_bill_amount,l_ben_entry_id ;
287 CLOSE c_get_prev_amts ;
288
289
290
291 l_count := 1;
292 FOR c_rec IN c_input_rec(l_bill_element_type_id,l_effective_start_date)
293 LOOP
294 l_input_values_rec(l_count).input_name := c_rec.name;
295 l_input_values_rec(l_count).input_value_id := c_rec.id;
296 l_input_values_rec(l_count).value := c_rec.value;
297
298 IF (g_debug)
299 THEN
300 pay_in_utils.trace('Input Value Name:'||l_count,TO_CHAR (c_rec.name));
301 pay_in_utils.trace('l_input_values_rec(1).input_value_id',TO_CHAR (l_input_values_rec(1).input_value_id));
302 END IF;
303
304 l_count := l_count + 1;
305 END LOOP;
306
307
308
309
310 OPEN c_get_screen_value(P_ELEMENT_ENTRY_ID, l_input_values_rec(1).input_value_id);
311 FETCH c_get_screen_value INTO l_entry_information1 ;
312 CLOSE c_get_screen_value ;
313
314 IF (g_debug)
315 THEN
316 pay_in_utils.trace('l_entry_information1',TO_CHAR (l_entry_information1));
317 END IF;
318
319
320
321 IF (g_debug)
322 THEN
323 pay_in_utils.trace('l_prev_bill_amount',TO_CHAR (l_prev_bill_amount));
324 END IF;
325
326
327
328 l_approved_bill_amount := NVL(p_approved_bill_amount,0) + NVL(l_entry_information1,0) - NVL(l_prev_bill_amount,0);
329 l_approved_bill_amount := greatest(l_approved_bill_amount,0);
330
331 pay_element_entry_api.update_element_entry
332 (p_datetrack_update_mode => hr_api.g_correction
333 ,p_effective_date => l_effective_start_date
334 ,p_business_group_id => l_business_group_id
335 ,p_element_entry_id => p_element_entry_id
336 ,p_object_version_number => l_object_version_no
337 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
338 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
339 ,p_entry_value1 => l_approved_bill_amount
340 ,p_entry_value2 => l_input_values_rec(2).value
341 ,p_effective_start_date => l_start_date
342 ,p_effective_end_date => l_end_date
343 ,p_update_warning => l_warnings
344 );
345
346
347 l_object_version_no := NULL ;
348
349 /*Code to change the Medical Benefit Entry if the approved
350 amount of an existing bill has been modified.*/
351 IF l_ben_entry_id IS NOT NULL
352 THEN
353
354 OPEN c_get_ele_type_id(l_ben_entry_id);
355 FETCH c_get_ele_type_id INTO l_ben_element_type_id ;
356 CLOSE c_get_ele_type_id ;
357
358 OPEN c_get_ele_object_version(l_ben_entry_id) ;
359 FETCH c_get_ele_object_version INTO l_ben_version_no,l_ben_start_date ;
360 CLOSE c_get_ele_object_version ;
361
362
363 l_count := 1;
364 FOR c_rec IN c_input_rec(l_ben_element_type_id,l_ben_start_date)
365 LOOP
366 l_ben_input_values_rec(l_count).input_name := c_rec.name;
367 l_ben_input_values_rec(l_count).input_value_id := c_rec.id;
368 l_ben_input_values_rec(l_count).value := c_rec.value;
369
370 IF (g_debug)
371 THEN
372 pay_in_utils.trace('Benefit Input Value Name:'||l_count,TO_CHAR (c_rec.name));
373 END IF;
374
375 l_count := l_count + 1;
376 END LOOP;
377
378
379 OPEN c_get_screen_value(l_ben_entry_id, l_ben_input_values_rec(3).input_value_id);
380 FETCH c_get_screen_value INTO l_entry_information3 ;
381 CLOSE c_get_screen_value ;
382
383 OPEN c_get_screen_value(l_ben_entry_id, l_ben_input_values_rec(5).input_value_id);
384 FETCH c_get_screen_value INTO l_entry_information5 ;
385 CLOSE c_get_screen_value ;
386
387 OPEN c_get_screen_value(l_ben_entry_id, l_ben_input_values_rec(7).input_value_id);
388 FETCH c_get_screen_value INTO l_entry_information7 ;
389 CLOSE c_get_screen_value ;
390
391 IF (g_debug)
392 THEN
393 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
394 pay_in_utils.trace('l_entry_information5',TO_CHAR (l_entry_information5));
395 pay_in_utils.trace('l_entry_information7',TO_CHAR (l_entry_information7));
396 END IF;
397
398
399 l_approved_bill_amount := NVL(p_approved_bill_amount,0) + NVL(l_entry_information3,0) - NVL(l_prev_bill_amount,0);
400 l_approved_bill_amount := GREATEST (l_approved_bill_amount,0);
401
402
403 pay_element_entry_api.update_element_entry
404 (p_datetrack_update_mode => hr_api.g_correction
405 ,p_effective_date => l_ben_start_date
406 ,p_business_group_id => l_business_group_id
407 ,p_element_entry_id => l_ben_entry_id
408 ,p_object_version_number => l_ben_version_no
409 ,p_input_value_id1 => l_ben_input_values_rec(1).input_value_id
410 ,p_input_value_id2 => l_ben_input_values_rec(2).input_value_id
411 ,p_input_value_id3 => l_ben_input_values_rec(3).input_value_id
412 ,p_input_value_id4 => l_ben_input_values_rec(4).input_value_id
413 ,p_input_value_id5 => l_ben_input_values_rec(5).input_value_id
414 ,p_input_value_id6 => l_ben_input_values_rec(6).input_value_id
415 ,p_input_value_id7 => l_ben_input_values_rec(7).input_value_id
416 ,p_input_value_id8 => l_ben_input_values_rec(8).input_value_id
417 ,p_input_value_id9 => l_ben_input_values_rec(9).input_value_id
418 ,p_entry_value1 => l_ben_input_values_rec(1).value
419 ,p_entry_value2 => l_ben_input_values_rec(2).value
420 ,p_entry_value3 => l_approved_bill_amount
421 ,p_entry_value4 => l_ben_input_values_rec(4).value
422 ,p_entry_value5 => l_entry_information5
423 ,p_entry_value6 => l_ben_input_values_rec(6).value
424 ,p_entry_value7 => l_entry_information7
425 ,p_entry_value8 => l_ben_input_values_rec(8).value
426 ,p_entry_value9 => l_ben_input_values_rec(9).value
427 ,p_effective_start_date => l_start_date
428 ,p_effective_end_date => l_end_date
429 ,p_update_warning => l_warnings
430 );
431
432
433 l_ben_version_no := NULL ;
434
435 END IF ;
436 ELSIF ((p_element_entry_id IS NULL) )
437 THEN
438
439
440 OPEN c_element_name(l_business_group_id);
441 FETCH c_element_name INTO l_medical_bill_element, l_medical_ben_element ;
442 CLOSE c_element_name ;
443
444 IF (g_debug)
445 THEN
446 pay_in_utils.trace('l_medical_bill_element',TO_CHAR (l_medical_bill_element));
447 pay_in_utils.trace('l_medical_ben_element ',TO_CHAR (l_medical_ben_element));
448 END IF;
449
450 OPEN csr_element_details(p_assignment_id, P_ENTRY_DATE, l_medical_bill_element) ;
451 FETCH csr_element_details INTO l_bill_element_type_id, l_bill_element_link_id ;
452 CLOSE csr_element_details ;
453
454 OPEN csr_element_details(p_assignment_id, P_ENTRY_DATE, l_medical_ben_element) ;
455 FETCH csr_element_details INTO l_ben_element_type_id, l_ben_element_link_id ;
456 CLOSE csr_element_details ;
457
458
459 IF (g_debug)
460 THEN
461 pay_in_utils.trace('l_bill_element_type_id',TO_CHAR (l_bill_element_type_id));
462 pay_in_utils.trace('l_bill_element_link_id',TO_CHAR (l_bill_element_link_id));
463 pay_in_utils.trace('l_ben_element_type_id ',TO_CHAR (l_ben_element_type_id));
464 pay_in_utils.trace('l_ben_element_link_id ',TO_CHAR (l_ben_element_link_id));
465 END IF;
466
467 OPEN c_check_element_entry(l_bill_element_type_id, P_ENTRY_DATE);
468 FETCH c_check_element_entry INTO l_element_entry_id ;
469 CLOSE c_check_element_entry ;
470
471 IF l_element_entry_id IS NOT NULL THEN
472
473 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,40);
474
475 /*Code to change the Medical Bill Entry if an additional bill has been approved.*/
476 OPEN c_get_ele_object_version(l_element_entry_id) ;
477 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
478 CLOSE c_get_ele_object_version ;
479
480
481 l_count := 1;
482 FOR c_rec IN c_input_rec(l_bill_element_type_id,l_effective_start_date)
483 LOOP
484 l_input_values_rec(l_count).input_name := c_rec.name;
485 l_input_values_rec(l_count).input_value_id := c_rec.id;
486 l_input_values_rec(l_count).value := c_rec.value;
487
488 IF (g_debug)
489 THEN
490 pay_in_utils.trace('Bill'||l_count,TO_CHAR (c_rec.name));
491 END IF;
492
493 l_count := l_count + 1;
494 END LOOP;
495
496
497
498 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(1).input_value_id);
499 FETCH c_get_screen_value INTO l_entry_information1 ;
500 CLOSE c_get_screen_value ;
501
502 IF (g_debug)
503 THEN
504 pay_in_utils.trace('l_entry_information1',TO_CHAR (l_entry_information1));
505 END IF;
506
507
508 l_approved_bill_amount := NVL(p_approved_bill_amount,0) + NVL(l_entry_information1,0) ;
509 l_approved_bill_amount := GREATEST (l_approved_bill_amount,0);
510
511 pay_element_entry_api.update_element_entry
512 (p_datetrack_update_mode => hr_api.g_correction
513 ,p_effective_date => l_effective_start_date
514 ,p_business_group_id => l_business_group_id
515 ,p_element_entry_id => l_element_entry_id
516 ,p_object_version_number => l_object_version_no
517 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
518 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
519 ,p_entry_value1 => l_approved_bill_amount
520 ,p_entry_value2 => l_input_values_rec(2).value
521 ,p_effective_start_date => l_start_date
522 ,p_effective_end_date => l_end_date
523 ,p_update_warning => l_warnings
524 );
525
526
527 l_object_version_no := NULL ;
528
529
530 /*Code to change the Medical Benefit Entry if an additional bill has been approved.*/
531 OPEN c_check_element_entry(l_ben_element_type_id, P_ENTRY_DATE);
532 FETCH c_check_element_entry INTO l_ben_entry_id ;
533 CLOSE c_check_element_entry ;
534
535 IF l_ben_entry_id IS NOT NULL
536 THEN
537
538 OPEN c_get_ele_object_version(l_ben_entry_id) ;
539 FETCH c_get_ele_object_version INTO l_ben_version_no,l_ben_start_date ;
540 CLOSE c_get_ele_object_version ;
541
542 l_count := 1;
543 FOR c_rec IN c_input_rec(l_ben_element_type_id,l_ben_start_date)
544 LOOP
545 l_ben_input_values_rec(l_count).input_name := c_rec.name;
546 l_ben_input_values_rec(l_count).input_value_id := c_rec.id;
547 l_ben_input_values_rec(l_count).value := c_rec.value;
548
549 IF (g_debug)
550 THEN
551 pay_in_utils.trace('Benefit Input Value Name:'||l_count,TO_CHAR (c_rec.name));
552 END IF;
553
554 l_count := l_count + 1;
555 END LOOP;
556
557
558 OPEN c_get_screen_value(l_ben_entry_id, l_ben_input_values_rec(3).input_value_id);
559 FETCH c_get_screen_value INTO l_entry_information3 ;
560 CLOSE c_get_screen_value ;
561
562 OPEN c_get_screen_value(l_ben_entry_id, l_ben_input_values_rec(5).input_value_id);
563 FETCH c_get_screen_value INTO l_entry_information5 ;
564 CLOSE c_get_screen_value ;
565
566 OPEN c_get_screen_value(l_ben_entry_id, l_ben_input_values_rec(7).input_value_id);
567 FETCH c_get_screen_value INTO l_entry_information7 ;
568 CLOSE c_get_screen_value ;
569
570
571 IF (g_debug)
572 THEN
573 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
574 pay_in_utils.trace('l_entry_information5',TO_CHAR (l_entry_information5));
575 pay_in_utils.trace('l_entry_information7',TO_CHAR (l_entry_information7));
576 END IF;
577
578
579 l_approved_bill_amount := NVL(p_approved_bill_amount,0) + NVL(l_entry_information3,0) ;
580 l_approved_bill_amount := GREATEST (l_approved_bill_amount,0);
581
582 pay_element_entry_api.update_element_entry
583 (p_datetrack_update_mode => hr_api.g_correction
584 ,p_effective_date => l_ben_start_date
585 ,p_business_group_id => l_business_group_id
586 ,p_element_entry_id => l_ben_entry_id
587 ,p_object_version_number => l_ben_version_no
588 ,p_input_value_id1 => l_ben_input_values_rec(1).input_value_id
589 ,p_input_value_id2 => l_ben_input_values_rec(2).input_value_id
590 ,p_input_value_id3 => l_ben_input_values_rec(3).input_value_id
591 ,p_input_value_id4 => l_ben_input_values_rec(4).input_value_id
592 ,p_input_value_id5 => l_ben_input_values_rec(5).input_value_id
593 ,p_input_value_id6 => l_ben_input_values_rec(6).input_value_id
594 ,p_input_value_id7 => l_ben_input_values_rec(7).input_value_id
595 ,p_input_value_id8 => l_ben_input_values_rec(8).input_value_id
596 ,p_input_value_id9 => l_ben_input_values_rec(9).input_value_id
597 ,p_entry_value1 => l_ben_input_values_rec(1).value
598 ,p_entry_value2 => l_ben_input_values_rec(2).value
599 ,p_entry_value3 => l_approved_bill_amount
600 ,p_entry_value4 => l_ben_input_values_rec(4).value
601 ,p_entry_value5 => l_entry_information5
602 ,p_entry_value6 => l_ben_input_values_rec(6).value
603 ,p_entry_value7 => l_entry_information7
604 ,p_entry_value8 => l_ben_input_values_rec(8).value
605 ,p_entry_value9 => l_ben_input_values_rec(9).value
606 ,p_effective_start_date => l_start_date
607 ,p_effective_end_date => l_end_date
608 ,p_update_warning => l_warnings
609 );
610
611
612 l_ben_version_no := NULL ;
613
614 END IF ;
615
616
617
618 ELSE
619
620 IF l_bill_element_link_id IS NULL THEN
621
622 OPEN c_element(TO_NUMBER(l_medical_bill_element));
623 FETCH c_element INTO l_element;
624 CLOSE c_element;
625
626 hr_utility.set_message(800, 'PER_IN_MISSING_LINK');
627 hr_utility.set_message_token('ELEMENT_NAME', l_element);
628 hr_utility.raise_error;
629 END IF;
630
631 IF l_ben_element_link_id IS NULL THEN
632
633 OPEN c_element(TO_NUMBER(l_medical_ben_element));
634 FETCH c_element INTO l_element;
635 CLOSE c_element;
636
637 hr_utility.set_message(800, 'PER_IN_MISSING_LINK');
638 hr_utility.set_message_token('ELEMENT_NAME', l_element);
639 hr_utility.raise_error;
640 END IF;
641
642 --Populate the input value id, name records
643 l_count := 1;
644 FOR c_rec IN c_input_rec(l_bill_element_type_id,P_ENTRY_DATE)
645 LOOP
646 l_input_values_rec(l_count).input_name := c_rec.name;
647 l_input_values_rec(l_count).input_value_id := c_rec.id;
648 l_input_values_rec(l_count).value := c_rec.value;
649
650 IF (g_debug)
651 THEN
652 pay_in_utils.trace('Bill Input Value Name:'||l_count,TO_CHAR (c_rec.name));
653 END IF;
654
655 l_count := l_count + 1;
656 END LOOP;
657
658 l_count := 1;
659 FOR c_rec IN c_input_rec(l_ben_element_type_id,P_ENTRY_DATE)
660 LOOP
661 l_ben_input_values_rec(l_count).input_name := c_rec.name;
662 l_ben_input_values_rec(l_count).input_value_id := c_rec.id;
663 l_ben_input_values_rec(l_count).value := c_rec.value;
664
665 IF (g_debug)
666 THEN
667 pay_in_utils.trace('Benefit Input Value Name:'||l_count,TO_CHAR (c_rec.name));
668 END IF;
669
670 l_count := l_count + 1;
671 END LOOP;
672
673
674
675
676
677
678 pay_in_utils.set_location(g_debug,'Creating Benefit Element Entries: '||l_procedure,50);
679
680 IF(p_approved_bill_amount IS NOT NULL) THEN
681 pay_element_entry_api.create_element_entry
682 (p_effective_date => p_entry_date
683 ,p_business_group_id => l_business_group_id
684 ,p_assignment_id => p_assignment_id
685 ,p_element_link_id => l_ben_element_link_id
686 ,p_entry_type => 'E'
687 ,p_input_value_id1 => l_ben_input_values_rec(1).input_value_id
688 ,p_input_value_id2 => l_ben_input_values_rec(2).input_value_id
689 ,p_input_value_id3 => l_ben_input_values_rec(3).input_value_id
690 ,p_input_value_id4 => l_ben_input_values_rec(4).input_value_id
691 ,p_input_value_id5 => l_ben_input_values_rec(5).input_value_id
692 ,p_input_value_id6 => l_ben_input_values_rec(6).input_value_id
693 ,p_input_value_id7 => l_ben_input_values_rec(7).input_value_id
694 ,p_input_value_id8 => l_ben_input_values_rec(8).input_value_id
695 ,p_input_value_id9 => l_ben_input_values_rec(9).input_value_id
696 ,p_entry_value1 => l_ben_input_values_rec(1).value
697 ,p_entry_value2 => l_ben_input_values_rec(2).value
698 ,p_entry_value3 => p_approved_bill_amount
699 ,p_entry_value4 => l_ben_input_values_rec(4).value
700 ,p_entry_value5 => l_ben_input_values_rec(5).value
701 ,p_entry_value6 => l_ben_input_values_rec(6).value
702 ,p_entry_value7 => l_ben_input_values_rec(7).value
703 ,p_entry_value8 => l_ben_input_values_rec(8).value
704 ,p_entry_value9 => l_ben_input_values_rec(9).value
705 ,p_effective_start_date => l_start_date
706 ,p_effective_end_date => l_end_date
707 ,p_element_entry_id => l_ben_entry_id
708 ,p_object_version_number => l_object_version_no
709 ,p_create_warning => l_warnings
710 );
711 END IF;
712 pay_in_utils.set_location(g_debug,'Benefit Element Creation Completed'||l_procedure,60);
713
714
715 pay_in_utils.set_location(g_debug,'Creating Benefit Bill Element Entries: '||l_procedure,70);
716
717 IF(p_approved_bill_amount IS NOT NULL) THEN
718 pay_element_entry_api.create_element_entry
719 (p_effective_date => p_entry_date
720 ,p_business_group_id => l_business_group_id
721 ,p_assignment_id => p_assignment_id
722 ,p_element_link_id => l_bill_element_link_id
723 ,p_entry_type => 'E'
724 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
725 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
726 ,p_entry_value1 => p_approved_bill_amount
727 ,p_entry_value2 => l_input_values_rec(2).value
728 ,p_effective_start_date => l_start_date
729 ,p_effective_end_date => l_end_date
730 ,p_element_entry_id => l_bill_entry_id
731 ,p_object_version_number => l_object_version_no
732 ,p_create_warning => l_warnings
733 );
734 END IF;
735
736 pay_in_utils.set_location(g_debug,'Benefit Bill Element Creation Completed'||l_procedure,80);
737
738 END IF ;
739 END IF;
740
741
742 pay_in_utils.set_location(g_debug,'Updating Assignment_Extra_Info: '||l_procedure,90);
743
744
745
746
747 UPDATE per_assignment_extra_info
748 SET AEI_INFORMATION7 = p_approved_bill_amount
749 ,AEI_INFORMATION9 = p_employer_remarks
750 ,AEI_INFORMATION10 = nvl(l_bill_entry_id,l_element_entry_id)
751 ,AEI_INFORMATION11 = l_ben_entry_id
752 WHERE ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;
753
754
755 pay_in_utils.trace('**************************************************','********************');
756 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,100);
757 END create_medical;
758
759
760 --------------------------------------------------------------------------
761 -- --
762 -- Name : CREATE_MEDICAL_BEN --
763 -- Type : PROCEDURE --
764 -- Access : Public --
765 -- Description : Function to update the Med Ben element enrty as per --
766 -- the details passed from the Web ADI Excel Sheet. --
767 -- --
768 ---------------------------------------------------------------------------
769
770 PROCEDURE create_medical_ben
771 ( P_employee_number IN VARCHAR2
772 ,P_full_name IN VARCHAR2
773 ,P_effective_start_date IN DATE
774 ,P_effective_end_date IN DATE DEFAULT NULL
775 ,P_Benefit IN NUMBER
776 ,P_Add_to_NetPay IN VARCHAR2
777 ,P_AnnualLimit IN NUMBER DEFAULT NULL
778 ,P_assignment_id IN NUMBER
779 ,P_element_entry_id IN NUMBER DEFAULT NULL
780 )
781
782 IS
783
784 CURSOR c_input_rec(p_element_type_id NUMBER
785 ,p_effective_date DATE
786 )
787 IS
788 SELECT inputs.name name
789 , inputs.input_value_id id
790 , inputs.default_value value
791 FROM pay_element_types_f types
792 , pay_input_values_f inputs
793 WHERE types.element_type_id = p_element_type_id
794 AND inputs.element_type_id = types.element_type_id
795 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
796 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
797 ORDER BY inputs.display_sequence;
798
799
800
801 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
802 IS
803 SELECT object_version_number
804 FROM pay_element_entries_f
805 WHERE element_entry_id = p_element_entryid;
806
807
808 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
809 IS
810 SELECT element_type_id
811 FROM pay_element_entries_f
812 WHERE element_entry_id = p_element_entryid;
813
814
815 --Variables Initialization
816 TYPE t_input_values_rec IS RECORD
817 (input_name pay_input_values_f.name%TYPE
818 ,input_value_id pay_input_values_f.input_value_id%TYPE
819 ,value pay_input_values_f.default_value%TYPE
820 );
821
822 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
823
824 l_procedure VARCHAR2(250);
825 l_warnings BOOLEAN;
826 l_input_values_rec t_input_values_tab;
827 l_business_group_id NUMBER;
828 l_count NUMBER;
829 l_element_type_id NUMBER;
830 l_start_date DATE ;
831 l_end_date DATE ;
832 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
833
834
835
836 BEGIN
837
838
839 g_debug := hr_utility.debug_enabled;
840 l_procedure := g_package ||'create_medical_ben';
841 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
842 IF (g_debug)
843 THEN
844 pay_in_utils.trace('**************************************************','********************');
845 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
846 pay_in_utils.trace('P_employee_number',TO_CHAR (P_employee_number));
847 pay_in_utils.trace('P_full_name',TO_CHAR (P_full_name));
848 pay_in_utils.trace('P_effective_start_date',TO_CHAR (P_effective_start_date));
849 pay_in_utils.trace('P_effective_end_date',TO_CHAR (P_effective_end_date));
850 pay_in_utils.trace('P_Benefit',TO_CHAR (P_Benefit));
851 pay_in_utils.trace('P_Add_to_NetPay',TO_CHAR (P_Add_to_NetPay));
852 pay_in_utils.trace('P_AnnualLimit',TO_CHAR (P_AnnualLimit));
853 pay_in_utils.trace('P_assignment_id',TO_CHAR (P_assignment_id));
854 pay_in_utils.trace('P_element_entry_id',TO_CHAR (P_element_entry_id));
855
856
857 END IF;
858
859 IF P_Benefit < 0 THEN
860 hr_utility.set_message(800, 'PER_IN_BEN_APPROVED_AMOUNT');
861 hr_utility.raise_error;
862 END IF ;
863
864
865
866 l_business_group_id := get_bg_id();
867
868 IF (g_debug)
869 THEN
870 pay_in_utils.trace('Business Group:',TO_CHAR (l_business_group_id));
871 END IF;
872
873
874 IF ((p_element_entry_id IS NOT NULL))
875 THEN
876
877 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
878
879 OPEN c_get_ele_object_version(p_element_entry_id) ;
880 FETCH c_get_ele_object_version INTO l_object_version_no ;
881 CLOSE c_get_ele_object_version ;
882
883 OPEN c_get_ele_type_id(p_element_entry_id);
884 FETCH c_get_ele_type_id INTO l_element_type_id ;
885 CLOSE c_get_ele_type_id ;
886
887 l_count := 1;
888 FOR c_rec IN c_input_rec(l_element_type_id,P_effective_start_date)
889 LOOP
890 l_input_values_rec(l_count).input_name := c_rec.name;
891 l_input_values_rec(l_count).input_value_id := c_rec.id;
892 l_input_values_rec(l_count).value := c_rec.value;
893
894 IF (g_debug)
895 THEN
896 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
897 END IF;
898
899 l_count := l_count + 1;
900 END LOOP;
901
902
903
904 pay_element_entry_api.update_element_entry
905 (p_datetrack_update_mode => hr_api.g_correction
906 ,p_effective_date => P_effective_start_date
907 ,p_business_group_id => l_business_group_id
908 ,p_element_entry_id => p_element_entry_id
909 ,p_object_version_number => l_object_version_no
910 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
911 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
912 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
913 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
914 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
915 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
916 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
917 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
918 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
919 ,p_entry_value1 => l_input_values_rec(1).value
920 ,p_entry_value2 => l_input_values_rec(2).value
921 ,p_entry_value3 => P_Benefit
922 ,p_entry_value4 => l_input_values_rec(4).value
923 ,p_entry_value5 => P_AnnualLimit
924 ,p_entry_value6 => l_input_values_rec(6).value
925 ,p_entry_value7 => P_Add_to_NetPay
926 ,p_entry_value8 => l_input_values_rec(8).value
927 ,p_entry_value9 => l_input_values_rec(9).value
928 ,p_effective_start_date => l_start_date
929 ,p_effective_end_date => l_end_date
930 ,p_update_warning => l_warnings
931 );
932
933
934
935 END IF ;
936
937
938 pay_in_utils.trace('**************************************************','********************');
939 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
940
941
942
943
944 END create_medical_ben;
945
946
947 --------------------------------------------------------------------------
948 -- --
949 -- Name : CREATE_LTC_ELEMENT --
950 -- Type : PROCEDURE --
951 -- Access : Public --
952 -- Description : Function to create and update the LTC element enrty --
953 -- as per the LTC Bill details passed from the Web ADI --
954 -- Excel Sheet. --
955 -- --
956 ---------------------------------------------------------------------------
957
958 PROCEDURE create_ltc_element
959 (
960 P_LTCBLOCK IN VARCHAR2
961 ,P_PLACE_FROM IN VARCHAR2
962 ,P_PLACE_TO IN VARCHAR2
963 ,P_MODE_CLASS IN VARCHAR2
964 ,P_CARRY_OVER IN VARCHAR2 DEFAULT NULL
965 ,P_SUBMITTED IN NUMBER
966 ,P_EXEMPTED IN NUMBER DEFAULT NULL
967 ,P_ELEMENT_ENTRY_ID IN NUMBER
968 ,P_START_DATE IN DATE
969 ,P_END_DATE IN DATE
970 ,P_BILL_NUM IN VARCHAR2 DEFAULT NULL
971 ,P_EE_COMMENTS IN VARCHAR2 DEFAULT NULL
972 ,P_ER_COMMENTS IN VARCHAR2 DEFAULT NULL
973 ,P_LAST_UPDATED_DATE IN DATE
974 ,P_ASSIGNMENT_ID IN NUMBER
975 ,P_EMPLOYEE_ID IN NUMBER
976 ,P_ASSIGNMENT_EXTRA_INFO_ID IN NUMBER
977 ,P_ENTRY_DATE IN DATE DEFAULT NULL
978 )
979 IS
980 CURSOR c_element_name(p_business_group_id NUMBER)
981 IS
982 SELECT hoi.org_information3
983 FROM hr_organization_information hoi
984 WHERE hoi.organization_id = p_business_group_id
985 AND org_information_context='PER_IN_REIMBURSE_ELEMENTS';
986
987
988
989 --Get Element Details (type id and link id)
990 CURSOR csr_element_details(p_assignment_id NUMBER
991 ,p_effective_date DATE
992 ,p_element_name VARCHAR2
993 )
994 IS
995 SELECT types.element_type_id
996 ,link.element_link_id
997 FROM per_assignments_f assgn
998 , pay_element_links_f link
999 , pay_element_types_f types
1000 WHERE assgn.assignment_id = p_assignment_id
1001 AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
1002 ,P_ENTRY_DATE
1003 ,types.element_type_id
1004 )
1005 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
1006 AND link.business_group_id = assgn.business_group_id
1007 AND link.element_type_id = types.element_type_id
1008 AND types.element_type_id = p_element_name
1009 AND p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
1010 AND p_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
1011 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
1012
1013
1014 CURSOR c_input_rec(p_element_type_id NUMBER
1015 ,p_effective_date DATE
1016 )
1017 IS
1018 SELECT inputs.name name
1019 , inputs.input_value_id id
1020 , inputs.default_value value
1021 FROM pay_element_types_f types
1022 , pay_input_values_f inputs
1023 WHERE types.element_type_id = p_element_type_id
1024 AND inputs.element_type_id = types.element_type_id
1025 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
1026 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
1027 ORDER BY inputs.display_sequence;
1028
1029
1030
1031
1032 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
1033 IS
1034 SELECT object_version_number
1035 ,effective_start_date
1036 FROM pay_element_entries_f
1037 WHERE element_entry_id = p_element_entryid;
1038
1039 CURSOR c_get_screen_value (p_element_entryid NUMBER
1040 ,p_input NUMBER )
1041 IS
1042 SELECT screen_entry_value
1043 FROM pay_element_entry_values_f
1044 WHERE element_entry_id = p_element_entryid
1045 AND input_value_id = p_input;
1046
1047
1048 CURSOR c_check_element_entry(p_element_type_id NUMBER
1049 ,p_effective_date DATE )
1050 IS
1051 SELECT pee.element_entry_id
1052 FROM pay_element_entries_f pee
1053 WHERE pee.element_type_id = p_element_type_id
1054 AND pee.assignment_id = p_assignment_id
1055 AND TO_CHAR(p_effective_date,'RRRR') = TO_CHAR(pee.effective_start_date,'RRRR') ;
1056
1057 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
1058 IS
1059 SELECT element_type_id
1060 FROM pay_element_entries_f
1061 WHERE element_entry_id = p_element_entryid;
1062
1063 CURSOR c_get_prev_amts
1064 IS
1065 SELECT pae.aei_information9,
1066 pae.aei_information10
1067 FROM per_assignment_extra_info pae
1068 WHERE pae.assignment_extra_info_id = p_assignment_extra_info_id;
1069
1070 CURSOR c_element(p_element_type_id NUMBER)
1071 IS
1072 SELECT element_name
1073 FROM pay_element_types_f
1074 WHERE element_type_id = p_element_type_id ;
1075
1076 --Variables Initialization
1077 TYPE t_input_values_rec IS RECORD
1078 (input_name pay_input_values_f.name%TYPE
1079 ,input_value_id pay_input_values_f.input_value_id%TYPE
1080 ,value pay_input_values_f.default_value%TYPE
1081 );
1082
1083 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
1084
1085 l_procedure VARCHAR2(250);
1086 l_message VARCHAR2(250);
1087 l_warnings BOOLEAN;
1088 l_input_values_rec t_input_values_tab;
1089 l_ben_input_values_rec t_input_values_tab;
1090 l_business_group_id NUMBER;
1091 l_medical_bill_element hr_organization_information.org_information2%TYPE ;
1092 l_ltc_element hr_organization_information.org_information2%TYPE ;
1093 l_element pay_element_types.element_name%TYPE ;
1094 l_count NUMBER;
1095 l_assignment_id NUMBER;
1096 l_ltc_element_type_id NUMBER;
1097 l_bill_element_link_id NUMBER;
1098 l_ltc_element_link_id NUMBER;
1099 l_element_entry_id NUMBER := NULL ;
1100 l_start_date DATE ;
1101 l_end_date DATE ;
1102 l_effective_start_date DATE ;
1103 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
1104 l_entry_information5 pay_element_entries_f.entry_information2%TYPE ;
1105 l_entry_information3 pay_element_entries_f.entry_information2%TYPE ;
1106 l_submitted NUMBER ;
1107 l_prev_submitted NUMBER ;
1108 l_check NUMBER :=0;
1109 l_exempted_amount NUMBER ;
1110 l_prev_exempted_amount NUMBER ;
1111 l_session NUMBER ;
1112
1113
1114
1115
1116
1117 BEGIN
1118 g_debug := hr_utility.debug_enabled;
1119 l_procedure := g_package ||'create_ltc_element';
1120 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1121 IF (g_debug)
1122 THEN
1123 pay_in_utils.trace('**************************************************','********************');
1124 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1125 pay_in_utils.trace('P_LTCBLOCK',TO_CHAR (P_LTCBLOCK));
1126 pay_in_utils.trace('P_PLACE_FROM',TO_CHAR (P_PLACE_FROM));
1127 pay_in_utils.trace('P_PLACE_TO',TO_CHAR (P_PLACE_TO));
1128 pay_in_utils.trace('P_MODE_CLASS',TO_CHAR (P_MODE_CLASS));
1129 pay_in_utils.trace('P_CARRY_OVER',TO_CHAR (P_CARRY_OVER));
1130 pay_in_utils.trace('P_SUBMITTED',TO_CHAR (P_SUBMITTED));
1131 pay_in_utils.trace('P_EXEMPTED',TO_CHAR (P_EXEMPTED));
1132 pay_in_utils.trace('P_ELEMENT_ENTRY_ID',TO_CHAR (P_ELEMENT_ENTRY_ID));
1133 pay_in_utils.trace('P_START_DATE',TO_CHAR (P_START_DATE));
1134 pay_in_utils.trace('P_END_DATE',TO_CHAR (P_END_DATE));
1135 pay_in_utils.trace('P_BILL_NUM',TO_CHAR (P_BILL_NUM));
1136 pay_in_utils.trace('P_EE_COMMENTS',TO_CHAR (P_EE_COMMENTS));
1137 pay_in_utils.trace('P_ER_COMMENTS',TO_CHAR (P_ER_COMMENTS));
1138 pay_in_utils.trace('P_LAST_UPDATED_DATE',TO_CHAR (P_LAST_UPDATED_DATE));
1139 pay_in_utils.trace('P_ASSIGNMENT_ID',TO_CHAR (P_ASSIGNMENT_ID));
1140 pay_in_utils.trace('P_EMPLOYEE_ID',TO_CHAR (P_EMPLOYEE_ID));
1141 pay_in_utils.trace('P_ASSIGNMENT_EXTRA_INFO_ID ',TO_CHAR (P_ASSIGNMENT_EXTRA_INFO_ID));
1142 pay_in_utils.trace('P_ENTRY_DATE ',TO_CHAR (P_ENTRY_DATE));
1143
1144
1145 END IF;
1146
1147 IF P_SUBMITTED < 0 THEN
1148 hr_utility.set_message(800, 'PER_IN_BEN_AMOUNT');
1149 hr_utility.raise_error;
1150 END IF ;
1151
1152 IF P_EXEMPTED < 0 THEN
1153 hr_utility.set_message(800, 'PER_IN_BEN_APPROVED_AMOUNT');
1154 hr_utility.raise_error;
1155 END IF ;
1156
1157
1158 l_business_group_id := get_bg_id();
1159
1160 IF (g_debug)
1161 THEN
1162 pay_in_utils.trace('l_business_group_id',TO_CHAR (l_business_group_id));
1163 END IF;
1164
1165 BEGIN
1166 SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
1167 EXCEPTION
1168 WHEN NO_DATA_FOUND THEN
1169 INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),P_ENTRY_DATE);
1170 END ;
1171
1172
1173 IF ((p_element_entry_id IS NOT NULL))
1174 THEN
1175
1176 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
1177
1178 OPEN c_get_ele_object_version(p_element_entry_id) ;
1179 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
1180 CLOSE c_get_ele_object_version ;
1181
1182 OPEN c_get_ele_type_id(p_element_entry_id);
1183 FETCH c_get_ele_type_id INTO l_ltc_element_type_id ;
1184 CLOSE c_get_ele_type_id ;
1185
1186 l_count := 1;
1187 FOR c_rec IN c_input_rec(l_ltc_element_type_id,l_effective_start_date)
1188 LOOP
1189 l_input_values_rec(l_count).input_name := c_rec.name;
1190 l_input_values_rec(l_count).input_value_id := c_rec.id;
1191 l_input_values_rec(l_count).value := c_rec.value;
1192
1193 IF (g_debug)
1194 THEN
1195 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
1196 END IF;
1197
1198 l_count := l_count + 1;
1199 END LOOP;
1200
1201
1202 OPEN c_get_screen_value(p_element_entry_id, l_input_values_rec(3).input_value_id);
1203 FETCH c_get_screen_value INTO l_entry_information3 ;
1204 CLOSE c_get_screen_value ;
1205
1206 IF (g_debug)
1207 THEN
1208 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
1209 END IF;
1210
1211
1212 OPEN c_get_screen_value(p_element_entry_id, l_input_values_rec(5).input_value_id);
1213 FETCH c_get_screen_value INTO l_entry_information5 ;
1214 CLOSE c_get_screen_value ;
1215
1216 OPEN c_get_prev_amts ;
1217 FETCH c_get_prev_amts INTO l_prev_submitted, l_prev_exempted_amount;
1218 CLOSE c_get_prev_amts ;
1219
1220 IF (g_debug)
1221 THEN
1222 pay_in_utils.trace('l_prev_submitted',TO_CHAR (l_prev_submitted));
1223 pay_in_utils.trace('l_prev_exempted_amount',TO_CHAR (l_prev_exempted_amount));
1224 END IF;
1225
1226
1227 l_submitted := NVL(P_EXEMPTED,0) + NVL(l_entry_information3,0) - NVL(l_prev_exempted_amount,0) ;
1228 l_submitted := GREATEST (l_submitted,0);
1229 l_exempted_amount := NVL(P_EXEMPTED,0) + NVL(l_entry_information5,0) - NVL(l_prev_exempted_amount,0) ;
1230 l_exempted_amount := GREATEST (l_exempted_amount,0);
1231
1232 pay_element_entry_api.update_element_entry
1233 (p_datetrack_update_mode => hr_api.g_correction
1234 ,p_effective_date => l_effective_start_date
1235 ,p_business_group_id => l_business_group_id
1236 ,p_element_entry_id => p_element_entry_id
1237 ,p_object_version_number => l_object_version_no
1238 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1239 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1240 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1241 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1242 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1243 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1244 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1245 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1246 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1247 ,p_entry_value1 => l_input_values_rec(1).value
1248 ,p_entry_value2 => l_input_values_rec(2).value
1249 ,p_entry_value3 => l_submitted
1250 ,p_entry_value4 => P_LTCBLOCK
1251 ,p_entry_value5 => l_exempted_amount
1252 ,p_entry_value6 => P_CARRY_OVER
1253 ,p_entry_value7 => l_input_values_rec(7).value
1254 ,p_entry_value8 => l_input_values_rec(8).value
1255 ,p_entry_value9 => l_input_values_rec(9).value
1256 ,p_effective_start_date => l_start_date
1257 ,p_effective_end_date => l_end_date
1258 ,p_update_warning => l_warnings
1259 );
1260
1261
1262
1263 l_object_version_no := NULL ;
1264
1265
1266 ELSIF ((p_element_entry_id IS NULL) )
1267 THEN
1268
1269
1270 OPEN c_element_name(l_business_group_id);
1271 FETCH c_element_name INTO l_ltc_element ;
1272 CLOSE c_element_name ;
1273
1274 IF (g_debug)
1275 THEN
1276 pay_in_utils.trace('l_ltc_element ',TO_CHAR (l_ltc_element));
1277 END IF;
1278
1279
1280 OPEN csr_element_details(p_assignment_id, p_entry_date, l_ltc_element) ;
1281 FETCH csr_element_details INTO l_ltc_element_type_id, l_ltc_element_link_id ;
1282 CLOSE csr_element_details ;
1283
1284
1285 IF (g_debug)
1286 THEN
1287 pay_in_utils.trace('l_ltc_element_type_id ',TO_CHAR (l_ltc_element_type_id));
1288 pay_in_utils.trace('l_ltc_element_link_id ',TO_CHAR (l_ltc_element_link_id));
1289 END IF;
1290
1291 OPEN c_check_element_entry(l_ltc_element_type_id, p_entry_date);
1292 FETCH c_check_element_entry INTO l_element_entry_id ;
1293 CLOSE c_check_element_entry ;
1294
1295 IF l_element_entry_id IS NOT NULL THEN
1296
1297 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,40);
1298
1299 OPEN c_get_ele_object_version(l_element_entry_id) ;
1300 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
1301 CLOSE c_get_ele_object_version ;
1302
1303
1304
1305
1306 l_count := 1;
1307 FOR c_rec IN c_input_rec(l_ltc_element_type_id,l_effective_start_date)
1308 LOOP
1309 l_input_values_rec(l_count).input_name := c_rec.name;
1310 l_input_values_rec(l_count).input_value_id := c_rec.id;
1311 l_input_values_rec(l_count).value := c_rec.value;
1312
1313 IF (g_debug)
1314 THEN
1315 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
1316 END IF;
1317
1318 l_count := l_count + 1;
1319 END LOOP;
1320
1321 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(3).input_value_id);
1322 FETCH c_get_screen_value INTO l_entry_information3 ;
1323 CLOSE c_get_screen_value ;
1324
1325 IF (g_debug)
1326 THEN
1327 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
1328 END IF;
1329
1330
1331 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(5).input_value_id);
1332 FETCH c_get_screen_value INTO l_entry_information5 ;
1333 CLOSE c_get_screen_value ;
1334
1335 IF (g_debug)
1336 THEN
1337 pay_in_utils.trace('l_entry_information5',TO_CHAR (l_entry_information5));
1338 END IF;
1339
1340
1341
1342 l_submitted := NVL(P_EXEMPTED,0) + NVL(l_entry_information3,0) ;
1343 l_submitted := GREATEST (l_submitted,0);
1344 l_exempted_amount := NVL(P_EXEMPTED,0) + NVL(l_entry_information5,0) ;
1345 l_exempted_amount := GREATEST (l_exempted_amount,0);
1346
1347 pay_element_entry_api.update_element_entry
1348 (p_datetrack_update_mode => hr_api.g_correction
1349 ,p_effective_date => l_effective_start_date
1350 ,p_business_group_id => l_business_group_id
1351 ,p_element_entry_id => l_element_entry_id
1352 ,p_object_version_number => l_object_version_no
1353 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1354 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1355 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1356 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1357 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1358 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1359 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1360 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1361 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1362 ,p_entry_value1 => l_input_values_rec(1).value
1363 ,p_entry_value2 => l_input_values_rec(2).value
1364 ,p_entry_value3 => l_submitted
1365 ,p_entry_value4 => P_LTCBLOCK
1366 ,p_entry_value5 => l_exempted_amount
1367 ,p_entry_value6 => P_CARRY_OVER
1368 ,p_entry_value7 => l_input_values_rec(7).value
1369 ,p_entry_value8 => l_input_values_rec(8).value
1370 ,p_entry_value9 => l_input_values_rec(9).value
1371 ,p_effective_start_date => l_start_date
1372 ,p_effective_end_date => l_end_date
1373 ,p_update_warning => l_warnings
1374 );
1375
1376
1377
1378 l_object_version_no := NULL ;
1379
1380
1381
1382 ELSE
1383
1384
1385
1386 IF l_ltc_element_link_id IS NULL THEN
1387
1388 OPEN c_element(TO_NUMBER(l_ltc_element));
1389 FETCH c_element INTO l_element;
1390 CLOSE c_element;
1391
1392 hr_utility.set_message(800, 'PER_IN_MISSING_LINK');
1393 hr_utility.set_message_token('ELEMENT_NAME', l_element);
1394 hr_utility.raise_error;
1395 END IF;
1396
1397 --Populate the input value id, name records
1398
1399 l_count := 1;
1400 FOR c_rec IN c_input_rec(l_ltc_element_type_id,p_entry_date)
1401 LOOP
1402 l_ben_input_values_rec(l_count).input_name := c_rec.name;
1403 l_ben_input_values_rec(l_count).input_value_id := c_rec.id;
1404 l_ben_input_values_rec(l_count).value := c_rec.value;
1405
1406 IF (g_debug)
1407 THEN
1408 pay_in_utils.trace('Input Value Name:'||l_count,TO_CHAR (c_rec.name));
1409 END IF;
1410
1411 l_count := l_count + 1;
1412 END LOOP;
1413
1414 pay_in_utils.set_location(g_debug,'Creating Benefit Element Entries: '||l_procedure,50);
1415
1416
1417
1418
1419
1420
1421 IF (P_EXEMPTED IS NOT NULL) THEN
1422 pay_element_entry_api.create_element_entry
1423 (p_effective_date => p_entry_date
1424 ,p_business_group_id => l_business_group_id
1425 ,p_assignment_id => p_assignment_id
1426 ,p_element_link_id => l_ltc_element_link_id
1427 ,p_entry_type => 'E'
1428 ,p_input_value_id1 => l_ben_input_values_rec(1).input_value_id
1429 ,p_input_value_id2 => l_ben_input_values_rec(2).input_value_id
1430 ,p_input_value_id3 => l_ben_input_values_rec(3).input_value_id
1431 ,p_input_value_id4 => l_ben_input_values_rec(4).input_value_id
1432 ,p_input_value_id5 => l_ben_input_values_rec(5).input_value_id
1433 ,p_input_value_id6 => l_ben_input_values_rec(6).input_value_id
1434 ,p_input_value_id7 => l_ben_input_values_rec(7).input_value_id
1435 ,p_input_value_id8 => l_ben_input_values_rec(8).input_value_id
1436 ,p_input_value_id9 => l_ben_input_values_rec(9).input_value_id
1437 ,p_entry_value1 => l_ben_input_values_rec(1).value
1438 ,p_entry_value2 => l_ben_input_values_rec(2).value
1439 ,p_entry_value3 => P_EXEMPTED
1440 ,p_entry_value4 => P_LTCBLOCK
1441 ,p_entry_value5 => P_EXEMPTED
1442 ,p_entry_value6 => P_CARRY_OVER
1443 ,p_entry_value7 => l_ben_input_values_rec(7).value
1444 ,p_entry_value8 => l_ben_input_values_rec(8).value
1445 ,p_entry_value9 => l_ben_input_values_rec(9).value
1446 ,p_effective_start_date => l_start_date
1447 ,p_effective_end_date => l_end_date
1448 ,p_element_entry_id => l_element_entry_id
1449 ,p_object_version_number => l_object_version_no
1450 ,p_create_warning => l_warnings
1451 );
1452
1453 END IF;
1454 pay_in_utils.set_location(g_debug,'Benefit Element Creation Completed'||l_procedure,60);
1455
1456
1457
1458 END IF ;
1459 END IF;
1460
1461
1462 pay_in_utils.set_location(g_debug,'Updating Assignment_Extra_Info: '||l_procedure,70);
1463
1464 IF P_EMPLOYEE_ID <> 0 THEN /* To prevent this from Self Service page and execute only for web adi*/
1465
1466 UPDATE per_assignment_extra_info
1467 SET AEI_INFORMATION10 = P_EXEMPTED
1468 ,AEI_INFORMATION6 = P_ER_COMMENTS
1469 ,AEI_INFORMATION11 = nvl(l_element_entry_id,p_element_entry_id)
1470 ,aei_information18 = P_CARRY_OVER
1471 WHERE ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;
1472 END IF;
1473
1474 pay_in_utils.trace('**************************************************','********************');
1475 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
1476 END create_ltc_element;
1477
1478
1479 --------------------------------------------------------------------------
1480 -- --
1481 -- Name : UPDATE_LTC_ELEMENT --
1482 -- Type : PROCEDURE --
1483 -- Access : Public --
1484 -- Description : Function to update the LTC element enrty as per the --
1485 -- details passed from the Web ADI Excel Sheet. --
1486 -- --
1487 ---------------------------------------------------------------------------
1488
1489 PROCEDURE update_ltc_element
1490 (
1491 p_employee_number IN VARCHAR2
1492 ,p_full_name IN VARCHAR2
1493 ,p_start_date IN DATE
1494 ,p_effective_end_date IN DATE DEFAULT NULL
1495 ,p_fare IN NUMBER
1496 ,p_blockYr IN VARCHAR2
1497 ,p_carry IN VARCHAR2
1498 ,p_benefit IN NUMBER
1499 ,p_assignment_id IN NUMBER
1500 ,p_element_entry_id IN NUMBER DEFAULT NULL
1501 )
1502 IS
1503
1504
1505
1506
1507 CURSOR c_input_rec(p_element_type_id NUMBER
1508 ,p_effective_date DATE
1509 )
1510 IS
1511 SELECT inputs.name name
1512 , inputs.input_value_id id
1513 , inputs.default_value value
1514 FROM pay_element_types_f types
1515 , pay_input_values_f inputs
1516 WHERE types.element_type_id = p_element_type_id
1517 AND inputs.element_type_id = types.element_type_id
1518 --AND inputs.legislation_code = 'IN'
1519 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
1520 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
1521 ORDER BY inputs.display_sequence;
1522
1523
1524
1525
1526 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
1527 IS
1528 SELECT object_version_number
1529 FROM pay_element_entries_f
1530 WHERE element_entry_id = p_element_entryid;
1531
1532
1533
1534 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
1535 IS
1536 SELECT element_type_id
1537 FROM pay_element_entries_f
1538 WHERE element_entry_id = p_element_entryid;
1539
1540
1541 --Variables Initialization
1542 TYPE t_input_values_rec IS RECORD
1543 (input_name pay_input_values_f.name%TYPE
1544 ,input_value_id pay_input_values_f.input_value_id%TYPE
1545 ,value pay_input_values_f.default_value%TYPE
1546 );
1547
1548 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
1549
1550 l_procedure VARCHAR2(250);
1551 l_warnings BOOLEAN;
1552 l_input_values_rec t_input_values_tab;
1553 l_business_group_id NUMBER;
1554 l_count NUMBER;
1555 l_element_type_id NUMBER;
1556 l_start_date DATE ;
1557 l_end_date DATE ;
1558 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
1559 l_session NUMBER ;
1560
1561
1562
1563 BEGIN
1564
1565
1566 g_debug := hr_utility.debug_enabled;
1567 l_procedure := g_package ||'update_ltc_element';
1568 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1569 IF (g_debug)
1570 THEN
1571 pay_in_utils.trace('**************************************************','********************');
1572 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1573 pay_in_utils.trace('p_employee_number',TO_CHAR (p_employee_number));
1574 pay_in_utils.trace('p_full_name',TO_CHAR (p_full_name));
1575 pay_in_utils.trace('p_start_date',TO_CHAR (p_start_date));
1576 pay_in_utils.trace('p_effective_end_date',TO_CHAR (p_effective_end_date));
1577 pay_in_utils.trace('p_fare',TO_CHAR (p_fare));
1578 pay_in_utils.trace('p_blockYr',TO_CHAR (p_blockYr));
1579 pay_in_utils.trace('p_carry',TO_CHAR (p_carry));
1580 pay_in_utils.trace('p_benefit',TO_CHAR (p_benefit));
1581 pay_in_utils.trace('p_assignment_id',TO_CHAR (p_assignment_id));
1582 pay_in_utils.trace(' p_element_entry_id',TO_CHAR (p_element_entry_id));
1583
1584 END IF;
1585
1586 IF p_fare < 0 THEN
1587 hr_utility.set_message(800, 'PER_IN_BEN_AMOUNT');
1588 hr_utility.raise_error;
1589 END IF ;
1590
1591
1592 IF p_benefit < 0 THEN
1593 hr_utility.set_message(800, 'PER_IN_BEN_APPROVED_AMOUNT');
1594 hr_utility.raise_error;
1595 END IF ;
1596
1597
1598 BEGIN
1599 SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
1600 EXCEPTION
1601 WHEN NO_DATA_FOUND THEN
1602 INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),p_start_date);
1603 END ;
1604
1605 l_business_group_id := get_bg_id();
1606
1607 IF (g_debug)
1608 THEN
1609 pay_in_utils.trace('Business Group:',TO_CHAR (l_business_group_id));
1610 END IF;
1611
1612
1613 IF ((p_element_entry_id IS NOT NULL))
1614 THEN
1615
1616 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
1617
1618 OPEN c_get_ele_object_version(p_element_entry_id) ;
1619 FETCH c_get_ele_object_version INTO l_object_version_no ;
1620 CLOSE c_get_ele_object_version ;
1621
1622 OPEN c_get_ele_type_id(p_element_entry_id);
1623 FETCH c_get_ele_type_id INTO l_element_type_id ;
1624 CLOSE c_get_ele_type_id ;
1625
1626 l_count := 1;
1627 FOR c_rec IN c_input_rec(l_element_type_id,p_start_date)
1628 LOOP
1629 l_input_values_rec(l_count).input_name := c_rec.name;
1630 l_input_values_rec(l_count).input_value_id := c_rec.id;
1631 l_input_values_rec(l_count).value := c_rec.value;
1632
1633 IF (g_debug)
1634 THEN
1635 pay_in_utils.trace('Input Values Name:'||l_count,TO_CHAR (c_rec.name));
1636 END IF;
1637
1638 l_count := l_count + 1;
1639 END LOOP;
1640
1641
1642
1643 pay_element_entry_api.update_element_entry
1644 (p_datetrack_update_mode => hr_api.g_correction
1645 ,p_effective_date => p_start_date
1646 ,p_business_group_id => l_business_group_id
1647 ,p_element_entry_id => p_element_entry_id
1648 ,p_object_version_number => l_object_version_no
1649 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1650 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1651 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1652 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1653 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1654 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1655 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1656 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1657 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1658 ,p_entry_value1 => l_input_values_rec(1).value
1659 ,p_entry_value2 => l_input_values_rec(2).value
1660 ,p_entry_value3 => p_fare
1661 ,p_entry_value4 => p_blockYr
1662 ,p_entry_value5 => p_benefit
1663 ,p_entry_value6 => p_carry
1664 ,p_entry_value7 => l_input_values_rec(7).value
1665 ,p_entry_value8 => l_input_values_rec(8).value
1666 ,p_entry_value9 => l_input_values_rec(9).value
1667 ,p_effective_start_date => l_start_date
1668 ,p_effective_end_date => l_end_date
1669 ,p_update_warning => l_warnings
1670 );
1671
1672
1673 END IF ;
1674
1675 UPDATE per_assignment_extra_info
1676 SET AEI_INFORMATION18 = p_carry
1677 where AEI_INFORMATION11 = p_element_entry_id
1678 and assignment_id = p_assignment_id;
1679
1680 pay_in_utils.trace('**************************************************','********************');
1681 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1682
1683
1684
1685
1686 END update_ltc_element;
1687
1688 END pay_in_med_web_adi;