[Home] [Help]
PACKAGE BODY: APPS.HR_NZ_BANK_ACCT_VALIDATION
Source
1 PACKAGE BODY hr_nz_bank_acct_validation AS
2 /* $Header: penzavbk.pkb 120.0.12010000.4 2008/08/06 09:17:42 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1999 Oracle Corporation Australia Ltd., *
7 * Brisbane, Australia. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation *
18 * Australia Ltd,. *
19 * *
20 ****************************************************************** */
21 /*
22 Name : hr_nz_bank_acct_validation (BODY)
23
24 Description : This package declares a function to validate
25 NZ bank accounts.
26
27 Change List
28 -----------
29
30 Version Date Author ER/CR No. Description of Change
31 -------+---------+----------+---------+--------------------------
32 110.0 14-May-99 pmcdonal Created
33 115.2 26-Jul-02 shoskatt 2466116 Validation of KiwiBank
34 115.3 26-Jul-02 shoskatt 2466116 Validation has been corrected
35 115.4 07-Aug-02 shoskatt 2501581 Cursor closed
36 115.5 20-Aug-02 nanuradh 2483154 KiwiBank(38) account no validation
37 115.6 21-Aug-02 nanuradh 2483154 Kiwibank Branch number validation
38 115.9 28-FEB-08 mdubasi 6819926 Branch Number Validations
39 ================================================================= */
40
41
42 ------------------------------------------------------------
43 -- Private Procedures
44 ------------------------------------------------------------
45
46
47 ------------------------------------------------------------
48 -- Private Functions
49 ------------------------------------------------------------
50
51
52 -- Function to validate the Bank/Account number for banks:
53 -- 01,02,03,06,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,30,35,38
54 -- Returns FALSE if Bank/Account checksum calculates correctly
55
56 FUNCTION validate_std (
57 p_bank_number IN VARCHAR2,
58 p_branch_number IN VARCHAR2,
59 p_account_number IN VARCHAR2,
60 p_account_suffix IN VARCHAR2
61 )
62 RETURN BOOLEAN IS
63 l_result NUMBER;
64 l_return BOOLEAN := TRUE;
65 BEGIN
66
67 IF (TO_NUMBER(p_account_number) > 990000 AND TO_NUMBER(p_account_number) < 99999999)
68 THEN
69 l_result := to_number(substr(p_account_number,3,1)) * 10 +
70 to_number(substr(p_account_number,4,1)) * 5 +
71 to_number(substr(p_account_number,5,1)) * 8 +
72 to_number(substr(p_account_number,6,1)) * 4 +
73 to_number(substr(p_account_number,7,1)) * 2 +
74 to_number(substr(p_account_number,8,1)) * 1;
75
76 IF (mod(l_result,11) = 0)
77 THEN
78 l_return := FALSE;
79 END IF;
80 ELSE
81 l_result := to_number(substr(p_branch_number, 1,1)) * 6 +
82 to_number(substr(p_branch_number, 2,1)) * 3 +
83 to_number(substr(p_branch_number, 3,1)) * 7 +
84 to_number(substr(p_branch_number, 4,1)) * 9 +
85 to_number(substr(p_account_number,3,1)) * 10 +
86 to_number(substr(p_account_number,4,1)) * 5 +
87 to_number(substr(p_account_number,5,1)) * 8 +
88 to_number(substr(p_account_number,6,1)) * 4 +
89 to_number(substr(p_account_number,7,1)) * 2 +
90 to_number(substr(p_account_number,8,1)) * 1;
91
92 IF (mod(l_result,11) = 0)
93 THEN
94 l_return := FALSE;
95 END IF;
96 END IF;
97 RETURN l_return;
98 END validate_std;
99
100
101 -- Function to validate the Bank/Account number for banks:
102 -- 08
103 -- Returns FALSE if Bank/Account checksum calculates correctly
104
105 FUNCTION validate_08 (
106 p_bank_number IN VARCHAR2,
107 p_branch_number IN VARCHAR2,
108 p_account_number IN VARCHAR2,
109 p_account_suffix IN VARCHAR2
110 )
111 RETURN BOOLEAN IS
112 l_result NUMBER;
113 l_return BOOLEAN := TRUE;
114
115 BEGIN
116 l_result := to_number(substr(p_account_number,2,1)) * 7 +
117 to_number(substr(p_account_number,3,1)) * 6 +
118 to_number(substr(p_account_number,4,1)) * 5 +
119 to_number(substr(p_account_number,5,1)) * 4 +
120 to_number(substr(p_account_number,6,1)) * 3 +
121 to_number(substr(p_account_number,7,1)) * 2 +
122 to_number(substr(p_account_number,8,1)) * 1;
123
124 IF (mod(l_result,11) = 0)
125 THEN
126 l_return := FALSE;
127 END IF;
128 RETURN l_return;
129 END validate_08;
130
131 -- Function to validate the Bank/Account number for banks:
132 -- 09
133 -- Returns FALSE if Bank/Account checksum calculates correctly
134
135 FUNCTION validate_09 (
136 p_bank_number IN VARCHAR2,
137 p_branch_number IN VARCHAR2,
138 p_account_number IN VARCHAR2,
139 p_account_suffix IN VARCHAR2
140 )
141 RETURN BOOLEAN IS
142 l_result NUMBER;
143 l_return BOOLEAN := TRUE;
144 l_acc NUMBER := 0;
145 BEGIN
146
147 l_result := to_number(substr(p_account_number,5,1)) * 5;
148 IF (l_result > 9)
149 THEN
150 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
151 END IF;
152 l_acc := l_acc + l_result;
153
154 l_result := to_number(substr(p_account_number,6,1)) * 4;
155 IF (l_result > 9)
156 THEN
157 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
158 END IF;
159 l_acc := l_acc + l_result;
160
161 l_result := to_number(substr(p_account_number,7,1)) * 3;
162 IF (l_result > 9)
163 THEN
164 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
165 END IF;
166 l_acc := l_acc + l_result;
167
168 l_result := to_number(substr(p_account_number,8,1)) * 2;
169 IF (l_result > 9)
170 THEN
171 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
172 END IF;
173 l_acc := l_acc + l_result;
174
175 l_result := to_number(substr(p_account_suffix,4,1)) * 1;
176 IF (l_result > 9)
177 THEN
178 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
179 END IF;
180 l_acc := l_acc + l_result;
181
182 IF (mod(l_acc,11) = 0)
183 THEN
184 l_return := FALSE;
185 END IF;
186 RETURN l_return;
187 END validate_09;
188
189 -- Function to validate the Bank/Account number for banks:
190 -- 25,33
191 -- Returns FALSE if Bank/Account checksum calculates correctly
192
193 FUNCTION validate_25_33 (
194 p_bank_number IN VARCHAR2,
195 p_branch_number IN VARCHAR2,
196 p_account_number IN VARCHAR2,
197 p_account_suffix IN VARCHAR2
198 )
199 RETURN BOOLEAN IS
200 l_result NUMBER;
201 l_return BOOLEAN := TRUE;
202 BEGIN
203 l_result := to_number(substr(p_account_number,2,1)) * 1 +
204 to_number(substr(p_account_number,3,1)) * 7 +
205 to_number(substr(p_account_number,4,1)) * 3 +
206 to_number(substr(p_account_number,5,1)) * 1 +
207 to_number(substr(p_account_number,6,1)) * 7 +
208 to_number(substr(p_account_number,7,1)) * 3 +
209 to_number(substr(p_account_number,8,1)) * 1;
210
211 IF (mod(l_result,10) = 0)
212 THEN
213 l_return := FALSE;
214 END IF;
215 RETURN l_return;
216 END validate_25_33;
217
218 -- Function to validate the Bank/Account number for banks:
219 -- 26,28 and 29
220 -- Returns FALSE if Bank/Account checksum calculates correctly
221
222 FUNCTION validate_29 (
223 p_bank_number IN VARCHAR2,
224 p_branch_number IN VARCHAR2,
225 p_account_number IN VARCHAR2,
226 p_account_suffix IN VARCHAR2
227 )
228 RETURN BOOLEAN IS
229 l_result NUMBER;
230 l_return BOOLEAN := TRUE;
231 l_acc NUMBER := 0;
232 BEGIN
233 -- First Digit of the Account Number
234 l_result := to_number(substr(p_account_number,2,1)) * 1;
235 IF (l_result > 9) /* Iteration 1 */
236 THEN
237 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
238 IF (l_result > 9) /* Iteration 2 */
239 THEN
240 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
241 END IF;
242 END IF;
243 l_acc := l_acc + l_result;
244
245 -- Second Digit of the Account Number
246 l_result := to_number(substr(p_account_number,3,1)) * 3;
247 IF (l_result > 9) /* Iteration 1 */
248 THEN
249 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
250 IF (l_result > 9) /* Iteration 2 */
251 THEN
252 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
253 END IF;
254 END IF;
255 l_acc := l_acc + l_result;
256
257 -- Third Digit of the Account Number
258 l_result := to_number(substr(p_account_number,4,1)) * 7;
259 IF (l_result > 9) /* Iteration 1 */
260 THEN
261 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
262 IF (l_result > 9) /* Iteration 2 */
263 THEN
264 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
265 END IF;
266 END IF;
267 l_acc := l_acc + l_result;
268
269 -- Fourth Digit of the Account Number
270 l_result := to_number(substr(p_account_number,5,1)) * 1;
271 IF (l_result > 9) /* Iteration 1 */
272 THEN
273 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
274 IF (l_result > 9) /* Iteration 2 */
275 THEN
276 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
277 END IF;
278 END IF;
279 l_acc := l_acc + l_result;
280
281 -- Fifth Digit of the Account Number
282 l_result := to_number(substr(p_account_number,6,1)) * 3;
283 IF (l_result > 9) /* Iteration 1 */
284 THEN
285 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
286 IF (l_result > 9) /* Iteration 2 */
287 THEN
288 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
289 END IF;
290 END IF;
291 l_acc := l_acc + l_result;
292
293 -- Sixth Digit of the Account Number
294 l_result := to_number(substr(p_account_number,7,1)) * 7;
295 IF (l_result > 9) /* Iteration 1 */
296 THEN
297 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
298 IF (l_result > 9) /* Iteration 2 */
299 THEN
300 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
301 END IF;
302 END IF;
303 l_acc := l_acc + l_result;
304
305 -- Seventh Digit of the Account Number
306 l_result := to_number(substr(p_account_number,8,1)) * 1;
307 IF (l_result > 9) /* Iteration 1 */
308 THEN
309 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
310 IF (l_result > 9) /* Iteration 2 */
311 THEN
312 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
313 END IF;
314 END IF;
315 l_acc := l_acc + l_result;
316
317 -- First Digit of the Suffix
318 l_result := to_number(substr(p_account_suffix,2,1)) * 3;
319 IF (l_result > 9) /* Iteration 1 */
320 THEN
321 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
322 IF (l_result > 9) /* Iteration 2 */
323 THEN
324 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
325 END IF;
326 END IF;
327 l_acc := l_acc + l_result;
328
329 -- Second Digit of the Suffix
330 l_result := to_number(substr(p_account_suffix,3,1)) * 7;
331 IF (l_result > 9) /* Iteration 1 */
332 THEN
333 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
334 IF (l_result > 9) /* Iteration 2 */
335 THEN
336 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
337 END IF;
338 END IF;
339 l_acc := l_acc + l_result;
340
341 -- Third Digit of the Suffix
342 l_result := to_number(substr(p_account_suffix,4,1)) * 1;
343 IF (l_result > 9) /* Iteration 1 */
344 THEN
345 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
346 IF (l_result > 9) /* Iteration 1 */
347 THEN
348 l_result := to_number(substr(to_char(l_result),1,1)) + to_number(substr(to_char(l_result),2,1));
349 END IF;
350 END IF;
351 l_acc := l_acc + l_result;
352
353 IF (mod(l_acc,10) = 0)
354 THEN
355 l_return := FALSE;
356 END IF;
357 RETURN l_return;
358 END validate_29;
359
360 /* Bug 6819926 starts */
361
362 -- Function to validate the Branch numbers for all the banks
363
364 -- Returns FALSE if Branch Number is valid for the respective Bank IDS
365
366 FUNCTION validate_branch(p_bank_no IN VARCHAR2,p_branch_no IN VARCHAR2)
367 RETURN BOOLEAN is
368 l_return boolean := TRUE;
369 BEGIN
370 IF(p_bank_no = '01')
371 THEN
372 IF(((to_number(p_branch_no) >= 0001) and (to_number(p_branch_no) <= 0999)) or
373 ((to_number(p_branch_no) >= 1100) and (to_number(p_branch_no) <= 1199)) or
374 ((to_number(p_branch_no) >= 1800) and (to_number(p_branch_no) <= 1899)) )
375 THEN
376 l_return := FALSE;
377 END IF;
378 ELSIF (p_bank_no = '02')
379 THEN
380 IF (((to_number(p_branch_no) >= 0001) and (to_number(p_branch_no) <= 0999)) or
381 ((to_number(p_branch_no) >= 1200) and (to_number(p_branch_no) <= 1299)) )
382 THEN
383 l_return := FALSE;
384 END IF;
385 ELSIF (p_bank_no = '03')
386 THEN
387 IF(((to_number(p_branch_no) >= 0001) and (to_number(p_branch_no) <= 0999)) or
388 ((to_number(p_branch_no) >= 1300) and (to_number(p_branch_no) <= 1399)) or
389 ((to_number(p_branch_no) >= 1500) and (to_number(p_branch_no) <= 1599)) or
390 ((to_number(p_branch_no) >= 1700) and (to_number(p_branch_no) <= 1799)))
391 THEN
392 l_return := FALSE;
393 END IF;
394 ELSIF (p_bank_no = '06')
395 THEN
396 IF(((to_number(p_branch_no) >= 0001) and (to_number(p_branch_no) <= 0999)) or
397 ((to_number(p_branch_no) >= 1400) and (to_number(p_branch_no) <= 1499)))
398 THEN
399 l_return := FALSE;
400 END IF;
401 ELSIF (p_bank_no = '08')
402 THEN
403 IF((to_number(p_branch_no) >= 6500) and (to_number(p_branch_no) <= 6599))
404 THEN
405 l_return := FALSE;
406 END IF;
407 ELSIF(p_bank_no = '09')
408 THEN
409 IF(to_number(p_branch_no)=0000)
410 THEN
411 l_return := FALSE;
412 END IF;
413 ELSIF(p_bank_no='11')
414 THEN
415 IF(((to_number(p_branch_no) >= 5000) and (to_number(p_branch_no) <= 6499)) or
416 ((to_number(p_branch_no) >= 6600) and (to_number(p_branch_no) <= 8999)))
417 THEN
418 l_return := FALSE;
419 END IF;
420 ELSIF(p_bank_no = '12')
421 THEN
422 IF(((to_number(p_branch_no) >= 3000) and (to_number(p_branch_no) <= 3299)) or
423 ((to_number(p_branch_no) >= 3400) and (to_number(p_branch_no) <= 3499)) or
424 ((to_number(p_branch_no) >= 3600) and (to_number(p_branch_no) <= 3699)) )
425 THEN
426 l_return := FALSE;
427 END IF;
428 ELSIF(p_bank_no = '13')
429 THEN
430 IF((to_number(p_branch_no) >= 4900) and (to_number(p_branch_no) <= 4999))
431 THEN
432 l_return := FALSE;
433 END IF;
434 ELSIF(p_bank_no = '14')
435 THEN
436 IF((to_number(p_branch_no) >= 4700) and (to_number(p_branch_no) <= 4799))
437 THEN
438 l_return := FALSE;
439 END IF;
440 ELSIF(p_bank_no = '15')
441 THEN
442 IF((to_number(p_branch_no) >= 3900) and (to_number(p_branch_no) <= 3999))
443 THEN
444 l_return := FALSE;
445 END IF;
446 ELSIF(p_bank_no = '16')
447 THEN
448 IF((to_number(p_branch_no) >= 4400) and (to_number(p_branch_no) <= 4499))
449 THEN
450 l_return := FALSE;
451 END IF;
452 ELSIF(p_bank_no = '17')
453 THEN
454 IF((to_number(p_branch_no) >= 3300) and (to_number(p_branch_no) <= 3399))
455 THEN
456 l_return := FALSE;
457 END IF;
458 ELSIF(p_bank_no = '18')
459 THEN
460 IF((to_number(p_branch_no) >= 3500) and (to_number(p_branch_no) <= 3599))
461 THEN
462 l_return := FALSE;
463 END IF;
464 ELSIF(p_bank_no = '19')
465 THEN
466 IF((to_number(p_branch_no) >= 4600) and (to_number(p_branch_no) <= 4649))
467 THEN
468 l_return := FALSE;
469 END IF;
470 ELSIF(p_bank_no = '20')
471 THEN
472 IF((to_number(p_branch_no) >= 4100) and (to_number(p_branch_no) <= 4199))
473 THEN
474 l_return := FALSE;
475 END IF;
476 ELSIF(p_bank_no = '21')
477 THEN
478 IF((to_number(p_branch_no) >= 4800) and (to_number(p_branch_no) <= 4899))
479 THEN
480 l_return := FALSE;
481 END IF;
482 ELSIF(p_bank_no = '22')
483 THEN
484 IF((to_number(p_branch_no) >= 4000) and (to_number(p_branch_no) <= 4049))
485 THEN
486 l_return := FALSE;
487 END IF;
488 ELSIF(p_bank_no = '23')
489 THEN
490 IF((to_number(p_branch_no) >= 3700) and (to_number(p_branch_no) <= 3799))
491 THEN
492 l_return := FALSE;
493 END IF;
494 ELSIF(p_bank_no = '24')
495 THEN
496 IF((to_number(p_branch_no) >= 4300) and (to_number(p_branch_no) <= 4349))
497 THEN
498 l_return := FALSE;
499 END IF;
500 ELSIF(p_bank_no = '25')
501 THEN
502 IF((to_number(p_branch_no) >= 2500) and (to_number(p_branch_no) <= 2599))
503 THEN
504 l_return := FALSE;
505 END IF;
506 ELSIF(p_bank_no = '26')
507 THEN
508 IF((to_number(p_branch_no) >= 2600) and (to_number(p_branch_no) <= 2699))
509 THEN
510 l_return := FALSE;
511 END IF;
512 ELSIF(p_bank_no = '27')
513 THEN
514 IF((to_number(p_branch_no) >= 3800) and (to_number(p_branch_no) <= 3849))
515 THEN
516 l_return := FALSE;
517 END IF;
518 ELSIF(p_bank_no = '28')
519 THEN
520 IF((to_number(p_branch_no) >= 2100) and (to_number(p_branch_no) <= 2149))
521 THEN
522 l_return := FALSE;
523 END IF;
524 ELSIF(p_bank_no = '29')
525 THEN
526 IF((to_number(p_branch_no) >= 2150) and (to_number(p_branch_no) <= 2299))
527 THEN
528 l_return := FALSE;
529 END IF;
530 ELSIF(p_bank_no ='30')
531 THEN
532 IF((to_number(p_branch_no) >= 2900) and (to_number(p_branch_no) <= 2949))
533 THEN
534 l_return := FALSE;
535 END IF;
536 ELSIF(p_bank_no ='31')
537 THEN
538 IF((to_number(p_branch_no) >= 2800) and (to_number(p_branch_no) <= 2849))
539 THEN
540 l_return := FALSE;
541 END IF;
542 ELSIF(p_bank_no = '33')
543 THEN
544 IF((to_number(p_branch_no) >= 6700) and (to_number(p_branch_no) <= 6799))
545 THEN
546 l_return := FALSE;
547 END IF;
548 ELSIF(p_bank_no = '35')
549 THEN
550 IF((to_number(p_branch_no) >= 2400) and (to_number(p_branch_no) <= 2499))
551 THEN
552 l_return := FALSE;
553 END IF;
554 ELSIF(p_bank_no = '38')
555 THEN
556 IF((to_number(p_branch_no) >= 9000) and (to_number(p_branch_no) <= 9499))
557 THEN
558 l_return := FALSE;
559 END IF;
560 END IF;
561 RETURN l_return;
562 END validate_branch;
563
564
565
566 ------------------------------------------------------------
567 -- Private Cursors
568 ------------------------------------------------------------
569
570
571
572
573 ------------------------------------------------------------
574 -- Public Functions
575 ------------------------------------------------------------
576
577 -- Main Entry point to the Bank/Account number validation function
578 -- Calculates the checksums for all known NZ bank/account combinations
579 -- Returns TRUE if Bank/Account checksum calculates correctly
580
581 FUNCTION validate_acct (
582 p_bank_branch_number IN VARCHAR2,
583 p_account_number IN VARCHAR2,
584 p_account_suffix IN VARCHAR2
585 )
586 RETURN VARCHAR2 IS
587
588 validation_failed EXCEPTION;
589
590 CURSOR bank_cursor(p_bank_no VARCHAR2) IS
591 SELECT MEANING
592 FROM HR_LOOKUPS
593 WHERE LOOKUP_TYPE = 'NZ_BANK' AND
594 LOOKUP_CODE = p_bank_no AND
595 ENABLED_FLAG = 'Y';
596
597 l_bank_no VARCHAR2(2);
598 l_bank_name VARCHAR2(80);
599 l_branch_no VARCHAR2(6);
600 l_acct_no VARCHAR2(8);
601 l_acct_suffix VARCHAR2(4);
602 l_return VARCHAR2(5) := 'FALSE';
603
604 BEGIN
605
606 -- Check for valid parameters
607
608 l_bank_no := substr(p_bank_branch_number,1,2);
609 /*Bug 6819926*/
610 --To pad the branch number,account number and suffix with zeros
611 l_branch_no := lpad(substr(p_bank_branch_number,3),4,0);
612 l_acct_no := lpad(p_account_number,8,0);
613 l_acct_suffix := lpad(p_account_suffix,4,0);
614
615
616 OPEN bank_cursor (l_bank_no);
617 FETCH bank_cursor INTO l_bank_name;
618 IF (bank_cursor%NOTFOUND)
619 THEN
620 RAISE validation_failed;
621 END IF;
622 CLOSE bank_cursor; /* Bug 2501851 */
623
624
625 --Validating Branch Number
626 /*Bug 6819926*/
627 IF(validate_branch(l_bank_no,l_branch_no))
628 THEN
629 RAISE validation_failed;
630 END IF;
631
632
633
634 IF (p_account_suffix is NULL)
635 THEN
636 RAISE validation_failed;
637 END IF;
638
639 -- These accounts are always valid
640
641 IF (to_number(l_acct_no) NOT IN ('0998800','0998907','0999993','9999977','9999985','9999993','9999999'))
642 THEN
643
644
645 -- Select the correct validation routine for the bank
646
647 IF (l_bank_no = '08')
648 THEN
649 IF (validate_08(l_bank_no, l_branch_no, l_acct_no, p_account_suffix))
650 THEN
651 RAISE validation_failed;
652 END IF;
653 ELSIF (l_bank_no = '09')
654 THEN
655 IF (validate_09(l_bank_no, l_branch_no, l_acct_no, p_account_suffix))
656 THEN
657 RAISE validation_failed;
658 END IF;
659 ELSIF (l_bank_no = '25' OR l_bank_no = '33')
660 THEN
661 IF (validate_25_33(l_bank_no, l_branch_no, l_acct_no, p_account_suffix))
662 THEN
663 RAISE validation_failed;
664 END IF;
665 ELSIF (l_bank_no = '26' OR l_bank_no = '28' OR l_bank_no = '29') /*Bug 6819926*/
666 THEN
667 IF (validate_29(l_bank_no, l_branch_no, l_acct_no, p_account_suffix))
668 THEN
669 RAISE validation_failed;
670 END IF;
671 ELSIF (l_bank_no = '31') /*Bug 6819926*/
672 THEN
673 l_return := 'TRUE';
674
675 ELSE
676 IF (validate_std(l_bank_no, l_branch_no, l_acct_no, p_account_suffix))
677 THEN
678 RAISE validation_failed;
679 END IF;
680 END IF;
681 END IF;
682
683 -- Must have passed all rules
684
685 l_return := 'TRUE';
686 RETURN l_return;
687
688 EXCEPTION
689 WHEN validation_failed THEN
690 RETURN l_return;
691 WHEN others THEN
692 RETURN l_return;
693 END validate_acct;
694
695 END hr_nz_bank_acct_validation;