[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.3.12020000.2 2012/12/03 06:43:16 sjawid ship $ */
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 05-Nov-08 Pannapur 115.17 7458671 Reverted the fix made in 2873584 . Including
53 Sick hours for WA SQWL .
54 31-MAY-11 rosuri 115.18 12547987 Modified function Get_Hours_Worked
55 restricted the no. of weeks in a quarter to 13
56 30-NOV-12 sjawid 115.19 12568937 Modified function Get_Hours_Worked
57 restricted the no. of weeks to 14 for PA SQWL
58 and retruning '00' as no of weeks(l_output_hours) when gross
59 earnings less than $100 as per specifications.
60
61 */
62
63 FUNCTION Get_City_Values(p_jurisdiction_code IN varchar2,
64 p_effective_date IN varchar2,
65 p_input_1 IN varchar2,
66 p_input_2 IN varchar2,
67 p_input_3 IN varchar2,
68 p_input_4 IN varchar2,
69 p_input_5 IN varchar2,
70 sp_out_1 OUT nocopy varchar2,
71 sp_out_2 OUT nocopy varchar2,
72 sp_out_3 OUT nocopy varchar2,
73 sp_out_4 OUT nocopy varchar2,
74 sp_out_5 OUT nocopy varchar2,
75 sp_out_6 OUT nocopy varchar2,
76 sp_out_7 OUT nocopy varchar2,
77 sp_out_8 OUT nocopy varchar2,
78 sp_out_9 OUT nocopy varchar2,
79 sp_out_10 OUT nocopy varchar2)
80
81 return varchar2
82
83 IS
84
85 CURSOR GET_CITY_NAME(c_jurisdiction_code varchar2)
86 IS
87 SELECT city_name
88 FROM pay_us_city_names
89 WHERE state_code = substr(c_jurisdiction_code,1,2)
90 AND county_code = substr(c_jurisdiction_code,4,3)
91 AND city_code = substr(c_jurisdiction_code,8,4)
92 AND primary_flag = 'Y';
93
94 CURSOR GET_CITY_CODE(c_jurisdiction_code varchar2,
95 c_date varchar2)
96 IS
97 /* City code starts from 5 instead of 4 in city_information1 column in
98 pay_us_city_tax_info_f table. This changes made to generalise the Local Mag Tape */
99 SELECT substr(city_information1,1,5),
100 substr(city_information1,6)
101 FROM pay_us_city_tax_info_f
102 WHERE to_date(c_date,'dd-mm-yyyy') between effective_start_date
103 and effective_end_date
104 AND jurisdiction_code = c_jurisdiction_code;
105
106 l_city_value varchar2(10);
107 l_city_id varchar2(10);
108 l_city_name pay_us_city_names.city_name%TYPE;
109
110 Begin
111
112 hr_utility.trace('Get_City_Values');
113 hr_utility.trace('p_jurisdiction_code = '||p_jurisdiction_code);
114
115 OPEN GET_CITY_NAME(p_jurisdiction_code);
116 hr_utility.trace('OPEN GET_CITY_NAME');
117 FETCH GET_CITY_NAME INTO l_city_name;
118 hr_utility.trace('FETCH GET_CITY_NAME '||l_city_name);
119
120 IF GET_CITY_NAME%NOTFOUND THEN
121
122 hr_utility.trace('No city found with this jurisdiction code = '||p_jurisdiction_code);
123 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','A');
124 pay_core_utils.push_token('record_name','jurisdiction '||p_jurisdiction_code);
125 pay_core_utils.push_token('description','City not found in pay_us_city_names.');
126 l_city_name := ' ';
127
128 END IF;
129
130 CLOSE GET_CITY_NAME;
131
132 OPEN GET_CITY_CODE(p_jurisdiction_code,p_effective_date);
133 hr_utility.trace('OPEN GET_CITY_CODE');
134 FETCH GET_CITY_CODE INTO l_city_value,l_city_id;
135 hr_utility.trace('FETCH GET_CITY_CODE '||l_city_id);
136
137 IF GET_CITY_CODE%NOTFOUND THEN
138
139 hr_utility.trace('No city information found for jurisdiction code = '||p_jurisdiction_code);
140 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','A');
141 pay_core_utils.push_token('record_name','jurisdiction '||p_jurisdiction_code
142 ||' in '||'pay_us_city_tax_info_f');
143 pay_core_utils.push_token('description','City tax infm not found.');
144
145 sp_out_1 := ' ';
146 sp_out_2 := ' ';
147
148 ELSIF GET_CITY_CODE%FOUND THEN
149
150 sp_out_1 := l_city_value;
151 sp_out_2 := l_city_id;
152 hr_utility.trace('city information found');
153 hr_utility.trace('l_city_value = '||l_city_value);
154 hr_utility.trace('l_city_id = '||l_city_id);
155
156 END IF;
157
158 RETURN l_city_name;
159
160 END;
161
162
163 FUNCTION Get_Hours_Worked(
164 p_report_type IN varchar2,
165 p_report_qualifier IN varchar2,
166 p_record_name IN varchar2,
167 p_regular_hours IN number,
168 p_sui_er_gross IN number,
169 p_gross_earnings IN number,
170 p_asg_hours IN number,
171 p_asg_freq IN varchar2,
172 p_scl_asg_work_sch IN varchar2,
173 p_input_1 IN varchar2,
174 p_input_2 IN varchar2,
175 sp_out_1 IN OUT nocopy varchar2,
176 sp_out_2 IN OUT nocopy varchar2,
177 sp_out_3 IN OUT nocopy varchar2,
178 sp_out_4 IN OUT nocopy varchar2,
179 sp_out_5 IN OUT nocopy varchar2)
180
181
182 return varchar2 IS
183 l_hours_worked number(10) :=0;
184 l_hours_per_week number(10);
185 l_output_hours varchar2(100);
186 l_add_days date;
187 lv_jd_sick_hrs number(10);
188 lv_jd_vacn_hrs number(10);
189 lv_jd_reg_hrs number(10);
190 lv_jd_ot_hrs number(10);
191
192 Begin
193
194 hr_utility.trace('Get_Hours_Worked');
195 hr_utility.trace('p_report_qualifier = '||p_report_qualifier);
196 hr_utility.trace('p_scl_asg_work_sch = '||p_scl_asg_work_sch);
197 hr_utility.trace('p_input_1 = '||p_input_1);
198
199 /* Bug:2873584 WA does not include Sick hours. */
200 /* Bug :7458671 WA include sick hours for non-Qualified plans */
201 /*IF p_report_qualifier = 'WA_SQWL' THEN
202
203 sp_out_1 :=0;
204
205 END IF; */
206
207 IF p_gross_earnings <> 0 THEN
208 IF p_input_2 = 'B' THEN
209
210 /* sp_out_1 = A_SUI_SICK_HOURS_BY_STATE_PER_JD_GRE_QTD
211 sp_out_2 = A_SUI_VACATION_HOURS_BY_STATE_PER_JD_GRE_QTD
212 sp_out_3 = A_SUI_REGULAR_HOURS_BY_STATE_PER_JD_GRE_QTD
213 sp_out_4 = A_SUI_OVERTIME_HOURS_BY_STATE_PER_JD_GRE_QTD */
214
215 lv_jd_sick_hrs := sp_out_1;
216 lv_jd_vacn_hrs := sp_out_2;
217 lv_jd_reg_hrs := sp_out_3;
218 lv_jd_ot_hrs := sp_out_4;
219
220 l_hours_worked := nvl(to_number(sp_out_1),0) + nvl(to_number(sp_out_2),0)
221 + nvl(to_number(sp_out_3),0) + nvl(to_number(sp_out_4),0);
222
223 hr_utility.trace('sp_out_1 = '||sp_out_1);
224 hr_utility.trace('sp_out_2 = '||sp_out_2);
225 hr_utility.trace('sp_out_3 = '||sp_out_3);
226 hr_utility.trace('sp_out_4 = '||sp_out_4);
227 ELSE
228 l_hours_worked := p_regular_hours*p_sui_er_gross/p_gross_earnings;
229 hr_utility.trace('p_regular_hours = '||to_char(p_regular_hours));
230 hr_utility.trace('p_sui_er_gross = '||to_char(p_sui_er_gross));
231 hr_utility.trace('p_gross_earnings = '||to_char(p_gross_earnings));
232 hr_utility.trace('p_gross_earnings <>0. l_hours_worked = '||to_char(l_hours_worked));
233 END IF;
234 END IF;
235
236 IF l_hours_worked <0 THEN /* Negative Hour checking*/
237
238 IF ((p_report_qualifier = 'MA_SQWL') OR
239 (p_report_qualifier = 'OH_SQWL') OR
240 (p_report_qualifier = 'WY_SQWL') OR
241 (p_report_qualifier = 'DE_SQWL') OR
242 (p_report_qualifier = 'NJ_SQWL') OR
243 (p_report_qualifier = 'PA_SQWL')) THEN
244
245 sp_out_5 :='Y';
246 hr_utility.trace(' l_hours_worked is negative = '||to_char(l_hours_worked));
247
248 END IF;
249
250 END IF;
251
252 IF p_scl_asg_work_sch = '99999' THEN
253
254 hr_utility.trace('p_scl_asg_work_sch = 99999 ');
255
256 l_add_days := fffunc.add_days(sysdate,6);
257 l_hours_per_week := hr_us_ff_udfs.Standard_Hours_Worked(
258 p_asg_hours,
259 sysdate,
260 l_add_days,
261 p_asg_freq);
262 hr_utility.trace('p_report_qualifier = NH.p_scl_asg_work_sch was defaulted');
263 hr_utility.trace('l_add_days = '||l_add_days);
264 hr_utility.trace('l_hours_per_week = '||to_char(l_hours_per_week));
265
266 ELSE
267
268 l_hours_per_week := hr_us_ff_udfs.work_schedule_total_hours(to_number(p_input_1),
269 p_scl_asg_work_sch,
270 null,
271 null);
272 hr_utility.trace('p_scl_asg_work_sch = '||p_scl_asg_work_sch);
273 hr_utility.trace('l_hours_per_week = '||to_char(l_hours_per_week));
274
275
276 END IF;
277
278 IF l_hours_per_week = 0 THEN
279
280 l_output_hours := '00';
281
282 ELSE
283
284 hr_utility.trace('l_hours_per_week <> 0 ');
285 hr_utility.trace('l_hours_worked = '||l_hours_worked);
286 l_output_hours := lpad(to_char(ceil(l_hours_worked/l_hours_per_week)),2,'0');
287 hr_utility.trace('l_output_hours = '||l_output_hours);
288
289 IF p_report_qualifier = 'PA_SQWL' AND to_number(l_output_hours) > 14 THEN
290 l_output_hours := 14;
291 ELSIF to_number(l_output_hours) > 13 THEN /* BUG 12547987*/
292 l_output_hours := 13;
293 END IF;
294
295 END IF;
296
297
298 IF p_report_type = 'SSA_SQWL' THEN
299
300 hr_utility.trace('SSA_SQWL');
301
302 IF p_report_qualifier = 'WY_SQWL' THEN
303
304 hr_utility.trace('WY');
305 l_output_hours := '00';
306 sp_out_1 := to_char(l_hours_worked);
307
308 ELSIF p_report_qualifier = 'DE_SQWL' THEN
309
310 hr_utility.trace('DE l_output_hours '||l_output_hours);
311 l_output_hours :=lpad(l_output_hours,2,'0');
312
313 ELSIF p_report_qualifier = 'RI_SQWL' THEN /*bug 6613661*/
314
315 hr_utility.trace('RI l_output_hours '||l_output_hours);
316 l_output_hours :=lpad(l_output_hours,2,'0');
317 sp_out_1 := to_char(round(l_hours_worked));
318 ELSE
319
320 l_output_hours := lpad(' ',2);
321
322 END IF;
323
324 ELSIF p_report_type = 'ICESA_SQWL' THEN
325
326 IF ((p_report_qualifier = 'MA_SQWL') OR
327 (p_report_qualifier = 'OH_SQWL') OR
328 (p_report_qualifier = 'PA_SQWL')) THEN
329
330 l_output_hours := lpad(l_output_hours,2,'0');
331
332 ELSIF ((p_report_qualifier = 'KY_SQWL') OR
333 (p_report_qualifier = 'KS_SQWL') OR
334 (p_report_qualifier = 'OK_SQWL')) THEN
335
336 l_output_hours:= lpad(' ', 2);
337 ELSIF (p_report_qualifier = 'VT_SQWL') THEN
338 l_output_hours := to_char(l_hours_worked);
339
340 ELSE
341
342 l_output_hours:= '00';
343
344 END IF;
345
346 IF ((p_report_qualifier = 'KS_SQWL') OR
347 (p_report_qualifier = 'KY_SQWL') OR
348 (p_report_qualifier = 'OH_SQWL')) THEN
349
350 sp_out_1 := lpad(' ',3);
351
352 ELSE
353
354 sp_out_1 := lpad('0',3,'0');
355
356 END IF;
357
358 IF p_report_qualifier = 'PA_SQWL' AND p_gross_earnings < 100 THEN
359 l_output_hours:= '00'; /*bug 12568937 */
360 END IF;
361
362 ELSIF p_report_type = 'MMREF_SQWL' THEN
363
364 IF (p_report_qualifier = 'MN_SQWL') THEN
365
366 IF p_input_2 = 'B' THEN
367 l_hours_worked := nvl(to_number(lv_jd_sick_hrs),0) + nvl(to_number(lv_jd_vacn_hrs),0)
368 + nvl(to_number(lv_jd_reg_hrs),0) + nvl(to_number(lv_jd_ot_hrs),0);
369 END IF;
370
371 ELSIF (p_report_qualifier = 'OR_SQWL') THEN /*Bug:2286335. */
372
373 IF p_input_2 = 'B' THEN
374 l_hours_worked := nvl(to_number(lv_jd_reg_hrs),0) + nvl(to_number(lv_jd_ot_hrs),0);
375 END IF;
376
377
378 IF l_hours_worked >999 THEN
379
380 l_hours_worked := 999;
381
382 END IF;
383 hr_utility.trace('l_hours_worked = '||l_hours_worked);
384
385 END IF;
386
387 hr_utility.trace('Report Qualifier = '||p_report_qualifier ||'and l_hours_worked = '||l_hours_worked);
388 RETURN to_char(l_hours_worked); -- hours worked for MN and OR
389
390 ELSE
391
392 IF p_report_qualifier = 'NJ_SQWL' THEN
393
394 l_output_hours := lpad(l_output_hours,2,'0');
395
396 ELSIF p_report_qualifier = 'WA_SQWL' THEN
397
398 RETURN to_char(l_hours_worked);
399
400 END IF;
401
402 END IF;
403
404
405 RETURN l_output_hours;
406
407 End;
408
409 FUNCTION Get_Sqwl_Extra_Info(p_payroll_action_id NUMBER, --CONTEXT
410 p_tax_unit_id NUMBER, --CONTEXT
411 p_report_type IN varchar2,
412 p_report_qualifier IN varchar2,
413 p_input_1 IN varchar2,
414 p_input_2 IN varchar2,
415 p_input_3 IN varchar2,
416 p_output_1 IN OUT nocopy varchar2,
417 p_output_2 IN OUT nocopy varchar2,
418 p_output_3 IN OUT nocopy varchar2)
419
420 return varchar2
421 IS
422
423 TYPE arch_columns IS RECORD(
424 p_user_name ff_database_items.user_name%type,
425 p_archived_value ff_archive_items.value%type);
426
427 arch_rec arch_columns;
428
429 TYPE arch_infm IS TABLE OF arch_rec%TYPE
430 INDEX BY BINARY_INTEGER;
431
432 arch_table arch_infm;
433
434 l_count number(10) := 0;
435 l_output_value varchar2(100);
436 l_entity_id FF_USER_ENTITIES.USER_ENTITY_ID%TYPE;
437
438 /*Bug:3092981 */
439
440 CURSOR GET_ENTITY_ID(C_USER_NAME FF_USER_ENTITIES.USER_ENTITY_NAME%TYPE)
441 IS
442 SELECT USER_ENTITY_ID
443 FROM FF_USER_ENTITIES
444 WHERE USER_ENTITY_NAME = C_USER_NAME;
445
446 CURSOR GET_ARCHIVED_VALUE
447 (C_PAYROLL_ACTION_ID PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID%TYPE,
448 C_TAX_UNIT_ID PAY_ASSIGNMENT_ACTIONS.TAX_UNIT_ID%TYPE,
449 C_USER_ENTITY_ID FF_USER_ENTITIES.USER_ENTITY_ID%TYPE)
450 IS
451 SELECT SUM(FAI.VALUE),COUNT(FAI.ARCHIVE_ITEM_ID)
452 FROM FF_ARCHIVE_ITEMS FAI,
453 PAY_ASSIGNMENT_ACTIONS PAA
454 WHERE PAA.PAYROLL_ACTION_ID = C_PAYROLL_ACTION_ID
455 AND PAA.TAX_UNIT_ID = C_TAX_UNIT_ID
456 AND FAI.CONTEXT1 = TO_CHAR(PAA.ASSIGNMENT_ACTION_ID)
457 AND FAI.USER_ENTITY_ID = C_USER_ENTITY_ID
458 AND FAI.VALUE >= 0;
459
460
461 Begin
462
463 hr_utility.trace('Get_Sqwl_Extra_Info');
464
465 arch_table(1).p_user_name:='A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD';
466 arch_table(2).p_user_name:='A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD';
467
468 FOR i in arch_table.first .. arch_table.last loop
469
470 OPEN GET_ENTITY_ID(arch_table(i).p_user_name);
471
472 FETCH GET_ENTITY_ID INTO l_entity_id;
473
474 IF GET_ENTITY_ID%NOTFOUND THEN
475
476 hr_utility.trace('User entity id not found for '||arch_table(i).p_user_name);
477
478 END IF;
479
480
481 hr_utility.trace('l_entity_id = '||to_char(l_entity_id));
482
483 OPEN GET_ARCHIVED_VALUE(p_payroll_action_id,
484 p_tax_unit_id,
485 l_entity_id);
486
487 FETCH GET_ARCHIVED_VALUE INTO arch_table(i).p_archived_value, l_count;
488
489
490 hr_utility.trace('Value = '||arch_table(i).p_archived_value);
491 hr_utility.trace('Count = '||l_count);
492
493 IF GET_ARCHIVED_VALUE%NOTFOUND THEN
494
495 hr_utility.trace('Archived value not found for '||arch_table(i).p_user_name);
496
497 END IF;
498
499 CLOSE GET_ARCHIVED_VALUE;
500 CLOSE GET_ENTITY_ID;
501
502 p_output_1 := to_char(l_count);
503 END LOOP;
504
505 l_output_value := to_char((to_number(arch_table(1).p_archived_value)-to_number(arch_table(2).p_archived_value))*100);
506
507 hr_utility.trace('p_output_1 = '||p_output_1);
508 RETURN l_output_value;
509
510 End;
511
512
513 END pay_us_mmref_function_pkg;