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