1 Package Body ota_trb_shd as
2 /* $Header: ottrbrhi.pkb 120.6.12000000.3 2007/07/05 09:22:53 aabalakr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 -- Private package current record structure definition
8 --
9 --
10 g_package varchar2(33) := ' ota_trb_shd.'; -- Global package name
11 --
12 --
13 -- ----------------------------------------------------------------------------
14 -- |------------------------< return_api_dml_status >-------------------------|
15 -- ----------------------------------------------------------------------------
16 Function return_api_dml_status Return Boolean Is
17 --
18 l_proc varchar2(72) := g_package||'return_api_dml_status';
19 --
20 Begin
21 hr_utility.set_location('Entering:'||l_proc, 5);
22 --
23 Return (nvl(g_api_dml, false));
24 --
25 hr_utility.set_location(' Leaving:'||l_proc, 10);
26 End return_api_dml_status;
27 --
28 -- ----------------------------------------------------------------------------
29 -- |---------------------------< constraint_error >---------------------------|
30 -- ----------------------------------------------------------------------------
31 -- {Start Of Comments}
32 --
33 -- Description:
34 -- This procedure is called when a constraint has been violated (i.e.
35 -- The exception hr_api.check_integrity_violated,
36 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
37 -- hr_api.unique_integrity_violated has been raised).
38 -- The exceptions can only be raised as follows:
39 -- 1) A check constraint can only be violated during an INSERT or UPDATE
40 -- dml operation.
41 -- 2) A parent integrity constraint can only be violated during an
42 -- INSERT or UPDATE dml operation.
43 -- 3) A child integrity constraint can only be violated during an
44 -- DELETE dml operation.
45 -- 4) A unique integrity constraint can only be violated during INSERT or
46 -- UPDATE dml operation.
47 --
48 -- Pre Conditions:
49 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated,
50 -- hr_api.child_integrity_violated or hr_api.unique_integrity_violated has
51 -- been raised with the subsequent stripping of the constraint name from the
52 -- 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
74 ) Is
75 --
76 l_proc varchar2(72) := g_package||'constraint_error';
77 --
78 Begin
79 --
80 hr_utility.set_location('Entering:'||l_proc, 5);
81 --
82 If (p_constraint_name = 'OTA_CHECK_START_END_TIMES') Then
83 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
84 fnd_message.set_token('PROCEDURE', l_proc);
85 fnd_message.set_token('STEP','5');
86 fnd_message.raise_error;
87 ElsIf (p_constraint_name = 'OTA_CHK_REQ_TIMES_END_FMT') Then
91 fnd_message.raise_error;
88 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
89 fnd_message.set_token('PROCEDURE', l_proc);
90 fnd_message.set_token('STEP','10');
92 ElsIf (p_constraint_name = 'OTA_CHK_REQ_TIMES_START_FMT') Then
93 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
94 fnd_message.set_token('PROCEDURE', l_proc);
95 fnd_message.set_token('STEP','15');
96 fnd_message.raise_error;
97 ElsIf (p_constraint_name = 'OTA_RESOURCE_BOOKINGS_FK1') Then
98 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
99 fnd_message.set_token('PROCEDURE', l_proc);
100 fnd_message.set_token('STEP','20');
101 fnd_message.raise_error;
102 ElsIf (p_constraint_name = 'OTA_RESOURCE_BOOKINGS_FK2') Then
103 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
104 fnd_message.set_token('PROCEDURE', l_proc);
105 fnd_message.set_token('STEP','25');
106 fnd_message.raise_error;
107 ElsIf (p_constraint_name = 'OTA_RESOURCE_BOOKINGS_PK') Then
108 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
109 fnd_message.set_token('PROCEDURE', l_proc);
110 fnd_message.set_token('STEP','30');
111 fnd_message.raise_error;
112 ElsIf (p_constraint_name = 'OTA_TRB_PRIMARY_VENUE_FLAG_CHK') Then
113 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
114 fnd_message.set_token('PROCEDURE', l_proc);
115 fnd_message.set_token('STEP','35');
116 fnd_message.raise_error;
117 ElsIf (p_constraint_name = 'OTA_TRB_STATUS_CHK') Then
118 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
119 fnd_message.set_token('PROCEDURE', l_proc);
120 fnd_message.set_token('STEP','40');
121 fnd_message.raise_error;
122 Else
123 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
124 fnd_message.set_token('PROCEDURE', l_proc);
125 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
126 fnd_message.raise_error;
127 End If;
128 --
129 hr_utility.set_location(' Leaving:'||l_proc, 10);
130 End constraint_error;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |-----------------------------< api_updating >-----------------------------|
134 -- ----------------------------------------------------------------------------
135 -- {Start Of Comments}
136 --
137 -- Description:
138 -- This function is used to populate the g_old_rec record with the current
139 -- row from the database for the specified primary key provided that the
140 -- primary key exists and is valid and does not already match the current
141 -- g_old_rec.
142 -- The function will always return a TRUE value if the g_old_rec is
143 -- populated with the current row. A FALSE value will be returned if all of
144 -- the primary key arguments are null.
145 --
146 -- Pre Conditions:
147 -- None.
148 --
149 -- In Arguments:
150 --
151 -- Post Success:
152 -- A value of TRUE will be returned indiciating that the g_old_rec is
153 -- current.
154 -- A value of FALSE will be returned if all of the primary key arguments
155 -- have a null value (this indicates that the row has not be inserted into
156 -- the Schema), and therefore could never have a corresponding row.
157 --
158 -- Post Failure:
159 -- A failure can only occur under two circumstances:
160 -- 1) The primary key is invalid (i.e. a row does not exist for the
161 -- specified primary key values).
162 -- 2) If an object_version_number exists but is NOT the same as the current
163 -- g_old_rec value.
164 --
165 -- Developer Implementation Notes:
166 -- None.
167 --
168 -- {End Of Comments}
169 -- ----------------------------------------------------------------------------
170 Function api_updating
171 (
172 p_resource_booking_id in number,
173 p_object_version_number in number
174 ) Return Boolean Is
175 --
176 --
177 -- Cursor selects the 'current' row from the HR Schema
178 --
179 Cursor C_Sel1 is
180 select
181 resource_booking_id,
182 supplied_resource_id,
183 event_id,
184 date_booking_placed,
185 object_version_number,
186 status,
187 absolute_price,
188 booking_person_id,
189 comments,
190 contact_name,
191 contact_phone_number,
192 delegates_per_unit,
193 quantity,
194 required_date_from,
195 required_date_to,
196 required_end_time,
197 required_start_time,
198 deliver_to,
199 primary_venue_flag,
200 role_to_play,
201 trb_information_category,
202 trb_information1,
203 trb_information2,
204 trb_information3,
205 trb_information4,
206 trb_information5,
207 trb_information6,
208 trb_information7,
209 trb_information8,
210 trb_information9,
211 trb_information10,
212 trb_information11,
213 trb_information12,
214 trb_information13,
215 trb_information14,
216 trb_information15,
217 trb_information16,
218 trb_information17,
219 trb_information18,
220 trb_information19,
221 trb_information20
222 ,display_to_learner_flag
223 ,book_entire_period_flag
224 -- ,unbook_request_flag
225 ,chat_id
226 ,forum_id
227 ,timezone_code
228 from ota_resource_bookings
229 where resource_booking_id = p_resource_booking_id;
230 --
231 l_proc varchar2(72) := g_package||'api_updating';
232 l_fct_ret boolean;
233 --
234 Begin
235 hr_utility.set_location('Entering:'||l_proc, 5);
236 --
237 If (
238 p_resource_booking_id is null and
239 p_object_version_number is null
240 ) Then
241 --
242 -- One of the primary key arguments is null therefore we must
243 -- set the returning function value to false
244 --
245 l_fct_ret := false;
246 Else
247 If (
248 p_resource_booking_id = g_old_rec.resource_booking_id and
249 p_object_version_number = g_old_rec.object_version_number
250 ) Then
251 hr_utility.set_location(l_proc, 10);
252 --
253 -- The g_old_rec is current therefore we must
254 -- set the returning function to true
255 --
256 l_fct_ret := true;
257 Else
258 --
259 -- Select the current row
260 --
261 Open C_Sel1;
262 Fetch C_Sel1 Into g_old_rec;
263 If C_Sel1%notfound Then
264 Close C_Sel1;
265 --
266 -- The primary key is invalid therefore we must error
267 --
268 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
269 hr_utility.raise_error;
270 End If;
271 Close C_Sel1;
272 If (p_object_version_number <> g_old_rec.object_version_number) Then
273 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
274 hr_utility.raise_error;
275 End If;
276 hr_utility.set_location(l_proc, 15);
277 l_fct_ret := true;
278 End If;
279 End If;
280 hr_utility.set_location(' Leaving:'||l_proc, 20);
281 Return (l_fct_ret);
282 --
283 End api_updating;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |---------------------------------< lck >----------------------------------|
287 -- ----------------------------------------------------------------------------
288 Procedure lck
289 (
290 p_resource_booking_id in number,
291 p_object_version_number in number
292 ) is
293 --
294 -- Cursor selects the 'current' row from the HR Schema
295 --
296 Cursor C_Sel1 is
297 select resource_booking_id,
298 supplied_resource_id,
299 event_id,
300 date_booking_placed,
301 object_version_number,
302 status,
303 absolute_price,
304 booking_person_id,
305 comments,
306 contact_name,
307 contact_phone_number,
308 delegates_per_unit,
309 quantity,
310 required_date_from,
311 required_date_to,
312 required_end_time,
313 required_start_time,
314 deliver_to,
315 primary_venue_flag,
316 role_to_play,
317 trb_information_category,
318 trb_information1,
319 trb_information2,
320 trb_information3,
321 trb_information4,
322 trb_information5,
323 trb_information6,
324 trb_information7,
325 trb_information8,
326 trb_information9,
327 trb_information10,
328 trb_information11,
329 trb_information12,
330 trb_information13,
331 trb_information14,
332 trb_information15,
333 trb_information16,
334 trb_information17,
335 trb_information18,
336 trb_information19,
337 trb_information20
338 ,display_to_learner_flag
339 ,book_entire_period_flag
340 -- ,unbook_request_flag
341 ,chat_id
342 ,forum_id
343 ,timezone_code
344 from ota_resource_bookings
345 where resource_booking_id = p_resource_booking_id
346 for update nowait;
347 --
348 l_proc varchar2(72) := g_package||'lck';
349 --
350 Begin
351 hr_utility.set_location('Entering:'||l_proc, 5);
352 --
353 -- Add any mandatory argument checking here:
354 -- Example:
355 -- hr_api.mandatory_arg_error
356 -- (p_api_name => l_proc,
357 -- p_argument => 'object_version_number',
358 -- p_argument_value => p_object_version_number);
359 --
360 Open C_Sel1;
361 Fetch C_Sel1 Into g_old_rec;
362 If C_Sel1%notfound then
363 Close C_Sel1;
364 --
365 -- The primary key is invalid therefore we must error
366 --
367 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
368 hr_utility.raise_error;
369 End If;
370 Close C_Sel1;
371 If (p_object_version_number <> g_old_rec.object_version_number) Then
372 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
373 hr_utility.raise_error;
374 End If;
375 --
376 hr_utility.set_location(' Leaving:'||l_proc, 10);
377 --
378 -- We need to trap the ORA LOCK exception
379 --
380 Exception
381 When HR_Api.Object_Locked then
382 --
386 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
383 -- The object is locked therefore we need to supply a meaningful
384 -- error message.
385 --
387 hr_utility.set_message_token('TABLE_NAME', 'ota_resource_bookings');
388 hr_utility.raise_error;
389 End lck;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |-----------------------------< convert_args >-----------------------------|
393 -- ----------------------------------------------------------------------------
394 -- {Start Of Comments}
395 --
396 -- Description:
397 -- This function is used to turn attribute arguments into the record
398 -- structure g_rec_type.
399 --
400 -- Pre Conditions:
401 -- This is a private function and can only be called from the ins or upd
402 -- attribute processes.
403 --
404 -- In Arguments:
405 --
406 -- Post Success:
407 -- A returning record structure will be returned.
408 --
409 -- Post Failure:
410 -- No direct error handling is required within this function. Any possible
411 -- errors within this function will be a PL/SQL value error due to conversion
412 -- of datatypes or data lengths.
413 --
414 -- Developer Implementation Notes:
415 --
416 -- {End Of Comments}
417 -- ----------------------------------------------------------------------------
418 Function convert_args
419 (
420 p_resource_booking_id in number,
421 p_supplied_resource_id in number,
422 p_event_id in number,
423 p_date_booking_placed in date,
424 p_object_version_number in number,
425 p_status in varchar2,
426 p_absolute_price in number,
427 p_booking_person_id in number,
428 p_comments in varchar2,
429 p_contact_name in varchar2,
430 p_contact_phone_number in varchar2,
431 p_delegates_per_unit in number,
432 p_quantity in number,
433 p_required_date_from in date,
434 p_required_date_to in date,
435 p_required_end_time in varchar2,
436 p_required_start_time in varchar2,
437 p_deliver_to in varchar2,
438 p_primary_venue_flag in varchar2,
439 p_role_to_play in varchar2,
440 p_trb_information_category in varchar2,
441 p_trb_information1 in varchar2,
442 p_trb_information2 in varchar2,
443 p_trb_information3 in varchar2,
444 p_trb_information4 in varchar2,
445 p_trb_information5 in varchar2,
446 p_trb_information6 in varchar2,
447 p_trb_information7 in varchar2,
448 p_trb_information8 in varchar2,
449 p_trb_information9 in varchar2,
450 p_trb_information10 in varchar2,
451 p_trb_information11 in varchar2,
452 p_trb_information12 in varchar2,
453 p_trb_information13 in varchar2,
454 p_trb_information14 in varchar2,
455 p_trb_information15 in varchar2,
456 p_trb_information16 in varchar2,
457 p_trb_information17 in varchar2,
458 p_trb_information18 in varchar2,
459 p_trb_information19 in varchar2,
460 p_trb_information20 in varchar2
461 ,p_display_to_learner_flag in varchar2
462 ,p_book_entire_period_flag in varchar2
463 -- ,p_unbook_request_flag in varchar2
464 ,p_chat_id in number
465 ,p_forum_id in number
466 ,p_timezone_code IN VARCHAR2
467 )
468 Return g_rec_type is
469 --
470 l_rec g_rec_type;
471 l_proc varchar2(72) := g_package||'convert_args';
472 --
473 Begin
474 --
475 hr_utility.set_location('Entering:'||l_proc, 5);
476 --
477 -- Convert arguments into local l_rec structure.
478 --
479 l_rec.resource_booking_id := p_resource_booking_id;
480 l_rec.supplied_resource_id := p_supplied_resource_id;
481 l_rec.event_id := p_event_id;
482 l_rec.date_booking_placed := p_date_booking_placed;
483 l_rec.object_version_number := p_object_version_number;
484 l_rec.status := p_status;
485 l_rec.absolute_price := p_absolute_price;
486 l_rec.booking_person_id := p_booking_person_id;
487 l_rec.comments := p_comments;
488 l_rec.contact_name := p_contact_name;
489 l_rec.contact_phone_number := p_contact_phone_number;
490 l_rec.delegates_per_unit := p_delegates_per_unit;
491 l_rec.quantity := p_quantity;
492 l_rec.required_date_from := p_required_date_from;
493 l_rec.required_date_to := p_required_date_to;
494 l_rec.required_end_time := p_required_end_time;
495 l_rec.required_start_time := p_required_start_time;
496 l_rec.deliver_to := p_deliver_to;
497 l_rec.primary_venue_flag := p_primary_venue_flag;
498 l_rec.role_to_play := p_role_to_play;
499 l_rec.trb_information_category := p_trb_information_category;
500 l_rec.trb_information1 := p_trb_information1;
501 l_rec.trb_information2 := p_trb_information2;
502 l_rec.trb_information3 := p_trb_information3;
503 l_rec.trb_information4 := p_trb_information4;
504 l_rec.trb_information5 := p_trb_information5;
505 l_rec.trb_information6 := p_trb_information6;
506 l_rec.trb_information7 := p_trb_information7;
507 l_rec.trb_information8 := p_trb_information8;
508 l_rec.trb_information9 := p_trb_information9;
509 l_rec.trb_information10 := p_trb_information10;
510 l_rec.trb_information11 := p_trb_information11;
511 l_rec.trb_information12 := p_trb_information12;
512 l_rec.trb_information13 := p_trb_information13;
513 l_rec.trb_information14 := p_trb_information14;
514 l_rec.trb_information15 := p_trb_information15;
515 l_rec.trb_information16 := p_trb_information16;
516 l_rec.trb_information17 := p_trb_information17;
517 l_rec.trb_information18 := p_trb_information18;
518 l_rec.trb_information19 := p_trb_information19;
519 l_rec.trb_information20 := p_trb_information20;
520 l_rec.display_to_learner_flag := p_display_to_learner_flag ;
521 l_rec.book_entire_period_flag := p_book_entire_period_flag ;
522 -- l_rec.unbook_request_flag := p_unbook_request_flag ;
523 l_rec.chat_id := p_chat_id;
524 l_rec.forum_id := p_forum_id;
525 l_rec.timezone_code := p_timezone_code;
526 --
527 -- Return the plsql record structure.
528 --
529 hr_utility.set_location(' Leaving:'||l_proc, 10);
530 Return(l_rec);
531 --
532 End convert_args;
533 --
534 end ota_trb_shd;
[Home] [Help]