[Home] [Help]
PACKAGE BODY: APPS.PQP_PTY_INS
Source
1 Package Body pqp_pty_ins as
2 /* $Header: pqptyrhi.pkb 120.0.12000000.1 2007/01/16 04:29:01 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_pty_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_pension_type_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_pension_type_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 pqp_pty_ins.g_pension_type_id_i := p_pension_type_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |----------------------------< dt_insert_dml >-----------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic for datetrack. The
39 -- functions of this procedure are as follows:
40 -- 1) Get the object_version_number.
41 -- 2) To set the effective start and end dates to the corresponding
42 -- validation start and end dates. Also, the object version number
43 -- record attribute is set.
44 -- 3) To set and unset the g_api_dml status as required (as we are about to
45 -- perform dml).
46 -- 4) To insert the row into the schema with the derived effective start
47 -- and end dates and the object version number.
48 -- 5) To trap any constraint violations that may have occurred.
49 -- 6) To raise any other errors.
50 --
51 -- Prerequisites:
52 -- This is an internal private procedure which must be called from the
53 -- insert_dml and pre_update (logic permitting) procedure and must have
54 -- all mandatory arguments set.
55 --
56 -- In Parameters:
57 -- A Pl/Sql record structure.
58 --
59 -- Post Success:
60 -- The specified row will be inserted into the schema.
61 --
62 -- Post Failure:
63 -- On the insert dml failure it is important to note that we always reset the
64 -- g_api_dml status to false.
65 -- If a check or unique integrity constraint violation is raised the
66 -- constraint_error procedure will be called.
67 -- If any other error is reported, the error will be raised after the
68 -- g_api_dml status is reset.
69 --
70 -- Developer Implementation Notes:
71 -- This is an internal datetrack maintenance procedure which should
72 -- not be modified in anyway.
73 --
74 -- Access Status:
75 -- Internal Row Handler Use Only.
76 --
77 -- {End Of Comments}
78 -- ----------------------------------------------------------------------------
79 Procedure dt_insert_dml
80 (p_rec in out nocopy pqp_pty_shd.g_rec_type
81 ,p_effective_date in date
82 ,p_datetrack_mode in varchar2
83 ,p_validation_start_date in date
84 ,p_validation_end_date in date
85 ) is
86 -- Cursor to select 'old' created AOL who column values
87 --
88 Cursor C_Sel1 Is
89 select t.created_by,
90 t.creation_date
91 from pqp_pension_types_f t
92 where t.pension_type_id = p_rec.pension_type_id
93 and t.effective_start_date =
94 pqp_pty_shd.g_old_rec.effective_start_date
95 and t.effective_end_date = (p_validation_start_date - 1);
96 --
97 l_proc varchar2(72) := g_package||'dt_insert_dml';
98 l_created_by pqp_pension_types_f.created_by%TYPE;
99 l_creation_date pqp_pension_types_f.creation_date%TYPE;
100 l_last_update_date pqp_pension_types_f.last_update_date%TYPE;
101 l_last_updated_by pqp_pension_types_f.last_updated_by%TYPE;
102 l_last_update_login pqp_pension_types_f.last_update_login%TYPE;
103 --
104 Begin
105 hr_utility.set_location('Entering:'||l_proc, 5);
106 --
107 -- Get the object version number for the insert
108 --
109 p_rec.object_version_number :=
110 dt_api.get_object_version_number
111 (p_base_table_name => 'pqp_pension_types_f'
115 --
112 ,p_base_key_column => 'pension_type_id'
113 ,p_base_key_value => p_rec.pension_type_id
114 );
116 -- Set the effective start and end dates to the corresponding
117 -- validation start and end dates
118 --
119 p_rec.effective_start_date := p_validation_start_date;
120 p_rec.effective_end_date := p_validation_end_date;
121 --
122 -- If the datetrack_mode is not INSERT then we must populate the WHO
123 -- columns with the 'old' creation values and 'new' updated values.
124 --
125 If (p_datetrack_mode <> hr_api.g_insert) then
126 hr_utility.set_location(l_proc, 10);
127 --
128 -- Select the 'old' created values
129 --
130 Open C_Sel1;
131 Fetch C_Sel1 Into l_created_by, l_creation_date;
132 If C_Sel1%notfound Then
133 --
134 -- The previous 'old' created row has not been found. We need
135 -- to error as an internal datetrack problem exists.
136 --
137 Close C_Sel1;
138 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
139 fnd_message.set_token('PROCEDURE', l_proc);
140 fnd_message.set_token('STEP','10');
141 fnd_message.raise_error;
142 End If;
143 Close C_Sel1;
144 --
145 -- Set the AOL updated WHO values
146 --
147 l_last_update_date := sysdate;
148 l_last_updated_by := fnd_global.user_id;
149 l_last_update_login := fnd_global.login_id;
150 End If;
151 --
152 --
153 --
154 -- Insert the row into: pqp_pension_types_f
155 --
156 insert into pqp_pension_types_f
157 (pension_type_id
158 ,effective_start_date
159 ,effective_end_date
160 ,pension_type_name
161 ,pension_category
162 ,pension_provider_type
163 ,salary_calculation_method
164 ,threshold_conversion_rule
165 ,contribution_conversion_rule
166 ,er_annual_limit
167 ,ee_annual_limit
168 ,er_annual_salary_threshold
169 ,ee_annual_salary_threshold
170 ,object_version_number
171 ,business_group_id
172 ,legislation_code
173 ,description
174 ,minimum_age
175 ,ee_contribution_percent
176 ,maximum_age
177 ,er_contribution_percent
178 ,ee_annual_contribution
179 ,er_annual_contribution
180 ,annual_premium_amount
181 ,ee_contribution_bal_type_id
182 ,er_contribution_bal_type_id
183 ,balance_init_element_type_id
184 ,ee_contribution_fixed_rate -- added for UK
185 ,er_contribution_fixed_rate -- added for UK
186 ,pty_attribute_category
187 ,pty_attribute1
188 ,pty_attribute2
189 ,pty_attribute3
190 ,pty_attribute4
191 ,pty_attribute5
192 ,pty_attribute6
193 ,pty_attribute7
194 ,pty_attribute8
195 ,pty_attribute9
196 ,pty_attribute10
197 ,pty_attribute11
198 ,pty_attribute12
199 ,pty_attribute13
200 ,pty_attribute14
201 ,pty_attribute15
202 ,pty_attribute16
203 ,pty_attribute17
204 ,pty_attribute18
205 ,pty_attribute19
206 ,pty_attribute20
207 ,pty_information_category
208 ,pty_information1
209 ,pty_information2
210 ,pty_information3
211 ,pty_information4
212 ,pty_information5
213 ,pty_information6
214 ,pty_information7
215 ,pty_information8
216 ,pty_information9
217 ,pty_information10
218 ,pty_information11
219 ,pty_information12
220 ,pty_information13
221 ,pty_information14
222 ,pty_information15
223 ,pty_information16
224 ,pty_information17
225 ,pty_information18
226 ,pty_information19
227 ,pty_information20
228 ,special_pension_type_code -- added for NL Phase 2B
229 ,pension_sub_category -- added for NL Phase 2B
230 ,pension_basis_calc_method -- added for NL Phase 2B
231 ,pension_salary_balance -- added for NL Phase 2B
232 ,recurring_bonus_percent -- added for NL Phase 2B
233 ,non_recurring_bonus_percent -- added for NL Phase 2B
234 ,recurring_bonus_balance -- added for NL Phase 2B
235 ,non_recurring_bonus_balance -- added for NL Phase 2B
236 ,std_tax_reduction -- added for NL Phase 2B
237 ,spl_tax_reduction -- added for NL Phase 2B
238 ,sig_sal_spl_tax_reduction -- added for NL Phase 2B
239 ,sig_sal_non_tax_reduction -- added for NL Phase 2B
240 ,sig_sal_std_tax_reduction -- added for NL Phase 2B
241 ,sii_std_tax_reduction -- added for NL Phase 2B
242 ,sii_spl_tax_reduction -- added for NL Phase 2B
243 ,sii_non_tax_reduction -- added for NL Phase 2B
244 ,previous_year_bonus_included -- added for NL Phase 2B
245 ,recurring_bonus_period -- added for NL Phase 2B
246 ,non_recurring_bonus_period -- added for NL Phase 2B
247 ,ee_age_threshold -- added for ABP TAR fixes
248 ,er_age_threshold -- added for ABP TAR fixes
249 ,ee_age_contribution -- added for ABP TAR fixes
250 ,er_age_contribution -- added for ABP TAR fixes
251 ,created_by
252 ,creation_date
253 ,last_update_date
254 ,last_updated_by
255 ,last_update_login
256 )
257 Values
258 (p_rec.pension_type_id
262 ,p_rec.pension_category
259 ,p_rec.effective_start_date
260 ,p_rec.effective_end_date
261 ,p_rec.pension_type_name
263 ,p_rec.pension_provider_type
264 ,p_rec.salary_calculation_method
265 ,p_rec.threshold_conversion_rule
266 ,p_rec.contribution_conversion_rule
267 ,p_rec.er_annual_limit
268 ,p_rec.ee_annual_limit
269 ,p_rec.er_annual_salary_threshold
270 ,p_rec.ee_annual_salary_threshold
271 ,p_rec.object_version_number
272 ,p_rec.business_group_id
273 ,p_rec.legislation_code
274 ,p_rec.description
275 ,p_rec.minimum_age
276 ,p_rec.ee_contribution_percent
277 ,p_rec.maximum_age
278 ,p_rec.er_contribution_percent
279 ,p_rec.ee_annual_contribution
280 ,p_rec.er_annual_contribution
281 ,p_rec.annual_premium_amount
282 ,p_rec.ee_contribution_bal_type_id
283 ,p_rec.er_contribution_bal_type_id
284 ,p_rec.balance_init_element_type_id
285 ,p_rec.ee_contribution_fixed_rate -- added for UK
286 ,p_rec.er_contribution_fixed_rate -- added for UK
287 ,p_rec.pty_attribute_category
288 ,p_rec.pty_attribute1
289 ,p_rec.pty_attribute2
290 ,p_rec.pty_attribute3
291 ,p_rec.pty_attribute4
292 ,p_rec.pty_attribute5
293 ,p_rec.pty_attribute6
294 ,p_rec.pty_attribute7
295 ,p_rec.pty_attribute8
296 ,p_rec.pty_attribute9
297 ,p_rec.pty_attribute10
298 ,p_rec.pty_attribute11
299 ,p_rec.pty_attribute12
300 ,p_rec.pty_attribute13
301 ,p_rec.pty_attribute14
302 ,p_rec.pty_attribute15
303 ,p_rec.pty_attribute16
304 ,p_rec.pty_attribute17
305 ,p_rec.pty_attribute18
306 ,p_rec.pty_attribute19
307 ,p_rec.pty_attribute20
308 ,p_rec.pty_information_category
309 ,p_rec.pty_information1
310 ,p_rec.pty_information2
311 ,p_rec.pty_information3
312 ,p_rec.pty_information4
313 ,p_rec.pty_information5
314 ,p_rec.pty_information6
315 ,p_rec.pty_information7
316 ,p_rec.pty_information8
317 ,p_rec.pty_information9
318 ,p_rec.pty_information10
319 ,p_rec.pty_information11
320 ,p_rec.pty_information12
321 ,p_rec.pty_information13
322 ,p_rec.pty_information14
323 ,p_rec.pty_information15
324 ,p_rec.pty_information16
325 ,p_rec.pty_information17
326 ,p_rec.pty_information18
327 ,p_rec.pty_information19
328 ,p_rec.pty_information20
329 ,p_rec.special_pension_type_code -- added for NL Phase 2B
330 ,p_rec.pension_sub_category -- added for NL Phase 2B
331 ,p_rec.pension_basis_calc_method -- added for NL Phase 2B
332 ,p_rec.pension_salary_balance -- added for NL Phase 2B
333 ,p_rec.recurring_bonus_percent -- added for NL Phase 2B
334 ,p_rec.non_recurring_bonus_percent -- added for NL Phase 2B
335 ,p_rec.recurring_bonus_balance -- added for NL Phase 2B
336 ,p_rec.non_recurring_bonus_balance -- added for NL Phase 2B
337 ,p_rec.std_tax_reduction -- added for NL Phase 2B
338 ,p_rec.spl_tax_reduction -- added for NL Phase 2B
339 ,p_rec.sig_sal_spl_tax_reduction -- added for NL Phase 2B
340 ,p_rec.sig_sal_non_tax_reduction -- added for NL Phase 2B
341 ,p_rec.sig_sal_std_tax_reduction -- added for NL Phase 2B
342 ,p_rec.sii_std_tax_reduction -- added for NL Phase 2B
343 ,p_rec.sii_spl_tax_reduction -- added for NL Phase 2B
344 ,p_rec.sii_non_tax_reduction -- added for NL Phase 2B
345 ,p_rec.previous_year_bonus_included -- added for NL Phase 2B
346 ,p_rec.recurring_bonus_period -- added for NL Phase 2B
347 ,p_rec.non_recurring_bonus_period -- added for NL Phase 2B
348 ,p_rec.ee_age_threshold -- added for ABP TAR fixes
349 ,p_rec.er_age_threshold -- added for ABP TAR fixes
350 ,p_rec.ee_age_contribution -- added for ABP TAR fixes
351 ,p_rec.er_age_contribution -- added for ABP TAR fixes
352 ,l_created_by
353 ,l_creation_date
354 ,l_last_update_date
355 ,l_last_updated_by
356 ,l_last_update_login
357 );
358 --
359 --
360 hr_utility.set_location(' Leaving:'||l_proc, 15);
361 --
362 Exception
363 When hr_api.check_integrity_violated Then
364 -- A check constraint has been violated
365 --
366 pqp_pty_shd.constraint_error
367 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
368 When hr_api.unique_integrity_violated Then
369 -- Unique integrity has been violated
370 --
371 pqp_pty_shd.constraint_error
372 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
373 When Others Then
374 --
375 Raise;
376 End dt_insert_dml;
377 --
378 -- ----------------------------------------------------------------------------
379 -- |-----------------------< create_app_ownerships >--------------------------|
380 -- ----------------------------------------------------------------------------
381 --
382 -- Description:
383 -- This procedure inserts a row into the HR_APPLICATION_OWNERSHIPS table
384 -- when the row handler is called in the appropriate mode.
385 --
386 -- ----------------------------------------------------------------------------
387 PROCEDURE create_app_ownerships(p_pk_column IN varchar2
388 ,p_pk_value IN varchar2) IS
389 --
390 CURSOR csr_definition IS
391 SELECT product_short_name
392 FROM hr_owner_definitions
393 WHERE session_id = hr_startup_data_api_support.g_session_id;
397 IF (hr_startup_data_api_support.return_startup_mode IN
394 --
395 BEGIN
396 --
398 ('STARTUP','GENERIC')) THEN
399 --
400 FOR c1 IN csr_definition LOOP
401 --
402 INSERT INTO hr_application_ownerships
403 (key_name
404 ,key_value
405 ,product_name
406 )
407 VALUES
408 (p_pk_column
409 ,fnd_number.number_to_canonical(p_pk_value)
410 ,c1.product_short_name
411 );
412 END LOOP;
413 END IF;
414 END create_app_ownerships;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |-----------------------< create_app_ownerships >--------------------------|
418 -- ----------------------------------------------------------------------------
419 PROCEDURE create_app_ownerships(p_pk_column IN varchar2
420 ,p_pk_value IN number) IS
421 --
422 BEGIN
423 create_app_ownerships(p_pk_column, to_char(p_pk_value));
424 END create_app_ownerships;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |------------------------------< insert_dml >------------------------------|
428 -- ----------------------------------------------------------------------------
429 Procedure insert_dml
430 (p_rec in out nocopy pqp_pty_shd.g_rec_type
431 ,p_effective_date in date
432 ,p_datetrack_mode in varchar2
433 ,p_validation_start_date in date
434 ,p_validation_end_date in date
435 ) is
436 --
437 l_proc varchar2(72) := g_package||'insert_dml';
438 --
439 Begin
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 pqp_pty_ins.dt_insert_dml
443 (p_rec => p_rec
444 ,p_effective_date => p_effective_date
445 ,p_datetrack_mode => p_datetrack_mode
446 ,p_validation_start_date => p_validation_start_date
447 ,p_validation_end_date => p_validation_end_date
448 );
449 --
450 hr_utility.set_location(' Leaving:'||l_proc, 10);
451 End insert_dml;
452 --
453 -- ----------------------------------------------------------------------------
454 -- |------------------------------< pre_insert >------------------------------|
455 -- ----------------------------------------------------------------------------
456 -- {Start Of Comments}
457 --
458 -- Description:
459 -- This private procedure contains any processing which is required before
460 -- the insert dml. Presently, if the entity has a corresponding primary
461 -- key which is maintained by an associating sequence, the primary key for
462 -- the entity will be populated with the next sequence value in
463 -- preparation for the insert dml.
464 -- Also, if comments are defined for this entity, the comments insert
465 -- logic will also be called, generating a comment_id if required.
466 --
467 -- Prerequisites:
468 -- This is an internal procedure which is called from the ins procedure.
469 --
470 -- In Parameters:
471 -- A Pl/Sql record structure.
472 --
473 -- Post Success:
474 -- Processing continues.
475 --
476 -- Post Failure:
477 -- If an error has occurred, an error message and exception will be raised
478 -- but not handled.
479 --
480 -- Developer Implementation Notes:
481 -- Any pre-processing required before the insert dml is issued should be
482 -- coded within this procedure. As stated above, a good example is the
483 -- generation of a primary key number via a corresponding sequence.
484 -- It is important to note that any 3rd party maintenance should be reviewed
485 -- before placing in this procedure.
486 --
487 -- Access Status:
488 -- Internal Row Handler Use Only.
489 --
490 -- {End Of Comments}
491 -- ----------------------------------------------------------------------------
492 Procedure pre_insert
493 (p_rec in out nocopy pqp_pty_shd.g_rec_type
494 ,p_effective_date in date
495 ,p_datetrack_mode in varchar2
496 ,p_validation_start_date in date
497 ,p_validation_end_date in date
498 ) is
499 --
500 Cursor C_Sel1 is select pqp_pension_types_s.nextval from sys.dual;
501 --
502 Cursor C_Sel2 is
503 Select null
504 from pqp_pension_types_f
505 where pension_type_id =
506 pqp_pty_ins.g_pension_type_id_i;
507 --
508 l_proc varchar2(72) := g_package||'pre_insert';
509 l_exists varchar2(1);
510 --
511 Begin
512 hr_utility.set_location('Entering:'||l_proc, 5);
513 --
514 If (pqp_pty_ins.g_pension_type_id_i is not null) Then
515 --
516 -- Verify registered primary key values not already in use
517 --
518 Open C_Sel2;
519 Fetch C_Sel2 into l_exists;
520 If C_Sel2%found Then
521 Close C_Sel2;
522 --
523 -- The primary key values are already in use.
524 --
525 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
526 fnd_message.set_token('TABLE_NAME','pqp_pension_types_f');
527 fnd_message.raise_error;
528 End If;
529 Close C_Sel2;
530 --
531 -- Use registered key values and clear globals
532 --
533 p_rec.pension_type_id :=
534 pqp_pty_ins.g_pension_type_id_i;
535 pqp_pty_ins.g_pension_type_id_i := null;
536 Else
537 --
541 -- Select the next sequence number
538 -- No registerd key values, so select the next sequence number
539 --
540 --
542 --
543 Open C_Sel1;
544 Fetch C_Sel1 Into p_rec.pension_type_id;
545 Close C_Sel1;
546 End If;
547 --
548 --
549 hr_utility.set_location(' Leaving:'||l_proc, 10);
550 End pre_insert;
551 --
552 -- ----------------------------------------------------------------------------
553 -- |----------------------------< post_insert >-------------------------------|
554 -- ----------------------------------------------------------------------------
555 -- {Start Of Comments}
556 --
557 -- Description:
558 -- This private procedure contains any processing which is required after
559 -- the insert dml.
560 --
561 -- Prerequisites:
562 -- This is an internal procedure which is called from the ins procedure.
563 --
564 -- In Parameters:
565 -- A Pl/Sql record structure.
566 --
567 -- Post Success:
568 -- Processing continues.
569 --
570 -- Post Failure:
571 -- If an error has occurred, an error message and exception will be raised
572 -- but not handled.
573 --
574 -- Developer Implementation Notes:
575 -- Any post-processing required after the insert dml is issued should be
576 -- coded within this procedure. It is important to note that any 3rd party
577 -- maintenance should be reviewed before placing in this procedure.
578 --
579 -- Access Status:
580 -- Internal Row Handler Use Only.
581 --
582 -- {End Of Comments}
583 -- ----------------------------------------------------------------------------
584 Procedure post_insert
585 (p_rec in pqp_pty_shd.g_rec_type
586 ,p_effective_date in date
587 ,p_datetrack_mode in varchar2
588 ,p_validation_start_date in date
589 ,p_validation_end_date in date
590 ) is
591 --
592 l_proc varchar2(72) := g_package||'post_insert';
593 --
594 Begin
595 hr_utility.set_location('Entering:'||l_proc, 5);
596 begin
597 --
598 -- insert ownerships if applicable
599 create_app_ownerships
600 ('PENSION_TYPE_ID', p_rec.pension_type_id
601 );
602 --
603 --
604 pqp_pty_rki.after_insert
605 (p_effective_date
606 => p_effective_date
607 ,p_validation_start_date
608 => p_validation_start_date
609 ,p_validation_end_date
610 => p_validation_end_date
611 ,p_pension_type_id
612 => p_rec.pension_type_id
613 ,p_effective_start_date
614 => p_rec.effective_start_date
615 ,p_effective_end_date
616 => p_rec.effective_end_date
617 ,p_pension_type_name
618 => p_rec.pension_type_name
619 ,p_pension_category
620 => p_rec.pension_category
621 ,p_pension_provider_type
622 => p_rec.pension_provider_type
623 ,p_salary_calculation_method
624 => p_rec.salary_calculation_method
625 ,p_threshold_conversion_rule
626 => p_rec.threshold_conversion_rule
627 ,p_contribution_conversion_rule
628 => p_rec.contribution_conversion_rule
629 ,p_er_annual_limit
630 => p_rec.er_annual_limit
631 ,p_ee_annual_limit
632 => p_rec.ee_annual_limit
633 ,p_er_annual_salary_threshold
634 => p_rec.er_annual_salary_threshold
635 ,p_ee_annual_salary_threshold
636 => p_rec.ee_annual_salary_threshold
637 ,p_object_version_number
638 => p_rec.object_version_number
639 ,p_business_group_id
640 => p_rec.business_group_id
641 ,p_legislation_code
642 => p_rec.legislation_code
643 ,p_description
644 => p_rec.description
645 ,p_minimum_age
646 => p_rec.minimum_age
647 ,p_ee_contribution_percent
648 => p_rec.ee_contribution_percent
649 ,p_maximum_age
650 => p_rec.maximum_age
651 ,p_er_contribution_percent
652 => p_rec.er_contribution_percent
653 ,p_ee_annual_contribution
654 => p_rec.ee_annual_contribution
655 ,p_er_annual_contribution
656 => p_rec.er_annual_contribution
657 ,p_annual_premium_amount
658 => p_rec.annual_premium_amount
659 ,p_ee_contribution_bal_type_id
660 => p_rec.ee_contribution_bal_type_id
661 ,p_er_contribution_bal_type_id
662 => p_rec.er_contribution_bal_type_id
663 ,p_balance_init_element_type_id
664 => p_rec.balance_init_element_type_id
665 ,p_ee_contribution_fixed_rate
666 => p_rec.ee_contribution_fixed_rate
667 ,p_er_contribution_fixed_rate
668 => p_rec.er_contribution_fixed_rate
669 ,p_pty_attribute_category
670 => p_rec.pty_attribute_category
671 ,p_pty_attribute1
672 => p_rec.pty_attribute1
673 ,p_pty_attribute2
674 => p_rec.pty_attribute2
675 ,p_pty_attribute3
676 => p_rec.pty_attribute3
677 ,p_pty_attribute4
678 => p_rec.pty_attribute4
679 ,p_pty_attribute5
680 => p_rec.pty_attribute5
681 ,p_pty_attribute6
682 => p_rec.pty_attribute6
683 ,p_pty_attribute7
684 => p_rec.pty_attribute7
685 ,p_pty_attribute8
686 => p_rec.pty_attribute8
687 ,p_pty_attribute9
688 => p_rec.pty_attribute9
689 ,p_pty_attribute10
693 ,p_pty_attribute12
690 => p_rec.pty_attribute10
691 ,p_pty_attribute11
692 => p_rec.pty_attribute11
694 => p_rec.pty_attribute12
695 ,p_pty_attribute13
696 => p_rec.pty_attribute13
697 ,p_pty_attribute14
698 => p_rec.pty_attribute14
699 ,p_pty_attribute15
700 => p_rec.pty_attribute15
701 ,p_pty_attribute16
702 => p_rec.pty_attribute16
703 ,p_pty_attribute17
704 => p_rec.pty_attribute17
705 ,p_pty_attribute18
706 => p_rec.pty_attribute18
707 ,p_pty_attribute19
708 => p_rec.pty_attribute19
709 ,p_pty_attribute20
710 => p_rec.pty_attribute20
711 ,p_pty_information_category
712 => p_rec.pty_information_category
713 ,p_pty_information1
714 => p_rec.pty_information1
715 ,p_pty_information2
716 => p_rec.pty_information2
717 ,p_pty_information3
718 => p_rec.pty_information3
719 ,p_pty_information4
720 => p_rec.pty_information4
721 ,p_pty_information5
722 => p_rec.pty_information5
723 ,p_pty_information6
724 => p_rec.pty_information6
725 ,p_pty_information7
726 => p_rec.pty_information7
727 ,p_pty_information8
728 => p_rec.pty_information8
729 ,p_pty_information9
730 => p_rec.pty_information9
731 ,p_pty_information10
732 => p_rec.pty_information10
733 ,p_pty_information11
734 => p_rec.pty_information11
735 ,p_pty_information12
736 => p_rec.pty_information12
737 ,p_pty_information13
738 => p_rec.pty_information13
739 ,p_pty_information14
740 => p_rec.pty_information14
741 ,p_pty_information15
742 => p_rec.pty_information15
743 ,p_pty_information16
744 => p_rec.pty_information16
745 ,p_pty_information17
746 => p_rec.pty_information17
747 ,p_pty_information18
748 => p_rec.pty_information18
749 ,p_pty_information19
750 => p_rec.pty_information19
751 ,p_pty_information20
752 => p_rec.pty_information20
753 ,p_special_pension_type_code
754 => p_rec.special_pension_type_code
755 ,p_pension_sub_category
756 => p_rec.pension_sub_category
757 ,p_pension_basis_calc_method
758 => p_rec.pension_basis_calc_method
759 ,p_pension_salary_balance
760 => p_rec.pension_salary_balance
761 ,p_recurring_bonus_percent
762 => p_rec.recurring_bonus_percent
763 ,p_non_recurring_bonus_percent
764 => p_rec.non_recurring_bonus_percent
765 ,p_recurring_bonus_balance
766 => p_rec.recurring_bonus_balance
767 ,p_non_recurring_bonus_balance
768 => p_rec.non_recurring_bonus_balance
769 ,p_std_tax_reduction
770 => p_rec.std_tax_reduction
771 ,p_spl_tax_reduction
772 => p_rec.spl_tax_reduction
773 ,p_sig_sal_spl_tax_reduction
774 => p_rec.sig_sal_spl_tax_reduction
775 ,p_sig_sal_non_tax_reduction
776 => p_rec.sig_sal_non_tax_reduction
777 ,p_sig_sal_std_tax_reduction
778 => p_rec.sig_sal_std_tax_reduction
779 ,p_sii_std_tax_reduction
780 => p_rec.sii_std_tax_reduction
781 ,p_sii_spl_tax_reduction
782 => p_rec.sii_spl_tax_reduction
783 ,p_sii_non_tax_reduction
784 => p_rec.sii_non_tax_reduction
785 ,p_previous_year_bonus_included
786 => p_rec.previous_year_bonus_included
787 ,p_recurring_bonus_period
788 => p_rec.recurring_bonus_period
789 ,p_non_recurring_bonus_period
790 => p_rec.non_recurring_bonus_period
791 ,p_ee_age_threshold
792 => p_rec.ee_age_threshold
793 ,p_er_age_threshold
794 => p_rec.er_age_threshold
795 ,p_ee_age_contribution
796 => p_rec.ee_age_contribution
797 ,p_er_age_contribution
798 => p_rec.er_age_contribution
799 );
800 --
801 exception
802 --
803 when hr_api.cannot_find_prog_unit then
804 --
805 hr_api.cannot_find_prog_unit_error
806 (p_module_name => 'PQP_PENSION_TYPES_F'
807 ,p_hook_type => 'AI');
808 --
809 end;
810 --
811 hr_utility.set_location(' Leaving:'||l_proc, 10);
812 End post_insert;
813 --
814 -- ----------------------------------------------------------------------------
815 -- |-------------------------------< ins_lck >--------------------------------|
816 -- ----------------------------------------------------------------------------
817 -- {Start Of Comments}
818 --
819 -- Description:
820 -- The ins_lck process has one main function to perform. When inserting
821 -- a datetracked row, we must validate the DT mode.
822 --
823 -- Prerequisites:
824 -- This procedure can only be called for the datetrack mode of INSERT.
825 --
826 -- In Parameters:
827 --
828 -- Post Success:
829 -- On successful completion of the ins_lck process the parental
830 -- datetracked rows will be locked providing the p_enforce_foreign_locking
831 -- argument value is TRUE.
832 -- If the p_enforce_foreign_locking argument value is FALSE then the
833 -- parential rows are not locked.
834 --
835 -- Post Failure:
836 -- The Lck process can fail for:
840 -- For the entity to be locked the parent must exist!
837 -- 1) When attempting to lock the row the row could already be locked by
838 -- another user. This will raise the HR_Api.Object_Locked exception.
839 -- 2) When attempting to the lock the parent which doesn't exist.
841 --
842 -- Developer Implementation Notes:
843 -- None.
844 --
845 -- Access Status:
846 -- Internal Row Handler Use Only.
847 --
848 -- {End Of Comments}
849 -- ----------------------------------------------------------------------------
850 Procedure ins_lck
851 (p_effective_date in date
852 ,p_datetrack_mode in varchar2
853 ,p_rec in pqp_pty_shd.g_rec_type
854 ,p_validation_start_date out nocopy date
855 ,p_validation_end_date out nocopy date
856 ) is
857 --
858 l_proc varchar2(72) := g_package||'ins_lck';
859 l_validation_start_date date;
860 l_validation_end_date date;
861 --
862 Begin
863 hr_utility.set_location('Entering:'||l_proc, 5);
864 --
865 -- Validate the datetrack mode mode getting the validation start
866 -- and end dates for the specified datetrack operation.
867 --
868 dt_api.validate_dt_mode
869 (p_effective_date => p_effective_date
870 ,p_datetrack_mode => p_datetrack_mode
871 ,p_base_table_name => 'pqp_pension_types_f'
872 ,p_base_key_column => 'pension_type_id'
873 ,p_base_key_value => p_rec.pension_type_id
874 ,p_enforce_foreign_locking => true
875 ,p_validation_start_date => l_validation_start_date
876 ,p_validation_end_date => l_validation_end_date
877 );
878 --
879 -- Set the validation start and end date OUT arguments
880 --
881 p_validation_start_date := l_validation_start_date;
882 p_validation_end_date := l_validation_end_date;
883 --
884 hr_utility.set_location(' Leaving:'||l_proc, 10);
885 --
886 End ins_lck;
887 --
888 -- ----------------------------------------------------------------------------
889 -- |---------------------------------< ins >----------------------------------|
890 -- ----------------------------------------------------------------------------
891 Procedure ins
892 (p_effective_date in date
893 ,p_rec in out nocopy pqp_pty_shd.g_rec_type
894 ) is
895 --
896 l_proc varchar2(72) := g_package||'ins';
897 l_datetrack_mode varchar2(30) := hr_api.g_insert;
898 l_validation_start_date date;
899 l_validation_end_date date;
900 --
901 Begin
902 hr_utility.set_location('Entering:'||l_proc, 5);
903 --
904 -- Call the lock operation
905 --
906 pqp_pty_ins.ins_lck
907 (p_effective_date => p_effective_date
908 ,p_datetrack_mode => l_datetrack_mode
909 ,p_rec => p_rec
910 ,p_validation_start_date => l_validation_start_date
911 ,p_validation_end_date => l_validation_end_date
912 );
913 --
914 -- Call the supporting insert validate operations
915 --
916 pqp_pty_bus.insert_validate
917 (p_rec => p_rec
918 ,p_effective_date => p_effective_date
919 ,p_datetrack_mode => l_datetrack_mode
920 ,p_validation_start_date => l_validation_start_date
921 ,p_validation_end_date => l_validation_end_date
922 );
923 --
924 -- Call to raise any errors on multi-message list
925 hr_multi_message.end_validation_set;
926 --
927 -- Call the supporting pre-insert operation
928 --
929 pqp_pty_ins.pre_insert
930 (p_rec => p_rec
931 ,p_effective_date => p_effective_date
932 ,p_datetrack_mode => l_datetrack_mode
933 ,p_validation_start_date => l_validation_start_date
934 ,p_validation_end_date => l_validation_end_date
935 );
936 --
937 -- Insert the row
938 --
939 pqp_pty_ins.insert_dml
940 (p_rec => p_rec
941 ,p_effective_date => p_effective_date
942 ,p_datetrack_mode => l_datetrack_mode
943 ,p_validation_start_date => l_validation_start_date
944 ,p_validation_end_date => l_validation_end_date
945 );
946 --
947 -- Call the supporting post-insert operation
948 --
949 pqp_pty_ins.post_insert
950 (p_rec => p_rec
951 ,p_effective_date => p_effective_date
952 ,p_datetrack_mode => l_datetrack_mode
953 ,p_validation_start_date => l_validation_start_date
954 ,p_validation_end_date => l_validation_end_date
955 );
956 --
957 -- Call to raise any errors on multi-message list
958 hr_multi_message.end_validation_set;
959 --
960 hr_utility.set_location('Leaving:'||l_proc,10);
961 end ins;
962 --
963 -- ----------------------------------------------------------------------------
964 -- |---------------------------------< ins >----------------------------------|
965 -- ----------------------------------------------------------------------------
966 Procedure ins
967 (p_effective_date in date
968 ,p_pension_type_name in varchar2
969 ,p_pension_category in varchar2
970 ,p_pension_provider_type in varchar2
971 ,p_salary_calculation_method in varchar2
972 ,p_threshold_conversion_rule in varchar2
973 ,p_contribution_conversion_rule in varchar2
974 ,p_er_annual_limit in number
975 ,p_ee_annual_limit in number
976 ,p_er_annual_salary_threshold in number
977 ,p_ee_annual_salary_threshold in number
978 ,p_business_group_id in number default null
979 ,p_legislation_code in varchar2 default null
980 ,p_description in varchar2 default null
981 ,p_minimum_age in number default null
982 ,p_ee_contribution_percent in number default null
983 ,p_maximum_age in number default null
984 ,p_er_contribution_percent in number default null
985 ,p_ee_annual_contribution in number default null
986 ,p_er_annual_contribution in number default null
987 ,p_annual_premium_amount in number default null
988 ,p_ee_contribution_bal_type_id in number default null
989 ,p_er_contribution_bal_type_id in number default null
990 ,p_balance_init_element_type_id in number default null
991 ,p_ee_contribution_fixed_rate in number default null --added for UK
992 ,p_er_contribution_fixed_rate in number default null --added for UK
993 ,p_pty_attribute_category in varchar2 default null
994 ,p_pty_attribute1 in varchar2 default null
995 ,p_pty_attribute2 in varchar2 default null
996 ,p_pty_attribute3 in varchar2 default null
997 ,p_pty_attribute4 in varchar2 default null
998 ,p_pty_attribute5 in varchar2 default null
999 ,p_pty_attribute6 in varchar2 default null
1000 ,p_pty_attribute7 in varchar2 default null
1001 ,p_pty_attribute8 in varchar2 default null
1002 ,p_pty_attribute9 in varchar2 default null
1003 ,p_pty_attribute10 in varchar2 default null
1007 ,p_pty_attribute14 in varchar2 default null
1004 ,p_pty_attribute11 in varchar2 default null
1005 ,p_pty_attribute12 in varchar2 default null
1006 ,p_pty_attribute13 in varchar2 default null
1008 ,p_pty_attribute15 in varchar2 default null
1009 ,p_pty_attribute16 in varchar2 default null
1010 ,p_pty_attribute17 in varchar2 default null
1011 ,p_pty_attribute18 in varchar2 default null
1012 ,p_pty_attribute19 in varchar2 default null
1013 ,p_pty_attribute20 in varchar2 default null
1014 ,p_pty_information_category in varchar2 default null
1015 ,p_pty_information1 in varchar2 default null
1016 ,p_pty_information2 in varchar2 default null
1017 ,p_pty_information3 in varchar2 default null
1018 ,p_pty_information4 in varchar2 default null
1019 ,p_pty_information5 in varchar2 default null
1020 ,p_pty_information6 in varchar2 default null
1021 ,p_pty_information7 in varchar2 default null
1022 ,p_pty_information8 in varchar2 default null
1023 ,p_pty_information9 in varchar2 default null
1024 ,p_pty_information10 in varchar2 default null
1025 ,p_pty_information11 in varchar2 default null
1026 ,p_pty_information12 in varchar2 default null
1027 ,p_pty_information13 in varchar2 default null
1028 ,p_pty_information14 in varchar2 default null
1029 ,p_pty_information15 in varchar2 default null
1030 ,p_pty_information16 in varchar2 default null
1031 ,p_pty_information17 in varchar2 default null
1032 ,p_pty_information18 in varchar2 default null
1033 ,p_pty_information19 in varchar2 default null
1034 ,p_pty_information20 in varchar2 default null
1035 ,p_special_pension_type_code in varchar2 default null -- added for NL Phase 2B
1036 ,p_pension_sub_category in varchar2 default null -- added for NL Phase 2B
1037 ,p_pension_basis_calc_method in varchar2 default null -- added for NL Phase 2B
1038 ,p_pension_salary_balance in number default null -- added for NL Phase 2B
1039 ,p_recurring_bonus_percent in number default null -- added for NL Phase 2B
1040 ,p_non_recurring_bonus_percent in number default null -- added for NL Phase 2B
1041 ,p_recurring_bonus_balance in number default null -- added for NL Phase 2B
1042 ,p_non_recurring_bonus_balance in number default null -- added for NL Phase 2B
1043 ,p_std_tax_reduction in varchar2 default null -- added for NL Phase 2B
1044 ,p_spl_tax_reduction in varchar2 default null -- added for NL Phase 2B
1045 ,p_sig_sal_spl_tax_reduction in varchar2 default null -- added for NL Phase 2B
1046 ,p_sig_sal_non_tax_reduction in varchar2 default null -- added for NL Phase 2B
1047 ,p_sig_sal_std_tax_reduction in varchar2 default null -- added for NL Phase 2B
1048 ,p_sii_std_tax_reduction in varchar2 default null -- added for NL Phase 2B
1049 ,p_sii_spl_tax_reduction in varchar2 default null -- added for NL Phase 2B
1050 ,p_sii_non_tax_reduction in varchar2 default null -- added for NL Phase 2B
1051 ,p_previous_year_bonus_included in varchar2 default null -- added for NL Phase 2B
1052 ,p_recurring_bonus_period in varchar2 default null -- added for NL Phase 2B
1053 ,p_non_recurring_bonus_period in varchar2 default null -- added for NL Phase 2B
1054 ,p_ee_age_threshold in varchar2 default null -- added for ABP TAR fixes
1055 ,p_er_age_threshold in varchar2 default null -- added for ABP TAR fixes
1056 ,p_ee_age_contribution in varchar2 default null -- added for ABP TAR fixes
1057 ,p_er_age_contribution in varchar2 default null -- added for ABP TAR fixes
1058 ,p_pension_type_id out nocopy number
1059 ,p_object_version_number out nocopy number
1060 ,p_effective_start_date out nocopy date
1061 ,p_effective_end_date out nocopy date
1062 ) is
1063 --
1064 l_rec pqp_pty_shd.g_rec_type;
1065 l_proc varchar2(72) := g_package||'ins';
1066 --
1067 Begin
1068 hr_utility.set_location('Entering:'||l_proc, 5);
1069 --
1070 -- Call conversion function to turn arguments into the
1071 -- p_rec structure.
1072 --
1073 l_rec :=
1074 pqp_pty_shd.convert_args
1075 (null
1076 ,null
1077 ,null
1078 ,p_pension_type_name
1079 ,p_pension_category
1080 ,p_pension_provider_type
1081 ,p_salary_calculation_method
1082 ,p_threshold_conversion_rule
1083 ,p_contribution_conversion_rule
1084 ,p_er_annual_limit
1085 ,p_ee_annual_limit
1086 ,p_er_annual_salary_threshold
1087 ,p_ee_annual_salary_threshold
1088 ,null
1089 ,p_business_group_id
1090 ,p_legislation_code
1091 ,p_description
1092 ,p_minimum_age
1093 ,p_ee_contribution_percent
1094 ,p_maximum_age
1095 ,p_er_contribution_percent
1096 ,p_ee_annual_contribution
1097 ,p_er_annual_contribution
1098 ,p_annual_premium_amount
1099 ,p_ee_contribution_bal_type_id
1100 ,p_er_contribution_bal_type_id
1101 ,p_balance_init_element_type_id
1102 ,p_ee_contribution_fixed_rate --added for UK
1103 ,p_er_contribution_fixed_rate --added for UK
1104 ,p_pty_attribute_category
1105 ,p_pty_attribute1
1106 ,p_pty_attribute2
1107 ,p_pty_attribute3
1108 ,p_pty_attribute4
1109 ,p_pty_attribute5
1110 ,p_pty_attribute6
1111 ,p_pty_attribute7
1112 ,p_pty_attribute8
1113 ,p_pty_attribute9
1114 ,p_pty_attribute10
1115 ,p_pty_attribute11
1116 ,p_pty_attribute12
1117 ,p_pty_attribute13
1118 ,p_pty_attribute14
1119 ,p_pty_attribute15
1120 ,p_pty_attribute16
1121 ,p_pty_attribute17
1122 ,p_pty_attribute18
1123 ,p_pty_attribute19
1124 ,p_pty_attribute20
1125 ,p_pty_information_category
1126 ,p_pty_information1
1127 ,p_pty_information2
1128 ,p_pty_information3
1129 ,p_pty_information4
1130 ,p_pty_information5
1131 ,p_pty_information6
1132 ,p_pty_information7
1133 ,p_pty_information8
1134 ,p_pty_information9
1135 ,p_pty_information10
1136 ,p_pty_information11
1137 ,p_pty_information12
1138 ,p_pty_information13
1139 ,p_pty_information14
1140 ,p_pty_information15
1141 ,p_pty_information16
1142 ,p_pty_information17
1143 ,p_pty_information18
1144 ,p_pty_information19
1145 ,p_pty_information20
1146 ,p_special_pension_type_code -- added for NL Phase 2B
1147 ,p_pension_sub_category -- added for NL Phase 2B
1148 ,p_pension_basis_calc_method -- added for NL Phase 2B
1149 ,p_pension_salary_balance -- added for NL Phase 2B
1150 ,p_recurring_bonus_percent -- added for NL Phase 2B
1151 ,p_non_recurring_bonus_percent -- added for NL Phase 2B
1152 ,p_recurring_bonus_balance -- added for NL Phase 2B
1153 ,p_non_recurring_bonus_balance -- added for NL Phase 2B
1154 ,p_std_tax_reduction -- added for NL Phase 2B
1155 ,p_spl_tax_reduction -- added for NL Phase 2B
1156 ,p_sig_sal_spl_tax_reduction -- added for NL Phase 2B
1157 ,p_sig_sal_non_tax_reduction -- added for NL Phase 2B
1158 ,p_sig_sal_std_tax_reduction -- added for NL Phase 2B
1159 ,p_sii_std_tax_reduction -- added for NL Phase 2B
1160 ,p_sii_spl_tax_reduction -- added for NL Phase 2B
1161 ,p_sii_non_tax_reduction -- added for NL Phase 2B
1162 ,p_previous_year_bonus_included -- added for NL Phase 2B
1163 ,p_recurring_bonus_period -- added for NL Phase 2B
1164 ,p_non_recurring_bonus_period -- added for NL Phase 2B
1165 ,p_ee_age_threshold -- added for ABP TAR fixes
1166 ,p_er_age_threshold -- added for ABP TAR fixes
1167 ,p_ee_age_contribution -- added for ABP TAR fixes
1168 ,p_er_age_contribution -- added for ABP TAR fixes
1169 );
1170 --
1171 -- Having converted the arguments into the pqp_pty_rec
1172 -- plsql record structure we call the corresponding record
1173 -- business process.
1174 --
1175 pqp_pty_ins.ins
1176 (p_effective_date
1177 ,l_rec
1178 );
1179 --
1180 -- Set the OUT arguments.
1181 --
1182 p_pension_type_id := l_rec.pension_type_id;
1183 p_effective_start_date := l_rec.effective_start_date;
1184 p_effective_end_date := l_rec.effective_end_date;
1185 p_object_version_number := l_rec.object_version_number;
1186 --
1187 --
1188 hr_utility.set_location(' Leaving:'||l_proc, 10);
1189 End ins;
1190 --
1191
1192 end pqp_pty_ins;
1193