1 Package Body per_esa_ins as
2 /* $Header: peesarhi.pkb 120.4 2010/09/20 10:34:02 psugumar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_esa_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_attendance_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_attendance_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_esa_ins.g_attendance_id_i := p_attendance_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 -- Pre Conditions:
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 Table Handler Use Only.
72 --
73 -- {End Of Comments}
74 -- ----------------------------------------------------------------------------
75 Procedure insert_dml(p_rec in out nocopy per_esa_shd.g_rec_type) is
76 --
77 l_proc varchar2(72) := g_package||'insert_dml';
78 --
79 Begin
80 hr_utility.set_location('Entering:'||l_proc, 5);
81 p_rec.object_version_number := 1; -- Initialise the object version
82 --
83 per_esa_shd.g_api_dml := true; -- Set the api dml status
84 --
85 -- Insert the row into: per_establishment_attendances
86 --
87 insert into per_establishment_attendances
88 ( attendance_id,
89 person_id,
90 establishment_id,
91 establishment,
92 attended_start_date,
93 attended_end_date,
94 full_time,
95 attribute_category,
96 attribute1,
97 attribute2,
98 attribute3,
99 attribute4,
100 attribute5,
101 attribute6,
102 attribute7,
103 attribute8,
104 attribute9,
105 attribute10,
106 attribute11,
107 attribute12,
108 attribute13,
109 attribute14,
110 attribute15,
111 attribute16,
112 attribute17,
113 attribute18,
114 attribute19,
115 attribute20,
116 object_version_number,
117 business_group_id,
118 party_id, -- HR/TCA merge
119 address
120 )
121 Values
122 ( p_rec.attendance_id,
123 p_rec.person_id,
124 p_rec.establishment_id,
125 p_rec.establishment,
126 p_rec.attended_start_date,
127 p_rec.attended_end_date,
128 p_rec.full_time,
129 p_rec.attribute_category,
130 p_rec.attribute1,
131 p_rec.attribute2,
132 p_rec.attribute3,
133 p_rec.attribute4,
134 p_rec.attribute5,
135 p_rec.attribute6,
136 p_rec.attribute7,
137 p_rec.attribute8,
138 p_rec.attribute9,
139 p_rec.attribute10,
140 p_rec.attribute11,
141 p_rec.attribute12,
142 p_rec.attribute13,
143 p_rec.attribute14,
144 p_rec.attribute15,
145 p_rec.attribute16,
146 p_rec.attribute17,
147 p_rec.attribute18,
148 p_rec.attribute19,
149 p_rec.attribute20,
150 p_rec.object_version_number,
151 p_rec.business_group_id,
152 p_rec.party_id, -- HR/TCA merge
153 p_rec.address
154 );
155 --
156 per_esa_shd.g_api_dml := false; -- Unset the api dml status
157 --
158 hr_utility.set_location(' Leaving:'||l_proc, 10);
159 Exception
160 When hr_api.check_integrity_violated Then
161 -- A check constraint has been violated
162 per_esa_shd.g_api_dml := false; -- Unset the api dml status
163 per_esa_shd.constraint_error
164 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
165 When hr_api.parent_integrity_violated Then
166 -- Parent integrity has been violated
167 per_esa_shd.g_api_dml := false; -- Unset the api dml status
168 per_esa_shd.constraint_error
169 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
170 When hr_api.unique_integrity_violated Then
171 -- Unique integrity has been violated
172 per_esa_shd.g_api_dml := false; -- Unset the api dml status
173 per_esa_shd.constraint_error
174 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
175 When Others Then
176 per_esa_shd.g_api_dml := false; -- Unset the api dml status
177 Raise;
178 End insert_dml;
179 --
180 -- ----------------------------------------------------------------------------
181 -- |------------------------------< pre_insert >------------------------------|
182 -- ----------------------------------------------------------------------------
183 -- {Start Of Comments}
184 --
185 -- Description:
186 -- This private procedure contains any processing which is required before
187 -- the insert dml. Presently, if the entity has a corresponding primary
188 -- key which is maintained by an associating sequence, the primary key for
189 -- the entity will be populated with the next sequence value in
190 -- preparation for the insert dml.
191 --
192 -- Pre Conditions:
193 -- This is an internal procedure which is called from the ins procedure.
194 --
195 -- In Parameters:
196 -- A Pl/Sql record structre.
197 --
198 -- Post Success:
199 -- Processing continues.
200 --
201 -- Post Failure:
202 -- If an error has occurred, an error message and exception will be raised
203 -- but not handled.
204 --
205 -- Developer Implementation Notes:
206 -- Any pre-processing required before the insert dml is issued should be
207 -- coded within this procedure. As stated above, a good example is the
208 -- generation of a primary key number via a corresponding sequence.
209 -- It is important to note that any 3rd party maintenance should be reviewed
210 -- before placing in this procedure.
211 --
212 -- Access Status:
213 -- Internal Table Handler Use Only.
214 --
215 -- {End Of Comments}
216 -- ----------------------------------------------------------------------------
217 Procedure pre_insert(p_rec in out nocopy per_esa_shd.g_rec_type) is
218 --
219 cursor C_Sel1 is
220 select per_estab_attendances_s.nextval
221 from sys.dual;
222 --
223 --
224 Cursor C_Sel2 is
225 Select null
226 from per_establishment_attendances
227 where attendance_id =
228 per_esa_ins.g_attendance_id_i;
229 --
230 l_proc varchar2(72) := g_package||'pre_insert';
231 l_exists varchar2(1);
232 --
233 Begin
234 hr_utility.set_location('Entering:'||l_proc, 5);
235 --
236 If (per_esa_ins.g_attendance_id_i is not null) Then
237 --
238 -- Verify registered primary key values not already in use
239 --
240 Open C_Sel2;
241 Fetch C_Sel2 into l_exists;
242 If C_Sel2%found Then
243 Close C_Sel2;
244 --
245 -- The primary key values are already in use.
246 --
247 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
248 fnd_message.set_name('TABLE_NAME','per_establishment_attendances');
249 fnd_message.raise_error;
250 End If;
251 Close C_Sel2;
252 --
253 -- Use registered key values and clear globals
254 --
255 p_rec.attendance_id :=
256 per_esa_ins.g_attendance_id_i;
257 per_esa_ins.g_attendance_id_i := null;
258 Else
259 --
260 -- No registerd key values, so select the next sequence number
261 --
262 --
263 -- Select the next sequence number
264 --
265 Open C_Sel1;
266 Fetch C_Sel1 Into p_rec.attendance_id;
267 Close C_Sel1;
268 End If;
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 End pre_insert;
272 --
273 -- ----------------------------------------------------------------------------
274 -- |-----------------------------< post_insert >------------------------------|
275 -- ----------------------------------------------------------------------------
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- This private procedure contains any processing which is required after the
280 -- insert dml.
281 --
282 -- Pre Conditions:
283 -- This is an internal procedure which is called from the ins procedure.
284 --
285 -- In Parameters:
286 -- A Pl/Sql record structre.
287 --
288 -- Post Success:
289 -- Processing continues.
290 --
291 -- Post Failure:
292 -- If an error has occurred, an error message and exception will be raised
293 -- but not handled.
294 --
295 -- Developer Implementation Notes:
296 -- Any post-processing required after the insert dml is issued should be
297 -- coded within this procedure. It is important to note that any 3rd party
298 -- maintenance should be reviewed before placing in this procedure.
299 --
300 -- Access Status:
301 -- Internal Table Handler Use Only.
302 --
303 -- {End Of Comments}
304 -- ----------------------------------------------------------------------------
305 Procedure post_insert(p_rec in per_esa_shd.g_rec_type,
306 p_effective_date in date
307 ) is
308 --
309 l_proc varchar2(72) := g_package||'post_insert';
310 --
311 Begin
312 hr_utility.set_location('Entering:'||l_proc, 5);
313 --
314 --
315 -- Start of API User Hook for post_insert.
316 begin
317 per_esa_rki.after_insert
318 (p_attendance_id => p_rec.attendance_id,
319 p_person_id => p_rec.person_id,
320 p_establishment_id => p_rec.establishment_id,
321 p_establishment => p_rec.establishment,
322 p_attended_start_date => p_rec.attended_start_date,
323 p_attended_end_date => p_rec.attended_end_date,
324 p_full_time => p_rec.full_time,
325 p_attribute_category => p_rec.attribute_category,
326 p_attribute1 => p_rec.attribute1,
327 p_attribute2 => p_rec.attribute2,
328 p_attribute3 => p_rec.attribute3,
329 p_attribute4 => p_rec.attribute4,
330 p_attribute5 => p_rec.attribute5,
331 p_attribute6 => p_rec.attribute6,
332 p_attribute7 => p_rec.attribute7,
333 p_attribute8 => p_rec.attribute8,
334 p_attribute9 => p_rec.attribute9,
335 p_attribute10 => p_rec.attribute10,
336 p_attribute11 => p_rec.attribute11,
337 p_attribute12 => p_rec.attribute12,
338 p_attribute13 => p_rec.attribute13,
339 p_attribute14 => p_rec.attribute14,
340 p_attribute15 => p_rec.attribute15,
341 p_attribute16 => p_rec.attribute16,
342 p_attribute17 => p_rec.attribute17,
343 p_attribute18 => p_rec.attribute18,
344 p_attribute19 => p_rec.attribute19,
345 p_attribute20 => p_rec.attribute20,
346 p_object_version_number => p_rec.object_version_number,
347 p_business_group_id => p_rec.business_group_id,
348 p_effective_date => p_effective_date,
349 p_party_id => p_rec.party_id, -- HR/TCA merge
350 p_address => p_rec.address
351 );
352 exception
353 when hr_api.cannot_find_prog_unit then
354 hr_api.cannot_find_prog_unit_error
355 (p_module_name => 'PER_ESTABLISHMENT_ATTENDANCES'
356 ,p_hook_type => 'AI'
357 );
358 end;
359 -- End of API User Hook for post_insert.
360 --
361 hr_utility.set_location(' Leaving:'||l_proc, 10);
362 End post_insert;
363 --
364 -- ----------------------------------------------------------------------------
365 -- |---------------------------------< ins >----------------------------------|
366 -- ----------------------------------------------------------------------------
367 Procedure ins
368 (
369 p_rec in out nocopy per_esa_shd.g_rec_type,
370 p_effective_date in date,
371 p_validate in boolean default false
372 ) is
373 --
374 l_proc varchar2(72) := g_package||'ins';
375 --
376 Begin
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 --
379 -- Determine if the business process is to be validated.
380 --
381 If p_validate then
382 --
383 -- Issue the savepoint.
384 --
385 SAVEPOINT ins_per_esa;
386 End If;
387 --
388 -- Call the supporting insert validate operations
389 --
390 per_esa_bus.insert_validate(p_rec,p_effective_date);
391 --
392 -- Call the supporting pre-insert operation
393 --
394 pre_insert(p_rec);
395 --
396 -- Insert the row
397 --
398 insert_dml(p_rec);
399 --
400 -- Call the supporting post-insert operation
401 --
402 post_insert(p_rec, p_effective_date);
403 --
404 -- If we are validating then raise the Validate_Enabled exception
405 --
406 If p_validate then
407 Raise HR_Api.Validate_Enabled;
408 End If;
409 --
410 hr_utility.set_location(' Leaving:'||l_proc, 10);
411 Exception
412 When HR_Api.Validate_Enabled Then
413 --
414 -- As the Validate_Enabled exception has been raised
415 -- we must rollback to the savepoint
416 --
417 ROLLBACK TO ins_per_esa;
418 end ins;
419 --
420 -- ----------------------------------------------------------------------------
421 -- |---------------------------------< ins >----------------------------------|
422 -- ----------------------------------------------------------------------------
423 Procedure ins
424 (
425 p_attendance_id out nocopy number,
426 p_person_id in number default null,
427 p_establishment_id in number default null,
428 p_establishment in varchar2 default null,
429 p_attended_start_date in date default null,
430 p_attended_end_date in date default null,
431 p_full_time in varchar2 default null,
432 p_attribute_category in varchar2 default null,
433 p_attribute1 in varchar2 default null,
434 p_attribute2 in varchar2 default null,
435 p_attribute3 in varchar2 default null,
436 p_attribute4 in varchar2 default null,
437 p_attribute5 in varchar2 default null,
438 p_attribute6 in varchar2 default null,
439 p_attribute7 in varchar2 default null,
440 p_attribute8 in varchar2 default null,
441 p_attribute9 in varchar2 default null,
442 p_attribute10 in varchar2 default null,
443 p_attribute11 in varchar2 default null,
444 p_attribute12 in varchar2 default null,
445 p_attribute13 in varchar2 default null,
446 p_attribute14 in varchar2 default null,
447 p_attribute15 in varchar2 default null,
448 p_attribute16 in varchar2 default null,
449 p_attribute17 in varchar2 default null,
450 p_attribute18 in varchar2 default null,
451 p_attribute19 in varchar2 default null,
452 p_attribute20 in varchar2 default null,
453 p_object_version_number out nocopy number,
454 p_business_group_id in number default null, -- HR/TCA merge
455 p_effective_date in date,
456 p_validate in boolean default false,
457 p_party_id in number default null, -- HR/TCA merge
458 p_address in varchar2 default null
459 ) is
460 --
461 l_rec per_esa_shd.g_rec_type;
462 l_proc varchar2(72) := g_package||'ins';
463 --
464 Begin
465 hr_utility.set_location('Entering:'||l_proc, 5);
466 --
467 -- Call conversion function to turn arguments into the
468 -- p_rec structure.
469 --
470 l_rec :=
471 per_esa_shd.convert_args
472 (
473 null,
474 p_person_id,
475 p_establishment_id,
476 p_establishment,
477 p_attended_start_date,
478 p_attended_end_date,
479 p_full_time,
480 p_attribute_category,
481 p_attribute1,
482 p_attribute2,
483 p_attribute3,
484 p_attribute4,
485 p_attribute5,
486 p_attribute6,
487 p_attribute7,
488 p_attribute8,
489 p_attribute9,
490 p_attribute10,
491 p_attribute11,
492 p_attribute12,
493 p_attribute13,
494 p_attribute14,
495 p_attribute15,
496 p_attribute16,
497 p_attribute17,
498 p_attribute18,
499 p_attribute19,
500 p_attribute20,
501 null,
502 p_business_group_id,
503 p_party_id, -- HR/TCA merge
504 p_address
505 );
506 --
507 -- Having converted the arguments into the per_esa_rec
508 -- plsql record structure we call the corresponding record business process.
509 --
510 ins(l_rec, p_effective_date, p_validate);
511 --
512 -- As the primary key argument(s)
513 -- are specified as an OUT's we must set these values.
514 --
515 p_attendance_id := l_rec.attendance_id;
516 p_object_version_number := l_rec.object_version_number;
517 --
518 hr_utility.set_location(' Leaving:'||l_proc, 10);
519 End ins;
520 --
521 end per_esa_ins;
[Home] [Help]