[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_IZA_UPLOAD
Source
1 PACKAGE BODY PAY_NL_IZA_UPLOAD AS
2 /* $Header: pynlizau.pkb 120.0 2005/05/29 06:55:42 appldev noship $ */
3
4 -- The package for IZA upload process
5
6 -- Global package name
7 g_package CONSTANT VARCHAR2 (400) := ' pay_nl_iza_upload';
8
9 g_batch_header VARCHAR2 (50) ;
10 g_batch_source VARCHAR2 (50) ;
11 g_batch_comments VARCHAR2 (100);
12 g_debug BOOLEAN;
13
14 c_update_action_if_exists VARCHAR2 (1);
15 c_default_dt_effective_changes VARCHAR2 (1);
16 c_read_file VARCHAR2 (1);
17 c_max_linesize NUMBER ;
18 c_data_exchange_dir VARCHAR2 (30);
19
20 -- Global for element_type_id of "Nominal IZA Contributions" element.
21
22 g_element_type_id pay_element_types_f.element_type_id%TYPE;
23
24 -- Global constants
25
26 c_error CONSTANT NUMBER := 1;
27
28
29
30 /*--------------------------------------------------------------------
31 |Name : iza_upload |
32 |Type : Procedure |
33 |Description: This Procedure initiates the IZA upload process. It |
34 | takes in the parameters passed from the information in |
35 | concurrent program definition and calls various |
36 | procedures for inserting data into pay_batch_headers |
37 | and pay_batch_lines table. |
38 ----------------------------------------------------------------------*/
39
40
41
42
43 PROCEDURE iza_upload( errbuf OUT NOCOPY VARCHAR2,
44 retcode OUT NOCOPY NUMBER,
45 p_file_name IN VARCHAR2,
46 p_batch_name IN VARCHAR2,
47 p_effective_date IN VARCHAR2,
48 p_business_group_id IN per_business_groups.business_group_id%TYPE,
49 p_action_if_exists IN VARCHAR2 DEFAULT NULL,
50 p_dummy_action_if_exists IN VARCHAR2 DEFAULT NULL,
51 p_date_effective_changes IN VARCHAR2 DEFAULT NULL
52 ) IS
53
54 -- Procedure name
55
56 l_proc VARCHAR2 (72) ;
57 l_legislation_code per_business_groups.legislation_code%TYPE;
58
59 -- File Handling variables
60 l_file_handle UTL_FILE.file_type;
61 l_filename VARCHAR2 (240);
62 l_location VARCHAR2 (400);
63 l_line_read VARCHAR2 (400);
64
65
66
67 -- Batch Variables
68 l_batch_seq NUMBER;
69 l_input_line_num NUMBER;
70
71 -- Variables to hold returning values from procedure calls
72 l_batch_id NUMBER;
73 l_batch_line_id NUMBER;
74 l_ovn NUMBER;
75 l_bl_ovn NUMBER;
76
77 -- Other local Variables
78 l_count number;
79 l_process_yr_mm varchar2(10);
80 l_payroll_center varchar2(80);
81 l_period_eff_start_date date;
82 l_period_eff_end_date date;
83 l_client_num VARCHAR2(10);
84 l_rec_client_num VARCHAR2(10);
85 l_sub_emplr_num VARCHAR2(10);
86 l_rec_sub_emplr_num VARCHAR2(10);
87 l_province_code VARCHAR2(10);
88 l_process_status VARCHAR2(10);
89 l_emp_name VARCHAR2(230);
90 l_date_of_birth date;
91 l_last_name VARCHAR2(150);
92 l_prefix VARCHAR2(30);
93 l_initials VARCHAR2(150);
94 l_participant_number number;
95
96 l_org_id number;
97 l_org_struct_version_id number;
98 l_rec_org_id number;
99 l_employee_number varchar2(30);
100
101
102 -- Exceptions
103 e_fatal_error EXCEPTION;
104 e_org_id EXCEPTION;
105
106
107 cursor csr_iza_info(v_org_id number) IS
108 select ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3
109 from HR_ORGANIZATION_INFORMATION
110 where ORG_INFORMATION_CONTEXT='NL_IZA_REPO_INFO'
111 and organization_id = v_org_id;
112
113
114 cursor csr_organization_id(v_client_number varchar2,v_sub_emplr_number varchar2,v_bg_id number) IS
115 select hoi.organization_id
116 from HR_ORGANIZATION_INFORMATION hoi, HR_ORGANIZATION_UNITS hou
117 where ORG_INFORMATION_CONTEXT='NL_IZA_REPO_INFO'
118 and lpad(ORG_INFORMATION1,3,'0') = v_client_number
119 and lpad(ORG_INFORMATION2,3,'0') = v_sub_emplr_number
120 and hou.ORGANIZATION_ID = hoi.organization_id
121 and hou.business_group_id = v_bg_id;
122
123 CURSOR csr_employee_info(v_business_group_id number,
124 v_person_id number,
125 v_period_start_date Date,
126 v_period_end_date Date
127 ) IS
128 SELECT
129 paa.organization_id org_id,
130 ltrim(substr(pap.employee_number,1,9),'0') employee_number
131 ,pap.PER_INFORMATION1 initials
132 ,pap.PRE_NAME_ADJUNCT prefix
133 ,pap.LAST_NAME last_name
134 ,pap.date_of_birth
135 ,pap.per_information15
136 FROM
137 per_all_people_f pap
138 ,per_all_assignments_f paa
139 WHERE pap.business_group_id = v_business_group_id
140 and pap.person_id = v_person_id
141 and pap.person_id = paa.person_id
142 and v_period_end_date between pap.effective_start_date and pap.effective_end_date
143 and paa.effective_start_date =
144 (
145 SELECT MIN(asg.effective_start_date)
146 FROM per_assignment_status_types past, per_all_assignments_f asg
147 WHERE asg.assignment_id = paa.assignment_id
148 and past.per_system_status = 'ACTIVE_ASSIGN'
149 and asg.assignment_status_type_id = past.assignment_status_type_id
150 and asg.effective_start_date <= v_period_End_Date
151 and asg.effective_end_date >= v_period_Start_Date
152 );
153
154
155 CURSOR csr_missing_employees(v_business_group_id number,
156 v_period_start_date Date,
157 v_period_end_date Date,
158 v_org_struct_version_id number
159 ) IS
160 SELECT
161 pap.person_id person_id
162 FROM
163 per_all_people_f pap
164 ,per_all_assignments_f paa
165 ,PER_ASSIGNMENT_EXTRA_INFO pae_iza
166 ,PER_ASSIGNMENT_EXTRA_INFO pae_sii
167 WHERE pap.business_group_id = v_business_group_id
168 and pap.person_id = paa.person_id
169 and paa.primary_flag='Y'
170 and v_period_end_date between pap.effective_start_date and pap.effective_end_date
171 and paa.effective_start_date =
172 (
173 SELECT MIN(asg.effective_start_date)
174 FROM per_assignment_status_types past, per_all_assignments_f asg
175 WHERE asg.assignment_id = paa.assignment_id
176 and past.per_system_status = 'ACTIVE_ASSIGN'
177 and asg.assignment_status_type_id = past.assignment_status_type_id
178 and asg.effective_start_date <= v_period_End_Date
179 and asg.effective_end_date >= v_period_Start_Date
180 )
181 and paa.organization_id in (select distinct piza.organization_id iza_org_id
182 from pay_nl_iza_upld_status piza
183 where piza.process_year_month = v_period_End_Date
184 AND lpad(piza.employer_number,3,'0') = l_rec_client_num
185 AND piza.business_group_id = p_business_group_id
186
187 UNION
188
189 (
190 SELECT iza_org_id from hr_organization_information e,(
191 SELECT distinct pose.organization_id_child iza_org_id
192 FROM per_org_structure_elements pose
193 where pose.org_structure_version_id = v_org_struct_version_id
194 START WITH pose.organization_id_parent in (select distinct piza.organization_id
195 from pay_nl_iza_upld_status piza
196 where piza.process_year_month = v_period_End_Date
197 AND lpad(piza.employer_number,3,'0') = l_rec_client_num
198 AND piza.business_group_id = p_business_group_id
199 )
200 CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
201
202 MINUS
203
204 SELECT iza_org_id from hr_organization_information e1,(
205 SELECT distinct pose.organization_id_child iza_org_id
206 FROM per_org_structure_elements pose
207 where pose.org_structure_version_id = v_org_struct_version_id
208 START WITH pose.organization_id_parent in (select distinct piza.organization_id
209 from pay_nl_iza_upld_status piza
210 where piza.process_year_month = v_period_End_Date
211 AND lpad(piza.employer_number,3,'0') = l_rec_client_num
212 AND piza.business_group_id = p_business_group_id
213 )
214 CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
215 where
216 e1.organization_id=iza_org_id and
217 e1.org_information_context = 'NL_IZA_REPO_INFO'
218 AND e1.org_information1 IS NOT NULL
219 AND e1.org_information2 IS NOT NULL
220 )
221
222 )
223
224 and paa.assignment_id = pae_iza.assignment_id
225 and pae_iza.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
226 and v_period_end_date >= fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION1)
227 and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION2),v_period_start_date)
228 and pae_sii.AEI_INFORMATION_CATEGORY = 'NL_SII'
229 and pae_sii.AEI_INFORMATION3 in ('ZFW','AMI')
230 and pae_sii.AEI_INFORMATION4 = '4'
231 and v_period_end_date >= fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION1)
232 and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION2),v_period_start_date)
233 and paa.assignment_id = pae_iza.assignment_id
234 and pae_iza.assignment_id = pae_sii.assignment_id
235 minus
236
237 SELECT
238 piza1.person_id
239 FROM pay_nl_iza_upld_status piza1
240 WHERE piza1.process_year_month = v_period_End_Date
241 AND lpad(piza1.employer_number,3,'0') = l_rec_client_num
242 AND piza1.business_group_id = p_business_group_id;
243
244 cursor csr_org_struct_version_id(v_bg_id number,v_period_end_date date) IS
245 SELECT sv.org_structure_version_id
246 FROM per_org_structure_versions sv
247 WHERE sv.organization_structure_id in
248 (
249 SELECT TO_NUMBER(inf.org_information1) organization_structure_id
250 FROM hr_organization_information inf
251 WHERE inf.organization_id = v_bg_id
252 AND inf.org_information_context = 'NL_BG_INFO'
253 AND inf.org_information1 IS NOT NULL
254 )
255 AND v_period_end_date BETWEEN sv.date_from
256 AND NVL(sv.date_to, Hr_general.End_Of_time);
257
258
259
260
261 BEGIN
262
263
264 -- g_debug := TRUE;
265
266
267 if g_debug then
268 hr_utility.trace_on(NULL,'IZA');
269 -- input parameters
270 hr_utility.set_location('p_file_name '||p_file_name,250);
271 hr_utility.set_location('p_effective_date '||p_effective_date,250);
272 hr_utility.set_location('p_business_group_id '||p_business_group_id,250 );
273 hr_utility.set_location('p_action_if_exists '||p_action_if_exists,250);
274 hr_utility.set_location('p_date_effective_changes '||p_date_effective_changes,250);
275 hr_utility.set_location('p_batch_name '||p_batch_name,250);
276 hr_utility.set_location ( 'Entering:' || l_proc, 250);
277
278 end if;
279
280 c_read_file := 'r';
281 c_max_linesize := 400;
282 c_data_exchange_dir := 'PER_DATA_EXCHANGE_DIR';
283
284 g_batch_header := hr_general.decode_lookup('HR_NL_REPORT_LABELS', 'IZA_BATCH_HEADER');
285 g_batch_source := hr_general.decode_lookup('HR_NL_REPORT_LABELS', 'IZA_BATCH_SOURCE');
286
287
288 l_proc := g_package || 'iza_upload ';
289 l_line_read := NULL;
290
291
292 -- Batch Variables
293 l_batch_seq := 0;
294
295 l_count := 0;
296
297 g_element_type_id := pay_nl_general.get_element_type_id('Nominal IZA Contribution',fnd_date.canonical_to_date(p_effective_date));
298
299
300 l_filename := p_file_name;
301 fnd_profile.get (c_data_exchange_dir, l_location);
302
303 if g_debug then
304 hr_utility.set_location ( 'directory = ' || l_location, 270);
305 end if;
306
307 IF l_location IS NULL
308 THEN
309 -- error : I/O directory not defined
310 RAISE e_fatal_error;
311 END IF;
312
313
314 -- Opening flat file from the specified directory
315 l_file_handle :=
316 UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
317
318
319 -- Loop over the file, reading in each line. GET_LINE will
320 -- raise NO_DATA_FOUND when it is done, so we use that as the
321 -- exit condition for the loop
322
323
324 <<read_lines_in_file>>
325 LOOP
326 BEGIN
327
328 -- read the file line by line into a string
329
330 UTL_FILE.get_line (l_file_handle, l_line_read);
331
332 -- check if the record is Opening Record
333
334 if substr(l_line_read,1,1) ='1' then
335
336
337 -- if the record is an opening record then take the process year month and
338 -- payroll center in the local variables
339
340 l_process_yr_mm:=substr(l_line_read,2,6);
341 l_payroll_center:=substr(l_line_read,8,30);
342 l_period_eff_start_date:=to_date(l_process_yr_mm,'YYYYMM');
343 l_period_eff_end_date:=last_day(to_date(l_process_yr_mm,'YYYYMM'));
344
345
346
347 -- Create the Batch header for every opening record
348
349 -- This create_batch_header procedure is a wrapper over the core
350 -- create_batch_header procedure defined in PAY_BATCH_ELEMENT_ENTRY_API
351
352 create_batch_header
353 (p_effective_date=> fnd_date.canonical_to_date (p_effective_date)
354 ,p_name => p_batch_name
355 ,p_bg_id => p_business_group_id
356 ,p_action_if_exists => NVL (p_action_if_exists, c_default_action_if_exists)
357 ,p_date_effective_changes => p_date_effective_changes
358 ,p_batch_id => l_batch_id
359 ,p_ovn => l_ovn );
360
361 l_batch_seq := l_batch_seq + 1;
362 end if;
363
364 -- exceptions handling
365
366 EXCEPTION
367 WHEN VALUE_ERROR
368 -- Input line too large for buffer specified in UTL_FILE.fopen
369 THEN
370 IF UTL_FILE.is_open (l_file_handle)
371 THEN
372 UTL_FILE.fclose (l_file_handle);
373 END IF;
374
375 if g_debug then
376 hr_utility.set_location (l_proc, 350);
377 end if;
378
379 retcode := c_error;
380 l_input_line_num := l_batch_seq + 1;
381 -- The error will mean batch_seq doesn't get upped so add 1 when
382 -- reporting line
383 errbuf := 'Input line (line nr = '|| l_input_line_num || ') too large for buffer (=' || c_max_linesize || ').';
384
385 EXIT;
386
387 -- when the file reaches the end, NO_DATA_FOUND exception would be raised and the
388 -- file should be closed
389
390 WHEN NO_DATA_FOUND
391 THEN
392 EXIT;
393 END;
394
395 if g_debug then
396
397 hr_utility.set_location ( 'line read: ' || SUBSTR (l_line_read, 1, 40), 350);
398
399 end if;
400
401 -- Performing the necessary actions for a Data Record
402
403 IF substr(l_line_read,1,1) = '2' then
404
405 l_rec_org_id := NULL;
406
407 l_rec_client_num:= SUBSTR (l_line_read,4,3);
408 l_rec_sub_emplr_num := SUBSTR (l_line_read,22,3);
409
410 if g_debug then
411 hr_utility.set_location ( 'l_rec_client_num: ' || SUBSTR (l_rec_client_num, 1, 40), 350);
412 hr_utility.set_location ( 'l_rec_sub_emplr_num: ' || SUBSTR (l_rec_sub_emplr_num, 1, 40), 350);
413 end if;
414
415 OPEN csr_organization_id(l_rec_client_num,l_rec_sub_emplr_num,p_business_group_id);
416 FETCH csr_organization_id INTO l_rec_org_id;
417 IF l_rec_org_id IS NULL THEN
418 RAISE e_org_id;
419 END IF;
420 CLOSE csr_organization_id;
421
422 if g_debug then
423 hr_utility.set_location ( 'l_rec_org_id: ' || SUBSTR (l_rec_org_id, 1, 40), 350);
424 end if;
425
426
427 OPEN csr_org_struct_version_id(p_business_group_id,l_period_eff_end_date);
428 FETCH csr_org_struct_version_id INTO l_org_struct_version_id;
429 CLOSE csr_org_struct_version_id;
430
431 if g_debug then
432 hr_utility.set_location ( 'l_org_struct_version_id: ' || SUBSTR (l_org_struct_version_id, 1, 40), 350);
433 end if;
434
435 val_create_batch_line(l_line_read,l_batch_id,l_batch_seq,l_process_yr_mm,l_payroll_center,l_rec_org_id,l_org_struct_version_id,p_business_group_id,fnd_date.canonical_to_date(p_effective_date),l_batch_line_id,l_bl_ovn);
436
437 END IF;
438
439 -- this is the case of a Closing Record
440
441 IF substr(l_line_read,1,1) = '3' then
442
443 FOR csr_missing_employees_rec in csr_missing_employees(p_business_group_id,l_period_eff_start_date,l_period_eff_end_date,l_org_struct_version_id)
444 LOOP
445
446 OPEN csr_employee_info( p_business_group_id,
447 csr_missing_employees_rec.person_id,
448 l_period_eff_start_date,
449 l_period_eff_end_date
450 ) ;
451 FETCH csr_employee_info INTO l_org_id,l_employee_number,l_initials,l_prefix,l_last_name,l_date_of_birth,l_participant_number;
452 CLOSE csr_employee_info;
453
454
455 OPEN csr_iza_info(l_rec_org_id);
456 FETCH csr_iza_info INTO l_client_num,l_sub_emplr_num,l_province_code;
457 CLOSE csr_iza_info;
458
459 if g_debug then
460
461 -- input parameters
462 hr_utility.set_location('p_business_group_id '||p_business_group_id,400);
463 hr_utility.set_location('l_org_id '||l_org_id,400);
464 hr_utility.set_location('l_sub_emplr_num '||l_sub_emplr_num,400 );
465 hr_utility.set_location('csr_missing_employees_rec.person_id '||csr_missing_employees_rec.person_id,400);
466 hr_utility.set_location('l_employee_number '||l_employee_number,400);
467 hr_utility.set_location('l_process_status '||l_process_status,400);
468 hr_utility.set_location('l_province_code '||l_province_code,400);
469 hr_utility.set_location('l_participant_number '||l_participant_number,400);
470 hr_utility.set_location('l_last_name '||l_last_name,400);
471 hr_utility.set_location('l_initials '||l_initials,400);
472 hr_utility.set_location('l_prefix '||l_prefix,400);
473
474 end if;
475
476 l_process_status:='MISSING';
477 insert into PAY_NL_IZA_UPLD_STATUS( BUSINESS_GROUP_ID
478 ,ORGANIZATION_ID
479 ,EMPLOYER_NUMBER
480 ,SUB_EMPLOYER_NUMBER
481 ,PAYROLL_CENTER
482 ,PROCESS_YEAR_MONTH
483 ,PERSON_ID
484 ,EMPLOYEE_NUMBER
485 ,PROCESS_STATUS
486 ,PROVINCE_CODE
487 ,DATE_OF_BIRTH
488 ,PARTICIPANT_NUMBER
489 ,EMPLOYEE_NAME
490 ,CONTRIBUTION_1
491 ,CORRECTION_CONTRIBUTION_1
492 ,DATE_CORRECTION_1
493 ,CONTRIBUTION_2
494 ,CORRECTION_CONTRIBUTION_2
495 ,DATE_CORRECTION_2
496 ,REJECT_REASON)
497 values (p_business_group_id
498 ,l_rec_org_id
499 ,l_client_num
500 ,l_sub_emplr_num
501 ,NULL
502 ,l_period_eff_end_date
503 ,csr_missing_employees_rec.person_id
504 ,l_employee_number
505 ,l_process_status
506 ,l_province_code
507 ,l_date_of_birth
508 ,l_participant_number
509 ,l_last_name || ' ' || l_initials || ' ' || l_prefix
510 ,NULL
511 ,NULL
512 ,NULL
513 ,NULL
514 ,NULL
515 ,NULL
516 ,NULL);
517 END LOOP;
518
519
520 END IF;
521
522
523 END LOOP read_lines_in_file;
524
525
526 -- Commit the outstanding records
527 COMMIT;
528 UTL_FILE.fclose (l_file_handle);
529
530 if g_debug then
531 hr_utility.set_location ( 'Leaving:'|| l_proc, 500);
532 end if;
533
534 -- Most off these exceptions are not translated as they should not happen normally
535 -- If they do happen, something is seriously wrong and SysAdmin interference will be necessary.
536
537
538 -- exceptions for file handling
539
540 EXCEPTION
541
542 WHEN e_org_id THEN
543 -- Close the file in case off error
544 ROLLBACK;
545 IF UTL_FILE.is_open (l_file_handle) THEN
546 UTL_FILE.fclose (l_file_handle);
547 END IF;
548
549 if g_debug then
550 hr_utility.set_location (l_proc, 500);
551 end if;
552 -- Set retcode to 2, indicating an ERROR to the ConcMgr
553 retcode := 2;
554
555 fnd_message.set_name('PAY','PAY_NL_ORG_ID');
556 fnd_message.set_token('ERNUM',l_rec_client_num);
557 fnd_message.set_token('SUBERNUM',l_rec_sub_emplr_num);
558
559 -- Return the message to the ConcMgr (This msg will appear in the log file)
560 errbuf := fnd_message.get();
561
562
563
564 WHEN e_fatal_error
565 -- No directory specified
566 THEN
570 UTL_FILE.fclose (l_file_handle);
567 -- Close the file in case off error
568 IF UTL_FILE.is_open (l_file_handle)
569 THEN
571 END IF;
572
573 if g_debug then
574 hr_utility.set_location (l_proc, 500);
575 end if;
576
577 -- Set retcode to 2, indicating an ERROR to the ConcMgr
578 retcode := c_error;
579
580 -- Set the application error
581
582 hr_utility.set_message (800, 'HR_78040_DATA_EXCHANGE_DIR_MIS');
583
584 -- Return the message to the ConcMgr (This msg will appear in the log file)
585 errbuf := hr_utility.get_message;
586
587 WHEN UTL_FILE.invalid_operation
588 -- File could not be opened as requested, perhaps because of operating system permissions
589 -- Also raised when attempting a write operation on a file opened for read, or a read operation
590 -- on a file opened for write.
591
592 THEN
593 IF UTL_FILE.is_open (l_file_handle)
594 THEN
595 UTL_FILE.fclose (l_file_handle);
596 END IF;
597
598 if g_debug then
599 hr_utility.set_location (l_proc, 550);
600 end if;
601
602 retcode := c_error;
603
604 fnd_message.set_name('PAY','PAY_NL_FILE_INVALID_OPERATION');
605 fnd_message.set_token('FILENAME',l_filename);
606 fnd_message.set_token('LOCATION',l_location);
607
608 errbuf := fnd_message.get();
609
610
611 WHEN UTL_FILE.internal_error
612 -- Unspecified internal error
613 THEN
614 IF UTL_FILE.is_open (l_file_handle)
615 THEN
616 UTL_FILE.fclose (l_file_handle);
617 END IF;
618
619 if g_debug then
620 hr_utility.set_location (l_proc, 550);
621 end if;
622
623 retcode := c_error;
624
625 fnd_message.set_name('PAY','PAY_NL_FILE_INTERNAL_ERROR');
626 fnd_message.set_token('FILENAME',l_filename);
627 fnd_message.set_token('LOCATION',l_location);
628
629 errbuf := fnd_message.get();
630
631
632 WHEN UTL_FILE.invalid_mode
633 -- Invalid string specified for file mode
634 THEN
635 IF UTL_FILE.is_open (l_file_handle)
636 THEN
637 UTL_FILE.fclose (l_file_handle);
638 END IF;
639
640 if g_debug then
641 hr_utility.set_location (l_proc, 550);
642 end if;
643
644 retcode := c_error;
645
646 fnd_message.set_name('PAY','PAY_NL_FILE_INVALID_MODE');
647 fnd_message.set_token('FILENAME',l_filename);
648 fnd_message.set_token('LOCATION',l_location);
649
650 errbuf := fnd_message.get();
651
652
653 WHEN UTL_FILE.invalid_path
654 -- Directory or filename is invalid or not accessible
655 THEN
656 IF UTL_FILE.is_open (l_file_handle)
657 THEN
658 UTL_FILE.fclose (l_file_handle);
659 END IF;
660
661 retcode := c_error;
662
663 fnd_message.set_name('PAY','PAY_NL_FILE_INVALID_PATH');
664
665 errbuf := fnd_message.get();
666
667
668 if g_debug then
669 hr_utility.set_location (l_proc, 550);
670 end if;
671
672 WHEN UTL_FILE.invalid_filehandle
673 -- File handle does not specify an open file
674 THEN
675 IF UTL_FILE.is_open (l_file_handle)
676 THEN
677 UTL_FILE.fclose (l_file_handle);
678 END IF;
679
680 if g_debug then
681 hr_utility.set_location (l_proc, 550);
682 end if;
683
684 retcode := c_error;
685
686
687 fnd_message.set_name('PAY','PAY_NL_INVALID_FILE_HANDLE');
688 fnd_message.set_token('FILENAME',l_filename);
689 fnd_message.set_token('LOCATION',l_location);
690
691 errbuf := fnd_message.get();
692
693
694 WHEN UTL_FILE.read_error
695
696 -- Operating system error occurred during a read operation
697 THEN
698 IF UTL_FILE.is_open (l_file_handle)
699 THEN
700 UTL_FILE.fclose (l_file_handle);
701 END IF;
702
703 if g_debug then
704 hr_utility.set_location (l_proc, 650);
705 end if;
706
707
708 fnd_message.set_name('PAY','PAY_NL_FILE_READ_ERROR');
709 fnd_message.set_token('FILENAME',l_filename);
710 fnd_message.set_token('LOCATION',l_location);
711
712 errbuf := fnd_message.get();
713
714
715
716 END iza_upload;
717
718
719 /*--------------------------------------------------------------------
720 |Name : create_batch_header |
721 |Type : Procedure |
722 |Description: This procedure is a wrapper over the core |
723 | create_batch_header procedure defined in |
724 | PAY_BATCH_ELEMENT_ENTRY_API |
725 ----------------------------------------------------------------------*/
726
727
728 -- The IN Parameters are
729 -- p_effective_date -> the effective date
730 -- p_name -> the batch name
731 -- p_bg_id -> the business group id
732 -- p_action_if_exists -> The action that needs to be taken when the entry already exists
733 -- Possible values are 'I' (Insert), 'R' (Reject) or 'U' (Update)
734 -- p_date_effective_changes -> The date effective change that needs to happen
735 -- Possible values are 'C' (Correct), 'O' (Override) or 'U' (Update)
736 -- This should only be used if p_action_if_exists = 'U'
737 --
738 -- The OUT Parameters are
739 -- p_batch_id -> the batch id of the created batch header
740 -- p_ovn -> the object version number of the created batch header
741
742
743 PROCEDURE create_batch_header (
747 p_action_if_exists IN VARCHAR2 DEFAULT c_default_action_if_exists,
744 p_effective_date IN DATE,
745 p_name IN VARCHAR2,
746 p_bg_id IN NUMBER,
748 p_date_effective_changes IN VARCHAR2 DEFAULT NULL,
749 p_batch_id OUT NOCOPY NUMBER,
750 p_ovn OUT NOCOPY NUMBER
751 )
752 IS
753 l_proc VARCHAR2 (72);
754 l_date_effective_changes VARCHAR2 (30);
755 c_batch_reference VARCHAR2 (50);
756 c_batch_source VARCHAR2 (50);
757 c_comments VARCHAR2 (100);
758
759 BEGIN
760
761 l_date_effective_changes := NULL;
762 c_batch_reference := g_batch_header;
763 c_batch_source := g_batch_source;
764 c_comments := g_batch_comments;
765 l_proc := g_package || 'create_batch_header';
766 c_update_action_if_exists := 'U'; --Update existing element entry;
767 c_default_dt_effective_changes := 'C'; --Update/Change Insert;
768
769
770
771 if g_debug then
772 hr_utility.set_location ( 'Entering:'
773 || l_proc, 700);
774 end if;
775
776 -- CREATE_BATCH_HEADER definition
777 /****************************************************************
778 procedure create_batch_header
779 (p_validate in boolean default false
780 ,p_session_date in date
781 ,p_batch_name in varchar2
782 ,p_batch_status in varchar2 default 'U'
783 ,p_business_group_id in number
784 ,p_action_if_exists in varchar2 default 'R'
785 ,p_batch_reference in varchar2 default null
786 ,p_batch_source in varchar2 default null
787 ,p_comments in varchar2 default null
788 ,p_date_effective_changes in varchar2 default 'C'
789 ,p_purge_after_transfer in varchar2 default 'N'
790 ,p_reject_if_future_changes in varchar2 default 'Y'
791 ,p_batch_id out number
792 ,p_object_version_number out number);
793 ******************************************************************/
794
795 -- p_date_effective_changes should only be populated if p_action_if_exists = 'U'
796 IF (p_action_if_exists = c_update_action_if_exists)
797 THEN
798 IF (p_date_effective_changes IS NULL)
799 THEN -- Default p_date_effective_changes
800 l_date_effective_changes := c_default_dt_effective_changes;
801 ELSE
802 l_date_effective_changes := p_date_effective_changes;
803 END IF;
804 ELSE -- set p_date_effective_changes to null
805 l_date_effective_changes := NULL;
806 END IF;
807
808 pay_batch_element_entry_api.create_batch_header (
809 p_session_date => p_effective_date,
810 p_batch_name => p_name,
811 p_business_group_id => p_bg_id,
812 p_action_if_exists => p_action_if_exists,
813 p_date_effective_changes=> l_date_effective_changes,
814 p_batch_reference => c_batch_reference,
815 p_batch_source => c_batch_source,
816 p_comments => c_comments,
817 p_batch_id => p_batch_id, -- out
818 p_object_version_number => p_ovn -- out
819 );
820
821 if g_debug then
822
823 hr_utility.set_location ( 'Leaving:'
824 || l_proc, 750);
825 end if;
826
827
828 END create_batch_header;
829
830
831
832 /*--------------------------------------------------------------------
833 |Name : create_batch_line |
834 |Type : Procedure |
835 |Description: This procedure is a wrapper over the core |
836 | create_batch_line procedure defined in |
837 | PAY_BATCH_ELEMENT_ENTRY_API |
838 ----------------------------------------------------------------------*/
839
840
841
842 PROCEDURE create_batch_line (p_session_date DATE
843 ,p_batch_id pay_batch_lines.batch_id%TYPE
844 ,p_assignment_id pay_batch_lines.assignment_id%TYPE
845 ,p_assignment_number pay_batch_lines.assignment_number%TYPE
846 ,p_batch_sequence pay_batch_lines.batch_sequence%TYPE
847 ,p_effective_date pay_batch_lines.effective_date%TYPE
848 ,p_date_earned pay_batch_lines.date_earned%TYPE
849 ,p_element_name pay_batch_lines.element_name%TYPE
850 ,p_element_type_id pay_batch_lines.element_type_id%TYPE
851 ,p_value_1 pay_batch_lines.value_1%TYPE
852 ,p_bline_id OUT NOCOPY NUMBER
853 ,p_obj_vn OUT NOCOPY NUMBER
854 ) IS
855
856 l_proc VARCHAR2 (72);
857
858 BEGIN
859
860 if g_debug then
861 hr_utility.set_location ( 'Entering:'|| l_proc, 800);
862 end if;
863
867 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_line (
864 l_proc := g_package|| 'create_batch_line';
865
866
868 p_session_date => p_session_date,
869 p_batch_id => p_batch_id,
870 p_assignment_id => p_assignment_id,
871 p_assignment_number => p_assignment_number,
872 p_batch_sequence => p_batch_sequence,
873 p_effective_date => p_effective_date,
874 p_date_earned => p_date_earned,
875 p_element_name => p_element_name,
876 p_element_type_id => p_element_type_id,
877 p_value_1 => p_value_1,
878 p_batch_line_id => p_bline_id,
879 p_object_version_number => p_obj_vn
880 );
881
882 if g_debug then
883 hr_utility.set_location ( 'Leaving:'
884 || l_proc, 800);
885 end if;
886
887 END create_batch_line;
888
889
890
891 /*--------------------------------------------------------------------
892 |Name : val_create_batch_line |
893 |Type : Procedure |
894 |Description: This procedure will take in the Data Record, validates |
895 | it and decides if it needs to be processed or rejected |
896 | by calling the function iza_validation. |
897 | After validation it calls the wrapper procedure |
898 | create_batch_line to create a record in pay_batch_lines|
899 | It also creates the record in the table |
900 | PAY_NL_IZA_UPLD_STATUS table for Rejected and Processed|
901 | records. |
902 ----------------------------------------------------------------------*/
903
904
905 Procedure val_create_batch_line( p_line_read IN VARCHAR2
906 ,p_batch_id IN NUMBER
907 ,p_batch_seq IN NUMBER
908 ,p_process_yr_mm IN VARCHAR2
909 ,p_payroll_center IN VARCHAR2
910 ,p_org_id IN NUMBER
911 ,p_org_struct_version_id IN NUMBER
912 ,p_bg_id IN NUMBER
913 ,p_eff_date IN DATE
914 ,p_batch_line_id OUT NOCOPY NUMBER
915 ,p_bl_ovn OUT NOCOPY NUMBER) IS
916
917
918 cursor csr_employee_terminate(v_person_id number,v_bg_id number,v_period_start_date date,v_period_end_date date) IS
919 select pap.effective_start_date - 1
920 from per_all_people_f pap
921 where PER_INFORMATION_CATEGORY='NL'
922 and pap.business_group_id = v_bg_id
923 and pap.person_id = v_person_id
924 and pap.current_employee_flag is null
925 and v_period_end_date >= pap.effective_start_date
926 and v_period_start_date <= pap.effective_end_date
927 and v_period_end_date between pap.effective_start_date and pap.effective_end_date;
928
929 c_commit_point CONSTANT NUMBER := 20;
930
931 l_record_eff_end_date date;
932 l_record_eff_start_date date;
933 l_assignment_id number;
934 l_assignment_num varchar2(40);
935 l_person_id number;
936 l_element_name varchar2(150);
937 l_org_id number;
938 l_client_num varchar2(10);
939 l_sub_emplr_num varchar2(10);
940 l_employee_number varchar2(50);
941 l_process_status varchar2(10);
942 l_province_code varchar2(10);
943 l_contribution1 number;
944 l_corr_cont1 number;
945 l_date_corr1 date;
946 l_contribution2 number;
947 l_corr_cont2 number;
948 l_date_corr2 date;
949 l_reject_reason_code1 varchar2(10);
950 l_reject_reason_code2 varchar2(10);
951 l_reject_reason_code3 varchar2(10);
952 l_reject_code varchar2(10);
953 l_reject_reason varchar2(80);
954 l_contr_sign varchar2(1);
955 l_iza_corr_yr_mm varchar2(10);
956 l_iza_corr_start_date date;
957 l_iza_corr_end_date date;
958 l_iza_corr2_yr_mm varchar2(10);
959 l_iza_corr2_start_date date;
960 l_iza_corr2_end_date date;
961 l_exchange_number varchar2(30);
962 l_participant_number number;
963 l_employee_name varchar2(100);
964 l_date_of_birth date;
965 l_element_entry_eff_date date;
966 l_entry_eff_date date;
967 l_record_term_date date;
968 l_entry_corr_eff_date date;
969 l_corr_term_date date;
970 l_entry_corr2_eff_date date;
971 l_corr2_term_date date;
972 l_contribution1_sign VARCHAR2(1);
973 l_corr_cont1_sign VARCHAR2(1);
974 l_contribution2_sign VARCHAR2(1);
975 l_corr_cont2_sign VARCHAR2(1);
976
977
978
979 BEGIN
980
981 if g_debug then
982 hr_utility.set_location ( 'Inside val_create_batch_line ', 900);
983 end if;
984
985 l_reject_reason_code1 := '00';
986 l_reject_reason_code2 := '00';
987 l_reject_reason_code3 := '00';
988 l_element_name := 'Nominal IZA Contribution';
989
990 l_record_eff_start_date:=to_date(p_process_yr_mm,'YYYYMM');
991 l_record_eff_end_date:=last_day(to_date(p_process_yr_mm,'YYYYMM'));
992 l_province_code := substr(p_line_read,2,2);
993 l_client_num := SUBSTR(p_line_read,4,3);
994 l_sub_emplr_num := SUBSTR(p_line_read,22,3);
995 l_exchange_number := SUBSTR(p_line_read,70,15);
996 l_participant_number := SUBSTR(p_line_read,7,15);
997 l_employee_name := SUBSTR(p_line_read,25,24) || SUBSTR(p_line_read,49,6) || SUBSTR(p_line_read,55,7) ;
998 l_date_of_birth := to_date(SUBSTR(p_line_read,62,8),'YYYYMMDD');
999
1000
1001 if g_debug then
1002
1003 -- input parameters
1004 hr_utility.set_location('l_province_code '||l_province_code,900);
1005 hr_utility.set_location('l_client_num '||l_client_num,900);
1009 hr_utility.set_location('l_employee_name '||l_employee_name,900);
1006 hr_utility.set_location('l_sub_emplr_num '||l_sub_emplr_num,900 );
1007 hr_utility.set_location('l_exchange_number '||l_exchange_number,900);
1008 hr_utility.set_location('l_participant_number '||l_participant_number,900);
1010
1011 end if;
1012
1013
1014 IF substr(p_line_read,101,6) <> '000000' THEN
1015
1016 l_iza_corr_yr_mm:=substr(p_line_read,101,6);
1017 l_iza_corr_start_date:=to_date(l_iza_corr_yr_mm,'YYYYMM');
1018 l_iza_corr_end_date:=last_day(to_date(l_iza_corr_yr_mm,'YYYYMM'));
1019 END IF;
1020
1021 IF substr(p_line_read,126,6) <> '000000' THEN
1022 l_iza_corr2_yr_mm:=substr(p_line_read,126,6);
1023 l_iza_corr2_start_date:=to_date(l_iza_corr2_yr_mm,'YYYYMM');
1024 l_iza_corr2_end_date:=last_day(to_date(l_iza_corr2_yr_mm,'YYYYMM'));
1025 END IF;
1026
1027 l_contribution1_sign := substr(p_line_read,92,1);
1028 l_contribution1 := fnd_number.canonical_to_number(substr(p_line_read,85,5) || '.' || substr(p_line_read,90,2)) ;
1029 IF l_contribution1_sign = '-' THEN
1030 l_contribution1 := (-1) * l_contribution1;
1031 END IF;
1032
1033 l_corr_cont1_sign := substr(p_line_read,100,1);
1034 l_corr_cont1 := fnd_number.canonical_to_number(substr(p_line_read,93,5) || '.' || substr(p_line_read,98,2));
1035 IF l_corr_cont1_sign = '-' THEN
1036 l_corr_cont1 := (-1) * l_corr_cont1;
1037 END IF;
1038
1039 l_contribution2_sign := substr(p_line_read,117,1);
1040 l_contribution2 := fnd_number.canonical_to_number(substr(p_line_read,110,5) || '.' || substr(p_line_read,115,2));
1041 IF l_contribution2_sign = '-' THEN
1042 l_contribution2 := (-1) * l_contribution2;
1043 END IF;
1044
1045 l_corr_cont2_sign := substr(p_line_read,125,1);
1046 l_corr_cont2 := fnd_number.canonical_to_number(substr(p_line_read,118,5) || '.' || substr(p_line_read,123,2));
1047 IF l_corr_cont2_sign = '-' THEN
1048 l_corr_cont2 := (-1) * l_corr_cont2;
1049 END IF;
1050
1051 l_employee_number := ltrim(substr(p_line_read,76,9),'0');
1052
1053 if g_debug then
1054
1055 -- input parameters
1056 hr_utility.set_location('l_contribution1 '||l_contribution1,950);
1057 hr_utility.set_location('l_corr_cont1 '||l_corr_cont1,950);
1058 hr_utility.set_location('l_contribution2 '||l_contribution2,950 );
1059 hr_utility.set_location('l_corr_cont2 '||l_corr_cont2,950);
1060 hr_utility.set_location('l_participant_number '||l_participant_number,950);
1061 hr_utility.set_location('l_employee_number '||l_employee_number,950);
1062
1063 end if;
1064
1065
1066
1067 -- Break the line up in its fields.
1068
1069 -- break the line into various fields and validate them if they need
1070 -- to be accepted or rejected for BEE. Use iza_validation function to validate the record
1071
1072
1073 -- Check if the Contribution amount IZA is not zero, then validate the record
1074 -- if the record gets validated then call create_batch_line procedure to create
1075 -- the record in pay_batch_lines
1076
1077
1078 IF l_contribution1 <> 0 OR l_contribution2 <> 0 THEN
1079 iza_validation(p_bg_id,l_record_eff_start_date,l_record_eff_end_date,l_exchange_number,l_client_num,l_sub_emplr_num,p_org_id,p_org_struct_version_id,l_person_id,l_assignment_id,l_assignment_num,l_reject_reason_code1);
1080 END IF;
1081
1082
1083 OPEN csr_employee_terminate(l_person_id,p_bg_id,l_record_eff_start_date,l_record_eff_end_date);
1084 FETCH csr_employee_terminate into l_record_term_date;
1085 CLOSE csr_employee_terminate;
1086
1087 IF l_record_term_date IS NOT NULL then
1088 l_entry_eff_date := l_record_term_date;
1089 ELSE
1090 l_entry_eff_date := l_record_eff_end_date;
1091 END IF;
1092
1093
1094 if g_debug then
1095
1096 -- input parameters
1097 hr_utility.set_location('l_person_id '||l_person_id,970);
1098 hr_utility.set_location('l_assignment_id '||l_assignment_id,970);
1099 hr_utility.set_location('p_org_id '||p_org_id,970);
1100 hr_utility.set_location('l_assignment_num '||l_assignment_num,970);
1101 hr_utility.set_location('l_reject_reason_code1 '||l_reject_reason_code1,970);
1102 end if;
1103
1104
1105 IF l_corr_cont1 <> 0 THEN
1106 iza_validation(p_bg_id,l_iza_corr_start_date,l_iza_corr_end_date,l_exchange_number,l_client_num,l_sub_emplr_num,p_org_id,p_org_struct_version_id,l_person_id,l_assignment_id,l_assignment_num,l_reject_reason_code2);
1107 END IF;
1108
1109
1110 OPEN csr_employee_terminate(l_person_id,p_bg_id,l_iza_corr_start_date,l_iza_corr_end_date);
1111 FETCH csr_employee_terminate into l_corr_term_date;
1112 CLOSE csr_employee_terminate;
1113
1114 IF l_corr_term_date IS NOT NULL then
1115 l_entry_corr_eff_date := l_corr_term_date;
1116 ELSE
1117 l_entry_corr_eff_date := l_iza_corr_end_date;
1118 END IF;
1119
1120
1121 if g_debug then
1122
1123 -- input parameters
1124 hr_utility.set_location('l_person_id '||l_person_id,970);
1125 hr_utility.set_location('l_assignment_id '||l_assignment_id,970);
1126 hr_utility.set_location('p_org_id '||p_org_id,970);
1127 hr_utility.set_location('l_assignment_num '||l_assignment_num,970);
1128 hr_utility.set_location('l_reject_reason_code2 '||l_reject_reason_code2,970);
1129 end if;
1130
1131
1132 IF l_corr_cont2 <> 0 THEN
1133 iza_validation(p_bg_id,l_iza_corr2_start_date,l_iza_corr2_end_date,l_exchange_number,l_client_num,l_sub_emplr_num,p_org_id,p_org_struct_version_id,l_person_id,l_assignment_id,l_assignment_num,l_reject_reason_code3);
1137 OPEN csr_employee_terminate(l_person_id,p_bg_id,l_iza_corr2_start_date,l_iza_corr2_end_date);
1134 END IF;
1135
1136
1138 FETCH csr_employee_terminate into l_corr2_term_date;
1139 CLOSE csr_employee_terminate;
1140
1141 IF l_corr2_term_date IS NOT NULL then
1142 l_entry_corr2_eff_date := l_corr2_term_date;
1143 ELSE
1144 l_entry_corr2_eff_date := l_iza_corr2_end_date;
1145 END IF;
1146
1147 if g_debug then
1148
1149 -- input parameters
1150 hr_utility.set_location('l_person_id '||l_person_id,1000);
1151 hr_utility.set_location('l_assignment_id '||l_assignment_id,1000);
1152 hr_utility.set_location('p_org_id '||p_org_id,1000 );
1153 hr_utility.set_location('l_assignment_num '||l_assignment_num,1000);
1154 hr_utility.set_location('l_reject_reason_code3 '||l_reject_reason_code3,1000);
1155 end if;
1156
1157
1158
1159
1160 IF l_reject_reason_code1 = '00' AND l_reject_reason_code2 = '00' AND l_reject_reason_code3 = '00' THEN
1161
1162
1163 IF l_contribution1 <> 0 THEN
1164
1165
1166 -- Create a batch line for every line found in the file.
1167 create_batch_line (p_session_date => p_eff_date
1168 ,p_batch_id => p_batch_id
1169 ,p_assignment_id => l_assignment_id
1170 ,p_assignment_number => l_assignment_num
1171 ,p_batch_sequence => p_batch_seq
1172 ,p_effective_date => l_entry_eff_date
1173 ,p_date_earned => l_entry_eff_date
1174 ,p_element_name => l_element_name
1175 ,p_element_type_id => g_element_type_id
1176 ,p_value_1 => fnd_number.number_to_canonical(l_contribution1)
1177 ,p_bline_id => p_batch_line_id
1178 ,p_obj_vn => p_bl_ovn
1179 );
1180 END IF;
1181
1182 IF l_corr_cont1 <> 0 THEN
1183
1184 -- Create a batch line for every line found in the file.
1185 create_batch_line (p_session_date => p_eff_date
1186 ,p_batch_id => p_batch_id
1187 ,p_assignment_id => l_assignment_id
1188 ,p_assignment_number => l_assignment_num
1189 ,p_batch_sequence => p_batch_seq
1190 ,p_effective_date => l_entry_corr_eff_date
1191 ,p_date_earned => l_entry_corr_eff_date
1192 ,p_element_name => l_element_name
1193 ,p_element_type_id => g_element_type_id
1194 ,p_value_1 => fnd_number.number_to_canonical(l_corr_cont1)
1195 ,p_bline_id => p_batch_line_id
1196 ,p_obj_vn => p_bl_ovn
1197 );
1198
1199 END IF;
1200
1201
1202 IF l_contribution2 <> 0 THEN
1203
1204
1205 -- Create a batch line for every line found in the file.
1206 create_batch_line (p_session_date => p_eff_date
1207 ,p_batch_id => p_batch_id
1208 ,p_assignment_id => l_assignment_id
1209 ,p_assignment_number => l_assignment_num
1210 ,p_batch_sequence => p_batch_seq
1211 ,p_effective_date => l_entry_eff_date
1212 ,p_date_earned => l_entry_eff_date
1213 ,p_element_name => l_element_name
1214 ,p_element_type_id => g_element_type_id
1215 ,p_value_1 => fnd_number.number_to_canonical(l_contribution2)
1216 ,p_bline_id => p_batch_line_id
1217 ,p_obj_vn => p_bl_ovn
1218 );
1219 END IF;
1220
1221
1222 IF l_corr_cont2 <> 0 THEN
1223
1224
1225 -- Create a batch line for every line found in the file.
1226 create_batch_line (p_session_date => p_eff_date
1227 ,p_batch_id => p_batch_id
1228 ,p_assignment_id => l_assignment_id
1229 ,p_assignment_number => l_assignment_num
1230 ,p_batch_sequence => p_batch_seq
1231 ,p_effective_date => l_entry_corr2_eff_date
1232 ,p_date_earned => l_entry_corr2_eff_date
1233 ,p_element_name => l_element_name
1234 ,p_element_type_id => g_element_type_id
1235 ,p_value_1 => fnd_number.number_to_canonical(l_corr_cont2)
1236 ,p_bline_id => p_batch_line_id
1237 ,p_obj_vn => p_bl_ovn
1238 );
1239
1240 END IF;
1241
1242
1243 l_process_status:='PROCESSED';
1244 insert into PAY_NL_IZA_UPLD_STATUS( BUSINESS_GROUP_ID
1245 ,ORGANIZATION_ID
1246 ,EMPLOYER_NUMBER
1247 ,SUB_EMPLOYER_NUMBER
1248 ,PAYROLL_CENTER
1249 ,PROCESS_YEAR_MONTH
1250 ,PERSON_ID
1251 ,EMPLOYEE_NUMBER
1252 ,PROCESS_STATUS
1253 ,PROVINCE_CODE
1254 ,DATE_OF_BIRTH
1255 ,PARTICIPANT_NUMBER
1256 ,EMPLOYEE_NAME
1257 ,CONTRIBUTION_1
1258 ,CORRECTION_CONTRIBUTION_1
1259 ,DATE_CORRECTION_1
1260 ,CONTRIBUTION_2
1261 ,CORRECTION_CONTRIBUTION_2
1262 ,DATE_CORRECTION_2
1263 ,REJECT_REASON)
1264 select p_bg_id
1265 ,p_org_id
1266 ,decode(l_client_num,'000','0',ltrim(l_client_num,'0'))
1267 ,decode(l_sub_emplr_num,'000','0',ltrim(l_sub_emplr_num,'0'))
1268 ,p_payroll_center
1269 ,l_record_eff_end_date
1270 ,l_person_id
1271 ,l_employee_number
1272 ,l_process_status
1273 ,l_province_code
1274 ,l_date_of_birth
1275 ,l_participant_number
1276 ,l_employee_name
1277 ,l_contribution1
1281 ,l_corr_cont2
1278 ,l_corr_cont1
1279 ,l_iza_corr_end_date
1280 ,l_contribution2
1282 ,l_iza_corr2_end_date
1283 ,NULL
1284 from dual;
1285
1286
1287 ELSE
1288
1289 select decode(l_reject_reason_code1,'00',decode(l_reject_reason_code2,'00',l_reject_reason_code3,l_reject_reason_code2),l_reject_reason_code1) into l_reject_code from dual;
1290 l_process_status:='REJECTED';
1291
1292 insert into PAY_NL_IZA_UPLD_STATUS( BUSINESS_GROUP_ID
1293 ,ORGANIZATION_ID
1294 ,EMPLOYER_NUMBER
1295 ,SUB_EMPLOYER_NUMBER
1296 ,PAYROLL_CENTER
1297 ,PROCESS_YEAR_MONTH
1298 ,PERSON_ID
1299 ,EMPLOYEE_NUMBER
1300 ,PROCESS_STATUS
1301 ,PROVINCE_CODE
1302 ,DATE_OF_BIRTH
1303 ,PARTICIPANT_NUMBER
1304 ,EMPLOYEE_NAME
1305 ,CONTRIBUTION_1
1306 ,CORRECTION_CONTRIBUTION_1
1307 ,DATE_CORRECTION_1
1308 ,CONTRIBUTION_2
1309 ,CORRECTION_CONTRIBUTION_2
1310 ,DATE_CORRECTION_2
1311 ,REJECT_REASON)
1312 select p_bg_id
1313 ,p_org_id
1314 ,decode(l_client_num,'000','0',ltrim(l_client_num,'0'))
1315 ,decode(l_sub_emplr_num,'000','0',ltrim(l_sub_emplr_num,'0'))
1316 ,p_payroll_center
1317 ,l_record_eff_end_date
1318 ,l_person_id
1319 ,l_employee_number
1320 ,l_process_status
1321 ,l_province_code
1322 ,l_date_of_birth
1323 ,l_participant_number
1324 ,l_employee_name
1325 ,l_contribution1
1326 ,l_corr_cont1
1327 ,l_iza_corr_end_date
1328 ,l_contribution2
1329 ,l_corr_cont2
1330 ,l_iza_corr2_end_date
1331 ,l_reject_code
1332 from dual;
1333
1334
1335 END IF;
1336
1337
1338
1339 -- commit the records uppon reaching the commit point
1340
1341 IF MOD (p_batch_seq, c_commit_point) = 0
1342 THEN
1343 COMMIT;
1344 NULL;
1345 END IF;
1346
1347 EXCEPTION
1348 WHEN OTHERS then
1349 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1350 HR_UTILITY.RAISE_ERROR;
1351
1352
1353 end val_create_batch_line;
1354
1355
1356
1357
1358 /*-----------------------------------------------------------------------
1359 |Name : iza_validation |
1360 |Type : Procedure |
1361 |Description: Procedure to validate the Data Record. This procedure |
1362 | decides if the record needs to be processed or rejected |
1363 | If the record needs to rejected, this Procedure |
1364 | sets the value of the OUT parameter p_reject_reason_code |
1365 | equalt to the reject reason code as given in the |
1366 | NL_IZA_REJECT_REASON lookup. Else the p_reject_reason_code|
1367 | is set to '00' |
1368 -----------------------------------------------------------------------*/
1369
1370
1371
1372 PROCEDURE iza_validation(p_business_group_id IN NUMBER
1373 ,p_period_start_date IN DATE
1374 ,p_period_end_date IN DATE
1375 ,p_exchange_number IN VARCHAR2
1376 ,p_client_num IN VARCHAR2
1377 ,p_sub_emplr_num IN VARCHAR2
1378 ,p_org_id IN NUMBER
1379 ,p_org_struct_version_id IN NUMBER
1380 ,p_person_id OUT NOCOPY NUMBER
1381 ,p_assignment_id OUT NOCOPY NUMBER
1382 ,p_assignment_num OUT NOCOPY VARCHAR2
1383 ,p_reject_reason_code OUT NOCOPY VARCHAR2) IS
1384
1385
1386
1387 cursor csr_abs_days(v_person_id number,v_bg_id number,v_period_start_date date,v_period_end_date date) IS
1388 select sum((NVL(paa.date_end,paa.date_start) - paa.date_start)+1)
1389 from PER_ABSENCE_ATTENDANCES paa
1390 where paa.business_group_id = v_bg_id
1391 and paa.person_id = v_person_id
1392 and decode(paa.ABS_INFORMATION_CATEGORY,'NL_S',ABS_INFORMATION2,ABS_INFORMATION1) = 'Y'
1393 and v_period_end_date >= paa.date_start
1394 and v_period_start_date <= NVL(paa.date_end,v_period_start_date);
1395
1396
1397 cursor csr_contract_other_cmpny(v_person_id number,v_bg_id number,v_period_start_date date,v_period_end_date date) IS
1398 select pap.per_information16
1399 from per_all_people_f pap
1400 where PER_INFORMATION_CATEGORY='NL'
1401 and pap.business_group_id = v_bg_id
1402 and pap.person_id = v_person_id
1403 and pap.per_information16 = 'Y'
1404 and v_period_end_date >= pap.effective_start_date
1405 and v_period_start_date <= NVL(pap.effective_end_date,v_period_start_date);
1406
1407
1408 cursor csr_emp_num_exists(v_employee_num varchar2,v_bg_id number,v_period_start_date date,v_period_end_date date) IS
1409 SELECT pap.person_id,paa.assignment_id,paa.assignment_number
1410 FROM per_all_people_f pap
1411 ,per_all_assignments_f paa
1412 WHERE ltrim(substr(pap.employee_number,1,9),'0') = v_employee_num
1413 AND paa.person_id = pap.person_id
1414 AND pap.business_group_id = v_bg_id
1415 AND v_period_end_date >= pap.effective_start_date
1416 AND v_period_start_date <= pap.effective_end_date;
1417
1418
1419
1420 cursor csr_employee_exists(v_person_id number,v_bg_id number,v_period_start_date date,v_period_end_date date) IS
1421 select pap.current_employee_flag
1422 from per_all_people_f pap
1423 where PER_INFORMATION_CATEGORY='NL'
1424 and pap.business_group_id = v_bg_id
1425 and pap.person_id = v_person_id
1429
1426 and pap.current_employee_flag = 'Y'
1427 and v_period_end_date >= pap.effective_start_date
1428 and v_period_start_date <= pap.effective_end_date;
1430
1431 cursor csr_iza_insured(v_assignment_id number,v_period_start_date date,v_period_end_date date) IS
1432 select pae_iza.AEI_INFORMATION3
1433 from PER_ASSIGNMENT_EXTRA_INFO pae_iza
1434 ,PER_ASSIGNMENT_EXTRA_INFO pae_sii
1435 where pae_iza.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
1436 and pae_iza.assignment_id = v_assignment_id
1437 and v_period_end_date >= fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION1)
1438 and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION2),v_period_start_date)
1439 and pae_sii.AEI_INFORMATION_CATEGORY = 'NL_SII'
1440 and pae_sii.AEI_INFORMATION3 in ('ZFW','AMI')
1441 and pae_sii.AEI_INFORMATION4 = '4'
1442 and v_period_end_date >= fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION1)
1443 and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION2),v_period_start_date)
1444 and pae_sii.assignment_id = pae_iza.assignment_id;
1445
1446
1447 cursor csr_exchange_num_valid (v_org_id number,v_bg_id number,v_person_id number,v_period_start_date date,v_period_end_date date) IS
1448 select paa.assignment_id, paa.assignment_number
1449 from per_all_assignments_f paa
1450 where paa.person_id = v_person_id
1451 and paa.primary_flag = 'Y'
1452 and paa.business_group_id = v_bg_id
1453 and hr_nl_org_info.Get_iza_Org_Id(p_org_struct_version_id,paa.organization_id) = v_org_id
1454 and paa.effective_start_date =
1455 (
1456 SELECT MIN(asg.effective_start_date)
1457 FROM per_assignment_status_types past, per_all_assignments_f asg
1458 WHERE asg.assignment_id = paa.assignment_id
1459 and past.per_system_status = 'ACTIVE_ASSIGN'
1460 and asg.assignment_status_type_id = past.assignment_status_type_id
1461 and asg.effective_start_date <= v_period_end_date
1462 and asg.effective_end_date >= v_period_start_date
1463 );
1464
1465
1466
1467 l_length_process_period number;
1468 l_leave_days number;
1469 l_employee_number varchar2(50);
1470 l_iza_insured_flag varchar2(10);
1471 l_contract_other_cmpny varchar2(10);
1472 l_current_employee_flag varchar2(10);
1473 l_already_rejected_flag varchar2(10);
1474 l_org_struc_version_id number;
1475
1476
1477
1478 BEGIN
1479
1480
1481 p_reject_reason_code :='00' ;
1482 l_already_rejected_flag := 'N';
1483
1484 l_length_process_period := (p_period_end_date - p_period_start_date) + 1;
1485 l_employee_number := ltrim(substr(p_exchange_number,7,9),'0');
1486
1487
1488 if g_debug then
1489
1490 -- input parameters
1491 hr_utility.set_location('l_length_process_period '||l_length_process_period,1350);
1492 hr_utility.set_location('l_employee_number '||l_employee_number,1350);
1493 end if;
1494
1495
1496 OPEN csr_emp_num_exists(l_employee_number,p_business_group_id,p_period_start_date,p_period_end_date) ;
1497 FETCH csr_emp_num_exists into p_person_id,p_assignment_id,p_assignment_num;
1498 CLOSE csr_emp_num_exists;
1499
1500 if g_debug then
1501
1502 -- input parameters
1503 hr_utility.set_location('p_person_id '||p_person_id,1350);
1504 hr_utility.set_location('p_assignment_id '||p_assignment_id,1350);
1505 hr_utility.set_location('p_assignment_num '||p_assignment_num,1350 );
1506 end if;
1507
1508
1509 IF p_person_id IS NULL THEN
1510 p_reject_reason_code := '05';
1511 l_already_rejected_flag := 'Y';
1512 END IF;
1513
1514 IF l_already_rejected_flag = 'N' THEN
1515
1516 OPEN csr_employee_exists(p_person_id,p_business_group_id,p_period_start_date,p_period_end_date);
1517 FETCH csr_employee_exists INTO l_current_employee_flag;
1518 CLOSE csr_employee_exists;
1519
1520 if g_debug then
1521
1522 -- input parameters
1523 hr_utility.set_location('l_current_employee_flag '||l_current_employee_flag,1350);
1524 end if;
1525
1526
1527 IF l_current_employee_flag IS NULL THEN
1528 p_reject_reason_code := '01';
1529 l_already_rejected_flag := 'Y';
1530 END IF;
1531 END IF;
1532
1533
1534 IF l_already_rejected_flag = 'N' THEN
1535 OPEN csr_iza_insured(p_assignment_id,p_period_start_date,p_period_end_date);
1536 FETCH csr_iza_insured INTO l_iza_insured_flag;
1537 CLOSE csr_iza_insured;
1538
1539 if g_debug then
1540
1541 -- input parameters
1542 hr_utility.set_location('l_iza_insured_flag '||l_iza_insured_flag,1400);
1543 end if;
1544
1545 IF l_iza_insured_flag IS NULL THEN
1546 p_reject_reason_code := '02';
1547 l_already_rejected_flag := 'Y';
1548 END IF;
1549 END IF;
1550
1551
1552 IF l_already_rejected_flag = 'N' THEN
1553 OPEN csr_contract_other_cmpny(p_person_id,p_business_group_id,p_period_start_date,p_period_end_date);
1554 FETCH csr_contract_other_cmpny INTO l_contract_other_cmpny;
1555 CLOSE csr_contract_other_cmpny;
1556
1557 if g_debug then
1558
1559 -- input parameters
1560 hr_utility.set_location('l_contract_other_cmpny '||l_contract_other_cmpny,1400);
1561 end if;
1562
1563 IF l_contract_other_cmpny IS NOT NULL THEN
1564 p_reject_reason_code := '03';
1565 l_already_rejected_flag := 'Y';
1566 END IF;
1567
1568 END IF;
1569
1570
1571
1572 IF l_already_rejected_flag = 'N' THEN
1573 OPEN csr_abs_days(p_person_id,p_business_group_id,p_period_start_date,p_period_end_date);
1574 FETCH csr_abs_days into l_leave_days;
1575 CLOSE csr_abs_days;
1576
1577
1578 if g_debug then
1579
1580 -- input parameters
1584 IF l_leave_days = l_length_process_period THEN
1581 hr_utility.set_location('l_leave_days '||l_leave_days,1400);
1582 end if;
1583
1585 p_reject_reason_code := '04';
1586 l_already_rejected_flag := 'Y';
1587 END IF;
1588 END IF;
1589
1590
1591
1592 if g_debug then
1593
1594 -- input parameters
1595 hr_utility.set_location('p_org_id '||p_org_id,1450);
1596 end if;
1597
1598
1599 IF l_already_rejected_flag = 'N' THEN
1600
1601
1602 IF p_org_id IS NOT NULL then
1603
1604 OPEN csr_exchange_num_valid(p_org_id,p_business_group_id,p_person_id,p_period_start_date,p_period_end_date);
1605 FETCH csr_exchange_num_valid INTO p_assignment_id,p_assignment_num;
1606 CLOSE csr_exchange_num_valid;
1607
1608 if g_debug then
1609
1610 -- input parameters
1611 hr_utility.set_location('p_assignment_id '||p_assignment_id,1450);
1612 hr_utility.set_location('p_assignment_num '||p_assignment_num,1450);
1613 end if;
1614
1615 IF p_assignment_id IS NULL then
1616 p_reject_reason_code := '05';
1617 l_already_rejected_flag := 'Y';
1618 END IF;
1619 ELSE
1620 p_reject_reason_code := '05';
1621 l_already_rejected_flag := 'Y';
1622
1623 END IF;
1624 END IF;
1625
1626
1627 EXCEPTION
1628 WHEN OTHERS then
1629 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1630 HR_UTILITY.RAISE_ERROR;
1631
1632
1633 end iza_validation;
1634
1635
1636 /*-----------------------------------------------------------------------
1637 |Name : purge_iza_process_status |
1638 |Type : Procedure |
1639 |Description: Driving Procedure for the concurrent program for |
1640 | IZA Upload Purge Process. This Procedure will purge all |
1641 | the records from the Process Status table that are no |
1642 | longer required |
1643 -----------------------------------------------------------------------*/
1644
1645 procedure purge_iza_process_status (p_errbuf OUT NOCOPY VARCHAR2
1646 ,p_retcode OUT NOCOPY VARCHAR2
1647 ,p_business_group_id IN NUMBER
1648 ,p_month_from IN VARCHAR2
1649 ,p_month_to IN VARCHAR2
1650 ,p_org_struct_id IN NUMBER
1651 ,p_employer_id IN NUMBER
1652 ) IS
1653
1654 l_period_start_date date;
1655 l_period_end_date date;
1656
1657 begin
1658 l_period_start_date := to_date(p_month_from,'MMYYYY');
1659 l_period_end_date := last_day(to_date(p_month_to,'MMYYYY'));
1660
1661 DELETE from PAY_NL_IZA_UPLD_STATUS pizas
1662 WHERE pizas.process_year_month between l_period_start_date and l_period_end_date
1663 AND pizas.organization_id = p_employer_id
1664 AND pizas.business_group_id = p_business_group_id;
1665
1666 EXCEPTION
1667 WHEN OTHERS then
1668 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1669 HR_UTILITY.RAISE_ERROR;
1670
1671 end purge_iza_process_status;
1672
1673 END PAY_NL_IZA_UPLOAD;