1 PACKAGE BODY hr_de_extra_api_checks AS
2 /* $Header: pedehkvl.pkb 115.8 2002/01/03 07:35:35 pkm 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 -- Service function to validate the SCL according to the following rules.
161 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
162 --
163 PROCEDURE validate_scl
164 (p_organization_id IN NUMBER
165 ,p_exempt_flag IN VARCHAR2
166 ,p_liability_prov IN VARCHAR2
167 ,p_class_of_risk IN VARCHAR2) IS
168 --
169 --
170 -- Local variables.
171 --
172 l_dummy VARCHAR2(2000);
173 BEGIN
174 null;
175 END validate_scl;
176 --
177 --
178 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
179 -- Assignment checks.
180 --
181 -- 1. Union Membership cannot be recorded.
182 --
183 -- 2. The information held in the SCL is valid (see service function validate_scl()
184 -- for details of the rules. To be implemented!
185 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
186 --
187 PROCEDURE assignment_checks
188 (p_labour_union_member_flag IN VARCHAR2) IS
189 BEGIN
190 --
191 --
192 -- Check that the union member flag has not been set.
193 --
194 If p_labour_union_member_flag IS NOT NULL THEN
195 hr_utility.set_message(800, 'HR_DE_INVALID_UNION_FLAG');
196 hr_utility.raise_error;
197 END IF;
198 --
199 --
200 -- Check that the SCL is valid.
201 --
202 validate_scl
203 (p_organization_id => null -- p_organization_id
204 ,p_exempt_flag => null -- p_segment2
205 ,p_liability_prov => null -- p_segment3
206 ,p_class_of_risk => null); -- p_segment4);
207 END assignment_checks;
208 --
209 --
210 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
211 -- Organization information checks.
212 --
213 -- - German HR organization information (DE_HR_ORG_INFO).
214 --
215 -- 1. Payroll betriebsnummer and Employers betriebsnummer (p_org_information1 and 2)
216 -- must conform to a prescribed format (see service function validate_betriebsnummer()
217 -- for details of the rules).
218 --
219 -- - German social insurance information (DE_SOCIAL_INSURANCE_INFO).
220 --
221 -- 1. West betriebsnummer and East betriebsnummer (p_org_information1 and 2)
222 -- must conform to a prescribed format (see service function validate_betriebsnummer()
223 -- for details of the rules).
224 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
225 --
226 PROCEDURE org_information_checks
227 (p_org_info_type_code IN VARCHAR2
228 ,p_org_information1 IN VARCHAR2
229 ,p_org_information2 IN VARCHAR2) IS
230 BEGIN
231 --
232 --
233 -- German HR organization information validation.
234 --
235 IF p_org_info_type_code = 'DE_HR_ORG_INFO' THEN
236 --
237 --
238 -- Validate the Employers betriebsnummer NB. passing the error message to be raised.
239 --
240 IF p_org_information1 IS NOT null THEN
241 validate_betriebsnummer(p_org_information1, 'HR_DE_INVL_EMPLOYER_BETRIEBESN');
242 END IF;
243 --
244 --
245 -- Validate the Payroll betriebsnummer NB. passing the error message to be raised.
246 --
247 IF p_org_information2 IS NOT null THEN
248 validate_betriebsnummer(p_org_information2, 'HR_DE_INVL_PAYROLL_BETRIEBESN');
249 END IF;
250 --
251 --
252 -- German social insurance information validation.
253 --
254 ELSIF p_org_info_type_code = 'DE_SOCIAL_INSURANCE_INFO' THEN
255 --
256 --
257 -- Validate the West betriebsnummer NB. passing the error message to be raised.
258 --
259 IF p_org_information1 IS NOT null THEN
260 validate_betriebsnummer(p_org_information1, 'HR_DE_INVL_WEST_BETRIEBESN');
261 END IF;
262 --
263 --
264 -- Validate the East betriebsnummer NB. passing the error message to be raised.
265 --
266 IF p_org_information2 IS NOT null THEN
267 validate_betriebsnummer(p_org_information2, 'HR_DE_INVL_EAST_BETRIEBESN');
268 END IF;
269 END IF;
270 END org_information_checks;
271 --
272 --
273 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
274 -- Person Extra Information checks.
275 --
276 -- - Military Service (DE_MILITARY_SERVICE).
277 --
278 -- 1. The Date From (p_pei_information1) must be on or before Date To (p_pei_information2).
279 --
280 -- - Residence Permit (DE_RESIDENCE_PERMITS).
281 --
282 -- 1. The Effective Date (p_pei_information6) must be on or before Expiry Date
286 --
283 -- (p_pei_information7).
284 --
285 -- - Work Permit (DE_WORK_PERMITS).
287 -- 1. The Effective Date (p_pei_information6) must be on or before Expiry Date
288 -- (p_pei_information7).
289 --
290 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
291 --
292 PROCEDURE person_information_checks
293 (p_pei_information_category IN VARCHAR2
294 ,p_pei_information1 IN VARCHAR2
295 ,p_pei_information2 IN VARCHAR2
296 ,p_pei_information6 IN VARCHAR2
297 ,p_pei_information7 IN VARCHAR2) IS
298 --
299 --
300 -- Local exceptions.
301 --
302 military_service_dates EXCEPTION;
303 permit_dates EXCEPTION;
304 --
305 --
306 -- Local variables.
307 --
308 l_lower_date DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
309 l_upper_date DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
310 BEGIN
311 --
312 --
313 -- Military Service validation.
314 --
315 IF p_pei_information_category = 'DE_MILITARY_SERVICE' THEN
316 --
317 --
318 -- Convert parameters to dates.
319 --
320 IF p_pei_information1 IS NOT NULL THEN
321 l_lower_date := TRUNC(fnd_date.canonical_to_date(p_pei_information1));
322 END IF;
323 IF p_pei_information2 IS NOT NULL THEN
324 l_upper_date := TRUNC(fnd_date.canonical_to_date(p_pei_information2));
325 END IF;
326 --
327 --
328 -- Date From > Date To so error.
329 --
330 IF l_lower_date > l_upper_date THEN
331 RAISE military_service_dates;
332 END IF;
333 --
334 --
335 -- Residence Permit validation.
336 --
337 ELSIF p_pei_information_category = 'DE_RESIDENCE_PERMITS' THEN
338 --
339 --
340 -- Convert parameters to dates.
341 --
342 IF p_pei_information6 IS NOT NULL THEN
343 l_lower_date := TRUNC(fnd_date.canonical_to_date(p_pei_information6));
344 END IF;
345 IF p_pei_information7 IS NOT NULL THEN
346 l_upper_date := TRUNC(fnd_date.canonical_to_date(p_pei_information7));
347 END IF;
348 --
349 --
350 -- Effective Date > Expiry Date so error.
351 --
352 IF l_lower_date > l_upper_date THEN
353 RAISE permit_dates;
354 END IF;
355 --
356 --
357 -- Work Permit validation.
358 --
359 ELSIF p_pei_information_category = 'DE_WORK_PERMITS' THEN
360 --
361 --
362 -- Convert parameters to dates.
363 --
364 IF p_pei_information6 IS NOT NULL THEN
365 l_lower_date := TRUNC(fnd_date.canonical_to_date(p_pei_information6));
366 END IF;
367 IF p_pei_information7 IS NOT NULL THEN
368 l_upper_date := TRUNC(fnd_date.canonical_to_date(p_pei_information7));
369 END IF;
370 --
371 --
372 -- Effective Date > Expiry Date so error.
373 --
374 IF l_lower_date > l_upper_date THEN
375 RAISE permit_dates;
376 END IF;
377 END IF;
378 EXCEPTION
379 --
380 --
381 -- Date From is not on or before Date To.
382 --
383 WHEN military_service_dates THEN
384 hr_utility.set_message(800, 'HR_DE_MILITARY_SERVICE_DATES');
385 hr_utility.raise_error;
386 --
387 --
388 -- Effective Date is not on or before Expiry Date.
389 --
390 WHEN permit_dates THEN
391 hr_utility.set_message(800, 'HR_DE_PERMIT_DATES');
392 hr_utility.raise_error;
393 END person_information_checks;
394 --
395 --
396 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
397 -- Work Incident checks.
398 --
399 -- 1. The Work Stop Date (p_inc_information10) must be on or after Work Incident Date
400 -- (p_incident_date).
401 --
402 -- 2. The Payment End Date (p_inc_information9) must be on or after Work Incident Date
403 -- (p_incident_date).
404 --
405 -- 3. The Work Resumption Date (p_inc_information11) must be on or after Work Incident Date
406 -- (p_incident_date).
407 --
408 -- 4. The Job Start Date (p_inc_information3) must be on or before Work Incident Date
409 -- (p_incident_date).
410 --
411 -- 5. The Work Stop Date (p_inc_information10) must be on or before Work Resumption Date
412 -- (p_inc_information11).
413 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
414 --
415 PROCEDURE work_incident_checks
416 (p_incident_date IN DATE
417 ,p_inc_information3 IN VARCHAR2
418 ,p_inc_information9 IN VARCHAR2
419 ,p_inc_information10 IN VARCHAR2
420 ,p_inc_information11 IN VARCHAR2) IS
421 --
422 --
423 -- Local exceptions.
424 --
425 work_stop_date_too_early EXCEPTION;
426 payment_end_date_too_early EXCEPTION;
427 work_resump_date_too_early EXCEPTION;
428 job_start_date_too_late EXCEPTION;
429 resump_date_before_stop_date EXCEPTION;
430 BEGIN
431 --
432 --
433 -- The Work Stop Date must be on or after Work Incident Date.
434 --
435 IF p_inc_information10 IS NOT NULL THEN
436 IF p_incident_date > TRUNC(fnd_date.canonical_to_date(p_inc_information10)) THEN
437 RAISE work_stop_date_too_early;
438 END IF;
439 END IF;
440 --
441 --
442 -- The Payment End Date must be on or after Work Incident Date.
443 --
444 IF p_inc_information9 IS NOT NULL THEN
445 IF p_incident_date > TRUNC(fnd_date.canonical_to_date(p_inc_information9)) THEN
446 RAISE payment_end_date_too_early;
447 END IF;
448 END IF;
449 --
450 --
451 -- The Work Resumption Date must be on or after Work Incident Date.
452 --
453 IF p_inc_information11 IS NOT NULL THEN
454 IF p_incident_date > TRUNC(fnd_date.canonical_to_date(p_inc_information11)) THEN
455 RAISE work_resump_date_too_early;
456 END IF;
457 END IF;
458 --
459 --
460 -- The Job Start Date must be on or before Work Incident Date.
461 --
462 IF p_inc_information3 IS NOT NULL THEN
463 IF p_incident_date < TRUNC(fnd_date.canonical_to_date(p_inc_information3)) THEN
464 RAISE job_start_date_too_late;
465 END IF;
466 END IF;
467 --
468 --
469 -- The Work Stop Date must be on or before Work Resumption Date.
470 --
471 IF p_inc_information10 IS NOT NULL AND p_inc_information11 IS NOT NULL THEN
475 END IF;
472 IF TRUNC(fnd_date.canonical_to_date(p_inc_information11)) < TRUNC(fnd_date.canonical_to_date(p_inc_information10)) THEN
473 RAISE resump_date_before_stop_date;
474 END IF;
476 EXCEPTION
477 --
478 --
479 -- Work stop date is before the work incident date.
480 --
481 WHEN work_stop_date_too_early THEN
482 hr_utility.set_message(800, 'HR_DE_STOP_DATE_TOO_EARLY');
483 hr_utility.raise_error;
484 --
485 --
486 -- Payment stop date is before the work incident date.
487 --
488 WHEN payment_end_date_too_early THEN
489 hr_utility.set_message(800, 'HR_DE_PYMNT_DATE_TOO_EARLY');
490 hr_utility.raise_error;
491 --
492 --
493 -- Work resumption date is before the work incident date.
494 --
495 WHEN work_resump_date_too_early THEN
496 hr_utility.set_message(800, 'HR_DE_RESUMP_DATE_TOO_EARLY');
497 hr_utility.raise_error;
498 --
499 --
500 -- Job start date is after the work incident date.
501 --
502 WHEN job_start_date_too_late THEN
503 hr_utility.set_message(800, 'HR_DE_JOB_START_DATE_TOO_LATE');
504 hr_utility.raise_error;
505 --
506 --
507 -- Work Stop Date is after the Work Resumption Date.
508 --
509 WHEN resump_date_before_stop_date THEN
510 hr_utility.set_message(800, 'HR_DE_RESUMP_BEFORE_STOP_DATE');
511 hr_utility.raise_error;
512 END work_incident_checks;
513 END hr_de_extra_api_checks;
[Home] [Help]