1 PACKAGE BODY hr_dk_validate_pkg AS
2 /* $Header: pedkvald.pkb 120.9 2007/02/28 10:08:46 spendhar noship $ */
3
4
5 PROCEDURE validate
6 (p_person_type_id in number
7 ,p_first_name in varchar2 default null
8 ,p_national_identifier in varchar2 default null
9 ) AS
10
11 l_field varchar2(300) := NULL;
12 l_valid_date varchar2(10);
13 l_type VARCHAR2(1) := NULL;
14 CURSOR c_type IS
15 SELECT 'Y'
16 FROM per_person_types ppt
17 WHERE ppt.system_person_type like 'EMP%'
18 AND ppt.person_type_Id = p_person_type_id;
19
20 BEGIN
21 --
22 -- Added for GSI Bug 5472781
23 --
24 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
25 --
26 l_type := NULL;
27 OPEN c_type;
28 FETCH c_type INTO l_type;
29 CLOSE c_type;
30 --
31 --Validate not null fields
32 -- IF p_first_name IS NULL THEN
33 -- l_field := hr_general.decode_lookup('DK_FORM_LABELS','FIRST_NAME');
34 -- END IF;
35 --
36 IF l_type IS NOT NULL THEN
37 IF p_national_identifier IS NULL OR p_national_identifier = hr_api.g_varchar2 THEN
38 IF l_field IS NULL THEN
39 l_field := hr_general.decode_lookup('DK_FORM_LABELS','CPR');
40 ELSE
41 l_field := l_field||', '||hr_general.decode_lookup('DK_FORM_LABELS','CPR');
42 END IF;
43 END IF;
44 --Moved mandatory check for First Name here
45 IF p_first_name IS NULL OR p_first_name = hr_api.g_varchar2 THEN
46 l_field := hr_general.decode_lookup('DK_FORM_LABELS','FIRST_NAME');
47 END IF;
48 END IF;
49
50 /*Added an additional check fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION')IN ('ERROR','WARN')*/
51 IF l_field IS NOT NULL AND fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION')IN ('ERROR','WARN') THEN
52 fnd_message.set_name('PER', 'HR_377002_DK_MANDATORY_MSG');
53 fnd_message.set_token('NAME',l_field, translate => true );
54 hr_utility.raise_error;
55 END IF;
56 --
57 END IF;
58 --
59 END;
60
61 --Procedure for validating person
62 PROCEDURE person_validate
63 (p_person_type_id in number
64 ,p_first_name in varchar2 default null
65 ,p_national_identifier in varchar2 default null
66 ) AS
67 BEGIN
68 --
69 -- Added for GSI Bug 5472781
70 --
71 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
72 --
73 validate
74 (p_person_type_id => p_person_type_id
75 ,p_first_name => p_first_name
76 ,p_national_identifier => p_national_identifier);
77 --
78 END IF;
79 --
80 END person_validate;
81
82 --Procedure for validating applicant
83 PROCEDURE applicant_validate
84 (p_business_group_id in number
85 ,p_person_type_id in number
86 ,p_first_name in varchar2 default null
87 ,p_national_identifier in varchar2 default null
88 ) AS
89 l_person_type_id number ;
90 BEGIN
91 per_per_bus.chk_person_type
92 (p_person_type_id => l_person_type_id
93 ,p_business_group_id => p_business_group_id
94 ,p_expected_sys_type => 'APL'
95 );
96 validate
97 (p_person_type_id => l_person_type_id
98 ,p_first_name => p_first_name
99 ,p_national_identifier => p_national_identifier
100 );
101
102 END applicant_validate;
103
104 --Procedure for validating employee
105 PROCEDURE employee_validate
106 (p_business_group_id in number
107 ,p_person_type_id in number
108 ,p_first_name in varchar2 default null
109 ,p_national_identifier in varchar2 default null
110 ) AS
111 l_person_type_id number ;
112 BEGIN
113 --
114 -- Added for GSI Bug 5472781
115 --
116 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
117 --
118 per_per_bus.chk_person_type
119 (p_person_type_id => l_person_type_id
120 ,p_business_group_id => p_business_group_id
121 ,p_expected_sys_type => 'EMP');
122 --
123 validate
124 (p_person_type_id => l_person_type_id
125 ,p_first_name => p_first_name
126 ,p_national_identifier => p_national_identifier);
127 --
128 END IF;
129 --
130 END employee_validate;
131
132 --Procedure for validating contact/cwk
133 PROCEDURE contact_cwk_validate
134 (p_business_group_id in number
135 ,p_person_type_id in number
136 ,p_first_name in varchar2 default null
137 ,p_national_identifier in varchar2 default null
138 ) AS
139 l_person_type_id number ;
140 BEGIN
141 --
142 -- Added for GSI Bug 5472781
143 --
144 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
145 --
146 per_per_bus.chk_person_type
147 (p_person_type_id => l_person_type_id
148 ,p_business_group_id => p_business_group_id
149 ,p_expected_sys_type => 'OTHER');
150 --
151 validate
152 (p_person_type_id => l_person_type_id
153 ,p_first_name => p_first_name
154 ,p_national_identifier => p_national_identifier);
155 --
156 END IF;
157 --
158 END contact_cwk_validate;
159
160 /* Bug Fix 4994922, added parameters p_org_information2 and p_org_information3 */
161 PROCEDURE validate_create_org_inf
162 (p_org_info_type_code in varchar2
163 ,p_organization_id in number
164 ,p_org_information1 in varchar2
165 ,p_org_information2 in varchar2
166 ,p_org_information3 in varchar2
167 ) IS
168
169 l_org_information1 hr_organization_information.org_information1%TYPE;
170 l_organization_id hr_organization_units.organization_id%TYPE;
171 l_business_group_id hr_organization_units.business_group_id%TYPE;
172
173 /* Bug Fix 4994922, added record variable */
174 l_sickpay_records l_rec;
175 l_no_records NUMBER ;
176 l_index NUMBER;
177 l_sec27_reg VARCHAR2(5);
178 l_sec27_sd DATE;
179 l_sec27_ed DATE;
180 l_curr_sec27_sd DATE;
181 l_curr_sec27_ed DATE;
182
183 cursor getbgid is
184 select business_group_id
185 from hr_organization_units
186 where organization_id = p_organization_id;
187
188 cursor orgnum is
189 select orgif.org_information1 from hr_organization_information orgif,hr_organization_units ou
190 where ( orgif.org_information_context = 'DK_SERVICE_PROVIDER_DETAILS' or orgif.org_information_context = 'DK_LEGAL_ENTITY_DETAILS')
191 and ou.organization_id = orgif.organization_id
192 and orgif.org_information1 = p_org_information1;
193
194 /* Bug Fix 4994922 added cursor csr_get_sickpay_defaults */
195 cursor csr_get_sickpay_defaults(p_business_group_id NUMBER) is
196 select orgif.org_information1, orgif.org_information2, orgif.org_information3
197 from hr_organization_information orgif,hr_organization_units ou
198 where orgif.org_information_context = 'DK_SICKPAY_DEFAULTS'
199 and ou.business_group_id = p_business_group_id
200 and ou.organization_id = p_organization_id
201 and ou.organization_id = orgif.organization_id;
202
203 BEGIN
204 --
205 -- Added for GSI Bug 5472781
206 --
207 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
208 --
209 open getbgid;
210 fetch getbgid into l_business_group_id;
211 close getbgid;
212
213 IF p_org_info_type_code = 'DK_SERVICE_PROVIDER_DETAILS' OR p_org_info_type_code = 'DK_LEGAL_ENTITY_DETAILS' THEN
214 open orgnum;
215 fetch orgnum into l_org_information1;
216 if l_org_information1 = p_org_information1 then
217 fnd_message.set_name('PER','HR_377005_DK_CVR_NUMBER_UNIQUE');
218 fnd_message.raise_error;
219 end if;
220 close orgnum;
221 END IF;
222
223
224 /* Bug Fix 4994922 */
225 IF p_org_info_type_code = 'DK_SICKPAY_DEFAULTS' THEN
226
227 l_curr_sec27_sd := fnd_date.canonical_to_date(p_org_information2);
228 l_curr_sec27_ed := fnd_date.canonical_to_date(p_org_information3);
229
230 /* Check if Section 27 Registration start date is before end date */
231 IF l_curr_sec27_sd > l_curr_sec27_ed THEN
232 fnd_message.set_name('PER','HR_377068_DK_SECTION27_DATES_E');
233 fnd_message.raise_error;
234 END IF;
235
236 OPEN csr_get_sickpay_defaults(l_business_group_id);
237 FETCH csr_get_sickpay_defaults BULK COLLECT INTO l_sickpay_records;
238 CLOSE csr_get_sickpay_defaults;
239
240 l_no_records := l_sickpay_records.COUNT;
241 IF l_no_records = 0 THEN /* no sickpay default records found */
242 null;
243 ELSE
244 FOR l_index IN 1 .. l_no_records LOOP
245
246 l_sec27_reg := l_sickpay_records(l_index).value;
247 l_sec27_sd := fnd_date.canonical_to_date(l_sickpay_records(l_index).date1);
248 l_sec27_ed := fnd_date.canonical_to_date(l_sickpay_records(l_index).date2);
249
250 IF l_curr_sec27_sd BETWEEN l_sec27_sd AND l_sec27_ed THEN
251 fnd_message.set_name('PER','HR_377069_DK_SECTION27_OVERLAP');
252 fnd_message.raise_error;
253 ELSIF l_curr_sec27_ed BETWEEN l_sec27_sd AND l_sec27_ed THEN
254 fnd_message.set_name('PER','HR_377069_DK_SECTION27_OVERLAP');
255 fnd_message.raise_error;
256 ELSIF l_curr_sec27_sd <= l_sec27_sd AND l_curr_sec27_ed >= l_sec27_ed THEN
257 fnd_message.set_name('PER','HR_377069_DK_SECTION27_OVERLAP');
258 fnd_message.raise_error;
259 END IF;
260
261 END LOOP;
262 END IF;
263 END IF;
264 END IF;
265 END validate_create_org_inf;
266
267 /* Bug Fix 4994922, added parameters p_org_information2 and p_org_information3 */
268 PROCEDURE validate_update_org_inf
269 (p_org_info_type_code in varchar2
270 ,p_org_information_id in number
271 ,p_org_information1 in varchar2
272 ,p_org_information2 in varchar2
273 ,p_org_information3 in varchar2
274 ) IS
275
276 l_org_information1 hr_organization_information.org_information1%TYPE;
277 l_organization_id hr_organization_information.organization_id%TYPE;
278 l_business_group_id hr_organization_units.business_group_id%TYPE;
279
280 /* Bug Fix 4994922, added record variable */
281 l_sickpay_records l_rec;
282 l_no_records NUMBER ;
283 l_index NUMBER;
284 l_sec27_reg VARCHAR2(5);
285 l_sec27_sd DATE;
286 l_sec27_ed DATE;
287 l_curr_sec27_sd DATE;
288 l_curr_sec27_ed DATE;
289
290 cursor getbgid is
291 select business_group_id
292 from hr_organization_units
293 where organization_id = l_organization_id;
294
295 cursor getorgid is
296 select organization_id
297 from hr_organization_information
298 where org_information_id = p_org_information_id;
299
300 cursor orgnum is
301 select orgif.org_information1 from hr_organization_information orgif,hr_organization_units ou
302 where ( orgif.org_information_context = 'DK_SERVICE_PROVIDER_DETAILS' or orgif.org_information_context = 'DK_LEGAL_ENTITY_DETAILS')
303 and ou.organization_id = orgif.organization_id
304 and orgif.organization_id <> nvl(l_organization_id,0)
305 and orgif.org_information1 = p_org_information1;
306
307 /* Bug Fix 4994922 added cursor csr_get_sickpay_defaults */
308 cursor csr_get_sickpay_defaults(p_business_group_id NUMBER, p_organization_id NUMBER) is
309 select orgif.org_information1, orgif.org_information2, orgif.org_information3
310 from hr_organization_information orgif,hr_organization_units ou
311 where orgif.org_information_context = 'DK_SICKPAY_DEFAULTS'
312 and ou.business_group_id = p_business_group_id
313 and ou.organization_id = p_organization_id
314 and ou.organization_id = orgif.organization_id;
315
316
317 BEGIN
318 --
319 -- Added for GSI Bug 5472781
320 --
321 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
322 --
323 open getorgid;
324 fetch getorgid into l_organization_id;
325 close getorgid;
326
327 open getbgid;
328 fetch getbgid into l_business_group_id;
329 close getbgid;
330
331 IF p_org_info_type_code = 'DK_SERVICE_PROVIDER_DETAILS' OR p_org_info_type_code = 'DK_LEGAL_ENTITY_DETAILS' THEN
332 open orgnum;
333 fetch orgnum into l_org_information1;
334 if l_org_information1 = p_org_information1 then
335 fnd_message.set_name('PER','HR_377005_DK_CVR_NUMBER_UNIQUE');
336 fnd_message.raise_error;
337 end if;
338 close orgnum;
339 END IF;
340
341 /* Bug Fix 4994922 */
342 IF p_org_info_type_code = 'DK_SICKPAY_DEFAULTS' THEN
343
344 l_curr_sec27_sd := fnd_date.canonical_to_date(p_org_information2);
345 l_curr_sec27_ed := fnd_date.canonical_to_date(p_org_information3);
346
347 /* Check if Section 27 Registration start date is before end date */
348 IF l_curr_sec27_sd > l_curr_sec27_ed THEN
349 fnd_message.set_name('PER','HR_377068_DK_SECTION27_DATES_E');
350 fnd_message.raise_error;
351 END IF;
352
353 OPEN csr_get_sickpay_defaults(l_business_group_id,l_organization_id);
354 FETCH csr_get_sickpay_defaults BULK COLLECT INTO l_sickpay_records;
355 CLOSE csr_get_sickpay_defaults;
356
357 l_no_records := l_sickpay_records.COUNT;
358 IF l_no_records = 0 THEN /* no sickpay default records found */
359 null;
360 ELSE
361 FOR l_index IN 1 .. l_no_records LOOP
362
363 l_sec27_reg := l_sickpay_records(l_index).value;
364 l_sec27_sd := fnd_date.canonical_to_date(l_sickpay_records(l_index).date1);
365 l_sec27_ed := fnd_date.canonical_to_date(l_sickpay_records(l_index).date2);
366
367 IF l_curr_sec27_sd BETWEEN l_sec27_sd AND l_sec27_ed THEN
368 fnd_message.set_name('PER','HR_377069_DK_SECTION27_OVERLAP');
369 fnd_message.raise_error;
370 ELSIF l_curr_sec27_ed BETWEEN l_sec27_sd AND l_sec27_ed THEN
371 fnd_message.set_name('PER','HR_377069_DK_SECTION27_OVERLAP');
372 fnd_message.raise_error;
373 ELSIF l_curr_sec27_sd <= l_sec27_sd AND l_curr_sec27_ed >= l_sec27_ed THEN
374 fnd_message.set_name('PER','HR_377069_DK_SECTION27_OVERLAP');
375 fnd_message.raise_error;
376 END IF;
377
378 END LOOP;
379 END IF;
380
381 END IF;
382 END IF;
383 END validate_update_org_inf;
384
385 PROCEDURE validate_update_emp_asg
386 (p_assignment_id in number
387 ,p_assignment_status_type_id in number
388 ,p_segment6 in varchar2
389 ,p_segment7 in varchar2
390 ,p_segment8 in varchar2
391 ,p_segment9 in varchar2
392 ) IS
393
394 l_asg_start_date date;
395 l_field varchar2(200);
396
397 cursor get_asg_creation_date is
398 select creation_date from per_all_assignments_f where
399 assignment_id = p_assignment_id;
400 BEGIN
401 --
402 -- Added for GSI Bug 5472781
403 --
404 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
405 --
406 IF p_assignment_status_type_id = 3 THEN
407 IF p_segment6 IS NULL THEN
408 l_field := hr_general.decode_lookup('DK_FORM_LABELS','TR');
409 fnd_message.set_name('PER', 'HR_377002_DK_MANDATORY_MSG');
410 fnd_message.set_token('NAME',l_field, translate => true );
411 hr_utility.raise_error;
412 END IF;
413
414 IF p_segment7 IS NULL THEN
415 l_field := hr_general.decode_lookup('DK_FORM_LABELS','ND');
416 fnd_message.set_name('PER', 'HR_377002_DK_MANDATORY_MSG');
417 fnd_message.set_token('NAME',l_field, translate => true );
418 hr_utility.raise_error;
419 END IF;
420
421 IF p_segment8 IS NULL THEN
422 l_field := hr_general.decode_lookup('DK_FORM_LABELS','TD');
423 fnd_message.set_name('PER', 'HR_377002_DK_MANDATORY_MSG');
424 fnd_message.set_token('NAME',l_field, translate => true );
425 hr_utility.raise_error;
426 END IF;
427 IF p_segment9 IS NULL THEN
428 l_field := hr_general.decode_lookup('DK_FORM_LABELS','AD');
429 fnd_message.set_name('PER', 'HR_377002_DK_MANDATORY_MSG');
430 fnd_message.set_token('NAME',l_field, translate => true );
431 hr_utility.raise_error;
432 END IF;
433
434 ELSE
435 -- GSI Bug 4585094
436 IF p_segment6 <> hr_api.g_varchar2 THEN
437 fnd_message.set_name('PER', 'HR_377009_DK_TR_INVALID');
438 hr_utility.raise_error;
439 END IF;
440 -- GSI Bug 4585094
441 IF p_segment7 <> hr_api.g_varchar2 THEN
442 fnd_message.set_name('PER', 'HR_377010_DK_ND_INVALID');
443 hr_utility.raise_error;
444 END IF;
445 -- GSI Bug 4585094
446 IF p_segment8 <> hr_api.g_varchar2 THEN
447 fnd_message.set_name('PER', 'HR_377011_DK_TD_INVALID');
448 hr_utility.raise_error;
449 END IF;
450
451 END IF;
452
453 BEGIN
454 OPEN get_asg_creation_date;
455 FETCH get_asg_creation_date into l_asg_start_date;
456 CLOSE get_asg_creation_date;
457 EXCEPTION
458 WHEN others THEN
459 null;
460 END;
461
462 -- Validation rule : notified date > assignment start date --
463 if p_segment7 <> hr_api.g_varchar2 -- GSI Bug 4585094
464 and l_asg_start_date is not NULL then
465 if fnd_date.canonical_to_date(p_segment7) < fnd_date.canonical_to_date(l_asg_start_date) then
466 fnd_message.set_name('PER', 'HR_377006_DK_NOTIFIED_DATE');
467 hr_utility.raise_error;
468 end if;
469 end if;
470 -- Validation rule : Termination date >= notified date --
471 if p_segment8 <> hr_api.g_varchar2 -- GSI Bug 4585094
472 and p_segment7 <> hr_api.g_varchar2 then
473 if fnd_date.canonical_to_date(p_segment8) < fnd_date.canonical_to_date(p_segment7) then
474 fnd_message.set_name('PER', 'HR_377007_DK_TERM_DATE_ERR');
475 hr_utility.raise_error;
476 end if;
477 end if;
478 -- Validation rule : Adjusted seniority date < notified date --
479 if p_segment9 <> hr_api.g_varchar2 -- GSI Bug 4585094
480 and p_segment7 <> hr_api.g_varchar2 then
481 if fnd_date.canonical_to_date(p_segment9) >= fnd_date.canonical_to_date(p_segment7) then
482 fnd_message.set_name('PER', 'HR_377008_DK_ASD_ERR');
483 hr_utility.raise_error;
484 end if;
485 end if;
486
487 END IF;
488 --
489 END validate_update_emp_asg;
490
491
492
493 -- Procedure to Validate the Organization Classification
494 PROCEDURE validate_create_org_cat
495 (p_organization_id in number
496 ,p_org_information1 in varchar2
497 ) IS
498
499 l_organization_id hr_organization_units.organization_id%TYPE;
500 l_business_group_id hr_organization_units.business_group_id%TYPE;
501 l_int_ext_flag hr_organization_units.internal_external_flag%TYPE;
502 /* Added for bug fix 4227055 */
503 l_sp hr_organization_units.organization_id%TYPE;
504
505 SP_DATA_FOUND EXCEPTION;
506
507 cursor orgtype is
508 select ou.internal_external_flag from hr_organization_units ou ,FND_SESSIONS s
509 where ou.organization_id= p_organization_id
510 and s.session_id = userenv('sessionid')
511 and s.effective_date between ou.date_from and nvl(ou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
512
513 /* Added for bug fix 4227055 */
514 /* Not more than one Service Provider can be created in a Business Group */
515 --
516 CURSOR get_sp( p_business_group_id hr_organization_units.business_group_id%TYPE) IS
517 select count(hou.organization_id)
518 from HR_ORGANIZATION_INFORMATION hoi
519 ,HR_ORGANIZATION_UNITS hou
520 ,FND_SESSIONS s
521 where hoi.org_information_context ='CLASS'
522 and hoi.org_information1 ='DK_SERVICE_PROVIDER'
523 and hou.organization_id = hoi.organization_id
524 and hou.BUSINESS_GROUP_ID = p_business_group_id
525 and s.session_id = userenv('sessionid')
526 and s.effective_date between hou.date_from and nvl(hou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
527
528 CURSOR getbgid is
529 select business_group_id
530 from hr_organization_units
531 where organization_id = p_organization_id;
532 --
533 BEGIN
534 --
535 -- Added for GSI Bug 5472781
536 --
537 IF hr_utility.chk_product_install('Oracle Human Resources', 'DK') THEN
538 --
539 IF p_org_information1 = 'DK_PENSION_PROVIDER' THEN
540
541 open orgtype;
542 fetch orgtype into l_int_ext_flag;
543 close orgtype;
544
545 if l_int_ext_flag='INT' then
546 fnd_message.set_name('PER','HR_377013_DK_PENSION_PVDR');
547 fnd_message.raise_error;
548 end if;
549
550 END IF;
551
552 IF p_org_information1 = 'DK_SERVICE_PROVIDER' THEN
553
554 open orgtype;
555 fetch orgtype into l_int_ext_flag;
556 close orgtype;
557
558 if l_int_ext_flag='INT' then
559 fnd_message.set_name('PER','HR_377014_DK_SERVICE_PVDR');
560 fnd_message.raise_error;
561 end if;
562
563 /* Added for bug fix 4227055 */
564
565 OPEN getbgid;
566 FETCH getbgid INTO l_business_group_id;
567 CLOSE getbgid;
568
569 OPEN get_sp(l_business_group_id);
570 FETCH get_sp INTO l_sp;
571
572 IF l_sp >1 THEN
573 RAISE SP_DATA_FOUND;
574 END IF;
575
576 CLOSE get_sp;
577
578
579 END IF;
580 END IF;
581 EXCEPTION
582 WHEN SP_DATA_FOUND THEN
583 fnd_message.set_name('PER','HR_377035_DK_SP_UNIQUE');
584 fnd_message.raise_error;
585
586
587
588 END validate_create_org_cat;
589
590
591 END hr_dk_validate_pkg;
592
593
[Home] [Help]