[Home] [Help]
PACKAGE BODY: APPS.OTA_EVT_SHD
Source
4 -- ----------------------------------------------------------------------------
1 PACKAGE BODY OTA_EVT_SHD as
2 /* $Header: otevt01t.pkb 120.19 2011/04/07 13:34:40 shwnayak ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_evt_shd.'; -- Global package name
9 --
10 -- Working records within procedures
11 --
12 G_FETCHED_REC ota_evt_shd.g_rec_type;
13 G_NULL_REC ota_evt_shd.g_rec_type;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< return_api_dml_status >-------------------------|
17 -- ----------------------------------------------------------------------------
18 Function return_api_dml_status Return Boolean Is
19 --
20 l_proc varchar2(72) := g_package||'return_api_dml_status';
21 --
22 Begin
23 hr_utility.set_location('Entering:'||l_proc, 5);
24 --
25 Return (nvl(g_api_dml, false));
26 --
27 hr_utility.set_location(' Leaving:'||l_proc, 10);
28 End return_api_dml_status;
29 --
30 -- ----------------------------------------------------------------------------
31 -- |---------------------------< constraint_error >---------------------------|
32 -- ----------------------------------------------------------------------------
33 Procedure CONSTRAINT_ERROR (
34 p_constraint_name in varchar2
35 ) Is
36 --
37 W_PROC varchar2 (72)
38 := g_package || 'CONSTRAINT_ERROR';
39 --
40 Begin
41 --
42 hr_utility.set_location ('Entering:' || W_PROC, 5);
43 hr_utility.set_location ('Constrint Name = '||p_constraint_name,5);
44 --
45 -- Key constraints
46 --
47 If (p_constraint_name = 'OTA_EVENTS_FK1') Then
48 FND_MESSAGE.SET_NAME('OTA','OTA_13429_EVT_NO_TAV');
49 fnd_message.raise_error;
50 ElsIf (p_constraint_name = 'OTA_EVENTS_FK2') Then
51 FND_MESSAGE.SET_NAME('OTA','OTA_13430_EVT_NO_BUS');
52 fnd_message.raise_error;
53 ElsIf (p_constraint_name = 'OTA_EVENTS_FK3') Then
54 FND_MESSAGE.SET_NAME('OTA','OTA_13431_EVT_NO_BUD');
55 fnd_message.raise_error;
56 ElsIf (p_constraint_name = 'OTA_EVENTS_FK4') Then
57 FND_MESSAGE.SET_NAME('OTA','OTA_13432_EVT_NO_ORG');
58 fnd_message.raise_error;
59 ElsIf (p_constraint_name = 'OTA_EVENTS_FK5') Then
60 FND_MESSAGE.SET_NAME('OTA','OTA_13433_EVT_NO_PAR');
61 fnd_message.raise_error;
62 /* 3803613 */
63 ElsIf (p_constraint_name = 'OTA_PROGRAM_MEMBERSHIPS_FK1') Then
64 FND_MESSAGE.SET_NAME('OTA','OTA_13681_EVT_PMM_EXISTS');
65 fnd_message.raise_error;
66 /* 3803613 */
67 ElsIf (p_constraint_name = 'OTA_EVENTS_PK') Then
68 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
69 hr_utility.set_message_token('PROCEDURE', W_PROC);
70 hr_utility.set_message_token('STEP','30');
71 hr_utility.raise_error;
72 ElsIf (p_constraint_name = 'OTA_EVENTS_UK2') Then
73 fnd_message.set_name('OTA','OTA_13471_SES_EXISTS');
74 fnd_message.raise_error;
75 ElsIf (p_constraint_name = 'OTA_EVENT_UK3') Then
76 fnd_message.set_name('OTA','OTA_13889_EVT_LINE_DUPLICATE');
77 fnd_message.raise_error;
78 --
79 -- Check constraints
80 --
81 ElsIf (p_constraint_name = 'OTA_EVT_ACTIVITY_BASED') Then
82 fnd_message.set_name('OTA','OTA_13434_EVT_ACTIVITY_BASED');
83 fnd_message.raise_error;
84 ElsIf (p_constraint_name = 'OTA_EVT_ATTENDANCE') Then
85 fnd_message.set_name('OTA','OTA_13435_EVT_ATTENDEES');
86 fnd_message.raise_error;
87 ElsIf (p_constraint_name = 'OTA_EVT_CATEGORY_NOT_NULL') Then
88 fnd_message.set_name('OTA','OTA_13437_EVT_CATEGORY_NULL');
89 fnd_message.raise_error;
90 ElsIf (p_constraint_name = 'OTA_EVT_CATEGORY_NULL') Then
91 fnd_message.set_name('OTA','OTA_13437_EVT_CATEGORY_NULL');
92 fnd_message.raise_error;
93 ElsIf (p_constraint_name = 'OTA_EVT_COURSE_END_DATE') Then
94 fnd_message.set_name('OTA','OTA_13438_EVT_COURSE_END_DATE');
95 fnd_message.raise_error;
96 ElsIf (p_constraint_name = 'OTA_EVT_COURSE_START_END_ORDER') Then
97 fnd_message.set_name('OTA','OTA_13439_EVT_COURSE_DATES');
98 fnd_message.raise_error;
99 ElsIf (p_constraint_name = 'OTA_EVT_COURSE_TIMES_ORDER') Then
100 fnd_message.set_name('OTA','OTA_13439_EVT_COURSE_DATES');
104 fnd_message.raise_error;
101 fnd_message.raise_error;
102 ElsIf (p_constraint_name = 'OTA_EVT_CURRENCY') Then
103 fnd_message.set_name('OTA', 'OTA_13440_EVT_CURR_PB');
105 ElsIf (p_constraint_name = 'OTA_EVT_DEVELOPMENT_TYPE') Then
106 fnd_message.set_name('OTA','OTA_13441_EVT_DEV_TYPE');
107 fnd_message.raise_error;
108 ElsIf (p_constraint_name = 'OTA_EVT_DURATION') Then
109 fnd_message.set_name('OTA','OTA_13442_EVT_DURATION');
110 fnd_message.raise_error;
111 ElsIf (p_constraint_name = 'OTA_EVT_DURATION_AND_UNITS') Then
112 fnd_message.set_name('OTA','OTA_13442_EVT_DURATION');
113 fnd_message.raise_error;
114 ElsIf (p_constraint_name = 'OTA_EVT_DURATION_MORE_THAN_0') Then
115 fnd_message.set_name('OTA','OTA_13443_EVT_DURATION_NOT_0');
116 fnd_message.raise_error;
117 ElsIf (p_constraint_name = 'OTA_EVT_END_TIME_FORMAT') Then
118 fnd_message.set_name('OTA','OTA_13444_EVT_TIME_FORMAT');
119 fnd_message.raise_error;
120 --Added for Bug 3405804
121 ElsIf (p_constraint_name = 'OTA_EVT_SESSION_TIMING') Then
122 fnd_message.set_name('OTA','OTA_13226_EVT_SESSION_TIMING');
123 fnd_message.raise_error;
124 -- Added for Bug 3405804
125 ElsIf (p_constraint_name = 'OTA_EVT_ENROLMENT_NOT_NULL') Then
126 fnd_message.set_name('OTA','OTA_13445_EVT_ENROL_DATES');
127 fnd_message.raise_error;
128 ElsIf (p_constraint_name = 'OTA_EVT_ENROLMENT_NULL') Then
129 fnd_message.set_name('OTA', 'OTA_13445_EVT_ENROL_DATES');
130 fnd_message.raise_error;
131 ElsIf (p_constraint_name = 'OTA_EVT_ENROL_START_END_ORDER') Then
132 fnd_message.set_name('OTA', 'OTA_13445_EVT_ENROL_DATES');
133 fnd_message.raise_error;
134 ElsIf (p_constraint_name = 'OTA_EVT_EVENT_STATUS_CHK') Then
135 fnd_message.set_name('OTA', 'OTA_13446_EVT_INVALID_STATUS');
136 fnd_message.raise_error;
137 ElsIf (p_constraint_name = 'OTA_EVT_EVENT_TYPE_CHK') Then
138 fnd_message.set_name('OTA', 'OTA_13447_EVT_INVALID_TYPE');
139 fnd_message.raise_error;
140 ElsIf (p_constraint_name = 'OTA_EVT_LANGUAGE') Then
141 fnd_message.set_name('OTA', 'OTA_13448_EVT_INVALID_LANGUAGE');
142 fnd_message.raise_error;
143 ElsIf (p_constraint_name = 'OTA_EVT_MAX_ATTENDEES_POSITIVE') Then
144 fnd_message.set_name('OTA', 'OTA_13449_EVT_ATTENDEES_POS');
145 fnd_message.raise_error;
146 ElsIf (p_constraint_name = 'OTA_EVT_MAX_INTERNALS_POSITIVE') Then
147 fnd_message.set_name('OTA', 'OTA_13449_EVT_ATTENDEES_POS');
148 fnd_message.raise_error;
149 ElsIf (p_constraint_name = 'OTA_EVT_MAX_INTERNAL_MAX_ORDER') Then
150 fnd_message.set_name('OTA', 'OTA_13449_EVT_ATTENDEES_POS');
151 fnd_message.raise_error;
152 ElsIf (p_constraint_name = 'OTA_EVT_MIN_ATTENDEES_POSITIVE') Then
153 fnd_message.set_name('OTA', 'OTA_13449_EVT_ATTENDEES_POS');
154 fnd_message.raise_error;
155 ElsIf (p_constraint_name = 'OTA_EVT_MIN_MAX_ORDER') Then
156 fnd_message.set_name('OTA', 'OTA_13449_EVT_ATTENDEES_POS');
157 fnd_message.raise_error;
158 ElsIf (p_constraint_name = 'OTA_EVT_NORMAL_STATUS_DATES') Then
159 fnd_message.set_name('OTA', 'OTA_13218_EVT_NORMAL_STATUS');
160 fnd_message.raise_error;
161 ElsIf (p_constraint_name = 'OTA_EVT_PARENT_NOT_NULL') Then
162 fnd_message.set_name('OTA', 'OTA_13450_EVT_PARENT');
163 fnd_message.raise_error;
164 ElsIf (p_constraint_name = 'OTA_EVT_PARENT_NULL') Then
165 fnd_message.set_name('OTA', 'OTA_13450_EVT_PARENT');
166 fnd_message.raise_error;
167 ElsIf (p_constraint_name = 'OTA_EVT_PRICE_APPLICABLE') Then
168 fnd_message.set_name('OTA', 'OTA_13440_EVT_CURR_PB');
169 fnd_message.raise_error;
170 ElsIf (p_constraint_name = 'OTA_EVT_PRICE_BASIS') Then
171 fnd_message.set_name('OTA', 'OTA_13440_EVT_CURR_PB');
172 fnd_message.raise_error;
173 ElsIf (p_constraint_name = 'OTA_EVT_PRICE_BASIS_CHK') Then
174 fnd_message.set_name('OTA', 'OTA_13451_EVT_INVALID_PB');
175 fnd_message.raise_error;
176 ElsIf (p_constraint_name = 'OTA_EVT_PRICING_NULL') Then
177 fnd_message.set_name('OTA', 'OTA_13440_EVT_CURR_PB');
178 fnd_message.raise_error;
179 ElsIf (p_constraint_name = 'OTA_EVT_START_TIME_FORMAT') Then
180 fnd_message.set_name('OTA','OTA_13444_EVT_TIME_FORMAT');
181 fnd_message.raise_error;
182 ElsIf (p_constraint_name = 'OTA_EVT_STATUS_NOT_NULL') Then
183 fnd_message.set_name('OTA','OTA_13452_EVT_STATUS');
184 fnd_message.raise_error;
185 ElsIf (p_constraint_name = 'OTA_EVT_STATUS_NULL') Then
186 fnd_message.set_name('OTA','OTA_13452_EVT_STATUS');
187 fnd_message.raise_error;
188 ElsIf (p_constraint_name = 'OTA_EVT_VENDOR_NULL') Then
189 fnd_message.set_name('OTA','OTA_13453_EVT_VENDOR');
190 fnd_message.raise_error;
191 ElsIf (p_constraint_name = 'OTA_EVENTS_UK4') Then
192 fnd_message.set_name('OTA', 'OTA_EVT_DUPLICATE_OFFERING');
193 fnd_message.raise_error;
194 --
195 -- ?
196 --
197 Else
198 FND_MESSAGE.SET_NAME ('OTA', 'OTA_13259_GEN_UNKN_CONSTRAINT');
199 FND_MESSAGE.SET_TOKEN ('PROCEDURE', W_PROC);
200 FND_MESSAGE.SET_TOKEN ('CONSTRAINT', P_CONSTRAINT_NAME);
201 hr_utility.raise_error;
202 End If;
203 --
204 hr_utility.set_location (' Leaving:' || W_PROC, 10);
205 --
206 End CONSTRAINT_ERROR;
207 --
211 Function api_updating
208 -- ----------------------------------------------------------------------------
209 -- |-----------------------------< api_updating >-----------------------------|
210 -- ----------------------------------------------------------------------------
212 (
213 p_event_id in number,
214 p_object_version_number in number
215 ) Return Boolean Is
216 --
217 --
218 -- Cursor selects the 'current' row from the HR Schema
219 --
220 Cursor C_Sel1 is
221 select
222 event_id,
223 vendor_id,
224 activity_version_id,
225 business_group_id,
226 organization_id,
227 event_type,
228 object_version_number,
229 title,
230 budget_cost,
231 actual_cost,
232 budget_currency_code,
233 centre,
234 comments,
235 course_end_date,
236 course_end_time,
237 course_start_date,
238 course_start_time,
239 duration,
240 duration_units,
241 enrolment_end_date,
242 enrolment_start_date,
243 language_id,
244 user_status,
245 development_event_type,
246 event_status,
247 price_basis,
248 currency_code,
249 maximum_attendees,
250 maximum_internal_attendees,
251 minimum_attendees,
252 standard_price,
253 category_code,
254 parent_event_id,
255 book_independent_flag,
256 public_event_flag,
257 secure_event_flag,
258 evt_information_category,
259 evt_information1,
260 evt_information2,
261 evt_information3,
262 evt_information4,
263 evt_information5,
264 evt_information6,
265 evt_information7,
266 evt_information8,
267 evt_information9,
268 evt_information10,
269 evt_information11,
270 evt_information12,
271 evt_information13,
272 evt_information14,
273 evt_information15,
274 evt_information16,
275 evt_information17,
276 evt_information18,
277 evt_information19,
278 evt_information20,
279 project_id,
280 owner_id,
281 line_id,
282 org_id,
283 training_center_id,
284 location_id,
285 offering_id,
286 timezone,
287 parent_offering_id,
288 data_source,
289 event_availability
290 from ota_events
291 where event_id = p_event_id;
292 --
293 l_proc varchar2(72) := g_package||'api_updating';
294 l_fct_ret boolean;
295 --
296 Begin
297 --
298 hr_utility.set_location('Entering:'||l_proc, 5);
299 --
300 if ( (P_EVENT_ID is null)
301 and (P_OBJECT_VERSION_NUMBER is null)) then
302 --
303 -- One of the primary key arguments is null therefore we must
304 -- set the returning function value to false
305 --
306 l_fct_ret := false;
307 elsif ( (p_event_id = g_old_rec.event_id )
308 and (p_object_version_number = g_old_rec.object_version_number)) then
309 hr_utility.set_location(l_proc, 10);
310 --
311 -- The g_old_rec is current therefore we must
312 -- set the returning function to true
313 --
314 l_fct_ret := true;
315 Else
316 --
317 -- Select the current row into g_old_rec
318 --
319 Open C_Sel1;
320 Fetch C_Sel1
321 Into g_old_rec;
322 If C_Sel1%notfound Then
323 Close C_Sel1;
324 --
325 -- The primary key is invalid therefore we must error
326 --
327 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
328 hr_utility.raise_error;
329 End If;
330 Close C_Sel1;
331 If (p_object_version_number <> g_old_rec.object_version_number) Then
332 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
333 hr_utility.raise_error;
334 End If;
335 hr_utility.set_location(l_proc, 15);
336 l_fct_ret := true;
337 End If;
338 hr_utility.set_location(' Leaving:'||l_proc, 20);
339 Return (l_fct_ret);
340 --
341 End api_updating;
342 --
343 -- ----------------------------------------------------------------------------
344 -- |---------------------------------< lck >----------------------------------|
345 -- ----------------------------------------------------------------------------
346 Procedure lck
347 (
348 p_event_id in number,
349 p_object_version_number in number
350 ) is
351 --
352 -- Cursor selects the 'current' row from the HR Schema
353 --
354 Cursor C_Sel1 is
355 select event_id,
356 vendor_id,
357 activity_version_id,
358 business_group_id,
359 organization_id,
360 event_type,
361 object_version_number,
362 title,
363 budget_cost,
364 actual_cost,
365 budget_currency_code,
366 centre,
367 comments,
368 course_end_date,
369 course_end_time,
370 course_start_date,
371 course_start_time,
372 duration,
373 duration_units,
374 enrolment_end_date,
375 enrolment_start_date,
376 language_id,
377 user_status,
378 development_event_type,
379 event_status,
380 price_basis,
381 currency_code,
382 maximum_attendees,
383 maximum_internal_attendees,
384 minimum_attendees,
385 standard_price,
386 category_code,
387 parent_event_id,
391 evt_information_category,
388 book_independent_flag,
389 public_event_flag,
390 secure_event_flag,
392 evt_information1,
393 evt_information2,
394 evt_information3,
395 evt_information4,
396 evt_information5,
397 evt_information6,
398 evt_information7,
399 evt_information8,
400 evt_information9,
401 evt_information10,
402 evt_information11,
403 evt_information12,
404 evt_information13,
405 evt_information14,
406 evt_information15,
407 evt_information16,
408 evt_information17,
409 evt_information18,
410 evt_information19,
411 evt_information20,
412 project_id,
413 owner_id,
414 line_id,
415 org_id,
416 training_center_id,
417 location_id,
418 offering_id,
419 timezone,
420 parent_offering_id,
421 data_source,
422 event_availability
423 from ota_events
424 where event_id = p_event_id
425 for update nowait;
426 --
427 l_proc varchar2(72) := g_package||'lck';
428 --
429 Begin
430 hr_utility.set_location('Entering:'||l_proc, 5);
431 --
432 -- Add any mandatory argument checking here:
433 -- Example:
434 -- hr_api.mandatory_arg_error
435 -- (p_api_name => l_proc,
436 -- p_argument => 'object_version_number',
437 -- p_argument_value => p_object_version_number);
438 --
439 Open C_Sel1;
440 Fetch C_Sel1 Into g_old_rec;
441 If C_Sel1%notfound then
442 Close C_Sel1;
443 --
444 -- The primary key is invalid therefore we must error
445 --
446 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
447 hr_utility.raise_error;
448 End If;
449 Close C_Sel1;
450 If (p_object_version_number <> g_old_rec.object_version_number) Then
451 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
452 hr_utility.raise_error;
453 End If;
454 --
455 hr_utility.set_location(' Leaving:'||l_proc, 10);
456 --
457 -- We need to trap the ORA LOCK exception
458 --
459 Exception
460 When HR_Api.Object_Locked then
461 --
462 -- The object is locked therefore we need to supply a meaningful
463 -- error message.
464 --
465 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
466 hr_utility.set_message_token('TABLE_NAME', 'ota_events');
467 hr_utility.raise_error;
468 End lck;
469 -- ----------------------------------------------------------------------------
470 -- |-----------------------------< convert_args >-----------------------------|
471 -- ----------------------------------------------------------------------------
472 Function convert_args
473 (
474 p_event_id in number,
475 p_vendor_id in number,
476 p_activity_version_id in number,
477 p_business_group_id in number,
478 p_organization_id in number,
479 p_event_type in varchar2,
480 p_object_version_number in number,
481 p_title in varchar2,
482 p_budget_cost in number,
483 p_actual_cost in number,
484 p_budget_currency_code in varchar2,
485 p_centre in varchar2,
486 p_comments in varchar2,
487 p_course_end_date in date,
488 p_course_end_time in varchar2,
489 p_course_start_date in date,
490 p_course_start_time in varchar2,
491 p_duration in number,
492 p_duration_units in varchar2,
493 p_enrolment_end_date in date,
494 p_enrolment_start_date in date,
495 p_language_id in number,
496 p_user_status in varchar2,
497 p_development_event_type in varchar2,
498 p_event_status in varchar2,
499 p_price_basis in varchar2,
500 p_currency_code in varchar2,
501 p_maximum_attendees in number,
502 p_maximum_internal_attendees in number,
503 p_minimum_attendees in number,
504 p_standard_price in number,
505 p_category_code in varchar2,
506 p_parent_event_id in number,
507 p_book_independent_flag in varchar2,
508 p_public_event_flag in varchar2,
509 p_secure_event_flag in varchar2,
510 p_evt_information_category in varchar2,
511 p_evt_information1 in varchar2,
512 p_evt_information2 in varchar2,
513 p_evt_information3 in varchar2,
514 p_evt_information4 in varchar2,
515 p_evt_information5 in varchar2,
516 p_evt_information6 in varchar2,
517 p_evt_information7 in varchar2,
518 p_evt_information8 in varchar2,
519 p_evt_information9 in varchar2,
520 p_evt_information10 in varchar2,
521 p_evt_information11 in varchar2,
522 p_evt_information12 in varchar2,
523 p_evt_information13 in varchar2,
524 p_evt_information14 in varchar2,
525 p_evt_information15 in varchar2,
526 p_evt_information16 in varchar2,
527 p_evt_information17 in varchar2,
528 p_evt_information18 in varchar2,
529 p_evt_information19 in varchar2,
530 p_evt_information20 in varchar2,
534 p_org_id in number,
531 p_project_id in number,
532 p_owner_id in number,
533 p_line_id in number,
535 p_training_center_id in number,
536 p_location_id in number,
537 p_offering_id in number,
538 p_timezone in varchar2,
539 p_parent_offering_id in number,
540 p_data_source in varchar2,
541 p_event_availability in varchar2
542 )
543 Return g_rec_type is
544 --
545 l_rec g_rec_type;
546 l_proc varchar2(72) := g_package||'convert_args';
547 --
548 Begin
549 --
550 hr_utility.set_location('Entering:'||l_proc, 5);
551 --
552 -- Convert arguments into local l_rec structure.
553 --
554 l_rec.event_id := p_event_id;
555 l_rec.vendor_id := p_vendor_id;
556 l_rec.activity_version_id := p_activity_version_id;
557 l_rec.business_group_id := p_business_group_id;
558 l_rec.organization_id := p_organization_id;
559 l_rec.event_type := p_event_type;
560 l_rec.object_version_number := p_object_version_number;
561 l_rec.title := p_title;
562 l_rec.budget_cost := p_budget_cost;
563 l_rec.actual_cost := p_actual_cost;
564 l_rec.budget_currency_code := p_budget_currency_code;
565 l_rec.centre := p_centre;
566 l_rec.comments := p_comments;
567 l_rec.course_end_date := p_course_end_date;
568 l_rec.course_end_time := p_course_end_time;
569 l_rec.course_start_date := p_course_start_date;
570 l_rec.course_start_time := p_course_start_time;
571 l_rec.duration := p_duration;
572 l_rec.duration_units := p_duration_units;
573 l_rec.enrolment_end_date := p_enrolment_end_date;
574 l_rec.enrolment_start_date := p_enrolment_start_date;
575 l_rec.language_id := p_language_id;
576 l_rec.user_status := p_user_status;
577 l_rec.development_event_type := p_development_event_type;
578 l_rec.event_status := p_event_status;
579 l_rec.price_basis := p_price_basis;
580 l_rec.currency_code := p_currency_code;
581 l_rec.maximum_attendees := p_maximum_attendees;
582 l_rec.maximum_internal_attendees := p_maximum_internal_attendees;
583 l_rec.minimum_attendees := p_minimum_attendees;
584 l_rec.standard_price := p_standard_price;
585 l_rec.category_code := p_category_code;
586 l_rec.parent_event_id := p_parent_event_id;
587 l_rec.book_independent_flag := p_book_independent_flag;
588 l_rec.public_event_flag := p_public_event_flag;
589 l_rec.secure_event_flag := p_secure_event_flag;
590 l_rec.evt_information_category := p_evt_information_category;
591 l_rec.evt_information1 := p_evt_information1;
592 l_rec.evt_information2 := p_evt_information2;
593 l_rec.evt_information3 := p_evt_information3;
594 l_rec.evt_information4 := p_evt_information4;
595 l_rec.evt_information5 := p_evt_information5;
596 l_rec.evt_information6 := p_evt_information6;
597 l_rec.evt_information7 := p_evt_information7;
598 l_rec.evt_information8 := p_evt_information8;
599 l_rec.evt_information9 := p_evt_information9;
600 l_rec.evt_information10 := p_evt_information10;
601 l_rec.evt_information11 := p_evt_information11;
602 l_rec.evt_information12 := p_evt_information12;
603 l_rec.evt_information13 := p_evt_information13;
604 l_rec.evt_information14 := p_evt_information14;
605 l_rec.evt_information15 := p_evt_information15;
606 l_rec.evt_information16 := p_evt_information16;
607 l_rec.evt_information17 := p_evt_information17;
608 l_rec.evt_information18 := p_evt_information18;
609 l_rec.evt_information19 := p_evt_information19;
610 l_rec.evt_information20 := p_evt_information20;
611 l_rec.project_id := p_project_id;
612 l_rec.owner_id := p_owner_id;
613 l_rec.line_id := p_line_id;
614 l_rec.org_id := p_org_id;
615 l_rec.training_center_id := p_training_center_id;
616 l_rec.location_id := p_location_id;
617 l_rec.offering_id := p_offering_id;
618 l_rec.timezone := p_timezone;
619 l_rec.parent_offering_id := p_parent_offering_id;
620 l_rec.data_source := p_data_source;
621 l_rec.event_availability := p_event_availability;
622 --
623 -- Return the plsql record structure.
624 --
625 hr_utility.set_location(' Leaving:'||l_proc, 10);
626 Return(l_rec);
627 --
628 End convert_args;
629 --
630 -- ----------------------------------------------------------------------------
631 -- -------------------------< FETCH_EVENT_DETAILS >----------------------------
632 -- ----------------------------------------------------------------------------
633 --
634 -- Populates the g_fetched_rec record with the specified event's details.
635 -- These details are then used throughout the package in checks etc.
639 P_EVENT_EXISTS out nocopy boolean
636 --
637 procedure FETCH_EVENT_DETAILS (
638 P_EVENT_ID number,
640 ) is
641 --
642 W_PROC varchar2 (72)
643 := G_PACKAGE || 'FETCH_EVENT_DETAILS';
644 --
645 cursor CSR_EVENT is
646 select
647 event_id
648 , vendor_id
649 , activity_version_id
650 , business_group_id
651 , organization_id
652 , event_type
653 , object_version_number
654 , title
655 , budget_cost
656 , actual_cost
657 , budget_currency_code
658 , centre
659 , comments
660 , course_end_date
661 , course_end_time
662 , course_start_date
663 , course_start_time
664 , duration
665 , duration_units
666 , enrolment_end_date
667 , enrolment_start_date
668 , language_id
669 , user_status
670 , development_event_type
671 , event_status
672 , price_basis
673 , currency_code
674 , maximum_attendees
675 , maximum_internal_attendees
676 , minimum_attendees
677 , standard_price
678 , category_code
679 , parent_event_id
680 , book_independent_flag
681 , public_event_flag
682 , secure_event_flag
683 , evt_information_category
684 , evt_information1
685 , evt_information2
686 , evt_information3
687 , evt_information4
688 , evt_information5
689 , evt_information6
690 , evt_information7
691 , evt_information8
692 , evt_information9
693 , evt_information10
694 , evt_information11
695 , evt_information12
696 , evt_information13
697 , evt_information14
698 , evt_information15
699 , evt_information16
700 , evt_information17
701 , evt_information18
702 , evt_information19
703 , evt_information20
704 , project_id
705 , owner_id
706 , line_id
707 , org_id
708 , training_center_id
709 , location_id
710 , offering_id
711 , timezone
712 , parent_offering_id
713 , data_source
714 , event_availability
715 from OTA_EVENTS_VL
716 where EVENT_ID = P_EVENT_ID;
717 --
718 begin
719 --
720 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
721 HR_UTILITY.TRACE ('Event id: ' || to_char (P_EVENT_ID));
722 --
723 if (P_EVENT_ID is not null) then
724 open CSR_EVENT;
725 fetch CSR_EVENT
726 into G_FETCHED_REC;
727 P_EVENT_EXISTS := CSR_EVENT%found;
728 close CSR_EVENT;
729 --
730 else
731 G_FETCHED_REC := G_NULL_REC;
732 P_EVENT_EXISTS := false;
733 end if;
734 --
735 HR_UTILITY.TRACE ('Fetch, start date: ' || to_char (G_FETCHED_REC.COURSE_START_DATE));
736 HR_UTILITY.TRACE ('Fetch, end date: ' || to_char (G_FETCHED_REC.COURSE_END_DATE));
737 HR_UTILITY.SET_LOCATION ('Leaving:' || W_PROC, 10);
738 --
739 end FETCH_EVENT_DETAILS;
740 --
741 -- ----------------------------------------------------------------------------
742 -- ---------------------------< CHECK_PROGRAMME_MEMBERS >----------------------
743 -- ----------------------------------------------------------------------------
744 --
745 -- Checks whether all programme members are ofnorml event status
746 --
747 function check_programme_members(p_event_id in number) return boolean is
748 --
749 l_proc varchar2(30) := 'Check_programme_members';
750 l_dummy varchar2(1);
751 l_found boolean := false;
752 --
753 cursor c1 is
754 select null
755 from ota_program_memberships mem,
756 ota_events evt
757 where mem.program_event_id = p_event_id
758 and mem.event_id = evt.event_id
759 and evt.event_status = 'P';
760 --
761 begin
762 hr_utility.set_location('Entering '||l_proc,10);
763 --
764 open c1;
765 fetch c1 into l_dummy;
766 if c1%found then
767 --
768 l_found := true;
769 --
770 end if;
771 close c1;
772 --
773 hr_utility.set_location('Leaving '||l_proc,10);
774 return l_found;
775 --
776 end check_programme_members;
777 -- ----------------------------------------------------------------------------
778 -- ---------------------------< GET_EVENT_DETAILS >----------------------------
779 -- ----------------------------------------------------------------------------
780 --
781 -- A public procedure for other objects to use to return a single,
782 -- complete event row.
783 --
784 procedure GET_EVENT_DETAILS (
785 P_EVENT_ID number,
786 P_EVENT_REC out nocopy ota_evt_shd.g_rec_type,
787 P_EVENT_EXISTS out nocopy boolean
788 ) is
789 --
790 W_PROC varchar2 (72)
791 := G_PACKAGE || 'GET_EVENT_DETAILS';
792 --
793 begin
794 --
795 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
796 --
797 FETCH_EVENT_DETAILS (P_EVENT_ID, P_EVENT_EXISTS);
798 P_EVENT_REC := G_FETCHED_REC;
799 --
800 HR_UTILITY.SET_LOCATION ('Leaving:' || W_PROC, 10);
801 --
802 end GET_EVENT_DETAILS;
803 --
804 -- ----------------------------------------------------------------------------
805 -- ---------------------------< GET_COURSE_DATES >-----------------------------
806 -- ----------------------------------------------------------------------------
807 --
808 -- Returns the course dates of a specified event.
809 --
810 procedure GET_COURSE_DATES (
814 ) is
811 P_EVENT_ID in number,
812 P_COURSE_START_DATE in out nocopy date,
813 P_COURSE_END_DATE in out nocopy date
815 --
816 W_PROC varchar2 (72)
817 := G_PACKAGE || 'GET_COURSE_DATES';
818 W_EVENT_EXISTS boolean;
819 --
820 begin
821 --
822 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
823 --
824 HR_API.MANDATORY_ARG_ERROR (
825 P_API_NAME => G_PACKAGE,
826 P_ARGUMENT => 'P_EVENT_ID',
827 P_ARGUMENT_VALUE => P_EVENT_ID);
828 --
829 OTA_EVT_SHD.FETCH_EVENT_DETAILS (
830 P_EVENT_ID => P_EVENT_ID,
831 P_EVENT_EXISTS => W_EVENT_EXISTS);
832 --
833 if W_EVENT_EXISTS then
834 --
835 P_COURSE_START_DATE := G_FETCHED_REC.COURSE_START_DATE;
836 P_COURSE_END_DATE := G_FETCHED_REC.COURSE_END_DATE;
837 --
838 else
839 --
840 FND_MESSAGE.SET_NAME (810, 'OTA_13205_GEN_PARAMETERS');
841 FND_MESSAGE.SET_TOKEN ('PROCEDURE', W_PROC);
842 FND_MESSAGE.SET_TOKEN ('SPECIFIC_MESSAGE', 'P_EVENT_ID does not identify a valid event');
843 FND_MESSAGE.RAISE_ERROR;
844 --
845 end if;
846 --
847 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
848 --
849 end GET_COURSE_DATES;
850 --
851 -- ----------------------------------------------------------------------------
852 -- -----------------------< CHECK_EVENT_IS_VALID >-----------------------------
853 -- ----------------------------------------------------------------------------
854 --
855 -- Raises an error if the event ID used does not exist on OTA_EVENTS.
856 --
857 procedure CHECK_EVENT_IS_VALID (
858 P_EVENT_ID in number
859 ) is
860 --
861 W_PROC varchar2 (72)
862 := G_PACKAGE || 'CHECK_EVENT_IS_VALID';
863 --
864 W_EVENT_IS_VALID boolean;
865 --
866 begin
867 --
868 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC,5);
869 --
870 if (P_EVENT_ID is not null) then
871 FETCH_EVENT_DETAILS (
872 P_EVENT_ID => P_EVENT_ID,
873 P_EVENT_EXISTS => W_EVENT_IS_VALID);
874 if (not W_EVENT_IS_VALID) then
875 CONSTRAINT_ERROR ('OTA_EVT_INVALID_EVENT');
876 end if;
877 end if;
878 --
879 HR_UTILITY.SET_LOCATION ('Leaving:'||W_PROC,10);
880 --
881 end CHECK_EVENT_IS_VALID;
882 --
883 -- ----------------------------------------------------------------------------
884 -- ---------------------< RESOURCE_BOOKING_FLAG >------------------------------
885 -- ----------------------------------------------------------------------------
886 --
887 -- Returns 'Y' if event has any resources booked to it.
888 --
889 -- NB: This function may be used in views so do not add code which
890 -- will affect variables outside the scope of this function.
891 --
892 function RESOURCE_BOOKING_FLAG (
893 P_EVENT_ID in number
894 ) return varchar2 is
895 --
896 W_DUMMY number (1);
897 W_RESOURCES_BOOKED boolean;
898 --
899 cursor C1 is
900 select 1
901 from OTA_RESOURCE_BOOKINGS TRB
902 where TRB.EVENT_ID = P_EVENT_ID;
903 --
904 begin
905 --
906 open C1;
907 fetch C1
908 into W_DUMMY;
909 W_RESOURCES_BOOKED := C1%found;
910 close C1;
911 --
912 if (W_RESOURCES_BOOKED) then
913 return ('Y');
914 else
915 return ('N');
916 end if;
917 --
918 end RESOURCE_BOOKING_FLAG;
919 --
920 -- ----------------------------------------------------------------------------
921 -- -----------------------< PUBLIC_EVENT_FLAG >--------------------------------
922 -- ----------------------------------------------------------------------------
923 --
924 -- Returns 'N' if the event has no event associations and thus may be
925 -- booked by anyone. If rows do exist in event associations for the
926 -- event, then only those organisations with associations may book
927 -- students to the event.
928 --
929 -- NB: This function may be used in views so do not add code which
930 -- will affect variables outside the scope of this function.
931 --
932 function PUBLIC_EVENT_FLAG (
933 P_EVENT_ID in number
934 ) return varchar2 is
935 --
936 W_DUMMY number (1);
937 W_PUBLIC_EVENT boolean;
938 --
939 cursor C1 is
940 select 1
941 from OTA_EVENT_ASSOCIATIONS TEA
942 where TEA.EVENT_ID = P_EVENT_ID;
943 --
944 begin
945 --
946 open C1;
947 fetch C1
948 into W_DUMMY;
949 W_PUBLIC_EVENT := C1%notfound;
950 close C1;
951 --
952 if (W_PUBLIC_EVENT) then
953 return ('Y');
954 else
955 return ('N');
956 end if;
957 --
958 end PUBLIC_EVENT_FLAG;
959 --
960 -- ----------------------------------------------------------------------------
961 -- -----------------------< INVOICED_AMOUNT_TOTAL >----------------------------
962 -- ----------------------------------------------------------------------------
963 --
964 -- Returns the total invoiced amount for an event.
965 --
966 -- NB: This function may be used in views so do not add code which
967 -- will affect variables outside the scope of this function.
968 --
969 function INVOICED_AMOUNT_TOTAL (
970 P_EVENT_ID in number
971 ) return number is
972 --
976 OTA_DELEGATE_BOOKINGS TDB
973 cursor C1 is
974 select sum (TFL.MONEY_AMOUNT)
975 from OTA_FINANCE_LINES TFL,
977 where TDB.EVENT_ID = P_EVENT_ID
978 and TFL.BOOKING_ID = TDB.BOOKING_ID
979 and TFL.CANCELLED_FLAG <> 'Y';
980
981
982 cursor C2 is
983 select ol.unit_selling_price,
984 ol.line_id
985 from ota_events evt,
986 oe_order_lines_all ol
987 where evt.event_id = p_event_id and
988 ol.line_id = evt.line_id;
989
990 --
991 w_line_id ota_events.line_id%type;
992 W_TOTAL number;
993 --
994 begin
995 --
996 -- Fetch invoiced amount
997 --
998 OPEN C2;
999 FETCH C2 INTO w_total,w_line_id;
1000 IF (C2%notfound) THEN
1001 null;
1002 END IF;
1003 CLOSE c2;
1004 IF w_line_id is null THEN
1005 open C1;
1006 fetch C1
1007 into W_TOTAL;
1008 if (C1%notfound) then
1009 null;
1010 end if;
1011 close C1;
1012 END IF;
1013 --
1014 return (W_TOTAL);
1015 --
1016 end INVOICED_AMOUNT_TOTAL;
1017 --
1018 end ota_evt_shd;