1 PACKAGE BODY hr_de_utility AS
2 /* $Header: hrdeutil.pkb 120.0.12010000.3 2009/12/16 10:50:18 bkeshary ship $ */
3 --
4 --
5 -- Formats the full name for the German legislation.
6 --
7 FUNCTION per_de_full_name
8 (p_first_name IN VARCHAR2
9 ,p_middle_names IN VARCHAR2
10 ,p_last_name IN VARCHAR2
11 ,p_known_as IN VARCHAR2
12 ,p_title IN VARCHAR2
13 ,p_suffix IN VARCHAR2
14 ,p_pre_name_adjunct IN VARCHAR2
15 ,p_per_information1 IN VARCHAR2
16 ,p_per_information2 IN VARCHAR2
17 ,p_per_information3 IN VARCHAR2
18 ,p_per_information4 IN VARCHAR2
19 ,p_per_information5 IN VARCHAR2
20 ,p_per_information6 IN VARCHAR2
21 ,p_per_information7 IN VARCHAR2
22 ,p_per_information8 IN VARCHAR2
23 ,p_per_information9 IN VARCHAR2
24 ,p_per_information10 IN VARCHAR2
25 ,p_per_information11 IN VARCHAR2
26 ,p_per_information12 IN VARCHAR2
27 ,p_per_information13 IN VARCHAR2
28 ,p_per_information14 IN VARCHAR2
29 ,p_per_information15 IN VARCHAR2
30 ,p_per_information16 IN VARCHAR2
31 ,p_per_information17 IN VARCHAR2
32 ,p_per_information18 IN VARCHAR2
33 ,p_per_information19 IN VARCHAR2
34 ,p_per_information20 IN VARCHAR2
35 ,p_per_information21 IN VARCHAR2
36 ,p_per_information22 IN VARCHAR2
37 ,p_per_information23 IN VARCHAR2
38 ,p_per_information24 IN VARCHAR2
39 ,p_per_information25 IN VARCHAR2
40 ,p_per_information26 IN VARCHAR2
41 ,p_per_information27 IN VARCHAR2
42 ,p_per_information28 IN VARCHAR2
43 ,p_per_information29 IN VARCHAR2
44 ,p_per_information30 in VARCHAR2) RETURN VARCHAR2 IS
45 --
46 --
47 -- Local variables
48 --
49 l_full_name varchar2(2000);
50 BEGIN
51 --
52 --
53 -- Construct the full name which has the following format:
54 --
55 -- <Last>, <First> <Prefix>
56 --
57 SELECT SUBSTR(LTRIM(RTRIM(p_last_name
58 || DECODE(p_first_name
59 ,null, ' '
60 ,', ' || p_first_name || ' ')
61 || DECODE(p_per_information10
62 ,null, ''
63 ,hr_general.decode_lookup('DE_PREFIX', p_per_information10))
64 )), 1, 240)
65 INTO l_full_name
66 FROM dual;
67 --
68 --
69 -- Return the full name.
70 --
71 RETURN (l_full_name);
72 END;
73 --
74 --
75 -- Formats the order name for the German legislation.
76 --
77 FUNCTION per_de_order_name
78 (p_first_name IN VARCHAR2
79 ,p_middle_names IN VARCHAR2
80 ,p_last_name IN VARCHAR2
81 ,p_known_as IN VARCHAR2
82 ,p_title IN VARCHAR2
83 ,p_suffix IN VARCHAR2
84 ,p_pre_name_adjunct IN VARCHAR2
85 ,p_per_information1 IN VARCHAR2
86 ,p_per_information2 IN VARCHAR2
87 ,p_per_information3 IN VARCHAR2
88 ,p_per_information4 IN VARCHAR2
89 ,p_per_information5 IN VARCHAR2
90 ,p_per_information6 IN VARCHAR2
91 ,p_per_information7 IN VARCHAR2
92 ,p_per_information8 IN VARCHAR2
93 ,p_per_information9 IN VARCHAR2
94 ,p_per_information10 IN VARCHAR2
95 ,p_per_information11 IN VARCHAR2
96 ,p_per_information12 IN VARCHAR2
97 ,p_per_information13 IN VARCHAR2
98 ,p_per_information14 IN VARCHAR2
99 ,p_per_information15 IN VARCHAR2
100 ,p_per_information16 IN VARCHAR2
101 ,p_per_information17 IN VARCHAR2
102 ,p_per_information18 IN VARCHAR2
103 ,p_per_information19 IN VARCHAR2
104 ,p_per_information20 IN VARCHAR2
105 ,p_per_information21 IN VARCHAR2
106 ,p_per_information22 IN VARCHAR2
107 ,p_per_information23 IN VARCHAR2
108 ,p_per_information24 IN VARCHAR2
109 ,p_per_information25 IN VARCHAR2
110 ,p_per_information26 IN VARCHAR2
111 ,p_per_information27 IN VARCHAR2
112 ,p_per_information28 IN VARCHAR2
113 ,p_per_information29 IN VARCHAR2
114 ,p_per_information30 IN VARCHAR2) RETURN VARCHAR2 IS
115 --
116 --
117 -- Local variables
118 --
119 l_order_name varchar2(2000) := NULL;
120 BEGIN
121 --
122 --
123 -- Return the order name.
124 --
125 RETURN (l_order_name);
126 END;
127
128 ----
129 -- Function added for IBAN Validation
130 ----
131 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
132
133 l_iban_ret_val NUMBER;
134 BEGIN
135 l_iban_ret_val := IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no);
136 hr_utility.set_location('IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) ' || l_iban_ret_val,99);
137 /* IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
138 RETURN 1;
139 else
140 RETURN 0;
141 END IF;
142 */
143 return l_iban_ret_val;
144 END validate_iban_acc;
145
146 ----
147 -- This function will get called from the bank keyflex field segments
148 ----
149 FUNCTION validate_account_entered
150 (p_acc_no IN VARCHAR2,
151 p_is_iban_acc IN varchar2 ,
152 p_iban_acc in varchar2 default null) RETURN NUMBER IS
153 --
154 l_ret NUMBER ;
155 begin
156 -- hr_utility.trace_on(null,'DEACCVAL');
157 l_ret :=0;
158 hr_utility.set_location('p_account_number ' || p_acc_no,1);
159 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
160 hr_utility.set_location('p_iban_acc ' || p_iban_acc,1);
161 -- hr_utility.set_location('p_bank_code ' || p_bank_code,1);
162
163 /* checking if either of the two account number are present , then return true*/
164
165 IF p_is_iban_acc = 'C' and (p_acc_no is not null or p_iban_acc is not null) then
166 hr_utility.set_location(' inside first if',1);
167 return 0;
168 end if;
169
170 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
171 l_ret:= 0;
172 hr_utility.set_location('l_ret ' || l_ret,2);
173
174 RETURN l_ret;
175
176 ELSIF (p_iban_acc IS NOT NULL AND p_is_iban_acc = 'Y') then
177 l_ret := validate_iban_acc(p_iban_acc);
178 hr_utility.set_location('l_ret ' || l_ret,4);
179 RETURN l_ret;
180
181 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
182 hr_utility.set_location('Both Account Nos Null',5);
183 RETURN 1;
184
185 ELSE
186 hr_utility.set_location('l_ret: 3 ' ,6);
187 RETURN 3;
188 END if;
189 End validate_account_entered;
190
191 --
192 END hr_de_utility;