1 Package Body ota_coi_ins as
2 /* $Header: otcoirhi.pkb 120.3 2005/08/12 02:46 pchandra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_coi_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_chat_id_i number default null;
14 g_object_id_i number default null;
15 g_object_type_i varchar2(30) default null;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------------------------< set_base_key_value >----------------------------|
19 -- ----------------------------------------------------------------------------
20 procedure set_base_key_value
21 (p_chat_id in number
22 ,p_object_id in number
23 ,p_object_type in varchar2) is
24 --
25 l_proc varchar2(72) := g_package||'set_base_key_value';
26 --
27 Begin
28 hr_utility.set_location('Entering:'||l_proc, 10);
29 --
30 ota_coi_ins.g_chat_id_i := p_chat_id;
31 ota_coi_ins.g_object_id_i := p_object_id;
32 ota_coi_ins.g_object_type_i := p_object_type;
33 --
34 hr_utility.set_location(' Leaving:'||l_proc, 20);
35 End set_base_key_value;
36 --
37 --
38 -- ----------------------------------------------------------------------------
39 -- |------------------------------< insert_dml >------------------------------|
40 -- ----------------------------------------------------------------------------
41 -- {Start Of Comments}
42 --
43 -- Description:
44 -- This procedure controls the actual dml insert logic. The processing of
45 -- this procedure are as follows:
46 -- 1) Initialise the object_version_number to 1 if the object_version_number
47 -- is defined as an attribute for this entity.
48 -- 2) To set and unset the g_api_dml status as required (as we are about to
49 -- perform dml).
50 -- 3) To insert the row into the schema.
51 -- 4) To trap any constraint violations that may have occurred.
52 -- 5) To raise any other errors.
53 --
54 -- Prerequisites:
55 -- This is an internal private procedure which must be called from the ins
56 -- procedure and must have all mandatory attributes set (except the
57 -- object_version_number which is initialised within this procedure).
58 --
59 -- In Parameters:
60 -- A Pl/Sql record structre.
61 --
62 -- Post Success:
63 -- The specified row will be inserted into the schema.
64 --
65 -- Post Failure:
66 -- On the insert dml failure it is important to note that we always reset the
67 -- g_api_dml status to false.
68 -- If a check, unique or parent integrity constraint violation is raised the
69 -- constraint_error procedure will be called.
70 -- If any other error is reported, the error will be raised after the
71 -- g_api_dml status is reset.
72 --
73 -- Developer Implementation Notes:
74 -- None.
75 --
76 -- Access Status:
77 -- Internal Row Handler Use Only.
78 --
79 -- {End Of Comments}
80 -- ----------------------------------------------------------------------------
81 Procedure insert_dml
82 (p_rec in out nocopy ota_coi_shd.g_rec_type
83 ) is
84 --
85 l_proc varchar2(72) := g_package||'insert_dml';
86 --
87 Begin
88 hr_utility.set_location('Entering:'||l_proc, 5);
89 p_rec.object_version_number := 1; -- Initialise the object version
90 --
91 --
92 --
93 -- Insert the row into: ota_chat_obj_inclusions
94 --
95 insert into ota_chat_obj_inclusions
96 (chat_id
97 ,object_id
98 ,object_type
99 ,start_date_active
100 ,end_date_active
101 ,primary_flag
102 ,object_version_number
103 )
104 Values
105 (p_rec.chat_id
106 ,p_rec.object_id
107 ,p_rec.object_type
108 ,p_rec.start_date_active
109 ,p_rec.end_date_active
110 ,p_rec.primary_flag
111 ,p_rec.object_version_number
112 );
113 --
114 --
115 --
116 hr_utility.set_location(' Leaving:'||l_proc, 10);
117 Exception
118 When hr_api.check_integrity_violated Then
119 -- A check constraint has been violated
120 --
121 ota_coi_shd.constraint_error
122 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
123 When hr_api.parent_integrity_violated Then
124 -- Parent integrity has been violated
125 --
126 ota_coi_shd.constraint_error
127 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
128 When hr_api.unique_integrity_violated Then
129 -- Unique integrity has been violated
130 --
131 ota_coi_shd.constraint_error
132 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
133 When Others Then
134 --
135 Raise;
136 End insert_dml;
137 --
138 -- ----------------------------------------------------------------------------
139 -- |------------------------------< pre_insert >------------------------------|
140 -- ----------------------------------------------------------------------------
141 -- {Start Of Comments}
142 --
143 -- Description:
144 -- This private procedure contains any processing which is required before
145 -- the insert dml. Presently, if the entity has a corresponding primary
146 -- key which is maintained by an associating sequence, the primary key for
147 -- the entity will be populated with the next sequence value in
148 -- preparation for the insert dml.
149 --
150 -- Prerequisites:
151 -- This is an internal procedure which is called from the ins procedure.
152 --
153 -- In Parameters:
154 -- A Pl/Sql record structure.
155 --
156 -- Post Success:
157 -- Processing continues.
158 --
159 -- Post Failure:
160 -- If an error has occurred, an error message and exception will be raised
161 -- but not handled.
162 --
163 -- Developer Implementation Notes:
164 -- Any pre-processing required before the insert dml is issued should be
165 -- coded within this procedure. As stated above, a good example is the
166 -- generation of a primary key number via a corresponding sequence.
167 -- It is important to note that any 3rd party maintenance should be reviewed
168 -- before placing in this procedure.
169 --
170 -- Access Status:
171 -- Internal Row Handler Use Only.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure pre_insert
176 (p_rec in out nocopy ota_coi_shd.g_rec_type
177 ) is
178 --
179 -- Cursor C_Sel1 is select ota_chat_obj_inclusions_s.nextval from sys.dual;
180 --
181 -- Cursor C_Sel2 is
182 -- Select null
183 -- from ota_chat_obj_inclusions
184 -- where chat_id =
185 -- ota_coi_ins.g_chat_id_i
186 -- or object_id =
187 -- ota_coi_ins.g_object_id_i
188 -- or object_type =
189 -- ota_coi_ins.g_object_type_i;
190 --
191 l_proc varchar2(72) := g_package||'pre_insert';
192 --l_exists varchar2(1);
193 --
194 Begin
195 hr_utility.set_location('Entering:'||l_proc, 5);
196 hr_utility.set_location(' Leaving:'||l_proc, 10);
197 End pre_insert;
198 --
199 -- ----------------------------------------------------------------------------
200 -- |-----------------------------< post_insert >------------------------------|
201 -- ----------------------------------------------------------------------------
202 -- {Start Of Comments}
203 --
204 -- Description:
205 -- This private procedure contains any processing which is required after
206 -- the insert dml.
207 --
208 -- Prerequisites:
209 -- This is an internal procedure which is called from the ins procedure.
210 --
211 -- In Parameters:
212 -- A Pl/Sql record structre.
213 --
214 -- Post Success:
215 -- Processing continues.
216 --
217 -- Post Failure:
218 -- If an error has occurred, an error message and exception will be raised
219 -- but not handled.
220 --
221 -- Developer Implementation Notes:
222 -- Any post-processing required after the insert dml is issued should be
223 -- coded within this procedure. It is important to note that any 3rd party
224 -- maintenance should be reviewed before placing in this procedure.
225 --
226 -- Access Status:
227 -- Internal Row Handler Use Only.
228 --
229 -- {End Of Comments}
230 -- ----------------------------------------------------------------------------
231 Procedure post_insert
232 (p_effective_date in date
233 ,p_rec in ota_coi_shd.g_rec_type
234 ) is
235 --
236 l_proc varchar2(72) := g_package||'post_insert';
237 --
238 Begin
239 hr_utility.set_location('Entering:'||l_proc, 5);
240 begin
241 --
242 ota_coi_rki.after_insert
243 (p_effective_date => p_effective_date
244 ,p_chat_id
245 => p_rec.chat_id
246 ,p_object_id
247 => p_rec.object_id
248 ,p_object_type
249 => p_rec.object_type
250 ,p_start_date_active
251 => p_rec.start_date_active
252 ,p_end_date_active
253 => p_rec.end_date_active
254 ,p_primary_flag
255 => p_rec.primary_flag
256 ,p_object_version_number
257 => p_rec.object_version_number
258 );
259 --
260 exception
261 --
262 when hr_api.cannot_find_prog_unit then
263 --
264 hr_api.cannot_find_prog_unit_error
265 (p_module_name => 'OTA_CHAT_OBJ_INCLUSIONS'
266 ,p_hook_type => 'AI');
267 --
268 end;
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 End post_insert;
272 --
273 -- ----------------------------------------------------------------------------
274 -- |---------------------------------< ins >----------------------------------|
275 -- ----------------------------------------------------------------------------
276 Procedure ins
277 (p_effective_date in date
278 ,p_rec in out nocopy ota_coi_shd.g_rec_type
279 ) is
280 --
281 l_proc varchar2(72) := g_package||'ins';
282 l_chat_id number;
283 l_object_id number;
284 l_object_type varchar2(30);
285 --
286 Begin
287 hr_utility.set_location('Entering:'||l_proc, 5);
288 l_chat_id := p_rec.chat_id;
289 l_object_id := p_rec.object_id;
290 l_object_type := p_rec.object_type;
291
292 -- p_rec.chat_id := null;
293 -- p_rec.object_id := null;
294 -- p_rec.object_type := null;
295
296 ota_coi_ins.set_base_key_value(l_chat_id,l_object_id,l_object_type);
297 --
298 -- Call the supporting insert validate operations
299 --
300 ota_coi_bus.insert_validate
301 (p_effective_date
302 ,l_chat_id
303 ,l_object_id
304 ,l_object_type
305 ,p_rec
306 );
307 --
308 -- Call to raise any errors on multi-message list
309 hr_multi_message.end_validation_set;
310 --
311 -- Call the supporting pre-insert operation
312 --
313 ota_coi_ins.pre_insert(p_rec);
314 --
315 -- Insert the row
316 --
317 ota_coi_ins.insert_dml(p_rec);
318 --
319 -- Call the supporting post-insert operation
320 --
321 ota_coi_ins.post_insert
322 (p_effective_date
323 ,p_rec
324 );
325 --
326 -- Call to raise any errors on multi-message list
327 hr_multi_message.end_validation_set;
328 --
329 hr_utility.set_location('Leaving:'||l_proc, 20);
330 end ins;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |---------------------------------< ins >----------------------------------|
334 -- ----------------------------------------------------------------------------
335 Procedure ins
336 (p_effective_date in date
337 ,p_chat_id in number
338 ,p_object_id in number
339 ,p_object_type in varchar2
340 ,p_start_date_active in date default null
341 ,p_end_date_active in date default null
342 ,p_primary_flag in varchar2 default null
346 l_rec ota_coi_shd.g_rec_type;
343 ,p_object_version_number out nocopy number
344 ) is
345 --
347 l_proc varchar2(72) := g_package||'ins';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- Call conversion function to turn arguments into the
353 -- p_rec structure.
354 --
355 l_rec :=
356 ota_coi_shd.convert_args
357 (p_chat_id
358 ,p_object_id
359 ,p_object_type
360 ,p_start_date_active
361 ,p_end_date_active
362 ,p_primary_flag
363 ,null
364 );
365 --
366 -- Having converted the arguments into the ota_coi_rec
367 -- plsql record structure we call the corresponding record business process.
368 --
369 ota_coi_ins.ins
370 (p_effective_date
371 ,l_rec
372 );
373 --
374 -- As the primary key argument(s)
375 -- are specified as an OUT's we must set these values.
376 --
377 -- p_chat_id := l_rec.chat_id;
378 -- p_object_id := l_rec.object_id;
379 -- p_object_type := l_rec.object_type;
380 p_object_version_number := l_rec.object_version_number;
381 --
382 hr_utility.set_location(' Leaving:'||l_proc, 10);
383 End ins;
384 --
385 end ota_coi_ins;