[Home] [Help]
PACKAGE BODY: APPS.PQH_CRD_INS
Source
1 Package Body pqh_crd_ins as
2 /* $Header: pqcrdrhi.pkb 120.0 2005/05/29 01:45 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_crd_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_criteria_rate_defn_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_criteria_rate_defn_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 pqh_crd_ins.g_criteria_rate_defn_id_i := p_criteria_rate_defn_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |-----------------------< create_app_ownerships >--------------------------|
34 -- ----------------------------------------------------------------------------
35 --
36 -- Description:
37 -- This procedure inserts a row into the HR_APPLICATION_OWNERSHIPS table
38 -- when the row handler is called in the appropriate mode.
39 --
40 -- ----------------------------------------------------------------------------
41 PROCEDURE create_app_ownerships(p_pk_column IN varchar2
42 ,p_pk_value IN varchar2) IS
43 --
44 CURSOR csr_definition IS
45 SELECT product_short_name
46 FROM hr_owner_definitions
47 WHERE session_id = hr_startup_data_api_support.g_session_id;
48 --
49 BEGIN
50 --
51 IF (hr_startup_data_api_support.return_startup_mode IN
52 ('STARTUP','GENERIC')) THEN
53 --
54 FOR c1 IN csr_definition LOOP
55 --
56 INSERT INTO hr_application_ownerships
57 (key_name
58 ,key_value
59 ,product_name
60 )
61 VALUES
62 (p_pk_column
63 ,fnd_number.number_to_canonical(p_pk_value)
64 ,c1.product_short_name
65 );
66 END LOOP;
67 END IF;
68 END create_app_ownerships;
69 --
70 -- ----------------------------------------------------------------------------
71 -- |-----------------------< create_app_ownerships >--------------------------|
72 -- ----------------------------------------------------------------------------
73 PROCEDURE create_app_ownerships(p_pk_column IN varchar2
74 ,p_pk_value IN number) IS
75 --
76 BEGIN
77 create_app_ownerships(p_pk_column, to_char(p_pk_value));
78 END create_app_ownerships;
79 --
80 -- ----------------------------------------------------------------------------
81 -- |------------------------------< insert_dml >------------------------------|
82 -- ----------------------------------------------------------------------------
83 -- {Start Of Comments}
84 --
85 -- Description:
86 -- This procedure controls the actual dml insert logic. The processing of
87 -- this procedure are as follows:
88 -- 1) Initialise the object_version_number to 1 if the object_version_number
89 -- is defined as an attribute for this entity.
90 -- 2) To set and unset the g_api_dml status as required (as we are about to
91 -- perform dml).
92 -- 3) To insert the row into the schema.
93 -- 4) To trap any constraint violations that may have occurred.
94 -- 5) To raise any other errors.
95 --
96 -- Prerequisites:
97 -- This is an internal private procedure which must be called from the ins
98 -- procedure and must have all mandatory attributes set (except the
99 -- object_version_number which is initialised within this procedure).
100 --
101 -- In Parameters:
102 -- A Pl/Sql record structre.
103 --
104 -- Post Success:
105 -- The specified row will be inserted into the schema.
106 --
107 -- Post Failure:
108 -- On the insert dml failure it is important to note that we always reset the
109 -- g_api_dml status to false.
110 -- If a check, unique or parent integrity constraint violation is raised the
111 -- constraint_error procedure will be called.
112 -- If any other error is reported, the error will be raised after the
113 -- g_api_dml status is reset.
114 --
115 -- Developer Implementation Notes:
116 -- None.
117 --
118 -- Access Status:
119 -- Internal Row Handler Use Only.
120 --
121 -- {End Of Comments}
122 -- ----------------------------------------------------------------------------
123 Procedure insert_dml
124 (p_rec in out nocopy pqh_crd_shd.g_rec_type
125 ) is
126 --
127 l_proc varchar2(72) := g_package||'insert_dml';
128 --
129 Begin
130 hr_utility.set_location('Entering:'||l_proc, 5);
131 p_rec.object_version_number := 1; -- Initialise the object version
132 --
133 --
134 --
135 -- Insert the row into: pqh_criteria_rate_defn
136 --
137 insert into pqh_criteria_rate_defn
138 (criteria_rate_defn_id
139 ,short_name
140 ,uom
141 ,currency_code
142 ,reference_period_cd
143 ,define_max_rate_flag
144 ,define_min_rate_flag
145 ,define_mid_rate_flag
146 ,define_std_rate_flag
147 ,rate_calc_cd
148 ,rate_calc_rule
149 ,preferential_rate_cd
150 ,preferential_rate_rule
151 ,rounding_cd
152 ,rounding_rule
153 ,business_group_id
154 ,legislation_code
155 ,attribute_category
156 ,attribute1
157 ,attribute2
158 ,attribute3
159 ,attribute4
160 ,attribute5
161 ,attribute6
162 ,attribute7
163 ,attribute8
164 ,attribute9
165 ,attribute10
166 ,attribute11
167 ,attribute12
168 ,attribute13
169 ,attribute14
170 ,attribute15
171 ,attribute16
172 ,attribute17
173 ,attribute18
174 ,attribute19
175 ,attribute20
176 ,attribute21
177 ,attribute22
178 ,attribute23
179 ,attribute24
180 ,attribute25
181 ,attribute26
182 ,attribute27
183 ,attribute28
184 ,attribute29
185 ,attribute30
186 ,object_version_number
187 )
188 Values
189 (p_rec.criteria_rate_defn_id
190 ,p_rec.short_name
191 ,p_rec.uom
192 ,p_rec.currency_code
193 ,p_rec.reference_period_cd
194 ,p_rec.define_max_rate_flag
195 ,p_rec.define_min_rate_flag
196 ,p_rec.define_mid_rate_flag
200 ,p_rec.preferential_rate_cd
197 ,p_rec.define_std_rate_flag
198 ,p_rec.rate_calc_cd
199 ,p_rec.rate_calc_rule
201 ,p_rec.preferential_rate_rule
202 ,p_rec.rounding_cd
203 ,p_rec.rounding_rule
204 ,p_rec.business_group_id
205 ,p_rec.legislation_code
206 ,p_rec.attribute_category
207 ,p_rec.attribute1
208 ,p_rec.attribute2
209 ,p_rec.attribute3
210 ,p_rec.attribute4
211 ,p_rec.attribute5
212 ,p_rec.attribute6
213 ,p_rec.attribute7
214 ,p_rec.attribute8
215 ,p_rec.attribute9
216 ,p_rec.attribute10
217 ,p_rec.attribute11
218 ,p_rec.attribute12
219 ,p_rec.attribute13
220 ,p_rec.attribute14
221 ,p_rec.attribute15
222 ,p_rec.attribute16
223 ,p_rec.attribute17
224 ,p_rec.attribute18
225 ,p_rec.attribute19
226 ,p_rec.attribute20
227 ,p_rec.attribute21
228 ,p_rec.attribute22
229 ,p_rec.attribute23
230 ,p_rec.attribute24
231 ,p_rec.attribute25
232 ,p_rec.attribute26
233 ,p_rec.attribute27
234 ,p_rec.attribute28
235 ,p_rec.attribute29
236 ,p_rec.attribute30
237 ,p_rec.object_version_number
238 );
239 --
240 --
241 --
242 hr_utility.set_location(' Leaving:'||l_proc, 10);
243 Exception
244 When hr_api.check_integrity_violated Then
245 -- A check constraint has been violated
246 --
247 pqh_crd_shd.constraint_error
248 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
249 When hr_api.parent_integrity_violated Then
250 -- Parent integrity has been violated
251 --
252 pqh_crd_shd.constraint_error
253 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
254 When hr_api.unique_integrity_violated Then
255 -- Unique integrity has been violated
256 --
257 pqh_crd_shd.constraint_error
258 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
259 When Others Then
260 --
261 Raise;
262 End insert_dml;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |------------------------------< pre_insert >------------------------------|
266 -- ----------------------------------------------------------------------------
267 -- {Start Of Comments}
268 --
269 -- Description:
270 -- This private procedure contains any processing which is required before
271 -- the insert dml. Presently, if the entity has a corresponding primary
272 -- key which is maintained by an associating sequence, the primary key for
273 -- the entity will be populated with the next sequence value in
274 -- preparation for the insert dml.
275 --
276 -- Prerequisites:
277 -- This is an internal procedure which is called from the ins procedure.
278 --
279 -- In Parameters:
280 -- A Pl/Sql record structure.
281 --
282 -- Post Success:
283 -- Processing continues.
284 --
285 -- Post Failure:
286 -- If an error has occurred, an error message and exception will be raised
287 -- but not handled.
288 --
289 -- Developer Implementation Notes:
290 -- Any pre-processing required before the insert dml is issued should be
291 -- coded within this procedure. As stated above, a good example is the
292 -- generation of a primary key number via a corresponding sequence.
293 -- It is important to note that any 3rd party maintenance should be reviewed
294 -- before placing in this procedure.
295 --
296 -- Access Status:
297 -- Internal Row Handler Use Only.
298 --
299 -- {End Of Comments}
300 -- ----------------------------------------------------------------------------
301 Procedure pre_insert
302 (p_rec in out nocopy pqh_crd_shd.g_rec_type
303 ) is
304 --
305 Cursor C_Sel1 is select pqh_criteria_rate_defn_s.nextval from sys.dual;
306 --
307 Cursor C_Sel2 is
308 Select null
309 from pqh_criteria_rate_defn
310 where criteria_rate_defn_id =
311 pqh_crd_ins.g_criteria_rate_defn_id_i;
312 --
313 l_proc varchar2(72) := g_package||'pre_insert';
314 l_exists varchar2(1);
315 --
316 Begin
317 hr_utility.set_location('Entering:'||l_proc, 5);
318 --
319 If (pqh_crd_ins.g_criteria_rate_defn_id_i is not null) Then
320 --
321 -- Verify registered primary key values not already in use
322 --
323 Open C_Sel2;
324 Fetch C_Sel2 into l_exists;
325 If C_Sel2%found Then
326 Close C_Sel2;
327 --
328 -- The primary key values are already in use.
329 --
330 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
331 fnd_message.set_token('TABLE_NAME','pqh_criteria_rate_defn');
332 fnd_message.raise_error;
333 End If;
334 Close C_Sel2;
335 --
336 -- Use registered key values and clear globals
337 --
338 p_rec.criteria_rate_defn_id :=
339 pqh_crd_ins.g_criteria_rate_defn_id_i;
340 pqh_crd_ins.g_criteria_rate_defn_id_i := null;
341 Else
342 --
343 -- No registerd key values, so select the next sequence number
344 --
345 --
346 -- Select the next sequence number
347 --
348 Open C_Sel1;
349 Fetch C_Sel1 Into p_rec.criteria_rate_defn_id;
350 Close C_Sel1;
351 End If;
355 --
352 --
353 hr_utility.set_location(' Leaving:'||l_proc, 10);
354 End pre_insert;
356 -- ----------------------------------------------------------------------------
357 -- |-----------------------------< post_insert >------------------------------|
358 -- ----------------------------------------------------------------------------
359 -- {Start Of Comments}
360 --
361 -- Description:
362 -- This private procedure contains any processing which is required after
363 -- the insert dml.
364 --
365 -- Prerequisites:
366 -- This is an internal procedure which is called from the ins procedure.
367 --
368 -- In Parameters:
369 -- A Pl/Sql record structre.
370 --
371 -- Post Success:
372 -- Processing continues.
373 --
374 -- Post Failure:
375 -- If an error has occurred, an error message and exception will be raised
376 -- but not handled.
377 --
378 -- Developer Implementation Notes:
379 -- Any post-processing required after the insert dml is issued should be
380 -- coded within this procedure. It is important to note that any 3rd party
381 -- maintenance should be reviewed before placing in this procedure.
382 --
383 -- Access Status:
384 -- Internal Row Handler Use Only.
385 --
386 -- {End Of Comments}
387 -- ----------------------------------------------------------------------------
388 Procedure post_insert
389 (p_effective_date in date
390 ,p_rec in pqh_crd_shd.g_rec_type
391 ) is
392 --
393 l_proc varchar2(72) := g_package||'post_insert';
394 --
395 Begin
396 hr_utility.set_location('Entering:'||l_proc, 5);
397 begin
398 --
399 -- insert ownerships if applicable
400 create_app_ownerships
401 ('CRITERIA_RATE_DEFN_ID', p_rec.criteria_rate_defn_id
402 );
403 --
404 --
405 pqh_crd_rki.after_insert
406 (p_effective_date => p_effective_date
407 ,p_criteria_rate_defn_id
408 => p_rec.criteria_rate_defn_id
409 ,p_short_name
410 => p_rec.short_name
411 ,p_uom
412 => p_rec.uom
413 ,p_currency_code
414 => p_rec.currency_code
415 ,p_reference_period_cd
416 => p_rec.reference_period_cd
417 ,p_define_max_rate_flag
418 => p_rec.define_max_rate_flag
419 ,p_define_min_rate_flag
420 => p_rec.define_min_rate_flag
421 ,p_define_mid_rate_flag
422 => p_rec.define_mid_rate_flag
423 ,p_define_std_rate_flag
424 => p_rec.define_std_rate_flag
425 ,p_rate_calc_cd
426 => p_rec.rate_calc_cd
427 ,p_rate_calc_rule
428 => p_rec.rate_calc_rule
429 ,p_preferential_rate_cd
430 => p_rec.preferential_rate_cd
431 ,p_preferential_rate_rule
432 => p_rec.preferential_rate_rule
433 ,p_rounding_cd
434 => p_rec.rounding_cd
435 ,p_rounding_rule
436 => p_rec.rounding_rule
437 ,p_business_group_id
438 => p_rec.business_group_id
439 ,p_legislation_code
440 => p_rec.legislation_code
441 ,p_attribute_category
442 => p_rec.attribute_category
443 ,p_attribute1
444 => p_rec.attribute1
445 ,p_attribute2
446 => p_rec.attribute2
447 ,p_attribute3
448 => p_rec.attribute3
449 ,p_attribute4
450 => p_rec.attribute4
451 ,p_attribute5
452 => p_rec.attribute5
453 ,p_attribute6
454 => p_rec.attribute6
455 ,p_attribute7
456 => p_rec.attribute7
457 ,p_attribute8
458 => p_rec.attribute8
459 ,p_attribute9
460 => p_rec.attribute9
461 ,p_attribute10
462 => p_rec.attribute10
463 ,p_attribute11
464 => p_rec.attribute11
465 ,p_attribute12
466 => p_rec.attribute12
467 ,p_attribute13
468 => p_rec.attribute13
469 ,p_attribute14
470 => p_rec.attribute14
471 ,p_attribute15
472 => p_rec.attribute15
473 ,p_attribute16
474 => p_rec.attribute16
475 ,p_attribute17
476 => p_rec.attribute17
477 ,p_attribute18
478 => p_rec.attribute18
479 ,p_attribute19
480 => p_rec.attribute19
481 ,p_attribute20
482 => p_rec.attribute20
483 ,p_attribute21
484 => p_rec.attribute21
485 ,p_attribute22
486 => p_rec.attribute22
487 ,p_attribute23
488 => p_rec.attribute23
489 ,p_attribute24
490 => p_rec.attribute24
491 ,p_attribute25
492 => p_rec.attribute25
493 ,p_attribute26
494 => p_rec.attribute26
495 ,p_attribute27
496 => p_rec.attribute27
497 ,p_attribute28
498 => p_rec.attribute28
499 ,p_attribute29
500 => p_rec.attribute29
501 ,p_attribute30
502 => p_rec.attribute30
503 ,p_object_version_number
504 => p_rec.object_version_number
505 );
506 --
507 exception
508 --
509 when hr_api.cannot_find_prog_unit then
510 --
511 hr_api.cannot_find_prog_unit_error
512 (p_module_name => 'PQH_CRITERIA_RATE_DEFN'
513 ,p_hook_type => 'AI');
514 --
515 end;
516 --
517 hr_utility.set_location(' Leaving:'||l_proc, 10);
518 End post_insert;
519 --
523 Procedure ins
520 -- ----------------------------------------------------------------------------
521 -- |---------------------------------< ins >----------------------------------|
522 -- ----------------------------------------------------------------------------
524 (p_effective_date in date
525 ,p_rec in out nocopy pqh_crd_shd.g_rec_type
526 ) is
527 --
528 l_proc varchar2(72) := g_package||'ins';
529 --
530 Begin
531 hr_utility.set_location('Entering:'||l_proc, 5);
532 --
533 -- Call the supporting insert validate operations
534 --
535 pqh_crd_bus.insert_validate
536 (p_effective_date
537 ,p_rec
538 );
539 --
540 -- Call to raise any errors on multi-message list
541 hr_multi_message.end_validation_set;
542 --
543 -- Call the supporting pre-insert operation
544 --
545 pqh_crd_ins.pre_insert(p_rec);
546 --
547 -- Insert the row
548 --
549 pqh_crd_ins.insert_dml(p_rec);
550 --
551 -- Call the supporting post-insert operation
552 --
553 pqh_crd_ins.post_insert
554 (p_effective_date
555 ,p_rec
556 );
557 --
558 -- Call to raise any errors on multi-message list
559 hr_multi_message.end_validation_set;
560 --
561 hr_utility.set_location('Leaving:'||l_proc, 20);
562 end ins;
563 --
564 -- ----------------------------------------------------------------------------
565 -- |---------------------------------< ins >----------------------------------|
566 -- ----------------------------------------------------------------------------
567 Procedure ins
568 (p_effective_date in date
569 ,p_uom in varchar2
570 ,p_rate_calc_cd in varchar2
571 ,p_preferential_rate_cd in varchar2
572 ,p_rounding_cd in varchar2
573 ,p_short_name in varchar2 default null
574 ,p_currency_code in varchar2 default null
575 ,p_reference_period_cd in varchar2 default null
576 ,p_define_max_rate_flag in varchar2 default null
577 ,p_define_min_rate_flag in varchar2 default null
578 ,p_define_mid_rate_flag in varchar2 default null
579 ,p_define_std_rate_flag in varchar2 default null
580 ,p_rate_calc_rule in number default null
581 ,p_preferential_rate_rule in number default null
582 ,p_rounding_rule in number default null
583 ,p_business_group_id in number default null
584 ,p_legislation_code in varchar2 default null
585 ,p_attribute_category in varchar2 default null
586 ,p_attribute1 in varchar2 default null
587 ,p_attribute2 in varchar2 default null
588 ,p_attribute3 in varchar2 default null
589 ,p_attribute4 in varchar2 default null
590 ,p_attribute5 in varchar2 default null
591 ,p_attribute6 in varchar2 default null
592 ,p_attribute7 in varchar2 default null
593 ,p_attribute8 in varchar2 default null
594 ,p_attribute9 in varchar2 default null
595 ,p_attribute10 in varchar2 default null
596 ,p_attribute11 in varchar2 default null
597 ,p_attribute12 in varchar2 default null
598 ,p_attribute13 in varchar2 default null
599 ,p_attribute14 in varchar2 default null
600 ,p_attribute15 in varchar2 default null
601 ,p_attribute16 in varchar2 default null
602 ,p_attribute17 in varchar2 default null
603 ,p_attribute18 in varchar2 default null
604 ,p_attribute19 in varchar2 default null
605 ,p_attribute20 in varchar2 default null
606 ,p_attribute21 in varchar2 default null
607 ,p_attribute22 in varchar2 default null
608 ,p_attribute23 in varchar2 default null
609 ,p_attribute24 in varchar2 default null
610 ,p_attribute25 in varchar2 default null
611 ,p_attribute26 in varchar2 default null
612 ,p_attribute27 in varchar2 default null
613 ,p_attribute28 in varchar2 default null
614 ,p_attribute29 in varchar2 default null
615 ,p_attribute30 in varchar2 default null
616 ,p_criteria_rate_defn_id out nocopy number
617 ,p_object_version_number out nocopy number
618 ) is
619 --
620 l_rec pqh_crd_shd.g_rec_type;
621 l_proc varchar2(72) := g_package||'ins';
622 --
623 Begin
624 hr_utility.set_location('Entering:'||l_proc, 5);
625 --
626 -- Call conversion function to turn arguments into the
627 -- p_rec structure.
628 --
629 l_rec :=
630 pqh_crd_shd.convert_args
631 (null
632 ,p_short_name
633 ,p_uom
634 ,p_currency_code
635 ,p_reference_period_cd
636 ,p_define_max_rate_flag
637 ,p_define_min_rate_flag
638 ,p_define_mid_rate_flag
639 ,p_define_std_rate_flag
640 ,p_rate_calc_cd
641 ,p_rate_calc_rule
642 ,p_preferential_rate_cd
643 ,p_preferential_rate_rule
644 ,p_rounding_cd
645 ,p_rounding_rule
646 ,p_business_group_id
647 ,p_legislation_code
648 ,p_attribute_category
649 ,p_attribute1
650 ,p_attribute2
651 ,p_attribute3
652 ,p_attribute4
653 ,p_attribute5
654 ,p_attribute6
655 ,p_attribute7
656 ,p_attribute8
657 ,p_attribute9
658 ,p_attribute10
659 ,p_attribute11
660 ,p_attribute12
661 ,p_attribute13
662 ,p_attribute14
663 ,p_attribute15
664 ,p_attribute16
665 ,p_attribute17
666 ,p_attribute18
667 ,p_attribute19
668 ,p_attribute20
669 ,p_attribute21
670 ,p_attribute22
671 ,p_attribute23
672 ,p_attribute24
673 ,p_attribute25
674 ,p_attribute26
675 ,p_attribute27
676 ,p_attribute28
677 ,p_attribute29
678 ,p_attribute30
679 ,null
680 );
681 --
682 -- Having converted the arguments into the pqh_crd_rec
683 -- plsql record structure we call the corresponding record business process.
684 --
685 pqh_crd_ins.ins
686 (p_effective_date
687 ,l_rec
688 );
689 --
690 -- As the primary key argument(s)
691 -- are specified as an OUT's we must set these values.
692 --
693 p_criteria_rate_defn_id := l_rec.criteria_rate_defn_id;
694 p_object_version_number := l_rec.object_version_number;
695 --
696 hr_utility.set_location(' Leaving:'||l_proc, 10);
697 End ins;
698 --
699 end pqh_crd_ins;