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