[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_TC_DP_UPLOAD
Source
1 PACKAGE BODY pay_dk_tc_dp_upload AS
2 /* $Header: pydktaxu.pkb 120.6.12010000.7 2010/04/07 06:44:33 rsahai ship $ */
3 g_package CONSTANT VARCHAR2 (33) := ' pay_dk_tc_dp_upload';
4 g_batch_header VARCHAR2 (50);
5 g_batch_source VARCHAR2 (50);
6 g_batch_comments VARCHAR2 (100);
7 e_wrong_indvnr EXCEPTION;
8 e_invalid_value EXCEPTION;
9 e_record_length_err EXCEPTION;
10 e_no_tax_link EXCEPTION;
11
12 PRAGMA exception_init (e_invalid_value, -1858);
13 -- Global constants
14 c_warning CONSTANT NUMBER := 1;
15 c_error CONSTANT NUMBER := 2;
16 c_end_of_time CONSTANT DATE := to_date('12/31/4712','MM/DD/YYYY');
17
18 PROCEDURE upload(
19 errbuf OUT NOCOPY VARCHAR2,
20 retcode OUT NOCOPY NUMBER,
21 p_file_name IN VARCHAR2,
22 p_effective_date IN VARCHAR2,
23 p_business_group_id IN per_business_groups.business_group_id%TYPE,
24 p_batch_name IN VARCHAR2 DEFAULT NULL,
25 p_reference IN VARCHAR2 DEFAULT NULL
26 )
27 IS
28
29 -- Constants
30 c_read_file CONSTANT VARCHAR2 (1) := 'r';
31 c_max_linesize CONSTANT NUMBER := 4000;
32 c_commit_point CONSTANT NUMBER := 20;
33 c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
34
35 -- Procedure name
36 l_proc CONSTANT VARCHAR2 (72) := g_package||'.upload' ;
37 l_legislation_code per_business_groups.legislation_code%TYPE;
38 l_bg_name per_business_groups.name%TYPE;
39
40 -- File Handling variables
41 l_file_type UTL_FILE.file_type;
42 l_filename VARCHAR2 (240);
43 l_location VARCHAR2 (4000);
44 l_line_read VARCHAR2 (4000);
45 -- Batch Variables
46 l_batch_seq NUMBER;
47 l_batch_id NUMBER;
48 l_record_length NUMBER;
49
50 -- Parameter values to create Batch Lines
51 l_ee_user_key VARCHAR2(240);
52 L_ASSIGNMENT_USER_KEY VARCHAR2(240);
53 L_ELEMENT_LINK_USER_KEY VARCHAR2(240);
54 l_user_key_value hr_pump_batch_line_user_keys.user_key_value%type;
55 l_unique_key_id hr_pump_batch_line_user_keys.unique_key_id%type;
56 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
57 l_datetrack_update_mode VARCHAR2(80);
58
59 -- Variables to Read from File
60 l_cpr VARCHAR2(80);
61 l_employer_org_no VARCHAR2(80);
62 l_effective_date VARCHAR2(80);
63 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
64 L_ELEMENT_LINK_ID pay_element_links_f.ELEMENT_LINK_ID%TYPE;
65 l_element_name pay_element_types_f.element_name%TYPE;
66 -- Added for Flugger fix
67 l_element_name_tl PAY_ELEMENT_TYPES_F_TL.element_name%TYPE;
68 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
69 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
70 l_input_value_name1 VARCHAR2(80);
71 l_input_value_name2 VARCHAR2(80);
72 l_input_value_name3 VARCHAR2(80);
73 l_input_value_name4 VARCHAR2(80);
74 l_input_value_name5 VARCHAR2(80);
75 l_input_value_name6 VARCHAR2(80);
76 l_input_value_name7 VARCHAR2(80);
77 l_input_value_name8 VARCHAR2(80);
78 l_input_value_name9 VARCHAR2(80);
79 l_input_value_name10 VARCHAR2(80);
80 l_input_value_name11 VARCHAR2(80);
81 l_input_value_name12 VARCHAR2(80);
82 l_input_value_name13 VARCHAR2(80);
83 l_input_value_name14 VARCHAR2(80);
84 l_input_value_name15 VARCHAR2(80);
85 l_entry_value1 VARCHAR2(60);
86 l_entry_value2 VARCHAR2(60);
87 l_entry_value3 VARCHAR2(60);
88 l_entry_value4 VARCHAR2(60);
89 l_entry_value5 VARCHAR2(60);
90 l_entry_value6 VARCHAR2(60);
91 l_entry_value7 VARCHAR2(60);
92 l_entry_value8 VARCHAR2(60);
93 l_entry_value9 VARCHAR2(60);
94 l_entry_value10 VARCHAR2(60);
95 l_entry_value11 VARCHAR2(60);
96 l_entry_value12 VARCHAR2(60);
97 l_entry_value13 VARCHAR2(60);
98 l_entry_value14 VARCHAR2(60);
99 l_entry_value15 VARCHAR2(60);
100 l_get_indvnr VARCHAR2(4);
101 l_le_id VARCHAR2(60);
102 l_se_num VARCHAR2(60);
103 l_flag VARCHAR2(1);
104 l_end_rec_flag VARCHAR2(1);
105
106 -- Exceptions
107 e_fatal_error EXCEPTION;
108 e_prim_assg_error EXCEPTION;
109 e_element_details EXCEPTION;
110 e_legal_employer_err EXCEPTION;
111 e_file_format_err EXCEPTION;
112 e_invalid_tax_card_type EXCEPTION;
113 e_tax_percentage_err EXCEPTION;
114
115
116 --Flag variables
117 l_element_link_found VARCHAR2(30);
118 l_batch_name hr_pump_batch_headers.batch_name%type;
119
120 cursor get_batch_name(p_batch_name hr_pump_batch_headers.batch_name%type) is
121 select batch_name from hr_pump_batch_headers where batch_name = p_batch_name;
122
123 CURSOR csr_leg (v_bg_id per_business_groups.business_group_id%TYPE)
124 IS
125 SELECT legislation_code, name
126 FROM per_business_groups_perf
127 WHERE business_group_id = v_bg_id;
128
129 CURSOR csr_get_prim_assg
130 ( p_business_group_id per_business_groups.business_group_id%TYPE
131 ,p_cpr per_all_people_f.national_identifier%TYPE
132 ,p_date per_all_assignments_f.effective_start_date%TYPE)
133 IS
134 SELECT PAA.ASSIGNMENT_ID
135 FROM per_all_assignments_f PAA
136 , per_all_people_f PAP
137 WHERE PAA.BUSINESS_GROUP_ID = p_business_group_id
138 AND PAP.per_information_category ='DK'
139 AND PAP.NATIONAL_IDENTIFIER = p_cpr
140 AND PAA.PERSON_ID = PAP.PERSON_ID
141 AND PAA.PRIMARY_FLAG = 'Y'
142 AND p_date between PAA.EFFECTIVE_START_DATE and PAA.EFFECTIVE_END_DATE
143 AND p_date between PAP.EFFECTIVE_START_DATE and PAP.EFFECTIVE_END_DATE ;
144
145 CURSOR csr_get_element_details(p_assignment_id per_all_assignments_f.assignment_id%TYPE, p_date per_all_assignments_f.effective_start_date%TYPE)
146 IS
147 SELECT pee.ELEMENT_ENTRY_ID , pet.ELEMENT_NAME, pee.EFFECTIVE_START_DATE,pee.EFFECTIVE_END_DATE
148 from pay_element_entries_f pee
149 , pay_element_types_f pet
150 , pay_element_links_f pel
151 , per_all_assignments_f paa
152 where pet.ELEMENT_NAME = 'Tax Card'
153 and pet.legislation_code = 'DK'
154 and pel.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
155 and pee.ELEMENT_LINK_ID = pel.ELEMENT_LINK_ID
156 and paa.ASSIGNMENT_ID = pee.ASSIGNMENT_ID
157 and pee.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
158 and pee.ASSIGNMENT_ID = p_assignment_id
159 and p_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE
160 and p_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
161 and p_date between pel.EFFECTIVE_START_DATE and pel.EFFECTIVE_END_DATE
162 and p_date between paa.EFFECTIVE_START_DATE and paa.EFFECTIVE_END_DATE ;
163
164 CURSOR csr_get_user_key(p_user_key_value hr_pump_batch_line_user_keys.user_key_value%type)
165 IS
166 SELECT user_key_value, unique_key_id
167 FROM hr_pump_batch_line_user_keys
168 WHERE user_key_value = p_user_key_value;
169
170 CURSOR csr_get_legal_employer(v_bg_id per_business_groups.business_group_id%TYPE)
171 IS
172 SELECT hoi.org_information1, hoi.org_information5
173 FROM hr_organization_units hru, hr_organization_information hoi
174 WHERE business_group_id = v_bg_id
175 and hru.organization_id = hoi.organization_id
176 and hoi.ORG_INFORMATION_CONTEXT = 'DK_LEGAL_ENTITY_DETAILS';
177
178 rg_csr_get_legal_employer csr_get_legal_employer%rowtype;
179
180 BEGIN
181 -- Initialise Variables
182 l_line_read := NULL;
183 l_batch_seq := 0;
184 l_record_length := 159;
185 -- FILE FORMAT CHECK
186 l_filename := p_file_name;
187 fnd_profile.get (c_data_exchange_dir, l_location);
188 hr_utility.set_location ( 'Directory = ' || l_location, 30);
189 IF l_location IS NULL THEN
190 -- error : I/O directory not defined
191 RAISE e_fatal_error;
192 END IF;
193 -- Open flat file
194 -- Bug fixes 4216001 and 4216029
195 l_file_type := UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
196 l_end_rec_flag := 'N';
197 <<file_format_check>>
198 LOOP
199 BEGIN
200 UTL_FILE.get_line (l_file_type, l_line_read);
201 l_batch_seq := l_batch_seq + 1;
202 EXCEPTION
203 WHEN VALUE_ERROR THEN
204 IF UTL_FILE.is_open (l_file_type) THEN
205 UTL_FILE.fclose (l_file_type);
206 END IF;
207 hr_utility.set_location (l_proc, 300);
208 retcode := c_error;
209 -- The error will mean batch_seq doesn't get upped so add 1 when
210 -- reporting line
211 errbuf := 'Input line (line nr = '
212 || l_batch_seq
213 + 1
214 || ') too large for buffer (='
215 || c_max_linesize
216 || ').';
217 EXIT;
218 WHEN NO_DATA_FOUND THEN
219 EXIT;
220 END;
221 l_get_indvnr := substr(l_line_read,1,4);
222 if l_batch_seq = 1 AND l_get_indvnr <> '0607' then
223 RAISE e_file_format_err;
224 end if;
225 if l_get_indvnr ='0607' and l_batch_seq <> 1 then
226 RAISE e_file_format_err;
227 end if;
228
229
230 l_flag := 'N';
231 --tax_rec_count = 0;
232 if l_get_indvnr = '0607' then
233 OPEN csr_get_legal_employer(p_business_group_id);
234 IF csr_get_legal_employer%NOTFOUND THEN
235 hr_utility.set_location ( ' The Employee Does not have a primary Assignment ' , 60);
236 RAISE e_legal_employer_err;
237 END IF;
238 LOOP
239 fetch csr_get_legal_employer into rg_csr_get_legal_employer;
240 l_le_id := rg_csr_get_legal_employer.org_information1;
241 l_se_num := rg_csr_get_legal_employer.org_information5;
242 -- fnd_file.put_line (fnd_file.LOG,'l_le_id :'||l_le_id||' l_se_num : '||l_se_num);
243 EXIT WHEN csr_get_legal_employer%NOTFOUND;
244 IF l_le_id = substr(l_line_read,75,8) OR l_se_num = substr(l_line_read,75,8) then -- included validation for SE num
245 l_flag := 'Y';
246 END IF;
247 END LOOP;
248 CLOSE csr_get_legal_employer;
249 if l_flag = 'N' then
250 RAISE e_legal_employer_err;
251 end if;
252 end if;
253
254 if l_get_indvnr = '0619' then
255 l_end_rec_flag := 'Y';
256 if to_number(substr(l_line_read,112,5)) <> l_batch_seq then
257 RAISE e_file_format_err;
258 end if;
259 begin
260 UTL_FILE.get_line (l_file_type, l_line_read);
261 exception
262 when NO_DATA_FOUND then
263 NULL;
264
265
266 when others then
267 -- Set retcode to 1, indicating a WARNING to the ConcMgr
268 retcode := c_error;
269 -- Set the application error
270 hr_utility.set_message (801, 'PAY_377021_DK_INVALID_FILE_FMT');
271 hr_utility.set_location (l_proc, 120);
272 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
273 end;
274
275 end if;
276 END LOOP file_format_check;
277 -- Commit the outstanding records
278
279 --COMMIT;
280
281
282 UTL_FILE.fclose (l_file_type);
283
284 if l_end_rec_flag = 'N' then
285 fnd_file.put_line (fnd_file.LOG,'end record error');
286 raise e_file_format_err;
287 end if;
288
289 -- END OF FILE FORMAT CHECK
290
291 -- End of Bug fixes 4216001 and 4216029
292
293
294 INSERT INTO fnd_sessions(SESSION_ID, EFFECTIVE_DATE)
295 VALUES(userenv('SESSIONID'),fnd_date.canonical_to_date(p_effective_date)) ;
296
297 hr_utility.set_location('p_file_name '||p_file_name,1);
298 hr_utility.set_location('p_effective_date '||p_effective_date,1);
299 hr_utility.set_location('p_business_group_id '||p_business_group_id,1 );
300 hr_utility.set_location('p_batch_name '||p_batch_name,1);
301 hr_utility.set_location ( 'Entering:' || l_proc, 10);
302
303 OPEN csr_leg (p_business_group_id);
304 FETCH csr_leg INTO l_legislation_code, l_bg_name;
305 CLOSE csr_leg;
306 hr_utility.set_location ( 'Legislation = ' || l_legislation_code, 20);
307 l_filename := p_file_name;
308 fnd_profile.get (c_data_exchange_dir, l_location);
309 hr_utility.set_location ( 'Directory = ' || l_location, 30);
310
311 IF l_location IS NULL THEN
312 -- error : I/O directory not defined
313 RAISE e_fatal_error;
314 END IF;
315 -- Open flat file
316
317 l_file_type :=
318 UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
319 -- Create the Batch header
320
321 l_batch_id := hr_pump_utils.create_batch_header
322 (p_batch_name => p_batch_name,
323 p_business_group_name => l_bg_name,
324 p_reference => substr(p_reference||'('||fnd_date.date_to_displaydt(SYSDATE)||')',1,30));
325
326 hr_utility.set_location ( ' Batch ID = ' || l_batch_id, 40);
327 -- Loop over the file, reading in each line. GET_LINE will
328 -- raise NO_DATA_FOUND when it is done, so we use that as the
329 -- exit condition for the loop
330
331 l_batch_seq := 0;
332 <<read_lines_in_file>>
333 LOOP
334 BEGIN
335 UTL_FILE.get_line (l_file_type, l_line_read);
336 l_batch_seq := l_batch_seq + 1;
337 EXCEPTION
338 WHEN VALUE_ERROR THEN
339 IF UTL_FILE.is_open (l_file_type) THEN
340 UTL_FILE.fclose (l_file_type);
341 END IF;
342 hr_utility.set_location (l_proc, 300);
343 retcode := c_error;
344 -- The error will mean batch_seq doesn't get upped so add 1 when
345 -- reporting line
346 errbuf := 'Input line (line nr = '
347 || l_batch_seq
348 + 1
349 || ') too large for buffer (='
350 || c_max_linesize
351 || ').';
352 EXIT;
353 WHEN NO_DATA_FOUND THEN
354 EXIT;
355 END;
356
357 hr_utility.set_location ( ' line read: '
358 || SUBSTR (l_line_read, 1, 44),50);
359
360 BEGIN
361 -- setting defualt value for element link found flag
362 l_element_link_found := 'FOUND';
363 -- fnd_file.put_line (fnd_file.LOG,'Line - before parsing it : '||l_line_read);
364 if substr(l_line_read,1,4) = '0608' then -- tax record
365 read_record
366 (
367 p_line => l_line_read
368 ,p_entry_value1 => l_entry_value1
369 ,p_entry_value2 => l_entry_value2
370 ,p_entry_value3 => l_entry_value3
371 ,p_entry_value4 => l_entry_value4
372 ,p_entry_value5 => l_entry_value5
373 ,p_entry_value6 => l_entry_value6
374 ,p_entry_value7 => l_entry_value7
375 ,p_entry_value8 => l_entry_value8
376 ,p_entry_value9 => l_entry_value9
377 ,p_entry_value10 => l_entry_value10
378 ,p_entry_value11 => l_entry_value11
379 ,p_entry_value12 => l_entry_value12
380 ,p_entry_value13 => l_entry_value13
381 ,p_entry_value14 => l_entry_value14
382 ,p_entry_value15 => l_entry_value15
383 ,p_return_value1 => l_cpr
384 ,p_return_value2 => l_employer_org_no
385 ,p_return_value3 => l_effective_date);
386
387 hr_utility.set_location ( ' CPR Number = ' || l_cpr, 60);
388 hr_utility.set_location ( ' Employer Organization Number = ' || l_employer_org_no, 60);
389
390 --fnd_file.put_line (fnd_file.LOG,'Read the record 0608');
391
392 if l_entry_value2 NOT IN ('H','B','F','NTC') then
393 raise e_invalid_tax_card_type;
394 end if;
395
396 if l_entry_value2 IN ('H','B') then
397 if fnd_number.CANONICAL_TO_NUMBER(l_entry_value3) <=0 then
398 raise e_tax_percentage_err;
399 end if;
400 end if;
401
402 if l_entry_value2 = 'F' then
403 if fnd_number.CANONICAL_TO_NUMBER(l_entry_value3) < 0 then
404 raise e_tax_percentage_err;
405 end if;
406 end if;
407
408 -- fnd_file.put_line (fnd_file.LOG,'CPR number : '||l_cpr);
409 -- fnd_file.put_line (fnd_file.LOG,'p_effective_date : '||p_effective_date);
410
411 OPEN csr_get_prim_assg
412 ( p_business_group_id => p_business_group_id
413 ,p_cpr => l_cpr
414 ,p_date => to_date(l_effective_date,'yyyymmdd')) ;
415 FETCH csr_get_prim_assg INTO l_assignment_id;
416 IF csr_get_prim_assg%NOTFOUND THEN
417 hr_utility.set_location ( ' The Employee Does not have a primary Assignment ' , 60);
418 RAISE e_prim_assg_error;
419 END IF;
420 CLOSE csr_get_prim_assg;
421
422 -- fnd_file.put_line(fnd_file.LOG,'Debug 1');
423
424 /* Initialize values for input value names of the seeded element */
425 l_input_value_name1 := 'Method of Receipt';
426 l_input_value_name2 := 'Tax Card Type';
427 l_input_value_name3 := 'Tax Percentage';
428 l_input_value_name4 := 'Tax Free Threshold';
429 l_input_value_name5 := 'Monthly Tax Deduction';
430 l_input_value_name6 := 'Bi Weekly Tax Deduction';
431 l_input_value_name7 := 'Weekly Tax Deduction';
432 l_input_value_name8 := 'Daily Tax Deduction';
433 l_input_value_name9 := 'Tax Card Requisition Status';
434 l_input_value_name10 := NULL;
435 l_input_value_name11 := NULL;
436 l_input_value_name12 := NULL;
437 l_input_value_name13 := NULL;
438 l_input_value_name14 := NULL;
439 l_input_value_name15 := NULL;
440
441 /* Fix for Flugger - starts */
442
443 select tl.name
444 into l_input_value_name1
445 from pay_input_values_f_tl tl, pay_input_values_f t
446 where t.input_value_id = tl.input_value_id
447 and t.legislation_code ='DK'
448 and t.name = l_input_value_name1
449 and tl.language ='US';
450
451 --fnd_file.put_line(fnd_file.LOG,'Debug14_1');
452
453 select tl.name
454 into l_input_value_name2
455 from pay_input_values_f_tl tl, pay_input_values_f t
456 where t.input_value_id = tl.input_value_id
457 and t.legislation_code ='DK'
458 and t.name = l_input_value_name2
459 and tl.language ='US';
460
461 --fnd_file.put_line(fnd_file.LOG,'Debug14_2');
462
463 select tl.name
464 into l_input_value_name3
465 from pay_input_values_f_tl tl, pay_input_values_f t
466 where t.input_value_id = tl.input_value_id
467 and t.legislation_code ='DK'
468 and t.name = l_input_value_name3
469 and tl.language ='US';
470
471 --fnd_file.put_line(fnd_file.LOG,'Debug14_3');
472 select tl.name
473 into l_input_value_name4
474 from pay_input_values_f_tl tl, pay_input_values_f t
475 where t.input_value_id = tl.input_value_id
476 and t.legislation_code ='DK'
477 and t.name = l_input_value_name4
478 and tl.language ='US';
479
480 --fnd_file.put_line(fnd_file.LOG,'Debug14_4');
481 select tl.name
482 into l_input_value_name5
483 from pay_input_values_f_tl tl, pay_input_values_f t
484 where t.input_value_id = tl.input_value_id
485 and t.legislation_code ='DK'
486 and t.name = l_input_value_name5
487 and tl.language ='US';
488
489 --fnd_file.put_line(fnd_file.LOG,'Debug14_5');
490 select tl.name
491 into l_input_value_name6
492 from pay_input_values_f_tl tl, pay_input_values_f t
493 where t.input_value_id = tl.input_value_id
494 and t.legislation_code ='DK'
495 and t.name = l_input_value_name6
496 and tl.language ='US';
497
498 --fnd_file.put_line(fnd_file.LOG,'Debug14_6');
499 select tl.name
500 into l_input_value_name7
501 from pay_input_values_f_tl tl, pay_input_values_f t
502 where t.input_value_id = tl.input_value_id
503 and t.legislation_code ='DK'
504 and t.name = l_input_value_name7
505 and tl.language ='US';
506
507 select tl.name
508 into l_input_value_name8
509 from pay_input_values_f_tl tl, pay_input_values_f t
510 where t.input_value_id = tl.input_value_id
511 and t.legislation_code ='DK'
512 and t.name = l_input_value_name8
513 and tl.language ='US';
514 /* Fix for Flugger - ends*/
515
516 -- fnd_file.put_line(fnd_file.LOG,' l_assignment_id : '||l_assignment_id);
517
518 --fnd_file.put_line(fnd_file.LOG,'bfore getting element details...');
519
520 OPEN csr_get_element_details(l_assignment_id, to_date(l_effective_date,'yyyymmdd'));
521 FETCH csr_get_element_details
522 INTO l_element_entry_id,l_element_name,l_effective_start_date,l_effective_end_date;
523 IF csr_get_element_details%NOTFOUND THEN
524 l_element_link_found := 'NOT_FOUND';
525 END IF;
526 CLOSE csr_get_element_details;
527
528 --Added for Flugger fix
529 select ELEMENTTL.element_name
530 into l_element_name_tl
531 from PAY_ELEMENT_TYPES_F_TL ELEMENTTL, PAY_ELEMENT_TYPES_F ELEMENT
532 where ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID
533 and ELEMENT.LEGISLATION_CODE ='DK'
534 and ELEMENT.ELEMENT_NAME = 'Tax Card'
535 AND ELEMENTTL.LANGUAGE = USERENV('LANG');
536
537 select ELEMENTTL.element_name
538 into l_element_name
539 from PAY_ELEMENT_TYPES_F_TL ELEMENTTL, PAY_ELEMENT_TYPES_F ELEMENT
540 where ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID
541 and ELEMENT.LEGISLATION_CODE ='DK'
542 and ELEMENT.ELEMENT_NAME = 'Tax Card'
543 AND ELEMENTTL.LANGUAGE = 'US';
544
545 --fnd_file.put_line(fnd_file.LOG,'l_effective_date : '||l_effective_date);
546 --fnd_file.put_line(fnd_file.LOG,'Before calling get_element_link_id');
547 -- Modified for Flugger fix
548 l_element_link_id := get_element_link_id(l_assignment_id, p_business_group_id, to_date(l_effective_date,'yyyymmdd'), l_element_name_tl);
549 hr_utility.set_location ( ' The Element Staus = '||l_element_link_found , 70);
550 hr_utility.set_location ( ' Element Entry ID = ' || l_element_entry_id, 70);
551 hr_utility.set_location ( ' Element Name = ' || l_element_name, 70);
552 hr_utility.set_location ( ' Element Entry Start Date = '||l_effective_start_date, 70);
553 hr_utility.set_location ( ' Element Entry End Date = '||l_effective_end_date, 70);
554 hr_utility.set_location ( ' Element Link ID = ' || l_element_link_id, 70);
555
556 -- Add User Keys for Data Pump
557 l_ee_user_key:=NULL;
558 L_ASSIGNMENT_USER_KEY:=NULL;
559 L_ELEMENT_LINK_USER_KEY:=NULL;
560
561 -- fnd_file.put_line(fnd_file.LOG,'l_element_link_id : '||l_element_link_id);
562 IF l_element_link_found = 'FOUND' THEN
563 l_ee_user_key := to_char(l_assignment_id )||to_char(L_ELEMENT_LINK_ID) ||' : ELEMENT ENTRY USER KEY';
564 OPEN csr_get_user_key(l_ee_user_key);
565 FETCH csr_get_user_key INTO l_user_key_value,l_unique_key_id;
566 -- Add user key only if it does not exist previously
567
568 IF csr_get_user_key%NOTFOUND THEN
569 hr_pump_utils.add_user_key(l_ee_user_key,l_element_entry_id);
570 hr_utility.set_location ( ' User Key added ' , 190);
571 ELSE
572 hr_pump_utils.modify_user_key(l_ee_user_key,l_ee_user_key,l_element_entry_id);
573 hr_utility.set_location ( ' User Key modified ' , 190.1);
574 END IF;
575
576 CLOSE csr_get_user_key;
577 hr_utility.set_location ( ' User Key added ' , 80);
578 hr_utility.set_location ( ' l_effective_start_date:' || to_char(l_effective_start_date), 90);
579 hr_utility.set_location ( ' l_effective_end_date:' || to_char(l_effective_end_date), 90);
580 hr_utility.set_location ( ' p_effective_date:' || to_char(fnd_date.canonical_to_date(p_effective_date)), 90);
581 hr_utility.set_location ( ' c_end_of_time:' || to_char(c_end_of_time), 90);
582
583 -- Define Datetrack Updation Mode
584 IF(l_effective_start_date = to_date(l_effective_date,'yyyymmdd')) THEN
585 l_datetrack_update_mode := 'CORRECTION';
586 ELSIF(l_effective_end_date <> c_end_of_time) THEN
587 l_datetrack_update_mode := 'UPDATE_OVERRIDE';
588 ELSE
589 l_datetrack_update_mode := 'UPDATE';
590 END IF;
591
592 --fnd_file.put_line(fnd_file.LOG,'l_datetrack_update_mode : '||l_datetrack_update_mode);
593
594 hr_utility.set_location ( ' Datetrack Update Mode:' || l_datetrack_update_mode, 90);
595 -- Data Pump procedure called to create batch lines to update element entries
596 -- Bug fix 4215700
597 hrdpp_update_element_entry.insert_batch_lines
598 (p_batch_id => l_batch_id
599 ,p_data_pump_business_grp_name => l_bg_name
600 ,P_DATETRACK_UPDATE_MODE => l_datetrack_update_mode
601 ,P_EFFECTIVE_DATE => fnd_date.canonical_to_date(l_effective_date)
602 ,P_ENTRY_VALUE1 => l_entry_value1
603 ,P_ENTRY_VALUE2 => l_entry_value2
604 ,P_ENTRY_VALUE3 => l_entry_value3
605 ,P_ENTRY_VALUE4 => l_entry_value4
606 ,P_ENTRY_VALUE5 => l_entry_value5
607 ,P_ENTRY_VALUE6 => l_entry_value6
608 ,P_ENTRY_VALUE7 => l_entry_value7
609 ,P_ENTRY_VALUE8 => l_entry_value8
610 ,P_ENTRY_VALUE9 => l_entry_value9
611 ,P_ENTRY_VALUE10 => NULL
612 ,P_ENTRY_VALUE11 => NULL
613 ,P_ENTRY_VALUE12 => NULL
614 ,P_ENTRY_VALUE13 => NULL
615 ,P_ENTRY_VALUE14 => NULL
616 ,P_ENTRY_VALUE15 => NULL
617 ,P_ELEMENT_ENTRY_USER_KEY => l_ee_user_key
618 ,P_ELEMENT_NAME => l_element_name
619 ,P_LANGUAGE_CODE =>'US'
620 ,P_INPUT_VALUE_NAME1 =>l_input_value_name1
621 ,P_INPUT_VALUE_NAME2 =>l_input_value_name2
622 ,P_INPUT_VALUE_NAME3 =>l_input_value_name3
623 ,P_INPUT_VALUE_NAME4 =>l_input_value_name4
624 ,P_INPUT_VALUE_NAME5 =>l_input_value_name5
625 ,P_INPUT_VALUE_NAME6 =>l_input_value_name6
626 ,P_INPUT_VALUE_NAME7 =>l_input_value_name7
627 ,P_INPUT_VALUE_NAME8 =>l_input_value_name8
628 ,P_INPUT_VALUE_NAME9 =>l_input_value_name9
629 ,P_INPUT_VALUE_NAME10 =>l_input_value_name10
630 ,P_INPUT_VALUE_NAME11 =>l_input_value_name11
631 ,P_INPUT_VALUE_NAME12 =>l_input_value_name12
632 ,P_INPUT_VALUE_NAME13 =>l_input_value_name13
633 ,P_INPUT_VALUE_NAME14 =>l_input_value_name14
634 ,P_INPUT_VALUE_NAME15 =>l_input_value_name15);
635
636 -- fnd_file.put_line (fnd_file.LOG,'After hrdpp_update_element_entry.insert_batch_lines 1');
637 -- End Bug fix 4215700
638 hr_utility.set_location ( ' Batch Lines created for Element Updation: ' , 100);
639 ELSE
640
641 -- fnd_file.put_line(fnd_file.LOG,'debug else1 ');
642
643 l_ee_user_key :=to_char(l_assignment_id )||to_char(L_ELEMENT_LINK_ID) ||' : ELEMENT ENTRY USER KEY';
644 L_ASSIGNMENT_USER_KEY :=to_char(l_assignment_id )||' : ASG USER KEY';
645 L_ELEMENT_LINK_USER_KEY :=to_char(L_ELEMENT_LINK_ID) ||' : ELEM LINK USER KEY';
646 /* deletion code for user key*/
647 OPEN csr_get_user_key(l_ee_user_key);
648 FETCH csr_get_user_key INTO l_user_key_value,l_unique_key_id;
649 --DELETE the key
650 IF csr_get_user_key%FOUND THEN
651 begin
652 DELETE FROM HR_PUMP_BATCH_LINE_USER_KEYS WHERE unique_key_id =l_unique_key_id;
653 end;
654 END IF;
655 CLOSE csr_get_user_key;
656
657 --fnd_file.put_line(fnd_file.LOG,'debug else2 ');
658 OPEN csr_get_user_key(L_ASSIGNMENT_USER_KEY);
659 FETCH csr_get_user_key INTO l_user_key_value,l_unique_key_id;
660 -- Add user key only if it does not exist previously
661 IF csr_get_user_key%NOTFOUND THEN
662 hr_pump_utils.add_user_key(L_ASSIGNMENT_USER_KEY,l_assignment_id);
663 END IF;
664 CLOSE csr_get_user_key;
665
666 --fnd_file.put_line(fnd_file.LOG,'debug else3 ');
667 OPEN csr_get_user_key(L_ELEMENT_LINK_USER_KEY);
668 FETCH csr_get_user_key INTO l_user_key_value,l_unique_key_id;
669 -- Add user key only if it does not exist previously
670 IF csr_get_user_key%NOTFOUND THEN
671 hr_pump_utils.add_user_key(L_ELEMENT_LINK_USER_KEY,L_ELEMENT_LINK_ID);
672 END IF;
673 CLOSE csr_get_user_key;
674
675 --fnd_file.put_line(fnd_file.LOG,'debug else4 ');
676 -- Bug fix 4215700
677 hrdpp_create_element_entry.insert_batch_lines
678 (p_batch_id => l_batch_id
679 ,p_data_pump_business_grp_name => l_bg_name
680 ,P_EFFECTIVE_DATE => fnd_date.canonical_to_date(l_effective_date)
681 ,P_ENTRY_TYPE => 'E'
682 ,P_CREATOR_TYPE => 'F'
683 ,P_ENTRY_VALUE1 => l_entry_value1
684 ,P_ENTRY_VALUE2 => l_entry_value2
685 ,P_ENTRY_VALUE3 => l_entry_value3
686 ,P_ENTRY_VALUE4 => l_entry_value4
687 ,P_ENTRY_VALUE5 => l_entry_value5
688 ,P_ENTRY_VALUE6 => l_entry_value6
689 ,P_ENTRY_VALUE7 => l_entry_value7
690 ,P_ENTRY_VALUE8 => l_entry_value8
691 ,P_ENTRY_VALUE9 => l_entry_value9
692 ,P_ENTRY_VALUE10 => NULL
693 ,P_ENTRY_VALUE11 => NULL
694 ,P_ENTRY_VALUE12 => NULL
695 ,P_ENTRY_VALUE13 => NULL
696 ,P_ENTRY_VALUE14 => NULL
697 ,P_ENTRY_VALUE15 => NULL
698 ,P_ELEMENT_ENTRY_USER_KEY => l_ee_user_key
699 ,P_ASSIGNMENT_USER_KEY => L_ASSIGNMENT_USER_KEY
700 ,P_ELEMENT_LINK_USER_KEY => L_ELEMENT_LINK_USER_KEY
701 ,P_ELEMENT_NAME => l_element_name
702 ,P_LANGUAGE_CODE =>'US'
703 ,P_INPUT_VALUE_NAME1 =>l_input_value_name1
704 ,P_INPUT_VALUE_NAME2 =>l_input_value_name2
705 ,P_INPUT_VALUE_NAME3 =>l_input_value_name3
706 ,P_INPUT_VALUE_NAME4 =>l_input_value_name4
707 ,P_INPUT_VALUE_NAME5 =>l_input_value_name5
708 ,P_INPUT_VALUE_NAME6 =>l_input_value_name6
709 ,P_INPUT_VALUE_NAME7 =>l_input_value_name7
710 ,P_INPUT_VALUE_NAME8 =>l_input_value_name8
711 ,P_INPUT_VALUE_NAME9 =>l_input_value_name9
712 ,P_INPUT_VALUE_NAME10 =>l_input_value_name10
713 ,P_INPUT_VALUE_NAME11 =>l_input_value_name11
714 ,P_INPUT_VALUE_NAME12 =>l_input_value_name12
715 ,P_INPUT_VALUE_NAME13 =>l_input_value_name13
716 ,P_INPUT_VALUE_NAME14 =>l_input_value_name14
717 ,P_INPUT_VALUE_NAME15 =>l_input_value_name15);
718 -- End Bug fix 4215700
719 hr_utility.set_location ( ' Batch Lines created for Element Creation: ' , 100);
720
721 -- fnd_file.put_line (fnd_file.LOG,'After hrdpp_update_element_entry.insert_batch_lines 2');
722 END IF;
723 hr_utility.set_location ( ' Batch Lines created ' , 100);
724 -- commit the records uppon reaching the commit point
725 IF MOD (l_batch_seq, c_commit_point) = 0 THEN
726 COMMIT;
727 NULL;
728 END IF;
729 elsif substr(l_line_read,1,4) = '0607' OR substr(l_line_read,1,4) = '0619' then -- tax record
730 hr_utility.set_location ( ' header or footer ' , 100);
731 null;
732 else
733 raise e_wrong_indvnr;
734 end if;
735
736 EXCEPTION
737 WHEN e_wrong_indvnr THEN
738 -- Wrong INDVNR
739 -- Set retcode to 1, indicating a WARNING to the ConcMgr
740 retcode := c_warning;
741 -- Set the application error
742 hr_utility.set_message (801, 'PAY_377015_DK_WRONG_INDVNR');
743 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
744 hr_utility.set_message_token (801, 'LINE', l_line_read);
745 hr_utility.set_location (l_proc, 110);
746 -- Write the message to log file, do not raise an application error but continue
747 -- (with next line)
748 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
749
750 WHEN e_prim_assg_error THEN
751 -- No primary assignment found for given CPR Number and CVR Number
752 -- Close the cursor
753 IF csr_get_prim_assg%ISOPEN THEN
754 CLOSE csr_get_prim_assg;
755 END IF;
756 -- Set retcode to 1, indicating a WARNING to the ConcMgr
757 retcode := c_warning;
758 -- Set the application error
759 hr_utility.set_message (801, 'PAY_377016_DK_ASSG_NOT_FOUND');
760 hr_utility.set_message_token (801, 'LINE_N0', l_batch_seq);
761 hr_utility.set_message_token (801, 'LINE', l_line_read);
762 hr_utility.set_location (l_proc, 120);
763 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
764
765 WHEN e_record_length_err THEN
766 --Record is too long
767 -- Set retcode to 1, indicating a WARNING to the ConcMgr
768 retcode := c_warning;
769 -- Set the application error
770 hr_utility.set_message (801, 'PAY_377017_DK_RECORD_LEN_ERR');
771 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
772 hr_utility.set_message_token (801, 'LINE', l_line_read);
773 hr_utility.set_message_token (801, 'RECLEN', l_record_length);
774 hr_utility.set_location (l_proc, 150);
775 -- Write the message to log file, do not raise an application error but continue
776 -- (with next line)
777 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
778
779 WHEN e_no_tax_link THEN
780 -- Wrong CSR routine
781 -- Set retcode to 1, indicating a WARNING to the ConcMgr
782 retcode := c_warning;
783 -- Set the application error
784 hr_utility.set_message (801, 'PAY_377019_DK_TAX_CARD_LINK');
785 hr_utility.set_message_token (801, 'CPRNO', l_cpr);
786 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
787 hr_utility.set_message_token (801, 'LINE', l_line_read);
788 hr_utility.set_location (l_proc, 320);
789 -- Write the message to log file, do not raise an application error but continue
790 -- (with next line)
791 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
792
793 -- Bug fixes 4216001 and 4216029
794 WHEN e_invalid_tax_card_type THEN
795 -- Wrong CSR routine
796 -- Set retcode to 1, indicating a WARNING to the ConcMgr
797 retcode := c_warning;
798 -- Set the application error
799 hr_utility.set_message (801, 'PAY_377022_DK_TAX_CARD_TYPE_E');
800 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
801 hr_utility.set_message_token (801, 'LINE', l_line_read);
802 hr_utility.set_location (l_proc, 320);
803 -- Write the message to log file, do not raise an application error but continue
804 -- (with next line)
805 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
806
807 WHEN e_tax_percentage_err THEN
808 -- Wrong CSR routine
809 -- Set retcode to 1, indicating a WARNING to the ConcMgr
810 retcode := c_warning;
811 -- Set the application error
812 hr_utility.set_message (801, 'PAY_377023_DK_TAX_PERCENTAGE_E');
813 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
814 hr_utility.set_message_token (801, 'LINE', l_line_read);
815 hr_utility.set_location (l_proc, 320);
816 -- Write the message to log file, do not raise an application error but continue
817 -- (with next line)
818 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
819
820
821 WHEN others THEN
822 -- Set retcode to 1, indicating a WARNING to the ConcMgr
823 retcode := c_warning;
824 -- Set the application error
825 fnd_file.put_line (fnd_file.LOG, SQLERRM);
826 hr_utility.set_message (801, 'PAY_377025_DK_INVALID_LINE');
827 hr_utility.set_message_token (801, 'LINE_N0', l_batch_seq);
828 hr_utility.set_message_token (801, 'LINE', l_line_read);
829 hr_utility.set_location (l_proc, 120);
830 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
831
832 -- end of Bug fixes 4216001 and 4216029
833
834 END;
835 END LOOP read_lines_in_file;
836 -- Commit the outstanding records
837
838 COMMIT;
839 UTL_FILE.fclose (l_file_type);
840 hr_utility.set_location ( 'Leaving:'|| l_proc, 200);
841 -- Most of these exceptions are not translated as they should not happen normally
842 -- If they do happen, something is seriously wrong and SysAdmin interference will be necessary.
843
844 EXCEPTION
845 WHEN e_fatal_error
846 -- No directory specified
847 THEN
848 -- Close the file in case of error
849 IF UTL_FILE.is_open (l_file_type)
850 THEN
851 UTL_FILE.fclose (l_file_type);
852 END IF;
853 hr_utility.set_location (l_proc, 210);
854 -- Set retcode to 2, indicating an ERROR to the ConcMgr
855 retcode := c_error;
856 -- Set the application error
857 hr_utility.set_message (801, 'PAY_377020_DK_DATA_EXC_DIR_MIS');
858 -- Return the message to the ConcMgr (This msg will appear in the log file)
859 errbuf := hr_utility.get_message;
860
861 WHEN UTL_FILE.invalid_operation
862 -- File could not be opened as requested, perhaps because of operating system permissions
863 -- Also raised when attempting a write operation on a file opened for read, or a read operation
864 -- on a file opened for write.
865 THEN
866 IF UTL_FILE.is_open (l_file_type)
867 THEN
868 UTL_FILE.fclose (l_file_type);
869 END IF;
870 hr_utility.set_location (l_proc, 220);
871 retcode := c_error;
872 errbuf := 'Reading File ('||l_location ||' -> '
873 || l_filename
874 || ') - Invalid Operation.';
875
876 WHEN UTL_FILE.internal_error
877 -- Unspecified internal error
878 THEN
879 IF UTL_FILE.is_open (l_file_type)
880 THEN
881 UTL_FILE.fclose (l_file_type);
882 END IF;
883 hr_utility.set_location (l_proc, 230);
884 retcode := c_error;
885 errbuf := 'Reading File ('
886 || l_location
887 || ' -> '
888 || l_filename
889 || ') - Internal Error.';
890
891 WHEN UTL_FILE.invalid_mode
892 -- Invalid string specified for file mode
893 THEN
894 IF UTL_FILE.is_open (l_file_type)
895 THEN
896 UTL_FILE.fclose (l_file_type);
897 END IF;
898 hr_utility.set_location (l_proc, 240);
899 retcode := c_error;
900 errbuf := 'Reading File ('
901 || l_location
902 || ' -> '
903 || l_filename
904 || ') - Invalid Mode.';
905
906 WHEN UTL_FILE.invalid_path
907 -- Directory or filename is invalid or not accessible
908 THEN
909 IF UTL_FILE.is_open (l_file_type)
910 THEN
911 UTL_FILE.fclose (l_file_type);
912 END IF;
913 retcode := c_error;
914 errbuf := 'Reading File ('
915 || l_location
916 || ' -> '
917 || l_filename
918 || ') - Invalid Path or Filename.';
919 hr_utility.set_location (l_proc, 250);
920
921 WHEN UTL_FILE.invalid_filehandle
922 -- File type does not specify an open file
923 THEN
924 IF UTL_FILE.is_open (l_file_type)
925 THEN
926 UTL_FILE.fclose (l_file_type);
927 END IF;
928 hr_utility.set_location (l_proc, 260);
929 retcode := c_error;
930 errbuf := 'Reading File ('
931 || l_location
932 || ' -> '
933 || l_filename
934 || ') - Invalid File Type.';
935
936 WHEN UTL_FILE.read_error
937 -- Operating system error occurred during a read operation
938 THEN
939 IF UTL_FILE.is_open (l_file_type)
940 THEN
941 UTL_FILE.fclose (l_file_type);
942 END IF;
943 hr_utility.set_location (l_proc, 270);
944 retcode := c_error;
945 errbuf := 'Reading File ('
946 || l_location
947 || ' -> '
948 || l_filename
949 || ') - Read Error.';
950
951 WHEN e_legal_employer_err THEN
952 IF csr_get_legal_employer%ISOPEN THEN
953 CLOSE csr_get_legal_employer;
954 END IF;
955 -- Set retcode to 1, indicating a WARNING to the ConcMgr
956 retcode := c_error;
957 -- Set the application error
958 hr_utility.set_message (801, 'PAY_377024_DK_LEGAL_EMPLOYER_E');
959 hr_utility.set_location (l_proc, 120);
960 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
961
962 WHEN e_file_format_err THEN
963 -- Set retcode to 1, indicating a WARNING to the ConcMgr
964 retcode := c_error;
965 -- Set the application error
966 hr_utility.set_message (801, 'PAY_377021_DK_INVALID_FILE_FMT');
967 hr_utility.set_location (l_proc, 120);
968 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
969 END upload;
970
971
972 PROCEDURE read_record
973 (p_line IN VARCHAR2
974 ,p_entry_value1 OUT NOCOPY VARCHAR2
975 ,p_entry_value2 OUT NOCOPY VARCHAR2
976 ,p_entry_value3 OUT NOCOPY VARCHAR2
977 ,p_entry_value4 OUT NOCOPY VARCHAR2
978 ,p_entry_value5 OUT NOCOPY VARCHAR2
979 ,p_entry_value6 OUT NOCOPY VARCHAR2
980 ,p_entry_value7 OUT NOCOPY VARCHAR2
981 ,p_entry_value8 OUT NOCOPY VARCHAR2
982 ,p_entry_value9 OUT NOCOPY VARCHAR2
983 ,p_entry_value10 OUT NOCOPY VARCHAR2
984 ,p_entry_value11 OUT NOCOPY VARCHAR2
985 ,p_entry_value12 OUT NOCOPY VARCHAR2
986 ,p_entry_value13 OUT NOCOPY VARCHAR2
987 ,p_entry_value14 OUT NOCOPY VARCHAR2
988 ,p_entry_value15 OUT NOCOPY VARCHAR2
989 ,p_return_value1 OUT NOCOPY VARCHAR2
990 ,p_return_value2 OUT NOCOPY VARCHAR2
991 ,p_return_value3 OUT NOCOPY VARCHAR2
992 )
993 IS
994
995 --Variables to store the extra values read from the flat file
996 l_extra_value0 VARCHAR2(80);
997 l_extra_value1 VARCHAR2(80);
998 l_extra_value2 VARCHAR2(80);
999 l_extra_value3 VARCHAR2(80);
1000 l_extra_value4 VARCHAR2(80);
1001 l_extra_value5 VARCHAR2(80);
1002 l_record_length NUMBER :=4000;
1003 l_indvnr VARCHAR2(4);
1004 -- Procedure name
1005 l_proc CONSTANT VARCHAR2 (72) := g_package|| '.read_record';
1006 l_line VARCHAR2(200); --9543947
1007
1008 BEGIN
1009 hr_utility.set_location ( 'Entering:'|| l_proc, 10);
1010 /* p_entry_value1 Method Of Receipt ( Not from file)
1011 * p_return_value1 CPR Number (21-30) Appended to have a hiphen
1012 * p_return_value2 CVR Number
1013 * p_entry_value2 Tax Card Type
1014 * p_entry_value3 Income Tax Percentage
1015 * p_entry_value4 Tax Free Threshold
1016 * p_entry_value5 Monthly Tax Deduction
1017 * p_entry_value6 Bi Weekly Tax Deduction
1018 * p_entry_value7 Weekly Tax Deduction
1019 * p_entry_value8 Daily Tax Deduction
1020 */
1021 --Set record length
1022 l_record_length := 159;
1023 hr_utility.set_location ( ' Record length:'|| l_record_length, 20);
1024 l_indvnr := substr( p_line, 1,4);
1025 IF l_indvnr = '0608' THEN
1026 p_entry_value1 := 'ET';
1027 /* CVR Number of legal emp */
1028 p_return_value2 := substr( p_line ,15,8);
1029 /* Read CPR Number and modify acccording to value retrieved from database */
1030 p_return_value1 := substr( p_line ,5,10);
1031 p_return_value1 := to_char(substr(p_return_value1,1,6)||'-'||substr(p_return_value1,7));
1032 p_return_value3 := substr( p_line, 31,8); /* Tax card valid from */
1033 p_entry_value2 := substr( p_line ,47,1); /* Tax Card Type */
1034 -- Changing from number to type of the card
1035 /*
1036 0 - No tax card
1037 2 - Main Tax card (H card)
1038 3 - Free Tax card (F card)
1039 4 - Secondary Tax card (B card)
1040 */
1041
1042 IF(p_entry_value2='0') THEN
1043 p_entry_value2:='NTC';
1044
1045 ELSIF (p_entry_value2='2') THEN
1046 p_entry_value2:='H';
1047
1048 ELSIF (p_entry_value2='3') THEN
1049 p_entry_value2:='F';
1050
1051 ELSIF (p_entry_value2='4') THEN
1052 p_entry_value2:='B';
1053
1054 END IF;
1055
1056 p_entry_value3 := substr( p_line ,52,3); /* Income Tax Percentage */
1057 p_entry_value4 := substr( p_line ,55,9); /* Max amount for F tax card */
1058 p_entry_value5 := substr( p_line ,91,9); /* Monthly Tax Deduction */
1059 p_entry_value6 := substr( p_line ,82,9); /* Bi Weekly Tax Deduction */
1060 p_entry_value7 := substr( p_line ,73,9); /* Weekly Tax Deduction */
1061 p_entry_value8 := substr( p_line ,64,9); /* Daily Tax Deduction */
1062 --p_entry_value9 := substr( p_line ,150,7); /* Serial number for e-Tax card */
1063 p_entry_value9 := 'UPLOAD COMPLETE';
1064
1065 -- Considering the last two digits as decimal digits.
1066 -- p_entry_value3 := fnd_number.CANONICAL_TO_NUMBER(p_entry_value3)/100;
1067 /* Commented for bug fix 7684343
1068 p_entry_value4 := fnd_number.CANONICAL_TO_NUMBER(p_entry_value4)/100;
1069 p_entry_value5 := fnd_number.CANONICAL_TO_NUMBER(p_entry_value5)/100;
1070 p_entry_value6 := fnd_number.CANONICAL_TO_NUMBER(p_entry_value6)/100;
1071 p_entry_value7 := fnd_number.CANONICAL_TO_NUMBER(p_entry_value7)/100;
1072 p_entry_value8 := fnd_number.CANONICAL_TO_NUMBER(p_entry_value8)/100;*/
1073
1074 ELSIF l_indvnr = '0607' OR l_indvnr = '0619' THEN
1075 null;
1076 ELSE
1077 hr_utility.set_location ('Wrong Tax Record', 40);
1078 RAISE e_wrong_indvnr;
1079 END IF;
1080
1081 -- fnd_file.put_line (fnd_file.LOG,'Line read : '||p_line);
1082 -- fnd_file.put_line (fnd_file.LOG,'Line Length : '||length(p_line));
1083
1084 --9543947
1085 IF ASCII(substr( p_line ,160,1)) = '13' THEN
1086 l_line := substr(p_line,1,159);
1087 ELSE
1088 l_line := p_line;
1089 END IF;
1090 --9543947
1091
1092 -- Error in record if it is too long according to given format
1093 --IF (length(p_line) <> l_record_length) THEN --9543947
1094 IF (length(l_line) <> l_record_length) THEN --9543947
1095 hr_utility.set_location ( ' Record too long', 50);
1096 RAISE e_record_length_err;
1097 END IF;
1098 /*
1099 hr_utility.set_location ( ' Tax Municipality:'|| p_entry_value2, 65);
1100 hr_utility.set_location ( ' Tax Table Number:'|| p_entry_value3, 66);
1101 hr_utility.set_location ( ' Tax Table Type:'|| p_entry_value4, 67);
1102 hr_utility.set_location ( ' Tax Percentage:'|| p_entry_value5, 68);
1103 hr_utility.set_location ( ' Tax Card Type:'|| p_entry_value6, 69);
1104
1105
1106 fnd_file.put_line (fnd_file.LOG,'Tax card type : '||p_entry_value2);
1107 fnd_file.put_line (fnd_file.LOG,'Tax % : '||p_entry_value3);
1108 fnd_file.put_line (fnd_file.LOG,'tax limit : '||p_entry_value4);
1109 fnd_file.put_line (fnd_file.LOG,'monthly tax : '||p_entry_value5);
1110 fnd_file.put_line (fnd_file.LOG,'bi weekly tax : '||p_entry_value6);
1111 fnd_file.put_line (fnd_file.LOG,'weekly tax : '||p_entry_value7);
1112 fnd_file.put_line (fnd_file.LOG,'daily tax : '||p_entry_value8);
1113 fnd_file.put_line (fnd_file.LOG,'start date : '||p_return_value3);
1114 */
1115 hr_utility.set_location ( 'Leaving:'|| l_proc, 100);
1116 END read_record;
1117 /* Function to get the element link ID */
1118
1119
1120 FUNCTION get_element_link_id
1121 (
1122 p_assignment_id IN NUMBER
1123 ,p_business_group_id IN NUMBER
1124 ,p_date IN per_all_assignments_f.effective_start_date%TYPE
1125 ,p_element_name pay_element_types_f.ELEMENT_NAME%TYPE
1126 ) RETURN NUMBER
1127 IS
1128 l_element_link_id pay_element_links_f.ELEMENT_LINK_ID%TYPE;
1129 CURSOR csr_get_payroll_id IS
1130 SELECT payroll_id
1131 FROM per_all_assignments_f
1132 WHERE business_group_id = p_business_group_id
1133 AND assignment_id = p_assignment_id
1134 AND p_date
1135 BETWEEN effective_start_date AND effective_end_date ;
1136 Cursor csr_element_link_id
1137 (
1138 p_payroll_id IN NUMBER
1139 )
1140 IS
1141 SELECT element.element_link_id
1142 FROM pay_paywsmee_elements_lov element
1143 WHERE element.assignment_id = p_assignment_id
1144 AND element.element_name = p_element_name
1145 AND (element.business_group_id = p_business_group_id
1146 OR (element.business_group_id is null and element.legislation_code = 'DK'))
1147 AND ( element.multiple_entries_allowed_flag = 'Y'
1148 OR (element.normal_exists = 'N'
1149 OR (p_payroll_id is not null
1150 AND ( (element.additional_entry_allowed_flag = 'Y'
1151 AND element.additional_exists = 'N' )
1152 OR (element.overridden = 'N' and element.adjusted = 'N' ))))) ;
1153 BEGIN
1154 l_element_link_id := NULL;
1155 FOR pay_rec IN csr_get_payroll_id
1156 LOOP
1157 OPEN csr_element_link_id(pay_rec.payroll_id ) ;
1158 FETCH csr_element_link_id
1159 INTO l_element_link_id ;
1160 IF csr_element_link_id%NOTFOUND THEN
1161 RAISE e_no_tax_link;
1162 END IF;
1163 CLOSE csr_element_link_id;
1164 END LOOP ;
1165 RETURN l_element_link_id ;
1166 END get_element_link_id;
1167 END pay_dk_tc_dp_upload;