1 Package Body per_ret_ins as
2 /* $Header: peretrhi.pkb 115.1 2002/12/06 11:29:20 eumenyio noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ret_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
54 (p_rec in out nocopy per_ret_shd.g_rec_type
55 ) is
56 --
57 l_proc varchar2(72) := g_package||'insert_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 p_rec.object_version_number := 1; -- Initialise the object version
62 --
63 --
64 --
65 -- Insert the row into: per_cagr_retained_rights
66 --
67 insert into per_cagr_retained_rights
68 (cagr_retained_right_id
69 ,assignment_id
70 ,cagr_entitlement_item_id
71 ,collective_agreement_id
72 ,cagr_entitlement_id
73 ,category_name
74 ,element_type_id
75 ,input_value_id
76 ,cagr_api_id
77 ,cagr_api_param_id
78 ,cagr_entitlement_line_id
79 ,freeze_flag
80 ,value
81 ,units_of_measure
82 ,start_date
83 ,end_date
84 ,parent_spine_id
85 ,formula_id
86 ,oipl_id
87 ,step_id
88 ,grade_spine_id
89 ,column_type
90 ,column_size
91 ,eligy_prfl_id
92 ,object_version_number
93 ,cagr_entitlement_result_id
94 ,business_group_id
95 ,flex_value_set_id
96 )
97 Values
98 (p_rec.cagr_retained_right_id
99 ,p_rec.assignment_id
100 ,p_rec.cagr_entitlement_item_id
101 ,p_rec.collective_agreement_id
102 ,p_rec.cagr_entitlement_id
103 ,p_rec.category_name
104 ,p_rec.element_type_id
105 ,p_rec.input_value_id
106 ,p_rec.cagr_api_id
107 ,p_rec.cagr_api_param_id
108 ,p_rec.cagr_entitlement_line_id
109 ,p_rec.freeze_flag
110 ,p_rec.value
111 ,p_rec.units_of_measure
112 ,p_rec.start_date
113 ,p_rec.end_date
114 ,p_rec.parent_spine_id
115 ,p_rec.formula_id
116 ,p_rec.oipl_id
117 ,p_rec.step_id
118 ,p_rec.grade_spine_id
119 ,p_rec.column_type
120 ,p_rec.column_size
121 ,p_rec.eligy_prfl_id
122 ,p_rec.object_version_number
123 ,p_rec.cagr_entitlement_result_id
124 ,p_rec.business_group_id
125 ,p_rec.flex_value_set_id
126 );
127 --
128 --
129 --
130 hr_utility.set_location(' Leaving:'||l_proc, 10);
131 Exception
132 When hr_api.check_integrity_violated Then
133 -- A check constraint has been violated
134 --
135 per_ret_shd.constraint_error
136 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
137 When hr_api.parent_integrity_violated Then
138 -- Parent integrity has been violated
139 --
140 per_ret_shd.constraint_error
141 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
142 When hr_api.unique_integrity_violated Then
143 -- Unique integrity has been violated
144 --
145 per_ret_shd.constraint_error
146 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
147 When Others Then
148 --
149 Raise;
150 End insert_dml;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------------------------------< pre_insert >------------------------------|
154 -- ----------------------------------------------------------------------------
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- This private procedure contains any processing which is required before
159 -- the insert dml. Presently, if the entity has a corresponding primary
160 -- key which is maintained by an associating sequence, the primary key for
161 -- the entity will be populated with the next sequence value in
162 -- preparation for the insert dml.
163 --
164 -- Prerequisites:
165 -- This is an internal procedure which is called from the ins procedure.
166 --
167 -- In Parameters:
168 -- A Pl/Sql record structre.
169 --
170 -- Post Success:
171 -- Processing continues.
172 --
173 -- Post Failure:
174 -- If an error has occurred, an error message and exception will be raised
175 -- but not handled.
176 --
177 -- Developer Implementation Notes:
178 -- Any pre-processing required before the insert dml is issued should be
179 -- coded within this procedure. As stated above, a good example is the
180 -- generation of a primary key number via a corresponding sequence.
181 -- It is important to note that any 3rd party maintenance should be reviewed
182 -- before placing in this procedure.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- {End Of Comments}
188 -- ----------------------------------------------------------------------------
189 Procedure pre_insert
190 (p_rec in out nocopy per_ret_shd.g_rec_type
191 ) is
192 --
193 l_proc varchar2(72) := g_package||'pre_insert';
194 --
195 Cursor C_Sel1 is select per_cagr_retained_rights_s.nextval from sys.dual;
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 --
200 --
201 -- Select the next sequence number
202 --
203 Open C_Sel1;
204 Fetch C_Sel1 Into p_rec.cagr_retained_right_id;
205 Close C_Sel1;
206 --
207 hr_utility.set_location(' Leaving:'||l_proc, 10);
208 End pre_insert;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |-----------------------------< post_insert >------------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This private procedure contains any processing which is required after
217 -- the insert dml.
218 --
219 -- Prerequisites:
220 -- This is an internal procedure which is called from the ins procedure.
221 --
222 -- In Parameters:
223 -- A Pl/Sql record structre.
224 --
225 -- Post Success:
226 -- Processing continues.
227 --
228 -- Post Failure:
229 -- If an error has occurred, an error message and exception will be raised
230 -- but not handled.
231 --
232 -- Developer Implementation Notes:
233 -- Any post-processing required after the insert dml is issued should be
234 -- coded within this procedure. It is important to note that any 3rd party
235 -- maintenance should be reviewed before placing in this procedure.
236 --
237 -- Access Status:
238 -- Internal Row Handler Use Only.
239 --
240 -- {End Of Comments}
241 -- ----------------------------------------------------------------------------
242 Procedure post_insert
243 (p_effective_date in date
244 ,p_rec in per_ret_shd.g_rec_type
245 ) is
246 --
247 l_proc varchar2(72) := g_package||'post_insert';
248 --
249 Begin
250 /*
251 hr_utility.set_location('Entering:'||l_proc, 5);
252 begin
253 --
254 per_ret_rki.after_insert
255 (p_effective_date => p_effective_date
256 ,p_cagr_retained_right_id
257 => p_rec.cagr_retained_right_id
258 ,p_assignment_id
259 => p_rec.assignment_id
260 ,p_cagr_entitlement_item_id
261 => p_rec.cagr_entitlement_item_id
262 ,p_collective_agreement_id
263 => p_rec.collective_agreement_id
264 ,p_cagr_entitlement_id
265 => p_rec.cagr_entitlement_id
266 ,p_category_name
267 => p_rec.category_name
268 ,p_element_type_id
269 => p_rec.element_type_id
270 ,p_input_value_id
271 => p_rec.input_value_id
272 ,p_cagr_api_id
273 => p_rec.cagr_api_id
274 ,p_cagr_api_param_id
275 => p_rec.cagr_api_param_id
276 ,p_cagr_entitlement_line_id
277 => p_rec.cagr_entitlement_line_id
278 ,p_freeze_flag
279 => p_rec.freeze_flag
280 ,p_value
281 => p_rec.value
282 ,p_units_of_measure
283 => p_rec.units_of_measure
284 ,p_start_date
285 => p_rec.start_date
286 ,p_end_date
287 => p_rec.end_date
288 ,p_parent_spine_id
289 => p_rec.parent_spine_id
290 ,p_formula_id
291 => p_rec.formula_id
292 ,p_oipl_id
293 => p_rec.oipl_id
294 ,p_step_id
295 => p_rec.step_id
296 ,p_grade_spine_id
297 => p_rec.grade_spine_id
298 ,p_column_type
299 => p_rec.column_type
300 ,p_column_size
301 => p_rec.column_size
302 ,p_eligy_prfl_id
303 => p_rec.eligy_prfl_id
304 ,p_object_version_number
305 => p_rec.object_version_number
306 ,p_cagr_entitlement_result_id
307 => p_rec.cagr_entitlement_result_id
308 ,p_business_group_id
309 => p_rec.business_group_id
310 ,p_flex_value_set_id
311 => p_rec.flex_value_set_id
312 );
313 --
314 exception
315 --
316 when hr_api.cannot_find_prog_unit then
317 --
318 hr_api.cannot_find_prog_unit_error
319 (p_module_name => 'PER_CAGR_RETAINED_RIGHTS'
320 ,p_hook_type => 'AI');
321 --
322 end;
323 */
324 --
325 hr_utility.set_location(' Leaving:'||l_proc, 10);
326 End post_insert;
327 --
328 -- ----------------------------------------------------------------------------
329 -- |---------------------------------< ins >----------------------------------|
330 -- ----------------------------------------------------------------------------
331 Procedure ins
332 (p_effective_date in date
333 ,p_rec in out nocopy per_ret_shd.g_rec_type
334 ) is
335 --
336 l_proc varchar2(72) := g_package||'ins';
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 -- Call the supporting insert validate operations
342 --
343 per_ret_bus.insert_validate
344 (p_effective_date
345 ,p_rec
346 );
347 --
348 -- Call the supporting pre-insert operation
349 --
350 per_ret_ins.pre_insert(p_rec);
351 --
352 -- Insert the row
353 --
354 per_ret_ins.insert_dml(p_rec);
355 --
356 -- Call the supporting post-insert operation
357 --
358 per_ret_ins.post_insert
359 (p_effective_date
360 ,p_rec
361 );
362 --
363 hr_utility.set_location('Leaving:'||l_proc, 20);
364 end ins;
365 --
366 -- ----------------------------------------------------------------------------
367 -- |---------------------------------< ins >----------------------------------|
368 -- ----------------------------------------------------------------------------
369 Procedure ins
370 (p_effective_date in date
371 ,p_assignment_id in number
372 ,p_cagr_entitlement_item_id in number
373 ,p_collective_agreement_id in number
374 ,p_cagr_entitlement_id in number
375 ,p_category_name in varchar2
376 ,p_element_type_id in number default null
377 ,p_input_value_id in number default null
378 ,p_cagr_api_id in number default null
379 ,p_cagr_api_param_id in number default null
380 ,p_cagr_entitlement_line_id in number default null
381 ,p_freeze_flag in varchar2 default null
382 ,p_value in varchar2 default null
383 ,p_units_of_measure in varchar2 default null
384 ,p_start_date in date default null
385 ,p_end_date in date default null
386 ,p_parent_spine_id in number default null
387 ,p_formula_id in number default null
388 ,p_oipl_id in number default null
389 ,p_step_id in number default null
390 ,p_grade_spine_id in number default null
391 ,p_column_type in varchar2 default null
392 ,p_column_size in number default null
393 ,p_eligy_prfl_id in number default null
394 ,p_cagr_entitlement_result_id in number default null
395 ,p_business_group_id in number default null
396 ,p_flex_value_set_id in number default null
397 ,p_cagr_retained_right_id out nocopy number
398 ,p_object_version_number out nocopy number
399 ) is
400 --
401 l_rec per_ret_shd.g_rec_type;
402 l_proc varchar2(72) := g_package||'ins';
403 --
404 Begin
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 -- Call conversion function to turn arguments into the
408 -- p_rec structure.
409 --
410 l_rec :=
411 per_ret_shd.convert_args
412 (null
413 ,p_assignment_id
414 ,p_cagr_entitlement_item_id
415 ,p_collective_agreement_id
416 ,p_cagr_entitlement_id
417 ,p_category_name
418 ,p_element_type_id
419 ,p_input_value_id
420 ,p_cagr_api_id
421 ,p_cagr_api_param_id
422 ,p_cagr_entitlement_line_id
423 ,p_freeze_flag
424 ,p_value
425 ,p_units_of_measure
426 ,p_start_date
427 ,p_end_date
428 ,p_parent_spine_id
429 ,p_formula_id
430 ,p_oipl_id
431 ,p_step_id
432 ,p_grade_spine_id
433 ,p_column_type
434 ,p_column_size
435 ,p_eligy_prfl_id
436 ,null
437 ,p_cagr_entitlement_result_id
438 ,p_business_group_id
439 ,p_flex_value_set_id
440 );
441 --
442 -- Having converted the arguments into the per_ret_rec
443 -- plsql record structure we call the corresponding record business process.
444 --
445 per_ret_ins.ins
446 (p_effective_date
447 ,l_rec
448 );
449 --
450 -- As the primary key argument(s)
451 -- are specified as an OUT's we must set these values.
452 --
453 p_cagr_retained_right_id := l_rec.cagr_retained_right_id;
454 p_object_version_number := l_rec.object_version_number;
455 --
456 hr_utility.set_location(' Leaving:'||l_proc, 10);
457 End ins;
458 --
459 end per_ret_ins;