[Home] [Help]
PACKAGE BODY: APPS.OTA_ACI_BUS
Source
1 PACKAGE BODY OTA_ACI_BUS as
2 /* $Header: otacirhi.pkb 120.0 2005/05/29 06:51:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_aci_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_activity_version_id number default null;
15 g_category_usage_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_activity_version_id in number
23 ,p_category_usage_id in number
24 ,p_associated_column1 in varchar2 default null
25 ,p_associated_column2 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 cursor csr_sec_grp is
31 select pbg.security_group_id,
32 pbg.legislation_code
33 from per_business_groups_perf pbg
34 , ota_act_cat_inclusions aci
35 , ota_category_usages ctu
36 where aci.activity_version_id = p_activity_version_id
37 and aci.category_usage_id = p_category_usage_id
38 and pbg.business_group_id = ctu.business_group_id
39 and ctu.category_usage_id = aci.category_usage_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 v_proc varchar2(72) := g_package||'set_security_group_id';
45 l_legislation_code varchar2(150);
46 --
47 begin
48 --
49 hr_utility.set_location('Entering:'|| v_proc, 10);
50 --
51 -- Ensure that all the mandatory parameter are not null
52 --
53 hr_api.mandatory_arg_error
54 (p_api_name => v_proc
55 ,p_argument => 'activity_version_id'
56 ,p_argument_value => p_activity_version_id
57 );
58 hr_api.mandatory_arg_error
59 (p_api_name => v_proc
60 ,p_argument => 'category_usage_id'
61 ,p_argument_value => p_category_usage_id
62 );
63 --
64 open csr_sec_grp;
65 fetch csr_sec_grp into l_security_group_id
66 , l_legislation_code;
67 --
68 if csr_sec_grp%notfound then
69 --
70 close csr_sec_grp;
71 --
72 -- The primary key is invalid therefore we must error
73 --
74 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
75 hr_multi_message.add
76 (p_associated_column1
77 => nvl(p_associated_column1,'ACTIVITY_VERSION_ID')
78 ,p_associated_column2
79 => nvl(p_associated_column2,'CATEGORY_USAGE_ID')
80 );
81 --
82 else
83 close csr_sec_grp;
84 --
85 -- Set the security_group_id in CLIENT_INFO
86 --
87 hr_api.set_security_group_id
88 (p_security_group_id => l_security_group_id
89 );
90 --
91 -- Set the sessions legislation context in HR_SESSION_DATA
92 --
93 hr_api.set_legislation_context(l_legislation_code);
94 end if;
95 --
96 hr_utility.set_location(' Leaving:'|| v_proc, 20);
97 --
98 end set_security_group_id;
99 --
100 -- ---------------------------------------------------------------------------
101 -- |---------------------< return_legislation_code >-------------------------|
102 -- ---------------------------------------------------------------------------
103 --
104 Function return_legislation_code
105 (p_activity_version_id in number
106 ,p_category_usage_id in number
107 )
108 Return Varchar2 Is
109 --
110 -- Declare cursor
111 --
112 cursor csr_leg_code is
113 select pbg.legislation_code
114 from per_business_groups_perf pbg
115 , ota_act_cat_inclusions aci
116 , ota_category_usages ctu
117 where aci.activity_version_id = p_activity_version_id
118 and aci.category_usage_id = p_category_usage_id
119 and pbg.business_group_id = ctu.business_group_id
120 and ctu.category_usage_id = aci.category_usage_id ;
121 --
122 -- Declare local variables
123 --
124 l_legislation_code varchar2(150);
125 v_proc varchar2(72) := g_package||'return_legislation_code';
126 --
127 Begin
128 --
129 hr_utility.set_location('Entering:'|| v_proc, 10);
130 --
131 -- Ensure that all the mandatory parameter are not null
132 --
133 hr_api.mandatory_arg_error
134 (p_api_name => v_proc
135 ,p_argument => 'activity_version_id'
136 ,p_argument_value => p_activity_version_id
137 );
138 hr_api.mandatory_arg_error
139 (p_api_name => v_proc
140 ,p_argument => 'category_usage_id'
141 ,p_argument_value => p_category_usage_id
142 );
143 --
144 if (( nvl(ota_aci_bus.g_activity_version_id, hr_api.g_number)
145 = p_activity_version_id)
146 and ( nvl(ota_aci_bus.g_category_usage_id, hr_api.g_number)
147 = p_category_usage_id)) then
148 --
149 -- The legislation code has already been found with a previous
150 -- call to this function. Just return the value in the global
151 -- variable.
152 --
153 l_legislation_code := ota_aci_bus.g_legislation_code;
154 hr_utility.set_location(v_proc, 20);
155 else
156 --
157 -- The ID is different to the last call to this function
158 -- or this is the first call to this function.
159 --
160 open csr_leg_code;
161 fetch csr_leg_code into l_legislation_code;
162 --
163 if csr_leg_code%notfound then
164 --
165 -- The primary key is invalid therefore we must error
166 --
167 close csr_leg_code;
168 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
169 fnd_message.raise_error;
170 end if;
171 hr_utility.set_location(v_proc,30);
172 --
173 -- Set the global variables so the values are
174 -- available for the next call to this function.
175 --
176 close csr_leg_code;
177 ota_aci_bus.g_activity_version_id := p_activity_version_id;
178 ota_aci_bus.g_category_usage_id := p_category_usage_id;
179 ota_aci_bus.g_legislation_code := l_legislation_code;
180 end if;
181 hr_utility.set_location(' Leaving:'|| v_proc, 40);
182 return l_legislation_code;
183 end return_legislation_code;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |-----------------------------< chk_ddf >----------------------------------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- Description:
190 -- Validates all the Developer Descriptive Flexfield values.
191 --
192 -- Prerequisites:
193 -- All other columns have been validated. Must be called as the
194 -- last step from insert_validate and update_validate.
195 --
196 -- In Arguments:
197 -- p_rec
198 --
199 -- Post Success:
200 -- If the Developer Descriptive Flexfield structure column and data values
201 -- are all valid this procedure will end normally and processing will
202 -- continue.
203 --
204 -- Post Failure:
205 -- If the Developer Descriptive Flexfield structure column value or any of
206 -- the data values are invalid then an application error is raised as
207 -- a PL/SQL exception.
208 --
209 -- Access Status:
210 -- Internal Row Handler Use Only.
211 --
212 -- ----------------------------------------------------------------------------
213 procedure chk_ddf
214 (p_rec in ota_aci_shd.g_rec_type
215 ) is
216 --
217 v_proc varchar2(72) := g_package || 'chk_ddf';
218 --
219 begin
220 hr_utility.set_location('Entering:'||v_proc,10);
221 --
222 if (
223 --(p_rec.activity_version_id is not null)
224 --(p_rec.category_usage_id is not null) and
225 (
226 nvl(ota_aci_shd.g_old_rec.aci_information_category, hr_api.g_varchar2) <>
227 nvl(p_rec.aci_information_category, hr_api.g_varchar2) or
228 nvl(ota_aci_shd.g_old_rec.aci_information1, hr_api.g_varchar2) <>
229 nvl(p_rec.aci_information1, hr_api.g_varchar2) or
230 nvl(ota_aci_shd.g_old_rec.aci_information2, hr_api.g_varchar2) <>
231 nvl(p_rec.aci_information2, hr_api.g_varchar2) or
232 nvl(ota_aci_shd.g_old_rec.aci_information3, hr_api.g_varchar2) <>
233 nvl(p_rec.aci_information3, hr_api.g_varchar2) or
234 nvl(ota_aci_shd.g_old_rec.aci_information4, hr_api.g_varchar2) <>
235 nvl(p_rec.aci_information4, hr_api.g_varchar2) or
236 nvl(ota_aci_shd.g_old_rec.aci_information5, hr_api.g_varchar2) <>
237 nvl(p_rec.aci_information5, hr_api.g_varchar2) or
238 nvl(ota_aci_shd.g_old_rec.aci_information6, hr_api.g_varchar2) <>
239 nvl(p_rec.aci_information6, hr_api.g_varchar2) or
240 nvl(ota_aci_shd.g_old_rec.aci_information7, hr_api.g_varchar2) <>
241 nvl(p_rec.aci_information7, hr_api.g_varchar2) or
242 nvl(ota_aci_shd.g_old_rec.aci_information8, hr_api.g_varchar2) <>
243 nvl(p_rec.aci_information8, hr_api.g_varchar2) or
244 nvl(ota_aci_shd.g_old_rec.aci_information9, hr_api.g_varchar2) <>
245 nvl(p_rec.aci_information9, hr_api.g_varchar2) or
246 nvl(ota_aci_shd.g_old_rec.aci_information10, hr_api.g_varchar2) <>
247 nvl(p_rec.aci_information10, hr_api.g_varchar2) or
248 nvl(ota_aci_shd.g_old_rec.aci_information11, hr_api.g_varchar2) <>
249 nvl(p_rec.aci_information11, hr_api.g_varchar2) or
250 nvl(ota_aci_shd.g_old_rec.aci_information12, hr_api.g_varchar2) <>
251 nvl(p_rec.aci_information12, hr_api.g_varchar2) or
252 nvl(ota_aci_shd.g_old_rec.aci_information13, hr_api.g_varchar2) <>
253 nvl(p_rec.aci_information13, hr_api.g_varchar2) or
254 nvl(ota_aci_shd.g_old_rec.aci_information14, hr_api.g_varchar2) <>
255 nvl(p_rec.aci_information14, hr_api.g_varchar2) or
256 nvl(ota_aci_shd.g_old_rec.aci_information15, hr_api.g_varchar2) <>
257 nvl(p_rec.aci_information15, hr_api.g_varchar2) or
258 nvl(ota_aci_shd.g_old_rec.aci_information16, hr_api.g_varchar2) <>
259 nvl(p_rec.aci_information16, hr_api.g_varchar2) or
260 nvl(ota_aci_shd.g_old_rec.aci_information17, hr_api.g_varchar2) <>
261 nvl(p_rec.aci_information17, hr_api.g_varchar2) or
262 nvl(ota_aci_shd.g_old_rec.aci_information18, hr_api.g_varchar2) <>
263 nvl(p_rec.aci_information18, hr_api.g_varchar2) or
264 nvl(ota_aci_shd.g_old_rec.aci_information19, hr_api.g_varchar2) <>
265 nvl(p_rec.aci_information19, hr_api.g_varchar2) or
266 nvl(ota_aci_shd.g_old_rec.aci_information20, hr_api.g_varchar2) <>
267 nvl(p_rec.aci_information20, hr_api.g_varchar2) ))
268 --or (p_rec.activity_version_id is null)
269 -- (p_rec.category_usage_id is null)
270 then
271 --
272 -- Only execute the validation if absolutely necessary:
273 -- a) During update, the structure column value or any
274 -- of the attribute values have actually changed.
275 -- b) During insert.
276 --
277 hr_dflex_utility.ins_or_upd_descflex_attribs
278 (p_appl_short_name => 'OTA'
279 ,p_descflex_name => 'OTA_ACT_CAT_INCLUSIONS'
280 ,p_attribute_category => p_rec.aci_information_category
281 ,p_attribute1_name => 'ACI_INFORMATION1'
282 ,p_attribute1_value => p_rec.aci_information1
283 ,p_attribute2_name => 'ACI_INFORMATION2'
284 ,p_attribute2_value => p_rec.aci_information2
285 ,p_attribute3_name => 'ACI_INFORMATION3'
286 ,p_attribute3_value => p_rec.aci_information3
287 ,p_attribute4_name => 'ACI_INFORMATION4'
288 ,p_attribute4_value => p_rec.aci_information4
289 ,p_attribute5_name => 'ACI_INFORMATION5'
290 ,p_attribute5_value => p_rec.aci_information5
291 ,p_attribute6_name => 'ACI_INFORMATION6'
292 ,p_attribute6_value => p_rec.aci_information6
293 ,p_attribute7_name => 'ACI_INFORMATION7'
294 ,p_attribute7_value => p_rec.aci_information7
295 ,p_attribute8_name => 'ACI_INFORMATION8'
296 ,p_attribute8_value => p_rec.aci_information8
297 ,p_attribute9_name => 'ACI_INFORMATION9'
298 ,p_attribute9_value => p_rec.aci_information9
299 ,p_attribute10_name => 'ACI_INFORMATION10'
300 ,p_attribute10_value => p_rec.aci_information10
301 ,p_attribute11_name => 'ACI_INFORMATION11'
302 ,p_attribute11_value => p_rec.aci_information11
303 ,p_attribute12_name => 'ACI_INFORMATION12'
304 ,p_attribute12_value => p_rec.aci_information12
305 ,p_attribute13_name => 'ACI_INFORMATION13'
306 ,p_attribute13_value => p_rec.aci_information13
307 ,p_attribute14_name => 'ACI_INFORMATION14'
308 ,p_attribute14_value => p_rec.aci_information14
309 ,p_attribute15_name => 'ACI_INFORMATION15'
310 ,p_attribute15_value => p_rec.aci_information15
311 ,p_attribute16_name => 'ACI_INFORMATION16'
312 ,p_attribute16_value => p_rec.aci_information16
313 ,p_attribute17_name => 'ACI_INFORMATION17'
314 ,p_attribute17_value => p_rec.aci_information17
315 ,p_attribute18_name => 'ACI_INFORMATION18'
319 ,p_attribute20_name => 'ACI_INFORMATION20'
316 ,p_attribute18_value => p_rec.aci_information18
317 ,p_attribute19_name => 'ACI_INFORMATION19'
318 ,p_attribute19_value => p_rec.aci_information19
320 ,p_attribute20_value => p_rec.aci_information20
321 );
322 end if;
323 --
324 hr_utility.set_location(' Leaving:'||v_proc,20);
325 end chk_ddf;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |-----------------------< chk_non_updateable_args >------------------------|
329 -- ----------------------------------------------------------------------------
330 -- {Start Of Comments}
331 --
332 -- Description:
333 -- This procedure is used to ensure that non updateable attributes have
334 -- not been updated. If an attribute has been updated an error is generated.
335 --
336 -- Pre Conditions:
337 -- g_old_rec has been populated with details of the values currently in
338 -- the database.
339 --
340 -- In Arguments:
341 -- p_rec has been populated with the updated values the user would like the
342 -- record set to.
343 --
344 -- Post Success:
345 -- Processing continues if all the non updateable attributes have not
346 -- changed.
347 --
348 -- Post Failure:
349 -- An application error is raised if any of the non updatable attributes
350 -- have been altered.
351 --
352 -- {End Of Comments}
353 -- ----------------------------------------------------------------------------
354 Procedure chk_non_updateable_args
355 (p_effective_date in date
356 ,p_rec in ota_aci_shd.g_rec_type
357 ) IS
358 --
359 v_proc varchar2(72) := g_package || 'chk_non_updateable_args';
360 --
361 Begin
362 --
363 -- Only proceed with the validation if a row exists for the current
364 -- record in the HR Schema.
365 --
366 IF NOT ota_aci_shd.api_updating
367 (p_activity_version_id => p_rec.activity_version_id
368 ,p_category_usage_id => p_rec.category_usage_id
369 ,p_object_version_number => p_rec.object_version_number
370 ) THEN
371 fnd_message.set_name('PER', 'HR_6153_ALv_procEDURE_FAIL');
372 fnd_message.set_token('PROCEDURE ', v_proc);
373 fnd_message.set_token('STEP ', '5');
374 fnd_message.raise_error;
375 END IF;
376 --
377 -- EDIT_HERE: Add checks to ensure non-updateable args have
378 -- not been updated.
379 --
380 End chk_non_updateable_args;
381 --
382 -- ----------------------------------------------------------------------------
383 -- |--------------------------< Check_course_category_dates >------------------------|
384 -- ----------------------------------------------------------------------------
385 --
386 -- PRIVATE
387 -- Description:
388 -- Validates the startdate and enddate with respect to category dates.
389 --
390 Procedure Check_course_category_dates
391 (p_activity_version_id in number
392 ,p_category_usage_id in number
393 ) is
394 --
395 -- Declare cursors and local variables
396 --
397 -- Cursor to get dates of primary category.
398
399 CURSOR csr_cat_start_end_date is
400 SELECT
401 start_date_active,
402 nvl(end_date_active, hr_api.g_eot ),
403 type
404 FROM ota_category_usages
405 WHERE category_usage_id =p_category_usage_id;
406
407 CURSOR csr_course_start_end_date IS
408 SELECT
409 start_date,
410 nvl(end_date, hr_api.g_eot)
411 FROM ota_activity_versions
412 WHERE activity_version_id = p_activity_version_id;
413
414
415 --
416 -- Variables for API Boolean parameters
417 l_proc varchar2(72) := g_package ||'Check_course_category_dates';
418 l_cat_start_date date;
419 l_cat_end_date date;
420 l_cate_type varchar2(30);
421 l_course_start_date date;
422 l_course_end_date date;
423
424 Begin
425 hr_utility.set_location(' Entering:' || l_proc,10);
426 --
427 IF hr_multi_message.no_exclusive_error
428 (p_check_column1 => 'ota_activity_versions.START_DATE'
429 ,p_check_column2 => 'ota_activity_versions.END_DATE'
430 ,p_associated_column1 => 'ota_activity_versions.START_DATE'
431 ,p_associated_column2 => 'ota_activity_versions.END_DATE'
432 ) THEN
433 --
434 OPEN csr_cat_start_end_date;
435 FETCH csr_cat_start_end_date into l_cat_start_date, l_cat_end_date,l_cate_type;
436
437 OPEN csr_course_start_end_date;
438 FETCH csr_course_start_end_date into l_course_start_date, l_course_end_date;
439
440 IF csr_cat_start_end_date%FOUND AND csr_course_start_end_date%FOUND THEN
441 CLOSE csr_cat_start_end_date;
442 CLOSE csr_course_start_end_date;
443 hr_utility.set_location(' Cursors found:' || l_proc,10);
444 IF (l_cate_type = 'C') THEN
445 --
446 IF ( l_cat_start_date > l_course_start_date
447 or l_cat_end_date < l_course_end_date
448 ) THEN
449 --
453 End IF;
450 fnd_message.set_name ( 'OTA','OTA_13062_ACT_OUT_OF_CAT_DATES');
451 fnd_message.raise_error;
452 --
454 --
455 End IF;
456 ELSE
457 CLOSE csr_cat_start_end_date;
458 CLOSE csr_course_start_end_date;
459 End IF;
460 End IF;
461 --
462 hr_utility.set_location(' Leaving:' || l_proc,10);
463 Exception
464 when app_exception.application_exception then
465 IF hr_multi_message.exception_add
466 (p_associated_column1 => 'ota_activity_versions.START_DATE'
467 ,p_associated_column2 => 'ota_activity_versions.END_DATE'
468 ) THEN
469 hr_utility.set_location(' Leaving:'|| l_proc,20);
470 raise;
471 END IF;
472
473 hr_utility.set_location(' Leaving:'|| l_proc,30);
474 --
475 End Check_course_category_dates;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |----------------------------< check_category >----------------------------|
479 -- ----------------------------------------------------------------------------
480 --
481 -- PUBLIC
482 -- Description:
483 -- The activity category must be in the domain of 'ACTIVITY_CATEGORY'.
484 --
485 Procedure check_category
486 (
487 p_activity_category in varchar2
488 ) is
489 --
490 v_proc varchar2(72) := g_package||'check_category';
491 --
492 Begin
493 hr_utility.set_location('Entering:'|| v_proc, 5);
494 --
495 -- ota_general.check_domain_value( 'ACTIVITY_CATEGORY', p_activity_category);
496 --
497 hr_utility.set_location(' Leaving:'|| v_proc, 10);
498 End check_category;
499 --
500 --
501 -- ----------------------------------------------------------------------------
502 -- |----------------------------< check_multiple_primary_ctgr >---------------|
503 -- ----------------------------------------------------------------------------
504 --
505 -- PUBLIC
506 -- Description:
507 -- There can be only one primary category for an activity.
508 --
509 Procedure check_multiple_primary_ctgr
510 (
511 p_activity_version_id in number
512 ) is
513 --
514 v_proc varchar2(72) := g_package||'check_multiple_primary_ctgr';
515 v_exists varchar2(1);
516 cursor sel_multiple_primary is
517 select 'Y'
518 from ota_act_cat_inclusions aci
519 where aci.activity_version_id = p_activity_version_id
520 and aci.primary_flag = 'Y';
521 --
522 Begin
523 hr_utility.set_location('Entering:'|| v_proc, 5);
524 --
525 Open sel_multiple_primary;
526 fetch sel_multiple_primary into v_exists;
527 --
528 if sel_multiple_primary%found then
529 close sel_multiple_primary;
530
531 fnd_message.set_name('OTA', 'OTA_13676_DCI_DUPLICATE');
532 fnd_message.raise_error;
533 end if;
534 close sel_multiple_primary;
535 --
536 hr_utility.set_location(' Leaving:'|| v_proc, 10);
537 End check_multiple_primary_ctgr;
538 --
539 --
540 -- ----------------------------------------------------------------------------
541 -- |----------------------------< check_if_primary_category >-----------------|
542 -- ----------------------------------------------------------------------------
543 --
544 -- PUBLIC
545 -- Description:
546 -- Check if an activity category already has a primary category.
547 -- This category cannot be deleted.
548 --
549 Procedure check_if_primary_category
550 (
551 p_activity_version_id in number
552 ,p_category_usage_id in number
553 ) is
554 --
555 v_proc varchar2(72) := g_package||'check_if_primary_category';
556 v_exists varchar2(1);
557 cursor sel_primary_category is
558 select 'Y'
559 from ota_act_cat_inclusions aci
560 where aci.activity_version_id = p_activity_version_id
561 and aci.category_usage_id = p_category_usage_id
562 and aci.primary_flag = 'Y';
563 --
564 Begin
565 hr_utility.set_location('Entering:'|| v_proc, 5);
566 --
567 Open sel_primary_category;
568 fetch sel_primary_category into v_exists;
569 --
570 if sel_primary_category%found then
571 close sel_primary_category;
572
573 fnd_message.set_name('OTA', 'OTA_443266_DCI_DEL_PRIMARY');
574 fnd_message.raise_error;
575 end if;
576 close sel_primary_category;
577 --
578 hr_utility.set_location(' Leaving:'|| v_proc, 10);
579 Exception
580 WHEN app_exception.application_exception THEN
581
582 IF hr_multi_message.exception_add(
583 p_associated_column1 => 'OTA_ACT_CAT_INCLUSIONS.PRIMARY_FLAG')
584
585 THEN
586
587 hr_utility.set_location(' Leaving:'||v_proc, 22);
588 RAISE;
589
590 END IF;
591 hr_utility.set_location(' Leaving:'||v_proc, 25);
592 End check_if_primary_category;
593 --
594 -- ----------------------------------------------------------------------------
598 -- Description:
595 -- |----------------------------< check_start_end_dates >-----------------|
596 -- ----------------------------------------------------------------------------
597 -- PUBLIC
599 -- Validates the startdate and enddate.
600 -- Startdate must be less than, or equal to, enddate.
601 --
602 Procedure check_start_end_dates
603 (
604 p_start_date in date
605 ,p_end_date in date
606 ) is
607 --
608 v_proc varchar2(72) := g_package||'check_start_end_dates';
609 --
610 Begin
611 --
612 hr_utility.set_location('Entering:'|| v_proc, 5);
613 --
614 ota_general.check_start_end_dates( p_start_date, p_end_date);
615 --
616 hr_utility.set_location(' Leaving:'|| v_proc, 10);
617
618 Exception
619 WHEN app_exception.application_exception THEN
620
621 IF hr_multi_message.exception_add(
622 p_associated_column1 => 'OTA_ACT_CAT_INCLUSIONS.START_DATE_ACTIVE'
623 ,p_associated_column2 => 'OTA_ACT_CAT_INCLUSIONS.END_DATE_ACTIVE')
624 THEN
625
626 hr_utility.set_location(' Leaving:'||v_proc, 22);
627 RAISE;
628
629 END IF;
630 hr_utility.set_location(' Leaving:'||v_proc, 25);
631 --
632 End check_start_end_dates;
633 --
634 -- ----------------------------------------------------------------------------
635 -- |-------------------------< check_dates_update >---------------------------|
636 -- ----------------------------------------------------------------------------
637 --
638 -- PUBLIC
639 -- Description:
640 -- Validates the startdate and enddate.
641 -- Update of start and end dates must be within the Category dates
642 -- for this Course Category.
643 --
644 Procedure check_dates_update
645 (
646 p_category_usage_id in number
647 ,p_start_date in date
648 ,p_end_date in date
649 ) is
650 --
651 v_start_date date;
652 v_end_date date;
653 l_error boolean := FALSE;
654 v_proc varchar2(72) := g_package||'check_dates_update';
655 --
656 cursor sel_check_dates is
657 select start_date_active
658 , end_date_active
659 from ota_category_usages aci
660 where aci.category_usage_id = p_category_usage_id;
661 --
662 Begin
663 --
664 hr_utility.set_location('Entering:'|| v_proc, 5);
665 --
666 Open sel_check_dates;
667 Fetch sel_check_dates into v_start_date
668 , v_end_date;
669 --
670 Loop
671 --
672 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
673 --
674
675 If v_start_date is not null Then
676 --
677 -- Child startdate is earlier than parent startdate
678 --
679 If p_start_date is not null Then
680 if p_start_date < v_start_date then
681 --
682 l_error := TRUE;
683 --
684 end if;
685 End if;
686 --
687 -- Child enddate is earlier than parent startdate
688 --
689 If nvl( p_end_date, hr_api.g_eot) < v_start_date Then
690 --
691 l_error := TRUE;
692 --
693 End if;
694 --
695 End if;
696 --
697 -- Existing date for the parent enddate => Boundary parent enddate
698 --
699 If v_end_date is not null Then
700 --
701 -- Child startdate is later than parent enddate
702 --
703 If nvl(p_start_date, hr_api.g_sot) > v_end_date Then
704 --
705 l_error := TRUE;
706 --
707 End if;
708 --
709 -- Child enddate is later than parent enddate
710 --
711 If p_end_date is not null Then
712 if p_end_date > v_end_date then
713 --
714 l_error := TRUE;
715 --
716 end if;
717 End if;
718 --
719 End if;
720 --
721
722 if l_error = true then
723 fnd_message.set_name('OTA', 'OTA_443267_DCI_DATES');
724 fnd_message.raise_error;
725 l_error := false;
726 end if;
727
728 Fetch sel_check_dates into v_start_date
729 , v_end_date;
730 End loop;
731 --
732 Close sel_check_dates;
733 --
734 hr_utility.set_location(' Leaving:'|| v_proc, 10);
735
736 Exception
737 WHEN app_exception.application_exception THEN
738
739 IF hr_multi_message.exception_add(
740 p_associated_column1 => 'OTA_ACT_CAT_INCLUSIONS.START_DATE_ACTIVE'
741 ,p_associated_column2 => 'OTA_ACT_CAT_INCLUSIONS.END_DATE_ACTIVE')
742 THEN
743
744 hr_utility.set_location(' Leaving:'||v_proc, 22);
745 RAISE;
746
747 END IF;
748 hr_utility.set_location(' Leaving:'||v_proc, 25);
749 --
750 End check_dates_update;
751
752 --
753 -- ----------------------------------------------------------------------------
757 -- PUBLIC
754 -- |-------------------------< check_dates_update_act >---------------------------|
755 -- ----------------------------------------------------------------------------
756 --
758 -- Description:
759 -- Validates the startdate and enddate.
760 -- Update of start and end dates must be within the Course dates
761 -- for this Course Category.
762 --
763
764 Procedure check_dates_update_act
765 (
766 p_activity_version_id in number
767 ,p_start_date in date
768 ,p_end_date in date
769 ) is
770 --
771 v_start_date date;
772 v_end_date date;
773 l_error boolean := FALSE;
774 v_proc varchar2(72) := g_package||'check_dates_update_act';
775 --
776 cursor sel_check_dates is
777 select start_date
778 , end_date
779 from ota_activity_versions tav
780 where tav.activity_version_id = p_activity_version_id;
781 --
782 Begin
783 --
784 hr_utility.set_location('Entering:'|| v_proc, 5);
785 --
786 Open sel_check_dates;
787 Fetch sel_check_dates into v_start_date
788 , v_end_date;
789 --
790 Loop
791 --
792 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
793 --
794
795 If v_start_date is not null Then
796 --
797 -- Child startdate is earlier than parent startdate
798 --
799 If p_start_date is not null Then
800 if p_start_date < v_start_date then
801 --
802 l_error := TRUE;
803 --
804 end if;
805 End if;
806 --
807 -- Child enddate is earlier than parent startdate
808 --
809 If nvl( p_end_date, hr_api.g_eot) < v_start_date Then
810 --
811 l_error := TRUE;
812 --
813 End if;
814 --
815 End if;
816 --
817 -- Existing date for the parent enddate => Boundary parent enddate
818 --
819 If v_end_date is not null Then
820 --
821 -- Child startdate is later than parent enddate
822 --
823 If nvl(p_start_date, hr_api.g_sot) > v_end_date Then
824 --
825 l_error := TRUE;
826 --
827 End if;
828 --
829 -- Child enddate is later than parent enddate
830 --
831 If p_end_date is not null Then
832 if p_end_date > v_end_date then
833 --
834 l_error := TRUE;
835 --
836 end if;
837 End if;
838 --
839 End if;
840 --
841
842 if l_error = true then
843 fnd_message.set_name('OTA', 'OTA_443533_ACI_AVT_DATES');
844 fnd_message.raise_error;
845 l_error := false;
846 end if;
847
848 Fetch sel_check_dates into v_start_date
849 , v_end_date;
850 End loop;
851 --
852 Close sel_check_dates;
853 --
854 hr_utility.set_location(' Leaving:'|| v_proc, 10);
855
856 Exception
857 WHEN app_exception.application_exception THEN
858
859 IF hr_multi_message.exception_add(
860 p_associated_column1 => 'OTA_ACT_CAT_INCLUSIONS.START_DATE_ACTIVE'
861 ,p_associated_column2 => 'OTA_ACT_CAT_INCLUSIONS.END_DATE_ACTIVE')
862 THEN
863
864 hr_utility.set_location(' Leaving:'||v_proc, 22);
865 RAISE;
866
867 END IF;
868 hr_utility.set_location(' Leaving:'||v_proc, 25);
869 --
870 End check_dates_update_act;
871
872
873 -- ----------------------------------------------------------------------------
874 -- |---------------------------< check_unique_key >---------------------------|
875 -- ----------------------------------------------------------------------------
876 --
877 -- PUBLIC
878 -- Description:
879 -- Validates the unique key.
880 -- The module version and module category must form a unique key.
881 --
882 Procedure check_unique_key
883 (
884 p_activity_version_id in number
885 ,p_category_usage_id in number
886 ) is
887 --
888 v_exists varchar2(1);
889 v_proc varchar2(72) := g_package||'check_unique_key';
890 --
891 cursor sel_unique_key is
892 select 'Y'
893 from ota_act_cat_inclusions aci
894 where aci.activity_version_id = p_activity_version_id
895 and aci.category_usage_id = p_category_usage_id;
896 --
897 Begin
898 hr_utility.set_location('Entering:'|| v_proc, 5);
899 --
900 Open sel_unique_key;
901 fetch sel_unique_key into v_exists;
902 --
903 if sel_unique_key%found then
904 close sel_unique_key;
905
906 fnd_message.set_name('OTA', 'OTA_13676_DCI_DUPLICATE');
907 fnd_message.raise_error;
908 end if;
909 close sel_unique_key;
910 --
911 hr_utility.set_location(' Leaving:'|| v_proc, 10);
912 Exception
913 WHEN app_exception.application_exception THEN
914
915 IF hr_multi_message.exception_add(
916 p_associated_column1 => 'OTA_ACT_CAT_INCLUSIONS.ACTIVITY_VERSION_ID',
920 RAISE;
917 p_associated_column2 => 'OTA_ACT_CAT_INCLUSIONS.CATEGORY_USAGE_ID')
918 THEN
919 hr_utility.set_location(' Leaving:'||v_proc, 22);
921
922 END IF;
923 hr_utility.set_location(' Leaving:'||v_proc, 25);
924 End check_unique_key;
925 --
926 -- ----------------------------------------------------------------------------
927 -- |---------------------------< insert_validate >----------------------------|
928 -- ----------------------------------------------------------------------------
929 Procedure insert_validate
930 (p_effective_date in date
931 ,p_rec in ota_aci_shd.g_rec_type
932 ,p_activity_version_id in number
933 ,p_category_usage_id in number
934 ) is
935 --
936 v_proc varchar2(72) := g_package||'insert_validate';
937 --
938 Begin
939 hr_utility.set_location('Entering:'||v_proc, 5);
940 --
941 ota_ctu_bus.set_security_group_id(p_category_usage_id);
942 --
943 -- Call all supporting business operations
944 --
945 --
946 check_unique_key( p_rec.activity_version_id
947 , p_rec.category_usage_id );
948 --
949 check_category( p_rec.activity_category );
950 --
951 if p_rec.primary_flag = 'Y' then
952 check_multiple_primary_ctgr(p_rec.activity_version_id);
953 --
954 Check_course_category_dates
955 (p_activity_version_id => p_rec.activity_version_id
956 ,p_category_usage_id => p_rec.category_usage_id);
957 --
958 end if;
959
960 check_start_end_dates(p_rec.start_date_active
961 ,p_rec.end_date_active);
962 --
963 check_dates_update(p_rec.category_usage_id
964 ,p_rec.start_date_active
965 ,p_rec.end_date_active);
966 --
967 check_dates_update_act( p_rec.activity_version_id
968 , p_rec.start_date_active
969 , p_rec.end_date_active);
970 --
971 ota_aci_bus.chk_ddf(p_rec);
972 --
973 hr_utility.set_location(' Leaving:'||v_proc, 10);
974 End insert_validate;
975 --
976 -- ----------------------------------------------------------------------------
977 -- |---------------------------< update_validate >----------------------------|
978 -- ----------------------------------------------------------------------------
979 Procedure update_validate
980 (p_effective_date in date
981 ,p_rec in ota_aci_shd.g_rec_type
982 ) is
983 --
984 v_proc varchar2(72) := g_package||'update_validate';
985 --
986 l_activity_version_id_changed boolean
987 := ota_general.value_changed( ota_aci_shd.g_old_rec.activity_version_id
988 , p_rec.activity_version_id );
989 --
990 l_activity_category_changed boolean
991 := ota_general.value_changed( ota_aci_shd.g_old_rec.activity_category
992 , p_rec.activity_category );
993 l_primary_flag_changed boolean
994 := ota_general.value_changed( ota_aci_shd.g_old_rec.primary_flag
995 , p_rec.primary_flag );
996 --
997 --
998 Begin
999 hr_utility.set_location('Entering:'||v_proc, 5);
1000 --
1004 ota_ctu_bus.set_security_group_id(p_rec.category_usage_id);
1001 -- Call all supporting business operations
1002 --
1003 --
1005 --
1006 --
1007 --
1008 -- Validate Dependent Attributes
1009 --
1010 /*
1011 chk_non_updateable_args
1012 (p_effective_date => p_effective_date
1013 ,p_rec => p_rec
1014 );
1015 */
1016 --
1017 -- Call all supporting business operations
1018 --
1019 If l_activity_version_id_changed Or
1020 l_activity_category_changed Then
1021 --
1022 check_unique_key( p_rec.activity_version_id
1023 , p_rec.category_usage_id );
1024
1025 --
1026 End if;
1027 --
1028 If l_activity_category_changed Then
1029 --
1030 check_category( p_rec.activity_category );
1031 --
1032 End if;
1033
1034 if p_rec.primary_flag = 'Y' then
1035 --
1036 Check_course_category_dates
1037 (p_activity_version_id => p_rec.activity_version_id
1038 ,p_category_usage_id => p_rec.category_usage_id);
1039 --
1040 end if;
1041 --
1042 if not l_primary_flag_changed then
1043 check_start_end_dates(p_rec.start_date_active
1044 ,p_rec.end_date_active);
1045 --
1046 check_dates_update(p_rec.category_usage_id
1047 ,p_rec.start_date_active
1048 ,p_rec.end_date_active);
1049
1050 --
1051 check_dates_update_act( p_rec.activity_version_id
1052 , p_rec.start_date_active
1053 , p_rec.end_date_active);
1054
1055 --
1056 ota_aci_bus.chk_ddf(p_rec);
1057 end if;
1058 --
1059 hr_utility.set_location(' Leaving:'||v_proc, 10);
1060 End update_validate;
1061 --
1062 -- ----------------------------------------------------------------------------
1063 -- |---------------------------< delete_validate >----------------------------|
1064 -- ----------------------------------------------------------------------------
1065 Procedure delete_validate
1066 (p_rec in ota_aci_shd.g_rec_type
1067 ) is
1068 --
1069 v_proc varchar2(72) := g_package||'delete_validate';
1070 --
1071 Begin
1072 hr_utility.set_location('Entering:'||v_proc, 5);
1073 --
1074 check_if_primary_category( p_rec.activity_version_id
1075 ,p_rec.category_usage_id);
1076 --
1077 hr_utility.set_location(' Leaving:'||v_proc, 10);
1078 End delete_validate;
1079 --
1080 end ota_aci_bus;
1081