[Home] [Help]
PACKAGE BODY: APPS.OTA_TPL_BUS
Source
1 Package Body ota_tpl_bus as
2 /* $Header: ottpl01t.pkb 115.2 99/07/16 00:55:57 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tpl_bus.'; -- Global package name
9 --
10 -- ici insert tpl.cat
11 --*******************************************************************************
12 -- TPL: ADDITIONAL API
13 --
14 -- Version Date Author Reason
15 -- 10.15 27Apr95 lparient.FR Reviewed error messages
16 --
17 --*******************************************************************************
18 --
19 -- ***************************
20 -- ADDITIONAL GLOBAL VARIABLES
21 -- ***************************
22 --
23 g_dummy integer(1);
24 g_end_of_time date;
25 g_update boolean;
26 --
27 --
28 --
29 -- **************************************************
30 -- MANUALLY WRITTEN SECTION AND GENERATED API SECTION
31 -- **************************************************
32 --
33 -- procedure insert_validate2 must be called from generated procedure insert_validate.
34 -- procedure update_validate2 must be called from generated procedure update_validate.
35 -- procedure delete_validate2 must be called from generated procedure delete_validate.
36 --
37 -- Utilities (public procedures):
38 -- procedure copy_price_list
39 -- procedure change_price_list_dates
40 -- procedure check_single_name
41 --
42 --*******************************************************************************
43 --
44 -- ============================================================================
45 -- GENERAL PROCEDURES
46 --==============================================================================
47 Procedure constraint_error2
48 (p_constraint_name varchar2) Is
49 --
50 l_proc varchar2(72) := g_package||'constraint_error2';
51 --
52 Begin
53 --
54 hr_utility.set_location('Entering:'||l_proc, 5);
55 --
56 if p_constraint_name = 'flag_must_be_populated' then
57 fnd_message.set_name('OTA','OTA_13567_TPL_DEFAULT_FLAG');
58 elsif p_constraint_name = 'tpl_name_and_date_is_mandatory' then
59 fnd_message.set_name('OTA','OTA_13566_TPL_COPY_PRICE_LIST');
60 elsif p_constraint_name = 'tpl_name_is_mandatory' then
61 fnd_message.set_name('OTA','OTA_13568_TPL_PRICE_LIST_NAME');
62 elsif p_constraint_name = 'single_unit_price_should_be_populated' then
63 fnd_message.set_name('OTA','OTA_13569_TPL_SINGLE_UNIT');
64 elsif p_constraint_name = 'training_unit_type_should_be_populated' then
65 fnd_message.set_name('OTA','OTA_13569_TPL_SINGLE_UNIT');
66 --
67 elsif p_constraint_name = 'price_list_doesnt_exist' then
68 fnd_message.set_name('OTA','OTA_13570_TPL_PRICE_LIST_NAME');
69 elsif p_constraint_name = 'price_list_type_must_be_populated' then
70 fnd_message.set_name('OTA','OTA_13571_TPL_LIST_TYPE');
71 elsif p_constraint_name = 'single_unit_price_should_be_null' then
72 fnd_message.set_name('OTA','OTA_13572_TPL_SINGLE_UNIT_NULL');
73 elsif p_constraint_name = 'training_unit_type_should_be_null' then
74 fnd_message.set_name('OTA','OTA_13572_TPL_SINGLE_UNIT_NULL');
75 elsif p_constraint_name = 'undeletable_child_tbd_exists' then
76 fnd_message.set_name('OTA','OTA_13228_TPL_TBD_EXISTS');
77 --
78 elsif p_constraint_name = 'tpl_name_already_exists' then
79 fnd_message.set_name('OTA','OTA_13258_TPL_WRONG_NAME');
80 elsif (p_constraint_name = 'default_price_list_already_exists') Then
81 fnd_message.set_name('OTA','OTA_13221_TPL_WRONG_FLAG');
82 elsif (p_constraint_name = 'tpl_dates_ple') Then
83 fnd_message.set_name('OTA','OTA_13233_TPL_DATES_PLE');
84 elsif (p_constraint_name = 'tpl_dates_tbd') Then
85 fnd_message.set_name('OTA','OTA_13234_TPL_DATES_TBD');
86 elsif p_constraint_name = 'startdate_must_be_populated' then
87 fnd_message.set_name('OTA','OTA_13230_GEN_MAND_START_DATE');
88 elsif p_constraint_name = 'enddate_must_be_greater_than_startdate' then
89 fnd_message.set_name('OTA','OTA_13312_GEN_DATE_ORDER');
90 elsif p_constraint_name = 'tpl_entries_exist' then
91 fnd_message.set_name('OTA','OTA_13639_TPL_ENTRIES_EXIST');
92 --
93 else
94 fnd_message.set_name('OTA','OTA_13259_GEN_UNKN_CONSTRAINT');
95 fnd_message.set_token('CONSTRAINT',p_constraint_name);
96 End If;
97 --
98 fnd_message.raise_error;
99 --
100 hr_utility.set_location(' Leaving:'||l_proc, 10);
101
102 End constraint_error2;
103 --==============================================================================
104 --=============================================================================
105 --
106 -- PUBLIC
107 --
108 procedure check_single_name
109 (
110 p_price_list_id in number,
111 p_name in varchar2,
112 p_business_group_id in number
113 )
114 is
115 --
116 cursor csr_tpl is
117 select 1
118 from ota_price_lists
119 where name = p_name
120 and business_group_id = p_business_group_id
121 and (p_price_list_id is null or price_list_id <> p_price_list_id);
122 --
123 l_tpl_exists boolean;
124 l_dummy number;
125 --
126 procedure chkp is
127 begin
128 hr_api.mandatory_arg_error(g_package,'business_group_id',p_business_group_id);
129 end chkp;
130 -------------------
131 begin
132 --
133 chkp;
134 --
135 if p_name is null then
136 constraint_error2('tpl_name_is_mandatory');
137 end if;
138 --
139 open csr_tpl;
140 fetch csr_tpl into l_dummy;
141 l_tpl_exists := csr_tpl%found;
142 close csr_tpl;
143 --
144 if l_tpl_exists then
145 constraint_error2('tpl_name_already_exists');
146 end if;
147 --
148 end check_single_name;
149 --=============================================================================
150 --=============================================================================
151 procedure check_flag
152 (
153 p_flag varchar2
154 )
155 is
156 ---------------------
157 begin
158 --
159 if p_flag is null
160 or (p_flag <> 'Y' and p_flag <> 'N') then
161 constraint_error2('flag_must_be_populated');
162 end if;
163 --
164 end check_flag;
165 --=============================================================================
166 --=============================================================================
167 procedure check_type
168 (
169 p_type varchar2,
170 p_price number,
171 p_tu_type varchar2
172 )
173 is
174 --
175 begin
176 --
177 if p_type is null
178 or p_type not in ('M','T') then
179 constraint_error2('price_list_type_must_be_populated');
180 elsif p_type = 'T' then
181 if p_price is null then
182 constraint_error2('single_unit_price_should_be_populated');
183 end if;
184 if p_tu_type is null then
185 constraint_error2('training_unit_type_should_be_populated');
186 end if;
187 ota_general.check_domain_value ('TRAINING_UNIT',p_tu_type);
188 elsif p_type = 'M' then
189 if p_price is not null then
190 constraint_error2('single_unit_price_should_be_null');
191 end if;
192 if p_tu_type is not null then
193 constraint_error2('training_unit_type_should_be_null');
194 end if;
195 end if;
196 --
197 end check_type;
198 --=============================================================================
199 --=============================================================================
200 procedure check_currency
201 (
202 p_currency varchar2
203 )
204 is
205 --
206 begin
207 --
208 if p_currency is null then
209 constraint_error2('currency_must_be_populated');
210 else
211 ota_general.check_currency_is_valid(p_currency_code => p_currency);
212 end if;
213 --
214 end check_currency;
215 --==============================================================================
216 --==============================================================================
217 function dates_are_in_order
218 (
219 p_startdate date,
220 p_enddate date
221 )
222 return boolean is
223 -------------------
224 procedure chkp is
225 begin
226 hr_api.mandatory_arg_error(g_package,'p_startdate',p_startdate);
227 end chkp;
228 -------------------
229 begin
230 --
231 chkp;
232 --
233 if p_enddate is null then
234 return TRUE;
235 elsif p_enddate >= p_startdate then
236 return TRUE;
237 else
238 return FALSE;
239 end if;
240 --
241 end dates_are_in_order;
242 --==============================================================================
243 --=============================================================================
244 procedure check_dates_order
245 (
246 p_startdate date,
247 p_enddate date
248 )
249 is
250 -------------------
251 begin
252 --
253 if p_startdate is null then
254 constraint_error2('startdate_must_be_populated');
255 end if;
256 --
257 if not dates_are_in_order(p_startdate,p_enddate) then
258 constraint_error2('enddate_must_be_greater_than_startdate');
259 end if;
260 --
261 end check_dates_order;
262 --==============================================================================
263 --==============================================================================
264 procedure check_table_constraints
265 (
266 p_rec in ota_tpl_shd.g_rec_type
267 )
268 is
269 -------------
270 begin
271 --
272 check_single_name (
273 p_price_list_id => p_rec.price_list_id,
274 p_name => p_rec.name,
275 p_business_group_id => p_rec.business_group_id);
276 --
277 check_flag (p_flag => p_rec.default_flag);
278 --
279 check_type (
280 p_type => p_rec.price_list_type,
281 p_price => p_rec.single_unit_price,
282 p_tu_type => p_rec.training_unit_type);
283 --
284 check_currency (p_currency => p_rec.currency_code);
285 --
286 check_dates_order (
287 p_startdate => p_rec.start_date,
288 p_enddate => p_rec.end_date);
289 --
290 end check_table_constraints;
291
292 -- ***********************************************************************-
293 -- PRICE LIST ENTRIES CHILDREN
294 --*************************************************************************-
295 function tpl_has_child_ple
296 ---------------------------
297 (
298 p_tpl_id number
299 )
300 return boolean is
301 ----------------------------
302 cursor csr_children_ple is
303 select 1
304 from ota_price_list_entries
305 where price_list_id = p_tpl_id;
306
307 l_ple_exists boolean;
308
309 procedure chkp is
310 begin
311 hr_api.mandatory_arg_error(g_package,'p_tpl_id',p_tpl_id);
312 end chkp;
313 ------------------------
314 begin
315
316 chkp;
317
318 open csr_children_ple;
319 fetch csr_children_ple into g_dummy;
320 l_ple_exists := csr_children_ple%found;
321 close csr_children_ple;
322
323 return l_ple_exists;
324
325 end tpl_has_child_ple;
326
327 /**************************************************************************/
328 procedure del_check_no_child_ple
329 (
330 p_tpl_id number
331 )
332 is
333 -------------------
334 begin
335 --
336 if tpl_has_child_ple (p_tpl_id) then
337 constraint_error2('tpl_entries_exist');
338 end if;
339 --
340 end del_check_no_child_ple;
341
342 --***********************************************************************-
343 -- CHILDREN BOOKING DEALS
344 --*************************************************************************-
345 function tpl_has_child_tbd
346 ---------------------------
347 (
348 p_tpl_id number
349 )
350 return boolean is
351 ----------------------------
352 cursor csr_child is
353 select 1
354 from ota_booking_deals
355 where price_list_id = p_tpl_id;
356
357 l_child_exists boolean;
358
359 procedure chkp is
360 begin
361 hr_api.mandatory_arg_error(g_package,'p_tpl_id',p_tpl_id);
362 end chkp;
363 ------------------------
364 begin
365
366 chkp;
367
368 open csr_child;
369 fetch csr_child into g_dummy;
370 l_child_exists := csr_child%found;
371 close csr_child;
372
373 return l_child_exists;
374
375 end tpl_has_child_tbd;
376 --========================================================================
377 --=======================================================================
378 procedure del_check_no_child_tbd
379 (
380 p_tpl_id number
381 )
382 is
383 -------------------
384 begin
385 --
386 if tpl_has_child_tbd (p_tpl_id) then
387 constraint_error2('undeletable_child_tbd_exists');
388 end if;
389 --
390 end del_check_no_child_tbd;
391 -- ***************************************************************************************
392 -- DEFAULT PRICE LIST ENTRY FOR A BUSINESS GROUP (DEFAULT_FLAG)
393 --***************************************************************************************
394 procedure check_single_default_tpl
395 (
396 p_tpl_id number
397 ,p_default_flag varchar2
398 ,p_business_group_id number
399 ,p_currency_code varchar2
400 ,p_start_date date
401 ,p_end_date date
402 )
403 is
404 --
405 --*** check that there is a single default price list
406 --*** business rule: there may only be one price list with the default flag set to 'Y'
407 --*** If p_tpl_id is null, it's an INSERT, otherwise it's an update.
408 --
409 cursor csr_default_price_list is
410 select 1
411 from ota_price_lists
412 where business_group_id = p_business_group_id
413 and default_flag = 'Y'
414 and currency_code = p_currency_code
415 and p_start_date <= nvl(end_date,p_start_date)
416 and nvl(p_end_date,start_date) >= start_date
417 and (p_tpl_id is null or (p_tpl_id is not null
418 and price_list_id <> p_tpl_id));
419 --
420 l_tpl_exists boolean;
421 --
422 procedure chkp is
423 begin
424 hr_api.mandatory_arg_error(g_package,'p_business_group_id',p_business_group_id);
425 hr_api.mandatory_arg_error(g_package,'p_default_flag',p_default_flag);
426 end chkp;
427 --
428 begin
429 --
430 if p_default_flag = 'N' then
431 return;
432 end if;
433 --
434 chkp;
435 --
436 open csr_default_price_list;
437 fetch csr_default_price_list into g_dummy;
438 l_tpl_exists := csr_default_price_list%found;
439 close csr_default_price_list;
440 if l_tpl_exists then
441 constraint_error2('default_price_list_already_exists');
442 end if;
443 --
444 end check_single_default_tpl;
445 -- *************************************************************************************
446 -- TPL: DATES VALIDATION
447 --*************************************************************************************
448 function tpl_start_precedes_ple_starts
449 -----------------------------
450 (
451 p_tpl_id number,
452 p_start_date date
453 )
454 return boolean is
455
456 --*** Returns true if Price list'new start date <= Price list entries'min start date
457 --*** This function must be called before updating ota_price_lists
458 --*** Start_dates are mandatory in price lists and price list entries
459 ----------------
460 cursor csr_ple_min_startdate is
461 select min(start_date)
462 from ota_price_list_entries
463 where price_list_id = p_tpl_id;
464
465 mindate date;
466
467 procedure chkp is
468 begin
469 hr_api.mandatory_arg_error(g_package,'p_tpl_id',p_tpl_id);
470 hr_api.mandatory_arg_error(g_package,'p_start_date',p_start_date);
471 end chkp;
472 -----------------
473 begin
474 --
475 chkp;
476 --
477 open csr_ple_min_startdate;
478 fetch csr_ple_min_startdate into mindate;
479 close csr_ple_min_startdate;
480 --
481 if mindate is null then
482 return TRUE;
483 elsif p_start_date <= mindate then
484 return TRUE;
485 elsif p_start_date > mindate then
486 if g_update then
487 return TRUE;
488 else
489 return FALSE;
490 end if;
491 else
492 return FALSE;
493 end if;
494 end tpl_start_precedes_ple_starts;
495 --=======================================================================================
496 --=======================================================================================
497 function tpl_start_precedes_tbd_starts
498 (
499 p_tpl_id number,
500 p_start_date date
501 )
502 return boolean is
503
504 --*** Returns true if Price list'new start date <= booking deals'min start date
505 --*** This function must be called before updating ota_price_lists
506 --*** Start_dates are mandatory in price lists and booking deals
507 ----------------
508 cursor csr_tbd_min_startdate is
509 select min(start_date)
510 from ota_booking_deals
511 where price_list_id = p_tpl_id;
512
513 mindate date;
514
515 procedure chkp is
516 begin
517 hr_api.mandatory_arg_error(g_package,'p_tpl_id',p_tpl_id);
518 hr_api.mandatory_arg_error(g_package,'p_start_date',p_start_date);
519 end chkp;
520 -----------------
521 begin
522 --
523 chkp;
524 --
525 open csr_tbd_min_startdate;
526 fetch csr_tbd_min_startdate into mindate;
527 close csr_tbd_min_startdate;
528 --
529 if mindate is null then
530 return TRUE;
531 elsif p_start_date <= mindate then
532 return TRUE;
533 else
534 return FALSE;
535 end if;
536 --
537 end tpl_start_precedes_tbd_starts;
538 --=======================================================================================
539 --=======================================================================================
540 function tpl_end_succeeds_ple_ends
541 -----------------------------
542 (
543 p_tpl_id number,
544 p_end_date date
545 )
546 return boolean is
547 --
548 --*** Returns true if Price list's new end date >= Price list entries' max end date
549 --*** This function must be called before updating ota_price_lists
550 --*** end dates may be null
551 --
552 cursor csr_ple_max_enddate is
553 select max(nvl(end_date,g_end_of_time))
554 from ota_price_list_entries
555 where price_list_id = p_tpl_id;
556 --
557 maxdate date;
558 --
559 procedure chkp is
560 begin
561 hr_api.mandatory_arg_error(g_package,'p_tpl_id',p_tpl_id);
562 end chkp;
563 --
564 begin
565 --
566 chkp;
567 --
568 if p_end_date is null
569 then
570 return TRUE;
571 end if;
572 --
573 open csr_ple_max_enddate;
574 fetch csr_ple_max_enddate into maxdate;
575 close csr_ple_max_enddate;
576 --
577 if maxdate is null then
578 return TRUE;
579 elsif p_end_date >= maxdate then
580 return TRUE;
581 elsif p_end_date < maxdate then
582 if g_update then
583 return TRUE;
584 else
585 return FALSE;
586 end if;
587 else
588 return FALSE;
589 end if;
590 --
591 end tpl_end_succeeds_ple_ends;
592 --=======================================================================================
593 --=======================================================================================
594 function tpl_end_succeeds_tbd_ends
595 -----------------------------
596 (
597 p_tpl_id number,
598 p_end_date date
599 )
600 return boolean is
601 --
602 --*** Returns true if Price list's new end date >= booking deals' max end date
603 --*** This function must be called before updating ota_price_lists
604 --*** end dates may be null
605 -------------------
606 cursor csr_tbd_max_enddate is
607 select max(nvl(end_date,g_end_of_time))
608 from ota_booking_deals
609 where price_list_id = p_tpl_id;
610 --
611 maxdate date;
612 --
613 procedure chkp is
614 begin
615 hr_api.mandatory_arg_error(g_package,'p_tpl_id',p_tpl_id);
616 end chkp;
617 -------------------
618 begin
619 --
620 chkp;
621 --
622 if p_end_date is null
623 then
624 return TRUE;
625 end if;
626 --
627 open csr_tbd_max_enddate;
628 fetch csr_tbd_max_enddate into maxdate;
629 close csr_tbd_max_enddate;
630 --
631 if maxdate is null then
632 return TRUE;
633 elsif p_end_date >= maxdate then
634 return TRUE;
635 else
636 return FALSE;
637 end if;
638 --
639 end tpl_end_succeeds_tbd_ends;
640 --=======================================================================================
641 --=======================================================================================
642 procedure check_tpl_new_start_date
643 (
644 p_tpl_id number,
645 p_start_date date
646 )
647 is
648 --------------
649 begin
650 --
651 if not tpl_start_precedes_ple_starts(p_tpl_id,p_start_date)
652 then
653 constraint_error2('tpl_dates_ple');
654 end if;
655 --
656 if not tpl_start_precedes_tbd_starts(p_tpl_id,p_start_date)
657 then
658 constraint_error2('tpl_dates_tbd');
659 end if;
660 --
661 end check_tpl_new_start_date;
662 --=======================================================================================
663 --=======================================================================================
664 procedure check_tpl_new_end_date
665 (
666 p_tpl_id number,
667 p_end_date date
668 )
669 is
670 --------------
671 begin
672 --
673 g_end_of_time := hr_general.end_of_time;
674
675 if not tpl_end_succeeds_ple_ends(p_tpl_id,p_end_date)
676 then
677 constraint_error2('tpl_dates_ple');
678 end if;
679 --
680 if not tpl_end_succeeds_tbd_ends(p_tpl_id,p_end_date)
681 then
682 constraint_error2('tpl_dates_tbd');
683 end if;
684 --
685 end check_tpl_new_end_date;
686 --=======================================================================================
687 --=======================================================================================
688 procedure check_tpl_new_dates
689 (
690 p_tpl_id number,
691 p_start_date date,
692 p_end_date date
693 )
694 is
695 --*** called when updating price list 's start_date and end_date
696 ----------------
697 begin
698 --
699 check_tpl_new_start_date(p_tpl_id,p_start_date);
700
701 check_tpl_new_end_date(p_tpl_id,p_end_date);
702 --
703 end check_tpl_new_dates;
704 -- **************************************************************************************
705 -- GENERAL VALIDATION PROCEDURES
706 --***************************************************************************************
707 procedure insert_and_update_validate
708 (
709 p_rec in ota_tpl_shd.g_rec_type
710 )
711 is
712 ---------------------
713 begin
714 --
715 check_table_constraints(p_rec);
716 --
717 check_single_default_tpl (
718 p_tpl_id => p_rec.price_list_id
719 ,p_default_flag => p_rec.default_flag
720 ,p_business_group_id => p_rec.business_group_id
721 ,p_currency_code => p_rec.currency_code
722 ,p_start_date => p_rec.start_date
723 ,p_end_date => p_rec.end_date
724 );
725 --
726 end insert_and_update_validate;
727 --=======================================================================================
728 --=======================================================================================
729 procedure insert_validate2
730 (
731 p_rec in ota_tpl_shd.g_rec_type
732 )
733 is
734 ---------------------
735 begin
736 --
737 insert_and_update_validate(p_rec);
738 --
739 end insert_validate2;
740 --=======================================================================================
741 --=======================================================================================
742 procedure update_validate2
743 (
744 p_rec in ota_tpl_shd.g_rec_type
745 )
746 is
747 ---------------------
748 begin
749 --
750 insert_and_update_validate(p_rec);
751 --
752 check_tpl_new_dates (
753 p_tpl_id => p_rec.price_list_id,
754 p_start_date => p_rec.start_date,
755 p_end_date => p_rec.end_date);
756 --
757 end update_validate2;
758 --=======================================================================================
759 --=======================================================================================
760 procedure delete_validate2
761 (
762 p_rec in ota_tpl_shd.g_rec_type
763 )
764 is
765 ---------------------
766 begin
767 --
768 --Check no child booking deals exist for price list.
769 --
770 del_check_no_child_tbd (p_tpl_id => p_rec.price_list_id);
771
772 --
773 --Check no child entries exist for price list.
774 --
775 del_check_no_child_ple (p_tpl_id => p_rec.price_list_id);
776 --
777 end delete_validate2;
778 --***************************************************************************************
779 -- END OF MANUALLY WRITTEN SECTION
780 --***************************************************************************************
781 -- *********************************************************************
782 --***********************************************************************
783 procedure modify_price_list
784 (
785 p_proc_use number,
786 p_old_tpl_id number,
787 p_new_tpl_name varchar2,
788 p_new_startdate date,
789 p_new_enddate date,
790 p_price_increase number,
791 p_rounding_direction varchar2,
792 p_rounding_factor number,
793 p_change_entries boolean
794 )
795 is
796 -----------------
797 --*** p_proc_use = 1 => copy_price_list utility:
798 --*** Create a new price list based on an existing price list
799 --*** and the associated price list entries,
800 --*** with new dates and a possible increase in price.
801 --*** p_price_increase is a percent.
802 --
803 --*** p_proc_use = 2 => change_price_list_dates utility;
804 --*** Update the end_date of a price_list
805 --*** Close_down_price_list:
806 --*** If the end_date is < old end_date then price list entries
807 --*** will be modified: if their start_date is > new end date,
808 --*** price list entries will be deleted. If their end_date
809 --*** is > new end_date, their end_date will be updated.
810 --*** Widen_dates:
811 --*** If end date > old end date, Entries ending at the same old
812 --*** end date will be updated after the price list update.
813 --*** If start date < old start date, Entries starting at the same
814 --*** old start date will be updated as well.
815 --
816 l_tplrec ota_tpl_shd.g_rec_type;
817 --
818 cursor csr_tplrec is
819 select
820 price_list_id,
821 business_group_id,
822 currency_code,
823 default_flag,
824 name,
825 object_version_number,
826 price_list_type,
827 start_date,
828 comments,
829 description,
830 end_date,
831 single_unit_price,
832 training_unit_type,
833 tpl_information_category,tpl_information1,tpl_information2,
834 tpl_information3,tpl_information4,tpl_information5,tpl_information6,
835 tpl_information7,tpl_information8,tpl_information9,tpl_information10,
836 tpl_information11,tpl_information12,tpl_information13,tpl_information14,
837 tpl_information15,tpl_information16,tpl_information17,tpl_information18,
838 tpl_information19,tpl_information20
839 from ota_price_lists
840 where price_list_id = p_old_tpl_id;
841 --
842 l_tpl_found boolean;
843 l_dates_difference number;
844 l_old_startdate date;
845 l_old_enddate date;
846 --
847 procedure chkp is
848 begin
849 --
850 hr_api.mandatory_arg_error(g_package,'old_price_list_id',p_old_tpl_id);
851 if p_proc_use = 1 then
852 if p_new_tpl_name is null or p_new_startdate is null then
853 constraint_error2('tpl_name_and_date_is_mandatory');
854 end if;
855 --
856 elsif p_proc_use = 2 then
857 if p_new_startdate is null then
858 constraint_error2('startdate_must_be_populated');
859 end if;
860 if not (p_new_tpl_name is null and p_price_increase is null and
861 p_rounding_direction is null and p_rounding_factor is null) then
862 constraint_error2('parameters_should_be_null');
863 end if;
864 --
865 else
866 constraint_error2('p_proc_use_is_wrong');
867 end if;
868 end chkp;
869 -------------
870 begin
871 --
872 chkp;
873 --
874 open csr_tplrec;
875 fetch csr_tplrec into l_tplrec;
876 l_tpl_found := csr_tplrec%found;
877 close csr_tplrec;
878 if not l_tpl_found then
879 constraint_error2('price_list_doesnt_exist');
880 end if;
881 --
882 ----------------------------
883 -- Copy price list function
884 ----------------------------
885 if p_proc_use = 1 then
886 if p_new_tpl_name = l_tplrec.name then
887 constraint_error2('tpl_name_already_exists');
888 end if;
889 --
890 l_old_startdate := l_tplrec.start_date;
891 l_old_enddate := l_tplrec.end_date;
892 l_tplrec.price_list_id := null;
893 l_tplrec.object_version_number := null;
894 l_tplrec.default_flag := 'N';
895 l_tplrec.name := p_new_tpl_name;
896 l_tplrec.start_date := p_new_startdate;
897 l_tplrec.end_date := p_new_enddate;
898 --
899 ota_tpl_ins.ins(l_tplrec,FALSE);
900 --
901 if l_tplrec.price_list_id is null then
902 constraint_error2('ins_tpl_failure');
903 end if;
904 OTA_PLE_BUS.copy_price_list (
905 p_old_price_list_id => p_old_tpl_id,
906 p_new_price_list_id => l_tplrec.price_list_id,
907 p_increase_rate => p_price_increase,
908 p_round_direction => p_rounding_direction,
909 p_round_factor => p_rounding_factor ,
910 p_old_startdate => l_old_startdate,
911 p_new_startdate => p_new_startdate,
912 p_old_enddate => l_old_enddate,
913 p_new_enddate => p_new_enddate
914 );
915 --
916 ------------------------------------
917 -- Change price list dates function
918 -----------------------------------
919 elsif p_proc_use = 2 then
920 --
921 if p_new_startdate = l_tplrec.start_date and
922 p_new_enddate = l_tplrec.end_date then
923 return;
924 end if;
925 --
926 l_old_startdate := l_tplrec.start_date;
927 l_old_enddate := l_tplrec.end_date;
928 l_tplrec.end_date := p_new_enddate;
929 l_tplrec.start_date := p_new_startdate;
930 ota_tpl_upd.upd(l_tplrec,FALSE);
931 --
932 if p_change_entries then
933 OTA_PLE_BUS.widen_entries_dates (
934 p_price_list_id => p_old_tpl_id
935 ,p_old_startdate => l_old_startdate
936 ,p_new_startdate => p_new_startdate
937 ,p_old_enddate => l_old_enddate
938 ,p_new_enddate => p_new_enddate );
939 end if;
940 ----------
941 end if;
942 --
943 end modify_price_list;
944 --***********************************************************************
945 --***********************************************************************
946 --
947 -- PUBLIC
948 --
949 procedure copy_price_list (
950 p_tpl_id in number,
951 p_new_tpl_name in varchar2,
952 p_new_startdate in date,
953 p_new_enddate in date,
954 p_price_increase in number,
955 p_rounding_direction in varchar2,
956 p_rounding_factor in number
957 )
958 is
959 -----------------
960 begin
961 --
962 modify_price_list (
963 p_proc_use => 1
964 ,p_old_tpl_id => p_tpl_id
965 ,p_new_tpl_name => p_new_tpl_name
966 ,p_new_startdate => p_new_startdate
967 ,p_new_enddate => p_new_enddate
968 ,p_price_increase => p_price_increase
969 ,p_rounding_direction => p_rounding_direction
970 ,p_rounding_factor => p_rounding_factor
971 ,p_change_entries => false);
972 --
973 end copy_price_list;
974 --***********************************************************************
975 --***********************************************************************
976 --
977 -- PUBLIC
978 --
979 procedure change_price_list_dates
980 (
981 p_price_list_id in number
982 ,p_new_startdate in date
983 ,p_new_enddate in date
984 ,p_change_entries in char
985 )
986 is
987 ---------------
988 begin
989 --
990 if p_change_entries not in ('Y','N') then
991 constraint_error2('alter_entry_flag_is_wrong');
992 end if;
993 if p_change_entries = 'Y' then
994 g_update := TRUE;
995 else
996 g_update := FALSE;
997 end if;
998 modify_price_list (
999 p_proc_use => 2
1000 ,p_old_tpl_id => p_price_list_id
1001 ,p_new_tpl_name => null
1002 ,p_new_startdate => p_new_startdate
1003 ,p_new_enddate => p_new_enddate
1004 ,p_price_increase => null
1005 ,p_rounding_direction => null
1006 ,p_rounding_factor => null
1007 ,p_change_entries => (p_change_entries='Y') );
1008 --
1009 g_update := FALSE;
1010 --
1011 end change_price_list_dates;
1012 -- ----------------------------------------------------------------------------
1013 -- |---------------------------< insert_validate >----------------------------|
1014 -- ----------------------------------------------------------------------------
1015 Procedure insert_validate(p_rec in ota_tpl_shd.g_rec_type) is
1016 --
1017 l_proc varchar2(72) := g_package||'insert_validate';
1018 --
1019 Begin
1020 hr_utility.set_location('Entering:'||l_proc, 5);
1021 --
1022 -- Call all supporting business operations
1023 insert_validate2(p_rec);
1024 --
1025 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1026 --
1027 hr_utility.set_location(' Leaving:'||l_proc, 10);
1028 End insert_validate;
1029 --
1030 -- ----------------------------------------------------------------------------
1031 -- |---------------------------< update_validate >----------------------------|
1032 -- ----------------------------------------------------------------------------
1033 Procedure update_validate(p_rec in ota_tpl_shd.g_rec_type) is
1034 --
1035 l_proc varchar2(72) := g_package||'update_validate';
1036 --
1037 Begin
1038 hr_utility.set_location('Entering:'||l_proc, 5);
1039 --
1040 -- Call all supporting business operations
1041 update_validate2(p_rec);
1042 --
1043 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1044 --
1045 hr_utility.set_location(' Leaving:'||l_proc, 10);
1046 End update_validate;
1047 --
1048 -- ----------------------------------------------------------------------------
1049 -- |---------------------------< delete_validate >----------------------------|
1050 -- ----------------------------------------------------------------------------
1051 Procedure delete_validate(p_rec in ota_tpl_shd.g_rec_type) is
1052 --
1053 l_proc varchar2(72) := g_package||'delete_validate';
1054 --
1055 Begin
1056 hr_utility.set_location('Entering:'||l_proc, 5);
1057 --
1058 -- Call all supporting business operations
1059 delete_validate2(p_rec);
1060 --
1061 hr_utility.set_location(' Leaving:'||l_proc, 10);
1062 End delete_validate;
1063 --
1064 end ota_tpl_bus;