1 Package Body ota_ftu_ins as
2 /* $Header: otfturhi.pkb 120.1 2005/09/21 02:31 aabalakr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ftu_ins.'; -- Global package name
9 --
10 -- The following global variables are only to be used by
11 -- the set_base_key_value and pre_insert procedures.
12 --
13 g_forum_thread_id_i number default null;
14 g_forum_id_i number default null;
15 g_person_id_i number default null;
16 g_contact_id_i number default null;
17 --
18 -- ----------------------------------------------------------------------------
19 -- |------------------------< set_base_key_value >----------------------------|
20 -- ----------------------------------------------------------------------------
21 procedure set_base_key_value
22 (p_forum_thread_id in number
23 ,p_forum_id in number
24 ,p_person_id in number
25 ,p_contact_id in number) is
26 --
27 l_proc varchar2(72) := g_package||'set_base_key_value';
28 --
29 Begin
30 hr_utility.set_location('Entering:'||l_proc, 10);
31 --
32 ota_ftu_ins.g_forum_thread_id_i := p_forum_thread_id;
33 ota_ftu_ins.g_forum_id_i := p_forum_id;
34 ota_ftu_ins.g_person_id_i := p_person_id;
35 ota_ftu_ins.g_contact_id_i := p_contact_id;
36 --
37 hr_utility.set_location(' Leaving:'||l_proc, 20);
38 End set_base_key_value;
39 --
40 --
41 -- ----------------------------------------------------------------------------
42 -- |------------------------------< insert_dml >------------------------------|
43 -- ----------------------------------------------------------------------------
44 -- {Start Of Comments}
45 --
46 -- Description:
47 -- This procedure controls the actual dml insert logic. The processing of
48 -- this procedure are as follows:
49 -- 1) Initialise the object_version_number to 1 if the object_version_number
50 -- is defined as an attribute for this entity.
51 -- 2) To set and unset the g_api_dml status as required (as we are about to
52 -- perform dml).
53 -- 3) To insert the row into the schema.
54 -- 4) To trap any constraint violations that may have occurred.
55 -- 5) To raise any other errors.
56 --
57 -- Prerequisites:
58 -- This is an internal private procedure which must be called from the ins
59 -- procedure and must have all mandatory attributes set (except the
60 -- object_version_number which is initialised within this procedure).
61 --
62 -- In Parameters:
63 -- A Pl/Sql record structre.
64 --
65 -- Post Success:
66 -- The specified row will be inserted into the schema.
67 --
68 -- Post Failure:
69 -- On the insert dml failure it is important to note that we always reset the
70 -- g_api_dml status to false.
71 -- If a check, unique or parent integrity constraint violation is raised the
72 -- constraint_error procedure will be called.
73 -- If any other error is reported, the error will be raised after the
74 -- g_api_dml status is reset.
75 --
76 -- Developer Implementation Notes:
77 -- None.
78 --
79 -- Access Status:
80 -- Internal Row Handler Use Only.
81 --
82 -- {End Of Comments}
83 -- ----------------------------------------------------------------------------
84 Procedure insert_dml
85 (p_rec in out nocopy ota_ftu_shd.g_rec_type
86 ) is
87 --
88 l_proc varchar2(72) := g_package||'insert_dml';
89 --
90 Begin
91 hr_utility.set_location('Entering:'||l_proc, 5);
92 p_rec.object_version_number := 1; -- Initialise the object version
93 --
94 --
95 --
96 -- Insert the row into: ota_pvt_frm_thread_users
97 --
98 insert into ota_pvt_frm_thread_users
99 (forum_thread_id
100 ,forum_id
101 ,business_group_id
102 ,author_person_id
103 ,author_contact_id
104 ,person_id
105 ,contact_id
106 ,object_version_number
107 )
108 Values
109 (p_rec.forum_thread_id
110 ,p_rec.forum_id
111 ,p_rec.business_group_id
112 ,p_rec.author_person_id
113 ,p_rec.author_contact_id
114 ,p_rec.person_id
115 ,p_rec.contact_id
116 ,p_rec.object_version_number
117 );
118 --
119 --
120 --
121 hr_utility.set_location(' Leaving:'||l_proc, 10);
122 Exception
123 When hr_api.check_integrity_violated Then
124 -- A check constraint has been violated
125 --
126 ota_ftu_shd.constraint_error
127 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
128 When hr_api.parent_integrity_violated Then
129 -- Parent integrity has been violated
130 --
131 ota_ftu_shd.constraint_error
132 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
133 When hr_api.unique_integrity_violated Then
134 -- Unique integrity has been violated
135 --
136 ota_ftu_shd.constraint_error
137 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
138 When Others Then
139 --
140 Raise;
141 End insert_dml;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |------------------------------< pre_insert >------------------------------|
145 -- ----------------------------------------------------------------------------
146 -- {Start Of Comments}
147 --
148 -- Description:
149 -- This private procedure contains any processing which is required before
150 -- the insert dml. Presently, if the entity has a corresponding primary
151 -- key which is maintained by an associating sequence, the primary key for
152 -- the entity will be populated with the next sequence value in
153 -- preparation for the insert dml.
154 --
155 -- Prerequisites:
156 -- This is an internal procedure which is called from the ins procedure.
157 --
158 -- In Parameters:
159 -- A Pl/Sql record structure.
160 --
161 -- Post Success:
162 -- Processing continues.
163 --
164 -- Post Failure:
165 -- If an error has occurred, an error message and exception will be raised
166 -- but not handled.
167 --
168 -- Developer Implementation Notes:
169 -- Any pre-processing required before the insert dml is issued should be
170 -- coded within this procedure. As stated above, a good example is the
171 -- generation of a primary key number via a corresponding sequence.
172 -- It is important to note that any 3rd party maintenance should be reviewed
173 -- before placing in this procedure.
174 --
175 -- Access Status:
176 -- Internal Row Handler Use Only.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure pre_insert
181 (p_rec in out nocopy ota_ftu_shd.g_rec_type
182 ) is
183 --
184 /*
185 Cursor C_Sel1 is select ota_pvt_frm_thread_users_s.nextval from sys.dual;
186 --
187 Cursor C_Sel2 is
188 Select null
189 from ota_pvt_frm_thread_users
190 where forum_thread_id =
191 ota_ftu_ins.g_forum_thread_id_i
192 or forum_id =
193 ota_ftu_ins.g_forum_id_i
194 or person_id =
195 ota_ftu_ins.g_person_id_i
196 or contact_id =
197 ota_ftu_ins.g_contact_id_i;
198 */
199 --
200 l_proc varchar2(72) := g_package||'pre_insert';
201 l_exists varchar2(1);
202 --
203 Begin
204 hr_utility.set_location('Entering:'||l_proc, 5);
205 --
206 /*
207 If (ota_ftu_ins.g_forum_thread_id_i is not null or
208 ota_ftu_ins.g_forum_id_i is not null or
209 ota_ftu_ins.g_person_id_i is not null or
210 ota_ftu_ins.g_contact_id_i is not null) Then
211 --
212 -- Verify registered primary key values not already in use
213 --
214 Open C_Sel2;
215 Fetch C_Sel2 into l_exists;
216 If C_Sel2%found Then
217 Close C_Sel2;
218 --
219 -- The primary key values are already in use.
220 --
221 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
222 fnd_message.set_token('TABLE_NAME','ota_pvt_frm_thread_users');
223 fnd_message.raise_error;
224 End If;
225 Close C_Sel2;
226 --
227 -- Use registered key values and clear globals
228 --
229 p_rec.forum_thread_id :=
230 ota_ftu_ins.g_forum_thread_id_i;
231 ota_ftu_ins.g_forum_thread_id_i := null;
232 p_rec.forum_id :=
233 ota_ftu_ins.g_forum_id_i;
234 ota_ftu_ins.g_forum_id_i := null;
235 p_rec.person_id :=
236 ota_ftu_ins.g_person_id_i;
237 ota_ftu_ins.g_person_id_i := null;
238 p_rec.contact_id :=
239 ota_ftu_ins.g_contact_id_i;
240 ota_ftu_ins.g_contact_id_i := null;
241 Else
242 --
243 -- No registerd key values, so select the next sequence number
244 --
245 --
246 -- Select the next sequence number
247 --
248 Open C_Sel1;
249 Fetch C_Sel1 Into p_rec.contact_id;
250 Close C_Sel1;
251 End If;
252 */
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 10);
255 End pre_insert;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |-----------------------------< post_insert >------------------------------|
259 -- ----------------------------------------------------------------------------
260 -- {Start Of Comments}
261 --
262 -- Description:
263 -- This private procedure contains any processing which is required after
264 -- the insert dml.
265 --
266 -- Prerequisites:
267 -- This is an internal procedure which is called from the ins procedure.
268 --
269 -- In Parameters:
270 -- A Pl/Sql record structre.
271 --
272 -- Post Success:
273 -- Processing continues.
274 --
275 -- Post Failure:
276 -- If an error has occurred, an error message and exception will be raised
277 -- but not handled.
278 --
279 -- Developer Implementation Notes:
280 -- Any post-processing required after the insert dml is issued should be
281 -- coded within this procedure. It is important to note that any 3rd party
282 -- maintenance should be reviewed before placing in this procedure.
283 --
284 -- Access Status:
285 -- Internal Row Handler Use Only.
286 --
287 -- {End Of Comments}
288 -- ----------------------------------------------------------------------------
289 Procedure post_insert
290 (p_effective_date in date
291 ,p_rec in ota_ftu_shd.g_rec_type
292 ) is
293 --
294 l_proc varchar2(72) := g_package||'post_insert';
295 --
296 Begin
297 hr_utility.set_location('Entering:'||l_proc, 5);
298 begin
299 --
300 ota_ftu_rki.after_insert
301 (p_effective_date => p_effective_date
302 ,p_forum_thread_id
303 => p_rec.forum_thread_id
304 ,p_forum_id
305 => p_rec.forum_id
306 ,p_business_group_id
307 => p_rec.business_group_id
308 ,p_author_person_id
309 => p_rec.author_person_id
310 ,p_author_contact_id
311 => p_rec.author_contact_id
312 ,p_person_id
313 => p_rec.person_id
314 ,p_contact_id
315 => p_rec.contact_id
316 ,p_object_version_number
317 => p_rec.object_version_number
318 );
319 --
320 exception
321 --
322 when hr_api.cannot_find_prog_unit then
323 --
324 hr_api.cannot_find_prog_unit_error
325 (p_module_name => 'OTA_PVT_FRM_THREAD_USERS'
326 ,p_hook_type => 'AI');
327 --
328 end;
329 --
330 hr_utility.set_location(' Leaving:'||l_proc, 10);
331 End post_insert;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |---------------------------------< ins >----------------------------------|
335 -- ----------------------------------------------------------------------------
336 Procedure ins
337 (p_effective_date in date
338 ,p_rec in out nocopy ota_ftu_shd.g_rec_type
339 ) is
340 --
341 l_proc varchar2(72) := g_package||'ins';
342 l_forum_id ota_pvt_frm_thread_users.forum_id%TYPE;
343 l_forum_thread_id ota_pvt_frm_thread_users.forum_thread_id%TYPE;
344 l_person_id ota_pvt_frm_thread_users.person_id%TYPE;
345 l_contact_id ota_pvt_frm_thread_users.contact_id%TYPE;
346
347 --
348 Begin
349 hr_utility.set_location('Entering:'||l_proc, 5);
350 --
351 l_forum_id := p_rec.forum_id;
352 l_forum_thread_id := p_rec.forum_thread_id;
353 l_person_id := p_rec.person_id;
354 l_contact_id := p_rec.contact_id;
355
356 /*p_rec.forum_id := null;
357 p_rec.forum_thread_id := null;
358 p_rec.person_id := null;
359 p_rec.contact_id := null;*/
360
361 ota_ftu_ins.set_base_key_value( p_forum_thread_id => l_forum_thread_id
362 ,p_forum_id => l_forum_id
363 ,p_person_id => l_person_id
364 ,p_contact_id => l_contact_id
365 );
366
367 -- Call the supporting insert validate operations
368 --
369 ota_ftu_bus.insert_validate
370 (p_effective_date
371 ,p_rec
372 ,l_forum_thread_id
373 ,l_forum_id
374 ,l_person_id
375 ,l_contact_id
376 );
377 --
378 -- Call to raise any errors on multi-message list
379 hr_multi_message.end_validation_set;
380 --
381 -- Call the supporting pre-insert operation
382 --
383 ota_ftu_ins.pre_insert(p_rec);
384 --
385 -- Insert the row
386 --
387 ota_ftu_ins.insert_dml(p_rec);
388 --
389 -- Call the supporting post-insert operation
390 --
391 ota_ftu_ins.post_insert
392 (p_effective_date
393 ,p_rec
394 );
395 --
396 -- Call to raise any errors on multi-message list
397 hr_multi_message.end_validation_set;
398 --
399 hr_utility.set_location('Leaving:'||l_proc, 20);
400 end ins;
401 --
402 -- ----------------------------------------------------------------------------
403 -- |---------------------------------< ins >----------------------------------|
404 -- ----------------------------------------------------------------------------
405 Procedure ins
406 (p_effective_date in date
407 ,p_forum_thread_id in number
408 ,p_forum_id in number
409 ,p_person_id in number
410 ,p_contact_id in number
411 ,p_business_group_id in number
412 ,p_author_person_id in number default null
413 ,p_author_contact_id in number default null
414 --,p_forum_thread_id out nocopy number
415 --,p_forum_id out nocopy number
416 --,p_person_id out nocopy number
417 --,p_contact_id out nocopy number
418 ,p_object_version_number out nocopy number
419 ) is
420 --
421 l_rec ota_ftu_shd.g_rec_type;
422 l_proc varchar2(72) := g_package||'ins';
423 --
424 Begin
425 hr_utility.set_location('Entering:'||l_proc, 5);
426 --
427 -- Call conversion function to turn arguments into the
428 -- p_rec structure.
429 --
430 l_rec :=
431 ota_ftu_shd.convert_args
432 (--null
433 --,null
434 p_forum_thread_id
435 ,p_forum_id
436 ,p_business_group_id
437 ,p_author_person_id
438 ,p_author_contact_id
439 --,null
440 --,null
441 ,p_person_id
442 ,p_contact_id
443 ,null
444 );
445 --
446 -- Having converted the arguments into the ota_ftu_rec
447 -- plsql record structure we call the corresponding record business process.
448 --
449 ota_ftu_ins.ins
450 (p_effective_date
451 ,l_rec
452 );
453 --
454 -- As the primary key argument(s)
455 -- are specified as an OUT's we must set these values.
456 --
457
458 --p_forum_thread_id := l_rec.forum_thread_id;
459 --p_forum_id := l_rec.forum_id;
460 --p_person_id := l_rec.person_id;
461 --p_contact_id := l_rec.contact_id;
462 p_object_version_number := l_rec.object_version_number;
463
464 --
465 hr_utility.set_location(' Leaving:'||l_proc, 10);
466 End ins;
467 --
468 end ota_ftu_ins;
[Home] [Help]