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