1 Package Body per_cei_ins as
2 /* $Header: peceirhi.pkb 120.1 2006/10/18 08:58:46 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cei_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_cei_shd.g_rec_type
55 ) is
56 --
57 l_proc varchar2(72) := g_package||'insert_dml';
58 l_created_by per_cagr_entitlement_items.created_by%TYPE;
59 l_creation_date per_cagr_entitlement_items.creation_date%TYPE;
60 l_last_update_date per_cagr_entitlement_items.last_update_date%TYPE;
61 l_last_updated_by per_cagr_entitlement_items.last_updated_by%TYPE;
62 l_last_update_login per_cagr_entitlement_items.last_update_login%TYPE;
63 --
64 Begin
65 --
66 hr_utility.set_location('Entering:'||l_proc, 5);
67 --
68 p_rec.object_version_number := 1; -- Initialise the object version
69 --
70 -- Set the who columns
71 --
72 l_creation_date := sysdate;
73 l_created_by := fnd_global.user_id;
74 l_last_update_date := sysdate;
75 l_last_updated_by := fnd_global.user_id;
76 l_last_update_login := fnd_global.login_id;
77 --
78 -- Insert the row into: per_cagr_entitlement_items
79 --
80 insert into per_cagr_entitlement_items
81 (cagr_entitlement_item_id
82 ,item_name
83 ,element_type_id
84 ,input_value_id
85 ,column_type
86 ,column_size
87 ,legislation_code
88 ,beneficial_rule
89 ,cagr_api_id
90 ,cagr_api_param_id
91 ,business_group_id
92 ,category_name
93 ,uom
94 ,flex_value_set_id
95 ,beneficial_formula_id
96 ,created_by
97 ,creation_date
98 ,last_update_date
99 ,last_updated_by
100 ,last_update_login
101 ,object_version_number
102 ,beneficial_rule_value_set_id
103 ,multiple_entries_allowed_flag
104 ,auto_create_entries_flag -- CEI Enh
105 ,opt_id
106 )
107 Values
108 (p_rec.cagr_entitlement_item_id
109 ,p_rec.item_name
110 ,p_rec.element_type_id
111 ,p_rec.input_value_id
112 ,p_rec.column_type
113 ,p_rec.column_size
114 ,p_rec.legislation_code
115 ,p_rec.beneficial_rule
116 ,p_rec.cagr_api_id
117 ,p_rec.cagr_api_param_id
118 ,p_rec.business_group_id
119 ,p_rec.category_name
120 ,p_rec.uom
121 ,p_rec.flex_value_set_id
122 ,p_rec.beneficial_formula_id
123 ,l_created_by
124 ,l_creation_date
125 ,l_last_update_date
126 ,l_last_updated_by
127 ,l_last_update_login
128 ,p_rec.object_version_number
129 ,p_rec.ben_rule_value_set_id
130 ,p_rec.mult_entries_allowed_flag
131 ,p_rec.auto_create_entries_flag -- CEI Enh
132 ,p_rec.opt_id
133 );
134 --
135 --
136 --
137 hr_utility.set_location(' Leaving:'||l_proc, 10);
138 Exception
139 When hr_api.check_integrity_violated Then
140 -- A check constraint has been violated
141 --
142 per_cei_shd.constraint_error
143 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
144 When hr_api.parent_integrity_violated Then
145 -- Parent integrity has been violated
146 --
147 per_cei_shd.constraint_error
148 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
149 When hr_api.unique_integrity_violated Then
150 -- Unique integrity has been violated
151 --
152 per_cei_shd.constraint_error
153 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
154 When Others Then
155 --
156 Raise;
157 End insert_dml;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |------------------------------< pre_insert >------------------------------|
161 -- ----------------------------------------------------------------------------
162 -- {Start Of Comments}
163 --
164 -- Description:
165 -- This private procedure contains any processing which is required before
166 -- the insert dml. Presently, if the entity has a corresponding primary
167 -- key which is maintained by an associating sequence, the primary key for
168 -- the entity will be populated with the next sequence value in
169 -- preparation for the insert dml.
170 --
171 -- Prerequisites:
172 -- This is an internal procedure which is called from the ins procedure.
173 --
174 -- In Parameters:
175 -- A Pl/Sql record structre.
176 --
177 -- Post Success:
178 -- Processing continues.
179 --
180 -- Post Failure:
181 -- If an error has occurred, an error message and exception will be raised
182 -- but not handled.
183 --
184 -- Developer Implementation Notes:
185 -- Any pre-processing required before the insert dml is issued should be
186 -- coded within this procedure. As stated above, a good example is the
187 -- generation of a primary key number via a corresponding sequence.
188 -- It is important to note that any 3rd party maintenance should be reviewed
189 -- before placing in this procedure.
190 --
191 -- Access Status:
192 -- Internal Row Handler Use Only.
193 --
194 -- {End Of Comments}
195 -- ----------------------------------------------------------------------------
196 Procedure pre_insert
197 (p_rec in out nocopy per_cei_shd.g_rec_type
198 ) is
199 --
200 l_proc varchar2(72) := g_package||'pre_insert';
201 --
202 Cursor C_Sel1 is select per_cagr_entitlement_items_s.nextval from sys.dual;
203 --
204 Begin
205 hr_utility.set_location('Entering:'||l_proc, 5);
206 --
207 --
208 -- Select the next sequence number
209 --
210 Open C_Sel1;
211 Fetch C_Sel1 Into p_rec.cagr_entitlement_item_id;
212 Close C_Sel1;
213 --
214 hr_utility.set_location(' Leaving:'||l_proc, 10);
215 --
216 End pre_insert;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |-----------------------------< post_insert >------------------------------|
220 -- ----------------------------------------------------------------------------
221 -- {Start Of Comments}
222 --
223 -- Description:
224 -- This private procedure contains any processing which is required after the
225 -- insert dml.
226 --
227 -- Prerequisites:
228 -- This is an internal procedure which is called from the ins procedure.
229 --
230 -- In Parameters:
231 -- A Pl/Sql record structre.
232 --
233 -- Post Success:
234 -- Processing continues.
235 --
236 -- Post Failure:
237 -- If an error has occurred, an error message and exception will be raised
238 -- but not handled.
239 --
240 -- Developer Implementation Notes:
241 -- Any post-processing required after the insert dml is issued should be
242 -- coded within this procedure. It is important to note that any 3rd party
243 -- maintenance should be reviewed before placing in this procedure.
244 --
245 -- Access Status:
246 -- Internal Row Handler Use Only.
247 --
248 -- {End Of Comments}
249 -- ----------------------------------------------------------------------------
250 Procedure post_insert
251 (p_effective_date in date
252 ,p_rec in per_cei_shd.g_rec_type
253 ) is
254 --
255 l_proc varchar2(72) := g_package||'post_insert';
256 --
257 Begin
258 --
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 begin
262 --
263 per_cei_rki.after_insert
264 (p_effective_date => p_effective_date
265 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
266 ,p_item_name => p_rec.item_name
267 ,p_element_type_id => p_rec.element_type_id
268 ,p_input_value_id => p_rec.input_value_id
269 ,p_column_type => p_rec.column_type
270 ,p_column_size => p_rec.column_size
271 ,p_legislation_code => p_rec.legislation_code
272 ,p_cagr_api_id => p_rec.cagr_api_id
273 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
274 ,p_beneficial_formula_id => p_rec.beneficial_formula_id
275 ,p_business_group_id => p_rec.business_group_id
276 ,p_beneficial_rule => p_rec.beneficial_rule
277 ,p_category_name => p_rec.category_name
278 ,p_uom => p_rec.uom
279 ,p_flex_value_set_id => p_rec.flex_value_set_id
280 ,p_object_version_number => p_rec.object_version_number
281 ,p_ben_rule_value_set_id => p_rec.ben_rule_value_set_id
282 ,p_mult_entries_allowed_flag => p_rec.mult_entries_allowed_flag
283 ,p_auto_create_entries_flag => p_rec.auto_create_entries_flag -- CEI Enh
284 ,p_opt_id => p_rec.opt_id
285 );
286 --
287 exception
288 --
289 when hr_api.cannot_find_prog_unit then
290 --
291 hr_api.cannot_find_prog_unit_error
292 (p_module_name => 'PER_CAGR_ENTITLEMENT_ITEMS'
293 ,p_hook_type => 'AI');
294 --
295 end;
296 --
297 hr_utility.set_location(' Leaving:'||l_proc, 10);
298 End post_insert;
299 --
300 -- ----------------------------------------------------------------------------
301 -- |---------------------------------< ins >----------------------------------|
302 -- ----------------------------------------------------------------------------
303 Procedure ins
304 (p_effective_date in date
305 ,p_rec in out nocopy per_cei_shd.g_rec_type
306 ) is
307 --
308 l_proc varchar2(72) := g_package||'ins';
309 --
310 Begin
311 hr_utility.set_location('Entering:'||l_proc, 5);
312 --
313 -- Call the supporting insert validate operations
314 --
315 per_cei_bus.insert_validate
316 (p_effective_date
317 ,p_rec
318 );
319 --
320 -- Call the supporting pre-insert operation
321 --
322 per_cei_ins.pre_insert(p_rec);
323 --
324 -- Insert the row
325 --
326 per_cei_ins.insert_dml(p_rec);
327 --
328 -- Call the supporting post-insert operation
329 --
330 per_cei_ins.post_insert
331 (p_effective_date
332 ,p_rec
333 );
334 --
335 hr_utility.set_location('Leaving:'||l_proc, 20);
336 end ins;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |---------------------------------< ins >----------------------------------|
340 -- ----------------------------------------------------------------------------
341 Procedure ins
342 (p_effective_date in date
343 ,p_item_name in varchar2
344 ,p_legislation_code in varchar2
345 ,p_business_group_id in number
346 ,p_category_name in varchar2
347 ,p_uom in varchar2
348 ,p_flex_value_set_id in number
349 ,p_element_type_id in number default null
350 ,p_input_value_id in varchar2 default null
351 ,p_column_type in varchar2
352 ,p_column_size in number
353 ,p_cagr_api_id in number default null
354 ,p_cagr_api_param_id in number default null
355 ,p_beneficial_formula_id in number default null
356 ,p_beneficial_rule in varchar2 default null
357 ,p_ben_rule_value_set_id in number default null
358 ,p_mult_entries_allowed_flag in varchar2 default null
359 ,p_auto_create_entries_flag in varchar2 default null -- CEI Enh
360 ,p_opt_id in number
361 ,p_cagr_entitlement_item_id out nocopy number
362 ,p_object_version_number out nocopy number
363
364 ) is
365 --
366 l_rec per_cei_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 := per_cei_shd.convert_args
376 (NULL
377 ,p_item_name
378 ,p_element_type_id
379 ,p_input_value_id
380 ,p_column_type
381 ,p_column_size
382 ,p_legislation_code
383 ,p_cagr_api_id
384 ,p_cagr_api_param_id
385 ,p_business_group_id
386 ,p_beneficial_rule
387 ,p_category_name
388 ,p_uom
389 ,p_flex_value_set_id
390 ,p_beneficial_formula_id
391 ,p_object_version_number
392 ,p_ben_rule_value_set_id
393 ,p_mult_entries_allowed_flag
394 ,p_auto_create_entries_flag -- CEI Enh
395 ,p_opt_id) ;
396 --
397 -- Having converted the arguments into the per_cei_rec
398 -- plsql record structure we call the corresponding record business process.
399 --
400 per_cei_ins.ins
401 (p_effective_date
402 ,l_rec
403 );
404 --
405 -- As the primary key argument(s)
406 -- are specified as an OUT's we must set these values.
407 --
408 p_cagr_entitlement_item_id := l_rec.cagr_entitlement_item_id;
409 p_object_version_number := l_rec.object_version_number;
410 --
411 --
412 hr_utility.set_location(' Leaving:'||l_proc, 10);
413 End ins;
414 --
415 end per_cei_ins;