[Home] [Help]
PACKAGE BODY: APPS.HR_HU_UTILITY
Source
1 PACKAGE BODY hr_hu_utility as
2 /* $Header: pehuutil.pkb 120.0.12010000.2 2009/12/02 11:36:00 dchindar ship $ */
3
4 ---
5 FUNCTION validate_account_no(p_acc_no VARCHAR2) RETURN NUMBER IS
6 --
7 X1 NUMBER;
8 X2 NUMBER;
9 X3 NUMBER;
10 X4 NUMBER;
11 X5 NUMBER;
12 X6 NUMBER;
13 X7 NUMBER;
14 X8 NUMBER;
15 X9 NUMBER;
16 X10 NUMBER;
17 X11 NUMBER;
18 X12 NUMBER;
19 X13 NUMBER;
20 X14 NUMBER;
21 X15 NUMBER;
22 X16 NUMBER;
23 --
24 l_len NUMBER;
25 check_digit NUMBER;
26
27 --
28 BEGIN
29 --
30 l_len := length(p_acc_no);
31 IF l_len < 17 OR l_len > 26 THEN
32 RETURN 0;
33 END IF;
34 IF l_len > 17 AND l_len <> 26 THEN
35 RETURN 0;
36 END IF;
37 --
38 IF l_len = 17 THEN
39 -- Modify to bug no. 3335549
40 IF p_acc_no = '00000000-00000000' THEN
41 RETURN 0;
42 END IF;
43 IF instr(p_acc_no,'-',1,1) <> 9 then
44 RETURN 0;
45 END IF;
46
47 -- Check format
48 IF hr_ni_chk_pkg.chk_nat_id_format(p_acc_no,'DDDDDDDD-DDDDDDDD') = '0' THEN
49 -- Incorrect format
50 RETURN 0;
51 END IF;
52 --
53 X1 := substr(p_acc_no,1,1);
54 X2 := substr(p_acc_no,2,1);
55 X3 := substr(p_acc_no,3,1);
56 X4 := substr(p_acc_no,4,1);
57 X5 := substr(p_acc_no,5,1);
58 X6 := substr(p_acc_no,6,1);
59 X7 := substr(p_acc_no,7,1);
60 X8 := substr(p_acc_no,8,1);
61 --
62 --
63 check_digit := (X1*9) + (X2*7) + (X3*3) + (X4*1)
64 + (X5*9) + (X6*7) + (X7*3);
65 --
66 check_digit := 10 - mod(check_digit,10);
67 --
68 IF check_digit = 10 THEN
69 check_digit := 0;
70 END IF;
71
72 IF check_digit <> X8 THEN
73 RETURN 0;
74 END IF;
75 --
76 X1 := substr(p_acc_no,10,1);
77 X2 := substr(p_acc_no,11,1);
78 X3 := substr(p_acc_no,12,1);
79 X4 := substr(p_acc_no,13,1);
80 X5 := substr(p_acc_no,14,1);
81 X6 := substr(p_acc_no,15,1);
82 X7 := substr(p_acc_no,16,1);
83 X8 := substr(p_acc_no,17,1);
84
85 check_digit := (X1*9) + (X2*7) + (X3*3) + (X4*1)
86 + (X5*9) + (X6*7) + (X7*3);
87
88 --
89 check_digit := 10 - mod(check_digit,10);
90 --
91 IF check_digit = 10 THEN
92 check_digit := 0;
93 END IF;
94 --
95 IF check_digit <> X8 THEN
96 RETURN 0;
97 END IF;
98 --
99 ELSE
100 -- Modify to bug no. 3335549
101 IF p_acc_no = '00000000-00000000-00000000' THEN
102 RETURN 0;
103 END IF;
104
105 IF (instr(p_acc_no,'-',1,1) <> 9 OR instr(p_acc_no,'-',1,2) <> 18) THEN
106 RETURN 0;
107 END IF;
108
109 IF hr_ni_chk_pkg.chk_nat_id_format(p_acc_no,'DDDDDDDD-DDDDDDDD-DDDDDDDD')= '0' THEN
110 -- Incorrect format
111 RETURN 0;
112 END IF;
113
114 -- check for branch code
115 X1 := substr(p_acc_no,1,1);
116 X2 := substr(p_acc_no,2,1);
117 X3 := substr(p_acc_no,3,1);
118 X4 := substr(p_acc_no,4,1);
119 X5 := substr(p_acc_no,5,1);
120 X6 := substr(p_acc_no,6,1);
121 X7 := substr(p_acc_no,7,1);
122 X8 := substr(p_acc_no,8,1);
123 --
124 --
125 check_digit := (X1*9) + (X2*7) + (X3*3) + (X4*1)
126 + (X5*9) + (X6*7) + (X7*3);
127 --
128 check_digit := 10 - mod(check_digit,10);
129 --
130 IF check_digit = 10 THEN
131 check_digit := 0;
132 END IF;
133
134 IF check_digit <> X8 THEN
135 RETURN 0;
136 END IF;
137
138 -- Check for account no 1 and account no 2
139 X1 := substr(p_acc_no,10,1);
140 X2 := substr(p_acc_no,11,1);
141 X3 := substr(p_acc_no,12,1);
142 X4 := substr(p_acc_no,13,1);
143 X5 := substr(p_acc_no,14,1);
144 X6 := substr(p_acc_no,15,1);
145 X7 := substr(p_acc_no,16,1);
146 X8 := substr(p_acc_no,17,1);
147 X9 := substr(p_acc_no,19,1);
148 X10 := substr(p_acc_no,20,1);
149 X11 := substr(p_acc_no,21,1);
150 X12 := substr(p_acc_no,22,1);
151 X13 := substr(p_acc_no,23,1);
152 X14 := substr(p_acc_no,24,1);
153 X15 := substr(p_acc_no,25,1);
154 X16 := substr(p_acc_no,26,1);
155 --
156 check_digit := (X1*9) + (X2*7) + (X3*3) + (X4*1)
157 + (X5*9) + (X6*7) + (X7*3) + (X8*1)
158 + (X9*9) + (X10*7) + (X11*3) + (X12*1)
159 + (X13*9) + (X14*7) + (X15*3) ;
160
161 --
162
163 check_digit := 10 - mod(check_digit,10);
164 --
165 IF check_digit = 10 THEN
166 check_digit := 0;
167 END IF;
168 --
169 IF check_digit <> X16 THEN
170 RETURN 0;
171 END IF;
172
173
174 END IF;
175 --
176 --
177 RETURN 1;
178 --
179 END validate_account_no;
180
181
182
183 ----
184 -- Function added for IBAN Validation
185 ----
186 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
187 BEGIN
188 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
189 RETURN 1;
190 else
191 RETURN 0;
192 END IF;
193 END validate_iban_acc;
194
195 ----
196 -- This function will get called from the bank keyflex field segments
197 ----
198 FUNCTION validate_account_entered
199 (p_acc_no IN VARCHAR2,
200 p_is_iban_acc IN varchar2 ) RETURN NUMBER IS
201 --
202 l_ret NUMBER ;
203 begin
204 -- hr_utility.trace_on(null,'ACCVAL');
205 l_ret :=0;
206 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
207 hr_utility.set_location('p_account_number ' || p_acc_no,1);
208
209 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
210 l_ret := validate_account_no(p_acc_no);
211 hr_utility.set_location('l_ret ' || l_ret,1);
212 RETURN l_ret;
213 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
214 l_ret := validate_iban_acc(p_acc_no);
215 hr_utility.set_location('l_ret ' || l_ret,3);
216 RETURN l_ret;
217 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
218 hr_utility.set_location('Both Account Nos Null',4);
219 RETURN 1;
220 ELSE
221 hr_utility.set_location('l_ret: 3 ' ,5);
222 RETURN 3;
223 END if;
224 End validate_account_entered;
225
226
227 --
228 FUNCTION check_tax_identification_no(p_tax_id_no VARCHAR2) RETURN NUMBER IS
229
230 X1 NUMBER;
231 X2 NUMBER;
232 X3 NUMBER;
233 X4 NUMBER;
234 X5 NUMBER;
235 X6 NUMBER;
236 X7 NUMBER;
237 X8 NUMBER;
238 X9 NUMBER;
239 X10 NUMBER;
240
241 check_digit NUMBER;
242
243 BEGIN
244 --
245 IF p_tax_id_no = '0000000000' THEN
246 RETURN 0;
247 END IF;
248
249 IF hr_ni_chk_pkg.chk_nat_id_format(substr(p_tax_id_no,1,30),'DDDDDDDDDD') = '0' THEN
250 -- Incorrect format
251 RETURN 0;
252 END IF;
253 --
254 X1 := substr(p_tax_id_no,1,1);
255 X2 := substr(p_tax_id_no,2,1);
256 X3 := substr(p_tax_id_no,3,1);
257 X4 := substr(p_tax_id_no,4,1);
258 X5 := substr(p_tax_id_no,5,1);
259 X6 := substr(p_tax_id_no,6,1);
260 X7 := substr(p_tax_id_no,7,1);
261 X8 := substr(p_tax_id_no,8,1);
262 X9 := substr(p_tax_id_no,9,1);
263 X10 := substr(p_tax_id_no,10,1);
264 --
265 IF X1 <> 8 then
266 RETURN 0;
267 END IF;
268 --
269 check_digit := (X1*1)+(X2*2)+(X3*3)+(X4*4)+(X5*5)+(X6*6)+(X7*7)+(X8*8)+(X9*9);
270 check_digit := mod(check_digit,11);
271 --
272 IF check_digit <> X10 then
273 RETURN 0;
274 END IF;
275 --
276 RETURN 1;
277 --
278 END check_tax_identification_no;
279 ---
280 FUNCTION per_hu_full_name(
281 p_first_name IN VARCHAR2
282 ,p_middle_names IN VARCHAR2
283 ,p_last_name IN VARCHAR2
284 ,p_known_as IN VARCHAR2
285 ,p_title IN VARCHAR2
286 ,p_suffix IN VARCHAR2
287 ,p_pre_name_adjunct IN VARCHAR2
288 ,p_per_information1 IN VARCHAR2
289 ,p_per_information2 IN VARCHAR2
290 ,p_per_information3 IN VARCHAR2
291 ,p_per_information4 IN VARCHAR2
292 ,p_per_information5 IN VARCHAR2
293 ,p_per_information6 IN VARCHAR2
294 ,p_per_information7 IN VARCHAR2
295 ,p_per_information8 IN VARCHAR2
296 ,p_per_information9 IN VARCHAR2
297 ,p_per_information10 IN VARCHAR2
298 ,p_per_information11 IN VARCHAR2
299 ,p_per_information12 IN VARCHAR2
300 ,p_per_information13 IN VARCHAR2
301 ,p_per_information14 IN VARCHAR2
302 ,p_per_information15 IN VARCHAR2
303 ,p_per_information16 IN VARCHAR2
304 ,p_per_information17 IN VARCHAR2
305 ,p_per_information18 IN VARCHAR2
306 ,p_per_information19 IN VARCHAR2
307 ,p_per_information20 IN VARCHAR2
308 ,p_per_information21 IN VARCHAR2
309 ,p_per_information22 IN VARCHAR2
310 ,p_per_information23 IN VARCHAR2
311 ,p_per_information24 IN VARCHAR2
312 ,p_per_information25 IN VARCHAR2
313 ,p_per_information26 IN VARCHAR2
314 ,p_per_information27 IN VARCHAR2
315 ,p_per_information28 IN VARCHAR2
316 ,p_per_information29 IN VARCHAR2
317 ,p_per_information30 IN VARCHAR2
318 ) RETURN VARCHAR2 is
319 --
320 l_full_name VARCHAR2(240);
321 --
322 BEGIN
323 --
324 SELECT SUBSTR(LTRIM(RTRIM(
325 DECODE(p_pre_name_adjunct , NULL,'',' ' || p_pre_name_adjunct)
326 ||DECODE(p_last_name, NULL, '', ' ' || p_last_name)
327 ||DECODE(p_first_name,NULL, '', ' ' || p_first_name)
328 ||DECODE(p_middle_names,NULL, '', ' ' || p_middle_names)
329 )), 1, 240)
330 INTO l_full_name
331 FROM dual;
332
333 RETURN l_full_name;
334 --
335 END per_hu_full_name;
336
337 ---
338 FUNCTION per_hu_order_name(
339 p_first_name IN VARCHAR2
340 ,p_middle_names IN VARCHAR2
341 ,p_last_name IN VARCHAR2
342 ,p_known_as IN VARCHAR2
343 ,p_title IN VARCHAR2
344 ,p_suffix IN VARCHAR2
345 ,p_pre_name_adjunct IN VARCHAR2
346 ,p_per_information1 IN VARCHAR2
347 ,p_per_information2 IN VARCHAR2
348 ,p_per_information3 IN VARCHAR2
349 ,p_per_information4 IN VARCHAR2
350 ,p_per_information5 IN VARCHAR2
351 ,p_per_information6 IN VARCHAR2
352 ,p_per_information7 IN VARCHAR2
353 ,p_per_information8 IN VARCHAR2
354 ,p_per_information9 IN VARCHAR2
355 ,p_per_information10 IN VARCHAR2
356 ,p_per_information11 IN VARCHAR2
357 ,p_per_information12 IN VARCHAR2
358 ,p_per_information13 IN VARCHAR2
359 ,p_per_information14 IN VARCHAR2
360 ,p_per_information15 IN VARCHAR2
361 ,p_per_information16 IN VARCHAR2
362 ,p_per_information17 IN VARCHAR2
363 ,p_per_information18 IN VARCHAR2
364 ,p_per_information19 IN VARCHAR2
365 ,p_per_information20 IN VARCHAR2
366 ,p_per_information21 IN VARCHAR2
367 ,p_per_information22 IN VARCHAR2
368 ,p_per_information23 IN VARCHAR2
369 ,p_per_information24 IN VARCHAR2
370 ,p_per_information25 IN VARCHAR2
371 ,p_per_information26 IN VARCHAR2
372 ,p_per_information27 IN VARCHAR2
373 ,p_per_information28 IN VARCHAR2
374 ,p_per_information29 IN VARCHAR2
375 ,p_per_information30 IN VARCHAR2)
376 RETURN VARCHAR2 IS
377 --
378 l_order_name VARCHAR2(240);
379 --
380 BEGIN
381 --
382 SELECT SUBSTR(TRIM(NVL(p_pre_name_adjunct,p_last_name)), 1, 240)
383 INTO l_order_name
384 FROM dual;
385
386 RETURN(l_order_name);
387 --
388 END per_hu_order_name;
389 --
390 ----------------------------------------------------------------
391 PROCEDURE validate_ss_no(p_org_info VARCHAR2) is
392 l_ss_no VARCHAR2(10);
393 BEGIN
394 l_ss_no := hr_ni_chk_pkg.chk_nat_id_format(p_org_info,'DDDDDDD-A');
395 IF l_ss_no ='0' THEN
396 hr_utility.set_message(800, 'HR_HU_INVALID_SS_NO');
397 hr_utility.raise_error;
398 END IF;
399 END validate_ss_no;
400 ---------------------------------------------------------------
401 PROCEDURE validate_tax_no(p_org_info VARCHAR2) is
402 l_tax_no VARCHAR2(15);
403 BEGIN
404 IF p_org_info = '00000000-0-00' THEN
405 hr_utility.set_message(800, 'HR_HU_INVALID_TAX_NO');
406 hr_utility.raise_error;
407 END IF;
408
409 IF (instr(p_org_info,'-',1,1) <> 9 OR instr(p_org_info,'-',1,2) <> 11) THEN
410 hr_utility.set_message(800, 'HR_HU_INVALID_TAX_NO');
411 hr_utility.raise_error;
412 END IF;
413
414 l_tax_no := hr_ni_chk_pkg.chk_nat_id_format(p_org_info,'DDDDDDDD-D-DD');
415 IF l_tax_no ='0' THEN
416 hr_utility.set_message(800, 'HR_HU_INVALID_TAX_NO');
417 hr_utility.raise_error;
418 END IF;
419 END validate_tax_no;
420 ---------------------------------------------------------------
421 PROCEDURE validate_cs_no(p_org_info4 VARCHAR2
422 ,p_org_info5 VARCHAR2) is
423
424 l_cs_no VARCHAR2(8);
425 BEGIN
426 l_cs_no := substr(p_org_info4,1,8);
427 IF l_cs_no <> p_org_info5 THEN
428 hr_utility.set_message(800, 'HR_HU_INVALID_CS_NO');
429 hr_utility.raise_error;
430 END IF;
431 END validate_cs_no;
432 -----------------------------------------------------------------
433
434 PROCEDURE check_tax_identifier_unique
435 ( p_identifier VARCHAR2,
436 p_person_id NUMBER,
437 p_business_group_id NUMBER)
438 is
439 --
440 l_status VARCHAR2(1);
441 l_legislation_code VARCHAR2(30);
442 l_nat_lbl VARCHAR2(2000);
443 local_warning EXCEPTION;
444
445 BEGIN
446 --
447 BEGIN
448 SELECT 'Y'
449 INTO l_status
450 FROM sys.dual
451 WHERE exists(SELECT '1'
452 FROM per_all_people_f pp
453 WHERE (p_person_id IS NULL
454 OR p_person_id <> pp.person_id)
455 AND p_identifier = pp.per_information2
456 AND pp.business_group_id = p_business_group_id);
457 --
458 IF l_status = 'Y' THEN
459 hr_utility.set_message(800, 'HR_HU_NI_UNIQUE_WARNING');
460 hr_utility.set_message_token('NI_NUMBER',hr_general.decode_lookup('HU_FORM_LABELS','TAX_ID_NO'));
461 hr_utility.raise_error;
462 END IF;
463 --
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN NULL;
466 END;
467 END check_tax_identifier_unique;
468
469
470 -----------------------------------------------------------------
471 END hr_hu_utility;