[Home] [Help]
PACKAGE BODY: APPS.OTA_TSR_BUS
Source
1 Package Body ota_tsr_bus as
2 /* $Header: ottsr01t.pkb 120.3.12020000.3 2012/12/14 09:48:09 atadepal ship $ */
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_467210_SUP_RES_TYPE_ERR');
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_467209_TRNG_CENTER_ERR');
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_467211_RES_CONS_FLAG_ERR');
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_467209_TRNG_CENTER_ERR');
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';
406 l_dummy varchar2(1);
407 --
408 Begin
409 hr_utility.set_location('Entering:'||l_proc, 5);
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 --
559 Procedure del_check_bookings (p_supplied_resource_id in number) is
560 --
561 Cursor c_bookings is
562 select 'X'
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
605 fetch c_usages into l_dummy;
602 hr_utility.set_location('Entering:'||l_proc, 5);
603 --
604 open c_usages;
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;
702 fetch c_location into l_exists;
703 if c_location%notfound then
704 close c_location;
705 fnd_message.set_name('OTA','OTA_13908_TSR_LOC_NOT_EXIST');
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, PER_PERSON_TYPE_USAGES_F ptuf
749 AND NVL(trunc(p_end_date),trunc(sysdate)) <= ptu.effective_end_date --bug no 3058027
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
750 AND trunc(p_start_date) >= ptuf.effective_start_date --Bug 13437083:Modified this clause
751 AND ptu.person_id = per.person_id
752 AND ptuf.person_id = per.person_id --Bug 13437083:Added this clause
753 AND ppt.business_group_id = per.business_group_id
754 -- AND ppt.system_person_type in ('EMP','CWK') Bug# 2956482
755 AND ((ppt.system_person_type = 'EMP' and nvl(per.current_employee_flag,'N')='Y')
756 or (ppt.system_person_type = 'CWK' and nvl(per.current_npw_flag,'N')='Y'))
757 AND ptu.person_type_id = ppt.person_type_id
758 AND ptuf.person_type_id = ppt.person_type_id ; --Bug 13437083:Added this clause
759
760 Cursor c_trainer
761 IS
762 Select null
763 From PER_ALL_PEOPLE_F per, PER_PERSON_TYPE_USAGES_F ptu, PER_PERSON_TYPES ppt, PER_PERSON_TYPE_USAGES_F ptuf
764 Where per.person_id = p_trainer_id
765 --AND trunc(p_start_date) between per.effective_start_date and per.effective_end_date Bug# 2956482
766 AND trunc(p_start_date) >= per.start_date
767 AND NVL(trunc(p_end_date),trunc(sysdate)) <= ptu.effective_end_date --bug no 3058027
768 AND trunc(p_start_date) >= ptuf.effective_start_date --Bug 13437083:Modified this clause
769 AND ptu.person_id = per.person_id
770 AND ptuf.person_id = per.person_id --Bug 13437083:Added this clause
771 AND ppt.business_group_id = per.business_group_id
772 AND per.business_group_id = p_business_group_id
773 -- AND ppt.system_person_type in ('EMP','CWK') Bug# 2956482
774 AND ((ppt.system_person_type = 'EMP' and nvl(per.current_employee_flag,'N')='Y')
775 or (ppt.system_person_type = 'CWK' and nvl(per.current_npw_flag,'N')='Y'))
776 AND ptu.person_type_id = ppt.person_type_id
777 AND ptuf.person_type_id = ppt.person_type_id ; --Bug 13437083:Added this clause
778
779
780
781 --Bug 1991061
782
783 Begin
784 hr_utility.set_location('Entering:'||l_proc, 5);
785
786 if (((p_supplied_resource_id is not null) and
787 nvl(ota_tsr_shd.g_old_rec.trainer_id,hr_api.g_number) <>
788 nvl(p_trainer_id,hr_api.g_number))
789 or (p_supplied_resource_id is null)) or
790 nvl(ota_tsr_shd.g_old_rec.start_date, hr_api.g_date) <>
791 nvl(p_start_date, hr_api.g_date) OR
792 nvl(ota_tsr_shd.g_old_rec.end_date, hr_api.g_date) <>
793 nvl(p_end_date, hr_api.g_date)
794 then
795 --
796 hr_utility.set_location('Entering:'||l_proc, 10);
797 if (p_trainer_id is not null) then
798 if l_global_business_group_id is not null then
799 hr_utility.set_location('Entering:'||l_proc, 15);
800 open c_trainer_global;
801 fetch c_trainer_global into l_exists;
802 if c_trainer_global%notfound then
803 close c_trainer_global;
804 fnd_message.set_name('OTA','OTA_13906_TSR_TRAIN_NOT_EXIST');
805 fnd_message.raise_error;
806 end if;
807 close c_trainer_global;
808 hr_utility.set_location('leaving:'||l_proc, 20);
809 else
810 hr_utility.set_location('Entering:'||l_proc, 25);
811 open c_trainer;
812 fetch c_trainer into l_exists;
813 if c_trainer%notfound then
814 close c_trainer;
815 fnd_message.set_name('OTA','OTA_13906_TSR_TRAIN_NOT_EXIST');
816 fnd_message.raise_error;
817 end if;
818 close c_trainer;
819 hr_utility.set_location('Leaving:'||l_proc, 30);
820 end if;
821 end if;
822 end if;
823 hr_utility.set_location('Leaving:'||l_proc, 35);
824
825 end;
826 --
827 -- ----------------------------------------------------------------------------
828 -- |------------------------------< chk_df >----------------------------------|
829 -- ----------------------------------------------------------------------------
830 --
831 -- Description:
832 -- Validates all the Descriptive Flexfield values.
833 --
834 -- Prerequisites:
835 -- All other columns have been validated. Must be called as the
836 -- last step from insert_validate and update_validate.
837 --
838 -- In Arguments:
839 -- p_rec
840 --
841 -- Post Success:
842 -- If the Descriptive Flexfield structure column and data values are
843 -- all valid this procedure will end normally and processing will
844 -- continue.
845 --
846 -- Post Failure:
847 -- If the Descriptive Flexfield structure column value or any of
848 -- the data values are invalid then an application error is raised as
849 -- a PL/SQL exception.
850 --
851 -- Access Status:
852 -- Internal Row Handler Use Only.
853 --
854 -- ----------------------------------------------------------------------------
855 PROCEDURE chk_df
856 (p_rec IN ota_tsr_shd.g_rec_type
857 ) IS
858 --
859 l_proc varchar2(72) := g_package || 'chk_df';
860 --
861 BEGIN
862 hr_utility.set_location('Entering:'||l_proc,10);
863 --
864 IF ((p_rec.supplied_resource_id IS NOT NULL) AND (
865 NVL(ota_tsr_shd.g_old_rec.tsr_information_category, hr_api.g_varchar2) <>
866 NVL(p_rec.tsr_information_category, hr_api.g_varchar2) OR
867 NVL(ota_tsr_shd.g_old_rec.tsr_information1, hr_api.g_varchar2) <>
871 NVL(ota_tsr_shd.g_old_rec.tsr_information3, hr_api.g_varchar2) <>
868 NVL(p_rec.tsr_information1, hr_api.g_varchar2) OR
869 NVL(ota_tsr_shd.g_old_rec.tsr_information2, hr_api.g_varchar2) <>
870 NVL(p_rec.tsr_information2, hr_api.g_varchar2) OR
872 NVL(p_rec.tsr_information3, hr_api.g_varchar2) OR
873 NVL(ota_tsr_shd.g_old_rec.tsr_information4, hr_api.g_varchar2) <>
874 NVL(p_rec.tsr_information4, hr_api.g_varchar2) OR
875 NVL(ota_tsr_shd.g_old_rec.tsr_information5, hr_api.g_varchar2) <>
876 NVL(p_rec.tsr_information5, hr_api.g_varchar2) OR
877 NVL(ota_tsr_shd.g_old_rec.tsr_information6, hr_api.g_varchar2) <>
878 NVL(p_rec.tsr_information6, hr_api.g_varchar2) OR
879 NVL(ota_tsr_shd.g_old_rec.tsr_information7, hr_api.g_varchar2) <>
880 NVL(p_rec.tsr_information7, hr_api.g_varchar2) OR
881 NVL(ota_tsr_shd.g_old_rec.tsr_information8, hr_api.g_varchar2) <>
882 NVL(p_rec.tsr_information8, hr_api.g_varchar2) OR
883 NVL(ota_tsr_shd.g_old_rec.tsr_information9, hr_api.g_varchar2) <>
884 NVL(p_rec.tsr_information9, hr_api.g_varchar2) OR
885 NVL(ota_tsr_shd.g_old_rec.tsr_information10, hr_api.g_varchar2) <>
886 NVL(p_rec.tsr_information10, hr_api.g_varchar2) OR
887 NVL(ota_tsr_shd.g_old_rec.tsr_information11, hr_api.g_varchar2) <>
888 NVL(p_rec.tsr_information11, hr_api.g_varchar2) OR
889 NVL(ota_tsr_shd.g_old_rec.tsr_information12, hr_api.g_varchar2) <>
890 NVL(p_rec.tsr_information12, hr_api.g_varchar2) OR
891 NVL(ota_tsr_shd.g_old_rec.tsr_information13, hr_api.g_varchar2) <>
892 NVL(p_rec.tsr_information13, hr_api.g_varchar2) OR
893 NVL(ota_tsr_shd.g_old_rec.tsr_information14, hr_api.g_varchar2) <>
894 NVL(p_rec.tsr_information14, hr_api.g_varchar2) OR
895 NVL(ota_tsr_shd.g_old_rec.tsr_information15, hr_api.g_varchar2) <>
896 NVL(p_rec.tsr_information15, hr_api.g_varchar2) OR
897 NVL(ota_tsr_shd.g_old_rec.tsr_information16, hr_api.g_varchar2) <>
898 NVL(p_rec.tsr_information16, hr_api.g_varchar2) OR
899 NVL(ota_tsr_shd.g_old_rec.tsr_information17, hr_api.g_varchar2) <>
900 NVL(p_rec.tsr_information17, hr_api.g_varchar2) OR
901 NVL(ota_tsr_shd.g_old_rec.tsr_information18, hr_api.g_varchar2) <>
902 NVL(p_rec.tsr_information18, hr_api.g_varchar2) OR
903 NVL(ota_tsr_shd.g_old_rec.tsr_information19, hr_api.g_varchar2) <>
904 NVL(p_rec.tsr_information19, hr_api.g_varchar2) OR
905 NVL(ota_tsr_shd.g_old_rec.tsr_information20, hr_api.g_varchar2) <>
906 NVL(p_rec.tsr_information20, hr_api.g_varchar2) ) )
907 OR (p_rec.supplied_resource_id IS NULL) THEN
908 --
909 -- Only execute the validation if absolutely necessary:
910 -- a) During update, the structure column value or any
911 -- of the tsr_information values have actually changed.
912 -- b) During insert.
913 --
914
915 hr_dflex_utility.ins_or_upd_descflex_attribs
916 (p_appl_short_name => 'OTA'
917 ,p_descflex_name => 'OTA_SUPPLIABLE_RESOURCES'
918 ,p_attribute_category => p_rec.tsr_information_category
919 ,p_attribute1_name => 'TSR_INFORMATION1'
920 ,p_attribute1_value => p_rec.tsr_information1
921 ,p_attribute2_name => 'TSR_INFORMATION2'
922 ,p_attribute2_value => p_rec.tsr_information2
923 ,p_attribute3_name => 'TSR_INFORMATION3'
924 ,p_attribute3_value => p_rec.tsr_information3
925 ,p_attribute4_name => 'TSR_INFORMATION4'
926 ,p_attribute4_value => p_rec.tsr_information4
927 ,p_attribute5_name => 'TSR_INFORMATION5'
928 ,p_attribute5_value => p_rec.tsr_information5
929 ,p_attribute6_name => 'TSR_INFORMATION6'
930 ,p_attribute6_value => p_rec.tsr_information6
931 ,p_attribute7_name => 'TSR_INFORMATION7'
932 ,p_attribute7_value => p_rec.tsr_information7
933 ,p_attribute8_name => 'TSR_INFORMATION8'
934 ,p_attribute8_value => p_rec.tsr_information8
935 ,p_attribute9_name => 'TSR_INFORMATION9'
936 ,p_attribute9_value => p_rec.tsr_information9
937 ,p_attribute10_name => 'TSR_INFORMATION10'
938 ,p_attribute10_value => p_rec.tsr_information10
939 ,p_attribute11_name => 'TSR_INFORMATION11'
940 ,p_attribute11_value => p_rec.tsr_information11
941 ,p_attribute12_name => 'TSR_INFORMATION12'
942 ,p_attribute12_value => p_rec.tsr_information12
943 ,p_attribute13_name => 'TSR_INFORMATION13'
944 ,p_attribute13_value => p_rec.tsr_information13
945 ,p_attribute14_name => 'TSR_INFORMATION14'
946 ,p_attribute14_value => p_rec.tsr_information14
947 ,p_attribute15_name => 'TSR_INFORMATION15'
948 ,p_attribute15_value => p_rec.tsr_information15
949 ,p_attribute16_name => 'TSR_INFORMATION16'
950 ,p_attribute16_value => p_rec.tsr_information16
951 ,p_attribute17_name => 'TSR_INFORMATION17'
952 ,p_attribute17_value => p_rec.tsr_information17
953 ,p_attribute18_name => 'TSR_INFORMATION18'
954 ,p_attribute18_value => p_rec.tsr_information18
955 ,p_attribute19_name => 'TSR_INFORMATION19'
956 ,p_attribute19_value => p_rec.tsr_information19
957 ,p_attribute20_name => 'TSR_INFORMATION20'
958 ,p_attribute20_value => p_rec.tsr_information20
959 );
960 END IF;
961
962 --
963 hr_utility.set_location(' Leaving:'||l_proc,20);
964 END chk_df;
965
966
967 --
968 --
969 --*****************************************************************************
970 -- GENERAL VALIDATION PROCEDURES
971 --*****************************************************************************
972 procedure validation1
973 (
974 p_rec in ota_tsr_shd.g_rec_type
975 )
976 is
977 ---------------------
978 begin
979 --
980 check_tsr_key (
981 p_business_group_id => p_rec.business_group_id,
982 p_resource_type => p_rec.resource_type,
983 p_vendor_id => p_rec.vendor_id,
984 p_supplied_resource_id => p_rec.supplied_resource_id,
985 p_name => p_rec.name,
986 p_training_center_id => p_rec.training_center_id
987 );
988 --
989 check_tsr_dates (
990 p_startdate => p_rec.start_date,
991 p_enddate => p_rec.end_date);
992
993 chk_Training_center
994 (p_supplied_resource_id => p_rec.supplied_resource_id,
995 p_training_center_id => p_rec.training_center_id);
996
997
998 Chk_location
999 (p_supplied_resource_id => p_rec.supplied_resource_id,
1000 p_location_id => p_rec.location_id,
1001 p_training_center_id => p_rec.training_center_id);
1002
1003 Chk_trainer
1004 (p_supplied_resource_id => p_rec.supplied_resource_id,
1005 p_trainer_id => p_rec.trainer_id,
1006 p_business_group_id => p_rec.business_group_id,
1007 p_start_date => p_rec.start_date,
1008 p_end_date => p_rec.end_date);
1009 --
1010 end validation1;
1011 --=============================================================================
1012 --=============================================================================
1013 procedure validation2
1014 (
1015 p_rec in ota_tsr_shd.g_rec_type
1016 )
1017 is
1018 ---------------------
1019 begin
1020 --
1021 check_cost_and_currency (
1022 p_cost => p_rec.cost,
1023 p_currency_code => p_rec.currency_code );
1024 --
1025 end validation2;
1026 --=============================================================================
1027 --=============================================================================
1028 procedure insert_validate2
1029 (
1030 p_rec in ota_tsr_shd.g_rec_type
1031 )
1032 is
1033 ---------------------
1034 begin
1035 --
1036 validation1(p_rec);
1037 --
1038 validation2(p_rec);
1039 --
1040 end insert_validate2;
1041 --=============================================================================
1042 --=============================================================================
1043 procedure update_validate2
1044 (
1045 p_rec in ota_tsr_shd.g_rec_type
1046 )
1047 is
1048 ---------------------
1049 begin
1050 --
1051 check_type_update(
1052 p_tsr_id => p_rec.supplied_resource_id,
1053 p_ovn => p_rec.object_version_number,
1054 p_resource_type => p_rec.resource_type);
1055 --
1056 validation1(p_rec);
1057 --
1058 check_tsr_new_dates (
1059 p_tsr_id => p_rec.supplied_resource_id,
1060 p_startdate => p_rec.start_date,
1061 p_enddate => p_rec.end_date);
1062 --
1063 validation2(p_rec);
1064 --
1065 end update_validate2;
1066 --=============================================================================
1067 --=============================================================================
1068 procedure delete_validate2
1069 (
1070 p_rec in ota_tsr_shd.g_rec_type
1071 )
1072 is
1073 ---------------------
1074 begin
1075 --
1076 del_check_bookings(p_rec.supplied_resource_id);
1077 --
1078 del_check_usages(p_rec.supplied_resource_id);
1079 --
1080 end delete_validate2;
1081 --*****************************************************************************
1082 -- END OF MANUALLY WRITTEN SECTION
1083 --*****************************************************************************
1084 -- ----------------------------------------------------------------------------
1085 -- |---------------------------< insert_validate >----------------------------|
1086 -- ----------------------------------------------------------------------------
1087 Procedure insert_validate(p_rec in ota_tsr_shd.g_rec_type) is
1088 --
1089 l_proc varchar2(72) := g_package||'insert_validate';
1090 --
1091 Begin
1092 hr_utility.set_location('Entering:'||l_proc, 5);
1093 --
1094 -- Call all supporting business operations
1095 insert_validate2(p_rec);
1096 --
1097 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1098 --
1099 ota_tsr_bus.chk_df(p_rec);
1100 hr_utility.set_location(' Leaving:'||l_proc, 10);
1101 End insert_validate;
1102 --
1103 -- ----------------------------------------------------------------------------
1104 -- |---------------------------< update_validate >----------------------------|
1105 -- ----------------------------------------------------------------------------
1106 Procedure update_validate(p_rec in ota_tsr_shd.g_rec_type) is
1107 --
1108 l_proc varchar2(72) := g_package||'update_validate';
1109 --
1110 Begin
1111 hr_utility.set_location('Entering:'||l_proc, 5);
1112 --
1113 -- Call all supporting business operations
1114 update_validate2(p_rec);
1115 --
1116 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1117 --
1118 hr_utility.set_location(' Leaving:'||l_proc, 10);
1119 End update_validate;
1120 --
1121 -- ----------------------------------------------------------------------------
1122 -- |---------------------------< delete_validate >----------------------------|
1123 -- ----------------------------------------------------------------------------
1124 Procedure delete_validate(p_rec in ota_tsr_shd.g_rec_type) is
1125 --
1126 l_proc varchar2(72) := g_package||'delete_validate';
1127 --
1128 Begin
1129 hr_utility.set_location('Entering:'||l_proc, 5);
1130 --
1131 -- Call all supporting business operations
1132 delete_validate2(p_rec);
1133 --
1134 hr_utility.set_location(' Leaving:'||l_proc, 10);
1135 End delete_validate;
1136 --
1137
1138 -- ----------------------------------------------------------------------------
1139 -- |-----------------------< return_legislation_code >-------------------------|
1140 -- ----------------------------------------------------------------------------
1141 --
1142 -- {Start Of Comments}
1143 --
1144 -- Description:
1145 -- This function will be used by the user hooks. This will be used
1146 -- of by the user hooks of ota_suppliable_resources and
1147 -- ota_resource_booking row handler user hook business process.
1148 --
1149 -- Pre Conditions:
1150 -- This function will be called by the user hook packages.
1151 --
1152 -- In Arguments:
1153 -- SUPPLIED_RESOURCE_ID
1154 --
1155 -- Post Success:
1156 -- Processing continues.
1157 --
1158 -- Post Failure:
1159 -- Errors out
1160 --
1161 -- Developer Implementation Notes:
1162 --
1163 -- Access Status:
1164 -- Internal Development Use Only.
1165 --
1166 -- {End Of Comments}
1167 --------------------------------------------------------------------------------
1168 --
1169 Function return_legislation_code
1170 ( p_supplied_resource_id in number
1171 ) return varchar2 is
1172 --
1173 -- Declare cursor
1174 --
1175 cursor csr_leg_code is
1176 select legislation_code
1177 from per_business_groups_perf pbg,
1178 ota_suppliable_resources tsr
1179 where pbg.business_group_id = tsr.business_group_id
1180 and tsr.supplied_resource_id = p_supplied_resource_id;
1181
1182
1183 l_proc varchar2(72) := g_package||'return_legislation_code';
1184 l_legislation_code varchar2(150);
1185 --
1186 Begin
1187 hr_utility.set_location('Entering:'||l_proc, 5);
1188 --
1189 -- Ensure that all the mandatory parameters are not null
1190 --
1191 hr_api.mandatory_arg_error (p_api_name => l_proc,
1192 p_argument => 'supplied_resource_id',
1193 p_argument_value => p_supplied_resource_id);
1194 open csr_leg_code;
1195 fetch csr_leg_code into l_legislation_code;
1196 if csr_leg_code%notfound then
1197 close csr_leg_code;
1198 --
1199 -- The primary key is invalid therefore we must error out
1200 --
1201 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1202 hr_utility.raise_error;
1203 end if;
1204 --
1205 close csr_leg_code;
1206 return l_legislation_code;
1207 --
1208 hr_utility.set_location(' Leaving:'||l_proc, 10);
1209 --
1210 End return_legislation_code;
1211
1212 end ota_tsr_bus;