1 Package Body per_grs_ins as
2 /* $Header: pegrsrhi.pkb 115.3 2002/12/05 17:40:37 pkakar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_grs_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 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 insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory attributes set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert 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 -- None.
47 --
48 -- Access Status:
49 -- Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml(p_rec in out nocopy per_grs_shd.g_rec_type) is
54 --
55 l_proc varchar2(72) := g_package||'insert_dml';
56 --
57 Begin
58 hr_utility.set_location('Entering:'||l_proc, 5);
59 p_rec.object_version_number := 1; -- Initialise the object version
60 --
61 per_grs_shd.g_api_dml := true; -- Set the api dml status
62 --
63 -- Insert the row into: per_cagr_grade_structures
64 --
65 insert into per_cagr_grade_structures
66 ( cagr_grade_structure_id,
67 collective_agreement_id,
68 object_version_number,
69 id_flex_num,
70 dynamic_insert_allowed,
71 attribute_category,
72 attribute1,
73 attribute2,
74 attribute3,
75 attribute4,
76 attribute5,
77 attribute6,
78 attribute7,
79 attribute8,
80 attribute9,
81 attribute10,
82 attribute11,
83 attribute12,
84 attribute13,
85 attribute14,
86 attribute15,
87 attribute16,
88 attribute17,
89 attribute18,
90 attribute19,
91 attribute20
92 )
93 Values
94 ( p_rec.cagr_grade_structure_id,
95 p_rec.collective_agreement_id,
96 p_rec.object_version_number,
97 p_rec.id_flex_num,
98 p_rec.dynamic_insert_allowed,
99 p_rec.attribute_category,
100 p_rec.attribute1,
101 p_rec.attribute2,
102 p_rec.attribute3,
103 p_rec.attribute4,
104 p_rec.attribute5,
105 p_rec.attribute6,
106 p_rec.attribute7,
107 p_rec.attribute8,
108 p_rec.attribute9,
109 p_rec.attribute10,
110 p_rec.attribute11,
111 p_rec.attribute12,
112 p_rec.attribute13,
113 p_rec.attribute14,
114 p_rec.attribute15,
115 p_rec.attribute16,
116 p_rec.attribute17,
117 p_rec.attribute18,
118 p_rec.attribute19,
119 p_rec.attribute20
120 );
121 --
122 per_grs_shd.g_api_dml := false; -- Unset the api dml status
123 --
124 hr_utility.set_location(' Leaving:'||l_proc, 10);
125 Exception
126 When hr_api.check_integrity_violated Then
127 -- A check constraint has been violated
128 per_grs_shd.g_api_dml := false; -- Unset the api dml status
129 per_grs_shd.constraint_error
130 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
131 When hr_api.parent_integrity_violated Then
132 -- Parent integrity has been violated
133 per_grs_shd.g_api_dml := false; -- Unset the api dml status
134 per_grs_shd.constraint_error
135 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
136 When hr_api.unique_integrity_violated Then
137 -- Unique integrity has been violated
138 per_grs_shd.g_api_dml := false; -- Unset the api dml status
139 per_grs_shd.constraint_error
140 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
141 When Others Then
142 per_grs_shd.g_api_dml := false; -- Unset the api dml status
143 Raise;
144 End insert_dml;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |------------------------------< pre_insert >------------------------------|
148 -- ----------------------------------------------------------------------------
149 -- {Start Of Comments}
150 --
151 -- Description:
152 -- This private procedure contains any processing which is required before
153 -- the insert dml. Presently, if the entity has a corresponding primary
154 -- key which is maintained by an associating sequence, the primary key for
155 -- the entity will be populated with the next sequence value in
156 -- preparation for the insert dml.
157 --
158 -- Prerequisites:
159 -- This is an internal procedure which is called from the ins procedure.
160 --
161 -- In Parameters:
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 pre-processing required before the insert dml is issued should be
173 -- coded within this procedure. As stated above, a good example is the
174 -- generation of a primary key number via a corresponding sequence.
175 -- It is important to note that any 3rd party maintenance should be reviewed
176 -- before placing in this procedure.
177 --
178 -- Access Status:
179 -- Internal Row Handler Use Only.
180 --
181 -- {End Of Comments}
182 -- ----------------------------------------------------------------------------
183 Procedure pre_insert(p_rec in out nocopy per_grs_shd.g_rec_type,
184 p_effective_date in date
185 ) is
186 --
187 l_proc varchar2(72) := g_package||'pre_insert';
188 --
189 Cursor C_Sel1 is select per_cagr_grade_structures_s.nextval from sys.dual;
190 --
191 Begin
192 hr_utility.set_location('Entering:'||l_proc, 5);
193 --
194 --
195 -- Select the next sequence number
196 --
197 Open C_Sel1;
198 Fetch C_Sel1 Into p_rec.cagr_grade_structure_id;
199 Close C_Sel1;
200 --
201 hr_utility.set_location(' Leaving:'||l_proc, 10);
202 End pre_insert;
203 --
204 -- ----------------------------------------------------------------------------
205 -- |-----------------------------< post_insert >------------------------------|
206 -- ----------------------------------------------------------------------------
207 -- {Start Of Comments}
208 --
209 -- Description:
210 -- This private procedure contains any processing which is required after the
211 -- insert dml.
212 --
213 -- Prerequisites:
214 -- This is an internal procedure which is called from the ins procedure.
215 --
216 -- In Parameters:
217 -- A Pl/Sql record structre.
218 --
219 -- Post Success:
220 -- Processing continues.
221 --
222 -- Post Failure:
223 -- If an error has occurred, an error message and exception will be raised
224 -- but not handled.
225 --
226 -- Developer Implementation Notes:
227 -- Any post-processing required after the insert dml is issued should be
228 -- coded within this procedure. It is important to note that any 3rd party
229 -- maintenance should be reviewed before placing in this procedure.
230 --
231 -- Access Status:
232 -- Internal Row Handler Use Only.
233 --
234 -- {End Of Comments}
235 -- ----------------------------------------------------------------------------
236 Procedure post_insert(p_rec in per_grs_shd.g_rec_type,
237 p_effective_date in date) is
238 --
239 l_proc varchar2(72) := g_package||'post_insert';
240 --
241 Begin
242 hr_utility.set_location('Entering:'||l_proc, 5);
243 --
244 --
245 -- Start of API User Hook for post_insert.
246 --
247 begin
248 --
249 per_grs_rki.after_insert
250 (
251 p_cagr_grade_structure_id =>p_rec.cagr_grade_structure_id
252 ,p_collective_agreement_id =>p_rec.collective_agreement_id
253 ,p_object_version_number =>p_rec.object_version_number
254 ,p_id_flex_num =>p_rec.id_flex_num
255 ,p_dynamic_insert_allowed =>p_rec.dynamic_insert_allowed
256 ,p_attribute_category =>p_rec.attribute_category
257 ,p_attribute1 =>p_rec.attribute1
258 ,p_attribute2 =>p_rec.attribute2
259 ,p_attribute3 =>p_rec.attribute3
260 ,p_attribute4 =>p_rec.attribute4
261 ,p_attribute5 =>p_rec.attribute5
262 ,p_attribute6 =>p_rec.attribute6
263 ,p_attribute7 =>p_rec.attribute7
264 ,p_attribute8 =>p_rec.attribute8
265 ,p_attribute9 =>p_rec.attribute9
266 ,p_attribute10 =>p_rec.attribute10
267 ,p_attribute11 =>p_rec.attribute11
268 ,p_attribute12 =>p_rec.attribute12
269 ,p_attribute13 =>p_rec.attribute13
270 ,p_attribute14 =>p_rec.attribute14
271 ,p_attribute15 =>p_rec.attribute15
272 ,p_attribute16 =>p_rec.attribute16
273 ,p_attribute17 =>p_rec.attribute17
274 ,p_attribute18 =>p_rec.attribute18
275 ,p_attribute19 =>p_rec.attribute19
276 ,p_attribute20 =>p_rec.attribute20
277 ,p_effective_date =>p_effective_date
278 );
279 --
280 exception
281 --
282 when hr_api.cannot_find_prog_unit then
283 --
284 hr_api.cannot_find_prog_unit_error
285 (p_module_name => 'per_cagr_grade_structures'
286 ,p_hook_type => 'AI');
287 --
288 end;
289 --
290 -- End of API User Hook for post_insert.
291 --
292 --
293 hr_utility.set_location(' Leaving:'||l_proc, 10);
294 End post_insert;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |---------------------------------< ins >----------------------------------|
298 -- ----------------------------------------------------------------------------
299 Procedure ins
300 (p_effective_date in date,
301 p_rec in out nocopy per_grs_shd.g_rec_type
302 ) is
303 --
304 l_proc varchar2(72) := g_package||'ins';
305 --
306 Begin
307 hr_utility.set_location('Entering:'||l_proc, 5);
308 --
309 -- Call the supporting insert validate operations
310 --
311 per_grs_bus.insert_validate(p_effective_date => p_effective_date,
312 p_rec => p_rec);
313 --
314 -- Call the supporting pre-insert operation
315 --
316 pre_insert(p_effective_date => p_effective_date,
317 p_rec => p_rec);
318 --
319 -- Insert the row
320 --
321 insert_dml(p_rec);
322 --
323 -- Call the supporting post-insert operation
324 --
325 post_insert(p_effective_date => p_effective_date,
326 p_rec => p_rec);
327 --
328 hr_utility.set_location(' Leaving:'||l_proc, 10);
329 --
330 end ins;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |---------------------------------< ins >----------------------------------|
334 -- ----------------------------------------------------------------------------
335 Procedure ins
336 (
337 p_cagr_grade_structure_id out nocopy number,
338 p_collective_agreement_id in number,
339 p_object_version_number out nocopy number,
340 p_id_flex_num in number,
341 p_dynamic_insert_allowed in varchar2 default null,
342 p_attribute_category in varchar2 default null,
343 p_attribute1 in varchar2 default null,
344 p_attribute2 in varchar2 default null,
345 p_attribute3 in varchar2 default null,
346 p_attribute4 in varchar2 default null,
347 p_attribute5 in varchar2 default null,
348 p_attribute6 in varchar2 default null,
349 p_attribute7 in varchar2 default null,
350 p_attribute8 in varchar2 default null,
351 p_attribute9 in varchar2 default null,
352 p_attribute10 in varchar2 default null,
353 p_attribute11 in varchar2 default null,
354 p_attribute12 in varchar2 default null,
355 p_attribute13 in varchar2 default null,
356 p_attribute14 in varchar2 default null,
357 p_attribute15 in varchar2 default null,
358 p_attribute16 in varchar2 default null,
359 p_attribute17 in varchar2 default null,
360 p_attribute18 in varchar2 default null,
361 p_attribute19 in varchar2 default null,
362 p_attribute20 in varchar2 default null,
363 p_effective_date in date
364 ) is
365 --
366 l_rec per_grs_shd.g_rec_type;
367 l_proc varchar2(72) := g_package||'ins';
368 --
369 Begin
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 -- Call conversion function to turn arguments into the
373 -- p_rec structure.
374 --
375 l_rec :=
376 per_grs_shd.convert_args
377 (
378 null,
379 p_collective_agreement_id,
380 null,
381 p_id_flex_num,
382 p_dynamic_insert_allowed,
383 p_attribute_category,
384 p_attribute1,
385 p_attribute2,
386 p_attribute3,
387 p_attribute4,
388 p_attribute5,
389 p_attribute6,
390 p_attribute7,
391 p_attribute8,
392 p_attribute9,
393 p_attribute10,
394 p_attribute11,
395 p_attribute12,
396 p_attribute13,
397 p_attribute14,
398 p_attribute15,
399 p_attribute16,
400 p_attribute17,
401 p_attribute18,
402 p_attribute19,
403 p_attribute20
404 );
405 --
406 -- Having converted the arguments into the per_grs_rec
407 -- plsql record structure we call the corresponding record business process.
408 --
409 ins(p_effective_date => p_effective_date,
410 p_rec => l_rec);
411 --
412 -- As the primary key argument(s)
413 -- are specified as an OUT's we must set these values.
414 --
415 p_cagr_grade_structure_id := l_rec.cagr_grade_structure_id;
416 p_object_version_number := l_rec.object_version_number;
417 --
418 hr_utility.set_location(' Leaving:'||l_proc, 10);
419 End ins;
420 --
421 end per_grs_ins;