[Home] [Help]
PACKAGE BODY: APPS.HR_LOC_ABSENCE
Source
1 PACKAGE BODY hr_loc_absence AS
2 /* $Header: hrabsloc.pkb 120.16 2011/05/17 13:33:29 nchinnam ship $ */
3
4 g_package varchar2(33) := ' hr_loc_absence.';
5
6 /* pgopal- Added p_original_entry_id parameter*/
7 procedure get_element_details
8 (p_absence_attendance_id in number
9 ,p_assignment_id in number
10 ,p_element_type_id out nocopy number
11 ,p_create_entry out nocopy varchar2
12 ,p_original_entry_id OUT NOCOPY NUMBER --gpopal
13 ,p_input_value_id1 out nocopy number
14 ,p_entry_value1 out nocopy VARCHAR2
15 ,p_input_value_id2 out nocopy number
16 ,p_entry_value2 out nocopy VARCHAR2
17 ,p_input_value_id3 out nocopy number
18 ,p_entry_value3 out nocopy VARCHAR2
19 ,p_input_value_id4 out nocopy number
20 ,p_entry_value4 out nocopy VARCHAR2
21 ,p_input_value_id5 out nocopy number
22 ,p_entry_value5 out nocopy VARCHAR2
23 ,p_input_value_id6 out nocopy number
24 ,p_entry_value6 out nocopy VARCHAR2
25 ,p_input_value_id7 out nocopy number
26 ,p_entry_value7 out nocopy VARCHAR2
27 ,p_input_value_id8 out nocopy number
28 ,p_entry_value8 out nocopy VARCHAR2
29 ,p_input_value_id9 out nocopy number
30 ,p_entry_value9 out nocopy VARCHAR2
31 ,p_input_value_id10 out nocopy number
32 ,p_entry_value10 out nocopy VARCHAR2
33 ,p_input_value_id11 out nocopy number
34 ,p_entry_value11 out nocopy VARCHAR2
35 ,p_input_value_id12 out nocopy number
36 ,p_entry_value12 out nocopy VARCHAR2
37 ,p_input_value_id13 out nocopy number
38 ,p_entry_value13 out nocopy VARCHAR2
39 ,p_input_value_id14 out nocopy number
40 ,p_entry_value14 out nocopy VARCHAR2
41 ,p_input_value_id15 out nocopy number
42 ,p_entry_value15 out nocopy VARCHAR2
43 ) is
44
45
46
47 cursor csr_get_absence_element(p_assignment_id NUMBER, p_element_type_id NUMBER) is
48 select pee.element_entry_id element_entry_id
49 ,pet.processing_type processing_type
50 ,pee.effective_start_date effective_start_date
51 ,pee.effective_end_date effective_end_date
52 from per_absence_attendances abs
53 ,per_all_assignments_f asg
54 ,pay_element_types_f pet
55 ,pay_element_links_f pel
56 ,pay_element_entries_f pee
57 ,pay_element_entry_values_f peev
58 ,pay_input_values_f piv
59 where abs.absence_attendance_id = p_absence_attendance_id
60 and abs.person_id = asg.person_id
61 and asg.assignment_id = p_assignment_id
62 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
63 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
64 and pet.element_type_id = p_element_type_id
65 and nvl(abs.date_start,pet.effective_end_date) <= pet.effective_end_date
66 and nvl(abs.date_end,pet.effective_start_date) >= pet.effective_start_date
67 and pet.element_type_id = pel.element_type_id
68 and pel.element_link_id = pee.element_link_id
69 and pee.assignment_id = asg.assignment_id
70 and pee.creator_type = 'F'
71 and pet.element_type_id = piv.element_type_id
72 and nvl(abs.date_start,piv.effective_end_date) <= piv.effective_end_date
73 and nvl(abs.date_end,piv.effective_start_date) >= piv.effective_start_date
74 and piv.name = 'CREATOR_ID'
75 and peev.element_entry_id = pee.element_entry_id
76 and peev.input_value_id=piv.input_value_id
77 and peev.screen_entry_value = abs.absence_attendance_id;
78
79 /*
80 select pee.element_entry_id element_entry_id
81 ,pet.processing_type processing_type
82 ,pee.effective_start_date effective_start_date
83 ,pee.effective_end_date effective_end_date
84 from per_absence_attendances abs
85 ,per_all_assignments_f asg
86 ,pay_element_types_f pet
87 ,pay_element_links_f pel
88 ,pay_element_entries_f pee
89 where abs.absence_attendance_id = p_absence_attendance_id
90 and abs.person_id = asg.person_id
91 and asg.assignment_id = p_assignment_id
92 and abs.date_start <= asg.effective_end_date
93 and abs.date_end >= asg.effective_start_date
94 and pet.element_type_id = p_element_type_id
95 and abs.date_start >= pet.effective_start_date
96 and abs.date_end <= pet.effective_end_date
97 and pet.element_type_id = pel.element_type_id
98 and pel.element_link_id = pee.element_link_id
99 and pee.assignment_id = asg.assignment_id
100 and pee.creator_id = abs.absence_attendance_id
101 and pee.creator_type = 'F';*/
102
103 --
104 -- Get details for the absence being procesed.
105 --
106 CURSOR csr_absence_details(p_absence_attendance_id NUMBER) IS
107 SELECT abs.business_group_id
108 ,abt.name
109 ,abs.person_id
110 ,abs.date_start
111 ,abs.date_end
112 ,abt.absence_category
113 FROM per_absence_attendances abs
114 ,per_absence_attendance_types abt
115 WHERE abs.absence_attendance_id = p_absence_attendance_id
116 AND abt.absence_attendance_type_id = abs.absence_attendance_type_id;
117 --
118 CURSOR csr_entry_values(p_element_type_id NUMBER, p_iv1_name VARCHAR2, p_iv1_value VARCHAR2, p_effective_date DATE) IS
119 SELECT iv.input_value_id input_value
120 ,p_iv1_value entry_value
121 FROM pay_input_values_f iv
122 WHERE iv.element_type_id = p_element_type_id
123 AND iv.name = p_iv1_name
124 AND p_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date;
125
126
127 CURSOR csr_legislation_code (p_business_group_id NUMBER) IS
128 SELECT legislation_code
129 FROM per_business_groups_perf
130 WHERE business_group_id = p_business_group_id;
131
132 CURSOR csr_is_record_seeded (p_legislation_code VARCHAR2,
133 p_table_name VARCHAR2,
134 p_exact VARCHAR2) IS
135 SELECT pur.legislation_code, pur.business_group_id
136 FROM pay_user_tables put
137 ,pay_user_rows_f pur
138 WHERE user_table_name = p_table_name
139 AND nvl(put.legislation_code, p_legislation_code) = p_legislation_code
140 AND put.user_table_id = pur.user_table_id
141 and pur.row_low_range_or_name = p_exact ;
142
143 l_abs_rec csr_absence_details%ROWTYPE;
144 l_is_record_seeded_rec csr_is_record_seeded%ROWTYPE;
145
146
147 l_legislation_code VARCHAR2(2);
148
149 l_entry_values_rec csr_entry_values%ROWTYPE;
150 l_element_link_id NUMBER;
151 l_date_start DATE;
152 l_date_end DATE;
153 l_element_entry_id NUMBER;
154 l_element_type_id NUMBER(15);
155 l_plsql_block VARCHAR2(2000); /* := 'BEGIN <PROC_NAME>(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14
156 ,:15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26
157 ,:27, :28, :29, :30, :31, :32, :33, :34); END;';*/
158
159
160 l_iv1_name VARCHAR2(30);
161 l_iv1_value VARCHAR2(240); --rravi
162 l_iv2_name VARCHAR2(30);
163 l_iv2_value VARCHAR2(240); --rravi
164 l_iv3_name VARCHAR2(30);
165 l_iv3_value VARCHAR2(240); --rravi
166 l_iv4_name VARCHAR2(30);
167 l_iv4_value VARCHAR2(240); --rravi
168 l_iv5_name VARCHAR2(30);
169 l_iv5_value VARCHAR2(240); --rravi
170 l_iv6_name VARCHAR2(30);
171 l_iv6_value VARCHAR2(240); --rravi
172 l_iv7_name VARCHAR2(30);
173 l_iv7_value VARCHAR2(240); --rravi
174 l_iv8_name VARCHAR2(30);
175 l_iv8_value VARCHAR2(30);
176 l_iv9_name VARCHAR2(30);
177 l_iv9_value VARCHAR2(30);
178 l_iv10_name VARCHAR2(30);
179 l_iv10_value VARCHAR2(30);
180 l_iv11_name VARCHAR2(30);
181 l_iv11_value VARCHAR2(30);
182 l_iv12_name VARCHAR2(30);
183 l_iv12_value VARCHAR2(30);
184 l_iv13_name VARCHAR2(30);
185 l_iv13_value VARCHAR2(30);
186 l_iv14_name VARCHAR2(30);
187 l_iv14_value VARCHAR2(30);
188 l_iv15_name VARCHAR2(30);
189 l_iv15_value VARCHAR2(30);
190
191 l_return VARCHAR2(20);
192 l_entry_package VARCHAR2(60);
193 l_proc varchar2(100) := g_package||'.get_element_details ';
194 BEGIN
195 --
196 hr_utility.set_location('Entering:'|| l_proc, 10);
197 --
198 -- Get absence information.
199 --
200 OPEN csr_absence_details(p_absence_attendance_id);
201 FETCH csr_absence_details INTO l_abs_rec;
202 CLOSE csr_absence_details;
203 --
204 --
205 -- Get absence to element mapping information.
206 --
207
208 l_element_type_id := get_element_for_category(p_absence_attendance_id);
209 l_entry_package := get_package_for_category(p_absence_attendance_id);
210
211 hr_utility.set_location('l_element_type_id '|| l_element_type_id, 15);
212
213 --
214 l_date_start := l_abs_rec.date_start;
215
216 -- Newly added 27th Feb 2006 - Category Checking Start
217 if (l_abs_rec.absence_category is null or l_entry_package is null) then
218 return;
219 else
220
221 --
222 --
223 --
224 -- Call the external procedure to get the values to be set on the element entry.
225 --
226
227 OPEN csr_legislation_code (l_abs_rec.business_group_id);
228 FETCH csr_legislation_code INTO l_legislation_code;
229 CLOSE csr_legislation_code;
230
231 OPEN csr_is_record_seeded(l_legislation_code, l_legislation_code || '_ABSENCE_TYPE_AND_DETAILS', l_abs_rec.name);
232 FETCH csr_is_record_seeded into l_is_record_seeded_rec;
233 close csr_is_record_seeded;
234
235 IF l_is_record_seeded_rec.legislation_code is null and l_is_record_seeded_rec.business_group_id is null then
236 OPEN csr_is_record_seeded(l_legislation_code, l_legislation_code || '_ABSENCE_CATEGORY_AND_DETAILS', l_abs_rec.absence_category);
237 FETCH csr_is_record_seeded into l_is_record_seeded_rec;
238 close csr_is_record_seeded;
239 END IF;
240
241
242 /*pgopal - Added one more parameter for p_original_entry_id*/
243 IF l_is_record_seeded_rec.business_group_id IS NOT NULL THEN
244 /* In case user table row is not seeded and is created by users then execute the procedure*/
245 l_plsql_block := 'BEGIN <PROC_NAME>(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14
246 ,:15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26
247 ,:27, :28, :29, :30, :31, :32, :33, :34, :36); END;';
248
249 SELECT REPLACE(l_plsql_block, '<PROC_NAME>', l_entry_package) INTO l_plsql_block FROM dual;
250 --
251 EXECUTE IMMEDIATE l_plsql_block
252 USING l_abs_rec.person_id
253 ,p_absence_attendance_id
254 ,l_element_type_id
255 ,l_abs_rec.absence_category
256 ,OUT p_original_entry_id --pgopal
257 ,OUT l_iv1_name
258 ,OUT l_iv1_value
259 ,OUT l_iv2_name
260 ,OUT l_iv2_value
261 ,OUT l_iv3_name
262 ,OUT l_iv3_value
263 ,OUT l_iv4_name
264 ,OUT l_iv4_value
265 ,OUT l_iv5_name
266 ,OUT l_iv5_value
267 ,OUT l_iv6_name
268 ,OUT l_iv6_value
269 ,OUT l_iv7_name
270 ,OUT l_iv7_value
271 ,OUT l_iv8_name
272 ,OUT l_iv8_value
273 ,OUT l_iv9_name
274 ,OUT l_iv9_value
275 ,OUT l_iv10_name
276 ,OUT l_iv10_value
277 ,OUT l_iv11_name
278 ,OUT l_iv11_value
279 ,OUT l_iv12_name
280 ,OUT l_iv12_value
281 ,OUT l_iv13_name
282 ,OUT l_iv13_value
283 ,OUT l_iv14_name
284 ,OUT l_iv14_value
285 ,OUT l_iv15_name
286 ,OUT l_iv15_value;
287
288 ELSE
289
290 l_plsql_block := 'BEGIN :l_return := <FUNC_NAME>(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13,
291 :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25,
292 :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36); END;';
293
294 hr_utility.set_location(' b4 execute imm of function ', 25);
295
296 SELECT REPLACE(l_plsql_block, '<FUNC_NAME>', l_entry_package) INTO l_plsql_block FROM dual;
297 --
298 EXECUTE IMMEDIATE l_plsql_block
299 USING out l_return
300 ,p_assignment_id
301 ,l_abs_rec.person_id
302 ,p_absence_attendance_id
303 ,l_element_type_id
304 ,l_abs_rec.absence_category
305 ,OUT p_original_entry_id --pgopal
306 ,OUT l_iv1_name
307 ,OUT l_iv1_value
308 ,OUT l_iv2_name
309 ,OUT l_iv2_value
310 ,OUT l_iv3_name
311 ,OUT l_iv3_value
312 ,OUT l_iv4_name
313 ,OUT l_iv4_value
314 ,OUT l_iv5_name
315 ,OUT l_iv5_value
316 ,OUT l_iv6_name
317 ,OUT l_iv6_value
318 ,OUT l_iv7_name
319 ,OUT l_iv7_value
320 ,OUT l_iv8_name
321 ,OUT l_iv8_value
322 ,OUT l_iv9_name
323 ,OUT l_iv9_value
324 ,OUT l_iv10_name
325 ,OUT l_iv10_value
326 ,OUT l_iv11_name
327 ,OUT l_iv11_value
328 ,OUT l_iv12_name
329 ,OUT l_iv12_value
330 ,OUT l_iv13_name
331 ,OUT l_iv13_value
332 ,OUT l_iv14_name
333 ,OUT l_iv14_value
334 ,OUT l_iv15_name
335 ,OUT l_iv15_value;
336
337 hr_utility.set_location('After execute imm of function ', 25);
338
339 END IF;
340
341 END IF; -- Newly added - Category Checking - End
342 --
343 hr_utility.set_location('After execute imm', 30);
344 --
345 -- Translate input value name / value pairs returned from external procedure into format to be used to create element entry.
346 --
347 p_create_entry := nvl(l_return,'Y');
348
349 IF nvl(l_return,'Y') = 'Y' THEN
350 IF l_iv1_name IS NOT NULL THEN
351 OPEN csr_entry_values(l_element_type_id, l_iv1_name, l_iv1_value, l_date_start);
352 FETCH csr_entry_values INTO l_entry_values_rec;
353 CLOSE csr_entry_values;
354 END IF;
355
356 p_input_value_id1 := l_entry_values_rec.input_value;
357 p_entry_value1 := l_entry_values_rec.entry_value;
358
359 hr_utility.set_location(' p_entry_value1: ' || l_entry_values_rec.entry_value, 35);
360
361 IF l_iv2_name IS NOT NULL THEN
362 OPEN csr_entry_values(l_element_type_id, l_iv2_name, l_iv2_value, l_date_start);
363 FETCH csr_entry_values INTO l_entry_values_rec;
364 CLOSE csr_entry_values;
365 END IF;
366
367 p_input_value_id2 := l_entry_values_rec.input_value;
368 p_entry_value2 := l_entry_values_rec.entry_value;
369
370 hr_utility.set_location(' p_entry_value2: ' || l_entry_values_rec.entry_value, 35);
371
372 IF l_iv3_name IS NOT NULL THEN
373 OPEN csr_entry_values(l_element_type_id, l_iv3_name, l_iv3_value, l_date_start);
374 FETCH csr_entry_values INTO l_entry_values_rec;
375 CLOSE csr_entry_values;
376 END IF;
377
378 p_input_value_id3 := l_entry_values_rec.input_value;
379 p_entry_value3 := l_entry_values_rec.entry_value;
380
381 hr_utility.set_location(' p_entry_value3: ' || l_entry_values_rec.entry_value, 35);
382
383
384 IF l_iv4_name IS NOT NULL THEN
385 OPEN csr_entry_values(l_element_type_id, l_iv4_name, l_iv4_value, l_date_start);
386 FETCH csr_entry_values INTO l_entry_values_rec;
387 CLOSE csr_entry_values;
388 END IF;
389
390 p_input_value_id4 := l_entry_values_rec.input_value;
391 p_entry_value4 := l_entry_values_rec.entry_value;
392
393 hr_utility.set_location(' p_entry_value4: ' || l_entry_values_rec.entry_value, 35);
394
395 IF l_iv5_name IS NOT NULL THEN
396 OPEN csr_entry_values(l_element_type_id, l_iv5_name, l_iv5_value, l_date_start);
397 FETCH csr_entry_values INTO l_entry_values_rec;
398 CLOSE csr_entry_values;
399 END IF;
400
401 p_input_value_id5 := l_entry_values_rec.input_value;
402 p_entry_value5 := l_entry_values_rec.entry_value;
403
404 IF l_iv6_name IS NOT NULL THEN
405 OPEN csr_entry_values(l_element_type_id, l_iv6_name, l_iv6_value, l_date_start);
406 FETCH csr_entry_values INTO l_entry_values_rec;
407 CLOSE csr_entry_values;
408 END IF;
409
410 p_input_value_id6 := l_entry_values_rec.input_value;
411 p_entry_value6 := l_entry_values_rec.entry_value;
412
413 IF l_iv7_name IS NOT NULL THEN
414 OPEN csr_entry_values(l_element_type_id, l_iv7_name, l_iv7_value, l_date_start);
415 FETCH csr_entry_values INTO l_entry_values_rec;
416 CLOSE csr_entry_values;
417 END IF;
418
419 p_input_value_id7 := l_entry_values_rec.input_value;
420 p_entry_value7 := l_entry_values_rec.entry_value;
421
422 IF l_iv8_name IS NOT NULL THEN
423 OPEN csr_entry_values(l_element_type_id, l_iv8_name, l_iv8_value, l_date_start);
424 FETCH csr_entry_values INTO l_entry_values_rec;
425 CLOSE csr_entry_values;
426 END IF;
427
428 p_input_value_id8 := l_entry_values_rec.input_value;
429 p_entry_value8 := l_entry_values_rec.entry_value;
430
431 IF l_iv9_name IS NOT NULL THEN
432 OPEN csr_entry_values(l_element_type_id, l_iv9_name, l_iv9_value, l_date_start);
433 FETCH csr_entry_values INTO l_entry_values_rec;
434 CLOSE csr_entry_values;
435 END IF;
436
437 p_input_value_id9 := l_entry_values_rec.input_value;
438 p_entry_value9 := l_entry_values_rec.entry_value;
439
440 IF l_iv10_name IS NOT NULL THEN
441 OPEN csr_entry_values(l_element_type_id, l_iv10_name, l_iv10_value, l_date_start);
442 FETCH csr_entry_values INTO l_entry_values_rec;
443 CLOSE csr_entry_values;
444 END IF;
445
446 p_input_value_id10 := l_entry_values_rec.input_value;
447 p_entry_value10 := l_entry_values_rec.entry_value;
448
449 IF l_iv11_name IS NOT NULL THEN
450 OPEN csr_entry_values(l_element_type_id, l_iv11_name, l_iv11_value, l_date_start);
451 FETCH csr_entry_values INTO l_entry_values_rec;
452 CLOSE csr_entry_values;
453 END IF;
454
455 p_input_value_id11 := l_entry_values_rec.input_value;
456 p_entry_value11 := l_entry_values_rec.entry_value;
457
458 IF l_iv12_name IS NOT NULL THEN
459 OPEN csr_entry_values(l_element_type_id, l_iv12_name, l_iv12_value, l_date_start);
460 FETCH csr_entry_values INTO l_entry_values_rec;
461 CLOSE csr_entry_values;
462 END IF;
463
464 p_input_value_id12 := l_entry_values_rec.input_value;
465 p_entry_value12 := l_entry_values_rec.entry_value;
466
467 IF l_iv13_name IS NOT NULL THEN
468 OPEN csr_entry_values(l_element_type_id, l_iv13_name, l_iv13_value, l_date_start);
469 FETCH csr_entry_values INTO l_entry_values_rec;
470 CLOSE csr_entry_values;
471 END IF;
472
473 p_input_value_id13 := l_entry_values_rec.input_value;
474 p_entry_value13 := l_entry_values_rec.entry_value;
475
476 IF l_iv14_name IS NOT NULL THEN
477 OPEN csr_entry_values(l_element_type_id, l_iv14_name, l_iv14_value, l_date_start);
478 FETCH csr_entry_values INTO l_entry_values_rec;
479 CLOSE csr_entry_values;
480 END IF;
481
482 p_input_value_id14 := l_entry_values_rec.input_value;
483 p_entry_value14 := l_entry_values_rec.entry_value;
484
485 IF l_iv15_name IS NOT NULL THEN
486 OPEN csr_entry_values(l_element_type_id, l_iv15_name, l_iv15_value, l_date_start);
487 FETCH csr_entry_values INTO l_entry_values_rec;
488 CLOSE csr_entry_values;
489 END IF;
490
491
492 p_element_type_id := l_element_type_id;
493 p_input_value_id15 := l_entry_values_rec.input_value;
494 p_entry_value15 := l_entry_values_rec.entry_value;
495
496 END IF;
497
498 end get_element_details;
499
500
501 PROCEDURE create_absence (p_absence_attendance_id NUMBER
502 ,p_effective_date DATE
503 ,p_date_start DATE
504 ,p_date_end DATE) IS
505 --
506 --
507 -- Local Cursors.
508 --
509 --
510 -- Find all assignments for the person as of a given date.
511 -- Bug no 5020916. Order by clause included in the cursor
512 CURSOR csr_assignments(p_person_id NUMBER, p_effective_date DATE) IS
513 SELECT asg.assignment_id, asg.effective_start_date, asg.effective_end_date
514 FROM per_all_assignments_f asg,
515 per_absence_attendances paa
516 WHERE paa.absence_attendance_id = p_absence_attendance_id
517 and asg.person_id = paa.person_id
518 AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
519 AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date
520 ORDER BY asg.assignment_id, asg.effective_start_date, asg.effective_end_date;
521 --
522 -- Find all assignments for the person as of a given date.
523 --
524 --
525 CURSOR csr_processing_type (p_element_type_id NUMBER) IS
526 SELECT processing_type
527 FROM pay_element_types_f
528 where element_type_id= p_element_type_id
529 AND p_effective_date between effective_start_date and effective_end_date;
530
531 -- Local Variables.
532 --
533 l_element_link_id NUMBER;
534 l_date_start DATE;
535 l_date_end DATE;
536 l_element_entry_id NUMBER;
537 l_input_value_id NUMBER(15);
538 l_input_value VARCHAR2(100);
539 l_element_type_id NUMBER(15);
540 l_proc varchar2(100) := g_package||'.create_absence ';
541
542 l_iv2_id NUMBER(15);
543 l_iv2_value VARCHAR2(240); --rravi
544 l_iv3_id NUMBER(15);
545 l_iv3_value VARCHAR2(30);
546 l_iv4_id NUMBER(15);
547 l_iv4_value VARCHAR2(30);
548 l_iv5_id NUMBER(15);
549 l_iv5_value VARCHAR2(30);
550 l_iv6_id NUMBER(15);
551 l_iv6_value VARCHAR2(30);
552 l_iv7_id NUMBER(15);
553 l_iv7_value VARCHAR2(30);
554 l_iv8_id NUMBER(15);
555 l_iv8_value VARCHAR2(30);
556 l_iv9_id NUMBER(15);
557 l_iv9_value VARCHAR2(30);
558 l_iv10_id NUMBER(15);
559 l_iv10_value VARCHAR2(30);
560 l_iv11_id NUMBER(15);
561 l_iv11_value VARCHAR2(30);
562 l_iv12_id NUMBER(15);
563 l_iv12_value VARCHAR2(30);
564 l_iv13_id NUMBER(15);
565 l_iv13_value VARCHAR2(30);
566 l_iv14_id NUMBER(15);
567 l_iv14_value VARCHAR2(30);
568 l_iv15_id NUMBER(15);
569 l_iv15_value VARCHAR2(30);
570 l_processing_type VARCHAR2(30);
571 l_create_entry VARCHAR2(20);
572 l_original_entry_id NUMBER ; --pgopal
573 -- Bug no 5020916. Local variable declared to keep assignment id
574 l_old_assignment NUMBER(15);
575 BEGIN
576 --
577 -- Added for GSI Bug 5472781
578 ---pgopal -Included 'NO','SE','PL' in the legislation installation check.
579 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') OR
580 hr_utility.chk_product_install('Oracle Human Resources', 'NO') OR
581 hr_utility.chk_product_install('Oracle Human Resources', 'SE') OR
582 hr_utility.chk_product_install('Oracle Human Resources', 'PL') OR
583 hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
584 --hr_utility.trace_on(null,'GOV_ABS');
585 -- Bug no 5020916. Initialize local variable l_old_assignment as 0
586 l_old_assignment := 0;
587 --
588 --
589 -- Loop through all assignments for the person.
590 --
591 hr_utility.set_location('Entering:'|| l_proc, 10);
592 FOR l_asg_rec IN csr_assignments(p_absence_attendance_id, p_date_start) LOOP
593 -- Bug no 5020916. Restricting the for loop to process one time for each assignment.
594 IF l_old_assignment <> l_asg_rec.assignment_id THEN
595 l_old_assignment := l_asg_rec.assignment_id;
596 --
597 --
598 hr_utility.set_location('in for loop' , 20);
599 hr_utility.set_location('asg details : '||l_asg_rec.assignment_id||l_asg_rec.effective_start_date||l_asg_rec.effective_end_date , 20);
600 get_element_details(p_absence_attendance_id, l_asg_rec.assignment_id, l_element_type_id , l_create_entry
601 ,l_original_entry_id --pgopal
602 ,l_input_value_id, l_input_value
603 ,l_iv2_id, l_iv2_value
604 ,l_iv3_id, l_iv3_value
605 ,l_iv4_id, l_iv4_value
606 ,l_iv5_id, l_iv5_value
607 ,l_iv6_id, l_iv6_value
608 ,l_iv7_id, l_iv7_value
609 ,l_iv8_id, l_iv8_value
610 ,l_iv9_id, l_iv9_value
611 ,l_iv10_id, l_iv10_value
612 ,l_iv11_id, l_iv11_value
613 ,l_iv12_id, l_iv12_value
614 ,l_iv13_id, l_iv13_value
615 ,l_iv14_id, l_iv14_value
616 ,l_iv15_id, l_iv15_value );
617
618 hr_utility.set_location('after get_element_details' , 20);
619 --
620 --
621 hr_utility.set_location('l_asg_rec.assignment_id: ' || l_asg_rec.assignment_id , 25);
622 hr_utility.set_location('l_element_type_id: ' || l_element_type_id , 25);
623 hr_utility.set_location('p_date_start: ' || p_date_start , 25);
624
625 -- Bug no 5020916. Commenting assignment date check while attaching absences to assignments.
626 -- IF p_date_start > l_asg_rec.effective_start_date THEN
627 l_date_start := p_date_start;
628 -- ELSE
629 -- l_date_start := l_asg_rec.effective_start_date;
630 -- END IF;
631
632 -- IF p_date_end < l_asg_rec.effective_end_date THEN
633 l_date_end := p_date_end;
634 -- ELSE
635 -- l_date_end := l_asg_rec.effective_end_date;
636 -- END IF;
637
638 l_element_link_id := hr_entry_api.get_link(l_asg_rec.assignment_id, l_element_type_id, l_date_start);
639
640 hr_utility.set_location('l_element_link_id: ' || l_element_link_id , 25);
641
642 -- Create the element entry.
643 --
644 if l_element_link_id is not null then
645
646
647 OPEN csr_processing_type (l_element_type_id);
648 FETCH csr_processing_type into l_processing_type;
649 CLOSE csr_processing_type;
650
651 hr_utility.set_location('l_input_value_id ' || l_input_value_id , 25);
652 hr_utility.set_location('l_input_value: ' || l_input_value , 25);
653 hr_utility.set_location('l_iv2_id: ' || l_iv2_id , 25);
654 hr_utility.set_location('l_iv2_value: ' || l_iv2_value , 25);
655
656
657 if (l_processing_type = 'N'
658 and l_date_start is not null
659 and l_date_end is not null)
660 or (l_processing_type = 'R'
661 and l_date_start is not null)
662 then
663
664
665 IF l_create_entry = 'Y' THEN
666 hr_entry_api.insert_element_entry
667 (p_effective_start_date => l_date_start
668 ,p_effective_end_date => l_date_end
669 ,p_element_entry_id => l_element_entry_id
670 ,p_assignment_id => l_asg_rec.assignment_id
671 ,p_element_link_id => l_element_link_id
672 ,p_creator_type => 'F' -- 'A' for absence
673 ,p_entry_type => 'E'
674 ,p_creator_id => null--p_absence_attendance_id
675 ,p_original_entry_id => l_original_entry_id --pgopal
676 ,p_input_value_id1 => l_input_value_id
677 ,p_entry_value1 => l_input_value
678 ,p_input_value_id2 => l_iv2_id
679 ,p_entry_value2 => l_iv2_value
680 ,p_input_value_id3 => l_iv3_id
681 ,p_entry_value3 => l_iv3_value
682 ,p_input_value_id4 => l_iv4_id
683 ,p_entry_value4 => l_iv4_value
684 ,p_input_value_id5 => l_iv5_id
685 ,p_entry_value5 => l_iv5_value
686 ,p_input_value_id6 => l_iv6_id
687 ,p_entry_value6 => l_iv6_value
688 ,p_input_value_id7 => l_iv7_id
689 ,p_entry_value7 => l_iv7_value
690 ,p_input_value_id8 => l_iv8_id
691 ,p_entry_value8 => l_iv8_value
692 ,p_input_value_id9 => l_iv9_id
693 ,p_entry_value9 => l_iv9_value
694 ,p_input_value_id10 => l_iv10_id
695 ,p_entry_value10 => l_iv10_value
696 ,p_input_value_id11 => l_iv11_id
697 ,p_entry_value11 => l_iv11_value
698 ,p_input_value_id12 => l_iv12_id
699 ,p_entry_value12 => l_iv12_value
700 ,p_input_value_id13 => l_iv13_id
701 ,p_entry_value13 => l_iv13_value
702 ,p_input_value_id14 => l_iv14_id
703 ,p_entry_value14 => l_iv14_value
704 ,p_input_value_id15 => l_iv15_id
705 ,p_entry_value15 => l_iv15_value);
706 --
707 hr_utility.set_location('after element_entry creation ' ||l_element_entry_id , 80);
708 END IF;
709 END IF;
710 --
711 -- End date the element entry in line with the absence.
712 --
713
714
715 IF p_date_end IS NOT NULL AND l_processing_type = 'R' THEN
716 hr_entry_api.delete_element_entry
717 (p_dt_delete_mode => 'DELETE'
718 ,p_session_date => p_date_end
719 ,p_element_entry_id => l_element_entry_id);
720 END IF;
721 END IF;
722 END IF;
723 END LOOP;
724 END IF;
725 END create_absence;
726 --
727
728 function get_element_for_category (p_absence_attendance_id NUMBER) return NUMBER As
729
730 CURSOR csr_absence_details(p_absence_attendance_id NUMBER) IS
731 SELECT abs.business_group_id
732 ,abt.name
733 ,abs.person_id
734 ,abs.date_start
735 ,abs.date_end
736 ,abt.absence_category
737 FROM per_absence_attendances abs
738 ,per_absence_attendance_types abt
739 WHERE abs.absence_attendance_id = p_absence_attendance_id
740 AND abt.absence_attendance_type_id = abs.absence_attendance_type_id;
741
742 CURSOR csr_legislation_code (p_business_group_id NUMBER) IS
743 SELECT legislation_code
744 FROM per_business_groups_perf
745 WHERE business_group_id = p_business_group_id;
746
747 l_abs_rec csr_absence_details%ROWTYPE;
748
749 CURSOR csr_element_details (p_element_name VARCHAR2,
750 p_business_group_id NUMBER,
751 p_legislation_code VARCHAR2) IS
752 SELECT element_type_id
753 FROM pay_element_types_f pet
754 WHERE pet.element_name = p_element_name
755 AND nvl( pet.business_group_id, p_business_group_id) = p_business_group_id
756 AND nvl( pet.legislation_code, p_legislation_code) = p_legislation_code;
757
758 CURSOR csr_get_session_eff_date IS
759 SELECT effective_date
760 FROM fnd_sessions
761 WHERE session_id = userenv('sessionid');
762
763 l_element_type_id pay_element_types_f.element_type_id%TYPE;
764 l_element_name pay_element_types_f.element_name%TYPE;
765 l_proc varchar2(72) := g_package||'get_element_for_category';
766 l_legislation_code VARCHAR2(2);
767 l_dummy_effective_date DATE;
768
769 begin
770
771 hr_utility.set_location('Entering:'|| l_proc, 10);
772 -- Get absence information.
773 --
774 OPEN csr_absence_details(p_absence_attendance_id);
775 FETCH csr_absence_details INTO l_abs_rec;
776 CLOSE csr_absence_details;
777
778 --hruserdt.set_g_effective_date(sysdate);
779 OPEN csr_legislation_code (l_abs_rec.business_group_id);
780 FETCH csr_legislation_code INTO l_legislation_code;
781 CLOSE csr_legislation_code;
782
783 l_dummy_effective_date := NULL;
784 OPEN csr_get_session_eff_date ;
785 FETCH csr_get_session_eff_date INTO l_dummy_effective_date;
786 CLOSE csr_get_session_eff_date;
787 IF l_dummy_effective_date is NULL THEN
788 hruserdt.set_g_effective_date(sysdate);
789 END IF;
790
791
792 BEGIN
793
794 l_element_name := hruserdt.GET_TABLE_VALUE (l_abs_rec.business_group_id, l_legislation_code ||'_ABSENCE_TYPE_AND_DETAILS',
795 'ELEMENT',
796 l_abs_rec.name);
797
798 hr_utility.set_location('l_element_name: '|| l_element_name, 10);
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 NULL;
802 END;
803
804 IF l_element_name IS NULL THEN
805
806 BEGIN
807 l_element_name := hruserdt.GET_TABLE_VALUE (l_abs_rec.business_group_id, l_legislation_code ||'_ABSENCE_CATEGORY_AND_DETAILS',
808 'ELEMENT',
809 l_abs_rec.absence_category);
810 EXCEPTION
811 WHEN NO_DATA_FOUND THEN
812 NULL;
813 END;
814
815 END IF;
816
817 OPEN csr_element_details (l_element_name, l_abs_rec.business_group_id, l_legislation_code );
818 FETCH csr_element_details INTO l_element_type_id;
819 CLOSE csr_element_details;
820
821 RETURN l_element_type_id;
822
823 END get_element_for_category;
824
825
826 function get_package_for_category (p_absence_attendance_id NUMBER) return varchar2 As
827
828 CURSOR csr_absence_details(p_absence_attendance_id NUMBER) IS
829 SELECT abs.business_group_id
830 ,abt.name
831 ,abs.person_id
832 ,abs.date_start
833 ,abs.date_end
834 ,abt.absence_category
835 FROM per_absence_attendances abs
836 ,per_absence_attendance_types abt
837 WHERE abs.absence_attendance_id = p_absence_attendance_id
838 AND abt.absence_attendance_type_id = abs.absence_attendance_type_id;
839
840 CURSOR csr_legislation_code (p_business_group_id NUMBER) IS
841 SELECT legislation_code
842 FROM per_business_groups_perf
843 WHERE business_group_id = p_business_group_id;
844
845 l_legislation_code VARCHAR2(2);
846
847 l_abs_rec csr_absence_details%ROWTYPE;
848
849
850 l_package_name varchar2(60);
851 l_proc varchar2(72) := g_package||'get_package_for_category';
852
853
854 begin
855
856 hr_utility.set_location('Entering:'|| l_proc, 10);
857 -- Get absence information.
858 --
859 OPEN csr_absence_details(p_absence_attendance_id);
860 FETCH csr_absence_details INTO l_abs_rec;
861 CLOSE csr_absence_details;
862
863 -- hruserdt.set_g_effective_date(sysdate);
864
865 OPEN csr_legislation_code (l_abs_rec.business_group_id);
866 FETCH csr_legislation_code INTO l_legislation_code;
867 CLOSE csr_legislation_code;
868
869 BEGIN
870 l_package_name := hruserdt.GET_TABLE_VALUE (l_abs_rec.business_group_id, l_legislation_code|| '_ABSENCE_TYPE_AND_DETAILS',
871 'ELEMENT_ENTRY_LOGIC',
872 l_abs_rec.name);
873 EXCEPTION
874 WHEN NO_DATA_FOUND THEN
875 NULL;
876 END;
877
878 IF l_package_name IS NULL THEN
879 BEGIN
880 l_package_name := hruserdt.GET_TABLE_VALUE (l_abs_rec.business_group_id, l_legislation_code|| '_ABSENCE_CATEGORY_AND_DETAILS',
881 'ELEMENT_ENTRY_LOGIC',
882 l_abs_rec.absence_category);
883 EXCEPTION
884 WHEN NO_DATA_FOUND THEN
885 NULL;
886 END;
887 END IF;
888
889 RETURN l_package_name;
890
891 END get_package_for_category;
892
893 procedure get_absence_element
894 (p_absence_attendance_id in number
895 ,p_assignment_id in number
896 ,p_effective_date in date
897 ,p_processing_type out nocopy varchar2
898 ,p_element_entry_id out nocopy number
899 ,p_effective_start_date out nocopy date
900 ,p_effective_end_date out nocopy date) is
901
902 cursor c_get_absence_element(p_element_type_id NUMBER) is
903 select pee.element_entry_id element_entry_id
904 ,pet.processing_type processing_type
905 ,pee.effective_start_date effective_start_date
906 ,pee.effective_end_date effective_end_date
907 from per_absence_attendances abs
908 ,per_all_assignments_f asg
909 ,pay_element_types_f pet
910 ,pay_element_links_f pel
911 ,pay_element_entries_f pee
912 ,pay_element_entry_values_f peev
913 ,pay_input_values_f piv
914 where abs.absence_attendance_id = p_absence_attendance_id
915 and abs.person_id = asg.person_id
916 and asg.assignment_id = p_assignment_id
917 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
918 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
919 and pet.element_type_id = p_element_type_id
920 and p_effective_date between pet.effective_start_date and pet.effective_end_date
921 and pet.element_type_id = pel.element_type_id
922 and pel.element_link_id = pee.element_link_id
923 and pee.assignment_id = asg.assignment_id
924 and pee.creator_type = 'F'
925 and pet.element_type_id = piv.element_type_id
926 and p_effective_date between piv.effective_start_date and piv.effective_end_date
927 and piv.name = 'CREATOR_ID'
928 and peev.element_entry_id = pee.element_entry_id
929 and peev.input_value_id=piv.input_value_id
930 and peev.screen_entry_value = to_char(abs.absence_attendance_id); --8823797
931
932
933 /* select pee.element_entry_id
934 ,pet.processing_type
935 ,pee.effective_start_date
936 ,pee.effective_end_date
937 from per_absence_attendances abs
938 ,per_all_assignments_f asg
939 ,pay_element_types_f pet
940 ,pay_element_links_f pel
941 ,pay_element_entries_f pee
942 where abs.absence_attendance_id = p_absence_attendance_id
943 and abs.person_id = asg.person_id
944 and asg.assignment_id = p_assignment_id
945 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
946 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
947 and pet.element_type_id = p_element_type_id
948 and p_effective_date between pet.effective_start_date and pet.effective_end_date
949 and pet.element_type_id = pel.element_type_id
950 and pel.element_link_id = pee.element_link_id
951 and pee.assignment_id = asg.assignment_id
952 and pee.creator_id = abs.absence_attendance_id
953 and pee.creator_type = 'F';*/
954
955
956 l_proc varchar2(72) := g_package||'get_absence_element';
957 l_element_type_id NUMBER(15);
958
959 begin
960
961 hr_utility.set_location('Entering:'|| l_proc, 10);
962
963 l_element_type_id := get_element_for_category (p_absence_attendance_id);
964
965 hr_utility.set_location('l_element_type_id:'|| l_element_type_id, 10);
966
967 open c_get_absence_element(l_element_type_id);
968 fetch c_get_absence_element into p_element_entry_id,
969 p_processing_type,
970 p_effective_start_date,
971 p_effective_end_date;
972 close c_get_absence_element;
973
974
975 hr_utility.set_location('Leaving:'|| l_proc, 20);
976 exception
977 when others then
978 p_element_entry_id := null;
979 p_effective_start_date := null;
980 p_effective_end_date := null;
981
982 end get_absence_element;
983
984 procedure delete_absence_element
985 (p_dt_delete_mode in varchar2
986 ,p_session_date in date
987 ,p_element_entry_id in number
988 ) is
989
990
991 l_proc varchar2(72) := g_package||'delete_absence_element';
992 l_input_value_id number;
993 l_entry_value number;
994
995 begin
996
997
998 hr_utility.set_location('Entering:'|| l_proc, 10);
999
1000 hr_utility.set_location('p_session_date :'|| to_char(p_session_date), 20);
1001 hr_utility.set_location('p_element_entry_id :'|| p_element_entry_id, 30);
1002 hr_utility.set_location('p_dt_delete_mode :'|| p_dt_delete_mode, 30);
1003
1004 hr_entry_api.delete_element_entry
1005 (p_dt_delete_mode => p_dt_delete_mode
1006 ,p_session_date => p_session_date
1007 ,p_element_entry_id => p_element_entry_id);
1008
1009
1010 hr_utility.set_location('Leaving:'|| l_proc, 20);
1011
1012 end delete_absence_element;
1013
1014 procedure update_absence_element
1015 (p_dt_update_mode in varchar2
1016 ,p_assignment_id in number
1017 ,p_session_date in date
1018 ,p_element_entry_id in number
1019 ,p_absence_attendance_id in number
1020 ) is
1021
1022
1023 l_proc varchar2(72) := g_package||'update_absence_element';
1024 l_element_type_id number;
1025 l_input_value_id number;
1026 l_entry_value VARCHAR2(30);
1027 l_iv2_id NUMBER(15);
1028 l_iv2_value VARCHAR2(30);
1029 l_iv3_id NUMBER(15);
1030 l_iv3_value VARCHAR2(30);
1031 l_iv4_id NUMBER(15);
1032 l_iv4_value VARCHAR2(30);
1033 l_iv5_id NUMBER(15);
1034 l_iv5_value VARCHAR2(30);
1035 l_iv6_id NUMBER(15);
1036 l_iv6_value VARCHAR2(30);
1037 l_iv7_id NUMBER(15);
1038 l_iv7_value VARCHAR2(30);
1039 l_iv8_id NUMBER(15);
1040 l_iv8_value VARCHAR2(30);
1041 l_iv9_id NUMBER(15);
1042 l_iv9_value VARCHAR2(30);
1043 l_iv10_id NUMBER(15);
1044 l_iv10_value VARCHAR2(30);
1045 l_iv11_id NUMBER(15);
1046 l_iv11_value VARCHAR2(30);
1047 l_iv12_id NUMBER(15);
1048 l_iv12_value VARCHAR2(30);
1049 l_iv13_id NUMBER(15);
1050 l_iv13_value VARCHAR2(30);
1051 l_iv14_id NUMBER(15);
1052 l_iv14_value VARCHAR2(30);
1053 l_iv15_id NUMBER(15);
1054 l_iv15_value VARCHAR2(30);
1055 l_create_entry VARCHAR2(20);
1056 l_original_entry_id NUMBER ; --pgopal
1057 begin
1058 --hr_utility.trace_on(null,'GOV_ABS');
1059
1060 hr_utility.set_location('Entering:'|| l_proc, 10);
1061
1062 get_element_details(p_absence_attendance_id, p_assignment_id,
1063 l_element_type_id , l_create_entry
1064 ,l_original_entry_id --pgopal
1065 ,l_input_value_id, l_entry_value
1066 ,l_iv2_id, l_iv2_value
1067 ,l_iv3_id, l_iv3_value
1068 ,l_iv4_id, l_iv4_value
1069 ,l_iv5_id, l_iv5_value
1070 ,l_iv6_id, l_iv6_value
1071 ,l_iv7_id, l_iv7_value
1072 ,l_iv8_id, l_iv8_value
1073 ,l_iv9_id, l_iv9_value
1074 ,l_iv10_id, l_iv10_value
1075 ,l_iv11_id, l_iv11_value
1076 ,l_iv12_id, l_iv12_value
1077 ,l_iv13_id, l_iv13_value
1078 ,l_iv14_id, l_iv14_value
1079 ,l_iv15_id, l_iv15_value );
1080
1081
1082 hr_utility.set_location('Updating element', 20);
1083 hr_utility.set_location('Updating element create entry : '||l_create_entry, 20);
1084
1085 -- We know the assignment is eligible for this element because
1086 -- we have the element_link_id. The entries API will handle
1087 -- all other validation (e.g., non-recurring entries must
1088 -- have a valid payroll).
1089
1090 IF l_create_entry = 'Y' THEN
1091
1092 hr_utility.set_location('Input value id 1 : '||l_input_value_id, 20);
1093 hr_utility.set_location('Input value 1 : '||l_entry_value, 20);
1094
1095 hr_utility.set_location('Input value id 2 : '||l_iv2_id, 20);
1096 hr_utility.set_location('Input value 2 : '||l_iv2_value, 20);
1097
1098 hr_utility.set_location('Input value id 3 : '||l_iv3_id, 20);
1099 hr_utility.set_location('Input value 3 : '||l_iv3_value, 20);
1100
1101 hr_utility.set_location('Input value id 4 : '||l_iv4_id, 20);
1102 hr_utility.set_location('Input value 4 : '||l_iv4_value, 20);
1103
1104 hr_utility.set_location('Input value id 5 : '||l_iv5_id, 20);
1105 hr_utility.set_location('Input value 5 : '||l_iv5_value, 20);
1106
1107 hr_utility.set_location('Input value id 8 : '||l_iv8_id, 20);
1108 hr_utility.set_location('Input value 8 : '||l_iv8_value, 20);
1109
1110 hr_utility.set_location('Dt update mode : '||p_dt_update_mode, 20);
1111 hr_utility.set_location('p_session_date : '||p_session_date, 20);
1112
1113 hr_entry_api.update_element_entry
1114 (p_dt_update_mode => p_dt_update_mode
1115 ,p_session_date => p_session_date
1116 ,p_element_entry_id => p_element_entry_id
1117 ,p_creator_type => 'F'
1118 ,p_creator_id => p_absence_attendance_id
1119 ,p_original_entry_id => l_original_entry_id --pgopal
1120 ,p_input_value_id1 => l_input_value_id
1121 ,p_entry_value1 => l_entry_value
1122 ,p_input_value_id2 => l_iv2_id
1123 ,p_entry_value2 => l_iv2_value
1124 ,p_input_value_id3 => l_iv3_id
1125 ,p_entry_value3 => l_iv3_value
1126 ,p_input_value_id4 => l_iv4_id
1127 ,p_entry_value4 => l_iv4_value
1128 ,p_input_value_id5 => l_iv5_id
1129 ,p_entry_value5 => l_iv5_value
1130 ,p_input_value_id6 => l_iv6_id
1131 ,p_entry_value6 => l_iv6_value
1132 ,p_input_value_id7 => l_iv7_id
1133 ,p_entry_value7 => l_iv7_value
1134 ,p_input_value_id8 => l_iv8_id
1135 ,p_entry_value8 => l_iv8_value
1136 ,p_input_value_id9 => l_iv9_id
1137 ,p_entry_value9 => l_iv9_value
1138 ,p_input_value_id10 => l_iv10_id
1139 ,p_entry_value10 => l_iv10_value
1140 ,p_input_value_id11 => l_iv11_id
1141 ,p_entry_value11 => l_iv11_value
1142 ,p_input_value_id12 => l_iv12_id
1143 ,p_entry_value12 => l_iv12_value
1144 ,p_input_value_id13 => l_iv13_id
1145 ,p_entry_value13 => l_iv13_value
1146 ,p_input_value_id14 => l_iv14_id
1147 ,p_entry_value14 => l_iv14_value
1148 ,p_input_value_id15 => l_iv15_id
1149 ,p_entry_value15 => l_iv15_value);
1150 END IF;
1151
1152 hr_utility.set_location('Leaving:'|| l_proc, 30);
1153
1154 end update_absence_element;
1155
1156 procedure insert_absence_element
1157 (p_date_start in date
1158 ,p_assignment_id in number
1159 ,p_absence_attendance_id in number
1160 ,p_element_entry_id out nocopy number
1161 ) is
1162
1163
1164 l_proc varchar2(72) := g_package||'insert_absence_element';
1165 l_date_start date := p_date_start;
1166 l_date_end date;
1167 l_element_type_id number;
1168 l_element_link_id number;
1169 l_input_value_id number;
1170 l_entry_value VARCHAR2(30);
1171 l_iv2_id NUMBER(15);
1172 l_iv2_value VARCHAR2(30);
1173 l_iv3_id NUMBER(15);
1174 l_iv3_value VARCHAR2(30);
1175 l_iv4_id NUMBER(15);
1176 l_iv4_value VARCHAR2(30);
1177 l_iv5_id NUMBER(15);
1178 l_iv5_value VARCHAR2(30);
1179 l_iv6_id NUMBER(15);
1180 l_iv6_value VARCHAR2(30);
1181 l_iv7_id NUMBER(15);
1182 l_iv7_value VARCHAR2(30);
1183 l_iv8_id NUMBER(15);
1184 l_iv8_value VARCHAR2(30);
1185 l_iv9_id NUMBER(15);
1186 l_iv9_value VARCHAR2(30);
1187 l_iv10_id NUMBER(15);
1188 l_iv10_value VARCHAR2(30);
1189 l_iv11_id NUMBER(15);
1190 l_iv11_value VARCHAR2(30);
1191 l_iv12_id NUMBER(15);
1192 l_iv12_value VARCHAR2(30);
1193 l_iv13_id NUMBER(15);
1194 l_iv13_value VARCHAR2(30);
1195 l_iv14_id NUMBER(15);
1196 l_iv14_value VARCHAR2(30);
1197 l_iv15_id NUMBER(15);
1198 l_iv15_value VARCHAR2(30);
1199 l_create_entry VARCHAR2(20);
1200 l_original_entry_id NUMBER ; --pgopal
1201 begin
1202
1203
1204 hr_utility.set_location('Entering:'|| l_proc, 10);
1205
1206 get_element_details(p_absence_attendance_id, p_assignment_id,
1207 l_element_type_id , l_create_entry
1208 ,l_original_entry_id --pgopal
1209 ,l_input_value_id, l_entry_value
1210 ,l_iv2_id, l_iv2_value
1211 ,l_iv3_id, l_iv3_value
1212 ,l_iv4_id, l_iv4_value
1213 ,l_iv5_id, l_iv5_value
1214 ,l_iv6_id, l_iv6_value
1215 ,l_iv7_id, l_iv7_value
1216 ,l_iv8_id, l_iv8_value
1217 ,l_iv9_id, l_iv9_value
1218 ,l_iv10_id, l_iv10_value
1219 ,l_iv11_id, l_iv11_value
1220 ,l_iv12_id, l_iv12_value
1221 ,l_iv13_id, l_iv13_value
1222 ,l_iv14_id, l_iv14_value
1223 ,l_iv15_id, l_iv15_value );
1224
1225
1226 hr_utility.set_location('l_element_type_id: '||l_element_type_id, 30);
1227 hr_utility.set_location('Checking element link', 20);
1228
1229 IF l_create_entry = 'Y' THEN
1230 l_element_link_id := hr_entry_api.get_link
1231 (p_assignment_id => p_assignment_id
1232 ,p_element_type_id => l_element_type_id
1233 ,p_session_date => p_date_start);
1234
1235 If l_element_link_id is null then
1236 -- Assignment is not eligible for the element type
1237 -- associated with this absence.
1238 fnd_message.set_name ('PAY','HR_7448_ELE_PER_NOT_ELIGIBLE');
1239 hr_utility.raise_error;
1240 end if;
1241
1242
1243 hr_utility.set_location('Inserting element', 30);
1244
1245 -- We know the assignment is eligible for this element because
1246 -- we have the element_link_id. The entries API will handle
1247 -- all other validation (e.g., non-recurring entries must
1248 -- have a valid payroll).
1249
1250
1251 hr_entry_api.insert_element_entry
1252 (p_effective_start_date => l_date_start
1253 ,p_effective_end_date => l_date_end
1254 ,p_element_entry_id => p_element_entry_id
1255 ,p_assignment_id => p_assignment_id
1256 ,p_element_link_id => l_element_link_id
1257 ,p_creator_type => 'F'
1258 ,p_entry_type => 'E'
1259 ,p_creator_id => null--p_absence_attendance_id
1260 ,p_original_entry_id => l_original_entry_id --pgopal
1261 ,p_input_value_id1 => l_input_value_id
1262 ,p_entry_value1 => l_entry_value
1263 ,p_input_value_id2 => l_iv2_id
1264 ,p_entry_value2 => l_iv2_value
1265 ,p_input_value_id3 => l_iv3_id
1266 ,p_entry_value3 => l_iv3_value
1267 ,p_input_value_id4 => l_iv4_id
1268 ,p_entry_value4 => l_iv4_value
1269 ,p_input_value_id5 => l_iv5_id
1270 ,p_entry_value5 => l_iv5_value
1271 ,p_input_value_id6 => l_iv6_id
1272 ,p_entry_value6 => l_iv6_value
1273 ,p_input_value_id7 => l_iv7_id
1274 ,p_entry_value7 => l_iv7_value
1275 ,p_input_value_id8 => l_iv8_id
1276 ,p_entry_value8 => l_iv8_value
1277 ,p_input_value_id9 => l_iv9_id
1278 ,p_entry_value9 => l_iv9_value
1279 ,p_input_value_id10 => l_iv10_id
1280 ,p_entry_value10 => l_iv10_value
1281 ,p_input_value_id11 => l_iv11_id
1282 ,p_entry_value11 => l_iv11_value
1283 ,p_input_value_id12 => l_iv12_id
1284 ,p_entry_value12 => l_iv12_value
1285 ,p_input_value_id13 => l_iv13_id
1286 ,p_entry_value13 => l_iv13_value
1287 ,p_input_value_id14 => l_iv14_id
1288 ,p_entry_value14 => l_iv14_value
1289 ,p_input_value_id15 => l_iv15_id
1290 ,p_entry_value15 => l_iv15_value);
1291 END IF;
1292
1293 hr_utility.set_location('EE ID: '|| to_char(p_element_entry_id), 40);
1294 hr_utility.set_location('Leaving:'|| l_proc, 50);
1295
1296 exception
1297 when others then
1298 p_element_entry_id := null ;
1299 raise;
1300
1301 end insert_absence_element;
1302
1303 PROCEDURE update_absence(p_absence_attendance_id NUMBER,
1304 p_date_start DATE,
1305 p_date_end DATE,
1306 P_EFFECTIVE_DATE DATE) IS
1307 --
1308 --
1309 -- Local Cursors.
1310 --
1311 -- Find all assignments for the person as of a given date.
1312 -- Bug no 5020916. Order by clause included in the cursor
1313 CURSOR csr_assignments(p_person_id NUMBER, p_effective_date DATE) IS
1314 SELECT asg.assignment_id, asg.effective_start_date, asg.effective_end_date
1315 FROM per_all_assignments_f asg,
1316 per_absence_attendances paa
1317 WHERE paa.absence_attendance_id = p_absence_attendance_id
1318 and asg.person_id = paa.person_id
1319 and paa.person_id = p_person_id
1320 AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
1321 AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date
1322 ORDER BY asg.assignment_id, asg.effective_start_date, asg.effective_end_date;
1323 --
1324 -- Find all assignments for the person as of a given date.
1325 --
1326 CURSOR csr_entry_values(p_element_type_id NUMBER, p_iv1_name VARCHAR2, p_iv1_value VARCHAR2, p_effective_date DATE) IS
1327 SELECT iv.input_value_id input_value1
1328 ,p_iv1_value entry_value1
1329 FROM pay_input_values_f iv
1330 WHERE iv.element_type_id = p_element_type_id
1331 AND iv.name = p_iv1_name
1332 AND p_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date;
1333
1334 CURSOR csr_processing_type (p_element_type_id NUMBER) IS
1335 SELECT processing_type
1336 FROM pay_element_types_f
1337 where element_type_id= p_element_type_id
1338 AND p_effective_date between effective_start_date and effective_end_date;
1339
1340
1341 CURSOR csr_invalid_entries (p_element_type_id NUMBER) IS
1342 SELECT pee.element_entry_id, pee.effective_start_date
1343 FROM pay_element_entries_f pee,
1344 per_all_assignments_f asg1,
1345 per_absence_attendances paa1
1346 where pee.element_type_id = p_element_type_id
1347 AND paa1.absence_attendance_id = p_absence_attendance_id
1348 AND asg1.person_id = paa1.person_id
1349 AND asg1.assignment_id = pee.assignment_id --9971535
1350 AND pee.effective_start_date between asg1.effective_start_date AND asg1.effective_end_date --9971535
1351 AND asg1.assignment_id not in (
1352 SELECT asg.assignment_id
1353 FROM per_all_assignments_f asg,
1354 per_absence_attendances paa
1355 WHERE paa.absence_attendance_id = p_absence_attendance_id
1356 AND asg.person_id = paa.person_id
1357 AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
1358 AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date);
1359 --
1360 --
1361 -- Local Variables.
1362 --
1363
1364 cursor c_get_absence_details is
1365 select abs.person_id,
1366 abs.absence_attendance_type_id
1367 from per_absence_attendances abs
1368 where abs.absence_attendance_id = p_absence_attendance_id;
1369
1370 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1371 l_processing_type VARCHAR2(30);
1372 l_person_id NUMBER(15);
1373 l_assignment_id csr_assignments%rowtype;
1374 l_effective_start_date DATE;
1375 l_effective_end_date DATE;
1376 l_absence_attendance_type_id NUMBER(15);
1377 l_element_link_id NUMBER;
1378 l_date_start DATE;
1379 l_date_end DATE;
1380 l_element_entry_id NUMBER;
1381 l_iv1_name VARCHAR2(30);
1382 l_iv1_value VARCHAR2(30);
1383 l_del_element_entry_warning BOOLEAN;
1384 l_proc varchar2(72) := g_package||'update_person_absence';
1385 -- Bug no 5020916. Local variable declared to keep assignment id
1386 l_old_assignment NUMBER(15);
1387 BEGIN
1388 --
1389 -- Added for GSI Bug 5472781
1390 ---pgopal -Included 'NO','SE','PL' in the legislation installation check.
1391 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') OR
1392 hr_utility.chk_product_install('Oracle Human Resources', 'NO') OR
1393 hr_utility.chk_product_install('Oracle Human Resources', 'SE') OR
1394 hr_utility.chk_product_install('Oracle Human Resources', 'PL') OR
1395 hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
1396 --
1397 -- Bug no 5020916. Initialize local variable l_old_assignment as 0
1398 l_old_assignment := 0;
1399
1400 /* Start of Absence Element Entry Section */
1401 --
1402 -- Update or insert the absence element element. First we
1403 -- check if the absence type is linked to an element type.
1404 --
1405 hr_utility.set_location('Entering:'|| l_proc, 10);
1406
1407 /* Level 1 */
1408 -- commented till we decide where we are going to store element type and category link.
1409 /* if linked_to_element
1410 (p_absence_attendance_id => p_absence_attendance_id)
1411 then*/
1412
1413 --
1414 -- Get the person_id, assignment_id, assignment_type_id
1415 -- and processing type for use later
1416 --
1417
1418 open c_get_absence_details;
1419 fetch c_get_absence_details into l_person_id,
1420 l_absence_attendance_type_id;
1421 close c_get_absence_details;
1422
1423 hr_utility.set_location('After c_get_absence_details ', 20);
1424 --
1425 -- We determine if an entry already exists.
1426 --*/
1427 for l_assignment_id in csr_assignments(l_person_id, p_effective_date) loop
1428
1429
1430 -- Bug no 5020916. Commenting assignment date check while attaching absences to assignments.
1431 -- IF p_date_start > l_assignment_id.effective_start_date THEN
1432 l_date_start := p_date_start;
1433 -- ELSE
1434 -- l_date_start := l_assignment_id.effective_start_date;
1435 -- END IF;
1436
1437 -- IF p_date_end < l_assignment_id.effective_end_date THEN
1438 l_date_end := p_date_end;
1439 -- ELSE
1440 -- l_date_end := l_assignment_id.effective_end_date;
1441 -- END IF;
1442
1443 get_absence_element
1444 (p_absence_attendance_id => p_absence_attendance_id
1445 ,p_assignment_id => l_assignment_id.assignment_id
1446 ,p_effective_date => p_effective_date
1447 ,p_processing_type => l_processing_type
1448 ,p_element_entry_id => l_element_entry_id
1449 ,p_effective_start_date => l_effective_start_date
1450 ,p_effective_end_date => l_effective_end_date);
1451
1452 hr_utility.set_location('l_person_id: ' || l_person_id , 20);
1453 hr_utility.set_location('p_effective_date: ' || p_effective_date , 20);
1454 hr_utility.set_location('l_element_entry_id: ' || l_element_entry_id , 100);
1455
1456
1457 /* Level 2 */
1458 if l_element_entry_id is null then
1459 --
1460 -- Scenario 1.
1461 -- An entry does not already exist. Insert if we have
1462 -- the appropriate dates.
1463 --
1464 hr_utility.set_location('Scenario 1', 45);
1465
1466 l_element_type_id := get_element_for_category(p_absence_attendance_id);
1467
1468 OPEN csr_processing_type (l_element_type_id );
1469 FETCH csr_processing_type INTO l_processing_type;
1470 CLOSE csr_processing_type;
1471
1472
1473
1474 hr_utility.set_location('l_processing_type: ' || l_processing_type , 20);
1475 hr_utility.set_location('l_date_start: ' || l_date_start , 20);
1476 hr_utility.set_location('l_assignment_id.assignment_id: ' || l_assignment_id.assignment_id , 100);
1477 hr_utility.set_location('l_element_type_id: ' || l_element_type_id , 100);
1478
1479 if (l_processing_type = 'N'
1480 and l_date_start is not null
1481 and l_date_end is not null)
1482 or (l_processing_type = 'R'
1483 and l_date_start is not null)
1484 then
1485 -- Bug no 5020916. Restricting the for loop to process one time for each assignment.
1486 IF l_old_assignment <> l_assignment_id.assignment_id THEN
1487 l_old_assignment := l_assignment_id.assignment_id;
1488 insert_absence_element
1489 (p_date_start => l_date_start
1490 ,p_assignment_id => l_assignment_id.assignment_id
1491 ,p_absence_attendance_id => p_absence_attendance_id
1492 ,p_element_entry_id => l_element_entry_id);
1493 END IF;
1494 if l_processing_type = 'R' and p_date_end is not null and l_element_entry_id is not null then
1495 --
1496 -- Scenario 2.
1497 -- If this is a recurring element entry and we have the
1498 -- absence end date, we date effectively delete the
1499 -- element immediately, otherwise it remains open until
1500 -- the end of time.
1501 --
1502 hr_utility.set_location('Scenario 2', 50);
1503
1504 delete_absence_element
1505 (p_dt_delete_mode => 'DELETE'
1506 ,p_session_date => l_date_end
1507 ,p_element_entry_id => l_element_entry_id);
1508 end if;
1509
1510 end if;
1511
1512 else
1513 --
1514 -- An entry already exists. Update it as appropriate.
1515 --
1516 /* Level 3 */
1517 hr_utility.set_location('element_entry id is not null ', 30);
1518
1519 if (l_processing_type = 'R' and l_date_start is null)
1520 or (l_processing_type = 'N' and (l_date_start is null
1521 or l_date_end is null)) then
1522 --
1523 -- Scenario 3.
1524 -- The element entry should be purged because the
1525 -- actual dates have been removed.
1526 --
1527 hr_utility.set_location('Scenario 3', 55);
1528
1529 --
1530 -- Warn the user before deleting.
1531 --
1532 l_del_element_entry_warning := TRUE;
1533
1534 hr_utility.set_location('before delete absence element ', 40);
1535 delete_absence_element
1536 (p_dt_delete_mode => 'ZAP'
1537 ,p_session_date => l_effective_start_date
1538 ,p_element_entry_id => l_element_entry_id);
1539
1540 elsif l_processing_type = 'N' and l_date_start not between
1541 l_effective_start_date and l_effective_end_date then
1542 --
1543 -- Scenario 4.
1544 -- The start date cannot be moved outside the entry's
1545 -- current period for non-recurring entries.
1546 --
1547 hr_utility.set_location('Scenario 4', 60);
1548
1549 fnd_message.set_name ('PAY', 'HR_6744_ABS_DET_ENTRY_PERIOD');
1550 /* Commented and added as a part of Bug#10239011 Starts
1551 fnd_message.set_token ('PERIOD_FROM',
1552 fnd_date.date_to_chardate(l_effective_start_date));
1553 fnd_message.set_token ('PERIOD_TO',
1554 fnd_date.date_to_chardate(l_effective_end_date)); */
1555 fnd_message.set_token ('PERIOD_FROM',
1556 fnd_date.date_to_chardate(l_effective_start_date, calendar_aware => FND_DATE.calendar_aware_alt));
1557 fnd_message.set_token ('PERIOD_TO',
1558 fnd_date.date_to_chardate(l_effective_end_date, calendar_aware => FND_DATE.calendar_aware_alt));
1559 /* Commented and added as a part of Bug#10239011 Ends */
1560 fnd_message.raise_error;
1561
1562 elsif l_processing_type = 'N' then
1563 --
1564 -- Scenario 5.
1565 -- Update the existing entry with the new input values.
1566 -- For simplicity, we make the update even if the value
1567 -- has not changed.
1568 --
1569 hr_utility.set_location('Scenario 5', 65);
1570
1571 update_absence_element
1572 (p_dt_update_mode => 'CORRECTION'
1573 ,p_assignment_id => l_assignment_id.assignment_id
1574 ,p_session_date => l_effective_start_date
1575 ,p_element_entry_id => l_element_entry_id
1576 ,p_absence_attendance_id => p_absence_attendance_id);
1577
1578 elsif l_processing_type = 'R'
1579 and l_date_start <> l_effective_start_date then
1580
1581 --
1582 -- Scenario 6.
1583 -- The start date has been moved. As this is part of the
1584 -- primary key we must delete the entry and re-insert it.
1585 --
1586 hr_utility.set_location('Scenario 6', 70);
1587
1588 delete_absence_element
1589 (p_dt_delete_mode => 'ZAP'
1590 ,p_session_date => l_effective_start_date
1591 ,p_element_entry_id => l_element_entry_id);
1592
1593 insert_absence_element
1594 (p_date_start => l_date_start
1595 ,p_assignment_id => l_assignment_id.assignment_id
1596 ,p_absence_attendance_id => p_absence_attendance_id
1597 ,p_element_entry_id => l_element_entry_id);
1598
1599 if p_date_end is not null then
1600 --
1601 -- We have the absence end date, we date effectively
1602 -- delete the element immediately, otherwise it
1603 -- remains open until the end of time.
1604 --
1605
1606 delete_absence_element
1607 (p_dt_delete_mode => 'DELETE'
1608 ,p_session_date => l_date_end
1609 ,p_element_entry_id => l_element_entry_id);
1610 end if;
1611
1612 elsif l_processing_type = 'R' and
1613 (l_date_end is null or
1614 l_date_end <> l_effective_end_date) then
1615 --
1616 -- Scenario 7.
1617 -- The end date has:
1618 -- . changed
1619 -- . been removed
1620 -- . entered for the first time
1621 -- . still not been entered.
1622 --
1623 hr_utility.set_location('Scenario 7', 75);
1624 hr_utility.set_location('l_date_end: '|| l_date_end, 75);
1625 hr_utility.set_location('l_effective_end_date: '|| l_effective_end_date, 75);
1626 hr_utility.set_location('l_element_entry_id: '|| l_element_entry_id, 75);
1627
1628 update_absence_element
1629 (p_dt_update_mode => 'CORRECTION'
1630 ,p_assignment_id => l_assignment_id.assignment_id
1631 ,p_session_date => l_effective_start_date
1632 ,p_element_entry_id => l_element_entry_id
1633 ,p_absence_attendance_id => p_absence_attendance_id);
1634
1635 if l_effective_end_date <> hr_api.g_eot then
1636 --
1637 -- End date has been changed or removed so we
1638 -- remove the end date so it continues through
1639 -- until the end of time.
1640 --
1641 hr_utility.set_location(l_proc, 76);
1642 if l_effective_end_date <> l_assignment_id.effective_end_date then
1643 delete_absence_element
1644 (p_dt_delete_mode => 'DELETE_NEXT_CHANGE'
1645 ,p_session_date => l_effective_end_date
1646 ,p_element_entry_id => l_element_entry_id);
1647 end if;
1648 end if;
1649
1650 if l_date_end is not null then
1651 --
1652 -- End date has been changed or entered for
1653 -- the first time. We end the element entry
1654 -- at the end date.
1655 --
1656 hr_utility.set_location(l_proc, 78);
1657
1658 delete_absence_element
1659 (p_dt_delete_mode => 'DELETE'
1660 ,p_session_date => l_date_end
1661 ,p_element_entry_id => l_element_entry_id);
1662
1663
1664 end if;
1665
1666 elsif l_processing_type = 'R' THEN
1667 --
1668 -- Scenario 8.
1669 -- Update the existing entry with the new input values.
1670 -- For simplicity, we make the update even if the value
1671 -- has not changed.
1672 --
1673 hr_utility.set_location('Scenario 8', 65);
1674
1675 update_absence_element
1676 (p_dt_update_mode => 'CORRECTION'
1677 ,p_assignment_id => l_assignment_id.assignment_id
1678 ,p_session_date => l_effective_start_date
1679 ,p_element_entry_id => l_element_entry_id
1680 ,p_absence_attendance_id => p_absence_attendance_id);
1681 /* Level 3 */
1682 end if;
1683
1684 /* Level 2 */
1685 end if;
1686 END LOOP;
1687
1688 /* Level 1 */
1689 -- end if;
1690
1691 /*logic to delete the element entries which are not valid due to change in absence dates*/
1692
1693 l_element_type_id := get_element_for_category (p_absence_attendance_id );
1694
1695 for l_invalid_entries in csr_invalid_entries(l_element_type_id) LOOP
1696 if l_invalid_entries.element_entry_id is not null then
1697 delete_absence_element
1698 (p_dt_delete_mode => 'ZAP'
1699 ,p_session_date => l_invalid_entries.effective_start_date
1700 ,p_element_entry_id => l_invalid_entries.element_entry_id);
1701 end if;
1702 END LOOP;
1703 END IF;
1704
1705 end update_absence;
1706
1707 procedure delete_absence
1708 (p_absence_attendance_id in number
1709 ) is
1710
1711
1712 CURSOR csr_assignments IS
1713 SELECT asg.assignment_id, asg.effective_start_date
1714 FROM per_all_assignments_f asg,
1715 per_absence_attendances paa
1716 WHERE paa.absence_attendance_id = p_absence_attendance_id
1717 and asg.person_id = paa.person_id
1718 AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
1719 AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date;
1720
1721 /*Pgopal - ADS Bug 5523013 fix, Converting abs.absence_attendance_id to char*/
1722 cursor csr_get_absence_element(p_assignment_id NUMBER, p_element_type_id NUMBER) is
1723 select pee.element_entry_id element_entry_id
1724 ,pet.processing_type processing_type
1725 ,pee.effective_start_date effective_start_date
1726 ,pee.effective_end_date effective_end_date
1727 from per_absence_attendances abs
1728 ,per_all_assignments_f asg
1729 ,pay_element_types_f pet
1730 ,pay_element_links_f pel
1731 ,pay_element_entries_f pee
1732 ,pay_element_entry_values_f peev
1733 ,pay_input_values_f piv
1734 where abs.absence_attendance_id = p_absence_attendance_id
1735 and abs.person_id = asg.person_id
1736 and asg.assignment_id = p_assignment_id
1737 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
1738 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
1739 and pet.element_type_id = p_element_type_id
1740 and nvl(abs.date_start,pet.effective_end_date) <= pet.effective_end_date
1741 and nvl(abs.date_end,pet.effective_start_date) >= pet.effective_start_date
1742 and pet.element_type_id = pel.element_type_id
1743 and pel.element_link_id = pee.element_link_id
1744 and pee.assignment_id = asg.assignment_id
1745 and pee.creator_type = 'F'
1746 and pet.element_type_id = piv.element_type_id
1747 and nvl(abs.date_start,piv.effective_end_date) <= piv.effective_end_date
1748 and nvl(abs.date_end,piv.effective_start_date) >= piv.effective_start_date
1749 and piv.name = 'CREATOR_ID'
1750 and peev.element_entry_id = pee.element_entry_id
1751 and peev.input_value_id=piv.input_value_id
1752 and peev.screen_entry_value = to_char(abs.absence_attendance_id);
1753 --and peev.screen_entry_value = abs.absence_attendance_id;
1754
1755
1756
1757 /* select pee.element_entry_id element_entry_id
1758 ,pet.processing_type processing_type
1759 ,pee.effective_start_date effective_start_date
1760 ,pee.effective_end_date effective_end_date
1761 from per_absence_attendances abs
1762 ,per_all_assignments_f asg
1763 ,pay_element_types_f pet
1764 ,pay_element_links_f pel
1765 ,pay_element_entries_f pee
1766 where abs.absence_attendance_id = p_absence_attendance_id
1767 and abs.person_id = asg.person_id
1768 and asg.assignment_id = p_assignment_id
1769 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
1770 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
1771 and pet.element_type_id = p_element_type_id
1772 and nvl(abs.date_start,pet.effective_start_date) >= pet.effective_start_date
1773 and nvl(abs.date_end,pet.effective_end_date) <= pet.effective_end_date
1774 and pet.element_type_id = pel.element_type_id
1775 and pel.element_link_id = pee.element_link_id
1776 and pee.assignment_id = asg.assignment_id
1777 and pee.creator_id = abs.absence_attendance_id
1778 and pee.creator_type = 'F';
1779 */
1780
1781
1782 rec_assignments csr_assignments%rowtype;
1783 rec_absence_element csr_get_absence_element%rowtype;
1784 l_element_type_id NUMBER(15);
1785
1786 l_flag varchar2(1);
1787 BEGIN
1788 --
1789 -- Added for GSI Bug 5472781
1790 ---pgopal -Included 'NO','SE','PL' in the legislation installation check.
1791 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') OR
1792 hr_utility.chk_product_install('Oracle Human Resources', 'NO') OR
1793 hr_utility.chk_product_install('Oracle Human Resources', 'SE') OR
1794 hr_utility.chk_product_install('Oracle Human Resources', 'PL') OR
1795 hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
1796 --
1797 hr_utility.set_location('Start of absence element deletion section', 30);
1798 --
1799 FOR rec_assignments in csr_assignments LOOP
1800 --
1801 hr_utility.set_location(' in for loop ', 35);
1802 --
1803 l_element_type_id := get_element_for_category (p_absence_attendance_id );
1804 --
1805 hr_utility.set_location('l_element_type_id '|| l_element_type_id, 35);
1806 --
1807 l_flag := 'N';
1808 --
1809 open csr_get_absence_element(rec_assignments.assignment_id,l_element_type_id );
1810 fetch csr_get_absence_element into rec_absence_element;
1811 if csr_get_absence_element%notfound then
1812 l_flag := 'Y';
1813 end if;
1814 close csr_get_absence_element;
1815 --
1816 hr_utility.set_location('rec_assignments.assignment_id '|| rec_assignments.assignment_id, 35);
1817 hr_utility.set_location('rec_absence_element.element_entry_id '|| rec_absence_element.element_entry_id, 35);
1818 if l_flag <> 'Y' then
1819 if rec_absence_element.element_entry_id is not null then
1820 delete_absence_element
1821 (p_dt_delete_mode => 'ZAP'
1822 ,p_session_date => rec_absence_element.effective_start_date
1823 ,p_element_entry_id => rec_absence_element.element_entry_id);
1824 end if;
1825 end if;
1826 --
1827 hr_utility.set_location('End of absence element deletion section', 40);
1828 --
1829 END LOOP;
1830 END IF;
1831 END delete_absence;
1832
1833 END hr_loc_absence;