1 Package Body OTA_TBD_API as
2 /* $Header: ottbd01t.pkb 120.1 2006/05/08 02:52:13 niarora noship $ */
3 --
4 -- Private package current record structure definition
5 --
6 g_old_rec g_rec_type;
7 --
8 -- Global package name
9 --
10 g_package varchar2(33) := ' OTA_TBD_API.';
11 --
12 -- Global api dml status
13 --
14 g_api_dml boolean;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |---------------------tatus >-------------------------|
18 -- ----------------------------------------------------------------------------
19 Function return_api_dml_status Return Boolean Is
20 --
21 l_proc varchar2(72) := g_package||'return_api_dml_status';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 5);
25 --
26 Return (nvl(g_api_dml, false));
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 10);
29 End return_api_dml_status;
30 --
31 -- ----------------------------------------------------------------------------
32 -- |---------------------------< constraint_error >---------------------------|
33 -- ----------------------------------------------------------------------------
34 -- {Start Of Comments}
35 --
36 -- Description:
37 -- This procedure is called when a constraint has been violated (i.e.
38 -- The exception hr_api.check_integrity_violated,
39 -- hr_api.parent_integrity_violated or hr_api.child_integrity_violated has
40 -- been raised).
41 -- The exceptions can only be raised as follows:
42 -- 1) A check constraint can only be violated during an INSERT or UPDATE
43 -- dml operation.
44 -- 2) A parent integrity constraint can only be violated during an
45 -- INSERT or UPDATE dml operation.
46 -- 3) A child integrity constraint can only be violated during an
47 -- DELETE dml operation.
48 --
49 -- Pre Conditions:
50 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated
51 -- or hr_api.child_integrity_violated has been raised with the subsequent
52 -- stripping of the constraint name from the generated error message text.
53 --
54 -- In Arguments:
55 -- p_constraint_name is in upper format and is just the constraint name
56 -- (e.g. not prefixed by brackets, schema owner etc).
57 --
58 -- Post Success:
59 -- Development dependant.
60 --
61 -- Post Failure:
62 -- Developement dependant.
63 --
64 -- Developer Implementation Notes:
65 -- For each constraint being checked the hr system package failure message
66 -- has been generated as a template only. These system error messages should
67 -- be modified as required (i.e. change the system failure message to a user
68 -- friendly defined error message).
69 --
70 -- {End Of Comments}
71 -- ----------------------------------------------------------------------------
72 Procedure constraint_error
73 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
74 --
75 l_proc varchar2(72) := g_package||'constraint_error';
76 --
77 Begin
78 --
79 hr_utility.set_location('Entering:'||l_proc, 5);
80 --
81 -- Foreign keys
82 --
83 If (p_constraint_name = 'OTA_BOOKING_DEALS_FK1') Then
84 -- Business Group ID
85 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13467_TBD_INVALID_KEY');
86 FND_MESSAGE.SET_TOKEN ('STEP', '1');
87 ElsIf (p_constraint_name = 'OTA_BOOKING_DEALS_FK2') Then
88 -- Price List
89 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13467_TBD_INVALID_KEY');
90 FND_MESSAGE.SET_TOKEN ('STEP', '2');
91 ElsIf (p_constraint_name = 'OTA_BOOKING_DEALS_FK3') Then
92 -- Activity
93 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13467_TBD_INVALID_KEY');
94 FND_MESSAGE.SET_TOKEN ('STEP', '3');
95 ElsIf (p_constraint_name = 'OTA_BOOKING_DEALS_FK4') Then
96 -- Event
97 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13467_TBD_INVALID_KEY');
98 FND_MESSAGE.SET_TOKEN ('STEP', '4');
99 --
100 -- Primary key
101 --
102 ElsIf (p_constraint_name = 'OTA_BOOKING_DEALS_PK') Then
103 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
104 hr_utility.set_message_token('PROCEDURE', l_proc);
105 hr_utility.set_message_token('STEP','25');
106 hr_utility.raise_error;
107 --
108 -- Check constraints
109 --
110 elsif (P_CONSTRAINT_NAME = 'OTA_TBD_DATES_SEQ') then
111 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13312_GEN_DATE_ORDER');
112 ElsIf (p_constraint_name = 'OTA_TBD_CHECK_DISCOUNT_CONTEXT') Then
113 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13242_TBD_DISCOUNT');
114 ElsIf (p_constraint_name = 'OTA_TBD_CHECK_PREPURCH_CONTEXT') Then
115 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13241_TBD_PREPURCH');
116 elsif (p_constraint_name = 'OTA_TBD_EXCLUSIVE_CONTEXT') Then
117 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13231_TBD_EXCLUSIVE');
118 elsif (p_constraint_name = 'OTA_TBD_TYPE_CHK') Then
119 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13243_TBD_TYPE');
120 --
121 -- Other errors (see below).
122 --
123 elsif (P_CONSTRAINT_NAME = 'DUPLICATE_NAME') then
124 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13468_TBD_NOT_UNIQUE');
125 elsif (P_CONSTRAINT_NAME = 'INVALID_APPROVER') then
126 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13393_TBD_APPROVER');
127 elsif (P_CONSTRAINT_NAME = 'OTA_TBD_BUS_GROUPS') then
128 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13244_TBD_BUS_GROUP');
129 elsif (P_CONSTRAINT_NAME = 'OTA_TBD_DATES_TPL') Then
130 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13211_TBD_DATES_TPL');
131 elsif (p_constraint_name = 'OTA_TBD_DATES_EVT') Then
132 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13212_TBD_DATES_EVT');
133 elsif (p_constraint_name = 'OTA_TBD_DATES_TAV') Then
134 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13213_TBD_DATES_TAV');
135 elsif (P_CONSTRAINT_NAME = 'OTA_TBD_CATEGORY') then
136 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13245_TBD_CATEGORY');
137 elsif (P_CONSTRAINT_NAME = 'OTA_TBD_FINANCE_LINES') then
138 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13246_TBD_FINANCE_LINES');
139 elsif (P_CONSTRAINT_NAME = 'NON-TRANSFERABLE BASIS') then
140 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13220_TBD_NON_TRANSFER');
141 --
142 -- ?
143 --
144 Else
145 FND_MESSAGE.SET_NAME (801, 'HR_6153_ALL_PROCEDURE_FAIL');
146 FND_MESSAGE.SET_TOKEN ('PROCEDURE', l_proc);
147 FND_MESSAGE.SET_TOKEN ('STEP','35');
148 End If;
149 --
150 FND_MESSAGE.RAISE_ERROR;
151 --
152 hr_utility.set_location(' Leaving:'||l_proc, 10);
153 --
154 End constraint_error;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |--------------------------< g_old_rec_current >---------------------------|
158 -- ----------------------------------------------------------------------------
159 -- {Start Of Comments}
160 --
161 -- Description:
162 -- This function is used to populate the g_old_rec record with the current
163 -- row from the database for the specified primary key provided that the
164 -- primary key exists and is valid and does not already match the current
165 -- g_old_rec.
166 -- The function will always return a TRUE value if the g_old_rec is
167 -- populated with the current row. A FALSE value will be returned if any of
168 -- the primary key arguments are null.
169 --
170 -- Pre Conditions:
171 -- None.
172 --
173 -- In Arguments:
174 --
175 -- Post Success:
176 -- A value of TRUE will be returned indiciating that the g_old_rec is
177 -- current.
178 -- A value of FALSE will be returned if any of the primary key arguments
179 -- has a null value (this indicates that the row has not be inserted into
180 -- the Schema), and therefore could never have a corresponding row.
181 --
182 -- Post Failure:
183 -- A failure can only occur under two circumstances:
184 -- 1) The primary key is invalid (i.e. a row does not exist for the
185 -- specified primary key values).
186 -- 2) If an object_version_number exists but is NOT the same as the current
187 -- g_old_rec value.
188 --
189 -- Developer Implementation Notes:
190 -- None.
191 --
192 -- {End Of Comments}
193 -- ----------------------------------------------------------------------------
194 Function g_old_rec_current
195 (
196 p_booking_deal_id in number,
197 p_object_version_number in number
198 ) Return Boolean Is
199 --
200 --
201 -- Cursor selects the 'current' row from the HR Schema
202 --
203 Cursor C_Sel1 is
204 select
205 booking_deal_id,
206 customer_id,
207 approved_by_person_id,
208 business_group_id,
209 name,
210 object_version_number,
211 start_date,
212 category,
213 comments,
214 description,
215 discount_percentage,
216 end_date,
217 number_of_places,
218 LIMIT_EACH_EVENT_FLAG,
219 overdraft_limit,
220 type,
221 price_list_id,
222 activity_version_id,
223 event_id,
224 tbd_information_category,
225 tbd_information1,
226 tbd_information2,
227 tbd_information3,
228 tbd_information4,
229 tbd_information5,
230 tbd_information6,
231 tbd_information7,
232 tbd_information8,
233 tbd_information9,
234 tbd_information10,
235 tbd_information11,
236 tbd_information12,
237 tbd_information13,
238 tbd_information14,
239 tbd_information15,
240 tbd_information16,
241 tbd_information17,
242 tbd_information18,
243 tbd_information19,
244 tbd_information20
245 from ota_booking_deals
246 where booking_deal_id = p_booking_deal_id;
247 --
248 l_proc varchar2(72) := g_package||'g_old_rec_current';
249 l_fct_ret boolean;
250 --
251 Begin
252 hr_utility.set_location('Entering:'||l_proc, 5);
253 --
254 If (
255 p_booking_deal_id is null or
256 p_object_version_number is null
257 ) Then
258 --
259 -- One of the primary key arguments is null therefore we must
260 -- set the returning function value to false
261 --
262 l_fct_ret := false;
263 Else
264 If (
265 p_booking_deal_id = g_old_rec.booking_deal_id and
266 p_object_version_number = g_old_rec.object_version_number
267 ) Then
268 hr_utility.set_location(l_proc, 10);
269 --
270 -- The g_old_rec is current therefore we must
271 -- set the returning function to true
272 --
273 l_fct_ret := true;
274 Else
275 --
276 -- Select the current row
277 --
278 Open C_Sel1;
279 Fetch C_Sel1 Into g_old_rec;
280 If C_Sel1%notfound Then
281 Close C_Sel1;
282 --
283 -- The primary key is invalid therefore we must error
284 --
285 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
286 hr_utility.raise_error;
287 End If;
288 Close C_Sel1;
289 If (p_object_version_number <> g_old_rec.object_version_number) Then
290 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
291 hr_utility.raise_error;
292 End If;
293 hr_utility.set_location(l_proc, 15);
294 l_fct_ret := true;
295 End If;
296 End If;
297 hr_utility.set_location(' Leaving:'||l_proc, 20);
298 Return (l_fct_ret);
299 --
300 End g_old_rec_current;
301 --
302 --
303 -- ----------------------------------------------------------------------------
304 -- --------------------------------< CHECK_DATES_CONFLICT >--------------------
305 -- ----------------------------------------------------------------------------
306 --
307 -- Checks if booking deal dates have been changed that they do not
308 -- validate any student enrollments.
309 --
310 procedure check_dates_conflict(p_booking_deal_id in number,
311 p_start_date in date,
312 p_end_date in date) is
313 --
314 l_before number;
315 l_after number;
316 l_proc varchar2(72) := g_package||'check_dates_conflict';
317 --
318 cursor c_check_enrollments is
319 select nvl(count(a.booking_deal_id),0)
320 from ota_finance_lines a
321 where p_booking_deal_id = booking_deal_id
322 and cancelled_flag = 'N';
323 --
324 -- Cursor to check how many records will come back if date changes
325 -- applied
326 --
327 cursor c_check_date_change is
328 select nvl(count(a.booking_deal_id),0)
329 from ota_finance_lines a,
330 ota_delegate_bookings b,
331 ota_events c
332 where a.booking_deal_id = p_booking_deal_id
333 and a.booking_id = b.booking_id
334 and b.event_id = c.event_id
335 and p_start_date <= nvl(c.course_start_date,p_start_date)
336 and nvl(p_end_date,hr_api.g_eot)
337 >= nvl(c.course_start_date,nvl(p_end_date,hr_api.g_eot))
338 and a.cancelled_flag = 'N';
339 --
340 begin
341 --
342 hr_utility.set_location('Entering:'||l_proc, 10);
343 hr_utility.set_location('Booking Deal ID '||p_booking_deal_id,10);
344 hr_utility.set_location('Start Date'||p_start_date,10);
345 hr_utility.set_location('End Date'||p_end_date,10);
346 --
347 -- Get number of enrollments currently using booking deal
348 --
349 open c_check_enrollments;
350 --
351 fetch c_check_enrollments into l_before;
352 --
353 close c_check_enrollments;
354 --
355 -- Get number of enrollments that will be unaffected by new booking deal
356 --
357 open c_check_date_change;
358 --
359 fetch c_check_date_change into l_after;
360 --
361 close c_check_date_change;
362 --
363 if l_after <> l_before then
364 --
365 -- Change results in some bookings becoming invalid
366 --
367 fnd_message.set_name('OTA','OTA_13596_INVALID_ENROLLMENTS');
368 fnd_message.raise_error;
369 --
370 end if;
371 --
372 hr_utility.set_location('Leaving:'||l_proc,10);
373 --
374 end check_dates_conflict;
375 -- ----------------------------------------------------------------------------
376 -- -----------------------------< SUM_AMOUNT >------------------------------
377 -- ----------------------------------------------------------------------------
378 function SUM_AMOUNT
379 (P_BOOKING_DEAL_ID in number,
380 P_TYPE in varchar2,
381 P_LINE_TYPE in varchar2
382 ) return number is
383 --
384 W_UNITS_PURCHASED number;
385 W_MONEY_PURCHASED number;
386 --
387 cursor C1 is
388 select sum (TFL.UNITARY_AMOUNT),
389 sum (TFL.MONEY_AMOUNT)
390 from OTA_FINANCE_LINES TFL
391 where TFL.BOOKING_DEAL_ID (+) = P_BOOKING_DEAL_ID
392 and ( (TFL.CANCELLED_FLAG is null)
393 or (TFL.CANCELLED_FLAG = 'N' ))
394 and TFL.LINE_TYPE = P_LINE_TYPE;
395 --
396 begin
397 --
398 open C1;
399 fetch C1
400 into W_UNITS_PURCHASED,
401 W_MONEY_PURCHASED;
402 close C1;
403 --
404 if P_TYPE = 'T' then
405 return (W_UNITS_PURCHASED);
406 elsif P_TYPE = 'M' then
407 return (W_MONEY_PURCHASED);
408 end if;
409 end SUM_AMOUNT;
410 -- ----------------------------------------------------------------------------
411 -- -----------------------------< TFL_PURCHASED >------------------------------
412 -- ----------------------------------------------------------------------------
413 --
414 -- Function returns the purchased amount from OTA_FINANCE_LINES for the
415 -- BOOKING_DEAL_ID supplied in uniTs or Money as requested.
416 --
417 function TFL_PURCHASED (
418 P_BOOKING_DEAL_ID in number,
419 P_TYPE in varchar2
420 ) return number is
421 --
422 begin
423 --
424 return (sum_amount(P_BOOKING_DEAL_ID
425 ,P_TYPE
426 ,'P'));
427 --
428 end TFL_PURCHASED;
429 --
430 -- ----------------------------------------------------------------------------
431 -- -----------------------------< TFL_BALANCE >--------------------------------
432 -- ----------------------------------------------------------------------------
433 --
434 -- Function returns the outstanding balance from OTA_FINANCE_LINES for the
435 -- BOOKING_DEAL_ID supplied in uniTs or Money as requested.
436 --
437 function TFL_BALANCE (
438 P_BOOKING_DEAL_ID in number,
439 P_TYPE in varchar2
440 ) return number is
441 --
442 begin
443 --
444 return (nvl(sum_amount(P_BOOKING_DEAL_ID
445 ,P_TYPE
446 ,'P'),0)
447 - nvl(sum_amount(P_BOOKING_DEAL_ID
448 ,P_TYPE
449 ,'E'),0)
450 );
451 --
452 end TFL_BALANCE;
453 --
454 -- ----------------------------------------------------------------------------
455 -- -------------------------------< TFL_FLAG >---------------------------------
456 -- ----------------------------------------------------------------------------
457 --
458 -- Returns 'TRUE' if OTA_FINANCE_LINES exist for the BOOKING_DEAL_ID,
459 -- or null if not present.
460 --
461 function TFL_FLAG (
462 P_BOOKING_DEAL_ID in number
463 ) return varchar2 is
464 --
465 W_FLAG varchar2 (4);
466 --
467 cursor C1 is
468 select 'TRUE'
469 from OTA_FINANCE_LINES TFL
470 where TFL.BOOKING_DEAL_ID (+) = P_BOOKING_DEAL_ID
471 and ( (TFL.CANCELLED_FLAG is null)
472 or (TFL.CANCELLED_FLAG = 'N' ));
473 --
474 begin
475 --
476 open C1;
477 fetch C1
478 into W_FLAG;
479 if (C1%notfound) then
480 W_FLAG := null;
481 end if;
482 close C1;
483 --
484 return (W_FLAG);
485 --
486 end TFL_FLAG;
487 --
488 -- ----------------------------------------------------------------------------
489 -- -----------------------------< CHECK_UNIQUE_NAME >--------------------------
490 -- ----------------------------------------------------------------------------
491 --
492 -- Just what the name would imply.
493 -- 07/11/95 - Changed to ensure that booking deals are unique within
494 -- Customer. If the Customer_id is null then not Customer can
495 -- use the Deal Name.
496 --
497 procedure CHECK_UNIQUE_NAME (
498 P_BUSINESS_GROUP_ID in number,
499 P_BOOKING_DEAL_ID in number,
500 P_NAME in varchar2,
501 P_CUSTOMER_ID in varchar2
502 ) is
503 --
504 W_FLAG number (1);
505 W_PROCEDURE varchar2 (72)
506 := G_PACKAGE || 'CHECK_UNIQUE_NAME';
507 W_UNIQUE boolean;
508 --
509 cursor C1 is
510 select 1
511 from OTA_BOOKING_DEALS TBD
512 where TBD.BUSINESS_GROUP_ID+0 = P_BUSINESS_GROUP_ID
513 and upper (TBD.NAME) = upper (P_NAME)
514 and (p_customer_id is not null and
515 (customer_id is null or
516 (customer_id is not null and
517 customer_id = p_customer_id))
518 or (p_customer_id is null))
519 and ( (P_BOOKING_DEAL_ID is null )
520 or (TBD.BOOKING_DEAL_ID <> P_BOOKING_DEAL_ID));
521 --
522 begin
523 --
524 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
525 --
526 open C1;
527 fetch C1
528 into W_FLAG;
529 if (C1%found) then
530 CONSTRAINT_ERROR ('DUPLICATE_NAME');
531 end if;
532 close C1;
533 --
534 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 99);
535 --
536 end CHECK_UNIQUE_NAME;
537 --
538 -- ----------------------------------------------------------------------------
539 -- --------------------------------< CHECK_APPROVER >--------------------------
540 -- ----------------------------------------------------------------------------
541 --
542 -- The person approving the booking deal must be an employee when the
543 -- deal first becomes valid.
544 --
545 procedure CHECK_APPROVER ( P_APPROVED_BY_PERSON_ID in number) is
546 --
547 W_FLAG number (1);
548 W_PROCEDURE varchar2 (72)
549 := G_PACKAGE || 'CHECK_APPROVER';
550 W_VALID boolean;
551 --
552 begin
553 --
554 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
555 --
556 if (P_APPROVED_BY_PERSON_ID is not null) then
557 if not ota_general.check_fnd_user(p_approved_by_person_id) then
558 fnd_message.set_name ('OTA', 'OTA_13281_TFH_AUTHORIZER');
559 fnd_message.raise_error;
560 end if;
561
562 end if;
563 --
564 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 99);
565 --
566 end CHECK_APPROVER;
567 --
568 -- ----------------------------------------------------------------------------
569 -- -------------------------------< CHECK_PREPURCH_CONTEXT >-------------------
570 -- ----------------------------------------------------------------------------
571 --
572 -- If the TYPE is 'P' (Pre-purchase) then the NUMBER_OF_PLACES must be
573 -- null and the PRICE_LIST_ID must be populated.
574 --
575 procedure CHECK_PREPURCH_CONTEXT (
576 P_NUMBER_OF_PLACES in number,
577 P_LIMIT_EACH_EVENT_FLAG in varchar2,
578 P_PRICE_LIST_ID in number
579 ) is
580 --
581 W_PROCEDURE varchar2 (72)
582 := G_PACKAGE || 'CHECK_PREPURCH_CONTEXT';
583 --
584 function VALID_PREPURCH_CONTEXT (
585 P_NUMBER_OF_PLACES in number,
586 P_LIMIT_EACH_EVENT_FLAG in varchar2,
587 P_PRICE_LIST_ID in number
588 ) return boolean is
589 begin
590 --
591 if ( (P_NUMBER_OF_PLACES is null )
592 and (P_LIMIT_EACH_EVENT_FLAG is null )
593 and (P_PRICE_LIST_ID is not null)) then
594 return (true);
595 else
596 return (false);
597 end if;
598 --
599 end VALID_PREPURCH_CONTEXT;
600 --
601 begin
602 --
603 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
604 --
605 if (not VALID_PREPURCH_CONTEXT (
606 P_NUMBER_OF_PLACES => P_NUMBER_OF_PLACES,
607 P_LIMIT_EACH_EVENT_FLAG => P_LIMIT_EACH_EVENT_FLAG,
608 P_PRICE_LIST_ID => P_PRICE_LIST_ID)) then
609 CONSTRAINT_ERROR ('OTA_TBD_CHECK_PREPURCH_CONTEXT');
610 end if;
611 --
612 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
613 --
614 end CHECK_PREPURCH_CONTEXT;
615 --
616 -- ----------------------------------------------------------------------------
617 -- -------------------------------< CHECK_DISCOUNT_CONTEXT >-------------------
618 -- ----------------------------------------------------------------------------
619 --
620 -- If the TYPE is 'D' (Discount) then the DISCOUNT_PERCENTAGE must be
621 -- populated and the OVERDRAFT_LIMIT must be null.
622 --
623 procedure CHECK_DISCOUNT_CONTEXT (
624 P_DISCOUNT_PERCENTAGE in number,
625 P_OVERDRAFT_LIMIT in number
626 ) is
627 --
628 W_PROCEDURE varchar2 (72)
629 := G_PACKAGE || 'CHECK_DISCOUNT_CONTEXT';
630 --
631 function VALID_DISCOUNT_CONTEXT (
632 P_DISCOUNT_PERCENTAGE in number,
633 P_OVERDRAFT_LIMIT in number
634 ) return boolean is
635 begin
636 --
637 if ((P_DISCOUNT_PERCENTAGE is not null) and
638 (P_OVERDRAFT_LIMIT is null)) then
639 return (true);
640 else
641 return (false);
642 end if;
643 --
644 end VALID_DISCOUNT_CONTEXT;
645 --
646 begin
647 --
648 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
649 --
650 if (not VALID_DISCOUNT_CONTEXT (
651 P_DISCOUNT_PERCENTAGE => P_DISCOUNT_PERCENTAGE,
652 P_OVERDRAFT_LIMIT => P_OVERDRAFT_LIMIT)) then
653 CONSTRAINT_ERROR ('OTA_TBD_CHECK_DISCOUNT_CONTEXT');
654 end if;
655 --
656 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
657 --
658 end CHECK_DISCOUNT_CONTEXT;
659 --
660 -- ----------------------------------------------------------------------------
661 -- -------------------------------< CHECK_DATES_TPL >--------------------------
662 -- ----------------------------------------------------------------------------
663 --
664 -- The start and end dates must be within the boundaries of the start and
665 -- end dates defined on the price list.
666 --
667 procedure CHECK_DATES_TPL (
668 P_PRICE_LIST_ID in number,
669 P_BUSINESS_GROUP_ID in number,
670 P_START_DATE in date,
671 P_END_DATE in date
672 ) is
673 --
674 W_PROCEDURE varchar2 (72)
675 := G_PACKAGE || 'CHECK_DATES_TPL';
676 --
677 W_BUSINESS_GROUP_ID number (9);
678 W_TPL_START_DATE date;
679 W_TPL_END_DATE date;
680 --
681 cursor C1 is
682 select TPL.BUSINESS_GROUP_ID,
683 nvl (TPL.START_DATE, hr_api.g_sot),
684 nvl (TPL.END_DATE, hr_api.g_eot)
685 from OTA_PRICE_LISTS TPL
686 where TPL.PRICE_LIST_ID = P_PRICE_LIST_ID;
687 --
688 begin
689 --
690 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
691 --
692 open C1;
693 fetch C1
694 into W_BUSINESS_GROUP_ID,
695 W_TPL_START_DATE,
696 W_TPL_END_DATE;
697 if (C1%notfound) then
698 close C1;
699 CONSTRAINT_ERROR ('OTA_BOOKING_DEALS_FK3');
700 end if;
701 close C1;
702 --
703 if (W_BUSINESS_GROUP_ID <> P_BUSINESS_GROUP_ID) then
704 CONSTRAINT_ERROR ('OTA_TBD_BUS_GROUPS');
705 end if;
706 --
707 if ( (P_START_DATE not between W_TPL_START_DATE
708 and W_TPL_END_DATE )
709 or (nvl (P_END_DATE, P_START_DATE)
710 not between W_TPL_START_DATE
711 and W_TPL_END_DATE )) then
712 CONSTRAINT_ERROR ('OTA_TBD_DATES_TPL');
713 end if;
714 --
715 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
716 --
717 end CHECK_DATES_TPL;
718 --
719 -- ----------------------------------------------------------------------------
720 -- -------------------------------< CHECK_EVENT >------------------------------
721 -- ----------------------------------------------------------------------------
722 --
723 -- The event BUSINESS_GROUP_ID must be the same as the booking deals.
724 --
725 -- The event type must be 'PROGRAMME' or 'SCHEDULED'.
726 --
727 -- The start and end dates must be within the boundaries of the course
728 -- start and end dates defined on the event.
729 --
730 procedure CHECK_EVENT (
731 P_EVENT_ID in number,
732 P_BUSINESS_GROUP_ID in number,
733 P_START_DATE in date,
734 P_END_DATE in date,
735 P_NUMBER_OF_PLACES in number
736 ) is
737 --
738 W_PROCEDURE varchar2 (72)
739 := G_PACKAGE || 'CHECK_EVENT';
740 --
741 W_BUSINESS_GROUP_ID number (9);
742 W_EVENT_TYPE varchar2 (30);
743 W_EVT_START_DATE date;
744 W_EVT_END_DATE date;
745 W_MAX_ATTENDEES number;
746 W_PRICE_BASIS varchar2(30);
747 --
748 cursor C1 is
749 select EVT.BUSINESS_GROUP_ID,
750 EVT.EVENT_TYPE,
751 nvl (EVT.COURSE_START_DATE,hr_api.g_sot),
752 nvl (EVT.COURSE_END_DATE ,hr_api.g_eot),
753 maximum_attendees,
754 price_basis
755 from OTA_EVENTS EVT
756 where EVT.EVENT_ID = P_EVENT_ID;
757 --
758 begin
759 --
760 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
761 --
762 -- Get event details
763 --
764 open C1;
765 fetch C1
766 into W_BUSINESS_GROUP_ID,
767 W_EVENT_TYPE,
768 W_EVT_START_DATE,
769 W_EVT_END_DATE,
770 W_MAX_ATTENDEES,
771 W_PRICE_BASIS;
772 if (C1%notfound) then
773 close C1;
774 CONSTRAINT_ERROR ('OTA_BOOKING_DEALS_FK5');
775 end if;
776 close C1;
777 --
778 -- Business group
779 --
780 if (W_BUSINESS_GROUP_ID <> P_BUSINESS_GROUP_ID) then
781 CONSTRAINT_ERROR ('OTA_TBD_BUS_GROUPS');
782 end if;
783 --
784 -- Event type
785 --
786 if (W_EVENT_TYPE not in ('PROGRAMME', 'SCHEDULED')) then
787 CONSTRAINT_ERROR ('OTA_TBS_EVENT_TYPE');
788 end if;
789 --
790 -- Dates within course dates
791 --
792 if ( (P_START_DATE not between W_EVT_START_DATE
793 and W_EVT_END_DATE )
794 or (nvl (P_END_DATE, P_START_DATE)
795 not between W_EVT_START_DATE
796 and W_EVT_END_DATE )) then
797 CONSTRAINT_ERROR ('OTA_TBD_DATES_EVT');
798 end if;
799 --
800 if p_number_of_places is not null and
801 w_max_attendees is not null and
802 p_number_of_places > w_max_attendees then
803 fnd_message.set_name('OTA','OTA_13496_TBD_PLACES_GT_EVENT');
804 fnd_message.raise_error;
805 end if;
806 --
807 if w_price_basis not in ('C','S') then
808 fnd_message.set_name('OTA','OTA_13497_TBD_PRICE_BASIS');
809 fnd_message.raise_error;
810 end if;
811 --
812 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
813 --
814 end CHECK_EVENT;
815 --
816 -- ----------------------------------------------------------------------------
817 -- -------------------------------< CHECK_DATES_TAV >--------------------------
818 -- ----------------------------------------------------------------------------
819 --
820 -- The start and end dates must be within the boundaries of the start and
821 -- end dates defined on the activity version.
822 --
823 procedure CHECK_DATES_TAV (
824 P_ACTIVITY_VERSION_ID in number,
825 P_START_DATE in date,
826 P_END_DATE in date
827 ) is
828 --
829 W_PROCEDURE varchar2 (72)
830 := G_PACKAGE || 'CHECK_DATES_TAV';
831 --
832 W_TAV_START_DATE date;
833 W_TAV_END_DATE date;
834 --
835 cursor C1 is
836 select nvl (TAV.START_DATE, hr_api.g_sot),
837 nvl (TAV.END_DATE, hr_api.g_eot)
838 from OTA_ACTIVITY_VERSIONS TAV
839 where TAV.ACTIVITY_VERSION_ID = P_ACTIVITY_VERSION_ID;
840 --
841 begin
842 --
843 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
844 --
845 open C1;
846 fetch C1
847 into W_TAV_START_DATE,
848 W_TAV_END_DATE;
849 if (C1%notfound) then
850 close C1;
851 CONSTRAINT_ERROR ('OTA_BOOKING_DEALS_FK4');
852 end if;
853 close C1;
854 --
855 if ( (P_START_DATE not between W_TAV_START_DATE
856 and W_TAV_END_DATE )
857 or (nvl (P_END_DATE, P_START_DATE)
858 not between W_TAV_START_DATE
859 and W_TAV_END_DATE )) then
860 CONSTRAINT_ERROR ('OTA_TBD_DATES_TAV');
861 end if;
862 --
863 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
864 --
865 end;
866 --
867 -- ----------------------------------------------------------------------------
868 -- -----------------------------< CHECK_CATEGORY >-----------------------------
869 -- ----------------------------------------------------------------------------
870 --
871 -- The category must be in the domain 'CATEGORY' and have an
872 -- OTA_CATEGORY_USAGES row of TYPE 'P'(ackage).
873 --
874 procedure CHECK_CATEGORY (
875 P_BUSINESS_GROUP_ID in number,
876 P_CATEGORY in varchar2
877 ) is
878 --
879 W_FLAG number (1);
880 W_PROCEDURE varchar2 (72)
881 := G_PACKAGE || 'CHECK_CATEGORY';
882 W_VALID boolean;
883 --
884 cursor C1 is
885 select 1
886 from OTA_CATEGORY_USAGES TCU
887 where TCU.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
888 and TCU.CATEGORY_usage_id = P_CATEGORY
889 and TCU.TYPE = 'D';
890 --
891 begin
892 --
893 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
894 --
895 /*OTA_GENERAL.CHECK_DOMAIN_VALUE (
896 P_DOMAIN_TYPE => 'ACTIVITY_CATEGORY',
897 P_DOMAIN_VALUE => P_CATEGORY);*/
898 --
899 open C1;
900 fetch C1
901 into W_FLAG;
902 W_VALID := C1%found;
903 close C1;
904 --
905 if (not W_VALID) then
906 CONSTRAINT_ERROR ('OTA_TBD_CATEGORY');
907 end if;
908 --
909 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
910 --
911 end CHECK_CATEGORY;
912 --
913 -- ----------------------------------------------------------------------------
914 -- -------------------------------< VALIDITY_CHECK >---------------------------
915 -- ----------------------------------------------------------------------------
916 --
917 -- Apply the above checks.
918 --
919 procedure VALIDITY_CHECK (
920 P_REC in G_REC_TYPE
921 ) is
922 --
923 W_PROCEDURE varchar2 (72)
924 := G_PACKAGE || 'VALIDITY_CHECK';
925 --
926 begin
927 --
928 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
929 --
930 -- Name must be unique to business group
931 --
932 CHECK_UNIQUE_NAME (
933 P_BUSINESS_GROUP_ID => P_REC.BUSINESS_GROUP_ID,
934 P_BOOKING_DEAL_ID => P_REC.BOOKING_DEAL_ID,
935 P_NAME => P_REC.NAME,
936 p_customer_id => p_rec.customer_id);
937 --
938 -- Approved by
939 --
940 CHECK_APPROVER (
941 P_APPROVED_BY_PERSON_ID => P_REC.APPROVED_BY_PERSON_ID);
942 --
943 -- Booking deal TYPE
944 --
945 if (P_REC.TYPE = 'P') then
946 CHECK_PREPURCH_CONTEXT (
947 P_NUMBER_OF_PLACES => P_REC.NUMBER_OF_PLACES,
948 P_LIMIT_EACH_EVENT_FLAG => P_REC.LIMIT_EACH_EVENT_FLAG,
949 P_PRICE_LIST_ID => P_REC.PRICE_LIST_ID);
950 elsif (P_REC.TYPE = 'D') then
951 CHECK_DISCOUNT_CONTEXT (
952 P_DISCOUNT_PERCENTAGE => P_REC.DISCOUNT_PERCENTAGE,
953 P_OVERDRAFT_LIMIT => P_REC.OVERDRAFT_LIMIT);
954 else
955 CONSTRAINT_ERROR ('OTA_TBD_TYPE_CHK');
956 end if;
957 --
958 -- Exclusivity
959 --
960 if (P_REC.PRICE_LIST_ID is not null) then
961 --
962 -- PRICE_LIST_ID
963 --
964 if ( (P_REC.EVENT_ID is not null)
965 or (P_REC.ACTIVITY_VERSION_ID is not null)
966 or (P_REC.CATEGORY is not null)) then
967 CONSTRAINT_ERROR ('OTA_TBD_EXCLUSIVE_CONTEXT');
968 end if;
969 --
970 CHECK_DATES_TPL (
971 P_PRICE_LIST_ID => P_REC.PRICE_LIST_ID,
972 P_BUSINESS_GROUP_ID => P_REC.BUSINESS_GROUP_ID,
973 P_START_DATE => P_REC.START_DATE,
974 P_END_DATE => P_REC.END_DATE);
975 --
976 elsif (P_REC.EVENT_ID is not null) then
977 --
978 -- EVENT_ID
979 --
980 if ((P_REC.ACTIVITY_VERSION_ID is not null) or
981 (P_REC.CATEGORY is not null)) then
982 CONSTRAINT_ERROR ('OTA_TBD_EXCLUSIVE_CONTEXT');
983 end if;
984 --
985 CHECK_EVENT (
986 P_EVENT_ID => P_REC.EVENT_ID,
987 P_BUSINESS_GROUP_ID => P_REC.BUSINESS_GROUP_ID,
988 P_START_DATE => P_REC.START_DATE,
989 P_END_DATE => P_REC.END_DATE,
990 p_number_of_places => p_rec.number_of_places);
991 --
992 elsif (P_REC.ACTIVITY_VERSION_ID is not null) then
993 --
994 -- ACTIVITY_VERSION_ID
995 --
996 if (P_REC.CATEGORY is not null) then
997 CONSTRAINT_ERROR ('OTA_TBD_EXCLUSIVE_CONTEXT');
998 end if;
999 --
1000 CHECK_DATES_TAV (
1001 P_ACTIVITY_VERSION_ID => P_REC.ACTIVITY_VERSION_ID,
1002 P_START_DATE => P_REC.START_DATE,
1003 P_END_DATE => P_REC.END_DATE);
1004 --
1005 elsif (P_REC.CATEGORY is not null) then
1006 --
1007 -- Category
1008 --
1009 CHECK_CATEGORY (
1010 P_BUSINESS_GROUP_ID => P_REC.BUSINESS_GROUP_ID,
1011 P_CATEGORY => P_REC.CATEGORY);
1012 --
1013 else
1014 --
1015 -- Must pick one
1016 --
1017 CONSTRAINT_ERROR ('OTA_TBD_EXCLUSIVE_CONTEXT');
1018 --
1019 end if;
1020 --
1021 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
1022 --
1023 end;
1024 --
1025 -- ----------------------------------------------------------------------------
1026 -- ---------------------------------< TFL_LINES >------------------------------
1027 -- ----------------------------------------------------------------------------
1028 --
1029 function TFL_LINES (
1030 P_BOOKING_DEAL_ID in number
1031 ) return boolean is
1032 --
1033 W_PROCEDURE varchar2 (72)
1034 := G_PACKAGE || 'TFL_LINES';
1035 --
1036 W_LINES varchar2 (3);
1037 --
1038 cursor C1 is
1039 select 'YES'
1040 from OTA_FINANCE_LINES TFL
1041 where TFL.BOOKING_DEAL_ID = P_BOOKING_DEAL_ID;
1042 --
1043 begin
1044 --
1045 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
1046 --
1047 open C1;
1048 fetch C1
1049 into W_LINES;
1050 if (C1%notfound) then
1051 W_LINES := 'NO';
1052 end if;
1053 close C1;
1054 --
1055 if (W_LINES = 'YES') then
1056 return (true);
1057 else
1058 return (false);
1059 end if;
1060 --
1061 end TFL_LINES;
1062 --
1063 -- ----------------------------------------------------------------------------
1064 -- |------------------------------< insert_dml >------------------------------|
1065 -- ----------------------------------------------------------------------------
1066 -- {Start Of Comments}
1067 --
1068 -- Description:
1069 -- This procedure controls the actual dml insert logic. The functions of this
1070 -- procedure are as follows:
1071 -- 1) Initialise the object_version_number to 1 if the object_version_number
1072 -- is defined as an attribute for this entity.
1073 -- 2) To set and unset the g_api_dml status as required (as we are about to
1074 -- perform dml).
1075 -- 3) To insert the row into the schema.
1076 -- 4) To trap any constraint violations that may have occurred.
1077 -- 5) To raise any other errors.
1078 --
1079 -- Pre Conditions:
1080 -- This is an internal private procedure which must be called from the ins
1081 -- procedure and must have all mandatory arguments set (except the
1082 -- object_version_number which is initialised within this procedure).
1083 --
1084 -- In Arguments:
1085 -- A Pl/Sql record structre.
1086 --
1087 -- Post Success:
1088 -- The specified row will be inserted into the schema.
1089 --
1090 -- Post Failure:
1091 -- On the insert dml failure it is important to note that we always reset the
1092 -- g_api_dml status to false.
1093 -- If a check, unique or parent integrity constraint violation is raised the
1094 -- constraint_error procedure will be called.
1095 -- If any other error is reported, the error will be raised after the
1096 -- g_api_dml status is reset.
1097 --
1098 -- Developer Implementation Notes:
1099 -- None.
1100 --
1101 -- {End Of Comments}
1102 -- ----------------------------------------------------------------------------
1103 Procedure insert_dml(p_rec in out nocopy g_rec_type) is
1104 --
1105 l_proc varchar2(72) := g_package||'insert_dml';
1106 --
1107 Begin
1108 hr_utility.set_location('Entering:'||l_proc, 5);
1109 p_rec.object_version_number := 1; -- Initialise the object version
1110 --
1111 g_api_dml := true; -- Set the api dml status
1112 --
1113 -- Insert the row into: ota_booking_deals
1114 --
1115 insert into ota_booking_deals
1116 ( booking_deal_id,
1117 customer_id,
1118 approved_by_person_id,
1119 business_group_id,
1120 name,
1121 object_version_number,
1122 start_date,
1123 category,
1124 comments,
1125 description,
1126 discount_percentage,
1127 end_date,
1128 number_of_places,
1129 LIMIT_EACH_EVENT_FLAG,
1130 overdraft_limit,
1131 type,
1132 price_list_id,
1133 activity_version_id,
1134 event_id,
1135 tbd_information_category,
1136 tbd_information1,
1137 tbd_information2,
1138 tbd_information3,
1139 tbd_information4,
1140 tbd_information5,
1141 tbd_information6,
1142 tbd_information7,
1143 tbd_information8,
1144 tbd_information9,
1145 tbd_information10,
1146 tbd_information11,
1147 tbd_information12,
1148 tbd_information13,
1149 tbd_information14,
1150 tbd_information15,
1151 tbd_information16,
1152 tbd_information17,
1153 tbd_information18,
1154 tbd_information19,
1155 tbd_information20
1156 )
1157 Values
1158 ( p_rec.booking_deal_id,
1159 p_rec.customer_id,
1160 p_rec.approved_by_person_id,
1161 p_rec.business_group_id,
1162 p_rec.name,
1163 p_rec.object_version_number,
1164 p_rec.start_date,
1165 p_rec.category,
1166 p_rec.comments,
1167 p_rec.description,
1168 p_rec.discount_percentage,
1169 p_rec.end_date,
1170 p_rec.number_of_places,
1171 P_REC.LIMIT_EACH_EVENT_FLAG,
1172 p_rec.overdraft_limit,
1173 p_rec.type,
1174 p_rec.price_list_id,
1175 p_rec.activity_version_id,
1176 p_rec.event_id,
1177 p_rec.tbd_information_category,
1178 p_rec.tbd_information1,
1179 p_rec.tbd_information2,
1180 p_rec.tbd_information3,
1181 p_rec.tbd_information4,
1182 p_rec.tbd_information5,
1183 p_rec.tbd_information6,
1184 p_rec.tbd_information7,
1185 p_rec.tbd_information8,
1186 p_rec.tbd_information9,
1187 p_rec.tbd_information10,
1188 p_rec.tbd_information11,
1189 p_rec.tbd_information12,
1190 p_rec.tbd_information13,
1191 p_rec.tbd_information14,
1192 p_rec.tbd_information15,
1193 p_rec.tbd_information16,
1194 p_rec.tbd_information17,
1195 p_rec.tbd_information18,
1196 p_rec.tbd_information19,
1197 p_rec.tbd_information20
1198 );
1199 --
1200 g_api_dml := false; -- Unset the api dml status
1201 --
1202 hr_utility.set_location(' Leaving:'||l_proc, 10);
1203 Exception
1204 When hr_api.check_integrity_violated Then
1205 -- A check constraint has been violated
1206 g_api_dml := false; -- Unset the api dml status
1207 constraint_error
1208 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1209 When hr_api.parent_integrity_violated Then
1210 -- Parent integrity has been violated
1211 g_api_dml := false; -- Unset the api dml status
1212 constraint_error
1213 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1214 When hr_api.unique_integrity_violated Then
1215 -- Unique integrity has been violated
1216 g_api_dml := false; -- Unset the api dml status
1217 constraint_error
1218 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1219 When Others Then
1220 g_api_dml := false; -- Unset the api dml status
1221 Raise;
1222 End insert_dml;
1223 --
1224 -- ----------------------------------------------------------------------------
1225 -- |------------------------------< update_dml >------------------------------|
1226 -- ----------------------------------------------------------------------------
1227 -- {Start Of Comments}
1228 --
1229 -- Description:
1230 -- This procedure controls the actual dml update logic. The functions of this
1231 -- procedure are as follows:
1232 -- 1) Increment the object_version_number by 1 if the object_version_number
1233 -- is defined as an attribute for this entity.
1234 -- 2) To set and unset the g_api_dml status as required (as we are about to
1235 -- perform dml).
1236 -- 3) To update the specified row in the schema using the primary key in
1237 -- the predicates.
1238 -- 4) To trap any constraint violations that may have occurred.
1239 -- 5) To raise any other errors.
1240 --
1241 -- Pre Conditions:
1242 -- This is an internal private procedure which must be called from the upd
1243 -- procedure.
1244 --
1245 -- In Arguments:
1246 -- A Pl/Sql record structre.
1247 --
1248 -- Post Success:
1249 -- The specified row will be updated in the schema.
1250 --
1251 -- Post Failure:
1252 -- On the update dml failure it is important to note that we always reset the
1253 -- g_api_dml status to false.
1254 -- If a check, unique or parent integrity constraint violation is raised the
1255 -- constraint_error procedure will be called.
1256 -- If any other error is reported, the error will be raised after the
1257 -- g_api_dml status is reset.
1258 --
1259 -- Developer Implementation Notes:
1260 -- The update 'set' arguments list should be modified if any of your
1261 -- attributes are not updateable.
1262 --
1263 -- {End Of Comments}
1264 -- ----------------------------------------------------------------------------
1265 Procedure update_dml(p_rec in out nocopy g_rec_type) is
1266 --
1267 l_proc varchar2(72) := g_package||'update_dml';
1268 --
1269 Begin
1270 hr_utility.set_location('Entering:'||l_proc, 5);
1271 --
1272 -- Increment the object version
1273 --
1274 p_rec.object_version_number := p_rec.object_version_number + 1;
1275 --
1276 g_api_dml := true; -- Set the api dml status
1277 --
1278 -- Update the ota_booking_deals Row
1279 --
1280 update ota_booking_deals
1281 set
1282 booking_deal_id = p_rec.booking_deal_id,
1283 customer_id = p_rec.customer_id,
1284 approved_by_person_id = p_rec.approved_by_person_id,
1285 business_group_id = p_rec.business_group_id,
1286 name = p_rec.name,
1287 object_version_number = p_rec.object_version_number,
1288 start_date = p_rec.start_date,
1289 category = p_rec.category,
1290 comments = p_rec.comments,
1291 description = p_rec.description,
1292 discount_percentage = p_rec.discount_percentage,
1293 end_date = p_rec.end_date,
1294 number_of_places = p_rec.number_of_places,
1295 LIMIT_EACH_EVENT_FLAG = P_REC.LIMIT_EACH_EVENT_FLAG,
1296 overdraft_limit = p_rec.overdraft_limit,
1297 type = p_rec.type,
1298 price_list_id = p_rec.price_list_id,
1299 activity_version_id = p_rec.activity_version_id,
1300 event_id = p_rec.event_id,
1301 tbd_information_category = p_rec.tbd_information_category,
1302 tbd_information1 = p_rec.tbd_information1,
1303 tbd_information2 = p_rec.tbd_information2,
1304 tbd_information3 = p_rec.tbd_information3,
1305 tbd_information4 = p_rec.tbd_information4,
1306 tbd_information5 = p_rec.tbd_information5,
1307 tbd_information6 = p_rec.tbd_information6,
1308 tbd_information7 = p_rec.tbd_information7,
1309 tbd_information8 = p_rec.tbd_information8,
1310 tbd_information9 = p_rec.tbd_information9,
1311 tbd_information10 = p_rec.tbd_information10,
1312 tbd_information11 = p_rec.tbd_information11,
1313 tbd_information12 = p_rec.tbd_information12,
1314 tbd_information13 = p_rec.tbd_information13,
1315 tbd_information14 = p_rec.tbd_information14,
1316 tbd_information15 = p_rec.tbd_information15,
1317 tbd_information16 = p_rec.tbd_information16,
1318 tbd_information17 = p_rec.tbd_information17,
1319 tbd_information18 = p_rec.tbd_information18,
1320 tbd_information19 = p_rec.tbd_information19,
1321 tbd_information20 = p_rec.tbd_information20
1322 where booking_deal_id = p_rec.booking_deal_id;
1323 --
1324 g_api_dml := false; -- Unset the api dml status
1325 --
1326 hr_utility.set_location(' Leaving:'||l_proc, 10);
1327 --
1328 Exception
1329 When hr_api.check_integrity_violated Then
1330 -- A check constraint has been violated
1331 g_api_dml := false; -- Unset the api dml status
1332 constraint_error
1333 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1334 When hr_api.parent_integrity_violated Then
1335 -- Parent integrity has been violated
1336 g_api_dml := false; -- Unset the api dml status
1337 constraint_error
1338 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1339 When hr_api.unique_integrity_violated Then
1340 -- Unique integrity has been violated
1341 g_api_dml := false; -- Unset the api dml status
1342 constraint_error
1343 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1344 When Others Then
1345 g_api_dml := false; -- Unset the api dml status
1346 Raise;
1347 End update_dml;
1348 --
1349 -- ----------------------------------------------------------------------------
1350 -- |------------------------------< delete_dml >------------------------------|
1351 -- ----------------------------------------------------------------------------
1352 -- {Start Of Comments}
1353 --
1354 -- Description:
1355 -- This procedure controls the actual dml delete logic. The functions of this
1356 -- procedure are as follows:
1357 -- 1) To set and unset the g_api_dml status as required (as we are about to
1358 -- perform dml).
1359 -- 2) To delete the specified row from the schema using the primary key in
1360 -- the predicates.
1361 -- 3) To trap any constraint violations that may have occurred.
1362 -- 4) To raise any other errors.
1363 --
1364 -- Pre Conditions:
1365 -- This is an internal private procedure which must be called from the del
1366 -- procedure.
1367 --
1368 -- In Arguments:
1369 -- A Pl/Sql record structre.
1370 --
1371 -- Post Success:
1372 -- The specified row will be delete from the schema.
1373 --
1374 -- Post Failure:
1375 -- On the delete dml failure it is important to note that we always reset the
1376 -- g_api_dml status to false.
1377 -- If a child integrity constraint violation is raised the
1378 -- constraint_error procedure will be called.
1379 -- If any other error is reported, the error will be raised after the
1380 -- g_api_dml status is reset.
1381 --
1382 -- Developer Implementation Notes:
1383 -- None.
1384 --
1385 -- {End Of Comments}
1386 -- ----------------------------------------------------------------------------
1387 Procedure delete_dml(p_rec in g_rec_type) is
1388 --
1389 l_proc varchar2(72) := g_package||'delete_dml';
1390 --
1391 Begin
1392 hr_utility.set_location('Entering:'||l_proc, 5);
1393 --
1394 g_api_dml := true; -- Set the api dml status
1395 --
1396 -- Delete the ota_booking_deals row.
1397 --
1398 delete from ota_booking_deals
1399 where booking_deal_id = p_rec.booking_deal_id;
1400 --
1401 g_api_dml := false; -- Unset the api dml status
1402 --
1403 hr_utility.set_location(' Leaving:'||l_proc, 10);
1404 --
1405 Exception
1406 When hr_api.child_integrity_violated then
1407 -- Child integrity has been violated
1408 g_api_dml := false; -- Unset the api dml status
1409 constraint_error
1410 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1411 When Others Then
1412 g_api_dml := false; -- Unset the api dml status
1413 Raise;
1414 End delete_dml;
1415 --
1416 -- ----------------------------------------------------------------------------
1417 -- |------------------------------< pre_insert >------------------------------|
1418 -- ----------------------------------------------------------------------------
1419 -- {Start Of Comments}
1420 --
1421 -- Description:
1422 -- This private procedure contains any processing which is required before
1423 -- the insert dml. Presently, if the entity has a corresponding primary
1424 -- key which is maintained by an associating sequence, the primary key for
1425 -- the entity will be populated with the next sequence value in
1426 -- preparation for the insert dml.
1427 --
1428 -- Pre Conditions:
1429 -- This is an internal procedure which is called from the ins procedure.
1430 --
1431 -- In Arguments:
1432 -- A Pl/Sql record structre.
1433 --
1434 -- Post Success:
1435 -- Processing continues.
1436 --
1437 -- Post Failure:
1438 -- If an error has occurred, an error message and exception will be raised
1439 -- but not handled.
1440 --
1441 -- Developer Implementation Notes:
1442 -- Any pre-processing required before the insert dml is issued should be
1443 -- coded within this procedure. As stated above, a good example is the
1444 -- generation of a primary key number via a corresponding sequence.
1445 -- It is important to note that any 3rd party maintenance should be reviewed
1446 -- before placing in this procedure.
1447 --
1448 -- {End Of Comments}
1449 -- ----------------------------------------------------------------------------
1450 Procedure pre_insert(p_rec in out nocopy g_rec_type) is
1451 --
1452 l_proc varchar2(72) := g_package||'pre_insert';
1453 --
1454 Cursor C_Sel1 is select ota_booking_deals_s.nextval from sys.dual;
1455 --
1456 Begin
1457 hr_utility.set_location('Entering:'||l_proc, 5);
1458 --
1459 --
1460 -- Select the next sequence number
1461 --
1462 Open C_Sel1;
1463 Fetch C_Sel1 Into p_rec.booking_deal_id;
1464 Close C_Sel1;
1465 --
1466 hr_utility.set_location(' Leaving:'||l_proc, 10);
1467 End pre_insert;
1468 --
1469 -- ----------------------------------------------------------------------------
1470 -- |------------------------------< pre_update >------------------------------|
1471 -- ----------------------------------------------------------------------------
1472 -- {Start Of Comments}
1473 --
1474 -- Description:
1475 -- This private procedure contains any processing which is required before
1476 -- the update dml.
1477 --
1478 -- Pre Conditions:
1479 -- This is an internal procedure which is called from the upd procedure.
1480 --
1481 -- In Arguments:
1482 -- A Pl/Sql record structre.
1483 --
1484 -- Post Success:
1485 -- Processing continues.
1486 --
1487 -- Post Failure:
1488 -- If an error has occurred, an error message and exception will be raised
1489 -- but not handled.
1490 --
1491 -- Developer Implementation Notes:
1492 -- Any pre-processing required before the update dml is issued should be
1493 -- coded within this procedure. It is important to note that any 3rd party
1494 -- maintenance should be reviewed before placing in this procedure.
1495 --
1496 -- {End Of Comments}
1497 -- ----------------------------------------------------------------------------
1498 Procedure pre_update(p_rec in g_rec_type) is
1499 --
1500 l_proc varchar2(72) := g_package||'pre_update';
1501 --
1502 Begin
1503 hr_utility.set_location('Entering:'||l_proc, 5);
1504 --
1505 hr_utility.set_location(' Leaving:'||l_proc, 10);
1506 End pre_update;
1507 --
1508 -- ----------------------------------------------------------------------------
1509 -- |------------------------------< pre_delete >------------------------------|
1510 -- ----------------------------------------------------------------------------
1511 -- {Start Of Comments}
1512 --
1513 -- Description:
1514 -- This private procedure contains any processing which is required before
1515 -- the delete dml.
1516 --
1517 -- Pre Conditions:
1518 -- This is an internal procedure which is called from the del procedure.
1519 --
1520 -- In Arguments:
1521 -- A Pl/Sql record structre.
1522 --
1523 -- Post Success:
1524 -- Processing continues.
1525 --
1526 -- Post Failure:
1527 -- If an error has occurred, an error message and exception will be raised
1528 -- but not handled.
1529 --
1530 -- Developer Implementation Notes:
1531 -- Any pre-processing required before the delete dml is issued should be
1532 -- coded within this procedure. It is important to note that any 3rd party
1533 -- maintenance should be reviewed before placing in this procedure.
1534 --
1535 -- {End Of Comments}
1536 -- ----------------------------------------------------------------------------
1537 Procedure pre_delete(p_rec in g_rec_type) is
1538 --
1539 l_proc varchar2(72) := g_package||'pre_delete';
1540 --
1541 Begin
1542 hr_utility.set_location('Entering:'||l_proc, 5);
1543 --
1544 hr_utility.set_location(' Leaving:'||l_proc, 10);
1545 End pre_delete;
1546 --
1547 -- ----------------------------------------------------------------------------
1548 -- |-----------------------------< post_insert >------------------------------|
1549 -- ----------------------------------------------------------------------------
1550 -- {Start Of Comments}
1551 --
1552 -- Description:
1553 -- This private procedure contains any processing which is required after the
1554 -- insert dml.
1555 --
1556 -- Pre Conditions:
1557 -- This is an internal procedure which is called from the ins procedure.
1558 --
1559 -- In Arguments:
1560 -- A Pl/Sql record structre.
1561 --
1562 -- Post Success:
1563 -- Processing continues.
1564 --
1565 -- Post Failure:
1566 -- If an error has occurred, an error message and exception will be raised
1567 -- but not handled.
1568 --
1569 -- Developer Implementation Notes:
1570 -- Any post-processing required after the insert dml is issued should be
1571 -- coded within this procedure. It is important to note that any 3rd party
1572 -- maintenance should be reviewed before placing in this procedure.
1573 --
1574 -- {End Of Comments}
1575 -- ----------------------------------------------------------------------------
1576 Procedure post_insert(p_rec in g_rec_type) is
1577 --
1578 l_proc varchar2(72) := g_package||'post_insert';
1579 --
1580 Begin
1581 hr_utility.set_location('Entering:'||l_proc, 5);
1582 --
1583 hr_utility.set_location(' Leaving:'||l_proc, 10);
1584 End post_insert;
1585 --
1586 -- ----------------------------------------------------------------------------
1587 -- |-----------------------------< post_update >------------------------------|
1588 -- ----------------------------------------------------------------------------
1589 -- {Start Of Comments}
1590 --
1591 -- Description:
1592 -- This private procedure contains any processing which is required after the
1593 -- update dml.
1594 --
1595 -- Pre Conditions:
1596 -- This is an internal procedure which is called from the upd procedure.
1597 --
1598 -- In Arguments:
1599 -- A Pl/Sql record structre.
1600 --
1601 -- Post Success:
1602 -- Processing continues.
1603 --
1604 -- Post Failure:
1605 -- If an error has occurred, an error message and exception will be raised
1606 -- but not handled.
1607 --
1608 -- Developer Implementation Notes:
1609 -- Any post-processing required after the update dml is issued should be
1610 -- coded within this procedure. It is important to note that any 3rd party
1611 -- maintenance should be reviewed before placing in this procedure.
1612 --
1613 -- {End Of Comments}
1614 -- ----------------------------------------------------------------------------
1615 Procedure post_update(p_rec in g_rec_type) is
1616 --
1617 l_proc varchar2(72) := g_package||'post_update';
1618 --
1619 Begin
1620 hr_utility.set_location('Entering:'||l_proc, 5);
1621 --
1622 hr_utility.set_location(' Leaving:'||l_proc, 10);
1623 End post_update;
1624 --
1625 -- ----------------------------------------------------------------------------
1626 -- |-----------------------------< post_delete >------------------------------|
1627 -- ----------------------------------------------------------------------------
1628 -- {Start Of Comments}
1629 --
1630 -- Description:
1631 -- This private procedure contains any processing which is required after the
1632 -- delete dml.
1633 --
1634 -- Pre Conditions:
1635 -- This is an internal procedure which is called from the del procedure.
1636 --
1637 -- In Arguments:
1638 -- A Pl/Sql record structre.
1639 --
1640 -- Post Success:
1641 -- Processing continues.
1642 --
1643 -- Post Failure:
1644 -- If an error has occurred, an error message and exception will be raised
1645 -- but not handled.
1646 --
1647 -- Developer Implementation Notes:
1648 -- Any post-processing required after the delete dml is issued should be
1649 -- coded within this procedure. It is important to note that any 3rd party
1650 -- maintenance should be reviewed before placing in this procedure.
1651 --
1652 -- {End Of Comments}
1653 -- ----------------------------------------------------------------------------
1654 Procedure post_delete(p_rec in g_rec_type) is
1655 --
1656 l_proc varchar2(72) := g_package||'post_delete';
1657 --
1658 Begin
1659 hr_utility.set_location('Entering:'||l_proc, 5);
1660 --
1661 hr_utility.set_location(' Leaving:'||l_proc, 10);
1662 End post_delete;
1663 --
1664 -- ----------------------------------------------------------------------------
1665 -- |---------------------------------< lck >----------------------------------|
1666 -- ----------------------------------------------------------------------------
1667 Procedure lck
1668 (
1669 p_booking_deal_id in number,
1670 p_object_version_number in number
1671 ) is
1672 --
1673 -- Cursor selects the 'current' row from the HR Schema
1674 --
1675 Cursor C_Sel1 is
1676 select booking_deal_id,
1677 customer_id,
1678 approved_by_person_id,
1679 business_group_id,
1680 name,
1681 object_version_number,
1682 start_date,
1683 category,
1684 comments,
1685 description,
1686 discount_percentage,
1687 end_date,
1688 number_of_places,
1689 LIMIT_EACH_EVENT_FLAG,
1690 overdraft_limit,
1691 type,
1692 price_list_id,
1693 activity_version_id,
1694 event_id,
1695 tbd_information_category,
1696 tbd_information1,
1697 tbd_information2,
1698 tbd_information3,
1699 tbd_information4,
1700 tbd_information5,
1701 tbd_information6,
1702 tbd_information7,
1703 tbd_information8,
1704 tbd_information9,
1705 tbd_information10,
1706 tbd_information11,
1707 tbd_information12,
1708 tbd_information13,
1709 tbd_information14,
1710 tbd_information15,
1711 tbd_information16,
1712 tbd_information17,
1713 tbd_information18,
1714 tbd_information19,
1715 tbd_information20
1716 from ota_booking_deals
1717 where booking_deal_id = p_booking_deal_id
1718 for update nowait;
1719 --
1720 l_proc varchar2(72) := g_package||'lck';
1721 --
1722 Begin
1723 hr_utility.set_location('Entering:'||l_proc, 5);
1724 --
1725 -- Add any mandatory argument checking here:
1726 -- Example:
1727 -- hr_api.mandatory_arg_error
1728 -- (p_api_name => l_proc,
1729 -- p_argument => 'object_version_number',
1730 -- p_argument_value => p_object_version_number);
1731 --
1732 Open C_Sel1;
1733 Fetch C_Sel1 Into g_old_rec;
1734 If C_Sel1%notfound then
1735 Close C_Sel1;
1736 --
1737 -- The primary key is invalid therefore we must error
1738 --
1739 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1740 hr_utility.raise_error;
1741 End If;
1742 Close C_Sel1;
1743 If (p_object_version_number <> g_old_rec.object_version_number) Then
1744 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
1745 hr_utility.raise_error;
1746 End If;
1747 --
1748 hr_utility.set_location(' Leaving:'||l_proc, 10);
1749 --
1750 -- We need to trap the ORA LOCK exception
1751 --
1752 Exception
1753 When HR_Api.Object_Locked then
1754 --
1755 -- The object is locked therefore we need to supply a meaningful
1756 -- error message.
1757 --
1758 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
1759 hr_utility.set_message_token('TABLE_NAME', 'ota_booking_deals');
1760 hr_utility.raise_error;
1761 End lck;
1762 --
1763 -- ----------------------------------------------------------------------------
1764 -- |-----------------------------< convert_args >-----------------------------|
1765 -- ----------------------------------------------------------------------------
1766 -- {Start Of Comments}
1767 --
1768 -- Description:
1769 -- This function is used to turn attribute arguments into the record
1770 -- structure g_rec_type.
1771 --
1772 -- Pre Conditions:
1773 -- This is a private function and can only be called from the ins or upd
1774 -- attribute processes.
1775 --
1776 -- In Arguments:
1777 --
1778 -- Post Success:
1779 -- A returning record structure will be returned.
1780 --
1781 -- Post Failure:
1782 -- No direct error handling is required within this function. Any possible
1783 -- errors within this function will be a PL/SQL value error due to conversion
1784 -- of datatypes or data lengths.
1785 --
1786 -- Developer Implementation Notes:
1787 --
1788 -- {End Of Comments}
1789 -- ----------------------------------------------------------------------------
1790 Function convert_args
1791 (
1792 p_booking_deal_id in number,
1793 p_customer_id in number,
1794 p_approved_by_person_id in number,
1795 p_business_group_id in number,
1796 p_name in varchar2,
1797 p_object_version_number in number,
1798 p_start_date in date,
1799 p_category in varchar2,
1800 p_comments in varchar2,
1801 p_description in varchar2,
1802 p_discount_percentage in number,
1803 p_end_date in date,
1804 p_number_of_places in number,
1805 P_LIMIT_EACH_EVENT_FLAG in varchar2,
1806 p_overdraft_limit in number,
1807 p_type in varchar2,
1808 p_price_list_id in number,
1809 p_activity_version_id in number,
1810 p_event_id in number,
1811 p_tbd_information_category in varchar2,
1812 p_tbd_information1 in varchar2,
1813 p_tbd_information2 in varchar2,
1814 p_tbd_information3 in varchar2,
1815 p_tbd_information4 in varchar2,
1816 p_tbd_information5 in varchar2,
1817 p_tbd_information6 in varchar2,
1818 p_tbd_information7 in varchar2,
1819 p_tbd_information8 in varchar2,
1820 p_tbd_information9 in varchar2,
1821 p_tbd_information10 in varchar2,
1822 p_tbd_information11 in varchar2,
1823 p_tbd_information12 in varchar2,
1824 p_tbd_information13 in varchar2,
1825 p_tbd_information14 in varchar2,
1826 p_tbd_information15 in varchar2,
1827 p_tbd_information16 in varchar2,
1828 p_tbd_information17 in varchar2,
1829 p_tbd_information18 in varchar2,
1830 p_tbd_information19 in varchar2,
1831 p_tbd_information20 in varchar2
1832 )
1833 Return g_rec_type is
1834 --
1835 l_rec g_rec_type;
1836 l_proc varchar2(72) := g_package||'convert_args';
1837 --
1838 Begin
1839 --
1840 hr_utility.set_location('Entering:'||l_proc, 5);
1841 --
1842 -- Convert arguments into local l_rec structure.
1843 --
1844 l_rec.booking_deal_id := p_booking_deal_id;
1845 l_rec.customer_id := p_customer_id;
1846 l_rec.approved_by_person_id := p_approved_by_person_id;
1847 l_rec.business_group_id := p_business_group_id;
1848 l_rec.name := p_name;
1849 l_rec.object_version_number := p_object_version_number;
1850 l_rec.start_date := p_start_date;
1851 l_rec.category := p_category;
1852 l_rec.comments := p_comments;
1853 l_rec.description := p_description;
1854 l_rec.discount_percentage := p_discount_percentage;
1855 l_rec.end_date := p_end_date;
1856 l_rec.number_of_places := p_number_of_places;
1857 L_REC.LIMIT_EACH_EVENT_FLAG := P_LIMIT_EACH_EVENT_FLAG;
1858 l_rec.overdraft_limit := p_overdraft_limit;
1859 l_rec.type := p_type;
1860 l_rec.price_list_id := p_price_list_id;
1861 l_rec.activity_version_id := p_activity_version_id;
1862 l_rec.event_id := p_event_id;
1863 l_rec.tbd_information_category := p_tbd_information_category;
1864 l_rec.tbd_information1 := p_tbd_information1;
1865 l_rec.tbd_information2 := p_tbd_information2;
1866 l_rec.tbd_information3 := p_tbd_information3;
1867 l_rec.tbd_information4 := p_tbd_information4;
1868 l_rec.tbd_information5 := p_tbd_information5;
1869 l_rec.tbd_information6 := p_tbd_information6;
1870 l_rec.tbd_information7 := p_tbd_information7;
1871 l_rec.tbd_information8 := p_tbd_information8;
1872 l_rec.tbd_information9 := p_tbd_information9;
1873 l_rec.tbd_information10 := p_tbd_information10;
1874 l_rec.tbd_information11 := p_tbd_information11;
1875 l_rec.tbd_information12 := p_tbd_information12;
1876 l_rec.tbd_information13 := p_tbd_information13;
1877 l_rec.tbd_information14 := p_tbd_information14;
1878 l_rec.tbd_information15 := p_tbd_information15;
1879 l_rec.tbd_information16 := p_tbd_information16;
1880 l_rec.tbd_information17 := p_tbd_information17;
1881 l_rec.tbd_information18 := p_tbd_information18;
1882 l_rec.tbd_information19 := p_tbd_information19;
1883 l_rec.tbd_information20 := p_tbd_information20;
1884 --
1885 -- Return the plsql record structure.
1886 --
1887 hr_utility.set_location(' Leaving:'||l_proc, 10);
1888 Return(l_rec);
1889 --
1890 End convert_args;
1891 --
1892 -- ----------------------------------------------------------------------------
1893 -- |-----------------------------< convert_defs >-----------------------------|
1894 -- ----------------------------------------------------------------------------
1895 -- {Start Of Comments}
1896 --
1897 -- Description:
1898 -- The Convert_Defs function has one very important function:
1899 -- It must return the record structure for the row with all system defaulted
1900 -- values converted into its corresponding argument value for update. When
1901 -- we attempt to update a row through the Upd business process , certain
1902 -- arguments can be defaulted which enables flexibility in the calling of
1903 -- the upd process (e.g. only attributes which need to be updated need to be
1904 -- specified). For the upd business process to determine which attributes
1905 -- have NOT been specified we need to check if the argument has a reserved
1906 -- system default value. Therefore, for all attributes which have a
1907 -- corresponding reserved system default mechanism specified we need to
1908 -- check if a system default is being used. If a system default is being
1909 -- used then we convert the defaulted value into its corresponding attribute
1910 -- value held in the g_old_rec data structure.
1911 --
1912 -- Pre Conditions:
1913 -- This private function can only be called from the upd process.
1914 --
1915 -- In Arguments:
1916 -- A Pl/Sql record structre.
1917 --
1918 -- Post Success:
1919 -- The record structure will be returned with all system defaulted argument
1920 -- values converted into its current row attribute value.
1921 --
1922 -- Post Failure:
1923 -- No direct error handling is required within this function. Any possible
1924 -- errors within this function will be a PL/SQL value error due to conversion
1925 -- of datatypes or data lengths.
1926 --
1927 -- Developer Implementation Notes:
1928 --
1929 -- {End Of Comments}
1930 -- ----------------------------------------------------------------------------
1931 Function convert_defs(p_rec in out nocopy g_rec_type)
1932 Return g_rec_type is
1933 --
1934 l_proc varchar2(72) := g_package||'convert_defs';
1935 --
1936 Begin
1937 --
1938 hr_utility.set_location('Entering:'||l_proc, 5);
1939 --
1940 -- We must now examine each argument value in the
1941 -- p_rec plsql record structure
1942 -- to see if a system default is being used. If a system default
1943 -- is being used then we must set to the 'current' argument value.
1944 --
1945 If (p_rec.customer_id = hr_api.g_number) then
1946 p_rec.customer_id := g_old_rec.customer_id;
1947 End If;
1948 If (p_rec.approved_by_person_id = hr_api.g_number) then
1949 p_rec.approved_by_person_id := g_old_rec.approved_by_person_id;
1950 End If;
1951 If (p_rec.business_group_id = hr_api.g_number) then
1952 p_rec.business_group_id := g_old_rec.business_group_id;
1953 End If;
1954 If (p_rec.name = hr_api.g_varchar2) then
1955 p_rec.name := g_old_rec.name;
1956 End If;
1957 If (p_rec.start_date = hr_api.g_date) then
1958 p_rec.start_date := g_old_rec.start_date;
1959 End If;
1960 If (p_rec.category = hr_api.g_varchar2) then
1961 p_rec.category := g_old_rec.category;
1962 End If;
1963 If (p_rec.comments = hr_api.g_varchar2) then
1964 p_rec.comments := g_old_rec.comments;
1965 End If;
1966 If (p_rec.description = hr_api.g_varchar2) then
1967 p_rec.description := g_old_rec.description;
1968 End If;
1969 If (p_rec.discount_percentage = hr_api.g_number) then
1970 p_rec.discount_percentage := g_old_rec.discount_percentage;
1971 End If;
1972 If (p_rec.end_date = hr_api.g_date) then
1973 p_rec.end_date := g_old_rec.end_date;
1974 End If;
1975 If (p_rec.number_of_places = hr_api.g_number) then
1976 p_rec.number_of_places := g_old_rec.number_of_places;
1977 End If;
1978 if (P_REC.LIMIT_EACH_EVENT_FLAG = HR_API.G_VARCHAR2) then
1979 P_REC.LIMIT_EACH_EVENT_FLAG := G_OLD_REC.LIMIT_EACH_EVENT_FLAG;
1980 end if;
1981 If (p_rec.overdraft_limit = hr_api.g_number) then
1982 p_rec.overdraft_limit := g_old_rec.overdraft_limit;
1983 End If;
1984 If (p_rec.type = hr_api.g_varchar2) then
1985 p_rec.type := g_old_rec.type;
1986 End If;
1987 If (p_rec.price_list_id = hr_api.g_number) then
1988 p_rec.price_list_id := g_old_rec.price_list_id;
1989 End If;
1990 If (p_rec.activity_version_id = hr_api.g_number) then
1991 p_rec.activity_version_id := g_old_rec.activity_version_id;
1992 End If;
1993 If (p_rec.event_id = hr_api.g_number) then
1994 p_rec.event_id := g_old_rec.event_id;
1995 End If;
1996 If (p_rec.tbd_information_category = hr_api.g_varchar2) then
1997 p_rec.tbd_information_category := g_old_rec.tbd_information_category;
1998 End If;
1999 If (p_rec.tbd_information1 = hr_api.g_varchar2) then
2000 p_rec.tbd_information1 := g_old_rec.tbd_information1;
2001 End If;
2002 If (p_rec.tbd_information2 = hr_api.g_varchar2) then
2003 p_rec.tbd_information2 := g_old_rec.tbd_information2;
2004 End If;
2005 If (p_rec.tbd_information3 = hr_api.g_varchar2) then
2006 p_rec.tbd_information3 := g_old_rec.tbd_information3;
2007 End If;
2008 If (p_rec.tbd_information4 = hr_api.g_varchar2) then
2009 p_rec.tbd_information4 := g_old_rec.tbd_information4;
2010 End If;
2011 If (p_rec.tbd_information5 = hr_api.g_varchar2) then
2012 p_rec.tbd_information5 := g_old_rec.tbd_information5;
2013 End If;
2014 If (p_rec.tbd_information6 = hr_api.g_varchar2) then
2015 p_rec.tbd_information6 := g_old_rec.tbd_information6;
2016 End If;
2017 If (p_rec.tbd_information7 = hr_api.g_varchar2) then
2018 p_rec.tbd_information7 := g_old_rec.tbd_information7;
2019 End If;
2020 If (p_rec.tbd_information8 = hr_api.g_varchar2) then
2021 p_rec.tbd_information8 := g_old_rec.tbd_information8;
2022 End If;
2023 If (p_rec.tbd_information9 = hr_api.g_varchar2) then
2024 p_rec.tbd_information9 := g_old_rec.tbd_information9;
2025 End If;
2026 If (p_rec.tbd_information10 = hr_api.g_varchar2) then
2027 p_rec.tbd_information10 := g_old_rec.tbd_information10;
2028 End If;
2029 If (p_rec.tbd_information11 = hr_api.g_varchar2) then
2030 p_rec.tbd_information11 := g_old_rec.tbd_information11;
2031 End If;
2032 If (p_rec.tbd_information12 = hr_api.g_varchar2) then
2033 p_rec.tbd_information12 := g_old_rec.tbd_information12;
2034 End If;
2035 If (p_rec.tbd_information13 = hr_api.g_varchar2) then
2036 p_rec.tbd_information13 := g_old_rec.tbd_information13;
2037 End If;
2038 If (p_rec.tbd_information14 = hr_api.g_varchar2) then
2039 p_rec.tbd_information14 := g_old_rec.tbd_information14;
2040 End If;
2041 If (p_rec.tbd_information15 = hr_api.g_varchar2) then
2042 p_rec.tbd_information15 := g_old_rec.tbd_information15;
2043 End If;
2044 If (p_rec.tbd_information16 = hr_api.g_varchar2) then
2045 p_rec.tbd_information16 := g_old_rec.tbd_information16;
2046 End If;
2047 If (p_rec.tbd_information17 = hr_api.g_varchar2) then
2048 p_rec.tbd_information17 := g_old_rec.tbd_information17;
2049 End If;
2050 If (p_rec.tbd_information18 = hr_api.g_varchar2) then
2051 p_rec.tbd_information18 := g_old_rec.tbd_information18;
2052 End If;
2053 If (p_rec.tbd_information19 = hr_api.g_varchar2) then
2054 p_rec.tbd_information19 := g_old_rec.tbd_information19;
2055 End If;
2056 If (p_rec.tbd_information20 = hr_api.g_varchar2) then
2057 p_rec.tbd_information20 := g_old_rec.tbd_information20;
2058 End If;
2059 --
2060 -- Return the plsql record structure.
2061 --
2062 hr_utility.set_location(' Leaving:'||l_proc, 10);
2063 Return(p_rec);
2064 --
2065 End convert_defs;
2066 --
2067 -- ----------------------------------------------------------------------------
2068 -- |---------------------------< insert_validate >----------------------------|
2069 -- ----------------------------------------------------------------------------
2070 -- {Start Of Comments}
2071 --
2072 -- Description:
2073 -- This procedure controls the execution of all insert business rules
2074 -- validation.
2075 --
2076 -- Pre Conditions:
2077 -- This private procedure is called from ins procedure.
2078 --
2079 -- In Arguments:
2080 -- A Pl/Sql record structre.
2081 --
2082 -- Post Success:
2083 -- Processing continues.
2084 --
2085 -- Post Failure:
2086 -- If a business rules fails the error will not be handled by this procedure
2087 -- unless explicity coded.
2088 --
2089 -- Developer Implementation Notes:
2090 -- For insert, your business rules should be coded within this procedure and
2091 -- should ideally (unless really necessary) just be straight procedure or
2092 -- function calls. Try and avoid using conditional branching logic.
2093 --
2094 -- {End Of Comments}
2095 -- ----------------------------------------------------------------------------
2096 Procedure insert_validate(p_rec in g_rec_type) is
2097 --
2098 l_proc varchar2(72) := g_package||'insert_validate';
2099 --
2100 Begin
2101 hr_utility.set_location('Entering:'||l_proc, 5);
2102 --
2103 -- Call all supporting business operations
2104 --
2105 VALIDITY_CHECK (
2106 P_REC => P_REC);
2107 --
2108 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2109 --
2110 hr_utility.set_location(' Leaving:'||l_proc, 10);
2111 End insert_validate;
2112 --
2113 -- ----------------------------------------------------------------------------
2114 -- |---------------------------< update_validate >----------------------------|
2115 -- ----------------------------------------------------------------------------
2116 -- {Start Of Comments}
2117 --
2118 -- Description:
2119 -- This procedure controls the execution of all update business rules
2120 -- validation.
2121 --
2122 -- Pre Conditions:
2123 -- This private procedure is called from upd procedure.
2124 --
2125 -- In Arguments:
2126 -- A Pl/Sql record structre.
2127 --
2128 -- Post Success:
2129 -- Processing continues.
2130 --
2131 -- Post Failure:
2132 -- If a business rules fails the error will not be handled by this procedure
2133 -- unless explicity coded.
2134 --
2135 -- Developer Implementation Notes:
2136 -- For update, your business rules should be coded within this procedure and
2137 -- should ideally (unless really necessary) just be straight procedure or
2138 -- function calls. Try and avoid using conditional branching logic.
2139 --
2140 -- {End Of Comments}
2141 -- ----------------------------------------------------------------------------
2142 Procedure update_validate(p_rec in g_rec_type) is
2143 --
2144 l_proc varchar2(72) := g_package||'update_validate';
2145 --
2146 Begin
2147 hr_utility.set_location('Entering:'||l_proc, 5);
2148 --
2149 -- Call all supporting business operations
2150 --
2151 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2152 --
2153 if ( (TFL_LINES (P_REC.BOOKING_DEAL_ID))
2154 and ( (P_REC.PRICE_LIST_ID <> G_OLD_REC.PRICE_LIST_ID )
2155 or (P_REC.EVENT_ID <> G_OLD_REC.EVENT_ID )
2156 or (P_REC.ACTIVITY_VERSION_ID <> G_OLD_REC.ACTIVITY_VERSION_ID)
2157 or (P_REC.CATEGORY <> G_OLD_REC.CATEGORY ))) then
2158 CONSTRAINT_ERROR ('NON-TRANSFERABLE BASIS');
2159 end if;
2160 --
2161 -- Check if dates invalidate any enrollments
2162 --
2163 if p_rec.start_date <> g_old_rec.start_date or
2164 p_rec.end_date <> g_old_rec.end_date then
2165 --
2166 check_dates_conflict(p_rec.booking_deal_id,
2167 p_rec.start_date,
2168 p_rec.end_date);
2169 --
2170 end if;
2171 --
2172 VALIDITY_CHECK (
2173 P_REC => P_REC);
2174 --
2175 hr_utility.set_location(' Leaving:'||l_proc, 10);
2176 End update_validate;
2177 --
2178 -- ----------------------------------------------------------------------------
2179 -- |---------------------------< delete_validate >----------------------------|
2180 -- ----------------------------------------------------------------------------
2181 -- {Start Of Comments}
2182 --
2183 -- Description:
2184 -- This procedure controls the execution of all delete business rules
2185 -- validation.
2186 --
2187 -- Pre Conditions:
2188 -- This private procedure is called from del procedure.
2189 --
2190 -- In Arguments:
2191 -- A Pl/Sql record structre.
2192 --
2193 -- Post Success:
2194 -- Processing continues.
2195 --
2196 -- Post Failure:
2197 -- If a business rules fails the error will not be handled by this procedure
2198 -- unless explicity coded.
2199 --
2200 -- Developer Implementation Notes:
2201 -- For delete, your business rules should be coded within this procedure and
2202 -- should ideally (unless really necessary) just be straight procedure or
2203 -- function calls. Try and avoid using conditional branching logic.
2204 --
2205 -- {End Of Comments}
2206 -- ----------------------------------------------------------------------------
2207 Procedure delete_validate(p_rec in g_rec_type) is
2208 --
2209 l_proc varchar2(72) := g_package||'delete_validate';
2210 --
2211 Begin
2212 hr_utility.set_location('Entering:'||l_proc, 5);
2213 --
2214 -- Call all supporting business operations
2215 --
2216 if (TFL_LINES (
2217 P_BOOKING_DEAL_ID => P_REC.BOOKING_DEAL_ID)) then
2218 CONSTRAINT_ERROR ('OTA_TBD_FINANCE_LINES');
2219 end if;
2220 --
2221 hr_utility.set_location(' Leaving:'||l_proc, 10);
2222 End delete_validate;
2223 --
2224 -- ----------------------------------------------------------------------------
2225 -- |---------------------------------< ins >----------------------------------|
2226 -- ----------------------------------------------------------------------------
2227 Procedure ins
2228 (
2229 p_rec in out nocopy g_rec_type,
2230 p_validate in boolean default false
2231 ) is
2232 --
2233 l_proc varchar2(72) := g_package||'ins';
2234 --
2235 Begin
2236 hr_utility.set_location('Entering:'||l_proc, 5);
2237 --
2238 -- Determine if the business process is to be validated.
2239 --
2240 If p_validate then
2241 --
2242 -- Issue the savepoint.
2243 --
2244 SAVEPOINT ins_tbd;
2245 End If;
2246 --
2247 -- Call the supporting insert validate operations
2248 --
2249 insert_validate(p_rec);
2250 --
2251 -- Call the supporting pre-insert operation
2252 --
2253 pre_insert(p_rec);
2254 --
2255 -- Insert the row
2256 --
2257 insert_dml(p_rec);
2258 --
2259 -- Call the supporting post-insert operation
2260 --
2261 post_insert(p_rec);
2262 --
2263 -- If we are validating then raise the Validate_Enabled exception
2264 --
2265 If p_validate then
2266 Raise HR_Api.Validate_Enabled;
2267 End If;
2268 --
2269 hr_utility.set_location(' Leaving:'||l_proc, 10);
2270 Exception
2271 When HR_Api.Validate_Enabled Then
2272 --
2273 -- As the Validate_Enabled exception has been raised
2274 -- we must rollback to the savepoint
2275 --
2276 ROLLBACK TO ins_tbd;
2277 end ins;
2278 --
2279 -- ----------------------------------------------------------------------------
2280 -- |---------------------------------< ins >----------------------------------|
2281 -- ----------------------------------------------------------------------------
2282 Procedure ins
2283 (
2284 p_booking_deal_id out nocopy number,
2285 p_customer_id in number default null,
2286 p_approved_by_person_id in number default null,
2287 p_business_group_id in number,
2288 p_name in varchar2,
2289 p_object_version_number out nocopy number,
2290 p_start_date in date,
2291 p_category in varchar2 default null,
2292 p_comments in varchar2 default null,
2293 p_description in varchar2 default null,
2294 p_discount_percentage in number default null,
2295 p_end_date in date default null,
2296 p_number_of_places in number default null,
2297 P_LIMIT_EACH_EVENT_FLAG in varchar2 default null,
2298 p_overdraft_limit in number default null,
2299 p_type in varchar2 default null,
2300 p_price_list_id in number default null,
2301 p_activity_version_id in number default null,
2302 p_event_id in number default null,
2303 p_tbd_information_category in varchar2 default null,
2304 p_tbd_information1 in varchar2 default null,
2305 p_tbd_information2 in varchar2 default null,
2306 p_tbd_information3 in varchar2 default null,
2307 p_tbd_information4 in varchar2 default null,
2308 p_tbd_information5 in varchar2 default null,
2309 p_tbd_information6 in varchar2 default null,
2310 p_tbd_information7 in varchar2 default null,
2311 p_tbd_information8 in varchar2 default null,
2312 p_tbd_information9 in varchar2 default null,
2313 p_tbd_information10 in varchar2 default null,
2314 p_tbd_information11 in varchar2 default null,
2315 p_tbd_information12 in varchar2 default null,
2316 p_tbd_information13 in varchar2 default null,
2317 p_tbd_information14 in varchar2 default null,
2318 p_tbd_information15 in varchar2 default null,
2319 p_tbd_information16 in varchar2 default null,
2320 p_tbd_information17 in varchar2 default null,
2321 p_tbd_information18 in varchar2 default null,
2322 p_tbd_information19 in varchar2 default null,
2323 p_tbd_information20 in varchar2 default null,
2324 p_validate in boolean default false
2325 ) is
2326 --
2327 l_rec g_rec_type;
2328 l_proc varchar2(72) := g_package||'ins';
2329 --
2330 Begin
2331 hr_utility.set_location('Entering:'||l_proc, 5);
2332 --
2333 -- Call conversion function to turn arguments into the
2334 -- p_rec structure.
2335 --
2336 l_rec :=
2337 convert_args
2338 (
2339 null,
2340 p_customer_id,
2341 p_approved_by_person_id,
2342 p_business_group_id,
2343 p_name,
2344 null,
2345 p_start_date,
2346 p_category,
2347 p_comments,
2348 p_description,
2349 p_discount_percentage,
2350 p_end_date,
2351 p_number_of_places,
2352 P_LIMIT_EACH_EVENT_FLAG,
2353 p_overdraft_limit,
2354 p_type,
2355 p_price_list_id,
2356 p_activity_version_id,
2357 p_event_id,
2358 p_tbd_information_category,
2359 p_tbd_information1,
2360 p_tbd_information2,
2361 p_tbd_information3,
2362 p_tbd_information4,
2363 p_tbd_information5,
2364 p_tbd_information6,
2365 p_tbd_information7,
2366 p_tbd_information8,
2367 p_tbd_information9,
2368 p_tbd_information10,
2369 p_tbd_information11,
2370 p_tbd_information12,
2371 p_tbd_information13,
2372 p_tbd_information14,
2373 p_tbd_information15,
2374 p_tbd_information16,
2375 p_tbd_information17,
2376 p_tbd_information18,
2377 p_tbd_information19,
2378 p_tbd_information20
2379 );
2380 --
2381 -- Having converted the arguments into the tbd_rec
2382 -- plsql record structure we call the corresponding record business process.
2383 --
2384 ins(l_rec, p_validate);
2385 --
2386 -- As the primary key argument(s)
2387 -- are specified as an OUT's we must set these values.
2388 --
2389 p_booking_deal_id := l_rec.booking_deal_id;
2390 p_object_version_number := l_rec.object_version_number;
2391 --
2392 hr_utility.set_location(' Leaving:'||l_proc, 10);
2393 End ins;
2394 --
2395 -- ----------------------------------------------------------------------------
2396 -- |---------------------------------< upd >----------------------------------|
2397 -- ----------------------------------------------------------------------------
2398 Procedure upd
2399 (
2400 p_rec in out nocopy g_rec_type,
2401 p_validate in boolean default false
2402 ) is
2403 --
2404 l_proc varchar2(72) := g_package||'upd';
2405 --
2406 Begin
2407 hr_utility.set_location('Entering:'||l_proc, 5);
2408 --
2409 -- Determine if the business process is to be validated.
2410 --
2411 If p_validate then
2412 --
2413 -- Issue the savepoint.
2414 --
2415 SAVEPOINT upd_tbd;
2416 End If;
2417 --
2418 -- We must lock the row which we need to update.
2419 --
2420 lck
2421 (
2422 p_rec.booking_deal_id,
2423 p_rec.object_version_number
2424 );
2425 --
2426 -- 1. During an update system defaults are used to determine if
2427 -- arguments have been defaulted or not. We must therefore
2428 -- derive the full record structure values to be updated.
2429 --
2430 -- 2. Call the supporting update validate operations.
2431 --
2432 update_validate(convert_defs(p_rec));
2433 --
2434 -- Call the supporting pre-update operation
2435 --
2436 pre_update(p_rec);
2437 --
2438 -- Update the row.
2439 --
2440 update_dml(p_rec);
2441 --
2442 -- Call the supporting post-update operation
2443 --
2444 post_update(p_rec);
2445 --
2446 -- If we are validating then raise the Validate_Enabled exception
2447 --
2448 If p_validate then
2449 Raise HR_Api.Validate_Enabled;
2450 End If;
2451 --
2452 hr_utility.set_location(' Leaving:'||l_proc, 10);
2453 Exception
2454 When HR_Api.Validate_Enabled Then
2455 --
2456 -- As the Validate_Enabled exception has been raised
2457 -- we must rollback to the savepoint
2458 --
2459 ROLLBACK TO upd_tbd;
2460 End upd;
2461 --
2462 -- ----------------------------------------------------------------------------
2463 -- |---------------------------------< upd >----------------------------------|
2464 -- ----------------------------------------------------------------------------
2465 Procedure upd
2466 (
2467 p_booking_deal_id in number,
2468 p_customer_id in number default hr_api.g_number,
2469 p_approved_by_person_id in number default hr_api.g_number,
2470 p_business_group_id in number default hr_api.g_number,
2471 p_name in varchar2 default hr_api.g_varchar2,
2472 p_object_version_number in out nocopy number,
2473 p_start_date in date default hr_api.g_date,
2474 p_category in varchar2 default hr_api.g_varchar2,
2475 p_comments in varchar2 default hr_api.g_varchar2,
2476 p_description in varchar2 default hr_api.g_varchar2,
2477 p_discount_percentage in number default hr_api.g_number,
2478 p_end_date in date default hr_api.g_date,
2479 p_number_of_places in number default hr_api.g_number,
2480 P_LIMIT_EACH_EVENT_FLAG in varchar2 default HR_API.G_VARCHAR2,
2481 p_overdraft_limit in number default hr_api.g_number,
2482 p_type in varchar2 default hr_api.g_varchar2,
2483 p_price_list_id in number default hr_api.g_number,
2484 p_activity_version_id in number default hr_api.g_number,
2485 p_event_id in number default hr_api.g_number,
2486 p_tbd_information_category in varchar2 default hr_api.g_varchar2,
2487 p_tbd_information1 in varchar2 default hr_api.g_varchar2,
2488 p_tbd_information2 in varchar2 default hr_api.g_varchar2,
2489 p_tbd_information3 in varchar2 default hr_api.g_varchar2,
2490 p_tbd_information4 in varchar2 default hr_api.g_varchar2,
2491 p_tbd_information5 in varchar2 default hr_api.g_varchar2,
2492 p_tbd_information6 in varchar2 default hr_api.g_varchar2,
2493 p_tbd_information7 in varchar2 default hr_api.g_varchar2,
2494 p_tbd_information8 in varchar2 default hr_api.g_varchar2,
2495 p_tbd_information9 in varchar2 default hr_api.g_varchar2,
2496 p_tbd_information10 in varchar2 default hr_api.g_varchar2,
2497 p_tbd_information11 in varchar2 default hr_api.g_varchar2,
2498 p_tbd_information12 in varchar2 default hr_api.g_varchar2,
2499 p_tbd_information13 in varchar2 default hr_api.g_varchar2,
2500 p_tbd_information14 in varchar2 default hr_api.g_varchar2,
2501 p_tbd_information15 in varchar2 default hr_api.g_varchar2,
2502 p_tbd_information16 in varchar2 default hr_api.g_varchar2,
2503 p_tbd_information17 in varchar2 default hr_api.g_varchar2,
2504 p_tbd_information18 in varchar2 default hr_api.g_varchar2,
2505 p_tbd_information19 in varchar2 default hr_api.g_varchar2,
2506 p_tbd_information20 in varchar2 default hr_api.g_varchar2,
2507 p_validate in boolean default false
2508 ) is
2509 --
2510 l_rec g_rec_type;
2511 l_proc varchar2(72) := g_package||'upd';
2512 --
2513 Begin
2514 hr_utility.set_location('Entering:'||l_proc, 5);
2515 --
2516 -- Call conversion function to turn arguments into the
2517 -- l_rec structure.
2518 --
2519 l_rec :=
2520 convert_args
2521 (
2522 p_booking_deal_id,
2523 p_customer_id,
2524 p_approved_by_person_id,
2525 p_business_group_id,
2526 p_name,
2527 p_object_version_number,
2528 p_start_date,
2529 p_category,
2530 p_comments,
2531 p_description,
2532 p_discount_percentage,
2533 p_end_date,
2534 p_number_of_places,
2535 P_LIMIT_EACH_EVENT_FLAG,
2536 p_overdraft_limit,
2537 p_type,
2538 p_price_list_id,
2539 p_activity_version_id,
2540 p_event_id,
2541 p_tbd_information_category,
2542 p_tbd_information1,
2543 p_tbd_information2,
2544 p_tbd_information3,
2545 p_tbd_information4,
2546 p_tbd_information5,
2547 p_tbd_information6,
2548 p_tbd_information7,
2549 p_tbd_information8,
2550 p_tbd_information9,
2551 p_tbd_information10,
2552 p_tbd_information11,
2553 p_tbd_information12,
2554 p_tbd_information13,
2555 p_tbd_information14,
2556 p_tbd_information15,
2557 p_tbd_information16,
2558 p_tbd_information17,
2559 p_tbd_information18,
2560 p_tbd_information19,
2561 p_tbd_information20
2562 );
2563 --
2564 -- Having converted the arguments into the
2565 -- plsql record structure we call the corresponding record
2566 -- business process.
2567 --
2568 upd(l_rec, p_validate);
2569 p_object_version_number := l_rec.object_version_number;
2570 --
2571 hr_utility.set_location(' Leaving:'||l_proc, 10);
2572 End upd;
2573 --
2574 -- ----------------------------------------------------------------------------
2575 -- |---------------------------------< del >----------------------------------|
2576 -- ----------------------------------------------------------------------------
2577 Procedure del
2578 (
2579 p_rec in g_rec_type,
2580 p_validate in boolean default false
2581 ) is
2582 --
2583 l_proc varchar2(72) := g_package||'del';
2584 --
2585 Begin
2586 hr_utility.set_location('Entering:'||l_proc, 5);
2587 --
2588 -- Determine if the business process is to be validated.
2589 --
2590 If p_validate then
2591 --
2592 -- Issue the savepoint.
2593 --
2594 SAVEPOINT del_tbd;
2595 End If;
2596 --
2597 -- We must lock the row which we need to delete.
2598 --
2599 lck
2600 (
2601 p_rec.booking_deal_id,
2602 p_rec.object_version_number
2603 );
2604 --
2605 -- Call the supporting delete validate operation
2606 --
2607 delete_validate(p_rec);
2608 --
2609 -- Call the supporting pre-delete operation
2610 --
2611 pre_delete(p_rec);
2612 --
2613 -- Delete the row.
2614 --
2615 delete_dml(p_rec);
2616 --
2617 -- Call the supporting post-delete operation
2618 --
2619 post_delete(p_rec);
2620 --
2621 -- If we are validating then raise the Validate_Enabled exception
2622 --
2623 If p_validate then
2624 Raise HR_Api.Validate_Enabled;
2625 End If;
2626 --
2627 hr_utility.set_location(' Leaving:'||l_proc, 10);
2628 Exception
2629 When HR_Api.Validate_Enabled Then
2630 --
2631 -- As the Validate_Enabled exception has been raised
2632 -- we must rollback to the savepoint
2633 --
2634 ROLLBACK TO del_tbd;
2635 End del;
2636 --
2637 -- ----------------------------------------------------------------------------
2638 -- |---------------------------------< del >----------------------------------|
2639 -- ----------------------------------------------------------------------------
2640 Procedure del
2641 (
2642 p_booking_deal_id in number,
2643 p_object_version_number in number,
2644 p_validate in boolean default false
2645 ) is
2646 --
2647 l_rec g_rec_type;
2648 l_proc varchar2(72) := g_package||'del';
2649 --
2650 Begin
2651 hr_utility.set_location('Entering:'||l_proc, 5);
2652 --
2653 -- As the delete procedure accepts a plsql record structure we do need to
2654 -- convert the arguments into the record structure.
2655 -- We don't need to call the supplied conversion argument routine as we
2656 -- only need a few attributes.
2657 --
2658 l_rec.booking_deal_id:= p_booking_deal_id;
2659 l_rec.object_version_number := p_object_version_number;
2660 --
2661 -- Having converted the arguments into the tbd_rec
2662 -- plsql record structure we must call the corresponding entity
2663 -- business process
2664 --
2665 del(l_rec, p_validate);
2666 --
2667 hr_utility.set_location(' Leaving:'||l_proc, 10);
2668 End del;
2669 --
2670 end OTA_TBD_API;
[Home] [Help]