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