1 Package Body ota_tdb_del as
2 /* $Header: ottdb01t.pkb 120.30 2011/02/07 11:01:05 shwnayak ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tdb_del.'; -- Global package name
9 g_event_id number; -- Use to save event_id before deleting enrollment.
10 --Added for Bug#4106893
11 g_person_id number;
12 g_contact_id number;
13 --
14 -- ----------------------------------------------------------------------------
15 -- |------------------------------< delete_dml >------------------------------|
16 -- ----------------------------------------------------------------------------
17 -- {Start Of Comments}
18 --
19 -- Description:
20 -- This procedure controls the actual dml delete logic. The functions of this
21 -- procedure are as follows:
22 -- 1) To set and unset the g_api_dml status as required (as we are about to
23 -- perform dml).
24 -- 2) To delete the specified row from the schema using the primary key in
25 -- the predicates.
26 -- 3) To trap any constraint violations that may have occurred.
27 -- 4) To raise any other errors.
28 --
29 -- Pre Conditions:
30 -- This is an internal private procedure which must be called from the del
31 -- procedure.
32 --
33 -- In Arguments:
34 -- A Pl/Sql record structre.
35 --
36 -- Post Success:
37 -- The specified row will be delete from the schema.
38 --
39 -- Post Failure:
40 -- On the delete dml failure it is important to note that we always reset the
41 -- g_api_dml status to false.
42 -- If a child integrity constraint violation is raised the
43 -- constraint_error procedure will be called.
44 -- If any other error is reported, the error will be raised after the
45 -- g_api_dml status is reset.
46 --
47 -- Developer Implementation Notes:
48 -- None.
49 --
50 -- Access Status:
51 -- Internal Development Use Only.
52 --
53 -- {End Of Comments}
54 -- ----------------------------------------------------------------------------
55 Procedure delete_dml(p_rec in ota_tdb_shd.g_rec_type) is
56 --
57 l_proc varchar2(72) := g_package||'delete_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 --
62 ota_tdb_shd.g_api_dml := true; -- Set the api dml status
63 --
64 -- Delete the ota_delegate_bookings row.
65 --
66 delete from ota_delegate_bookings
67 where booking_id = p_rec.booking_id;
68 --
69 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
70 --
71 hr_utility.set_location(' Leaving:'||l_proc, 10);
72 --
73 Exception
74 When hr_api.child_integrity_violated then
75 -- Child integrity has been violated
76 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
77 ota_tdb_shd.constraint_error
78 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
79 When Others Then
80 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
81 Raise;
82 End delete_dml;
83 --
84 -- ----------------------------------------------------------------------------
85 -- |------------------------------< pre_delete >------------------------------|
86 -- ----------------------------------------------------------------------------
87 -- {Start Of Comments}
88 --
89 -- Description:
90 -- This private procedure contains any processing which is required before
91 -- the delete dml.
92 --
93 -- Pre Conditions:
94 -- This is an internal procedure which is called from the del procedure.
95 --
96 -- In Arguments:
97 -- A Pl/Sql record structre.
98 --
99 -- Post Success:
100 -- Processing continues.
101 --
102 -- Post Failure:
103 -- If an error has occurred, an error message and exception will be raised
104 -- but not handled.
105 --
106 -- Developer Implementation Notes:
107 -- Any pre-processing required before the delete dml is issued should be
108 -- coded within this procedure. It is important to note that any 3rd party
109 -- maintenance should be reviewed before placing in this procedure.
110 --
111 -- Access Status:
112 -- Internal Development Use Only.
113 --
114 -- {End Of Comments}
115 -- ----------------------------------------------------------------------------
116 Procedure pre_delete(p_rec in ota_tdb_shd.g_rec_type) is
117 --
118 cursor c_get_event_id is
119 select event_id, delegate_person_id, delegate_contact_id
120 from ota_delegate_bookings
121 where booking_id = p_rec.booking_id;
122
123 l_proc varchar2(72) := g_package||'pre_delete';
124 --
125 Begin
126 hr_utility.set_location('Entering:'||l_proc, 5);
127 --
128
129 --
130 -- Get the event id before deleting, in case the event status needs to
131 -- be reset at a later stage.
132 --
133 --Modified for Bug#4106893
134 open c_get_event_id;
135 fetch c_get_event_id into g_event_id, g_person_id, g_contact_id;
136 close c_get_event_id;
137
138 --
139 -- Bug 663791. This code moved from post_delete procedure to here.
140 -- Delete all status history records for the deleted booking
141 --
142 delete from ota_booking_status_histories
143 where booking_id = p_rec.booking_id;
144 --
145
146 hr_utility.set_location(' Leaving:'||l_proc, 10);
147 End pre_delete;
148 --
149 -- ----------------------------------------------------------------------------
150 -- |-----------------------------< post_delete >------------------------------|
151 -- ----------------------------------------------------------------------------
152 -- {Start Of Comments}
153 --
154 -- Description:
155 -- This private procedure contains any processing which is required after the
156 -- delete dml.
157 --
158 -- Pre Conditions:
159 -- This is an internal procedure which is called from the del procedure.
160 --
161 -- In Arguments:
162 -- A Pl/Sql record structre.
163 --
164 -- Post Success:
165 -- Processing continues.
166 --
167 -- Post Failure:
168 -- If an error has occurred, an error message and exception will be raised
169 -- but not handled.
170 --
171 -- Developer Implementation Notes:
172 -- Any post-processing required after the delete dml is issued should be
173 -- coded within this procedure. It is important to note that any 3rd party
174 -- maintenance should be reviewed before placing in this procedure.
175 --
176 -- Access Status:
177 -- Internal Development Use Only.
178 --
179 -- {End Of Comments}
180 -- ----------------------------------------------------------------------------
181 Procedure post_delete(p_rec in ota_tdb_shd.g_rec_type) is
182 --
183 cursor c_get_forum_id is
184 select fns.forum_id,fns.object_version_number
185 from ota_frm_obj_inclusions foi,ota_frm_notif_subscribers fns
186 where foi.object_id = g_event_id
187 and foi.object_Type = 'E'
188 and foi.forum_id = fns.forum_id
189 and (fns.person_id = g_person_id or fns.contact_id = g_contact_id);
190 l_event_rec ota_evt_shd.g_rec_type;
191 l_event_exists boolean;
192 l_proc varchar2(72) := g_package||'post_delete';
193
194 --Added for Bug#4106893
195 l_lp_enrollment_ids varchar2(4000);
196 --for cert members
197 l_cert_prd_enrollment_ids varchar2(4000);
198 v_forum_id number;
199 v_object_version_number number;
200 --
201 Begin
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 --
204
205 ota_evt_shd.get_event_details(g_event_id,
206 l_event_rec,
207 l_event_exists);
208
209 if l_event_exists then
210 --
211 --
212 -- Reset Event Status
213 --
214
215 ota_evt_bus2.reset_event_status(g_event_id
216 ,l_event_rec.object_version_number
217 ,l_event_rec.event_status
218 ,l_event_rec.maximum_attendees);
219 --
220 end if;
221
222 --
223 --Added for Bug#4106893
224 IF g_person_id IS NOT NULL THEN
225 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => g_event_id,
226 p_person_id => g_person_id,
227 p_contact_id => null,
228 p_lp_enrollment_ids => l_lp_enrollment_ids);
229 --if the enrollment is deleted then check any associated cert enrollments that get effected
230 ota_cme_util.update_cme_status(p_event_id => g_event_id,
231 p_person_id => g_person_id,
232 p_contact_id => null,
233 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
234 ELSIF g_contact_id IS NOT NULL THEN
235 ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => g_event_id,
236 p_person_id => null,
237 p_contact_id => g_contact_id,
238 p_lp_enrollment_ids => l_lp_enrollment_ids);
239 --if the enrollment is deleted then check any associated cert enrollments that get effected
240 ota_cme_util.update_cme_status(p_event_id => g_event_id,
241 p_person_id => null,
242 p_contact_id => g_contact_id,
243 p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
244 END IF;
245 --Delete the forum notification record for this class,for this user
246 OPEN c_get_forum_id;
247 FETCH c_get_forum_id into v_forum_id, v_object_version_number;
248
249 LOOP
250 Exit When c_get_forum_id%notfound OR c_get_forum_id%notfound is null;
251
252 ota_fns_del.del
253 (
254 p_forum_id => v_forum_id
255 ,p_person_id => g_person_id
256 ,p_contact_id => g_contact_id
257 ,p_object_version_number => v_object_version_number
258 );
259
260 FETCH c_get_forum_id into v_forum_id, v_object_version_number;
261 End Loop;
262 Close c_get_forum_id;
263
264
265
266 hr_utility.set_location(' Leaving:'||l_proc, 10);
267 End post_delete;
268 --
269 -- ----------------------------------------------------------------------------
270 -- |---------------------------------< del >----------------------------------|
271 -- ----------------------------------------------------------------------------
272 Procedure del
273 (
274 p_rec in ota_tdb_shd.g_rec_type,
275 p_validate in boolean
276 ) is
277 --
278 l_proc varchar2(72) := g_package||'del';
279 --
280 Begin
281 hr_utility.set_location('Entering:'||l_proc, 5);
282 --
283 -- Determine if the business process is to be validated.
284 --
285 If p_validate then
286 --
287 -- Issue the savepoint.
288 --
289 SAVEPOINT del_ota_tdb;
290 End If;
291 --
292 -- We must lock the row which we need to delete.
293 --
294 ota_tdb_shd.lck
295 (
296 p_rec.booking_id,
297 p_rec.object_version_number
298 );
299 --
300 -- Call the supporting delete validate operation
301 --
302 ota_tdb_bus.delete_validate(p_rec);
303 --
304 -- Call the supporting pre-delete operation
305 --
306 pre_delete(p_rec);
307 --
308 -- Delete the row.
309 --
310 delete_dml(p_rec);
311 --
312 -- Call the supporting post-delete operation
313 --
314 post_delete(p_rec);
315 --
316 -- If we are validating then raise the Validate_Enabled exception
317 --
318 If p_validate then
319 Raise HR_Api.Validate_Enabled;
320 End If;
321 --
322 hr_utility.set_location(' Leaving:'||l_proc, 10);
323 Exception
324 When HR_Api.Validate_Enabled Then
325 --
326 -- As the Validate_Enabled exception has been raised
327 -- we must rollback to the savepoint
328 --
329 ROLLBACK TO del_ota_tdb;
330 End del;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |---------------------------------< del >----------------------------------|
334 -- ----------------------------------------------------------------------------
335 Procedure del
336 (
337 p_booking_id in number,
338 p_object_version_number in number,
339 p_validate in boolean
340 ) is
341 --
342 l_rec ota_tdb_shd.g_rec_type;
343 l_proc varchar2(72) := g_package||'del';
344 --
345 Begin
346 hr_utility.set_location('Entering:'||l_proc, 5);
347 --
348 -- As the delete procedure accepts a plsql record structure we do need to
349 -- convert the arguments into the record structure.
350 -- We don't need to call the supplied conversion argument routine as we
351 -- only need a few attributes.
352 --
353 l_rec.booking_id:= p_booking_id;
354 l_rec.object_version_number := p_object_version_number;
355 --
356 -- Having converted the arguments into the ota_tdb_rec
357 -- plsql record structure we must call the corresponding entity
358 -- business process
359 --
360 del(l_rec, p_validate);
361 --
362 hr_utility.set_location(' Leaving:'||l_proc, 10);
363 End del;
364 --
365 end ota_tdb_del;
[Home] [Help]