[Home] [Help]
PACKAGE BODY: APPS.PAY_US_EARNINGS_TEMPLATE
Source
1 PACKAGE BODY pay_us_earnings_template AS
2 /* $Header: payusearningtemp.pkb 120.2 2012/01/19 08:21:00 rpahune ship $ */
3 -- =======================================================================
4 -- DECLARE THE GLOBAL variables
5 -- =======================================================================
6 g_bg_id NUMBER;
7 g_legislation_code VARCHAR2(60);
8 -- =======================================================================
9 -- CREATE_ELE_TEMPLATE_OBJECTS
10 -- =======================================================================
11 FUNCTION create_ele_template_objects
12 (p_ele_name IN VARCHAR2
13 ,p_ele_reporting_name IN VARCHAR2
14 ,p_ele_description IN VARCHAR2 DEFAULT NULL
15 ,p_ele_classification IN VARCHAR2
16 ,p_ele_category IN VARCHAR2 DEFAULT NULL
17 ,p_ele_processing_type IN VARCHAR2
18 ,p_ele_priority IN NUMBER DEFAULT NULL
19 ,p_ele_standard_link IN VARCHAR2 DEFAULT 'N'
20 ,p_ele_ot_base IN VARCHAR2 DEFAULT 'N'
21 ,p_flsa_hours IN VARCHAR2
22 ,p_ele_calc_ff_name IN VARCHAR2
23 ,p_sep_check_option IN VARCHAR2 DEFAULT 'N'
24 ,p_dedn_proc IN VARCHAR2
25 ,p_reduce_regular IN VARCHAR2 DEFAULT 'N'
26 ,p_ele_eff_start_date IN DATE DEFAULT NULL
27 ,p_ele_eff_end_date IN DATE DEFAULT NULL
28 ,p_supp_category IN VARCHAR2
29 ,p_legislation_code IN VARCHAR2
30 ,p_bg_id IN NUMBER
31 ,p_termination_rule IN VARCHAR2 DEFAULT 'F'
32 ,p_stop_reach_rule IN VARCHAR2 DEFAULT 'N'
33 ,p_student_earning IN VARCHAR2 DEFAULT 'N'
34 ,p_special_input_flag IN VARCHAR2 DEFAULT 'N'
35 ,p_special_feature_flag IN VARCHAR2 DEFAULT 'Y'
36 )
37 RETURN NUMBER IS
38 --
39 TYPE TypeIdNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
40 TYPE TypeIdChar IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
41
42 t_bal_id TypeIdNumber;
43 t_form_id TypeIdNumber;
44 t_ipv_id TypeIdNumber;
45 t_def_val TypeIdChar;
46 t_we_flag TypeIdChar;
47 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
48
49 l_config2_amt CHAR(1) := 'N';
50 l_config3_perc CHAR(1) := 'N';
51 l_config4_hr CHAR(1) := 'N';
52 l_config5_hrm CHAR(1) := 'N';
53 l_config2_NR_amt CHAR(1);
54 l_config2_RSI_amt CHAR(1);
55 l_config3_NR_perc CHAR(1);
56 l_config3_RSI_perc CHAR(1);
57 l_config4_NR_hr CHAR(1);
58 l_config4_RSI_hr CHAR(1);
59 l_multiple_entries CHAR(1) := 'N';
60
61 l_hours_bal_id NUMBER;
62 l_ipv_id NUMBER;
63 l_ovn NUMBER;
64 l_pri_bal_id NUMBER;
65 l_pri_ele_type_id NUMBER;
66 l_repl_bal_id NUMBER;
67 l_si_ele_type_id NUMBER;
68 l_sf_ele_type_id NUMBER;
69 l_ssf_ele_type_id NUMBER;
70 l_source_template_id NUMBER;
71 l_supp_bal_id NUMBER;
72 l_template_id NUMBER;
73 l_counter NUMBER;
74 l_addl_bal_id NUMBER;
75 l_element_type_id NUMBER;
76 l_si_rel_priority NUMBER;
77 l_sf_rel_priority NUMBER;
78 l_element_priority NUMBER;
79 l_hr_ele_id NUMBER;
80 l_hr_iv_id NUMBER;
81 l_rr_id NUMBER;
82 l_stat_proc_rule_id NUMBER;
83 l_cat_bal_type_id NUMBER;
84 l_ele_type_usages VARCHAR2(1);
85 l_calc_type VARCHAR2(30);
86 l_category_bal_name VARCHAR2(60);
87 l_temp_ele_name VARCHAR2(255);
88 l_template_name VARCHAR2(50);
89 l_proc VARCHAR2(80) := 'pay_us_earnings_template.create_ele_template_objects';
90 l_info_category VARCHAR2(50);
91 l_skip_formula VARCHAR2(50);
92 l_si_flag VARCHAR2(1) := 'Y';
93 l_sf_flag VARCHAR2(1) := 'N';
94 l_sf_iv_flag VARCHAR2(1);
95 l_se_iv_flag VARCHAR2(1);
96 l_stop_reach_flag VARCHAR2(1);
97 l_reg_earning_flag VARCHAR2(1) := 'N';
98 l_supp_earn_flag VARCHAR2(1) := 'N';
99 l_red_reg_hour_xrule_flag VARCHAR2(1) := 'N';
100
101 l_formula_text1 ff_formulas.formula_text%TYPE;
102 l_formula_text2 ff_formulas.formula_text%TYPE;
103 --
104 -- cursor to fetch the new element type id
105 --
106 CURSOR c_element(p_ele_name VARCHAR2,
107 p_template_id NUMBER) IS
108 SELECT element_type_id,
109 object_version_number
110 FROM pay_shadow_element_types
111 WHERE element_name = p_ele_name
112 AND template_id = p_template_id;
113 --
114 -- cursor to get the template id
115 --
116 CURSOR c_template(p_template_name VARCHAR2) IS
117 SELECT template_id
118 FROM pay_element_templates
119 WHERE template_name = p_template_name
120 AND legislation_code = p_legislation_code
121 AND template_type = 'T'
122 AND business_group_id IS NULL;
123
124 -- Added the following cursor - tmehra for the balance architecture changes
125 -- AS per US Payroll Team request
126
127 CURSOR get_asg_gre_run_dim_id IS
128 SELECT balance_dimension_id
129 FROM pay_balance_dimensions
130 WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
131 AND legislation_code = 'US';
132
133 CURSOR c_get_shadow_formula(p_formula_name IN VARCHAR) IS
134 SELECT formula_text
135 FROM pay_shadow_formulas
136 WHERE formula_name = p_formula_name;
137
138 CURSOR c_ele_priority(p_classification_name IN VARCHAR2) IS
139 SELECT default_priority
140 FROM pay_element_classifications
141 WHERE classification_name = p_classification_name;
142
143 CURSOR c_ele(p_element_name IN VARCHAR2) IS
144 SELECT element_type_id
145 FROM pay_element_types_f
146 WHERE upper(element_name) = upper(p_element_name)
147 AND legislation_code = 'US';
148
149 CURSOR c_inp_val(p_input_val_name IN VARCHAR2,
150 p_element_type_id IN NUMBER) IS
151 SELECT input_value_id
152 FROM pay_input_values_f
153 WHERE element_type_id = p_element_type_id
154 AND upper(NAME) = upper(p_input_val_name);
155
156 CURSOR c_pspr(p_element_type_id IN NUMBER,
157 p_bg_id IN NUMBER,
158 p_assgn_status_id IN NUMBER) IS
159 SELECT status_processing_rule_id
160 FROM pay_status_processing_rules_f
161 WHERE element_type_id = p_element_type_id
162 AND business_group_id = p_bg_id;
163 -- AND assignment_status_type_id = p_assgn_status_id;
164
165 ------------------------------------------------------------------------------
166 -- MAIN FUNCTION
167 ------------------------------------------------------------------------------
168 BEGIN
169 hr_utility.set_location('Entering : '||l_proc, 10);
170 --
171 -- Set the global variables
172 --
173 g_bg_id := p_bg_id;
174 g_legislation_code := p_legislation_code;
175 hr_utility.set_location(l_proc, 20);
176 -------------------
177 -- Set session date
178 -------------------
179 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
180 hr_utility.set_location(l_proc, 30);
181 -- IMPORTANT NOTE :
182 -- The skip rules are removed from all the earnings. This have been
183 -- replaced with entry in pay_element_type_usages table and c code
184 -- changes.This Changes are made to imporve the performance of the payroll
185 -- run. It was observed that loading of the Formula takes longer time
186 -- The script pypusetd.sql seeds the element type usages. For all
187 -- Regular earning and Imputed earnings we rows will be added in
188 -- pay_elememnt_type_usages table so that this element will not
189 -- be run in supplement run. Both BASE ELEMENT and SPECIAL INPUT
190 -- element will be not be processed.
191 -- local variable l_ele_type_usages controls the exclusion rules
192
193 -------------------------
194 -- Determine the priority
195 -------------------------
196 FOR c_ep IN c_ele_priority(p_ele_classification)
197 LOOP
198 l_element_priority := c_ep.default_priority;
199 END LOOP;
200 IF (p_ele_classification = 'Earnings') THEN
201 l_si_rel_priority := -249;
202 l_sf_rel_priority := 250;
203 l_info_category := 'US_EARNINGS';
204 l_skip_formula := NULL;
205 ELSIF (p_ele_classification = 'Supplemental Earnings') THEN
206 l_si_rel_priority := -499;
207 l_sf_rel_priority := 500;
208 l_info_category := 'US_SUPPLEMENTAL EARNINGS';
209 l_ele_type_usages := 'N';
210 l_skip_formula := NULL;
211 ELSIF (p_ele_classification = 'Imputed Earnings') THEN
212 l_si_rel_priority := -249;
213 l_sf_rel_priority := 250;
214 l_info_category := 'US_IMPUTED_EARNINGS';
215 l_skip_formula := NULL;
216 ELSIF (p_ele_classification = 'Non-payroll Payments') THEN
217 l_si_rel_priority := -249;
218 l_sf_rel_priority := 250;
219 l_info_category := 'US_NON-PAYROLL PAYMENTS';
220 l_skip_formula := NULL;
221 l_ele_type_usages := 'N';
222 END IF;
223 IF p_ele_priority IS NOT NULL THEN
224 l_element_priority := p_ele_priority;
225 END IF;
226 hr_utility.set_location(l_proc, 40);
227 --------------------------------------------
228 -- set the appropriate exclusion rules
229 --------------------------------------------
230 -- The Configuration Flex segments for the Exclusion Rules are as follows:
231 -- Config1 - Xclude SI and SF elements IF ele_processing_type='N'
232 -- Config2 - Xclude objects IF calc type is Not Amount
233 -- Config3 - Xclude objects IF calc type is Not Percentage
234 -- Config4 - Xclude objects IF calc type is Not Rate * Hours
235 -- Config5 - Xclude objects IF calc type is Not Rate * Hours with a multiple
236 -- Config6 - Xclude objects IF FLSA hours is not checked
237 -- Config7 - Xclude objects IF overtime base is not checked
238 -- Config8 -
239 -- Config9 -
240 -- Config10 -
241 -- Config11 -
242 -- Config12 -
243 -- Config13 -
244 -- Config14 -
245 -- Config15 -
246 -- Config16 -
247 -- Config17 -
248 -- Config18 -
249 -- Config19 -
250 -- Config20 -
251 -- Config22 - Element type usages exlusion rule. dont enter anyting for supplemental earning element
252 IF (p_ele_category = 'REG') THEN
253 l_reg_earning_flag := 'Y';
254 END IF;
255 IF (p_ele_classification = 'Supplemental Earnings') THEN
256 l_supp_earn_flag := 'Y';
257 l_ele_type_usages := 'N';
258 END IF;
259
260 IF (SUBSTR(p_ele_calc_ff_name,1,11) = 'FLAT_AMOUNT') THEN
261 l_config2_amt := 'Y';
262 l_calc_type := 'FLAT_AMOUNT';
263 ELSIF (SUBSTR(p_ele_calc_ff_name,1,26) = 'PERCENTAGE_OF_REG_EARNINGS') THEN
264 l_config3_perc := 'Y';
265 l_calc_type := 'PERCENTAGE';
266 ELSIF (SUBSTR(p_ele_calc_ff_name,1,21) = 'HOURS_X_RATE_MULTIPLE') THEN
267 l_config4_hr := 'Y';
268 l_config5_hrm := 'Y';
269 l_calc_type := 'HOURS_X_RATE';
270 ELSIF (SUBSTR(p_ele_calc_ff_name,1,12) = 'HOURS_X_RATE') THEN
271 l_config4_hr := 'Y';
272 l_config5_hrm := 'N';
273 l_calc_type := 'HOURS_X_RATE';
274 END IF;
275
276 hr_utility.set_location(l_proc, 50);
277
278 l_si_flag := 'Y';
279 IF ((p_ele_processing_type = 'N'
280 -- AND p_termination_rule <> 'L'
281 ) OR
282 p_special_input_flag = 'N') THEN
283 l_si_flag := 'N';
284 END IF;
285
286 IF (p_special_feature_flag = 'Y') THEN
287 l_sf_flag := 'Y';
288 l_sf_iv_flag := 'N';
289 ELSE
290 l_sf_flag := 'N';
291 l_sf_iv_flag := 'Y';
292 END IF;
293 l_se_iv_flag := 'N';
294 l_stop_reach_flag := 'N';
295
296 IF (p_student_earning = 'Y') THEN
297 l_sf_flag := 'Y';
298 l_se_iv_flag := 'Y';
299 l_sf_iv_flag := 'N';
300 l_stop_reach_flag := 'Y';
301 -- l_multiple_entries :='N';
302 END IF;
303 IF (p_stop_reach_rule = 'Y') THEN
304 l_stop_reach_flag := 'Y';
305 END IF;
306 hr_utility.set_location(l_proc, 60);
307
308 l_config2_NR_amt := 'N';
309 l_config2_RSI_amt := 'N';
310 l_config3_NR_perc := 'N';
311 l_config3_RSI_perc := 'N';
312 l_config4_NR_hr := 'N';
313 l_config4_RSI_hr := 'N';
314
315 IF (l_si_flag = 'Y') THEN
316 IF (l_config2_amt = 'Y') THEN
317 l_config2_RSI_amt := 'Y';
318 elsIF (l_config3_perc = 'Y') THEN
319 l_config3_RSI_perc := 'Y';
320 elsIF (l_config5_hrm = 'Y') THEN
321 l_config4_RSI_hr := 'Y';
322 elsIF (l_config4_hr = 'Y') THEN
323 l_config4_RSI_hr := 'Y';
324 END IF;
325 ELSE
326 IF (l_config2_amt = 'Y') THEN
327 l_config2_NR_amt := 'Y';
328 elsIF (l_config3_perc = 'Y') THEN
329 l_config3_NR_perc := 'Y';
330 elsIF (l_config5_hrm = 'Y') THEN
331 l_config4_NR_hr := 'Y';
332 elsIF (l_config4_hr = 'Y') THEN
333 l_config4_NR_hr := 'Y';
334 END IF;
335 END IF;
336 l_red_reg_hour_xrule_flag := 'N';
337 IF (l_config4_hr = 'Y' ) THEN
338 l_red_reg_hour_xrule_flag := 'Y';
339 END IF;
340 IF (p_reduce_regular = 'Y') THEN
341 l_red_reg_hour_xrule_flag := 'N';
342 END IF;
343
344 hr_utility.set_location(l_proc, 70);
345 ----------------------
346 -- get the template id
347 ----------------------
348 l_template_name := 'US Earnings';
349 l_temp_ele_name := p_ele_name ;
350
351 FOR c_rec IN c_template(l_template_name) LOOP
352 l_source_template_id := c_rec.template_id;
353 END LOOP;
354
355 hr_utility.set_location(l_proc, 70);
356
357 pay_element_template_api.create_user_structure
358 (p_validate => FALSE
359 ,p_effective_date => p_ele_eff_start_date
360 ,p_business_group_id => p_bg_id
361 ,p_source_template_id => l_source_template_id
362 ,p_base_name => p_ele_name
363 ,p_base_processing_priority => l_element_priority
364 ,p_configuration_info_category => p_ele_category
365 ,p_configuration_information1 => l_si_flag -- p_processing_type
366 ,p_configuration_information2 => l_config2_amt
367 ,p_configuration_information3 => l_config3_perc
368 ,p_configuration_information4 => l_config4_hr
369 ,p_configuration_information5 => l_config5_hrm
370 ,p_configuration_information6 => p_flsa_hours
371 ,p_configuration_information7 => p_ele_ot_base
372 ,p_configuration_information8 => l_sf_flag
373 ,p_configuration_information9 => l_sf_iv_flag
374 ,p_configuration_information10 => l_se_iv_flag
375 ,p_configuration_information11 => l_reg_earning_flag
376 ,p_configuration_information12 => l_supp_earn_flag
377 ,p_configuration_information13 => p_reduce_regular
378 ,p_configuration_information14 => l_red_reg_hour_xrule_flag
379 ,p_configuration_information15 => l_config2_NR_amt
380 ,p_configuration_information16 => l_config2_RSI_amt
381 ,p_configuration_information17 => l_config3_NR_perc
382 ,p_configuration_information18 => l_config3_RSI_perc
383 ,p_configuration_information19 => l_config4_NR_hr
384 ,p_configuration_information20 => l_config4_RSI_hr
385 ,p_configuration_information21 => l_stop_reach_flag
386 ,p_configuration_information22 => l_ele_type_usages
387 ,p_configuration_information23 => p_ele_processing_type
388 ,p_template_id => l_template_id
389 ,p_object_version_number => l_ovn );
390
391 hr_utility.set_location(l_proc, 80);
392 -----------------------------------------------------------
393 -- Update Base shadow Element with user-specified details
394 -----------------------------------------------------------
395 FOR c_rec IN c_element(l_temp_ele_name,
396 l_template_id)
397 LOOP
398 l_element_type_id := c_rec.element_type_id;
399 l_ovn := c_rec.object_version_number;
400 END LOOP;
401 IF p_ele_processing_type = 'N' THEN
402 l_multiple_entries := 'Y';
403 END IF;
404 hr_utility.set_location(l_proc, 90);
405
406 pay_shadow_element_api.update_shadow_element
407 (p_validate => FALSE
408 ,p_effective_date => p_ele_eff_start_date
409 ,p_element_type_id => l_element_type_id
410 ,p_element_name => p_ele_name
411 ,p_classification_name => p_ele_classification
412 ,p_description => p_ele_description
413 ,p_reporting_name => p_ele_reporting_name
414 ,p_processing_type => NVL(p_ele_processing_type,
415 hr_api.g_varchar2)
416 ,p_standard_link_flag => NVL(p_ele_standard_link,
417 hr_api.g_varchar2)
418 ,p_multiple_entries_allowed_fla => l_multiple_entries
419 ,p_post_termination_rule => p_termination_rule
420 , p_skip_formula => l_skip_formula
421 ,p_element_information_category => l_info_category --'US_SUPPLEMENTAL EARNINGS'
422 ,p_element_information1 => NVL(p_ele_category, hr_api.g_varchar2)
423 ,p_element_information8 => NVL(p_ele_ot_base, hr_api.g_varchar2)
424 ,p_element_information11 => NVL(p_flsa_hours, hr_api.g_varchar2)
425 ,p_element_information13 => NVL(p_reduce_regular, hr_api.g_varchar2)
426 ,p_element_information14 => NVL(p_special_input_flag, hr_api.g_varchar2)
427 ,p_element_information15 => NVL(p_stop_reach_rule, hr_api.g_varchar2)
428 ,p_object_version_number => l_ovn
429 );
430
431 hr_utility.set_location(l_proc, 100);
432 -------------------------------------------------------------------
433 -- Update user-specified details on Special Features Element.
434 -------------------------------------------------------------------
435 IF (l_sf_flag = 'Y') THEN
436 FOR c1_rec IN c_element(p_ele_name ||' Special Features', l_template_id)
437 LOOP
438 l_element_type_id := c1_rec.element_type_id;
439 l_ovn := c1_rec.object_version_number;
440
441 pay_shadow_element_api.update_shadow_element
442 (p_validate => FALSE
443 ,p_reporting_name => p_ele_reporting_name||' SF'
444 ,p_classification_name => p_ele_classification
445 ,p_effective_date => p_ele_eff_start_date
446 ,p_element_type_id => l_element_type_id
447 ,p_description => 'Special Features element for '||
448 p_ele_name
449 ,p_relative_processing_priority =>l_sf_rel_priority
450 ,p_element_information_category => l_info_category
451 ,p_element_information1 => NVL(p_ele_category, hr_api.g_varchar2)
452 ,p_element_information8 => NVL(p_ele_ot_base, hr_api.g_varchar2)
453 ,p_object_version_number => l_ovn
454 );
455 END LOOP;
456 END IF;
457 hr_utility.set_location(l_proc, 110);
458 --------------------------------------------------------------------
459 -- Update user-specified Classification Special Inputs IF it exists.
460 --------------------------------------------------------------------
461 IF (l_si_flag = 'Y') THEN
462 FOR c1_rec IN c_element ( p_ele_name||' Special Inputs', l_template_id )
463 LOOP
464 l_element_type_id := c1_rec.element_type_id;
465 l_ovn := c1_rec.object_version_number;
466 END LOOP;
467 pay_shadow_element_api.update_shadow_element
468 (p_validate => FALSE
469 ,p_reporting_name => p_ele_reporting_name ||' SI'
470 ,p_classification_name => p_ele_classification
471 ,p_effective_date => p_ele_eff_start_date
472 ,p_element_type_id => l_element_type_id
473 ,p_description => 'Special Inputs element for '||
474 p_ele_name
475 ,p_relative_processing_priority => l_si_rel_priority
476 ,p_element_information_category => l_info_category
477 ,p_element_information1 => NVL(p_ele_category, hr_api.g_varchar2)
478 ,p_element_information8 => NVL(p_ele_ot_base, hr_api.g_varchar2)
479 ,p_object_version_number => l_ovn
480 );
481 END IF;
482 hr_utility.set_location(l_proc, 120);
483 ------------------------------------------------------------
484 -- Generate Core Objects
485 ------------------------------------------------------------
486 pay_element_template_api.generate_part1
487 (p_validate => FALSE
488 ,p_effective_date => p_ele_eff_start_date
489 ,p_hr_only => FALSE
490 ,p_hr_to_payroll => FALSE
491 ,p_template_id => l_template_id);
492 --
493 hr_utility.set_location(l_proc, 130);
494 --
495 -- Add logic to generate part2 only IF payroll is installed
496 --
497 pay_element_template_api.generate_part2
498 (p_validate => FALSE
499 ,p_effective_date => p_ele_eff_start_date
500 ,p_template_id => l_template_id);
501 hr_utility.set_location(l_proc, 140);
502 --
503 -------------------------------------------------------------------
504 -- Get Element and Balance Id's to update the Further Information
505 -------------------------------------------------------------------
506 l_pri_bal_id := get_obj_id('BAL', p_ele_name);
507 l_addl_bal_id := get_obj_id('BAL', p_ele_name||' Additional');
508 l_repl_bal_id := get_obj_id('BAL', p_ele_name||' Replacement');
509 l_hours_bal_id := get_obj_id('BAL', p_ele_name||' Hours');
510
511 l_pri_ele_type_id := get_obj_id('ELE', p_ele_name);
512 l_si_ele_type_id := get_obj_id('ELE', p_ele_name||' Special Inputs');
513 l_sf_ele_type_id := get_obj_id('ELE', p_ele_name||' Speacial Features');
514
515 hr_utility.set_location(l_proc, 150);
516
517 UPDATE pay_element_types_f
518 SET element_name = p_ele_name
519 ,element_information10 = l_pri_bal_id
520 ,element_information12 = l_hours_bal_id
521 WHERE element_type_id = l_pri_ele_type_id
522 AND business_group_id = p_bg_id;
523
524 hr_utility.set_location(l_proc, 160);
525
526 -------------------------------------------------------------------
527 -- Update Input values with default values, validation formula etc.
528 -------------------------------------------------------------------
529 t_ipv_id(1) := get_obj_id('IPV', 'Deduction Processing', l_pri_ele_type_id); t_form_id(1) := NULL;
530 t_we_flag(1) := NULL;
531 t_def_val(1) := p_dedn_proc;
532 t_ipv_id(2) := get_obj_id('IPV', 'Separate Check', l_pri_ele_type_id);
533 t_form_id(2) := NULL;
534 t_we_flag(2) := NULL;
535 t_def_val(2) := p_sep_check_option;
536 --
537 hr_utility.set_location(l_proc, 170);
538 FOR i in 1..2 LOOP
539 UPDATE pay_input_values_f
540 SET formula_id = t_form_id(i)
541 ,warning_or_error = t_we_flag(i)
542 ,default_value = t_def_val(i)
543 WHERE input_value_id = t_ipv_id(i);
544 END LOOP;
545
546
547
548 ------------------------------------
549 -- Get the _ASG_GRE_RUN dimension id
550 ------------------------------------
551
552 FOR crec IN get_asg_gre_run_dim_id
553 LOOP
554 l_asg_gre_run_dim_id := crec.balance_dimension_id;
555 END LOOP;
556 --
557 hr_utility.set_location(l_proc, 175);
558
559 FOR c_rec IN c_ele('Hours by Rate')
560 LOOP
561 l_hr_ele_id := c_rec.element_type_id;
562 END LOOP;
563 FOR c_rec IN c_inp_val('Element Type Id', l_hr_ele_id)
564 LOOP
565 l_hr_iv_id := c_rec.input_value_id;
566 END LOOP;
567 hr_utility.set_location(l_proc, 180);
568 IF (l_config4_hr = 'Y') THEN
569 FOR c_rec IN c_pspr(p_element_type_id => l_pri_ele_type_id,
570 p_bg_id => p_bg_id ,
571 p_assgn_status_id => NULL)
572 LOOP
573 l_stat_proc_rule_id := c_rec.status_processing_rule_id;
574 END LOOP;
575 hr_utility.set_location(l_proc, 190);
576 l_rr_id := pay_formula_results.ins_form_res_rule (
577 p_business_group_id => p_bg_id,
578 p_legislation_code => NULL,
579 p_effective_start_date => p_ele_eff_start_date,
580 p_effective_end_date => NULL,
581 p_status_processing_rule_id => l_stat_proc_rule_id,
582 p_element_type_id => l_hr_ele_id,
583 p_input_value_id => l_hr_iv_id,
584 p_result_name => 'ELEMENT_TYPE_ID_PASSED',
585 p_result_rule_type => 'I',
586 p_severity_level => NULL);
587 hr_utility.set_location(l_proc, 200);
588 FOR c_rec IN c_inp_val('Hours', l_hr_ele_id)
589 LOOP
590 l_hr_iv_id := c_rec.input_value_id;
591 END LOOP;
592 hr_utility.set_location(l_proc, 210);
593 l_rr_id := pay_formula_results.ins_form_res_rule (
594 p_business_group_id => p_bg_id,
595 p_legislation_code => NULL,
596 p_effective_start_date => p_ele_eff_start_date,
597 p_effective_end_date => NULL,
598 p_status_processing_rule_id => l_stat_proc_rule_id,
599 p_element_type_id => l_hr_ele_id,
600 p_input_value_id => l_hr_iv_id,
601 p_result_name => 'HOURS_PASSED',
602 p_result_rule_type => 'I',
603 p_severity_level => NULL);
604 hr_utility.set_location(l_proc, 220);
605 IF (l_config5_hrm = 'Y') THEN
606 FOR c_rec IN c_inp_val('Multiple', l_hr_ele_id)
607 LOOP
608 l_hr_iv_id := c_rec.input_value_id;
609 END LOOP;
610 hr_utility.set_location(l_proc, 230);
611
612 l_rr_id := pay_formula_results.ins_form_res_rule (
613 p_business_group_id => p_bg_id,
614 p_legislation_code => NULL,
615 p_effective_start_date => p_ele_eff_start_date,
616 p_effective_end_date => NULL,
617 p_status_processing_rule_id => l_stat_proc_rule_id,
618 p_element_type_id => l_hr_ele_id,
619 p_input_value_id => l_hr_iv_id,
620 p_result_name => 'MULTIPLE_PASSED',
621 p_result_rule_type => 'I',
622 p_severity_level => NULL);
623 END IF;
624 hr_utility.set_location(l_proc, 240);
625 FOR c_rec IN c_inp_val('Rate', l_hr_ele_id)
626 LOOP
627 l_hr_iv_id := c_rec.input_value_id;
628 END LOOP;
629 hr_utility.set_location(l_proc, 250);
630 l_rr_id := pay_formula_results.ins_form_res_rule (
631 p_business_group_id => p_bg_id,
632 p_legislation_code => NULL,
633 p_effective_start_date => p_ele_eff_start_date,
634 p_effective_end_date => NULL,
635 p_status_processing_rule_id => l_stat_proc_rule_id,
636 p_element_type_id => l_hr_ele_id,
637 p_input_value_id => l_hr_iv_id,
638 p_result_name => 'RATE_PASSED',
639 p_result_rule_type => 'I',
640 p_severity_level => NULL);
641 hr_utility.set_location(l_proc, 260);
642 END IF;
643 hr_utility.set_location('Leaving : '||l_proc, 290);
644 -------------------------
645 RETURN l_pri_ele_type_id;
646 -------------------------
647 END create_ele_template_objects;
648 --=======================================================================
649 -- FUNCTION GET_OBJ_ID
650 --=======================================================================
651 FUNCTION get_obj_id (p_object_type IN VARCHAR2
652 ,p_object_name IN VARCHAR2
653 ,p_object_id IN NUMBER DEFAULT NULL )
654 RETURN NUMBER IS
655 --
656 l_object_id NUMBER := NULL;
657 l_proc VARCHAR2(60) := 'pay_us_earnings_template.get_obj_id';
658 --
659 CURSOR c_element IS -- Gets the element type id
660 SELECT element_type_id
661 FROM pay_element_types_f
662 WHERE element_name = p_object_name
663 AND business_group_id = g_bg_id;
664 --
665 CURSOR c_get_ipv_id IS -- Gets the input value id
666 SELECT piv.input_value_id
667 FROM pay_input_values_f piv
668 WHERE piv.NAME = p_object_name
669 AND piv.element_type_id = p_object_id
670 AND piv.business_group_id = g_bg_id;
671 --
672 CURSOR c_get_bal_id IS -- Gets the Balance type id
673 SELECT balance_type_id
674 FROM pay_balance_types pbt
675 WHERE pbt.balance_name = p_object_name
676 AND NVL(pbt.business_group_id, g_bg_id) = g_bg_id
677 AND NVL(pbt.legislation_code, g_legislation_code) = g_legislation_code;
678 --
679 BEGIN
680 hr_utility.set_location('Entering: '||l_proc, 10);
681 --
682 IF p_object_type = 'ELE' THEN
683 FOR c_rec IN c_element LOOP
684 l_object_id := c_rec.element_type_id; -- element id
685 END LOOP;
686 ELSIF p_object_type = 'BAL' THEN
687 FOR c_rec IN c_get_bal_id LOOP
688 l_object_id := c_rec.balance_type_id; -- balance id
689 END LOOP;
690 ELSIF p_object_type = 'IPV' THEN
691 FOR c_rec IN c_get_ipv_id LOOP
692 l_object_id := c_rec.input_value_id; -- input value id
693 END LOOP;
694 END IF;
695 hr_utility.set_location('Leaving: '||l_proc, 50);
696 --
697 RETURN l_object_id;
698 END get_obj_id;
699 --===========================================================================
700 -- Deletion procedure -- AG This can be reused.
701 --===========================================================================
702 PROCEDURE delete_ele_template_objects
703 (p_business_group_id IN NUMBER
704 ,p_ele_type_id IN NUMBER
705 ,p_ele_name IN VARCHAR2
706 ,p_effective_date IN DATE
707 ) IS
708 --
709 l_template_id NUMBER(9);
710 --
711 l_proc VARCHAR2(60) := 'pay_earnings_template.delete_ele_template_objects';
712 --
713 CURSOR c1 IS
714 SELECT template_id
715 FROM pay_element_templates
716 WHERE base_name = p_ele_name
717 AND business_group_id = p_business_group_id
718 AND template_type = 'U';
719 --
720 BEGIN
721 --
722 hr_utility.set_location('Entering :'||l_proc, 10);
723 FOR c1_rec IN c1 LOOP
724 l_template_id := c1_rec.template_id;
725 END LOOP;
726 --
727 pay_element_template_api.delete_user_structure
728 (p_validate => FALSE
729 ,p_drop_formula_packages => TRUE
730 ,p_template_id => l_template_id);
731 hr_utility.set_location('Leaving :'||l_proc, 50);
732 --
733 END delete_ele_template_objects;
734 END pay_us_earnings_template;