1 Package Body ota_coi_bus as
2 /* $Header: otcoirhi.pkb 120.3 2005/08/12 02:46 pchandra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_coi_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_chat_id number default null;
15 g_object_id number default null;
16 g_object_type varchar2(30) default null;
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_chat_id in number
24 ,p_object_id in number
25 ,p_object_type in varchar2
26 ,p_associated_column1 in varchar2 default null
27 ,p_associated_column2 in varchar2 default null
28 ,p_associated_column3 in varchar2 default null
29 ) is
30 --
31 -- Declare cursor
32 --
33 -- EDIT_HERE In the following cursor statement add join(s) between
34 -- ota_chat_obj_inclusions and PER_BUSINESS_GROUPS_PERF
35 -- so that the security_group_id for
36 -- the current business group context can be derived.
37 -- Remove this comment when the edit has been completed.
38 cursor csr_sec_grp is
39 select pbg.security_group_id,
40 pbg.legislation_code
41 from per_business_groups_perf pbg
42 , ota_chat_obj_inclusions coi
43 -- , EDIT_HERE table_name(s) 333
44 where coi.chat_id = p_chat_id
45 and coi.object_id = p_object_id
46 and coi.object_type = p_object_type;
47 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
48 --
49 -- Declare local variables
50 --
51 l_security_group_id number;
52 l_proc varchar2(72) := g_package||'set_security_group_id';
53 l_legislation_code varchar2(150);
54 --
55 begin
56 --
57 hr_utility.set_location('Entering:'|| l_proc, 10);
58 --
59 -- Ensure that all the mandatory parameter are not null
60 --
61 hr_api.mandatory_arg_error
62 (p_api_name => l_proc
63 ,p_argument => 'chat_id'
64 ,p_argument_value => p_chat_id
65 );
66 hr_api.mandatory_arg_error
67 (p_api_name => l_proc
68 ,p_argument => 'object_id'
69 ,p_argument_value => p_object_id
70 );
71 hr_api.mandatory_arg_error
72 (p_api_name => l_proc
73 ,p_argument => 'object_type'
74 ,p_argument_value => p_object_type
75 );
76 --
77 open csr_sec_grp;
78 fetch csr_sec_grp into l_security_group_id
79 , l_legislation_code;
80 --
81 if csr_sec_grp%notfound then
82 --
83 close csr_sec_grp;
84 --
85 -- The primary key is invalid therefore we must error
86 --
87 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
88 hr_multi_message.add
89 (p_associated_column1
90 => nvl(p_associated_column1,'CHAT_ID')
91 ,p_associated_column2
92 => nvl(p_associated_column2,'OBJECT_ID')
93 ,p_associated_column3
94 => nvl(p_associated_column3,'OBJECT_TYPE')
95 );
96 --
97 else
98 close csr_sec_grp;
99 --
100 -- Set the security_group_id in CLIENT_INFO
101 --
102 hr_api.set_security_group_id
103 (p_security_group_id => l_security_group_id
104 );
105 --
106 -- Set the sessions legislation context in HR_SESSION_DATA
107 --
108 hr_api.set_legislation_context(l_legislation_code);
109 end if;
110 --
111 hr_utility.set_location(' Leaving:'|| l_proc, 20);
112 --
113 end set_security_group_id;
114 --
115 -- ---------------------------------------------------------------------------
116 -- |---------------------< return_legislation_code >-------------------------|
117 -- ---------------------------------------------------------------------------
118 --
119 Function return_legislation_code
120 (p_chat_id in number
121 ,p_object_id in number
122 ,p_object_type in varchar2
123 )
124 Return Varchar2 Is
125 --
126 -- Declare cursor
127 --
128 -- EDIT_HERE In the following cursor statement add join(s) between
129 -- ota_chat_obj_inclusions and PER_BUSINESS_GROUPS_PERF
130 -- so that the legislation_code for
131 -- the current business group context can be derived.
132 -- Remove this comment when the edit has been completed.
133 cursor csr_leg_code is
134 select pbg.legislation_code
135 from per_business_groups_perf pbg
136 , ota_chat_obj_inclusions coi
137 -- , EDIT_HERE table_name(s) 333
138 where coi.chat_id = p_chat_id
139 and coi.object_id = p_object_id
140 and coi.object_type = p_object_type;
141 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
142 --
143 -- Declare local variables
144 --
145 l_legislation_code varchar2(150);
146 l_proc varchar2(72) := g_package||'return_legislation_code';
147 --
148 Begin
149 --
150 hr_utility.set_location('Entering:'|| l_proc, 10);
151 --
152 -- Ensure that all the mandatory parameter are not null
153 --
154 hr_api.mandatory_arg_error
155 (p_api_name => l_proc
156 ,p_argument => 'chat_id'
157 ,p_argument_value => p_chat_id
158 );
159 hr_api.mandatory_arg_error
160 (p_api_name => l_proc
161 ,p_argument => 'object_id'
162 ,p_argument_value => p_object_id
163 );
164 hr_api.mandatory_arg_error
165 (p_api_name => l_proc
166 ,p_argument => 'object_type'
167 ,p_argument_value => p_object_type
168 );
169 --
170 if (( nvl(ota_coi_bus.g_chat_id, hr_api.g_number)
171 = p_chat_id)
172 and ( nvl(ota_coi_bus.g_object_id, hr_api.g_number)
173 = p_object_id)
174 and ( nvl(ota_coi_bus.g_object_type, hr_api.g_varchar2)
175 = p_object_type)) then
176 --
177 -- The legislation code has already been found with a previous
178 -- call to this function. Just return the value in the global
179 -- variable.
180 --
181 l_legislation_code := ota_coi_bus.g_legislation_code;
182 hr_utility.set_location(l_proc, 20);
183 else
184 --
185 -- The ID is different to the last call to this function
186 -- or this is the first call to this function.
187 --
188 open csr_leg_code;
189 fetch csr_leg_code into l_legislation_code;
190 --
191 if csr_leg_code%notfound then
192 --
193 -- The primary key is invalid therefore we must error
194 --
195 close csr_leg_code;
196 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
197 fnd_message.raise_error;
198 end if;
199 hr_utility.set_location(l_proc,30);
200 --
201 -- Set the global variables so the values are
202 -- available for the next call to this function.
203 --
204 close csr_leg_code;
205 ota_coi_bus.g_chat_id := p_chat_id;
206 ota_coi_bus.g_object_id := p_object_id;
207 ota_coi_bus.g_object_type := p_object_type;
208 ota_coi_bus.g_legislation_code := l_legislation_code;
209 end if;
210 hr_utility.set_location(' Leaving:'|| l_proc, 40);
211 return l_legislation_code;
212 end return_legislation_code;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-----------------------< chk_non_updateable_args >------------------------|
216 -- ----------------------------------------------------------------------------
217 -- {Start Of Comments}
218 --
219 -- Description:
220 -- This procedure is used to ensure that non updateable attributes have
221 -- not been updated. If an attribute has been updated an error is generated.
222 --
223 -- Pre Conditions:
224 -- g_old_rec has been populated with details of the values currently in
225 -- the database.
226 --
227 -- In Arguments:
228 -- p_rec has been populated with the updated values the user would like the
229 -- record set to.
230 --
231 -- Post Success:
232 -- Processing continues if all the non updateable attributes have not
233 -- changed.
234 --
235 -- Post Failure:
236 -- An application error is raised if any of the non updatable attributes
237 -- have been altered.
238 --
239 -- {End Of Comments}
240 -- ----------------------------------------------------------------------------
241 Procedure chk_non_updateable_args
242 (p_effective_date in date
243 ,p_rec in ota_coi_shd.g_rec_type
244 ) IS
245 --
246 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
247 --
248 Begin
249 --
250 -- Only proceed with the validation if a row exists for the current
251 -- record in the HR Schema.
252 --
253 IF NOT ota_coi_shd.api_updating
254 (p_chat_id => p_rec.chat_id
255 ,p_object_id => p_rec.object_id
256 ,p_object_type => p_rec.object_type
257 ,p_object_version_number => p_rec.object_version_number
258 ) THEN
259 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
260 fnd_message.set_token('PROCEDURE ', l_proc);
261 fnd_message.set_token('STEP ', '5');
262 fnd_message.raise_error;
263 END IF;
264 --
265 -- EDIT_HERE: Add checks to ensure non-updateable args have
266 -- not been updated.
267 --
268 End chk_non_updateable_args;
269 --
270 -- ----------------------------------------------------------------------------
271 -- |---------------------------< check_unique_key >---------------------------|
272 -- ----------------------------------------------------------------------------
273 --
274 -- PUBLIC
275 -- Description:
276 -- Validates the unique key.
277 -- The module version and module category must form a unique key.
278 --
279 Procedure check_unique_key
280 (
281 p_chat_id in number
282 ,p_object_id in number
283 ) is
284 --
285 v_exists varchar2(1);
286 v_proc varchar2(72) := g_package||'check_unique_key';
287 --
288 cursor sel_unique_key is
289 select 'Y'
290 from OTA_CHAT_OBJ_INCLUSIONS coi
291 where coi.chat_id = p_chat_id
292 and coi.object_id = p_object_id
293 and coi.object_type = 'C';
294 --
295 Begin
296 hr_utility.set_location('Entering:'|| v_proc, 5);
297 --
298 Open sel_unique_key;
299 fetch sel_unique_key into v_exists;
300 --
301 if sel_unique_key%found then
302 close sel_unique_key;
303
304 fnd_message.set_name('OTA', 'OTA_13676_DCI_DUPLICATE');
305 fnd_message.raise_error;
306 end if;
307 close sel_unique_key;
308 --
309 hr_utility.set_location(' Leaving:'|| v_proc, 10);
310 Exception
311 WHEN app_exception.application_exception THEN
312
313 IF hr_multi_message.exception_add(
314 p_associated_column1 => 'OTA_CHAT_OBJ_INCLUSIONS.chat_id',
315 p_associated_column2 => 'OTA_CHAT_OBJ_INCLUSIONS.object_id')
316 THEN
317 hr_utility.set_location(' Leaving:'||v_proc, 22);
318 RAISE;
319
320 END IF;
321 hr_utility.set_location(' Leaving:'||v_proc, 25);
322 End check_unique_key;
323 --
324 -- ----------------------------------------------------------------------------
325 -- |--------------------------< check_chat_category_dates >------------------------|
326 -- ----------------------------------------------------------------------------
327 --
328 -- PUBLIC
329 -- Description:
333 (
330 -- Validates the startdate and enddate with respect to category dates.
331 --
332 Procedure check_chat_category_dates
334 p_chat_id in number
335 , p_object_id in number
336 ) is
337 --
338 -- Declare cursors and local variables
339 --
340 -- Cursor to get value if parent category is already exits in child hierarchy of base category
341
342 CURSOR csr_cat_start_end_date is
343 SELECT
344 start_date_active,
345 nvl(end_date_active, to_date ('31-12-4712', 'DD-MM-YYYY'))
346 FROM ota_category_usages
347 WHERE category_usage_id =p_object_id;
348
349 CURSOR csr_chat_start_end_date IS
350 SELECT start_date_active,
351 nvl(end_date_active, to_date ('31-12-4712', 'DD-MM-YYYY'))
352 FROM ota_chats_b
353 WHERE chat_id = p_chat_id;
354
355
356 --
357 -- Variables for API Boolean parameters
358 l_proc varchar2(72) := g_package ||'check_category_dates';
359 l_cat_start_date date;
360 l_cat_end_date date;
361 l_chat_start_date date;
362 l_chat_end_date date;
363
364 Begin
365 hr_utility.set_location(' Entering:' || l_proc,10);
366 --
367 IF hr_multi_message.no_exclusive_error
368 (p_check_column1 => 'OTA_CHATS_B.START_DATE_ACTIVE'
369 ,p_check_column2 => 'OTA_CHATS_B.END_DATE_ACTIVE'
370 ,p_associated_column1 => 'OTA_CHATS_B.START_DATE_ACTIVE'
371 ,p_associated_column2 => 'OTA_CHATS_B.END_DATE_ACTIVE'
372 ) THEN
373 --
374 OPEN csr_cat_start_end_date;
375 FETCH csr_cat_start_end_date into l_cat_start_date, l_cat_end_date;
376
377 OPEN csr_chat_start_end_date;
378 FETCH csr_chat_start_end_date into l_chat_start_date, l_chat_end_date;
379
380 IF csr_cat_start_end_date%FOUND AND csr_chat_start_end_date%FOUND THEN
381 CLOSE csr_cat_start_end_date;
382 CLOSE csr_chat_start_end_date;
383 IF ( l_cat_start_date > l_chat_start_date
384 or l_cat_end_date < l_chat_end_date
385 ) THEN
386 --
387 fnd_message.set_name ( 'OTA','OTA_443833_CHT_OUT_OF_CAT_DATE');
388 fnd_message.raise_error;
389 --
390 End IF;
391 ELSE
392 CLOSE csr_cat_start_end_date;
393 CLOSE csr_chat_start_end_date;
394 End IF;
395 End IF;
396 --
397 hr_utility.set_location(' Leaving:' || l_proc,10);
398 Exception
399 when app_exception.application_exception then
400 IF hr_multi_message.exception_add
401 (p_associated_column1 => 'OTA_CHATS_B.START_DATE_ACTIVE'
402 ,p_associated_column2 => 'OTA_CHATS_B.END_DATE_ACTIVE'
403 ) THEN
404 hr_utility.set_location(' Leaving:'|| l_proc,20);
405 raise;
406 END IF;
407
408 hr_utility.set_location(' Leaving:'|| l_proc,30);
409 --
410 End check_chat_category_dates;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |----------------------------< check_multiple_primary_ctgr >---------------|
414 -- ----------------------------------------------------------------------------
415 --
416 -- PUBLIC
417 -- Description:
418 -- There can be only one primary category for a chat..
419 --
420 Procedure check_multiple_primary_ctgr
421 (
422 p_chat_id in number
423 ) is
424 --
425 v_proc varchar2(72) := g_package||'check_multiple_primary_ctgr';
426 v_exists varchar2(1);
427 cursor sel_multiple_primary is
428 select 'Y'
429 from OTA_CHAT_OBJ_INCLUSIONS coi
430 where coi.chat_id = p_chat_id
431 and coi.primary_flag = 'Y';
432 --
433 Begin
434 hr_utility.set_location('Entering:'|| v_proc, 5);
435 --
436 Open sel_multiple_primary;
437 fetch sel_multiple_primary into v_exists;
438 --
439 if sel_multiple_primary%found then
440 close sel_multiple_primary;
441
442 fnd_message.set_name('OTA', 'OTA_13676_DCI_DUPLICATE');
443 fnd_message.raise_error;
444 end if;
445 close sel_multiple_primary;
446 --
447 hr_utility.set_location(' Leaving:'|| v_proc, 10);
448
449 Exception
450 WHEN app_exception.application_exception THEN
451
452 IF hr_multi_message.exception_add(
453 p_associated_column1 => 'OTA_CHAT_OBJ_INCLUSIONS.PRIMARY_FLAG')
454
455 THEN
456
457 hr_utility.set_location(' Leaving:'||v_proc, 22);
458 RAISE;
459
460 END IF;
461 End check_multiple_primary_ctgr;
462 --
463 --
464 -- ----------------------------------------------------------------------------
465 -- |----------------------------< check_if_primary_category >-----------------|
466 -- ----------------------------------------------------------------------------
467 --
468 -- PUBLIC
469 -- Description:
470 -- Check if th category chat already has a primary category.
471 -- This category cannot be deleted.
472 --
473 Procedure check_if_primary_category
474 (
475 p_chat_id in number
476 ,p_object_id in number
477 ) is
478 --
479 v_proc varchar2(72) := g_package||'check_if_primary_category';
480 v_exists varchar2(1);
481 cursor sel_primary_category is
482 select 'Y'
483 from OTA_CHAT_OBJ_INCLUSIONS coi
484 where coi.chat_id = p_chat_id
485 and coi.object_id = p_object_id
486 and coi.primary_flag = 'Y';
487 --
488 Begin
492 fetch sel_primary_category into v_exists;
489 hr_utility.set_location('Entering:'|| v_proc, 5);
490 --
491 Open sel_primary_category;
493 --
494 if sel_primary_category%found then
495 close sel_primary_category;
496
497 fnd_message.set_name('OTA', 'OTA_443941_CHT_DEL_PRIMARY');
498 fnd_message.raise_error;
499 end if;
500 close sel_primary_category;
501 --
502 hr_utility.set_location(' Leaving:'|| v_proc, 10);
503 Exception
504 WHEN app_exception.application_exception THEN
505
506 IF hr_multi_message.exception_add(
507 p_associated_column1 => 'OTA_CHAT_OBJ_INCLUSIONS.PRIMARY_FLAG')
508
509 THEN
510
511 hr_utility.set_location(' Leaving:'||v_proc, 22);
512 RAISE;
513
514 END IF;
515 hr_utility.set_location(' Leaving:'||v_proc, 25);
516 End check_if_primary_category;
517 --
518 -- ----------------------------------------------------------------------------
519 -- |----------------------------< check_start_end_dates >-----------------|
520 -- ----------------------------------------------------------------------------
521 -- PUBLIC
522 -- Description:
523 -- Validates the startdate and enddate.
524 -- Startdate must be less than, or equal to, enddate.
525 --
526 Procedure check_start_end_dates
527 (
528 p_start_date in date
529 ,p_end_date in date
530 ) is
531 --
532 v_proc varchar2(72) := g_package||'check_start_end_dates';
533 --
534 Begin
535 --
536 hr_utility.set_location('Entering:'|| v_proc, 5);
537 --
538 ota_general.check_start_end_dates( p_start_date, p_end_date);
539 --
540 hr_utility.set_location(' Leaving:'|| v_proc, 10);
541
542 Exception
543 WHEN app_exception.application_exception THEN
544
545 IF hr_multi_message.exception_add(
546 p_associated_column1 => 'OTA_CHAT_OBJ_INCLUSIONS.START_DATE_ACTIVE'
547 ,p_associated_column2 => 'OTA_CHAT_OBJ_INCLUSIONS.END_DATE_ACTIVE')
548 THEN
549
550 hr_utility.set_location(' Leaving:'||v_proc, 22);
551 RAISE;
552
553 END IF;
554 hr_utility.set_location(' Leaving:'||v_proc, 25);
555 --
556 End check_start_end_dates;
557 --
558
559 -- ----------------------------------------------------------------------------
560 -- |---------------------------< insert_validate >----------------------------|
561 -- ----------------------------------------------------------------------------
562 Procedure insert_validate
563 (p_effective_date in date
564 ,p_chat_id in number
565 ,p_object_id in number
566 ,p_object_type in varchar2
567 ,p_rec in ota_coi_shd.g_rec_type
568 ) is
569 --
570 l_proc varchar2(72) := g_package||'insert_validate';
571 --
572 Begin
573 hr_utility.set_location('Entering:'||l_proc, 5);
574 --
575 -- Call all supporting business operations
576 --
577 --
578 -- EDIT_HERE: As this table does not have a mandatory business_group_id
579 -- column, ensure client_info is populated by calling a suitable
580 -- ???_???_bus.set_security_group_id procedure, or add one of the following
581 -- comments:
582 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
583 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
584 --
585 -- Validate Dependent Attributes
586 --
587 check_unique_key( p_chat_id
588 , p_object_id );
589
590 if p_rec.primary_flag = 'Y' then
591 check_multiple_primary_ctgr(p_chat_id);
592 check_chat_category_dates(p_chat_id => p_rec.chat_id
593 , p_object_id => p_rec.object_id);
594 end if;
595
596 check_start_end_dates(p_rec.start_date_active
597 ,p_rec.end_date_active);
598
599 --
600
601 --
602 hr_utility.set_location(' Leaving:'||l_proc, 10);
603 End insert_validate;
604 --
605 -- ----------------------------------------------------------------------------
606 -- |---------------------------< update_validate >----------------------------|
607 -- ----------------------------------------------------------------------------
608 Procedure update_validate
609 (p_effective_date in date
610 ,p_rec in ota_coi_shd.g_rec_type
611 ) is
612 --
613 l_proc varchar2(72) := g_package||'update_validate';
614 --
615 Begin
616 hr_utility.set_location('Entering:'||l_proc, 5);
617 --
618 -- Call all supporting business operations
619 --
620 --
621 -- EDIT_HERE: As this table does not have a mandatory business_group_id
622 -- column, ensure client_info is populated by calling a suitable
623 -- ???_???_bus.set_security_group_id procedure, or add one of the following
624 -- comments:
625 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
626 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
627 --
628 -- Validate Dependent Attributes
629 --
630 chk_non_updateable_args
631 (p_effective_date => p_effective_date
632 ,p_rec => p_rec
633 );
634 --
635 --
636 hr_utility.set_location(' Leaving:'||l_proc, 10);
637 End update_validate;
638 --
639 -- ----------------------------------------------------------------------------
640 -- |---------------------------< delete_validate >----------------------------|
641 -- ----------------------------------------------------------------------------
642 Procedure delete_validate
643 (p_rec in ota_coi_shd.g_rec_type
644 ) is
645 --
646 l_proc varchar2(72) := g_package||'delete_validate';
647 --
648 Begin
649 hr_utility.set_location('Entering:'||l_proc, 5);
650 --
651 -- Call all supporting business operations
652 --
653 -- check_if_primary_category(p_rec.chat_id, p_rec.object_id);
654
655 hr_utility.set_location(' Leaving:'||l_proc, 10);
656 End delete_validate;
657 --
658 end ota_coi_bus;
[Home] [Help]