[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_MED_WEB_ADI
Source
4 g_debug BOOLEAN ;
1 PACKAGE BODY pay_in_med_web_adi AS
2 /* $Header: pyinmadi.pkb 120.10.12020000.3 2012/07/04 23:05:23 amnaraya ship $ */
3 g_package CONSTANT VARCHAR2(100) := 'pay_in_med_web_adi.';
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
110 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
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
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
147 CURSOR c_check_element_entry(p_element_type_id NUMBER
144 AND input_value_id = p_input;
145
146
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 ;
213 l_approved_bill_amount NUMBER ;
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;
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;
299 THEN
296 l_input_values_rec(l_count).value := c_rec.value;
297
298 IF (g_debug)
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
319
316 pay_in_utils.trace('l_entry_information1',TO_CHAR (l_entry_information1));
317 END IF;
318
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
431
428 ,p_effective_end_date => l_end_date
429 ,p_update_warning => l_warnings
430 );
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
565
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 ;
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
592 ,p_input_value_id5 => l_ben_input_values_rec(5).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
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;
832 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
829 l_element_type_id NUMBER;
830 l_start_date DATE ;
831 l_end_date DATE ;
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 ;
884 FETCH c_get_ele_type_id INTO l_element_type_id ;
881 CLOSE c_get_ele_object_version ;
882
883 OPEN c_get_ele_type_id(p_element_entry_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
913 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
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
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 ,P_BENEFIT IN VARCHAR2
979 )
980 IS
981 CURSOR c_element_name(p_business_group_id NUMBER)
982 IS
983 SELECT hoi.org_information3
984 FROM hr_organization_information hoi
985 WHERE hoi.organization_id = p_business_group_id
986 AND org_information_context='PER_IN_REIMBURSE_ELEMENTS';
987
988
989
990 --Get Element Details (type id and link id)
991 CURSOR csr_element_details(p_assignment_id NUMBER
992 ,p_effective_date DATE
993 ,p_element_name VARCHAR2
994 )
995 IS
996 SELECT types.element_type_id
997 ,link.element_link_id
998 FROM per_assignments_f assgn
999 , pay_element_links_f link
1000 , pay_element_types_f types
1001 WHERE assgn.assignment_id = p_assignment_id
1002 AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
1003 ,P_ENTRY_DATE
1004 ,types.element_type_id
1005 )
1006 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
1007 AND link.business_group_id = assgn.business_group_id
1008 AND link.element_type_id = types.element_type_id
1009 AND types.element_type_id = p_element_name
1010 AND p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
1011 AND p_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
1012 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
1013
1014
1015 CURSOR c_input_rec(p_element_type_id NUMBER
1016 ,p_effective_date DATE
1017 )
1018 IS
1019 SELECT inputs.name name
1020 , inputs.input_value_id id
1024 WHERE types.element_type_id = p_element_type_id
1021 , inputs.default_value value
1022 FROM pay_element_types_f types
1023 , pay_input_values_f inputs
1025 AND inputs.element_type_id = types.element_type_id
1026 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
1027 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
1028 ORDER BY inputs.display_sequence;
1029
1030
1031
1032
1033 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
1034 IS
1035 SELECT object_version_number
1036 ,effective_start_date
1037 FROM pay_element_entries_f
1038 WHERE element_entry_id = p_element_entryid;
1039
1040 CURSOR c_get_screen_value (p_element_entryid NUMBER
1041 ,p_input NUMBER )
1042 IS
1043 SELECT screen_entry_value
1044 FROM pay_element_entry_values_f
1045 WHERE element_entry_id = p_element_entryid
1046 AND input_value_id = p_input;
1047
1048
1049 CURSOR c_check_element_entry(p_element_type_id NUMBER
1050 ,p_effective_date DATE )
1051 IS
1052 SELECT pee.element_entry_id
1053 FROM pay_element_entries_f pee
1054 WHERE pee.element_type_id = p_element_type_id
1055 AND pee.assignment_id = p_assignment_id
1056 AND TO_CHAR(p_effective_date,'RRRRMM') = TO_CHAR(pee.effective_start_date,'RRRRMM') ; /*Added MM check for Bug 12819651 */
1057
1058 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
1059 IS
1060 SELECT element_type_id
1061 FROM pay_element_entries_f
1062 WHERE element_entry_id = p_element_entryid;
1063
1064 CURSOR c_get_prev_amts
1065 IS
1066 SELECT pae.aei_information9,
1067 pae.aei_information10
1068 FROM per_assignment_extra_info pae
1069 WHERE pae.assignment_extra_info_id = p_assignment_extra_info_id;
1070
1071 CURSOR c_element(p_element_type_id NUMBER)
1072 IS
1073 SELECT element_name
1074 FROM pay_element_types_f
1075 WHERE element_type_id = p_element_type_id ;
1076
1077 --Variables Initialization
1078 TYPE t_input_values_rec IS RECORD
1079 (input_name pay_input_values_f.name%TYPE
1080 ,input_value_id pay_input_values_f.input_value_id%TYPE
1081 ,value pay_input_values_f.default_value%TYPE
1082 );
1083
1084 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
1085
1086 l_procedure VARCHAR2(250);
1087 l_message VARCHAR2(250);
1088 l_warnings BOOLEAN;
1089 l_input_values_rec t_input_values_tab;
1090 l_ben_input_values_rec t_input_values_tab;
1091 l_business_group_id NUMBER;
1092 l_medical_bill_element hr_organization_information.org_information2%TYPE ;
1093 l_ltc_element hr_organization_information.org_information2%TYPE ;
1094 l_element pay_element_types.element_name%TYPE ;
1095 l_count NUMBER;
1096 l_assignment_id NUMBER;
1097 l_ltc_element_type_id NUMBER;
1098 l_bill_element_link_id NUMBER;
1099 l_ltc_element_link_id NUMBER;
1100 l_element_entry_id NUMBER := NULL ;
1101 l_start_date DATE ;
1102 l_end_date DATE ;
1103 l_effective_start_date DATE ;
1104 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
1105 l_entry_information5 pay_element_entries_f.entry_information2%TYPE ;
1106 l_entry_information3 pay_element_entries_f.entry_information2%TYPE ;
1107 l_submitted NUMBER ;
1108 l_prev_submitted NUMBER ;
1109 l_check NUMBER :=0;
1110 l_exempted_amount NUMBER ;
1111 l_prev_exempted_amount NUMBER ;
1112 l_session NUMBER ;
1113
1114
1115
1116
1117
1118 BEGIN
1119 --hr_utility.trace_on (null, 'ORACLE1234');
1120 g_debug := hr_utility.debug_enabled;
1121 l_procedure := g_package ||'create_ltc_element';
1122 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1123 IF (g_debug)
1124 THEN
1125 pay_in_utils.trace('**************************************************','********************');
1126 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1127 pay_in_utils.trace('P_LTCBLOCK',TO_CHAR (P_LTCBLOCK));
1128 pay_in_utils.trace('P_PLACE_FROM',TO_CHAR (P_PLACE_FROM));
1129 pay_in_utils.trace('P_PLACE_TO',TO_CHAR (P_PLACE_TO));
1130 pay_in_utils.trace('P_MODE_CLASS',TO_CHAR (P_MODE_CLASS));
1131 pay_in_utils.trace('P_CARRY_OVER',TO_CHAR (P_CARRY_OVER));
1132 pay_in_utils.trace('P_SUBMITTED',TO_CHAR (P_SUBMITTED));
1133 pay_in_utils.trace('P_EXEMPTED',TO_CHAR (P_EXEMPTED));
1134 pay_in_utils.trace('P_ELEMENT_ENTRY_ID',TO_CHAR (P_ELEMENT_ENTRY_ID));
1135 pay_in_utils.trace('P_START_DATE',TO_CHAR (P_START_DATE));
1136 pay_in_utils.trace('P_END_DATE',TO_CHAR (P_END_DATE));
1137 pay_in_utils.trace('P_BILL_NUM',TO_CHAR (P_BILL_NUM));
1138 pay_in_utils.trace('P_EE_COMMENTS',TO_CHAR (P_EE_COMMENTS));
1139 pay_in_utils.trace('P_ER_COMMENTS',TO_CHAR (P_ER_COMMENTS));
1140 pay_in_utils.trace('P_LAST_UPDATED_DATE',TO_CHAR (P_LAST_UPDATED_DATE));
1144 pay_in_utils.trace('P_ASSIGNMENT_EXTRA_INFO_ID ',TO_CHAR (P_ASSIGNMENT_EXTRA_INFO_ID));
1141 pay_in_utils.trace('P_ASSIGNMENT_ID',TO_CHAR (P_ASSIGNMENT_ID));
1142 pay_in_utils.trace('P_EMPLOYEE_ID',TO_CHAR (P_EMPLOYEE_ID));
1143 pay_in_utils.trace('P_BENEFIT',TO_CHAR (P_BENEFIT));
1145 pay_in_utils.trace('P_ENTRY_DATE ',TO_CHAR (P_ENTRY_DATE));
1146
1147
1148 END IF;
1149
1150 IF P_SUBMITTED < 0 THEN
1151 hr_utility.set_message(800, 'PER_IN_BEN_AMOUNT');
1152 hr_utility.raise_error;
1153 END IF ;
1154
1155 IF P_EXEMPTED < 0 THEN
1156 hr_utility.set_message(800, 'PER_IN_BEN_APPROVED_AMOUNT');
1157 hr_utility.raise_error;
1158 END IF ;
1159
1160
1161 l_business_group_id := get_bg_id();
1162
1163 IF (g_debug)
1164 THEN
1165 pay_in_utils.trace('l_business_group_id',TO_CHAR (l_business_group_id));
1166 END IF;
1167
1168 BEGIN
1169 SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
1170 EXCEPTION
1171 WHEN NO_DATA_FOUND THEN
1172 INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),P_ENTRY_DATE);
1173 END ;
1174
1175
1176 IF ((p_element_entry_id IS NOT NULL))
1177 THEN
1178
1179 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
1180
1181 OPEN c_get_ele_object_version(p_element_entry_id) ;
1182 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
1183 CLOSE c_get_ele_object_version ;
1184
1185 OPEN c_get_ele_type_id(p_element_entry_id);
1186 FETCH c_get_ele_type_id INTO l_ltc_element_type_id ;
1187 CLOSE c_get_ele_type_id ;
1188
1189 l_count := 1;
1190 FOR c_rec IN c_input_rec(l_ltc_element_type_id,l_effective_start_date)
1191 LOOP
1192 l_input_values_rec(l_count).input_name := c_rec.name;
1193 l_input_values_rec(l_count).input_value_id := c_rec.id;
1194 l_input_values_rec(l_count).value := c_rec.value;
1195
1196 IF (g_debug)
1197 THEN
1198 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
1199 END IF;
1200
1201 l_count := l_count + 1;
1202 END LOOP;
1203
1204
1205 OPEN c_get_screen_value(p_element_entry_id, l_input_values_rec(3).input_value_id);
1206 FETCH c_get_screen_value INTO l_entry_information3 ;
1207 CLOSE c_get_screen_value ;
1208
1209 IF (g_debug)
1210 THEN
1211 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
1212 END IF;
1213
1214
1215 OPEN c_get_screen_value(p_element_entry_id, l_input_values_rec(5).input_value_id);
1216 FETCH c_get_screen_value INTO l_entry_information5 ;
1217 CLOSE c_get_screen_value ;
1218
1219 OPEN c_get_prev_amts ;
1220 FETCH c_get_prev_amts INTO l_prev_submitted, l_prev_exempted_amount;
1221 CLOSE c_get_prev_amts ;
1222
1223 IF (g_debug)
1224 THEN
1225 pay_in_utils.trace('l_prev_submitted',TO_CHAR (l_prev_submitted));
1226 pay_in_utils.trace('l_prev_exempted_amount',TO_CHAR (l_prev_exempted_amount));
1227 END IF;
1228
1229
1230 l_submitted := NVL(P_EXEMPTED,0) + NVL(l_entry_information3,0) - NVL(l_prev_exempted_amount,0) ;
1231 l_submitted := GREATEST (l_submitted,0);
1232 l_exempted_amount := NVL(P_EXEMPTED,0) + NVL(l_entry_information5,0) - NVL(l_prev_exempted_amount,0) ;
1233 l_exempted_amount := GREATEST (l_exempted_amount,0);
1234
1235 pay_element_entry_api.update_element_entry
1236 (p_datetrack_update_mode => hr_api.g_correction
1237 ,p_effective_date => l_effective_start_date
1238 ,p_business_group_id => l_business_group_id
1239 ,p_element_entry_id => p_element_entry_id
1240 ,p_object_version_number => l_object_version_no
1241 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1242 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1243 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1244 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1245 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1246 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1247 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1248 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1249 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1250 ,p_entry_value1 => l_input_values_rec(1).value
1251 ,p_entry_value2 => l_input_values_rec(2).value
1252 ,p_entry_value3 => l_submitted
1253 ,p_entry_value4 => P_LTCBLOCK
1254 ,p_entry_value5 => P_BENEFIT
1255 ,p_entry_value6 => P_CARRY_OVER
1256 ,p_entry_value7 => l_input_values_rec(7).value
1257 ,p_entry_value8 => l_input_values_rec(8).value
1258 ,p_entry_value9 => l_input_values_rec(9).value
1259 ,p_effective_start_date => l_start_date
1260 ,p_effective_end_date => l_end_date
1261 ,p_update_warning => l_warnings
1262 );
1263
1264 UPDATE per_assignment_extra_info
1265 SET
1266 AEI_INFORMATION21 = P_BENEFIT
1267 WHERE AEI_INFORMATION11 = p_element_entry_id
1268 AND assignment_id = p_assignment_id;
1269 l_object_version_no := NULL ;
1270 ELSIF ((p_element_entry_id IS NULL) )
1271 THEN
1272
1273
1274 OPEN c_element_name(l_business_group_id);
1275 FETCH c_element_name INTO l_ltc_element ;
1276 CLOSE c_element_name ;
1277
1278 IF (g_debug)
1279 THEN
1280 pay_in_utils.trace('l_ltc_element ',TO_CHAR (l_ltc_element));
1281 END IF;
1282
1283
1284 OPEN csr_element_details(p_assignment_id, p_entry_date, l_ltc_element) ;
1285 FETCH csr_element_details INTO l_ltc_element_type_id, l_ltc_element_link_id ;
1286 CLOSE csr_element_details ;
1287
1288
1289 IF (g_debug)
1290 THEN
1291 pay_in_utils.trace('l_ltc_element_type_id ',TO_CHAR (l_ltc_element_type_id));
1292 pay_in_utils.trace('l_ltc_element_link_id ',TO_CHAR (l_ltc_element_link_id));
1293 END IF;
1294
1295 OPEN c_check_element_entry(l_ltc_element_type_id, p_entry_date);
1296 FETCH c_check_element_entry INTO l_element_entry_id ;
1297 CLOSE c_check_element_entry ;
1298
1299 IF l_element_entry_id IS NOT NULL THEN
1300
1301 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,40);
1302
1303 OPEN c_get_ele_object_version(l_element_entry_id) ;
1304 FETCH c_get_ele_object_version INTO l_object_version_no,l_effective_start_date ;
1305 CLOSE c_get_ele_object_version ;
1306
1307
1308
1309
1310 l_count := 1;
1311 FOR c_rec IN c_input_rec(l_ltc_element_type_id,l_effective_start_date)
1312 LOOP
1313 l_input_values_rec(l_count).input_name := c_rec.name;
1314 l_input_values_rec(l_count).input_value_id := c_rec.id;
1315 l_input_values_rec(l_count).value := c_rec.value;
1316
1317 IF (g_debug)
1318 THEN
1319 pay_in_utils.trace('Input Value Name'||l_count,TO_CHAR (c_rec.name));
1320 END IF;
1321
1322 l_count := l_count + 1;
1323 END LOOP;
1324
1325 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(3).input_value_id);
1326 FETCH c_get_screen_value INTO l_entry_information3 ;
1327 CLOSE c_get_screen_value ;
1328
1329 IF (g_debug)
1330 THEN
1331 pay_in_utils.trace('l_entry_information3',TO_CHAR (l_entry_information3));
1332 END IF;
1333
1334
1335 OPEN c_get_screen_value(l_element_entry_id, l_input_values_rec(5).input_value_id);
1336 FETCH c_get_screen_value INTO l_entry_information5 ;
1337 CLOSE c_get_screen_value ;
1338
1339 IF (g_debug)
1340 THEN
1341 pay_in_utils.trace('l_entry_information5',TO_CHAR (l_entry_information5));
1342 END IF;
1343
1344
1345
1346 l_submitted := NVL(P_EXEMPTED,0) + NVL(l_entry_information3,0) ;
1347 l_submitted := GREATEST (l_submitted,0);
1348 l_exempted_amount := NVL(P_EXEMPTED,0) + NVL(l_entry_information5,0) ;
1349 l_exempted_amount := GREATEST (l_exempted_amount,0);
1350
1351 pay_element_entry_api.update_element_entry
1352 (p_datetrack_update_mode => hr_api.g_correction
1353 ,p_effective_date => l_effective_start_date
1354 ,p_business_group_id => l_business_group_id
1355 ,p_element_entry_id => l_element_entry_id
1356 ,p_object_version_number => l_object_version_no
1357 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1358 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1359 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1360 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1361 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1362 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1363 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1364 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1365 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1366 ,p_entry_value1 => l_input_values_rec(1).value
1367 ,p_entry_value2 => l_input_values_rec(2).value
1368 ,p_entry_value3 => l_submitted
1369 ,p_entry_value4 => P_LTCBLOCK
1370 ,p_entry_value5 => P_BENEFIT
1371 ,p_entry_value6 => P_CARRY_OVER
1372 ,p_entry_value7 => l_input_values_rec(7).value
1373 ,p_entry_value8 => l_input_values_rec(8).value
1374 ,p_entry_value9 => l_input_values_rec(9).value
1375 ,p_effective_start_date => l_start_date
1376 ,p_effective_end_date => l_end_date
1377 ,p_update_warning => l_warnings
1378 );
1379
1380 UPDATE per_assignment_extra_info
1381 SET
1382 AEI_INFORMATION21 = P_BENEFIT
1383 where AEI_INFORMATION11 = l_element_entry_id
1384 and assignment_id = p_assignment_id;
1385
1386 l_object_version_no := NULL ;
1387
1388
1389
1390 ELSE
1391
1392
1393
1394 IF l_ltc_element_link_id IS NULL THEN
1395
1396 OPEN c_element(TO_NUMBER(l_ltc_element));
1397 FETCH c_element INTO l_element;
1398 CLOSE c_element;
1399
1400 hr_utility.set_message(800, 'PER_IN_MISSING_LINK');
1401 hr_utility.set_message_token('ELEMENT_NAME', l_element);
1402 hr_utility.raise_error;
1403 END IF;
1404
1405 --Populate the input value id, name records
1406
1407 l_count := 1;
1408 FOR c_rec IN c_input_rec(l_ltc_element_type_id,p_entry_date)
1409 LOOP
1410 l_ben_input_values_rec(l_count).input_name := c_rec.name;
1411 l_ben_input_values_rec(l_count).input_value_id := c_rec.id;
1412 l_ben_input_values_rec(l_count).value := c_rec.value;
1413
1414 IF (g_debug)
1415 THEN
1416 pay_in_utils.trace('Input Value Name:'||l_count,TO_CHAR (c_rec.name));
1417 END IF;
1418
1419 l_count := l_count + 1;
1420 END LOOP;
1421
1422 pay_in_utils.set_location(g_debug,'Creating Benefit Element Entries: '||l_procedure,50);
1423
1424
1425
1426
1427
1428
1429 IF (P_EXEMPTED IS NOT NULL) THEN
1430 pay_element_entry_api.create_element_entry
1431 (p_effective_date => p_entry_date
1432 ,p_business_group_id => l_business_group_id
1433 ,p_assignment_id => p_assignment_id
1434 ,p_element_link_id => l_ltc_element_link_id
1435 ,p_entry_type => 'E'
1439 ,p_input_value_id4 => l_ben_input_values_rec(4).input_value_id
1436 ,p_input_value_id1 => l_ben_input_values_rec(1).input_value_id
1437 ,p_input_value_id2 => l_ben_input_values_rec(2).input_value_id
1438 ,p_input_value_id3 => l_ben_input_values_rec(3).input_value_id
1440 ,p_input_value_id5 => l_ben_input_values_rec(5).input_value_id
1441 ,p_input_value_id6 => l_ben_input_values_rec(6).input_value_id
1442 ,p_input_value_id7 => l_ben_input_values_rec(7).input_value_id
1443 ,p_input_value_id8 => l_ben_input_values_rec(8).input_value_id
1444 ,p_input_value_id9 => l_ben_input_values_rec(9).input_value_id
1445 ,p_entry_value1 => l_ben_input_values_rec(1).value
1446 ,p_entry_value2 => l_ben_input_values_rec(2).value
1447 ,p_entry_value3 => P_EXEMPTED
1448 ,p_entry_value4 => P_LTCBLOCK
1449 ,p_entry_value5 => P_BENEFIT
1450 ,p_entry_value6 => P_CARRY_OVER
1451 ,p_entry_value7 => l_ben_input_values_rec(7).value
1452 ,p_entry_value8 => l_ben_input_values_rec(8).value
1453 ,p_entry_value9 => l_ben_input_values_rec(9).value
1454 ,p_effective_start_date => l_start_date
1455 ,p_effective_end_date => l_end_date
1456 ,p_element_entry_id => l_element_entry_id
1457 ,p_object_version_number => l_object_version_no
1458 ,p_create_warning => l_warnings
1459 );
1460
1461 END IF;
1462 pay_in_utils.set_location(g_debug,'Benefit Element Creation Completed'||l_procedure,60);
1463
1464
1465
1466 END IF ;
1467 END IF;
1468
1469
1470 pay_in_utils.set_location(g_debug,'Updating Assignment_Extra_Info: '||l_procedure,70);
1471
1472 IF P_EMPLOYEE_ID <> 0 THEN /* To prevent this from Self Service page and execute only for web adi*/
1473
1474 UPDATE per_assignment_extra_info
1475 SET AEI_INFORMATION10 = P_EXEMPTED
1476 ,AEI_INFORMATION6 = P_ER_COMMENTS
1477 ,AEI_INFORMATION11 = nvl(l_element_entry_id,p_element_entry_id)
1478 ,aei_information18 = P_CARRY_OVER
1479 ,AEI_INFORMATION21 = P_BENEFIT
1480 WHERE ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;
1481
1482 pay_in_utils.set_location(g_debug,'Updating Assignment_Extra_Info: '||l_procedure,72);
1483
1484 END IF;
1485
1486 pay_in_utils.trace('**************************************************','********************');
1487 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
1488 END create_ltc_element;
1489
1490
1491 --------------------------------------------------------------------------
1492 -- --
1493 -- Name : UPDATE_LTC_ELEMENT --
1494 -- Type : PROCEDURE --
1495 -- Access : Public --
1496 -- Description : Function to update the LTC element enrty as per the --
1497 -- details passed from the Web ADI Excel Sheet. --
1498 -- --
1499 ---------------------------------------------------------------------------
1500
1501 PROCEDURE update_ltc_element
1502 (
1503 p_employee_number IN VARCHAR2
1504 ,p_full_name IN VARCHAR2
1505 ,p_start_date IN DATE
1506 ,p_effective_end_date IN DATE DEFAULT NULL
1507 ,p_fare IN NUMBER
1508 ,p_blockYr IN VARCHAR2
1509 ,p_carry IN VARCHAR2
1510 ,p_benefit IN NUMBER
1511 ,p_assignment_id IN NUMBER
1512 ,p_element_entry_id IN NUMBER DEFAULT NULL
1513 )
1514 IS
1515
1516
1517
1518
1519 CURSOR c_input_rec(p_element_type_id NUMBER
1520 ,p_effective_date DATE
1521 )
1522 IS
1523 SELECT inputs.name name
1524 , inputs.input_value_id id
1525 , inputs.default_value value
1526 FROM pay_element_types_f types
1527 , pay_input_values_f inputs
1528 WHERE types.element_type_id = p_element_type_id
1529 AND inputs.element_type_id = types.element_type_id
1530 --AND inputs.legislation_code = 'IN'
1531 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
1532 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
1533 ORDER BY inputs.display_sequence;
1534
1535
1536
1537
1538 CURSOR c_get_ele_object_version(p_element_entryid NUMBER )
1539 IS
1540 SELECT object_version_number
1541 FROM pay_element_entries_f
1542 WHERE element_entry_id = p_element_entryid;
1543
1544
1545
1546 CURSOR c_get_ele_type_id(p_element_entryid NUMBER)
1547 IS
1548 SELECT element_type_id
1549 FROM pay_element_entries_f
1550 WHERE element_entry_id = p_element_entryid;
1551
1552
1553 --Variables Initialization
1554 TYPE t_input_values_rec IS RECORD
1555 (input_name pay_input_values_f.name%TYPE
1556 ,input_value_id pay_input_values_f.input_value_id%TYPE
1557 ,value pay_input_values_f.default_value%TYPE
1558 );
1559
1563 l_warnings BOOLEAN;
1560 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
1561
1562 l_procedure VARCHAR2(250);
1564 l_input_values_rec t_input_values_tab;
1565 l_business_group_id NUMBER;
1566 l_count NUMBER;
1567 l_element_type_id NUMBER;
1568 l_start_date DATE ;
1569 l_end_date DATE ;
1570 l_object_version_no per_assignment_extra_info.object_version_number%TYPE ;
1571 l_session NUMBER ;
1572
1573
1574
1575 BEGIN
1576
1577 --hr_utility.trace_on (null, 'ORACLE1212');
1578 g_debug := hr_utility.debug_enabled;
1579 l_procedure := g_package ||'update_ltc_element';
1580 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1581 IF (g_debug)
1582 THEN
1583 pay_in_utils.trace('**************************************************','********************');
1584 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1585 pay_in_utils.trace('p_employee_number',TO_CHAR (p_employee_number));
1586 pay_in_utils.trace('p_full_name',TO_CHAR (p_full_name));
1587 pay_in_utils.trace('p_start_date',TO_CHAR (p_start_date));
1588 pay_in_utils.trace('p_effective_end_date',TO_CHAR (p_effective_end_date));
1589 pay_in_utils.trace('p_fare',TO_CHAR (p_fare));
1590 pay_in_utils.trace('p_blockYr',TO_CHAR (p_blockYr));
1591 pay_in_utils.trace('p_carry',TO_CHAR (p_carry));
1592 pay_in_utils.trace('p_benefit',TO_CHAR (p_benefit));
1593 pay_in_utils.trace('p_assignment_id',TO_CHAR (p_assignment_id));
1594 pay_in_utils.trace(' p_element_entry_id',TO_CHAR (p_element_entry_id));
1595
1596 END IF;
1597
1598 IF p_fare < 0 THEN
1599 hr_utility.set_message(800, 'PER_IN_BEN_AMOUNT');
1600 hr_utility.raise_error;
1601 END IF ;
1602
1603
1604 IF p_benefit < 0 THEN
1605 hr_utility.set_message(800, 'PER_IN_BEN_APPROVED_AMOUNT');
1606 hr_utility.raise_error;
1607 END IF ;
1608
1609
1610 BEGIN
1611 SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
1612 EXCEPTION
1613 WHEN NO_DATA_FOUND THEN
1614 INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),p_start_date);
1615 END ;
1616
1617 l_business_group_id := get_bg_id();
1618
1619 IF (g_debug)
1620 THEN
1621 pay_in_utils.trace('Business Group:',TO_CHAR (l_business_group_id));
1622 END IF;
1623
1624
1625 IF ((p_element_entry_id IS NOT NULL))
1626 THEN
1627
1628 pay_in_utils.set_location(g_debug,'Updating Element Entries: '||l_procedure,30);
1629
1630 OPEN c_get_ele_object_version(p_element_entry_id) ;
1631 FETCH c_get_ele_object_version INTO l_object_version_no ;
1632 CLOSE c_get_ele_object_version ;
1633
1634 OPEN c_get_ele_type_id(p_element_entry_id);
1635 FETCH c_get_ele_type_id INTO l_element_type_id ;
1636 CLOSE c_get_ele_type_id ;
1637
1638 l_count := 1;
1639 FOR c_rec IN c_input_rec(l_element_type_id,p_start_date)
1640 LOOP
1641 l_input_values_rec(l_count).input_name := c_rec.name;
1642 l_input_values_rec(l_count).input_value_id := c_rec.id;
1643 l_input_values_rec(l_count).value := c_rec.value;
1644
1645 IF (g_debug)
1646 THEN
1647 pay_in_utils.trace('Input Values Name:'||l_count,TO_CHAR (c_rec.name));
1648 END IF;
1649
1650 l_count := l_count + 1;
1651 END LOOP;
1652
1653
1654
1655 pay_element_entry_api.update_element_entry
1656 (p_datetrack_update_mode => hr_api.g_correction
1657 ,p_effective_date => p_start_date
1658 ,p_business_group_id => l_business_group_id
1659 ,p_element_entry_id => p_element_entry_id
1660 ,p_object_version_number => l_object_version_no
1661 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1662 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1663 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1664 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1665 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1666 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1667 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1668 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1669 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1670 ,p_entry_value1 => l_input_values_rec(1).value
1671 ,p_entry_value2 => l_input_values_rec(2).value
1672 ,p_entry_value3 => p_fare
1673 ,p_entry_value4 => p_blockYr
1674 ,p_entry_value5 => p_benefit
1675 ,p_entry_value6 => p_carry
1676 ,p_entry_value7 => l_input_values_rec(7).value
1677 ,p_entry_value8 => l_input_values_rec(8).value
1678 ,p_entry_value9 => l_input_values_rec(9).value
1679 ,p_effective_start_date => l_start_date
1680 ,p_effective_end_date => l_end_date
1681 ,p_update_warning => l_warnings
1682 );
1683
1684
1688 SET AEI_INFORMATION18 = p_carry
1685 END IF ;
1686
1687 UPDATE per_assignment_extra_info
1689 ,AEI_INFORMATION21 = P_BENEFIT
1690 where AEI_INFORMATION11 = p_element_entry_id
1691 and assignment_id = p_assignment_id;
1692
1693 pay_in_utils.trace('**************************************************','********************');
1694 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1695
1696
1697
1698
1699 END update_ltc_element;
1700
1701 END pay_in_med_web_adi;