[Home] [Help]
PACKAGE BODY: APPS.OTA_OFF_BUS
Source
1 PACKAGE BODY OTA_OFF_BUS as
2 /* $Header: otoffrhi.pkb 120.1.12000000.2 2007/02/06 15:25:23 vkkolla noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_off_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_offering_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_offering_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_offerings off
32 where off.offering_id = p_offering_id
33 and pbg.business_group_id = off.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 => 'offering_id'
50 ,p_argument_value => p_offering_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,'OFFERING_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_offering_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_offerings off
102 where off.offering_id = p_offering_id
103 and pbg.business_group_id = off.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 => 'offering_id'
119 ,p_argument_value => p_offering_id
120 );
121 --
122 if ( nvl(ota_off_bus.g_offering_id, hr_api.g_number)
123 = p_offering_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_off_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_off_bus.g_offering_id := p_offering_id;
154 ota_off_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 -- |--------------------------< call_error_message >--------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Passes the error information to the procedure set_message of package
166 -- hr_utility.
167 --
168 Procedure call_error_message
169 (
170 p_error_appl varchar2
171 ,p_error_txt varchar2
172 ) is
173 --
174 v_proc varchar2(72) := g_package||'call_error_message';
175 --
176 Begin
177 --
178 hr_utility.set_location('Entering:'|| v_proc, 5);
179 --
180 -- ** TEMP ** Add error message with the following text.
181 --
182 fnd_message.set_name ( p_error_appl ,p_error_txt);
183 fnd_message.raise_error;
184 --
185 hr_utility.set_location(' Leaving:'|| v_proc, 10);
186 --
187 End call_error_message;
188 --
189 -- ----------------------------------------------------------------------------
190 -- |------------------------------< chk_df >----------------------------------|
191 -- ----------------------------------------------------------------------------
192 --
193 -- Description:
194 -- Validates all the Descriptive Flexfield values.
195 --
196 -- Prerequisites:
197 -- All other columns have been validated. Must be called as the
198 -- last step from insert_validate and update_validate.
199 --
200 -- In Arguments:
201 -- p_rec
202 --
203 -- Post Success:
204 -- If the Descriptive Flexfield structure column and data values are
205 -- all valid this procedure will end normally and processing will
206 -- continue.
207 --
208 -- Post Failure:
209 -- If the Descriptive Flexfield structure column value or any of
210 -- the data values are invalid then an application error is raised as
211 -- a PL/SQL exception.
212 --
213 -- Access Status:
214 -- Internal Row Handler Use Only.
215 --
216 -- ----------------------------------------------------------------------------
217 procedure chk_df
218 (p_rec in ota_off_shd.g_rec_type
219 ) is
220 --
221 l_proc varchar2(72) := g_package || 'chk_df';
222 --
223 begin
224 hr_utility.set_location('Entering:'||l_proc,10);
225 --
226 if ((p_rec.offering_id is not null) and (
227 nvl(ota_off_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
229 nvl(ota_off_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute1, hr_api.g_varchar2) or
231 nvl(ota_off_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute2, hr_api.g_varchar2) or
233 nvl(ota_off_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
234 nvl(p_rec.attribute3, hr_api.g_varchar2) or
235 nvl(ota_off_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
236 nvl(p_rec.attribute4, hr_api.g_varchar2) or
237 nvl(ota_off_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
238 nvl(p_rec.attribute5, hr_api.g_varchar2) or
239 nvl(ota_off_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
240 nvl(p_rec.attribute6, hr_api.g_varchar2) or
241 nvl(ota_off_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
242 nvl(p_rec.attribute7, hr_api.g_varchar2) or
243 nvl(ota_off_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
244 nvl(p_rec.attribute8, hr_api.g_varchar2) or
245 nvl(ota_off_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
246 nvl(p_rec.attribute9, hr_api.g_varchar2) or
247 nvl(ota_off_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
248 nvl(p_rec.attribute10, hr_api.g_varchar2) or
249 nvl(ota_off_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
250 nvl(p_rec.attribute11, hr_api.g_varchar2) or
251 nvl(ota_off_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
252 nvl(p_rec.attribute12, hr_api.g_varchar2) or
253 nvl(ota_off_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
254 nvl(p_rec.attribute13, hr_api.g_varchar2) or
255 nvl(ota_off_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
256 nvl(p_rec.attribute14, hr_api.g_varchar2) or
257 nvl(ota_off_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
258 nvl(p_rec.attribute15, hr_api.g_varchar2) or
259 nvl(ota_off_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
260 nvl(p_rec.attribute16, hr_api.g_varchar2) or
261 nvl(ota_off_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
262 nvl(p_rec.attribute17, hr_api.g_varchar2) or
263 nvl(ota_off_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
264 nvl(p_rec.attribute18, hr_api.g_varchar2) or
265 nvl(ota_off_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
266 nvl(p_rec.attribute19, hr_api.g_varchar2) or
267 nvl(ota_off_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
268 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
269 or (p_rec.offering_id is null) then
270 --
271 -- Only execute the validation if absolutely necessary:
272 -- a) During update, the structure column value or any
273 -- of the attribute values have actually changed.
274 -- b) During insert.
275 --
276 hr_dflex_utility.ins_or_upd_descflex_attribs
277 (p_appl_short_name => 'OTA'
278 ,p_descflex_name => 'OTA_OFFERINGS'
279 ,p_attribute_category => p_rec.attribute_category
280 ,p_attribute1_name => 'ATTRIBUTE1'
281 ,p_attribute1_value => p_rec.attribute1
282 ,p_attribute2_name => 'ATTRIBUTE2'
283 ,p_attribute2_value => p_rec.attribute2
284 ,p_attribute3_name => 'ATTRIBUTE3'
285 ,p_attribute3_value => p_rec.attribute3
286 ,p_attribute4_name => 'ATTRIBUTE4'
287 ,p_attribute4_value => p_rec.attribute4
288 ,p_attribute5_name => 'ATTRIBUTE5'
289 ,p_attribute5_value => p_rec.attribute5
290 ,p_attribute6_name => 'ATTRIBUTE6'
291 ,p_attribute6_value => p_rec.attribute6
292 ,p_attribute7_name => 'ATTRIBUTE7'
293 ,p_attribute7_value => p_rec.attribute7
294 ,p_attribute8_name => 'ATTRIBUTE8'
295 ,p_attribute8_value => p_rec.attribute8
296 ,p_attribute9_name => 'ATTRIBUTE9'
297 ,p_attribute9_value => p_rec.attribute9
298 ,p_attribute10_name => 'ATTRIBUTE10'
299 ,p_attribute10_value => p_rec.attribute10
300 ,p_attribute11_name => 'ATTRIBUTE11'
301 ,p_attribute11_value => p_rec.attribute11
302 ,p_attribute12_name => 'ATTRIBUTE12'
303 ,p_attribute12_value => p_rec.attribute12
304 ,p_attribute13_name => 'ATTRIBUTE13'
305 ,p_attribute13_value => p_rec.attribute13
306 ,p_attribute14_name => 'ATTRIBUTE14'
307 ,p_attribute14_value => p_rec.attribute14
308 ,p_attribute15_name => 'ATTRIBUTE15'
309 ,p_attribute15_value => p_rec.attribute15
310 ,p_attribute16_name => 'ATTRIBUTE16'
311 ,p_attribute16_value => p_rec.attribute16
312 ,p_attribute17_name => 'ATTRIBUTE17'
313 ,p_attribute17_value => p_rec.attribute17
314 ,p_attribute18_name => 'ATTRIBUTE18'
315 ,p_attribute18_value => p_rec.attribute18
316 ,p_attribute19_name => 'ATTRIBUTE19'
317 ,p_attribute19_value => p_rec.attribute19
318 ,p_attribute20_name => 'ATTRIBUTE20'
319 ,p_attribute20_value => p_rec.attribute20
320 );
321 end if;
322 --
323 hr_utility.set_location(' Leaving:'||l_proc,20);
324 end chk_df;
325 --
326 -- ----------------------------------------------------------------------------
327 -- |-----------------------< chk_non_updateable_args >------------------------|
328 -- ----------------------------------------------------------------------------
329 -- {Start Of Comments}
330 --
331 -- Description:
332 -- This procedure is used to ensure that non updateable attributes have
333 -- not been updated. If an attribute has been updated an error is generated.
334 --
335 -- Pre Conditions:
336 -- g_old_rec has been populated with details of the values currently in
337 -- the database.
338 --
339 -- In Arguments:
340 -- p_rec has been populated with the updated values the user would like the
341 -- record set to.
342 --
343 -- Post Success:
344 -- Processing continues if all the non updateable attributes have not
345 -- changed.
346 --
347 -- Post Failure:
348 -- An application error is raised if any of the non updatable attributes
349 -- have been altered.
350 --
351 -- {End Of Comments}
352 -- ----------------------------------------------------------------------------
353 Procedure chk_non_updateable_args
354 (p_effective_date in date
355 ,p_rec in ota_off_shd.g_rec_type
356 ) IS
357 --
358 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
359 --
360 Begin
361 --
362 -- Only proceed with the validation if a row exists for the current
363 -- record in the HR Schema.
364 --
365 IF NOT ota_off_shd.api_updating
366 (p_offering_id => p_rec.offering_id
367 ,p_object_version_number => p_rec.object_version_number
368 ) THEN
369 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
370 fnd_message.set_token('PROCEDURE ', l_proc);
371 fnd_message.set_token('STEP ', '5');
372 fnd_message.raise_error;
373 END IF;
374 --
375 -- EDIT_HERE: Add checks to ensure non-updateable args have
376 -- not been updated.
377 --
378 End chk_non_updateable_args;
379 -------------------------------------------------------------------
380 --
381 -- ----------------------------------------------------------------------------
382 -- |----------------------< Chk_Dm_Start_End_Date >---------------------------|
383 -- ----------------------------------------------------------------------------
384 --
385 Procedure Chk_Dm_Start_End_Date
386 (p_delivery_mode_id in number
387 ,p_start_date in date
388 ,p_end_date in date
389 ) is
390 --
391 -- Declare cursors and local variables
392 --
393 -- Cursor to get value if parent category is already exits in child hierarchy of base category
394
395 CURSOR cur_dm_start_end_date is
396 select
397 ctu.start_date_active,
398 ctu.end_date_active
399 from
400 ota_category_usages ctu
401 where
402 ctu.category_usage_id = p_delivery_mode_id;
403 --
404 -- Variables for API Boolean parameters
405 l_proc varchar2(72) := g_package ||'Chk_Dm_Start_End_Date';
406 v_start_date date;
407 v_end_date date;
408
409 Begin
410 hr_utility.set_location(' Entering:' || l_proc,10);
411 --
412
413 OPEN cur_dm_start_end_date;
414 FETCH cur_dm_start_end_date into v_start_date, v_end_date;
415
416 -- Assignment if v_start_date or v_end_date is null
417 /*
418 If p_end_date is null Then
419 p_end_date := v_end_date;
420 End if;
421 */
422 If ota_general.check_par_child_dates_fun(v_start_date,
423 v_end_date,
424 NVL(p_start_date, hr_api.g_sot),
425 NVL(p_end_date, hr_api.g_eot) ) then
426 --
427 fnd_message.set_name ( 'OTA','OTA_443459_OFF_OUT_OF_DM_DATES');
428 fnd_message.raise_error;
429 End If;
430
431 --
432 CLOSE cur_dm_start_end_date;
433
434 hr_utility.set_location(' Leaving:' || l_proc,10);
435 Exception
436 when app_exception.application_exception then
437 IF hr_multi_message.exception_add
438 (p_associated_column1 => 'OTA_OFFERINGS.START_DATE'
439 ,p_associated_column2 => 'OTA_OFFERINGS.END_DATE'
440 ) THEN
441 hr_utility.set_location(' Leaving:'|| l_proc,20);
442 raise;
443 END IF;
444
445 hr_utility.set_location(' Leaving:'|| l_proc,30);
446 --
447 End Chk_Dm_Start_End_Date;
448 --
449 -- ----------------------------------------------------------------------------
450 -- -------------------------< CLASS_DATES_ARE_VALID >-------------------------
451 -- ----------------------------------------------------------------------------
452 --
453 -- Checks if classes are within the parent offering start date
454 -- and end date.
455 -- N.B. Planned classes may have NULL Dates
456 --
457 --
458
459 procedure CLASS_DATES_ARE_VALID (p_offering_id in number,
460 p_start_date in date,
461 p_end_date in date
462 ) is
463 --
464 l_proc varchar2(30) := 'CLASS_DATES_ARE_VALID';
465 l_start_date date;
466 l_end_date date;
467 l_evt_start_date date;
468 l_evt_end_date date;
469 l_event_status varchar2(1);
470 --
471 cursor events is
472 select course_start_date, course_end_date ,event_status
473 from ota_events
474 where parent_offering_id = p_offering_id;
475 begin
476 --
477 hr_utility.set_location(' Entering:'||l_proc,10);
478 hr_utility.trace('p_offering_id'||p_offering_id);
479 hr_utility.trace('p_start_date'||p_start_date);
480 hr_utility.trace('p_end_date'||p_end_date);
481
482 --
483 l_start_date := p_start_date;
484 l_end_date := p_end_date;
485 --
486 if l_start_date is null then
487 l_start_date := hr_api.g_sot;
488 end if;
489 if l_end_date is null then
490 l_end_date := hr_api.g_eot;
491 end if;
492 --
493 for v_events in events
494 loop
495 --
496 l_evt_start_date := v_events.course_start_date;
497 l_evt_end_date:= v_events.course_end_date;
498 l_event_status:= v_events.event_status;
499 --
500 if l_event_status = 'P' then
501 if l_evt_start_date is null then
502 l_evt_start_date := l_start_date;
503 end if;
504 if l_evt_end_date is null then
505 l_evt_end_date := l_end_date;
506 end if;
507 end if;
508 --
509 if l_evt_end_date is null then
510 l_evt_end_date := hr_api.g_eot;
511 end if;
512 --
513 if l_evt_start_date < l_start_date or
514 l_evt_start_date > l_end_date or
515 l_evt_end_date > l_end_date or
516 l_evt_end_date < l_start_date then
517 fnd_message.set_name('OTA','OTA_443375_OFF_CLASS_DATES');
518 fnd_message.raise_error;
519 end if;
520 end loop;
521 --
522 hr_utility.set_location(' Exiting:'||l_proc,10);
523 Exception
524 WHEN app_exception.application_exception THEN
525
526 IF hr_multi_message.exception_add(
527 p_associated_column1 => 'OTA_OFFERINGS.START_DATE',
528 p_associated_column2 => 'OTA_OFFERINGS.END_DATE')
529 THEN
530
531 --hr_utility.set_location(' Leaving:'||v_proc, 22);
532 RAISE;
533
534 END IF;
535 end CLASS_DATES_ARE_VALID;
536 -------------------------------------------------------------------
537 --
538 -- ----------------------------------------------------------------------------
539 -- -------------------------< OFFERING_DATES_ARE_VALID >-------------------------
540 -- ----------------------------------------------------------------------------
541 --
542 -- Checks if offerings are within the parent course start date
543 -- and end date.
544 --
545 --
546 procedure OFFERING_DATES_ARE_VALID (p_activity_version_id in number,
547 p_offering_start_date in date,
548 p_offering_end_date in date ) is
549 --
550 l_proc varchar2(30) := 'course_dates_are_valid';
551 l_start_date date;
552 l_end_date date;
553 l_offering_start_date date;
554 l_offering_end_date date;
555 --
556 cursor check_dates is
557 select start_date, end_date
558 from ota_activity_versions
559 where activity_version_id = p_activity_version_id;
560 begin
561 --
562 hr_utility.set_location(' Entering:'||l_proc,10);
563 hr_utility.trace('p_activity_version_id'||p_activity_version_id);
564 hr_utility.trace('p_offering_start_date'||p_offering_start_date);
565 hr_utility.trace('p_offering_end_date'||p_offering_end_date);
566 --
567 open check_dates;
568 fetch check_dates into l_start_date, l_end_date;
569 close check_dates;
570 --
571 if p_offering_start_date is not null and p_offering_end_date is not null and p_offering_start_date > p_offering_end_date then
572 fnd_message.set_name('OTA','OTA_13312_GEN_DATE_ORDER');
573 fnd_message.raise_error;
574 end if;
575
576 if l_start_date is null then
577 l_start_date := hr_api.g_sot;
578 end if;
579
580 if l_end_date is null then
581 l_end_date := hr_api.g_eot;
582 end if;
583 --
584 l_offering_start_date := p_offering_start_date;
585 l_offering_end_date := p_offering_end_date;
586 --
587 if l_offering_end_date is null then
588 l_offering_end_date := hr_api.g_eot;
589 end if;
590 --
591 --
592 -- Added extra conditions to handle development events
593 --
594 if l_offering_start_date < l_start_date or
595 l_offering_start_date > l_end_date or
596 l_offering_end_date > l_end_date or
597 l_offering_end_date < l_start_date then
598 fnd_message.set_name('OTA','OTA_443316_OFF_INVALID_DATES');
599 fnd_message.raise_error;
600 end if;
601 --
602 hr_utility.set_location(' Exiting:'||l_proc,10);
603 Exception
604 WHEN app_exception.application_exception THEN
605
606 IF hr_multi_message.exception_add(
607 p_associated_column1 => 'OTA_OFFERINGS.START_DATE',
608 p_associated_column2 => 'OTA_OFFERINGS.END_DATE')
609 THEN
610
611 --hr_utility.set_location(' Leaving:'||v_proc, 22);
612 RAISE;
613
614 END IF;
615 end OFFERING_DATES_ARE_VALID;
616 -- ----------------------------------------------------------------------------
617 -- |---------------------------< chk_competency_update_level >------------------------|
618 -- ----------------------------------------------------------------------------
619 PROCEDURE chk_competency_update_level (p_offering_id IN number
620 ,p_object_version_number IN NUMBER
621 ,p_competency_update_level IN VARCHAR2
622 ,p_effective_date IN date) IS
623
624 --
625 l_proc VARCHAR2(72) := g_package||'chk_competency_update_level';
626 l_api_updating boolean;
627
628 BEGIN
629 hr_utility.set_location(' Leaving:'||l_proc, 10);
630 --
631 -- check mandatory parameters has been set
632 --
633 hr_api.mandatory_arg_error
634 (p_api_name => l_proc
635 ,p_argument => 'effective_date'
636 ,p_argument_value => p_effective_date);
637
638 l_api_updating := ota_off_shd.api_updating
639 (p_offering_id => p_offering_id
640 ,p_object_version_number => p_object_version_number);
641
642
643 IF ((l_api_updating AND
644 NVL(ota_off_shd.g_old_rec.competency_update_level,hr_api.g_varchar2) <>
645 NVL(p_competency_update_level, hr_api.g_varchar2))
646 OR NOT l_api_updating AND p_competency_update_level IS NOT NULL) THEN
647
648 hr_utility.set_location(' Leaving:'||l_proc, 20);
649 --
650
651 IF p_competency_update_level IS NOT NULL THEN
652 IF hr_api.not_exists_in_hr_lookups
653 (p_effective_date => p_effective_date
654 ,p_lookup_type => 'OTA_COMPETENCY_UPDATE_LEVEL'
655 ,p_lookup_code => p_competency_update_level) THEN
656 fnd_message.set_name('OTA','OTA_443411_COMP_UPD_LEV_INVLD');
657 fnd_message.raise_error;
658 END IF;
659 hr_utility.set_location(' Leaving:'||l_proc, 30);
660
661 END IF;
662
663 END IF;
664 hr_utility.set_location(' Leaving:'||l_proc, 40);
665
666 EXCEPTION
667
668 WHEN app_exception.application_exception THEN
669
670 IF hr_multi_message.exception_add
671 (p_associated_column1 => 'OTA_OFFERINGS.COMPETENCY_UPDATE_LEVEL') THEN
672
673 hr_utility.set_location(' Leaving:'||l_proc, 42);
674 RAISE;
675 END IF;
676
677 hr_utility.set_location(' Leaving:'||l_proc, 44);
678
679 END chk_competency_update_level;
680
681 --
682 -- ----------------------------------------------------------------------------
683 -- -----------------------< CHECK_UNIQUE >----------------------------
684 -- ----------------------------------------------------------------------------
685 --
686 -- Validates the uniqueness of the event title (ignoring case).
687 --
688 procedure CHECK_UNIQUE (
689 P_NAME in varchar2,
690 P_BUSINESS_GROUP_ID in number,
691 P_ACTIVITY_VERSION_ID in number,
692 P_OFFERING_ID in number
693 ) is
694 --
695 W_PROC varchar2 (72)
696 := G_PACKAGE || 'CHECK_UNIQUE';
697 --
698 begin
699 --
700 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
701 --
702 -- Do not perform the uniqueness check unless inserting, or updating
703 -- with a value different from the current value (and not just changing
704 -- case)
705 --
706 --
707 if (not UNIQUE_OFFERING_TITLE (
708 P_NAME => P_NAME,
709 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
710 P_ACTIVITY_VERSION_ID => P_ACTIVITY_VERSION_ID,
711 P_OFFERING_ID => P_OFFERING_ID)) then
712 fnd_message.set_name('OTA','OTA_443317_OFF_UNIQUE');
713 fnd_message.raise_error;
714 --
715 end if;
716 --
717 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
718 --
719 Exception
720 WHEN app_exception.application_exception THEN
721
722 IF hr_multi_message.exception_add(
723 p_associated_column1 => 'OTA_OFFERINGS.BUSINESS_GROUP_ID',
724 p_associated_column2 => 'OTA_OFFERINGS.DELIVERY_MODE_ID')
725 THEN
726
727 --hr_utility.set_location(' Leaving:'||v_proc, 22);
728 RAISE;
729
730 END IF;
731
732 end CHECK_UNIQUE;
733 --------------------------------------------------------------------------------
734 --
735 -- ----------------------------------------------------------------------------
736 -- -------------------------< UNIQUE_OFFERING_TITLE >-----------------------------
737 -- ----------------------------------------------------------------------------
738 --
739 -- Returns TRUE if the event has a title which is unique within its
740 -- business group. If the event id is not null, then the check avoids
741 -- comparing the title against itself. Titles are compared regardless
742 -- of case.
743 --
744 --
745 --
746 function UNIQUE_OFFERING_TITLE (
747 P_NAME in varchar2,
748 P_BUSINESS_GROUP_ID in number,
749 P_ACTIVITY_VERSION_ID in number,
750 P_OFFERING_ID in number
751 ) return boolean is
752 --
753 W_PROC varchar2 (72)
754 := G_PACKAGE || 'UNIQUE_OFFERING_TITLE';
755 W_TITLE_IS_UNIQUE boolean;
756 --
757 cursor C1 is
758 SELECT 1 FROM OTA_OFFERINGS_VL OFF
759 WHERE OFF.NAME = P_NAME
760 AND OFF.ACTIVITY_VERSION_ID = P_ACTIVITY_VERSION_ID
761 AND OFF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
762 AND ( OFF.OFFERING_ID <> P_OFFERING_ID or P_OFFERING_ID IS NULL ) ;
763 l_num number(10);
764 --
765 begin
766 --
767 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
768 --
769 -- Check arguments
770 --
771 HR_API.MANDATORY_ARG_ERROR (
772 G_PACKAGE,
773 'P_NAME',
774 P_NAME);
775 HR_API.MANDATORY_ARG_ERROR (
776 G_PACKAGE,
777 'P_BUSINESS_GROUP_ID',
778 P_BUSINESS_GROUP_ID);
779 --
780 -- Unique ?
781 --
782 open C1;
783 fetch C1
784 into l_num;
785 W_TITLE_IS_UNIQUE := C1%notfound;
786 close C1;
787 --
788 HR_UTILITY.SET_LOCATION (W_PROC, 10);
789 return W_TITLE_IS_UNIQUE;
790 --
791 end UNIQUE_OFFERING_TITLE;
792 --
793 --
794 --
795 -- ----------------------------------------------------------------------------
796 -- |-----------------------< check_is_test_selected >-------------------------|
797 -- ----------------------------------------------------------------------------
798 -- Added for Bug 3486188. This procedure throws an error if the show toolbar flag
799 -- or the Exit button for a test learning object is set to No.
800 --
801 --
802 Procedure check_is_test_selected (p_offering_id IN ota_offerings.offering_id%TYPE,
803 p_learning_object_id IN ota_offerings.learning_object_id%TYPE,
804 p_player_toolbar_flag IN ota_offerings.player_toolbar_flag%TYPE,
805 p_player_toolbar_bitset IN ota_offerings.player_toolbar_bitset%TYPE)
806 Is
807 l_proc varchar2(72) := g_package||'check_is_test_selected';
808
809 CURSOR c_is_test
810 IS
811 SELECT test_id
812 FROM ota_learning_objects
813 WHERE learning_object_id = p_learning_object_id;
814
815 l_test_id ota_learning_objects.test_id%TYPE;
816
817 Begin
818 --
819 hr_utility.set_location('Entering:'||l_proc, 5);
820 --
821 OPEN c_is_test;
822 FETCH c_is_test into l_test_id;
823 CLOSE c_is_test;
824
825 IF l_test_id IS NOT NULL THEN
826 IF p_player_toolbar_flag = 'N' THEN
827 fnd_message.set_name('OTA','OTA_13068_OFF_TEST_LO_ERR');
828 fnd_message.raise_error;
829 END IF;
830
831 IF mod(floor(p_player_toolbar_bitset/1),2) <> 1 THEN
832 fnd_message.set_name('OTA','OTA_13068_OFF_TEST_LO_ERR');
833 fnd_message.raise_error;
834 END IF;
835
836 END IF;
837 hr_utility.set_location('Leaving:'||l_proc, 40);
838
839
840 Exception
841 WHEN app_exception.application_exception THEN
842
843 IF hr_multi_message.exception_add(
844 p_associated_column1 => 'OTA_OFFERINGS.PLAYER_TOOLBAR_FLAG',
845 p_associated_column2 => 'OTA_OFFERINGS.PLAYER_TOOLBAR_BITSET')
846 THEN
847
848 hr_utility.set_location(' Leaving:'||l_proc, 22);
849 RAISE;
850
851 END IF;
852 end check_is_test_selected;
853
854 -- ----------------------------------------------------------------------------
855 -- |---------------------------< check_owner_id >-----------------------------|
856 -- ----------------------------------------------------------------------------
857 --
858 -- This function checks to see if any the owner_id exists in
859 -- per_people_f table
860 --
861 --
862 Procedure check_owner_id (p_offering_id in number,
863 p_owner_id in number,
864 p_business_group_id in number,
865 start_date in date)
866 Is
867 l_proc varchar2(72) := g_package||'check_owner_id';
868 CURSOR c_people
869 IS
870 SELECT null
871 FROM Per_all_people_f per
872 WHERE per.person_id = p_owner_id and
873 per.business_group_id = p_business_group_id and
874 NVL(start_date,TRUNC(SYSDATE)) between
875 effective_start_date and effective_end_date;
876 CURSOR c_people_cross
877 IS
878 SELECT null
879 FROM Per_all_people_f per
880 WHERE per.person_id = p_owner_id and
881 NVL(start_date,TRUNC(SYSDATE)) between
882 effective_start_date and effective_end_date;
883 l_exist varchar2(1);
884 --l_cross_business_group varchar2(1):= FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP');
885 l_single_business_group_id number := FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
886 Begin
887 --
888 hr_utility.set_location('Entering:'||l_proc, 5);
889 --
890 if (((p_offering_id is not null) and
891 nvl(ota_off_shd.g_old_rec.owner_id,hr_api.g_number) <>
892 nvl(p_owner_id,hr_api.g_number))
893 or (p_offering_id is null)) then
894 IF p_owner_id is not null then
895 If l_single_business_group_id is not null then
896 hr_utility.set_location('Entering:'||l_proc, 10);
897 OPEN c_people_cross;
898 FETCH c_people_cross into l_exist;
899 if c_people_cross%notfound then
900 close c_people_cross;
901 fnd_message.set_name('OTA','OTA_13887_EVT_OWNER_INVALID');
902 fnd_message.raise_error;
903 end if;
904 close c_people_cross;
905 else
906 hr_utility.set_location('Entering:'||l_proc, 20);
907 OPEN c_people;
908 FETCH c_people into l_exist;
909 if c_people%notfound then
910 close c_people;
911 fnd_message.set_name('OTA','OTA_13887_EVT_OWNER_INVALID');
912 fnd_message.raise_error;
913 end if;
914 close c_people;
915 end if;
916 hr_utility.set_location('Leaving:'||l_proc, 40);
917 END IF;
918 End if;
919 Exception
920 WHEN app_exception.application_exception THEN
921
922 IF hr_multi_message.exception_add(
923 p_associated_column1 => 'OTA_OFFERINGS.OWNER_ID')
924 THEN
925
926 --hr_utility.set_location(' Leaving:'||v_proc, 22);
927 RAISE;
928
929 END IF;
930 end check_owner_id;
931
932 --
933 -- ----------------------------------------------------------------------------
934 -- |----------------------< chk_dm_online_flag >-------------------------------|
935 -- ----------------------------------------------------------------------------
936 --
937 Procedure chk_dm_online_flag
938 (p_delivery_mode_id in ota_category_usages.category_usage_id%TYPE
939 ,p_learning_object_id in ota_learning_objects.learning_object_id%TYPE
940 ,p_activity_version_id in ota_offerings.activity_version_id%TYPE
941 ) is
942 --
943 -- Declare cursors and local variables
944 --
945 -- Cursor to get the online flag of the delivery mode id
946
947 CURSOR cur_dm_online_flag is
948 select
949 ctu.online_flag
950 from
951 ota_category_usages ctu
952 where
953 ctu.category_usage_id = p_delivery_mode_id;
954 --
955 -- Variables for API Boolean parameters
956 l_proc varchar2(72) := g_package ||'chk_dm_online_flag';
957 v_online_flag ota_category_usages.online_flag%TYPE;
958 l_iln_rco_id varchar2(50);
959
960 Begin
961 hr_utility.set_location(' Entering:' || l_proc,10);
962 --
963
964 OPEN cur_dm_online_flag;
965 FETCH cur_dm_online_flag into v_online_flag;
966
967 If v_online_flag = 'Y' AND
968 p_learning_object_id IS NULL then
969 --
970 l_iln_rco_id := ota_utility.get_iln_rco_id(p_activity_version_id);
971 If l_iln_rco_id IS NULL THEN
972 fnd_message.set_name ( 'OTA','OTA_13072_OFF_LO_NULL');
973 fnd_message.raise_error;
974 END IF;
975 End If;
976
977 --
978 CLOSE cur_dm_online_flag;
979
980 hr_utility.set_location(' Leaving:' || l_proc,10);
981 Exception
982 when app_exception.application_exception then
983 IF hr_multi_message.exception_add
984 (p_associated_column1 => 'OTA_OFFERINGS.LEARNING_OBJECT_ID'
985 ) THEN
986 hr_utility.set_location(' Leaving:'|| l_proc,20);
987 raise;
988 END IF;
989
990 hr_utility.set_location(' Leaving:'|| l_proc,30);
991 --
992 End chk_dm_online_flag;
993
994 ---------------------------------------------------------------------
995 ----------------------------------------------------------------------
996 function checkDecimal( p_num number,p_length number) return boolean is
997
998 begin
999
1000 return length(to_char( p_num - trunc(p_num) )) > p_length;
1001
1002 end checkDecimal;
1003
1004
1005 procedure check_attendees(
1006 p_maximum_attendees number
1007 ,p_maximum_internal_attendees number
1008 ,p_minimum_attendees number ) is
1009
1010 begin
1011
1012 if(p_minimum_attendees < 0 or p_maximum_attendees < 0 or p_maximum_internal_attendees < 0)
1013 then
1014 fnd_message.set_name('OTA','OTA_13449_EVT_ATTENDEES_POS');
1015 fnd_message.raise_error;
1016 end if;
1017
1018 if( checkDecimal(p_minimum_attendees,1) or checkDecimal(p_maximum_attendees,1) or checkDecimal(p_maximum_internal_attendees,1))
1019 then
1020 fnd_message.set_name('OTA','OTA_13449_EVT_ATTENDEES_POS');
1021 fnd_message.raise_error;
1022 end if;
1023
1024 if (( p_minimum_attendees is not null and p_maximum_attendees is not null and p_minimum_attendees > p_maximum_attendees
1025 ) or
1026 ( p_maximum_internal_attendees is not null and p_maximum_attendees is not null and p_maximum_internal_attendees > p_maximum_attendees))
1027 then
1028 fnd_message.set_name('OTA','OTA_13449_EVT_ATTENDEES_POS');
1029 fnd_message.raise_error;
1030 end if;
1031 Exception
1032 WHEN app_exception.application_exception THEN
1033
1034 IF hr_multi_message.exception_add(
1035 p_associated_column1 => 'OTA_OFFERINGS.MAXIMUM_ATTENDEES'
1036 ,p_associated_column2 => 'OTA_OFFERINGS.MAXIMUM_INTERNAL_ATTENDEES'
1037 ,p_associated_column3 => 'OTA_OFFERINGS.MINIMUM_ATTENDEES')
1038 THEN
1039
1040 --hr_utility.set_location(' Leaving:'||v_proc, 22);
1041 RAISE;
1042
1043 END IF;
1044
1045 end check_attendees;
1046
1047 procedure check_duration( p_duration number,p_duration_units varchar2) is
1048
1049 begin
1050 if( p_duration < 0 )
1051 then
1052 fnd_message.set_name('OTA','OTA_443368_POSITIVE_NUMBER');
1053 fnd_message.raise_error;
1054 end if;
1055
1056 if( (p_duration is null and p_duration_units is not null) or (p_duration is not null and p_duration_units is null) )
1057 then
1058 fnd_message.set_name('OTA','OTA_13881_NHS_COMB_INVALID');
1059 fnd_message.raise_error;
1060 end if;
1061 Exception
1062 WHEN app_exception.application_exception THEN
1063
1064 IF hr_multi_message.exception_add(
1065 p_associated_column1 => 'OTA_OFFERINGS.DURATION'
1066 ,p_associated_column2 => 'OTA_OFFERINGS.DURATION_UNITS')
1067 THEN
1068
1069 --hr_utility.set_location(' Leaving:'||v_proc, 22);
1070 RAISE;
1071
1072 END IF;
1073 end check_duration;
1074
1075 procedure check_amount(p_actual_cost number,p_budget_cost number,p_standard_price number,
1076 p_budget_currency_code varchar2 ,p_price_basis varchar2,p_currency_code varchar2)
1077 is
1078 begin
1079 if( p_actual_cost < 0 or p_budget_cost < 0 or p_standard_price < 0 or checkDecimal(p_actual_cost,3)
1080 or checkDecimal(p_budget_cost,3) or checkDecimal(p_standard_price,3) )
1081 then
1082 fnd_message.set_name('OTA','OTA_443354_OFF_AMT_NEGATIVE');
1083 fnd_message.raise_error;
1084 end if;
1085
1086 if( ( p_actual_cost is not null or p_budget_cost is not null ) and p_budget_currency_code is null ) then
1087 fnd_message.set_name('OTA','OTA_13394_TAV_COST_ATTR');
1088 fnd_message.raise_error;
1089 end if;
1090
1091 if ( p_price_basis is not null and p_price_basis ='S' and (p_standard_price is null or p_currency_code is null)) then
1092 fnd_message.set_name('OTA','OTA_443348_SE_AMOUNT_FIELD_NUL');
1093 fnd_message.raise_error;
1094 end if;
1095
1096 if ( p_price_basis is not null and p_price_basis ='C' and (p_standard_price is not null or p_currency_code is null)) then
1097 fnd_message.set_name('OTA','OTA_443348_SE_AMOUNT_FIELD_NUL');
1098 fnd_message.raise_error;
1099 end if;
1100
1101
1102 Exception
1103 WHEN app_exception.application_exception THEN
1104
1105 IF hr_multi_message.exception_add(
1106 p_associated_column1 => 'OTA_OFFERINGS.ACTUAL_COST'
1107 ,p_associated_column2 => 'OTA_OFFERINGS.BUDGET_COST'
1108 ,p_associated_column3 => 'OTA_OFFERINGS.STANDARD_PRICE')
1109 THEN
1110
1111 --hr_utility.set_location(' Leaving:'||v_proc, 22);
1112 RAISE;
1113
1114 END IF;
1115 end check_amount;
1116
1117 --
1118 -- ----------------------------------------------------------------------------
1119 -- |---------------------< check_vendor >----------------------------------|
1120 -- ----------------------------------------------------------------------------
1121 --
1122 procedure check_vendor (p_vendor_id in number,p_date date) is
1123 --
1124 v_proc varchar2(72) := g_package||'check_vendor';
1125 begin
1126 --
1127 hr_utility.set_location('Entering:'|| v_proc, 5);
1128 --
1129 ota_general.check_vendor_is_valid(p_vendor_id,p_date);
1130 --
1131 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1132 --
1133 exception
1134 when app_exception.application_exception then
1135 if hr_multi_message.exception_add
1136 (p_associated_column1 => 'OTA_OFFERINGS.VENDOR_ID'
1137 ) then
1138 hr_utility.set_location(' Leaving:'|| v_proc,70);
1139 raise;
1140 end if;
1141 hr_utility.set_location(' Leaving:'|| v_proc,80);
1142 end check_vendor;
1143 --
1144 -- ----------------------------------------------------------------------------
1145 -- |-----------------------< Chk_Inventory_Course >---------------------------|
1146 -- ----------------------------------------------------------------------------
1147 Procedure Chk_Inventory_Course
1148 (p_delivery_mode_id in ota_offerings.delivery_mode_id%TYPE
1149 ,p_activity_version_id in ota_offerings.activity_version_id%TYPE
1150 ) is
1151 --
1152 -- Declare cursors and local variables
1153 --
1154 -- Cursor to get value if offering DM is not Offline Synchronous and the
1155 -- course is linked with inventory.
1156
1157 CURSOR Cur_Inventory_Course is
1158 select
1159 'found'
1160 From
1161 ota_category_usages dm
1162 where
1163 dm.category_usage_id = p_delivery_mode_id
1164 and (dm.online_flag <> 'N' or dm.synchronous_flag <> 'Y')
1165 and exists
1166 (select '1'
1167 from ota_activity_versions tav
1168 where tav.activity_version_id = p_activity_version_id
1169 and inventory_item_id is not null);
1170
1171 -- Variables for API Boolean parameters
1172 l_proc varchar2(72) := g_package ||'Chk_Inventory_Course';
1173 l_inv_org_flag varchar2(10);
1174
1175 Begin
1176 hr_utility.set_location(' Entering:' || l_proc,10);
1177 --
1178 OPEN Cur_Inventory_Course;
1179 FETCH Cur_Inventory_Course into l_inv_org_flag;
1180 --
1181 If Cur_Inventory_Course%FOUND Then
1182 --
1183 CLOSE Cur_Inventory_Course;
1184 --
1185 fnd_message.set_name ( 'OTA', 'OTA_443961_OFF_INV_CRS_ERR');
1186 fnd_message.raise_error;
1187 Else
1188 --
1189 CLOSE Cur_Inventory_Course;
1190 End If;
1191 --
1192 hr_utility.set_location(' Leaving:' || l_proc,10);
1193 Exception
1194 When app_exception.application_exception Then
1195 --
1196 If hr_multi_message.exception_add
1197 (p_associated_column1 => 'OTA_OFFERINGS.DELIVERY_MODE_ID'
1198 ) Then
1199 --
1200 hr_utility.set_location(' Leaving:'|| l_proc,20);
1201 raise;
1202 --
1203 End If;
1204 --
1205 hr_utility.set_location(' Leaving:'|| l_proc,30);
1206 --
1207 End Chk_Inventory_Course;
1208 --
1209
1210
1211 procedure VALIDITY_CHECKS (
1212 P_REC in out nocopy OTA_OFF_SHD.G_REC_TYPE
1213 ,p_name in varchar
1214 ) is
1215 --
1216 l_owner_id_changed boolean
1217 := ota_general.value_changed(ota_off_shd.g_old_rec.owner_id,
1218 p_rec.owner_id);
1219 l_start_date_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.start_date,
1220 p_rec.start_date);
1221 l_end_date_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.end_date,
1222 p_rec.end_date);
1223
1224 l_maximum_attendees_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.maximum_attendees,
1225 p_rec.maximum_attendees);
1226 l_maximum_int_att_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.maximum_internal_attendees,
1227 p_rec.maximum_internal_attendees);
1228 l_minimum_attendees_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.minimum_attendees,
1229 p_rec.minimum_attendees);
1230 l_actual_cost_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.actual_cost,
1231 p_rec.actual_cost);
1232 l_budget_cost_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.budget_cost,
1233 p_rec.budget_cost);
1234 l_standard_price_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.standard_price,
1235 p_rec.standard_price);
1236 l_duration_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.duration,
1237 p_rec.duration);
1238 l_duration_units_changed boolean := ota_general.value_changed(ota_off_shd.g_old_rec.duration_units,
1239 p_rec.duration_units);
1240
1241 l_vendor_id_changed boolean := ota_general.value_changed( ota_off_shd.g_old_rec.vendor_id, p_rec.vendor_id);
1242
1243 l_budget_currency_code_changed boolean := ota_general.value_changed( ota_off_shd.g_old_rec.budget_currency_code, p_rec.budget_currency_code);
1244
1245 l_price_basis_changed boolean := ota_general.value_changed( ota_off_shd.g_old_rec.price_basis, p_rec.price_basis);
1246
1247 l_currency_code_changed boolean := ota_general.value_changed( ota_off_shd.g_old_rec.currency_code, p_rec.currency_code);
1248
1249 begin
1250 CHECK_UNIQUE (
1251 p_name,
1252 p_rec.business_group_id,
1253 p_rec.ACTIVITY_VERSION_ID,
1254 p_rec.OFFERING_ID
1255 );
1256
1257 ota_off_bus.chk_competency_update_level (p_offering_id => p_rec.offering_id
1258 ,p_object_version_number => p_rec.object_version_number
1259 ,p_competency_update_level => p_rec.competency_update_level
1260 ,p_effective_date => trunc(sysdate));
1261
1262 if l_duration_changed or l_duration_units_changed
1263 then
1264 check_duration(p_rec.duration,p_rec.duration_units);
1265 end if;
1266
1267 if l_maximum_attendees_changed or l_maximum_int_att_changed or l_minimum_attendees_changed
1268 then
1269 check_attendees( p_rec.maximum_attendees,p_rec.maximum_internal_attendees,p_rec.minimum_attendees);
1270 end if;
1271
1272 if l_start_date_changed or l_end_date_changed
1273 then
1274 OFFERING_DATES_ARE_VALID(p_rec.activity_version_id,p_rec.start_date,p_rec.end_date);
1275 CLASS_DATES_ARE_VALID (p_rec.OFFERING_ID, p_rec.start_date,p_rec.end_date );
1276 --
1277 Chk_Dm_Start_End_Date
1278 (p_delivery_mode_id => p_rec.delivery_mode_id
1279 ,p_start_date => p_rec.start_date
1280 ,p_end_date => p_rec.end_date
1281 );
1282 --
1283 end if;
1284
1285 if l_owner_id_changed then
1286 check_owner_id (p_rec.offering_id,
1287 p_rec.owner_id,
1288 p_rec.business_group_id,
1289 p_rec.start_date);
1290
1291
1292 end if;
1293
1294
1295 if l_actual_cost_changed or l_budget_cost_changed or l_standard_price_changed or l_budget_currency_code_changed or l_price_basis_changed or l_currency_code_changed
1296 then
1297 check_amount(p_rec.actual_cost,p_rec.budget_cost,p_rec.standard_price,p_rec.budget_currency_code,p_rec.price_basis,p_rec.currency_code);
1298 end if;
1299
1300 if l_vendor_id_changed
1301 then
1302 check_vendor(p_rec.vendor_id,p_rec.start_date);
1303 end if;
1304
1305 --bug 3607018
1306 chk_dm_online_flag(p_rec.delivery_mode_id,
1307 p_rec.learning_object_id,
1308 p_rec.activity_version_id);
1309
1310 --Bug 3486188
1311 IF p_rec.learning_object_id IS NOT NULL THEN
1312
1313 check_is_test_selected(p_offering_id => p_rec.offering_id,
1314 p_learning_object_id => p_rec.learning_object_id,
1315 p_player_toolbar_flag => p_rec.player_toolbar_flag,
1316 p_player_toolbar_bitset => p_rec.player_toolbar_bitset);
1317 END IF;
1318 --Bug 3486188
1319
1320 --Bug#4612340
1321 --
1322 Chk_Inventory_Course
1323 (p_delivery_mode_id => p_rec.delivery_mode_id
1324 ,p_activity_version_id => p_rec.activity_version_id
1325 );
1326 --
1327 --Bug#4612340
1328
1329 End VALIDITY_CHECKS;
1330 --
1331 -- ----------------------------------------------------------------------------
1332 -- |---------------------------< insert_validate >----------------------------|
1333 -- ----------------------------------------------------------------------------
1334 Procedure insert_validate
1335 (p_effective_date in date
1336 ,p_rec in out nocopy ota_off_shd.g_rec_type
1337 ,p_name in varchar
1338 ) is
1339 --
1340 l_proc varchar2(72) := g_package||'insert_validate';
1341 --
1342 Begin
1343 hr_utility.set_location('Entering:'||l_proc, 5);
1344 --
1345 -- Call all supporting business operations
1346 --
1347 hr_api.validate_bus_grp_id
1348 (p_business_group_id => p_rec.business_group_id
1349 ,p_associated_column1 => ota_off_shd.g_tab_nam
1350 || '.BUSINESS_GROUP_ID');
1351 --
1352 VALIDITY_CHECKS ( P_REC => P_REC,p_name => p_name );
1353 --
1354 -- After validating the set of important attributes,
1355 -- if Multiple Message detection is enabled and at least
1356 -- one error has been found then abort further validation.
1357 --
1358 hr_multi_message.end_validation_set;
1359 --
1360 -- Validate Dependent Attributes
1361 --
1362 --
1363
1364 -- 2733966
1365 If p_rec.language_code IS NULL THEN
1366 fnd_message.set_name ( 'OTA','OTA_467063_MAND_LANGUAGE_CODE');
1367 fnd_message.raise_error;
1368 END IF;
1369
1370
1371 ota_off_bus.chk_df(p_rec);
1372
1373 --
1374 hr_utility.set_location(' Leaving:'||l_proc, 10);
1375 End insert_validate;
1376 --
1377 -- ----------------------------------------------------------------------------
1378 -- |---------------------------< update_validate >----------------------------|
1379 -- ----------------------------------------------------------------------------
1380 Procedure update_validate
1381 (p_effective_date in date
1382 ,p_rec in out nocopy ota_off_shd.g_rec_type
1383 ,p_name in varchar2
1384 ) is
1385 --
1386 l_proc varchar2(72) := g_package||'update_validate';
1387 l_owner_id_changed boolean
1388 := ota_general.value_changed(ota_off_shd.g_old_rec.owner_id,
1389 p_rec.owner_id);
1390
1391 --
1392 Begin
1393 hr_utility.set_location('Entering:'||l_proc, 5);
1394 --
1395 -- Call all supporting business operations
1396 --
1397 hr_api.validate_bus_grp_id
1398 (p_business_group_id => p_rec.business_group_id
1399 ,p_associated_column1 => ota_off_shd.g_tab_nam
1400 || '.BUSINESS_GROUP_ID');
1401
1402 VALIDITY_CHECKS ( p_rec => P_REC,p_name => p_name );
1403
1404
1405
1406 --
1407 -- After validating the set of important attributes,
1408 -- if Multiple Message detection is enabled and at least
1409 -- one error has been found then abort further validation.
1410 --
1411 hr_multi_message.end_validation_set;
1412 --
1413 -- Validate Dependent Attributes
1414 --
1415 chk_non_updateable_args
1416 (p_effective_date => p_effective_date
1417 ,p_rec => p_rec
1418 );
1419 --
1420 --
1421
1422
1423 -- 2733966
1424 If p_rec.language_code IS NULL THEN
1425 fnd_message.set_name ( 'OTA','OTA_467063_MAND_LANGUAGE_CODE');
1426 fnd_message.raise_error;
1427 END IF;
1428
1429
1430 ota_off_bus.chk_df(p_rec);
1431
1432 --
1433 hr_utility.set_location(' Leaving:'||l_proc, 10);
1434 End update_validate;
1435 --
1436 -- ----------------------------------------------------------------------------
1437 -- |---------------------------< delete_validate >----------------------------|
1438 -- ----------------------------------------------------------------------------
1439 Procedure delete_validate
1440 (p_rec in ota_off_shd.g_rec_type
1441 ) is
1442 --
1443 l_proc varchar2(72) := g_package||'delete_validate';
1444 --
1445 Begin
1446 hr_utility.set_location('Entering:'||l_proc, 5);
1447 --
1448 -- Call all supporting business operations
1449 --
1450 check_if_evt_exists( p_offering_id => p_rec.offering_id );
1451 check_if_comp_exists( p_offering_id => p_rec.offering_id );
1452 hr_utility.set_location(' Leaving:'||l_proc, 10);
1453 End delete_validate;
1454 --
1455 -- ----------------------------------------------------------------------------
1456 -- |-------------------------< check_if_evt_exists >--------------------------|
1457 -- ----------------------------------------------------------------------------
1458 --
1459 -- PUBLIC
1460 -- Description:
1461 -- Delete Validation.
1462 -- This activity version may not be deleted if child rows in
1463 -- ota_events exist.
1464 --
1465 Procedure check_if_evt_exists
1466 (
1467 p_offering_id in number
1468 ) is
1469 --
1470 v_exists varchar2(1);
1471 v_proc varchar2(72) := g_package||'check_if_evt_exists';
1472 --
1473 cursor sel_evt_exists is
1474 select 'Y'
1475 from ota_events evt
1476 where evt.parent_offering_id = p_offering_id;
1477 --
1478 Begin
1479 --
1480 hr_utility.set_location('Entering:'|| v_proc, 5);
1481 --
1482 Open sel_evt_exists;
1483 fetch sel_evt_exists into v_exists;
1484 --
1485 if sel_evt_exists%found then
1486 --
1487 close sel_evt_exists;
1488 --
1489 -- ** TEMP ** Add error message with the following text.
1490 --
1491 call_error_message( p_error_appl => 'OTA'
1492 , p_error_txt => 'OTA_443238_OFF_DEL_EVT_EXISTS'
1493 );
1494 --
1495 end if;
1496 --
1497 close sel_evt_exists;
1498 --
1499 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1500 --
1501 End check_if_evt_exists;
1502 --
1503 --
1504 -- ----------------------------------------------------------------------------
1505 -- |-------------------------< check_if_comp_exists >--------------------------|
1506 -- ----------------------------------------------------------------------------
1507 --
1508 -- PUBLIC
1509 -- Description:
1510 -- Delete Validation.
1511 -- This activity version may not be deleted if child rows in
1512 -- ota_events exist.
1513 --
1514 Procedure check_if_comp_exists
1515 (
1516 p_offering_id in number
1517 ) is
1518 --
1519 v_exists varchar2(1);
1520 v_proc varchar2(72) := g_package||'check_if_comp_exists';
1521 --
1522 cursor sel_comp_exists is
1523 select 'Y'
1524 from per_competence_elements comp
1525 where comp.object_id = p_offering_id
1526 and comp.type = 'OTA_OFFERING'; --bug 3691224
1527 --
1528 Begin
1529 --
1530 hr_utility.set_location('Entering:'|| v_proc, 5);
1531 --
1532 Open sel_comp_exists;
1533 fetch sel_comp_exists into v_exists;
1534 --
1535 if sel_comp_exists%found then
1536 --
1537 close sel_comp_exists;
1538 --
1539 -- ** TEMP ** Add error message with the following text.
1540 --
1541 call_error_message( p_error_appl => 'OTA'
1542 , p_error_txt => 'OTA_443328_OFF_DEL_COMP_EXSITS'
1543 );
1544 --
1545 end if;
1546 --
1547 close sel_comp_exists;
1548 --
1549 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1550 --
1551 End check_if_comp_exists;
1552 --
1553 end ota_off_bus;