1 PACKAGE BODY per_nl_bank_details_pkg AS
2 -- $Header: penlbank.pkb 120.0.12010000.2 2009/11/27 09:49:44 dchindar ship $
3 --
4 -- Validates the bank account number
5 --
6 FUNCTION validate_account_number
7 ( accno IN VARCHAR2) RETURN NUMBER IS
8
9 a number := 0;
10 b number := 0;
11 c number := 0;
12 d number := 0;
13 e number := 0;
14 f number := 0;
15 g number := 0;
16 h number := 0;
17 i number := 0;
18 j number := 0;
19 ii varchar2(1);
20 a_total number := 0;
21 a_rem number := 0;
22 a_diff number := 0;
23 acno number := 0;
24 begin
25 -- Check for length of the account number
26 -- If Account number starts with 'P' followed by up to 8
27 -- numbers, then account is valid, no validations are
28 -- performed.
29
30 if length(accno) >1 then
31 ii := substr(accno,1,1);
32 else
33 ii := '';
34 end if;
35
36 if length(accno) >1 and length(accno) <= 9 and upper(ii) = 'P' then
37 acno := to_number(substr(accno,2));
38 a_rem := length(substr(accno,2));
39 if (a_rem > 8) or (acno = 0) or
40 (substr(accno,2,1) = ' ') then
41 return 1;
42 else
43 return 0;
44 end if;
45
46 -- If Account Number is nine characters long, perform the weighted
47 -- average calculations.
48
49 elsif length(accno) = 9 then
50 acno := to_number(accno);
51 a := to_number(substr(acno,1,1));
52 b := to_number(substr(acno,2,1));
53 c := to_number(substr(acno,3,1));
54 d := to_number(substr(acno,4,1));
55 e := to_number(substr(acno,5,1));
56 f := to_number(substr(acno,6,1));
57 g := to_number(substr(acno,7,1));
58 h := to_number(substr(acno,8,1));
59 i := to_number(substr(acno,9,1));
60 a_total := (a*9) + (b*8) + (c*7) + (d*6) + (e*5) + (f*4) + (g*3) + (h*2);
61 a_rem := (a_total - 11*floor(a_total/11));
62 if a_rem = 0 then
63 a_diff := 0;
64 else
65 a_diff := 11 - a_rem;
66 end if;
67 if a_diff = i then
68 return 0;
69 else
70 return 1;
71 end if;
72
73 -- If Account Number is ten characters long
74
75 elsif length(accno) = 10 then
76 --acno := to_number(accno);
77 a := to_number(substr(accno,1,1));
78 b := to_number(substr(accno,2,1));
79 c := to_number(substr(accno,3,1));
80 d := to_number(substr(accno,4,1));
81 e := to_number(substr(accno,5,1));
82 f := to_number(substr(accno,6,1));
83 g := to_number(substr(accno,7,1));
84 h := to_number(substr(accno,8,1));
85 i := to_number(substr(accno,9,1));
86 j := to_number(substr(accno,10,1));
87 a_total := (a*10) + (b*9) + (c*8) + (d*7) + (e*6) + (f*5) + (g*4) + (h*3) + (i*2);
88 a_rem := (a_total - 11*floor(a_total/11));
89 if a_rem = 0 then
90 a_diff := 0;
91 else
92 a_diff := 11 - a_rem;
93 end if;
94 if a_diff = j then
95 return 0;
96 else
97 return 1;
98 end if;
99
100 else
101 return 1;
102 end if;
103
104 EXCEPTION
105 when INVALID_NUMBER then
106 return 1;
107 when VALUE_ERROR then
108 return 1;
109
110 end validate_account_number;
111
112 ----
113 -- Function added for IBAN Validation
114 ----
115 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
116 BEGIN
117 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
118 RETURN 1;
119 else
120 RETURN 0;
121 END IF;
122 END validate_iban_acc;
123
124 ----
125 -- This function will get called from the bank keyflex field segments
126 ----
127 FUNCTION validate_account_entered
128 (p_acc_no IN VARCHAR2,
129 p_is_iban_acc IN varchar2 ) RETURN NUMBER IS
130 --
131 l_ret NUMBER ;
132 begin
133 -- hr_utility.trace_on(null,'ACCVAL');
134 l_ret :=0;
135 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
136 hr_utility.set_location('p_account_number ' || p_acc_no,1);
137
138 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
139 l_ret := validate_account_number(p_acc_no);
140 hr_utility.set_location('l_ret ' || l_ret,1);
141 RETURN l_ret;
142 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
143 l_ret := validate_iban_acc(p_acc_no);
144 hr_utility.set_location('l_ret ' || l_ret,3);
145 RETURN l_ret;
146 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
147 hr_utility.set_location('Both Account Nos Null',4);
148 RETURN 1;
149 ELSE
150 hr_utility.set_location('l_ret: 3 ' ,5);
151 RETURN 3;
152 END if;
153 End validate_account_entered;
154
155 end per_nl_bank_details_pkg;
[Home] [Help]