[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_EHOL_CARD_REQUEST
Source
1 PACKAGE BODY PAY_DK_EHOL_CARD_REQUEST AS
2 /* $Header: pydkeholreq.pkb 120.0.12020000.2 2012/07/18 06:52:00 rsahai noship $ */
3
4
5 TYPE hol_card_emp IS RECORD ( emp_name per_all_people_f.full_name%type,
6 assignmnet_number per_all_assignments_f.assignment_number%type,
7 cpr_number per_all_people_f.national_identifier%type,
8 hire_date varchar2(50) ,
9 start_date varchar2(15),
10 no_of_hol varchar2(5) ,
11 year varchar2(10) );
12 TYPE hol_card_emp_tab IS TABLE OF hol_card_emp INDEX BY BINARY_INTEGER;
13 g_hol_card_emp hol_card_emp_tab;
14
15 g_report_type varchar2(10) ;
16 g_date_created varchar2(10);
17 g_time_created varchar2(10);
18 g_run_id varchar2(50);
19 g_payroll_name VARCHAR2(52);
20 g_version varchar2(20);
21 g_se_no hr_organization_information.org_information5%TYPE ;
22 g_cvr_no hr_organization_information.org_information1%TYPE ;
23 g_date_last_changed varchar2(10);
24 g_company_name varchar2(255);
25 g_count_employee number;
26 g_absence_attendance_type_id per_absence_attendance_types.absence_attendance_type_id%type;
27 g_test_submission varchar2(1) ;
28 g_business_group_id hr_organization_units.business_group_id%type;
29 g_legal_employer_id hr_organization_information.organization_id%type;
30 g_effective_date date;
31 g_vendor_id varchar2(25);
32
33 TYPE error_rec IS RECORD ( -- emp_name per_all_people_f.full_name%type ,
34 -- p_cpr_number per_all_people_f.national_identifier%type,
35 p_assg_number per_all_assignments_f.assignment_number%type,
36 p_err_msg VARCHAR2(1000),
37 p_absence_start date,
38 p_duration number);
39
40 TYPE err_tab IS TABLE OF error_rec INDEX BY BINARY_INTEGER;
41
42 l_err_tab err_tab;
43 err_cnt number := 1;
44
45 TYPE process_rec IS RECORD ( -- emp_name per_all_people_f.full_name%type ,
46 -- p_cpr_number per_all_people_f.national_identifier%type,
47 p_assg_number per_all_assignments_f.assignment_number%type,
48 p_proc_msg VARCHAR2(1000),
49 p_absence_start date,
50 p_duration number);
51
52 TYPE process_tab IS TABLE OF process_rec INDEX BY BINARY_INTEGER;
53
54 l_proc_tab process_tab;
55 proc_cnt number := 1;
56 g_warning CONSTANT NUMBER := 1;
57 g_error CONSTANT NUMBER := 2;
58
59 PROCEDURE log_dk_ehol_card_header AS
60 l_line_1 varchar2(1000) := ' ';
61 l_line_2 varchar2(1000) := ' ';
62 l_line_3 varchar2(1000) := ' ';
63
64 BEGIN
65 l_line_1 := rpad('Assignment',15)
66 || ' '
67 || rpad('Absence Start Date',25)
68 || ' '
69 || rpad('Holidays',20)
70 || ' '
71 || rpad('Status',50);
72
73 l_line_3 := rpad('-',15,'-')
74 || ' '
75 || rpad('-',25,'-')
76 || ' '
77 || rpad('-',20,'-')
78 || ' '
79 || rpad('-',50,'-');
80
81 FND_FILE.NEW_LINE(fnd_file.output, 1);
82 fnd_file.put_line(fnd_file.output,l_line_1);
83 fnd_file.put_line(fnd_file.output,l_line_3);
84
85 END log_dk_ehol_card_header;
86
87 PROCEDURE log_dk_ehol_card_body AS
88 l_line varchar2(1000);
89
90 BEGIN
91
92 FND_FILE.NEW_LINE(fnd_file.output, 1);
93
94 IF l_proc_tab.COUNT <> 0 THEN
95 FOR i IN l_proc_tab.first..l_proc_tab.last
96 LOOP
97 fnd_file.put_line(fnd_file.output,rpad(l_proc_tab(i).p_assg_number,16,' ')
98 ||rpad(l_proc_tab(i).p_absence_start,26,' ')||rpad(l_proc_tab(i).p_duration,21,' ')||l_proc_tab(i).p_proc_msg);
99 END LOOP;
100 END IF;
101
102 IF l_err_tab.COUNT <> 0 then
103 FOR i IN l_err_tab.first..l_err_tab.last
104 LOOP
105 fnd_file.put_line(fnd_file.output,rpad(nvl(l_err_tab(i).p_assg_number,' '),16,' ')
106 ||rpad(nvl(to_char(l_err_tab(i).p_absence_start),' '),26,' ')||rpad(l_err_tab(i).p_duration,21,' ')||l_err_tab(i).p_err_msg);
107 END LOOP;
108 END IF;
109
110 END log_dk_ehol_card_body;
111
112
113 FUNCTION get_eholiday_details
114 (p_assignment_id IN NUMBER
115 ,p_effective_date IN DATE
116 ,p_abs_start_date IN DATE
117 ,p_abs_end_date IN DATE
118 ,p_start_date OUT NOCOPY DATE
119 ,p_end_date OUT NOCOPY DATE
120 ,p_over_days OUT NOCOPY NUMBER
121 ,p_over_hours OUT NOCOPY NUMBER
122 ,p_creator_id OUT NOCOPY varchar2
123 ,p_year OUT NOCOPY varchar2 ) RETURN NUMBER IS
124 --
125 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
126 SELECT ee.element_entry_id element_entry_id
127 , eev1.screen_entry_value screen_entry_value
128 , iv1.name
129 FROM per_all_assignments_f asg1
130 ,per_all_assignments_f asg2
131 ,per_all_people_f per
132 ,pay_element_links_f el
133 ,pay_element_types_f et
134 ,pay_input_values_f iv1
135 ,pay_element_entries_f ee
136 ,pay_element_entry_values_f eev1
137 WHERE asg1.assignment_id = p_assignment_id
138 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
139 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
140 AND per.person_id = asg1.person_id
141 AND asg2.person_id = per.person_id
142 AND et.element_name = 'Override Holiday Duration'
143 AND et.legislation_code = 'DK'
144 AND iv1.element_type_id = et.element_type_id
145 AND iv1.name in ('Start Date', 'End Date', 'Override Hours', 'Override Days','File ID','Year')
146 AND el.business_group_id = per.business_group_id
147 AND el.element_type_id = et.element_type_id
148 AND ee.assignment_id = asg2.assignment_id
149 AND ee.element_link_id = el.element_link_id
150 AND eev1.element_entry_id = ee.element_entry_id
151 AND eev1.input_value_id = iv1.input_value_id
152 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
153 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
154 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
155 ORDER BY ee.element_entry_id;
156 --
157 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
158 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
159 eename pay_input_values_f.name%TYPE );
160 l_rec l_record;
161 TYPE l_table is table of l_record index by BINARY_INTEGER;
162 l_tab l_table;
163
164 l_start_date date;
165 l_end_date date;
166 l_over_hours number;
167 l_over_days number;
168 l_counter number ;
169 l_bool_match boolean;
170 l_num_match number;
171 l_creator_id varchar2(50);
172 l_year varchar2(5);
173 --
174 BEGIN
175 --
176 l_counter := 1;
177 l_bool_match := FALSE;
178
179 -- Open cursor to fetch all screen entry values of Override Holiday Duration element.
180 OPEN get_details(p_assignment_id , p_effective_date );
181 -- Assign the values to a table type
182 FETCH get_details BULK COLLECT INTO l_tab;
183 CLOSE get_details;
184
185 -- Loop through each values for processing.
186 FOR l_cur in 1..l_tab.count LOOP
187 -- Assign values to local variables.
188 IF l_tab(l_cur).eename = 'Start Date' THEN
189 l_start_date := to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss') ;
190 ELSIF l_tab(l_cur).eename = 'End Date' THEN
191 l_end_date := to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
192 ELSIF l_tab(l_cur).eename = 'Override Days' THEN
193 l_over_days := l_tab(l_cur).eevalue;
194 ELSIF l_tab(l_cur).eename = 'Override Hours' THEN
195 l_over_hours := l_tab(l_cur).eevalue;
196 ELSIF l_tab(l_cur).eename = 'File ID' THEN
197 l_creator_id := l_tab(l_cur).eevalue;
198 ELSIF l_tab(l_cur).eename = 'Year' THEN
199 l_year := l_tab(l_cur).eevalue;
200 END IF;
201 -- Check no. of input values of override element is 4
202 IF l_counter < 6 then
203 l_counter := l_counter + 1;
204 ELSE
205 -- Check override element's start and end date matches with Absent element.
206 IF l_start_date = p_abs_start_date and l_end_date = p_abs_end_date THEN
207 -- Multiple entry exists with same start and end date
208 IF l_bool_match THEN
209 p_start_date := null;
210 p_end_date := null;
211 p_over_days := null;
212 p_over_hours := null;
213 p_year := null;
214 p_creator_id := null;
215 return -1;
216 -- Exact match found
217 ELSE
218 l_bool_match := True;
219 END IF;
220 -- Assign input values to output variables.
221 p_start_date := l_start_date;
222 p_end_date := l_end_date;
223 p_over_days := l_over_days;
224 p_over_hours := l_over_hours;
225 p_creator_id := l_creator_id;
226 p_year := l_year;
227 END IF;
228 l_counter := 1;
229 END IF;
230 END LOOP;
231
232 -- Match found successfully
233 IF p_start_date is not null then
234 RETURN 1;
235 -- Override element exists but date doesnt match.
236 elsif p_start_date is null and l_tab.count > 0 then
237 RETURN 2;
238 -- No override element attached
239 ELSE
240 RETURN 0;
241 END IF;
242 --
243 END get_eholiday_details;
244
245
246 PROCEDURE create_timestamp_entry ( p_effective_date IN DATE
247 , p_assignment_id IN NUMBER
248 , p_business_group_id IN NUMBER
249 , p_run_id IN varchar2
250 , p_date_created IN varchar2
251 , p_time_created IN varchar2
252 ) IS
253 CURSOR element_csr IS
254 SELECT element_type_id
255 FROM pay_element_types_f
256 WHERE element_name = 'Holiday Card Payment Request'
257 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
258 AND nvl(legislation_code, 'DK') = 'DK'
259 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
260 --
261 element_rec element_csr%ROWTYPE;
262 --
263 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
264 SELECT input_value_id
265 FROM pay_input_values_f
266 WHERE element_type_id = p_element_type_id
267 AND name = p_name
268 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
269 AND nvl(legislation_code, 'DK') = 'DK'
270 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
271 --
272 input_val_rec1 input_val_csr%ROWTYPE;
273 input_val_rec2 input_val_csr%ROWTYPE;
274 input_val_rec3 input_val_csr%ROWTYPE;
275 --
276
277 CURSOR link_csr(p_element_type_id IN NUMBER) IS
278 SELECT links.element_link_id
279 FROM pay_element_links_f links, per_all_assignments_f assign
280 WHERE links.element_type_id = p_element_type_id
281 AND links.business_group_id=p_business_group_id
282 AND assign.assignment_id=p_assignment_id
283 AND (( links.payroll_id is not null
284 and links.payroll_id = assign.payroll_id)
285 OR ( links.link_to_all_payrolls_flag='Y'
286 and assign.payroll_id is not null)
287 OR ( links.payroll_id is null
288 and links.link_to_all_payrolls_flag='N')
289 OR links.job_id=assign.job_id
290 OR links.position_id=assign.position_id
291 OR links.people_group_id=assign.people_group_id
292 OR links.organization_id=assign.organization_id
293 OR links.grade_id=assign.grade_id
294 OR links.location_id=assign.location_id
295 OR links.pay_basis_id=assign.pay_basis_id
296 OR links.employment_category=assign.employment_category)
297 AND p_effective_date BETWEEN links.effective_start_date
298 AND links.effective_end_date;
299 --
300 link_rec link_csr%ROWTYPE;
301 --
302 l_element_entry_id NUMBER;
303 l_effective_start_date DATE;
304 l_effective_end_date DATE;
305 l_object_version_number NUMBER;
306 l_create_warning BOOLEAN := FALSE;
307 BEGIN
308
309 hr_utility.set_location('Entering create_overide_hol element procedure',501);
310 --
311 -- Get Element information
312 --
313 OPEN element_csr;
314 FETCH element_csr INTO element_rec;
315 CLOSE element_csr;
316 --
317 -- Get Input Values
318 --
319 OPEN input_val_csr(element_rec.element_type_id, 'File ID');
320 FETCH input_val_csr INTO input_val_rec1;
321 CLOSE input_val_csr;
322 --
323 OPEN input_val_csr(element_rec.element_type_id, 'Date Created');
324 FETCH input_val_csr INTO input_val_rec2;
325 CLOSE input_val_csr;
326 --
327 OPEN input_val_csr(element_rec.element_type_id, 'Time Created');
328 FETCH input_val_csr INTO input_val_rec3;
329 CLOSE input_val_csr;
330 --
331
332 -- Get element link information
333 --
334 OPEN link_csr(element_rec.element_type_id);
335 FETCH link_csr INTO link_rec;
336 CLOSE link_csr;
337
338 -- Call API To Create element entry.
339 py_element_entry_api.create_element_entry (
340 p_effective_date => p_effective_date,
341 p_business_group_id => p_business_group_id,
342 --p_original_entry_id => p_original_entry_id, -- default
343 p_assignment_id => p_assignment_id,
344 p_element_link_id => link_rec.element_link_id,
345 p_entry_type => 'E',
346 p_creator_type => 'F',
347 p_input_value_id1 => input_val_rec1.input_value_id,
348 p_input_value_id2 => input_val_rec2.input_value_id,
349 p_input_value_id3 => input_val_rec3.input_value_id,
350 p_entry_value1 => p_run_id,
351 p_entry_value2 => p_date_created,
352 p_entry_value3 => p_time_created,
353 p_effective_start_date => l_effective_start_date,
354 p_effective_end_date => l_effective_end_date,
355 p_element_entry_id => l_element_entry_id,
356 p_object_version_number => l_object_version_number,
357 p_create_warning => l_create_warning
358 );
359 hr_utility.set_location('leaving procedure create_timestamp_entry ',501);
360 END create_timestamp_entry;
361
362 PROCEDURE create_override_hol ( p_effective_date IN DATE
363 , p_assignment_id IN NUMBER
364 , p_business_group_id IN NUMBER
365 , p_start_date IN DATE
366 , p_end_date IN DATE
367 , p_over_days IN NUMBER
368 , p_over_hours IN NUMBER
369 , p_year varchar2 ) IS
370 CURSOR element_csr IS
371 SELECT element_type_id
372 FROM pay_element_types_f
373 WHERE element_name = 'Override Holiday Duration'
374 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
375 AND nvl(legislation_code, 'DK') = 'DK'
376 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
377 --
378 element_rec element_csr%ROWTYPE;
379 --
380 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
381 SELECT input_value_id
382 FROM pay_input_values_f
383 WHERE element_type_id = p_element_type_id
384 AND name = p_name
385 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
386 AND nvl(legislation_code, 'DK') = 'DK'
387 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
388 --
389 input_val_rec1 input_val_csr%ROWTYPE;
390 input_val_rec2 input_val_csr%ROWTYPE;
391 input_val_rec3 input_val_csr%ROWTYPE;
392 input_val_rec4 input_val_csr%ROWTYPE;
393 input_val_rec5 input_val_csr%ROWTYPE;
394 input_val_rec6 input_val_csr%ROWTYPE;
395 --
396
397 CURSOR link_csr(p_element_type_id IN NUMBER) IS
398 SELECT links.element_link_id
399 FROM pay_element_links_f links, per_all_assignments_f assign
400 WHERE links.element_type_id = p_element_type_id
401 AND links.business_group_id=p_business_group_id
402 AND assign.assignment_id=p_assignment_id
403 AND (( links.payroll_id is not null
404 and links.payroll_id = assign.payroll_id)
405 OR ( links.link_to_all_payrolls_flag='Y'
406 and assign.payroll_id is not null)
407 OR ( links.payroll_id is null
408 and links.link_to_all_payrolls_flag='N')
409 OR links.job_id=assign.job_id
410 OR links.position_id=assign.position_id
411 OR links.people_group_id=assign.people_group_id
412 OR links.organization_id=assign.organization_id
413 OR links.grade_id=assign.grade_id
414 OR links.location_id=assign.location_id
415 OR links.pay_basis_id=assign.pay_basis_id
416 OR links.employment_category=assign.employment_category)
417 AND p_effective_date BETWEEN links.effective_start_date
418 AND links.effective_end_date;
419 --
420 link_rec link_csr%ROWTYPE;
421 --
422 l_element_entry_id NUMBER;
423 l_effective_start_date DATE;
424 l_effective_end_date DATE;
425 l_object_version_number NUMBER;
426 l_create_warning BOOLEAN := FALSE;
427 BEGIN
428
429 hr_utility.set_location('Entering create_overide_hol element procedure',501);
430 --
431 -- Get Element information
432 --
433 OPEN element_csr;
434 FETCH element_csr INTO element_rec;
435 CLOSE element_csr;
436 --
437 -- Get Input Values
438 --
439 OPEN input_val_csr(element_rec.element_type_id, 'Start Date');
440 FETCH input_val_csr INTO input_val_rec1;
441 CLOSE input_val_csr;
442 --
443 OPEN input_val_csr(element_rec.element_type_id, 'End Date');
444 FETCH input_val_csr INTO input_val_rec2;
445 CLOSE input_val_csr;
446 --
447 OPEN input_val_csr(element_rec.element_type_id, 'Override Days');
448 FETCH input_val_csr INTO input_val_rec3;
449 CLOSE input_val_csr;
450 --
451 OPEN input_val_csr(element_rec.element_type_id, 'Override Hours');
452 FETCH input_val_csr INTO input_val_rec4;
453 CLOSE input_val_csr;
454 --
455 OPEN input_val_csr(element_rec.element_type_id, 'File ID');
456 FETCH input_val_csr INTO input_val_rec5;
457 CLOSE input_val_csr;
458 --
459 OPEN input_val_csr(element_rec.element_type_id, 'Year');
460 FETCH input_val_csr INTO input_val_rec6;
461 CLOSE input_val_csr;
462 --
463 -- Get element link information
464 --
465 OPEN link_csr(element_rec.element_type_id);
466 FETCH link_csr INTO link_rec;
467 CLOSE link_csr;
468
469 -- Call API To Create element entry.
470 py_element_entry_api.create_element_entry (
471 p_effective_date => p_start_date,
472 p_business_group_id => p_business_group_id,
473 --p_original_entry_id => p_original_entry_id, -- default
474 p_assignment_id => p_assignment_id,
475 p_element_link_id => link_rec.element_link_id,
476 p_entry_type => 'E',
477 p_creator_type => 'F',
478 p_input_value_id1 => input_val_rec1.input_value_id,
479 p_input_value_id2 => input_val_rec2.input_value_id,
480 p_input_value_id3 => input_val_rec3.input_value_id,
481 p_input_value_id4 => input_val_rec4.input_value_id,
482 p_input_value_id5 => input_val_rec5.input_value_id,
483 p_input_value_id6 => input_val_rec6.input_value_id,
484 p_entry_value1 => p_start_date,
485 p_entry_value2 => p_end_date,
486 p_entry_value3 => nvl(p_over_days,0),
487 p_entry_value4 => p_over_hours,
488 p_entry_value5 => g_run_id,
489 p_entry_value6 => p_year,
490 p_effective_start_date => l_effective_start_date,
491 p_effective_end_date => l_effective_end_date,
492 p_element_entry_id => l_element_entry_id,
493 p_object_version_number => l_object_version_number,
494 p_create_warning => l_create_warning
495 );
496 hr_utility.set_location('leaving create_overide_hol element procedure',501);
497 END create_override_hol;
498
499 PROCEDURE get_end_date(p_start_date date,
500 p_duration number,
501 p_assg_id number,
502 p_work_pattern varchar,
503 p_end_date OUT NOCOPY date) is
504
505
506 l_start_time_char varchar2(10) := '0';
507 l_end_time_char varchar2(10) := '0';
508 l_duration number := 0;
509 l_duration1 number := 0;
510 l_duration2 number := 0;
511
512 l_start_date date;
513 l_end_date date;
514 l_weekdays number ;
515 l_calendar_days number ;
516 l_days_or_hours varchar2(1);
517 l_include_event varchar2(1);
518 l_days_without_holidays number;
519 l_days_with_holidays number;
520 l_holidays number;
521 l_hol_days number;
522
523 l_return number;
524
525 BEGIN
526 l_duration1 := p_duration;
527 l_duration2 := l_duration1;
528 l_hol_days := 0;
529
530 WHILE l_hol_days <> l_duration1 LOOP
531 l_start_date := p_start_date;
532 p_end_date := fffunc.add_days(l_start_date,l_duration2-1);
533
534 l_weekdays := apps.PAY_DK_HOLIDAY_PAY_PKG.get_weekdays(l_start_date,p_end_date,p_work_pattern);
535 l_calendar_days := fffunc.DAYS_BETWEEN(p_end_date,l_start_date) + 1;
536
537 l_days_or_hours := 'D';
538 l_include_event := 'N';
539 l_return := hr_loc_work_schedule.calc_sch_based_dur( p_assg_id,l_days_or_hours, l_include_event, l_start_date, p_end_date, l_start_time_char, l_end_time_char, l_duration);
540 l_days_without_holidays := l_duration;
541
542 l_include_event := 'Y';
543 l_return := hr_loc_work_schedule.calc_sch_based_dur( p_assg_id,l_days_or_hours, l_include_event, l_start_date, p_end_date, l_start_time_char, l_end_time_char, l_duration);
544 l_days_with_holidays := l_duration;
545
546 l_holidays := l_days_without_holidays - l_days_with_holidays;
547
548 l_hol_days := l_weekdays - l_holidays;
549 l_duration2 := (l_duration1 - l_hol_days) + l_duration2 ;
550 END LOOP;
551 END get_end_date;
552
553 PROCEDURE create_absence(p_effective_date date,
554 p_start_date date,
555 p_end_date date,
556 p_business_group_id number,
557 p_person_id number
558 -- , p_assg_id number ,
559 -- p_holidays number,
560 -- p_year varchar2
561 ) IS
562 l_absence_attendance_id number;
563 l_absence_days number;
564 l_absence_hours number;
565 l_occurrence number;
566 l_object_version_number number;
567 l_dur_dys_less_warning boolean;
568 l_dur_hrs_less_warning boolean;
569 l_exceeds_pto_entit_warning boolean;
570 l_exceeds_run_total_warning boolean;
571 l_abs_overlap_warning boolean;
572 l_abs_day_after_warning boolean;
573 l_dur_overwritten_warning boolean;
574
575 l_start_date date;
576 l_end_date date;
577 l_effective_date DATE;
578 l_person_id per_all_people_f.person_id%type;
579 l_business_group_id hr_organization_units.business_group_id%type;
580 l_abs_attendance_id per_absence_attendance_types.absence_attendance_type_id%type;
581 --l_assignment_id number;
582
583 BEGIN
584 hr_utility.set_location(' Entering Procedure create_absence',10);
585 l_start_date := p_start_date;
586 l_end_date := p_end_date;
587 l_effective_date := p_effective_date;
588 l_person_id := p_person_id ;
589 l_business_group_id := p_business_group_id;
590
591 /* open get_absence_type_id;
592 fetch get_absence_type_id into l_abs_attendance_id;
593 close get_absence_type_id; */
594
595 l_abs_attendance_id := g_absence_attendance_type_id ;
596
597 hr_utility.set_location('l_person_id: '||l_person_id,301);
598 hr_utility.set_location('l_abs_attendance_id: '||l_abs_attendance_id,302);
599 hr_utility.set_location('entering create_person_absence',302);
600
601 hr_person_absence_api.create_person_absence
602 ( p_effective_date => l_effective_date
603 ,p_person_id => l_person_id
604 ,p_business_group_id => l_business_group_id
605 ,p_absence_attendance_type_id => l_abs_attendance_id --21044
606 ,p_date_notification => l_start_date -- l_effective_date
607 ,p_date_start => l_start_date
608 ,p_date_end => l_end_date
609 ,p_absence_days => l_absence_days
610 ,p_absence_hours => l_absence_hours
611 ,p_absence_attendance_id => l_absence_attendance_id
612 ,p_object_version_number => l_object_version_number
613 ,p_occurrence => l_occurrence
614 ,p_dur_dys_less_warning =>l_dur_dys_less_warning
615 ,p_dur_hrs_less_warning => l_dur_hrs_less_warning
616 ,p_exceeds_pto_entit_warning =>l_exceeds_pto_entit_warning
617 ,p_exceeds_run_total_warning =>l_exceeds_run_total_warning
618 ,p_abs_overlap_warning =>l_abs_overlap_warning
619 ,p_abs_day_after_warning =>l_abs_day_after_warning
620 ,p_dur_overwritten_warning =>l_dur_overwritten_warning
621 , p_create_element_entry => TRUE
622 ) ;
623 hr_utility.set_location('leaving create_person_absence',302);
624
625
626 END create_absence;
627
628 PROCEDURE validate_ecard_details (p_business_group_id number
629 , p_effective_date date
630 , retcode OUT NOCOPY varchar2)
631 IS
632
633 CURSOR csr_Legal_Emp_Details ( p_se_no hr_organization_information.ORG_INFORMATION5%TYPE
634 ,p_cvr_no hr_organization_information.ORG_INFORMATION1%TYPE)
635 IS
636 SELECT o1.name le_name ,
637 hoi2.organization_id org_id,
638 hoi2.ORG_INFORMATION1 cvr_no,
639 hoi2.ORG_INFORMATION2,
640 hoi2.ORG_INFORMATION3,
641 hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5 se_no,
642 hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
643 FROM hr_organization_units o1
644 , hr_organization_information hoi1
645 , hr_organization_information hoi2
646 WHERE o1.business_group_id = p_business_group_id
647 AND hoi1.organization_id = o1.organization_id
648 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
649 AND hoi1.org_information_context = 'CLASS'
650 AND o1.organization_id = hoi2.organization_id
651 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
652 AND hoi2.ORG_INFORMATION5 = NVL(p_se_no,hoi2.ORG_INFORMATION5)
653 AND hoi2.ORG_INFORMATION1= p_cvr_no
654 AND hoi2.ORG_INFORMATION3 = 'Y';
655
656 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
657
658
659 -- Service Provider information.
660 CURSOR service_provider_details(p_cvr_no hr_organization_information.ORG_INFORMATION1%TYPE)
661 IS
662 SELECT ORGANIZATION_ID org_id,ORG_INFORMATION1 cvr_no,ORG_INFORMATION3
663 FROM hr_organization_information
664 WHERE org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
665 AND organization_id in (
666 select organization_id from hr_organization_units
667 where business_group_id= p_business_group_id)
668 AND org_information1 = p_cvr_no ;
669
670 sp service_provider_details%rowtype;
671
672
673 CURSOR csr_le_people(p_assignment_no varchar, p_cvr_no varchar,p_effective_date date) is
674 SELECT DISTINCT papf.PERSON_ID person_id,
675 papf.full_name full_name,
676 paf.assignment_id assg_id ,
677 scl.SEGMENT10 def_wrk_patt ,
678 ppf.payroll_id payroll_id ,
679 ppf.PRL_INFORMATION1 holiday_card
680 FROM pay_payrolls_f ppf,
681 per_assignments_f paf,
682 hr_organization_units hou,
683 hr_organization_information hoi,
684 hr_soft_coding_keyflex scl ,
685 per_people_f papf
686 WHERE hou.business_group_id = p_business_group_id
687 AND hou.organization_id = g_legal_employer_id
688 AND hou.organization_id = hoi.organization_id
689 AND hoi.org_information_context = 'CLASS'
690 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
691 AND paf.payroll_id = ppf.payroll_id
692 AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
693 AND scl.segment1 = to_char(hou.organization_id)
694 AND paf.business_group_id = p_business_group_id
695 AND paf.ASSIGNMENT_NUMBER = p_assignment_no
696 AND papf.person_id = paf.person_id
697 AND papf.NATIONAL_IDENTIFIER = p_cvr_no
698 AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
699 AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
700 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
701 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date ;
702
703 rg_le_people csr_le_people%rowtype;
704
705 /* Cursor to fetch the Legal Employer level Holiday Entitlement */
706 CURSOR csr_get_hol_entity(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
707 SELECT hoi2.ORG_INFORMATION1 def_wrk_patt,
708 NVL(hoi2.org_information5,'N') holiday_card
709 FROM HR_ORGANIZATION_UNITS hou
710 , HR_ORGANIZATION_INFORMATION hoi1
711 , HR_ORGANIZATION_INFORMATION hoi2
712 WHERE hou.business_group_id = p_business_group_id
713 AND hoi1.organization_id = hou.organization_id
714 AND hoi1.organization_id = p_le_id
715 AND hoi1.ORG_INFORMATION_CONTEXT='CLASS'
716 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
717 AND hoi1.ORG_INFORMATION2 = 'Y'
718 AND hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
719 AND hoi2.organization_id = hoi1.organization_id
720 AND p_effective_date BETWEEN hou.DATE_FROM AND nvl(hou.DATE_TO, p_effective_date);
721
722 l_5d_6d hr_soft_coding_keyflex.SEGMENT10%type;
723 l_work_pattern HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%type;
724 l_ass_no per_assignments_f.ASSIGNMENT_NUMBER%TYPE;
725 l_cvr_no per_people_f.NATIONAL_IDENTIFIER%TYPE;
726 l_flag number;
727 l_start_date date;
728 l_end_date date;
729 l_holidays varchar2(20);
730 l_assg_id per_all_assignments_f.assignment_id%type;
731 l_year varchar2(10);
732 l_name per_all_people_f.full_name%type;
733 l_return NUMBER;
734 l_param VARCHAR2(100);
735 e_record_too_long EXCEPTION;
736 name_is_null EXCEPTION;
737
738 CURSOR get_value_from_ddf(p_effective_date date, p_payroll_id number) IS
739 SELECT ppf.PRL_INFORMATION1
740 FROM pay_payrolls_f ppf
741 WHERE PAYROLL_id = p_payroll_id
742 AND p_effective_date BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
743
744
745 CURSOR csr_ele_link(p_element_name pay_element_types_f.element_name%type,
746 p_payroll_id pay_payrolls_f.payroll_id%type ) IS
747 SELECT pel.ELEMENT_LINK_ID
748 FROM pay_element_links_f pel ,pay_element_types_f pet
749 WHERE pel.ELEMENT_TYPE_ID = pet.element_type_id
750 AND nvl(pel.PAYROLL_ID,p_payroll_id) = p_payroll_id
751 AND pet.element_name = p_element_name
752 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
753 AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
754 AND pel.business_group_id = p_business_group_id;
755
756
757 CURSOR check_run_id (p_assg_id number, p_run_id VARCHAR2) IS
758 SELECT 1
759 FROM per_all_assignments_f asg1
760 ,pay_element_types_f et
761 ,pay_input_values_f iv1
762 ,pay_element_entries_f ee
763 ,pay_element_entry_values_f eev1
764 WHERE asg1.assignment_id = p_assg_id
765 -- AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
766 AND et.element_name = 'Holiday Card Payment Request'
767 AND et.legislation_code = 'DK'
768 AND iv1.element_type_id = et.element_type_id
769 AND iv1.name in ('File ID')
770 AND ee.element_type_id = et.element_type_id
771 AND ee.assignment_id = asg1.assignment_id
772 AND eev1.element_entry_id = ee.element_entry_id
773 AND eev1.input_value_id = iv1.input_value_id
774 AND eev1.screen_entry_value = p_run_id;
775
776 CURSOR chk_multiple_assg(p_assig_no varchar2, p_cpr_no varchar2) IS
777 SELECT asg2.ASSIGNMENT_ID
778 FROM per_all_people_f per ,
779 per_all_assignments_f asg1,
780 per_all_assignments_f asg2
781 WHERE asg1.assignment_number = p_assig_no
782 AND per.NATIONAL_IDENTIFIER = p_cpr_no
783 AND asg1.person_id = per.person_id
784 AND per.person_id = asg2.person_id
785 AND asg2.business_group_id = p_business_group_id
786 AND asg1.assignment_id <> asg2.assignment_id
787 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
788 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
789 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date;
790
791 l_use_holiday_card VARCHAR2(10) := 'N';
792 l_uhc_payroll VARCHAR2(10);
793 l_payroll_id pay_payrolls_f.payroll_id%type;
794 l_last_yr VARCHAR2(10);
795 l_curr_entit_yr VARCHAR2(10);
796 l_action_info_id NUMBER;
797 l_ovn NUMBER;
798 l_ele_link_id pay_element_links_f.element_link_id%type;
799 l_payroll_exists NUMBER;
800 l_assignment_id per_all_assignments_f.assignment_id%type;
801
802 wrong_entit_year EXCEPTION;
803 person_not_found EXCEPTION;
804 assg_already_processed EXCEPTION;
805 holiday_card_no_set EXCEPTION;
806 abs_term_date EXCEPTION;
807
808 BEGIN
809
810 hr_utility.set_location('Entering procedure validate_E Holiday card',100);
811 hr_utility.set_location('SE Number of the receving company = ' ||g_se_no,101);
812 hr_utility.set_location('CVR Number of the receving company = ' ||g_cvr_no,102);
813
814 OPEN csr_Legal_Emp_Details(g_se_no,g_cvr_no);
815 FETCH csr_Legal_Emp_Details into rg_Legal_Emp_Details;
816 CLOSE csr_Legal_Emp_Details;
817 g_legal_employer_id := rg_Legal_Emp_Details.org_id ;
818
819 IF g_legal_employer_id IS NULL THEN
820
821 OPEN service_provider_details(g_cvr_no);
822 FETCH service_provider_details INTO sp;
823 CLOSE service_provider_details;
824 g_legal_employer_id := sp.org_id ;
825
826 END IF ;
827
828 hr_utility.set_location('organization id = ' ||g_legal_employer_id,102);
829 IF g_legal_employer_id IS NULL THEN
830 hr_utility.set_message(801, 'PAY_377024_DK_LEGAL_EMPLOYER_E');
831 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
832 hr_utility.raise_error;
833 END IF;
834
835 OPEN csr_get_hol_entity(g_legal_employer_id,g_effective_date,g_business_group_id);
836 FETCH csr_get_hol_entity into l_work_pattern,l_use_holiday_card;
837 CLOSE csr_get_hol_entity;
838
839 hr_utility.set_location('le_name = ' ||rg_Legal_Emp_Details.le_name,103);
840 hr_utility.set_location('run id = ' ||g_run_id,105);
841 hr_utility.set_location('total employee on xml = ' ||g_count_employee,106);
842
843 IF (l_use_holiday_card ='N' ) THEN
844 hr_utility.set_message(801, 'PAY_DK_USE_HOLIDAY_CARD');
845 hr_utility.raise_error;
846 END IF ;
847
848
849 FOR i IN 1..g_count_employee LOOP
850 BEGIN
851 l_assignment_id := NUll;
852 l_flag := 0;
853
854 l_ass_no := g_hol_card_emp(i).assignmnet_number;
855 l_cvr_no := g_hol_card_emp(i).cpr_number;
856 l_year := g_hol_card_emp(i).year;
857 l_name := g_hol_card_emp(i).emp_name;
858 l_start_date := fnd_date.canonical_to_date(g_hol_card_emp(i).start_date);
859 l_holidays := to_number(replace(g_hol_card_emp(i).no_of_hol,',','.'));
860
861 IF l_name is null THEN
862 l_param := 'Navn';
863 hr_utility.set_location ( 'Mandatory parameter is missing', 50);
864 RAISE name_is_null;
865 END IF;
866
867 IF (length(l_name) > 100) THEN
868 l_param := 'Navn';
869 hr_utility.set_location ( ' Record too long', 50);
870 RAISE e_record_too_long;
871 END IF;
872
873 IF l_holidays is null THEN
874 l_param := 'Antal_dage';
875 hr_utility.set_location ( 'Mandatory parameter is missing', 50);
876 RAISE name_is_null;
877 END IF;
878
879 IF (length(l_year) > 4) THEN
880 l_param := 'Optjeningsaar';
881 hr_utility.set_location ( ' Record too long', 50);
882 RAISE e_record_too_long;
883 END IF;
884
885 IF l_year is null THEN
886 l_param := 'Optjeningsaar';
887 hr_utility.set_location ( 'Mandatory parameter is missing', 50);
888 RAISE name_is_null;
889 END IF;
890
891 IF l_start_date is null THEN
892 l_param := 'Ferie_start';
893 hr_utility.set_location ( 'Mandatory parameter is missing', 50);
894 RAISE name_is_null;
895 END IF;
896
897 IF l_ass_no is null THEN
898 l_param := 'Medarbejdernummer';
899 hr_utility.set_location ( 'Mandatory parameter is missing', 50);
900 RAISE name_is_null;
901 END IF;
902
903 IF (length(l_cvr_no) > 11) THEN
904 l_param := 'CPR_nummer';
905 hr_utility.set_location ( ' Record too long', 50);
906 RAISE e_record_too_long;
907 END IF;
908
909
910 FND_FILE.PUT_LINE(fnd_file.log, 'Holiday Pay request made for l_ass_no'||l_ass_no);
911 FND_FILE.PUT_LINE(fnd_file.log, 'Holiday Pay request made for l_cvr_no'||l_cvr_no);
912 FND_FILE.PUT_LINE(fnd_file.log, 'Holiday Pay request made for year'||l_year);
913
914 OPEN csr_le_people(l_ass_no,l_cvr_no,l_start_date);
915 FETCH csr_le_people into rg_le_people;
916 IF csr_le_people%NOTFOUND THEN
917 CLOSE csr_le_people;
918 RAISE person_not_found;
919 END IF;
920 CLOSE csr_le_people;
921
922 OPEN check_run_id (rg_le_people.assg_id , g_run_id);
923 FETCH check_run_id into l_flag ;
924 CLOSE check_run_id;
925 IF l_flag = 1 THEN
926 RAISE assg_already_processed;
927 END IF ;
928
929 l_payroll_id := rg_le_people.payroll_id;
930
931
932 l_uhc_payroll := rg_le_people.holiday_card;
933
934 l_uhc_payroll := NVL(l_uhc_payroll,l_use_holiday_card);
935
936 IF l_uhc_payroll ='N' THEN
937 RAISE holiday_card_no_set;
938 END IF ;
939
940 OPEN csr_ele_link('Override Holiday Duration',l_payroll_id);
941 FETCH csr_ele_link INTO l_ele_link_id ;
942 IF csr_ele_link%NOTFOUND THEN
943 CLOSE csr_ele_link;
944 FND_FILE.PUT_LINE(fnd_file.log, 'No element links for Override Holiday Duration ');
945 hr_utility.raise_error;
946 END IF;
947 CLOSE csr_ele_link;
948
949 OPEN csr_ele_link('Absent Holiday Card Request',l_payroll_id);
950 FETCH csr_ele_link INTO l_ele_link_id;
951 IF csr_ele_link%NOTFOUND THEN
952 CLOSE csr_ele_link;
953 FND_FILE.PUT_LINE(fnd_file.log, 'No element links for Absent Holiday Card Request ');
954 hr_utility.raise_error;
955 END IF;
956 CLOSE csr_ele_link;
957
958 OPEN csr_ele_link('Holiday Card Payment Request',l_payroll_id);
959 FETCH csr_ele_link INTO l_ele_link_id;
960 IF csr_ele_link%NOTFOUND THEN
961 CLOSE csr_ele_link;
962 FND_FILE.PUT_LINE(fnd_file.log, 'No element links for Holiday Card Payment Request ');
963 hr_utility.raise_error;
964 END IF;
965 CLOSE csr_ele_link;
966 l_last_yr := to_char(TRUNC(l_start_date,'YEAR') - 1, 'RRRR');
967
968 l_curr_entit_yr := l_last_yr;
969
970 IF to_char(l_start_date,'MM') <='04' THEN
971
972 l_curr_entit_yr := to_char(to_number(l_last_yr)- 1);
973 END IF;
974 hr_utility.set_location('l_curr_entit_yr = ' ||l_curr_entit_yr,208);
975 IF l_year <> l_curr_entit_yr THEN
976 hr_utility.set_location('l_year = ' ||l_year,209);
977 RAISE wrong_entit_year;
978 END IF;
979
980 l_assg_id := rg_le_people.assg_id;
981 l_5d_6d := rg_le_people.def_wrk_patt;
982 l_5d_6d := NVL(l_5d_6d,l_work_pattern);
983 hr_utility.set_location('l_start_date = ' ||l_start_date,201);
984 hr_utility.set_location('l_holidays = ' ||l_holidays,202);
985 hr_utility.set_location('l_5d_6d = ' ||l_5d_6d,203);
986
987 get_end_date(l_start_date,l_holidays,l_assg_id,l_5d_6d,l_end_date);
988 hr_utility.set_location('l_end_date = ' ||l_end_date,204);
989
990 IF NOT per_abs_bus.per_valid_for_absence
991 (p_person_id => rg_le_people.person_id
992 ,p_business_group_id => g_business_group_id
993 ,p_date_projected_start => null
994 ,p_date_projected_end => null
995 ,p_date_start => l_start_date
996 ,p_date_end => l_end_date)
997 THEN
998 RAISE abs_term_date;
999 END IF;
1000
1001
1002 IF g_test_submission = 'N' THEN
1003
1004
1005 create_absence(g_effective_date, l_start_date ,l_end_date ,g_business_group_id ,rg_le_people.person_id );
1006
1007 create_override_hol ( g_effective_date
1008 , rg_le_people.assg_id
1009 , g_business_group_id
1010 , l_start_date
1011 , l_end_date
1012 , l_holidays
1013 , NULL
1014 , l_year ) ;
1015
1016 FND_FILE.PUT_LINE(fnd_file.log, 'l_assg_id'||l_assg_id);
1017 FND_FILE.PUT_LINE(fnd_file.log, 'l_ass_no'||l_ass_no);
1018 FND_FILE.PUT_LINE(fnd_file.log, 'l_year'||l_year);
1019 FND_FILE.PUT_LINE(fnd_file.log, 'l_cvr_no'||l_cvr_no);
1020 FND_FILE.PUT_LINE(fnd_file.log, 'g_absence_attendance_type_id'||g_absence_attendance_type_id);
1021 FND_FILE.PUT_LINE(fnd_file.log, 'g_company_name'||g_company_name);
1022 FND_FILE.PUT_LINE(fnd_file.log, 'g_cvr_no'||g_cvr_no);
1023 FND_FILE.PUT_LINE(fnd_file.log, 'g_se_no'||g_se_no);
1024
1025
1026 create_timestamp_entry ( l_start_date
1027 , rg_le_people.assg_id
1028 , g_business_group_id
1029 , g_run_id
1030 , g_date_created
1031 , g_time_created );
1032 END IF ;
1033 -- l_proc_tab(proc_cnt).emp_name := g_hol_card_emp(i).emp_name;
1034 -- l_proc_tab(proc_cnt).p_cpr_number := l_cvr_no;
1035 l_proc_tab(proc_cnt).p_assg_number := l_ass_no;
1036 l_proc_tab(proc_cnt).p_absence_start := l_start_date;
1037 l_proc_tab(proc_cnt).p_duration := l_holidays;
1038
1039 OPEN chk_multiple_assg(l_ass_no,l_cvr_no);
1040 FETCH chk_multiple_assg INTO l_assignment_id;
1041 CLOSE chk_multiple_assg;
1042
1043 IF l_assignment_id IS NOT NULL THEN
1044 l_proc_tab(proc_cnt).p_proc_msg := 'Warn: Multiple Assignments present for employee.';
1045
1046 ELSE
1047 l_proc_tab(proc_cnt).p_proc_msg := 'Processed';
1048
1049 END IF;
1050
1051 -- FND_FILE.PUT_LINE(fnd_file.log, 'Holiday Pay request suceeded for'||l_proc_tab(proc_cnt).p_cpr_number);
1052 FND_FILE.PUT_LINE(fnd_file.log, 'Holiday Pay request suceeded for'||l_proc_tab(proc_cnt).p_assg_number);
1053 proc_cnt := proc_cnt + 1;
1054
1055
1056 EXCEPTION
1057 WHEN wrong_entit_year THEN
1058 retcode := g_warning;
1059
1060 fnd_message.set_name('PAY','PAY_DK_WRONG_ENTITLEMENT');
1061
1062 -- Return the message to the ConcMgr (This msg will appear in the log file)
1063 l_err_tab(err_cnt).p_err_msg := fnd_message.get();
1064 FND_FILE.PUT_LINE(fnd_file.log, l_err_tab(err_cnt).p_err_msg);
1065 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1066 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1067 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1068 l_err_tab(err_cnt).p_absence_start := l_start_date;
1069 l_err_tab(err_cnt).p_duration := l_holidays;
1070 err_cnt := err_cnt + 1;
1071
1072 WHEN person_not_found THEN
1073 retcode := g_warning;
1074 fnd_message.set_name('PAY','PAY_INVALID_CPR_NUMBER');
1075
1076 -- Return the message to the ConcMgr (This msg will appear in the log file)
1077 l_err_tab(err_cnt).p_err_msg := fnd_message.get();
1078 FND_FILE.PUT_LINE(fnd_file.log, l_err_tab(err_cnt).p_err_msg);
1079 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1080 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1081 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1082 l_err_tab(err_cnt).p_absence_start := l_start_date;
1083 l_err_tab(err_cnt).p_duration := l_holidays;
1084
1085
1086 err_cnt := err_cnt + 1;
1087
1088
1089
1090 WHEN assg_already_processed THEN
1091 retcode := g_warning;
1092 fnd_message.set_name('PAY','PAY_DK_ASSG_NOT_FOUND');
1093
1094 -- Return the message to the ConcMgr (This msg will appear in the log file)
1095 l_err_tab(err_cnt).p_err_msg := fnd_message.get();
1096 FND_FILE.PUT_LINE(fnd_file.log, l_err_tab(err_cnt).p_err_msg);
1097 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1098 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1099 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1100 l_err_tab(err_cnt).p_absence_start := l_start_date;
1101 l_err_tab(err_cnt).p_duration := l_holidays;
1102 -- l_err_tab(err_cnt).p_err_msg := fnd_message.get_string('PER','HR_ASSG_NOT_FOUND');
1103 err_cnt := err_cnt + 1;
1104
1105 WHEN holiday_card_no_set THEN
1106 retcode := g_warning;
1107 fnd_message.set_name('PAY','PAY_DK_USE_HOLIDAY_CARD');
1108
1109 -- Return the message to the ConcMgr (This msg will appear in the log file)
1110 l_err_tab(err_cnt).p_err_msg := fnd_message.get();
1111 FND_FILE.PUT_LINE(fnd_file.log, l_err_tab(err_cnt).p_err_msg);
1112 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1113 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1114 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1115 l_err_tab(err_cnt).p_absence_start := l_start_date;
1116 l_err_tab(err_cnt).p_duration := l_holidays;
1117 err_cnt := err_cnt + 1;
1118
1119 WHEN abs_term_date THEN
1120 retcode := g_warning;
1121 fnd_message.set_name('PER', 'PER_7715_ABS_TERM_PROJ_DATE');
1122 l_err_tab(err_cnt).p_err_msg := fnd_message.get();
1123 FND_FILE.PUT_LINE(fnd_file.log, l_err_tab(err_cnt).p_err_msg);
1124
1125 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1126 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1127 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1128 l_err_tab(err_cnt).p_absence_start := l_start_date;
1129 l_err_tab(err_cnt).p_duration := l_holidays;
1130 err_cnt := err_cnt + 1;
1131
1132 WHEN e_record_too_long THEN
1133 retcode := g_warning;
1134 l_err_tab(err_cnt).p_err_msg := 'Tag value for tag '||l_param||' too long.';
1135 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1136 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1137 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1138 l_err_tab(err_cnt).p_absence_start := l_start_date;
1139 l_err_tab(err_cnt).p_duration := l_holidays;
1140 err_cnt := err_cnt + 1;
1141 WHEN name_is_null THEN
1142 retcode := g_warning;
1143 l_err_tab(err_cnt).p_err_msg := 'Tag value for tag '||l_param||' is missing.';
1144 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1145 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1146 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1147 l_err_tab(err_cnt).p_absence_start := l_start_date;
1148 l_err_tab(err_cnt).p_duration := l_holidays;
1149 err_cnt := err_cnt + 1;
1150
1151 WHEN OTHERS THEN
1152 retcode := g_warning;
1153 -- l_err_tab(err_cnt).emp_name := g_hol_card_emp(i).emp_name;
1154 -- l_err_tab(err_cnt).p_cpr_number := l_cvr_no;
1155 l_err_tab(err_cnt).p_assg_number := l_ass_no;
1156 l_err_tab(err_cnt).p_absence_start := l_start_date;
1157 l_err_tab(err_cnt).p_duration := l_holidays;
1158 l_err_tab(err_cnt).p_err_msg := 'Failed : '||SQLERRM;
1159 err_cnt := err_cnt + 1;
1160
1161 END;
1162
1163 END LOOP;
1164 hr_utility.set_location(' leaving validate_ecard_details',10);
1165 log_dk_ehol_card_header;
1166 log_dk_ehol_card_body;
1167
1168 END validate_ecard_details;
1169
1170 PROCEDURE printElements(doc xmldom.DOMDocument) IS
1171
1172 nl xmldom.DOMNodeList;
1173 n xmldom.DOMNode;
1174
1175 len number;
1176 k number := 0;
1177 t number := 1;
1178
1179 l_nodename varchar2(1000);
1180 l_param varchar2(100);
1181
1182
1183 TYPE hol_card IS RECORD ( tag_name varchar2(255),
1184 tag_value varchar2(255) );
1185
1186
1187 TYPE hol_card_tab IS TABLE OF hol_card INDEX BY BINARY_INTEGER;
1188
1189 l_hol_card hol_card_tab;
1190
1191 record_value_is_null EXCEPTION;
1192 e_record_too_long EXCEPTION;
1193
1194 BEGIN
1195
1196 hr_utility.set_location(' Entering Procedure PrintElements',10);
1197
1198 -- k:= 0;
1199
1200 -- get all elements
1201
1202 nl := xmldom.getElementsByTagName(doc, '*');
1203
1204 len := xmldom.getLength(nl);
1205
1206 -- loop through elements
1207
1208 FOR i IN 0..len-1 LOOP
1209
1210 n := xmldom.item(nl, i);
1211
1212 l_nodename := xmldom.getNodeName(n);
1213 -- get the text node associated with the element node
1214
1215 n := xmldom.getFirstChild(n);
1216
1217 IF xmldom.getNodeType(n) = xmldom.TEXT_NODE OR l_nodename = 'Navn' THEN
1218 k:= k+1 ;
1219 l_hol_card(k).tag_name := l_nodename;
1220 l_hol_card(k).tag_value := xmldom.getNodeValue(n);
1221 END IF;
1222
1223 end loop;
1224
1225 WHILE TRUE LOOP
1226 if l_hol_card(t).tag_name= 'Indberetningstype' then
1227 g_report_type := l_hol_card(t).tag_value;
1228 elsif l_hol_card(t).tag_name= 'Dannet_dato' then
1229 g_date_created := l_hol_card(t).tag_value;
1230 elsif l_hol_card(t).tag_name= 'Dannet_klokken'then
1231 g_time_created:= l_hol_card(t).tag_value;
1232 elsif l_hol_card(t).tag_name= 'Koersels_ID' then
1233 g_run_id:= l_hol_card(t).tag_value;
1234 elsif l_hol_card(t).tag_name='Genereret_Af' then
1235 g_payroll_name:= l_hol_card(t).tag_value;
1236 elsif l_hol_card(t).tag_name='Version' then
1237 g_version:= l_hol_card(t).tag_value;
1238 elsif l_hol_card(t).tag_name='UDBID' then
1239 g_vendor_id := l_hol_card(t).tag_value;
1240 elsif l_hol_card(t).tag_name='OPDATO' then
1241 g_date_last_changed := l_hol_card(t).tag_value;
1242 elsif l_hol_card(t).tag_name='Virksomheds_Navn' then
1243 g_company_name := l_hol_card(t).tag_value;
1244 elsif l_hol_card(t).tag_name='Virksomheds_CVRnr' then
1245 g_cvr_no := l_hol_card(t).tag_value;
1246 elsif l_hol_card(t).tag_name='Virksomheds_SEnr' then
1247 g_se_no:= l_hol_card(t).tag_value;
1248 end if;
1249 IF l_hol_card(t).tag_name = 'Navn' THEN
1250 EXIT;
1251 END IF;
1252
1253 t:= t+1;
1254
1255 END LOOP;
1256
1257 IF g_run_id IS NULL THEN
1258 l_param := 'RUN_ID';
1259 raise record_value_is_null;
1260 END IF;
1261
1262 IF (length(g_run_id) > 50) THEN
1263 l_param := 'RUN_ID';
1264 hr_utility.set_location ( ' Record too long', 50);
1265 RAISE e_record_too_long;
1266 END IF;
1267
1268 IF g_se_no IS NULL THEN
1269
1270 l_param := 'Virksomheds_SEnr';
1271 raise record_value_is_null;
1272 END IF;
1273
1274 IF (length(g_se_no) > 20) THEN
1275 l_param := 'Virksomheds_SEnr';
1276 hr_utility.set_location ( ' Record too long', 50);
1277 RAISE e_record_too_long;
1278 END IF;
1279
1280 IF g_cvr_no IS NULL THEN
1281 l_param := 'Virksomheds_CVRnr';
1282 raise record_value_is_null;
1283 END IF;
1284
1285 IF (length(g_cvr_no) > 20) THEN
1286 l_param := 'Virksomheds_CVRnr';
1287 hr_utility.set_location ( ' Record too long', 50);
1288 RAISE e_record_too_long;
1289 END IF;
1290
1291 IF g_company_name IS NULL THEN
1292 l_param := 'Virksomheds_Navn';
1293 raise record_value_is_null;
1294 END IF;
1295
1296 IF (length(g_company_name) > 255) THEN
1297 l_param := 'Virksomheds_Navn';
1298 hr_utility.set_location ( ' Record too long', 50);
1299 RAISE e_record_too_long;
1300 END IF;
1301
1302 g_count_employee := 0;
1303 FOR j IN t..k LOOP
1304
1305 IF l_hol_card(j).tag_name = 'Navn' THEN
1306 g_count_employee:= g_count_employee+1;
1307 g_hol_card_emp(g_count_employee).emp_name := l_hol_card(j).tag_value;
1308 elsif l_hol_card(j).tag_name = 'Medarbejdernummer' then
1309 g_hol_card_emp(g_count_employee).assignmnet_number := l_hol_card(j).tag_value;
1310 elsif l_hol_card(j).tag_name = 'CPR_nummer' then
1311 g_hol_card_emp(g_count_employee).cpr_number := l_hol_card(j).tag_value;
1312 elsif l_hol_card(j).tag_name = 'Reference4' then
1313 g_hol_card_emp(g_count_employee).hire_date := l_hol_card(j).tag_value;
1314 elsif l_hol_card(j).tag_name = 'Ferie_start' then
1315 g_hol_card_emp(g_count_employee).start_date := l_hol_card(j).tag_value;
1316 elsif l_hol_card(j).tag_name = 'Antal_dage' then
1317 g_hol_card_emp(g_count_employee).no_of_hol := l_hol_card(j).tag_value;
1318 elsif l_hol_card(j).tag_name = 'Optjeningsaar' then
1319 g_hol_card_emp(g_count_employee).year := l_hol_card(j).tag_value;
1320 END IF;
1321
1322
1323 END LOOP;
1324 fnd_file.put_line(fnd_file.log,'Total employees requesting E-Holiday absence = ' ||g_count_employee);
1325 hr_utility.set_location ('Total employees requesting E-Holiday absence = ' ||g_count_employee,400);
1326 hr_utility.set_location(' leaving Procedure printelements',10);
1327
1328 EXCEPTION
1329 WHEN record_value_is_null THEN
1330
1331 raise_application_error(-20120, 'Tag value for mandatory tag '||l_param||' is missing.');
1332
1333 WHEN e_record_too_long THEN
1334
1335 raise_application_error(-20120, 'Tag value for tag '||l_param||' too long.');
1336
1337 END printElements;
1338
1339
1340 PROCEDURE read_hol_card( errbuf OUT NOCOPY VARCHAR2
1341 , retcode OUT NOCOPY VARCHAR2
1342 , p_filename IN VARCHAR2
1343 , p_effective_date IN VARCHAR2
1344 , p_business_group_id IN number
1345 , p_absence_type_id IN NUMBER
1346 , test_submission IN VARCHAR2
1347 ) IS
1348
1349 p xmlparser.parser;
1350
1351 doc xmldom.DOMDocument;
1352
1353 l_location VARCHAR2(2000);
1354
1355 c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
1356 p_legislation_code per_business_groups.legislation_code%TYPE;
1357
1358 CURSOR csr_leg (v_bg_id per_business_groups.business_group_id%TYPE)
1359 IS
1360 SELECT legislation_code
1361 FROM per_business_groups
1362 WHERE business_group_id = v_bg_id;
1363
1364 l_test_submission varchar2(1);
1365
1366 e_fatal_error exception;
1367
1368 BEGIN
1369
1370 hr_utility.set_location(' Entering Procedure READ_HOL_CARD',10);
1371
1372
1373 OPEN csr_leg (p_business_group_id);
1374 FETCH csr_leg INTO p_legislation_code;
1375 CLOSE csr_leg;
1376
1377 g_effective_date := fnd_date.canonical_to_date(p_effective_date);
1378 g_business_group_id := p_business_group_id;
1379 g_absence_attendance_type_id := p_absence_type_id;
1380 g_test_submission := test_submission;
1381
1382 hr_utility.set_location ( 'Legislation = ' || p_legislation_code, 20);
1383 hr_utility.set_location ( 'p_absence_type_id = ' || p_absence_type_id, 21);
1384 hr_utility.set_location ( 'business group id = ' || g_business_group_id, 22);
1385 hr_utility.set_location ( 'effective date = ' || g_effective_date, 23);
1386 hr_utility.set_location ( 'g_test_submission = ' || g_test_submission, 24);
1387
1388 -- p := xmlparser.newParser;
1389 -- xmlparser.setValidationMode(p, FALSE);
1390
1391 fnd_profile.get(c_data_exchange_dir, l_location);
1392 IF l_location IS NULL
1393 THEN
1394 -- error : I/O directory not defined
1395 RAISE e_fatal_error;
1396 END IF;
1397 l_location := l_location||'/'||p_filename;
1398
1399 hr_utility.set_location ( 'location: '||l_location,30);
1400 p := xmlparser.newParser;
1401 xmlparser.setValidationMode(p, FALSE);
1402
1403 xmlparser.parse(p, l_location);
1404 doc := xmlparser.getDocument(p);
1405 printElements(doc);
1406 validate_ecard_details(g_business_group_id,g_effective_date,retcode);
1407 hr_utility.set_location(' leaving Procedure READ_HOL_CARD',10);
1408
1409 EXCEPTION
1410 WHEN e_fatal_error
1411 -- No directory specified
1412 THEN
1413 -- Set retcode to 2, indicating an ERROR to the ConcMgr
1414 retcode := g_error;
1415 -- Set the application error
1416 hr_utility.set_message (800, 'HR_78040_DATA_EXCHANGE_DIR_MIS');
1417 -- Return the message to the ConcMgr (This msg will appear in the log file)
1418 errbuf := hr_utility.get_message;
1419
1420 when xmldom.INDEX_SIZE_ERR then
1421 raise_application_error(-20120, 'Index Size error');
1422
1423 when xmldom.DOMSTRING_SIZE_ERR then
1424 raise_application_error(-20120, 'String Size error');
1425
1426 when xmldom.HIERARCHY_REQUEST_ERR then
1427 raise_application_error(-20120, 'Hierarchy request error');
1428
1429 when xmldom.WRONG_DOCUMENT_ERR then
1430 raise_application_error(-20120, 'Wrong doc error');
1431
1432 when xmldom.INVALID_CHARACTER_ERR then
1433 raise_application_error(-20120, 'Invalid Char error');
1434
1435 when xmldom.NO_DATA_ALLOWED_ERR then
1436 raise_application_error(-20120, 'Nod data allowed error');
1437
1438 when xmldom.NO_MODIFICATION_ALLOWED_ERR then
1439 raise_application_error(-20120, 'No mod allowed error');
1440
1441 when xmldom.NOT_FOUND_ERR then
1442 raise_application_error(-20120, 'Not found error');
1443
1444 when xmldom.NOT_SUPPORTED_ERR then
1445 raise_application_error(-20120, 'Not supported error');
1446
1447 when xmldom.INUSE_ATTRIBUTE_ERR then
1448 raise_application_error(-20120, 'In use attr error');
1449
1450 WHEN others then
1451 fnd_file.put_line(fnd_file.log,'exception: '||SQLERRM);
1452 fnd_file.put_line(fnd_file.log,'exception: '||SQLCODE);
1453 hr_utility.raise_error;
1454
1455 END read_hol_card;
1456
1457
1458 END PAY_DK_EHOL_CARD_REQUEST;