[Home] [Help]
PACKAGE BODY: APPS.HR_USER_DEDN_DRV
Source
1 package body hr_user_dedn_drv as
2 /* $Header: pyusddwp.pkb 115.4 2004/01/07 07:04:49 kaverma ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9 */
10
11 /*
12 ******************************************************************
13 * *
14 * Copyright (C) 1993 Oracle Corporation. *
15 * All rights reserved. *
16 * *
17 * This material has been provided pursuant to an agreement *
18 * containing restrictions on its use. The material is also *
19 * protected by copyright law. No part of this material may *
20 * be copied or distributed, transmitted or transcribed, in *
21 * any form or by any means, electronic, mechanical, magnetic, *
22 * manual, or otherwise, or disclosed to third parties without *
23 * the express written permission of Oracle Corporation, *
24 * 500 Oracle Parkway, Redwood City, CA, 94065. *
25 * *
26 ******************************************************************
27
28 Name : hr_user_init_dedn_drv
29 Filename : pyusddwp.pkb
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ---- ---- ---- ------ -----------
34 26-APR-98 PMADORE 1.0 First Created.
35 Initial Procedures
36 25-Mar-02 EKIM 115.1 2276457 Added p_termination_rule
37 to ins_deduction_template
38 26-Mar-02 ekim 115.2 Added commit.
39 07-Jan-04 kaverma 115.3 3349575 Modified query in insert_formula
40 to remove Full Table Scan
41 07-Jan-04 kaverma 115.3 3349575 Modified query in insert_formula
42 to correct the join condition
43
44
45
46 */
47
48
49
50 /* Cursor to get input_value_id and element_type_id given the names of the objects */
51
52 CURSOR csr_input_id(p_ele_name VARCHAR2
53 , p_inp_val_name VARCHAR2
54 , p_bg_id NUMBER
55 , p_eff_start_date DATE) IS
56 SELECT piv.input_value_id
57 ,piv.element_type_id
58 FROM pay_element_types_f pet
59 ,pay_input_values_f piv
60 WHERE pet.element_name = p_ele_name
61 AND pet.element_type_id = piv.element_type_id
62 AND piv.name = p_inp_val_name
63 AND pet.business_group_id +0 = p_bg_id
64 AND p_eff_start_date between pet.effective_start_date
65 AND pet.effective_end_date;
66
67 ------------------------- insert_formula -----------------------
68
69 FUNCTION insert_formula ( p_ff_ele_name in varchar2,
70 p_ff_formula_name in varchar2,
71 p_ele_formula_name in varchar2,
72 p_ff_bg_id in number,
73 p_eff_start_date in date,
74 p_eff_end_date in date)
75 RETURN number IS
76
77 /* Retrieves template formula text, replaces <ELE_NAME> in the formula with element_name
78 * passed in and inserts the formula.
79 */
80 -- local vars
81 r_formula_id number; -- Return var
82 --
83 r_description VARCHAR2(240);
84 r_skeleton_formula_text VARCHAR2(32000);
85 r_skeleton_formula_type_id NUMBER(9);
86 r_ele_formula_text VARCHAR2(32000);
87 r_ele_formula_name VARCHAR2(80);
88 r_ele_name VARCHAR2(80);
89
90 BEGIN
91 hr_utility.set_location('pyusddwp.insert_formula',10);
92 SELECT FF.formula_text, FF.formula_type_id, FF.description
93 INTO r_skeleton_formula_text, r_skeleton_formula_type_id, r_description
94 FROM ff_formulas_f FF
95 WHERE FF.formula_name = p_ff_formula_name
96 AND FF.business_group_id IS NULL
97 AND FF.legislation_code = 'US'
98 AND p_eff_start_date between FF.effective_start_date and FF.effective_end_date
99 AND FF.formula_id >= 0; -- Bug#3349575
100
101 -- Replace element name placeholders with current element name:
102 hr_utility.set_location('pyusddwp.insert_formula',15);
103 r_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
104
105 r_ele_formula_name := SUBSTR(REPLACE(LTRIM(RTRIM(UPPER(p_ele_formula_name))),' ','_'), 1, 80);
106
107 r_ele_formula_text := REPLACE( r_skeleton_formula_text,
108 '<ELE_NAME>',
109 r_ele_name);
110 --
111
112
113
114 --
115 -- Insert the new formula into current business goup:
116 -- Get new id
117
118 hr_utility.set_location('pyusddwp.insert_formula',30);
119 SELECT ff_formulas_s.nextval
120 INTO r_formula_id
121 FROM sys.dual;
122
123 hr_utility.set_location('pyusddwp.insert_formula',40);
124 INSERT INTO ff_formulas_f (
125 FORMULA_ID,
126 EFFECTIVE_START_DATE,
127 EFFECTIVE_END_DATE,
128 BUSINESS_GROUP_ID,
129 LEGISLATION_CODE,
130 FORMULA_TYPE_ID,
131 FORMULA_NAME,
132 DESCRIPTION,
133 FORMULA_TEXT,
134 STICKY_FLAG,
135 LAST_UPDATE_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN,
138 CREATED_BY,
139 CREATION_DATE)
140 values (
141 r_formula_id,
142 p_eff_start_date,
143 p_eff_end_date,
144 p_ff_bg_id,
145 NULL,
146 r_skeleton_formula_type_id,
147 r_ele_formula_name,
148 r_description,
149 r_ele_formula_text,
150 'N',
151 NULL,
152 NULL,
153 NULL,
154 -1,
155 p_eff_start_date);
156
157 RETURN r_formula_id;
158
159 END insert_formula;
160 ----------------------- END insert_formula ---------------------
161
162 ------------------------- do_employer_match -----------------------
163
164 PROCEDURE do_employer_match (p_ename IN VARCHAR2
165 , p_bg_id IN NUMBER
166 , p_start_date IN DATE
167 , p_end_date IN DATE
168 , p_leg_code IN VARCHAR2
169 , p_er_ename IN VARCHAR2) IS
170
171 v_formula_id number;
172 v_stat_proc_rule_id number;
173 v_inpval_id number;
174 v_frr_iv_ele_id number;
175 v_fres_rule_id number;
176 v_inpval_name varchar2(80):= 'Pay Value';
177 v_formula_name varchar2(80);
178
179 /* Creates the 'Employer Match' formula for an element, creates the Status Processing Rule
180 * and inserts the Result Rule for the formula
181 */
182
183 BEGIN
184 hr_utility.set_location('pyusddwp.do_employer_match',10);
185 v_formula_name := p_er_ename;
186
187 v_formula_id:= insert_formula (
188 p_ff_ele_name => p_ename,
189 p_ff_formula_name => 'EMPLOYER_MATCH_TEMPLATE',
190 p_ele_formula_name=> v_formula_name,
191 p_ff_bg_id => p_bg_id,
192 p_eff_start_date => p_start_date,
193 p_eff_end_date => p_end_date);
194
195 hr_utility.set_location('pyusddwp.do_employer_match',20);
196 -- get input values, element_id
197 OPEN csr_input_id(p_er_ename,v_inpval_name,p_bg_id, p_start_date);
198 FETCH csr_input_id INTO
199 v_inpval_id
200 ,v_frr_iv_ele_id;
201 IF csr_input_id%FOUND THEN
202 hr_utility.set_location('pyusddwp.do_employer_match',30);
203 v_stat_proc_rule_id :=
204 pay_formula_results.ins_stat_proc_rule (
205 p_business_group_id => p_bg_id,
206 p_legislation_code => NULL,
207 p_legislation_subgroup => p_leg_code,
208 p_effective_start_date => p_start_date,
209 p_effective_end_date => p_end_date,
210 p_element_type_id => v_frr_iv_ele_id,
211 p_assignment_status_type_id => NULL,
212 p_formula_id => v_formula_id,
213 p_processing_rule => 'P');
214
215
216 hr_utility.set_location('pyusddwp.do_employer_match',40);
217
218 v_fres_rule_id := pay_formula_results.ins_form_res_rule (
219 p_business_group_id => p_bg_id,
220 p_legislation_code => NULL,
221 p_legislation_subgroup => p_leg_code,
222 p_effective_start_date => p_start_date,
223 p_effective_end_date => p_end_date,
224 p_status_processing_rule_id => v_stat_proc_rule_id,
225 p_input_value_id => v_inpval_id,
226 p_result_name => 'ER_Match',
227 p_result_rule_type => 'D',
228 p_severity_level => NULL,
229 p_element_type_id => v_frr_iv_ele_id);
230
231 hr_utility.set_location('pyusddwp.do_employer_match',50);
232 END IF;
233 CLOSE csr_input_id;
234
235 END do_employer_match;
236
237 ----------------------- END do_employer_match ---------------------
238
239
240 ---------------------- Begin do_passthru_feed --------------------
241 PROCEDURE do_passthru_feed( p_src_ele IN VARCHAR2
242 , p_bg_id IN NUMBER
243 , p_src_iv IN VARCHAR2
244 , p_targ_bal IN VARCHAR2
245 , p_eff_start_date IN DATE
246 , p_eff_end_date IN DATE) IS
247
248
249 /* Create the balance feed for the "overlimit" balance which is checked by the aftertax
250 * components
251 */
252
253 l_row rowid;
254 l_balance_feed_id number;
255 l_balance_type_id number;
256 l_inpval_id number;
257 l_dummy number;
258
259 CURSOR csr_bal (p_bal_name IN VARCHAR2)IS
260 SELECT balance_type_id
261 FROM pay_balance_types
262 WHERE balance_name = p_bal_name
263 AND business_group_id + 0 = p_bg_id;
264
265
266 BEGIN
267 hr_utility.set_location('pyusddwp.do_passthru_feed',10);
268 OPEN csr_input_id(p_src_ele,p_src_iv,p_bg_id,p_eff_start_date);
269 FETCH csr_input_id INTO
270 l_inpval_id
271 ,l_dummy;
272 IF csr_input_id%NOTFOUND THEN
273 hr_utility.set_location('pyusddwp.do_passthru_feed',20);
274 ELSE
275 hr_utility.set_location('pyusddwp.do_passthru_feed',30);
276 OPEN csr_bal(p_targ_bal);
277 FETCH csr_bal INTO l_balance_type_id;
278 IF csr_bal%FOUND THEN
279 pay_balance_feeds_f_pkg.insert_row (l_row,
280 l_balance_feed_id,
281 p_eff_start_date,
282 p_eff_end_date,
283 p_bg_id,
284 g_template_leg_code,
285 l_balance_type_id,
286 l_inpval_id,
287 1,
288 g_template_leg_subgroup);
289 ELSE
290 hr_utility.set_location('pyusddwp.do_passthru_feed',40);
291 END IF;
292 CLOSE csr_bal;
293 hr_utility.set_location('pyusddwp.do_passthru_feed',50);
294
295 END IF; -- input _id
296 CLOSE csr_input_id;
297 hr_utility.set_location('pyusddwp.do_passthru_feed',60);
298
299 END do_passthru_feed;
300 ----------------------- END do_passthru_feed ---------------------
301 -------------------- BEGIN Main Driver Program -------------------
302
303 FUNCTION ins_deduction_template (
304 p_ele_name in varchar2,
305 p_ele_reporting_name in varchar2,
306 p_ele_description in varchar2 default NULL,
307 p_ele_classification in varchar2,
308 p_ben_class_id in number,
309 p_ele_category in varchar2 default NULL,
310 p_ele_processing_type in varchar2,
311 p_ele_priority in number default NULL,
312 p_ele_standard_link in varchar2 default 'N',
313 p_ele_proc_runtype in varchar2,
314 p_ele_start_rule in varchar2,
315 p_ele_stop_rule in varchar2,
316 p_ele_ee_bond in varchar2 default 'N',
317 p_ele_amount_rule in varchar2,
318 p_ele_paytab_name in varchar2 default NULL,
319 p_ele_paytab_col in varchar2 default NULL,
320 p_ele_paytab_row_type in varchar2 default NULL,
321 p_ele_arrearage in varchar2 default 'N',
322 p_ele_partial_dedn in varchar2 default 'N',
323 p_mix_flag in varchar2 default NULL,
324 p_ele_er_match in varchar2 default 'N',
325 p_ele_at_component in varchar2 default 'N',
326 p_ele_eff_start_date in date default NULL,
327 p_ele_eff_end_date in date default NULL,
328 p_bg_id in number,
329 p_termination_rule in varchar2 default 'F'
330 ) RETURN NUMBER IS
331
332 l_er_ename varchar2(80) := substr(p_ele_name,1,77)||' ER';
333 l_at_ename varchar2(80) := substr(p_ele_name,1,77)||' AT';
334 l_ele_id number;
335 l_ele_at_id number;
336
337
338 l_limit_bal varchar2(80):= substr(p_ele_name,1,67)||' AT Overlimit';
339 l_withhold_ele varchar2(80):= substr(p_ele_name,1,68 )||' Withholding';
340 l_withhold_iv varchar2(80):= 'Pass To Aftertax';
341
342
343
344 --
345 BEGIN
346 IF p_ele_classification = 'Pre-Tax Deductions' THEN
347
348 hr_utility.set_location('pyusddwp.ins_deduction_template',10);
349
350 l_ele_id:=
351 hr_generate_pretax.pretax_deduction_template (
352 p_ele_name => p_ele_name,
353 p_ele_reporting_name => p_ele_reporting_name,
354 p_ele_description => p_ele_description ,
355 p_ele_classification => p_ele_classification ,
356 p_ben_class_id => p_ben_class_id,
357 p_ele_category => p_ele_category,
358 p_ele_processing_type => p_ele_processing_type ,
359 p_ele_priority => p_ele_priority ,
360 p_ele_standard_link => p_ele_standard_link,
361 p_ele_proc_runtype => p_ele_proc_runtype ,
362 p_ele_start_rule => p_ele_start_rule,
363 p_ele_stop_rule => p_ele_stop_rule,
364 p_ele_ee_bond => p_ele_ee_bond,
365 p_ele_amount_rule => p_ele_amount_rule,
366 p_ele_paytab_name => p_ele_paytab_name,
367 p_ele_paytab_col => p_ele_paytab_col,
368 p_ele_paytab_row_type => p_ele_paytab_row_type,
369 p_ele_arrearage => p_ele_arrearage,
370 p_ele_partial_dedn => p_ele_partial_dedn,
371 p_mix_flag => p_mix_flag,
372 p_ele_er_match => p_ele_er_match,
373 p_ele_eff_start_date => p_ele_eff_start_date,
374 p_ele_eff_end_date => p_ele_eff_end_date,
375 p_bg_id => p_bg_id);
376
377 -- Add Employer Match Formula for Pre-Tax --
378 IF p_ele_er_match = 'Y' THEN
379 --
380 hr_utility.set_location('pyusddwp.ins_deduction_template',20);
381
382 do_employer_match(
383 p_ename => p_ele_name
384 , p_bg_id => p_bg_id
385 , p_start_date => p_ele_eff_start_date
386 , p_end_date => p_ele_eff_end_date
387 , p_leg_code => g_template_leg_subgroup
388 , p_er_ename => l_er_ename);
389 END IF; -- Employer Match
390
391 --hr_utility.trace_on;
392
393 -- Check to see if Aftertax components need to be created
394 IF p_ele_at_component = 'Y' THEN
395
396 hr_utility.set_location('pyusddwp.ins_deduction_template',30);
397
398 -- This Pre-tax deduction has an associated Aftertax Component
399 -- Redefine element names for Aftertax components
400
401 l_er_ename := substr(l_at_ename,1,77)||' ER';
402
403
404 l_ele_at_id:=
405 hr_user_init_dedn.ins_deduction_template (
406 p_ele_name => l_at_ename,
407 p_ele_reporting_name => p_ele_reporting_name||' AT',
408 p_ele_description => p_ele_description||' - Aftertax Component',
409 p_ele_classification => 'Voluntary Deductions',
410 p_ben_class_id => p_ben_class_id,
411 p_ele_category => NULL,
412 p_ele_processing_type => p_ele_processing_type ,
413 p_ele_priority => 5750,
414 p_ele_standard_link => p_ele_standard_link,
415 p_ele_proc_runtype => p_ele_proc_runtype,
416 p_ele_start_rule => p_ele_start_rule,
417 p_ele_stop_rule => p_ele_stop_rule,
418 p_ele_ee_bond => p_ele_ee_bond,
419 p_ele_amount_rule => p_ele_amount_rule,
420 p_ele_paytab_name => p_ele_paytab_name,
421 p_ele_paytab_col => p_ele_paytab_col,
422 p_ele_paytab_row_type => p_ele_paytab_row_type,
423 p_ele_arrearage => p_ele_arrearage,
424 p_ele_partial_dedn => p_ele_partial_dedn,
425 p_mix_flag => p_mix_flag,
426 p_ele_er_match => p_ele_er_match,
427 p_ele_at_component => p_ele_at_component,
428 p_ele_eff_start_date => p_ele_eff_start_date,
429 p_ele_eff_end_date => p_ele_eff_end_date,
430 p_bg_id => p_bg_id,
431 p_termination_rule => p_termination_rule);
432
433 hr_utility.set_location('pyusddwp.ins_deduction_template',40);
434
435 -- Add Employer Match Formula for AT component--
436 IF p_ele_er_match = 'Y' THEN
437 --
438 hr_utility.set_location('pyusddwp.ins_deduction_template',50);
439
440 do_employer_match(
441 p_ename => l_at_ename
442 , p_bg_id => p_bg_id
443 , p_start_date => p_ele_eff_start_date
444 , p_end_date => p_ele_eff_end_date
445 , p_leg_code => g_template_leg_code
446 , p_er_ename => l_er_ename);
447 END IF; -- Employer Match
448
449 hr_utility.set_location('pyusddwp.ins_deduction_template',60);
450
451 do_passthru_feed(
452 p_src_ele => l_withhold_ele
453 , p_bg_id => p_bg_id
454 , p_src_iv => l_withhold_iv
455 , p_targ_bal => l_limit_bal
456 , p_eff_start_date=> p_ele_eff_start_date
457 , p_eff_end_date => p_ele_eff_end_date);
458
459 hr_utility.set_location('pyusddwp.ins_deduction_template',70);
460
461
462 END IF; -- AT Component = 'Y'
463
464 ELSE
465 -- Not a 'PRE-Tax' Deduction element, do standard processing
466
467 hr_utility.set_location('pyusddwp.ins_deduction_template',90);
468
469 l_ele_id:=
470 hr_user_init_dedn.ins_deduction_template (
471 p_ele_name => p_ele_name,
472 p_ele_reporting_name => p_ele_reporting_name,
473 p_ele_description => p_ele_description,
474 p_ele_classification => p_ele_classification,
475 p_ben_class_id => p_ben_class_id,
476 p_ele_category => p_ele_category,
477 p_ele_processing_type => p_ele_processing_type,
478 p_ele_priority => p_ele_priority,
479 p_ele_standard_link => p_ele_standard_link,
480 p_ele_proc_runtype => p_ele_proc_runtype,
481 p_ele_start_rule => p_ele_start_rule,
482 p_ele_stop_rule => p_ele_stop_rule,
483 p_ele_ee_bond => p_ele_ee_bond,
484 p_ele_amount_rule => p_ele_amount_rule,
485 p_ele_paytab_name => p_ele_paytab_name,
486 p_ele_paytab_col => p_ele_paytab_col,
487 p_ele_paytab_row_type => p_ele_paytab_row_type,
488 p_ele_arrearage => p_ele_arrearage,
489 p_ele_partial_dedn => p_ele_partial_dedn,
490 p_mix_flag => p_mix_flag,
491 p_ele_er_match => p_ele_er_match,
492 p_ele_eff_start_date => p_ele_eff_start_date,
493 p_ele_eff_end_date => p_ele_eff_end_date,
494 p_bg_id => p_bg_id,
495 p_termination_rule => p_termination_rule);
496
497 hr_utility.set_location('pyusddwp.ins_deduction_template',100);
498
499 END IF ; -- Classification = 'Pre-tax'
500
501 --hr_utility.trace_off;
502 hr_utility.set_location('pyusddwp.ins_deduction_template',110);
503
504 RETURN l_ele_id;
505
506 end ins_deduction_template;
507
508 end hr_user_dedn_drv;
509