1 PACKAGE BODY hr_be_validation AS
2 /* $Header: hrbevali.pkb 120.0.12010000.2 2009/12/01 09:34:43 bkeshary ship $ */
3 --
4 --
5 -- This function validates that the bank account no is valid (see comments
6 -- within code for details of correct format and check digit calculation).
7 --
8 -- Its primary usage is within the bank account key flexfield definition
9 -- where it is used to drive some cross validation rules.
10 --
11 -- This function returns either:
12 --
13 -- Bank account no is OK - hr_be_validation.success
14 -- Bank account no is invalid - hr_be_validation.failure
15 --
16 FUNCTION bank_account_no
17 (p_bank_acc_no VARCHAR2) RETURN VARCHAR2 AS
18 --
19 --
20 -- Local exceptions.
21 --
22 invalid_bank_acc_no EXCEPTION;
23 --
24 --
25 -- Local variables.
26 --
27 l_temp_val NUMBER;
28 l_check_digits NUMBER;
29 l_10_digits NUMBER;
30 BEGIN
31 --
32 --
33 -- Ensure the format is NN-NNNNNNN-NN
34 --
35 IF INSTR(p_bank_acc_no, 'N') > 0 OR NVL(translate(p_bank_acc_no, '1234567890-', 'NNNNNNNNNN-'), 'ERROR') <> 'NNN-NNNNNNN-NN' THEN
36 RAISE invalid_bank_acc_no;
37 END IF;
38 --
39 --
40 -- Calculate the check digit using the following algorthmn -
41 --
42 -- 1. Take the first 10 digits and divide these by 97 (rounding the result).
43 -- 2. Multiply the result by 97.
44 -- 3. Subtract this number from ther original first 10 digits.
45 -- 4. This is the check digits NB. if < 10 need to front pad with a zero.
46 --
47 l_10_digits := TO_NUMBER(SUBSTR(p_bank_acc_no, 1, 3) || SUBSTR(p_bank_acc_no, 5, 7));
48 l_temp_val := TRUNC(l_10_digits / 97) * 97;
49 l_check_digits := l_10_digits - l_temp_val;
50 --
51 --
52 -- Compare the check digit with the calculated one.
53 --
54 IF LPAD(TO_CHAR(l_check_digits), 2, '0') <> SUBSTR(p_bank_acc_no, 13, 2) and l_check_digits <> 0 THEN
55 RAISE invalid_bank_acc_no;
56 END IF;
57 --
58 IF l_check_digits = 0 and SUBSTR(p_bank_acc_no, 13, 2) <> 97 then
59 RAISE invalid_bank_acc_no;
60 END IF;
61 --
62 -- Bank account no is OK.
63 --
64 RETURN success;
65 EXCEPTION
66 WHEN invalid_bank_acc_no THEN
67 --
68 --
69 -- Bank account no is incorrect.
70 --
71 RETURN failure;
72 END bank_account_no;
73
74 ----
75 -- Function added for IBAN Validation
76 ----
77 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
78 BEGIN
79 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
80 RETURN 1;
81 else
82 RETURN 0;
83 END IF;
84 END validate_iban_acc;
85
86 ----
87 -- This function will get called from the bank keyflex field segments Bug
88 ----
89 FUNCTION validate_account_entered
90 (p_acc_no IN VARCHAR2,
91 p_is_iban_acc IN varchar2 ) RETURN NUMBER IS
92 --
93 l_ret1 varchar2(20) ;
94 l_ret number;
95 begin
96 -- hr_utility.trace_on(null,'ACCVAL');
97 l_ret :=0;
98 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
99 hr_utility.set_location('p_account_number ' || p_acc_no,1);
100
101 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
102 l_ret1 := bank_account_no(p_acc_no);
103 hr_utility.set_location('l_ret1 ' || l_ret1,1);
104 if l_ret1 = 'SUCCESS' then
105 return 0;
106 else
107 RETURN 1;
108 end if;
109 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
110 l_ret := validate_iban_acc(p_acc_no);
111 hr_utility.set_location('l_ret ' || l_ret,3);
112 RETURN l_ret;
113 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
114 hr_utility.set_location('Both Account Nos Null',4);
115 RETURN 1;
116 ELSE
117 hr_utility.set_location('l_ret: 3 ' ,5);
118 RETURN 3;
119 END if;
120 End validate_account_entered;
121 --
122 END hr_be_validation;
[Home] [Help]