[Home] [Help]
PACKAGE BODY: APPS.OTA_TSR_BUS
Source
1 Package Body ota_tsr_bus as
2 /* $Header: ottsr01t.pkb 120.2 2005/08/08 23:27:40 ssur noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tsr_bus.'; -- Global package name
9 g_legislation_code varchar2(150) default null;
10 -- ici insert tsr.cat
11 --*******************************************************************************
12 -- TPL: ADDITIONAL API
13 --
14 -- Version Date Author Reason
15 -- 1.01 15/11/94 M.Roychowdhury Added business rules
16 -- 10.2 28feb95 lparient.FR New API generator, new business rules
17 -- 10.11 01Jun95 lparient.FR Name and person_id became mutually exclusive
18 -- Unique key was changed.
19 --*******************************************************************************
20 --
21 -- ***************************
22 -- ADDITIONAL GLOBAL VARIABLES
23 -- ***************************
24 --
25 g_trainer_type varchar2(10) := 'T';
26 g_venue_type varchar2(10) := 'V';
27 --
28 --
29 --
30 -- **************************************************
31 -- MANUALLY WRITTEN SECTION AND GENERATED API SECTION
32 -- **************************************************
33 --
34 -- procedure insert_validate2 must be called from generated procedure insert_validate.
35 -- procedure update_validate2 must be called from generated procedure update_validate.
36 -- procedure delete_validate2 must be called from generated procedure delete_validate.
37 --
38 --*******************************************************************************
39 --
40 --added for bug4310348 by jbharath
41 -- ---------------------------------------------------------------------------
42 -- |----------------------< set_security_group_id >--------------------------|
43 -- ---------------------------------------------------------------------------
44 --
45 Procedure set_security_group_id
46 (p_supplied_resource_id in number
47 ,p_associated_column1 in varchar2 default null
48 ) is
49 --
50 -- Declare cursor
51 --
52 cursor csr_sec_grp is
53 select pbg.security_group_id,
54 pbg.legislation_code
55 from per_business_groups_perf pbg
56 , ota_suppliable_resources tsr
57 where tsr.supplied_resource_id = p_supplied_resource_id
58 and pbg.business_group_id = tsr.business_group_id;
59 --
60 -- Declare local variables
61 --
62 l_security_group_id number;
63 l_proc varchar2(72) := g_package||'set_security_group_id';
64 l_legislation_code varchar2(150);
65 --
66 begin
67 --
68 hr_utility.set_location('Entering:'|| l_proc, 10);
69 --
70 -- Ensure that all the mandatory parameter are not null
71 --
72 hr_api.mandatory_arg_error
73 (p_api_name => l_proc
74 ,p_argument => 'supplied_resource_id'
75 ,p_argument_value => p_supplied_resource_id
76 );
77 --
78 open csr_sec_grp;
79 fetch csr_sec_grp into l_security_group_id
80 , l_legislation_code;
81 --
82 if csr_sec_grp%notfound then
83 --
84 close csr_sec_grp;
85 --
86 -- The primary key is invalid therefore we must error
87 --
88 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
89 hr_multi_message.add
90 (p_associated_column1
91 => nvl(p_associated_column1,'SUPPLIED_RESOURCE_ID')
92 );
93 --
94 else
95 close csr_sec_grp;
96 --
97 -- Set the security_group_id in CLIENT_INFO
98 --
99 hr_api.set_security_group_id
100 (p_security_group_id => l_security_group_id
101 );
102 --
103 -- Set the sessions legislation context in HR_SESSION_DATA
104 --
105 hr_api.set_legislation_context(l_legislation_code);
106 end if;
107 --
108 hr_utility.set_location(' Leaving:'|| l_proc, 20);
109 --
110 end set_security_group_id;
111 --
112 -- added for bug#4310348 by jbharath
113 --
114 --============================================================================
115 -- GENERAL PROCEDURES
116 --==============================================================================
117 Procedure constraint_error2
118 (p_constraint_name varchar2) Is
119 --
120 l_proc varchar2(72) := g_package||'constraint_error2';
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'||l_proc, 5);
125 --
126 if p_constraint_name = 'startdate_is_mandatory' then
127 fnd_message.set_name('OTA','OTA_13457_GEN_MAND_START_DATE');
128 elsif p_constraint_name = 'end_precedes_start' then
129 fnd_message.set_name('OTA','OTA_13312_GEN_DATE_ORDER');
130 elsif p_constraint_name = 'location_is_not_active' then
131 fnd_message.set_name('OTA','OTA_13377_GEN_LOCAT_NOT_ACTIVE');
132 --
133 elsif p_constraint_name = 'resource_type_is_mandatory' then
134 fnd_message.set_name('OTA','OTA_13222_GEN_MANDATORY_VALUE');
135 fnd_message.set_token('FIELD','Supplied resource Type');
136 fnd_message.set_token('OPTIONAL_EXTENSION','');
137 elsif p_constraint_name = 'vendor_is_mandatory' then
138 fnd_message.set_name('OTA','OTA_13222_GEN_MANDATORY_VALUE');
139 fnd_message.set_token('FIELD','Training Center or Supplier');
140 fnd_message.set_token('OPTIONAL_EXTENSION','');
141 elsif p_constraint_name = 'consumable_flag_is_mandatory' then
142 fnd_message.set_name('OTA','OTA_13222_GEN_MANDATORY_VALUE');
143 fnd_message.set_token('FIELD','Resource Consumable flag');
144 fnd_message.set_token('OPTIONAL_EXTENSION','');
145 --
146 elsif (p_constraint_name='cost_without_currency' or p_constraint_name='currency_without_cost') then
147 fnd_message.set_name('OTA','OTA_13203_TSR_COST_CURRENCY');
148 fnd_message.set_token('FIELD1','CURRENCY');
149 fnd_message.set_token('FIELD2','COST');
150 --
151 elsif p_constraint_name = 'wrong_consumable_flag' then
152 fnd_message.set_name('OTA','OTA_13204_GEN_INVALID_LOOKUP');
153 fnd_message.set_token('FIELD','CONSUMABLE_FLAG');
154 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
155 --
156 elsif p_constraint_name = 'address_must_be_null' then
157 fnd_message.set_name('OTA','OTA_13223_GEN_MANDATORY_NULL');
158 fnd_message.set_token('FIELD','Address line');
159 fnd_message.set_token('OPTIONAL_EXTENSION','No location.');
160 --
161 elsif p_constraint_name = 'del_child_trb_exists' then
162 fnd_message.set_name('OTA','OTA_13227_TSR_TRB_EXISTS');
163 elsif p_constraint_name = 'del_child_rud_exists' then
164 fnd_message.set_name('OTA','OTA_13229_TSR_RUD_EXISTS');
165 --
166 elsif p_constraint_name = 'resource_already_exists' then
167 fnd_message.set_name('OTA','OTA_13381_TSR_DUPLICATE');
168 elsif p_constraint_name = 'cons_flag_other_excl' then
169 fnd_message.set_name('OTA','OTA_13382_TSR_CONS_FLAG_EXCL');
170 elsif p_constraint_name = 'resource_type_is_not_updatable' then
171 fnd_message.set_name('OTA','OTA_13378_TSR_TYPE_NOT_UPD');
172 elsif p_constraint_name = 'new_date_invalids_usage_date' then
173 fnd_message.set_name('OTA','OTA_13379_TSR_DATES_RUD');
174 elsif p_constraint_name = 'new_date_invalids_booking_date' then
175 fnd_message.set_name('OTA','OTA_13380_TSR_DATES_TRB');
176 --
177 elsif p_constraint_name = 'mutual_exclusive' then
178 fnd_message.set_name('OTA','OTA_13222_GEN_MANDATORY_VALUE');
179 fnd_message.set_token('FIELD','Either Training Center or Supplier');
180 fnd_message.set_token('OPTIONAL_EXTENSION','');
181
182 else
183 fnd_message.set_name('OTA','OTA_13259_GEN_UNKN_CONSTRAINT');
184 fnd_message.set_token('CONSTRAINT',p_constraint_name);
185 End If;
186 --
187 fnd_message.raise_error;
188 --
189 hr_utility.set_location(' Leaving:'||l_proc, 10);
190
191 End constraint_error2;
192 --======================================================================
193 --======================================================================
194 function from_other_type (p_type in varchar2)
195 return boolean is
196 begin
197 --
198 if p_type = g_venue_type or p_type = g_trainer_type then
199 return false;
200 else
201 return true;
202 end if;
203 --
204 end from_other_type;
205
206 --
207 --***************************************************************************
208 -- CHECKS OVER TYPE AND KEY (Vendor,person and name)
209 --***************************************************************************
210 procedure check_tsr_type (
211 p_resource_type varchar2)
212 is
213 -----------------
214 begin
215 --
216 if p_resource_type is null then
217 constraint_error2('resource_type_is_mandatory');
218 end if;
219 --
220 ota_general.check_domain_value ('RESOURCE_TYPE',p_resource_type);
221 --
222 end check_tsr_type;
223 --===========================================================================
224 --===========================================================================
225 --===========================================================================
226 -- The following primary keys operate for Suppliable Resources
227 -- Type Key
228 -- ---- ---
229 -- V Name,Supplier,Centre,Location
230 -- T Name,Supplier (anonymous trainer)
231 -- T PERSON_ID (Named Trainer)
232 -- O Name,Supplier,Centre
233 --===========================================================================
234 --
235 -- PRIVATE
236 --
237 procedure check_unique_key (
238 p_biz number,
239 p_suppres_id number,
240 p_name varchar2,
241 p_resource_type varchar2
242 ) is
243 ----------------
244 cursor cur_tsr is
245 select 1
246 from ota_suppliable_resources_vl
247 where business_group_id = p_biz
248 and (p_suppres_id is null or p_suppres_id <> supplied_resource_id)
249 and name = p_name
250 and resource_type = p_resource_type;
251 --
252 l_tsr_exists boolean;
253 --
254 l_dummy integer;
255 --
256 procedure chkp is
257 begin
258 hr_api.mandatory_arg_error(g_package,'business_group',p_biz);
259 end chkp;
260 -----------------
261 begin
262 --
263 chkp;
264 --
265 open cur_tsr;
266 fetch cur_tsr into l_dummy;
267 l_tsr_exists := cur_tsr%found;
268 close cur_tsr;
269 --
270 if l_tsr_exists then
271 constraint_error2('resource_already_exists');
272 end if;
273 --
274 end check_unique_key;
275 --===========================================================================
276 --===========================================================================
277 procedure check_tsr_key
278 (
279 p_business_group_id number,
280 p_resource_type varchar2,
281 p_vendor_id number,
282 p_supplied_resource_id number,
283 p_name varchar2,
284 p_training_center_id number
285 )
286 is
287 -------------
288 begin
289 --
290 if p_vendor_id is null and p_training_center_id is null then
291 constraint_error2('vendor_is_mandatory');
292 --elsif p_vendor_id is not null and p_training_center_id is not null then
293 -- constraint_error2('mutual_exclusive' ); Ench# 2004405
294 end if;
295 --
296 check_tsr_type(p_resource_type);
297 --
298 check_unique_key(
299 p_biz => p_business_group_id,
300 p_suppres_id => p_supplied_resource_id,
301 p_name => p_name,
302 p_resource_type => p_resource_type);
303 --
304 end check_tsr_key;
305 --*************************************************************
306 -- DATES VALIDATION
307 --***************************************************************
308 procedure check_dates_order
309 (
310 p_startdate date,
311 p_enddate date
312 )
313 is
314 -------------------
315 begin
316 --
317 if p_startdate is null then
318 constraint_error2('startdate_is_mandatory');
319 end if;
320 --
321 if p_enddate is not null then
322 if p_enddate < p_startdate then
323 constraint_error2('end_precedes_start');
324 end if;
325 end if;
326 --
327 end check_dates_order;
328 --
329 --=================================================================
330 --=================================================================
331 procedure check_tsr_dates
332 (
333 p_startdate date,
334 p_enddate date
335 )
336 is
337 -------------------
338 begin
339 --
340 check_dates_order (
341 p_startdate => p_startdate,
342 p_enddate => p_enddate);
343 end check_tsr_dates;
344 --
345 --********************************************************************************
346 -- UPDATE VALIDATION
347 --********************************************************************************
348 --
349 -- ----------------------------------------------------------------------------
350 -- |-----------------------< check_type_update >------------------------------|
351 -- ----------------------------------------------------------------------------
352 --
353 Procedure check_type_update (
354 p_tsr_id in number,
355 p_ovn in number,
356 p_resource_type in varchar2) is
357 ---------------
358 l_proc varchar2(72) := g_package||'check_type_update';
359 --
360 l_api_updating boolean;
361 --
362 procedure chkp is
363 begin
364 if not l_api_updating or p_tsr_id <> OTA_TSR_SHD.g_old_rec.supplied_resource_id then
365 constraint_error2('wrong_old_rec');
366 end if;
367 end chkp;
368 ---------------
369 Begin
370 --
371 hr_utility.set_location('Entering:'||l_proc, 5);
372 --
373 l_api_updating := OTA_TSR_SHD.api_updating (
374 p_supplied_resource_id => p_tsr_id,
375 p_object_version_number => p_ovn );
376 --
377 chkp;
378 --
379 if OTA_TSR_SHD.g_old_rec.resource_type <> p_resource_type then
380 constraint_error2('resource_type_is_not_updatable');
381 end if;
382 --
383 hr_utility.set_location(' Leaving:'||l_proc, 10);
384 --
385 End check_type_update;
386 --
387 -- ----------------------------------------------------------------------------
388 -- |-------------------------< check_usage_dates >----------------------------|
389 -- ----------------------------------------------------------------------------
390 --
391 Procedure check_usage_dates (p_supplied_resource_id in number,
392 p_start_date in date,
393 p_end_date in date) is
394 --
395 -- cursor to check that the dates of resource usag are within the bounds
396 -- of the resource dates
397 --
398 Cursor c_invalid_usage is
399 select 'X'
400 from ota_resource_usages
401 where supplied_resource_id = p_supplied_resource_id
402 and (start_date < p_start_date or
403 nvl(end_date, hr_api.g_eot) > nvl(p_end_date, hr_api.g_eot));
404 --
405 l_proc varchar2(72) := g_package||'check_usage_dates';
409 hr_utility.set_location('Entering:'||l_proc, 5);
406 l_dummy varchar2(1);
407 --
408 Begin
410 --
411 open c_invalid_usage;
412 fetch c_invalid_usage into l_dummy;
413 if c_invalid_usage%found then
414 --
415 close c_invalid_usage;
416 --
417 constraint_error2('new_date_invalids_usage_date');
418 --
419 end if;
420 --
421 close c_invalid_usage;
422 --
423 hr_utility.set_location(' Leaving:'||l_proc, 10);
424 End check_usage_dates;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |--------------------------< check_booking_dates >-------------------------|
428 -- ----------------------------------------------------------------------------
429 --
430 Procedure check_booking_dates (p_supplied_resource_id in number,
431 p_start_date in date,
432 p_end_date in date) is
433 --
434 Cursor c_invalid_bookings is
435 select 'X'
436 from ota_resource_bookings
437 where supplied_resource_id = p_supplied_resource_id
438 and (required_date_from < p_start_date
439 or
440 nvl(required_date_to,hr_api.g_eot) > nvl(p_end_date,hr_api.g_eot));
441 --
442 l_proc varchar2(72) := g_package||'check_booking_dates';
443 l_dummy varchar2(1);
444 --
445 Begin
446 hr_utility.set_location('Entering:'||l_proc, 5);
447 --
448 open c_invalid_bookings;
449 fetch c_invalid_bookings into l_dummy;
450 --
451 if c_invalid_bookings%found then
452 close c_invalid_bookings;
453 constraint_error2('new_date_invalids_booking_date');
454 end if;
455 --
456 close c_invalid_bookings;
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 10);
459 End check_booking_dates;
460 --**************************************************************
461 --**************************************************************
462 procedure check_tsr_new_dates
463 (
464 p_tsr_id number,
465 p_startdate date,
466 p_enddate date
467 )
468 is
469 ----------------
470 -- New dates cannot invalidate children's dates.
471 ---------------
472 procedure chkp is
473 begin
474 check_dates_order(p_startdate,p_enddate);
475 end chkp;
476 ---------------
477 begin
478 --
479 chkp;
480 --
481 check_booking_dates(p_tsr_id,p_startdate,p_enddate);
482 --
483 check_usage_dates(p_tsr_id,p_startdate,p_enddate);
484 --
485 end check_tsr_new_dates;
486 --
487 --*****************************************************************************
488 -- COST CHECKS
489 --*****************************************************************************
490 --
491 procedure check_currency (
492 p_currency in varchar2
493 )
494 is
495 -------------
496 begin
497 --
498 if p_currency is not null then
499 ota_general.check_currency_is_valid(p_currency);
500 end if;
501 --
502 end check_currency;
503 --=======================================================
504 --=======================================================
505 procedure check_cost_and_currency (
506 p_cost number,
507 p_currency_code varchar2
508 )
509 is
510 ---------------
511 begin
512 --
513 if p_currency_code is not null then
514 if p_cost is null then
515 constraint_error2('currency_without_cost');
516 end if ;
517 check_currency (p_currency_code);
518 --
519 elsif p_cost is not null then
520 constraint_error2('cost_without_currency');
521 end if;
522 --
523 end check_cost_and_currency;
524 --
525 --*****************************************************************************
526 -- STOCK CHECKS
527 --*****************************************************************************
528 --
529 -- PRIVATE ONLY (resource_type must be ok)
530 --
531 procedure check_consumable_flag
532 (
533 p_resource_type in varchar2,
534 p_consumable_flag in varchar2
535 )
536 is
537 -------------
538 begin
539 --
540 if p_consumable_flag is null then
541 constraint_error2('consumable_flag_is_mandatory');
542 elsif NOT (p_consumable_flag in ('N', 'Y')) then
543 constraint_error2('wrong_consumable_flag');
544 elsif p_consumable_flag = 'Y' and not from_other_type(p_resource_type) then
545 constraint_error2('cons_flag_other_excl');
546 end if;
547 --
548 end check_consumable_flag;
549 --*****************************************************************************
550 -- DELETE VALIDATION
551 --*****************************************************************************
552 --
553 -- ----------------------------------------------------------------------------
554 -- |--------------------------< check_bookings >------------------------------|
555 -- ----------------------------------------------------------------------------
556 --
557 -- Checks that no bookings exist for this resource before deletion
558 --
562 select 'X'
559 Procedure del_check_bookings (p_supplied_resource_id in number) is
560 --
561 Cursor c_bookings is
563 from ota_resource_bookings
564 where supplied_resource_id = p_supplied_resource_id;
565 --
566 l_proc varchar2(72) := g_package||'check_bookings';
567 l_dummy varchar2(1);
568 --
569 Begin
570 hr_utility.set_location('Entering:'||l_proc, 5);
571 --
572 open c_bookings;
573 fetch c_bookings into l_dummy;
574 --
575 if c_bookings%found then
576 close c_bookings;
577 constraint_error2('del_child_trb_exists');
578 end if;
579 --
580 close c_bookings;
581 --
582 hr_utility.set_location(' Leaving:'||l_proc, 10);
583 End del_check_bookings;
584 --
585 -- ----------------------------------------------------------------------------
586 -- |--------------------------< check_usages >------------------------------|
587 -- ----------------------------------------------------------------------------
588 --
589 -- Checks that resource is not being used before deletion
590 --
591 Procedure del_check_usages (p_supplied_resource_id in number) is
592 --
593 Cursor c_usages is
594 select 'X'
595 from ota_resource_usages
596 where supplied_resource_id = p_supplied_resource_id;
597 --
598 l_proc varchar2(72) := g_package||'check_usages';
599 l_dummy varchar2(1);
600 --
601 Begin
602 hr_utility.set_location('Entering:'||l_proc, 5);
603 --
604 open c_usages;
605 fetch c_usages into l_dummy;
606 --
607 if c_usages%found then
608 close c_usages;
609 constraint_error2('del_child_rud_exists');
610 end if;
611 --
612 close c_usages;
613 --
614 hr_utility.set_location(' Leaving:'||l_proc, 10);
615 End del_check_usages;
616
617 /** Added for globalization **/
618 -- ----------------------------------------------------------------------------
619 -- |-----------------------< chk_Training_center >---------------------------|
620 -- ----------------------------------------------------------------------------
621 -- Description : This procedure will be called by Insert_validate procedure and
622 -- Update_validaate procedure. This
623 -- procedure will check whether Training center exist or not.
624 --
625 Procedure chk_Training_center
626 (p_supplied_resource_id in number,
627 p_training_center_id in number)
628 IS
629
630
631 --
632 l_proc varchar2(72) := g_package||'chk_training_center';
633 l_exists varchar2(1);
634
635 Cursor c_training_center
636 IS
637 Select null
638 From HR_ALL_ORGANIZATION_UNITS
639 Where organization_id = p_training_center_id;
640
641 Begin
642 hr_utility.set_location('Entering:'||l_proc, 5);
643 if (((p_supplied_resource_id is not null) and
644 nvl(ota_tsr_shd.g_old_rec.training_center_id,hr_api.g_number) <>
645 nvl(p_training_center_id,hr_api.g_number))
646 or (p_supplied_resource_id is null)) then
647 --
648 hr_utility.set_location('Entering:'||l_proc, 10);
649 if (p_training_center_id is not null) then
650 hr_utility.set_location('Entering:'||l_proc, 15);
651 open c_training_center;
652 fetch c_training_center into l_exists;
653 if c_training_center%notfound then
654 close c_training_center;
655 fnd_message.set_name('OTA','OTA_13907_TSR_TRNCTR_NOT_EXIST');
656 fnd_message.raise_error;
657 end if;
658 close c_training_center;
659 hr_utility.set_location('Entering:'||l_proc, 20);
660 end if;
661 end if;
662 hr_utility.set_location('Entering:'||l_proc, 30);
663
664 end;
665
666 -- ----------------------------------------------------------------------------
667 -- |-----------------------------< chk_location >---------------------------|
668 -- ----------------------------------------------------------------------------
669 -- Description : This procedure will be called by Insert_validate procedure and
670 -- Update_validate procedure. This
671 -- procedure will check whether Location exist or not.
672
673 --
674 Procedure Chk_location
675 (p_supplied_resource_id in number,
676 p_location_id in number,
677 p_training_center_id in number)
678 IS
679
680
681 --
682 l_proc varchar2(72) := g_package||'chk_location';
683 l_exists varchar2(1);
684 Cursor c_location
685 IS
686 Select null
687 From HR_LOCATIONS_ALL loc
688 Where loc.location_id = p_location_id ;
689
690 Begin
691 hr_utility.set_location('Entering:'||l_proc, 5);
692
693 if (((p_supplied_resource_id is not null) and
694 nvl(ota_tsr_shd.g_old_rec.location_id,hr_api.g_number) <>
695 nvl(p_location_id,hr_api.g_number))
696 or (p_supplied_resource_id is null)) then
697 --
698 hr_utility.set_location('Entering:'||l_proc, 10);
699 if (p_location_id is not null) then
700 hr_utility.set_location('Entering:'||l_proc, 15);
701 open c_location;
705 fnd_message.set_name('OTA','OTA_13908_TSR_LOC_NOT_EXIST');
702 fetch c_location into l_exists;
703 if c_location%notfound then
704 close c_location;
706 fnd_message.raise_error;
707 end if;
708 close c_location;
709 hr_utility.set_location('Entering:'||l_proc, 20);
710 end if;
711 end if;
712 hr_utility.set_location('Entering:'||l_proc, 30);
713
714 end;
715
716 -- ----------------------------------------------------------------------------
717 -- |-----------------------------< chk_trainer >---------------------------|
718 -- ----------------------------------------------------------------------------
719 -- Description : This procedure will be called by Insert_validate procedure and
720 -- Update_validate procedure. This
721 -- procedure will check whether trainer exist or not.
722
723 --
724 Procedure Chk_trainer
725 (p_supplied_resource_id in number,
726 p_trainer_id in number,
727 p_business_group_id in number,
728 p_start_date IN DATE,
729 p_end_date IN DATE
730 )
731 IS
732
733
734 --
735 l_proc varchar2(72) := g_package||'chk_trainer';
736 l_exists varchar2(1);
737 l_global_business_group_id number := FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
738 --Bug 1991061
739 -- Enh 2530860 Following 2 cursors modified to support PTU . Included the table
740 -- PER_PERSON_TYPE_USAGES_F & PER_PERSON_TYPES
741
742 Cursor c_trainer_global
743 IS
744 Select null
745 From PER_ALL_PEOPLE_F per, PER_PERSON_TYPE_USAGES_F ptu, PER_PERSON_TYPES ppt
746 Where per.person_id = p_trainer_id
747 --AND trunc(p_start_date) between per.effective_start_date and per.effective_end_date Bug# 2956482
748 AND trunc(p_start_date) >= per.start_date
749 AND NVL(trunc(p_end_date),trunc(sysdate)) <= ptu.effective_end_date --bug no 3058027
750 AND trunc(p_start_date) between ptu.effective_start_date and ptu.effective_end_date
751 AND ptu.person_id = per.person_id
752 AND ppt.business_group_id = per.business_group_id
753 -- AND ppt.system_person_type in ('EMP','CWK') Bug# 2956482
754 AND ((ppt.system_person_type = 'EMP' and nvl(per.current_employee_flag,'N')='Y')
755 or (ppt.system_person_type = 'CWK' and nvl(per.current_npw_flag,'N')='Y'))
756 AND ptu.person_type_id = ppt.person_type_id ;
757
758 Cursor c_trainer
759 IS
760 Select null
761 From PER_ALL_PEOPLE_F per, PER_PERSON_TYPE_USAGES_F ptu, PER_PERSON_TYPES ppt
762 Where per.person_id = p_trainer_id
763 -- AND trunc(p_start_date) between per.effective_start_date and per.effective_end_date Bug# 2956482
764 AND trunc(p_start_date) >= per.start_date
765 AND NVL(trunc(p_end_date),trunc(sysdate)) <= ptu.effective_end_date --bug no 3058027
766 AND trunc(p_start_date) between ptu.effective_start_date and ptu.effective_end_date
767 AND ptu.person_id = per.person_id
768 AND ppt.business_group_id = per.business_group_id
769 AND per.business_group_id = p_business_group_id
770 -- AND ppt.system_person_type in ('EMP','CWK') Bug# 2956482
771 AND ((ppt.system_person_type = 'EMP' and nvl(per.current_employee_flag,'N')='Y')
772 or (ppt.system_person_type = 'CWK' and nvl(per.current_npw_flag,'N')='Y'))
773 AND ptu.person_type_id = ppt.person_type_id ;
774
775
776
777 --Bug 1991061
778
779 Begin
780 hr_utility.set_location('Entering:'||l_proc, 5);
781
782 if (((p_supplied_resource_id is not null) and
783 nvl(ota_tsr_shd.g_old_rec.trainer_id,hr_api.g_number) <>
784 nvl(p_trainer_id,hr_api.g_number))
785 or (p_supplied_resource_id is null)) or
786 nvl(ota_tsr_shd.g_old_rec.start_date, hr_api.g_date) <>
787 nvl(p_start_date, hr_api.g_date) OR
788 nvl(ota_tsr_shd.g_old_rec.end_date, hr_api.g_date) <>
789 nvl(p_end_date, hr_api.g_date)
790 then
791 --
792 hr_utility.set_location('Entering:'||l_proc, 10);
793 if (p_trainer_id is not null) then
794 if l_global_business_group_id is not null then
795 hr_utility.set_location('Entering:'||l_proc, 15);
796 open c_trainer_global;
797 fetch c_trainer_global into l_exists;
798 if c_trainer_global%notfound then
799 close c_trainer_global;
800 fnd_message.set_name('OTA','OTA_13906_TSR_TRAIN_NOT_EXIST');
801 fnd_message.raise_error;
802 end if;
803 close c_trainer_global;
804 hr_utility.set_location('leaving:'||l_proc, 20);
805 else
806 hr_utility.set_location('Entering:'||l_proc, 25);
807 open c_trainer;
808 fetch c_trainer into l_exists;
809 if c_trainer%notfound then
810 close c_trainer;
811 fnd_message.set_name('OTA','OTA_13906_TSR_TRAIN_NOT_EXIST');
812 fnd_message.raise_error;
813 end if;
814 close c_trainer;
815 hr_utility.set_location('Leaving:'||l_proc, 30);
816 end if;
817 end if;
818 end if;
819 hr_utility.set_location('Leaving:'||l_proc, 35);
820
821 end;
822 --
826 --
823 -- ----------------------------------------------------------------------------
824 -- |------------------------------< chk_df >----------------------------------|
825 -- ----------------------------------------------------------------------------
827 -- Description:
828 -- Validates all the Descriptive Flexfield values.
829 --
830 -- Prerequisites:
831 -- All other columns have been validated. Must be called as the
832 -- last step from insert_validate and update_validate.
833 --
834 -- In Arguments:
835 -- p_rec
836 --
837 -- Post Success:
838 -- If the Descriptive Flexfield structure column and data values are
839 -- all valid this procedure will end normally and processing will
840 -- continue.
841 --
842 -- Post Failure:
843 -- If the Descriptive Flexfield structure column value or any of
844 -- the data values are invalid then an application error is raised as
845 -- a PL/SQL exception.
846 --
847 -- Access Status:
848 -- Internal Row Handler Use Only.
849 --
850 -- ----------------------------------------------------------------------------
851 PROCEDURE chk_df
852 (p_rec IN ota_tsr_shd.g_rec_type
853 ) IS
854 --
855 l_proc varchar2(72) := g_package || 'chk_df';
856 --
857 BEGIN
858 hr_utility.set_location('Entering:'||l_proc,10);
859 --
860 IF ((p_rec.supplied_resource_id IS NOT NULL) AND (
861 NVL(ota_tsr_shd.g_old_rec.tsr_information_category, hr_api.g_varchar2) <>
862 NVL(p_rec.tsr_information_category, hr_api.g_varchar2) OR
863 NVL(ota_tsr_shd.g_old_rec.tsr_information1, hr_api.g_varchar2) <>
864 NVL(p_rec.tsr_information1, hr_api.g_varchar2) OR
865 NVL(ota_tsr_shd.g_old_rec.tsr_information2, hr_api.g_varchar2) <>
866 NVL(p_rec.tsr_information2, hr_api.g_varchar2) OR
867 NVL(ota_tsr_shd.g_old_rec.tsr_information3, hr_api.g_varchar2) <>
868 NVL(p_rec.tsr_information3, hr_api.g_varchar2) OR
869 NVL(ota_tsr_shd.g_old_rec.tsr_information4, hr_api.g_varchar2) <>
870 NVL(p_rec.tsr_information4, hr_api.g_varchar2) OR
871 NVL(ota_tsr_shd.g_old_rec.tsr_information5, hr_api.g_varchar2) <>
872 NVL(p_rec.tsr_information5, hr_api.g_varchar2) OR
873 NVL(ota_tsr_shd.g_old_rec.tsr_information6, hr_api.g_varchar2) <>
874 NVL(p_rec.tsr_information6, hr_api.g_varchar2) OR
875 NVL(ota_tsr_shd.g_old_rec.tsr_information7, hr_api.g_varchar2) <>
876 NVL(p_rec.tsr_information7, hr_api.g_varchar2) OR
877 NVL(ota_tsr_shd.g_old_rec.tsr_information8, hr_api.g_varchar2) <>
878 NVL(p_rec.tsr_information8, hr_api.g_varchar2) OR
879 NVL(ota_tsr_shd.g_old_rec.tsr_information9, hr_api.g_varchar2) <>
880 NVL(p_rec.tsr_information9, hr_api.g_varchar2) OR
881 NVL(ota_tsr_shd.g_old_rec.tsr_information10, hr_api.g_varchar2) <>
882 NVL(p_rec.tsr_information10, hr_api.g_varchar2) OR
883 NVL(ota_tsr_shd.g_old_rec.tsr_information11, hr_api.g_varchar2) <>
884 NVL(p_rec.tsr_information11, hr_api.g_varchar2) OR
885 NVL(ota_tsr_shd.g_old_rec.tsr_information12, hr_api.g_varchar2) <>
886 NVL(p_rec.tsr_information12, hr_api.g_varchar2) OR
887 NVL(ota_tsr_shd.g_old_rec.tsr_information13, hr_api.g_varchar2) <>
888 NVL(p_rec.tsr_information13, hr_api.g_varchar2) OR
889 NVL(ota_tsr_shd.g_old_rec.tsr_information14, hr_api.g_varchar2) <>
890 NVL(p_rec.tsr_information14, hr_api.g_varchar2) OR
891 NVL(ota_tsr_shd.g_old_rec.tsr_information15, hr_api.g_varchar2) <>
892 NVL(p_rec.tsr_information15, hr_api.g_varchar2) OR
893 NVL(ota_tsr_shd.g_old_rec.tsr_information16, hr_api.g_varchar2) <>
894 NVL(p_rec.tsr_information16, hr_api.g_varchar2) OR
895 NVL(ota_tsr_shd.g_old_rec.tsr_information17, hr_api.g_varchar2) <>
896 NVL(p_rec.tsr_information17, hr_api.g_varchar2) OR
897 NVL(ota_tsr_shd.g_old_rec.tsr_information18, hr_api.g_varchar2) <>
898 NVL(p_rec.tsr_information18, hr_api.g_varchar2) OR
899 NVL(ota_tsr_shd.g_old_rec.tsr_information19, hr_api.g_varchar2) <>
900 NVL(p_rec.tsr_information19, hr_api.g_varchar2) OR
901 NVL(ota_tsr_shd.g_old_rec.tsr_information20, hr_api.g_varchar2) <>
902 NVL(p_rec.tsr_information20, hr_api.g_varchar2) ) )
903 OR (p_rec.supplied_resource_id IS NULL) THEN
904 --
905 -- Only execute the validation if absolutely necessary:
906 -- a) During update, the structure column value or any
907 -- of the tsr_information values have actually changed.
908 -- b) During insert.
909 --
910
911 hr_dflex_utility.ins_or_upd_descflex_attribs
912 (p_appl_short_name => 'OTA'
913 ,p_descflex_name => 'OTA_SUPPLIABLE_RESOURCES'
914 ,p_attribute_category => p_rec.tsr_information_category
915 ,p_attribute1_name => 'TSR_INFORMATION1'
916 ,p_attribute1_value => p_rec.tsr_information1
917 ,p_attribute2_name => 'TSR_INFORMATION2'
918 ,p_attribute2_value => p_rec.tsr_information2
919 ,p_attribute3_name => 'TSR_INFORMATION3'
920 ,p_attribute3_value => p_rec.tsr_information3
921 ,p_attribute4_name => 'TSR_INFORMATION4'
922 ,p_attribute4_value => p_rec.tsr_information4
923 ,p_attribute5_name => 'TSR_INFORMATION5'
924 ,p_attribute5_value => p_rec.tsr_information5
925 ,p_attribute6_name => 'TSR_INFORMATION6'
926 ,p_attribute6_value => p_rec.tsr_information6
927 ,p_attribute7_name => 'TSR_INFORMATION7'
928 ,p_attribute7_value => p_rec.tsr_information7
932 ,p_attribute9_value => p_rec.tsr_information9
929 ,p_attribute8_name => 'TSR_INFORMATION8'
930 ,p_attribute8_value => p_rec.tsr_information8
931 ,p_attribute9_name => 'TSR_INFORMATION9'
933 ,p_attribute10_name => 'TSR_INFORMATION10'
934 ,p_attribute10_value => p_rec.tsr_information10
935 ,p_attribute11_name => 'TSR_INFORMATION11'
936 ,p_attribute11_value => p_rec.tsr_information11
937 ,p_attribute12_name => 'TSR_INFORMATION12'
938 ,p_attribute12_value => p_rec.tsr_information12
939 ,p_attribute13_name => 'TSR_INFORMATION13'
940 ,p_attribute13_value => p_rec.tsr_information13
941 ,p_attribute14_name => 'TSR_INFORMATION14'
942 ,p_attribute14_value => p_rec.tsr_information14
943 ,p_attribute15_name => 'TSR_INFORMATION15'
944 ,p_attribute15_value => p_rec.tsr_information15
945 ,p_attribute16_name => 'TSR_INFORMATION16'
946 ,p_attribute16_value => p_rec.tsr_information16
947 ,p_attribute17_name => 'TSR_INFORMATION17'
948 ,p_attribute17_value => p_rec.tsr_information17
949 ,p_attribute18_name => 'TSR_INFORMATION18'
950 ,p_attribute18_value => p_rec.tsr_information18
951 ,p_attribute19_name => 'TSR_INFORMATION19'
952 ,p_attribute19_value => p_rec.tsr_information19
953 ,p_attribute20_name => 'TSR_INFORMATION20'
954 ,p_attribute20_value => p_rec.tsr_information20
955 );
956 END IF;
957
958 --
959 hr_utility.set_location(' Leaving:'||l_proc,20);
960 END chk_df;
961
962
963 --
964 --
965 --*****************************************************************************
966 -- GENERAL VALIDATION PROCEDURES
967 --*****************************************************************************
968 procedure validation1
969 (
970 p_rec in ota_tsr_shd.g_rec_type
971 )
972 is
973 ---------------------
974 begin
975 --
976 check_tsr_key (
977 p_business_group_id => p_rec.business_group_id,
978 p_resource_type => p_rec.resource_type,
979 p_vendor_id => p_rec.vendor_id,
980 p_supplied_resource_id => p_rec.supplied_resource_id,
981 p_name => p_rec.name,
982 p_training_center_id => p_rec.training_center_id
983 );
984 --
985 check_tsr_dates (
986 p_startdate => p_rec.start_date,
987 p_enddate => p_rec.end_date);
988
989 chk_Training_center
990 (p_supplied_resource_id => p_rec.supplied_resource_id,
991 p_training_center_id => p_rec.training_center_id);
992
993
994 Chk_location
995 (p_supplied_resource_id => p_rec.supplied_resource_id,
996 p_location_id => p_rec.location_id,
997 p_training_center_id => p_rec.training_center_id);
998
999 Chk_trainer
1000 (p_supplied_resource_id => p_rec.supplied_resource_id,
1001 p_trainer_id => p_rec.trainer_id,
1002 p_business_group_id => p_rec.business_group_id,
1003 p_start_date => p_rec.start_date,
1004 p_end_date => p_rec.end_date);
1005 --
1006 end validation1;
1007 --=============================================================================
1008 --=============================================================================
1009 procedure validation2
1010 (
1011 p_rec in ota_tsr_shd.g_rec_type
1012 )
1013 is
1014 ---------------------
1015 begin
1016 --
1017 check_cost_and_currency (
1018 p_cost => p_rec.cost,
1019 p_currency_code => p_rec.currency_code );
1020 --
1021 end validation2;
1022 --=============================================================================
1023 --=============================================================================
1024 procedure insert_validate2
1025 (
1026 p_rec in ota_tsr_shd.g_rec_type
1027 )
1028 is
1029 ---------------------
1030 begin
1031 --
1032 validation1(p_rec);
1033 --
1034 validation2(p_rec);
1035 --
1036 end insert_validate2;
1037 --=============================================================================
1038 --=============================================================================
1039 procedure update_validate2
1040 (
1041 p_rec in ota_tsr_shd.g_rec_type
1042 )
1043 is
1044 ---------------------
1045 begin
1046 --
1047 check_type_update(
1048 p_tsr_id => p_rec.supplied_resource_id,
1049 p_ovn => p_rec.object_version_number,
1050 p_resource_type => p_rec.resource_type);
1051 --
1052 validation1(p_rec);
1053 --
1054 check_tsr_new_dates (
1055 p_tsr_id => p_rec.supplied_resource_id,
1056 p_startdate => p_rec.start_date,
1057 p_enddate => p_rec.end_date);
1058 --
1059 validation2(p_rec);
1060 --
1061 end update_validate2;
1062 --=============================================================================
1063 --=============================================================================
1064 procedure delete_validate2
1065 (
1066 p_rec in ota_tsr_shd.g_rec_type
1067 )
1068 is
1069 ---------------------
1070 begin
1071 --
1072 del_check_bookings(p_rec.supplied_resource_id);
1073 --
1074 del_check_usages(p_rec.supplied_resource_id);
1075 --
1076 end delete_validate2;
1077 --*****************************************************************************
1078 -- END OF MANUALLY WRITTEN SECTION
1079 --*****************************************************************************
1080 -- ----------------------------------------------------------------------------
1081 -- |---------------------------< insert_validate >----------------------------|
1082 -- ----------------------------------------------------------------------------
1083 Procedure insert_validate(p_rec in ota_tsr_shd.g_rec_type) is
1084 --
1085 l_proc varchar2(72) := g_package||'insert_validate';
1086 --
1087 Begin
1088 hr_utility.set_location('Entering:'||l_proc, 5);
1089 --
1090 -- Call all supporting business operations
1091 insert_validate2(p_rec);
1092 --
1093 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1094 --
1095 ota_tsr_bus.chk_df(p_rec);
1096 hr_utility.set_location(' Leaving:'||l_proc, 10);
1097 End insert_validate;
1098 --
1099 -- ----------------------------------------------------------------------------
1100 -- |---------------------------< update_validate >----------------------------|
1101 -- ----------------------------------------------------------------------------
1102 Procedure update_validate(p_rec in ota_tsr_shd.g_rec_type) is
1103 --
1104 l_proc varchar2(72) := g_package||'update_validate';
1105 --
1106 Begin
1107 hr_utility.set_location('Entering:'||l_proc, 5);
1108 --
1109 -- Call all supporting business operations
1110 update_validate2(p_rec);
1111 --
1112 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1113 --
1114 hr_utility.set_location(' Leaving:'||l_proc, 10);
1115 End update_validate;
1116 --
1117 -- ----------------------------------------------------------------------------
1118 -- |---------------------------< delete_validate >----------------------------|
1119 -- ----------------------------------------------------------------------------
1120 Procedure delete_validate(p_rec in ota_tsr_shd.g_rec_type) is
1121 --
1122 l_proc varchar2(72) := g_package||'delete_validate';
1123 --
1124 Begin
1125 hr_utility.set_location('Entering:'||l_proc, 5);
1126 --
1127 -- Call all supporting business operations
1128 delete_validate2(p_rec);
1129 --
1130 hr_utility.set_location(' Leaving:'||l_proc, 10);
1131 End delete_validate;
1132 --
1133
1134 -- ----------------------------------------------------------------------------
1135 -- |-----------------------< return_legislation_code >-------------------------|
1136 -- ----------------------------------------------------------------------------
1137 --
1138 -- {Start Of Comments}
1139 --
1140 -- Description:
1141 -- This function will be used by the user hooks. This will be used
1142 -- of by the user hooks of ota_suppliable_resources and
1143 -- ota_resource_booking row handler user hook business process.
1144 --
1145 -- Pre Conditions:
1146 -- This function will be called by the user hook packages.
1147 --
1148 -- In Arguments:
1149 -- SUPPLIED_RESOURCE_ID
1150 --
1151 -- Post Success:
1152 -- Processing continues.
1153 --
1154 -- Post Failure:
1155 -- Errors out
1156 --
1157 -- Developer Implementation Notes:
1158 --
1159 -- Access Status:
1160 -- Internal Development Use Only.
1161 --
1162 -- {End Of Comments}
1163 --------------------------------------------------------------------------------
1164 --
1165 Function return_legislation_code
1166 ( p_supplied_resource_id in number
1167 ) return varchar2 is
1168 --
1169 -- Declare cursor
1170 --
1171 cursor csr_leg_code is
1172 select legislation_code
1173 from per_business_groups_perf pbg,
1174 ota_suppliable_resources tsr
1175 where pbg.business_group_id = tsr.business_group_id
1176 and tsr.supplied_resource_id = p_supplied_resource_id;
1177
1178
1179 l_proc varchar2(72) := g_package||'return_legislation_code';
1180 l_legislation_code varchar2(150);
1181 --
1182 Begin
1183 hr_utility.set_location('Entering:'||l_proc, 5);
1184 --
1185 -- Ensure that all the mandatory parameters are not null
1186 --
1187 hr_api.mandatory_arg_error (p_api_name => l_proc,
1188 p_argument => 'supplied_resource_id',
1189 p_argument_value => p_supplied_resource_id);
1190 open csr_leg_code;
1191 fetch csr_leg_code into l_legislation_code;
1192 if csr_leg_code%notfound then
1193 close csr_leg_code;
1194 --
1195 -- The primary key is invalid therefore we must error out
1196 --
1197 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1198 hr_utility.raise_error;
1199 end if;
1200 --
1201 close csr_leg_code;
1202 return l_legislation_code;
1203 --
1204 hr_utility.set_location(' Leaving:'||l_proc, 10);
1205 --
1206 End return_legislation_code;
1207
1208 end ota_tsr_bus;