[Home] [Help]
PACKAGE BODY: APPS.PAY_US_MMREF_FUNCTION_PKG
Source
1 package body pay_us_mmref_function_pkg as
2 /* $Header: pyusmrfn.pkb 120.1 2007/11/26 19:42:24 sjawid noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 pay_us_mmref_function_pkg
11
12 Purpose
13 The purpose of this package is to support the generation of magnetic tape W2
14 reports for US legilsative requirements incorporating magtape resilience
15 and the new end-of-year design. New Functions will support the Year end
16 reporting in MMREF format initially and will be extended to have more
17 format.
18
19
20 History
21 23-Jan-02 fusman 115.0 created
22 14-may-02 fusman 115.1 Added Get_Hours_Worked function.
23 10-Jun-02 fusman 115.2 2404709 Removed the padding in NH hours calc.
24 11-Nov-02 ppanda 115.3 For Rita/CCA city code in
25 pay_us_city_tax_info_f.city_information1
26 starts from 6 char position. This changes
27 made to generalised the Local Mag Tape
28 02-Dec-02 ppanda 115.4 Nocopy hint added to OUT and IN OUT parameters
29 19-FEB-03 sodhingr 115.5 Changed Get_hours_worked for bug
30 2442629, to pass new balances for SUI
31 hours by state
32 23-Apr-03 fusman 115.6 2873551 Created new function get_sqwl_extra_info to calculate
33 the SUI_ER_SUBJ_WHABLE and SUI_ER_PRE_TAX.
34 15-May-03 fusman 115.7 2873584 Added SSA,ICESA,NJ formula hour calculations to the function.
35 16-May-03 fusman 115.8 Changed the data types of values that are being calculated.
36 02-Jun-03 fusman 115.9 2985476 Negative hour checking for Non-mmref states.
37 2873584 Exclusion of Sick hours for WA SQWL.
38 29-Aug-03 fusman 115.10 3092981 Split the cursor GET_ARCHIVED_VALUE in Get_Sqwl_Extra_Info
39 27-FEB-04 Jgoswami 115.12 3334497 Added out parameters to Get_Sqwl_Extra_Inf
40 Changed GET_ARCHIVED_VALUE to get value >= 0
41 09-MAR-04 JGoswami 115.13 3489556 Modified Get_Hours_Worked function to return
42 Regular Hours Worked for Vermont (VT).
43 07-MAY-04 JGoswami 115.14 3414759 Modified Get_Hours_Worked function to return
44 Hours for Minnesota(MN) and Oregon(OR).
45 16-JUL-04 JGoswami 115.15 3770719 Modified Get_Hours_Worked function to return
46 Regular Hours for Minnesota(MN) and Oregon(OR)
47 when Hours Worked Calculation Method is not set
48 to Balance.
49 25-NOV-07 sjawid 115.16 6613661 Modified Get_Hours_Worked function to return
50 Worked weeks and hours for the State "RI" and
51 Report type "SSA_SQWL".
52
53 */
54
55 FUNCTION Get_City_Values(p_jurisdiction_code IN varchar2,
56 p_effective_date IN varchar2,
57 p_input_1 IN varchar2,
58 p_input_2 IN varchar2,
59 p_input_3 IN varchar2,
60 p_input_4 IN varchar2,
61 p_input_5 IN varchar2,
62 sp_out_1 OUT nocopy varchar2,
63 sp_out_2 OUT nocopy varchar2,
64 sp_out_3 OUT nocopy varchar2,
65 sp_out_4 OUT nocopy varchar2,
66 sp_out_5 OUT nocopy varchar2,
67 sp_out_6 OUT nocopy varchar2,
68 sp_out_7 OUT nocopy varchar2,
69 sp_out_8 OUT nocopy varchar2,
70 sp_out_9 OUT nocopy varchar2,
71 sp_out_10 OUT nocopy varchar2)
72
73 return varchar2
74
75 IS
76
77 CURSOR GET_CITY_NAME(c_jurisdiction_code varchar2)
78 IS
79 SELECT city_name
80 FROM pay_us_city_names
81 WHERE state_code = substr(c_jurisdiction_code,1,2)
82 AND county_code = substr(c_jurisdiction_code,4,3)
83 AND city_code = substr(c_jurisdiction_code,8,4)
84 AND primary_flag = 'Y';
85
86 CURSOR GET_CITY_CODE(c_jurisdiction_code varchar2,
87 c_date varchar2)
88 IS
89 /* City code starts from 5 instead of 4 in city_information1 column in
90 pay_us_city_tax_info_f table. This changes made to generalise the Local Mag Tape */
91 SELECT substr(city_information1,1,5),
92 substr(city_information1,6)
93 FROM pay_us_city_tax_info_f
94 WHERE to_date(c_date,'dd-mm-yyyy') between effective_start_date
95 and effective_end_date
96 AND jurisdiction_code = c_jurisdiction_code;
97
98 l_city_value varchar2(10);
99 l_city_id varchar2(10);
100 l_city_name pay_us_city_names.city_name%TYPE;
101
102 Begin
103
104 hr_utility.trace('Get_City_Values');
105 hr_utility.trace('p_jurisdiction_code = '||p_jurisdiction_code);
106
107 OPEN GET_CITY_NAME(p_jurisdiction_code);
108 hr_utility.trace('OPEN GET_CITY_NAME');
109 FETCH GET_CITY_NAME INTO l_city_name;
110 hr_utility.trace('FETCH GET_CITY_NAME '||l_city_name);
111
112 IF GET_CITY_NAME%NOTFOUND THEN
113
114 hr_utility.trace('No city found with this jurisdiction code = '||p_jurisdiction_code);
115 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','A');
116 pay_core_utils.push_token('record_name','jurisdiction '||p_jurisdiction_code);
117 pay_core_utils.push_token('description','City not found in pay_us_city_names.');
118 l_city_name := ' ';
119
120 END IF;
121
122 CLOSE GET_CITY_NAME;
123
124 OPEN GET_CITY_CODE(p_jurisdiction_code,p_effective_date);
125 hr_utility.trace('OPEN GET_CITY_CODE');
126 FETCH GET_CITY_CODE INTO l_city_value,l_city_id;
127 hr_utility.trace('FETCH GET_CITY_CODE '||l_city_id);
128
129 IF GET_CITY_CODE%NOTFOUND THEN
130
131 hr_utility.trace('No city information found for jurisdiction code = '||p_jurisdiction_code);
132 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','A');
133 pay_core_utils.push_token('record_name','jurisdiction '||p_jurisdiction_code
134 ||' in '||'pay_us_city_tax_info_f');
135 pay_core_utils.push_token('description','City tax infm not found.');
136
137 sp_out_1 := ' ';
138 sp_out_2 := ' ';
139
140 ELSIF GET_CITY_CODE%FOUND THEN
141
142 sp_out_1 := l_city_value;
143 sp_out_2 := l_city_id;
144 hr_utility.trace('city information found');
145 hr_utility.trace('l_city_value = '||l_city_value);
146 hr_utility.trace('l_city_id = '||l_city_id);
147
148 END IF;
149
150 RETURN l_city_name;
151
152 END;
153
154
155 FUNCTION Get_Hours_Worked(
156 p_report_type IN varchar2,
157 p_report_qualifier IN varchar2,
158 p_record_name IN varchar2,
159 p_regular_hours IN number,
160 p_sui_er_gross IN number,
161 p_gross_earnings IN number,
162 p_asg_hours IN number,
163 p_asg_freq IN varchar2,
164 p_scl_asg_work_sch IN varchar2,
165 p_input_1 IN varchar2,
166 p_input_2 IN varchar2,
167 sp_out_1 IN OUT nocopy varchar2,
168 sp_out_2 IN OUT nocopy varchar2,
169 sp_out_3 IN OUT nocopy varchar2,
170 sp_out_4 IN OUT nocopy varchar2,
171 sp_out_5 IN OUT nocopy varchar2)
172
173
174 return varchar2 IS
175 l_hours_worked number(10) :=0;
176 l_hours_per_week number(10);
177 l_output_hours varchar2(100);
178 l_add_days date;
179 lv_jd_sick_hrs number(10);
180 lv_jd_vacn_hrs number(10);
181 lv_jd_reg_hrs number(10);
182 lv_jd_ot_hrs number(10);
183
184 Begin
185
186 hr_utility.trace('Get_Hours_Worked');
187 hr_utility.trace('p_report_qualifier = '||p_report_qualifier);
188 hr_utility.trace('p_scl_asg_work_sch = '||p_scl_asg_work_sch);
189 hr_utility.trace('p_input_1 = '||p_input_1);
190
191 /* Bug:2873584 WA does not include Sick hours. */
192
193 IF p_report_qualifier = 'WA_SQWL' THEN
194
195 sp_out_1 :=0;
196
197 END IF;
198
199 IF p_gross_earnings <> 0 THEN
200 IF p_input_2 = 'B' THEN
201
202 /* sp_out_1 = A_SUI_SICK_HOURS_BY_STATE_PER_JD_GRE_QTD
203 sp_out_2 = A_SUI_VACATION_HOURS_BY_STATE_PER_JD_GRE_QTD
204 sp_out_3 = A_SUI_REGULAR_HOURS_BY_STATE_PER_JD_GRE_QTD
205 sp_out_4 = A_SUI_OVERTIME_HOURS_BY_STATE_PER_JD_GRE_QTD */
206
207 lv_jd_sick_hrs := sp_out_1;
208 lv_jd_vacn_hrs := sp_out_2;
209 lv_jd_reg_hrs := sp_out_3;
210 lv_jd_ot_hrs := sp_out_4;
211
212 l_hours_worked := nvl(to_number(sp_out_1),0) + nvl(to_number(sp_out_2),0)
213 + nvl(to_number(sp_out_3),0) + nvl(to_number(sp_out_4),0);
214
215 hr_utility.trace('sp_out_1 = '||sp_out_1);
216 hr_utility.trace('sp_out_2 = '||sp_out_2);
217 hr_utility.trace('sp_out_3 = '||sp_out_3);
218 hr_utility.trace('sp_out_4 = '||sp_out_4);
219 ELSE
220 l_hours_worked := p_regular_hours*p_sui_er_gross/p_gross_earnings;
221 hr_utility.trace('p_regular_hours = '||to_char(p_regular_hours));
222 hr_utility.trace('p_sui_er_gross = '||to_char(p_sui_er_gross));
223 hr_utility.trace('p_gross_earnings = '||to_char(p_gross_earnings));
224 hr_utility.trace('p_gross_earnings <>0. l_hours_worked = '||to_char(l_hours_worked));
225 END IF;
226 END IF;
227
228 IF l_hours_worked <0 THEN /* Negative Hour checking*/
229
230 IF ((p_report_qualifier = 'MA_SQWL') OR
231 (p_report_qualifier = 'OH_SQWL') OR
232 (p_report_qualifier = 'WY_SQWL') OR
233 (p_report_qualifier = 'DE_SQWL') OR
234 (p_report_qualifier = 'NJ_SQWL') OR
235 (p_report_qualifier = 'PA_SQWL')) THEN
236
237 sp_out_5 :='Y';
238 hr_utility.trace(' l_hours_worked is negative = '||to_char(l_hours_worked));
239
240 END IF;
241
242 END IF;
243
244 IF p_scl_asg_work_sch = '99999' THEN
245
246 hr_utility.trace('p_scl_asg_work_sch = 99999 ');
247
248 l_add_days := fffunc.add_days(sysdate,6);
249 l_hours_per_week := hr_us_ff_udfs.Standard_Hours_Worked(
250 p_asg_hours,
251 sysdate,
252 l_add_days,
253 p_asg_freq);
254 hr_utility.trace('p_report_qualifier = NH.p_scl_asg_work_sch was defaulted');
255 hr_utility.trace('l_add_days = '||l_add_days);
256 hr_utility.trace('l_hours_per_week = '||to_char(l_hours_per_week));
257
258 ELSE
259
260 l_hours_per_week := hr_us_ff_udfs.work_schedule_total_hours(to_number(p_input_1),
261 p_scl_asg_work_sch,
262 null,
263 null);
264 hr_utility.trace('p_scl_asg_work_sch = '||p_scl_asg_work_sch);
265 hr_utility.trace('l_hours_per_week = '||to_char(l_hours_per_week));
266
267
268 END IF;
269
270 IF l_hours_per_week = 0 THEN
271
272 l_output_hours := '00';
273
274 ELSE
275
276 hr_utility.trace('l_hours_per_week <> 0 ');
277 hr_utility.trace('l_hours_worked = '||l_hours_worked);
278 l_output_hours := lpad(to_char(ceil(l_hours_worked/l_hours_per_week)),2,'0');
279 hr_utility.trace('l_output_hours = '||l_output_hours);
280
281 IF to_number(l_output_hours) > 14 THEN
282
283 l_output_hours := 14;
284
285 END IF;
286
287 END IF;
288
289
290 IF p_report_type = 'SSA_SQWL' THEN
291
292 hr_utility.trace('SSA_SQWL');
293
294 IF p_report_qualifier = 'WY_SQWL' THEN
295
296 hr_utility.trace('WY');
297 l_output_hours := '00';
298 sp_out_1 := to_char(l_hours_worked);
299
300 ELSIF p_report_qualifier = 'DE_SQWL' THEN
301
302 hr_utility.trace('DE l_output_hours '||l_output_hours);
303 l_output_hours :=lpad(l_output_hours,2,'0');
304
305 ELSIF p_report_qualifier = 'RI_SQWL' THEN /*bug 6613661*/
306
307 hr_utility.trace('RI l_output_hours '||l_output_hours);
308 l_output_hours :=lpad(l_output_hours,2,'0');
309 sp_out_1 := to_char(round(l_hours_worked));
310 ELSE
311
312 l_output_hours := lpad(' ',2);
313
314 END IF;
315
316 ELSIF p_report_type = 'ICESA_SQWL' THEN
317
318 IF ((p_report_qualifier = 'MA_SQWL') OR
319 (p_report_qualifier = 'OH_SQWL') OR
320 (p_report_qualifier = 'PA_SQWL')) THEN
321
322 l_output_hours := lpad(l_output_hours,2,'0');
323
324 ELSIF ((p_report_qualifier = 'KY_SQWL') OR
325 (p_report_qualifier = 'KS_SQWL') OR
326 (p_report_qualifier = 'OK_SQWL')) THEN
327
328 l_output_hours:= lpad(' ', 2);
329 ELSIF (p_report_qualifier = 'VT_SQWL') THEN
330 l_output_hours := to_char(l_hours_worked);
331
332 ELSE
333
334 l_output_hours:= '00';
335
336 END IF;
337
338 IF ((p_report_qualifier = 'KS_SQWL') OR
339 (p_report_qualifier = 'KY_SQWL') OR
340 (p_report_qualifier = 'OH_SQWL')) THEN
341
342 sp_out_1 := lpad(' ',3);
343
344 ELSE
345
346 sp_out_1 := lpad('0',3,'0');
347
348 END IF;
349
350 ELSIF p_report_type = 'MMREF_SQWL' THEN
351
352 IF (p_report_qualifier = 'MN_SQWL') THEN
353
354 IF p_input_2 = 'B' THEN
355 l_hours_worked := nvl(to_number(lv_jd_sick_hrs),0) + nvl(to_number(lv_jd_vacn_hrs),0)
356 + nvl(to_number(lv_jd_reg_hrs),0) + nvl(to_number(lv_jd_ot_hrs),0);
357 END IF;
358
359 ELSIF (p_report_qualifier = 'OR_SQWL') THEN /*Bug:2286335. */
360
361 IF p_input_2 = 'B' THEN
362 l_hours_worked := nvl(to_number(lv_jd_reg_hrs),0) + nvl(to_number(lv_jd_ot_hrs),0);
363 END IF;
364
365
366 IF l_hours_worked >999 THEN
367
368 l_hours_worked := 999;
369
370 END IF;
371 hr_utility.trace('l_hours_worked = '||l_hours_worked);
372
373 END IF;
374
375 hr_utility.trace('Report Qualifier = '||p_report_qualifier ||'and l_hours_worked = '||l_hours_worked);
376 RETURN to_char(l_hours_worked); -- hours worked for MN and OR
377
378 ELSE
379
380 IF p_report_qualifier = 'NJ_SQWL' THEN
381
382 l_output_hours := lpad(l_output_hours,2,'0');
383
384 ELSIF p_report_qualifier = 'WA_SQWL' THEN
385
386 RETURN to_char(l_hours_worked);
387
388 END IF;
389
390 END IF;
391
392
393 RETURN l_output_hours;
394
395 End;
396
397 FUNCTION Get_Sqwl_Extra_Info(p_payroll_action_id NUMBER, --CONTEXT
398 p_tax_unit_id NUMBER, --CONTEXT
399 p_report_type IN varchar2,
400 p_report_qualifier IN varchar2,
401 p_input_1 IN varchar2,
402 p_input_2 IN varchar2,
403 p_input_3 IN varchar2,
404 p_output_1 IN OUT nocopy varchar2,
405 p_output_2 IN OUT nocopy varchar2,
406 p_output_3 IN OUT nocopy varchar2)
407
408 return varchar2
409 IS
410
411 TYPE arch_columns IS RECORD(
412 p_user_name ff_database_items.user_name%type,
413 p_archived_value ff_archive_items.value%type);
414
415 arch_rec arch_columns;
416
417 TYPE arch_infm IS TABLE OF arch_rec%TYPE
418 INDEX BY BINARY_INTEGER;
419
420 arch_table arch_infm;
421
422 l_count number(10) := 0;
423 l_output_value varchar2(100);
424 l_entity_id FF_USER_ENTITIES.USER_ENTITY_ID%TYPE;
425
426 /*Bug:3092981 */
427
428 CURSOR GET_ENTITY_ID(C_USER_NAME FF_USER_ENTITIES.USER_ENTITY_NAME%TYPE)
429 IS
430 SELECT USER_ENTITY_ID
431 FROM FF_USER_ENTITIES
432 WHERE USER_ENTITY_NAME = C_USER_NAME;
433
434 CURSOR GET_ARCHIVED_VALUE
435 (C_PAYROLL_ACTION_ID PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID%TYPE,
436 C_TAX_UNIT_ID PAY_ASSIGNMENT_ACTIONS.TAX_UNIT_ID%TYPE,
437 C_USER_ENTITY_ID FF_USER_ENTITIES.USER_ENTITY_ID%TYPE)
438 IS
439 SELECT SUM(FAI.VALUE),COUNT(FAI.ARCHIVE_ITEM_ID)
440 FROM FF_ARCHIVE_ITEMS FAI,
441 PAY_ASSIGNMENT_ACTIONS PAA
442 WHERE PAA.PAYROLL_ACTION_ID = C_PAYROLL_ACTION_ID
443 AND PAA.TAX_UNIT_ID = C_TAX_UNIT_ID
444 AND FAI.CONTEXT1 = TO_CHAR(PAA.ASSIGNMENT_ACTION_ID)
445 AND FAI.USER_ENTITY_ID = C_USER_ENTITY_ID
446 AND FAI.VALUE >= 0;
447
448
449 Begin
450
451 hr_utility.trace('Get_Sqwl_Extra_Info');
452
453 arch_table(1).p_user_name:='A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD';
454 arch_table(2).p_user_name:='A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD';
455
456 FOR i in arch_table.first .. arch_table.last loop
457
458 OPEN GET_ENTITY_ID(arch_table(i).p_user_name);
459
460 FETCH GET_ENTITY_ID INTO l_entity_id;
461
462 IF GET_ENTITY_ID%NOTFOUND THEN
463
464 hr_utility.trace('User entity id not found for '||arch_table(i).p_user_name);
465
466 END IF;
467
468
469 hr_utility.trace('l_entity_id = '||to_char(l_entity_id));
470
471 OPEN GET_ARCHIVED_VALUE(p_payroll_action_id,
472 p_tax_unit_id,
473 l_entity_id);
474
475 FETCH GET_ARCHIVED_VALUE INTO arch_table(i).p_archived_value, l_count;
476
477
478 hr_utility.trace('Value = '||arch_table(i).p_archived_value);
479 hr_utility.trace('Count = '||l_count);
480
481 IF GET_ARCHIVED_VALUE%NOTFOUND THEN
482
483 hr_utility.trace('Archived value not found for '||arch_table(i).p_user_name);
484
485 END IF;
486
487 CLOSE GET_ARCHIVED_VALUE;
488 CLOSE GET_ENTITY_ID;
489
490 p_output_1 := to_char(l_count);
491 END LOOP;
492
493 l_output_value := to_char((to_number(arch_table(1).p_archived_value)-to_number(arch_table(2).p_archived_value))*100);
494
495 hr_utility.trace('p_output_1 = '||p_output_1);
496 RETURN l_output_value;
497
498 End;
499
500
501 END pay_us_mmref_function_pkg;