[Home] [Help]
PACKAGE BODY: APPS.OTA_EVT_SHD
Source
1 PACKAGE BODY OTA_EVT_SHD as
2 /* $Header: otevt01t.pkb 120.13.12010000.2 2008/09/17 08:05:36 srgnanas ship $ */
3 --
4 -- ----------------------------------------------------------------------------
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');
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');
104 fnd_message.raise_error;
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 --
208 -- ----------------------------------------------------------------------------
209 -- |-----------------------------< api_updating >-----------------------------|
210 -- ----------------------------------------------------------------------------
211 Function api_updating
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 from ota_events
290 where event_id = p_event_id;
291 --
292 l_proc varchar2(72) := g_package||'api_updating';
293 l_fct_ret boolean;
294 --
295 Begin
296 --
297 hr_utility.set_location('Entering:'||l_proc, 5);
298 --
299 if ( (P_EVENT_ID is null)
300 and (P_OBJECT_VERSION_NUMBER is null)) then
301 --
302 -- One of the primary key arguments is null therefore we must
303 -- set the returning function value to false
304 --
305 l_fct_ret := false;
306 elsif ( (p_event_id = g_old_rec.event_id )
307 and (p_object_version_number = g_old_rec.object_version_number)) then
308 hr_utility.set_location(l_proc, 10);
309 --
310 -- The g_old_rec is current therefore we must
311 -- set the returning function to true
312 --
313 l_fct_ret := true;
314 Else
315 --
316 -- Select the current row into g_old_rec
317 --
318 Open C_Sel1;
319 Fetch C_Sel1
320 Into g_old_rec;
321 If C_Sel1%notfound Then
322 Close C_Sel1;
323 --
324 -- The primary key is invalid therefore we must error
325 --
326 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
327 hr_utility.raise_error;
328 End If;
329 Close C_Sel1;
330 If (p_object_version_number <> g_old_rec.object_version_number) Then
331 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
332 hr_utility.raise_error;
333 End If;
334 hr_utility.set_location(l_proc, 15);
335 l_fct_ret := true;
336 End If;
337 hr_utility.set_location(' Leaving:'||l_proc, 20);
338 Return (l_fct_ret);
339 --
340 End api_updating;
341 --
342 -- ----------------------------------------------------------------------------
343 -- |---------------------------------< lck >----------------------------------|
344 -- ----------------------------------------------------------------------------
345 Procedure lck
346 (
347 p_event_id in number,
348 p_object_version_number in number
349 ) is
350 --
351 -- Cursor selects the 'current' row from the HR Schema
352 --
353 Cursor C_Sel1 is
354 select event_id,
355 vendor_id,
356 activity_version_id,
357 business_group_id,
358 organization_id,
359 event_type,
360 object_version_number,
361 title,
362 budget_cost,
363 actual_cost,
364 budget_currency_code,
365 centre,
366 comments,
367 course_end_date,
368 course_end_time,
369 course_start_date,
370 course_start_time,
371 duration,
372 duration_units,
373 enrolment_end_date,
374 enrolment_start_date,
375 language_id,
376 user_status,
377 development_event_type,
378 event_status,
379 price_basis,
380 currency_code,
381 maximum_attendees,
382 maximum_internal_attendees,
383 minimum_attendees,
384 standard_price,
385 category_code,
386 parent_event_id,
387 book_independent_flag,
388 public_event_flag,
389 secure_event_flag,
390 evt_information_category,
391 evt_information1,
392 evt_information2,
396 evt_information6,
393 evt_information3,
394 evt_information4,
395 evt_information5,
397 evt_information7,
398 evt_information8,
399 evt_information9,
400 evt_information10,
401 evt_information11,
402 evt_information12,
403 evt_information13,
404 evt_information14,
405 evt_information15,
406 evt_information16,
407 evt_information17,
408 evt_information18,
409 evt_information19,
410 evt_information20,
411 project_id,
412 owner_id,
413 line_id,
414 org_id,
415 training_center_id,
416 location_id,
417 offering_id,
418 timezone,
419 parent_offering_id,
420 data_source
421 from ota_events
422 where event_id = p_event_id
423 for update nowait;
424 --
425 l_proc varchar2(72) := g_package||'lck';
426 --
427 Begin
428 hr_utility.set_location('Entering:'||l_proc, 5);
429 --
430 -- Add any mandatory argument checking here:
431 -- Example:
432 -- hr_api.mandatory_arg_error
433 -- (p_api_name => l_proc,
434 -- p_argument => 'object_version_number',
435 -- p_argument_value => p_object_version_number);
436 --
437 Open C_Sel1;
438 Fetch C_Sel1 Into g_old_rec;
439 If C_Sel1%notfound then
440 Close C_Sel1;
441 --
442 -- The primary key is invalid therefore we must error
443 --
444 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
445 hr_utility.raise_error;
446 End If;
447 Close C_Sel1;
448 If (p_object_version_number <> g_old_rec.object_version_number) Then
449 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
450 hr_utility.raise_error;
451 End If;
452 --
453 hr_utility.set_location(' Leaving:'||l_proc, 10);
454 --
455 -- We need to trap the ORA LOCK exception
456 --
457 Exception
458 When HR_Api.Object_Locked then
459 --
460 -- The object is locked therefore we need to supply a meaningful
461 -- error message.
462 --
463 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
464 hr_utility.set_message_token('TABLE_NAME', 'ota_events');
465 hr_utility.raise_error;
466 End lck;
467 -- ----------------------------------------------------------------------------
468 -- |-----------------------------< convert_args >-----------------------------|
469 -- ----------------------------------------------------------------------------
470 Function convert_args
471 (
472 p_event_id in number,
473 p_vendor_id in number,
474 p_activity_version_id in number,
475 p_business_group_id in number,
476 p_organization_id in number,
477 p_event_type in varchar2,
478 p_object_version_number in number,
479 p_title in varchar2,
480 p_budget_cost in number,
481 p_actual_cost in number,
482 p_budget_currency_code in varchar2,
483 p_centre in varchar2,
484 p_comments in varchar2,
485 p_course_end_date in date,
486 p_course_end_time in varchar2,
487 p_course_start_date in date,
488 p_course_start_time in varchar2,
489 p_duration in number,
490 p_duration_units in varchar2,
491 p_enrolment_end_date in date,
492 p_enrolment_start_date in date,
493 p_language_id in number,
494 p_user_status in varchar2,
495 p_development_event_type in varchar2,
496 p_event_status in varchar2,
497 p_price_basis in varchar2,
498 p_currency_code in varchar2,
499 p_maximum_attendees in number,
500 p_maximum_internal_attendees in number,
501 p_minimum_attendees in number,
502 p_standard_price in number,
503 p_category_code in varchar2,
504 p_parent_event_id in number,
505 p_book_independent_flag in varchar2,
506 p_public_event_flag in varchar2,
507 p_secure_event_flag in varchar2,
508 p_evt_information_category in varchar2,
509 p_evt_information1 in varchar2,
510 p_evt_information2 in varchar2,
511 p_evt_information3 in varchar2,
512 p_evt_information4 in varchar2,
513 p_evt_information5 in varchar2,
514 p_evt_information6 in varchar2,
515 p_evt_information7 in varchar2,
516 p_evt_information8 in varchar2,
517 p_evt_information9 in varchar2,
518 p_evt_information10 in varchar2,
519 p_evt_information11 in varchar2,
520 p_evt_information12 in varchar2,
521 p_evt_information13 in varchar2,
522 p_evt_information14 in varchar2,
523 p_evt_information15 in varchar2,
524 p_evt_information16 in varchar2,
525 p_evt_information17 in varchar2,
526 p_evt_information18 in varchar2,
527 p_evt_information19 in varchar2,
528 p_evt_information20 in varchar2,
532 p_org_id in number,
529 p_project_id in number,
530 p_owner_id in number,
531 p_line_id in number,
533 p_training_center_id in number,
534 p_location_id in number,
535 p_offering_id in number,
536 p_timezone in varchar2,
537 p_parent_offering_id in number,
538 p_data_source in varchar2
539 )
540 Return g_rec_type is
541 --
542 l_rec g_rec_type;
543 l_proc varchar2(72) := g_package||'convert_args';
544 --
545 Begin
546 --
547 hr_utility.set_location('Entering:'||l_proc, 5);
548 --
549 -- Convert arguments into local l_rec structure.
550 --
551 l_rec.event_id := p_event_id;
552 l_rec.vendor_id := p_vendor_id;
553 l_rec.activity_version_id := p_activity_version_id;
554 l_rec.business_group_id := p_business_group_id;
555 l_rec.organization_id := p_organization_id;
556 l_rec.event_type := p_event_type;
557 l_rec.object_version_number := p_object_version_number;
558 l_rec.title := p_title;
559 l_rec.budget_cost := p_budget_cost;
560 l_rec.actual_cost := p_actual_cost;
561 l_rec.budget_currency_code := p_budget_currency_code;
562 l_rec.centre := p_centre;
563 l_rec.comments := p_comments;
564 l_rec.course_end_date := p_course_end_date;
565 l_rec.course_end_time := p_course_end_time;
566 l_rec.course_start_date := p_course_start_date;
567 l_rec.course_start_time := p_course_start_time;
568 l_rec.duration := p_duration;
569 l_rec.duration_units := p_duration_units;
570 l_rec.enrolment_end_date := p_enrolment_end_date;
571 l_rec.enrolment_start_date := p_enrolment_start_date;
572 l_rec.language_id := p_language_id;
573 l_rec.user_status := p_user_status;
574 l_rec.development_event_type := p_development_event_type;
575 l_rec.event_status := p_event_status;
576 l_rec.price_basis := p_price_basis;
577 l_rec.currency_code := p_currency_code;
578 l_rec.maximum_attendees := p_maximum_attendees;
579 l_rec.maximum_internal_attendees := p_maximum_internal_attendees;
580 l_rec.minimum_attendees := p_minimum_attendees;
581 l_rec.standard_price := p_standard_price;
582 l_rec.category_code := p_category_code;
583 l_rec.parent_event_id := p_parent_event_id;
584 l_rec.book_independent_flag := p_book_independent_flag;
585 l_rec.public_event_flag := p_public_event_flag;
586 l_rec.secure_event_flag := p_secure_event_flag;
587 l_rec.evt_information_category := p_evt_information_category;
588 l_rec.evt_information1 := p_evt_information1;
589 l_rec.evt_information2 := p_evt_information2;
590 l_rec.evt_information3 := p_evt_information3;
591 l_rec.evt_information4 := p_evt_information4;
592 l_rec.evt_information5 := p_evt_information5;
593 l_rec.evt_information6 := p_evt_information6;
594 l_rec.evt_information7 := p_evt_information7;
595 l_rec.evt_information8 := p_evt_information8;
596 l_rec.evt_information9 := p_evt_information9;
597 l_rec.evt_information10 := p_evt_information10;
598 l_rec.evt_information11 := p_evt_information11;
599 l_rec.evt_information12 := p_evt_information12;
600 l_rec.evt_information13 := p_evt_information13;
601 l_rec.evt_information14 := p_evt_information14;
602 l_rec.evt_information15 := p_evt_information15;
603 l_rec.evt_information16 := p_evt_information16;
604 l_rec.evt_information17 := p_evt_information17;
605 l_rec.evt_information18 := p_evt_information18;
606 l_rec.evt_information19 := p_evt_information19;
607 l_rec.evt_information20 := p_evt_information20;
608 l_rec.project_id := p_project_id;
609 l_rec.owner_id := p_owner_id;
610 l_rec.line_id := p_line_id;
611 l_rec.org_id := p_org_id;
612 l_rec.training_center_id := p_training_center_id;
613 l_rec.location_id := p_location_id;
614 l_rec.offering_id := p_offering_id;
615 l_rec.timezone := p_timezone;
616 l_rec.parent_offering_id := p_parent_offering_id;
617 l_rec.data_source := p_data_source;
618 --
619 -- Return the plsql record structure.
620 --
621 hr_utility.set_location(' Leaving:'||l_proc, 10);
622 Return(l_rec);
623 --
624 End convert_args;
625 --
626 -- ----------------------------------------------------------------------------
630 -- Populates the g_fetched_rec record with the specified event's details.
627 -- -------------------------< FETCH_EVENT_DETAILS >----------------------------
628 -- ----------------------------------------------------------------------------
629 --
631 -- These details are then used throughout the package in checks etc.
632 --
633 procedure FETCH_EVENT_DETAILS (
634 P_EVENT_ID number,
635 P_EVENT_EXISTS out nocopy boolean
636 ) is
637 --
638 W_PROC varchar2 (72)
639 := G_PACKAGE || 'FETCH_EVENT_DETAILS';
640 --
641 cursor CSR_EVENT is
642 select
643 event_id
644 , vendor_id
645 , activity_version_id
646 , business_group_id
647 , organization_id
648 , event_type
649 , object_version_number
650 , title
651 , budget_cost
652 , actual_cost
653 , budget_currency_code
654 , centre
655 , comments
656 , course_end_date
657 , course_end_time
658 , course_start_date
659 , course_start_time
660 , duration
661 , duration_units
662 , enrolment_end_date
663 , enrolment_start_date
664 , language_id
665 , user_status
666 , development_event_type
667 , event_status
668 , price_basis
669 , currency_code
670 , maximum_attendees
671 , maximum_internal_attendees
672 , minimum_attendees
673 , standard_price
674 , category_code
675 , parent_event_id
676 , book_independent_flag
677 , public_event_flag
678 , secure_event_flag
679 , evt_information_category
680 , evt_information1
681 , evt_information2
682 , evt_information3
683 , evt_information4
684 , evt_information5
685 , evt_information6
686 , evt_information7
687 , evt_information8
688 , evt_information9
689 , evt_information10
690 , evt_information11
691 , evt_information12
692 , evt_information13
693 , evt_information14
694 , evt_information15
695 , evt_information16
696 , evt_information17
697 , evt_information18
698 , evt_information19
699 , evt_information20
700 , project_id
701 , owner_id
702 , line_id
703 , org_id
704 , training_center_id
705 , location_id
706 , offering_id
707 , timezone
708 , parent_offering_id
709 , data_source
710 from OTA_EVENTS_VL
711 where EVENT_ID = P_EVENT_ID;
712 --
713 begin
714 --
715 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
716 HR_UTILITY.TRACE ('Event id: ' || to_char (P_EVENT_ID));
717 --
718 if (P_EVENT_ID is not null) then
719 open CSR_EVENT;
720 fetch CSR_EVENT
721 into G_FETCHED_REC;
722 P_EVENT_EXISTS := CSR_EVENT%found;
723 close CSR_EVENT;
724 --
725 else
726 G_FETCHED_REC := G_NULL_REC;
727 P_EVENT_EXISTS := false;
728 end if;
729 --
730 HR_UTILITY.TRACE ('Fetch, start date: ' || to_char (G_FETCHED_REC.COURSE_START_DATE));
731 HR_UTILITY.TRACE ('Fetch, end date: ' || to_char (G_FETCHED_REC.COURSE_END_DATE));
732 HR_UTILITY.SET_LOCATION ('Leaving:' || W_PROC, 10);
733 --
734 end FETCH_EVENT_DETAILS;
735 --
736 -- ----------------------------------------------------------------------------
737 -- ---------------------------< CHECK_PROGRAMME_MEMBERS >----------------------
738 -- ----------------------------------------------------------------------------
739 --
740 -- Checks whether all programme members are ofnorml event status
741 --
742 function check_programme_members(p_event_id in number) return boolean is
743 --
744 l_proc varchar2(30) := 'Check_programme_members';
745 l_dummy varchar2(1);
746 l_found boolean := false;
747 --
748 cursor c1 is
749 select null
750 from ota_program_memberships mem,
751 ota_events evt
752 where mem.program_event_id = p_event_id
753 and mem.event_id = evt.event_id
754 and evt.event_status = 'P';
755 --
756 begin
757 hr_utility.set_location('Entering '||l_proc,10);
758 --
759 open c1;
760 fetch c1 into l_dummy;
761 if c1%found then
762 --
763 l_found := true;
764 --
765 end if;
766 close c1;
767 --
768 hr_utility.set_location('Leaving '||l_proc,10);
769 return l_found;
770 --
771 end check_programme_members;
772 -- ----------------------------------------------------------------------------
773 -- ---------------------------< GET_EVENT_DETAILS >----------------------------
774 -- ----------------------------------------------------------------------------
775 --
776 -- A public procedure for other objects to use to return a single,
777 -- complete event row.
778 --
779 procedure GET_EVENT_DETAILS (
780 P_EVENT_ID number,
781 P_EVENT_REC out nocopy ota_evt_shd.g_rec_type,
782 P_EVENT_EXISTS out nocopy boolean
783 ) is
784 --
785 W_PROC varchar2 (72)
786 := G_PACKAGE || 'GET_EVENT_DETAILS';
787 --
788 begin
789 --
790 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
791 --
792 FETCH_EVENT_DETAILS (P_EVENT_ID, P_EVENT_EXISTS);
793 P_EVENT_REC := G_FETCHED_REC;
794 --
795 HR_UTILITY.SET_LOCATION ('Leaving:' || W_PROC, 10);
796 --
797 end GET_EVENT_DETAILS;
798 --
799 -- ----------------------------------------------------------------------------
803 -- Returns the course dates of a specified event.
800 -- ---------------------------< GET_COURSE_DATES >-----------------------------
801 -- ----------------------------------------------------------------------------
802 --
804 --
805 procedure GET_COURSE_DATES (
806 P_EVENT_ID in number,
807 P_COURSE_START_DATE in out nocopy date,
808 P_COURSE_END_DATE in out nocopy date
809 ) is
810 --
811 W_PROC varchar2 (72)
812 := G_PACKAGE || 'GET_COURSE_DATES';
813 W_EVENT_EXISTS boolean;
814 --
815 begin
816 --
817 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
818 --
819 HR_API.MANDATORY_ARG_ERROR (
820 P_API_NAME => G_PACKAGE,
821 P_ARGUMENT => 'P_EVENT_ID',
822 P_ARGUMENT_VALUE => P_EVENT_ID);
823 --
824 OTA_EVT_SHD.FETCH_EVENT_DETAILS (
825 P_EVENT_ID => P_EVENT_ID,
826 P_EVENT_EXISTS => W_EVENT_EXISTS);
827 --
828 if W_EVENT_EXISTS then
829 --
830 P_COURSE_START_DATE := G_FETCHED_REC.COURSE_START_DATE;
831 P_COURSE_END_DATE := G_FETCHED_REC.COURSE_END_DATE;
832 --
833 else
834 --
835 FND_MESSAGE.SET_NAME (810, 'OTA_13205_GEN_PARAMETERS');
836 FND_MESSAGE.SET_TOKEN ('PROCEDURE', W_PROC);
837 FND_MESSAGE.SET_TOKEN ('SPECIFIC_MESSAGE', 'P_EVENT_ID does not identify a valid event');
838 FND_MESSAGE.RAISE_ERROR;
839 --
840 end if;
841 --
842 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
843 --
844 end GET_COURSE_DATES;
845 --
846 -- ----------------------------------------------------------------------------
847 -- -----------------------< CHECK_EVENT_IS_VALID >-----------------------------
848 -- ----------------------------------------------------------------------------
849 --
850 -- Raises an error if the event ID used does not exist on OTA_EVENTS.
851 --
852 procedure CHECK_EVENT_IS_VALID (
853 P_EVENT_ID in number
854 ) is
855 --
856 W_PROC varchar2 (72)
857 := G_PACKAGE || 'CHECK_EVENT_IS_VALID';
858 --
859 W_EVENT_IS_VALID boolean;
860 --
861 begin
862 --
863 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC,5);
864 --
865 if (P_EVENT_ID is not null) then
866 FETCH_EVENT_DETAILS (
867 P_EVENT_ID => P_EVENT_ID,
868 P_EVENT_EXISTS => W_EVENT_IS_VALID);
869 if (not W_EVENT_IS_VALID) then
870 CONSTRAINT_ERROR ('OTA_EVT_INVALID_EVENT');
871 end if;
872 end if;
873 --
874 HR_UTILITY.SET_LOCATION ('Leaving:'||W_PROC,10);
875 --
876 end CHECK_EVENT_IS_VALID;
877 --
878 -- ----------------------------------------------------------------------------
879 -- ---------------------< RESOURCE_BOOKING_FLAG >------------------------------
880 -- ----------------------------------------------------------------------------
881 --
882 -- Returns 'Y' if event has any resources booked to it.
883 --
884 -- NB: This function may be used in views so do not add code which
885 -- will affect variables outside the scope of this function.
886 --
887 function RESOURCE_BOOKING_FLAG (
888 P_EVENT_ID in number
889 ) return varchar2 is
890 --
891 W_DUMMY number (1);
892 W_RESOURCES_BOOKED boolean;
893 --
894 cursor C1 is
895 select 1
896 from OTA_RESOURCE_BOOKINGS TRB
897 where TRB.EVENT_ID = P_EVENT_ID;
898 --
899 begin
900 --
901 open C1;
902 fetch C1
903 into W_DUMMY;
904 W_RESOURCES_BOOKED := C1%found;
905 close C1;
906 --
907 if (W_RESOURCES_BOOKED) then
908 return ('Y');
909 else
910 return ('N');
911 end if;
912 --
913 end RESOURCE_BOOKING_FLAG;
914 --
915 -- ----------------------------------------------------------------------------
916 -- -----------------------< PUBLIC_EVENT_FLAG >--------------------------------
917 -- ----------------------------------------------------------------------------
918 --
919 -- Returns 'N' if the event has no event associations and thus may be
920 -- booked by anyone. If rows do exist in event associations for the
921 -- event, then only those organisations with associations may book
922 -- students to the event.
923 --
924 -- NB: This function may be used in views so do not add code which
925 -- will affect variables outside the scope of this function.
926 --
927 function PUBLIC_EVENT_FLAG (
928 P_EVENT_ID in number
929 ) return varchar2 is
930 --
931 W_DUMMY number (1);
932 W_PUBLIC_EVENT boolean;
933 --
934 cursor C1 is
935 select 1
936 from OTA_EVENT_ASSOCIATIONS TEA
937 where TEA.EVENT_ID = P_EVENT_ID;
938 --
939 begin
940 --
941 open C1;
942 fetch C1
943 into W_DUMMY;
944 W_PUBLIC_EVENT := C1%notfound;
945 close C1;
946 --
947 if (W_PUBLIC_EVENT) then
948 return ('Y');
949 else
950 return ('N');
951 end if;
952 --
953 end PUBLIC_EVENT_FLAG;
954 --
955 -- ----------------------------------------------------------------------------
956 -- -----------------------< INVOICED_AMOUNT_TOTAL >----------------------------
957 -- ----------------------------------------------------------------------------
958 --
959 -- Returns the total invoiced amount for an event.
960 --
961 -- NB: This function may be used in views so do not add code which
962 -- will affect variables outside the scope of this function.
963 --
964 function INVOICED_AMOUNT_TOTAL (
965 P_EVENT_ID in number
966 ) return number is
967 --
968 cursor C1 is
969 select sum (TFL.MONEY_AMOUNT)
970 from OTA_FINANCE_LINES TFL,
971 OTA_DELEGATE_BOOKINGS TDB
972 where TDB.EVENT_ID = P_EVENT_ID
973 and TFL.BOOKING_ID = TDB.BOOKING_ID
974 and TFL.CANCELLED_FLAG <> 'Y';
975
976
977 cursor C2 is
978 select ol.unit_selling_price,
979 ol.line_id
980 from ota_events evt,
981 oe_order_lines_all ol
982 where evt.event_id = p_event_id and
983 ol.line_id = evt.line_id;
984
985 --
986 w_line_id ota_events.line_id%type;
987 W_TOTAL number;
988 --
989 begin
990 --
991 -- Fetch invoiced amount
992 --
993 OPEN C2;
994 FETCH C2 INTO w_total,w_line_id;
995 IF (C2%notfound) THEN
996 null;
997 END IF;
998 CLOSE c2;
999 IF w_line_id is null THEN
1000 open C1;
1001 fetch C1
1002 into W_TOTAL;
1003 if (C1%notfound) then
1004 null;
1005 end if;
1006 close C1;
1007 END IF;
1008 --
1009 return (W_TOTAL);
1010 --
1011 end INVOICED_AMOUNT_TOTAL;
1012 --
1013 end ota_evt_shd;