[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_K27_REIM_UPLD
Source
1 PACKAGE BODY PAY_NO_K27_REIM_UPLD AS
2 /* $Header: pynok27up.pkb 120.0.12020000.10 2013/03/28 04:40:59 smeduri noship $ */
3
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5 g_package VARCHAR2(33) := ' PAY_NO_K27_REIM_UPLD.';
6 g_payroll_action_id NUMBER ;
7 g_le_assignment_action_id NUMBER ;
8 g_business_group_id NUMBER;
9 g_legal_employer_id NUMBER;
10 --Create globals
11 g_effective_date DATE;
12 g_period_date DATE;
13 g_element_entry_id NUMBER;
14 g_start_date VARCHAR2(30); /* 9489806 */
15 g_end_date VARCHAR2(30); /* 9489806 */
16 g_payroll_type VARCHAR2(30); /* 9489806 */
17 g_payroll_id NUMBER;
18 g_year VARCHAR2(30);
19 g_payroll_period NUMBER;
20 g_test_submission VARCHAR2(1);
21 g_company_terminating VARCHAR2(1);
22 g_msg_txt VARCHAR2(300);
23 g_flag NUMBER:=0;
24
25 l_bincome_exception exception;
26
27 error_message boolean; --14078879
28
29 --
30 PROCEDURE create_k27_element ( p_effective_date IN DATE
31 , p_assignment_id IN NUMBER
32 , p_business_group_id IN NUMBER
33 , p_start_date IN DATE
34 , p_end_date IN DATE
35 , p_reimb_date IN DATE
36 , p_reimb_amt IN NUMBER
37 , p_municipality_number IN VARCHAR2
38 , p_code IN VARCHAR2
39 )
40 IS
41 CURSOR element_csr IS
42 SELECT element_type_id
43 FROM pay_element_types_f
44 WHERE element_name = 'K27 Reimbursement File Details'
45 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
46 AND nvl(legislation_code, 'NO') = 'NO'
47 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
48 --
49 element_rec element_csr%ROWTYPE;
50 --
51 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
52 SELECT input_value_id
53 FROM pay_input_values_f
54 WHERE element_type_id = p_element_type_id
55 AND name = p_name
56 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
57 AND nvl(legislation_code, 'NO') = 'NO'
58 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
59 --
60 input_val_rec1 input_val_csr%ROWTYPE;
61 input_val_rec2 input_val_csr%ROWTYPE;
62 input_val_rec3 input_val_csr%ROWTYPE;
63 input_val_rec4 input_val_csr%ROWTYPE;
64 input_val_rec5 input_val_csr%ROWTYPE;
65 input_val_rec6 input_val_csr%ROWTYPE;
66 --
67 CURSOR link_csr(p_element_type_id IN NUMBER) IS
68 SELECT links.element_link_id
69 FROM pay_element_links_f links, per_all_assignments_f assign
70 WHERE links.element_type_id = p_element_type_id
71 AND links.business_group_id=p_business_group_id
72 AND assign.assignment_id=p_assignment_id
73 AND (( links.payroll_id is not null
74 and links.payroll_id = assign.payroll_id)
78 and links.link_to_all_payrolls_flag='N')
75 OR ( links.link_to_all_payrolls_flag='Y'
76 and assign.payroll_id is not null)
77 OR ( links.payroll_id is null
79 OR links.job_id=assign.job_id
80 OR links.position_id=assign.position_id
81 OR links.people_group_id=assign.people_group_id
82 OR links.organization_id=assign.organization_id
83 OR links.grade_id=assign.grade_id
84 OR links.location_id=assign.location_id
85 OR links.pay_basis_id=assign.pay_basis_id
86 OR links.employment_category=assign.employment_category)
87 AND p_effective_date BETWEEN links.effective_start_date
88 AND links.effective_end_date;
89 --
90 link_rec link_csr%ROWTYPE;
91 --
92 l_element_entry_id NUMBER;
93 l_effective_start_date DATE;
94 l_effective_end_date DATE;
95 l_object_version_number NUMBER;
96 l_create_warning BOOLEAN := FALSE;
97
98 CURSOR csr_payroll_periods
99 IS
100 select distinct ptp.start_date
101 from
102 per_all_assignments_f paaf,
103 pay_payrolls_f ppf,
104 per_time_periods ptp
105 where
106 paaf.assignment_id = p_assignment_id
107 AND paaf.business_group_id = p_business_group_id
108 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
109 AND paaf.payroll_id = ppf.payroll_id
110 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
111 AND paaf.business_group_id = ppf.business_group_id
112 AND ptp.payroll_id = ppf.payroll_id
113 AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date;
114
115 l_effective_date DATE;
116
117
118 BEGIN
119
120 hr_utility.set_location('Entering create_k27_element procedure',501);
121 hr_utility.set_location('p_effective_date:'||p_effective_date,502);
122 hr_utility.set_location('p_reimb_date:'||p_reimb_date,503);
123 --
124 -- Get Element information
125 --
126 OPEN element_csr;
127 FETCH element_csr INTO element_rec;
128 CLOSE element_csr;
129 --
130 -- Get Input Values
131 --
132 OPEN input_val_csr(element_rec.element_type_id, 'Amount'); --14363238
133 FETCH input_val_csr INTO input_val_rec1;
134 CLOSE input_val_csr;
135 --
136 OPEN input_val_csr(element_rec.element_type_id, 'Start Date');
137 FETCH input_val_csr INTO input_val_rec2;
138 CLOSE input_val_csr;
139 --
140 OPEN input_val_csr(element_rec.element_type_id, 'End Date');
141 FETCH input_val_csr INTO input_val_rec3;
142 CLOSE input_val_csr;
143 --
144 OPEN input_val_csr(element_rec.element_type_id, 'Reimbursement Max Date');
145 FETCH input_val_csr INTO input_val_rec4;
146 CLOSE input_val_csr;
147 --
148 OPEN input_val_csr(element_rec.element_type_id, 'Tax Municipality');
149 FETCH input_val_csr INTO input_val_rec5;
150 CLOSE input_val_csr;
151 --
152 OPEN input_val_csr(element_rec.element_type_id, 'Code');
153 FETCH input_val_csr INTO input_val_rec6;
154 CLOSE input_val_csr;
155 -- Get element link information
156 --
157 OPEN link_csr(element_rec.element_type_id);
158 FETCH link_csr INTO link_rec;
159 CLOSE link_csr;
160 --
161 --Period start date
162 OPEN csr_payroll_periods;
163 FETCH csr_payroll_periods INTO l_effective_date;
164 CLOSE csr_payroll_periods;
165 --
166
167 hr_utility.set_location('l_effective_date:'||l_effective_date,504);
168
169 -- Call API To Create element entry.
170 py_element_entry_api.create_element_entry (
171 p_effective_date => nvl(l_effective_date,p_effective_date),
172 p_business_group_id => p_business_group_id,
173 --p_original_entry_id => p_original_entry_id, -- default
174 p_assignment_id => p_assignment_id,
175 p_element_link_id => link_rec.element_link_id,
176 p_entry_type => 'E',
177 p_creator_type => 'F',
178 p_input_value_id1 => input_val_rec1.input_value_id,
179 p_input_value_id2 => input_val_rec2.input_value_id,
180 p_input_value_id3 => input_val_rec3.input_value_id,
181 p_input_value_id4 => input_val_rec4.input_value_id,
182 p_input_value_id5 => input_val_rec5.input_value_id,
183 p_input_value_id6 => input_val_rec6.input_value_id,
184 p_entry_value1 => p_reimb_amt,
185 p_entry_value2 => p_start_date,
186 p_entry_value3 => p_end_date,
187 p_entry_value4 => p_reimb_date,
188 p_entry_value5 => p_municipality_number,
189 p_entry_value6 => p_code,
190 p_entry_information_category => 'NO_INFORMATION', --14363238
191 p_effective_start_date => l_effective_start_date,
192 p_effective_end_date => l_effective_end_date,
193 p_element_entry_id => l_element_entry_id,
194 p_object_version_number => l_object_version_number,
195 p_create_warning => l_create_warning
196 );
197
198 g_element_entry_id := l_element_entry_id;
199 hr_utility.set_location('leaving create_k27_element element procedure',505);
200 END create_k27_element;
201
202 --
203 FUNCTION get_element_link_id
204 (
205 p_assignment_id IN NUMBER
206 ,p_business_group_id IN NUMBER
207 ,p_date IN per_all_assignments_f.effective_start_date%TYPE
211 l_element_link_id pay_element_links_f.ELEMENT_LINK_ID%TYPE;
208 ,p_element_name IN pay_element_types_f.ELEMENT_NAME%TYPE
209 ) RETURN NUMBER
210 IS
212 CURSOR csr_get_ele_type_id IS
213 SELECT element_type_id
214 FROM pay_element_types_f
215 WHERE
216 Element_name = p_element_name
217 AND p_date BETWEEN effective_start_date AND effective_end_date ;
218
219 Cursor csr_element_link_id(p_element_type_id NUMBER)
220 IS
221 SELECT links.element_link_id
222 FROM pay_element_links_f links, per_all_assignments_f assign
223 WHERE links.element_type_id = p_element_type_id
224 AND links.business_group_id= p_business_group_id
225 AND assign.assignment_id= p_assignment_id
226 AND (( links.payroll_id is not null
227 and links.payroll_id = assign.payroll_id)
228 OR ( links.link_to_all_payrolls_flag='Y'
229 and assign.payroll_id is not null)
230 OR ( links.payroll_id is null
231 and links.link_to_all_payrolls_flag='N')
232 OR links.job_id=assign.job_id
233 OR links.position_id=assign.position_id
234 OR links.people_group_id=assign.people_group_id
235 OR links.organization_id=assign.organization_id
236 OR links.grade_id=assign.grade_id
237 OR links.location_id=assign.location_id
238 OR links.pay_basis_id=assign.pay_basis_id
239 OR links.employment_category=assign.employment_category)
240 AND p_date BETWEEN links.effective_start_date
241 AND links.effective_end_date;
242 BEGIN
243 l_element_link_id := -1;
244 FOR pay_rec IN csr_get_ele_type_id
245 LOOP
246 OPEN csr_element_link_id(pay_rec.element_type_id) ;
247 FETCH csr_element_link_id
248 INTO l_element_link_id ;
249 IF csr_element_link_id%NOTFOUND THEN
250 l_element_link_id := -1;
251 END IF;
252 CLOSE csr_element_link_id;
253 END LOOP ;
254 RETURN l_element_link_id ;
255 END get_element_link_id;
256 --
257
258 --
259 /* GET PARAMETER */
260 FUNCTION GET_PARAMETER(
261 p_parameter_string IN VARCHAR2
262 ,p_token IN VARCHAR2
263 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
264 IS
265 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
266 l_start_pos NUMBER;
267 l_delimiter VARCHAR2(1):=' ';
268 l_proc VARCHAR2(40):= g_package||' get parameter ';
269 BEGIN
270 --
271 IF g_debug THEN
272 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
273 END IF;
274 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
275 --
276 IF l_start_pos = 0 THEN
277 l_delimiter := '|';
278 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
279 END IF;
280
281 IF l_start_pos <> 0 THEN
282 l_start_pos := l_start_pos + length(p_token||'=');
283 l_parameter := substr(p_parameter_string,
284 l_start_pos,
285 instr(p_parameter_string||' ',
286 l_delimiter,l_start_pos)
287 - l_start_pos);
288
289 IF p_token = 'MESSAGE_TEXT' THEN
290 l_parameter := substr(p_parameter_string,l_start_pos);
291 END IF;
292
293 IF p_segment_number IS NOT NULL THEN
294 l_parameter := ':'||l_parameter||':';
295 l_parameter := substr(l_parameter,
296 instr(l_parameter,':',1,p_segment_number)+1,
297 instr(l_parameter,':',1,p_segment_number+1) -1
298 - instr(l_parameter,':',1,p_segment_number));
299 END IF;
300 END IF;
301 --
302 IF g_debug THEN
303 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
304 END IF;
305
306 RETURN l_parameter;
307
308 END;
309
310 /* GET ALL PARAMETERS */
311 PROCEDURE GET_ALL_PARAMETERS
312 ( p_payroll_action_id IN NUMBER
313 ,p_business_group_id OUT NOCOPY NUMBER
314 ,p_legal_employer_no OUT NOCOPY VARCHAR2
315 ,p_effective_date OUT NOCOPY DATE
316 ,p_test_submission OUT NOCOPY VARCHAR2
317 ,p_filename OUT NOCOPY VARCHAR2
318 ,p_period_date OUT NOCOPY DATE)
319 IS
320
321 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
322 SELECT PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NUM')
323 ,PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
324 ,PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'FILE_NAME')
325 ,effective_date
326 ,business_group_id
327 ,PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'PERIOD_DATE')
328 FROM pay_payroll_actions
329 WHERE payroll_action_id = p_payroll_action_id;
330
331 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
332 --
333 l_period_date varchar2(20);
334 BEGIN
335
336 OPEN csr_parameter_info (p_payroll_action_id);
337
338 FETCH csr_parameter_info
339 INTO p_legal_employer_no
340 ,p_test_submission
341 ,p_filename
342 ,p_effective_date
343 ,p_business_group_id
344 ,l_period_date;
345 CLOSE csr_parameter_info;
346 --
347 hr_utility.set_location(' p_legal_employer_no:'||p_legal_employer_no,31);
348 hr_utility.set_location(' p_filename:'||p_filename,32);
349 hr_utility.set_location(' p_test_submission:'||p_test_submission,33);
350 hr_utility.set_location(' p_effective_date:'||p_effective_date,34);
351 hr_utility.set_location(' l_period_date:'||l_period_date,35);
352
353 p_period_date := fnd_date.canonical_to_date(l_period_date);
354
358 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',40);
355 hr_utility.set_location(' p_period_date:'||p_period_date,36);
356
357 IF g_debug THEN
359 END IF;
360
361 END GET_ALL_PARAMETERS;
362
363 PROCEDURE READ_K27_FILE(
364 errbuf OUT NOCOPY VARCHAR2
365 , retcode OUT NOCOPY VARCHAR2
366 , p_filename IN VARCHAR2
367 , p_employer_number IN VARCHAR2
368 , p_test_submission IN VARCHAR2 :='YES'
369 )
370 IS
371 l_location VARCHAR2(2000);
372 l_file_handle utl_file.file_type;
373 l_present_line VARCHAR2(32767) := NULL;
374 l_present_line1 VARCHAR2(32767) := NULL;
375 l_present_line2 VARCHAR2(32767) := NULL;
376 l_line_number number := 0;
377 l_assignment_id number;
378 l_element_link_id number;
379 l_payroll_id number;
380
381 invalid_file_format exception;
382 e_fatal_error exception;
383 no_rec_found_in_file exception;
384
385 l_pkg varchar2(100) := 'PAY_NO_K27_REIM_UPLD.READ_K27_FILE';
386 l_action_info_id NUMBER;
387 l_ovn NUMBER;
388
389 l_mun_sso_num VARCHAR2(4);
390 l_le_org_num VARCHAR2(9);
391 l_lu_org_num VARCHAR2(9);
392 l_ref_code VARCHAR2(1);
393 l_emp_ni_num VARCHAR2(11);
394 l_reimb_start_dt VARCHAR2(11);
395 l_le_bank_ac_num VARCHAR2(11);
396 l_emp_name VARCHAR2(25);
397 l_reimb_end_dt VARCHAR2(11);
398 l_reimb_amount VARCHAR2(20);
399 l_filler VARCHAR2(20);
400 l_max_reimb_dt VARCHAR2(11);
401 l_skip_flag VARCHAR2(1) := 'N';
402 l_format_altered VARCHAR2(1) := 'N';
403
404 CURSOR csr_get_prim_assg
405 ( p_business_group_id per_business_groups.business_group_id%TYPE
406 ,p_ni_number per_all_people_f.national_identifier%TYPE
407 ,p_date per_all_assignments_f.effective_start_date%TYPE)
408 IS
409 SELECT distinct PAA.ASSIGNMENT_ID, PAA.PAYROLL_ID
410 FROM per_all_assignments_f PAA
411 , per_all_people_f PAP
412 WHERE PAA.BUSINESS_GROUP_ID = p_business_group_id
413 AND PAP.per_information_category ='NO'
414 AND (substr(PAP.NATIONAL_IDENTIFIER,1,6)||substr(PAP.NATIONAL_IDENTIFIER,8,5) = lpad(p_ni_number,11,'0')
415 OR PAP.NATIONAL_IDENTIFIER = lpad(p_ni_number,11,'0'))
416 AND PAA.PERSON_ID = PAP.PERSON_ID
417 AND PAA.PRIMARY_FLAG = 'Y'
418 AND PAA.payroll_id is not null --14363238
419 AND p_date between PAA.EFFECTIVE_START_DATE and PAA.EFFECTIVE_END_DATE
420 AND p_date between PAP.EFFECTIVE_START_DATE and PAP.EFFECTIVE_END_DATE ;
421
422
423 CURSOR csr_asg_lu_id
424 ( p_business_group_id per_business_groups.business_group_id%TYPE
425 ,p_asg_id NUMBER
426 ,p_date per_all_assignments_f.effective_start_date%TYPE
427 )
428 IS
429 select distinct SEGMENT2
430 from
431 per_all_assignments_f paaf,
432 hr_soft_coding_keyflex hsck
433 where
434 paaf.assignment_id = p_asg_id
435 and paaf.business_group_id = p_business_group_id
436 and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
437 AND p_date between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;
438
439 l_asg_lu_id hr_soft_coding_keyflex.SEGMENT2%TYPE;
440
441 CURSOR csr_lu_org_number
442 (p_business_group_id per_business_groups.business_group_id%TYPE,
443 p_asg_lu_org_id number)
444 IS
445 select hoi.ORG_INFORMATION1
446 FROM
447 HR_ORGANIZATION_INFORMATION hoi,
448 HR_ORGANIZATION_UNITS hou,
449 HR_ORGANIZATION_INFORMATION hoi1
450 where hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
451 and HOi1.ORG_INFORMATION_CONTEXT = 'CLASS'
452 and HOI1.org_information2 ='Y'
453 and HOI1.org_information1 = 'NO_LOCAL_UNIT'
454 and HOI.organization_id = HOI1.organization_id
455 and hou.organization_id = hoi.organization_id
456 and hou.business_group_id= p_business_group_id
457 and hoi.organization_id = p_asg_lu_org_id;
458
459 l_asg_lu_org_num HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1%TYPE;
460
461 CURSOR Cur_Rec_uploaded
462 (
463 --p_mun_sso_num IN VARCHAR2
464 p_le_org_num IN VARCHAR2
465 ,p_lu_org_num IN VARCHAR2
466 --,p_ref_code IN VARCHAR2
467 ,p_emp_ni_num IN VARCHAR2
468 ,p_reimb_start_dt IN VARCHAR2
469 ,p_reimb_end_dt IN VARCHAR2
470 ,p_reimb_amount IN VARCHAR2
471 ,p_max_reimb_dt VARCHAR2
472 )
473 IS
474 SELECT 'Y'
475 FROM
476 PAY_ACTION_INFORMATION
477 WHERE
478 action_information_category = 'EMEA REPORT INFORMATION'
479 and action_information1 = 'K27_REIMB_UPLOAD'
480 --and action_information2 = p_mun_sso_num
481 and action_information3 = p_le_org_num
482 and action_information4 = p_lu_org_num
483 --and action_information5 = p_ref_code
484 and action_information6 = p_emp_ni_num
485 and action_information7 = p_reimb_start_dt
486 and action_information10 = p_reimb_end_dt
487 and action_information11 = p_reimb_amount
488 and nvl(action_information13,1) = nvl(p_max_reimb_dt,1); -- added nvl for bug 16318591
489
490 l_rec_uploaded VARCHAR2(1) := 'N';
491
492 CURSOR cur_is_payroll_run(p_assignment_id NUMBER, p_bg_id NUMBER, p_payroll_id NUMBER, p_period_date DATE )
493 IS
494 select 'Y' from
495 pay_payroll_actions ppa,
496 pay_assignment_actions paa
497 where
498 ppa.payroll_action_id = paa.payroll_action_id
499 and paa.assignment_id = p_assignment_id
500 and ppa.action_type in ('R','Q')
501 and ppa.business_group_id = p_bg_id
502 and ppa.action_status = 'C'
503 and ppa.payroll_id = p_payroll_id
504 and exists
505 (select 1
506 from per_time_periods ptp
507 where
508 ptp.payroll_id = ppa.payroll_id
512
509 and ptp.time_period_id = ppa.time_period_id
510 and p_period_date BETWEEN ptp.start_date AND ptp.end_date
511 );
513 l_is_payroll_run VARCHAR2(1) := 'N';
514
515 ------------
516 /* This function is used to read each assignment line and split the data into columns.
517 * This function takes the below arguments
518 * in_line - Each Line, which contains the delimiter tokens.
519 * token_index - Nth Occurance of the token.
520 * delim - Delimiter token.
521 * return value - String between (N-1)th Occurence and Nth Occurence of the delimiter.
522 */
523
524 function get_token(
525 in_line varchar2,
526 token_index number,
527 delim varchar2 default ';'
528 )
529 return varchar2
530 is
531 start_pos number;
532 end_pos number;
533 begin
534 if token_index = 1 then
535 start_pos := 1;
536 else
537 start_pos := instr(in_line, delim, 1, token_index - 1);
538 if start_pos = 0 then
539 return null;
540 else
541 start_pos := start_pos + length(delim);
542 end if;
543 end if;
544
545 end_pos := instr(in_line, delim, start_pos, 1);
546
547 if end_pos = 0 then
548 return replace(trim(substr(in_line, start_pos)),'"');
549 else
550 return replace(trim(substr(in_line, start_pos, end_pos - start_pos)),'"');
551 end if;
552
553 end get_token;
554
555 /* This function is used to count the number of occurances of the given delimiter.
556 * in_line - Input Line
557 * return value - Number of occurances.
558 */
559 function count_tokens (in_line varchar2,
560 delim varchar2 default ';')
561 return number is
562 l_token_count number := 0;
563 l_start number :=0;
564 begin
565 while true loop
566 l_start := instr(in_line, delim, l_start+length(delim), 1);
567 if l_start = 0 then
568 -- No More Token Found. Hence return the count.
569 exit;
570 else
571 -- One more Token Found. Increment the count.
572 l_token_count := l_token_count+1;
573 end if;
574 end loop;
575 return l_token_count;
576 end count_tokens;
577
578 BEGIN
579
580 fnd_file.PUT_LINE(FND_FILE.LOG, 'File Name:'|| p_filename);
581 fnd_file.PUT_LINE(FND_FILE.LOG, 'p_test_submission:'||p_test_submission);
582 fnd_file.PUT_LINE(FND_FILE.LOG, 'g_period_date:'||g_period_date);
583
584
585 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
586
587 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_location:'||l_location);
588
589 l_file_handle := utl_file.fopen(l_location,p_filename,'r');
590
591 --13359423
592 /* if (count_tokens(l_present_line) <> 5) then
593 retcode := 2;
594 errbuf := 'Header Record Format altered.';
595 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Delimiter count is not as expected. Record Format altered.');
596 raise invalid_file_format;
597 end if; */
598 --13359423
599
600
601 --fnd_file.PUT_LINE(FND_FILE.LOG, '+++++++++BODY+++++++++');
602
603 LOOP
604 BEGIN
605 l_skip_flag := 'N';
606 l_format_altered := 'N';
607 l_assignment_id := NULL;
608 l_payroll_id := NULL;
609 l_element_link_id := -1;
610 l_asg_lu_id := NULL;
611 l_rec_uploaded := 'N';
612 l_is_payroll_run := 'N';
613
614 utl_file.get_line(l_file_handle,l_present_line); --Reading Body values
615 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line:'||l_present_line);
616
617 l_line_number := l_line_number + 1;
618 fnd_file.PUT_LINE(FND_FILE.LOG, 'Line Number:'||l_line_number);
619
620 fnd_file.PUT_LINE(FND_FILE.OUTPUT, '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
621 fnd_file.PUT_LINE(FND_FILE.OUTPUT, 'Line Number '||l_line_number||':'||l_present_line);
622
623 IF (count_tokens(l_present_line) <> 11) then
624 retcode := 1;
625 errbuf := 'Record Format altered.';
626 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Delimiter count is not as expected. Record Format altered. Skipping Line Number'||l_line_number);
627 l_format_altered := 'Y';
628 END IF;
629
630 IF l_format_altered = 'N' THEN
631
632 l_mun_sso_num := get_token(l_present_line,1);
633 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_mun_sso_num:'||l_mun_sso_num);
634
635 l_le_org_num := get_token(l_present_line,2);
636 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_le_org_num:'||l_le_org_num);
637
638 l_lu_org_num := get_token(l_present_line,3);
639 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_lu_org_num:'||l_lu_org_num);
640
641 l_ref_code := get_token(l_present_line,4);
642 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_ref_code:'||l_ref_code);
643
644 l_emp_ni_num := get_token(l_present_line,5);
645 l_emp_ni_num := lpad(l_emp_ni_num,11,'0');
646 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_emp_ni_num:'||l_emp_ni_num);
647
648 l_reimb_start_dt := get_token(l_present_line,6);
649 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_reimb_start_dt:'||l_reimb_start_dt);
650
651 l_le_bank_ac_num := get_token(l_present_line,7);
652 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_le_bank_ac_num:'||l_le_bank_ac_num);
653
654 l_emp_name := get_token(l_present_line,8);
655 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_emp_name:'||l_emp_name);
656
657 l_reimb_end_dt := get_token(l_present_line,9);
658 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_reimb_end_dt:'||l_reimb_end_dt);
659
663 IF substr(l_reimb_amount,11,1) = '-' THEN
660 l_reimb_amount := get_token(l_present_line,10);
661 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_reimb_amount:'||l_reimb_amount);
662
664 l_reimb_amount := '-'||substr(l_reimb_amount,1,10);
665 fnd_file.PUT_LINE(FND_FILE.LOG, '1:l_reimb_amount:'||l_reimb_amount);
666 END IF;
667
668 l_filler := get_token(l_present_line,11);
669 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_filler:'||l_filler);
670
671 l_max_reimb_dt := get_token(l_present_line,12);
672 IF substr(l_max_reimb_dt,1,8) = '00000000' THEN -- Bug 16318591 --added if condition for 16318591
673 l_max_reimb_dt := null;
674 END IF;
675 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_max_reimb_dt:'||l_max_reimb_dt);
676
677
678 fnd_file.PUT_LINE(FND_FILE.LOG, 'p_employer_number:'||p_employer_number);
679
680 IF p_employer_number <> l_le_org_num THEN
681 fnd_file.PUT_LINE(FND_FILE.output, 'Employer Organization Number does not match. Skipping line number '||l_line_number);
682 l_skip_flag := 'Y';
683 retcode := 1;
684 errbuf := 'Employer Organization Number mismatch.';
685 END IF;
686
687 OPEN csr_get_prim_assg
688 ( p_business_group_id => g_business_group_id
689 ,p_ni_number => l_emp_ni_num
690 ,p_date => g_period_date) ;
691
692 FETCH csr_get_prim_assg INTO l_assignment_id, l_payroll_id;
693
694 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_assignment_id:'||l_assignment_id||'-Payroll_id-'||l_payroll_id);
695
696 IF csr_get_prim_assg%NOTFOUND THEN
697 fnd_file.PUT_LINE(FND_FILE.output, 'No valid primary assignment exists for NI Number '||l_emp_ni_num||'. Skipping line number '||l_line_number); --14363238
698 l_skip_flag := 'Y';
699 retcode := 1;
700 errbuf := 'No primary assignment exists.';
701 ELSE
702 OPEN csr_asg_lu_id
703 ( g_business_group_id
704 ,l_assignment_id
705 ,g_period_date
706 );
707 FETCH csr_asg_lu_id INTO l_asg_lu_id;
708 CLOSE csr_asg_lu_id;
709
710 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_asg_lu_id:'||l_asg_lu_id);
711
712 OPEN csr_lu_org_number
713 (g_business_group_id,
714 l_asg_lu_id);
715 FETCH csr_lu_org_number INTO l_asg_lu_org_num;
716 CLOSE csr_lu_org_number;
717
718 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_asg_lu_org_num:'||l_asg_lu_org_num);
719
720 IF l_asg_lu_org_num <> l_lu_org_num THEN
721 fnd_file.PUT_LINE(FND_FILE.output, 'Local Unit Organization Number does not match for NI Number '||l_emp_ni_num||'. Skipping line number '||l_line_number);
722 l_skip_flag := 'Y';
723 retcode := 1;
724 errbuf := 'Local Unit Organization Number mismatch.';
725 END IF;
726 END IF;
727 CLOSE csr_get_prim_assg;
728
729 l_element_link_id := get_element_link_id(l_assignment_id, g_business_group_id, g_period_date, 'K27 Reimbursement File Details');
730
731 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_element_link_id:'||l_element_link_id);
732
733 IF l_element_link_id = -1 THEN
734 fnd_file.PUT_LINE(FND_FILE.output, 'No K27 Reimbursement File Details element link exists for NI Number '||l_emp_ni_num||'. Skipping line number '||l_line_number);
735 l_skip_flag := 'Y';
736 retcode := 1;
737 errbuf := 'No element link exists.';
738 END IF;
739
740 OPEN Cur_Rec_uploaded
741 (
742 --l_mun_sso_num
743 l_le_org_num
744 ,l_lu_org_num
745 --,l_ref_code
746 ,l_emp_ni_num
747 ,l_reimb_start_dt
748 ,l_reimb_end_dt
749 ,l_reimb_amount
750 ,l_max_reimb_dt
751 );
752 FETCH Cur_Rec_uploaded INTO l_rec_uploaded;
753 CLOSE Cur_Rec_uploaded;
754
755 IF l_rec_uploaded = 'Y'
756 THEN
757 l_skip_flag := 'Y';
758 fnd_file.PUT_LINE(FND_FILE.output, 'Record already uploaded for NI Number '||l_emp_ni_num||'. Skipping line number '||l_line_number);
759 retcode := 1;
760 errbuf := 'Record already uploaded.';
761 END IF;
762
763 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_rec_uploaded:'||l_rec_uploaded);
764 fnd_file.PUT_LINE(FND_FILE.LOG, 'l_skip_flag:'||l_skip_flag);
765 fnd_file.PUT_LINE(FND_FILE.LOG, 'p_test_submission:'||p_test_submission);
766
767 OPEN cur_is_payroll_run(l_assignment_id, g_business_group_id, l_payroll_id, g_period_date);
768 FETCH cur_is_payroll_run INTO l_is_payroll_run;
769 CLOSE cur_is_payroll_run;
770
771 IF l_is_payroll_run = 'Y'
772 THEN
773 l_skip_flag := 'Y';
774 fnd_file.PUT_LINE(FND_FILE.output, 'Payroll already run for NI Number '||l_emp_ni_num||'. Skipping line number '||l_line_number);
775 retcode := 1;
776 errbuf := 'Payroll already run.';
777 END IF;
778
779 IF l_skip_flag = 'N' AND p_test_submission = 'N' THEN
780
781 create_k27_element ( g_period_date
782 , l_assignment_id
783 , g_business_group_id
784 , to_date(l_reimb_start_dt,'RRRRMMDD')
785 , to_date(l_reimb_end_dt,'RRRRMMDD')
786 , to_date(l_max_reimb_dt,'RRRRMMDD')
787 , fnd_number.canonical_to_number(l_reimb_amount)/100
788 , l_mun_sso_num
789 , l_ref_code
790 );
791
792 pay_action_information_api.create_action_information (
793 p_action_information_id => l_action_info_id
794 ,p_action_context_id => g_payroll_action_id
795 ,p_action_context_type => 'PA'
796 ,p_object_version_number => l_ovn
797 ,p_effective_date => g_effective_date
798 ,p_source_id => NULL
802 ,p_action_information1 => 'K27_REIMB_UPLOAD'
799 ,p_source_text => NULL
800 ,p_action_information_category => 'EMEA REPORT INFORMATION'
801 ,p_assignment_id => l_assignment_id
803 ,p_action_information2 => l_mun_sso_num
804 ,p_action_information3 => l_le_org_num
805 ,p_action_information4 => l_lu_org_num
806 ,p_action_information5 => l_ref_code
807 ,p_action_information6 => l_emp_ni_num
808 ,p_action_information7 => l_reimb_start_dt
809 ,p_action_information8 => l_le_bank_ac_num
810 ,p_action_information9 => l_emp_name
811 ,p_action_information10 => l_reimb_end_dt
812 ,p_action_information11 => l_reimb_amount
813 ,p_action_information12 => l_filler
814 ,p_action_information13 => l_max_reimb_dt
815 ,p_action_information14 => to_char(g_period_date,'RRRR/MM/DD HH:MI:SS')
816 ,p_action_information15 => g_element_entry_id
817 ,p_action_information16 => null
818 ,p_action_information17 => null
819 ,p_action_information18 => null
820 ,p_action_information19 => null
821 ,p_action_information20 => null
822 ,p_action_information21 => null
823 ,p_action_information22 => null
824 ,p_action_information23 => null
825 ,p_action_information24 => null
826 ,p_action_information25 => null
827 ,p_action_information26 => null
828 ,p_action_information27 => null
829 ,p_action_information28 => null
830 ,p_action_information29 => null
831 ,p_action_information30 => p_filename);
832 END IF;
833
834 IF l_skip_flag = 'N' THEN
835 fnd_file.PUT_LINE(FND_FILE.output, 'Line Number '||l_line_number||' passed.');
836 END IF;
837
838 END IF; --l_format_altered
839
840 EXCEPTION
841 WHEN NO_DATA_FOUND THEN
842 fnd_file.PUT_LINE(FND_FILE.LOG, 'End of file reached.');
843 EXIT;
844 WHEN OTHERS THEN
845 fnd_file.PUT_LINE(FND_FILE.LOG, 'Other error occured');
846 fnd_file.PUT_LINE(FND_FILE.LOG, sqlerrm);
847 retcode := 2;
848 errbuf := 'Other error occured';
849 EXIT;
850 END;
851 END LOOP;
852
853 EXCEPTION
854 WHEN e_fatal_error
855 THEN
856 hr_utility.set_location( l_pkg ,100);
857 retcode := 2;
858 errbuf := 'Reading File - Invalid Operation (file not found).';
859
860 WHEN UTL_FILE.INVALID_OPERATION
861 THEN
862
863 UTL_FILE.FCLOSE(l_file_handle);
864 hr_utility.set_location( l_pkg ,110);
865 retcode:=2;
866 errbuf := 'Reading File - Invalid Operation (file not found).';
867
868 WHEN UTL_FILE.INTERNAL_ERROR
869 THEN
870
871 UTL_FILE.FCLOSE(l_file_handle);
872 hr_utility.set_location( l_pkg,120);
873 retcode:=2;
874 errbuf := 'Reading File - Internal Error.';
875 WHEN UTL_FILE.INVALID_MODE
876 THEN
877
878 UTL_FILE.FCLOSE(l_file_handle);
879 hr_utility.set_location( l_pkg,130);
880 retcode:=2;
881 errbuf := 'Reading Flat File - Invalid Mode.';
882
883 WHEN UTL_FILE.INVALID_PATH
884 THEN
885
886 UTL_FILE.FCLOSE(l_file_handle);
887 hr_utility.set_location( l_pkg ||'PAYE Upload',140);
888 retcode:=2;
889 errbuf := 'Reading File - Invalid Path.';
890
891 WHEN UTL_FILE.INVALID_FILEHANDLE
892 THEN
893
894 UTL_FILE.FCLOSE(l_file_handle);
895 hr_utility.set_location( l_pkg,150);
896 retcode:=2;
897 errbuf := 'Reading File - Invalid File Handle.';
898
899 WHEN UTL_FILE.READ_ERROR
900 THEN
901
902 UTL_FILE.FCLOSE(l_file_handle);
903 hr_utility.set_location( l_pkg,160);
904 retcode:=2;
905 errbuf := 'Reading File - Read Error.';
906
907 WHEN NO_DATA_FOUND
908 THEN
909 UTL_FILE.FCLOSE(l_file_handle);
910 hr_utility.set_location( l_pkg,170);
911 retcode:=2;
912 errbuf := 'No Data Found.';
913
914 WHEN INVALID_FILE_FORMAT
915 THEN
916 UTL_FILE.FCLOSE(l_file_handle);
917 hr_utility.set_location( l_pkg,180);
918 retcode:=2;
919 errbuf := 'Reading File - Invalid File Format.';
920
921 WHEN NO_REC_FOUND_IN_FILE
922 THEN
923 UTL_FILE.FCLOSE(l_file_handle);
924 hr_utility.set_location( l_pkg,190);
925 retcode:=2;
926 errbuf := 'Reading File - No Records found.';
927
928 WHEN others
929 THEN
930 retcode:=2;
931 errbuf := 'Exception occured :'||sqlerrm;
932 hr_utility.set_location( l_pkg,210);
933
934 END READ_K27_FILE;
935
936
937 /* RANGE CODE */
938 PROCEDURE RANGE_CODE
939 (p_payroll_action_id IN NUMBER
940 ,p_sql OUT NOCOPY VARCHAR2)
941 IS
942
943 l_count NUMBER := 0;
944 l_business_group_id NUMBER;
945
946 -- l_company_terminating varchar2(1); bug 10367494
947 l_emp_id hr_organization_units.organization_id%TYPE ;
948 l_le_name hr_organization_units.name%TYPE ;
949 l_business_id hr_organization_information.org_information1%TYPE ;
950 l_cvr_number hr_organization_information.org_information1%TYPE ;
951 l_sp_cvr_number hr_organization_information.org_information1%TYPE ;
952 l_org_type hr_organization_information.org_information1%TYPE ;
953 l_date VARCHAR2(100);
954 l_time VARCHAR2(10);
955
956 l_date_id VARCHAR2(20);
957 l_time_id VARCHAR2(20);
958
962
959 l_lb_num VARCHAR2(10);
960 l_unique_id VARCHAR2(50);
961 l_unique_id1 VARCHAR2(50); /* 9489806 */
963 l_canonical_start_date DATE; /* 9489806 */
964 l_canonical_end_date DATE; /* 9489806 */
965
966 l_errbuf VARCHAR2(1000);
967 l_retcode VARCHAR2(10);
968 l_filename VARCHAR2(1000);
969 l_employer_number VARCHAR2(100);
970 l_test_submission VARCHAR2(10);
971 l_effective_date DATE;
972
973 /*Cursors */
974
975 /*Legal Employer Information*/
976 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
977 IS
978 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
979 FROM hr_organization_units o1
980 , hr_organization_information hoi1
981 , hr_organization_information hoi2
982 WHERE o1.business_group_id =l_business_group_id
983 AND hoi1.organization_id = o1.organization_id
984 AND hoi1.organization_id = csr_v_legal_emp_id
985 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
986 AND hoi1.org_information_context = 'CLASS'
987 AND o1.organization_id =hoi2.organization_id
988 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
989
990 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
991
992 /*Legal Employer MO, FO, EH Codes */
993 Cursor csr_Legal_Emp_MO_FO_FH ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
994 IS
995 SELECT
996 hoi2.ORG_INFORMATION2,
997 hoi2.ORG_INFORMATION3,
998 hoi2.ORG_INFORMATION4,
999 hoi2.ORG_INFORMATION5,
1000 hoi2.ORG_INFORMATION6
1001 FROM hr_organization_units o1
1002 , hr_organization_information hoi1
1003 , hr_organization_information hoi2
1004 WHERE o1.business_group_id =l_business_group_id
1005 AND hoi1.organization_id = o1.organization_id
1006 AND hoi1.organization_id = csr_v_legal_emp_id
1007 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1008 AND hoi1.org_information_context = 'CLASS'
1009 AND o1.organization_id =hoi2.organization_id
1010 AND hoi2.ORG_INFORMATION_CONTEXT='DK_DA_OFFICE_CODE';
1011
1012 rg_Legal_Emp_MO_FO_FH csr_Legal_Emp_MO_FO_FH%rowtype;
1013
1014
1015 -- Service Provider information.
1016 cursor service_provider_details
1017 is
1018 select * from hr_organization_information
1019 where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
1020 and organization_id in (
1021 select organization_id from hr_organization_units
1022 where business_group_id= l_business_group_id);
1023
1024 sp service_provider_details%rowtype;
1025
1026 /* Payroll Time period */
1027 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
1028 IS
1029 SELECT ptp.start_date, ptp.end_date, ptp.period_name,
1030 default_dd_date,
1031 decode(PERIOD_TYPE
1032 ,'Calendar Month','1'
1033 ,'Bi-Week' ,'2'
1034 ,'Week' ,'3'
1035 ,'Lunar Month' ,'4') PAYROLL_PERIOD
1036 from per_time_periods ptp
1037 WHERE payroll_id = p_payroll_id
1038 AND time_period_id = p_payroll_period;
1039
1040 rg_csr_pay_periods csr_pay_periods%rowtype;
1041
1042 /* Payroll Name */
1043 CURSOR csr_payroll_name(id pay_all_payrolls_f.payroll_id%type)
1044 IS
1045 select payroll_name from pay_payrolls_f
1046 where payroll_id=id;
1047
1048 -- rg_csr_payroll_name csr_payroll_name%rowtype;
1049 l_payroll_name VARCHAR2(100):=NULL;
1050
1051 CURSOR csr_le_payrolls is /* 9489806 */
1052 SELECT DISTINCT
1053 ppf.payroll_name,
1054 ppf.payroll_id,
1055 ptp.start_date,
1056 ptp.end_date,
1057 ptp.period_name,
1058 ptp.default_dd_date,
1059 decode(ptp.PERIOD_TYPE
1060 ,'Calendar Month','1'
1061 ,'Bi-Week' ,'2'
1062 ,'Week' ,'3'
1063 ,'Lunar Month' ,'4') PAYROLL_PERIOD,
1064 ptp.TIME_PERIOD_ID
1065 FROM
1066 pay_payrolls_f ppf,
1067 per_assignments_f paf,
1068 hr_organization_units hou,
1069 hr_organization_information hoi,
1070 hr_soft_coding_keyflex scl,
1071 per_time_periods ptp
1072 WHERE hou.business_group_id = l_business_group_id
1073 AND hou.organization_id = g_legal_employer_id
1074 AND hou.organization_id = hoi.organization_id
1075 AND hoi.org_information_context = 'CLASS'
1076 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
1077 AND paf.payroll_id = ppf.payroll_id
1078 AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
1079 AND ppf.effective_start_date <= l_canonical_start_date and ppf.effective_end_date >= l_canonical_end_date
1080 AND ptp.payroll_id=ppf.payroll_id
1081 AND ptp.end_date BETWEEN l_canonical_start_date AND l_canonical_end_date
1082 AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
1083 AND scl.segment1 = to_char(hou.organization_id)
1084 AND ppf.business_group_id = l_business_group_id;
1085
1086 /* End of Cursors */
1087
1088
1089 Cursor cur_report_type IS
1090 select
1091 report_type
1092 from
1093 pay_payroll_actions
1094 where payroll_action_id = p_payroll_action_id;
1095
1096 l_report_type VARCHAR2(100);
1097
1098 CURSOR csr_use_hol_card(p_org_id NUMBER) is
1099 SELECT NVL(org_information5,'N')
1100 FROM hr_organization_information
1101 WHERE organization_id = p_org_id
1102 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
1103
1104 CURSOR get_value_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
1105 SELECT PRL_INFORMATION1, Payroll_name
1106 FROM pay_payrolls_f ppf
1107 WHERE PAYROLL_ID = p_payroll_id
1108 AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
1109
1110 BEGIN
1111
1112 hr_utility.set_location(' Entering Procedure RANGE_CODE',1);
1116 END IF;
1113
1114 IF g_debug THEN
1115 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
1117
1118 p_sql := 'SELECT DISTINCT person_id FROM per_people_f ppf,pay_payroll_actions ppa WHERE ppa.payroll_action_id = :payroll_action_id
1119 AND ppa.business_group_id = ppf.business_group_id and 1=2 ORDER BY ppf.person_id ';
1120
1121 hr_utility.set_location('RANGE_CODE:Before Calling GET_ALL_PARAMETERS',2);
1122 g_payroll_action_id := p_payroll_action_id;
1123
1124 PAY_NO_K27_REIM_UPLD.GET_ALL_PARAMETERS
1125 ( p_payroll_action_id
1126 ,g_business_group_id
1127 ,l_employer_number
1128 ,g_effective_date
1129 ,l_test_submission
1130 ,l_filename
1131 ,g_period_date);
1132
1133 hr_utility.set_location('RANGE_CODE:After Calling GET_ALL_PARAMETERS',3);
1134
1135 /* Pick up the details belonging to Legal Employer
1136
1137 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
1138 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
1139 CLOSE csr_Legal_Emp_Details;
1140
1141 l_le_name := rg_Legal_Emp_Details.name ;
1142 l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ; */
1143
1144
1145 READ_K27_FILE(
1146 l_errbuf
1147 , l_retcode
1148 , l_filename
1149 , l_employer_number
1150 , l_test_submission);
1151
1152 IF l_retcode = 2 THEN
1153 Fnd_file.put_line(FND_FILE.LOG,l_errbuf);
1154 Fnd_file.put_line(FND_FILE.OUTPUT,'Report completed with Error(s)');
1155 Fnd_file.put_line(FND_FILE.OUTPUT,l_errbuf);
1156 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
1157 'Report completed with Error(s).Please check the log/output file.');
1158 ELSIF l_retcode = 1 THEN
1159 Fnd_file.put_line(FND_FILE.LOG,l_errbuf);
1160 Fnd_file.put_line(FND_FILE.OUTPUT,'Report completed with warning(s)');
1161 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',
1162 'Report completed with validation warning(s).Please check the log/output file.');
1163 END IF;
1164
1165
1166 IF g_debug THEN
1167 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN others THEN
1172 IF g_debug THEN
1173 hr_utility.set_location('error raised assignment_action_code ',5);
1174 END if;
1175 RAISE;
1176 END RANGE_CODE;
1177
1178 /* ASSIGNMENT ACTION CODE */
1179 PROCEDURE ASSIGNMENT_ACTION_CODE
1180 (p_payroll_action_id IN NUMBER
1181 ,p_start_person IN NUMBER
1182 ,p_end_person IN NUMBER
1183 ,p_chunk IN NUMBER )
1184 IS
1185
1186 BEGIN
1187 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE START 1');
1188 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE END 2');
1189 EXCEPTION
1190 WHEN others THEN
1191 IF g_debug THEN
1192 hr_utility.set_location('error raised assignment_action_code ',5);
1193 END if;
1194 RAISE;
1195 END ASSIGNMENT_ACTION_CODE;
1196
1197
1198 /* INITIALIZATION CODE */
1199 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1200 IS
1201
1202 BEGIN
1203 IF g_debug THEN
1204 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
1205 END IF;
1206 IF g_debug THEN
1207 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
1208 END IF;
1209
1210 EXCEPTION
1211 WHEN others THEN
1212 IF g_debug THEN
1213 hr_utility.set_location('error raised initialization code ',5);
1214 END if;
1215 RAISE;
1216 END INITIALIZATION_CODE;
1217
1218 /* ARCHIVE CODE */
1219 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1220 ,p_effective_date IN DATE)
1221 IS
1222 BEGIN
1223 fnd_file.put_line(fnd_file.log,'ARCHIVE_CODE START 1');
1224 fnd_file.put_line(fnd_file.log,'ARCHIVE_CODE END 2');
1225 EXCEPTION
1226 WHEN others THEN
1227 IF g_debug THEN
1228 hr_utility.set_location('error raised in archive code ',5);
1229 END if;
1230 RAISE;
1231 END ARCHIVE_CODE;
1232
1233 PROCEDURE DEINITIALIZATION_CODE
1234 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
1235 BEGIN
1236 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE START 1');
1237 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE END 2');
1238 EXCEPTION
1239 WHEN others THEN
1240 IF g_debug THEN
1241 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',999);
1242 END if;
1243 RAISE;
1244 END DEINITIALIZATION_CODE;
1245
1246 PROCEDURE K27_NOTIFICATION_REP
1247 ( errbuf OUT NOCOPY VARCHAR2
1248 , retcode OUT NOCOPY VARCHAR2
1249 , p_legal_employer IN VARCHAR2
1250 , p_Date_from IN VARCHAR2
1251 , p_Date_to IN VARCHAR2)
1252 IS
1253
1254 l_date_from DATE;
1255 l_date_to DATE;
1256 /* modified cursor for bug 16290513 */
1257 /* CURSOR cur_k27_employee_list(p_legal_employer VARCHAR2, p_from_dt DATE, p_to_dt DATE)
1258 IS
1259 SELECT substr(action_information6,1,6)||'-'||substr(action_information6,7,5) NI_number,
1260 to_char(to_date(action_information13,'RRRRMMDD'),'DD-MON-RRRR') Reimb_date
1261 FROM
1262 PAY_ACTION_INFORMATION
1263 WHERE
1264 action_information_category = 'EMEA REPORT INFORMATION'
1265 and action_information1 = 'K27_REIMB_UPLOAD'
1266 and action_information3 = p_legal_employer
1267 and to_date(action_information13,'RRRRMMDD') BETWEEN p_from_dt AND p_to_dt; */
1268
1269 CURSOR cur_k27_employee_list(p_legal_employer VARCHAR2,
1270 p_from_dt DATE, p_to_dt DATE) IS
1271 SELECT substr(pai.action_information6,1,6)||'-'||substr(pai.action_information6,7,5) NI_number,
1272 to_char(to_date(pai.action_information13,'RRRRMMDD'),'DD-MON-RRRR') Reimb_date,
1273 papf.employee_number emp_number,
1274 pai.action_information11 reimburse_amount FROM
1275 pay_action_information pai,
1276 per_all_assignments_f paaf ,
1277 per_all_people_f papf
1278 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1279 and pai.action_information1 = 'K27_REIMB_UPLOAD'
1280 and pai.action_information3 = p_legal_employer
1281 and to_date(pai.action_information13,'RRRRMMDD') BETWEEN p_from_dt AND p_to_dt
1282 and paaf.assignment_id = pai.assignment_id
1283 and to_date(pai.action_information13,'RRRRMMDD') between paaf.effective_start_date and paaf.effective_end_date
1284 and paaf.person_id = papf.person_id
1285 and to_date(pai.action_information13,'RRRRMMDD') between papf.effective_start_date and papf.effective_end_date;
1286
1287
1288 BEGIN
1289 hr_utility.set_location('Entering K27_NOTIFICATION_REP ',800);
1290
1291 fnd_file.PUT_LINE(FND_FILE.LOG,'p_legal_employer:'||p_legal_employer);
1292 fnd_file.PUT_LINE(FND_FILE.LOG,'p_Date_from:'||p_Date_from);
1293 fnd_file.PUT_LINE(FND_FILE.LOG,'p_Date_to:'||p_Date_to);
1294
1295 l_date_from := fnd_date.canonical_to_date(p_Date_from);
1296 l_date_to := fnd_date.canonical_to_date(p_Date_to);
1297
1298 fnd_file.PUT_LINE(FND_FILE.LOG,'l_Date_from:'||l_Date_from);
1299 fnd_file.PUT_LINE(FND_FILE.LOG,'l_Date_to:'||l_Date_to);
1300
1301 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'------------------- --------------------- ------------------------ -------------------------');
1302 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number NI Number Reimbursement Max Date Reimbursement Amount');
1303 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'------------------- --------------------- ------------------------ -------------------------');
1304
1305 FOR I IN cur_k27_employee_list(p_legal_employer, l_date_from, l_date_to)
1306 LOOP
1307
1308 fnd_file.PUT_LINE(FND_FILE.OUTPUT,RPAD(I.emp_number,21,' ')||RPAD(I.NI_number,25,' ')||RPAD(I. Reimb_date,26,' ')||I.reimburse_amount);
1309
1310 END LOOP;
1311
1312 hr_utility.set_location('Leaving K27_NOTIFICATION_REP ',888);
1313
1314 EXCEPTION
1315 WHEN others THEN
1316 IF g_debug THEN
1317 hr_utility.set_location('error raised in K27_NOTIFICATION_REP ',888);
1318 END if;
1319 RAISE;
1320 END k27_notification_rep;
1321
1322 BEGIN
1323 g_payroll_action_id :=NULL;
1324 g_business_group_id :=NULL;
1325 g_effective_date :=NULL;
1326 g_test_submission :=NULL;
1327
1328 END PAY_NO_K27_REIM_UPLD;