[Home] [Help]
PACKAGE BODY: APPS.PER_HU_PENSION_OBJECTS
Source
1 PACKAGE BODY per_hu_pension_objects AS
2 /* $Header: pehupqpp.pkb 120.0.12010000.2 2009/05/11 12:25:41 rbabla ship $ */
3 --------------------------------------------------------------------------------
4 -- create_pension_objects
5 --------------------------------------------------------------------------------
6 --
7 -- Description:
8 -- This procedure is the self-service wrapper procedure to the following API:
9 --
10 -- pay_element_extra_info_api.create_element_extra_info
11 --
12 --
13 -- Pre-requisites
14 -- All 'IN' parameters to this procedure have been appropriately derived.
15 --
16 -- Post Success:
17 -- p_return_status will return value indicating success.
18 --
19 -- Post Failure:
20 -- p_return_status will return value indication failure.
21 --
22 -- Access Status:
23 -- Internal Development use only.
24 --
25
26
27 -------------------------------------------------------------------------------
28 -- create_pension_element
29 -------------------------------------------------------------------------------
30 -- Pension Category -- goes into element extra information
31 -- Start Date -- defines the start date for all payroll objects
32 -- Pension Year Start Date -- goes into element extra information
33 -- Pension Scheme Name -- goes into element extra information
34 -- Pension Provider (id) -- goes into element extra information
35 -- Pension Type (id) -- goes into element extra information
36 -- Employee deduction method (lookup code) -- goes into element extra information
37 -- Fund reference number -- goes into element extra information
38 -- Employer reference number -- goes into element extra information
39 -- Employee supplement(lookup code) -- goes into element extra information
40 -- Employer supplement(lookup code) -- goes into element extra information
41 -- Employer deduction method(lookup code) -- goes into element extra information
42 -- Prefix -- element type base name
43 -- reporting name -- element type reporting name
44 -- description -- element type description
45 -- third party payment -- third party payment flag
46 -- termination rule -- termination rule for the element type
47 -- standard link -- standard link flag for element type.
48 --
49 --
50 -------------------------------------------------------------------------------
51 -- create_pension_element
52 -------------------------------------------------------------------------------
53 --
54 PROCEDURE create_pension_element(p_supp_element VARCHAR2
55 ,p_ee_element VARCHAR2
56 ,p_validate BOOLEAN
57 ,p_element_name VARCHAR2
58 ,p_reporting_name VARCHAR2
59 ,p_element_description VARCHAR2
60 ,p_business_group_id NUMBER
61 ,p_effective_start_date DATE
62 ,p_standard_link_flag VARCHAR2
63 ,p_post_termination_rule VARCHAR2
64 ,p_third_party_pay_only VARCHAR2
65 ,p_contribution_type VARCHAR2
66 ,p_pension_scheme_name VARCHAR2
67 ,p_pension_provider VARCHAR2
68 ,p_pension_type VARCHAR2
69 ,p_pension_category VARCHAR2
70 ,p_pension_year_start_date VARCHAR2
71 ,p_employee_deduction_method VARCHAR2
72 ,p_employer_deduction_method VARCHAR2
73 ,p_scheme_number VARCHAR2
74 ,p_employee_supplement VARCHAR2
75 ,p_employer_supplement VARCHAR2
76 ,p_employer_reference_number VARCHAR2
77 ,p_scheme_prefix VARCHAR2
78 ,p_element_type_id OUT NOCOPY NUMBER) IS
79
80 --
81 l_input_value_id pay_input_values_f.input_value_id%TYPE;
82 --
83 -- Variables for API Boolean parameters
84 l_validate boolean;
85
86 g_package varchar2(30) :='per_hu_pension_objects';
87 --
88 -- Variables for IN/OUT parameters
89 --
90 l_element_type_extra_info_id number;
91 --l_object_version_number number;
92 --
93 l_element_type_id NUMBER;
94 l_effective_start_date DATE;
95 l_effective_end_date DATE;
96 l_object_version_number NUMBER;
97 l_comment_id NUMBER;
98 l_processing_priority_warning BOOLEAN;
99
100 l_default_val_warning BOOLEAN;
101 l_min_max_warning BOOLEAN;
102 l_pay_basis_warning BOOLEAN;
103 l_formula_warning BOOLEAN;
104 l_assignment_id_warning BOOLEAN;
105 l_percent NUMBER := 0;
106 l_rate NUMBER := 0;
107 l_formula_message VARCHAR2(200);
108 l_classification_id pay_element_classifications.classification_id%TYPE;
109
110 CURSOR cur_get_classification_id IS
111 SELECT classification_id
112 FROM pay_element_classifications
113 WHERE classification_name = 'Information'
114 AND legislation_code = 'HU';
115
116 CURSOR cur_get_pt_details IS
117 SELECT decode(p_ee_element,'Y',nvl(ee_contribution_percent,0),nvl(er_contribution_percent,0))
118 ,decode(p_ee_element,'Y',nvl(ee_contribution_fixed_rate,0),nvl(er_contribution_fixed_rate,0))
119 FROM pqp_pension_types_f
120 WHERE pension_type_id = p_pension_type
121 AND p_effective_start_date BETWEEN effective_start_date
122 AND effective_end_date;
123
124 BEGIN
125 --
126 --
127 OPEN cur_get_classification_id;
128 FETCH cur_get_classification_id INTO l_classification_id;
129 CLOSE cur_get_classification_id;
130
131 OPEN cur_get_pt_details;
132 FETCH cur_get_pt_details INTO l_percent,l_rate;
133 CLOSE cur_get_pt_details;
134
135 --
136 -- Create Element for HU Pension
137
138 pay_element_types_api.create_element_type
139 (p_validate => p_validate
140 ,p_effective_date => p_effective_start_date
141 ,p_classification_id => l_classification_id
142 ,p_element_name => p_element_name
143 ,p_input_currency_code => 'EUR'
144 ,p_output_currency_code => 'EUR'
145 ,p_multiple_entries_allowed_fla => 'N'
146 ,p_processing_type => 'R'
147 ,p_business_group_id => p_business_group_id
148 ,p_reporting_name => p_reporting_name
149 ,p_description => p_element_description
150 ,p_post_termination_rule => p_post_termination_rule
151 ,p_standard_link_flag => p_standard_link_flag
152 ,p_third_party_pay_only_flag => p_third_party_pay_only
153 ,p_element_type_id => l_element_type_id
154 ,p_effective_start_date => l_effective_start_date
155 ,p_effective_end_date => l_effective_end_date
156 ,p_object_version_number => l_object_version_number
157 ,p_comment_id => l_comment_id
158 ,p_processing_priority_warning => l_processing_priority_warning);
159 --
160 pay_input_value_api.create_input_value
161 (p_validate => p_validate
162 ,p_effective_date => p_effective_start_date
163 ,p_element_type_id => l_element_type_id
164 ,p_name => 'Override Start Date'
165 ,p_uom => 'D'
166 ,p_input_value_id => l_input_value_id
167 ,p_object_version_number => l_object_version_number
168 ,p_effective_start_date => l_effective_start_date
169 ,p_effective_end_date => l_effective_end_date
170 ,p_default_val_warning => l_default_val_warning
171 ,p_min_max_warning => l_min_max_warning
172 ,p_pay_basis_warning => l_pay_basis_warning
173 ,p_formula_warning => l_formula_warning
174 ,p_assignment_id_warning => l_assignment_id_warning
175 ,p_formula_message => l_formula_message
176 );
177 --
178 pay_input_value_api.create_input_value
179 ( p_validate => p_validate
180 ,p_effective_date => p_effective_start_date
181 ,p_element_type_id => l_element_type_id
182 ,p_name => 'Reason for Joining'
183 ,p_uom => 'C'
184 ,p_input_value_id => l_input_value_id
185 ,p_lookup_type => 'HU_JOINING_REASON'
186 ,p_warning_or_error => 'E' --Added for Bug 8504813
187 ,p_object_version_number => l_object_version_number
188 ,p_effective_start_date => l_effective_start_date
189 ,p_effective_end_date => l_effective_end_date
190 ,p_default_val_warning => l_default_val_warning
191 ,p_min_max_warning => l_min_max_warning
192 ,p_pay_basis_warning => l_pay_basis_warning
193 ,p_formula_warning => l_formula_warning
194 ,p_assignment_id_warning => l_assignment_id_warning
195 ,p_formula_message => l_formula_message
196 );
197 --
198 pay_input_value_api.create_input_value
199 ( p_validate => p_validate
200 ,p_effective_date => p_effective_start_date
201 ,p_element_type_id => l_element_type_id
202 ,p_name => 'Opt Out Date'
203 ,p_uom => 'D'
204 ,p_input_value_id => l_input_value_id
205 ,p_object_version_number => l_object_version_number
206 ,p_effective_start_date => l_effective_start_date
207 ,p_effective_end_date => l_effective_end_date
208 ,p_default_val_warning => l_default_val_warning
209 ,p_min_max_warning => l_min_max_warning
210 ,p_pay_basis_warning => l_pay_basis_warning
211 ,p_formula_warning => l_formula_warning
212 ,p_assignment_id_warning => l_assignment_id_warning
213 ,p_formula_message => l_formula_message
214 );
215 --
216 IF p_contribution_type = 'PE' THEN
217 pay_input_value_api.create_input_value
218 ( p_validate => p_validate
219 ,p_effective_date => p_effective_start_date
220 ,p_element_type_id => l_element_type_id
221 ,p_name => 'Contribution Percent'
222 ,p_uom => 'N'
223 ,p_input_value_id => l_input_value_id
224 ,p_default_value => fnd_number.number_to_canonical(l_percent)
225 ,p_object_version_number => l_object_version_number
226 ,p_effective_start_date => l_effective_start_date
227 ,p_effective_end_date => l_effective_end_date
228 ,p_default_val_warning => l_default_val_warning
229 ,p_min_max_warning => l_min_max_warning
230 ,p_pay_basis_warning => l_pay_basis_warning
231 ,p_formula_warning => l_formula_warning
232 ,p_assignment_id_warning => l_assignment_id_warning
233 ,p_formula_message => l_formula_message
234 );
235 ELSIF p_contribution_type = 'FR' THEN
236 pay_input_value_api.create_input_value
237 ( p_validate => p_validate
238 ,p_effective_date => p_effective_start_date
239 ,p_element_type_id => l_element_type_id
240 ,p_name => 'Contribution Rate'
241 ,p_uom => 'N'
242 ,p_input_value_id => l_input_value_id
243 ,p_default_value => fnd_number.number_to_canonical(l_rate)
244 ,p_object_version_number => l_object_version_number
245 ,p_effective_start_date => l_effective_start_date
246 ,p_effective_end_date => l_effective_end_date
247 ,p_default_val_warning => l_default_val_warning
248 ,p_min_max_warning => l_min_max_warning
249 ,p_pay_basis_warning => l_pay_basis_warning
250 ,p_formula_warning => l_formula_warning
251 ,p_assignment_id_warning => l_assignment_id_warning
252 ,p_formula_message => l_formula_message
253 );
254 ELSIF p_contribution_type = 'PEFR' THEN
255 pay_input_value_api.create_input_value
256 ( p_validate => p_validate
257 ,p_effective_date => p_effective_start_date
258 ,p_element_type_id => l_element_type_id
259 ,p_name => 'Contribution Percent'
260 ,p_uom => 'N'
261 ,p_input_value_id => l_input_value_id
262 ,p_default_value => fnd_number.number_to_canonical(l_percent)
263 ,p_object_version_number => l_object_version_number
264 ,p_effective_start_date => l_effective_start_date
265 ,p_effective_end_date => l_effective_end_date
266 ,p_default_val_warning => l_default_val_warning
267 ,p_min_max_warning => l_min_max_warning
268 ,p_pay_basis_warning => l_pay_basis_warning
269 ,p_formula_warning => l_formula_warning
270 ,p_assignment_id_warning => l_assignment_id_warning
271 ,p_formula_message => l_formula_message
272 );
273 pay_input_value_api.create_input_value
274 ( p_validate => p_validate
275 ,p_effective_date => p_effective_start_date
276 ,p_element_type_id => l_element_type_id
277 ,p_name => 'Contribution Rate'
278 ,p_uom => 'N'
279 ,p_input_value_id => l_input_value_id
280 ,p_default_value => fnd_number.number_to_canonical(l_rate)
281 ,p_object_version_number => l_object_version_number
282 ,p_effective_start_date => l_effective_start_date
283 ,p_effective_end_date => l_effective_end_date
284 ,p_default_val_warning => l_default_val_warning
285 ,p_min_max_warning => l_min_max_warning
286 ,p_pay_basis_warning => l_pay_basis_warning
287 ,p_formula_warning => l_formula_warning
288 ,p_assignment_id_warning => l_assignment_id_warning
289 ,p_formula_message => l_formula_message
290 );
291 END IF;
292 --
293 pay_input_value_api.create_input_value
294 ( p_validate => p_validate
295 ,p_effective_date => p_effective_start_date
296 ,p_element_type_id => l_element_type_id
297 ,p_name => 'Personal Membership Code'
298 ,p_uom => 'C'
299 ,p_input_value_id => l_input_value_id
300 ,p_object_version_number => l_object_version_number
301 ,p_effective_start_date => l_effective_start_date
302 ,p_effective_end_date => l_effective_end_date
303 ,p_default_val_warning => l_default_val_warning
304 ,p_min_max_warning => l_min_max_warning
305 ,p_pay_basis_warning => l_pay_basis_warning
306 ,p_formula_warning => l_formula_warning
307 ,p_assignment_id_warning => l_assignment_id_warning
308 ,p_formula_message => l_formula_message
309 );
310
311 --only if the element being created is not a supplementary element
312 -- then create element extra info
313
314 IF p_supp_element = 'N' THEN
315
316 pay_element_extra_info_api.create_element_extra_info
317 (p_validate => p_validate
318 ,p_element_type_id => l_element_type_id
319 ,p_information_type => 'HU_PENSION_SCHEME_INFO'
320 ,p_eei_information_category => 'HU_PENSION_SCHEME_INFO'
321 ,p_eei_information1 => p_pension_scheme_name
322 ,p_eei_information2 => p_pension_provider
323 ,p_eei_information3 => p_pension_type
324 ,p_eei_information4 => p_pension_category
325 ,p_eei_information5 => p_pension_year_start_date
326 ,p_eei_information6 => p_employee_deduction_method
327 ,p_eei_information7 => p_employer_deduction_method
328 ,p_eei_information8 => p_scheme_number
329 ,p_eei_information9 => p_employee_supplement
330 ,p_eei_information10 => p_employer_supplement
331 ,p_eei_information11 => p_employer_reference_number
332 ,p_eei_information12 => p_scheme_prefix
333 ,p_eei_information13 => p_ee_element
334 ,p_element_type_extra_info_id => l_element_type_extra_info_id
335 ,p_object_version_number => l_object_version_number
336 );
337 END IF;
338
339 END create_pension_element;
340
341 --
342 -------------------------------------------------------------------------------
343 -- create_pension_objects
344 -------------------------------------------------------------------------------
345 --
346 PROCEDURE create_pension_objects(p_validate BOOLEAN DEFAULT FALSE
347 ,p_element_name VARCHAR2
348 ,p_reporting_name VARCHAR2
349 ,p_element_description VARCHAR2
350 ,p_business_group_id NUMBER
351 ,p_effective_start_date DATE
352 ,p_standard_link_flag VARCHAR2
353 ,p_post_termination_rule VARCHAR2
354 ,p_third_party_pay_only VARCHAR2
355 ,p_contribution_type VARCHAR2
356 ,p_pension_scheme_name VARCHAR2
357 ,p_pension_provider VARCHAR2
358 ,p_pension_type VARCHAR2
359 ,p_pension_category VARCHAR2
360 ,p_pension_year_start_date VARCHAR2
361 ,p_employee_deduction_method VARCHAR2
362 ,p_employer_deduction_method VARCHAR2
363 ,p_scheme_number VARCHAR2
364 ,p_employee_supplement VARCHAR2
365 ,p_employer_supplement VARCHAR2
366 ,p_employer_reference_number VARCHAR2
367 ,p_scheme_prefix VARCHAR2
368 ,p_element_type_id OUT NOCOPY NUMBER) IS
369
370 l_ee_supplement NUMBER;
371 l_er_supplement NUMBER;
372
373 BEGIN
374
375 --first call the create_pension_element to create the primary element
376 create_pension_element(p_supp_element => 'N'
377 ,p_ee_element => 'Y'
378 ,p_validate => p_validate
379 ,p_element_name => p_scheme_prefix || ' Employee Pension Information'
380 ,p_reporting_name => p_reporting_name
381 ,p_element_description => p_element_description
382 ,p_business_group_id => p_business_group_id
383 ,p_effective_start_date => p_effective_start_date
384 ,p_standard_link_flag => p_standard_link_flag
385 ,p_post_termination_rule => p_post_termination_rule
386 ,p_third_party_pay_only => p_third_party_pay_only
387 ,p_contribution_type => p_contribution_type
388 ,p_pension_scheme_name => p_pension_scheme_name
389 ,p_pension_provider => p_pension_provider
390 ,p_pension_type => p_pension_type
391 ,p_pension_category => p_pension_category
392 ,p_pension_year_start_date => p_pension_year_start_date
393 ,p_employee_deduction_method => p_employee_deduction_method
394 ,p_employer_deduction_method => p_employer_deduction_method
395 ,p_scheme_number => p_scheme_number
396 ,p_employee_supplement => p_employee_supplement
397 ,p_employer_supplement => p_employer_supplement
398 ,p_employer_reference_number => p_employer_reference_number
399 ,p_scheme_prefix => p_scheme_prefix
400 ,p_element_type_id => p_element_type_id);
401
402 --if an ER component exists, then create an element for the ER component
403
404 IF p_employer_deduction_method IS NOT NULL THEN
405
406 create_pension_element(p_supp_element => 'N'
407 ,p_ee_element => 'N'
408 ,p_validate => p_validate
409 ,p_element_name => p_scheme_prefix || ' Employer Pension Information'
410 ,p_reporting_name => p_reporting_name
411 ,p_element_description => p_element_description
412 ,p_business_group_id => p_business_group_id
413 ,p_effective_start_date => p_effective_start_date
414 ,p_standard_link_flag => p_standard_link_flag
415 ,p_post_termination_rule => p_post_termination_rule
416 ,p_third_party_pay_only => p_third_party_pay_only
417 ,p_contribution_type => p_employer_deduction_method
418 ,p_pension_scheme_name => p_pension_scheme_name
419 ,p_pension_provider => p_pension_provider
420 ,p_pension_type => p_pension_type
421 ,p_pension_category => p_pension_category
422 ,p_pension_year_start_date => p_pension_year_start_date
423 ,p_employee_deduction_method => p_employee_deduction_method
424 ,p_employer_deduction_method => p_employer_deduction_method
425 ,p_scheme_number => p_scheme_number
426 ,p_employee_supplement => p_employee_supplement
427 ,p_employer_supplement => p_employer_supplement
428 ,p_employer_reference_number => p_employer_reference_number
429 ,p_scheme_prefix => p_scheme_prefix
430 ,p_element_type_id => l_ee_supplement);
431
432 END IF;
433
434 --if EE/ER supplements exist then also create those elements
435
436 IF p_employee_supplement IS NOT NULL THEN
437
438 create_pension_element(p_supp_element => 'Y'
439 ,p_ee_element => 'Y'
440 ,p_validate => p_validate
441 ,p_element_name => p_scheme_prefix || ' Employee Supplement'
442 ,p_reporting_name => p_reporting_name
443 ,p_element_description => p_element_description
444 ,p_business_group_id => p_business_group_id
445 ,p_effective_start_date => p_effective_start_date
446 ,p_standard_link_flag => p_standard_link_flag
447 ,p_post_termination_rule => p_post_termination_rule
448 ,p_third_party_pay_only => p_third_party_pay_only
449 ,p_contribution_type => p_employee_supplement
450 ,p_pension_scheme_name => p_pension_scheme_name
451 ,p_pension_provider => p_pension_provider
452 ,p_pension_type => p_pension_type
453 ,p_pension_category => p_pension_category
454 ,p_pension_year_start_date => p_pension_year_start_date
455 ,p_employee_deduction_method => p_employee_deduction_method
456 ,p_employer_deduction_method => p_employer_deduction_method
457 ,p_scheme_number => p_scheme_number
458 ,p_employee_supplement => p_employee_supplement
459 ,p_employer_supplement => p_employer_supplement
460 ,p_employer_reference_number => p_employer_reference_number
461 ,p_scheme_prefix => p_scheme_prefix
462 ,p_element_type_id => l_ee_supplement);
463
464 END IF;
465
466 IF p_employer_supplement IS NOT NULL THEN
467
468 create_pension_element(p_supp_element => 'Y'
469 ,p_ee_element => 'N'
470 ,p_validate => p_validate
471 ,p_element_name => p_scheme_prefix || ' Employer Supplement'
472 ,p_reporting_name => p_reporting_name
473 ,p_element_description => p_element_description
474 ,p_business_group_id => p_business_group_id
475 ,p_effective_start_date => p_effective_start_date
476 ,p_standard_link_flag => p_standard_link_flag
477 ,p_post_termination_rule => p_post_termination_rule
478 ,p_third_party_pay_only => p_third_party_pay_only
479 ,p_contribution_type => p_employer_supplement
480 ,p_pension_scheme_name => p_pension_scheme_name
481 ,p_pension_provider => p_pension_provider
482 ,p_pension_type => p_pension_type
483 ,p_pension_category => p_pension_category
484 ,p_pension_year_start_date => p_pension_year_start_date
485 ,p_employee_deduction_method => p_employer_deduction_method
486 ,p_employer_deduction_method => p_employer_deduction_method
487 ,p_scheme_number => p_scheme_number
488 ,p_employee_supplement => p_employee_supplement
489 ,p_employer_supplement => p_employer_supplement
490 ,p_employer_reference_number => p_employer_reference_number
491 ,p_scheme_prefix => p_scheme_prefix
492 ,p_element_type_id => l_er_supplement);
493
494 END IF;
495
496 END create_pension_objects;
497
498
499 --------------------------------------------------------------------------------
500 -- create_pension_objects_swi
501 --------------------------------------------------------------------------------
502
503 PROCEDURE create_pension_objects_swi(
504 p_validate IN NUMBER DEFAULT hr_api.g_false_num
505 ,p_element_name IN VARCHAR2
506 ,p_reporting_name IN VARCHAR2
507 ,p_element_description IN VARCHAR2
508 ,p_business_group_id IN NUMBER
509 ,p_effective_start_date IN DATE
510 ,p_standard_link_flag IN VARCHAR2
511 ,p_post_termination_rule IN VARCHAR2
512 ,p_third_party_pay_only IN VARCHAR2
513 ,p_contribution_type IN VARCHAR2
514 ,p_pension_scheme_name IN VARCHAR2
515 ,p_pension_provider IN VARCHAR2
516 ,p_pension_type IN VARCHAR2
517 ,p_pension_category IN VARCHAR2
518 ,p_pension_year_start_date IN VARCHAR2
519 ,p_employee_deduction_method IN VARCHAR2
520 ,p_employer_deduction_method IN VARCHAR2
521 ,p_scheme_number IN VARCHAR2
522 ,p_employee_supplement IN VARCHAR2
523 ,p_employer_supplement IN VARCHAR2
524 ,p_employer_reference_number IN VARCHAR2
525 ,p_scheme_prefix IN VARCHAR2
526 ,p_element_type_id OUT NOCOPY NUMBER
527 ,p_return_status OUT NOCOPY VARCHAR2)
528 IS
529 -- Variables for API Boolean parameters
530 l_validate BOOLEAN;
531 --
532 g_package VARCHAR2(30) :='per_hu_pension_objects';
533 -- Variables for IN/OUT parameters
534 --
535 -- Other variables
536 l_proc VARCHAR2(72) := g_package ||'create_pension_objects_swi';
537 --
538 BEGIN
539 hr_utility.set_location(' Entering:' || l_proc,10);
540 --
541 -- Issue a savepoint
542 --
543 savepoint create_pension_objects_swi;
544 --
545 -- Initialise Multiple Message Detection
546 --
547 hr_multi_message.enable_message_list;
548 --
549 -- Remember IN OUT parameter IN values
550 --
551 --
552 -- Convert constant values to their corresponding boolean value
553 --
554 l_validate :=
555 hr_api.constant_to_boolean
556 (p_constant_value => p_validate);
557 --
558 -- Register Surrogate ID or user key values
559 --
560 -- irc_inp_ins.set_base_key_value
561 -- (p_notification_preference_id => p_notification_preference_id
562 -- );
563 --
564 -- Call API
565 --
566 per_hu_pension_objects.create_pension_objects(
567 p_validate => l_validate
568 ,p_element_name => p_element_name
569 ,p_reporting_name => p_reporting_name
570 ,p_element_description => p_element_description
571 ,p_business_group_id => p_business_group_id
572 ,p_effective_start_date => p_effective_start_date
573 ,p_standard_link_flag => p_standard_link_flag
574 ,p_post_termination_rule => p_post_termination_rule
575 ,p_third_party_pay_only => p_third_party_pay_only
576 ,p_contribution_type => p_contribution_type
577 ,p_pension_scheme_name => p_pension_scheme_name
578 ,p_pension_provider => p_pension_provider
579 ,p_pension_type => p_pension_type
580 ,p_pension_category => p_pension_category
581 ,p_pension_year_start_date => fnd_date.date_to_canonical(p_pension_year_start_date)
582 ,p_employee_deduction_method => p_employee_deduction_method
583 ,p_employer_deduction_method => p_employer_deduction_method
584 ,p_scheme_number => p_scheme_number
585 ,p_employee_supplement => p_employee_supplement
586 ,p_employer_supplement => p_employer_supplement
587 ,p_employer_reference_number => p_employer_reference_number
588 ,p_scheme_prefix => p_scheme_prefix
589 ,p_element_type_id => p_element_type_id);
590 --
591 -- Convert API warning boolean parameter values to specific
592 -- messages and add them to Multiple Message List
593 --
594 --
595 -- Convert API non-warning boolean parameter values
596 --
597 --
598 -- Derive the API return status value based on whether
599 -- messages of any type exist in the Multiple Message List.
600 -- Also disable Multiple Message Detection.
601 --
602 p_return_status := hr_multi_message.get_return_status_disable;
603 hr_utility.set_location(' Leaving:' || l_proc,20);
604 --
605 EXCEPTION
606 WHEN hr_multi_message.error_message_exist THEN
607 --
608 -- Catch the Multiple Message List exception which
609 -- indicates API processing has been aborted because
610 -- at least one error message exists in the list.
611 --
612 ROLLBACK TO create_pension_objects_swi;
613 --
614 -- Reset IN OUT paramters and set OUT parameters
615 --
616 p_element_type_id := null;
617 p_return_status := hr_multi_message.get_return_status_disable;
618 hr_utility.set_location(' Leaving:' || l_proc,30);
619 WHEN OTHERS THEN
620 --
621 -- When Multiple Message Detection is enabled catch
622 -- any Application specific or other unexpected
623 -- exceptions. Adding appropriate details to the
624 -- Multiple Message List. Otherwise re-raise
625 -- the error.
626 --
627 ROLLBACK TO create_pension_objects_swi;
628 IF hr_multi_message.unexpected_error_add(l_proc) THEN
629 hr_utility.set_location(' Leaving:' || l_proc, 40);
630 RAISE;
631 END IF;
632 --
633 -- Reset IN OUT parameters and set OUT parameters
634 --
635 p_element_type_id := null;
636 p_return_status := hr_multi_message.get_return_status_disable;
637 hr_utility.set_location(' Leaving: ' || l_proc, 50);
638 --
639
640 END create_pension_objects_swi;
641
642 --------------------------------------------------------------------------------
643 -- delete_pension_objects_swi
644 --------------------------------------------------------------------------------
645
646 PROCEDURE delete_pension_objects_swi
647 (p_validate NUMBER DEFAULT hr_api.g_false_num
648 ,p_element_type_id IN NUMBER
649 ,p_effective_date IN DATE
650 ,p_object_version_number IN NUMBER
651 ,p_return_status OUT NOCOPY VARCHAR2 ) IS
652 --
653 -- Variables for API Boolean parameters
654 l_validate boolean;
655 --
656 g_package varchar2(30) :='per_hu_pension_objects';
657 -- Variables for IN/OUT parameters
658 --
659 -- Other variables
660 l_proc varchar2(72) := g_package ||'delete_pension_objects_swi';
661 --
662 BEGIN
663 --
664 hr_utility.set_location(' Entering:' || l_proc,10);
665 --
666 -- Issue a savepoint
667 --
668 savepoint delete_pension_objects_swi;
669 --
670 -- Initialise Multiple Message Detection
671 --
672 hr_multi_message.enable_message_list;
673 --
674 -- Remember IN OUT parameter IN values
675 --
676 --
677 -- Convert constant values to their corresponding boolean value
678 --
679 l_validate :=
680 hr_api.constant_to_boolean
681 (p_constant_value => p_validate);
682 --
683 -- Register Surrogate ID or user key values
684 --
685 per_hu_pension_objects.delete_pension_objects(
686 p_validate => l_validate
687 ,p_element_type_id => p_element_type_id
688 ,p_effective_date => p_effective_date
689 ,p_object_version_number => p_object_version_number);
690
691 --
692 -- Convert API non-warning boolean parameter values
693 --
694 --
695 -- Derive the API return status value based on whether
696 -- messages of any type exist in the Multiple Message List.
697 -- Also disable Multiple Message Detection.
698 --
699 p_return_status := hr_multi_message.get_return_status_disable;
700 hr_utility.set_location(' Leaving:' || l_proc,20);
701 --
702 exception
703 when hr_multi_message.error_message_exist then
704 --
705 -- Catch the Multiple Message List exception which
706 -- indicates API processing has been aborted because
707 -- at least one error message exists in the list.
708 --
709 rollback to delete_pension_objects_swi;
710 --
711 -- Reset IN OUT paramters and set OUT parameters
712 --
713 p_return_status := hr_multi_message.get_return_status_disable;
714 hr_utility.set_location(' Leaving:' || l_proc,30);
715 when others then
716 --
717 -- When Multiple Message Detection is enabled catch
718 -- any Application specific or other unexpected
719 -- exceptions. Adding appropriate details to the
720 -- Multiple Message List. Otherwise re-raise
721 -- the error.
722 --
723 rollback to delete_pension_objects_swi;
724 if hr_multi_message.unexpected_error_add(l_proc) then
725 hr_utility.set_location(' Leaving:' || l_proc, 40);
726 raise;
727 end if;
728 --
729 -- Reset IN OUT parameters and set OUT parameters
730 --
731 p_return_status := hr_multi_message.get_return_status_disable;
732 hr_utility.set_location(' Leaving: ' || l_proc, 50);
733
734 END delete_pension_objects_swi;
735 --
736 -------------------------------------------------------------------------------
737 -- delete_pension_objects
738 -------------------------------------------------------------------------------
739
740 PROCEDURE delete_pension_objects(p_validate BOOLEAN DEFAULT FALSE
741 ,p_element_type_id NUMBER
742 ,p_effective_date DATE
743 ,p_object_version_number NUMBER
744 ) IS
745 --
746 l_element_type_id pay_element_types_f.element_type_id%TYPE;
747 l_business_group_id pay_element_types_f.business_group_id%TYPE;
748 l_object_version_number NUMBER;
749 --
750 CURSOR csr_del_element_value IS
751 SELECT business_group_id FROM pay_element_types_f
752 WHERE element_type_id=p_element_type_id
753 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
754
755 -- For Input Values
756 CURSOR csr_del_input_value(p_input_name VARCHAR2) IS
757 SELECT input_value_id,object_version_number FROM pay_input_values_f
758 WHERE name = p_input_name
759 AND element_type_id = p_element_type_id--l_element_type_id
760 AND business_group_id = l_business_group_id
761 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
762
763 -- For Extra information
764 CURSOR csr_ele_extra_info IS
765 SELECT element_type_extra_info_id,object_version_number
766 FROM pay_element_type_extra_info
767 WHERE eei_information_category = 'HU_PENSION_SCHEME_INFO'
768 AND element_type_id = p_element_type_id;--l_element_type_id;
769
770
771 TYPE v_input_values IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
772
773 l_input_values v_input_values;
774 l_ele_ovn pay_element_types_f.object_version_number%TYPE;
775 l_effective_start_date pay_input_values_f.effective_start_date%TYPE;
776 l_effective_end_date pay_input_values_f.effective_end_date%TYPE;
777 l_balance_feeds_warning boolean;
778 l_input_value_id pay_input_values_f.input_value_id%TYPE;
779 l_inp_ovn pay_input_values_f.object_version_number%TYPE;
780 l_element_extra_info_id pay_element_type_extra_info.element_type_extra_info_id%TYPE;
781 l_extra_info_ovn pay_element_type_extra_info.object_version_number%TYPE;
782 l_processing_rules_warning boolean;
783
784 BEGIN
785
786 l_input_values(1) := 'Override Start Date';
787 l_input_values(2) := 'Reason for Joining';
788 l_input_values(3) := 'Opt Out Date';
789 l_input_values(4) := 'Personal Membership Code';
790 l_input_values(5) := 'Contribution Percent';
791 l_input_values(6) := 'Contribution Amount';
792
793 --
794 -- Convert constant values to their corresponding boolean value
795 --
796
797 OPEN csr_del_element_value;
798 FETCH csr_del_element_value into l_business_group_id;
799 CLOSE csr_del_element_value;
800
801 -- For Input Values
802 FOR i IN 1..6
803 LOOP
804 OPEN csr_del_input_value(l_input_values(i));
805 FETCH csr_del_input_value INTO l_input_value_id,l_inp_ovn;
806 IF csr_del_input_value%FOUND THEN
807 pay_input_value_api.delete_input_value
808 ( p_validate => p_validate
809 ,p_effective_date => p_effective_date
810 ,p_datetrack_delete_mode => 'ZAP'
811 ,p_input_value_id => l_input_value_id
812 ,p_object_version_number => l_inp_ovn -- in/out
813 ,p_effective_start_date => l_effective_start_date -- out
814 ,p_effective_end_date => l_effective_end_date -- out
815 ,p_balance_feeds_warning => l_balance_feeds_warning -- out
816 );
817 END IF;
818 CLOSE csr_del_input_value;
819 END LOOP;
820
821 -- For Extra Information
822
823 OPEN csr_ele_extra_info;
824 FETCH csr_ele_extra_info into l_element_extra_info_id,l_extra_info_ovn;
825 CLOSE csr_ele_extra_info;
826
827 pay_element_extra_info_api.delete_element_extra_info
828 (p_validate => p_validate
829 ,p_element_type_extra_info_id => l_element_extra_info_id
830 ,p_object_version_number => l_extra_info_ovn
831 ) ;
832 --
833 l_object_version_number := p_object_version_number;
834 -- For Element Types
835
836 pay_element_types_api.delete_element_type
837 (p_validate => p_validate
838 ,p_effective_date => p_effective_date
839 ,p_datetrack_delete_mode => 'ZAP'
840 ,p_element_type_id => p_element_type_id
841 ,p_object_version_number => l_object_version_number
842 ,p_effective_start_date => l_effective_start_date
843 ,p_effective_end_date => l_effective_start_date
844 ,p_balance_feeds_warning => l_balance_feeds_warning
845 ,p_processing_rules_warning => l_processing_rules_warning
846 ) ;
847
848 END delete_pension_objects;
849 --
850 END per_hu_pension_objects;