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