1 PACKAGE BODY hr_de_extra_org_checks AS
2 /* $Header: hrdeorgv.pkb 120.0.12000000.2 2007/02/28 10:03:42 spendhar ship $ */
3 --
4 --
5 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
6 -- Service function to return one of two values based on the result of an
7 -- expression. If the expression is true then the first value is returned
8 -- otherwise the second value is returned.
9 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
10 --
11 FUNCTION decode
12 (p_expr IN BOOLEAN
13 ,p_val1 IN NUMBER
14 ,p_val2 IN NUMBER) RETURN NUMBER IS
15 BEGIN
16 IF p_expr THEN
17 RETURN p_val1;
18 ELSE
19 RETURN p_val2;
20 END IF;
21 END decode;
22 --
23 --
24 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
25 -- Service function to validate the betriebsnummer according to the following rules.
26 --
27 -- Format is xxxyyyyz (all numeric) where...
28 --
29 -- xxx is the ID from the unemployment office. Currently allowed values are 010
30 -- to 099 or it must be larger than 110.
31 --
32 -- yyyy is a sequential number issued by unemployment office.
33 --
34 -- z is a check digit.
35 --
36 -- NB. the message name for the error to be raised is passed in as there is more than
37 -- one betriebsnummer.
38 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
39 --
40 PROCEDURE validate_betriebsnummer
41 (p_betriebsnummer IN VARCHAR2
42 ,p_message_name IN VARCHAR2) IS
43 --
44 --
45 -- Local exceptions
46 --
47 invalid_format EXCEPTION;
48 --
49 --
50 -- Local variables.
51 --
52 l_dummy VARCHAR2(2000);
53 l_betriebsnummer VARCHAR2(240) := p_betriebsnummer;
54 l_id_num NUMBER;
55 l_seq_num NUMBER;
56 l_check_digit NUMBER;
57 l_temp_value NUMBER := 0;
58 l_temp_sum NUMBER := 0;
59 l_temp_last_digit NUMBER := 0;
60 BEGIN
61 --
62 --
63 -- Must be an integer.
64 --
65 BEGIN
66 hr_chkfmt.checkformat
67 (value => l_betriebsnummer
68 ,format => 'I'
69 ,output => l_dummy
70 ,minimum => null
71 ,maximum => null
72 ,nullok => 'N'
73 ,rgeflg => l_dummy
74 ,curcode => null);
75 EXCEPTION
76 WHEN OTHERS THEN
77 RAISE invalid_format;
78 END;
79 --
80 --
81 -- Must be 8 characters in length.
82 --
83 IF LENGTH(p_betriebsnummer) <> 8 THEN
84 RAISE invalid_format;
85 END IF;
86 --
87 --
88 -- Extract the 3 components and convert them to numbers.
89 --
90 l_id_num := TO_NUMBER(SUBSTR(p_betriebsnummer, 1, 3));
91 l_seq_num := TO_NUMBER(SUBSTR(p_betriebsnummer, 4, 4));
92 l_check_digit := TO_NUMBER(SUBSTR(p_betriebsnummer, 8, 1));
93 --
94 --
95 -- ID number validation (xxx).
96 --
97 IF NOT ((l_id_num >= 10 AND l_id_num <= 99) OR l_id_num > 110) THEN
98 RAISE invalid_format;
99 END IF;
100 --
101 --
102 -- Sequential number validation (yyyy).
103 --
104 -- IF NOT (l_seq_num > 0) THEN
105 -- RAISE invalid_format;
106 -- END IF;
107 --
108 --
109 -- Check digit validation (z).
110 --
111 -- Process the first 7 digits of the betriebsnummer to calculate the sum from
112 -- which the check digit is derived.
113 --
114 FOR i IN 1..7 LOOP
115 --
116 --
117 -- Each odd digit is multiplied by 1 and even digit by 2.
118 --
119 l_temp_value := TO_NUMBER(SUBSTR(p_betriebsnummer, i, 1)) * (MOD(i - 1, 2) + 1);
120 --
121 --
122 -- If the result is greater than 9 then add together the individual digits e.g.
123 --
124 -- 18 -> 1 + 8 -> 9
125 --
126 IF l_temp_value > 9 THEN
127 l_temp_value := TO_NUMBER(SUBSTR(TO_CHAR(l_temp_value), 1, 1)) + TO_NUMBER(SUBSTR(TO_CHAR(l_temp_value), 2, 1));
128 END IF;
129 --
130 --
131 -- Sum all the individual values. This produces the number used to derive the check digit.
132 --
133 l_temp_sum := l_temp_sum + l_temp_value;
134 END LOOP;
135 --
136 --
137 -- Get the last digit of the check digit sum.
138 --
139 l_temp_last_digit := MOD(l_temp_sum, 10);
140 --
141 --
142 -- Format is valid.
143 --
144 IF NOT(l_check_digit = l_temp_last_digit
145 OR l_check_digit = decode(l_temp_last_digit > 4, l_temp_last_digit - 5, l_temp_last_digit + 5)) THEN
146 RAISE invalid_format;
147 END IF;
148 EXCEPTION
149 --
150 --
151 -- Format is invalid.
152 --
153 WHEN invalid_format THEN
154 hr_utility.set_message(800, p_message_name);
155 hr_utility.raise_error;
156 END validate_betriebsnummer;
157 --
158 --
159 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
160 -- Organization information checks.
161 --
162 -- - German HR organization information (DE_HR_ORG_INFO).
163 --
164 -- 1. Payroll betriebsnummer and Employers betriebsnummer (p_org_information1 and 2)
165 -- must conform to a prescribed format (see service function validate_betriebsnummer()
166 -- for details of the rules).
167 --
168 -- - German social insurance information (DE_SOCIAL_INSURANCE_INFO).
169 --
170 -- 1. West betriebsnummer and East betriebsnummer (p_org_information1 and 2)
171 -- must conform to a prescribed format (see service function validate_betriebsnummer()
172 -- for details of the rules).
173 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
174 --
175 PROCEDURE org_information_checks
176 (p_org_info_type_code IN VARCHAR2
177 ,p_org_information1 IN VARCHAR2
178 ,p_org_information2 IN VARCHAR2) IS
179 BEGIN
180 --
181 --
182 -- Check if DE is installed
183 IF hr_utility.chk_product_install('Oracle Human Resources', 'DE') THEN
184
185 -- German HR organization information validation.
186 --
187 IF p_org_info_type_code = 'DE_HR_ORG_INFO' THEN
188 --
189 --
190 -- Validate the Employers betriebsnummer NB. passing the error message to be raised.
191 --
192 IF p_org_information1 IS NOT null THEN
193 validate_betriebsnummer(p_org_information1, 'HR_DE_INVL_EMPLOYER_BETRIEBESN');
194 END IF;
195 --
196 --
197 -- Validate the Payroll betriebsnummer NB. passing the error message to be raised.
198 --
199 IF p_org_information2 IS NOT null THEN
200 validate_betriebsnummer(p_org_information2, 'HR_DE_INVL_PAYROLL_BETRIEBESN');
201 END IF;
202 --
203 --
204 -- German social insurance information validation.
205 --
206 ELSIF p_org_info_type_code = 'DE_SOCIAL_INSURANCE_INFO' THEN
207 --
208 --
209 -- Validate the West betriebsnummer NB. passing the error message to be raised.
210 --
211 IF p_org_information1 IS NOT null THEN
212 validate_betriebsnummer(p_org_information1, 'HR_DE_INVL_WEST_BETRIEBESN');
213 END IF;
214 --
215 --
216 -- Validate the East betriebsnummer NB. passing the error message to be raised.
217 --
218 IF p_org_information2 IS NOT null THEN
219 validate_betriebsnummer(p_org_information2, 'HR_DE_INVL_EAST_BETRIEBESN');
220 END IF;
221 END IF;
222
223 END IF;
224 END org_information_checks;
225 END hr_de_extra_org_checks;
[Home] [Help]