[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_ITREF_UPLOAD
Source
1 PACKAGE BODY PY_ZA_ITREF_UPLOAD AS
2 /* $Header: PYZAITREFU.pkb 120.3 2011/09/16 11:28:33 abdash ship $ */
3
4 /*
5
6 RESULT CODES
7 BN - Not found Matching Employee
8 CE - Error Occured while processing Employee ITREF.
9 DS - Not processed because of same ITREF.
10 EU - Updated Employee ITREF sucessfully
11 FC - Corrected Employee ITREF sucessfully
12 NA - No Action taken as IT REF Number not returned by SARS -- Bug 12991144
13 */
14
15 g_pkg_name VARCHAR2(40):='PY_ZA_ITREF_UPLOAD.';
16
17
18 TYPE csv_rec_defn IS RECORD
19 (
20 line_num VARCHAR2(200),
21 cert_num VARCHAR2(200),
22 surname_or_trade_name VARCHAR2(200),
23 first_two_names VARCHAR2(200),
24 initials VARCHAR2(200),
25 identity_num VARCHAR2(200),
26 passport_num VARCHAR2(200),
27 country_of_issue VARCHAR2(200),
28 date_of_birth VARCHAR2(200),
29 tax_ref_num VARCHAR2(200),
30 emp_num VARCHAR2(200),
31 status VARCHAR2(200)
32 );
33
34 PROCEDURE create_payroll_action AS
35 BEGIN
36
37 SELECT pay_payroll_actions_s.nextval
38 INTO g_payroll_action_id
39 FROM dual;
40
41 INSERT INTO pay_payroll_actions
42 (payroll_action_id
43 ,action_type
44 ,business_group_id
45 ,effective_date
46 ,date_earned
47 ,payroll_id
48 ,consolidation_set_id
49 ,time_period_id
50 ,action_population_status
51 ,action_status
52 ,object_version_number
53 ,report_type
54 ,report_qualifier
55 ,request_id)
56 VALUES
57 (g_payroll_action_id
58 ,'X'
59 ,P_BUSINESS_GROUP_ID
60 ,P_EFFECTIVE_DATE
61 ,NULL
62 ,NULL
63 ,NULL
64 ,NULL
65 ,'U'
66 ,'U'
67 ,1
68 ,'ZA_ITREF_UPLOAD'
69 ,'ZA'
70 ,FND_GLOBAL.CONC_REQUEST_ID);
71
72 END create_payroll_action;
73
74
75 PROCEDURE create_action_information( p_csv_rec in csv_rec_defn,
76 p_result in VARCHAR2,
77 p_error_msg in VARCHAR2 DEFAULT NULL,
78 p_old_tax_ref_num in VARCHAR2 DEFAULT NULL ) AS
79 l_ovn NUMBER;
80 l_action_id NUMBER;
81 l_status VARCHAR2(2000); -- Bug 12991144
82 BEGIN
83
84 SELECT pay_action_information_s.nextval
85 INTO l_action_id
86 FROM dual;
87
88 -- Bug 12991144
89
90 SELECT DECODE(upper(p_csv_rec.status), 'REGISTERED', 'R', 'EXISTING TAXPAYER', 'E','TAXPAYER TO CONTACT SARS', 'T', 'INSUFFICIENT INFORMATION', 'I', 'NOT VERIFIED', 'NV', 'NON-INDIVIDUAL', 'NI')
91 INTO l_status
92 FROM dual;
93
94 -- Bug 12991144
95
96 pay_action_information_api.create_action_information(
97 p_action_information_id => l_action_id,
98 p_object_version_number => l_ovn,
99 p_action_information_category => 'ZA_ITREF_UPLOAD',
100 p_action_context_id => g_payroll_action_id,
101 p_action_context_type => 'PA',
102 p_assignment_id => null,
103 p_effective_date => p_effective_date,
104 p_action_information1 => cp_le_name,
105 p_action_information2 => cp_paye_num,
106 p_action_information3 => p_csv_rec.line_num,
107 p_action_information4 => p_csv_rec.cert_num,
108 p_action_information5 => p_csv_rec.surname_or_trade_name,
109 p_action_information6 => p_csv_rec.first_two_names,
110 p_action_information7 => p_csv_rec.initials,
111 p_action_information8 => p_csv_rec.identity_num,
112 p_action_information9 => p_csv_rec.passport_num,
113 p_action_information10 => p_csv_rec.country_of_issue,
114 p_action_information11 => p_csv_rec.date_of_birth,
115 p_action_information12 => p_csv_rec.tax_ref_num,
116 p_action_information13 => p_csv_rec.emp_num,
117 -- p_action_information14 => p_csv_rec.status,
118 p_action_information14 => l_status, -- Bug 12991144
119 p_action_information15 => p_result,
120 p_action_information16 => p_error_msg,
121 p_action_information17 => p_old_tax_ref_num
122 );
123
124 END create_action_information;
125
126 --TODO Revisit the logic again
127 FUNCTION get_code_value(p_present_line in VARCHAR2
128 ,p_code in NUMBER) RETURN VARCHAR2 AS
129
130 l_retval varchar2(200):=null;
131 l_start_pos number;
132 l_end_pos number;
133 BEGIN
134
135 l_start_pos:= INSTR(p_present_line,p_code||',');
136 IF (l_start_pos <> 0) THEN
137 l_start_pos :=l_start_pos+5;
138 l_end_pos:= INSTR(p_present_line,',',l_start_pos);
139 l_retval:=substr(p_present_line,l_start_pos,(l_end_pos-l_start_pos));
140
141 -- Strip Quotes at start/end if exists
142 IF (substr(l_retval,1,1) ='"' AND substr(l_retval,-1,1) ='"') THEN
143 l_retval := substr(l_retval,2,(l_end_pos-l_start_pos)-2);
144 END IF;
145 END IF;
146
147 hr_utility.set_location('Code:'||p_code||',Value:'||l_retval,52);
148
149 return l_retval;
150
151 END get_code_value;
152
153
154 PROCEDURE convert_line_to_rec(p_present_line in VARCHAR2,
155 p_line_num in NUMBER,
156 p_csv_rec out NOCOPY csv_rec_defn) AS
157
158 BEGIN
159 p_csv_rec.line_num := p_line_num;
160 p_csv_rec.cert_num := get_code_value(p_present_line,3010);
161 p_csv_rec.surname_or_trade_name := get_code_value(p_present_line,3030);
162 p_csv_rec.first_two_names := get_code_value(p_present_line,3040);
163 p_csv_rec.initials := get_code_value(p_present_line,3050);
164 p_csv_rec.identity_num := get_code_value(p_present_line,3060);
165 p_csv_rec.passport_num := get_code_value(p_present_line,3070);
166 p_csv_rec.country_of_issue := get_code_value(p_present_line,3075);
167 p_csv_rec.date_of_birth := get_code_value(p_present_line,3080);
168 p_csv_rec.tax_ref_num := get_code_value(p_present_line,3100);
169 p_csv_rec.emp_num := get_code_value(p_present_line,3160);
170 p_csv_rec.status := get_code_value(p_present_line,4501);
171
172 END convert_line_to_rec;
173
174
175
176 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
177
178 l_proc_name varchar2(40):=g_pkg_name||'BEFOREREPORT';
179
180 l_location VARCHAR2(2000);
181 l_defined BOOLEAN;
182 l_file_handle UTL_FILE.FILE_TYPE;
183 -- l_validate BOOLEAN:= true; /* Bug 12991144 - no longer required as API is not being used */
184 l_present_line VARCHAR2(2000);
185 l_person_rec PER_ALL_PEOPLE_F%ROWTYPE;
186 -- l_datetrack_update_mode VARCHAR2(2000); /* Bug 12991144- no longer required as API is not being used */
187 l_result VARCHAR2(2);
188 l_line_num NUMBER := 1;
189
190 E_DATA_EX_DIR_NOT_DEFINED EXCEPTION;
191
192 -- l_object_version_number NUMBER; /* Bug 12991144 - no longer required as API is not being used */
193 l_effective_start_date DATE;
194 l_effective_end_date DATE;
195 l_full_name VARCHAR2(200);
196 l_comment_id NUMBER;
197 l_name_combination_warning BOOLEAN;
198 l_assign_payroll_warning BOOLEAN;
199 l_orig_hire_warning BOOLEAN;
200
201
202 csv_rec csv_rec_defn;
203
204 cursor csr_find_by_emp_num(p_emp_num varchar2)
205 IS
206 SELECT *
207 FROM per_all_people_f papf
208 WHERE papf.business_group_id = p_business_group_id
209 AND papf.employee_number = p_emp_num
210 AND p_effective_date BETWEEN papf.effective_start_date
211 AND papf.effective_end_date
212 And papf.PER_INFORMATION_CATEGORY = 'ZA'; -- Bug 12991144
213
214 BEGIN
215 -- Uncomment the below line to enable trace
216 -- hr_utility.trace_on(NULL,'ZAITREF');
217
218 hr_utility.set_location('Entering:'||g_pkg_name,5);
219
220 -- Find Busincess group name
221 SELECT pbg.name
222 INTO cp_bg_name
223 FROM per_business_groups pbg
224 WHERE pbg.business_group_id = p_business_group_id
225 AND p_effective_date BETWEEN pbg.date_from AND nvl(pbg.date_to,to_date ('31-12-4712','DD-MM-YYYY'));
226 hr_utility.set_location('Business Group Name:'||cp_bg_name,10);
227
228 -- Get the directory path from the profile
229 --fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
230 fnd_profile.get_specific('PER_DATA_EXCHANGE_DIR',
231 FND_GLOBAL.USER_ID,
232 FND_GLOBAL.RESP_ID,
233 FND_GLOBAL.RESP_APPL_ID,
234 l_location,
235 l_defined,
236 FND_GLOBAL.ORG_ID,
237 FND_GLOBAL.server_id);
238 hr_utility.set_location('Input Directory:'|| l_location ,15);
239 IF l_location IS NULL THEN
240 RAISE E_DATA_EX_DIR_NOT_DEFINED;
241 END IF;
242
243 -- Bug 12991144
244 -- no longer required since API is not being used
245 /*
246 -- Set the processing mode
247 IF P_MODE = 'Validate and Update' THEN
248 hr_utility.set_location('Validate mode is false',20);
249 l_validate := false;
250 ELSE
251 hr_utility.set_location('Validate mode is true',25);
252 l_validate := true;
253 END IF;
254 */
255 -- Bug 12991144
256
257 -- Open the CSV file for Processing
258 l_file_handle := utl_file.fopen(l_location,p_file_name,'r');
259 hr_utility.set_location(p_file_name|| ' File Opened for Processing',30);
260
261 -- Create a Payroll action id
262 create_payroll_action();
263 hr_utility.set_location('Payroll action id:'||G_PAYROLL_ACTION_ID ,35);
264
265 -- Read each csv record and process
266 LOOP
267 -- Read line from the file
268 BEGIN
269 utl_file.get_line(l_file_handle,l_present_line);
270 EXCEPTION
271 WHEN NO_DATA_FOUND THEN -- Reached End of the file
272 hr_utility.set_location('Finished Processing the file:'||p_file_name,40);
273 UTL_FILE.FCLOSE(l_file_handle);
274 EXIT;
275 END;
276
277 -- Process the csv line
278 IF (substr(l_present_line,1,4) = 3010) THEN
279 l_line_num := l_line_num+1;
280 hr_utility.set_location('Processing Line No:'||l_line_num,45);
281 hr_utility.set_location('Processing Line:'||l_present_line,50);
282
283 -- Convert csv line to record
284 convert_line_to_rec(l_present_line,l_line_num,csv_rec);
285
286 -- Bug 12991144
287
288 -- Checking if the record needs to be processed depending upon the status returned by SARS
289 IF upper(csv_rec.status) in ('TAXPAYER TO CONTACT SARS', 'INSUFFICIENT INFORMATION', 'NOT VERIFIED', 'NON-INDIVIDUAL') THEN
290 hr_utility.set_location('As no data provided by SARS, no need to process csv record of employee number:'||csv_rec.emp_num,52);
291 create_action_information(csv_rec,'NA');
292 ELSIF upper(csv_rec.status) in ('REGISTERED', 'EXISTING TAXPAYER') THEN
293
294 -- Bug 12991144
295
296 -- Check if employee number and tax ref number exists
297 IF (csv_rec.emp_num IS NOT NULL AND csv_rec.tax_ref_num IS NOT NULL) THEN
298 hr_utility.set_location('Processing csv record of employee number:'||csv_rec.emp_num,55);
299 OPEN csr_find_by_emp_num(csv_rec.emp_num);
300 FETCH csr_find_by_emp_num into l_person_rec;
301
302 IF (csr_find_by_emp_num%NOTFOUND) THEN
303 -- Report the record as not able to find the matching employee
304 create_action_information(csv_rec,'BN');
305 ELSIF (l_person_rec.per_information1 IS NULL) OR (l_person_rec.per_information1 <> csv_rec.tax_ref_num) THEN
306 -- Bug 12991144
307 IF (l_person_rec.per_information1 IS NULL) THEN
308 l_result := 'EU';
309 ELSE
310 l_result := 'FC';
311 END IF;
312
313 BEGIN
314 UPDATE PER_ALL_PEOPLE_F
315 SET PER_INFORMATION1 = csv_rec.tax_ref_num
316 WHERE EMPLOYEE_NUMBER = l_person_rec.employee_number
317 and BUSINESS_GROUP_ID = l_person_rec.business_group_id
318 and PER_INFORMATION_CATEGORY = 'ZA';
319
320 /*
321 l_object_version_number := l_person_rec.object_version_number;
322 IF (l_person_rec.per_information1 IS NULL) THEN
323 l_datetrack_update_mode := 'UPDATE';
324 l_result := 'EU';
325 ELSE
326 l_datetrack_update_mode := 'CORRECTION';
327 l_result := 'FC';
328 END IF;
329 hr_utility.set_location('Processing employee number in Mode:'||l_datetrack_update_mode,60);
330 BEGIN
331 hr_person_api.update_person(p_validate => l_validate
332 ,p_effective_date => p_effective_date
333 ,p_datetrack_update_mode => l_datetrack_update_mode
334 ,p_person_id => l_person_rec.person_id
335 ,p_object_version_number => l_object_version_number
336 ,p_employee_number => l_person_rec.employee_number
337 ,p_per_information1 => csv_rec.tax_ref_num
338 ,p_effective_start_date => l_effective_start_date
339 ,p_effective_end_date => l_effective_end_date
340 ,p_full_name => l_full_name
341 ,p_comment_id => l_comment_id
342 ,p_name_combination_warning => l_name_combination_warning
343 ,p_assign_payroll_warning => l_assign_payroll_warning
344 ,p_orig_hire_warning => l_orig_hire_warning
345 );
346
347 IF (NOT HR_ERRORS_API.ERROREXISTS) THEN
348 */
349 -- Bug 12991144
350 -- Report the record as Sucessfully Processed
351 create_action_information(csv_rec,l_result,null,l_person_rec.per_information1);
352 hr_utility.set_location('Updated Employee IT Ref number:'||l_person_rec.employee_number,65);
353
354 -- Bug 12991144
355 /* ELSE
356 -- Report the record as Error While Processing
357 create_action_information(csv_rec,'CE',SUBSTR(hr_utility.get_message,1,100));
358 hr_utility.set_location('Error in Updating Employee IT Ref number'||l_person_rec.employee_number,68);
359 END IF;
360 */
361 -- Bug 12991144
362 EXCEPTION
363 WHEN OTHERS THEN
364 hr_utility.set_location('Error in Updating Employee IT Ref number'||l_person_rec.employee_number,70);
365 hr_utility.set_location('Error:'||SUBSTR(hr_utility.get_message,1,100),76);
366 -- Report the record as Error While Processing
367 create_action_information(csv_rec,'CE',SUBSTR(hr_utility.get_message,1,100));
368 END;
369 ELSE -- CSV ITREF and Employee ITREF is same
370 -- Report ITREF is same in both CSV Record and employee
371 create_action_information(csv_rec,'DS');
372 END IF;
373 CLOSE csr_find_by_emp_num;
374
375 ELSE -- i.e. emp num and tax ref num is null
376 -- Report the record as invalid record
377 create_action_information(csv_rec,'BN');
378 END IF;
379 END IF; -- for the block checking statuses (Bug 12991144)
380 ELSIF (substr(l_present_line,1,4) = 2010) THEN
381 CP_LE_NAME := GET_CODE_VALUE(l_present_line,2010);
382 CP_PAYE_NUM := GET_CODE_VALUE(l_present_line,2020);
383 ELSIF (substr(l_present_line,1,4) = 6010) THEN
384 CP_TOT_REC := GET_CODE_VALUE(l_present_line,6010);
385 END IF;
386
387 END LOOP;
388
389 --hr_utility.trace_off;
390 return true;
391
392
393 EXCEPTION
394 WHEN E_DATA_EX_DIR_NOT_DEFINED THEN
395 hr_utility.set_location('Exception :'||g_pkg_name,100);
396 ROLLBACK;
397 RAISE_APPLICATION_ERROR(-20010,'Input directory not defined. Set PER_DATA_EXCHANGE_DIR profile (HR: Data Exchange directory).');
398 WHEN UTL_FILE.INVALID_OPERATION THEN
399 UTL_FILE.FCLOSE(l_file_handle);
400 hr_utility.set_location('Exception :'||g_pkg_name,110);
401 ROLLBACK;
402 RAISE;
403 WHEN UTL_FILE.INTERNAL_ERROR THEN
404 UTL_FILE.FCLOSE(l_file_handle);
405 hr_utility.set_location('Exception :'||g_pkg_name,120);
406 ROLLBACK;
407 RAISE;
408 WHEN UTL_FILE.INVALID_MODE THEN
409 UTL_FILE.FCLOSE(l_file_handle);
410 hr_utility.set_location('Exception :'||g_pkg_name,130);
411 ROLLBACK;
412 RAISE;
413 WHEN UTL_FILE.INVALID_PATH THEN
414 UTL_FILE.FCLOSE(l_file_handle);
415 hr_utility.set_location('Exception :'||g_pkg_name,140);
416 ROLLBACK;
417 RAISE;
418 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
419 UTL_FILE.FCLOSE(l_file_handle);
420 hr_utility.set_location('Exception :'||g_pkg_name,150);
421 ROLLBACK;
422 RAISE;
423 WHEN UTL_FILE.READ_ERROR THEN
424 UTL_FILE.FCLOSE(l_file_handle);
425 hr_utility.set_location('Exception :'||g_pkg_name,160);
426 ROLLBACK;
427 RAISE;
428 WHEN NO_DATA_FOUND THEN
429 UTL_FILE.FCLOSE(l_file_handle);
430 hr_utility.set_location('Exception :'||g_pkg_name,170);
431 ROLLBACK;
432 RAISE;
433 WHEN OTHERS THEN
434 UTL_FILE.FCLOSE(l_file_handle);
435 hr_utility.set_location('Exception :'||g_pkg_name,180);
436 ROLLBACK;
437 RAISE;
438
439 END BEFOREREPORT;
440
441
442 FUNCTION AFTERREPORT RETURN BOOLEAN IS
443
444 BEGIN
445 IF P_MODE = 'Validate and Update' THEN
446 COMMIT;
447 ELSE
448 ROLLBACK;
449 END IF;
450 return true;
451
452 END AFTERREPORT;
453
454 END PY_ZA_ITREF_UPLOAD;