[Home] [Help]
PACKAGE BODY: APPS.NTG_EARNINGS_TEMPLATE
Source
1 PACKAGE BODY ntg_earnings_template AS
2 /* $Header: pyusntgf.pkb 120.2 2006/05/11 02:48:10 saikrish noship $ */
3
4 /*========================================================================
5 * CREATE_ELE_NTG_OBJECTS
6 *=======================================================================*/
7 FUNCTION create_ele_ntg_objects
8 (p_ele_name in varchar2
9 ,p_ele_reporting_name in varchar2
10 ,p_ele_description in varchar2 default NULL
11 ,p_ele_classification in varchar2
12 ,p_ele_category in varchar2 default NULL
13 ,p_ele_processing_type in varchar2
14 ,p_ele_priority in number default NULL
15 ,p_ele_standard_link in varchar2 default 'N'
16 ,p_ele_ot_base in varchar2 default 'N'
17 ,p_flsa_hours in varchar2 default 'N'
18 ,p_sep_check_option in varchar2 default 'N'
19 ,p_ele_eff_start_date in date default NULL
20 ,p_ele_eff_end_date in date default NULL
21 ,p_supp_category in varchar2
22 ,p_legislation_code in varchar2
23 ,p_bg_id in number
24 ,p_termination_rule in varchar2 default 'F'
25 )
26 RETURN NUMBER IS
27 --
28 TYPE TypeNumber IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
29 TYPE TypeChar20 IS TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
30 t_bal_id TypeNumber;
31 t_form_id TypeNumber;
32 t_ipv_id TypeNumber;
33 t_def_val TypeChar20;
34 t_we_flag TypeChar20;
35 --
36 l_reserved VARCHAR2(1) := 'N';
37 l_element_type_id number;
38 l_calc_type varchar2(100);
39 l_multiple_entries char(1) := 'Y';
40 l_ovn number;
41 l_pri_bal_id number;
42 l_pri_ele_type_id number;
43 l_ssf_ele_type_id number;
44 l_source_template_id number;
45 l_template_id number;
46 l_sf_element_type_id number;
47 l_sf_ele_obj_ver_number number;
48 l_iter_formula_id number;
49 l_skip_formula varchar2(50);
50 l_ele_obj_ver_number number;
51 l_priority number;
52 l_result_name varchar2(20);
53 l_iterative_rule_type varchar2(1);
54 l_iv_id number;
55 l_insert varchar2(1) := 'N';
56 l_iter_rule_id number;
57 l_iter_rule_ovn number;
58 l_effective_start_date date;
59 l_effective_end_date date;
60 l_seeded_ele_type_id number;
61 l_seeded_input_val_id number;
62 l_nextval number;
63 l_status_pro_rule_id number;
64 l_configuration_information2 VARCHAR2(200); --Added for bug 5219568
65 --
66 l_proc varchar2(80) := 'ntg_earnings_template.create_ele_template_objects';
67 --
68 -- cursor to get the template id
69 --
70 CURSOR c_template (l_template_name varchar2) IS
71 SELECT template_id
72 FROM pay_element_templates
73 WHERE template_name = l_template_name
74 AND legislation_code = p_legislation_code
75 AND template_type = 'T'
76 AND business_group_id is NULL;
77 --
78 -- cursor to get the iterative formula id
79 --
80 CURSOR c_iter_formula_id IS
81 SELECT formula_id
82 FROM ff_formulas_f
83 WHERE formula_name = 'US_ITER_GROSSUP'
84 and legislation_code = 'US';
85 --
86 -- Cursor to get Input value to set iterative processing rule.
87 --
88 CURSOR c_input_value_id IS
89 SELECT input_value_id, name
90 FROM pay_input_values_f
91 WHERE element_type_id = l_pri_ele_type_id;
92 --
93 --=======================================================================
94 -- FUNCTION GET_OBJ_ID
95 --=======================================================================
96 FUNCTION get_obj_id (p_object_type in varchar2,
97 p_object_name in varchar2,
98 p_object_id in number default NULL)
99 RETURN NUMBER is
100 --
101 l_object_id NUMBER := NULL;
102 l_proc VARCHAR2(60) := 'ntg_earnings_template.get_obj_id';
103 --
104 CURSOR c_element IS -- Gets the element type id
105 SELECT element_type_id
106 FROM pay_element_types_f
107 WHERE element_name = p_object_name
108 AND business_group_id+0 = p_bg_id;
109 --
110 CURSOR c_get_ipv_id IS -- Gets the input value id
111 SELECT piv.input_value_id
112 FROM pay_input_values_f piv
113 WHERE piv.name = p_object_name
114 AND piv.element_type_id = p_object_id
115 AND piv.business_group_id + 0 = p_bg_id;
116 --
117 CURSOR c_get_bal_id IS -- Gets the Balance type id
118 SELECT balance_type_id
119 FROM pay_balance_types pbt
120 WHERE pbt.balance_name = p_object_name
121 AND NVL(pbt.business_group_id, p_bg_id) = p_bg_id
122 AND NVL(pbt.legislation_code, p_legislation_code) = p_legislation_code;
123 --
124 BEGIN
125 hr_utility.set_location('Entering: '||l_proc, 10);
126 --
127 IF p_object_type = 'ELEMENT' then
128 FOr c_rec in c_element LOOP
129 l_object_id := c_rec.element_type_id; -- element id
130 end loop;
131 ELSIF p_object_type = 'BALANCE' THEN
132 FOR c_rec in c_get_bal_id LOOP
133 l_object_id := c_rec.balance_type_id; -- balance id
134 END LOOP;
135 ELSIF p_object_type = 'IPV' THEN
136 FOR c_rec in c_get_ipv_id LOOP
137 l_object_id := c_rec.input_value_id; -- input value id
138 END LOOP;
139 END IF;
140 hr_utility.set_location('Leaving: '||l_proc, 50);
141 --
142 RETURN l_object_id;
143 END get_obj_id;
144 ---------------------------------------------------------------------------------
145 ---------------------------------- MAIN FUNCTION --------------------------------
146 ---------------------------------------------------------------------------------
147 BEGIN
148 -- hr_utility.trace_on('Y','ELISA');
149
150 hr_utility.set_location('Entering : '||l_proc, 10);
151 --
152 -- Set session date and Source template id
153 --
154 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
155 --
156 -- Check Element Name
157 --
158 hr_utility.set_location(l_proc, 15);
159 --
160 BEGIN
161 select 'Y'
162 into l_reserved
163 from pay_balance_types
164 where p_ele_name = balance_name -- Bug 3350067
165 and nvl(legislation_code, 'US') = 'US'
166 and nvl(business_group_id, p_bg_id) = p_bg_id;
167
168 EXCEPTION WHEN NO_DATA_FOUND THEN
169 l_reserved := 'N';
170
171 END;
172
173 if l_reserved = 'Y' then
174 hr_utility.set_location(l_proc,16);
175 hr_utility.set_message(801,'HR_7564_ALL_RES_WORDS');
176 hr_utility.raise_error;
177 end if;
178
179 hr_utility.set_location(l_proc, 20);
180 --
181 -- Set Skip Rules
182 --
183 if p_ele_classification = 'Supplemental Earnings' then
184 l_skip_formula := 'SUPPLEMENTAL_EARNINGS';
185 -- l_calc_type := 'GROSSUP_FLAT_AMOUNT_NONRECUR_V2';
186 elsif p_ele_classification = 'Earnings' then
187 l_skip_formula := 'REGULAR_EARNINGS';
188 -- l_calc_type := 'GROSSUP_FLAT_AMOUNT_NONRECUR_V2';
189 end if;
190 --
191 -- get the template id
192 --
193 OPEN c_template('Net To Gross Earning');
194 FETCH c_template into l_source_template_id;
195 CLOSE c_template;
196 --
197 -- Default element processing priority
198 --
199 if p_ele_priority is null then
200 if p_ele_classification = 'Supplemental Earnings' then
201 l_priority := 2500;
202 elsif p_ele_classification = 'Earnings' then
203 l_priority := 1750;
204 elsif p_ele_classification = 'Imputed Earnings' then
205 l_priority := 3250;
206 end if;
207 end if;
208
209
210 --------------------------------------------
211 -- Create the user Structure
212 --------------------------------------------
213 --
214 hr_utility.set_location(l_proc, 60);
215 --
216 -- This procedure replaces <base name> with actual
217 -- element name that the user passed and creates
218 -- all elements in user schema (in template tables).
219 --
220 --Added for bug 5219568
221 IF p_ele_classification = 'Supplemental Earnings' AND p_supp_category = 'CM' THEN
222 l_configuration_information2 := 'Y';
223 ELSE
224 l_configuration_information2 := 'N';
225 END IF;
226
227 pay_element_template_api.create_user_structure
228 (p_validate => false
229 ,p_effective_date => p_ele_eff_start_date
230 ,p_business_group_id => p_bg_id
231 ,p_source_template_id => l_source_template_id
232 ,p_base_name => p_ele_name
233 ,p_base_processing_priority => l_priority
234 ,p_configuration_information1 => p_ele_processing_type
235 ,p_configuration_information2 => l_configuration_information2
236 ,p_configuration_information11 => p_sep_check_option
237 ,p_template_id => l_template_id
238 ,p_object_version_number => l_ovn );
239 --
240 hr_utility.set_location(l_proc, 80);
241 --
242 ---------------------- Get Element Type ID of new Template -----------------
243 --
244 select element_type_id, object_version_number
245 into l_element_type_id, l_ele_obj_ver_number
246 from pay_shadow_element_types
247 where template_id = l_template_id
248 and element_name = p_ele_name;
249 --
250 /*
251 select element_type_id, object_version_number
252 into l_sf_element_type_id, l_sf_ele_obj_ver_number
253 from pay_shadow_element_types
254 where template_id = l_template_id
255 and element_name = p_ele_name||' Special Features';
256 */
257 -----------------------------------------------------------
258 -- Update Base shadow Element with user-specified details
259 -----------------------------------------------------------
260 --
261 --IF p_ele_processing_type = 'N' THEN
262 -- l_multiple_entries := 'N';
263 --END IF;
264 --
265 pay_shadow_element_api.update_shadow_element
266 (p_validate => false
267 ,p_effective_date => p_ele_eff_start_date
268 ,p_element_type_id => l_element_type_id
269 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
270 ,p_description => p_ele_description
271 ,p_reporting_name => p_ele_reporting_name
272 ,p_element_information_category => nvl(upper(p_legislation_code||'_'||
273 p_ele_classification), hr_api.g_varchar2)
274 ,p_element_information1 => nvl(p_supp_category, hr_api.g_varchar2)
275 --,p_element_information10 => l_pri_bal_id /* done later */
276 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
277 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
278 ,p_skip_formula => l_skip_formula
279 ,p_object_version_number => l_ele_obj_ver_number
280 );
281 hr_utility.set_location(l_proc, 90);
282
283 /* NO SPECIAL FEATURES FOR NTG
284
285 ------------------------------------------------------------------
286 -- Update user-specified details on Special Features Element.
287 ------------------------------------------------------------------
288 --
289 pay_shadow_element_api.update_shadow_element
290 (p_validate => false
291 ,p_effective_date => p_ele_eff_start_date
292 ,p_element_type_id => l_sf_element_type_id
293 ,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
294 ,p_description => 'SF element for '||p_ele_name
295 ,p_element_information_category => nvl(upper(p_legislation_code||'_'||
296 p_ele_classification), hr_api.g_varchar2)
297 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
298 ,p_object_version_number => l_sf_ele_obj_ver_number
299 );
300 */
301 ------------------------------------------------------------
302 -- Generate Core Objects
303 ------------------------------------------------------------
304 hr_utility.set_location(l_proc, 120);
305
306 pay_element_template_api.generate_part1
307 (p_validate => false
308 ,p_effective_date => p_ele_eff_start_date
309 ,p_hr_only => false
310 ,p_hr_to_payroll => false
311 ,p_template_id => l_template_id);
312 --
313 hr_utility.set_location(l_proc, 130);
314 --
315 if (hr_utility.chk_product_install('Oracle Payroll','US')) then
316 pay_element_template_api.generate_part2
317 (p_validate => false
318 ,p_effective_date => p_ele_eff_start_date
319 ,p_template_id => l_template_id);
320 end if;
321 hr_utility.set_location(l_proc, 140);
322 --
323 -------------------------------------------------------------------
324 -- Get Element and Balance Id's to update the Further Information
325 -------------------------------------------------------------------
326 l_pri_bal_id := get_obj_id('BALANCE', p_ele_name); /* primay balance */
327 l_pri_ele_type_id := get_obj_id('ELEMENT', p_ele_name);
328 -- l_ssf_ele_type_id := get_obj_id('ELEMENT',p_ele_name||' Special Features');
329
330 --
331 -- Get Iterative formula
332 --
333 OPEN c_iter_formula_id;
334 FETCH c_iter_formula_id into l_iter_formula_id;
335 IF c_iter_formula_id%NOTFOUND then
336 hr_utility.set_location(l_proc, 145);
337 hr_utility.set_message(800,'ITERATIVE FORMULA NOT FOUND');
338 hr_utility.raise_error;
339 END IF;
340 CLOSE c_iter_formula_id;
341 ----------------------------------------------
342 -- Set iterative formula and Termination Rule
343 ----------------------------------------------
344 UPDATE pay_element_types_f
345 SET element_information10 = l_pri_bal_id,
346 iterative_formula_id = l_iter_formula_id,
347 iterative_flag = 'Y',
348 iterative_priority = 5 ,
349 grossup_flag = 'Y',
350 process_mode = 'S',
351 post_termination_rule = p_termination_rule
352 WHERE element_type_id = l_pri_ele_type_id
353 AND business_group_id + 0 = p_bg_id;
354 ---------------------------------
355 -- Set iterative processing rules
356 ---------------------------------
357 FOR c_iv_rec in c_input_value_id LOOP
358
359 IF c_iv_rec.name = 'Additional Amount'
360 then l_result_name := 'ADDITIONAL_AMOUNT';
361 l_iterative_rule_type := 'A';
362 l_iv_id := c_iv_rec.input_value_id;
363 l_insert := 'Y';
364
365 elsif c_iv_rec.name = 'Low Gross'
366 then l_result_name := 'LOW_GROSS';
367 l_iterative_rule_type := 'A';
368 l_iv_id := c_iv_rec.input_value_id;
369 l_insert := 'Y';
370
371 elsif c_iv_rec.name = 'High Gross'
372 then l_result_name := 'HIGH_GROSS';
373 l_iterative_rule_type := 'A';
374 l_iv_id := c_iv_rec.input_value_id;
375 l_insert := 'Y';
376
377 elsif c_iv_rec.name = 'Remainder'
378 then l_result_name := 'REMAINDER';
379 l_iterative_rule_type := 'A';
380 l_iv_id := c_iv_rec.input_value_id;
381 l_insert := 'Y';
382
383 elsif c_iv_rec.name = 'Pay Value'
384 -- Using any other Input Value to insert Stopper.
385 then l_result_name := 'STOPPER';
386 l_iterative_rule_type := 'S';
387 l_iv_id := NULL;
388 l_insert := 'Y';
389 end if;
390
391 IF l_insert = 'Y' THEN
392
393 hr_utility.set_location('p_ele_eff_start_date = '||p_ele_eff_start_date, 149);
394
395 pay_iterative_rules_api.create_iterative_rule
396 (
397 p_effective_date => p_ele_eff_start_date
398 ,p_element_type_id => l_pri_ele_type_id
399 ,p_result_name => l_result_name
400 ,p_iterative_rule_type => l_iterative_rule_type
401 ,p_input_value_id => l_iv_id
402 ,p_severity_level => NULL
403 ,p_business_group_id => p_bg_id
404 ,p_legislation_code => 'US'
405 ,p_iterative_rule_id => l_iter_rule_id
406 ,p_object_version_number => l_iter_rule_ovn
407 ,p_effective_start_date => l_effective_start_date
408 ,p_effective_end_date => l_effective_end_date
409 );
410 END IF;
411 l_insert := 'N';
412 END LOOP;
413
414 --
415 hr_utility.set_location(l_proc, 150);
416 -------------------------------------------------------------------
417 -- Update Input values with default values, validation formula etc.
418 -------------------------------------------------------------------
419 t_ipv_id(1) := get_obj_id('IPV', 'Separate Check', l_pri_ele_type_id);
420 t_form_id(1) := NULL;
421 t_we_flag(1) := NULL;
422 t_def_val(1) := p_sep_check_option;
423
424 hr_utility.set_location('Leaving: '||l_proc, 170);
425 FOR i in 1..1 LOOP
426 UPDATE pay_input_values_f
427 SET formula_id = t_form_id(i)
428 ,warning_or_error = t_we_flag(i)
429 ,default_value = t_def_val(i)
430 WHERE input_value_id = t_ipv_id(i);
431 END LOOP;
432
433 hr_utility.set_location('Leaving: '||l_proc, 175);
434
435 --
436 hr_utility.set_location('Leaving: '||l_proc, 180);
437
438 -- Amount needs to feed the Seeded element (FIT_GROSSUP_ADJUSTMENT)
439 -- of Input Value Amount.
440 -- Thus need to get the element_type_id of the seeded element
441 -- and input_value_id of Amount from the seeded element.
442 hr_utility.set_location('select element type id', 136);
443
444 Select element_type_id
445 into l_seeded_ele_type_id
446 from pay_element_types_f
447 where upper(element_name) = 'FIT_GROSSUP_ADJUSTMENT'
448 and legislation_code = 'US';
449
450 hr_utility.set_location('element type id' || l_seeded_ele_type_id , 137);
451 Select input_value_id
452 into l_seeded_input_val_id
453 from pay_input_values_f
454 where element_type_id = l_seeded_ele_type_id
455 and upper(name) = 'AMOUNT';
456
457 select pay_formula_result_rules_s.nextval
458 into l_nextval
459 from dual;
460
461 select status_processing_rule_id
462 into l_status_pro_rule_id
463 from pay_status_processing_rules_f
464 where element_type_id = l_pri_ele_type_id;
465 --and legislation_code = 'US';
466
467
468 insert into pay_formula_result_rules_f
469 (formula_result_rule_id,
470 effective_start_date,
471 effective_end_date,
472 business_group_id,
473 legislation_code,
474 element_type_id,
475 status_processing_rule_id,
476 result_name,
477 result_rule_type,
478 input_value_id,
479 last_update_date,
480 last_updated_by,
481 last_update_login,
482 created_by,
483 creation_date)
484 values
485 (l_nextval,
486 trunc(TO_DATE('0001/01/01', 'YYYY/MM/DD')),
487 trunc(TO_DATE('4712/12/31', 'YYYY/MM/DD')),
488 p_bg_id,
489 decode(p_bg_id,NULL,'US',NULL),
490 l_seeded_ele_type_id,
491 l_status_pro_rule_id,
492 'AMOUNT',
493 'I',
494 l_seeded_input_val_id,
495 sysdate,
496 -1,
497 -1,
498 -1,
499 sysdate);
500
501
502
503 -------------------------
504 RETURN l_pri_ele_type_id;
505 -------------------------
506
507 END create_ele_ntg_objects;
508 --
509 --===========================================================================
510 -- Deletion procedure
511 --===========================================================================
512 --
513 PROCEDURE delete_user_template_objects
514 (p_business_group_id in number
515 ,p_ele_name in varchar2
516 ) IS
517 --
518 l_template_id NUMBER(9);
519 --
520 l_proc VARCHAR2(60) := 'ntg_earnings_template.delete_ele_template_objects';
521 --
522 CURSOR c1 is
523 SELECT template_id
524 FROM pay_element_templates
525 WHERE base_name = p_ele_name
526 AND business_group_id + 0 = p_business_group_id
527 AND template_type = 'U';
528 --
529 BEGIN
530 --
531 hr_utility.set_location('Entering :'||l_proc, 10);
532 for c1_rec in c1 loop
533 l_template_id := c1_rec.template_id;
534 end loop;
535 --
536 pay_element_template_api.delete_user_structure
537 (p_validate => false
538 ,p_drop_formula_packages => true
539 ,p_template_id => l_template_id);
540 hr_utility.set_location('Leaving :'||l_proc, 50);
541 --
542 END delete_user_template_objects;
543 --
544 END ntg_earnings_template;