[Home] [Help]
PACKAGE BODY: APPS.OTA_CTU_BUS
Source
1 Package Body ota_ctu_bus as
2 /* $Header: otcturhi.pkb 120.2 2005/09/29 05:31:13 pgupta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ctu_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_category_usage_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_category_usage_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_category_usages ctu
32 where ctu.category_usage_id = p_category_usage_id
33 and pbg.business_group_id = ctu.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 => 'category_usage_id'
50 ,p_argument_value => p_category_usage_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,'CATEGORY_USAGE_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_category_usage_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_category_usages ctu
102 where ctu.category_usage_id = p_category_usage_id
103 and pbg.business_group_id = ctu.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 => 'category_usage_id'
119 ,p_argument_value => p_category_usage_id
120 );
121 --
122 if ( nvl(ota_ctu_bus.g_category_usage_id, hr_api.g_number)
123 = p_category_usage_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_ctu_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_ctu_bus.g_category_usage_id := p_category_usage_id;
154 ota_ctu_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in ota_ctu_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.category_usage_id is not null) and (
198 nvl(ota_ctu_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(ota_ctu_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(ota_ctu_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(ota_ctu_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(ota_ctu_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(ota_ctu_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(ota_ctu_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(ota_ctu_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(ota_ctu_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(ota_ctu_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(ota_ctu_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(ota_ctu_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(ota_ctu_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(ota_ctu_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(ota_ctu_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(ota_ctu_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(ota_ctu_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(ota_ctu_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(ota_ctu_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(ota_ctu_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(ota_ctu_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2)))
240 or (p_rec.category_usage_id is null) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'OTA'
249 ,p_descflex_name => 'OTA_CATEGORY_USAGES'
250 ,p_attribute_category => p_rec.attribute_category
251 ,p_attribute1_name => 'ATTRIBUTE1'
252 ,p_attribute1_value => p_rec.attribute1
253 ,p_attribute2_name => 'ATTRIBUTE2'
254 ,p_attribute2_value => p_rec.attribute2
255 ,p_attribute3_name => 'ATTRIBUTE3'
256 ,p_attribute3_value => p_rec.attribute3
257 ,p_attribute4_name => 'ATTRIBUTE4'
258 ,p_attribute4_value => p_rec.attribute4
259 ,p_attribute5_name => 'ATTRIBUTE5'
260 ,p_attribute5_value => p_rec.attribute5
261 ,p_attribute6_name => 'ATTRIBUTE6'
262 ,p_attribute6_value => p_rec.attribute6
263 ,p_attribute7_name => 'ATTRIBUTE7'
264 ,p_attribute7_value => p_rec.attribute7
265 ,p_attribute8_name => 'ATTRIBUTE8'
266 ,p_attribute8_value => p_rec.attribute8
267 ,p_attribute9_name => 'ATTRIBUTE9'
268 ,p_attribute9_value => p_rec.attribute9
269 ,p_attribute10_name => 'ATTRIBUTE10'
270 ,p_attribute10_value => p_rec.attribute10
271 ,p_attribute11_name => 'ATTRIBUTE11'
272 ,p_attribute11_value => p_rec.attribute11
273 ,p_attribute12_name => 'ATTRIBUTE12'
274 ,p_attribute12_value => p_rec.attribute12
275 ,p_attribute13_name => 'ATTRIBUTE13'
276 ,p_attribute13_value => p_rec.attribute13
277 ,p_attribute14_name => 'ATTRIBUTE14'
278 ,p_attribute14_value => p_rec.attribute14
279 ,p_attribute15_name => 'ATTRIBUTE15'
280 ,p_attribute15_value => p_rec.attribute15
281 ,p_attribute16_name => 'ATTRIBUTE16'
282 ,p_attribute16_value => p_rec.attribute16
283 ,p_attribute17_name => 'ATTRIBUTE17'
284 ,p_attribute17_value => p_rec.attribute17
285 ,p_attribute18_name => 'ATTRIBUTE18'
286 ,p_attribute18_value => p_rec.attribute18
287 ,p_attribute19_name => 'ATTRIBUTE19'
288 ,p_attribute19_value => p_rec.attribute19
289 ,p_attribute20_name => 'ATTRIBUTE20'
290 ,p_attribute20_value => p_rec.attribute20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_df;
296 --
297 -- ----------------------------------------------------------------------------
298 -- ----------------------< Chk_Parent_Category_Dates >------------------------|
299 -- ----------------------------------------------------------------------------
300 --
301
302 Procedure Chk_Parent_Category_Dates
303 (
304 p_parent_cat_usage_id in number
305 ,p_start_date in date
306 ,p_end_date in date
307 ) is
308 --
309 -- Declare cursors and local variables
310 --
311 -- Cursor to get value if parent category is already exits in child hierarchy of base category
312
313 CURSOR cur_par_cat_start_end_date is
314 select
315 ctu.start_date_active,
316 nvl(ctu.end_date_active, hr_api.g_eot)
317 from
318 ota_category_usages ctu
319 where
320 ctu.category_usage_id = p_parent_cat_usage_id;
321 --
322 -- Variables for API Boolean parameters
323 l_proc varchar2(72) := g_package ||'Chk_Parent_Category_Dates';
324 l_par_cat_start_date date;
325 l_par_cat_end_date date;
326
327 Begin
328 hr_utility.set_location(' Entering:' || l_proc,10);
329 --
330 IF hr_multi_message.no_exclusive_error
331 (p_check_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
332 ,p_check_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
333 ,p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
334 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
335 ) THEN
336 --
337 OPEN cur_par_cat_start_end_date;
338 FETCH cur_par_cat_start_end_date into l_par_cat_start_date, l_par_cat_end_date;
339
340 IF cur_par_cat_start_end_date%FOUND THEN
341 CLOSE cur_par_cat_start_end_date;
342 IF ( l_par_cat_start_date > p_start_date
343 or l_par_cat_end_date < nvl(p_end_date, hr_api.g_eot)
344 ) THEN
345 --
346 fnd_message.set_name ( 'OTA','OTA_13742_CTU_PAR_CAT_DATES');
347 fnd_message.raise_error;
348 --
349 End IF;
350 ELSE
351 CLOSE cur_par_cat_start_end_date;
352 End IF;
353 End IF;
354 --
355 hr_utility.set_location(' Leaving:' || l_proc,10);
356 Exception
357 when app_exception.application_exception then
358 IF hr_multi_message.exception_add
359 (p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
360 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
361 ) THEN
362 hr_utility.set_location(' Leaving:'|| l_proc,20);
363 raise;
364 END IF;
365
366 hr_utility.set_location(' Leaving:'|| l_proc,30);
367 --
368 End Chk_Parent_Category_Dates;
369 --
370 -- ----------------------------------------------------------------------------
371 -- |---------------------< Chk_Child_Category_Dates >-------------------------|
372 -- ----------------------------------------------------------------------------
373 --
374 Procedure Chk_Child_Category_Dates
375 (p_category_usage_id in number
376 ,p_start_date in date
377 ,p_end_date in date
378 ) is
379 --
380 -- Declare cursors and local variables
381 --
382 -- Cursor to get value if parent category is already exits in child hierarchy of base category
383 --
384 CURSOR cur_child_cat_dates is
385 select
386 ccat.start_date_active,
387 ccat.end_date_active
388 from
389 ota_category_usages ccat
390 where
391 ccat.parent_cat_usage_id = p_category_usage_id;
392 --
393 -- Variables for API Boolean parameters
394 l_proc varchar2(72) := g_package ||'Chk_Child_Category_Dates';
395 v_start_date ota_category_usages.start_date_active%TYPE;
396 v_end_date ota_category_usages.end_date_active%TYPE;
397 l_obj_cat varchar2(80);
398 l_obj_child_cat varchar2(80);
399
400 Begin
401 hr_utility.set_location(' Entering:' || l_proc,10);
402 --
403 OPEN cur_child_cat_dates;
404 FETCH cur_child_cat_dates into v_start_date,
405 v_end_date;
406 LOOP
407 Exit When cur_child_cat_dates%notfound OR cur_child_cat_dates%notfound is null;
408
409 -- Assignment if v_start_date or v_end_date is null
410 --
411 If v_end_date is null Then
412 --
413 v_end_date := hr_api.g_eot;
414 --
415 End if;
416
417 If ota_general.check_par_child_dates_fun( p_start_date
418 , p_end_date
419 , v_start_date
420 , v_end_date ) then
421 --
422 l_obj_cat := ota_utility.Get_lookup_meaning('OTA_OBJECT_TYPE','C',810);
423 l_obj_child_cat := ota_utility.Get_lookup_meaning('OTA_OBJECT_TYPE','CHC',810);
424 fnd_message.set_name( 'OTA','OTA_443166_OBJ_CHILD_DATE');
425 fnd_message.set_token('OBJECT_NAME', l_obj_cat );
426 fnd_message.set_token('CHILD_OBJECT', l_obj_child_cat);
427 fnd_message.raise_error;
428 --
429 End if;
430 --
431 Fetch cur_child_cat_dates into v_start_date
432 , v_end_date;
433 End loop;
434 --
435 Close cur_child_cat_dates;
436 --
437 hr_utility.set_location(' Leaving:' || l_proc,10);
438 Exception
439 when app_exception.application_exception then
440 IF hr_multi_message.exception_add
441 (p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
442 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
443 ) THEN
444 hr_utility.set_location(' Leaving:'|| l_proc,20);
445 raise;
446 END IF;
447
448 hr_utility.set_location(' Leaving:'|| l_proc,30);
449 --
450 End Chk_Child_Category_Dates;
451 --
452 -- ----------------------------------------------------------------------------
453 -- |----------------------< Chk_Act_Start_End_Date >--------------------------|
454 -- ----------------------------------------------------------------------------
455 --
456 Procedure Chk_Act_Start_End_Date
457 (p_category_usage_id in number
458 ,p_start_date in date
459 ,p_end_date in date
460 ) is
461 --
462 -- Declare cursors and local variables
463 --
464 -- Cursor to get value if parent category is already exits in child hierarchy of base category
465 --
466 CURSOR cur_act_dates is
467 select
468 tav.start_date,
469 tav.end_date
470 from
471 ota_activity_versions tav,
472 ota_act_cat_inclusions aci
473 where
474 tav.activity_version_id = aci.activity_version_id
475 and aci.primary_flag= 'Y'
476 and aci.category_usage_id = p_category_usage_id;
477 --
478 -- Variables for API Boolean parameters
479 l_proc varchar2(72) := g_package ||'Chk_Act_Start_End_Date';
480 v_start_date ota_activity_versions.start_date%TYPE;
481 v_end_date ota_activity_versions.end_date%TYPE;
482 l_obj_cat varchar2(80);
483 l_obj_act varchar2(80);
484
485 Begin
486 hr_utility.set_location(' Entering:' || l_proc,10);
487 --
488 OPEN cur_act_dates;
489 FETCH cur_act_dates into v_start_date,
490 v_end_date;
491 LOOP
492 Exit When cur_act_dates%notfound OR cur_act_dates%notfound is null;
493
494 -- Assignment if v_start_date or v_end_date is null
495 --
496 If v_start_date is null Then
497 --
498 v_start_date := p_start_date;
499 --
500 End if;
501 --
502 If v_end_date is null Then
503 --
504 v_end_date := hr_api.g_eot;
505 --
506 End if;
507
508 If ota_general.check_par_child_dates_fun( p_start_date
509 , p_end_date
510 , v_start_date
511 , v_end_date ) then
512 --
513
514 l_obj_cat := ota_utility.Get_lookup_meaning('OTA_OBJECT_TYPE','C',810);
515 l_obj_act := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','H',810);
516 fnd_message.set_name ( 'OTA','OTA_443166_OBJ_CHILD_DATE');
517 fnd_message.set_token('OBJECT_NAME', l_obj_cat );
518 fnd_message.set_token('CHILD_OBJECT', l_obj_act);
519 fnd_message.raise_error;
520 --
521 End if;
522 --
523 Fetch cur_act_dates into v_start_date
524 , v_end_date;
525 End loop;
526 --
527 Close cur_act_dates;
528
529
530 hr_utility.set_location(' Leaving:' || l_proc,10);
531 Exception
532 when app_exception.application_exception then
533 IF hr_multi_message.exception_add
534 (p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
535 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
536 ) THEN
537 hr_utility.set_location(' Leaving:'|| l_proc,20);
538 raise;
539 END IF;
540
541 hr_utility.set_location(' Leaving:'|| l_proc,30);
542 --
543 End Chk_Act_Start_End_Date;
544 --
545 -- ----------------------------------------------------------------------------
546 -- |----------------------< Chk_lp_Start_End_Date >--------------------------|
547 -- ----------------------------------------------------------------------------
548 --
549 Procedure Chk_lp_Start_End_Date
550 (p_category_usage_id in number
551 ,p_start_date in date
552 ,p_end_date in date
553 ) is
554 --
555 -- Declare cursors and local variables
556 --
557 -- Cursor to get value if parent category is already exits in child hierarchy of base category
558 --
559 CURSOR cur_lp_dates is
560 select
561 lps.start_date_active,
562 lps.end_date_active
563 from
564 ota_learning_paths lps,
565 ota_lp_cat_inclusions lci
566 where
567 lps.learning_path_id = lci.learning_path_id
568 and lci.primary_flag= 'Y'
569 and lci.category_usage_id = p_category_usage_id;
570 --
571 -- Variables for API Boolean parameters
572 l_proc varchar2(72) := g_package ||'Chk_lp_Start_End_Date';
573 v_start_date ota_learning_paths.start_date_active%TYPE;
574 v_end_date ota_learning_paths.end_date_active%TYPE;
575 l_obj_cat varchar2(80);
576 l_obj_lp varchar2(80);
577
578 Begin
579 hr_utility.set_location(' Entering:' || l_proc,10);
580 --
581 OPEN cur_lp_dates;
582 FETCH cur_lp_dates into v_start_date,
583 v_end_date;
584 LOOP
585 Exit When cur_lp_dates%notfound OR cur_lp_dates%notfound is null;
586
587 -- Assignment if v_start_date or v_end_date is null
588 --
589 If v_start_date is null Then
590 --
591 v_start_date := p_start_date;
592 --
593 End if;
594 --
595 If v_end_date is null Then
596 --
597 v_end_date := hr_api.g_eot;
598 --
599 End if;
600
601 If ota_general.check_par_child_dates_fun( p_start_date
602 , p_end_date
603 , v_start_date
604 , v_end_date ) then
605 --
606
607 l_obj_cat := ota_utility.Get_lookup_meaning('OTA_OBJECT_TYPE','C',810);
608 l_obj_lp := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','CLP',810);
609 fnd_message.set_name ( 'OTA','OTA_443166_OBJ_CHILD_DATE');
610 fnd_message.set_token('OBJECT_NAME', l_obj_cat );
611 fnd_message.set_token('CHILD_OBJECT', l_obj_lp);
612 fnd_message.raise_error;
613 --
614 End if;
615 --
616 Fetch cur_lp_dates into v_start_date
617 , v_end_date;
618 End loop;
619 --
620 Close cur_lp_dates;
621
622
623 hr_utility.set_location(' Leaving:' || l_proc,10);
624 Exception
625 when app_exception.application_exception then
626 IF hr_multi_message.exception_add
627 (p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
628 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
629 ) THEN
630 hr_utility.set_location(' Leaving:'|| l_proc,20);
631 raise;
632 END IF;
633
634 hr_utility.set_location(' Leaving:'|| l_proc,30);
635 --
636 End Chk_lp_Start_End_Date;
637 --
638 -- ----------------------------------------------------------------------------
639 -- |---------------------------< Chk_Root_Cat >-------------------------------|
640 -- ----------------------------------------------------------------------------
641 Procedure Chk_Root_Cat
642 (p_business_group_id in number
643 ) is
644 --
645 -- Declare cursors and local variables
646 --
647 -- Cursor to get value if parent category is already exits in child hierarchy of base category
648
649 CURSOR cur_root_category is
650 select
651 distinct 'found'
652 From
653 ota_category_usages ctu
654 where
655 ctu.business_group_id = p_business_group_id
656 and ctu.parent_cat_usage_id is null
657 and ctu.type = 'C';
658
659 -- Variables for API Boolean parameters
660 l_proc varchar2(72) := g_package ||'Chk_Root_Cat';
661 l_root_cat_flag varchar2(10);
662
663 Begin
664 hr_utility.set_location(' Entering:' || l_proc,10);
665 --
666
667 OPEN cur_root_category;
668 FETCH cur_root_category into l_root_cat_flag;
669
670 IF cur_root_category%FOUND then
671 CLOSE cur_root_category;
672 fnd_message.set_name ( 'OTA' ,'OTA_443103_ROOT_OBJ_EXISTS');
673 fnd_message.raise_error;
674 ELSE
675 CLOSE cur_root_category;
676 END IF;
677
678 --
679 hr_utility.set_location(' Leaving:' || l_proc,10);
680 Exception
681 when app_exception.application_exception then
682 IF hr_multi_message.exception_add
683 (p_associated_column1 => 'OTA_CATEGORY_USAGES.BUSINESS_GROUP_ID'
684 ) THEN
685 hr_utility.set_location(' Leaving:'|| l_proc,20);
686 raise;
687 END IF;
688
689 hr_utility.set_location(' Leaving:'|| l_proc,30);
690 --
691 End Chk_Root_Cat;
692 --
693 -- ----------------------------------------------------------------------------
694 -- |----------------------< Chk_Off_Start_End_Date >---------------------------|
695 -- ----------------------------------------------------------------------------
696 --
697 Procedure Chk_Off_Start_End_Date
698 (p_category_usage_id in number
699 ,p_start_date in date
700 ,p_end_date in date
701 ) is
702 --
703 -- Declare cursors and local variables
704 --
705 -- Cursor to get value if parent category is already exits in child hierarchy of base category
706
707 CURSOR cur_off_dates is
708 select start_date,
709 end_date
710 from
711 ota_offerings off
712 where
713 off.delivery_mode_id = p_category_usage_id;
714
715 -- Variables for API Boolean parameters
716 l_proc varchar2(72) := g_package ||'Chk_Off_Start_End_Date';
717 v_start_date ota_offerings.start_date%TYPE;
718 v_end_date ota_offerings.end_date%TYPE;
719 l_obj_dm VARCHAR2(80);
720 l_obj_off VARCHAR2(80);
721
722 Begin
723 hr_utility.set_location(' Entering:' || l_proc,10);
724 --
725
726 OPEN cur_off_dates;
727 FETCH cur_off_dates into v_start_date,
728 v_end_date;
729 LOOP
730 Exit When cur_off_dates%notfound OR cur_off_dates%notfound is null;
731
732 -- Assignment if v_start_date or v_end_date is null
733 --
734 If v_start_date is null Then
735 --
736 v_start_date := p_start_date;
737 --
738 End if;
739 --
740 If v_end_date is null Then
741 --
742 v_end_date := hr_api.g_eot;
743 --
744 End if;
745 If ota_general.check_par_child_dates_fun( p_start_date
746 , p_end_date
747 , v_start_date
748 , v_end_date ) then
749 --
750
751 l_obj_dm := ota_utility.Get_lookup_meaning('CATEGORY_TYPE','DM',800);
752 l_obj_off := ota_utility.Get_lookup_meaning('OTA_OBJECT_TYPE','OFS',810);
753 fnd_message.set_name ( 'OTA','OTA_443166_OBJ_CHILD_DATE');
754 fnd_message.set_token('OBJECT_NAME', l_obj_dm );
755 fnd_message.set_token('CHILD_OBJECT', l_obj_off);
756 fnd_message.raise_error;
757 --
758 End if;
759 --
760 Fetch cur_off_dates into v_start_date
761 , v_end_date;
762 End loop;
763 --
764 Close cur_off_dates;
765
766
767 hr_utility.set_location(' Leaving:' || l_proc,10);
768 Exception
769 when app_exception.application_exception then
770 IF hr_multi_message.exception_add
771 (p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
772 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.END_DATE_ACTIVE'
773 ) THEN
774 hr_utility.set_location(' Leaving:'|| l_proc,20);
775 raise;
776 END IF;
777
778 hr_utility.set_location(' Leaving:'|| l_proc,30);
779 --
780 End Chk_Off_Start_End_Date;
781 --
782 -- ----------------------------------------------------------------------------
783 -- |-----------------------< Chk_Sync_Online_Flag >---------------------------|
784 -- ----------------------------------------------------------------------------
785 --
786 Procedure Chk_Sync_Online_Flag
787 (p_category_usage_id in number
788 ,p_online_flag in varchar2
789 ,p_synchronous_flag in varchar2
790 ) is
791 --
792 -- Declare cursors and local variables
793 --
794 -- Cursor to get value if parent category is already exits in child hierarchy of base category
795
796 CURSOR cur_dm_offerings is
797 select 'Y'
798 From ota_offerings off
799 where off.delivery_mode_id = p_category_usage_id;
800
801 -- Variables for API Boolean parameters
802 l_proc varchar2(72) := g_package ||'Chk_Sync_Online_Flag';
803 l_off_flag varchar2(10);
804
805 Begin
806 hr_utility.set_location(' Entering:' || l_proc,10);
807 --
808
809 OPEN cur_dm_offerings;
810 FETCH cur_dm_offerings into l_off_flag;
811
812 IF cur_dm_offerings%FOUND then
813 CLOSE cur_dm_offerings;
814 fnd_message.set_name ( 'OTA' ,'OTA_443269_CTU_FLG_OFF_EXISTS');
815 fnd_message.raise_error;
816 ELSE
817 CLOSE cur_dm_offerings;
818 END IF;
819
820 --
821 hr_utility.set_location(' Leaving:' || l_proc,10);
822 --
823 Exception
824 when app_exception.application_exception then
825 IF hr_multi_message.exception_add
826 (p_associated_column1 => 'OTA_CATEGORY_USAGES.ONLINE_FLAG'
827 ,p_associated_column2 => 'OTA_CATEGORY_USAGES.SYNCHRONOUS_FLAG'
828 ) THEN
829 hr_utility.set_location(' Leaving:'|| l_proc,20);
830 raise;
831 END IF;
832
833 hr_utility.set_location(' Leaving:'|| l_proc,30);
834 --
835 End Chk_Sync_Online_Flag;
836 --
837 -- ----------------------------------------------------------------------------
838 -- |-----------------------< chk_non_updateable_args >------------------------|
839 -- ----------------------------------------------------------------------------
840 -- {Start Of Comments}
841 --
842 -- Description:
843 -- This procedure is used to ensure that non updateable attributes have
844 -- not been updated. If an attribute has been updated an error is generated.
845 --
846 -- Pre Conditions:
847 -- g_old_rec has been populated with details of the values currently in
848 -- the database.
849 --
850 -- In Arguments:
851 -- p_rec has been populated with the updated values the user would like the
852 -- record set to.
853 --
854 -- Post Success:
855 -- Processing continues if all the non updateable attributes have not
856 -- changed.
857 --
858 -- Post Failure:
859 -- An application error is raised if any of the non updatable attributes
860 -- have been altered.
861 --
862 -- {End Of Comments}
863 -- ----------------------------------------------------------------------------
864 Procedure chk_non_updateable_args
865 (p_effective_date in date
866 ,p_rec in ota_ctu_shd.g_rec_type
867 ) IS
868 --
869 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
870 --
871 Begin
872 --
873 -- Only proceed with the validation if a row exists for the current
874 -- record in the HR Schema.
875 --
876 IF NOT ota_ctu_shd.api_updating
877 (p_category_usage_id => p_rec.category_usage_id
878 ,p_object_version_number => p_rec.object_version_number
879 ) THEN
880 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
881 fnd_message.set_token('PROCEDURE ', l_proc);
882 fnd_message.set_token('STEP ', '5');
883 fnd_message.raise_error;
884 END IF;
885 --
886 --
887 -- EDIT_HERE: Add checks to ensure non-updateable args have
888 -- not been updated.
889 --
890 End chk_non_updateable_args;
891 --
892 -- ---------------------------------------------------------------------------
893 -- |--------------------------< Chk_unique_category >-------------------------------|
894 -- ---------------------------------------------------------------------------
895 --
896 Procedure Chk_unique_category
897 (p_category_usage_id in number
898 ,p_category in varchar2
899 ,p_business_group_id in varchar2
900 ,p_type in varchar2
901 ,p_parent_cat_usage_id in number
902 )
903 Is
904 --
905 -- Declare cursor
906 --
907 cursor csr_cat_name is
908 select
909 distinct ctu.type
910 from
911 ota_category_usages_vl ctu
912 where
913 ctu.category = p_category
914 and (p_category_usage_id is null or ctu.category_usage_id <> p_category_usage_id)
915 and (ctu.parent_cat_usage_id = p_parent_cat_usage_id or ctu.type <> 'C')
916 and ctu.business_group_id = p_business_group_id
917 and ctu.type = p_type;
918
919 --
920 --
921 -- Declare local variables
922 --
923 l_dup_cat_type varchar2(30);
924 l_proc varchar2(72) := g_package||'Chk_unique_category';
925 --
926 Begin
927 --
928 hr_utility.set_location('Entering:'|| l_proc, 10);
929 --
930 open csr_cat_name;
931 fetch csr_cat_name into l_dup_cat_type;
932 --
933 if csr_cat_name%found then
934 --
935 -- The category name cannot be duplicated therefore we must error
936 --
937 close csr_cat_name;
938 if l_dup_cat_type = 'DM' then
939 fnd_message.set_name('OTA','OTA_443388_CTU_DUP_DM');
940 else
941 fnd_message.set_name('OTA','OTA_443337_CTU_DUP_NAME');
942 end if;
943 hr_utility.set_location(l_proc,20);
944 fnd_message.raise_error;
945 end if;
946 hr_utility.set_location(l_proc,30);
947 --
948 -- Set the global variables so the values are
949 -- available for the next call to this function.
950 --
951 close csr_cat_name;
952
953 hr_utility.set_location(' Leaving:'|| l_proc, 40);
954 --
955 end Chk_unique_category;
956 --
957 -- ----------------------------------------------------------------------------
958 -- |---------------------------< insert_validate >----------------------------|
959 -- ----------------------------------------------------------------------------
960 Procedure insert_validate
961 (p_effective_date in date
962 ,p_rec in ota_ctu_shd.g_rec_type
963 ) is
964 --
965 l_proc varchar2(72) := g_package||'insert_validate';
966 --
967 Begin
968 hr_utility.set_location('Entering:'||l_proc, 5);
969 --
970 -- Call all supporting business operations
971 --
972 hr_api.validate_bus_grp_id
973 (p_business_group_id => p_rec.business_group_id
974 ,p_associated_column1 => ota_ctu_shd.g_tab_nam
975 || '.BUSINESS_GROUP_ID');
976 --
977 -- After validating the set of important attributes,
978 -- if Multiple Message detection is enabled and at least
979 -- one error has been found then abort further validation.
980 --
981 hr_multi_message.end_validation_set;
982 --
983 -- Validate Dependent Attributes
984 --
985 --
986 -- Validating category name to be unique
987 --
988 Chk_unique_category
989 (p_category_usage_id => p_rec.category_usage_id
990 ,p_category => p_rec.category
991 ,p_business_group_id => p_rec.business_group_id
992 ,p_type => p_rec.type
993 ,p_parent_cat_usage_id => p_rec.parent_cat_usage_id
994 );
995 --
996 If p_rec.type = 'C' then
997 --
998 If p_rec.parent_cat_usage_id is null Then
999 --
1000 Chk_Root_Cat
1001 (p_business_group_id => p_rec.business_group_id
1002 );
1003 --
1004 End If;
1005 --
1006 Chk_Parent_Category_Dates
1007 (p_parent_cat_usage_id => p_rec.parent_cat_usage_id
1008 ,p_start_date => p_rec.start_date_active
1009 ,p_end_date => p_rec.end_date_active
1010 );
1011 --
1012 End If;
1013 --
1014 Chk_online_flag
1015 (p_online_flag => p_rec.online_flag
1016 ,p_type => p_rec.type
1017 );
1018 --
1019 --
1020 Chk_synchronous_flag
1021 (p_synchronous_flag => p_rec.synchronous_flag
1022 ,p_type => p_rec.type
1023 );
1024 --
1025 --
1026 Chk_start_end_dates
1027 (p_start_date => p_rec.start_date_active
1028 ,p_end_date => p_rec.end_date_active
1029 );
1030 --
1031 -- Bug 3456546 : dff available only for Category : type = C
1032 if p_rec.type = 'C' then
1033 ota_ctu_bus.chk_df(p_rec);
1034 end if;
1035
1036 --
1037 hr_utility.set_location(' Leaving:'||l_proc, 10);
1038 End insert_validate;
1039 --
1040 -- ----------------------------------------------------------------------------
1041 -- |---------------------------< update_validate >----------------------------|
1042 -- ----------------------------------------------------------------------------
1043 Procedure update_validate
1044 (p_effective_date in date
1045 ,p_rec in ota_ctu_shd.g_rec_type
1046 ) is
1047 --
1048 l_proc varchar2(72) := g_package||'update_validate';
1049 --
1050 Begin
1051 hr_utility.set_location('Entering:'||l_proc, 5);
1052 --
1053 -- Call all supporting business operations
1054 --
1055 hr_api.validate_bus_grp_id
1056 (p_business_group_id => p_rec.business_group_id
1057 ,p_associated_column1 => ota_ctu_shd.g_tab_nam
1058 || '.BUSINESS_GROUP_ID');
1059 --
1060 -- After validating the set of important attributes,
1061 -- if Multiple Message detection is enabled and at least
1062 -- one error has been found then abort further validation.
1063 --
1064 hr_multi_message.end_validation_set;
1065 --
1066 -- Validate Dependent Attributes
1067 --
1068 -- Validating category name to be unique
1069 --
1070 Chk_unique_category
1071 (p_category_usage_id => p_rec.category_usage_id
1072 ,p_category => p_rec.category
1073 ,p_business_group_id => p_rec.business_group_id
1074 ,p_type => p_rec.type
1075 ,p_parent_cat_usage_id => p_rec.parent_cat_usage_id
1076 );
1077 --
1078 --
1079 -- Validating parent category for type category only
1080 IF p_rec.type = 'C' THEN
1081 ota_ctu_bus.Chk_valid_parent_category
1082 (p_parent_cat_usage_id => p_rec.parent_cat_usage_id
1083 ,p_category_usage_id => p_rec.category_usage_id
1084 );
1085 END IF;
1086 --
1087 --
1088 Chk_start_end_dates
1089 (p_start_date => p_rec.start_date_active
1090 ,p_end_date => p_rec.end_date_active
1091 );
1092 --
1093 IF p_rec.type = 'C' THEN
1094 --
1095 Chk_Parent_Category_Dates
1096 (p_parent_cat_usage_id => p_rec.parent_cat_usage_id
1097 ,p_start_date => p_rec.start_date_active
1098 ,p_end_date => p_rec.end_date_active
1099 );
1100 --
1101 Chk_Act_Start_End_Date
1102 (p_category_usage_id => p_rec.category_usage_id
1103 ,p_start_date => p_rec.start_date_active
1104 ,p_end_date => p_rec.end_date_active
1105 );
1106 --
1107 Chk_lp_Start_End_Date
1108 (p_category_usage_id => p_rec.category_usage_id
1109 ,p_start_date => p_rec.start_date_active
1110 ,p_end_date => p_rec.end_date_active
1111 );
1112 --
1113 Chk_Child_Category_Dates
1114 (p_category_usage_id => p_rec.category_usage_id
1115 ,p_start_date => p_rec.start_date_active
1116 ,p_end_date => p_rec.end_date_active
1117 );
1118 --
1119 End IF;
1120 --
1121 IF p_rec.type = 'DM' THEN
1122 --
1123 Chk_Off_Start_End_Date
1124 (p_category_usage_id => p_rec.category_usage_id
1125 ,p_start_date => p_rec.start_date_active
1126 ,p_end_date => p_rec.end_date_active
1127 );
1128 --
1129 IF ( p_rec.online_flag <> ota_ctu_shd.g_old_rec.online_flag
1130 or p_rec.synchronous_flag <> ota_ctu_shd.g_old_rec.synchronous_flag )THEN
1131 --
1132 Chk_Sync_Online_Flag
1133 (p_category_usage_id => p_rec.category_usage_id
1134 ,p_online_flag => p_rec.online_flag
1135 ,p_synchronous_flag => p_rec.synchronous_flag
1136 );
1137 --
1138 End IF;
1139 --
1140 End IF;
1141 --
1142 chk_non_updateable_args
1143 (p_effective_date => p_effective_date
1144 ,p_rec => p_rec
1145 );
1146 --
1147 --
1148 -- Bug 3456546 : dff available only for Category : type = C
1149 if p_rec.type = 'C' then
1150 ota_ctu_bus.chk_df(p_rec);
1151 end if;
1152
1153 --
1154 hr_utility.set_location(' Leaving:'||l_proc, 10);
1155 End update_validate;
1156 --
1157 -- ----------------------------------------------------------------------------
1158 -- |---------------------------< delete_validate >----------------------------|
1159 -- ----------------------------------------------------------------------------
1160 Procedure delete_validate
1161 (p_rec in ota_ctu_shd.g_rec_type
1162 ) is
1163 --
1164 CURSOR cur_category_type is
1165 select
1166 ctu.type
1167 From
1168 ota_category_usages ctu
1169 where
1170 ctu.category_usage_id = p_rec.category_usage_id;
1171 --
1172 l_proc varchar2(72) := g_package||'delete_validate';
1173 l_cat_type varchar2(30);
1174 --
1175 Begin
1176 hr_utility.set_location('Entering:'||l_proc, 5);
1177 --
1178 -- Call all supporting business operations
1179 --
1180 OPEN cur_category_type;
1181 FETCH cur_category_type into l_cat_type;
1182 CLOSE cur_category_type;
1183
1184 --
1185 -- check for child category and Activity Association for type Category only
1186 IF l_cat_type = 'C' THEN
1187 Chk_child_category
1188 (p_category_usage_id => p_rec.category_usage_id
1189 );
1190 --
1191 Chk_act_association
1192 (p_category_usage_id => p_rec.category_usage_id
1193 );
1194 --
1195 Chk_lp_association
1196 (p_category_usage_id => p_rec.category_usage_id
1197 );
1198 --
1199 Chk_act_def_for_org_tp
1200 (p_category_usage_id => p_rec.category_usage_id
1201 );
1202 --
1203 -- check for offering Association for type Delivery Mode only
1204 ELSIF l_cat_type = 'DM' THEN
1205 --
1206 Chk_offering_association
1207 (p_category_usage_id => p_rec.category_usage_id
1208 );
1209 --
1210 END IF;
1211 --
1212 hr_utility.set_location(' Leaving:'||l_proc, 10);
1213 End delete_validate;
1214 -- ----------------------------------------------------------------------------
1215 -- |---------------------< Chk_valid_parent_category >------------------------|
1216 -- ----------------------------------------------------------------------------
1217 --
1218 Procedure Chk_valid_parent_category
1219 (p_parent_cat_usage_id in number
1220 ,p_category_usage_id in number
1221 )
1222
1223 is
1224 --
1225 -- Declare cursors and local variables
1226 --
1227 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1228
1229 CURSOR cur_parent_categories is
1230 select
1231 '1' Cat_exists
1232 From
1233 ota_category_usages ctu1
1234 where
1235 ctu1.category_usage_id = p_category_usage_id
1236 and ctu1.category_usage_id in
1237 (select
1238 ctu2.parent_cat_usage_id
1239 From
1240 ota_category_usages ctu2
1241 connect by ctu2.category_usage_id = prior ctu2.parent_cat_usage_id
1242 start with ctu2.category_usage_id = p_parent_cat_usage_id
1243 );
1244
1245
1246 l_proc varchar2(72) := g_package||'Chk_valid_parent_category';
1247 l_cat_flag varchar2(10);
1248 l_result boolean := FALSE;
1249 --
1250 --
1251 begin
1252 hr_utility.set_location('Entering:'|| l_proc, 10);
1253 --
1254 --
1255
1256 IF (p_category_usage_id = p_parent_cat_usage_id) THEN
1257 fnd_message.set_name ( 'OTA' ,'OTA_443200_CTU_CHILD_EXITS');
1258 fnd_message.raise_error;
1259 ELSE
1260
1261 OPEN cur_parent_categories;
1262 FETCH cur_parent_categories into l_cat_flag;
1263
1264 IF cur_parent_categories%FOUND then
1265 CLOSE cur_parent_categories;
1266 fnd_message.set_name ( 'OTA' ,'OTA_443200_CTU_CHILD_EXITS');
1267 fnd_message.raise_error;
1268 ELSE
1269 CLOSE cur_parent_categories;
1270 END IF;
1271 END IF;
1272 --
1273 hr_utility.set_location(' Leaving:'||l_proc, 10);
1274 Exception
1275 when app_exception.application_exception then
1276 IF hr_multi_message.exception_add
1277 (p_associated_column1 => 'OTA_CATEGORY_USAGES_TL.CATEGORY'
1278 ) THEN
1279 hr_utility.set_location(' Leaving:'|| l_proc,20);
1280 raise;
1281 END IF;
1282 hr_utility.set_location(' Leaving:'|| l_proc,30);
1283 --
1284 End Chk_valid_parent_category;
1285 --
1286 -- ----------------------------------------------------------------------------
1287 -- |------------------------< Chk_child_category >----------------------------|
1288 -- ----------------------------------------------------------------------------
1289 Procedure Chk_child_category
1290 (p_category_usage_id in number
1291 ) is
1292 --
1293 -- Declare cursors and local variables
1294 --
1295 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1296
1297 CURSOR cur_child_categories is
1298 select
1299 distinct 'found'
1300 From
1301 ota_category_usages ctu
1302 where
1303 parent_cat_usage_id = p_category_usage_id;
1304
1305 -- Variables for API Boolean parameters
1306 l_proc varchar2(72) := g_package ||'Chk_child_category';
1307 l_cat_flag varchar2(10);
1308
1309 Begin
1310 hr_utility.set_location(' Entering:' || l_proc,10);
1311 --
1312
1313 OPEN cur_child_categories;
1314 FETCH cur_child_categories into l_cat_flag;
1315
1316 IF cur_child_categories%FOUND then
1317 CLOSE cur_child_categories;
1318 fnd_message.set_name ( 'OTA' ,'OTA_443273_CTU_CHLD_CAT_EXISTS');
1319 fnd_message.raise_error;
1320 ELSE
1321 CLOSE cur_child_categories;
1322 END IF;
1323
1324 --
1325 hr_utility.set_location(' Leaving:' || l_proc,10);
1326 --
1327
1328 Exception
1329 when app_exception.application_exception then
1330 IF hr_multi_message.exception_add
1331 (p_associated_column1 => 'OTA_CATEGORY_USAGES.CATEGORY'
1332 ,p_same_associated_columns => 'Y'
1333 ) THEN
1334 hr_utility.set_location(' Leaving:'|| l_proc,20);
1335 raise;
1336 END IF;
1337
1338 hr_utility.set_location(' Leaving:'|| l_proc,30);
1339 --
1340
1341 End Chk_child_category;
1342 --
1343 -- ----------------------------------------------------------------------------
1344 -- |------------------------< Chk_act_association >----------------------------|
1345 -- ----------------------------------------------------------------------------
1346 Procedure Chk_act_association
1347 (p_category_usage_id in number
1348 ) is
1349 --
1350 -- Declare cursors and local variables
1351 --
1352 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1353
1354 CURSOR cur_cat_activities is
1355 select
1356 distinct 'found'
1357 From
1358 ota_act_cat_inclusions cti
1359 where
1360 cti.category_usage_id = p_category_usage_id;
1361
1362 -- Variables for API Boolean parameters
1363 l_proc varchar2(72) := g_package ||'Chk_act_association';
1364 l_act_flag varchar2(10);
1365
1366 Begin
1367 hr_utility.set_location(' Entering:' || l_proc,10);
1368 --
1369
1370 OPEN cur_cat_activities;
1371 FETCH cur_cat_activities into l_act_flag;
1372
1373 IF cur_cat_activities%FOUND then
1374 CLOSE cur_cat_activities;
1375 fnd_message.set_name ( 'OTA' ,'OTA_443272_CTU_ACT_EXISTS');
1376 fnd_message.raise_error;
1377 ELSE
1378 CLOSE cur_cat_activities;
1379 END IF;
1380
1381 --
1382 hr_utility.set_location(' Leaving:' || l_proc,10);
1383 Exception
1384 when app_exception.application_exception then
1385 IF hr_multi_message.exception_add
1386 (p_associated_column1 => 'OTA_CATEGORY_USAGES.CATEGORY'
1390 raise;
1387 ,p_same_associated_columns => 'Y'
1388 ) THEN
1389 hr_utility.set_location(' Leaving:'|| l_proc,20);
1391 END IF;
1392
1393 hr_utility.set_location(' Leaving:'|| l_proc,30);
1394 --
1395 End Chk_act_association;
1396 --
1397 -- ----------------------------------------------------------------------------
1398 -- |---------------------< Chk_offering_association >-------------------------|
1399 -- ----------------------------------------------------------------------------
1400 Procedure Chk_offering_association
1401 (p_category_usage_id in number
1402 ) is
1403 --
1404 -- Declare cursors and local variables
1405 --
1406 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1407
1408 CURSOR cur_dm_offerings is
1409 select 'Y'
1410 From ota_offerings off
1411 where off.delivery_mode_id = p_category_usage_id;
1412
1413 -- Variables for API Boolean parameters
1414 l_proc varchar2(72) := g_package ||'Chk_offering_association';
1415 l_off_flag varchar2(10);
1416
1417 Begin
1418 hr_utility.set_location(' Entering:' || l_proc,10);
1419 --
1420
1421 OPEN cur_dm_offerings;
1422 FETCH cur_dm_offerings into l_off_flag;
1423
1424 IF cur_dm_offerings%FOUND then
1425 CLOSE cur_dm_offerings;
1426 fnd_message.set_name ( 'OTA' ,'OTA_443271_CTU_OFF_EXISTS');
1427 fnd_message.raise_error;
1428 ELSE
1429 CLOSE cur_dm_offerings;
1430 END IF;
1431
1432 --
1433 hr_utility.set_location(' Leaving:' || l_proc,10);
1434 --
1435 End Chk_offering_association;
1436 --
1437 -- ----------------------------------------------------------------------------
1438 -- |------------------------< Chk_online_flag >----------------------------|
1439 -- ----------------------------------------------------------------------------
1440 Procedure Chk_online_flag
1441 (p_online_flag in varchar2
1442 ,p_type in varchar2
1443 ) is
1444 --
1445 -- Declare cursors and local variables
1446 --
1447 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1448 --
1449 l_proc varchar2(72) := g_package ||'Chk_online_flag';
1450
1451 Begin
1452 hr_utility.set_location(' Entering:' || l_proc,10);
1453 --
1454
1455 IF p_type = 'DM' THEN
1456 IF p_online_flag is null THEN
1457 fnd_message.set_name ( 'OTA' ,'OTA_443268_CTU_ONLINE_FLAG');
1458 fnd_message.raise_error;
1459 END IF;
1460 END IF;
1461
1462 --
1463 hr_utility.set_location(' Leaving:' || l_proc,10);
1464 --
1465 Exception
1466 when app_exception.application_exception then
1467 if hr_multi_message.exception_add
1468 (p_associated_column1 => 'OTA_CATEGORY_USAGES.ONLINE_FLAG'
1469 ) then
1470 hr_utility.set_location(' Leaving:'|| l_proc,20);
1471 raise;
1472 end if;
1473 hr_utility.set_location(' Leaving:'|| l_proc,30);
1474 End Chk_online_flag;
1475 --
1476 -- ----------------------------------------------------------------------------
1477 -- |------------------------< Chk_synchronous_flag >----------------------------|
1478 -- ----------------------------------------------------------------------------
1479 Procedure Chk_synchronous_flag
1480 (p_synchronous_flag in varchar2
1481 ,p_type in varchar2
1482 ) is
1483 --
1484 -- Declare cursors and local variables
1485 --
1486 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1487 --
1488 l_proc varchar2(72) := g_package ||'Chk_synchronous_flag';
1489
1490 Begin
1491 hr_utility.set_location(' Entering:' || l_proc,10);
1492 --
1493
1494 IF p_type = 'DM' THEN
1495 IF p_synchronous_flag is null THEN
1496 fnd_message.set_name ( 'OTA' ,'OTA_443268_CTU_ONLINE_FLAG');
1497 fnd_message.raise_error;
1498 END IF;
1499 END IF;
1500
1501 --
1502 hr_utility.set_location(' Leaving:' || l_proc,10);
1503 --
1504 Exception
1505 when app_exception.application_exception then
1506 if hr_multi_message.exception_add
1507 (p_associated_column1 => 'OTA_CATEGORY_USAGES.SYNCHRONOUS_FLAG'
1508 ) then
1509 hr_utility.set_location(' Leaving:'|| l_proc,70);
1510 raise;
1511 end if;
1512 hr_utility.set_location(' Leaving:'|| l_proc,80);
1513 End Chk_synchronous_flag;
1514 --
1515
1516 -- ----------------------------------------------------------------------------
1517 -- ---------------------------< Chk_start_end_dates >-------------------------|
1518 -- ----------------------------------------------------------------------------
1519 --
1520 -- The start date must be less than, or equal to, the end date.
1521 --
1522 Procedure Chk_start_end_dates
1523 (p_start_date in date
1524 ,p_end_date in date
1525 ) is
1526 --
1527 l_proc varchar2 (72)
1528 := g_package || 'Chk_start_end_dates';
1529 --
1530 --
1531 --
1532 begin
1533 --
1534 hr_utility.set_location ('entering:' || l_proc, 5);
1535 --
1536 if (p_start_date
1537 > nvl (p_end_date, to_date ('31-12-4712', 'DD-MM-YYYY'))) then
1538
1539 fnd_message.set_name ( 'OTA' ,'OTA_13312_GEN_DATE_ORDER');
1540 fnd_message.raise_error;
1541
1542 end if;
1543 --
1544 hr_utility.set_location (' leaving:' || l_proc, 10);
1545 --
1546 Exception
1547 when app_exception.application_exception then
1548 IF hr_multi_message.exception_add
1549 (p_associated_column1 => 'OTA_CATEGORY_USAGES.START_DATE_ACTIVE'
1550 ) THEN
1551 hr_utility.set_location(' Leaving:'|| l_proc,20);
1552 raise;
1553 END IF;
1554 hr_utility.set_location(' Leaving:'|| l_proc,30);
1555 --
1556
1557 End Chk_start_end_dates;
1558 --
1559 -- ----------------------------------------------------------------------------
1560 -- |------------------------< Chk_lp_association >----------------------------|
1561 -- ----------------------------------------------------------------------------
1562 Procedure Chk_lp_association
1563 (p_category_usage_id in number
1564 ) is
1565 --
1566 -- Declare cursors and local variables
1567 --
1568 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1569
1570 CURSOR cur_cat_learning_paths is
1571 select
1572 distinct 'found'
1573 From
1574 ota_lp_cat_inclusions lci
1575 where
1576 lci.category_usage_id = p_category_usage_id;
1577
1578 -- Variables for API Boolean parameters
1579 l_proc varchar2(72) := g_package ||'Chk_lp_association';
1580 l_lp_flag varchar2(10);
1581
1582 Begin
1583 hr_utility.set_location(' Entering:' || l_proc,10);
1584 --
1585
1586 OPEN cur_cat_learning_paths;
1587 FETCH cur_cat_learning_paths into l_lp_flag;
1588
1589 IF cur_cat_learning_paths%FOUND then
1590 CLOSE cur_cat_learning_paths;
1591 fnd_message.set_name ( 'OTA' ,'OTA_443350_CTU_LP_EXISTS');
1592 fnd_message.raise_error;
1593 ELSE
1594 CLOSE cur_cat_learning_paths;
1595 END IF;
1596
1597 --
1598 hr_utility.set_location(' Leaving:' || l_proc,10);
1599 Exception
1600 when app_exception.application_exception then
1601 IF hr_multi_message.exception_add
1602 (p_associated_column1 => 'OTA_CATEGORY_USAGES.CATEGORY'
1603 ,p_same_associated_columns => 'Y'
1604 ) THEN
1605 hr_utility.set_location(' Leaving:'|| l_proc,20);
1606 raise;
1607 END IF;
1608
1609 hr_utility.set_location(' Leaving:'|| l_proc,30);
1610 --
1611 End Chk_lp_association;
1612 --
1613 -- ----------------------------------------------------------------------------
1614 -- |----------------------< Chk_act_def_for_org_tp >--------------------------|
1615 -- ----------------------------------------------------------------------------
1616 Procedure Chk_act_def_for_org_tp
1617 (p_category_usage_id in number
1618 ) is
1619 --
1620 -- Declare cursors and local variables
1621 --
1622 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1623
1624 CURSOR cur_act_def_org_tp is
1625 select
1626 distinct tad.name
1627 From
1628 ota_training_plan_members tpm,
1629 ota_activity_definitions_vl tad,
1630 ota_category_usages ctu
1631 where
1632 ctu.category_usage_id = tad.category_usage_id
1633 and tad.activity_id = tpm.activity_definition_id
1634 and ctu.category_usage_id = p_category_usage_id;
1635
1636 -- Variables for API Boolean parameters
1637 l_proc varchar2(72) := g_package ||'Chk_act_def_for_org_tp';
1638 l_act_def_name varchar2(240);
1639
1640 Begin
1641 hr_utility.set_location(' Entering:' || l_proc,10);
1642 --
1643
1644 OPEN cur_act_def_org_tp;
1645 FETCH cur_act_def_org_tp into l_act_def_name;
1646
1647 IF cur_act_def_org_tp%FOUND then
1648 CLOSE cur_act_def_org_tp;
1649 fnd_message.set_name ( 'OTA','OTA_443097_CAT_TP_EXISTS');
1650 fnd_message.set_token('ACT_DEF_NAME', l_act_def_name);
1651 fnd_message.raise_error;
1652 ELSE
1653 CLOSE cur_act_def_org_tp;
1654 END IF;
1655 --
1656 hr_utility.set_location(' Leaving:' || l_proc,10);
1657 Exception
1658 when app_exception.application_exception then
1659 IF hr_multi_message.exception_add
1660 (p_associated_column1 => 'OTA_CATEGORY_USAGES.CATEGORY'
1661 ,p_same_associated_columns => 'Y'
1662 ) THEN
1663 hr_utility.set_location(' Leaving:'|| l_proc,20);
1664 raise;
1665 END IF;
1666
1667 hr_utility.set_location(' Leaving:'|| l_proc,30);
1668 --
1669 End Chk_act_def_for_org_tp;
1670 --
1671 end ota_ctu_bus;