[Home] [Help]
PACKAGE BODY: APPS.HR_LOC_ABSENCE
Source
1 PACKAGE BODY hr_loc_absence AS
2 /* $Header: hrabsloc.pkb 120.5.12000000.3 2007/05/03 08:53:28 pdavidra 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 = abs.absence_attendance_id;
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 not in (
1350 SELECT asg.assignment_id
1351 FROM per_all_assignments_f asg,
1352 per_absence_attendances paa
1353 WHERE paa.absence_attendance_id = p_absence_attendance_id
1354 AND asg.person_id = paa.person_id
1355 AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
1356 AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date);
1357 --
1358 --
1359 -- Local Variables.
1360 --
1361
1362 cursor c_get_absence_details is
1363 select abs.person_id,
1364 abs.absence_attendance_type_id
1365 from per_absence_attendances abs
1366 where abs.absence_attendance_id = p_absence_attendance_id;
1367
1368 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1369 l_processing_type VARCHAR2(30);
1370 l_person_id NUMBER(15);
1371 l_assignment_id csr_assignments%rowtype;
1372 l_effective_start_date DATE;
1373 l_effective_end_date DATE;
1374 l_absence_attendance_type_id NUMBER(15);
1375 l_element_link_id NUMBER;
1376 l_date_start DATE;
1377 l_date_end DATE;
1378 l_element_entry_id NUMBER;
1379 l_iv1_name VARCHAR2(30);
1380 l_iv1_value VARCHAR2(30);
1381 l_del_element_entry_warning BOOLEAN;
1382 l_proc varchar2(72) := g_package||'update_person_absence';
1383 -- Bug no 5020916. Local variable declared to keep assignment id
1384 l_old_assignment NUMBER(15);
1385 BEGIN
1386 --
1387 -- Added for GSI Bug 5472781
1388 ---pgopal -Included 'NO','SE','PL' in the legislation installation check.
1389 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') OR
1390 hr_utility.chk_product_install('Oracle Human Resources', 'NO') OR
1391 hr_utility.chk_product_install('Oracle Human Resources', 'SE') OR
1392 hr_utility.chk_product_install('Oracle Human Resources', 'PL') OR
1393 hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
1394 --
1395 -- Bug no 5020916. Initialize local variable l_old_assignment as 0
1396 l_old_assignment := 0;
1397
1398 /* Start of Absence Element Entry Section */
1399 --
1400 -- Update or insert the absence element element. First we
1401 -- check if the absence type is linked to an element type.
1402 --
1403 hr_utility.set_location('Entering:'|| l_proc, 10);
1404
1405 /* Level 1 */
1406 -- commented till we decide where we are going to store element type and category link.
1407 /* if linked_to_element
1408 (p_absence_attendance_id => p_absence_attendance_id)
1409 then*/
1410
1411 --
1412 -- Get the person_id, assignment_id, assignment_type_id
1413 -- and processing type for use later
1414 --
1415
1416 open c_get_absence_details;
1417 fetch c_get_absence_details into l_person_id,
1418 l_absence_attendance_type_id;
1419 close c_get_absence_details;
1420
1421 hr_utility.set_location('After c_get_absence_details ', 20);
1422 --
1423 -- We determine if an entry already exists.
1424 --*/
1425 for l_assignment_id in csr_assignments(l_person_id, p_effective_date) loop
1426
1427
1428 -- Bug no 5020916. Commenting assignment date check while attaching absences to assignments.
1429 -- IF p_date_start > l_assignment_id.effective_start_date THEN
1430 l_date_start := p_date_start;
1431 -- ELSE
1432 -- l_date_start := l_assignment_id.effective_start_date;
1433 -- END IF;
1434
1435 -- IF p_date_end < l_assignment_id.effective_end_date THEN
1436 l_date_end := p_date_end;
1437 -- ELSE
1438 -- l_date_end := l_assignment_id.effective_end_date;
1439 -- END IF;
1440
1441 get_absence_element
1442 (p_absence_attendance_id => p_absence_attendance_id
1443 ,p_assignment_id => l_assignment_id.assignment_id
1444 ,p_effective_date => p_effective_date
1445 ,p_processing_type => l_processing_type
1446 ,p_element_entry_id => l_element_entry_id
1447 ,p_effective_start_date => l_effective_start_date
1448 ,p_effective_end_date => l_effective_end_date);
1449
1450 hr_utility.set_location('l_person_id: ' || l_person_id , 20);
1451 hr_utility.set_location('p_effective_date: ' || p_effective_date , 20);
1452 hr_utility.set_location('l_element_entry_id: ' || l_element_entry_id , 100);
1453
1454
1455 /* Level 2 */
1456 if l_element_entry_id is null then
1457 --
1458 -- Scenario 1.
1459 -- An entry does not already exist. Insert if we have
1460 -- the appropriate dates.
1461 --
1462 hr_utility.set_location('Scenario 1', 45);
1463
1464 l_element_type_id := get_element_for_category(p_absence_attendance_id);
1465
1466 OPEN csr_processing_type (l_element_type_id );
1467 FETCH csr_processing_type INTO l_processing_type;
1468 CLOSE csr_processing_type;
1469
1470
1471
1472 hr_utility.set_location('l_processing_type: ' || l_processing_type , 20);
1473 hr_utility.set_location('l_date_start: ' || l_date_start , 20);
1474 hr_utility.set_location('l_assignment_id.assignment_id: ' || l_assignment_id.assignment_id , 100);
1475 hr_utility.set_location('l_element_type_id: ' || l_element_type_id , 100);
1476
1477 if (l_processing_type = 'N'
1478 and l_date_start is not null
1479 and l_date_end is not null)
1480 or (l_processing_type = 'R'
1481 and l_date_start is not null)
1482 then
1483 -- Bug no 5020916. Restricting the for loop to process one time for each assignment.
1484 IF l_old_assignment <> l_assignment_id.assignment_id THEN
1485 l_old_assignment := l_assignment_id.assignment_id;
1486 insert_absence_element
1487 (p_date_start => l_date_start
1488 ,p_assignment_id => l_assignment_id.assignment_id
1489 ,p_absence_attendance_id => p_absence_attendance_id
1490 ,p_element_entry_id => l_element_entry_id);
1491 END IF;
1492 if l_processing_type = 'R' and p_date_end is not null and l_element_entry_id is not null then
1493 --
1494 -- Scenario 2.
1495 -- If this is a recurring element entry and we have the
1496 -- absence end date, we date effectively delete the
1497 -- element immediately, otherwise it remains open until
1498 -- the end of time.
1499 --
1500 hr_utility.set_location('Scenario 2', 50);
1501
1502 delete_absence_element
1503 (p_dt_delete_mode => 'DELETE'
1504 ,p_session_date => l_date_end
1505 ,p_element_entry_id => l_element_entry_id);
1506 end if;
1507
1508 end if;
1509
1510 else
1511 --
1512 -- An entry already exists. Update it as appropriate.
1513 --
1514 /* Level 3 */
1515 hr_utility.set_location('element_entry id is not null ', 30);
1516
1517 if (l_processing_type = 'R' and l_date_start is null)
1518 or (l_processing_type = 'N' and (l_date_start is null
1519 or l_date_end is null)) then
1520 --
1521 -- Scenario 3.
1522 -- The element entry should be purged because the
1523 -- actual dates have been removed.
1524 --
1525 hr_utility.set_location('Scenario 3', 55);
1526
1527 --
1528 -- Warn the user before deleting.
1529 --
1530 l_del_element_entry_warning := TRUE;
1531
1532 hr_utility.set_location('before delete absence element ', 40);
1533 delete_absence_element
1534 (p_dt_delete_mode => 'ZAP'
1535 ,p_session_date => l_effective_start_date
1536 ,p_element_entry_id => l_element_entry_id);
1537
1538 elsif l_processing_type = 'N' and l_date_start not between
1539 l_effective_start_date and l_effective_end_date then
1540 --
1541 -- Scenario 4.
1542 -- The start date cannot be moved outside the entry's
1543 -- current period for non-recurring entries.
1544 --
1545 hr_utility.set_location('Scenario 4', 60);
1546
1547 fnd_message.set_name ('PAY', 'HR_6744_ABS_DET_ENTRY_PERIOD');
1548 fnd_message.set_token ('PERIOD_FROM',
1549 fnd_date.date_to_chardate(l_effective_start_date));
1550 fnd_message.set_token ('PERIOD_TO',
1551 fnd_date.date_to_chardate(l_effective_end_date));
1552 fnd_message.raise_error;
1553
1554 elsif l_processing_type = 'N' then
1555 --
1556 -- Scenario 5.
1557 -- Update the existing entry with the new input values.
1558 -- For simplicity, we make the update even if the value
1559 -- has not changed.
1560 --
1561 hr_utility.set_location('Scenario 5', 65);
1562
1563 update_absence_element
1564 (p_dt_update_mode => 'CORRECTION'
1565 ,p_assignment_id => l_assignment_id.assignment_id
1566 ,p_session_date => l_effective_start_date
1567 ,p_element_entry_id => l_element_entry_id
1568 ,p_absence_attendance_id => p_absence_attendance_id);
1569
1570 elsif l_processing_type = 'R'
1571 and l_date_start <> l_effective_start_date then
1572
1573 --
1574 -- Scenario 6.
1575 -- The start date has been moved. As this is part of the
1576 -- primary key we must delete the entry and re-insert it.
1577 --
1578 hr_utility.set_location('Scenario 6', 70);
1579
1580 delete_absence_element
1581 (p_dt_delete_mode => 'ZAP'
1582 ,p_session_date => l_effective_start_date
1583 ,p_element_entry_id => l_element_entry_id);
1584
1585 insert_absence_element
1586 (p_date_start => l_date_start
1587 ,p_assignment_id => l_assignment_id.assignment_id
1588 ,p_absence_attendance_id => p_absence_attendance_id
1589 ,p_element_entry_id => l_element_entry_id);
1590
1591 if p_date_end is not null then
1592 --
1593 -- We have the absence end date, we date effectively
1594 -- delete the element immediately, otherwise it
1595 -- remains open until the end of time.
1596 --
1597
1598 delete_absence_element
1599 (p_dt_delete_mode => 'DELETE'
1600 ,p_session_date => l_date_end
1601 ,p_element_entry_id => l_element_entry_id);
1602 end if;
1603
1604 elsif l_processing_type = 'R' and
1605 (l_date_end is null or
1606 l_date_end <> l_effective_end_date) then
1607 --
1608 -- Scenario 7.
1609 -- The end date has:
1610 -- . changed
1611 -- . been removed
1612 -- . entered for the first time
1613 -- . still not been entered.
1614 --
1615 hr_utility.set_location('Scenario 7', 75);
1616 hr_utility.set_location('l_date_end: '|| l_date_end, 75);
1617 hr_utility.set_location('l_effective_end_date: '|| l_effective_end_date, 75);
1618 hr_utility.set_location('l_element_entry_id: '|| l_element_entry_id, 75);
1619
1620 update_absence_element
1621 (p_dt_update_mode => 'CORRECTION'
1622 ,p_assignment_id => l_assignment_id.assignment_id
1623 ,p_session_date => l_effective_start_date
1624 ,p_element_entry_id => l_element_entry_id
1625 ,p_absence_attendance_id => p_absence_attendance_id);
1626
1627 if l_effective_end_date <> hr_api.g_eot then
1628 --
1629 -- End date has been changed or removed so we
1630 -- remove the end date so it continues through
1631 -- until the end of time.
1632 --
1633 hr_utility.set_location(l_proc, 76);
1634 if l_effective_end_date <> l_assignment_id.effective_end_date then
1635 delete_absence_element
1636 (p_dt_delete_mode => 'DELETE_NEXT_CHANGE'
1637 ,p_session_date => l_effective_end_date
1638 ,p_element_entry_id => l_element_entry_id);
1639 end if;
1640 end if;
1641
1642 if l_date_end is not null then
1643 --
1644 -- End date has been changed or entered for
1645 -- the first time. We end the element entry
1646 -- at the end date.
1647 --
1648 hr_utility.set_location(l_proc, 78);
1649
1650 delete_absence_element
1651 (p_dt_delete_mode => 'DELETE'
1652 ,p_session_date => l_date_end
1653 ,p_element_entry_id => l_element_entry_id);
1654
1655
1656 end if;
1657
1658 elsif l_processing_type = 'R' THEN
1659 --
1660 -- Scenario 8.
1661 -- Update the existing entry with the new input values.
1662 -- For simplicity, we make the update even if the value
1663 -- has not changed.
1664 --
1665 hr_utility.set_location('Scenario 8', 65);
1666
1667 update_absence_element
1668 (p_dt_update_mode => 'CORRECTION'
1669 ,p_assignment_id => l_assignment_id.assignment_id
1670 ,p_session_date => l_effective_start_date
1671 ,p_element_entry_id => l_element_entry_id
1672 ,p_absence_attendance_id => p_absence_attendance_id);
1673 /* Level 3 */
1674 end if;
1675
1676 /* Level 2 */
1677 end if;
1678 END LOOP;
1679
1680 /* Level 1 */
1681 -- end if;
1682
1683 /*logic to delete the element entries which are not valid due to change in absence dates*/
1684
1685 l_element_type_id := get_element_for_category (p_absence_attendance_id );
1686
1687 for l_invalid_entries in csr_invalid_entries(l_element_type_id) LOOP
1688 if l_invalid_entries.element_entry_id is not null then
1689 delete_absence_element
1690 (p_dt_delete_mode => 'ZAP'
1691 ,p_session_date => l_invalid_entries.effective_start_date
1692 ,p_element_entry_id => l_invalid_entries.element_entry_id);
1693 end if;
1694 END LOOP;
1695 END IF;
1696
1697 end update_absence;
1698
1699 procedure delete_absence
1700 (p_absence_attendance_id in number
1701 ) is
1702
1703
1704 CURSOR csr_assignments IS
1705 SELECT asg.assignment_id, asg.effective_start_date
1706 FROM per_all_assignments_f asg,
1707 per_absence_attendances paa
1708 WHERE paa.absence_attendance_id = p_absence_attendance_id
1709 and asg.person_id = paa.person_id
1710 AND nvl(paa.date_start,asg.effective_end_date) <= asg.effective_end_date
1711 AND nvl(paa.date_end,asg.effective_start_date) >= asg.effective_start_date;
1712
1713 /*Pgopal - ADS Bug 5523013 fix, Converting abs.absence_attendance_id to char*/
1714 cursor csr_get_absence_element(p_assignment_id NUMBER, p_element_type_id NUMBER) is
1715 select pee.element_entry_id element_entry_id
1716 ,pet.processing_type processing_type
1717 ,pee.effective_start_date effective_start_date
1718 ,pee.effective_end_date effective_end_date
1719 from per_absence_attendances abs
1720 ,per_all_assignments_f asg
1721 ,pay_element_types_f pet
1722 ,pay_element_links_f pel
1723 ,pay_element_entries_f pee
1724 ,pay_element_entry_values_f peev
1725 ,pay_input_values_f piv
1726 where abs.absence_attendance_id = p_absence_attendance_id
1727 and abs.person_id = asg.person_id
1728 and asg.assignment_id = p_assignment_id
1729 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
1730 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
1731 and pet.element_type_id = p_element_type_id
1732 and nvl(abs.date_start,pet.effective_end_date) <= pet.effective_end_date
1733 and nvl(abs.date_end,pet.effective_start_date) >= pet.effective_start_date
1734 and pet.element_type_id = pel.element_type_id
1735 and pel.element_link_id = pee.element_link_id
1736 and pee.assignment_id = asg.assignment_id
1737 and pee.creator_type = 'F'
1738 and pet.element_type_id = piv.element_type_id
1739 and nvl(abs.date_start,piv.effective_end_date) <= piv.effective_end_date
1740 and nvl(abs.date_end,piv.effective_start_date) >= piv.effective_start_date
1741 and piv.name = 'CREATOR_ID'
1742 and peev.element_entry_id = pee.element_entry_id
1743 and peev.input_value_id=piv.input_value_id
1744 and peev.screen_entry_value = to_char(abs.absence_attendance_id);
1745 --and peev.screen_entry_value = abs.absence_attendance_id;
1746
1747
1748
1749 /* select pee.element_entry_id element_entry_id
1750 ,pet.processing_type processing_type
1751 ,pee.effective_start_date effective_start_date
1752 ,pee.effective_end_date effective_end_date
1753 from per_absence_attendances abs
1754 ,per_all_assignments_f asg
1755 ,pay_element_types_f pet
1756 ,pay_element_links_f pel
1757 ,pay_element_entries_f pee
1758 where abs.absence_attendance_id = p_absence_attendance_id
1759 and abs.person_id = asg.person_id
1760 and asg.assignment_id = p_assignment_id
1761 and nvl(abs.date_start,asg.effective_end_date) <= asg.effective_end_date
1762 and nvl(abs.date_end,asg.effective_start_date) >= asg.effective_start_date
1763 and pet.element_type_id = p_element_type_id
1764 and nvl(abs.date_start,pet.effective_start_date) >= pet.effective_start_date
1765 and nvl(abs.date_end,pet.effective_end_date) <= pet.effective_end_date
1766 and pet.element_type_id = pel.element_type_id
1767 and pel.element_link_id = pee.element_link_id
1768 and pee.assignment_id = asg.assignment_id
1769 and pee.creator_id = abs.absence_attendance_id
1770 and pee.creator_type = 'F';
1771 */
1772
1773
1774 rec_assignments csr_assignments%rowtype;
1775 rec_absence_element csr_get_absence_element%rowtype;
1776 l_element_type_id NUMBER(15);
1777
1778 l_flag varchar2(1);
1779 BEGIN
1780 --
1781 -- Added for GSI Bug 5472781
1782 ---pgopal -Included 'NO','SE','PL' in the legislation installation check.
1783 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') OR
1784 hr_utility.chk_product_install('Oracle Human Resources', 'NO') OR
1785 hr_utility.chk_product_install('Oracle Human Resources', 'SE') OR
1786 hr_utility.chk_product_install('Oracle Human Resources', 'PL') OR
1787 hr_utility.chk_product_install('Oracle Human Resources', 'FI') THEN
1788 --
1789 hr_utility.set_location('Start of absence element deletion section', 30);
1790 --
1791 FOR rec_assignments in csr_assignments LOOP
1792 --
1793 hr_utility.set_location(' in for loop ', 35);
1794 --
1795 l_element_type_id := get_element_for_category (p_absence_attendance_id );
1796 --
1797 hr_utility.set_location('l_element_type_id '|| l_element_type_id, 35);
1798 --
1799 l_flag := 'N';
1800 --
1801 open csr_get_absence_element(rec_assignments.assignment_id,l_element_type_id );
1802 fetch csr_get_absence_element into rec_absence_element;
1803 if csr_get_absence_element%notfound then
1804 l_flag := 'Y';
1805 end if;
1806 close csr_get_absence_element;
1807 --
1808 hr_utility.set_location('rec_assignments.assignment_id '|| rec_assignments.assignment_id, 35);
1809 hr_utility.set_location('rec_absence_element.element_entry_id '|| rec_absence_element.element_entry_id, 35);
1810 if l_flag <> 'Y' then
1811 if rec_absence_element.element_entry_id is not null then
1812 delete_absence_element
1813 (p_dt_delete_mode => 'ZAP'
1814 ,p_session_date => rec_absence_element.effective_start_date
1815 ,p_element_entry_id => rec_absence_element.element_entry_id);
1816 end if;
1817 end if;
1818 --
1819 hr_utility.set_location('End of absence element deletion section', 40);
1820 --
1821 END LOOP;
1822 END IF;
1823 END delete_absence;
1824
1825 END hr_loc_absence;