[Home] [Help]
PACKAGE BODY: APPS.HR_HU_UTILITY
Source
1 PACKAGE BODY hr_hu_utility as
2 /* $Header: pehuutil.pkb 115.8 2004/05/25 07:18:02 viviswan noship $ */
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 FUNCTION check_tax_identification_no(p_tax_id_no VARCHAR2) RETURN NUMBER IS
182
183 X1 NUMBER;
184 X2 NUMBER;
185 X3 NUMBER;
186 X4 NUMBER;
187 X5 NUMBER;
188 X6 NUMBER;
189 X7 NUMBER;
190 X8 NUMBER;
191 X9 NUMBER;
192 X10 NUMBER;
193
194 check_digit NUMBER;
195
196 BEGIN
197 --
198 IF p_tax_id_no = '0000000000' THEN
199 RETURN 0;
200 END IF;
201
202 IF hr_ni_chk_pkg.chk_nat_id_format(substr(p_tax_id_no,1,30),'DDDDDDDDDD') = '0' THEN
203 -- Incorrect format
204 RETURN 0;
205 END IF;
206 --
207 X1 := substr(p_tax_id_no,1,1);
208 X2 := substr(p_tax_id_no,2,1);
209 X3 := substr(p_tax_id_no,3,1);
210 X4 := substr(p_tax_id_no,4,1);
211 X5 := substr(p_tax_id_no,5,1);
212 X6 := substr(p_tax_id_no,6,1);
213 X7 := substr(p_tax_id_no,7,1);
214 X8 := substr(p_tax_id_no,8,1);
215 X9 := substr(p_tax_id_no,9,1);
216 X10 := substr(p_tax_id_no,10,1);
217 --
218 IF X1 <> 8 then
219 RETURN 0;
220 END IF;
221 --
222 check_digit := (X1*1)+(X2*2)+(X3*3)+(X4*4)+(X5*5)+(X6*6)+(X7*7)+(X8*8)+(X9*9);
223 check_digit := mod(check_digit,11);
224 --
225 IF check_digit <> X10 then
226 RETURN 0;
227 END IF;
228 --
229 RETURN 1;
230 --
231 END check_tax_identification_no;
232 ---
233 FUNCTION per_hu_full_name(
234 p_first_name IN VARCHAR2
235 ,p_middle_names IN VARCHAR2
236 ,p_last_name IN VARCHAR2
237 ,p_known_as IN VARCHAR2
238 ,p_title IN VARCHAR2
239 ,p_suffix IN VARCHAR2
240 ,p_pre_name_adjunct IN VARCHAR2
241 ,p_per_information1 IN VARCHAR2
242 ,p_per_information2 IN VARCHAR2
243 ,p_per_information3 IN VARCHAR2
244 ,p_per_information4 IN VARCHAR2
245 ,p_per_information5 IN VARCHAR2
246 ,p_per_information6 IN VARCHAR2
247 ,p_per_information7 IN VARCHAR2
248 ,p_per_information8 IN VARCHAR2
249 ,p_per_information9 IN VARCHAR2
250 ,p_per_information10 IN VARCHAR2
251 ,p_per_information11 IN VARCHAR2
252 ,p_per_information12 IN VARCHAR2
253 ,p_per_information13 IN VARCHAR2
254 ,p_per_information14 IN VARCHAR2
255 ,p_per_information15 IN VARCHAR2
256 ,p_per_information16 IN VARCHAR2
257 ,p_per_information17 IN VARCHAR2
258 ,p_per_information18 IN VARCHAR2
259 ,p_per_information19 IN VARCHAR2
260 ,p_per_information20 IN VARCHAR2
261 ,p_per_information21 IN VARCHAR2
262 ,p_per_information22 IN VARCHAR2
263 ,p_per_information23 IN VARCHAR2
264 ,p_per_information24 IN VARCHAR2
265 ,p_per_information25 IN VARCHAR2
266 ,p_per_information26 IN VARCHAR2
267 ,p_per_information27 IN VARCHAR2
268 ,p_per_information28 IN VARCHAR2
269 ,p_per_information29 IN VARCHAR2
270 ,p_per_information30 IN VARCHAR2
271 ) RETURN VARCHAR2 is
272 --
273 l_full_name VARCHAR2(240);
274 --
275 BEGIN
276 --
277 SELECT SUBSTR(LTRIM(RTRIM(
278 DECODE(p_pre_name_adjunct , NULL,'',' ' || p_pre_name_adjunct)
279 ||DECODE(p_last_name, NULL, '', ' ' || p_last_name)
280 ||DECODE(p_first_name,NULL, '', ' ' || p_first_name)
281 ||DECODE(p_middle_names,NULL, '', ' ' || p_middle_names)
282 )), 1, 240)
283 INTO l_full_name
284 FROM dual;
285
286 RETURN l_full_name;
287 --
288 END per_hu_full_name;
289
290 ---
291 FUNCTION per_hu_order_name(
292 p_first_name IN VARCHAR2
293 ,p_middle_names IN VARCHAR2
294 ,p_last_name IN VARCHAR2
295 ,p_known_as IN VARCHAR2
296 ,p_title IN VARCHAR2
297 ,p_suffix IN VARCHAR2
298 ,p_pre_name_adjunct IN VARCHAR2
299 ,p_per_information1 IN VARCHAR2
300 ,p_per_information2 IN VARCHAR2
301 ,p_per_information3 IN VARCHAR2
302 ,p_per_information4 IN VARCHAR2
303 ,p_per_information5 IN VARCHAR2
304 ,p_per_information6 IN VARCHAR2
305 ,p_per_information7 IN VARCHAR2
306 ,p_per_information8 IN VARCHAR2
307 ,p_per_information9 IN VARCHAR2
308 ,p_per_information10 IN VARCHAR2
309 ,p_per_information11 IN VARCHAR2
310 ,p_per_information12 IN VARCHAR2
311 ,p_per_information13 IN VARCHAR2
312 ,p_per_information14 IN VARCHAR2
313 ,p_per_information15 IN VARCHAR2
314 ,p_per_information16 IN VARCHAR2
315 ,p_per_information17 IN VARCHAR2
316 ,p_per_information18 IN VARCHAR2
317 ,p_per_information19 IN VARCHAR2
318 ,p_per_information20 IN VARCHAR2
319 ,p_per_information21 IN VARCHAR2
320 ,p_per_information22 IN VARCHAR2
321 ,p_per_information23 IN VARCHAR2
322 ,p_per_information24 IN VARCHAR2
323 ,p_per_information25 IN VARCHAR2
324 ,p_per_information26 IN VARCHAR2
325 ,p_per_information27 IN VARCHAR2
326 ,p_per_information28 IN VARCHAR2
327 ,p_per_information29 IN VARCHAR2
328 ,p_per_information30 IN VARCHAR2)
329 RETURN VARCHAR2 IS
330 --
331 l_order_name VARCHAR2(240);
332 --
333 BEGIN
334 --
335 SELECT SUBSTR(TRIM(NVL(p_pre_name_adjunct,p_last_name)), 1, 240)
336 INTO l_order_name
337 FROM dual;
338
339 RETURN(l_order_name);
340 --
341 END per_hu_order_name;
342 --
343 ----------------------------------------------------------------
344 PROCEDURE validate_ss_no(p_org_info VARCHAR2) is
345 l_ss_no VARCHAR2(10);
346 BEGIN
347 l_ss_no := hr_ni_chk_pkg.chk_nat_id_format(p_org_info,'DDDDDDD-A');
348 IF l_ss_no ='0' THEN
349 hr_utility.set_message(800, 'HR_HU_INVALID_SS_NO');
350 hr_utility.raise_error;
351 END IF;
352 END validate_ss_no;
353 ---------------------------------------------------------------
354 PROCEDURE validate_tax_no(p_org_info VARCHAR2) is
355 l_tax_no VARCHAR2(15);
356 BEGIN
357 IF p_org_info = '00000000-0-00' THEN
358 hr_utility.set_message(800, 'HR_HU_INVALID_TAX_NO');
359 hr_utility.raise_error;
360 END IF;
361
362 IF (instr(p_org_info,'-',1,1) <> 9 OR instr(p_org_info,'-',1,2) <> 11) THEN
363 hr_utility.set_message(800, 'HR_HU_INVALID_TAX_NO');
364 hr_utility.raise_error;
365 END IF;
366
367 l_tax_no := hr_ni_chk_pkg.chk_nat_id_format(p_org_info,'DDDDDDDD-D-DD');
368 IF l_tax_no ='0' THEN
369 hr_utility.set_message(800, 'HR_HU_INVALID_TAX_NO');
370 hr_utility.raise_error;
371 END IF;
372 END validate_tax_no;
373 ---------------------------------------------------------------
374 PROCEDURE validate_cs_no(p_org_info4 VARCHAR2
375 ,p_org_info5 VARCHAR2) is
376
377 l_cs_no VARCHAR2(8);
378 BEGIN
379 l_cs_no := substr(p_org_info4,1,8);
380 IF l_cs_no <> p_org_info5 THEN
381 hr_utility.set_message(800, 'HR_HU_INVALID_CS_NO');
382 hr_utility.raise_error;
383 END IF;
384 END validate_cs_no;
385 -----------------------------------------------------------------
386
387 PROCEDURE check_tax_identifier_unique
388 ( p_identifier VARCHAR2,
389 p_person_id NUMBER,
390 p_business_group_id NUMBER)
391 is
392 --
393 l_status VARCHAR2(1);
394 l_legislation_code VARCHAR2(30);
395 l_nat_lbl VARCHAR2(2000);
396 local_warning EXCEPTION;
397
398 BEGIN
399 --
400 BEGIN
401 SELECT 'Y'
402 INTO l_status
403 FROM sys.dual
404 WHERE exists(SELECT '1'
405 FROM per_all_people_f pp
406 WHERE (p_person_id IS NULL
407 OR p_person_id <> pp.person_id)
408 AND p_identifier = pp.per_information2
409 AND pp.business_group_id = p_business_group_id);
410 --
411 IF l_status = 'Y' THEN
412 hr_utility.set_message(800, 'HR_HU_NI_UNIQUE_WARNING');
413 hr_utility.set_message_token('NI_NUMBER',hr_general.decode_lookup('HU_FORM_LABELS','TAX_ID_NO'));
414 hr_utility.raise_error;
415 END IF;
416 --
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN NULL;
419 END;
420 END check_tax_identifier_unique;
421
422
423 -----------------------------------------------------------------
424 END hr_hu_utility;