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