1 PACKAGE BODY OTA_EVT_UPD as
2 /* $Header: otevt01t.pkb 120.19 2011/04/07 13:34:40 shwnayak ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_evt_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The functions of this
17 -- procedure are as follows:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row in the schema using the primary key in
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Pre Conditions:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Arguments:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated in the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' arguments list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Development Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy ota_evt_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 ota_evt_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the ota_events Row
68 --
69 update ota_events
70 set
71 event_id = p_rec.event_id,
72 vendor_id = p_rec.vendor_id,
73 activity_version_id = p_rec.activity_version_id,
74 business_group_id = p_rec.business_group_id,
75 organization_id = p_rec.organization_id,
76 event_type = p_rec.event_type,
77 object_version_number = p_rec.object_version_number,
78 title = p_rec.title,
79 budget_cost = p_rec.budget_cost,
80 actual_cost = p_rec.actual_cost,
81 budget_currency_code = p_rec.budget_currency_code,
82 centre = p_rec.centre,
83 comments = p_rec.comments,
84 course_end_date = p_rec.course_end_date,
85 course_end_time = p_rec.course_end_time,
86 course_start_date = p_rec.course_start_date,
87 course_start_time = p_rec.course_start_time,
88 duration = p_rec.duration,
89 duration_units = p_rec.duration_units,
90 enrolment_end_date = p_rec.enrolment_end_date,
91 enrolment_start_date = p_rec.enrolment_start_date,
92 language_id = p_rec.language_id,
93 user_status = p_rec.user_status,
94 development_event_type = p_rec.development_event_type,
95 event_status = p_rec.event_status,
96 price_basis = p_rec.price_basis,
97 currency_code = p_rec.currency_code,
98 maximum_attendees = p_rec.maximum_attendees,
99 maximum_internal_attendees = p_rec.maximum_internal_attendees,
100 minimum_attendees = p_rec.minimum_attendees,
101 standard_price = p_rec.standard_price,
102 category_code = p_rec.category_code,
103 parent_event_id = p_rec.parent_event_id,
104 book_independent_flag = p_rec.book_independent_flag,
105 public_event_flag = p_rec.public_event_flag,
106 secure_event_flag = p_rec.secure_event_flag,
107 evt_information_category = p_rec.evt_information_category,
108 evt_information1 = p_rec.evt_information1,
109 evt_information2 = p_rec.evt_information2,
113 evt_information6 = p_rec.evt_information6,
110 evt_information3 = p_rec.evt_information3,
111 evt_information4 = p_rec.evt_information4,
112 evt_information5 = p_rec.evt_information5,
114 evt_information7 = p_rec.evt_information7,
115 evt_information8 = p_rec.evt_information8,
116 evt_information9 = p_rec.evt_information9,
117 evt_information10 = p_rec.evt_information10,
118 evt_information11 = p_rec.evt_information11,
119 evt_information12 = p_rec.evt_information12,
120 evt_information13 = p_rec.evt_information13,
121 evt_information14 = p_rec.evt_information14,
122 evt_information15 = p_rec.evt_information15,
123 evt_information16 = p_rec.evt_information16,
124 evt_information17 = p_rec.evt_information17,
125 evt_information18 = p_rec.evt_information18,
126 evt_information19 = p_rec.evt_information19,
127 evt_information20 = p_rec.evt_information20,
128 project_id = p_rec.project_id,
129 owner_id = p_rec.owner_id,
130 line_id = p_rec.line_id,
131 org_id = p_rec.org_id,
132 training_center_id = p_rec.training_center_id,
133 location_id = p_rec.location_id,
134 offering_id = p_rec.offering_id,
135 timezone = p_rec.timezone,
136 parent_offering_id = p_rec.parent_offering_id,
137 data_source = p_rec.data_source,
138 event_availability = p_rec.event_availability
139 where event_id = p_rec.event_id;
140 --
141 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
142 --
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144 --
145 Exception
146 When hr_api.check_integrity_violated Then
147 -- A check constraint has been violated
148 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
149 ota_evt_shd.constraint_error
150 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
151 When hr_api.parent_integrity_violated Then
152 -- Parent integrity has been violated
153 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
154 ota_evt_shd.constraint_error
155 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
156 When hr_api.unique_integrity_violated Then
157 -- Unique integrity has been violated
158 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
159 ota_evt_shd.constraint_error
160 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
161 When Others Then
162 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
163 Raise;
164 End update_dml;
165 --
166 -- ----------------------------------------------------------------------------
167 -- |------------------------------< pre_update >------------------------------|
168 -- ----------------------------------------------------------------------------
169 -- {Start Of Comments}
170 --
171 -- Description:
172 -- This private procedure contains any processing which is required before
173 -- the update dml.
174 --
175 -- Pre Conditions:
176 -- This is an internal procedure which is called from the upd procedure.
177 --
178 -- In Arguments:
179 -- A Pl/Sql record structre.
180 --
181 -- Post Success:
182 -- Processing continues.
183 --
184 -- Post Failure:
185 -- If an error has occurred, an error message and exception will be raised
186 -- but not handled.
187 --
188 -- Developer Implementation Notes:
189 -- Any pre-processing required before the update dml is issued should be
190 -- coded within this procedure. It is important to note that any 3rd party
191 -- maintenance should be reviewed before placing in this procedure.
192 --
193 -- Access Status:
194 -- Internal Development Use Only.
195 --
196 -- {End Of Comments}
197 -- ----------------------------------------------------------------------------
198 Procedure pre_update(p_rec in ota_evt_shd.g_rec_type) is
199 --
200 l_proc varchar2(72) := g_package||'pre_update';
201 --
202 Begin
203 hr_utility.set_location('Entering:'||l_proc, 5);
204 --
205 hr_utility.set_location(' Leaving:'||l_proc, 10);
206 End pre_update;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |-----------------------------< post_update >------------------------------|
210 -- ----------------------------------------------------------------------------
211 -- {Start Of Comments}
212 --
213 -- Description:
214 -- This private procedure contains any processing which is required after the
215 -- update dml.
216 --
217 -- Pre Conditions:
218 -- This is an internal procedure which is called from the upd procedure.
219 --
220 -- In Arguments:
221 -- A Pl/Sql record structre.
222 --
223 -- Post Success:
224 -- Processing continues.
225 --
226 -- Post Failure:
227 -- If an error has occurred, an error message and exception will be raised
228 -- but not handled.
229 --
230 -- Developer Implementation Notes:
231 -- Any post-processing required after the update dml is issued should be
232 -- coded within this procedure. It is important to note that any 3rd party
233 -- maintenance should be reviewed before placing in this procedure.
234 --
235 -- Access Status:
236 -- Internal Development Use Only.
237 --
238 -- {End Of Comments}
239 -- ----------------------------------------------------------------------------
243 --
240 Procedure post_update(p_rec in ota_evt_shd.g_rec_type) is
241 --
242 l_proc varchar2(72) := g_package||'post_update';
244 Begin
245 hr_utility.set_location('Entering:'||l_proc, 5);
246 --
247 ota_evt_bus.chk_status_changed
248 (p_line_id => p_rec.line_id
249 ,p_event_status => p_rec.event_status
250 ,p_event_id => p_rec.event_id
251 ,p_org_id => p_rec.org_id
252 ,p_owner_id => p_rec.owner_id
253 ,p_event_title => p_rec.title);
254
255 hr_utility.set_location(' Leaving:'||l_proc, 10);
256 End post_update;
257 --
258 -- ----------------------------------------------------------------------------
259 -- |-----------------------------< convert_defs >-----------------------------|
260 -- ----------------------------------------------------------------------------
261 -- {Start Of Comments}
262 --
263 -- Description:
264 -- The Convert_Defs function has one very important function:
265 -- It must return the record structure for the row with all system defaulted
266 -- values converted into its corresponding argument value for update. When
267 -- we attempt to update a row through the Upd business process , certain
268 -- arguments can be defaulted which enables flexibility in the calling of
269 -- the upd process (e.g. only attributes which need to be updated need to be
270 -- specified). For the upd business process to determine which attributes
271 -- have NOT been specified we need to check if the argument has a reserved
272 -- system default value. Therefore, for all attributes which have a
273 -- corresponding reserved system default mechanism specified we need to
274 -- check if a system default is being used. If a system default is being
275 -- used then we convert the defaulted value into its corresponding attribute
276 -- value held in the g_old_rec data structure.
277 --
278 -- Pre Conditions:
279 -- This private function can only be called from the upd process.
280 --
281 -- In Arguments:
282 -- A Pl/Sql record structre.
283 --
284 -- Post Success:
285 -- The record structure will be returned with all system defaulted argument
286 -- values converted into its current row attribute value.
287 --
288 -- Post Failure:
289 -- No direct error handling is required within this function. Any possible
293 -- Developer Implementation Notes:
290 -- errors within this function will be a PL/SQL value error due to conversion
291 -- of datatypes or data lengths.
292 --
294 -- None.
295 --
296 -- Access Status:
297 -- Internal Development Use Only.
298 --
299 -- {End Of Comments}
300 -- ----------------------------------------------------------------------------
301 Function convert_defs(p_rec in out nocopy ota_evt_shd.g_rec_type)
302 Return ota_evt_shd.g_rec_type is
303 --
304 l_proc varchar2(72) := g_package||'convert_defs';
305 --
306 Begin
307 --
308 hr_utility.set_location('Entering:'||l_proc, 5);
309 --
310 -- We must now examine each argument value in the
311 -- p_rec plsql record structure
312 -- to see if a system default is being used. If a system default
313 -- is being used then we must set to the 'current' argument value.
314 --
315 If (p_rec.vendor_id = hr_api.g_number) then
316 p_rec.vendor_id :=
317 ota_evt_shd.g_old_rec.vendor_id;
318 End If;
319 If (p_rec.activity_version_id = hr_api.g_number) then
320 p_rec.activity_version_id :=
321 ota_evt_shd.g_old_rec.activity_version_id;
322 End If;
323 If (p_rec.business_group_id = hr_api.g_number) then
324 p_rec.business_group_id :=
325 ota_evt_shd.g_old_rec.business_group_id;
326 End If;
327 If (p_rec.organization_id = hr_api.g_number) then
328 p_rec.organization_id :=
329 ota_evt_shd.g_old_rec.organization_id;
330 End If;
331 If (p_rec.event_type = hr_api.g_varchar2) then
332 p_rec.event_type :=
333 ota_evt_shd.g_old_rec.event_type;
334 End If;
335 If (p_rec.title = hr_api.g_varchar2) then
336 p_rec.title :=
337 ota_evt_shd.g_old_rec.title;
338 End If;
339 If (p_rec.budget_cost = hr_api.g_number) then
340 p_rec.budget_cost :=
341 ota_evt_shd.g_old_rec.budget_cost;
342 End If;
343 If (p_rec.actual_cost = hr_api.g_number) then
344 p_rec.actual_cost :=
345 ota_evt_shd.g_old_rec.actual_cost;
346 End If;
347 If (p_rec.budget_currency_code = hr_api.g_varchar2) then
348 p_rec.budget_currency_code :=
349 ota_evt_shd.g_old_rec.budget_currency_code;
350 End If;
351 If (p_rec.centre = hr_api.g_varchar2) then
355 If (p_rec.comments = hr_api.g_varchar2) then
352 p_rec.centre :=
353 ota_evt_shd.g_old_rec.centre;
354 End If;
356 p_rec.comments :=
357 ota_evt_shd.g_old_rec.comments;
358 End If;
359 If (p_rec.course_end_date = hr_api.g_date) then
360 p_rec.course_end_date :=
361 ota_evt_shd.g_old_rec.course_end_date;
362 End If;
363 If (p_rec.course_end_time = hr_api.g_varchar2) then
364 p_rec.course_end_time :=
365 ota_evt_shd.g_old_rec.course_end_time;
366 End If;
367 If (p_rec.course_start_date = hr_api.g_date) then
368 p_rec.course_start_date :=
369 ota_evt_shd.g_old_rec.course_start_date;
370 End If;
371 If (p_rec.course_start_time = hr_api.g_varchar2) then
372 p_rec.course_start_time :=
373 ota_evt_shd.g_old_rec.course_start_time;
374 End If;
375 If (p_rec.duration = hr_api.g_number) then
376 p_rec.duration :=
377 ota_evt_shd.g_old_rec.duration;
378 End If;
379 If (p_rec.duration_units = hr_api.g_varchar2) then
380 p_rec.duration_units :=
381 ota_evt_shd.g_old_rec.duration_units;
382 End If;
383 If (p_rec.enrolment_end_date = hr_api.g_date) then
384 p_rec.enrolment_end_date :=
385 ota_evt_shd.g_old_rec.enrolment_end_date;
386 End If;
387 If (p_rec.enrolment_start_date = hr_api.g_date) then
388 p_rec.enrolment_start_date :=
389 ota_evt_shd.g_old_rec.enrolment_start_date;
390 End If;
391 If (p_rec.language_id = hr_api.g_number) then
392 p_rec.language_id :=
393 ota_evt_shd.g_old_rec.language_id;
394 End If;
395 If (p_rec.user_status = hr_api.g_varchar2) then
396 p_rec.user_status :=
397 ota_evt_shd.g_old_rec.user_status;
398 End If;
399 If (p_rec.development_event_type = hr_api.g_varchar2) then
400 p_rec.development_event_type :=
401 ota_evt_shd.g_old_rec.development_event_type;
402 End If;
403 If (p_rec.event_status = hr_api.g_varchar2) then
404 p_rec.event_status :=
405 ota_evt_shd.g_old_rec.event_status;
406 End If;
407 If (p_rec.price_basis = hr_api.g_varchar2) then
408 p_rec.price_basis :=
409 ota_evt_shd.g_old_rec.price_basis;
410 End If;
411 If (p_rec.currency_code = hr_api.g_varchar2) then
412 p_rec.currency_code :=
413 ota_evt_shd.g_old_rec.currency_code;
414 End If;
415 If (p_rec.maximum_attendees = hr_api.g_number) then
416 p_rec.maximum_attendees :=
417 ota_evt_shd.g_old_rec.maximum_attendees;
418 End If;
419 If (p_rec.maximum_internal_attendees = hr_api.g_number) then
420 p_rec.maximum_internal_attendees :=
421 ota_evt_shd.g_old_rec.maximum_internal_attendees;
422 End If;
423 If (p_rec.minimum_attendees = hr_api.g_number) then
424 p_rec.minimum_attendees :=
425 ota_evt_shd.g_old_rec.minimum_attendees;
426 End If;
427 If (p_rec.standard_price = hr_api.g_number) then
428 p_rec.standard_price :=
429 ota_evt_shd.g_old_rec.standard_price;
430 End If;
431 If (p_rec.category_code = hr_api.g_varchar2) then
432 p_rec.category_code :=
433 ota_evt_shd.g_old_rec.category_code;
434 End If;
435 If (p_rec.parent_event_id = hr_api.g_number) then
436 p_rec.parent_event_id :=
437 ota_evt_shd.g_old_rec.parent_event_id;
438 End If;
439 If (p_rec.book_independent_flag = hr_api.g_varchar2) then
440 p_rec.book_independent_flag :=
441 ota_evt_shd.g_old_rec.book_independent_flag;
442 End If;
443 If (p_rec.public_event_flag = hr_api.g_varchar2) then
444 p_rec.public_event_flag :=
445 ota_evt_shd.g_old_rec.public_event_flag;
446 End If;
447 If (p_rec.secure_event_flag = hr_api.g_varchar2) then
448 p_rec.secure_event_flag :=
449 ota_evt_shd.g_old_rec.secure_event_flag;
450 End If;
451 If (p_rec.evt_information_category = hr_api.g_varchar2) then
452 p_rec.evt_information_category :=
453 ota_evt_shd.g_old_rec.evt_information_category;
454 End If;
455 If (p_rec.evt_information1 = hr_api.g_varchar2) then
456 p_rec.evt_information1 :=
457 ota_evt_shd.g_old_rec.evt_information1;
458 End If;
459 If (p_rec.evt_information2 = hr_api.g_varchar2) then
460 p_rec.evt_information2 :=
461 ota_evt_shd.g_old_rec.evt_information2;
462 End If;
466 End If;
463 If (p_rec.evt_information3 = hr_api.g_varchar2) then
464 p_rec.evt_information3 :=
465 ota_evt_shd.g_old_rec.evt_information3;
470 End If;
467 If (p_rec.evt_information4 = hr_api.g_varchar2) then
468 p_rec.evt_information4 :=
469 ota_evt_shd.g_old_rec.evt_information4;
471 If (p_rec.evt_information5 = hr_api.g_varchar2) then
472 p_rec.evt_information5 :=
473 ota_evt_shd.g_old_rec.evt_information5;
474 End If;
475 If (p_rec.evt_information6 = hr_api.g_varchar2) then
476 p_rec.evt_information6 :=
477 ota_evt_shd.g_old_rec.evt_information6;
478 End If;
479 If (p_rec.evt_information7 = hr_api.g_varchar2) then
480 p_rec.evt_information7 :=
481 ota_evt_shd.g_old_rec.evt_information7;
482 End If;
483 If (p_rec.evt_information8 = hr_api.g_varchar2) then
484 p_rec.evt_information8 :=
485 ota_evt_shd.g_old_rec.evt_information8;
486 End If;
487 If (p_rec.evt_information9 = hr_api.g_varchar2) then
488 p_rec.evt_information9 :=
489 ota_evt_shd.g_old_rec.evt_information9;
490 End If;
491 If (p_rec.evt_information10 = hr_api.g_varchar2) then
492 p_rec.evt_information10 :=
493 ota_evt_shd.g_old_rec.evt_information10;
494 End If;
495 If (p_rec.evt_information11 = hr_api.g_varchar2) then
496 p_rec.evt_information11 :=
497 ota_evt_shd.g_old_rec.evt_information11;
498 End If;
499 If (p_rec.evt_information12 = hr_api.g_varchar2) then
500 p_rec.evt_information12 :=
501 ota_evt_shd.g_old_rec.evt_information12;
502 End If;
503 If (p_rec.evt_information13 = hr_api.g_varchar2) then
504 p_rec.evt_information13 :=
505 ota_evt_shd.g_old_rec.evt_information13;
506 End If;
507 If (p_rec.evt_information14 = hr_api.g_varchar2) then
508 p_rec.evt_information14 :=
509 ota_evt_shd.g_old_rec.evt_information14;
510 End If;
511 If (p_rec.evt_information15 = hr_api.g_varchar2) then
512 p_rec.evt_information15 :=
513 ota_evt_shd.g_old_rec.evt_information15;
514 End If;
515 If (p_rec.evt_information16 = hr_api.g_varchar2) then
516 p_rec.evt_information16 :=
517 ota_evt_shd.g_old_rec.evt_information16;
518 End If;
519 If (p_rec.evt_information17 = hr_api.g_varchar2) then
520 p_rec.evt_information17 :=
521 ota_evt_shd.g_old_rec.evt_information17;
522 End If;
523 If (p_rec.evt_information18 = hr_api.g_varchar2) then
524 p_rec.evt_information18 :=
525 ota_evt_shd.g_old_rec.evt_information18;
526 End If;
527 If (p_rec.evt_information19 = hr_api.g_varchar2) then
528 p_rec.evt_information19 :=
529 ota_evt_shd.g_old_rec.evt_information19;
530 End If;
531 If (p_rec.evt_information20 = hr_api.g_varchar2) then
532 p_rec.evt_information20 :=
533 ota_evt_shd.g_old_rec.evt_information20;
534 End If;
535 If (p_rec.project_id = hr_api.g_number) then
536 p_rec.project_id :=
537 ota_evt_shd.g_old_rec.project_id;
538 End If;
539 If (p_rec.owner_id = hr_api.g_number) then
540 p_rec.owner_id :=
541 ota_evt_shd.g_old_rec.owner_id;
542 End If;
543 If (p_rec.line_id = hr_api.g_number) then
544 p_rec.line_id :=
545 ota_evt_shd.g_old_rec.line_id;
546 End If;
547 If (p_rec.org_id = hr_api.g_number) then
548 p_rec.org_id :=
549 ota_evt_shd.g_old_rec.org_id;
550 End If;
551 If (p_rec.training_center_id = hr_api.g_number) then
552 p_rec.training_center_id :=
553 ota_evt_shd.g_old_rec.training_center_id;
554 End If;
555 If (p_rec.location_id = hr_api.g_number) then
556 p_rec.location_id :=
557 ota_evt_shd.g_old_rec.location_id;
558 End If;
559
560 If (p_rec.offering_id = hr_api.g_number) then
561 p_rec.offering_id :=
562 ota_evt_shd.g_old_rec.offering_id;
563 End If;
564
565 If (p_rec.timezone = hr_api.g_varchar2) then
566 p_rec.timezone :=
567 ota_evt_shd.g_old_rec.timezone;
568 End If;
569
570 If (p_rec.parent_offering_id = hr_api.g_number) then
571 p_rec.parent_offering_id :=
572 ota_evt_shd.g_old_rec.parent_offering_id;
573 End If;
574
575 If (p_rec.data_source = hr_api.g_varchar2) then
576 p_rec.data_source :=
577 ota_evt_shd.g_old_rec.data_source;
578 End If;
579
580 If (p_rec.event_availability = hr_api.g_varchar2) then
581 p_rec.event_availability :=
582 ota_evt_shd.g_old_rec.event_availability;
583 End If;
584
585 --
586 -- Return the plsql record structure.
587 --
588 hr_utility.set_location(' Leaving:'||l_proc, 10);
589 Return(p_rec);
590 --
591 End convert_defs;
592 --
593 -- ----------------------------------------------------------------------------
594 -- |---------------------------------< upd >----------------------------------|
595 -- ----------------------------------------------------------------------------
596 Procedure upd
597 (
598 p_rec in out nocopy ota_evt_shd.g_rec_type,
599 p_validate in boolean default false
600 ) is
601 --
602 l_proc varchar2(72) := g_package||'upd';
603 -- VT 05/06/97 #488173
604 temp_var ota_evt_shd.g_rec_type;
605 l_course_start_date_changed boolean;
606 l_course_end_date_changed boolean;
607 l_course_start_time_changed boolean;
608 l_course_end_time_changed boolean;
609 l_location_id_changed boolean;
610 l_training_center_id_changed boolean;
611 l_class_title_changed boolean;
612 --
613 Begin
614 hr_utility.set_location('Entering:'||l_proc, 5);
615 --
619 --
616 -- Determine if the business process is to be validated.
617 --
618 If p_validate then
620 -- Issue the savepoint.
621 --
622 SAVEPOINT upd_ota_evt;
623 End If;
624 --
625 -- We must lock the row which we need to update.
626 --
627 ota_evt_shd.lck
628 (
629 p_rec.event_id,
630 p_rec.object_version_number
631 );
632 --
633 -- 1. During an update system defaults are used to determine if
634 -- arguments have been defaulted or not. We must therefore
635 -- derive the full record structure values to be updated.
636 --
637 -- 2. Call the supporting update validate operations.
638 --
639 -- VT 05/06/97 #488173
640 temp_var := convert_defs(p_rec);
641 ota_evt_bus.update_validate(temp_var);
642 -- added for eBS by asud
643 hr_multi_message.end_validation_set;
644 -- added for eBS by asud
648 --
645 -- ota_evt_bus.update_validate(convert_defs(p_rec));
646 --
647 -- Call the supporting pre-update operation
649 pre_update(p_rec);
650 --
651 -- Update the row.
652 --
653 update_dml(p_rec);
654 --
655
656
657 -- Call the supporting post-update operation
658 --
659 post_update(p_rec);
660 -- added for eBS by asud
661 hr_multi_message.end_validation_set;
662 -- added for eBS by asud
663 --
664 -- If we are validating then raise the Validate_Enabled exception
665 --
666 If p_validate then
667 Raise HR_Api.Validate_Enabled;
668 End If;
669 -- CODING FOR NTF PROCESS
670
671 l_course_start_date_changed
672 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_start_date,
673 temp_var.course_start_date);
674 l_course_end_date_changed
675 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_end_date,
676 temp_var.course_end_date);
677 l_course_start_time_changed := ota_general.value_changed(ota_evt_shd.g_old_rec.course_start_time,
678 temp_var.course_start_time);
679 l_course_end_time_changed := ota_general.value_changed(ota_evt_shd.g_old_rec.course_end_time,
680 temp_var.course_end_time);
681
682 l_location_id_changed
683 := ota_general.value_changed(ota_evt_shd.g_old_rec.location_id,
684 temp_var.location_id);
685
686 l_training_center_id_changed
687 := ota_general.value_changed(ota_evt_shd.g_old_rec.training_center_id,
688 temp_var.training_center_id);
689
690 l_class_title_changed := ota_general.value_changed(ota_evt_shd.g_old_rec.title ,temp_var.title);
691
692
693
694 if (l_course_start_date_changed or
695 l_course_end_date_changed or
696 l_course_start_time_changed or
697 l_course_end_time_changed ) then
698 --send notification to all instructors for class/session rescheduling
699 OTA_INITIALIZATION_WF.initialize_instructor_wf(
700 p_item_type => 'OTWF',
701 p_eventid => p_rec.event_id,
702 p_event_fired => 'CLASS_RESCHEDULE');
703
704 if ota_evt_shd.g_old_rec.parent_event_id is null then
705 --send notification to all learners for class rescheduling
706 OTA_INITIALIZATION_WF.initialize_wf(p_process => 'OTA_CLASS_CANCEL_JSP_PRC',
707 p_item_type => 'OTWF',
708 p_eventid => p_rec.event_id,
709 p_event_fired => 'CLASS_RESCHEDULE');
710 end if;
711
712 elsif l_location_id_changed then
713
714 --send notification to all instructors for class/session location change
715 OTA_INITIALIZATION_WF.initialize_instructor_wf(
716 p_item_type => 'OTWF',
717 p_eventid => p_rec.event_id,
718 p_event_fired => 'LOCATION_CHANGE');
719
720 end if;
721
722 ------------ Start Raise Business Event --------------------
723 if ota_evt_shd.g_old_rec.parent_event_id is null then
724
725 if (l_course_start_date_changed or
726 l_course_end_date_changed or
727 l_course_start_time_changed or
728 l_course_end_time_changed ) then
729 --Raise Business Event to all learners for class rescheduling
730 OTA_INITIALIZATION_WF.RAISE_BUSINESS_EVENT(
731 p_eventid => p_rec.event_id,
732 p_event_fired => 'oracle.apps.ota.api.event_api.update_class_schedule',
733 p_type => 'class_reschedule');
734 ---
735 end if;
736
737 if l_class_title_changed then
738 OTA_INITIALIZATION_WF.RAISE_BUSINESS_EVENT(
739 p_eventid => p_rec.event_id,
740 p_event_fired => 'oracle.apps.ota.api.event_api.update_class_schedule',
741 p_type => 'class_title_change');
745 --- Raise Business Event to all learners for class/session location change & Training center change
742 end if;
743
744 if l_location_id_changed and l_training_center_id_changed then
746 OTA_INITIALIZATION_WF.RAISE_BUSINESS_EVENT(
747 p_eventid => p_rec.event_id,
748 p_event_fired => 'oracle.apps.ota.api.event_api.update_trng_cntr_and_location');
749
750 elsif l_location_id_changed then
751 --- Raise Business Event to all learners for class/session Location change
752 OTA_INITIALIZATION_WF.RAISE_BUSINESS_EVENT(
753 p_eventid => p_rec.event_id,
754 p_event_fired => 'oracle.apps.ota.api.event_api.update_location');
755 ---
756
757 elsif l_training_center_id_changed then
758 --- Raise Business Event to all learners for class/session Training center change
759 OTA_INITIALIZATION_WF.RAISE_BUSINESS_EVENT(
760 p_eventid => p_rec.event_id,
761 p_event_fired => 'oracle.apps.ota.api.event_api.update_training_center');
762 ---
763 end if;
764 end if;
765
766 ------------ End Raise Business Event --------------------
767
768 if (l_course_start_date_changed and trunc(temp_var.course_start_date) <= trunc(sysdate)
769 and ota_evt_shd.g_old_rec.course_start_date > trunc(sysdate)
770 and ota_evt_shd.g_old_rec.parent_event_id is null) then
771 -- send notification to all waitlisted learners of class begining
772 if (ota_utility.is_con_prog_periodic('OTEVTNTF')) then
773 ota_initialization_wf.Initialize_auto_wf(p_process => 'OTA_ENROLL_STATUS_CHNG_JSP_PRC',
774 p_item_type => 'OTWF',
775 p_event_fired => 'CLASS_START',
776 p_event_id => p_rec.event_id);
777
778 end if;
779 end if;
780
781 --
782 hr_utility.set_location(' Leaving:'||l_proc, 10);
783 Exception
784 When HR_Api.Validate_Enabled Then
785 --
786 -- As the Validate_Enabled exception has been raised
787 -- we must rollback to the savepoint
788 --
789 ROLLBACK TO upd_ota_evt;
790 End upd;
791 --
792 -- ----------------------------------------------------------------------------
793 -- |---------------------------------< upd >----------------------------------|
794 -- ----------------------------------------------------------------------------
795 Procedure upd
796 (
797 p_event_id in number,
798 p_vendor_id in number default hr_api.g_number,
799 p_activity_version_id in number default hr_api.g_number,
800 p_business_group_id in number default hr_api.g_number,
801 p_organization_id in number default hr_api.g_number,
802 p_event_type in varchar2 default hr_api.g_varchar2,
803 p_object_version_number in out nocopy number,
807 p_budget_currency_code in varchar2 default hr_api.g_varchar2,
804 p_title in varchar2 default hr_api.g_varchar2,
805 p_budget_cost in number default hr_api.g_number,
806 p_actual_cost in number default hr_api.g_number,
808 p_centre in varchar2 default hr_api.g_varchar2,
809 p_comments in varchar2 default hr_api.g_varchar2,
810 p_course_end_date in date default hr_api.g_date,
811 p_course_end_time in varchar2 default hr_api.g_varchar2,
812 p_course_start_date in date default hr_api.g_date,
813 p_course_start_time in varchar2 default hr_api.g_varchar2,
814 p_duration in number default hr_api.g_number,
815 p_duration_units in varchar2 default hr_api.g_varchar2,
816 p_enrolment_end_date in date default hr_api.g_date,
817 p_enrolment_start_date in date default hr_api.g_date,
818 p_language_id in number default hr_api.g_number,
819 p_user_status in varchar2 default hr_api.g_varchar2,
820 p_development_event_type in varchar2 default hr_api.g_varchar2,
821 p_event_status in varchar2 default hr_api.g_varchar2,
822 p_price_basis in varchar2 default hr_api.g_varchar2,
823 p_currency_code in varchar2 default hr_api.g_varchar2,
824 p_maximum_attendees in number default hr_api.g_number,
825 p_maximum_internal_attendees in number default hr_api.g_number,
826 p_minimum_attendees in number default hr_api.g_number,
830 p_book_independent_flag in varchar2 default hr_api.g_varchar2,
827 p_standard_price in number default hr_api.g_number,
828 p_category_code in varchar2 default hr_api.g_varchar2,
829 p_parent_event_id in number default hr_api.g_number,
831 p_public_event_flag in varchar2 default hr_api.g_varchar2,
832 p_secure_event_flag in varchar2 default hr_api.g_varchar2,
833 p_evt_information_category in varchar2 default hr_api.g_varchar2,
834 p_evt_information1 in varchar2 default hr_api.g_varchar2,
835 p_evt_information2 in varchar2 default hr_api.g_varchar2,
836 p_evt_information3 in varchar2 default hr_api.g_varchar2,
837 p_evt_information4 in varchar2 default hr_api.g_varchar2,
838 p_evt_information5 in varchar2 default hr_api.g_varchar2,
839 p_evt_information6 in varchar2 default hr_api.g_varchar2,
840 p_evt_information7 in varchar2 default hr_api.g_varchar2,
841 p_evt_information8 in varchar2 default hr_api.g_varchar2,
842 p_evt_information9 in varchar2 default hr_api.g_varchar2,
843 p_evt_information10 in varchar2 default hr_api.g_varchar2,
844 p_evt_information11 in varchar2 default hr_api.g_varchar2,
845 p_evt_information12 in varchar2 default hr_api.g_varchar2,
846 p_evt_information13 in varchar2 default hr_api.g_varchar2,
847 p_evt_information14 in varchar2 default hr_api.g_varchar2,
848 p_evt_information15 in varchar2 default hr_api.g_varchar2,
849 p_evt_information16 in varchar2 default hr_api.g_varchar2,
850 p_evt_information17 in varchar2 default hr_api.g_varchar2,
851 p_evt_information18 in varchar2 default hr_api.g_varchar2,
852 p_evt_information19 in varchar2 default hr_api.g_varchar2,
853 p_evt_information20 in varchar2 default hr_api.g_varchar2,
854 p_project_id in number default hr_api.g_number,
855 p_owner_id in number default hr_api.g_number,
856 p_line_id in number default hr_api.g_number,
857 p_org_id in number default hr_api.g_number,
858 p_training_center_id in number default hr_api.g_number,
859 p_location_id in number default hr_api.g_number,
860 p_offering_id in number default hr_api.g_number,
861 p_timezone in varchar2 default hr_api.g_varchar2,
862 p_parent_offering_id in number default hr_api.g_number,
863 p_data_source in varchar2 default hr_api.g_varchar2,
864 -- Bug#2200078 Corrected default value for offering_id and timezone
865 -- p_offering_id in number default null,
866 -- p_timezone in varchar2 default null,
867 p_validate in boolean default false,
868 p_event_availability in varchar2 default hr_api.g_varchar2
869 ) is
870 --
871 l_rec ota_evt_shd.g_rec_type;
872 l_proc varchar2(72) := g_package||'upd';
873 --
874 Begin
875 hr_utility.set_location('Entering:'||l_proc, 5);
876 --
877 -- Call conversion function to turn arguments into the
878 -- l_rec structure.
879 --
880 l_rec :=
881 ota_evt_shd.convert_args
882 (
883 p_event_id,
884 p_vendor_id,
885 p_activity_version_id,
886 p_business_group_id,
887 p_organization_id,
888 p_event_type,
889 p_object_version_number,
890 p_title,
891 p_budget_cost,
892 p_actual_cost,
893 p_budget_currency_code,
894 p_centre,
895 p_comments,
896 p_course_end_date,
897 p_course_end_time,
898 p_course_start_date,
899 p_course_start_time,
900 p_duration,
901 p_duration_units,
902 p_enrolment_end_date,
903 p_enrolment_start_date,
904 p_language_id,
905 p_user_status,
906 p_development_event_type,
907 p_event_status,
908 p_price_basis,
909 p_currency_code,
910 p_maximum_attendees,
911 p_maximum_internal_attendees,
912 p_minimum_attendees,
913 p_standard_price,
914 p_category_code,
915 p_parent_event_id,
916 p_book_independent_flag,
917 p_public_event_flag,
918 p_secure_event_flag,
919 p_evt_information_category,
920 p_evt_information1,
921 p_evt_information2,
922 p_evt_information3,
923 p_evt_information4,
924 p_evt_information5,
925 p_evt_information6,
926 p_evt_information7,
927 p_evt_information8,
928 p_evt_information9,
929 p_evt_information10,
930 p_evt_information11,
931 p_evt_information12,
932 p_evt_information13,
933 p_evt_information14,
934 p_evt_information15,
935 p_evt_information16,
936 p_evt_information17,
937 p_evt_information18,
938 p_evt_information19,
939 p_evt_information20,
940 p_project_id,
941 p_owner_id,
942 p_line_id,
943 p_org_id,
944 p_training_center_id,
945 p_location_id,
946 p_offering_id,
947 p_timezone,
948 p_parent_offering_id ,
949 p_data_source,
950 p_event_availability
951 );
952 --
953 -- Having converted the arguments into the
954 -- plsql record structure we call the corresponding record
958 p_object_version_number := l_rec.object_version_number;
955 -- business process.
956 --
957 upd(l_rec, p_validate);
959 --
960 hr_utility.set_location(' Leaving:'||l_proc, 10);
961 End upd;
962 end ota_evt_upd;