1 Package Body per_cnl_ins as
2 /* $Header: pecnlrhi.pkb 120.0 2005/05/31 06:55 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cnl_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_location_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_location_id in number) is
20 --
21 l_proc varchar2(72) := g_package||'set_base_key_value';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 per_cnl_ins.g_location_id_i := p_location_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< insert_dml >------------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic. The processing of
39 -- this procedure are as follows:
40 -- 1) Initialise the object_version_number to 1 if the object_version_number
41 -- is defined as an attribute for this entity.
42 -- 2) To set and unset the g_api_dml status as required (as we are about to
43 -- perform dml).
44 -- 3) To insert the row into the schema.
45 -- 4) To trap any constraint violations that may have occurred.
46 -- 5) To raise any other errors.
47 --
48 -- Prerequisites:
49 -- This is an internal private procedure which must be called from the ins
50 -- procedure and must have all mandatory attributes set (except the
51 -- object_version_number which is initialised within this procedure).
52 --
53 -- In Parameters:
54 -- A Pl/Sql record structre.
55 --
56 -- Post Success:
57 -- The specified row will be inserted into the schema.
58 --
59 -- Post Failure:
60 -- On the insert dml failure it is important to note that we always reset the
61 -- g_api_dml status to false.
62 -- If a check, unique or parent integrity constraint violation is raised the
63 -- constraint_error procedure will be called.
64 -- If any other error is reported, the error will be raised after the
65 -- g_api_dml status is reset.
66 --
67 -- Developer Implementation Notes:
68 -- None.
69 --
70 -- Access Status:
71 -- Internal Row Handler Use Only.
72 --
73 -- {End Of Comments}
74 -- ----------------------------------------------------------------------------
75 Procedure insert_dml
76 (p_rec in out nocopy per_cnl_shd.g_rec_type
77 ) is
78 --
79 l_proc varchar2(72) := g_package||'insert_dml';
80 --
81 Begin
82 hr_utility.set_location('Entering:'||l_proc, 5);
83 p_rec.object_version_number := 1; -- Initialise the object version
84 --
85 --
86 --
87 -- Insert the row into: per_ri_config_locations
88 --
89 insert into per_ri_config_locations
90 (configuration_code
91 ,configuration_context
92 ,location_id
93 ,location_code
94 ,description
95 ,style
96 ,address_line_1
97 ,address_line_2
98 ,address_line_3
99 ,town_or_city
100 ,country
101 ,postal_code
102 ,region_1
103 ,region_2
104 ,region_3
105 ,telephone_number_1
106 ,telephone_number_2
107 ,telephone_number_3
108 ,loc_information13
109 ,loc_information14
110 ,loc_information15
111 ,loc_information16
112 ,loc_information17
113 ,loc_information18
114 ,loc_information19
115 ,loc_information20
116 ,object_version_number
117 )
118 Values
119 (p_rec.configuration_code
120 ,p_rec.configuration_context
121 ,p_rec.location_id
122 ,p_rec.location_code
123 ,p_rec.description
124 ,p_rec.style
125 ,p_rec.address_line_1
126 ,p_rec.address_line_2
127 ,p_rec.address_line_3
128 ,p_rec.town_or_city
129 ,p_rec.country
130 ,p_rec.postal_code
131 ,p_rec.region_1
132 ,p_rec.region_2
133 ,p_rec.region_3
134 ,p_rec.telephone_number_1
135 ,p_rec.telephone_number_2
136 ,p_rec.telephone_number_3
137 ,p_rec.loc_information13
138 ,p_rec.loc_information14
139 ,p_rec.loc_information15
140 ,p_rec.loc_information16
141 ,p_rec.loc_information17
142 ,p_rec.loc_information18
143 ,p_rec.loc_information19
144 ,p_rec.loc_information20
145 ,p_rec.object_version_number
146 );
147 --
148 --
149 --
150 hr_utility.set_location(' Leaving:'||l_proc, 10);
151 Exception
152 When hr_api.check_integrity_violated Then
153 -- A check constraint has been violated
154 --
155 per_cnl_shd.constraint_error
156 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
157 When hr_api.parent_integrity_violated Then
158 -- Parent integrity has been violated
159 --
160 per_cnl_shd.constraint_error
161 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
162 When hr_api.unique_integrity_violated Then
163 -- Unique integrity has been violated
164 --
165 per_cnl_shd.constraint_error
166 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
167 When Others Then
168 --
169 Raise;
170 End insert_dml;
171 --
172 -- ----------------------------------------------------------------------------
173 -- |------------------------------< pre_insert >------------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This private procedure contains any processing which is required before
179 -- the insert dml. Presently, if the entity has a corresponding primary
180 -- key which is maintained by an associating sequence, the primary key for
181 -- the entity will be populated with the next sequence value in
182 -- preparation for the insert dml.
183 --
184 -- Prerequisites:
185 -- This is an internal procedure which is called from the ins procedure.
186 --
187 -- In Parameters:
188 -- A Pl/Sql record structure.
189 --
190 -- Post Success:
191 -- Processing continues.
192 --
193 -- Post Failure:
194 -- If an error has occurred, an error message and exception will be raised
195 -- but not handled.
196 --
197 -- Developer Implementation Notes:
198 -- Any pre-processing required before the insert dml is issued should be
199 -- coded within this procedure. As stated above, a good example is the
200 -- generation of a primary key number via a corresponding sequence.
201 -- It is important to note that any 3rd party maintenance should be reviewed
202 -- before placing in this procedure.
203 --
204 -- Access Status:
205 -- Internal Row Handler Use Only.
206 --
207 -- {End Of Comments}
208 -- ----------------------------------------------------------------------------
209 Procedure pre_insert
210 (p_rec in out nocopy per_cnl_shd.g_rec_type
211 ) is
212 --
213 Cursor C_Sel1 is select per_ri_config_locations_s.nextval from sys.dual;
214 --
215 Cursor C_Sel2 is
216 Select null
217 from per_ri_config_locations
218 where location_id =
219 per_cnl_ins.g_location_id_i;
220 --
221 l_proc varchar2(72) := g_package||'pre_insert';
222 l_exists varchar2(1);
223 --
224 Begin
225 hr_utility.set_location('Entering:'||l_proc, 5);
226 --
227 If (per_cnl_ins.g_location_id_i is not null) Then
228 --
229 -- Verify registered primary key values not already in use
230 --
231 Open C_Sel2;
232 Fetch C_Sel2 into l_exists;
233 If C_Sel2%found Then
234 Close C_Sel2;
235 --
236 -- The primary key values are already in use.
237 --
238 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
239 fnd_message.set_token('TABLE_NAME','per_ri_config_locations');
240 fnd_message.raise_error;
241 End If;
242 Close C_Sel2;
243 --
244 -- Use registered key values and clear globals
245 --
246 p_rec.location_id :=
247 per_cnl_ins.g_location_id_i;
248 per_cnl_ins.g_location_id_i := null;
249 Else
250 --
251 -- No registerd key values, so select the next sequence number
252 --
253 --
254 -- Select the next sequence number
255 --
256 Open C_Sel1;
257 Fetch C_Sel1 Into p_rec.location_id;
258 Close C_Sel1;
259 End If;
260 --
261 hr_utility.set_location(' Leaving:'||l_proc, 10);
262 End pre_insert;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |-----------------------------< post_insert >------------------------------|
266 -- ----------------------------------------------------------------------------
267 -- {Start Of Comments}
268 --
269 -- Description:
270 -- This private procedure contains any processing which is required after
271 -- the insert dml.
272 --
273 -- Prerequisites:
274 -- This is an internal procedure which is called from the ins procedure.
275 --
276 -- In Parameters:
277 -- A Pl/Sql record structre.
278 --
279 -- Post Success:
280 -- Processing continues.
281 --
282 -- Post Failure:
283 -- If an error has occurred, an error message and exception will be raised
284 -- but not handled.
285 --
286 -- Developer Implementation Notes:
287 -- Any post-processing required after the insert dml is issued should be
288 -- coded within this procedure. It is important to note that any 3rd party
289 -- maintenance should be reviewed before placing in this procedure.
290 --
291 -- Access Status:
292 -- Internal Row Handler Use Only.
293 --
294 -- {End Of Comments}
295 -- ----------------------------------------------------------------------------
296 Procedure post_insert
297 (p_effective_date in date
298 ,p_rec in per_cnl_shd.g_rec_type
299 ) is
300 --
301 l_proc varchar2(72) := g_package||'post_insert';
302 --
303 Begin
304 hr_utility.set_location('Entering:'||l_proc, 5);
305 begin
306 --
307 per_cnl_rki.after_insert
308 (p_effective_date => p_effective_date
309 ,p_configuration_code => p_rec.configuration_code
310 ,p_configuration_context => p_rec.configuration_context
311 ,p_location_id => p_rec.location_id
312 ,p_location_code => p_rec.location_code
313 ,p_description => p_rec.description
314 ,p_style => p_rec.style
315 ,p_address_line_1 => p_rec.address_line_1
316 ,p_address_line_2 => p_rec.address_line_2
317 ,p_address_line_3 => p_rec.address_line_3
318 ,p_town_or_city => p_rec.town_or_city
319 ,p_country => p_rec.country
320 ,p_postal_code => p_rec.postal_code
321 ,p_region_1 => p_rec.region_1
322 ,p_region_2 => p_rec.region_2
323 ,p_region_3 => p_rec.region_3
324 ,p_telephone_number_1 => p_rec.telephone_number_1
325 ,p_telephone_number_2 => p_rec.telephone_number_2
326 ,p_telephone_number_3 => p_rec.telephone_number_3
327 ,p_loc_information13 => p_rec.loc_information13
328 ,p_loc_information14 => p_rec.loc_information14
329 ,p_loc_information15 => p_rec.loc_information15
330 ,p_loc_information16 => p_rec.loc_information16
331 ,p_loc_information17 => p_rec.loc_information17
332 ,p_loc_information18 => p_rec.loc_information18
333 ,p_loc_information19 => p_rec.loc_information19
334 ,p_loc_information20 => p_rec.loc_information20
335 ,p_object_version_number => p_rec.object_version_number
336 );
337 --
338 exception
339 --
340 when hr_api.cannot_find_prog_unit then
341 --
342 hr_api.cannot_find_prog_unit_error
343 (p_module_name => 'PER_RI_CONFIG_LOCATIONS'
344 ,p_hook_type => 'AI');
345 --
346 end;
347 --
348 hr_utility.set_location(' Leaving:'||l_proc, 10);
349 End post_insert;
350 --
351 -- ----------------------------------------------------------------------------
352 -- |---------------------------------< ins >----------------------------------|
353 -- ----------------------------------------------------------------------------
354 Procedure ins
355 (p_effective_date in date
356 ,p_rec in out nocopy per_cnl_shd.g_rec_type
357 ) is
358 --
359 l_proc varchar2(72) := g_package||'ins';
360 --
361 Begin
362 hr_utility.set_location('Entering:'||l_proc, 5);
363 --
364 -- Call the supporting insert validate operations
365 --
366 per_cnl_bus.insert_validate
367 (p_effective_date
368 ,p_rec
369 );
370 --
371 -- Call to raise any errors on multi-message list
372 hr_multi_message.end_validation_set;
373 --
374 -- Call the supporting pre-insert operation
375 --
376 per_cnl_ins.pre_insert(p_rec);
377 --
378 -- Insert the row
379 --
380 per_cnl_ins.insert_dml(p_rec);
381 --
382 -- Call the supporting post-insert operation
383 --
384 per_cnl_ins.post_insert
385 (p_effective_date ,p_rec );
386 --
387 -- Call to raise any errors on multi-message list
388 hr_multi_message.end_validation_set;
389 --
390 hr_utility.set_location('Leaving:'||l_proc, 20);
391 end ins;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------------< ins >----------------------------------|
395 -- ----------------------------------------------------------------------------
396 Procedure ins
397 (p_effective_date in date
398 ,p_configuration_code in varchar2
399 ,p_configuration_context in varchar2
400 ,p_location_code in varchar2 default null
401 ,p_description in varchar2 default null
402 ,p_style in varchar2 default null
403 ,p_address_line_1 in varchar2 default null
404 ,p_address_line_2 in varchar2 default null
405 ,p_address_line_3 in varchar2 default null
406 ,p_town_or_city in varchar2 default null
407 ,p_country in varchar2 default null
408 ,p_postal_code in varchar2 default null
409 ,p_region_1 in varchar2 default null
410 ,p_region_2 in varchar2 default null
411 ,p_region_3 in varchar2 default null
412 ,p_telephone_number_1 in varchar2 default null
413 ,p_telephone_number_2 in varchar2 default null
414 ,p_telephone_number_3 in varchar2 default null
415 ,p_loc_information13 in varchar2 default null
416 ,p_loc_information14 in varchar2 default null
417 ,p_loc_information15 in varchar2 default null
418 ,p_loc_information16 in varchar2 default null
419 ,p_loc_information17 in varchar2 default null
420 ,p_loc_information18 in varchar2 default null
421 ,p_loc_information19 in varchar2 default null
422 ,p_loc_information20 in varchar2 default null
423 ,p_location_id out nocopy number
424 ,p_object_version_number out nocopy number
425 ) is
426 --
427 l_rec per_cnl_shd.g_rec_type;
428 l_proc varchar2(72) := g_package||'ins';
429 --
430 Begin
431 hr_utility.set_location('Entering:'||l_proc, 5);
432 --
433 -- Call conversion function to turn arguments into the
434 -- p_rec structure.
435 --
436 l_rec :=
437 per_cnl_shd.convert_args
438 (p_configuration_code
439 ,p_configuration_context
440 ,null
441 ,p_location_code
442 ,p_description
443 ,p_style
444 ,p_address_line_1
445 ,p_address_line_2
446 ,p_address_line_3
447 ,p_town_or_city
448 ,p_country
449 ,p_postal_code
450 ,p_region_1
451 ,p_region_2
452 ,p_region_3
453 ,p_telephone_number_1
454 ,p_telephone_number_2
455 ,p_telephone_number_3
456 ,p_loc_information13
457 ,p_loc_information14
458 ,p_loc_information15
459 ,p_loc_information16
460 ,p_loc_information17
461 ,p_loc_information18
462 ,p_loc_information19
463 ,p_loc_information20
464 ,null
465 );
466 --
467 -- Having converted the arguments into the per_cnl_rec
468 -- plsql record structure we call the corresponding record business process.
469 --
470 per_cnl_ins.ins
471 (p_effective_date
472 ,l_rec
473 );
474 --
475 -- As the primary key argument(s)
476 -- are specified as an OUT's we must set these values.
477 --
478 p_location_id := l_rec.location_id;
479 p_object_version_number := l_rec.object_version_number;
480 --
481 hr_utility.set_location(' Leaving:'||l_proc, 10);
482 End ins;
483 --
484 end per_cnl_ins;