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