[Home] [Help]
PACKAGE BODY: APPS.OTA_CPR_BUS
Source
1 Package Body ota_cpr_bus as
2 /* $Header: otcprrhi.pkb 120.2 2006/10/09 11:33:40 sschauha noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cpr_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_prerequisite_course_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_prerequisite_course_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_course_prerequisites cpr
35 where cpr.activity_version_id = p_activity_version_id
36 and cpr.prerequisite_course_id = p_prerequisite_course_id
37 and pbg.business_group_id = cpr.business_group_id;
38 --
39 -- Declare local variables
40 --
41 l_security_group_id number;
42 l_proc varchar2(72) := g_package||'set_security_group_id';
43 l_legislation_code varchar2(150);
44 --
45 begin
46 --
47 hr_utility.set_location('Entering:'|| l_proc, 10);
48 --
49 -- Ensure that all the mandatory parameter are not null
50 --
51 hr_api.mandatory_arg_error
52 (p_api_name => l_proc
53 ,p_argument => 'activity_version_id'
54 ,p_argument_value => p_activity_version_id
55 );
56 hr_api.mandatory_arg_error
57 (p_api_name => l_proc
58 ,p_argument => 'prerequisite_course_id'
59 ,p_argument_value => p_prerequisite_course_id
60 );
61 --
62 open csr_sec_grp;
63 fetch csr_sec_grp into l_security_group_id
64 , l_legislation_code;
65 --
66 if csr_sec_grp%notfound then
67 --
68 close csr_sec_grp;
69 --
70 -- The primary key is invalid therefore we must error
71 --
72 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
73 hr_multi_message.add
74 (p_associated_column1
75 => nvl(p_associated_column1,'ACTIVITY_VERSION_ID')
76 ,p_associated_column2
77 => nvl(p_associated_column2,'PREREQUISITE_COURSE_ID')
78 );
79 --
80 else
81 close csr_sec_grp;
82 --
83 -- Set the security_group_id in CLIENT_INFO
84 --
85 hr_api.set_security_group_id
86 (p_security_group_id => l_security_group_id
87 );
88 --
89 -- Set the sessions legislation context in HR_SESSION_DATA
90 --
91 hr_api.set_legislation_context(l_legislation_code);
92 end if;
93 --
94 hr_utility.set_location(' Leaving:'|| l_proc, 20);
95 --
96 end set_security_group_id;
97 --
98 -- ---------------------------------------------------------------------------
99 -- |---------------------< return_legislation_code >-------------------------|
100 -- ---------------------------------------------------------------------------
101 --
102 Function return_legislation_code
103 (p_activity_version_id in number
104 ,p_prerequisite_course_id in number
105 )
106 Return Varchar2 Is
107 --
108 -- Declare cursor
109 --
110 cursor csr_leg_code is
111 select pbg.legislation_code
112 from per_business_groups_perf pbg
113 , ota_course_prerequisites cpr
114 where cpr.activity_version_id = p_activity_version_id
115 and cpr.prerequisite_course_id = p_prerequisite_course_id
116 and pbg.business_group_id = cpr.business_group_id;
117 --
118 -- Declare local variables
119 --
120 l_legislation_code varchar2(150);
121 l_proc varchar2(72) := g_package||'return_legislation_code';
122 --
123 Begin
124 --
125 hr_utility.set_location('Entering:'|| l_proc, 10);
126 --
127 -- Ensure that all the mandatory parameter are not null
128 --
129 hr_api.mandatory_arg_error
130 (p_api_name => l_proc
131 ,p_argument => 'activity_version_id'
132 ,p_argument_value => p_activity_version_id
133 );
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc
136 ,p_argument => 'prerequisite_course_id'
137 ,p_argument_value => p_prerequisite_course_id
138 );
139 --
140 if (( nvl(ota_cpr_bus.g_activity_version_id, hr_api.g_number)
141 = p_activity_version_id)
142 and ( nvl(ota_cpr_bus.g_prerequisite_course_id, hr_api.g_number)
143 = p_prerequisite_course_id)) then
144 --
145 -- The legislation code has already been found with a previous
146 -- call to this function. Just return the value in the global
147 -- variable.
148 --
149 l_legislation_code := ota_cpr_bus.g_legislation_code;
150 hr_utility.set_location(l_proc, 20);
151 else
152 --
153 -- The ID is different to the last call to this function
154 -- or this is the first call to this function.
155 --
156 open csr_leg_code;
157 fetch csr_leg_code into l_legislation_code;
158 --
159 if csr_leg_code%notfound then
160 --
161 -- The primary key is invalid therefore we must error
162 --
163 close csr_leg_code;
164 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
165 fnd_message.raise_error;
166 end if;
167 hr_utility.set_location(l_proc,30);
168 --
169 -- Set the global variables so the values are
170 -- available for the next call to this function.
171 --
172 close csr_leg_code;
173 ota_cpr_bus.g_activity_version_id := p_activity_version_id;
174 ota_cpr_bus.g_prerequisite_course_id := p_prerequisite_course_id;
175 ota_cpr_bus.g_legislation_code := l_legislation_code;
176 end if;
177 hr_utility.set_location(' Leaving:'|| l_proc, 40);
178 return l_legislation_code;
179 end return_legislation_code;
180 --
181 -- ----------------------------------------------------------------------------
182 -- |-----------------------< chk_non_updateable_args >------------------------|
183 -- ----------------------------------------------------------------------------
184 -- {Start Of Comments}
185 --
186 -- Description:
187 -- This procedure is used to ensure that non updateable attributes have
188 -- not been updated. If an attribute has been updated an error is generated.
189 --
190 -- Pre Conditions:
191 -- g_old_rec has been populated with details of the values currently in
192 -- the database.
193 --
194 -- In Arguments:
195 -- p_rec has been populated with the updated values the user would like the
196 -- record set to.
197 --
198 -- Post Success:
199 -- Processing continues if all the non updateable attributes have not
200 -- changed.
201 --
202 -- Post Failure:
203 -- An application error is raised if any of the non updatable attributes
204 -- have been altered.
205 --
206 -- {End Of Comments}
207 -- ----------------------------------------------------------------------------
208 Procedure chk_non_updateable_args
209 (p_effective_date in date
210 ,p_rec in ota_cpr_shd.g_rec_type
211 ) IS
212 --
213 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
214 --
215 Begin
216 --
217 -- Only proceed with the validation if a row exists for the current
218 -- record in the HR Schema.
219 --
220 IF NOT ota_cpr_shd.api_updating
221 (p_activity_version_id => p_rec.activity_version_id
222 ,p_prerequisite_course_id => p_rec.prerequisite_course_id
223 ,p_object_version_number => p_rec.object_version_number
224 ) THEN
225 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
226 fnd_message.set_token('PROCEDURE ', l_proc);
227 fnd_message.set_token('STEP ', '5');
228 fnd_message.raise_error;
229 END IF;
230 --
231 End chk_non_updateable_args;
232 --
233 --
234 -- ----------------------------------------------------------------------------
235 -- |---------------------------< check_unique_key >---------------------------|
236 -- ----------------------------------------------------------------------------
237 --
238 -- PUBLIC
239 -- Description:
240 -- Validates the unique key.
241 -- The activity version id and prerequisite course id must form a unique key.
242 --
243 -- This procedure checks whether the prerequisite course has already been
244 -- attached to the destination course
245
246 Procedure check_unique_key
247 (
248 p_activity_version_id in number
249 ,p_prerequisite_course_id in number
250 ) is
251 --
252 l_exists varchar2(1);
253 l_proc varchar2(72) := g_package||'check_unique_key';
254 --
255 cursor sel_unique_key is
256 select 'Y'
257 from OTA_COURSE_PREREQUISITES cpr
258 where cpr.activity_version_id = p_activity_version_id
259 and cpr.prerequisite_course_id = p_prerequisite_course_id;
260 --
261 Begin
262 hr_utility.set_location('Entering:'|| l_proc, 5);
263 --
264 Open sel_unique_key;
265 fetch sel_unique_key into l_exists;
266 --
267 if sel_unique_key%found then
268 close sel_unique_key;
269
270 fnd_message.set_name('OTA', 'OTA_443707_DUP_CRS_PREREQ');
271 fnd_message.raise_error;
272 end if;
273 close sel_unique_key;
274 --
275 hr_utility.set_location(' Leaving:'|| l_proc, 10);
276 Exception
277 WHEN app_exception.application_exception THEN
278 IF hr_multi_message.exception_add(
279 p_associated_column1 => 'OTA_COURSE_PREREQUISITES.activity_version_id',
280 p_associated_column2 => 'OTA_COURSE_PREREQUISITES.prerequisite_course_id')
281 THEN
282 hr_utility.set_location(' Leaving:'||l_proc, 22);
283 RAISE;
284
285 END IF;
286 hr_utility.set_location(' Leaving:'||l_proc, 25);
287 End check_unique_key;
288 --
289 -- ----------------------------------------------------------------------------
290 -- |----------------------------< check_prereq_course_expiry >----------------|
291 -- ----------------------------------------------------------------------------
292 -- PUBLIC
293 -- Description:
294 -- Validates the expiry date of prerequisite course
295 -- Prerequisite course end date must be greater than or equal to sysdate
296 --
297 Procedure check_prereq_course_expiry
298 (
299 p_prerequisite_course_id in number
300 ) is
301 --
302 cursor get_prereq_crs_end_date is
303 select nvl(end_date, trunc(sysdate))
304 from OTA_ACTIVITY_VERSIONS oav
305 where oav.activity_version_id = p_prerequisite_course_id;
306
307 l_prereq_crs_end_date OTA_ACTIVITY_VERSIONS.END_DATE%TYPE;
308 l_proc varchar2(72) := g_package||'check_prereq_course_expiry';
309 --
310 Begin
311 --
312 hr_utility.set_location('Entering:'|| l_proc, 5);
313 --
314 Open get_prereq_crs_end_date;
315 fetch get_prereq_crs_end_date into l_prereq_crs_end_date;
316 close get_prereq_crs_end_date;
317
318 If ( l_prereq_crs_end_date < trunc(sysdate) ) Then
319 fnd_message.set_name('OTA', 'OTA_443751_PREREQ_CRS_EXPIRED');
320 fnd_message.raise_error;
321 End If;
322 --
323 hr_utility.set_location(' Leaving:'|| l_proc, 10);
324
325 Exception
326 WHEN app_exception.application_exception THEN
327 IF hr_multi_message.exception_add(
328 p_associated_column1 => 'OTA_COURSE_PREREQUISITES.prerequisite_course_id')
329 THEN
330
331 hr_utility.set_location(' Leaving:'||l_proc, 22);
332 RAISE;
333
334 END IF;
335 hr_utility.set_location(' Leaving:'||l_proc, 25);
336 --
337 End check_prereq_course_expiry;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |----------------------------< check_course_start_date >-----------------|
341 -- ----------------------------------------------------------------------------
342 -- PUBLIC
343 -- Description:
344 -- Validates the start date of prerequisite and destination courses
345 -- Prerequisite course start date must be less than or equal to destination
346 -- course start date.
347 --
348 Procedure check_course_start_date
349 (
350 p_activity_version_id in number
351 ,p_prerequisite_course_id in number
352 ) is
353 --
354 cursor get_course_start_date(p_crs_id in number) is
355 select nvl(start_date, trunc(sysdate))
356 from OTA_ACTIVITY_VERSIONS oav
357 where oav.activity_version_id = p_crs_id;
358
359 l_dest_course_start_date OTA_ACTIVITY_VERSIONS.START_DATE%TYPE;
360 l_prereq_course_start_date OTA_ACTIVITY_VERSIONS.START_DATE%TYPE;
361 l_proc varchar2(72) := g_package||'check_course_start_date';
362 --
363 Begin
364 --
365 hr_utility.set_location('Entering:'|| l_proc, 5);
366 --
367 Open get_course_start_date(p_activity_version_id);
368 fetch get_course_start_date into l_dest_course_start_date;
369 close get_course_start_date;
370
371 Open get_course_start_date(p_prerequisite_course_id);
372 fetch get_course_start_date into l_prereq_course_start_date;
373 close get_course_start_date;
374
375 If ( l_prereq_course_start_date > l_dest_course_start_date ) Then
376 fnd_message.set_name('OTA', 'OTA_443708_CRS_PREREQ_ST_DT_GR');
377 fnd_message.raise_error;
378 End If;
379 --
380 hr_utility.set_location(' Leaving:'|| l_proc, 10);
381
382 Exception
386 p_associated_column2 => 'OTA_COURSE_PREREQUISITES.prerequisite_course_id')
383 WHEN app_exception.application_exception THEN
384 IF hr_multi_message.exception_add(
385 p_associated_column1 => 'OTA_COURSE_PREREQUISITES.activity_version_id',
387 THEN
388
389 hr_utility.set_location(' Leaving:'||l_proc, 22);
390 RAISE;
391
392 END IF;
393 hr_utility.set_location(' Leaving:'||l_proc, 25);
394 --
395 End check_course_start_date;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |-------------------------< check_valid_classes_available >-----------------|
399 -- ----------------------------------------------------------------------------
400 -- PUBLIC
401 -- Description:
402 -- Validates whether prerequisite course contains valid classes or not.
403 -- Course should have associated offering and valid classes. Valid classes
404 -- include classes whose class type is SCHEDULED or SELFPACED and whose
405 -- class status is not Cancelled and which are not expired
406 --
407 Procedure check_valid_classes_available
408 (p_prerequisite_course_id in number
409 ) is
410 --
411 cursor get_valid_classes is
412 select 'Y'
413 from OTA_EVENTS oev
414 where oev.ACTIVITY_VERSION_ID = p_prerequisite_course_id
415 and (oev.EVENT_TYPE = 'SCHEDULED' or oev.EVENT_TYPE = 'SELFPACED')
416 and oev.EVENT_STATUS <> 'A'
417 and nvl(trunc(oev.course_end_date), trunc(sysdate)) >= trunc(sysdate);
418
419 l_proc varchar2(72) := g_package||'check_valid_classes_available';
420 l_flag varchar2(1);
421 --
422 Begin
423 --
424 hr_utility.set_location('Entering:'|| l_proc, 5);
425 --
426 Open get_valid_classes;
427 fetch get_valid_classes into l_flag;
428
429 If ( get_valid_classes%notfound ) Then
430 close get_valid_classes;
431
432 fnd_message.set_name('OTA', 'OTA_443709_CRS_PREREQ_NOVLDCLS');
433 fnd_message.raise_error;
434 End If;
435 close get_valid_classes;
436
437 --
438 hr_utility.set_location(' Leaving:'|| l_proc, 10);
439
440 Exception
441 WHEN app_exception.application_exception THEN
442
443 IF hr_multi_message.exception_add(
444 p_associated_column1 => 'OTA_COURSE_PREREQUISITES.prerequisite_course_id')
445 THEN
446
447 hr_utility.set_location(' Leaving:'||l_proc, 22);
448 RAISE;
449
450 END IF;
451 hr_utility.set_location(' Leaving:'||l_proc, 25);
452 --
453 End check_valid_classes_available;
454 --
455 -- ----------------------------------------------------------------------------
456 -- |-------------------------< check_course_chaining >------------------------|
457 -- ----------------------------------------------------------------------------
458 -- PUBLIC
459 -- Description:
460 -- Validates whether specifying prerequisite course for a course results in
461 -- course chaining or not.
462 --
463 Procedure check_course_chaining
464 (
465 p_activity_version_id in number
466 ,p_prerequisite_course_id in number
467 ) is
468 --
469 cursor is_course_chained is
470 select 'Y'
471 from OTA_COURSE_PREREQUISITES cpr
472 where cpr.PREREQUISITE_COURSE_ID = p_activity_version_id
473 and cpr.PREREQUISITE_COURSE_ID in
474 (select PREREQUISITE_COURSE_ID
475 from ota_course_prerequisites
476 start with ACTIVITY_VERSION_ID = p_prerequisite_course_id
477 connect by prior PREREQUISITE_COURSE_ID = ACTIVITY_VERSION_ID);
478
479 l_proc varchar2(72) := g_package||'check_course_chaining';
480 l_flag varchar2(1);
481 --
482 Begin
483 --
484 hr_utility.set_location('Entering:'|| l_proc, 5);
485 --
486 if ( p_activity_version_id = p_prerequisite_course_id ) then
487 fnd_message.set_name('OTA', 'OTA_443727_CRS_PREREQ_CHAINING');
488 fnd_message.raise_error;
489 else
490 Open is_course_chained;
491 fetch is_course_chained into l_flag;
492
493 If ( is_course_chained%found ) Then
494 close is_course_chained;
495
496 fnd_message.set_name('OTA', 'OTA_443727_CRS_PREREQ_CHAINING');
497 fnd_message.raise_error;
498 End If;
499 close is_course_chained;
500 end if;
501
502 --
503 hr_utility.set_location(' Leaving:'|| l_proc, 10);
504
505 Exception
506 WHEN app_exception.application_exception THEN
507
508 IF hr_multi_message.exception_add(
509 p_associated_column1 => 'OTA_COURSE_PREREQUISITES.activity_version_id',
510 p_associated_column2 => 'OTA_COURSE_PREREQUISITES.prerequisite_course_id')
511 THEN
512
513 hr_utility.set_location(' Leaving:'||l_proc, 22);
514 RAISE;
515
516 END IF;
517 hr_utility.set_location(' Leaving:'||l_proc, 25);
518 --
519 End check_course_chaining;
520 --
524 Procedure insert_validate
521 -- ----------------------------------------------------------------------------
522 -- |---------------------------< insert_validate >----------------------------|
523 -- ----------------------------------------------------------------------------
525 (p_effective_date in date
526 ,p_rec in ota_cpr_shd.g_rec_type
527 ) is
528 --
529 l_proc varchar2(72) := g_package||'insert_validate';
530 --
531 Begin
532 hr_utility.set_location('Entering:'||l_proc, 5);
533 --
534 -- Call all supporting business operations
535 --
536
537 hr_api.validate_bus_grp_id
538 (p_business_group_id => p_rec.business_group_id
539 ,p_associated_column1 => ota_cpr_shd.g_tab_nam
540 || '.BUSINESS_GROUP_ID');
541 --
542 -- After validating the set of important attributes,
543 -- if Multiple Message detection is enabled and at least
544 -- one error has been found then abort further validation.
545 --
546 hr_multi_message.end_validation_set;
547 --
548 -- Validate Dependent Attributes
549
550 check_unique_key( p_rec.activity_version_id
551 , p_rec.prerequisite_course_id );
552
553 -- check_valid_classes_available( p_rec.prerequisite_course_id ); --Bug 4452700
554
555 check_prereq_course_expiry( p_rec.prerequisite_course_id );
556
557 check_course_start_date( p_rec.activity_version_id
558 , p_rec.prerequisite_course_id );
559
560 check_course_chaining( p_rec.activity_version_id
561 , p_rec.prerequisite_course_id );
562
563 --
564 hr_utility.set_location(' Leaving:'||l_proc, 10);
565 End insert_validate;
566 --
567 -- ----------------------------------------------------------------------------
568 -- |---------------------------< update_validate >----------------------------|
569 -- ----------------------------------------------------------------------------
570 Procedure update_validate
571 (p_effective_date in date
572 ,p_rec in ota_cpr_shd.g_rec_type
573 ) is
574 --
575 l_proc varchar2(72) := g_package||'update_validate';
576 --
577 Begin
578 hr_utility.set_location('Entering:'||l_proc, 5);
579 --
580 -- Call all supporting business operations
581 --
582
583 hr_api.validate_bus_grp_id
584 (p_business_group_id => p_rec.business_group_id
585 ,p_associated_column1 => ota_cpr_shd.g_tab_nam
586 || '.BUSINESS_GROUP_ID');
587 --
588 -- After validating the set of important attributes,
589 -- if Multiple Message detection is enabled and at least
590 -- one error has been found then abort further validation.
591 --
592 hr_multi_message.end_validation_set;
593 --
594 -- Validate Dependent Attributes
595 --
596 chk_non_updateable_args
597 (p_effective_date => p_effective_date
598 ,p_rec => p_rec
599 );
600 --
601 --
602 hr_utility.set_location(' Leaving:'||l_proc, 10);
603 End update_validate;
604 --
605 -- ----------------------------------------------------------------------------
606 -- |---------------------------< delete_validate >----------------------------|
607 -- ----------------------------------------------------------------------------
608 Procedure delete_validate
609 (p_rec in ota_cpr_shd.g_rec_type
610 ) is
611 --
612 l_proc varchar2(72) := g_package||'delete_validate';
613 --
614 Begin
615 hr_utility.set_location('Entering:'||l_proc, 5);
616 --
617 -- Call all supporting business operations
618 --
619 hr_utility.set_location(' Leaving:'||l_proc, 10);
620 End delete_validate;
621 --
622 end ota_cpr_bus;