[Home] [Help]
PACKAGE BODY: APPS.OTA_CMB_BUS
Source
1 Package Body ota_cmb_bus as
2 /* $Header: otcmbrhi.pkb 120.5 2005/08/19 17:58 gdhutton noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cmb_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_certification_member_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_certification_member_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , ota_certification_members cmb
32 where cmb.certification_member_id = p_certification_member_id
33 and pbg.business_group_id = cmb.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'certification_member_id'
50 ,p_argument_value => p_certification_member_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'CERTIFICATION_MEMBER_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_certification_member_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , ota_certification_members cmb
102 where cmb.certification_member_id = p_certification_member_id
103 and pbg.business_group_id = cmb.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'certification_member_id'
119 ,p_argument_value => p_certification_member_id
120 );
121 --
122 if ( nvl(ota_cmb_bus.g_certification_member_id, hr_api.g_number)
123 = p_certification_member_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := ota_cmb_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 ota_cmb_bus.g_certification_member_id := p_certification_member_id;
154 ota_cmb_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in ota_cmb_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.certification_member_id is not null) and (
198 nvl(ota_cmb_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(ota_cmb_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(ota_cmb_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(ota_cmb_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(ota_cmb_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(ota_cmb_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(ota_cmb_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(ota_cmb_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(ota_cmb_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(ota_cmb_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(ota_cmb_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(ota_cmb_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(ota_cmb_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(ota_cmb_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(ota_cmb_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(ota_cmb_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(ota_cmb_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(ota_cmb_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(ota_cmb_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(ota_cmb_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(ota_cmb_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.certification_member_id is null) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'OTA'
249 ,p_descflex_name => 'OTA_CERTIFICATION_MEMBERS'
250 ,p_attribute_category => p_rec.attribute_category
251 ,p_attribute1_name => 'ATTRIBUTE1'
252 ,p_attribute1_value => p_rec.attribute1
253 ,p_attribute2_name => 'ATTRIBUTE2'
254 ,p_attribute2_value => p_rec.attribute2
255 ,p_attribute3_name => 'ATTRIBUTE3'
256 ,p_attribute3_value => p_rec.attribute3
257 ,p_attribute4_name => 'ATTRIBUTE4'
258 ,p_attribute4_value => p_rec.attribute4
259 ,p_attribute5_name => 'ATTRIBUTE5'
260 ,p_attribute5_value => p_rec.attribute5
261 ,p_attribute6_name => 'ATTRIBUTE6'
262 ,p_attribute6_value => p_rec.attribute6
263 ,p_attribute7_name => 'ATTRIBUTE7'
264 ,p_attribute7_value => p_rec.attribute7
265 ,p_attribute8_name => 'ATTRIBUTE8'
266 ,p_attribute8_value => p_rec.attribute8
267 ,p_attribute9_name => 'ATTRIBUTE9'
268 ,p_attribute9_value => p_rec.attribute9
269 ,p_attribute10_name => 'ATTRIBUTE10'
270 ,p_attribute10_value => p_rec.attribute10
271 ,p_attribute11_name => 'ATTRIBUTE11'
272 ,p_attribute11_value => p_rec.attribute11
273 ,p_attribute12_name => 'ATTRIBUTE12'
274 ,p_attribute12_value => p_rec.attribute12
275 ,p_attribute13_name => 'ATTRIBUTE13'
276 ,p_attribute13_value => p_rec.attribute13
277 ,p_attribute14_name => 'ATTRIBUTE14'
278 ,p_attribute14_value => p_rec.attribute14
279 ,p_attribute15_name => 'ATTRIBUTE15'
280 ,p_attribute15_value => p_rec.attribute15
281 ,p_attribute16_name => 'ATTRIBUTE16'
282 ,p_attribute16_value => p_rec.attribute16
283 ,p_attribute17_name => 'ATTRIBUTE17'
284 ,p_attribute17_value => p_rec.attribute17
285 ,p_attribute18_name => 'ATTRIBUTE18'
286 ,p_attribute18_value => p_rec.attribute18
287 ,p_attribute19_name => 'ATTRIBUTE19'
288 ,p_attribute19_value => p_rec.attribute19
289 ,p_attribute20_name => 'ATTRIBUTE20'
290 ,p_attribute20_value => p_rec.attribute20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_df;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |-----------------------< chk_non_updateable_args >------------------------|
299 -- ----------------------------------------------------------------------------
300 -- {Start Of Comments}
301 --
302 -- Description:
303 -- This procedure is used to ensure that non updateable attributes have
304 -- not been updated. If an attribute has been updated an error is generated.
305 --
306 -- Pre Conditions:
307 -- g_old_rec has been populated with details of the values currently in
308 -- the database.
309 --
310 -- In Arguments:
311 -- p_rec has been populated with the updated values the user would like the
312 -- record set to.
313 --
314 -- Post Success:
315 -- Processing continues if all the non updateable attributes have not
316 -- changed.
317 --
318 -- Post Failure:
319 -- An application error is raised if any of the non updatable attributes
320 -- have been altered.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324 Procedure chk_non_updateable_args
325 (p_effective_date in date
326 ,p_rec in ota_cmb_shd.g_rec_type
327 ) IS
328 --
329 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
330 --
331 Begin
332 --
333 -- Only proceed with the validation if a row exists for the current
334 -- record in the HR Schema.
335 --
336 IF NOT ota_cmb_shd.api_updating
337 (p_certification_member_id => p_rec.certification_member_id
338 ,p_object_version_number => p_rec.object_version_number
339 ) THEN
340 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
341 fnd_message.set_token('PROCEDURE ', l_proc);
342 fnd_message.set_token('STEP ', '5');
343 fnd_message.raise_error;
344 END IF;
345 --
346 -- EDIT_HERE: Add checks to ensure non-updateable args have
347 -- not been updated.
348 --
349 End chk_non_updateable_args;
350
351 --
352 -- ----------------------------------------------------------------------------
353 -- |--------------------------< chk_act_version_dates >-----------------------|
354 -- ----------------------------------------------------------------------------
355 Procedure chk_act_version_dates
356 (
357 p_activity_version_id in number
358 ,p_cmb_start_date in date
359 ,p_cmb_end_date in date
360 ) is
361 --
362 -- Declare cursors and local variables
363 --
364 --
365 -- Cursor to get activity version start and end date
366
367 CURSOR csr_av_start_end_date is
368 SELECT
369 start_date,
370 nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
371 FROM ota_activity_versions
372 WHERE activity_version_id = p_activity_version_id;
373
374 --
375 -- Variables
376 l_proc varchar2(72) := g_package||'check_act_version_dates';
377 l_av_start_date date;
378 l_av_end_date date;
379 l_cmb_start_date date;
380 l_cmb_end_date date;
381
382 Begin
383 hr_utility.set_location(' Entering:' || l_proc,10);
384 --
385 IF hr_multi_message.no_exclusive_error
386 (p_check_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
387 ,p_check_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
388 ,p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
389 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
390 ) THEN
391
392 --
393 OPEN csr_av_start_end_date;
394 FETCH csr_av_start_end_date into l_av_start_date, l_av_end_date;
395 CLOSE csr_av_start_end_date;
396
397 if p_cmb_start_date is not null and p_cmb_end_date is not null
398 and p_cmb_start_date > p_cmb_end_date then
399 fnd_message.set_name('OTA','OTA_13312_GEN_DATE_ORDER');
400 fnd_message.raise_error;
401 end if;
402
403 if l_av_start_date is null then
404 l_av_start_date := hr_api.g_sot;
405 end if;
406
407 if l_av_end_date is null then
408 l_av_end_date := hr_api.g_eot;
409 end if;
410
411 --
412 l_cmb_start_date := p_cmb_start_date;
413 l_cmb_end_date := p_cmb_end_date;
414 --
415 if l_cmb_end_date is null then
416 l_cmb_end_date := hr_api.g_eot;
417 end if;
418
419 if l_cmb_start_date < l_av_start_date or
420 l_cmb_start_date > l_av_end_date or
421 l_cmb_end_date > l_av_end_date or
422 l_cmb_end_date < l_av_start_date then
423
424 fnd_message.set_name('OTA','OTA_443911_CMB_OUT_OF_AV_DATE');
425 fnd_message.raise_error;
426 end if;
427
428 END IF;
429 --
430 hr_utility.set_location(' Leaving:' || l_proc,10);
431
432 Exception
433 when app_exception.application_exception then
434 IF hr_multi_message.exception_add
435 (p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
436 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
437 ) THEN
438 raise;
439 END IF;
440
441 --
442 End chk_act_version_dates;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |--------------------------< chk_cert_dates >-----------------------|
446 -- ----------------------------------------------------------------------------
447 Procedure chk_cert_dates
448 (
449 p_certification_id in number
450 ,p_cmb_start_date in date
451 ,p_cmb_end_date in date
452 ) is
453 --
454 -- Declare cursors and local variables
455 --
456 --
457 -- Cursor to get certification start and end date
458
459 CURSOR csr_cert_start_end_date is
460 SELECT
461 start_date_active,
462 nvl(end_date_active, to_date('31-12-4712', 'DD-MM-YYYY'))
463 FROM ota_certifications_b
464 WHERE certification_id = p_certification_id;
465
466 --
467 -- Variables
468 l_proc varchar2(72) := g_package||'check_cert_dates';
469 l_cert_start_date date;
470 l_cert_end_date date;
471 l_cmb_start_date date;
472 l_cmb_end_date date;
473
474 Begin
475 hr_utility.set_location(' Entering:' || l_proc,10);
476 --
477 IF hr_multi_message.no_exclusive_error
478 (p_check_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
479 ,p_check_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
480 ,p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
481 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
482 ) THEN
483
484 --
485 OPEN csr_cert_start_end_date;
486 FETCH csr_cert_start_end_date into l_cert_start_date, l_cert_end_date;
487 CLOSE csr_cert_start_end_date;
488
489 if p_cmb_start_date is not null and p_cmb_end_date is not null
490 and p_cmb_start_date > p_cmb_end_date then
491 fnd_message.set_name('OTA','OTA_13312_GEN_DATE_ORDER');
492 fnd_message.raise_error;
496 l_cert_start_date := hr_api.g_sot;
493 end if;
494
495 if l_cert_start_date is null then
497 end if;
498
499 if l_cert_end_date is null then
500 l_cert_end_date := hr_api.g_eot;
501 end if;
502
503 --
504 l_cmb_start_date := p_cmb_start_date;
505 l_cmb_end_date := p_cmb_end_date;
506 --
507 if l_cmb_end_date is null then
508 l_cmb_end_date := hr_api.g_eot;
509 end if;
510
511 if l_cmb_start_date < l_cert_start_date or
512 l_cmb_start_date > l_cert_end_date or
513 l_cmb_end_date > l_cert_end_date or
514 l_cmb_end_date < l_cert_start_date then
515
516 fnd_message.set_name('OTA','OTA_443918_CMB_OUT_OF_CRT_DATE');
517 fnd_message.raise_error;
518 end if;
519
520 END IF;
521 --
522 hr_utility.set_location(' Leaving:' || l_proc,10);
523
524 Exception
525 when app_exception.application_exception then
526 IF hr_multi_message.exception_add
527 (p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
528 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
529 ) THEN
530 raise;
531 END IF;
532
533 --
534 End chk_cert_dates;
535
536 -- ----------------------------------------------------------------------------
537 -- |--------------------------< chk_dup_cmb_dates >-----------------------|
538 -- ----------------------------------------------------------------------------
539 Procedure chk_dup_cmb_dates
540 (
541 p_activity_version_id in number
542 ,p_certification_id in number
543 ,p_cmb_start_date in date
544 ,p_cmb_end_date in date
545 ,p_cert_member_id in number
546 ) is
547 --
548 -- Declare cursors and local variables
549 --
550 --
551 -- Cursor to get other certification components with the same activity_version
552
553 CURSOR csr_dup_cmb(p_activity_version_id ota_certification_members.object_id%type,
554 p_certification_id ota_certification_members.certification_id%type,
555 p_certification_member_id ota_certification_members.certification_member_id%type)
556 is
557 SELECT
558 start_date_active,
559 end_date_active
560 FROM ota_certification_members
561 WHERE certification_id = p_certification_id
562 and object_id = p_activity_version_id
563 and object_type = 'H'
564 and (p_certification_member_id is null OR not certification_member_id = p_certification_member_id);
565
566 --
567 -- Variables
568 l_proc varchar2(72) := g_package||'chk_dup_cmb_dates';
569 l_dup_cmb_start_date date;
570 l_dup_cmb_end_date date;
571
572 Begin
573 hr_utility.set_location(' Entering:' || l_proc,10);
574 --
575 IF hr_multi_message.no_exclusive_error
576 (p_check_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
577 ,p_check_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
578 ,p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
579 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
580 ) THEN
581
582 for a_dup_cmb in csr_dup_cmb(p_activity_version_id, p_certification_id, p_cert_member_id) loop
583
584 if (p_cmb_start_date >= a_dup_cmb.start_date_active
585 and p_cmb_start_date <= nvl(a_dup_cmb.end_date_active, hr_api.g_eot))
586 OR (nvl(p_cmb_end_date, hr_api.g_eot) >= a_dup_cmb.start_date_active
587 and nvl(p_cmb_end_date, hr_api.g_eot) <= nvl(a_dup_cmb.end_date_active, hr_api.g_eot))
588 OR (a_dup_cmb.start_date_active >= p_cmb_start_date
589 and a_dup_cmb.start_date_active <= nvl(p_cmb_end_date, hr_api.g_eot))
590 OR (nvl(a_dup_cmb.end_date_active, hr_api.g_eot) >= p_cmb_start_date
591 and nvl(a_dup_cmb.end_date_active, hr_api.g_eot) <= nvl(p_cmb_end_date, hr_api.g_eot))
592 then
593 fnd_message.set_name('OTA','OTA_443927_CMB_DUPLICATE');
594 fnd_message.raise_error;
595 end if;
596
597 end loop;
598 END IF;
599 hr_utility.set_location(' Leaving:' || l_proc,10);
600
601 Exception
602 when app_exception.application_exception then
603 IF hr_multi_message.exception_add
604 (p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
605 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
606 ) THEN
607 raise;
608 END IF;
609
610 --
611 End chk_dup_cmb_dates;
612 --
613
614 -- ----------------------------------------------------------------------------
615 -- |--------------------------< chk_dup_cmb_dates >-----------------------|
616 -- ----------------------------------------------------------------------------
617 Procedure chk_dup_cmb_dates
618 (
619 p_activity_version_id in number
620 ,p_certification_id in number
621 ,p_cmb_start_date in date
622 ,p_cmb_end_date in date
623 ,p_cert_member_id in number
624 ,p_return_status OUT NOCOPY VARCHAR2
625 ) is
626 --
627 -- Declare cursors and local variables
628 --
629 --
630 -- Cursor to get other certification components with the same activity_version
631
632 CURSOR csr_dup_cmb(p_activity_version_id ota_certification_members.object_id%type,
633 p_certification_id ota_certification_members.certification_id%type,
634 p_certification_member_id ota_certification_members.certification_member_id%type)
635 is
639 FROM ota_certification_members
636 SELECT
637 start_date_active,
638 end_date_active
640 WHERE certification_id = p_certification_id
641 and object_id = p_activity_version_id
642 and object_type = 'H'
643 and (p_certification_member_id is null OR not certification_member_id = p_certification_member_id);
644
645 --
646 -- Variables
647 l_proc varchar2(72) := g_package||'chk_dup_cmb_dates';
648 l_dup_cmb_start_date date;
649 l_dup_cmb_end_date date;
650
651 Begin
652 hr_utility.set_location(' Entering:' || l_proc,10);
653 --
654
655 p_return_status := 'S';
656
657 IF hr_multi_message.no_exclusive_error
658 (p_check_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
659 ,p_check_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
660 ,p_associated_column1 => 'OTA_CERTIFICATION_MEMBERS.START_DATE_ACTIVE'
661 ,p_associated_column2 => 'OTA_CERTIFICATION_MEMBERS.END_DATE_ACTIVE'
662 ) THEN
663
664 for a_dup_cmb in csr_dup_cmb(p_activity_version_id, p_certification_id, p_cert_member_id) loop
665
666 if (p_cmb_start_date >= a_dup_cmb.start_date_active
667 and p_cmb_start_date <= nvl(a_dup_cmb.end_date_active, hr_api.g_eot))
668 OR (nvl(p_cmb_end_date, hr_api.g_eot) >= a_dup_cmb.start_date_active
669 and nvl(p_cmb_end_date, hr_api.g_eot) <= nvl(a_dup_cmb.end_date_active, hr_api.g_eot))
670 OR (a_dup_cmb.start_date_active >= p_cmb_start_date
671 and a_dup_cmb.start_date_active <= nvl(p_cmb_end_date, hr_api.g_eot))
672 OR (nvl(a_dup_cmb.end_date_active, hr_api.g_eot) >= p_cmb_start_date
673 and nvl(a_dup_cmb.end_date_active, hr_api.g_eot) <= nvl(p_cmb_end_date, hr_api.g_eot))
674 then
675 p_return_status := 'E';
676 end if;
677
678 end loop;
679 END IF;
680 hr_utility.set_location(' Leaving:' || l_proc,10);
681
682 --
683 End chk_dup_cmb_dates;
684 --
685 --
686 PROCEDURE chk_if_dup_cmb_exists
687 (p_object_id IN ota_certification_members.object_id%TYPE
688 ,p_certification_id IN ota_certification_members.certification_id%TYPE
689 , p_return_status OUT NOCOPY VARCHAR2)
690 IS
691 --
692 --
693 v_exists varchar2(1);
694 v_proc varchar2(72) := g_package||'chk_if_dup_cmb_exists';
695 --
696 cursor sel_cmb_exists(p_object_id ota_certification_members.object_id%type,
697 p_certification_id ota_certification_members.certification_id%type) is
698 select 'Y'
699 from ota_certification_members cmb
700 where cmb.object_id = p_object_id
701 and cmb.certification_id = p_certification_id;
702 --
703 Begin
704 --
705 hr_utility.set_location('Entering:'|| v_proc, 5);
706 --
707
708 p_return_status := 'S';
709
710 Open sel_cmb_exists(p_object_id, p_certification_id);
711 fetch sel_cmb_exists into v_exists;
712 --
713 if sel_cmb_exists%found then
714 --
715 close sel_cmb_exists;
716 --
717 p_return_status := 'E';
718 --
719 else
720 close sel_cmb_exists;
721
722 end if;
723 --
724
725 hr_utility.set_location(' Step:'|| v_proc, 30);
726
727 END chk_if_dup_cmb_exists;
728
729 PROCEDURE chk_should_warn_delete
730 (p_cert_mbr_id IN ota_certification_members.certification_member_id%TYPE
731 , p_return_status OUT NOCOPY VARCHAR2)
732 IS
733 --
734 v_proc varchar2(72) := g_package||'chk_should_warn_delete';
735 --
736 cursor csr_cmb_info(p_cert_mbr_id ota_certification_members.certification_member_id%type) is
737 select certification_id
738 from ota_certification_members
739 where certification_member_id = p_cert_mbr_id and
740 (end_date_active is null or
741 end_date_active > sysdate);
742
743 cursor csr_cmb_exists(
744 p_cert_mbr_id ota_certification_members.certification_member_id%type,
745 p_certification_id ota_certifications_b.certification_id%type) is
746 select 'Y'
747 from ota_certification_members cmb
748 where cmb.certification_id = p_certification_id
749 and cmb.certification_member_id <> p_cert_mbr_id
750 and (cmb.end_date_active is null or
751 cmb.end_date_active > sysdate);
752 --
753 Begin
754 --
755 hr_utility.set_location('Entering:'|| v_proc, 5);
756 --
757
758 p_return_status := 'S';
759
760 for a_cmb in csr_cmb_info(p_cert_mbr_id) loop
761
762 for another_cmb in csr_cmb_exists(p_cert_mbr_id, a_cmb.certification_id) loop
763 -- There is at least one more cert member, no need to warn, just return.
764 hr_utility.set_location(' Step:'|| v_proc, 30);
765 return;
766 end loop;
767
768 -- No other current cert members exist, so we need to warn about
769 -- deleting this one.
770 p_return_status := 'E';
771
772 end loop;
773
774 hr_utility.set_location(' Step:'|| v_proc, 30);
775
776 END chk_should_warn_delete;
777 -- ----------------------------------------------------------------------------
778 -- |---------------------------< insert_validate >----------------------------|
779 -- ----------------------------------------------------------------------------
780 Procedure insert_validate
781 (p_effective_date in date
782 ,p_rec in ota_cmb_shd.g_rec_type
783 ) is
784 --
785 l_proc varchar2(72) := g_package||'insert_validate';
786 --
787 Begin
788 hr_utility.set_location('Entering:'||l_proc, 5);
789 --
793 (p_business_group_id => p_rec.business_group_id
790 -- Call all supporting business operations
791 --
792 hr_api.validate_bus_grp_id
794 ,p_associated_column1 => ota_cmb_shd.g_tab_nam
795 || '.BUSINESS_GROUP_ID');
796
797 --
798 -- After validating the set of important attributes,
799 -- if Multiple Message detection is enabled and at least
800 -- one error has been found then abort further validation.
801 --
802 hr_multi_message.end_validation_set;
803 hr_utility.set_location(' Step:'|| l_proc,10);
804 --
805 -- Validate Dependent Attributes
806 --
807 --
808
809 ota_cmb_bus.chk_act_version_dates(p_activity_version_id => p_rec.object_id
810 ,p_cmb_start_date => p_rec.start_date_active
811 ,p_cmb_end_date => p_rec.end_date_active);
812
813 ota_cmb_bus.chk_cert_dates(p_certification_id => p_rec.certification_id
814 ,p_cmb_start_date => p_rec.start_date_active
815 ,p_cmb_end_date => p_rec.end_date_active);
816
817 ota_cmb_bus.chk_dup_cmb_dates(p_activity_version_id => p_rec.object_id
818 ,p_certification_id => p_rec.certification_id
819 ,p_cmb_start_date => p_rec.start_date_active
820 ,p_cmb_end_date => p_rec.end_date_active
821 ,p_cert_member_id => p_rec.certification_member_id);
822
823 ota_cmb_bus.chk_df(p_rec);
824 --
825 hr_utility.set_location(' Leaving:'||l_proc, 10);
826 End insert_validate;
827 --
828 -- ----------------------------------------------------------------------------
829 -- |---------------------------< update_validate >----------------------------|
830 -- ----------------------------------------------------------------------------
831 Procedure update_validate
832 (p_effective_date in date
833 ,p_rec in ota_cmb_shd.g_rec_type
834 ) is
835 --
836 l_proc varchar2(72) := g_package||'update_validate';
837 --
838 Begin
839 hr_utility.set_location('Entering:'||l_proc, 5);
840 --
841 -- Call all supporting business operations
842 --
843 hr_api.validate_bus_grp_id
844 (p_business_group_id => p_rec.business_group_id
845 ,p_associated_column1 => ota_cmb_shd.g_tab_nam
846 || '.BUSINESS_GROUP_ID');
847 --
848 -- After validating the set of important attributes,
849 -- if Multiple Message detection is enabled and at least
850 -- one error has been found then abort further validation.
851 --
852 hr_multi_message.end_validation_set;
853 --
854 -- Validate Dependent Attributes
855 --
856 chk_non_updateable_args
857 (p_effective_date => p_effective_date
858 ,p_rec => p_rec
859 );
860 --
861 --
862 ota_cmb_bus.chk_act_version_dates(p_activity_version_id => p_rec.object_id
863 ,p_cmb_start_date => p_rec.start_date_active
864 ,p_cmb_end_date => p_rec.end_date_active);
865
866 ota_cmb_bus.chk_cert_dates(p_certification_id => p_rec.certification_id
867 ,p_cmb_start_date => p_rec.start_date_active
868 ,p_cmb_end_date => p_rec.end_date_active);
869
870
871 ota_cmb_bus.chk_df(p_rec);
872 --
873 hr_utility.set_location(' Leaving:'||l_proc, 10);
874 End update_validate;
875 --
876 -- ----------------------------------------------------------------------------
877 -- |---------------------------< delete_validate >----------------------------|
878 -- ----------------------------------------------------------------------------
879 Procedure delete_validate
880 (p_rec in ota_cmb_shd.g_rec_type
881 ) is
882 --
883 l_proc varchar2(72) := g_package||'delete_validate';
884 --
885 Begin
886 hr_utility.set_location('Entering:'||l_proc, 5);
887 --
888 -- Call all supporting business operations
889 --
890 hr_utility.set_location(' Leaving:'||l_proc, 10);
891 End delete_validate;
892 --
893 end ota_cmb_bus;