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