[Home] [Help]
PACKAGE BODY: APPS.PQP_EARNINGS_TEMPLATE
Source
1 PACKAGE BODY pqp_earnings_template AS
2 /* $Header: pqeetdrv.pkb 120.2 2006/08/29 10:50:00 jdevasah noship $ */
3
4
5 /*========================================================================
6 * Declare the global variables
7 *=======================================================================*/
8 g_bg_id number;
9 g_legislation_code varchar2(60);
10
11 /*========================================================================
12 * CREATE_ELE_TEMPLATE_OBJECTS
13 *=======================================================================*/
14 FUNCTION create_ele_template_objects
15 (p_ele_name in varchar2
16 ,p_ele_reporting_name in varchar2
17 ,p_ele_description in varchar2 default NULL
18 ,p_ele_classification in varchar2
19 ,p_ele_category in varchar2 default NULL
20 ,p_ele_processing_type in varchar2
21 ,p_ele_priority in number default NULL
22 ,p_ele_standard_link in varchar2 default 'N'
23 ,p_ele_ot_base in varchar2 default 'N'
24 ,p_flsa_hours in varchar2
25 ,p_ele_calc_ff_name in varchar2
26 ,p_sep_check_option in varchar2 default 'N'
27 ,p_dedn_proc in varchar2
28 ,p_reduce_regular in varchar2 default 'N'
29 ,p_ele_eff_start_date in date default NULL
30 ,p_ele_eff_end_date in date default NULL
31 ,p_supp_category in varchar2
32 ,p_legislation_code in varchar2
33 ,p_bg_id in number
34 ,p_termination_rule in varchar2 default 'F'
35 )
36 RETURN NUMBER IS
37 --
38 TYPE TypeIdNumber IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
39 TYPE TypeIdChar IS TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER;
40 t_bal_id TypeIdNumber;
41 t_form_id TypeIdNumber;
42 t_ipv_id TypeIdNumber;
43 t_def_val TypeIdChar;
44 t_we_flag TypeIdChar;
45 --
46 l_addl_bal_id number;
47 l_element_type_id number;
48 l_calc_type varchar2(30);
49 l_cat_bal_type_id number;
50 l_category_bal_name varchar2(60);
51 l_config2_amt char(1) := 'N';
52 l_config3_perc char(1) := 'N';
53 l_config4_hr char(1) := 'N';
54 l_config5_hrm char(1) := 'N';
55 l_hours_bal_id number;
56 l_ipv_id number;
57 l_multiple_entries char(1) := 'N';
58 l_ovn number;
59 l_pri_bal_id number;
60 l_pri_ele_type_id number;
61 l_repl_bal_id number;
62 l_si_ele_type_id number;
63 l_asf_ele_type_id number;
64 l_ssf_ele_type_id number;
65 l_source_template_id number;
66 l_supp_bal_id number;
67 l_template_id number;
68 --
69 l_proc varchar2(80) := 'pqp_earnings_template.create_ele_template_objects';
70
71 l_asg_gre_run_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
72
73 --
74 -- cursor to fetch the new element type id
75 --
76 CURSOR c_element (c_ele_name varchar2) is
77 SELECT element_type_id, object_version_number
78 FROM pay_shadow_element_types
79 WHERE template_id = l_template_id
80 AND element_name = c_ele_name;
81 --
82 -- cursor to get the template id
83 --
84 CURSOR c_template (l_template_name varchar2) is
85 SELECT template_id
86 FROM pay_element_templates
87 WHERE template_name = l_template_name
88 AND legislation_code = p_legislation_code
89 AND template_type = 'T'
90 AND business_group_id is NULL;
91 --
92 -- cursor to get the alien category balance name
93 --
94 CURSOR c_cat_bal_name IS
95 SELECT meaning
96 FROM hr_lookups
97 WHERE lookup_type = 'PQP_US_ALIEN_INCOME_BALANCE'
98 AND lookup_code = p_ele_category;
99 --
100 -- cursor to get the validation formula id
101 --
102 --CURSOR c_get_formula_id IS
103 --SELECT formula_id
104 --FROM ff_formulas_f
105 --WHERE formula_name = 'JURISDICTION_VALIDATION'
106 -- AND p_ele_eff_start_date BETWEEN
107 -- effective_start_date AND effective_end_date;
108
109
110 /* Added the following cursor - tmehra for the balance architecture changes
111 as per US Payroll Team request - 02-APR-03
112 */
113
114 CURSOR get_asg_gre_run_dim_id IS
115 SELECT balance_dimension_id
116 FROM pay_balance_dimensions
117 WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
118 AND legislation_code = 'US';
119
120 --
121 ------------------------------------------------------------------------------
122 -- MAIN FUNCTION
123 ------------------------------------------------------------------------------
124 BEGIN
125 hr_utility.set_location('Entering : '||l_proc, 10);
126 --
127 -- Set the global variables
128 --
129 g_bg_id := p_bg_id;
130 g_legislation_code := p_legislation_code;
131 --
132 -- Set session date and Source template id
133 --
134 pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
135 --
136 -- get the template id
137 --
138 FOR c_rec IN c_template('Alien Earning') LOOP
139 l_source_template_id := c_rec.template_id;
140 END LOOP;
141 --
142 hr_utility.set_location(l_proc, 20);
143 --------------------------------------------
144 -- Create the user Structure
145 --------------------------------------------
146 -- The Configuration Flex segments for the Exclusion Rules are as follows:
147 -- Config1 - Xclude SI and SF elements if ele_processing_type='N'
148 -- Config2 - Xclude objects if calc type is Not Amount
149 -- Config3 - Xclude objects if calc type is Not Percentage
150 -- Config4 - Xclude objects if calc type is Not Rate * Hours
151 -- Config5 - Xclude objects if calc type is Not Rate * Hours with a multiple
152 -- Config6 - Xclude objects if FLSA hours is not checked
153 -- Config7 - Xclude objects if overtime base is not checked
154 --
155 -- set the appropriate exclusion rules
156 --
157 IF SUBSTR(p_ele_calc_ff_name,1,11) = 'FLAT_AMOUNT' THEN
158 l_config2_amt := 'Y';
159 l_calc_type := 'FLAT_AMOUNT';
160 ELSIF SUBSTR(p_ele_calc_ff_name,1,26) = 'PERCENTAGE_OF_REG_EARNINGS' THEN
161 l_config3_perc := 'Y';
162 l_calc_type := 'PERCENTAGE';
163 ELSIF SUBSTR(p_ele_calc_ff_name,1,21) = 'HOURS_X_RATE_MULTIPLE' THEN
164 l_config4_hr := 'Y';
165 l_config5_hrm := 'Y';
166 l_calc_type := 'HOURS_X_RATE';
167 ELSIF SUBSTR(p_ele_calc_ff_name,1,12) = 'HOURS_X_RATE' THEN
168 l_config4_hr := 'Y';
169 l_calc_type := 'HOURS_X_RATE';
170 END IF;
171 --
172 hr_utility.set_location(l_proc, 60);
173 --
174 pay_element_template_api.create_user_structure
175 (p_validate => false
176 ,p_effective_date => p_ele_eff_start_date
177 ,p_business_group_id => p_bg_id
178 ,p_source_template_id => l_source_template_id
179 ,p_base_name => p_ele_name
180 ,p_base_processing_priority => p_ele_priority
181 ,p_configuration_information1 => p_ele_processing_type
182 ,p_configuration_information2 => l_config2_amt
183 ,p_configuration_information3 => l_config3_perc
184 ,p_configuration_information4 => l_config4_hr
185 ,p_configuration_information5 => l_config5_hrm
186 ,p_configuration_information6 => p_flsa_hours
187 ,p_configuration_information7 => p_ele_ot_base
188 ,p_template_id => l_template_id
189 ,p_object_version_number => l_ovn );
190 --
191 hr_utility.set_location(l_proc, 80);
192 -----------------------------------------------------------
193 -- Update Base shadow Element with user-specified details
194 -----------------------------------------------------------
195 FOR c_rec in c_element ( p_ele_name ) LOOP
196 l_element_type_id := c_rec.element_type_id;
197 l_ovn := c_rec.object_version_number;
198 END LOOP;
199 --
200 IF p_ele_processing_type = 'N' THEN
201 l_multiple_entries := 'Y';
202 END IF;
203 --
204 pay_shadow_element_api.update_shadow_element
205 (p_validate => false
206 ,p_effective_date => p_ele_eff_start_date
207 ,p_element_type_id => l_element_type_id
208 ,p_description => p_ele_description
209 ,p_reporting_name => p_ele_reporting_name
210 ,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
211 ,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
212 ,p_multiple_entries_allowed_fla => l_multiple_entries
213 ,p_post_termination_rule => p_termination_rule
214 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
215 ,p_element_information8 => p_ele_ot_base
216 ,p_element_information11 => p_flsa_hours
217 ,p_object_version_number => l_ovn
218 );
219 hr_utility.set_location(l_proc, 90);
220 ------------------------------------------------------------------
221 -- Update user-specified details on Supp Special Features Element.
222 ------------------------------------------------------------------
223 FOR c1_rec in c_element ( p_ele_name||' Supp SF') LOOP
224 l_element_type_id := c1_rec.element_type_id;
225 l_ovn := c1_rec.object_version_number;
226 --
227 pay_shadow_element_api.update_shadow_element
228 (p_validate => false
229 ,p_effective_date => p_ele_eff_start_date
230 ,p_element_type_id => l_element_type_id
231 ,p_description => 'Supp. SF element for:'||p_ele_name
232 ,p_reporting_name => NVL(p_ele_reporting_name,p_ele_name)||':Supp SF' -- bug 5470399
233 ,p_post_termination_rule => p_termination_rule
234 ,p_element_information1 => nvl(p_supp_category, hr_api.g_varchar2)
235 ,p_element_information8 => p_ele_ot_base
236 ,p_object_version_number => l_ovn
237 );
238 END LOOP;
239 hr_utility.set_location(l_proc, 100);
240 -------------------------------------------------------------------
241 -- Update user-specified details on Alien Special Features Element.
242 -------------------------------------------------------------------
243 FOR c1_rec in c_element ( p_ele_name||' Alien SF') LOOP
244 l_element_type_id := c1_rec.element_type_id;
245 l_ovn := c1_rec.object_version_number;
246 --
247 pay_shadow_element_api.update_shadow_element
248 (p_validate => false
249 ,p_effective_date => p_ele_eff_start_date
250 ,p_element_type_id => l_element_type_id
251 ,p_description => 'Alien SF element for:'||p_ele_name
252 ,p_post_termination_rule => p_termination_rule
253 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
254 ,p_element_information8 => p_ele_ot_base
255 ,p_object_version_number => l_ovn
256 );
257 END LOOP;
258 hr_utility.set_location(l_proc, 110);
259 --------------------------------------------------------------------
260 -- Update user-specified Classification Special Inputs if it exists.
261 --------------------------------------------------------------------
262 IF p_ele_processing_type = 'R' THEN
263 FOR c1_rec in c_element ( p_ele_name||' Special Inputs' ) LOOP
264 l_element_type_id := c1_rec.element_type_id;
265 l_ovn := c1_rec.object_version_number;
266 END LOOP;
267 pay_shadow_element_api.update_shadow_element
268 (p_validate => false
269 ,p_effective_date => p_ele_eff_start_date
270 ,p_element_type_id => l_element_type_id
271 ,p_description => 'Generated Special Inputs element for:'
272 ||p_ele_name
273 ,p_post_termination_rule => p_termination_rule
274 ,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
275 ,p_element_information8 => p_ele_ot_base
276 ,p_object_version_number => l_ovn
277 );
278 END IF;
279 --
280 hr_utility.set_location(l_proc, 120);
281 ------------------------------------------------------------
282 -- Generate Core Objects
283 ------------------------------------------------------------
284 pay_element_template_api.generate_part1
285 (p_validate => false
286 ,p_effective_date => p_ele_eff_start_date
287 ,p_hr_only => false
288 ,p_hr_to_payroll => false
289 ,p_template_id => l_template_id);
290 --
291 hr_utility.set_location(l_proc, 130);
292 --
293 -- Add logic to generate part2 only if payroll is installed
294 --
295 pay_element_template_api.generate_part2
296 (p_validate => false
297 ,p_effective_date => p_ele_eff_start_date
298 ,p_template_id => l_template_id);
299 hr_utility.set_location(l_proc, 140);
300 --
301 -------------------------------------------------------------------
302 -- Get Element and Balance Id's to update the Further Information
303 -------------------------------------------------------------------
304 l_pri_bal_id := get_obj_id('BAL', p_ele_name);
305 l_addl_bal_id := get_obj_id('BAL', p_ele_name||' Additional Amount');
306 l_repl_bal_id := get_obj_id('BAL', p_ele_name||' Replacement Amount');
307 l_hours_bal_id := get_obj_id('BAL', p_ele_name||' Hours');
308 l_supp_bal_id := get_obj_id('BAL', p_ele_name||' Supp');
309 l_pri_ele_type_id := get_obj_id('ELE', p_ele_name);
310 l_si_ele_type_id := get_obj_id('ELE',p_ele_name||' Special Inputs');
311 l_ssf_ele_type_id := get_obj_id('ELE',p_ele_name||' Supp SF');
312 l_asf_ele_type_id := get_obj_id('ELE',p_ele_name||' Alien SF');
313 --
314 UPDATE pay_element_types_f
315 SET element_information10 = l_pri_bal_id
316 ,element_information12 = l_hours_bal_id
317 ,element_information13 = p_reduce_regular
318 ,element_information14 = p_supp_category
319 ,element_information16 = l_addl_bal_id
320 ,element_information17 = l_repl_bal_id
321 ,element_information18 = l_si_ele_type_id
322 ,element_information19 = l_ssf_ele_type_id
323 ,element_information20 = l_calc_type
324 WHERE element_type_id = l_pri_ele_type_id
325 AND business_group_id = p_bg_id;
326
327
328 /* Get the _ASG_GRE_RUN dimension id */
329
330 FOR crec IN get_asg_gre_run_dim_id
331 LOOP
332
333 l_asg_gre_run_dim_id := crec.balance_dimension_id;
334
335 END LOOP;
336
337 /* The following update statement has been added by tmehra
338 for the balance architecture changes as per the US Payroll Team
339 */
340
341 /*This statement is commented as per US Payroll team advice
342 due to performance issue */
343 /* Bug 3651755 : This update is not required. The category def will take
344 care of creating balance with save run balances to 'Yes'*/
345 /*UPDATE pay_defined_balances
346 SET save_run_balance = 'Y'
347 WHERE balance_type_id = l_pri_bal_id
348 AND balance_dimension_id = l_asg_gre_run_dim_id
349 AND business_group_id = p_bg_id;*/
350 --
351
352 --
353 hr_utility.set_location(l_proc, 150);
354 --------------------------------------------------------------------
355 -- Update the Further Information for the Alien Supplemental element
356 --------------------------------------------------------------------
357 l_supp_bal_id := get_obj_id('BAL', p_ele_name||' Supp');
358 --
359 UPDATE pay_element_types_f
360 SET element_information10 = l_supp_bal_id
361 WHERE element_type_id = l_ssf_ele_type_id
362 AND business_group_id = p_bg_id;
363
364 /* The following update statement has been added by tmehra
365 for the balance architecture changes as per the US Payroll Team
366 */
367
368 /*This statement is commented as per US Payroll team advice
369 due to performance issue */
370 /* Bug 3651755 : This update is not required. The category def will take
371 care of creating balance with save run balances to 'Yes'*/
372
373 /* UPDATE pay_defined_balances
374 SET save_run_balance = 'Y'
375 WHERE balance_type_id = l_supp_bal_id
376 AND balance_dimension_id = l_asg_gre_run_dim_id
377 AND business_group_id = p_bg_id;*/
378
379
380 -----------------------------------------------------
381 -- Create balance feeds for the Supp category
382 -----------------------------------------------------
383 -- creating it here and not in the templates as it is
384 -- illegal to create these feeds according to core rules.
385 --
386 FOR c_rec in c_cat_bal_name LOOP
387 l_category_bal_name := c_rec.meaning;
388 END LOOP;
389 --
390 t_ipv_id(1) := get_obj_id('IPV', 'Pay Value', l_pri_ele_type_id);
391 t_bal_id(1) := get_obj_id('BAL', l_category_bal_name); -- category balance
392 t_ipv_id(2) := get_obj_id('IPV', 'Alien CITY', l_ssf_ele_type_id);
393 t_bal_id(2) := get_obj_id('BAL', 'Supp Earnings for CITY');
394 t_ipv_id(3) := t_ipv_id(2);
395 t_bal_id(3) := get_obj_id('BAL', 'Supp Earnings CITY');
396 t_ipv_id(4) := t_ipv_id(2);
397 t_bal_id(4) := get_obj_id('BAL', 'Supp Earnings for NWCITY');
398 t_ipv_id(5) := get_obj_id('IPV', 'Alien COUNTY', l_ssf_ele_type_id);
399 t_bal_id(5) := get_obj_id('BAL', 'Supp Earnings for COUNTY');
400 t_ipv_id(6) := t_ipv_id(5);
401 t_bal_id(6) := get_obj_id('BAL', 'Supp Earnings for NWCOUNTY');
402 t_ipv_id(7) := get_obj_id('IPV', 'Alien FUTA', l_ssf_ele_type_id);
403 t_bal_id(7) := get_obj_id('BAL', 'Supplemental Earnings for FUTA');
404 t_ipv_id(8) := get_obj_id('IPV', 'Alien Medicare', l_ssf_ele_type_id);
405 t_bal_id(8) := get_obj_id('BAL', 'Supplemental Earnings for Medicare');
406 t_ipv_id(9) := get_obj_id('IPV', 'Alien SCHOOL', l_ssf_ele_type_id);
407 t_bal_id(9) := get_obj_id('BAL', 'Supp Earnings for SCHOOL');
408 t_ipv_id(10) := t_ipv_id(9);
409 t_bal_id(10) := get_obj_id('BAL', 'Supp Earnings for NWSCHOOL');
410 t_ipv_id(11) := get_obj_id('IPV', 'Alien SDI', l_ssf_ele_type_id);
411 t_bal_id(11) := get_obj_id('BAL', 'Supplemental Earnings for SDI');
412 t_ipv_id(12) := get_obj_id('IPV', 'Alien SS', l_ssf_ele_type_id);
413 t_bal_id(12) := get_obj_id('BAL', 'Supplemental Earnings for SS');
414 t_ipv_id(13) := get_obj_id('IPV', 'Alien SUI', l_ssf_ele_type_id);
415 t_bal_id(13) := get_obj_id('BAL', 'Supplemental Earnings for SUI');
416 t_ipv_id(14) := get_obj_id('IPV', 'Alien SIT', l_ssf_ele_type_id);
417 t_bal_id(14) := get_obj_id('BAL', 'Supplemental Earnings for SIT');
418 t_ipv_id(15) := t_ipv_id(14);
419 t_bal_id(15) := get_obj_id('BAL', 'Supplemental Earnings for NWSIT');
420 t_ipv_id(16) := get_obj_id('IPV', 'Alien SUPP', l_ssf_ele_type_id);
421 t_bal_id(16) := get_obj_id('BAL', 'Supplemental Earnings');
422 t_ipv_id(17) := get_obj_id('IPV', 'Alien SIT 1042s', l_ssf_ele_type_id);
423 t_bal_id(17) := get_obj_id('BAL', 'Alien 1042s for SIT');
424 t_ipv_id(18) := get_obj_id('IPV', 'Alien SIT 1042s', l_ssf_ele_type_id);
425 t_bal_id(18) := get_obj_id('BAL', 'Alien 1042s for NWSIT');
426 --
427 hr_utility.set_location(l_proc, 160);
428 FOR i in 1..18 LOOP
429 hr_balances.ins_balance_feed(
430 p_option => 'INS_MANUAL_FEED',
431 p_input_value_id => t_ipv_id(i),
432 p_element_type_id => NULL,
433 p_primary_classification_id => NULL,
434 p_sub_classification_id => NULL,
435 p_sub_classification_rule_id => NULL,
436 p_balance_type_id => t_bal_id(i),
437 p_scale => '1',
438 p_session_date => p_ele_eff_start_date,
439 p_business_group => p_bg_id,
440 p_legislation_code => NULL,
441 p_mode => 'USER');
442 END LOOP;
443 hr_utility.set_location(l_proc, 170);
444 -------------------------------------------------------------------
445 -- Update Input values with default values, validation formula etc.
446 -------------------------------------------------------------------
447 t_ipv_id(1) := get_obj_id('IPV', 'Deduction Processing', l_pri_ele_type_id);
448 t_form_id(1) := NULL;
449 t_we_flag(1) := NULL;
450 t_def_val(1) := p_dedn_proc;
451 t_ipv_id(2) := get_obj_id('IPV', 'Separate Check', l_pri_ele_type_id);
452 t_form_id(2) := NULL;
453 t_we_flag(2) := NULL;
454 t_def_val(2) := p_sep_check_option;
455 --
456 -- Not using Jurisdiction as the functionality is removed currently
457 --
458 -- t_ipv_id(3) := get_obj_id('IPV', 'Jurisdiction', l_pri_ele_type_id);
459 -- FOR c_rec in c_get_formula_id LOOP
460 -- t_form_id(3) := c_rec.formula_id; -- get the jurisdiction val formula
461 -- END LOOP;
462 -- t_we_flag(3) := 'E'; -- warning or error flag
463 -- t_def_val(3) := NULL; -- default value
464 --
465 hr_utility.set_location(l_proc, 170);
466 FOR i in 1..2 LOOP
467 UPDATE pay_input_values_f
468 SET formula_id = t_form_id(i)
469 ,warning_or_error = t_we_flag(i)
470 ,default_value = t_def_val(i)
471 WHERE input_value_id = t_ipv_id(i);
472 END LOOP;
473 -------------------------------------------------------------------
474 -- Create the balance feeds for FLSA Hours and Reduce Regular
475 -------------------------------------------------------------------
476 hr_utility.set_location(l_proc, 180);
477 add_flsa_reduce_reg_feeds
478 (p_ele_ot_base => p_ele_ot_base
479 ,p_flsa_hours => p_flsa_hours
480 ,p_reduce_regular => p_reduce_regular
481 ,p_pri_ele_type_id => l_pri_ele_type_id
482 ,p_ssf_ele_type_id => l_ssf_ele_type_id
483 ,p_asf_ele_type_id => l_asf_ele_type_id
484 ,p_ele_eff_start_date => p_ele_eff_start_date );
485 --
486 hr_utility.set_location('Leaving: '||l_proc, 200);
487 -------------------------
488 RETURN l_pri_ele_type_id;
489 -------------------------
490 END create_ele_template_objects;
491 --
492 --
493 --=======================================================================
494 -- FUNCTION GET_OBJ_ID
495 --=======================================================================
496 FUNCTION get_obj_id (p_object_type in varchar2
497 ,p_object_name in varchar2
498 ,p_object_id in number default NULL )
499 RETURN NUMBER is
500 --
501 l_object_id NUMBER := NULL;
502 l_proc VARCHAR2(60) := 'pqp_earnings_template.get_obj_id';
503 --
504 CURSOR c_element IS -- Gets the element type id
505 SELECT element_type_id
506 FROM pay_element_types_f
507 WHERE element_name = p_object_name
508 AND business_group_id = g_bg_id;
509 --
510 CURSOR c_get_ipv_id IS -- Gets the input value id
511 SELECT piv.input_value_id
512 FROM pay_input_values_f piv
513 WHERE piv.name = p_object_name
514 AND piv.element_type_id = p_object_id
515 AND piv.business_group_id = g_bg_id;
516 --
517 CURSOR c_get_bal_id IS -- Gets the Balance type id
518 SELECT balance_type_id
519 FROM pay_balance_types pbt
520 WHERE pbt.balance_name = p_object_name
521 AND NVL(pbt.business_group_id, g_bg_id) = g_bg_id
522 AND NVL(pbt.legislation_code, g_legislation_code) = g_legislation_code;
523 --
524 BEGIN
525 hr_utility.set_location('Entering: '||l_proc, 10);
526 --
527 IF p_object_type = 'ELE' then
528 FOR c_rec in c_element LOOP
529 l_object_id := c_rec.element_type_id; -- element id
530 END LOOP;
531 ELSIF p_object_type = 'BAL' THEN
532 FOR c_rec in c_get_bal_id LOOP
533 l_object_id := c_rec.balance_type_id; -- balance id
534 END LOOP;
535 ELSIF p_object_type = 'IPV' THEN
536 FOR c_rec in c_get_ipv_id LOOP
537 l_object_id := c_rec.input_value_id; -- input value id
538 END LOOP;
539 END IF;
540 hr_utility.set_location('Leaving: '||l_proc, 50);
541 --
542 RETURN l_object_id;
543 END get_obj_id;
544 --===========================================================================
545 -- Add_Flsa_Reduce_Reg_Feeds procedure
546 --===========================================================================
547 PROCEDURE add_flsa_reduce_reg_feeds
548 (p_ele_ot_base in varchar2
549 ,p_flsa_hours in varchar2
550 ,p_reduce_regular in varchar2
551 ,p_pri_ele_type_id in number
552 ,p_ssf_ele_type_id in number
553 ,p_asf_ele_type_id in number
554 ,p_ele_eff_start_date in date
555 ) IS
556 --
557 l_proc VARCHAR2(60):= 'pqp_earnings_template.add_flsa_reduce_reg_feeds';
558 TYPE TypeIdNumber IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
559 t_bal_id TypeIdNumber;
560 t_ipv_id TypeIdNumber;
561 t_scale TypeIdNumber;
562 l_count number := 0;
563 l_ipv_id number;
564 --
565 CURSOR get_reg_feeds IS
566 SELECT distinct pbf.balance_type_id
567 FROM pay_balance_feeds_f pbf,
568 pay_balance_types pbt
569 WHERE p_ele_eff_start_date BETWEEN pbf.effective_start_date
570 AND pbf.effective_end_date
571 AND ((pbt.business_group_id is NULL AND pbt.legislation_code = 'US')
572 OR (pbt.business_group_id = g_bg_id AND pbt.legislation_code is NULL))
573 AND pbt.balance_name not in ('FLSA Earnings', 'FLSA Hours')
574 AND pbt.balance_type_id = pbf.balance_type_id
575 AND pbf.input_value_id IN
576 (SELECT piv.input_value_id
577 FROM pay_element_types_f pet,
578 pay_input_values_f piv
579 WHERE pet.element_name IN ('Regular Salary', 'Regular Wages')
580 AND p_ele_eff_start_date BETWEEN pet.effective_start_date
581 AND pet.effective_end_date
582 AND pet.business_group_id is NULL
583 AND pet.legislation_code = 'US'
584 AND piv.element_type_id = pet.element_type_id
585 AND piv.name = 'Pay Value'
586 AND p_ele_eff_start_date BETWEEN piv.effective_start_date
587 AND piv.effective_end_date
588 AND piv.business_group_id is NULL
589 AND piv.legislation_code = 'US') ;
590 --
591 BEGIN
592 --
593 hr_utility.set_location(l_proc, 60);
594 IF p_flsa_hours = 'Y' THEN
595 -- create balance feeds for FLSA hours
596 hr_utility.set_location(l_proc, 60);
597 l_count := l_count + 1;
598 t_ipv_id(l_count) := get_obj_id('IPV', 'Hours', p_pri_ele_type_id);
599 t_bal_id(l_count) := get_obj_id('BAL', 'FLSA Hours');
600 t_scale(l_count) := 1;
601 END IF;
602 IF p_ele_ot_base = 'Y' THEN
603 -- create balance feeds for FLSA Earnings
604 l_count := l_count + 1;
605 t_ipv_id(l_count) := get_obj_id('IPV', 'Pay Value', p_pri_ele_type_id);
606 t_bal_id(l_count) := get_obj_id('BAL', 'FLSA Earnings');
607 t_scale(l_count) := 1;
608 --
609 l_count := l_count + 1;
610 t_ipv_id(l_count) := get_obj_id('IPV', 'Pay Value', p_ssf_ele_type_id);
611 t_bal_id(l_count) := get_obj_id('BAL', 'FLSA Earnings');
612 t_scale(l_count) := 1;
613 END IF;
614 IF p_reduce_regular = 'Y' THEN
615 -- create balance feeds for Reduce Reg Hours input value
616 l_count := l_count + 1;
617 t_ipv_id(l_count) := get_obj_id('IPV', 'Reduce Reg Hours', p_asf_ele_type_id);
618 t_bal_id(l_count) := get_obj_id('BAL', 'Regular Hours Worked');
619 t_scale(l_count) := -1;
620 --
621 l_count := l_count + 1;
622 t_ipv_id(l_count) := get_obj_id('IPV', 'Reduce Reg Hours', p_asf_ele_type_id);
623 t_bal_id(l_count) := get_obj_id('BAL', 'Regular Salary Hours');
624 t_scale(l_count) := -1;
625 --
626 l_count := l_count + 1;
627 t_ipv_id(l_count) := get_obj_id('IPV', 'Reduce Reg Hours', p_asf_ele_type_id);
628 t_bal_id(l_count) := get_obj_id('BAL', 'Regular Wages Hours');
629 t_scale(l_count) := -1;
630 --
631 -- create balance feeds for Reduce Reg Earnings input value
632 --
633 l_ipv_id := get_obj_id('IPV', 'Reduce Reg Pay', p_asf_ele_type_id);
634 FOR c_rec1 IN get_reg_feeds LOOP
635 l_count := l_count + 1;
636 t_ipv_id(l_count) := l_ipv_id;
637 t_bal_id(l_count) := c_rec1.balance_type_id;
638 t_scale(l_count) := -1;
639 END LOOP;
640 --
641 END IF;
642 --
643 FOR i in 1..l_count LOOP
644 hr_balances.ins_balance_feed(
645 p_option => 'INS_MANUAL_FEED',
646 p_input_value_id => t_ipv_id(i),
647 p_element_type_id => NULL,
648 p_primary_classification_id => NULL,
649 p_sub_classification_id => NULL,
650 p_sub_classification_rule_id => NULL,
651 p_balance_type_id => t_bal_id(i),
652 p_scale => t_scale(i),
653 p_session_date => p_ele_eff_start_date,
654 p_business_group => g_bg_id,
655 p_legislation_code => NULL,
656 p_mode => 'USER');
657 END LOOP;
658 hr_utility.set_location(l_proc, 70);
659 --
660 END add_flsa_reduce_reg_feeds;
661 --===========================================================================
662 -- Deletion procedure
663 --===========================================================================
664 PROCEDURE delete_ele_template_objects
665 (p_business_group_id in number
666 ,p_ele_type_id in number
667 ,p_ele_name in varchar2
668 ,p_effective_date in date
669 ) IS
670 --
671 l_template_id NUMBER(9);
672 --
673 l_proc VARCHAR2(60) := 'pqp_earnings_template.delete_ele_template_objects';
674 --
675 CURSOR c1 is
676 SELECT template_id
677 FROM pay_template_core_objects
678 WHERE core_object_type = 'ET'
679 AND core_object_id = p_ele_type_id;
680
681 --
682 BEGIN
683 --
684 hr_utility.set_location('Entering :'||l_proc, 10);
685 for c1_rec in c1 loop
686 l_template_id := c1_rec.template_id;
687 end loop;
688 --
689 pay_element_template_api.delete_user_structure
690 (p_validate => false
691 ,p_drop_formula_packages => true
692 ,p_template_id => l_template_id);
693 hr_utility.set_location('Leaving :'||l_proc, 50);
694 --
695 END delete_ele_template_objects;
696 --
697 END pqp_earnings_template;