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