1 PACKAGE BODY pay_wat_udfs AS
2 /* $Header: pywatudf.pkb 120.1.12000000.2 2007/07/17 08:29:56 sudedas noship $*/
3 FUNCTION entry_subpriority ( p_date_earned in date,
4 p_ele_entry_id in number) RETURN number IS
5 l_subprio number(9);
6 BEGIN
7 SELECT nvl(pee.subpriority, 1)
8 INTO l_subprio
9 FROM pay_element_entries_f PEE
10 WHERE PEE.element_entry_id = p_ele_entry_id
11 AND p_date_earned
12 BETWEEN PEE.effective_start_date
13 AND PEE.effective_end_date;
14 RETURN l_subprio;
15 EXCEPTION when no_data_found THEN
16 l_subprio := 1;
17 RETURN l_subprio;
18 END entry_subpriority;
19 FUNCTION garn_cat( p_date_earned in date,
20 p_ele_entry_id in number) return varchar2 IS
21 l_cat varchar2(20);
22 begin
23 SELECT pet.element_information1
24 INTO l_cat
25 FROM pay_element_types_f pet,
26 pay_element_links_f pel,
27 pay_element_entries_f PEE
28 WHERE PEE.element_entry_id = p_ele_entry_id
29 AND p_date_earned
30 BETWEEN PEE.effective_start_date
31 AND PEE.effective_end_date
32 AND pel.element_link_id = PEE.element_link_id
33 AND PEE.effective_start_date between pel.effective_start_date
34 and pel.effective_end_date
35 ANd pet.element_type_id = pel.element_type_id
36 AND pel.effective_start_date between pet.effective_start_date
37 and pet.effective_end_date;
38 RETURN l_cat;
39 EXCEPTION when no_data_found THEN
40 l_cat := null;
41 RETURN l_cat;
42 END garn_cat;
43
44
45 /*************************************************************************
46 routine name: fnc_fee_calculation
47 purpose: It calculates the garnishment fee value and then checks this
48 fee against legislative maximum for time period.
49 parameters:
50 return: Calculated fee value
51 specs:
52 ***************************************************************************/
53 FUNCTION FNC_FEE_CALCULATION ( IN_JURISDICTION IN VARCHAR2,
54 IN_GARN_FEE_FEE_RULE IN VARCHAR2,
55 IN_GARN_FEE_FEE_AMOUNT IN NUMBER,
56 IN_GARN_FEE_PCT_CURRENT IN NUMBER,
57 IN_TOTAL_OWED IN NUMBER,
58 IN_PRIMARY_AMOUNT_BALANCE IN NUMBER,
59 IN_ADDL_GARN_FEE_AMOUNT IN NUMBER,
60 IN_GARN_FEE_MAX_FEE_AMOUNT IN NUMBER,
61 IN_GARN_FEE_BAL_ASG_GRE_PTD IN NUMBER,
62 IN_GARN_TOTAL_FEES_ASG_GRE_RUN IN NUMBER,
63 IN_DEDN_AMT IN NUMBER,
64 IN_GARN_FEE_BAL_ASG_GRE_MONTH IN NUMBER,
65 IN_ACCRUED_FEES IN NUMBER) RETURN NUMBER IS
66
67
68 l_calcd_fee NUMBER(7,2):=0.0; -- stores value to be returned
69
70 BEGIN
71 IF IN_garn_fee_fee_rule <> 'NONE' THEN
72 IF IN_garn_fee_fee_rule = 'AMT_OR_PCT' THEN /*Bug:1009539,Bug:1020421*/
73 l_calcd_fee := GREATEST(IN_garn_fee_fee_amount,IN_garn_fee_pct_current * IN_dedn_amt);
74 END IF;
75 IF IN_garn_fee_fee_rule = 'AMT_OR_PCT_PER_GARN' THEN
76 /*
77 IF SUBSTR(IN_jurisdiction,1,2) = '11'
78 OR SUBSTR(IN_jurisdiction,1,2) = '14'
79 OR SUBSTR(IN_jurisdiction,1,2) = '15'
80 OR SUBSTR(IN_jurisdiction,1,2) = '26'
81 THEN
82 */
83 -- Bug 3734540
84 l_calcd_fee := Greatest(IN_garn_fee_fee_amount,IN_garn_fee_pct_current *IN_dedn_amt);
85 -- END IF;
86 END IF;
87
88 IF IN_garn_fee_fee_rule = 'PCT_PER_GARN' OR
89 IN_garn_fee_fee_rule = 'PCT_PER_PERIOD' OR
90 IN_garn_fee_fee_rule = 'PCT_PER_MONTH' OR
91 IN_garn_fee_fee_rule = 'PCT_PER_RUN'
92 THEN
93 /* Tennessee Fee rule for support is 5% of the amount withheld but not to exceed $5 a month */
94 l_calcd_fee := IN_garn_fee_pct_current * IN_dedn_amt;
95 END IF;
96
97 IF IN_garn_fee_fee_rule = 'AMT_PER_GARN' OR
98 IN_garn_fee_fee_rule = 'AMT_PER_PERIOD' OR
99 IN_garn_fee_fee_rule = 'AMT_PER_MONTH' OR
100 IN_garn_fee_fee_rule = 'AMT_PER_RUN'
101 THEN
102 l_calcd_fee := IN_garn_fee_fee_amount;
103 END IF;
104
105 IF IN_garn_fee_fee_rule = 'AMT_PER_GARN_ADDL' OR
106 IN_garn_fee_fee_rule = 'AMT_PER_PERIOD_ADDL' OR
107 IN_garn_fee_fee_rule = 'AMT_PER_MONTH_ADDL' OR
108 IN_garn_fee_fee_rule = 'AMT_PER_RUN_ADDL'
109 THEN
110 /* 344140: Check for Accrued Fees = 0 to determine if
111 this is first time the wage attachment has been processed, or a
112 subsequent processing.*/
113 IF IN_Accrued_Fees = 0 THEN
114 l_calcd_fee := IN_garn_fee_fee_amount;
115 -- Bug 4748532
116 -- Modified the package to return Initial Fee for every run in the
117 -- payroll period where garnishment is processed first.
118 ELSIF IN_Accrued_Fees > 0 AND IN_Accrued_Fees = IN_GARN_FEE_BAL_ASG_GRE_PTD THEN
119 l_calcd_fee := IN_garn_fee_fee_amount;
120 ELSE
121 l_calcd_fee := IN_addl_garn_fee_amount;
122 END IF;
123 END IF;
124 END IF;
125 /* *** Fee processing END *** */
126
127 /* Check garnishment fee against legislative maximum for time period. */
128 IF IN_garn_fee_fee_rule = 'AMT_OR_PCT' OR
129 IN_garn_fee_fee_rule = 'AMT_PER_GARN_ADDL' OR
130 IN_garn_fee_fee_rule = 'AMT_PER_GARN' OR
131 IN_garn_fee_fee_rule = 'PCT_PER_GARN'
132 THEN
133 IF IN_Accrued_Fees > 0 AND
134 IN_Accrued_Fees < IN_garn_fee_fee_amount THEN
135 l_calcd_fee:= IN_garn_fee_fee_amount - IN_Accrued_fees;
136 END IF;
137
138 IF IN_garn_fee_max_fee_amount <> -99999 THEN
139 /* Check that total fees collected are within legislative limit.
140 Check if the fee has addl amt.IF so see whether the accrued has taken all
141 Initial amount else assign the left over initial fee. */
142 IF IN_ACCRUED_FEES >= IN_garn_fee_max_fee_amount THEN
143 l_calcd_fee := 0;
144 ELSIF l_calcd_fee = IN_addl_garn_fee_amount THEN
145 IF l_calcd_fee + IN_Accrued_Fees > IN_Garn_fee_max_fee_amount THEN
146 l_calcd_fee:= IN_Garn_fee_max_fee_amount - IN_Accrued_Fees;
147 ELSE
148 l_calcd_fee:= IN_addl_Garn_fee_amount ;
149 END IF;
150 END IF;
151 END IF;
152 END IF;
153
154
155 IF IN_garn_fee_fee_rule = 'AMT_OR_PCT_PER_GARN' THEN
156 IF IN_garn_fee_max_fee_amount <> -99999 THEN
157 /* Check that total fees collected are within legislative limit. */
158 IF l_calcd_fee > IN_garn_fee_max_fee_amount THEN
159 l_calcd_fee := IN_garn_fee_max_fee_amount;
160 END IF;
161 END IF;
162 IF IN_Accrued_Fees > 0 AND IN_Accrued_Fees >= IN_garn_fee_max_fee_amount THEN
163 /* changes for bug 3734540 */
164 /* l_calcd_fee := 0 ; Check if Accrued Fee is less than the calcd fee
165 Otherwise assign the remaining fee to be taken to
166 l_calcd_Fee so that the initial fee is picked up in full.*/
167 l_calcd_fee := 0;
168 ELSIF (IN_Accrued_Fees + l_calcd_fee) > IN_garn_fee_max_fee_amount THEN
169 l_calcd_fee:= IN_garn_fee_max_fee_amount - IN_Accrued_Fees;
170 END IF;
171
172 ELSIF IN_garn_fee_fee_rule = 'AMT_PER_PERIOD' OR
173 IN_garn_fee_fee_rule = 'PCT_PER_PERIOD' OR
174 IN_garn_fee_fee_rule = 'AMT_PER_PERIOD_ADDL'
175 THEN
176 IF IN_garn_fee_max_fee_amount <> -99999 THEN
177 /* Check that total fees collected are within legislative limit. */
178 IF IN_GARN_FEE_BAL_ASG_GRE_PTD + l_calcd_fee > IN_garn_fee_max_fee_amount THEN
179 /* Recalculate fee amount */
180 l_calcd_fee := IN_garn_fee_max_fee_amount - IN_GARN_FEE_BAL_ASG_GRE_PTD;
181 END IF;
182 END IF;
183 ELSIF IN_garn_fee_fee_rule = 'AMT_PER_MONTH' OR
184 IN_garn_fee_fee_rule = 'PCT_PER_MONTH' OR
185 IN_garn_fee_fee_rule = 'AMT_PER_MONTH_ADDL'
186 THEN
187 IF IN_garn_fee_max_fee_amount <> -99999 THEN
188 IF (IN_GARN_FEE_BAL_ASG_GRE_MONTH + l_calcd_fee) > IN_garn_fee_max_fee_amount THEN
189 /* Recalculate fee amount Check against Month Fee Balance */
190 l_calcd_fee := IN_garn_fee_max_fee_amount - IN_GARN_FEE_BAL_ASG_GRE_MONTH;
191 END IF;
192 END IF;
193 ELSIF IN_garn_fee_fee_rule = 'AMT_PER_RUN' OR
194 IN_garn_fee_fee_rule = 'PCT_PER_RUN' OR
195 IN_garn_fee_fee_rule = 'AMT_PER_RUN_ADDL'
196 THEN
197 IF IN_garn_fee_max_fee_amount <> -99999 THEN
198 IF IN_GARN_FEE_BAL_ASG_GRE_PTD + l_calcd_fee > IN_garn_fee_max_fee_amount THEN
199 /* Recalculate fee amount Check against PTD Fee balance*/
200 l_calcd_fee := IN_garn_fee_max_fee_amount - IN_garn_total_fees_asg_gre_run;
201 END IF;
202
203 END IF;
204 END IF;
205
206
207 RETURN l_calcd_fee;
208
209 END fnc_fee_calculation;
210
211 FUNCTION get_garn_limit_max_duration (p_element_type_id NUMBER,
212 p_element_entry_id NUMBER,
213 p_effective_date DATE,
214 p_jursd_code VARCHAR2)
215 RETURN NUMBER IS
216
217 /******************************************************************************
218 Function : get_garn_limit_max_duration
219 Description : This function is used to return the maximum duration, in
220 number of days, for which a particular garnishment can be
221 taken in a particular state. The duration is obtained with
222 respect to the 'Date Served' of the garnishment. If 'Date Served'
223 is null, then the mimimum effective_start_date for the
224 element_entry is used.
225 Parameters : p_element_type_id (element_type_id context)
226 p_element_entry_id (original_entry_id context)
227 p_effective_date (date_earned context)
228 p_jursd_code (jurisdiction_code context)
229 ******************************************************************************/
230
231 CURSOR c_get_dt_srvd IS
232 SELECT fnd_date.canonical_to_date(ev.screen_entry_value)
233 FROM pay_input_values_f iv,
234 pay_element_entry_values_f ev
235 WHERE ev.element_entry_id = p_element_entry_id
236 AND p_effective_date
237 BETWEEN ev.effective_start_date and ev.effective_end_date
238 AND iv.input_value_id = ev.input_value_id
239 AND iv.name = 'Date Served';
240
241 ld_garn_date DATE := NULL;
242 ln_garn_limit_days NUMBER(15) := 0;
243 lv_mod_name VARCHAR2(30) := 'get_garn_limit_max_duration';
244
245 BEGIN
246 hr_utility.trace(lv_mod_name || ': p_element_type_id');
247 hr_utility.trace(lv_mod_name || ': ' || to_char(p_element_type_id));
248 hr_utility.trace(lv_mod_name || ': p_element_entry_id');
249 hr_utility.trace(lv_mod_name || ': ' || to_char(p_element_entry_id));
250 hr_utility.trace(lv_mod_name || ': p_effective_date');
251 hr_utility.trace(lv_mod_name || ': ' || p_effective_date);
252 hr_utility.trace(lv_mod_name || ': p_jursd_code');
253 hr_utility.trace(lv_mod_name || ': ' || p_jursd_code);
254
255 OPEN c_get_dt_srvd;
256
257 FETCH c_get_dt_srvd
258 INTO ld_garn_date;
259
260 CLOSE c_get_dt_srvd;
261
262 IF ld_garn_date is NULL
263 THEN
264 SELECT MIN(effective_start_date)
265 INTO ld_garn_date
266 FROM pay_element_entries_f ee
267 WHERE ee.element_entry_id = p_element_entry_id;
268 hr_utility.trace(lv_mod_name || ': Min. Effective Start Date');
269 hr_utility.trace(lv_mod_name || ': '
270 || to_char(ld_garn_date, 'dd-mon-yyyy'));
271 END IF;
272
273 SELECT target.max_withholding_duration_days
274 INTO ln_garn_limit_days
275 FROM PAY_US_GARN_LIMIT_RULES_F target,
276 PAY_ELEMENT_TYPES_F pet
277 WHERE target.state_code = SUBSTR(p_jursd_code,1,2)
278 AND target.garn_category = pet.element_information1
279 AND ld_garn_date BETWEEN target.effective_start_date
280 AND target.effective_end_date
281 AND pet.element_type_id = p_element_type_id;
282
283 /* Commenting the section below as per the Update by Nora Daly
284 in Bug# 6140374 on 07/16/07 11:16 am
285
286 AND ld_garn_date
287 BETWEEN pet.effective_start_date AND pet.effective_end_date;
288 */
289
290 hr_utility.trace(lv_mod_name || ': Garnishment Duration Limit');
291 hr_utility.trace(lv_mod_name || ': ' || to_char(ln_garn_limit_days));
292 return ln_garn_limit_days;
293
294 EXCEPTION WHEN NO_DATA_FOUND THEN
295 hr_utility.oracle_error(sqlcode);
296 return ln_garn_limit_days;
297
298 WHEN OTHERS THEN
299 hr_utility.oracle_error(sqlcode);
300 return ln_garn_limit_days;
301
302 END get_garn_limit_max_duration;
303
304
305 END pay_wat_udfs;
[Home] [Help]