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