[Home] [Help]
PACKAGE BODY: APPS.OTA_PLE_BUS
Source
1 Package Body ota_ple_bus as
2 /* $Header: otple01t.pkb 115.3 99/07/16 00:52:56 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ple_bus.'; -- Global package name
9 -- ici insert ple.cat
10 --*******************************************************************************
11 -- PLE: ADDITIONAL API
12 --
13 -- Version Date Author Reason
14 -- 10.15 31May95 lparient.FR Price increase percentage can be negative
15 --*******************************************************************************
16 --
17 -- ***************************
18 -- ADDITIONAL GLOBAL VARIABLES
19 -- ***************************
20 --
21 g_dummy integer(1);
22 g_end_of_time date;
23 --
24 --
25 -- **************************************************
26 -- MANUALLY WRITTEN SECTION AND GENERATED API SECTION
27 -- **************************************************
28 --
29 -- procedure insert_validate2 must be called from generated procedure insert_validate.
30 -- procedure update_validate2 must be called from generated procedure update_validate.
31 -- procedure delete_validate2 must be called from generated procedure delete_validate.
32 --
33 -- Utilities (public procedures):
34 -- copy_entries
35 -- copy_price_list_entries
36 --
37 --*******************************************************************************
38 --
39 --==============================================================================
40 -- GENERAL PROCEDURES
41 --==============================================================================
42 Procedure constraint_error2
43 (p_constraint_name varchar2) Is
44 --
45 l_proc varchar2(72) := g_package||'constraint_error2';
46 --
47 Begin
48 --
49 hr_utility.set_location('Entering:'||l_proc, 5);
50 --
51 if p_constraint_name = 'startdate_must_be_populated' then
52 fnd_message.set_name('OTA','OTA_13457_GEN_MAND_START_DATE');
53 elsif p_constraint_name = 'enddate_must_be_greater_than_startdate' then
54 fnd_message.set_name('OTA','OTA_13312_GEN_DATE_ORDER');
55 elsif p_constraint_name = 'minimum_attendees_should_be_positive' then
56 fnd_message.set_name('OTA','OTA_13296_GEN_MINMAX_POS');
57 elsif p_constraint_name = 'maximum_should_be_greater_than_minimum' then
58 fnd_message.set_name('OTA','OTA_13298_GEN_MINMAX_ORDER');
59 elsif p_constraint_name = 'price_list_must_be_polulated' then
60 fnd_message.set_name('OTA','OTA_13573_PLE_PRICE_LIST_MAND');
61 elsif p_constraint_name = 'activityversion_or_vendorsupply_should_be_null' then
62 fnd_message.set_name('OTA','OTA_13201_PLE_TAV_VSP_EXCL');
63 elsif p_constraint_name = 'price_list_doesnt_exist' Then
64 fnd_message.set_name('OTA','OTA_13574_PLE_NO_PRICE_LIST');
65 elsif p_constraint_name = 'activity_version_doesnt_exist' Then
66 fnd_message.set_name('OTA','OTA_13575_PLE_NO_ACTIVITY');
67 elsif p_constraint_name = 'minimum_attendees_should_be_null' then
68 fnd_message.set_name('OTA','OTA_13576_PLE_MIN_MAX_ATTS');
69 elsif p_constraint_name = 'maximum_attendees_should_be_null' then
70 fnd_message.set_name('OTA','OTA_13576_PLE_MIN_MAX_ATTS');
71 elsif p_constraint_name = 'minimum_attendees_should_be_populated' then
72 fnd_message.set_name('OTA','OTA_13576_PLE_MIN_MAX_ATTS');
73 elsif p_constraint_name = 'maximum_attendees_should_be_populated' then
74 fnd_message.set_name('OTA','OTA_13576_PLE_MIN_MAX_ATTS');
75 elsif p_constraint_name = 'wrong_copy_entries_dates' then
76 fnd_message.set_name('OTA','OTA_13577_PLE_COPY_ENTRY_DATES');
77 elsif p_constraint_name = 'activity_and_vendor_cant_be_both_null' then
78 fnd_message.set_name('OTA','OTA_13238_PLE_TAV_OR_VSP_NOTNU');
79 elsif p_constraint_name = 'price_basis_must_be_C_or_S' then
80 fnd_message.set_name('OTA','OTA_13240_PLE_PRICE_BAS_DOMAIN');
81 elsif p_constraint_name = 'cple_overlapping' Then
82 fnd_message.set_name('OTA','OTA_13208_PLE_C_OVERLAP');
83 elsif p_constraint_name = 'dple_overlapping' Then
84 fnd_message.set_name('OTA','OTA_13210_PLE_D_OVERLAP');
85 elsif p_constraint_name = 'startdate_must_succeed_tpl_startdate' Then
86 fnd_message.set_name('OTA','OTA_13374_PLE_DATES_TPL');
87 elsif p_constraint_name = 'enddate_must_precede_tpl_enddate' Then
88 fnd_message.set_name('OTA','OTA_13374_PLE_DATES_TPL');
89 elsif p_constraint_name = 'startdate_must_succeed_tav_startdate' Then
90 fnd_message.set_name('OTA','OTA_13375_PLE_DATES_TAV');
91 elsif p_constraint_name = 'enddate_must_precede_tav_enddate' Then
92 fnd_message.set_name('OTA','OTA_13375_PLE_DATES_TAV');
93 elsif p_constraint_name = 'increase_is_a_percent' then
94 fnd_message.set_name('OTA','OTA_13399_PLE_INCREASE_RANGE');
95 elsif p_constraint_name = 'wrong_rounding_factor' then
96 fnd_message.set_name('OTA','OTA_13427_PLE_ROUNDING_FACTOR');
97 else
98 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
99 hr_utility.set_message_token('PROCEDURE', l_proc);
100 hr_utility.set_message_token('STEP',p_constraint_name);
101 hr_utility.raise_error;
102 End If;
103 --
104 fnd_message.raise_error;
105 --
106 hr_utility.set_location(' Leaving:'||l_proc, 10);
107 --
108 End constraint_error2;
109 -- =============================================================================
110 --===============================================================================
111 procedure check_tpl
112 (
113 p_tpl_id number
114 )
115 is
116 -------------
117 cursor csr_tpl is
118 select 1 from ota_price_lists
119 where price_list_id = p_tpl_id;
120 --
121 l_parent_exists boolean;
122 -------------
123 begin
124 --
125 hr_utility.set_location('Entering: check_tpl', 5);
126 if p_tpl_id is null then
127 constraint_error2('price_list_must_be_polulated');
128 else
129 hr_utility.trace('Check_tpl '||to_char(p_tpl_id));
130 open csr_tpl;
131 fetch csr_tpl into g_dummy;
132 l_parent_exists := csr_tpl%found;
133 if not l_parent_exists then
134 constraint_error2('price_list_doesnt_exist');
135 end if;
136 end if;
137 hr_utility.set_location('Leaving: check_tpl', 5);
138 --
139 end check_tpl;
140 --=============================================================================
141 --=============================================================================
142 procedure check_tav_vsp_exclusivity
143 (p_tav_id number,
144 p_vsp_id number
145 )
146 is
147 -----------
148 begin
149 hr_utility.set_location('Entering: check_tav_vsp_exclusively', 5);
150 if p_tav_id is not null and p_vsp_id is not null then
151 constraint_error2('activityversion_or_vendorsupply_should_be_null');
152 elsif p_tav_id is null and p_vsp_id is null then
153 constraint_error2('activity_and_vendor_cant_be_both_null');
154 end if;
155 hr_utility.set_location('Leaving: check_tav_vsp_exclusivity', 5);
156
157 --
158 end check_tav_vsp_exclusivity;
159 --=============================================================================
160 --=============================================================================
161 /* N.B. Vendor Supply Id is no longer used, however it is left here in
162 order to maintain compatibility
163 */
164 procedure check_tav_and_vsp
165 (
166 p_tav_id number,
167 p_vsp_id number
168 )
169 is
170 ---------------------
171 cursor csr_tav is
172 select 1 from ota_activity_versions
173 where activity_version_id = p_tav_id;
174 --
175 l_parent_exists boolean;
176 -------------
177
178 begin
179 hr_utility.set_location('Entering: check_tav_and_vsp', 5);
180 --
181 check_tav_vsp_exclusivity(p_tav_id,p_vsp_id);
182 --
183 if p_tav_id is not null then
184 open csr_tav;
185 fetch csr_tav into g_dummy;
186 l_parent_exists := csr_tav%found;
187 close csr_tav;
188 if not l_parent_exists then
189 constraint_error2('activity_version_doesnt_exist');
190 end if;
191 end if;
192 hr_utility.set_location('Leaving: check_tav_and_vsp', 5);
193 --
194 end check_tav_and_vsp;
195 --=============================================================================
196 --=============================================================================
197 procedure check_pricebasis_attendees
198 (
199 p_price_basis varchar2,
200 p_minattendees number,
201 p_maxattendees number
202 )
203 is
204 ---------------
205 begin
206 hr_utility.set_location('Entering: check_pricebasis_attendees', 5);
207 --
208 if p_price_basis is null
209 or p_price_basis not in ('C','S') then
210 constraint_error2('price_basis_must_be_C_or_S');
211 end if;
212 --
213 if p_price_basis = 'S' then
214 if p_minattendees is not null then
215 constraint_error2('minimum_attendees_should_be_null');
216 end if;
217 if p_maxattendees is not null then
218 constraint_error2('maximum_attendees_should_be_null');
219 end if;
220 elsif p_price_basis = 'C' then
221 if p_minattendees is null then
222 constraint_error2('minimum_attendees_should_be_populated');
223 elsif p_minattendees < 1 then
224 constraint_error2('minimum_attendees_should_be_positive');
225 end if;
226 if p_maxattendees is null then
227 constraint_error2('maximum_attendees_should_be_populated');
228 elsif p_maxattendees < p_minattendees then
229 constraint_error2('maximum_should_be_greater_than_minimum');
230 end if;
231 end if;
232 hr_utility.set_location('Leaving: check_pricebasis_attendees', 5);
233 --
234 end check_pricebasis_attendees;
235 --=============================================================================
236 --=============================================================================
237 function dates_are_in_order
238 (
239 p_startdate date,
240 p_enddate date
241 )
242 return boolean is
243 -------------------
244 procedure chkp is
245 begin
246 hr_api.mandatory_arg_error(g_package,'p_startdate',p_startdate);
247 end chkp;
248 -------------------
249 begin
250 hr_utility.set_location('Entering: dates_are_in_order', 5);
251 --
252 chkp;
253 --
254 if p_enddate is null then
255 return TRUE;
256 elsif p_enddate >= p_startdate then
257 return TRUE;
258 else
259 return FALSE;
260 end if;
261 --
262 hr_utility.set_location('Leaving: dates_are_in_order', 5);
263 end dates_are_in_order;
264 --==============================================================================
265 procedure check_dates_order
266 (
267 p_startdate date,
268 p_enddate date
269 )
270 is
271 -------------------
272 begin
273 hr_utility.set_location('Entering: check_dates_order', 5);
274 --
275 if p_startdate is null then
276 constraint_error2('startdate_must_be_populated');
277 end if;
278 --
279 if not dates_are_in_order(p_startdate,p_enddate) then
280 constraint_error2('enddate_must_be_greater_than_startdate');
281 end if;
282 hr_utility.set_location('Leaving: check_dates_order', 5);
283 --
284 end check_dates_order;
285 --============================================================================
286 --============================================================================
287 procedure check_table_constraints
288 (
289 p_rec in OTA_PLE_SHD.g_rec_type
290 )
291 is
292 -------------
293 begin
294 hr_utility.set_location('Entering: check_table_constraints', 5);
295 --
296 check_tpl (
297 p_tpl_id => p_rec.price_list_id);
298 --
299 check_tav_and_vsp (
300 p_tav_id => p_rec.activity_version_id,
301 p_vsp_id => p_rec.vendor_supply_id);
302 --
303 check_pricebasis_attendees (
304 p_price_basis => p_rec.price_basis,
305 p_minattendees => p_rec.minimum_attendees,
306 p_maxattendees => p_rec.maximum_attendees);
307 --
308 check_dates_order (
309 p_startdate => p_rec.start_date,
310 p_enddate => p_rec.end_date);
311 --
312 hr_utility.set_location('Leaving: check_table_constraints', 5);
313 end check_table_constraints;
314 --===============================================================================
315 --===============================================================================
316 function return_tav_id
317 (
318 p_tav_id number,
319 p_vsp_id number
320 )
321 return number is
322 -------------
323 cursor csr_vsp is
324 select activity_version_id from ota_vendor_supplies
325 where vendor_supply_id = p_vsp_id;
326 --
327 l_activity_id ota_activity_versions.activity_version_id%type;
328 --
329 procedure chkp is
330 -------------
331 begin
335 begin
332 check_tav_vsp_exclusivity(p_tav_id,p_vsp_id);
333 end chkp;
334 ---------
336 hr_utility.set_location('Entering: return_tav_id', 5);
337 --
338 chkp;
339 --
340 if p_tav_id is null then
341 open csr_vsp;
342 fetch csr_vsp into l_activity_id;
343 close csr_vsp;
344 else
345 l_activity_id := p_tav_id;
346 end if;
347 --
348 return l_activity_id;
349 --
350 hr_utility.set_location('Leaving: return_tav_id', 5);
351 end return_tav_id;
352 -- *****************************************************************************
353 -- DATES VALIDATION
354 -- See also overlapping
355 --*******************************************************************************
356 function start_succeeds_tpl_start
357 (
358 p_ple_startdate date,
359 p_tpl_id number
360 )
361 return boolean is
362 --
363 --*** Price list entry's start date must be >= Price list's start date
364 ---------------------
365 cursor csr_tpl_date is
366 select start_date
367 from ota_price_lists
368 where price_list_id = p_tpl_id;
369 --
370 l_tpl_startdate date;
371 --
372 l_parent_exists boolean;
373 --
374 procedure chkp is
375 begin
376 hr_api.mandatory_arg_error(g_package,'p_ple_startdate',p_ple_startdate);
377 hr_api.mandatory_arg_error(g_package,'start_succeeds_tpl_start:p_tpl_id',p_tpl_id);
378 end chkp;
379 ------------------
380 begin
381 hr_utility.set_location('Entering: start_succeeds_tpl_start', 5);
382 --
383 chkp;
384 --
385 open csr_tpl_date;
386 fetch csr_tpl_date into l_tpl_startdate;
387 l_parent_exists := csr_tpl_date%found;
388 close csr_tpl_date;
389 --
390 if not l_parent_exists then
391 hr_utility.trace('start_succeeds_tpl_start ');
392 constraint_error2('price_list_doesnt_exist');
393 end if;
394 --
395 if p_ple_startdate >= l_tpl_startdate then
396 return TRUE;
397 else
398 return FALSE;
399 end if;
400 --
401 hr_utility.set_location('Leaving: start_succeeds_tpl_start', 5);
402 end start_succeeds_tpl_start;
403 --==============================================================================
404 --==============================================================================
405 function start_succeeds_tav_start
406 (
407 p_ple_startdate date,
408 p_tav_id number
409 )
410 return boolean is
411 --
412 --*** Price list entry's start date must be >= Activity version start date
413 --*** Activity version id may be null in ota_price_list_entries
414 ---------------------
415 cursor csr_tav_date is
416 select start_date
417 from ota_activity_versions
418 where activity_version_id = p_tav_id;
419 --
420 l_tav_startdate date;
421 --
422 l_parent_exists boolean;
423 --
424 procedure chkp is
425 begin
429 ------------------
426 hr_api.mandatory_arg_error(g_package,'p_ple_startdate',p_ple_startdate);
427 hr_api.mandatory_arg_error(g_package,'start_succeeds_tav_start:p_tav_id',p_tav_id);
428 end chkp;
430 begin
431 hr_utility.set_location('Entering: start_succeeds_tav_start', 5);
432 --
433 chkp;
434 --
435 open csr_tav_date;
436 fetch csr_tav_date into l_tav_startdate;
437 l_parent_exists := csr_tav_date%found;
438 close csr_tav_date;
439 --
440 if not l_parent_exists then
441 constraint_error2('activity_version_doesnt_exist');
442 end if;
443 --
444 hr_utility.set_location('ple_startdate = '||to_char(p_ple_startdate
445 ,'DD-MON-YYYY'), 5);
446 hr_utility.set_location('l_tav_startdate = '||to_char(l_tav_startdate
447 ,'DD-MON-YYYY'), 5);
448 if l_tav_startdate is null then
449 return TRUE;
450 elsif p_ple_startdate >= l_tav_startdate then
451 return TRUE;
452 else
453 return FALSE;
454 end if;
455 --
456 hr_utility.set_location('Leaving: start_succeeds_tav_start', 5);
457 end start_succeeds_tav_start;
458 --==============================================================================
459 --==============================================================================
460 function end_precedes_tpl_end
461 (
462 p_ple_enddate date,
463 p_tpl_id number
464 )
465 return boolean is
466 --
467 --*** Price list entry's end date must be <= Price list's end date
468 ---------------------
469 cursor csr_tpl_date is
470 select end_date
471 from ota_price_lists
472 where price_list_id = p_tpl_id;
473 --
474 l_tpl_enddate date;
475 --
476 l_parent_exists boolean;
477 --
478 procedure chkp is
479 begin
480 hr_api.mandatory_arg_error(g_package,'end_precedes_tpl_end:p_tpl_id',p_tpl_id);
481 end chkp;
482 ------------------
483 begin
484 hr_utility.set_location('Entering: end_precedes_tpl_end', 5);
485 --
486 chkp;
487 --
488 open csr_tpl_date;
489 fetch csr_tpl_date into l_tpl_enddate;
490 l_parent_exists := csr_tpl_date%found;
491 close csr_tpl_date;
492 --
493 if not l_parent_exists then
494 hr_utility.trace('end_precedes_tpl_end');
495 constraint_error2('price_list_doesnt_exist');
496 end if;
497 --
498 hr_utility.trace('TPL End Date = '||to_char(l_tpl_enddate));
499 hr_utility.trace('PLE End Date = '||to_char(p_ple_enddate));
500 --
501 if l_tpl_enddate is null then
502 return TRUE;
503 elsif nvl(p_ple_enddate,hr_general.end_of_time) <= l_tpl_enddate then
504 return TRUE;
505 else
506 return FALSE;
507 end if;
508 --
509 hr_utility.set_location('Leaving: end_precedes_tpl_end', 5);
510 end end_precedes_tpl_end;
511 --==============================================================================
512 --==============================================================================
513 function end_precedes_tav_end
514 (
515 p_ple_enddate date,
516 p_tav_id number
517 )
518 return boolean is
519 --
520 --*** Price list entry's must be <= Activity version's end date
521 --*** Activity version id may be null in ota_price_list_entries
522 ---------------------
523 cursor csr_tav_date is
524 select end_date
525 from ota_activity_versions
526 where activity_version_id = p_tav_id;
527 --
528 l_tav_enddate date;
529 --
530 l_parent_exists boolean;
531 --
532 procedure chkp is
533 begin
534 hr_api.mandatory_arg_error(g_package,'end_precedes_tav_end:p_tav_id',p_tav_id);
535 end chkp;
536 --------------
537 begin
538 --
539 open csr_tav_date;
540 fetch csr_tav_date into l_tav_enddate;
541 l_parent_exists := csr_tav_date%found;
542 close csr_tav_date;
543 --
544 if not l_parent_exists then
545 constraint_error2('activity_version_doesnt_exist');
546 end if;
547 --
548 if l_tav_enddate is null then
549 return TRUE;
550 elsif nvl(p_ple_enddate,hr_general.end_of_time) <= l_tav_enddate then
551 return TRUE;
552 else
553 return FALSE;
554 end if;
555 --
556 end end_precedes_tav_end;
557 --==============================================================================
558 --==============================================================================
559 procedure check_ple_startdate
560 (
561 p_ple_startdate date,
562 p_tpl_parent number,
563 p_tav_parent number
564 )
565 is
566 -----------------
567 begin
568 --
569 if not start_succeeds_tpl_start(p_ple_startdate,p_tpl_parent) then
570 constraint_error2('startdate_must_succeed_tpl_startdate');
571 end if;
572 --
573 if not start_succeeds_tav_start(p_ple_startdate,p_tav_parent) then
574 constraint_error2('startdate_must_succeed_tav_startdate');
575 end if;
576 --
577 end check_ple_startdate;
578 --==============================================================================
579 --==============================================================================
580 procedure check_ple_enddate
581 (
582 p_ple_enddate date,
583 p_tpl_parent number,
584 p_tav_parent number
585 )
586 is
587 -----------------
588 begin
589 --
590 if not end_precedes_tpl_end(p_ple_enddate,p_tpl_parent) then
591 constraint_error2('enddate_must_precede_tpl_enddate');
592 end if;
593 --
594 if not end_precedes_tav_end(p_ple_enddate,p_tav_parent) then
598 end check_ple_enddate;
595 constraint_error2('enddate_must_precede_tav_enddate');
596 end if;
597 --
599 --==============================================================================
600 --==============================================================================
601 procedure check_ple_dates
602 (
603 p_ple_startdate date,
604 p_ple_enddate date,
605 p_tpl_parent number,
606 p_tav_parent number
607 )
608 is
609 ------------
610 begin
611 --
612 check_ple_startdate(p_ple_startdate,p_tpl_parent,p_tav_parent);
613 check_ple_enddate(p_ple_enddate,p_tpl_parent,p_tav_parent);
614 --
615 end check_ple_dates;
616 -- *****************************************************************************
617 -- OVERLAPPING CHECKS
618 --*******************************************************************************
619 procedure check_ple_overlapping
620 (
621 p_ple_id number,
622 p_tpl_id number,
623 p_tav_id number,
624 p_vsp_id number,
625 p_price_basis varchar2,
626 p_start_date date,
627 p_end_date date,
628 p_minattendees number,
629 p_maxattendees number
630 )
631 is
632 --
633 --*** For PLE based on Customer (price_basis = 'C'),the dates of 2 PLE
634 --*** may overlap only when their attendance ranges do not overlap.
635 --*** For other PLEs, dates may never overlap.
636 --*** NB: p_ple_id must be null if it's an INSERT.
637 --
638 ----------------
639 cursor csr_dple is
640 select 1
641 from ota_price_list_entries
642 where price_list_id = p_tpl_id
643 and nvl(activity_version_id,0) = nvl(p_tav_id,0)
644 and nvl(vendor_supply_id,0) = nvl(p_vsp_id,0)
645 and price_basis = p_price_basis
646 and p_start_date <= nvl(end_date,g_end_of_time)
647 and nvl(p_end_date,g_end_of_time) >= start_date
648 and (p_ple_id is null or price_list_entry_id <> p_ple_id);
649 --
650 cursor csr_cple is
651 select 1
652 from ota_price_list_entries
653 where price_list_id = p_tpl_id
654 and nvl(activity_version_id,0) = nvl(p_tav_id,0)
655 and nvl(vendor_supply_id,0) = nvl(p_vsp_id,0)
656 and price_basis = p_price_basis
657 and p_start_date <= nvl(end_date,g_end_of_time)
658 and nvl(p_end_date,g_end_of_time) >= start_date
659 and p_minattendees <= maximum_attendees
660 and p_maxattendees >= minimum_attendees
661 and (p_ple_id is null or price_list_entry_id <> p_ple_id);
662 --
663 l_found boolean;
664 l_dummy integer;
665 --
666 procedure chkp is
667 begin
668 hr_api.mandatory_arg_error(g_package,'check_ple_overlapping:p_tpl_id',p_tpl_id);
669 check_tav_and_vsp(p_tav_id,p_vsp_id);
670 check_pricebasis_attendees(p_price_basis,p_minattendees,p_maxattendees);
671 hr_api.mandatory_arg_error(g_package,'p_start_date',p_start_date);
672 end chkp;
673 -------------------------
674 begin
675 --
676 chkp;
677 --
678 g_end_of_time := hr_general.end_of_time;
679 --
680 if p_price_basis = 'C' then
681 open csr_cple;
682 fetch csr_cple into l_dummy;
683 l_found := csr_cple%found;
684 close csr_cple;
685 if l_found then
686 constraint_error2('cple_overlapping');
687 end if;
688 else
689 open csr_dple;
690 fetch csr_dple into l_dummy;
691 l_found := csr_dple%found;
692 close csr_dple;
693 if l_found then
694 constraint_error2('dple_overlapping');
695 end if;
696 end if;
697 --
698 end check_ple_overlapping;
699 -- ******************************************************************************
700 -- GENERAL VALIDATION PROCEDURES
701 --********************************************************************************
702 procedure insert_and_update_validate
703 (
704 p_rec in OTA_PLE_SHD.g_rec_type
705 )
706 is
707 ---------------------
708 l_tav_id ota_activity_versions.activity_version_id%type;
709 --
710 begin
711 --
712 check_table_constraints (p_rec);
713 --
714 l_tav_id := return_tav_id(p_rec.activity_version_id,p_rec.vendor_supply_id);
715 check_ple_dates (
716 p_ple_startdate => p_rec.start_date,
717 p_ple_enddate => p_rec.end_date,
718 p_tpl_parent => p_rec.price_list_id,
719 p_tav_parent => l_tav_id);
720 --
721 check_ple_overlapping (
722 p_ple_id => p_rec.price_list_entry_id,
723 p_tpl_id => p_rec.price_list_id,
724 p_tav_id => p_rec.activity_version_id,
725 p_vsp_id => p_rec.vendor_supply_id,
726 p_price_basis => p_rec.price_basis,
727 p_start_date => p_rec.start_date,
728 p_end_date => p_rec.end_date,
729 p_minattendees => p_rec.minimum_attendees,
730 p_maxattendees => p_rec.maximum_attendees);
731
732 --
733 end insert_and_update_validate;
734 --==============================================================================
735 --==============================================================================
736 procedure insert_validate2
737 (
738 p_rec in OTA_PLE_SHD.g_rec_type
739 )
740 is
741 ---------------------
742 begin
743 --
744 insert_and_update_validate(p_rec);
745 --
746 end insert_validate2;
747 --==============================================================================
748 --==============================================================================
749 procedure update_validate2
750 (
751 p_rec in OTA_PLE_SHD.g_rec_type
752 )
753 is
754 ---------------------
755 begin
756 --
757 insert_and_update_validate(p_rec);
758 --
762 (
759 end update_validate2;
760 --==============================================================================
761 procedure delete_validate2
763 p_rec in OTA_PLE_SHD.g_rec_type
764 )
765 is
766 ---------------------
767 begin
768 --
769 return;
770 --
771 end delete_validate2;
772 -- *********************************************************************
773 --***********************************************************************
774 function return_new_price (
775 p_old_price number,
776 p_price_increase number,
777 p_rounding_direction varchar2,
778 p_rounding_factor number
779 )
780 return number is
781 ---------------------
782 l_new_price OTA_PRICE_LIST_ENTRIES.price%TYPE;
783 --
784 procedure chkp is
785 begin
786 hr_api.mandatory_arg_error(g_package,'old_price_list_price',p_old_price);
787 if p_price_increase is null then
788 constraint_error2('increase_is_a_percent');
789 elsif p_price_increase < -100 or p_price_increase > 100 then
790 constraint_error2('increase_is_a_percent');
791 end if;
792 if p_rounding_direction not in ('N','U','D','R') then
793 constraint_error2('rounding_direction_wrong_value');
794 end if;
795 if p_rounding_direction <> 'N' and (p_rounding_factor is null or p_rounding_factor=0) then
796 constraint_error2('wrong_rounding_factor');
797 end if;
798 end chkp;
799 --------------------
800 begin
801 hr_utility.set_location('Entering: return_new_price', 5);
802 --
803 chkp;
804 --
805 l_new_price := (p_old_price * p_price_increase /100) + p_old_price;
806 --
807 if p_rounding_direction = 'D' then
808 l_new_price := p_rounding_factor*(floor(l_new_price/p_rounding_factor));
809 --
810 elsif p_rounding_direction = 'U' then
811 l_new_price := p_rounding_factor*(ceil(l_new_price/p_rounding_factor));
812 --
813 elsif p_rounding_direction = 'R' then
814 l_new_price := p_rounding_factor*(round(l_new_price/p_rounding_factor));
815 --
816 end if;
817 --
818 hr_utility.set_location('Leaving: return_new_price', 5);
819 return l_new_price;
820 --
821 end return_new_price;
822 --***********************************************************************
823 --***********************************************************************
824 procedure modify_entries
825 (
826 p_proc_use number,
827 p_old_tpl_id number,
828 p_price_increase number,
829 p_rounding_direction varchar2,
830 p_rounding_factor number,
831 p_new_tpl_id number,
832 p_dates_difference number,
833 p_increase_date date,
834 p_enddate date,
835 p_starting_from date,
836 p_old_startdate date default null,
837 p_new_startdate date default null,
838 p_old_enddate date default null,
839 p_new_enddate date default null
840 )
841 is
842 -----------------
843 --*** Create new entries based on existing Entries
844 --*** with new dates and a price increase.
845 --
846 l_plerec ota_ple_shd.g_rec_type;
847 l_plerec2 ota_ple_shd.g_rec_type;
848 l_activity_version_id number;
849 l_activity_start_date date;
850 l_activity_end_date date;
851 l_start_date date;
852 l_end_date date;
853 --
854 cursor csr_plerec is
855 select
856 ple.price_list_entry_id,
857 ple.vendor_supply_id,
858 ple.activity_version_id,
859 ple.price_list_id,
860 ple.object_version_number,
861 ple.price,
862 ple.price_basis,
863 ple.start_date,
864 ple.comments,
865 ple.end_date,
866 ple.maximum_attendees,
867 ple.minimum_attendees,
868 ple.ple_information_category,
869 ple.ple_information1,
870 ple.ple_information2,
871 ple.ple_information3,
872 ple.ple_information4,
873 ple.ple_information5,
874 ple.ple_information6,
875 ple.ple_information7,
876 ple.ple_information8,
877 ple.ple_information9,
878 ple.ple_information10,
879 ple.ple_information11,
880 ple.ple_information12,
881 ple.ple_information13,
882 ple.ple_information14,
883 ple.ple_information15,
884 ple.ple_information16,
885 ple.ple_information17,
886 ple.ple_information18,
890 where ple.price_list_id = p_old_tpl_id
887 ple.ple_information19,
888 ple.ple_information20
889 from ota_price_list_entries ple
891 and ((p_proc_use = 2
892 and ple.start_date >= nvl(p_starting_from,hr_api.g_sot)
893 and not (ple.start_date > nvl(p_enddate,hr_api.g_eot)
894 or nvl(ple.end_date,hr_api.g_eot)
895 < nvl(p_increase_date,hr_api.g_sot)
896 )
897 )
898 or p_proc_use <>2);
899 --
900 cursor get_activity is
901 select start_date,end_date
902 from ota_activity_versions
903 where activity_version_id = l_activity_version_id;
904 --
905 l_copy boolean;
906 l_dates_difference number;
907 --
908 procedure chkp is
909 begin
910 if p_proc_use = 1 then
911 -- Copy price list and entries with price increase function.
912 hr_api.mandatory_arg_error(g_package,'new_price_list_id',p_new_tpl_id);
913 if not (p_increase_date is null and p_starting_from is null) then
914 constraint_error2('arguments_should_be_null_1');
915 end if;
916 elsif p_proc_use = 2 then
917 -- Copy entries with price increase function.
918 hr_api.mandatory_arg_error(g_package,'old_price_list_id',p_old_tpl_id);
919 if p_increase_date is null then
920 constraint_error2('startdate_must_be_populated');
921 end if;
922 if not(p_new_tpl_id is null and p_dates_difference is null) then
923 constraint_error2('arguments_should_be_null_2');
924 end if;
925 -- Close down price list function.
926 elsif p_proc_use = 3 then
927 hr_api.mandatory_arg_error(g_package,'old_price_list_id',p_old_tpl_id);
928 if not(p_new_tpl_id is null and p_dates_difference is null and p_increase_date is null
929 and p_starting_from is null and p_price_increase is null
930 and p_rounding_direction is null and p_rounding_factor is null) then
931 constraint_error2('arguments_should_be_null_3');
932 end if;
933 else
934 constraint_error2('p_proc_use is wrong');
935 end if;
936 end chkp;
937 ---------------------
938 begin
939 hr_utility.set_location('Entering: modify_entries', 5);
940 --
941 chkp;
942 -------------
943 open csr_plerec;
944 --
945 LOOP
946 --
947 hr_utility.trace('Price List ID '||to_char(p_old_tpl_id));
948 l_copy := true;
949 fetch csr_plerec into l_plerec;
950 l_activity_version_id := l_plerec.activity_version_id;
951 exit when csr_plerec%notfound;
952 --
953 ---------------------------
954 -- Copy price list function
955 ---------------------------
956 if p_proc_use = 1 then
957 hr_utility.trace('Price List Entry ID '||to_char(l_plerec.price_list_entry_id));
958 --
959 open get_activity;
960 fetch get_activity into l_activity_start_date,l_activity_end_date;
961 close get_activity;
962 --
963 l_plerec.price_list_id := p_new_tpl_id;
964 --
965 if p_new_startdate < p_old_startdate then
969 ,l_activity_start_date);
966 hr_utility.trace('New Start Date < Old Start Date');
967 if p_old_startdate = l_plerec.start_date then
968 l_plerec.start_date := greatest(p_new_startdate
970 end if;
971 --
972 elsif p_new_startdate > p_old_startdate then
973 hr_utility.trace('New Start Date > Old Start Date');
974 l_plerec.start_date := greatest(l_plerec.start_date
975 ,p_new_startdate);
976 end if;
977 --
978 if nvl(p_new_enddate,hr_api.g_eot) < nvl(p_old_enddate,hr_api.g_eot) then
979 hr_utility.trace('New End Date < Old End Date');
980 if l_plerec.end_date is null and p_new_enddate is null then
981 l_plerec.end_date := null;
982 else
983 l_plerec.end_date := least(nvl(p_new_enddate,hr_api.g_eot)
984 ,nvl(l_plerec.end_date,hr_api.g_eot));
985 end if;
986 --
987 elsif
988 nvl(p_new_enddate,hr_api.g_eot) > nvl(p_old_enddate,hr_api.g_eot) then
989 hr_utility.trace('New End Date > Old End Date');
990 if nvl(p_old_enddate,hr_api.g_eot) =
991 nvl(l_plerec.end_date,hr_api.g_eot) then
992 if p_new_enddate is null and l_activity_end_date is null then
993 l_plerec.end_date := null;
994 else
995 l_plerec.end_date := least(nvl(p_new_enddate,hr_api.g_eot)
996 ,nvl(l_activity_end_date,hr_api.g_eot));
997 end if;
998 end if;
999 --
1000 end if;
1001 --
1002 hr_utility.trace('Start Date '||to_char(l_plerec.start_date));
1003 hr_utility.trace(' End Date '||to_char(nvl(l_plerec.end_date,hr_api.g_eot)));
1004 if l_plerec.start_date > nvl(l_plerec.end_date,hr_api.g_eot) then
1005 null;
1006 else
1007 hr_utility.trace('Inserting Entry');
1008 l_plerec.price_list_entry_id := null;
1009 l_plerec.object_version_number := null;
1010 l_plerec.price := return_new_price(l_plerec.price
1011 ,p_price_increase
1012 ,p_rounding_direction
1013 ,p_rounding_factor);
1014 ota_ple_ins.ins(l_plerec,FALSE);
1015 end if;
1016 --
1017 -------------------------
1018 -- Copy entries function
1019 -------------------------
1020 elsif p_proc_use = 2 then
1021 --
1022 hr_utility.trace('Proc Use = 2');
1023 hr_utility.trace('PLE Start Date '||to_char(l_plerec.start_date));
1024 hr_utility.trace('PLE End Date '||to_char(l_plerec.end_date));
1025 --
1026 if p_increase_date <= l_plerec.start_date then
1027 if nvl(p_enddate,hr_api.g_eot)
1028 >= nvl(l_plerec.end_date,hr_api.g_eot) then
1029 --
1030 hr_utility.trace('Updating the whole entry');
1031 l_plerec2 := l_plerec;
1032 l_plerec2.price := return_new_price(l_plerec2.price
1033 ,p_price_increase
1034 ,p_rounding_direction
1035 ,p_rounding_factor);
1036 ota_ple_upd.upd(l_plerec2,FALSE);
1037 --
1038 else -- nvl(p_enddate,hr_api.g_eot)
1039 -- < nvl(l_plerec.end_date,hr_api.g_eot)
1040 --
1041 hr_utility.trace('Updating the entry up until the New End Date');
1042 l_plerec2 := l_plerec;
1043 l_plerec2.end_date := p_enddate;
1044 l_plerec2.price := return_new_price(l_plerec2.price
1045 ,p_price_increase
1046 ,p_rounding_direction
1047 ,p_rounding_factor);
1048 ota_ple_upd.upd(l_plerec2,FALSE);
1049 --
1050 hr_utility.trace('Inserting the remainder of the entry with old price');
1051 l_plerec2 := l_plerec;
1052 l_plerec2.price_list_entry_id := null;
1053 l_plerec2.object_version_number := null;
1057 end if;
1054 l_plerec2.start_date := p_enddate + 1;
1055 ota_ple_ins.ins(l_plerec2,FALSE);
1056 --
1058 --
1059 else -- p_increase_date > l_plerec.start_date
1060 --
1061 if nvl(p_enddate,hr_api.g_eot)
1062 >= nvl(l_plerec.end_date,hr_api.g_eot) then
1063 --
1064 hr_utility.trace('Setting New End Date on old entry');
1065 l_plerec2 := l_plerec;
1066 l_plerec2.end_date := p_increase_date - 1;
1067 ota_ple_upd.upd(l_plerec2,FALSE);
1068 --
1069 hr_utility.trace('Inserting new entry with updated price');
1070 l_plerec2 := l_plerec;
1071 l_plerec2.price_list_entry_id := null;
1072 l_plerec2.object_version_number := null;
1073 l_plerec2.start_date := p_increase_date;
1074 l_plerec2.price := return_new_price(l_plerec2.price
1075 ,p_price_increase
1076 ,p_rounding_direction
1077 ,p_rounding_factor);
1078 ota_ple_ins.ins(l_plerec2,FALSE);
1079 --
1080 else -- nvl(p_enddate,hr_api.g_eot)
1081 -- < nvl(l_plerec.end_date,hr_api.g_eot)
1082 --
1083 hr_utility.trace('Setting New End Date on old entry');
1084 l_plerec2 := l_plerec;
1085 l_plerec2.end_date := p_increase_date - 1;
1086 ota_ple_upd.upd(l_plerec2,FALSE);
1087 --
1088 hr_utility.trace('Inserting entry with new price');
1089 l_plerec2 := l_plerec;
1090 l_plerec2.price_list_entry_id := null;
1091 l_plerec2.object_version_number := null;
1092 l_plerec2.start_date := p_increase_date;
1093 l_plerec2.end_date := p_enddate;
1094 l_plerec2.price := return_new_price(l_plerec2.price
1095 ,p_price_increase
1096 ,p_rounding_direction
1097 ,p_rounding_factor);
1098 ota_ple_ins.ins(l_plerec2,FALSE);
1099 --
1100 hr_utility.trace('Inserting the remainder of the entry with old price');
1101 l_plerec2 := l_plerec;
1102 l_plerec2.price_list_entry_id := null;
1103 l_plerec2.object_version_number := null;
1104 l_plerec2.start_date := p_enddate + 1;
1105 ota_ple_ins.ins(l_plerec2,FALSE);
1106 --
1107 end if;
1108 --
1109 end if;
1110 --
1111 ------------------------
1112 -- Change Price List Entry Dates (called when Chnage Dates used)
1113 ------------------------
1114 elsif p_proc_use = 3 then
1115 l_copy := false;
1116 --
1117 open get_activity;
1118 fetch get_activity into l_activity_start_date,l_activity_end_date;
1119 close get_activity;
1120 --
1121 -- if the entry is outside of the new dates delete the entry
1122 --
1123 if l_plerec.start_date >= nvl(p_new_enddate,hr_api.g_eot) or
1124 nvl(l_plerec.end_date,hr_api.g_eot) <= p_new_startdate then
1125 OTA_PLE_DEL.del(l_plerec,FALSE);
1126 else
1127 --
1128 -- if the start date is before the entry then open up the entry
1129 --
1130 if l_plerec.start_date = p_old_startdate
1131 and l_plerec.start_date > p_new_startdate then
1132 --
1133 l_start_date := greatest(p_new_startdate
1134 ,l_activity_start_date);
1135 --
1136 --
1137 -- if the start date is after the entry start then close down the entry
1138 --
1139 elsif l_plerec.start_date < p_new_startdate then
1140 --
1141 l_start_date := p_new_startdate;
1142 else
1143 l_start_date := l_plerec.start_date;
1144 end if;
1145 --
1146 --
1147 -- if the end date is after the entry start then open up the entry
1148 --
1149 if nvl(l_plerec.end_date,hr_api.g_eot) =
1150 nvl(p_old_enddate,hr_api.g_eot)
1151 and nvl(l_plerec.end_date,hr_api.g_eot) <
1152 nvl(p_new_enddate,hr_api.g_eot) then
1153 --
1154 l_end_date := least(nvl(l_activity_end_date,hr_api.g_eot)
1155 ,nvl(p_new_enddate,hr_api.g_eot));
1156 --
1157 --
1158 -- if the start date is after the entry start then close down the entry
1159 --
1160 elsif nvl(l_plerec.end_date,hr_api.g_eot) >
1161 nvl(p_new_enddate,hr_api.g_eot) then
1162 --
1163 l_end_date := p_new_enddate;
1164 else
1165 l_end_date := nvl(l_plerec.end_date,hr_api.g_eot);
1166 end if;
1167 --
1168 if l_end_date = hr_api.g_eot then
1169 l_plerec.end_date := null;
1170 end if;
1171 --
1172 --
1173 -- Only do the update if the Entry Start or End Dates have changed
1174 --
1175 if l_plerec.start_date <> l_start_date
1176 or nvl(l_plerec.end_date,hr_api.g_eot) <> l_end_date then
1177 if l_end_date = hr_api.g_eot then
1178 l_plerec.end_date := null;
1179 else
1180 l_plerec.end_date := l_end_date;
1181 end if;
1182 l_plerec.start_date := l_start_date;
1183 --
1184 hr_utility.trace('Start Date = '||to_char(l_plerec.start_date));
1185 hr_utility.trace(' End Date = '||to_char(l_plerec.end_date));
1186 OTA_PLE_UPD.upd(l_plerec,FALSE);
1187 --
1188 end if;
1189 end if;
1190 -----------------------
1191 end if;
1192 -----------------------
1193 END LOOP;
1197 hr_utility.set_location('Leaving: modify_entries', 5);
1194 --
1195 close csr_plerec;
1196 --
1198 end modify_entries;
1199 --***********************************************************************
1200 --***********************************************************************
1201 --
1202 -- PUBLIC
1203 -- Description: Create new entries based on existing Entries
1204 -- with new dates and a price increase.
1205 --
1206 procedure copy_price_list_entries (
1207 p_price_list_id number,
1208 p_increase_date date,
1209 p_enddate date,
1210 p_increase_rate number,
1211 p_round_direction varchar2,
1212 p_round_factor number,
1213 p_select_entries char,
1214 p_starting_from date
1215 )
1216 is
1217 ---------------
1218 l_enddate date;
1219 l_dummy varchar2(50);
1220 --
1221 --
1222 procedure chkp is
1223 begin
1224 if not ( (p_select_entries='S' and p_starting_from is not null)
1225 or (p_select_entries='A' and p_starting_from is null) ) then
1226 constraint_error2('Copy_entries_wrong_parameters');
1227 end if;
1228 end chkp;
1229 ---------------
1230 begin
1231 hr_utility.set_location('Entering: copy_price_list_entries', 5);
1232 --
1233 chkp;
1234 --
1235 modify_entries (
1236 p_proc_use => 2,
1237 p_old_tpl_id => p_price_list_id,
1238 p_price_increase => p_increase_rate,
1239 p_rounding_direction => p_round_direction,
1240 p_rounding_factor => p_round_factor,
1241 p_new_tpl_id => null,
1242 p_dates_difference => null,
1243 p_increase_date => p_increase_date,
1244 p_enddate => p_enddate,
1245 p_starting_from => p_starting_from );
1246 --
1247 hr_utility.set_location('Leaving: copy_price_list_entries', 5);
1248 end copy_price_list_entries;
1249 --=============================================================
1250 --============================================================
1251 --
1252 -- PUBLIC: called from OTA_TPL_BUS
1253 -- No client side procedure.
1254 --
1255 procedure copy_price_list (
1256 p_old_price_list_id in number,
1257 p_new_price_list_id in number,
1258 p_increase_rate number,
1259 p_round_direction varchar2,
1260 p_round_factor number,
1261 p_old_startdate date,
1262 p_new_startdate date,
1263 p_old_enddate date,
1264 p_new_enddate date
1265 )
1266 is
1267 ---------------
1268 begin
1269 hr_utility.set_location('Entering: copy_price_list', 5);
1270 --
1271 modify_entries (
1272 p_proc_use => 1,
1273 p_old_tpl_id => p_old_price_list_id,
1274 p_price_increase => p_increase_rate,
1275 p_rounding_direction => p_round_direction,
1276 p_rounding_factor => p_round_factor,
1277 p_new_tpl_id => p_new_price_list_id,
1278 p_old_startdate => p_old_startdate,
1279 p_new_startdate => p_new_startdate,
1280 p_old_enddate => p_old_enddate,
1281 p_new_enddate => p_new_enddate,
1282 p_dates_difference => null,
1283 p_increase_date => null,
1284 p_enddate => null,
1285 p_starting_from => null );
1286 --
1287 hr_utility.set_location('Leaving: copy_price_list', 5);
1288 end copy_price_list;
1289 --=============================================================
1290 --=============================================================
1291 --
1292 -- PUBLIC: Called from OTA_TPL_BUS
1293 -- Not a client-side procedure
1294 --
1295 procedure widen_entries_dates (
1296 p_price_list_id in number
1297 ,p_old_startdate in date
1298 ,p_new_startdate in date
1299 ,p_old_enddate in date
1300 ,p_new_enddate in date
1301 )
1302 is
1303 ---------------
1304 begin
1305 hr_utility.set_location('Entering: widen_entries_dates', 5);
1306 --
1307 modify_entries (
1308 p_proc_use => 3
1309 ,p_old_tpl_id => p_price_list_id
1310 ,p_price_increase => null
1311 ,p_rounding_direction => null
1312 ,p_rounding_factor => null
1313 ,p_new_tpl_id => null
1314 ,p_dates_difference => null
1315 ,p_increase_date => null
1316 ,p_enddate => p_new_enddate
1317 ,p_starting_from => null
1318 ,p_old_startdate => p_old_startdate
1319 ,p_new_startdate => p_new_startdate
1320 ,p_old_enddate => p_old_enddate
1321 ,p_new_enddate => p_new_enddate
1322 );
1323 --
1324 hr_utility.set_location('Leaving: widen_entries_dates', 5);
1325 end widen_entries_dates;
1326
1327
1328 -- ----------------------------------------------------------------------------
1329 -- |-----------------------------< copy_price >------------------------------|
1330 -- ----------------------------------------------------------------------------
1331 --
1332 -- PUBLIC
1333 -- Description:
1334 -- Copies all pricelistentry information from a given activity version to
1335 -- another activity version.
1336 --
1337 Procedure copy_price
1338 (
1339 p_activity_version_from in number
1340 ,p_activity_version_to in number
1341 ) is
1342 --
1343 l_rec ota_price_list_entries%rowtype;
1344 v_proc varchar2(72) := g_package||'copy_price';
1345 --
1346 cursor sel_price is
1347 select *
1348 from ota_price_list_entries ple
1349 where ple.activity_version_id = p_activity_version_from;
1350 --
1351 Begin
1352 --
1353 hr_utility.set_location('Entering:'|| v_proc, 5);
1354 --
1355 Open sel_price;
1356 fetch sel_price into l_rec;
1357 --
1358 Loop
1359 --
1360 Exit When sel_price%notfound;
1361 --
1365 , l_rec.price_list_id
1362 OTA_PLE_INS.ins( l_rec.price_list_entry_id
1363 , l_rec.vendor_supply_id
1364 , p_activity_version_to
1366 , l_rec.object_version_number
1367 , l_rec.price
1368 , l_rec.price_basis
1369 , l_rec.start_date
1370 , l_rec.comments
1371 , l_rec.end_date
1372 , l_rec.maximum_attendees
1373 , l_rec.minimum_attendees
1374 , l_rec.ple_information_category
1375 , l_rec.ple_information1
1376 , l_rec.ple_information2
1377 , l_rec.ple_information3
1378 , l_rec.ple_information4
1379 , l_rec.ple_information5
1380 , l_rec.ple_information6
1381 , l_rec.ple_information7
1382 , l_rec.ple_information8
1383 , l_rec.ple_information9
1384 , l_rec.ple_information10
1385 , l_rec.ple_information11
1386 , l_rec.ple_information12
1387 , l_rec.ple_information13
1388 , l_rec.ple_information14
1389 , l_rec.ple_information15
1390 , l_rec.ple_information16
1391 , l_rec.ple_information17
1392 , l_rec.ple_information18
1393 , l_rec.ple_information19
1394 , l_rec.ple_information20
1395 , false );
1396 --
1397 fetch sel_price into l_rec;
1398 --
1399 End Loop;
1400 --
1401 close sel_price;
1402 --
1403 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1404 --
1405 End copy_price;
1406 --
1407 --******************************************************************************
1408 -- END OF MANUALLY WRITTEN SECTION
1409 --******************************************************************************
1410 -- ----------------------------------------------------------------------------
1411 -- |---------------------------< insert_validate >----------------------------|
1412 -- ----------------------------------------------------------------------------
1413 Procedure insert_validate(p_rec in ota_ple_shd.g_rec_type) is
1414 --
1415 l_proc varchar2(72) := g_package||'insert_validate';
1416 --
1417 Begin
1418 hr_utility.set_location('Entering:'||l_proc, 5);
1419 --
1420 -- Call all supporting business operations
1421 insert_validate2(p_rec);
1422 --
1423 --
1424 --
1425 hr_utility.set_location(' Leaving:'||l_proc, 10);
1426 End insert_validate;
1427 --
1428 -- ----------------------------------------------------------------------------
1429 -- |---------------------------< handle_leap_years >---------------------------
1430 -- ----------------------------------------------------------------------------
1431 Procedure handle_leap_years(p_start_date in out date,
1432 p_dates_difference number,
1433 p_difference number) is
1434 --
1435 l_proc varchar2(72) := g_package||'handle_leap_years';
1436 l_old_start_date date;
1437 l_days number;
1438 l_new_days number := 0;
1439 l_old_start_year number;
1440 l_old_leap_days number;
1441 --
1442 Begin
1443 hr_utility.set_location('Entering:'||l_proc, 5);
1444 --
1445 if p_dates_difference is not null then
1446 l_old_start_date := p_start_date;
1447 l_days := p_dates_difference;
1448 l_old_leap_days := p_difference;
1449 --
1450 -- we have to consider what to do for dates that have included a leap year.
1451 --
1452 l_old_start_year := to_number(to_char(l_old_start_date,'YYYY'));
1453 for counter in 1..l_days loop
1454 --
1455 -- Check if leap year
1456 --
1457 l_old_start_date := l_old_start_date + 1;
1458 l_old_start_year := to_number(to_char(l_old_start_date,'YYYY'));
1459 if mod(l_old_start_year,4) = 0 then
1460 --
1461 -- Check if day is 29th February. if so add a day to p_start_date
1462 --
1463 if to_char(l_old_start_date,'DD/MM') = '29/02' then
1464 if l_old_leap_days = 0 then
1465 p_start_date := p_start_date + 1;
1466 l_days := l_days + 1;
1467 else
1468 l_old_leap_days := l_old_leap_days - 1;
1469 end if;
1470 end if;
1471 end if;
1472 end loop;
1473 end if;
1474 if l_old_leap_days > 0 then
1475 p_start_date := p_start_date - l_old_leap_days;
1476 end if;
1477 hr_utility.set_location(' Leaving:'||l_proc, 10);
1478 End handle_leap_years;
1479 --
1480 -- ----------------------------------------------------------------------------
1481 -- |---------------------------< consider_leap_years >-------------------------
1482 -- ----------------------------------------------------------------------------
1483 Procedure consider_leap_years(p_old_tpl_id number,
1484 p_dates_difference in out number) is
1485 --
1486 l_proc varchar2(72) := g_package||'consider_leap_years';
1487 cursor c1 is
1488 select start_date
1489 from ota_price_lists
1490 where price_list_id = p_old_tpl_id;
1491 l_old_start_date date;
1492 l_days number := 0;
1493 Begin
1494 hr_utility.set_location('Entering:'||l_proc, 5);
1495 --
1496 open c1;
1497 fetch c1 into l_old_start_date;
1498 if c1%found then
1499 for counter in 1..p_dates_difference loop
1500 l_old_start_date := l_old_start_date + 1;
1501 --
1502 -- Check if day is 29th February. if so add a day to p_start_date
1503 --
1504 if to_char(l_old_start_date,'DD/MM') = '29/02' then
1505 l_days := l_days + 1;
1506 end if;
1507 end loop;
1508 end if;
1509 close c1;
1510 if l_days > 0 then
1511 p_dates_difference := l_days;
1512 else
1516 End consider_leap_years;
1513 p_dates_difference := 0;
1514 end if;
1515 hr_utility.set_location(' Leaving:'||l_proc, 10);
1517 --
1518 -- ----------------------------------------------------------------------------
1519 -- |---------------------------< update_validate >----------------------------|
1520 -- ----------------------------------------------------------------------------
1521 Procedure update_validate(p_rec in ota_ple_shd.g_rec_type) is
1522 --
1523 l_proc varchar2(72) := g_package||'update_validate';
1524 --
1525 Begin
1526 hr_utility.set_location('Entering:'||l_proc, 5);
1527 --
1528 -- Call all supporting business operations
1529 update_validate2(p_rec);
1530 --
1531 --
1532 --
1533 hr_utility.set_location(' Leaving:'||l_proc, 10);
1534 End update_validate;
1535 --
1536 -- ----------------------------------------------------------------------------
1537 -- |---------------------------< delete_validate >----------------------------|
1538 -- ----------------------------------------------------------------------------
1539 Procedure delete_validate(p_rec in ota_ple_shd.g_rec_type) is
1540 --
1541 l_proc varchar2(72) := g_package||'delete_validate';
1542 --
1543 Begin
1544 hr_utility.set_location('Entering:'||l_proc, 5);
1545 --
1546 -- Call all supporting business operations
1547 delete_validate2(p_rec);
1548 --
1549 hr_utility.set_location(' Leaving:'||l_proc, 10);
1550 End delete_validate;
1551 --
1552 end ota_ple_bus;