[Home] [Help]
PACKAGE BODY: APPS.PER_HU_PENSION_OBJECTS
Source
1 PACKAGE BODY per_hu_pension_objects AS
2 /* $Header: pehupqpp.pkb 115.12 2004/03/29 08:51:02 rajkalya noship $ */
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_object_version_number => l_object_version_number
187 ,p_effective_start_date => l_effective_start_date
188 ,p_effective_end_date => l_effective_end_date
189 ,p_default_val_warning => l_default_val_warning
190 ,p_min_max_warning => l_min_max_warning
191 ,p_pay_basis_warning => l_pay_basis_warning
192 ,p_formula_warning => l_formula_warning
193 ,p_assignment_id_warning => l_assignment_id_warning
194 ,p_formula_message => l_formula_message
195 );
196 --
197 pay_input_value_api.create_input_value
198 ( p_validate => p_validate
199 ,p_effective_date => p_effective_start_date
200 ,p_element_type_id => l_element_type_id
201 ,p_name => 'Opt Out Date'
202 ,p_uom => 'D'
203 ,p_input_value_id => l_input_value_id
204 ,p_object_version_number => l_object_version_number
205 ,p_effective_start_date => l_effective_start_date
206 ,p_effective_end_date => l_effective_end_date
207 ,p_default_val_warning => l_default_val_warning
208 ,p_min_max_warning => l_min_max_warning
209 ,p_pay_basis_warning => l_pay_basis_warning
210 ,p_formula_warning => l_formula_warning
211 ,p_assignment_id_warning => l_assignment_id_warning
212 ,p_formula_message => l_formula_message
213 );
214 --
215 IF p_contribution_type = 'PE' THEN
216 pay_input_value_api.create_input_value
217 ( p_validate => p_validate
218 ,p_effective_date => p_effective_start_date
219 ,p_element_type_id => l_element_type_id
220 ,p_name => 'Contribution Percent'
221 ,p_uom => 'N'
222 ,p_input_value_id => l_input_value_id
223 ,p_default_value => fnd_number.number_to_canonical(l_percent)
224 ,p_object_version_number => l_object_version_number
225 ,p_effective_start_date => l_effective_start_date
226 ,p_effective_end_date => l_effective_end_date
227 ,p_default_val_warning => l_default_val_warning
228 ,p_min_max_warning => l_min_max_warning
229 ,p_pay_basis_warning => l_pay_basis_warning
230 ,p_formula_warning => l_formula_warning
231 ,p_assignment_id_warning => l_assignment_id_warning
232 ,p_formula_message => l_formula_message
233 );
234 ELSIF p_contribution_type = 'FR' THEN
235 pay_input_value_api.create_input_value
236 ( p_validate => p_validate
237 ,p_effective_date => p_effective_start_date
238 ,p_element_type_id => l_element_type_id
239 ,p_name => 'Contribution Rate'
240 ,p_uom => 'N'
241 ,p_input_value_id => l_input_value_id
242 ,p_default_value => fnd_number.number_to_canonical(l_rate)
243 ,p_object_version_number => l_object_version_number
244 ,p_effective_start_date => l_effective_start_date
245 ,p_effective_end_date => l_effective_end_date
246 ,p_default_val_warning => l_default_val_warning
247 ,p_min_max_warning => l_min_max_warning
248 ,p_pay_basis_warning => l_pay_basis_warning
249 ,p_formula_warning => l_formula_warning
250 ,p_assignment_id_warning => l_assignment_id_warning
251 ,p_formula_message => l_formula_message
252 );
253 ELSIF p_contribution_type = 'PEFR' THEN
254 pay_input_value_api.create_input_value
255 ( p_validate => p_validate
256 ,p_effective_date => p_effective_start_date
257 ,p_element_type_id => l_element_type_id
258 ,p_name => 'Contribution Percent'
259 ,p_uom => 'N'
260 ,p_input_value_id => l_input_value_id
261 ,p_default_value => fnd_number.number_to_canonical(l_percent)
262 ,p_object_version_number => l_object_version_number
263 ,p_effective_start_date => l_effective_start_date
264 ,p_effective_end_date => l_effective_end_date
265 ,p_default_val_warning => l_default_val_warning
266 ,p_min_max_warning => l_min_max_warning
267 ,p_pay_basis_warning => l_pay_basis_warning
268 ,p_formula_warning => l_formula_warning
269 ,p_assignment_id_warning => l_assignment_id_warning
270 ,p_formula_message => l_formula_message
271 );
272 pay_input_value_api.create_input_value
273 ( p_validate => p_validate
274 ,p_effective_date => p_effective_start_date
275 ,p_element_type_id => l_element_type_id
276 ,p_name => 'Contribution Rate'
277 ,p_uom => 'N'
278 ,p_input_value_id => l_input_value_id
279 ,p_default_value => fnd_number.number_to_canonical(l_rate)
280 ,p_object_version_number => l_object_version_number
281 ,p_effective_start_date => l_effective_start_date
282 ,p_effective_end_date => l_effective_end_date
283 ,p_default_val_warning => l_default_val_warning
284 ,p_min_max_warning => l_min_max_warning
285 ,p_pay_basis_warning => l_pay_basis_warning
286 ,p_formula_warning => l_formula_warning
287 ,p_assignment_id_warning => l_assignment_id_warning
288 ,p_formula_message => l_formula_message
289 );
290 END IF;
291 --
292 pay_input_value_api.create_input_value
293 ( p_validate => p_validate
294 ,p_effective_date => p_effective_start_date
295 ,p_element_type_id => l_element_type_id
296 ,p_name => 'Personal Membership Code'
297 ,p_uom => 'C'
298 ,p_input_value_id => l_input_value_id
299 ,p_object_version_number => l_object_version_number
300 ,p_effective_start_date => l_effective_start_date
301 ,p_effective_end_date => l_effective_end_date
302 ,p_default_val_warning => l_default_val_warning
303 ,p_min_max_warning => l_min_max_warning
304 ,p_pay_basis_warning => l_pay_basis_warning
305 ,p_formula_warning => l_formula_warning
306 ,p_assignment_id_warning => l_assignment_id_warning
307 ,p_formula_message => l_formula_message
308 );
309
310 --only if the element being created is not a supplementary element
311 -- then create element extra info
312
313 IF p_supp_element = 'N' THEN
314
315 pay_element_extra_info_api.create_element_extra_info
316 (p_validate => p_validate
317 ,p_element_type_id => l_element_type_id
318 ,p_information_type => 'HU_PENSION_SCHEME_INFO'
319 ,p_eei_information_category => 'HU_PENSION_SCHEME_INFO'
320 ,p_eei_information1 => p_pension_scheme_name
321 ,p_eei_information2 => p_pension_provider
322 ,p_eei_information3 => p_pension_type
323 ,p_eei_information4 => p_pension_category
324 ,p_eei_information5 => p_pension_year_start_date
325 ,p_eei_information6 => p_employee_deduction_method
326 ,p_eei_information7 => p_employer_deduction_method
327 ,p_eei_information8 => p_scheme_number
328 ,p_eei_information9 => p_employee_supplement
329 ,p_eei_information10 => p_employer_supplement
330 ,p_eei_information11 => p_employer_reference_number
331 ,p_eei_information12 => p_scheme_prefix
332 ,p_eei_information13 => p_ee_element
333 ,p_element_type_extra_info_id => l_element_type_extra_info_id
334 ,p_object_version_number => l_object_version_number
335 );
336 END IF;
337
338 END create_pension_element;
339
340 --
341 -------------------------------------------------------------------------------
342 -- create_pension_objects
343 -------------------------------------------------------------------------------
344 --
345 PROCEDURE create_pension_objects(p_validate BOOLEAN DEFAULT FALSE
346 ,p_element_name VARCHAR2
347 ,p_reporting_name VARCHAR2
348 ,p_element_description VARCHAR2
349 ,p_business_group_id NUMBER
350 ,p_effective_start_date DATE
351 ,p_standard_link_flag VARCHAR2
352 ,p_post_termination_rule VARCHAR2
353 ,p_third_party_pay_only VARCHAR2
354 ,p_contribution_type VARCHAR2
355 ,p_pension_scheme_name VARCHAR2
356 ,p_pension_provider VARCHAR2
357 ,p_pension_type VARCHAR2
358 ,p_pension_category VARCHAR2
359 ,p_pension_year_start_date VARCHAR2
360 ,p_employee_deduction_method VARCHAR2
361 ,p_employer_deduction_method VARCHAR2
362 ,p_scheme_number VARCHAR2
363 ,p_employee_supplement VARCHAR2
364 ,p_employer_supplement VARCHAR2
365 ,p_employer_reference_number VARCHAR2
366 ,p_scheme_prefix VARCHAR2
367 ,p_element_type_id OUT NOCOPY NUMBER) IS
368
369 l_ee_supplement NUMBER;
370 l_er_supplement NUMBER;
371
372 BEGIN
373
374 --first call the create_pension_element to create the primary element
375 create_pension_element(p_supp_element => 'N'
376 ,p_ee_element => 'Y'
377 ,p_validate => p_validate
378 ,p_element_name => p_scheme_prefix || ' Employee Pension Information'
379 ,p_reporting_name => p_reporting_name
380 ,p_element_description => p_element_description
381 ,p_business_group_id => p_business_group_id
382 ,p_effective_start_date => p_effective_start_date
383 ,p_standard_link_flag => p_standard_link_flag
384 ,p_post_termination_rule => p_post_termination_rule
385 ,p_third_party_pay_only => p_third_party_pay_only
386 ,p_contribution_type => p_contribution_type
387 ,p_pension_scheme_name => p_pension_scheme_name
388 ,p_pension_provider => p_pension_provider
389 ,p_pension_type => p_pension_type
390 ,p_pension_category => p_pension_category
391 ,p_pension_year_start_date => p_pension_year_start_date
392 ,p_employee_deduction_method => p_employee_deduction_method
393 ,p_employer_deduction_method => p_employer_deduction_method
394 ,p_scheme_number => p_scheme_number
395 ,p_employee_supplement => p_employee_supplement
396 ,p_employer_supplement => p_employer_supplement
397 ,p_employer_reference_number => p_employer_reference_number
398 ,p_scheme_prefix => p_scheme_prefix
399 ,p_element_type_id => p_element_type_id);
400
401 --if an ER component exists, then create an element for the ER component
402
403 IF p_employer_deduction_method IS NOT NULL THEN
404
405 create_pension_element(p_supp_element => 'N'
406 ,p_ee_element => 'N'
407 ,p_validate => p_validate
408 ,p_element_name => p_scheme_prefix || ' Employer Pension Information'
409 ,p_reporting_name => p_reporting_name
410 ,p_element_description => p_element_description
411 ,p_business_group_id => p_business_group_id
412 ,p_effective_start_date => p_effective_start_date
413 ,p_standard_link_flag => p_standard_link_flag
414 ,p_post_termination_rule => p_post_termination_rule
415 ,p_third_party_pay_only => p_third_party_pay_only
416 ,p_contribution_type => p_employer_deduction_method
417 ,p_pension_scheme_name => p_pension_scheme_name
418 ,p_pension_provider => p_pension_provider
419 ,p_pension_type => p_pension_type
420 ,p_pension_category => p_pension_category
421 ,p_pension_year_start_date => p_pension_year_start_date
422 ,p_employee_deduction_method => p_employee_deduction_method
423 ,p_employer_deduction_method => p_employer_deduction_method
424 ,p_scheme_number => p_scheme_number
425 ,p_employee_supplement => p_employee_supplement
426 ,p_employer_supplement => p_employer_supplement
427 ,p_employer_reference_number => p_employer_reference_number
428 ,p_scheme_prefix => p_scheme_prefix
429 ,p_element_type_id => l_ee_supplement);
430
431 END IF;
432
433 --if EE/ER supplements exist then also create those elements
434
435 IF p_employee_supplement IS NOT NULL THEN
436
437 create_pension_element(p_supp_element => 'Y'
438 ,p_ee_element => 'Y'
439 ,p_validate => p_validate
440 ,p_element_name => p_scheme_prefix || ' Employee Supplement'
441 ,p_reporting_name => p_reporting_name
442 ,p_element_description => p_element_description
443 ,p_business_group_id => p_business_group_id
444 ,p_effective_start_date => p_effective_start_date
445 ,p_standard_link_flag => p_standard_link_flag
446 ,p_post_termination_rule => p_post_termination_rule
447 ,p_third_party_pay_only => p_third_party_pay_only
448 ,p_contribution_type => p_employee_supplement
449 ,p_pension_scheme_name => p_pension_scheme_name
450 ,p_pension_provider => p_pension_provider
451 ,p_pension_type => p_pension_type
452 ,p_pension_category => p_pension_category
453 ,p_pension_year_start_date => p_pension_year_start_date
454 ,p_employee_deduction_method => p_employee_deduction_method
455 ,p_employer_deduction_method => p_employer_deduction_method
456 ,p_scheme_number => p_scheme_number
457 ,p_employee_supplement => p_employee_supplement
458 ,p_employer_supplement => p_employer_supplement
459 ,p_employer_reference_number => p_employer_reference_number
460 ,p_scheme_prefix => p_scheme_prefix
461 ,p_element_type_id => l_ee_supplement);
462
463 END IF;
464
465 IF p_employer_supplement IS NOT NULL THEN
466
467 create_pension_element(p_supp_element => 'Y'
468 ,p_ee_element => 'N'
469 ,p_validate => p_validate
470 ,p_element_name => p_scheme_prefix || ' Employer Supplement'
471 ,p_reporting_name => p_reporting_name
472 ,p_element_description => p_element_description
473 ,p_business_group_id => p_business_group_id
474 ,p_effective_start_date => p_effective_start_date
475 ,p_standard_link_flag => p_standard_link_flag
476 ,p_post_termination_rule => p_post_termination_rule
477 ,p_third_party_pay_only => p_third_party_pay_only
478 ,p_contribution_type => p_employer_supplement
479 ,p_pension_scheme_name => p_pension_scheme_name
480 ,p_pension_provider => p_pension_provider
481 ,p_pension_type => p_pension_type
482 ,p_pension_category => p_pension_category
483 ,p_pension_year_start_date => p_pension_year_start_date
484 ,p_employee_deduction_method => p_employer_deduction_method
485 ,p_employer_deduction_method => p_employer_deduction_method
486 ,p_scheme_number => p_scheme_number
487 ,p_employee_supplement => p_employee_supplement
488 ,p_employer_supplement => p_employer_supplement
489 ,p_employer_reference_number => p_employer_reference_number
490 ,p_scheme_prefix => p_scheme_prefix
491 ,p_element_type_id => l_er_supplement);
492
493 END IF;
494
495 END create_pension_objects;
496
497
498 --------------------------------------------------------------------------------
499 -- create_pension_objects_swi
500 --------------------------------------------------------------------------------
501
502 PROCEDURE create_pension_objects_swi(
503 p_validate IN NUMBER DEFAULT hr_api.g_false_num
504 ,p_element_name IN VARCHAR2
505 ,p_reporting_name IN VARCHAR2
506 ,p_element_description IN VARCHAR2
507 ,p_business_group_id IN NUMBER
508 ,p_effective_start_date IN DATE
509 ,p_standard_link_flag IN VARCHAR2
510 ,p_post_termination_rule IN VARCHAR2
511 ,p_third_party_pay_only IN VARCHAR2
512 ,p_contribution_type IN VARCHAR2
513 ,p_pension_scheme_name IN VARCHAR2
514 ,p_pension_provider IN VARCHAR2
515 ,p_pension_type IN VARCHAR2
516 ,p_pension_category IN VARCHAR2
517 ,p_pension_year_start_date IN VARCHAR2
518 ,p_employee_deduction_method IN VARCHAR2
519 ,p_employer_deduction_method IN VARCHAR2
520 ,p_scheme_number IN VARCHAR2
521 ,p_employee_supplement IN VARCHAR2
522 ,p_employer_supplement IN VARCHAR2
523 ,p_employer_reference_number IN VARCHAR2
524 ,p_scheme_prefix IN VARCHAR2
525 ,p_element_type_id OUT NOCOPY NUMBER
526 ,p_return_status OUT NOCOPY VARCHAR2)
527 IS
528 -- Variables for API Boolean parameters
529 l_validate BOOLEAN;
530 --
531 g_package VARCHAR2(30) :='per_hu_pension_objects';
532 -- Variables for IN/OUT parameters
533 --
534 -- Other variables
535 l_proc VARCHAR2(72) := g_package ||'create_pension_objects_swi';
536 --
537 BEGIN
538 hr_utility.set_location(' Entering:' || l_proc,10);
539 --
540 -- Issue a savepoint
541 --
542 savepoint create_pension_objects_swi;
543 --
544 -- Initialise Multiple Message Detection
545 --
546 hr_multi_message.enable_message_list;
547 --
548 -- Remember IN OUT parameter IN values
549 --
550 --
551 -- Convert constant values to their corresponding boolean value
552 --
553 l_validate :=
554 hr_api.constant_to_boolean
555 (p_constant_value => p_validate);
556 --
557 -- Register Surrogate ID or user key values
558 --
559 -- irc_inp_ins.set_base_key_value
560 -- (p_notification_preference_id => p_notification_preference_id
561 -- );
562 --
563 -- Call API
564 --
565 per_hu_pension_objects.create_pension_objects(
566 p_validate => l_validate
567 ,p_element_name => p_element_name
568 ,p_reporting_name => p_reporting_name
569 ,p_element_description => p_element_description
570 ,p_business_group_id => p_business_group_id
571 ,p_effective_start_date => p_effective_start_date
572 ,p_standard_link_flag => p_standard_link_flag
573 ,p_post_termination_rule => p_post_termination_rule
574 ,p_third_party_pay_only => p_third_party_pay_only
575 ,p_contribution_type => p_contribution_type
576 ,p_pension_scheme_name => p_pension_scheme_name
577 ,p_pension_provider => p_pension_provider
578 ,p_pension_type => p_pension_type
579 ,p_pension_category => p_pension_category
580 ,p_pension_year_start_date => fnd_date.date_to_canonical(p_pension_year_start_date)
581 ,p_employee_deduction_method => p_employee_deduction_method
582 ,p_employer_deduction_method => p_employer_deduction_method
583 ,p_scheme_number => p_scheme_number
584 ,p_employee_supplement => p_employee_supplement
585 ,p_employer_supplement => p_employer_supplement
586 ,p_employer_reference_number => p_employer_reference_number
587 ,p_scheme_prefix => p_scheme_prefix
588 ,p_element_type_id => p_element_type_id);
589 --
590 -- Convert API warning boolean parameter values to specific
591 -- messages and add them to Multiple Message List
592 --
593 --
594 -- Convert API non-warning boolean parameter values
595 --
596 --
597 -- Derive the API return status value based on whether
598 -- messages of any type exist in the Multiple Message List.
599 -- Also disable Multiple Message Detection.
600 --
601 p_return_status := hr_multi_message.get_return_status_disable;
602 hr_utility.set_location(' Leaving:' || l_proc,20);
603 --
604 EXCEPTION
605 WHEN hr_multi_message.error_message_exist THEN
606 --
607 -- Catch the Multiple Message List exception which
608 -- indicates API processing has been aborted because
609 -- at least one error message exists in the list.
610 --
611 ROLLBACK TO create_pension_objects_swi;
612 --
613 -- Reset IN OUT paramters and set OUT parameters
614 --
615 p_element_type_id := null;
616 p_return_status := hr_multi_message.get_return_status_disable;
617 hr_utility.set_location(' Leaving:' || l_proc,30);
618 WHEN OTHERS THEN
619 --
620 -- When Multiple Message Detection is enabled catch
621 -- any Application specific or other unexpected
622 -- exceptions. Adding appropriate details to the
623 -- Multiple Message List. Otherwise re-raise
624 -- the error.
625 --
626 ROLLBACK TO create_pension_objects_swi;
627 IF hr_multi_message.unexpected_error_add(l_proc) THEN
628 hr_utility.set_location(' Leaving:' || l_proc, 40);
629 RAISE;
630 END IF;
631 --
632 -- Reset IN OUT parameters and set OUT parameters
633 --
634 p_element_type_id := null;
635 p_return_status := hr_multi_message.get_return_status_disable;
636 hr_utility.set_location(' Leaving: ' || l_proc, 50);
637 --
638
639 END create_pension_objects_swi;
640
641 --------------------------------------------------------------------------------
642 -- delete_pension_objects_swi
643 --------------------------------------------------------------------------------
644
645 PROCEDURE delete_pension_objects_swi
646 (p_validate NUMBER DEFAULT hr_api.g_false_num
647 ,p_element_type_id IN NUMBER
648 ,p_effective_date IN DATE
649 ,p_object_version_number IN NUMBER
650 ,p_return_status OUT NOCOPY VARCHAR2 ) IS
651 --
652 -- Variables for API Boolean parameters
653 l_validate boolean;
654 --
655 g_package varchar2(30) :='per_hu_pension_objects';
656 -- Variables for IN/OUT parameters
657 --
658 -- Other variables
659 l_proc varchar2(72) := g_package ||'delete_pension_objects_swi';
660 --
661 BEGIN
662 --
663 hr_utility.set_location(' Entering:' || l_proc,10);
664 --
665 -- Issue a savepoint
666 --
667 savepoint delete_pension_objects_swi;
668 --
669 -- Initialise Multiple Message Detection
670 --
671 hr_multi_message.enable_message_list;
672 --
673 -- Remember IN OUT parameter IN values
674 --
675 --
676 -- Convert constant values to their corresponding boolean value
677 --
678 l_validate :=
679 hr_api.constant_to_boolean
680 (p_constant_value => p_validate);
681 --
682 -- Register Surrogate ID or user key values
683 --
684 per_hu_pension_objects.delete_pension_objects(
685 p_validate => l_validate
686 ,p_element_type_id => p_element_type_id
687 ,p_effective_date => p_effective_date
688 ,p_object_version_number => p_object_version_number);
689
690 --
691 -- Convert API non-warning boolean parameter values
692 --
693 --
694 -- Derive the API return status value based on whether
695 -- messages of any type exist in the Multiple Message List.
696 -- Also disable Multiple Message Detection.
697 --
698 p_return_status := hr_multi_message.get_return_status_disable;
699 hr_utility.set_location(' Leaving:' || l_proc,20);
700 --
701 exception
702 when hr_multi_message.error_message_exist then
703 --
704 -- Catch the Multiple Message List exception which
705 -- indicates API processing has been aborted because
706 -- at least one error message exists in the list.
707 --
708 rollback to delete_pension_objects_swi;
709 --
710 -- Reset IN OUT paramters and set OUT parameters
711 --
712 p_return_status := hr_multi_message.get_return_status_disable;
713 hr_utility.set_location(' Leaving:' || l_proc,30);
714 when others then
715 --
716 -- When Multiple Message Detection is enabled catch
717 -- any Application specific or other unexpected
718 -- exceptions. Adding appropriate details to the
719 -- Multiple Message List. Otherwise re-raise
720 -- the error.
721 --
722 rollback to delete_pension_objects_swi;
723 if hr_multi_message.unexpected_error_add(l_proc) then
724 hr_utility.set_location(' Leaving:' || l_proc, 40);
725 raise;
726 end if;
727 --
728 -- Reset IN OUT parameters and set OUT parameters
729 --
730 p_return_status := hr_multi_message.get_return_status_disable;
731 hr_utility.set_location(' Leaving: ' || l_proc, 50);
732
733 END delete_pension_objects_swi;
734 --
735 -------------------------------------------------------------------------------
736 -- delete_pension_objects
737 -------------------------------------------------------------------------------
738
739 PROCEDURE delete_pension_objects(p_validate BOOLEAN DEFAULT FALSE
740 ,p_element_type_id NUMBER
741 ,p_effective_date DATE
742 ,p_object_version_number NUMBER
743 ) IS
744 --
745 l_element_type_id pay_element_types_f.element_type_id%TYPE;
746 l_business_group_id pay_element_types_f.business_group_id%TYPE;
747 l_object_version_number NUMBER;
748 --
749 CURSOR csr_del_element_value IS
750 SELECT business_group_id FROM pay_element_types_f
751 WHERE element_type_id=p_element_type_id
752 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
753
754 -- For Input Values
755 CURSOR csr_del_input_value(p_input_name VARCHAR2) IS
756 SELECT input_value_id,object_version_number FROM pay_input_values_f
757 WHERE name = p_input_name
758 AND element_type_id = p_element_type_id--l_element_type_id
759 AND business_group_id = l_business_group_id
760 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
761
762 -- For Extra information
763 CURSOR csr_ele_extra_info IS
764 SELECT element_type_extra_info_id,object_version_number
765 FROM pay_element_type_extra_info
766 WHERE eei_information_category = 'HU_PENSION_SCHEME_INFO'
767 AND element_type_id = p_element_type_id;--l_element_type_id;
768
769
770 TYPE v_input_values IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
771
772 l_input_values v_input_values;
773 l_ele_ovn pay_element_types_f.object_version_number%TYPE;
774 l_effective_start_date pay_input_values_f.effective_start_date%TYPE;
775 l_effective_end_date pay_input_values_f.effective_end_date%TYPE;
776 l_balance_feeds_warning boolean;
777 l_input_value_id pay_input_values_f.input_value_id%TYPE;
778 l_inp_ovn pay_input_values_f.object_version_number%TYPE;
779 l_element_extra_info_id pay_element_type_extra_info.element_type_extra_info_id%TYPE;
780 l_extra_info_ovn pay_element_type_extra_info.object_version_number%TYPE;
781 l_processing_rules_warning boolean;
782
783 BEGIN
784
785 l_input_values(1) := 'Override Start Date';
786 l_input_values(2) := 'Reason for Joining';
787 l_input_values(3) := 'Opt Out Date';
788 l_input_values(4) := 'Personal Membership Code';
789 l_input_values(5) := 'Contribution Percent';
790 l_input_values(6) := 'Contribution Amount';
791
792 --
793 -- Convert constant values to their corresponding boolean value
794 --
795
796 OPEN csr_del_element_value;
797 FETCH csr_del_element_value into l_business_group_id;
798 CLOSE csr_del_element_value;
799
800 -- For Input Values
801 FOR i IN 1..6
802 LOOP
803 OPEN csr_del_input_value(l_input_values(i));
804 FETCH csr_del_input_value INTO l_input_value_id,l_inp_ovn;
805 IF csr_del_input_value%FOUND THEN
806 pay_input_value_api.delete_input_value
807 ( p_validate => p_validate
808 ,p_effective_date => p_effective_date
809 ,p_datetrack_delete_mode => 'ZAP'
810 ,p_input_value_id => l_input_value_id
811 ,p_object_version_number => l_inp_ovn -- in/out
812 ,p_effective_start_date => l_effective_start_date -- out
813 ,p_effective_end_date => l_effective_end_date -- out
814 ,p_balance_feeds_warning => l_balance_feeds_warning -- out
815 );
816 END IF;
817 CLOSE csr_del_input_value;
818 END LOOP;
819
820 -- For Extra Information
821
822 OPEN csr_ele_extra_info;
823 FETCH csr_ele_extra_info into l_element_extra_info_id,l_extra_info_ovn;
824 CLOSE csr_ele_extra_info;
825
826 pay_element_extra_info_api.delete_element_extra_info
827 (p_validate => p_validate
828 ,p_element_type_extra_info_id => l_element_extra_info_id
829 ,p_object_version_number => l_extra_info_ovn
830 ) ;
831 --
832 l_object_version_number := p_object_version_number;
833 -- For Element Types
834
835 pay_element_types_api.delete_element_type
836 (p_validate => p_validate
837 ,p_effective_date => p_effective_date
838 ,p_datetrack_delete_mode => 'ZAP'
839 ,p_element_type_id => p_element_type_id
840 ,p_object_version_number => l_object_version_number
841 ,p_effective_start_date => l_effective_start_date
842 ,p_effective_end_date => l_effective_start_date
843 ,p_balance_feeds_warning => l_balance_feeds_warning
844 ,p_processing_rules_warning => l_processing_rules_warning
845 ) ;
846
847 END delete_pension_objects;
848 --
849 END per_hu_pension_objects;