1 Package Body OTA_BST_API as
2 /* $Header: otbst01t.pkb 120.2 2005/07/21 19:01:44 dhmulia 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_BST_API.';
11 --
12 -- Global api dml status
13 --
14 g_api_dml boolean;
15 --
16 --
17 -- The following global variables are only to be used by
18 -- the set_base_key_value and pre_insert procedures.
19 --
20 g_booking_status_type_id_i number default null;
21 --
22 --
23 -- The following two global variables are only to be
24 -- used by the return_legislation_code function.
25 --
26 g_legislation_code varchar2(150) default null;
27 g_booking_status_type_id number default null;
28 --
29 -- ----------------------------------------------------------------------------
30 -- |------------------------< return_api_dml_status >-------------------------|
31 -- ----------------------------------------------------------------------------
32 Function return_api_dml_status Return Boolean Is
33 --
34 W_PROC varchar2(72) := G_PACKAGE||'return_api_dml_status';
35 --
36 Begin
37 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
38 --
39 Return (nvl(g_api_dml, false));
40 --
41 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
42 End return_api_dml_status;
43 --
44 -- ----------------------------------------------------------------------------
45 -- |---------------------------< constraint_error >---------------------------|
46 -- ----------------------------------------------------------------------------
47 -- {Start Of Comments}
48 --
49 -- Description:
50 -- This procedure is called when a constraint has been violated (i.e.
51 -- The exception hr_api.check_integrity_violated,
52 -- hr_api.parent_integrity_violated or hr_api.child_integrity_violated has
53 -- been raised).
54 -- The exceptions can only be raised as follows:
55 -- 1) A check constraint can only be violated during an INSERT or UPDATE
56 -- dml operation.
57 -- 2) A parent integrity constraint can only be violated during an
58 -- INSERT or UPDATE dml operation.
59 -- 3) A child integrity constraint can only be violated during an
60 -- DELETE dml operation.
61 --
62 -- Pre Conditions:
63 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated
64 -- or hr_api.child_integrity_violated has been raised with the subsequent
65 -- stripping of the constraint name from the generated error message text.
66 --
67 -- In Arguments:
68 -- P_CONSTRAINT_NAME is in upper format and is just the constraint name
69 -- (e.g. not prefixed by brackets, schema owner etc).
70 --
71 -- Post Success:
72 -- Development dependant.
73 --
74 -- Post Failure:
75 -- Developement dependant.
76 --
77 -- Developer Implementation Notes:
78 -- For each constraint being checked the hr system package failure message
79 -- has been generated as a template only. These system error messages should
80 -- be modified as required (i.e. change the system failure message to a user
81 -- friendly defined error message).
82 --
83 -- {End Of Comments}
84 -- ----------------------------------------------------------------------------
85 procedure CONSTRAINT_ERROR (
86 P_CONSTRAINT_NAME in ALL_CONSTRAINTS.CONSTRAINT_NAME%type
87 ) is
88 --
89 W_PROC varchar2 (72)
90 := G_PACKAGE || 'CONSTRAINT_ERROR';
91 --
92 begin
93 --
94 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
95 --
96 -- Key constraints
97 --
98 if (P_CONSTRAINT_NAME = 'OTA_BOOKING_STATUS_TYPES_FK1') Then
99 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13202_GEN_INVALID_KEY');
100 FND_MESSAGE.SET_TOKEN ('COLUMN_NAME', 'ORGANIZATION_ID');
101 FND_MESSAGE.SET_TOKEN ('TABLE_NAME', 'HR_ORGANIZATION_UNITS');
102 -- elsif (P_CONSTRAINT_NAME = 'OTA_BOOKING_STATUS_TYPES_PK') Then
103 -- Unkn constraint will be raised giving constraint name.
104 -- FND_MESSAGE.SET_NAME ('OTA', 'HR_6153_ALW_PROCEDURE_FAIL');
105 -- FND_MESSAGE.SET_TOKEN ('PROCEDURE', W_PROC);
106 -- FND_MESSAGE.SET_TOKEN ('STEP','10');
107 elsif (P_CONSTRAINT_NAME = 'OTA_BOOKING_STATUS_TYPES_UK2') Then
108 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13209_GEN_NOT_UNIQUE');
109 FND_MESSAGE.SET_TOKEN ('FIELD', 'The status name');
110 FND_MESSAGE.SET_TOKEN ('MESSAGE_EXTENSION', 'Must be unique');
111 --
112 -- Check constraints
113 --
114 elsif (P_CONSTRAINT_NAME = 'OTA_BST_ACTIVE_FLAG_CHK') Then
115 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13387_BST_ACTIVE_FLAG_CHK');
116 elsif (P_CONSTRAINT_NAME = 'OTA_BST_DEFAULT_FLAG_CHK') Then
117 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13388_BST_DEFAULT_FLAG_CHK');
118 elsif (P_CONSTRAINT_NAME = 'OTA_BST_TYPE_CHK') Then
119 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13389_BST_TYPE_CHK');
120 --
121 -- Other errors, see below
122 --
123 elsif (P_CONSTRAINT_NAME = 'OTA_BST_DUPLICATE_NAME') then
124 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13209_GEN_NOT_UNIQUE');
125 FND_MESSAGE.SET_TOKEN ('FIELD', 'Name');
126 FND_MESSAGE.SET_TOKEN ('MESSAGE_EXTENSION', 'Must be unique');
127 elsif (P_CONSTRAINT_NAME = 'OTA_BST_BSH_EXISTS') then
128 FND_MESSAGE.SET_NAME ('OTA', 'OTA_443818_NO_DEL_HAS_CHILD');
129 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', 'Booking status history');
130 elsif (P_CONSTRAINT_NAME = 'OTA_BST_TDB_EXISTS') then
131 FND_MESSAGE.SET_NAME ('OTA', 'OTA_443818_NO_DEL_HAS_CHILD');
132 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', 'Delegate booking');
133 elsif p_constraint_name = 'OTA_BST_ACTIVE_DEFAULT' then
134 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13390_BST_ACTIVE_DEFAULT');
135 elsif p_constraint_name = 'OTA_BST_DUPLICATE_DEFAULT' then
136 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13391_BST_DUPLICATE_DEFAUL');
137 --
138 -- Unknown !
139 --
140 else
141 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13259_GEN_UNKN_CONSTRAINT');
142 FND_MESSAGE.SET_TOKEN ('PROCEDURE', W_PROC);
143 FND_MESSAGE.SET_TOKEN ('CONSTRAINT', P_CONSTRAINT_NAME);
144 end if;
145 FND_MESSAGE.RAISE_ERROR;
146 --
147 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
148 --
149 end CONSTRAINT_ERROR;
150 --
151 -- ----------------------------------------------------------------------------
152 -- -------------------------------< GET_BOOKING_STATUS_TYPE_ID >---------------
153 -- ----------------------------------------------------------------------------
154 --
155 -- Retrieve the Booking Status Type ID using the NAME and
156 -- BUSINESS_GROUP_ID.
157 --
158 function GET_BOOKING_STATUS_TYPE_ID (
159 P_BUSINESS_GROUP_ID in number,
160 P_NAME in varchar2
161 ) return number is
162 --
163 W_PROCEDURE varchar2 (72)
164 := G_PACKAGE || 'GET_BOOKING_STATUS_TYPE_ID';
165 --
166 W_BOOKING_STATUS_TYPE_ID number (9);
167 --
168 cursor C1 is
169 select BST.BOOKING_STATUS_TYPE_ID
170 from OTA_BOOKING_STATUS_TYPES_VL BST
171 where BST.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
172 and BST.NAME = P_NAME;
173 --
174 begin
175 --
176 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
177 --
178 open C1;
179 fetch C1
180 into W_BOOKING_STATUS_TYPE_ID;
181 if (C1%notfound) then
182 CONSTRAINT_ERROR ('OTA_BST_UNKNOWN');
183 end if;
184 close C1;
185 --
186 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
187 --
188 return (W_BOOKING_STATUS_TYPE_ID);
189 --
190 end;
191 --
192 -- ----------------------------------------------------------------------------
193 -- ----------------------< GET_BOOKING_STATUS_TYPE_ID >---------------------------
194 -- ----------------------------------------------------------------------------
195 --
196 -- Returns the booking status NAME corresponding
197 -- to P_BOOKING_STATUS_TYPE_ID.
198 --
199 function GET_BOOKING_STATUS_TYPE (
200 P_BOOKING_STATUS_TYPE_ID in number
201 ) return varchar2 is
202 --
203 cursor C1 is
204 select BST.NAME
205 from OTA_BOOKING_STATUS_TYPES_TL BST
206 where BST.BOOKING_STATUS_TYPE_ID = P_BOOKING_STATUS_TYPE_ID
207 and BST.LANGUAGE = USERENV('LANG');
208 --
209 W_PROCEDURE varchar2 (72)
210 := G_PACKAGE || 'GET_BOOKING_STATUS_TYPE';
211 W_NAME varchar2 (80);
212 --
213 begin
214 --
215 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
216 --
217 open C1;
218 fetch C1
219 into W_NAME;
220 if (C1%notfound) then
221 CONSTRAINT_ERROR ('OTA_BST_UNKNOWN');
222 end if;
223 close C1;
224 --
225 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
226 --
227 return (W_NAME);
228 --
229 end GET_BOOKING_STATUS_TYPE;
230 --
231 -- ----------------------------------------------------------------------------
232 -- ---------------------< DEFAULT_BOOKING_STATUS_TYPE >------------------------
233 -- ----------------------------------------------------------------------------
234 --
235 -- Takes business group and booking status type and retrieves default
236 -- booking status type id and name.
237 --
238 procedure DEFAULT_BOOKING_STATUS_TYPE (
239 P_BUSINESS_GROUP_ID in number,
240 P_TYPE in varchar2,
241 P_EVENT_STATUS in varchar2,
242 P_BOOKING_STATUS_TYPE_ID out nocopy number,
243 P_NAME out nocopy varchar2
244 ) is
245 --
246 W_PROCEDURE varchar2 (72)
247 := G_PACKAGE || 'DEFAULT_BOOKING_STATUS_TYPE';
248 --
249 cursor C_DEFAULT is
250 select BST.BOOKING_STATUS_TYPE_ID,
251 BST.NAME
252 from OTA_BOOKING_STATUS_TYPES_VL BST
253 where BST.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
254 and BST.DEFAULT_FLAG = 'Y'
255 and BST.TYPE <> 'C'
256 and ((P_EVENT_STATUS = 'F' AND -- Bug #1870097. For status 'F' use Waitlist as default, for status 'P' use either Waitlisted or Requested
257 BST.TYPE = 'W')
258 or (P_EVENT_STATUS = 'P' AND
259 BST.TYPE IN ('R', 'W'))
260 or P_EVENT_STATUS NOT IN ('F', 'P'))
261 order by BST.TYPE;
262 --
263 begin
264 --
265 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
266 --
267 open C_DEFAULT;
268 fetch C_DEFAULT
269 into P_BOOKING_STATUS_TYPE_ID,
270 P_NAME;
271 close C_DEFAULT;
272 --
273 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
274 --
275 end DEFAULT_BOOKING_STATUS_TYPE;
276 --
277 -- ----------------------------------------------------------------------------
278 -- --------------------------< CHECK_UNIQUE_NAME >-----------------------------
279 -- ----------------------------------------------------------------------------
280 --
281 -- Check that a business status type name is unique within the
282 -- business group.
283 --
284 procedure CHECK_UNIQUE_NAME (
285 P_BUSINESS_GROUP_ID in number,
286 P_NAME in varchar2,
287 P_BOOKING_STATUS_TYPE_ID in number
288 ) is
289 --
290 W_PROCEDURE varchar2 (72)
291 := G_PACKAGE || 'CHECK_UNIQUE_NAME';
292 --
293 V_UNIQUE varchar (3);
294 --
295 cursor C_UNIQUE is
296 select 'NO'
297 from OTA_BOOKING_STATUS_TYPES_VL BST
298 where BST.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
299 and upper (BST.NAME) = upper (P_NAME)
300 and ( (P_BOOKING_STATUS_TYPE_ID
301 is null)
302 or (BST.BOOKING_STATUS_TYPE_ID
303 <> P_BOOKING_STATUS_TYPE_ID));
304 --
305 begin
306 --
307 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
308 --
309 open C_UNIQUE;
310 fetch C_UNIQUE
311 into V_UNIQUE;
312 if(C_UNIQUE%notfound) then
313 V_UNIQUE := 'YES';
314 end if;
315 close C_UNIQUE;
316 --
317
318 if (V_UNIQUE <> 'YES') then
319 CONSTRAINT_ERROR ('OTA_BOOKING_STATUS_TYPES_UK2');
320 end if;
321 --
322 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
323 --
324 end CHECK_UNIQUE_NAME;
325 --
326 -- ----------------------------------------------------------------------------
327 -- -------------------------< CHECK_SINGLE_DEFAULT >---------------------------
328 -- ----------------------------------------------------------------------------
329 --
330 -- Checks that within P_BUSINESS_GROUP_ID there will only be one default
331 -- status type for P_TYPE, including P_NAME, P_DEFAULT_FLAG.
332 --
333 procedure CHECK_SINGLE_DEFAULT (
334 P_BUSINESS_GROUP_ID in number,
335 P_TYPE in varchar2,
336 P_NAME in varchar2
337 ) is
338 --
339 W_PROCEDURE varchar2 (72)
340 := G_PACKAGE || 'CHECK_SINGLE_DEFAULT';
341 L_NAME VARCHAR2(80);
342
343 --
344 cursor C_DEFAULT is
345 select BST.NAME
349 and BST.DEFAULT_FLAG = 'Y';
346 from OTA_BOOKING_STATUS_TYPES_VL BST
347 where BST.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
348 and BST.TYPE = P_TYPE
350 --
351 begin
352 --
353 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
354 --
355 open C_DEFAULT;
356 fetch C_DEFAULT into L_NAME;
357 close C_DEFAULT;
358 --
359
360 if l_name is not null and l_name <> p_name then
361 CONSTRAINT_ERROR ('OTA_BST_DUPLICATE_DEFAULT');
362 end if;
363
364 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
365 --
366 end CHECK_SINGLE_DEFAULT;
367 --
368 -- ----------------------------------------------------------------------------
369 -- -------------------------------< VALIDITY_CHECK >---------------------------
370 -- ----------------------------------------------------------------------------
371 --
372 procedure VALIDITY_CHECK (
373 P_REC in G_REC_TYPE
374 ) is
375 --
376 W_PROCEDURE varchar2 (72)
377 := G_PACKAGE || 'VALIDITY_CHECK';
378
379 --
380 begin
381 --
382 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
383 --
384 CHECK_UNIQUE_NAME (
385 P_BUSINESS_GROUP_ID => P_REC.BUSINESS_GROUP_ID,
386 P_NAME => P_REC.NAME,
387 P_BOOKING_STATUS_TYPE_ID => P_REC.BOOKING_STATUS_TYPE_ID);
388 --
389 if P_REC.DEFAULT_FLAG = 'Y' AND
390 P_REC.ACTIVE_FLAG <> 'Y' then
391 CONSTRAINT_ERROR ('OTA_BST_ACTIVE_DEFAULT');
392 end if;
393
394 /* Bug 1634112 only call check_single_default if default_flag='Y'*/
395 IF P_REC.DEFAULT_FLAG ='Y' THEN
396 IF (((p_rec.booking_Status_type_id is not null) and
397 nvl(g_old_rec.default_flag,hr_api.g_varchar2) <>
398 nvl(p_rec.default_flag,hr_api.g_varchar2))
399 or (p_rec.booking_status_type_id is null)) then
400 CHECK_SINGLE_DEFAULT (
401 P_BUSINESS_GROUP_ID => P_REC.BUSINESS_GROUP_ID,
402 P_TYPE => P_REC.TYPE,
403 P_NAME => P_REC.NAME);
404 END IF;
405 END IF;
406 --
407 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
408 --
409 end VALIDITY_CHECK;
410 --
411 -- ----------------------------------------------------------------------------
412 -- -------------------------------< CHECK_BSH_EXISTS >-------------------------
413 -- ----------------------------------------------------------------------------
414 --
415 -- Delete not allowed if any OTA_BOOKING_STATUS_HISTORIES rows exist
416 -- for this BOOKING_STATUS_TYPE_ID.
417 --
418 procedure CHECK_BSH_EXISTS (
419 P_BOOKING_STATUS_TYPE_ID in number
420 ) is
421 --
422 W_PROCEDURE varchar2 (72)
423 := G_PACKAGE || 'CHECK_BSH_EXISTS';
424 --
425 W_OK varchar2 (3);
426 --
427 cursor C1 is
428 select 'NO'
429 from OTA_BOOKING_STATUS_HISTORIES
430 BSH
431 where BSH.BOOKING_STATUS_TYPE_ID
432 = P_BOOKING_STATUS_TYPE_ID;
433 --
434 begin
435 --
436 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
437 --
438 -- Any history ?
439 --
440 open C1;
441 fetch C1
442 into W_OK;
443 if (C1%found) then
444 close C1;
445 CONSTRAINT_ERROR ('OTA_BST_BSH_EXISTS');
446 end if;
447 close C1;
448 --
449 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
450 --
451 end CHECK_BSH_EXISTS;
452 --
453 -- ----------------------------------------------------------------------------
454 -- -------------------------------< CHECK_TDB_EXISTS >-------------------------
455 -- ----------------------------------------------------------------------------
456 --
457 -- Delete not allowed if any OTA_TRANING_DELEGATES rows exist for
458 -- this BOOKING_STATUS_TYPE_ID.
459 --
460 procedure CHECK_TDB_EXISTS (
461 P_BOOKING_STATUS_TYPE_ID in number
462 ) is
463 --
464 W_PROCEDURE varchar2 (72)
465 := G_PACKAGE || 'CHECK_TDB_EXISTS';
466 --
467 W_OK varchar2 (3);
468 --
469 cursor C1 is
470 select 'NO'
471 from OTA_DELEGATE_BOOKINGS TDB
472 where TDB.BOOKING_STATUS_TYPE_ID
473 = P_BOOKING_STATUS_TYPE_ID;
474 --
475 begin
476 --
477 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
478 --
479 open C1;
480 fetch C1
481 into W_OK;
482 if (C1%found) then
483 close C1;
484 CONSTRAINT_ERROR ('OTA_BST_TDB_EXISTS');
485 end if;
486 close C1;
487 --
488 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
489 --
490 end CHECK_TDB_EXISTS;
491
492 -- ----------------------------------------------------------------------------
493 -- -------------------------------< CHECK_BSE_EXISTS >-------------------------
494 -- ----------------------------------------------------------------------------
495 --
496 -- Delete not allowed if any OTA_BOOKING_STATUS_EXCL rows exist for
497 -- this BOOKING_STATUS_TYPE_ID.
498 --
499 procedure CHECK_BSE_EXISTS (
500 P_BOOKING_STATUS_TYPE_ID in number
501 ) is
502 --
503 W_PROCEDURE varchar2 (72)
504 := G_PACKAGE || 'CHECK_BSE_EXISTS';
505 --
506 W_OK varchar2 (3);
507 --
508 cursor C1 is
509 select 'NO'
510 from OTA_BOOKING_STATUS_EXCL BSE
511 where BSE.BOOKING_STATUS_TYPE_ID
512 = P_BOOKING_STATUS_TYPE_ID;
513 --
514 begin
515 --
516 HR_UTILITY.SET_LOCATION ('Entering: ' || W_PROCEDURE, 5);
517 --
518 open C1;
519 fetch C1
520 into W_OK;
521 if (C1%found) then
522 close C1;
523 fnd_message.set_name('OTA','OTA_443877_BST_DELETE_CHK');
524 fnd_message.raise_error;
525 end if;
526 close C1;
527 --
528 HR_UTILITY.SET_LOCATION (' Leaving: ' || W_PROCEDURE, 10);
529 --
530 end CHECK_BSE_EXISTS;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |--------------------------< g_old_rec_current >---------------------------|
534 -- ----------------------------------------------------------------------------
535 -- {Start Of Comments}
536 --
537 -- Description:
538 -- This function is used to populate the g_old_rec record with the current
539 -- row from the database for the specified primary key provided that the
540 -- primary key exists and is valid and does not already match the current
541 -- g_old_rec.
542 -- The function will always return a TRUE value if the g_old_rec is
543 -- populated with the current row. A FALSE value will be returned if any of
544 -- the primary key arguments are null.
545 --
546 -- Pre Conditions:
547 -- None.
548 --
549 -- In Arguments:
550 --
551 -- Post Success:
552 -- A value of TRUE will be returned indiciating that the g_old_rec is
553 -- current.
554 -- A value of FALSE will be returned if any of the primary key arguments
555 -- has a null value (this indicates that the row has not be inserted into
556 -- the Schema), and therefore could never have a corresponding row.
557 --
558 -- Post Failure:
559 -- A failure can only occur under two circumstances:
560 -- 1) The primary key is invalid (i.e. a row does not exist for the
561 -- specified primary key values).
562 -- 2) If an object_version_number exists but is NOT the same as the current
563 -- g_old_rec value.
564 --
565 -- Developer Implementation Notes:
566 -- None.
567 --
568 -- {End Of Comments}
569 -- ----------------------------------------------------------------------------
570 Function g_old_rec_current
571 (
572 p_booking_status_type_id in number,
573 p_object_version_number in number
574 ) Return Boolean Is
575 --
576 --
577 -- Cursor selects the 'current' row from the HR Schema
578 --
579 Cursor C_Sel1 is
580 select
581 booking_status_type_id,
582 business_group_id,
583 active_flag,
584 default_flag,
585 name,
586 object_version_number,
587 type,
588 comments,
589 description,
590 bst_information_category,
591 bst_information1,
592 bst_information2,
593 bst_information3,
594 bst_information4,
595 bst_information5,
596 bst_information6,
597 bst_information7,
598 bst_information8,
599 bst_information9,
600 bst_information10,
601 bst_information11,
602 bst_information12,
603 bst_information13,
604 bst_information14,
605 bst_information15,
606 bst_information16,
607 bst_information17,
608 bst_information18,
609 bst_information19,
610 bst_information20
611 from ota_booking_status_types_vl
612 where booking_status_type_id = p_booking_status_type_id;
613 --
614 W_PROC varchar2(72) := G_PACKAGE||'g_old_rec_current';
615 l_fct_ret boolean;
616 --
617 Begin
618 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
619 --
620 If (
621 p_booking_status_type_id is null or
622 p_object_version_number is null
623 ) Then
624 --
625 -- One of the primary key arguments is null therefore we must
626 -- set the returning function value to false
627 --
628 l_fct_ret := false;
629 Else
630 If (
631 p_booking_status_type_id = g_old_rec.booking_status_type_id and
632 p_object_version_number = g_old_rec.object_version_number
633 ) Then
634 HR_UTILITY.SET_LOCATION(W_PROC, 10);
635 --
636 -- The g_old_rec is current therefore we must
637 -- set the returning function to true
638 --
639 l_fct_ret := true;
640 Else
641 --
642 -- Select the current row
643 --
644 Open C_Sel1;
645 Fetch C_Sel1 Into g_old_rec;
646 If C_Sel1%notfound Then
647 Close C_Sel1;
648 --
649 -- The primary key is invalid therefore we must error
650 --
651 FND_MESSAGE.SET_NAME('OTA', 'HR_7220_INVALID_PRIMARY_KEY');
652 HR_UTILITY.raise_error;
653 End If;
654 Close C_Sel1;
655 If (p_object_version_number <> g_old_rec.object_version_number) Then
656 FND_MESSAGE.SET_NAME('OTA', 'HR_7155_OBJECT_INVALID');
657 HR_UTILITY.raise_error;
658 End If;
659 HR_UTILITY.SET_LOCATION(W_PROC, 15);
660 l_fct_ret := true;
661 End If;
662 End If;
663 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 20);
664 Return (l_fct_ret);
665 --
666 End g_old_rec_current;
667 --
668 --
669 -- ---------------------------------------------------------------------------
670 -- |----------------------< set_security_group_id >--------------------------|
671 -- ---------------------------------------------------------------------------
672 --
673 Procedure set_security_group_id
674 (p_booking_status_type_id in number
675 ,p_associated_column1 in varchar2 default null
676 ) is
677 --
678 -- Declare cursor
679 --
680 cursor csr_sec_grp is
681 select pbg.security_group_id,
682 pbg.legislation_code
683 from per_business_groups_perf pbg
684 , ota_booking_status_types bst
685 where bst.booking_status_type_id = p_booking_status_type_id
686 and pbg.business_group_id = bst.business_group_id;
687 --
688 -- Declare local variables
689 --
690 l_security_group_id number;
691 l_proc varchar2(72) := g_package||'set_security_group_id';
692 l_legislation_code varchar2(150);
693 --
694 begin
695 --
696 hr_utility.set_location('Entering:'|| l_proc, 10);
697 --
698 -- Ensure that all the mandatory parameter are not null
699 --
700 hr_api.mandatory_arg_error
701 (p_api_name => l_proc
702 ,p_argument => 'booking_status_type_id'
703 ,p_argument_value => p_booking_status_type_id
704 );
705 --
706 open csr_sec_grp;
707 fetch csr_sec_grp into l_security_group_id
708 , l_legislation_code;
709 --
710 if csr_sec_grp%notfound then
711 --
712 close csr_sec_grp;
713 --
714 -- The primary key is invalid therefore we must error
715 --
716 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
717 hr_multi_message.add
718 (p_associated_column1
719 => nvl(p_associated_column1,'BOOKING_STATUS_TYPE_ID')
720 );
721 --
722 else
723 close csr_sec_grp;
724 --
725 -- Set the security_group_id in CLIENT_INFO
726 --
727 hr_api.set_security_group_id
728 (p_security_group_id => l_security_group_id
729 );
730 --
731 -- Set the sessions legislation context in HR_SESSION_DATA
732 --
733 hr_api.set_legislation_context(l_legislation_code);
734 end if;
735 --
736 hr_utility.set_location(' Leaving:'|| l_proc, 20);
737 --
738 end set_security_group_id;
739 --
740 -- ---------------------------------------------------------------------------
741 -- |---------------------< return_legislation_code >-------------------------|
742 -- ---------------------------------------------------------------------------
743 --
744 Function return_legislation_code
745 (p_booking_status_type_id in number
746 )
747 Return Varchar2 Is
748 --
749 -- Declare cursor
750 --
751 cursor csr_leg_code is
752 select pbg.legislation_code
753 from per_business_groups_perf pbg
754 , ota_booking_status_types bst
755 where bst.booking_status_type_id = p_booking_status_type_id
756 and pbg.business_group_id = bst.business_group_id;
757 --
758 -- Declare local variables
759 --
760 l_legislation_code varchar2(150);
761 l_proc varchar2(72) := g_package||'return_legislation_code';
762 --
763 Begin
764 --
765 hr_utility.set_location('Entering:'|| l_proc, 10);
766 --
767 -- Ensure that all the mandatory parameter are not null
768 --
769 hr_api.mandatory_arg_error
770 (p_api_name => l_proc
771 ,p_argument => 'booking_status_type_id'
772 ,p_argument_value => p_booking_status_type_id
773 );
774 --
775 if ( nvl(ota_bst_api.g_booking_status_type_id, hr_api.g_number)
776 = p_booking_status_type_id) then
777 --
778 -- The legislation code has already been found with a previous
779 -- call to this function. Just return the value in the global
780 -- variable.
781 --
782 l_legislation_code := ota_bst_api.g_legislation_code;
783 hr_utility.set_location(l_proc, 20);
784 else
785 --
786 -- The ID is different to the last call to this function
787 -- or this is the first call to this function.
788 --
789 open csr_leg_code;
790 fetch csr_leg_code into l_legislation_code;
791 --
792 if csr_leg_code%notfound then
793 --
794 -- The primary key is invalid therefore we must error
795 --
796 close csr_leg_code;
797 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
798 fnd_message.raise_error;
799 end if;
800 hr_utility.set_location(l_proc,30);
801 --
802 -- Set the global variables so the values are
803 -- available for the next call to this function.
804 --
805 close csr_leg_code;
806 ota_bst_api.g_booking_status_type_id := p_booking_status_type_id;
807 ota_bst_api.g_legislation_code := l_legislation_code;
808 end if;
809 hr_utility.set_location(' Leaving:'|| l_proc, 40);
810 return l_legislation_code;
811 end return_legislation_code;
812 --
813
814 --
815 -- ----------------------------------------------------------------------------
816 -- |------------------------< set_base_key_value >----------------------------|
817 -- ----------------------------------------------------------------------------
818 procedure set_base_key_value
819 (p_booking_status_type_id in number) is
820 --
821 l_proc varchar2(72) := g_package||'set_base_key_value';
822 --
823 Begin
824 hr_utility.set_location('Entering:'||l_proc, 10);
825 --
826 ota_bst_api.g_booking_status_type_id_i := p_booking_status_type_id;
827 --
828 hr_utility.set_location(' Leaving:'||l_proc, 20);
829 End set_base_key_value;
830 --
831 --
832 -- ----------------------------------------------------------------------------
833 -- |------------------------------< insert_dml >------------------------------|
834 -- ----------------------------------------------------------------------------
835 -- {Start Of Comments}
836 --
837 -- Description:
838 -- This procedure controls the actual dml insert logic. The functions of this
839 -- procedure are as follows:
840 -- 1) Initialise the object_version_number to 1 if the object_version_number
841 -- is defined as an attribute for this entity.
842 -- 2) To set and unset the g_api_dml status as required (as we are about to
843 -- perform dml).
844 -- 3) To insert the row into the schema.
845 -- 4) To trap any constraint violations that may have occurred.
846 -- 5) To raise any other errors.
847 --
848 -- Pre Conditions:
849 -- This is an internal private procedure which must be called from the ins
850 -- procedure and must have all mandatory arguments set (except the
851 -- object_version_number which is initialised within this procedure).
852 --
853 -- In Arguments:
854 -- A Pl/Sql record structre.
855 --
856 -- Post Success:
857 -- The specified row will be inserted into the schema.
858 --
859 -- Post Failure:
860 -- On the insert dml failure it is important to note that we always reset the
861 -- g_api_dml status to false.
862 -- If a check, unique or parent integrity constraint violation is raised the
863 -- constraint_error procedure will be called.
864 -- If any other error is reported, the error will be raised after the
865 -- g_api_dml status is reset.
866 --
867 -- Developer Implementation Notes:
868 -- None.
869 --
870 -- {End Of Comments}
871 -- ----------------------------------------------------------------------------
872 Procedure insert_dml(p_rec in out nocopy g_rec_type) is
873 --
874 W_PROC varchar2(72) := G_PACKAGE||'insert_dml';
875 --
876 Begin
877 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
878 p_rec.object_version_number := 1; -- Initialise the object version
879 --
880 g_api_dml := true; -- Set the api dml status
881 --
882 -- Insert the row into: ota_booking_status_types
883 --
884 insert into ota_booking_status_types
885 ( booking_status_type_id,
886 business_group_id,
887 active_flag,
888 default_flag,
889 name,
890 object_version_number,
891 type,
892 comments,
893 description,
894 bst_information_category,
895 bst_information1,
896 bst_information2,
897 bst_information3,
898 bst_information4,
899 bst_information5,
900 bst_information6,
901 bst_information7,
902 bst_information8,
903 bst_information9,
904 bst_information10,
905 bst_information11,
906 bst_information12,
907 bst_information13,
908 bst_information14,
909 bst_information15,
910 bst_information16,
911 bst_information17,
912 bst_information18,
913 bst_information19,
914 bst_information20
915 )
916 Values
917 ( p_rec.booking_status_type_id,
918 p_rec.business_group_id,
919 p_rec.active_flag,
923 p_rec.type,
920 p_rec.default_flag,
921 p_rec.name,
922 p_rec.object_version_number,
924 p_rec.comments,
925 p_rec.description,
926 p_rec.bst_information_category,
927 p_rec.bst_information1,
928 p_rec.bst_information2,
929 p_rec.bst_information3,
930 p_rec.bst_information4,
931 p_rec.bst_information5,
932 p_rec.bst_information6,
933 p_rec.bst_information7,
934 p_rec.bst_information8,
935 p_rec.bst_information9,
936 p_rec.bst_information10,
937 p_rec.bst_information11,
938 p_rec.bst_information12,
939 p_rec.bst_information13,
940 p_rec.bst_information14,
941 p_rec.bst_information15,
942 p_rec.bst_information16,
943 p_rec.bst_information17,
944 p_rec.bst_information18,
945 p_rec.bst_information19,
946 p_rec.bst_information20
947 );
948 --
949 g_api_dml := false; -- Unset the api dml status
950 --
951 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
952 Exception
953 When hr_api.check_integrity_violated Then
954 -- A check constraint has been violated
955 g_api_dml := false; -- Unset the api dml status
956 constraint_error
957 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
958 When hr_api.parent_integrity_violated Then
959 -- Parent integrity has been violated
960 g_api_dml := false; -- Unset the api dml status
961 constraint_error
962 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
963 When hr_api.unique_integrity_violated Then
964 -- Unique integrity has been violated
965 g_api_dml := false; -- Unset the api dml status
966 constraint_error
967 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
968 When Others Then
969 g_api_dml := false; -- Unset the api dml status
970 Raise;
971 End insert_dml;
972 --
973 -- ----------------------------------------------------------------------------
974 -- |------------------------------< update_dml >------------------------------|
975 -- ----------------------------------------------------------------------------
976 -- {Start Of Comments}
977 --
978 -- Description:
979 -- This procedure controls the actual dml update logic. The functions of this
980 -- procedure are as follows:
981 -- 1) Increment the object_version_number by 1 if the object_version_number
982 -- is defined as an attribute for this entity.
983 -- 2) To set and unset the g_api_dml status as required (as we are about to
984 -- perform dml).
985 -- 3) To update the specified row in the schema using the primary key in
986 -- the predicates.
987 -- 4) To trap any constraint violations that may have occurred.
988 -- 5) To raise any other errors.
989 --
990 -- Pre Conditions:
991 -- This is an internal private procedure which must be called from the upd
992 -- procedure.
993 --
994 -- In Arguments:
995 -- A Pl/Sql record structre.
996 --
997 -- Post Success:
998 -- The specified row will be updated in the schema.
999 --
1000 -- Post Failure:
1001 -- On the update dml failure it is important to note that we always reset the
1002 -- g_api_dml status to false.
1003 -- If a check, unique or parent integrity constraint violation is raised the
1004 -- constraint_error procedure will be called.
1005 -- If any other error is reported, the error will be raised after the
1006 -- g_api_dml status is reset.
1007 --
1008 -- Developer Implementation Notes:
1009 -- The update 'set' arguments list should be modified if any of your
1010 -- attributes are not updateable.
1011 --
1012 -- {End Of Comments}
1013 -- ----------------------------------------------------------------------------
1014 Procedure update_dml(p_rec in out nocopy g_rec_type) is
1015 --
1016 W_PROC varchar2(72) := G_PACKAGE||'update_dml';
1017 --
1018 Begin
1019 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1020 --
1021 -- Increment the object version
1022 --
1023 p_rec.object_version_number := p_rec.object_version_number + 1;
1024 --
1025 g_api_dml := true; -- Set the api dml status
1026 --
1027 -- Update the ota_booking_status_types Row
1028 --
1029 update ota_booking_status_types
1030 set
1031 booking_status_type_id = p_rec.booking_status_type_id,
1032 business_group_id = p_rec.business_group_id,
1033 active_flag = p_rec.active_flag,
1037 type = p_rec.type,
1034 default_flag = p_rec.default_flag,
1035 name = p_rec.name,
1036 object_version_number = p_rec.object_version_number,
1038 comments = p_rec.comments,
1039 description = p_rec.description,
1040 bst_information_category = p_rec.bst_information_category,
1041 bst_information1 = p_rec.bst_information1,
1042 bst_information2 = p_rec.bst_information2,
1043 bst_information3 = p_rec.bst_information3,
1044 bst_information4 = p_rec.bst_information4,
1045 bst_information5 = p_rec.bst_information5,
1046 bst_information6 = p_rec.bst_information6,
1047 bst_information7 = p_rec.bst_information7,
1048 bst_information8 = p_rec.bst_information8,
1049 bst_information9 = p_rec.bst_information9,
1050 bst_information10 = p_rec.bst_information10,
1051 bst_information11 = p_rec.bst_information11,
1052 bst_information12 = p_rec.bst_information12,
1053 bst_information13 = p_rec.bst_information13,
1054 bst_information14 = p_rec.bst_information14,
1055 bst_information15 = p_rec.bst_information15,
1056 bst_information16 = p_rec.bst_information16,
1057 bst_information17 = p_rec.bst_information17,
1058 bst_information18 = p_rec.bst_information18,
1059 bst_information19 = p_rec.bst_information19,
1060 bst_information20 = p_rec.bst_information20
1061 where booking_status_type_id = p_rec.booking_status_type_id;
1062 --
1063 g_api_dml := false; -- Unset the api dml status
1064 --
1065 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1066 --
1067 Exception
1068 When hr_api.check_integrity_violated Then
1069 -- A check constraint has been violated
1070 g_api_dml := false; -- Unset the api dml status
1071 constraint_error
1072 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
1073 When hr_api.parent_integrity_violated Then
1074 -- Parent integrity has been violated
1075 g_api_dml := false; -- Unset the api dml status
1076 constraint_error
1080 g_api_dml := false; -- Unset the api dml status
1077 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
1078 When hr_api.unique_integrity_violated Then
1079 -- Unique integrity has been violated
1081 constraint_error
1082 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
1083 When Others Then
1084 g_api_dml := false; -- Unset the api dml status
1085 Raise;
1086 End update_dml;
1087 --
1088 -- ----------------------------------------------------------------------------
1089 -- |------------------------------< delete_dml >------------------------------|
1090 -- ----------------------------------------------------------------------------
1091 -- {Start Of Comments}
1092 --
1093 -- Description:
1094 -- This procedure controls the actual dml delete logic. The functions of this
1095 -- procedure are as follows:
1096 -- 1) To set and unset the g_api_dml status as required (as we are about to
1097 -- perform dml).
1098 -- 2) To delete the specified row from the schema using the primary key in
1099 -- the predicates.
1100 -- 3) To trap any constraint violations that may have occurred.
1101 -- 4) To raise any other errors.
1102 --
1103 -- Pre Conditions:
1104 -- This is an internal private procedure which must be called from the del
1105 -- procedure.
1106 --
1107 -- In Arguments:
1108 -- A Pl/Sql record structre.
1109 --
1110 -- Post Success:
1111 -- The specified row will be delete from the schema.
1112 --
1113 -- Post Failure:
1114 -- On the delete dml failure it is important to note that we always reset the
1115 -- g_api_dml status to false.
1116 -- If a child integrity constraint violation is raised the
1117 -- constraint_error procedure will be called.
1118 -- If any other error is reported, the error will be raised after the
1119 -- g_api_dml status is reset.
1120 --
1121 -- Developer Implementation Notes:
1122 -- None.
1123 --
1124 -- {End Of Comments}
1125 -- ----------------------------------------------------------------------------
1126 Procedure delete_dml(p_rec in g_rec_type) is
1127 --
1128 W_PROC varchar2(72) := G_PACKAGE||'delete_dml';
1129 --
1130 Begin
1131 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1132 --
1133 g_api_dml := true; -- Set the api dml status
1134 --
1135 -- Delete the ota_booking_status_types row.
1136 --
1137 delete from ota_booking_status_types
1141 --
1138 where booking_status_type_id = p_rec.booking_status_type_id;
1139 --
1140 g_api_dml := false; -- Unset the api dml status
1142 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1143 --
1144 Exception
1145 When hr_api.child_integrity_violated then
1146 -- Child integrity has been violated
1147 g_api_dml := false; -- Unset the api dml status
1148 constraint_error
1149 (P_CONSTRAINT_NAME => hr_api.strip_constraint_name(SQLERRM));
1150 When Others Then
1151 g_api_dml := false; -- Unset the api dml status
1152 Raise;
1153 End delete_dml;
1154 --
1155 -- ----------------------------------------------------------------------------
1156 -- |------------------------------< pre_insert >------------------------------|
1157 -- ----------------------------------------------------------------------------
1158 -- {Start Of Comments}
1159 --
1160 -- Description:
1161 -- This private procedure contains any processing which is required before
1162 -- the insert dml. Presently, if the entity has a corresponding primary
1163 -- key which is maintained by an associating sequence, the primary key for
1164 -- the entity will be populated with the next sequence value in
1165 -- preparation for the insert dml.
1166 --
1167 -- Pre Conditions:
1168 -- This is an internal procedure which is called from the ins procedure.
1169 --
1170 -- In Arguments:
1171 -- A Pl/Sql record structre.
1172 --
1173 -- Post Success:
1174 -- Processing continues.
1175 --
1176 -- Post Failure:
1177 -- If an error has occurred, an error message and exception will be raised
1178 -- but not handled.
1179 --
1180 -- Developer Implementation Notes:
1181 -- Any pre-processing required before the insert dml is issued should be
1182 -- coded within this procedure. As stated above, a good example is the
1183 -- generation of a primary key number via a corresponding sequence.
1184 -- It is important to note that any 3rd party maintenance should be reviewed
1185 -- before placing in this procedure.
1186 --
1187 -- {End Of Comments}
1188 -- ----------------------------------------------------------------------------
1189 Procedure pre_insert(p_rec in out nocopy g_rec_type) is
1190 --
1191 W_PROC varchar2(72) := G_PACKAGE||'pre_insert';
1192 --
1193 Cursor C_Sel1 is select ota_booking_status_types_s.nextval from sys.dual;
1194 --
1195 Begin
1196 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1197 --
1198 --
1199 -- Select the next sequence number
1200 --
1201 Open C_Sel1;
1202 Fetch C_Sel1 Into p_rec.booking_status_type_id;
1203 Close C_Sel1;
1204 --
1205 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1206 End pre_insert;
1207 --
1208 -- ----------------------------------------------------------------------------
1209 -- |------------------------------< pre_update >------------------------------|
1210 -- ----------------------------------------------------------------------------
1211 -- {Start Of Comments}
1212 --
1213 -- Description:
1214 -- This private procedure contains any processing which is required before
1215 -- the update dml.
1216 --
1217 -- Pre Conditions:
1218 -- This is an internal procedure which is called from the upd procedure.
1219 --
1220 -- In Arguments:
1221 -- A Pl/Sql record structre.
1222 --
1223 -- Post Success:
1224 -- Processing continues.
1225 --
1226 -- Post Failure:
1227 -- If an error has occurred, an error message and exception will be raised
1228 -- but not handled.
1229 --
1230 -- Developer Implementation Notes:
1231 -- Any pre-processing required before the update dml is issued should be
1232 -- coded within this procedure. It is important to note that any 3rd party
1233 -- maintenance should be reviewed before placing in this procedure.
1234 --
1235 -- {End Of Comments}
1236 -- ----------------------------------------------------------------------------
1237 Procedure pre_update(p_rec in g_rec_type) is
1238 --
1239 W_PROC varchar2(72) := G_PACKAGE||'pre_update';
1240 --
1241 Begin
1242 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1243 --
1244 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1245 End pre_update;
1246 --
1247 -- ----------------------------------------------------------------------------
1251 --
1248 -- |------------------------------< pre_delete >------------------------------|
1249 -- ----------------------------------------------------------------------------
1250 -- {Start Of Comments}
1252 -- Description:
1253 -- This private procedure contains any processing which is required before
1254 -- the delete dml.
1255 --
1256 -- Pre Conditions:
1257 -- This is an internal procedure which is called from the del procedure.
1258 --
1259 -- In Arguments:
1260 -- A Pl/Sql record structre.
1261 --
1262 -- Post Success:
1263 -- Processing continues.
1264 --
1265 -- Post Failure:
1266 -- If an error has occurred, an error message and exception will be raised
1267 -- but not handled.
1268 --
1269 -- Developer Implementation Notes:
1270 -- Any pre-processing required before the delete dml is issued should be
1271 -- coded within this procedure. It is important to note that any 3rd party
1272 -- maintenance should be reviewed before placing in this procedure.
1273 --
1274 -- {End Of Comments}
1275 -- ----------------------------------------------------------------------------
1276 Procedure pre_delete(p_rec in g_rec_type) is
1277 --
1278 W_PROC varchar2(72) := G_PACKAGE||'pre_delete';
1279 --
1280 Begin
1281 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1282 --
1283 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1284 End pre_delete;
1285 --
1286 -- ----------------------------------------------------------------------------
1287 -- |-----------------------------< post_insert >------------------------------|
1291 -- Description:
1288 -- ----------------------------------------------------------------------------
1289 -- {Start Of Comments}
1290 --
1292 -- This private procedure contains any processing which is required after the
1293 -- insert dml.
1294 --
1295 -- Pre Conditions:
1296 -- This is an internal procedure which is called from the ins procedure.
1297 --
1298 -- In Arguments:
1299 -- A Pl/Sql record structre.
1300 --
1301 -- Post Success:
1302 -- Processing continues.
1303 --
1304 -- Post Failure:
1305 -- If an error has occurred, an error message and exception will be raised
1306 -- but not handled.
1307 --
1308 -- Developer Implementation Notes:
1309 -- Any post-processing required after the insert dml is issued should be
1310 -- coded within this procedure. It is important to note that any 3rd party
1311 -- maintenance should be reviewed before placing in this procedure.
1312 --
1313 -- {End Of Comments}
1314 -- ----------------------------------------------------------------------------
1315 Procedure post_insert(p_rec in g_rec_type) is
1316 --
1317 W_PROC varchar2(72) := G_PACKAGE||'post_insert';
1318 --
1319 Begin
1320 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1321 --
1322 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1323 End post_insert;
1324 --
1325 -- ----------------------------------------------------------------------------
1326 -- |-----------------------------< post_update >------------------------------|
1327 -- ----------------------------------------------------------------------------
1328 -- {Start Of Comments}
1329 --
1330 -- Description:
1331 -- This private procedure contains any processing which is required after the
1332 -- update dml.
1333 --
1334 -- Pre Conditions:
1335 -- This is an internal procedure which is called from the upd procedure.
1336 --
1337 -- In Arguments:
1338 -- A Pl/Sql record structre.
1339 --
1340 -- Post Success:
1341 -- Processing continues.
1342 --
1343 -- Post Failure:
1344 -- If an error has occurred, an error message and exception will be raised
1345 -- but not handled.
1346 --
1347 -- Developer Implementation Notes:
1348 -- Any post-processing required after the update dml is issued should be
1349 -- coded within this procedure. It is important to note that any 3rd party
1350 -- maintenance should be reviewed before placing in this procedure.
1351 --
1352 -- {End Of Comments}
1353 -- ----------------------------------------------------------------------------
1354 Procedure post_update(p_rec in g_rec_type) is
1355 --
1356 W_PROC varchar2(72) := G_PACKAGE||'post_update';
1357 --
1358 Begin
1359 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1360 --
1361 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1362 End post_update;
1363 --
1364 -- ----------------------------------------------------------------------------
1365 -- |-----------------------------< post_delete >------------------------------|
1366 -- ----------------------------------------------------------------------------
1367 -- {Start Of Comments}
1368 --
1369 -- Description:
1370 -- This private procedure contains any processing which is required after the
1371 -- delete dml.
1372 --
1373 -- Pre Conditions:
1374 -- This is an internal procedure which is called from the del procedure.
1375 --
1376 -- In Arguments:
1377 -- A Pl/Sql record structre.
1378 --
1379 -- Post Success:
1380 -- Processing continues.
1381 --
1382 -- Post Failure:
1383 -- If an error has occurred, an error message and exception will be raised
1384 -- but not handled.
1385 --
1386 -- Developer Implementation Notes:
1387 -- Any post-processing required after the delete dml is issued should be
1388 -- coded within this procedure. It is important to note that any 3rd party
1389 -- maintenance should be reviewed before placing in this procedure.
1390 --
1391 -- {End Of Comments}
1392 -- ----------------------------------------------------------------------------
1393 Procedure post_delete(p_rec in g_rec_type) is
1394 --
1395 W_PROC varchar2(72) := G_PACKAGE||'post_delete';
1396 --
1397 Begin
1398 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1399 --
1400 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1401 End post_delete;
1402 --
1403 -- ----------------------------------------------------------------------------
1404 -- |---------------------------------< lck >----------------------------------|
1405 -- ----------------------------------------------------------------------------
1406 Procedure lck
1407 (
1408 p_booking_status_type_id in number,
1409 p_object_version_number in number
1410 ) is
1411 --
1412 -- Cursor selects the 'current' row from the HR Schema
1413 --
1414 Cursor C_Sel1 is
1415 select booking_status_type_id,
1416 business_group_id,
1417 active_flag,
1418 default_flag,
1419 name,
1420 object_version_number,
1421 type,
1422 comments,
1423 description,
1424 bst_information_category,
1425 bst_information1,
1426 bst_information2,
1427 bst_information3,
1428 bst_information4,
1429 bst_information5,
1430 bst_information6,
1431 bst_information7,
1432 bst_information8,
1433 bst_information9,
1434 bst_information10,
1435 bst_information11,
1436 bst_information12,
1437 bst_information13,
1438 bst_information14,
1439 bst_information15,
1440 bst_information16,
1441 bst_information17,
1442 bst_information18,
1443 bst_information19,
1444 bst_information20
1448 --
1445 from ota_booking_status_types
1446 where booking_status_type_id = p_booking_status_type_id
1447 for update nowait;
1449 W_PROC varchar2(72) := G_PACKAGE||'lck';
1450 --
1451 Begin
1452 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1453 --
1454 -- Add any mandatory argument checking here:
1455 -- Example:
1456 -- hr_api.mandatory_arg_error
1457 -- (p_api_name => W_PROC,
1458 -- p_argument => 'object_version_number',
1459 -- p_argument_value => p_object_version_number);
1460 --
1461 Open C_Sel1;
1462 Fetch C_Sel1 Into g_old_rec;
1463 If C_Sel1%notfound then
1464 Close C_Sel1;
1465 --
1466 -- The primary key is invalid therefore we must error
1467 --
1468 FND_MESSAGE.SET_NAME('OTA', 'HR_7220_INVALID_PRIMARY_KEY');
1469 HR_UTILITY.raise_error;
1470 End If;
1471 Close C_Sel1;
1472 If (p_object_version_number <> g_old_rec.object_version_number) Then
1473 FND_MESSAGE.SET_NAME('OTA', 'HR_7155_OBJECT_INVALID');
1474 HR_UTILITY.raise_error;
1475 End If;
1476 --
1477 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1478 --
1479 -- We need to trap the ORA LOCK exception
1480 --
1481 Exception
1482 When HR_Api.Object_Locked then
1483 --
1484 -- The object is locked therefore we need to supply a meaningful
1485 -- error message.
1486 --
1487 FND_MESSAGE.SET_NAME('OTA', 'HR_7165_OBJECT_LOCKED');
1488 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'ota_booking_status_types');
1489 HR_UTILITY.raise_error;
1490 End lck;
1491 --
1492 -- ----------------------------------------------------------------------------
1493 -- |-----------------------------< convert_args >-----------------------------|
1494 -- ----------------------------------------------------------------------------
1495 -- {Start Of Comments}
1496 --
1497 -- Description:
1498 -- This function is used to turn attribute arguments into the record
1499 -- structure g_rec_type.
1500 --
1501 -- Pre Conditions:
1502 -- This is a private function and can only be called from the ins or upd
1503 -- attribute processes.
1504 --
1505 -- In Arguments:
1506 --
1507 -- Post Success:
1508 -- A returning record structure will be returned.
1509 --
1510 -- Post Failure:
1511 -- No direct error handling is required within this function. Any possible
1512 -- errors within this function will be a PL/SQL value error due to conversion
1513 -- of datatypes or data lengths.
1514 --
1515 -- Developer Implementation Notes:
1516 --
1517 -- {End Of Comments}
1518 -- ----------------------------------------------------------------------------
1519 Function convert_args
1520 (
1521 p_booking_status_type_id in number,
1522 p_business_group_id in number,
1523 p_active_flag in varchar2,
1524 p_default_flag in varchar2,
1525 p_name in varchar2,
1526 p_object_version_number in number,
1527 p_type in varchar2,
1528 p_comments in varchar2,
1529 p_description in varchar2,
1530 p_bst_information_category in varchar2,
1531 p_bst_information1 in varchar2,
1532 p_bst_information2 in varchar2,
1533 p_bst_information3 in varchar2,
1534 p_bst_information4 in varchar2,
1535 p_bst_information5 in varchar2,
1536 p_bst_information6 in varchar2,
1537 p_bst_information7 in varchar2,
1538 p_bst_information8 in varchar2,
1539 p_bst_information9 in varchar2,
1540 p_bst_information10 in varchar2,
1541 p_bst_information11 in varchar2,
1542 p_bst_information12 in varchar2,
1543 p_bst_information13 in varchar2,
1544 p_bst_information14 in varchar2,
1545 p_bst_information15 in varchar2,
1546 p_bst_information16 in varchar2,
1547 p_bst_information17 in varchar2,
1548 p_bst_information18 in varchar2,
1549 p_bst_information19 in varchar2,
1550 p_bst_information20 in varchar2
1551 )
1552 Return g_rec_type is
1553 --
1554 l_rec g_rec_type;
1555 W_PROC varchar2(72) := G_PACKAGE||'convert_args';
1556 --
1557 Begin
1558 --
1559 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1560 --
1561 -- Convert arguments into local l_rec structure.
1562 --
1563 l_rec.booking_status_type_id := p_booking_status_type_id;
1564 l_rec.business_group_id := p_business_group_id;
1565 l_rec.active_flag := p_active_flag;
1566 l_rec.default_flag := p_default_flag;
1567 l_rec.name := p_name;
1568 l_rec.object_version_number := p_object_version_number;
1569 l_rec.type := p_type;
1570 l_rec.comments := p_comments;
1571 l_rec.description := p_description;
1572 l_rec.bst_information_category := p_bst_information_category;
1573 l_rec.bst_information1 := p_bst_information1;
1574 l_rec.bst_information2 := p_bst_information2;
1575 l_rec.bst_information3 := p_bst_information3;
1576 l_rec.bst_information4 := p_bst_information4;
1577 l_rec.bst_information5 := p_bst_information5;
1578 l_rec.bst_information6 := p_bst_information6;
1579 l_rec.bst_information7 := p_bst_information7;
1583 l_rec.bst_information11 := p_bst_information11;
1580 l_rec.bst_information8 := p_bst_information8;
1581 l_rec.bst_information9 := p_bst_information9;
1582 l_rec.bst_information10 := p_bst_information10;
1584 l_rec.bst_information12 := p_bst_information12;
1585 l_rec.bst_information13 := p_bst_information13;
1586 l_rec.bst_information14 := p_bst_information14;
1587 l_rec.bst_information15 := p_bst_information15;
1588 l_rec.bst_information16 := p_bst_information16;
1589 l_rec.bst_information17 := p_bst_information17;
1590 l_rec.bst_information18 := p_bst_information18;
1591 l_rec.bst_information19 := p_bst_information19;
1592 l_rec.bst_information20 := p_bst_information20;
1593 --
1594 -- Return the plsql record structure.
1595 --
1596 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1597 Return(l_rec);
1598 --
1599 End convert_args;
1600 --
1601 -- ----------------------------------------------------------------------------
1602 -- |-----------------------------< convert_defs >-----------------------------|
1603 -- ----------------------------------------------------------------------------
1604 -- {Start Of Comments}
1605 --
1606 -- Description:
1607 -- The Convert_Defs function has one very important function:
1608 -- It must return the record structure for the row with all system defaulted
1609 -- values converted into its corresponding argument value for update. When
1610 -- we attempt to update a row through the Upd business process , certain
1611 -- arguments can be defaulted which enables flexibility in the calling of
1612 -- the upd process (e.g. only attributes which need to be updated need to be
1613 -- specified). For the upd business process to determine which attributes
1614 -- have NOT been specified we need to check if the argument has a reserved
1615 -- system default value. Therefore, for all attributes which have a
1616 -- corresponding reserved system default mechanism specified we need to
1617 -- check if a system default is being used. If a system default is being
1618 -- used then we convert the defaulted value into its corresponding attribute
1619 -- value held in the g_old_rec data structure.
1620 --
1621 -- Pre Conditions:
1622 -- This private function can only be called from the upd process.
1623 --
1624 -- In Arguments:
1625 -- A Pl/Sql record structre.
1626 --
1627 -- Post Success:
1628 -- The record structure will be returned with all system defaulted argument
1629 -- values converted into its current row attribute value.
1630 --
1631 -- Post Failure:
1632 -- No direct error handling is required within this function. Any possible
1633 -- errors within this function will be a PL/SQL value error due to conversion
1634 -- of datatypes or data lengths.
1635 --
1636 -- Developer Implementation Notes:
1637 --
1638 -- {End Of Comments}
1639 -- ----------------------------------------------------------------------------
1640 Function convert_defs(p_rec in out nocopy g_rec_type)
1641 Return g_rec_type is
1642 --
1643 W_PROC varchar2(72) := G_PACKAGE||'convert_defs';
1644 --
1645 Begin
1646 --
1647 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1648 --
1649 -- We must now examine each argument value in the
1650 -- p_rec plsql record structure
1651 -- to see if a system default is being used. If a system default
1652 -- is being used then we must set to the 'current' argument value.
1653 --
1654 If (p_rec.business_group_id = hr_api.g_number) then
1655 p_rec.business_group_id := g_old_rec.business_group_id;
1656 End If;
1657 If (p_rec.active_flag = hr_api.g_varchar2) then
1658 p_rec.active_flag := g_old_rec.active_flag;
1659 End If;
1660 If (p_rec.default_flag = hr_api.g_varchar2) then
1661 p_rec.default_flag := g_old_rec.default_flag;
1662 End If;
1663 If (p_rec.name = hr_api.g_varchar2) then
1664 p_rec.name := g_old_rec.name;
1665 End If;
1666 If (p_rec.type = hr_api.g_varchar2) then
1667 p_rec.type := g_old_rec.type;
1668 End If;
1669 If (p_rec.comments = hr_api.g_varchar2) then
1670 p_rec.comments := g_old_rec.comments;
1671 End If;
1672 If (p_rec.description = hr_api.g_varchar2) then
1673 p_rec.description := g_old_rec.description;
1674 End If;
1675 If (p_rec.bst_information_category = hr_api.g_varchar2) then
1676 p_rec.bst_information_category := g_old_rec.bst_information_category;
1677 End If;
1678 If (p_rec.bst_information1 = hr_api.g_varchar2) then
1679 p_rec.bst_information1 := g_old_rec.bst_information1;
1680 End If;
1681 If (p_rec.bst_information2 = hr_api.g_varchar2) then
1682 p_rec.bst_information2 := g_old_rec.bst_information2;
1683 End If;
1684 If (p_rec.bst_information3 = hr_api.g_varchar2) then
1685 p_rec.bst_information3 := g_old_rec.bst_information3;
1686 End If;
1687 If (p_rec.bst_information4 = hr_api.g_varchar2) then
1688 p_rec.bst_information4 := g_old_rec.bst_information4;
1689 End If;
1690 If (p_rec.bst_information5 = hr_api.g_varchar2) then
1691 p_rec.bst_information5 := g_old_rec.bst_information5;
1692 End If;
1693 If (p_rec.bst_information6 = hr_api.g_varchar2) then
1694 p_rec.bst_information6 := g_old_rec.bst_information6;
1695 End If;
1696 If (p_rec.bst_information7 = hr_api.g_varchar2) then
1697 p_rec.bst_information7 := g_old_rec.bst_information7;
1698 End If;
1699 If (p_rec.bst_information8 = hr_api.g_varchar2) then
1700 p_rec.bst_information8 := g_old_rec.bst_information8;
1701 End If;
1702 If (p_rec.bst_information9 = hr_api.g_varchar2) then
1703 p_rec.bst_information9 := g_old_rec.bst_information9;
1704 End If;
1705 If (p_rec.bst_information10 = hr_api.g_varchar2) then
1709 p_rec.bst_information11 := g_old_rec.bst_information11;
1706 p_rec.bst_information10 := g_old_rec.bst_information10;
1707 End If;
1708 If (p_rec.bst_information11 = hr_api.g_varchar2) then
1710 End If;
1711 If (p_rec.bst_information12 = hr_api.g_varchar2) then
1712 p_rec.bst_information12 := g_old_rec.bst_information12;
1713 End If;
1714 If (p_rec.bst_information13 = hr_api.g_varchar2) then
1715 p_rec.bst_information13 := g_old_rec.bst_information13;
1716 End If;
1717 If (p_rec.bst_information14 = hr_api.g_varchar2) then
1718 p_rec.bst_information14 := g_old_rec.bst_information14;
1719 End If;
1720 If (p_rec.bst_information15 = hr_api.g_varchar2) then
1721 p_rec.bst_information15 := g_old_rec.bst_information15;
1722 End If;
1723 If (p_rec.bst_information16 = hr_api.g_varchar2) then
1724 p_rec.bst_information16 := g_old_rec.bst_information16;
1725 End If;
1726 If (p_rec.bst_information17 = hr_api.g_varchar2) then
1727 p_rec.bst_information17 := g_old_rec.bst_information17;
1728 End If;
1729 If (p_rec.bst_information18 = hr_api.g_varchar2) then
1730 p_rec.bst_information18 := g_old_rec.bst_information18;
1731 End If;
1732 If (p_rec.bst_information19 = hr_api.g_varchar2) then
1733 p_rec.bst_information19 := g_old_rec.bst_information19;
1734 End If;
1735 If (p_rec.bst_information20 = hr_api.g_varchar2) then
1736 p_rec.bst_information20 := g_old_rec.bst_information20;
1737 End If;
1738 --
1739 -- Return the plsql record structure.
1740 --
1741 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1742 Return(p_rec);
1743 --
1744 End convert_defs;
1745 --
1746 -- ----------------------------------------------------------------------------
1747 -- |---------------------------< insert_validate >----------------------------|
1748 -- ----------------------------------------------------------------------------
1749 -- {Start Of Comments}
1750 --
1751 -- Description:
1752 -- This procedure controls the execution of all insert business rules
1753 -- validation.
1754 --
1755 -- Pre Conditions:
1756 -- This private procedure is called from ins procedure.
1757 --
1758 -- In Arguments:
1759 -- A Pl/Sql record structre.
1760 --
1761 -- Post Success:
1762 -- Processing continues.
1763 --
1764 -- Post Failure:
1765 -- If a business rules fails the error will not be handled by this procedure
1766 -- unless explicity coded.
1767 --
1768 -- Developer Implementation Notes:
1769 -- For insert, your business rules should be coded within this procedure and
1770 -- should ideally (unless really necessary) just be straight procedure or
1771 -- function calls. Try and avoid using conditional branching logic.
1772 --
1773 -- {End Of Comments}
1774 -- ----------------------------------------------------------------------------
1775 Procedure insert_validate(p_rec in g_rec_type) is
1776 --
1777 W_PROC varchar2(72) := G_PACKAGE||'insert_validate';
1778 --
1779 Begin
1780 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1781 --
1782 -- Call all supporting business operations
1783 --
1784 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1785 --
1786 VALIDITY_CHECK (
1787 P_REC => P_REC);
1788 --
1789 --
1790 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1791 End insert_validate;
1792 --
1793 -- ----------------------------------------------------------------------------
1794 -- |---------------------------< update_validate >----------------------------|
1795 -- ----------------------------------------------------------------------------
1796 -- {Start Of Comments}
1797 --
1798 -- Description:
1799 -- This procedure controls the execution of all update business rules
1800 -- validation.
1801 --
1802 -- Pre Conditions:
1803 -- This private procedure is called from upd procedure.
1804 --
1805 -- In Arguments:
1806 -- A Pl/Sql record structre.
1807 --
1808 -- Post Success:
1809 -- Processing continues.
1810 --
1811 -- Post Failure:
1812 -- If a business rules fails the error will not be handled by this procedure
1813 -- unless explicity coded.
1814 --
1815 -- Developer Implementation Notes:
1816 -- For update, your business rules should be coded within this procedure and
1817 -- should ideally (unless really necessary) just be straight procedure or
1818 -- function calls. Try and avoid using conditional branching logic.
1819 --
1820 -- {End Of Comments}
1821 -- ----------------------------------------------------------------------------
1822 Procedure update_validate(p_rec in g_rec_type) is
1823 --
1824 l_default_flag_changed boolean :=
1825 ota_general.value_changed(g_old_rec.default_flag,
1826 p_rec.default_flag);
1827 --
1828 W_PROC varchar2(72) := G_PACKAGE||'update_validate';
1829 --
1830 Begin
1831 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1832 --
1833 -- Call all supporting business operations
1834 --
1835 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1836 --
1837 if l_default_flag_changed then
1838 VALIDITY_CHECK (
1839 P_REC => P_REC);
1840 end if;
1841 --
1842 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1843 End update_validate;
1844 --
1845 -- ----------------------------------------------------------------------------
1846 -- |---------------------------< delete_validate >----------------------------|
1847 -- ----------------------------------------------------------------------------
1848 -- {Start Of Comments}
1849 --
1850 -- Description:
1851 -- This procedure controls the execution of all delete business rules
1852 -- validation.
1853 --
1857 -- In Arguments:
1854 -- Pre Conditions:
1855 -- This private procedure is called from del procedure.
1856 --
1858 -- A Pl/Sql record structre.
1859 --
1860 -- Post Success:
1861 -- Processing continues.
1862 --
1863 -- Post Failure:
1864 -- If a business rules fails the error will not be handled by this procedure
1865 -- unless explicity coded.
1866 --
1867 -- Developer Implementation Notes:
1868 -- For delete, your business rules should be coded within this procedure and
1869 -- should ideally (unless really necessary) just be straight procedure or
1870 -- function calls. Try and avoid using conditional branching logic.
1871 --
1872 -- {End Of Comments}
1873 -- ----------------------------------------------------------------------------
1874 Procedure delete_validate(p_rec in g_rec_type) is
1875 --
1876 W_PROC varchar2(72) := G_PACKAGE||'delete_validate';
1877 --
1878 Begin
1879 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1880 --
1881 -- Call all supporting business operations
1882 --
1883 CHECK_BSH_EXISTS (
1884 P_BOOKING_STATUS_TYPE_ID => P_REC.BOOKING_STATUS_TYPE_ID);
1885 --
1886 CHECK_TDB_EXISTS (
1887 P_BOOKING_STATUS_TYPE_ID => P_REC.BOOKING_STATUS_TYPE_ID);
1888 --
1889 CHECK_BSE_EXISTS (
1890 P_BOOKING_STATUS_TYPE_ID => P_REC.BOOKING_STATUS_TYPE_ID);
1891
1892 --
1893 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1894 End delete_validate;
1895 --
1896 -- ----------------------------------------------------------------------------
1897 -- |---------------------------------< ins >----------------------------------|
1898 -- ----------------------------------------------------------------------------
1899 Procedure ins
1900 (
1901 p_rec in out nocopy g_rec_type,
1902 p_validate in boolean
1903 ) is
1904 --
1905 W_PROC varchar2(72) := G_PACKAGE||'ins';
1906 --
1907 Begin
1908 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1909 --
1910 -- Determine if the business process is to be validated.
1911 --
1912 If p_validate then
1913 --
1914 -- Issue the savepoint.
1915 --
1916 SAVEPOINT ins_bst;
1917 End If;
1918 --
1919 -- Call the supporting insert validate operations
1920 --
1921 insert_validate(p_rec);
1922 --
1923 -- Call the supporting pre-insert operation
1924 --
1925 pre_insert(p_rec);
1926 --
1927 -- Insert the row
1928 --
1929 insert_dml(p_rec);
1930 --
1931 -- Call the supporting post-insert operation
1932 --
1933 post_insert(p_rec);
1934 --
1935 -- If we are validating then raise the Validate_Enabled exception
1936 --
1937 If p_validate then
1938 Raise HR_Api.Validate_Enabled;
1939 End If;
1940 --
1941 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
1942 Exception
1943 When HR_Api.Validate_Enabled Then
1944 --
1945 -- As the Validate_Enabled exception has been raised
1946 -- we must rollback to the savepoint
1947 --
1948 ROLLBACK TO ins_bst;
1949 end ins;
1950 --
1951 -- ----------------------------------------------------------------------------
1952 -- |---------------------------------< ins >----------------------------------|
1953 -- ----------------------------------------------------------------------------
1954 Procedure ins
1955 (
1956 p_booking_status_type_id out nocopy number,
1957 p_business_group_id in number,
1958 p_active_flag in varchar2,
1959 p_default_flag in varchar2,
1960 p_name in varchar2,
1961 p_object_version_number out nocopy number,
1962 p_type in varchar2,
1963 p_comments in varchar2 ,
1964 p_description in varchar2 ,
1965 p_bst_information_category in varchar2 ,
1966 p_bst_information1 in varchar2 ,
1967 p_bst_information2 in varchar2 ,
1968 p_bst_information3 in varchar2 ,
1969 p_bst_information4 in varchar2 ,
1970 p_bst_information5 in varchar2 ,
1971 p_bst_information6 in varchar2 ,
1972 p_bst_information7 in varchar2 ,
1973 p_bst_information8 in varchar2 ,
1974 p_bst_information9 in varchar2 ,
1975 p_bst_information10 in varchar2 ,
1976 p_bst_information11 in varchar2 ,
1977 p_bst_information12 in varchar2 ,
1978 p_bst_information13 in varchar2 ,
1979 p_bst_information14 in varchar2 ,
1980 p_bst_information15 in varchar2 ,
1981 p_bst_information16 in varchar2 ,
1982 p_bst_information17 in varchar2 ,
1983 p_bst_information18 in varchar2 ,
1984 p_bst_information19 in varchar2 ,
1985 p_bst_information20 in varchar2 ,
1986 p_validate in boolean
1987 ) is
1988 --
1989 l_rec g_rec_type;
1990 W_PROC varchar2(72) := G_PACKAGE||'ins';
1991 --
1992 Begin
1993 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
1994 --
1995 -- Call conversion function to turn arguments into the
1996 -- p_rec structure.
1997 --
1998 l_rec :=
1999 convert_args
2000 (
2001 null,
2002 p_business_group_id,
2003 p_active_flag,
2004 p_default_flag,
2005 p_name,
2006 null,
2007 p_type,
2008 p_comments,
2009 p_description,
2010 p_bst_information_category,
2011 p_bst_information1,
2015 p_bst_information5,
2012 p_bst_information2,
2013 p_bst_information3,
2014 p_bst_information4,
2016 p_bst_information6,
2017 p_bst_information7,
2018 p_bst_information8,
2019 p_bst_information9,
2020 p_bst_information10,
2021 p_bst_information11,
2022 p_bst_information12,
2023 p_bst_information13,
2024 p_bst_information14,
2025 p_bst_information15,
2026 p_bst_information16,
2027 p_bst_information17,
2028 p_bst_information18,
2029 p_bst_information19,
2030 p_bst_information20
2031 );
2032 --
2033 -- Having converted the arguments into the bst_rec
2034 -- plsql record structure we call the corresponding record business process.
2035 --
2036 ins(l_rec, p_validate);
2037 --
2038 -- As the primary key argument(s)
2039 -- are specified as an OUT's we must set these values.
2040 --
2041 p_booking_status_type_id := l_rec.booking_status_type_id;
2042 p_object_version_number := l_rec.object_version_number;
2043 --
2044 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
2045 End ins;
2046 --
2047 -- ----------------------------------------------------------------------------
2048 -- |---------------------------------< upd >----------------------------------|
2049 -- ----------------------------------------------------------------------------
2050 Procedure upd
2051 (
2052 p_rec in out nocopy g_rec_type,
2053 p_validate in boolean
2054 ) is
2055 --
2056 W_PROC varchar2(72) := G_PACKAGE||'upd';
2057 --
2058 Begin
2059 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
2060 --
2061 -- Determine if the business process is to be validated.
2062 --
2063 If p_validate then
2064 --
2065 -- Issue the savepoint.
2066 --
2067 SAVEPOINT upd_bst;
2068 End If;
2069 --
2070 -- We must lock the row which we need to update.
2071 --
2072 lck
2073 (
2074 p_rec.booking_status_type_id,
2075 p_rec.object_version_number
2076 );
2077 --
2078 -- 1. During an update system defaults are used to determine if
2079 -- arguments have been defaulted or not. We must therefore
2080 -- derive the full record structure values to be updated.
2081 --
2082 -- 2. Call the supporting update validate operations.
2083 --
2084 update_validate(convert_defs(p_rec));
2085 --
2086 -- Call the supporting pre-update operation
2087 --
2088 pre_update(p_rec);
2089 --
2090 -- Update the row.
2091 --
2092 update_dml(p_rec);
2093 --
2094 -- Call the supporting post-update operation
2095 --
2096 post_update(p_rec);
2097 --
2098 -- If we are validating then raise the Validate_Enabled exception
2099 --
2100 If p_validate then
2101 Raise HR_Api.Validate_Enabled;
2102 End If;
2103 --
2104 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
2105 Exception
2106 When HR_Api.Validate_Enabled Then
2107 --
2108 -- As the Validate_Enabled exception has been raised
2109 -- we must rollback to the savepoint
2110 --
2111 ROLLBACK TO upd_bst;
2112 End upd;
2113 --
2114 -- ----------------------------------------------------------------------------
2115 -- |---------------------------------< upd >----------------------------------|
2116 -- ----------------------------------------------------------------------------
2117 Procedure upd
2118 (
2119 p_booking_status_type_id in number,
2120 p_business_group_id in number ,
2121 p_active_flag in varchar2 ,
2122 p_default_flag in varchar2 ,
2123 p_name in varchar2 ,
2124 p_object_version_number in out nocopy number,
2125 p_type in varchar2 ,
2126 p_comments in varchar2 ,
2127 p_description in varchar2 ,
2128 p_bst_information_category in varchar2 ,
2129 p_bst_information1 in varchar2 ,
2130 p_bst_information2 in varchar2 ,
2131 p_bst_information3 in varchar2 ,
2132 p_bst_information4 in varchar2 ,
2133 p_bst_information5 in varchar2 ,
2134 p_bst_information6 in varchar2 ,
2135 p_bst_information7 in varchar2 ,
2136 p_bst_information8 in varchar2 ,
2137 p_bst_information9 in varchar2 ,
2138 p_bst_information10 in varchar2 ,
2139 p_bst_information11 in varchar2 ,
2140 p_bst_information12 in varchar2 ,
2141 p_bst_information13 in varchar2 ,
2142 p_bst_information14 in varchar2 ,
2143 p_bst_information15 in varchar2 ,
2144 p_bst_information16 in varchar2 ,
2145 p_bst_information17 in varchar2 ,
2146 p_bst_information18 in varchar2 ,
2147 p_bst_information19 in varchar2 ,
2148 p_bst_information20 in varchar2 ,
2149 p_validate in boolean
2150 ) is
2151 --
2152 l_rec g_rec_type;
2153 W_PROC varchar2(72) := G_PACKAGE||'upd';
2154 --
2155 Begin
2156 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
2157 --
2158 -- Call conversion function to turn arguments into the
2159 -- l_rec structure.
2160 --
2161 l_rec :=
2162 convert_args
2163 (
2164 p_booking_status_type_id,
2165 p_business_group_id,
2166 p_active_flag,
2167 p_default_flag,
2168 p_name,
2169 p_object_version_number,
2170 p_type,
2171 p_comments,
2175 p_bst_information2,
2172 p_description,
2173 p_bst_information_category,
2174 p_bst_information1,
2176 p_bst_information3,
2177 p_bst_information4,
2178 p_bst_information5,
2179 p_bst_information6,
2180 p_bst_information7,
2181 p_bst_information8,
2182 p_bst_information9,
2183 p_bst_information10,
2184 p_bst_information11,
2185 p_bst_information12,
2186 p_bst_information13,
2187 p_bst_information14,
2188 p_bst_information15,
2189 p_bst_information16,
2190 p_bst_information17,
2191 p_bst_information18,
2192 p_bst_information19,
2193 p_bst_information20
2194 );
2195 --
2196 -- Having converted the arguments into the
2197 -- plsql record structure we call the corresponding record
2198 -- business process.
2199 --
2200 upd(l_rec, p_validate);
2201 p_object_version_number := l_rec.object_version_number;
2202 --
2203 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
2204 End upd;
2205 --
2206 -- ----------------------------------------------------------------------------
2207 -- |---------------------------------< del >----------------------------------|
2208 -- ----------------------------------------------------------------------------
2209 Procedure del
2210 (
2211 p_rec in g_rec_type,
2212 p_validate in boolean
2213 ) is
2214 --
2215 W_PROC varchar2(72) := G_PACKAGE||'del';
2216 --
2217 Begin
2218 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
2219 --
2220 -- Determine if the business process is to be validated.
2221 --
2222 If p_validate then
2223 --
2224 -- Issue the savepoint.
2225 --
2226 SAVEPOINT del_bst;
2227 End If;
2228 --
2229 -- We must lock the row which we need to delete.
2230 --
2231 lck
2232 (
2233 p_rec.booking_status_type_id,
2234 p_rec.object_version_number
2235 );
2236 --
2237 -- Call the supporting delete validate operation
2238 --
2239 delete_validate(p_rec);
2240 --
2241 -- Call the supporting pre-delete operation
2242 --
2243 pre_delete(p_rec);
2244 --
2245 -- Delete the row.
2246 --
2247 delete_dml(p_rec);
2248 --
2249 -- Call the supporting post-delete operation
2250 --
2251 post_delete(p_rec);
2252 --
2253 -- If we are validating then raise the Validate_Enabled exception
2254 --
2255 If p_validate then
2256 Raise HR_Api.Validate_Enabled;
2257 End If;
2258 --
2259 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
2260 Exception
2261 When HR_Api.Validate_Enabled Then
2262 --
2263 -- As the Validate_Enabled exception has been raised
2264 -- we must rollback to the savepoint
2265 --
2266 ROLLBACK TO del_bst;
2267 End del;
2268 --
2269 -- ----------------------------------------------------------------------------
2270 -- |---------------------------------< del >----------------------------------|
2271 -- ----------------------------------------------------------------------------
2272 Procedure del
2273 (
2274 p_booking_status_type_id in number,
2275 p_object_version_number in number,
2276 p_validate in boolean
2277 ) is
2278 --
2279 l_rec g_rec_type;
2280 W_PROC varchar2(72) := G_PACKAGE||'del';
2281 --
2282 Begin
2283 HR_UTILITY.SET_LOCATION('Entering:'||W_PROC, 5);
2284 --
2285 -- As the delete procedure accepts a plsql record structure we do need to
2286 -- convert the arguments into the record structure.
2287 -- We don't need to call the supplied conversion argument routine as we
2288 -- only need a few attributes.
2289 --
2290 l_rec.booking_status_type_id:= p_booking_status_type_id;
2291 l_rec.object_version_number := p_object_version_number;
2292 --
2293 -- Having converted the arguments into the bst_rec
2294 -- plsql record structure we must call the corresponding entity
2295 -- business process
2296 --
2297 del(l_rec, p_validate);
2298 --
2299 HR_UTILITY.SET_LOCATION(' Leaving:'||W_PROC, 10);
2300 End del;
2301 --
2302 end OTA_BST_API;