[Home] [Help]
PACKAGE BODY: APPS.PAY_US_EARN_TEMPL_WRAPPER
Source
1 PACKAGE BODY pay_us_earn_templ_wrapper AS
2 /* $Header: pyuseewr.pkb 120.6 2008/04/24 13:26:34 pvelugul noship $ */
3 FUNCTION create_earnings_element
4 (p_ele_name in varchar2
5 ,p_ele_reporting_name in varchar2
6 ,p_ele_description in varchar2
7 ,p_ele_classification in varchar2
8 ,p_ele_category in varchar2
9 ,p_ele_ot_base in varchar2
10 ,p_flsa_hours in varchar2
11 ,p_ele_processing_type in varchar2
12 ,p_ele_priority in number
13 ,p_ele_standard_link in varchar2
14 ,p_ele_calc_ff_id in number
15 ,p_ele_calc_ff_name in varchar2
16 ,p_sep_check_option in varchar2
17 ,p_dedn_proc in varchar2
18 ,p_mix_flag in varchar2
19 ,p_reduce_regular in varchar2
20 ,p_ele_eff_start_date in date
21 ,p_ele_eff_end_date in date
22 ,p_alien_supp_category in varchar2
23 ,p_bg_id in number
24 ,p_termination_rule in varchar2 default 'F'
25 ,p_stop_reach_rule in varchar2 default 'N'
26 ,p_student_earning IN varchar2 default 'N'
27 ,p_special_input_flag IN varchar2 default 'N'
28 ,p_special_feature_flag IN varchar2 default 'Y'
29 )
30 RETURN NUMBER is
31 --
32 CURSOR c_architecture is
33 SELECT parameter_value
34 FROM pay_action_parameters
35 WHERE parameter_name = 'US_ADVANCE_EARNING_TEMPLATE';
36
37 CURSOR c_bg_name (p_bg_id NUMBER) IS
38 SELECT name
39 FROM per_business_groups
40 WHERE business_group_id = p_bg_id;
41
42 CURSOR c_get_ff_id (p_formula_name VARCHAR2) is
43 SELECT formula_id
44 FROM ff_formulas_f
45 WHERE formula_name = p_formula_name
46 AND legislation_code = 'US';
47
48
49 l_ele_type_id number;
50 l_proc varchar2(80) := 'pay_us_earn_temp_wrapper';
51 l_architecture varchar2(10) := 'Y';
52 l_tw_rec PAY_ELE_TMPLT_OBJ;
53 l_sub_class pay_ele_sub_class_table;
54 l_freq_rule pay_freq_rule_table;
55 l_business_group_name VARCHAR2 (240);
56 l_ele_template_id NUMBER;
57 l_ele_priority NUMBER;
58 l_ele_calc_ff_name VARCHAR2(100);
59 l_ele_calc_ff_id NUMBER;
60
61
62
63 BEGIN
64 -- HR_UTILITY.TRACE_on(NULL,'rdhingra_pyuseewr');
65 --
66 hr_utility.trace('p_ele_name -->' || p_ele_name);
67 hr_utility.trace('p_ele_reporting_name -->' || p_ele_reporting_name);
68 hr_utility.trace('p_ele_description -->' || p_ele_description);
69 hr_utility.trace('p_ele_classification -->' || p_ele_classification);
70 hr_utility.trace('p_ele_category -->' || p_ele_category);
71 hr_utility.trace('p_ele_ot_base -->' || p_ele_ot_base);
72 hr_utility.trace('p_flsa_hours -->' || p_flsa_hours);
73 hr_utility.trace('p_ele_processing_type-->' || p_ele_processing_type);
74 hr_utility.trace('p_ele_priority -->' || p_ele_priority);
75 hr_utility.trace('p_ele_standard_link -->' || p_ele_standard_link);
76 hr_utility.trace('p_ele_calc_ff_id -->' || p_ele_calc_ff_id);
77 hr_utility.trace('p_ele_calc_ff_name -->' || p_ele_calc_ff_name);
78 hr_utility.trace('p_sep_check_option -->' || p_sep_check_option);
79 hr_utility.trace('p_dedn_proc -->' || p_dedn_proc);
80 hr_utility.trace('p_mix_flag -->' || p_mix_flag);
81 hr_utility.trace('p_reduce_regular -->' || p_reduce_regular);
82 hr_utility.trace('p_ele_eff_start_date -->' || p_ele_eff_start_date);
83 hr_utility.trace('p_ele_eff_end_date -->' || p_ele_eff_end_date);
84 hr_utility.trace('p_alien_supp_category-->' || p_alien_supp_category);
85 hr_utility.trace('p_bg_id -->' || p_bg_id );
86 hr_utility.trace('p_termination_rule -->' || p_termination_rule);
87 hr_utility.trace('p_stop_reach_rule -->' || p_stop_reach_rule);
88 hr_utility.trace('p_student_earning -->' || p_student_earning);
89 hr_utility.trace('p_special_input_flag -->' || p_special_input_flag);
90 hr_utility.trace('p_special_feature_flag-->'|| p_special_feature_flag);
91 -- =======================================================================
92 -- Initialize THE GLOBAL variables
93 -- =======================================================================
94
95 g_ele_type_id := NULL;
96
97
98
99 /* Check if element is used without using new tempelate
100 architecture */
101
102 open c_architecture;
103 fetch c_architecture into l_architecture;
104 close c_architecture;
105 l_architecture := upper(substr(l_architecture,1,1));
106
107 l_proc := l_proc||'.create_earnings_element';
108 hr_utility.trace('Value of arch is ' || l_architecture);
109
110 -- Correctly Set the formula name depending on the formula passed from
111 -- the Earnings form
112 IF p_ele_processing_type = 'R' THEN
113 -- IF p_ele_calc_ff_name = 'Flat Amount' THEN
114 IF p_ele_calc_ff_id = 1 THEN
115 l_ele_calc_ff_name := 'FLAT_AMOUNT_RECUR_V2';
116 -- ELSIF p_ele_calc_ff_name = 'Percent of Earnings' THEN
117 ELSIF p_ele_calc_ff_id = 2 THEN
118 l_ele_calc_ff_name := 'PERCENTAGE_OF_REG_EARNINGS_RECUR_V2';
119 -- ELSIF p_ele_calc_ff_name = 'Hours * Rate * Factor' THEN
120 ELSIF p_ele_calc_ff_id = 3 THEN
121 l_ele_calc_ff_name := 'HOURS_X_RATE_MULTIPLE_RECUR_V2';
122 -- ELSIF p_ele_calc_ff_name = 'Premium' THEN
123 ELSIF p_ele_calc_ff_id = 4 THEN
124 l_ele_calc_ff_name := 'PREMIUM_RECUR_V2';
125 l_architecture := 'Y';
126 END IF;
127 ELSE
128 -- IF p_ele_calc_ff_name = 'Flat Amount' THEN
129 IF p_ele_calc_ff_id = 1 THEN
130 l_ele_calc_ff_name := 'FLAT_AMOUNT_NONRECUR_V2';
131 -- ELSIF p_ele_calc_ff_name = 'Percent of Earnings' THEN
132 ELSIF p_ele_calc_ff_id = 2 THEN
133 l_ele_calc_ff_name := 'PERCENTAGE_OF_REG_EARNINGS_NONRECUR_V2';
134 -- ELSIF p_ele_calc_ff_name = 'Hours * Rate * Factor' THEN
135 ELSIF p_ele_calc_ff_id = 3 THEN
136 l_ele_calc_ff_name := 'HOURS_X_RATE_MULTIPLE_NONRECUR_V2';
137 -- ELSIF p_ele_calc_ff_name = 'Premium' THEN
138 ELSIF p_ele_calc_ff_id = 4 THEN
139 l_ele_calc_ff_name := 'PREMIUM_NONRECUR_V2';
140 l_architecture := 'Y';
141 END IF;
142 END IF; /* IF p_ele_processing_type = 'R' */
143
144
145 -- Get the formula ID associated with the above formuls
146 open c_get_ff_id(l_ele_calc_ff_name);
147 fetch c_get_ff_id into l_ele_calc_ff_id;
148 close c_get_ff_id;
149
150 l_ele_calc_ff_id := nvl(l_ele_calc_ff_id, 1);
151
152 --
153 IF p_ele_classification = 'Alien/Expat Earnings' THEN
154 --
155 l_ele_type_id := pqp_earnings_template.create_ele_template_objects
156 (p_ele_name => p_ele_name
157 ,p_ele_reporting_name => p_ele_reporting_name
158 ,p_ele_description => p_ele_description
159 ,p_ele_classification => p_ele_classification
160 ,p_ele_category => p_ele_category
161 ,p_ele_processing_type => p_ele_processing_type
162 ,p_ele_priority => p_ele_priority
163 ,p_ele_standard_link => p_ele_standard_link
164 ,p_ele_ot_base => p_ele_ot_base
165 ,p_flsa_hours => p_flsa_hours
166 ,p_ele_calc_ff_name => l_ele_calc_ff_name
167 ,p_sep_check_option => p_sep_check_option
168 ,p_dedn_proc => p_dedn_proc
169 ,p_reduce_regular => p_reduce_regular
170 ,p_ele_eff_start_date => p_ele_eff_start_date
171 ,p_ele_eff_end_date => p_ele_eff_end_date
172 ,p_supp_category => p_alien_supp_category
173 ,p_legislation_code => 'US'
174 ,p_bg_id => p_bg_id
175 ,p_termination_rule => p_termination_rule
176
177 );
178 hr_utility.set_location(l_proc, 20);
179
180 ELSE /* Not Alien/Expat Earnings */
181
182 IF NVL(l_architecture,'Y') = 'N' THEN
183 --
184 l_ele_type_id := hr_user_init_earn.do_insertions
185 (p_ele_name => p_ele_name
186 ,p_ele_reporting_name => p_ele_reporting_name
187 ,p_ele_description => p_ele_description
188 ,p_ele_classification => p_ele_classification
189 ,p_ele_category => p_ele_category
190 ,p_ele_ot_base => p_ele_ot_base
191 ,p_flsa_hours => p_flsa_hours
192 ,p_ele_processing_type => p_ele_processing_type
193 ,p_ele_priority => p_ele_priority
194 ,p_ele_standard_link => p_ele_standard_link
195 ,p_ele_calc_ff_id => l_ele_calc_ff_id
196 ,p_ele_calc_ff_name => l_ele_calc_ff_name
197 ,p_sep_check_option => p_sep_check_option
198 ,p_dedn_proc => p_dedn_proc
199 ,p_mix_flag => p_mix_flag
200 ,p_reduce_regular => p_reduce_regular
201 ,p_ele_eff_start_date => p_ele_eff_start_date
202 ,p_ele_eff_end_date => p_ele_eff_end_date
203 ,p_bg_id => p_bg_id
204 ,p_termination_rule => p_termination_rule
205 );
206 hr_utility.set_location(l_proc, 30);
207 --
208 ELSE /* Architecture */
209
210 hr_utility.set_location('10. Start of new template call',10);
211
212 l_tw_rec := PAY_ELE_TMPLT_OBJ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
213 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
214 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
215 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
216 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
217 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
218 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
219 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
220 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
221 NULL,NULL,NULL,NULL,NULL,NULL);
222
223 l_sub_class := pay_ele_sub_class_table(NULL);
224 l_freq_rule := pay_freq_rule_table(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
225
226
227 OPEN c_bg_name (p_bg_id);
228 hr_utility.trace('102. Start of new template call' );
229 FETCH c_bg_name
230 INTO l_tw_rec.business_group_name;
231 hr_utility.trace('105. Start of new template call');
232 CLOSE c_bg_name;
233
234 /* Priority to be kept as 1526 for
235 all reduce regular elements
236 all Regular Non-worked category elements
237 */
238 IF ((p_reduce_regular = 'Y') OR (p_ele_category = 'RN')) THEN
239 l_ele_priority := 1526;
240 ELSE
241 l_ele_priority := p_ele_priority;
242 END IF;
243
244 hr_utility.trace('11. Start of new template call');
245 -- FLSA Changes
246 -- Need to modify the IF condition to include all Calculation
247 -- rules for FLSA
248 IF ((SUBSTR (l_ele_calc_ff_name, 1, 7) = 'PREMIUM') OR
249 (SUBSTR (l_ele_calc_ff_name, 1, 12) = 'HOURS_X_RATE')
250 ) THEN
251 -- hr_utility.trace_on(NULL, 'FLSA');
252 hr_utility.trace('Using FLSA template');
253 hr_utility.trace('p_ele_category = ' ||p_ele_category );
254 hr_utility.trace('p_reduce_regular = ' || p_reduce_regular);
255 hr_utility.trace('p_flsa_hours = ' || p_flsa_hours);
256 hr_utility.trace('p_ele_ot_base = ' || p_ele_ot_base);
257 hr_utility.trace('p_ele_processing_type = ' || p_ele_processing_type);
258 hr_utility.trace('p_ele_eff_start_date = ' || p_ele_eff_start_date);
259 hr_utility.trace('p_ele_classification = ' || p_ele_classification);
260 hr_utility.trace('p_ele_name = ' || p_ele_name);
261 hr_utility.trace('p_ele_calc_ff_name = ' || l_ele_calc_ff_name);
262 hr_utility.trace('p_student_earning = ' || p_student_earning);
263 hr_utility.trace('p_dedn_proc = ' || p_dedn_proc);
264 hr_utility.trace('p_ele_priority = ' || p_ele_priority);
265 hr_utility.trace('p_sep_check_option = ' || p_sep_check_option);
266 hr_utility.trace('p_special_input_flag = ' || p_special_input_flag);
267 hr_utility.trace('p_stop_reach_rule = ' || p_stop_reach_rule);
268 hr_utility.trace('p_ele_processing_type = ' || p_ele_processing_type);
269 hr_utility.trace('p_ele_standard_link = ' || p_ele_standard_link);
270 hr_utility.trace('p_termination_rule = ' || p_termination_rule);
271
272 IF (SUBSTR (l_ele_calc_ff_name, 1, 7) = 'PREMIUM') THEN
273 -- Modifying the Priority of PREMIUM Elements
274 l_ele_priority := 3300;
275 END IF;
276
277
278 l_tw_rec.calculation_rule := 'US FLSA ' || p_ele_classification;
279 l_tw_rec.configuration_info_category := p_ele_category;
280 l_tw_rec.configuration_information5 := nvl(p_reduce_regular
281 ,'N');
282 l_tw_rec.configuration_information6 := nvl(p_flsa_hours, 'N');
283 l_tw_rec.configuration_information7 := nvl(p_ele_ot_base, 'N');
284 l_tw_rec.configuration_information8 := nvl(p_ele_processing_type
285 ,'N');
286 l_tw_rec.effective_date := p_ele_eff_start_date;
287 l_tw_rec.element_classification := p_ele_classification;
288 l_tw_rec.element_description := p_ele_description;
289 l_tw_rec.element_name := p_ele_name;
290 l_tw_rec.input_currency_code := 'USD';
291 l_tw_rec.legislation_code := 'US';
292 l_tw_rec.multiple_entries_allowed := 'N';
293 l_tw_rec.preference_information1 := l_ele_calc_ff_name;
294 l_tw_rec.preference_information2 := nvl(p_student_earning,'N');
295 l_tw_rec.preference_information3 := 'Y';
296 l_tw_rec.preference_information4 := p_ele_eff_end_date;
297 l_tw_rec.preference_information5 := NULL;
298 l_tw_rec.preference_information6 := p_dedn_proc;
299 l_tw_rec.preference_information8 := p_sep_check_option;
300 l_tw_rec.preference_information10 := 'Y';
301 l_tw_rec.preference_information14 := nvl(p_special_input_flag,'N');
302 l_tw_rec.preference_information15 := nvl(p_stop_reach_rule,'N');
303 l_tw_rec.proc_once_pay_period := 'Y';
304 l_tw_rec.process_mode := 'N';
305 l_tw_rec.processing_priority := l_ele_priority;
306 l_tw_rec.processing_type := nvl(p_ele_processing_type
307 ,'N');
308 l_tw_rec.reporting_name := nvl(p_ele_reporting_name,p_ele_name);
309 l_tw_rec.standard_link := p_ele_standard_link;
310 l_tw_rec.termination_rule := p_termination_rule;
311 ELSE
312 hr_utility.trace('Using US Earnings template');
313 l_tw_rec.calculation_rule := 'US ' || p_ele_classification;
314 l_tw_rec.configuration_info_category := p_ele_category;
315 l_tw_rec.configuration_information1 := 'Y';
316 l_tw_rec.configuration_information2 := 'N';
317 l_tw_rec.configuration_information3 := 'N';
318 l_tw_rec.configuration_information7 := nvl(p_ele_ot_base, 'N');
319 l_tw_rec.configuration_information8 := 'N';
320 l_tw_rec.configuration_information11 := 'N';
321 l_tw_rec.configuration_information12 := 'N';
322 l_tw_rec.configuration_information13 := nvl(p_reduce_regular
323 ,'N');
324 l_tw_rec.configuration_information23 := nvl(p_ele_processing_type
325 ,'N');
326 l_tw_rec.effective_date := p_ele_eff_start_date;
327 l_tw_rec.element_classification := p_ele_classification;
328 l_tw_rec.element_description := p_ele_description;
329 l_tw_rec.element_name := p_ele_name;
330 l_tw_rec.input_currency_code := 'USD';
331 l_tw_rec.legislation_code := 'US';
332 l_tw_rec.multiple_entries_allowed := 'N';
333 l_tw_rec.preference_information1 := l_ele_calc_ff_name;
334 l_tw_rec.preference_information2 := nvl(p_student_earning,'N');
335 l_tw_rec.preference_information3 := 'Y';
336 l_tw_rec.preference_information4 := p_ele_eff_end_date;
337 l_tw_rec.preference_information5 := NULL;
338 l_tw_rec.preference_information6 := p_dedn_proc;
339 l_tw_rec.preference_information8 := p_sep_check_option;
340 l_tw_rec.preference_information10 := 'Y';
341 l_tw_rec.preference_information14 := nvl(p_special_input_flag,'N');
342 l_tw_rec.preference_information15 := nvl(p_stop_reach_rule,'N');
343 l_tw_rec.proc_once_pay_period := 'Y';
344 l_tw_rec.process_mode := 'N';
345 l_tw_rec.processing_priority := l_ele_priority;
346 l_tw_rec.processing_type := NVL(p_ele_processing_type
347 ,'N');
348 l_tw_rec.reporting_name := nvl(p_ele_reporting_name,p_ele_name);
349 l_tw_rec.standard_link := p_ele_standard_link;
350 l_tw_rec.termination_rule := p_termination_rule;
351 END IF;
352
353 hr_utility.trace('20. Calling pay_us_earnings_template.create_element');
354
355 /* New Call */
356 pay_element_template_user_init.create_element
357 (p_validate => FALSE,
358 p_save_for_later => 'N',
359 p_rec => l_tw_rec,
360 p_sub_class => l_sub_class,
361 p_freq_rule => l_freq_rule,
362 p_ele_template_id => l_ele_template_id
363 );
364 l_ele_type_id := g_ele_type_id;
365
366
367 /* Currently Special Feature flag is always 'Y' */
368 /*l_ele_type_id :=
369 pay_us_earnings_template.create_ele_template_objects
370 (p_ele_name => p_ele_name
371 ,p_ele_reporting_name => p_ele_reporting_name
372 ,p_ele_description => p_ele_description
373 ,p_ele_classification => p_ele_classification
374 ,p_ele_category => p_ele_category
375 ,p_ele_processing_type => p_ele_processing_type
376 ,p_ele_priority => p_ele_priority
377 ,p_ele_standard_link => p_ele_standard_link
378 ,p_ele_ot_base => p_ele_ot_base
379 ,p_flsa_hours => p_flsa_hours
380 -- ,p_ele_calc_ff_id => p_ele_calc_ff_id
381 ,p_ele_calc_ff_name => p_ele_calc_ff_name
382 ,p_sep_check_option => p_sep_check_option
383 ,p_dedn_proc => p_dedn_proc
384 ,p_reduce_regular => p_reduce_regular
385 -- ,p_mix_flag => p_mix_flag
386 ,p_ele_eff_start_date => p_ele_eff_start_date
387 ,p_ele_eff_end_date => p_ele_eff_end_date
388 ,p_supp_category => NULL
389 ,p_legislation_code => 'US'
390 ,p_bg_id => p_bg_id
391 ,p_termination_rule => p_termination_rule
392 ,p_stop_reach_rule => p_stop_reach_rule
393 ,p_student_earning => p_student_earning
394 ,p_special_input_flag => p_special_input_flag
395 ,p_special_feature_flag => 'Y'
396 );*/
397
398 hr_utility.set_location(l_proc, 35);
399 END IF; /* architecture */
400 END IF; /* Alien/Expat Earnings */
401
402
403 --
404 RETURN (l_ele_type_id);
405 --
406 hr_utility.set_location('Leaving '||l_proc, 50);
407 --
408 -- HR_UTILITY.TRACE_OFF;
409 END create_earnings_element;
410
411
412
413
414 -------------------------------------------------------------------------------
415 -- DELETE_EARNINGS_ELEMENT
416 -------------------------------------------------------------------------------
417 PROCEDURE delete_earnings_element
418 (p_business_group_id in number
419 ,p_ele_type_id in number
420 ,p_ele_name in varchar2
421 ,p_ele_priority in number
422 ,p_ele_primary_baltype_id in varchar2 default null
423 ,p_ele_info_12 in varchar2 default null
424 ,p_session_date in date
425 ,p_eff_start_date in date
426 ,p_eff_end_date in date
427 ,p_ele_classification in varchar2
428 ) IS
429 --
430 l_proc varchar2(80) := 'pay_us_earn_temp_wrapper';
431 l_template_based number;
432 l_template_id number;
433
434 CURSOR c1 is
435 SELECT template_id
436 FROM pay_element_templates
437 WHERE base_name = p_ele_name
438 AND business_group_id = p_business_group_id
439 AND template_type = 'U';
440
441 BEGIN
442 --
443 -- HR_UTILITY.TRACE_on(NULL,'rdhingra_pyuseewr');
444 open c1;
445 fetch c1 into l_template_id;
446 Close c1;
447
448 l_proc := l_proc||'.delete_earnings_element';
449 hr_utility.set_location('Entering: '||l_proc, 10);
450 --
451
452 g_ele_type_id := p_ele_type_id;
453
454
455 IF l_template_id is NOT NULL THEN
456 /* pay_us_earnings_template.delete_ele_template_objects
457 (p_business_group_id => p_business_group_id
458 ,p_ele_type_id => p_ele_type_id
459 ,p_ele_name => p_ele_name
460 ,p_effective_date => p_eff_start_date
461 );
462 */
463 hr_utility.trace('Calling global package for delete');
464 PAY_ELEMENT_TEMPLATE_USER_INIT.delete_element
465 (
466 p_validate => FALSE
467 ,p_template_id => l_template_id
468 );
469 hr_utility.set_location(l_proc, 20);
470 --
471 ELSE
472 -- call the old template deletion procedure
473 hr_user_init_earn.do_deletions
474 (p_business_group_id => p_business_group_id
475 ,p_ele_type_id => p_ele_type_id
476 ,p_ele_name => p_ele_name
477 ,p_ele_priority => p_ele_priority
478 ,p_ele_info_10 => p_ele_primary_baltype_id
479 ,p_ele_info_12 => p_ele_info_12 -- p_ele_hours_baltype_id
480 ,p_del_sess_date => p_session_date
481 ,p_del_val_start_date => p_eff_start_date
482 ,p_del_val_end_date => NULL
483 );
484 hr_utility.set_location(l_proc, 40);
485 --
486 END IF;
487 --
488 hr_utility.set_location('Leaving '||l_proc, 50);
489 --
490 END delete_earnings_element;
491 --
492 --
493 END pay_us_earn_templ_wrapper;
494